Эффективная работа в Excel 2016. Скрытые и неочевидные функции и приемы

1.

Эффективная работа
в Excel 2016
Скрытые и неочевидные функции и приемы
Ренат Шагабутдинов
29.07.2022

2.

Программа мини-курса
Вебинар 1 (20.07.2022). Интерфейс Excel. Форматирование ячеек. Таблицы
Интерфейс Excel и быстрый ввод данных. Форматирование ячеек. Пользовательские числовые
форматы. Стили. Таблицы Excel
Вебинар 2 (27.07.2022). Сводные таблицы
Сводные таблицы. Источники данных для сводных. Настройка внешнего вида отчета сводной
таблицы. Вычисляемые поля. Создание копий отчета сводной таблицы
Вебинар 3 (29.07.2022). Функции и формулы
Функции для работы с датами. Расчеты с условиями. Расширенный фильтр. Функции поиска.
Разбор домашних работ

3.

Вебинар 3. Функции и формулы
Еще немного про сводные: кэш и срезы у нескольких сводных
Функции для работы с датами
Функции для расчетов с условиями
Расширенный фильтр и функции баз данных
Функции для поиска: ВПР / VLOOKUP, ПРОСМОТРX / XLOOKUP, ИНДЕКС / INDEX
Бонус: еще 5 с половиной приемов для эффективной работы в Excel
Разбор домашних работ

4.

Формулы и функции

5. Формулы Excel

Формула – вычисление в ячейке. В формулах могут обрабатываться другие
ячейки листа и книги, ячейки других книг Excel. В формулах используются
математические знаки и функции.
Формулы вводятся со знака «равно» (=)
Элементы формул:
Константы (числа, даты, текст, указанные прямо в формуле)
Математические знаки (+ - * / ^), символ объединения (конкатенации) амперсанд
(&), знаки сравнения (= > <)
Ссылки на ячейки (A1), диапазоны (A1:E5), столбцы/строки (E:E), столбцы Таблиц
(Таблица[Выручка]), именованные диапазоны (Выручка)
Функции рабочего листа (например, СУММ / SUM)

6. Функции рабочего листа

Функция принимает аргументы, которые указываются в скобках (и разделяются
точкой с запятой в случае российских региональных настроек), и возвращает
результат вычисления.
Есть функции без аргументов (например, СЕГОДНЯ / TODAY), есть функции с 1
или несколькими аргументами, с фиксированным и произвольным количеством
аргументов, с [необязательными] аргументами.
Как вставить функцию:
- Начать вводить название функции и выбрать ее в выпадающем списке
- Нажать Shift + F3 для вызова окна «Вставка функций»
- Выбрать Формулы – Библиотека функций – Вставить функцию на ленте
- Нажать на fx слева от строки формул

7. Относительные и абсолютные ссылки

A2 = относительная ссылка.
Это ссылка на ячейку слева от формулы (в
данном примере). Какой это будет адрес –
зависит от расположения формулы. Для
формулы в B5 ячейка слева – это A5.
$D$1 = абсолютная ссылка.
Это ссылка на ячейку D1. Она не зависит от того,
где находится формула.

8. Ссылки на Таблицы

