Как в экселе выбрать случайную ячейку


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

Нечастая, но интересная задача: выбрать из массива данных (списка) случайным образом 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
 
 Application.Volatile
 
 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
 
 Lotto = Application.Transpose(Out)
 
 End Function
 

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

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

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

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

 

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

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

Ссылки по теме

 

www.planetaexcel.ru

Как случайным образом выбрать ячейку и соседнюю ячейку на excel

Я пытаюсь найти формулу на excel, которая позволит мне случайным образом выбрать ячейку между диапазоном (эта часть, для которой я нашел формулу) и вставить значение в другую ячейку AND, а затем выбрать ячейку рядом со значением случайно выбранной ячейки и вставить ее в другую ячейку.

Не уверен, что это имеет смысл, поэтому я объясню, что я пытаюсь сделать.

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

Заранее благодарю вас!

Анна

excel formula Поделиться Источник annadavis23     22 декабря 2016 в 12:44

1 Ответ



1

Скажем, данные находятся в диапазоне от A1 до B10

На D1 введите:

=INDEX(A1:A10,RANDBETWEEN(1,10)) 

а в Е1 введите:

=VLOOKUP(D1,A1:B10,2,FALSE) 

Другой подход заключается в том, чтобы войти:

=RANDBETWEEN(1,10) =INDIRECT("A" & C1) =INDIRECT("B" & C1) 

В C1 через E1

(Любой подход будет работать, если столбец A содержит годы, а не имена)

Еще один подход заключается в том, чтобы войти:

=RANDBETWEEN(1,10) =INDEX(A1:A10,C1) =INDEX(B1:B10,C1) 

В C1 через E1

Поделиться Gary's Student     22 декабря 2016 в 13:03


Похожие вопросы:


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

Мне было интересно, если кто-то знает, как выбрать ячейку столбца из нескольких столбцов в Excel листа? образец : Б C 0 1 2 Я хочу, чтобы число выбиралось случайным образом между (0-2) Спасибо


Как разделить часть ячейки excel на другую ячейку?

Есть список ячеек, который выглядит как [a-z ]* [1-9.]* . Я хочу разделить числовую часть на соседнюю ячейку. Как я могу это сделать?


VBA Excel макрос найти текст и найти следующую соседнюю ячейку

Я пытаюсь написать макрос VBA Excel, чтобы создать алгоритм, который будет находить текст и находить следующую соседнюю ячейку для отображения текста в msgbox, например: Я разобрал предложение The...


Excel нужно сопоставить и вставить в соседнюю ячейку на листе 1

Итак, на листе 2 у меня есть набор адресов IP в столбце A, которые соответствуют координатам в столбце B. На листе 1 у меня есть все эти адреса IP, разбросанные вокруг. Мне нужна формула на листе 1,...


Если ячейка содержит "name", то измените соседнюю ячейку на " number"

В моей книге excel столбец C содержит список имен, а столбец E содержит список телефонных номеров. Имена вставляются в каждую ночь из другой книги, и телефоны вводятся вручную, чтобы соответствовать...


Как сфокусировать ячейку в Excel VSTO с помощью C#? Как выбрать первую ячейку с помощью C# в VSTO?

Как сфокусировать ячейку в Excel VSTO с помощью C#? Как выбрать первую ячейку, используя C# в VSTO? Пожалуйста, помогите с кодом...


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

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


Вытяните соседнюю ячейку таблицы, используя BeautifulSoup Python

table = plan1.find('table', id = 'planComparison') pcp = table.findChildren(text=' Doctor Visit - Primary Care ') Я использовал приведенный выше код, чтобы установить переменную pcp в ячейку,...


Как я могу выбрать соседнюю ячейку таблицы, используя jQuery

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


Скопируйте ячейку и соседнюю ячейку и вставьте как новую строку: Excel VBA

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


coderoad.ru

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

Инструмент Пакета анализа MS EXCEL «Выборка» извлекает случайную выборку из входного диапазона, рассматривая его как генеральную совокупность. Также случайную выборку можно извлечь с помощью формул.

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

Данную выборку можно получить с помощью инструмента «Выборка» надстройки Пакет анализа ( Analysis ToolPak ).

Надстройка доступна из вкладки Данные , группа Анализ .

СОВЕТ : Подробнее о других инструментах надстройки Пакет анализа и ее подключении – читайте в статье Надстройка Пакет анализа MS EXCEL .

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

Разместим исходную генеральную совокупность в диапазоне A7:A32 (см. файл примера лист Пример ).

Для наглядности все значения совокупности сделаны последовательными числами .

Вызовем надстройку Пакет анализа , выберем инструмент Выборка. Нажмем ОК.

В появившемся диалоговом окне в поле Входной интервал введите ссылку на A 7: A 32 .

Если диапазон включает и заголовок, то нужно установить галочку в поле Метки . В нашем случае устанавливать галочку не требуется, т.к. заголовок столбца не входит в диапазон A 7: A 32 .

Метод выборки установите Случайный , в поле Число выборок введите 6. Таким образом, из совокупности будет выбрано 6 чисел в случайном порядке (возможны повторы).

В поле Выходной интервал достаточно ввести ссылку на верхнюю ячейку диапазона с результатами (укажем ячейку С7 ). Нажмем ОК.

В результате работы надстройки, MS EXCEL разместил в столбце D 6 значений выбранных случайных образом из диапазона A 7: A 32 .

В принципе, количество случайных значений можно установить даже больше чем значений в исходной совокупности (безусловно, при этом будут повторы).

Аналогичную случайную выборку можно осуществить с помощью формулы =ИНДЕКС($A$7:$A$32;СЛУЧМЕЖДУ(1;СЧЁТ($A$7:$A$32)))

Функция СЛУЧМЕЖДУ() случайным образом выбирает позицию совокупности, из которой нужно взять 1 значение ( для этой функции вероятность выбрать любую строку одинакова ).

Выборка с определенной периодичностью


С помощью инструмента «Выборка» можно осуществить выборку с определенной заданной периодичностью. В этом случае пользователь должен сам убедиться, что данная выборка будет репрезентативной (как минимум, исходная совокупность не должна быть отсортирована ).

Установив в поле Период , например, значение 3, выведем каждое третье значение из Входного интервала .

Этот же результат можно получить формулой (см. файл примера лист Пример ): =ИНДЕКС($A$7:$A$32;$H$6*(СТРОКА()-СТРОКА($G$6)))

В ячейке H6 содержится период выборки .

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

Для выборки из всех кварталов кроме 4-го придется включить в исходную совокупность пустые ячейки. Например, для 3-го квартала нужно ввести в поле Входной интервал ссылку на A 6: A 32 , причем А6 должна быть пустой ячейкой (или содержать фиктивное число).

Гораздо проще для этого использовать формулу (см. файл примера лист Квартал ): =ИНДЕКС($C$9:$C$32;4*(СТРОКА()-СТРОКА($F$8))-4+$F$6) или =СУММЕСЛИМН($C$9:$C$32;$A$9:$A$32;E9;$B$9:$B$32;$F$6&" кв.")

Примечание : Для наглядности строки, относящиеся к нужному кварталу, выделены Условным форматированием .

Выборка из нормального распределения

Пусть имеется генеральная совокупность из 5000 значений случайной величины имеющей стандартное нормальное распределение (см. файл примера лист Выборка из НОРМ ).

Примечание : Случайные значения сгенерированы с помощью формулы =НОРМ.СТ.ОБР(СЛЧИС())

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

Это удобнее сделать с помощью формулы =ИНДЕКС($A$7:$A$5006; СЛУЧМЕЖДУ(1;СЧЁТ($A$12:$A$5006)))

При каждый раз пересчете листа будет извлекаться новая выборка .

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

excel2.ru

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

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


Синтаксис функции

СЛУЧМЕЖДУ( нижняя_граница;верхняя_граница )

Нижн_граница — наименьшее целое число, которое возвращает функция.

