Excel содержит множество различных функций, но не все они необходимы при анализе данных. В этой статье вы узнаете о 10 самых популярных функциях, которые пригодятся вам при работе с информацией. Эти функции позволяют выполнять большинство задач, возникающих при анализе данных.
1. ВПР
Эта функция является одной из самых популярных и часто используемых функций в Excel. Если вам нужно найти данные в одном столбце таблицы и получить значение из другого столбца таблицы, эта функция поможет вам. Его синтаксис таков:
VPR (искомое значение; таблица; номер столбца; интервальный вид)
— Искомое значение — это значение, которое мы будем искать в таблице данных
— Таблица — диапазон данных, в первом столбце которого мы будем искать искомое значение
— Количество столбцов — этот параметр определяет, сколько столбцов мы должны сдвинуть вправо в таблице, чтобы получить результат
— Интервальный поиск — может принимать значение 0 или FALSE, что означает, что соответствие между значением поиска и значением в первом столбце таблицы должно быть точным; или 1 или TRUE, тогда соответствие должно быть неточным. Я настоятельно рекомендую использовать только FALSE, иначе вы можете получить непредсказуемые результаты.
В приведенном выше примере мы ищем фамилию Петров в таблице базы данных по имени. В функции VPR(E2;A1:C6;2;0) первый параметр (E2) — ссылка на ячейку, в которой мы будем искать фамилию; второй параметр A1:C6 — ссылка на таблицу, в первом столбце которой мы будем искать значение, заданное в первом параметре; третий параметр «2» — из какого столбца справа вытаскивать значение; четвертый параметр «0» — точный поиск.
Если вы хотите более подробно узнать, как работает функция VRP, прочитайте нашу статью «Функция VRP в Excel».
2. ГПР
Функция БПФ выполняет ту же задачу, что и функция ДЗЗ, за исключением того, что она ищет искомое значение в первой строке и перемещается вниз на определенное количество строк, чтобы получить результат.
Синтаксис функции следующий:
FPR(поисковое значение;таблица;номер строки;представление диапазона)
— Искомое значение — это значение, которое мы ищем в данной строке. — Таблица — диапазон данных на листе, где в первой строке мы ищем нужное значение и перемещаем его на необходимое количество строк. — Номер ряда — числовое значение, указывающее, на сколько рядов вниз нам нужно переместиться. — Вид интервала — всегда устанавливайте значение 0, тогда Excel будет искать точное совпадение, что нам и нужно в большинстве случаев.
В приведенном выше примере мы ищем доход за сентябрь в ежемесячном отчете о доходах. В формуле GPR(A5;B1:M2;2;0) первый параметр (A5) — это ссылка на месяц, для которого нам нужна выручка; второй параметр (B1:M2) — это ссылка на таблицу, в которой первая строка содержит месяцы, из которых мы хотим найти выбранный; третий параметр «2» — из какой строки ниже мы будем брать данные; четвертый параметр «0» — это когда мы ищем точное совпадение.
Если вы хотите узнать больше о том, как использовать функцию БПФ, прочитайте статью «Функция БПФ в Excel» на нашем сайте.
3. ЕСЛИ
Функция IF — очень популярная функция в Excel. Он позволяет автоматически выполнять действие в зависимости от заданного условия.
Функция IF проверяет логическое выражение, и если оно истинно, то выдается одно значение, а если ложно, то альтернативное значение. Синтаксис следующий:
IF(булево выражение; значение, если true; значение, если false)
— Булево выражение — это выражение, которое при оценке должно возвращать значение TRUE или FALSE.
— Значение, если true — устанавливает указанное значение, если логическое выражение возвращает TRUE
— Значение, если false — устанавливает указанное значение, если булево выражение возвращает FALSE.
В приведенном выше примере мы хотим определить, получили ли мы за месяц более 500 рублей. В формуле ЕСЛИ(B2>500; «Да»; «Нет») первый параметр (B2>500) проверяет, превышает ли доход в данном месяце 500 рублей или нет; второй параметр («Да») — функция вернет Да, если доход больше 500 рублей и соответственно Нет (третий параметр), если доход меньше.
Обратите внимание, что значениями true или false могут быть не только текстовые, числовые значения, но и функции (в том числе IF), что позволяет реализовать достаточно сложные логические конструкции.
4. ЕСЛИОШИБКА
При работе с формулами в Excel вы можете периодически сталкиваться с различными ошибками. Например, в следующем примере функция VPR возвращает ошибку #N/D, поскольку искомой фамилии нет в базе данных фамилий (подробнее об ошибке #N/D вы можете прочитать в статье: «Как исправить ошибку #N/D в Excel»).
Лучший способ справиться с такими ситуациями — использовать функцию USER ERROR. Его синтаксис выглядит следующим образом:
USER ERROR(значение; значение при ошибке)
— Значение, результат которого проверяется на наличие ошибки.
— Значение при ошибке — Если результатом функции является ошибка, то вместо ошибки будет выведено это значение.
В случае нашего примера выше мы можем предположить, что имя может быть неправильным, поэтому IF ERROR вернет предупреждение о том, что мы должны проверить правильность написания имени.
В приведенном выше примере мы проверяем результат функции VPR(E2;A1:C6;2;0), и если возвращается ошибка, то выводится сообщение «Проверьте фамилию!».
5. СУММЕСЛИМН
Функция SUMMESLIMN позволяет суммировать значения при определенных условиях. Условий может быть несколько. В Excel также есть функция СУММЕСЛИ, которая позволяет суммировать один критерий. Я призываю вас использовать более универсальную формулу.
Функция SUMMESLYMN имеет следующий синтаксис:
SUMMESLIMN(Диапазон суммирования; Диапазон условий 1; Условие 1;…)
— Диапазон суммирования — это область рабочего листа Excel, из которой мы суммируем данные
— Диапазон условий 1 — диапазон ячеек, которые мы проверяем на наличие условия
— Условие 1 — Условие, которое мы проверяем на согласованность в диапазоне 1.
Обратите внимание, что диапазонов условий, а значит и условий, может быть столько, сколько вам нужно.
В приведенном примере мы хотим получить доход, принесенный нам Петровым в городе Москве. Формула имеет вид AMMESLIMN(C2:C13;A2:A13;E2;B2:B13;F2), где C2:C13 — диапазон со значениями выручки, которые мы хотим суммировать; A2:A13 — диапазон с именами, которые мы будем проверять; E2 — ссылка на конкретное имя; B2:B13 — ссылка на диапазон с городами; F2 — ссылка на конкретный город.
Подробнее о функциях SUMMESLIMN и SUMMESLIMN читайте в статье «SUMMESLIMN и SUMMESLIMN в Excel».
6. СЧЁТЕСЛИМН
CALCULINA очень похожа на функцию AMMESLYMN, но в отличие от нее, она не суммирует значения, а только подсчитывает количество ячеек, удовлетворяющих определенным условиям. Как и AMMEMSLYMN, он имеет упрощенную форму SCHUTESLY, которая подсчитывает количество клеток только для одного критерия, но лучше использовать более общую версию.
Синтаксис функции следующий:
SCRATELIMN(диапазон условий 1; условие 1;…)
— Диапазон условий 1 — диапазон клеток, которые проверяются для данного условия.
— Условие 1 — условие, определяющее, какие клетки должны быть учтены при подсчете.
Обратите внимание, что может быть несколько диапазонов условий и, соответственно, условий.
В приведенном выше примере мы подсчитываем, сколько ячеек в таблице содержат фамилию Петров и город Москва. В формуле SCRATESLIMN(A2:A13;E2;B2:B13;F2) диапазон A2:A13 — это диапазон фамилий, которые мы проверяем, E2 — это фамилия, которую мы ищем в диапазоне; B2:B13 — это диапазон городов и соответственно F2 — это город, который мы считаем в ячейке. Полученное число 3 — это количество строк в таблице, где фамилия — Иванов, а город — Москва.
7. СЖПРОБЕЛЫ
При работе с данными в Excel мы можем брать их из разных источников, что может привести к тому, что в результирующих значениях есть «мусорная» информация, очень часто это лишние пробелы, которые необходимо удалить. Мы можем удалить их вручную, но это долго и обременительно. Чтобы помочь нам, мы можем использовать функцию SJPROBLES, которая удаляет лишние пробелы, если их больше одного в строке. Синтаксис этой функции очень прост:
SPLASHES(текст).
— Текст — это текст, из которого мы хотим удалить лишние пробелы.
Как видно из примера выше, функция успешно удалила лишние пробелы из исходной строки.
8. ЛЕВСИМВ и ПРАВСИМВ
Функции LEFTIMB и RIGHTIMB возвращают указанное количество символов от начала (LEFTIMB) или конца (RIGHTIMB) строки. Эти функции используются для получения частей строки. Синтаксис этих функций одинаков:
LEFTIMB(текст; количество символов)
RIGHTSIMB(текст; количество символов)
— Текст — это строковое выражение, из которого мы хотим получить часть.
— Количество символов — это количество символов, которые мы хотим получить.
В приведенном выше примере мы извлекаем 6 символов из текста «Example text» слева и получаем текст «Example».
9. СЦЕПИТЬ
Функция SPITE позволяет объединить значения из нескольких ячеек. Его синтаксис довольно прост:
CONNECT(text1; text2;…)
— Текст 1 — Текст, который мы хотим объединить в одну строку.
— Текст 2 — Текст, который необходимо объединить в одну строку
Обратите внимание, что можно объединить до 255 текстовых значений.
В приведенном выше примере мы объединяем имя и фамилию. В функции CONNECT(A2;» «;B2) первый параметр (A2) — ссылка на ячейку с фамилией; второй параметр (» «) — пробел, чтобы окончательный текст выглядел нормально; третий параметр (B2) — ссылка на ячейку с фамилией.
10.ЗНАЧЕН
Часто данные, которые мы получаем из внешних источников, имеют текстовый формат, и мы не можем выполнять над ними какие-либо математические операции (сложение, вычитание и т.д.). Сначала нам нужно преобразовать текст в число, для этого мы воспользуемся функцией SIGNED. Синтаксис функции следующий:
— Текст — это число, представленное в текстовом формате.
Как видно из приведенного примера, у нас есть число 12522, которое представлено в виде текста. Используя функцию VALUE, мы преобразовали его в число 12,522, с которым мы можем работать как с любым другим числом.