Анализ данных с помощью сводных таблиц

Август 2, 2022

Главная
Информатика
Анализ данных с помощью сводных таблиц

Содержание

2. Ситуация Вы продолжаете работать над анализом неисправностей и замечаете, что на устранение некоторых поломок выделяется недостаточно
3. Необходимо провести анализ данных и определить: Сколько человеко-часов приходится на каждый простой? Каково суммарное количество человеко-часов,
4. Изучение сводных таблиц: Вертикальный ПРосмотр (ВПР). Продвинутая работа с ВПР. Функция ЕСЛИОШИБКА. Вычисление значений в сводной
5. Изучение сводных таблиц: Вертикальный ПРосмотр (ВПР). Продвинутая работа с ВПР. Функция ЕСЛИОШИБКА. Вычисление значений в сводной
6. Рассмотрим способ решения этой задачи на примере ситуации: на склад привезли книги, но данные о них
7. Функция ВПР Используется для поиска и извлечения значений в таблице или диапазоне. 01 Необходимые инструменты:
8. =ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр]) Что ищем? Значение для поиска. Можно указать вручную или при помощи ссылки.
9. Чтобы найти значение в диапазоне, необходимо: Выделить ячейку. Ввести =ВПР(. Выбрать значение для поиска (можно указать
10. Изучение сводных таблиц: Вертикальный ПРосмотр (ВПР). Продвинутая работа с ВПР. Функция ЕСЛИОШИБКА. Вычисление значений в сводной
11. Нам удалось корректно перенести одно из значений при помощи ВПР. Однако если протянуть функцию на весь
12. Интервальный просмотр Содержит информацию о том, допускается ли неполное совпадение содержимого ячейки с заданным параметром. 01
13. Чтобы настроить интервальный просмотр, нужно: За последним параметром функции ВПР поставить знак ; Если неполное совпадение
14. Нам удалось заполнить столбец корректными данными, но при этом в некоторых ячейках отображается значение «#Н/Д». Так
15. Функция ЕСЛИОШИБКА 01 Логическая функция, проверяющая, возвращает ли указанная формула ошибку или корректное значение. Если в
16. =ЕСЛИОШИБКА(значение; значение_если_ошибка) Проверяемый на ошибку аргумент. Можно использовать формулу, содержащую любые функции Excel. Значение, возвращаемое в
17. Чтобы поменять значения #Н/Д на заданный текст, необходимо: Активировать ячейку с формулой нажатием. Перед составленной формулой
18. Изучение сводных таблиц: Вертикальный ПРосмотр (ВПР). Продвинутая работа с ВПР. Функция ЕСЛИОШИБКА. Вычисление значений в сводной
19. Как в сводной таблице посчитать количество человеко-часов на специалиста? Как в сводной таблице создать новое поле
20. Вычисляемое поле Позволяет создать в сводной таблице новое вычисляемое поле с помощью заданной пользователем формулы. 01
21. Перед началом создания вычисляемого поля следует: Создать сводную таблицу для дальнейших вычислений. Сделать макет сводной таблицы,
22. Создадим поле для подсчёта выручки с учётом НДС: Щёлкнуть по сводной таблице. В Ленте нажать на
23. Изменение или удаление вычисляемого поля Щёлкнем по сводной таблице: 1 2 3 4 Перейти на вкладку
24. Изучение сводных таблиц: Вертикальный ПРосмотр (ВПР). Продвинутая работа с ВПР. Функция ЕСЛИОШИБКА. Вычисление значений в сводной
25. Дополните книгу. В основной таблице: Скачайте файл с исходными данными. Дополните основной лист информацией о специалистах-распределителях.
26. Изучение сводных таблиц: Вертикальный ПРосмотр (ВПР). Продвинутая работа с ВПР. Функция ЕСЛИОШИБКА. Вычисление значений в сводной
28. Скачать презентацию

Слайд 2

Ситуация
Вы продолжаете работать над анализом неисправностей и замечаете, что на устранение

некоторых поломок выделяется недостаточно ресурсов. Из-за этого ремонт затягивается и оборудование простаивает. Для оптимизации процесса следует оценить количество человеко-часов, затраченных на ремонт в период с 30 июня по 31 июля 2021 года. Помимо уже известных данных, таблица содержит несколько обновлённых значений.

Данные о количестве персонала, задействованного в ремонте

Дополнительная таблица с фамилиями специалистов-распределителей для каждой из групп ремонта

Слайд 3

Необходимо провести анализ данных и определить:
Сколько человеко-часов приходится на каждый простой?
Каково

суммарное количество человеко-часов, потраченных на устранение неисправностей, для каждого специалиста-распределителя?
Кто из специалистов-распределителей эффективнее всего использует ресурсы для устранения поломок?
Человеко-часов на простой:
Человеко-часы пр. = Количество персонала * Время простоя
Человеко-часов на специалиста:
Человеко-часы сп. = (Чч пр. 1 + Чч пр. 2 + Чч пр. 3 + …) * 24
Примечание: умножение на 24 делается потому, что при создании сводной таблицы в MS Excel время, данное в исходной таблице в часах, преобразуется в сутки.

Список вопросов

Слайд 4

Изучение сводных таблиц:
Вертикальный ПРосмотр (ВПР).
Продвинутая работа с ВПР. Функция

ЕСЛИОШИБКА.
Вычисление значений в сводной таблице.
Работа с несколькими таблицами данных.
Повторение пройденного материала.
Практическая работа.
Тестирование по практической работе.
Итоги занятия.

02
03
04
05

План урока:

Слайд 5

Изучение сводных таблиц:
Вертикальный ПРосмотр (ВПР).
Продвинутая работа с ВПР. Функция

02
03
04
05

План урока:

Слайд 6

Рассмотрим способ решения этой задачи на примере ситуации: на склад привезли книги, но

данные о них находятся на отдельном листе и расположены в другом порядке.

Как автоматизировать перенос данных с одного листа на другой?

Как дополнить строки релевантной информацией с другого листа?

Слайд 7

Функция ВПР
Используется для поиска и извлечения значений в таблице или диапазоне.
01
Необходимые

инструменты:

Слайд 8

=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])
Что ищем?
Значение для поиска. Можно указать вручную или

