Как генерировать текст в эксель
Нечастая, но интересная задача: выбрать из массива данных (списка) случайным образом 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) вытащить имена из списка, соответствующие случайным номерам.
В далёких от IT проектных организациях Excel часто используется в качестве инструмента обработки бог весть каких данных.
Передо мной встала задача в очень сжатые сроки наладить в Excel мониторинг весьма специфичного проекта с попутной выдачей заданий в проектные отделы. Касательно мониторинга ТЗ было более-менее определено и весь функционал был реализован средствами VBA самого Excel.
С выдачей заданий всё было не так однозначно. Поскрипев креслом, я решил попробовать отправлять задачи в Redmine прямо из Excel, получая обратно гиперссылку и номер задачи.
Статья написана инженером-проектировщиком для таких же новичков в программировании, автоматизирующих подручными средствами рутинную работу. Буду рад любым замечаниям!
Зачем это вообще?
Так получилось, что Excel в отрасли проектирования де-факто такой же стандарт при обмене между сотрудничающими организациями всевозможными списками документации и спецификациями, как и AutoCAD для чертежей.
Раньше я думал, что так только в нашей организации, а продвинутые конторы используют умные инструменты и базы, исключающие дублирование и нарушения целостности в данных. Но на деле оказалось, что даже большие компании на многомиллиардных проектах используют Excel в режиме «туда-сюда» при обмене данными и технической документацией с контрагентами.
В таких проектах одновременно в разработке могут быть сотни комплектов технической документации. К нам, например, регулярно присылают документы с прилагаемой экселевской таблицей на
4500 строк, где каждая строка определяет текущий статус конкретного документа. При достижении определенного статуса каждый документ должен быть запущен в работу: переведён, согласован, адаптирован под ГОСТ и т.д.
Такие однотипные задачи удобно запускать в Redmine прямо из Excel, формируя их в соответствии с данными из таблицы.
Ко всему выше сказанному, какой-никакой опыт написания костылей на vba у меня уже был, и примерный путь решения проблемы в голове уже крутился.
Я не буду подробно останавливаться на установке и настройке самого Redmine. Те, кто ещё не сталкивался с этой системой управления проектами, могут попробовать demo, или скачать и запустить стак от bitnami. Документацию по работе с виртуальной машиной Bitnami Redmine можно посмотреть тут.
Источник информации по API — Redmine API.
Простой пример создания задачи
Итак, заходим в редактор vba в Excel, создаем новый модуль и вписываем в него следующее:
Примечание: для авторизации в Redmine можно использовать логин и пароль, или ключ API, который можно посмотреть в профиле. В примере выше используется логин-пароль, строки с ключом закомментированы.
Всё хорошо, но как быть с параметрами, которые мы можем узнать только из базы данных Redmine? Я имею в виду ID для проекта, трекера, и на кого назначена задача.
В моем случае все эти параметры связаны с данными из таблицы (проект, его статус, ответственный отдел), поэтому я поступил просто, хоть и неправильно с точки зрения универсальности. Я подсмотрел ID в базе Redmine через phpMyAdmin и создал на отдельном листе табличку настроек, где прописал эти ID для нужных мне параметров из основной таблицы. Как результат, пользователю нужно только выбрать нужную строку в таблице и нажать кнопку отправки задачи, никаких дополнительных диалоговых окон.
Я немного упростил реальную логику для наглядности, но смысл тот же — берем значение из текущей строки и сравниваем по таблице с ID.
Как видите, в моем случае вписывать ID совсем немного: проект с несколькими подпроектами, четыре трекера, и чуть больше дюжины ответственных руководителей отделов, на которых изначально должна назначаться задача.
Таким образом, вместо:
Я вписал как-то так:
Парсинг xml
Более правильный и сложный подход — считывать нужные данные прямо из Redmine. Тут нам снова поможет API.
Нам понадобятся следующие функции:
XMLtoArray — функция парсит xml начиная с заданного узла. Требует подключения дополнительной библиотеки Microsoft XML, поэтому если кто знает, как сделать это проще, подскажите, пожалуйста.
Подключение библиотеки Microsoft XML через Tools --> Reference
SWAP — функция перестановки столбцов в двумерном массиве (взял отсюда).
Теперь можно делать запросы в Redmine:
На выходе получаем два массива — массив со всеми полученными данными, и урезанный SWAP массив только с нужными нам столбцами, в данном случае, с именами проектов, которые можно выводить в форму для пользователя.
Аналогичным образом получаем:
где Arr_projects(i, 0) — ID конкретного проекта.
где Arr_projects(i, 0) — ID конкретного проекта.
Этих данных вполне достаточно для реализации функционала создания задачи, например, при помощи такой формы:
Эта форма делалась под Word (сути не меняет, так как код vba тот же и в Excel) для другого проекта, с параллельной генерацией задания на печать и подпись для наших бюрократов. Но это, как говорится, уже другая история.
Например, у вас есть список имен, и теперь вам нужно случайным образом выбрать несколько имен и заполнить заданные ячейки, как с этим бороться? Здесь я расскажу о нескольких методах случайного заполнения значений из списка данных в Excel.
Произвольно заполнять значения из списка данных функциями
Чтобы случайным образом заполнить значения из заданного списка данных в Excel, мы можем применить функцию RANDBETWEEN и функцию VLOOKUP, чтобы сделать это следующим образом:
Шаг 1: Щелкните правой кнопкой мыши столбец данного списка и выберите Вставить из контекстного меню.
Шаг 2: Во вставленном столбце введите NO. в качестве заголовка столбца, а затем введите порядковые номера в следующие ячейки, как показано на скриншоте выше:
Шаг 3: Случайным образом заполнять указанные ячейки из данного списка:
(1) В пустую ячейку введите формулу = СЛУЧМЕЖДУ (1,15) , и перетащите маркер заполнения в нужный диапазон.
(2) В ячейку, в которую вы будете вводить имя случайным образом из данного списка, введите формулу = ВПР (C2; $ A $ 1: $ B $ 16,2; FALSE) , и перетащите маркер заполнения в нужный диапазон.
Ноты:
(1) В формуле = СЛУЧМЕЖДУ (1,15) , 1 - наименьший порядковый номер, а 15 - наибольший.
(2) В формуле = ВПР (C2; $ A $ 1: $ B $ 16,2; FALSE) , C2 - это случайный порядковый номер, который вы получили только что, $ A $ 1: $ B $ 16 - это диапазон заданного списка и вставленных порядковых номеров, 2 означает второй столбец в диапазоне $ A $ 1: $ B $ 16.
 | Формула слишком сложна для запоминания? Сохраните формулу как запись Auto Text для повторного использования одним щелчком мыши в будущем! Подробнее . Бесплатная пробная версия |
Функции RANDBETWEEN и VLOOKUP могут легко помочь вам в случайном заполнении имен из данного списка в Excel. Однако этот метод оставляет две проблемы:
- Случайные имена меняются после обновления текущего рабочего листа, например, ввод новой формулы в рабочий лист или просто двойной щелчок в ячейке, а затем выход и т. Д.
- Повторяющиеся имена иногда заполняются в указанных ячейках.
Случайно заполняйте значения из списка данных с помощью Kutools for Excel
Чтобы случайным образом заполнить имена из данного списка без дубликатов и сохранить все заполненные имена статичными, вы можете попробовать Kutools for Excel's Вставить случайные данные утилита, чтобы сделать это с легкостью.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Шаг 1: Нажмите Kutools > Вставить > Вставить случайные данные.
Шаг 2: В открывшемся диалоговом окне Вставка случайных данных перейдите к Пользовательский список , нажмите Добавить кнопка; затем в открывшемся диалоговом окне Create Sequence щелкните значок кнопка; в новом диалоговом окне укажите диапазон заданного списка имен; следующий щелчок OK > Ok.
Шаг 3: Теперь указанный список имен добавлен как настраиваемый список в диалоговом окне «Вставить случайные данные». Выберите диапазон, в который вы будете случайным образом заполнять имена из данного списка, нажмите кнопку Ok или Применить кнопку.
Внимание: Для случайного заполнения имен из данного списка без дубликатов, пожалуйста, проверьте Уникальные ценности в диалоговом окне «Вставить случайные данные». См. Снимок экрана выше.
Иногда может потребоваться сгенерировать случайные строки в ячейках, например разные пароли. В этой статье мы пытаемся показать вам некоторые приемы создания различных случайных строк в 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. Выберите диапазон, в котором вы будете генерировать случайные строки, а затем щелкните Kutools > Вставить > Вставить случайные данные. Смотрите скриншот:
3. В Вставить случайные данные диалоговое окно, нажмите строка вкладку и выберите нужный тип символов, затем укажите длину строки в Длина строки поле и, наконец, щелкните OK кнопка. Смотрите скриншот:
Затем выбранный диапазон был заполнен случайными строками символов.
Чаевые: если вы хотите сгенерировать или вставить указанные строки данных форматирования (например, . @. . com) в диапазон, вы также можете использовать эту утилиту. Сделайте так:
1. Выберите диапазон, укажите символы и установите флажок По маске. Затем введите указанные строки данных, которые вам нужны. Смотрите скриншот:
Внимание: С помощью ? для обозначения цифры случайного символа в конечных указанных строках форматирования.
2. Затем нажмите OK or Применить. Указанные строки данных форматирования были сгенерированы случайным образом следующим образом. Смотрите скриншот:
Kutools for Excel's Вставить случайные данные может помочь вам легко выполнить следующие операции в ячейках диапазона.
- Сгенерировать или вставить случайные числа в диапазон
- Сгенерировать или вставить случайную дату в диапазон
- Создание или вставка настраиваемого списка в диапазон
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Читайте также: