В excel работа с базой данных: Создание базы данных в Excel по клиентам с примерами и шаблонами

Функции базы данных (справка) — Служба поддержки Office

Обработка данных в списках или электронных таблицах Excel

2.2. Процессоры электронных таблиц

2.2.5. Работа с таблицей Excel как с базой данных

Обработка информации в электронных таблицах Excel или списках. Основные понятия и требования к спискам

Список представляет собой электронную таблицу с большим объемом взаимосвязанной информации (список товаров на складах или список номеров телефонов и адресов абонентов). Список — это набор строк электронной таблицы со взаимосвязанными однотипными данными постоянного формата. Другими словами список — это плоская база данных, а строки и столбцы списка соответствуют записям и полям в базе данных. Образец списка представлен на рисунке.

Рис. 1.

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

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

К средствам, которые предназначены для обработки и анализа данных в списке относятся команды из меню Данные: Сортировка, Фильтр, Форма, Итоги, Проверка. При выполнении этих команд, редактор автоматически распознает список как базу данных и осуществляет обработку и анализ данных в списке как в базе данных.

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

Для добавления новых записей в список, удаления и поиска существующих записей в списках применяется команда Форма. Для проверки данных при вводе используется средство, которое называется проверкой ввода (команда Проверка).

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

1. Чтобы редактор автоматически распознавал список как базу данных и обрабатывал данные при выполнении команд обработки необходимо на рабочем листе располагать один список.
2. Формат шрифта заголовков (подписей) столбцов или имен полей в списках должен отличаться от формата шрифта записей. Обычно шрифту заголовкам столбцов назначается полужирный шрифт, а ячейкам для заголовков присваивается текстовый формат.
3. Ячейки под заголовками столбцов необходимо отформатировать в соответствии с данными, которые будут вводиться в эти ячейки (например, установить денежный формат, выбрать выравнивание и т.д.).
4. Для обеспечения автоматического форматирования введенных данных в список целесообразно активизировать команду «Расширение форматов и формул». Для этого необходимо установить флажок «Расширять форматы и формулы в диапазонах данных» в окне диалога «Параметры» на вкладке «Правка», которое открывается командой «Параметры» в меню Сервис.
5. В списке не должно быть пустых записей (строк) и полей (столбцов), даже для отделения имен полей от записей следует использовать границы ячеек, а не пустые строки.

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

Далее …>>> Тема: 2.2.6. Экономико — математические приложения Excel

Урок 6.7. Базы данных в Excel. Компьютер на 100. Начинаем с Windows Vista

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

Вывод данных из записной книжки в таблицу Microsoft Excel

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

Рис. 8.10. Рабочая книга Microsoft Excel с данными из файла book.xmlДля того чтобы использовать определенные в

Импорт данных из электронных таблиц Excel 2007 в списки SharePoint

Импорт данных из электронных таблиц Excel 2007 в списки SharePoint
Часто встречаются такие ситуации, когда данные уже введены в электронные таблицы, но их нужно предоставить в совместное пользование другим членам группы. Службы SharePoint предоставляют возможность импорта данных из

Экспорт данных из базы данных Access 2007 в список SharePoint

Экспорт данных из базы данных Access 2007 в список SharePoint
Access 2007 позволяет экспортировать таблицу или другой объект базы данных в различных форматах, таких как внешний файл, база данных dBase или Paradox, файл Lotus 1–2–3, рабочая книга Excel 2007, файл Word 2007 RTF, текстовый файл, документ XML

Перемещение данных из базы данных Access 2007 на узел SharePoint

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

Спасение данных из поврежденной базы данных

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

2.2.5. Базы данных

2.2.5. Базы данных
При написании CGI приложений, вам необходим, какой то путь для доступа к данным базы. Одним из простых решений будет использование BDE и помещение ваших данных в таблицы Парадокса или dBASE. Если по какой либо причине BDE не инсталлировано на вашем NT Web сервере

Базы данных

Базы данных
1. В чем заключаются преимущества нового 32-разрядного Borland Database Engine? Новый 32-разрядный Borland Database Engine включает полностью новое ядро запросов, которое было оптимизировано для работы как с удаленными SQL-серверами, так и с локальными данными. 32-разрядный Borland Database

Обновление базы данных с помощью объекта адаптера данных

Обновление базы данных с помощью объекта адаптера данных
Адаптеры данных могут не только заполнять для вас таблицы объекта DataSet. Они могут также поддерживать набор объектов основных SQL-команд, используя их для возвращения модифицированных данных обратно в хранилище

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

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

Кэш базы данных

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

Базы данных (классы для работы с базами данных)

Базы данных (классы для работы с базами данных)
В MFC включены несколько классов, обеспечивающую поддержку приложений, работающих с базами данных. В первую очередь это классы ориентированные на работу с ODBC драйверами – CDatabase и CRecordSet. Поддерживаются также новые средства для

Урок 6.1. Знакомство с программой Excel

Урок 6.1. Знакомство с программой Excel

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

Урок 6.3. Простые расчеты в Excel

Урок 6.3. Простые расчеты в Excel

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

Урок 7.4. Проектирование базы данных. Создание связей между таблицами

Урок 7.4. Проектирование базы данных. Создание связей между таблицами

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

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

Лекция 3. Списки в MS Excel. Простейшие базы данных

1. Списки в MS Excel

Простейшие базы данных

2. База данных

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

3. Системы управления базами данных (СУБД)

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

4. Список

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

5. Создание списка

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

6. Формы для ввода и редактирования данных

Форма — способ отображения данных, когда на экране
располагаются поля только одной записи-строки.
Для работы с формой нужно выбрать пункт меню
Данные, Форма… главного меню Excel, предварительно
выделив любую ячейку списка. Открывшееся диалоговое
окно будет иметь то же название, что и рабочий лист.
В окне выводятся заголовки столбцов таблицы в качестве
названий полей и окна для ввода данных, курсор стоит в
первом окне ввода. Для перехода в следующее окно
ввода используется клавиша Tab, для возврата в
предыдущее — одновременно нажатые клавиши Shift и
Tab. Перемещаться по всем элементам формы можно
щелчком левой кнопки мыши. Доступ к вычисляемым
полям блокирован. Значения этих полей автоматически
заполняются на основе информации, содержащейся в
таблице.
Сортировка
Способы
обработки
списка
Фильтрация
Подведение
итогов

8. Сортировка списка

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

9. Фильтрация данных в списке

Фильтрация позволяет находить и отбирать для
обработки часть записей (строк), которые содержат
определенное значение или отвечают определенным
критериям. Остальные строки при этом скрыты.
В Excel для отбора данных списка (реализации запроса к
базе данных) используются Автофильтр и Расширенный
фильтр.
Расширенный фильтр применяется, если в отборе
участвуют более двух критериев (условий поиска) или
если при сравнении используются результаты
вычислений.
Для установки Автофильтра следует, находясь в
пределах списка, выполнить команду Данные, Фильтр,
Автофильтр.

10. Формирование итогов

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

Практическая работа «Создание базы данных в Excel»

Практическая работа «Создание базы данных в Excel»

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

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

Методические рекомендации по выполнению практической работы

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

Создаем таблицу Прайс:

1. Создаем в Excel новый лист с названием Прайс.

2. Создаем три столбца: Наименование, Категория, Цена. Заполняем 20 строк в созданной таблице по следующему образцу:

4. Превращаем созданную таблицу в «умную таблицу». Для этого выделяем все заполненные ячейки, нажимаем Главная – Форматировать как таблицу. Из выпавшего списка выбираем тот стиль оформления, который нам понравился.

5. Далее идем на вкладку Работа с таблицами — Конструктор. В окошке Имя таблицы меняем наименование на Прайс.

6. Создаем в Excel новый лист с названием Клиенты.

7. Создаем два столбца: Клиент, Город. Заполняем 20 строк в созданной таблице по следующему образцу:

8. Аналогично предыдущем листу превращаем созданную таблицу в «умную таблицу». Для этого выделяем все заполненные ячейки, нажимаем Главная – Форматировать как таблицу. Из выпавшего списка выбираем тот стиль оформления, который нам понравился.

9. Далее идем на вкладку Работа с таблицами — Конструктор. В окошке Имя таблицы меняем наименование на Клиенты.

10. Создаем в Excel новый лист с названием Продажи.

11. Создаем пять столбцов: Дата, Товар, Кол-во, Стоимость, Клиент. Не заполняем!!!

12. Аналогично предыдущем листу превращаем созданную таблицу в «умную таблицу». Для этого выделяем все заполненные ячейки, нажимаем Главная – Форматировать как таблицу. Из выпавшего списка выбираем тот стиль оформления, который нам понравился.

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

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

Создаем Форму ввода:

1. Создаем в Excel новый лист с названием Форма ввода.

2. Оформляем лист следующим образом:

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

4. Откроется диалоговое окно:

5. В поле Тип данных выбираем Список.

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

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

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

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

После нажатия кнопки Enter нужная цена появится автоматически.

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

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

1. После заполнения формы нужно введенные в нее данные добавить в конец таблицы Продажи. Сформируем при помощи простых ссылок строку для добавления прямо под формой (обратите внимание, ячейки формируются в той последовательности, в какой они идут в таблице Продажи, т.е. в ячейке A20 будет ссылка =B3, в ячейке B20 ссылка на =B7 и т.д.):

2. Теперь создадим макрос, который копирует созданную строку и добавляет его в таблицу Продажи. Для этого нажимаем Разработчик — Visual Basic. Если вкладку Разработчик не видно, то включите ее сначала в настройках Файл — Параметры — Настройка ленты. Поставить галочку напротив меню Разработчик:

3. После этого откроется окно Microsoft Visual Basic for Applications:

4. В открывшемся окне редактора Visual Basic вставляем новый пустой модуль через меню Insert — Module и вводим туда код нашего макроса (обратите внимание, названия листов должны полностью совпадать с вашими):

Sub Add_Sell()

Worksheets(«Форма ввода»).Range(«A20:E20»).Copy

n = Worksheets(«Продажи»).Range(«A100000»).End(xlUp).Row

Worksheets(«Продажи»).Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues

Worksheets(«Форма ввода»).Range(«B5,B7,B9»).ClearContents

End Sub

5. Закрываем окно редактора Visual Basic (никаких сохранений это действие не потребует).

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

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

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

9. Сохранять созданный файл нужно следующим образом: Файл – Сохранить как – Тип файла: Книга Excel с поддержкой макросов.

План урока по теме «Работа с таблицей как с базой данных в MS Excel» | План-конспект по информатике и икт по теме:

План занятия

№ занятия 23,24

Тема занятия: «Работа с таблицей как с базой данных».

Дата проведения 02 марта 2018

Дисциплина: Информационные технологии в профессиональной деятельности

Профессия: 29.01.04. «Художник по костюму»

Тип занятия: комбинированный

Вид занятия: урок-практикум

Уровень усвоения учебной информации второй, воспроизведение

Цели занятия:

1. Образовательная

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

углубить знания  обучающихся  по теме: обработка данных средствами Microsoft Excel;

2. Воспитательная

Содействие формированию общих компетенций: ОК 2, ОК 3, ОК 4, ОК 5

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

3. Развивающая

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

Методы обучения: словесные, наглядные и практические.

Междисциплинарные и внутрипредметные связи: Тема «Базы данных»

Средства обучения: персональные компьютеры с установленными ОС Windows и пакетом Microsoft Office 2010 по количеству обучающихся, раздаточный материал, презентация, тест.

По итогам обучения обучающийся должен:

Иметь представление о базах данных, их роли в современном мире;

Знать: правила оформления баз данных (списков) в среде Excel, способы ввода данных в списки, приемы обработки записей базы данных;

Уметь: создавать списки в среде Excel, вводить данные списка, обрабатывать записи базы данных (производить сортировку, поиск и фильтрацию записей).

Ход занятия

1. Ефимова О.В. Microsoft Excel. Электронные таблицы. Тетрадь 1,2 Интеллект-Центр
2. www.infourok.ru

Использование Access или Excel для управления данными

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

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

Локальные и внешние данные Вы можете использовать Access для подключения к данным из различных внешних источников данных, чтобы вы могли просматривать, запрашивать и редактировать эти данные без необходимости их импорта. Например, Access предоставляет команды для подключения к существующим данным в базе данных Microsoft SQL Server, файлу dBASE или папке Outlook, а также ко многим другим источникам данных.Вы можете использовать Excel для подключения к широкому спектру источников данных, включая базы данных Access, SQL Server и Analysis Services, текстовые и XML-файлы, а также источники данных ODBC и OLE DB. Однако вы не можете редактировать данные для изменения исходных данных через пользовательский интерфейс Excel.

И Access, и Excel предоставляют команды для подключения к данным в списках Windows SharePoint Services. Однако Excel обеспечивает только подключение к спискам SharePoint только для чтения; тогда как Access позволяет читать и записывать данные в списки SharePoint.

Целостность данных или гибкость Уникальные идентификаторы помогают сохранить целостность ваших данных и гарантируют, что никакие две строки (или записи) не содержат абсолютно одинаковых данных. Уникальные идентификаторы также обеспечивают самый быстрый способ извлечения данных при поиске или сортировке данных. В Access вы можете использовать тип данных AutoNumber для автоматического создания уникального идентификатора для каждой записи. Затем вы можете использовать эти идентификаторы, чтобы связать записи в одной таблице с одной или несколькими записями в другой таблице.

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

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

Как создать базу данных с возможностью поиска в Excel | Small Business

Автор: Nicky LaMarco Обновлено 21 февраля 2019 г.

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

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

Как работает реляционная база данных?

Excel структурирован таким образом, чтобы обеспечить его совместимость с базами данных.По крайней мере, согласно PC World, база данных — это просто набор взаимосвязанных элементов. Это в значительной степени то, что представляет собой электронная таблица. Когда элементы в базе данных связаны, они создают записи в группах из нескольких записей. Одна запись может быть эквивалентом строки в электронной таблице, а набор записей может быть эквивалентом таблицы в электронной таблице. Связь сложно игнорировать.

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

Пример базы данных

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

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

Основная база данных

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

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

Для чего нужна реляционная база данных?

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

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

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

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

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

Пришло время ввести свои данные. Сделайте это в таком количестве строк, которое, по вашему мнению, вам понадобится. Клавиши со стрелками должны помочь вам перемещаться по электронной таблице, в то время как подтверждение ввода в любую ячейку должно быть таким же простым, как нажатие клавиши ввода.Возможно, вы ввели свои данные в другой документ, где они разделены символом табуляции, например в Microsoft Word. В этом случае вы можете просто скопировать и вставить его в свою электронную таблицу. Чтобы вставить его в базу данных, щелкните ячейку с меткой A2 и нажмите Ctrl + V на клавиатуре. Данные будут вставлены под вашими заголовками.

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

Использование фильтров в столбцах

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

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

Фильтр в соответствии с условиями

Если вы хотите выполнить фильтрацию в соответствии с условиями, выберите формулу из «Текстовые фильтры», или «Числовые фильтры». Вы можете использовать такие вещи, как значения между двумя конкретными значениями или больше или меньше заданного значения и т. Д. Есть кнопки для «И», и «Или», для каждого условия, так что вы можете объединить свои критерии, чтобы они были взаимоисключающими или складывающимися.

Щелкните на кнопке с надписью «ОК» , чтобы ваши данные были отфильтрованы. В выбранном столбце вы увидите только те строки, которые соответствуют условиям фильтрации.

Отключить фильтрацию

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

Базы данных Excel: создание реляционных таблиц

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

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

скачать

Это рабочая книга с несколькими электронными таблицами, содержимое которых можно использовать для отработки задач Excel в реляционных базах данных, отчетах и ​​сводных таблицах. JD Sartain

Как работают реляционные базы данных

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

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

Microsoft Office для дома и бизнеса 2016

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

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

JD Sartain / PC World

Базовая база данных электронных таблиц в виде плоских файлов

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

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

JD Sartain / PC World

Главный и подчиненные устройства реляционной базы данных

Кому нужна реляционная база данных

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

Наиболее важной причиной для реляционных баз данных является создание запросов и фильтров, которые могут извлекать конкретную информацию и распечатывать отчеты. Допустим, вашему начальнику нужен отчет, в котором все жители округа Санта-Роса, штат Флорида, имеют штрафы на сумму свыше 300 долларов; или сколько водителей в Пенсаколе продлили лицензию в декабре? Без емкости реляционной базы данных вам пришлось бы вырезать и вставить эту информацию вместе из трех или более таблиц в четвертую таблицу, а затем надеяться — когда вы все закончите — ваш босс не объявит: «Я сказал округ Сарасота, а не Округ Санта-Роза.»

JD Sartain / PC World

Отчеты реляционной базы данных — штрафы

Создание реляционной базы данных

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

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

1. Используйте те же поля, что и на первом снимке экрана выше («Базовая база данных электронных таблиц с плоскими файлами»), а затем заполните 12-15 записей своими данными (или используйте наши). Эти поля: Номер лицензии, Имя, Адрес, Город, Штат, Почтовый индекс, Дата рождения, Срок действия и Пол.

2. По завершении выделите всю таблицу, включая заголовки; то есть с A1 по I15 (A1: I15). Помните, что CTRL + A выделяет всю таблицу одним движением, пока ваш курсор находится где-то / где-нибудь внутри диапазона таблицы.

3. Выберите вкладку и кнопку: Вставить > Таблица

4. Если диапазон выделен (что должно быть), адрес диапазона появится в поле с названием: Где данные для вашей таблицы в диалоговом окне Создать таблицу .Установите флажок: Моя таблица имеет заголовки , затем нажмите ОК .

JD Sartain / PC World

Создать / вставить главную таблицу

5. Как только это диалоговое окно закроется, ОБЯЗАТЕЛЬНО укажите имя своей таблицы в поле Имя таблицы (верхний левый угол) в группе «Свойства» (на текущем экране). Введите MASTER, затем нажмите клавишу Return.

6. Обратите внимание, что в таблице теперь отображаются стрелки вниз рядом с каждым именем поля. Щелкните одну из стрелок вниз (e.g., City) и обратите внимание на появившееся диалоговое окно меню. Это поле содержит параметры сортировки, которые включают фильтры, которые позволяют отображать конкретную информацию на основе выбранного фильтра. Например, если вы снимите отметку с поля Выбрать все , а затем установите только те поля, которые говорят Майами и Дестин , Excel отфильтрует все города, ЗА ИСКЛЮЧЕНИЕМ Майами и Дестина.

ПРИМЕЧАНИЕ: Не паникуйте, когда все оставшиеся записи исчезнут. Они не исчезли, просто спрятаны.Чтобы просмотреть их снова, щелкните стрелку вниз рядом с полем «Город», затем нажмите кнопку « Все », и они сразу же снова станут видимыми.

СОВЕТ: Щелкните параметр Текстовый фильтр s в раскрывающемся меню «Фильтры», чтобы выбрать параметры фильтрации из всплывающего списка (в отличие от простого сопоставления полей из списка флажков). Варианты: «Равно», «Не равно», «Начинается с», «Заканчивается на», «Содержит», «Не содержит», «Пользовательский фильтр».

7. Не забудьте изменить вкладку (которая говорит Sheet1) в нижней части электронной таблицы на Master .Дважды щелкните вкладку электронной таблицы, чтобы быстро изменить имя.

JD Sartain / PC World

Используйте фильтры для извлечения определенных данных из таблицы электронной таблицы

Подчиненные таблицы

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

2. Скопируйте столбец A (Номера лицензий) в столбец A обеих других электронных таблиц. Отрегулируйте ширину столбца до 11.

ПРИМЕЧАНИЕ: Помните, что таблица Slave в этом примере является таблицей «один ко многим», что означает, что для каждого драйвера существует несколько записей, поэтому поле ключа (номер лицензии) может — и будет — повторяться много раз. Мы копируем его в таблицы Slave, чтобы вам не приходилось повторно вводить данные. Для каждой многократной записи просто повторно скопируйте номер лицензии, прежде чем вводить данные в остальную часть каждой записи.

3. Используйте те же поля, что и на следующем снимке экрана «Реляционная база данных: подчиненная таблица нарушений» ниже, а затем введите свои собственные данные (или используйте наши).Это следующие поля: Номер лицензии (в столбце A, конечно), Тип нарушения, Плата за нарушение, Дата нарушения, Дата истечения срока, Дата уплаты сбора

4. Введите около 30 записей с повторяющимися номерами лицензий. Например: номер лицензии F298-6588 повторяется трижды. Это означает, что у Роджера Андерсона (водителя с этим номером лицензии) имеется три водительских правонарушения.

5. По завершении выделите всю таблицу, включая заголовки: A1: h41.

6. Выберите Вставить > Таблица

7.Адрес диапазона отображается в поле под названием: Где находятся данные для вашей таблицы в диалоговом окне Create Table . Установите флажок: Моя таблица имеет заголовки , затем нажмите ОК.

8. Как только это диалоговое окно закроется, ОБЯЗАТЕЛЬНО укажите имя своей таблицы в поле Имя таблицы (верхний левый угол) в группе «Свойства» (на текущем экране). Введите НАРУШЕНИЯ, затем нажмите клавишу возврата.

9. Щелкните одну из стрелок вниз рядом с одним из столбцов / полей.Обратите внимание на появившееся диалоговое окно меню. Поэкспериментируйте с сортировкой и фильтрами, чтобы увидеть разные результаты из этой таблицы.

JD Sartain / PC World

Создание электронной таблицы ведомой таблицы нарушений

10. Для таблицы подчиненных адресов вернитесь к главному устройству и скопируйте столбцы C, D, E и F (адрес, город, штат, почтовый индекс) в столбцы B, C, D и E в таблице адресов (помните, Номер лицензии — поле уникального ключа — уже находится в столбце A.)

11. Отрегулируйте ширину столбца для размещения данных.

12. Введите около 30 записей с повторяющимися номерами лицензий. Например: номер лицензии F298-6588 повторяется трижды. Это означает, что у Роджера Андерсона (водителя с этим номером лицензии) было три предыдущих адреса.

13. Повторите инструкции с 5 по 9 выше, на этот раз назвав адреса таблиц и определив диапазон как A1: E31. Вот и все.

JD Sartain / PC World

Создание электронной таблицы таблицы подчиненных адресов

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

Как переключиться с Excel на SQL

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

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

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

У

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

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

Часто есть лучший инструмент для работы.

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

Ой, привет, SQL

Данные, с которыми вы работаете в Excel, должны были поступать из где-то из .Это где-то база данных. Даже когда вы извлекаете данные из чего-то, что не кажется техническим (например, Google Analytics, Stripe или Salesforce), за кулисами вы запрашиваете базу данных.

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

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

Перенеся некоторые из ваших начальных вычислений в SQL, вы можете сократить объем экспортируемых данных. А с меньшим набором данных у вас меньше шансов столкнуться с проблемами производительности Excel.

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

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

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

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

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

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

Причина, по которой вы не можете изменять ячейки на лету, заключается в том, что база данных имеет жесткую структуру. Значения в каждой строке связаны как единое целое. Каждый столбец должен иметь уникальное имя и может содержать данные только определенного типа («Целое число», «Текст», «Дата» и т. Д.).

Гибкая структура

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

Переход от формул к запросам

Самый распространенный способ манипулирования данными в Excel — использование формул. Формула состоит из одной или нескольких функций, которые сообщают Excel, что делать с данными в ячейке. Например, вы можете сложить числовые значения, используя SUM (A1: A5) , или усреднить их, используя AVERAGE (A1: A5) .

Эквивалент формулы в SQL — запрос . Запрос на возврат приведенной выше таблицы выглядит так:

  ВЫБЕРИТЕ player_name,
       родной город
       штат,
       масса
  ОТ benn.college_football_players
  

SELECT и FROM — это два основных ингредиента любого SQL-запроса: SELECT указывает столбцы данных, которые вы хотите, а FROM указывает, в какой таблице они находятся. Фактически вы можете выбрать отображение всех столбцов, добавив звездочка (*) после SELECT , например:

  ВЫБРАТЬ *
  ОТ Бенна.College_football_players
  

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

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

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

= ЕСЛИ (логический_тест, значение_если_ истинно, [значение_если_ ложь])

Вы могли прочитать, что как IF <какое-то условие выполнено> THEN <отобразить это значение> ИНАЧЕ <отобразить другое значение> .Часть OTHERWISE , которая отображается как [value_if_false] , является необязательной.

Эквивалент IF в SQL — CASE . У них очень похожий синтаксис:

  СЛУЧАЙ, КОГДА <условие 1 выполняется> ТО <отображаемое значение 1>
     Иначе <отобразить другое значение>
     КОНЕЦ
  

Операторы

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

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

  ВЫБЕРИТЕ player_name,
       масса,
       СЛУЧАЙ, КОГДА вес> 250, ЗАТЕМ "более 250"
            КОГДА вес> 200 ТОГДА '201-250'
            КОГДА вес> 175, ТО '176-200'
            ИНАЧЕ '175 или меньше' КОНЕЦ AS weight_group
  ОТ benn.college_football_players
  

А вот как выглядит таблица результатов:

Не слишком сложно, правда? Заявление IF для этого было бы кошмаром.

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

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

Аналитика режима

Бесплатная книга: краткое руководство пользователя Excel по SQL

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

• Арифметика
• IF
• Фильтрация
• ВПР
• СУММ , СЧЕТ и СРЕДНЕЕ
• Групповое агрегирование (сводные таблицы)
  

Загрузите Краткое руководство пользователя Excel по SQL и начните изучать SQL уже сегодня.

Следующие шаги для изучения SQL

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

Pick учебные пособия, предназначенные для анализа данных

Ресурсов SQL много, но не все они ориентированы на анализ данных.

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

Не увязайте в учебных курсах по SQL, предназначенных для управления базами данных.Изучите учебники, ориентированные на запросы. Вот несколько уроков по SQL для начала:

Если вы обнаружите, что в учебных курсах вы говорите о таких вещах, как:

• СОЗДАТЬ ТАБЛИЦУ
• КАБЕЛЬНЫЙ ТАБЛИЦА
• СОЗДАТЬ БАЗУ ДАННЫХ
• УДАЛЕНИЕ БАЗЫ ДАННЫХ

… вы попали не в ту кроличью нору.

Практикуйтесь с данными вашей компании

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

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

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

Ряд клиентов Mode даже организовали регулярные учебные сессии, во время которых аналитики обучают своих коллег SQL, используя их внутренние данные. Отзывы были в подавляющем большинстве положительные от обеих групп людей! Сообщите нам, если вы думаете о создании этого в своей компании.Мы рады поделиться некоторыми знаниями.

Вы в пути!

Не терпится начать? Загрузите наше бесплатное краткое руководство пользователя Excel и погрузитесь в наше учебное пособие по SQL!

Рекомендуемые статьи

Как создать базу данных в Excel (8 простых шагов)

Не знаете, как создать простую базу данных в Excel? В этой статье я покажу, как создать базу данных в Excel всего за 8 простых шагов.

Считаете ли вы MS Access как сложным инструментом для использования в качестве базы данных? Тогда Excel — отличный инструмент для этого.

Давайте изучим технику…

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

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

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

Функции MS Office очень пригодятся в таких ситуациях. Access — это стандартная база данных, которую вы должны использовать, но ее немного сложно изучить и освоить перед завтрашней первой деловой встречей в вашей жизни. С другой стороны, Excel немного более привычен и проще в использовании.

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

Подробнее: Введение в концепции системы управления реляционными базами данных (СУБД)!

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

Шаг 1: Ввод данных

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

Итак, поля этой базы данных — это StdID, StdName, State, Age, Department и Class Teacher.

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

Я кое-что сделал. Позвольте мне показать вам, как я ввожу еще одну запись.

Скажем, это ввод, который нужно вставить в базу данных:

StdID: 1030456042,

Стандартное имя: Джемми Фокс,

Штат Оклахома,

Студенческий возраст 25,

Департамент CSE,

А классный руководитель — г.Джон.

Итак, вы видите, что ввод данных в базу данных Excel довольно прост.

Шаг 2. Правильный ввод данных

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

Скажем, после последней строки я помещаю некоторые данные в 2 строки ^и из нее:

Стандартный идентификатор: 103457045,

Стандартное имя — Джексон,

Штат Нью-Йорк,

Возраст 23,

Отдел ETE,

А классный руководитель — г.Бальмар.

Это четкая разбивка этой базы данных.

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

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

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

Подробнее: 10 основных преимуществ MS Access над другими СУБД

Шаг 3. Знайте, что строки называются записями

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

Все строки записи. Здесь я отметил несколько для ясности.

Шаг 4. Знайте, что столбцы называются полями

Все эти столбцы — Поля . Заголовки столбцов известны как Имена полей .

Итак, StdID , StdName , State , Age , Department и Class Teacher — это шесть Имен полей этой базы данных.

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

Шаг 5: Создание таблицы

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

Откроется диалоговое окно «Создать таблицу ». Я беру то, что предлагает Excel.Да, у моей таблицы есть заголовки. Нажмите ОК , и таблица будет создана. Тада !!!

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

Шаг 6. Использование инструментов базы данных

Инструменты базы данных могут пригодиться при анализе и интерпретации данных. Вы можете и должны узнать больше об этих инструментах здесь.

Шаг 7. Расширение базы данных

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

Шаг 8: Завершение форматирования базы данных

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

Итак, поехали! Вы создали свою собственную базу данных в Excel (до тех пор, пока вы не освоите Access или у вас не закончится место и процессоры Excel).

Это снова краткое описание того, как создать базу данных в Excel.

Скачать рабочий файл

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

База данных_в_Excel.xlsx

Подробнее:

Как использовать функции базы данных в Excel

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

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

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

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

‍

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

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

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

В базе данных каждая строка называется записью. И каждый столбец — это поле.

‍

Что такое запись (строки)?

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

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

Что такое поле (столбцы)?

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

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

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

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

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

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

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

Выделите всю информацию в своей электронной таблице (от A1 до F11) и выберите Таблица в раскрывающемся списке Вставить в основном заголовке.

Excel автоматически добавит выделенные ячейки во всплывающее окно создания таблицы .Отметьте Моя таблица имеет заголовки и выберите ОК.

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

Сортировка, поиск, фильтр

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

Сортировка

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

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

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

Поиск

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

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

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

Фильтр

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

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

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

Объединение поиска, сортировки и фильтрации

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

Хотя легко было быстро определить, у какого сотрудника была самая высокая общая сумма вознаграждения, что, если вам нужно было узнать среднюю зарплату инженера-программиста в вашей компании, чтобы создать предложение о новом найме?

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

Динамическая база данных / форма для ввода данных

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

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

Используйте эти реляционные базы данных в Excel для ускорения записи важных бизнес-данных

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

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

Реляционная база данных необходима для работы с большими объемами бизнес-данных.

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

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

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

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

1. Дважды щелкните Sheet1 внизу экрана и введите Master
2. В ячейке A1 введите Мастер.
3. В ячейках A3 — F3 введите следующие заголовки столбцов: Торговый идентификатор, Продавец, Адрес, Город, Штат, Почтовый индекс.
4. В ячейках A4 — A13 введите идентификационные номера продаж — в экспериментальных целях введите 101 — 110 .
5. Теперь продолжите, заполнив имена, адреса, города, штат и почтовые индексы в остальных полях.Чтобы найти самые высокие продажи по городам, обязательно введите один и тот же город в несколько ячеек.
6. После ввода всех данных выделите таблицу и в группе «Стили» выберите «Формат » как «Таблица». В раскрывающемся меню выберите нужный цвет и формат. Когда появится диалоговое окно, убедитесь, что Моя таблица имеет заголовки отмечен.
7. Не снимая выделения с таблицы, выберите вкладку Design . В крайнем левом углу введите Master в поле «Имя таблицы».

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

1. В нижней части экрана рядом с вкладкой «Мастер» щелкните символ «плюс» (+), чтобы вставить новый лист. Дважды щелкните строку тега в левом нижнем углу с надписью Sheet2 и сделайте так, чтобы она говорила Sales.
2. В ячейке A1 введите Всего продаж за 2014 год. В ячейках A3 — E3 введите Идентификатор продажи, квартал 1, квартал 2, квартал 3, квартал 4.
3. В ячейках A4 — A13 введите идентификационные номера продаж: 101 — 110 .
4. В B4 — E13 введите 40 случайных чисел, представляющих продажи в долларах
5. После ввода данных выделите ячейки A3 — E13. Выберите Format as Table, а выберите нужный цвет и формат из раскрывающегося меню. Когда появится диалоговое окно, убедитесь, что Моя таблица имеет заголовки отмечена.
6. Не снимая выделения с таблицы, введите Продажи в разделе «Имя таблицы» слева.

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

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