Поиск позиции в таблице по сигналам - Мир MS Excel

Регистрация

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

= Мир MS Excel/Поиск позиции в таблице по сигналам - Мир MS Excel

Страница 1 из 2 1 2 »
Модератор форума: китин, _Boroda_, DrMini

Поиск позиции в таблице по сигналам (Формулы/Formulas)

Поиск позиции в таблице по сигналам

Дата: Суббота, 11.10.2025, 12:13 | Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 164

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Здравствуйте уважаемые эксперты.

Необходима ваша помощь в написании формулы поиска позиции имен в таблице №1, исходя из данных имен, имеющихся в таблице №2 и сигналов в таблице №1

Подробное ТЗ написал в приложенном файле. Если есть доп. вопросы, пишите. Надеюсь на вашу помощь.

С уважением

К сообщению приложен файл: poisk_pozicii_v_tablice.xlsx (51.8 Kb)

Ответить

Дата: Суббота, 11.10.2025, 18:08 | Сообщение № 2

Группа: Проверенные

Ранг: Старожил

Сообщений: 1402

Репутация: 275 ±

Замечаний: 0% ±

нет

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

Ответить

Дата: Суббота, 11.10.2025, 19:02 | Сообщение № 3

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 164

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Цитата bigor, 11.10.2025 в 18:08, в сообщении № 2 (

)

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

В таблице 2 нет сплошных строк, они копируются из другой книги, если в столбце K напротив стоит единица. Смысл в отфильтровке сигналов таблицы 1. Нужны только сигналы с теми именами, которые есть в таблице 2
Должны совпасть все три имени (головное имя и пара)

Прикладываю файл с примером правильного вычисления (красная и сиреневая комбинация с единицами в столбцах M и N)

С уважением

К сообщению приложен файл: 9771204.xlsx (52.2 Kb)

Сообщение отредактировал ars1734 - Суббота, 11.10.2025, 19:16

Ответить

Дата: Суббота, 11.10.2025, 21:52 | Сообщение № 4

Группа: Проверенные

Ранг: Старожил

Сообщений: 1402

Репутация: 275 ±

Замечаний: 0% ±

нет

ars1734, Понятнее не стало, но вдруг правильно
в M17 и протянуть вправо вниз

Код

=IF(COUNTIF($L$17:$L$180;D$12);IF($L17=D$12;1;MIN(1;COUNTIFS(A$17:A$400;1;$D$17:$D$400;$L17)+COUNTIFS(A$17:A$400;1;$E$17:$E$400;$L17)));"")

Ответить

Дата: Суббота, 11.10.2025, 22:07 | Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 164

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Цитата bigor, 11.10.2025 в 21:52, в сообщении № 4 (

)

=ЕСЛИ(СЧЁТЕСЛИ($L$17:$L$180;D$12);ЕСЛИ($L17=D$12;1;МИН(1;СЧЁТЕСЛИМН(A$17:A$400;1;$D$17:$D$400;$L17)+СЧЁТЕСЛИМН(A$17:A$400;1;$E$17:$E$400;$L17)));"")

Уже, хорошо, но есть нюанс: чтобы в таблице 2 появились единицы в столбцах M или N, нужно чтобы в столбцах A или B тоже стояли единицы (сигналы)

Протянул данную формулу и удалил все единицы в столбцах A и B. Имя Герман показывает 1 1 в первой строке таблицы 2, когда как в стобцах A12 и B12 (сумма) стоят нули. Если нули, то нет сигналов в таблице 1, а значит и в таблице 2
Для уточнения: таблица действует до ячейки № 647
Файл с результатом прилагаю

К сообщению приложен файл: 0008107.xlsx (53.6 Kb)

Сообщение отредактировал ars1734 - Суббота, 11.10.2025, 22:10

Ответить

Дата: Суббота, 11.10.2025, 22:30 | Сообщение № 6

Группа: Проверенные

Ранг: Старожил

Сообщений: 1402

Репутация: 275 ±

Замечаний: 0% ±

нет

ars1734, так даже проще,

Код

=IF(COUNTIF($L$17:$L$180;D$12);MIN(1;COUNTIFS(A$12:A$400;">="&1;$D$12:$D$400;$L17)+COUNTIFS(A$12:A$400;">="&1;$E$12:$E$400;$L17)))

Ответить

Дата: Суббота, 11.10.2025, 23:38 | Сообщение № 7

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 164

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Цитата bigor, 11.10.2025 в 22:30, в сообщении № 6 (

)

=ЕСЛИ(СЧЁТЕСЛИ($L$17:$L$180;D$12);МИН(1;СЧЁТЕСЛИМН(A$12:A$400;">="&1;$D$12:$D$400;$L17)+СЧЁТЕСЛИМН(A$12:A$400;">="&1;$E$12:$E$400;$L17)))

