Базы данных в excel – Базы данных — Хитрости работы с Excel – таблицы, функции, формулы, списки, рабочие книги, макросы

Содержание

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

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

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

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

Со всем этим вполне может справиться Microsoft Excel, если приложить немного усилий. Давайте попробуем это реализовать.

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

Информацию о товарах, продажах и клиентах будем хранить в трех таблицах (на одном листе или на разных - все равно). Принципиально важно, превратить их в "умные таблицы" с автоподстройкой размеров, чтобы не думать об этом в будущем. Это делается с помощью команды Форматировать как таблицу на вкладке Главная (Home - Format as Table). На появившейся затем вкладке Конструктор (Design) присвоим таблицам наглядные имена в поле Имя таблицы для последующего использования:

Присвоение имени "умной таблице"

Итого у нас должны получиться три "умных таблицы":

Умные таблицы для хранения данных


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

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

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

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

Форма ввода

В ячейке B3 для получения обновляемой текущей даты-времени используем функцию ТДАТА (NOW). Если время не нужно, то вместо ТДАТА можно применить функцию СЕГОДНЯ (TODAY).

В ячейке B11 найдем цену выбранного товара в третьем столбце умной таблицы

Прайс с помощью функции ВПР (VLOOKUP). Если раньше с ней не сталкивались, то сначала почитайте и посмотрите видео тут.

В ячейке B7 нам нужен выпадающий список с товарами из прайс-листа. Для этого можно использовать команду Данные - Проверка данных (Data - Validation), указать в качестве ограничения Список (List) и ввести затем в поле Источник (Source) ссылку на столбец Наименование из нашей умной таблицы Прайс:

Выпадающий список

Аналогичным образом создается выпадающий список с клиентами, но источник будет уже:

=ДВССЫЛ("Клиенты[Клиент]")

Функция ДВССЫЛ (INDIRECT) нужна, в данном случае, потому что Excel, к сожалению, не понимает прямых ссылок на умные таблицы в поле Источник. Но та же ссылка "завернутая" в функцию ДВССЫЛ работает при этом "на ура" (подробнее об этом было в статье про создание выпадающих списков с наполнением).

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

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

Форма ввода данных со строкой для загрузки

Т.е. в ячейке A20 будет ссылка =B3, в ячейке B20 ссылка на =B7 и т.д.

Теперь добавим элементарный макрос в 2 строчки, который копирует созданную строку и добавляет ее к таблице Продажи. Для этого жмем сочетание Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer). Если эту вкладку не видно, то включите ее сначала в настройках Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon). В открывшемся окне редактора Visual Basic вставляем новый пустой модуль через меню Insert - Module и вводим туда код нашего макроса:

Sub Add_Sell()
    Worksheets("Форма ввода").Range("A20:E20").Copy                         'копируем строчку с данными из формы
    n = Worksheets("Продажи").Range("A100000").End(xlUp).Row                'определяем номер последней строки в табл. Продажи
    Worksheets("Продажи").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'вставляем в следующую пустую строку
    Worksheets("Форма ввода").Range("B5,B7,B9").ClearContents               'очищаем форму
End Sub

Теперь можно добавить к нашей форме кнопку для запуска созданного макроса, используя выпадающий список Вставить на вкладке Разработчик (Developer - Insert - Button):

Добавление кнопки для запуска макроса

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

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

Продажи, а затем форма очищается для ввода новой сделки.

Шаг 4. Связываем таблицы

Перед построением отчета свяжем наши таблицы между собой, чтобы потом можно было оперативно вычислять продажи по регионам, клиентам или категориям. В старых версиях Excel для этого потребовалось бы использовать несколько функций ВПР (VLOOKUP) для подстановки цен, категорий, клиентов, городов и т.д. в таблицу Продажи. Это требует времени и сил от нас, а также "кушает" немало ресурсов Excel. Начиная с Excel 2013 все можно реализовать существенно проще, просто настроив связи между таблицами.

Для этого на вкладке Данные (Data) нажмите кнопку Отношения (Relations). В появившемся окне нажмите кнопку Создать (New) и выберите из выпадающих списков таблицы и названия столбцов, по которым они должны быть связаны:

Настройка связей между таблицами

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

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

Само-собой, аналогичным образом связываются и таблица Продажи с таблицей Клиенты по общему столбцу Клиент:

Связывание таблиц

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

Шаг 5. Строим отчеты с помощью сводной

Теперь для анализа продаж и отслеживания динамики процесса, сформируем для примера какой-нибудь отчет с помощью сводной таблицы. Установите активную ячейку в таблицу Продажи и выберите на ленте вкладку Вставка - Сводная таблица (Insert - Pivot Table). В открывшемся окне Excel спросит нас про источник данных (т.е. таблицу Продажи) и место для выгрузки отчета (лучше на новый лист):

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


Жизненно важный момент состоит в том, что нужно обязательно включить флажок Добавить эти данные в модель данных (Add data to Data Model) в нижней части окна, чтобы Excel понял, что мы хотим строить отчет не только по текущей таблице, но и задействовать все связи.

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

Отчет сводной таблицы

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

Обновить (Refresh), т.к. автоматически она этого делать не умеет.

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

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

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

Печатная форма счета

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

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


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

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

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

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

Согласно специальной терминологии, строки БД именуются «записями». В каждой записи находится информация об отдельном объекте.

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

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

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

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

  1. Вписываем заголовки полей (столбцов) БД.
  2. Заполнение полей в Microsoft Excel

  3. Заполняем наименование записей (строк) БД.
  4. Заполнение записей в Microsoft Excel

  5. Переходим к заполнению базы данными.
  6. Заполнение БД данными в Microsoft Excel

  7. После того, как БД заполнена, форматируем информацию в ней на свое усмотрение (шрифт, границы, заливка, выделение, расположение текста относительно ячейки и т.д.).

Форматирование БД в Microsoft Excel

На этом создание каркаса БД закончено.

Урок: Как сделать таблицу в Excel

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

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

  1. Переходим во вкладку «Данные».
  2. Переход во вкладку Данные в Microsoft Excel

  3. Выделяем весь диапазон таблицы. Кликаем правой кнопкой мыши. В контекстном меню жмем на кнопку «Присвоить имя…».
  4. Переход к присвоению имени БД в Microsoft Excel

  5. В графе «Имя» указываем то наименование, которым мы хотим назвать базу данных. Обязательным условием является то, что наименование должно начинаться с буквы, и в нём не должно быть пробелов. В графе «Диапазон» можно изменить адрес области таблицы, но если вы её выделили правильно, то ничего тут менять не нужно. При желании в отдельном поле можно указать примечание, но этот параметр не является обязательным. После того, как все изменения внесены, жмем на кнопку «OK».
  6. Присвоение имени БД в Microsoft Excel

  7. Кликаем по кнопке «Сохранить» в верхней части окна или набираем на клавиатуре сочетание клавиш Ctrl+S, для того, чтобы сберечь БД на жестком диске или съемном носителе, подключенном к ПК.

Сохранение БД в Microsoft Excel

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

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

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

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

    Включение сортировки БД в Microsoft Excel

    Сортировку можно проводить практически по любому параметру:

    • имя по алфавиту;
    • дата;
    • число и т.д.
  2. В следующем появившемся окне будет вопрос, использовать ли для сортировки только выделенную область или автоматически расширять её. Выбираем автоматическое расширение и жмем на кнопку «Сортировка…».
  3. Автоматическое расширение сортировки в Microsoft Excel

  4. Открывается окно настройки сортировки. В поле «Сортировать по» указываем имя поля, по которому она будет проводиться.
    • В поле «Сортировка» указывается, как именно она будет выполняться. Для БД лучше всего выбрать параметр «Значения».
    • В поле «Порядок» указываем, в каком порядке будет проводиться сортировка. Для разных типов информации в этом окне высвечиваются разные значения. Например, для текстовых данных – это будет значение «От А до Я» или «От Я до А», а для числовых – «По возрастанию» или «По убыванию».
    • Важно проследить, чтобы около значения «Мои данные содержат заголовки» стояла галочка. Если её нет, то нужно поставить.

    После ввода всех нужных параметров жмем на кнопку «OK».

    Настройка сортировки в Microsoft Excel

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

  5. Данные отсортированы в Microsoft Excel

  6. Одним из наиболее удобных инструментов при работе в базе данных Excel является автофильтр. Выделяем весь диапазон БД и в блоке настроек «Сортировка и фильтр» кликаем по кнопке «Фильтр».
  7. Включение фильтра в Microsoft Excel

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

    Применение фильтрации в Microsoft Excel

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

  9. Для того, чтобы вернуть все данные на экран, кликаем на пиктограмму того столбца, по которому проводилась фильтрация, и в открывшемся окне напротив всех пунктов устанавливаем галочки. Затем жмем на кнопку «OK».
  10. Отмена фильтрации в Microsoft Excel

  11. Для того, чтобы полностью убрать фильтрацию, жмем на кнопку «Фильтр» на ленте.

Отключение фильтра в Microsoft Excel

Урок: Сортировка и фильтрация данных в Excel

Поиск

При наличии большой БД поиск по ней удобно производить с помощь специального инструмента.

  1. Для этого переходим во вкладку «Главная» и на ленте в блоке инструментов «Редактирование» жмем на кнопку «Найти и выделить».
  2. Переход к поиску в Microsoft Excel

  3. Открывается окно, в котором нужно указать искомое значение. После этого жмем на кнопку «Найти далее» или «Найти все».
  4. Окно поиска в Microsoft Excel

  5. В первом случае первая ячейка, в которой имеется указанное значение, становится активной.

    Значение найдено в Microsoft Excel

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

Список найденных значений в Microsoft Excel

Урок: Как сделать поиск в Экселе

Закрепление областей

Удобно при создании БД закрепить ячейки с наименованием записей и полей. При работе с большой базой – это просто необходимое условие. Иначе постоянно придется тратить время на пролистывание листа, чтобы посмотреть, какой строке или столбцу соответствует определенное значение.

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

  3. Находясь во вкладке «Вид» кликаем по кнопке «Закрепить области», которая расположена в группе инструментов «Окно». В выпадающем списке выбираем значение «Закрепить области».

Закрепление областей в Microsoft Excel

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

Урок: Как закрепить область в Экселе

Выпадающий список

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

  1. Создаем дополнительный список. Удобнее всего его будет разместить на другом листе. В нём указываем перечень значений, которые будут появляться в выпадающем списке.
  2. Дополнительный список в Microsoft Excel

  3. Выделяем этот список и кликаем по нему правой кнопкой мыши. В появившемся меню выбираем пункт «Присвоить имя…».
  4. Переход к присвоению имени в Microsoft Excel

  5. Открывается уже знакомое нам окно. В соответствующем поле присваиваем имя нашему диапазону, согласно условиям, о которых уже шла речь выше.
  6. Присвоении имени диапазону в Microsoft Excel

  7. Возвращаемся на лист с БД. Выделяем диапазон, к которому будет применяться выпадающий список. Переходим во вкладку «Данные». Жмем на кнопку «Проверка данных», которая расположена на ленте в блоке инструментов «Работа с данными».
  8. Переход к проверке данных в Microsoft Excel

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

Окно проверки видимых значений в Microsoft Excel

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

Выбор значения в Microsoft Excel

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

Сообщение об ошибке в Microsoft Excel

Урок: Как сделать выпадающий список в Excel

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

Сообщение об ошибке в Microsoft ExcelМы рады, что смогли помочь Вам в решении проблемы.
Сообщение об ошибке в Microsoft ExcelОпишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

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

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

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

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

Со всем этим вполне может справиться Microsoft Excel, если приложить немного усилий. Давайте попробуем это реализовать.

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

Информацию о товарах, продажах и клиентах будем хранить в трех таблицах (на одном листе или на разных - все равно). Принципиально важно, превратить их в "умные таблицы" с автоподстройкой размеров, чтобы не думать об этом в будущем. Это делается с помощью команды Форматировать как таблицу на вкладке Главная (Home - Format as Table). На появившейся затем вкладке Конструктор (Design) присвоим таблицам наглядные имена в поле Имя таблицы для последующего использования:

Присвоение имени "умной таблице"

Итого у нас должны получиться три "умных таблицы":

Умные таблицы для хранения данных

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

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

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

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

Форма ввода

В ячейке B3 для получения обновляемой текущей даты-времени используем функцию ТДАТА (NOW). Если время не нужно, то вместо ТДАТА можно применить функцию СЕГОДНЯ (TODAY).

В ячейке B11 найдем цену выбранного товара в третьем столбце умной таблицы Прайс с помощью функции ВПР (VLOOKUP). Если раньше с ней не сталкивались, то сначала почитайте и посмотрите видео тут.

В ячейке B7 нам нужен выпадающий список с товарами из прайс-листа. Для этого можно использовать команду Данные - Проверка данных (Data - Validation), указать в качестве ограничения Список (List) и ввести затем в поле Источник (Source) ссылку на столбец Наименование из нашей умной таблицы Прайс:

Выпадающий список

Аналогичным образом создается выпадающий список с клиентами, но источник будет уже:

=ДВССЫЛ("Клиенты[Клиент]")

Функция ДВССЫЛ (INDIRECT) нужна, в данном случае, потому что Excel, к сожалению, не понимает прямых ссылок на умные таблицы в поле Источник. Но та же ссылка "завернутая" в функцию ДВССЫЛ работает при этом "на ура" (подробнее об этом было в статье про создание выпадающих списков с наполнением).

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

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

Форма ввода данных со строкой для загрузки

Т.е. в ячейке A20 будет ссылка =B3, в ячейке B20 ссылка на =B7 и т.д.

Теперь добавим элементарный макрос в 2 строчки, который копирует созданную строку и добавляет ее к таблице Продажи. Для этого жмем сочетание Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer). Если эту вкладку не видно, то включите ее сначала в настройках Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon). В открывшемся окне редактора Visual Basic вставляем новый пустой модуль через меню Insert - Module и вводим туда код нашего макроса:

Sub Add_Sell()
    Worksheets("Форма ввода").Range("A20:E20").Copy                         'копируем строчку с данными из формы
    n = Worksheets("Продажи").Range("A100000").End(xlUp).Row                'определяем номер последней строки в табл. Продажи
    Worksheets("Продажи").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'вставляем в следующую пустую строку
    Worksheets("Форма ввода").Range("B5,B7,B9").ClearContents               'очищаем форму
End Sub

Теперь можно добавить к нашей форме кнопку для запуска созданного макроса, используя выпадающий список Вставить на вкладке Разработчик (Developer - Insert - Button):

Добавление кнопки для запуска макроса

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

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

Шаг 4. Связываем таблицы

Перед построением отчета свяжем наши таблицы между собой, чтобы потом можно было оперативно вычислять продажи по регионам, клиентам или категориям. В старых версиях Excel для этого потребовалось бы использовать несколько функций ВПР (VLOOKUP) для подстановки цен, категорий, клиентов, городов и т.д. в таблицу Продажи. Это требует времени и сил от нас, а также "кушает" немало ресурсов Excel. Начиная с Excel 2013 все можно реализовать существенно проще, просто настроив связи между таблицами.

Для этого на вкладке Данные (Data) нажмите кнопку Отношения (Relations). В появившемся окне нажмите кнопку Создать (New) и выберите из выпадающих списков таблицы и названия столбцов, по которым они должны быть связаны:

Настройка связей между таблицами

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

Само-собой, аналогичным образом связываются и таблица Продажи с таблицей Клиенты по общему столбцу Клиент:

Связывание таблиц

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

Шаг 5. Строим отчеты с помощью сводной

Теперь для анализа продаж и отслеживания динамики процесса, сформируем для примера какой-нибудь отчет с помощью сводной таблицы. Установите активную ячейку в таблицу Продажи и выберите на ленте вкладку Вставка - Сводная таблица (Insert - Pivot Table). В открывшемся окне Excel спросит нас про источник данных (т.е. таблицу Продажи) и место для выгрузки отчета (лучше на новый лист):

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

Жизненно важный момент состоит в том, что нужно обязательно включить флажок Добавить эти данные в модель данных (Add data to Data Model) в нижней части окна, чтобы Excel понял, что мы хотим строить отчет не только по текущей таблице, но и задействовать все связи.

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

Отчет сводной таблицы

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

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

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

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

Печатная форма счета

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

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


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

Создание простой базы данных на рабочем листе Excel. Использование раскрывающихся списков для ввода данных. Файл с примером для скачивания.

  1. Создание базы данных в Excel
  2. Создание раскрывающихся списков для базы данных
  3. Внешний вид базы данных в Excel
  4. Скачать пример базы данных в Excel

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

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

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

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

Наименование полей базы данных

Как видно, для примера, я уже добавил в базу данных начальное сальдо. Еще, обязательно, нужно закрепить на экране первую строку (в Excel 2010 это: Вид - Закрепить области - Закрепить верхнюю строку). Затем выбираем первые три строки нашей таблицы и добавляем границы ячеек. Лист назовем «Касса».

Создание раскрывающихся списков для базы данных

Списки для выбора значений мы создаем для полей «Торговая точка», «Вид прихода», «Вид расхода», «Получатель/плательщик». В моей базе данных «Получатель/плательщик» называется короче - «Субъект», и между полями «Дата» и «Торговая точка» есть поле «Фирма», где до 2 квартала 2012 года выбирал ИП или ООО.

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

Списки значений для выбора

Чтобы эти диапазоны значений можно было добавить в формулу для выбора в раскрывающемся списке, им необходимо присвоить имена. Причем, создавать мы будем динамические именованные диапазоны, чтобы не приходилось каждый раз изменять диапазон в формуле выбора данных при добавлении нового значения. Для этого переходим на лист «Списки» и открываем окно создания имени (в Excel 2010 это: Формулы - Присвоить имя; свои файлы создавал в Excel 2000, но сейчас его структуру меню не помню). Записываем:
Имя: Торговая_точка_выбор,
Диапазон: =СМЕЩ(Списки!$A$2;0;0;СЧЁТЗ(Списки!$A:$A)-1;1)

Создание именованного диапазона

Нажимаем «ОК» и первый динамический диапазон создан. То же самое проделываем и для других диапазонов:

Имя: Вид_прихода_выбор
Диапазон: =СМЕЩ(Списки!$B$2;0;0;СЧЁТЗ(Списки!$B:$B)-1;1)

Имя: Вид_расхода_выбор
Диапазон: =СМЕЩ(Списки!$C$2;0;0;СЧЁТЗ(Списки!$C:$C)-1;1)

Имя: Получатель_плательщик_выбор
Диапазон: =СМЕЩ(Списки!$D$2;0;0;СЧЁТЗ(Списки!$D:$D)-1;1)

Когда всем диапазонам со списками значений для выбора присвоены имена, переходим на лист «Касса» и создаем в ячейках соответствующих колонок раскрывающиеся списки. Для этого выбираем ячейку «B3» и открываем окно «Проверка вводимых значений» (в Excel 2010 это: Данные - Проверка данных - Проверка данных). На вкладке «Параметры» выбираем Тип данных: Список, а в Источник записываем: =Торговая_точка_выбор.

Проверка вводимых значений

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

для «D3»:  =Вид_прихода_выбор,
для «F3»:  =Вид_расхода_выбор,
для «G3»:  =Получатель_плательщик_выбор.

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

Внешний вид базы данных в Excel

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

База данных в Excel

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

Ну и раз уж это база данных для ведения кассы, в любую ячейку закрепленной строки, правее наименований полей, вставьте формулу: =СУММ(C:C)-СУММ(E:E), чтобы всегда видеть текущий остаток денег в кассе.

Скачать пример базы данных в Excel

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

Скачать архив с файлом базы данных

База данных в 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

92-0-база данных 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 создаст связи и отобразит результирующий отчет на экране. Дайте имя вашему отчету, и он будет готов.

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

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

особенности создания, примеры и рекомендации

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

база данных excel

Что такое база данных?

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

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

Создание хранилища данных в Excel

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

создать базу данных в excel

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

Горизонтальные строки в разметке листа «Эксель» принято называть записями, а вертикальные колонки – полями. Можно приступать к работе. Открываем программу и создаем новую книгу. Затем в самую первую строку нужно записать названия полей.

Особенности формата ячеек

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

создание базы данных в excel

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

Что такое автоформа в «Эксель» и зачем она требуется?

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

Фиксация «шапки» базы данных

Кроме этого, не нужно забывать о закреплении первой строки. В Excel 2007 это можно совершить следующим образом: перейти на вкладку «Вид», затем выбрать «Закрепить области» и в контекстном меню кликнуть на «Закрепить верхнюю строку». Это требуется, чтобы зафиксировать «шапку» работы. Так как база данных Excel может быть достаточно большой по объему, то при пролистывании вверх-вниз будет теряться главная информация – названия полей, что неудобно для пользователя.

