Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Протяжка формулы ВПР по горизонтали и по вертикали
 
#1
10.10.2018 17:54:12

На листе 3, в ячейках I2:I9, находится выпадающий список, значения которого берутся с листа 2,  с диапазона B25:D38.

В ячейке P2 листа 3 указала формулу =ВПР(I2;Лист2!B25:D125;3;0) при помощи которой значения в на листе 3, в ячейках I2:I9 связаны с диапазоном B25: D125, расположенном на листе 2.

Не получается протянуть указанную выше формулу по горизонтали на диапазон P2:W2 листа 2 и по вертикали на диапазон P2:P9. В последующих ячейках  выскакивает Н/Д.

Помогите пожалуйста протянуть формулу на диапазоны P2:W2 и P2:P9, чтобы не выскакивало Н/Д.
 
 
 
#2
10.10.2018 18:00:05
Olga H., почитайте про относительныеи абсолютные ссылки.
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#3
10.10.2018 18:43:47
Попыталась расставить значки $ в формуле =ВПР(I2;Лист2!B25:D125;3;0), но ничего не выходит. При протяжке по горизонтали и вертикали, формула продолжает давать Н/Д. Помогите пожалуйста откорректировать формулу.
 
 
 
#4
10.10.2018 18:58:15
Цитата
Olga H. написал:
Попыталась расставить значки $ в формуле =ВПР(I2;Лист2!B25:D125;3;0), но ничего не выходит.
Как говорил Медведь, и входит, и выходит.  :D
=ВПР(I2;Лист2!$B$25:$D$125;3;0)
 
 
 
#5
10.10.2018 19:22:17
RAN,
по вертикали ваша формула пропягивается и получаются правильные значения, а по горизонтали не протягиваться:  в ячейках выходят Н/Д.
 
 
 
#6
10.10.2018 20:23:21
У вас пчелы не правильные.
Сильнее трясти надо.
=ВПР($I2;Лист2!$B$25:$XFD$125;СТОЛБЕЦ(C$1);0)
Изменено: RAN - 10.10.2018 20:31:33
 
 
 
#7
10.10.2018 20:41:25
RAN,
спасибо, вставила вашу формулу в ячейку P2 на листе 3 и протяжка формулы заработала по вертикали и горизонтали.  

Скажите пожалуйста, какую функцию несёт указание столбцов XFD в вашей формуле?
 
 
 
#8
10.10.2018 20:57:03
Сильнее трясти (тянуть вправо) не возможно.
Изменено: RAN - 10.10.2018 20:58:25
 
 
 
#9
10.10.2018 20:57:28
Цитата
Olga H. написал:
XFD в вашей формуле
это у кота аллергия на пчел :-)
Пришел медведь :-) С запасом  диапазон прикручен, на самом деле можно так
=VLOOKUP($I2;Лист2!$B$25:D$125;COLUMN(C1);0), не закрепляя D, да и в битве за краткость и C1 закреплять нет смысла.
Можно и так
=INDEX(Лист2!D$25:D$125;MATCH($I2;Лист2!$B$25:$B$125;0)) но получится длиннее
А вот так , в  Вашем случае, при отсортированном списке
=LOOKUP($I2;Лист2!$B$25:B$125;Лист2!D$25:D$125)
Изменено: БМВ - 10.10.2018 21:05:32
По вопросам из тем форума, личку не читаю.
 
 
 
#10
10.10.2018 21:32:43
БМБ,
вставляла ваши формулы в ячейку P2 листа 3, но во все случаях в этой ячейке выскакивало #ИМЯ?

Подскажите пожалуйста, что надо сделать, чтобы ваши формулы заработали в ячейке P2?
Изменено: Olga H. - 10.10.2018 21:33:20
 
 
 
#11
10.10.2018 21:35:10
VLOOKUP - ВПР
COLUMN - СТОЛБЕЦ
INDEX- ИНДЕКС
MATCH - ПОИСКПОЗ
LOOKUP - ПРОСМОТР
Изменено: БМВ - 10.10.2018 21:39:24
По вопросам из тем форума, личку не читаю.
 
 
 
#12
10.10.2018 22:33:49
БМВ, в приложенном к вашему сообщению №11 файле указанные ниже формулы работают:
Код
=ВПР($I2;Лист2!$B$25:D$125;СТОЛБЕЦ(C1);0)
=ИНДЕКС(Лист2!D$25:D$125;ПОИСКПОЗ($I3;Лист2!$B$25:$B$125;0))
=ПРОСМОТР($I4;Лист2!$B$25:B$125;Лист2!D$25:D$125)

В моём файле, приложенном к сообщению №1, работает формула
Код
=ВПР($I2;Лист2!$B$25:D$125;СТОЛБЕЦ(C1);0)

Но не работают формулы:
Код
=ИНДЕКС(Лист2!D$25:D$125;ПОИСКПОЗ($I3;Лист2!$B$25:$B$125;0))

Как начинаю менять значения выпадающего списка в ячейке I2,  не реагирует на изменения диапазон P2…  и далее по горизонтали
Код
=ПРОСМОТР($I4;Лист2!$B$25:B$125;Лист2!D$25:D$125)

Вообще не работает при изменении значения выпадающего списка в ячейке I2
 
 
 
#13
10.10.2018 23:07:39
Цитата
Olga H. написал:
Но не работают формулы:=ИНДЕКС(Лист2!D$25:D$125;ПОИСКПОЗ($I3;Лист2!$B$25:$B$125;0))Как начинаю менять значения выпадающего списка в ячейке I2
Странно, не правда-ли?
 
 
 
#14
11.10.2018 00:06:53
RAN,
да, всё формулы БМВ работают: мне в формуле =ИНДЕКС(Лист2!D$25:D$125;ПОИСКПОЗ($I3;Лист2!$B$25:$B$125;0)) надо было заменить для моего вложенного файла I3 на I2, а в формуле =ПРОСМОТР($I4;Лист2!$B$25:B$125;Лист2!D$25:D$125)  заменить I4 на I2.
 
 
 
Страницы: 1
Наверх