У меня есть лист MS Excel со 100 именами в одном столбце.
На другом листе у меня есть сетка ячеек 10 x 10, из которой я хочу случайным образом присвоить имя из столбца.
Есть ли относительно простой способ добиться этого или он будет включать работу с типом VBA?
2 ответа
Это можно сделать с помощью вспомогательного столбца, который создает случайное число от 1 до 100. С вашими именами в A2: A101. В B2 введите:
=AGGREGATE(15,7,ROW($1:$100)/(COUNTIFS($B$1:B1,ROW($1:$100))=0),RANDBETWEEN(1,100-COUNT($B$1:B1)))
И скопируйте.
Будет случайным образом выбрано число от 1 до 100 с буквой k, а AGGREGATE будет RANDBETWEEN(1,100-COUNT($B$1:B1)). В то время как COUNTIFS($B$1:B1,ROW($1:$100))=0 следит за тем, чтобы мы не получали дубликатов.
Затем мы используем ИНДЕКС / ПОИСКПОЗ, чтобы найти значение. Поместите это в верхний правый угол сетки:
=INDEX($A:$A,MATCH((ROW($A1)-1)*10+COLUMN(A$1),$B:$B,0))
Как это лекарство снова и снова, он ищет 1-10 в первом ряду, 11-20 во втором и так далее. И поскольку столбец подстановки рандомизирован, он будет случайным.
Затем скопируйте 10 и 10 вниз:
Если у вас есть Office 365 Excel, то ИНДЕКС / ПОИСКПОЗ можно заменить этой динамической версией, которая автоматически выведет 10×10:
=INDEX(A:A,MATCH(SEQUENCE(10,10),B:B,0))
Предполагая, что имена хранятся в столбце A:
- В столбце B примените формулу
=RAND() - Скопируйте и вставьте полученные значения в столбец B, перезаписав формулу.
- В столбце C примените формулу
=RANK(B2, $B$2:$B$101). Это позволит вам присвоить каждому имени номер от 1 до 100. - Над сеткой 10х10 добавьте числа от 1 до 10. Сделайте то же самое слева от сетки 10х10. Они будут служить заголовками строк и столбцов.
Теперь, предполагая, что заголовки ваших строк находятся в E2:E11 и заголовки ваших столбцов находятся в F1:O1…
- Введите формулу
=INDEX($A$2:$A$101, MATCH(($E2-1)*10+F$1, $C$2:$C$101,0))в ячейку F2 и перетащите по сетке 10×10
