Microsoft Excel предлагает пользователям сотни различных функций и формул для разных целей. Если вам нужно проанализировать свои личные финансы или любой большой набор данных, эти функции облегчат вашу работу. Кроме того, это экономит много времени и сил. Однако может быть очень сложно найти правильную функцию для вашего набора данных.
Итак, если у вас возникли проблемы с поиском подходящей функции Excel для анализа ваших данных, вы обратились по адресу. Вот список некоторых основных функций Microsoft Excel, которые вы можете использовать для анализа данных и повышения производительности в процессе.
Примечание. Для непосвященных функции, упомянутые в этом посте, следует добавить в строку формул электронной таблицы Excel, содержащую данные, или в ячейку, где вы хотите получить результат.
1. СЦЕПИТЬ
= СЦЕПИТЬ — одна из самых важных функций анализа данных, поскольку она позволяет объединять текст, числа, даты и т д из нескольких ячеек в одну. Эта функция особенно полезна для объединения данных из разных ячеек в одну ячейку. Например, это полезно для создания параметров для отслеживания маркетинговых кампаний, отправки запросов API, добавления текста в числовой формат и некоторых других вещей.
В приведенном выше примере я хотел, чтобы месяц и продажи были вместе в одном столбце. Для этого я использовал формулу = СЦЕПИТЬ (A2, B2) в ячейке C2, чтобы получить в результате 700 долларов.
Формула: = СЦЕПИТЬ (ячейки, которые вы хотите объединить)
2. ЛЕН
= LEN — еще одна полезная функция анализа данных, которая в основном печатает количество символов в любой ячейке. Эту функцию можно использовать в основном при создании тегов заголовков или тегов описания с ограничением по количеству символов. Это также может быть полезно при попытке найти различия между разными уникальными идентификаторами, которые часто бывают довольно длинными и расположены не в правильном порядке.
В приведенном выше примере я хотел подсчитать количество просмотров, которые я получал каждый месяц. Для этого я использовал формулу = LEN (C2) в ячейке D2, чтобы получить результат 5.
Формула: = LSTR (ячейка)
3. ВПР
= ВПР, вероятно, является одной из самых узнаваемых функций для всех, кто знаком с наукой о данных. Вы можете использовать его для сопоставления данных в таблице с входным значением. Функция предлагает два режима соответствия — точное и приблизительное, которое контролируется областью поиска. Если вы установите диапазон в FALSE, он будет искать точное совпадение, но если вы установите его в TRUE, он будет искать приблизительное совпадение.
В приведенном выше примере я хотел увидеть количество просмотров в данном месяце. Для этого я использовал формулу =ВПР(«Июнь»,А2:С13.3) в ячейке G4 и результат 74992. Здесь «Июнь» — искомое значение, А2:С13 — таблица таблиц где я нахожусь ищет «июнь», а 3 — это номер столбца, в котором формула находит подходящие представления для июня.
Единственным недостатком использования этой функции является то, что она работает только с данными, организованными в столбцы, отсюда и название вертикального поиска. Поэтому, если ваши данные находятся в строках, вам сначала нужно переупорядочить их в столбцы.
Формула: = ВПР (значение_поиска; массив_таблиц; индекс_столбца; [диапазон_поиска])
4. ИНДЕКС/СООТВЕТСТВИЕ
Как и функция ВПР, функции ИНДЕКС и ПОИСКПОЗ полезны для поиска конкретных данных на основе входного значения. ИНДЕКС и ПОИСКПОЗ при совместном использовании могут преодолеть ограничения ВПР, связанные с получением неправильных результатов (если вы не будете осторожны). Поэтому, когда вы объединяете эти две функции, они могут точно определить данные и найти значение в одномерном массиве. Возвращает координаты данных в виде числа.
В приведенном выше примере я хотел увидеть количество просмотров в январе. Для этого я использовал формулу =ИНДЕКС(A2:C13,ПОИСКПОЗ(«Ян»,A2:A13,0),3). Здесь A2: C13 — это столбец данных, который я хочу, чтобы формула возвращала, «Jan» — это значение, которое я хочу сопоставить, A2: A13 — это столбец, в котором формула найдет «Jan», а 0 означает, что я хочу, чтобы формула нашла точное значение значение соответствия.
Если вы хотите найти приблизительное совпадение, вам придется изменить 0 на 1 или -1. Таким образом, 1 найдет наибольшее значение, меньшее или равное искомому значению, а -1 найдет наименьшее значение, меньшее или равное искомому значению. Помните, что если вы не используете 0, 1 или -1, формула будет использовать от 1 до.
Теперь, если вы не хотите кодировать название месяца, вы можете заменить его номером ячейки. Таким образом, мы можем заменить «Jan» в формуле, упомянутой выше, на F3 или A2, чтобы получить тот же результат.
Формула: = ИНДЕКС (столбец данных, который вы хотите вернуть ПОИСКПОЗ (общая точка данных, которую вы пытаетесь сопоставить, столбец другого источника данных, который имеет общую точку данных, 0))
5. МИНИ-/МАКСИФ
Функции =MINIFS и =MAXIFS очень похожи на функции =MIN и =MAX, за исключением того, что они позволяют взять минимальный/максимальный набор значений и сопоставить их с определенными критериями. В основном функция ищет минимальное/максимальное значение и сопоставляет его с входными критериями.
В приведенном выше примере я хотел найти минимальные баллы в зависимости от пола ученика. Для этого я воспользовался формулой = МИНИФС(С2:С10, В2:В10, «М») и получил результат 27. Здесь С2:С10 — столбец, в котором формула будет искать точки, В2:В10 — столбец, в котором формула будет искать критерии (пол), а «М» — это критерий.
Точно так же, чтобы получить максимальный результат, я использовал формулу = МАКСЕСЛИ (C2: C10, B2: B10, «M») и получил результат 100.
Формула MINIFS: = MINIFS (минимальный_диапазон, критерий_диапазон1, критерий1,…)
Формула MAXIFS: = MAXIFS (максимальный_диапазон, критерий_диапазон1, критерий1,…)
6. СРЕДНИЕ
Функция = СРЗНАЧ.ЕСЛИ находит среднее значение для определенного набора данных на основе одного или нескольких критериев. При использовании этой функции имейте в виду, что каждый критерий и средний диапазон могут быть разными. Однако в функции = СРЗНАЧЕСЛИ и диапазон критериев, и диапазон сумм должны находиться в одном и том же диапазоне размеров. Заметили разницу в единственном и множественном числе между этими функциями? Ну тут надо быть осторожным.
В этом примере я хотел найти средний балл по полу учащихся. Для этого я использовал формулу =СРЗНАЧЕСЛИ(С2:С10, В2:В10,»М») и получил результат 56,8. Здесь C2:C10 — это диапазон, в котором формула будет усреднена, B2: B10 — это диапазон критериев, а «M» — это критерии.
Формула: = СРЗНАЧ (средний_диапазон, критерий_диапазон1, критерий1,…)
7. СЧЁТЕСЛИ
Теперь, если вы хотите подсчитать, сколько раз набор данных соответствует определенным критериям, вам нужно использовать функцию = СЧЁТЕСЛИМН. Эта функция позволяет вам добавлять неограниченное количество критериев к вашему запросу и, таким образом, упрощает поиск числа на основе ваших входных критериев.
В этом примере я хотел найти количество учеников и студентов, получивших положительные оценки (т.е.> = 40). Для этого я использовал формулу = СЧЁТЕСЛИМН (B2: B10, «M», C2: C10, «> = 40″). Здесь B2: B10 — диапазон, в котором формула будет искать первый критерий (пол), «M» — первый критерий, C2: C10 — диапазон, в котором формула будет искать второй критерий (результаты), и «> = 40» это второй критерий.
Формула: = СЧЁТЕСЛИМН (диапазон_критериев1, критерий1,…)
8. СУММПРОДУКТ
Функция =СУММПРОИЗВ помогает умножать диапазоны или массивы, а затем возвращает сумму произведений. Это довольно универсальная функция, и ее можно использовать для подсчета и суммирования массивов, таких как СЧЁТЕСЛИМН или СУММЕСЛИМН, но с дополнительной гибкостью. Вы также можете использовать другие функции СУММПРОИЗВ, чтобы еще больше расширить его функциональность.
В этом примере я хотел найти общую сумму всех проданных товаров. Для этого я использовал формулу =СУММПРОИЗВ(B2:B8, C2:C8). Здесь B2: B8 — первый массив (количество проданных товаров), а C2: C8 — второй массив (цена каждого товара). Затем формула умножает количество каждого проданного продукта на его цену, а затем складывает все это, чтобы получить общий объем продаж.
Формула: =ПРОДУКТСУМА(массив1,[массив2],[массив3],…)
9. ОТДЕЛКА
Функция = TRIM особенно полезна при работе с набором данных, который содержит много пробелов или нежелательных символов. Эта функция позволяет вам легко удалять эти пробелы или символы из ваших данных для получения точных результатов при использовании других функций.
В этом примере я хотел удалить все лишние пробелы между словами Mouse и Pad в A7. Для этого я использовал формулу = ОБРЕЗ (A7).
Формула просто убрала лишние пробелы и вернула получившийся коврик для мыши с одним пробелом.
Формула: = ВЫРЕЗАТЬ (текст)
10. НАЙТИ/ПОИСК
Завершение всех функций НАЙТИ/ПОИСК, которые помогают выделить конкретный текст в наборе данных. Эти две функции очень похожи по своему поведению, за исключением одного важного отличия: функция = НАЙТИ возвращает только совпадения с учетом регистра. Между тем, у функции =ПОИСК таких ограничений нет. Эти функции особенно полезны при поиске аномалий или уникальных идентификаторов.
В этом примере я хотел узнать, сколько раз «Gui» появлялся в Guiding Tech, для чего я использовал формулу = НАЙТИ (A2, B2), в результате чего получилось 1. Теперь, если бы я хотел найти количество раз «gui» появилось в Guiding вместо Tech, мне пришлось бы использовать формулу = ПОИСК, так как она не чувствительна к регистру.
Формула поиска: = НАЙТИ (найти_текст; входящий_текст; [начальный_номер])
Формула поиска: =ПОИСК(найти_текст;в_тексте;[начальный_номер])
Овладейте анализом данных
Эти основные функции Microsoft Excel, безусловно, помогут вам проанализировать ваши данные, но этот список — лишь верхушка айсберга. Excel также включает в себя некоторые другие расширенные функции, которые помогут вам достичь определенных результатов. Если вы хотите узнать больше об этих функциях, сообщите нам об этом в разделе комментариев ниже.
Далее: Если вы хотите использовать Excel более эффективно, ознакомьтесь со статьей ниже, чтобы узнать о некоторых полезных сочетаниях клавиш для навигации по Excel, которые вам следует знать.