Применил данную формулу. Поставил 1 напротив пары Ангелина Павел. В столбце M появились две единицы. Герман и Ангелина. Сразу есть две ошибки:
1. Имени Павел нет в таблице 2, а должно совпасть все три имени (три единицы в таблице 2)
2. В таблице 2, напротив Герман, должна появиться единица в ячейке N17 (противоположный столбец) Т.е. если у пары сигнал в зеленом столбце, то у головного имени в красном или наоборот.

Файл с результатом прилагаю

К сообщению приложен файл: 8271966.xlsx (53.9 Kb)

Сообщение отредактировал ars1734 - Суббота, 11.10.2025, 23:45

Ответить

Дата: Воскресенье, 12.10.2025, 14:43 | Сообщение № 8

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 164

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Еще раз здравствуйте уважаемые эксперты.

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

Если остались вопросы - пишите
Надеюсь на ваше участие

С уважением

К сообщению приложен файл: 0089155.xlsx (70.8 Kb)

Сообщение отредактировал ars1734 - Воскресенье, 12.10.2025, 14:45

Ответить

Дата: Воскресенье, 12.10.2025, 15:05 | Сообщение № 9

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 164

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

В дополнение прикладываю файл с примерами правильного вычисления. Всего пять сигналов. Два ИСТИНА и три ЛОЖЬ (имена Алина, Алла, Никита и Роман отсутствуют в списке таблицы 3)

К сообщению приложен файл: 7279384.xlsx (70.9 Kb)

Сообщение отредактировал ars1734 - Воскресенье, 12.10.2025, 15:09

Ответить

Дата: Воскресенье, 12.10.2025, 21:06 | Сообщение № 10

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 164

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Продолжаю упрощать задачу

Теперь не нужно ставить единицу у головного имени в противоположный столбец в столбцах Q и R. Все три единицы у трех имен в одном столбце.

Файл с примером правильного вычисления прилагаю

С уважением

К сообщению приложен файл: 7017068.xlsx (70.8 Kb)

Ответить

Дата: Воскресенье, 12.10.2025, 22:54 | Сообщение № 11

Группа: Проверенные

Ранг: Старожил

Сообщений: 1402

Репутация: 275 ±

Замечаний: 0% ±

нет

ars1734, в q17

Код

=IF(COUNTIFS($P$17:$P$178;D$12);MIN(1;SUM(COUNTIFS($P$17:$P$178;$E$12:$E$647)*(A$12:A$647>=1)*(($D$12:$D$647=$P17)+($E$12:$E$647=$P17))));"")

массивная, затем скопировать вниз/вправо

Ответить

Дата: Воскресенье, 12.10.2025, 23:16 | Сообщение № 12

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 164

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Цитата bigor, 12.10.2025 в 22:54, в сообщении № 11 (

)

=ЕСЛИ(СЧЁТЕСЛИМН($P$17:$P$178;D$12);МИН(1;СУММ(СЧЁТЕСЛИМН($P$17:$P$178;$E$12:$E$647)*(A$12:A$647>=1)*(($D$12:$D$647=$P17)+($E$12:$E$647=$P17))));"")

Скопировал в Q17, протянул вниз, вправо, в обоих столбцах нули показывает.

Ответить

Дата: Понедельник, 13.10.2025, 08:50 | Сообщение № 13

Группа: Проверенные

Ранг: Старожил

Сообщений: 1402

Репутация: 275 ±

Замечаний: 0% ±

нет

Цитата ars1734, 12.10.2025 в 23:16, в сообщении № 12 (

)

нули показывает

Цитата bigor, 12.10.2025 в 22:54, в сообщении № 11 (

)

массивная

для пользователей не самых новых версий Excel ввод массивных формул осуществляется 3-мя кнопками Ctrl+Shift+Enter

Ответить

Дата: Понедельник, 13.10.2025, 14:30 | Сообщение № 14

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 164

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Цитата bigor, 13.10.2025 в 08:50, в сообщении № 13 (

)

ввод массивных формул осуществляется 3-мя кнопками Ctrl+Shift+Enter

Понял, не знал такого. Благодарю за информацию.

Загрузил данную массивную формулу, протянул по столбцам Q R. Обнаружил пока единственную ошибку: головное имя в таблице 3 показывает единицу в столбце R, когда как сигнал этот ложный.
Имя Роман в сигнальной паре E23, отсутствует в списке таблицы 3, а это значит что данный сигнал попадает под фильтр и не может учитываться в столбцах Q или R.
Возможно, есть смысл сделать две формулы, для каждого столбца отдельно?
Файл с результатом и ошибкой прилагаю

С уважением

К сообщению приложен файл: 5934544.xlsx (77.3 Kb)

