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

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

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

Внешний вид рабочей области программы – таблица. А реляционная база данных структурирует информацию в строки и столбцы. Несмотря на то что стандартный пакет MS Office имеет отдельное приложение для создания и ведения баз данных – Microsoft Access, пользователи активно используют Microsoft Excel для этих же целей. Ведь возможности программы позволяют: сортировать; форматировать; фильтровать; редактировать; систематизировать и структурировать информацию.

То есть все то, что необходимо для работы с базами данных. Единственный нюанс: программа Excel — это универсальный аналитический инструмент, который больше подходит для сложных расчетов, вычислений, сортировки и даже для сохранения структурированных данных, но в небольших объемах (не более миллиона записей в одной таблице, у версии 2010-го года выпуска ).

Структура базы данных – таблица Excel

База данных – набор данных, распределенных по строкам и столбцам для удобного поиска, систематизации и редактирования. Как сделать базу данных в Excel?

Вся информация в базе данных содержится в записях и полях.

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

Поле – столбец в БД, содержащий однотипные данные обо всех объектах.

Записи и поля БД соответствуют строкам и столбцам стандартной таблицы Microsoft Excel.

Если Вы умеете делать простые таблицы, то создать БД не составит труда.

Создание базы данных в Excel: пошаговая инструкция

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

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

  1. Вводим названия полей БД (заголовки столбцов).
  2. Вводим данные в поля БД. Следим за форматом ячеек. Если числа – то числа во всем столбце. Данные вводятся так же, как и в обычной таблице. Если данные в какой-то ячейке – итог действий со значениями других ячеек, то заносим формулу.
  3. Чтобы пользоваться БД, обращаемся к инструментам вкладки «Данные».
  4. Присвоим БД имя. Выделяем диапазон с данными – от первой ячейки до последней. Правая кнопка мыши – имя диапазона. Даем любое имя. В примере – БД1. Проверяем, чтобы диапазон был правильным.

Основная работа – внесение информации в БД – выполнена. Чтобы этой информацией было удобно пользоваться, необходимо выделить нужное, отфильтровать, отсортировать данные.

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

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

  1. Выделяем тот диапазон, который нужно отсортировать. Для целей нашей выдуманной компании – столбец «Дата заключения договора». Вызываем инструмент «Сортировка».
  2. При нажатии система предлагает автоматически расширить выделенный диапазон. Соглашаемся. Если мы отсортируем данные только одного столбца, остальные оставим на месте, то информация станет неправильной. Открывается меню, где мы должны выбрать параметры и значения сортировки.

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

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

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

  1. Одновременным нажатием кнопок Ctrl + F или Shift + F5. Появится окно поиска «Найти и заменить».
  2. Функцией «Найти и выделить» («биноклем») в главном меню.

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

В программе Excel чаще всего применяются 2 фильтра:

  • Автофильтр;
  • фильтр по выделенному диапазону.

Автофильтр предлагает пользователю выбрать параметр фильтрации из готового списка.

  1. На вкладке «Данные» нажимаем кнопку «Фильтр».
  2. После нажатия в шапке таблицы появляются стрелки вниз. Они сигнализируют о включении «Автофильтра».
  3. Чтобы выбрать значение фильтра, щелкаем по стрелке нужного столбца. В раскрывающемся списке появляется все содержимое поля. Если хотим спрятать какие-то элементы, сбрасываем птички напротив их.
  4. Жмем «ОК». В примере мы скроем клиентов, с которыми заключали договоры в прошлом и текущем году.
  5. Чтобы задать условие для фильтрации поля типа «больше», «меньше», «равно» и т.п. числа, в списке фильтра нужно выбрать команду «Числовые фильтры».
  6. Если мы хотим видеть в таблице клиентов, с которыми заключили договор на 3 и более лет, вводим соответствующие значения в меню пользовательского автофильтра.

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

  1. Выделяем те данные, информация о которых должна остаться в базе видной. В нашем случае находим в столбце страна – «РБ». Щелкаем по ячейке правой кнопкой мыши.
  2. Выполняем последовательно команду: «фильтр – фильтр по значению выделенной ячейки». Готово.

