Случайные (рандомные) числа в таблицах «excel». генератор случайных чисел excel в функциях и анализе данных

Случайная выборка

Нечастая, но интересная задача: выбрать из массива данных (списка) случайным образом N элементов. Причин для ее возникновения может быть несколько, например:

Объем данных слишком велик, поэтому мы удовлетворяемся анализом случайной выборки из полного набора данных.
Выбор победителей из числа участников какого-либо конкурса или лотереи.

В любом случае перед нами стоит задача отобрать случайным образом заданное количество элементов из какого-либо набора (например, вот такого):

Способ 1. Случайная сортировка

Добавить к нашему списку еще один столбец и вставить в него функцию генерации случайных чисел СЛЧИС (RAND). Затем отсортировать наш список по добавленному столбцу (Данные — Сортировка) и взять N первых элементов из получившейся таблицы:

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

Способ 2. Функция НАИМЕНЬШИЙ

Этот способ заключается в использовании функции НАИМЕНЬШИЙ (SMALL) для выбора из списка N позиций с наименьшим случайным числом в столбце А:

После выбора пяти (в нашем примере) наименьших случайных чисел из столбца А, мы вытаскиваем имена, которые соответствуют этим числам с помощью функции ВПР (VLOOKUP).

Способ 3. Случайная выборка без повторов — функция Lotto на VBA

Можно создать простую функцию на VBA, которая будет выдавать заданное количество случайных чисел из нужного интервала. Откроем редактор Visual Basic (ALT+F11 или в старых версиях Excel через меню Сервис — Макрос — Редактор Visual Basic), вставим новый модуль через меню Insert — Module и скопируем туда текст вот такой функции:

Function Lotto(Bottom As Integer, Top As Integer, Amount As Integer) Dim iArr As Variant Dim i As Integer Dim r As Integer Dim temp As Integer Dim Out(1000) As Variant

ReDim iArr(Bottom To Top) For i = Bottom To Top iArr(i) = i Next i

For i = Top To Bottom + 1 Step -1 r = Int(Rnd() * (i — Bottom + 1)) + Bottom temp = iArr(r) iArr(r) = iArr(i) iArr(i) = temp Next i j = 0 For i = Bottom To Bottom + Amount — 1 Out(j) = iArr(i) j = j + 1 Next i

У этой функции будет три аргумента:

Bottom — нижняя граница интервала случайных чисел
Top — верхняя граница интервала случайных чисел
Amount — количество случайных чисел, которое мы хотим отобрать из интервала

Т.е., например, чтобы отобрать 5 случайных чисел от 10 до 100, нужно будет ввести =Lotto(10;100;5)

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

Обратите внимание, что наша функция Lotto должна быть введена как формула массива, т.е. сначала необходимо выделить диапазон ячеек результатов (D2:D6) затем ввести нашу функцио Lotto и, после ввода аргументов функции, нажать Ctrl+Shift+Enter, чтобы ввести эту функцию именно как функцию массива во все выделенные ячейки

Ну, а дальше останется при помощи уже знакомой функции ВПР (VLOOKUP) вытащить имена из списка, соответствующие случайным номерам.

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

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

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

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

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

Вот так все просто. Успехов.

шаг

Часть 1 из 3: Ввод данных в Excel

Откройте Microsoft Excel. Вы также можете открыть документ Microsoft Excel, содержащий нужные вам данные.
Выберите Пустая книга. Если у вас нет открытого нового документа, пропустите этот шаг.
Введите свои данные. Для этого щелкните ячейку, которую вы хотите заполнить данными, затем введите свои данные.
- В зависимости от типа имеющихся данных процесс будет отличаться. Однако вы должны начать все данные в столбце «А».
- Например, вы можете написать имена опрошенных людей в столбце «А» и их ответы на опрос (например, «да» и «нет») в столбце «В».
Убедитесь, что все соответствующие данные указаны в ваших документах. После того, как все необходимые данные внесены в документы, можно приступать к составлению случайных выборок.

Часть 2 из 3: Создание случайных выборок

Щелкните правой кнопкой мыши имя крайнего левого столбца. Например, если все ваши данные начинаются с столбца «A», щелкните правой кнопкой мыши букву «A» над соответствующим столбцом.
Щелкните Вставить.

