Формула в excel случмежду как сделать так чтобы перестали изменяться

Опубликовано автором

15K

08 января 2007 года

olen

11 / / 04.04.2006

Спасибо огромное — всё работает как надо.

Правда появилось пара ламерских вопросов:

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

2. Все значения в нескольких ячейках получаются одинаковыми 🙁 мне это не подходит — надо чтобы были разные.

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

Попробовал написать так —

Sub GetRandom()

Randomize

If ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«E19») > 5 Then

ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«F19»).Value = Rnd * 0.06 — 0.03

If ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«E19») < 5 Then

ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«F19»).Value = Rnd * 0.04 — 0.03

If ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«E20») > 5 Then

ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«F20»).Value = Rnd * 0.06 — 0.03

If ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«E20») < 5 Then

ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«F20»).Value = Rnd * 0.04 — 0.03

If ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«E21») > 5 Then

ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«F21»).Value = Rnd * 0.06 — 0.03

If ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«E21») < 5 Then

ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«F21»).Value = Rnd * 0.04 — 0.03

If ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«E22») > 5 Then

ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«F22»).Value = Rnd * 0.06 — 0.03

If ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«E22») < 5 Then

ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«F22»).Value = Rnd * 0.04 — 0.03

End If

End Sub

Но ВБ при этом ругается 🙁

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

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

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

  1. Функция СЛЧИС возвращает случайное равномерно распределенное вещественное число. Оно будет меньше 1, больше или равно 0.
  2. Функция СЛУЧМЕЖДУ возвращает случайное целое число.

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

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

Данная функция аргументов не требует (СЛЧИС()).

Чтобы сгенерировать случайное вещественное число в диапазоне от 1 до 5, например, применяем следующую формулу: =СЛЧИС()*(5-1)+1.

Возвращаемое случайное число распределено равномерно на интервале .

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

  1. Щелкаем по ячейке со случайным числом.
  2. В строке формул выделяем формулу.
  3. Нажимаем F9. И ВВОД.

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

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

Диапазон вертикальных значений – частота. Горизонтальных – «карманы».

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

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

Пример использования функции:

Случайные числа с точностью 0,1 и 0,01:

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

Сделаем генератор случайных чисел с генерацией значения из определенного диапазона. Используем формулу вида: =ИНДЕКС(A1:A10;ЦЕЛОЕ(СЛЧИС()*10)+1).

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

Из списка текстовых значений нужно выбрать 2 случайных. С помощью функции СЛЧИС сопоставим текстовые значения в диапазоне А1:А7 со случайными числами.

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

Чтобы выбрать одно случайное значение из списка, применим такую формулу: =ИНДЕКС(A1:A7;СЛУЧМЕЖДУ(1;СЧЁТЗ(A1:A7))).

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

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

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

Себестоимость товара Х – 100 рублей. Вся произведенная партия подчиняется нормальному распределению. Случайная переменная тоже подчиняется нормальному распределению вероятностей.

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

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

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

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

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

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

График с нормальным распределением готов. Как и должно быть, по форме он напоминает колокол.

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

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

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

Жмем ОК. Получаем набор случайных чисел. Снова вызываем инструмент «Анализ данных». Выбираем «Гистограмма». Настраиваем параметры. Обязательно ставим галочку «Вывод графика».

Получаем результат:

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

График с нормальным распределением в Excel построен.

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

Если речь идёт о генерации случайных чисел, Excel предлагает две полезные функции: RAND (СЛЧИС) и RANDBETWEEN (СЛУЧМЕЖДУ).

СЛЧИС

Функция RAND (СЛЧИС) генерирует случайное десятичное число между 0 и 1.

  1. Выделите ячейку A1.
  2. Введите следующую формулу и нажмите Enter.

    =RAND()
    =СЛЧИС()

Функция RAND (СЛЧИС) не принимает аргументов.

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

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

  2. Если вы не хотите этого, просто скопируйте случайные числа и вставьте их как значения.
  3. Выберите ячейку C1 и посмотрите на строку формул. Теперь в этой ячейке прописано значение, а не функция RAND (СЛЧИС).

СЛУЧМЕЖДУ

Функция RANDBETWEEN (СЛУЧМЕЖДУ) возвращает случайное целое число между двумя границами.

  1. Выделите ячейку A1.
  2. Введите формулу ниже и нажмите Enter.

    =RANDBETWEEN(50,75)
    =СЛУЧМЕЖДУ(50;75)

Если вы хотите сгенерировать ряд не целых чисел между 50 и 75, используйте следующую формулу:

=50+25*RAND()
=50+25*СЛЧИС()

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: /> Перевел: Антон Андронов

Правила перепечаткиЕще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

В разделе Другие языки и технологии на вопрос Как в Excel 2003 «слегка» изменить ряд чисел? (Сейчас поясню) заданный автором Невропатолог лучший ответ это так?
С2=СЛЧИС ()*((B2*1,014)-(B2*0,986))+(B2*0,986)

Ответ от

2 ответа

Привет! Вот подборка тем с ответами на Ваш вопрос: Как в Excel 2003 «слегка» изменить ряд чисел? (Сейчас поясню)

Ответ от Ноябрь
Функция СЛУЧМЕЖДУ.. . Попробуй так — =C4*СЛУЧМЕЖДУ (0,002;0,005) — результирующая ячейка. Пусть буде С6, Ячейка С4 с содежимым 0,002 и 0,005 — нижняя и верхняя граница случайного числа. У тебя будет 0,004 и 0,009

Ответ от силосовать
Попробуйте =(ячейка*(100-(0,7-(СЛЧИС ()))))/100 например: =(A1*(100-(0,7-(СЛЧИС ()))))/100 P.S.: Проверил, работает. Для изменения диапазона чисел результата, просто измените число «0,7». «СЛЧИС ()» дает случайное число от 0 до 1

Ответ от ?????? ????r?????u
вы там не данные от скважин обрабатываете?

Ответ от

2 ответа

Привет! Вот еще темы с нужными ответами:

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

Если речь идёт о генерации случайных чисел, Excel предлагает две полезные функции: RAND (СЛЧИС) и RANDBETWEEN (СЛУЧМЕЖДУ).

СЛЧИС

Функция RAND (СЛЧИС) генерирует случайное десятичное число между 0 и 1.

  1. Выделите ячейку A1.
  2. Введите следующую формулу и нажмите Enter.

    =RAND()
    =СЛЧИС()

Функция RAND (СЛЧИС) не принимает аргументов.

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

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

  2. Если вы не хотите этого, просто скопируйте случайные числа и вставьте их как значения.
  3. Выберите ячейку C1 и посмотрите на строку формул. Теперь в этой ячейке прописано значение, а не функция RAND (СЛЧИС).

СЛУЧМЕЖДУ

Функция RANDBETWEEN (СЛУЧМЕЖДУ) возвращает случайное целое число между двумя границами.

  1. Выделите ячейку A1.
  2. Введите формулу ниже и нажмите Enter.

    =RANDBETWEEN(50,75)
    =СЛУЧМЕЖДУ(50;75)

Если вы хотите сгенерировать ряд не целых чисел между 50 и 75, используйте следующую формулу:

=50+25*RAND()
=50+25*СЛЧИС()

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: /> Перевел: Антон Андронов

Правила перепечаткиЕще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

Похожие записи:

Округление чисел в excel как сделать Как ячейки в excel сделать невидимыми ячейки? Как формулу в excel сделать числом? Как сделать ячейку в excel?