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

Вход

Регистрация

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

 

= Мир MS Excel/Найти уникальные значения из нескольких диапазонов - Страница 2 - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 2 из 2
  • «
  • 1
  • 2
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Найти уникальные значения из нескольких диапазонов (Формулы/Formulas)
Найти уникальные значения из нескольких диапазонов
_Boroda_ Дата: Пятница, 12.04.2019, 23:13 | Сообщение № 21
Группа: Модераторы
Ранг: Местный житель
Сообщений: 15417
Репутация: 6032 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Это не "страшащая", это как раз простая, там все логично.
Я завтра распишу подробнее


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЭто не "страшащая", это как раз простая, там все логично.
Я завтра распишу подробнее

Автор - _Boroda_
Дата добавления - 12.04.2019 в 23:13
Kaktus8 Дата: Пятница, 12.04.2019, 23:22 | Сообщение № 22
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Спасибо, большое!
Будет очень интересно.
 
Ответить
СообщениеСпасибо, большое!
Будет очень интересно.

Автор - Kaktus8
Дата добавления - 12.04.2019 в 23:22
_Boroda_ Дата: Воскресенье, 14.04.2019, 01:18 | Сообщение № 23
Группа: Модераторы
Ранг: Местный житель
Сообщений: 15417
Репутация: 6032 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
1. В диапазоне $LT5:$MO5 22 столбца. Желтых по 4 столбца, а белых по 2, всего 4+2=6
2. СТОЛБЕЦ($A:$V) - дает массив 1; 2; ...; 22. СТОЛБЕЦ($A:$V)-1 - 0;1;...;21
3. ОСТАТ(СТОЛБЕЦ($A:$V)-1;6) - остаток от деления п.2 на 6, дает 0;1;2;3;4;5;0;1;... 0-3 - желтые столбцы, 4-5 - белые
4. ЕСЛИ(ОСТАТ(СТОЛБЕЦ($A:$V)-1;6)<4;$LT5:$MO5) - если столбцы желтые, то значения из $LT5:$MO5, если белые, то ЛОЖЬ
5. ЕСЛИОШИБКА(ПОИСКПОЗ($LT5:$MO5; п.4 ;);"") - ищем номер вхождения (номер первой по порядку встречающейся ячейки с нужным значением, см. *) значений из $LT5:$MO5 в полученном в п.4 (то есть в желтых значениях из $LT5:$MO5). Если не находим (это некоторые белые), то ""
* Например, ПОИСКПОЗ({5;6;222;6;"";5};{5;6;ЛОЖЬ;6;"";5};) даст массив {1;2;Н/Д;2;5;1}
6. СТОЛБЕЦ($A:$V)*($LT5:$MO5<>"") - даст массив 1;2;... (см. п.2) только для тех случаев, когда в $LT5:$MO5 не пусто
7. ЕСЛИОШИБКА(ПОИСКПОЗ($LT5:$MO5;ЕСЛИ(ОСТАТ(СТОЛБЕЦ($A:$V)-1;6)<4;$LT5:$MO5););"")=СТОЛБЕЦ($A:$V)*($LT5:$MO5<>"") - сравниваем массивы, полученные в п. 5 и в п.6, получаем ИСТИНА только для тех элементов массива, где $LT5:$MO5 не пустые и встречаются в первый раз
8. ЕСЛИ( п.7 ;СТОЛБЕЦ($LT:$MO)) - если в массиве, полученном в п.7, у нас ИСТИНА, то дает номер соответствующего столбца, иначе - ЛОЖЬ
9. СТОЛБЕЦ(A5) - дает номер столбца ячейки А5 - 1. При смещении формулы вправо ссылка на А5 тоже будет смещаться (станет В5, С5, ...) и даст 2, 3, ...
10. НАИМЕНЬШИЙ( п.8 ; п.9 ) - вынимает из массива п.8 то наименьшее значение, которое мы получили в п.9.
11. ИНДЕКС(5:5; п.10 ) - из строки 5 вынимает значение той ячейки, номер столбца которой мы нашли в п.10
12. ЕСЛИОШИБКА( п.11 ; "" ) - когда уникальные кончились, НАИМЕНЬШИЙ даст ошибку. Вместо нее пишем ""


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение1. В диапазоне $LT5:$MO5 22 столбца. Желтых по 4 столбца, а белых по 2, всего 4+2=6
2. СТОЛБЕЦ($A:$V) - дает массив 1; 2; ...; 22. СТОЛБЕЦ($A:$V)-1 - 0;1;...;21
3. ОСТАТ(СТОЛБЕЦ($A:$V)-1;6) - остаток от деления п.2 на 6, дает 0;1;2;3;4;5;0;1;... 0-3 - желтые столбцы, 4-5 - белые
4. ЕСЛИ(ОСТАТ(СТОЛБЕЦ($A:$V)-1;6)<4;$LT5:$MO5) - если столбцы желтые, то значения из $LT5:$MO5, если белые, то ЛОЖЬ
5. ЕСЛИОШИБКА(ПОИСКПОЗ($LT5:$MO5; п.4 ;);"") - ищем номер вхождения (номер первой по порядку встречающейся ячейки с нужным значением, см. *) значений из $LT5:$MO5 в полученном в п.4 (то есть в желтых значениях из $LT5:$MO5). Если не находим (это некоторые белые), то ""
* Например, ПОИСКПОЗ({5;6;222;6;"";5};{5;6;ЛОЖЬ;6;"";5};) даст массив {1;2;Н/Д;2;5;1}
6. СТОЛБЕЦ($A:$V)*($LT5:$MO5<>"") - даст массив 1;2;... (см. п.2) только для тех случаев, когда в $LT5:$MO5 не пусто
7. ЕСЛИОШИБКА(ПОИСКПОЗ($LT5:$MO5;ЕСЛИ(ОСТАТ(СТОЛБЕЦ($A:$V)-1;6)<4;$LT5:$MO5););"")=СТОЛБЕЦ($A:$V)*($LT5:$MO5<>"") - сравниваем массивы, полученные в п. 5 и в п.6, получаем ИСТИНА только для тех элементов массива, где $LT5:$MO5 не пустые и встречаются в первый раз
8. ЕСЛИ( п.7 ;СТОЛБЕЦ($LT:$MO)) - если в массиве, полученном в п.7, у нас ИСТИНА, то дает номер соответствующего столбца, иначе - ЛОЖЬ
9. СТОЛБЕЦ(A5) - дает номер столбца ячейки А5 - 1. При смещении формулы вправо ссылка на А5 тоже будет смещаться (станет В5, С5, ...) и даст 2, 3, ...
10. НАИМЕНЬШИЙ( п.8 ; п.9 ) - вынимает из массива п.8 то наименьшее значение, которое мы получили в п.9.
11. ИНДЕКС(5:5; п.10 ) - из строки 5 вынимает значение той ячейки, номер столбца которой мы нашли в п.10
12. ЕСЛИОШИБКА( п.11 ; "" ) - когда уникальные кончились, НАИМЕНЬШИЙ даст ошибку. Вместо нее пишем ""

Автор - _Boroda_
Дата добавления - 14.04.2019 в 01:18
Kaktus8 Дата: Воскресенье, 14.04.2019, 19:08 | Сообщение № 24
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Ух-ты! Спасибо, большое!
Теперь всё гораздо понятнее стало!

СТОЛБЕЦ($A:$V) - дает массив

Ах, - это массив! А я, почему-то, в упор этого не видел и не осознал.
Соответственно, вся конструкция ОСТАТ(СТОЛБЕЦ($A:$V)-1;6) воспринималась совершенно таинственной.
Спасибо!
Теперь в общих чертах стало понятно, почему старая формула давала ошибку на белых.
Но пока не понял, почему она не заработала, ведь все операторы те же самые и "" проверялся?
Кстати, там в формуле есть ПОИСКПОЗ(-$LT6:$MO6;-$LT6:$MO6;). Что дают эти минусы перед диапазонами?

Ещё, когда сам пытался что-то соорудить, возникла идея использовать массив констант.
То есть напрямую указать в формуле что-то типа {1;2;3;4;7;8;9;10;13;14;15;16;19;20;21;22} в качестве целевых ячеек,
или даже сразу номера столбцов {332;333;334;335;338;339;340;341;344;345;346;347;350;351;352;353},
но не получилось, а разобраться не успел.
Вообще возможна ли такая конструкция? Ну, чисто теоретически и в познавательных целях?
 
Ответить
СообщениеУх-ты! Спасибо, большое!
Теперь всё гораздо понятнее стало!

СТОЛБЕЦ($A:$V) - дает массив

Ах, - это массив! А я, почему-то, в упор этого не видел и не осознал.
Соответственно, вся конструкция ОСТАТ(СТОЛБЕЦ($A:$V)-1;6) воспринималась совершенно таинственной.
Спасибо!
Теперь в общих чертах стало понятно, почему старая формула давала ошибку на белых.
Но пока не понял, почему она не заработала, ведь все операторы те же самые и "" проверялся?
Кстати, там в формуле есть ПОИСКПОЗ(-$LT6:$MO6;-$LT6:$MO6;). Что дают эти минусы перед диапазонами?

Ещё, когда сам пытался что-то соорудить, возникла идея использовать массив констант.
То есть напрямую указать в формуле что-то типа {1;2;3;4;7;8;9;10;13;14;15;16;19;20;21;22} в качестве целевых ячеек,
или даже сразу номера столбцов {332;333;334;335;338;339;340;341;344;345;346;347;350;351;352;353},
но не получилось, а разобраться не успел.
Вообще возможна ли такая конструкция? Ну, чисто теоретически и в познавательных целях?

Автор - Kaktus8
Дата добавления - 14.04.2019 в 19:08
_Boroda_ Дата: Воскресенье, 14.04.2019, 21:58 | Сообщение № 25
Группа: Модераторы
Ранг: Местный житель
Сообщений: 15417
Репутация: 6032 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
1. В старой формуле результаты ПОИСКПОЗ без ограничений на желтые сравнивались с номерами столбцов с ограничениями. И во второй строке конечно же для двойки ничего не получалось - она же первая белая, а не желтая
2. Попробуйте для случая, когда не ="", а просто пустая ячейка, написать без минусов. Там, где пусто, Вы получите НД. А минусы пустую ячейку преобразуют в ноль и тогда ПОИСКПОЗ отрабатывает нормально
3. Да, можно и так
Код
=ЕСЛИОШИБКА(ИНДЕКС(5:5;НАИМЕНЬШИЙ(ЕСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ($LT5:$MO5;ЕСЛИ(ОСТАТ({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22}-1;6)<4;$LT5:$MO5););"")={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22}*($LT5:$MO5<>"");{332;333;334;335;336;337;338;339;340;341;342;343;344;345;346;347;348;349;350;351;352;353});СТОЛБЕЦ(A5)));"")


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение1. В старой формуле результаты ПОИСКПОЗ без ограничений на желтые сравнивались с номерами столбцов с ограничениями. И во второй строке конечно же для двойки ничего не получалось - она же первая белая, а не желтая
2. Попробуйте для случая, когда не ="", а просто пустая ячейка, написать без минусов. Там, где пусто, Вы получите НД. А минусы пустую ячейку преобразуют в ноль и тогда ПОИСКПОЗ отрабатывает нормально
3. Да, можно и так
Код
=ЕСЛИОШИБКА(ИНДЕКС(5:5;НАИМЕНЬШИЙ(ЕСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ($LT5:$MO5;ЕСЛИ(ОСТАТ({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22}-1;6)<4;$LT5:$MO5););"")={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22}*($LT5:$MO5<>"");{332;333;334;335;336;337;338;339;340;341;342;343;344;345;346;347;348;349;350;351;352;353});СТОЛБЕЦ(A5)));"")

Автор - _Boroda_
Дата добавления - 14.04.2019 в 21:58
Kaktus8 Дата: Вторник, 16.04.2019, 20:18 | Сообщение № 26
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Да, можно и так

Спасибо за пример!

Спасибо большое за мастер-класс!
Оч. познавательно!
 
Ответить
Сообщение
Да, можно и так

Спасибо за пример!

Спасибо большое за мастер-класс!
Оч. познавательно!

Автор - Kaktus8
Дата добавления - 16.04.2019 в 20:18
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Найти уникальные значения из нескольких диапазонов (Формулы/Formulas)
  • Страница 2 из 2
  • «
  • 1
  • 2
Поиск:

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