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

Задание 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).

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

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

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

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

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

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

5.3.6. Задания для самостоятельной работы

«Обработка данных в табличном процессоре MS Excel 2010»

1. Создайте таблицу-список согласно своему варианту задания. Варианты таблиц приведены в табл. 2 (прил.).

2. Выполните сортировку таблицы. Параметры сортировки указаны в табл. 2 прил.

3. Постройте диаграмму. В качестве диапазона возьмите значения второго и последнего столбца таблицы.

4. Для данных последнего столбца таблицы вычислите: итоговое значение суммы, среднее значение, максимальное и минимальное значения.

5. С помощью фильтров составьте отдельные списки. В качестве критериев отбора выберите следующие:

• Значения второго столбца таблицы, равное Значение 1;

• Значения четвертого столбца таблицы, равное Значение 2;

• Значения последнего столбца таблицы, отвечающие условию >=Значение 3 и <= Значение 4;

Данные для фильтрации (Значение 1, Значение 2, Значение 3, Значение 4) выбрать самостоятельно в соответствии с содержанием таблицы. Полученные списки разместите на отдельных рабочих листах.

Лабораторная работа № 4

Практическая работа в СУБД MICROSOFT ACCESS 2010

Цель работы: приобретение навыков работы с базами данных в СУБД Access 2010.

Задание

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

Создание базовых таблиц

1. Запустите Microsoft Office Access: Пуск→Все программы→Microsoft Office→ Microsoft Office Access 2010. Появится представление Backstage.

2. В группе Доступные шаблоны щелкните элемент Пустая база данных. Справа в разделе Пустая база данных в поле Имя файла введите имя файла «Книготорговля». Нажмите кнопку Создать. Будет создана новая база данных и открыта новая таблица.

3. Создайте структуру таблицы в режиме конструктора. Для этого выберите команды Главная→Режим→Конструктор. В открывшемся диалоговом окне Сохранение введите имя таблицы: Книги в продаже. Откро­ется бланк создания структуры таблицы в режиме Конструктора.

4. Таблица должна содержать следующие поля:

Имя поля	Тип данных
Наименование	Текстовый
Автор	Текстовый
Объем	Числовой
Цена	Денежный
Примечание	MEMO

5. Для создания первого столбца в строке Имя поля введите: Наименование, для перехода к столбцу Тип данных нажмите клавишу Tab. C помощью кнопки найдите в раскрывающемся списке тип данных: Текстовый.

6. В окне Свойства поля введите следующие данные:

Размер поля: 50;

Обязательное поле: Да;

Пустые строки: Нет;

Индексированное поле: Нет.

7. Задайте поле Наименование ключевым. Для этого выделите поле Наименование ищелкните на кнопке Ключевое поле. Это необходимо для связи с будущей таблицей поставщиков.

8. Перейдите к описанию следующего поля Автор, щелкнув мышью вторую строку в столбце Имя поля, повторяя действия, описанные выше.

9. Для поля Объем задайте свойство Число деся­тичных знаков: 0, а для поля Цена: Размер поля: Одинарное с плавающей точкой; Число деся­тичных знаков: 2.

10. Нажмите кнопку Сохранить и закройте окно Конструктора.

11. Для создания второй таблицы выполните команды Создание→Конструктор таблиц. Повторив действия пунктов 5-10, создайте структуру таблицы Поставщики, в которую входят следующие поля:

Имя поля Тип данных

Наименование Текстовый

Автор Текстовый

Цена оптовая Денежный

Поставщик Текстовый

Телефон Текстовый

Адрес Текстовый

Примечание MEMO

Ключевое поле— Наименование.

12. В окне Книготорговля: база данных откройте по очереди созданные структуры таблиц и наполните их экспериментальным содержанием (8-10 записей). Для этого переходите с помощью клавиши Tab от одного столбца к другому, и вводите произвольные данные. Закончив работу, сохраните и закройте таблицы.