Как связать аксесс и эксель

Пример быстрого развертывания системы аналитики на предприятии

Итак, как развернуть систему аналитики на предприятии за максимально короткий промежуток времени? Для этого воспользуемся связкой MS Excel + MS Access. В Access развертываем систему хранения детальных данных, а MS Excel используем как систему визуализации при помощи сводных таблиц.

Вопросы, на которые должна давать ответы аналитическая система:

1. Анализ выручки по предприятию в разрезе групп и месяцев.
2. Проведение ABC и XYZ анализов.
3. Мониторинг акций, проходящих в магазине.

План действий такой:

1. Предварительный анализ размера системы, подсчет количества строк и размера БД.
2. Разработка ТЗ в отдел ИТ для проектирования выгрузки данных из системы управления в нашу систему аналитики.
3. Под ТЗ проектируем БД в Access для дальнейшего автоматического импорта выгрузки под наши нужды.
4. Ну, и самый, пожалуй, легкий, для знатоков Excel — это создание сводной таблицы на основе среза из Access.

Произведем оценку возможности развертывания такой системы на предприятии. Пусть имеется предприятие, продающее бытовую технику. Оцениваемый ежемесячный объем продаваемых товаров через магазин в день составляет 52 товара. Пусть у предприятия имеется 30 магазинов. Тогда оценочный годовой прирост БД составит порядка 52*30*365= 569 400 строк для хранения информации о продажах с каждодневной разбивкой (далее будем называть это Детальным Слоем). Итак, полмиллиона строк — это много или мало? На самом деле, я работал и с 8 000 000 строк в БД Access, и это не составляло особой трудности. То есть БД Access можно использовать в течении 14 лет для хранения детального слоя. Далее мы можем свободно перейти и на другие БД, главное — суметь доказать, что такие проекты приносят реальную пользу и могут быть развернуты в принципиально короткие сроки.

Проектирование ТЗ для выгрузки из системы управления

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

1. Составить запрос информации в разрезе:
   • Магазин (для рассмотрения динамики в разрезе магазинов).
   • Дата (для выявление сезонных и ежедневных колебаний в предпраздничные дни).
   • Название товара (для рассмотрения АВС анализа по товарам).
   • Производитель товара (для исследования распределения брендов).
   • Количество товара, сумма реализации (сами данные).
   • Номер дисконтной карты (проникновение акции среди покупателей).
   • Продавец-консультант (по возможности, для отслеживания наиболее успешных продавцов).
   • Номер сертификата (для отслеживания продвижения акций с сертификатами).
   • Номер заказа или чека (для отслеживания взаимосвязанных товаров).
   • Тип магазина: интернет или обычный (отслеживание доли интернет продаж).
   • Группы товаров (для ABC и XYZ анализа).
   • Покупатель (для отслеживания продаж по покупателям).
   • Направление продаж (B2B, B2C).
   • Название акции, по которой продается товар.
2. Организовать выгрузку в файл, который сможет принять большой объем данных (можно и в CSV, или в dBASE). При экспорте в CSV файл получается большой и надо правильно выбрать разделитель полей (его не должно быть в названии товара!)

Прописываем каталог для обмена данными с IT и приступаем к проектированию БД в системе Access.

Проектирование БД

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

1. Что добавилось (способна ли ваша БД отследить добавление данных и принять их)?
2. Как разработать процедуру добавления данных в БД?

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

Здесь выбрана система снежинка для развертывания БД. Хотя, при удалении таблицы city схема превратится в обычную звездочку. Таблицы, расположенные в центре, являются ключевыми в проекте. Первая таблица cube_sale — это детализированный слой хранения данных, вторая tmp_pr5 таблица со входными данными будет любезно предоставляться для нашей системы отделом ИТ.
Соответствие tmp_pr5 с консольными таблицами осуществляется по названиям (товара, групп товара, бренду и т.д.), тогда как cube_sale связывается с консольными таблицами по ключу для экономии места размещения и скорости работы БД. На основании cube_sale будем строить отчеты и проектировать кубы для дальнейшего анализа. Параллельно можно спроектировать другие детальные слои при необходимости.

Для вставки данных в cube_sale требуется проверка на соответствие всех данных во входной таблице tmp_pr5 с таблицами измерений. Как пример можно предложить следующий SQL запрос для проверки начала продаж нового бренда:

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

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

Получилось 7 запросов, которые отслеживают изменения в каждой таблице. Далее нужно вставить недостающие данные в таблицы измерений. Если не произвести вставку данных в таблицы измерений, то часть данных, которых прислал отдел ИТ, будет утеряна при вставке данных в cube_sale из tmp_prd5.
Для вставки данных из tmp_prd5 в cube_sale я использовал следующий запрос:

Использовать для вставки пришлось по некоторым полям функцию IIf, так как в консольных таблицах были пустые названия (групп товара, брендов и т.д.). Пустые названия в таблицах измерений находились с ключевым значением 1, поэтому использовалась следующая связка IIf(IsNull(tmp_prd5.TOVAR_GRUP),1,tovar_group_name.id_tovar_group), где 1 – это пустая группа товара.
В качестве первой версией куба можно рассмотреть запрос, при котором будет создаваться куб для анализа данных в Excel.

Проект запроса будет следующий в SQL:

Этот куб способен отвечать на следующие группы вопросов. Какова реализация по складам (магазин), по годам, месяцам, кварталам, брендам, группам товаров, выручки товаров и количеству товаров в любом разрезе.
Далее остается только соединить этот запрос с Excel и строить любые графики и сводные таблицы.
Алгоритм соединения очень прост:

1. Данные -> Получить данные из Access.
2. Выбираем подготовленный запрос.
3. Вставляем сводную таблицу и диаграмму.
4. Далее работаем как со сводной таблицей.

На реализацию проекта ушло 2 рабочих дня. Была собрана детальная статистика за период с марта 2009 по март 2013 года. Загрузка данных осуществлялась за годовой промежуток времени. Возникающие сложности в проекте носили лишь технический характер. Такие сложности могут решаться специалистами очень оперативно, главное — не допускать ошибок при проектировании и вставке значений в целевые таблицы.

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

Схема-алгоритм

Дополнительные материалы и ссылки.

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

Обмен данными между Microsoft Excel и Microsoft Access

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

Перенос в Microsoft Excel обновляемых данных Microsoft Access

Если требуется обновить данные в листе при изменении базы данных Microsoft Access, например обновить ежемесячно отправляемые итоги Microsoft Excel, содержащие данные за текущий месяц, перенести данные в Microsoft Excel можно создав запрос или odc-файл. Запрос следует создавать в случае, если требуется получить данные из нескольких таблиц или нужно изменить границы получаемых данных. ODC-файл следует использовать в случае, если требуется получить данные из одной таблицы в базе данных и при этом извлечь все данные в таблице. Данные можно вернуть в Microsoft Excel как внешний диапазон данных или отчет сводной таблицы, причем в обоих случаях доступно обновление.

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

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

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

Если в Microsoft Excel установлена надстройка связей с Microsoft Access, можно использовать некоторые возможности Microsoft Access для сохранения данных Microsoft Excel. Эта надстройка доступна на веб-узле Microsoft Office.

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

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

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

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

Формы для ввода данных

Microsoft Excel предоставляет следующие типы форм, помогающие вводить данные в списки.

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

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

Формы Microsoft Access

Если установлено приложение Microsoft Access, надстройка Excel AccessLinks позволяет создавать формы Microsoft Access для работы с данными в списке Microsoft Excel. Используйте мастер формы Access для создания настраиваемой формы, после чего используйте форму для ввода, поиска и удаления хранящихся на листе данных. Эта программа надстройки доступна на веб-узле Microsoft Office.

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

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

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

Формы мастера шаблонов

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

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

Копирование данных Microsoft Access в Microsoft Excel

Определите объем данных, с которым потребуется работать: 1) вся таблица, запрос либо все данные формы или отчета; 2) только некоторые записи.

Выполните одно из следующих действий.

Скопируйте все данные в Microsoft Excel

В меню Сервис укажите на пункт Связи с Office и выберите команду Анализ в Microsoft Excel.

Примечание. При наличии основной формы и одной или нескольких вспомогательных форм либо основного отчета и одного или нескольких вспомогательных отчетов в книге сохраняются только данные основной формы или отчета Microsoft Access.

Скопируйте выделенные записи в Microsoft Excel

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

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

Щелкните в левом верхнем углу области листа, в которую требуется поместить имя первого поля.

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

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

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

Предположим, мы имеем такую базу данных Access:

Чтобы загрузить данные, откроем пустой файл Excel, выберем в меню Данные — Получить внешние данные из Access.

В появившемся окне, выберем необходимый файл Access. Далее, появится следующее окно:

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

Теперь мы получили таблицу в Excel, которая связана с данными из файла Access . Но наша таблица не является простой, фактически она является запросом к базе данных. Это так называемая Умная таблица, которую можно обновить и получить «свежие» данные (щелкаем правой кнопкой мыши на таблицу и выбираем «Обновить«).

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

Этот пример научит вас импортировать информацию из базы данных Microsoft Access. Импортируя данные в Excel, вы создаёте постоянную связь, которую можно обновлять.

1. На вкладке Data (Данные) в разделе Get External Data (Получение внешних данных) кликните по кнопке From Access (Из Access).
2. Выберите файл Access.
3. Кликните Open (Открыть).
4. Выберите таблицу и нажмите ОК.
5. Выберите, как вы хотите отображать данные в книге, куда их следует поместить и нажмите ОК.

Результат: Записи из базы данных Access появились в Excel.

Примечание: Когда данные Access изменятся, Вам достаточно будет нажать Refresh (Обновить), чтобы загрузить изменения в Excel.