Лабораторная работа №3 по EXCEL

(план работы)

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

- Группировка в сводной таблице

- Работа со списками;

- Анализ данных с использованием сценариев;

- Подбор параметров;

I. В сводной таблице можно группировать даты, времена, числа и выделенные элементы. Например, можно объединить месяцы, чтобы подвести итоги за квартал. Если временно изменить ориентацию поля страницы на ориентацию по строке или по столбцу, можно сгруппировать элементы этого поля и затем снова изменить ориентацию на ориентацию по странице. При выполнении операции группировки и разгруппировки данных сводной таблицы автоматически пересчитываются все сводные таблицы, созданные на основе текущей. Если элементы поля сгруппированы, добавление вычисляемых элементов в поле невозможно.

Задание 1. Составьте список продаж трех товаров в течение месяца с указанием наименования товара, даты продажи и суммы. Учтите, что каждый товар продается не менее двух раз в неделю. Выведите на экран распределение итоговых сумм продаж каждого товара по неделям месяца.

Технология:

- Создайте на отдельном листе таблицу, содержащую столбцы с заголовками: Товар, Дата продажи, Сумма. Заполните таблицу данными в соответствии с заданием.

- Создайте сводную таблицу продаж товаров по дням, используя команду ДАННЫЕ/Сводная таблица. Расположите в области Строка поле Товар, в области Столбец - поле Дата продажи, в области Данные - поле Сумма.

- После создания сводной таблицы включите инструментальную панель Сводные таблицы. Выделите в таблице даты продаж. Для этого щелкните по кнопке поля Дата продажи в заголовке сводной таблицы.

-

.

- Настройте параметры в диалоговом окне Группирование. Введите начальную и конечную даты месяца. Укажите шаг Дни. Для этого щелкните по элементу списка Дни, выделив его, и отмените выделение элемента Месяц, также щелкнув по нему.

- В поле Количество дней установите значение 7. Щелкните по кнопке OK

- Просмотрите сгруппированную cводную таблицу. Разгруппируйте, щелкнув по соответствующей кнопке инструментальной панели, и повторно сгруппируйте.

II. Списком называется таблица, содержащая однородные данные по строкам и столбцам. Списки можно сортировать, осуществлять в них поиск данных по различным критериям, обрабатывать с помощью фильтров и форм данных. Найденные в списке данные могут в дальнейшем подвергаться дополнительной обработке: удалению, корректировке, включению в вычисления, построению на их основе диаграмм.

1. Использование формы данных. С помощью форм можно просматривать и вводить данные в список, искать строки по содержимому ячеек, а также удалять строки из списка.

Задание 2. Использовать форму данных для просмотра и корректировки записей таблицы “Справочник работников”. Форма — это наиболее удобный способ для просмотра, изменения, добавления и удаления записей списка, а также для поиска записей, удовлетворяющих заданным условиям. Перед работой с формой необходимо задать заголовки столбцов списка. Эти заголовки используются для создания полей формы.

Технология:

- выделить область рабочего листа с таблицей “Справочник работников”, начиная со строки, содержащей названия столбцов;

- выполнить команду ДАННЫЕ/Форма и вывести данные одной строки в форму;

- переместиться вперед и назад по таблице, используя кнопки “ Далее ” и “ Назад ”;

- удалить последнюю строку таблицы нажатием кнопки “ Удалить ”;

- добавить новую строку, используя кнопку “ Добавить ”;

- закрыть диалог нажатием кнопки “ Закрыть ”;

- используя кнопку «Критерии», найти данные работника по его фамилии, вывести в форму сведения только по членам профсоюза, просмотреть сведения о работниках, имеющих только 1 льготу.

2. Использование автофильтра. В Microsoft Excel предусмотрены различные методы анализа данных в списке. К списку можно применить фильтр, чтобы отобрать только записи, соответствующие определенным условиям. Для этого служит команда ДАННЫЕ/Автофильтр.

Задание 3. Использовать автофильтр для вывода в таблице “Справочник работников” информации только о тех работниках, оклад которых меньше заданного значения.

Технология:

- отметить область рабочего листа с данными и с заголовками;

- выполнить команду ДАННЫЕ/Фильтр /Автофильтр;

- раскрыть список на поле “ Оклад ”, выбрать пункт “ Условие ” и ввести выражение “ меньше < значение > ”;

- щелкнуть кнопку “Ok”.

- отменить автофильтр, для этого выполнить команду ДАННЫЕ/Фильтр и выбрать из списка пункт «все» для поля “ Оклад ”.

Задание 4. Использовать автофильтр для вывода в таблице “Справочник работников” информации о работниках:

- с фамилиями, начинающимися на заданную букву,

- 1-ого и 3-его отделов.

3. Использование расширенного фильтра. Расширенный фильтр позволяет более гибко настроить фильтр для просмотра сведений из списка данных. Просмотр осуществляется на основании условий отбора. В условия отбора расширенного фильтра может входить несколько условий, накладываемых на один столбец, несколько условий, накладываемых одновременно на несколько столбцов, а также условия, накладываемые на возвращаемое формулой значение. Порядок составления условий отбора смотрите в разделе помощи Excel «Примеры условий отбора расширенного фильтра».

Задание 5. Использовать расширенный фильтр для получения данных о работниках 1-го отдела с окладом меньшим 5000000 руб. и не являющих­ся членами профсоюза.

Технология:

- скопировать имена столбцов “ Отдел ”, “ Оклад ” и “ Член профсоюза ” в ту часть рабочего листа, которая не содержит данных для поиска, например в ячейки H1, I1, J1;

- в ячейки H2, I2, J2 ввести соответственно критерии поиска (1, < 50000, 0);

- выполнить команду меню ДАННЫЕ/Фильтр / Расширенный фильтр;

- в диалоге “ Расширенный фильтр ” задать область, где находятся исходные данные и область, в которой заданы критерии поиска (диапазон условий);

- в группе “ Обработка ” указать, что фильтрация будет выполняться на месте;

- щелкнуть кнопку “ Ok ”.

- восстановить список, выполнив команду ДАННЫЕ/Фильтр / Восстановить все.

Задание 6. Использовать расширенный фильтр для получения данных о работниках 1-го и 3-его отделов, с окладом меньшим 500000 руб. и не являющих­ся членами профсоюза.