Access или Excel: связанные данные на учебном наборе

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

Плоское хранение и расчёт против реляционного хранения и связей

Microsoft в справке «Using Access or Excel to manage your data» описывает оба продукта как похожие по возможностям: они хранят данные, выполняют запросы и расчёты. Различие лежит в типе данных. Данные, которые умещаются в одну таблицу или лист, в справке названы плоскими, или неряляционными. Простой список клиентов с одним адресом и одним контактным лицом на клиента там отнесён к случаям Excel. Список со счетами и адресами доставки, с несколькими контактными лицами на клиента, отнесён к случаям Access.

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

В Access связи между таблицами строятся на общих полях. Справка «Create, edit or delete a relationship» объясняет: связь помогает объединять данные двух таблиц, а в запросе определяет, какие записи попадут в результат. Общие поля могут называться по-разному, но обязаны иметь один тип данных. Для пары AutoNumber и Number требуется одинаковое значение свойства FieldSize, например Long Integer.

Условия доступности различаются. Excel входит в подписки Microsoft 365 и доступен в вебе. Access входит только в отдельные планы Microsoft 365 и доступен в настольной версии Windows. Excel поддерживает одновременное совместное редактирование книги с разных устройств. Access применяется для структурных многопользовательских сценариев на Windows. Эти условия приведены в справке Microsoft по выбору программы.

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

Исходные значения учебного набора

Учебный вход задан полностью. Клиенты: C1 Анна, anna@example.test; C2 Борис, boris@example.test; C3 Вера, vera@example.test. Заказы: O1/C1/120, O2/C1/80, O3/C2/100, O4/C2/40, O5/C3/60, O6/C3/50. Суммы условные, в рублях. Ключи — CustomerID и OrderID.

Таблица клиентов:

Таблица заказов:

Обе таблицы плоские: в каждой строке один тип записи. Связь задаёт столбец CustomerID в заказах, который указывает на строку клиента. Значения email условные и служат адресом для проверки изменения в одном справочнике.

Из набора сразу видны величины, которые понадобятся для проверок. Уникальных OrderID шесть. Клиентов три. Сумма всех заказов равна 450. По клиентам: C1 — 200, C2 — 140, C3 — 110. Эти числа заданы входом и используются как эталон.

Заполненная матрица выбора

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

Строка про аналитическую выгрузку опирается на справку Microsoft: Access хранит данные, Excel их анализирует, и совместное применение даёт выгоду обоих продуктов. Строка про совместное редактирование опирается на условие хранения книги в OneDrive или SharePoint. Строка про одновременную работу опирается на описание Access как структурного многопользовательского сценария на Windows.

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

Условная рекомендация и условия её пересмотра

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

Пересмотр происходит при изменении трёх групп требований. Первая — совместное редактирование. Если несколько человек должны править одну книгу одновременно, работает Excel с хранением в OneDrive или SharePoint. Если несколько человек работают с набором данных и правят разные записи, применяется Access: в справке Microsoft сказано, что Access блокирует только редактируемую запись, и другие пользователи правят иные записи без конфликтов.

Вторая группа — целостность связей. Если потеря связи между заказом и клиентом недопустима, набор переносится в Access, и связь получает включённое Enforce Referential Integrity. Если связь допустимо проверять вручную, расчёт остаётся в Excel.

Третья группа — аналитика. Если нужны сценарии «что если», сводные отчёты и графики, применяется Excel: справка Microsoft относит к нему расширенные сводные отчёты и диаграммы, а также анализ вариантов. Если нужны частые выборки по меняющимся условиям, применяется Access с SQL-запросами.

Рекомендация пересматривается и при смене платформы. Access доступен в настольной версии Windows, поэтому работа на других устройствах меняет выбор. Excel доступен в вебе и в подписках Microsoft 365.

Эталон и проверки набора

Эталон задан входом: общий итог 450, по клиентам C1=200, C2=140, C3=110, уникальных OrderID шесть, клиентов три. Проверки выполняет читатель на своих копиях таблиц.

Проверка первая — совпадение общего итога и суммы клиентских итогов. Складываем 200, 140 и 110, получаем 450. Складываем суммы заказов 120, 80, 100, 40, 60 и 50, получаем 450. Совпадение подтверждает согласованность итогов; принадлежность каждого заказа клиенту проверяйте отдельно по CustomerID и списку исходных строк.

Проверка вторая — отсутствие неизвестных CustomerID. Каждое значение CustomerID в таблице заказов обязано найти строку в таблице клиентов. Значения C1, C2 и C3 находятся. Если появляется значение, которого нет в справочнике, проверка его показывает.

Проверка третья — изменение email C1 выполняется в одном справочнике. Меняем anna@example.test в таблице клиентов. В таблице заказов email не хранится, поэтому правка затрагивает одну строку. Это следствие разделения таблиц по типу данных.

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

Проверки опираются на структуру, описанную в справке Microsoft: уникальные идентификаторы сохраняют целостность данных и ускоряют поиск и сортировку. В Access для генерации идентификатора применяется тип AutoNumber.

Отрицательный вход O7/C9/10 и внешние ключи

Отрицательный вход: заказ O7 с CustomerID C9 и суммой 10. Значение C9 отсутствует в справочнике клиентов. Проверка отсутствующего CustomerID находит такую строку: она ссылается на клиента, которого нет.

В Access эту ситуацию закрывает связь с включённым Enforce Referential Integrity. Справка Microsoft описывает referential integrity как требование, при котором новая запись в таблице заказов обязана иметь соответствующее значение в таблице клиентов. Тогда запись без клиента не создаётся. Важное уточнение: линия связи сама по себе этого не гарантирует. При создании связи через Field List и Lookup Wizard связь появляется без включённого Enforce Referential Integrity, и её нужно открыть и включить отдельно. При создании через окно Relationships флажок Enforce Referential Integrity выставляется в диалоге Edit Relationships до нажатия Create.

В Excel внешние ключи автоматически не обеспечиваются. Справка Microsoft прямо указывает: Excel не поддерживает реляционные данные и не поддерживает referential integrity. Для контроля ввода там есть команда Data Validation, которая задаёт правила ввода. Она ограничивает значения в столбце, и её настройка остаётся на авторе таблицы.

Практический порядок для набора: перенести таблицы в Access, открыть окно Relationships, добавить обе таблицы, перетащить CustomerID из клиентов в CustomerID заказов, в диалоге Edit Relationships включить Enforce Referential Integrity и нажать Create. После этого заказ O7 с C9 не создастся. Если строка уже есть, её находит проверка отсутствующего CustomerID.

План проверки читателя

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

Шаг первый. Создать две таблицы с полями из набора: клиенты с CustomerID, именем и email; заказы с OrderID, CustomerID и суммой. Внести три строки клиентов и шесть строк заказов.

Шаг второй. Посчитать суммы по клиентам и общий итог. Сверить с эталоном: 200, 140, 110 и 450.

Шаг третий. Проверить уникальность OrderID: шесть строк, шесть различных значений.

Шаг четвёртый. Проверить отсутствие неизвестных CustomerID в таблице заказов.

Шаг пятый. Изменить email C1 в таблице клиентов и убедиться, что правка одна.

Шаг шестой. Добавить строку O7 с CustomerID C9 и суммой 10. В Excel она пройдёт, если не настроена проверка ввода. В Access со включённым Enforce Referential Integrity она не создастся.

Шаг седьмой. Построить аналитическую выгрузку: подключить Excel к данным Access и собрать сводку по клиентам.

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

Как записать результат проверки набора

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

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

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

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

Частые вопросы

Access или Excel: что выбрать для связанных данных?

Для единичного сводного расчёта подходит Excel. Для справочника клиентов и заказов, где потеря связи недопустима, подходит Access. Аналитическую выгрузку удобно делать в Excel поверх данных Access.

Excel умеет обеспечивать внешние ключи?

Excel не поддерживает реляционные данные и referential integrity, поэтому внешние ключи там не обеспечиваются автоматически. Контроль ввода задаётся командой Data Validation, и её настройка остаётся на авторе таблицы.

Как в Access запретить заказ без клиента?

Нужно создать связь между таблицами по общему полю CustomerID и включить Enforce Referential Integrity в диалоге Edit Relationships. Тогда запись заказа без существующего клиента не создастся. Линия связи без этого флажка такой запрет не даёт.

Как проверить, что суммы посчитаны верно?

Сложите суммы по клиентам и сравните с общим итогом. На учебном наборе 200, 140 и 110 дают 450, и сумма шести заказов тоже равна 450. Это проверяет согласованность итогов; принадлежность каждого заказа клиенту проверяйте отдельно по CustomerID и исходным строкам.

Где менять email клиента?

Email хранится в таблице клиентов, поэтому правка выполняется в одном справочнике. В таблице заказов email не дублируется, и изменение C1 затрагивает одну строку. Так работает разделение таблиц по типу данных.