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

Содержание

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

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

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

В этой статье

Общее представление об импорте данных из Excel

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

При импорте данных в Access создается их копия в новой или существующей таблице, а исходный лист Excel не изменяется.

Стандартные сценарии импорта данных Excel в Access

  • Опытному пользователю Excel требуется использовать Access для работы с данными. Для этого необходимо переместить данные из листов Excel в одну или несколько новых таблиц Access.

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

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

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

Первый импорт данных из Excel

  • Сохранить книгу Excel в виде базы данных Access невозможно. В Excel не предусмотрена функция создания базы данных Access с данными Excel.

  • При открытии книги Excel в Access (для этого следует открыть диалоговое окно Открытие файла, выбрать в поле со списком

    Тип файлов значение Файлы Microsoft Office Excel и выбрать файл) создается ссылка на эту книгу, но данные из нее не импортируются. Связывание с книгой Excel кардинально отличается от импорта листа в базу данных. Дополнительные сведения о связывании см. ниже в разделе Связывание с данными Excel.

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

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

Подготовка листа

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

    Определение именованного диапазона (необязательно)   

    1. Перейдите в Excel и откройте лист, данные из которого нужно импортировать.

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

    3. Щелкните выделенный диапазон правой кнопкой мыши и выберите пункт Имя диапазона или Определить имя.

    4. В диалоговом окне Создание имени укажите имя диапазона в поле Имя и нажмите кнопку ОК.

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

  2. Просмотрите исходные данные и выполните необходимые действия в соответствии с приведенной ниже таблицей.

    Элемент

    Описание

    Число столбцов

    Число исходных столбцов, которые необходимо импортировать, не должно превышать 255, т.  к. Access поддерживает не более 255 полей в таблице.

    Пропуск столбцов и строк

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

    Смещ_по_строкам    В ходе операции импорта невозможно фильтровать или пропускать строки.

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

    Табличный формат

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

    Пустые столбцы, строки и ячейки

    Удалите все лишние пустые столбцы и строки из листа или диапазона. При наличии пустых ячеек добавьте в них отсутствующие данные. Если планируется добавлять записи к существующей таблице, убедитесь, что соответствующие поля таблицы допускают использование пустых (отсутствующих или неизвестных) значений. Поле допускает использование пустых значений, если свойство Обязательное поле (Required) имеет значение Нет, а свойство Условие на значение (ValidationRule) не запрещает пустые значения.

    Значения ошибок

    Если одна или несколько ячеек на листе или в диапазоне содержат значения ошибок, например #ЧИСЛО или #ДЕЛ, исправьте эти значения до начала операции импорта.

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

    Тип данных

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

    Рекомендуется также отформатировать все исходные столбцы в Excel и назначить им определенный формат данных перед началом операции импорта. Форматирование является необходимым, если столбец содержит значения с различными типами данных. Например, столбец «Номер рейса» может содержать числовые и текстовые значения, такие как 871, AA90 и 171. Чтобы исключить отсутствующие или неверные значения, выполните указанные ниже действия.

    1. Щелкните заголовок столбца правой кнопкой мыши и выберите пункт Формат ячеек.

    2. На вкладке Числовой в группе Категория выберите формат. Для столбца «Номер рейса» лучше выбрать значение Текстовый.

    3. Нажмите кнопку ОК.

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

    Первая строка

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

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

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

Подготовка конечной базы данных

  1. Откройте базу данных Access, в которой будут храниться импортируемые данные. Убедитесь, что база данных доступна не только для чтения и что есть права на ее изменение.

    -или-

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

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

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

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

    Добавление в существующую таблицу.    При добавлении данных в существующую таблицу строки из листа Excel добавляются в указанную таблицу.

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

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

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

      Совет: Поле допускает использование пустых значений, если его свойство Обязательное поле (Required) имеет значение Нет, а свойство Условие на значение (ValidationRule) не запрещает пустые значения.

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

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

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

Запуск операции импорта

  1. Расположение мастера импорта или связывания зависит от используемой версии Access. Выполните действия, которые соответствуют вашей версии Access.

    • Если вы используете последнюю версию Microsoft 365 подписки Access или Access 2019, на вкладке Внешние данные в группе Импорт & выберите пункт новый источник данных > из файла > Excel.

    • Если вы используете Access 2016, Access 2013 или Access 2010, на вкладке Внешние данные в группе Импорт и связи нажмите кнопку Excel.

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

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

    -или-

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

  3. Укажите способ сохранения импортируемых данных.

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

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

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

  4. Нажмите кнопку ОК.

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

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

  1. На первой странице мастера выберите лист, содержащий данные, которые необходимо импортировать, и нажмите кнопку Далее.

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

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

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

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

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

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

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

    • Чтобы создать индекс для поля, присвойте свойству Индексировано (Indexed) значение Да.

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

      Настроив параметры, нажмите кнопку Далее.

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

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

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

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

