Базы данных в excel: Создание базы данных в 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 и создание модели данных

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

Аннотация.    Это первый учебник из серии, который поможет ознакомиться с программой Excel и ее возможностями объединения и анализа данных, а также научиться легко использовать их. С помощью этой серии учебников вы научитесь создавать с нуля и совершенствовать рабочие книги Excel, строить модели данных и создавать удивительные интерактивные отчеты с использованием надстройки Power View. В этих учебниках приводится описание возможностей средств бизнес-аналитики Майкрософт в Excel, сводных таблиц, Power Pivot и Power View.

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

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

Учебники этой серии

1. Импорт данных в Excel 2013 и создание модели данных

2. Расширение связей модели данных с помощью Excel, Power PIVOT и DAX

3. Создание отчетов Power View на основе карт

4. Объединение интернет-данных и настройка параметров отчета Power View по умолчанию

5. Справка по Power Pivot

6. Создание впечатляющих отчетов Power View, часть 2

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

Разделы учебника

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

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

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

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

Контрольная точка и тест

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

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

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

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

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

1. Щелкните следующие ссылки, чтобы скачать файлы, используемые во время этого ряда учебников. Скачайте каждый из четырех файлов в нужное место, например загружаемЫе файлы или Мои документы, или новую созданную папку.
   _Гт_ олимпикмедалс. accdb Access

   Книга Excel _гт_ олимпикспортс. xlsx
   Книга Excel _гт_. xlsxКнига Excel _Гт_ дисЦимаже_табле. xlsx

2. Откройте пустую книгу в Excel 2013.

3. Щелкните данные _Гт_ получить внешние данные _Гт_ из Access. Лента настраивается динамически в зависимости от ширины книги, поэтому команды на ленте могут немного отличаться от следующих экранов. На первом экране отображается лента, если книга является широкой, а на втором — книга, размер которой был изменен, чтобы занимать только часть экрана.

4. Выберите скачанный файл Олимпикмедалс. accdb и нажмите кнопку Открыть. Откроется следующее окно Выбор таблицы, в котором отображаются таблицы, найденные в базе данных. Таблицы в базе данных похожи на листы и таблицы в Excel. Установите флажок Разрешить выбор нескольких таблиц и выберите все таблицы. Нажмите кнопку ОК.
   
   

5. Появится окно «Импорт данных».

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

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

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

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

Просмотр данных в сводной таблице

Просматривать импортированные данные удобнее всего с помощью сводной таблицы. В сводной таблице можно перетаскивать поля (похожие на столбцы в Excel) из таблиц (например, таблиц, импортированных из базы данных Access) в разные области, настраивая представление данных. Сводная таблица содержит четыре области: ФИЛЬТРЫ, СТОЛБЦЫ, СТРОКИ и ЗНАЧЕНИЯ.

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

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

1. В полях сводной таблицы разверните таблицу medals , щелкнув стрелку рядом с ней. Найдите поле Нок_каунтрирегион в развернутой таблице medals и перетащите его в область столбцы. В NOC используется National спортивных комитетов, который является организационным подразделением для страны или региона.

2. Затем перетащите виды спорта из таблицы Disciplines в область СТРОКИ.

3. Давайте отфильтруем дисциплины, чтобы отображались только пять видов спорта: стрельба из лука (Archery), прыжки в воду (Diving), фехтование (Fencing), фигурное катание (Figure Skating) и конькобежный спорт (Speed Skating). Это можно сделать в области Поля сводной таблицы или в фильтре Метки строк в самой сводной таблице.

   1. Щелкните в любом месте сводной таблицы, чтобы убедиться, что сводная таблица Excel выбрана. В списке Поля сводной таблицы, где развернута таблица Disciplines, наведите указатель на поле Discipline, и в его правой части появится стрелка раскрывающегося списка. Щелкните эту стрелку, нажмите кнопку (Выбрать все), чтобы снять отметку со всех выбранных параметров, а затем прокрутите вниз и выберите пункты Archery, Diving, Fencing, Figure Skating и Speed Skating. Нажмите кнопку ОК.

   2. Либо щелкните в разделе сводной таблицы Метки строк стрелку раскрывающегося списка рядом с полем Метки строк, нажмите кнопку (Выбрать все), чтобы снять отметку со всех выбранных параметров, а затем прокрутите вниз и выберите пункты Archery, Diving, Fencing, Figure Skating и Speed Skating. Нажмите кнопку ОК.

4. В разделе Поля сводной таблицы перетащите поле Medal из таблицы Medals в область ЗНАЧЕНИЯ. Поскольку значения должны быть числовыми, Excel автоматически изменит поле Medal на Count of Medal.

5. В таблице Medals снова выберите поле Medal и перетащите его в область ФИЛЬТРЫ.

6. Давайте отфильтруем сводную таблицу таким образом, чтобы отображались только страны или регионы, завоевавшие более 90 медалей. Вот как это сделать.

   1. В сводной таблице щелкните стрелку раскрывающегося списка рядом с полем Метки столбцов.

   2. Выберите Фильтры по значению, а затем — Больше…

   3. Введите 90 в последнем поле (справа). Нажмите кнопку ОК.
      

Сводная таблица будет иметь следующий вид:

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

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

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

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

Начнем с создания пустого листа, а затем импортируем данные из книги Excel.

1. Вставьте новый лист Excel и назовите его Sports.

2. Перейдите к папке, в которой содержатся загруженные файлы образцов данных, и откройте файл OlympicSports.xlsx.

3. Выберите и скопируйте данные на листе Sheet1. При выборе ячейки с данными, например, ячейки А1, можно нажать клавиши Ctrl + A, чтобы выбрать все смежные данные. Закройте книгу OlympicSports.xlsx.

4. На листе Sports поместите курсор в ячейку А1 и вставьте данные.

5. Выделив данные, нажмите клавиши CTRL + T, чтобы отформатировать данные как таблицу. Вы также можете отформатировать данные в виде таблицы на ленте, выбрав в _Гт_ формат таблицы. Так как у данных есть заголовки, в появившемся окне Создание таблицы выберите пункт таблица с заголовками, как показано ниже.

   Форматирование данных в виде таблицы имеет много преимуществ. Можно назначить имя таблице, что упрощает ее определение. Кроме того, вы можете устанавливать связи между таблицами, позволяя исследовать и анализировать в сводных таблицах, Power Pivot и в Power View.

6. ПриСвойте таблице имя. В разделе Работа с таблицаМи _Гт_ свойства _ГТ_ макета найдите поле имя таблицы и введите Спорт. Книга будет выглядеть так, как показано на следующем экране.
   

7. Сохраните книгу.

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

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

1. Вставьте новый лист Excel и назовите его Hosts.

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

City	NOC_CountryRegion	Alpha-2 Code	Edition	Season
Melbourne / Stockholm	AUS	AS	1956	Summer
Sydney	AUS	AS	2000	Summer
Innsbruck	AUT	AT	1964	Winter
Innsbruck	AUT	AT	1976	Winter
Antwerp	BEL	BE	1920	Summer
Antwerp	BEL	BE	1920	Winter
Montreal	CAN	CA	1976	Summer
Lake Placid	CAN	CA	1980	Winter
Calgary	CAN	CA	1988	Winter
St. Moritz	SUI	SZ	1928	Winter
St. Moritz	SUI	SZ	1948	Winter
Beijing	CHN	CH	2008	Summer
Berlin	GER	GM	1936	Summer
Garmisch-Partenkirchen	GER	GM	1936	Winter
Barcelona	ESP	SP	1992	Summer
Helsinki	FIN	FI	1952	Summer
Paris	FRA	FR	1900	Summer
Paris	FRA	FR	1924	Summer
Chamonix	FRA	FR	1924	Winter
Grenoble	FRA	FR	1968	Winter
Albertville	FRA	FR	1992	Winter
London	GBR	UK	1908	Summer
London	GBR	UK	1908	Winter
London	GBR	UK	1948	Summer
Munich	GER	DE	1972	Summer
Athens	GRC	GR	2004	Summer
Cortina d’Ampezzo	ITA	IT	1956	Winter
Rome	ITA	IT	1960	Summer
Turin	ITA	IT	2006	Winter
Tokyo	JPN	JA	1964	Summer
Sapporo	JPN	JA	1972	Winter
Nagano	JPN	JA	1998	Winter
Seoul	KOR	KS	1988	Summer
Mexico	MEX	MX	1968	Summer
Amsterdam	NED	NL	1928	Summer
Oslo	NOR	NO	1952	Winter
Lillehammer	NOR	NO	1994	Winter
Stockholm	SWE	SW	1912	Summer
St Louis	USA	US	1904	Summer
Los Angeles	USA	US	1932	Summer
Lake Placid	USA	US	1932	Winter
Squaw Valley	USA	US	1960	Winter
Moscow	URS	RU	1980	Summer
Los Angeles	USA	US	1984	Summer
Atlanta	USA	US	1996	Summer
Salt Lake City	USA	US	2002	Winter
Sarajevo	YUG	YU	1984	Winter

1. В Excel поместите курсор в ячейку А1 на листе Hosts и вставьте данные.

2. Отформатируйте данные в виде таблицы. Как описано выше, для форматирования данных в виде таблицы нажмите клавиши Ctrl + T или выберите пункт меню ГЛАВНАЯ > Форматировать как таблицу. Поскольку у данных есть заголовки, установите флажок Таблица с заголовками в окне Создание таблицы.

3. Присвойте таблице имя. На вкладках РАБОТА С ТАБЛИЦАМИ > КОНСТРУКТОР > Свойства найдите поле Имя таблицы и введите слово Hosts.

4. Выберите столбец Edition и на вкладке ГЛАВНАЯ задайте для него числовой формат с 0 десятичных знаков.

5. Сохраните книгу. Книга будет иметь следующий вид:

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

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

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

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

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

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

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

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

5. В области Таблица выберите пункт Disciplines из раскрывающегося списка.

6. В области Столбец (чужой) выберите пункт SportID.

7. В области Связанная таблица выберите пункт Sports.

8. В области Связанный столбец (первичный ключ) выберите пункт SportID.

9. Нажмите кнопку ОК.

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

1. Переместите пункт Sport выше пункта Discipline в области СТРОКИ. Теперь данные отображаются в сводной таблице так, как нам нужно (см. снимок экрана).

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

В следующем учебнике Расширение связей модели данных с использованием Excel 2013, Power Pivot и DAX вы закрепите знания, полученные в данном уроке, и узнаете о расширении модели данных с использованием мощной визуальной надстройки Excel, которая называется Power Pivot. Также вы научитесь вычислять столбцы в таблице и использовать вычисляемый столбец, чтобы в модель данных можно было добавить несвязанную таблицу.

Контрольная точка и тест

Повторение изученного материала

Теперь у вас есть книга 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. Базы данных Access и многие другие базы данных.

B. Существующие файлы Excel.

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

D. Все вышеперечисленное.

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

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

B. Формат сводной таблицы изменится в соответствии с макетом, но это не повлияет на базовые данные.

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

D. Базовые данные изменятся, что приведет к созданию новых наборов данных.

Вопрос 4. Что необходимо для создания связи между таблицами?

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

B. Таблица не должна быть частью книги Excel.

C. Столбцы не должны быть преобразованы в таблицы.

D. Ни один из вышеперечисленных ответов не является правильным.

Ответы на вопросы теста

1. Правильный ответ: C

2. Правильный ответ: D

3. Правильный ответ: Б

4. Правильный ответ: D

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

• Набор данных об Олимпийских играх © Guardian News & Media Ltd.

• Изображения флагов из справочника CIA Factbook (cia.gov).

• Данные о населении из документов Всемирного банка (worldbank.org).

• Авторы эмблем олимпийских видов спорта Thadius856 и Parutakupiu.

Функции базы данных (справка) — Служба поддержки Office

Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Дополнительные сведения об этих различиях.

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

Функция	Описание
ДСРЗНАЧ	Возвращает среднее значение выбранных записей базы данных.
БСЧЁТ	Подсчитывает количество числовых ячеек в базе данных.
БСЧЁТА	Подсчитывает количество непустых ячеек в базе данных.
БИЗВЛЕЧЬ	Извлекает из базы данных одну запись, удовлетворяющую заданному условию.
ДМАКС	Возвращает максимальное значение среди выделенных записей базы данных.
ДМИН	Возвращает минимальное значение среди выделенных записей базы данных.
БДПРОИЗВЕД	Перемножает значения определенного поля в записях базы данных, удовлетворяющих условию.
ДСТАНДОТКЛ	Оценивает стандартное отклонение по выборке из выделенных записей базы данных.
ДСТАНДОТКЛП	Вычисляет стандартное отклонение по генеральной совокупности из выделенных записей базы данных.
БДСУММ	Суммирует числа в поле для записей базы данных, удовлетворяющих условию.
БДДИСП	Оценивает дисперсию по выборке из выделенных записей базы данных.
БДДИСПП	Вычисляет дисперсию по генеральной совокупности для выделенных записей базы данных.

См. также

Функции Excel (по категориям)

Функции Excel (по алфавиту)

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

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

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

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

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

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

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

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

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

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

​Смотрите также​ статический: код действия.​ остаются данные только​ с порядкового номера.​После нажатия на​.​ с товарами из​ для учета, например,​Чтобы выбрать значение фильтра,​ в Excel. Перед​ выберите вкладку «Таблица»,​ этим как раз​ если правильно ее​ даты рождения и​ разместить на другом​ которыми хотим скрыть.​Можно сказать, что после​В пакете Microsoft Office​

​ По умолчанию в​ по Петрову.​

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

​ Чтобы не проставлять​ОК​Теперь после заполнения формы​ прайс-листа. Для этого​ классических продаж должна​

​ щелкаем по стрелке​ нами стоит задача​​ группу «Представления» и​​ превосходно справляется динамический​ настроить. Удобно, правда?​ табель успеваемости –​

​ листе. В нём​​ После того как​​ этого мы уже​ есть специальная программа​ Excel сумма закодирована​

​Обратите внимание! При сортировке​ цифры вручную, пропишем​в правой половине​ можно просто жать​

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

​ можно использовать команду​ уметь:​

1. ​ нужного столбца. В​ – сформировать клиентскую​

2. ​ смело кликайте на​ диапазон. Он задается​

3. ​Кроме этого, не нужно​ все это в​

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

​ окна появится панель​ на нашу кнопку,​

​Данные — Проверка данных​​хранить​ раскрывающемся списке появляется​

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

​ БД. За несколько​ кнопку «Представление». Выбирайте​ двумя координатами ячеек:​ забывать о закреплении​ совокупности представляет собой​ которые будут появляться​ на кнопку​

1. ​ данных. С ней​​ данных и работы​​ ставим ее. Второй​

2. ​ только все позиции​ и А5 единицу​Поля сводной таблицы​ и введенные данные​ (Data — Validation)​​в таблицах информацию​​ все содержимое поля.​

3. ​ лет работы у​​ пункт «Режим таблицы»​​ верхней левой и​ первой строки. В​ базу данных. Она​ в выпадающем списке.​«OK»​ можно работать и​ с ними –​ и последующие аргументы​ в столбцах, но​ и двойку, соответственно.​​, где нужно щелкнуть​​ будут автоматически добавляться​, указать в качестве​ по товарам (прайс),​ Если хотим спрятать​ компании появилось несколько​ и вставляйте информацию,​ правой нижней, словно​ Excel 2007 это​ нужна везде –​Выделяем этот список и​.​ в таком состоянии,​ Access. Тем не​ динамические: это ссылки​​ и номера соответствующих​​ Затем выделим их,​

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

​ как она представлена​ менее, многие пользователи​ на диапазоны, по​ строк на листе​ схватимся за уголок​Все​Продажи​Список (List)​ клиентам и связывать​ птички напротив их.​ Необходимо отслеживать сроки​ мышки и выбрав​ нужно обратить внимание​

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

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

1. ​ которым подводятся итоги.​ (они подсвечены синим).​ получившегося выделения и​, чтобы увидеть не​, а затем форма​и ввести затем​​ эти таблицы между​​Жмем «ОК». В примере​​ договоров, направления сотрудничества.​​ «Вставить».​

   ​ на место, откуда​ вкладку «Вид», затем​

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

Практическая работа «Создание базы данных в Excel»

Практическая работа «Создание базы данных в Excel»

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

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

Методические рекомендации по выполнению практической работы

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

Создаем таблицу Прайс:

1. Создаем в Excel новый лист с названием Прайс.

2. Создаем три столбца: Наименование, Категория, Цена. Заполняем 20 строк в созданной таблице по следующему образцу:

4. Превращаем созданную таблицу в «умную таблицу». Для этого выделяем все заполненные ячейки, нажимаем Главная – Форматировать как таблицу. Из выпавшего списка выбираем тот стиль оформления, который нам понравился.

5. Далее идем на вкладку Работа с таблицами — Конструктор. В окошке Имя таблицы меняем наименование на Прайс.

6. Создаем в Excel новый лист с названием Клиенты.

7. Создаем два столбца: Клиент, Город. Заполняем 20 строк в созданной таблице по следующему образцу:

8. Аналогично предыдущем листу превращаем созданную таблицу в «умную таблицу». Для этого выделяем все заполненные ячейки, нажимаем Главная – Форматировать как таблицу. Из выпавшего списка выбираем тот стиль оформления, который нам понравился.

9. Далее идем на вкладку Работа с таблицами — Конструктор. В окошке Имя таблицы меняем наименование на Клиенты.

10. Создаем в Excel новый лист с названием Продажи.

11. Создаем пять столбцов: Дата, Товар, Кол-во, Стоимость, Клиент. Не заполняем!!!

12. Аналогично предыдущем листу превращаем созданную таблицу в «умную таблицу». Для этого выделяем все заполненные ячейки, нажимаем Главная – Форматировать как таблицу. Из выпавшего списка выбираем тот стиль оформления, который нам понравился.

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

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

Создаем Форму ввода:

1. Создаем в Excel новый лист с названием Форма ввода.

2. Оформляем лист следующим образом:

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

4. Откроется диалоговое окно:

5. В поле Тип данных выбираем Список.

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

7. Аналогичным образом поступаем с ячейкой Товар. В результате произведенных действий Форма ввода должна иметь следующий вид (дата, количество заполняются вручную):

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

В скобках первым в кавычках указывается наименование позиции, которая была выбрана в поле Товар. После точки с запятой указывается наименование таблицы, откуда будут подставляться значения (наименование Прайс мы задали в Шаге 1, п. 5). Далее через точку с запятой идет номер столбца в таблице Прайс, где содержится нужный нам параметр.

После нажатия кнопки Enter нужная цена появится автоматически.

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

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

1. После заполнения формы нужно введенные в нее данные добавить в конец таблицы Продажи. Сформируем при помощи простых ссылок строку для добавления прямо под формой (обратите внимание, ячейки формируются в той последовательности, в какой они идут в таблице Продажи, т.е. в ячейке A20 будет ссылка =B3, в ячейке B20 ссылка на =B7 и т.д.):

2. Теперь создадим макрос, который копирует созданную строку и добавляет его в таблицу Продажи. Для этого нажимаем Разработчик — Visual Basic. Если вкладку Разработчик не видно, то включите ее сначала в настройках Файл — Параметры — Настройка ленты. Поставить галочку напротив меню Разработчик:

3. После этого откроется окно Microsoft Visual Basic for Applications:

4. В открывшемся окне редактора 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

5. Закрываем окно редактора Visual Basic (никаких сохранений это действие не потребует).

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

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

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

9. Сохранять созданный файл нужно следующим образом: Файл – Сохранить как – Тип файла: Книга Excel с поддержкой макросов.

Экспорт данных в Excel — Access

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

В этой статье

Экспорт данных в Excel: основы

Подготовка к экспортной операции

Запустить операцию экспорта