при помощи ссылки.

Где ищем?
Ссылка на диапазон ячеек, в первом столбце которого будет осуществляться поиск значения.

Откуда берём значение, которое вернёт формула?
Порядковый номер столбца, в интервале которого нужно взять возвращаемое значение.

Допустимы ли приблизительные совпадения?
Параметр поиска. Если указать 0, будут найдены точные совпадения, если указать 1 — приблизительные.

Синтаксис функции ВПР

Слайд 9

Чтобы найти значение в диапазоне, необходимо:
Выделить ячейку.
Ввести =ВПР(.
Выбрать значение для поиска

(можно указать ячейку со значением).
Ввести ; и выбрать диапазон для поиска значения. Ссылку на диапазон можно закрепить нажатием на F4 — она станет абсолютной.
Ввести ; и затем номер столбца, из которого будет возвращаться значение.

Работа с функцией ВПР

Слайд 10

Изучение сводных таблиц:
Вертикальный ПРосмотр (ВПР).
Продвинутая работа с ВПР. Функция

02
03
04
05

План урока:

Слайд 11

Нам удалось корректно перенести одно из значений при помощи ВПР. Однако

если протянуть функцию на весь столбец, обнаружится, что некоторые наименования, отсутствующие на складе, всё равно находятся.

«Собачье сердце» отсутствует на складе, но ВПР её находит. Почему это происходит и как это исправить?

Продвинутая работа с функцией ВПР

Слайд 12

Интервальный просмотр
Содержит информацию о том, допускается ли неполное совпадение содержимого ячейки

с заданным параметром.

Необходимые инструменты:

Слайд 13

Чтобы настроить интервальный просмотр, нужно:
За последним параметром функции ВПР поставить знак

;
Если неполное совпадение недопустимо, следует установить значение параметра «ЛОЖЬ» или 0.
Если при отсутствии точного совпадения разрешается выбрать значение, близкое к заданному, следует установить значение параметра «ИСТИНА» или 1.
Нажать на клавишу Enter.

Настройка интервального просмотра

Слайд 14

Нам удалось заполнить столбец корректными данными, но при этом в некоторых

ячейках отображается значение «#Н/Д». Так происходит, если искомое значение, указанное как первый аргумент для ВПР, не найдено.

Внешний вид таблицы и её читабельность испортились. Можно ли настроить таблицу так, чтобы вместо #Н/Д в ячейке отображалось другое значение?

#Н/Д может влиять на работу формул, которые ссылаются на ячейки, содержащие ошибку.

Разбор #Н/Д и функции ЕСЛИОШИБКА

Слайд 15

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

значение.
Если в результате выполнения формулы произошла ошибка, функция позволяет вывести в ячейку не сообщение об ошибке, а указанное пользователем значение.

Необходимые инструменты:

Слайд 16

=ЕСЛИОШИБКА(значение; значение_если_ошибка)
Проверяемый на ошибку аргумент. Можно использовать формулу, содержащую любые функции

Excel.

Значение, возвращаемое в случае ошибки. Задаётся пользователем.

Синтаксис функции ЕСЛИОШИБКА

Слайд 17

Чтобы поменять значения #Н/Д на заданный текст, необходимо:
Активировать ячейку с формулой

нажатием.
Перед составленной формулой ВПР ввести =ЕСЛИОШИБКА(.
За формулой ВПР поставить знак ; и вписать новое возвращаемое значение.
Закрыть скобку символом ) и нажать Enter.

Работа с функцией ЕСЛИОШИБКА

Слайд 18

Изучение сводных таблиц:
Вертикальный ПРосмотр (ВПР).
Продвинутая работа с ВПР. Функция

02
03
04
05

План урока:

Слайд 19

Как в сводной таблице посчитать количество человеко-часов на специалиста?
Как в сводной таблице создать новое поле

для подсчёта человеко-часов на специалиста?

Подсказка: формула со ссылками на ячейким сводной таблицы не будет работать корректно, т.к. при вычислении значения ячеек используются фамилии специалистов-распределителей.

Слайд 20

Вычисляемое поле
Позволяет создать в сводной таблице новое вычисляемое поле с помощью

заданной пользователем формулы.

Необходимые инструменты:

Слайд 21

Перед началом создания вычисляемого поля следует:
Создать сводную таблицу для дальнейших вычислений.
Сделать

макет сводной таблицы, добавив строки и/или столбцы.

Создание нового вычисляемого поля с формулой

Слайд 22

Создадим поле для подсчёта выручки с учётом НДС:
Щёлкнуть по сводной таблице.
В

Ленте нажать на вкладку «Анализ сводной таблицы».
В группе «Вычисления» выберите «Поля, элементы и наборы», а затем — пункт «Вычисляемое поле».
В поле «Имя» ввести имя для поля.
В поле «Формула» составить формулу для поля. Чтобы использовать в формуле данные из другого поля, необходимо щёлкнуть по нему в списке «Поля» и нажать кнопку «Добавить поле».
Нажать кнопку «Добавить», затем — ОК.
Созданное поле окажется в области значения, а также сохранится в перечне полей сводной таблицы. Его можно будет убрать и добавить снова при необходимости.

Создание нового вычисляемого поля с формулой

Слайд 23

Изменение или удаление вычисляемого поля
Щёлкнем по сводной таблице:
1
2
3
4
Перейти на вкладку «Анализ

сводной таблицы».
Выбрать опцию «Поля, элементы и наборы» ➜ «Вычисляемое поле…».
В появившемся окне выбрать область «Имя» и ввести имя нужного вычисляемого поля. Имя должно точно совпадать с тем, что есть в списке полей сводной таблицы.
Справа от области «Имя» появятся две опции: «Изменить» и «Удалить».
Опция «Изменить» позволяет переписать формулу заново, а опция «Удалить» — стереть выбранное поле из списка полей сводной таблицы.

Слайд 24

Изучение сводных таблиц:
Вертикальный ПРосмотр (ВПР).
Продвинутая работа с ВПР. Функция

02
03
04
05

План урока:

Слайд 25

Дополните книгу.
В основной таблице:
Скачайте файл с исходными данными.
Дополните основной лист информацией

о специалистах-распределителях.
Посчитайте в отдельном столбце человеко-часы на простой по формуле:
Человеко-часы пр. = Количество персонала * Время простоя
Создайте сводную таблицу с информацией о специалистах. В сводной таблице:
Для каждого из сменщиков рассчитайте общее количество потраченных на исправление неисправностей человеко-часов по формуле:
Человеко-часы сп. = (Чч пр. 1 + Чч пр. 2 + Чч пр. 3 + …) * 24
Убедитесь в том, что в отчёте приведены только значения за период с 30 июня по 31 июля. Определите, кому из специалистов-распределителей удаётся эффективнее использовать ресурсы для устранения неисправностей на производстве.

Практическая работа

Слайд 26

Изучение сводных таблиц:
Вертикальный ПРосмотр (ВПР).
Продвинутая работа с ВПР. Функция

02
03
04
05

План урока:

Анализ данных с помощью сводных таблиц

Содержание

СитуацияВы продолжаете работать над анализом неисправностей и замечаете, что на устранение

Необходимо провести анализ данных и определить:Сколько человеко-часов приходится на каждый простой? Каково

Изучение сводных таблиц: Вертикальный ПРосмотр (ВПР). Продвинутая работа с ВПР. Функция

Изучение сводных таблиц: Вертикальный ПРосмотр (ВПР). Продвинутая работа с ВПР. Функция

Рассмотрим способ решения этой задачи на примере ситуации: на склад привезли книги, но

Функция ВПРИспользуется для поиска и извлечения значений в таблице или диапазоне.01Необходимые

=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])Что ищем?Значение для поиска. Можно указать вручную или

Чтобы найти значение в диапазоне, необходимо:Выделить ячейку.Ввести =ВПР(.Выбрать значение для поиска

Изучение сводных таблиц: Вертикальный ПРосмотр (ВПР). Продвинутая работа с ВПР. Функция

Нам удалось корректно перенести одно из значений при помощи ВПР. Однако

Интервальный просмотрСодержит информацию о том, допускается ли неполное совпадение содержимого ячейки

Чтобы настроить интервальный просмотр, нужно:За последним параметром функции ВПР поставить знак

Нам удалось заполнить столбец корректными данными, но при этом в некоторых

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

=ЕСЛИОШИБКА(значение; значение_если_ошибка)Проверяемый на ошибку аргумент. Можно использовать формулу, содержащую любые функции

Чтобы поменять значения #Н/Д на заданный текст, необходимо:Активировать ячейку с формулой

Изучение сводных таблиц: Вертикальный ПРосмотр (ВПР). Продвинутая работа с ВПР. Функция

Как в сводной таблице посчитать количество человеко-часов на специалиста?Как в сводной таблице создать новое поле

Вычисляемое полеПозволяет создать в сводной таблице новое вычисляемое поле с помощью

Перед началом создания вычисляемого поля следует:Создать сводную таблицу для дальнейших вычислений.Сделать

Создадим поле для подсчёта выручки с учётом НДС:Щёлкнуть по сводной таблице.В

Изменение или удаление вычисляемого поляЩёлкнем по сводной таблице:1234Перейти на вкладку «Анализ

Изучение сводных таблиц: Вертикальный ПРосмотр (ВПР). Продвинутая работа с ВПР. Функция

Дополните книгу.В основной таблице:Скачайте файл с исходными данными.Дополните основной лист информацией

Изучение сводных таблиц: Вертикальный ПРосмотр (ВПР). Продвинутая работа с ВПР. Функция

Похожие презентации

Ситуация
Вы продолжаете работать над анализом неисправностей и замечаете, что на устранение

Необходимо провести анализ данных и определить:
Сколько человеко-часов приходится на каждый простой?
Каково

Изучение сводных таблиц:
Вертикальный ПРосмотр (ВПР).
Продвинутая работа с ВПР. Функция

Изучение сводных таблиц:
Вертикальный ПРосмотр (ВПР).
Продвинутая работа с ВПР. Функция

Функция ВПР
Используется для поиска и извлечения значений в таблице или диапазоне.
01
Необходимые

=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])
Что ищем?
Значение для поиска. Можно указать вручную или

Чтобы найти значение в диапазоне, необходимо:
Выделить ячейку.
Ввести =ВПР(.
Выбрать значение для поиска

Изучение сводных таблиц:
Вертикальный ПРосмотр (ВПР).
Продвинутая работа с ВПР. Функция

Интервальный просмотр
Содержит информацию о том, допускается ли неполное совпадение содержимого ячейки

Чтобы настроить интервальный просмотр, нужно:
За последним параметром функции ВПР поставить знак

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

=ЕСЛИОШИБКА(значение; значение_если_ошибка)
Проверяемый на ошибку аргумент. Можно использовать формулу, содержащую любые функции

Чтобы поменять значения #Н/Д на заданный текст, необходимо:
Активировать ячейку с формулой

Изучение сводных таблиц:
Вертикальный ПРосмотр (ВПР).
Продвинутая работа с ВПР. Функция

Как в сводной таблице посчитать количество человеко-часов на специалиста?
Как в сводной таблице создать новое поле

Вычисляемое поле
Позволяет создать в сводной таблице новое вычисляемое поле с помощью

Перед началом создания вычисляемого поля следует:
Создать сводную таблицу для дальнейших вычислений.
Сделать

Создадим поле для подсчёта выручки с учётом НДС:
Щёлкнуть по сводной таблице.
В

Изменение или удаление вычисляемого поля
Щёлкнем по сводной таблице:
1
2
3
4
Перейти на вкладку «Анализ

Изучение сводных таблиц:
Вертикальный ПРосмотр (ВПР).
Продвинутая работа с ВПР. Функция

Дополните книгу.
В основной таблице:
Скачайте файл с исходными данными.
Дополните основной лист информацией

Изучение сводных таблиц:
Вертикальный ПРосмотр (ВПР).
Продвинутая работа с ВПР. Функция