Microsoft Excel

1.

Казанский национальный исследовательский технический университет
им. А.Н.Туполева - КАИ
Microsoft Excel
Учебно-методический курс (углубленный)
доцент каф. Прочность конструкций,
к.т.н. Алексеев Кирилл Анатольевич

2.

Аннотация к курсу
ЦЕЛЬ ИЗУЧЕНИЯ ДИСЦИПЛИНЫ :
практическое освоение углубленного функционала MsExcel.
ЗАДАЧИ ИЗУЧЕНИЯ ДИСЦИПЛИНЫ:
- знакомство с различными типами функций Excel;
- приобретение теоретических и практических навыков по вводу, форматированию,
управлению данными и настройками Excel;
- изучение основных возможностей Excel по построению и управлению диаграммами;
- знакомство и приобретение первичных навыков работы со сводными таблицами.
КВАЛИФИКАЦИЯ:
- базовая компьютерная подготовка в Windows 7,8,10/Vista/XP;
- базовые навыки работы в MsExcel.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
2

3.

Лекция 1. Функции Excel.
Математические&Статистические.
Суммирование по одному критерию СУММЕСЛИ
ЗАДАЧА: просуммировать данные по продажам разных групп товаров
Формат:
=СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования])
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
3

4.

Лекция 1. Функции Excel.
Математические&Статистические.
Суммирование по нескольким критериям СУММЕСЛИМН
ЗАДАЧА: просуммировать данные по продажам товаров с разными признаками
Формат:
=СУММЕСЛИМН(диапазон_суммирования;диапазон_условия1;условие1;…)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
4

5.

Лекция 1. Функции Excel.
Математические&Статистические.
Вычисление количества записей по одному критерию СЧЁТЕСЛИ
Задача: найти количество позиций в одной группе товаров
Формат:
=СЧЁТЕСЛИ(диапазон;критерий)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
5

6.

Лекция 1. Функции Excel.
Математические&Статистические.
Подсчет количества записей по нескольким критериям СЧЁТЕСЛИМН
Задача: найти количество позиций в нескольких группах товаров с разными
признакам
Формат:
=СЧЁТЕСЛИМН(диапазон_условия1;условие1;…)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
6

7.

Лекция 1. Функции Excel.
Математические&Статистические.
Поиск дублей с функцией СЧЁТЕСЛИ
Задача: найти повторяющиеся позиции в массиве данных
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
7

8.

Лекция 1. Функции Excel.
Математические&Статистические.
Среднее значение по одному критерию СРЗНАЧЕСЛИ
Задача: найти средние арифметические данных по продажам разных групп
товаров
Формат:
=СРЗНАЧЕСЛИ(диапазон;условие;[диапазон_усреднения])
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
8

9.

Лекция 1. Функции Excel.
Математические&Статистические.
Среднее значение по нескольким критериям СРЗНАЧЕСЛИМН
Задача: найти средние арифметические данных по продажам товаров с
разными признаками
Формат:
=СРЗНАЧЕСЛИМН(диапазон_усреднения;диапазон_условия1;условие1;…)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
9

10.

Лекция 1. Функции Excel.
Математические&Статистические.
Поиск мин.&макс. значений по нескольким условиям
Задача: найти минимальное (максимальное) значение продаж в группе
товаров
Формат:
=МИНЕСЛИ(минимальный_диапазон;диапазон_условий1;условия1;…)
=МАКСЕСЛИ(максимальный_диапазон;диапазон_условий1;условия1;…)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
10

11.

Лекция 1. Функции Excel.
Математические&Статистические.
Поиск крайних значений НАИМЕНЬШИЙ, НАИБОЛЬШИЙ
Задача: найти группу из N наименьших или N наибольших значений в массиве
данных
Формат:
=НАИМЕНЬШИЙ(массив;k)
=НАИБОЛЬШИЙ(массив;k)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
11

12.

Лекция 1. Функции Excel.
Математические&Статистические.
Суммирование произведений СУММПРОИЗВ
ЗАДАЧА: вычислить общий налог для группы товаров
Формат:
=СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
12

13.

Лекция 1. Функции Excel.
Математические&Статистические.
Функции округления
Проблема: по умолчанию Excel округляет числа до 15 знаков после запятой,
поэтому итоги, рассчитанные разными способами, иногда немного не сходятся
ОКРУГЛ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ, ОКРВВЕРХ.МАТ, ОКРВНИЗ.МАТ
КОМАНДА(число; число_разрядов)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
13

14.

Лекция 1. Функции Excel.
Математические&Статистические.
Генерация случайного числа СЛУЧМЕЖДУ, СЛЧИС
Задача: сгенерировать массив случайных чисел в указанном диапазоне
Формат:
=СЛУЧМЕЖДУ(нижняя_граница;верхняя_граница)
=СЛЧИС()
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
14

15.

Лекция 1. Функции Excel.
Математические&Статистические.
Выявление частоты появления события ЧАСТОТА (1#2)
Массив - это набор элементов, которые могут обрабатываться как единая группа.
Массивы создаются нажатием Ctrl+Shift+Enter, вместо Enter.
Формат:
=СУММА(первый_столбец*второй_столбец)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
15

16.

Лекция 1. Функции Excel.
Математические&Статистические.
Выявление частоты появления события ЧАСТОТА (2#2)
Задача: выявить, в каких диапазонах сумм чаще всего делают покупки
Формат:
Для изменения формулы в массиве надо его снова выделить,
=ЧАСТОТА(массив_данных;массив_интервалов)
изменить и нажать Ctrl+Shift+Enter
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
16

17.

Лекция 1. Функции Excel.
Текстовые.
Подсчет количества символов в ячейке ДЛСТР
Задача: подсчитать количество символов в строке (включая пробелы)
Формат:
=ДЛСТР(текст)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
17

18.

Лекция 1. Функции Excel.
Текстовые.
Вернуть указанное количество символов в ячейке слева или справа
Задача: извлечь код группы товара ТНВЭД
Формат:
=ЛЕВСИМВ(текст;[количество_знаков])
=ПРАВСИМВ(текст;[количество_знаков])
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
18

19.

Лекция 1. Функции Excel.
Текстовые.
Извлечение текста из произвольного места ячейки ПСТР
Задача: извлечь указанное количество символов из середины текста
Формат:
=ПСТР(текст;начальная_позиция;количество_знаков)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
19

20.

Лекция 1. Функции Excel.
Текстовые.
Удаление лишних пробелов СЖПРОБЕЛЫ
Задача: сделать текст единообразным, оставив по одному пробелу между
словами
Формат:
=СЖПРОБЕЛЫ(текст)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
20

21.

Лекция 1. Функции Excel.
Текстовые.
Возвращение порядкового номера символа в строке НАЙТИ, ПОИСК
Задача: определить положение символа в тексте
Формат:
=НАЙТИ(искомый_текст;просматриваемый_текст;) – с учетом регистра
=ПОИСК(искомый_текст;просматриваемый_текст;) – без учета регистра
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
21

22.

Лекция 1. Функции Excel.
Текстовые.
Замена текста в ячейке ПОДСТАВИТЬ, ЗАМЕНИТЬ (1#2)
Задача: заменить один фрагмент текста на другой
Формат:
=ПОДСТАВИТЬ(текст;стар_текст;новый_текст;)
=ЗАМЕНИТЬ(старый_текст;нач_поз;число_знаков;новый_текст)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
22

23.

Лекция 1. Функции Excel.
Текстовые.
Замена текста в ячейке ПОДСТАВИТЬ, ЗАМЕНИТЬ (2#2)
Задача: извлечь последнее слово из текстовой строки произвольной длины
Исходные данные: наличие разделителей между словами и длина строки
1.
Определяем длину строки (26)
2.
Создаем 26 пробелов
3.
Вставляем 26 пробелов между каждой парой слов
4.
Оставляем справа 26 символов
5.
Сжимаем пробелы
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
23

24.

Лекция 1. Функции Excel.
Текстовые.
Соединение содержимого ячеек СЦЕПИТЬ, &, СЦЕП
Задача: соединить содержимое нескольких ячеек в одной
Формат:
=СЦЕПИТЬ(текст1;[текст2];…)
=СЦЕП(текст1;…) – сцепляет ячейки в диапазоне, но без разделителей
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
24

25.

Лекция 1. Функции Excel.
Текстовые.
Соединение содержимого ячеек ОЪЕДИНИТЬ
Задача: соединить содержимое нескольких ячеек в одной
Формат:
=ОБЪЕДИНИТЬ(разделитель;пропускать_пустые;текст1;…)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
25

26.

Лекция 1. Функции Excel.
Текстовые.
Применение кодов форматирования к тексту ТЕКСТ
Проблема: текущую дату тяжело сцепить с текстом
Формат:
=ТЕКСТ(значение;формат)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
26

27.

Лекция 1. Функции Excel.
Логические.
Выполнение действия по условию ЕСЛИ
Задача: распределить премию сотрудникам в зависимости от одного параметра
эффективности
Формат:
=ЕСЛИ(лог_выражение;[значение_если_истина];[значение_если_ложь])
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
27

28.

Лекция 1. Функции Excel.
Логические.
Выполнение действия по нескольким условиям (вложенная ЕСЛИ)
Задача: распределить
эффективности
премию
сотрудникам
по
нескольким
параметрам
Формат:
=ЕСЛИ(лог_выражение;[значение_если_истина];[значение_если_ложь])
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
28

29.

Лекция 1. Функции Excel.
Логические.
Выполнение действия по нескольким условиям ЕСЛИМН
Задача: распределить
эффективности
премию
сотрудникам
по
нескольким
параметрам
Формат:
=ЕСЛИМН(логическая_проверка1;если_значение_истина1;…)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
29

30.

Лекция 1. Функции Excel.
Логические.
Проверка по нескольким условиям И, ИЛИ
Задача: проверить выполнение одного или нескольких условий
Формат:
=И(логическое_значение1;[логическое_значение2];…)
=ИЛИ(логическое_значение1;[логическое_значение2];…)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
30

31.

Лекция 1. Функции Excel.
Логические.
Выявление ошибок вычисления ЕСЛИОШИБКА
Задача: изменить сообщение для ячейки с ошибочной операцией
Формат:
=ЕСЛИОШИБКА(значение;значение_если_ошибка)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
31

32.

Лекция 1. Функции Excel.
Дата и Время.
Выделение элементов дат
Задача: выделить год, месяц, номер, число и день недели из текущей даты
Формат:
=СЕГОДНЯ() – возвращает сегодняшнюю дату
=СЕГОДНЯ()-1 – возвращает вчерашнюю дату
=ГОД(дата_в_числовом_формате) – возвращает год из указанной даты
=МЕСЯЦ(дата_в_числовом_формате) – возвращает месяц из указанной даты
=НОМНЕДЕЛИ(дата_в_числовом_формате) – возвращает номер недели
=ДЕНЬ(дата_в_числовом_формате) – возвращает число из указанной даты
=ДЕНЬНЕД(дата_в_числовом_формате) – возвращает день недели (число)
Горячая клавиша Ctrl+; вставляет статическую текущую дату
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
32

33.

Лекция 1. Функции Excel.
Дата и Время.
Дата, отстоящая на заданное количество дней РАБДЕНЬ.МЕЖД
Задача: определить дату окончания проекта по известному количеству
рабочих дней
Формат:
=РАБДЕНЬ.МЕЖД(нач_дата;количество_дней;[выходной];[праздники])
код выходных: 0 – раб.день, 1 – выходной. Первая цифра – понедельник
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
33

34.

Лекция 1. Функции Excel.
Дата и Время.
Количество рабочих дней между датами ЧИСТРАБДНИ.МЕЖД
Задача: определить длительность проекта, как количество дней между
двумя датами
Формат:
=ЧИСТРАБДНИ.МЕЖД(нач_дата;кон_дата;[выходной];[праздники])
код выходных: 0 – раб.день, 1 – выходной. Первая цифра – понедельник
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
34

35.

Лекция 1. Функции Excel.
Ссылки&Массивы.
Сослаться на смещенную ячейку или смещенный диапазон СМЕЩ
Задача: получить значение из ячейки или диапазона, смещенных относительно
указанной ячейки
Формат:
=СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина])
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
35

36.

Лекция 1. Функции Excel.
Ссылки&Массивы.
Формула, автоматически учитывающая изменение диапазона
Проблема: значение в добавленной строке не учитывается автоматически в
формуле
Формат:
=СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
36

37.

Лекция 1. Функции Excel.
Ссылки&Массивы.
Получение относительной позиции ячейки в диапазоне ПОИСКПОЗ
Задача: получить относительную позицию искомой ячейки в диапазоне поиска
Формат:
=ПОИСКПОЗ(иском_значение;просматриваем_массив;[тип_сопоставления])
Для типов сопоставления
«1» и «-1»
просматриваемый
массив
тип_сопоставления:
-1 ближ.большее;
0 точное
совпадение, 1 ближ.меньшее
должен быть отсортирован по возрастанию или по убыванию, соотв.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
37

38.

Лекция 1. Функции Excel.
Ссылки&Массивы.
Получение относительной позиции ячейки в диапазоне ПОИСКПОЗ
Задача: выполнить динамическое суммирование по выбранному критерию
Формат:
=ПОИСКПОЗ(иском_значение;просматриваем_массив;[тип_сопоставления])
=СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина])
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
38

39.

Лекция 1. Функции Excel.
Ссылки&Массивы.
Создание выпадающего списка в ячейке
Задача: обеспечить точное совпадение имени критерия с соответствующим
именем в диапазоне поиска
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
39

40.

Лекция 1. Функции Excel.
Ссылки&Массивы.
Подстановка данных из одной таблицы в другую ВПР
Задача: найти ячейку в первом столбце первой таблицы по критерию и
подставить значения из этой строки во вторую таблицу
Формат:
=ВПР(искомое_значен;таблица;номер_столбца;[интервальный_просмотр])
интервальный_просмотр: 0 точное совпадение, 1 приблизит. совпадение
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
40

41.

Лекция 1. Функции Excel.
Ссылки&Массивы.
Особенности использования функции ВПР (1#4)
Проблема: разный формат данных у критериев дает ошибку
Решение: преобразовать «искомые_значения» в текстовый формат
=ТЕКСТ(значение;формат) « # » - текстовый формат
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
41

42.

Лекция 1. Функции Excel.
Ссылки&Массивы.
Особенности использования функции ВПР (2#4)
Проблема: разный формат данных у критериев дает ошибку
Решение: преобразовать «искомые_значения» в числовой формат
=ЗНАЧЕН(текст)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
42

43.

Лекция 1. Функции Excel.
Ссылки&Массивы.
Особенности использования функции ВПР (3#4)
Проблема: разный формат данных у критериев дает ошибку
Решение: преобразовать «искомые_значения» в числовой или текстовый формат
=ЗНАЧЕН(текст)
=ЕСЛИОШИБКА(значение;значение_если_ошибка)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
43

44.

Лекция 1. Функции Excel.
Ссылки&Массивы.
Особенности использования функции ВПР (4#4)
Проблема: найти ячейку в первом столбце первой таблицы по неточному
критерию и подставить значения из этой строки во вторую таблицу
Формат:
=ВПР(искомое_значен;таблица;номер_столбца;[интервальный_просмотр])
интервальный_просмотр: 0 точное совпадение, 1 приблизит. совпадение
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
44

45.

Лекция 1. Функции Excel.
Ссылки&Массивы.
Возвращение значения из таблицы ИНДЕКС (1#3)
Задача: получить значение на пересечении третьей строки и второго
столбца
Формат:
=ИНДЕКС(массив;номер_строки;[номер_столбца])
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
45

46.

Лекция 1. Функции Excel.
Ссылки&Массивы.
Возвращение значения из таблицы ИНДЕКС (2#3)
Задача: вернуть последнее значение в столбце массива
Формат:
=ИНДЕКС(массив;номер_строки;[номер_столбца])
=СЧЁТЗ(значение1;[значение2];…)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
46

47.

Лекция 1. Функции Excel.
Ссылки&Массивы.
Возвращение значения из таблицы ИНДЕКС (3#3)
Задача: найти и вернуть искомые значения указанного критерия из массива
Формат:
=ИНДЕКС(массив; номер_строки; [номер_столбца])
=ПОИСКПОЗ(иском_значение;просматриваем_массив;[тип_сопоставления])
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
47

48.

Лекция 1. Функции Excel.
Агрегированные.
ПРОМЕЖУТОЧНЫЕ ИТОГИ (1#2)
Задача: вычислить сумму отфильтрованных значений
Формат:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции;ссылка1;[ссылка2];…)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
48

49.

Лекция 1. Функции Excel.
Агрегированные.
ПРОМЕЖУТОЧНЫЕ ИТОГИ (2#2)
Задача: вычислить общий итог без учета промежуточных сумм
Формат:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции;ссылка1;[ссылка2];…)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
49

50.

Лекция 1. Функции Excel.
Агрегированные.
Настройка параметров вычисления АГРЕГАТ
Задача: не учитывать ошибку в параметрах расчета
Формат:
=АГРЕГАТ(номер_функции;параметры;массив;[k])
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
50

51.

Лекция 1. Функции Excel.
Инструменты.
Именованные ячейки и диапазоны (1#4)
Понятные имена можно присваивать не только ячейкам и диапазонам, но также
константам и формулам
Задача: использовать в формулах имена вместо адресов ячеек и диапазонов
Имена не должны содержать названий столбцов, пробелов или
состоять из одних цифр
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
51

52.

Лекция 1. Функции Excel.
Инструменты.
Именованные ячейки и диапазоны (2#4)
Задача: научиться управлять именами и автоматизировать процесс их создания
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
52

53.

Лекция 1. Функции Excel.
Инструменты.
Именованные ячейки и диапазоны (3#4)
Задача: научиться присваивать имена константам и формулам
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
53

54.

Лекция 1. Функции Excel.
Инструменты.
Именованные ячейки и диапазоны (4#4)
Все вышеперечисленные функции можно выполнить в диалоге Диспетчер имен
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
54

55.

Лекция 1. Функции Excel.
Инструменты.
Инструменты поиска ошибок и отладки формул (1#2)
Инструменты анализа и корректировки формул находятся в меню Формулы
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
55

56.

Лекция 1. Функции Excel.
Инструменты.
Инструменты поиска ошибок и отладки формул (2#2)
Команда Вычислить формулу выполняет пошаговое вычисление формулы
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
56

57.

Лекция 1. Функции Excel.
Инструменты.
Отслеживание изменений в других листах и книгах
Команда Окно контрольного значения отображает изменения в ячейках на
других листах или книгах
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
57

58.

Лекция 1. Функции Excel
Подводя итоги ... (1#2)
В Excel очень много функций. Поэтому они разделены на категории
Математические функции производят базовые арифметические вычисления,
такие как суммирование или округление. Наиболее часто применяются СУММ,
СУММЕСЛИ, ОКРУГЛ (в различных вариациях).
Статистические функции рассчитывают числовые характеристики данных:
среднее, количество значений и др. Чаще всего применяют СРЗНАЧ,
СРЗНАЧЕСЛИ, НАИБОЛЬШИЙ/НАИМЕНЬШИЙ. Есть аналоги СУММЕСЛИ, только
для подсчета среднего, максимального и минимального значений.
Текстовые функции выполняют операции над текстом. Например, производят
замену одних символов на другие, извлекают отдельные символы из ячейки,
объединяют ячейки и многое другое.
Логические
функции проверяют выполнение заданных условий и в
зависимости от результата возвращаются значение или производят некоторые
вычисления. Функции ЕСЛИ, ЕСЛИОШИБКА входят в топ-10 всех функции Excel.
Функции И и ИЛИ позволяют учитывать несколько условий одновременно.
Алексеев Кирилл Анатольевич, КНИТУ им. А.Н.Туполева-КАИ, [email protected]
"Основы проектирования в САПР SolidWorks".
58

59.

Лекция 1. Функции Excel
Подводя итоги ... (2#2)
В последних версиях Exсel
(агрегированные) функции.
все
чаще
начинают
появляться
новые
Функции даты и времени производят операции с датами. Легко определить
количество рабочих дней между датами, рассчитать дату, отстающую от
заданной на указанное количество дней, учитывать праздники и режим рабочей
недели.
Функции ссылок и массивов выполняют задачи поиска данных или ячеек в
массивах. Эти функции являются рабочим инструментом при работе с большими
таблицами. Так, ВПР или аналоги быстро находят нужные ячейки по заданному
критерию.
Инструменты работы с функциями. Для более удобной работы с функциями
используются различные инструменты использования именованных ячеек и
диапазонов, контроля вычислений, поиска и исправления ошибок.
Алексеев Кирилл Анатольевич, КНИТУ им. А.Н.Туполева-КАИ, [email protected]
"Основы проектирования в САПР SolidWorks".
59

60.

Лекция 2. Форматирование
Общие рекомендации
Не увлекайтесь «раскрашиванием». Вместо ярких цветов используйте оттенки.
Границы между ячейками отделяйте едва заметно
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
60

61.

Лекция 2. Форматирование.
Числовые форматы.
Шаблоны форматов
Все типы форматов в диалоге «Формат ячеек» являются заранее
подготовленными шаблонами, которые могут быть созданы в разделе «(все
форматы)»
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
61

62.

Лекция 2. Форматирование.
Пользовательские форматы.
Пользовательские форматы (1#2)
Все шаблоны форматов в диалоге «Формат ячеек» имеют свой специальный код,
по которому Excel распознает формат ячейки.
Например, рассмотрим положительное число в числовом формате с тремя
знаками после запятой, отображаемое в красном цвете.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
62

63.

Лекция 2. Форматирование.
Пользовательские форматы.
Пользовательские форматы (2#2)
Пользовательский код состоит из четырех разделов, отделяемых точкой с
запятой: ПОЛОЖИТЕЛЬНЫЕ ЧИСЛА;ОТРИЦАТЕЛЬНЫЕ ЧИСЛА;НОЛЬ;ТЕКСТ
Если в шаблоне заполнен только один раздел – он применяется для всех типов
данных. Если заполнено два раздела – первый применяется для положительных
чисел и нулей, второй – для отрицательных. Три раздела используются для
положительных чисел, отрицательных чисел и нулей.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
63

64.

Лекция 2. Форматирование.
Пользовательские форматы.
Коды пользовательских форматов
Некоторые часто используемые коды пользовательского формата
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
64

65.

Лекция 2. Форматирование.
Пользовательские форматы.
Примеры пользовательских форматов (1#2)
Задачи:
1. Все положительные числа записывать без изменений, все отрицательные –
выделять красным цветом и заключать в скобки.
2. Отсечь от числа сотни и тысячи, оставить только миллионы и выше.
3. Отсечь незначащий нуль в числе, месяц написать словами, добавить день
недели.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
65

66.

Лекция 2. Форматирование.
Пользовательские форматы.
Примеры пользовательских форматов (2#2)
Некоторые интересные случаи применения пользовательских форматов
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
66

67.

Лекция 2. Форматирование.
Пример оформления.
Пример оформления небольшой таблицы
Незначащие нули в листе скрываются командой Файл Параметры
Дополнительно Параметры отображения листа Показывать нули …
Для очистки форматов используйте кнопку Очистить
на вкладке Главная
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
67

68.

Лекция 2. Форматирование.
Защита информации.
Защита ячеек
По умолчанию, все ячейки листа имеют статус защищаемых. Процедура защита
ячейки приводится в действие командой Рецензирование Защитить лист.
Статус защищаемости ячеек можно изменить в диалоге Формат ячеек Защита
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
68

69.

Лекция 2. Форматирование.
Готовые правила.
Условное форматирование ячеек – готовые правила (1#3)
Условное форматирование изменяет формат ячейки, если она соответствует
указанному правилу
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
69

70.

Лекция 2. Форматирование.
Готовые правила.
Условное форматирование ячеек – готовые правила (2#3)
Выделение цветом значительно повышает наглядность данных.
К одному и тому же диапазону данных
можно применять сразу несколько правил
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
70

71.

Лекция 2. Форматирование.
Готовые правила.
Условное форматирование ячеек – готовые правила (3#3)
Значки, гистограммы и цветовые шкалы являются промежуточным звеном между
вычислениями формул и диаграммами
Для изменения правила снова выделите диапазон и выберите
команду Условное форматирование Управление правилами
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
71

72.

Лекция 2. Форматирование.
Собственные правила.
Условное форматирование ячеек – пользовательские правила
В диалоге Создание правила форматирования наиболее общим правилом
является Использовать формулу… Все создаваемые правила в нем являются
логическими. Если условие выполняется – применяются правила выделения.
Если не выполняется – не применяются.
Если правил несколько, то выполняются они в порядке следования. При
достижении первой ИСТИНЫ, следующие правила уже не выполняются
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
72

73.

Лекция 2. Форматирование.
Собственные правила.
Примеры пользовательского условного форматирования (1#10)
Задача: выделить в диапазоне текстовые значения
Формат:
=ЕТЕКСТ(значение) – проверяет, является ли значение текстом
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
73

74.

Лекция 2. Форматирование.
Собственные правила.
Примеры пользовательского условного форматирования (2#10)
Абсолютная адресация распространяет правило с текущей ячейки на:
$A$4 - весь диапазон
A$4 – все столбцы, первые ячейки которых содержат текст
$A4 – все строки, четвертые ячейки которых содержат текст
Относительная адресация применяет правило к каждой конкретной ячейке вне
зависимости от того, какая ячейка является текущей.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
74

75.

Лекция 2. Форматирование.
Собственные правила.
Примеры пользовательского условного форматирования (3#10)
Задача: выделить числа, совпадающие с заданным числом
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
75

76.

Лекция 2. Форматирование.
Собственные правила.
Примеры пользовательского условного форматирования (4#10)
Задача: выделить выходные дни в диапазоне с датами
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
76

77.

Лекция 2. Форматирование.
Собственные правила.
Примеры пользовательского условного форматирования (5#10)
Задача: выделить строки с товарными запасами (ТЗ) менее 3 и менее 2 разными
цветами
Шаг 1. Сначала красным цветом выделим строки с наиболее критичными ТЗ<2
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
77

78.

Лекция 2. Форматирование.
Собственные правила.
Примеры пользовательского условного форматирования (6#10)
Шаг 2. Желтым цветом выделим строки с менее критичными 2<ТЗ<3
Шаг 3. Изменим порядок следования правил
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
78

79.

Лекция 2. Форматирование.
Собственные правила.
Примеры пользовательского условного форматирования (7#10)
Задача: выделить цветами ячейки с перевыполнением и недовыполнением плана
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
79

80.

Лекция 2. Форматирование.
Собственные правила.
Примеры пользовательского условного форматирования (8#10)
Задача: сделать чередование цвета по строкам
Формат:
=СТРОКА(ссылка) – возвращает номер строки
=ОСТАТ(число;делитель) – возвращает остаток от деления
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
80

81.

Лекция 2. Форматирование.
Собственные правила.
Примеры пользовательского условного форматирования (8#10)
Задача: показать итог только после заполнения всех ячеек
Шаг 1. Сначала создадим правило, выделяющее желтым цветом пустые ячейки и
введем формулу суммы
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
81

82.

Лекция 2. Форматирование.
Собственные правила.
Примеры пользовательского условного форматирования (9#10)
Шаг 2. Сделаем бесцветным по умолчанию шрифт текста в ячейке с суммой
Шаг 3. Создадим правило, выделяющее ячейку с суммой красным полужирным
шрифтом, если количество значений в заполняемом диапазоне равно пяти
Формат:
=СЧЁТ(значение1;[значение2];…) – подсчитывает количество ячеек в диапазоне
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
82

83.

Лекция 2. Форматирование.
Собственные правила.
Примеры пользовательского условного форматирования (10#10)
Задача: найти в новом диапазоне значения, отсутствующие в старом диапазоне
Формат:
=СЧЁТЕСЛИ(диапазон;критерий) – подсчитывает количество ячеек по условию
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
83

84.

Лекция 2. Форматирование.
Собственные правила.
Поиск и удаление условного форматирования
Внешний вид ячеек с условным форматированием ничем не отличается от
традиционного форматирования. Для поиска и выделения таких ячеек
используйте команду Найти Выделить Условное форматирование.
Для
удаления
форматирования
форматирование Удалить правила
используйте
команду
Условное
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
84

85.

Лекция 2. Форматирование
Подводя итоги ...
Знание правил форматирования ячеек значительно ускоряет анализ данных,
повышает эффективность их обработки и переводит работу в Excel на
качественно новый уровень.
Обычные
форматы
позволяют сделать
данные
более
удобными
для
восприятия.
Пользовательские
форматы дают возможность создавать собственные
форматы ячеек, например, скрыть нули, отобразить число в 1000 раз меньше и
т.д.
Защита листа не позволит специально или случайно изменить ячейки.
Условный формат дает пользователю возможность анализировать данные
визуально.
Алексеев Кирилл Анатольевич, КНИТУ им. А.Н.Туполева-КАИ, [email protected]
"Основы проектирования в САПР SolidWorks".
85

86.

Лекция 3. Управление данными.
Ввод данных.
Переход между ячейками
Перед обработкой данных их надо ввести в ячейки.
Для перехода в следующую ячейку можно использовать следующие способы:
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
86

87.

Лекция 3. Управление данными.
Ввод данных.
Ввод множественных данных в диапазон
Для заполнения данными всего выделенного диапазона введите значение (или
формулу) в первую ячейку и нажмите комбинацию клавиш Ctrl+Enter
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
87

88.

Лекция 3. Управление данными.
Ввод данных.
Заполнение только пустых ячеек
Для заполнения данными только пустых ячеек выделите весь диапазон, затем
выделите только пустые ячейки, введите формулу в первую пустую ячейку и
нажмите комбинацию клавиш Ctrl+Enter. Для удаления формул скопируйте
диапазон и используйте специальную вставку Только значения.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
88

89.

Лекция 3. Управление данными.
Ввод данных.
Ввод повторяющихся данных
При вводе в столбец большого количества данных, некоторые из которых
повторяются, комбинация клавиш Alt+ покажет все уникальные значения в
столбце и позволит вставить требуемое.
Предварительное выделение диапазона из нескольких строк и столбцов позволит
при помощи только одной клавиши Enter поочередно заполнить все ячейки.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
89

90.

Лекция 3. Управление данными.
Ввод данных.
Автозаполнение
Двойной щелчок в правом нижнем углу заполненной ячейки автоматически
заполнит этот столбец до конца соседнего. Аналогичная операция с двумя
выделенными числовыми значениями создаст арифметическую прогрессию с
шагом, равным разности между числами.
При заполнении дат удобно пользоваться выпадающим списком для выбора
вариантов заполнения.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
90

91.

Лекция 3. Управление данными.
Ввод данных.
Прогрессия
Все вышеперечисленные варианты автозаполнения
случаями более общей команды Прогрессия
являются
частными
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
91

92.

Лекция 3. Управление данными.
Ввод данных.
Пользовательские списки
При протягивании названий месяцев или дней недели Excel автоматически
заполняет диапазон. Для такого заполнения используются пользовательские
списки, создать или изменить которые можно в меню:
Файл Параметры Дополнительно Общие Изменить списки…
Примеры практического использования списков см. Л.06 Сводные таблицы.
Сортировка по пользовательскому списку.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
92

93.

Лекция 3. Управление данными.
Копирование.
Перемещение и выделение без мышки (1#2)
Навыки перемещения и выделения больших диапазонов данных без мышки
существенно ускоряют работу.
Для перемещения на одну ячейку используются клавиши со стрелками. Для
перемещения в конец диапазона данных используйте клавиши Ctrl+стрелка. Для
выделения – Ctrl+Shift+стрелка.
Первое нажатие комбинации клавиш Ctrl+A выделяет текущий диапазон, второе
– весь лист.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
93

94.

Лекция 3. Управление данными.
Копирование.
Перемещение и выделение без мышки (2#2)
Для выделения всей строки листа используйте комбинацию
Shift+пробел, выделения всего столбца – Ctrl+пробел.
клавиш
–
Выделение можно продолжить, используя клавиши Shift и стрелки.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
94

95.

Лекция 3. Управление данными.
Копирование.
Копирование и вставка
При копировании данных необходимо помнить, что содержимое ячеек это не
только видимые на экране числа или текст. Ячейка может содержать различную
информацию - заливку, обрамление, формулы, ширину столбца и т.д.
В буфер обмена копируются все части этой информации, а вот вставить можно,
каждую часть по отдельности. При простом копировании и вставке часто может
возникнуть ошибка.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
95

96.

Лекция 3. Управление данными.
Копирование.
Специальная вставка (1#3)
Для вставки отдельных частей информации о ячейке используют Специальную
вставку.
Задача: создать полную копию таблицы, но без столбца Факт.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
96

97.

Лекция 3. Управление данными.
Копирование.
Специальная вставка (2#3)
Специальная вставка позволяет в процессе работы выполнять элементарные
математические операции.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
97

98.

Лекция 3. Управление данными.
Копирование.
Специальная вставка (3#3)
Функция Пропускать пустые ячейки исключает замену имеющихся данных
пустыми значениями, а Транспонирование меняет местами столбцы и строки
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
98

99.

Лекция 3. Управление данными.
Поиск&Выделение.
Поиск всех вхождений
Для поиска в требуемом диапазоне его следует выделить. В противном случае
Excel ищет по всему листу или книге.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
99

100.

Лекция 3. Управление данными.
Поиск&Выделение.
Подстановочные символы
Подстановочные символы заменяют один или группу произвольных символов:
? – ищет один любой символ;
* – ищет группу любых символов.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
100

101.

Лекция 3. Управление данными.
Поиск&Выделение.
Поиск формул и значений
В ячейках Excel различают два типа данных, которые:
-
записываются пользователем вручную (в адресной строке или ячейке);
-
вычисляются формулой.
Для поиска записанных и вычисленных значений используйте функцию
Значения. Для поиска только записанных данных используйте Формулы.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
101

102.

Лекция 3. Управление данными.
Поиск&Выделение.
Выделение ячеек
В Excel предусмотрено несколько десятков способов выделения ячеек с данными
различного типа
Для выделения только видимых ячеек используйте горячую
клавишу Alt + ;
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
102

103.

Лекция 3. Управление данными.
Сортировка.
Сортировка
Инструменты сортировки находятся на вкладке Данные. Перед выполнением
команды сортируемые данные необходимо выделить.
Задача: Отсортировать данные по трем критериям в порядке их указания –
Регион, Менеджер и Группа.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
103

104.

Лекция 3. Управление данными.
Сортировка.
Пользовательская сортировка
При необходимости создать свою собственную последовательность сортировки
хорошим способом будет создать соответствующий список (порядок создания
списков см. Л.03. Управление данными. Пользовательские списки) и в диалоге
Сортировка, в разделе Порядок выбрать требуемый пользовательский список.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
104

105.

Лекция 3. Управление данными.
Сортировка.
Сортировка по цвету
В результатах сортировки можно собрать выделенные цветом ячейки, которые до
этого были разбросаны по всему диапазону.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
105

106.

Лекция 3. Управление данными.
Сортировка.
Сортировка по столбцам
При сортировке по столбцам обязательно должны быть выделены заголовки
столбцов, по которым будет выполнена сортировка
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
106

107.

Лекция 3. Управление данными.
Сортировка.
Сортировка Промежуточных итогов (1#2)
В Лекции 1 мы изучали функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая вычисляла
промежуточные суммы в структурированном диапазоне вместе с общим итогом.
Такие структурированные списки можно создавать автоматически командой
Промежуточные итоги. Более подробно эта команда будет рассмотрена в
разделе Структурированные группы.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
107

108.

Лекция 3. Управление данными.
Сортировка.
Сортировка Промежуточных итогов (2#2)
Для изменения порядка сортировки внутри структуры (например, чтобы группы
товаров сортировались по убыванию, но товары внутри группы не менялись)
необходимо сгруппировать структуру до уровня групп и в диалоге Сортировка
указать требуемые параметры.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
108

109.

Лекция 3. Управление данными.
Сортировка.
Сортировка данных случайным образом
Для перемешивания строк случайным образом используйте случайные числа,
генерируемые функцией
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
109

110.

Лекция 3. Управление данными.
Фильтрация.
Фильтр
Инструмент Фильтр находится на вкладке Данные.
В случае большого количества уникальных данных используйте строку поиска
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
110

111.

Лекция 3. Управление данными.
Фильтрация.
Фильтрация текста
Помимо общих правил способы фильтрации отличаются для следующих типов
данных: текст, число, дата и цвет.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
111

112.

Лекция 3. Управление данными.
Фильтрация.
Фильтрация чисел
Для чисел можно указывать принадлежность к числовому диапазону.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
112

113.

Лекция 3. Управление данными.
Фильтрация.
Фильтрация дат
Даты автоматически группируются в фильтре по годам и месяцам.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
113

114.

Лекция 3. Управление данными.
Фильтрация.
Фильтрация по цвету
Если столбец содержит ячейки с заливкой цветом становится доступным Фильтр
по цвету.
Для повторения последней операции используйте клавишу F4
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
114

115.

Лекция 3. Управление данными.
Фильтрация.
Быстрый фильтр
Находясь в ячейке можно быстро выполнить фильтр по ее содержимому.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
115

116.

Лекция 3. Управление данными.
Фильтрация.
Фильтр по нескольким столбцам
Для выключения всех фильтров на листе используйте кнопку
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
116

117.

Лекция 3. Управление данными.
Фильтрация.
Вычисления в отфильтрованных данных
Функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ выполняют вычисления
с учетом отфильтрованных данных. Остальные функции вычисляют значения
без учета отфильтрованных данных.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
117

118.

Лекция 3. Управление данными.
Структурированные группы.
Локальная группировка (1#2)
Самый простой способ скрыть или отобразить отдельные строки или столбцы –
использовать команды Скрыть, Показать.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
118

119.

Лекция 3. Управление данными.
Структурированные группы.
Локальная группировка (2#2)
Группировка данных предоставляет больше возможностей по структурированию
отдельных диапазонов.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
119

120.

Лекция 3. Управление данными.
Структурированные группы.
Структура
Для создания нескольких структурированных групп по строкам и/или по столбцам
используйте команду Создать структуру. Если заголовки таблицы разбиты на
логические группы и отсортированы, то Excel автоматически определит глубину
структуры и количество групп по строкам и столбцам.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
120

121.

Лекция 3. Управление данными.
Структурированные группы.
Промежуточные итоги (1#4)
Команда Промежуточный итог создает структуру из групп ячеек, объединенных
общим
признаком
и
автоматически
добавляет
формулу
ПРОМЕЖУТОЧНЫЕ.ИТОГИ между группами. Перед выполнением команды
группы должны быть отсортированы по критерию общего признака.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
121

122.

Лекция 3. Управление данными.
Структурированные группы.
Промежуточные итоги (2#4)
В диалоге Промежуточный итог необходимо указать группировочный
признак, вид вычисления (обычно – сумма) и столбец, в котором выполняется
вычисление. По окончании Excel автоматически формирует структурированный
список, между группами которого вставляет строки с итогами по группам и
общий итог.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
122

123.

Лекция 3. Управление данными.
Структурированные группы.
Промежуточные итоги (3#4)
Приятным бонусом в этой команде является возможность разделить каждую
группу по страницам и распечатать на отдельных листах. Эту возможность, в
частности, можно использовать для составления инвентаризационных
ведомостей
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
123

124.

Лекция 3. Управление данными.
Структурированные группы.
Промежуточные итоги (4#4)
При необходимости скопировать сгруппированные данные в новое место
используйте команду Только видимые ячейки.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
124

125.

Лекция 3. Управление данными.
Дополнительные инструменты.
Текст по столбцам (1#4)
Для разделения текста на части удобно использовать команду Текст по
столбцам.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
125

126.

Лекция 3. Управление данными.
Дополнительные инструменты.
Текст по столбцам (2#4)
В следующих двух диалогах необходимо указать тип разделителя,
местоположение первой ячейки итогового текста и пропускаемые столбцы.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
126

127.

Лекция 3. Управление данными.
Дополнительные инструменты.
Текст по столбцам (3#4)
Если преобразуемые данные имеют постоянное количество символов можно
использовать опцию Фиксированная ширина.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
127

128.

Лекция 3. Управление данными.
Дополнительные инструменты.
Текст по столбцам (4#4)
Даты в последнем примере можно было также преобразовать с использованием
функции Дата.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
128

129.

Лекция 3. Управление данными.
Дополнительные инструменты.
Мгновенное заполнение (1#3)
Функция Мгновенное заполнение автоматически заполняет
обнаружении закономерности с данными в соседних столбцах.
данные
при
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
129

130.

Лекция 3. Управление данными.
Дополнительные инструменты.
Мгновенное заполнение (2#3)
Функция Мгновенное заполнение может вычленять отдельные символы из
текста, добавлять новые символы и объединять данные из нескольких столбцов.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
130

131.

Лекция 3. Управление данными.
Дополнительные инструменты.
Мгновенное заполнение (3#3)
Если Excel не хватает данных и он начинает делать ошибки, «помогите» ему,
продолжив в следующей ячейке аналогичную запись.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
131

132.

Лекция 3. Управление данными.
Дополнительные инструменты.
Удаление дубликатов (1#2)
Особенностями команды Удалить дубликаты является возможность поиска
дубликатов сразу по нескольким столбцам/строкам и удаление их без
предупреждения.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
132

133.

Лекция 3. Управление данными.
Дополнительные инструменты.
Удаление дубликатов (2#2)
Поиск в одном столбце удаляет все строки с одинаковыми значениями в ячейках.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
133

134.

Лекция 3. Управление данными.
Дополнительные инструменты.
Проверка данных (1#5)
При необходимости ограничить ввод данных в ячейку каким-то определённым
типом значений используйте команду Проверка данных.
Задача: ограничить ввод данных в ячейки целыми числами от 1 до 100
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
134

135.

Лекция 3. Управление данными.
Дополнительные инструменты.
Проверка данных (2#5)
Задача: ограничить бюджет закупаемых продуктов – не более 500 USD.
Результатом вычисления формулы должны быть
ИСТИНА или ЛОЖЬ
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
135

136.

Лекция 3. Управление данными.
Дополнительные инструменты.
Проверка данных (3#5)
Задача: ограничить ввод дат всеми днями, кроме воскресенья.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
136

137.

Лекция 3. Управление данными.
Дополнительные инструменты.
Проверка данных (4#5)
Задача: вводить данные не вручную, а только из заранее подготовленного списка
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
137

138.

Лекция 3. Управление данными.
Дополнительные инструменты.
Проверка данных (5#5)
Для использования автоматически расширяемого диапазона данных для списка
удобно использовать т.н. «умную» таблицу Excel. Для этого столбцу данных в
созданной «умной» таблице нужно присвоить имя и сопоставить это имя с
имеющимся списком в диалоге Проверка вводимых значений.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
138

139.

Лекция 3. Управление данными.
«Умные» таблицы Excel.
Введение
С точки зрения Excel то, что мы привыкли называть таблицами, на самым деле
является обычным диапазоном данных, слегка отформатированным для лучшего
восприятия.
«Умные» таблицы Excel отличаются от таких обычных таблиц тем, что имеют
специальный режим организации и хранения данных. В них выделяют название
таблицы, строку заголовков и данные без заголовков, итоги, строки и
столбцы.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
139

140.

Лекция 3. Управление данными.
«Умные» таблицы Excel.
Отличительные особенности (1#2)
-
Инструменты «Умных» таблиц находятся на вкладке Конструктор;
-
каждая таблица имеет свое имя;
-
каждый столбец таблицы получает автофильтр;
-
при пролистывании таблицы вниз ее заголовки совмещаются с названиями
заголовков листа;
-
для преобразования «умной» таблицы в обычную используйте команду
Преобразовать в диапазон
При вводе формулы она распространяется на весь столбец
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
140

141.

Лекция 3. Управление данными.
«Умные» таблицы Excel.
Отличительные особенности (2#2)
-
ссылки в формулах таблицы ссылаются не на отдельные ячейки, а на столбец
целиком;
-
ссылки извне таблицы ссылаются на имя таблицы.
Строки, добавленные снизу,
автоматически включаются в «умную» таблицу
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
141

142.

Лекция 3. Управление данными.
«Умные» таблицы Excel.
Срезы (1#2)
Срез – это инструмент фильтрации данных по одному или более критериям,
выполненный в виде графического элемента.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
142

143.

Лекция 3. Управление данными.
«Умные» таблицы Excel.
Срезы (2#2)
На вкладке Параметры
выбранного среза.
можно
настраивать
графическое
оформление
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
143

144.

Лекция 3. Управление данными
Подводя итоги ... (1#2)
Операции управления данными повышают эффективность работы на каждом из
этапов их обработки – от ввода до подготовки к печати. При умелом их
применении ваша работа больше никогда не станет прежней.
Этап ввода и копирования данных позволит сэкономить много времени.
Сортировку можно производить по нескольким полям, цвету и даже столбцам.
Фильтрацию также можно выполнять по числам, тексту, дате, цвету.
Команда Найти и заменить способна значительно улучшить качество поиска
данных в больших массивах, ускоряя работу в разы.
Текст по столбцам разделяет текстовое содержимое на несколько ячеек.
Мгновенное заполнение – это шедевр разработчиков Excel, позволяющий
заполнять столбец по заданному образцу в соседних столбцах.
Таблица
Excel – в большинстве случаях является лучшим способом
организации и хранения данных.
Алексеев Кирилл Анатольевич, КНИТУ им. А.Н.Туполева-КАИ, [email protected]
"Основы проектирования в САПР SolidWorks".
144

145.

Лекция 4. Настройки Excel.
Восстановление данных.
Закрепление файлов
Перед началом изучения этого урока создайте новый документ с произвольным
текстом для демонстрации возможностей Excel по восстановлению данных
Часто используемые файлы можно закреплять на вкладке Файл – Открыть,
чтобы ускорить к ним доступ.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
145

146.

Лекция 4. Настройки Excel.
Восстановление данных.
Возврат к автосохраненной копии (1#2)
Для возврата к промежуточному состоянию документа в текущем сеансе
используйте автосохраненные копии в меню Файл Сведения. Параметры
автосохранения указываются в диалоге Файл Параметры Сохранение.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
146

147.

Лекция 4. Настройки Excel.
Восстановление данных.
Возврат к автосохраненной копии (2#2)
Если между последним пользовательским и автосохранением в документ были
внесены какие либо изменения Excel сообщит о доступности последней копии
этого файла в будущем. После открытия файла, автосохраненную копию можно
будет открыть в меню Файл Сведения. После сохранения все
автосохраненные копии удаляются.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
147

148.

Лекция 4. Настройки Excel.
Восстановление данных.
Восстановление нового несохраненного документа
При закрытии без сохранения нового документа, который даже не имеет имени,
но имеет какие-то данные, его можно восстановить, используя команду Файл
Сведения Управление книгой Восстановить несохраненные копии.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
148

149.

Лекция 4. Настройки Excel.
Вид и разметка страницы.
Разметка страницы. Область печати.
Excel позволяет задать несколько областей печати. В этом случае каждая
область печатается на отдельном листе. При необходимости распечатать
выделенный фрагмент или весь лист поставьте галочку Игнорировать область
печати.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
149

150.

Лекция 4. Настройки Excel.
Вид и разметка страницы.
Разметка страницы. Разрывы страниц.
Для того, чтобы каждый функционально самостоятельный диапазон данных
печатался с нового листа используйте разрывы страниц.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
150

151.

Лекция 4. Настройки Excel.
Вид и разметка страницы.
Разметка страницы. Печать заголовков.
Для повышения наглядности таблиц, размещаемых на двух и более страницах,
следует использовать функцию Печать заголовков.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
151

152.

Лекция 4. Настройки Excel.
Вид и разметка страницы.
Разметка страницы. Выравнивание объектов.
Графические объекты не только можно выравнивать по горизонтали, вертикали и
т.д., но и равномерно распределять по длине.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
152

153.

Лекция 4. Настройки Excel.
Вид и разметка страницы.
Вид. Режимы просмотра книги.
В страничном режиме можно не только увидеть, как будет печататься лист, но и
изменять границы печати.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
153

154.

Лекция 4. Настройки Excel.
Вид и разметка страницы.
Вид. Представления.
Функция
Представления
позволяет
сохранять
и
в
последующем
восстанавливать
пользовательские
настройки
отображения
ячеек,
скрытых/сгруппированных строк и столбцов, условия фильтрации и параметры
печати.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
154

155.

Лекция 4. Настройки Excel.
Вид и разметка страницы.
Вид. Два окна одной книги.
Команды Новое окно и Упорядочить все позволяют отображать данные
одновременно из разных мест листа или книги.
Имя файла одного и того же документа в заголовках окон получают
дополнительные цифры 1, 2 ….
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
155

156.

Лекция 4. Настройки Excel.
Вид и разметка страницы.
Вид. Синхронная прокрутка.
При работе с двумя открытыми документами или двумя окнами одного документа
после выбора кнопки Рядом становится доступной опция Синхронная
прокрутка.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
156

157.

Лекция 4. Настройки Excel.
Вид и разметка страницы.
Вид. Закрепление областей.
Функция Закрепить области закрепляет (предохраняет от прокрутки) строки и
столбцы, расположенные слева и выше текущей ячейки
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
157

158.

Лекция 5. Сводные таблицы.
Введение.
Постановка задачи
Сводные таблицы позволяют мгновенно осуществлять выборку из массива
данных по одному или нескольким критериям и содержат удобные инструменты
наглядного графического представления этих данных.
Для демонстрации возможностей сводных таблиц, сначала попытаемся
сформировать подобную таблицу с использованием знакомых инструментов.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
158

159.

Лекция 5. Сводные таблицы.
Введение.
Сводка с использованием формул (1#2)
1. Скопируем столбцы Область и Товар, удалим дубликаты и транспонируем
товары в строку.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
159

160.

Лекция 5. Сводные таблицы.
Введение.
Сводка с использованием формул (2#2)
2. Для удобства последующих расчетов создадим именованные диапазоны
Выручка, Область, Товар и с использованием СУММЕСЛИМН просуммируем
каждый товар по двум критериям: Область и Товар. Формат команды
=СУММЕСЛИМН(диапазон_суммирования;диапазон_условия1;условие1;…).
По окончании создадим итоги.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
160

161.

Лекция 5. Сводные таблицы.
Введение.
Анализ создания сводных таблиц с использованием формул
Вышеприведенный расчет, помимо своей трудоемкости, потребует больших
затрат времени в случае изменения критериев расчета. Например суммировать
не выручку, а прибыль, области расположить сверху, а товары сбоку, включить
суммирование по годам или сделать такой отчет для каждого клиента отдельно.
В этом случае вместо повторных вычислений следует использовать функционал
Сводных таблиц. Перед началом преобразуем данные в «Умную» таблицу Excel и
присвоим ей имя Данные.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
161

162.

Лекция 5. Сводные таблицы.
Введение.
Создание сводной таблицы
После создания «умной» таблицы на ленте открывается дополнительная вкладка
Конструктор, в которой находятся все необходимые команды.
Перед созданием сводной таблицы желательно выделить любую ячейку из
«умной» таблицы. После нажатия кнопки Сводная таблица надо указать ее
данные в диалоге Создание сводной таблицы.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
162

163.

Лекция 5. Сводные таблицы.
Введение.
Повторение ранее созданной сводки
Перед подробным изучением функционала сводных таблиц повторим ранее
созданную сводку. Для этого перетащим поля Область, Товар и Количество в
соответствующие области Списка полей.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
163

164.

Лекция 5. Сводные таблицы
Введение.
Изменение ранее созданной сводки (1#2)
Выполним ранее предложенные изменения. Просуммируем не выручку, а
прибыль. Области расположим сверху, а товары сбоку. Фильтрацию по годам
сделаем при помощи временной шкалы.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
164

165.

Лекция 5. Сводные таблицы.
Введение.
Изменение ранее созданной сводки (2#2)
Создание отчета для каждого клиента можно сделать при помощи поля
Фильтры. Или можно создать отдельный лист для каждого клиента.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
165

166.

Лекция 5. Сводные таблицы.
Интерфейс и терминология.
Интерфейс сводной таблицы
Инструменты для работы со сводной таблицы
находятся в меню Анализ и Конструктор на
ленте. Эти вкладки становятся доступными,
если щелкнуть по любой ячейке таблицы
Отображение Полей сводной
включается в команде Показать.
таблицы
Содержимое полей можно условно разделить
на:
-
Заголовки столбцов, импортируемых из
исходных данных
-
Области сводной таблицы, которые
включают все или некоторые заголовки
столбцов
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
166

167.

Лекция 5. Сводные таблицы.
Введение.
Четыре области сводной таблицы (1#2)
- Область значений – это данные в центральной части таблицы, над которыми
выполняются различные операции, чаще всего математические.
- Область строк (столбцов) – это уникальные значения, извлеченные из тех
столбцов исходных данных, которые находятся в области сводной таблицы. В эти
области обычно помещают качественные признаки (группа товаров и т.д). Данные
на пересечении строки и столбца – сумма значений соответствующих категорий.
- Область фильтров – фильтрует область значений по дополнительному одному
или нескольким критериям.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
167

168.

Лекция 5. Сводные таблицы.
Введение.
Четыре области сводной таблицы (2#2)
В областях Сводной таблицы можно размещать по несколько заголовков. В этом
случае Excel изменяет структуру данных.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
168

169.

Лекция 5. Сводные таблицы.
Введение.
Требования и рекомендации к исходным данным
-
Обязательно наличие названий каждого заголовка
-
Пустые текстовые ячейки в таблице могут исключить всю строку из расчетов. В
численных данных пустые ячейки рекомендуется заполнять нулями
-
Все данные должны иметь соответствующее форматирование: числа –
числовое, тексты – текстовое и т.д.
-
Для создания сводной таблицы рекомендуется использовать «умную» таблицу
-
Повторения в названиях столбцов могут вызвать ошибки вычисления
-
Ячейки внутри столбцов не должны содержать названия заголовков этих
столбцов
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
169

170.

Лекция 5. Сводные таблицы.
Введение.
Обновление и удаление сводной таблицы
Сводные таблицы не обновляются автоматически. Если в исходные данные
внесены новые значения сводную таблицу надо обновить командой Обновить.
Удаляются Сводные таблицы командой Действия Очистить.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
170

171.

Лекция 5. Сводные таблицы.
Настройки.
Косметические настройки (1#3)
Все настройки в сводных таблицах можно условно разделить на:
косметические – параметры оформления таблицы
структурные – макет, расположение полей, итоги, вычисления и т.д.
Многие из настроек находятся в диалоге Параметры сводной таблицы.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
171

172.

Лекция 5. Сводные таблицы.
Настройки.
Косметические настройки (2#3)
Некоторые характерные настройки диалога Параметры сводной таблицы
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
172

173.

Лекция 5. Сводные таблицы.
Настройки.
Косметические настройки (3#3)
Настройки числового формата на вкладке Главная изменяют только видимые
ячейки в области Значений. Для изменения числового формата для всех данных,
в том числе, которые будут обновляться в будущем, Сводные таблицы имеют
свой диалог Числовой формат.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
173

174.

Лекция 5. Сводные таблицы.
Настройки.
Структурные настройки (1#7)
Большинство структурных настроек находятся на вкладке Конструктор.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
174

175.

Лекция 5. Сводные таблицы.
Настройки.
Структурные настройки (2#7)
Сжатая форма Макета отчета размещает структуру в одном столбце
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
175

176.

Лекция 5. Сводные таблицы.
Настройки.
Структурные настройки (3#7)
Структурная форма Макета отчета размещает каждый уровень структуры в
своем столбце
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
176

177.

Лекция 5. Сводные таблицы.
Настройки.
Структурные настройки (4#7)
Табличная форма Макета отчета исключает пустые строки в названиях полей.
Промежуточные итоги в такой форме всегда располагаются под группой.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
177

178.

Лекция 5. Сводные таблицы.
Настройки.
Структурные настройки (5#7)
При большом количестве записей в полях строк нижнего уровня в Структурной и
Табличной формах Макета можно включить Повторение подписей элементов
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
178

179.

Лекция 5. Сводные таблицы.
Настройки.
Структурные настройки (6#7)
При необходимости, группы структуры можно разделить пустыми строками.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
179

180.

Лекция 5. Сводные таблицы.
Настройки.
Структурные настройки (7#7)
При управления сворачиванием и разворачиванием большого количества полей
используйте команды Свернуть и Развернуть поле на вкладке Анализ.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
180

181.

Лекция 5. Сводные таблицы.
Способы агрегирования данных.
Два уровня агрегирования
Сводные таблицы Excel содержат два уровня вычисления данных
(агрегирования). Второй уровень агрегирования применяется дополнительно к
первому.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
181

182.

Лекция 5. Сводные таблицы.
Способы агрегирования данных.
Первый уровень агрегирования
Область Значения может содержать несколько заголовков столбцов, для каждого
из которых можно настроить любой способ из первого уровня агрегирования
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
182

183.

Лекция 5. Сводные таблицы.
Способы агрегирования данных.
Второй уровень агрегирования (1#8)
Второй уровень агрегирования позволяет выполнить
вычисления в области значений сводной таблицы.
дополнительные
Например, % от общей суммы делит каждое значение на общий итог (326785).
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
183

184.

Лекция 5. Сводные таблицы.
Способы агрегирования данных.
Второй уровень агрегирования (2#8)
Можно вычислить не только долю от общей выручки, но и в каком отношении
находится выручка каждого региона по отношению к указанному.
Например, в сравнении с Тверской величина выручки во всех остальных
областях выглядит следующим образом:
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
184

185.

Лекция 5. Сводные таблицы.
Способы агрегирования данных.
Второй уровень агрегирования (3#8)
Можно вычислять долю не только от общей выручки, но и отдельно по каждому
столбцу многоуровневой структуры.
Например, проценты продаж каждого менеджера по областям выглядят
следующим образом:
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
185

186.

Лекция 5. Сводные таблицы.
Способы агрегирования данных.
Второй уровень агрегирования (4#8)
Увеличение вложенности структуры путем добавления городов добавит к
таблице дополнительный уровень:
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
186

187.

Лекция 5. Сводные таблицы.
Способы агрегирования данных.
Второй уровень агрегирования (5#8)
Функция % от родительской суммы позволяет указать уровень структуры, по
отношению к которому рассчитывается доля
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
187

188.

Лекция 5. Сводные таблицы.
Способы агрегирования данных.
Второй уровень агрегирования (6#8)
Функция Отличие показывает абсолютную разницу между значением ячейки и
указанным элементом.
Например, прирост продаж по месяцам, начиная с января, будет выглядеть
следующим образом:
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
188

189.

Лекция 5. Сводные таблицы.
Способы агрегирования данных.
Второй уровень агрегирования (7#8)
Относительную разницу между значением ячейки и указанным элементом
(в %) дает функция Приведенное отличие.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
189

190.

Лекция 5. Сводные таблицы.
Способы агрегирования данных.
Второй уровень агрегирования (8#8)
Сортировка
от
минимального
(максимального)
к
(минимальному) определяет ранг (степень важности) строки.
максимальному
Например, ниже показано место каждого города в ранжированном ряду продаж:
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
190

191.

Лекция 5. Сводные таблицы.
Группировка данных.
Группировка по датам (1#4)
Несмотря на то, что сводные таблицы уже являются сгруппированными данными
по одному или нескольким признакам, иногда эту группировку нужно изменять.
Посмотрим динамику изменения выручки по датам. При перетаскивании поля
Дата в область Строки, Excel автоматически группирует даты по Годам,
Кварталам и Месяцам. При этом, в области Строки, создаются новые
виртуальные поля Годы и Кварталы, отсутствующие в исходных данных.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
191

192.

Лекция 5. Сводные таблицы.
Группировка данных.
Группировка по датам (2#4)
Изменить
группировку
дат
можно
командами
Группировать…
и
Разгруппировать… по щелчку ПКМ. После исключения поля Кварталы и
перетаскивания поля Годы в область заголовков получается вот такая картина
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
192

193.

Лекция 5. Сводные таблицы.
Группировка данных.
Группировка по датам (3#4)
Для группировки по неделям в диалоге Группировка потребуется указать
определить первый день недели в расчетном году, в качестве шага выбрать Дни
и ввести количество дней 7:
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
193

194.

Лекция 5. Сводные таблицы.
Группировка данных.
Группировка по датам (4#4)
Чтобы узнать, как распределяется выручка по дням недели, в исходных данных
придется создать новый столбец Дата с соответствующей формулой, а в
сводной таблице в области Строки вместо поля Дата добавить День недели.
Excel покажет, что максимальные продажи приходятся на среду.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
194

195.

Лекция 5. Сводные таблицы.
Группировка данных.
Группировка по интервалам (1#2)
Для анализа, в какие заданные диапазоны данных попадают числа, используют
группировку по интервалам.
Например, подсчитаем, как распределяется число сделок по суммам продаж. Для
этого преобразуем поле сумма в количество.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
195

196.

Лекция 5. Сводные таблицы.
Группировка данных.
Группировка по интервалам (2#2)
На втором этапе сгруппируем суммы от нуля до максимального текущего
значения с шагом 500. Вычисление % от общей суммы повысит наглядность
представления данных.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
196

197.

Лекция 5. Сводные таблицы.
Группировка данных.
Группировка по текстовым полям (1#3)
Это вид группировки не вычисляется автоматически, а задается по выделенному
диапазону.
Например, после ухода из компании менеджера Иванова, его области нужно
поделить между Сидоровым и Петровым, полученные области объединить в
регионы под названиями Юг и Север и посмотреть, какая нагрузка ляжет на
оставшихся работников.
Схема реорганизации:
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
197

198.

Лекция 5. Сводные таблицы.
Группировка данных.
Группировка по текстовым полям (2#3)
Поочередно выделим и сгруппируем Брянскую, Курскую, Орловскую области и
Калужскую, Смоленскую, Тверскую области. Полученные группы переименуем
в Юг и Север, а Область2 в Регионы.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
198

199.

Лекция 5. Сводные таблицы.
Группировка данных.
Группировка по текстовым полям (3#3)
Теперь, добавив в область столбцов показатель Годы, можно видеть динамику
изменения продаж по новым регионам, и что объемы продаж по Северу почти в
два раза превышает аналогичные показатели по Югу.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
199

200.

Лекция 5. Сводные таблицы.
Сортировка данных.
Сортировка по возрастанию/убыванию (1#3)
Сортировка данных в Сводных таблицах выполняется известной командой
Сортировка на вкладке Данные. Плюсом является то, что при изменении
исходных данных значения в таблице не только пересчитываются, но и заново
ранжируются в соответствии с указанными правилами.
Отсортируем, например, таблицу по убыванию выручки в Общем итоге:
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
200

201.

Лекция 5. Сводные таблицы.
Сортировка данных.
Сортировка по возрастанию/убыванию (2#3)
Если добавить дополнительное поле, например, Менеджер, то сортировка внутри
областей сохранится. Для включения в сортировку, надо «встать» в ячейку
одного из менеджеров и выполнить сортировку.
Для сортировки столбцов (по горизонтали), надо встать в любую ячейку их
заголовков или итогов.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
201

202.

Лекция 5. Сводные таблицы.
Сортировка данных.
Сортировка по возрастанию/убыванию (3#3)
Столбцы и строки, при необходимости, можно перетаскивать вручную
или переименовывать их заголовки
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
202

203.

Лекция 5. Сводные таблицы.
Сортировка данных.
Сортировка по пользовательскому списку (1#2)
Для больших объемов данных или при частом обновлении таблицы сортировка
вручную может занять много времени. В этом случае следует воспользоваться
пользовательскими списками.
Заранее создайте список в требуемой последовательности. Пример создания
пользовательского списка см. Л03. Ввод данных. Пользовательские списки.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
203

204.

Лекция 5. Сводные таблицы.
Сортировка данных.
Сортировка по пользовательскому списку (2#2)
Для применения созданного списка выберите в выпадающем меню заголовка
Название столбцов команду Дополнительные параметры сортировки…, и в
следующих двух диалогах настройте все необходимые параметры.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
204

205.

Лекция 5. Сводные таблицы.
Фильтрация и срезы.
Фильтры
Фильтрация данных – один из ключевых инструментов Сводных таблиц,
позволяющий оживить отчет за счет интерактивного выбора нужного среза.
Инструменты для работы с фильтрами находятся в выпадающем меню
заголовков столбцов. Видимость заголовков включается командой Показать на
вкладке Анализ.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
205

206.

Лекция 5. Сводные таблицы.
Фильтрация и срезы.
Типы фильтров
Фильтры по подписи – фильтруют названия строк или столбцов, обладающих
общими признаками. Фильтры по значению – фильтруют ячейки в области
данных.
В отличие от обычных диапазонов Excel, в сводных таблицах есть возможность
фильтрации по столбцам. Отфильтруем, например, области и товары с
величиной выручки более 100 тыс.руб.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
206

207.

Лекция 5. Сводные таблицы.
Фильтрация и срезы.
Отображение топовых позиций
Для отображения полей с максимальным
используйте фильтр Первые 10.
или
минимальным
рейтингом
Покажем, например, первые пять городов с максимальными продажами.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
207

208.

Лекция 5. Сводные таблицы.
Фильтрация и срезы.
Фильтры по дате
Для Сводных таблиц с датами в строках
становится доступным Фильтры по дате.
Например, внизу показаны продажи с
15.10.2015 по 15.12.2015.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
208

209.

Лекция 5. Сводные таблицы.
Фильтрация и срезы.
Использование области Фильтры
Поля в области Фильтры позволяют фильтровать строки по значениям
отсутствующим в списке.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
209

210.

Лекция 5. Сводные таблицы.
Фильтрация и срезы.
Разбиение сводной таблицы на листы Excel
Сводную таблицу можно разбить на отдельные таблицы для указанных категорий
области Фильтр. Каждая из таких категории помещается на отдельный лист.
Например, если мы хотим продажи менеджера Иванова отдельно для каждой из
указанных компаний, надо настроить таблицу, выбрать команду Анализ
Сводная таблица Параметры Отобразить страницы фильтра и указать
фильтр, по которому Excel будет создавать страницы.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
210

211.

Лекция 5. Сводные таблицы.
Фильтрация и срезы.
Срезы
Срезы представляют более удобный, в сравнении с фильтрами, инструмент
сортировки данных по одному иди нескольким критериям.
Вставляются срезы на вкладке Анализ.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
211

212.

Лекция 5. Сводные таблицы.
Фильтрация и срезы.
Настройка внешнего вида Среза
При щелчке по срезу становится доступной вкладка Параметры, где можно
настроить внешний вид среза.
Дополнительные параметры среза настраиваются командой Настройки среза.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
212

213.

Лекция 5. Сводные таблицы.
Фильтрация и срезы.
Срезы для нескольких таблиц
По умолчанию, каждый срез управляет только одной таблицей. Для управления
несколькими таблицами используйте кнопку Подключения к отчетам на вкладке
Параметры для среза
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
213

214.

Лекция 5. Сводные таблицы.
Фильтрация и срезы.
Даты в Срезах
Если в сводной таблице присутствуют даты используйте Временную шкалу для
удобной фильтрации по дате
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
214

215.

Лекция 5. Сводные таблицы.
Вычисляемые поля и элементы.
Ссылки на Сводные таблицы извне
В одиночных ячейках, ссылающихся извне на значения Сводной таблицы, можно
использовать функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Чтобы эта
функция работала при растягивании диапазона, выключите опцию GetPivotData.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
215

216.

Лекция 5. Сводные таблицы.
Вычисляемые поля и элементы.
Вычисляемые поля и объекты
Однако, при изменении формы или структуры Сводной таблицы внешние ссылки
перестанут работать. Поэтому более правильным способом будет создать т.н.
вычисляемые поля и объекты внутри Сводной таблицы.
Вычисляемое поле – виртуальный столбец, образуемый путем операций со
столбцами Сводной таблицы (например, суммированием или делением)
Вычисляемое объект – виртуальная строка, образуемая подобно виртуальному
полю, но путем операций уже со строками Сводной таблицы.
Таким образом, в Сводной таблице можно
создавать поля, отсутствующие в Исходных
данных.
При изменении Исходных данных,
вычисляемые поля и объекты также
пересчитываются
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
216

217.

Лекция 5. Сводные таблицы.
Вычисляемые поля и элементы.
Вычисляемые поля (1#2)
Задача: создадим в Сводной
Рентабельность по областям
таблице
поля
Прибыль
и
среднюю
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
217

218.

Лекция 5. Сводные таблицы.
Вычисляемые поля и элементы.
Вычисляемые поля (2#2)
В процессе создания новые поля могут ссылаться на такие же виртуальные поля,
отсутствующие в Исходных данных
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
218

219.

Лекция 5. Сводные таблицы.
Вычисляемые поля и элементы.
Вычисляемые объекты (элементы)
Создадим Регионы, объединяющие по несколько областей и вычислим для них
средние значения выручки – Среднее Север (Тверская, Смоленская, Калужская)
и Среднее Юг (Орловская, Курская, Брянская)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
219

220.

Лекция 5. Сводные таблицы.
Вычисляемые поля и элементы.
Отображение формул вычисляемых полей/объектов
Для анализа зависимостей, использованных в вычисляемых полях/объектах
используйте команду Вывести формулы.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
220

221.

Лекция 5. Сводные таблицы.
Вычисляемые поля и элементы.
Ограничения вычисляемых объектов (1#4)
Сводные таблицы с вычисляемыми объектами имеют ряд ограничений:
-
Значения вычисляемых объектов учитываются в общих итогах
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
221

222.

Лекция 5. Сводные таблицы.
Вычисляемые поля и элементы.
Ограничения вычисляемых объектов (2#4)
-
Не действуют функции
отклонений и Дисперсии
вычисления
Средних
значений,
Стандартных
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
222

223.

Лекция 5. Сводные таблицы.
Вычисляемые поля и элементы.
Ограничения вычисляемых объектов (3#4)
-
Не выполняется группировка данных (в частности, не получится сгруппировать
даты в столбцах по месяцам, года и т.д.)
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
223

224.

Лекция 5. Сводные таблицы.
Вычисляемые поля и элементы.
Ограничения вычисляемых объектов (4#4)
-
Вычисляемые поля невозможно использовать в области Фильтров
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
224

225.

Лекция 5. Сводные таблицы.
Условное форматирование.
Условное форматирование. Гистограммы (1#2).
Типовые средства форматирования ячеек неудобны ввиду постоянного смещения
фокуса при изменении структуры Сводных таблиц. Поэтому использование
Условного форматирование многократно увеличивает наглядность расчетов.
Задача: исследовать объемы продаж в Областях по дням недели
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
225

226.

Лекция 5. Сводные таблицы.
Условное форматирование.
Условное форматирование. Гистограммы (2#2).
Для лучшей наглядности условное форматирование (гистограммы) можно
показать в продублированном столбце.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
226

227.

Лекция 5. Сводные таблицы.
Условное форматирование.
Условное форматирование. Тепловая карта.
Для улучшения наглядности большого количества данных, разбросанных в
широком диапазоне, используйте Трехцветную шкалу.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
227

228.

Лекция 5. Сводные таблицы.
Условное форматирование.
Условное форматирование. Топовые позиции.
Временная шкала подчеркивает динамику изменения топовых показателей.
Задача: найти пять топовых продаж товаров по областям
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
228

229.

Лекция 5. Сводные таблицы.
Сводные диаграммы.
Некоторые характерные особенности
Сводные диаграммы создаются одноименным инструментом в меню Вставка.
При этом названия строк всегда располагаются по оси Х. При изменении
расположения названий изменяется структура сводной таблицы. При выделении
диаграммы становятся доступными новые вкладки Конструктор и Формат.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
229

230.

Лекция 5. Сводные таблицы.
Сводные диаграммы.
Линейчатая диаграмма
Исключим из сводной таблицы поля Годы и Дата, перенесем поле Области в
поле Оси, изменим тип диаграммы на Линейчатая и слегка ее отформатируем.
Кнопки полей также можно исключить, т.к. фильтровать удобнее с
использованием Срезов.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
230

231.

Лекция 5. Сводные таблицы.
Сводные диаграммы.
Срезы и временная шкала в сводных диаграммах
Срезы и Временная шкала позволяют
фильтрацию в сводной диаграмме.
быстро
выполнять
требуемую
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
231

232.

Лекция 5. Сводные таблицы.
Сводные диаграммы.
Ограничение по типам графиков
Если нужный тип графика недоступен в сводной диаграмме, используйте
обычную диаграмму, данные которой ссылаются на сводную таблицу. Для таких
стандартных диаграмм продолжают действовать фильтры сводной таблицы.
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
232

233.

Часть 6 Домашнее задание
Образмеривание основной надписи
Образмерьте
ранее
созданную
основную
надпись .
Выполните эти операции с
рисунком Документы\Урок 01\01
первые шаги.dwg
Новые документы создаются на основе шаблонов
Алексеев Кирилл Анатольевич "Microsoft Excel (углубл.)". КНИТУ им. А.Н.Туполева - КАИ, [email protected]
233