Функции по диапазону

Итог таблицы покупок в уроке 2.1 считали длинной формулой =D2+D3+D4+D5. Для четырёх строк сойдёт, для пятисот — нет. Функция — готовая операция с именем: она получает в скобках данные, чаще всего диапазон, и возвращает одно значение.

Разберём, как записать функцию, и главные функции по диапазону: сумму, количество, среднее и крайние значения.

Имя функции, скобки, в скобках аргументы через точку с запятой; двоеточие соединяет границы одного диапазона. Вместо =D2+D3+D4+D5 пишут =СУММ(D2:D5): СУММ — имя, D2:D5 — диапазон, который нужно сложить.

Начни набирать =СУ — Excel покажет список функций с таким началом, нужную выбирают клавишей Tab. Сумму столбца ставит и кнопка «Автосумма» Σ на вкладке «Главная» или клавиши Alt+=: Excel сам предложит диапазон над ячейкой — проверь его, прежде чем нажать Enter.

Данные в скобках называются аргументами. В =ОКРУГЛ(A2;2) два аргумента: округляемое число и количество знаков после запятой. Точка с запятой разделяет аргументы, двоеточие соединяет границы диапазона — это разные знаки.

Функция возвращает обычное значение, поэтому её можно вставить в формулу: =СУММ(A2:F2)-МАКС(A2:F2) — сумма строки без наибольшего числа. Для строки 4, 4, 1, 2, 3, 5:

СУММ складывает числа диапазона, СЧЁТ считает ячейки с числами, СЧЁТЗ — непустые, СЧИТАТЬПУСТОТЫ — пустые. В A2:A5 лежат 10, 20, текст «нет» и пустая ячейка — каждая функция берёт из них свои:

Впиши 0 в A5: сумма останется 30, а СЧЁТ станет 3 и СЧИТАТЬПУСТОТЫ — 0. Одинаковая сумма не значит одинаковые данные: ноль — число, пустота — нет.

СРЗНАЧ делит сумму чисел диапазона на их количество: текст и пустые ячейки не участвуют, нули участвуют. Для 4, 7, 10 сумма 21, чисел три, и =СРЗНАЧ(A2:C2) даёт 7. Если чисел нет совсем, делить не на что — функция выдаёт #ДЕЛ/0!.

Строки 3 и 4 различаются только нулём и пустотой в C — и дают 10 и 15, как в уроке 1.3.

Среднее средних не равно общему среднему, если группы разного размера. В 10А два ученика со средним баллом 80, в 10Б восемь со средним 60. Общий средний балл — сумма всех баллов на число всех учеников: (80 × 2 + 60 × 8) / (2 + 8) = 64. Простое (80 + 60) / 2 = 70 даёт маленькому и большому классу одинаковый вес.

Сумма баллов класса — его средний балл, умноженный на число учеников; так устроена формула в B5.

МАКС и МИН дают наибольшее и наименьшее число, НАИБОЛЬШИЙ и НАИМЕНЬШИЙ — k-е по порядку с учётом повторений. Для 3, 8, 8, 2 максимум 8; сколько раз он встречается, МАКС не сообщает — это отдельный подсчёт.

=НАИБОЛЬШИЙ(A2:D2;2) — второе число по убыванию. Для 3, 8, 8, 2 это снова 8: второе место в упорядоченном ряду, а не второе различное значение. НАИМЕНЬШИЙ считает места по возрастанию.

Сумма двух наибольших с учётом повторений — НАИБОЛЬШИЙ(…;1) + НАИБОЛЬШИЙ(…;2) = 16. Если условие требует два разных значения, этого мало — нужен отбор, который исключает повтор максимума.

Ещё одна функция по диапазону — СУММКВ: складывает квадраты чисел. =СУММКВ(A2:C2) для 3, 4 и 12 даёт 9 + 16 + 144 = 169 — то же, что =A2^2+B2^2+C2^2, но короче для длинного диапазона.

Одна и та же функция отвечает на разные вопросы в зависимости от диапазона: строка — это одна запись, столбец — все записи. =СУММ(A2:F2) складывает шесть чисел одной строки, =СУММ(A2:A100) — один столбец многих записей.

Перед вводом формулы проговори диапазон словами: «складываю шесть чисел этой строки» или «складываю стоимости всех покупок». Так ловится самая частая ошибка — вычисление не по той оси таблицы.

Функцию по строке копируют вниз, как любую формулу: =СУММ(A2:F2) в G2 после заполнения вниз станет =СУММ(A3:F3) в G3 — сумма своей строки.