Как сделать join в excel
Давайте предположим, что у меня есть некоторые данные в Excel (а не в реальной базе данных). На одном листе у меня есть данные, где один столбец работает как идентификатор, и я убедился, что значения в этом столбце уникальны. На другом листе у меня также есть некоторые данные, опять же с одним столбцом, который может быть взят в качестве идентификатора, и он также уникален. Если строка N на листе 1 имеет какое-то значение, а строка M на листе 2 имеет то же значение, я уверен, что строка N и строка M описывают один и тот же объект реального мира.
Что я спрашиваю: как я могу получить эквивалент полного внешнего соединения без написания макросов? Формулы и все функции, доступные через ленту, в порядке.
Небольшой пример "play data":
Желаемый вывод (сортировка не важна):
Всем, кто в ужасе от этого сценария: я знаю, что это неправильный способ сделать это. Если бы у меня был какой-либо выбор, я бы не стал использовать Excel для этого. Тем не менее, существует достаточно ситуаций, когда необходимо прагматичное решение, невозможно применить стат и лучшее (с точки зрения ИТ) решение.
Если вы сортируете оба списка и размещаете их рядом, будет довольно легко вручную сопоставить нужный столбец. Если у вас много данных, вы можете написать функцию, которая принимает два диапазона и делает это за вас.
@ Дэн, как я уже сказал, я прошу сделать это без написания функции . В ситуациях, когда я могу это написать, решение тривиально (по крайней мере, для меня, потому что я уже писал на VBA)
Excel поддерживает запросы SQL через соединения данных. Я ужасен в этом, поэтому я, к сожалению, не могу предложить гораздо больше советов, но, возможно, стоит обратить внимание на вас.
@Kyle Я добавил эту опцию в свой ответ. Это не для слабонервных и для этого может быть беспорядок, чтобы играть и / или полный перебор, но эй, почему бы и нет!
Сначала скопируйте / вставьте оба ключевых столбца из обеих таблиц в один новый лист в виде одного столбца.
Используйте «Удалить дубликаты», чтобы получить единый список всех ваших уникальных ключей.
Затем добавьте два столбца (в данном случае), по одному для каждого столбца данных в каждой таблице. Я также рекомендую использовать формат в качестве табличного параметра, поскольку он делает ваши формулы более привлекательными. Используя vlookup, используйте следующую формулу:
Это дает вам вашу итоговую таблицу.
Формулы результата ( Ctrl + ~ переключит это):
Вы также можете сделать это с помощью встроенного SQL-запроса. Это . гораздо менее удобно для пользователя, но, возможно, будет лучшим вариантом использования. Это, вероятно, потребует от вас отформатировать ваши «исходные» данные в виде таблиц.
- Нажмите на ячейку на новом листе
- Перейти к данным -> из других источников -> из запроса Microsoft
- Выберите файлы Excel * на вкладке Базы данных и нажмите ОК
- Выберите вашу рабочую книгу
- Выберите следующие четыре поля:
- Нажмите «Далее» и «ОК», чтобы увидеть красивое отформатированное предупреждение 1990-х годов.
- Следуя этим инструкциям, создайте первое левое внешнее соединение. В моем случае я использую таблицу «страны» в качестве левого источника и «имена» в качестве правого.
- Это дает только некоторые строки (так как вы присоединяетесь к ID)
Часть «создать вычитаемое соединение, а затем добавить его как объединение» более сложна ..
Вернитесь к первому внешнему соединению, которое вы создали. Вручную отредактируйте SQL и
- добавить Union в конец
- Добавьте приведенный выше текст объединения вычитания в конец объединения
- Вы можете отредактировать SQL, чтобы выбрать только конкретные данные, которые вы хотите на данный момент. Мне проще скрыть столбцы в результате.
Не для слабонервных. Но если вы хотите получить отличную возможность увидеть некоторые не обновленные до тех пор, пока вы можете быть живыми части Office, это отличный шанс.
У вас есть ссылка на нотацию [@ID]? Я не сомневаюсь, что это работает, но я никогда не видел это раньше, и я делаю много работы в Excel.
@TJL, если вы отформатируете таблицу как таблицу, она будет использовать такую запись для ссылок на другие столбцы. Гораздо понятнее, чем ссылки на ячейки. Это может быть функция 2010+? Я не уверен. Я не использовал магию форматирования как таблицы достаточно до 2010 года ..
Разве вы не можете просто изменить слово LEFT на слово FULL в исходном запросе? Я уверен, что он просто использует драйвер ACE OLEDB для выполнения этого запроса, который поддерживает синтаксис FULL JOIN.
В качестве альтернативного решения, могу ли я предложить Power Query ? Это бесплатная надстройка Excel от Microsoft, предназначенная для выполнения именно такого рода задач. Его функциональные возможности будут непосредственно включены в Excel 2016, так что он защищен от будущего.
В любом случае, с Power Query шаги довольно просты:
- Импортируйте обе таблицы как запросы в Power Query Editor.
- Выполните преобразование запросов слияния для них, установив соответствующий столбец соединения и установив тип соединения как Full Outer.
- Загрузите таблицу результатов на новый лист.
Хорошая вещь об этом - после того, как вы настроите это, если вы вносите изменения в свои базовые таблицы данных, вы просто нажимаете Данные> Обновить все, и ваш лист результатов Power Query также обновляется.
Очень интересно. Мой друг, владеющий рестораном, спрашивал меня о MS Office и показывал мне его набор на 2016 год, который не включает MS Access. Теперь я понимаю, почему это так. В Excel было много ограничений. Я никогда не покупал выше 2003 года, потому что ни одна из функций не имела ценности, и мне не понравился новый внешний вид; 2010 и выше. Access делает все это и даже больше, поэтому возникает вопрос: почему так много людей пытаются работать с базами данных в Excel, когда Access - это способ добиться этого? Единственное, что я вижу, это то, что, возможно, для того, чтобы получить хорошую работу с БД Access, ей также нужен хороший опыт работы с VB.
@ejbytes большинство людей понимают концепцию электронных таблиц. Большинство из них . не понимают концепции базы данных. У меня был проект по преобразованию проекта электронной таблицы в базу данных, и пользователи все еще называли его «электронной таблицей» - даже после подробных объяснений того, как работает база данных!
@enderland Я думаю, ты прав. Электронная таблица с не строгими правилами и несмежной гибкостью . и запросы, замаскированные под фильтры. Платформа для сменных таблиц и списков.
@ejbytes tldr версия - это MS, которая хотела протестировать механизм памяти columnstore в Excel, чтобы добавить больше строк, она включала соединения с внешними данными, оказалась чрезвычайно популярной, поэтому они добавлены в инструмент ETL. Теперь они свернули все инструменты как PowerBI.
@KyleHale Я использую Office 2010 и пытаюсь выполнить FULL OUTER JOIN, но Power Query игнорирует идентификаторы из второй таблицы, которых нет в первой таблице.
Быстрый способ полу-симуляции (внешнего соединения) состоит в том, чтобы взять второй список и вставить его непосредственно под первым списком, т. Е. Чтобы ваши (первичные ключи) были в одном (первичном) столбце. Затем отсортируйте этот основной столбец, в результате вы получите список с чередованием, а затем выполните транспонированное уравнение ПЧ (суперпользователь испортил представление макета таблицы . ):
сэм синий тим 32874 тим красный мэри 5710 крис грин густав 047 фред блю мэри
чёрный
копия / вставка / сортировка, результат выглядит так: AB Крис Грин Фред Блю Густав 047 Мэри 5710 Мэри черный Сэм Блю Тим 32874 Тим красный
тогда формула для ячейки c1: (c1) = if (A1 = A2, B2)
Результат выглядит так:
ABC Крис Грин ЛОЖЬ фред синий ЛОЖЬ густав 047 ЛОЖЬ Мэри 5710 черный Мэри черный ЛОЖЬ сэм синий ЛОЖЬ тим 32874 красный тим красный ЛОЖЬ
Сортировка C, чтобы избавиться от ложных и т.д. Это базовая версия идеи, просто расширьте ее, если вам нужно больше данных. -wag770310
Вы применяли функцию ВПР, чтобы переместить данные столбца из одной таблицы в другой? Так как в Excel теперь есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами на основе совпадающих данных в них. Затем можно создать листы Power View или сводные таблицы и другие отчеты с полями из каждой таблицы, даже если они получены из различных источников. Например, если у вас есть данные о продажах клиентам, вам может потребоваться импортировать и связать данные логики операций со временем, чтобы проанализировать тенденции продаж по годам и месяцам.
Все таблицы в книге указываются в списках полей сводной таблицы и Power View.
При импорте связанных таблиц из реляционной базы данных Excel часто может создавать эти связи в модели данных, формируемой в фоновом режиме. В других случаях необходимо создавать связи вручную.
Убедитесь, что книга содержит хотя бы две таблицы и в каждой из них есть столбец, который можно сопоставить со столбцом из другой таблицы.
Вы можете отформатировать данные как таблицу или импортировать внешние данные в виде таблицы на новом.
Присвойте каждой из таблиц понятное имя: На вкладке Работа с таблицами щелкните Конструктор > Имя таблицы и введите имя.
Убедитесь, что столбец в одной из таблиц имеет уникальные значения без дубликатов. Excel может создавать связи только в том случае, если один столбец содержит уникальные значения.
Например, чтобы связать продажи клиента с логикой операций со временем, обе таблицы должны включать дату в одинаковом формате (например, 01.01.2012) и по крайней мере в одной таблице (логика операций со временем) должны быть перечислены все даты только один раз в столбце.
Щелкните Данные> Отношения.
Если команда Отношения недоступна, значит книга содержит только одну таблицу.
В окне Управление связями нажмите кнопку Создать.
В окне Создание связи щелкните стрелку рядом с полем Таблица и выберите таблицу из раскрывающегося списка. В связи "один ко многим" эта таблица должна быть частью с несколькими элементами. В примере с клиентами и логикой операций со временем необходимо сначала выбрать таблицу продаж клиентов, потому что каждый день, скорее всего, происходит множество продаж.
Для элемента Столбец (чужой) выберите столбец, который содержит данные, относящиеся к элементу Связанный столбец (первичный ключ). Например, при наличии столбца даты в обеих таблицах необходимо выбрать этот столбец именно сейчас.
В поле Связанная таблица выберите таблицу, содержащую хотя бы один столбец данных, которые связаны с таблицей, выбранной в поле Таблица.
В поле Связанный столбец (первичный ключ) выберите столбец, содержащий уникальные значения, которые соответствуют значениям в столбце, выбранном в поле Столбец.
Дополнительные сведения о связях между таблицами в Excel
Примечания о связях
Вы узнаете, существуют ли связи, при перетаскивании полей из разных таблиц в список полей сводной таблицы. Если вам не будет предложено создать связь, то в Excel уже есть сведения, необходимые для связи данных.
Создание связей аналогично использованию VLOOKUP: вам нужны столбцы, содержащие совпадающие данные, чтобы Excel могли ссылаться на строки в одной таблице с строками из другой таблицы. В примере со временем в таблице Customer должны быть значения дат, которые также существуют в таблице аналитики времени.
В модели данных связи таблиц могут быть типа "один к одному" (у каждого пассажира есть один посадочный талон) или "один ко многим" (в каждом рейсе много пассажиров), но не "многие ко многим". Связи "многие ко многим" приводят к ошибкам циклической зависимости, таким как "Обнаружена циклическая зависимость". Эта ошибка может произойти, если вы создаете прямое подключение между двумя таблицами со связью "многие ко многим" или непрямые подключения (цепочку связей таблиц, в которой каждая таблица связана со следующей отношением "один ко многим", но между первой и последней образуется отношение "многие ко многим"). Дополнительные сведения см. в статье Связи между таблицами в модели данных.
Типы данных в двух столбцах должны быть совместимы. Подробные сведения см. в статье Типы данных в моделях данных.
Другие способы создания связей могут оказаться более понятными, особенно если неизвестно, какие столбцы использовать. Дополнительные сведения см. в статье Создание связи в представлении диаграммы в Power Pivot.
Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании
Вы можете узнать о связях обеих таблиц и логики операций со временем с помощью свободных данных на Microsoft Azure Marketplace. Некоторые из этих наборов данных очень велики, и для их загрузки за разумное время необходимо быстрое подключение к Интернету.
Нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace. В мастере импорта таблиц откроется домашняя страница Microsoft Azure Marketplace.
В разделе Price (Цена) нажмите Free (Бесплатно).
В разделе Category (Категория) нажмите Science & Statistics (Наука и статистика).
Найдите DateStream и нажмите кнопку Subscribe (Подписаться).
Прокрутите вниз и нажмите Select Query (Запрос на выборку).
Чтобы импортировать данные, выберите BasicCalendarUS и нажмите Готово. При быстром подключении к Интернету импорт займет около минуты. После выполнения вы увидите отчет о состоянии перемещения 73 414 строк. Нажмите Закрыть.
Чтобы импортировать второй набор данных, нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace.
В разделе Type (Тип) нажмите Data Данные).
В разделе Price (Цена) нажмите Free (Бесплатно).
Найдите US Air Carrier Flight Delays и нажмите Select (Выбрать).
Прокрутите вниз и нажмите Select Query (Запрос на выборку).
Нажмите Готово для импорта данных. При быстром подключении к Интернету импорт займет около 15 минут. После выполнения вы увидите отчет о состоянии перемещения 2 427 284 строк. Нажмите Закрыть. Теперь у вас есть две таблицы в модели данных. Чтобы связать их, нужны совместимые столбцы в каждой таблице.
Убедитесь, что значения в столбце DateKey в таблице BasicCalendarUS указаны в формате 01.01.2012 00:00:00. В таблице On_Time_Performance также есть столбец даты и времени FlightDate, значения которого указаны в том же формате: 01.01.2012 00:00:00. Два столбца содержат совпадающие данные одинакового типа и по крайней мере один из столбцов (DateKey) содержит только уникальные значения. В следующих действиях вы будете использовать эти столбцы, чтобы связать таблицы.
В окне Power Pivot нажмите Сводная таблица, чтобы создать сводную таблицу на новом или существующем листе.
В списке полей разверните таблицу On_Time_Performance и нажмите ArrDelayMinutes, чтобы добавить их в область значений. В сводной таблице вы увидите общее время задержанных рейсов в минутах.
Разверните таблицу BasicCalendarUS и нажмите MonthInCalendar, чтобы добавить его в область строк.
Обратите внимание, что теперь в сводной таблице перечислены месяцы, но количество минут одинаковое для каждого месяца. Нужны одинаковые значения, указывающие на связь.
В списке полей, в разделе "Могут потребоваться связи между таблицами" нажмите Создать.
В поле "Связанная таблица" выберите On_Time_Performance, а в поле "Связанный столбец (первичный ключ)" — FlightDate.
В поле "Таблица" выберитеBasicCalendarUS, а в поле "Столбец (чужой)" — DateKey. Нажмите ОК для создания связи.
Обратите внимание, что время задержки в настоящее время отличается для каждого месяца.
В таблице BasicCalendarUS перетащите YearKey в область строк над пунктом MonthInCalendar.
Теперь вы можете разделить задержки прибытия по годам и месяцам, а также другим значениям в календаре.
Советы: По умолчанию месяцы перечислены в алфавитном порядке. С помощью надстройки Power Pivot вы можете изменить порядок сортировки так, чтобы они отображались в хронологическом порядке.
Таблица BasicCalendarUS должна быть открыта в окне Power Pivot.
В главной таблице нажмите Сортировка по столбцу.
В поле "Сортировать" выберите MonthInCalendar.
В поле "По" выберите MonthOfYear.
Сводная таблица теперь сортирует каждую комбинацию "месяц и год" (октябрь 2011, ноябрь 2011) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, потому что канал DateStream предоставляет все необходимые столбцы для работы этого сценария. Если вы используете другую таблицу логики операций со временем, ваши действия будут другими.
"Могут потребоваться связи между таблицами"
По мере добавления полей в сводную таблицу вы получите уведомление о необходимости связи между таблицами, чтобы разобраться с полями, выбранными в сводной таблице.
Хотя Excel может подсказать вам, когда необходима связь, он не может подсказать, какие таблицы и столбцы использовать, а также возможна ли связь между таблицами. Чтобы получить ответы на свои вопросы, попробуйте сделать следующее.
Шаг 1. Определите, какие таблицы указать в связи
Если ваша модель содержит всего лишь несколько таблиц, понятно, какие из них нужно использовать. Но для больших моделей вам может понадобиться помощь. Один из способов заключается в том, чтобы использовать представление диаграммы в надстройке Power Pivot. Представление диаграммы обеспечивает визуализацию всех таблиц в модели данных. С помощью него вы можете быстро определить, какие таблицы отделены от остальной части модели.
Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблице к другой
После того как вы определили, какая таблица не связана с остальной частью модели, пересмотрите столбцы в ней, чтобы определить содержит ли другой столбец в другом месте модели соответствующие значения.
Предположим, у вас есть модель, которая содержит продажи продукции по территории, и вы впоследствии импортируете демографические данные, чтобы узнать, есть ли корреляция между продажами и демографическими тенденциями на каждой территории. Так как демографические данные поступают из различных источников, то их таблицы первоначально изолированы от остальной части модели. Для интеграции демографических данных с остальной частью своей модели вам нужно будет найти столбец в одной из демографических таблиц, соответствующий тому, который вы уже используете. Например, если демографические данные организованы по регионам и ваши данные о продажах определяют область продажи, то вы могли бы связать два набора данных, найдя общие столбцы, такие как государство, почтовый индекс или регион, чтобы обеспечить подстановку.
Кроме совпадающих значений есть несколько дополнительных требований для создания связей.
Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликаты. В модели данных нули и пустые строки эквивалентны пустому полю, которое является самостоятельным значением данных. Это означает, что не может быть несколько нулей в столбце подстановок.
Типы данных столбца подстановок и исходного столбца должны быть совместимы. Подробнее о типах данных см. в статье Типы данных в моделях данных.
Подробнее о связях таблиц см. в статье Связи между таблицами в модели данных.
Have you ever used VLOOKUP to bring a column from one table into another table? Now that Excel has a built-in Data Model, VLOOKUP is obsolete. You can create a relationship between two tables of data, based on matching data in each table. Then you can create Power View sheets and build PivotTables and other reports with fields from each table, even when the tables are from different sources. For example, if you have customer sales data, you might want to import and relate time intelligence data to analyze sales patterns by year and month.
All the tables in a workbook are listed in the PivotTable and Power View Fields lists.
When you import related tables from a relational database, Excel can often create those relationships in the Data Model it’s building behind the scenes. For all other cases, you’ll need to create relationships manually.
Make sure the workbook contains at least two tables, and that each table has a column that can be mapped to a column in another table.
Do one of the following: Format the data as a table, or Import external data as a table in a new worksheet.
Give each table a meaningful name: In Table Tools, click Design > Table Name > enter a name.
Verify the column in one of the tables has unique data values with no duplicates. Excel can only create the relationship if one column contains unique values.
For example, to relate customer sales with time intelligence, both tables must include dates in the same format (for example, 1/1/2012), and at least one table (time intelligence) lists each date just once within the column.
Click Data > Relationships.
If Relationships is grayed out, your workbook contains only one table.
In the Manage Relationships box, click New.
In the Create Relationship box, click the arrow for Table, and select a table from the list. In a one-to-many relationship, this table should be on the many side. Using our customer and time intelligence example, you would choose the customer sales table first, because many sales are likely to occur on any given day.
For Column (Foreign), select the column that contains the data that is related to Related Column (Primary). For example, if you had a date column in both tables, you would choose that column now.
For Related Table, select a table that has at least one column of data that is related to the table you just selected for Table.
For Related Column (Primary), select a column that has unique values that match the values in the column you selected for Column.
More about relationships between tables in Excel
Notes about relationships
You’ll know whether a relationship exists when you drag fields from different tables onto the PivotTable Fields list. If you aren’t prompted to create a relationship, Excel already has the relationship information it needs to relate the data.
Creating relationships is similar to using VLOOKUPs: you need columns containing matching data so that Excel can cross-reference rows in one table with those of another table. In the time intelligence example, the Customer table would need to have date values that also exist in a time intelligence table.
In a data model, table relationships can be one-to-one (each passenger has one boarding pass) or one-to-many (each flight has many passengers), but not many-to-many. Many-to-many relationships result in circular dependency errors, such as “A circular dependency was detected.” This error will occur if you make a direct connection between two tables that are many-to-many, or indirect connections (a chain of table relationships that are one-to-many within each relationship, but many-to-many when viewed end to end. Read more about Relationships between tables in a Data Model.
The data types in the two columns must be compatible. See Data types in Excel Data Models for details.
Other ways to create relationships might be more intuitive, especially if you are not sure which columns to use. See Create a relationship in Diagram View in Power Pivot.
Example: Relating time intelligence data to airline flight data
You can learn about both table relationships and time intelligence using free data on the Microsoft Azure Marketplace. Some of these datasets are very large, requiring a fast internet connection to complete the data download in a reasonable period of time.
Click Get External Data > From Data Service > From Microsoft Azure Marketplace. The Microsoft Azure Marketplace home page opens in the Table Import Wizard.
Under Price, click Free.
Under Category, click Science & Statistics.
Find DateStream and click Subscribe.
Enter your Microsoft account and click Sign in. A preview of the data should appear in the window.
Scroll to the bottom and click Select Query.
Click Next.
Choose BasicCalendarUS and then click Finish to import the data. Over a fast internet connection, import should take about a minute. When finished, you should see a status report of 73,414 rows transferred. Click Close.
Click Get External Data > From Data Service > From Microsoft Azure Marketplace to import a second dataset.
Under Type, click Data.
Under Price, click Free.
Find US Air Carrier Flight Delays and click Select.
Scroll to the bottom and click Select Query.
Click Next.
Click Finish to import the data. Over a fast internet connection, this can take 15 minutes to import. When finished, you should see a status report of 2,427,284 rows transferred. Click Close. You should now have two tables in the data model. To relate them, we’ll need compatible columns in each table.
Notice that the DateKey in BasicCalendarUS is in the format 1/1/2012 12:00:00 AM. The On_Time_Performance table also has a datetime column, FlightDate, whose values are specified in the same format: 1/1/2012 12:00:00 AM. The two columns contain matching data, of the same data type, and at least one of the columns (DateKey) contains only unique values. In the next several steps, you’ll use these columns to relate the tables.
In the Power Pivot window, click PivotTable to create a PivotTable in a new or existing worksheet.
In the Field List, expand On_Time_Performance and click ArrDelayMinutes to add it to the Values area. In the PivotTable, you should see the total amount of time flights were delayed, as measured in minutes.
Expand BasicCalendarUS and click MonthInCalendar to add it to the Rows area.
Notice that the PivotTable now lists months, but the sum total of minutes is the same for every month. Repeating, identical values indicate a relationship is needed.
In the Field List, in “Relationships between tables may be needed”, click Create.
In Related Table, select On_Time_Performance and in Related Column (Primary) choose FlightDate.
In Table, select BasicCalendarUS and in Column (Foreign) choose DateKey. Click OK to create the relationship.
Notice that the sum of minutes delayed now varies for each month.
In BasicCalendarUS and drag YearKey to the Rows area, above MonthInCalendar.
You can now slice arrival delays by year and month, or other values in the calendar.
Tips: By default, months are listed in alphabetical order. Using the Power Pivot add-in, you can change the sort so that months appear in chronological order.
Make sure that the BasicCalendarUS table is open in the Power Pivot window.
On the Home table, click Sort by Column.
In Sort, choose MonthInCalendar
In By, choose MonthOfYear.
The PivotTable now sorts each month-year combination (October 2011, November 2011) by the month number within a year (10, 11). Changing the sort order is easy because the DateStream feed provides all of the necessary columns to make this scenario work. If you’re using a different time intelligence table, your step will be different.
“Relationships between tables may be needed”
As you add fields to a PivotTable, you’ll be informed if a table relationship is required to make sense of the fields you selected in the PivotTable.
Although Excel can tell you when a relationship is needed, it can’t tell you which tables and columns to use, or whether a table relationship is even possible. Try following these steps to get the answers you need.
Step 1: Determine which tables to specify in the relationship
If your model contains just a few tables, it might be immediately obvious which ones you need to use. But for larger models, you could probably use some help. One approach is to use Diagram View in the Power Pivot add-in. Diagram View provides a visual representation of all the tables in the Data Model. Using Diagram View, you can quickly determine which tables are separate from the rest of the model.
Note: It’s possible to create ambiguous relationships that are invalid when used in a PivotTable or Power View report. Suppose all of your tables are related in some way to other tables in the model, but when you try to combine fields from different tables, you get the “Relationships between tables may be needed” message. The most likely cause is that you’ve run into a many-to-many relationship. If you follow the chain of table relationships that connect to the tables you want to use, you will probably discover that you have two or more one-to-many table relationships. There is no easy workaround that works for every situation, but you might try creating calculated columns to consolidate the columns you want to use into one table.
Step 2: Find columns that can be used to create a path from one table to the next
After you’ve identified which table is disconnected from the rest of the model, review its columns to determine whether another column, elsewhere in the model, contains matching values.
For example, suppose you have a model that contains product sales by territory, and that you subsequently import demographic data to find out if there is correlation between sales and demographic trends in each territory. Because the demographic data comes from a different data source, its tables are initially isolated from the rest of the model. To integrate the demographic data with the rest of your model, you’ll need to find a column in one of the demographic tables that corresponds to one you’re already using. For example if the demographic data is organized by region, and your sales data specifies which region the sale occurred, you could relate the two datasets by finding a common column, such as a State, Zip code, or Region, to provide the lookup.
Besides matching values, there are a few additional requirements for creating a relationship:
Data values in the lookup column must be unique. In other words, the column can’t contain duplicates. In a Data Model, nulls and empty strings are equivalent to a blank, which is a distinct data value. This means that you can’t have multiple nulls in the lookup column.
Data types of both the source column and lookup column must be compatible. For more information about data types, see Data types in Data Models.
To learn more about table relationships, see Relationships between tables in a Data Model.
У меня есть две таблицы в двух разных файлах Excel. Они оба содержат список имен, идентификационных номеров и связанных данных. Один представляет собой основной список, который включает в себя общие демографические поля, а другой представляет собой список, который включает только имя и идентификатор и адрес. Этот список был исключен из основного списка другим офисом.
Я хочу использовать 2-й список для фильтрации первого. Кроме того, я хочу, чтобы результаты включали другие поля из главной таблицы вместе с полями адреса из второй таблицы. Я знаю, как можно легко это сделать с помощью внутреннего соединения с базой данных, но я не совсем понимаю, как это сделать эффективно в Excel. Как можно объединить две таблицы в Excel? Бонусные баллы за показ того, как выполнять внешние объединения, и я бы предпочел знать, как это сделать без макроса.
Для 2007+ использования Data > From Other Sources > From Microsoft Query :
- выбрать Excel File и выбрать свой первый Excel
- выберите столбцы
(если вы не видите списка столбцов, обязательно отметьте Options > System Tables ) - перейти к Data > Connections > [выбрать подключение только что созданный]> Properties > Definition > Command text
Теперь вы можете редактировать это Command text как SQL. Не уверен, какой синтаксис поддерживается, но я пробовал неявные объединения, "внутреннее соединение", "левое соединение" и объединения, которые все работают. Вот пример запроса:
Работает ли это с файлами CSV? Я использую MS Office Professional Plus 2010 и не вижу, как выполнить шаг 3 - я оставляю диалоговое окно на шаге 2 зависшим? В моем диалоговом окне «Подключения» нет «выбрать новое подключение».
@JohnFreeman, потому что вам нужно выбрать один из доступных вариантов в соответствии с вашими потребностями (например, «Добавить . »)
Обратите внимание, что может появиться диалоговое окно с надписью «Этот источник данных не содержит видимых таблиц», за которым следует диалоговое окно «Мастер запросов - Выбор столбцов». В этом диалоговом окне вы хотите нажать кнопку «Параметры», а затем установить флажок «Системные таблицы», чтобы увидеть данные, которые вы хотите запросить.
Поддержите принятый ответ. Я просто хочу подчеркнуть "выбрать столбцы (если вы не видите списка столбцов, обязательно проверьте Параметры> Системные таблицы)"
После выбора файла Excel, скорее всего, вы увидите this data source contains no visible tables подсказку, а доступных вкладок и столбцов нет. Microsoft признала, что ошибка в том, что вкладки в файлах Excel рассматриваются как «Системные таблицы», а опция «Системные таблицы» по умолчанию не выбрана. Так что не паникуйте на этом шаге, вам просто нужно нажать «опция» и проверить «Системные таблицы», после чего вы увидите доступные столбцы.
VLOOKUP и HLOOKUP могут использоваться для поиска совпадающих первичных ключей (хранящихся вертикально или горизонтально) и возврата значений из столбцов / строк «атрибута».
очень полезно для листов в одной и той же книге (но я считаю, что функции INDEX + MATCH еще более полезны), немного сложнее при обновлении данных из закрытых внешних книг .
Вы можете использовать Microsoft Power Query, доступный для более новых версий Excel (аналогично принятому ответу, но гораздо проще и проще). Power Query вызывает объединения «слияния».
Самый простой способ - это получить 2 листа Excel в виде таблиц Excel. Затем в Excel перейдите на вкладку ленты Power Query и нажмите кнопку «Из Excel». После того, как вы импортировали обе таблицы в Power Query, выберите одну и нажмите «Объединить».
Намного проще, чем любой из предложенных вариантов. Это должен быть ответ! Power Query теперь включен в Excel 2016 на вкладке « Данные ».
Хотя я думаю, что ответ Aprillion с использованием Microsoft Query превосходен, это вдохновило меня на использование Microsoft Access для присоединения к таблицам данных, которые мне показались намного проще.
Конечно, вам нужно установить MS Access.
- Создайте новую базу данных Access (или используйте чистую базу данных).
- Используйте Get External Data для импорта данных Excel в виде новых таблиц.
- Используйте, Relationships чтобы показать, как соединяются ваши таблицы.
- Установите тип Отношения, чтобы соответствовать тому, что вы хотите (представляющий левое соединение и т. Д.)
- Создайте новый запрос, который объединит ваши таблицы.
- Используйте External Data->Export to Excel для генерации ваших результатов.
Я действительно не мог бы сделать это без отличного ответа Aprillion.
Я никогда не использовал доступ до сегодняшнего дня, но это заняло у меня всего 10 минут. Я копирую / вставляю столбцы Excel в 2 таблицы вместо импорта.
Вы не можете предварительно сформировать соединения в стиле SQL в таблицах Excel из Excel. Тем не менее, есть несколько способов выполнить то, что вы пытаетесь сделать.
В Excel, как говорит Рувим, формулы, которые, вероятно, будут работать лучше, VLOOKUP и HLOOKUP . В обоих случаях вы сопоставляете уникальную строку, и она возвращает значение данного столбца \ строки влево \ вниз от найденного идентификатора.
Если вы хотите добавить только пару дополнительных полей во второй список, добавьте формулы во второй список. Если вы хотите таблицу стилей "external join", добавьте VLOOKUP формулу в первый список, ISNA чтобы проверить, был ли найден поиск. Если в справке Excel недостаточно подробностей о том, как их использовать в вашем конкретном случае, сообщите нам об этом.
Если вы предпочитаете использовать SQL, то свяжите данные с программой базы данных, создайте запрос и экспортируйте результаты обратно в Excel. (В Access вы можете импортировать таблицы Excel или именованные диапазоны в виде связанной таблицы.)
С помощью этого инструмента вы можете создать любой SQL-запрос к таблицам в книгах Excel с помощью встроенного редактора SQL и сразу же запустить его с возможностью поместить результат на новый или любой существующий лист.
Вы можете использовать практически любой тип соединения, включая LEFT OUTER JOIN (только RIGHT OUTER JOIN и FULL OUTER JOIN не поддерживаются).
Для пользователей Excel 2007: Данные> Из других источников> Из Microsoft Query> перейдите к файлу Excel
Согласно этой статье , запрос из XLS версии 2003 может привести к «Этот источник данных не содержит видимых таблиц». ошибка, потому что ваши рабочие листы рассматриваются как таблица SYSTEM. Поэтому проверяйте параметры «Системные таблицы» в диалоговом окне «Мастер запросов - выберите столбцы», когда вы создаете запрос, который будет работать.
Чтобы определить ваше объединение: диалоговое окно Microsoft Query> меню «Таблица»> «Объединения . »
Чтобы вернуть данные на исходный лист Excel, выберите «Вернуть данные в лист Excel» в диалоговом окне Microsoft Query> меню «Файл».
Если вы достаточно знакомы с базами данных, вы можете использовать SQL Server для подключения обеих таблиц в качестве связанных серверов, а затем использовать T-SQL для выполнения ваших внутренних данных. Затем закончите, подключив Excel обратно к SQL и вытяните данные в таблицу (обычную или сводную). Вы также можете рассмотреть возможность использования Powerpivot; это позволит объединять любые источники базы данных, включая Excel, используемый в качестве плоских баз данных.
Вы правы, но первоначальный вопрос основан на использовании электронной таблицы (или двух) для выполнения функции базы данных, поэтому я не уверен в каком-либо действительно эффективном способе выполнить задачу.
В поисках той же проблемы я наткнулся на RDBMerge , который, на мой взгляд, является удобным для пользователя способом объединения данных из нескольких рабочих книг Excel, файлов CSV и XML в сводную рабочую книгу .
Как известно самым популярным и эффективным инструментом работы с табличными данными (или просто таблицами) является программа Microsoft Excel.
При этом также известно, что самым мощным и распространённым языком программирования для работы с табличными данными является язык Structured Query Language = SQL = Язык структурированных запросов
Исходя из этого факта, логично предположить, что Microsoft Excel должен поддерживать язык SQL по умолчанию. Но, как правило, SQL поддерживается только базами данных (СУБД).
Несмотря на это, для Excel (начиная с 2010 версии) появилась бесплатная надстройка Power Query, которая позволяет имитировать часть полезного функционала языка SQL, а именно:
В языке SQL есть очень интересный оператор «SELECT», который позволяет делать запросы к табличным данным в базе данных. В результате запроса возвращается набор данных (выборка из базы данных), удовлетворяющий заданному условию выборки.
Как правило, выборка данных делается из нескольких таблиц в базе данных. Для связи таблиц в языке программирования SQL существует оператор «JOIN», который выполняет различные операции соединения реляционных таблиц (в основе этого принципа лежат законы реляционной алгебры)
Различают следующие виды оператора «JOIN»:
- INNER JOIN — Оператор внутреннего соединения двух таблиц
- LEFT OUTER JOIN — Оператор левого внешнего соединения двух таблиц
- RIGHT OUTER JOIN — Оператор правого внешнего соединения двух таблиц
- FULL OUTER JOIN — Оператор полного внешнего соединения двух таблиц
- CROSS JOIN — Оператор перекрёстного соединения (декартово произведение) двух таблиц
С выходом надстройки Power Query для Excel (это один из инструментов уровня Self-Service BI) в Excel появилась поддержка функционала всех видов операторов «JOIN» языка SQL:
Рассмотрим операторов «JOIN» в Excel на примерах:
Представим, что у нас в Excel есть две Таблицы: A «Люди» и B «Города»
Теперь давайте объединим данные таблицы с помощью различных операторов «JOIN»:
(объединяем таблицы через столбцы: A.Cityid = B.id)
Оператор INNER JOIN вернет следующий результат:
Оператор LEFT JOIN вернет следующий результат:
Оператор RIGHT JOIN вернет следующий результат:
Оператор FULL OUTER JOIN вернет следующий результат:
Оператор CROSS JOIN вернет следующий результат:
В надстройке Power Query для Excel данная функция «JOIN» называется «Слияние» — слияние запросов, где:
Оператору слияния INNER JOIN соответствует тип соединения: Внутреннее (только совпадающие строки)
Оператору слияния LEFT JOIN соответствует тип соединения: Внешнее соединение слева (все из первой таблицы, совпадающие из второй)
Оператору слияния RIGHT JOIN соответствует тип соединения: Внешнее соединение справа (все из второй таблицы, совпадающие из первой)
Оператору слияния FULL OUTER JOIN соответствует тип соединения: Полное внешнее (все строки из обеих таблиц)
Оператора слияния CROSS JOIN в интерфейсе Power Query нет, но его можно создать из оператора слияния FULL OUTER JOIN, убрав связи таблиц
Пошаговая инструкция использования функции «Слияние»/«Объединения» в Power Query находится в видеоуроке к данной статье
Читайте также: