39049

Создание баз данных в MS Excel

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

Информатика, кибернетика и программирование

Писаренко MS Excel Создание баз данных в MS Excel Создание баз данных средствами MS Excel Для создания баз данных БД и работы с ними обычно используются специализированные достаточно сложные программные системы называемые системами управления базами данных СУБД такие как Orcle MS ccess Fox Pro Dbse MS ccess и др. Табличный процессор MS Excel не является специализированным программным средством для...

Русский

2013-09-30

1021 KB

239 чел.

5

PAGE  2

Э.В. Писаренко  MS Excel                                                               Создание баз данных в MS Excel

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

Для создания баз данных (БД) и работы с ними обычно используются  специализированные, достаточно сложные программные системы, называемые системами управления базами данных (СУБД), такие как Oracle, MS Access, Fox Pro, Dbase, MS Access и др.

Табличный процессор MS Excel не является специализированным программным средством для разработки баз данных, в том понимании их, которое принято в области информационных технологий, но имеет достаточно простые и удобные средства для создания таблиц, во многом похожих на таблицы баз данных СУБД и средства для работы с этими таблицами. Это позволяет получить некоторые, начальные представления о базах данных, как об основных объектах информационно-поисковых систем, и получить некоторые практические навыки создания их и работы с ними, не получив ещё знаний по созданию и работе с указанными выше СУБД.    

Базы данных в терминологии MS Excel называют "списками".

Список – это обычная многоколоночная таблица MS Excel (рис. 1), каждая строка которой представляет собой запись сведений о некотором информационном объекте, включённом в этот список (таблицу). Каждая запись состоит из полей – клеток таблицы, в которых содержатся данные - значения определённых свойств объекта (в виде чисел, дат, периодов времени и т.п.). Названия свойств (характеристик, атрибутов) объекта, указываются в колонках шапки таблицы.

Каждую такую таблицу, далее будем использовать термин таблица база данных (ТБД), удобно размещать на отдельном листе MS Excel. В первой строке листа размещают шапку таблицы (рис. 1), в остальных строках таблицы размещают записи значений характеристик объектов. Тогда текст, размещенный в шапке колонки таблицы, будет соответствовать названию (имени, идентификатору)  конкретной характеристики объектов, записи о которых есть в этой таблице, а в клетке, расположенной на пересечении некоторой строки и колонки таблицы, будет находиться данное - значение этой характеристики.

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

Таким образом, База данных, разработанная и эксплуатируемая в среде MS Excel, будет состоять из:       - одной многоколоночной таблицы базы данных (ТБД);

- средств ввода и редактирования новых записей в ТБД (входных форм);

- средств и способов отображения данных, полученных в ответ на запрос к БД (выходных форм);

- средств манипулирования данными в ТБД (поиска, удаления, редактирования, сортировки и т.п.).   

В следующих разделах учебного пособия рассматривается порядок разработки таблицы базы данных "Аспиранты" (рис. 1) и выполнения типичных операций с записями в ней.

1. Разработка таблицы базы данных "Аспиранты"

Вначале, на рабочем листе MS Excel создадим таблицу базу данных «Аспиранты».

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

Лист 1, Переименовать: ТБД Аспиранты.  

Разрабатываем таблицу в следующей последовательности.

  1.  Выделяем все клетки колонок с А по M (выделяем левой клавишей мышки ярлычки,  расположенные сверху колонок с A по M) и устанавливаем общие для них настройки:

Главная, Формат, Формат ячеек, Число: Текстовый, Выравнивание, по горизонтали: по центру, по вертикали: по верхнему краю, Отображение: переносить по словам, Шрифт: Arial cyr, Размер: 10, Начертание: обычный, Граница: внешние, внутренние, Тип линии: тонкая сплошная линия, Ok.

  1.  Выделяем колонку A и устанавливаем для неё дополнительные настройки:

Главная, Формат, Формат ячеек, Число: Числовой, Число десятичных знаков: 0, Ok.

  1.  Выделяем колонку K и устанавливаем для неё дополнительные настройки:

Главная, Формат, Формат ячеек, Число, Числовой, Число десятичных знаков: 2, Ok.

  1.  Выделяем колонки G,L и M и подстраиваем их форматы:

