В общем проблема: задача какая - у меня есть отчетная таблица по ДДС. она формируется из двух других - приход и расход. таблицы двумерные, поэтому ни о каких сводных таблицах речи нет((( выгружаются из 1С.
на входных таблицах большой перечень операций. при раскрытии иерархии много повторяющихся значений. количество операций со временем растет. т.е. делать жесткую привязку по диапазонам - антивариант.
но это лирика.
не знаю по каким причинам, но функция СМЕЩ для задания динамического диапазона - ни в какую не хочет работать. вроде все правильно ввожу. все компоненты индивидуально выдают правильные результаты. но свожу вместе - и все(
ломал ломал голову... не придумал ничего лучше как обойти эту проблему АДРЕС.
функциями АДРЕС задаю начальную и конечную ячейки диапазона, по соответствующим критериям. пытаюсь вставить их в определение размера диапазона - ругается. через СЦЕПИТЬ, съедает, но диапазон не виден в выпадающем списке диапазонов, т.е. его нет по прежнему....
В общем две формулы определения абсолютных адресов ячеек. Определяют чётко то что надо: =АДРЕС(ПОИСКПОЗ(поставщики!A13;приход_статья;0)+7;2;;;"приход" и =АДРЕС(ПОИСКПОЗ(поставщики!A14;приход_статья;0)+6;366;;;"приход"
одна на выходе условно дает результат приход!$b$88 вторая - приход!$NB$120 но ЭКСЕЛЬ не хочет воспринимать формулу
с файлом долго возиться. поэтому задам совсем краткий вопрос:
можно ли использовать АДРЕС, задающий начальную и конечную ячейки диапазона согласно условиям, как альтернативу СМЕЩ при задании динамически именованных диапазонов и если да - то правильный синтаксис.
можно но это не лучший выход. для работы ф-ции Адрес нужна сцепка ДВССЫЛ(АДРЕС.........) по мне так СМЕЩ проще если естественно ссылки абсолютные. можете такую сцепку попробовать ИНДЕКС(массив;ПОИСКПОЗ(....)), для диапазона ИНДЕКС(массив;ПОИСКПОЗ(....)):ИНДЕКС(массив;ПОИСКПОЗ(....)).
двумерный поиск через ИНДЕКС и ПОИСКПОЗ не подходит((( увы. я с удовольствием пользуюсь этой функцией когда нет повторяющихся наименований в иерархической структуре. тут они есть. из-за чего и весь сыр бор.
индекс ищет первое вхождение, второе и третье и т.д. игнорирует. к тому же индекс возвращает значение в ячейке, и не может использоваться при определении диапазона... или я не прав? Оо
два ИНДЕКС через ":" дают диапазон - если есть необходимость искать и первое и второе значение. например =ИНДЕКС(A1:C3;2;) в итоге диапазон А2:С2 при повторяющихся значениях ПОИСКПОЗ скорей всего не подойдет - здесь вы правы но вы же в формулах с АДРЕС его используете? Поэтому и нужен ваш пример чтоб придумать другой алгоритм.
Функция ИНДЕКС имеет две синтаксические формы: ссылка и массив. Ссылочная форма всегда возвращает ссылку; форма массива всегда возвращает значение или массив значений. Если первый аргумент функции ИНДЕКС является массивом констант, то лучше использовать форму массива.
фрилансер Excel, VBA - контакты в профиле "Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
вопрос снимается. решил проблему по другому. не совсем элегантно, но работает. с использованием ИНДЕКС и двуступенчатого ПОИСКПОЗ нужной строки в группе.
в общем у меня была проблема формирования фактического ДДС для сопоставления с планом. В выгрузке из 1с у нас часто бывают повторы, которые проходят в разных статьях. Поэтому сначала была идея формирования динамических диапазонов по критерию названия статьи. Но поскольку это не прокатило, равно как и не прокатывало СУММЕСЛИМН, т.к. таблица двуранговая, пришлось колдовать с ИНДЕКС и ПОИСКПОЗ.
Решение может и несколько громоздко и не элегантно, но мою задачу оно выполняет и мне этого достаточно.
Такие или похожие идеи озвучивались в теме. Но что получилось то получилось.
В общем суть решения.Ключевым моментом являлось определение начала поиска первого совпадения, для чего: 1. с помощью ПОИСКПОЗ ищется ячейка начала диапазона статьи ДДС (ее имя уникально, но ее состав может дублироваться в других статья). СЦЕПИТЬ через АДРЕС в связке с именем листа задаются координаты первой ячейки диапазона соответствующего диапазона статьи ДДС. ДВССЫЛ преображает ее в удобоваримый формулами формат. 2. дальше работает стандартная сцепка поиска по двум критериям ИНДЕКС и 2х ПОИСКПОЗ.
Исходные данные - отчет по ДДС из 1С торговля. Приход и расход разнесены по разным листам.
ЗЫ. На +12, -1, +1 в формуле не обращайте внимания. Это подгонка под кривость основных диапазонов. В идеале надо было правильно задать диапазоны, но было лениво, плюс поджимало время... в общем как то так))) сейчас уже лень переделывать.
ЗЫЗЫ Файл пример пришлось вычистить по максимуму, т.к. надо было уложиться в порог 100кб