Формулы электронных таблиц, которые должен знать каждый
Электронные таблицы — один из самых мощных инструментов для работы с данными, но многие пользователи ограничиваются простейшими операциями. Знание ключевых формул позволяет автоматизировать вычисления, находить нужную информацию в больших массивах данных и обрабатывать текст. В этой статье мы рассмотрим формулы, которые пригодятся каждому, от бухгалтера до менеджера проектов.
SUM, AVERAGE и COUNT: основа вычислений
Три базовые функции, с которых начинается работа с любой таблицей. SUM складывает значения в указанном диапазоне: =SUM(A1:A100) мгновенно посчитает сумму ста ячеек. AVERAGE вычисляет среднее арифметическое, что незаменимо для анализа показателей: средний чек, средняя зарплата, средний балл. COUNT подсчитывает количество ячеек с числовыми данными, а COUNTA считает все непустые ячейки, включая текстовые. Каждая из этих функций имеет условную версию: SUMIF, AVERAGEIF, COUNTIF — они выполняют вычисления только для строк, соответствующих заданному критерию. Например, =SUMIF(B1:B100,"Москва",C1:C100) посчитает сумму только для строк, где город — Москва.
VLOOKUP и INDEX-MATCH: поиск данных
VLOOKUP (ВПР) — одна из самых популярных функций для поиска данных в таблице. Она ищет значение в первом столбце указанного диапазона и возвращает значение из соответствующей строки в другом столбце. Например, =VLOOKUP("A-1001",Товары!A:D,3,FALSE) найдёт товар с артикулом A-1001 и вернёт его цену из третьего столбца. Однако VLOOKUP имеет ограничения: ищет только слева направо и работает медленно с большими таблицами. Комбинация INDEX-MATCH более гибкая и мощная: =INDEX(C:C,MATCH("A-1001",A:A,0)) делает то же самое, но позволяет искать в любом направлении. Освоение INDEX-MATCH — это переход от начинающего к уверенному пользователю электронных таблиц.
IF и IFS: условные вычисления
Функция IF позволяет выполнять разные действия в зависимости от условия. Простой пример: =IF(A1>=60,"Сдал","Не сдал") выводит результат экзамена на основе набранных баллов. Вложенные IF позволяют проверять несколько условий: =IF(A1>=90,"Отлично",IF(A1>=70,"Хорошо",IF(A1>=60,"Удовлетворительно","Неудовлетворительно"))). Однако вложенные IF быстро становятся трудночитаемыми, поэтому для множественных условий лучше использовать функцию IFS: =IFS(A1>=90,"Отлично",A1>=70,"Хорошо",A1>=60,"Удовлетворительно",TRUE,"Неудовлетворительно"). Также полезны AND и OR для составных условий: =IF(AND(A1>0,B1>0),"Прибыль","Убыток") проверяет два условия одновременно.
TEXT, CONCATENATE, LEFT, RIGHT и MID: работа с текстом
Текстовые функции незаменимы при обработке данных, полученных из разных источников. CONCATENATE (или оператор &) объединяет текст: =A1&" "&B1 соединит имя и фамилию через пробел. LEFT, RIGHT и MID извлекают части текста: =LEFT(A1,3) вернёт первые три символа, =RIGHT(A1,4) — последние четыре, =MID(A1,5,3) — три символа начиная с пятого. Функция TEXT форматирует числа и даты как текст: =TEXT(TODAY(),"DD.MM.YYYY") выведет текущую дату в нужном формате. TRIM удаляет лишние пробелы, UPPER и LOWER меняют регистр, а SUBSTITUTE заменяет одну подстроку другой. Эти функции особенно полезны при очистке данных, импортированных из внешних систем.
Функции для работы с датами
Правильная работа с датами — частая головная боль пользователей электронных таблиц. Функция TODAY() возвращает текущую дату, а NOW() — дату и время. DATEDIF вычисляет разницу между датами в днях, месяцах или годах: =DATEDIF(A1,TODAY(),"Y") покажет, сколько полных лет прошло с даты в ячейке A1. EDATE сдвигает дату на заданное количество месяцев: =EDATE(A1,3) вернёт дату через три месяца. WEEKDAY определяет день недели, EOMONTH — последний день месяца, а NETWORKDAYS подсчитывает рабочие дни между двумя датами. Для финансовых расчётов особенно полезны YEARFRAC и DAYS360. Помните, что в электронных таблицах даты хранятся как числа, поэтому с ними можно выполнять арифметические операции: =A1+30 прибавит к дате 30 дней.