база данных ms excel

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

Продолжение работы над проектом

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

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

Как создать раскрывающиеся списки?

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

работа с базой данных в excel

Для того чтобы база данных MS Excel предоставляла возможность выбора данных из раскрывающегося списка, необходимо создать специальную формулу. Для этого нужно присвоить всем сведениям о родителях диапазон значений, имена. Переходим на тот лист, где записаны все данные под названием «Родители» и открываем специальное окно для создания имени. К примеру, в Excel 2007 это можно сделать, кликнув на «Формулы» и нажав «Присвоить имя». В поле имени записываем: ФИО_родителя_выбор. Но что написать в поле диапазона значений? Здесь все сложнее.

Диапазон значений в Excel

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

Чтобы получился динамический диапазон, необходимо использовать формулу СМЕЩ. Она, независимо от того, как были заданы аргументы, возвращает ссылку на исходные данные. В выпадающем списке, который получится в итоге, не должно встречаться пустых значений. С этим как раз превосходно справляется динамический диапазон. Он задается двумя координатами ячеек: верхней левой и правой нижней, словно по диагонали. Поэтому нужно обратить внимание на место, откуда начинается ваша таблица, а точнее, на координаты верхней левой ячейки. Пусть табличка начинается в месте А5. Это значение и будет верхней левой ячейкой диапазона. Теперь, когда первый искомый элемент найден, перейдем ко второму.

Нижнюю правую ячейку определяют такие аргументы, как ширина и высота. Значение последней пусть будет равно 1, а первую вычислит формула СЧЁТ3(Родители!$B$5:$I$5).

база данных в excel пример

Итак, в поле диапазона записываем =СМЕЩ(Родители!$A$5;0;0;СЧЁТЗ(Родители!$A:$A)-1;1). Нажимаем клавишу ОК. Во всех последующих диапазонах букву A меняем на B, C и т. д.

Работа с базой данных в Excel почти завершена. Возвращаемся на первый лист и создаем раскрывающиеся списки на соответствующих ячейках. Для этого кликаем на пустой ячейке (например B3), расположенной под полем «ФИО родителей». Туда будет вводиться информация. В окне «Проверка вводимых значений» во вкладке под названием «Параметры» записываем в «Источник» =ФИО_родителя_выбор. В меню «Тип данных» указываем «Список».

Аналогично поступаем с остальными полями, меняя название источника на соответствующее данным ячейкам. Работа над выпадающими списками почти завершена. Затем выделяем третью ячейку и «протягиваем» ее через всю таблицу. База данных в Excel почти готова!

Внешний вид базы данных

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

Как перенести базу данных из Excel в Access

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

Но как же сделать так, чтобы получилась база данных Access? Excel учитывает такое желание пользователя. Это можно сделать несколькими способами:

• Можно выделить всю информацию, содержащуюся на листе Excel, скопировать ее и перенести в другую программу. Для этого выделите данные, предназначенные для копирования, и щелкните правой кнопкой мышки. В контекстном меню нажимайте «Копировать». Затем переключитесь на Access, выберите вкладку «Таблица», группу «Представления» и смело кликайте на кнопку «Представление». Выбирайте пункт «Режим таблицы» и вставляйте информацию, щелкнув правой кнопкой мышки и выбрав «Вставить».

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

• Можно импортировать лист формата .xls (.xlsx). Откройте Access, предварительно закрыв Excel. В меню выберите команду «Импорт», и кликните на нужную версию программы, из которой будете импортировать файл. Затем нажимайте «ОК».

• Можно связать файл Excel с таблицей в программе Access. Для этого в «Экселе» нужно выделить диапазон ячеек, содержащих необходимую информацию, и, кликнув на них правой кнопкой мыши, задать имя диапазона. Сохраните данные и закройте Excel. Откройте «Аксесс», на вкладке под названием «Внешние данные» выберите пункт «Электронная таблица Эксель» и введите ее название. Затем щелкните по пункту, который предлагает создать таблицу для связи с источником данных, и укажите ее наименование.

Вот и все. Работа готова!

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *