Лабораторная работа 7. Технология организации и обработки списков данных в EXCEL

Технология организации и обработки списков данных в EXCEL

Цели работы

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

2. Освоить технологию обработки cписков в Excel.

3. Научиться извлекать определенные записи и поля из баз данных.

Планирование списка

Excel может работать со списками данных, занимающими большой объем дискового пространства или базами данных. Введем некоторые понятия, характерные для баз данных в Excel.

База данных – список, состоящий из одного или более столбцов.

Список – последовательность строк рабочего листа, содержащего в столбцах подобные по типу данные.

Список включает три основных компонента:

запись – содержать полную информацию по конкретному пункту;

поле – составная часть записи, содержимое которого можно использовать для сортировки и поиска записей;

строка заголовка – с трока в начале списка. Каждое поле записи помечается заголовком и используется при сортировке, извлечении данных и составлении отчетов.

В списке, реализованном в Excel, каждый столбец является полем, а каждая строка – записью.

При построении списка следует:

– использовать различные шрифты для таблицы и строки заголовков (курсив или полужирный шрифт);

– заносить данные строго в соответствующие поля;

– использовать прописные буквы (сортировка и поиск с учетом или без учета регистра);

– при необходимости вычислений использовать формулы;

– не отделять строку заголовков от прочих данных пустой строкой;

– не разделять заголовки пустыми столбцами;

– не использовать пробел первым символом в полях (создает проблемы при поиске и сортировке);

– избегать размещения списка и прочих данных на одном листе или отделить список пустыми строками и столбцами;

– при работе с несколькими списками выделять для каждого из них отдельный лист;

– не дублировать информацию записей списка;

– предусмотреть пространство для расширения списка.

Создать и заполнить список можно:

1. С помощью команды Форма можно автоматически создать форму, в которой выводится нужная запись и средства для редактирования данных и для добавления новой информации. Если команды Форма нет на ленте, выполнить команды Настройка панели быстрого доступа - Команды не на ленте - Формы. Для этого варианта создания списка:

– задать строку заголовков, в каждом столбце указать название поля;

– щелкнуть мышью на любой ячейке в строке заголовков, выбрать Форма (рис. 18);

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

2. Создать строку заголовка, затем заполнять список, вводя вручную информацию в каждое поле. Если данные в ячейках таблицы повторяются, использовать Автозаполнение. Выделив диапазон базы данных нажать Главная / Форматировать как таблицу, в полях заголовка автоматически появятся маркеры всплывающего списка.

Рис. 18. Окно формы для просмотра, редактирования
и фильтрации записей по критерию

Задание

Создать базу данных Excel оплаты населением города коммунальных услуг, состоящую из следующих столбцов (полей):

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

- поступление счетов происходит раз в неделю;

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

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

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

- стоимость – случайно распределенная величина в диапазоне: за газ – [1; 10]; за свет – [10; 25]; за кв. м – [50; 500];

- пени – случайно распределенная величина от 1 до 12.

Задания разместить на 5-ти рабочих листах Excel.

1. Базу данных в виде списка, все сведения о поступивших счетах оплаты коммунальных услуг по районам города.

2. Таблица, отфильтрованная по категории за газ.

3. Таблица, отфильтрованная по оплате за свет в первую десятидневку месяца с построением диаграммы.

4. Таблица в соответствии с условием фильтрации списка по технологии Расширенного фильтра.

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

Примечание. Учитывать рабочие дни в соответствии с календарем.