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

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

Столбец A — имя.
Столбец B — офис.
Столбец C — год.

Ячейка G2 — интересующий вас офис.
Ячейка 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.

Он работает, создавая массив, который содержит номера строк (если критерии совпадают) или ноль, если нет. Затем он беретн-е наибольшее значение, гденслучайное число от 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функциях так, чтобы он соответствовал ячейке над начальной ячейкой.

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