Как сделать join в excel
Говоря про соединение таблиц в SQL, обычно подразумевают один из видов операции JOIN. Не стоит путать с объединением таблиц через операцию UNION. В этой статье я постараюсь простыми словами рассказать именно про соединение, чтобы после ее прочтения Вы могли использовать джойны в работе и не допускать грубых ошибок.
Соединение – это операция, когда таблицы сравниваются между собой построчно и появляется возможность вывода столбцов из всех таблиц, участвующих в соединении.
Придумаем 2 таблицы, на которых будем тренироваться.
Таблица «Сотрудники», содержит поля:
Таблица «Отделы», содержит поля:
Давайте уже быстрее что-нибудь покодим.
INNER JOIN
Самый простой вид соединения INNER JOIN – внутреннее соединение. Этот вид джойна выведет только те строки, если условие соединения выполняется (является истинным, т.е. TRUE). В запросах необязательно прописывать INNER – если написать только JOIN, то СУБД по умолчанию выполнить именно внутреннее соединение.
Давайте соединим таблицы из нашего примера, чтобы ответить на вопрос, в каких отделах работают сотрудники (читайте комментарии в запросе для понимания синтаксиса).
Получим следующий результат:
| id | Имя | Отдел |
|---|---|---|
| 1 | Юлия | Кухня |
| 2 | Федор | Бар |
| 4 | Светлана | Бар |
Из результатов пропал сотрудник Алексей (id = 3), потому что условие «Сотрудники.Отдел = Отделы.id» не будет истинно для этой сроки из таблицы «Сотрудники» с каждой строкой из таблицы «Отделы». По той же логике в результате нет отдела «Администрация». Попробую это визуализировать (зеленные линии – условие TRUE, иначе линия красная):
Если не углубляться в то, как внутреннее соединение работает под капотом СУБД, то происходит примерно следующее:
- Каждая строка из одной таблицы сравнивается с каждой строкой из другой таблицы
- Строка возвращается, если условие сравнения является истинным
Если для одной или нескольких срок из левой таблицы (в рассмотренном примере левой таблицей является «Сотрудники», а правой «Отделы») истинным условием соединения будут являться одна или несколько срок из правой таблицы, то строки умножат друг друга (повторятся). В нашем примере так произошло для отдела с поэтому строка из таблицы «Отделы» повторилась дважды для Федора и Светланы.
Перемножение таблиц проще ощутить на таком примере, где условие соединения будет всегда возвращать TRUE, например 1=1:
В результате получится 12 строк (4 сотрудника * 3 отдела), где для каждого сотрудника подтянется каждый отдел.
Также хочу сразу отметить, что в соединении может участвовать сколько угодно таблиц, можно таблицу соединить даже саму с собой (в аналитических задачах это не редкость). Какая из таблиц будет правой или левой не имеется значения для INNER JOIN (для внешних соединений типа LEFT JOIN или RIGHT JOIN это важно. Читайте далее). Пример соединения 4-х таблиц:
Как видите, все просто, прописываем новый джойн после завершения условий предыдущего соединения. Обратите внимание, что для Table_3 указано несколько условий соединения с двумя разными таблицами, а также Table_1 соединяется сама с собой по условию с использованием сложения.
Строки, которые выведутся запросом, должны совпасть по всем условиям. Например:
- Строка из Table_1 соединилась со строкой из Table_2 по условию первого JOIN. Давайте назовем ее «объединенной строкой» из двух таблиц;
- Объединенная строка успешно соединилась с Table_3 по условию второго JOIN и теперь состоит из трех таблиц;
- Для объединенной строки не нашлось строки из Table_1 по условию третьего JOIN, поэтому она не выводится вообще.
На этом про внутреннее соединение и логику соединения таблиц в SQL – всё. Если остались неясности, то спрашивайте в комментариях.
Далее рассмотрим отличия остальных видов джойнов.
LEFT JOIN и RIGHT JOIN
Левое и правое соединения еще называют внешними. Главное их отличие от внутреннего соединения в том, что строка из левой (для LEFT JOIN) или из правой таблицы (для RIGHT JOIN) попадет в результаты в любом случае. Давайте до конца определимся с тем, какая таблица левая, а какая правая.
Левая таблица та, которая идет перед написанием ключевых слов [LEFT | RIGHT| INNER] JOIN, правая таблица – после них:
Теперь изменим наш SQL-запрос из самого первого примера так, чтобы ответить на вопрос «В каких отделах работают сотрудники, а также показать тех, кто не распределен ни в один отдел?»:
Результат запроса будет следующим:
| id | Имя | Отдел |
|---|---|---|
| 1 | Юлия | Кухня |
| 2 | Федор | Бар |
| 3 | Алексей | NULL |
| 4 | Светлана | Бар |
Как видите, запрос вернул все строки из левой таблицы «Сотрудники», дополнив их значениями из правой таблицы «Отделы». А вот строка для отдела «Администрация» не показана, т.к. для нее не нашлось совпадений слева.
Это мы рассмотрели пример для левого внешнего соединения. Для RIGHT JOIN будет все тоже самое, только вернутся все строки из таблицы «Отделы»:
| id | Имя | Отдел |
|---|---|---|
| 1 | Юлия | Кухня |
| 2 | Федор | Бар |
| 4 | Светлана | Бар |
| NULL | NULL | Администрация |
Алексей «потерялся», Администрация «нашлась».
Вопрос для Вас. Что надо изменить в последнем приведенном SQL-запросе, чтобы результат остался тем же, но вместо LEFT JOIN, использовался RIGHT JOIN?
Ответ. Нужно поменять таблицы местами:
В одном запросе можно применять и внутренние соединения, и внешние одновременно, главное соблюдать порядок таблиц, чтобы не потерять часть записей (строк).
FULL JOIN
Еще один вид соединения, который осталось рассмотреть – полное внешнее соединение.
Этот вид джойна вернет все строки из всех таблиц, участвующих в соединении, соединив между собой те, которые подошли под условие ON.
Давайте посмотрим всех сотрудников и все отделы из наших тестовых таблиц:
| id | Имя | Отдел |
|---|---|---|
| 1 | Юлия | Кухня |
| 2 | Федор | Бар |
| 3 | Алексей | NULL |
| 4 | Светлана | Бар |
| NULL | NULL | Администрация |
Теперь мы видим все, даже Алексея без отдела и Администрацию без сотрудников.
Вместо заключения
Помните о порядке выполнения соединений и порядке таблиц, если используете несколько соединений и используете внешние соединения. Можно выполнять LEFT JOIN для сохранения всех строк из самой первой таблицы, а последним внутренним соединением потерять часть данных. На маленьких таблицах косяк заметить легко, на огромных очень тяжело, поэтому будьте внимательны.
Рассмотрим последний пример и введем еще одну таблицу «Банки», в которой обслуживаются наши придуманные сотрудники:
| id | Наименование |
|---|---|
| 1 | Банк №1 |
| 2 | Лучший банк |
| 3 | Банк Лидер |
В таблицу «Сотрудники» добавим столбец «Банк»:
| id | Имя | Отдел | Банк |
|---|---|---|---|
| 1 | Юлия | 1 | 2 |
| 2 | Федор | 2 | 2 |
| 3 | Алексей | NULL | 3 |
| 4 | Светлана | 2 | 4 |
Теперь выполним такой запрос:
В результате потеряли информацию о Светлане, т.к. для нее не нашлось банка с (такое происходит из-за неправильной проектировки БД):
| id | Имя | Отдел | Банк |
|---|---|---|---|
| 1 | Юлия | Кухня | Лучший банк |
| 2 | Федор | Бар | Лучший банк |
| 3 | Алексей | NULL | Банк Лидер |
Хочу обратить внимание на то, что любое сравнение с неизвестным значением никогда не будет истинным (даже NULL = NULL). Эту грубую ошибку часто допускают начинающие специалисты. Подробнее читайте в статье про значение NULL в SQL.
Пройдите мой тест на знание основ SQL. В нем есть задания на соединения таблиц, которые помогут закрепить материал.
Дополнить Ваше понимание соединений в SQL могут схемы, изображенные с помощью кругов Эйлера. В интернете много примеров в виде картинок.
Если какие нюансы джойнов остались не раскрытыми, или что-то описано не совсем понятно, что-то надо дополнить, то пишите в комментариях. Буду только рад вопросам и предложениям.
Привожу простыню запросов, чтобы Вы могли попрактиковаться на легких примерах, рассмотренных в статье:
Как известно самым популярным и эффективным инструментом работы с табличными данными (или просто таблицами) является программа Microsoft Excel.
При этом также известно, что самым мощным и распространённым языком программирования для работы с табличными данными является язык Structured Query Language = SQL = Язык структурированных запросов
Исходя из этого факта, логично предположить, что Microsoft Excel должен поддерживать язык SQL по умолчанию. Но, как правило, SQL поддерживается только базами данных (СУБД).
Несмотря на это, для Excel (начиная с 2010 версии) появилась бесплатная надстройка Power Query, которая позволяет имитировать часть полезного функционала языка SQL, а именно:
В языке SQL есть очень интересный оператор «SELECT», который позволяет делать запросы к табличным данным в базе данных. В результате запроса возвращается набор данных (выборка из базы данных), удовлетворяющий заданному условию выборки.
Как правило, выборка данных делается из нескольких таблиц в базе данных. Для связи таблиц в языке программирования SQL существует оператор «JOIN», который выполняет различные операции соединения реляционных таблиц (в основе этого принципа лежат законы реляционной алгебры)
Различают следующие виды оператора «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 вернет следующий результат:
Оператору слияния INNER JOIN соответствует тип соединения: Внутреннее (только совпадающие строки)
Оператору слияния LEFT JOIN соответствует тип соединения: Внешнее соединение слева (все из первой таблицы, совпадающие из второй)
Оператору слияния RIGHT JOIN соответствует тип соединения: Внешнее соединение справа (все из второй таблицы, совпадающие из первой)
Оператору слияния FULL OUTER JOIN соответствует тип соединения: Полное внешнее (все строки из обеих таблиц)
Оператора слияния CROSS JOIN в интерфейсе Power Query нет, но его можно создать из оператора слияния FULL OUTER JOIN, убрав связи таблиц
Пошаговая инструкция использования функции «Слияние»/«Объединения» в Power Query находится в видеоуроке к данной статье
Вы применяли функцию ВПР, чтобы переместить данные столбца из одной таблицы в другой? Так как в 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. Найдите столбцы, которые могут быть использованы для создания пути от одной таблице к другой
После того как вы определили, какая таблица не связана с остальной частью модели, пересмотрите столбцы в ней, чтобы определить содержит ли другой столбец в другом месте модели соответствующие значения.
Предположим, у вас есть модель, которая содержит продажи продукции по территории, и вы впоследствии импортируете демографические данные, чтобы узнать, есть ли корреляция между продажами и демографическими тенденциями на каждой территории. Так как демографические данные поступают из различных источников, то их таблицы первоначально изолированы от остальной части модели. Для интеграции демографических данных с остальной частью своей модели вам нужно будет найти столбец в одной из демографических таблиц, соответствующий тому, который вы уже используете. Например, если демографические данные организованы по регионам и ваши данные о продажах определяют область продажи, то вы могли бы связать два набора данных, найдя общие столбцы, такие как государство, почтовый индекс или регион, чтобы обеспечить подстановку.
Кроме совпадающих значений есть несколько дополнительных требований для создания связей.
Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликаты. В модели данных нули и пустые строки эквивалентны пустому полю, которое является самостоятельным значением данных. Это означает, что не может быть несколько нулей в столбце подстановок.
Типы данных столбца подстановок и исходного столбца должны быть совместимы. Подробнее о типах данных см. в статье Типы данных в моделях данных.
Подробнее о связях таблиц см. в статье Связи между таблицами в модели данных.
У меня есть две таблицы в двух разных файлах 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 на вкладке « Данные ». Еще одна особенность, которая, кажется, не существует на Mac jХотя я думаю, что ответ 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 или именованные диапазоны в виде связанной таблицы.)
на самом деле, вы можете - это называется Microsoft QueryС помощью этого инструмента вы можете создать любой 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 в сводную рабочую книгу .
Продолжаю серию статей по работе с таблицами. На прошлой неделе уже писал о том, как работать с CONCATENATE для объединения таблиц по строкам, а в этой статье расскажу, как добавлять столбцы к уже загруженным таблицам данных с помощью JOIN.
Предыстория
В прошлой статье мы рассматривали пример, в котором компания X объединяла данные по сотрудникам отдела продаж с другим офисом компании в другом регионе. Мы объединяли данные таблицы, используя CONCATENATE, рассмотрев разницу между скрытым и открытым объединением строк.
Новые требования
Теперь после получения единой таблицы появляются новые требования, а именно объединить данные по управлению персоналом с новой таблицей по сотрудникам так, чтобы были доступны описания функционала работы. Итак, как же это сделать?
Простая загрузка таблиц
Первый способ решения этой задачи – загрузить таблицу как есть. Получится вот такая модель данных.
Технически такой способ решения не является ошибкой, ну лучше объединить представленные данные в единую таблицу, что позволит нам:
- Упростить модель данных.
- Опять же проще поддерживать модель данных.
JOIN
Когда добавляем JOIN до скрипта загрузки LOAD, QlikView не загружает данные в единую таблицу, а объединяет данные с предыдущей таблицей.
В этом случае QlikView сравнивает все общие поля в двух таблицах и объединяет строки из двух таблиц по совпадающим полям.
Дополнительно можно определить таблицы, с которыми будет идти работа по объединению данных. Если имя таблицы не определено, по умолчанию QlikView считает, что данные должны быть объединены с последней загруженной таблицей.
НА ЗАМЕТКУ! Рекомендую всегда определять имя таблицы, с которой будет вестись работа.
Типы объединений Join
Когда вы посмотрите на наши таблицы, то увидите, что не все ID сотрудников имеют совпадения в таблицах. Таблицы Employees содержат поля сотрудник, Michael Scott, которого нет в таблице данных по управлению персоналом. А данные по управлению персоналом содержат также данные по секретарю, которого нет в другой таблице.
Когда мы выполняем функцию стандартного объединения, таблицы объединяются так, что связанные записи из двух таблиц отображаются в одной и той же строке. Записи без связи получаются пустыми для остальных столбцов (см. рисунок ниже):
Это практически та таблица, которую мы хотели получить в итоге, но здесь, по-прежнему, есть данные по секретарю, которые мы должны исключить. Эту задачу легко решить, определив тип объединения данных.
У нас есть следующие варианты объединения данных, или JOIN:
- INNER: Только записи, которые представлены в двух таблицах входят в объединённую таблицу. В нашем случае это означает, что и первый сотрудник из таблицы 1 с ID 1 (Michael Scott) и ID 10 (секретарь) были бы исключены из итоговой таблицы.
- OUTER: Все записи из двух таблиц будут включены в итоговую таблицу, не важно имеют они общие данные по строкам или нет. Получается такой же итог как использование обычного объединения.
- LEFT: Все записи из первой таблицы будут включены в итоговую таблицу и только те записи из второй таблицы, которые имеют одинаковое значение с первой таблицей. В нашем примере это означает, что сотрудник ID 1 (Michael Scott) будет включен в итоговую таблицу и ID 10 (секретарь) исключен, поскольку у этой строки нет связанных данных с первой таблицей.
- RIGHT: Все записи из второй таблицы будут включены в объединенную таблицу, а также только те строки из первой таблицы, у которых есть одинаковые записи со второй таблицей. В нашем примере это означает, что ID 10 (секретарь) будет включен в итоговую таблицу и ID 1 (Michael Scott) будет исключен.
Поскольку мы хотим иметь полную таблицу сотрудников и добавить связанные значения из данных по УП, используем LEFT JOIN.
Получим такую таблицу:
На этом все! Если остались вопросы по работе с JOIN, задавайте в комментариях.
П.С. В приложении вы найдете скрипт, а также какие результаты вы получите, используя другие типы объединения данных.
Читайте также: