Электронные таблицы: формулы, функции, диаграммы

В конце четверти у тебя 12 оценок по информатике, и учитель складывает их на калькуляторе — третий раз пересчитывает, боясь ошибки. В электронной таблице это делает формула: оценки записаны в ячейки A1:A12, под ними =СУММ(A1:A12) — и готово. Таблица состоит из ячеек; у каждой адрес из буквы столбца и номера строки: A1, B3, C7. В ячейку кладут число, текст или формулу, которая всегда начинается со знака равенства: =B2*1.2 пересчитается сама, стоит поменять значение в B2.

Адреса, диапазоны и как они считаются

Столбцы в таблице обозначают латинскими буквами: A, B, C, ..., Z, а дальше идут двухбуквенные AA, AB, AC. Строки нумеруют числами: 1, 2, 3 и так далее до миллиона с лишним. Адрес ячейки — буква столбца плюс номер строки: C7 — это столбец C, строка 7. Диапазон записывают через двоеточие: A1:C5 означает прямоугольник из 3 столбцов и 5 строк, то есть 15 ячеек. Правило простое: число столбцов умножить на число строк. Отдельные ячейки, если они не рядом, перечисляют через точку с запятой: =СУММ(A1;B4;C7) сложит три несоседних значения. Диапазоны экономят время: вместо двенадцати слагаемых через плюс пишут =СУММ(A1:A12).

Формулу вводят так: щёлкаешь ячейку, ставишь знак равенства, набираешь выражение и жмёшь Enter — в ячейке появится результат, а сама формула останется видна в строке формул сверху. Чтобы не набирать формулу в каждой строке заново, её протягивают: цепляешь маленький квадратик в правом нижнем углу ячейки и тянешь вниз — таблица сама скопирует формулу, поправив относительные ссылки по пути. Именно в момент протягивания относительные и абсолютные ссылки начинают вести себя по-разному, поэтому разберём их отдельно.

Ссылки: почему $A$1 не съезжает

Формулу из одной ячейки копируют в другие — и тут начинается магия ссылок. Относительная ссылка A1 при копировании меняется: в соседнем столбце станет B1, строкой ниже — A2. Абсолютная — $A$1 — закреплена двумя долларами и при любом копировании остаётся собой. Это нужно, когда множитель общий: курс валюты или вес оценки лежит в одной ячейке, и каждая формула в столбце должна брать именно её. Функции ускоряют счёт: СУММ складывает диапазон, СРЗНАЧ находит среднее, МАКС и МИН — крайние значения. А по готовым числам строят диаграммы: круговая показывает доли одного целого, гистограмма сравнивает столбики.

Смешанные ссылки: доллар на половину адреса

Доллар можно поставить не на весь адрес, а на одну часть. Запись $A1закрепляетстолбецA,нооставляетстрокусвободной:прикопированиивправоссылкаостанется$A1, а вниз поедет в $A2.ЗаписьA$1 наоборот — столбец свободен, строка закреплена: вправо станет B$1,внизостанетсяA$1. Смешанные ссылки выручают в таблицах умножения и прайсах, где курс или множитель лежит в шапке первой строки, а названия товаров — в первом столбце. Проверяй себя так: доллар перед буквой замораживает столбец, доллар перед числом замораживает строку.

Функции, которые выручают чаще всего

Разберём копирование формул на деньгах. В B1 лежит курс: 92 рубля за доллар, в столбец A введены цены в долларах. В B5 пишешь =A5*$B$1 и протягиваешь вниз: A5 превращается в A6, A7 — это относительная часть, а $B$1 остаётся на месте. Убери доллары — и формула поедет вниз вместе с курсом, ссылки попадут на пустые ячейки, и таблица насчитает ерунду. Функция ЕСЛИ добавляет таблице мозги: =ЕСЛИ(СРЗНАЧ(A1:A12)>=4; "хорошист"; "подтянись") сама решает, что написать в ячейке.

