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

Урок №9

«Работа с таблицей Excel как с базой данных»

С помощью Microsoft Excel можно создавать и обрабатывать базы данных. База данных в Microsoft Excel – таблица, состоящая из однотипных записей (строк). Столбцы таблицы являются полями записи в базе данных. Под имена полей выделяется первая строка базы данных.

Список представляет собой электронную таблицу с большим объемом взаимосвязанной информации (список товаров на складах или список номеров телефонов и адресов абонентов).

Список — это набор строк электронной таблицы со взаимосвязанными однотипными данными постоянного формата. Другими словами список — это плоская база данных, а строки и столбцы списка соответствуют записям и полям в базе данных. Образец списка представлен на рисунке

К спискам в Excel предъявляются более строгие требования, чем к обычным электронным таблицам. Количество столбцов в списке должно быть постоянным, а количество строк переменным. Это позволяет добавлять, удалять или переставлять строки таблицы или записи списка (базы данных).

К средствам, которые предназначены для обработки и анализа данных в списке относятся команды из меню Данные: Сортировка, Фильтр, Форма, Итоги, Проверка. При выполнении этих команд, редактор автоматически распознает список как базу данных и осуществляет обработку и анализ данных в списке как в базе данных.

При применении команды сортировка можно отсортировать записи по одному или нескольким полям. С помощью фильтров (Автофильтра и Расширенного фильтра) можно быстро найти (отфильтровать) необходимые данные в списках по одному, двум или нескольким параметрам поиска. Командой Итоги можно упорядочить данные в списках с помощью итоговых значений.

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

При создании списка необходимо выполнить определенные требования:

• чтобы редактор автоматически распознавал список как базу данных и обрабатывал данные при выполнении команд обработки необходимо на рабочем листе располагать один список;

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

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

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

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

Рассмотрим пример.

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

Рисунок 1.

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

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

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

3. таблицу следует отделить от других данных рабочего листа пустой строкой и столбцом;

4. информация по полям должна быть однородной, т.е. только числа или только текст.

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

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

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

Рисунок 2.

В поле списка Сортировать по (рис.2) выбирается поле, по которому будут отсортированы данные, и тип сортировки:

При сортировке по возрастанию используется следующий порядок:

Числа сортируются от наименьшего отрицательного до наибольшего положительного числа.

При сортировке алфавитно-цифрового текста сравниваются значения по знакам слева направо. Например, если ячейка содержит текст «Дом100», она будет поставлена после ячейки, содержащей запись «Дом1», и перед ячейкой, содержащей запись «Дом11».

Текст, в том числе содержащий числа, сортируется в следующем порядке:

0 1 2 3 4 5 6 7 8 9 (пробел) ! » # $% ( ) *,. /:; ? @ [ ] ^ _ ` { | } ~ + A B C D E F G H I J K L M N O P Q R S T U V W X Y Z А Б В Г Д Е Ё Ж З И Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я.

Апострофы (‘) и дефисы (-) игнорируются с единственным исключением: если две строки текста одинаковы, не считая дефиса, текст с дефисом ставится в конец.

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

По убыванию – сортировка в обратном порядке.

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

Результат сортировки вы видите на рисунке 3.

Рисунок 3.

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

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

Обработка базы данных.

При просмотре, изменении, добавлении и удалении записи в базе данных, а также при поиске записей по определенному критерию удобно использовать формы данных. При обращении к команде Форма меню Данные Microsoft Excel считывает данные и создает диалоговое окно формы данных (рис.4).

Рисунок 4.

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

Для использования форм данных таблица должна иметь имена столбцов.

Имена столбцов становятся именами полей в форме данных. Поле соответствует каждому столбцу таблицы.

С помощью полосы прокрутки можно прокручивать записи базы данных.

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

Передвигаться по полям формы можно с помощью мыши и клавиш Tab (вниз), Shift+Tab (вверх).

В правой части окна расположены следующие кнопки.

Добавить – очищает поля для ввода новой записи базы данных. Если снова щелкнуть кнопку Добавить, то введенные данные будут добавлены, как новая запись, в конец базы данных.

Удалить – удаляет выведенную запись, другие записи базы данных сдвигаются. Удаленные записи не могут быть восстановлены.

Вернуть – восстанавливает отредактированные поля в выведенной записи, удаляя сделанные изменения. Чтобы восстановить запись, необходимо нажать Вернуть перед нажатием клавиши Enter или перед переходом к другой записи.

Назад – выводит предыдущую запись в списке. Если был определен критерий с помощью кнопки Критерии, то кнопка Назад выведет предыдущую запись из тех, которые удовлетворяют заданному критерию.

Далее – выводит следующую запись базы данных.

Критерии – очищает поля перед вводом критериев сравнения с операторами сравнения для поиска необходимого подмножества записей.

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

Очистить – удаляет существующий критерий из окна диалога. Доступна только после нажатия кнопки Критерии.

Закрыть – закрывает форму данных.

Для добавления записи к базе данных необходимо:

1. выделить ячейку в таблице, к которой следует добавить запись;

2. в меню Данные выбрать команду Форма;

3. щелкнуть кнопку Добавить;

4. заполнить поля новой записи;

5. для перемещения к следующему полю нажать клавишу Тab;

6. после ввода данных нажать клавишу Enter для добавления записи;

7. после добавления всех необходимых записей, щелкнуть кнопку Закрыть.

Новые записи будут добавлены в конец базы данных.

Поиск данных.

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

1. выделить ячейку в таблице;

2. в меню Данные выбрать команду Форма;

3. щелкнуть кнопку Критерии;

4. в полях редактирования ввести критерии для поиска данных, например, ввести Иванов в поле Фамилия;

5. для вывода на экран первой записи, отвечающей критерию, щелкнуть кнопку Далее;

6. для вывода на экран предыдущей записи, отвечающей критерию, щелкнуть кнопку Назад;

7. для поиска записей в списке по другим критериям щелкнуть кнопку Критерии и ввести новые критерии;

8. по окончанию щелкнуть кнопку Закрыть.

Команда Фильтр меню Данные позволяет отыскивать и использовать нужное подмножество данных в списке. В отфильтрованном списке выводятся на экран только те строки, которые содержат определенное значение или отвечают определенным критериям, при этом другие строки скрываются. Для фильтрации данных используются команды Автофильтр и Расширенный фильтр пункта Фильтр меню Данные.

Команда Автофильтр устанавливает кнопки скрытых списков (кнопки со стрелками) непосредственно в строку с именами столбцов (рис.5).

Рисунок 5.

С их помощью можно выбирать записи базы данных, которые следует вывести на экран. После выделения элемента в открывшемся списке, строки, не содержащие данный элемент, будут скрыты. Например, если в скрытом списке поля Фамилия выбрать Иванов, то будут выведены только записи, у которых в поле Фамилия содержится значение Иванов. (рис. 6)

Рисунок 6.

Если в поле списка выбрать пункт Условие … , то появится окно Пользовательский автофильтр (рис.7).

Рисунок 7.

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

Чтобы вывести все данные таблицы, необходимо вызвать команду Отобразить все или отменить команду Автофильтр меню Данные, подменю Фильтр.

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

Тема: «Создание БД в 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-ю половину месяца.

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

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

Любая база данных (БД) – это сводная таблица с параметрами и информацией. Программа большинства школ предусматривала создание БД в Microsoft Access, но и Excel имеет все возможности для формирования простых баз данных и удобной навигации по ним.

Как сделать базу данных в Excel, чтобы не было удобно не только хранить, но и обрабатывать данные: формировать отчеты, строить графики, диаграммы и т.д.

Пошаговое создание базы данных в Excel

Для начала научимся создавать БД с помощью инструментов Excel. Пусть мы – магазин. Составляем сводную таблицу данных по поставкам различных продуктов от разных поставщиков.

С шапкой определились. Теперь заполняем таблицу. Начинаем с порядкового номера. Чтобы не проставлять цифры вручную, пропишем в ячейках А4 и А5 единицу и двойку, соответственно. Затем выделим их, схватимся за уголок получившегося выделения и продлим вниз на любое количество строк. В небольшом окошечке будет показываться конечная цифра.

Примечание. Данную таблицу можно скачать в конце статьи.

По базе видим, что часть информации будет представляться в текстовом виде (продукт, категория, месяц и т.п.), а часть – в финансовом. Выделим ячейки из шапки с ценой и стоимостью, правой кнопкой мыши вызовем контекстное меню и выберем ФОРМАТ ЯЧЕЕК.

Появится окно, где мы выберем формат – финансовый. Число десятичных знаков поставим 1. Обозначение выбирать не будем, т.к. в шапке у нас уже указано, что цена и стоимость в рублях.

Аналогично поступаем с ячейками, куда будет вписываться количество. Формат выбираем числовой.

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

Теперь заполняем таблицу данными.

Важно! При заполнении ячеек, нужно придерживаться единого стиля написания. Т.е. если изначально ФИО сотрудника записывается как Петров А.А., то остальные ячейки должны быть заполнены аналогично. Если где-то будет написано иначе, например, Петров Алексей, то работа с БД будет затруднена.

Таблица готова. В реальности она может быть гораздо длиннее. Мы вписали немного позиций для примера. Придадим базе данных более эстетичный вид, сделав рамки. Для этого выделяем всю таблицу и на панели находим параметр ИЗМЕНЕНИЕ ГРАНИЦ.

Аналогично обрамляем шапку толстой внешней границей.



Функции Excel для работы с базой данных

Теперь обратимся к функциям, которые Excel предлагает для работы с БД.

Работа с базами данных в Excel

Пример: нам нужно узнать все товары, которые принимал Петров А.А. Теоретически можно глазами пробежаться по всем строкам, где фигурирует эта фамилия, и скопировать их в отдельную таблицу. Но если наша БД будет состоять из нескольких сотен позиций? На помощь приходит ФИЛЬТР.

Выделяем шапку таблицы и во вкладке ДАННЫЕ нажимаем ФИЛЬТР (CTRL+SHIFT+L).

У каждой ячейки в шапке появляется черная стрелочка на сером фоне, куда можно нажать и отфильтровать данные. Нажимаем ее у параметра ПРИНИМАЛ ТОВАР и снимаем галочку с фамилии КОТОВА.

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

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

Можно произвести дополнительную фильтрацию. Определим, какие крупы принял Петров. Нажмем стрелочку на ячейке КАТЕГОРИЯ ПРОДУКТА и оставим только крупы.

Вернуть полную БД на место легко: нужно только выставить все галочки в соответствующих фильтрах.

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

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

К примеру, мы хотим отсортировать продукты по мере увеличения цены. Т.е. в первой строке будет самый дешевый продукт, в последней – самый дорогой. Выделяем столбец с ценой и на вкладке ГЛАВНАЯ выбираем СОРТИРОВКА И ФИЛЬТР.

Т.к. мы решили, что сверху будет меньшая цена, выбираем ОТ МИНИМАЛЬНОГО К МАКСИМАЛЬНОМУ. Появится еще одно окно, где в качестве предполагаемого действия выберем АВТОМАТИЧЕСКИ РАСШИРИТЬ ВЫДЕЛЕННЫЙ ДИАПАЗОН, чтобы остальные столбцы тоже подстроились под сортировку.

Видим, что данные выстроились по увеличивающейся цене.

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

Сортировка по условию

Нам нужно извлечь из БД товары, которые покупались партиями от 25 кг и более. Для этого на ячейке КОЛ-ВО нажимаем стрелочку фильтра и выбираем следующие параметры.

В появившемся окне напротив условия БОЛЬШЕ ИЛИ РАВНО вписываем цифру 25. Получаем выборку с указанием продукты, которые заказывались партией больше или равной 25 кг. А т.к. мы не убирали сортировку по цене, то эти продукты расположились еще и в порядке ее возрастания.

Промежуточные итоги

И еще одна полезная функция, которая позволит посчитать сумму, произведение, максимальное, минимальное или среднее значение и т.п. в имеющейся БД. Она называется ПРОМЕЖУТОЧНЫЕ ИТОГИ. Отличие ее от обычных команд в том, что она позволяет считать заданную функцию даже при изменении размера таблицы. Чего невожнможно реалиловать в данном случаи с помощью функции =СУММ(). Рассмотрим на примере.

Предварительно придадим нашей БД полный вид. Затем создадим формулу для автосуммы стоимости, записав ее в ячейке F26. Параллельно вспоминаем особенность сортировки БД в Excel: номера строк сохраняются. Поэтому, даже когда мы будем делать фильтрацию, формула все равно будет находиться в ячейке F26.

Функция ПРОМЕЖУТОЧНЫЕ ИТОГИ имеет 30 аргументов. Первый статический: код действия. По умолчанию в Excel сумма закодирована цифрой 9, поэтому ставим ее. Второй и последующие аргументы динамические: это ссылки на диапазоны, по которым подводятся итоги. У нас один диапазон: F4:F24. Получилось 19670 руб.

Теперь попробуем снова отсортировать кол-во, оставив только партии от 25 кг.

Видим, что сумма тоже изменилась.

Скачать пример

Получается, что в Excel тоже можно создавать небольшие БД и легко работать с ними. При больших объемах данных это очень удобно и рационально.

СОДЕРЖАНИЕ

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 стр.

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

по теме: «Microsoft Excel».

Создание базы данных в Excel и работа с ней.

Цели урока:

• знакомство с функциями ЭТ по созданию баз данных и работе с ней;

• закрепление знаний по работе с формулами в ЭТ и соответствующих умений.

Задачи урока:

• обучающие – ввести понятия «база данных», «запись», «поле»; рассмотреть приёмы работы с БД; закрепить работу с формулами;

• развивающие – развитие операционного мышления, понятийного мышления;

• воспитательная – воспитание информационной культуры (поиск информации, обмен между приложениями Windows).

Ход урока:

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

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

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

Что это такое? Запишите в тетради: база данных — это совокупность информации об однотипных объектах, некое хранилище данных.

База данных состоит из записей. Запись — это информация об одном объекте.

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

Карточка-задание 1.

1. В электронной таблице Excel создать базу данных, внести в нее представленные ниже записи.

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

3. Внести информацию о курсе доллара.

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

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

Проанализируем поля нашей базы данных. В процессе работы ярмарки для ее организаторов необходима следующая информация — количество поставленных экземпляров каждого наименования, количество проданных экземпляров, цена каждой книги в долларах и рублях и, наконец, выручка от продажи книг. Обратите внимание на то, что есть столбцы, значения в ячейках которых появятся в результате расчета по формулам (используются относительная и абсолютная ссылки). Базы данных, которые можно представить в виде таблицы (столбцы и строки), называются реляционными. Именно такие удобно создавать в Excel.

Рассмотрим, какие операции можно осуществлять с информацией, хранящейся в базе данных. Записи можно сортировать, удалять, вводить новые. Кроме того, в базе данных можно осуществлять поиск по запросу.

Конечно, выполнять все эти действия с тремя записями, которые вы ввели, выполняя первое задание, по меньшей мере, неинтересно и ненаглядно. Поэтому то, что вы сейчас ввели, нужно будет добавить к уже имеющимся записям. На ваших компьютерах имеется файл Книги.xls, в котором подготовлена необходимая инфор­мация. Вам нужно будет выполнить следующее задание:

1. Найти подготовленный мною файл (С:Книжная ярмарка Книги.хls — путь для поиска файла записывается на доске) Прил.

2. Скопировать записи, самостоятельно созданные вами при выполнении первого задания, в этот файл.

3. Скопировать все формулы.

Карточка-задание 2.

Осуществить сортировку записей в базе данных;

1. Выполнить команду меню Данные, Сортировка.

2. В окне Сортировка диапазона выбрать поле для сортировки Сортировать по), например «Автор».

3. Указать, как следует выполнять сортировку: по возрастанию или по убыванию.

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

Учитель рассказывает о назначении кнопок, имеющихся в окне Форма, подроб­но останавливается на кнопке Критерии и предлагает учащимся выполнить следующее задание:

1. В поле «Автор» ввести букву «С», просмотреть кнопкой Далее.

2. В поле «Цена, $» ввести «

3. В поле «Область знаний» ввести «Программирование», в поле «Цена, $» — «Просмотреть, сколько таких книг.

Карточка-задание 3.

Добавить в базу данных новые записи, используя форму, — внести информацию о книгах, имеющихся в кабинете информатики (3-5 записей).

Карточка-задание 4.

Работа с кнопкой Критерии.

1. Сколько на ярмарке было представлено книг по математике?

2. Сколько было представлено книг стоимостью больше 100 рублей?

3. Сколько всего было представлено книг по делопроизводству?

Работа с таблицей, использование автосуммирования.

1. Каково общее количество книг, представленных на ярмарке?

2. Сколько на ярмарке было продано книг и на какую сумму?

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

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

Карточка-задание 5.

На новом листе ЭТ создать структуру базы данных, обеспечивающей выполнение следующих запросов (т. е. важно правильно задать поля базы):

• Посмотреть список посетителей-студентов.

• Посмотреть список посетителей с высшим образованием

• Посмотреть список посетителей, пришедших по пригласительным билетам.

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

• Посмотреть, какие мероприятия пользовались большей популярностью.

(База данных должна содержать следующие поля: «Ф.И.О.», «Род занятий», «Образование», «Регион», «Место жительства», «Наличие пригласительного биле­та», «Кол-во купленных книг», «Посещаемые мероприятия».)

Итак, вы создали структуру базы данных, но остается открытой задача: как эту информацию собрать для базы данных?

Ребята могут предложить различные варианты решения этой проблемы. Вместе с учениками нужно прийти к выводу, что можно подготовить анкету для участников.

Задание.

Подготовить анкету вида:

Это не что иное, как часть формы:

Чтобы получить такие анкеты от посетителей выставки, необходимо:

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

2. Переименовать лист:

• щелкнуть правой кнопкой мыши на ярлычке «Лист№»;

• выбрать команду Переименовать;

• ввести «Анкета участника ярмарки» (это и будет заголовок формы).

1. Открыть форму с чистыми полями для ввода.

2. Клавишей поместить содержимое экрана в буфер обмена.

3. Загрузить программу Paint (графический редактор).

4. Вставить копию экрана из буфера обмена: команда меню Правка, Вставить

5. Выделить нужный фрагмент формы для анкеты.

6. Поместить полученный рисунок в буфер обмена: команда меню Правка, Копировать.

7. Открыть документ Microsoft Word и вставить рисунок из буфера обмена.

10.Вывести полученную анкету на печать.

При выполнении этого задания происходит закрепление многих умений и навыков: работа с листом ЭТ, осуществление обмена между приложениями (Word, Paint назначение клавиши PrintScreen (все это изучалось при изучении Microsoft Word

Дополнительное задание.

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

Пролож.( С:Книжная ярмарка Книги.хls)

1