Как да създам автоматично формулата Vlookup и за останалите две колони , без да се налага да копирам формулата?

Question

Краси Кръстев · Accepted Answer

Добре, искаш да кажеш че е въвел данни само и единствено в полето col_index_num и формулата сама се е прехвърлила ( без да се раздърпва или копира в съседните колони в office phone и cost center )

или по скоро е въвел така нещата във формулата че разтегляйки да не променяш нищо по диапазоните

Ако е въвел нещо в col_index_num и след това е раздърпал без да пипа нищо по диапазони числа и т.н., тогава нещо което лично аз се сещам което може да улесни формулата е в полето col_index_num да се въведе column функцията и по този начин да НЕ сменяш ръчно числото което показва коя колона искаш да вземеш.

Ето каква е формулата =VLOOKUP($K7;$A$2:$D$8;COLUMN(L3)-10;FALSE)

На снимка 1 примера :

За параметъра за който търсим дадената стойност фиксирам само колоната за в която се намира параметъра в случая е колона K - > $K7 така разтегляйки надясно следва винаги да се търси за Георги т.е. за клетка K7, Но разтегляйки надолу следва да се търси вина за К, НО реда да е променлив т.е. раздърпвайки следва да  стане $K8, $K9 и така на долните редове ще търси за Мария, Асен и т.н. - Т.Е. няма да променям нищо по този параметър разтегляйки таблицата.

За диапазон на основната таблица от която ще взимам информацията го поставям изцяло фиксиран правя го константен така съм сигурен че както и да разтеглям няма да има промяна в този диапзон $A$2:$D$8

За въвеждане на стойността за колоната

( col_index_num ) от която искам да взема информация от основната таблица

НЯМА да въведа директно число 2 например защото ще трябва да го сменям

ръчно в последващите редове, а ще използвам COLUMN функцията ... как

работи тя = COLUMN ( reference ) в reference задавам някоя клетка да речем L3

така записвам стойност 12 ( 12 е номера на колоната от избраната клетка ) т.е.

за col_index_num имам 12 демек 12 колона от диапазона на мен ми трябва 2 в

случая затова пища -10 ..... какво ще се случи когато ратегля формулата

надясно следва COLUMN функцията да стане M3 ( умишлено не фиксирах по

никакъв начин reference стойноста на функцията Column ) и какво става М3 ( М е

13 буква ) 13-10 = 3 демек третата колона от основната таблица когато

разтеглям надолу в дадения ред няма да имам проблем защото става L4, L5, L6

( респективно M4, M5, M6 ) винаги ще е числото което ми трябва .

Ето това е вариант по който може  да се реши само с промяна в col_index_num.

Естествено може да има друг вариант , НО пак ще пропоръчам използването на Match и Index вместо Vlookup.

Краси Кръстев · Answer

Здравей,

разтегли формулата

надясно като обаче преди това си фиксирала диапазона на Vlookup - a

т.е. $А$2:$D$15

фиксирай и колоната и реда H т.е. $H$2 ще се наложи да промениш номера на

колоната тъй като е въведена ръчно т.е. ще трябва да промениш на 3 в Оffice phone и в Cost Center да е 4

След което ако искаш да си копираш формулите бързо и лесно надолу по

редовете следва да махнеш $H$2 доларчето от 2 - ката т.е. $H2 след което

маркираш и трите клектки в които имаш vlookup и разтегляш до долу....

Това е малкото неудобното при търсенето с Vlookup функцията когато разтегляш формули много внимателно трябва да следиш референциите как са !!! за да не омажеш нещата с разтеглянето пък и посочваш точно колона която се променя ръчно препоръчвамти да ползваш Match  и Index в такива случай

Малко повече за този Match и Index в тази тема тук :

https://aula.bg/question#id-122969600001

Надявам се да съм бил полезен с отговора.

Evelina Kavalova · Answer

Благодаря много за отговора , проблемът беше , че по време на изпита , аз предложих веднага да разтегля формулата като променя диапазона , но изпитващия категорично ми каза без дърпане на формулата ??? Аз не се сетих, и той тогава въведе допълнително данни в поле Col_index_num , но не успях да видя точно как ги въведе !

Evelina Kavalova · Answer

Благодаря много за изчерпателния отговор ! Ще отработя предложението ! Предполагам , че има няколко решения въпроса , но условието беше зададено конкретно , което ме подлуди докато не намеря правилното решение ! Още веднъж благодаря !

Evelina Kavalova · Answer

Отработих примера и се получи , благодаря ! Само един въпрос относно клетка L13 , произволно ли я избираме и после извъждаме разликата до която ни е необходимо или .......?

Краси Кръстев · Answer

От клетка L13 ни е необходим само и само и единствено за да вземем номера на колоната й като стойност... т.е може да е и L14, L15 и т.н. стойността като число която взимаме благодарение на фукнцията Column е една и съща = 12 .

Evelina Kavalova · Answer

ОК , благодаря !

Соня Димитрова · Answer

Г-н Кръстев, говорите за Index и Match. Можете ли да ни покажете как работят тези функции, моля?

Краси Кръстев · Answer

@Соня Димитрова - тук : линк
има един коментар в който съм дал разяснение за Index Match, ако не стане ясно пишете под темата в която Ви препратих ще дообесня.

Никола Петров · Answer

Лек ден и успехи!

Лек ден!

Никола Петров · Answer

Лек ден!

Kocho · Answer

Формулата  =VLOOKUP($K7;$A$2:$D$8;COLUMN(L3)-10;FALSE) има един бъг, който може да доведе до големи главоболия, ако се вмъкне колона преди колона L.

По-добре така: =VLOOKUP($K7;$A$2:$D$8;COLUMNS($A3:B3);FALSE)

А може и така:  =VLOOKUP($K7;$A$2:$D$8;MATCH(L$6;$A$1:$D$1;0);FALSE)

Райна · Answer

И за едновременното въвеждане на формулата, маркирали сме областта, в която трябва да се въведе и завършваме въвеждането като натиснем Ctrl+Enter. Относно номера на колонката, най лесно е предварително да въведем номерацията на колонките и използваме адресите на тези клетки.

Райна · Answer

А таблицата с данните преобразувате в таблица (Ctrl+T) и използвате нейното име. Ако не е съвсем ясно пишете.

Как да създам автоматично формулата Vlookup и за останалите две колони , без да се налага да копирам формулата?

Вашият отговор