Проверь функции на понятных числах. В A1:A4 записаны 3, 5, 2, 6: СУММ даст 16, СРЗНАЧ — 4, МАКС — 6, МИН — 2, СЧЁТ — 4. Если в диапазон попала ячейка с текстом «болел», СЧЁТ вернёт по-прежнему 4 — текст он не считает, — а СУММ и СРЗНАЧ просто проигнорируют эту ячейку. Такая проверка на пальцах занимает полминуты и страхует от перепутанных ответов: МАКС и МИН различаются одной буквой, а на контрольной цена путаницы — целая задача.

Ячейка#

пересечение столбца и строки — минимальное хранилище таблицы

Адрес#

имя ячейки из буквы столбца и номера строки: B3

Диапазон#

прямоугольник ячеек, записывается через двоеточие: A1:C5

Относительная ссылка#

адрес без долларов, меняется при копировании формулы

Абсолютная ссылка#

адрес с двумя долларами, застывает на месте при копировании

Формула#

запись, начинающаяся со знака равенства, по которой таблица вычисляет значение

• СУММ — сумма диапазона: =СУММ(A1:A12)
• СРЗНАЧ — среднее арифметическое диапазона
• МАКС и МИН — наибольшее и наименьшее значения
• СЧЁТ — сколько чисел в диапазоне, текст не считается
• СЧЁТЕСЛИ — сколько ячеек попало под условие
• ЕСЛИ — выбор из двух ответов по условию

ЕСЛИ и СЧЁТЕСЛИ: таблица решает сама

Функция ЕСЛИ проверяет условие и выбирает один из двух ответов. В ячейку пишешь =ЕСЛИ(СРЗНАЧ(B1:B12)>=4; "молодец"; "повтори тему") — и таблица сама печатает вердикт, стоит поменять хоть одну оценку. Условие внутри может быть любым: больше, меньше, равно, а текстовые ответы всегда берут в кавычки. Функция СЧЁТЕСЛИ считает ячейки, попавшие под условие: =СЧЁТЕСЛИ(A1:A12; 5) вернёт количество пятёрок за четверть, а =СЧЁТЕСЛИ(B1:B50; ">60") — число работ выше шестидесяти баллов. С такими функциями таблица превращается из калькулятора в помощника: она не только считает, но и делает выводы.

Соберём маленький проект: семейный бюджет. В столбец A — названия покупок, в B — суммы, в C — категории. Внизу формула =СУММ(B2:B30) покажет общий расход, =МАКС(B2:B30) — самую дорогую покупку, а =СЧЁТЕСЛИ(C2:C30; "транспорт") — сколько раз тратились на проезд. Поменял одну сумму — все формулы пересчитались мгновенно, без калькулятора и ручных правок. Именно так устроены и школьные журналы, и счета в магазинах: одна таблица, десятки формул, ни одной ошибки от усталости.

Диаграммы: строим по правилам

Диаграмма строится за три клика: выделяешь диапазон с данными, открываешь Вставку и выбираешь тип. Дальше начинается главное — подписи. Круговая требует один ряд данных: доли пятёрок, четвёрок и троек за четверть; все доли вместе обязаны дать целое. Гистограмма сравнивает категории: средний балл 7А против 7Б или продажи по месяцам. График показывает изменение во времени: температура за неделю, рост за год. Подписи осей и заголовок ставят почти всегда — без них даже верная диаграмма читается как абстрактная картинка. И следи за легендой: если диаграмма показывает ерунду, проверь выделенный диапазон — чаще всего в него попала лишняя ячейка или пустая строка.

Помимо формул у таблицы есть два инструмента наведения порядка — сортировка и фильтр. Сортировка выстраивает строки по выбранному столбцу: по возрастанию цены или по алфавиту фамилий. Фильтр прячет строки, не подходящие под условие: остаются только пятёрки или только покупки дороже тысячи. Следи за одним: сортировать и фильтровать нужно связанный диапазон целиком, иначе строки разъедутся — название от одной суммы, а фамилия от другой оценки.

Как это спрашивают на ОГЭ

В задании 5 ОГЭ по информатике дают готовую таблицу и просят найти сумму, среднее или количество ячеек под условием, а часто — предсказать, что покажет формула после копирования. Последний вариант — самый частый источник потерь, поэтому разберём его как задачу.

Подсказка: клавиши J — следующая тема, K — предыдущая, на телефоне: свайп влево/вправо — соседняя тема