Если в БД содержится финансовая информация, можно найти сумму по разным параметрам:

  • сумма (суммировать данные);
  • счет (подсчитать число ячеек с числовыми данными);
  • среднее значение (подсчитать среднее арифметическое);
  • максимальные и минимальные значения в выделенном диапазоне;
  • произведение (результат умножения данных);
  • стандартное отклонение и дисперсия по выборке.

Порядок работы с финансовой информацией в БД:

  1. Выделить диапазон БД. Переходим на вкладку «Данные» — «Промежуточные итоги».
  2. В открывшемся диалоге выбираем параметры вычислений.

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

Готовые образцы шаблонов для ведения клиентской базы по сегментам.

  1. Шаблон для менеджера, позволяющий контролировать результат обзвона клиентов. Скачать шаблон для клиентской базы Excel. Образец:
  2. Простейший шаблон.Клиентская база в Excel скачать бесплатно. Образец:

Шаблоны можно подстраивать «под себя», сокращать, расширять и редактировать.

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

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

Читать еще:  Как сделать excel a4?

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

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

Со всем этим вполне может справиться 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 и вводим туда код нашего макроса:

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

Читать еще:  Как сделать морской бой в excel?

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

Также, выделив любую ячейку в сводной и нажав кнопку Сводная диаграмма (Pivot Chart) на вкладке Анализ (Analysis) или Параметры (Options) можно быстро визуализировать посчитанные в ней результаты.

Шаг 6. Заполняем печатные формы

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

Предполагается, что в ячейку C2 пользователь будет вводить число (номер строки в таблице Продажи, по сути), а затем нужные нам данные подтягиваются с помощью уже знакомой функции ВПР (VLOOKUP) и функции ИНДЕКС (INDEX) .

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

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

  • 1. Мы перенесем (импортируем) Список Excel Книги в Access и поработаем с формами, запросами и отчетами. Формы Access создаются Мастерами буквально несколькими кликами мыши. Запросы позволяют манипулировать информацией: отбирать, преобразовывать, вычислять. Это больше и легче, чем фильтрация Excel. Отчеты Access позволяют создавать на основе информации базы данных, часто, используя запросы, выходные документы с колонтитулами, названиями, заголовками и другими атрибутами документа.
  • 2. Мы научимся использовать шаблон Microsoft Access электронного каталога Biblio и модифицируем его в соответствии с нашими потребностями. Серьезные специалисты работают с текстами, делают выписки. Но нужен инструмент, который бы позволил среди этих многочисленных выписок находить полезные именно для текущей работы. Делать это можно с использованием ключевых слов. Мы сделаем такую базу данных. Легко. Действительно легко.
  • 3. Модифицировать — это часто, как носить костюм, сшитый для другого человека. Можно походить и в таком, но все же приятнее, когда вещь сделана именно для тебя. То есть, если это не очень сложно для вас, было бы полезно научиться делать несложные базы данных самостоятельно. Мы обозначим некоторый минимум знаний, который позволит создавать базы данных в 3—5 таблиц с отношениями многие-ко-многим, подстановками и т.д. И научимся с ними работать.

Таблицы Google, облако, таблицы Excel — это все очень хорошо. Но мы хотим показать, что манипулировать данными о книгах много проще в Access. Предварительно определим, что

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

Мы об этом говорили, но подчеркнем еще раз: очень важно определить перечень вопросов, на которые должны быть получены ответы с помощью базы данных и задач, которые необходимо решать. Также полезно определить некоторые рамки. Например, насколько большой будет эта база данных, на каком временном интервале эта база данных будет использоваться. Мне думается, что 3—5 тыс. книг это более чем приличная библиотека. Если человек активно использует несколько сотен книг — это уже очень много. Но в каталог библиотеки может попасть в несколько раз больше книг, поскольку далеко не все заинтересовавшие вас книги станут вашим рабочим инструментом.

Наши задачи следующие.

  • 1. Определить наличие той или иной книги в домашней библиотеке.
  • 2. Подготовить требование для книги из библиотеки Университета (вы работали с этой книгой и в вашем каталоге есть реквизиты книги).
  • 3. Составить список литературы по учебной дисциплине.
  • 4. Составить список литературы по проблеме, которой вы занимаетесь.
  • 5. Составить список литературы по определенной проблеме за определенный период времени.
  • 6. И, может быть главное, обеспечить возможность создания выписок из изученных книг и их систематизацию по ключевым словам.

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

При исключении повторяющихся записей можно использовать один из двух подходов. Во-первых, Расширенный фильтр. Если выполнить команду Данные / Дополнительно, то появится окно Расширенный фильтр (рис. 4.1). В этом окне нужно выставить флаг Только уникальные записи.

Рис. 4.1. Фильтрация только в уникальных записях

Во-вторых, на вкладке Данные в группе Работа с данными есть команда Удалить дубликаты. После выполнения команды появится окно оповещения (рис. 4.2). [1] [2]

Рис. 4.2. Окно оповещения об удалении дубликатов записей

Рис. 43. Шаблоны баз данных Access

A/c. 4.4. Начальная страница работы с пустой базой данных

Чтобы импортировать в Access таблицу Excel из Excel, необходимо выполнить команду Внешние данные / Excel. В результате появится окно Внешние данные (рис. 4.5).

Рис. 4.5. Импорт таблицы в Access таблицы Excel

Через кнопку Обзор нужно выбрать файл для импорта данных. Это также файл Книги80. Заголовки столбцов таблицы Excel будут полями таблицы Access. Лучше, если будут удалены пробелы между словами в этих заголовках. Система, конечно, укажет на ошибки, но чтобы потом с ними не возиться, следует перед импортом внимательно просмотреть таблицу Excel. Вот что получится, если этого не сделать (рис. 4.6).

Рис. 4.6. Пример ошибки при импорте

В нашем случае просто отсутствовал один из заголовков. Хорошо, если названия будут из одного слова: ГодИздания, но не Год издания. Первое начертание упростит дальнейшие манипуляции с именами полей (их не нужно будет брать в кавычки).

Система может автоматически создать ключевое поле, поле счетчика, когда каждой записи присваивается порядковый номер (рис. 4.7). Сейчас этот вопрос для нас не является принципиальным: можно выбрать опцию автоматического создания ключа, можно ключ не создавать.

Рис. 4.7. Работа с мастером Импорта

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

Чтобы работать в режиме конструктора, необходимо вызвать Контекстное меню (рис. 4.9). Работать с ним предпочтительнее, нежели обращаться к командам ленты.

Рис. 4.8. Импортированная таблица в режиме конструктора

Рис. 4.9. Работа с Контекстным меню

  • [1] Экспериментируйте. Попробуйте оба варианта. Мне второй кажетсяболее наглядным. Но если необходимо еще и осуществлять фильтрацию,то предпочтительным может оказаться первый. Хотя, конечно, дело вкуса. Список книг, с которым мы работали (Книги70), имел еще одну неправильность: в поле Ключевые слова через запятую могли быть записанынесколько ключевых слов. В Access и это недопустимо. Чтобы получитьправильную таблицу, необходимы следующие действия:
  • [2] лист Книги70 копируется в лист с именем одного из ключевых слов,например Инн; 2) в листе Инн фильтруются записи с ключевым словом Инн; 3) но среди этих записей будут и такие, где помимо ключевого словаИнн будут через запятую и другие. Размножением в поле КлСлова остается только слово Инн; 4) далее фильтруются все записи, которые не содержат ключевого словаИнн. Строки, удовлетворяющие фильтру, уничтожаются. Именно строки; 5) подобная процедура проделывается для каждого ключевого слова; 6) все вновь созданные листы копируются в лист Итог. В результате мы получили новый список книг, который стал более чемна 10% больше. Назовем его Книги80 (по числу записей). Запустим приложение Microsoft Access из пакета Microsoft Office.Чтобы импортировать Список Excel в базу данных Access, необходимо создать базу данных. Создадим базу данных на основе Пустой базы данныхна рабочем столе (рис. 4.3) и назовем ее Книги. Автоматически будет создана Таблица 1, которую несколько позжеможно будет удалить (рис. 4.4).
Читать еще:  Как сделать закрепленную область в excel?

Сводная из базы данных Access

