Как создать рандомизированные списки слов, зависящие от основного списка?

Question

Я использую Excel для Mac 2011.

Для лингвистического проекта я хочу сделать следующее. У меня будет такой список слов (на самом деле он будет намного больше):

Основной список:

1. Сумка
2. Нога
3. Магнит
4. Яблоко
5. Жвачка

Сначала я копирую и вставляю слова на новый лист. Затем я создаю случайные числа (используя RAND) в соседнем столбце. Чтобы получить рандомизированный список, я использую SORT, чтобы отсортировать список слов по значению случайно созданных чисел.

1. Сумка .011234123
2. Нога .001324234
3. Магнит .01324243
4. Яблоко .013245588
5. Резинка .0123412341

Сортировать по КОЛОНКЕ ЧИСЕЛ

ЭТО МЕНЯ ЗАСТАВЛЯЕТ:::

Рандомизация 1:

5. Жвачка
6. Магнит
7. Яблоко
8. Сумка
9. Позволять

У меня будет несколько рандомизаций. Предположим, я хочу изменить слово. Есть ли способ сделать рандомизацию зависимой от главного списка? То есть, если я внесу изменения в главный список (изменю слово), есть ли способ, чтобы эти изменения также были выполнены в моей рандомизации?

Answer 1

Я мог бы предложить решение, использующее комбинацию функций RAND(), RANK() и VLOOKUP():

Сначала создайте свой основной список:

Затем добавьте столбец, который генерирует случайное число, используя RAND():

Затем столбец, который будет использовать RANK() для ранжирования этих случайных чисел:=RANK(E2,E:E)

Наконец, используйте ROW() и VLOOKUP() для возврата отсортированных рангов:=VLOOKUP(ROW()-1,C:D,2,0)

Любые слова, которые вы добавляете в основной список, приведут к пересчету каждого RAND(), что затем изменит рейтинг, по сути создавая новый рандомизированный порядок.

Повторите это три раза для трех необходимых вам случайных списков.

Answer 2

Предположим, что ваши данные находятся на Листе 1, начиная с ячейки A1, и отсортированы по столбцу B, который является случайным числом, введите эту формулу, начиная со строки 1, и выполните автозаполнение:

=INDEX(Sheet1!A$1:A$6,ROW())

Он просто ROW()увеличивает row_numаргумент по INDEX()мере его заполнения.

Или, если вы не хотите сортировать, попробуйте эту формулу:

=INDEX(Sheet1!A$1:A$6,MATCH(LARGE(Sheet1!B$1:B$6,ROW()),Sheet1!B$1:B$6,0))

Это немного веселее. Опять же, используя ROW()автоматическое увеличение, он использует LARGE()для поиска следующего по величине случайного числа, а затем INDEX/MATCHвозвращает слово на этой позиции.

Имейте в виду, что RAND()это изменчиво, то есть оно будет пересчитываться каждый раз, когда ваш рабочий лист будет рассчитываться. Я рекомендую использовать ручной расчет, если вы собираетесь использовать этот метод.