Основные функции для аналитика в Excel

Есть много людей, которым приходится работать с данными. А Excel — одна из лучших программ для эффективного анализа информации. Сегодня мы рассмотрим 13 функций, которые каждый должен знать, прежде чем начать использовать эту программу. Настоятельно рекомендуется попрактиковаться, прежде чем применять полученные знания в реальной жизни.

13 функций для аналитика в Excel

Независимо от того, в какой сфере вы работаете — продажа продукта, составление финансовой отчетности или составление ежеквартального маркетингового отчета — вам необходимо четко понимать, с помощью каких инструментов будет осуществляться этот бизнес. Вот самые популярные функции, которые могут быть полезны в разных сферах. Конечно, их намного больше, но это 20%, что дает 80% результата. Начнем с общих математических функций, а затем перейдем к более продвинутым, более близким к программированию.

СУММ функция

Начнем наш хит-парад с простейшей функции, которая складывает числа из определенного диапазона данных. Это называется СУММ. У него есть только один обязательный аргумент, но теоретически можно использовать гораздо больше параметров.

Каждый аргумент представляет собой массив данных, которые будут суммироваться, а также отдельные ячейки и числа, которые можно складывать друг с другом. Используя этот оператор, вы можете, например, рассчитать общий объем продаж товаров в таблице или общий доход бюджета страны за определенный период. В общем, синтаксис функции следующий: = СУММ (Массив 1, Массив 2…..)

ПРОИЗВЕД функция

Синтаксис этой функции такой же, как и для SUM, но умножает несколько значений, переданных в качестве аргументов. Как и в предыдущей функции, вы можете использовать в качестве параметров следующие типы данных: диапазон, число, ячейку с числовым значением. В общем, синтаксис этой функции следующий: = СУММ (Массив 1, Массив 2…..)

Практические примеры использования этой функции: анализ данных бухгалтерского учета, маркетинговые расчеты. Надо сказать, что сама по себе эта функция используется не так часто, как СУММ. Как правило, он идет рука об руку с другими формулами. Например, вы можете получить ряд данных, которые затем умножаете друг на друга.

Формулы также можно использовать в качестве аргументов для обеих функций.

ЕСЛИ функция

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

1. Выражение. Здесь написано условие. Например, ячейка A1 должна быть больше или равна единице.
2. Результат, если правда. Этот аргумент указывает, что будет выполнено, если выражение истинно. То есть простым языком, если критерий подтвердится. Вы также можете добавить сюда другую формулу, которая будет выполняться, и даже указать повторяющуюся функцию ЕСЛИ для выполнения другой проверки.
3. Результат, если ложь. Типы данных, которые можно использовать в этом разделе, такие же. Точно так же вы можете запрограммировать эту функцию на выполнение другого действия в случае, если выражение окажется ложным.

В результате использования этой функции можно получить что-то вроде такого дерева. Говоря простым языком, эта функция нужна для задания алгоритмов вычислений или действий.

И для наглядности приведем пример формулы, запускающей последовательность проверок, которая показана на этой диаграмме. = SE (A22 = 1; SE (A23

Кстати, вы можете автоматизировать анализ данных, используя полное программирование. Такая программа называется макросом, только запускается она не из ячейки, а через специальное меню.

СРЗНАЧ функция

Используя эту функцию, вы можете получить среднее арифметическое значений, введенных в скобки. Аргументы такие же, как для функции СУММ. Поддерживаемые типы данных — числа, диапазоны значений, ячейки числового формата и другие функции, возвращающие числовое значение. В общем, синтаксис функции следующий: = СРЕДНЕЕ (массив 1; массив 2).

Эта функция очень активно используется для статистических расчетов. Он особенно популярен для анализа данных в социономических науках, таких как психология, социология и другие.

СУММПРОИЗВ функция

Эта функция, как следует из названия, представляет собой комбинацию функций СУММ и ПРОДУКТ. Сначала он умножает значения, содержащиеся в аргументах, затем получает сумму полученных значений. Например, у нас есть таблица с тремя наборами данных.

Чтобы получить продукты, нужно умножить значения в каждом из столбцов. Затем сложите полученные значения. Это занимает много времени, поэтому использовать функцию СУММПРОИЗВ гораздо проще.

В этом случае достаточно указать массивы в качестве аргументов, разделенных точкой с запятой. Все остальное программа сделает за человека.

МАКС функция

С помощью этой функции вы можете получить максимальное значение из набора данных. Часто также используется в статистике. Также он востребован в кругах трейдеров, которым приходится анализировать большие объемы цифровых данных и определять максимальные и минимальные точки. Или проанализировать эффективность любой другой деятельности. Например, если у человека есть интернет-магазин, и он хочет определить, в каком месяце выручка была наибольшей. Для этого необязательно просматривать огромное количество данных, достаточно использовать соответствующую функцию.

Синтаксис очень прост: в скобках нужно указать числа, диапазоны или функции, возвращающие числа, между которыми вы затем найдете наибольшее значение.

МИН функция

Его структура такая же, как и у почти всех других статистических операторов. Однако он выполняет другую функцию: вычисляет наименьшее значение в диапазоне. Чаще всего функции MIN и MAX используются парами. Таким образом, в одной и той же торговле они необходимы, чтобы найти точку входа и точку продажи акций или криптовалют.

Конечно, теперь эту задачу можно выполнить с помощью других онлайн-программ, но когда дело доходит до позиционной торговли (то есть долгосрочной торговли), по-прежнему активно используется стандартная электронная таблица Excel. Эти две функции являются гарантом безопасности трейдера, поскольку позволяют вовремя войти на рынок, чтобы быстро заработать деньги, и вовремя выйти из него, когда цена пробьет один из предыдущих максимумов.

Конечно, эти функции также имеют некоторые ограничения при торговле на финансовых рынках, но это совершенно другая тема для обсуждения.

НАИБОЛЬШИЙ функция

На первый взгляд может показаться, что эта функция делает то же самое, что и оператор MAX. Но это не так. Используя функцию НАИБОЛЬШИЙ, вы можете вернуть наименьшее желаемое число после максимального. Синтаксис немного сложен для понимания. Содержит следующие темы:

1. Вектор. Диапазон значений, используемых этой функцией.
2. Коэффициент. Это число сбоку от наибольшего, от которого вы хотите отсчитать значение.

Если вы введете число k — один, эта функция будет вести себя точно так же, как оператор MAX. Но если вы введете другое значение, оператор определит второе, третье и т.д. Значение по величине.

Для наглядности возьмем небольшой пример. Допустим, у нас есть ряд чисел 1; 3; 6; 5; 10. Если мы установим коэффициент равным 1, значение, возвращаемое функцией, будет аналогично значению, возвращаемому MAX — 10. Если число k установлено на уровне 2, функция вернет значение 6, если число k равно равно трем, то вернет значение 5 и так далее.

НАИМЕНЬШИЙ функция

Эта функция работает точно так же, как LARGE, но начинает отсчет с наименьшего значения. Подобно предыдущему оператору, когда значение числа k равно единице, возвращаемое значение идентично функции MIN. С каждым последующим увеличением этого коэффициента минимальное значение становится все больше и больше.

Поэтому второй по стоимости продукт на рынке можно рассматривать как один из возможных вариантов использования этой функции. Или найдите третий фонд на биржевой диаграмме.

ВПР функция

Еще одна очень продвинутая функция, позволяющая искать необходимое значение в ситуациях, когда человек не знает, где именно в таблице оно может содержаться. Эта функция просматривает диапазон по горизонтали и имеет следующие аргументы:

1. Ценность, которую вы ищете. Это строка или число, которое нужно найти.
2. Стол. В этом разделе вам нужно указать диапазон значений, которые можно анализировать.
3. Номер столбца.
4. Интервальный просмотр. Это логическое значение, которое может быть нулем или единицей. Если значение этого аргумента равно нулю, совпадение должно быть точным, чтобы функция работала. Если значение равно единице, точное значение не требуется.

А теперь приведем пример использования этой функции. Допустим, у нас есть таблица с такими данными.

Исходя из условий нашей задачи, нам необходимо найти:

1. Сколько лет сотруднику Иванову.
2. Какой вуз окончил сотрудник Петров.

Пример формул, которые могут решить каждую из этих проблем:

1. = ВПР (A6; A1: C4; 3; 0). Эта формула ищет первый столбец таблицы и возвращает значение в третьем столбце, соответствующее найденному значению. В результате мы узнаем, что Иванову 22 года.
2. = ВПР (A6; A1: C4; 2; 0). Эта формула проанализирует первый столбец, чтобы узнать, есть ли значение для «Петрова». Если да, поищите, какое значение во втором столбце ему соответствует. В результате мы получили значение HSE.

У функции ВПР есть аналог, который выполняет горизонтальный поиск — ВПР. Синтаксис у него такой же, только принцип работы немного отличается.

ИНДЕКС функция

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

Предположим, нас интересует фамилия человека, которая находится на пересечении третьей строки и первого столбца. Следовательно, для этого мы должны написать такую ​​формулу: = ИНДЕКС (A1; C4; 3; 1).

СУММЕСЛИ функция

Эта функция, как следует из названия, объединяет функции СУММ и ЕСЛИ. То есть, при соблюдении определенного требования, эта функция суммирует определенные значения. Синтаксис оператора включает три обязательных аргумента:

1. Диапазон критерия. Тема обязательна. Это набор данных, который будет проверяться на соответствие определенному условию.
2. Критерий. Это условие, при котором выполняется набор данных, указанный в первом аргументе.
3. Диапазон суммирования. Этот аргумент является необязательным, но является обязательным, если вы указываете диапазон текста в первом аргументе.

СУММЕСЛИМН функция

Эта функция аналогична предыдущей, которая может проверять интервал на соответствие нескольким критериям одновременно. Итак, его синтаксис также немного отличается:

1. Диапазон суммирования.
2. Диапазон критериев 1.
3. Критерий 1.
4. Диапазон критериев 2.
5. Критерий 2.

И т.п. Вы можете указать множество критериев, но на практике достаточно 3-5. Как видите, есть некоторые особенности. Но на самом деле их даже больше. Но сначала нужно их освоить.