Работа с базами данных в excel лабораторная работа

Практическая работа

Тема: «Создание БД в MS Excel»

Цель: познакомиться с правилами оформления БД в MS Excel

Оборудование: АРМ.

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

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

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

• Даты поступления счетов от районов фиксируются в произвольном порядке, т.е. поздние даты могут быть впереди начальных дат месяца;
• Наименование районов   формируются в произвольном порядке;
• Категория услуг формируется последовательно (газ, свет, кв.м);
• Сумма-случайно распределенные числа в диапазоне,соответственно:

Требования:

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

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

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

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

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

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

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

1. Создать новую книгу в Excel, дополнить ее необходимым количеством

рабочих листов для выполнения задания.

Рис. 1 База данных «Коммунальные платежи»

Выделить пять листов и заполнить оглавление, шапку таблицы Рис1.

Рис.2 Заполнение базы данных

3.в ячейки С10,С11,С12 ввести текст соответственно газ, свет, кв.м, выделить диапазон ячеек С10:С12, установить указатели мыши на квадратик в нмжнем правом углу (маркер заполнения) ячейки С12, пока не появится черный крестик и протянуть обрамляющую рамку вниз до ячейки С55.

4. В ячейку D10 ввести формулу =ОКРУГЛ(СЛУЧМЕЖДУ(1;10);2) и нажать клавишу; [Enter].

5.  В ячейку D11 ввести формулу =ОКРУГЛ(СЛУЧМЕЖДУ(10;25);2) и нажать клавишу; [Enter].

6. В ячейку D12 ввести формулу =ОКРУГЛ(СЛУЧМЕЖДУ(50;500);2) и нажать клавишу; [Enter].

7.Выделить  диапазон ячеек D10: D12 и установить указатель мыши на маркер заполнения, скопировать формулы до ячейки D55.

8. В ячейку E10 ввести формулу ОКРУГЛ(СЛУЧМЕЖДУ(1;12);2) и нажать клавишу [Enter].

9. заполнить формулами диапазон ячеек Е11:Е55 методом АВТОЗАПОЛНЕНИЯ.  В результате выполнения вычислений по формулам, значения результатов не будут совпадать с результатами на образце, т.к для расчетов применилась функция датчика случайных чисел.

10. В ячейку F10 ввести формулу =D10*E1/100 и нажать клавишу [Enter]. Заполнить этой формулой диапазон ячеек F11:F55.

11. В ячейку  G10 ввести формулу =D10+F10 и нажать клавишу [Enter]. заполнить этой формулой диапазон ячеек G11:G55.

12. Выделить числовые значения таблицы (диапазон D10:G55). Установить указатель мыши на выделенной области и нажать правую клавишу мыши. в появившемся контекстном меня выбрать команду Копировать. Выделить ячейку D10 и выбрать команду Правка=>Специальная вставка. В появившемся диалоговом окне Специальная вставка установить переключатель Значения, нажать кнопку ОК.

Этими действиями в скопированном блоке ячеек зафиксированы только числовые значения.

13. Выделить только первый лист.

14.переименовать лист 1, присвоить ему имя ПЛАТЕЖИ.

15. на втором листе произвести фильтрацию по полю Услуги. Для этого выделить ячейку С9 и выбрать команду данные=>фильтр=>Автофильтр. Установить  указатель мыши на появившемся квадратике с черным треугольником (список) и выбрать из появившегося списка-газ. В итоге, будет получен результат фильтра См рис2.

Рис 3

16.перейти на Лист 3 и произвести фильтрацию таблицы по полю Дата (условие…<11.05.00) и Услуги (Условие…=свет).

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

Рис 4 Задание на Лист 3

18.Перейти на четвертый лист, отфильтровать  данные с помощью Расширенного фильтра и скопировать результат в другое место рабочего листа Excel. Расширенный фильтр распознает три специальные имени диапазонов: «База данных», «Критерии», «Извлечь» (рис.4.). Оформить область критериев по образцу (рис.4). критерий отбора записей формируется так:

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

ИЛИ

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

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

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

• Активизировать расширенный фильтр по схеме данные=>фильтр=>расширенные фильтр
• Заполнить поля окна расширенного фильтра по образцу:

Заполнять диапазоны базы данных критериев и извлеченных данных можно выделением. Для этого следует предварительно щелкнуть на красной стрелке поля, которая находится справа в полях ввода, а затем выделить мышью соответствующие диапазоны для  Базы данных, критериев, извлечь. В группе переключателей Обработка следует установить переключатель Скопировать результат в другое место.

20. На пятом листе выполнить самостоятельную работу .

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

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

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

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

