Как сделать в экселе базу данных: Как в excel сделать базу данных — Заработок в сети Интернет. Как заработать на дому в Интернете. Удаленная работа в сети.

Содержание

Создание базы данных в 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

Графические элементы

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

Вычисляемые значения

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

Значения TRUE или FALSE и -1 или 0

Если исходный лист или диапазон включает столбец, который содержит только значения TRUE или FALSE, в Access для этого столбца создается логическое поле, в которое вставляется значение -1 или 0. Если же исходный лист или диапазон включает столбец, который содержит только значения -1 и 0, в Access для этого столбца по умолчанию создается числовое поле. Чтобы избежать этой проблемы, можно изменить в ходе импорта тип данных поля на логический.

Многозначные поля

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

Усеченные данные

В случае усечения данных в столбце таблицы Access попытайтесь увеличить ширину столбца в режиме таблицы. Если не удается решить проблему с помощью этого способа, это означает, что объем данных в числовом столбце Excel слишком велик для конечного поля в Access. Например, в базе данных Access свойство FieldSize конечного поля может иметь значение Байт, а исходные данные могут содержать значение больше 255. Исправьте значения в исходном файле и повторите операцию импорта.

Формат отображения

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

После завершения импорта в логическом поле в режиме таблицы отображаются значения -1 и 0. Чтобы устранить эту проблему, необходимо после завершения импорта изменить значение свойства Формат этого поля на Да/Нет для отображения флажков.
Даты в длинном и среднем форматах отображаются в Access как краткие даты. Чтобы устранить эту проблему, откройте конечную таблицу в Access в режиме конструктора и измените свойство Формат поля даты на Длинный формат даты или Средний формат даты.

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

Повторяющиеся значения (нарушение уникальности ключа)

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

Значения дат, сдвинутые на 4 года

Значения полей дат, импортированных с листа Excel, оказываются сдвинуты на четыре года. В Excel для Windows используется система дат 1900, в которой даты представляются целыми числами от 1 до 65 380, соответствующими датам от 1 января 1900 г. до 31 декабря 2078 г. В Excel для Macintosh используется система дат 1904, в которой даты представляются целыми числами от 0 до 63 918, соответствующими датам от 1 января 1904 г. до 31 декабря 2078 г.

Прежде чем импортировать данные, измените систему дат для книги Excel или выполните после добавления данных запрос на обновление, используя выражение [имя поля даты] + 1462 для корректировки дат.

Пустые значения

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

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

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

Тип отсутствующих значений	Конечный объект импорта	Тип целевого поля	Решение
Текст	Новая таблица	Поле даты	Замените все текстовые данные значениями дат и повторите попытку импорта.
Текст	Существующая таблица	Числовое поле или поле даты	Замените все текстовые данные значениями, соответствующими типу данных конечного поля, и повторите попытку импорта.

Значения дат, замененные числовыми значениями

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

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

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

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

Числовые значения, замененные значениями даты

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

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

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

Управление данными с помощью Access или Excel

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

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

Локальные и внешние данные Access можно использовать для подключения к данным из различных внешних источников, чтобы просматривать, запрашивать и редактировать эти данные, не импортировать их. Например, в Access предусмотрены команды для подключения к существующим данным в базе данных Microsoft SQL Server, файлу dBASE или папкеOutlook, а также ко многим другим источникам данных. С помощью Excel можно подключаться к различным источникам данных, включая базы данных Access, SQL Server и analysis Services, текстовые и XML-файлы, а также источники данных ODBC и OLE DB. Однако изменить исходные данные с помощью Excel пользовательского интерфейса невозможно.

Access и Excel предоставляют команды для подключения к данным в Windows SharePoint Services списках. Однако Excel обеспечивает доступ только для чтения к SharePoint спискам. в то время как Access позволяет читать данные из списков и записывать их SharePoint списки.

Целостность и гибкость данных Уникальные идентификаторы обеспечивают целостность данных и гарантируют отсутствие в двух строках (или записях) одинаковых данных. Уникальные идентификаторы также обеспечивают самый быстрый способ извлечения данных при поиске или сортировке данных. В Access тип данных «Автономная» можно использовать для автоматического создания уникальных идентификаторов для каждой записи. Затем с помощью этих идентификаторов можно связать записи из одной таблицы с одной или несколько записями в другой таблице.

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

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

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

QuintaDB представляет систему импорта данных из Excel и CSV файлов.

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

Импорт Excel в базу данных QuintaDB

Создайте Excel файл с данными;

После регистрации, создайте проект и нажмите «Импорт данных».

QuintaDB создаст форму, где полями будут названия колонок в Excel.

QuintaDB не импортирует не все типы полей. Поэтому, сразу после импорта, перейдите в меню «Конструктор форм» и обновите типы полей (колонок).

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

Перейдите в меню Записи и нажмите на ссылку импорт. Затем выберите нужный файл и загрузите его.

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

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

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

Импорт CSV в базу данных QuintaDB.

Если ваши данные сохранены в CSV файле, вы легко сможете импортировать их в базу данных QuintaDB. Последовательность действий та же, что и при импорте из Excel (смотрите скриншоты). Единственное, что нужно обязательно учитывать формат этого файла.

Данные должны быть в виде:

75281,676,A541,Вася Пупкин,Москва,Россия. Т.е. значения должны быть разделены запятой.

Если данные будут в виде:

75281;676;A541;Вася Пупкин;Москва;Россия — то система не сможет импортировать такой файл.

Кодировка: UTF-8.

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

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

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

Поделиться обсуждением в социальных сетях: Tweet

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

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

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

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

Откройте Excel Sheet и начните вводить данные. Заголовки, например, имя учащегося, номер учащегося, имя родителей, дата рождения, предметы и оценка.

Начиная с заголовков.

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

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

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

Быстрый способ добавления простых данныхДобавление данных inn.

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

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

С другой стороны, столбец – это поле, в которое добавляются заголовки, заголовки и определение того, что представляет собой «запись».

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

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

Выберите данные, которые вы хотите представить в виде таблицы.выберите ваши данные
Перейти к вставкеНайдите «Вставить» на своей странице в Excel.
Нажмите на таблицуСоздайте таблицу
Это диалоговое окно появится, если есть другие данные, помимо выбранных столбцов и строк на электронной таблице, которые вы хотите добавить в таблицу, вы можете просто изменить числа и алфавиты для строк и столбцов. Если вы отметите опцию My Table has headers, ваши заголовки станут частью вашей таблицы.
Щелкнув OK, ваша электронная таблица теперь будет выглядеть так.Ваша таблица для всех данных, которые вы только что ввели.
Все стрелки в первой строке показывают вам варианты, с помощью которых вы можете сортировать дату. Вы можете расположить их в алфавитном порядке, вы можете выбрать конкретных студентов.Изучение вариантов. Вы даже можете установить или снять флажки, чтобы включить или исключить этого конкретного учащегося из таблицы.

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

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

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

Место для дополнительных данных в вашей таблице.

Вы можете добавить сюда больше данных.

База данных в Excel | Блог Александра Воробьева

Опубликовано 29 Апр 2014
Рубрика: Справочник Excel | 19 комментариев

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример.

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

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

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

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

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

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

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

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

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

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

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

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

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

Итоги.

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

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

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

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

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

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

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

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

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

Вопросы и замечания пишите в комментариях.

Другие статьи автора блога

На главную

Статьи с близкой тематикой

Отзывы

Сбор базы данных и сделать таблицы в Excel за 3000 рублей

Цена договорная

Верстка альбомов

Пришлю образец в псд и фотографии, нужно будет сделать также, вставить фото в псд, заказов будет много в ближайшие 2 недели

Елена Г.

Цена договорная

Монтажер

На постоянную работу в продакшн студию требуется видеомонтажер. Работа удалённая, можно работать из дома. Задачи: Ежедневный монтаж роликов по техзаданиям от 15 секунд до 1 минуты. В день от 2х до 5 роликов…

Алексей Д.

1 700 руб

Создание бланка pdf

Нужно создать pdf- файл (тип- форма для заполнения) по образцу, текст с меня также в образце . Суть вопроса в том чтобы сделать редактируемую копию файла ,который в оригинале не редактируемый . В задании…

Руслан Я.

Цена договорная

Нужен художник

Нужно нарисовать портрет человека по фото на памятник, человека в живых уже нет, есть 2 не очень качественные фотографии и с них нужно написать портрет

Екатерина

Цена договорная

Шаблон для PDF презентаций с логотипом и подписью

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

Dager

Как создать базу данных в 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,

Стандартное имя: Джемми Фокс,

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

Студенческий возраст 25,

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

А классный руководитель — г.Джон.

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

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

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

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

Стандартный идентификатор: 103457045,

Стандартное имя — Джексон,

Штат Нью-Йорк,

Возраст 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 шагов, чтобы понять, как эта база данных была создана и как ее можно использовать!

База данных_в_Excel. xlsx

Подробнее:

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

Как создать базу данных с возможностью поиска в Excel | Small Business

Автор: Nicky LaMarco Обновлено 21 февраля 2019 г.

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

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

Как работает реляционная база данных?

Excel структурирован таким образом, чтобы обеспечить его простую совместимость с базами данных. По крайней мере, согласно PC World, база данных — это просто набор взаимосвязанных элементов. Это в значительной степени то, что представляет собой электронная таблица. Когда элементы в базе данных связаны, они создают записи в группах из нескольких записей. Одна запись может быть эквивалентом строки в электронной таблице, а набор записей может быть эквивалентом таблицы в электронной таблице. Связь сложно игнорировать.

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

Пример базы данных

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

Основная база данных будет содержать все сведения о ваших водительских правах.Будет одна или несколько дочерних баз данных, в которых будет больше информации о конкретных людях, которые будут идентифицированы с помощью ключевого поля. У некоторых могут быть нарушения правил вождения человека, в то время как у других могут быть адреса, которые у них были ранее, и так далее. На языке баз данных такой вид отношений известен как отношения «один ко многим» , потому что у каждого драйвера может быть много разных адресов и нарушений. Однако адреса и нарушения могут быть связаны только с одним драйвером.
Существуют и другие типы отношений, такие как «один к одному» и «многие ко многим» — например, база данных с клиентами и ставками дисконтирования, которыми они пользуются. Поскольку каждый покупатель может иметь только одну скидку за раз, это взаимно однозначные отношения. Если бы база данных состояла из клиентов и продуктов, которые они покупают, тогда у клиентов могло бы быть несколько продуктов, и продукты могли бы быть приобретены более чем одним покупателем, что было бы отношением «многие ко многим».
Для чего нужна реляционная база данных?
Самая очевидная причина, по которой вам может понадобиться такая база данных, — это избегать повторения ваших данных в каждой электронной таблице. Это может потребовать значительных ресурсов, особенно с точки зрения времени. Однако наиболее важной причиной использования базы данных является то, что она позволяет запрашивать данные с помощью фильтров, чтобы найти конкретную информацию и использовать ее для создания отчетов.
Как создать простую базу данных в Excel
Первый шаг к созданию таблицы с возможностью поиска — это описание ваших данных. Вам понадобятся метки для описания ваших данных, и они войдут в первую строку вашей таблицы.Сделаем это самой первой строкой вашей таблицы. Назначение заголовков почти такое же, как и у полей в надлежащей базе данных. Каждая строка в вашей электронной таблице будет определять одну запись базы данных, в то время как каждый столбец будет иметь значения, которые подпадают под одну категорию.
Заголовок первой строки необходимо закрепить. Для этого щелкните заголовок строки, который находится у правого края второй строки. Перейдите на вкладку «Просмотр» на ленте в верхней части приложения Excel и найдите на вкладке раздел «Закрепить области».Ниже находится стрелка. Нажмите на эту стрелку и выберите в появившемся раскрывающемся меню параметр «Закрепить верхнюю строку». Это будет означать, что верхняя строка всегда будет видна даже при прокрутке таблицы. Таким образом вы узнаете, к какой категории данных все относится.
Введите свои данные

Пришло время ввести свои данные. Сделайте это в таком количестве строк, которое, по вашему мнению, вам понадобится. Клавиши со стрелками должны помочь вам перемещаться по электронной таблице, в то время как подтверждение ввода в любую ячейку должно быть таким же простым, как нажатие клавиши ввода.Возможно, вы ввели свои данные в другой документ, где они разделены символом табуляции, например в Microsoft Word. В этом случае вы можете просто скопировать и вставить его в свою электронную таблицу. Чтобы вставить его в базу данных, щелкните ячейку с меткой A2 и нажмите Ctrl + V на клавиатуре. Данные будут вставлены под вашими заголовками.
В верхнем левом углу листа находится кнопка, которую можно использовать для выбора всех ячеек в электронной таблице.Эта кнопка находится там, где пересекаются заголовки столбца и строки. Нажмите на этой кнопке, и все ячейки на вашем листе будут выделены. На ленте переключитесь на вкладку «Данные». Там вы найдете группу с надписью «Сортировка и фильтрация». Вы найдете кнопку в виде воронки, которая называется «Фильтр» . Щелкните по нему .
Использование фильтров в столбцах

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

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

Если вы хотите отключить фильтрацию, нажмите кнопку фильтра в верхнем правом углу заголовка данного столбца, фильтры которого вы хотите отключить. Таким образом вы отключите фильтрацию для этого столбца, не затрагивая остальную часть таблицы.Если вы хотите отключить всю фильтрацию, просто выберите опцию «Очистить» , и все ваши данные вернутся в исходное состояние.
Как создать базу данных в Excel
В каждом бизнесе есть цифры, но не каждый генеральный директор разбирается в математике. Вот почему владельцы малого бизнеса часто передают бухгалтерский учет на аутсорсинг или позволяют бухгалтеру заниматься этим.
Однако есть много других способов обработки данных для повышения эффективности операций.Кроме того, вы, вероятно, не захотите постоянно бегать к кому-нибудь за помощью.
Вот где пригодятся базовые знания о базах данных Excel.
Excel — один из тех инструментов, которые есть почти у каждого на рабочем столе, и вы можете пользоваться его преимуществами, не будучи мастером чисел.
Базы данных
Excel предоставляют простой способ анализировать данные (например, данные о продажах и прогнозы), просматривать различные расчеты и сравнивать различные наборы данных. Конечно, есть продвинутые формулы и функции, если вы погрузитесь глубже и потратите время на то, чтобы стать профессионалом.Но для многих владельцев бизнеса простое знание того, как создать базу данных в Excel, даст им много возможностей.
Давайте рассмотрим пошаговый тур по использованию Excel для создания базы данных.
Просто чтобы вы знали
Узнайте, как максимально использовать ваши данные с помощью подробного руководства Microsoft Excel от JotForm. Вы даже можете интегрировать JotForm с Excel для синхронизации данных формы с вашими электронными таблицами!
1. Создайте электронную таблицу данных
Начните с открытия нового листа Excel.
Лист Excel состоит из вертикальных столбцов и горизонтальных строк, каждая из которых представляет отдельную строку данных. Столбцы обозначены буквами, а строки — числами.
В первой строке должны отображаться имена для каждого столбца. Щелкните ячейку в первой строке и начните вводить текст, чтобы вставить имена заголовков. Кнопка Tab — это быстрый способ перемещаться по таблице; каждый раз, когда вы нажимаете кнопку Tab, вы переходите к следующему столбцу в строке. Когда вы дойдете до конца строки, курсор переместится в первый столбец следующей строки и так далее.
2. Добавить или импортировать данные
Вы можете добавлять данные двумя способами: вводя их вручную или импортируя данные из других файлов, таких как текстовые или CSV-файлы.
Чтобы добавить данные вручную, щелкните ячейку и начните вводить текст. Чтобы вставить новую строку или столбец, перейдите на вкладку Home и найдите раздел Cells . Там вы найдете стрелку раскрывающегося списка Insert . Щелкнув стрелку, вы можете выбрать элемент, который хотите вставить (например, столбец, строку и т. Д.).).
Чтобы импортировать данные из внешних источников, щелкните вкладку Data , перейдите в раздел Get / Transform Data и выберите место назначения источника. Для успешного импорта данных необходимо убедиться, что данные имеют правильное форматирование, а форматирование зависит от типа исходного файла.
3. Преобразуйте данные в таблицу
Чтобы получить функциональность базы данных, вы должны преобразовать данные в таблицу.
Щелкните мышью по любой ячейке с данными, которые вы ввели, а затем щелкните Insert > Table. Появится всплывающее окно, показывающее поля данных, которые необходимо включить в таблицу. В таблицу автоматически войдут все строки и столбцы блока. Важно не оставлять пустых строк в блоке данных — они действуют как «разрывы» и указывают программе, что они не являются частью таблицы.
Если в диалоговом окне отображаются правильные поля и заголовки в порядке, нажмите ОК.
Помните, что любые пустые строки , а не автоматически интегрируются в таблицу.Чтобы включить новые введенные данные в таблицу, наведите указатель мыши на маленький треугольник в правом нижнем углу текущей таблицы и перетащите границу таблицы вниз, чтобы включить новые строки данных.
4. Настройте дизайн таблицы и присвойте имя
Есть несколько вариантов оформления стола, но не тратьте на это много времени. Цель игры — сделать ваш стол легко просматриваемым.
Щелкните вкладку Design в главном меню. Измените цвета таблицы быстрым щелчком по одной из предопределенных цветовых тем.По умолчанию таблица имеет полосатые строки, то есть каждая вторая строка имеет фоновую заливку. Многим пользователям нравится этот стиль, так как он упрощает просмотр данных. Снимите отметку с поля Banded Rows , если вы предпочитаете чистый белый стол.
В крайней левой части панели инструментов Design вы можете изменить имя таблицы. Дайте ему конкретное имя, которое легко идентифицировать. Это поможет вам в будущем, когда вы будете работать со многими базами данных Excel.
5. Взаимодействие с данными
После того, как ваш стол настроен, самое время начать с ним взаимодействовать и получать нужную информацию.В программе Excel предопределено несколько формул, в том числе Среднее, Счетчик, и Сумма, и другие.
Щелкните ячейку в основании любого столбца данных, с которым вы хотите работать. В ячейке отображается интерактивная стрелка, открывающая раскрывающееся меню с доступными формулами. Выберите формулу, и расчет автоматически появится в ячейке.
В приведенном ниже примере мы вычисляем среднее количество проданных единиц. Формула Среднее значение — вариант в раскрывающемся меню.
Среднее количество проданных единиц рассчитывается и отображается в ячейке.
Существует множество способов использования Excel для выполнения вычислений и получения полезных данных для принятия решений. Теперь, когда вы создали базу данных в Excel, вы можете изучить все доступные функции и возможности.
Выйти за рамки Excel: работа с таблицами JotForm
Хотя Excel уже давно является фаворитом для обработки чисел, такие инструменты, как JotForm Tables, могут дать вам гораздо больше, чем электронные таблицы.JotForm Tables предоставляет полное рабочее пространство для управления, отслеживания и обработки данных, а также интеграции их в рабочие процессы и сотрудничество для более плавной работы.
Если вы работаете с базами данных Excel, вы можете легко импортировать их в таблицы JotForm и продолжить управление данными там. И как бесплатный инструмент, JotForm Tables не требует инвестиций и лицензий, необходимых для работы с Excel.
Для компаний, уже использующих JotForm, JotForm Tables — следующий шаг, поскольку он позволяет создавать эффективные стратегии на основе данных, чтобы ваш бизнес мог действительно преуспеть.
Эта статья изначально опубликована 10 ноября 2020 г. и обновлена 6 апреля 2021 г.
Знакомство с тремя типами баз данных электронных таблиц Excel
Excel предлагает три основных способа упорядочения данных в электронной таблице, чтобы вы могли использовать ее в качестве базы данных с формулами рабочего листа:
Простые (или «серая ячейка») таблицы , которые я использовал с Excel 2.0.
Таблицы Excel , представленные в Excel 2007.
Сводные таблицы с табличным макетом отчета, представленные в Excel 2010.
Эксперты по базам данных, вероятно, будут оскорблены, если я назову их «базой данных». В конце концов, эти три базы данных электронных таблиц легко настроить и использовать… совсем не как «настоящую» базу данных. Даже в этом случае эти базы данных работают как настоящая база данных для ваших формул Excel. Если ваши отчеты и анализы могут получать данные из одного из этих типов баз данных электронных таблиц, вы можете значительно улучшить свои отчеты и анализ.
В этой статье я расскажу о каждом типе базы данных электронных таблиц. Затем в следующих статьях я расскажу подробнее.
База данных электронных таблиц 1: простые таблицы
Более 20 лет, до Excel 2007, пользователи Excel могли использовать только простые таблицы в качестве базы данных электронных таблиц. Этот тип базы данных может быть двух стилей: горизонтальный и вертикальный.
Базы данных электронных таблиц с простыми таблицами, горизонтальный стиль
Горизонтальные базы данных электронных таблиц имеют даты в одной строке заголовка, как показано на рисунке ниже.
Обычно в столбцах даты используются «сегменты» по месяцам, но они могут использовать любой требуемый период времени. А столбец «Коды» может содержать номера счетов Главной книги, артикулы, коды сотрудников и т. Д.
Серые строки и столбцы — наиболее очевидная особенность простых таблиц. Эти пограничные ячейки служат очень важной цели.
С помощью базы данных с серыми ячейками вы привязываете имена диапазонов и формулы к серым строкам и столбцам. Затем вы всегда вставляете новые данные между этими строками и столбцами.
Вот почему необходимы серые строки:
Когда вы настраиваете простую таблицу, вы определяете имена диапазонов для всех диапазонов ключей, а затем в ваших формулах для ссылки на таблицу используются только эти имена диапазонов, а не ссылки на ячейки. И все эти имена диапазонов ссылаются только на ячейки с серой рамкой, а не на ячейки с данными.
Для иллюстрации, эта база данных находится на листе с именем Horiz и использует эти четыре имени диапазона:
Коды = Horiz! $ A $ 3: $ A $ 10
Desc = Horiz! $ B $ 3: $ B $ 10
Даты = Horiz! $ C $ 2: $ G $ 2
Данные = Horiz! $ C $ 3: $ G $ 10
Вот критическое требование для использования этого дизайна: когда вы добавляете строки или столбцы данных в простую таблицу, вы всегда вставляете строки и столбцы между серыми ячейками. Таким образом, вы всегда будете знать, что все формулы, использующие таблицу, «увидят» ваши новые данные.
Базы данных электронных таблиц с простыми таблицами, вертикальный стиль
На этом рисунке показана вертикальная база данных Excel с простой таблицей. Как и в большинстве баз данных, каждое поле состоит только из одного столбца данных.
В качестве краткого описания я называю это вертикальной базой данных, потому что ее даты отображаются вертикально.
Здесь имена легко присваиваются. Просто выберите диапазон A2: D12.А потом…
В классическом Excel выберите «Вставить», «Имя», «Создать»…
В новом Excel выберите «Формулы», «Определенные имена», «Создать из выбранного»…
… а затем в диалоговом окне «Создание имен» убедитесь, что указана только верхняя строка; затем выберите ОК.
База данных электронных таблиц 2: таблицы Excel
В
Excel 2007 появилась новая важная функция: таблицы Excel.
Когда вы настраиваете таблицу Excel (или просто «Таблицу»), Excel автоматически присваивает имя каждому столбцу данных.Кроме того, когда вы добавляете в таблицу соседние строки или столбцы, Excel автоматически расширяет таблицу, чтобы включить в нее новые данные.
В этом отличие от простых таблиц, где мы должны вручную выполнять действия, необходимые для добавления граничных ячеек и определения имен диапазонов.
Чтобы создать эту таблицу, настройте данные, расположенные так, как вы видите в этом примере; выберите заголовки и данные; выберите Вставка, Таблицы, Таблица; в диалоговом окне «Создать таблицу» убедитесь, что установлен флажок «Моя таблица имеет заголовки»; затем выберите ОК.
Excel автоматически форматирует таблицу и называет ее так же, как именует новые рабочие листы.То есть он присваивает имена, такие как Таблица1, Таблица2 и т. Д. И, как рабочие листы, вы можете переименовывать каждую таблицу.
В формулах, когда вы ссылаетесь на столбец данных, который находится в таблице или простой таблице, вы используете имена, а не ссылки на ячейки. Например, если вы хотите просуммировать столбец «Сумма», ваши формулы будут такими:
Простая таблица : = СУММ (сумма)
Excel Таблица : = СУММ (Таблица1 [Сумма])
Конечно, если вы переименуете Table1, ссылка на Table1 во второй формуле обновится автоматически.
База данных электронных таблиц 3: Табличный макет для сводных таблиц
Сводные таблицы
обладают рядом существенных преимуществ, поэтому они так популярны. Но у них также есть существенные ограничения, поэтому я редко использовал их в прошлом.
Преимущества очевидны. Сводные таблицы предоставляют пользователям Excel мощную возможность исследовать реляционные данные в Excel и возвращать отсортированные, обобщенные и отфильтрованные фрагменты данных в электронные таблицы. Я не знаю другого продукта, который обладал бы такой мощностью.
С другой стороны, с моей точки зрения, сводные таблицы всегда казались просто генератором отчетов, привязанным к Excel. Они предлагают множество возможностей отчетности, но только одна функция электронной таблицы — GETPIVOTDATA — позволяющая функциям таблицы использовать данные сводной таблицы. Поэтому пользователям Excel — опять же, на мой взгляд, — всегда приходилось работать намного усерднее, чем нам, чтобы использовать данные из одной или нескольких сводных таблиц в стандартных отчетах Excel.
Но, наконец, в Excel 2010 Microsoft добавила большинство функций, необходимых пользователям 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, которые можно публиковать, защищать и совместно использовать.
Уроки этой серии следующие:
org/ItemList»>
Импорт данных в Excel 2013 и создание модели данных
Расширение отношений модели данных с помощью Excel, Power Pivot и DAX
Создание отчетов Power View на основе карт
Включение данных из Интернета и установка параметров отчета Power View по умолчанию
Справка по Power Pivot
org/ListItem»>
Создание потрясающих отчетов Power View — часть 2
В этом руководстве вы начнете с пустой книги Excel.
В этом учебном пособии есть следующие разделы:
В конце этого урока вы можете пройти тест, чтобы проверить свои знания.
В этой серии руководств используются данные, описывающие олимпийские медали, принимающие страны и различные олимпийские спортивные соревнования. Мы предлагаем вам пройти каждое руководство по порядку. Кроме того, в руководствах используется Excel 2013 с включенным Power Pivot. Для получения дополнительной информации о Excel 2013 щелкните здесь. Чтобы получить инструкции по включению Power Pivot, щелкните здесь.
Импорт данных из базы данных
Мы начинаем это руководство с пустой книги. Цель этого раздела — подключиться к внешнему источнику данных и импортировать эти данные в Excel для дальнейшего анализа.
Начнем с загрузки данных из Интернета. Данные описывают олимпийские медали и представляют собой базу данных Microsoft Access.
org/ListItem»>
Щелкните следующие ссылки, чтобы загрузить файлы, которые мы используем в этой серии руководств.Загрузите каждый из четырех файлов в легкодоступное место, например, Загрузки или Мои документы , или в новую папку, которую вы создаете:
> OlympicMedals.accdb База данных доступа
> OlympicSports.xlsx Рабочая тетрадь Excel
> Population.xlsx Рабочая тетрадь Excel
> DiscImage_table.xlsx Книга Excel
В Excel 2013 откройте пустую книгу.
Щелкните ДАННЫЕ> Получить внешние данные> Из доступа .Лента динамически настраивается в зависимости от ширины книги, поэтому команды на ленте могут немного отличаться от представленных на следующих экранах. Первый экран показывает ленту, когда книга широкая, второе изображение показывает книгу, размер которой был изменен, чтобы занимать только часть экрана.
Выберите загруженный файл OlympicMedals.accdb и щелкните Открыть .Появится следующее окно выбора таблицы, в котором отображаются таблицы, найденные в базе данных. Таблицы в базе данных похожи на рабочие листы или таблицы в Excel. Установите флажок Разрешить выбор нескольких таблиц и выберите все таблицы. Затем нажмите ОК .
Откроется окно Импорт данных.
Примечание: Обратите внимание на флажок в нижней части окна, который позволяет вам Добавить эти данные в модель данных , показанную на следующем экране. Модель данных создается автоматически при одновременном импорте двух или более таблиц или работе с ними. Модель данных объединяет таблицы, обеспечивая обширный анализ с использованием сводных таблиц, Power Pivot и Power View. Когда вы импортируете таблицы из базы данных, существующие отношения базы данных между этими таблицами используются для создания модели данных в Excel. Модель данных прозрачна в Excel, но вы можете просматривать и изменять ее напрямую с помощью надстройки Power Pivot. Модель данных обсуждается более подробно позже в этом руководстве.

Выберите параметр PivotTable Report , который импортирует таблицы в Excel и подготавливает сводную таблицу для анализа импортированных таблиц, и нажмите OK .
После импорта данных создается сводная таблица с использованием импортированных таблиц.
После импорта данных в Excel и автоматического создания модели данных вы готовы исследовать данные.
Изучите данные с помощью сводной таблицы
Изучать импортированные данные легко с помощью сводной таблицы. В сводной таблице вы перетаскиваете поля (аналогичные столбцам в Excel) из таблиц (например, таблицы, которые вы только что импортировали из базы данных Access) в разные области сводной таблицы, чтобы настроить способ представления данных. Сводная таблица имеет четыре области: ФИЛЬТРЫ, , КОЛОННЫ, , СТРОК, и ЗНАЧЕНИЯ, .
Может потребоваться некоторое экспериментирование, чтобы определить, в какую область следует перетащить поле. Вы можете перетаскивать сколько угодно полей из таблиц, пока сводная таблица не представит ваши данные в том виде, в котором вы хотите их видеть. Не стесняйтесь исследовать, перетаскивая поля в разные области сводной таблицы; на базовые данные не влияет расположение полей в сводной таблице.
Давайте рассмотрим данные об олимпийских медалях в сводной таблице, начиная с олимпийских медалистов, сгруппированных по дисциплинам, типам медалей и стране или региону спортсмена.
В Полях сводной таблицы разверните таблицу Медали , щелкнув стрелку рядом с ней. Найдите поле NOC_CountryRegion в развернутой таблице Медали и перетащите его в область COLUMNS . НОК означает национальные олимпийские комитеты, которые являются организационной единицей страны или региона.
Затем из таблицы Disciplines перетащите Discipline в область ROWS .
Давайте отфильтруем дисциплины, чтобы отобразить только пять видов спорта: стрельба из лука, прыжки в воду, фехтование, фигурное катание и конькобежный спорт. Это можно сделать в области полей сводной таблицы или с помощью фильтра Ярлыки строк в самой сводной таблице.
Щелкните в любом месте сводной таблицы, чтобы убедиться, что она выбрана. В списке полей сводной таблицы , где развернута таблица Дисциплины , наведите указатель мыши на его поле Дисциплина, и справа от поля появится стрелка раскрывающегося списка.Щелкните раскрывающийся список, нажмите (Выбрать все) , чтобы удалить все выбранные элементы, затем прокрутите вниз и выберите Стрельба из лука, Дайвинг, Фехтование, Фигурное катание и Скоростное катание на коньках. Нажмите ОК .
Или в разделе Ярлыки строк сводной таблицы щелкните раскрывающийся список рядом с Ярлыки строк в сводной таблице, щелкните (Выбрать все) , чтобы удалить все выбранные элементы, затем прокрутите вниз и выберите Стрельба из лука, Дайвинг, Фехтование, Фигурное катание и конькобежный спорт. Нажмите ОК .
В Полях сводной таблицы из таблицы Медали перетащите Медаль в область ЗНАЧЕНИЙ . Поскольку значения должны быть числовыми, Excel автоматически изменяет медаль на Количество медалей .
В таблице Медали снова выберите Медаль и перетащите ее в область ФИЛЬТРЫ .
Давайте отфильтруем сводную таблицу, чтобы отобразить только те страны или регионы, у которых общее количество медалей превышает 90. Вот как.
org/ListItem»>
В сводной таблице щелкните раскрывающийся список справа от Ярлыки столбцов .
Выберите Value Filters и выберите Greater Than….
Введите 90 в последнее поле (справа).Нажмите ОК .
Ваша сводная таблица выглядит как на следующем экране.
С небольшими усилиями теперь у вас есть базовая сводная таблица, которая включает поля из трех разных таблиц. Что сделало эту задачу настолько простой, так это существовавшие ранее отношения между таблицами. Поскольку связи таблиц существовали в исходной базе данных, и поскольку вы импортировали все таблицы за одну операцию, Excel мог воссоздать эти связи таблиц в своей модели данных.
Но что, если ваши данные происходят из разных источников или импортируются позже? Как правило, вы можете создавать отношения с новыми данными на основе совпадающих столбцов. На следующем шаге вы импортируете дополнительные таблицы и узнаете, как создавать новые отношения.
Импорт данных из электронной таблицы
Теперь давайте импортируем данные из другого источника, на этот раз из существующей книги, а затем определим отношения между нашими существующими данными и новыми данными. Взаимосвязи позволяют анализировать коллекции данных в Excel и создавать интересные и захватывающие визуализации на основе импортируемых данных.
Начнем с создания пустого листа, а затем импортируем данные из книги Excel.
Вставьте новый лист Excel и назовите его Sports .
Перейдите в папку, содержащую загруженные файлы примеров данных, и откройте OlympicSports.xlsx .
org/ListItem»>
Выберите и скопируйте данные в Sheet1 . Если вы выбрали ячейку с данными, например ячейку A1, вы можете нажать Ctrl + A, чтобы выбрать все смежные данные. Закройте книгу OlympicSports.xlsx.
На листе Sports поместите курсор в ячейку A1 и вставьте данные.
Когда данные все еще выделены, нажмите Ctrl + T, чтобы отформатировать данные в виде таблицы.Вы также можете отформатировать данные в виде таблицы с ленты, выбрав HOME> Форматировать как таблицу . Поскольку данные имеют заголовки, выберите Моя таблица имеет заголовки в появившемся окне Создать таблицу , как показано здесь.
Форматирование данных в виде таблицы имеет много преимуществ. Вы можете присвоить таблице имя, чтобы ее было легко идентифицировать. Вы также можете устанавливать связи между таблицами, что позволяет исследовать и анализировать сводные таблицы, Power Pivot и Power View.
Назовите таблицу. В ИНСТРУМЕНТЫ ТАБЛИЦ> ДИЗАЙН> Свойства найдите поле Имя таблицы и введите Sports . Рабочая книга выглядит как на следующем экране.
Сохраните книгу.
Импорт данных с помощью копирования и вставки
Теперь, когда мы импортировали данные из книги Excel, давайте импортируем данные из таблицы, которую мы находим на веб-странице, или из любого другого источника, из которого мы можем скопировать и вставить в Excel. На следующих шагах вы добавите города-организаторы Олимпиады из таблицы.
Вставьте новый лист Excel и назовите его Hosts .
Выделите и скопируйте следующую таблицу, включая ее заголовки.
Город
NOC_CountryRegion
Альфа-2 Код
Выпуск
Сезон
Мельбурн / Стокгольм
AUS
AS
1956
Лето
Сидней
AUS
AS
2000
Лето
Инсбрук
AUT
AT
1964
Зима
Инсбрук
AUT
AT
1976
Зима
Антверпен
BEL
BE
1920
Лето
Антверпен
BEL
BE
1920
Зима
Монреаль
CAN
CA
1976
Лето
Лейк-Плэсид
CAN
CA
1980
Зима
Калгари
CAN
CA
1988
Зима
ул. Мориц
SUI
SZ
1928
Зима
Санкт-Мориц
SUI
SZ
1948
Зима
Пекин
ЧН
CH
2008
Лето
Берлин
GER
GM
1936
Лето
Гармиш-Партенкирхен
GER
GM
1936
Зима
Барселона
ESP
SP
1992
Лето
Хельсинки
FIN
FI
1952
Лето
Париж
FRA
FR
1900
Лето
Париж
FRA
FR
1924
Лето
Шамони
FRA
FR
1924
Зима
Гренобль
FRA
FR
1968
Зима
Альбервиль
FRA
FR
1992
Зима
Лондон
ГБР
Великобритания
1908
Лето
Лондон
ГБР
Великобритания
1908
Зима
Лондон
ГБР
Великобритания
1948
Лето
Мюнхен
GER
DE
1972
Лето
Афины
GRC
ГР
2004
Лето
Кортина д’Ампеццо
ITA
IT
1956
Зима
Рим
ITA
IT
1960
Лето
Турин
ITA
IT
2006
Зима
Токио
JPN
JA
1964
Лето
Саппоро
JPN
JA
1972
Зима
Нагано
JPN
JA
1998
Зима
Сеул
KOR
кс
1988
Лето
Мексика
МЕХ
MX
1968
Лето
Амстердам
NED
NL
1928
Лето
Осло
NOR
НЕТ
1952
Зима
Лиллехаммер
NOR
НЕТ
1994
Зима
Стокгольм
SWE
SW
1912
Лето
Сент-Луис
США
США
1904
Лето
Лос-Анджелес
США
США
1932
Лето
Лейк-Плэсид
США
США
1932
Зима
Скво-Вэлли
США
США
1960
Зима
Москва
УРС
RU
1980
Лето
Лос-Анджелес
США
США
1984
Лето
Атланта
США
США
1996
Лето
Солт-Лейк-Сити
США
США
2002
Зима
Сараево
ЮГ
Ю
1984
Зима
org/ItemList»>
В Excel поместите курсор в ячейку A1 на листе Hosts и вставьте данные.
Отформатируйте данные в виде таблицы. Как описано ранее в этом руководстве, вы нажимаете Ctrl + T, чтобы отформатировать данные в виде таблицы, или из HOME> Format as Table . Поскольку данные имеют заголовки, выберите Моя таблица имеет заголовки в появившемся окне Создать таблицу .
Назовите таблицу. В TABLE TOOLS> DESIGN> Properties найдите поле Table Name и введите Hosts .
org/ListItem»>
Выберите столбец «Издание» и на вкладке HOME отформатируйте его как Число с 0 десятичными знаками.
Сохраните книгу. Ваша рабочая тетрадь выглядит как на следующем экране.
Теперь, когда у вас есть книга Excel с таблицами, вы можете создавать связи между ними. Создание связей между таблицами позволяет объединить данные из двух таблиц.
Создать связь между импортированными данными
Вы можете сразу начать использовать поля в своей сводной таблице из импортированных таблиц. Если Excel не может определить, как включить поле в сводную таблицу, необходимо установить связь с существующей моделью данных. В следующих шагах вы узнаете, как создать связь между данными, импортированными из разных источников.
На Sheet1 в верхней части Поля сводной таблицы щелкните Все , чтобы просмотреть полный список доступных таблиц, как показано на следующем экране.
Прокрутите список, чтобы увидеть только что добавленные таблицы.
org/ListItem»>
Разверните Sports и выберите Sport , чтобы добавить его в сводную таблицу. Обратите внимание, что Excel предлагает вам создать связь, как показано на следующем экране.

Это уведомление появляется из-за того, что вы использовали поля из таблицы, не являющейся частью базовой модели данных.Один из способов добавить таблицу в модель данных — создать связь с таблицей, которая уже находится в модели данных. Чтобы создать связь, в одной из таблиц должен быть столбец с уникальными, неповторяющимися значениями. В примере данных таблица Disciplines , импортированная из базы данных, содержит поле со спортивными кодами, называемое SportID. Эти же спортивные коды представлены в виде поля в импортированных нами данных Excel. Давайте создадим отношения.
Нажмите СОЗДАТЬ. .. в выделенной области полей сводной таблицы , чтобы открыть диалоговое окно Create Relationship , как показано на следующем экране.
В таблице выберите Дисциплины из раскрывающегося списка.
В столбце (иностранный) выберите SportID .
В связанной таблице выберите Sports .
В Связанном столбце (основном) выберите SportID .
org/ListItem»>
Нажмите ОК .
Сводная таблица изменится, чтобы отразить новую связь. Но сводная таблица пока выглядит некорректно из-за упорядочения полей в области ROWS . Дисциплина — это подкатегория данного вида спорта, но поскольку мы расположили дисциплину выше спорта в области ROWS , она не организована должным образом.На следующем экране показан этот нежелательный заказ.
В области РЯД и переместите Спорт выше Дисциплины. Это намного лучше, и сводная таблица отображает данные в том виде, в каком вы хотите их видеть, как показано на следующем экране.
За кулисами Excel создает модель данных, которую можно использовать во всей книге, в любой сводной таблице, сводной диаграмме, в Power Pivot или любом отчете Power View.Отношения между таблицами являются основой модели данных и определяют пути навигации и вычислений.
В следующем руководстве Расширение отношений модели данных с помощью Excel 2013, Power Pivot и DAX , вы опираетесь на то, что узнали здесь, и пошагово расширяете модель данных с помощью мощной и наглядной надстройки Excel под названием Power Вращаться. Вы также узнаете, как вычислять столбцы в таблице и использовать этот вычисляемый столбец, чтобы в вашу модель данных можно было добавить в противном случае несвязанную таблицу.
Контрольно-пропускной пункт и викторина
Повторение того, что вы узнали
Теперь у вас есть книга Excel, которая включает в себя сводную таблицу для доступа к данным в нескольких таблицах, некоторые из которых вы импортировали отдельно. Вы научились импортировать из базы данных, из другой книги Excel, а также копировать данные и вставлять их в Excel.
Чтобы данные работали вместе, вам нужно было создать связь между таблицами, которую Excel использовал для корреляции строк.Вы также узнали, что наличие столбцов в одной таблице, которые коррелируют с данными в другой таблице, необходимо для создания связей и поиска связанных строк.
Вы готовы к следующему руководству из этой серии. Вот ссылка:
Расширение отношений модели данных с помощью Excel 2013, Power Pivot и DAX
ВИКТОРИНА
Хотите увидеть, насколько хорошо вы запоминаете то, что узнали? Вот твой шанс. Следующая викторина освещает функции, возможности или требования, о которых вы узнали в этом руководстве.Внизу страницы вы найдете ответы. Удачи!
Вопрос 1: Почему важно преобразовывать импортированные данные в таблицы?
A: Вам не нужно преобразовывать их в таблицы, потому что все импортированные данные автоматически преобразуются в таблицы.
B: Если вы конвертируете импортированные данные в таблицы, они будут исключены из модели данных. Только когда они исключены из модели данных, они доступны в сводных таблицах, Power Pivot и Power View.
C. Если вы конвертируете импортированные данные в таблицы, их можно включить в модель данных и сделать доступными для сводных таблиц, Power Pivot и Power View.
D: Вы не можете преобразовать импортированные данные в таблицы.
Вопрос 2: Какие из следующих источников данных можно импортировать в Excel и включить в модель данных?
A: Доступ к базам данных, а также ко многим другим базам данных.
B: Существующие файлы Excel.
C: все, что вы можете скопировать и вставить в Excel и отформатировать в виде таблицы, включая таблицы данных на веб-сайтах, документы или что-либо еще, что можно вставить в Excel.
D: все вышеперечисленное
Вопрос 3: Что происходит в сводной таблице, когда вы меняете порядок полей в четырех областях полей сводной таблицы?
A: Ничего — вы не можете изменить порядок полей после того, как поместите их в области полей сводной таблицы.
B: формат сводной таблицы изменен, чтобы отразить макет, но базовые данные не затронуты.
C: формат сводной таблицы изменен, чтобы отразить макет, и все базовые данные изменяются безвозвратно.
D: Базовые данные изменены, в результате чего появятся новые наборы данных.
Вопрос 4: Что требуется при создании связи между таблицами?
A: Ни в одной таблице не может быть столбцов, содержащих уникальные неповторяющиеся значения.
B: Одна таблица не должна входить в книгу Excel.
C: Столбцы нельзя преобразовывать в таблицы.
D: Все вышеперечисленное неверно.
Ответы на викторину
org/ListItem»>
Правильный ответ: C
Правильный ответ: D
Правильный ответ: B
Правильный ответ: D
Примечания: Данные и изображения в этой серии руководств основаны на следующем:
Набор данных
Олимпийских игр от Guardian News & Media Ltd.
Изображения флагов из CIA Factbook (cia.gov)
Данные о населении Всемирного банка (worldbank.org)
Пиктограммы олимпийских видов спорта, сделанные Тадиусом 856 и Парутакупиу
Создать связь между таблицами в Excel
Вы когда-нибудь использовали ВПР для переноса столбца из одной таблицы в другую? Теперь, когда Excel имеет встроенную модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами данных на основе совпадающих данных в каждой таблице. Затем вы можете создавать листы Power View и строить сводные таблицы и другие отчеты с полями из каждой таблицы, даже если таблицы из разных источников. Например, если у вас есть данные о продажах клиентов, вы можете импортировать и связать данные анализа времени для анализа структуры продаж по годам и месяцам.
Все таблицы в книге перечислены в списках полей сводной таблицы и Power View.
Когда вы импортируете связанные таблицы из реляционной базы данных, Excel часто может создавать эти отношения в модели данных, которую он строит незаметно. Во всех остальных случаях вам потребуется создавать отношения вручную.
Убедитесь, что книга содержит как минимум две таблицы и что в каждой таблице есть столбец, который можно сопоставить со столбцом в другой таблице.
Выполните одно из следующих действий: Отформатируйте данные как таблицу или Импортируйте внешние данные как таблицу на новом листе.
Дайте каждой таблице осмысленное имя: В Инструменты таблиц щелкните Дизайн > Имя таблицы > введите имя.
Убедитесь, что столбец в одной из таблиц имеет уникальные значения данных без дубликатов. Excel может создать связь только в том случае, если один столбец содержит уникальные значения.
Например, чтобы связать продажи клиентов с аналитикой времени, обе таблицы должны включать даты в одном формате (например, 01.01.2012), и по крайней мере одна таблица (аналитика времени) перечисляет каждую дату только один раз в столбце.
Щелкните Данные > Отношения .
Если Отношения неактивны, ваша книга содержит только одну таблицу.
В поле Управление отношениями щелкните Новый .
В поле Создать связь щелкните стрелку для Таблица и выберите таблицу из списка.В отношениях «один ко многим» эта таблица должна быть многосторонней. Используя наш пример анализа данных о клиентах и времени, вы сначала выберете таблицу продаж по клиентам, потому что многие продажи, вероятно, произойдут в любой конкретный день.
Для столбца (внешний) выберите столбец, содержащий данные, относящиеся к Связанный столбец (основной) . Например, если бы у вас был столбец даты в обеих таблицах, вы бы выбрали этот столбец сейчас.
Для Связанная таблица выберите таблицу, в которой есть хотя бы один столбец данных, связанный с таблицей, которую вы только что выбрали для Таблица .
Для Связанный столбец (основной) выберите столбец с уникальными значениями, соответствующими значениям в столбце, выбранном для Столбец .
Нажмите ОК .
Подробнее о связях между таблицами в Excel
Примечания к отношениям
Вы узнаете, существует ли связь, когда перетащите поля из разных таблиц в список полей сводной таблицы. Если вам не предлагается создать взаимосвязь, значит, в Excel уже есть информация о взаимосвязи, необходимая для сопоставления данных.
Создание отношений аналогично использованию ВПР: вам нужны столбцы, содержащие совпадающие данные, чтобы Excel мог связывать строки в одной таблице со строками другой таблицы.В примере аналитики времени в таблице «Клиент» должны быть значения дат, которые также существуют в таблице аналитики времени.
В модели данных отношения таблиц могут быть взаимно-однозначными (у каждого пассажира есть один посадочный талон) или «один-ко-многим» (на каждом рейсе много пассажиров), но не «многие-ко-многим». Отношения «многие ко многим» приводят к ошибкам циклической зависимости, таким как «Обнаружена циклическая зависимость». Эта ошибка возникает, если вы устанавливаете прямое соединение между двумя таблицами, которые являются многими-ко-многим, или косвенными соединениями (цепочка отношений таблиц, которые являются взаимно-многими внутри каждого отношения, но многие-ко-многим при просмотре end до конца.Узнайте больше о взаимосвязях между таблицами в модели данных.
Типы данных в двух столбцах должны быть совместимы. Дополнительные сведения см. В разделе Типы данных в моделях данных Excel.
Другие способы создания отношений могут быть более интуитивными, особенно если вы не уверены, какие столбцы использовать. См. Раздел Создание отношения в представлении схемы в Power Pivot.
Пример: Связывание данных анализа времени с данными полета авиакомпании
Вы можете узнать как о связях таблиц, так и о логике времени, используя бесплатные данные в Microsoft Azure Marketplace.Некоторые из этих наборов данных очень большие и требуют быстрого подключения к Интернету для завершения загрузки данных за разумный период времени.
Запустите Power Pivot в надстройке Microsoft Excel и откройте окно Power Pivot.
Нажмите Получить внешние данные > из службы данных > из Microsoft Azure Marketplace . Домашняя страница Microsoft Azure Marketplace откроется в мастере импорта таблиц.
Меньше Цена , нажмите Бесплатно .
В разделе Категория щелкните Наука и статистика .
Найдите DateStream и нажмите Подписаться .
Войдите в свою учетную запись Microsoft и нажмите Войти .Предварительный просмотр данных должен появиться в окне.
Прокрутите вниз и нажмите Выбрать запрос .
Нажмите Далее .
Выберите BasicCalendarUS и затем нажмите Finish , чтобы импортировать данные. При быстром подключении к Интернету импорт должен занять около минуты. По завершении вы должны увидеть отчет о 73 414 переданных строках.Нажмите Закрыть .
Щелкните Получить внешние данные > из службы данных > из Microsoft Azure Marketplace , чтобы импортировать второй набор данных.
В разделе Тип щелкните Данные .
Меньше Цена , нажмите Бесплатно .
Найдите US Air Carrier Flight Delays и нажмите Выберите .
Прокрутите вниз и нажмите Выбрать запрос .
Нажмите Далее .
Нажмите Готово , чтобы импортировать данные. Импорт может занять 15 минут при быстром подключении к Интернету. По завершении вы должны увидеть отчет о состоянии передачи 2 427 284 строк. Нажмите Закрыть .Теперь у вас должно быть две таблицы в модели данных. Чтобы связать их, нам понадобятся совместимые столбцы в каждой таблице.
Обратите внимание, что DateKey в BasicCalendarUS находится в формате 01.01.2012 12:00:00 AM. Таблица On_Time_Performance также имеет столбец datetime, FlightDate , значения которого указаны в том же формате: 01.01.2012 12:00:00 AM. Два столбца содержат совпадающие данные одного и того же типа данных, и по крайней мере один из столбцов ( DateKey ) содержит только уникальные значения.В следующих нескольких шагах вы будете использовать эти столбцы, чтобы связать таблицы.
В окне Power Pivot щелкните сводную таблицу , чтобы создать сводную таблицу на новом или существующем листе.
В списке полей разверните On_Time_Performance и щелкните ArrDelayMinutes , чтобы добавить его в область значений. В сводной таблице вы должны увидеть общее время задержки рейсов, измеренное в минутах.
Разверните BasicCalendarUS и щелкните MonthInCalendar , чтобы добавить его в область Строки.
Обратите внимание, что в сводной таблице теперь перечислены месяцы, но общее количество минут одинаково для каждого месяца. Повторяющиеся одинаковые значения указывают на необходимость связи.
В списке полей в разделе «Могут потребоваться связи между таблицами» щелкните Создать .
В связанной таблице выберите On_Time_Performance , а в связанном столбце (основной) выберите FlightDate .
В таблице выберите BasicCalendarUS , а в столбце (внешний) выберите DateKey . Нажмите OK , чтобы создать связь.
Обратите внимание, что сумма задержанных минут теперь меняется для каждого месяца.
в BasicCalendarUS и перетащите YearKey в область строк выше MonthInCalendar .
Теперь вы можете разделить задержки прибытия по годам и месяцам или другим значениям в календаре.
Советы: По умолчанию месяцы перечислены в алфавитном порядке. Используя надстройку Power Pivot, вы можете изменить сортировку, чтобы месяцы отображались в хронологическом порядке.
Убедитесь, что таблица BasicCalendarUS открыта в окне Power Pivot.
В главной таблице щелкните Сортировать по столбцу .
В разделе «Сортировка» выберите MonthInCalendar
In By выберите MonthOfYear .
Сводная таблица теперь сортирует каждую комбинацию месяц-год (октябрь 2011 г., ноябрь 2011 г.) по номеру месяца в течение года (10, 11). Изменить порядок сортировки легко, потому что канал DateStream предоставляет все необходимые столбцы для работы этого сценария. Если вы используете другую таблицу интеллекта времени, ваш шаг будет другим.
«Могут потребоваться связи между таблицами»
Когда вы добавляете поля в сводную таблицу, вы будете проинформированы, требуется ли связь между таблицами, чтобы иметь смысл полей, выбранных в сводной таблице.
Хотя Excel может сказать вам, когда требуется связь, он не может сказать вам, какие таблицы и столбцы использовать, или возможна ли связь между таблицами. Попробуйте выполнить следующие действия, чтобы получить нужные ответы.
Шаг 1. Определите, какие таблицы указать в отношении
Если ваша модель содержит всего несколько таблиц, может быть сразу очевидно, какие из них вам нужно использовать. Но для более крупных моделей вам, вероятно, понадобится помощь.Один из подходов — использовать представление схемы в надстройке Power Pivot. Представление диаграммы обеспечивает визуальное представление всех таблиц в модели данных. Используя представление диаграммы, вы можете быстро определить, какие таблицы отделены от остальной модели.
Примечание. Возможно создание неоднозначных отношений, которые недопустимы при использовании в сводной таблице или отчете Power View. Предположим, что все ваши таблицы каким-то образом связаны с другими таблицами в модели, но когда вы пытаетесь объединить поля из разных таблиц, вы получаете сообщение «Могут потребоваться связи между таблицами».Скорее всего, вы столкнулись с отношениями «многие ко многим». Если вы проследите цепочку отношений между таблицами, которые соединяются с таблицами, которые вы хотите использовать, вы, вероятно, обнаружите, что у вас есть две или несколько отношений между таблицами «один ко многим». Не существует простого обходного пути, который работал бы в любой ситуации, но вы можете попробовать создать вычисляемые столбцы, чтобы объединить столбцы, которые вы хотите использовать, в одну таблицу.
Шаг 2. Найдите столбцы, которые можно использовать для создания пути от одной таблицы к следующей.
После того, как вы определили, какая таблица отключена от остальной части модели, просмотрите ее столбцы, чтобы определить, содержит ли другой столбец в другом месте модели совпадающие значения.
Например, предположим, что у вас есть модель, которая содержит продажи продуктов по территории, и вы впоследствии импортируете демографические данные, чтобы узнать, есть ли корреляция между продажами и демографическими тенденциями на каждой территории. Поскольку демографические данные поступают из другого источника данных, его таблицы изначально изолированы от остальной части модели. Чтобы интегрировать демографические данные с остальной частью вашей модели, вам нужно найти столбец в одной из демографических таблиц, который соответствует той, которую вы уже используете.Например, если демографические данные организованы по регионам, а в ваших данных о продажах указывается, в каком регионе произошла продажа, вы можете связать два набора данных, найдя общий столбец, например штат, почтовый индекс или регион, для поиска.
Помимо совпадающих значений, существует несколько дополнительных требований для создания отношения:
Значения данных в столбце подстановки должны быть уникальными. Другими словами, в столбце не должно быть дубликатов.В модели данных значения NULL и пустые строки эквивалентны пустому значению, которое является отдельным значением данных. Это означает, что в столбце подстановки не может быть нескольких значений NULL.
Типы данных как исходного столбца, так и столбца подстановки должны быть совместимы. Дополнительные сведения о типах данных см. В разделе Типы данных в моделях данных.
Чтобы узнать больше о связях таблиц, см. Связи между таблицами в модели данных.
Верх страницы
Использование Access или Excel для управления данными
Плоские или реляционные данные Чтобы решить, какая программа лучше всего подходит для хранения ваших данных, задайте себе следующий вопрос: являются ли данные реляционными или нет? Данные, которые могут эффективно содержаться в одной таблице или листе, называются плоскими или нереляционными данными . Например, если вы хотите создать простой список клиентов с одним адресом и контактным лицом для каждого клиента, Excel может быть лучшим выбором.Однако, если вы хотите сохранить более сложный список клиентов, содержащий адреса выставления счетов и доставки для каждого клиента или несколько контактных лиц для каждого клиента, Access — лучшее решение.
В реляционной базе данных вы организуете свою информацию в несколько таблиц. В хорошо спроектированной реляционной базе данных каждая таблица является плоской и содержит информацию только об одном типе данных. Например, если вы создаете базу данных клиентов, имена клиентов должны храниться в одной таблице, тогда как адреса для выставления счетов и доставки этих клиентов должны храниться в отдельной таблице.Хранение адресов отдельно от имен — хорошая идея, потому что у каждого клиента может быть несколько адресов, и вы хотите иметь возможность вводить несколько адресов для каждого клиента без необходимости повторно вводить имя клиента для каждого адреса.
Локальные и внешние данные Вы можете использовать Access для подключения к данным из различных внешних источников данных, чтобы вы могли просматривать, запрашивать и редактировать эти данные без необходимости их импорта. Например, Access предоставляет команды для подключения к существующим данным в базе данных Microsoft SQL Server, файлу dBASE или папке Outlook, а также ко многим другим источникам данных.Вы можете использовать Excel для подключения к широкому спектру источников данных, включая Access, базы данных SQL Server и Analysis Services, текстовые и XML-файлы, а также источники данных ODBC и OLE DB. Однако вы не можете редактировать данные для изменения исходных данных через пользовательский интерфейс Excel.
И Access, и Excel предоставляют команды для подключения к данным в списках Windows SharePoint Services. Однако Excel обеспечивает только подключение к спискам SharePoint только для чтения; тогда как Access позволяет читать и записывать данные в списки SharePoint.
Целостность данных или гибкость Уникальные идентификаторы помогают сохранить целостность ваших данных и гарантируют, что никакие две строки (или записи) не содержат абсолютно одинаковых данных. Уникальные идентификаторы также обеспечивают самый быстрый способ извлечения данных при поиске или сортировке данных. В Access вы можете использовать тип данных AutoNumber для автоматического создания уникального идентификатора для каждой записи. Затем вы можете использовать эти идентификаторы, чтобы связать записи в одной таблице с одной или несколькими записями в другой таблице.
Структура, которую Access применяет к вашим данным, помогает обеспечить целостность данных. Access может потребовать, чтобы новые записи в одной таблице имели существующее соответствующее значение в другой таблице, чтобы вы не могли создавать «потерянные» записи. Например, вы не хотели бы иметь заказ, в котором не указана информация о клиенте. Access может потребовать, чтобы каждая новая запись в таблице «Заказы» имела соответствующее значение клиента в таблице «Клиенты».
No related posts.

Город	NOC_CountryRegion	Альфа-2 Код	Выпуск	Сезон
Мельбурн / Стокгольм	AUS	AS	1956	Лето
Сидней	AUS	AS	2000	Лето
Инсбрук	AUT	AT	1964	Зима
Инсбрук	AUT	AT	1976	Зима
Антверпен	BEL	BE	1920	Лето
Антверпен	BEL	BE	1920	Зима
Монреаль	CAN	CA	1976	Лето
Лейк-Плэсид	CAN	CA	1980	Зима
Калгари	CAN	CA	1988	Зима
ул. Мориц	SUI	SZ	1928	Зима
Санкт-Мориц	SUI	SZ	1948	Зима
Пекин	ЧН	CH	2008	Лето
Берлин	GER	GM	1936	Лето
Гармиш-Партенкирхен	GER	GM	1936	Зима
Барселона	ESP	SP	1992	Лето
Хельсинки	FIN	FI	1952	Лето
Париж	FRA	FR	1900	Лето
Париж	FRA	FR	1924	Лето
Шамони	FRA	FR	1924	Зима
Гренобль	FRA	FR	1968	Зима
Альбервиль	FRA	FR	1992	Зима
Лондон	ГБР	Великобритания	1908	Лето
Лондон	ГБР	Великобритания	1908	Зима
Лондон	ГБР	Великобритания	1948	Лето
Мюнхен	GER	DE	1972	Лето
Афины	GRC	ГР	2004	Лето
Кортина д’Ампеццо	ITA	IT	1956	Зима
Рим	ITA	IT	1960	Лето
Турин	ITA	IT	2006	Зима
Токио	JPN	JA	1964	Лето
Саппоро	JPN	JA	1972	Зима
Нагано	JPN	JA	1998	Зима
Сеул	KOR	кс	1988	Лето
Мексика	МЕХ	MX	1968	Лето
Амстердам	NED	NL	1928	Лето
Осло	NOR	НЕТ	1952	Зима
Лиллехаммер	NOR	НЕТ	1994	Зима
Стокгольм	SWE	SW	1912	Лето
Сент-Луис	США	США	1904	Лето
Лос-Анджелес	США	США	1932	Лето
Лейк-Плэсид	США	США	1932	Зима
Скво-Вэлли	США	США	1960	Зима
Москва	УРС	RU	1980	Лето
Лос-Анджелес	США	США	1984	Лето
Атланта	США	США	1996	Лето
Солт-Лейк-Сити	США	США	2002	Зима
Сараево	ЮГ	Ю	1984	Зима

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

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

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

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

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

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

Шаг 6.

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

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

Управление данными с помощью Access или Excel

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

Импорт Excel в базу данных QuintaDB

Импорт CSV в базу данных QuintaDB.

Поделиться обсуждением в социальных сетях: Tweet

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

База данных в Excel | Блог Александра Воробьева

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

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

Пример.

Итоги.

Статьи с близкой тематикой

Отзывы

Сбор базы данных и сделать таблицы в Excel за 3000 рублей

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

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

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

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

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

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

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

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

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

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

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

Как создать базу данных с возможностью поиска в Excel | Small Business

Как работает реляционная база данных?

Пример базы данных

Основная база данных

Для чего нужна реляционная база данных?

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

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

Использование фильтров в столбцах

Фильтр в соответствии с условиями

Отключить фильтрацию

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

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

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

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

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

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

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

Знакомство с тремя типами баз данных электронных таблиц Excel

База данных электронных таблиц 1: простые таблицы

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

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

База данных электронных таблиц 2: таблицы Excel

База данных электронных таблиц 3: Табличный макет для сводных таблиц

Сделайте следующий шаг

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

В этом учебном пособии есть следующие разделы:

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

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

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

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

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

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

Подробнее о связях между таблицами в Excel

Примечания к отношениям

Пример: Связывание данных анализа времени с данными полета авиакомпании

«Могут потребоваться связи между таблицами»

Шаг 1. Определите, какие таблицы указать в отношении

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

Использование Access или Excel для управления данными

Добавить комментарий Отменить ответ