Какие существуют ограничения накладываемые на структуру базы данных в excel

Обновлено: 04.07.2024

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

строка списка – запись базы данных;
столбец списка – поле базы данных.

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

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

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

Для размещения имени поля списка в одной ячейке (рис.5.1 рис. 5.1) необходимо:

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

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

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

Excel предоставляет возможности для работы с базами данных различных форматов, которые при открытии в среде Excel автоматически преобразуются в список. Такое преобразование называют импортом. Данные в электронную таблицу можно включить не только путем импорта из "чужой" базы данных, но и посредством запросов данных, адресованных тому или иному серверу баз данных. Такие запросы формируются специальной программой MS Query , вызываемой по команде Данные, Внешние данные. Создать запрос. Результат запроса возвращается в электронную таблицу в виде списка.

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

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

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

В среде Excel предусмотрены три уровня сортировки, которые определяются в диалоговом окне Сортировка диапазона (рис. 5.2 рис. 5.2, а) параметром Сортировать по.

Рис. 5.2. Диалоговые окна для операции сортировки: а – сортировка диапазона; б – параметры сортировки

Сначала осуществляется сортировка в столбце 1-го уровня, затем сортируются одинаковые записи 1-го столбца по столбцу 2-го уровня, затем сортируются одинаковые записи 2-го столбца по столбцу 3-го уровня.

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

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

Кнопка <Параметры> выводит диалоговое окно "Параметры сортировки", в котором задаются дополнительные установки сортировки (рис. 5.2 б рис. 5.2): с учетом регистра или без учета; по столбцам или по строкам; порядок сортировки – обычный или специальный, выбранный из предлагаемого списка. Этот список можно сформировать самостоятельно с помощью Сервис, Параметры, вкладка Списки.

Установить курсор в области списка
Выполнить команду Данные, Сортировка
Указать порядок и направление сортировки для каждого ключа сортировки
Нажать кнопку <Параметры> и выбрать параметры сортировки (порядок по первому ключу, учет регистра, направление сортировки – по строкам или по столбцам)

Выполнить команду Сервис, Параметры, вкладка Списки
Нажать кнопку <Добавить>
Сформировать элементы списка

Выполнить команду Сервис, Параметры, вкладка Списки
Выделить в окне Списки начало редактируемого списка
Перейти к элементам списка и отредактировать их (добавить, удалить, отредактировать)
Нажать кнопку <ОК>

Выполнить команду Сервис, Параметры, вкладка Списки
Выделить в окне Списки начало редактируемого списка
Нажать кнопку <Удалить>

Фильтрация данных в списке – это выбор данных по заданному критерию (условию). Осуществляется эта операция с помощью команды Данные > Фильтр.

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

Автофильтрация

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

По отдельному столбцу в списке критериев отбора предусматриваются следующие варианты:

все – выбираются все записи без ограничений;
первые 10 – данный пункт позволяет во вновь появляющемся диалоговом окне "Наложение условия по списку" (рис. 5.4 рис. 5.4) выбрать определенное количество наибольших или наименьших элементов списка, которые необходимо отобразить;
значения – будут выбраны только те записи, которые в данном столбце содержат указанное значение;
условие – выбираются записи по формируемому пользователем условию в диалоговом окне "Пользовательский фильтр" (рис. 5.5 рис. 5.5).

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

Каждая часть условия включает:

оператор отношения: = (равно), <> (не равно), > (больше), >= (больше или равно), < (меньше), <= (меньше или равно), начинается с, содержит и т.п.;
значение, которое может выбираться из списка или содержать шаблонные символы *.

Пример. Для Кода предмета можно сформировать условия:

>=п* – отобрать все записи, которые содержат код предмета, начинающийся с буквы п;
>= п1 И <=п2 – отобрать все записи, которые содержат коды предметов п1 и п2;
<>п1 – отобрать все записи, которые не содержат кода предмета п1.

Рис. 5.4. Диалоговое окно "Наложение условия по списку"
Рис. 5.5. Диалоговое окно "Пользовательский фильтр"

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

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

Команда Данные, Фильтр, Расширенный фильтр обеспечивает использование двух типов критериев для фильтрации записей списка:

критерий сравнения;
вычисляемый критерий.

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

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

Технология использования расширенного фильтра состоит из двух этапов:

этап 1 – формирование области критериев поиска;
этап 2 – фильтрация записей списка.

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

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

Критерий сравнения формируется при соблюдении следующих требований:

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

Правила формирования множественного критерия:

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

Пример1. Условие выбора записей о сдаче экзаменов студентами группы 133 по предмету п1 на оценки 4 или 5 можно записать несколькими способами:

1-й способ. Множественный критерий сравнения- все условия находятся в одной строке, связка И. Номер группы, код предмета заданы как точные значения, оценка- оператор сравнения со значением константы.

2-й способ. Множественный критерий сравнения – все условия (точные значения полей) находятся в одной строке, столбец Оценка используется дважды, связка И.

3-й способ. Множественный критерий сравнения – условия (точные значения полей) записаны в двух строках, связка ИЛИ.

Вычисляемый критерий представляет собой формулу, записанную в строке области условий, которая возвращает логическое значение ИСТИНА или ЛОЖЬ.

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

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

Пример2. Выбрать записи о сдаче экзаменов студентами группы 133 с оценкой ниже общего среднего балла или записи с оценкой 5:

В области критериев присутствуют столбцы с заголовками: Номер группы, Оценка1.

Последовательность действий по созданию данного вычисляемого критерия:

присвоить отличное от имен полей списка новое имя столбцу, куда будет введен вычисляемый критерий;
установить курсор в ячейку ввода;
вызвать Мастер функций - команда Вставка, Функция, выбор категории - Логические и выбор функции - ИЛИ;
ввод параметров функции ИЛИ:

Логическое1: G2<=CP3HAЧ($G$2:$G$17) (при вводе формулы использовать курсорный указатель на ячейки таблицы, вызов встроенной функции СРЗНАЧ, указание на абсолютные ссылки с помощью клавиши <F4>)

После завершения ввода вычисляемого критерия в ячейке должна появиться логическая константа ИСТИНА или ЛОЖЬ – результат применения сформированного вычисляемого критерия по отношению к первой записи списка; формулу критерия можно просмотреть лишь в строке формул. Этот же критерий можно было записать по-другому:

либо в комбинированном виде:

Этап 2. Фильтрация записей расширенным фильтром

После подготовки области критерия курсор устанавливается в список и выполняется команда Данные, Фильтр, Расширенный фильтр (рис. 5. рис. 5.6).

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

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

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

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

Фильтрация с помощью формы данных

Excel позволяет работать с отдельными записями списка с помощью экранной формы (рис. 5.7 рис. 5.7).

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

Добавление кнопки "Форма" на панель быстрого доступа

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

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

В списке выберите кнопку Форма Кнопка "Форма" и нажмите кнопку Добавить.

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

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

При повторном нажатии кнопки <Добавить> сформированная запись добавляется в конец списка. Для удаления текущей записи нажимается кнопка <Удалить>. Удаленные записи не могут быть восстановлены, при их удалении происходит сдвиг всех остальных записей списка.

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

Задание 1. Заполните таблицу, содержащую информацию о планетах Солнечной системы согласно рисунку 8. 1 и сохраните ее под именем раб_ 6.xls.

Единицы измерения, используемые в таблице:

период обращения по орбите - в земных годах;

среднее растояние от Солнца - в млн. км;

экваториальный диаметр - в тыс. км;

масса - х10 24 кг.

Основные понятия баз данных

Область таблицы А2:F12 можно рассматривать как базу данных. Столбцы А, В, C, D, E, F этой таблицы называются полями, а строки с 3 по 12 - записями. Область A2:F2 содержит имена полей.

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

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

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

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

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

Фильтрация данных

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

П
ри использовании Автофильтра необходимо переместить курсор в область, содержащую базу данных, или выделить ее. Затем нужно выполнить команды Данные, Фильтр, Автофильтр. На именах полей появятся кнопки с изображением стрелок вниз. Нажимая на кнопки, можно задавать критерии фильтрации. В появляющемся подменю пункт Условия вызывает диалоговое окно, в котором можно установить параметры фильтрации (см. рис. 8.2). Для одного поля могут быть заданы два условия одновременно, связанные логическим И или ИЛИ.

Задание 2. С использоованием Автофильтра осуществить поиск планет, начинающихся на букву С или букву Ю с массой менее 600х10 24 кг.

2.1. Выполните команды Данные, Фильтр, Автофильтр.

На полях появились кнопки.

2.2. Нажмите на кнопку в поле Планета. Выберите пункт Условие.

2.3. В диалоговом окне задайте критерии согласно рис. 8. 2. и нажмите на кнопку ОК.

Проверте! В базе данных остались планеты Солнце, Юпитер, Сатурн.

2.4. Нажмите на кнопку на поле Масса. Выберите пункт Условие.

2.5. В диалоговом окне задайте критерий: Масса <600.

Проверте! Остался только Сатурн.

2.6. Выполните команды меню Данные, Фильтр, Все.

Задание 3. С использованием Автофильтра самостоятельно:

Осуществите поиск планеты, имеющтх экваториальный диаметр менее 50 тыс. Км. И массу менее 4х10 24 (ответ:Меркурий, Марс, Плутон);

Осуществите поиск планет, находящихся от Солнца на расстоянии не менее 100 млн.км, имеющих массу в диапазоне от 3х10 24 до 500х10 24 кг, а так же не долее 2 спутников (ответ:Венера, Земля, Нептун).

При использовании Расширенного фильтра необходимо сначала определить (создать) три области (см. рис. 8. 3.):

Интервал списка - область базы данных (А2: F 12);

Интервал критериев - область, где задаются критерии фильтрации (А14: F 15);

Интервал извлечения-область,в которой будут появляться результаты фильтрации (А17: F 21).

И
мена полей во всех интервалах должны точно совпадать.

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

Если необходимо получать результаты фильтрации в интервале извлечения, нужно выбрать Копировать на другое место (см.. рис. 8. 4.)

Задание 4. С использованием Расширенного фильтра осуществить поиск планет с периодом обращения более 10 земных лет и количеством спутников не менее 2.

4.1. Создайте интервал критерии поиска в интервал критериев.

4.3. Поместите курсор в область базы данных.

4.4. Выполните команды Данные, Фильтр, Расширенный фильтр.

4.5. Установите Копировать на другое место.

Проверте! Должны быть найдены планеты Юпитер, Сатурн, Уран, Нептун.

Задание 5. Сохраните результаты в файле planeta .. xls ..

Задание 6. Распечатайте результаты работы, предварительно введя в строку 13 заголовок «Критерии поиска», а в строку 16 заголовок «Результаты поиска».

Задание 7 . С использованием Расширенного фильтра самостоятельно:

Найдите планеты, имеющие период обращения более 2 световых лет и экваториальный диаметр более 50 тыс. Км (ответ: Уран, Плутон );

Осуществите поиск планет, находящихся от Солнца на расстоянии более 1000 млн.км. а так же имеющих более 1 спутника (ответ: Сатурн, Уран, Нептун).

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

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

Для выполнения сортировки необходимо выделить область базы или поместить в нее курсор, а затем выполнить команды Данные, Сортировка. При этом появится диалоговое окно. Нужно установить Сортировать по и выбрать название поля, по которому нужно производить сортировку (см. рис.8. 5.)

Кроме того, необходимо указать метод сортировки: по возрастанию или по убыванию и нажать кнопку ОК.

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

Задание 8. Отсортируйте данные в таблице в порядке убывания количества спутников.

Задание 9. Отсортируйте данные в таблице в алфавитном порядке названий планет.

Задание 10. Отсортируйте данные в порядке увеличения их расстояния от солнца.

Задание 11. Подведите итоги.

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

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

Использование электронной таблицы как базы данных. Сортировка и фильтрация данных в Microsoft Excel .

Другие материалы по предмету

Министерство образования и науки Украины

Николаевская общеобразовательная школа № 3

по информатике

Использование электронной таблицы как базы данных. Сортировка и фильтрация данных в Microsoft Excel 97

ученица 11-А класса

2002Использование электронной таблицы как базы данных. Сортировка и

фильтрация данных в Microsoft Excel 97

Электронные таблицы Excel 9x часто используют для ведения простейших баз данных Таблица, используемая в качестве базы данных, обычно состоит из нескольких столбцов, являющихся полями базы данных. Каждая строка представляет отдельную запись.

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

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

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

Если данные представлены в таком виде, программа Excel позволяет упростить этот процесс путем сортировки и фильтрации данных.

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

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

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

При фильтрации базы отображаются только записи, обладающие нужными свойствами. Простейшее средство фильтрации автофильтр. Он запускается командой Данные > Фильтр > Автофильтр.

По команде Автофильтр в ячейках, содержащих заголовки полей появляются раскрывающие кнопки. Щелчок на такой кнопке открывает доступ к списку вариантов фильтрации. Варианты фильтрации записываются обычно с помощью простых или составных условий. Составные условия формируются с помощью логических выражений и логических операции И или ИЛИ. Записи, не удовлетворяющие условию фильтрации, не отображаются.

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

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

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

С помощью диалогового окна Расширенный фильтр можно установить критерий сравнения, а также создать критерии И или ИЛИ. Например, можно заказать поиск в базе данных сведений обо всех служащих, идентификационные номера которых меньше числа 650. А с помощью критерия И можно отфильтровать всех служащих, идентификационные номера которых меньше числа 650 и которые работают в бухгалтерии. Используя критерий или, можно найти всех служащих, идентификационные номера которых больше числа 660, либо служащих, которые проработали в компании более 10 лет.

