Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
Динамический диапазон не через СМЕЩ, Альтернативы СМЕЩ при задании динамически именованных диапазонов
 
В общем проблема:
задача какая - у меня есть отчетная таблица по ДДС. она формируется из двух других - приход и расход. таблицы двумерные, поэтому ни о каких сводных таблицах речи нет((( выгружаются из 1С.

на входных таблицах большой перечень операций. при раскрытии иерархии много повторяющихся значений. количество операций со временем растет. т.е. делать жесткую привязку по диапазонам - антивариант.

но это лирика.

не знаю по каким причинам, но функция СМЕЩ для задания динамического диапазона - ни в какую не хочет работать. вроде все правильно ввожу. все компоненты индивидуально выдают правильные результаты. но свожу вместе - и все(

ломал ломал голову... не придумал ничего лучше как обойти эту проблему АДРЕС.

функциями АДРЕС задаю начальную и конечную ячейки диапазона, по соответствующим критериям.
пытаюсь вставить их в определение размера диапазона - ругается. через СЦЕПИТЬ, съедает, но диапазон не виден в выпадающем списке диапазонов, т.е. его нет по прежнему....

В общем две формулы определения абсолютных адресов ячеек. Определяют чётко то что надо:
=АДРЕС(ПОИСКПОЗ(поставщики!A13;приход_статья;0)+7;2;;;"приход" ;)
и
=АДРЕС(ПОИСКПОЗ(поставщики!A14;приход_статья;0)+6;366;;;"приход" ;)

одна на выходе условно дает результат приход!$b$88
вторая - приход!$NB$120
но ЭКСЕЛЬ не хочет воспринимать формулу

=АДРЕС(ПОИСКПОЗ(поставщики!A13;приход_статья;0)+7;2;;;"приход" ;) :АДРЕС(ПОИСКПОЗ(поставщики!A14;приход_статья;0)+6;366;;;"приход" ;)

таблица тут не принципиальна - поэтому не прикладываю.

если какие либо альтернативные варианты функции СМЕЩ. VBA не знаю, поэтому ограничиваюсь только формулами.

Заранее спасибо всем кто откликнется.
Изменено: Павел Бузо - 9 Янв 2013 16:59:23
 
Пытался осилить - "букафф много".., не получилось...

Резюме - без файла-примера ни чего не понятно.
 
с файлом долго возиться. поэтому задам совсем краткий вопрос:

можно ли использовать АДРЕС, задающий начальную и конечную ячейки диапазона согласно условиям, как альтернативу СМЕЩ при задании динамически именованных диапазонов и если да - то правильный синтаксис.

есть другие альтернативы СМЕЩ (без VBA)?
 
можно но это не лучший выход.
для работы ф-ции Адрес нужна сцепка ДВССЫЛ(АДРЕС.........)
по мне так СМЕЩ проще если естественно ссылки абсолютные.
можете такую сцепку попробовать
ИНДЕКС(массив;ПОИСКПОЗ(....)), для диапазона ИНДЕКС(массив;ПОИСКПОЗ(....)):ИНДЕКС(массив;ПОИСКПОЗ(....)).
 
двумерный поиск через ИНДЕКС и ПОИСКПОЗ не подходит((( увы. я с удовольствием пользуюсь этой функцией когда нет повторяющихся наименований в иерархической структуре. тут они есть. из-за чего и весь сыр бор.

индекс ищет первое вхождение, второе и третье и т.д. игнорирует. к тому же индекс возвращает значение в ячейке, и не может использоваться при определении диапазона... или я не прав? Оо
 
Цитата
Павел Бузо пишет:
какие либо альтернативные варианты
макрос.
обрабатывающий 1с-вский бардак и превращающий его в конфетку.

предупреждение: если бардак чудовищный, то макрос, скорее всего, будет небесплатным.
а если бардак обычный - то всё возможно
;)

пс. а ДДС ваша 1с-ка почему не формирует?
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
два ИНДЕКС через ":" дают диапазон - если есть необходимость искать и первое и второе значение.
например =ИНДЕКС(A1:C3;2;) в итоге диапазон А2:С2
при повторяющихся значениях ПОИСКПОЗ скорей всего не подойдет - здесь вы правы но вы же в формулах с АДРЕС его используете? Поэтому и нужен ваш пример чтоб придумать другой алгоритм.
Изменено: V - 9 Янв 2013 17:43:56
 
иногда полезно читать справку от разработчика.
Цитата
Функция ИНДЕКС имеет две синтаксические формы: ссылка и массив. Ссылочная форма всегда возвращает ссылку; форма массива всегда возвращает значение или массив значений. Если первый аргумент функции ИНДЕКС является массивом констант, то лучше использовать форму массива.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
1С выдает стандартный отчет ДДС. беда в том что отделы у нас не особо корректно заполняют входные данные...

пример надо допилить напильником, а то в том виде в каком он сейчас есть - стыдно выкладывать(((

попробую разобраться с массивной функцией индекса)))

спасибо за участие) вернусь с проблемой завтра с утра) а то рабочий день к концу близиться...
 
Может необходимо вычислять последнюю ячейку с данными? Как получить последннюю заполненную ячейку формулой?
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Неее... получение последней заполненной ячейки не прокатывает, т.к. надо найти именно ячейку соответствующую критерию в целом столбике...

в общем поколдовал над примером. цифры ессно условны. постарался изложить свою проблему в таблице. заранее еще раз огромное спасибо за помощь.
 
и в какой ячейке должна быть данная формула? Как определять диапазоны?
ваша формула
разве начало B1 или всетаки B11
Код
=СМЕЩ(ДВССЫЛ(АДРЕС(ПОИСКПОЗ("Возврат денежных средств поставщиком";приход_статья;0)+10;2));;;ПОИСКПОЗ("Обналичивание денежных средств";приход_статья;0)-1;25)
 
Я тоже смотрел ..да и высота диапазона кривая.... там число 9 , а это положение "Обналичивания .." в диапазоне заголовков
 
эмм... ну да.. что то я накосячил сам....
адрес определяет что-то не совсем правильно. углубился в помощь(((

беру паузу. твикс, увы, не ем(((
 
вопрос снимается. решил проблему по другому. не совсем элегантно, но работает. с использованием ИНДЕКС и двуступенчатого ПОИСКПОЗ нужной строки в группе.

спасибо за ваше время и внимание)
 
А показать? Неужели не поможете такому же страждущему (вдруг зайдет в тему)?
 
Покажу)))

в общем у меня была проблема формирования фактического ДДС для сопоставления с планом. В выгрузке из 1с у нас часто бывают повторы, которые проходят в разных статьях. Поэтому сначала была идея формирования динамических диапазонов по критерию названия статьи. Но поскольку это не прокатило, равно как и не прокатывало СУММЕСЛИМН, т.к. таблица двуранговая, пришлось колдовать с ИНДЕКС и ПОИСКПОЗ.

Решение может и несколько громоздко и не элегантно, но мою задачу оно выполняет и мне этого достаточно.

Пример формулы (работу ее см. в приложении)

=ЕСЛИОШИБКА(ИНДЕКС(приход;ПОИСКПОЗ($A19;ДВССЫЛ(СЦЕПИТЬ("приход!"&АДРЕС(ПОИСКПОЗ($A$17;Приход_статья_группа;0)+12;2))):приход!$B$1300;0)+ПОИСКПОЗ($A$17;приход_статья;0)-1;ПОИСКПОЗ(B$4;приход_дата;0)+1);0)

Такие или похожие идеи озвучивались в теме. Но что получилось то получилось.

В общем суть решения.Ключевым моментом являлось определение начала поиска первого совпадения, для чего:
1. с помощью ПОИСКПОЗ ищется ячейка начала диапазона статьи ДДС (ее имя уникально, но ее состав может дублироваться в других статья). СЦЕПИТЬ через АДРЕС в связке с именем листа задаются координаты первой ячейки диапазона соответствующего диапазона статьи ДДС. ДВССЫЛ преображает ее в удобоваримый формулами формат.
2. дальше работает стандартная сцепка поиска по двум критериям ИНДЕКС и 2х ПОИСКПОЗ.

Исходные данные - отчет по ДДС из 1С торговля. Приход и расход разнесены по разным листам.

ЗЫ. На +12, -1, +1 в формуле не обращайте внимания. Это подгонка под кривость основных диапазонов. В идеале надо было правильно задать диапазоны, но было лениво, плюс поджимало время... в общем как то так))) сейчас уже лень переделывать.

ЗЫЗЫ Файл пример пришлось вычистить по максимуму, т.к. надо было уложиться в порог 100кб
Изменено: BPN1973 - 14 Янв 2013 18:35:49
Страницы: 1
Читают тему (гостей: 1)
Наверх