Как сделать базу данных в excel

Как создать базу данных в Excel?

Добрый день!

Темой этой статьи, как вы поняли, станет создание собственной базы данных. Для тех, кто по опытнее, слова база данных сразу вызовет ассоциацию с MS Access, 1C, Oracle, SQL, СУБД FoxPro и другие, с которыми могла свести судьба и работа. Сразу соглашусь с вами, что это очень хорошие, мощные и достойные базы данных, которые позволяют обрабатывать преогромнейшие массивы данных, но такие удовольствия и стоят соответственно совсем не копейки. В случае если ваш бизнес маленький вам всё равно нужно упорядочить, отсортировать и автоматизировать свои данные что позволит улучшить анализ и принимаемые решения, что зачастую являются главными условиями выживаемости в экономических реалиях. Также небольшие базы данных можно создавать и для своих нужд или структурирования информации, например, база данных книг (которые у вас есть или прочитали или хотите прочитать), ведения личного финансового учёта и прочее.

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

Если вас интересует вопрос, почему именно в MS Excel я решил создавать базу данных, то думаю, что это не секрет что это самая распространённая и доступная программа из тех, что имеется у вас на компьютере, да и с этой задачей сможет справиться, на 5 с плюсом. Так как инструментов у нее предостаточно, это и формулы, и возможность создания интерфейса с использованием VBA, и формирование дашбордов, и инструменты для связывания диаграмм и ячеек, инструменты которые могут производить поиск и создание обновляемых динамических отчётов.

Небольшое отступления, чтобы определить, что же такое база данных, ее терминология:

• База данных – это обыкновенная двумерная таблица в Excel, которая была создана при соблюдении определенных правил;
• Поле (или столбик) – содержит в себе информацию об определенном признаке или значении для записей, во всей базе данных (определяется в шапке базы данных);
• Запись (или строка) – состоит из нескольких или множества признаков, или значений, которые могут охарактеризовать только один объект вашей базы данных;
• Расширяемая база данных – это созданная таблица, куда постоянно производится добавление новых данных или записей (строк) вашей информации. Неизменными остаются всегда количество полей и название.

Всё вышенаписанное является очень важным, так как незнание механизмов работы приведет к ошибке, поэтому это нужно знать и помнить!

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

Правила создания базы данных в Excel

1. Обязательно! Без исключений! Первая строка в вашей базе данных должна содержать название заголовков полей (столбцов);
2. Каждая запись (строка) базы данных обязана содержать ячейку с заполненными данными (никаких пустых строк);
3. Любое объединение диапазонов ячеек запрещено на всей таблице базы данных;
4. Каждое поле (столбик) должно, обязательно, содержать в себе только один определенный тип данных, это либо текстовые значения, либо числовые или значения времени;
5. Пространство возле вашей базы данных обязательно должно быть пустым;
6. Всему диапазону вашей базы данных необходимо присвоить имя;
7. Укажите, что диапазон вашей базы данных является списком;
8. Рекомендовано создание и ведение базы данных на отдельном листе.

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

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

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

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

Для начала работы я создаю макет моей базы данных, так как это основа при выполнении таких задач. Моим целям достаточно 4 страницы Excel, в которых я и буду размещать, и обрабатывать данные:

• Страница №1 будет содержать весь набор данных для формы ввода данных, а точнее для формирования данных выпадающего списка, что создаст удобство в использовании, унифицирует данные и застрахует меня от большинства ошибок;
• Страница №2 будет служить мне формой ввода данных в мою базу данных, это позволит мне быстро и почти в автоматическом режиме с помощью макроса добавлять новые записи мне в базу данных;
• Страница №3 будет хранить собственно базу данных, никаких активных работ я здесь не буду делать, чтобы не повредить ее целостности;
• Страница №4 это будет структурированный результат на основе сводной таблицы, которая будет удобно и в нужной форме отбирать записи с базы данных и предоставлять их мне.

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

Следующим шагом станет создание формы ввода для нашей базы данных. В ней я перечислил все те данные, которые мне нужно внести в мою базу. Порядковый номер записи уже стоит, так как он определяется формулой: =СЧЁТЗ(Библиотека!B3:B9949)+1 которая считает записи и выводит номер следующей. В ячейки выделенные синим цветом я буду вводить данные вручную, а в белых ячейках я создам выпадающий список, это позволит мне водить одинаковые значения и избавится от несовпадения данных и ошибок. Введенные данные будут формировать строку A16:L16, которую с помощью макроса, прикреплённого к кнопке, буду переносить в свою библиотеку. Создаю кнопку запуска макроса, на вкладке «Разработчик» в блоке «Элементы управления» нажимаем пиктограмму «Вставить» и в выпадающем меню выбираем элемент «Кнопка», рисуем ее на нашем поле, где будет удобно ее использовать и подписываем ее. Теперь вызываем редактор VBA для написания выполняемого макроса по внесению записи в базу данных: Создаем для нашей кнопки отдельный обыкновенный модуль, выбрав пункт «Insert», потом «Module»: Вставляем в модуль наш макрос «Add_Books»:

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

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

