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

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

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

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

БД – инструмент специалиста

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

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

С тем, что такое БД все понятно, а вот как ее создать в Excel, давайте разбираться дальше.

Где хранятся данные в Excel?

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

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

Иными словами, БД – это совокупная таблица, содержащая множество важной информации.

Формат ячеек Excel

В Excel можно прописывать любую информацию – числа, даты рождения, текст произвольный, проценты, символы. Чтобы в таблицу корректно занести данные, нужно определиться с форматом ячейки и выбрать соответствующий занесенным данным. Меню «Формат ячеек» позволят изменять формат оных вручную. Это можно делать и автоматически, перетаскивая курсор в виде плюса, выделяя нужные ячейки для установки в них нужного формата отражения информации.

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

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

Автоформа Excel, для чего она нужна?

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

Создание заголовков БД в Excel

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

После того, как первая строка БД закреплена, определяются границы ячеек.

Работа над проектом

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

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

Раскрывающиеся списки: как их создать в Excel?

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

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

Далее нужно перейти на тот лист, где были записаны сведения о людях под названием «Родители», открыть окно для создания имени. Дляэтого кликаем по вкладке «Формулы и нажимаем «Присвоить имя». В поле имени записывается «ФИО_родители_выбор». А вот в поле диапазона значений что указать?

Excel – диапазон значений

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

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

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

Значение последней установим 1 , а значение последней определит формула СЧЕТ3 (Родители!$B$5:$I$5).

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

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

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

Красочное оформление базы данных

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

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

Конкурент Excel — новый программный продукт Access

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

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

А еще современные пользователи научились переносить базы данных из Эксель в Access. Получается весьма простой ход конем.

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

Также можно из одной программы в другую импортировать файлы. Для этого нужно открыть Access – Импорт – выбрать Эксель из списка программ – Ок.

А можно просто связать файлы одной программы с таблицами другой. Открываем для этого Эксель, выделяем нужные ячейки с информацией, кликаем ПКМ, задаем диапазон, сохраняем данные, выходим из Эксель. Открываем Access, выбираем «Внешние данные» -«Электронные таблицы Эксель» — вводим название таблицы – создать таблицу для связи с указанием ее наименования.

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

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

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

Создание простой базы данных на рабочем листе 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

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

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

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

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

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

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

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

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

Как создать базу данных — learn-office

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

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

Поиск информации в Excel, и в т.ч. в базе данных

может осуществляться двумя способами:

1. Использование функции поиска

Вызвать функцию поиска и замены в Excel можно тремя способами:

— одновременным нажатием SHIFT и F5;

— «Правка» в главном меню -> «Найти»

— бинокликом в меню правки вверху страницы.

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

2. Использование фильтра

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

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

Как же установить автофильтр?

Необходимо активизировать шапку, нажатием левой кнопки мыши на серое поле, зайти в «Данные» главного меню и выбрать закладки «Фильтр» -> «Автофильтр»:

после чего в правом нижнем углу каждой ячейки шапки появится кнопочка.

С помощью такой кнопочки можно:

1. Отсортировать подобные значения

Например в ниже представленном примере мы отсортируем всех клиентов, с которыми были подписаны договоры в 2010 году. Нажмем на кнопочку в нижнем правом углу поля «Год заключения договора» и выберем 2010:

2. Задать условие и отсортировать значения, соответствующие условиям

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

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

Получаем:

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

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

Автофильтр — многофункциональный помощник при правильном употреблении. С его помощью можно совершать много действий. Например:

Посчитаем количество клиентов по каналам продаж

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

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

Маленький секрет: если сначала сделать сортировку по одному из каналов продаж, а потом добавить автосумму, то при смене канала, сумма будет работать. При этом не забудьте захватить область автосуммы с небольшим запасом (на одну ячейку больше сверху и снизу), иначе некоторые значения из других каналов продажи могут не попасть в расчет.

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

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

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