После этого все данные, которые были в столбце «A», теперь находятся в столбце «B» и так далее.

Слева от текущего столбца появится новый столбец.
Выберите новую ячейку «A1».
Введите «= RAND ()» без кавычек.

Если Excel автоматически форматирует вашу команду «RAND», удалите форматирование и введите команду еще раз.

Команда «RAND» применяется к числам от 0 до 1 в выбранных ячейках.
Нажмите ↵ Войти. Вы увидите десятичное число (например, 0,5647) в выбранной ячейке.
Выделите ячейки, содержащие случайные числа выборки.
Оставайтесь на линии контроль и нажмите С. Этот ярлык скопирует команду «RAND».
- Пользователи Mac, удерживайте ⌘ команда вместо контроль.
- Вы также можете щелкнуть правой кнопкой мыши ячейку «RAND» и выбрать «Копировать».
Выберите ячейку под номером случайной выборки.

Щелчок и выделение ячейки «A1» может вызвать ошибку (ошибка) в сортировке.

Если номер случайной выборки находится в «A1», выберите ячейку «A2».
Выделите оставшиеся ячейки случайной выборки. Уловка, держи это ⇧ сдвиг

Например, если данные находятся в столбцах «B» и «C» и продолжают опускаться до строки 100, удерживайте сдвиг и щелкните «A100», чтобы выбрать все ячейки «A» от A2 до A100.

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

Опять же, пользователям Mac нужно сдерживаться ⌘ команда вместо контроль.

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

Часть 3 из 3: Сортировка образцов

Выберите ячейку в самом верхнем левом углу.

Вам также нужно будет выделить примерные цифры слева от данных.

Обычно это ячейка «А1». Прежде чем сортировать образец, выделите все свои данные.
Оставайтесь на линии ⇧ сдвиг и выберите ячейку данных, которая находится в правом нижнем углу. Этот ярлык выделит все ваши данные, чтобы их можно было отсортировать.
- Например, если данные идут из ячейки «A1» в «C50», удерживайте ⇧ сдвиг и щелкните ячейку «C50».
- Вы также можете выделить данные, удерживая и перетаскивая курсор из верхнего левого угла в нижний правый угол ваших данных (или наоборот).
Щелкните правой кнопкой мыши свои данные.

Пользователи Mac: щелкните двумя пальцами (или удерживайте Ctrl и щелкните), чтобы открыть меню.

Появится меню с опциями для сортировки ваших данных.
Установите курсор на кнопку «Сортировка».
Щелкните Сортировать от наименьшего к наибольшему. Здесь вы можете нажать Сортировать от большего к меньшему. Ваши данные будут сброшены случайным образом на основе значения «= RAND ()» в столбце «A».
Просмотрите результаты сортировки. В зависимости от многих требуемых результатов процесс будет отличаться. Однако есть несколько способов сортировки данных:
- Выберите начало, конец или половину ваших данных, Если у вас слишком много данных для этого процесса, вы можете уменьшить часть проверяемых данных (например, первую восьмую ваших данных).
- Выберите четные или нечетные числа, Например, для данных, содержащих числа до 10, выберите числа 1, 3, 5, 7, 9 или 2, 4, 6, 8, 10.
- Выберите случайное число из данных, Этот метод лучше всего подходит для очень больших данных, которые слишком обременительны, чтобы использовать половину данных.
Выберите случайные образцы. Теперь вы получаете объективные выборки опросов, бонусных продуктов или аналогичных данных.

Проводим выборку

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

Решение №1: применяем расширенный автофильтр

Метод с применением автофильтра считается наиболее простым и доступным для понимания малоопытному пользователю. Рассмотрим процесс выполнения на отдельном примере:

Выделите таблицу, нуждающуюся в редактировании.
На вкладке «Главная» отыскиваем блок «Редактирование». В нем найдете «Сортировка и фильтр». Кликнув по нему, откроется список, в котором нужно активировать «Фильтр». Произойдет автоматическая установка выпадающего списка в шапке таблицы.

Можно включить выпадающий список и другим способом. Открыв вкладку «Данные», отыскав блок «Сортировка и фильтр», кликаем по «Фильтр».

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

