Excel: случайный выбор в таблице, который соответствует двум критериям

Question

Вот пример моих данных:

Name:        Office:   Years:

John Smith   Canada      1

Bob Smith    Canada      1

Jake Smith   Canada      1

Sarah Smith  Canada      1

На другом листе я хочу вывести случайное имя на основе Office и Years. Когда я перетаскиваю формулу вниз, я хочу, чтобы она каждый раз давала другое имя (вот где я застрял). Я попробовал несколько формул, и это самое близкое.

=INDEX($B$2:$B$436,MATCH(SMALL(IF($D$2:$D$436&$F$2:$F$436=$H$3&$I$3,$G$2:$G$436),1),$G$2:$G$436,0))

Столбец B = Имя Столбец D = Офисный столбец F = Годы, тогда H3 и I3 - мои значения поиска

столбец G представляет собой набор случайных чисел = rand()

Answer 1

Я собираюсь поставить рандомизацию в самой формуле. Вы всегда можете поместить это в другой столбец, если хотите.

Колонка А это имя
Колонна Б офисная
Колонка C - это год

Cell G2 - интересующий вас офис
Cell G3 - это год, который вас интересует

=INDIRECT("A"&LARGE(IF($B$2:$B$7=G2,IF($C$2:$C$7=G3,ROW($A$2:$A$7),0),0), RANDBETWEEN(1,COUNTIFS(B:B,G2,C:C,G3))))

Это формула массива, и ее нужно вводить, используя Ctrl+Shift+Enter.

Он работает путем создания массива, который имеет номера строк (если критерии соответствуют) или ноль, если его нет. Затем он принимает n- е наибольшее значение, где n - это случайное число от 1 до количества строк, соответствующих критериям.

Answer 2

Предполагая, что вы хотите вернуть имена в A2 вниз (где A1 - заголовок или пробел), затем используйте эту формулу массива в A2

=INDEX($B$2:$B$436,SMALL(IF(($D$2:$D$436=$H$3)*($F$2:$F$436=$I$3)*(COUNTIF(A$1:A1,$B$2:$B$436)=0),ROW($B$2:$B$436)-ROW($B$2)+1),RANDBETWEEN(1,SUM(($D$2:$D$436=$H$3)*($F$2:$F$436=$I$3)*(COUNTIF(A$1:A1,$B$2:$B$436)=0)))))

подтвердите нажатием CTRL+SHIFT+CTRL и скопируйте вниз - измените диапазон A$1:A1 в функциях COUNTIF чтобы он соответствовал ячейке над стартовой ячейкой.

Примечание: - вам не нужен столбец случайных чисел с этим методом