Цели работы
1. Изучить возможности для организации данных в Excel в виде списка или базы данных.
2. Освоить технологию обработки cписков в Excel.
3. Научиться извлекать определенные записи и поля из баз данных.
Планирование списка
Excel может работать со списками данных, занимающими большой объем дискового пространства или базами данных. Введем некоторые понятия, характерные для баз данных в Excel.
База данных – список, состоящий из одного или более столбцов.
Список – последовательность строк рабочего листа, содержащего в столбцах подобные по типу данные.
Список включает три основных компонента:
запись – содержать полную информацию по конкретному пункту;
поле – составная часть записи, содержимое которого можно использовать для сортировки и поиска записей;
строка заголовка – с трока в начале списка. Каждое поле записи помечается заголовком и используется при сортировке, извлечении данных и составлении отчетов.
В списке, реализованном в Excel, каждый столбец является полем, а каждая строка – записью.
При построении списка следует:
– использовать различные шрифты для таблицы и строки заголовков (курсив или полужирный шрифт);
– заносить данные строго в соответствующие поля;
– использовать прописные буквы (сортировка и поиск с учетом или без учета регистра);
– при необходимости вычислений использовать формулы;
– не отделять строку заголовков от прочих данных пустой строкой;
– не разделять заголовки пустыми столбцами;
– не использовать пробел первым символом в полях (создает проблемы при поиске и сортировке);
– избегать размещения списка и прочих данных на одном листе или отделить список пустыми строками и столбцами;
– при работе с несколькими списками выделять для каждого из них отдельный лист;
– не дублировать информацию записей списка;
– предусмотреть пространство для расширения списка.
Создать и заполнить список можно:
1. С помощью команды Форма можно автоматически создать форму, в которой выводится нужная запись и средства для редактирования данных и для добавления новой информации. Для этого варианта создания списка:
– задать строку заголовков, в каждом столбце указать название поля;
– щелкнуть мышью на любой ячейке в строке заголовков, выбрать Форма (рис. 18);
Рис. 18. Окно формы для просмотра, редактирования
и фильтрации записей по критерию
– заполнить форму, используя клавишу < Тab > для перехода между полями, щелкнуть по кнопке Добавить. По этой команде данные из формы будут перенесены в соответствующие поля записи (запись размещается в конце списка).
2. Создать строку заголовка, затем заполнять список, вводя вручную информацию в каждое поле. Если данные в ячейках таблицы повторяются, использовать Автозаполнение. Выделив диапазон базы данных нажать Главная / Форматировать как таблицу, в полях заголовка автоматически появятся маркеры всплывающего списка.
Задание
Создать базу данных Excel оплаты населением города коммунальных услуг, состоящую из следующих столбцов (полей):
| № п/п | Характеристики данных списка | Имена полей |
| Наименование округа | Район | |
| Дата поступления счета | Дата | |
| Плательщик | Квартира | |
| Категория коммунальных услуг (газ, свет, кв. м) | Услуги | |
| Оплаченная сумма (тыс. руб.) | Сумма | |
| Задолженность (%) | Пени(%) | |
| Задолженность (руб.) | Пени(руб) | |
| Всего оплачено | Всего |
Сформировать таблицу поступлений счетов за коммунальные услуги от населения за месяц с учётом следующих условий:
- поступление счетов происходит раз в неделю;
- даты поступления счетов от районов фиксируются в произвольном порядке;
- наименования районов формируются в произвольной последовательности;
- категории услуг формируются последовательно (газ, свет, м2);
- стоимость – случайно распределенная величина в диапазоне: за газ – [1; 10]; за свет – [10; 25]; за кв. м – [50; 500];
- пени – случайно распределенная величина от 1 до 12.
Задания разместить на 5-ти рабочих листах Excel.
1. Базу данных в виде списка, все сведения о поступивших счетах оплаты коммунальных услуг по районам города.
2. Таблица, отфильтрованная по категории за газ.
3. Таблица, отфильтрованная по оплате за свет в первую десятидневку месяца с построением диаграммы.
4. Таблица в соответствии с условием фильтрации списка по технологии Расширенного фильтра.
5. Таблица и диаграмма в соответствии с условием фильтрации списка по варианту самостоятельной работы.
Примечание. Учитывать рабочие дни в соответствии с календарем.
Порядок выполнения работы
1. Создать книгу Excel, дополните ее необходимым количеством рабочих листов.
- Заполнить рабочий лист по образцу (рис. 19).
Рис. 19. Лист «База данных»
- заполнить данными столбцы A, B, C (использовать автозаполнение, задав в С 3, C 4, C 5 соответственно газ, свет, кв. м). Задать 50 записей;
- выполнить вычисления в столбце поля Сумма с использованием функций:
ОКРУГЛ() –округления чисел до указанного количества десятичных разрядов,
Например: =ОКРУГЛ(СЛУЧМЕЖДУ(1;10);2)
СЛУЧМЕЖДУ() –генерирования случайных чисел в указанном интервале значений.
Примечание. Результаты вычислений по формулам могут не совпадать с результатами вычислений на образце, так как использован датчик случайных чисел;
- выполнить вычисления в столбце поля Пени (%) диапазон [1, 12].
- выполнить вычисления в столбце поля Пени ( руб): (формула = D 3* E 3/100).
- выполнить вычисления в столбце поля Всего (= D 3+ F 3).
3. Сохранить числовые значения сформированной базы данных на отдельном листе.
4. Выделив диапазон базы данных, нажать Главная / Форматировать как таблицу, в полях заголовка автоматически появятся маркеры всплывающего списка.
5. Просмотреть строки базы данных по одной, используя режим Формы (рис. 18).
6. Скопировать сформированную базу данных на следующие три листа книги, озаглавить листы.
7. Произвести фильтрациюбазы данных по полю Услуги (рис. 20).
Рис. 20. Фильтрация базы данных по полю Услуги
8. Произвести фильтрацию базы данных по полям Дата (меньше указанной даты) и Услуги (Свет).
9. Построить гистограмму для списка значений отфильтрованных по критериям двух полей – Дата и Услуги.
10. Выполнить фильтрацию данных списка на отдельном листе с помощью Расширенного фильтра.
Расширенный фильтр распознает три специальных диапазона: «База данных», «Критерии», «Извлечь», которые можно разместить на одном листе, разделив диапазоны пустыми строками и столбцами:
- скопировать диапазон «База данных»;
- выделить заголовки полей базы данных, скопировать в область критериев и в область отфильтрованных результатов, ввести названия областей соответственно «Критерии» и «Извлечь»;
Рис. 21. Фильтрация при помощи Расширенного фильтра
- оформить область критериев, задав номер квартиры и выбранную услугу (рис. 21);
Примечание. Несколько критериев одной строки связаны логической функцией И. Критерии разных строк связаны логической функцией ИЛИ (см. рис. 21 ).
- активизировать расширенный фильтр Данные / Фильтр / Дополнительно / Расширенный фильтр. Появится окно Расширенный фильтр (рис. 22). Заполнить поля окна Расширенного фильтра для трех диапазонов «База данных», «Критерии», «Извлечь» выделением, предварительно щелкнув на красной стрелке поля справа. Установить Фильтровать список на месте.
11. Сохранить книгу.
Отчет о лабораторной работе должен содержать электронный документ с указанием фамилии и группы студента, времени выполнения, названия, цели лабораторной работы, выполненные задания описательной части работы и задания по предложенному преподавателем варианту.
Рис. 22. Диалоговое окно расширенного фильтра
Задания для самостоятельного выполнения
Сформировать таблицу – результат фильтрации из исходной таблицы базы данных по условию варианта (1–5).
| Вар. | Условие фильтрации | |||
| Счета для газа во 2-ю декаду | Сумма счета в диапазоне 5¸10 за газ | Счета ЮВАО за свет | Пени за газ не превышают 5 % | |
| Счета для света в 3-ю декаду | Сумма счета в диапазоне 15¸25 за свет | Счета ВАО за газ | Сумма счета в диапазоне 25¸100 | |
| Счета для света в 1-ю декаду | Сумма счета в диапазоне 50¸200 за кв.м. | Счета САО за свет | Пени за свет не превышают 5 % | |
| Счета для кв.м. в 1-ю неделю | Сумма счета за кв.м. в диапазоне 200¸500 | Счета ЮАО за кв.м. | Сумма счета в диапазоне 50¸120 | |
| Счета для кв.м в 3-ю неделю | Сумма счета в диапазоне 15¸20 за свет | Счета ЗАО за газ | Пени за газ менее 5 % |