Остановимся на суммах полученной прибыли произведем в них фильтрацию. При открытии пиктограммы и установке курсора на фильтры откроется следующее окно со списком, где выбираем «Настраиваемый фильтр».
В открывшемся окне пользовательской фильтрации задаем ограничение, по которому будет проводится отбор. Для выбора ограничений можно остановится на одном из следующих условий: равно; не равно; больше; больше или равно; меньше. К примеру, нам нужно, чтобы в таблице остались товары, по сумме превышающие 10000 рублей. Для этого прописываем в поле справа числовое значение «10000», а слева останавливаемся на значении «больше». Соглашаемся с выполненными действиями кликнув по кнопке «ОК».

После фильтрации увидим, что в таблице остались суммы, превышающие значение «10000».

Таким же образом проводится фильтрация в соседних столбцах. К примеру, нужно отфильтровать таблицу по дате. Откройте пользовательский автофильтр и установите в левой стороне значения – сверху (после и равно) и снизу (до или равно). В правой стороне пропишите даты. Устанавливаем фильтр в положение «И» и соглашаемся, нажав «ОК». В итоге конечный результат выглядит следующим образом:

Решение №2: используем формулы массива

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

Создаем аналогичную таблицу в правой стороне листа и прописываем в шапке те же значения.
Выделите пустые ячейки первого столбца в новой таблице. Активируйте поле ввода формулы и пропишите следующие значения: =ИНДЕКС(A2:A8;НАИМЕНЬШИЙ(ЕСЛИ(10000<=C2:C8;СТРОКА(C2:C8);””);СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1)).

Чтобы формула вступила в силу, необходимо запустить активацию сочетанием клавиш «Ctrl+Shift+Enter».

Выделяем второй столбец и вписываем другую формулу в поле ввода: =ИНДЕКС(B2:B8;НАИМЕНЬШИЙ(ЕСЛИ(10000<=C2:C8;СТРОКА(C2:C8);””);СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1)). Набираем комбинацию клавиш «Ctrl+Shift+Enter».
Выделяем третий столбец и записываем следующие значения формулы: =ИНДЕКС(C2:C8;НАИМЕНЬШИЙ(ЕСЛИ(10000<=C2:C8;СТРОКА(C2:C8);””);СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1)). Набираем сочетание клавиш «Ctrl+Shift+Enter» и активируем формулу.

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

Чтобы исправить ошибку, выделите весь столбец «Дата» и кликните ПКМ. В открывшемся списке выберите «Формат ячеек…». В следующем окне остаемся на вкладке «Число» и находим формат «Дата». Переходим по данной ссылке и выбираем правильное значение для даты. В нашем случае это верхний вариант. Жмем кнопку «ОК».

Выделите все ячейки новой таблицы, не затрагивая шапки. Оставаясь во вкладке «Главная», жмем по «Условное форматирование». В списке находим «Создать правило…» и активируем нажатием ЛКМ.

В окне спускаемся ко второй строке, содержащей правило «Форматировать только ячейки, которые содержат». Ниже кликаем по пиктограмме и выбираем «Ошибки».

Жмем по кнопке «Формат…». Выбираем «Шрифт», устанавливаем белый цвет и соглашаемся кнопкой «ОК». Таким же образом кликаем по кнопке «ОК» в следующем открытом окне.

На данном этапе, выборка имеет законченный вид и выглядит надлежащим образом.

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

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

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

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

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

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

Делаем клик по ячейке, содержащей случайное число.
Делаем клик по строке формул, после чего выделяем ее.
Нажимаем на кнопку F9 на клавиатуре.
Заканчиваем эту последовательность действий нажатием клавиши Enter.

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

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

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

Если говорить про функцию СЛУЧМЕЖДУ, то согласно ее синтаксису, у нее есть два аргумента: нижняя граница и верхняя граница

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

Давайте посмотрим, как эта функция работает, на этом скриншоте.

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

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

Генератор случайных букв в Excel

Чтобы вставить случайную букву, необходимо использовать комбинацию трех различных функций:

Где A — первый символ, а Z — последний символ в диапазоне букв, которые вы хотите включить (в алфавитном порядке).

Разберем функции, в приведенной выше формуле:

