Если вы ищете работу и она хоть как-то связана с математикой/экономикой/финансами, то вы очень часто будете встречать такие требования к кандидату:
— Отличное знание статистики;
— Знание и умение Python/R, чтобы эту статистику применять.
Но что делать, если никаких знаний по языкам программирования у вас нет, а встречаться со статистическими моделями так или иначе придется? А работу-то найти нужно срочно…
К счастью, в версии Microsoft Excel выше 2010 вшит целый статистический пакет. О нем мало кто знает, а его реально можно использовать, если нет навыков программирования или доступного компилятора под рукой.
Для начала поговорим, где все эти формулы найти. Как обычно, переходим на вкладку «Формулы» на главной панели, выбираем «Другие функции» и пакет «Статистические». Перед Вами полный перечень статистических возможностей Excel.
Как вы можете видеть, формул в этом разделе представлено довольно много, как «ходовых», так и «узкоспециализированных». К сожалению, разобрать все в одной статье не получится, поэтому рассмотрим здесь самые часто встречающиеся. Если вы хотите познакомиться с математическими функциями Excel, то рекомендуем скачать наш бесплатный гайд.
На первый взгляд они могут показаться очень простыми. Однако, мы постарались раскрыть те моменты, которые обычно остаются без внимания и могут быть полезными.
СРЗНАЧ() и СРЗНАЧА()
Редко кто задумывался, а ведь вычисление среднего значения – сугубо статистическая процедура: именно поэтому это операция и помещена в статистический пакет.
Наверно, особо не стоит останавливаться на правилах использования формулы: функция СРЗНАЧ() принимает на вход массив аргументов и дает на выходе среднее значение по всем ячейкам, содержащим числа(!). Это очень важный момент, который далеко не все знают. Поясним на примере.
Пусть дан диапазон А1:С2 и мы ищем среднее значение по всем 6 ячейкам диапазона:
Однако, результат функции СРЗНАЧ(А1:С2) будет не 8,7, а 13. Почему? (4+15+11+22)/6 = 8,7 ведь?
Да, это правильно, но функция СРЗНАЧ() берет в расчет только те ячейки, где «встречает» числа. Текстовая информация и пустые ячейки просто игнорируются. Поэтому в данном примере СРЗНАЧ() усредняет по 4 ячейкам и выдает правильный ответ – 13.
А вот если нужно произвести усреднение по всему диапазону, вне зависимости от типа данных, нужно использовать функцию СРЗНАЧА().
Принцип работы такой же, как и у СРЗНАЧ(), только на вход будут поступать абсолютно все ячейки. Результат в нашем примере будет уже ожидаемый – 8,7.
Замечание
Выбор той или иной функции происходит в зависимости от задачи. В реальной жизни они могут понадобится в одинаковой мере.
Например, менеджеру нужно узнать среднедневную выручку за месяц на основании продаж за каждый день. Допустим, за несколько дней ячейки оставлены пустыми. Есть два варианта, почему так произошло:
1. В эти дни не было ни одной продажи. Тогда эти дни должны принимать участие в расчете среднего значения и менеджеру нужно использовать СРЗНАЧА() – так он исключит игнорирование пустых ячеек.
2. Эти дни были выходными. Тогда пропуски сами по себе никакой информации не несут и их надо игнорировать: фактически, эти дни не принимают участие в статистической выборке и функция СРЗНАЧ() поможет их пропустить.
СРЗНАЧЕСЛИ()
Очевидно, что функция СРЗНАЧЕСЛИ() возвращает среднее тех значений, который удовлетворяют каким-то условиям. Помимо этого, условия можно накладывать не только на сами значения, но и на другие ячейки. Проиллюстрируем.
Например, вычислим среднее значение всех ячеек, которые больше нуля:
Мы выделили диапазон А1:С3 и наложили на него условие – «>0». А можно сделать по-другому.
Рассмотрим таблицу, в которую занесены продажи лекарств в городе. Посчитаем среднюю цену Анальгина по всему городу. Для этого наложим условие уже не на саму цену, а на название лекарства.
Формула записывается так:
=СРЗНАЧЕСЛИ(Диапазон_на_который_накладываем_условия; “Условие”; Диапазон_по_которому_считаем_среднее_значение)
В нашем случае это примет вид:
Кстати говоря, условия можно комбинировать с помощью функции СРЗНАЧЕСЛИМН().
Предположим, что в аптеке Зеленый Крест продается несколько видов Анальгина и в нашу таблицу они все занесены как Анальгин.
Тогда, чтобы усреднить цену всех Анальгинов в аптеке Зеленый Крест, нужно просто использовать формулу:
=СРЗНАЧЕСЛИМН(С2:С13; A2:A13; “зеленый крест”;B2:B13; “анальгин”)
Обратите внимание: диапазон усреднения указывается в конце только при использовании функции СРЗНАЧЕСЛИ() с дополнительным условием. В остальных случаях диапазон ячеек, по которым вычисляется среднее значение, стоит первым.
МИН()/МАКС() и НАИБОЛЬШИЙ()/НАИМЕНЬШИЙ()
На первый взгляд, разница между этими функциями не особо прослеживается, хотя зачем их используют – очевидно – найти самое большое или маленькое число. Однако, в работе этих функций есть небольшая, но очень полезная разница. Разберем подробней.
Функция МИН() просто принимает массив аргументов и находит самое маленькое число. МАКС() – самое большое. Все просто.
Функция НАИМЕНЬШИЙ() же находит n-ое наименьшее число в массиве. НАИБОЛЬШИЙ(), наоборот, находит n-ое наибольшее число.
Например, нужно найти пятое по величине число. Вводим:
=НАИБОЛЬШИЙ(диапазон; 5).
Фактически, получается, что результат работы НАИБОЛЬШИЙ(массив;1) и МАКС(массив) – одно и то же. Аналогичная ситуация с НАИМЕНЬШИЙ(массив;1) и МИН(массив).
Рекомендуем записаться на наш открытый онлайн-курс «Аналитика в Excel», если вы хотите научиться выполнять рутинную работу быстрее.
МЕДИАНА() и МОДА()
Общеизвестные и достаточно важные статистические характеристики моды и медианы вычисляются по одноименным формулам.
Напомним, что медианой называется «середина» числового множества.
Например, если есть массив чисел от одного до десяти, то медианой будет число 5,5 (хотя оно само в массив не входит). Это из-за того, что количество элементов в массиве – четно и выбрать «центральное» просто невозможно.
Вот если бы выборка начиналась не с единицы, а с двойки, то ответ был бы ровно 6.
Теперь перейдем к моде. Мода – самое часто встречающееся число в выборке.
У функции нахождения моды есть целых три модификации в Excel старшее версии 2010 года: МОДА(), МОДА.ОДН() и МОДА.НСК().
Функция МОДА() оставлена для совместимости – ей, в целом, можно пользоваться: она работает совершенно аналогично функции МОДА.ОДН().
«ОДН» в названии функции значит, что, если в выборке несколько самых часто встречающихся элементов, то возвращено в качестве ответа будет только первое.
Для подсчета всех мод в выборке нужно использовать функцию МОДА.НСК().
Работает МОДА.НСК() следующим образом: выделяем побольше ячеек (если заранее не знаем, сколько мод у нас получится), в строке формул прописываем =МОДА.НСК(диапазон) и нажимаем Ctrl+Shift+Enter. Получили все моды в столбик.
Значения #Н/Д появляются, просто потому что мод у нас всего 2. Такой метод поиска мод называется «слепым» – мы просто берем побольше ячеек, чтобы наверняка хватило.
Если Вы не любите подобный «мусор» и Вам нравится, когда все красиво, можно сначала оценить: а сколько же у нас вообще будет мод? А потом просто выделить нужное количество ячеек.
Делается это так: сначала применяем функцию СЧЁТ() к нашей МОДА.НСК() – получили количество мод. А теперь выделяем только две ячейки и делаем все также, как написано выше.
Заключение
Статистический пакет Microsoft Excel содержит в себе еще огромное количество формул: проверку гипотез, принадлежность распределениям, доверительные интервалы, корреляцию и прочие инструменты, которые могут пригодиться при работе со статистикой даже на серьезном уровне.
Как мы и писали ранее, обозреть все в одной статье невозможно. Поэтому, если вы хотите узнать про менее известные, но не менее полезные статистические возможности Excel – пишите в комментариях, что вам было бы интересно и мы подготовим для вас новую статью из этого цикла.
Автор: Андрон Алексанян, СОО “Аптека-Центр”, эксперт SF Education