Методические указания Excel
.pdf
Если изменения коснулись исходных данных, то для ихизменения в сводной диаграмме, также необходимо использовать команду меню
Обновить данные (рис. 25).
После окончания корректировок для диаграммы следует поставить галочку для команды Скрыть кнопки полей сводной диаграммы
(рис. 25).
Рис. 25. Корректировка Сводной диаграммы
4.МЕТОДИКА ВЫПОЛНЕНИЯ ИНДИВИДУАЛЬНОГО ЗАДАНИЯ
Перед выполнением заданий оформите книгу:
-Добавьте в книгу недостающие листы.
-Переименуйте листы книги как: Исходные данные,
Сортировка, Расширенный фильтр, Итоги, Сводные таблицы.
Шаг 1 – Формирование списка. Заполните таблицу Справка и
создайте Список на листе Исходные данные согласно вашему варианту. Пустые столбцы заполните необходимыми формулами, используя функции: ВПР (Вертикальный ПРосмотр) и ГПР (Горизонтальный ПРосмотр).
Шаг 2 – Сортировка. Перед выполнением задания скопируйте только список вашего варианта с листа Исходные данные и вставьте его на лист Сортировка. Исправьте формулу, добавив перед диапазоном ячеек, который содержит данные Справки, название листа, где этот диапазон находится (рис. 26). Скопируйте исправленную формулу на весь диапазон данных.
=ВПР(E3; 'Исходные данные'! $B$4:$C$9;2;ЛОЖЬ)*F3*G3
Рис. 26. Корректировка формулы
Отсортируйте список по параметрам вашего варианта. Перед выполнением сортировки не забудьте поместить указатель в любое место области данных, только после этого вызвать команду меню Данные Сортировка.
Шаг 3 – Фильтрация данных. Перед выполнением задания скопируйте только список с листа Исходные данные и вставьте его на лист Расширенный фильтр. Исправьте формулу.
Задайте условие выборки согласно вашему варианту. Отфильтруйте данные, используя расширенный фильтр, используя команду меню Данные
Фильтр Расширенный фильтр.
Шаг 4 – Подведение итогов. Перед выполнением задания скопируйте только список с листа Исходные данные и вставьте его на лист Итоги. Исправьте формулу.
Отсортируйте список по параметру, заданному в вашем варианте. Подведите итоги, используя команду меню Данные Итоги. В диалоговом окне укажите параметры вашего варианта.
Шаг 5 – Создание сводной таблицы. Перед выполнением задания скопируйте только список с листа Исходные данные и вставьте его на лист Сводные таблицы. Исправьте формулу.
Создайте сводную таблицу, используя Мастер сводных таблиц, который вызывается командой меню Данные Сводные таблицы. На третьем шаге мастера оформите Макет согласно вашему варианту. На этом же листе постройте Сводную диаграмму.
5.ИНДИВИДУАЛЬНЫЕ ЗАДАНИЯ
Cоздать и обработать список, соответствующий приведенному ниже рисунку, согласно варианту инивидуального задания:
-Создать список согласно варианту и рисунку. Пустые столбцы заполнить с помощью формул.
-Отсортировать список по заданным параметрам.
-Отфильтровать список с помощью расширенного фильтра по заданным параметрам.
-Подвести промежуточные итоги по заданным параметрам.
-Создать сводную таблицу и сводную диаграмму по заданным параметрам.
|
№ |
|
|
№ |
|
|
|
|
|
|
|
|
|
|
|
|
вар |
|
|
|
|
Параметры |
|
|
|
|
|
|
Параметры сводной таблицы и |
|
|
|
|
|
рису |
|
|
|
|
Условия фильтра |
|
Параметры итогов |
|
|
|||
|
иан |
|
|
нка |
|
|
сортировки |
|
|
|
|
|
|
сводной диаграммы |
|
|
та |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
по стране |
|
по производителю |
|
общая стоимость |
|
просмотр и подсчет количества |
||
1 |
|
1 |
|
|
производителя и |
|
(Польша) и типу краски |
|
краски по странам |
|
краски в литрах по типу краски и |
||||
|
|
|
|
|
|
|
году выпуска |
|
(масляная) |
|
производителей |
|
стране производителя |
||
|
|
|
|
|
|
|
по типу краски и |
|
по типу краски |
|
общая стоимость |
|
просмотр и подсчет количества |
||
2 |
|
1 |
|
|
|
(акриловая) и стоимости |
|
|
краски в упаковках по типу |
||||||
|
|
|
|
|
|
|
году выпуска |
|
1 л (< 30). |
|
краски по ее типу |
|
краски и году выпуска |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
по типу автомобиля |
|
общая стоимость |
|
просмотр и подсчет стоимости |
|
3 |
|
2 |
|
|
по типу автомобиля |
|
расхода топлива на |
|
|||||||
|
|
|
и виду топлива |
|
(легковой) и виду |
|
пробег по типу |
расхода топлива по виду топлива |
|||||||
|
|
|
|
|
|
|
|
топлива (АИ-92). |
|
|
и типу автомобиля |
||||
|
|
|
|
|
|
|
|
|
|
|
автомобиля. |
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
|
|
|
|
|
|
по виду топлива и |
|
по виду топлива (АИ-98) |
|
общий пробег по |
|
просмотр и подсчет общего |
||
4 |
|
2 |
|
расходу топлива |
|
и расходу топлива л/100 |
|
|
пробега по виду топлива и типу |
||||||
|
|
|
|
|
|
|
л/100 км |
|
км (< 10 |
|
виду топливо |
|
автомобиля |
||
|
|
|
|
|
|
|
|
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
32 |
|
|
|
|
|
|
|
№ |
|
|
№ |
|
|
|
|
|
|
|
|
|
|
|
|
вар |
|
|
|
|
Параметры |
|
|
|
|
|
|
Параметры сводной таблицы и |
|
|
|
|
|
рису |
|
|
|
|
Условия фильтра |
|
Параметры итогов |
|
|
|||
|
иан |
|
|
нка |
|
|
сортировки |
|
|
|
|
|
|
сводной диаграммы |
|
|
та |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
по марке телефона |
|
|
|
просмотр и подсчет общей |
|
|
|
|
|
|
|
|
по марке телефона и |
|
(Nokia) и цене со |
|
общая стоимость |
|
|||
5 |
|
3 |
|
|
|
|
|
стоимости проданных телефонов |
|||||||
|
|
|
цене без скидки |
|
скидкой |
|
по марке телефона |
|
|||||||
|
|
|
|
|
|
|
|
|
|
по марке и типу |
|||||
|
|
|
|
|
|
|
|
|
(< 5000). |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
по типу телефона |
|
общее количество |
|
просмотр и подсчет общего |
|
6 |
|
3 |
|
|
по типу телефона и |
|
(Смартфон) и цене без |
|
проданных |
|
количества проданных телефонов |
||||
|
|
|
|
|
|
|
цене со скидкой |
|
скидки (< 10000). |
|
телефонов по типу |
|
по марке и типу |
||
|
|
|
|
|
|
|
|
|
|
|
|
телефона |
|
|
|
|
|
|
|
|
|
|
|
|
|
по производителю |
|
общее количество |
|
просмотр и подсчет общего |
|
7 |
|
4 |
|
|
по производителю и |
|
(Samsung) и объему |
|
проданных |
|
количества проданных мониторов |
||||
|
|
|
объему видеопамяти |
|
видеопамяти |
|
мониторов по |
|
по производителю и диагонали |
||||||
|
|
|
|
|
|
|
|
|
|
||||||
|
|
|
|
|
|
|
|
|
|
(< 10 ГБ). |
|
производителю. |
|
экрана |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
общая стоимость |
|
просмотр и подсчет общей |
|
8 |
|
4 |
|
|
по производителю и |
|
по диагонали экрана (< |
|
проданных |
|
стоимости проданных мониторов |
||||
|
|
|
диагонали экрана |
|
15) и цене (< 18000). |
|
мониторов по |
|
по производителю и объему |
||||||
|
|
|
|
|
|
|
|
|
|
||||||
|
|
|
|
|
|
|
|
|
|
|
|
производителю |
|
видеопамяти |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
по производителю и |
|
Принадлежности (Ж) и |
|
общее количество |
|
просмотр и подсчет общего |
||
9 |
|
5 |
|
|
|
|
велосипедов по |
|
количества велосипедов по фирме |
||||||
|
|
|
типу велосипеда |
|
весу (< 12). |
|
|
||||||||
|
|
|
|
|
|
|
|
|
принадлежности |
|
и принадлежности |
||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
10 |
|
5 |
|
|
по принадлежности |
|
Тип (Шоссейный) и |
|
общее количество |
|
подсчет общего количества |
||||
|
|
|
и типу велосипеда |
|
Фирма (Kross). |
|
велосипедов по |
|
велосипедов по фирме и типу |
||||||
|
|
|
|
|
|
|
|
|
типу |
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
33 |
|
|
|
|
|
|
|
№ |
|
|
№ |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
вар |
|
|
|
|
Параметры |
|
|
|
|
|
|
|
Параметры сводной таблицы и |
|
|||||
|
|
|
рису |
|
|
|
|
Условия фильтра |
|
|
Параметры итогов |
|
|
|||||||
|
иан |
|
|
нка |
|
|
сортировки |
|
|
|
|
|
|
|
сводной диаграммы |
|
|
|||
|
та |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
по фирме и |
|
Тип печати (цветная) и |
|
общее количество |
|
просмотр |
и |
подсчет |
общего |
||||
11 |
|
6 |
|
|
|
|
принтеров по типу |
|
количества принтеров по фирме и |
|||||||||||
|
|
|
технологии печати |
|
скорость печати (> 20). |
|
|
|||||||||||||
|
|
|
|
|
|
|
|
|
печати |
|
типу печати |
|
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
по технологии и |
|
Технология печати |
|
общее количество |
|
просмотр |
и |
подсчет |
общего |
||||
12 |
|
6 |
|
|
|
(струйная) и скорость |
|
принтеров по |
|
количества принтеров по фирме и |
||||||||||
|
|
|
скорости печати |
|
|
|
||||||||||||||
|
|
|
|
|
|
|
|
печати (> 25) |
|
технологии печати |
|
технологии печати |
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Фирма (Samsung или HP) |
|
общая стоимость |
|
просмотр |
и |
подсчет |
|
общей |
||
13 |
|
6 |
|
|
по фирме и |
|
|
проданных |
|
|
||||||||||
|
|
|
технологии печати |
|
и технология печати |
|
принтеров каждой |
|
стоимости |
проданных принтеров |
||||||||||
|
|
|
|
|
|
|
|
(лазерная). |
|
|
по фирме и типу печати |
|
|
|
||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
фирмы |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
общее количество |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
по отделению банка |
|
Тип вклада (депозит) и |
|
начисленных |
|
просмотр и подсчет общей суммы |
|||||||
14 |
|
7 |
|
|
размер вклада (> 300 |
|
процентов по |
|
на счетах по каждому отделению |
|||||||||||
|
|
|
|
|
|
|
и типу вклада |
000). |
|
|
каждому |
|
банка. |
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
|
|
|
отделению банка |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
по отделению банка |
|
Отделение банка |
|
общая сумма на |
|
просмотр |
и |
подсчет количества |
|||||
15 |
|
7 |
|
|
|
(Северное) и размер |
|
|
||||||||||||
|
|
|
и начисленным |
|
вклада |
|
счете по каждому |
|
начисленных |
процентов |
по |
|||||||||
|
|
|
|
|
|
|
процентам. |
|
|
типу вклада |
|
каждому отделению банка |
|
|
|
|||||
|
|
|
|
|
|
|
(> 300 000). |
|
|
|
|
|
|
|||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
34
Рисунок 1
Рисунок 2
35
Рисунок 3
Рисунок 4
36
Рисунок 5
Рисунок 2
37
Рисунок 3
38