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

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

Основные термины

• Список (база данных) – это таблица на рабочем листе, состоящая из строк с однотипными данными и организованная по принципу базы данных.

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

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

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

• Диапазон – это диапазон ячеек, содержащий записи базы данных.

Основные правила создания списка

• На отдельном рабочем листе создавайте только одну базу данных.

• Не вставляйте в базу данных пустые строки. Если пустая строка вставлена между именами полей и данными, то Excel не определит имена полей. Пустые строки между записями воспринимаются Excel как конец БД.

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

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

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

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

• Теоретически размер базы данных в Excel ограничен размерами листа (65536 строк и 256 столбцов). Но если вы хотите использовать для базы данных это пространство полностью, то лучше воспользуйтесь реляционными базами данных, например Access или Visual FoxPro, так как Excel не обработает такой объем информации.

Основные правила подготовки информации для создания базы данных

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

• Обдумайте содержание вашей базы данных, ее назначение, размеры, название, расположение на рабочем листе.

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

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

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

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

• Не вводите непонятные или не принятые сокращения слов.

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

В следующих упражнениях создадим базу данных Зарплата (рисунок 1), а затем будем работать с ней.

Рисунок 1. Начальный вариант базы данных Зарплата

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

Упражнение 1. Порядок ввода данных в список Зарплата

1. Для создания БД Зарплата введите сначала в ячейку В1 справочную информацию Тарифная ставка, а в ячейку С1 введите значение тарифной ставки, равное 1100 рублям. В дальнейшем при изменении тарифной

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

2. Введите название таблицы (см. рисунок 1) в ячейку В3.

3. В ячейки В5:В14 (поле Ф.И.О.) введите фамилии с инициалами имен и отчеств работников (см. рисунок 1).

4. В ячейки А4:F4 введите имена полей базы данных (см. рисунок 1). Выделите имена полей жирным шрифтом, выровняйте по центру с переносом по словам.

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

Упражнение 2. Заполнение ячеек последовательными значениями

1. В ячейку А5 введите число 1.

2. Выделите ячейки А5:А14.

3. Выполните команды Правка/Заполнить/Прогрессия. Появится диалоговое окно Прогрессия.

4. В секции Расположение выберите по столбцам.

5. Выберите тип прогрессии Арифметическая прогрессия.

6. В поле Шаг: введите 1.

7. Щелкните ОК. Ячейки заполнятся числами от 1 до 10.

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

Упражнение 3. Выделение составных частей имени

1. Вставьте столбец после столбца В.

2. Выделите поле Ф.И.О. в БД Зарплата. Выберите Данные/Текст по столбцам…. Появится диалоговое окно Мастер текстов – шаг 1.

3. Установите селектор с разделителями. Щелкните Далее.

4. Во втором окне мастера текстов выберите символом разделителем пробел. Щелкните Далее.

5. В третьем окне мастера текстов установите формат общий. Нажмите кнопку Готово.

6. В ячейку В4 введите новое имя поля Фамилия, в ячейку С4 введите имя поля И.О.

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

Упражнение 4. Проверка данных при вводе

1. Выделите ячейки D5:D14.

2. Выберите Данные/Проверка…. Появится диалоговое окно Проверка вводимых значений, которое содержит три вкладки.

3. На вкладке Параметры откройте выпадающий список Тип данных и выберите Целое число.

4. Откройте выпадающий список Значение и выберите между.

5. В поле Минимум наберите

   1, в поле Максимум введите 25.

6. Щелкните на вкладке Сообщение для ввода.

7. В текстовом поле Заголовок: напечатайте Число дней. В текстовом поле Сообщение: напечатайте Вводите числа от 1 до 25.

8. Перейдите на вкладку Сообщение об ошибке. Установите переключатель Выводить сообщение об ошибке.

9. Откройте выпадающий список Вид. Выберите формат сообщения Останов. В поле Заголовок: напечатайте Дни. В поле Сообщение: наберите текст сообщения — Нажмите клавишу Повторить и введите правильное значение от 1 до 25.

10. Нажмите ОК. Диалоговое окно закроется.

11. Теперь вводите данные в поле Число дней. Введите в ячейку D5 число 240. С помощью проверки данных исправьте ошибку.

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

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

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

В поле Начислено нужно ввести формулу вычисления заработной платы =D5*$C$1. Заработная плата в данной задаче вычисляется умножением числа дней, отработанных за месяц (ячейки D5:D14), на величину тарифной ставки. Значение тарифной ставки содержится в ячейке С1

. При копировании формулы в ячейки D5:D14 это значение, очевидно, должно оставаться неизменным. Значит, в формулу в ячейке Е5 должна быть введена абсолютная ссылка на ячейку С1. Абсолютную ссылку можно задать с помощью функциональной клавиши [F4].

Упражнение 5. Использование клавиши [F4] для ввода абсолютной ссылки

1. Активизируйте ячейку Е5 и наберите (но не вводите!) с клавиатуры или с помощью панели формул (см. упражнение 22) формулу =D5*C1.

2. Нажмите клавишу [F4]. Ссылка C1 в ячейке D5 станет абсолютной: $C$1.

3. Снова нажмите клавишу [F4]. Абсолютным станет номер строки: C$1.

4. Нажмите клавишу [F4]. Абсолютным станет имя столбца: $C1.

5. Нажмите клавишу [F4]. Ссылка С1 станет относительной: С1.

6. Еще раз нажмите клавишу [F4]. Ссылка C1 в ячейке D5 станет абсолютной: $C$1.

7. Теперь подтвердите ввод формулы в ячейку Е5.

8. Скопируйте содержимое ячейки Е5 в ячейки Е6:Е14.

Упражнение 6. Вычисление совокупного налога

1. Для заполнения формулами вычисляемого поля Удержано в ячейку F5 введите формулу =0,13*Е5 (тринадцать процентов совокупного налога от начисленного заработка).

2. Скопируйте эту формулу в ячейки F6:F14.

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

Упражнение 7. Использование имен полей в вычисляемых полях

1. Активизируйте ячейку G5.

2. Введите с клавиатуры формулу с именами полей =Начислено-Удержано.

3. Скопируйте эту формулу в диапазон ячеек G6:G14.

Округлите все результаты расчетов до двух знаков после запятой. Сохраните базу данных Зарплата в своей папке. Результат всех действий по созданию базы данных показан на рисунке 2.

Рисунок 2. Подготовленная к работе база данных Зарплата

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

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

​Смотрите также​ можно, как и​ регионам, клиентам или​ но источник будет​ бизнес-процесса с точки​ убирали сортировку по​ ГРАНИЦ.​ Microsoft Access, но​ интересующую пользователя информацию.​ базе данных (БД),​ желание пользователя. Это​ количества значений в​ может предложить ему​ и хранящая в​ для поля​ столбца, значение которого​ жестком диске или​В пакете Microsoft Office​

​ в классической сводной​ категориям. В старых​

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

​ уже:​ зрения руководителя​ цене, то эти​Аналогично обрамляем шапку толстой​ и Excel имеет​

​ Данные остаются в​ включающая информацию об​​ можно сделать несколькими​​ определенном диапазоне.​ автоматическое заполнение ячеек​ себе информационные материалы​

​«Пол»​​ собираемся отфильтровать. В​​ съемном носителе, подключенном​ есть специальная программа​ таблице, просто перетащить​

​ версиях Excel для​=ДВССЫЛ(«Клиенты[Клиент]»)​Со всем этим вполне​ продукты расположились еще​

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

​ внешней границей.​ все возможности для​

1. ​ таблице, но невидимы.​ одном объекте.​

2. ​ способами:​Чтобы получился динамический диапазон,​

3. ​ заранее заданной информацией.​ самого разного назначения.​

4. ​. Ведь тут возможно​ открывшемся окошке снимаем​ к ПК.​ для создания базы​ мышью нужные нам​ этого потребовалось бы​Функция​

​ может справиться Microsoft​ и в порядке​

​​​ формирования простых баз​ В любой момент​

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

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

1. ​Можно сказать, что после​​ данных и работы​​ поля из любых​

2. ​ использовать несколько функций​ДВССЫЛ (INDIRECT)​ Excel, если приложить​ ее возрастания.​Теперь обратимся к функциям,​​ данных и удобной​​ их можно восстановить.​

3. ​ БД, содержащий однотипные​​ информацию, содержащуюся на​​ СМЕЩ. Она, независимо​ высота строки, размер​ это набор организованных​ мужской и женский.​ значений, записи с​ этого мы уже​ с ними –​ связанных таблиц в​ВПР (VLOOKUP)​нужна, в данном​​ немного усилий. Давайте​​И еще одна полезная​ которые Excel предлагает​ навигации по ним.​В программе Excel чаще​ данные обо всех​ листе Excel, скопировать​ от того, как​ и тип шрифта,​ и разложенных по​Создаем дополнительный список. Удобнее​ которыми хотим скрыть.​ имеем готовую базу​ Access. Тем не​ области​​для подстановки цен,​​ случае, потому что​

4. ​ попробуем это реализовать.​​ функция, которая позволит​​ для работы с​Как сделать базу данных​ всего применяются 2​ объектах.​​ ее и перенести​​ были заданы аргументы,​ цвет поля и​ полочкам сведений. Сколько​ всего его будет​ После того как​

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

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

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

1. ​,​ и т.д. в​ не понимает прямых​ и клиентах будем​ максимальное, минимальное или​Пример: нам нужно узнать​​ не было удобно​​Автофильтр;​​ соответствуют строкам и​​ Для этого выделите​

   ​ исходные данные. В​ все может в​

   • ​ классе, их характеристики,​
   • ​ листе. В нём​
   • ​ на кнопку​
2. ​ в таком состоянии,​ этих целей более​Строк​ таблицу​ ссылок на умные​ хранить в трех​ среднее значение и​ все товары, которые​​ не только хранить,​​фильтр по выделенному диапазону.​

3. ​ столбцам стандартной таблицы​ данные, предназначенные для​​ выпадающем списке, который​​ один клик сделать​ даты рождения и​ указываем перечень значений,​
   • ​«OK»​​ как она представлена​​ знакомое им приложение​,​Продажи​ таблицы в поле​​ таблицах (на одном​​ т.п. в имеющейся​
   • ​ принимал Петров А.А.​​ но и обрабатывать​​Автофильтр предлагает пользователю выбрать​ Microsoft Excel.​ копирования, и щелкните​ получится в итоге,​ за вас автоформа,​ табель успеваемости –​ которые будут появляться​.​ сейчас, но многие​​ – Excel. Нужно​​Столбцов​​. Это требует времени​​ Источник. Но та​ листе или на​​ БД. Она называется​​ Теоретически можно глазами​​ данные: формировать отчеты,​​ параметр фильтрации из​
   • ​Если Вы умеете делать​ правой кнопкой мышки.​​ не должно встречаться​​ если правильно ее​ все это в​ в выпадающем списке.​

   ​Как видим, после этого,​ возможности при этом​ отметить, что у​​или​​ и сил от​

   ​ же ссылка «завернутая»​ разных — все​ ПРОМЕЖУТОЧНЫЕ ИТОГИ. Отличие​ пробежаться по всем​ строить графики, диаграммы​ готового списка.​ простые таблицы, то​

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

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

Содержание

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

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

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

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

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

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

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

1. Вписываем заголовки полей (столбцов) БД.
2. Заполняем наименование записей (строк) БД.
3. Переходим к заполнению базы данными.
4. После того, как БД заполнена, форматируем информацию в ней на свое усмотрение (шрифт, границы, заливка, выделение, расположение текста относительно ячейки и т.д.).

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

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

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

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

1. Переходим во вкладку «Данные».
2. Выделяем весь диапазон таблицы. Кликаем правой кнопкой мыши. В контекстном меню жмем на кнопку «Присвоить имя…».
3. В графе «Имя» указываем то наименование, которым мы хотим назвать базу данных. Обязательным условием является то, что наименование должно начинаться с буквы, и в нём не должно быть пробелов. В графе «Диапазон» можно изменить адрес области таблицы, но если вы её выделили правильно, то ничего тут менять не нужно. При желании в отдельном поле можно указать примечание, но этот параметр не является обязательным. После того, как все изменения внесены, жмем на кнопку «OK».
4. Кликаем по кнопке «Сохранить» в верхней части окна или набираем на клавиатуре сочетание клавиш Ctrl+S, для того, чтобы сберечь БД на жестком диске или съемном носителе, подключенном к ПК.

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

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

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

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

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

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

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

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

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

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

6. Для того, чтобы вернуть все данные на экран, кликаем на пиктограмму того столбца, по которому проводилась фильтрация, и в открывшемся окне напротив всех пунктов устанавливаем галочки. Затем жмем на кнопку «OK».
7. Для того, чтобы полностью убрать фильтрацию, жмем на кнопку «Фильтр» на ленте.

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

Поиск

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

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

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

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

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

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

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

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

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

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

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

1. Создаем дополнительный список. Удобнее всего его будет разместить на другом листе. В нём указываем перечень значений, которые будут появляться в выпадающем списке.
2. Выделяем этот список и кликаем по нему правой кнопкой мыши. В появившемся меню выбираем пункт «Присвоить имя…».
3. Открывается уже знакомое нам окно. В соответствующем поле присваиваем имя нашему диапазону, согласно условиям, о которых уже шла речь выше.
4. Возвращаемся на лист с БД. Выделяем диапазон, к которому будет применяться выпадающий список. Переходим во вкладку «Данные». Жмем на кнопку «Проверка данных», которая расположена на ленте в блоке инструментов «Работа с данными».
5. Открывается окно проверки видимых значений. В поле «Тип данных» выставляем переключатель в позицию «Список». В поле «Источник» устанавливаем знак «=» и сразу после него без пробела пишем наименование выпадающего списка, которое мы дали ему чуть выше. После этого жмем на кнопку «OK».

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

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

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

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

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

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

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

1 — пример базы данных в Excel с финансовой информацией

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

Для начала рассмотрим пример финансовой базы данных:

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

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

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

2 — пример базы данных в Excel с информацией о продажах

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

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

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

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

3 — для чего нужны базы данных?

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

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

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

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

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

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

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

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

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

• Шаг 1 — Подумайте о структуре базы данных в Excel

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

• Шаг 2 — Перечислите интересующие поля

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

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

• Шаг 3 — Сделать выпуск

В приведенном выше примере у нас были выпуски 3: рубашка, средняя и короткая.

• Шаг 4 — Создавать отчеты и информацию из базы данных в Excel

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

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

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

Если вы хотите сделать еще один шаг вперед в самом Excel, то он платит, чтобы понять немного VBA (Visual Basic для приложений), Я не буду объяснять большую часть VBA, но для тех, кто этого не знает, VBA позволяет создавать ряд элементов, которые упрощают заполнение и создание вашей базы данных excel, например:

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

• Автоматические сообщения

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

• Улучшена обработка данных
• Автоматизация некоторых процессов
• Автоматическое создание отчетов

Эти элементы 3 могут быть сделаны несколькими различными способами.

5 — Основные функции Excel, которые вы должны знать

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

• создавать таблицы вместо использования выбора данных
• создавать сводные таблицы (не обязательно, но хорошо и помогает)
• использовать приборные панели (приборные панели)
• основные функции, такие как PROCV, SE, E, OR
• используйте расширенные функции, такие как INDIRECT, OFFLOCK, CORRESP
• применять фильтры и промежуточные итоги
• поиск предметов с помощью команды поиска и поиска
• применять условное форматирование
• использовать проверку данных

80% этих знаний вы можете узнать в нашей Курс Basic и Intermediate Excel.

6 — Ограничения баз данных в Excel

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

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

• Очень большие базы данных Excel могут начать сбой
• Чрезмерное использование формул и функций может сделать ваш рабочий лист медленным (мне не нравится работать с электронными таблицами над 5MB — в некоторых случаях это просто невозможно)
• Безопасность базы данных в Excel не самая лучшая (относительно легко обойти более простые стандарты защиты Excel)
• Вам необходимо будет заботиться об архитектуре данных и особенно с их вставкой (в некоторых BD этот процесс более защищен от ошибок)

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

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

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

​Смотрите также​ заголовком. Вспомогательная табличка​Шаблон для менеджера, позволяющий​ должны выбрать параметры​ то что стандартный​ с помощью сводной​ модуль через меню​Клиенты​ формула все равно​ во вкладке ДАННЫЕ​ строить графики, диаграммы​ полями, меняя название​ учится 25 детей,​ в которой будет​, которая расположена на​ открывшемся окне напротив​ будут урезаны. Ниже​В пакете Microsoft Office​

​ критериев поискового запроса​ контролировать результат обзвона​

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

​ и значения сортировки.​ пакет MS Office​ таблицы. Установите активную​Insert — Module​- город и​

​ будет находиться в​ нажимаем ФИЛЬТР (CTRL+SHIFT+L).​​ и т.д.​​ источника на соответствующее​ значит, и родителей​ вся нужная информация.​

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

​ клиентов. Скачать шаблон​Данные в таблице распределились​ имеет отдельное приложение​ ячейку в таблицу​

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

​и вводим туда​ регион (адрес, ИНН,​

1. ​ ячейке F26.​У каждой ячейки в​

2. ​Для начала научимся создавать​ данным ячейкам. Работа​

3. ​ будет соответствующее количество.​ Прежде чем приступить​

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

​ для создания и​Продажи​

​ код нашего макроса:​​ банковские реквизиты и​Функция ПРОМЕЖУТОЧНЫЕ ИТОГИ имеет​

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

​ шапке появляется черная​ БД с помощью​ над выпадающими списками​ Чтобы не нагромождать​ к решению вопроса,​«Работа с данными»​ на кнопку​

1. ​ функциональной.​​ данных и работы​​ сформулирована, чтобы критерии​

2. ​ Excel. Образец:​ договора.​ ведения баз данных​и выберите на​Sub Add_Sell() Worksheets(«Форма​​ т.п.) каждого из​​ 30 аргументов. Первый​

3. ​ стрелочка на сером​​ инструментов Excel. Пусть​​ почти завершена. Затем​ базу данных большим​ как сделать базу​.​«OK»​Работа с базами данных,​ с ними –​ однозначно и точно​Простейший шаблон.Клиентская база в​Теперь менеджер видит, с​​ – Microsoft Access,​​ ленте вкладку​ ввода»).Range(«A20:E20»).Copy ‘копируем строчку​ них.​ статический: код действия.​ фоне, куда можно​ мы – магазин.​ выделяем третью ячейку​ числом записей, стоит​ данных в Excel,​Открывается окно проверки видимых​.​ прежде всего, предусматривает​ Access. Тем не​ определяли данные, которые​​ Excel скачать бесплатно.​​ кем пора перезаключить​

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

​ возможность упорядочивания, отбора​ менее, многие пользователи​ нужно найти в​ Образец:​ договор. А с​ Microsoft Excel для​ (Insert — Pivot​ формы n =​Продажи​ Excel сумма закодирована​ данные. Нажимаем ее​ данных по поставкам​ через всю таблицу.​

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

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

1. ​ БД. Если же​Шаблоны можно подстраивать «под​ какими компаниями продолжаем​ этих же целей.​ Table)​ Worksheets(«Продажи»).Range(«A100000»).End(xlUp).Row ‘определяем номер​​будет использоваться нами​​ цифрой 9, поэтому​​ у параметра ПРИНИМАЛ​​ различных продуктов от​

   ​ База данных в​ информацию, а когда​

   • ​ взаимодействии с ней.​
   • ​выставляем переключатель в​
   • ​ на кнопку​
2. ​ Подключим эти функции​ этих целей более​ функция БИЗВЕЧЬ возвращает​ себя», сокращать, расширять​ сотрудничество.​ Ведь возможности программы​. В открывшемся окне​ последней строки в​​ впоследствии для занесения​​ ставим ее. Второй​

3. ​ ТОВАР и снимаем​ разных поставщиков.​​ Excel почти готова!​​ она снова потребуется,​Горизонтальные строки в разметке​ позицию​
   • ​«Фильтр»​​ к нашей БД.​​ знакомое им приложение​ ошибку #ЗНАЧ! –​ и редактировать.​БД в процессе деятельности​​ позволяют: сортировать; форматировать;​​ Excel спросит нас​
   • ​ табл. Продажи Worksheets(«Продажи»).Cells(n​​ в нее совершенных​​ и последующие аргументы​ галочку с фамилии​№п/п​Красивое оформление тоже играет​ они услужливо предоставят​ листа «Эксель» принято​«Список»​на ленте.​Выделяем информацию того поля,​​ – 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, выбираем «Внешние данные» -«Электронные таблицы Эксель» — вводим название таблицы – создать таблицу для связи с указанием ее наименования.

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

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

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

Как сделать 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 заключается в том, что срок бесплатного пользования надстройкой ограничен всего двумя календарными неделями.

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

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

ДА НЕТ