Сообщение отредактировал ars1734 - Понедельник, 13.10.2025, 14:33

Ответить

Дата: Понедельник, 13.10.2025, 14:42 | Сообщение № 15

Группа: Проверенные

Ранг: Старожил

Сообщений: 1402

Репутация: 275 ±

Замечаний: 0% ±

нет

Я за три дня так и не понял логику простановки единиц, до данного момента я исходил из логики, если в A12, B12 больше 0, то в Q и R =1

Ответить

Дата: Понедельник, 13.10.2025, 14:59 | Сообщение № 16

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 164

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Цитата bigor, 13.10.2025 в 14:42, в сообщении № 15 (

)

если в A12, B12 больше 0, то в Q и R =1

A12, B12 это сумма сигналов, если A12 или B12 больше нуля, значит головное имя активно и если оно есть в списке таблицы 3, то запускается поиск позиций. Дальше отрабатываются сигнальные пары. Если оба имени пары есть в списке таблицы 3, то в столбцах Q или R должны появиться единицы напротив этих имен, в зависимости от того, где стоит сигнал пары: в столбцах A или B + добавляется единица к головному имени там же в Q или R. Сколько бы сигнальных пар не появилось, напротив головного имени достаточно одной единицы в столбцах Q или R. или в обоих столбцах по одной, если истинные сигналы есть в A и B.

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

С уважением

Сообщение отредактировал ars1734 - Понедельник, 13.10.2025, 18:11

Ответить

Дата: Понедельник, 13.10.2025, 19:55 | Сообщение № 17

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 164

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

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

(H17=K17)*(H17=1)*(K17=1)*(A17=1)

Для R17

(I17=L17)*(I17=1)*(L17=1)*(B17=1)

Все упирается в поиск совпадений пары имен таблицы 1 с именами в таблице 3

Ответить

Дата: Понедельник, 13.10.2025, 21:10 | Сообщение № 18

Группа: Проверенные

Ранг: Старожил

Сообщений: 1402

Репутация: 275 ±

Замечаний: 0% ±

нет

Еще вариант, массивная

Код

=IF(COUNTIFS($P$17:$P$178;D$12);IF($P17=D$12;MIN(1;SUM(COUNTIFS($P$17:$P$178;$D$17:$D$647)*(COUNTIFS($P$17:$P$178;$E$17:$E$647))*(A$17:A$647=1)));MIN(1;SUM(COUNTIFS($P$17:$P$178;$E$17:$E$647)*(A$17:A$647>=1)*(($D$17:$D$647=$P17)+($E$17:$E$647=$P17)))));"")

Сообщение отредактировал bigor - Понедельник, 13.10.2025, 22:48

Ответить

Дата: Понедельник, 13.10.2025, 22:51 | Сообщение № 19

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 164

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Цитата bigor, 13.10.2025 в 21:10, в сообщении № 18 (

)

=ЕСЛИ(СЧЁТЕСЛИМН($P$17:$P$178;D$12);ЕСЛИ($P17=D$12;МИН(1;СУММ(СЧЁТЕСЛИМН($P$17:$P$178;D$17:D$647)*(A$17:A$647=1)));МИН(1;СУММ(СЧЁТЕСЛИМН($P$17:$P$178;$E$17:$E$647)*(A$17:A$647>=1)*(($D$17:$D$647=$P17)+($E$17:$E$647=$P17)))));"")

Поставил головное имя Всеволод

Если A18=1 (Алла, Николай) то Q42=0 Верно. Аллы нет в таблице 3, но если B18=1, то R42=1 ошибка.

Если A42=1, то Q42=1 (Всеволод) Ошибка. Если B42=1, то R42=0 - Верно. Эдуарда нет в таблице 3

С уважением

Сообщение отредактировал ars1734 - Понедельник, 13.10.2025, 23:10

Ответить

Дата: Вторник, 14.10.2025, 20:58 | Сообщение № 20

Группа: Проверенные

Ранг: Старожил

Сообщений: 1402

Репутация: 275 ±

Замечаний: 0% ±

нет

массивная

Код

=IF(COUNTIFS($P$17:$P$178;D$12);IF($P17=D$12;MIN(1;SUM(COUNTIFS($P$17:$P$178;$D$17:$D$647)*(COUNTIFS($P$17:$P$178;$E$17:$E$647))*(A$17:A$647=1)));MIN(1;SUM(COUNTIFS($P$17:$P$178;$D$17:$D$647)*COUNTIFS($P$17:$P$178;$E$17:$E$647)*(A$17:A$647=1)*(($D$17:$D$647=$P17)+($E$17:$E$647=$P17)))));"")

Ответить

Поиск позиции в таблице по сигналам (Формулы/Formulas)

Страница 1 из 2
1
2
»

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