Многим из нас приходится работать в 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).