10 формул, которые превратят вас в профессионала в Excel (фото)

Многим из нас приходится работать в Excel, потому что эту программу у нас издавна полюбили бухгалтеры и прочие составители отчётности.

Но далеко не все знают об этих секретах, которые значительно ускорят вашу работу!

1. Формула СУММ.

Эта формула позволяет суммировать числа.

Формула: =СУММ(5,5) или =СУММ(A1, B1) или =СУММ(A1:B5).

Выше приведены три варианта использования: если указать числа через запятую, они суммируются; если указать номера ячеек с числовыми значениями, эти числа суммируются; а если указать диапазон числовых ячеек через двоеточие, указанные в них числа опять-таки суммируются.

Сочетание клавиш: Alt + =. Это сочетание автоматически выделяет все ячейки над формулой вплоть до пустой ячейки. Если применить его к отфильтрованной колонке, отобразится сумма отфильтрованных значений в колонке.

2. Формула СЧЁТ.

Эта формула подсчитывает количество ячеек в диапазоне, содержащих числа.

Она не работает, если в ячейках указаны НЕ числа.

Формула: =СЧЁТ(A1:A10).

3. Формула СЧЁТЗ.

Подсчитывает количество заполненных ячеек в диапазоне. Содержание ячеек (числа или буквы) не имеет значения.

Формула: =СЧЁТЗ (A1:A10).

4. Формула ДЛСТР.

Эта формула считает количество символов в ячейках. Учтите, что пробелы тоже считаются за символы!

Формула: =ДЛСТР(A1).

Обратите внимание на разницу в итогах (на фото): 10 символов без пробелов, 12 — с пробелами.

5. Формула СЖПРОБЕЛЫ.

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

Лишние пробелы довольно часто прокрадываются в базы данных.

Это приводит к путанице при попытке использовать функции ЕСЛИ или ВПР.

Формула: =СЖПРОБЕЛЫ (A1).

Я добавил лишний пробел после слов «I love Excel»; формула СЖПРОБЕЛЫ его удалила. Обратите внимание на количество символов до и после.

6. Функции ПРАВСИМВ, ЛЕВСИМВ и MID.

Эти формулы отображают заданное количество символов в текстовой строке.

ПРАВСИМВ отображает заданное количество символов справа.

ЛЕВСИМВ отображает заданное количество символов слева.

MID отображает заданное количество символов из середины.

Формула: =ПРАВСИМВ (ссылка на текст или ячейку, количество символов), =ЛЕВСИМВ (ссылка на текст или ячейку, количество символов), =MID(ссылка на текст или ячейку, начальный номер, количество символов).

7. Формула ВПР.

Самая популярная формула во многих офисах.

Она ищет значение в первом столбце другой таблицы или массива и возвращает значение из ячейки в найденной строке и указанном столбце.

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

Формула: =ВПР(искомое_значение, таблица, номер_столбца, интервальный_просмотр).

Давайте разберём её по частям:

а) искомое_значение — значение для поиска. Искомое значение должно находиться в первом столбце диапазона ячеек, указанного в поле «таблица».

б) таблица — диапазон ячеек, в котором будет выполнен поиск «искомого значения» и возвращаемого значения.

в) номер_столбца — номер столбца, содержащий возвращаемое значение.

г) интервальный_просмотр — логическое значение, определяющее, какое совпадение должна найти функция ВПР, — точное (ЛОЖЬ) или приблизительное (ИСТИНА).

8. Формула ЕСЛИ.

Это тоже очень популярная формула.

Формула: =ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь).

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

Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй — если сравнение ложно.

Давайте разберём формулу по частям:

а) лог_выражение — это условие, которое нужно проверить.

б) значение_если_истина — это значение, которое должно возвращаться, если «лог_выражение» имеет значение ИСТИНА.

в) значение_если_ложь — это значение, которое должно возвращаться, если «лог_выражение» имеет значение ЛОЖЬ.

Возможны и более сложные варианты этой функции.

Вложенные функции ЕСЛИ имеют вид ЕСЛИ(лог_выражение; значение_если_истина; ЕСЛИ(лог_выражение; значение_если_истина; ЕСЛИ(и так далее).

Ещё один вариант — функция ЕСЛИОШИБКА.

Она возвращает указанное значение, если вычисление по формуле вызывает ошибку; в противном случае она возвращает результат формулы. Функция ЕСЛИОШИБКА позволяет перехватывать и обрабатывать ошибки в формулах.

Формула: =ЕСЛИОШИБКА(значение;значение_если_ошибка).

9. Формулы СУММЕСЛИ, СЧЁТЕСЛИ и СРЗНАЧЕСЛИ.

Они работают только при условии, что указанные критерии соблюдены.

Эти формулы выдают сумму значений в ячейках, число ячеек и среднее значение ячеек соответственно.

Формулы: СУММЕСЛИ(диапазон; условие; диапазон_суммирования),СЧЁТЕСЛИ(диапазон;критерий),

СРЗНАЧЕСЛИ(диапазон, условия, диапазон_усреднения).

10. Формула СЦЕПИТЬ.

Очень простая и полезная функция, обозначаемая также символом &.

Одна объединяет две и больше текстовые строки в одну.

Формула: =СЦЕПИТЬ(текст1;текст2;…).

На примере показано действие формулы =СЦЕПИТЬ(A2, “ ”, B2, “ ”, C2).