1. Счета для света в 3-ю десятидневку месяца
2. Счета для света в 1-ю десятидневку месяца
3. Счета для кв м в  1-ю неделю  месяца
4. Счета для кв м в  3-ю неделю  месяца
5. Счета в диапазоне 50/100 тыс.руб.
6. Счета в диапазоне 15/50 тыс.руб.
7. Счета в диапазоне 250/350тыс.руб.
8. Счета в диапазоне 100/200 тыс.руб.
9. Счета в диапазоне 350/500 тыс.руб.
10. Счета Центрального района за свет
11. Счета Центрального района за газ
12. Счета Центрального района за кв.м
13. Счета Индустриального района в 1-ю половину месяца
14. Счета Торгового  района во  2-ю половину месяца.

После выполнения практической работы, сделать самостоятельную работу по вариантам (номер варианта соответствует номеру ученика в списке электронного журнала).

По практической работе сделать отчёт, написать вывод.

Лабораторная работа 7. 

 РАБОТА С БАЗАМИ ДАННЫХ В MICROSOFT EXCEL

Цель работы: освоить основные приемы работы
с базами данных в Microsoft Excel:

производить сортировку данных, использовать автофильтр и
расширенный фильтр при поиске информации в базе данных. 

Пример

Задание. Создайте в Microsoft Excel таблицу,
как на рисунке 1.8. Рассчитайте стаж работы. Просмотрите данные о продавцах
женского пола, работающих на предприятии больше 4-х лет. Отсортируйте данные
таким образом, чтобы работники располагались по возрастанию разряда, а те, в
свою очередь, по алфавиту. Выведите на экран список сотрудников, проживающих в
Пятигорске. Сформируйте список сотрудников – женщин, проживающих в Пятигорске,
имеющих 3-ий разряд. 

Решение. Переименуйте лист. Для этого дважды
щелкните мышью по названию текущего рабочего листа. Название листа выделится.
Введите База данных – ОК.  В строке «2» наберите шапку таблицы как на
рисунке 1.8. 

Рис. 1.8. Сведения о сотрудниках

Установите в столбце Ф.И.О. формат ячейки текстовый
(выделите столбец – нажмите левой кнопкой мыши на обозначение столбца 
(например D), нажмите ФОРМАТ, выберите «ячейки» — текстовый), установите
формат ячейки – текстовый – для столбцов «должность», «адрес», «пол».
Для столбца «телефон» таким же образом установите тип «номер телефона» из
формата дополнительный. Для столбцов содержащих даты – установите формат
«дата», для столбцов №п/п, Разряд – числовой формат, 0 знаков
после запятой. 

Справа от столбца «стаж работы» вставьте новый столбец. Для
этого выделите столбец справа от «стажа работы», наведите на выделенную область
курсор, нажмите правую кнопку мыши и в МЕНЮ выберите ДОБАВИТЬ ЯЧЕЙКУ.
Появился новый пустой столбец. Введите название «стаж работы – округленный».

Выделите ячейку А1 и введите текст «текущая дата», в ячейку
рядом введите текущую дату, задайте формат ячейке «дата».

Рассчитайте стаж работы. Для этого в столбце «стаж работы»
введите формулу «(текущая дата — дата найма)/365». Установите ячейку «текущая
дата» в формуле — абсолютной (используйте знак $). «Протяните» формулу. В
столбце «стаж работы округленный» необходимо округлить полученный результат
так, чтобы учитывались только полные годы работы. Для этого выделите ячейку в
столбце «стаж работы округленный», вызовите список функций, в Математических
найдите формулу ОКРУГЛВНИЗ. Откройте окно формулы, в ячейку число
введите ту ячейку, число в которой надо округлить, количество цифр – 0,
ОК. Проделайте то же самое для всей таблицы.     

Поиск необходимых сведений в базе данных. 

Выделите всю таблицу. Задайте ей имя «Штат_сотрудников».
Для этого нажмите ВСТАВКА, ИМЯ, ПРИСВОИТЬ (рис. 1.9). Затем войдите в меню
ДАННЫЕ, выберите ФОРМА. Появится окно, отражающее аргументы созданной таблицы.
В появившемся окне просмотрите данные о продавцах женского пола работающих на
предприятии больше 4-х лет. Для этого нажмите кнопку КРИТЕРИИ и введите
в соответствующие ячейки заданные условия (в «стаж работы» — >4). Нажмите ДАЛЕЕ.
Просмотрите список полученных результатов отбора (рис. 1.10). Закройте окно.

Рис. 1.10. Просмотр
данных с помощью приложения  Microsoft Excel ФОРМА Для того, чтобы
расположить информацию в таблице в определенном порядке используйте команду СОРТИРОВКА.
Для этого выделите ячейку «Ф.И.О.», выберите в строке меню ДАННЫЕ,
СОРТИРОВКА, в появившемся окне установите «Сортировать по» — разряду,
«Затем по» Ф.И.О. – по возрастанию. Просмотрите отсортированные
данные.

При необходимости выделить из таблицы данные, отвечающие
определенному условию, воспользуйтесь командой Фильтрация. Для этого
активизируйте ячейку «Ф.И.О.». Выберите ДАННЫЕ, ФИЛЬТР, АВТОФИЛЬТР. В ячейках
с названиями столбцов появились стрелочки. Нажмите на такую стрелку в столбце Адрес.
В появившемся списке выберите УСЛОВИЕ, в окне Пользовательский
автофильтр введите Пятигорск (рис. 1.11). На экране появится список
работников проживающих в Пятигорске. Повторно нажмите стрелку в столбце Адрес,
выберите ВСЕ. Самостоятельно сформируйте список сотрудников принятых на
работу после 01.01.1999 года. Скопируйте полученный список сотрудников в нижнюю
часть страницы. Первоначальную таблицу верните к исходному виду.

Рис. 1.11. Использование приложения АВТОФИЛЬТР
для обработки данных

Если необходимо найти информацию, отвечающую двум и более
условиям, используйте команду Расширенный фильтр. Для этого скопируйте
шапку таблицы и вставьте ее в нижнюю свободную часть листа. В столбце Адрес
запишите условие Пятигорск, в столбце Разряд — 3, в столбце Пол —
жен. Затем в меню ДАННЫЕ выберите ФИЛЬТР — РАСШИРЕННЫЙ ФИЛЬТР,
в появившемся окне задайте аргументы: Исходный диапазон – диапазон
исходной таблицы, Диапазон условий – таблица с условиями, в ОБРАБОТКЕ
выберите Скопировать результат в другое место (рис. 1.12), в строке Поместить
результат в другое место укажите пустой диапазон ниже таблиц. Нажмите ОК. 

Рис. 1.12. Использование
приложения РАСШИРЕННЫЙ ФИЛЬТР для обработки данных Появилась таблица с
работниками, отвечающими заданным условиям.    

Контрольные вопросы

Какие способы существуют в программе Microsoft Excel для
просмотра и редактирования данных? 

В чем различие между приложением Microsoft Excel
АВТОФИЛЬТР И РАСШИРЕННЫЙ ФИЛЬТР?

Что необходимо сделать, прежде чем воспользоваться
РАСШИРЕННЫМ ФИЛЬТРОМ?

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ ПО
ЛАБОРАТОРНОЙ РАБОТЕ 7

Задача 1. Создайте таблицу, содержащую
следующие сведения о сотрудниках организации (см.: рисунок 1.13).

Отсортируйте записи по: 1). алфавиту фамилий, 2).
уменьшению разряда. Выдайте список сотрудников организации:

а). Проживающих в городе Пятигорске,

б). Чей телефон начинается на 34.

в). Проживающих в Пятигорске, старше 25 лет
на момент осуществления поиска

информации, принятых после 16.04.2000. 

Задача 2. Предприятие «Альфа» осуществляет
оптовую реализацию бытовой техники со складов. Имея сведения о количестве
проданной продукции в феврале, определите суммы выручки предприятия за месяц.
Создайте таблицу, отражающую реализацию кофеварок и миксеров за месяц. Создайте
таблицу, показывающую все поставки предприятия «Бета» ООО «Авангард» с 15.02.03
на сумму превышающую 2000000 руб. 

Рис. 1.14 Продажи предприятия «Альфа» за
февраль

Текущая
и две последующие лабораторные работы
посвящены изучению средств и методов
работы с таблицами данных, предоставляемых
табличным процессором Excel.
Работы могут проводиться на компьютерах
с различными операционными системами.
Поэтому большая часть рисунков
представлена как для ОС Windows
так и для ОС Mac—OS—X.
Рисунки для ОС Mac—OS—X
имеют те же номера что и для ОС Windows,
но завершаются пометкой «mac».

Лабораторная работа № 1

Цель занятия.

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

Начало
работы: Открыть программуWordи создать файл «Отчет по лаборатории
№1»; Открыть программуExcel и
создать файл «Лабораторная работа №1».

Открыть
Интернет для поиска значений команд по
справочным системам.

Задание.

1. Изучить
   теоретическое обоснование.

