Урок №25
Встроенные функции для поиска максимума, минимума, суммы и среднего арифметического.
СУММИРОВАНИЕ.
Мощь электронных таблиц связана ещё и с тем, что они содержат большое количество встроенных функций. Функции могут выполнять довольно сложные вычисления и обрабатывать данные сразу целого диапазона ячеек.
Пусть, например, нам нужно подсчитать сумму значений в ячейках Al, А2, АЗ, А4, А5.
Запишите формулу, с помощью которой можно сложить значения этих ячеек.
Формулу, в которой перечисляются все ячейки, очень тяжело использовать для диапазона, состоящего, скажем, из 1000 ячеек. Стандартная функция SUM (в русской версии Excel — СУММ) позволяет сделать то же самое более красиво:
=SUM(A1:A5)
В скобках записан адрес диапазона, включающего все нужные ячейки.
Найдите в дополнительных источниках перевод английского слова sum на русский язык.
Адрес диапазона составляется из двух адресов ячеек, расположенных в левом верхнем и правом нижнем углах диапазона, они разделяются двоеточием. На рисунке выделен диапазон В2:С5.
Выделите в таблице диапазоны А1:С1, А1:А2, А1:В2, В1:В3, А1:С3, В2:С3.
Сколько ячеек входит в диапазоны:
а) А1:А2; б) А1:С1; в) А15:А48; г) B16:G16; д) B3:G5; е) F12:G25?
Запишите формулу, с помощью которой можно найти сумму всех значений в столбце F, которые расположены в строках с 15-й по 37-ю.
Чтобы не ошибиться, при вводе формулы с функцией можно набрать название функции, открыть скобку, а затем мышью выделить нужный диапазон прямо в таблице и закрыть скобку.
Функция SUM умеет складывать данные сразу из несколько диапазонов. Например, нужно найти общую зарплату двух бригад рабочих
Запишите в ячейке В5 формулу, с помощью которой можно найти общую сумму зарплат рабочих обеих бригад.
Для решения этой задачи достаточно одного вызова функции:
=SUM(B2:B3;D2:D4)
Адреса диапазонов, которые нужно использовать, перечисляются через точку с запятой. В режиме редактирования формулы (клавиша F2) можно увидеть, какие диапазоны участвуют в вычислениях (они выделяются цветными рамками).
Вспомните, как можно выделить несколько диапазонов.
Функция SUM складывает данные только из ячеек с числами, на остальные «не обращает внимания». Поэтому в нашей задаче правильный результат можно было получить и с помощью формулы
= SUM(A1:D4)
Какие числа появятся в ячейках с формулами после ввода формул, показанных на рис.
В ячейках диапазона А1:А3 находятся однозначные натуральные числа. В ячейку В1 записали формулу =А1+А2, а в ячейку В2 — формулу =А2+А3. После ввода формул значение в В1 стало равно 16, а значение в В2 равно 15. Какие числа могли быть записаны в ячейки диапазона А1:А3? Найдите все варианты.
После ввода всех формул в ячейке В3 появилось число 16. Определите, какое число было записано в ячейке В1 и какие числа появятся в ячейках А2 и В2 после ввода формул
МИНИМУМ, МАКСИМУМ, СРЕДНЕЕ АРИФМЕТИЧЕСКОЕ.
Для вычисления минимального и максимального значений используются функции MIN и МАХ (в русской версии — МИН и МАКС). При их вызове в скобках можно задать один или несколько диапазонов, например:
=MIN(В2:С4)
=MAX(A1:A20;C1:C20;D8:D34)
Определите значения всех ячеек после ввода формул
Запишите в тетради несколько различных формул, которые при вводе в ячейку D4 выведут тот же результат.
Среднее арифметическое (сумму чисел, разделённую на их количество) вычисляет функция AVERAGE (СРЗНАЧ), например,
=AVERAGE(B2:C4)
=AVERAGE(A1:A20;C1:C20;D8:D34)
Экспериментально выясните, какие ячейки учитывает функция AVERAGE.
Проверьте её работу в трёх случаях, каждый раз вычисляя
среднее арифметическое для диапазона А1:В2
Определите значения всех ячеек после ввода формул
Запишите в тетради несколько различных формул, которые при вводе в ячейку С3 выведут тот же результат.
Предположим, что нам нужно вычислить среднее арифметическое значений, записанных в два диапазона, А1:А2 и C1:D2
Выясните, можно ли найти сначала среднее арифметическое для каждого диапазона, а затем — среднее арифметическое из двух полученных средних:
1) вычислите в ячейке А3 среднее арифметическое для диапазона А1:А2;
2) вычислите в ячейке С3 среднее арифметическое для диапазона C1:D2;
3) вычислите в ячейке ВЗ среднее арифметическое для диапазона А3:С3 («среднее из средних»);
4) вычислите в ячейке В4 среднее арифметическое для диапазона A1:D2 (правильное значение среднего арифметического).
Сравните значения в ячейках В3 и В4, сделайте выводы.
ДРУГИЕ ФУНКЦИИ.
Мы изучим ещё одну функцию — SUMPRODUCT (СУММПРОИЗВ), которая вычисляет сумму произведений ячеек двух диапазонов.
Предположим, что нам нужно построить электронную таблицу для расчёта стоимости покупки в магазине
Запишите в тетради формулу, которую нужно ввести в ячейку С5.
Такие формулы очень тяжело вводить, когда в таблице несколько сотен (или даже тысяч!) строк. Обратите внимание, что в этой формуле участвуют ячейки двух диапазонов: В2:В4 и С2:С4. Сначала первая ячейка первого диапазона умножается на первую ячейку второго диапазона, вторая — на вторую и т. д., а затем все эти произведения складываются. Функция SUMPRODUCT делает всё это сразу, поэтому в С5 можно записать формулу
=SUMPRODUCT(B2: В4; С2: С4)
Эта функция принимает два аргумента — диапазоны одинаковой длины.
Электронные таблицы содержат очень много встроенных функций, которые разбиты на группы: математические, логические, текстовые и др. Более сложные функции, которые применяются при обработке больших массивов данных, мы будем изучать в следующем году.
