Microsoft Excel предлагает пользователям сотни различных функций и формул для самых разных целей. Если вам нужно проанализировать ваши личные финансы или любой большой набор данных, это функции, которые облегчают работу. Кроме того, это экономит много времени и усилий. Однако найти правильную функцию для вашего набора данных может быть очень сложно.
Так что, если вы изо всех сил пытались найти подходящую функцию Excel для анализа данных, то вы попали по адресу. Вот список некоторых важных функций Microsoft Excel, которые вы можете использовать для анализа данных, и вы можете повысить свою производительность в процессе.
Note: Для непосвященных функции, упомянутые в этом посте, необходимо добавить в панель формул электронной таблицы Excel, содержащей данные, или в ячейку, в которой вы хотите получить результат.
1. КОНКАТЕНАТ
= CONCATENATE – одна из важнейших функций анализа данных, поскольку она позволяет объединять текст, числа, даты и т. Д. Из нескольких ячеек в одну. Эта функция особенно полезна для объединения данных из разных ячеек в одну ячейку. Например, это удобно для создания параметров отслеживания для маркетинговых кампаний, построения запросов API, добавления текста в числовой формат и некоторых других вещей.
В приведенном выше примере я хотел, чтобы месяц и продажи были в одном столбце. Для этого я использовал формулу = CONCATENATE (A2, B2) в ячейке C2, чтобы получить в результате $ 700 за январь.
Формула: = CONCATENATE (ячейки, которые вы хотите объединить)
2. ЛЕН
= LEN – еще одна удобная функция для анализа данных, которая, по сути, выводит количество символов в любой заданной ячейке. Эта функция преимущественно используется при создании тегов заголовков или описаний, которые имеют ограничение по количеству символов. Это также может быть полезно, когда вы пытаетесь выяснить различия между различными уникальными идентификаторами, которые часто бывают довольно длинными и не в правильном порядке.
В приведенном выше примере я хотел посчитать цифры для количества просмотров, которые я получал каждый месяц. Для этого я использовал формулу = LEN (C2) в ячейке D2, чтобы получить 5 в качестве результата.
Формула: = LEN (ячейка)
3. ВЛУКУП
= VLOOKUP, вероятно, одна из самых узнаваемых функций для всех, кто знаком с анализом данных. Вы можете использовать его для сопоставления данных из таблицы с входным значением. Функция предлагает два режима сопоставления – точный и приблизительный, который контролируется диапазоном поиска. Если вы установите диапазон в FALSE, он будет искать точное совпадение, но если вы установите его в TRUE, он будет искать приблизительное совпадение.
В приведенном выше примере я хотел посмотреть количество просмотров за конкретный месяц. Для этого я использовал формулу = VLOOKUP («Jun», A2: C13, 3) в ячейке G4, и в результате я получил 74992. Здесь «Jun» – это значение поиска, A2: C13 – массив таблиц, в котором я ищу «Jun», а 3 – номер столбца, в котором формула найдет соответствующие представления для июня.
Единственным недостатком использования этой функции является то, что она работает только с данными, которые были организованы в столбцы, отсюда и название – вертикальный поиск. Поэтому, если ваши данные упорядочены по строкам, вам сначала нужно переместить строки в столбцы.
Формула: = VLOOKUP (lookup_value, table_array, col_index_num, (range_lookup))
4. ИНДЕКС / МАТЧ
Подобно функции VLOOKUP, функции INDEX и MATCH пригодятся для поиска определенных данных на основе входного значения. INDEX и MATCH, при совместном использовании, могут преодолеть ограничения VLOOKUP по доставке неправильных результатов (если вы не будете осторожны). Поэтому, когда вы объединяете эти две функции, они могут точно определять ссылку на данные и искать значение в одном массиве измерений. Это возвращает координаты данных в виде числа.
В приведенном выше примере я хотел посмотреть количество просмотров в январе. Для этого я использовал формулу = INDEX (A2: C13, MATCH («Jan», A2: A13,0), 3). Здесь A2: C13 – столбец данных, который я хочу вернуть формуле, «Jan» – значение, которое я хочу сопоставить, A2: A13 – столбец, в котором формула найдет «Jan», а 0 означает, что я хочу формула, чтобы найти точное соответствие для значения.
Если вы хотите найти приблизительное совпадение, вам придется заменить 0 на 1 или -1. Так что 1 найдет наибольшее значение, меньшее или равное значению поиска, а -1 найдет наименьшее значение, меньшее или равное значению поиска. Обратите внимание, что если вы не используете 0, 1 или -1, формула будет использовать 1, by.
Теперь, если вы не хотите жестко задавать название месяца, вы можете заменить его на номер ячейки. Таким образом, мы можем заменить «Ян» в вышеприведенной формуле на F3 или A2, чтобы получить тот же результат.
Формула: = INDEX (столбец данных, которые вы хотите вернуть, MATCH (общая точка данных, которую вы пытаетесь сопоставить, столбец другого источника данных, который имеет общую точку данных, 0))
5. МИНИФЫ / МАКСИФЫ
= MINIFS и = MAXIFS очень похожи на функции = MIN и = MAX, за исключением того факта, что они позволяют вам брать минимальный / максимальный набор значений и сопоставлять их также по определенным критериям. По сути, функция ищет минимальные / максимальные значения и сопоставляет их с критериями ввода.
В приведенном выше примере я хотел найти минимальные оценки в зависимости от пола студента. Для этого я использовал формулу = MINIFS (C2: C10, B2: B10, «M») и получил результат 27. Здесь C2: C10 – столбец, в котором формула будет искать оценки, B2: B10 – столбец, в котором формула будет искать критерии (пол), а «М» – критерии.
Аналогично, для получения максимальных баллов я использовал формулу = MAXIFS (C2: C10, B2: B10, «M») и получил результат 100.
Формула для MINIFS: = MINIFS (min_range, критерии_range1, критерии1, …)
Формула для MAXIFS: = MAXIFS (max_range, crit_range1, критерий1, …)
6. СРЕДНИЕ
Функция = AVERAGEIFS позволяет найти среднее значение для определенного набора данных на основе одного или нескольких критериев. При использовании этой функции вы должны иметь в виду, что каждый критерий и средний диапазон могут быть разными. Однако в функции = AVERAGEIF диапазон критериев и диапазон сумм должны иметь одинаковый диапазон размеров. Заметьте разницу между единственным и множественным числом между этими функциями? Ну, вот где тебе нужно быть осторожным.
В этом примере я хотел найти среднюю оценку в зависимости от пола студентов. Для этого я использовал формулу = AVERAGEIFS (C2: C10, B2: B10, «M») и получил 56,8 в результате. Здесь C2: C10 – это диапазон, в котором формула будет искать среднее значение, B2: B10 – диапазон критериев, а «M» – критерии.
Формула: = AVERAGEIFS (средний диапазон, критерий_1, критерии1, …)
7. СЧЕТЧИКИ
Теперь, если вы хотите подсчитать количество случаев, когда набор данных соответствует определенным критериям, вам нужно использовать функцию = COUNTIFS. Эта функция позволяет вам добавлять неограниченные критерии к вашему запросу и, таким образом, делает его наиболее простым способом определения количества на основе критериев ввода.
В этом примере я хотел найти количество студентов мужского или женского пола, которые получили проходные баллы (т. Е.> = 40). Для этого я использовал формулу = COUNTIFS (B2: B10, «M», C2: C10, «> = 40»). Здесь B2: B10 – это диапазон, в котором формула будет искать первые критерии (пол), «M» – это первый критерий, C2: C10 – диапазон, в котором формула будет искать вторые критерии (знаки), а "> = 40" является вторым критерием.
Формула: = COUNTIFS (crit__range1, критерий1, …)
8. СУМПРОДУКТ
Функция = SUMPRODUCT помогает вам умножать диапазоны или массивы вместе, а затем возвращает сумму произведений. Это довольно универсальная функция и может использоваться для подсчета и суммирования массивов, таких как COUNTIFS или SUMIFS, но с дополнительной гибкостью. Вы также можете использовать другие функции в SUMPRODUCT, чтобы расширить его функциональность.
В этом примере я хотел найти общую сумму всех проданных продуктов. Для этого я использовал формулу = SUMPRODUCT (B2: B8, C2: C8). Здесь B2: B8 – это первый массив (количество проданных товаров), а C2: C8 – второй массив (цена каждого товара). Затем формула умножает количество каждого проданного продукта на его цену, а затем складывает все это, чтобы получить общий объем продаж.
Формула: = SUMPRODUCT (массив1, (массив2), (массив3), …)
9. TRIM
Функция = TRIM особенно полезна, когда вы работаете с набором данных, который имеет несколько пробелов или нежелательных символов. Эта функция позволяет с легкостью удалять эти пробелы или символы из ваших данных, что позволяет получать точные результаты при использовании других функций.
В этом примере я хотел удалить все лишние пробелы между словами Mouse и pad в A7. Для этого я использовал формулу = TRIM (A7).
Формула просто удалила лишние пробелы и доставила в результате коврик для мыши с одним пробелом.
Формула: = TRIM (текст)
10. НАЙТИ / ПОИСК
Округление – это функции НАЙТИ / ПОИСК, которые помогут вам выделить конкретный текст в наборе данных. Обе функции очень похожи в том, что они делают, за исключением одного существенного различия – функция = FIND возвращает только регистрозависимые совпадения. Между тем, функция = SEARCH не имеет таких ограничений. Эти функции особенно полезны при поиске аномалий или уникальных идентификаторов.
В этом примере я хотел узнать, сколько раз в Guiding Tech появлялось «Gui», для которого я использовал формулу = FIND (A2, B2), которая дала результат 1. Теперь, если я хочу найти количество раз ' Вместо этого gui появился в Guiding Tech, мне пришлось бы использовать формулу = SEARCH, потому что она не чувствительна к регистру.
Формула для поиска: = НАЙТИ (find_text, Within_text, (start_num))
Формула для поиска: = ПОИСК (find_text, Within_text, (start_num))
Овладеть анализом данных
Эти важные функции Microsoft Excel определенно помогут вам с анализом данных, но этот список только царапает верхушку айсберга. Excel также включает в себя несколько других расширенных функций для достижения конкретных результатов. Если вы хотите узнать больше об этих функциях, сообщите нам об этом в разделе комментариев ниже.
Следующий: Если вы хотите использовать Excel более эффективно, то вам следует ознакомиться со следующей статьей, где вы найдете несколько удобных ярлыков навигации в Excel, которые вам необходимо знать.
Add comment