Прежде чем приступить к работе с моделью данных, необходимо получить некоторые данные. Для этого мы воспользуемся интерфейсом «получение & преобразования (Power Query)», поэтому вам может потребоваться выполнить шаг назад и посмотреть видео или выполнить наше руководство по началу работы с надстройкой «создание & преобразования и Power Pivot».

Excel 2016 & Excel для Office 365 -Power Pivot входит в ленту.

Excel 2013 — компонент Power Pivot входит в состав набора Office профессиональный плюс в выпуске Excel 2013, но по умолчанию не включен. Узнайте больше о том , как запустить надстройку Power Pivot для Excel 2013.

Excel 2016 & Excel для Office 365 -Get & Transform (Power Query) был интегрирован с Excel на вкладке данные .

Excel 2013 -Power Query — это надстройка, которая входит в состав Excel, но должна быть активирована. Перейдите в меню файл > Параметры > надстройки, а затем в раскрывающемся списке Управление в нижней части области выберите пункт надстройки com > Перейти. Установите флажок Microsoft Power Query для Excelи нажмите кнопку ОК , чтобы активировать его. Вкладка Power Query будет добавлена на ленту.

Excel 2010 — Загрузка и установка надстройки Power Query.. После активации вкладка Power Query будет добавлена на ленту.

Начало работы

Сначала необходимо получить некоторые данные.

В Excel 2016 и Excel для Office 365 используйте данные > получения данных & преобразования > получения данных для импорта данных из любого количества внешних источников данных, таких как текстовый файл, книга Excel, веб-сайт, Microsoft Access, SQL Server или другая реляционная база данных, содержащая несколько связанных таблиц.

В Excel 2013 и 2010 перейдите в Power Query > получить внешние данные, а затем выберите источник данных.

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

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

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

Если вам нужно изменить исходные данные, можно выбрать параметр изменить . Дополнительные сведения можно найти в разделе Введение в редактор запросов (Power Query).

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

Модели создаются неявно, когда вы импортируете в Excel несколько таблиц одновременно.

Модели создаются явно, если вы импортируете данные с помощью надстройки Power Pivot. В надстройке модель представлена в макете с вкладками, аналогичном Excel, где каждая вкладка содержит табличные данные. Ознакомьтесь с информацией Получение данных с помощью надстройки Power Pivot, чтобы узнать основы импорта данных с помощью базы данных SQL Server.

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

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

Совет: Как определить, содержит ли книга модель данных? Перейдите в раздел Power Pivot > Управление. Если вы видите данные, похожие на лист, модель уже существует. Ознакомьтесь со статьей: Определение источников данных, используемых в модели данных книги , для более подробной информации.

Создание связей между таблицами

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

Перейдите в Power Pivot > Управление.

На вкладке Главная нажмите кнопку представление диаграммы.

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

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

Мы создали следующие ссылки:

tbl_Students | КОД учащегося > tbl_Grades | ИДЕНТИФИКАТОР учащегося

Другими словами, перетащите поле учащихся ID из таблицы Students в поле «код учащегося» в таблице «оценки».

tbl_Semesters | > с ИДЕНТИФИКАТОРом «семестр» tbl_Grades | Семестра

tbl_Classes | Номер класса > tbl_Grades | Номер класса

Имена полей не должны быть одинаковыми для создания связи, но должны быть одного типа данных.

Соединительные линии в представлении схемы имеют «1» с одной стороны и «*» на другом. Это означает, что существует связь «один-ко-многим» между таблицами, которая определяет способ использования данных в сводных таблицах. Для получения дополнительных сведений ознакомьтесь со связями между таблицами в модели данных .

Соединители указывают на то, что между таблицами есть связь. Они не будут показывать, какие поля связаны друг с другом. Чтобы просмотреть ссылки, перейдите на вкладку Power Pivot > Управление > > связей > Управление связями. В Excel можно переходить к данным > связям.

Использование модели данных для создания сводной таблицы или сводной диаграммы

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

В Power Pivotвыберите Управление.

На вкладке Главная нажмите кнопку Сводная таблица.

Выберите место, куда нужно поместить сводную таблицу: новый лист или текущее расположение.

Нажмите кнопку ОК, и Excel добавит пустую сводную таблицу с областью списка полей, которая отображается справа.

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

Добавление имеющихся несвязанных данных в модель данных

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

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

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

Щелкните Power Pivot > Добавить в модель данных.

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

Диапазон или таблица будут добавлены в модель как связанная таблица. Дополнительные сведения о работе со связанными таблицами в модели см. в статье Добавление данных с помощью связанных таблиц Excel в Power Pivot.

Добавление данных в таблицу Power Pivot

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

При упоминании баз данных (БД) первым делом, конечно, в голову приходят всякие умные слова типа SQL, Oracle, 1С или хотя бы Access. Безусловно, это очень мощные (и недешевые в большинстве своем) программы, способные автоматизировать работу большой и сложной компании с кучей данных. Беда в том, что иногда такая мощь просто не нужна. Ваш бизнес может быть небольшим и с относительно несложными бизнес-процессами, но автоматизировать его тоже хочется. Причем именно для маленьких компаний это, зачастую, вопрос выживания.

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

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

Со всем этим вполне может справиться Microsoft Excel, если приложить немного усилий. Давайте попробуем это реализовать.

Шаг 1. Исходные данные в виде таблиц

Информацию о товарах, продажах и клиентах будем хранить в трех таблицах (на одном листе или на разных — все равно). Принципиально важно, превратить их в «умные таблицы» с автоподстройкой размеров, чтобы не думать об этом в будущем. Это делается с помощью команды Форматировать как таблицу на вкладке Главная (Home — Format as Table) . На появившейся затем вкладке Конструктор (Design) присвоим таблицам наглядные имена в поле Имя таблицы для последующего использования:

Итого у нас должны получиться три «умных таблицы»:

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

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

Шаг 2. Создаем форму для ввода данных

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

В ячейке B3 для получения обновляемой текущей даты-времени используем функцию ТДАТА (NOW) . Если время не нужно, то вместо ТДАТА можно применить функцию СЕГОДНЯ (TODAY) .

В ячейке B11 найдем цену выбранного товара в третьем столбце умной таблицы Прайс с помощью функции ВПР (VLOOKUP) . Если раньше с ней не сталкивались, то сначала почитайте и посмотрите видео тут.

В ячейке B7 нам нужен выпадающий список с товарами из прайс-листа. Для этого можно использовать команду Данные — Проверка данных (Data — Validation) , указать в качестве ограничения Список (List) и ввести затем в поле Источник (Source) ссылку на столбец Наименование из нашей умной таблицы Прайс:

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

Функция ДВССЫЛ (INDIRECT) нужна, в данном случае, потому что Excel, к сожалению, не понимает прямых ссылок на умные таблицы в поле Источник. Но та же ссылка «завернутая» в функцию ДВССЫЛ работает при этом «на ура» (подробнее об этом было в статье про создание выпадающих списков с наполнением).

Шаг 3. Добавляем макрос ввода продаж

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

Т.е. в ячейке A20 будет ссылка =B3, в ячейке B20 ссылка на =B7 и т.д.

Теперь добавим элементарный макрос в 2 строчки, который копирует созданную строку и добавляет ее к таблице Продажи. Для этого жмем сочетание Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer) . Если эту вкладку не видно, то включите ее сначала в настройках Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon) . В открывшемся окне редактора Visual Basic вставляем новый пустой модуль через меню Insert — Module и вводим туда код нашего макроса:

Теперь можно добавить к нашей форме кнопку для запуска созданного макроса, используя выпадающий список Вставить на вкладке Разработчик (Developer — Insert — Button) :

После того, как вы ее нарисуете, удерживая нажатой левую кнопку мыши, Excel сам спросит вас — какой именно макрос нужно на нее назначить — выбираем наш макрос Add_Sell. Текст на кнопке можно поменять, щелкнув по ней правой кнопкой мыши и выбрав команду Изменить текст.

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

Шаг 4. Связываем таблицы

Перед построением отчета свяжем наши таблицы между собой, чтобы потом можно было оперативно вычислять продажи по регионам, клиентам или категориям. В старых версиях Excel для этого потребовалось бы использовать несколько функций ВПР (VLOOKUP) для подстановки цен, категорий, клиентов, городов и т.д. в таблицу Продажи. Это требует времени и сил от нас, а также «кушает» немало ресурсов Excel. Начиная с Excel 2013 все можно реализовать существенно проще, просто настроив связи между таблицами.

Для этого на вкладке Данные (Data) нажмите кнопку Отношения (Relations) . В появившемся окне нажмите кнопку Создать (New) и выберите из выпадающих списков таблицы и названия столбцов, по которым они должны быть связаны:

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

Само-собой, аналогичным образом связываются и таблица Продажи с таблицей Клиенты по общему столбцу Клиент:

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

Шаг 5. Строим отчеты с помощью сводной