Сведения о том, как сохранить спецификацию, см. в статье Сохранение параметров операции импорта или экспорта в виде спецификации.

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

Сведения о том, как запланировать выполнение задач импорта и связывания в определенное время, см. в статье Планирование спецификации импорта или экспорта.

Разрешение вопросов, связанных с отсутствующими и неверными значениями

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

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

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

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

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

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

Проблема

Решение

Графические элементы

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

Вычисляемые значения

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

Значения TRUE или FALSE и -1 или 0

Если исходный лист или диапазон включает столбец, который содержит только значения TRUE или FALSE, в Access для этого столбца создается логическое поле, в которое вставляется значение -1 или 0. Если же исходный лист или диапазон включает столбец, который содержит только значения -1 и 0, в Access для этого столбца по умолчанию создается числовое поле. Чтобы избежать этой проблемы, можно изменить в ходе импорта тип данных поля на логический.

Многозначные поля

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

Усеченные данные

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

Формат отображения

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

  • После завершения импорта в логическом поле в режиме таблицы отображаются значения -1 и 0. Чтобы устранить эту проблему, необходимо после завершения импорта изменить значение свойства Формат этого поля на Да/Нет для отображения флажков.

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

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

Повторяющиеся значения (нарушение уникальности ключа)

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

Значения дат, сдвинутые на 4 года

Значения полей дат, импортированных с листа Excel, оказываются сдвинуты на четыре года. В Excel для Windows используется система дат 1900, в которой даты представляются целыми числами от 1 до 65 380, соответствующими датам от 1 января 1900 г. до 31 декабря 2078 г. В Excel для Macintosh используется система дат 1904, в которой даты представляются целыми числами от 0 до 63 918, соответствующими датам от 1 января 1904 г. до 31 декабря 2078 г.

Прежде чем импортировать данные, измените систему дат для книги Excel или выполните после добавления данных запрос на обновление, используя выражение [имя поля даты] + 1462 для корректировки дат.

Пустые значения

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

  • Отформатируйте исходные столбцы.

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

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

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

Тип отсутствующих значений

Конечный объект импорта

Тип целевого поля

Решение

Текст

Новая таблица

Поле даты

Замените все текстовые данные значениями дат и повторите попытку импорта.

Текст

Существующая таблица

Числовое поле или поле даты

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

Значения дат, замененные числовыми значениями

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

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

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

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

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

Числовые значения, замененные значениями даты

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

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

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

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

Кроме того, может потребоваться просмотреть таблицу ошибок, о которой говорится на последней странице мастера, в режиме таблицы. Эта таблица содержит три поля: «Ошибка», «Поле» и «Строка». В каждой ее строке содержатся сведения об определенной ошибке, и данные в поле «Ошибка» можно использовать при разрешении вопросов.

Строки ошибок и советы по устранению неполадок

Ошибка

Описание

Обрезка полей

Значение в поле превышает размер, заданный свойством FieldSize этого поля.

Ошибка преобразования типа

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

Нарушение уникальности ключа

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

Нарушение условия на значение

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

Пустое значение в обязательном поле

Данное поле не может иметь пустое значение, поскольку его свойство Обязательное поле (Required) имеет значение Да.

Пустое значение в поле счетчика

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

Неинтерпретируемая запись

Текстовое значение содержит знак разделителя (как правило, это прямые кавычки). Если значение содержит знак разделителя, он должен содержаться в текстовом файле дважды, например:

Диаметр 4 1/2″»

К началу страницы

Связывание с данными Excel

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

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

Стандартные сценарии связывания с листом Excel из Access

Обычно связывание с листом Excel (вместо импорта) выполняют по указанным ниже причинам.

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

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

Первое связывание с листом Excel

  • В Excel невозможно создать связь с базой данных Access.

  • При связывании с файлом Excel в Access создается новая таблица, которая называется также связанной таблицей. В ней отображаются данные исходного листа или именованного диапазона; при этом они не сохраняются в базе данных.

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

  • База данных может содержать несколько связанных таблиц.

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

  • При открытии книги Excel в Access (для этого в диалоговом окне Открытие файла необходимо выбрать в поле со списком Тип файлов значение Microsoft Excel и выбрать нужный файл) в Access создается пустая база данных и автоматически запускается мастер связи с электронной таблицей.

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

    Создание именованного диапазона в Excel (необязательно — целесообразно лишь для связывания отдельных данных на листе)   

    1. Перейдите в Excel и откройте лист, в котором нужно определить именованный диапазон.

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

    3. Щелкните выделенный диапазон правой кнопкой мыши и выберите пункт Имя диапазона или Определить имя.

    4. В диалоговом окне Создание имени укажите имя диапазона в поле Имя и нажмите кнопку ОК.

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

  2. Просмотрите исходные данные и выполните необходимые действия в соответствии с приведенной ниже таблицей.

    Элемент

    Описание

    Табличный формат

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

    Пропуск столбцов и строк

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

    Число столбцов

    Число исходных столбцов не должно превышать 255, т.к. Access поддерживает не более 255 полей в таблице.

    Пустые столбцы, строки и ячейки

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

    Значения ошибок

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

    Тип данных

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

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

    1. Щелкните столбец правой кнопкой мыши и выберите пункт Формат ячеек.

    2. На вкладке Числовой в группе Категория выберите формат.

    3. Нажмите кнопку ОК.

    Первая строка

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

  3. Закройте исходный файл, если он открыт.

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

  2. Если вы не хотите хранить ссылку ни в одной из существующих баз данных, создайте пустую базу данных: Откройте вкладку файл , нажмите кнопку создатьи выберите вариант пустая база данных. Обратите внимание: если вы используете Access 2007, нажмите кнопку Microsoft Office и выберите пункт Создать.

После этого можно начать операцию связывания.

Создание связи
  1. Расположение мастера импорта или связывания зависит от используемой версии Access. Выполните действия, которые соответствуют вашей версии Access.

    • Если вы используете последнюю версию Microsoft 365 подписки Access или Access 2019, на вкладке Внешние данные в группе Импорт & выберите пункт новый источник данных > из файла > Excel.

    • Если вы используете Access 2016, Access 2013 или Access 2010, на вкладке Внешние данные в группе Импорт и связи нажмите кнопку Excel.

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

  2. В диалоговом окне Внешние данные — Электронная таблица Excel в поле Имя файла укажите имя исходного файла Excel.

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

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

  4. На первой странице мастера выберите лист или именованный диапазон с нужными данными и нажмите кнопку Далее.

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

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

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

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

К началу страницы

Разрешение вопросов, связанных со значением #Число! и другими неверными значениями в связанной таблице

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

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

Проблема

Решение

Графические элементы

Графические элементы листов Excel, такие как логотипы, диаграммы и рисунки, не могут быть включены в связь с Aсcess.

Формат отображения

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

Вычисляемые значения

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

Усеченные текстовые значения

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

Сообщение о переполнении числового поля

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

Значения TRUE или FALSE и -1 или 0

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

Многозначные поля

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

#Число!

В Access значение ошибки #Число! отображается в полях вместо данных в указанных ниже случаях.

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

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

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

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

  1. Убедитесь, что исходный столбец не содержит значения с разными типами данных.

  2. Отформатируйте столбцы на листе Excel.

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

Числовые значения, замененные значениями дат

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

Значения дат, замененные числовыми значениями

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


К началу страницы

Что такое база данных? Отличие Access от Excel.

Access 2010 — это программа создания и управления базами данных. Чтобы понять Access, вы должны сначала понять базы данных.

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

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

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

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

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

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

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

Если вы знакомы с другими программами в пакете Microsoft Office, это может напоминать вам Excel, что позволяет организовать данные аналогичным образом. Фактически, вы можете создать аналогичную таблицу в Excel.

Зачем использовать базу данных?

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

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

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

Отличие Access от Excel

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

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

 С уважением, авторы сайта Компьютерапия


Понравилась статья? Поделитесь ею с друзьями и напишите отзыв в комментариях!


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

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

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

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

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

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

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



Рис. 1.

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

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

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

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

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

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

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

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

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

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

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

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

Импорт Excel в базу данных QuintaDB
  • Создайте Excel файл с данными;
  • После регистрации, создайте проект и  нажмите «Импорт данных».

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

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

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

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

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

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

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

Импорт CSV в базу данных 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:

XLTools.ExtractTable
WorkbookFile:source.xls
Range:LABCODE!E:F
SkipTopRowsCount:4
ApplyTableName:LabCodes
Headers:lab,stat
PreserveFormat:TRUE
OutputTo:NewHiddenSheet[LabCodes]

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

  • Книга под названием “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:

XLTools.SQLSelect
SQLQuery:SELECT
tbl2.[lab] as [ic_code],
tbl1.[Nb] as [rank],
tbl1.[%] as [percent],
tbl1.[N°] as [lab_code],
CASE WHEN tbl1.[sign] = ‘-‘ THEN -1 * tbl1.[d] ELSE tbl1.[d] END as [dev],
tbl1.[Sd] as [s_dev],
tbl1.[D2] as [dist],
tbl1.[Method] as [method],
FROM [source.xls].[Table1]tbl1
LEFT JOIN [LabCodes]tbl2 ON tbl1.[Nb] = tbl2.[stat]
PreserveFormat:TRUE
ApplyTableName:data
OutputTo:NewSheet[data]

Укажите, какие поля нужно выбрать (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:

XLTools.SQLSelect
Tables:zscore-fix,zscore-pt,outliers,repeatability,data
Server:Имя сервера или IP адрес сервера
Database:Имя вашей БД
Uid:Имя пользователя для доступа в вашу БД
Pwd:Пароль для доступа в вашу БД
ApplyTableName:ExportResults
OutputTo:NewSheet[ExportResults]

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

  • Взять таблицы “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, которые можно публиковать, защищать и совместно использовать.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. Щелкните следующие ссылки, чтобы загрузить файлы, которые мы используем в этой серии руководств.Загрузите каждый из четырех файлов в легкодоступное место, например, Загрузки или Мои документы , или в новую папку, которую вы создаете:
    > 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. Щелкните в любом месте сводной таблицы, чтобы убедиться, что выбрана сводная таблица Excel. В списке полей сводной таблицы , где развернута таблица Дисциплины , наведите указатель мыши на ее поле Дисциплина, и справа от поля появится стрелка раскрывающегося списка.Щелкните раскрывающийся список, нажмите (Выбрать все) , чтобы удалить все выбранные параметры, затем прокрутите вниз и выберите Стрельба из лука, Дайвинг, Фехтование, Фигурное катание и Конькобежный спорт. Нажмите ОК .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Город

NOC_CountryRegion

Альфа-2 Код

Издание

Сезон

Мельбурн / Стокгольм

AUS

AS

1956

Лето

Сидней

AUS

AS

2000

Лето

Инсбрук

AUT

AT

1964

Зима

Инсбрук

AUT

AT

1976

Зима

Антверпен

BEL

BE

1920

Лето

Антверпен

BEL

BE

1920

Зима

Монреаль

CAN

CA

1976

Лето

Лейк-Плэсид

CAN

CA

1980

Зима

Калгари

CAN

CA

1988

Зима

ул. Мориц

SUI

SZ

1928

Зима

Санкт-Мориц

SUI

SZ

1948

Зима

Пекин

ЧН

СН

2008

Лето

Берлин

GER

GM

1936

Лето

Гармиш-Партенкирхен

GER

GM

1936

Зима

Барселона

ESP

SP

1992

Лето

Хельсинки

FIN

FI

1952

Лето

Париж

FRA

FR

1900

Лето

Париж

FRA

FR

1924

Лето

Шамони

FRA

FR

1924

Зима

Гренобль

FRA

FR

1968

Зима

Альбервиль

FRA

FR

1992

Зима

Лондон

ГБР

Великобритания

1908

Лето

Лондон

ГБР

Великобритания

1908

Зима

Лондон

ГБР

Великобритания

1948

Лето

Мюнхен

GER

DE

1972

Лето

Афины

GRC

GR

2004

Лето

Кортина д’Ампеццо

ITA

IT

1956

Зима

Рим

ITA

IT

1960

Лето

Турин

ITA

IT

2006

Зима

Токио

JPN

JA

1964

Лето

Саппоро

JPN

JA

1972

Зима

Нагано

JPN

JA

1998

Зима

Сеул

KOR

кс

1988

Лето

Мексика

МЕХ

MX

1968

Лето

Амстердам

NED

NL

1928

Лето

Осло

NOR

НЕТ

1952

Зима

Лиллехаммер

NOR

НЕТ

1994

Зима

Стокгольм

SWE

SW

1912

Лето

Сент-Луис

США

США

1904

Лето

Лос-Анджелес

США

США

1932

Лето

Лейк-Плэсид

США

США

1932

Зима

Скво-Вэлли

США

США

1960

Зима

Москва

УРС

RU

1980

Лето

Лос-Анджелес

США

США

1984

Лето

Атланта

США

США

1996

Лето

Солт-Лейк-Сити

США

США

2002

Зима

Сараево

ЮГ

Ю

1984

Зима

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  9. Нажмите ОК .

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

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

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

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

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

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

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

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

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

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

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