Загальна формула
=INDEX(names,RANDARRAY(n,1,1,COUNTA(names),TRUE))
Резюме
Щоб створити випадковий список імен, ви можете використовувати функцію INDEX та RANDARRAY, щоб вибрати випадкові імена з існуючого списку. У наведеному прикладі формула в D5 має вигляд:
=INDEX(names,RANDARRAY(10,1,1,COUNTA(names),TRUE))
який повертає 10 випадкових значень із названого діапазону "імена" (B5: B104).
Пояснення
По суті, ця формула використовує функцію INDEX для отримання 10 випадкових імен з іменованого діапазону, що називається "імена", який містить 100 імен. Наприклад, щоб отримати п’яте ім’я зі списку, ми використовуємо INDEX таким чином:
=INDEX(names,5)
Однак фокус у цьому випадку полягає в тому, що ми не хочемо жодного імені у відомому місці, ми хочемо 10 випадкових імен у невідомих місцях від 1 до 100. Це чудовий варіант використання функції RANDARRAY, яка може створити випадковий набір цілих чисел у заданому діапазоні. Працюючи зсередини, ми використовуємо RANDARRAY, щоб отримати 10 випадкових чисел від 1 до 100, як це:
RANDARRAY(10,1,1,COUNTA(names)
Функція COUNTA використовується для отримання динамічного підрахунку імен у списку, але ми могли б замінити COUNTA твердим кодом 100, у цьому випадку з тим самим результатом:
=INDEX(names,RANDARRAY(10,1,1,100,TRUE))
У будь-якому випадку RANDARRAY поверне 10 чисел у масиві, який виглядає так:
(64;74;13;74;96;65;5;73;84;85)
Примітка: ці цифри є лише випадковими і не відображаються безпосередньо на наведеному прикладі.
Цей масив повертається безпосередньо до функції INDEX як аргумент рядка:
=INDEX(names, (64;74;13;74;96;65;5;73;84;85)
Оскільки ми даємо INDEX 10 номерів рядків, це дасть 10 результатів, кожен з яких відповідає імені в даній позиції. 10 випадкових імен повертаються в діапазоні розливу, що починається з комірки D5.
Примітка: RANDARRAY - це нестабільна функція, і вона перераховуватиметься щоразу, коли робочий аркуш змінюється, внаслідок чого застосовуються значення. Щоб зупинити автоматичне сортування значень, можна скопіювати формули, а потім скористатися спеціальною вставкою> Значення для перетворення формул у статичні значення.
Запобігання дублікатам
Однією з проблем наведеної вище формули (залежно від ваших потреб) є те, що RANDARRAY іноді генерує повторювані числа. Іншими словами, немає гарантії, що RANDARRAY поверне 10 унікальних номерів.
Щоб забезпечити 10 різних імен зі списку, ви можете адаптувати формулу, щоб довільно відсортувати повний список імен, а потім отримати перші 10 імен зі списку. Формула в F5 використовує такий підхід:
=INDEX(SORTBY(names,RANDARRAY(COUNTA(names))),SEQUENCE(10))
Тут підхід такий самий, як і вище - ми використовуємо INDEX для отримання 10 значень зі списку імен. Однак у цій версії формули ми сортуємо список імен випадковим чином, перш ніж передати список INDEX таким чином:
SORTBY(names,RANDARRAY(COUNTA(names)))
Тут функція SORTBY використовується для випадкового сортування списку імен за значеннями масиву, створеними функцією RANDARRAY, як це пояснюється тут більш докладно.
Нарешті, нам потрібно отримати 10 значень. Оскільки ми вже маємо імена у випадковому порядку, ми можемо просто запитати перші 10 за допомогою масиву, створеного функцією SEQUENCE, таким чином:
SEQUENCE(10)
SEQUENCE створює масив послідовних чисел:
(1;2;3;4;5;6;7;8;9;10)
який повертається функції INDEX як аргумент рядка. Потім INDEX повертає перші 10 імен у діапазоні розливу, як оригінальна формула.
