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

В пакете Microsoft Office есть особая программка для сотворения базы данных и работы с ними – Access. Тем не наименее, почти все юзеры предпочитают применять для этих целей наиболее знакомое им приложение – Excel. Необходимо отметить, что у данной для нас программки имеется весь инструментарий для сотворения настоящей базы данных (БД). Давайте выясним, как это создать.

Процесс сотворения

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

Согласно специальной терминологии, строчки БД называются «записями». В каждой записи находится информация о отдельном объекте.

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

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

Создание таблицы

Итак, до этого всего нам необходимо сделать таблицу.

1. Вписываем заглавия полей (столбцов) БД.

На этом создание каркаса БД закончено.

Присвоение атрибутов базы данных

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

Перебегаем во вкладку «Данные».

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

Сортировка и фильтр

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

Выделяем информацию того поля, по которому собираемся провести упорядочивание. Кликаем по кнопочке «Сортировка» расположенной на ленте во вкладке «Данные» в блоке инструментов «Сортировка и фильтр».

Сортировку можно проводить фактически по хоть какому параметру:

• имя по алфавиту;
• дата;
• число и т.д.

• В поле «Сортировка» указывается, как конкретно она будет производиться. Для БД идеальнее всего избрать параметр «Значения».
• В поле «Порядок» указываем, в каком порядке будет проводиться сортировка. Для различных типов инфы в этом окне высвечиваются различные значения. К примеру, для текстовых данных – это будет значение «От А до Я» либо «От Я до А», а для числовых – «По возрастанию» либо «По убыванию».
• Принципиально проследить, чтоб около значения «Мои данные содержат заглавия» стояла галочка. Если её нет, то необходимо поставить.

Опосля ввода всех подходящих характеристик нажимаем на клавишу «OK».

Поиск

При наличии большенный БД поиск по ней комфортно создавать с помощь специального инструмента.

Для этого перебегаем во вкладку «Основная» и на ленте в блоке инструментов «Редактирование» нажимаем на клавишу «Отыскать и выделить».

Закрепление областей

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

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

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

Выпадающий перечень

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

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

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

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

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

Мы рады, что смогли посодействовать Для вас в решении трудности.

Кроме данной для нас статьи, на веб-сайте еще 12327 инструкций.
Добавьте веб-сайт Lumpics.ru в закладки (CTRL+D) и мы буквально еще пригодимся для вас.

Отблагодарите создателя, поделитесь статьей в соц сетях.

Опишите, что у вас не вышло. Наши спецы постараются ответить очень стремительно.

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

Колонки в базах данных именуются полями, а строчки — записями. В первую строчку незапятнанного листа рабочей книжки записываем наименования полей. Если в вашем наборе данных в 1-ое поле записывается дата, то лучше сходу этому полю задать соответственный формат. Выделите 1-ый столбец, изберите в контекстном меню «Формат ячеек …» и задайте ему нужный формат даты. Не считая того, весьма хорошо будет организовать автоматическую запись текущей даты в первом поле каждой новейшей записи. В графах создаваемой базы данных с валютными суммами устанавливаем числовой формат с 2-мя знаками опосля запятой. В других полях можно бросить общий формат.

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

Как видно, для примера, я уже добавил в базу данных изначальное сальдо. Еще, непременно, необходимо закрепить на дисплее первую строчку (в Excel 2010 это: Вид — Закрепить области — Закрепить верхнюю строчку). Потом избираем 1-ые три строчки нашей таблицы и добавляем границы ячеек. Лист назовем «Касса».

Создание раскрывающихся списков

Списки для выбора значений мы создаем для полей «Торговая точка», «Вид прихода», «Вид расхода», «Получатель/плательщик». В моей базе данных «Получатель/плательщик» именуется короче — «Субъект», и меж полями «Дата» и «Торговая точка» есть поле «Компания», где до 2 квартала 2012 года выбирал ИП либо ООО.

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

Чтоб эти спектры значений можно было добавить в формулу для выбора в раскрывающемся перечне, им нужно присвоить имена. При этом, создавать мы будем динамические именованные спектры, чтоб не приходилось всякий раз изменять спектр в формуле выбора данных при добавлении новейшего значения. Для этого перебегаем на лист «Списки» и открываем окно сотворения имени (в Excel 2010 это: Формулы — Присвоить имя; свои файлы создавал в Excel 2000, но на данный момент его структуру меню не помню). Записываем:
Имя: Торговая_точка_выбор,
Спектр: =СМЕЩ(Списки!$A$2;0;0;СЧЁТЗ(Списки!$A:$A)-1;1)

Жмем «ОК» и 1-ый динамический спектр сотворен. То же самое проделываем и для остальных диапазонов:

Имя: Вид_прихода_выбор
Спектр: =СМЕЩ(Списки!$B$2;0;0;СЧЁТЗ(Списки!$B:$B)-1;1)

Имя: Вид_расхода_выбор
Спектр: =СМЕЩ(Списки!$C$2;0;0;СЧЁТЗ(Списки!$C:$C)-1;1)

Имя: Получатель_плательщик_выбор
Спектр: =СМЕЩ(Списки!$D$2;0;0;СЧЁТЗ(Списки!$D:$D)-1;1)

Когда всем спектрам со перечнями значений для выбора присвоены имена, перебегаем на лист «Касса» и создаем в ячейках соответственных колонок раскрывающиеся списки. Для этого избираем ячейку «B3» и открываем окно «Проверка вводимых значений» (в Excel 2010 это: Данные — Проверка данных — Проверка данных). На вкладке «Характеристики» избираем Тип данных: Перечень, а в Источник записываем: =Торговая_точка_выбор.

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

для «D3»: =Вид_прихода_выбор,
для «F3»: =Вид_расхода_выбор,
для «G3»: =Получатель_плательщик_выбор.

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

Наружный вид базы данных в Excel

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

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

Ну и раз уж это база данных для ведения кассы, в всякую ячейку закрепленной строчки, правее наименований полей, вставьте формулу: =СУММ(C:C)-СУММ(E:E), чтоб постоянно созидать текущий остаток средств в кассе.

Скачать пример базы данных в Excel

В архиве для скачки представлен мой файл базы данных в Excel с макросом для автоматической записи текущей даты. При выбирании ячейки во 2-м столбце, если ячейка в первом столбце пустая, то в нее автоматом записывается текущая дата. Код для автоматической записи текущей даты был доработан, и, если вы желаете его применять, скопируйте из статьи VBA Excel. Автоматическая запись текущей даты и времени, параграф «Окончательный вариант».

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

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

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

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

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

Шаг 1. Начальные данные в виде таблиц

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

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

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

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

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

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

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

В ячейке B11 найдем стоимость избранного продукта в 3-ем столбце умной таблицы Прайс при помощи функции ВПР (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) .