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

• таблица должна обязательно содержать заголовки столбцов. Эти заголовки должны располагаться строго в одной строке, не должны содержать объединенных и пустых ячеек.
• таблица должна быть неделимая, т.е. не должна содержать полностью пустых строк и столбцов, а так же объединенных ячеек
• в каждом столбце должна содержаться однотипная информация: если в столбце должны содержаться даты, значит кроме дат там не должно быть ничего другого; если в столбце числа(суммы, кол-во) — значит должны быть только числа. Не следует при отсутствии чисел оставлять ячейку пустой или ставить пробел. Вместо этого необходимо ставить 0.

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

Все функции из категории баз данных имеют три одинаковых аргумента:
Функция(база_данных; поле; критерий)
база_данных — ссылка на ячейки данных таблицы, включая заголовок(A6:E12).
поле — в данном аргументе можно записать как непосредственно текст с названием столбца в кавычках («Дерево», «Возраст» или «Урожай»), так и число, задающее положение столбца в таблице: 1 — для первого поля(столбца) в таблице «Дерево», 2 — для второго поля «Высота», 3 — для третьего поля «Возраст» и так далее.
критерий — ссылка на диапазон ячеек с условиями отбора(A1:F3). Функция отберет данные из таблицы, которые удовлетворяют условиям, указанным в ячейках критериев. В ссылке на критерии обязательно должны быть включены названия столбцов, для которых выполняется отбор данных.

• ДСРЗНАЧ(DAVERAGE) — Вычисляет среднее значение выбранных записей базы данных:
  =ДСРЗНАЧ(A6:E12;5;A1:F3)
  =ДСРЗНАЧ(A6:E12;»Прибыль»;A1:F3)
  =DAVERAGE(A6:E12,5,A1:F3)вернет значение 90 000р., т.к. сумма прибыли отобранных записей равна 180 000р., а всего отобрано 2 записи. 180 000/2 = 90 000.
• БСЧЁТ(DCOUNT) — Подсчитывает количество числовых ячеек в базе данных:
  =БСЧЁТ(A6:E12;5;A1:F3)
  =БСЧЁТ(A6:E12;»Прибыль»;A1:F3)
  =DCOUNT(A6:E12,5,A1:F3)вернет число 2, т.к. только две строки в таблице отвечают критериям
• БСЧЁТА(DCOUNTA) — Подсчитывает количество непустых ячеек в базе данных:
  =БСЧЁТА(A6:E12;4;A1:F3)
  =БСЧЁТА(A6:E12;»Прибыль»;A1:F3)
  =DCOUNTA(A6:E12,4,A1:F3)вернет 2, т. е. подсчитает в отвечающих критериям строках количество непустых ячеек в столбце «Прибыль»
• БИЗВЛЕЧЬ(DGET) — Извлекает из базы данных одну запись, удовлетворяющую заданному условию:
  =БИЗВЛЕЧЬ(A6:E12;5;A1:F3)
  =БИЗВЛЕЧЬ(A6:E12;»Прибыль»;A1:F3)
  =DGET(A6:E12,5,A1:F3)для заданных условий вернет значение ошибки #ЧИСЛО!(#NUM!), т.к. этим условиям отвечает более одной записи. Если же указать диапазон для критерия как:
  =БИЗВЛЕЧЬ(A6:E12;5;A1:F2)то функция вернет значение 75 000р., т.е. единственную запись о прибыли для Яблонь с высотой больше 3 и меньше 6 (в данный промежуток попадает лишь строка 10 — Яблона, высота 5)
• ДМАКС(DMAX) — Находит максимальное значение среди выделенных записей базы данных:
  =ДМАКС(A6:E12;5;A1:F3)
  =ДМАКС(A6:E12;»Прибыль»;A1:F3)
  =DMAX(A6:E12,5,A1:F3)вернет сумму 105 000р., т.к. это максимальная прибыль из всех отвечающих критериям строк.
• ДМИН(DMIN) — Находит минимальное значение среди выделенных записей базы данных:
  =ДМИН(A6:E12;5;A1:F3)
  =ДМИН(A6:E12;»Прибыль»;A1:F3)
  
  =DMIN(A6:E12,5,A1:F3)вернет сумму 75 000р. , т.к. это минимальная прибыль из всех строк, отвечающих критериям
• БДПРОИЗВЕД(DPRODUCT) — Перемножает значения определенного поля в записях базы данных, удовлетворяющих условию:
  =БДПРОИЗВЕД(A6:E12;3;A1:F3)
  =БДПРОИЗВЕД(A6:E12;»Возраст»;A1:F3)
  =DPRODUCT(A6:E12,3,A1:F3)вернет 210, т.к. будут перемножены все значения столбца «Возраст», отвечающие критериям(14*15=210)
• ДСТАНДОТКЛ(DSTDEV) — Оценивает стандартное отклонение по выборке из выделенных записей базы данных:
  =ДСТАНДОТКЛ(A6:E12;4;A1:F3)
  =ДСТАНДОТКЛ(A6:E12;»Урожайность»;A1:F3)
  =DSTDEV(A6:E12,4,A1:F3)вернет 0,707107, т.е. оценку стандартного отклонения урожайности по указанным критериям.
• ДСТАНДОТКЛП(DSTDEVP) — Вычисляет стандартное отклонение по генеральной совокупности из выделенных записей базы данных:
  =ДСТАНДОТКЛП(A6:E12;4;A1:F3)
  =ДСТАНДОТКЛП(A6:E12;»Урожайность»;A1:F3)
  =DSTDEVP(A6:E12,4,A1:F3)вернет 0,5, т.е. точное стандартное отклонение урожайности по указанным критериям, если считать, что данные в базе данных описывают генеральную совокупность всех деревьев в саду.
• БДСУММ(DSUM) — Суммирует числа в поле для записей базы данных, удовлетворяющих условию:
  =БДСУММ(A6:E12;5;A1:F3)
  =БДСУММ(A6:E12;»Прибыль»;A1:F3)
  =DSUM(A6:E12,5,A1:F3)вернет сумму прибыли всех строк, отвечающих критериям, т.е. 180 000р.
  =БДСУММ(A6:E12;5;A1:A2)
  =DSUM(A6:E12,5,A1:A2)вернет сумму прибыли от всех Яблонь, т.е. 225 000р.
• БДДИСП(DVAR) — Оценивает дисперсию по выборке из выделенных записей базы данных:
  =БДДИСП(A6:E12;4;A1:A2)
  =БДДИСП(A6:E12;»Урожайность»;A1:A2)
  =DVAR(A6:E12,4,A1:A2)вернет 0,5, что будет оценкой дисперсии урожайности по указанным критериям, если считать, что данные в таблице являются выборкой из генеральной совокупности всех деревьев в саду
• БДДИСПП(DVARP) — Вычисляет дисперсию по генеральной совокупности из выделенных записей базы данных:
  =БДДИСПП(A6:E12;4;A1:A2)
  =БДДИСПП(A6:E12;»Урожайность»;A1:A2)
  =DVARP(A6:E12,4,A1:A2)вернет 10,66667, т.е. точную дисперсию урожайности Яблонь и Вишень, если считать, что данные в базе данных описывают генеральную совокупность всех деревьев в саду

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

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

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

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

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

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

Примечание. Все пакеты Microsoft Office включают Excel, но не все пакеты включают Access.

Сравните преимущества каждой программы

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

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

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

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

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

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

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

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

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

Моделирование     В Excel вы можете использовать инструменты анализа «что, если» для прогнозирования результатов модели рабочего листа. Анализ «что, если» позволяет вам запускать различные сценарии с вашими данными, такие как сценарии наилучшего и наихудшего случая, и сравнивать полученные данные нескольких сценариев в сводном отчете. Аналогичная функция недоступна в Access.

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

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

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

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

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

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

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

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

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

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

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

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

Дополнительные сведения о том, как защитить данные, см. в статьях Установка или изменение безопасности на уровне пользователя Access 2003 в Access 2007 или более поздней версии и Защита и безопасность в Excel.

Когда использовать Access

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

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

Используйте доступ, когда вы:

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

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

• Хотите выполнять сложные запросы.

• Хотите создавать различные отчеты или почтовые этикетки.

• org/ListItem»>

  Управление контактами     Вы можете управлять своими контактами и почтовыми адресами, а затем создавать отчеты в Access или объединять данные с Microsoft Office Word для печати стандартных писем, конвертов или почтовых наклеек.

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

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

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

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

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

• Отслеживание питания     Отслеживайте рецепты, записывайте диету и физические упражнения.

Когда использовать Excel

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

Используйте Excel, когда вы:

• org/ListItem»>

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

• Часто запускайте расчеты и статистические сравнения ваших данных.

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

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

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

• Хотите выполнять сложные операции анализа «что, если» с вашими данными, такие как статистический, инженерный и регрессионный анализ.

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

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

• org/ListItem»>

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

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

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

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

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

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

Полезные шаблоны Excel в любой из этих категорий см. в разделе Шаблоны для Excel в Microsoft Office Online.

Совместное использование Access и Excel

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

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

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

Как создать базу данных с возможностью поиска в Excel | Малый бизнес

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Введите ваши данные

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

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

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

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

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

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

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

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