Страницы: 1
RSS
Сделать выборку из диапазона данных, в котором есть объединенные ячейки разные по количеству строк, Получение дынных с использованием объединенных ячеек и зависимых выпадающих списков
 
Здравствуйте!
Перерыл весь форум, подобной темы не нашел.
Задача: из диапазона данных (лист Товары) при выборе в зависимых списках Товар и Регион (лист Выборка) возвращать данные поставщиков - их название и контакты.
Проблема: Некоторые товары расположены в объединенных ячейках с разным количеством объединенных строк. Никак не получается возвращать значения по всем имеющимся поставщикам.
В данный момент формулы работают не правильно, подтягивают данные из ненужных строк.
Товаров и поставщиков очень много, в примере взял небольшой фрагмент файла. Поставщики постоянно меняются, т.е. изменяется количество строк в объединенных ячейках, в основном строки добавляются.
Важно: т.к. контора казенная, в наличии только MS Office 2013, также, из-за политики безопасности запрещены макросы.
Знаний не хватает своих, прошу помощи у опытных форумчан, спасибо!
Изменено: Сергей Пенкин - 29.06.2022 00:17:37
 
Цитата
Товары расположены в объединенных ячейках
Цитата
формулы работают не правильно
ИМХО от чего-то нужно отказываться
Например в сторону макросов вместо формул
Изменено: Александр Моторин - 29.06.2022 05:31:08
 
Вариант.
 
Еще один
Код
=ЕСЛИ(СТРОКА(C1)>СУММПРОИЗВ(Ч(ПРОСМОТР(СТРОКА($3:$38);СТРОКА($3:$38)/(Товары!$A$3:$A$38>0);Товары!$B$3:$B$38)=$C$4));"";ИНДЕКС(Товары!C3:I38;ПОИСКПОЗ($C$4;Товары!$B$3:$B$38;);ПОИСКПОЗ($C$10;Товары!$C$1:$J$1;)))
Скажи мне, кудесник, любимец ба’гов...
 
Здравствуйте memo и _Boroda_!
У вас получилось! Но как?
Пытаюсь проанализировать каждую маленькую формулу, из которых состоит вся конструкция из обоих вариантов - но, увы... Есть странности, для меня не понятные. Например, в ваших формулах странным образом участвуют ячейки, которые никак не используются
Еще у _Boroda_ в середине кода стоит вопросительный знак, выделенный зеленым, что это значит?) И еще, я двигаю скопированный кусок формулы =СТРОКА($3:$38)/(Товары!$A$3:$A$38>0)
по соседним ячейкам и получаю разные результаты!) Как такое возможно???)
Для меня было полной и приятной неожиданностью то, что здесь, на форуме, так быстро отвечают и всё - по-делу!
Я очень вам благодарен за помощь, и, конечно, продолжу изучать оба варианта, чтоб в случае катастрофы, я мог сам все починить. Буду рад вашим комментариям с пояснениями)

Есть еще один вопрос, господа) Он из разряда необязательных, но все-же, интересно, решается он как-то по-другому или нет.
В некоторые ячейки на листе "Товары" вставлено примечание, и чтоб его вывести, я делаю формулу на листе "Выборка" рядом с ячейкой, где должны всплывать нужные данные, и переписываю в нее то самое примечание.
К примеру, на листе "Выборка", левее ячеек, в каждой строке, где должны всплывать поставщики, прописана формула:
=ЕСЛИОШИБКА(ЕСЛИ($C12="ООО "Визави";"Курьеру: Около офиса злая собака!!!";ЕСЛИ($C12="ИП Аверьянов";"Договариваться о встрече минимум за сутки!!!";""));"")
с перечислением важных примечаний. Эти примечания постоянны, пока актуальна информация о поставщике, их порядка 15-20 на всю огромную таблицу.
Спасибо огромное всем за помощь!!!) :)  
Изменено: Сергей Пенкин - 01.07.2022 01:59:24
 
Цитата
Сергей Пенкин написал:
у  _Boroda_  в середине кода стоит вопросительный знак
Это движок форума что-то сам перевел ))) Смотрите формулу в приложенном файле

Разбор формулы
Вторая часть формулы
1 ПОИСКПОЗ($C$4;Товары!$B$3:$B$38;)
Ищет позицию первого вхождения товара (дынь) на листе Товары
2 ПОИСКПОЗ($C$10;Товары!$C$1:$J$1;)
Ищет позицию первого вхождения региона на листе Товары
3 ИНДЕКС(Товары!C3:I38;п.1;п.2)
Из таблицы Товары!C3:I38 выводит строку из п.1 и столбец из п.2
Обратите внимание на отсутствие закрепления (знак $)
При протягивании формулы вправо диапазон Товары!C3:I38 сместится и станет диапазоном Товары!D3:J38
При протягивании формулы вниз диапазон Товары!C3:I38 сместится и станет диапазоном Товары!C4:I39
За счет этого можно не добавлять 1 к п.2 для вывода контактов и не добавлять +1 на каждую следующую строку

Первая часть формулы
4 СТРОКА($3:$38)
Даст массив из номеров строк 3:4:5:…;38
5 Товары!$A$3:$A$38>0
Даст массив из ИСТИНА и ЛОЖЬ
6 СТРОКА($3:$38)/(Товары!$A$3:$A$38>0)
Даст массив из номеров строк (где в п.5 ИСТИНА) и ошибок (где ЛОЖЬ)
7 ПРОСМОТР(СТРОКА($3:$38);СТРОКА($3:$38)/(Товары!$A$3:$A$38>0);Товары!$B$3:$B$38)
Даст массив из товара (где ИСТИНА в п.5) и повторов предыдущего найденного товара (где ЛОЖЬ)
Типа вот так
"Яблоки":"Яблоки":"Яблоки":"Айва":"Груши":"Груши":"Груши":"Груши":"Бананы":…
Понять принцип работы этой конструкции нелегко, попробуйте почитать мою старую пояснялку вот здесь
http://www.excelworld.ru/forum/2-16573-1#138042
8 п.7=$C$4
Даст массив из ИСТИНА (зде товар совпадает) и ЛОЖЬ
9 Ч(п.8)
Преобразует ИСТИНА в 1, а ЛОЖЬ в 0
10 СУММПРОИЗВ(п.9)
Суммирует все полученные в п.9 единички и нолики
В результате мы получаем количество дынь в таблице товаров
11 СТРОКА(C1)
Номер строки для ячейки С1. Даст 1
Но, при протягивании формулы вниз, С1 станет уже С2 и СТРОКА(C1) даст нам двойку
12 ЕСЛИ(п.11>п.12;"";п.3)
Как только номера строк из п.11 станут больше, чем количество товара из п.10, то это покажет нам, что товар закончился и нужно выводить пусто ("")
А пока не закончился - выводим полученное в п.3



Цитата
Сергей Пенкин написал:
Есть еще один вопрос, господа

А где вопрос-то?
Изменено: _Boroda_ - 01.07.2022 10:00:34
Скажи мне, кудесник, любимец ба’гов...
 
Добрый день!
_Boroda_, вопрос касался показа примечаний. Мой способ единственный, или есть альтернативы, чтоб, например, примечание было в виде обычного примечания?
Спасибо за разбор формулы, сегодня буду изучать!  
Изменено: Сергей Пенкин - 04.07.2022 09:19:07
 
Добрый день! Может такая тема уже была...нужна сумма в объединенной ячейке, строки всегда отличаются по количеству, чтобы в ручную не набивать, а с помощью условия какого-нибудь, сумма в столбце J и K
Страницы: 1
Наверх