III. Статистические функции

В Excel имеется категория статистических функций. Статистические функции (всего их около 80) позволяют выполнять анализ данных. Среди них много достаточно сложных функций, для применения которых требуется знать хотя бы основы теории вероятностей. Мы же рассмотрим наиболее простые статистические функции, которые часто используются и для своего применения не требуют специальных знаний из области математики.

МАКС – возвращает максимальное числовое значение из списка аргументов.

МИН – возвращает минимальное числовое значение из списка аргументов.

СРЗНАЧ – возвращает среднее арифметическое значение заданных аргументов.

СРГЕОМ - возвращает среднее геометрическое значение заданных аргументов (среднее геометрическое для n чисел вычисляется по формуле Q= )

НАИБОЛЬШИЙ – находит наибольшее n-е значение элемента в массиве данных. Например, = НАИБОЛЬШИЙ(А1:А7; 2) – находит второй наибольший элемент в массиве чисел А1:А7.

НАИМЕНЬШИЙ – находит наименьшее n-e значение элемента в массиве данных. Например, = НАИМЕНЬШИЙ (В4:В12; 4) – находит четвертый наименьший элемент в массиве чисел В4:В12.

РАНГ – возвращает позицию элемента в списке по его значению, относительно значений других элементов.

МАКСА – вычисляет наибольшее из значений, заданное в списке аргументов, включая логические значения и текст.

МИНА - вычисляет наименьшее из значений, заданное в списке аргументов, включая логические значения и текст.

СРЗНАЧА - вычисляет среднее арифметическое из значений, заданное в списке аргументов, включая логические значения и текст

СРЗНАЧЕСЛИ – вычисляет среднее арифметическое значение для ячеек, удовлетворяющих заданному условию. Например, =СРЗНАЧЕСЛИ (А1:А5; «>0») – находит среднее значение положительных чисел, находящихся в диапазоне ячеек А1:А5.

СЧЁТ – подсчитывает количество числовых значений в диапазоне.

СЧЁТЕСЛИ – подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию.

IV. Логические функции

Логические функции используются в случае, когда необходимо выполнить те или иные действия в зависимости от выполнения каких-либо условий.

В Excel имеются следующие основные логические функции: ЕСЛИ, И, ИЛИ, НЕ.

Функция ЕСЛИ (логич. выражение; действие_когда_истина; действие_когда_ложь).

Например: ЕСЛИ (А1> 10; A1+B1; A1*B1)

Если число в ячейке А1>10, вычисляется значение выражения А1+В1. Если число в ячейке А1≤10, вычисляется значение выражения A1*B1.

Пример 1. Дан следующий фрагмент таблицы Excel:

В ячейке С1 набрана формула: = ЕСЛИ (А1> 10; A1+B1; A1*B1)

После нажатия клавиши Enter получим следующий результат:

В ячейке С1 получим число 17, т.к. в ячейке А1 находится число 12, которое больше 10 (условие A1>10 выполняется), поэтому Excel будет выполнять расчёт по первой формуле: А1+В1.

Пример 2. Дан следующий фрагмент таблицы Excel:

В ячейке С1 набрана формула: = ЕСЛИ (А1> 10; A1+B1; A1*B1)

После нажатия клавиши Enter получим следующий результат:

В ячейке С1 получим число 40, т.к. в ячейке А1 находится число 8, которое меньше 10 (условие А1>10 не выполняется), поэтому Excel будет выполнять расчёт по второй формуле: А1*В1.

В качестве вторых и третьих аргументов функции ЕСЛИ может быть не только формула, но и обычный текст. Например: ЕСЛИ (D1> 5; «УРА!»; «Жаль!»). В данном случае, если в ячейке D1 будет находиться число, большее 5, Excel напишет слово «УРА!»; если же в ячейке D1 будет находиться число меньшее 5, либо равное 5, Excel напишет текст: «Жаль!».

Как правило, набор логической функции ЕСЛИ не проводится вручную, т.к. это довольно утомительно и требует внимательности. Чаще используют мастер функций (вызывается кнопкой ). При выборе функции ЕСЛИ (из категории логические функции) появляется примерно такая картинка:

Рис.1. Окно задания аргументов функции ЕСЛИ

В первом окошке (Лог_выражение) записывается соответственно первый аргумент функции (это логическое выражение, которое обычно содержит проверку какого-то условия, например, A1>10).

Во втором окошке (Значение_если_истина) записывается второй аргумент функции: что делать машине, если условие, заданное в первом окошке, выполняется.

В третьем окошке (Значение_если_ложь) задаётся третий аргумент функции: что делать машине, если условие, заданное в первом окошке, не выполняется.

Пример заполненного окна «Аргументы функции» для функции ЕСЛИ:

Рис. 2. Окно задания аргументов функции ЕСЛИ с введенными аргументами

Функции И, ИЛИ, НЕ часто используются в качестве аргументов функции ЕСЛИ.

НЕ(арг) – изменяет значение аргумента на противоположное. Она часто используется в тех случаях, когда необходима уверенность в том, что значение не равно некоторой величине.

Пример: =ЕСЛИ(НЕ(F1=0); “Всё нормально”; “Плохо”) – во всех случаях пишется «Все нормально», кроме случая, когда в ячейке F1 записан 0.

И(арг1, арг2, арг3…) – возвращает значение Истина, если все аргументы имеют значение Истина. Если хотя бы один аргумент имеет значение Ложь, то функция И возвращает значение Ложь.

Пример: =ЕСЛИ(И(A1>10; В1>5); A1+B1; A1*B1)

ИЛИ (арг1; арг2; арг3…) – возвращает значение Истина, если хотя бы один аргумент имеет значение Истина. Только если все аргументы имеют значение Ложь, функция ИЛИ также возвращает значение Ложь.

Пример: =ЕСЛИ(ИЛИ(A1>10; В1>5); A1+B1; A1*B1)

Контрольные вопросы:

1. Что называется аргументом и результатом функции в Excel?

2. В табличном процессоре Excel набрана функция: =КОРЕНЬ(16). Поясните, где здесь имя функции, аргумент функции и каков будет результат этой функции:

3. Какие категории функций в программе Excel вы знаете?

4. Приведите примеры математических функций Excel.

5. Необходимо найти sin75°. Как правильно набрать функцию в Excel для вычисления этого выражения?

6. Для чего предназначены статистические функции?

7. Какие статистические функции чаще всего встречаются на практике?

8. В чём разница между функциями МАКС и НАИБОЛЬШИЙ?

9. Дан фрагмент таблицы Excel:

Какие ответы мы получим в ячейке D3, если в ней набрать формулы:

а) МАКС (А3:С3) -? б) МИН (А3:С3) -? В) СРЗНАЧ (А3:С3) -?

10. В чём основное назначение логических функций (кратко)?

11. Назовите основные логические функции программы Excel/

12. Дан фрагмент таблицы Excel:

Какой результат получится в ячейке С1 после нажатия клавиши Enter?

Какой результат получится в ячейке С1, если в ячейку А1 вместо числа 5 вбить число 10?

13. Дан фрагмент таблицы Excel:

Какой результат получится в ячейке D1 после нажатия клавиши Enter?

Какой результат получится в ячейке D1, если в ячейку B1 вместо числа 4 вбить число 5?