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

Вход

Регистрация

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

 

= Мир MS Excel/двойной отбор из списка - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » двойной отбор из списка (Формулы/Formulas)
двойной отбор из списка
nikitan95 Дата: Среда, 07.06.2017, 23:55 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 221
Репутация: 0 ±
Замечаний: 0% ±

2016
Доброй всем ночи)
Застрял чуток с двойным подбором. Никак не смог запихнуть СМЕЩ в ИНДЕКС. Помогите, пожалуйста, кто сможет. Заранее благодарствую
К сообщению приложен файл: 4908351.xlsx(12Kb)
 
Ответить
СообщениеДоброй всем ночи)
Застрял чуток с двойным подбором. Никак не смог запихнуть СМЕЩ в ИНДЕКС. Помогите, пожалуйста, кто сможет. Заранее благодарствую

Автор - nikitan95
Дата добавления - 07.06.2017 в 23:55
vikttur Дата: Четверг, 08.06.2017, 00:13 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 2387
Репутация: 412 ±
Замечаний: 0% ±

Зачем запихивать? Будьте проще )
Доп. столбец для определения нужных строк:
Код
=ЕСЛИ(C2;МАКС($A$1:A1)+1)

В результирующей таблице ВПР (в файле - с доп. проверками):
Код
=ВПР(СТОЛБЕЦ(B1)/2;Список!$A$2:$E$9;2;)
К сообщению приложен файл: 6479894.xlsx(12Kb)
 
Ответить
СообщениеЗачем запихивать? Будьте проще )
Доп. столбец для определения нужных строк:
Код
=ЕСЛИ(C2;МАКС($A$1:A1)+1)

В результирующей таблице ВПР (в файле - с доп. проверками):
Код
=ВПР(СТОЛБЕЦ(B1)/2;Список!$A$2:$E$9;2;)

Автор - vikttur
Дата добавления - 08.06.2017 в 00:13
AlexM Дата: Четверг, 08.06.2017, 00:21 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3584
Репутация: 902 ±
Замечаний: 0% ±

Excel 2003
Не упрощая.
Формула массива для имен
Код
=ИНДЕКС(Список!$A$2:$A$9;НАИМЕНЬШИЙ(ЕСЛИ(Список!$B$2:$B$9=1;СТРОКА($A$1:$A$8));СТОЛБЕЦ(B1)/2))

вводится в одну ячейку, а потом делается объединение.
Формула для данных
Код
=ЕСЛИ(ВПР(B15;Список!$A$2:$D$9;2;)=1;ВПР(B15;Список!$A$2:$D$9;3;);"")
или так лучше
Код
=ЕСЛИ(ЕЧИСЛО(ВПР(B15;Список!$A$2:$D$9;2;));ВПР(B15;Список!$A$2:$D$9;3;);"")
К сообщению приложен файл: 4908351_01.xlsx(11Kb)



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.


Сообщение отредактировал AlexM - Четверг, 08.06.2017, 00:25
 
Ответить
СообщениеНе упрощая.
Формула массива для имен
Код
=ИНДЕКС(Список!$A$2:$A$9;НАИМЕНЬШИЙ(ЕСЛИ(Список!$B$2:$B$9=1;СТРОКА($A$1:$A$8));СТОЛБЕЦ(B1)/2))

вводится в одну ячейку, а потом делается объединение.
Формула для данных
Код
=ЕСЛИ(ВПР(B15;Список!$A$2:$D$9;2;)=1;ВПР(B15;Список!$A$2:$D$9;3;);"")
или так лучше
Код
=ЕСЛИ(ЕЧИСЛО(ВПР(B15;Список!$A$2:$D$9;2;));ВПР(B15;Список!$A$2:$D$9;3;);"")

Автор - AlexM
Дата добавления - 08.06.2017 в 00:21
nikitan95 Дата: Четверг, 08.06.2017, 00:37 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 221
Репутация: 0 ±
Замечаний: 0% ±

2016
vikttur, Ваша формула, вроде удобная очень и простая, но учитывая что список содержит около 2000 строк, то получится накладно.
AlexM, принимаю Ваш вариант. Но не пойму, чем Вас не устроила короткая формула для данных? Может есть нюанс какой-то который я не знаю?


