Как генерировать текст в эксель
Нечастая, но интересная задача: выбрать из массива данных (списка) случайным образом N элементов. Причин для ее возникновения может быть несколько, например:
- Объем данных слишком велик, поэтому мы удовлетворяемся анализом случайной выборки из полного набора данных.
- Выбор победителей из числа участников какого-либо конкурса или лотереи.
В любом случае перед нами стоит задача отобрать случайным образом заданное количество элементов из какого-либо набора (например, вот такого):
Способ 1. Случайная сортировка
Добавить к нашему списку еще один столбец и вставить в него функцию генерации случайных чисел СЛЧИС (RAND) . Затем отсортировать наш список по добавленному столбцу (Данные - Сортировка) и взять N первых элементов из получившейся таблицы:
Минусы такого способа очевидны - придется вручную каждый раз пересортировывать список, если нам необходимо будет сделать другую случайную выборку. В плюсах - простота и доступность.
Способ 2. Функция НАИМЕНЬШИЙ
Этот способ заключается в использовании функции НАИМЕНЬШИЙ (SMALL) для выбора из списка N позиций с наименьшим случайным числом в столбце А:
После выбора пяти (в нашем примере) наименьших случайных чисел из столбца А, мы вытаскиваем имена, которые соответствуют этим числам с помощью функции ВПР (VLOOKUP).
Способ 3. Случайная выборка без повторов - функция Lotto на VBA
Можно создать простую функцию на VBA, которая будет выдавать заданное количество случайных чисел из нужного интервала. Откроем редактор Visual Basic (ALT+F11 или в старых версиях Excel через меню Сервис - Макрос - Редактор Visual Basic), вставим новый модуль через меню Insert - Module и скопируем туда текст вот такой функции:
У этой функции будет три аргумента:
- Bottom - нижняя граница интервала случайных чисел
- Top - верхняя граница интервала случайных чисел
- Amount - количество случайных чисел, которое мы хотим отобрать из интервала
Т.е., например, чтобы отобрать 5 случайных чисел от 10 до 100, нужно будет ввести =Lotto(10;100;5)
Теперь эту функцию легко использовать для отбора случайных значений. Добавим к нашему списку столбец с нумерацией и будем отбирать людей по случайным номерам, которые генерирует функция Lotto:
Обратите внимание, что наша функция Lotto должна быть введена как формула массива, т.е. сначала необходимо выделить диапазон ячеек результатов (D2:D6) затем ввести нашу функцио Lotto и, после ввода аргументов функции, нажать Ctrl+Shift+Enter, чтобы ввести эту функцию именно как функцию массива во все выделенные ячейки.
Ну, а дальше останется при помощи уже знакомой функции ВПР (VLOOKUP) вытащить имена из списка, соответствующие случайным номерам.
В надстройку Пакет анализа входит инструмент Генерация случайных чисел , с помощью которого можно сгенерировать случайные числа, имеющие различные распределения .
С помощью надстройки Пакет Анализа можно сгенерировать случайные числа следующих распределений:
- Равномерное распределение (непрерывное)
- Нормальное распределение
- Распределение Бернулли
- Биномиальное распределение
- Распределение Пуассона
Кликнув на нужное распределение, можно открыть статью, где подробно написано о генерировании случайных чисел с помощью надстройки Пакет Анализа и с помощью формул.
В MS EXCEL имеется множество функций, с помощью которых можно сгенерировать случайные числа, принадлежащие различным распределениям. И лишь часть из этих распределений представлено в окне инструмента Генерация случайных чисел. Чтобы сгенерировать случайные числа из других распределений см. статью про распределения MS EXCEL , в которой можно найти ссылки на другие распределения.
Примечание : С помощью другого инструмента надстройки Пакет анализа , который называется «Выборка», можно извлечь случайную выборку из конечной генеральной совокупности . Подробнее см. статью Случайная выборка из генеральной совокупности в MS EXCEL .
Модельное распределение
С помощью надстройки Пакет Анализа можно сгенерировать числа, имеющее так называемое модельное распределение . В этом распределении нет никакой случайности - генерируются заранее заданные последовательности чисел .
Поле Число переменных задает количество столбцов генерируемых данных. Т.к. в них будут сгенерированы совершенно одинаковые данные, то нет особого смысла указывать в поле Число переменных значение отличное от 1.
Поле Число случайных чисел можно оставить незаполненным, т.к. оно рассчитывается автоматически в зависимости от значений, указанных в группе Параметры диалогового окна. Например, при параметрах, указанных на рисунке выше, в каждом столбце будет выведено по 24 «случайных» числа: четыре нечетных числа 1; 3; 5; 7 (от 1 и до 7; шаг равен 2) будут повторены по 2 раза, а каждая последовательность будет повторена по 3 раза (4*2*3=24).
Произвольное дискретное распределение
С помощью надстройки Пакет Анализа можно сгенерировать числа, имеющие произвольное дискретное распределение , т.е. распределение, где пользователь сам задает значения случайной величины и соответствующие вероятности.
Необходимо следить, чтобы сумма вероятностей модельного распределения была равна 1. Для этого в MS EXCEL имеется специальная функция ВЕРОЯТНОСТЬ() .
СОВЕТ : О генерации чисел, имеющих произвольное дискретное распределение , см. статью Генерация дискретного случайного числа с произвольной функцией распределения в MS EXCEL . В этой статье также рассмотрена функция ВЕРОЯТНОСТЬ() .
Недавно ко мне обратился один знакомый с просьбой помочь с генерацией всех возможных фраз, состоящих из набора заданных слов. Подобного рода задачи могут возникать при составлении списков ключевых слов и фраз для интернет-рекламы и SEO-продвижения, когда нужно перебрать все возможные варианты перестановок слов в поисковом запросе:
В математике такая операция называется декартовым произведением. Официальное определение звучит так: декартовым произведением множеств А и В называется множество всех пар, первая компонента которых принадлежит множеству А, а вторая компонента принадлежит множеству В. Причем элементами множеств могут быть как числа, так и текст.
В переводе на человеческий язык, это означает, что если в множестве А у нас, например, слова «белый» и «красный», а в множестве В «БМВ» и «Мерседес», то после декартова произведения этих двух наборов мы получим на выходе совокупность всех возможных вариантов фраз, составленных из слов обоих списков:
- белый БМВ
- красный БМВ
- белый Мерседес
- красный Мерседес
. т.е. как раз то, что нам нужно. Давайте рассмотрим пару способов решения этой задачи в Excel.
Способ 1. Формулы
Начнём с формул. Предположим, что в качестве исходных данных мы имеем три списка исходных слов в столбцах A, B и C, соответственно, причем количество элементов в каждом списке может меняться:
Сначала сделаем три столбца с индексами, т.е. порядковыми номерами слов из каждого списка во всех возможных сочетаниях. Первый ряд единичек (E2:G2) введём вручную, а для остальных используем следующую формулу:
Логика здесь простая: если индекс в вышестоящей предыдущей ячейке уже дошёл до конца списка, т.е. равен количеству элементов в списке вычисленному функцией СЧЁТЗ (COUNTA) , то мы запускаем нумерацию заново. В противном случае - увеличиваем индекс на 1. Обратите особое внимание на хитрое закрепление диапазонов знаками доллара ($), чтобы можно было скопировать формулу вниз и вправо.
Теперь, когда у нас есть порядковые номера нужных нам слов из каждого списка, можно извлечь и сами слова с помощью функции ИНДЕКС (INDEX) в три отдельных столбца:
Если вы раньше не сталкивались в своей работе с этой функцией, то очень советую хотя бы по диагонали её изучить - она выручает в очень многих ситуациях и полезна ничуть не меньше (и даже больше!), чем популярная ВПР (VLOOKUP) .
Ну, а после останется только склеить построчно получившиеся фрагменты с помощью символа конкатенации (&):
. или (если у вас последняя версия Excel) с помощью удобной функции ОБЪЕДИНИТЬ (TEXTJOIN) , умеющей склеивать всё содержимое указанных ячеек через заданный символ-разделитель (пробел):
Способ 2. Через Power Query
Power Query - это мощная надстройка для Microsoft Excel, выполняющая две основные задачи: 1. загрузку данных в Excel из почти любых внешних источников и 2. всяческие трансформации загруженных таблиц. Power Query уже встроена в Excel 2016-2019, а для Excel 2010-2013 она устанавливается как отдельная надстройка (скачать можно с официального сайта Microsoft совершенно бесплатно). Если вы ещё не начали использовать Power Query в своей работе, то самое время об этом подумать, ибо преобразования типа вышеописанных делаются там легко и непринужденно, буквально за пару движений.
Сначала загрузим исходные списки как отдельные запросы в Power Query. Для этого для каждой таблицы выполним следующие действия:
- Превратим таблицы в "умные" кнопкой Форматировать как таблицу на вкладке Главная (Home - Format as Table) или сочетанием клавиш Ctrl + T . Каждой таблице автоматически будет присвоено имя Таблица1,2,3. , которое, впрочем, можно при желании поменять на вкладке Конструктор (Design) .
- Установив активную ячейку в таблицу, нажмем кнопку Из таблицы (From Table) на вкладке Данные (Data) или на вкладке Power Query (если она установлена у вас как отдельная надстройка для Excel 2010-2013).
- В открывшемся окне редактора запросов выберем команду Главная - Закрыть и загрузить - Закрыть и загрузить в. (Home - Close&Load - Close&Load to..) и затем опцию Только создать подключение (Create only connection) . Это оставит загруженную таблицу в памяти и позволит обращаться к ней в будущем.
Если всё сделаете правильно, то на выходе в правой панели должны получиться три запроса в режиме Только подключение с именами наших таблиц:
Теперь щёлкнем правой кнопкой мыши по первому запросу и выберем команду Ссылка (Reference) , чтобы сделать его обновляемую копию, а затем добавим к данным дополнительный столбец через команду Добавление столбца - Настраиваемый столбец (Add Column - Custom Column) . В окне ввода формулы введём имя нового столбца (например, Фрагмент2) и предельно простое выражение в качестве формулы:
. т.е., другими словами, название второго запроса:
После нажатия на ОК мы увидим новый столбец, в каждой ячейке которого будет лежать вложенная таблица с фразами из второй таблицы (увидеть содержимое этих таблиц можно, если щёлкнуть мышью в фон ячейки рядом со словом Table):
Останется развернуть всё содержимое этих вложенных таблиц с помощью кнопки с двойными стрелками в заголовке полученного столбца и сняв при этом флажок Использовать исходное имя столбца как префикс (Use original column name as prefix) :
. и мы получим все возможные сочетания элементов из первых двух наборов:
Дальше всё аналогично. Добавляем еще один вычисляемый столбец с формулой:
…, а затем ещё раз разворачиваем вложенные таблицы – и вот у нас уже все возможные варианты перестановок слов из трёх наборов, соответственно:
Осталось выделить все три столбца слева-направо, удерживая Ctrl , и сцепить их содержимое через пробел, используя команду Объединить столбцы (Merge Columns) с вкладки Преобразование (Transform) :
Получившиеся результаты можно выгрузить обратно на лист знакомой уже командой Главная - Закрыть и загрузить - Закрыть и загрузить в. (Home - Close&Load - Close&Load to..) :
Если в будущем что-то изменится в наших исходных таблицах с фрагментами, то достаточно будет просто обновить созданный запрос, щёлкнув по результирующей таблице правой кнопкой мыши и выбрав команду Обновить (Refresh) или нажав сочетание клавиш Ctrl + Alt + F5 .
Сгенерировать случайное число легко с помощью функции = RANDBETWEEN (range), но пробовали ли вы когда-нибудь сгенерировать случайное значение из заданного списка, как показано ниже?
Сгенерировать случайное значение из заданного списка по формуле
На самом деле, есть несколько простых формул, которые помогут быстро сгенерировать случайное значение из заданного списка.
Выберите пустую ячейку, в которую будет помещено случайное значение, введите эту формулу = ИНДЕКС ($ A $ 2: $ A $ 16, RANDBETWEEN (2; 16)) , Затем нажмите Enter ключ. Смотрите скриншот:
В формуле A2: A16 - это заданный список, который вы хотите использовать, 2 и 16 - это номер начальной и конечной строки списка.
Если вы хотите сгенерировать несколько случайных значений из списка, вы можете перетащить формулу вниз по мере необходимости. Смотрите скриншот:
Вы можете нажать F9 ключ для обновления значений.
Внимание: Если вы хотите сгенерировать случайное число на основе списка, вы можете использовать эту формулу = ИНДЕКС ($ I $ 2: $ I $ 7, СЛУЧАЙНО (1; 6)) , и нажмите Enter ключ.
Иногда может потребоваться сгенерировать случайные строки в ячейках, например разные пароли. В этой статье мы пытаемся показать вам некоторые приемы создания различных случайных строк в Excel.
Создавайте случайные символьные строки с формулами
Следующие формулы могут помочь вам создать случайные числа, буквы и буквенно-цифровые значения в диапазоне в Excel.
1. Чтобы создать случайное 5-значное число от 10000 до 99999, используйте эту формулу: = СЛУЧМЕЖДУ (10000,99999) , и нажмите Enter key, в ячейке будет отображаться 5-значное число, затем выберите ячейку и перетащите маркер заполнения в диапазон, который вы хотите содержать эту формулу, был сгенерирован диапазон 5-значных чисел, см. снимки экрана:
Внимание: вы можете изменить аргументы по своему усмотрению.
2. Чтобы создать случайную букву, используйте эту формулу: = СИМВОЛ (СЛУЧМЕЖДУ (65,90)) . Эта формула может генерировать случайную букву от a до z, если вам нужно создать четыре случайных буквы, вам нужно использовать символ &, чтобы добавить буквы. Такие как = СИМВОЛ (RANDBETWEEN (65,90)) & CHAR (RANDBETWEEN (65,90)) & CHAR (RANDBETWEEN (65,90)) & CHAR (RANDBETWEEN (65,90)) . Затем вы получите четыре случайных буквы, а затем перетащите дескриптор заполнения в диапазон, который вы хотите содержать эту формулу. Смотрите скриншот:
Заметки:
(1.) Формула действительна в Excel 2007, 2010 и 2013, но не в Excel 2003. В Excel 2003 используйте формулу = СИМВОЛ (INT (RAND () * 25 + 65)) & CHAR (INT (RAND () * 25 + 65)) & CHAR (INT (RAND () * 25 + 65)) & CHAR (INT (RAND () * 25 + 65))
(2.) В формуле 65 - A, а 90 - Z.
(3.) Вы можете использовать символ &, чтобы добавить необходимое количество букв.
3. Чтобы создать случайную буквенно-цифровую строку, состоящую из двух букв и двух цифр, используйте эту формулу: = СИМВОЛ (СЛУЧАЙНОМУ (65,90)) И СИМВОЛ (СЛУЧМЕЖДУ (65,90)) & СЛУЧМЕЖДУ (10,99) И вы получите следующие строки в диапазоне, содержащем две буквы и две цифры:
Внимание: вы можете изменить аргументы, чтобы получить цифру, как вам нужно, и использовать &, чтобы добавить количество букв.
Легко генерировать случайные строки символов в выбранном диапазоне:
Легко сгенерировать случайные символьные строки с определенными символами и длиной текста с помощью Вставить случайные данные полезности Kutools for Excel.
Загрузите Kutools для Excel прямо сейчас! (30-дневная бесплатная трасса)
Генерация случайных символьных строк с кодом VBA
Если вы считаете, что приведенные выше формулы сложны и проблематичны, следующий код VBA может вам намного проще. Пожалуйста, сделайте так:
1. Удерживайте ALT + F11 ключи, и он открывает Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модули, и вставьте следующий макрос в Модули окно.
3. Затем сохраните и закройте код, в ячейке введите эту функцию = RandomizeF (x, y) для вставки случайной строки символов с минимальной длиной x символов и максимальной длиной y символов.
4. В этом примере я буду использовать функцию = RandomizeF (5,10) для создания символьной строки от 5 до 10 символов. Затем нажмите Enter , выберите ячейку и перетащите маркер заполнения в диапазон, который вы хотите содержать эту функцию. И случайные буквенно-цифровые и определенные символьные строки, которые были созданы от 5 до 10 символов. Смотрите скриншот:
Создание случайных строк символов с помощью Kutools for Excel
Есть ли способ сгенерировать случайные строки с буквами, цифрами, специальными символами и т. Д.? Kutools for Excel's Вставить случайные данные - отличный генератор случайных чисел (и текстовой строки), который может генерировать случайные числа, случайную текстовую строку или случайные числа, текст и символы со всеми видами символов, включая буквы, числа, специальные символы, пробелы и даже настраиваемые строки.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите диапазон, в котором вы будете генерировать случайные строки, а затем щелкните Кутулс > Вставить > Вставить случайные данные. Смотрите скриншот:
3. В Вставить случайные данные диалоговое окно, нажмите строка вкладку и выберите нужный тип символов, затем укажите длину строки в Длина строки поле и, наконец, щелкните OK кнопка. Смотрите скриншот:
Затем выбранный диапазон был заполнен случайными строками символов.
Функции: если вы хотите сгенерировать или вставить указанные строки данных форматирования (например, . @. . com) в диапазон, вы также можете использовать эту утилиту. Сделайте так:
1. Выберите диапазон, укажите символы и установите флажок По маске. Затем введите указанные строки данных, которые вам нужны. Смотрите скриншот:
Внимание: С помощью ? для обозначения цифры случайного символа в конечных указанных строках форматирования.
2. Затем нажмите OK or Применить. Указанные строки данных форматирования были сгенерированы случайным образом следующим образом. Смотрите скриншот:
Kutools for Excel's Вставить случайные данные может помочь вам легко выполнить следующие операции в ячейках диапазона.
- Сгенерировать или вставить случайные числа в диапазон
- Сгенерировать или вставить случайную дату в диапазон
- Создание или вставка настраиваемого списка в диапазон
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Office Tab - Tabbed Browsing, Editing, and Managing of Workbooks in Excel:
Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:
Читайте также: