EXCEL: поиск значения и минимальной даты

Question

У меня проблема с Excel. Мои данные находятся в 2 разных электронных таблицах.

Листы логин и события:

АВТОРИЗОВАТЬСЯ

user_id (COL A)

дата регистрации (COL B)

СОБЫТИЯ

user_id (COL A)

дата окончания (COL B)

доход (COL C)

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

=MIN(IF(A2=Events!$A$2:$A$12282,(Events!B$2:B$12282)-B2))

В этом случае для каждого пользователя при входе в систему я пытаюсь найти соответствующие даты в событиях, которые соответствуют этому пользователю. Затем я пытаюсь найти разницу в датах и найти минимальную в массиве, но вместо этого она дает максимальное значение и / или не работает. Любая помощь приветствуется!

Answer 1

Попробуйте что-то вроде этого: в третьем столбце первого листа введите эту формулу

=VLOOKUP(A$2,Events!A$2:A$12282,1,0)

Это должно поместить соответствующее значение end_date пользователя в A2. Осторожно, это сработает, если для каждого пользователя существует только одна конечная дата.

Если это было то, что вы искали, просто перетащите формулу, чтобы заполнить все значения.

Теперь в четвертом столбце вы можете сделать что-то вроде

=C$2-B$2

Снова перетащите формулу, чтобы заполнить все пропущенные значения.

Теперь у вас должны быть все расчеты готовы, и вам просто нужно сделать

=MIN(D2:D12282)

Answer 2

На странице входа в систему я бы добавил еще один столбец days_to_first_event . И в C2 добавьте следующую формулу массива (нажмите Ctrl+Shift+Enter, чтобы установить ее)

=MIN(IF(tbl_events[user_id]=A2,tbl_events[end_date]))-B2

Это находит самое раннее end_date для user_id на листе Events , затем вычитает signup_date пользователя. (Я отформатировал их как таблицу с именем tbl_events но вы можете заменить свой диапазон)

Затем вы можете усреднить, основываясь на положительных значениях, поскольку все отрицательные значения означают, что они еще не завершили событие. Используя эту формулу

=SUMIF(C:C,">=0")/COUNTIF(C:C,">=0")

Можно вместить все это в одну формулу, но я не могу обернуть это вокруг себя этим утром.

Answer 3

Вы не можете сравнить одну ячейку с диапазоном, как у вас есть:

IF(A2=Events!$A$2:$A$12282...

Вместо этого вы можете попробовать функцию поиска.