Excel является неотъемлемой частью работы любого офиса. Эта программа автоматизирует многие математические, статистические и логические задачи. В статье мы расскажем о пяти полезных формулах, которые могут пригодиться каждому.
№1. ВПР
Функция ВПР ищет определенную информацию в указанных таблице и столбце и отображает ее в выбранной ячейке. ВПР помогает, например, отыскать в справочной таблице номер телефона сотрудника по его фамилии или установить размер скидки для определенного клиента, исходя из количества купленной им продукции и заранее рассчитанных ставок.
Синтаксис функции выглядит следующим образом:
ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр).
Разберем ее на примере.
Чтобы рассчитать итоговую стоимость закупаемых товаров, необходимо указать цену. Для этого введем в ячейку, где должна быть информация, формулу:
=ВПР(Е2;$А$2:$B$12;2;ЛОЖЬ).
Этим мы даем команду: найти значение из ячейки Е2 («Морковь») в левом столбце указанного диапазона (т. е. первой таблицы), взять информацию из той же строки во втором столбце («Цена/кг», на что указывает третий аргумент = 2) и отразить ее в выделенной ячейке. Четвертый аргумент дает указания, какой именно вести поиск — точный или приближенный.
Далее мы просто копируем формулу из G2 в G3:G6 и видим наши цены.
№2. ЕСЛИ
Этот логический оператор автоматически выставляет значения в выбранной ячейке в зависимости от соблюдения или несоблюдения указанного условия. В первом случае ЕСЛИ выдаст истинное значение, во втором — ложное, оба они также указываются пользователем.
Функция выглядит так:
ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь).
Предположим, нам требуется определить, кто из студентов допущен к экзамену.
Введем следующую формулу:
=ЕСЛИ(B2="зач.";"допуск";"недопуск").
Этим мы сообщаем Excel, как нужно действовать: при условии, что значение ячейки B2 соответствует «зач.», в указанной графе должно отразиться слово «допуск», в ином случае — «недопуск».
Дальше остается перенести формулу в остальные ячейки.
Таким образом можно, например, определить, прошел ли сотрудник аттестацию в зависимости от набранных им баллов, или узнать, сколько будет стоить билет для определенного человека, исходя из его возраста. Также функция ЕСЛИ используется при создании бухгалтерских отчетов.
№3. ЕСЛИОШИБКА
Функция ЕСЛИОШИБКА позволяет «перехватывать» ошибки в других формулах и выводить вместо них заданное пользователем значение. В ней всего два аргумента:
ЕСЛИОШИБКА(значение;значение_если_ошибка).
Проще говоря, она скрывает ошибки, чем может вылечить целые их цепочки.
В нашем примере Excel выдал значение «ДЕЛ/0!», потому что в ячейке задана функция вычисления среднего, которая подразумевает под собой деление, а так как оба аргумента равняются нулю, действие не может быть выполнено. Из-за этого невозможно посчитать и итоги.
Команда ЕСЛИОШИБКА позволяет устранить возникшую проблему. Вводим:
=ЕСЛИОШИБКА(C2/B2;"")
и теперь ошибка замаскирована под пустую графу, которая не мешает провести итоговое вычисление. Такая функция придает отчетам более презентабельный вид.
№4. СЧЕТЕСЛИ
Статистическая функция, которая подсчитывает ячейки в заданном диапазоне по определенному критерию. Она используется для анализа числовых значений, текстовых строк, дат и данных другого типа. Например, помогает узнать, продукцию какой фирмы клиенты покупают чаще, определить, сколько раз определенный человек заходил на сайт, или найти число именинников среди сотрудников, чтобы их поздравить.
Синтаксис следующий:
СЧЕТЕСЛИ(диапазон;критерий).
Допустим, мы хотим выяснить, сколько видов столов имеется в нашем ассортименте. Задаем команду:
=СЧЕТЕСЛИ(A2:A11;"*стол*"),
где знаки * указывают на любое количество символов до и после подстроки «стол». То есть оператор должен отразить все значения, в которых она содержится, включая графу «Журнальный столик».
Это полезный инструмент для статистического анализа.
№5. ДЛСТР
Логический оператор ДЛСТР относится к текстовым. Он подсчитывает количество символов в указанной ячейке или диапазоне с учетом пробелов. Это позволяет контролировать длину текстовых строк в таблицах, выделять те из них, что не соответствуют пороговым значениям.
Скелет функции довольно прост:
ДЛСТР(текст).
Аргумент указывает на определенную графу. Чаще всего ее используют в связке с другими функциями, чтобы, к примеру, извлечь расширение из имени файла в ячейке, или узнать, сколько раз конкретное слово встречается в тексте.