Фильтры позволяют отобразить пять типов критериев

Отобразить все. Отображаются все записи в поле

Расширенный фильтр. Открывается диалоговое окно Расширенный фильтр, позволяющее создать критерии с использованием И либо ИЛИ.

Точные значения. Отображаются только те записи, которые содержат точные значения в заданных полях.

Пустые. Отображаются все записи с пустыми полями.

Непустые. Отображаются все записи с непустыми полями.

При использовании Расширенного фильтра необходимо сначала определить (создать) три области

интервал списка - область базы данных, которая подлежит фильтрации;

интервал критериев - область, где задаются критерии фильтрации;

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

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

Имена полей во всех интервалах должны точно совпадать.

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

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

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

Что такое база данных?

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

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

Создание хранилища данных в Excel

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

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

Горизонтальные строки в разметке листа «Эксель» принято называть записями, а вертикальные колонки – полями. Можно приступать к работе. Открываем программу и создаем новую книгу. Затем в самую первую строку нужно записать названия полей.

Особенности формата ячеек

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

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

Что такое автоформа в «Эксель» и зачем она требуется?

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

Фиксация «шапки» базы данных

Кроме этого, не нужно забывать о закреплении первой строки. В Excel 2007 это можно совершить следующим образом: перейти на вкладку «Вид», затем выбрать «Закрепить области» и в контекстном меню кликнуть на «Закрепить верхнюю строку». Это требуется, чтобы зафиксировать «шапку» работы. Так как база данных Excel может быть достаточно большой по объему, то при пролистывании вверх-вниз будет теряться главная информация – названия полей, что неудобно для пользователя.

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

Продолжение работы над проектом

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

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

Как создать раскрывающиеся списки?

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

Для того чтобы база данных MS Excel предоставляла возможность выбора данных из раскрывающегося списка, необходимо создать специальную формулу. Для этого нужно присвоить всем сведениям о родителях диапазон значений, имена. Переходим на тот лист, где записаны все данные под названием «Родители» и открываем специальное окно для создания имени. К примеру, в Excel 2007 это можно сделать, кликнув на «Формулы» и нажав «Присвоить имя». В поле имени записываем: ФИО_родителя_выбор. Но что написать в поле диапазона значений? Здесь все сложнее.

Диапазон значений в Excel

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

Чтобы получился динамический диапазон, необходимо использовать формулу СМЕЩ. Она, независимо от того, как были заданы аргументы, возвращает ссылку на исходные данные. В выпадающем списке, который получится в итоге, не должно встречаться пустых значений. С этим как раз превосходно справляется динамический диапазон. Он задается двумя координатами ячеек: верхней левой и правой нижней, словно по диагонали. Поэтому нужно обратить внимание на место, откуда начинается ваша таблица, а точнее, на координаты верхней левой ячейки. Пусть табличка начинается в месте А5. Это значение и будет верхней левой ячейкой диапазона. Теперь, когда первый искомый элемент найден, перейдем ко второму.

Нижнюю правую ячейку определяют такие аргументы, как ширина и высота. Значение последней пусть будет равно 1, а первую вычислит формула СЧЁТ3(Родители!$B$5:$I$5).

Итак, в поле диапазона записываем =СМЕЩ(Родители!$A$5;0;0;СЧЁТЗ(Родители!$A:$A)-1;1). Нажимаем клавишу ОК. Во всех последующих диапазонах букву A меняем на B, C и т. д.

Работа с базой данных в Excel почти завершена. Возвращаемся на первый лист и создаем раскрывающиеся списки на соответствующих ячейках. Для этого кликаем на пустой ячейке (например B3), расположенной под полем «ФИО родителей». Туда будет вводиться информация. В окне «Проверка вводимых значений» во вкладке под названием «Параметры» записываем в «Источник» =ФИО_родителя_выбор. В меню «Тип данных» указываем «Список».

Аналогично поступаем с остальными полями, меняя название источника на соответствующее данным ячейкам. Работа над выпадающими списками почти завершена. Затем выделяем третью ячейку и «протягиваем» ее через всю таблицу. База данных в Excel почти готова!

Внешний вид базы данных

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

Как перенести базу данных из Excel в Access

Не только лишь Excel может сделать базу данных. Microsoft выпустила еще один продукт, который великолепно управляется с этим непростым делом. Название ему – Access. Так как эта программа более адаптирована под создание базы данных, чем Excel, то и работа в ней будет более быстрой и удобной.

Но как же сделать так, чтобы получилась база данных Access? Excel учитывает такое желание пользователя. Это можно сделать несколькими способами:

Читайте также: