Как сделать базу данных в Excel — Как создать в программном обеспечении Excel базу данных?
В пакете Microsoft Office есть специальная программа для создания базы данных и работы с ними — Access. Тем не менее, многие пользователи предпочитают использовать для этих целей более знакомое им приложение — Excel. Нужно отметить, что у этой программы имеется весь инструментарий для создания полноценной базы данных (БД). Давайте выясним, как это сделать.
Содержание
Процесс создания
База данных в Экселе представляет собой структурированный набор информации, распределенный по столбцам и строкам листа.
Согласно специальной терминологии, строки БД именуются «записями». В каждой записи находится информация об отдельном объекте.
Столбцы называются «полями». В каждом поле располагается отдельный параметр всех записей.
То есть, каркасом любой базы данных в Excel является обычная таблица.
Создание таблицы
Итак, прежде всего нам нужно создать таблицу.
- Вписываем заголовки полей (столбцов) БД.
- Заполняем наименование записей (строк) БД.
- Переходим к заполнению базы данными.
- После того, как БД заполнена, форматируем информацию в ней на свое усмотрение (шрифт, границы, заливка, выделение, расположение текста относительно ячейки и т.д.).
На этом создание каркаса БД закончено.
Урок: Как сделать таблицу в Excel
Присвоение атрибутов базы данных
Для того, чтобы Excel воспринимал таблицу не просто как диапазон ячеек, а именно как БД, ей нужно присвоить соответствующие атрибуты.
- Переходим во вкладку «Данные».
- Выделяем весь диапазон таблицы. Кликаем правой кнопкой мыши. В контекстном меню жмем на кнопку
- В графе «Имя» указываем то наименование, которым мы хотим назвать базу данных. Обязательным условием является то, что наименование должно начинаться с буквы, и в нём не должно быть пробелов. В графе «Диапазон» можно изменить адрес области таблицы, но если вы её выделили правильно, то ничего тут менять не нужно. При желании в отдельном поле можно указать примечание, но этот параметр не является обязательным. После того, как все изменения внесены, жмем на кнопку «OK».
- Кликаем по кнопке «Сохранить» в верхней части окна или набираем на клавиатуре сочетание клавиш Ctrl+S, для того, чтобы сберечь БД на жестком диске или съемном носителе, подключенном к ПК.
Можно сказать, что после этого мы уже имеем готовую базу данных. С ней можно работать и в таком состоянии, как она представлена сейчас, но многие возможности при этом будут урезаны. Ниже мы разберем, как сделать БД более функциональной.
Сортировка и фильтр
Работа с базами данных, прежде всего, предусматривает возможность упорядочивания, отбора и сортировки записей. Подключим эти функции к нашей БД.
- Выделяем информацию того поля, по которому собираемся провести упорядочивание. Кликаем по кнопке «Сортировка» расположенной на ленте во вкладке «Данные» в блоке инструментов
Сортировку можно проводить практически по любому параметру:
- имя по алфавиту;
- дата;
- число и т.д.
- В следующем появившемся окне будет вопрос, использовать ли для сортировки только выделенную область или автоматически расширять её. Выбираем автоматическое расширение и жмем на кнопку «Сортировка…».
- Открывается окно настройки сортировки. В поле «Сортировать по» указываем имя поля, по которому она будет проводиться.
- В поле «Сортировка» указывается, как именно она будет выполняться. Для БД лучше всего выбрать параметр «Значения».
- В поле «Порядок»
- Важно проследить, чтобы около значения «Мои данные содержат заголовки» стояла галочка. Если её нет, то нужно поставить.
После ввода всех нужных параметров жмем на кнопку «OK».
После этого информация в БД будет отсортирована, согласно указанным настройкам. В этом случае мы выполнили сортировку по именам сотрудников предприятия.
- Одним из наиболее удобных инструментов при работе в базе данных Excel является автофильтр. Выделяем весь диапазон БД и в блоке настроек
- Как видим, после этого в ячейках с наименованием полей появились пиктограммы в виде перевернутых треугольников. Кликаем по пиктограмме того столбца, значение которого собираемся отфильтровать. В открывшемся окошке снимаем галочки с тех значений, записи с которыми хотим скрыть. После того как выбор сделан, жмем на кнопку «OK».
Как видим, после этого, строки, где содержатся значения, с которых мы сняли галочки, были скрыты из таблицы.
- Для того, чтобы вернуть все данные на экран, кликаем на пиктограмму того столбца, по которому проводилась фильтрация, и в открывшемся окне напротив всех пунктов устанавливаем галочки. Затем жмем на кнопку
- Для того, чтобы полностью убрать фильтрацию, жмем на кнопку «Фильтр» на ленте.
Урок: Сортировка и фильтрация данных в Excel
Поиск
При наличии большой БД поиск по ней удобно производить с помощь специального инструмента.
- Для этого переходим во вкладку «Главная» и на ленте в блоке инструментов «Редактирование» жмем на кнопку «Найти и выделить».
- Открывается окно, в котором нужно указать искомое значение. После этого жмем на кнопку «Найти далее» или
- В первом случае первая ячейка, в которой имеется указанное значение, становится активной.
Во втором случае открывается весь перечень ячеек, содержащих это значение.
Урок: Как сделать поиск в Экселе
Закрепление областей
Удобно при создании БД закрепить ячейки с наименованием записей и полей. При работе с большой базой — это просто необходимое условие. Иначе постоянно придется тратить время на пролистывание листа, чтобы посмотреть, какой строке или столбцу соответствует определенное значение.
- Выделяем ячейку, области сверху и слева от которой нужно закрепить. Она будет располагаться сразу под шапкой и справа от наименований записей.
- Находясь во вкладке «Вид» кликаем по кнопке «Закрепить области», которая расположена в группе инструментов «Окно». В выпадающем списке выбираем значение «Закрепить области».
Теперь наименования полей и записей будут у вас всегда перед глазами, как бы далеко вы не прокручивали лист с данными.
Урок: Как закрепить область в Экселе
Выпадающий список
Для некоторых полей таблицы оптимально будет организовать выпадающий список, чтобы пользователи, добавляя новые записи, могли указывать только определенные параметры. Это актуально, например, для поля
- Создаем дополнительный список. Удобнее всего его будет разместить на другом листе. В нём указываем перечень значений, которые будут появляться в выпадающем списке.
- Выделяем этот список и кликаем по нему правой кнопкой мыши. В появившемся меню выбираем пункт «Присвоить имя…».
- Открывается уже знакомое нам окно. В соответствующем поле присваиваем имя нашему диапазону, согласно условиям, о которых уже шла речь выше.
- Возвращаемся на лист с БД. Выделяем диапазон, к которому будет применяться выпадающий список. Переходим во вкладку «Данные». Жмем на кнопку «Проверка данных»
- Открывается окно проверки видимых значений. В поле «Тип данных» выставляем переключатель в позицию «Список». В поле «Источник» устанавливаем знак «=» и сразу после него без пробела пишем наименование выпадающего списка, которое мы дали ему чуть выше. После этого жмем на кнопку «OK».
Теперь при попытке ввести данные в диапазон, где было установлено ограничение, будет появляться список, в котором можно произвести выбор между четко установленными значениями.
Если же вы попытаетесь написать в этих ячейках произвольные символы, то будет появляться сообщение об ошибке. Вам придется вернутся и внести корректную запись.
Урок: Как сделать выпадающий список в Excel
Конечно, Excel уступает по своим возможностям специализированным программам для создания баз данных. Тем не менее, у него имеется инструментарий, который в большинстве случаев удовлетворит потребности пользователей, желающих создать БД. Учитывая тот факт, что возможности Эксель, в сравнении со специализированными приложениями, обычным юзерам известны намного лучше, то в этом плане у разработки компании Microsoft есть даже некоторые преимущества.
Как импортировать информацию из базы данных
За основу я взял свою базу данных по банковским депозитам:
(на рисунке приведен фрагмент структурированной базы данных, созданной в программе MS Access)
Перед нами стоит задача перенести данные в таблицы MS Excel для последующей обработки и фильтрации.
Импорт информации БД в среду Excel
Воспользуемся функцией импорта в MS Excel. Для работы с внешними источниками данных в Excel предусмотрено достаточно большое количество специальных возможностей. Они сгруппированы на вкладке «Данные». Щелкнем на эту вкладку, в результате чего откроется такая картинка:
Необходимо выбрать требуемый вид импорта данных. В нашем случае нам необходимо осуществить импорт из базы данных MS Access. Этот тип баз данных для пакета MS Office является «родным», поэтому для него предусмотрена специальная кнопка. Нам достаточно кликнуть на нее.
Открывается следующее окно:
В этом окне мы выбираем нужную нам базу данных для импорта из нее информации. Поскольку это окно является стандартным для пакета MS Office, то никаких сложностей с его использованием не предполагаем:
После выбора базы данных откроется такое диалоговое окно со списком элементов, содержащихся внутри базы данных MS Access. Нам необходимо выбрать одну из них, которая и будет размещена на одном из листов Excel.
Выбираем нужную нам таблицу для импорта. Для примера я выбрал таблицу “Физические лица”. Появится диалог выбора способа импорта данных и представления результата на листе Excel. Обратите внимание, что во время импорта, данные можно преобразовать и представить в виде сводной таблицы и даже сводной диаграммы.
Функция «создать подключение» позволяет создавать динамическую связь между базой данных и результатом на листе Excel. То есть при изменении данных в базе, будет меняться и ее отображение на листе.
Несмотря на то, что можно выбрать несколько способов отображения данных в Excel, я выбрал таблицу.
В итоге я получил такую таблицу из базы данных, импортированную из Excel.
Теперь мы можем отфильтровать данные по нужным нам критериям. Ecel представляет массу возможностей для отбора, фильтрации и сортировки данных. Для этого мы необходимо выделить всю область с импортированными только что данными. После этого, заходим на вкладку «Данные» и нажимаем на кнопку “Фильтр”. Эта кнопка включает фильтрацию для выделенной нами области данных. При этом каждая колонка считается индивидуальным фильтром, а каждая строка — единицей данных.
Если в процессе фильтрации, элемент будет отфильтрован (скрыт из отбора или наоборот, включен в отбор), то это действие осуществляется по отношению ко всей строке — она появляется или исчезает из списка. Таким образом появляется возможность, например, отобрать все записи, у которых значение конкретной колонки «26». В результате, все строки, не соответствующие этому параметру будут скрыты.
Разумеется, фильтры работают в режиме отображения, с самими данными ничего не происходит. При отключении фильтра данные «вернутся назад».
О том, что фильтр включен, свидетельствует выпадающий список, появляющийся вверху каждой колонки, включенной в фильтрацию. По умолчанию у каждого фильтра установлен режим «все», то есть отображать все данные.
Кроме функции фильтрации, Excel позволяет нам упорядочить (отсортировать) данные, что позволяет представить их в более удобном виде. Например мы можем отсортировать счета по дате закрытия. При этом сортировка возможна как по возрастанию, так и по убыванию.
Рассмотрим, как работает фильтрация. Для этого нажимаем на фильтр столбца, который мы хотим подвергнуть фильтрации и выбираем нужный нам критерий для отображения счетов. Я выбрал для примера счета, которые еще не закрыты. Обратите внимание, о том, что для данной колонки включена фильтрация данных, свидетельствует соответствующее изображение «фильтр» на кнопке выпадающего списка (см. колонку F)
Так же мы хотим отсортировать счета по нужному нам значению. Допустим по убыванию размера суммы на счете. Для этого воспользуемся функцией сортировки для соответствующей колонки данных. Нам осталось указать в открывшемся меню, как мы хотим упорядочить строки.
В очередной раз заметим, что при выполнении операции сортировки перемещаются строки в целом, а не отдельные ячейки, так как Excel считает, что строка является неразрывной логической единицей данных.
Получаем результат сортировки от максимального к минимальному значению.
Как использовать формулы в Excel | Описание курса | Сведение и группировка статистических данных
Практическая работа «Создание базы данных в Excel»
Практическая работа «Создание базы данных в Excel»
Цель работы: создание базы данных в Excel, которая будет содержать сведения о продажах в продуктовом магазине.
Для этого нам надо будет создать три таблицы: Прайс (в ней будут храниться наименования товаров, их цена), Клиенты (в ней будут храниться данные о клиентах), Продажи (в эту таблицу будут заноситься данные о продажах с учетом сведений из первых двух таблиц). Так же будет создан лист Форма ввода, с помощью которой можно будет заполнять таблицу Продажи, используя уже введенные ранее данные в таблицы Прайс и Клиенты.
Методические рекомендации по выполнению практической работы
Шаг 1. Исходные данные в виде таблиц.
Создаем таблицу Прайс:
Создаем в Excel новый лист с названием Прайс.
Создаем три столбца: Наименование, Категория, Цена. Заполняем 20 строк в созданной таблице по следующему образцу:
Превращаем созданную таблицу в «умную таблицу». Для этого выделяем все заполненные ячейки, нажимаем Главная – Форматировать как таблицу. Из выпавшего списка выбираем тот стиль оформления, который нам понравился.
Далее идем на вкладку Работа с таблицами — Конструктор. В окошке Имя таблицы меняем наименование на Прайс.
Создаем в Excel новый лист с названием Клиенты.
Создаем два столбца: Клиент, Город. Заполняем 20 строк в созданной таблице по следующему образцу:
Аналогично предыдущем листу превращаем созданную таблицу в «умную таблицу». Для этого выделяем все заполненные ячейки, нажимаем Главная – Форматировать как таблицу. Из выпавшего списка выбираем тот стиль оформления, который нам понравился.
Далее идем на вкладку Работа с таблицами — Конструктор. В окошке Имя таблицы меняем наименование на Клиенты.
Создаем в Excel новый лист с названием Продажи.
Создаем пять столбцов: Дата, Товар, Кол-во, Стоимость, Клиент. Не заполняем!!!
Аналогично предыдущем листу превращаем созданную таблицу в «умную таблицу». Для этого выделяем все заполненные ячейки, нажимаем Главная – Форматировать как таблицу. Из выпавшего списка выбираем тот стиль оформления, который нам понравился.
Шаг 2. Создаем форму для ввода данных
Можно вводить данные о продажах непосредственно в таблицу Продажи, но это не всегда удобно и влечет за собой появление ошибок и опечаток из-за «человеческого фактора». Поэтому лучше будет на отдельном листе сделать специальную форму для ввода данных.
Создаем Форму ввода:
Создаем в Excel новый лист с названием Форма ввода.
Оформляем лист следующим образом:
В ячейке Клиент найдем нужное значение из созданной ранее «умной таблицы». Для этого выделяем ячейку, используем команду Данные – Проверка данных:
Откроется диалоговое окно:
В поле Тип данных выбираем Список.
Поставить курсор в появившееся поле Источник данных, перейти на лист Клиенты, выделить диапазон ячеек с фамилиями, нажать ОК. После этого, в появившемся выпадающем списке выбираем любую фамилию.
Аналогичным образом поступаем с ячейкой Товар. В результате произведенных действий Форма ввода должна иметь следующий вид (дата, количество заполняются вручную):
Для того, чтобы подставить цену товара в форму ввода, необходимо воспользоваться функцией ВПР, для этого в ячейке Цена введем следующую формулу:
В скобках первым в кавычках указывается наименование позиции, которая была выбрана в поле Товар. После точки с запятой указывается наименование таблицы, откуда будут подставляться значения (наименование Прайс мы задали в Шаге 1, п. 5). Далее через точку с запятой идет номер столбца в таблице Прайс, где содержится нужный нам параметр.
После нажатия кнопки Enter нужная цена появится автоматически.
В поле Стоимость вводим формулу, для вычисления стоимости данного товара при выбранных цене и количестве.
Шаг 3. Добавляем макрос ввода продаж
После заполнения формы нужно введенные в нее данные добавить в конец таблицы Продажи. Сформируем при помощи простых ссылок строку для добавления прямо под формой (обратите внимание, ячейки формируются в той последовательности, в какой они идут в таблице Продажи, т.е. в ячейке A20 будет ссылка =B3, в ячейке B20 ссылка на =B7 и т.д.):
Теперь создадим макрос, который копирует созданную строку и добавляет его в таблицу Продажи. Для этого нажимаем Разработчик — Visual Basic. Если вкладку Разработчик не видно, то включите ее сначала в настройках Файл — Параметры — Настройка ленты. Поставить галочку напротив меню Разработчик:
После этого откроется окно Microsoft Visual Basic for Applications:
В открывшемся окне редактора 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
Закрываем окно редактора Visual Basic (никаких сохранений это действие не потребует).
Теперь можно добавить к нашей форме кнопку для запуска созданного макроса используя выпадающий список Вставить на вкладке Разработчик:
После того, как вы ее нарисуете, удерживая нажатой левую кнопку мыши, Excel сам спросит вас — какой именно макрос нужно на нее назначить — выбираем наш макрос Add_Sell. Текст на кнопке можно поменять, щелкнув по ней правой кнопкой мыши и выбрав команду Изменить текст.
Теперь после заполнения формы можно просто жать на нашу кнопку, и введенные данные будут автоматически добавляться к таблице Продажи, а затем форма очищается для ввода новой сделки.
Сохранять созданный файл нужно следующим образом: Файл – Сохранить как – Тип файла: Книга Excel с поддержкой макросов.
Как создать базу данных в Excel
Введите данные
Основным форматом для хранения данных в базе данных Excel является таблица. После создания таблицы используйте инструменты данных Excel для поиска, сортировки и фильтрации записей в базе данных для поиска конкретной информации.
Чтобы следовать этому уроку, введите данные, как показано на рисунке выше.
Введите студенческие идентификаторы быстро
- Введите первые два идентификатора, ST348-245 и ST348-246 , в ячейки A5 и A6 соответственно.
- Выделите два идентификатора, чтобы выбрать их.
- Перетащите заливки ручку в ячейку А13 .
Остальные идентификаторы ученика правильно вводятся в ячейки с A6 по A13.
Введите данные правильно
При вводе данных важно убедиться, что они введены правильно. За исключением строки 2 между заголовком электронной таблицы и заголовками столбцов, не оставляйте никаких других пустых строк при вводе данных. Также убедитесь, что вы не оставляете пустых ячеек.
Ошибки данных , вызванные неправильным вводом данных, являются источником многих проблем, связанных с управлением данными. Если данные введены правильно в начале, программа, скорее всего, вернет вам желаемые результаты.
Используйте инструменты базы данных
После того, как вы создали базу данных, используйте инструменты, расположенные под стрелками раскрывающегося списка рядом с каждым именем поля, чтобы отсортировать или отфильтровать ваши данные.
Сортировать данные
- Выберите стрелку раскрывающегося списка рядом с полем Фамилия.
- Выберите Сортировка от А до Я, чтобы отсортировать базу данных по алфавиту.
- После сортировки Грэм Дж. — первая запись в таблице, а Уилсон Р. — последняя.
Фильтровать данные
- Выберите стрелку раскрывающегося списка рядом с полем «Программа».
- Установите флажок рядом с « Выбрать все», чтобы снять все флажки.
- Установите флажок рядом с Business, чтобы добавить флажок в поле.
- Выберите ОК .
- Видны только два студента, Дж. Томпсон и Ф. Смит, потому что они — единственные два студента, зарегистрированные в деловой программе.
- Чтобы показать все записи, выберите стрелку раскрывающегося списка рядом с полем «Программа» и выберите « Очистить фильтр» из «Программы» .
Функции базы данных
Синтаксис : Dfunction (Database_arr, Field_str | num, Criteria_arr)
Где функция D является одним из следующих:
- DAVERAGE
- DCOUNT
- DCOUNTA
- DGET
- DMAX
- DMIN
- DPRODUCT
- DSTDEV
- DSTDEVP
- DSUM
- DVAR
- DVARP
Тип : База данных
Функции базы данных особенно удобны, когда Google Sheets используется для поддержки структурированных данных, таких как база данных. Каждая функция базы данных, Dfunction, вычисляет соответствующую функцию на подмножестве диапазона ячеек, рассматриваемого как таблица базы данных. Функции базы данных принимают три аргумента:
- Database_arr — это диапазон, встроенный массив или массив, сгенерированный выражением массива. Он построен таким образом, что каждая строка после строки 1 является записью базы данных, а каждый столбец — полем базы данных . Строка 1 содержит метки для каждого поля.
- Field_str | num указывает, какой столбец (поле) содержит значения для усреднения. Это может быть выражено как имя поля (текстовая строка) или номер столбца, где крайний левый столбец будет представлен как 1.
- Criteria_arr — это диапазон, встроенный массив или массив, сгенерированный выражением массива. Он структурирован таким образом, что первая строка содержит имена полей, к которым будет применяться критерий (критерии), а последующие строки содержат условные тесты.
Первая строка в Criteria определяет имена полей. Каждая вторая строка в Критериях представляет фильтр, который представляет собой набор ограничений для соответствующих полей. Ограничения описываются с использованием нотации Query-by-Example и включают в себя значение для сопоставления или оператор сравнения, за которым следует значение сравнения. Примеры ограничений: «Шоколад», «42», «> = 42» и «<> 42». Пустая ячейка означает отсутствие ограничений на соответствующее поле.
Фильтр соответствует строке базы данных, если все ограничения фильтра (ограничения в строке фильтра) выполнены. Строка (запись) базы данных удовлетворяет критериям, если хотя бы один фильтр соответствует ей. Имя поля может появляться более одного раза в диапазоне критериев, чтобы разрешить несколько ограничений, которые применяются одновременно (например, температура> = 65 и температура <= 82).
DGET — единственная функция базы данных, которая не агрегирует значения. DGET возвращает значение поля, указанного во втором аргументе (аналогично VLOOKUP ), только когда точно одна запись соответствует критерию; в противном случае возвращается ошибка, указывающая, что совпадений нет или несколько совпадений.
База данных изображений в Excel
Постановка задачи
К нам обратилась компания, которая занимается продажей обложек на различные документы. В прочем это могла быть любая компания, которая ведет номенклатуру своих товаров в Excel. Проблема №1 заключалась в том, что фотографии к товарам постоянно перемещались («съезжали») при редактировании описания товаров, изменении ширины столбцов и так далее.
Выглядело это примерно так:
Проблема №2 заключалась в том, что некоторые изображения были настолько мелкие, что с ними было не удобно работать. Хотелось, чтобы при клике на изображение оно увеличивалось,а после возвращалось в исходное состояние и при этом чтобы ничего «не съезжало«.
Как мы решали проблему
Вставка новых изображений
Во первых, чтобы исключить необходимость постоянного «подгона» размеров фотографий под ячейки, мы сделали функционал по добавлению новых изображений в базу Excel.
При нажатии на кнопку открывалось диалоговое окно с выбором файла и в результате изображение добавлялось в заданную строку. При этом оно форматировалось и подстраивалось под стандартные размеры ячейки.
Увеличение размеров изображений по клику
Далее разбираемся с увеличением изображений. Задача решается путем назначения макроса, который срабатывает при клике на изображением. Макрос копирует изображение и увеличивает его размеры, при повторном клике изображение плавно уменьшается и дубликат удаляется. Выглядит это так:
Конечно качество изображений не повышается, чтобы увеличенное изображение было качественным, необходимо изначально вставлять фото с более высоким разрешением.
Прочие улучшения для организации базы фотографий
Глобальные задачи решены, теперь база стала намного функциональней, чем исходная. Можно заняться дополнительным функционалом, который мы для удобства разместили на ленте меню.
Выровнять все фото — проходит по всем изображениям и вписывает их по размерам ячеек. Таким образом, все фотографии выравниваются по размерам и не выходят за границы ячеек.
Обновить нумерацию — так как изображение может быть вставлено в середину базы, изменить нумерацию также теперь не составляет проблемы.
Обновить группировку — Изображения сортируются по размерам и в случае необходимости можно скрывать те или иные разделы. Кнопка служит чтобы исправить группировку разделов, если ее случайно нарушил оператор.
Выровнять строки — когда строки базы одинаково выровнены по высоте, то база приобретает более наглядный вид. Функционал автоматически выравнивает высоту ячеек в том случае если он был нарушен.
Вывод
Казалось бы Excel не предназначен для хранение изображений, однако вот таким способом можно организовать работу. Если вы столкнулись с подобной задачи, то свяжитесь с нами, мы будем рады помочь.
Поиск в базе данных Excel — Трюки и приемы в Microsoft Excel
Представим на минуту, что наш журнал контроля изменений содержит много страниц, а количество записей столь велико, что об удобстве поиска интересующей нас информации вообще не приходится говорить. Как, например, узнать, сколько в журнале контроля содержится активных запросов на внесение изменений, не прибегая к физическому просмотру каждой строки (записи) этого журнала? Excel может помочь нам в решении этой задачи. Для этого мы можем воспользоваться встроенной функцией DCOUNTA (БСЧЁТА — в русифицированной версии Excel).
Во-первых, нам придется освежить в памяти фундаментальные знания о базах данных Excel. Например то, что база в Excel состоит из данных, представленных в табличном формате. Каждый столбец такой таблицы представляет собой одно из полей данных, а каждая строка является отдельной записью базы данных. Основные элементы любой базы данных показаны на примере журнала контроля изменений для проекта Grant St. Move.
В данном случае строка заголовков журнала контроля изменений охватывает ячейки с А14 по Н14. Эта строка содержит названия полей (или столбцов) для каждого из элементов данных. Строки 15, 16 и 17 содержат записи базы данных. Каждая строка представляет собой одну запись. Помните: между записями базы данных не должно быть пустых строк!
Воспользуемся встроенной функцией Excel DCOUNTA (БСЧЁТА) для поиска интересующих нас данных в этой базе. Начнем с перехода на вкладку Formulas (Формулы). Как видите, в группе Function Library (Библиотека функций) этой вкладки не предусмотрена кнопка для активизации перечня встроенных функций, предназначенных для работы с базами данных. Чтобы получить доступ к функциям этой категории, щелкните на кнопке Function Wizard (Вставить функцию) (как показано далее, на рис. 2). На экране появится диалоговое окно Function Wizard (Мастер функций). Из раскрывающегося списка Or Select a Category (Категория) выберите элемент Database (Работа с базой данных), а из списка Select a function (Выберите функцию) — элемент DCOUNTA (БСЧЁТА).
Чтобы воспользоваться функцией DCOUNTA, нам нужно сформировать две строки, которые будут выполнять роль критериев поиска в базе данных. Допустим, нам требуется подсчитать в поле Disposition (Принятое решение) количество записей, для которых указан статус Active (Реализуется). Для этого нам понадобится следующее.
- Совокупность из двух строк. Первая из этих строк содержит точную копию информации в строке заголовка, а вторая строка — информацию о критериях поиска в базе данных.
- Формула DCOUNTA (БСЧЁТА).
Вы заметите, что мы уже фактически создали три отдельных диапазона ячеек с критериями поиска в базе данных: А6:Н7, А8:Н9 и А10:Н11. Каждый из них состоит из двух строк реквизитов, которые выполняют роль наших критериев поиска в базе данных (как описано в приведенных выше пунктах 1 и 2). Мы создали три отдельные пары критериев поиска в базе данных, поскольку хотим одновременно вести поиск трех элементов информации.
Обратите внимание и на то, что у нас есть три строки с формулами: А25:В25, А26:В26 и А27:В27. Синтаксис функции DCOUNTA (ячейка А25) отображен в строке формул. Этот механизм действует следующим образом. Строки критериев говорят Excel о том, какую информацию вы хотите отыскать. В данном примере мы пытаемся найти текстовую информацию. В первых строках критериев, А6:Н7, мы ищем слово «Denied» (Отвергнут). Ячейка F7 содержит интересующую нас текстовую информацию (Отвергнут). Однако поскольку мы хотим найти текстовую информацию, то должны использовать два знака равенства, а именно: =»=Отвергнут».
Нам нужно ввести два знака равенства, заключив с двух сторон в кавычки второй знак равенства и собственно текст. Если бы мы ввели «Отвергнут» с одним знаком равенства, то тем самым как бы попросили Excel поместить содержимое диапазона под именем «отвергнут» в эту ячейку. Регистр клавиатуры (верхний или нижний) в данном случае не имеет значения, если набранный вами текст в точности соответствует тексту, который вы хотите найти. При поиске численной информации вам нужно было бы ввести только один знак равенства и число, которое вы хотите найти (=16). В этом случае не требуются ни кавычки, ни двойные знаки равенства.
Теперь нам нужно ввести формулу DCOUNTA (БСЧЁТА). Соответствующая формула в ячейке А25, =DCOUNTA(А14:Н20,"Принятое решение",А6:Н7), говорит следующее: «Войти в базу данных, состоящую из ячеек от А14 до Н20, и найти в поле «Принятое решение» требуемую информацию. В качестве критериев поиска использовать строки от А6 до Н7. Мне нужно подсчитать, сколько строк соответствует указанному критерию, и вывести на экран полученный результат».
В ячейке А25 Excel отображает число 1, поскольку удалось найти только одну запись, которая соответствует указанному критерию поиска (слово «Отвергнут» в столбце «Принятое решение»). Мы ввели «=Отвергнут» в ячейку В25, «=Утвержден» — в ячейку В26 и «=Отменен» — в ячейку В27, чтобы было понятно, какая формула в каком случае использовалась. Обратите внимание: если решение, принятое по запросу на внесение изменения и указанное в строке 15, заменить на «Утвержден», тогда количество записей, в поле «Принятое решение» которых указано «Отвергнут», стало бы равным нулю, тогда как количество записей, в поле «Принятое решение» которых указано «Утвержден», увеличилось бы до двух.
Несколько замечаний по поводу использования функции DCOUNTA
Пользуясь функцией DCOUNTA (БСЧЁТА), а также другими функциями баз данных, следует помнить несколько важных вещей.
- Во-первых, вам нет необходимости использовать всю строку заголовков в качестве критериев. Мы сделали это для большей ясности, однако в рассмотренном нами примере вы могли бы запросто использовать в качестве критериев ячейки F6:F7.
- Во-вторых, критерии поиска, база данных и формула DCOUNTA (БСЧЁТА) вовсе необязательно должны находиться на одном и том же рабочем листе. Например, сама формула DCOUNTA (БСЧЁТА) может находиться на одном рабочем листе, а ссылки на эту формулу — на другом.
- В-третьих, вы могли бы связать эту электронную таблицу со списком в SharePoint. Это дало бы вам возможность создавать фильтры, группы и специализированные представления для решения той же самой задачи без написания каких-либо формул.
Функции Excel
Для отображения списка встроенных функций определенной категории активизируйте вкладку Formulas (Формулы), которая расположена на ленте Excel. Затем в группе Function Library (Библиотека функций) щелкните на соответствующей кнопке. Например, для отображения списка функций, предназначенных для работы с текстовыми фрагментами, щелкните на кнопке Text (Текстовые), как показано на рис. 1.
Рис. 1. Для отображения списка встроенных функций, предназначенных для работы с текстовыми фрагментами, достаточно щелкнуть мышью на кнопке Text (Текстовые)
В качестве альтернативы можно щелкнуть на кнопке Function Wizard (Вставить функцию). Это первая из кнопок группы Function Library (Библиотека функций) вкладки Function (см. рис. 1). В результате на экране появится диалоговое окно Insert Function (Мастер функций).
Мастер функций программы Excel особенно удобен тем, что в его первом диалоговом окне предусмотрена возможность поиска интересующей вас функции по ключевому слову. (Отметим, что мастер функций Excel 2007/2010/2013 ничем не отличается от одноименного программного средства предыдущих версий программы.) Обратите внимание на то, что команда Function Wizard (Вставить функцию) также предусмотрена в нижней части каждого меню, которое появляется на экране после щелчка мышью на любой из кнопок группы Function Library (Библиотека функций) вкладки Function (Функции) (см. рис. 2). Первое диалоговое окно мастера функций показано на рис. 1.
Рис. 2. Первое диалоговое окно мастера функций
В программе Excel имеется достаточно много функций для работы с базами данных, которые в качестве критериев выборки используют введенные вами данные в ячейках рабочего листа. Чтобы получить более подробную информацию о функции DCOUNTA (БСЧЁТА), откройте окно справочной системы Excel и выполните поиск по названию этой функции. В результате ваших действий появится очередная страница справочной системы с перечнем ссылок на описания функций, предназначенных для работы с базами данных. Щелкните на ссылке с названием интересующей вас функции, чтобы открыть следующую страницу справочной системы. На этой странице будет приведена подробная информация о функции и примеры ее применения.
Теперь, когда вы знаете, как составлять отчеты по запросам на внесение изменений в проект и решениям, принятым по этим запросам, нам следует рассмотреть вопрос о том, как оценить возможное влияние этих изменений.
Использование функций баз данных Excel | Piter Melnikov
MS Excel имеет 12 специальных функций, предназначенных для работы со списками. Эти функции находятся в категории функций Работа с базой данных Мастера функций.
Каждая из этих функций возвращает информацию об элементах списка, которые удовлетворяют определенным условиям. Все они являются аналогами статистических функций с той разницей, что функции баз данных работают только с элементами диапазона, которые удовлетворяют заданным условиям.
Синтаксис всех функций баз данных следующий:
ИМЯ_ФУНКЦИИ(база-данных; поле; критерий), где
— параметр база данных служит для указания адресов диапазона, содержащего список;
— параметр поле задает столбец, в котором производятся вычисления (суммирование, усреднение, подсчет количества и т.д.). В качестве этого параметра можно использовать заголовок столбца, заключенного в кавычки, или номер столбца;
— параметр критерий задает диапазон условий, правила составления которых такие же, как для расширенного фильтра.
Перечень некоторых функций Excel для работы с базой данных, их назначение приведен в таблице.
Чтобы продемонстрировать технологию работы с функциями баз данных, рассмотрим пример.
Пример. Пусть требуется определить количество расходных операций в период с 12 января по 24 января для номеров счетов 4 — 15 (фрагмент списка (БД) приведен на рис. 1).
Рис. 1.Рис. 1.
Решение
1. В свободном диапазоне ячеек рабочего листа запишем выражение условия отбора (рис. 2)
Рис. 2.Рис. 2.
2. Включим Мастер функций и в категории Работа с базой данных включим функцию БСЧЕТ.
3. Заполним поля аргументов функции, как показано на рис. 2.
4. Кликнем на кнопке ОК, в ячейке, где помещена функция( в нашем случае E6) будет выведен результат 13.
Упражнение
Попробуйте проверить свои навыки по работе в Excel.
Откройте страницу по ссылке.
После открытия страницы кликните на кнопке Редактировать.
После завершения загрузки на Яндекс Диск откроется рабочая книга, на Листе 1 которой есть задания.
Выполните предлагаемые задания, проверяя корректность ответов по графе Правильный результат.
При решении задач используйте информацию из статей канала по тематике Excel.
в Excel | Пошаговое руководство по созданию базы данных в Excel
Excel представляет собой комбинацию строк и столбцов, и эти строки и столбцы хранят наши данные, которые в других терминах называются записями, поскольку Excel является наиболее распространенным инструментом, который мы храним данные в Excel, что делает его базой данных, когда мы помещаем данные в Excel в какой-либо форме таблиц в строках и столбцах и даем таблице имя, которое является базой данных в Excel, мы также можем импортировать данные из других источников в Excel с учетом формата данных соответствует формату Excel.
Создание базы данных Excel
Наличие данных в Excel упростит вам жизнь, потому что Excel — такой мощный инструмент, с помощью которого мы можем постоянно играть с данными. Если вы храните данные в каких-либо других источниках, вы можете неправильно получить все формулы, формат даты и времени. Надеюсь, вы испытали это на своей повседневной работе. Очень важно иметь данные на правильной платформе базы данных. Наличие данных в Excel имеет свои плюсы и минусы.Однако, если вы являетесь постоянным пользователем Excel, вам будет намного проще работать с Excel. В этой статье я покажу вам способ создания базы данных в Excel.
Вы можете свободно использовать это изображение на своем веб-сайте, в шаблонах и т. Д. Пожалуйста, предоставьте нам ссылку с указанием авторства Ссылка на статью с гиперссылкой
Например:
Источник: База данных в Excel (wallstreetmojo.com)
Как создать базу данных в Excel?
Мы не видим ни в одной из школ колледжей, которые учат нас преуспевать в качестве программного обеспечения для наших академиков.Какими бы ни были бизнес-модели, мы изучаем теорию, пока не присоединимся к корпоративной компании.
Самая большая проблема с этими теоретическими знаниями заключается в том, что они не поддерживают примеры из жизни в реальном времени. Не о чем беспокоиться; мы проведем вас через весь процесс создания базы данных в Excel.
Нам необходимо тщательно разработать рабочий лист Excel, чтобы иметь правильные данные в формате базы данных. Выполните следующие шаги, чтобы создать базу данных в Excel.
- Убедитесь, что у вас есть все необходимые столбцы и правильно назовите каждый заголовок.
- Когда заголовки таблицы данных очищены, мы можем легко начать ввод данных сразу под заголовками соответствующих столбцов.
В терминологии базы данных строки называются записями, и столбцы называются полями.
- При вводе данных нельзя оставить ни одной строки пустой. Допустим, вы ввели заголовки в первую строку, и если вы начнете вводить данные из третьей строки, оставив пустую строку 2 и , то вы уйдете.
Не только первая или вторая строка, но вы также не можете оставить какую-либо строку пустой после ввода определенных данных в поле базы данных.
- Как я уже сказал, каждый столбец в базе данных называется Поля . Точно так же у вас не может быть пустого поля между данными.
Поля нужно вводить по очереди. Строго запрещено иметь пробел даже в один столбец или поле.Причина, по которой так много внимания уделяется отсутствию пустой записи или поля, заключается в том, что, когда данные необходимо экспортировать в другое программное обеспечение или в Интернет, как только программное обеспечение видит пустую запись или поле, оно предполагает, что конец данных, и он может не учитывать полные данные.
- Тщательно заполните все данные.
На изображении выше у меня есть данные от строки 1 до строки 5001. - Последнее, что вам нужно сделать, это преобразовать эти данные в таблицу Excel. В Excel таблицы представляют собой диапазон данных в строках и столбцах, и они расширяются, когда новые данные вставляются в диапазон в любой новой строке или столбце таблицы. Чтобы использовать таблицу, щелкните таблицу и выберите диапазон данных. Читать дальше. Выбрав данные, нажмите Ctrl + T.
- Здесь вам нужно убедиться, что в поле «Мои данные есть заголовок» установлен флажок и диапазон выбран правильно.
- Щелкните OK, чтобы завершить создание таблицы. Теперь у нас есть такая таблица.
- Дайте собственное имя таблице под вкладкой дизайна таблицы.
- Поскольку мы создали таблицу, всякий раз, когда вы вводите данные после последнего столбца, она будет расширяться автоматически.
Итак, база данных готова. Ознакомьтесь с нижеприведенными плюсами и минусами, чтобы иметь хорошие руки в своей базе данных.
Что нужно помнить при создании базы данных в Excel
Рекомендуемые статьи
Это руководство по базе данных в Excel. Здесь мы обсудим, как создать базу данных в Excel с примерами и загружаемыми шаблонами Excel. Вы также можете посмотреть эти полезные функции в excel —
Пакет All in One Excel VBA (35 курсов с проектами)- 35+ курсов
- 120+ часов
- Полный доступ на весь срок службы
- Акт выполненных работ
Базы данных Excel: Создание реляционных таблиц
Excel обладает огромными возможностями базы данных.Создание реляционной базы данных начинается с главной таблицы, которая связывает ее с подчиненными, называемыми (что неудобно) подчиненными, дочерними или подробными таблицами.
Прежде чем мы начнем, мы предоставили вам образец учебного пособия, который вы можете использовать, чтобы следовать нашим инструкциям и практиковаться на каждом этапе. Просто щелкните стрелку или ссылку ниже, чтобы начать загрузку.
скачатьЭто рабочая книга с несколькими электронными таблицами, содержимое которых можно использовать для отработки задач Excel в реляционных базах данных, отчетах и сводных таблицах.JD Sartain
Как работают реляционные базы данных
Организационная структураExcel хорошо подходит для работы с базами данных. База данных — это набор связанных элементов (электронная таблица), которые при связывании создают одну запись (строку) внутри группы из нескольких записей (таблица).
Одна электронная таблица сама по себе является базой данных, но не реляционной. Реляционная база данных представляет собой комбинацию главной электронной таблицы и всех ее подчиненных таблиц или электронных таблиц.
Например, ваши водительские права — это отдельная запись в базе данных в виде плоских файлов, которая хранится на компьютере в Департаменте транспортных средств (DMV).Ваша лицензия содержит ваше имя, пол, рост, вес, цвет глаз и волос, адрес, дату рождения, выдачу лицензии и дату истечения срока действия, класс лицензии.
Поскольку у нескольких человек может быть одно и то же имя, адрес, пол и описание (например, отец и сын), все водительские права имеют номера. Этот уникальный номер называется ключевым полем, которое используется для подключения базы данных плоских файлов к другим связанным базам данных (называемым реляционными базами данных).
JD Sartain / PC WorldБазовая база данных электронных таблиц в виде плоских файлов
Эта основная база данных Master (в этом примере) содержит всю информацию, имеющуюся в ваших водительских правах.Одна или несколько связанных баз данных, называемых подчиненными, дочерними или подробными базами данных, содержат дополнительную информацию, относящуюся к отдельным драйверам (идентифицируемую по уникальному ключевому полю «Номер лицензии»). Например, одна подчиненная база данных может содержать все нарушения вождения отдельных лиц, а другая может содержать все предыдущие адреса отдельных водителей. Этот тип реляционной базы данных называется отношением «один ко многим», что означает, что каждый драйвер может иметь несколько нарушений или несколько адресов, но базы данных нарушений и / или адресов не могут иметь несколько драйверов.
Другие типы отношений включают «один к одному» и «многие ко многим». В базе данных продаж, где клиенты покупают продукты, некоторые клиенты могут получать специальные скидки (например, оптовая, розничная, без налогов и т. Д.), Для чего потребуется база данных о скидках. Это будут отношения один-к-одному с базой данных клиентов. Когда клиент может приобрести много продуктов, а один и тот же продукт может быть приобретен многими клиентами, у вас есть отношения «многие ко многим».
JD Sartain / PC WorldГлавный и подчиненные устройства реляционной базы данных
Кому нужна реляционная база данных
Есть много причин для создания реляционных баз данных.Самым очевидным является то, что вы не повторяете одни и те же данные в каждой таблице в своей книге. Например, повторный ввод всех имен и дат рождения водителя из главной базы данных в несколько подчиненных баз данных является избыточным, трудоемким и ресурсоемким. И даже если вы скопируете информацию с одного на другой, он все равно будет использовать ненужное дисковое пространство и память, что все замедлит. Кроме того, имя и дата рождения на самом деле не уникальны. Например, вероятно, существуют сотни Джека Тейлора и Джона Смита, и у некоторых из них вполне может быть одна и та же дата рождения.Но если вы просто используете номер лицензии, вы не сможете идентифицировать драйверы, поэтому вам придется (на всякий случай) использовать все три поля (по крайней мере).
Наиболее важной причиной для реляционных баз данных является создание запросов и фильтров, которые могут извлекать конкретную информацию и распечатывать отчеты. Допустим, вашему начальнику нужен отчет, в котором все жители округа Санта-Роса, штат Флорида, имеют штрафы на сумму более 300 долларов; или сколько водителей в Пенсаколе продлили лицензию в декабре? Без емкости реляционной базы данных вам пришлось бы вырезать и вставить эту информацию вместе из трех или более таблиц в четвертую таблицу, а затем надеяться — когда вы все закончите — ваш босс не объявляет: «Я сказал округ Сарасота, а не Округ Санта-Роза.”
JD Sartain / PC WorldОтчеты реляционной базы данных — штрафы
Создание реляционной базы данных
Давайте создадим реляционную базу данных с несколькими отдельными таблицами или электронными таблицами.
Создание главной таблицы
1. Используйте те же поля, что и на первом снимке экрана выше («Базовая база данных электронных таблиц с плоскими файлами»), а затем заполните 12-15 записей своими данными (или используйте наши). Эти поля: Номер лицензии, Имя, Адрес, Город, Штат, Почтовый индекс, Дата рождения, Срок действия и Пол.
2. По завершении выделите всю таблицу, включая заголовки; то есть с A1 по I15 (A1: I15). Помните, что CTRL + A выделяет всю таблицу одним движением, пока ваш курсор находится где-нибудь / где-нибудь внутри диапазона таблицы.
3. Выберите вкладку и кнопку: Вставить > Таблица
4. Если диапазон выделен (что должно быть), адрес диапазона отображается в поле с именем: Где находятся данные для вашей таблицы в диалоговом окне Create Table .Установите флажок: Моя таблица имеет заголовки , затем нажмите ОК .
JD Sartain / PC WorldСоздать / вставить главную таблицу
5. Как только это диалоговое окно закроется, ОБЯЗАТЕЛЬНО укажите имя вашей таблицы в поле Имя таблицы (верхний левый угол) в группе «Свойства» (на текущем экране). Введите MASTER, затем нажмите клавишу возврата.
6. Обратите внимание, что в таблице теперь отображаются стрелки вниз рядом с каждым именем поля. Щелкните одну из стрелок вниз (e.g., City) и обратите внимание на появившееся диалоговое окно меню. Это поле содержит параметры сортировки, которые включают фильтры, которые позволяют отображать конкретную информацию на основе выбранного фильтра. Например, если вы снимите отметку с поля Select All , а затем установите только те поля, в которых написано Miami и Destin , Excel отфильтрует все города, ЗА ИСКЛЮЧЕНИЕМ Майами и Дестина.
ПРИМЕЧАНИЕ: Не паникуйте, когда все оставшиеся записи исчезнут. Они не исчезли, просто спрятаны.Чтобы просмотреть их снова, нажмите стрелку вниз рядом с полем «Город», затем нажмите кнопку « Все », и они сразу же снова станут видимыми.
СОВЕТ: Щелкните параметр Текстовый фильтр s в раскрывающемся меню «Фильтры», чтобы выбрать параметры фильтрации из всплывающего списка (в отличие от простого сопоставления полей из списка флажков). Варианты: «Равно», «Не равно», «Начинается с», «Заканчивается на», «Содержит», «Не содержит», «Пользовательский фильтр».
7. Не забудьте изменить вкладку (которая говорит Sheet1) внизу электронной таблицы на Master .Дважды щелкните вкладку электронной таблицы, чтобы быстро изменить имя.
JD Sartain / PC WorldИспользуйте фильтры для извлечения определенных данных из таблицы электронной таблицы
Подчиненные столы
1. Затем создайте ведомые таблицы. Нажмите + (плюс) на панели навигации, чтобы добавить две дополнительные таблицы. Назовите одно из этих нарушений и другие адреса.
2. Скопируйте столбец A (Номера лицензий) в столбец A в обеих других таблицах. Отрегулируйте ширину столбца до 11.
ПРИМЕЧАНИЕ: Помните, что таблица Slave в этом примере является таблицей «один ко многим», что означает, что для каждого драйвера существует несколько записей, поэтому поле ключа (номер лицензии) может — и будет — повторяться много раз. Мы копируем его в таблицы Slave, чтобы вам не приходилось повторно вводить данные. Для каждой многократной записи просто повторно скопируйте номер лицензии, прежде чем вводить данные в остальную часть каждой записи.
3. Используйте те же поля, что и на следующем снимке экрана «Реляционная база данных: подчиненная таблица нарушений» ниже, а затем введите свои собственные данные (или используйте наши).Эти поля: номер лицензии (в колонке А, конечно), Тип нарушения, нарушение Фи, нарушение Дата, дата истечения срока, плата Оплаченный Дата
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 не является базой данных
Так случилось снова.16000 тестов COVID-19 в Англии потерялись, и виноват MS Excel. Причина? На него полагались огромные объемы данных, что приводило к большим проблемам.
Проработав более 20 лет в качестве консультанта и разработчика системы, я определенно видел много креативных и иногда, откровенно говоря, опасных для жизни решений, в которых электронная таблица неконтролируемо превращалась в критически важную систему для бизнеса или документации (и с помощью «творческих решений», Я действительно имел ввиду страшилки).
Excel — популярный инструмент, и не зря.Это гибкий инструмент, с которым часто можно очень быстро приступить к выполнению расчетов и созданию диаграмм. Кроме того, благодаря интеграции с Sharepoint, Excel можно быстро настроить как многопользовательскую среду.
Но, конечно, есть и недостатки.
Некоторые из них могут быть связаны с техническими проблемами, как в начальном примере с утерянными тестовыми данными COVID-19, где, по-видимому, текстовый файл был импортирован в Excel, но с большим количеством строк / записей, чем можно было бы обработать в электронной таблице, что привело к потере записи.
Опытные и разбирающиеся в Excel пользователи могут хорошо знать об этих ограничениях, но когда вы спешите (и находитесь в кризисном режиме), об этом легко можно забыть. И непросто узнать точные ограничения, которые у вас есть в то время, поскольку они также могут зависеть от версии. Даже если бы ограничение было обнаружено вовремя, необходимо было найти какое-то обходное решение. Может быть, разбить таблицу на несколько листов?
Что такое «настоящая база данных»?
В этом случае на самом деле кажется, что Excel использовался в качестве замены «настоящей базы данных», и во многих случаях это может быть довольно плохой идеей.Конечно, когда вы обрабатываете более миллиона записей, и особенно, когда это данные, связанные с серьезными проблемами со здоровьем. Неудивительно, однако, что Excel можно считать настоящей базой данных: Электронная таблица очень похожа на базу данных для пользователя , не имеющего отношения к базе данных.
Под «реальной базой данных» я подразумеваю такие системы, как PostgreSQL, SQL Server, Oracle, или даже более легкие системы, такие как SQLite. Эти системы ориентированы на хранение больших объемов данных в целостности.Это означает, что вы можете установить правила в отношении ваших ценных данных. В случае COVID-19 это может означать, что один и тот же тест не регистрируется дважды.
Однако загрузка «реальной базы данных» с данными может быть сложной задачей, поскольку существует множество различных способов импорта данных. И, конечно же, автоматизация непрерывного обновления базы данных может стать настоящей проблемой.
Excel и базы данных могут работать вместе
Существуют эффективные способы настройки автоматизированных рабочих процессов для этих сценариев баз данных.Лучшее решение — часто использовать правильные инструменты для правильной цели, и Excel по-прежнему может быть частью этого решения. Хороший пример можно найти в Европейском управлении по окружающей среде, где эти сценарии были тщательно продуманы после многолетнего опыта. Они регулярно собирают экологические данные из большого числа стран-членов. Г-н Ян Блики, руководитель группы по управлению данными, объясняет:
«Excel по-прежнему является самым простым средством ввода среди пользователей, не работающих с базами данных.Трудно заменить, если пользователи привыкли к этому формату. В нашем случае мы просим доставить несколько файлов, используя один и тот же шаблон: файлы Excel, в которых хранится только часть данных (например, по больнице и по дням). Затем мы используем центральную службу, которая объединяет все эти ежедневные результаты в единую базу данных с помощью одного инструмента ETL. Чтобы упростить использование такой базы данных, можно связать инструмент бизнес-аналитики, чтобы те же пользователи по-прежнему имели возможность производить обзор всех результатов ».
Здесь есть несколько действительно хороших моментов:
- Единая база данных помогает сохранить целостность ваших данных.
- Возможности автоматизации инструмента ETL, такого как FME, могут включать правила QA, которые отправляют предупреждения, когда происходит что-то подозрительное. Но это также упрощает поддержание базы данных в актуальном состоянии.
- Как отмечает Ян, использование бизнес-аналитики вместе с базой данных — отличный способ предоставить пользователям обратную связь и понимание.
Итак, как инструмент ETL, такой как FME, может обрабатывать валидацию и контроль качества в таком процессе? Думайте о базе данных как о безопасном банковском хранилище. Чтобы получать (и отправлять) данные, у него должны быть правильные учетные данные.FME — это система безопасности, проверяющая, что ваши данные содержат правильные учетные данные, и, если нет, предупреждает вас о том, что происходит что-то подозрительное.
Несколько примеров проверок, которые вы можете включить:
- На листе Excel больше определенного количества строк? Это могло бы помочь предотвратить инцидент с COVID, упомянутый в начале.
- Есть ли повторяющиеся записи?
- Находятся ли данные-значения в разумных пределах?
- Все ли поля имеют значения?
Список можно продолжить.Одним из самых больших преимуществ автоматизированного процесса является то, что вы можете постоянно добавлять дополнительные проверки по мере развития ситуации! Со временем система безопасности становится сильнее.
Не обвиняйте отдельные инструменты
Подводя итог, я честно считаю, что обвинять недостатки электронной таблицы в несчастных случаях, как в случае с тестами на COVID-19, слишком легко и позволяет избежать основной проблемы. Скорее всего, инструмент использовался не для того, для чего он предназначен. И самое главное: Любой владелец информации должен принимать меры, чтобы относиться к своим данным как к ценному ресурсу, которым они действительно являются!
Хотите узнать больше об Excel? Посетите веб-семинар «5 способов, которыми FME может улучшить ваши данные Excel», организованный соучредителем Safe Software Дейлом Лутцем.
О данных Базы данных ExcelКак создать соединение Excel с базой данных SQL
Microsoft Excel может быть мощным инструментом для оценки данных SQL. В Excel можно создать соединение для прямой ссылки на конкретную базу данных, отфильтрованную в соответствии с вашими требованиями.Это позволяет вам сообщать данные SQL, прикреплять таблицу данных в Excel, создавать сводную таблицу и лучше управлять вашими данными SQL.
Это соединение должно быть подтверждено с помощью входа в систему SQL, чтобы обеспечить безопасность содержимого ваших баз данных. Соединение можно обновить для обновления содержимого из базы данных SQL.
Как создать соединение с Excel- Откройте Microsoft Excel
- Выберите вкладку «Данные»
- Нажмите «Получить данные»
- Выберите «Из базы данных»
- Выбрать из базы данных SQL Server
- Введите имя сервера SQL
- При желании вы можете ввести здесь имя базы данных, если оно вам известно.
- В противном случае вы сможете выбрать базу данных на следующем этапе.
- Определите учетные данные, которые вы собираетесь использовать.
- «Использовать мои текущие учетные данные» по умолчанию. Это будет использовать ваш логин Windows, который вы использовали для подключения к Windows.
- «Использовать альтернативные учетные данные» использует другую учетную запись Windows, отличную от текущей.
- Выберите База данных слева, чтобы использовать учетные данные базы данных SQL.
- Выберите учетную запись Microsoft слева, чтобы использовать свой логин Microsoft365.
- Нажмите Connect
- Выберите базу данных и таблицу, которую вы хотите использовать для запроса.
- Вы можете использовать строку поиска для поиска таблицы
- Вы можете установить флажок «Выбрать несколько элементов», чтобы отобразить более одной таблицы.
- Теперь вы можете выбрать 1) Как вы хотите загрузить данные; либо «Загрузить», который помещает данные в таблицу Excel, либо «Загрузить в», который позволяет загружать данные в виде запроса, а не таблицы.«Загрузить в» — для более сложных отчетов. 2) Вы можете «Преобразовать данные»; это открывает редактор Microsoft Power Query Editor и позволяет добавлять поля, удалять поля, изменять типы данных и т. Д.
- После нажатия на «Загрузить» данные загружаются в таблицу Excel.
Чтобы обновить соединение с Excel:
- Выберите вкладку «Данные»
- Выбрать Обновить
- Обновить: обновит текущий запрос, в котором вы находитесь.
- Обновить все: обновит все запросы в книге.
Обратитесь в ArcherPoint, чтобы получить помощь по всем вопросам, связанным с Microsoft Dynamics 365 Business Central и отчетностью. Мы здесь, чтобы помочь!
Почему Excel не является базой данных продуктов
Microsoft Excel — это универсальная программа для работы с электронными таблицами с широким спектром приложений, которую множество организаций используют каждый день. Но является ли Excel системой управления базами данных, которую компании должны использовать для разработки или отслеживания продуктов? Проще говоря: нет.
Вот шесть причин, по которым использование Excel в качестве базы данных продуктов может привести к потере времени и денег … и ухудшению ситуации. Узнайте больше о том, насколько надежное программное обеспечение для управления информацией о продукте (PIM) является лучшим вариантом.
1. База данных подключает таблицы данных автоматически, в Excel нет
Excel обновляет ячейки при вводе новой информации, но не переносит эти данные автоматически во все связанные поля. Кроме того, перенос информации из одной электронной таблицы в другую таблицу данных — это трудоемкая задача, выполняемая вручную, которая уязвима для человеческой ошибки.
Программное обеспечениедля управления информацией о продуктах, или PIM, устраняет эти проблемы, мгновенно и автоматически обновляя информацию во всех связанных записях о продуктах и таблицах данных.
2. Отсутствие контроля версий
Использование Excel в качестве базы данных подвергает вас риску работы с неточной информацией и потере времени. Поскольку обновления доступны только после того, как пользователи активно сохранили изменения, а файлы могут быть сохранены в любом месте, может существовать несколько версий с конфликтующими или устаревшими данными для управления.
Благодаря обновлениям в реальном времени по всей платформе программное обеспечение PIM исключает возможность непреднамеренного получения множества версий, содержащих различную информацию, плавающую в вашей организации.
3. Работа с большими наборами данных может быть сложной задачей
Excel полезен для организации наборов данных до определенного размера, но общая известная проблема с этим программным обеспечением заключается в том, что скорость отклика обычно снижается до обхода, когда вы достигли верхнего предела данных программы.Ошибки программы и сбои программного обеспечения также могут стать обычной головной болью по мере увеличения размеров файлов.
Благодаря большой емкости хранилища программное обеспечение PIM легко обрабатывает большие и сложные наборы данных без потери производительности системы.
4. Не могут редактировать одновременно несколько человек
Поскольку для обновления информации в файле Excel необходимо, чтобы все изменения сохранялись на отдельной рабочей станции, одновременное редактирование не может быть выполнено несколькими пользователями. Это ограничение может привести к ошибочной работе пользователей с устаревшими данными и затруднить совместную работу в режиме реального времени.
Централизованная база данныхPIM позволяет нескольким пользователям на любом количестве рабочих станций получать доступ и редактировать информацию в любое время.
5. Фильтрация и поиск данных ограничены
Поиск нужных данных в Excel ограничивается поиском с помощью Control + F и фильтров столбцов. Этим методом поиска можно управлять, особенно для тех, кто хорошо знаком с электронной таблицей. Но если разным командам нужен легкий доступ к данным о товарах, разве не стоит предлагать современные возможности поиска?
Обладая надежными возможностями поиска, предлагающими несколько способов поиска информации, программное обеспечение PIM упорядочивает ваши данные и обеспечивает легкий доступ к ним всего за несколько щелчков мышью.Фактически, вы можете воспроизвести опыт поиска, аналогичный тому, что ваши клиенты найдут на Amazon или Home Depot.
6. Excel не может управлять визуальным контентом
Компании, которые пытаются использовать Excel в качестве базы данных, часто борются с разрывом между текстовой информацией и визуальными медиа. Excel не предназначен для поддержки изображений и видео с высоким разрешением, и единственная точка связи между информацией о продукте и связанными медиафайлами — это URL-адрес. Это приводит к очевидным проблемам при работе с информацией о продукте.
Для доступа к медиафайлам из Excel требуется как минимум несколько дополнительных шагов, а удаленные коллеги и соавторы вообще не могут получить доступ к визуальным медиафайлам, если вы используете локальный жесткий диск для хранения.
СистемыPIM хранят текстовые и визуальные медиа в одном интерфейсе. Как правило, визуальные медиа будут поставляться через интеграцию с системой управления цифровыми активами (DAM), которая является центральным источником всей библиотеки фотографий, видео, иллюстраций, документов и другого маркетингового контента в организации.
Какое решение?
Учитывая его ограничения, хотите ли вы доверить Excel свою важную информацию или данные о продуктах? Программное обеспечение PIM — это комплексное решение для баз данных, разработанное специально с учетом потребностей управления продуктами и электронной коммерции.
Испытайте преимущества освобождения себя от Excel из первых рук. Widen создает программное обеспечение DAM и PIM, чтобы помочь компаниям быстрее выводить контент на рынок. Cегодня!
Превратите вашу книгу Excel в базу данных SQLite | Скотт А.Адамс
Преобразование электронных таблиц в запрашиваемые таблицы базы данных
Реляционная база данных представляет собой набор таблиц данных — наборов строк и столбцов, в которых хранятся отдельные фрагменты данных, которые могут быть связаны друг с другом. Таким образом, реляционная база данных не полностью отличается от книги Excel со связанными наборами данных, хранящимися на нескольких листах. Имея это в виду, в этом посте рассматривается пример использования Python для преобразования электронной таблицы Excel в базу данных, которую можно запрашивать с помощью языка структурированных запросов (SQL) .
В примере в этом посте используются данные из набора данных Superstore-Sales , который можно найти здесь. Этот набор данных хранится в виде книги Excel и содержит примеры данных транзакций продаж, хранящихся на следующих четырех листах: продаж, , заказов, , клиентов, и продуктов.
Чтение данных в Python
Во-первых, если вы не установили пакет xlrd с использованием conda или pip , сделайте это перед запуском сеанса Python, иначе вы столкнетесь со следующей ошибкой при попытке читать в файле Excel (даже с установленным пандами ).
Теперь давайте запустим сеанс Python и импортируем pandas и sqlite3 , используя import pandas и import sqlite3 . Мы прочитаем данные из каждой электронной таблицы Excel в отдельный фрейм данных pandas , используя read_excel , как показано в приведенном ниже коде.
В каждом фрагменте кода первый параметр в read_excel определяет имя файла для обработки. При необходимости укажите путь к файлу.Например, когда я запускал этот код, мои данные хранились в каталоге data , но мой рабочий каталог был на один каталог выше. Второй параметр, sheet_name = , указывает имя обрабатываемой электронной таблицы в книге. Последний параметр, header = 0 , сообщает Python, что первая строка читаемой электронной таблицы содержит заголовки столбцов. Помните, что Python имеет нулевой индекс, поэтому для идентификации первой строки мы используем 0 , а не 1 .При явной идентификации строки заголовка значения в первой строке каждой электронной таблицы будут обрабатываться как имена столбцов для соответствующего фрейма данных.
Давайте взглянем на первые несколько строк каждого фрейма данных, чтобы убедиться, что все выглядит правильно.
Отлично! Теперь давайте создадим эту базу данных.
Доступно несколько продуктов для реляционных баз данных, и конкретный продукт, который мы здесь будем использовать, — это SQLite . Это легкий механизм базы данных SQL, который можно использовать для создания баз данных, хранящихся в виде файлов на персональном компьютере.Мы можем инициировать новый объект подключения к базе данных SQLite и назначить этот объект переменной. Ниже я назвал эту переменную db_conn .
db_conn = sqlite3.connect ("data / superstore.db") При выполнении этого кода, приведенного выше, в каталоге data будет создан новый файл с именем superstore.db (при условии, что superstore.db еще не существует в этом каталоге). Вновь созданный файл superstore.db на данный момент является пустой базой данных SQLite (т.е.е., таблиц в нем нет). db_conn также указывает на базу данных superstore.db и может рассматриваться как интерфейс для работы с этой базой данных. Прохладный! Но подождите … как мы на самом деле запускаем код SQL, который будет создавать таблицы данных? Рад, что ты спросил. Теперь нам нужно создать объект cursor , который представляет собой инструмент, который выполняет код SQL для интересующей базы данных. Здесь курсор назначен переменной, которую я назвал c .
c = db_conn.cursor ()
Следующим шагом является создание таблиц, которые будут включены в базу данных, хотя важно подчеркнуть, что следующий шаг создания таблиц приведет к пустым таблицам. Позже мы заполним наши таблицы данными из четырех ранее созданных фреймов данных. Однако прежде чем двигаться дальше, давайте взглянем на фактический код SQL, который будет использоваться для создания таблицы sales , чтобы лучше понять процесс создания таблицы.
Строка № 1 в приведенном выше коде предоставляет команду для создания таблицы с именем sales , а строки № 2–8 создают 7 новых столбцов в sales : SalesID, OrderID, ProductID, Sales, Quantity, Discount и Profit.Тип данных для каждого столбца указывается справа от имени соответствующего столбца. Более подробную информацию о типах данных SQLite можно найти здесь. Обратите внимание, что порядок столбцов соответствует порядку столбцов из связанного фрейма данных.
Порядок столбцов в операторе CREATE TABLE является преднамеренным, поскольку это гарантирует, что соответствующие значения из фрейма данных попадут в заданный столбец в таблице базы данных. Например, если бы я сделал OrderID первым столбцом, а SalesID — вторым, значения SalesID были бы записаны в столбец OrderID в таблице sales , а столбец SalesID в этой таблице содержал бы значения OrderID.
Строка № 9 устанавливает первичный ключ таблицы , который представляет собой столбец, содержащий значения, однозначно идентифицирующие каждую строку. В таблице sales SalesID удовлетворяет требованиям для первичного ключа, поскольку никакие две строки не имеют одинакового значения SalesID.
Снимок таблицы «продаж»Строка № 10 в предыдущем наборе кода устанавливает первый из двух внешних ключей для продаж . Внешний ключ — это столбец в одной таблице, который является первичным ключом в другой таблице.Например, обратите внимание, что разные строки могут иметь одно и то же значение OrderID в таблице sales , что исключает возможность использования OrderID в качестве первичного ключа в этой таблице . Однако в таблице orders каждая строка действительно содержит уникальное значение OrderID . Таким образом, OrderID может служить первичным ключом для заказов .
Снимок таблицы «заказы» Вернемся к фактическому коду в строке № 10 в предыдущем операторе CREATE TABLE .Первая часть этого кода, FOREIGN KEY (OrderID) , устанавливает, что столбец OrderID в sales является внешним ключом. Вторая часть, REFERENCE orders (OrderID) , затем указывает таблицу и первичный ключ, на который ссылается OrderID. Строка № 11 из инструкции CREATE TABLE следует той же логике для столбца ProductID.
Указав первичный и внешний ключи, мы можем создать «карту», которая показывает, как таблицы в базе данных соотносятся друг с другом.Например, тот, кто знаком с концепциями SQL, но не знаком с этими конкретными данными, может взглянуть на оператор CREATE TABLE , который создает таблицу sales и признать, что данные из таблиц sales и orders могут быть собраны вместе, или присоединились к , путем сопоставления значений в столбцах OrderID из обеих таблиц. Чтобы вкратце проиллюстрировать, как работает объединение, рассмотрим, как мы можем добавить OrderDate в таблицу sales путем «объединения» по OrderID, как показано ниже.
Завершение базы данных
Установив базовый обзор процесса создания таблицы, давайте создадим все таблицы базы данных. Здесь мы помещаем курсор, c , чтобы работать. Мы можем запустить c.execute () и включить желаемый код SQL в виде строки в круглые скобки для запуска указанного кода SQL в базе данных, к которой мы в настоящее время подключены (т.е. data / superstore.db ).
Пришло время заполнить таблицы в базе данных соответствующими данными из четырех ранее созданных фреймов данных.К счастью, этот шаг можно легко выполнить, используя pandas ’ to_sql в каждом фрейме данных (см. Более подробную информацию о to_sql здесь). Код в блоке ниже передает данные из каждого из четырех фреймов данных в соответствующие таблицы в базе данных.
Первый параметр в каждой строке кода выше определяет имя таблицы базы данных, в которую будут записаны значения из фрейма данных, а следующий параметр определяет фактическое соединение с базой данных.Затем if_exists = 'append' сообщает to_sql , что если таблица уже существует, что он делает в этом случае, тогда значения из фрейма данных должны быть вставлены в таблицу. Наконец, с index = False мы говорим to_sql не включать индекс в качестве дополнительного столбца в таблице.
Теперь у нас есть база данных SQLite, которая готова к работе! Вы можете запросить файл базы данных superstore.db с помощью оболочки командной строки SQLite или другого программного обеспечения базы данных, которое поддерживает SQLite, например DbVisualizer.Вы также можете запускать запросы к базе данных, используя read_sql из pandas (подробнее см. Здесь). Например, если мы хотим увидеть первые пять строк из sales , мы можем запустить код, показанный ниже, в нашей оболочке Python или записной книжке.
pd.read_sql ("SELECT * FROM sales LIMIT 5", db_conn) Когда вы закончите работу с этой базой данных в Python, вы можете закрыть соединение, запустив db_conn.close () . Если вы хотите снова работать с этой базой данных после закрытия, просто восстановите соединение с базой данных
db_conn = sqlite3.connect ("data / superstore.db") и выполнять запросы, используя read_sql . Нет необходимости устанавливать новый курсор, если вы не записываете данные в базу данных.
Еще одно уточняющее слово перед тем, как мы закончим. В начале этого поста я упомянул, что структура книги Excel похожа на базу данных SQL, поскольку обе могут состоять из нескольких связанных таблиц. С этой целью данное упражнение предназначено для демонстрации того, как книгу Excel можно легко превратить в файл базы данных SQLite.Тем не менее, мы лишь коснулись поверхности дизайна базы данных, и я хочу подчеркнуть, что хорошо спроектированная база данных — это больше, чем просто набор таблиц. Действительно, процесс нормализации , , , который направлен на устранение избыточности данных и обеспечение целостности данных, очень важен при проектировании реляционной базы данных. Имейте в виду, что созданная нами база данных может быть нормализована и дальше. Если вам интересно узнать больше о реляционных базах данных и причинах хранения данных в нескольких таблицах, я рекомендую прочитать больше о нормализации.
Хорошо, для этого поста хватит. Благодарим за прочтение. Обращайтесь к нам, если у вас есть какие-либо вопросы и / или конструктивный отзыв.
Как перейти с 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, что делать с данными в ячейке. Например, вы можете сложить числовые значения, используя СУММ (A1: A5) , или усреднить их, используя СРЕДНЕЕ (A1: A5) .
Эквивалент формулы в SQL — запрос . Запрос на возврат приведенной выше таблицы выглядит так:
ВЫБЕРИТЕ player_name,
родной город
государство,
масса
ОТ benn.college_football_players
SELECT и FROM — два основных ингредиента любого SQL-запроса: SELECT указывает столбцы данных, которые вы хотите, а FROM указывает, в какой таблице они находятся.Фактически вы можете выбрать отображение всех столбцов, добавив звездочку (*) после SELECT , например:
ВЫБРАТЬ *
ОТ benn.college_football_players
Этот запрос покажет вам все столбцы в таблице benn.college_football_players , чтобы вы могли получить представление о том, как выглядит весь набор данных. Когда вы знаете, что вам нужно, вы можете быстро вырезать столбцы, чтобы уменьшить размер набора данных.
Подобно формулам, запросы состоят из функций , которые определяют манипуляции с данными.Запросы также могут содержать предложения, операторы, выражения и некоторые другие компоненты, но мы не будем здесь вдаваться в подробности. Что вам нужно знать, так это то, что вы можете использовать SQL для управления данными практически так же, как в Excel.
Возьмем, к примеру, функцию IF . Вы используете IF для создания условных операторов, которые фильтруют данные или добавляют новые данные на основе определенных вами правил. Вот как выглядит функция IF , когда вы применяете ее к ячейке:
= ЕСЛИ (логический_тест, значение_если_ истинно, [значение_если_ ложь])
Вы могли прочитать, что как IF <какое-то условие выполнено> THEN <отобразить это значение> ИНАЧЕ <отобразить другое значение> .Часть OTHERWISE , которая отображается как [value_if_false] , является необязательной.
Эквивалент IF в SQL — CASE . У них очень похожий синтаксис:
CASE WHEN <условие 1> THEN
ИНАЧЕ <отобразить другое значение>
КОНЕЦ
Операторы CASE значительно легче читать, чем операторы IF , поскольку запросы SQL состоят из нескольких строк.Эта структура идеально подходит для оператора IF с несколькими условиями. Например, если вы хотите добавить две категории на основе существующих данных в Excel, вы должны вложить один оператор IF в другой оператор IF. Когда вы добавляете много условий, все быстро становится уродливым. Но в SQL вы можете просто добавить новое условие в виде другой строки.
Давайте добавим несколько условий в SQL, используя данные американского футбола, указанные выше. Мы собираемся добавить столбец, который разделит футболистов на четыре весовые группы.Вот запрос:
ВЫБЕРИТЕ player_name,
масса,
СЛУЧАЙ, КОГДА вес> 250, ЗАТЕМ "более 250"
КОГДА вес> 200 ТОГДА '201-250'
КОГДА вес> 175, ТО '176-200'
ИНАЧЕ '175 или меньше' КОНЕЦ AS весовая_группа
ОТ benn.college_football_players
А вот как выглядит таблица результатов:
Не слишком сложно, правда? Заявление IF об этом было бы кошмаром.
Вы, наверное, думаете, а как насчет диаграмм и графиков? А как насчет графиков, которые превращают мой отчет в отчет? Один из вариантов — управлять данными в SQL, экспортировать их и строить диаграммы в Excel.
Если вы хотите пропустить этап экспорта, некоторые программы SQL (например, Mode) позволяют строить диаграммы поверх результатов запроса. Эти диаграммы связаны непосредственно с вашей базой данных, поэтому каждый раз, когда вы повторно запускаете запрос, ваши результаты и визуализации автоматически обновляются.
Аналитика режима
Бесплатная книга: краткое руководство пользователя Excel по SQL
Мы составили книгу из шести задач Excel и их аналогов SQL. Каждый урок содержит пример набора данных и подробные инструкции.Вы узнаете, как выполнять следующие функции Excel в SQL:
- Арифметика
-
IF - Фильтрация
-
ВПР -
SUM,COUNTиAVG - Групповое агрегирование (сводные таблицы)
Следующие шаги для изучения SQL
По мере того, как вы приобретаете знания SQL, полезно знать, на чем следует сосредоточить свое обучение и как ориентироваться в данных вашей компании.
Pick учебники, ориентированные на анализ данных
Ресурсов SQL много, но не все они ориентированы на анализ данных.
Инженеры и администраторы баз данных используют SQL для создания, обновления и удаления таблиц в базах данных. Они могут загрузить целую новую таблицу или навсегда удалить одну из базы данных. Эти задачи сильно отличаются от того, как вы будете использовать SQL (по крайней мере, до тех пор, пока вы не полюбите данные настолько, что переключитесь на аналитику).
Не увязните в учебных курсах по SQL, предназначенных для управления базами данных.Изучите учебники, ориентированные на запросы. Вот несколько уроков по SQL для начала:
Если вы обнаружите, что в учебных курсах вы говорите о таких вещах, как:
-
СОЗДАТЬ ТАБЛИЦУ -
ТАБЛИЦА ПАДЕНИЯ -
СОЗДАТЬ БАЗУ ДАННЫХ -
ПАДЕНИЕ БАЗЫ ДАННЫХ
… вы попали не в ту кроличью нору.
Практикуйтесь с данными вашей компании
Если вы занимаетесь анализом на работе, ничто не заменит использование данных вашей компании для изучения SQL.Вы можете изучить структуру данных вашей компании и одновременно с изучением технических концепций. Все, что вы выберете, будет немедленно применимо к вашей работе.
Для этого вам необходимо понимать, как организованы данные вашей компании. Где хранятся данные о продукте и маркетинге? Если вы хотите посмотреть на аккаунты, которые были активированы в прошлом месяце, какие таблицы вы должны запрашивать?
У большинства предприятий есть команда аналитиков, которая ежедневно работает с данными вашей компании.Эти люди смогут ответить на ваши вопросы или порекомендовать вам полезную документацию. Здесь есть взаимовыгодные отношения: если вы запрашиваете данные самостоятельно, вы сокращаете количество невыполненных запросов данных командой аналитиков. Гарантированные шоколадные очки.
Ряд клиентов Mode даже организовали регулярные тренинги, во время которых аналитики обучают своих коллег SQL, используя их внутренние данные. Отзывы были в основном положительными от обеих групп! Сообщите нам, если вы думаете об установке этого в своей компании.Мы рады поделиться некоторыми знаниями.
Вы в пути!
Не терпится начать? Загрузите наше бесплатное краткое руководство пользователя Excel и погрузитесь в наше учебное пособие по SQL!