Excel. Вебинар 3

Июль 29, 2022

Главная
Информатика
Excel. Вебинар 3

Содержание

2. Программа мини-курса Вебинар 1 (20.07.2022). Интерфейс Excel. Форматирование ячеек. Таблицы Интерфейс Excel и быстрый ввод данных.
3. Вебинар 3. Функции и формулы Еще немного про сводные: кэш и срезы у нескольких сводных Функции
4. Формулы и функции
5. Формула – вычисление в ячейке. В формулах могут обрабатываться другие ячейки листа и книги, ячейки других
6. Функция принимает аргументы, которые указываются в скобках (и разделяются точкой с запятой в случае российских региональных
7. Относительные и абсолютные ссылки A2 = относительная ссылка. Это ссылка на ячейку слева от формулы (в
8. Ссылки на Таблицы Ссылка на все данные (без заголовков и строки итогов): Таблица Ссылка на все
9. Даты Книга Excel с примерами: 1 Даты
10. Дата и время в Excel За любой датой в Excel скрывается целое число. Датой его делает
11. Функции для отображения текущей даты
12. Функции для получения параметров даты
13. Функции для вычислений с рабочими днями У обеих функций есть версия «международная» (.МЕЖД или .INTL на
14. Расчеты с условиями Книги Excel с примерами: 2 Логика и ЕСЛИ 3 Расчеты с условиями
15. Функция ЕСЛИ / IF =ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь) Лог_выражение (logical_test) — может быть истинным или ложным (равенство,
16. ЕСЛИМН / IFS =ЕСЛИМН(логическая_проверка1 ; если_значение_истина1 ; [логическая_проверка2] ;[если_значение_истина2]; … ) логическая_проверка1 (logical_test1) — первое условие,
17. Функции для расчетов с условиями
18. Как записываются условия
19. Расширенный фильтр и функции баз данных Книга Excel с примерами: 4 Расширенный фильтр и функции БД
20. Расширенный фильтр (Advanced Filter) Data → Advanced Расширенный фильтр — это инструмент для фильтрации данных по
21. Условия для расширенного фильтра
22. Символы подстановки (wildcard) Эти символы работают в условиях расширенного фильтра, функций баз данных (о них ниже),
23. Функции баз данных (Database functions) Синтаксис функций баз данных на примере ДСРЗНАЧ / DAVERAGE (вычисление среднего
24. Функции баз данных (Database functions) * Это относится ко всем функциям баз данных и является их
25. Функции баз данных (Database functions)
26. Функции поиска Книга Excel с примерами: 5 Поиск
28. Скачать презентацию

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

Формулы Excel

Слайд 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

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

Слайд 12

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

Слайд 13

Функции для вычислений с рабочими днями
У обеих функций есть версия «международная»

(.МЕЖД или .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

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

Слайд 18

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

Слайд 19

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

фильтр и функции БД

Слайд 20

Расширенный фильтр (Advanced Filter)
Data → Advanced
Расширенный фильтр — это инструмент для

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

Слайд 21

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

Слайд 22

Символы подстановки (wildcard)
Эти символы работают в условиях расширенного фильтра, функций баз

данных (о них ниже), функций СУММЕСЛИМН / SUMIFS (и СЧЁТЕСЛИМН / COUNTIFS, СРЗНАЧЕСЛИМН / AVERAGEIFS).
А также — напоминание — в окне «Найти и заменить».

Слайд 23

Функции баз данных (Database functions)
Синтаксис функций баз данных на примере ДСРЗНАЧ

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

Слайд 24

Функции баз данных (Database functions)
* Это относится ко всем функциям баз

данных и является их ключевой особенностью — условия задаются как в расширенном фильтре

Слайд 25

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

Слайд 26

Excel. Вебинар 3

Содержание

Программа мини-курса Вебинар 1 (20.07.2022). Интерфейс Excel. Форматирование ячеек. ТаблицыИнтерфейс Excel

Вебинар 3. Функции и формулыЕще немного про сводные: кэш и срезы

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

Формула – вычисление в ячейке. В формулах могут обрабатываться другие ячейки

Функция принимает аргументы, которые указываются в скобках (и разделяются точкой с

Относительные и абсолютные ссылкиA2 = относительная ссылка.Это ссылка на ячейку слева

Ссылки на ТаблицыСсылка на все данные (без заголовков и строки итогов):ТаблицаСсылка

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

Дата и время в ExcelЗа любой датой в Excel скрывается целое

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

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

Функции для вычислений с рабочими днямиУ обеих функций есть версия «международная»

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

Функция ЕСЛИ / IF=ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь)Лог_выражение (logical_test) — может быть истинным

ЕСЛИМН / IFS=ЕСЛИМН(логическая_проверка1 ; если_значение_истина1 ; [логическая_проверка2] ;[если_значение_истина2]; … )логическая_проверка1 (logical_test1)