Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:

Задание_№9_Excel

.doc
Скачиваний:
29
Добавлен:
08.03.2015
Размер:
1.32 Mб
Скачать

Задание №9. Поиск, фильтрация и редактирование в списках

Задание В центральный банк г.NNN от филиалов по локально-вычислительной сети (ЛВС) поступают счета оплаты населением города коммунальных услуг. Поступающая информация фиксируется в ЭТ таблице Excel, состоящей из следующих столбцов (полей).

Характеристики списка (БД) __________ ______________ Имена полей

  1. 1- Наименование районов г.NNN; Район

  2. 2- Дата поступления счета; Дата

  3. 3- Категория коммунальных услуг (газ, свет, кв.м); Услуги

  4. 4- Стоимость (тыс. руб.); Сумма

  5. 5- Пеня за задолженность (% от стоимости); Пеня(%)

  6. 6- Пеня в рублях Пеня(руб)

  7. 7- Всего оплачено (тыс.руб.)= Сумма + Пеня(%)*Сумма/100 Всего

Сформировать в Excel таблицу поступлений счетов за коммунальные услуги от населения за 1 месяц с отражением в структуре таблицы информации 1-7 задачи.

Учесть следующее:

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

  2. - наименования районов формируются в произвольном порядке;

  3. - категории услуг формируются последовательно (газ, свет, кв.м);

  4. - сумма — случайно распределенные числа в диапазоне, соответственно:

за газ — [150;600];

за свет — [120;800];

за кв.м — [250-1000];

пеня (%) — [0-5] - случайные целые числа.

Требования:

1. Для выполнения задания потребуется 8 рабочих листов Excel.

На 1-ом листе оформляется в виде списка все сведения о поступивших счетах оплаты коммунальных услуг по районам города (Рис. 1).

На 2-ом листе сформировать таблицу, отфильтрованную по категории оплаты за газ (Рис. 3).

На 3-м листе сформировать таблицу, отфильтрованную по оплате за свет в первую десятидневку месяца с построением диаграммы (Рис. 4).

На 4-м листе сформировать сводную таблицу по заданию.

На 5-ом листе сформировать таблицу в соответствии с условием фильтрации списка по варианту самостоятельной работы.

На 6, 7, 8-м листах сформировать сводные таблицы самостоятельной работы.

  1. 2. Оглавление таблицы должно содержать: название города, месяца;

  2. 3. Указаны реквизиты банка (наименование банка, адрес, расчетный счет).

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

  1. 9.1 На первом листе заполнить заголовок и строку заголовков таблицы (рис. 1). Заполнить данными столбцы Район, Дата, Услуги (газ, свет, кв.м – чередуются).

  2. 9.2 Выполнить вычисления в полях Сумма, Пеня в соответствием с заданием.

  1. 9.3 Сохранить (зафиксировать) числовые значения Базы данных.

  2. (Выделить числовые значения таблицы (диапазон D10:E55). Действовать по схеме Главная Буфер обмена Копировать. Затем открыть выпадающий список команды Вставить, выбрать Вставить значения Значения и форматы чисел. Этими действиями в скопированном блоке ячеек будут зафиксированы только числовые значения.)

  3. 9.4 Выполнить вычисления в полях Пеня(руб), Всего по формулам:

  4. Пеня руб = Пеня* Сумма/100,

  5. Всего = Сумма+Пеня(руб).

  1. 9.5 Добавить новые записи, используя возможности Формы.

Режим Формы — это альтернативный вариант заполнения и редактирования таблиц данными в Excel. Названия полей ввода соответствуют названиям полей записи, определенным в строке заголовков.

Действия:

    1. • Добавить команду Форма на вкладку Данные.

    2. • Щелкнуть мышью на любой ячейке в строке заголовков, затем выбрать Данные ► Форма. На рабочем листе появится окно Формы для ввода и редактирования данных списка (Рис. 2).

Рис. 2. Окно формы для ввода и редактирования записей списка

Заполнять форму можно используя клавишу <Таb> для перехода между полями, затем щелкая по кнопке Добавить окна Формы. По этой команде данные из формы будут перенесены в соответствующие поля записи. Добавляемая запись размещается в конце списка.

  1. (Дополнить таблицу не менее десятью новыми записями, используя форму.)

Рис. 1. Таблица списка данных Базы данных.

  1. 9.6 После полного оформления таблицы (рис. 1.), переименовать стандартное имя листа на имя ПЛАТЕЖИ и скопировать сформированную таблицу на другие три свободных листа.

  2. 9.7 Произвести фильтрацию таблицы по полю Услуги. (На рис. 3. результат фильтра)

Рис. 3 Результат работы Автофильтра выборки оплаты за газ

9.8 Произвести фильтрацию таблицы по полю Дата и Услуги (свет). Построить диаграмму для данных столбцов Дата и Всего отфильтрованной таблицы (рис. 4.).

Рис. 4 Таблица и график данных фильтра по полям: Дата и Услуги

  1. 9.9 Отфильтровать данные с помощью Расширенного фильтра и скопировать результат в другое место рабочего листа Excel.

• Скопировать еще раз исходную таблицу данных на свободный лист.

Расширенный фильтр (рис. 6.) распознает три специальных имени диапазонов: "База данных", "Критерии", "Извлечь".

• Оформить область критериев по образцу (внести диапазон A57:G60 рис.6.). Критерий отбора:

критерий 1-й строки—Извлечь из базы данных платежи за кв.м в 1-ю половину месяца, не превосходящие сумму (столбец Всего 500);

Рис. 5. Диалоговое окно Расширенного фильтра

критерий 2-й строки — Извлечь платежи по Центральному району за Свет.

Несколько критериев одной строки связаны логической функцией — И

Критерии на разных строках связаны логической функцией — ИЛИ

• Выделить диапазон A9:G55.

• Активизировать Расширенный фильтр по схеме Данные Сортировка и Фильтр Дополнительно…

Появится окно Расширенный фильтр (рис.5).

  1. • Заполнить поля окна Расширенного фильтра. В группе переключателей Обработка следует установить переключатель Скопировать результат в другое место.

  1. • Результат отобразится в указанном диапазоне.

  2. 9.10 Сформировать Сводную таблицу, содержащую данные по районам и услугам.

Действия:

    1. • Открыть лист Платежи.

    2. • Выполнить команду ВставкаТаблицыСводная таблица.

    3. • Выполнить первые шаг работы с Мастером самостоятельно.

• На втором шаге (Рис.7.) перетащить значки с названиями столбцов таблицы, например:

    1. − Район. Схватить мышью значок и перетащить в область Строка.

    2. − Услуги – в область Столбец.

    3. − Всего - в область Данные.

    4. • Если выполнить щелчок на перенесенных значках, можно редактировать их назначение.

Рис. 2.6 Три диапазона данных расширенного фильтра

Рис. 7. Второй шаг Мастера Сводных таблиц.

• Программа предлагает создать на новом или существующем листе сводную таблицу.

• Просмотреть сформированную таблицу (Рис.8.).

Рис. 8. Сводная таблица.

9.11 Приобрести навыки преобразований Сводных таблиц.

Действия:

• Установить курсор в область сводной таблицы и выполнить команду Работа со сводными таблицами ►Параметры ► Показать ► Список полей.

• Перенести поле Район на область Столбец, а поле Услуги на область Строка.

• Просмотреть полученную таблицу(Рис.9):

Рис.9. Сводная таблица.

• Переместить поле Дата на область Строка.

• Выполнить команду Работа со сводными таблицами ►Конструктор ►Макет ► Макет отчѐта ► Показать в табличной форме, получиться следующая таблица (Рис.10.).

Рис.10. Сводная таблица

9.18 Отфильтровать сведенные данные.

• Установить курсор в область сводной таблицы и выполнить команду Работа со сводными таблицами ►Параметры ► Показать ► Список полей.

• Перенести поле Район из области Название столбцов в поле Фильтр отчѐта. В первой строке рабочей области появится надпись Район и выпадающий список с надписью Все.

• Щелкнуть по язычку Все и выбрать Центральный район.

• Просмотреть полученные результаты (Рис 11).

Рис 11. Отфильтрованная Сводная таблица

9.19 Скопировать еще раз исходную таблицу данных на свободный лист для выполнения самостоятельной работы.

Самостоятельная работа

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

2. Сформировать таблицу — результат фильтрации данных из исходной таблицы ПЛАТЕЖИ по условию варианта (1-15) и построить диаграмму по полученным результатам для столбцов:

Район и Всего (если фильтр по полю Всего, или по — Дата, или по— Услуги).

Услуги и Всего (если фильтр по полю Всего, или по — Дата, или по— Район)

Варианты фильтров для поля Всего:

1. Счета для газа во 2-ю десятидневку месяца

2. Счета для света в 3-ю десятидневку месяца

3. Счета для света в 1 -ю десятидневку месяца

4. Счета для кв.м в 1-ю неделю месяца

5. Счета для кв.м в 3-ю неделю месяца

6. Счета в диапазоне 15—50 тыс.руб.

7. Счета в диапазоне 50-100 тыс.руб.

8. Счета в диапазоне 100-200 тыс.руб.

9. Счета в диапазоне 250-350 тыс.руб.

10.Счета в диапазоне 350-500 тыс.руб.

11.Счета Центрального района за свет

12.Счета Центрального района за газ

13.Счета Центрального района за кв.м

14.Счета Ингодинского района в 1-ю половину месяца

15.Счета Железнодорожного района во 2-ю половину месяца

3. Сформировать Сводную таблицу по таблице ПЛАТЕЖИ.

4. По результатам Сводной таблице построить диаграмму.

5. Создать не менее 3 видов макетов сводных таблиц.

6. Придумать и применить не менее 3 видов фильтрации