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

Создание форм в Excel: 2 проверенных способа

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


Sub DataEntryForm()
Dim nextRow As Long
nextRow = Producty.Cells(Producty.Rows.Count, 2).End(xlUp).Offset(1, 0).Row
With Producty
If .Range("A2").Value = "" And .Range("B2").Value = "" Then
nextRow = nextRow - 1
End If
Producty.Range("Name").Copy
.Cells(nextRow, 2).PasteSpecial Paste:=xlPasteValues
.Cells(nextRow, 3).Value = Producty.Range("Volum").Value
.Cells(nextRow, 4).Value = Producty.Range("Price").Value
.Cells(nextRow, 5).Value = Producty.Range("Volum").Value * Producty.Range("Price").Value
.Range("A2").Formula = "=IF(ISBLANK(B2), """", COUNTA($B$2:B2))"
If nextRow > 2 Then
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A" & nextRow)
Range("A2:A" & nextRow).Select
End If
.Range("Diapason").ClearContents
End With
End Sub


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

Итак, первая строка:

Sub DataEntryForm()

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

Везде, где встречается в коде слово «Producty» вы должны его заменить на то наименование, которое ранее присвоили для своего листа в поле «(Name)» области «Properties» редактора макросов. Естественно, это нужно делать только в том случае, если вы назвали лист по-другому.

Теперь рассмотрим такую строку:

nextRow = Producty. Cells(Producty.Rows.Count, 2).End(xlUp).Offset(1, 0).Row

Цифра «2» в данной строчке означает второй столбец листа. Именно в этом столбце находится колонка «Наименование товара». По ней мы будем считать количество рядов. Поэтому, если в вашем случае аналогичный столбец имеет другой порядок по счету, то нужно ввести соответствующее число. Значение «End(xlUp).Offset(1, 0).Row» в любом случае оставляем без изменений.

Далее рассмотрим строку

If .Range("A2").Value = "" And .Range("B2").Value = "" Then

«A2» — это координаты первой ячейки, в которой будет выводиться нумерация строк. «B2» — это координаты первой ячейки, по которой будет производиться вывод данных («Наименование товара»). Если они у вас отличаются, то введите вместо этих координат свои данные.

Переходим к строке

Producty.Range("Name").Copy

В ней параметр «Name» означат имя, которое мы присвоили полю «Наименование товара» в форме ввода.

В строках


.Cells(nextRow, 2).PasteSpecial Paste:=xlPasteValues
.Cells(nextRow, 3).Value = Producty.Range("Volum").Value
.Cells(nextRow, 4).Value = Producty.Range("Price").Value
.Cells(nextRow, 5).Value = Producty.Range("Volum").Value * Producty.Range("Price").Value


наименования «Volum» и «Price» означают названия, которые мы присвоили полям «Количество» и «Цена» в той же форме ввода.

В этих же строках, которые мы указали выше, цифры «2», «3», «4», «5» означают номера столбцов на листе Excel, соответствующих колонкам «Наименование товара», «Количество», «Цена» и «Сумма». Поэтому, если в вашем случае таблица сдвинута, то нужно указать соответствующие номера столбцов. Если столбцов больше, то по аналогии нужно добавить её строки в код, если меньше – то убрать лишние.

В строке производится умножение количества товара на его цену:

. Cells(nextRow, 5).Value = Producty.Range("Volum").Value * Producty.Range("Price").Value

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

В этом выражении выполняется автоматическая нумерация строк:


If nextRow > 2 Then
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A" & nextRow)
Range("A2:A" & nextRow).Select
End If


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

В строке производится очистка диапазона формы ввода данных после того, как информация из неё была перенесена в таблицу:

.Range("Diapason").ClearContents

Не трудно догадаться, что («Diapason») означает наименование того диапазона, который мы ранее присвоили полям для ввода данных. Если вы дали им другое наименование, то в этой строке должно быть вставлено именно оно.

Дальнейшая часть кода универсальна и во всех случаях будет вноситься без изменений.

После того, как вы записали код макроса в окно редактора, следует нажать на значок сохранения в виде дискеты в левой части окна. Затем можно его закрывать, щелкнув по стандартной кнопке закрытия окон в правом верхнем углу.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример.

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

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

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

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

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

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

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

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

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

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

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

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

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

Итоги.

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

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

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

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

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

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

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

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

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

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

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

На главную

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

Отзывы

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

Практическая работа «Создание базы данных в 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 и базы данных

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

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

База данных офиса «Родная Речь»

В качестве тестовой базы данных я рассмотрю базу данных гипотетического офиса «Родная Речь», который в дальнейшем буду называть офисом РР. Офис РР занимается издательской деятельностью и имеет отделы (группы), которые непосредственно готовят и издают книги, занимаются маркетингом и рекламой, распространением и сбытом книг. С издательством сотрудничают авторы, переводчики, книготоргующие организации.

Создание в приложении Access базы данных офиса «РР»

Для пользователей Microsoft Office 2000 создание базы данных именно в Access самая естественная вещь. Этот параграф может служить предварительным знакомством с Access для тех, кто действительно не знаком с этим замечательным приложением. Создание базы данных в Access помимо прочего обладает двумя несомненными достоинствами:

• Допускается создание и ведение базы данных простыми и интуитивно понятными средствами в визуальном стиле. Даже человек, далекий от этой весьма специфической области программирования, способен после предварительного непродолжительного знакомства начать создавать свою собственную базу данных.
• В любом из приложений Office 2000 (Word, Excel, PowerPoint) легко получить доступ к БД Access.

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

Построение БД офиса РР начну с введения минимально необходимых средств. По мере необходимости база будет расширяться.

Построение таблиц «Заказчики» и «Книги»

Access относится к реляционным базам данных, в которых основной формой представления данных являются таблицы. Таблицы связываются между собой за счет общих полей. Поскольку таблица еще и общечеловеческая форма представления данных, то ее создание интуитивно понятно всем. Запись (строку) таблицы можно рассматривать, как совокупность полей разного типа. Каждый столбец таблицы хранит значения одного поля. Так что каждая таблица задает некоторый набор записей, и каждая запись представляется одной строкой таблицы. Число полей записи однозначно определяет число столбцов таблицы. Access предоставляет различные средства, облегчающие задание структуры таблицы. Полагаю, что даже человек, не имеющий опыта работы с Access, работая в Конструкторе таблиц Access, без труда определит собственную таблицу. Отмечу лишь, что для каждого поля задается имя, выбирается его тип, и задаются другие свойства поля, например, описание. Свойства поля зависят не только от его типа, они включают такие характеристики, как указание на то, является ли поле индексируемым, имеет ли уникальное значение, должно ли обязательно присутствовать в записи. Они определяют также формат и маску, которой должно удовлетворять значение поля, и другие характеристики.

Я не буду сейчас останавливаться на средствах Access, позволяющих создавать таблицы визуально, они достаточно просты. Для нас сейчас важнее понимать структуру таблиц, входящих в состав базы данных. Начну с определения таблицы «Заказчики», хранящей информацию о заказчиках офиса РР. Каждая ее запись включает следующие поля: Код заказчика, Название, Адрес, Город, Телефон, Директор, Прочее, Email. Вот как выглядит определение таблицы в конструкторе Access, где для каждого поля задаются имя, тип, описание и другие характеристики:

Рис.
4.1.
Структура таблицы «Заказчики

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

Определим теперь структуру таблицы «Книги». Эта таблица содержит информацию о книгах, выпускаемых издательством «РР». Достаточно взглянуть на рисунок, чтобы увидеть, какие поля в нее включены:

Рис.
4.2.
Структура таблицы «Книги»

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

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

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

К сожалению (а может и к счастью), Excel в первую очередь является электронной таблицей, поэтому чтобы создать базу данных в этой программе, требуется выстраивать связи с помощью формул подстановки, создавать интерфейс на VBA и добавлять дашборды. Все это было до недавнего времени. С приходом Excel 2013, программа обзавелась новыми инструментами работы с таблицами, позволяющими связывать диаграммы и ячейки, выполнять поиск и создавать динамически обновляемые отчеты. Все верно!!! Как в реляционных базах данных. Excel может ежедневно обрабатывать большие количество данных. Каким образом? Читайте дальше.

Как создать реляционную базу данных в Excel

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

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

Одной записи основной таблицы может соответствовать несколько записей из дополнительной (или дочерней) таблицы. Эта связь называется один-ко-многим. Информация в дочерней таблице – такая как, ежедневные продажи, цена на продукт, количество – обычно периодически изменяется.

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

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

Создание основной и дополнительной таблиц

Наша основная таблица будет содержать 4 поля: ID Продаж, Имя продавца, Адрес и Город. Создайте таблицу аналогично изображению на рисунке. Данные для таблицы можно взять из файла прикрепленном в конце статьи.

Моя таблица имеет двадцать продавцов из 3-х различных городов, по которым в дальнейшем мы будем делать отчет.

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

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

Осталось дать имя нашей таблице. Выберите любую ячейку в таблице, перейдите по вкладке Работа с таблицами -> Конструктор в группу Свойства. В поле Имя таблицы поменяйте название таблицы на Основной.

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

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

Определение отношений между таблицами

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

Выделяем таблицу с продажами, во вкладке Вставка переходим в группу Таблицы, щелкаем Сводная таблица.

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

Excel создаст новый лист с пустой сводной таблицей. В левой части экрана появится панель Поля сводной таблицы. Чтобы свести данные обоих таблиц, в панели Поля сводной таблицы вкладки Активная проставьте галочки напротив пунктов Квартал 1, Квартал 2, Квартал 3 и Квартал 4. Excel построит сводную таблицу с данными по кварталам, пока не обращайте на нее внимание. Далее в этой же панели переходим на вкладку Все, где вы увидите обе наши таблицы. Ставим галочку напротив поля Город, таблицы Основной. Появится желтое поле с уведомлением Могут потребоваться связи между таблицами, щелкаем кнопку Создать.

В появившемся диалоговом окне Создание связи необходимо определить отношения между таблицами. Выберите из выпадающего списка Таблица пункт Продажи, а из выпадающего списка Столбец (Чужой) пункт ID Продаж. Помните, что ID Продаж единственное поле, которое находится в обоих таблицах. Из списка Связанная таблица выбираем Основной, из списка Связанный столбец (первичный ключ) — пункт ID Продаж. Жмем ОК.

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

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

Вам также могут быть интересны следующие статьи

Создание базы данных — Access

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

В этой статье

Обзор

Создание базы данных с помощью шаблона

Создание базы данных без использования шаблона

Копирование данных из другого источника в таблицу Access

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

Добавление части приложения

Открытие существующей базы данных Access

Обзор

При первом запуске Access, а также при закрытии базы данных без завершения работы Access отображается представление Microsoft Office Backstage.

Представление Backstage является отправным пунктом для создания новых и открытия существующих баз данных, просмотра релевантных статей на сайте Office.com и т. д., то есть для выполнения любых операций с файлом базы данных или вне базы данных, но не в ней.

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

При открытии Access в представлении Backstage откроется вкладка создать . На вкладке » Создание » можно создать новую базу данных несколькими способами.

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

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

• 
  Шаблон из Office.com    В дополнение к шаблонам, поставляемым с Access, много других шаблонов доступно на сайте Office.com. Для их использования даже не нужно открывать браузер, потому что эти шаблоны доступны на вкладке Создать.

Добавление объектов в базу данных

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

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

Также можно создавать запросы, формы, отчеты, макросы — любые объекты базы данных, необходимые для работы.

Создание базы данных с помощью шаблона

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

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

1. Если база данных открыта, нажмите на вкладке Файл кнопку Закрыть. В представлении Backstage откроется вкладка Создать.

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

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

4. Приложение Access предложит имя файла базы данных в поле имя файла — при желании вы можете изменить имя файла. Чтобы сохранить базу данных в другой папке, расположенной под полем Имя файла, нажмите кнопку
   
   , перейдите в папку, в которой вы хотите ее сохранить, и нажмите кнопку ОК. Кроме того, вы можете создать и связать базу данных с сайтом SharePoint.

5. Нажмите кнопку Создать.

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

   На вкладке Главная в группе Записи нажмите кнопку Удалить.

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

Дополнительные сведения о работе с шаблонами см. в статье Создание базы данных Access на компьютере с помощью шаблона.

К началу страницы

Создание базы данных без использования шаблона

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

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

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

Создание пустой базы данных

1. На вкладке Файл щелкните Создать и выберите вариант Пустая база данных.

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

3. Нажмите Создать.

   Access создаст базу данных с пустой таблицей «Таблица1» и откроет ее в режиме таблицы. Курсор будет помещен в первую пустую ячейку столбца Щелкните для добавления.

4. Чтобы добавить данные, начните вводить их или вставьте из другого источника (см. раздел Копирование данных из другого источника в таблицу Access).

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

Если на этом этапе вводить данные в таблицу «Таблица1» не нужно, нажмите кнопку Закрыть

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

Совет:  Access ищет файл с именем Blank.accdb в папке [диск установки]:\Program Files\Microsoft Office\Templates\1049\Access\. Если он существует, то пустой. accdb — это шаблон для всех новых пустых баз данных. Все новые базы данных наследуют содержимое этого файла. Это отличный способ распространения содержимого по умолчанию, например номеров компонентов или заявлений об отказе от ответственности и политик компании.

Важно: Если хотя бы один раз закрыть таблицу «Таблица1» без сохранения, она будет удалена полностью, даже если в нее введены данные.

Добавление таблицы

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

Создание таблицы в режиме таблицы.    В режиме таблицы можно начать ввод данных сразу, структура таблицы при этом будет создаваться автоматически. Полям присваиваются имена с последовательными номерами («Поле1», «Поле2» и т. д.), а тип данных автоматически задается с учетом вводимых данных.

1. на вкладке Создание в группе Таблицы нажмите кнопку Таблица.

   Access создаст таблицу и выделит первую пустую ячейку в столбце Щелкните для добавления.

2. На вкладке Поля в группе Добавление и удаление выберите нужный тип поля. Если нужный тип поля не отображается, нажмите кнопку Другие поля
   
   .

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

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

4. Чтобы добавить данные, начните вводить их в первую пустую ячейку или вставьте из другого источника (см. раздел Копирование данных из другого источника в таблицу Access).

5. Для переименования столбца (поля) дважды щелкните его заголовок и введите новое название.

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

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

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

1. На вкладке Создание в группе Таблицы нажмите кнопку Конструктор таблиц.

2. Для каждого поля в таблице введите имя в столбце Имя поля, а затем в списке Тип данных выберите тип данных.

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

4. Когда все необходимые поля будут добавлены, сохраните таблицу:

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

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

Щелкните свойство поля, чтобы просмотреть его описание рядом со списком Свойства поля. Более подробные сведения можно получить, нажав кнопку справки.

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

К началу страницы

Копирование данных из другого источника в таблицу Access

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

При вставке данных в пустую таблицу приложение Access задает тип данных для каждого поля в зависимости от того, какие данные в нем находятся. Например, если во вставляемом поле содержатся только значения даты, для этого поля используется тип данных «Дата/время». Если же вставляемое поле содержит только слова «Да» и «Нет», для этого поля выбирается тип данных «Логический».

Создание базы данных в Excel и ее функционал

Любая база данных (БД) представляет собой сводную таблицу с параметрами и информацией. Большинство школьных программ предусматривают создание базы данных в Microsoft Access. Но Excel дает все возможности для создания простых баз данных и удобной навигации по ним.

Как сделать базу данных в Excel, чтобы было удобно не только хранить, но и обрабатывать данные: формировать отчеты, строить диаграммы, графики и т.д.

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

Пошаговое создание базы данных в Excel

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

С заголовками ломали голову. Теперь заполните таблицу. Начнем с серийного номера. Нам не нужно вводить числа вручную.Введите в ячейки A4 и A5 один и два соответственно. Затем выберите эти две ячейки левой кнопкой мыши, возьмитесь за угол разделения и пройдите вниз до любого количества строк. В маленьком окошечке вы увидите окончательное число.

Примечание. Эту таблицу можно скачать в конце статьи.

Base показывает нам, что часть информации будет представлена ​​в текстовой форме (продукт, категория, месяц и т. Д.), А часть — в финансовой. Выделите ячейки с заголовками Цена в кг и Общее значение E4: F23.Щелкните правой кнопкой мыши, чтобы увидеть контекстное меню, в котором выберите «Формат ячеек» CTRL + 1.

Появится окно, в котором мы выберем формат валюты. Введите количество десятичных знаков, равное 1. Нам не нужно выбирать обозначение, потому что в заголовке мы уже указали, что цена и значение в долларах США (США).

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

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

Теперь заполните таблицу данными.

Это важно! При заполнении ячеек необходимо придерживаться единого стиля письма. Если исходное имя сотрудника записано как Alex AA, то остальные ячейки должны быть заполнены аналогично.Работа с базой будет затруднена, если где-то имя будет написано иначе, например, Алексей Алексей.

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

Точно так же обрамляйте заголовки толстой внешней границей.

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

Функции Excel для работы с базой данных

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

Работа с базами данных в Excel

Пример: мы хотим знать все продукты, полученные Алексом А.А. Теоретически, вы можете визуально просмотреть все строки, где встречается это имя, а затем скопировать их в отдельную таблицу. Но что, если наша база данных состоит из нескольких сотен элементов? ФИЛЬТР приходит нам на помощь.

Выделите все заголовки в таблице и нажмите «ФИЛЬТР» на вкладке «ДАННЫЕ» (CTRL + SHIFT + L).

В каждой ячейке заголовка появляется черная стрелка на сером фоне, которую можно щелкнуть для фильтрации данных.Щелкните его в параметре КТО ПРИНЯЛИ ДЕТАЛИ и снимите галочку с имени КАРОЛИН.

При этом остались данные только с Алексом.

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

Можно произвести дополнительную фильтрацию. Определите, какой раствор принял Алекс. Щелкните стрелку в ячейке КАТЕГОРИЙ ПРОДУКТА и оставьте только растворы.

Если вам нужно вернуть полную базу данных, вы можете сделать это самым простым способом: установите все флажки в соответствующих фильтрах.

Сортировка данных

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

Например, мы хотим отсортировать товары по возрастанию цены. В первой строке будет самый дешевый продукт, а в последней — самый дорогой.Выберите столбец с ценой и на вкладке ГЛАВНАЯ и выберите «Сортировка и фильтр».

Поскольку мы решили, что более низкая цена будет наверху, выберите «Сортировать от наименьшего к наибольшему». Вы увидите другое окно, в котором мы выбираем «Расширить выделение», чтобы другие столбцы также могли настроить сортировку.

Мы видим, что данные отсортированы по возрастанию цены.

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

Сортировать по состоянию

Необходимо извлечь из базы данных продукты, которые были закуплены партиями от 25 кг. Для этого щелкните стрелку фильтра в ячейке КОЛИЧЕСТВО и выберите следующие параметры.

В появившемся окне перед условием БОЛЬШЕ ИЛИ РАВНО впишите номер 25. После этого вы получите образец с указанием продуктов, заказанных партиями больше 25 кг или равными этому. Эти товары также отсортированы по росту цены, так как сортировку по цене мы не убрали.

Промежуточные итоги

Это еще одна полезная функция, которая позволяет вычислить сумму, умножение, минимальное или среднее значение и т. Д. С использованием существующей базы данных. Это называется промежуточными итогами. = ПРОМЕЖУТОЧНЫЙ ИТОГ () У этой функции есть одно главное преимущество. Это позволяет производить расчеты с определенной функцией, даже если вы измените размер таблицы. Давайте рассмотрим это на примере.

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

Функция промежуточного итога имеет 30 аргументов. Первый статичен: код действия. По умолчанию в Excel установлен код номер 9, так пусть будет. Второй и последующие аргументы являются динамическими: это ссылки на суммируемые диапазоны. У нас есть диапазон: F4: F24. Получилось 19 670 руб.

Теперь попробуйте еще раз отсортировать номер, оставив только партию 25 кг и>.

Видно, что изменилась и сумма.

пример загрузки

В Excel также можно создать небольшие базы данных и легко с ними работать. При больших объемах данных очень удобно и эффективно.

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

Создание базы данных Excel (содержание)

Создать базу данных в Excel

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

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

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

Ввод данных для создания базы данных Excel

Ввод данных является основным аспектом, когда вы пытаетесь создать базу данных в Excel.

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

Я добавил первые несколько идентификаторов сотрудников . Скажите D01, D02, D03, а затем перетащите оставшиеся до строки 12, используя Fill Handle . Второй столбец и далее содержит общую информацию о сотрудниках, такую ​​как Имя , Фамилия , Обозначение и Заработная плата. Введите эту информацию в ячейки вручную в соответствии с вашими данными. Убедитесь, что формат столбца «Зарплата» применяется ко всем ячейкам в столбце (в противном случае эта база данных может вызвать ошибку при использовании).

Ввод правильных данных

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

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

Функция базы данных в Excel (содержание)

Введение в функцию базы данных в Excel

Функции базы данных Excel

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

Синтаксис:

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

• DAVERAGE : возвращает среднее значение выбранной базы данных, удовлетворяющей критериям пользователя.
• DCOUNT : будет подсчитывать ячейки, которые содержат некоторое число в выбранной базе данных и удовлетворяют критериям пользователя.
• DCOUNTA : подсчитывает непустые ячейки в выбранной базе данных, удовлетворяющей критериям пользователя.
• DGET : Возвращает единственное значение из выбранной базы данных, которое удовлетворяет критериям пользователя.
• DMAX : возвращает максимальное значение выбранной базы данных, которое удовлетворяет критериям пользователя.
• DMIN : возвращает минимальное значение выбранной базы данных, удовлетворяющее критериям пользователя.
• DPRODUCT : Возвращает результат умножения выбранной базы данных, удовлетворяющий критериям пользователя.
• DSTDEV : Возвращает оценочное стандартное отклонение генеральной совокупности на основе всей генеральной совокупности в выбранной базе данных, которая удовлетворяет критериям пользователя.
• DSTDEVP : возвращает стандартное отклонение всей генеральной совокупности на основе выбранной базы данных, удовлетворяющей критериям пользователя.
• DSUM : возвращает суммирование значений из выбранной базы данных, удовлетворяющих критериям пользователя.
• DVAR : возвращает оценки дисперсии совокупности на основе всей совокупности в выбранной базе данных, которая удовлетворяет критериям пользователя.
• DVARP : он вернет оценку дисперсии всей генеральной совокупности в выбранной базе данных, которая удовлетворяет критериям пользователя.

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

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

Вы можете скачать этот шаблон Excel с функцией базы данных здесь —

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

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

Хотите узнать , как создать базу данных с возможностью поиска в Excel ? не волнуйтесь, эта статья поможет вам создать базу данных в Excel .

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

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

В чем необходимость или преимущества создания базы данных в Excel?

Таблицы

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

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

Помимо этого, двумя наиболее важными преимуществами базы данных в Excel являются:

• Сниженная избыточность данных
• Это повысит емкость целостности данных
• Уменьшение количества ошибок обновления и повышение согласованности
• Большая целостность данных и независимость от прикладных программ
• Вы можете легко вести отчет и делиться своими данными
• Повышенная безопасность данных
• Снижение затрат на ввод, хранение и поиск данных

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

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

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

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

В показанном примере полями базы данных являются StdID, StdName, State, Age, Department и Class Teacher.

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

Как это,

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

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

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

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

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

Хотя разрешено оставлять некоторые ячейки строки пустыми. Как это:

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

Каждая отдельная строка базы данных называется записями. Итак, вы можете видеть, что здесь все строки являются записями.

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

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

Итак, здесь, на показанном рисунке, имена полей базы данных: StdID , StdName , State , Age , Department и Class Teacher .

Примечание:

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

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

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

При нажатии на таблицу открывается диалоговое окно Create Table . Нажмите на вариант ОК, и он создаст таблицу.

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

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

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

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

Теперь все настроено, и вы можете начать добавлять новые записи и поля в свою базу данных.

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

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

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

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

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

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

Предположим, вы импортировали следующую таблицу из базы данных Access на лист 2 книги Excel.

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

1. Откройте книгу Excel, коснитесь вкладки Sheet2 и выберите диапазон A2: H5 .
2. Теперь нажмите на поле Имя и введите Interndata . После этого нажмите [Enter].
3. Нажмите на вкладку Sheet1.
4. Выберите ячейку D6 и введите Идентификатор сотрудника .
5. Выберите ячейку D8 и присвойте Имя .
6. Выберите ячейку E8 и введите следующую функцию:

= ВПР (E6, Interndata, 3, FALSE) & ”“ & VLOOKUP (E6, Interndata, 2, FALSE)

7. В D10 введите Pay Rate .
8. Теперь коснитесь E10 и введите следующую функцию:

= ВПР (E6, Interndata, 8, FALSE)

9. Пора изменить формат ячеек E6, E8 и E10 для сопоставления типа данных с данными, представленными в таблице.
10. Теперь добавьте заголовок и выполните форматирование, как показано здесь.

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

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

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

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

Итог:

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

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

Маргрет

Маргрет Артур — предприниматель и эксперт по контент-маркетингу. Она ведет технические блоги и специализируется на MS Office, Excel и других технических темах. Ее отличительное искусство представления технической информации простым для понимания языком очень впечатляет. Когда не пишет, любит незапланированные путешествия.

Создавайте базы данных в Excel быстро и легко — бесплатные обучающие видео по Excel

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

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

Первая форма командной кнопки имеет заголовок (текст) «Добавить запись». Макрос, прикрепленный к этой кнопке, выполняет следующие действия: мы заполняем данные в ячейки с E2 по E8 и нажимаем «Добавить запись», и данные передаются в следующую пустую строку на том же листе несколькими строками ниже. Теперь мы хотим убедиться, что введенных данных еще нет в базе данных.Мы используем вложенный цикл для проверки дубликатов, и если последняя запись является дубликатом на основе имени и фамилии клиента, мы удаляем ее из базы данных. Конечно, мы можем сравнить номер мобильного телефона или идентификатор и еще больше убедиться, что не было сделано повторяющихся записей. Я бы посоветовал нам практиковать эту идею, чтобы получить больше знаний в программировании на VBA.

Вторая форма командной кнопки с текстом (заголовком) «Очистить данные» удаляет данные из ячеек листа с E2 по E8, чтобы мы могли сделать новую запись.

Третья форма командной кнопки с заголовком «Существующая запись» заполняет ячейки с E4 по E8, когда вы вводите имя и фамилию клиента в ячейки E2 и E3 соответственно. Если вы не введете имя и фамилию в ячейки E2 и E3, макрос выдаст предупреждающее сообщение и поместит курсор в соответствующую ячейку, чтобы мы могли ввести соответствующие данные.

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

Посмотрите это видео на YouTube.

Вот полный код для создания базы данных на листе Excel без пользовательской формы:

(1) Код, прикрепленный к кнопке Добавить запись

Sub addData ()

‘сначала мы объявляем несколько переменных
Dim i As Long, lastrow As Long, nextBlankRow As Long

‘lastrow на основе Find
lastrow = Cells.Find (What: = ”*”, _
After: = Range (“A1”), _
LookAt: = xlPart, _
LookIn: = xlFormulas, _
SearchOrder: = xlByRows, _
SearchDirection: = xlPrevious, _
MatchCase: = False).Ряд

nextBlankRow = последняя строка + 1

‘мы гарантируем, что пользователь вводит имя

If Range («E2») = «» Then
MsgBox «Вы не ввели имя клиента!» Диапазон
(«E2»). Выберите
Выход из подпрограммы
End If

‘мы гарантируем, что пользователь вводит фамилию

If Range («E3») = «» Then
MsgBox «Вы не ввели фамилию клиента!» Диапазон
(«E3»). Выберите
Exit Sub
End If

‘мы переносим данные из ячеек E2 в E8 на рабочий лист ниже

ячеек (nextBlankRow, 3) = диапазон («E2»)
ячеек (nextBlankRow, 4) = диапазон («E3»)
ячеек (nextBlankRow, 5) = диапазон («E4»)
ячеек (nextBlankRow, 6) = Диапазон («E5»)
ячеек (nextBlankRow, 7) = Диапазон («E6»)
ячеек (nextBlankRow, 8) = Диапазон («E7»)
ячеек (nextBlankRow, 9) = Диапазон («E8»)

«Теперь мы проверяем дубликаты с помощью вложенного цикла« do while »

‘и удалите данные, если это повторяющаяся запись

Разм. P до длины, q до длины
p = 13
q = p + 1
Ячейки «Пока» (p, 3) <> «»
Ячейки «Пока» (q, 3) <> «»
Ячейки с условием выполнения (p , 3) = Ячейки (q, 3) И Ячейки (p, 4) = Ячейки (q, 4) Затем
MsgBox «Повторяющиеся данные! Будет удалено из базы данных! »
Диапазон (Ячейки (q, 3), Ячейки (q, 9)).ClearContents
Else
q = q + 1
End If
Loop
p = p + 1
q = p + 1
Loop

Концевой переводник

(2) Код для кнопки «Очистить данные»

Sub resetData ()
Диапазон («E2: E8 ″). ClearContents
End Sub

(3) Код для кнопки «Существующая запись»

Sub checkExistingData ()

Dim i As Long, последняя строка As Long

lastrow = Cells.Find (What: = ”*”, _
After: = Range (“A1”), _
LookAt: = xlPart, _
LookIn: = xlFormulas, _
SearchOrder: = xlByRows, _
SearchDirection : = xlPrevious, _
MatchCase: = False).Ряд

If Range («E2») = «» Then
MsgBox «Вы не ввели имя клиента!» Диапазон
(«E2»). Выберите
Выход из подпрограммы
End If

If Range («E3») = «» Then
MsgBox «Вы не ввели фамилию клиента!» Диапазон
(«E3»). Выберите
Exit Sub
End If

‘циклический процесс проверки существующей записи на основе записей пользователя в ячейках E2 и E3

‘пользователь вводит имя в E2 и фамилию в E3

«Конечно, мы также можем сверить с другими данными, такими как номер мобильного телефона или идентификатор, если используется

Для i = 13 До последней строки

Если ячейки (i, 3) = диапазон («E2») и ячейки (i, 4) = диапазон («E3»), то
диапазон («E4») = ячейки (i, 5)
диапазон («E5» ) = Ячейки (i, 6)
Диапазон («E6») = Ячейки (i, 7)
Диапазон («E7») = Ячейки (i, 8)
Диапазон («E8») = Ячейки (i, 9)
Выход из подпрограммы
Конец Если
Далее i

MsgBox «Запись не существует»

Концевой переводник

Дополнительная литература

Базы данных в Excel с пользовательской формой

Загрузите образец файла для практики:

Создание базы данных управления в Excel с помощью формы пользователя — бесплатные обучающие видео по Excel

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

Посмотрите обучающее видео ниже, прежде чем изучать код VBA или макроса:

Посмотрите это видео на YouTube.

Сначала мы определяем глобальные переменные прямо в верхней части всех модулей:

Dim blnNew As Boolean
Dim totRows As Long, i As Long

Код кнопки закрытия:

Частная подпрограмма cmdClose_Click ()
Если cmdClose.Caption = «Close», то
Выгрузить меня
Конец, если

Если cmdClose.Caption = «Отменить» Затем
cmdClose.Caption = «Close»
txtItemNo.Text = «»
txtDescription.Text = «»
txtUnitPrice.Text = «»
txtQty.Text = «»
txtSupplier = «
txtSupplier». cmdNew.Enabled = True
End If

Концевой переводник

Код VBA для кнопки Удалить:

Private Sub cmdDelete_Click ()
totRows = Worksheets («Data»). Range («A1»). CurrentRegion.Rows.Count
Dim strDel As String
strDel = MsgBox («Вы уверены, что хотите удалить данные?», VbYesNo , «Удалить»)
Если strDel = vbYes Then
For i = 2 To totRows
If Trim (Worksheets («Data»).Ячейки (i, 1)) = Обрезать (ComboBox1.Text) Затем
рабочих листов («Данные»). Диапазон (i & «:» & i) .DeletetxtItemNo.Text = «»
txtDescription.Text = «»
txtUnitPrice. Text = «»
txtQty.Text = «»
txtSupplier.Text = «» Вызов comboboxFill
Exit For
End If
Next i

Если Trim (ComboBox1.Text) = «», то
cmdSave.Enabled = False
cmdDelete.Enabled = False
Else
cmdSave.Enabled = True
cmdDelete.Enabled = True
End If

Конец Если

Концевой переводник

Код VBA для кнопки New:

Private Sub cmdNew_Click ()
blnNew = True
txtItemNo.Text = «»
txtDescription.Text = «»
txtUnitPrice.Text = «»
txtQty.Text = «»
txtSupplier.Text = «»

txtItemNo.SetFocus
cmdClose.Caption = «Отмена»
cmdNew.Enabled = False
cmdDelete.Enabled = False
cmdSave.Enabled = True

Концевой переводник

Код макроса для кнопки «Сохранить»:

Частная подпрограмма cmdSave_Click ()

Если txtItemNo.Text = «», то
MsgBox «Введите номер элемента», vbCritical, «Сохранить»
txtItemNo.SetFocus
Exit Sub
End If
Call pSave
End Sub

Код VBA для процедуры pSave:

Частная подписка pSave ()

Если blnNew = True, то
totRows = Worksheets («Data»). Range («A1»). CurrentRegion.Rows.Count
With Worksheets («Data»). Range («A1»)
.Offset (totRows, 0 ) = txtItemNo.Text
.offset (totRows, 1) = txtDescription.Text
.offset (totRows, 2) = txtUnitPrice.Text
.offset (totRows, 3) = txtQty.Text
.offset (totRows, 4) = txtSupplier.Текст
Конец на

Вызов combobox Заполнить

Остальное

totRows = Рабочие листы («Данные»). Диапазон («A1»). CurrentRegion.Rows.Count

Для i = 2 To totRows

Если обрезать (рабочие листы («Данные»). Ячейки (i, 1)) = Обрезать (ComboBox1.Text), то
рабочих листов («Данные»). Ячейки (i, 1) = txtItemNo.Text
рабочих листов («Данные» ) .Cells (i, 2) .Value = txtDescription.Text
Worksheets («Данные»). Cells (i, 3) .Value = txtUnitPrice.Text
Worksheets («Data»). Cells (i, 4) .Value = txtQty.Текст
Рабочие листы («Данные»). Ячейки (i, 5) .Value = txtSupplier.Text
txtItemNo = «»
txtDescription = «»
txtUnitPrice = «»
txtQty = «»
txtSupplier = «
» для
Конец Если
Далее i

Конец Если

cmdSave.Enabled = True
cmdDelete.Enabled = False
cmdNew.Enabled = True
cmdClose.Caption = «Закрыть»

млрд Новое = Ложь

Концевой переводник

Код VBA для кнопки поиска:

Private Sub cmdSearch_Click ()
blnNew = False
txtItemNo = «»
txtDescription = «»
txtUnitPrice = «»
txtQty = «»
txtSupplier = «»
totRows = «Таблицы данных».Диапазон («A1»). CurrentRegion.Rows.Count

Для i = 2 To totRows

If Trim (Worksheets («Data»). Cells (i, 1)) = Trim (ComboBox1.Text) Then
txtItemNo.Text = Worksheets («Data»). Cells (i, 1)
txtDescription.Text = Worksheets («Данные»). Ячейки (i, 2). Значение
txtUnitPrice.Text = Рабочие листы («Данные»). Ячейки (i, 3) .Значение
txtQty.Text = Рабочие листы («Данные»). Ячейки (i, 4) .Value
txtSupplier.Text = Worksheets («Данные»). Cells (i, 5) .ValueExit For
End IfNext i

Если txtItemNo.Text = «» Затем
MsgBox «Выберите номер элемента»
Иначе
cmdSave.Enabled = True
cmdDelete.Enabled = True
End If

Концевой переводник

Код макроса для процедуры ComboBoxFill:

Private Sub comboboxFill ()
ComboBox1.Clear
totRows = Worksheets («Data»). Range («A1»). CurrentRegion.Rows.Count
For i = 2 To totRows
ComboBox1.AddItem Worksheets («Data»). Cells (i, 1). Value
Next i
End Sub

Макрокод для инициализации пользовательской формы:

Частная подписка UserForm_Initialize ()

Вызов combobox Заполнить

cmdСохранить.