Материал: ЭКСЕЛЬ2 2013 Методичка Расширенные возможности

17

Microsoft Excel 2013. Уровень 2. Расширенные возможности

СРЗНАЧ(Число1;Число2;) – определение среднего арифметического своих аргументов, которые могут быть числами, именами или ссылками на ячейки с числами.

AVERAGE(Number1;Number2;)

=СРЗНАЧ(120;50;100) 90

СЧЁТ(Значение1;Значение2;) – подсчитывает количество ячеек в диапазоне, которые содержат

числа.

COUNT(Value1;Value2;)

=СЧЁТ(70;50;100;«масса») 3

СЧЁТЗ(Значение1;Значение2;) – подсчитывает количество непустых ячеек в указанном

диапазоне.

COUNTA(Value1;Value2;)

=СЧЁТЗ(70;50;100;«масса») 4

СЧИСТАТЬПУСТОТЫ(Диапазон) – подсчитывает количество пустых ячеек в указанном

диапазоне.

COUNTBLANK(Range)

ПРАКТИКУМ:

Открыть файл 01_3 Статистические функции.

На листе ЗАДАНИЕ1 выполнить задание.

Сохранить сделанные изменения в файле.

СЧЁТЕСЛИ(Диапазон;Критерий) – подсчитывает количество ячеек в диапазоне,

удовлетворяющих заданному условию.

COUNTIF(Range; Criteria)

Диапазон [Range] – диапазон ячеек, где ответ на критерий (условие).

Критерий [Criteria] – критерий отбора (условие).

18

Microsoft Excel 2013. Уровень 2. Расширенные возможности

ПРИМЕР: Рассчитать сколько было продаж Киви.

=СЧЁТЕСЛИ(A:A;"Киви") – количество ячеек в столбце A, содержащих слово Киви.

=СЧЁТЕСЛИ(D:D;">5.5.2012") – количество ячеек в столбце D с датой продажи после 5.5.2012.

Функция СЧЁТЕСЛИ подсчитывает количество ячеек только при выполнении одного критерия, если критериев несколько, то нужно использовать функцию СЧЁТЕСЛИМН. По сути дела, функция СЧЁТЕСЛИМН позволяет подсчитать количество строк в таблице, где одновременно выполняется несколько условий.

СЧЁТЕСЛИМН(Диапазон_условия1;Условие1;) – подсчитывает количество ячеек в диапазоне,

удовлетворяющих заданному набору условий.

COUNTIFS(Criteria_range1;Criteria1;)

Диапазон_условия [Criteria_range] – диапазон ячеек, где ответ на условие (критерий).

Условие [Criteria] – условие отбора (критерий).

ПРИМЕР: Рассчитать количество продаж Киви, до 5 мая 2012 г.

19

Microsoft Excel 2013. Уровень 2. Расширенные возможности

=СЧЁТЕСЛИМН(A:A;"Киви";D:D;"<5.5.2012") – подсчитывает количество записей в таблице, если в соответствующей ячейке столбца A содержится слово Киви, а в соответствующей ячейке столбца D содержится дата ранее 5 мая 2012 г.

СРЗНАЧЕСЛИ(Диапазон;Условие;Диапазон_усреднения) – подсчитывает среднее арифметическое для ячеек, удовлетворяющих заданному условию.

AVERAGEIF(Range;Criteria;Average_range)

Диапазон [Range] – диапазон ячеек, где ответ на условие (критерий).

Условие [Criteria] – условие отбора (критерий).

Диапазон_усреднения [Average_range] – диапазон ячеек для расчета среднего значения. Если Диапазон_усреднения не указан, то будут использоваться ячейки, указанные в поле Диапазон.

ПРИМЕР: Рассчитать средний объем продаж (кг) Киви.

=СРЗНАЧЕСЛИ(A:A;"Киви";B:B) – рассчитывает среднее значение по ячейкам столбца B, если соответствующие им ячейки столбца A содержат слово Киви.

СРЗНАЧЕСЛИМН(Диапазон_усреднения;Диапазон_условия1;Условие1;) – подсчитывает среднее арифметическое для ячеек, удовлетворяющих заданному набору условий.

AVERAGEIFS(Average_range;Criteria_range1;Criteria1;)

Диапазон_усреднения [Average_range] – диапазон ячеек для расчета среднего значения.

Диапазон_условия [Criteria_range] – диапазон ячеек, где ответ на условие (критерий).

Условие [Criteria] – условие отбора (критерий).

20

Microsoft Excel 2013. Уровень 2. Расширенные возможности

ПРИМЕР: Рассчитать среднее значение объема продаж (кг) Киви до 5 мая 2012 г.

=СРЗНАЧЕСЛИМН(B:B;A:A;"киви";D:D;"<5-5-12") – рассчитывает среднее значение по ячейкам столбца B, если соответствующие им ячейки столбца A содержат слово Киви, а соответствующие им ячейки в столбце D дату до 5 мая 2012 г.

ПРАКТИКУМ:

Открыть файл 01_3 Статистические функции.

На листе ЗАДАНИЕ2 и ЗАДАНИЕ3 выполнить задание.

Сохранить сделанные изменения в файле и закрыть его.

САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ

Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листе 3-1 и 3-2.

Функции ссылок и подстановки

При работе с большими таблицами для быстрого получения отдельных записей из этих списков можно использовать функции подстановок. Функции поиска используются для поиска связанных записей в таблицах. При использовании таких функций задача формулируется следующим образом – есть значение, для которого нужно найти совпадение в другой таблице и получить в ответ значение, которое хранится в ячейке, соответствующей строки или столбца этой другой таблицы. Основное применение этих функций – это подставлять данные, осуществлять сравнение двух таблиц.

Рассмотрим функции: ВПР, ГПР, ПОИСКПОЗ и ИНДЕКС. Их использование зависит от расположения исходных данных в таблицах, из которых осуществляется подстановка.

В случае если данные хранятся в столбцах:

Например, если известен Код страны-изготовителя (критерий) и необходимо из таблицы получить данные столбца Компания-Изготовитель, которые находятся правее критерия, то удобней воспользоваться функцией ВПР (применяется для вертикальных таблиц).