Как перемешать ячейки в эксель

Обновлено: 06.07.2024

Рассмотрим несколько быстрых способов случайного выбора имен, чисел или любых других данных. Вы также узнаете, как получить случайную выборку без дубликатов и как произвольным образом выбрать указанное количество или определённый процент ячеек, строк или столбцов.

Независимо от того, проводите ли вы исследование рынка для запуска нового продукта или оцениваете результаты своей маркетинговой кампании, важно использовать для анализа объективную выборку данных. И самый простой способ добиться этого - получить случайный выбор в Excel.

Что такое случайная выборка?

Прежде чем обсуждать методы выборки, давайте предоставим небольшую справочную информацию о случайном выборе и о том, когда целесообразно его использовать.

В теории вероятностей и статистике случайная выборка - это подмножество данных, выбранных из более крупного набора данных, также известного как генеральная совокупность . Каждый элемент случайной выборки выбирается совершенно произвольно и имеет равную вероятность быть выбранным. Зачем он вам нужен? В основном, чтобы получить непредвзятое представление обо всей совокупности.

Например, вы хотите провести небольшой опрос среди своих клиентов. Очевидно, было бы неразумно рассылать анкету каждому человеку в вашей большой базе данных. Итак, кого вы побеспокоите своими вопросами? Будет ли это 100 новых клиентов, или первые 100 клиентов, перечисленные в алфавитном порядке, или 100 человек с самыми короткими именами? Ни один из этих подходов не соответствует вашим потребностям, потому что они изначально субъективны. Чтобы получить беспристрастную выборку, в которой у всех клиентов есть равные возможности быть выбранными, произведите случайный выбор, используя один из методов, описанных ниже.

Случайный выбор значения из списка

В Excel нет встроенной функции для случайного выбора ячеек, но вы можете использовать одну из функций для генерации случайных чисел в качестве временного решения. Их, наверное, нельзя назвать простыми и интуитивно понятными формулами, но они работают.

Предположим, у вас есть список имен в ячейках A2:A15, и вы хотите выбрать одно из них. Это можно сделать с помощью одной из следующих формул:

Вот и все! Средство выбора случайных имен для Excel настроено и готово к работе:

Примечание. Имейте в виду, что СЛУЧМЕЖДУ – это непостоянная функция, то есть она будет пересчитываться при каждом изменении, которое вы вносите в рабочий лист. В результате ваш случайный выбор из списка также будет постоянно меняться. Чтобы этого не произошло, вы можете скопировать извлеченное имя и вставить его как значение в другую ячейку (Специальная вставка > Значения).

Естественно, эти формулы могут не только выбирать случайные имена, но также и случайные числа, даты или любые другие данные из ячеек.

Как работают эти формулы

Мы используем функцию ИНДЕКС для извлечения значения из списка на основе случайного порядкового номера, возвращаемого СЛУЧМЕЖДУ.

То есть, функция СЛУЧМЕЖДУ генерирует случайное целое число между двумя указанными вами значениями. Для нижнего порога вы указываете число 1. Для верхнего — используете СЧЁТЗ() или ЧСТРОК(), чтобы получить общее количество ячеек с данными. В результате СЛУЧМЕЖДУ() возвращает случайный номер ячейки в вашем наборе данных. Этот номер передается в функцию ИНДЕКС, сообщая ей, какую по счёту ячейку выбрать. Второй аргумент (номер столбца) можно не указывать, поскольку он у нас только один.

Примечание. Этот метод хорошо подходит для выбора одного случайного значения из списка. Если ваш выбор должен включать несколько результатов, приведенная выше формула может возвращать несколько вхождений одного и того же значения, поскольку функция СЛУЧМЕЖДУ не защищена от дубликатов. Это особенно актуально, когда вы выбираете относительно большую выборку из относительно небольшого списка.

Эту задачу можно также решить с помощью формулы

Функция СЛУЧМЕЖДУ() случайным образом выбирает позицию списка, из которой нужно взять одно значение ( для этой функции вероятность выбрать любую строку одинакова).

Если скопировать эту формулу вниз по столбцу, то будет выбрано несколько значений из списка. Однако, есть подводный камень: есть вероятность выбора несколько одинаковых значений, то есть получения дубликатов. Особенно, если ваш список относительно небольшой.

В следующем примере показано, как сделать случайный выбор в Excel без дубликатов.

