Сделать выборку из диапазона данных, в котором есть объединенные ячейки разные по количеству строк, Получение дынных с использованием объединенных ячеек и зависимых выпадающих списков
Здравствуйте! Перерыл весь форум, подобной темы не нашел. Задача: из диапазона данных (лист Товары) при выборе в зависимых списках Товар и Регион (лист Выборка) возвращать данные поставщиков - их название и контакты. Проблема: Некоторые товары расположены в объединенных ячейках с разным количеством объединенных строк. Никак не получается возвращать значения по всем имеющимся поставщикам. В данный момент формулы работают не правильно, подтягивают данные из ненужных строк. Товаров и поставщиков очень много, в примере взял небольшой фрагмент файла. Поставщики постоянно меняются, т.е. изменяется количество строк в объединенных ячейках, в основном строки добавляются. Важно: т.к. контора казенная, в наличии только MS Office 2013, также, из-за политики безопасности запрещены макросы. Знаний не хватает своих, прошу помощи у опытных форумчан, спасибо!
Здравствуйте memo и _Boroda_! У вас получилось! Но как? Пытаюсь проанализировать каждую маленькую формулу, из которых состоит вся конструкция из обоих вариантов - но, увы... Есть странности, для меня не понятные. Например, в ваших формулах странным образом участвуют ячейки, которые никак не используются Еще у _Boroda_ в середине кода стоит вопросительный знак, выделенный зеленым, что это значит?) И еще, я двигаю скопированный кусок формулы =СТРОКА($3:$38)/(Товары!$A$3:$A$38>0) по соседним ячейкам и получаю разные результаты!) Как такое возможно???) Для меня было полной и приятной неожиданностью то, что здесь, на форуме, так быстро отвечают и всё - по-делу! Я очень вам благодарен за помощь, и, конечно, продолжу изучать оба варианта, чтоб в случае катастрофы, я мог сам все починить. Буду рад вашим комментариям с пояснениями)
Есть еще один вопрос, господа) Он из разряда необязательных, но все-же, интересно, решается он как-то по-другому или нет. В некоторые ячейки на листе "Товары" вставлено примечание, и чтоб его вывести, я делаю формулу на листе "Выборка" рядом с ячейкой, где должны всплывать нужные данные, и переписываю в нее то самое примечание. К примеру, на листе "Выборка", левее ячеек, в каждой строке, где должны всплывать поставщики, прописана формула: =ЕСЛИОШИБКА(ЕСЛИ($C12="ООО "Визави";"Курьеру: Около офиса злая собака!!!";ЕСЛИ($C12="ИП Аверьянов";"Договариваться о встрече минимум за сутки!!!";""));"") с перечислением важных примечаний. Эти примечания постоянны, пока актуальна информация о поставщике, их порядка 15-20 на всю огромную таблицу. Спасибо огромное всем за помощь!!!)
Сергей Пенкин написал: у _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_, вопрос касался показа примечаний. Мой способ единственный, или есть альтернативы, чтоб, например, примечание было в виде обычного примечания? Спасибо за разбор формулы, сегодня буду изучать!
Добрый день! Может такая тема уже была...нужна сумма в объединенной ячейке, строки всегда отличаются по количеству, чтобы в ручную не набивать, а с помощью условия какого-нибудь, сумма в столбце J и K