Шаблон базы данных для  Excel

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

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

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

Образцы базы данных

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

• База данных для счетов-фактур в Excel
• База данных для склада в Excel
• База данных по продажам в Excel
• База данных для инвентаризации в Excel
• База данных клиентов в Excel

База данных в Excel для бесплатного скачивания

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

Шаблон базы данных для Excel

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

Как сделать SQL запрос в Excel

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

Читайте также: Как создать базу данных в Экселе

Создание SQL запроса в Excel

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

Способ 1: использование надстройки

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

Скачать надстройку XLTools

1. После того, как вы скачали файл надстройки xltools.exe, следует приступить к его установке. Для запуска инсталлятора нужно произвести двойной щелчок левой кнопки мыши по установочному файлу. После этого запустится окно, в котором нужно будет подтвердить согласие с лицензионным соглашением на использование продукции компании Microsoft — NET Framework 4. Для этого всего лишь нужно кликнуть по кнопке «Принимаю» внизу окошка.
2. После этого установщик производит загрузку обязательных файлов и начинает процесс их установки.
3. Далее откроется окно, в котором вы должны подтвердить свое согласие на установку этой надстройки. Для этого нужно щелкнуть по кнопке «Установить».
4. Затем начинается процедура установки непосредственно самой надстройки.
5. После её завершения откроется окно, в котором будет сообщаться, что инсталляция успешно выполнена. В указанном окне достаточно нажать на кнопку «Закрыть».
6. Надстройка установлена и теперь можно запускать файл Excel, в котором нужно организовать SQL запрос. Вместе с листом Эксель открывается окно для ввода кода лицензии XLTools. Если у вас имеется код, то нужно ввести его в соответствующее поле и нажать на кнопку «OK». Если вы желаете использовать бесплатную версию на 14 дней, то следует просто нажать на кнопку «Пробная лицензия».
7. При выборе пробной лицензии открывается ещё одно небольшое окошко, где нужно указать своё имя и фамилию (можно псевдоним) и электронную почту. После этого жмите на кнопку «Начать пробный период».
8. Далее мы возвращаемся к окну лицензии. Как видим, введенные вами значения уже отображаются. Теперь нужно просто нажать на кнопку «OK».
9. После того, как вы проделаете вышеуказанные манипуляции, в вашем экземпляре Эксель появится новая вкладка – «XLTools». Но не спешим переходить в неё. Прежде, чем создавать запрос, нужно преобразовать табличный массив, с которым мы будем работать, в так называемую, «умную» таблицу и присвоить ей имя.
   Для этого выделяем указанный массив или любой его элемент. Находясь во вкладке «Главная» щелкаем по значку «Форматировать как таблицу». Он размещен на ленте в блоке инструментов «Стили». После этого открывается список выбора различных стилей. Выбираем тот стиль, который вы считаете нужным. На функциональность таблицы указанный выбор никак не повлияет, так что основывайте свой выбор исключительно на основе предпочтений визуального отображения.
