0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Быстро создавать случайные группы для списка данных в Excel

Быстро создавать случайные группы для списка данных в Excel

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

Ваш браузер не поддерживает видео.

Введите данные для выпадающего списка на новом листе. Предпочтительно, чтобы элементы списка хранились в электронной таблице Excel. Список можно быстро преобразовать в таблицу, выделив любую ячейку в диапазоне и нажав CTRL+T.

Таблица Excel, используемая в качестве источника для списка проверки данных

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

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

Выберите вкладку Данные на ленте, а затем щелкните Проверить данные.

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

В разделе Тип данных выберите Список на вкладке Опции.

Вы можете выделить диапазон списка, щелкнув поле Источник. В примере данные находятся на листе Города в диапазоне от A2 до A9. Поскольку строка заголовка не входит в число доступных для выбора вариантов, она отсутствует в диапазоне.

Параметры списка проверки данных

Если вы можете оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.

Установите флажок «Разрешить список

Откройте вкладку Сообщение для ввода данных.

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

Вкладка "Сообщение для ввода" в диалоговом окне "Проверка данных"

Откройте вкладку «Сообщение об ошибке».

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

Читайте так же:
Как быстро выделить активную ячейку перекрестием в Excel?

Параметры сообщения об ошибке для раскрывающегося списка проверки данных

Вы не представляете, что выбрать в поле «Вид»?

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

Выберите опцию Stop, чтобы запретить пользователям вводить данные, которых нет в раскрывающемся списке.

Примечание: Если вы не добавили заголовок и текст, по умолчанию будет отображаться заголовок «Microsoft Excel» и сообщение «Введенное значение недействительно». Набор значений, которые можно ввести в ячейку, ограничен».

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

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

Если вы решили изменить элементы выпадающего списка, см. статью Добавление и удаление элементов раскрывающегося списка.

Удалите раскрывающийся список, как описано в статье Удаление раскрывающегося списка

Мы предлагаем вам загрузить набор образцов, содержащий несколько примеров проверки данных, аналогичных примеру, представленному в этой статье. Вы можете использовать их или создать собственные сценарии проверки данных. Скачать примеры проверки данных в Excel.

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

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

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

Читайте так же:
Как быстро посчитать только первый экземпляр значений в Excel?

Отметьте ячейки, в которых необходимо ограничить ввод данных.

В группе Инструменты на вкладке Данные нажмите Проверить или верифицировать данные.

Меню "Данные" на панели инструментов Excel с выбранной кнопкой "Проверка данных"

Примечание: Если команда проверки недоступна, лист может быть защищен или рабочая книга находится в общем доступе. Если рабочая книга находится в общем доступе или лист защищен, вы не сможете изменить параметры управления данными. Дополнительные сведения о защите рабочей книги см. в статье Защита рабочих книг.

Выберите Список во всплывающем меню Разрешить на вкладке Параметры.

Щелкните по полю Источник и выберите список допустимых элементов на листе.

Диалоговое окно будет свернуто таким образом, что будет виден весь лист.

Нажмите клавишу ВВОД или кнопку РазвернутьКнопка "Развернуть" в диалоговом окне "Проверка данных", чтобы развернуть диалоговое окно, а затем нажмите кнопку ОК.

Значения также могут быть введены в поле «Источник» через запятую, разделенные запятыми.

Чтобы изменить список допустимых элементов, просто измените значения исходного списка или диапазона в поле Источник.

Вы можете указать собственное сообщение об ошибке, которое будет отображаться при вводе недопустимых данных. На вкладке Данные нажмите Проверить данные или Проверить, а затем перейдите на вкладку Сообщение об ошибке.

См. также

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

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

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

На ленте откройте вкладку Данные и нажмите Проверка данных.

На вкладке Параметры в поле Разрешить выберите Список.

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

Читайте так же:
Как в Excel объединить несколько столбцов в один список?

Фрукты; Овощи; Зерновые; Молочные продукты; Закуски

Если ячейку можно оставить пустой, установите флажок Игнорировать пустые ячейки.

Поставьте флажок в списке допустимых значений

Откройте вкладку «Сообщение» для ввода.

Установите флажок Показывать сообщения и введите заголовок и сообщение (до 225 символов) при выборе ячейки. Чтобы отключить сообщение, снимите флажок.

Откройте вкладку «Отчет об ошибках».

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

Нажмите кнопку ОК.

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

Дополнительные сведения

Задайте вопрос эксперту Excel Tech Community или опубликуйте вопрос в сообществе «Ответы».

Генератор случайных чисел Excel в функциях и анализе данных

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

Вы можете генерировать случайные числа в Excel различными способами и по-разному. Давайте рассмотрим только лучшие из них.

Функция случайного числа в Excel

  1. Функция SLCHIS возвращает равномерно распределенное случайное вещественное число. Оно должно быть меньше 1, больше или равно 0.
  2. Функция SLCHIS возвращает случайное целое число.

Рассмотрим их использование на примере.

Выборка случайных чисел с помощью СЛЧИС

Для этой функции (SLCHIS()) аргументы не требуются.

Для генерации случайного числа в диапазоне от 1 до 5 можно воспользоваться следующей формулой.

СЛЧИС.

Возвращенное случайное число равномерно распределено в интервале [1,10]

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

  1. Щелкните по ячейке со случайным числом.
  2. Выберите формулу в строке формул.
  3. Нажмите клавишу F9. И ВХОД.
Читайте так же:
Как автоматически пронумеровать строки, если соседняя ячейка не пуста в Excel?

Гистограмма распределения может быть использована для определения равномерности распределения исходной пробы.

  1. Сформируем «карманы». Диапазоны, в пределах которых будут находиться значения. Первый такой диапазон – 0-0,1. Для следующих – формула =C2+$C$2. Карманы.
  2. Определим частоту для случайных чисел в каждом диапазоне. Используем формулу массива <=ЧАСТОТА(A2:A201;C2:C11)>. ЧАСТОТА.
  3. Сформируем диапазоны с помощью знака «сцепления» (=»[0,0-«&C2&»]»). Диапазоны.
  4. Строим гистограмму распределения 200 значений, полученных с помощью функции СЛЧИС ().

Частота — это диапазон вертикальных значений. Горизонтальные — «карманы».

Функция СЛУЧМЕЖДУ

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

Пример применения функции

СЛУЧМЕЖДУ.

С точностью 0,1 и 0,01 случайные числа выглядят следующим образом:

Случайные числа.

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

Создайте генератор случайных чисел, который генерирует значение между двумя диапазонами. Например, можно написать такую формулу: =INDEX(A1:A10;Integer(SLCHIS()*10)+1).

ИНДЕКС.

Давайте создадим генератор случайных чисел с диапазоном от 0 до 100 и приращениями 10.

Генератор.

Список текстовых значений должен быть разбит на 2 случайных значения. SLCHIS сопоставляет текстовые значения в диапазоне A1:A7 со случайными числами.

Список.

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

ИНДЕКС.

Примените эту формулу для выбора случайного значения из списка: =INDEX(A1:A7;SLUCHMEAN(1;COUNT(A1:A7)))

Одно случайное значение.

Генератор случайных чисел нормального распределения

Функции SLCHIS и SLUGWISH производят случайные числа с равномерным распределением. Значение с одинаковой вероятностью может попасть в нижнюю или верхнюю границу диапазона. Результатом является огромная разница между заданным и фактическим значением.

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

Стоимость продукта X составляет 100 рублей. Вся произведенная партия подчиняется нормальному распределению вероятности. Кроме того, случайная величина подчиняется нормальному распределению.

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

Используем функцию: =НОРМОБР(СЛЧИС();100;1,5).

НОРМОБР.

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

Далее следует построение графика. Первым нашим шагом должно стать создание таблицы с категориями. Это можно сделать, разделив массив на периоды:

  1. Определим минимальное и максимальное значение в диапазоне с помощью функций МИН и МАКС.
  2. Укажем величину каждого периода либо шаг. В нашем примере – 1.
  3. Количество категорий – 10. Таблица.
  4. Нижняя граница таблицы с категориями – округленное вниз ближайшее кратное число. В ячейку Н1 вводим формулу =ОКРВНИЗ(E1;E5).
  5. В ячейке Н2 и последующих формула будет выглядеть следующим образом: =ЕСЛИ(G2;H1+$E$5;»»). То есть каждое последующее значение будет увеличено на величину шага. ЕСЛИ.
  6. Посчитаем количество переменных в заданном промежутке. Используем функцию ЧАСТОТА. Формула будет выглядеть так:

На основе собранных данных мы сможем построить график с нормальным распределением. Ось значений — количество переменных в интервале, ось категорий — периоды.

График.

Вот график с нормальным распределением. Как колокол, он должен иметь такую форму.

Вы можете сделать то же самое гораздо более простым способом. Используйте пакет «Анализ данных». Выберите «Генерация случайных чисел».

Анализ данных.

О том, как подключить стандартную настройку «Анализ данных», можно прочитать здесь.

Заполнение параметров для генерации. Распределение является «нормальным».

Параметры.

Нажмите OK. Мы получаем набор случайных чисел. Снова вызываем инструмент «Анализ данных». Выбираем «Гистограмма». Настраиваем параметры. Установите флажок «Вывод графика».

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector