10 основных функций Microsoft Excel для анализа данных

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

Итак, если вы изо всех сил пытались найти подходящую функцию Excel для анализа данных, то вы попали в нужное место. Вот список некоторых основных функций Microsoft Excel, которые вы можете использовать для анализа данных и повысить свою производительность в этом процессе.

Примечание. Для непосвященных: функции, упомянутые в этом посте, необходимо добавить в строку формул электронной таблицы Excel, содержащей данные, или в ячейку, в которой вы хотите получить результат.

1. СЦЕПИТЬ

=СЦЕПИТЬ — одна из наиболее важных функций для анализа данных, поскольку она позволяет объединять текст, числа, даты и т. д. из нескольких ячеек в одну. Эта функция особенно полезна для объединения данных из разных ячеек в одну ячейку. Например, он удобен для создания параметров отслеживания для маркетинговых кампаний, создания запросов API, добавления текста в числовой формат и ряда других вещей.

В приведенном выше примере я хотел, чтобы месяц и продажи были вместе в одном столбце. Для этого я использовал формулу = СЦЕПИТЬ (A2, B2) в ячейке C2, чтобы получить в результате 700 долларов США.

Формула: = СЦЕПИТЬ (ячейки, которые вы хотите объединить)

2. ЛЕН

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

В приведенном выше примере я хотел подсчитать цифры количества просмотров, которые я получал каждый месяц. Для этого я использовал формулу =LEN(C2) в ячейке D2, чтобы получить в результате 5.

Формула: = ДЛСТР (клетка)

3. ВПР

=VLOOKUP, вероятно, одна из самых узнаваемых функций для всех, кто знаком с анализом данных. Вы можете использовать его для сопоставления данных из таблицы с входным значением. Функция предлагает два режима сопоставления — точное и приближенное, которое контролируется диапазоном поиска. Если вы установите диапазон в FALSE, он будет искать точное совпадение, но если вы установите его в TRUE, он будет искать приблизительное совпадение.

В приведенном выше примере я хотел посмотреть количество просмотров в определенном месяце. Для этого я использовал формулу = ВПР («Июнь», A2: C13, 3) в ячейке G4 и в результате получил 74992. Здесь «Июнь» — это значение поиска, A2:C13 — это массив таблиц, в котором я ищу «Июнь», а 3 — это номер столбца, в котором формула найдет соответствующие представления для июня.

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

Формула: =ВПР(искомое_значение, table_array, col_index_num, [range_lookup])

4. ИНДЕКС/СООТВЕТСТВИЕ

Как и функция ВПР, функции ИНДЕКС и ПОИСКПОЗ удобны для поиска конкретных данных на основе входного значения. ИНДЕКС и ПОИСКПОЗ при совместном использовании могут преодолеть ограничения ВПР, связанные с получением неправильных результатов (если вы не будете осторожны). Таким образом, когда вы объединяете эти две функции, они могут точно определять ссылку на данные и искать значение в одномерном массиве. Это возвращает координаты данных в виде числа.

В приведенном выше примере я хотел посмотреть количество просмотров в январе. Для этого я использовал формулу = ИНДЕКС (A2: C13, ПОИСКПОЗ («Янв», A2: A13,0), 3). Здесь A2: C13 — это столбец данных, который я хочу, чтобы формула возвращала, «Jan» — это значение, которое я хочу сопоставить, A2: A13 — это столбец, в котором формула найдет «Jan», а 0 означает, что я хочу формула для поиска точного совпадения значения.

Если вы хотите найти приблизительное совпадение, вам придется заменить 0 на 1 или -1. Таким образом, 1 найдет наибольшее значение, меньшее или равное искомому значению, а -1 найдет наименьшее значение, меньшее или равное искомому значению. Обратите внимание, что если вы не используете 0, 1 или -1, формула будет использовать 1, by.

Теперь, если вы не хотите жестко указывать название месяца, вы можете заменить его номером ячейки. Таким образом, мы можем заменить «Jan» в формуле, упомянутой выше, на F3 или A2, чтобы получить тот же результат.

Формула: =ИНДЕКС(столбец данных, которые вы хотите вернуть, MATCH (общая точка данных, которую вы пытаетесь сопоставить, столбец другого источника данных, который имеет общую точку данных, 0))

5. МИНИ-/МАКСИФ

=MINIFS и =MAXIFS очень похожи на функции =MIN и =MAX, за исключением того факта, что они позволяют вам взять минимальный/максимальный набор значений и также сопоставить их по определенным критериям. По сути, функция ищет минимальное/максимальное значение и сопоставляет его с входными критериями.

В приведенном выше примере я хотел найти минимальные баллы в зависимости от пола учащегося. Для этого я использовал формулу =MINIFS (C2:C10, B2:B10, «M») и получил результат 27. Здесь C2:C10 — столбец, в котором формула будет искать баллы, B2:B10 — столбец, в котором формула будет искать критерии (пол), а «M» — это критерии.

Точно так же для получения максимальных баллов я использовал формулу =МАКСЕСЛИ(C2:C10, B2:B10, «M») и получил результат 100.

Формула для МИНИФС: =МИНИФС(минимальный_диапазон, диапазон_критериев1, критерий1,…)

Формула для МАКСЕСЛИ: =МАКСЕСЛИ(максимальный_диапазон, диапазон_критериев1, критерий1,…)

6. СРЕДНИЕ

Функция =СРЗНАЧЕСЛИМН позволяет найти среднее значение для определенного набора данных на основе одного или нескольких критериев. При использовании этой функции следует помнить, что каждый критерий и средний диапазон могут быть разными. Однако в функции =СРЗНАЧЕСЛИ и диапазон критериев, и диапазон суммы должны иметь один и тот же диапазон размеров. Заметили разницу в единственном и множественном числе между этими функциями? Ну, тут нужно быть осторожным.

В этом примере я хотел найти средний балл в зависимости от пола учащихся. Для этого я использовал формулу =СРЗНАЧЕСЛИМН(C2:C10, B2:B10, «M») и в результате получил 56,8. Здесь C2:C10 — это диапазон, в котором формула будет искать среднее значение, B2:B10 — это диапазон критериев, а «M» — это критерии.

Формула: =СРЗНАЧЕСЛИ(средний_диапазон, критерий_диапазон1, критерий1,…)

7. СЧЁТЕСЛИ

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

В этом примере я хотел найти количество студентов мужского и женского пола, получивших проходные баллы (т.е. >=40). Для этого я использовал формулу =СЧЁТЕСЛИМН(B2:B10, «M», C2:C10, «>=40»). Здесь B2:B10 — диапазон, в котором формула будет искать первые критерии (пол), «M» — первые критерии, C2:C10 — диапазон, в котором формула будет искать вторые критерии (оценки), и «>=40» — второй критерий.

Формула: =СЧЁТЕСЛИМН(диапазон_критериев1, критерий1,…)

8. СУММПРОДУКТ

Функция =СУММПРОИЗВ помогает перемножать диапазоны или массивы вместе, а затем возвращает сумму произведений. Это довольно универсальная функция, и ее можно использовать для подсчета и суммирования массивов, таких как СЧЁТЕСЛИМН или СУММЕСЛИМН, но с дополнительной гибкостью. Вы также можете использовать другие функции в SUMPRODUCT, чтобы еще больше расширить его функциональность.

В этом примере я хотел найти общую сумму всех проданных товаров. Для этого я использовал формулу =СУММПРОИЗВ(B2:B8, C2:C8). Здесь B2:B8 — первый массив (количество проданных товаров), а C2:C8 — второй массив (цена каждого товара). Затем формула умножает количество каждого проданного продукта на его цену, а затем складывает все это, чтобы получить общий объем продаж.

Формула: =СУММПРОИЗВ(массив1, [array2], [array3],…)

9. ОТДЕЛКА

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

В этом примере я хотел удалить все лишние пробелы между словами Mouse и pad в A7. Для этого я использовал формулу =TRIM(A7).

Формула просто удалила лишние пробелы и выдала результат Коврик для мыши с одним пробелом.

Формула: =ОБРЕЗАТЬ(текст)

10. НАЙТИ/ПОИСК

Завершают все функции НАЙТИ/ПОИСК, которые помогут вам выделить конкретный текст в наборе данных. Обе функции очень похожи в том, что они делают, за исключением одного важного отличия — функция =НАЙТИ возвращает только совпадения с учетом регистра. Между тем функция =ПОИСК таких ограничений не имеет. Эти функции особенно полезны при поиске аномалий или уникальных идентификаторов.

В этом примере я хотел найти количество раз, когда «Gui» появлялся в Guiding Tech, для чего я использовал формулу = НАЙТИ (A2, B2), которая дала результат 1. Теперь, если бы я хотел найти количество раз ‘ gui’ вместо этого появился в Guiding Tech, мне пришлось бы использовать формулу =ПОИСК, потому что она не чувствительна к регистру.

Формула поиска: =НАЙТИ(найти_текст, внутри_текста, [start_num])

Формула поиска: =ПОИСК(найти_текст, внутри_текста, [start_num])

Овладейте анализом данных

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

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