Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Выбор случайного значения из таблицы - по критериям - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выбор случайного значения из таблицы - по критериям (Формулы/Formulas)
Выбор случайного значения из таблицы - по критериям
Dalm Дата: Среда, 31.08.2022, 21:53 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 58
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Добрый вечер.
Помогите решить задачу.

Есть таблица - где каждому предложению, записанному в зеленый столбец - соответствуют от одного до нескольких критериев (записаны в желтые столбцы).
Как записав в рыжие ячейки - случайные критерии, получить в синей ячейке - случайное предложение, наиболее подходящее критериям ?
(А если подходящих - несколько, то случайный из подходящих)
К сообщению приложен файл: 9291956.xls (26.0 Kb)
 
Ответить
СообщениеДобрый вечер.
Помогите решить задачу.

Есть таблица - где каждому предложению, записанному в зеленый столбец - соответствуют от одного до нескольких критериев (записаны в желтые столбцы).
Как записав в рыжие ячейки - случайные критерии, получить в синей ячейке - случайное предложение, наиболее подходящее критериям ?
(А если подходящих - несколько, то случайный из подходящих)
Автор - Dalm
Дата добавления - 31.08.2022 в 21:53
jakim Дата: Четверг, 01.09.2022, 07:20 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1199
Репутация: 313 ±
Замечаний: 0% ±

Excel 2010
Формула

Код
=IFERROR(INDEX($K$11:$K$60;AGGREGATE(15;6;ROW($1:$200)/($M$11:$M$60=$F$3)/($L$11:$L$60=$E$3)/($N$11:$N$60=$G$3)/($O$11:$O$60=$H$3);ROWS($3:3)));"")
К сообщению приложен файл: 7722250.xls (27.0 Kb)
 
Ответить
Сообщение
Формула

Код
=IFERROR(INDEX($K$11:$K$60;AGGREGATE(15;6;ROW($1:$200)/($M$11:$M$60=$F$3)/($L$11:$L$60=$E$3)/($N$11:$N$60=$G$3)/($O$11:$O$60=$H$3);ROWS($3:3)));"")
Автор - jakim
Дата добавления - 01.09.2022 в 07:20
Dalm Дата: Четверг, 01.09.2022, 14:20 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 58
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
jakim, почему-то не работает.
Оставил только один критерий - и формула ничего не смогла найти (хотя в таблице подходящих предложений - полно).
К сообщению приложен файл: 0406084.png (123.7 Kb)
 
Ответить
Сообщениеjakim, почему-то не работает.
Оставил только один критерий - и формула ничего не смогла найти (хотя в таблице подходящих предложений - полно).
Автор - Dalm
Дата добавления - 01.09.2022 в 14:20
scriptapplications Дата: Четверг, 01.09.2022, 22:49 | Сообщение № 4
Группа: Проверенные
Ранг: Участник
Сообщений: 68
Репутация: 12 ±
Замечаний: 0% ±

Dalm, попробуйте записать в синюю клетку формулу массива (через Ctrl+Shift+Enter)
Код
=ИНДЕКС(K11:K39;НАИБОЛЬШИЙ(ЕСЛИ((ИНДЕКС(L11:L39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$E$3)*ABS(ЕПУСТО($E$3)-1)+(ИНДЕКС(M11:M39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$F$3)*ABS(ЕПУСТО($F$3)-1)+(ИНДЕКС(N11:N39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$G$3)*ABS(ЕПУСТО($G$3)-1)+(ИНДЕКС(O11:O39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$H$3)*ABS(ЕПУСТО($H$3)-1)=НАИБОЛЬШИЙ((ИНДЕКС(L11:L39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$E$3)*ABS(ЕПУСТО($E$3)-1)+(ИНДЕКС(M11:M39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$F$3)*ABS(ЕПУСТО($F$3)-1)+(ИНДЕКС(N11:N39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$G$3)*ABS(ЕПУСТО($G$3)-1)+(ИНДЕКС(O11:O39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$H$3)*ABS(ЕПУСТО($H$3)-1);1);СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39)));0);СЛУЧМЕЖДУ(1;СУММ(--((ИНДЕКС(L11:L39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$E$3)*ABS(ЕПУСТО($E$3)-1)+(ИНДЕКС(M11:M39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$F$3)*ABS(ЕПУСТО($F$3)-1)+(ИНДЕКС(N11:N39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$G$3)*ABS(ЕПУСТО($G$3)-1)+(ИНДЕКС(O11:O39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$H$3)*ABS(ЕПУСТО($H$3)-1)+(ИНДЕКС(P11:P39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$I$3)*ABS(ЕПУСТО($I$3)-1)+(ИНДЕКС(Q11:Q39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$J$3)*ABS(ЕПУСТО($J$3)-1)+(ИНДЕКС(R11:R39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$K$3)*ABS(ЕПУСТО($K$3)-1)+(ИНДЕКС(S11:S39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$L$3)*ABS(ЕПУСТО($L$3)-1)=НАИБОЛЬШИЙ((ИНДЕКС(L11:L39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$E$3)*ABS(ЕПУСТО($E$3)-1)+(ИНДЕКС(M11:M39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$F$3)*ABS(ЕПУСТО($F$3)-1)+(ИНДЕКС(N11:N39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$G$3)*ABS(ЕПУСТО($G$3)-1)+(ИНДЕКС(O11:O39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$H$3)*ABS(ЕПУСТО($H$3)-1);1))))))
К сообщению приложен файл: 9291956_1.xlsx (14.6 Kb)
 
Ответить
СообщениеDalm, попробуйте записать в синюю клетку формулу массива (через Ctrl+Shift+Enter)
Код
=ИНДЕКС(K11:K39;НАИБОЛЬШИЙ(ЕСЛИ((ИНДЕКС(L11:L39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$E$3)*ABS(ЕПУСТО($E$3)-1)+(ИНДЕКС(M11:M39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$F$3)*ABS(ЕПУСТО($F$3)-1)+(ИНДЕКС(N11:N39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$G$3)*ABS(ЕПУСТО($G$3)-1)+(ИНДЕКС(O11:O39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$H$3)*ABS(ЕПУСТО($H$3)-1)=НАИБОЛЬШИЙ((ИНДЕКС(L11:L39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$E$3)*ABS(ЕПУСТО($E$3)-1)+(ИНДЕКС(M11:M39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$F$3)*ABS(ЕПУСТО($F$3)-1)+(ИНДЕКС(N11:N39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$G$3)*ABS(ЕПУСТО($G$3)-1)+(ИНДЕКС(O11:O39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$H$3)*ABS(ЕПУСТО($H$3)-1);1);СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39)));0);СЛУЧМЕЖДУ(1;СУММ(--((ИНДЕКС(L11:L39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$E$3)*ABS(ЕПУСТО($E$3)-1)+(ИНДЕКС(M11:M39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$F$3)*ABS(ЕПУСТО($F$3)-1)+(ИНДЕКС(N11:N39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$G$3)*ABS(ЕПУСТО($G$3)-1)+(ИНДЕКС(O11:O39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$H$3)*ABS(ЕПУСТО($H$3)-1)+(ИНДЕКС(P11:P39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$I$3)*ABS(ЕПУСТО($I$3)-1)+(ИНДЕКС(Q11:Q39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$J$3)*ABS(ЕПУСТО($J$3)-1)+(ИНДЕКС(R11:R39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$K$3)*ABS(ЕПУСТО($K$3)-1)+(ИНДЕКС(S11:S39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$L$3)*ABS(ЕПУСТО($L$3)-1)=НАИБОЛЬШИЙ((ИНДЕКС(L11:L39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$E$3)*ABS(ЕПУСТО($E$3)-1)+(ИНДЕКС(M11:M39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$F$3)*ABS(ЕПУСТО($F$3)-1)+(ИНДЕКС(N11:N39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$G$3)*ABS(ЕПУСТО($G$3)-1)+(ИНДЕКС(O11:O39;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(K11:K39))))=$H$3)*ABS(ЕПУСТО($H$3)-1);1))))))
Автор - scriptapplications
Дата добавления - 01.09.2022 в 22:49
Dalm Дата: Пятница, 02.09.2022, 01:18 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 58
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
scriptapplications, вообще не работает.
В синей ячейке - всегда 0
 
Ответить
Сообщениеscriptapplications, вообще не работает.
В синей ячейке - всегда 0
Автор - Dalm
Дата добавления - 02.09.2022 в 01:18
scriptapplications Дата: Пятница, 02.09.2022, 07:16 | Сообщение № 6
Группа: Проверенные
Ранг: Участник
Сообщений: 68
Репутация: 12 ±
Замечаний: 0% ±

Dalm, значит Вы вводите не как формулу массива или в Вашей версии формулы массива не работают, (тогда потребуется использовать дополнительные столбцы) .
Попробуйте завершить ввод формулы одновременным нажатием Ctrl+Shift+Enter или попробуйте вариант с доп. столбцами (во вложенном файле).
Или задайте формулы для дополнительных столбцов и синей ячейки(D3) .
Растяните на весь диапазон формулу из ячейки T11:
Код
=(--И(L11=$E$3;НЕ(ЕПУСТО($E$3))))+(--И(M11=$F$3;НЕ(ЕПУСТО($F$3))))+(--И(N11=$G$3;НЕ(ЕПУСТО($G$3))))+(--И(O11=$H$3;НЕ(ЕПУСТО($H$3))))+(--И(P11=$I$3;НЕ(ЕПУСТО($I$3))))+(--И(Q12=$J$3;НЕ(ЕПУСТО($J$3))))+(--И(R12=$K$3;НЕ(ЕПУСТО($K$3))))+(--И(S12=$L$3;НЕ(ЕПУСТО($L$3))))

Растяните на весь диапазон формулу из ячейки U11:
Код
=ЕСЛИ(НАИБОЛЬШИЙ($T$11:$T$39;1)=T11;СТРОКА()-СТРОКА($T$11)+1;0)

В ячейку D3 запишите выражение:
Код
=ИНДЕКС(K11:K39;НАИБОЛЬШИЙ($U$11:$U$39;СЛУЧМЕЖДУ(1;СЧЁТЕСЛИ($U$11:$U$39;">0"))))
К сообщению приложен файл: 9291956_3.xlsx (15.0 Kb)


Сообщение отредактировал scriptapplications - Пятница, 02.09.2022, 11:15
 
Ответить
СообщениеDalm, значит Вы вводите не как формулу массива или в Вашей версии формулы массива не работают, (тогда потребуется использовать дополнительные столбцы) .
Попробуйте завершить ввод формулы одновременным нажатием Ctrl+Shift+Enter или попробуйте вариант с доп. столбцами (во вложенном файле).
Или задайте формулы для дополнительных столбцов и синей ячейки(D3) .
Растяните на весь диапазон формулу из ячейки T11:
Код
=(--И(L11=$E$3;НЕ(ЕПУСТО($E$3))))+(--И(M11=$F$3;НЕ(ЕПУСТО($F$3))))+(--И(N11=$G$3;НЕ(ЕПУСТО($G$3))))+(--И(O11=$H$3;НЕ(ЕПУСТО($H$3))))+(--И(P11=$I$3;НЕ(ЕПУСТО($I$3))))+(--И(Q12=$J$3;НЕ(ЕПУСТО($J$3))))+(--И(R12=$K$3;НЕ(ЕПУСТО($K$3))))+(--И(S12=$L$3;НЕ(ЕПУСТО($L$3))))

Растяните на весь диапазон формулу из ячейки U11:
Код
=ЕСЛИ(НАИБОЛЬШИЙ($T$11:$T$39;1)=T11;СТРОКА()-СТРОКА($T$11)+1;0)

В ячейку D3 запишите выражение:
Код
=ИНДЕКС(K11:K39;НАИБОЛЬШИЙ($U$11:$U$39;СЛУЧМЕЖДУ(1;СЧЁТЕСЛИ($U$11:$U$39;">0"))))
Автор - scriptapplications
Дата добавления - 02.09.2022 в 07:16
Dalm Дата: Пятница, 02.09.2022, 15:40 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 58
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
scriptapplications, спасибо.
Но тоже почему-то не работает.
Поставил в критериях - слово "вечер" и "туман" - и формула подбирает из таблицы предложения, где вообще нет ни одного сочетания таких критериев.
К сообщению приложен файл: 4632901.png (138.5 Kb)
 
Ответить
Сообщениеscriptapplications, спасибо.
Но тоже почему-то не работает.
Поставил в критериях - слово "вечер" и "туман" - и формула подбирает из таблицы предложения, где вообще нет ни одного сочетания таких критериев.
Автор - Dalm
Дата добавления - 02.09.2022 в 15:40
scriptapplications Дата: Пятница, 02.09.2022, 17:10 | Сообщение № 8
Группа: Проверенные
Ранг: Участник
Сообщений: 68
Репутация: 12 ±
Замечаний: 0% ±

Dalm, в приведённом на рисунке примере нет ни одной строки с сочетанием обоих указанных слов ("вечер" и "туман").
По условию задачи формула позволяет
Цитата
записав в рыжие ячейки - случайные критерии, получить в синей ячейке - случайное предложение, наиболее подходящее критериям

В соответствии с заданными условиями, такое, у которого есть, хотя бы одно слово, заданное в параметрах (слово "туман"), если в списке предложений нет ни одного с приведёнными параметрами, то будет выбор любого предложения.
Результат вычисления полностью соответствует условиям задачи.
Может быть надо получить в синей ячейке - случайное предложение, полностью соответствующее критериям?
Тогда в синей ячейке придётся написать другую формулу:
Код
=ЕСЛИ(НАИБОЛЬШИЙ($T$11:$T$39;1)=СЧЁТЗ($E$3:$L$3);(ИНДЕКС($K$11:$K$39;НАИБОЛЬШИЙ($U$11:$U$39;СЛУЧМЕЖДУ(1;СЧЁТЕСЛИ($U$11:$U$39;">0"))))); "нет подходящих предложений")
К сообщению приложен файл: 9291956_4.xlsx (15.1 Kb)


Сообщение отредактировал scriptapplications - Пятница, 02.09.2022, 17:53
 
Ответить
СообщениеDalm, в приведённом на рисунке примере нет ни одной строки с сочетанием обоих указанных слов ("вечер" и "туман").
По условию задачи формула позволяет
Цитата
записав в рыжие ячейки - случайные критерии, получить в синей ячейке - случайное предложение, наиболее подходящее критериям

В соответствии с заданными условиями, такое, у которого есть, хотя бы одно слово, заданное в параметрах (слово "туман"), если в списке предложений нет ни одного с приведёнными параметрами, то будет выбор любого предложения.
Результат вычисления полностью соответствует условиям задачи.
Может быть надо получить в синей ячейке - случайное предложение, полностью соответствующее критериям?
Тогда в синей ячейке придётся написать другую формулу:
Код
=ЕСЛИ(НАИБОЛЬШИЙ($T$11:$T$39;1)=СЧЁТЗ($E$3:$L$3);(ИНДЕКС($K$11:$K$39;НАИБОЛЬШИЙ($U$11:$U$39;СЛУЧМЕЖДУ(1;СЧЁТЕСЛИ($U$11:$U$39;">0"))))); "нет подходящих предложений")
Автор - scriptapplications
Дата добавления - 02.09.2022 в 17:10
Dalm Дата: Пятница, 02.09.2022, 22:34 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 58
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
scriptapplications, Спасибо Большое.
Теперь все работает.

Скажите, а можно изменить эту формулу, чтобы не было промежуточных столбцов T и U ?
(я просто эту таблицу еще на 10 столбцов буду заполнять - а там по правую сторону столбцы стоят промежуточные и мешают)


Сообщение отредактировал Dalm - Пятница, 02.09.2022, 23:00
 
Ответить
Сообщениеscriptapplications, Спасибо Большое.
Теперь все работает.

Скажите, а можно изменить эту формулу, чтобы не было промежуточных столбцов T и U ?
(я просто эту таблицу еще на 10 столбцов буду заполнять - а там по правую сторону столбцы стоят промежуточные и мешают)
Автор - Dalm
Дата добавления - 02.09.2022 в 22:34
scriptapplications Дата: Суббота, 03.09.2022, 00:04 | Сообщение № 10
Группа: Проверенные
Ранг: Участник
Сообщений: 68
Репутация: 12 ±
Замечаний: 0% ±

Dalm надо добавить столбцы и слагаемые (по одному на каждый новый параметр) в массив (например, колонка AD)
Пример слагаемого
Код
+(--И(U12=$N$3;НЕ(ЕПУСТО($N$3))))

U12 - параметр из таблицы с предложениями
$N$3 - заданный параметр
Пример с добавлением 2-х новых параметров
Код
=(--И(L11=$E$3;НЕ(ЕПУСТО($E$3))))+(--И(M11=$F$3;НЕ(ЕПУСТО($F$3))))+(--И(N11=$G$3;НЕ(ЕПУСТО($G$3))))+(--И(O11=$H$3;НЕ(ЕПУСТО($H$3))))+(--И(P11=$I$3;НЕ(ЕПУСТО($I$3))))+(--И(Q12=$J$3;НЕ(ЕПУСТО($J$3))))+(--И(R12=$K$3;НЕ(ЕПУСТО($K$3))))+(--И(S12=$L$3;НЕ(ЕПУСТО($L$3))))+(--И(T12=$M$3;НЕ(ЕПУСТО($M$3))))+(--И(U12=$N$3;НЕ(ЕПУСТО($N$3))))

Без доп. столбцов только формулой массива (у Вас почему-то не сработало)
К сообщению приложен файл: 9291956_5.xlsx (16.1 Kb)


Сообщение отредактировал scriptapplications - Суббота, 03.09.2022, 00:24
 
Ответить
СообщениеDalm надо добавить столбцы и слагаемые (по одному на каждый новый параметр) в массив (например, колонка AD)
Пример слагаемого
Код
+(--И(U12=$N$3;НЕ(ЕПУСТО($N$3))))

U12 - параметр из таблицы с предложениями
$N$3 - заданный параметр
Пример с добавлением 2-х новых параметров
Код
=(--И(L11=$E$3;НЕ(ЕПУСТО($E$3))))+(--И(M11=$F$3;НЕ(ЕПУСТО($F$3))))+(--И(N11=$G$3;НЕ(ЕПУСТО($G$3))))+(--И(O11=$H$3;НЕ(ЕПУСТО($H$3))))+(--И(P11=$I$3;НЕ(ЕПУСТО($I$3))))+(--И(Q12=$J$3;НЕ(ЕПУСТО($J$3))))+(--И(R12=$K$3;НЕ(ЕПУСТО($K$3))))+(--И(S12=$L$3;НЕ(ЕПУСТО($L$3))))+(--И(T12=$M$3;НЕ(ЕПУСТО($M$3))))+(--И(U12=$N$3;НЕ(ЕПУСТО($N$3))))

Без доп. столбцов только формулой массива (у Вас почему-то не сработало)
Автор - scriptapplications
Дата добавления - 03.09.2022 в 00:04
Dalm Дата: Суббота, 03.09.2022, 01:41 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 58
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
scriptapplications, понятно.
Спасибо за ответ
 
Ответить
Сообщениеscriptapplications, понятно.
Спасибо за ответ
Автор - Dalm
Дата добавления - 03.09.2022 в 01:41
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выбор случайного значения из таблицы - по критериям (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!