Главная, Формат, Формат ячеек, Число:  Дата, Тип: 14.03.01 (или 14/03/01), Ok.

  1.  Выделяем клетки для размещения шапки создаваемой таблицы (все клетки в строке A1:M1) и подстраиваем их форматы

Формат ячеек, Шрифт: Arial cyr, Размер: 10, Начертание: полужирный, Граница: Все, внешние, Тип линии: сплошная линия двойной толщины.

  1.  Вводим в таблицу данные, указанные на рис. 1 (рекомендуется осуществлять ввод записей в таблицу базы данных, используя для этого форму ввода данных вызвав её на экран кнопкой Форма (см. раздел 3.2).

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

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

Присвоим имя База_данных всему диапазону клеток в колонках A:M, в строках которых будут размещаться записи базы данных Аспиранты. Для этого проделаем следующее.

  1.  Выделим мышкой диапазон колонок A:M.
  2.  Выполним команду Формулы, Определённые имена, Присвоить имя, Имя: База_данных, Диапазон: БДАспиранты!$A:$M, Ok.

Присвоим имя Форма_обучения колонке С.

1.  Выделим мышкой колонку С.

2. Выполним команду Формулы, Определённые имена, Присвоить имя, Имя: Форма_обучения, Диапазон: БДАспиранты!$С:$С, Ok.

Аналогичным способом присвоим имена остальным колонкам базы данных (рис. 1).

Таблица 1 – Таблица имен в базе данных "Аспиранты"

3. Основные операции с записями в базе данных

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

К этим операциям относятся:

  1.  ввод новых записей в БД;
  2.  удаление записей из БД;
  3.  корректировка существующих записей в БД;
  4.  поиск записей в БД по заданному условию;
  5.  сортировка (упорядочивание расположения записей в определённом порядке) БД;
  6.  получение сводных таблиц и представление их в виде диаграмм и др.

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

3.1. Сортировка (упорядочивание расположения) записей в базе данных

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

Для выполнения указанной операции щелкнем мышкой в любой клетке в пределах таблицы Базы данных "Аспиранты" (рис. 1) и выполним следующую команду:

Данные, Сортировка, Столбец, Сортировать по: Форма обучения, Сортировка: Значения, Порядок: От А до Я, Добавить уровень, Затем по: Дата окончания аспирантуры, Сортировка: Значения, Порядок: От старых к новым, Добавить уровень, Затем по: Ф.И.О. аспиранта, Сортировка: Значения, Порядок: От А до Я, Ok.

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

3.2. Работа с записями в БД с использованием Формы данных

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

Кнопка Форма обычно заранее не установлена на панели быстрого доступа. Её необходимо установить следующим образом:

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

 Настройка панели быстрого доступа, Выбрать команды из:, Часто используемые команды: Все, Форма.

Затем указываем, где расположить панель быстрого доступа: Настройка панели быстрого доступа, Разместить панель быстрого доступа над лентой основного меню и инструментов.

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

Для этого выполним команду

Форма, Критерии: Ф.И.О. аспиранта: Баламут, Enter.

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

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

Нажав кнопку Добавить можно увидеть форму с пустыми полями.

Введите в поля формы данные новой записи и снова нажмите кнопку Добавить. База дополнится новой записью и появится новая форма с пустыми полями. Тем самым MS Excel приготовится к приёму следующей записи в таблицу базы данных.

  

3.3. Поиск и просмотр записей с помощью Пользовательского фильтра

В MS Excel имеется ещё одно средство для работы с записями базы данных – Пользовательский фильтр.

С помощью Фильтра удобно находить и просматривать отдельные группы записей непосредственно в таблице.

Пример 1. Допустим, нас интересует запись с данными об аспирантке Мамлеевой Светланы Изельевны.

  1.  Кликнем в любой клетке в пределах таблицы БД (рис. 1).
  2.  Далее  выполняем следующие команды:

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

  1.  Нажимаем кнопку со стрелочкой в клетке Ф.И.О. аспиранта и выполняем

Выделить все, Мамлеева, Ok.

Под шапкой таблицы остается только одна запись с данными об этой аспирантке.

  1.  Поиск записи  в БД может быть выполнен в соответствии с более сложным критерием.

Для этого в меню кнопки Автофильтр нужно выбрать опцию Текстовые (Числовые,…) фильтры и в диалоговом окне опции задать соответствующий критерий поиска записи.

Для восстановления на экране всей таблицы нужно выполнить команду

Данные, Сортировка и фильтр, Фильтр.

Пример 2.

Допустим, нас интересуют записи об аспирантах, зачисленных в период между 01.01.1997 и 31.12.2000 годами.

Выполняем последовательно

Данные, Сортировка и фильтр, Фильтр, Дата зачисления, Фильтр по дате, Между, после или равно: 03.11.1997 И  до или равно: 02.11.1998, Ok .

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

Далее, допустим, в этой группе нас интересуют только очные аспиранты.

Выполняем

Форма обучения, Текстовые фильтры и выбираем Очная. На экране останутся записи только об очных аспирантах, поступивших в аспирантуру в период с 03.11.1997 по 02.11.1998 г.

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

3.4. Анализ данных в БД с помощью сводных таблиц

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

Рассмотрим примеры создания сводных таблиц (рис.2).

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

Построение сводной таблицы начинаем c того, что щелкаем мышкой в любой клетке таблицы - базы данных “Аспиранты” (рис. 1).

Далее выполняем следующую последовательность команд из меню Вставка.

1-й шаг.  

Вставка, Сводная таблица, Таблица и диапазон: БдАспиранты!$A:$M,  На новый лист, Ok.

2-й шаг.

В появившемся на экране окне Макета сводных таблиц выделяем мышкой в списке полей сводной таблицы поле “Форма обучения” и размещаем это поле в области “Название строк” макета сводной таблицы. Перетаскиваем кнопку поля “Ф.И.О. аспиранта” в область “Значения”.  

В результате этих действий на новом листе MS Excel появится сводная таблица (таблица 1, рис. 2) с информацией о том, сколько всего аспирантов учатся по очной и заочной формам обучения.

Переименуем лист MS Excel с созданной  таблицей: СводныеТаблицы.

В дальнейшем, перед печатью сводной  таблицы  на принтере,   таблицу можно подкорректировать - изменить заголовок, изменить ширину колонок, выбрать другие шрифты. Например, выделим полученную сводную таблицу 1 и установим следующие настройки:

Формат ячеек, Число, Выравнивание: По горизонтали: по центру, По вертикали: по верхнему краю, Отображение: переносит по словам.

Щелкните мышкой в клетке A1 и с помощью команды Вставка, Строки сдвиньте Сводную таблицу на 2-е строки вниз. Затем введите в клетку А2 текст: Таблица 1.

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

Пример 2. Создадим сводную таблицу 2 для получения  итоговых данных по национальностям и формам обучения  аспирантов. Таблицу 2 создадим на том же листе, что и таблица 1, созданная в предыдущем примере.

Щелкаем мышкой в клетке на листе, на котором размещена таблица базы данных "Аспиранты" (рис.1).

Далее выполняем следующую последовательность команд из меню Вставка.

1-й шаг.  

Вставка, Сводная таблица, Таблица и диапазон: БдАспиранты!$A:$M, На существующий лист, Диапазон: Сводные таблицы! D3, Ok.

2-й шаг.

В появившемся на экране окне Макета сводных таблиц перетаскиваем мышкой поле “Форма обучения” в область “Названия столбцов”. Перетаскиваем  поле “Ф.И.О. аспиранта” в область “Значения” и затем перетаскиваем поле “Национальность аспиранта” в область “Названия строк”.   

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

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

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

Отдельные базы данных следует создавать в отдельных книгах (файлах) MS Excel.

3.5 Создание графиков и диаграмм по данным в сводных таблицах

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

 В качестве примера постройте круговую  диаграмму на основе сводных данных в таблице 1 (рис. 2), отражающую соотношение количества очных и заочных аспирантов.

3.6 Обновление данных в сводных таблицах

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

1. Удалим в базе данных “Аспиранты” с помощью команды Правка, Удалить основного или  Удалить контекстного меню запись под номером 4.

2. Кликнем в любой клетке в области Сводной таблицы 1или в таблице базы данных и выполним команду

Данные, Обновить, Обновить все.   

Сведения в Сводной таблице и на диаграмме к  этой таблице изменятся в соответствии с изменениями в таблице базы данных.

Аналогичным образом обновим данные в Сводной таблице 2 и Сводной таблице 3.

3.7  Вставка примечаний

Каждую клетку  таблицы MS Excel можно снабдить примечанием, содержащим  дополнительную информацию к содержанию в  клетке таблицы.

Пример 1. Снабдим, для примера, клетку D2 (рис.1), в которой записана фамилия первого аспиранта, примечанием следующего содержания: c 05.01.99 находится в академическом отпуске в связи с болезнью.  Для этого выполним следующие действия:

  1.   Выделим мышкой  клетку D2.
  2.   Выполним команду: Рецензирование, Создать примечание.
  3.   В появившееся прямоугольное поле введём примечание и щелкнем мышкой в любом месте рабочего листа. В верхнем правом углу клетки D2 появится красный треугольник, который будет напоминать о том, что клетка снабжена примечанием. Размеры поля с текстом примечания можно менять с помощью элементов выделения по краям поля.

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

3.8 Фиксация на экране шапки таблицы Базы данных

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

Рассмотрим, как  это делается на примере фиксации верхней шапки базы данных “Аспиранты” (рис. 1).

Выделим мышкой 2-ю строку таблицы “Аспиранты”, т.е. строку с первой записью в базе данных.

Выполним команду Вид, Закрепить области.

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

Рисунок 2 – Сводные таблицы и диаграммы к базе данных "Аспиранты"


 

А также другие работы, которые могут Вас заинтересовать

13727. Тест биология. Вариант 4 ДПА 507.42 KB
  1 ВАРИАНТ 4 Первая часть Назовите часть покрытосеменного растения содержащую точку роста. А листовая пластинка Б пестик В чашелистик Г кончик корня Назовите органеллу растительной клетки в которой накапливаются п
13728. Тест биология. Вариант 5 ДПА 145.03 KB
  ВАРИАНТ5 Первая часть Назовите травянистое растение с сидячими листьями. А мята Б бархатцы В кукуруза Г огурец Назовите способ размножения плауна булавовидного. А спорами или семенами Б плодами В семенами Г спорами Назовите тип...
13729. Тест биология. Вариант 6 ДПА 657.1 KB
  ВАРИАНТ 6 Первая часть Назовите тип стебля характерный для пшеницы. А вьющийся Б цепкий В соломина Г ползучий Укажите представителя отдела Голосеменные. А сфагнум остролистый Б кедр сибирский В хвощ полевой Г щитник му...
13730. Тест биология. Вариант 7 ДПА 1.74 MB
  ВАРИАНТ 7 Первая часть 1 Назовите нитчатую зеленую водоросль. А саргассум Б хлорелла В ламинария Г спирогира 2. Назовите подземное видоизменение вегетативного органа образованного путем утолщения дополнительного корня. А микориза Б клубнекор
13731. Тест биология. Вариант 8 ДПА 395.69 KB
  ВАРИАНТ 8 Первая часть Назовите структуру расположенную в пазухе листа древесного растения. А дополнительный корень Б боковая почка В камбий Г соцветие Назовите клеточную структуру хламидомонады отсутствующую в клетках высших растений. А клет
13732. Тест биология. Вариант 9 ДПА 1.99 MB
  ВАРИАНТ 9 1 Назовите внешний слой коры стебля древесного растения А пробка Бкамбий В кожица Г луб 2. Укажите споровое растение А щитник мужской Б сосна обыкновенная В горох посевной Г паслен черный 3. Укажите насекомых которые являются переносчиками...
13733. История отечества. Тест (9-11 классы) 69 KB
  Инструкция по выполнению работы Часть 1 состоит из 65 заданий. К каждому заданию дается 4 варианта ответа только один из которых верный. Часть 2 состоит из 10 заданий. При их выполнении требуется записать развернутый ответ на специальном бланке для записи ответ
13734. История отечества. Тест. Вариант 1 97.5 KB
  Вариант №1 Часть 1 А1. В каком веке была создана Русская Правда – свод законов Древнерусского государства 1 IX в. 2 X в. 3XI в. 4XII в. А2. Кто из названных лиц благословил войско Дмитрия Донского
13735. История отечества. Тест. Вариант 2 124 KB
  Вариант №2 Часть 1 А1. Какое из названных событий произошло в XI в. 1 принятие христианства на Руси 2 создание Русской Правды 3 первое летописное упоминание Москвы 4 создание Повести временны