Как синхронизировать таблицы в excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Начало работы со связями между таблицами
Главное достоинство реляционных баз данных заключается в возможности использовать информацию из разных таблиц. Для этого сначала требуется создать между ними связи. После этого вы сможете объединять эти данные в запросах, формах и отчетах.
Чтобы увидеть все связи в базе данных, откройте шаблон Access, а затем на вкладке Работа с базами данных нажмите кнопку Схема данных.
Примечание: Если вы открыли пустую базу данных или не еще не создали связей между таблицами, в Access вам будет предложено добавить таблицу или запрос. Для создания связи у вас должны быть хотя бы две таблицы. Лучше всего сразу создать все нужные таблицы. Дополнительные сведения см. в статьях Создание связей с помощью мастера подстановок и Создание связей в области «Схема данных».
Описание области «Схема данных»
Линии в представлении «Отношения» указывают на связи между таблицами. На рисунке ниже таблица слева является родительской. Таблица справа является детской. Линия между ними соединяет поля (в данном случае — «ИД заказа» и «ИД товара»), используемые для совпадения данных.
По линиям и символам можно определить параметры связи.
- Толстая соединительная линия означает, что включено обеспечение целостности данных. Это хорошо. Данные будут синхронизироваться.
- На приведенном изображении цифра 1 означает, что в таблице слева может быть только одна связанная запись. В таблице «Заказы» каждому заказу может соответствовать только одна запись.
- Значок «∞» означает, что в нескольких записях может быть указан одинаковый номер или код. Заказ из таблицы слева, определяемый номером заказа, может быть указан в таблице «Сведения о заказах» несколько раз, поскольку в одном заказе может быть несколько продуктов.
Виды связей между таблицами
Между таблицами могут быть установлены связи трех видов:
- Один-к-одному. Каждый элемент используется в каждой таблице только один раз. Например, каждый сотрудник может использовать только один служебный автомобиль. Дополнительные сведения см. в статье Создание связей типа «один-к-одному».
- Один-ко-многим. Для одного элемента из первой таблицы можно создать связь с несколькими элементами из второй таблицы. Например, в каждой накладной может быть указано несколько продуктов.
- Многие-ко-многим. Для одного или нескольких элементов из первой таблицы можно создать связь с одним или несколькими элементами из второй таблицы. Например, в каждый заказ может входить несколько продуктов, и каждый продукт может быть указан в нескольких заказах. Дополнительные сведения см. в статье Создание связей типа «многие-ко-многим».
Связи типа «один ко многим»
Связи типа «один-ко-многим» — одни из наиболее распространенных в хорошо структурированных базах данных.
Связи между таблицами обычно определяются первичным ключом в одной из них. Первичный ключ служит уникальным идентификатором каждой записи (часто числовым). Чтобы показать, что сведения в двух таблицах связаны, с помощью первичного ключа обычно создается связь, исходящая из одной таблицы.
В показанной ниже связи у каждого человека из таблицы «Контакты» есть идентификатор, представляющий собой первичный ключ (он отмечен значком ключа). Этот идентификатор также используется в поле «Владелец» в таблице «Активы». Чтобы написать электронное письмо человеку, связанному с активом, следует использовать значение поля «Адрес электронной почты». Для этого необходимо узнать значение поля «Владелец» из таблицы «Активы», а затем найти этот идентификатор в таблице «Контакты». Число 1 на одном конце соединительной линии и знак «∞» на другом означают, что это связь типа «один-ко-многим», поэтому один контакт может быть связан с несколькими активами.
Изменение связи
Если вы работаете с существующей базой данных или создали базу из шаблона, вы можете изменить связь нужным образом.
Примечание: Если необходимые таблицы открыты, сначала следует закрыть их, а также любые открытые объекты, которые их используют.
- Выберите Работа с базами данных >Схема данных.
- Выберите линию, соединяющую две связанные таблицы.
Совет: Если необходимая связь отсутствует, на вкладке Конструктор в группе Связи нажмите кнопку Все связи.
| Обеспечение целостности данных | Выберите этот пункт, чтобы избежать ошибок в данных и поддерживать синхронизацию сведений с помощью связи. Например, предположим, что у вас есть связь типа «один-к-одному» между таблицами «Сотрудники» и «Льготы сотрудников». Если сотрудник уволится и вы удалите его из таблицы «Сотрудники», соответствующая запись в таблице «Льготы сотрудников» тоже удалится. Иногда не имеет смысла применять обеспечение целостности данных. Предположим, у вас есть связь «один-к-многим» между «Грузоотправителями» и «Заказы». Вы удаляете грузоотправителя, и он сопопосывается с заказами в таблице «Заказы». Эти заказы становятся потерянными, то есть по-прежнему содержат ИД грузоотправителя, но он не является допустимым, так как запись, на которую он ссылается, больше не существует. |
| Каскадное обновление связанных полей | Установите этот флажок, чтобы данные в связанных полях обновлялись во всех связанных таблицах. Предположим, вам нужно просто изменить код поставщика. Если у вас установлен этот флажок, код поставщика обновится не только в таблице «Поставщики», но и в других связанных с ней таблицах, в которых также используется это значение кода (например, в таблице «Заказы»). |
| Каскадное удаление связанных записей | Установите этот флажок, если при удалении записей вам требуется удалять и связанные записи из других таблиц. Предположим, вы удалили грузоотправителя. Если выбран этот параметр, Access удаляет все записи во всех таблицах, ссылаясь на этот ИД грузоотправителя, включая все заказы (в таблице «Заказы»), отправленные этим грузоотправии. Этот параметр можно выбрать только в том случае, если вы уверены, что хотите удалить историю заказов. |
Удаление отношения между таблицами
Примечание: Если необходимые таблицы открыты, сначала следует закрыть их, а также любые открытые объекты, которые их используют.
Удаление связи между таблицами
- Выберите Работа с базами данных >Схема данных.
- Выберите линию, соединяющую две связанные таблицы.
Совет: Если необходимая связь отсутствует, на вкладке Конструктор в группе Связи нажмите кнопку Все связи.
Примечание: При удалении связи для нее также отключается обеспечение целостности данных, если оно было включено. В результате Access больше не будет предотвращать изменения, ведущие к появлению неполных записей со стороны многих записей в связи типа «один-ко-многим».
Как в Excel 2010 синхронизировать две сводные таблицы при помощи одного раскрывающегося списка
Отчет, показанный на рис. 11.15, содержит две сводные таблицы. Каждая сводная таблица имеет поле страниц, позволяющее выбирать рынок сбыта. Проблема в том, что каждый раз при выборе рынка в поле страниц одной сводной таблицы вам приходится выбирать тот же рынок в поле страниц другой сводной таблицы.
Синхронизация данных двух таблиц на этапе анализа данных не представляет большую проблему, но существует вероятность, что вы или ваши клиенты все же забудут ее выполнить.
Рис 11.15. Две сводные таблицы содержат поля страниц, которые выполняют фильтрацию данных по рынкам. Для анализа данных отдельного рынка нужно синхронизировать обе сводные таблицы
Один из способов синхронизации этих сводных таблиц состоит в использовании раскрывающегося списка. Идея заключается в записи макроса, который выбирает нужный рынок из поля Рынок сбыта в обеих таблицах. Затем нужно создать раскрывающееся меню и заполнить его названиями рынков из двух сводных таблиц. И наконец, записанный макрос необходимо изменить для фильтрации обеих сводных таблиц, используя значения из раскрывающегося списка.
Для решения этой задачи нужно выполнить следующие действия.
- Создайте новый макрос и присвойте ему имя SynchMarkets. Когда начнется запись, выберите в поле Рынок сбыта обеих сводных таблиц рынок сбыта Калифорния и остановите запись.
- Отобразите на экране палитру элементов управления формы и добавьте на рабочий лист раскрывающееся меню.
- Создайте жестко определенный список всех рынков сводной таблицы. Заметьте, что первым элементом списка указывается значение (Все). Вы должны включить этот элемент, если хотите иметь возможность выбирать в раскрывающемся меню все рынки. На этом этапе отчет сводной таблицы должен выглядеть так, как показано на рис. 11.16.
Рис 11.16. В вашем распоряжении есть все требуемые средства: макрос, изменяющий поле Рынок сбыта обеих сводных таблиц, раскрывающееся меню и список всех рынков сбыта, содержащихся в сводной таблице
Рис. 11.17. Настройки раскрывающегося меню должны указывать на список рынков как на исходный диапазон значений, а в качестве точки связывания — определять ячейку Н1
Теперь у вас появилась возможность выбирать в раскрывающемся меню рынок, а также определять связанный с ним порядковый номер в ячейке H1 (рис. 11.18). Возникает вопрос: зачем вместо реального имени рынка используется его индексное значение? Потому что раскрывающееся меню возвращает не имя, а номер. Например, при выборе в раскрывающемся меню имени Шарлотта оно возвращает в ячейку H1 значение 5. Это означает, что Шарлотта является пятым элементом списка.
Рис. 11.18. Раскрывающееся меню теперь заполняется названиями рынков с выводом порядкового номера выбранного рынка в ячейке H1
Рис. 11.19. Функция индекс в ячейке 11 преобразует порядковый номер в ячейке H1 в значение. В конечном счете вы будете использовать значение в ячейке 11 для изменения макроса
Рис. 11.20. Чтобы получить доступ к VBA-коду макроса, выберите макрос SynchMarkets и щелкните на кнопке Изменить
При записи макроса вы выбрали в обеих сводных таблицах рынок сбыта Калифорния из поля Market (Рынок сбыта). Как видно из рис. 11.21, рынок Калифорния теперь жестко задан в VBA-коде макроса. Замените значение «Калифорния» выражением ActiveSheet.Range(«I1»).Value, которое ссылается на значение в ячейке I1.
Рис. 11.21. Рынок сбыта Калифорния жестко задан в VBA-коде записанного макроса
Рис. 11.22. Замените значение «Калифорния» выражением Activesheet.Range(«I1»).Value и закройте редактор Visual Basic
На рис. 11.23 показан окончательный результат. Вы получили пользовательский интерфейс, позволяющий клиентам выбирать рынок сбыта в обеих сводных таблицах с помощью единственного раскрывающегося списка.
Рис. 11.23. Отчет сводной таблицы, готовый к использованию
При выборе в раскрывающемся списке нового элемента размеры столбцов автоматически изменяются, чтобы вместить все отображаемые в них данные. Подобное поведение программы порядком надоедает при форматировании шаблона рабочего листа. Можно предотвратить его, если щелкнуть на сводной таблице правой кнопкой мыши и выбрать команду Параметры сводной таблицы (PivotTable Options). На экране появится одноименное диалоговое окно, в котором необходимо сбросить флажок Автоматически изменять ширину столбцов при обновлении (Autofit Column Widths on Update).
Объединение двух или нескольких таблиц
Вы можете объединить строки из одной таблицы в другую, просто вклеив данные в первые пустые ячейки под целевой таблицей. Таблица увеличится в размере, чтобы включить в нее новые строки. Если строки в обеих таблицах совпадают, вы можете объединить столбцы одной таблицы с другой, вклеив их в первые пустые ячейки справа от таблицы. В этом случае таблица также увеличится вместить новые столбцы.
Слияние строк на самом деле очень просто, но слияние столбцов может быть непросто, если строки одной таблицы не соответствуют строкам в другой таблице. Некоторых проблем с выравниванием можно избежать, если воспользоваться функцией ВПР.
Объединение двух таблиц с помощью функции ВЛОП
В приведенного ниже примере вы увидите две таблицы с другими именами: «Синяя» и «Оранжевая». В таблице «Синяя» каждая строка представляет собой позицию заказа. Например, заказ № 20050 содержит две позиции, № 20051 — одну, № 20052 — три и т. д. Мы хотим объединить столбцы «Код продажи» и «Регион» с таблицей «Синяя» с учетом соответствия значений в столбце «Номер заказа» таблицы «Оранжевая».
Значения «ИД заказа» повторяются в таблице «Синяя», но значения «ИД заказа» в таблице «Оранжевая» уникальны. Если просто скопировать и ввести данные из таблицы «Оранжевая», значения «ИД продаж» и «Регион» для второй строки заказа 20050 будут отключены на одну строку, что изменит значения в новых столбцах таблицы «Синяя».
Вот данные для таблицы «Синяя», которую можно скопировать на пустой лист. После в таблицы нажмите CTRL+T, чтобы преобразовать ее в таблицу, а затем переименуйте таблицу Excel синюю.
Вот данные для таблицы «Оранжевая». Скопируйте его на тот же самый таблицу. После в таблицы нажмите CTRL+T, чтобы преобразовать ее в таблицу, а затем переименуйте таблицу в Оранжевая.
Нам необходимо обеспечить правильное выравнивание значений «ИД продаж» и «Регион» для каждого заказа с каждым уникальным элементом строки заказа. Для этого впустим заголовки таблицы «ИД продажи» и «Регион» в ячейки справа от таблицы «Синяя», а затем с помощью формулЫ ВЗ ПРОСМОТР выберем правильные значения из столбцов «ИД продажи» и «Регион» таблицы «Оранжевая».
Вот как это сделать.
- Скопируйте заголовки «ИД продажи» и «Регион» в таблице «Оранжевая» (только эти две ячейки).
- В ячейку справа от заголовка «ИД товара» таблицы «Синяя». Теперь таблица «Синяя» содержит пять столбцов, включая новые — «Код продажи» и «Регион».
- В таблице «Синяя», в первой ячейке столбца «Код продажи» начните вводить такую формулу: =ВПР(
- В таблице «Синяя» выберите первую ячейку столбца «Номер заказа» — 20050. Частично заполненная формула выглядит так:
Выражение [@[Номер заказа]] означает, что нужно взять значение в этой же строке из столбца «Номер заказа». Введите точку с запятой и выделите всю таблицу «Оранжевая» с помощью мыши. В формулу будет добавлен аргумент Оранжевая[#Все]. - Введите точку с запятой, число 2, еще раз точку с запятой, а потом 0, вот так: ;2;0
- Нажмите клавишу ВВОД, и законченная формула примет такой вид:
Выражение Оранжевая[#Все] означает, что нужно просматривать все ячейки в таблице «Оранжевая». Число 2 означает, что нужно взять значение из второго столбца, а 0 — что возвращать значение следует только в случае точного совпадения. Обратите внимание: Excel заполняет ячейки вниз по этому столбцу, используя формулу ВПР. - Вернитесь к шагу 3, но в этот раз начните вводить такую же формулу в первой ячейке столбца «Регион».
- На шаге 6 вместо 2 введите число 3, и законченная формула примет такой вид:
Между этими двумя формулами есть только одно различие: первая получает значения из столбца 2 таблицы «Оранжевая», а вторая — из столбца 3. Теперь все ячейки новых столбцов в таблице «Синяя» заполнены значениями. В них содержатся формулы ВПР, но отображаются значения. Возможно, вы захотите заменить формулы ВПР в этих ячейках фактическими значениями. - Выделите все ячейки значений в столбце «Код продажи» и нажмите клавиши CTRL+C, чтобы скопировать их.
- На вкладке Главная щелкните стрелку под кнопкой Вставить.
- В коллекции параметров вставки нажмите кнопку Значения.
- Выделите все ячейки значений в столбце «Регион», скопируйте их и повторите шаги 10 и 11. Теперь формулы ВПР в двух столбцах заменены значениями.
Дополнительные сведения о таблицах и функции ВПР
- Как добавить или удалить строку или столбец в таблице
- Использование структурированных ссылок в формулах таблиц Excel
- Использование функции ВПР (учебный курс)
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.