КУРС

EXCEL ACADEMY

Научитесь использовать все прикладные инструменты из функционала MS Excel.
 

Что для работодателя главное в сотруднике? Добросовестность, ответственность, профессионализм и, конечно же, умение пользоваться отведенным временем!

Предлагаем познакомиться с очень нужными, на наш взгляд, возможностями, которые помогут вам сэкономить время, работая с данными в Excel.

1. Формула ОКРУГЛ()

Очень часто руководителям приходится заново подписывать уже подписанные документы или счета из-за того, что имеются копеечные, в прямом смысле этого слова, расхождения.

Это происходит, когда менеджеры, составляя расчеты и сметы по договорам в Экселе, не обращают внимание на то, что используют в формулах значения с «десятью» знаками после запятой.

Такое встречается очень часто, ведь в ячейках такие числа могут отображаться лишь с двумя знаками.

Например, число 1,1056978 в одной и той же ячейке может выглядеть по-разному:

Как сэкономить время с помощью Excel?

Мы просто скопировали одно число в 4 ячейки и уменьшили их размер. Несмотря на то, что в ячейке Е2 наше число 1,1056978 отображается как 1, в расчетах она будет участвовать, как 1,1056978.

Поэтому специалист, работая с огромной таблицей данных, может даже и не обратить внимание на то, какую точность значений он берёт.

Бухгалтеры работают в 1С с числами с двумя знаками после запятой. Отсюда и идут расхождения в копейках, создавая лишнюю работу и для менеджеров, и для бухгалтеров.

Чтобы предотвратить ненужную бумажную волокиту и поиск «виноватых в краже копеек», достаточно научиться использовать в повседневной работе функцию ОКРУГЛ() в Excel.

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

=ОКРУГЛ(число; число_разрядов), где число – значение, которое нужно округлить, число_разрядов – количество знаков после запятой.

Пример:

Как сэкономить время с помощью Excel?, image #2

После того, как проделаем округление с одним значением, можно распространить его на все ячейки.

Мы потратили всего лишь пару секунд на данную операцию, при этом сэкономив кучу времени сотрудников на поиск ошибки, ее исправление, распечатывание документов и так далее.

2. Функция ВПР()

Функция ВПР() незаменима в тех случаях, когда нужно сопоставлять данные из нескольких таблиц и объединять их особым образом. Благодаря ей можно находить необходимые данные в одной таблице и вставлять их в другую в соответствии с параметрами.

Разберем простенький пример по применению функции ВПР() в работе с таблицами.

У функции четыре параметра:
Искомое_значение – те значения, которые функция должна найти в таблице.
Таблица – название таблицы, в которой нужно обнаружить искомое значение.

Важно помнить, что поиск происходит по первому столбцу.

Номер_столбца – порядковый номер столбца относительно первого, откуда нужно копировать данные.
Интервальный просмотр – необязательный параметр, который принимает значение 0, если совпадение точное, и 1, если совпадение приближенное. В последнем случае значения должны быть сортированы по возрастанию.

Предположим, нам нужно составить накладную для заказчиков, в которой будет содержаться названия товаров, необходимое количество, цена за единицу товара и суммарная стоимость.

В нашем распоряжении есть таблица заказанного товара и прайс-лист:

Как сэкономить время с помощью Excel?, image #3
Как сэкономить время с помощью Excel?, image #4

Нам необходимо добавить в первую таблицу два столбика: «Цена» и «Стоимость». Но не будем же мы вручную переносить данные, когда в таблицах могут быть тысячи строк!

Поэтому воспользуемся функцией ВПР()!

Во вкладке «Формулы» найдем раздел «Ссылки и массивы» и выберем функцию ВПР().

В открывшемся окне введём аргументы:

Как сэкономить время с помощью Excel?, image #5

То есть функция ВПР() будет искать название материала (ячейки А2:А15) в первом столбике таблицы «Прайс-лист».

После того, как найдет, скопирует значение цены, соответствующее названию, и вставит в первую таблицу в столбец «Цена».

После применения функции таблица будет выглядеть следующим образом:

Как сэкономить время с помощью Excel?, image #6

Теперь несложно найти стоимость по формуле: С2*D2=Стоимость, распространив на весь столбец.

С помощью функции ВПР() можно быстро сравнивать две таблицы.

Например, есть два прайс-листа: один старый, другой новый, нужно проанализировать, как изменилась цена:

Как сэкономить время с помощью Excel?, image #7
Как сэкономить время с помощью Excel?, image #8

Добавляем к таблице со старой ценой столбец «Новая цена», применяем функцию ВПР():

Как сэкономить время с помощью Excel?, image #9
Как сэкономить время с помощью Excel?, image #10

Мы получили подходящую для анализа таблицу всего за пару минут!

Особенно выигрыш по времени ощутим, когда речь идет о сотнях, тысячах строк.

Замечание 1

Возможно, вы обратили внимание на то, что в предыдущем примере значение аргумента «Таблица» выражалось в виде диапазона, а здесь – в виде названия таблицы.

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

Делается это просто: выделяем таблицу, не включая название столбцов, жмем на правую кнопку мыши и выбираем «Присвоить имя».

Как сэкономить время с помощью Excel?, image #11

В открывшемся окошке вводим имя таблицы, жмем ОК, и готово!

Теперь достаточно в поле аргумента «Таблица» вводить этот заголовок. Это намного быстрее, чем выделять каждый раз огромные диапазоны.

Замечание 2

Очень часто во избежание сбоев и накопления ошибок при работе с функцией ВПР() используют функцию — ЕСЛИОШИБКА(). Эта функция способна находить ошибку в расчетах (первый аргумент – формула, которую вам нужно посчитать), и в случае обнаружения она выводит «0», пустую строчку, текстовую запись или любое другое значение, указанное во втором аргументе.

Это необходимо в тех случаях, когда есть риск появления ошибки #Н/Д (например, если недостаточно данных для поиска или нет соответствий).

Тогда можно использовать следующую комбинацию функций:

=ЕСЛИОШИБКА(ВПР(A3;прайс;2;ЛОЖЬ);0), которая будет ставить в ячейку 0 при появлении ошибки.

 

КУРС

EXCEL ACADEMY

Научитесь использовать все прикладные инструменты из функционала MS Excel.
 

3. Функция ВПР() для работы с выпадающими списками

Это тоже очень привлекательная возможность, которую предоставляет Excel.

Благодаря ей можно делать очень интересные таблицы, например, выпадающий список, в котором можно выбрать название фрукта и сразу узнать его цену.

Рассмотрим на примере.

Создадим две строки: «Название» и «Цена». Выделим ячейку рядом с названием. Заходим в раздел «Данные», выбираем «Проверка данных»:

Как сэкономить время с помощью Excel?, image #12

В открывшемся окне выбираем параметры: тип данных — «Список», в источник вставляем диапазон с названиями фруктов:

Как сэкономить время с помощью Excel?, image #13

Получаем выпадающий список, в котором можно выбирать разные значения. Теперь нужно настроить автоматический вывод цены.

Для этого ставим курсор в ячейку рядом с ячейкой «Цена» и вводим формулу:

Как сэкономить время с помощью Excel?, image #14

После выполнения функции мы получим то, что хотели:

Как сэкономить время с помощью Excel?, image #15

При изменении строки с названием изменится и цена:

Как сэкономить время с помощью Excel?, image #16

В Excel есть формула ГПР() – поиск по горизонтали. Эта функция очень похожа на ВПР(), за исключением того, что ГПР() ищет значения по строкам, а не по столбцам (вместо аргумента Номер_столбца в ГПР() – Номер_строки).

Например, таблица для расчета скидки покупателям:

Как сэкономить время с помощью Excel?, image #17

Мы видим, что в качестве Искомого_значения выступает значение прибыли, которую приносит покупатель, Таблица – диапазон D1:G2, Номер_строки – 2, Интервальный_ просмотр – ИСТИНА.

В итоге в таблице с уровнем прибыли ищется примерно соответствующее значение принесенной клиентом прибыли, и в качестве результата функции ГПР() в ячейку F5 выводится процент бонуса, соответствующий сопоставленному уровню прибыли.

То есть берется значение 100500 из Е5, и сравнивается со значениями ячеек D1:G1.

Находится приближенное значение – 100000 в ячейке Е1 и берется соответствующее этой ячейке значение процентов – 10% из ячейки Е2.

Это число и будет результатом функции ГПР() в ячейке F5.

Замечание. Функции ВПР() и ГПР() можно применять только к одномерным массивам.

4 и 5. Функции ПОИСКПОЗ() и ИНДЕКС()

Мы решили познакомить вас с альтернативой функции ВПР(), которая в некоторых случаях (например, при многомерном выборе) бывает более полезной.

Познакомимся с каждой по отдельности.

Функция ПОИСКПОЗ() предназначена для поиска индекса ячейки, в которой находится необходимое значение.

То есть, вы вводите искомое значение; диапазон, в котором требуется найти это значение и тип сопоставления:

1 – наибольшее из значений, которое не превышает искомое.
0 – полностью совпадает.
-1 – наименьшее значение из тех, что превышают искомое, а нам выводят порядковый номер этого значения.

Замечание 1. В качестве просматриваемого массива может выступать или одна строка, или один столбец.

Замечание 2. Для текстовых строк подходит только тип сопоставления — 0.

Применим функцию ПОИСПОЗ() к нашей фруктовой таблице. Будем искать позицию «персиков» в массиве названий:

Как сэкономить время с помощью Excel?, image #18

Результатом функции будет число 4.

То есть значение «персики» в массиве названий находится на 4 месте:

Как сэкономить время с помощью Excel?, image #19

Теперь поговорим о функции ИНДЕКС(). С помощью нее можно находить значение, находящееся на пересечении указанной строки и столбца.

На примере видно, как работает функция:

Как сэкономить время с помощью Excel?, image #20

В диапазоне А1:С4 находим значение ячейки, находящееся в третьей строке, во втором столбце.

Отсчет номера строки и столбца начинается с верхней левой ячейки.

Замечание. Если в качестве диапазона указан вектор (строка или столбец), то в функции ИНДЕКС() задается два аргумента: диапазон вектора и порядковый номер значения:

Как сэкономить время с помощью Excel?, image #21

Теперь, узнав о каждой функции, посмотрим, как сделать их супер-информативными.

Связка функций ПОИСКПОЗ() и ИНДЕКС() оказывается очень эффективной, когда необходимо произвести поиск по двум параметрам.

Предположим, у нас есть таблица с месячными объемами продаж четырех видов товара. Нам нужно получить сумму продаж, указав месяц и тип товара.

Это можно сделать с помощью функции ВПР(), например, сумма за продажу овощей в марте:

=ВПР(А3;А2:Е13;3;0)

Получим результат ячейки С4:

Как сэкономить время с помощью Excel?, image #22

Но посмотрим, как получить результат по-другому. Для удобства выпишем отдельно интересующий нас месяц, тип товара и отведем ячейку под сумму продаж, в которую будем вставлять формулу.

Находим в столбце с месяцами номер нужного нам месяца по формуле ПОИСКПОЗ(C15;A2:A13;0), затем номер интересующего нас столбца в массиве типов товаров ПОИСКПОЗ(C16;B1:E1;0).

Теперь находим ячейку на пересечении строки «Май» и столбца «Овощи» с помощью формулы:

=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))

Как сэкономить время с помощью Excel?, image #23

Достаточно громоздкая формула, намного сложнее, чем ВПР(), но зато комбинацию ИНДЕКС() и ПОИСКПОЗ() можно использовать, когда нужно совершать поиск данных по двум критериям.

Мы рассмотрели не так много функций, но и их достаточно, чтобы начать оптимизировать свое рабочее время!

Если вас заинтересовала тема и вам хочется узнать больше о полезном функционале Excel, то рекомендуем записаться на наш открытый онлайн-курс «Аналитика в Excel».

 

КУРС

EXCEL ACADEMY

Научитесь использовать все прикладные инструменты из функционала MS Excel.