Теперь для анализа продаж и отслеживания динамики процесса, сформируем для примера какой-нибудь отчет с помощью сводной таблицы. Установите активную ячейку в таблицу Продажи и выберите на ленте вкладку Вставка — Сводная таблица (Insert — Pivot Table) . В открывшемся окне Excel спросит нас про источник данных (т.е. таблицу Продажи) и место для выгрузки отчета (лучше на новый лист):

Жизненно важный момент состоит в том, что нужно обязательно включить флажок Добавить эти данные в модель данных (Add data to Data Model) в нижней части окна, чтобы Excel понял, что мы хотим строить отчет не только по текущей таблице, но и задействовать все связи.

После нажатия на ОК в правой половине окна появится панель Поля сводной таблицы, где нужно щелкнуть по ссылке Все, чтобы увидеть не только текущую, а сразу все «умные таблицы», которые есть в книге.А затем можно, как и в классической сводной таблице, просто перетащить мышью нужные нам поля из любых связанных таблиц в области Фильтра, Строк, Столбцов или Значений — и Excel моментально построит любой нужный нам отчет на листе:

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

Также, выделив любую ячейку в сводной и нажав кнопку Сводная диаграмма (Pivot Chart) на вкладке Анализ (Analysis) или Параметры (Options) можно быстро визуализировать посчитанные в ней результаты.

Шаг 6. Заполняем печатные формы

Еще одной типовой задачей любой БД является автоматическое заполнение различных печатных бланков и форм (накладные, счета, акты и т.п.). Про один из способов это сделать, я уже как-то писал. Здесь же реализуем, для примера, заполнение формы по номеру счета:

Предполагается, что в ячейку C2 пользователь будет вводить число (номер строки в таблице Продажи, по сути), а затем нужные нам данные подтягиваются с помощью уже знакомой функции ВПР (VLOOKUP) и функции ИНДЕКС (INDEX) .

База данных в Excel

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

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

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

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

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

Чуть-чуть теории.

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

Столбцы таблицы Excel – это поля базы данных, а строки – это записи базы данных.

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

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

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

Все вышесказанное очень важно понимать, знать, и помнить!

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

Основные правила создания базы данных в Excel.

1. Первая строка базы данных должна содержать заголовки столбцов!

2. Каждая последующая строка базы данных должна содержать хотя бы одну заполненную данными ячейку.

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

4. Каждый столбец должен содержать только один тип данных – или текст, или числа, или даты!

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

6. Необходимо присвоить диапазону базы данных имя.

7. Следует объявить диапазон базы данных списком.

Пример.

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

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

Загружаем программу MS Excel и приступаем к работе.

1. Заголовок базы данных «Выпуск металлоконструкций участком №2» располагаем в объединенных ячейках A6…F6, оставляя сверху рабочего листа несколько строк пустыми. Эти строки могут понадобиться в будущем при анализе данных.

Написанный заголовок не будет находиться в области базы данных! Строка №6 не имеет отношения к таблице базы данных, обратите на это внимание.

2. В ячейки A7…F7 записываем заголовки столбцов – полей базы данных.

3. Далее построчно в ячейки A8…E17 заносим записи о выпуске металлоконструкций.

4. В ячейку F8 вписываем формулу: =D8*E8 и копируем ее в ячейки F9. F17 .

5. Присваиваем диапазону базы данных имя.

Для этого выделяем область базы данных вместе с заголовками столбцов A7…F17; в главном меню выбираем «Вставка» — «Имя» — «Присвоить…». В выпавшем окне «Присвоение имени» пишем имя, например — БД2 и нажимаем на кнопку «ОК» – диапазону присвоено имя!

6. Объявляем диапазон базы данных списком.

Для этого вновь выделяем область базы данных вместе с заголовками столбцов; в главном меню выбираем «Данные» — «Список» — «Создать список».
В выпавшем окне «Создание списка» проверяем правильность указанной области расположения данных и наличие галочки у надписи «Список с заголовками». Нажимаем на кнопку «ОК» – список создан!

База данных в Excel готова!

Итоги.

Если активировать любую из ячеек внутри списка («встать мышью»), то мы увидим:

1. Объявленный список окаймлен синей жирной граничной линией.

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

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

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

Для чего мы уделили столько внимания созданию элементарной таблицы?! Это всем станет понятно, когда будем фильтровать, сортировать данные и формировать различные отчеты по запросам, но об этом читайте в последующих статьях. Эти азы работы с информацией в Excel я постараюсь изложить максимально кратко, интересно и просто. Правильная и продуманная предварительная подготовка «скелета» для хранения информации — это залог того, что в будущем вы легко найдете необходимые данные!

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

Прошу уважающих труд автора скачивать файл после подписки на анонсы статей!

Ссылка на скачивание файла с примером: database (xls 31,0 KB).