Предположим, что у вас есть большая база данных. Назовем её «products». И под большая я подразумеваю порядка 3млн записей(строк) или больше. Непосредственно на один лист Excel такое количество данных точно не поместится. Можно, конечно, хранить и на разных листах. Тогда можно воспользоваться статьей Сводная таблица из нескольких листов. Но во-первых, данный метод работает не очень стабильно и может требовать изменений в зависимости от версии Excel и так же требует разрешения выполнения макросов, а во-вторых, для такого количества записей это не лучшее решение, т.к. хранить такое количество данных в книгах Excel не совсем правильно. Поэтому даже если у вас есть несколько книг/листов, забитых нужными данными по полной и надо эти данные объединить для дальнейшего анализа сводной таблицей — то самое лучшее на мой взгляд решение, это объединить их через MS Access в одну таблицу и потом уже построить сводную на основании таблицы не составит труда.

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

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

Теперь, когда все таблицы готовы можно приступить к импорту данных в Access. Открываете Access и выбираете Создать (New)Новая база данных (Blank database) . Указываете имя базы и месторасположение(папку):

После создания базы проходим несколько шагов:

  1. переходим на вкладку Внешние данные (External Data) -группа Импорт и связи (Import & Link)Excel.
  2. Выбираем файл, данные из которого необходимо перенести в Access
  3. Указываем Импортировать данные источника в новую таблицу в текущей базе данных (Import the source data into a new table in the current database) :
  4. на следующем шаге будет предложено выбрать лист или именованный диапазон для импорта (Show worksheets, Show named ranges) :

    я выбрал лист «products», т.к. именно так у меня называется лист с данными. Жмем Далее (Next)
  5. на этом шаге просто убеждаемся, что галка Первая строка содержит заголовки столбцов (First Row Contains Column Heading) установлена. Если нет — устанавливаем
  6. жмем Готово (Finish)

Первая часть базы наполнена. Теперь необходимо дополнить созданную в Access таблицу данными других листов или книг. Для этого повторяем все описанные выше шаги, но на 2-м шаге выбираем Добавить копию записей в конец таблицы (Append a copy of the record to the table) . Тогда данные будут дополнены в уже созданную нами таблицу из первого листа, а не будут записаны в новую(чего нам не надо).
Все, теперь можно приступать к созданию сводной таблицы.

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

  • Excel 2010Файл (File)Параметры (Options)Панель быстрого доступа (Quick Access Toolbar)
  • Excel 2007Кнопка офисПараметры Excel (Excel options)Панель быстрого доступа (Quick Access Toolbar)

или непосредственно с панели быстрого доступа:

Выбрать команды из: Все команды (All Commands) . Ищем там Мастер сводных таблиц и диаграмм (PivotTable and PivotChart Wizard) и переносим на панель быстрого доступа:

Теперь жмем на эту кнопку и на первом шаге появившегося окна Мастера выбираем во внешнем источнике данных (external data source) :

на втором шаге жмем кнопку Получить данные (Get Data) :

В появившемся окне необходимо выбрать MS Access Database или База данных MS Access.

Тут есть важный момент. Галочка Использовать мастер запросов (Use the Query Wizard to create/edit queries) должна быть включена. Жмем ОК.

Далее выбираем в правом окне папку, в которой расположена наша база данных. В левом окне выбираем сам файл Базы данных:

Подтверждаем выбор нажатием кнопки ОК.
Далее необходимо создать запрос выборки. По сути можно просто нажать на имя таблицы Базы данных и после этого на значек «>»

Но если вам необходимо будет работать только с некоторыми столбцами из всей таблицы — можно последовательно перенести их в правое поле(предварительно в левом развернув плюсик рядом с именем таблицы). Убрать лишние столбцы из правого поля можно кнопочкой » (Return Data to Microsoft Excel) и жмем Готово. При этом в окне второго шага мастера сводных таблиц и диаграмм правее кнопки Получить данные должна появиться надпись Данные получены (Data fields have been retrieved) :

Если у вас данная надпись появилась, то смело жмем Далее (Next) и на последнем шаге мастера выбираем ячейку и лист, в которые необходимо поместить сводную таблицу:

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

Статья помогла? Поделись ссылкой с друзьями!

Ссылка на основную публикацию
Adblock
detector