Верхн_граница — наибольшее целое число, которое возвращает функция.

Если значение нижняя_граница больше значения верхняя_граница , функция вернет ошибку #ЧИСЛО! Предполагается, что границы диапазона – целые числа. Если введено число с дробной частью, то дробная часть будет отброшена.

Если необходимо получить случайное число, например, в интервале от 0 до 0,1, то нужно написать следующую формулу: =СЛУЧМЕЖДУ(0;10)/100 (с точностью 0,01, т.е. случайные значения будут 0,02; 0,05 и т.д.) =СЛУЧМЕЖДУ(0;1000)/10000 (с точностью 0,0001, т.е. случайные значения будут 0,0689; 0,0254 и т.д.)

Если необходимо получить не целое, а вещественное число, например, в интервале от 3 до 10, то нужно использовать функцию СЛЧИС() : =СЛЧИС()*(10-3)+3 (точностью 15 знаков, т.е. случайные значения будут 7,68866700270417; 8,68428856478223 и т.д.)

Если требуется сгенерировать случайное число от -2,699 до 30,02, причем оно должно быть округлено до тысячных (количество знаков после запятой случайного числа = максимальному количеству знаков после запятой границ интервала), то сначала нужно определить количество знаков после запятой у обоих границ (см. файл примера ). Это можно сделать с помощью формулы = ДЛСТР(A15)-1-ДЛСТР(ЦЕЛОЕ(A15))

Затем воспользоваться функцией СЛЧИС() , округлив значение.

Примечание . Границы интервала должны быть заданы константой. В случае расчетных значений интервалов результат непредсказуем (см. Проблемы округления в MS EXCEL ). Совет: Если границы интервала рассчитываются формулами, что в них можно задать необходимую точность.

Функция пересчитывает свое значение после каждого ввода нового значения в любую ячейку листа (или изменения значения ячейки) или нажатии клавиши F9 .

excel2.ru

СЛЧИС (функция СЛЧИС) - Служба поддержки Office

В этой статье описаны синтаксис формулы и использование функции СЛЧИС в Microsoft Excel.

Описание

Функция СЛЧИС возвращает равномерно распределенное случайное вещественное число, большее или равное 0, но меньшее 1. При каждом пересчете листа возвращается новое случайное вещественное число.

Синтаксис

СЛЧИС()

У функции СЛЧИС нет аргументов.

Замечания

=СЛЧИС()*(b–a)+a

  • Если требуется применить функцию СЛЧИС для генерации случайного числа, но изменение этого числа при каждом вычислении значения ячейки нежелательно, вы можете ввести в строке формулы =СЛЧИС(), а затем нажать клавишу F9, чтобы заменить формулу случайным числом. Формула будет вычислена и оставьте только значение.

Пример

Скопируйте образец данных из приведенной ниже таблицы и вставьте их в ячейку A1 на новом листе Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — ВВОД. При необходимости вы можете настроить ширину столбцов, чтобы просмотреть все данные.

Формула

Описание

Результат

=СЛЧИС()

Случайное число, большее или равное 0 и меньшее 1

меняется

=СЛЧИС()*100

Случайное число, большее или равное 0 и меньшее 100

меняется

=ЦЕЛОЕ(СЛЧИС()*100)

Произвольное целое число, большее или равное 0 и меньшее 100

меняется

Примечание. При пересчете листа после ввода формулы или данных в другую ячейку или при выполнении пересчета вручную (по нажатию клавиши F9) каждая формула, в которой используется функция СЛЧИС, создает новое случайное число.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

См. также

Алгоритм начиная с

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

support.office.com

Выделение отдельных ячеек или диапазонов

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

Для выбора именованных и неименованных ячеек или диапазонов можно также использовать команду Перейти к (F5 или CTRL + G).

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

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

  • Чтобы выделить неименованную ссылку на ячейку или диапазон, введите ссылку на ячейку или диапазон ячеек, который нужно выделить, а затем нажмите клавишу Ввод. Например, введите B3, чтобы выделить эту ячейку, или введите B1: B3, чтобы выделить диапазон ячеек.

Примечание: Вы не можете удалить или изменить имена, определенные для ячеек или диапазонов в поле имя . Имена можно удалять и изменять только в диспетчере имен (вкладка "формулы ", Группа " определенные имена "). Дополнительные сведения см. в статье Определение и использование имен в формулах.

  1. Нажмите клавишу F5 или CTRL + G , чтобы открыть диалоговое окно "перейти ".

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

    Например, в поле ссылка введите B3 , чтобы выделить эту ячейку, или введите B1: B3 , чтобы выделить диапазон ячеек. Чтобы выделить несколько ячеек или диапазонов, введите их в поле ссылки , разделенные запятыми. Если вы ссылаетесь на диапазон с сбросом, созданный с помощью динамической формулы массива, вы можете добавить оператор Range. Например, если в ячейках A1: A4 есть массив, вы можете выбрать его, введя a1 # в поле ссылка , а затем нажать кнопку ОК.

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

  1. Перейдите в раздел формулы > определенные имена > Диспетчер имен.

  2. Выберите имя, которое вы хотите изменить или удалить.

  3. Выберите команду изменить или Удалить.

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

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

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

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

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

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

support.office.com

Случайные числа в Excel - Microsoft Excel для начинающих

Те, кто работает в продажах, в маркетинге или в любом другом направлении, которое использует

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

Уроки MS Excel

Условное форматирование в Excel позволяет выделять ячейки различными цветами в зависимости от их содержимого.

Уроки MS Excel

Если в Excel необходимо отобразить только записи, удовлетворяющие определённому критерию, то используйте фильтр. Для

Уроки MS Excel

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

Уроки MS Excel

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

office-guru.ru

Выпадающий список в Excel с помощью инструментов или макросов

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

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

Создание раскрывающегося списка

Путь: меню «Данные» - инструмент «Проверка данных» - вкладка «Параметры». Тип данных – «Список».

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

  1. Вручную через «точку-с-запятой» в поле «Источник».
  2. Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.
  3. Назначить имя для диапазона значений и в поле источник вписать это имя.

Любой из вариантов даст такой результат.



Выпадающий список в Excel с подстановкой данных

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

  1. Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу».
  2. Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья». То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона:
  3. Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

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

  1. Сформируем именованный диапазон. Путь: «Формулы» - «Диспетчер имен» - «Создать». Вводим уникальное название диапазона – ОК.
  2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
  3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
  4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
  5. Private Sub Worksheet_Change(ByVal Target As Range)   Dim lReply As Long   If Target.Cells.Count > 1 Then Exit Sub If Target.Address = "$C$2" Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range("Деревья"), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & _ Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Range("Деревья").Cells(Range("Деревья").Rows.Count + 1, 1) = Target End If End If End If End Sub  
  6. Сохраняем, установив тип файла «с поддержкой макросов».
  7. Переходим на лист со списком. Вкладка «Разработчик» - «Код» - «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

Выпадающий список в Excel с данными с другого листа/файла

Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.

  1. Делаем активной ячейку, куда хотим поместить раскрывающийся список.
  2. Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).

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

Как сделать зависимые выпадающие списки

Возьмем три именованных диапазона:

Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.

  1. Создадим первый выпадающий список, куда войдут названия диапазонов.
  2. Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
  3. Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.
  4. Выбор нескольких значений из выпадающего списка Excel

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

    1. Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
    2. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("Е2:Е9")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(0, 1)) = 0 Then Target.Offset(0, 1) = Target Else Target.End(xlToRight).Offset(0, 1) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub  
    3. Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
    4. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("Н2:К2")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub  
    5. Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препина

exceltable.com

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

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

Выбор уникальных и повторяющихся значений в Excel

Для примера возьмем историю взаиморасчетов с контрагентами, как показано на рисунке:

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

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

  1. Выделите первый столбец таблицы A1:A19.
  2. Выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Дополнительно».
  3. В появившемся окне «Расширенный фильтр» включите «скопировать результат в другое место», а в поле «Поместить результат в диапазон:» укажите $F$1.
  4. Отметьте галочкой пункт «Только уникальные записи» и нажмите ОК.

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



Теперь нам необходимо немного модифицировать нашу исходную таблицу. Выделите первые 2 строки и выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Вставить» или нажмите комбинацию горячих клавиш CTRL+SHIFT+=.

У нас добавилось 2 пустые строки. Теперь в ячейку A1 введите значение «Клиент:».

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

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

  1. Перейдите в ячейку B1 и выберите инструмент «ДАННЫЕ»-«Работа с данными»-«Проверка данных».
  2. На вкладке «Параметры» в разделе «Условие проверки» из выпадающего списка «Тип данных:» выберите значение «Список».
  3. В поле ввода «Источник:» введите =$F$4:$F$8 и нажмите ОК.

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

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

Выборка ячеек из таблицы по условию в Excel:

  1. Выделите табличную часть исходной таблицы взаиморасчетов A4:D21 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматируемых ячеек».
  2. Чтобы выбрать уникальные значения из столбца, в поле ввода введите формулу: =$A4=$B$1 и нажмите на кнопку «Формат», чтобы выделить одинаковые ячейки цветом. Например, зеленым. И нажмите ОК на всех открытых окнах.

Готово!

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

Скачать пример выборки из списка с условным форматированием.

Принцип действия автоматической подсветки строк по критерию запроса очень прост. Каждое значение в столбце A сравнивается со значением в ячейке B1. Это позволяет найти уникальные значения в таблице Excel. Если данные совпадают, тогда формула возвращает значение ИСТИНА и для целой строки автоматически присваивается новый формат. Чтобы формат присваивался для целой строки, а не только ячейке в столбце A, мы используем смешанную ссылку в формуле =$A4.

exceltable.com

Excel. Как сделать выбор значения ячейки из списка « Марк и Марта.Ру. Записки отца-программиста

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

Рис.1. Возможные варианты для выбора значений ячейки

 

Так, если таблица для заполнения будет на Листе 1, то варианты для выбора можно разместить на Листе 2.

 

1. Переключитесь на Лист 1 и установите курсов в ту ячейку, где нужно сделать возможность выбора значения.

 

2. В верхней панели перейти в раздел "Данные" и нажать на кнопку "Проверка данных" (Рис.2)

 

 

 

Рис.2. Проверка данных

 

3. В открывшемся окне во вкладке "Параметры" выберите тип данных "Список", после чего появится поле для выбора источника данных. Выберите диапазон данных и нажмите "Ок" (Рис.3)

 

Рис.3. Изменение параметров для ячейки (Gif)

 

4. Теперь при наведении курсора мыши на ячейку справа от нее появится стрелка вниз, при нажатии на которую откроется наш список значений (Рис.4)

Рис.4. Ячейка с возможностью выбора значения

 

5. Свойства ячеек можно распространить на соседние (Рис.5)

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

 


markimarta.ru

Как заполнить диапазон ячеек случайными числами?

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

Стандартные функции Excel

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

Надстройка "Пакет анализа"

Встроенная в excel надстройка «Пакет анализа» устанавливается через диалоговое окно менеджера надстроек. После установки на вкладке «Данные» ленты Excel появляется новая кнопка «Анализ данных». Одним из инструментов анализа является «Генерация случайных чисел». Подробнее об этом инструменте можно узнать в справке. Этот инструмент применяется для заполнения диапазона случайными числами, извлеченными из одного или нескольких распределений. С помощью этой процедуры можно моделировать объекты, имеющие случайную природу, по известному распределению вероятностей.

Генератор случайных чисел

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

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

Целые числа

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

Целые числа без повторов

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

Дробные числа с заданным количеством знаков

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

Дробные числа с разным количеством знаков после запятой

Кроме всего прочего в надстройке предусмотрена возможность генерации случайных (рандомных) чисел с различным количеством знаков после запятой. Это количество может варьироваться от 0 до 15.

Видео по работе с надстройкой

macros-vba.ru


Смотрите также