Excel содержит огромное количество самых разнообразных функций, однако не все они нужны при анализе данных. В этой статье вы узнаете о 10 наиболее популярных функций, которые будут нужны при работе с информацией. Эти функции позволяют выполнить большинство задач, которые появляются при анализе данных.
1. ВПР
Эта функция является одной из самых популярных и часто используемых в Excel. Если вам необходимо найти данные в одном столбце в таблице и получить значение из другого столбца таблицы, то эта функция вам поможет. Ее синтаксис:
ВПР (искомое значение; таблица; номер столбца; интервальный просмотр)
- Искомое значение - это то значение, которое мы будем искать в таблице с данными
- Таблица - диапазон данных, в первом столбце которого мы будем искать искомое значение
- Номер столбца - этот параметр обозначает, на какое количество столбцов надо сдвинуться вправо в таблице для получения результата
- Интервальный просмотр - Может принимать параметр 0 или ЛОЖЬ, что обозначает что совпадение между искомым значением и значением в первом столбце таблицы должен быть точным; либо 1 или ИСТИНА, соответственно совпадение должно быть неточным. Настоятельно рекомендую использовать только параметр ЛОЖЬ, иначе можно получать непредсказуемые результаты.
Если хотите изучить более подробно, как работает функция ВПР, прочитайте нашу статью "Функция ВПР в Excel".
2. ГПР
Функция ГПР выполняет туже задачу, что и ВПР, только она просматривает первую строку в поиске искомого значения и для получения результата сдвигается на указанное количество строк вниз.
ГПР(искомое значение;таблица;номер строки;интервальный просмотр)
В примере выше мы ищем выручку за сентябрь в помесячном отчете по выручке. В формуле ГПР(A5;B1:M2;2;0) первый параметр (А5) - ссылка на месяц, по которому мы хотим получить выручку; второй параметр (B1:M2) - ссылка на таблицу, где в первой строке указаны месяцы, среди которых нам нужно найти выбранный; третий параметр "2" - из какой строки ниже мы будем получать данные; четвертый параметр "0" - ищем точное совпадение.
Если вы хотите более подробно изучить, как пользоваться функцией ГПР - прочитайте статью на нашем сайте "Функция ГПР в Excel".
3. ЕСЛИ
Функция ЕСЛИ является очень популярной в Excel. Она позволяет автоматически выполнять какое-либо действие, в зависимости от поставленного условия.
Функция ЕСЛИ выполняет проверку логического выражения и если выражение истинно, то поставляется одно значение и альтернативное, если ложь. Синтаксис следующий:
ЕСЛИ(логическое выражение; значение если истина; значение если ложь)
- Логическое выражение - выражение, которое по итогу своего вычисления должно вырнуться значение ИСТИНА или ЛОЖЬ.
- Значение, если истина - устанавливаем указанное значение, если логическое выражение вернуло ИСТИНА
- Значение, если ложь - устанавливает указанное значение, если логическое выражение вернуло ЛОЖЬ.
В примере выше мы хотим определить, получили ли мы за месяц выручку больше 500 рублей или нет. В формуле ЕСЛИ(B2>500;"Да";"Нет") первый параметр (B2>500) проверяет, выручка за месяц больше 500 рублей или нет; второй параметр ("Да") - функция вернет Да, если выручка больше 500 рублей и соответственно Нет (третий параметр), если выручка меньше.
Обратите внимание, что значения при истине или лжи могут быть не только текстовые, числовые, но также и функции(в том числе и ЕСЛИ), что позволяет реализовать достаточно сложные логические конструкции.
4. ЕСЛИОШИБКА
При работе с формулами в Excel, можно время от времени сталкиваться с различными ошибками. Так в примере ниже функция ВПР вернула ошибку #Н/Д из-за того, что в базе данных по ФИО нет искомой нами фамилии (более подробно об ошибке #Н/Д вы можете прочитать в этой статье: "Как исправить ошибку #Н/Д в Excel")
Для обработки таких ситуаций отлично подойдет функция ЕСЛИОШИБКА. Ее синтаксис следующий:
ЕСЛИОШИБКА(значение; значение если ошибка)
- Значение, результат которого проверяется на ошибку.
- Значение, если ошибка - В случае, если в результате работы функции получаем ошибку, то выводится не ошибка, а данное значение.
В случае с нашим примером выше, мы можем предположить, что фамилия может быть некорректной, соответственно ЕСЛИОШИБКА вернет нам предупреждение, что бы мы проверили написание фамилии.
В примере выше, мы проверяем результат работы функции ВПР(E2;A1:C6;2;0) и в случае, если вернется ошибка, то выдаем сообщение "Проверьте фамилию!".
5. СУММЕСЛИМН
Функция СУММЕСЛИМН позволяет суммировать значения по определенным условиям. Условий может быть несколько. В Excel также есть функция СУММЕСЛИ, которая позволяет суммировать по одному критерию. Призываю вас использовать более универсальную формулу.
У функции СУММЕСЛИМН следующий синтаксис:
СУММЕСЛИМН(Диапазон суммирования; Диапазон условия 1; Условие 1;...)
- Диапазон суммирования - область листа Эксель, из которой мы суммируем данные
- Диапазон условия 1 - Диапазон ячеек, которые мы проверяем на соответствие условию
- Условие 1 - Условие, которое проверяется на соответствие в Диапазоне 1.
Обратите внимание, что диапазонов условий и соответственно условий может быть столько, сколько вам нужно.
Для примера выше мы хотим получит выручку, которую принес нам Петров в городе Москва. Формула имеет вид СУММЕСЛИМН(C2:C13;A2:A13;E2;B2:B13;F2), где C2:C13 - диапазон со значениями выручки, которые необходимо просуммировать; А2:А13 - диапазон с фамилиями, которые мы будем проверять; Е2 - ссылка на конкретную фамилию; B2:B13 - ссылка на диапазон с городами; F2 - ссылка на конкретный город.
Более подробно о функциях СУММЕСЛИМН и СУММЕСЛИ рассказано в статье "СУММЕСЛИ и СУММЕСЛИМН в Excel".
6. СЧЁТЕСЛИМН
СЧЁТЕСЛИМН очень похожа на функцию СУММЕСЛИМН, только в отличии от нее, она не суммируется значения, а только считает количество ячеек, которые соответствуют определенным условиям. Как и в случае с СУММЕСЛИМН, у СЧЁТЕСЛИМН есть упрощенная форма СЧЁТЕСЛИ, который считает количество ячеек только по одному критерию, но лучше используйте более общий вариант.
Синтаксис у функции следующий:
СЧЁТЕСЛИМН(диапазон условия 1; условие 1;...)
- Диапазон условия 1 - Диапазон ячеек, которые проверяются на соответствие определенному условию.
- Условие 1 - Условие, которое определяет какие ячейки надо учитывать при подсчете.
Обратите внимания, что диапазонов условий и соответственно условий может быть несколько.
В примере выше, мы считаем сколько в таблице ячеек, в которых фамилия - Петров, а город - Москва. В формуле СЧЁТЕСЛИМН(A2:A13;E2;B2:B13;F2) диапазон A2:A13 - диапазон фамилий, которые мы проверяем, Е2 - та фамилия, которую мы ищем в диапазоне; B2:B13 - диапазон городов и соответственно F2 - город, который мы учитываем при подсчете ячеек. Получившееся число 3 - это количество строк в таблице, где фамилия равна Иванов, а город равен Москва.
7. СЖПРОБЕЛЫ
При работе с данными в Excel, мы можем получать их из разных источников, что может привести к тому, что получаемые значения имеют "мусорную" информацию, очень часто это лишние пробелы, которые надо удалить. Можно удалять вручную, но это долго и муторно. На выручку нам приходит функция СЖПРОБЕЛЫ, которая удаляет лишние пробелы, в случае если их больше одного подряд. Синтаксис у функции очень простой:
СЖПРОБЕЛЫ(текст)
- Текст - тот текст, из которого надо убрать лишние пробелы.
Как видно из примера выше, функция успешно удалила лишние пробелы из исходной строки.
8. ЛЕВСИМВ и ПРАВСИМВ
Функции ЛЕВСИМВ и ПРАВСИМВ возвращают определенное количество знаков с начала (ЛЕВСИМВ) либо с конца (ПРАВСИМВ) строки. Эти функции нужны для получения части строки. Синтаксис у функций однотипный:
ЛЕВСИМВ(текст; количество знаков)
ПРАВСИМВ(текст; количество знаков)
- Текст - то строковое выражение, из которого мы хотим получить часть.
- Количество знаков - число символов, которое мы хотим получить.
В примере выше мы из текста "Пример текста" извлекаем 6 символов слева и получаем текст "Пример".
9. СЦЕПИТЬ
Функция СПЕПИТЬ позволяет объединить значения из нескольких ячеек. Синтаксис у функции достаточно простой:
СЦЕПИТЬ(текст1; текст2;...)
- Текст 1 - Текст, который надо соединить в одну строку
- Текст 2 - Текст, который надо соединить в одну строку
Обратите внимание, что вы можете объединить до 255 текстовых значений.
В примере выше мы объединяем фамилию и имя. В функции СЦЕПИТЬ(A2;" ";B2), первый параметр(А2) - ссылка на ячейку с фамилией; второй параметр (" ") - пробел, что бы итоговый текст смотрелся нормально; третий параметр(В2) - ссылка на ячейку с именем.
10.ЗНАЧЕН
Часто данные, которые мы получаем из внешних источников, имеют текстовый формат и мы не можем производить с ними математических действий (складывать, вычитать и т.п.). Нам требуется сначала преобразовать текст в число, для этого используйте функцию ЗНАЧЕН. Синтаксис у функции следующий:
ЗНАЧЕН(текст)
- Текст - число, представленное в текстовом формате
Как видно в примере выше, у нас есть число 12522, которое представлено в виде текста, при помощи функции ЗНАЧЕН мы преобразовали его в число 12 522, с которым в дальнейшем можем работать, как с любыми другими числами.
Спасибо, что дочитали статью. Я постарался выбрать 10 наиболее полезных функций в Excel, которые нужны при анализе данных. Жду ваши комментарии.
0 comments:
Отправить комментарий
Спасибо за комментарий.