Если теперь вы попытаетесь ввести данные в диапазоне, для которого было установлено предельное значение, появится список, в котором вы сможете выбрать между четко определенными значениями.
Создание базы данных в Excel
Давайте рассмотрим создание базы данных в Excel на примере рабочего листа для учета движения денежных средств малого бизнеса. Это моя основная база данных на протяжении 9 лет, и она ежедневно пополняется. Кроме того, я использовал свои базы данных Excel для хранения информации о сотрудниках, для начисления заработной платы и расчета налогов и взносов, для учета результатов инвентаризации и многого другого. Реальные базы данных представляют собой систему связанных таблиц с программным ядром для выбора и отображения информации, в то время как базы данных Excel обычно состоят из одной таблицы — набора данных.
Столбцы в базах данных называются полями, а строки — записями. Напишите названия полей в первой строке пустого рабочего листа в рабочей книге. Если первым полем в вашей записи является дата, лучше всего отформатировать поле соответствующим образом. Выберите первый столбец, в контекстном меню выберите «Формат ячеек…» и установите нужный формат даты. Также было бы неплохо автоматически вводить текущую дату в первое поле каждой новой записи. В столбцах базы данных, созданных с денежными суммами, установите числовой формат с двумя знаками после запятой. Остальные поля могут оставаться в общем формате.
В справке Excel рекомендуется выделять имена полей жирным курсивом, чтобы программа могла распознавать их самостоятельно и отличать от записей. Мы используем только отдельные ячейки для имен полей, а не общие ячейки, поэтому вы можете легко использовать автоматический фильтр для отбора записей и, при желании, автоматическое форматирование. Я никогда не использую автофайл, потому что меня особенно привлекает визуализация в таблице Excel, где можно увидеть весь диапазон записей, а не только одну строку, как в автофайле. Не всегда удобно использовать автофайл для вставки новой записи в базу данных Excel, поскольку заполнение новой строки можно организовать непосредственно в таблице с помощью выпадающих списков, а аналогичные строки можно добавить путем копирования и вставки, впоследствии заменяя элементы, подлежащие изменению.
Как вы видите, для примера я уже ввел в базу данных начальный баланс. Также убедитесь, что вы привязали первый ряд к экрану (в Excel 2010: View — Anchor Ranges — Anchor Top Row). Затем выделите первые три строки таблицы и добавьте границы ячеек. Мы называем этот лист «Кассир».
Создание раскрывающихся списков
Мы создаем списки для выбора значений полей «Торговая точка», «Вид дохода», «Вид расхода», «Получатель/плательщик». В моей базе данных «Получатель/Плательщик» для краткости называется «Юридическое лицо», и между полями «Дата» и «Место продажи» есть поле «Компания», где я выбрал ИП или ООО до 2 квартала 2012 года.
Чтобы создать выпадающие списки, скопируем названия необходимых полей на чистый лист в той же рабочей книге, который назовем «Списки». Под названиями полей напишите необходимые значения в формате списка.
Чтобы иметь возможность добавить эти диапазоны к типу опции в выпадающем списке, им необходимо дать имя. Мы также создадим динамические диапазоны имен, чтобы вам не приходилось менять диапазон в типе выбора данных каждый раз, когда вы добавляете новое значение. Для этого перейдите на лист Списки и откройте окно для создания имени (в Excel 2010: Формулы — Присвоить имя; я создавал свои файлы в Excel 2000, но сейчас не помню структуру меню). Давайте писать: Имя: trade_point_select, Диапазон: =MEMBERSHIP(Lists!$A3500;0;0;ACCOUNT(Lists!$A:$A)-1;1)
Нажмите OK, и первый динамический диапазон будет создан. Сделайте то же самое для остальных рядов:
Имя: type_expenditure_selection Диапазон: =MEMBERSHIP(Lists!$B3500;0;0;RATE(Lists!$B:$B)-1;1)
Название: Expenditure_selection Диапазон типов: =MESH(Lists!$C3500,0,0,ACCOUNT(Lists!$C:$C)-1,1)
Имя: Recipient_pay_select Диапазон: =MESH(Lists!$D3500,0,0,SCREEN(Lists!$D:$D)-1,1)
Когда все строки будут названы списками значений для выбора, перейдите на лист «Оформление заказа» и создайте выпадающие списки в ячейках соответствующих столбцов. Для этого выделите ячейку B3 и откройте окно Input Validation (в Excel 2010 это Data — Data Validation — Data Validation). На вкладке Параметры выберите опцию Тип данных: Список и напишите в поле Источник: =Торговая_точка_выбора.
Для других полей базы данных, где у нас будут выпадающие списки, также выберите Тип данных: Список, в Источник добавьте имя диапазона, соответствующего столбцу:
для «D3»: =Выбор_типа_дохода, для «F3»: =Выбор_типа_расхода, для «G3»: =Выбор_плательщика.
На данном этапе ячейки выпадающего списка были созданы в третьей строке нашей базы данных. Затем выделите третью запись в нашей таблице и скопируйте ее, перетащив на нужное количество строк.
Внешний вид базы данных в Excel
Для внешнего вида базы данных вы можете использовать любые цвета, которые вам нравятся. Я выделяю строку с названиями полей, фон за пределами таблицы и ячейки, в которые вводятся значения из выпадающих списков.
Когда у вас будет достаточно записей, примените к таблице автоматический фильтр, который позволит вам делать всевозможные отборы в вашей базе данных без использования VBA.
Поскольку это база данных управления денежными средствами, введите формулу: =SUM(C:C)-SUM(E:E) в каждую определенную ячейку справа от названий полей, чтобы всегда видеть текущий остаток денежных средств.
Затем создайте рабочий лист или сводную таблицу. Если вы уже создали связи между таблицами, вы можете использовать любое из их полей в сводной таблице. Мы уже создали ссылки в образце книги модели данных ученика.
Чуть-чуть теории.
Простая двумерная таблица Excel, созданная по определенным правилам, является таблицей базы данных.
Столбцы таблицы Excel — это поля базы данных, а строки — записи базы данных.
Поле (столбец) содержит информацию об атрибуте для всех записей в базе данных.
Запись (строка) состоит из нескольких (в зависимости от количества полей) различных информационных сообщений (атрибутов), которые характеризуют один объект базы данных.
Расширяемая база данных — это таблица, в которую постоянно добавляются новые записи (строки). Имена и количество полей (столбцов) остаются неизменными.
Все это очень важно понимать, знать и помнить!
При создании таблицы базы данных в Excel необходимо соблюдать некоторые важные правила, которые облегчат вам извлечение информации в будущем! Их несложно запомнить!
Основные правила создания базы данных в Excel.
1. Первая строка базы данных должна содержать заголовки столбцов!
2. каждая последующая строка базы данных должна содержать хотя бы одну ячейку, заполненную данными!
3. в табличной области базы данных нельзя использовать объединенные ячейки!
4. каждый столбец может содержать только один тип данных — текст, числа или данные!
5. окружающие ячейки базы данных должны быть пустыми или образовывать границу рабочего листа.
6 Области базы данных необходимо присвоить имя.
7. Область базы данных должна быть объявлена как список.
Пример.
В качестве примера создадим в Excel небольшую учебную базу данных, содержащую записи с информацией о работе завода по производству металлических строительных конструкций.
Администратор сайта поддерживает базу данных Excel. Он вводит информацию о продукции, производимой каждый день. Почему он так поступает и на какие вопросы может ответить, мы узнаем в следующих статьях. А пока давайте создадим информационное пространство для хранения важных производственных данных.
Загрузите MS Excel и приступайте к работе. 1.
1. определите заголовок базы данных «Производство металлоконструкций по участку №2» в связанных ячейках A6…F6 и оставьте несколько строк пустыми в верхней части рабочего листа. Эти строки могут понадобиться при анализе данных в будущем.
Строка заголовка не записывается в область базы данных! Строка 6 не имеет никакого отношения к таблице базы данных, обратите на это внимание.
2. в ячейках A7…F7 напишите заголовки столбцов — полей базы данных. 3.
3. затем в ячейки A8…E17 внесите записи о стальных черновых конструкциях в таблице построчно.
4. в ячейке F8 напишите формулу: =D8*E8 и скопируйте ее в ячейки F9 и F17.
Назначьте имя области базы данных.
Для этого выделите область базы данных с заголовками столбцов A7…F17, а затем в главном меню выберите Вставка — Имя — Назначить….. В появившемся окне «Назначить имя» введите имя, например, DB2, и нажмите на кнопку «OK» — имя присвоено! 6.
Объявите область базы данных в виде списка.
Для этого снова выберите область базы данных с заголовками столбцов, а затем в главном меню выберите Данные — Список — Создать список. Во всплывающем окне «Создать список» проверьте, правильно ли указан диапазон данных и выбран ли пункт «Список с заголовками». Нажмите «OK» — список создан!
База данных Excel готова!
Хотя MS Office предназначен для создания и управления большими, сложными базами данных в Access, миллионы пользователей по всему миру предпочитают создавать простые (и не очень большие) базы данных в Excel. Этому есть несколько причин, и большинство из них.
Введите студенческие идентификаторы быстро
- Введите первые два идентификатора, ST348-245 и ST348-246, в поля A5 и A6 соответственно.
- Выделите два идентификатора, чтобы выбрать их.
- Перетащите перо заливки в ячейку A13 .
Остальные идентификаторы учеников введены правильно в ячейки A6 — A13.
Введите данные правильно
При вводе данных убедитесь, что они введены правильно. За исключением строки 2 между заголовком таблицы и заголовками столбцов, не оставляйте других пустых строк при вводе данных. Также убедитесь, что вы не оставляете пустых ячеек.
Ошибки данных, вызванные неправильным вводом данных, являются причиной многих проблем управления данными. Если данные изначально введены правильно, программа, скорее всего, даст желаемые результаты.
Строки — это записи
Каждая отдельная строка данных в базе данных называется записью. При вводе записей данных помните о следующем:
- Не оставляйте пустых строк в таблице. Это включает в себя отсутствие пустой строки между заголовками столбцов и первой строкой данных.
- Запись должна содержать данные только для конкретного элемента.
- Запись также должна содержать все данные базы данных для этого элемента. Одна строка не может содержать информацию об элементе.