Что еще я должен знать об экспорте?

Устранение неполадок с отсутствующими и неправильными значениями

Экспорт данных в Excel: основы

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

Распространенные сценарии экспорта данных в Excel

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

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

Об экспорте данных в Excel

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

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

• Microsoft Excel включает команду для импорта данных из базы данных Access.Вы можете использовать эту команду вместо команды экспорта в Access; однако команда импорта Excel позволяет импортировать только таблицы или запросы. Дополнительные сведения см. В статье справки Excel Подключение к внешним данным (импорт).

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

• За одну операцию экспорта можно экспортировать только один объект базы данных. Однако вы можете объединить несколько листов в Excel после завершения отдельных операций экспорта.

Вершина Pge

Подготовка к экспортной операции

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

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

Экспорт	Исходный объект	Поля и записи	Форматирование
Без форматирования	Таблица или запрос Примечание. Формы и отчеты нельзя экспортировать без их форматирования.	Все поля и записи в базовом объекте экспортируются.	Параметры свойства Формат игнорируются во время операции. Для полей поиска экспортируются только значения идентификатора поиска. Для полей гиперссылок содержимое экспортируется в виде текстового столбца, который отображает ссылки в формате displaytext # address #.
С форматированием	Таблица, запрос, форма или отчет	Экспортируются только поля и записи, которые отображаются в текущем представлении или объекте. Отфильтрованные записи, скрытые столбцы в таблице и поля, не отображаемые в форме или отчете, не экспортируются.	Мастер соблюдает настройки свойства Формат. Для полей поиска значения поиска экспортируются. Для полей гиперссылок значения экспортируются как гиперссылки. Для полей с расширенным текстом текст экспортируется, а форматирование — нет.

1. Выберите целевую книгу и формат файла. Обратите внимание, что отчеты можно экспортировать только в более старый формат файла * .xls, но не в более новый *.xlsx формат файла.

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

Если книга назначения	А исходный объект —	А вы хотите экспортировать	Затем
Не существует	Таблица, запрос, форма или отчет	Данные, с форматированием или без него	Книга создается во время операции экспорта.
Уже существует	Таблица или запрос	Данные, но не форматирование	Книга не перезаписана. В книгу добавляется новый рабочий лист, которому дается имя объекта, из которого экспортируются данные.Если лист с таким именем уже существует в книге, Access предложит вам либо заменить содержимое соответствующего листа, либо указать другое имя для нового листа.
Уже существует	Таблица, запрос, форма или отчет	Данные, включая форматирование	Книга перезаписана экспортированными данными.Все существующие листы удаляются, и создается новый лист с тем же именем, что и экспортируемый объект. Данные на листе Excel наследуют параметры формата исходного объекта.

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

Верх страницы

Запустить операцию экспорта

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

2. В области навигации исходной базы данных выберите объект, который нужно экспортировать.

   Экспорт только части данных

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

   Чтобы открыть форму в режиме таблицы:

   1. Дважды щелкните форму, чтобы открыть ее.

   2. Щелкните форму правой кнопкой мыши и выберите Просмотр таблицы . Если эта опция недоступна:

      1. Щелкните Просмотр дизайна .

      2. Нажмите F4, чтобы открыть панель задач Property Sheet.

      3. Выберите Form из раскрывающегося списка в верхней части страницы свойств.

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

      5. На вкладке Design в группе Views щелкните Datasheet View .

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

3. На вкладке Внешние данные в группе Экспорт щелкните Excel .

4. В диалоговом окне «Экспорт — таблица Excel » просмотрите предлагаемое имя файла для книги Excel (Access использует имя исходного объекта).При желании вы можете изменить имя файла.

5. В поле Формат файла выберите нужный формат файла.

6. Если вы экспортируете таблицу или запрос и хотите экспортировать отформатированные данные, выберите Экспорт данных с форматированием и макетом . Дополнительные сведения см. В разделе «Подготовка к операции экспорта».

   Примечание: Если вы экспортируете форму или отчет, этот параметр всегда выбран, но недоступен (отображается серым цветом).

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

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

   Примечание: Этот флажок остается недоступным (затененным), если записи не выбраны.

8. Щелкните ОК .

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

Верх страницы

Что еще мне нужно знать об экспорте?

Верх страницы

Устранение неполадок с отсутствующими и неправильными значениями

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

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

Выпуск	Описание и разрешение
Расчетные поля	Результаты вычисленных полей экспортируются; лежащие в основе выражения, выполняющие вычисления, — нет.
Многозначные поля	Поля, поддерживающие несколько значений, экспортируются в виде списка значений, разделенных точкой с запятой (;).
Рисунки, предметы и приложения	Графические элементы (например, логотипы, содержимое полей объекта OLE и вложения, являющиеся частью исходных данных) не экспортируются.Добавьте их на рабочий лист вручную после завершения операции экспорта.
График	При экспорте формы или отчета, содержащего объект Microsoft Graph, объект графика не экспортируется.
Данные в неправильном столбце	Нулевые значения в итоговом листе иногда заменяются данными, которые должны быть в следующем столбце.
Отсутствуют значения даты	Значения даты до 1 января 1900 г. не экспортируются. Соответствующие ячейки на листе будут содержать нулевое значение.
Отсутствующие выражения	Выражения, используемые для вычисления значений, не экспортируются в Excel.Экспортируются только результаты расчетов. Добавьте формулу на лист Excel вручную после завершения операции экспорта.
Отсутствующие подчиненные формы, подчиненные отчеты и вспомогательные таблицы	При экспорте формы, отчета или таблицы экспортируется только основная форма, отчет или таблица. Вы должны повторить операцию экспорта для каждой подчиненной формы, вложенного отчета и вложенной таблицы, которые вы хотите экспортировать.
Столбцы отсутствуют или неправильно отформатированы	Если ни один из столбцов не отформатирован на итоговом листе, повторите операцию экспорта, не забудьте установить флажок Экспорт данных с форматированием и макетом в мастере. И наоборот, если форматирование только некоторых столбцов отличается от форматов в исходном объекте, примените нужное форматирование вручную в Excel. Форматирование столбца в Excel Откройте целевую книгу Excel, а затем отобразите рабочий лист, содержащий экспортированные данные. Щелкните правой кнопкой мыши столбец или выбранный диапазон ячеек и выберите Форматировать ячейки . На вкладке Номер в разделе Категории выберите формат, например Текст , Номер , Дата или Время . Щелкните ОК .
Значение «#» в столбце	Вы можете увидеть значение # в столбце, который соответствует полю Да / Нет в форме. Это может быть результатом запуска операции экспорта из области навигации или в режиме формы.Чтобы решить эту проблему, откройте форму в режиме таблицы перед экспортом данных.
Индикаторы ошибок или значения ошибок	Проверьте ячейки на наличие индикаторов ошибок (зеленые треугольники в углах) или значений ошибок (строки, начинающиеся с символа # вместо соответствующих данных).

Верх страницы

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

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

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

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

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

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

1. Импорт данных в Excel 2013 и создание модели данных

2. Расширение отношений модели данных с помощью Excel, Power Pivot и DAX

3. Создание отчетов Power View на основе карт

4. Включение данных из Интернета и установка параметров отчета Power View по умолчанию

5. Справка по Power Pivot

6. Создание потрясающих отчетов Power View — часть 2

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

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

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

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

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

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

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

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

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

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

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

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

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

2. В Excel 2013 откройте пустую книгу.

3. Щелкните ДАННЫЕ> Получить внешние данные> Из доступа . Лента динамически регулируется в зависимости от ширины книги, поэтому команды на ленте могут немного отличаться от следующих экранов.На первом экране отображается лента, когда книга широкая, на втором изображении показана книга, размер которой был изменен, чтобы занимать только часть экрана.

4. Выберите загруженный файл OlympicMedals.accdb и щелкните Открыть . Появится следующее окно Выбрать таблицу, в котором отображаются таблицы, найденные в базе данных. Таблицы в базе данных похожи на рабочие листы или таблицы в Excel.Установите флажок Разрешить выбор нескольких таблиц и выберите все таблицы. Затем нажмите ОК .

5. Откроется окно Импорт данных.

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

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

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

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

Изучите данные с помощью сводной таблицы

Изучать импортированные данные легко с помощью сводной таблицы.В сводной таблице вы перетаскиваете поля (аналогичные столбцам в Excel) из таблиц (например, таблицы, которые вы только что импортировали из базы данных Access) в различные области , сводной таблицы, чтобы настроить способ представления данных. Сводная таблица имеет четыре области: ФИЛЬТРЫ , КОЛОННЫ , СТРОКИ и ЗНАЧЕНИЯ .

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

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

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

2. Затем из таблицы Disciplines перетащите Discipline в область ROWS .

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

   1. Щелкните в любом месте сводной таблицы, чтобы убедиться, что выбрана сводная таблица Excel. В списке полей сводной таблицы , где развернута таблица Дисциплины , наведите указатель мыши на ее поле Дисциплина, и справа от поля появится стрелка раскрывающегося списка. Щелкните раскрывающийся список, щелкните (Выбрать все) , чтобы удалить все выборы, затем прокрутите вниз и выберите Стрельба из лука, Дайвинг, Фехтование, Фигурное катание и Конькобежный спорт.Нажмите ОК .

   2. Или в разделе Ярлыки строк сводной таблицы щелкните раскрывающийся список рядом с Ярлыки строк в сводной таблице, щелкните (Выбрать все) , чтобы удалить все выборки, затем прокрутите вниз и выберите Стрельба из лука, Дайвинг, Фехтование, Фигурное катание и конькобежный спорт. Нажмите ОК .

4. В полях сводной таблицы из таблицы Медали перетащите Медаль в область ЗНАЧЕНИЯ .Поскольку значения должны быть числовыми, Excel автоматически изменяет медаль на Количество медалей .

5. Из таблицы Медали снова выберите Медаль и перетащите ее в область ФИЛЬТРЫ .

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

   1. В сводной таблице щелкните раскрывающийся список справа от Ярлыки столбцов .

   2. Выберите Фильтры значений и выберите Больше, чем….

   3. Введите 90 в последнем поле (справа). Нажмите ОК .
      

Ваша сводная таблица выглядит как на следующем экране.

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

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

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

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

Начнем с создания пустого листа, а затем импортируем данные из книги Excel.

1. Вставьте новый лист Excel и назовите его Sports .

2. Перейдите в папку, содержащую загруженные файлы данных примеров, и откройте OlympicSports.xlsx .

3. Выберите и скопируйте данные в Лист1 . Если вы выбрали ячейку с данными, например ячейку A1, вы можете нажать Ctrl + A, чтобы выбрать все смежные данные. Закройте книгу OlympicSports.xlsx.

4. На листе Sports поместите курсор в ячейку A1 и вставьте данные.

5. Не снимая выделения с данных, нажмите Ctrl + T, чтобы отформатировать данные в виде таблицы. Вы также можете отформатировать данные в виде таблицы с ленты, выбрав HOME> Форматировать как таблицу . Поскольку данные имеют заголовки, выберите Моя таблица имеет заголовки в появившемся окне Создать таблицу , как показано здесь.

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

6. Назовите таблицу. В ИНСТРУМЕНТЫ ТАБЛИЦ> ДИЗАЙН> Свойства найдите поле Имя таблицы и введите Sports . Книга выглядит как на следующем экране.
   

7. Сохраните книгу.

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

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

1. Вставьте новый лист Excel и назовите его Хосты .

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

Город	NOC_CountryRegion	Альфа-2 Код	Издание	Сезон
Мельбурн / Стокгольм	AUS	AS	1956	Лето
Сидней	AUS	AS	2000	Лето
Инсбрук	AUT	AT	1964	Зима
Инсбрук	AUT	AT	1976	Зима
Антверпен	BEL	BE	1920	Лето
Антверпен	BEL	BE	1920	Зима
Монреаль	МОЖНО	CA	1976	Лето
Лейк-Плэсид	МОЖНО	CA	1980	Зима
Калгари	МОЖНО	CA	1988	Зима
ул.Мориц	SUI	СЗ	1928	Зима
Санкт-Мориц	SUI	СЗ	1948	Зима
Пекин	ЧН	СН	2008	Лето
Берлин	GER	GM	1936	Лето
Гармиш-Партенкирхен	GER	GM	1936	Зима
Барселона	ESP	СП	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	Зима

1. В Excel поместите курсор в ячейку A1 на листе Hosts и вставьте данные.

2. Отформатируйте данные в виде таблицы. Как описано ранее в этом руководстве, вы нажимаете Ctrl + T, чтобы отформатировать данные в виде таблицы, или из HOME> Format as Table . Поскольку у данных есть заголовки, выберите Моя таблица имеет заголовки в появившемся окне Создать таблицу .

3. Назовите таблицу. В ИНСТРУМЕНТЫ ТАБЛИЦ> ДИЗАЙН> Свойства найдите поле Имя таблицы и введите Хосты .

4. Выберите столбец «Издание» и на вкладке HOME отформатируйте его как Число с 0 десятичными знаками.

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

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

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

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

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

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

3. Разверните Sports и выберите Sport , чтобы добавить его в сводную таблицу. Обратите внимание, что Excel предлагает вам создать связь, как показано на следующем экране.
   
   

   Это уведомление появляется из-за того, что вы использовали поля из таблицы, которая не является частью базовой модели данных.Один из способов добавить таблицу в модель данных — создать связь с таблицей, которая уже находится в модели данных. Для создания связи в одной из таблиц должен быть столбец с уникальными неповторяющимися значениями. В примере данных таблица Disciplines , импортированная из базы данных, содержит поле со спортивными кодами, называемое SportID. Те же спортивные коды представлены в виде поля в импортированных нами данных Excel. Давайте создадим отношения.

4. Щелкните СОЗДАТЬ… в выделенной области Поля сводной таблицы , чтобы открыть диалоговое окно Создать связь , как показано на следующем экране.

5. В таблице выберите Дисциплины из раскрывающегося списка.

6. В столбце (иностранный) выберите SportID .

7. В связанной таблице выберите Sports .

8. В Связанном столбце (основном) выберите SportID .

9. Щелкните ОК .

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

Преобразование базы данных Access в Excel

Как экспортировать данные из Microsoft Access в электронную таблицу Excel.

Часто при изучении Access у многих возникает вопрос: «Как преобразовать базу данных Access в электронную таблицу Excel?».

Ну, вы на самом деле не конвертируете Access в Excel. Но обычно они имеют в виду «как мне экспортировать данные из моей базы данных Access в электронную таблицу Excel».

Часто они хотят это сделать, потому что им нужно передать данные кому-то еще, у кого может не быть Microsoft Access. Но даже если у них есть Access, часто проще отправить кому-нибудь простую электронную таблицу, содержащую именно те данные, которые им требуются, чем отправить им всю базу данных, а затем сказать: «Выполните запрос XYZ».

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

Вот как это сделать.

1. Запуск диалогового окна экспорта

   Щелкните правой кнопкой мыши запрос в левом меню и выберите Экспорт> Excel из контекстного меню.

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

2. Параметры экспорта

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

   Щелкните OK , чтобы экспортировать данные.

3. Сохранить шаги экспорта?

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

   В этом примере я решил не делать этого.Для завершения щелкните Закрыть .

4. Просмотр экспортированного файла

   Теперь вы можете проверить экспортированный файл в Excel. Перейдите в место, которое вы выбрали на шаге 2, и откройте файл.

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

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

Как собрать и экспортировать данные из XML-файлов в Excel

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

Давайте посмотрим на его инструменты для создания и обработки файлов веб-приложений.На конкретном примере изучим основы работы с XML в Excel.

Взаимодействие с другими людьми

Как создать XML-файл в Excel

XML — это файловый стандарт для передачи файлов через Интернет. Excel поддерживает экспорт и импорт.

Рассмотрим процесс создания XML-файла на примере заводского календаря.

1. Создайте таблицу, на которой вы хотите создать XML-файл в Excel, и заполните ее данными.
2. Создайте и вставьте карту с необходимой структурой XML-документа.
3. Экспорт данных таблицы в формате XML.

Всего.

1. Наша таблица — это праздничный календарь.
2. Создайте в любом текстовом редакторе (например, «Блокнот») желаемая XML-структура для создания файла карты сохраняется. В этом примере используйте структуру карты:
3. Откройте инструмент «РАЗРАБОТЧИК». На вкладке XML выберите диалоговое окно «Источник».
4. Если Excel не видит карты XML, их необходимо добавить. Нажмите кнопку «XML Maps».
5. Нажмите «Добавить».Найдите свой XML-файл — нажмите ОК.
6. Элементы схемы появятся в правом столбце. Их нужно перетащить в соответствующие имена столбцов таблицы.
7. Проверить, возможен ли экспорт.
8. Когда все элементы будут связаны, щелкните правой кнопкой мыши любую ячейку в таблице и выберите XML — Экспорт.

Взаимодействие с другими людьми

Сохраните файл.

Как сохранить файл Excel в формате XML

Один из вариантов:

1. Нажмите CTRL + S, выберите «Сохранить как» — «Другие форматы».
2. Задайте имя. Выберите место назначения и тип файла — XML.

Нажать кнопку «Сохранить».

При возникновении ошибок попробуйте сохранить книгу как таблицу или веб-страницу XML 2003. Обычно с этими форматами проблем не возникает.

Другие варианты:

1. Загрузите конвертер XLC в XML или найдите службу, которая экспортирует файлы в Интернете.
2. Загрузите надстройку XML Tools с официального веб-сайта Microsoft. Это доступно бесплатно.
3. Откройте новую книгу.Нажмите CTRL + O «Открыть».

Как открыть файл XML в Excel

1. Измените формат на файл XML. Выберите нужный файл. Нажмите CTRL + O.
2. Открыть как XML-таблицу. Нажмите ОК.
3. Появится аналогичное уведомление.
4. Нажмите ОК. Вы можете работать с этой таблицей так же, как и с любым файлом Excel.

Как преобразовать файл XML в Excel

1. Откройте инструмент «РАЗРАБОТЧИК». Выберите диалоговое окно «Импорт».
2. Выберите в диалоговом окне файл, который вы хотите преобразовать.
3. Нажмите «Импорт». Excel предложит создать схему автоматически на основе полученных данных. Нажмите ОК. Откроется окно, в котором вам нужно будет выбрать место назначения для импортируемого файла.
4. Задайте диапазон для импорта. Лучше добавить дополнительные ячейки для будущих записей. Нажмите «ОК».

С этого момента созданная таблица редактируется и сохраняется в формате Excel.

Как собрать данные из XML-файлов в Excel

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

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

Для настройки параметров привязки в меню «РАЗРАБОТЧИК» выберите инструмент «Свойства карты».

Особенности:

1. Excel будет проверять каждый новый файл на соответствие существующей карте (если вы установите флажок рядом с этой строкой).
2. Данные можно обновлять. Также в существующую таблицу можно добавлять новую информацию (имеет смысл, если вам нужно собрать данные из похожих файлов).

Другие способы получения данных структуры (схемы):

1. Загрузите его из базы данных специализированного бизнес-приложения. Коммерческие сайты и сервисы могут предоставлять схемы. Простые варианты находятся в свободном доступе.
2. Используйте подготовленные примеры для проверки карт. Примеры содержат основные элементы и данные структуры.Скопируйте и вставьте его в программу «Блокнот» — и сохраните с желаемым расширением.

Это все ручные методы импорта и экспорта данных структуры файлов.

Как автоматически экспортировать данные из SQL Server в Excel

«Здравствуйте, я новичок в SQL. Мы используем SQL-запросы в SQL 2016 Server. А затем мы экспортируем вывод в Excel. Не могли бы вы рассказать мне, как сделать процесс экспорта данных из SQL в Excel автоматизированным.Поскольку я больше не хочу выполнять задачу вручную. «

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

Два способа экспорта данных из SQL Server в Excel

Для экспорта данных из SQL Server в Excel можно использовать мастер импорта и экспорта SQL Server и мастер подключения данных.См. Подробности ниже.

Решение 1. Используйте мастер импорта и экспорта SQL Server

SQL Server Management Studio (SSMS) предлагает функцию мастера импорта и экспорта для экспорта данных из одного источника данных в другой источник данных. А именно, вы можете применить эту функцию для экспорта SQL в Excel. Для экспорта базы данных SQL Server в CSV перейдите по ссылке, чтобы проверить подробности.

1. Откройте SQL Server Management Studio и подключитесь к базе данных.

2. Перейдите в «Обозреватель объектов», найдите базу данных сервера, которую хотите экспортировать в Excel.Щелкните его правой кнопкой мыши и выберите «Задачи»> «Экспорт данных», чтобы экспортировать данные таблицы в SQL. Затем появится приветственное окно мастера импорта и экспорта SQL Server.

3. Теперь вам нужно щелкнуть раскрывающуюся кнопку Источник данных, чтобы выбрать источник данных, который вы хотите скопировать. Здесь выберите «SQL Server Native Client 11.0». В раскрывающемся списке Имя сервера выберите экземпляр SQL Server. В разделе «Проверка подлинности» выберите проверку подлинности для подключения к источнику данных и в раскрывающемся списке «База данных» выберите базу данных, из которой будут скопированы данные.После того, как все настроено, нажимаем кнопку «Далее».

4. В окне «Выберите место назначения» в поле «Место назначения» выберите элемент Microsoft Excel для экспорта данных из SQL Server в Excel. Выберите нужный путь и версию файла Excel. Затем нажмите кнопку «Далее», чтобы продолжить.

5. На экране «Указать копию таблицы или запрос» вы можете выбрать «Копировать данные из одной или нескольких таблиц или представлений». Затем нажмите «Далее», чтобы продолжить.

6.В окне «Выбор исходной таблицы и представлений» вы можете выбрать одну или несколько таблиц и представлений, из которых вы хотите экспортировать данные SQL Server в Excel. Нажмите кнопку «Предварительный просмотр», чтобы просмотреть, какие данные будут сгенерированы в файл Excel. Нажмите кнопку «Далее», чтобы продолжить.

7. Здесь, в окне «Сохранить и запустить пакет», отметьте «Запустить немедленно» и нажмите кнопку «Далее».

8. В окне мастера «Завершить» вы можете проверить все настройки, установленные в процессе экспорта.Если все в порядке, нажмите «Готово», чтобы начать экспорт базы данных SQL в Excel.

9. На этом шаге вы можете просмотреть процесс экспорта. По завершении нажмите кнопку раскрывающегося меню «Отчет».

10. Вы можете выбрать «Сохранить отчет в файл …» (включая Excel, Блокнот), чтобы сохранить отчет. Здесь вам нужно выбрать Excel в качестве целевого формата файла.

Решение 2. Использование мастера подключения данных в Excel

Другой способ экспортировать данные из SQL Server в файл Excel — использовать диалоговое окно мастера подключения данных в Excel.

1. Запустите Excel, откройте файл Excel, в который вы хотите экспортировать данные SQL Server. На панели инструментов щелкните вкладку «Данные»> выберите «Из других источников»> выберите «Из SQL Server».

2. Откроется окно «Мастер подключения данных». Введите имя экземпляра SQL Server, из которого необходимо экспортировать данные. В разделе «Учетные данные для входа» выберите режим «Использовать проверку подлинности Windows» для подключения к источнику данных и нажмите кнопку «Далее».

3.Выберите базу данных, из которой будут экспортированы данные. И нажмите кнопку «Далее», чтобы продолжить. В следующем окне оставьте все как есть и нажмите кнопку «Готово».

4. В диалоговом окне «Импорт данных» выберите «Таблица» и «Существующий рабочий лист», как показано на снимке экрана. Нажать кнопку «ОК». После этого данные SQL Server будут экспортированы в файл Excel.

Как импортировать и экспортировать данные SQL Server в файл Excel

Есть несколько способов экспортировать / импортировать данные SQL Server в файл Excel.В этой статье будет объяснено несколько способов импорта и экспорта данных SQL Server в файл Excel:

• Экспорт данных SQL Server в файл Excel с помощью
  • мастер импорта и экспорта SQL Server
  • Код T-SQL
  • Экспорт в Excel из ApexSQL Complete
• Импорт данных SQL Server в Excel с помощью мастера подключения данных

Экспорт данных SQL Server в файл Excel с помощью мастера импорта и экспорта SQL Server

В SQL Server Management Studio (SSMS) есть функция, которая обеспечивает экспорт данных из одного источника данных в другой источник данных; в этом случае данные из SQL Server будут скопированы в файл Excel с помощью мастера экспорта.

Чтобы начать использовать эту функцию, перейдите в Object Explorer , щелкните правой кнопкой мыши любую базу данных (например, AdventureworksDW2016CTP3), в разделе Tasks выберите Export Data command:

Откроется окно мастера импорта и экспорта SQL Server :

Чтобы продолжить экспорт данных SQL Server в файл Excel, нажмите кнопку Далее . Откроется окно Выбор источника данных .В этом окне можно выбрать источник, из которого вы хотите скопировать данные:

В раскрывающемся списке Источник данных выберите элемент SQL Server Native Client 11.0 . В раскрывающемся списке Имя сервера выберите экземпляр SQL Server, данные с которого нужно скопировать. В разделе Аутентификация выберите режим аутентификации для подключения к источнику данных, а в раскрывающемся списке База данных выберите базу данных, из которой будут скопированы данные.После настройки всех этих параметров нажмите кнопку Далее , чтобы продолжить.

Откроется окно Choose a Destination , в котором можно указать, куда копировать данные из источника данных:

В раскрывающемся списке Назначение выберите пункт Microsoft Excel . В пути к файлу Excel выберите место назначения для скопированных данных из источника данных SQL Server (SQL Data.xlsx). В раскрывающемся списке Excel версии выберите версию листа Microsoft Excel.После того, как все они настроены, нажмите кнопку Next , чтобы продолжить. Но после нажатия кнопки Далее может появиться следующее предупреждающее сообщение:

Операция не может быть выполнена.
ДОПОЛНИТЕЛЬНАЯ ИНФОРМАЦИЯ:
Поставщик «Microsoft.ACE.OLEDB.12.0» не зарегистрирован на локальном компьютере. (Системные данные)

Это происходит потому, что SSMS является 32-разрядным приложением, и когда вы запускаете мастер экспорта через SSMS, он запускает 32-разрядную версию мастера экспорта.С другой стороны, машина (операционная система), на которой установлена ​​SSMS, имеет 64-разрядную версию, а установленный SQL Server — 64-разрядную версию.

Чтобы решить эту проблему, закройте SSMS, перейдите в меню Start и найдите 64-разрядную версию SQL Server Import and Export Data:

После запуска этого мастера импорта и экспорта SQL Server будет отображено то же окно, что и в SSMS. После настройки всех предыдущих параметров нажмите кнопку Далее.Появится окно Указать копию таблицы или запрос :

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

Установите переключатель Копировать данные из одной или нескольких таблиц или представлений и нажмите кнопку Далее . Окно Select Source Table and Views позволяет выбрать одну или несколько таблиц и представлений, из которых вы хотите экспортировать данные SQL Server в файл Excel, установив флажок рядом с именем таблицы / представления:

Как можно заметить, как только будет установлен флажок рядом с таблицей / представлением, имя таблицы / представления будет скопировано в столбец Назначение .Это имя представляет собой имя листа, на котором будут помещены данные из таблицы, это имя можно изменить на все, что вам нравится, но пока оно останется как есть:

Чтобы просмотреть, какие данные будут сгенерированы в файл Excel, нажмите кнопку Preview :

После выбора данных таблиц / представлений для копирования в файл Excel нажмите кнопку Далее :

В окне Сохранить как запустить пакет установите флажок Запустить немедленно и нажмите кнопку Далее :

В окне Complete the Wizard показаны все параметры, выбранные для операций экспорта.Чтобы завершить процесс экспорта данных SQL Server в файл Excel, нажмите кнопку Finish . В последнем окне мастера импорта и экспорта SQL Server показано, что данные были успешно экспортированы или произошли ошибки при экспорте данных:

В нашем случае данные были успешно сгенерированы в файл SQL Data.xlsx на листе DimScenario:

Экспорт данных SQL Server в файл Excel с использованием кода T-SQL

Transact-SQL OPENROWSET можно использовать для экспорта данных SQL Server в файл Excel через SSMS.В редакторе запросов введите и выполните следующий код:

 INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; 
 Database = C: \ Users \ Zivko \ Desktop \ SQL Data.xlsx;', 'SELECT * FROM [Sheet1 $]') 
 SELECT * ИЗ dbo.DimScenario 

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

Msg 15281, уровень 16, состояние 1, строка 1
SQL Server заблокировал доступ к ЗАЯВЛЕНИЮ «OpenRowset / OpenDatasource» компонента «Ad Hoc Distributed Queries», поскольку этот компонент отключен как часть конфигурации безопасности для этого сервера.Системный администратор может включить использование «Ad Hoc Distributed Queries» с помощью процедуры sp_configure. Для получения дополнительной информации о включении «Ad Hoc Distributed Queries» найдите «Ad Hoc Distributed Queries» в электронной документации по SQL Server.

Чтобы устранить эту ошибку, необходимо включить параметр Ad Hoc Distributed Queries. Это можно сделать с помощью процедуры sp_configure и выполнения следующего кода SQL в редакторе запросов:

 EXEC sp_configure 'показать дополнительные параметры', 1
ПЕРЕКОНФИГУРИРОВАТЬ
EXEC sp_configure 'Специальные распределенные запросы', 1
РЕКОНФИГУРА 

После выполнения приведенного выше кода появится следующее сообщение, указывающее, что показывает расширенные параметры. и Ad Hoc Distributed Queries. Параметры включены:

Параметр конфигурации «показать дополнительные параметры» изменен с 0 на 1.Для установки запустите оператор RECONFIGURE.
Параметр конфигурации «Ad Hoc Distributed Queries» изменен с 0 на 1. Запустите инструкцию RECONFIGURE для установки.

Теперь, когда снова выполняется код:

 INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; 
 Database = C: \ Users \ Zivko \ Desktop \ SQL Data.xlsx;', 'SELECT * FROM [Sheet1 $]') 
 SELECT * ИЗ dbo.DimScenario 

Может появиться следующая ошибка:

Провайдер OLE DB «Microsoft.ACE.OLEDB.12.0 »для связанного сервера« (null) »вернул сообщение« Ядро базы данных Microsoft Access не может открыть или записать в файл ». Он уже открыт исключительно другим пользователем, или вам нужно разрешение на просмотр и запись его данных ».
Msg 7303, уровень 16, состояние 1, строка 1
Не удается инициализировать объект источника данных поставщика OLE DB «Microsoft.ACE.OLEDB.12.0» для связанного сервера «(null)

Обычно это происходит из-за неадекватных разрешений.

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

Один из способов решения этой проблемы — открыть SSMS от имени администратора и выполнить код еще раз. Но на этот раз может появиться другая ошибка:

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

Чтобы решить эту проблему, откройте файл Excel (например, SQL Data.xlsx), для которого планируется хранить данные из таблицы SQL Server (например, dbo.DimScenario), и введите имена столбцов, которые будут представлять имена столбцов из таблицы DimScenario:

Закройте данные SQL.xlsx и еще раз выполните код:

Теперь появится следующее сообщение:

(Затронуты 3 ряда)

Наконец, данные из таблицы SQL Server копируются в файл Excel:

Импорт данных SQL Server в Excel с помощью диалогового окна мастера подключения данных

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

Для этого откройте файл Excel (например,грамм. SQL Data.xlsx), в который вы хотите импортировать данные. На вкладке Data в подменю From Other Source выберите команду From SQL Server :

Откроется диалоговое окно мастера подключения данных . В поле «Имя сервера» введите имя экземпляра SQL Server, с которого необходимо скопировать данные. В разделе Log on credentials выберите режим аутентификации для подключения к источнику данных и нажмите кнопку Next :

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

В этом окне оставьте все как есть и нажмите кнопку Finish :

В диалоговом окне Импорт данных выберите переключатель Таблица под Куда вы хотите поместить данные? , выберите переключатель Существующий рабочий лист и нажмите кнопку OK :

После нажатия кнопки OK данные из таблицы DimScenario будут скопированы в данные SQL.xslx файл:

Экспорт данных SQL Server в файл Excel с помощью Экспорт в Excel из ApexSQL Complete

Используя функцию Export to Excel из ApexSQL Complete, бесплатной надстройки для SSMS и Visual Studio, экспорт данных из таблицы SQL Server в файл Excel можно выполнить одним щелчком мыши.

В редакторе запросов введите и выполните следующий код:

 ВЫБРАТЬ * ИЗ dbo.DimScenario ds 

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

Это откроет новую книгу Excel, и выбранные данные из таблицы результатов будут скопированы на первый рабочий лист:

Подробнее об экспорте в Excel через ApexSQL Complete можно найти на странице «Экспорт в Excel».

См. Также:

11 сентября 2017 года.