Случайный выбор без дубликатов.

Есть несколько способов выбрать случайные данные без дубликатов в Excel. К примеру, вы должны использовать функцию СЛЧИС, чтобы присвоить случайное число каждой ячейке, а затем выбрать несколько ячеек с помощью формулы индекса ранга.

Со списком имен в ячейках A2: A16 выполните следующие действия, чтобы извлечь несколько имен:

Введите формулу случайного числа в B2 и скопируйте ее вниз по столбцу:
=СЛЧИС()
Поместите приведенную ниже формулу в C2, чтобы извлечь случайное значение из столбца A:

Скопируйте приведенную выше формулу в столько ячеек, сколько случайных значений вы хотите выбрать. В нашем примере мы копируем формулу еще в четыре ячейки (C2: C6).

Вот и все! Извлекаются пять имен без повторов:

Как работает эта формула

Как и в предыдущем примере, вы используете функцию ИНДЕКС для извлечения значения из столбца A на основе номера позиции. Для этого требуются две разные функции:

Формула СЛЧИС() заполняет столбец B случайными числами.
Функция РАНГ() возвращает ранг случайного числа из колонки B и из той же строки. Например, РАНГ(B2;$B$2:$B$16) получает ранг числа из B2 (0,188906401). B2 сравнивается со всеми числами из диапазона $B$2:$B$16. Оно занимает по величине 13-е место. При копировании в C3 относительная ссылка B2 изменяется на B3 и возвращает ранг числа из B3 и так далее.
Число, возвращаемое функцией РАНГ(), передается функции INDEX, поэтому она выбирает значение из соответствующей позиции. Значит, в С3 нужно поместить 13-е по порядку значение из диапазона $A$2:$A$16.

Предупреждение! Как показано на скриншоте выше, наша случайная выборка Excel содержит только уникальные значения. Теоретически вероятность появления дубликатов здесь очень мала, но все же существует. И вот почему: в очень большом наборе данных СЛЧИС() может сгенерировать повторяющиеся числа, а РАНГ() будет возвращать одинаковое место для этих чисел. Лично у меня во время тестов ни разу не было дубликатов, но теоретически такая вероятность есть.

Если вы ищете железобетонно надёжную формулу для случайного выбора только с уникальными значениями, используйте комбинацию РАНГ + СЧЁТЕСЛИ вместо просто РАНГ.

Полная формула немного громоздка, но на 100% не содержит дубликатов:

Примечания:

Как и СЛУЧМЕЖДУ(), функция СЛЧИС() в Excel также пересоздает новые числа при каждом пересчете вашего рабочего листа, что приводит к изменению набора выбора. Чтобы результат оставался неизменным, скопируйте его и вставьте в другое место как значение (Специальнаявставка > Значения).
Если одно и то же имя (число, дата или любое другое значение) встречается в исходном наборе данных более одного раза, результат может также содержать несколько вхождений одного и того же значения.

А вот еще одно похожее на предыдущее решение, в котором используется функция НАИМЕНЬШИЙ().

Рядом со столбцом значений для выборки добавляем столбец случайных чисел. Как обычно, используем для этого функцию СЛЧИС().

Предположим, нам нужно выбрать 5 имён. Для этого в колонке С записываем цифры от 1 до 5.

Далее используем формулу

Поясним, как это работает. При помощи функции НАИМЕНЬШИЙ() выбираем самое маленькое значение из сгенерированных чисел. Функция ПОИСКПОЗ помогает нам определить его позицию в списке. И затем при помощи ИНДЕКС() извлекаем имя, находящееся в этой позиции.

При копировании вниз по столбцу следующая формула будет брать второе наименьшее значение из списка случайных чисел, и так далее.

Но можно сэкономить себе время и не создавать колонку с порядковыми номерами. В качестве счётчика принято использовать функцию СТРОКА().

При копировании вниз СТРОКА(A1) изменится на СТРОКА(А2) и соответственно возвратит номер 2. И так далее. В остальном всё работает точно так же.

Думаю, вы понимаете, что вместо НАИМЕНЬШИЙ() можно вполне использовать НАИБОЛЬШИЙ(). Дело вкуса 😊.

Как выбрать случайные строки в Excel

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

Вставьте новый столбец справа или слева от вашей таблицы (столбец D в этом примере).
В первой ячейке вставленного столбца, исключая заголовки столбцов, введите формулу =СЛЧИС()
Дважды щелкните маркер заполнения, чтобы скопировать формулу вниз по столбцу. В результате в каждой строке будет записано случайное число.
Сортировка этих чисел от наибольшего к наименьшему (сортировка в порядке возрастания переместит заголовки столбцов в нижнюю часть таблицы, поэтому обязательно выполняйте сортировку по убыванию). Для этого перейдите на вкладку Данные в группу Сортировка и фильтр и нажмите кнопку Сортировка. Excel автоматически расширит выделение и предложит выбрать столбец и порядок сортировки.

Вы можете для экономии времени использовать кнопки сортировки АЯ или ЯА, но при этом курсор нужно обязательно установить на столбец с формулой СЛЧИС(). Заголовок колонки желательно написать по-русски, иначе он имеет шансы переместиться в конец таблицы.

Теперь осталось нажать ОК, и строки таблицы будут пересортированы и произвольно перемешаны.

Пусть вас не смущает тот факт, что после сортировки по столбцу D вы видите в нем совершенно не упорядоченные числа. Дело в том, что сортировка меняет порядок строк, и тут же вновь происходит пересчёт всех формул СЛЧИС(). Но ведь наша задача не отсортировать, а произвольно перемешать строки, не так ли?

Если вас не совсем устраивает рандомизация вашей таблицы, нажмите кнопку сортировки еще раз, чтобы вновь перемешать её.

Наконец, выберите необходимое количество строк для вашей выборки, скопируйте их в буфер обмена и вставьте куда хотите.

Как случайно выбрать в Excel с помощью инструмента Randomize.

Теперь, когда вы знаете несколько формул для получения случайной выборки в Excel, давайте посмотрим, как можно добиться того же результата буквально одним щелчком мыши.

Если в вашем Excel установлена надстройка Ultimate Suite , вы можете использовать инструмент случайной сортировки. Для этого делаете следующее:

Выберите любую ячейку в вашей таблице.
Перейдите на вкладку AblebitsTools > Utilites и нажмите кнопки Randomize > Select Randomly (Случайный выбор):

Слева появится панель настройки, о которой следует сказать чуть подробнее.

Поясним, что скрывается за каждой из цифр.

По умолчанию будет выбран весь ваш диапазон данных. Выберите другой диапазон, выделив его при помощи мышки или введя адрес в это поле.
Если вы изменили диапазон, снова выделите всю таблицу, щелкнув значок «Развернуть выделение» .
Вы можете исключить строки заголовков из выбора, если таковые есть, установив флажок Моя таблица имеет 1 строку заголовка .

Замечание. Если в вашей таблице более широкий заголовок, кликните по этой надписи, введите реальное количество строк, и они не будут выделены и использованы.

Например, вот как мы можем выбрать 5 случайных строк из нашего набора данных:

И через секунду вы получите случайный выбор:

Теперь вы можете нажать Ctrl + C чтобы скопировать выделенное, а затем использовать комбинацию Ctrl + V , чтобы вставить это в нужное место на том же или другом листе.

Совершенно аналогичным образом вы можете сделать случайную выборку столбцов или отдельных ячеек. И точно так же скопировать их в нужное место. Не нужно ни формул, ни дополнительных столбцов. Всего пара кликов.

Если вы хотите протестировать инструмент «Случайный выбор» на своих данных, загрузите полнофункциональную пробную версию Ultimate Suite for Excel.

Если у вас еще остались вопросы относительно случайного выбора из списка либо перемешивания ячеек и строк в произвольном порядке — пишите в комментариях.

Расширенный генератор случайных чисел для Excel - Теперь, когда вы знаете, как использовать случайные функции в Excel, позвольте мне продемонстрировать вам более быстрый, простой и не требующий формул способ создания списка случайных чисел, дат или текстовых строк… Как создать случайные числа в Excel - В статье объясняются особенности алгоритма получения случайных чисел Excel и показано, как использовать функции СЛЧИС и СЛУЧМЕЖДУ для чисел, дат, паролей и других текстовых выражений. Прежде чем мы углубимся в…

Перемешать числа или слова в Excel, нажав на одну кнопку, не получится. По умолчанию можно сортировать от большего к меньшему (для чисел) и от А до Я (для слов) и в обратном порядке. Но как отсортировать данные рандомно (случайно)? Это довольно просто и решается применением формулы =СЛЧИС(). Далее будет детальное описание перемешивания данных при использовании этой формулы. Итак, открываем документ в Microsoft Office Excel с какими-либо данными, в нашем случае это будет список с названиями сортов винограда.

В столбце B вводим формулу: случайное число =СЛЧИС(). После ввода этой формулы в ячейке будет случайное число в диапазоне от 0,0 до 1,0.

Дальше описана дополнительная информация по работе с формулой. Для формирования случайного числа в диапазоне от 0,0 до 10,0 умножаем формулу на 10 (=СЛЧИС()*10). Для случайных значений в промежутке от 0,0 до 100 умножаем формулу на 100, в диапазоне до 1000 умножаем на 1000 и так далее по аналогии. Если необходимо сформировать числа в определённом диапазоне, например с 50 до 100, то вставляем формулу следующего вида =СЛЧИС()*(50-100)+100.

Для расстановки слов в случайном порядке вид рандомного числа не имеет значения. После подстановки формулы, растягиваем её до конца списка.

Следующим этапом производим сортировку по столбцу со случайными числами (в примере столбец B). Сортировать от минимального к максимальному или наоборот не принципиально. Если выделен столбец только со случайными числами, то важно автоматически расширить выделенный диапазон до столбца с названиями.

Если же выделены нужные столбцы (это делается нажатием сочетанием Ctrl+A), то окошко с предложением автоматически расширить диапазон не выскачет. Таким образом, сортировка в случайном порядке произведена.

Предположим, у вас есть диапазон ячеек, и теперь вы хотите перемешать строки в случайном порядке, как показано на скриншоте ниже, как вы можете быстро и случайным образом перемешать их в Excel?

Случайным образом перемешайте строки / столбцы / диапазон ячеек с помощью Kutools for Excel

Перемешать строки / значения столбца с помощью формулы

1. Выберите список ячеек рядом с диапазоном, например D1: D8, а затем введите эту формулу. = СЛЧИС () , см. снимок экрана:

2. Затем нажмите Ctrl + Enter. Теперь вы можете увидеть, что отображается список случайных данных.

3. Теперь вы можете перейти к Данные Вкладка и выберите Сортировать от меньшего к большему or Сортировать от большего к меньшему как вам нужно. Смотрите скриншот:

4. Затем появится всплывающее диалоговое окно и проверьте Расширить выбор вариант. И нажмите "Сортировать".

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

Затем вы можете удалить ячейки формулы.

Однако, если вы хотите перетасовать диапазон ячеек по столбцам или все ячейки, как показано на скриншотах ниже, как вы можете решить эту проблему в Excel? Теперь перейдите к следующему методу, я представлю вам многофункциональный инструмент, который поможет вам легко решить эту проблему.

Случайным образом перемешайте строки / столбцы / диапазон ячеек с помощью Kutools for Excel

Если у вас есть Kutools for Excel установлен, есть утилита - Сортировка диапазона случайным образом может перетасовать ваши данные по всей строке, целому столбцу или всем ячейкам диапазона, а также может выбирать случайные ячейки, случайные столбцы или случайные строки из диапазона.

После установки Kutools for Excel, сделайте следующее: （Бесплатная загрузка Kutools for Excel прямо сейчас!)

1. Выберите ячейки диапазона, которые нужно перемешать случайным образом, и нажмите Kutools > Диапазон > Сортировка / выбор диапазона случайным образом. Смотрите скриншот:

2. Затем в Сортировка / выбор диапазона случайным образом диалога под Сортировать на вкладке выберите нужный вариант.

3. Затем нажмите Ok or Применить.

Сортировать по целым строкам

Сортировать по столбцам целиком

Сортировать ячейки в диапазоне

Сортировать ячейки в каждой строке

Сортировать ячейки в каждом столбце

Работы С Нами Kutools for ExcelАвтора Сортировка диапазона случайным образом утилита, вы можете выбирать ячейки случайным образом в диапазоне.

В Excel ， вы всегда можете столкнуться с этой проблемой, если у вас есть диапазон данных, содержащий несколько повторяющихся записей, и теперь вы хотите объединить повторяющиеся данные и суммировать соответствующие значения в другом столбце, как показано на следующих снимках экрана. Как бы вы могли решить эту проблему?

Объедините повторяющиеся строки и суммируйте значения с помощью функции консолидации

Консолидация - это полезный инструмент для нас, позволяющий объединить несколько листов или строк в Excel, поэтому с помощью этой функции мы также можем суммировать несколько строк на основе дубликатов. Пожалуйста, выполните следующие действия:

1. Щелкните ячейку, в которой вы хотите разместить результат на текущем листе.

2. Перейти к щелчку Данные > Консолидировать, см. снимок экрана:

3. В Консолидировать диалоговое окно:

(1.) Выберите Сумма от функция раскрывающийся список;
(2.) Щелкните кнопку, чтобы выбрать диапазон, который вы хотите объединить, а затем нажмите Добавить кнопка, чтобы добавить ссылку на Все ссылки список;
(3.) Проверить Верхний ряд и Левая колонка от Используйте ярлыки в вариант. Смотрите скриншот:

4. После завершения настройки нажмите OK, а дубликаты объединяются и суммируются. Смотрите скриншот:

Внимание: Если в диапазоне нет строки заголовка, снимите флажок Верхний ряд из Используйте ярлыки в опцию.

Объедините повторяющиеся строки и суммируйте / усредняйте соответствующие значения в другом столбце

Kutools for Excel's Продвинутые ряды комбайна помогает объединить несколько повторяющихся строк в одну запись на основе ключевого столбца, а также может применять некоторые вычисления, такие как сумма, среднее значение, количество и т. д., для других столбцов. Нажмите, чтобы загрузить Kutools for Excel!

Объедините повторяющиеся строки и суммируйте значения с кодом VBA

Следующий код VBA также может помочь вам объединить повторяющиеся строки на листе, но в то же время исходные данные будут уничтожены, вам необходимо сделать резервную копию копии данных.

1. Удерживайте ALT + F11 ключи, затем он открывает Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модульи вставьте следующий код в Окно модуля.

Код VBA: объедините повторяющиеся строки и просуммируйте значения

3, Затем нажмите F5 ключ для запуска этого кода, и вам нужно выбрать диапазон, который вы хотите объединить, во всплывающем окне подсказки. Смотрите скриншот:

4, Затем нажмите OK, повторяющиеся строки были объединены, а значения суммированы.

Внимание: Если вы хотите использовать этот код, вам лучше сделать копию файла, чтобы избежать разрушения данных, и этот код применяется только к двум столбцам.

Объедините повторяющиеся строки и суммируйте значения с помощью Kutools for Excel

Здесь я представляю удобный инструмент - Kutools for Excel для вас это Расширенные ряды комбинирования также может быстро решить эту проблему.

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Выберите нужный вам диапазон и нажмите Kutools > Слияние и разделение > Расширенные ряды комбинирования. Смотрите скриншот:

2. В Расширенные ряды комбинирования диалог, проверьте У моих данных есть заголовки если в вашем диапазоне есть заголовки, выберите имя столбца, дубликаты которого вы хотите объединить, и щелкните Основной ключ, см. снимок экрана:

3. Затем выберите имя столбца, значения которого вы хотите суммировать, и нажмите Рассчитать > Сумма или другие расчеты по мере необходимости. Смотрите скриншот:

4. Нажмите Ok чтобы закрыть диалоговое окно, вы увидите, что дубликаты объединены, а соответствующие данные в другом столбце суммируются. Смотрите скриншоты:

Объедините соответствующие строки на основе повторяющихся значений в другом столбце с помощью Kutools for Excel

Иногда вы хотите объединить строки на основе повторяющихся значений в другом столбце, Расширенные ряды комбинирования of Kutools for Excel также может оказать вам услугу, пожалуйста, сделайте следующее:

1. Выберите диапазон данных, который вы хотите использовать, а затем щелкните Kutools > Слияние и разделение > Расширенные ряды комбинирования для Расширенные ряды комбинирования диалоговое окно.

2. В Расширенные ряды комбинирования В диалоговом окне щелкните имя столбца, на основе которого вы хотите объединить другие данные, и щелкните Основной ключ, см. снимок экрана:

3. Затем щелкните имя другого столбца, данные которого вы хотите объединить, и щелкните Сочетать чтобы выбрать разделитель для разделения объединенных значений, см. снимок экрана:

4. Затем нажмите Ok, все значения в одной ячейке в столбце A были объединены вместе, см. скриншоты:

Читайте также: