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

Excel Create Database (Содержание)

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

Создать базу данных в Excel

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

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

Ввод данных для создания базы данных Excel

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

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

Я добавил первые несколько идентификаторов сотрудников . Произнесите D01, D02, D03, а затем перетащите оставшиеся до строки 12, используя ручку заполнения . Второй столбец содержит общую информацию о сотруднике, такую ​​как Имя, Фамилия, Назначение и Зарплата. Заполните эту информацию в ячейках вручную в соответствии с вашими данными. Убедитесь, что формат столбца Зарплата применяется ко всем ячейкам столбца (в противном случае эта база данных может вызвать ошибку при использовании).

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

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

Смотрите скриншот ниже:

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

Предположим, вы добавили правильную запись, но на 2- ^й строке после последней строки (т.е. одна строка оставлена ​​пустой). Также не рекомендуется делать это. Это разбивка для ваших данных. Смотрите скриншот ниже:

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

Давайте посмотрим на скриншот ниже:

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

Все строки называются «Запись в базе данных Excel».

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

Каждый столбец — это поле в базе данных Excel

Каждый столбец называется Поле в базе данных Excel. Заголовки столбцов называются именами полей.

Таблица формата

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

• Выберите ячейки от A2 до F12 в электронной таблице.
• Перейдите на вкладку « Главная ».
• Выберите « Формат как таблица» в раскрывающемся меню. Вы можете выбрать собственный стол.

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

Как только вы довольны диапазоном, вы можете выбрать OK. Теперь вы можете увидеть свои данные в табличной форме. Смотрите скриншот, приведенный ниже:

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

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

Расширение базы данных

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

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

Форматирование базы данных

Выделите ячейку A1 до F1 из электронной таблицы.

• Выберите вкладку « Главная »
• На вкладке «Главная страница» перейдите к « Обтекание текстом», а также « Объединение и центрирование».
• Вы также можете изменить цвет заливки. Выберите Цвет заливки. Выберите цвет вашего интереса. Здесь я выбрал зеленый цвет.

Вот как мы создали нашу базу данных в Excel.

• Информация об одном предмете должна быть полностью заполнена в одном ряду. Вы не можете использовать несколько строк для добавления разных данных одного и того же элемента в базу данных Excel.
• Поле не должно быть пустым. (Включая заголовки столбцов / название поля).
• Тип данных, введенный в один столбец, должен быть однородным. Например, Если вы вводите данные о зарплате в столбце Зарплата, в этом столбце не должно быть текстовой строки. Аналогично, любой столбец, содержащий текстовые строки, не должен содержать никакой числовой информации.
• База данных, созданная здесь, действительно очень маленький пример. Это становится огромным с точки зрения сотрудников, которые время от времени присоединяются, и становится беспокойным, чтобы поддерживать данные снова и снова со стандартным форматированием. Вот почему рекомендуется использовать базы данных.

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

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

1. Как импортировать данные в Excel?
2. Применение стилей таблиц в Excel
3. Как использовать панель инструментов Excel?
4. Руководство по строкам и столбцам Excel

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

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

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

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

Откройте лист Excel и начните вводить данные. Заголовки, например, Имя ученика, Номер зачисления ученика, Имя родителя, Дата рождения, Предметы и Оценка.

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

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

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

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

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

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

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

1. Выберите данные, которые вы хотите быть в форме таблицы.
   выберите ваши данные
2. Перейти к Вставить
   Найдите «Вставить» на своей странице Excel
3. Нажмите на таблицу
   Создать таблицу
4. Это диалоговое окно появится, если есть другие данные, помимо выбранных столбцов и строк на электронной таблице, которые вы хотите добавить в таблицу, вы можете просто изменить цифры и алфавиты для строк и столбцов.
   Если вы отметите опцию «Мои таблицы имеют заголовки», ваши заголовки станут частью вашей таблицы.
5. Нажав OK, вы увидите, как теперь ваша таблица будет выглядеть так.
   Ваша таблица для всех данных, которые вы только что ввели.
6. Все стрелки в первом ряду показывают варианты, с помощью которых вы можете отсортировать дату. Вы можете расположить их в алфавитном порядке, вы можете выбрать конкретных студентов.
   Изучаем варианты. Вы даже можете выбрать и отменить выбор чеков, чтобы включить или исключить этого конкретного учащегося из таблицы.

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

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

Вы можете добавить больше данных здесь и сейчас.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как создать базу данных онлайн, импортируя данные из Excel и CSV файлов | QuintaDB

QuintaDB представляет систему импорта данных из Excel и CSV файлов

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

• Создайте Excel файл с данными;

• После регистрации, создайте проект и  нажмите «Импорт данных».

QuintaDB создаст форму, где полями будут названия колонок в Excel.

QuintaDB не импортирует не все типы полей. Поэтому, сразу после импорта, перейдите в меню «Конструктор форм» и обновите типы полей (колонок).

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

• Перейдите в меню Записи и нажмите на ссылку импорт. Затем выберите нужный файл и загрузите его.

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

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

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

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

Данные должны быть в виде:

75281,676,A541,Вася Пупкин,Москва,Россия. Т.е. значения должны быть разделены запятой.

Если  данные будут в виде:

75281;676;A541;Вася Пупкин;Москва;Россия — то система не сможет импортировать такой файл.

Кодировка: UTF-8.

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

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

Автоматизация обработки таблиц в Excel и перенос данных в MySQL [ Разбор кейса ]

Обзор кейса

Карло, ИТ консультант, работал над сложной, но довольно типичной задачей. Ему нужно было разработать систему, которая бы создавала отчеты для руководства на основе Excel файлов, полученных от порядка 10-20 исследовательских лабораторий.

Сначала эти отчеты создавали в Excel – в основном вручную – и направляли руководству и лабораториям в печатном виде или PDF.

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

«Первоначальный подход занимал около 20 дней ужасной работы. Нам приходилось иметь дело с ошибками после копирования огромного объема данных из одного сводного отчета в Excel в отдельные отчеты по каждой задействованной лаборатории. Форматы очень разные, поэтому не представлялось возможным автоматизировать этот процесс в Excel. Кроме того, клиенту нужны были графики, где можно было бы сравнить результаты, полученные много месяцев назад – такое возможно только при выгрузке данных в MySQL.»

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

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

Надстройка XLTools, с другой стороны, дала Карло гибкие инструменты для автоматизации всего процесса без VBA: извлечь, подготовить и экспортировать данные в базу данных MySQL.

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

«Мой клиент очень доволен результатами, потому что теперь мы можем выполнить всю работу за 2 часа вместо 20 дней. XLTools помог нам облегчить работу с текущей XLS структурой. Процесс открыт для правок в будущем – скрипт очень легко изменить. Сейчас мы переносим данные в базу данных за минуты, где уже формируем все нужные отчеты. »

Рассмотрим некоторые операции, автоматизированные в этом процессе.

Шаг 1. Автоматически собрать данные из файлов Excel

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

Именно это и выполняет команда XLTools.ExtractTable:

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

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

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

Для этого, пропишем команду автоматизации XLTools. ExtractTable:

Укажите диапазон данных, которые нужно извлечь:

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

• Диапазон E:F на листе “LABCODE”

• Пропустить (Skip) 4 строки сверху, поскольку данные по факту начинаются со строки 5

Укажите, куда поместить извлеченные данные:

• Назвать новую таблицу “LabCodes”

• Присвоить заголовки “lab” and “stat”

• Сохранить формат ячеек (PreserveFormat)

• Поместить результат на новый скрытый лист “LabCodes”.

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

Выполнив эту команду, получим такую таблицу “LabCodes”, начиная с ячейки A1 на новом скрытом листе “LabCodes”:

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

Шаг 2. Объединить две таблицы Excel c помощью автоматизированного SQL запроса

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

Для обработки данных мы рекомендуем команду XLTools.SQLSelect. Мы очень любим и сами часто используем эту команду:

• SQL запросы – первый выбор, если нужно объединить две или более таблиц по общему полю;

• XLTools позволяет применять SQL SELECT запросы напрямую к таблицам Excel и быстро подготовить данные;

• SQL позволяет выполнять несколько сложных задач за один раз – можно реорганизовать и отформатировать данные всего за пару запросов.

Например, нам нужно было объединить эти две таблица и переименовать столбцы. Кроме того, нам нужно было обработать положительные/отрицательные значения: столбец “sign” указывает, что значения в столбце “d” положительные или отрицательные.

Для этого, мы используем команду XLTools.SQLSelect:

Укажите, какие поля нужно выбрать (SELECT):

• Взять столбец “lab” из таблицы 2 и назвать “ic_code”

• Взять столбец “Nb” из таблицы 1 и назвать “rank”

• Взять столбец “%” из таблицы 1 и назвать “percent”

• Взять столбец “N°” из таблицы 1 и назвать “lab_code”

• Взять столбец “sign” из таблицы 1 и назвать “dev”. В то же время добавляем условие (CASE WHEN): если значение в столбце “sign” равно “-”, тогда умножить значение в столбце “d” на -1

• Взять столбец “Sd” из таблицы 1 и назвать “s_dev”

• Взять столбец “D2” из таблицы 1 и назвать “dist”

• Взять столбец “Method” из таблицы 1 и назвать “method”

• Найти и выбрать таблицу “Table1” из (FROM) книги “source.xls”

• Объединить таблицы по значениям (LEFT JOIN ON) в столбцах “Nb” и “stat”

Укажите, как выгрузить результат запроса:

• Сохранить формат ячеек (PreserveFormat)

• Присвоить новой таблице название “data”

• Поместить результат на новый лист “data”

Как только вы выполните эту команду, через секунду будет сгенерирована таблица результата:

Обратите внимание, что все столбцы имеют новые названия. Значения в столбце “dev” либо положительные, либо отрицательные. XLTools.SQLSelect – отличный инструмент для автоматизации обработки таблиц Excel. Практически все сложные манипуляции в проекте Карло мы выполнили с помощью SQL запросов.

Шаг 3. Автоматически экспортировать данные из Excel в базу данных MySQL

В проекте Карло было немало сложных и вдохновляющих задач (СПАСИБО)! Если извлечение данных и SQL запросы уже давно включены в Автоматизацию XLTools, то экспорт из Excel в MySQL – новая разработка.

После того, как мы подготовили и нужным образом отформатировали данные, создаем команду XLTools.ExportToMySQL:

Укажите, какие таблицы нужно экспортировать в базу данных:

• Взять таблицы “zscore-fix”, ”zscore-pt”, ”outliers”, ”repeatability”, ”data”

Введите данные для подключения к MySQL:

• Введите учетные данные для входа в вашу БД: сервер, база данных, имя пользователя и пароль. Это позволит XLTools связаться с вашей базой данных.

Укажите, где поместить отчет об экспорте данных:

По завершении выполнения, команда выгрузит данные в MySQL и создаст сводный отчет об экспорте:

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

В данном случае, экспорт двух записей в таблице “zscore-fix” не был успешен. Похоже, некоторые ячейки в этой таблице были пустыми – а такие записи не могут быть экспортированы в MySQL. Теперь легко вернуться и исправить ошибки.

Привязать выполнение всего процесса к одной кнопке

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

Напр., команды Карло привязаны к трем кнопкам. “Prepare & Export” запускает весь процесс (извлечение, подготовка и экспорт данных) по нажатию всего одной кнопки:

У вас есть процессы в Excel, которые можно автоматизировать?

Свяжитесь с нами! Готовы помочь.

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

Что если в XLTools нет (пока нет) функционала, нужного для вашей конкретной задачи? Мы постараемся разработать его для вас. В основе многих новых надстроек – предложения наших пользователей. Благодаря проекту Карло, Автоматизация теперь поддерживает Экспорт из Excel в MySQL.

«Мы выбрали XLTools, потому что команда очень быстро отреагировала на наш запрос. Они были готовы обсуждать варианты, помогли нам с кастомным решением и были всегда на связи. »

Благодарность: Карло Мелис (Carlo Melis), ИТ консультант, работает по заданию международного негосударственного органа по сертификации пищевой продукции, Рим, Италия.

Надстройки в фокусе: Автоматизация без VBA (извлечение таблиц, SQL запросы, экспорт из Excel в MySQL). Поддерживается в версии XLTools 5.0.0.762 и выше.

Как создать базу данных с возможностью поиска в 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 (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,

StdName: Jemmy Fox,

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

Возраст учащихся 25,

Кафедра CSE,

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

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

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

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

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

StdID — 103457045,

StdName — Jackson,

State is New York,

Возраст 23,

Department ETE,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Появится диалоговое окно Create Table . Я беру то, что предлагает Excel. Да, у моей таблицы есть заголовки. Щелкните ОК , и таблица будет создана. Тада !!!

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

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

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

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

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

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

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

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

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

Загрузите рабочий файл

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

Database_in_Excel.xlsx

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

Здравствуйте!
Добро пожаловать в мой блог о Excel! Мне потребовалось некоторое время, чтобы стать поклонником Excel.Но теперь я убежденный поклонник MS Excel. Я узнаю новые способы работы с Excel и делюсь здесь. Не только руководство по 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 и создание модели данных

Abstract: Это первое руководство из серии, предназначенное для ознакомления и ознакомления с работой с Excel и его встроенными функциями объединения и анализа данных. Эти руководства создают и уточняют книгу Excel с нуля, создают модель данных, а затем создают потрясающие интерактивные отчеты с помощью Power View. Учебники предназначены для демонстрации функций и возможностей Microsoft Business Intelligence в Excel, сводных таблицах, Power Pivot и Power View.

Примечание. В этой статье описываются модели данных в Excel 2013. Однако те же функции моделирования данных и Power Pivot, представленные в Excel 2013, также применимы к Excel 2016.

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

Уроки из этой серии следующие:

org/ItemList»>

1. Импорт данных в Excel 2013 и создание модели данных

2. Расширение отношений модели данных с помощью Excel, Power Pivot и DAX

3. Создание отчетов Power View на основе карт

4. Включение данных из Интернета и установка параметров отчета Power View по умолчанию

5. Справка по Power Pivot

6. org/ListItem»>

   Создание потрясающих отчетов Power View — часть 2

В этом руководстве вы начнете с пустой книги Excel.

В этом руководстве есть следующие разделы:

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

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

Импорт данных из базы данных

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

Начнем с загрузки данных из Интернета. Данные описывают олимпийские медали и представляют собой базу данных Microsoft Access.

1. org/ListItem»>

   Щелкните следующие ссылки, чтобы загрузить файлы, которые мы используем в этой серии руководств.Загрузите каждый из четырех файлов в легкодоступное место, например, Загрузки или Мои документы , или в новую папку, которую вы создаете:
   > OlympicMedals.accdb База данных доступа
   > OlympicSports.xlsx Рабочая тетрадь Excel
   > Population.xlsx Книга Excel
   > DiscImage_table.xlsx Книга Excel

2. В Excel 2013 откройте пустую книгу.

3. Щелкните ДАННЫЕ> Получить внешние данные> Из доступа .Лента динамически настраивается в зависимости от ширины книги, поэтому команды на ленте могут немного отличаться от следующих экранов. На первом экране отображается лента, когда книга широкая, на втором изображении показана книга, размер которой был изменен, чтобы занимать только часть экрана.

4. Выберите загруженный файл OlympicMedals.accdb и щелкните Открыть .Появится следующее окно выбора таблицы, в котором отображаются таблицы, найденные в базе данных. Таблицы в базе данных похожи на рабочие листы или таблицы в Excel. Установите флажок Разрешить выбор нескольких таблиц и выберите все таблицы. Затем нажмите ОК .

5. Откроется окно Импорт данных.

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

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

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

После импорта данных в Excel и автоматического создания модели данных вы готовы исследовать данные.

Изучите данные с помощью сводной таблицы

Изучать импортированные данные легко с помощью сводной таблицы. В сводной таблице вы перетаскиваете поля (аналогичные столбцам в Excel) из таблиц (например, таблицы, которые вы только что импортировали из базы данных Access) в разные области сводной таблицы, чтобы настроить способ представления данных. Сводная таблица имеет четыре области: ФИЛЬТРЫ , СТОЛБЦЫ , СТРОК и ЗНАЧЕНИЯ .

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

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

1. В полях сводной таблицы разверните таблицу Медали , щелкнув стрелку рядом с ней. Найдите поле NOC_CountryRegion в развернутой таблице Медали и перетащите его в область COLUMNS . НОК означает национальные олимпийские комитеты, которые являются организационной единицей страны или региона.

2. Затем из таблицы Disciplines перетащите Discipline в область ROWS .

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

   1. org/ListItem»>

      Щелкните в любом месте сводной таблицы, чтобы убедиться, что выбрана сводная таблица Excel. В списке полей сводной таблицы , где развернута таблица Дисциплины , наведите указатель мыши на ее поле Дисциплина, и справа от поля появится стрелка раскрывающегося списка.Щелкните раскрывающийся список, нажмите (Выбрать все) , чтобы удалить все выбранные параметры, затем прокрутите вниз и выберите Стрельба из лука, Дайвинг, Фехтование, Фигурное катание и Конькобежный спорт. Нажмите ОК .

   2. Или в разделе Ярлыки строк сводной таблицы щелкните раскрывающийся список рядом с Ярлыками строк в сводной таблице, щелкните (Выбрать все) , чтобы удалить все выборы, затем прокрутите вниз и выберите Стрельба из лука, Дайвинг, Фехтование, Фигурное катание и конькобежный спорт. Нажмите ОК .

4. В полях сводной таблицы из таблицы Медали перетащите Медаль в область ЗНАЧЕНИЯ . Поскольку значения должны быть числовыми, Excel автоматически изменяет медаль на . Количество медалей .

5. Из таблицы Медали снова выберите Медаль и перетащите ее в область ФИЛЬТРЫ .

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

   1. org/ListItem»>

      В сводной таблице щелкните раскрывающийся список справа от Ярлыки столбцов .

   2. Выберите Value Filters и выберите Greater Than….

   3. Введите 90 в последнем поле (справа).Нажмите ОК .
      

Ваша сводная таблица выглядит как на следующем экране.

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

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

Импорт данных из электронной таблицы

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

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

1. Вставьте новый лист Excel и назовите его Sports .

2. Перейдите в папку, содержащую загруженные файлы образцов данных, и откройте OlympicSports.xlsx .

3. org/ListItem»>

   Выберите и скопируйте данные в Sheet1 . Если вы выбрали ячейку с данными, например ячейку A1, вы можете нажать Ctrl + A, чтобы выбрать все смежные данные. Закройте книгу OlympicSports.xlsx.

4. На листе Sports поместите курсор в ячейку A1 и вставьте данные.

5. Не снимая выделения с данных, нажмите Ctrl + T, чтобы отформатировать данные в виде таблицы.Вы также можете отформатировать данные в виде таблицы с ленты, выбрав HOME> Форматировать как таблицу . Так как данные имеют заголовки, выберите Моя таблица имеет заголовки в появившемся окне Создать таблицу , как показано здесь.

   Форматирование данных в виде таблицы имеет много преимуществ. Вы можете присвоить таблице имя, которое упростит идентификацию. Вы также можете устанавливать связи между таблицами, позволяя исследовать и анализировать в сводных таблицах, Power Pivot и Power View.

6. Назовите таблицу. В ИНСТРУМЕНТЫ ТАБЛИЦ> ДИЗАЙН> Свойства найдите поле Имя таблицы и введите Sports . Рабочая книга выглядит как на следующем экране.
   

7. Сохраните книгу.

Импорт данных с помощью копирования и вставки

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

1. Вставьте новый лист Excel и назовите его Hosts .

2. Выделите и скопируйте следующую таблицу, включая ее заголовки.

org/ItemList»>

1. В Excel поместите курсор в ячейку A1 на листе Hosts и вставьте данные.

2. Отформатируйте данные в виде таблицы. Как описано ранее в этом руководстве, вы нажимаете Ctrl + T, чтобы отформатировать данные в виде таблицы, или из HOME> Format as Table . Так как данные имеют заголовки, выберите Моя таблица имеет заголовки в появившемся окне Создать таблицу .

3. Назовите таблицу. В TABLE TOOLS> DESIGN> Properties найдите поле Table Name и введите Hosts .

4. org/ListItem»>

   Выберите столбец «Издание» и на вкладке HOME отформатируйте его как Число с 0 десятичными знаками.

5. Сохраните книгу. Ваша рабочая тетрадь выглядит как на следующем экране.

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

Создать связь между импортированными данными

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

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

2. Прокрутите список, чтобы увидеть только что добавленные таблицы.

3. org/ListItem»>

   Разверните Sports и выберите Sport , чтобы добавить его в сводную таблицу. Обратите внимание, что Excel предлагает вам создать связь, как показано на следующем экране.
   
   

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

4. Щелкните СОЗДАТЬ. .. в выделенной области Поля сводной таблицы , чтобы открыть диалоговое окно Create Relationship , как показано на следующем экране.

5. В таблице выберите Дисциплины из раскрывающегося списка.

6. В колонке (иностранная) выберите SportID .

7. В связанной таблице выберите Sports .

8. В Связанном столбце (основном) выберите SportID .

9. org/ListItem»>

   Нажмите ОК .

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

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

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

В следующем руководстве Extend Data Model Relationships с помощью Excel 2013, Power Pivot и DAX вы опираетесь на то, что узнали здесь, и пошагово расширяете модель данных с помощью мощной и наглядной надстройки Excel под названием Power Вращаться. Вы также узнаете, как вычислять столбцы в таблице и использовать этот вычисляемый столбец, чтобы в вашу модель данных можно было добавить в противном случае несвязанную таблицу.

Контрольно-пропускной пункт и викторина

Повторение того, что вы узнали

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

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

Вы готовы к следующему руководству из этой серии. Вот ссылка:

Расширение отношений модели данных с помощью Excel 2013, Power Pivot и DAX

ВИКТОРИНА

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

Вопрос 1: Почему важно преобразовывать импортированные данные в таблицы?

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

B: Если вы конвертируете импортированные данные в таблицы, они будут исключены из модели данных. Только когда они исключены из модели данных, они доступны в сводных таблицах, Power Pivot и Power View.

C: Если вы конвертируете импортированные данные в таблицы, их можно включить в модель данных и сделать доступными для сводных таблиц, Power Pivot и Power View.

D: Вы не можете преобразовать импортированные данные в таблицы.

Вопрос 2: Какие из следующих источников данных можно импортировать в Excel и включить в модель данных?

A: Доступ к базам данных, а также ко многим другим базам данных.

B: Существующие файлы Excel.

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

D: все вышеперечисленное

Вопрос 3: Что происходит в сводной таблице, когда вы меняете порядок полей в четырех областях полей сводной таблицы?

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

B: формат сводной таблицы изменен, чтобы отразить макет, но основные данные не затронуты.

C: формат сводной таблицы изменен, чтобы отразить макет, и все базовые данные навсегда изменены.

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

Вопрос 4: Что требуется при создании связи между таблицами?

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

B: Одна таблица не должна быть частью книги Excel.

C: Столбцы нельзя преобразовывать в таблицы.

D: Все вышеперечисленное неверно.

Ответы на викторину

1. org/ListItem»>

   Правильный ответ: C

2. Правильный ответ: D

3. Правильный ответ: B

4. Правильный ответ: D

Примечания: Данные и изображения в этой серии руководств основаны на следующем:

• набор данных Олимпийских игр от Guardian News & Media Ltd.

• Изображения флагов из CIA Factbook (cia.gov)

• Данные о населении Всемирного банка (worldbank.org)

• Пиктограммы олимпийского спорта, сделанные Тадиусом 856 и Парутакупиу

Как создать форму ввода данных в Excel (Пошаговое руководство)

Посмотрите видео об использовании форм ввода данных в Excel

Ниже приведено подробное письменное руководство по форме ввода данных Excel в Если вы предпочитаете читать, а не смотреть видео.

Excel имеет много полезных функций, когда дело доходит до ввода данных.

И одной из таких функций является форма для ввода данных .

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

Зачем нужно знать формы для ввода данных?

Может быть, нет!

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

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

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

Форма ввода данных может помочь, сделав процесс быстрее и менее подверженным ошибкам.

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

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

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

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

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

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

Ниже представлена ​​демонстрация того, как это работает:

Как видите, это проще, чем обычный ввод данных, так как все в одном диалоговом окне.

Форма ввода данных в Excel

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

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

Чтобы использовать его, вам необходимо сначала добавить его на панель быстрого доступа (или на ленту).

Добавление опции формы ввода данных на панель быстрого доступа

Ниже приведены шаги по добавлению опции формы ввода данных на панель быстрого доступа:

1. Щелкните правой кнопкой мыши любой из существующих значков на панели быстрого доступа.
2. Щелкните «Настроить панель быстрого доступа».
3. В открывшемся диалоговом окне «Параметры Excel» выберите в раскрывающемся списке параметр «Все команды».
4. Прокрутите список команд и выберите «Форма».
5. Нажмите кнопку «Добавить».
6. Нажмите ОК.

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

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

Части формы ввода данных

Форма ввода данных в Excel имеет много разных кнопок (как вы можете видеть ниже).

Вот краткое описание того, что означает каждая кнопка:

• Новый : Это очистит все существующие данные в форме и позволит вам создать новую запись.
• Удалить : Это позволит вам удалить существующую запись. Например, если я нажму клавишу Delete в приведенном выше примере, он удалит запись для Майка Бейнса.
• Восстановить : если вы редактируете существующую запись, вы можете восстановить предыдущие данные в форме (если вы не нажимали «Создать» или не нажимали Enter).
• Найти предыдущий : поиск предыдущей записи.
• Найти следующий : поиск следующей записи.
• Критерий : позволяет находить определенные записи.Например, если я ищу все записи, в которых кандидат был нанят, мне нужно нажать кнопку «Критерии», ввести «Наемный» в поле «Статус» и затем использовать кнопки поиска. Пример этого рассматривается далее в этом руководстве.
• Закрыть : форма закроется.
• Полоса прокрутки : Вы можете использовать полосу прокрутки для просмотра записей.

Теперь давайте рассмотрим все, что вы можете делать с помощью формы ввода данных в Excel.

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

Если вы не выбрали ячейку в таблице Excel, отобразится запрос, как показано ниже:

Создание новой записи

Ниже приведены шаги для создания новой записи с помощью формы ввода данных в Excel :

1. Выберите любую ячейку в таблице Excel.
2. Щелкните значок формы на панели быстрого доступа.
3. Введите данные в поля формы.
4. Нажмите клавишу Enter (или нажмите кнопку «Создать»), чтобы ввести запись в таблицу и получить пустую форму для следующей записи.

Навигация по существующим записям

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

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

Ниже приведены шаги для навигации и редактирования записей с помощью формы ввода данных:

1. Выберите любую ячейку в таблице Excel.
2. Щелкните значок формы на панели быстрого доступа.
3. Чтобы перейти к следующей записи, нажмите кнопку «Найти далее», а для перехода к предыдущей записи нажмите кнопку «Найти предыдущую».
4. Чтобы отредактировать запись, просто внесите изменения и нажмите Enter. Если вы хотите вернуться к исходной записи (если вы не нажимали клавишу ввода), нажмите кнопку «Восстановить».

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

Снимок экрана выше показывает базовую навигацию, в которой вы просматриваете все записи одну за другой.

Но вы также можете быстро перемещаться по всем записям на основе критериев.

Например, если вы хотите просмотреть все записи со статусом «Выполняется», вы можете сделать это, используя следующие шаги:

• Выберите любую ячейку в таблице Excel.
• Щелкните значок формы на панели быстрого доступа.
• В диалоговом окне «Форма ввода данных» нажмите кнопку «Критерии».
• В поле «Статус» введите «В процессе». Обратите внимание, что это значение не чувствительно к регистру.Таким образом, даже если вы войдете в IN-PROGRESS, он все равно будет работать.
• Используйте кнопки «Найти предыдущий» / «Найти следующий», чтобы перемещаться по записям, состояние которых имеет статус «Выполняется».

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

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

Например, если вы хотите просмотреть все записи «Выполняется» после 07-08-2018, вы можете использовать «> 07-08-2018» в критериях для поля «Дата» и «Выполняется». ‘как значение в поле статуса.Теперь, когда вы перемещаетесь с помощью кнопок Найти предыдущий / Найти следующий, будут отображаться только записи после 07-08-2018, где статус находится в стадии выполнения.

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

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

Ниже приведены шаги для этого:

1. Выберите любую ячейку в таблице Excel.
2. Щелкните значок формы на панели быстрого доступа.
3. Щелкните кнопку Критерии.
4. В поле «Состояние» введите * progress
5. Используйте кнопки «Найти предыдущий» / «Найти следующий» для навигации по записям, состояние которых находится в состоянии «Выполняется».

Звездочка (*) — это подстановочный знак, который может представлять любое количество символов в Excel. Таким образом, если статус содержит «прогресс», он будет отмечен кнопками «Найти предыдущий / Найти следующий» независимо от того, что было перед ним).

Удаление записи

Вы можете удалить записи из самой формы ввода данных.

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

Ниже приведены шаги для удаления записи с помощью формы для ввода данных:

1. Выберите любую ячейку в таблице Excel.
2. Щелкните значок формы на панели быстрого доступа.
3. Перейдите к записи, которую хотите удалить
4. Нажмите кнопку «Удалить».

   Ограничение ввода данных на основе правил

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

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

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

   Вот как создать эти правила при вводе данных:

   1. Выберите ячейки (или даже весь столбец), в которых вы хотите создать правило проверки данных. В этом примере я выбрал столбец A.
   2. Щелкните вкладку Data.
   3. Щелкните параметр «Проверка данных».
   4. В диалоговом окне «Проверка данных» на вкладке «Настройки» выберите «Дата» в раскрывающемся списке «Разрешить».
   5. Укажите дату начала и дату окончания. Записи в пределах этого диапазона дат будут действительны, а остальные будут отклонены.
   6. Нажмите ОК.

   Теперь, если вы используете форму ввода данных для ввода данных в столбец «Дата», и если это не дата, то это будет запрещено.

   Вы увидите сообщение, показанное ниже:

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

   Вот несколько важных вещей, которые нужно знать о форме ввода данных Excel:

   1. Вы можете использовать подстановочные знаки при навигации по записям (с помощью параметра критериев ).
   2. У вас должна быть таблица Excel для использования формы ввода данных.Кроме того, для использования формы вам необходимо выбрать в ней ячейку. Но есть одно исключение. Если у вас есть именованный диапазон с именем «База данных», тогда форма Excel также будет ссылаться на этот именованный диапазон, даже если у вас есть таблица Excel.
   3. Ширина поля в форме ввода данных зависит от ширины столбца данных. Если ширина столбца слишком мала, то же самое будет отражено в форме.
   4. Вы также можете вставить маркеры в форму ввода данных. Для этого используйте сочетание клавиш ALT + 7 или ALT + 9 на цифровой клавиатуре.Вот видео о маркированных пунктах.

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

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

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

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

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

   У

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

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

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

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

   Ой, привет, SQL

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

     ВЫБРАТЬ имя_плеера,
          родной город
          государственный,
          масса
     ОТ 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] , является необязательной.

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

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

   Операторы

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

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

     ВЫБРАТЬ имя_плеера,
          масса,
          СЛУЧАЙ, КОГДА вес> 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
   • Фильтрация
   • ВПР
   • SUM , COUNT и AVG
   • Групповое агрегирование (сводные таблицы)
     
   Загрузите Краткое руководство пользователя Excel по SQL и начните изучать SQL уже сегодня.

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

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

   Pick учебники, ориентированные на анализ данных

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

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

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

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

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

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

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

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

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

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

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

   Вы в пути!

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

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

   Импорт таблицы SQL из Excel

   Как импортировать электронную таблицу Excel в таблицу базы данных SQL Server с помощью мастера импорта и экспорта SQL Server

   Создайте новую таблицу базы данных SQL из электронной таблицы Excel за девять шагов.

   Вы можете легко импортировать таблицу Microsoft SQL Server из электронной таблицы Excel, с помощью мастера импорта и экспорта SQL Server .(Вы также можете использовать Импорт и Мастер экспорта в экспортировать данные из таблицы SQL Server в электронную таблицу Excel.)

   Вы можете использовать мастер в SQL Server Standard, Enterprise, Developer или Evaluation. редакции.

   1. Введите данные в электронную таблицу Excel

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

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

   2. Запустите мастер импорта и экспорта SQL

   .
   • Затем в Windows запустите мастер импорта и экспорта на Пуск / Все Программы / Microsoft SQL Server 2008 / Импорт и экспорт данных.
   • Появится страница Добро пожаловать, . Нажмите Далее .

   Мастер импорта и экспорта SQL в меню «Пуск» Windows
   

   3. Выберите электронную таблицу Excel в качестве источника данных

   • В раскрывающемся списке Data Source на странице Choose a Data Source выберите Microsoft Excel .
   • В поле Путь к файлу Excel укажите путь к файлу электронной таблицы Excel.
   • Выберите версию в раскрывающемся списке Excel версия .
   • Убедитесь, что Первая строка содержит имена столбцов. отмечен.
   • Нажмите Далее .
   
   
   

   4. Выберите базу данных SQL в качестве назначения

   • В раскрывающемся списке Назначение примите настройку по умолчанию SQL Server Собственный клиент 10.0 .
   • В раскрывающемся списке Имя сервера введите имя сервера. Пример является удаленным сервером, поэтому были указаны IP-адрес и порт сервера.
   • Выберите тип Аутентификация . Пример — удаленный сервер, поэтому SQL Server требуется аутентификация с использованием имени пользователя и пароля.
   • В раскрывающемся списке База данных выберите или введите имя базы данных.
   • Нажмите Далее .

   5. Укажите, как копировать данные

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

   МОЖЕТ ЕСТЬ SQL?
   DBASE IZ GETDB (‘db / demo.db ‘)
   FUNNAHS IZ DBUCKET (& DBASE &, «МОГУ ЛИ Я, ПОЖАЛУЙСТА, ПОЛУЧИТЬ * ВСЕ НА ЛОЛИТЕ»)
   IM IN UR FUNNAHS ITZA TITLE
   VOTEZ IZ & TITLE # ups & — & TITLE # downs &

   6. Выберите исходные таблицы и представления

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

   7.Запускаем «Пакет»

   Следующее сообщение появляется внизу страницы Run Package : In SQL Server Express, Web или Workgroup, вы можете запустить пакет, который используется для импорта и экспорта. Мастер создает, но не может сохранить. Чтобы сохранить пакеты, созданные мастером, вы необходимо обновить до SQL Server Standard, Enterprise, Developer или Evaluation. «Пакет» — это все настройки, которые вы настроили до сих пор.В коммерческие версии SQL Server, вы можете сохранить пакет для повторного использования, чтобы не нужно вводить все настройки при следующем запуске мастера. В Express (бесплатная) версия Microsoft SQL Server 2008 Management Studio , вы должны повторно вводить все параметры каждый раз, когда запускаете SQL Server Import и Мастер экспорта.

   8.Убедитесь, что пакет успешно выполнен.

   No related posts.