39049

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

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

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

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

Русский

2013-09-30

1021 KB

246 чел.

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 – Сводные таблицы и диаграммы к базе данных "Аспиранты"


 

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

25313. Свойства нервных центров 39 KB
  Проведение волны возбуждения от одного нейрона к другому через синапс происходит в большинстве нервных клеток химическим путем с помощью медиатора а медиатор содержится лишь в пресинаптической части синапса и отсутствует в постсинаптической мембране. В связи с этим поток нервных импульсов в рефлекторной дуге имеет определенное направление от афферентных нейронов к вставочным и затем к эфферентным мотонейронам или вегетативным нейронам. Суммация возбуждения В ответ на одиночную афферентную волну идущую от рецепторов к нейронам в...
25314. Торможение в центральной нервной системе 28.5 KB
  Сеченовым опыт: у лягушки делали разрез головного мозга на уровне зрительных бугров и удаляли большие полушария после этого измеряли время рефлекса отдергивания задних лапок при погружении их в раствор серной кислоты.раздражение на эту область мозга то время рефлекса резко удлиняется. На основании этого он пришел к заключению что в таламической области мозга у лягушки существуют нервные центры оказывающие тормозяшие влияния на спинномозговые рефлексы. мозга наряду с возбуждающими нейронами существуют и тормозящие аксоны кот.
25315. Строение мышечного волокна 32 KB
  В состав волокна входят его оболочка сарколемма жидкое содержимое саркоплазма ядро митохондрии рибосомы сократительные элементы миофибриллы а также замкнутая система продольных трубочек и цистерн расположенных вдоль миофибрилл и содержащих ионы Са2 саркоплазматический ретикулум. Поверхностная мембрана клетки через равные промежутки образует поперечные трубочки входящие внутрь мышечного волокна по которым внутрь клетки проникает потенциал действия при ее возбуждении. Миофибриллы тонкие волокна содержащие 2 вида...
25316. Физиология спинного мозга 30 KB
  В составе серого вещества спинного мозга человека насчитывают около 13. Из них основную массу 97 представляют промежуточные клетки вставочные или интернейроны которые обеспечивают сложные процессы координации внутри спинного мозга. Среди мотонейронов спинного мозга выделяют крупные альфамотонейроны имелкие гаммамотонейроны.
25317. Значение промежуточного мозга 33 KB
  Она формирует положительные и отрицательные эмоции со всеми двигательными вегетативными и гормональными их компонентами. Электрические раздражения различных участков лимбической системы через вживленные электроды выявили наличие центров удовольствия формирующих положительные эмоции и неудовольствия формирующих отрицательные эмоции. ФИЗИОЛОГИЯ ЭМОЦИЙ Эмоции это выражение реакции возбуждения от фр. Если этой мобилизации оказывается недостаточно для отражения опасности или удовлетворения внутренней потребности вспыхивают стенические...
25318. Ретикулярная формация ствола мозга 40 KB
  Дейтерс впервые описавший ее строение во второй половине прошлого столетия назвал ее сетчатой или ретикулярной формацией. Близкие по структуре к ретикулярной формации ядра имеются и в таламусе; нервные волокна идущие от них к коре образуют так называемые неспецифические пути. Физиологическое значение ретикулярной формации было выявлено в сравнительно недавнее время путем исследования изменений электрической активности больших полушарий и спинного мозга в опытах с точно локализованным разрушением или раздражением разных участков...
25319. Мозжечок 56.5 KB
  Полушария мозжечка делят па переднюю долю и заднюю долю; последнюю разделяют еще на две части. Филогенетически наиболее молодым образованием мозжечка является передняя часть задней доли новый мозжечок; она достигает максимального развития у человека и высших обезьян. Верхняя поверхность полушарий мозжечка состоит из серого вещества толщиной от 1 до 25 мм называемого корой мозжечка. В белом веществе мозжечка составляющем основную его массу находятся скопления серого вещества ядра мозжечка.
25320. Промежуточный мозг и подкорковые ядра 54 KB
  Функционально все ядра таламуса делят на две большие группы специфические и неспецифические. Специфические ядра таламуса имеют прямые связи с определенными участками коры больших полушарий. Неспецифические же ядра в большинстве случаев передают сигналы в подкорковые ядра от которых импульсы поступают одновременно в разные отделы коры.
25321. Кора больших полушарий головного мозга 27.5 KB
  Ритмы электроэнцефалограммы. Альфаритм это ритмические колебания потенциала почти синусоидальной формы частотой 8 13 в секунду с амплитудой до 50 мкв. Альфаритм отчетливо выражен если испытуемый человек находится в условиях физического и умственного покоя лежа или сидя в удобном кресле с расслабленной мускулатурой и закрытыми глазами при отсутствии внешних раздражений. Многие исследователи считают что существует две области коры в которых альфаритм имеет наибольшую амплитуду и характеризуется большим постоянством: одна из них...