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

    Создание простой базы данных на рабочем листе 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. Автоматическая запись текущей даты и времени, параграф «Окончательный вариант».

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

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

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