Работа с данными списка (сортировка, фильтрация)

Задание 3

Создайте таблицу «Список сотрудников» (рис. 34). Выполните сортировку и отбор записей по заданным критериям.

1.

Рис.34. Таблица «Список сотрудников»

Введите заголовок «Список сотрудников фирмы» и создайте шапку таблицы.

2. Введите в таблицу 10 произвольных записей. Установите заголовок каждого столбца по центру ячейки, используя кнопку Выравнивание по центру на панели инструментов. Оптимальную ширину столбца задайте параметром Формат® Автоподбор ширины столбца.

3. Отсортируйте данную таблицу-список по трем уровням одновременно: Оклад – по убыванию; Фамилия – по возрастанию; Имя – по возрастанию.

Параметры сортировки (рис. 35) устанавливаются в диалоговом окне Сортировка, которое открывается при активизации команд Главная ®Редактирование ®Сортировка® Настраиваемая сортировка. Для добавления к сортировке следующего столбца нажмите кнопку Добавить уровень.

4.

Рис. 35. Окно «Сортировка» Рис. 38 Окно «Сортировка»   Рис. 38 Окно «Сортировка» Рис. 38 Окно «Сортировка»

Установите указатель ячейки внутри области списка. Активизируйте команды Данные→Фильтр. В каждом поле появятся метки автофильтров. С помощью фильтров составьте отдельные списки:

• Сотрудников с заданной Должностью;

• Сотрудников с датой рождения >=Дата 1 и <=Дата 2;

• Сотрудников с окладом > Значение 1 и < Значение 2.

Данные для фильтрации (Должность, Дата 1, Дата 2, Значение 1, Значение 2 выбрать самостоятельно в соответствии с содержанием таблицы).

5. Для составления первого списка щелкните на кнопке фильтра в поле Должность. Из представленного списка выберите элемент, равный заданной Должности. Появившуюся таблицу скопируйте на новый рабочий лист.

6. Отмените предыдущий критерий фильтра, установив в поле списка Должность элемент Все.

7. Для создания второго списка щелкните на кнопке фильтра в поле Дата рождения, выделите элемент Фильтры по дате →После.

8. В диалоговом окне Пользовательский автофильтр задайте данное условие:

После Дата 1 и До Дата 2.

Полученный список также скопируйте на Лист2.

9. Не отменяя результатов фильтрации, аналогичным образом составьте третий список. Щелкните на кнопке фильтра в поле Оклад. Выберите из списка элемент Числовые фильтры→Больше и задайте условие поиска: >=Значение 1 и <=Значение 2.

10. Проанализируйте полученный результат. Критерий поиска по окладу был применен к подмножеству списка, полученного в результате применения предыдущего критерия, из списка сотрудников с датой рождения >Дата 1 и < Дата 2 были выбраны сотрудники с окладом >Значение 1 и <Значение 2.

11. Для снятия фильтра повторно щелкните мышью на параметре Данные→Фильтр. Сохраните результаты в рабочей книге с именем Задание_ 3.

Вычисление итогов

Задание 4

Рис. 36. Таблица Автосалон «Колесо»

Создайте таблицу-список Автосалон«КОЛЕСО» (рис. 36). Выполните анализ значений, содержащих информацию о сбыте: оборот для каждого продавца за указанный период времени. Используйте функцию автоматического вычисления итогов.

1. Функция Промежуточные итоги вычисляет Итого только для заданной группы данных, поэтому предварительно выполните сортировку данных в таблице. Активизируйте команды Данные→Сортировка. В диалоговом окне Сортировка установите критерии сортировки:

поле Продавец −по возрастанию; Год выпуска ─ по возрастанию.

2. Установите указатель ячейки внутри списка. Активизируйте команды Данные→Структура→Промежуточные Итоги. В диалоговом окне Промежуточные итоги установите параметры (рис.37):

• При каждом изменении в: Продавец

• Операция: Сумма

• Добавить итоги по: Оборот

ü Заменить текущие итоги

ü Итоги под данными.

3.

Рис. 37. Окно Итоги

Завершите ввод нажатием кнопки [OK]. В результате в таблице появятся дополнительные строки, в которых отобразятся итоги для каждого продавца отдельно. В последней строке содержится информация об общем итоге для всех продавцов.

4. Дополните таблицу еще одним показателем: количеством автомобилей, проданных конкретным продавцом. Активизируйте команду Промежуточные итоги. Установите параметры:

• При каждом изменении в: Продавец

• Операция: Количество значений

• Добавить итоги по: Марка.

Для того чтобы итоги по обоим критериям были представлены в таблице, отмените опцию Заменить текущие итоги. Завершите ввод параметров нажатием кнопки [OK].

5. Сохраните документ в рабочей книге с именем Задание_4.

Консолидация данных

Задание 5

Составьте таблицу итогов об обороте различных филиалов автосалона «Колесо».

1. На каждом отдельном листе составьте таблицы с данными об объемах продаж в автосалоне «Колесо» в Липецке и его филиалах в Тамбове и Воронеже. Таблицы должны содержать поля: Марка автомобиля, Количество проданных автомобилей. Заполните таблицы произвольными записями. Количество записей для каждого филиала должно быть равным и не менее 10. Для добавления листов щелкните правой кнопкой мыши по ярлычкам листов и в контекстном меню выберите команды Вставить→Лист.

2. Каждому добавленному листу присвойте соответствующее имя – Липецк, Тамбов, Воронеж, Консолидация. Для этого установите указатель мыши на ярлычке листа и щелкните правой кнопкой мыши. В появившемся контекстном меню выберите команду Переименовать. Введите новое имя листа и нажмите клавишу [Enter].

3. Для представления консолидированных данных используйте лист Консолидация. Установите указатель в ячейку, начиная с которой будет вставлен диапазон ячеек с итогами (достаточно указать левый верхний угол).

4.

Рис. 38. Окно Итоги

Активизируйте команду Данные→Консолидация. В открывшемся диалоговом окне (рис. 38) следует указать консолидируемые диапазоны ячеек и вид операции (функцию).

5. В поле Ссылка диапазон с данными, подлежащими консолидации, можно ввести вручную. Но удобнее представить адрес в поле Ссылка с помощью выделения диапазона. После выделения диапазона ячеек Липецк!$A$1:$B$12 и щелчка на кнопке Добавить ссылка на указанный диапазон буде представлена в поле Список диапазонов. В консолидируемый диапазон ячеек следует включить и соответствующие заголовки (метки) строк. Выполните аналогичные действия для двух других консолидируемых областей.

6. Установите метки:

ü Использовать в качестве имен:Значения левого столбца.

ü Создавать связи с исходными данными.

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

При изменении данных в исходном диапазоне ячеек автоматически будут изменяться и консолидированные данные.

7. Нажмите кнопку [OK]. Активизируйте рабочий лист, в котором должны быть представлены результаты консолидации. Полученная таблица состоит из двух столбцов: Список автомобилей и Количество.

Отсортируйте данные по убыванию значений столбца Количество. Это позволит получить представление о том, какие марки пользуются наибольшим спросом.

8. Создайте сводную таблицу Оборот автосалона «Колесо»(рис. 36).

Для этого выполните действия:

• выделите любую ячейку исходной таблицы;

• выполните команду Вставка → Сводная таблица;

• выполните все шаги Мастера сводных таблиц;

• переместите в указанные ячейки соответствующие поля (Продавец, Дата, Оборот) из Списка полей.

• Сохраните сводную таблицу на новом рабочем листе.