Перекрестные ссылки на данные в Excel

Я пытаюсь запустить инструмент рекламной аналитики для своего бизнеса. У меня есть электронная таблица Excel с 3 листами. Лист 1 содержит данные о кликерах рекламы, Лист 2 содержит информацию о покупателях продукта, а Лист 3 должен собирать данные на основе данных перекрестных ссылок между Листами 1 и 2.

Столбцы на листе 1 следующие:

    A           B       C               D              E                F       G
------------------------------------------------------------------------------------
product type, tag, click date/time, IP address, expiry time (days), expiry date/time

Каждая строка представляет собой щелчок. Время истечения срока действия (дни) определяется типом продукта, а время/дата истечения срока действия – это время истечения срока действия (дни), добавляемое к дате/времени клика. Тег — это код, который сообщает мне, где кликер нашел ссылку.

Столбцы на листе 2 следующие:

       A           B               C
---------------------------------------------
product type, IP address, purchase date/time.

Каждая строка представляет продажу продукта.

На листе 2 больше строк, чем на листе 1.

Есть три части информации, которые я хочу сопоставить между Листом 1 и Листом 2: IP address, product type и попадает ли purchase date/time (Лист 2) между щелчком date/time и истечением срока действия date/time. Я сделал формулы, которые могут проверить, происходит ли это, но только для назначенных ячеек на каждом листе.

Я пытаюсь заставить Excel сопоставить одну строку из Листа 1 со всеми строками в Листе 2. Поскольку на Листе 2 больше строк, чем на Листе 1, может быть несколько совпадений для каждого тега, определенного на Листе 1, поскольку клиенты могут решить купить более одного продукта после перехода по ссылке.

Есть ли способ попытаться сопоставить данные между двумя листами, а затем собрать количество совпадений в листе 3? Этого может быть проще достичь, если сначала сопоставить IP-адрес и тип продукта, а затем попытаться посмотреть, попадает ли purchase date/time в число кликов date/time и срок действия date/time.

Любая помощь в этом будет принята с благодарностью и заранее большое спасибо. Это беспокоит меня некоторое время, и я не могу понять это.


analytics excel match cross-reference
person maxthackray    schedule 29.01.2016    source источник
comment
COUNTIFS может быть тем, что ты ищешь. Если вы собираетесь проверить, был ли щелчок вовремя...   -  person Dirk Reichel    schedule 29.01.2016
comment
что-то вроде =COUNTIFS(Sheet1!A:A,Sheet2!A2,Sheet1!D:D,Sheet2!B2,Sheet1!C:C,"<="&Sheet2!C2,Sheet1!F:F,">="&Sheet2!C2), а затем скопируйте (чтобы проверить лист2 против листа1)   -  person Dirk Reichel    schedule 29.01.2016

Ответы (2)


arrow_upward
0
arrow_downward

Я думаю, у меня есть хорошее начало для вас. С vlookup вы можете указать несколько критериев. Сначала начните с внесения полезных данных на один лист - я бы взял столбцы D и E "листа2" и использовал их:

In D:

=VLOOKUP(A2& " "&B2,Sheet1!$A$2:[BOTTOM RIGHT OF TABLE],3)

3 в конце указывает, что вы хотите вернуть дату клика.

In E:

=VLOOKUP(A2& " "&B2,Sheet1!$A$2:[BOTTOM RIGHT OF TABLE],6)

4 в конце указывает, что вы хотите перенастроить дату истечения срока действия.

Как только они окажутся на одном листе, вы можете выполнить сравнение оператора if, чтобы увидеть, попадает ли дата (столбец C) между двумя вашими датами (столбец D и E). Вот оператор if для этого:

=IF(AND(C2>=D2,C2<=E2),1,0)

В этом есть один недостаток, и это если есть несколько совпадений как для продукта, так и для ip - это вернет только 1, но в противном случае вы говорите о написании сценария в VBA.

person Beutler    schedule 29.01.2016
comment
Привет Beutler, спасибо за ваши предложения. Это очень полезно. Что касается вашего последнего комментария, означает ли это, что если есть несколько совпадений для IP и типа продукта, я должен использовать VBA? Я где-то читал о замене функции ВПР комбинацией ПОИСКПОЗ и ИНДЕКС. Поможет ли это мне, или мне все равно придется использовать VBA, чтобы найти несколько совпадений? - person maxthackray; 29.01.2016
comment
К сожалению, я плохо знаком с ИНДЕКСОМ и ПОИСКПОЗОМ. Из того, что я понял, он имеет аналогичную функциональность, но имеет 4 преимущества (ни одно из которых не решает проблему, которая у вас есть): это 1) вы можете искать влево (vlookup должен иметь значение поиска в самом дальнем левом столбце) 2) Лучшая защита от вставленных/удаленных строк. 3) Vlookup имеет предел поиска 255 символов. 4) ИНДЕКС/ПОИСКПОЗ быстрее. Ни одно из них, на мой взгляд, не является огромным преимуществом, если только у вас нет серьезных данных и у вас нет проблем с их удобной организацией. - person Beutler; 29.01.2016
comment
Не уверен, что вы пробовали vlookup, но еще я забыл упомянуть, что мне пришлось вытащить .s из IP-адреса (найти заменить . даром). Если вы хотите попробовать VBA, я полагаю, вам придется иметь несколько умных циклов (возможно, с использованием словарей), чтобы организовать данные желаемым образом. - person Beutler; 29.01.2016
comment
В порядке. Я думаю, что понял. Можно ли использовать функцию ВПР для поиска и сопоставления строки с использованием IP-адреса и типа продукта, а затем заставить Excel вставить дату/время покупки на лист 3 в той же строке? Затем, после того, как это будет сделано, могу ли я запустить ту же функцию сопоставления, но пропустить строку, которая уже сопоставлена, и найти следующую, которая соответствует? или мне придется сделать это в VBA? - person maxthackray; 29.01.2016
comment
Решение, которое я опубликовал, должно решить первую часть вашего вопроса. Я действительно не думаю, что есть манипуляции с формулами, которые вы можете использовать в Excel, чтобы решить эту проблему без VBA, итеративный характер поиска по нескольким спискам, как это, для программирования. Извини :/ - person Beutler; 29.01.2016
comment
В порядке. Я в значительной степени зашел так далеко. Есть ли у вас какие-либо идеи о кодировании того, что я хочу в VBA? - person maxthackray; 29.01.2016
comment
Да... это довольно запутанно, на самом деле я сейчас работаю над похожей проблемой, но, вероятно, ее нельзя будет использовать до конца следующей недели. Основная идея, если вы хотите провести какое-то исследование, состоит в том, чтобы создать словарь из коллекций (для каждого листа у вас будет словарь, содержащий информацию о каждой строке). Тогда вам понадобится вложенный цикл for, чтобы сравнивать каждую строку одного листа с каждой строкой другого. Внутри внутреннего цикла будут ваши операторы If, отвечающие запрошенным вами условиям. Я постараюсь опубликовать краткую версию, когда закончу. - person Beutler; 29.01.2016
comment
Удивительно. Вы спасатель жизни. Дай мне знать, когда закончишь. У меня есть довольно хорошее представление о том, как будут работать логические шаги с точки зрения передачи данных, но мои навыки VBA практически равны нулю. Я посмотрю на это и посмотрю, что я могу придумать. Спасибо за вашу помощь. Был действительно полезен - person maxthackray; 29.01.2016
comment
Привет @beutler. Я понял полный код для моей проблемы. Это видео действительно помогло мне встать на правильный путь. Прошло много испытаний, проб и ошибок, но это видео навело меня на правильный путь. youtube.com/watch?v=QOxhRSCfHaw - person maxthackray; 01.02.2016

arrow_upward
-2
arrow_downward

В Excel вы можете сослаться на ячейку на основе листа, подобного этому, и сделать ее на третьем листе:

=Лист1!A1+Лист2!A2

person Tanveer Ali    schedule 29.01.2016