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

Обновлено: 04.07.2024

Отслеживать контакты, коллекции и другие данные

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

Эта статья относится к Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel для Mac, Excel для Android и Excel Online.

Введите данные

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

Чтобы следовать этому уроку, введите данные, как показано на рисунке выше.

Введите студенческие идентификаторы быстро

Остальные идентификаторы ученика правильно вводятся в ячейки с A6 по A13.

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

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

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

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

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

Столбцы поля

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

Имена полей используются для обеспечения того, чтобы данные для каждой записи вводились в одинаковой последовательности.
Данные в столбце должны быть введены в том же формате. Если вы начинаете вводить цифры в виде цифр (например, 10 или 20), продолжайте в том же духе. Не меняйте часть пути и начинайте вводить числа в виде слов (например, десять или двадцать). Быть последовательным.
Таблица не должна содержать пустых столбцов.

Создать таблицу

После ввода данных их можно преобразовать в таблицу. Чтобы преобразовать данные в таблицу:

Выделите ячейки от A3 до E13 на листе.
Выберите вкладку Главная .
Выберите Форматировать как таблицу , чтобы открыть раскрывающееся меню.
Выберите синий параметр Средний стиль таблицы 9 , чтобы открыть диалоговое окно «Формат таблицы».
Когда диалоговое окно открыто, ячейки с A3 по E13 на листе обведены пунктирной линией.
Если пунктирная линия окружает правильный диапазон ячеек, выберите ОК в диалоговом окне «Формат таблицы».
Если пунктирная линия не окружает правильный диапазон ячеек, выделите правильный диапазон на листе и затем выберите ОК в диалоговом окне «Формат таблицы».

Рядом с каждым именем поля добавляются раскрывающиеся стрелки, а строки таблицы форматируются чередующимся светло-синим цветом.

Используйте инструменты базы данных

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

Расширить базу данных

Чтобы добавить дополнительные записи в вашу базу данных:

Завершите форматирование базы данных

Выделите ячейки A1 и E1 на листе.
Выберите Домой .
Выберите Объединить и центрировать , чтобы центрировать заголовок.
Выберите Цвет заливки , чтобы открыть раскрывающийся список «Цвет заливки».
Выберите Синий, Акцент 1 из списка, чтобы изменить цвет фона в ячейках с A1 на E1 на темно-синий.
Выберите Цвет шрифта , чтобы открыть раскрывающийся список цветов шрифта.
Выберите Белый из списка, чтобы изменить цвет текста в ячейках с A1 на E1 на белый.
Выделите ячейки A2 и E2 на листе.
Выберите Цвет заливки , чтобы открыть раскрывающийся список «Цвет заливки».
Выберите в списке синий, акцент 1, светлее 80 , чтобы изменить цвет фона в ячейках с A2 на E2 на голубой.
Выделите ячейки от A4 до E14 на листе.
Выберите Центр , чтобы выровнять текст по центру в ячейках от A14 до E14.

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

Синтаксис : Dfunction (Database_arr, Field_str | num, Criteria_arr)

Где D function является одним из следующих:

Тип : база данных

Функции базы данных особенно удобны, когда Google Sheets используется для поддержки структурированных данных, таких как база данных. Каждая функция базы данных, Dfunction, вычисляет соответствующую функцию в подмножестве диапазона ячеек, рассматриваемого как таблица базы данных. Функции базы данных принимают три аргумента:

Первая строка в Criteria определяет имена полей. Каждая вторая строка в Критериях представляет фильтр, который представляет собой набор ограничений для соответствующих полей. Ограничения описываются с использованием нотации Query-by-Example и включают в себя значение для сопоставления или оператор сравнения, за которым следует значение сравнения. Примеры ограничений: «Шоколад», «42», «> = 42» и «42». Пустая ячейка означает отсутствие ограничений на соответствующее поле.

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

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

В справке по 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

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

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

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

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

Хотя в 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 я постараюсь изложить максимально кратко, интересно и просто. Правильная и продуманная предварительная подготовка «скелета» для хранения информации — это залог того, что в будущем вы легко найдете необходимые данные!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также: