База данных vba: Excel — eXcellence team

Программы и макросы Excel в категории База данных

Программы для работы с базой данных в Excel — обработка баз данных, ввод и редактирование данных через форму, формирование отчётов, поиск по базе данных Excel

  • Программное создание формы для ввода и редактирования данных таблицы Excel

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

  • Программа формирования договоров по шаблону

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

  • Программа формирования договоров в Word из таблицы Excel

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

  • База данных «Преподаватели»

    База данных «Преподаватели» предназначена для автоматизации работы администрации учебных заведений.
    Программа обеспечивает выполнение всех необходимых операций по приему документов, поиску и анализу информации, составлению отчетов.
    Эта база данных представляюет собой урезанную и немного изменённую версию программы АИСС СПК.
    Программа обеспечивает:
    Хранение полной…

  • Программа оформления и редактирования заказов

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

  • Автоматизированная информационно-справочная система сотрудника приемной комиссии

    Программа АИСС «СПК» предназначена для автоматизации работы приемной комиссии колледжей и техникумов.
    АИСС обеспечивает выполнение всех необходимых операций по приему документов, поиску и анализу информации, составлению отчетов.
    Назначением программы является автоматизация труда работника приёмной комиссии учебного заведения.
    Программа обеспечивает:
    Хранение полной…

  • Программа учёта и администрирования сети связи телекоммуникационной компании

    Программный комплекс, предназначенный для автоматизации учёта и администрирования сети связи телефонной или телекоммуникационной компании.
    Программа позволяет хранить и редактировать базу данных объектов (в виде файлов Excel), просматривать существующие трассы и формировать новые.
     
    Особенность программы — возможность учёта и администрирования узлов связи с нестандартными…

  • База данных больницы — формирование отчётов

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

  • Программа формирования прайс-листов для каталога Onliner.by

    Программа предназначена для формирования прайс-листов на ноутбуки с соответствии с требованиями интернет-каталога Onliner. by
    Основные функции программы:
    (учитываются цены фирм-конкурентов, цены onliner.by и надбавка фирмы)
    загрузка каталога ноутбуков (названия, характеристики, цены) с сайта onliner.by в файл Excel, и обновление этого каталога
    назначение соответствий моделей…

  • Система учёта заявок на заказ запасный частей

    Система учёта заявок предназначена для автоматизации ввода, редактирования и учёта заданий на заказ запасных частей для автомобилей.
    Программа позволяет производить поиск по базе данных, и распечатку информации о выбранном заказе.
    Эта база данных представляет собой урезанную и немного изменённую версию программы АИСС СПК.

  • Программа обработки заданий на производство

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

  • Программа загрузки базы данных с сайта АвтоТрансИнфо

    Описанная в этой статье программа уже не работает (на сайте веели ограничение на просмотр данных)
    Посмотрите видеоинструкцию по работающей нынче программе, — парсер сайта грузоперевозок ati.su
    Программа предназначена для загрузки базы данных сайта autotransinfo.ru (АТИ) в книгу Excel.
    Результатом работы программы является книга Excel, состоящая из 2 листов: «Организации…

  • Обработка объявлений о продаже недвижимости

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

  • Система учета заявок на оборудование металлургического холдинга

    Программа предназначена для работы с заявками на оборудование.
    Цели программы:
    автоматизация составления и редактирования заявок
    преобразование имеющихся заявок в виде отдельных файлов Excel различной структуры к единому виду
    формирование отчётов по категориям и типам заявок
    стандартизация печатной формы заявок
    разграничение доступа на работу с базой данных заявок
     …

  • Программа для игрового клуба / интернет кафе — учет времени

    Программа, выполненная в виде файла Excel, предназначена для управления игровым клубом, администрирования интернет-кафе или подобного заведения.
    В таблице Excel регистрируются сеансы пользователей (время, проведенное за компьютером или игровой приставкой) и все оплаты
    Поддерживаются разные варианты начисления оплаты (5 минут, 10 минут, 1 час, акция 3+1, а также ночной режим — где даётся 7 часов…

  • Программа обработки отчётов управления

    Программа предназначена для обработки отчётов управления по сделкам с ценными бумагами
    Основные функции программы:
    Создание новых отчётов управления (за следующий квартал) на основании текущих отчётов
    Внесение изменений и дополнений в существующие отчёты
    Формирование сводной таблицы по вкладам для оценки роста, а также коэффициентов ликвидности и версификации вкладов
    Обработка. ..

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

152
08.09.2016
Скачать пример


При упоминании баз данных (БД) первым делом, конечно, в голову приходят всякие умные слова типа 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).

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

  • Как использовать функцию ВПР (VLOOKUP) для поиска и подстановки значений

  • Как заменить ВПР функциями ИНДЕКС и ПОИСКПОЗ

  • Автоматическое заполнение форм и бланков данными из таблицы

  • Создание отчетов с помощью сводных таблиц

Использование Excel в качестве базы данных » Chandoo.org

Это гостевой пост от Виджая, нашего штатного эксперта по VBA.

Часто я думал, что если бы я мог написать «Выберите имя сотрудника из листа, где EmployeeID = 123» и использовать это на моем листе Excel, моя жизнь была бы проще. Итак, сегодня мы научимся это делать.

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

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

Excel как база данных — демонстрация

Мы узнаем, как это построить:

Прежде чем мы начнем:

  1. Весь лист (где хранятся необработанные данные) будет называться одной базой данных таблица в экселе. Это очень важно понять.
  2. Это никак не связано со встроенной функцией таблицы (2007 и более поздние версии)/списка (2003 и более ранние версии) Excel.
  3. Если вы знаете SQL (язык структурированных запросов), ваша задача значительно упрощается.

Настройка Excel в качестве базы данных

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

Начнем.

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

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

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

Добавление ссылки на объекты данных Active-x

Нам нужно добавить ссылку на библиотеку объектов данных Microsoft ActiveX, чтобы иметь возможность использовать рабочий лист в качестве таблицы базы данных. Это можно сделать в редакторе Visual Basic > Инструменты.

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

Открытие Excel как базы данных

Как только это будет сделано, нам нужно отправиться в путь с некоторым кодом VBA.


Public Sub OpenDB()
If cnn.State = adStateOpen Then cnn.Close
cnn.ConnectionString = "Driver={Драйвер Microsoft Excel (*.xls, *.xlsx, *.xlsm, *.xlsb)} ;DBQ=" & _
ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
cnn.Open
End Sub

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

cnn.ConnectionString = "Driver={Драйвер Microsoft Excel (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
ActiveWorkbook. Path & Application.PathSeparator & ActiveWorkbook.Name

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

Давайте разберемся с модулем кода

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

Пример раскрывающегося списка «Продукты»


strSQL = «Выбрать отдельный [Продукт] Из [data$] Упорядочить по [Продукту]»
closeRS
OpenDB
cmbProducts.Clear
rs.Open strSQL, cnn, объявлениеOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
Do While Not rs.EOF
cmbProducts.AddItem rs.Fields(0)
rs.MoveNext
Loop
Else
MsgBox «Мне не удалось найти ни одного уникального продукта. », vbCritical + vbOKOnly
Выход Sub
End If

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

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

Использование Excel SQL для объединения двух листов в один

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


ВЫБРАТЬ ID, Имя, Отчество, Фамилию, Возраст, Дата рождения Из [Table1$]
UNION ALL
ВЫБРАТЬ ID, Имя, Отчество, Фамилия, Возраст, Дата рождения Из [Table2$]

Это позволит мне используйте оба листа как одну таблицу и извлеките все мои данные на новый лист.

Загрузить демонстрационный файл базы данных Excel

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

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

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

Подробнее о VBA и макросах

Если вы не знакомы с макросами VBA и Excel, перейдите по этим ссылкам, чтобы узнать больше.

  • Что такое VBA и макросы? Введение
  • Пример макроса Excel VBA
  • Учебные видеоролики по VBA

Присоединяйтесь к нашим классам VBA

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

Щелкните здесь, чтобы узнать больше о классах VBA и присоединиться к нам .

О Виджее

Виджай (многие из вас знают его по курсам VBA) присоединился к chandoo.org на постоянной основе в феврале этого года. Он будет чаще писать об использовании VBA и анализе данных в нашем блоге. Кроме того, Виджей будет помогать нам с консультационными и обучающими программами. Вы можете написать Виджею по адресу sharma.vijay1 @ gmail.com. Если вам понравился этот пост, поблагодарите Виджая.

vba — Поиск в базе данных Access из excel

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

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

 Опция Явная
 Sub getDataFromAccess()
 'Значение поиска ячейки
 Dim var As Integer
 переменная = Диапазон ("A1"). Значение
 Dim DBFullName как строка
 Dim Connect As String, Source As String
 Dim Connection As ADODB.Connection
 Dim Recordset As ADODB.Recordset
 Dim Col как целое число
 'Ячейки.Очистить
 'Информация о пути к базе данных
 DBFullName = "C:\Users\jmike\Desktop\Excel
 база данных - Копировать\Database.accdb"
 'Открыть соединение
 Установить соединение = новый ADODB.Connection
 Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
 Подключить = Подключить & "Источник данных=" & DBFullName & ";"
 Connection.Open ConnectionString:=Connect
'Создать набор записей
 Установить набор записей = новый ADODB.Recordset
 С набором записей
 'Данные фильтра
 Source = "SELECT * FROM Orders Where[Voltage]=var"
 .Открытый источник:=Источник, ActiveConnection:=Соединение
 'Напишите имена полей
 Для Col = 0 To Recordset.Fields.Count - 1
 Range("A2").Offset(0, Col).