2. Создать
   собственную таблицу на основе структуры
   таблицы №1 и наполнить её данными из
   таблицы №1. Отдельно сохранить копию
   созданной таблицы. Сохранить файл.
   Таблицу или копию экрана поместить в
   файл отчета. Дать объяснение каждой
   копии и команде.

3. Выполнить
   поиск по трем заданным в работе критериям.
   Описание критерий и выборки записей,
   удовлетворяющие им, поместить в файл
   отчета.

4. Выполнить
   сортировку по трем заданным в работе
   критериям. Описание критерий или копии
   экрана с ними, а также получаемые после
   сортировок таблиц поместить в файл
   отчета. Дать объяснение каждой копии
   и команде.

5. С помощью
   Автофильтра произвести фильтрацию по
   одному полю, двум полям и трем полям
   одновременно. Описание условий фильтраций
   и копии экранов с ними, а также виды
   получаемых таблиц поместить в отчет.
   Дать объяснение каждой копии и команде.

6. Выполнить
   Расширенную фильтрацию на основе
   заданных в работе критерий. Таблицу с
   заданных критерий для расширенной
   фильтрации и полученную таблицу
   поместить в отчет. Дать объяснение
   каждой копии и команде.

7. Предложить
   и сформулировать виды итогов. Получить
   таблицу итогов. Описание получаемых
   итогов и таблицу с ними поместить в
   отчет. Дать объяснение каждой копии и
   команде.

8. Оформить
   отчет: TimesNewRoman, 14; поля: верхнее – 2,
   нижнее – 1,5 левое – 3, правое – 1,5. Общая
   страница – не менее 15 стр.

9. Защищать
   Отчет у преподавателя.

Заключение.

В результате
выполнения лабораторной работы вы
должны знать и уметь выполнять:

ввод данных,

поиск данных
в базе,

сортировку
и фильтрацию данных,

подведение
промежуточных итогов по данным базы.

Теоретическое
обоснование.

Таблицы Excelможно использовать в качестве баз
данных. В этом случае применяется
следующая терминология: столбцы таблицы
называются полями, верхняя строка –
заголовками полей, остальные строки –
записями (рис.1).

Рис.1

Основными
задачами при работе с базами данных
являются:

• ввод данных,

• поиск
  данных,

• сортировка
  и фильтрация данных,

• подведение
  промежуточных итогов по данным базы.

Эти операции
доступны через пункт меню ДАННЫЕ
(DATA)(рис.2).

Рис.2

Рис.2-mac

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

Выделяется
любая ячейка таблицы

Выбирается
пункт меню: ДАННЫЕ -> Форма (DATA-Form…).

На экране
появится форма, приведенная на рис.3.

Рис.3

Рис.3-mac

Перемещение
по записям:

Стрелки вверх
и вниз.

Переход к
первой или последней записи:

Ctrl+стрелка
вверх,Ctrl+стрелка вниз.

Перемещение
по полям формы:

Клавиши TABиSHIFT+TAB.

Добавление
или удаления записи:

Кнопки Добавить
(New)иУдалить (Delete).

Копирование
данных из поля предыдущей записи:

Ctrl+кавычки
(клавиша с буквой Э).

Поиск данных
(записей) по критерию.

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

Переход в
режим поиска записей по критерию
осуществляется выбором команд: ДАННЫЕ
-> Форма -> Критерии (DATA-Form…-Criteria).
Затем следует задать в полях условия
поиска. При этом можно использовать
следующие символы-заменители:

?— заменяет
один символ,

*— заменяет
любое количество символов,

< > <=
>= =используются для записи условий.

После задания критерия
нажатие кнопок Назад и Далее будет
приводить к переходам только на те
записи, которые удовлетворяют заданным
условиям.

Для нашего
примера таблицы (рис.1) при задании
критериев как на рис.4 отобрана будет
только 1-я запись (рис.3).

Рис.4

Для
упорядочивания данныхслужит операциясортировки:ДАННЫЕ -> Сортировка…
(DATA-Sort…)

Перед вызовом
команды надо поставить курсор в одно
из полей таблицы. После выбора команды
ДАННЫЕ -> Сортировка(DATA-Sort…)
откроется диалоговое окно рис.5.

Рис.5

Рис.5-mac

Как видно в
этом окне можно задать три критерия
сортировки. В полях Сортировать по
(Sort by),Затем по (Then by),В последнюю
очередь по (Then by)задаются названия
сортируемых полей. Справа размещены
переключатели направления сортировки:
по возрастанию (Ascending) – от первой буквы
алфавита к последней, от меньшего числа
к большему, от более ранней даты к более
поздней и по убыванию (Descending)– в обратном
направлении. Переключатели Идентифицировать
поля по подписям и Идентифицировать
поля по обозначениям позволяют
соответственно включать и не включать
верхнюю строку таблицы в процессе
сортировки. Кнопка Параметры открывает
диалоговое окно, позволяющее установить
последовательность нестандартной
сортировки.

Для базы
представленной на рис.1 зададим критерии
сортировки рис.6.

Рис.6

Рис.6-mac

Сортировка
будет осуществляться по полю Жанра
затем Цены и в последнюю очередь по
Количеству заказанных экземпляров.
Результат сортировки представлен на
рис.7.

Рис.7

Рис.7-mac

Для +задаваемым
условиям и перевода остальных в скрытое
состояние служит фильтрацияданных:ДАННЫЕ->Фильтр->Автофильтр
(Data-Filter-AutoFilter)илиДАННЫЕ->Фильтр->Расширенный
фильтр… (Data—Filter—Advanced
Filter…)

Рассмотри
сначала работу с автофильтром. При
выборе команды ДАННЫЕ->Фильтр->Автофильтр
(Data-Filter-AutoFilter)в строке названий полей
размещаются кнопки раскрывающихся
списков (рис.8). С помощью этих кнопок в
выпадающих окнах можно задавать критерии
отбора строк (фильтрации). При задании
критериев в нескольких полях они
объединяются по принципу логического
умножения.

Рис.8

Рис.8-mac

Кроме выбора
одного критерия в выпадающем окне
(например, ЖЗЛ на рис.8) можно выбрать
пункт Условие (Custom Filter…). В этом случае
откроется окно, в котором можно задать
более сложный критерий выбора для
текущего поля. На рис.9 для поля Ценазадан диапазон цен больше 80 и по 240
рублей.

Рис.9

Рис.9-mac

Результат
выполнения сложного условия фильтрации
представлен на рис.10.

Рис.10

Кнопки полей,
для которых выполнена фильтрация,
выделяются синим цветом. Чтобы отменить
автофильтр снимите флажок Автофильтр(Data-Filter-AutoFilter)(повторное выполнение
командыДАННЫЕ->Фильтр->Автофильтр).

Теперь
рассмотрим использование расширенного
фильтра (ДАННЫЕ->Фильтр->Расширенный
фильтр…)(Data-Filter-Advanced Filter…)для
более сложных критериев фильтрации.

Возможности расширенной
фильтрации рассмотрим на примере решения
следующей задачи.

На основе
имеющейся базы (таблицы) нам надо
сформировать таблицу из полей Заказчик,Адрес заказчика,Автор,Название,Жанрс записями соответствующими
условиям:

1. заказ
   оформлен в период 04.01.2000-30.05.2000 на книги
   издательства Молодая гвардия при цене
   книги более 50 рублей,

2. а также
   (или) заказ оформлен в период
   02.02.2000-14.02.2000 на книги издательства Чужое
   ЭХО а также при цене книги менее 400
   рублей,

3. а также
   (или) все заказы, оформленные в период
   01.05.2000-30.05.2000,

4. а также
   (или) все заказы на книги Издательства
   Крымский мост-9Д.

Для решения
задачи необходимо выполнить следующие
действия:

Создать
область критерия. Для этого заполнить
ячейки как показано на рис.11.

Рис.11

Здесь столбцы
соответствуют критериям, отобранным
для фильтрации. Каждая строка описывает
соответствующее условие из четырех
условий фильтрации сформулированных
выше. Условия, записываемые в одной
строке, объединяются условием И. Условия,
записанные в разных строках, объединяются
условием ИЛИ.

Такую таблицу
удобно расположить на отдельном листе.
Мы так и сделаем. Создадим такую таблицу
на листе 2.

Далее на том
же листе где находится база справа или
ниже ее заполняем ячейки именами полей
таблицы, которая будет создана после
фильтрации (Заказчик, Адрес заказчика,
Автор, Название, Жанр). Мы сделаем это
в области расположенной справа от базы.

Рис.12

Устанавливаем
курсор на любую ячейку базы данных и
вызываем диалоговое окно Расширенный
фильтр (ДАННЫЕ->Фильтр->Расширенный
фильтр…)(Data-Filter-Advanced
Filter…) (Рис. 13).

Рис.13

Рис.13-mac

Устанавливаем
переключатель скопировать результат
в другое место. В этом случае мы сможем
видеть и исходную базу и таблицу с
отфильтрованными данными. Задаем
исходный диапазон (соответствует
координатам базы). Задаем диапазоны
условий и размещения результата. Для
понимания координат см. рис.11 и рис.12.

Нажимаем OKи получаем результат представленный
на рис.14.

Рис.14

Теперь перейдем
к рассмотрению процесса получения
итоговпо данным базы. Эту процедуру
рассмотрим на примере решения конкретной
задачи. Но прежде чем сформулировать
эту задачу внесем некоторые изменения
в базу данных. После поля количество,
обозначающее количество книг в заказе,
добавим поле сумма, обозначающее сумму
заказа. В ячейки записей на пересечении
с полемСуммавставим формулу
произведения содержимого ячеек полейЦенаиКоличество. Для этого,
предварительно выделив ячейку первой
записи поляСуммав поле ввода
формулы введем формулу =I3*J3; в ячейку
поляСуммавторой записи введем
формулу =I4*J4 и т.д. Для ускорения этого
процесса после ввода формулы в ячейку
первой записи можно выделить эту ячейку
однократным нажатием и отпусканием
левой кнопки мыши, затем поставить
курсор мыши на правый нижний угол ячейки
и нажав левую кнопку мыши тянуть ее вниз
до конца таблицы. Произойдет копирование
формулы во все ячейки. В результате
будем иметь таблицу как на рис.15.

Рис.15

Теперь
сформулируем задачу получения итогов
по базе данных. Требуется получить
результаты продаж книг в натуральном
и денежном эквиваленте по книгам каждого
жанра отдельно и вместе. Для решения
данной задачи вначале отсортируем
порядок записей по полю Издательство
(ДАННЫЕ -> Сортировка…)(Data-Sort…).
Результат на рис.16.

Рис.16

Теперь вызываем
команду Итоги… (ДАННЫЕ ->Итоги…)(Data-Subtotals…). Результатом станет
появление диалогового окна рис.17. Задаем
значение полей как показано на рисунке.
В полеПри каждом изменении в: (At each
change in)указываем имя поля по группам
позиций которого требуется получение
итогов. В полеОперация (Use function)указываем операцию над значениями
ячеек. В нашем случае это сумма как для
количеств книг так и для величин сумм
заказов. В поле добавить итоги по:
указываем те поля для которых собственно
требуется получение итогов. ЭтоКоличествоиСумма заказа. НажимаемOKи получаем результат рис.18.

Рис.17

Рис.17-mac

Рис.18

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

Рис.19

Контрольные
вопросы к лабораторной работе №1

1. В чем
   заключается цель данной лабораторной
   работы?

2. Дайте
   определение команды «Сортировка»;

3. Что такое
   команда «Фильтр»?

4. В чем
   заключаются отличия между командами
   «Сортировка» и «Фильтр»?

5. Какие
   виде фильтрации производятся в данной
   лабораторной работе?

6. Чем
   отличаются фильтры «Автофильтр» и
   «Расширенный фильтр»?

7. Что такое
   команда «Промежуточные итоги»?

8. Как
   формируются условия для создания
   таблицы «Промежуточные итоги?

9. Объясните,
   чем отличается полученная вами таблица
   «Промежуточные итоги» от основной
   (базовой) таблицы.

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

СОДЕРЖАНИЕ

1. Цель работы.

2. Теоретические положения.

2.1. Общие положения.

2.2. Списки Excel как база данных.

2.3. Проверка данных при вводе.

2.4. Сортировка данных.

2.5. Промежуточные итоги в базе данных.

2.6. Автофильтр.

2.7. Расширенный фильтр.

3. Порядок выполнения работы.

4. Контрольные вопросы.

5. Список рекомендуемой литературы.

Приложения.

Приложение 1. Технологические карты № 1, 2, 3 к лабораторным работам.

Приложение 2. Готовый файл со списком (документ Excel).

Приложение 3. Технология автоматизированного создания списков в Excel.

1. ЦЕЛЬ РАБОТЫ

Цели:

• повышение уровня понимания темы «Базы данных в приложении Microsoft Excel»;
• овладение специальными техническими умениями конструирования и использования реляционной базы данных на уровне их свободного использования;
• развитие навыков самостоятельной работы и способности применить полученные знания на практике при разработке собственной базы данных.

2. ТЕОРЕТИЧЕСКИЕ ПОЛОЖЕНИЯ

2.1. Общие положения

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

Для учета данных о сотрудниках на предприятиях используют самые разнообразные методы. В одних организациях существуют журналы учета, куда информация вносится вручную, в других применяются классические базы данных для учета кадров, в третьих используются СУБД Access. Но в большинстве случаев на небольших предприятиях учет данных о сотрудниках ведется в электронных таблицах Microsoft Excel.

В предложенном пособии рассматриваются основные возможности, предоставляемые Excel, для работы с базами данных на примере списка «Сотрудники», который содержит информацию о работниках некоторого условного предприятия. Такая база данных удобна по следующим причинам:

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

2.2. Списки Excel как база данных

Приложение Microsoft Excel обладает богатыми встроенными средствами для обработки и анализа данных. Аналогом простой базы данных в Excel служить список.

Список — это группа строк таблицы, содержащая связанные данные.

Отличительной особенностью списка является то, что каждый его столбец содержит однотипные данные, например, перечень фамилий, дату рождения и т. д. (рис. 1).

Если провести аналогию между списком и табличной базой данных, то столбцы списка являются полями базы данных, а его строки — записями. Считается, что первая строка списка является его заголовком и содержит названия столбцов списка. Заголовок должен иметь на листе электронных таблиц горизонтальную ориентацию. Заголовки применяются Excel при составлении отчетов, а также при поиске и организации данных. Шрифт, размер шрифта, выравнивание и другие параметры форматирования, присвоенные заголовкам столбцов списка, должны отличаться от параметров, назначенных для строк данных. В списке не должно быть пустых строк и столбцов.

Рис. 1. Фрагмент списка «Сотрудники»

2.3. Проверка данных при вводе

Если с файлом работает сразу несколько пользователей, желательно контролировать тип вводимой ими информации и свести к минимуму ошибки ввода. В Excel выполнение подобных условий проверяется при помощи средства, которое называется проверкой ввода. Для этого надо:

1. Выделить ячейки столбца, для которого устанавливается проверка ввода.
2. На ленте Данные в группе Работа с данными выбрать команду Проверка данных.
3. На вкладке Параметры в области Условие проверки выбрать Тип данных: Любое значение (используется для отмены проверки ввода), Целое число, Действительное, Список, Дата, Время, Длина текста и Другой (формат, для которого можно задать собственную формулу, например, «м» or «ж»). При выборе значения внизу окна появляются дополнительные поля для ввода условий или ограничений – например, минимального и максимального допустимого значения.
4. На вкладке Сообщение для ввода можно установить флажок Отображать подсказку, если ячейка является текущей и ввести сообщение, чтобы оно появлялось на экране при выделении ячеек.
5. На вкладке Сообщение об ошибке можно установить флажок Выводить сообщение об ошибке, чтобы задать тип сообщения об ошибке, которое появится при вводе в ячейку недопустимого значения.

2.4. Сортировка данных

Команда Сортировка позволяет переставить записи в другом порядке на основании значений одного или нескольких столбцов. Записи сортируются возрастанию/убыванию или по выбранному пользователю порядке (например, по дням недели).

Чтобы отсортировать список надо:

1. Установить курсор в ячейку списка.
2. Выполнить команду Сортировка на ленте Данные в группе Сортировка и Фильтр.
3. В диалоговом окне Сортировка выбрать поле, по которому будет происходить сортировка; тип сортировки (по значению, цвет ячейки, цвет шрифта, значок ячейки) и порядок (по возрастанию, убыванию, настраиваемый).

Рис. 2. Создание пользовательского порядка сортировки

Примечание. Выбор настраиваемого порядка позволяет задать нестандартный порядок сортировки. Для этого надо в диалоговом окне Списки (рис. 2) выбрать НОВЫЙ СПИСОК, в поле Элементы списка ввести значения, образующие пользовательский порядок сортировки (например, АОП, ФЭО, ИВЦ, ИТО, МПО), после чего последовательно выбрать кнопки Добавить и ОК.

2.5. Промежуточные итоги в БД

Для организации списков используют команду Промежуточные итоги на ленте Данные в группе Структура, которая позволяет:

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

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

Режим структуры, в котором оказывается список после выполнения команды Итоги, позволяет просматривать различные части списка с помощью кнопок, расположенных на левом поле (рис. 3).

Рис. 3. Просмотр списка в режиме структуры

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

Чтобы удалить промежуточный и окончательные итоги, надо повторно выполнить команду Промежуточные итоги, а затем щелкнуть по кнопке Убрать все.

2.6. Автофильтр

Отфильтровать список — значит показать только те записи, которые удовлетворяют заданному критерию.

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

Показать все записи по отфильтрованному полю, не убирая фильтр, можно выбрав в списке фильтра критерий Снять фильтр с….

Показать все записи по всем полям, не убирая фильтр, команда Очистить.

Для данных разного типа существуют дополнительные автофильтры, которые находятся в списке критериев Текстовые фильтры, Числовые фильтры, Фильтры по дате и т.д.

Если выделить какое-то числовое поле (например, Возраст), а в списке критериев выбрать Числовые фильтры, то появится список дополнительных фильтров (рис. 4), которые позволяют:

• задать критерий в виде неравенства – критерии равно, не равно, больше, больше или равно, меньше, меньше или равно, между;
• вывести первые N значений – критерий Первые 10: после выбора в списке Числовых фильтров команду Первые 10…, необходимо в появившемся окне указать число значений (N), а также способ вычисления: количество элементов списка, % от количества элементов;
• определить условие по среднему значению в указанном столбце – критерии Выше среднего, Ниже среднего;
• самостоятельно задаваемый фильтр – критерий Настраиваемый фильтр.

Рис. 4. Дополнительные числовые фильтры

Настраиваемый фильтр позволяет задать критерии из одного или двух условий.

Простое условие состоит: из имени поля (атрибута); варианта условия (равно, не равно, больше, меньше, больше или равно, меньше или равно; начинается с, не начинается с, заканчивается на или не заканчивается на; содержит, не содержит); слова или числа для сравнения.

Сложное условие состоит из двух простых, соединенных союзами И или ИЛИ.

При написании значений в условиях сравнения в фильтрах можно использовать подстановочные знаки (Таблица 1).

Таблица 1

Подстановочные знаки

2.7. Расширенный фильтр

Расширенный фильтр позволяет сформировать более сложные условия, в том числе состоящие из более, чем двух условий.

Перед вызовом команды Расширенный фильтр, необходимо сформировать критерии. Для удобства лучше формировать критерии на отдельном листе (можно дать ему имя, например, Критерии) и давать критериям имена Кр1, Кр2 и т.д.

Основное правило: если критерии связаны между собой операцией И, то они должны располагаться в одной строке, а если ИЛИ, то в разных.

После формирования критерия, вызывают расширенный фильтр: на ленте Данные в группе Сортировка и фильтр команда Дополнительно.

Восстановить исходный список можно выбрав на ленте Данные в группе Сортировка и фильтр команду Очистить.

3. ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ

1. Ознакомиться с теоретическими положениями.

2. Получить файл с готовой базой данных у преподавателя или сформировать собственную базу данных для автоматизации любой предметной области человеческой деятельности (учет сотрудников на предприятии, отдел кадров, туристическое агентство, центр недвижимости, гостиница, магазин и т.д.). 

Примечание. Технология создания базы данных конфигурации любой сложности и объема представлена в приложении 3. Заполнить базу данных правдоподобными непротиворечивыми данными.

3. Получить технологические карты (прил. 1).     Технологическая карта – руководство, описывающее алгоритм деятельности по созданию базы данных, — последовательность действий, которые необходимо выполнить, чтобы создать авторский продукт.

4. Используя построенную модель базы данных, выполнить:

• сформировать структуру базы данных;
• сформировать итоги по одному или двум атрибутам;
• построить диаграмму;
• сформулировать и реализовать запросы на отбор данных с помощью автофильтра, при этом использовать простой автофильтр по значению и автофильтр с дополнительными критериями для данных разных типов (числовые, текстовые, дата / время), а также настраиваемый пользовательский автофильтр;
• сформулировать и реализовать запросы на поиск и отбор данных с помощью расширенного фильтра таким образом, чтобы созданные критерии содержали 2-3 условия, относящиеся, как минимум к двум различным полям, и среди критериев были вычисляемые.

5. Представить преподавателю отчет о проделанной работе в электронном или печатном виде.
Работа рассчитана на 6 академических часов.

4. КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Для каких целей применяются электронные таблицы?
2. Для чего необходимы базы данных?
3. Какие возможности предоставляет программа Microsoft Excel для работы с базами данных?
4. Что такое запись?
5. Что такое поле?
6. Что такое тип данных? Зачем необходимо указывать типы полей?
7. Данные каких типов могут быть записаны в ячейку?
8. Как установить проверку вводимых в список значений?
9. Как записываются абсолютные и относительные адреса ячеек?
10. Что такое сортировка? Как отсортировать список по двум и более ключам?
11. Что такое фильтр?
12. Какие виды фильтров вы знаете?
13. В чем отличие сортировки списка от фильтрации списка?
14. Что такое расширенный фильтр?
15. Как сформировать критерий для расширенного фильтра?

5. СПИСОК РЕКОМЕНДУЕМОЙ ЛИТЕРАТУРЫ

1. Кошелев В. Excel 2007. Эффективное использование. — М.: Бином. Лаборатория знаний, 2008 — 544 стр.
2. Слетова Л. Excel 2007 — М.: «ЭКСМО», 2007 — 336 стр.
3. Сурядный А., Глушаков С. Microsoft Excel 2007: самоучитель, 2-е издание. – М.: АСТ, 2008 — 416 стр.