data_client

Технологии Blogger.

10 наиболее полезных функций при анализе данных в Excel

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

1. ВПР

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

ВПР (искомое значение; таблица; номер столбца; интервальный просмотр)

- Искомое значение - это то значение, которое мы будем искать в таблице с данными

- Таблица - диапазон данных, в первом столбце которого мы будем искать искомое значение

- Номер столбца - этот параметр обозначает, на какое количество столбцов надо сдвинуться вправо в таблице для получения результата

- Интервальный просмотр - Может принимать параметр 0 или ЛОЖЬ, что обозначает что совпадение между искомым значением и значением в первом столбце таблицы должен быть точным; либо 1 или ИСТИНА, соответственно совпадение должно быть неточным. Настоятельно рекомендую использовать только параметр ЛОЖЬ, иначе можно получать непредсказуемые результаты.

10 наиболее полезных функций при анализе данных в Excel
В примере выше мы ищем по фамилии Петров имя в таблице с базой данных по ФИО. В функции ВПР(E2;A1:C6;2;0) первый параметр (E2) - ссылка на ячейку с фамилией, по которой мы будем искать имя; второй параметр A1:C6 - ссылка на таблицу, в первом столбце которой мы ищем указанное в первом параметре значение; третий параметр "2" - из какого столбца справа извлекать значение; четвертый параметр "0" - точный поиск.

Если хотите изучить более подробно, как работает функция ВПР, прочитайте нашу статью "Функция ВПР в Excel".

2. ГПР

Функция ГПР выполняет туже задачу, что и ВПР, только она просматривает первую строку в поиске искомого значения и для получения результата сдвигается на указанное количество строк вниз.

10 наиболее полезных функций при анализе данных в Excel
Синтаксис функции следующий:
ГПР(искомое значение;таблица;номер строки;интервальный просмотр)
- Искомое значение - значение, которое мы ищем в строке.
- Таблица- диапазон данных на листе, где в первой строке мы ищем искомое значение и сдвигаемся на необходимое количество строк.
- Номер строки- числовое значение, указывающее на сколько строк вниз надо сместиться.
- Интервальный просмотр - ставьте всегда 0, тогда Эксель будет искать точное совпадение, что нам и нужно в большинстве случаев.

В примере выше мы ищем выручку за сентябрь в помесячном отчете по выручке. В формуле ГПР(A5;B1:M2;2;0) первый параметр (А5) - ссылка на месяц, по которому мы хотим получить выручку; второй параметр (B1:M2) - ссылка на таблицу, где в первой строке указаны месяцы, среди которых нам нужно найти выбранный; третий параметр "2" - из какой строки ниже мы будем получать данные; четвертый параметр "0" - ищем точное совпадение.

Если вы хотите более подробно изучить, как пользоваться функцией ГПР - прочитайте статью на нашем сайте "Функция ГПР в Excel".

3. ЕСЛИ

Функция ЕСЛИ является очень популярной в Excel. Она позволяет автоматически выполнять какое-либо действие, в зависимости от поставленного условия.

10 наиболее полезных функций при анализе данных в Excel

Функция ЕСЛИ выполняет проверку логического выражения и если выражение истинно, то  поставляется одно значение и альтернативное, если ложь. Синтаксис следующий:

ЕСЛИ(логическое выражение; значение если истина; значение если ложь)

- Логическое выражение - выражение, которое по итогу своего вычисления должно вырнуться значение ИСТИНА или ЛОЖЬ.
- Значение, если истина - устанавливаем указанное значение, если логическое выражение вернуло ИСТИНА
- Значение, если ложь - устанавливает указанное значение, если логическое выражение вернуло ЛОЖЬ.

В примере выше мы хотим определить, получили ли мы за месяц выручку больше 500 рублей или нет. В формуле  ЕСЛИ(B2>500;"Да";"Нет") первый параметр (B2>500) проверяет, выручка за месяц больше 500 рублей или нет; второй параметр ("Да") - функция вернет Да, если выручка больше 500 рублей и соответственно Нет (третий параметр), если выручка меньше.

Обратите внимание, что значения при истине или лжи могут быть не только текстовые, числовые, но также и функции(в том числе и ЕСЛИ), что позволяет реализовать достаточно сложные логические конструкции.

4. ЕСЛИОШИБКА

При работе с формулами в Excel, можно время от времени сталкиваться с различными ошибками. Так в примере ниже функция ВПР вернула ошибку #Н/Д из-за того, что в базе данных по ФИО нет искомой нами фамилии (более подробно об ошибке #Н/Д вы можете прочитать в этой статье: "Как исправить ошибку #Н/Д в Excel")

10 наиболее полезных функций при анализе данных в Excel

Для обработки таких ситуаций отлично подойдет функция ЕСЛИОШИБКА. Ее синтаксис следующий:

ЕСЛИОШИБКА(значение; значение если ошибка)

- Значение, результат которого проверяется на ошибку.
- Значение, если ошибка - В случае, если в результате работы функции получаем ошибку, то выводится не ошибка, а данное значение.

В случае с нашим примером выше, мы можем предположить, что фамилия может быть некорректной, соответственно ЕСЛИОШИБКА вернет нам предупреждение, что бы мы проверили написание фамилии.

10 наиболее полезных функций при анализе данных в Excel
В примере выше, мы проверяем результат работы функции ВПР(E2;A1:C6;2;0) и в случае, если вернется ошибка, то выдаем сообщение "Проверьте фамилию!".

5. СУММЕСЛИМН

Функция СУММЕСЛИМН позволяет суммировать значения по определенным условиям. Условий может быть несколько. В Excel также есть функция СУММЕСЛИ, которая позволяет суммировать по одному критерию. Призываю вас использовать более универсальную формулу.

10 наиболее полезных функций при анализе данных в Excel

У функции СУММЕСЛИМН следующий синтаксис:

СУММЕСЛИМН(Диапазон суммирования; Диапазон условия 1; Условие 1;...)

- Диапазон суммирования - область листа Эксель, из которой мы суммируем данные
- Диапазон условия 1 - Диапазон ячеек, которые мы проверяем на соответствие условию
- Условие 1 - Условие, которое проверяется на соответствие в Диапазоне 1.
Обратите внимание, что диапазонов условий и соответственно условий может быть столько, сколько вам нужно.

Для примера выше мы хотим получит выручку, которую принес нам Петров в городе Москва. Формула имеет вид СУММЕСЛИМН(C2:C13;A2:A13;E2;B2:B13;F2), где C2:C13 - диапазон со значениями выручки, которые необходимо просуммировать; А2:А13 - диапазон с фамилиями, которые мы будем проверять; Е2 - ссылка на конкретную фамилию; B2:B13 - ссылка на диапазон с городами; F2 - ссылка на конкретный город.

6. СЧЁТЕСЛИМН

СЧЁТЕСЛИМН очень похожа на функцию СУММЕСЛИМН, только в отличии от нее, она не суммируется значения, а только считает количество ячеек, которые соответствуют определенным условиям. Как и в случае с СУММЕСЛИМН, у СЧЁТЕСЛИМН есть упрощенная форма СЧЁТЕСЛИ, который считает количество ячеек только по одному критерию, но лучше используйте более общий вариант.

10 наиболее полезных функций при анализе данных в Excel

Синтаксис у функции следующий:

СЧЁТЕСЛИМН(диапазон условия 1; условие 1;...)

- Диапазон условия 1 - Диапазон ячеек, которые проверяются на соответствие определенному условию.
- Условие 1 - Условие, которое определяет какие ячейки надо учитывать при подсчете.
Обратите внимания, что диапазонов условий и соответственно условий может быть несколько.

В примере выше, мы считаем сколько в таблице ячеек, в которых фамилия - Петров, а город - Москва. В формуле СЧЁТЕСЛИМН(A2:A13;E2;B2:B13;F2) диапазон A2:A13  - диапазон фамилий, которые мы проверяем, Е2 - та фамилия, которую мы ищем в диапазоне; B2:B13 - диапазон городов и соответственно F2 - город, который мы учитываем при подсчете ячеек. Получившееся число 3 - это количество строк в таблице, где фамилия равна Иванов, а город равен Москва.

7. СЖПРОБЕЛЫ

При работе с данными в Excel, мы можем получать их из разных источников, что может привести к тому, что получаемые значения имеют "мусорную" информацию, очень часто это лишние пробелы, которые надо удалить. Можно удалять вручную, но это долго и муторно. На выручку нам приходит функция СЖПРОБЕЛЫ, которая удаляет лишние пробелы, в случае если их больше одного подряд. Синтаксис у функции очень простой:

СЖПРОБЕЛЫ(текст)

- Текст - тот текст, из которого надо убрать лишние пробелы.

10 наиболее полезных функций при анализе данных в Excel

Как видно из примера выше, функция успешно удалила лишние пробелы из исходной строки.

8. ЛЕВСИМВ и ПРАВСИМВ

Функции ЛЕВСИМВ и ПРАВСИМВ возвращают определенное количество знаков с начала (ЛЕВСИМВ) либо с конца (ПРАВСИМВ) строки. Эти функции нужны для получения части строки. Синтаксис у функций однотипный:

ЛЕВСИМВ(текст; количество знаков)
ПРАВСИМВ(текст; количество знаков)

- Текст - то строковое выражение, из которого мы хотим получить часть.
- Количество знаков - число символов, которое мы хотим получить.

10 наиболее полезных функций при анализе данных в Excel

В примере выше мы из текста "Пример текста" извлекаем 6 символов слева и получаем текст "Пример".

9. СЦЕПИТЬ

Функция СПЕПИТЬ позволяет объединить значения из нескольких ячеек. Синтаксис у функции достаточно простой:

СЦЕПИТЬ(текст1; текст2;...)

- Текст 1 - Текст, который надо соединить в одну строку
- Текст 2 - Текст, который надо соединить в одну строку
Обратите внимание, что вы можете объединить до 255 текстовых значений.

10 наиболее полезных функций при анализе данных в Excel

В примере выше мы объединяем фамилию и имя. В функции СЦЕПИТЬ(A2;" ";B2), первый параметр(А2) - ссылка на ячейку с фамилией; второй параметр (" ") - пробел, что бы итоговый текст смотрелся нормально; третий параметр(В2) - ссылка на ячейку с именем.

10.ЗНАЧЕН

Часто данные, которые мы получаем из внешних источников, имеют текстовый формат и мы не можем производить с ними математических действий (складывать, вычитать и т.п.). Нам требуется сначала преобразовать текст в число, для этого используйте функцию ЗНАЧЕН. Синтаксис у функции следующий:

ЗНАЧЕН(текст)

- Текст - число, представленное в текстовом формате

10 наиболее полезных функций при анализе данных в Excel


Как видно в примере выше, у нас есть число 12522, которое представлено в виде текста, при помощи функции ЗНАЧЕН мы преобразовали его в число 12 522, с которым в дальнейшем можем работать, как с любыми другими числами.

Спасибо, что дочитали статью. Я постарался выбрать 10 наиболее полезных функций в Excel, которые нужны при анализе данных. Жду ваши комментарии.

Опубликовать в Google Plus