Как создать базу данных в экселе: Создание базы данных в Excel по клиентам с примерами и шаблонами

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

3753 08.09.2016 Скачать пример

При упоминании баз данных (БД) первым делом, конечно, в голову приходят всякие умные слова типа 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 и вводим туда код нашего макроса:

Sub Add_Sell()
    Worksheets("Форма ввода").Range("A20:E20").Copy                         'копируем строчку с данными из формы
    n = Worksheets("Продажи").Range("A100000").End(xlUp).Row                'определяем номер последней строки в табл. Продажи
    Worksheets("Продажи"). Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'вставляем в следующую пустую строку
    Worksheets("Форма ввода").Range("B5,B7,B9").ClearContents               'очищаем форму
End Sub

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

Ссылки по теме

• Как использовать функцию ВПР (VLOOKUP) для поиска и подстановки значений
• Как заменить ВПР функциями ИНДЕКС и ПОИСКПОЗ
• Автоматическое заполнение форм и бланков данными из таблицы
• Создание отчетов с помощью сводных таблиц
  

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

Создание простой базы данных на рабочем листе Excel. Использование раскрывающихся списков для ввода данных. Файл с примером для скачивания.

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

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

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

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

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

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

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

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

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

Скачать архив с файлом базы данных

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

Шаги по созданию базы данных в Excel

1. Создание электронной таблицы данных
2. Добавление или импорт данных
3. Преобразование данных в таблицу
4. Настройка дизайна таблицы и присвоение имени
900 05 Взаимодействовать с данными

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

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

Вот где пригодятся базовые знания баз данных Excel.

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

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

Давайте пошагово рассмотрим использование Excel для создания базы данных.

1. Создайте электронную таблицу данных

Начните с открытия нового листа Excel.

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

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

2. Добавить или импортировать данные

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

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

Чтобы импортировать данные из внешних источников, щелкните вкладку Data , перейдите в раздел Get/Transform Data и выберите источник назначения. Для успешного импорта данных необходимо убедиться, что данные имеют правильное форматирование, а форматирование зависит от типа исходного файла.

3. Преобразование ваших данных в таблицу

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

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

Если в диалоговом окне отображаются правильные поля и заголовки в порядке, нажмите OK.

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

4. Настроить дизайн таблицы и присвоить имя

Есть несколько вариантов оформления таблицы, но не стоит тратить на это много времени. Цель игры — сделать ваш стол легко видимым.

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

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

5. Взаимодействуйте с данными

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

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

В приведенном ниже примере мы рассчитываем среднее количество проданных единиц. Формула Среднее является опцией в раскрывающемся меню.

Среднее количество проданных единиц рассчитывается и отображается в ячейке.

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

Выйдите за рамки Excel: работа с таблицами Jotform

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

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

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

Эта статья была первоначально опубликована 10 ноября 2020 г. и обновлена ​​24 февраля 2023 г.

Как создать базу данных в Excel [Руководство + рекомендации]

Вам нужно создать и использовать базу данных? Этот пост покажет вам, как создать базу данных в Microsoft Excel.

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

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

Этот пост покажет вам, что такое база данных, и рекомендации, которым вы должны следовать, если собираетесь попробовать использовать Excel в качестве базы данных.

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

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

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

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

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

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

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

Вот некоторые ключевые особенности базы данных и их сравнение с файлом Excel.

Функция	База данных	Excel
Создание, чтение, обновление и удаление записей	✔️	✔️ Excel позволяет любому добавлять или редактировать данные. Это можно расценивать как негативное соображение.
Типы данных	✔️	⚠️ Excel позволяет использовать простые типы данных, такие как текст, числа, даты, логические значения, изображения и значения ошибок. Но не хватает более сложных типов данных, таких как дата и часовой пояс, файлы или JSON.
Проверка данных	✔️	⚠️ В Excel есть некоторые функции проверки данных, но вы можете применять только одно правило за раз, и их можно легко переопределить намеренно или случайно.
Доступ и безопасность	✔️	❌ В Excel нет средств контроля доступа или безопасности. Обычно это управляется через вашу локальную сеть или через SharePoint в Интернете. Но любой может получить доступ к вашему файлу Excel, если он загружен и отправлен им.
Контроль версий	✔️	❌ В Excel нет контроля версий. Этим можно управлять через SharePoint.
Резервные копии	✔️	❌ В Excel нет автоматического резервного копирования. Они могут быть созданы вручную или автоматизированы в SharePoint.
Извлечение и запрос данных	✔️	✔️ Excel позволяет извлекать и запрашивать данные с помощью простого в освоении и использовании Power Query.
Выполнение расчетов	✔️	✔️ Excel имеет большую библиотеку функций, которые можно использовать в вычисляемых столбцах внутри таблиц. В Excel также есть язык формул DAX для вычисляемых столбцов в Power Pivot.
Агрегирование и суммирование данных	✔️	✔️ Excel может легко агрегировать и суммировать данные с помощью формул, сводных таблиц или сводных таблиц.
Отношения	✔️	✔️ Excel имеет множество функций поиска, таких как XLOOKUP, а также функцию слияния таблиц в Power Query и отношения 1 ко многим в Power Pivot.
Весы с большими объемами данных	✔️	⚠️ Excel может хранить до 1 048 576 строк данных на одном листе. Такие инструменты, как Power Query и Power Pivot, могут помочь вам справиться с большими объемами данных, но они будут ограничены в зависимости от характеристик вашего оборудования.
Удобный для пользователя	❌ База данных может быть неудобной для пользователя и может потребовать крутой кривой обучения, с которой предполагаемые пользователи не смогут справиться.	✔️ Большинство людей имеют некоторый опыт работы с Excel.
Стоимость	❌ Настройка, запуск и обслуживание надлежащего инструмента базы данных могут быть дорогими.	✔️ Возможно, ваша организация уже имеет доступ к Excel и использует его.

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

Функции базы данных зависят от того, что это за база данных. Не все базы данных имеют одинаковые функции и функции.

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

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

Проектирование реляционной базы данных

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

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

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

Один из вариантов — создать единую плоскую таблицу, содержащую всю информацию о заказе, продуктах и ​​покупателе, создавшем заказ.

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

Лучшим вариантом является создание отдельных таблиц для хранения данных Order , Product и Customer .

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

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

1. Ан Заказы , которая содержит поля Item и Customer ID .
2. Таблица Products , которая относится к полям Item в таблице Orders .
3. Таблица Customers , связанная с идентификатором Customer ID в таблице Orders .

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

Структура табличных данных в Excel

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

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

Вот несколько правил, которым должны следовать ваши данные, чтобы они были в табличном формате.

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

В приведенном выше примере данных заказов вы можете видеть, что B2:E2 содержит заголовки столбцов ID заказа , ID клиента , Дата заказа , Товар и Количество .

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

Каждая строка в наборе данных представляет собой заказ на один тип продукта.

Вы также заметите, что каждый столбец содержит данные одного типа. Например, столбец «Товар» содержит только информацию о названии продукта и не включает другую информацию о продукте, такую ​​как цена.

⚠️ Предупреждение : Если вы не будете придерживаться этого типа структуры для своих данных, то обобщать и анализировать ваши данные будет сложнее позже. Такие инструменты, как сводные таблицы, требуют табличных данных!

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

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

Таблица Excel — это контейнер для ваших табличных наборов данных. Они помогают хранить одни и те же данные в одном объекте и обладают многими другими преимуществами.

💡 Совет . Прочтите этот пост, чтобы узнать больше обо всех удивительных функциях таблиц Excel.

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

Как создать таблицу Excel

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

1. Выберите любую ячейку в вашем наборе данных.
2. Перейдите на вкладку Вставить на ленте.
3. Выберите Команда таблицы .

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

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

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

4. Установите флажок В моей таблице есть заголовки .
5. Нажмите кнопку OK .

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

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

💡 Совет : Вы можете выбрать один из множества вариантов формата для своей таблицы на вкладке Дизайн таблицы в разделе Стили таблиц .

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

6. Перейдите на вкладку Table Design .
7. Щелкните поле ввода Имя таблицы .
8. Введите новое имя таблицы.
9. Нажмите клавишу Введите , чтобы принять новое имя.

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

Как добавить новые данные в таблицу

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

Добавить строки в таблицу Excel очень просто, и вы можете сделать это несколькими способами.

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

1. Выберите ячейку внутри таблицы.
2. Щелкните правой кнопкой мыши на ячейке.
3. Выберите Вставка из меню.
4. Выберите Строки таблицы выше из подменю.

Это вставит новую пустую строку прямо над выбранными ячейками в вашей таблице.

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

1. Поместите курсор активной ячейки в нижнюю правую ячейку таблицы.
2. Нажмите клавишу Tab .

В конец таблицы будет добавлена ​​новая пустая строка.

Но самый простой способ добавить новые данные в таблицу — ввести текст непосредственно под таблицей. Данные, введенные непосредственно под таблицей, автоматически переносятся в таблицу!

Макет рабочей книги Excel

Если вы собираетесь создать базу данных Excel, она должна быть простой.

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

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

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

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

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

💡 Совет : Вы можете создать гиперссылку на имя таблицы, указанное в оглавлении, на соответствующий лист. Выберите имя и нажмите Ctrl + K для создания гиперссылки. Это может помочь вам перемещаться по книге, когда в вашей базе данных много таблиц.

Использование проверки данных для предотвращения появления недействительных данных

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

Вы можете создать правила проверки данных, такие как.

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

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

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

1. Щелкните левой кнопкой мыши на заголовке столбца, чтобы выбрать весь столбец.

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

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

2. Перейдите на вкладку Данные .
3. Щелкните команду Проверка данных .

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

Разрешить только положительные целые числа

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

1. Перейдите на вкладку Настройки в меню Проверка данных .
2. Выберите параметр Весь номер в раскрывающемся списке Разрешить .
3. Выберите больше из раскрывающегося списка Data .
4. Введите 0 в поле ввода Минимум .
5. Нажмите кнопку OK .

💡 Совет : оставьте флажок Игнорировать пустые , если вы хотите разрешить пустые ячейки в столбце.

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

Разрешить элементы только из списка

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

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

1. Перейдите на вкладку Настройки в меню Проверка данных .
2. Выберите параметр Список в раскрывающемся списке Разрешить .
3. Проверьте раскрывающийся список В ячейке 9вариант 0042.
4. Добавить список элементов на вход Source .
5. Нажмите кнопку OK .

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

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

Разрешить только уникальные значения

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

1. Перейдите на вкладку Настройки в меню Проверка данных .
2. Выберите параметр Пользовательский в раскрывающемся списке Разрешить .

 =(СЧЁТЕСЛИМН(ДВССЫЛ("Продукты[Элемент]"),A2)=1) 

3. Введите приведенную выше формулу в поле ввода Формула .
4. Нажмите кнопку OK .

Формула подсчитывает, сколько раз значение текущей строки появляется в столбце Products[Item] с использованием функции COUNTIFS .

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

📝 Примечание : Вам нужно сослаться на столбец по имени, используя функцию INDIRECT , чтобы диапазон увеличивался по мере добавления элементов в таблицу!

Показать входное сообщение при выборе ячейки

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

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

1. Перейдите на вкладку Входное сообщение в меню Проверка данных .
2. Оставьте флажок Показывать входное сообщение при выборе ячейки .
3. Добавьте текст в раздел Заголовок .
4. Добавьте текст в раздел Входное сообщение .
5. Нажмите кнопку OK .

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

Предотвращение ввода неверных данных с помощью сообщения об ошибке

При наличии правила проверки данных обычно требуется предотвратить ввод неверных данных.

Этого можно добиться с помощью функции сообщения об ошибке в меню проверки данных.

1. Перейдите на вкладку Предупреждение об ошибке в меню Проверка данных .
2. Сохранить предупреждение об ошибке Show после ввода неверных данных опция проверена.
3. Выберите параметр Stop в раскрывающемся списке Style .
4. Добавьте текст в раздел Заголовок .
5. Добавьте текст в раздел Сообщение об ошибке .
6. Нажмите кнопку OK .

📝 Примечание : Параметр Stop необходим, если вы хотите предотвратить ввод неверных данных, а не только предупредить пользователя о том, что данные недействительны.

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

Форма ввода данных для вашей базы данных Excel

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

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

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

• Используйте таблицу для ввода данных.
• Используйте форму ввода данных панели быстрого доступа.
• Используйте Microsoft Forms для ввода данных.
• Используйте приложение Microsoft Power Automate для ввода данных.
• Используйте Microsoft Power Apps для ввода данных.

💡 Совет : Прочтите этот пост, чтобы узнать больше о различных вариантах ввода данных в Excel.

Такие инструменты, как Microsoft Forms, Power Automate и Power Apps, предоставят вам больше возможностей для проверки данных, доступа и контроля безопасности при вводе данных по сравнению с базовыми параметрами Excel.

Доступ и безопасность вашей базы данных Excel

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

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

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

Однако вы можете управлять доступом и безопасностью файла из SharePoint.

💡 Совет : ознакомьтесь с этим сообщением Microsoft о рекомендациях по защите файлов SharePoint для получения более подробной информации.

При сохранении файла Excel в SharePoint вы также сможете увидеть последние изменения.

1. Перейдите на вкладку Обзор .
2. Щелкните команду Показать изменения .

Откроется панель Изменения в правой части листа Excel.

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

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

Это отличный способ отследить причину любых потенциальных ошибок в ваших данных.

Запрос базы данных Excel с помощью Power Query

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

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

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

Вот как можно использовать запрос мощности для быстрого импорта данных в любой файл Excel.

1. Перейдите на вкладку Данные .
2. Щелкните команду Получить данные .
3. Выберите вариант Из файла .
4. Выберите в подменю параметр Из книги Excel .

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

5. Выберите файл базы данных Excel.
6. Нажмите кнопку Импорт .

⚠️ Предупреждение : Убедитесь, что файл базы данных Excel закрыт, иначе процесс импорта покажет предупреждение о невозможности подключения к файлу, поскольку он используется!

При нажатии кнопки «Импорт» открывается меню Navigator . Здесь вы можете выбрать, какие данные загружать и куда их загружать.

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

7. Установите флажок Выберите несколько элементов , если вы хотите загрузить более одной таблицы из базы данных.
8. Выберите таблицы для загрузки.
9. Щелкните значок маленькой стрелки рядом с кнопкой Загрузить .
10. Выберите параметр Загрузить в в подменю Загрузить .

Откроется меню «Импорт данных», в котором вы можете импортировать данные в таблицу , PivotTable , PivotChart или только создать Соединение с данными без их загрузки.

11. Выберите параметр Таблица .
12. Нажмите кнопку OK .

Затем ваши данные загружаются в таблицу Excel в новой книге.

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

💡 Совет . С помощью Power Query вы можете делать гораздо больше, чем просто загружать данные. Вы также можете преобразовывать свои данные практически любым мыслимым способом, используя кнопку Transform Data в меню Navigator . Прочтите этот пост о том, как использовать Power Query, чтобы узнать больше об этом удивительном инструменте.

Анализ и обобщение базы данных Excel с помощью Power Pivot

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

При загрузке данных с помощью Power Quer есть возможность Добавить эти данные в модель данных в меню Импорт данных .

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

Вы можете построить отношения между таблицами на вкладке Данные .

1. Перейдите на вкладку Данные .
2. Щелкните команду Отношения или Управление моделью данных .

Теперь вы сможете анализировать несколько таблиц из своей базы данных в одной сводной таблице!

Выводы

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

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

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