10. Вслед за этим запускается небольшое окошко. В нем указываются координаты таблицы. Как правило, программа сама «подхватывает» полный адрес массива, даже если вы выделили только одну ячейку в нем. Но на всякий случай не мешает проверить ту информацию, которая находится в поле «Укажите расположение данных таблицы». Также нужно обратить внимание, чтобы около пункта «Таблица с заголовками», стояла галочка, если заголовки в вашем массиве действительно присутствуют. Затем жмите на кнопку «OK».
11. После этого весь указанный диапазон будет отформатирован, как таблица, что повлияет как на его свойства (например, растягивание), так и на визуальное отображение. Указанной таблице будет присвоено имя. Чтобы его узнать и по желанию изменить, клацаем по любому элементу массива. На ленте появляется дополнительная группа вкладок – «Работа с таблицами». Перемещаемся во вкладку «Конструктор», размещенную в ней. На ленте в блоке инструментов «Свойства» в поле «Имя таблицы» будет указано наименование массива, которое ему присвоила программа автоматически.
12. При желании это наименование пользователь может изменить на более информативное, просто вписав в поле с клавиатуры желаемый вариант и нажав на клавишу Enter.
13. После этого таблица готова и можно переходить непосредственно к организации запроса. Перемещаемся во вкладку «XLTools».
14. После перехода на ленте в блоке инструментов «SQL запросы» щелкаем по значку «Выполнить SQL».
15. Запускается окно выполнения SQL запроса. В левой его области следует указать лист документа и таблицу на древе данных, к которой будет формироваться запрос.

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

    Далее пишется текст команды, которую вы хотите применить к выбранным объектам. Команды составляются при помощи специальных операторов. Вот основные операторы SQL:

    • ORDER BY – сортировка значений;
    • JOIN – объединение таблиц;
    • GROUP BY – группировка значений;
    • SUM – суммирование значений;
    • DISTINCT – удаление дубликатов.

    Кроме того, в построении запроса можно использовать операторы MAX, MIN, AVG, COUNT, LEFT и др.

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

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

Урок: «Умные» таблицы в Экселе

Способ 2: использование встроенных инструментов Excel

Существует также способ создать SQL запрос к выбранному источнику данных с помощью встроенных инструментов Эксель.

1. Запускаем программу Excel. После этого перемещаемся во вкладку «Данные».
2. В блоке инструментов «Получение внешних данных», который расположен на ленте, жмем на значок «Из других источников». Открывается список дальнейших вариантов действий. Выбираем в нем пункт «Из мастера подключения данных».
3. Запускается Мастер подключения данных. В перечне типов источников данных выбираем «ODBC DSN». После этого щелкаем по кнопке «Далее».
4. Открывается окно Мастера подключения данных, в котором нужно выбрать тип источника. Выбираем наименование «MS Access Database». Затем щелкаем по кнопке «Далее».
5. Открывается небольшое окошко навигации, в котором следует перейти в директорию расположения базы данных в формате mdb или accdb и выбрать нужный файл БД. Навигация между логическими дисками при этом производится в специальном поле «Диски». Между каталогами производится переход в центральной области окна под названием «Каталоги». В левой области окна отображаются файлы, расположенные в текущем каталоге, если они имеют расширение mdb или accdb. Именно в этой области нужно выбрать наименование файла, после чего кликнуть на кнопку «OK».
6. Вслед за этим запускается окно выбора таблицы в указанной базе данных. В центральной области следует выбрать наименование нужной таблицы (если их несколько), а потом нажать на кнопку «Далее».
7. После этого открывается окно сохранения файла подключения данных. Тут указаны основные сведения о подключении, которое мы настроили. В данном окне достаточно нажать на кнопку «Готово».
8. На листе Excel запускается окошко импорта данных. В нем можно указать, в каком именно виде вы хотите, чтобы данные были представлены:
   • Таблица;
   • Отчёт сводной таблицы;
   • Сводная диаграмма.

   Выбираем нужный вариант. Чуть ниже требуется указать, куда именно следует поместить данные: на новый лист или на текущем листе. В последнем случае предоставляется также возможность выбора координат размещения. По умолчанию данные размещаются на текущем листе. Левый верхний угол импортируемого объекта размещается в ячейке A1.

   После того, как все настройки импорта указаны, жмем на кнопку «OK».

9. Как видим, таблица из базы данных перемещена на лист. Затем перемещаемся во вкладку «Данные» и щелкаем по кнопке «Подключения», которая размещена на ленте в блоке инструментов с одноименным названием.
10. После этого запускается окно подключения к книге. В нем мы видим наименование ранее подключенной нами базы данных. Если подключенных БД несколько, то выбираем нужную и выделяем её. После этого щелкаем по кнопке «Свойства…» в правой части окна.
11. Запускается окно свойств подключения. Перемещаемся в нем во вкладку «Определение». В поле «Текст команды», находящееся внизу текущего окна, записываем SQL команду в соответствии с синтаксисом данного языка, о котором мы вкратце говорили при рассмотрении Способа 1. Затем жмем на кнопку «OK».
12. После этого производится автоматический возврат к окну подключения к книге. Нам остается только кликнуть по кнопке «Обновить» в нем. Происходит обращение к базе данных с запросом, после чего БД возвращает результаты его обработки назад на лист Excel, в ранее перенесенную нами таблицу.

Способ 3: подключение к серверу SQL Server

Кроме того, посредством инструментов Excel существует возможность соединения с сервером SQL Server и посыла к нему запросов. Построение запроса не отличается от предыдущего варианта, но прежде всего, нужно установить само подключение. Посмотрим, как это сделать.

1. Запускаем программу Excel и переходим во вкладку «Данные». После этого щелкаем по кнопке «Из других источников», которая размещается на ленте в блоке инструментов «Получение внешних данных». На этот раз из раскрывшегося списка выбираем вариант «С сервера SQL Server».
2. Происходит открытие окна подключения к серверу баз данных. В поле «Имя сервера» указываем наименование того сервера, к которому выполняем подключение. В группе параметров «Учетные сведения» нужно определиться, как именно будет происходить подключение: с использованием проверки подлинности Windows или путем введения имени пользователя и пароля. Выставляем переключатель согласно принятому решению. Если вы выбрали второй вариант, то кроме того в соответствующие поля придется ввести имя пользователя и пароль. После того, как все настройки проведены, жмем на кнопку «Далее». После выполнения этого действия происходит подключение к указанному серверу. Дальнейшие действия по организации запроса к базе данных аналогичны тем, которые мы описывали в предыдущем способе.

Как видим, в Экселе SQL запрос можно организовать, как встроенными инструментами программы, так и при помощи сторонних надстроек. Каждый пользователь может выбрать тот вариант, который удобнее для него и является более подходящим для решения конкретно поставленной задачи. Хотя, возможности надстройки XLTools, в целом, все-таки несколько более продвинутые, чем у встроенных инструментов Excel. Главный же недостаток XLTools заключается в том, что срок бесплатного пользования надстройкой ограничен всего двумя календарными неделями.

Мы рады, что смогли помочь Вам в решении проблемы.

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

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

ДА НЕТ

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

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

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

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

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

1На вкладке «Данные» выберите «Из других источников» → «Из Microsoft Query».

Excel отображает диалоговое окно «Выбор источника данных».

2 Используя вкладку «Базы данных», определите тип базы данных, к которой вы хотите выполнить запрос.

Например, чтобы запросить базу данных Microsoft Access, щелкните запись База данных MS Access и затем ОК.

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

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

3 Выберите базу данных.

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

4Выберите базу данных, которую вы хотите запросить, из списка каталогов и нажмите OK.

Excel отображает диалоговое окно «Мастер запросов — выбор столбцов».

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

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

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

Чтобы удалить поле, выберите поле в списке «Столбцы в вашем запросе», а затем нажмите кнопку со стрелкой влево, которая указывает на поле списка «Доступные таблицы и столбцы».

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

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

Excel отображает диалоговое окно «Мастер запросов — фильтрация данных».

Вы можете фильтровать данные, возвращаемые как часть вашего запроса, используя текстовые поля «Только включать строки, где». Например, чтобы включить только те строки, в которых в поле аббревиатуры состояния указано CA, щелкните поле state_abbrev в списке «Столбец для фильтрации».Затем выберите операцию фильтрации «Равно» в первом раскрывающемся списке и введите или выберите значение CA во втором раскрывающемся списке.

Мастер запросов — диалоговое окно «Фильтрация данных» выполняет те же виды фильтрации, которые вы можете выполнять с помощью команд «Автофильтр» и «Расширенный фильтр».

