Что для работодателя главное в сотруднике? Добросовестность, ответственность, профессионализм и, конечно же, умение пользоваться отведенным временем!
Предлагаем познакомиться с очень нужными, на наш взгляд, возможностями, которые помогут вам сэкономить время, работая с данными в Excel.
1. Формула ОКРУГЛ()
Очень часто руководителям приходится заново подписывать уже подписанные документы или счета из-за того, что имеются копеечные, в прямом смысле этого слова, расхождения.
Это происходит, когда менеджеры, составляя расчеты и сметы по договорам в Экселе, не обращают внимание на то, что используют в формулах значения с «десятью» знаками после запятой.
Такое встречается очень часто, ведь в ячейках такие числа могут отображаться лишь с двумя знаками.
Например, число 1,1056978 в одной и той же ячейке может выглядеть по-разному:
Мы просто скопировали одно число в 4 ячейки и уменьшили их размер. Несмотря на то, что в ячейке Е2 наше число 1,1056978 отображается как 1, в расчетах она будет участвовать, как 1,1056978.
Поэтому специалист, работая с огромной таблицей данных, может даже и не обратить внимание на то, какую точность значений он берёт.
Бухгалтеры работают в 1С с числами с двумя знаками после запятой. Отсюда и идут расхождения в копейках, создавая лишнюю работу и для менеджеров, и для бухгалтеров.
Чтобы предотвратить ненужную бумажную волокиту и поиск «виноватых в краже копеек», достаточно научиться использовать в повседневной работе функцию ОКРУГЛ() в Excel.
Чтобы воспользоваться формулой, напишем ее в строку ввода функций:
=ОКРУГЛ(число; число_разрядов), где число – значение, которое нужно округлить, число_разрядов – количество знаков после запятой.
Пример:
После того, как проделаем округление с одним значением, можно распространить его на все ячейки.
Мы потратили всего лишь пару секунд на данную операцию, при этом сэкономив кучу времени сотрудников на поиск ошибки, ее исправление, распечатывание документов и так далее.
2. Функция ВПР()
Функция ВПР() незаменима в тех случаях, когда нужно сопоставлять данные из нескольких таблиц и объединять их особым образом. Благодаря ей можно находить необходимые данные в одной таблице и вставлять их в другую в соответствии с параметрами.
Разберем простенький пример по применению функции ВПР() в работе с таблицами.
У функции четыре параметра:
Искомое_значение – те значения, которые функция должна найти в таблице.
Таблица – название таблицы, в которой нужно обнаружить искомое значение.
Важно помнить, что поиск происходит по первому столбцу.
Номер_столбца – порядковый номер столбца относительно первого, откуда нужно копировать данные.
Интервальный просмотр – необязательный параметр, который принимает значение 0, если совпадение точное, и 1, если совпадение приближенное. В последнем случае значения должны быть сортированы по возрастанию.
Предположим, нам нужно составить накладную для заказчиков, в которой будет содержаться названия товаров, необходимое количество, цена за единицу товара и суммарная стоимость.
В нашем распоряжении есть таблица заказанного товара и прайс-лист:
Нам необходимо добавить в первую таблицу два столбика: «Цена» и «Стоимость». Но не будем же мы вручную переносить данные, когда в таблицах могут быть тысячи строк!
Поэтому воспользуемся функцией ВПР()!
Во вкладке «Формулы» найдем раздел «Ссылки и массивы» и выберем функцию ВПР().
В открывшемся окне введём аргументы:
То есть функция ВПР() будет искать название материала (ячейки А2:А15) в первом столбике таблицы «Прайс-лист».
После того, как найдет, скопирует значение цены, соответствующее названию, и вставит в первую таблицу в столбец «Цена».
После применения функции таблица будет выглядеть следующим образом:
Теперь несложно найти стоимость по формуле: С2*D2=Стоимость, распространив на весь столбец.
С помощью функции ВПР() можно быстро сравнивать две таблицы.
Например, есть два прайс-листа: один старый, другой новый, нужно проанализировать, как изменилась цена:
Добавляем к таблице со старой ценой столбец «Новая цена», применяем функцию ВПР():
Мы получили подходящую для анализа таблицу всего за пару минут!
Особенно выигрыш по времени ощутим, когда речь идет о сотнях, тысячах строк.
Замечание 1
Возможно, вы обратили внимание на то, что в предыдущем примере значение аргумента «Таблица» выражалось в виде диапазона, а здесь – в виде названия таблицы.
Действительно, порой удобнее не выделять необходимый диапазон для поиска, а называть имя таблицы, в которой нужно искать.
Делается это просто: выделяем таблицу, не включая название столбцов, жмем на правую кнопку мыши и выбираем «Присвоить имя».
В открывшемся окошке вводим имя таблицы, жмем ОК, и готово!
Теперь достаточно в поле аргумента «Таблица» вводить этот заголовок. Это намного быстрее, чем выделять каждый раз огромные диапазоны.
Замечание 2
Очень часто во избежание сбоев и накопления ошибок при работе с функцией ВПР() используют функцию – ЕСЛИОШИБКА(). Эта функция способна находить ошибку в расчетах (первый аргумент – формула, которую вам нужно посчитать), и в случае обнаружения она выводит «0», пустую строчку, текстовую запись или любое другое значение, указанное во втором аргументе.
Это необходимо в тех случаях, когда есть риск появления ошибки #Н/Д (например, если недостаточно данных для поиска или нет соответствий).
Тогда можно использовать следующую комбинацию функций:
=ЕСЛИОШИБКА(ВПР(A3;прайс;2;ЛОЖЬ);0), которая будет ставить в ячейку 0 при появлении ошибки.
3. Функция ВПР() для работы с выпадающими списками
Это тоже очень привлекательная возможность, которую предоставляет Excel.
Благодаря ей можно делать очень интересные таблицы, например, выпадающий список, в котором можно выбрать название фрукта и сразу узнать его цену.
Рассмотрим на примере.
Создадим две строки: «Название» и «Цена». Выделим ячейку рядом с названием. Заходим в раздел «Данные», выбираем «Проверка данных»:
В открывшемся окне выбираем параметры: тип данных – «Список», в источник вставляем диапазон с названиями фруктов:
Получаем выпадающий список, в котором можно выбирать разные значения. Теперь нужно настроить автоматический вывод цены.
Для этого ставим курсор в ячейку рядом с ячейкой «Цена» и вводим формулу:
После выполнения функции мы получим то, что хотели:
При изменении строки с названием изменится и цена:
В Excel есть формула ГПР() – поиск по горизонтали. Эта функция очень похожа на ВПР(), за исключением того, что ГПР() ищет значения по строкам, а не по столбцам (вместо аргумента Номер_столбца в ГПР() – Номер_строки).
Например, таблица для расчета скидки покупателям:
Мы видим, что в качестве Искомого_значения выступает значение прибыли, которую приносит покупатель, Таблица – диапазон D1:G2, Номер_строки – 2, Интервальный_ просмотр – ИСТИНА.
В итоге в таблице с уровнем прибыли ищется примерно соответствующее значение принесенной клиентом прибыли, и в качестве результата функции ГПР() в ячейку F5 выводится процент бонуса, соответствующий сопоставленному уровню прибыли.
То есть берется значение 100500 из Е5, и сравнивается со значениями ячеек D1:G1.
Находится приближенное значение – 100000 в ячейке Е1 и берется соответствующее этой ячейке значение процентов – 10% из ячейки Е2.
Это число и будет результатом функции ГПР() в ячейке F5.
Замечание. Функции ВПР() и ГПР() можно применять только к одномерным массивам.
4 и 5. Функции ПОИСКПОЗ() и ИНДЕКС()
Мы решили познакомить вас с альтернативой функции ВПР(), которая в некоторых случаях (например, при многомерном выборе) бывает более полезной.
Познакомимся с каждой по отдельности.
Функция ПОИСКПОЗ() предназначена для поиска индекса ячейки, в которой находится необходимое значение.
То есть, вы вводите искомое значение; диапазон, в котором требуется найти это значение и тип сопоставления:
1 – наибольшее из значений, которое не превышает искомое.
0 – полностью совпадает.
-1 – наименьшее значение из тех, что превышают искомое, а нам выводят порядковый номер этого значения.
Замечание 1. В качестве просматриваемого массива может выступать или одна строка, или один столбец.
Замечание 2. Для текстовых строк подходит только тип сопоставления – 0.
Применим функцию ПОИСПОЗ() к нашей фруктовой таблице. Будем искать позицию «персиков» в массиве названий:
Результатом функции будет число 4.
То есть значение «персики» в массиве названий находится на 4 месте:
Теперь поговорим о функции ИНДЕКС(). С помощью нее можно находить значение, находящееся на пересечении указанной строки и столбца.
На примере видно, как работает функция:
В диапазоне А1:С4 находим значение ячейки, находящееся в третьей строке, во втором столбце.
Отсчет номера строки и столбца начинается с верхней левой ячейки.
Замечание. Если в качестве диапазона указан вектор (строка или столбец), то в функции ИНДЕКС() задается два аргумента: диапазон вектора и порядковый номер значения:
Теперь, узнав о каждой функции, посмотрим, как сделать их супер-информативными.
Связка функций ПОИСКПОЗ() и ИНДЕКС() оказывается очень эффективной, когда необходимо произвести поиск по двум параметрам.
Предположим, у нас есть таблица с месячными объемами продаж четырех видов товара. Нам нужно получить сумму продаж, указав месяц и тип товара.
Это можно сделать с помощью функции ВПР(), например, сумма за продажу овощей в марте:
=ВПР(А3;А2:Е13;3;0)
Получим результат ячейки С4:
Но посмотрим, как получить результат по-другому. Для удобства выпишем отдельно интересующий нас месяц, тип товара и отведем ячейку под сумму продаж, в которую будем вставлять формулу.
Находим в столбце с месяцами номер нужного нам месяца по формуле ПОИСКПОЗ(C15;A2:A13;0), затем номер интересующего нас столбца в массиве типов товаров ПОИСКПОЗ(C16;B1:E1;0).
Теперь находим ячейку на пересечении строки «Май» и столбца «Овощи» с помощью формулы:
=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))
Достаточно громоздкая формула, намного сложнее, чем ВПР(), но зато комбинацию ИНДЕКС() и ПОИСКПОЗ() можно использовать, когда нужно совершать поиск данных по двум критериям.
Мы рассмотрели не так много функций, но и их достаточно, чтобы начать оптимизировать свое рабочее время!
Если вас заинтересовала тема и вам хочется узнать больше о полезном функционале Excel, то рекомендуем записаться на наш открытый онлайн-курс «Аналитика в Excel».