Excel — 2 таблицы — если совпадают 2 ячейки в одной строке, вернуть другую ячейку той же строки

Работа с двумя отдельными наборами данных (с дубликатами) Набор данных уникален, идентифицируется идентификатором. Там может не быть записи для временной метки, которую я требую. Наборы данных довольно большие, и из-за дубликатов нельзя использовать vlookup.

Образцы:

Таблица 1:

    Device Name|Time Bracket| On/Off?
    ID1        |06:20:00    | 
    ID2        |06:20:00    |
    ID3        |06:30:00    |

Таблица 2:

    Device Name |Timestamp  |On/Off?
    ID1         |06:20:00   |On
    ID2         |06:50:00   |Off
    ID3         |07:20:00   |Off

Чего я хочу достичь:

Я хочу, чтобы оператор if проверял, соответствует ли: 1) идентификатор устройства И 2) совпадение метки времени

Если это так, верните значение On/Off из таблицы 2. Если нет, то я хочу, чтобы оно возвращало значение ячейки над ним, IF это одно и то же устройство, иначе просто поместите «отсутствует» в сотовый.

Я думал, что смогу сделать это с помощью некоторых операторов IF, например:

    =if(HOUR([@[Time Bracket]]) = HOUR(Table13[@[Timestamp Rounded (GMT)]]) and 
    minute([@[Time Bracket]]) = minute(Table13[@[Timestamp Rounded (GMT)]]) and 
    [@[Device Name]]=Table13[@[Device Name]], Table13[@[On/Off?]], 
    IF([@[Device Name]]=Table13[@[Device Name]], INDIRECT("B" and Rows()-1), "absent"))

(Я добавил несколько новых строк для удобства чтения)

Однако, похоже, это вообще не решает... что я делаю неправильно? Это даже правильный способ добиться этого?

Я также пробовал что-то подобное с VLookUp, но это ужасно не удалось.

Спасибо всем!


excel nested-if
person R.Sama    schedule 14.06.2016    source источник
comment
Вы хотели бы использовать ИНДЕКС/ПОИСКПОЗ, см. здесь stackoverflow.com/questions/18767439/ Есть два метода, посмотрите на второй.   -  person Scott Craner    schedule 14.06.2016
comment
Спасибо, позвольте мне взглянуть на это...   -  person R.Sama    schedule 15.06.2016
comment
Привет, спасибо, я реализовал конкатенацию/вспомогательный столбец, и это, похоже, сработало!   -  person R.Sama    schedule 15.06.2016

Ответы (2)


arrow_upward
0
arrow_downward

Чтобы не иметь дело с формулами массива или слиянием строк, которые (не в вашем случае) все еще могут быть неправильными в конце, я предлагаю использовать COUNTIFS из-за того, что у вас очень небольшое количество результатов (только включено или выключено) ...

для первой таблицы (начиная с A1, поэтому формула находится в C2):

=IFERROR(CHOOSE(
 OR(COUNTIFS(Table13[Device Name],[@[Device Name]],Table13[Timestamp],[@[Time Bracket]],Table13[On/Off?],"On"))+
 OR(COUNTIFS(Table13[Device Name],[@[Device Name]],Table13[Timestamp],[@[Time Bracket]],Table13[On/Off?],"Off"))*2
 ,"On","Off","Error"),IF(A1=[@[Device Name]],C1,"Absent"))

это также покажет «Ошибку» совпадения для «Вкл» и «Выкл»... чтобы пропустить это и увеличить скорость, вы также можете использовать:

=IF(COUNTIFS(Table13[Device Name],[@[Device Name]],Table13[Timestamp],[@[Time Bracket]],Table13[On/Off?],"On"),"On",
 IF(COUNTIFS(Table13[Device Name],[@[Device Name]],Table13[Timestamp],[@[Time Bracket]],Table13[On/Off?],"Off"),"Off",
 IF(A1=[@[Device Name]],C1,"Absent")))

Для обоих «Имя устройства» находится в столбце A, «Временная скобка» в столбце B и «Вкл / Выкл?» в столбце C, а таблица начинается в строке 1... Если это не так, измените A1 и C1, чтобы они совпадали

(Также вставлены разрывы строк для лучшего чтения)

Изображение для демонстрации макета:

макет

Я выбрал вторую формулу, чтобы показать, как она работает... кроме того, эта формула не должна возвращать 0... Я запутался

person Dirk Reichel    schedule 14.06.2016
comment
Эй, спасибо, что вернулся ко мне. Я пробовал оба, но получаю кучу нулей... Итак, для обоих столбцов я установил идентификатор устройства | Временная метка/скобка | вкл выкл. В моей таблице есть заголовки, поэтому я изменил это на: =IF(COUNTIFS(Table13[Device Name],[@[Device Name]],Table13[Timestamp],[@[Time Bracket]],Table13[On/Off ?], Вкл), Вкл, ЕСЛИ (СЧЁТЕСЛИМН(Таблица 13[Имя устройства],[@[Имя устройства]],Таблица 13[Временная метка],[@[Временная скобка]],Таблица 13[Вкл/Выкл?],Выкл), Off,IF(A2=[@[Device Name]],C2,Absent))) Не знаю, что происходит. - person R.Sama; 15.06.2016
comment
@R.Sama не знаю, почему это происходит ... Я проверил это, и это сработало без ошибок ... также добавил изображение к ответу ... также, пожалуйста, проверьте, есть ли какие-либо скрытые символы, такие как начальные пробелы и все это... - person Dirk Reichel; 15.06.2016
comment
Ах, я обнаружил (одну из) проблем с использованием этого сейчас - форматирование ячеек - потому что у меня есть разрозненные данные, поступающие со всех сторон, трудно поддерживать правильное форматирование ячеек, а это означает, что иногда, даже если отображаемое значение правильное, а то, как его интерпретирует Excel, - нет. Попробовав это на простом, самостоятельно созданном наборе данных, он действительно работает! - person R.Sama; 15.06.2016

arrow_upward
0
arrow_downward

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

Еще один извлеченный урок: мыслите нестандартно и ищите простые решения, вместо того, чтобы пытаться + быть слишком умным, как я делал... :)

person R.Sama    schedule 15.06.2016