Создание правильной формулы для корректной работы при перетаскивании вправо

Пользователь

Сообщений: 29 Регистрация: 07.11.2019

27.02.2021 21:49:47

Добрый день.

За название извиняюсь, я даже не знаю, как обозвать.
Есть документ, в котором я делаю удобную для себя выгрузку.
https://docs.google.com/spreadsheets/d/1v5sjuwv-VfHqgWnAa0gz3ge5YW0kOgjRflLF3dT6MZI/edit?usp=sharing

Все необходимые для себя формулы вывел. Уперся в самое простое. Выгрузка на 100 позиций. На 1 позицию приходится ГРПрить с другого листа с диапазона в 2 строки. Условно формула:

Код
=ГПР(ПОДСТАВИТЬ($A4;":";"");'Лист1'!9:10;2;ЛОЖЬ)

Если заполнять перетягиванием вправо, ничего не меняется в ссылке на диапазон. Я довел формулу, которая по моей логике, должна работать корректно при автозаполнении:

="'Лист1'!"&(СТОЛБЕЦ(F$3)-1)*2-1&":"&(СТОЛБЕЦ(F$3)-1)*2

Но, даже без перетаскивания, она не работает, когда ее подставляешь в функцию. Хотя, возврат идет того самого значения "'Лист1'!9:10". В чем проблема, не понимаю. Пробовал разными вариантами. Можете направить в нужное русло?

Проблема с формулой на листе "Выгрузка".

gling

Пользователь

Сообщений: 4265 Регистрация: 01.01.1970

27.02.2021 22:46:43

Здравствуйте.
Попробуйте такую формулу

Код

=ЕСЛИОШИБКА(ИНДЕКС(ИНДЕКС(Лист1!$B$1:$BJ$200;ПОИСКПОЗ(B$2;Лист1!$A$1:$A$200;)+1;);ПОИСКПОЗ(ПОДСТАВИТЬ($A3;":";"");ИНДЕКС(Лист1!$B$1:$BJ$200;ПОИСКПОЗ(B$2;Лист1!$A$1:$A$200;););));"")

AVERS

Пользователь

Сообщений: 29 Регистрация: 07.11.2019

27.02.2021 22:59:40

gling, все отлично работает. Растянул на один столбец, потом растянул на весь лист. Выгружает, как надо. Спасибо за помощь.

Только, так и не понял, почему моя формула не работает, несмотря на то, что возвращает нужные данные.

Ігор Гончаренко

Пользователь

Сообщений: 14576 Регистрация: 01.01.1970

27.02.2021 23:11:43

Цитата
AVERS написал: формула не работает, несмотря на то, что возвращает нужные данные

а я до этого считал если формула возвращает нужные данные - она работает

Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!

gling

Пользователь

Сообщений: 4265 Регистрация: 01.01.1970

27.02.2021 23:13:55

Если вы об этой формуле

Цитата
AVERS написал: ="'Лист1'!"&(СТОЛБЕЦ(F$3)-1)2-1&":"&(СТОЛБЕЦ(F$3)-1)2

то это просто текст а не ссылка на строки листа1. "Лист1" в кавычках это текст сцепленный с числами рассчитанными по функциям СТОЛБЕЦ() . Чтобы такая формула работала её нужно внедрить в ДВССЫЛ()

Код
=ДВССЫЛ("'Лист1'!"&(СТОЛБЕЦ(F$3)-1)2-1&":"&(СТОЛБЕЦ(F$3)-1)2)

и то не знаю будет ли она работать в таком виде со строками без определения столбцов, так как она работает ссылками либо A1 либо R1C1.

Изменено: gling - 27.02.2021 23:17:24

AVERS Пользователь Сообщений: 29 Регистрация: 07.11.2019	#6 27.02.2021 23:20:17 gling, про ДВССЫЛ не знал. Было подозрение, что дело в возвращаемом формате, но не знал, как обойти. По поводу строк, вроде бы работает. Но не факт, что это правильно. Я так 3 столбца выгружал, только номера строк руками менять приходилось. По этому и озадачился формулой.