КОДСИМВ возвращает числовые коды ANSI для указанных букв.
СЛУЧМЕЖДУ принимает числа, возвращаемые функциями КОДСИМВ , как нижнее и верхнее значения диапазона.
СИМВОЛ преобразует случайные коды ANSI, возвращаемые СЛУЧМЕЖДУ, в соответствующие буквы.

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

Так как коды ANSI отличаются для прописных и строчных букв, эта формула учитывает регистр.

Если кто-то наизусть знает Коды символов ANSI, ничто не мешает вам передавать коды непосредственно в функцию СЛУЧМЕЖДУ.

Например, чтобы получить произвольные прописные буквы между A (код ANSI 65) и Z (код ANSI 90), вы пишете:

Чтобы генерировать строчные буквы между а (код ANSI 97) в z (код ANSI 122), вы используете следующую формулу:

Чтобы вставить случайный специальный символ, например! «# $% & ‘() * +, -. /, используйте функцию СЛУЧМЕЖДУ с нижним параметром, установленным на 33 (код ANSI для«! »), а верхний параметр — 47 (код ANSI для« / »).

Генератор случайных чисел в Excel – Генерация случайных символов

Произвольное дискретное распределение

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

В поле Входной интервал значений и вероятностей необходимо ввести ссылку на двухстолбцовый диапазон (см. файл примера ).

Необходимо следить, чтобы сумма вероятностей модельного распределения была равна 1. Для этого в MS EXCEL имеется специальная функция ВЕРОЯТНОСТЬ() .

СОВЕТ: О генерации чисел, имеющих произвольное дискретное распределение, см. статью Генерация дискретного случайного числа с произвольной функцией распределения в MS EXCEL. В этой статье также рассмотрена функция ВЕРОЯТНОСТЬ() .

Доброго времени суток, уважаемый, читатель!

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

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

во-первых: мы можем для тестировки формул, заполнить нужный нам диапазон случайными числами;
во-вторых: для формирования вопросов различных тестов;
в-третьих: для любого случайно распределения заранее пронумерованных задач между вашими сотрудниками;
в-четвёртых: для симуляции разнообразнейших процессов;

…… да и во многих других ситуациях!

В этой статье я рассмотрю только 3 варианта создания генератора (возможности макроса, я не буду описывать), а именно:

Создаем генератор случайных чисел в Excel

Доброго времени суток, уважаемый, читатель!

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

во-первых: мы можем для тестировки формул, заполнить нужный нам диапазон случайными числами;
во-вторых: для формирования вопросов различных тестов;
в-третьих: для любого случайно распределения заранее пронумерованных задач между вашими сотрудниками;
в-четвёртых: для симуляции разнообразнейших процессов;

…… да и во многих других ситуациях!

Создаём генератор случайных чисел с помощью функции СЛЧИС

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

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

=СЛЧИС()*100; А вот если вам не нравятся дробные числа или просто нужно использовать целые числа, тогда используйте такую комбинацию функций, это позволит вам отсечь значения после запятой или просто отбросить их:

=ОТБР((СЛЧИС()*100);0) Когда возникает необходимость использовать генератор случайных чисел в каком-то определённом, конкретном диапазоне, согласно нашим условиям, к примеру, от 1 до 6 надо использовать следующую конструкцию (обязательно закрепите ячейки с помощью абсолютных ссылок):

a – представляет нижнюю границу,
b – верхний предел

и полная формула будет выглядеть: =СЛЧИС()*(6-1)+1, а без дробных частей вам нужно написать: =ОТБР(СЛЧИС()*(6-1)+1;0)

Создаём генератор случайных чисел с помощью функции СЛУЧМЕЖДУ

Эта функция более проста и начала нас радовать в базовой комплектации Excel, после 2007 версии, что значительно облегчило работу с генератором, когда необходимо использовать диапазон. К примеру, для генерации случайного числа в диапазоне от 20 до 50 мы будем использовать конструкцию следующего вида:

Создаём генератор с помощью надстройки AnalysisToolPack

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

Для получения доступа к этой, бесспорно, полезной надстройки, нужно, для начала, с помощью диалогового окна «Надстройки» установить этот пакет. Если у вас он уже установлен, то дело за малым, выбираете пункт меню «Данные» – «Анализ» – «Анализ данных», выбираете «Генерация случайных чисел» в предложенном программой списке и жмём «ОК».

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

А на этом у меня всё! Я очень надеюсь, что вопрос по созданию генератора случайных чисел я раскрыл полностью и вам всё понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

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

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

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

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

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

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

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

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

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

Формулы массива в Excel

Терминология

Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов:

Формулы массива в Excel — это специальные формулы для обработки данных из таких массивов. Формулы массива делятся на две категории — те, что возвращают одно значение и те, что дают на выходе целый набор (массив) значений. Рассмотрим их на простых примерах.

Пример 1. Классика жанра — товарный чек

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

выделяем ячейку С7
вводим с клавиатуры =СУММ(
выделяем диапазон B2:B5
вводим знак умножения (звездочка)
выделяем диапазон C2:C5 и закрываем скобку функции СУММ — в итоге должно получиться так:

чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter

Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

Обратите внимание на фигурные скобки, появившиеся в формуле — отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно — они автоматически появляются при нажатии Ctrl + Shift + Enter

Пример 2. Разрешите Вас. транспонировать?

При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.

Допустим, имеем двумерный массив ячеек, который хотим транспонировать.

Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
вводим функцию транспонирования =ТРАНСП(
в качестве аргумента функции выделяем наш массив ячеек A1:B8

жмем Ctrl + Shift + Enter и получаем «перевернутый массив» в качестве результата:

Редактирование формулы массива

Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.

Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)

Пример 3. Таблица умножения

Вспомните детство, школу, свою тетрадку по математике. На обороте тетради на обложке было что? Таблица умножения вот такого вида:

При помощи формул массива она вся делается в одно движение:

выделяем диапазон B2:K11
вводим формулу =A2:A11*B1:K1
жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива

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

Пример 4. Выборочное суммирование

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

В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

Как сделать выборку в Excel с помощью формул массива

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

Как сделать выборку в Excel по условию

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

Сначала научимся делать выборку по одному числовому критерию. Задача – выбрать из таблицы товары с ценой выше 200 рублей. Один из способов решения – применение фильтрации. В результате в исходной таблице останутся только те товары, которые удовлетворяют запросу.

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

Сначала создаем пустую таблицу рядом с исходной: дублируем заголовки, количество строк и столбцов. Новая таблица занимает диапазон Е1:G10.Теперь выделяем Е2:Е10 (столбец «Дата») и вводим следующую формулу: .

Чтобы получилась формула массива, нажимаем сочетание клавиш Ctrl + Shift + Enter. В соседний столбец – «Товар» — вводим аналогичную формулу массива: . Изменился только первый аргумент функции ИНДЕКС.

В столбец «Цена» введем такую же формулу массива, изменив первый аргумент функции ИНДЕКС.

В результате получаем отчет по товарам с ценой больше 200 рублей.

Такая выборка является динамичной: при изменении запроса или появлении в исходной таблице новых товаров, автоматически поменяется отчет.

Задача №2 – выбрать из исходной таблицы товары, которые поступили в продажу 20.09.2015. То есть критерий отбора – дата. Для удобства искомую дату введем в отдельную ячейку, I2.

Для решения задачи используется аналогичная формула массива. Только вместо критерия >.

Подобные формулы вводятся и в другие столбцы (принцип см. выше).

Теперь используем текстовый критерий. Вместо даты в ячейку I2 введем текст «Товар 1». Немного изменим формулу массива: .

Такая большая функция выборки в Excel.

Выборка по нескольким условиям в Excel

Сначала возьмем два числовых критерия:

Задача – отобрать товары, которые стоят меньше 400 и больше 200 рублей. Объединим условия знаком «*». Формула массива выглядит следующим образом: >.

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

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

Случайная выборка в Excel

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

Исходный набор данных:

Сначала вставим слева два пустых столбца. В ячейку А2 впишем формулу СЛЧИС (). Размножим ее на весь столбец:

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

Чтобы вставились значения, а не формула, щелкаем правой кнопкой мыши по столбцу В и выбираем инструмент «Специальная вставка». В открывшемся окне ставим галочку напротив пункта «Значения»:

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