Организация вычислений в электронных таблицах
Давай превратим электронные таблицы в умных помощников! Сегодня мы узнаем, как организовать вычисления так, чтобы таблицы работали за нас — автоматически считали, проверяли условия и обрабатывали тысячи строк данных. Готов? Поехали!
Зачем нам всё это?
Представь: ты ведёшь учёт своих расходов на неделю, считаешь средний балл по предметам или следишь за статистикой в любимой игре. Можно, конечно, каждый раз брать калькулятор и пересчитывать всё вручную. Но есть способ круче — электронные таблицы, которые сделают всю работу за тебя!
✨ Магия таблиц
Стоит тебе изменить одно число, и все результаты пересчитаются автоматически. Магия? Нет, просто грамотно настроенные формулы и функции.
🎯 Основное назначение
Электронные таблицы созданы для организации всевозможных вычислений. Вычисление — это процесс расчёта по формулам. Формула начинается со знака равенства и может включать в себя знаки операций, числа, ссылки и встроенные функции.
Часть 1. Ссылки — связующие нити таблицы
Сейчас мы узнаем, как ссылки помогают связывать данные в разных ячейках и автоматизировать вычисления.
Ссылки соединяют ячейки и автоматизируют расчёты
🔗 Что такое ссылка?
Ссылка — это как адрес ячейки в таблице. Например, А1 означает "ячейка в столбце А, строка 1". Ссылки позволяют использовать данные из разных ячеек в формулах.
Пример из жизни: В ячейке А2 у тебя записано число 5, а в В2 — число 10. Если в ячейку С2 ты введёшь формулу =А2+В2, таблица автоматически посчитает и выдаст результат: 15. Круто, да?
📌 Для чего нужны ссылки?
Ссылки позволяют:
- Использовать в одной формуле данные, находящиеся в разных частях электронной таблицы
- Использовать в нескольких формулах значение одной ячейки
Типы ссылок: относительные, абсолютные и смешанные
Ссылки бывают трёх типов. Разберёмся, чем они отличаются и когда каждую из них использовать.
Три типа ссылок: гибкие, жёсткие и смешанные
📍 Относительная ссылка
Это самая обычная ссылка, например А1 или В2. Она "привязана" к позиции ячейки относительно формулы.
Как работает: Присутствующая в формуле относительная ссылка определяет расположение ячейки с данными относительно ячейки, в которой записана формула. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка.
🔒 Абсолютная ссылка
Абсолютная ссылка — это "заморозка" адреса. Она записывается со знаком доллара: $A$1.
Как работает: Абсолютная ссылка в формуле всегда ссылается на ячейку, расположенную в определённом (фиксированном) месте. Перед каждой буквой и цифрой помещается знак $, например $A$1. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется.
🔓 Смешанная ссылка
Смешанная ссылка — это гибрид: одна часть адреса фиксирована (со знаком $), а другая — изменяется.
Примеры: $A1 (фиксирован столбец) или A$1 (фиксирована строка). При изменении позиции ячейки, содержащей формулу, относительная часть адреса изменяется, а абсолютная часть адреса не изменяется.
Примеры работы со ссылками
📊 Пример 1: Относительные ссылки
Задача: Рассмотрим формулу =A1^2, записанную в ячейке А2. Она содержит относительную ссылку А1.
| A | B | C | D | |
| 1 | 2 | 3 | 4 | 5 |
| 2 | =A1^2 | =B1^2 | =C1^2 | =D1^2 |
Объяснение: При копировании формулы вдоль столбца и вдоль строки относительная ссылка автоматически корректируется:
- Смещение на один столбец приводит к изменению в ссылке одной буквы в имени столбца
- Смещение на одну строку приводит к изменению в ссылке номера строки на единицу
💰 Пример 2: Абсолютные ссылки
Задача: Некий гражданин открывает в банке счёт на сумму 10 000 рублей. Ему сообщили, что каждый месяц сумма вклада будет увеличиваться на 1,2%. Для того чтобы узнать возможную сумму и приращение суммы вклада через 1, 2, ..., 6 месяцев, гражданин провёл следующие расчёты:
| A | B | C |
| Начальная сумма вклада: | 10 000 | |
| Месяц | Сумма | Приращение |
| 1 | =C1+C1*0,012 | =B3-$C$1 |
| 2 | =B3+B3*0,012 | =B4-$C$1 |
| ... | ... | ... |
Обратите внимание: В абсолютной ссылке перед каждой буквой и цифрой помещается знак $, например $A$1. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется.
🔢 Пример 3: Смешанные ссылки
Задача: Требуется составить таблицу сложения чисел первого десятка, т. е. заполнить таблицу следующего вида:
| A | B | C | D | E | ... | J |
| 1 | 2 | 3 | 4 | ... | 9 | |
| 1 | 2 | 3 | 4 | 5 | ... | 10 |
| ... | ... | ... | ... | ... | ... | ... |
Решение: Внесите в ячейку B2 формулу =$A2+B$1 и скопируйте её на весь диапазон B2:J10.
Объяснение: У первого слагаемого неизменным остаётся имя столбца (на нём следует дать абсолютную ссылку), но изменяется номер строки (на неё следует дать относительную ссылку); у второго слагаемого изменяется номер столбца (относительная ссылка), но остаётся неизменным номер строки (абсолютная ссылка).
⚡ Как преобразовать ссылку?
Чтобы преобразовать ссылку из относительной в абсолютную и наоборот, можно выделить её в строке ввода и нажать клавишу F4 (Microsoft Office Excel) или комбинацию клавиш Shift+F4 (OpenOffice Calc).
- При первом нажатии получится абсолютная ссылка ($A$1)
- При втором нажатии — смешанная ссылка (A$1)
- При третьем — другая смешанная ($A1)
- При четвёртом — снова относительная (A1)
Самое главное (Часть 1)
Проверь себя (Часть 1)
1. Представь, что ты копируешь формулу из ячейки B2 в ячейку C3. Как изменится ссылка А1, если она относительная? А если абсолютная?
Подсказка: При копировании относительная ссылка смещается на столько же клеток, на сколько переместилась формула.
2. Задачка на сообразительность: У тебя в ячейке А1 записан курс доллара (например, 75 рублей). Ты хочешь перевести суммы из столбца В (в долларах) в рубли и записать результаты в столбец С. Какую ссылку на А1 лучше использовать — относительную или абсолютную? Почему?
Подумай: Курс доллара один для всех расчётов, или он должен меняться для каждой строки?
3. Придумай свой пример, где тебе понадобится смешанная ссылка.
Идея: Подумай о таблице умножения, расписании уроков с ценами, или конвертере величин.
Часть 2. Встроенные функции — готовые решения для любых задач
Формулы — это здорово, но что если тебе нужно сложить не 2 числа, а 100? Или найти среднее значение? Или проверить условие? Для таких задач придуманы встроенные функции — готовые "рецепты", которые делают сложные вычисления за пару секунд.
Встроенные функции — твой набор инструментов для вычислений
⚙️ Что такое функции?
При обработке данных в электронных таблицах можно использовать встроенные функции — заранее определённые формулы. Функция возвращает результат выполнения действий над значениями, выступающими в качестве аргументов. Использование функций позволяет упростить формулы и сделать процесс вычислений более понятным.
📚 Категории функций
В электронных таблицах реализовано несколько сотен встроенных функций, подразделяющихся на:
- Математические
- Статистические
- Логические
- Текстовые
- Финансовые
- И другие
🏷️ Имена функций
Каждая функция имеет уникальное имя, которое используется для её вызова. Имя, как правило, представляет собой сокращённое название функции на естественном языке. При выполнении табличных расчётов достаточно часто используются функции:
➕ СУММ (SUM)
Назначение: Суммирование аргументов
Пример: =СУММ(A1:A10) — сложит все числа от ячейки А1 до А10.
Когда пригодится? Ты ведёшь учёт карманных денег за месяц и хочешь узнать общую сумму.
📉 МИН (MIN)
Назначение: Определение наименьшего значения из списка аргументов
Пример: =МИН(B1:B20) — найдёт самое маленькое число в диапазоне.
Когда пригодится? Ты ищешь самый низкий результат в игре или минимальную температуру за неделю.
📈 МАКС (MAX)
Назначение: Определение наибольшего значения из списка аргументов
Пример: =МАКС(B1:B20) — найдёт самое большое число в диапазоне.
Когда пригодится? Ты записываешь свои результаты в игре и хочешь узнать свой лучший рекорд.
📊 СРЗНАЧ (AVERAGE)
Назначение: Вычисляет среднее арифметическое
Пример: =СРЗНАЧ(C1:C5) — найдёт среднее значение пяти чисел.
Когда пригодится? Ты хочешь посчитать свой средний балл по всем предметам.
🔢 СЧЁТ (COUNT)
Назначение: Определение количества числовых ячеек диапазона (пустые и текстовые ячейки не учитываются)
Пример: =СЧЁТ(D1:D50) — посчитает, сколько ячеек с числами в диапазоне.
Когда пригодится? Ты проводишь опрос в классе и хочешь узнать, сколько человек ответило на вопрос.
🔧 Мастер функций
Диалоговое окно Мастер функций позволяет упростить создание формул и свести к минимуму количество опечаток и синтаксических ошибок. При вводе функции в формулу диалоговое окно Мастер функций отображает имя функции, все её аргументы, описание функции и каждого из аргументов, текущий результат функции и всей формулы.
Пример 4: Работа со встроенными функциями
🏆 Задача: Спортивные соревнования
Правила судейства в международных соревнованиях по одному из видов спорта таковы:
- Выступление каждого спортсмена оценивают N судей
- Максимальная и минимальная оценки (по одной, если их несколько) каждого спортсмена отбрасываются
- В зачёт спортсмену идёт среднее арифметическое оставшихся оценок
Информация о соревнованиях представлена в электронной таблице:
| A | B | C | D | E | F |
| Протокол соревнований | |||||
| Спортсмен 1 | Спортсмен 2 | Спортсмен 3 | Спортсмен 4 | Спортсмен 5 | |
| Судья 1 | 5,9 | 9,8 | 7,8 | 9,1 | 6,9 |
| Судья 2 | 6,3 | 9,7 | 8,0 | 9,3 | 7,8 |
| ... | ... | ... | ... | ... | ... |
Требуется подсчитать оценки всех участников соревнований и определить оценку победителя.
✅ Решение
Для этого:
- В ячейки A10, A11, A12 и A14 заносим тексты «Максимальная оценка», «Минимальная оценка», «Итоговая оценка», «Оценка победителя»
- В ячейку B10 заносим формулу
=МАКС(B3:B8); копируем содержимое ячейки B10 в ячейки C10:F10 - В ячейку B11 заносим формулу
=МИН(B3:B8); копируем содержимое ячейки B11 в ячейки C11:F11 - В ячейку B12 заносим формулу
=(СУММ(B3:B8)–B10–B11)/4; копируем содержимое ячейки B12 в ячейки C12:F12 - В ячейку B14 заносим формулу
=МАКС(B12:F12)
📋 Результат решения задачи:
| A | B | C | D | E | F |
| Протокол соревнований | |||||
| Спортсмен 1 | Спортсмен 2 | Спортсмен 3 | Спортсмен 4 | Спортсмен 5 | |
| Максимальная оценка | 6,6 | 9,9 | 8,9 | 9,9 | 8,9 |
| Минимальная оценка | 5,4 | 8,9 | 6,4 | 8,8 | 6,9 |
| Итоговая оценка | 6,05 | 9,55 | 7,975 | 9,25 | 7,975 |
| Оценка победителя | 9,55 | ||||
Самое главное (Часть 2)
Проверь себя (Часть 2)
1. Как ты думаешь, почему функции называют "встроенными"? Чем они отличаются от обычных формул?
Подумай: встроенные функции — это готовые решения, которые уже "зашиты" в программу.
2. Задачка: У тебя есть таблица с оценками за четверть: 5, 4, 5, 3, 4, 5. Какую функцию ты используешь, чтобы быстро посчитать средний балл?
Подсказка: нужна функция для вычисления среднего арифметического.
3. Представь, что ты собираешь статистику по своим тренировкам: количество отжиманий каждый день. Какая функция поможет найти твой личный рекорд? А какая — среднее количество отжиманий за неделю?
Две задачи:
- Личный рекорд — это максимальное значение
- Среднее за неделю — это среднее арифметическое
Часть 3. Логические функции — учим таблицу принимать решения
А теперь самое интересное! Что если таблице нужно не просто считать, а принимать решения? Например: "Если балл выше 4, написать 'Молодец!', а если ниже — 'Нужно подтянуть'". Для этого существуют логические функции.
Логические функции помогают таблице выбирать путь
🔀 Логические операции
При изучении предшествующего материала вы неоднократно встречались с логическими операциями НЕ, И, ИЛИ (not, and, or). Построенные с их помощью логические выражения вы использовали при организации поиска в базах данных, при программировании различных вычислительных процессов.
⚡ Логические функции в таблицах
Реализованы логические операции и в электронных таблицах, но здесь они представлены как функции: сначала записывается имя логической операции, а затем в круглых скобках перечисляются логические операнды.
Например, логическое выражение, соответствующее двойному неравенству 0 < A1 < 10, в электронных таблицах будет записано как:
И(A1>0;A1<10)(в англоязычной версии программы AND(A1>0;A1<10))
🧮 Пример 5: Таблица истинности
Вычислим в электронных таблицах значения логического выражения НЕ A И НЕ B при всех возможных значениях входящих в него логических переменных.
| A | B | C | D | E |
| Таблица истинности НЕ A И НЕ B | ||||
| A | B | НЕ A | НЕ B | НЕ A И НЕ B |
| ЛОЖЬ | ЛОЖЬ | =НЕ(A3) | =НЕ(B3) | =И(C3;D3) |
| ЛОЖЬ | ИСТИНА | =НЕ(A4) | =НЕ(B4) | =И(C4;D4) |
| ИСТИНА | ЛОЖЬ | =НЕ(A5) | =НЕ(B5) | =И(C5;D5) |
| ИСТИНА | ИСТИНА | =НЕ(A6) | =НЕ(B6) | =И(C6;D6) |
Объяснение: При решении этой задачи мы следовали известному вам алгоритму построения таблицы истинности для логического выражения. Вычисления в диапазонах ячеек C3:C6, D3:D6, E3:E6 проводятся компьютером по заданным нами формулам.
Условная функция ЕСЛИ (IF)
🎯 Структура функции ЕСЛИ
Для проверки условий при выполнении расчётов в электронных таблицах реализована логическая функция ЕСЛИ (IF), называемая условной функцией.
ЕСЛИ (условие; действие_1; действие_2)
Здесь условие — логическое выражение, т. е. любое выражение, построенное с помощью операций отношения и логических операций, принимающее значение ИСТИНА или ЛОЖЬ.
Если логическое выражение истинно, то значение ячейки, в которую записана условная функция, определяет действие_1, если ложно — действие_2.
💭 Что вам напоминает структура условной функции?
Это напоминает алгоритмическую структуру "ветвление" из языков программирования!
🏀 Пример 6: Отбор в баскетбольную команду
Рассмотрим задачу о приёме в школьную баскетбольную команду: ученик может быть принят в эту команду, если его рост не менее 170 см.
Данные о претендентах (фамилия, рост) представлены в электронной таблице:
| A | B | C |
| Баскетбольная команда | ||
| Ученик | Рост, см | Решение |
| Васечкин | 169 | =ЕСЛИ(B3>=170;"принят";"не принят") |
| Дроздов | 182 | =ЕСЛИ(B4>=170;"принят";"не принят") |
| Иванов | 173 | =ЕСЛИ(B5>=170;"принят";"не принят") |
| Куликов | 158 | =ЕСЛИ(B6>=170;"принят";"не принят") |
| Петров | 190 | =ЕСЛИ(B7>=170;"принят";"не принят") |
| Сидоров | 170 | =ЕСЛИ(B8>=170;"принят";"не принят") |
| =СЧЁТЕСЛИ(C3:C8;"принят") | ||
Использование условной функции в диапазоне ячеек C3:C8 позволяет вынести решение (принят/не принят) по каждому претенденту.
Функция СЧЁТЕСЛИ (COUNTIF) позволяет подсчитать количество ячеек в диапазоне, удовлетворяющих заданному условию. С помощью этой функции в ячейке C9 подсчитывается число претендентов, прошедших отбор в команду.
📝 Другие функции с условиями
- СУММЕСЛИ (SUMIF) — суммирует значения ячеек диапазона, удовлетворяющие заданному условию
- СРЗНАЧЕСЛИ (AVERAGEIF) — вычисляет среднее значение ячеек диапазона, удовлетворяющих заданному условию
Самое главное (Часть 3)
ЕСЛИ(условие; действие_если_ИСТИНА; действие_если_ЛОЖЬ)
Проверь себя (Часть 3)
1. Объясни своими словами: что делает функция ЕСЛИ? Приведи свой пример из жизни.
Подумай: функция ЕСЛИ работает как развилка на дороге — в зависимости от условия выбирается один из двух путей.
2. Задачка: У тебя в таблице записаны результаты твоих друзей в беге на 100 метров (в секундах). Как ты запишешь формулу, которая выведет "Быстрый!", если время меньше 15 секунд, и "Тренируйся!", если больше?
Подсказка: используй функцию ЕСЛИ с условием на время.
=ЕСЛИ(A1<15; "Быстрый!"; "Тренируйся!")
3. Усложнённая задачка: В твоей таблице есть столбец с оценками по информатике (от 2 до 5). Какую функцию ты используешь, чтобы посчитать, сколько пятёрок в классе?
Ответ: СЧЁТЕСЛИ — она подсчитает количество ячеек, удовлетворяющих условию (равных 5).
Часть 4. Работа с большими наборами данных
Все примеры, которые мы разбирали до этого, можно было выполнить и вручную — или на калькуляторе. Но настоящая сила электронных таблиц проявляется, когда нужно обработать большие объёмы данных — сотни или тысячи строк!
Электронные таблицы превращают хаос данных в порядок
📊 Обработка больших наборов данных
Все примеры вычислений в электронных таблицах, которые были рассмотрены до сих пор, вы могли выполнить вручную или с помощью калькулятора. Но приведённые выше функции можно применять и для обработки больших наборов данных, содержащих тысячи строк. Вряд ли кто-то из вас возьмётся за обработку такой таблицы вручную!
🎓 Пример 7: Тестирование учеников
В электронную таблицу занесли данные о тестировании учеников по выбранным ими предметам.
| A | B | C | D | E |
| Ученик | Район | Математика | Информатика | Физика |
| Код 1 | Майский | 100 | 58 | 74 |
| Код 2 | Майский | 100 | 80 | 81 |
| Код 3 | Майский | 99 | 76 | 63 |
| Код 4 | Центральный | 98 | 86 | 51 |
| Код 5 | Подгорный | 97 | 66 | 47 |
| Код 6 | Заречный | 96 | 98 | 63 |
В столбце A записан код фамилии и имени ученика, в столбце B — название района, в котором учится ученик; в столбцах C, D, E — баллы, набранные учеником на тестировании по математике, информатике и физике соответственно.
Всего в электронную таблицу были занесены данные 1000 учеников. На основании данных, содержащихся в этой таблице, требуется ответить на ряд вопросов.
❓ Вопрос 1. Сколько всего учащихся Майского района приняли участие в тестировании?
Варианты решения:
Вариант 1 решения:
Заполним ячейки диапазона F2:F1001 вспомогательной информацией: запишем в ячейку 1, если в соответствующей ей строке указано название района «Майский», и 0 — в противном случае.
Для этого в ячейку F2 запишем формулу:
=ЕСЛИ(B2="Майский";1;0)(=IF(B2="Майский";1;0))
Скопируем эту формулу во все следующие ячейки столбца F, достаточно выполнить двойной щелчок мышью на маркере автозаполнения ячейки с формулой.
В ячейку G1002 запишем формулу:
=СУММ(F2:F1001)(=SUM(F2:F1001))
Вариант 2 решения:
Можно воспользоваться функцией СЧЁТЕСЛИ (COUNTIF), подсчитав с её помощью количество ячеек диапазона B2:B1001, содержащих название района «Майский». Для этого достаточно в ячейку G1002 записать формулу:
=СЧЁТЕСЛИ(B2:B1001;"Майский")(=COUNTIF(B2:B1001;"Майский"))
❓ Вопрос 2. Сколько учащихся Кировского района набрали больше 75 баллов по математике?
Вариант 1 решения:
Заполним ячейки диапазона G2:G1001 вспомогательной информацией: запишем в ячейку 1, если в соответствующей ей строке указано название района «Кировский» и есть информация о том, что количество баллов по математике больше, чем 75; в противном случае укажем 0.
Для этого в ячейку G2 запишем формулу:
=ЕСЛИ(И(B2="Кировский";C2>75);1;0)(=IF(AND(B2="Кировский";C2>75);1;0))
Скопируем эту формулу во все ячейки диапазона G3:G1001. В ячейку G1003 запишем формулу:
=СУММ(G2:G1001)(=SUM(G2:G1001))
Вариант 2 решения:
Функция СЧЁТЕСЛИ (COUNTIF) не работает с составными условиями. Для этого предназначена функция СЧЁТЕСЛИМН (COUNTIFS). Вот формула, которую для ответа на вопрос достаточно ввести в ячейку G1003:
=СЧЁТЕСЛИМН(B2:B102;"Кировский";C2:C102;">75")(=COUNTIFS(B2:B102;"Кировский";C2:C102;">75"))
❓ Вопрос 3. Каков средний балл по информатике учеников из Центрального района?
Вариант 1 решения:
Заполним ячейки диапазона H2:H1001 вспомогательной информацией: если в строке указано название района «Центральный», то в соответствующей ячейке укажем количество баллов, набранных учеником по информатике; в противном случае укажем 0. В ячейку H2 запишем формулу:
=ЕСЛИ(B2="Центральный";D2;0)(=IF(B2="Центральный";D2;0))
Определим число учеников Центрального района, сдававших информатику; оно равно количеству ненулевых значений в столбце H. В ячейку I2 запишем формулу:
=СЧЁТЕСЛИ(H2:H1001;">0")(=COUNTIF(H2:H1001;">0"))
Подсчитаем сумму баллов, набранных учениками Центрального района по информатике; она равна сумме значений ячеек столбца H. В ячейку I3 запишем формулу:
=СУММ(H2:H1001)(=SUM(H2:H1001))
В ячейку G1004 запишем формулу:
=I3/I2
Установим требуемую точность ответа.
Вариант 2 решения:
Для подсчёта числа учеников Центрального района в ячейку I2 введём формулу:
=СЧЁТЕСЛИ(B2:B1001;"Центральный")(=COUNTIF(B2:B1001;"Центральный"))
Чтобы найти суммарный балл, набранный учениками Центрального района по информатике, можно воспользоваться функцией СУММЕСЛИ(SUMIF). Для этого в ячейку I3 введём формулу:
=СУММЕСЛИ(B2:B1001;"Центральный";D2:D1001)(=SUMIF(B2:B1001;"Центральный";D2:D1001))
Обратите внимание на аргументы этой функции:
- первый — это диапазон, по которому выполняется проверка условия
- второй — проверяемое условие
- третий — диапазон, значения которого суммируются
В ячейку G1004 запишем формулу =I3/I2
Установим требуемую точность ответа.
Вариант 3 решения:
Функция СРЗНАЧЕСЛИ(AVERAGEIF) позволяет решить задачу с помощью единственной формулы:
=СРЗНАЧЕСЛИ(B2:B1001;"Центральный";D2:D1001)(=AVERAGEIF(B2:B1001;"Центральный";D2:D1001))
💡 Важно помнить!
Откройте файл Тестирование.xls с данными из примера 7. Выполните на компьютере все рассмотренные выше вычисления.
Самое главное (Часть 4)
Проверь себя (Часть 4)
1. Почему при работе с большими данными лучше использовать вспомогательные ячейки, а не писать одну гигантскую формулу?
Причины:
- Легче найти ошибку
- Можно проверить промежуточные результаты
- Формулы становятся понятнее
- Проще изменить логику расчёта
2. Задачка: У тебя есть таблица с результатами олимпиады по программированию (1000 участников). В столбце B записан класс участника (7, 8 или 9), в столбце C — количество баллов. Какую функцию ты используешь, чтобы узнать средний балл девятиклассников?
Решение:
=СРЗНАЧЕСЛИ(B2:B1001;"9";C2:C1001)
Эта функция найдёт все строки, где в столбце B указан класс 9, и вычислит среднее значение соответствующих баллов из столбца C.
3. Придумай свою задачу, где тебе понадобится анализировать большой набор данных. Какие функции ты бы использовал?
Примеры задач:
- Анализ продаж интернет-магазина за год
- Статистика посещаемости спортивных секций
- Результаты голосования за лучший фильм
- Учёт успеваемости всех классов школы
📋 Итоговое резюме: Самое-самое главное
🤔 Вопросы и задания
Проверьте, как хорошо вы усвоили материал!
1. Назовите основные типы ссылок
Вспомни три типа: относительные, абсолютные и смешанные. Объясни разницу между ними.
2. Охарактеризуйте относительный тип ссылок
Относительная ссылка определяет расположение ячейки относительно ячейки, в которой записана формула. При копировании формулы она автоматически изменяется.
3. По данным электронной таблицы определите значение в ячейке C1
| A | B | C |
| 3 | =A1*2+2 | =A1+B1 |
Подсказка: Сначала вычисли значение B1, потом используй его для вычисления C1.
4. Определите значения в ячейках C2 и C3 после копирования в них формулы из ячейки C1. Проверьте свои предположения на компьютере.
Используй знания об относительных ссылках. Как изменится формула при копировании вниз?
5. Определите значения в ячейках диапазона D1:D3 после копирования в них формулы из ячейки C3. Проверьте свои предположения на компьютере.
Помни: при копировании вправо изменяются буквы столбцов в ссылках.
6. Охарактеризуйте абсолютный тип ссылок
Абсолютная ссылка всегда ссылается на ячейку в фиксированном месте. Записывается со знаками $ перед буквой и цифрой ($A$1). При копировании формулы не изменяется.
7. Определите значения в ячейках C2 и C3 после копирования в них формулы из ячейки C1. Проверьте свои предположения на компьютере.
Обрати внимание на абсолютные ссылки в формуле!
8. Охарактеризуйте смешанный тип ссылок
Смешанная ссылка содержит одну фиксированную часть (столбец или строку) и одну изменяемую. Примеры: $A1 (фиксирован столбец) или A$1 (фиксирована строка).
9. Определите значения в ячейках диапазона C1:D3 после копирования в них формулы из ячейки C1. Проверьте свои предположения на компьютере.
Анализируй, как будут изменяться смешанные ссылки при копировании по горизонтали и вертикали.
10. Как можно изменить тип ссылки?
Ответ: Выделить ссылку в строке ввода и нажать клавишу F4 (или Shift+F4 в OpenOffice). При каждом нажатии тип ссылки будет меняться циклически.
11. О чём идёт речь в следующем высказывании: «Знак доллара "замораживает" как весь адрес, так и его отдельную часть»? Дайте развёрнутый комментарий к высказыванию, основываясь на материале параграфа. Обсудите этот вопрос в группе.
Это высказывание описывает принцип работы абсолютных и смешанных ссылок. Знак $ "замораживает" ту часть адреса, перед которой он стоит, делая её неизменной при копировании формулы.
12. Для чего нужны встроенные функции?
Встроенные функции — это готовые формулы для выполнения типовых вычислений. Они упрощают работу и делают формулы понятнее. Без функций пришлось бы писать длинные сложные формулы.
13. Какие категории встроенных функций реализованы в табличном процессоре, имеющемся в вашем распоряжении?
Математические, статистические, логические, текстовые, финансовые, функции даты и времени, функции для работы с базами данных и другие.
14. Определите значение в ячейке D3
| A | B | C | D |
| 2 | 3 | 1 | =MIN(A1:C1) |
| 1 | 4 | =B2+A2 | =SUM(A2:C2) |
| =D2*D1 |
Решение: Последовательно вычисли значения D1, C2, D2, затем D3.
15. Какая из формул не содержит ошибок?
а) =ЕСЛИ((С4>4) И (С5>4));"Принят!";"Не принят")
б) =ЕСЛИ(И(D2=0;B2/4);D3–A1;D3+A1)
в) =ЕСЛИ((A4=0 И D1<0);1;0)
г) =ЕСЛИ(ИЛИ(A2>10;C2>10);1;"ура!")
Подсказка: Проверь правильность синтаксиса каждой формулы.
16. Формула, позволяющая подсчитать полную стоимость заказа с учётом стоимости его доставки
Условие: В ячейке A5 электронной таблицы находится суммарная стоимость товаров, заказанных Ивановым в интернет-магазине. Формула, позволяющая подсчитать полную стоимость заказа с учётом стоимости его доставки, имеет вид:
=ЕСЛИ(A5>2000;A5;A5+150)
По данной формуле постройте блок-схему. Определите, какие льготы предоставляются покупателю в случае, если суммарная стоимость заказанных им товаров превышает 2000.
17. Оплата за аренду конференц-зала
Оплата за аренду конференц-зала вычисляется по следующим правилам: каждый из первых четырёх часов аренды стоит 1000 рублей, каждый последующий час — 750 рублей. В ячейке B8 электронной таблицы находится количество полных часов аренды зала. Какая из формул позволяет подсчитать полную стоимость аренды зала?
- а) =ЕСЛИ(B8<=4;B8*1000;4000+B8*750)
- б) =ЕСЛИ(B8<=4;B8*1000;B8*1000+(B8–4)*750)
- в) =ЕСЛИ(B8<=4;B8*1000;(B8+(B8–4)*750))
- г) =ЕСЛИ(B8<=4;B8*1000;4000+(B8–4)*750)
18. В электронную таблицу занесли результаты муниципального этапа олимпиады по программированию
В столбце A указаны фамилия и имя учащегося; в столбцах B и C — класс и школа, в которой он учится; в столбцах D, E, F — баллы, полученные учеником за решение каждой из трёх олимпиадных задач. За каждую задачу можно получить от 0 до 25 баллов. Всего в электронную таблицу были занесены данные 115 учащихся. Гарантируется, что есть ученики, правильно решившие все задачи. Порядок записей в таблице произвольный.
Определите, значения каких величин будут получены в результате последовательного выполнения вычислений по формулам из задачи.
🎯 Практические задания
Попробуй применить полученные знания на практике!
📝 Задание 1: Работа со ссылками
Создай таблицу для конвертера валют. В ячейке A1 — курс доллара, в столбце B — суммы в долларах, в столбце C — суммы в рублях. Используй правильный тип ссылки на курс!
🔢 Задание 2: Встроенные функции
Создай таблицу со своими оценками за четверть по разным предметам. Используй функции СУММ, СРЗНАЧ, МАКС и МИН для анализа своих результатов.
⚖️ Задание 3: Логические функции
Создай таблицу "Допуск к экзамену". Ученик допускается, если средний балл ≥4 И пропусков <10. Используй функции ЕСЛИ и И.
📊 Задание 4: Большие данные
Придумай свою задачу с большим количеством данных (например, результаты спортивных соревнований) и реши её, используя функции с условиями.
