Как подтянуть данные из одной таблицы в другую в excel по нескольким признакам

Как подтянуть данные из одной таблицы в другую в excel по нескольким признакам

Всем добрый день.
Есть необходимость подтянуть из одной таблицы в другую значения по следующим признакам:
1) Имя
2) Дата
Таким образом, чтобы ВПР подтягивал Имя только из совпадающего по Дате диапазона.
Ниже (и во вложении) пример таблицы.
Необходимо подтянуть Результат к Имени с датой 01.02.2016 ТОЛЬКО в том случае и ТОЛЬКО тот результат, которые фигурирует в совпадающем по дате диапазоне (недеюсь не слишком замудрено написал..)

Заранее спасибо за консультацию.

Прикрепленные файлы

• смс-звонок.xlsx (10.06 КБ)

Изменено: asmityurin — 19.03.2016 16:05:31
Пользователь
Сообщений: 15596 Регистрация: 10.01.2013
19.03.2016 15:34:09

Цитата
asmityurin написал: подтянуть Результат к Имени с датой 01.02.2016 ТОЛЬКО в том случае и ТОЛЬКО тот результат, которые фигурирует в совпадающем по дате диапазоне

У Вас Петр в таблице Звонок за 01/02/2016 фигурирует дважды, с результатами Перезвонить и Думает. Что подтягивать?

Согласие есть продукт при полном непротивлении сторон.

ВПР на максималках

Думаю многие, если не большинство, в курсе, что такое ВПР и его неоспоримая сила при поиске и объединении данных из разных таблиц. Те же, кто достиг просветления, используют не менее полезную функцию ИНДЕКС, чтобы не париться, где там идентификатор: слева или справа.

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

Итак, что мы имеем:

Таблица раз — со списком значений, который надо обогатить данными

В другой таблице имеем данные по статусам, которые могут повторяться, и которые связаны с первой таблицей по идентификатору обращения

Обычная задача для ВПР или ИНДЕКС звучит, как «мне надо добавить данные из одной таблицы в другую по какому-то критерию или критериям», после чего берем критерий первой таблицы и начинаем искать по нему первое совпадающее значение из другой таблицы и возвращаем значение из искомого столбца:

=ВПР(A2;Лист2!$C$1:$C$170;2;0), где A2 - критерий, Лист2!$C$1:$I$13 - диапазон в котором ищем, 2 - номер столбца, из которого возвращаем значение, 0 - тип сопоставлениятип 

=ИНДЕКС(Лист2!$B$1:$I$170;ПОИСКПОЗ(Лист1!A3;Лист2!$C$1:$C$170;0);3), где - Лист2!$B$1:$I$170 - диапазон, в кокотором ищем, ПОИСКПОЗ(Лист1!A3;Лист2!$C$1:$C$170;0) - строка, которую ищем, 3 - столбец, откуда возвращаем данные, 

В целом двух этих формул хватит для 90% ситуаций при работе с excel, чтобы подтянуть данные из другой таблицы. Хватит ровно до того момента, пока к нам не присоединится условие с датой, при этом дата будет неизвестной переменной.

Собственно сама задача:

Имеем таблицу с идентификатором, в которую надо вернуть данные из другой таблицы (для моего примера — последний статус и последняя дата статуса) при условии, что запись должна быть последняя из всех записей, связанных с идентификатором

То есть мы оперируем идентификатором и условием, что дата должна быть самая поздняя из возможных записей с совпадающими идентификаторами. Когда меня попросили изобразить это в excel, я сначала начал думать в сторону VBA, так как Googlение проблемы приводило в основном к примерам, как искать записи по нескольким критериям, то есть из исходной таблицы мы берем эти несколько критериев и пытаемся по ним найти соответствующие записи в другой таблицы. Эти варианты не подходили, так как определение последней даты записи должно было происходить также в формуле, и заранее ее значение было неизвестно, ну и плюс она должна быть определена для записей с конкретным идентификатором. VBA тоже не походил, так как человеку надо было отправить просто формулу, чтобы можно было вставить в ячейку.

В итоге с помощью того Googlа и небольшой доработки напильником была рождена формула следующего типа:

Вот он — ВПР на максималках) тут использовано все, чтобы вернуть ту искомую запись в таблице: и массивы, и индекс, и несколько критериев, и условие по дате.
Как это работает:

1. Используем в формуле ИНДЕКС массивы. При вводе формулы используем сочетание клавиш ctrl+shift+enter
2. Выделяем просматриваемый диапазон, в моем случае от B2 до I170
3. Искомую строку определяем по формуле ПОИСКПОЗ, при этом поиск осуществляем по двум условиям A4&МАКС (идентификатор A4 исходной таблицы и максимальное значение даты при равном идентификаторе, значение даты берется из функции ЕСЛИ ). Тут важно не забыть, что поиск по нескольким критериям можно задать через & перечислив критерии, а также надо через & перечислить диапазоны, в которых excel будет искать эти критерии, в той же последовательности, что и критерии
4. Искомый столбец оформил тоже через формулу ПОИСКПОЗ, где формула ищет позицию искомого значения «Статус» в просматриваемом диапазоне заголовков второй таблицы и возвращает таким образом номер столбца, из которого нам надо вытащить значение

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

Зачем этот пост?

Хотел поделиться в сети информацией для будущих искателей решения схожей проблемы, так как мой ТОП операций и функций excel ctrl+c и ctrl+v 🙂 Но мне не подвернулось готового решения, когда я искал. Может кому-то повезет больше с моей помощью.

Как подтянуть данные из другой таблицы? (Вариант 3. ИНДЕКС + ПОИСКПОЗ)

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

Однако, в экселе есть один универсальный вариант решения задачи, который не только заменяет сразу обе функции, но и лишён её ключевых недостатков: это функция ИНДЕКС (англ. INDEX).

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

• первый аргумент (диапазон A:D) представляет собой таблицу (диапазон), из которой нужно взять данные. Можно задать совершенно любой непрерывный массив ячеек.
• Второй аргумент — номер строки, из которой мы берем данные в этом массиве (2)
• Третий аргумент — номер столбца, из которого мы берем данные (3)

По сути, функция из примера возвращает значение ячейки С2.

Итак, думаю, как работает функция ИНДЕКС — вполне понятно.

Остаётся вопрос: если вводить вручную номер строки и столбца, то как это поможет решить задачу?

Ответом будет использование функции ПОИСКПОЗ (англ. MATCH).

Эта функция возвращает порядковый номер строки в массиве.

В данном примере, мы берем значение ячейки C2 (искомое значение ключа, которое в ВПР или ГПР было бы первым аргументом), и ищем его в столбце A. Если функция найдёт в указанном диапазоне нужное значение, выведет его порядковый номер. Иначе, мы получим ошибку #Н/Д.

Важно! Функции ВПР, ГПР и ПОИСКПОЗ находят только первое совпадение. Имейте это в виду, если в таблице, в которой вы осуществляете поиск, есть дубликаты.

Третьим аргументом функции ПОИСКПОЗ является уже знакомый нам интервальный просмотр. Правда теперь можно использовать три значения:

• 0 — точный поиск
• -1 — интервальный поиск, диапазон отсортирован по убыванию
• 1 — интервальный поиск, диапазон отсортирован по возрастанию

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

Примеры использования связки ИНДЕКС + ПОИСКПОЗ (иногда этот способ также называют «левый ВПР») можно детально изучить в приложенном файле.

Функции связанных значений в DAX: RELATED и RELATEDTABLE в Power BI и Power Pivot

до конца распродажи осталось:

» Функции связанных значений в DAX: RELATED и RELATEDTABLE в Power BI и Power Pivot

Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):

• DAX функция RELATED
• DAX функция RELATEDTABLE

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В этой статье мы поговорим про функции RELATED и RELATEDTABLE в Power BI и Power Pivot.

Именно эти функции позволяют, находясь в одной таблице, дотянуться до значений в другой таблице через внутренние связи DAX, настроенные во вкладке «Связи» в Power BI или Excel (Power Pivot).

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

Да, и еще один момент, до 24 ноября 2023 г. у Вас имеется возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 60% (вместо 10000, всего за 4000 руб.)

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

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 60% (до 24 ноября 2023 г.): узнать подробнее

до конца распродажи осталось:

DAX функция RELATED в Power BI и Power Pivot

RELATED () — находясь в одной таблице, позволяет в рамках контекста строки получить связанное значение из второй таблицы по связи «Многие к одному».

Синтаксис: RELATED ([Столбец])

Рассмотрим пример DAX формулы с участием RELATED.

В Power BI Desktop имеются 2 исходные таблицы «Менеджеры Продажи» и «Менеджеры Отделы»:

Между ними настроена связь по полю «Менеджер» по типу «Многие к одному», то есть, много менеджеров может находится в одном отделе:

Попробуем в таблицу «Менеджеры Продажи» через связь добавить третий столбец [Отделы]:

Для этого создадим в Power BI Desktop во вкладке «Моделирование» вычисляемый столбец и постараемся в его формуле прописать столбец [Отделы] из связанной таблицы «Менеджеры Отделы»:

Отделы = 'МенеджерыОтделы'[Отдел]

И по данной формуле у нас всплывает ошибка:

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

Давайте перепишем формулу вычисляемого столбца с использованием RELATED:

Отделы = RELATED ('МенеджерыОтделы'[Отдел])

И вот теперь, все прошло удачно. Функция RELATED позволила нам, находясь в одной таблице, дотянуться до значения другой таблицы по связи «Многие к одному» и создать соответствующий новый столбец:

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

То есть, нам нужно подсоединиться через связь к таблице «Менеджеры Продажи» и там посчитать количество продаж каждого менеджера. Это количество мы можем посчитать при помощи DAX функции COUNTWROS, которая считает количество строк. Ну а подсоединяться к другой таблице через связь мы будем с помощью RELATED.

Пропишем данную формулу:

КолПродаж = COUNTROWS( RELATED ('МенеджерыПродажи') )

И у нас опять получилась ошибка:

На самом деле ошибка закономерна, так как функция RELATED работает по связи «Многие к одному», что у нас было соблюдено в первом примере и что мы нарушили сейчас. Так как в данном примере у нас уже связь другая, а именно «Один ко многим» (один отдел может в себе содержать много менеджеров) и с этой связью RELATED уже не работает.

Тут нам на помощь может прийти вторая функция работы по связям в DAX: RELATEDTABLE.

DAX функция RELATEDTABLE в Power BI и Power Pivot

RELATEDTABLE () — находясь в одной таблице, возвращает связанную таблицу значений из второй таблицы по связи «Один ко многим», где одна строка соответствуем многим строкам.

Синтаксис: RELATEDTABLE (‘Таблица’)

Функция RELATEDTABLE, в отличие от RELATED, уже не возвращает какое-то одно скалярное значение, она возвращает именно связанную таблицу значений, с которыми мы можем что-то сделать, например посчитать количество строк:

Давайте доработаем формулу предыдущего примера и исправим там ошибку, а именно, заменим функцию RELATED на RELATEDTABLE:

КолПродаж = COUNTROWS( RELATEDTABLE ('МенеджерыПродажи') )

Теперь у нас все хорошо, пример формулы с RELATEDTABLE отработал отлично и посчитал нам количество продаж по каждому менеджеру:

На этом, с разбором функций связи языка DAX в Power BI и Power Pivot — RELATED и RELATEDTABLE, все.

Также, напоминаю Вам, что до 24 ноября 2023 г. у Вас имеется шикарная возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 60% (вместо 10000, всего за 4000 руб.)

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

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 60% (до 24 ноября 2023 г.): узнать подробнее

До конца распродажи осталось:

Пожалуйста, оцените статью:

1. 5
2. 4
3. 3
4. 2
5. 1

Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

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

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

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

до конца распродажи осталось:

Что еще посмотреть / почитать?

Как в Power BI (Power Pivot) отследить одно отфильтрованное значение? DAX функции HASONEVALUE и HASONEFILTER

Как в Power BI (Power Pivot) вывести найденный текст? DAX функции LEFT, RIGHT и MID

Таблица последовательных значений — DAX функция GENERATESERIES в Power BI и Power Pivot