АкушерствоАнатомияАнестезиологияВакцинопрофилактикаВалеологияВетеринарияГигиенаЗаболеванияИммунологияКардиологияНеврологияНефрологияОнкологияОториноларингологияОфтальмологияПаразитологияПедиатрияПервая помощьПсихиатрияПульмонологияРеанимацияРевматологияСтоматологияТерапияТоксикологияТравматологияУрологияФармакологияФармацевтикаФизиотерапияФтизиатрияХирургияЭндокринологияЭпидемиология

Порядок выполнения задания

1) Скопируйте файл ex4_2.xlsx в свою рабочую папку. Сохраните его под именем «Упражнение 4 Excel. Задание 2».

2) Для формирования сводной таблицы по трем листам будем использовать «мастер сводных таблиц и диаграмм». Сначала его нужно подключить и поместить на панель быстрого запуска: Настройка панели быстрого запуска -> Другие команды -> Выбрать команды из: -> Все команды -> Мастер сводных таблиц -> Добавить -> OK.

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

Запустите мастер сводных таблиц. Выберите создание сводной таблицы в нескольких диапазонах консолидации (так как исходные таблицы находятся на разных листах):

Теперь укажите все таблицы, которые нужно консолидировать. Начните с первого листа «июнь». Выделите все заполненные ячейки на этом листе (A1–E7) и нажмите «Добавить»:

Теперь перейдите к следующему листу «июль», выберете тот же диапазон ячеей и добавьте диапазон в список. Затем перейдите к листу «август» и к листу «сентябрь»:

Важно: диапазоны будут отсортированы в алфавитном порядке: «август», «июль», «июнь», «сентябрь»:

Нажмите «Далее» и поместите сводную таблицу на новый лист:

У Вас должна получиться следующая таблица (текст в ячейках C4 и E4 можно подредактировать):

При нажатии на область сводной таблицы появится список полей сводной таблицы:

3) В параметрах сводной таблицы (панель «Параметры» из меню «Работы со сводными таблицами» -> вкладка «Сводная таблица» -> Параметры выберите «Классический макет сводной таблицы».

В классическом макете при нажатии мышью на область таблицы поля таблицы выделяются (и их можно перетаскивать непосредственно в самой таблице):

4) Для работы с полями сводной таблицы будет удобнее их переименовать. Из сводной таблицы видно, что названиям строк соответствуют названия туров (направлений), названиям столбцов – названия туристических компаний, поле «страница 1» соответствует названиям месяцев, а в поле «значение» подсчитывается количество туристов. Поэтому переименуем поля следующим образом: «Строка» -> «Тур», «Столбец» -> «Компания», «Страница1» -> «Месяц», «Значение» -> «Количество туристов».

Для переименования каждого поля выберите из меню «Работы со сводными таблицами» панель «Параметры» -> вкладка «Активное поле»

Задайте новое имя активного поля: «Месяц» и нажмите Enter. Обратите внимание, что при этом автоматически изменится значение ячейки A1:

Аналогично переименуйте оставшиеся три поля.

Проверьте все выпадающие списки (ячейки B1, A4, B3). Вы увидите, что в списке ячейки B1 не отображаются названия месяцев. Поскольку этот выпадающий список является списком страниц (листов), из которых были взяты таблицы для консолидации, то эти страницы по умолчанию называются: «Объект 1», «Объект 2» и т.д. Для исправления ситуации переименуйте каждый объект в соответствующий месяц. При этом учтите, что диапазоны консолидации были упорядочены по алфавиту, поэтому «Объекту 1» соответствует лист «Август», «Объекту 2» – лист лист «Июль», «Объекту 3» – лист «Июнь», «Объекту 4» – лист «Сентябрь». Выберите первый объект и введите в строке редактирования название месяца: «Август»:

Аналогично переименуйте остальные объекты.

Снова выделите все месяцы:

5) Посмотрите еще раз на список полей сводной таблицы. В фильтре отчета находится поле «Месяц», названиям строк соответствуют значения поля «Компания», названиям столбцов – значения поля «Тур», в области «Значения» вычисляется количество туристов.

Получившаяся сводная таблица соответствует пункту задания 2-а). Введите в ячейку А13 текст: «Количество туристов, выбравших определенный тур в каждой компании». Назовите лист со сводной таблицей: «Лист 2-а)». У Вас должна получиться таблица, изображенная на рис. 8.

6) Перейдем к пункту 2-б) – количество туристов, обслуженных каждой компанией в каждом месяце. Чтобы не создавать сводную таблицу заново, изменим макет имеющейся сводной таблицы. Для этого скопируйте «Лист 2-а)» и назовите его «Лист 2-б)».

Изменим расположение полей сводной таблицы. Поместите поле «Тур» в фильтр отчета, поле «Месяц» – в названия столбцов, а поле «Компания» – в названия строк. Поля можно перетаскивать мышью между областями списка полей сводной таблицы. Также поля можно перетаскивать с помощью перетаскивания ячеек в таблице. Должно получиться:

Можно задать естественный порядок следования месяцев. Для этого выделите ячейку B4 со значениями поля «Месяц». В выпадающем списке выберите «Дополнительные параметры сортировки»:

В дополнительных параметрах сортировки отмените автоматическую сортировку и добавьте сортировку по первому ключу:

Теперь в выпадающем списке выберите сортировку от А до Я:

Введите в ячейку А12 текст: «Количество туристов, обслуженных каждой компанией в каждом месяце». В итоге у Вас должна получиться сводная таблица, изображенная на рис. 9.

7) Для ответа на вопрос пункта 2-в) – количество туристов, выбравших определенный тур в каждом месяце – достаточно слегка изменить макет последней сводной таблицы. Скопируйте «Лист 2-б)» и назовите его «Лист 2-в)». Поменяйте местами поля «Тур» и «Компания».

Введите в ячейку А14 текст: «Количество туристов, выбравших определенный тур в каждом месяце». В итоге у Вас должна получиться сводная таблица, изображенная на рис. 10.

8) Ответить на вопрос следующего пункта 2-г) – количество туристов, отдыхавших в России и за рубежом в каждом месяце – можно, применив к последней таблице группировку. Скопируйте «Лист 2-в)» и назовите его «Лист 2-г)».

Выделите мышью все международные направления, щелкните по правой кнопке мыши и нажмите «Группировать»:

Аналогично сгруппируйте все российские направления:

Переименуйте группу 1 в «Зарубежные страны», а группу 2 назовите «Россия».

Обратите внимание, что автоматически появилось новое поле «Тур 2». Переименуйте его в «Направления». Данные по конкретным турам в ответе на данный вопрос не нужны, поэтому поле «Тур» можно не включать в сводную таблицу:

Введите в ячейку А10 текст: «Количество туристов, отдыхавших в России и за рубежом в каждом месяце». В итоге у Вас должна получиться сводная таблица, изображенная на рис. 11.

Обратите внимание, что поле «Тур2» появилось и на предыдущих листах. Не включайте его в предыдущие сводные таблицы!

9) Ответить на последний вопрос (пункт 2-д) – общее количество туристов, обслуженных в каждом месяце и в целом за лето – можно также на основе последней сводной таблицы. Скопируйте «Лист 2-г)» и назовите его «Лист 2-д)». Для ответа на этот вопрос не нужны данные по направлениям, поэтому поле «Направления» следует убрать из сводной таблицы:

Выделите заполненные ячейки в диапазоне А2–F6.

Скопируйте выделенную область в ячейку А10. В скопированной таблице поставьте фильтр только на летние месяцы.

Вторая сводная таблица показывает количество туристов, обслуженных за все лето.

Введите в ячейку А8 текст: «Общее количество туристов, обслуженных в каждом месяце», а в ячейку А16 – «Общее количество туристов, обслуженных в целом за лето». Проверьте свой результат по образцу (рис. 12).

Дата добавления: 2015-09-27 | Просмотры: 377 | Нарушение авторских прав