Сообщение отредактировал nikitan95 - Четверг, 08.06.2017, 00:43
 
Ответить
Сообщениеvikttur, Ваша формула, вроде удобная очень и простая, но учитывая что список содержит около 2000 строк, то получится накладно.
AlexM, принимаю Ваш вариант. Но не пойму, чем Вас не устроила короткая формула для данных? Может есть нюанс какой-то который я не знаю?

Автор - nikitan95
Дата добавления - 08.06.2017 в 00:37
nikitan95 Дата: Четверг, 08.06.2017, 01:16 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 221
Репутация: 0 ±
Замечаний: 0% ±

2016
AlexM, формула действует, но сильно тормозит((. Как этого избежать?
 
Ответить
СообщениеAlexM, формула действует, но сильно тормозит((. Как этого избежать?

Автор - nikitan95
Дата добавления - 08.06.2017 в 01:16
AndreTM Дата: Четверг, 08.06.2017, 02:42 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1762
Репутация: 496 ±
Замечаний: 0% ±

2003 & 2010
Как избежать? Отключать автоперасчет на время добавления данных, например...

Кроме того, понятно, что отбираете вы фильтром не 1000 записей же (столько столбцов получится - ужас)? А ячейки с формулами вы вынуждены держать "с запасом".
Не пробовали просто использовать сводную для отбора? (см.пример - немного некорректный, но общее представление даёт)
К сообщению приложен файл: 2-34063-1.xlsx(16Kb)


Skype: andre.tm.007
Donate: Qiwi: 9517375010
 
Ответить
СообщениеКак избежать? Отключать автоперасчет на время добавления данных, например...

Кроме того, понятно, что отбираете вы фильтром не 1000 записей же (столько столбцов получится - ужас)? А ячейки с формулами вы вынуждены держать "с запасом".
Не пробовали просто использовать сводную для отбора? (см.пример - немного некорректный, но общее представление даёт)

Автор - AndreTM
Дата добавления - 08.06.2017 в 02:42
nikitan95 Дата: Четверг, 08.06.2017, 08:09 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 221
Репутация: 0 ±
Замечаний: 0% ±

2016
AndreTM, отключить автопересчет не вариант, ибо данные надо сверять постоянно с другими листами. Сводная тоже отпадает, почти по той же причине
 
Ответить
СообщениеAndreTM, отключить автопересчет не вариант, ибо данные надо сверять постоянно с другими листами. Сводная тоже отпадает, почти по той же причине

Автор - nikitan95
Дата добавления - 08.06.2017 в 08:09
AndreTM Дата: Четверг, 08.06.2017, 08:28 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1762
Репутация: 496 ±
Замечаний: 0% ±

2003 & 2010
Не понял логики, почему "отпадает". "Сверять" вы можете, используя снова исходные данные, а не построенный вами список. Это же уже другая задача.
Либо вы решаете задачу, придумывая какие-то свои "методы" вместо использования имеющихся алгоритмов. Если вы ставили задачу "получить такой-то список" - то и получаете этот список. А если "про себя" вы считали, что "надо получить вот этот список а потом на его основе я дальше ещё что-то буду выбирать/прикручивать" - может, стоило сразу рассказывать про желательный конечный результат? :)


Skype: andre.tm.007
Donate: Qiwi: 9517375010
 
Ответить
СообщениеНе понял логики, почему "отпадает". "Сверять" вы можете, используя снова исходные данные, а не построенный вами список. Это же уже другая задача.
Либо вы решаете задачу, придумывая какие-то свои "методы" вместо использования имеющихся алгоритмов. Если вы ставили задачу "получить такой-то список" - то и получаете этот список. А если "про себя" вы считали, что "надо получить вот этот список а потом на его основе я дальше ещё что-то буду выбирать/прикручивать" - может, стоило сразу рассказывать про желательный конечный результат? :)

Автор - AndreTM
Дата добавления - 08.06.2017 в 08:28
vikttur Дата: Четверг, 08.06.2017, 09:28 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 2387
Репутация: 412 ±
Замечаний: 0% ±

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

Автор - vikttur
Дата добавления - 08.06.2017 в 09:28
AlexM Дата: Четверг, 08.06.2017, 10:12 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3584
Репутация: 902 ±
Замечаний: 0% ±

Excel 2003
Но не пойму, чем Вас не устроила короткая формула для данных
При протяжке формулы вправо, при отсутствии данных нет ошибки.

сильно тормозит((. Как этого избежать?
Прислушайтесь к советам коллег.
Одно из важных характеристик таблицы, это количество строк около 2000. Почему-то об этом вспоминают/сообщают, когда есть готовые решения.
Хорошо что предложенные решения без СМЕЩ(). Вы бы устали ждать пересчет формул.



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
Но не пойму, чем Вас не устроила короткая формула для данных
При протяжке формулы вправо, при отсутствии данных нет ошибки.

сильно тормозит((. Как этого избежать?
Прислушайтесь к советам коллег.
Одно из важных характеристик таблицы, это количество строк около 2000. Почему-то об этом вспоминают/сообщают, когда есть готовые решения.
Хорошо что предложенные решения без СМЕЩ(). Вы бы устали ждать пересчет формул.

Автор - AlexM
Дата добавления - 08.06.2017 в 10:12
nikitan95 Дата: Четверг, 08.06.2017, 15:24 | Сообщение № 11
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 221
Репутация: 0 ±
Замечаний: 0% ±

2016
vikttur, жму Вашу руку. Спасибо!
AlexM, спасибо за ценные советы. Если честно, я думал, что СМЕЩ () не такой уж плохиш при большом объеме данных. Теперь буду знать.


Сообщение отредактировал nikitan95 - Четверг, 08.06.2017, 15:25
 
Ответить
Сообщениеvikttur, жму Вашу руку. Спасибо!
AlexM, спасибо за ценные советы. Если честно, я думал, что СМЕЩ () не такой уж плохиш при большом объеме данных. Теперь буду знать.

Автор - nikitan95
Дата добавления - 08.06.2017 в 15:24
jakim Дата: Четверг, 08.06.2017, 15:52 | Сообщение № 12
Группа: Друзья
Ранг: Ветеран
Сообщений: 800
Репутация: 206 ±
Замечаний: 0% ±

Вариант с функцией АГРЕГАТ.
Фамилии

Код
=IFERROR(INDEX(Список!$A$2:$A$100;AGGREGATE(15;6;ROW($1:$100)/(Список!$B$2:$B$100=1);CEILING(COLUMNS($B:B)/2;1)));"")

данные

Код
=IFERROR(INDEX(Список!$C$2:$D$9;AGGREGATE(15;6;ROW($1:$100)/(Список!$B$2:$B$100=1);CEILING(COLUMNS($B:B)/2;1));MOD(COLUMNS($B:B)-1;2)+1);"")
К сообщению приложен файл: 8550235.xlsx(12Kb)
 
Ответить
Сообщение
Вариант с функцией АГРЕГАТ.
Фамилии

Код
=IFERROR(INDEX(Список!$A$2:$A$100;AGGREGATE(15;6;ROW($1:$100)/(Список!$B$2:$B$100=1);CEILING(COLUMNS($B:B)/2;1)));"")

данные

Код
=IFERROR(INDEX(Список!$C$2:$D$9;AGGREGATE(15;6;ROW($1:$100)/(Список!$B$2:$B$100=1);CEILING(COLUMNS($B:B)/2;1));MOD(COLUMNS($B:B)-1;2)+1);"")

Автор - jakim
Дата добавления - 08.06.2017 в 15:52
nikitan95 Дата: Четверг, 08.06.2017, 16:12 | Сообщение № 13
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 221
Репутация: 0 ±
Замечаний: 0% ±

2016
jakim, спасибо за интересный вариант. Завтра поэкспериментирую.
 
Ответить
Сообщениеjakim, спасибо за интересный вариант. Завтра поэкспериментирую.

Автор - nikitan95
Дата добавления - 08.06.2017 в 16:12
Мир MS Excel » Вопросы и решения » Вопросы по Excel » двойной отбор из списка (Формулы/Formulas)
Страница 1 из 11
Поиск:

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