Случайный образец в Excel с условным оператором?

Question

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

Можно ли случайно выбрать x% элементов под именем каждого человека в Excel?

Ниже приведен очень маленький размер выборки в качестве иллюстрации - настоящая электронная таблица содержит 100 имен и тысячи строк данных.

Мне нужна формула для случайного выбора заданного% (например, 5%)"точек данных" для каждого "имени".

(Column A)              (Column B)
Name                    Data Point
Sue                      123
Sue                      456
Sue                      789
Brian                    586
Brian                    566
Brian                    949
Brian                    928
Bob                      643
Bob                      235
Bob                      594

Answer 1

Это должно быть возможно с помощью формулы =rand() . Добавление случайного числа в каждую строку. Затем скопируйте и вставьте случайное число в качестве значения на себя, чтобы оно постоянно сохраняло свое значение. Затем вы можете отсортировать его по случайному числу, оставить его для первых значений X и удалить его для остальных. Затем вы возвращаете его в исходном порядке.

Чтобы дать вам более точные инструкции, мне нужно лучшее объяснение или скриншот вашего листа.

Answer 2

Расширяя (но ответвляясь от) ответ MartinX:

Я предполагаю (поскольку вы не сказали иначе), что A и B - единственные столбцы, в которых есть данные.  Если это не так, замените C и D в приведенном ниже объяснении именами двух доступных столбцов.

• Введите =RAND() в ячейку C2 (я предполагаю, что есть ровно одна строка заголовка, и эти данные начинаются со строки 2, как показано).
• Введите =C2>=LARGE(C$2:C$100*(A$2:A$100=A2), CEILING(0.5*COUNTIF(A$2:A$100,A2),1)) в ячейку D2 , заменив 100 на число ≥ номер последней строки, содержащей данные, и замена 0.5 на значение x % (т. Е. X ÷ 100; поэтому для 5% используйте 0,05) и нажмите Ctrl+Shift+Enter. 
• Выберите ячейки C2:D2 и перетащите / заполните вниз, чтобы покрыть все строки данными.

Столбец D теперь будет заполнен значениями TRUE и FALSE .  x % (округленных в большую сторону) строк для каждого имени будет TRUE . Затем вы можете работать непосредственно с этим списком или использовать его в качестве основы для условного форматирования или чего угодно.

Заметки:

• RAND() является изменчивой функцией.  Каждый раз, когда вы что-либо меняете, все значения RAND() будут переоцениваться / пересчитываться / изменяться.  Вы можете предотвратить это, отключив автоматический расчет рабочей книги.  Это может быть проще сделать, как предлагает MartinX: скопировать и вставить значения.
• Итак, столбец C заполнен случайными числами от 0,000000 до 1,000000.

• Формула в столбце D , очевидно, является более сложной.  Я объясню это задом наперед: наизнанку, справа налево.

  • =COUNTIF(A$2:A$100,A2) считает строки с тем же именем, что и текущая строка.  Таким образом, для строк 2, 3 и 4 («Сью») это значение равно 3.  Для строк 5, 6, 7 и 8 («Брайан») это значение равно 4.  Как обычно, мы используем знаки доллара ($) в A$2:A$100 чтобы указать, что мы всегда хотим посмотреть именно на этот абсолютный диапазон ячеек, но мы используем A2 без знака доллара, чтобы указать относительную адресацию, то есть значение столбца A в текущем ряду.

  • CEILING(0.5*(the_above),1) дает округленное число х % (в моем примере 50%) от числа людей с таким именем.  Важно не допустить, чтобы это значение равнялось 0.  Если вы хотите округлить в целом, но по-прежнему округлять числа меньше 1,00 до 1, используйте что-то вроде MAX(INT(0.5*(the_above)),1) .

    Для данных данного примера (с «Сью» и «Брайан») это значение равно 2 для каждой строки.  Если у вас было 831 строка данных, скажем, для «Джона», т. COUNTIF(A$2:A$100,A2) равен 831, а x равен 5 (то есть мы хотим провести аудит 5% = 0,05 этих строк), тогда это значение будет равно 42 для каждой строки «Джон» (0,05 * 831 = 41,55, что округляет до 42).

  • (A$2:A$100=A2) создает виртуальный массив, который имеет значение TRUE в позициях, соответствующих строкам с тем же именем, что и текущая строка, и FALSE противном случае.  Итак, если мы посмотрим на строку 2 (или 3 или 4), этот виртуальный массив будет выглядеть как {T, T, T, F, F, F, F, F, F, F}.
  • C$2:C$100*(A$2:A$100=A2) расширяет виртуальный массив из предыдущего шага, умножая его на случайные числа в столбце C В математическом контексте TRUE равна 1, а ЛОЖЬ - 0.  Итак, если случайные числа в столбце C равны {0,83, 0,17, 0,60, 0,42, 0,95,…}, то этот виртуальный массив будет выглядеть как {0,83, 0,17, 0,60, 0, 0,…}, то есть случайные числа соответствует текущему человеку, и нули для всех остальных.
  • Таким образом, LARGE(C$2:C$100*(A$2:A$100=A2), CEILING(0.5*COUNTIF(A$2:A$100,A2),1)) - это LARGE((virtual_array_of_selected_random_numbers), N) , где N - х % от числа людей с текущим именем.  Это дает N е наибольшее число в этом массиве.  Итак, с данными вашего примера (с «Сью» и т.д.) и мои гипотетические числа (выше), это будет 0,60 для «Сью» (второе по величине число в массиве 0,83, 0,17, 0,60, 0, 0,…).

  • =C2>=LARGE((that_mess)) определяет, является ли случайное число в текущей строке больше или равно N му наибольшему случайному числу для текущего человека.  В значительной степени по определению, это будет верно для N строк текущего человека.

    Функция LARGE - это ключ к получению х % элементов под именем каждого человека, как того требует вопрос.  Следуя приведенному выше примеру, если у вас было 831 строка данных для «Джона», а x равно 5 (т. Е. Мы хотим проверить 5% = 0,05 этих строк), то CEILING(0.05*COUNTIF(A$2:A$100,A2),1) будет 42 для Джона, а LARGE(…, 42) - 42-е по величине число.  Таким образом, C2>=(that_number) верно для самых больших 42 чисел (но см. Следующий абзац); то есть, это TRUE для 42 строк.  Таким образом, это логическое выражение TRUE на 5% из 831 строки для «John».  То есть, это автоматизирует шаг, который выполняется вручную в ответе MartinX.

    Теоретически возможно, что два случайных числа будут равны.  Например, числа Сью могут быть 0,83, 0,17 и 0,17. Тогда 2-й по величине из них (LARGE({those_numbers},2)) будет равен 0,17, и поэтому это будет верно для 3 строк.  (То есть вы получите еще одно TRUE чем хотите.)  Это случится очень редко.

• Нам нужно ввести формулу в ячейку D2 с помощью Ctrl+Shift+Enter, чтобы сделать ее «формулой массива», которая необходима для работы с виртуальными массивами.

Вот пример результата:

          

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