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

Содержание

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

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

Процесс создания

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

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

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

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

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

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

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

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

Урок: Как сделать таблицу в Excel

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

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

Переходим во вкладку «Данные».
Выделяем весь диапазон таблицы. Кликаем правой кнопкой мыши. В контекстном меню жмем на кнопку «Присвоить имя…».
В графе «Имя» указываем то наименование, которым мы хотим назвать базу данных. Обязательным условием является то, что наименование должно начинаться с буквы, и в нём не должно быть пробелов. В графе «Диапазон» можно изменить адрес области таблицы, но если вы её выделили правильно, то ничего тут менять не нужно. При желании в отдельном поле можно указать примечание, но этот параметр не является обязательным. После того, как все изменения внесены, жмем на кнопку «OK».
Кликаем по кнопке «Сохранить» в верхней части окна или набираем на клавиатуре сочетание клавиш Ctrl+S, для того, чтобы сберечь БД на жестком диске или съемном носителе, подключенном к ПК.

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

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

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

Выделяем информацию того поля, по которому собираемся провести упорядочивание. Кликаем по кнопке «Сортировка» расположенной на ленте во вкладке «Данные» в блоке инструментов «Сортировка и фильтр».
Сортировку можно проводить практически по любому параметру:
- имя по алфавиту;
- дата;
- число и т.д.
В следующем появившемся окне будет вопрос, использовать ли для сортировки только выделенную область или автоматически расширять её. Выбираем автоматическое расширение и жмем на кнопку «Сортировка…».
Открывается окно настройки сортировки. В поле «Сортировать по» указываем имя поля, по которому она будет проводиться.
- В поле «Сортировка» указывается, как именно она будет выполняться. Для БД лучше всего выбрать параметр «Значения».
- В поле «Порядок» указываем, в каком порядке будет проводиться сортировка. Для разных типов информации в этом окне высвечиваются разные значения. Например, для текстовых данных – это будет значение «От А до Я» или «От Я до А», а для числовых – «По возрастанию» или «По убыванию».
- Важно проследить, чтобы около значения «Мои данные содержат заголовки» стояла галочка. Если её нет, то нужно поставить.
После ввода всех нужных параметров жмем на кнопку «OK».
После этого информация в БД будет отсортирована, согласно указанным настройкам. В этом случае мы выполнили сортировку по именам сотрудников предприятия.
Одним из наиболее удобных инструментов при работе в базе данных Excel является автофильтр. Выделяем весь диапазон БД и в блоке настроек «Сортировка и фильтр» кликаем по кнопке «Фильтр».
Как видим, после этого в ячейках с наименованием полей появились пиктограммы в виде перевернутых треугольников. Кликаем по пиктограмме того столбца, значение которого собираемся отфильтровать. В открывшемся окошке снимаем галочки с тех значений, записи с которыми хотим скрыть. После того как выбор сделан, жмем на кнопку «OK».
Как видим, после этого, строки, где содержатся значения, с которых мы сняли галочки, были скрыты из таблицы.
Для того, чтобы вернуть все данные на экран, кликаем на пиктограмму того столбца, по которому проводилась фильтрация, и в открывшемся окне напротив всех пунктов устанавливаем галочки. Затем жмем на кнопку «OK».
Для того, чтобы полностью убрать фильтрацию, жмем на кнопку «Фильтр» на ленте.

Урок: Сортировка и фильтрация данных в Excel

Поиск

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

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

Урок: Как сделать поиск в Экселе

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

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

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

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

Урок: Как закрепить область в Экселе

Выпадающий список

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

Создаем дополнительный список. Удобнее всего его будет разместить на другом листе. В нём указываем перечень значений, которые будут появляться в выпадающем списке.
Выделяем этот список и кликаем по нему правой кнопкой мыши. В появившемся меню выбираем пункт «Присвоить имя…».
Открывается уже знакомое нам окно. В соответствующем поле присваиваем имя нашему диапазону, согласно условиям, о которых уже шла речь выше.
Возвращаемся на лист с БД. Выделяем диапазон, к которому будет применяться выпадающий список. Переходим во вкладку «Данные». Жмем на кнопку «Проверка данных», которая расположена на ленте в блоке инструментов «Работа с данными».
Открывается окно проверки видимых значений. В поле «Тип данных» выставляем переключатель в позицию «Список». В поле «Источник» устанавливаем знак «=» и сразу после него без пробела пишем наименование выпадающего списка, которое мы дали ему чуть выше. После этого жмем на кнопку «OK».

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

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

Урок: Как сделать выпадающий список в Excel

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

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

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

Помогла ли вам эта статья?

ДА НЕТ

Создание базы данных в 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 и вводим туда код нашего макроса:

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).

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

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

Автор Глеб Захаров На чтение 7 мин. Просмотров 28 Опубликовано 25.06.2019

Отслеживать контакты, коллекции и другие данные

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

Эта статья относится к Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel для Mac, Excel для Android и Excel Online.

Введите данные

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

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

Введите студенческие идентификаторы быстро

Введите первые два идентификатора, ST348-245 и ST348-246 , в ячейки A5 и A6, соответственно.
Выделите два идентификатора, чтобы выбрать их.
Перетащите маркер заполнения в ячейку A13 .

Остальные идентификаторы ученика правильно вводятся в ячейки с A6 по A13.

Введите данные правильно

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

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

Строки – это записи

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

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

Столбцы поля

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

Имена полей используются для обеспечения того, чтобы данные для каждой записи вводились в одинаковой последовательности.
Данные в столбце должны быть введены в том же формате. Если вы начинаете вводить цифры в виде цифр (например, 10 или 20), продолжайте в том же духе. Не меняйте часть пути и начинайте вводить числа в виде слов (например, десять или двадцать). Быть последовательным.
Таблица не должна содержать пустых столбцов.

Создать таблицу

После ввода данных их можно преобразовать в таблицу. Чтобы преобразовать данные в таблицу:

Выделите ячейки от A3 до E13 на листе.
Выберите вкладку Главная .
Выберите Форматировать как таблицу , чтобы открыть раскрывающееся меню.
Выберите синий параметр Средний стиль таблицы 9 , чтобы открыть диалоговое окно «Формат таблицы».
Когда диалоговое окно открыто, ячейки с A3 по E13 на листе обведены пунктирной линией.
Если пунктирная линия окружает правильный диапазон ячеек, выберите ОК в диалоговом окне «Формат таблицы».
Если пунктирная линия не окружает правильный диапазон ячеек, выделите правильный диапазон на листе и затем выберите ОК в диалоговом окне «Формат таблицы».

Рядом с каждым именем поля добавляются раскрывающиеся стрелки, а строки таблицы форматируются чередующимся светло-синим цветом.

Используйте инструменты базы данных

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

Сортировать данные

Выберите стрелку раскрывающегося списка рядом с полем Фамилия.
Выберите Сортировать от А до Я , чтобы отсортировать базу данных по алфавиту.
После сортировки Грэм Дж. – первая запись в таблице, а Уилсон Р. – последняя.

Фильтровать данные

Выберите стрелку раскрывающегося списка рядом с полем Программы.
Установите флажок рядом с Выбрать все , чтобы снять все флажки.
Установите флажок рядом с Бизнес , чтобы добавить флажок в поле.
Выберите ОК .
Всего двое учеников Г.Томпсон и Ф. Смит видны, потому что они – единственные два студента, зарегистрированные в деловой программе.
Чтобы показать все записи, выберите стрелку раскрывающегося списка рядом с полем «Программа» и выберите Очистить фильтр из «Программы» .

Расширить базу данных

Чтобы добавить дополнительные записи в вашу базу данных:

Наведите указатель мыши на маленькую точку в нижнем правом углу стола.
Указатель мыши изменится на двуглавую стрелку.
Нажмите и удерживайте правую кнопку мыши и перетащите указатель вниз, чтобы добавить пустую строку в конец базы данных.
Добавьте следующие данные в эту новую строку:
Ячейка A14: ST348-255
Ячейка B14: Кристофер
Ячейка C14: А.
Ячейка D14: 22
Ячейка E14: Наука .

Завершите форматирование базы данных

Выделите ячейки A1 и E1 на листе.
Выберите Домой .
Выберите Объединить и центрировать , чтобы центрировать заголовок.
Выберите Цвет заливки , чтобы открыть раскрывающийся список «Цвет заливки».
Выберите Синий, Акцент 1 из списка, чтобы изменить цвет фона в ячейках с A1 на E1 на темно-синий.
Выберите Цвет шрифта , чтобы открыть раскрывающийся список цветов шрифта.
Выберите Белый из списка, чтобы изменить цвет текста в ячейках с A1 на E1 на белый.
Выделите ячейки A2 и E2 на листе.
Выберите Цвет заливки , чтобы открыть раскрывающийся список «Цвет заливки».
Выберите в списке синий, акцент 1, светлее 80 , чтобы изменить цвет фона в ячейках с A2 на E2 на голубой.
Выделите ячейки от A4 до E14 на листе.
Выберите Центр , чтобы выровнять текст по центру в ячейках от A14 до E14.

Функции базы данных

Синтаксис : Dfunction (Database_arr, Field_str | num, Criteria_arr)

Где D function является одним из следующих:

DAVERAGE
DCOUNT
DCOUNTA
DGET
DMAX
DMIN
DPRODUCT
DSTDEV
DSTDEVP
DSUM
DVAR
DVARP

Тип : база данных

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

Database_arr – это диапазон, встроенный массив или массив, созданный выражением массива. Он построен таким образом, что каждая строка после строки 1 является записью базы данных, а каждый столбец – полем базы данных. Строка 1 содержит метки для каждого поля.
Field_str | num указывает, какой столбец (поле) содержит значения для усреднения. Это может быть выражено как имя поля (текстовая строка) или номер столбца, где крайний левый столбец будет представлен как 1.
Criteria_arr – это диапазон, встроенный массив или массив, сгенерированный выражением массива. Он структурирован таким образом, что первая строка содержит имена полей, к которым будет применяться критерий (критерии), а последующие строки содержат условные тесты.

Первая строка в Criteria определяет имена полей. Каждая вторая строка в Критериях представляет фильтр, который представляет собой набор ограничений для соответствующих полей. Ограничения описываются с использованием нотации Query-by-Example и включают в себя значение для сопоставления или оператор сравнения, за которым следует значение сравнения. Примеры ограничений: «Шоколад», «42», «> = 42» и «42». Пустая ячейка означает отсутствие ограничений на соответствующее поле.

Фильтр соответствует строке базы данных, если все ограничения фильтра (ограничения в строке фильтра) выполнены. Строка (запись) базы данных удовлетворяет критериям, если ей соответствует хотя бы один фильтр. Имя поля может появляться более одного раза в диапазоне критериев, чтобы разрешить несколько ограничений, которые применяются одновременно (например, температура> = 65 и температура

DGET – единственная функция базы данных, которая не агрегирует значения. DGET возвращает значение поля, указанного во втором аргументе (аналогично 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

Доброго здоровья, уважаемый читатель журнала «Web4job.ru”! В этой статье мы поговорим о том, как создать базу клиентов в программе Excel, рассмотрим способы ведения базы данных и как работать с таблицами.

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

Содержание статьи:

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

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

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

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

Можно разработать таблицу в форме, которая будет удобна вам.

В лист «Услуги» включать все услуги, предоставляемые вами клиенту, объединять их примерно по 10 видов и переносить в таблицу.

В лист «Мои клиенты» включить всех клиентов, с которыми вы работали. Он включает данные, указанные в таблице.

По количеству первой графы No п/п вы будете видеть, сколько у вас было клиентов.

Во вторую графу можно включать ФИО клиента или наименование организации.

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

Графа «Дата первого заказа» показывает, с какого времени вы с ним сотрудничаете.

Графа «Дата последнего заказа» дает возможность проследить, когда был последний заказ.

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

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

Как работать с простой базой

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

Расширенный способ ведения базы данных

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

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

Посмотрим видео Как создать базу клиентов в программе Excel

Как сделать и вести базу клиентов в Excel

Создание и работа с клиентской базой

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

Заключение

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

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

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

Понравилось? Поделитесь с друзьями!

Получите высокооплачиваемую интернет-профессию!

особенности создания, примеры и рекомендации

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Можно выделить всю информацию, содержащуюся на листе Excel, скопировать ее и перенести в другую программу. Для этого выделите данные, предназначенные для копирования, и щелкните правой кнопкой мышки. В контекстном меню нажимайте «Копировать». Затем переключитесь на Access, выберите вкладку «Таблица», группу «Представления» и смело кликайте на кнопку «Представление». Выбирайте пункт «Режим таблицы» и вставляйте информацию, щелкнув правой кнопкой мышки и выбрав «Вставить».

• Можно импортировать лист формата .xls (.xlsx). Откройте Access, предварительно закрыв Excel. В меню выберите команду «Импорт», и кликните на нужную версию программы, из которой будете импортировать файл. Затем нажимайте «ОК».

• Можно связать файл Excel с таблицей в программе Access. Для этого в «Экселе» нужно выделить диапазон ячеек, содержащих необходимую информацию, и, кликнув на них правой кнопкой мыши, задать имя диапазона. Сохраните данные и закройте Excel. Откройте «Аксесс», на вкладке под названием «Внешние данные» выберите пункт «Электронная таблица Эксель» и введите ее название. Затем щелкните по пункту, который предлагает создать таблицу для связи с источником данных, и укажите ее наименование.

Вот и все. Работа готова!

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

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

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

Где есть Power Pivot?

Где найти & преобразования (Power Query)?

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

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

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

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

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

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

Примечания:

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

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

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

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

Советы по уменьшению размера модели данных можно найти в статье Создание модели данных с эффективным использованием памяти с помощью Excel и Power Pivot.

Дополнительные сведения можно найти в статье Учебник: импорт данных в Excel и создание модели данных.

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

Следующим шагом является создание связей между таблицами, чтобы можно было извлечь данные из любого из них. Каждая таблица должна содержать первичный ключ или уникальный идентификатор поля, например идентификатор студента или номер класса. Самый простой способ — перетащить эти поля, чтобы соединить их в представлении схемы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.

См. также

Справочные руководства по & преобразованию и PowerPivot

Общие сведения о редакторе запросов (Power Query)

Создание модели данных с эффективным использованием памяти с помощью Excel и Power Pivot

Учебник: импорт данных в Excel и создание модели данных

Определение источников данных, используемых в модели данных книги

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

Как создать базу данных в Excel (8 простых шагов)

Не знаете, как создать простую базу данных в Excel? В этой статье я покажу, как создать базу данных в Excel всего за 8 простых шагов.

Считаете ли вы MS Access как сложным инструментом для использования в качестве базы данных? Тогда Excel — отличный инструмент для этого.

Давайте изучим технику…

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

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

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

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

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

Подробнее: Введение в концепции системы управления реляционными базами данных (СУБД)!

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

Шаг 1: Ввод данных

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

Итак, поля этой базы данных — это StdID, StdName, State, Age, Department и Class Teacher.

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

Я кое-что сделал. Позвольте мне показать, как я ввожу еще одну запись.

Скажем, это входные данные, которые необходимо вставить в базу данных:

StdID: 1030456042,

StdName: Jemmy Fox,

Штат Оклахома,

Возраст учащихся 25,

Департамент CSE,

А классным руководителем является г.Джон.

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

Шаг 2. Правильный ввод данных

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

Скажем, после последней строки я помещаю некоторые данные в строку 2 ^nd из нее:

StdID — 103457045,

StdName — Jackson,

State is New York,

Возраст 23,

Департамент ETE,

Классный руководитель — г.Бальмар.

Это четкая разбивка этой базы данных.

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

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

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

Подробнее: 10 главных преимуществ MS Access над другими СУБД

Шаг 3: Знайте, что строки называются записями

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

Все строки записи. Здесь я отметил несколько для ясности.

Шаг 4. Знайте, что столбцы называются полями

Все эти столбцы — это поля . Заголовки столбцов известны как Имена полей .

Итак, StdID , StdName , State , Age , Department и Class Teacher — это шесть имен полей этой базы данных.

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

Шаг 5: Создание таблицы

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

Откроется диалоговое окно «Создать таблицу» . Я беру то, что предлагает Excel.Да, у моей таблицы есть заголовки. Нажмите ОК , и таблица будет создана. Тада !!!

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

Шаг 6. Использование инструментов базы данных

Инструменты базы данных могут пригодиться при анализе и интерпретации данных. Вы можете и должны узнать больше об этих инструментах здесь.

Шаг 7: Расширение базы данных

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

Шаг 8: Завершение форматирования базы данных

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

Итак, поехали! Вы создали свою собственную базу данных в Excel (до тех пор, пока вы не освоите Access или у вас не закончится место и процессоры Excel).

Это снова краткое описание того, как создать базу данных в Excel.

Скачать рабочий файл

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

Database_in_Excel.xlsx

Подробнее:

Как использовать функции базы данных в Excel

Здравствуйте!
Добро пожаловать в мой блог о Excel! Мне потребовалось некоторое время, чтобы стать поклонником Excel.Но теперь я убежденный поклонник MS Excel. Я узнаю новые способы работы с Excel и делюсь здесь. Не только руководство по Excel, но и разделы по финансам, статистике, анализу данных и бизнес-аналитике. Будьте на связи!

.Учебное пособие по

: импорт данных в Excel и создание модели данных

Abstract: Это первое руководство из серии, предназначенное для ознакомления и ознакомления с работой с Excel и его встроенными функциями объединения и анализа данных. Эти руководства создают и уточняют книгу Excel с нуля, создают модель данных, а затем создают потрясающие интерактивные отчеты с помощью Power View. Учебники предназначены для демонстрации функций и возможностей Microsoft Business Intelligence в Excel, сводных таблицах, Power Pivot и Power View.

Примечание: В этой статье описываются модели данных в Excel 2013. Однако те же функции моделирования данных и Power Pivot, представленные в Excel 2013, также применимы к Excel 2016.

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

Уроки этой серии следующие:

Импорт данных в Excel 2013 и создание модели данных
Расширение отношений модели данных с помощью Excel, Power Pivot и DAX
Создание отчетов Power View на основе карт
Включение данных из Интернета и установка параметров отчета Power View по умолчанию
Справка по Power Pivot
Создание потрясающих отчетов Power View — часть 2

В этом руководстве вы начнете с пустой книги Excel.

В этом руководстве есть следующие разделы:

Импортировать данные из базы данных

Импортировать данные из электронной таблицы

Импортируйте данные с помощью копирования и вставки

Создать связь между импортированными данными

Контрольно-пропускной пункт и викторина

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

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

Импорт данных из базы данных

Мы начинаем это руководство с пустой книги. Цель этого раздела — подключиться к внешнему источнику данных и импортировать эти данные в Excel для дальнейшего анализа.

Начнем с загрузки данных из Интернета. Данные описывают олимпийские медали и представляют собой базу данных Microsoft Access.

Щелкните следующие ссылки, чтобы загрузить файлы, которые мы используем в этой серии руководств. Загрузите каждый из четырех файлов в легко доступное место, например Загрузки или Мои документы , или в новую папку, которую вы создаете:
> OlympicMedals.accdb Доступ к базе данных
> OlympicSports.xlsx книга Excel
> Population.xlsx Книга Excel
> DiscImage_table.xlsx Книга Excel
В Excel 2013 откройте пустую книгу.
Щелкните DATA> Get Ext

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

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

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

Где Power Pivot?

Где получить и преобразовать (Power Query)?

Excel 2016 и Excel для Microsoft 365 — Get & Transform (Power Query) интегрирован с Excel на вкладке Data .
Excel 2013 — Power Query — это надстройка, которая входит в состав Excel, но ее необходимо активировать. Перейдите к File > Options > Add-Ins , затем в раскрывающемся списке Manage в нижней части панели выберите COM Add-Ins > Go . Отметьте Microsoft Power Query для Excel , затем OK , чтобы активировать его.Вкладка Power Query будет добавлена на ленту.
Excel 2010 — Загрузите и установите надстройку Power Query. После активации на ленту будет добавлена вкладка Power Query .

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

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

В Excel 2016 и Excel для Microsoft 365 используйте Data > Get & Transform Data > Get Data для импорта данных из любого количества внешних источников данных, таких как текстовый файл, книга Excel, веб-сайт, Microsoft Access, SQL Server или другая реляционная база данных, содержащая несколько связанных таблиц.
В Excel 2013 и 2010 перейдите к Power Query > Получить внешние данные и выберите свой источник данных.
Excel предложит вам выбрать таблицу. Если вы хотите получить несколько таблиц из одного источника данных, установите флажок Включить выбор нескольких таблиц . Когда вы выбираете несколько таблиц, Excel автоматически создает для вас модель данных.
Выберите одну или несколько таблиц, затем щелкните Загрузить .
Если вам нужно отредактировать исходные данные, вы можете выбрать опцию Edit . Дополнительные сведения см. В разделе: Введение в редактор запросов (Power Query).

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

Примечания:

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

Создать базу данных в Access

С Access вы можете создать базу данных, не написав код и не будучи экспертом по базам данных.

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

Создать базу данных

Открытый доступ.
Если Access уже открыт, выберите Файл > Новый .
Выберите Пустая база данных или выберите шаблон.
Введите имя для базы данных, выберите расположение, а затем выберите Создать .
При необходимости выберите Включить содержимое на желтой панели сообщений при открытии базы данных.

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

Импорт данных из Excel

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

Дополнительные сведения см. В разделе Импорт данных в книге Excel или создание ссылок на них.