Ссылка на все данные (без заголовков и строки итогов):
Таблица
Ссылка на все данные в таблице, включая заголовки и строку итогов:
Таблица[#Все] Table[#All]
Ссылка только на строку итогов:
Таблица[#Итоги] Table[#Headers]
Ссылки на отдельные столбцы в таблице
Ссылка на все данные в столбце (без заголовка):
Таблица[название_столбца]
Ссылка на ячейку с итогами в конкретном столбце:
Таблица[[#Итоги];[название_столбца]] Table[[#Totals];[название_столбца]]
Ссылка на ячейку с заголовком в столбце:
Таблица[[#Заголовки];[название_столбца]] Table[[#Headers];[название_столбца]]
Cсылка все данные в столбце (с итогами и заголовками):
Таблица[[#Все];[название_столбца]] Таблица[[#All];[название_столбца]]
Ссылка на диапазон (несколько столбцов) в таблице. Через двоеточие указываются первый (левый) и последний (правый)
столбец в диапазоне:
Таблица[[Первый_столбец]:[Последний столбец]]

9.

Даты
Книга Excel с примерами: 1 Даты

10. Дата и время в Excel

За любой датой в Excel скрывается целое число. Датой его делает формат.
Аналогично со временем: одна единица — это день, а часть единицы (число от 0
до 1) — время, то есть часть дня.
Если видите числа там, где должны быть даты — дело может быть только
в числовом форматировании (нужно поменять формат на «Дату»).
Даты в формулах можно использовать:
— как константы, указывая в кавычках (“01.01.2021”, “01/01/2021”, “2021-01-01”);
— ссылаясь на ячейки, где даты хранятся.
Для превращения даты в текстовом формате в «настоящую» дату используйте
функцию ДАТАЗНАЧ / DATEVALUE.

11. Функции для отображения текущей даты

Функция
Что делает
СЕГОДНЯ
TODAY
Возвращает текущую дату
ТДАТА
NOW
Возвращает текущие дату
и время

12. Функции для получения параметров даты

Функция
Что делает
ДЕНЬ
DAY
Возвращает номер дня (в числовом формате)
МЕСЯЦ
MONTH
Возвращает номер месяца (в числовом формате)
ГОД
YEAR
Возвращает номер года (в числовом формате)
КОНМЕСЯЦА
EOMONTH
Возвращает дату последнего дня любого месяца, отстоящего от заданной даты
(в первом аргументе) на любое количество месяцев (второй аргумент)
=ДЕНЬ("01.07.2021") → 1
=МЕСЯЦ("01.07.2021") → 7
=ГОД("01.07.2021") → 2021
=КОНМЕСЯЦА("01.07.2021";1) → 31.08.2021
НОМНЕДЕЛИ
WEEKNUM
Возвращает порядковый номер недели (в числовом формате).
Второй аргумент задает тип нумерации (1 = первая неделя с 1 января, 2 = первая
неделя с первого четверга года)
=НОМНЕДЕЛИ("01.07.2021";1) → 27
ДЕНЬНЕД
WEEKDAY
Возвращает порядковый номер дня недели (в числовом формате)
=ДЕНЬНЕД("01.07.2021";2) → 4

13. Функции для вычислений с рабочими днями

Функция
Что делает
ЧИСТРАБДНИ
NETWORKDAYS
Возвращает количество рабочих дней между
датами
ЧИСТРАБДНИ.МЕЖД
NETWORKDAYS.INTL
=ЧИСТРАБДНИ("10.01.2021";"01.07.2021")
→ 124
РАБДЕНЬ
WORKDAY
Возвращает дату, которая наступит через N
(второй аргумент) рабочих дней от заданной
даты (первый аргумент)
РАБДЕНЬ.МЕЖД
WORKDAY.INTL
=РАБДЕНЬ("01.07.2021";5) → 08.07.2021
У обеих функций есть версия «международная» (.МЕЖД или .INTL на конце) —
в которой задается специальный тип рабочей недели. В том числе есть
возможность задать ваш собственный в формате «0011001», где 1 = выходной,
а 0 = рабочий день.

14.

Расчеты с условиями
Книги Excel с примерами:
2 Логика и ЕСЛИ
3 Расчеты с условиями

15. Функция ЕСЛИ / IF

=ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь)
Лог_выражение (logical_test) — может быть истинным или ложным (равенство,
неравенство).
Значение_если_истина (value_if_true) — что будет возвращать функция ЕСЛИ,
когда первый аргумент (логическое выражение) истинный.
Значение_если_ложь (value_if_false) — что будет возвращать функция ЕСЛИ,
когда первый аргумент (логическое выражение) ложный.

16. ЕСЛИМН / IFS

=ЕСЛИМН(логическая_проверка1 ; если_значение_истина1 ;
[логическая_проверка2] ;[если_значение_истина2]; … )
логическая_проверка1 (logical_test1) — первое условие, может быть истинным или
ложным (равенство, неравенство). Как логическое выражение в ЕСЛИ / IF.
если_значение_истина1 (value_if_true1) — что будет возвращать функция ЕСЛИМН
при выполнении первого условия.
[логическая_проверка2] — второе условие.
[если_значение_истина2] — что будет возвращать функция ЕСЛИМН при выполнении
второго условия.
…

17. Функции для расчетов с условиями

Сумма
Количество
Среднее
Функция без
условий
СУММ
/ SUM — сумма чисел
СЧЁТ
/ COUNT —
количество чисел
Одно условие
(ЕСЛИ / IF)
СУММЕСЛИ
/ SUMIF — сумма по 1
условию
СЧЁТЕСЛИ / COUNTIF — количество значений,
удовлетворяющих условию
СРЗНАЧЕСЛИ
/ AVERAGEIF —
среднее по 1 условию
Одно и более
условие (ЕСЛИМН
/ IFS)
СУММЕСЛИМН
/ SUMIFS — сумма по
условиям
СЧЁТЕСЛИМН / COUNTIFS — количество строк
или столбцов, удовлетворяющих заданным
условиям
СРЗНАЧЕСЛИМН
/ AVERAGEIFS —
среднее по условиям
СЧЁТЗ
/ COUNTA —
количество любых
значений
СРЗНАЧ
/ AVERAGE — среднее
арифметическое

18. Как записываются условия

Тип данных в столбце,
на который
накладывается
условие
Условие берем из ячейки
или указываем прямо в
формуле
Ячейка
Пример условия
Условие
“<”&B2
Дата до даты, указанной в ячейке
B2
“>=15.06.2021”
Дата от 15 июня 2021 года
включительно и позже
“<=”&B8
Число меньше либо равно числа
из ячейки B8
“>=100”
От 100 включительно
B8
Текст соответствует тексту из
ячейки B8
“Москва”
Текст = “Москва”
Даты
В формуле
Ячейка
Числа
В формуле
Ячейка
Текстовые значения
В формуле

19.

Расширенный фильтр и
функции баз данных
Книга Excel с примерами: 4 Расширенный фильтр и
функции БД

20. Расширенный фильтр (Advanced Filter)

Data → Advanced
Расширенный фильтр — это инструмент для фильтрации данных по одному или нескольким наборам условий, в том
числе не пересекающихся друг с другом. Он гораздо мощнее обычного автофильтра.
Отличия расширенного фильтра от обычного фильтра на рабочем листе в следующем:
● Можно фильтровать результат на месте (как в обычном фильтре), а можно сразу выводить результаты в
другое место, не фильтруя исходный диапазон.
● Условия задаются не в самом фильтре, а в отдельных ячейках.
● Можно фильтровать данные по нескольким независимым наборам условий (когда одному значению в одном
столбце соответствует другое значение в другом).

21. Условия для расширенного фильтра

22. Символы подстановки (wildcard)

Символ
Что заменяет
Пример условия
Условие
*
(звездочка)
Любое кол-во любых символов
*Москва*
Ищем ячейки, в которых в любом
месте встречается слово «Москва»
?
(знак вопроса)
Один любой символ
~
(тильда)
Позволяет указывать в
условиях именно звездочку и
знаки вопроса (для этого тильду
нужно ставить перед ними)
М?????
Ищем ячейки с буквой М и
любыми пятью символами после
нее
*Москва~*
Ищем ячейки, в конце которых
встречается слово «Москва*»
со звездочкой
Эти символы работают в условиях расширенного фильтра, функций баз данных
(о них ниже), функций СУММЕСЛИМН / SUMIFS (и СЧЁТЕСЛИМН / COUNTIFS,
СРЗНАЧЕСЛИМН / AVERAGEIFS).
А также — напоминание — в окне «Найти и заменить».

23. Функции баз данных (Database functions)

Синтаксис функций баз данных на примере ДСРЗНАЧ / DAVERAGE
(вычисление среднего значения):
=ДСРЗНАЧ(данные; столбец; критерии)
Данные (database) = исходная таблица с заголовками.
Столбец (field) = ссылка на заголовок или текст с заголовком того столбца,
по которому нужно вести расчет (суммировать/усреднять/извлекать
значение, вычислять максимальное или минимальное значение и так
далее).
Критерии (criteria) = ссылка на диапазон с условиями (эти условия
задаются как для расширенного фильтра).

24. Функции баз данных (Database functions)

Функция
Что делает
БДСУММ
DSUM
Суммирует числа (по критериям, заданным в формате
расширенного фильтра*)
ДСРЗНАЧ
DAVERAGE
Возвращает среднее арифметическое
БСЧЁТ
DCOUNT
Подсчитывает количество числовых ячеек в одном из столбцов
БСЧЁТА
DCOUNTA
Подсчитывает количество непустых ячеек в одном из столбцов
БИЗВЛЕЧЬ
DGET
Извлекает из таблицы одно значение по условиям. Если условиям
соответствует несколько значений, возвращает ошибку #ЧИСЛО!
(#NUM!)
* Это относится ко всем функциям баз данных и является их ключевой особенностью
— условия задаются как в расширенном фильтре

25. Функции баз данных (Database functions)

Функция
Что делает
ДМИН / ДМАКС
DMIN / DMAX
Возвращают минимальное / максимальное значение в одном
из столбцов
БДПРОИЗВЕД
DPRODUCT
Перемножает числа (соответствующие условиям) из одного из
столбцов
БДДИСП
DVAR
Оценивает дисперсию по выборке
БДДИСПП
DVARP
Оценивает дисперсию по генеральной совокупности
ДСТАНДОТКЛ
DSTDEV
Оценивает стандартное отклонение по выборке
ДСТАНДОТКЛП
DSTDEVP
Оценивает стандартное отклонение по генеральной
совокупности

26.

Функции поиска
Книга Excel с примерами: 5 Поиск

27. Функции для поиска и извлечения данных

ВПР
VLOOKUP
Где есть
Особенности
Минус
Синтаксис (на человеческом
языке)
В любой версии Excel
и в Google Таблицах
Символы подстановки работают по
умолчанию
Столбец для поиска
должен быть первым
(нельзя извлекать
данные левее
«ключа»)
=ВПР(что ищем; таблица с
данными, где «что ищем»
должно быть в первом
столбце; номер столбца, из
которого нужны данные; 0)
Более сложная для
новичков конструкция
(две функции в
формуле)
=ИНДЕКС(диапазон, из
которого нужны данные;
ПОИСКПОЗ (что ищем; где
ищем ; 0))
Нет в Excel 2019 и
ранних версиях, нет в
Google Таблицах
=ПРОСМОТРX(что ищем; где
ищем; диапазон, из которого
нужны данные)
Направление поиска — сверху вниз
ИНДЕКС +
ПОИСКПОЗ
INDEX + MATCH
В любой версии Excel
и в Google Таблицах
Символы подстановки работают по
умолчанию
Направление поиска — сверху вниз
ПРОСМОТРX
XLOOKUP
В Excel в рамках
пакета Office 365 (с
обновлениями) и
Office 2021
Символы подстановки работают при
«включении» соответствующего
режима через аргумент
«режим_сопоставления»
Направление поиска задается через
соответствующий аргумент
Точный поиск по умолчанию
* Последний аргумент в ВПР и ПОИСКПОЗ, равный нулю, указывается, если мы ищем текстовое значение (это точный поиск). ПРОСМОТРX
по умолчанию ищет точное совпадение