Лабораторная№9 по Excel 2010
.pdfЗадание №9. Поиск, фильтрация и редактирование в списках
Задание В центральный банк г.NNN от филиалов по локально-вычислительной сети (ЛВС)
поступают счета оплаты населением города коммунальных услуг. Поступающая информация фиксируется в ЭТ таблице Excel, состоящей из следующих столбцов (полей).
Характеристики списка (БД) ________________________ Имена полей
1-Наименование районов г.NNN; |
Район |
2-Дата поступления счета; |
Дата |
3-Категория коммунальных услуг (газ, свет, кв.м); |
Услуги |
4-Стоимость (тыс. руб.); |
Сумма |
5-Пеня за задолженность (% от стоимости); |
Пеня(%) |
6-Пеня в рублях |
Пеня(руб) |
7-Всего оплачено (тыс.руб.)= Сумма + Пеня(%)*Сумма/100 |
Всего |
Сформировать в Excel таблицу поступлений счетов за коммунальные услуги от населения за
1 месяц с отражением в структуре таблицы информации 1-7 задачи. Учесть следующее:
-даты поступления счетов от районов фиксируются в произвольном порядке, т.е. поздние даты могут быть впереди начальных дат месяца;
-наименования районов формируются в произвольном порядке;
-категории услуг формируются последовательно (газ, свет, кв.м);
-сумма — случайно распределенные числа в диапазоне, соответственно:
за газ — [150;600];
за свет — [120;800];
за кв.м — [250-1000];
пеня (%) — [0-5] - случайные целые числа.
Требования:
1.Для выполнения задания потребуется 8 рабочих листов Excel.
На 1-ом листе оформляется в виде списка все сведения о поступивших счетах оплаты коммунальных услуг по районам города (Рис. 2.35).
На 2-ом листе сформировать таблицу, отфильтрованную по категории оплаты за газ (Рис. 2.37).
На 3-м листе сформировать таблицу, отфильтрованную по оплате за свет в первую десятидневку месяца с построением диаграммы (Рис. 2.38).
На 4-м листе сформировать сводную таблицу по заданию.
На 5-ом листе сформировать таблицу в соответствии с условием фильтрации списка по
варианту самостоятельной работы.
На 6, 7, 8-м листах сформировать сводные таблицы самостоятельной работы.
1
2.Оглавление таблицы должно содержать: название города, месяца;
3.Указаны реквизиты банка (наименование банка, адрес, расчетный счет).
Порядок выполнения задания.
9.1Открыть книгу с заданиями по Excel и дополнить ее необходимым количеством рабочих листов для выполнения задания.
9.2Выбрать чистый рабочий лист и заполнить заголовок и строку заголовков таблицы (рис. 2.35).
Действия:
•Оформить диапазон A1:G8.
•Задать строку заголовков (рис.2.35, 9-я строка), для этого в каждом столбце этой строки задать
имя поля по образцу.
•Заполнить данными столбцы Район и Дата.
•В ячейки С10, С11, С12 ввести текст соответственно газ, свет, кв.м
•Выделить диапазон ячеек С10:С12, установить указатель мыши на квадратик в нижнем правом углу (маркер заполнения) ячейки С12, пока не появится черный крестик и протянуть обрамляющую рамку вниз до ячейки С55.
9.3Выполнить вычисления.
Действия:
•В ячейку D10 ввести формулу =ОКРУГЛ(СЛУЧМЕЖДУ(150;600);2) и нажать клавишу
<Enter>.
•В ячейку D11 ввести формулу =ОКРУГЛ(СЛУЧМЕЖДУ(120;800);2) и нажать клавишу
<Enter>.
•В ячейку D12 введите формулу =ОКРУГЛ(СЛУЧМЕЖДУ(250;1000);2) и нажать клавишу
<Enter>.
9.4Заполнить формулами ячеек D10, D11, D12 диапазон ячеек D13:D55.
9.5В ячейку Е10 ввести формулу =ОКРУГЛ(СЛУЧМЕЖДУ(0;5);2) и нажать клавишу <Enter>.
9.6Заполнить формулами диапазон ячеек Е11:Е55.
В результате выполненных вычислений по формулам, значения результатов не будут совпадать с
результатами на образце (рис.2.35), т. к. для расчетов применялась функция датчика случайных
чисел.
9.7В ячейку F10 ввести формулу =D10*E10/100 и нажать клавишу <Enter>. Заполнить этой формулой диапазон ячеек F11:F55.
9.8В ячейку G10 ввести формулу =D10+F10 и нажать клавишу <Enter>. Заполнить этой формулой диапазон ячеек G11:G55.
9.9Сохранить числовые значения Базы данных.
•Выделить числовые значения таблицы (диапазон D10:G55). Действовать по схеме
Главная ► Буфер обмена ► Копировать.
2
Затем открываем выпадающий список команды Вставить
Выбираем Вставить значения ► Значения и форматы чисел
Этими действиями в скопированном блоке ячеек будут зафиксированы только числовые значения.
Рис. 2. 1 Таблица списка данных базы данных
3
9.10 Добавить новые записи, используя возможности Формы.
Режим Формы — это альтернативный вариант заполнения и редактирования таблиц данными в Excel. Названия полей ввода соответствуют названиям полей записи, определенным в строке заголовков.
Действия:
•Добавить команду Форма на вкладку Данные.
•Щелкнуть мышью на любой ячейке в строке заголовков, затем выбрать Данные ► Форма. На рабочем листе появится окно Формы для ввода и редактирования данных списка (Рис. 2.36).
Рис. 2. 2 Окно формы для ввода и редактирования записей списка
Заполнять форму можно используя клавишу <Таb> для перехода между полями, затем щелкая по кнопке Добавить окна Формы. По этой команде данные из формы будут перенесены в соответствующие поля записи. Добавляемая запись размещается в конце списка.
• Дополнить таблицу не менее десяти новыми записями, используя форму.
9.11После полного оформления таблицы (рис. 2.35), переименовать стандартное имя листа на имя ПЛАТЕЖИ и скопировать сформированную таблицу на другие три свободных листа.
9.12Произвести фильтрацию таблицы по полю Услуги.
Действия:
•Перейти на лист со скопированной таблицей и установить курсор в поле Услуги строки
Заголовка. Выбрать команду Данные ► Сортировка и фильтр ►Фильтр. В углах ячеек имен полей строки Заголовка появятся квадратики с черными треугольничками (списки).
•Щѐлкнуть указателем мыши по черному треугольничку поля Услуги и установить флажок напротив услуги — газ. В итоге, будет получен результат фильтра (рис. 2.37).
4
Рис. 2.3 Результат работы Автофильтра выборки оплаты за газ
9.13 Произвести фильтрацию таблицы по полю Дата и Услуги.
Действия:
•Перейти на другой лист со скопированной таблицей и установить курсор в поле Дата строки
Заголовка. Выбрать команду Данные ► Сортировка и фильтр ► Фильтр. В углах ячеек имен полей строки Заголовка появятся квадратики с черными треугольничками (списки).
•Щѐлкнуть указателем мыши по черному треугольничку поля Дата и выбрать из появившегося списка Фильтр по дате ► До…. Появится окно Пользовательский автофильтр.
•В окне Пользовательский автофильтр, установить в поле Дата условие — до, а в соседнем поле справа установить дату — 11.01.10.
•Произвести фильтрацию по полю Услуги, выбрать условие — свет. В результате на экране будут отображены отфильтрованные данные (рис. 2.38).
9.14Построить диаграмму для данных столбцов Дата и Всего отфильтрованной таблицы
(рис. 2.38).
Действия
•Выделить данные отфильтрованного столбца Дата, нажать клавишу <Ctrl> и, не отпуская клавишу, выделить данные столбца Всего.
•Для построения диаграммы активизировать Мастер диаграмм.
•Результат построений на рис. 2.38.
5
Рис. 2.4 Таблица и график данных фильтра по полям: Дата и Услуги
9.15 Отфильтровать данные с помощью Расширенного фильтра и скопировать результат в другое место рабочего листа Excel.
Действия
• Скопировать еще раз исходную таблицу данных на свободный лист (рис. 2.40).
Расширенный фильтр распознает три специальных имени диапазонов: "База данных",
"Критерии", "Извлечь" (рис.2.40).
•Оформить область критериев по образцу (внести диапазон A57:G60 рис.2.40). Критерий отбора записей формулируется так:
критерий 1-й строки—Извлечь из базы данных платежи за кв.м в 1-ю половину месяца, не превосходящие сумму (столбец Всего 500);
критерий 2-й строки — Извлечь платежи по Центральному району за Свет.
Несколько критериев одной строки связаны логической функцией — И
6
Критерии на разных строках связаны логической функцией — ИЛИ
• Выделить диапазон A9:G55.
• Активизировать Расширенный фильтр по схеме Данные ► Сортировка и
Фильтр ► Дополнительно…
Появится окно Расширенный фильтр(Рис. 2.39).
Рис. 2.5 Диалоговое окно Расширенного фильтра
•Заполнить поля окна Расширенного фильтра по образцу (Рис. 2.39). Заполнять диапазоны
базы данных, критериев и извлеченных данных можно выделением. Для этого следует предварительно щелкнуть на красной стрелке поля, которая находится справа в полях ввода, а
затем выделять мышью соответствующие диапазоны для Базы данных, Критерии, Извлечь.
В группе переключателей Обработка следует установить переключатель Скопировать результат
в другое место.
•Результат отобразится в указанном диапазоне.
9.16Сформировать Сводную таблицу, содержащую данные по районам и услугам.
Действия:
•Открыть лист Платежи.
•Выполнить команду Вставка ► Таблицы ► Сводная таблица.
•Выполнить первые шаг работы с Мастером самостоятельно.
•На втором шаге (Рис.2.41) перетащить значки с названиями столбцов нашей таблицы следующим образом:
Информация, которую мы хотим разместить в строках, например:
−Район. Схватить мышью значок и перетащить в область Строка.
−Услуги – в область Столбец.
−Всего - в область Данные.
•Если выполнить щелчок на перенесенных значках, можно редактировать их назначение.
7
Рис. 2.6 Три диапазона данных расширенного фильтра
8
Рис. 2.71 Второй шаг Мастера Сводных таблиц.
•Программа предлагает создать на новом или существующем листе сводную таблицу.
•Просмотреть сформированную таблицу (Рис.2.42).
Рис. 2.42 Сводная таблица.
9.17 Приобрести навыки преобразований Сводных таблиц.
Действия:
•Установить курсор в область сводной таблицы и выполнить команду Работа со сводными таблицами ►Параметры ► Показать ► Список полей.
•Перенести поле Район на область Столбец, а поле Услуги на область Строка.
•Просмотреть полученную таблицу(Рис.2.43):
Рис.2.43 Сводная таблица.
•Переместить поле Дата на область Строка.
•Выполнить команду Работа со сводными таблицами ►Конструктор ►Макет ► Макет
9
отчѐта ► Показать в табличной форме, получиться следующая таблица (Рис.2.44).
Рис.2.44 Сводная таблица
9.18Отфильтровать сведенные данные.
•Установить курсор в область сводной таблицы и выполнить команду Работа со сводными таблицами ►Параметры ► Показать ► Список полей.
•Перенести поле Район из области Название столбцов в поле Фильтр отчѐта.
В первой строке рабочей области появится надпись Район и выпадающий список с надписью
Все.
•Щелкнуть по язычку Все и выбрать Центральный район.
•Просмотреть полученные результаты (Рис 2.45).
9.19 Скопировать еще раз исходную таблицу данных на свободный лист для выполнения самостоятельной работы.
10