7 Отфильтруйте данные на основе нескольких фильтров, выбрав переключатели И или Или.

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

Или: Использование фильтров или означает, что если строка соответствует любому фильтруемому условию, она включается.

8Нажмите Далее.

Excel отображает диалоговое окно «Мастер запросов — порядок сортировки».

9Выберите порядок сортировки данных результатов запроса в диалоговом окне «Мастер запросов — порядок сортировки».

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

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

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

10Щелкните Далее.

Excel отображает диалоговое окно «Мастер запросов — Готово».

11 В диалоговом окне «Мастер запросов — Готово» укажите, куда Excel должен поместить результаты запроса.

В этом диалоговом окне есть переключатели, с помощью которых вы выбираете, где вы хотите разместить данные результатов запроса: в Excel, в окне Microsoft Query, которое вы затем можете просмотреть, или в кубе OLAP.Как правило, вы просто хотите вернуть данные в Microsoft Excel и поместить их в книгу. Чтобы сделать этот выбор, установите переключатель «Вернуть данные в Microsoft Office Excel».

12Нажмите кнопку Готово.

После нажатия кнопки «Готово» для завершения работы мастера запросов Excel отображает диалоговое окно «Импорт данных».

13 В диалоговом окне «Импорт данных» выберите расположение рабочего листа для данных результатов запроса.

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

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

Или же, чтобы поместить данные на новый рабочий лист, выберите переключатель «Новый рабочий лист».

14Щелкните OK.

Excel помещает данные в выбранное вами место.

Об авторе книги

Стивен Л.Нельсон — автор и бухгалтер, который предоставляет услуги бухгалтерского учета, бизнес-консультирования, налогового планирования и подготовки налогов для малых предприятий. Он является автором более 100 книг, в том числе QuickBooks для чайников и Quicken For Dummies .

.

Создать связь между таблицами в Excel

Вы когда-нибудь использовали ВПР для переноса столбца из одной таблицы в другую? Теперь, когда Excel имеет встроенную модель данных, функция VLOOKUP устарела. Вы можете создать связь между двумя таблицами данных на основе совпадающих данных в каждой таблице. Затем вы можете создавать листы Power View и строить сводные таблицы и другие отчеты с полями из каждой таблицы, даже если таблицы из разных источников. Например, если у вас есть данные о продажах клиентов, вы можете импортировать и связать данные анализа времени для анализа моделей продаж по годам и месяцам.

Все таблицы в книге перечислены в списках полей сводной таблицы и Power View.

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

1. Убедитесь, что книга содержит как минимум две таблицы и что в каждой таблице есть столбец, который можно сопоставить со столбцом в другой таблице.

2. Выполните одно из следующих действий: Отформатируйте данные как таблицу или Импортируйте внешние данные как таблицу на новый лист.

3. Дайте каждой таблице осмысленное имя: В Table Tools щелкните Design > Table Name > введите имя.

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

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

5. Щелкните Данные > Отношения .

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

1. В поле Управление взаимосвязями щелкните Новый .

2. В поле Create Relationship щелкните стрелку для Таблица и выберите таблицу из списка. В отношениях «один ко многим» эта таблица должна быть на стороне многих. Используя наш пример анализа данных о клиентах и ​​времени, вы должны сначала выбрать таблицу продаж по клиентам, потому что многие продажи, вероятно, произойдут в любой день.

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

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

5. Для Связанный столбец (основной) выберите столбец, который имеет уникальные значения, соответствующие значениям в столбце, выбранном для Столбец .

6. Щелкните ОК .

Подробнее о связях между таблицами в Excel

Заметки об отношениях

Пример: связь данных времени с данными о рейсах авиакомпаний

«Могут потребоваться связи между таблицами»

Шаг 1. Определите, какие таблицы указать в отношении

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

Примечания к отношениям

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

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

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

.