Автоматично изваждане на данни от отделни листа - Vlookup

Безплатни 20 урока
регистрирай се и научи

Ако сте вече потребител - Регистриран съм
Видео

Тест за преминаване към следващия урок

 
Ако искаме да използваме VLOOKUP за точно търсене в една таблица, кой е последният параметър, който трябва да зададем?
FALSE
TRUE
 
Ако искаме да използваме VLOOKUP за приблизително търсене в една таблица, кой е последният параметър, който трябва да зададем?
TRUE
FALSE

Въпроси и отговори

  • За Data Validation и Vlookup

    Иванка Калева Чолакова:

    Докато да благодаря за Data Validation, дойде и VLOOKUP. В комбинация явно стават страшно добри неща, които иначе биха били възможни само в Access. Благодаря!

    Иван Цукев:

    Да, Excel спокойно може да се използва като една по-опростена база данни.

    Аз дори го ползвам за анализи на данни от други бази данни с някои плъгини. VLOOKUP донякъде симулира действието на JOIN функцията в mysql

    Марина:

     С VLOOKUP  стават добри неща, но ключовото поле трябва да е уникално.

    Марина:

    Искам да споделя с Вас моят опит. Когато работите с тази функция е много удобно table_array да е в друг шийт и Data Validation да задавате само с колони. Например =VLOOKUP(A1;Sheet2!A:D;2;FALSE). Така не се налага, когато дописвате данни в table_arra да актуализирате Data Validation

    Весела Костадинова:

    Аз работя много с тази функция и същоискам да споделя опита си – много по-лесно ми e да използвам прозореца за създаване на функция, който се избира отреда, в който се показва номера на клетката. Така не трябва да помня в каквапоследователност какви данни се задават. А и то ми подсказва в кои случаи сеползва FALSE и в кои TRUE. Вместо False може да пишете 0, а вместо TRUE – 1, но аз лично винаги ползвам 0 (FALSE).

    Като се ползва този прозорец, веднага севижда какъв резултат ще се върне в клетката. Това е полезно, ако базата данни ес много колони и е трудно да се преброи от кой номер колона ще връща резултата, а по отговора може да се ориентирам дали съм въвела правилната колона.

     

    Vesela Hristova:

    Благодаря, наистина е много лесно по този начин!

    Цветелин Стоянов:

    Пробвах и data validation и table_array да маркирам с цели колони. Супер е. Всичко се получава без да има нужда от актуализиране в name manager.

    Теменужка Тасева:

    Въпрос към Весела: как се отваря този прозорец? при мен при търсене на функцията , VLOOKUP я няма.Работя с версия 2010

    Теменужка Тасева:

    появява се този прозорец и функцията VLOOKUP липсва

    Краси Кръстев:

    Здравей, Теменужка.


    Виждам че въпроса е отправен към Весела, но искам и аз да се включа.

    За да видиш тази функция отиди на fx иконката ( снимка 1 позиция 1 ) след което като ти се отвори прозореца ( снимка 1 - оградения ) след което много важно е в insert function прозореца да зададеш ( снимка 1 позиция 2 ) за категория на търсене All след което в долния прозорец ( снимка 1 позиция 3 ) маркирай една функция после натисни V за да може да отидеш на функциите започващи с V  и съответно намери Vlookup и готово.

    Снимката е от Excel 2013, но реално на 2010 има същата иконка горе при "командния" ред на Excel - ( fx ) от там съответно ще ти излезне и прозореца.

    Надявам се да се е получило.
    Теменужка Тасева:

    Благодаря.Доста се поизмъчих. В отговора ти липсваше това :долния прозорец ( снимка 1 позиция 4 ) .Докато загрея ,че това V трябва да натисна на клавиатурата, еле справих се.Още веднъж благодаря.

    Райна:

    В прозореца Insert Function, заложената категория функции е Most resently used (последно използвани) и ако не е била използвана тази функция, тя не може да бъде в изброените по-долу. Може да изберем функцията Vlookup или от категория All или Lookup & Reference.

    Краси Кръстев:

    @Теменужка, добре че каза позицията, да така една позиция съм сложил повече реално 4 е 3, но сега вече го оправих да е по - разбираемо за другите.Радвам се че си се ориентирала и съответно ти е помогнал отговора. 

    Krastana_kostova@abv.bg:

    Функцията наистина е много интересна и предлага много възможности. Благодаря на всички за споделения опит! Невероятни сте!

  • Марина:

    При VLOOKUP търсенето е по един критерий (стойност равна на стойността на една клетка). Има ли друга подобна на VLOOKUP функция при която търсенето да е по няколко критерии (стойности на няколко клетки) .Подобни функции (с повече критерии) са SUMIFS, COUNTIFS .... Питам и по друг начин. VLOOKUP(lookup_value;table_array;col_index_num;[range_lookup]) това е правилната формула

    ??? VLOOKUP???(lookup_value1;lookup_value2;table_array;col_index_num;[range_lookup]) има ли някаква подобна формула?

    Иван Цукев:

    Това може да се постигне с комбинация от функциите INDEX и MATCH


    например ако имаме една таблица и искаме да "отчетем" стойност по колона Х и ред Y - тоест по два критерия - това най-лесно става с INDEX и MATCH

    Ако дадеш конкретен пример мога да ти помогна сглобиш израза.
    Таня Иванова Говедарова:

    Tова е нещо уникално. Много благодаря. Аз също тъпча цялата информация в една обща база с данни и от там дърпам това, което ми е необходимо за различните справки. Много по-лесно е от колкото да се правят различни таблици и накрая се загубваш в тях. Още веднъж благодаря за този урок. Ще ми е от голяма полза.

    КРАСИМИРА КРЪСТЕВА:

    И при функция VLOOKUP се получава ,ако се изберат всички колони  =VLOOKUP(C5;Данни!A:D;3;false)

    Росица Найденова Кузманова:

    Аз се опитвам да направя упражнението дума по дума или с този израз=VLOOKUP('Отчет-шаблон'!A1:D9,2,FALSE),но версия 2010 ми дава,че има грешка и резултат нула.Пробвах с точка и запетая";" ,пробвах с тве точки":"не се получава по никой начин.Как да се справя?

    Zenny:

    Здравейте, Росица! Правилният израз е ето този :  =VLOOKUP(C5,Данни!A1:D9,2,FALSE) . Първият критерий на функцията трябва да е в първа колона, уникален и да се посочи като име на клетка, а не като таблица както е при вас. 

    Марина Станилова:

    Росица, при мен също се получаваше нула докато не забелязах, че по подразбиране е избран последния ред, в който беше допълнено ново име, но срещу името няма данни за телефон и т.н. След като избрах който и да е друг от списъка (Ана, Георги и т.н.) и повторих, се получи без проблем.

    Никола Петров:

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

  • Отчита ми грешка #REF

    Татяна:

    Здравейте, използвах формулата, за друга моя си бланка, които данни са от 17 колони, до 16 колона работи чудесно, но не мога да разбера защо на последната клетка, която по данните е 17 колона, ми излиза грешка?

    Татяна:

    Искам само да допълня, че съм с версия 2013.

    Иван Цукев:

    Изпрати ми файла по имейл и ще видим от къде идва проблема.

    Татяна:

    Здравейте, и за много години с пожелания за всичко най-хубаво!
    Ами, аз на ново си направих файла и вече не ми отчита грешката... А изобщо не ми дойде до акъла да запазя файла с грешката и да Ви го изпратя, и сега съжалявам, защото все още съм любопитна как се беше получило. Иначе дълго сменях опции, проследявах стъпките, формулата ясно се виждаше, че е правилно написана, бях филтрирала данните, реших, че може да е от филтъра, но не е и от него, гледах формата на клетките... доста "чоплих" по всевъзможните опции и накрая се вбесих и го направих на ново. Файла не е нещо особено, но го създавам за последваща обработка и попълване на данни. Знам ли, може пак да покаже грешка, за това ще Ви го изпратя да го видите.

    Alexander Jordanov:


    Отчита ми грешка #N/A
    Здравейте,отчита грешка в мой sheet,но в вашият от урока няма проблем.Някъква идея какво бъркам
    Alexander Jordanov:

    ето снимка

    Alexander Jordanov:

    изображение на sheet клиенти от където съм взел имената за data validation

    Alexander Jordanov:

    версия на excel 2010

    Альоша Исаев:

    За Александър Йорданов. Доколкото виждам от изображението в таблицата с база данни не е включено името посочено във формулата за B6. Според мен ще трябва да се допълни базата данни с още една колона включваща имената на "Получател".

    Alexander Jordanov:

    има колона  ето я Колона F

    Альоша Исаев:

    Освен да се създаде нов файл нямам друга идея. Според това което виждам е правилно.

    Юлияна Борисова:

    Здравей Alexander, за да работи коректно функцията VLOOKUP, колоната, от която взема данните, трябва да е първа. Просто я изрежи (cut) и премести (paste) като колона "А".  Аз така успях да реша този проблем.

  • Как да извадя на дублирани данни с VLOOKUP?

    Александрина Желева:

    Има ли начин с VLOOKUP да се извадят от таблица, в която има критерий, който се повтаря? Например, едно населено място в България го има в 4 региона и има различни пощенски кодове. Ползвам VLOOKUP, за да ги извадя, но той хваща винаги първото и на следващите слага пощенския код на първия.

    Христо:

    Според мен имаш два начина да се справиш с проблема. Първият е като промениш търсенето, не на населено място, а по пощенски код. Втория е да зададеш на дублираните населени места допълнителна номерация ( или нещо специфично само за това населено място ) и да го търсиш чрез него.

    Александрина Желева:

    Така май пак си е трудоемко. Има населени места, които са 8 с едно и също име. Иска ми се да не ги "цъкам" едно по едно . Целта ми е да сложа точния пощенски код на съответния регион.

    Христо:

    Съжалявам, но без да видя поне примерна таблица, не мога да помогна. Може би първо трябва да ги селектираш по област и след това по населено място.  

    Георги:

    Здравейте, аз също имам проблем с повтарящи се данни.... Има ли опция след извършване на Vlookup да може да се избират данните отново от падащ списък? В смисъл: Избираме от един списък определена информация, използваме Vlookup и в резултата да може да се избира точно каквото ни е необходимо...

    Когато данните са идентични както в първият ред на прикачената снимка всичко е наред... Но когато имам един производител, но различни години на производство, съответно различен одобрен тип, нещо не се получава.... 
    Благодаря предварително!

    Andrey Glushkov:

    @ Георги
    Това мисля, че по-лесно би се получило с index и match. Eто тук има описание на функциите:
     https://support.office.com/bg-bg/article/%D0%A2%D1%8A%D1%80%D1%81%D0%B5%D0%BD%D0%B5-%D0%BD%D0%B0-%D1%81%D1%82%D0%BE%D0%B9%D0%BD%D0%BE%D1%81%D1%82%D0%B8-%D0%B2-%D1%81%D0%BF%D0%B8%D1%81%D1%8A%D0%BA-%D0%BE%D1%82-%D0%B4%D0%B0%D0%BD%D0%BD%D0%B8-c249efc5-5847-4329-bfee-ecffead5ef88?ui=bg-BG&rs=bg-BG&ad=BG#bmcreate_a_lookup_formula_with_the_look

    Краси Кръстев:

    @ Георги,


    Може да погледнеш тази тема тук :

    http://aula.bg/question#id-106572800001

    където си задал подобен въпрос. В нея има отговори които да те насочът към проблема.
  • Може ли да се извършва повторно попълване на данни след Data Validation и Vlookup?

    Георги:

    Здравейте имам следният проблем:

    На прикачената снимка имам данни които се повтарят като производител и модел, но се различават по година на производство и одобрен тип. Искам да попълня чрез Data Validation и List за полето "Производител" и след това чрез Vlookup да попълни автоматично следващите позиции - модел, година на производство и одобрен тип. Когато извърша всичко това се сблъсквам със следните проблеми:
    1. Имам случай когато се дублират имената - пример за производител ITRON имам два модела;
    2. Когато се дублират модела, но се различава годината на производство, съответно одобреният тип;
    Искам да попитам има ли някаква опция, след извършване на Data Validation и List за полето "Производител" в следващото поле да имам падащ списък от който да избера съответния модел и така нататък за попълване на повтарящите се данни.
    Благодаря предварително!
    Andrey Glushkov:

    @ Георги
    Това може би, по-лесно ще стане с index и match. Тук има кратко обяснение: 
    https://support.office.com/bg-bg/article/%D0%A2%D1%8A%D1%80%D1%81%D0%B5%D0%BD%D0%B5-%D0%BD%D0%B0-%D1%81%D1%82%D0%BE%D0%B9%D0%BD%D0%BE%D1%81%D1%82%D0%B8-%D0%B2-%D1%81%D0%BF%D0%B8%D1%81%D1%8A%D0%BA-%D0%BE%D1%82-%D0%B4%D0%B0%D0%BD%D0%BD%D0%B8-c249efc5-5847-4329-bfee-ecffead5ef88?ui=bg-BG&rs=bg-BG&ad=BG#__migbm_2

    Краси Кръстев:

    @ Георги,


    Здравей, 

    Може да ползваш Index и Match.

    Формулата е : = INDEX ($A$3:$B$6;MATCH(H2;$A$3:$A$6;0);2) Най - простичък пример за това това как действа функцията - на снимка 1 съм показал най - основно как действа.

    Снимка 1 позиция 1 ( 2 )  това е диапазона в който ще правя търсенето, след това позиция 3 ( 4 ) това е стойността която искам да намеря  по съвпадение ( в случая Бургас ), позиция 5 ( 6 ) е диапазона в който ще търся "стойността ( в случая търся Бургас ), в позиция 7 ( 8 ) е номера на колоната от която искам да запиша стойността за търсеното от мен стойност ( в случая е 2  и съответно ми изписва бройките, ако е 1 следва да ми изпише Бургас )


    Не знам до къде си стигнал с уроците по Excel, но реално тук може да използваш и Pivot таблици за които става дума в целия модул 10 от курса, като под самите уроци има и вече доста теми във форума с допълнения за тях и интересни дискусии. Анализа с Pivot таблиците е доста удобен когато искаш да правиш справки и анализи само с няколко цъкания. Но не искам да влизам в подробности тук с Pivot т.к. има цял Модул за тях и са обширна тема.

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

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

    Краси Кръстев:

    Добре мисля да дообесня горния пример защото е доста семпъл и ако дам по - голям пример следва да е още по объркващо.

    Задачата е : в клетка I искам да запиша стойност от колона B, която стойност отговаря за точно определена стойност ( съдържание ) от колона A

    Т.е. искам в клетка I да запиша стойността от колона B която отговаря за Бургас да речем.

    Формулата е :

    = INDEX ($A$3:$B$6;MATCH(H2;$A$3:$A$6;0);2)

    $A$3:$B$6 - това е диапазона ( таблицата ) в която ще правя търсенето;

    след което изписвам Match с който ще определя номера на реда който търся. Как ще определя този ред : Първо с H2 - показвам коя стойност от колоната A търся ( Бургас ), в случая посочвам клектка H2 защото в нея съм записал думичката Бургас - стойността спрямо която ще търся. След това избирам диапазона в който да ми направи търсенето точно на тази стойност - $A$3:$A$6 от клектка H2, с След това записвам 0 - нулата показва че търся точно съвпадение на стойността , т.е. търся Бургас, не друг град.Така приключих с функцията Match и избирам следва да завърша функцията INDEX.

    Записвам 2 , с което посочвам от коя колона на диапапзона $A$3:$B$6 да запиша стойността на база вече определениея ред от колоната който търся ( това което направих с Match ) , т.е. от колона 2 от диапзона ми запиши стойността която отговаря на ред на който пише Бургас.

    Така стана ли по - ясно или трябва още малко пояснение.

  • Защо получавам грешка?

    Stasi:

    Как да направя търсенето без да дава грешка? сн.1-база данни, сн.2 опит за vlookup. Искам да сложа номера на всеки магазин след адреса или преди него.

    Краси Кръстев:

    Здравей, Stasi.

    Проблем при теб идва от това че VLOOKUP работи по следния начин:

    V - Означава че търсиш нещо по вертикала;

    Първия параметър който въвеждаш lookup_value е стойност която се търси в 

    ПЪРВАТА вертикала ( колона ) от зададения диапазон, т.е. ти в момента 

    първата ти колона от диапазона е A в която колона нямаш наименованията на 

    ами последователни номера, а реално задаваш из номера ( 1, 2,3 ..... )  да ти 

    намери адреса ( Чепеларе, ул.Дачо Петров ) .... няма как да стане ...


    И сега идва въпроса как да се справиш в проблема, единия вариант е да 

    ползваш Index и Match, който вариант е по  - добрия с оглед на това че там 

    логиката е да търсиш на база пресичане на колона ред и не си ограничен както 

    тук в vlookup - a само по вертикала и с първа колона от диапазон,



    Другия вариант е чрез добавяне ( копиране ) на колоната с адресите пред 

    абсолютно всичко ( или поне преди това което искап да запишеш благодарение 

    на vlookup - a ) и когато зададеш диапазона реално първата колона да ти е тази 

    в която търсиш .... т.е. ако търсиш винаги по точен адрес стоейки винаги 

    отпред ще е пред всички и винаги може диапазона да започва с тази колона и 

    и няма да имаш повече проблеми.

    Надявам се съм те ориентирал и си разбрала логиката на lookup _value че 
    реално тази стойност се търси в ПЪРВАТА колона от зададения диапазон.
    Stasi:

    Да, схванах логиката. Днес си поиграх малко повече и като копирах номерата на магазините след адреса се получи. Тогава схванах, че excel брои първа колона, от която се търси и в дясно от нея колоните се номерират 2, 3 , и т.н. Ще опитам и  Index и Match

    Деница Човикова:

    Бихте ли ми казали , кой е урокът с Index и Match ? Потърсих , но не можах да го открия

    Соня Димитрова:

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

    Краси Кръстев:

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

  • Защо Excel 2011 (Mac) дава грешка при въвеждане на формулата VLOOKUP?

    Лиляна Чавдарова:

    Защо при прилагане на формулата в работния файл, излиза грешка (работя с Office 2011 за Мак). Има ли вариант това да бъде коригирано или е необходимо колоната, от която искаме данните да бъдат взети да бъде винаги първа? Не значи ли това, че променяйки отново колоните за следващия критерий (фирмен номер), и тази формула ще даде грешка поради промяна в референтните колони?

    Иван Цукев:

    Лиляна, дай моля те конкретен пример, за да мога да ти помогна.

    Лиляна Чавдарова:

    Използвам примера от урока, като въвеждам формулата, за да излезе телефонния номер на съответното лице (Ани Петрова) като взимам данните за телефонния и номер от от лист 2 "Данни", но Excel разпознава формулата.

    Лиляна Чавдарова:

    Това е единият проблем:

    Лиляна Чавдарова:

    Ето и вторият проблем:

    Mila:

    При първия проблем е сгрешена формулата. Написали сте + вместо ; след избор на стойност. 


  • Иван Цукев:

    За да разбера по-добре въпроса, имам един уточняващ въпрос:


    Опитваш се да съединиш две таблици, като увеличиш колоните - тоест повече данни за един артикул или се опитваш да увеличиш редовете - тоест едни и същи колони, но повече данни - например данни за миналата и данни за тази година?
    Елена Николаева:

    Благодаря за въпросът Ви инж. Цукев. Зададоха ми следният въпрос по време на интервю: Как бихте направили анализ на продажбите от 2 различни файла (предполагам, че е с еднакви параметри по колони и редове, може би различни години, месеци)). Инстинктивно отговорих, че бих направила анализа с Vlookup... И се оказа вярно, но след срещата, опитах и всъщност не знам как да го направя с функцията Vlookup.

    Olga:

    И аз да допълня въпроса: имам таблица с ЕГН на длъжници. Трябва да извадя от друга таблица със всички клиенти съвпадащите по ЕГН, техните данни по кредита от целия ред

  • Краси Кръстев:

    Здравей, 


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

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

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

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

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


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

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

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



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




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

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


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

    Evelina Kavalova:

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

    Краси Кръстев:

    Добре, искаш да кажеш че е въвел данни само и единствено в полето 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.


    Evelina Kavalova:

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

    Evelina Kavalova:

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

    Краси Кръстев:

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

    Evelina Kavalova:

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

    Соня Димитрова:

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

    Краси Кръстев:

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

    Никола Петров:

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

    Лек ден!

    Никола Петров:

    Лек ден!

    Kocho:

    Формулата =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)

  • Защо вместо резултата в клетката ми излиза формулата след като натисна Enter?

    Милена Цанкова:

    Защо вместо резултата в клетката ми излиза формулата след като натисна Enter (за подробности вижте прикаченото изображение)?

    Краси Кръстев:

    Виж да не би проблема да идва от това че си включила Show Formulas - Ctrl + ~


    За по - ясна представа, прегледай този урок от 4':13" натам и веднага ще се сетиш какво имам предвид:

    http://aula.bg/Excel-rabota-s-formuli-v-excel#00:04:13
  • Как работи VLOOKUP с вградена CHOOSE функция

    Мая:

    Защо не работи формулата VLOOKUP с вградена CHOOSE функция, както съм показала в приложения файл?

    Силвия Данаилова:

    Здравей, Мая,

    Такава формула (Array) трябва да се въведе с Ctrl + Shift + Enter, а не само с Enter. Ако това не е причината за проблема, най-вероятно е разделителя в къдравите скоби - опитай с \ вместо с ; (с две думи - разделите ; \ , в къдрави скоби се интерпретират по различен начин от регионалните настройки).

    =VLOOKUP(G2&H2&I2;CHOOSE({1\2};$A$2:$A$5&$B$2:$B$5&$C$2:$C$5;$D$2:$D$5);2;0)

    и натисни Ctrl + Shift + Enter. Във формула бара ще се вижда:

    {=VLOOKUP(G2&H2&I2;CHOOSE({1\2};$A$2:$A$5&$B$2:$B$5&$C$2:$C$5;$D$2:$D$5);2;0)}

    Array формулите са доста капризни, развалят се много лесно. Всеки път, когато си в режим на редакция трябва да се натиска Ctrl + Shift + Enter, освен ако не се излезе от него с Esc.

    Аз предпочитам формули, които не изискват Ctrl + Shift + Enter. В случая такава е:

    =INDEX($D$2:$D$5;MATCH(G2&H2&I2;INDEX($A$2:$A$5&$B$2:$B$5&$C$2:$C$5;);0))

    Надявам се да съм успяла да помогна!

    Мая:

    Да, получи се, много благодаря! :)

  • Как да позквам Vlookup заедно със Sum?

    Arif Kozumali:

    Как да задам търсене по определен критерий в дадена колона (vlookup) и намерените стойности директно да ги сумира. .

    Краси Кръстев:

    Здравей, Ариф.

    Може да изпозлваш SUMIF. Не знам какъв ти е конкретния пример, но съм на 90% сигурен че функцията SUMIF, би ти свършила работа т.к. е една от най-често приложимите функции що се касае до сумиране на по даден критерии.

    Как работи функцията?

    Например искам да събера всички клетки (числа) в колона C (снимка 1 позиция 1), които обаче стойности са на един ред с името Иван (снимка 1 позиция 2), т.е. всички стойности оцветени в жълто.

    Снимка_1

    Как би изглеждала формулата в такъв случай?

    =SUMIF(A2:A15;"Иван";C2:C15)

    • A2:A15 - обхвата в който да се търси критерия

    • "Иван" - критерия. В случая е записано "Иван" в кавички, може да си го написал в клетка и да направиш абсолютна препратка към тази клетка.

    • C2:C15 - обхвата от който да прави сумирането на база критерия.

    Снимка_2

    На снимка 3 е резултатът.

    Снимка_3

    Надявам се да ти влезне в употреба, функцията.

    Arif Kozumali:

    Здравей Краси, Благодаря много за изчерпателния отговор. Точно това е което исках да разбера, вече ми влезе в употреба.

    Краси Кръстев:

    Супер, радвам се!

    Никола Петров:

    Ето това е абсолютната референция от името Иван, тя е осветена в зелено и е със знака за долар - $H$3 в този случай. Стрелка 3 показва резултатът. Краси, благодаря! Лек ден и успехи!

    ренета николаева мир4ева:

    Благодаря!!! Информацията е полезна.

    Никола Петров:

    Функцията SUMIFS е по-гъвкава и позволява сумиране по няколко критерия. Или това: Лек ден и успехи!

  • Има ли друга подобна функция на vlookup, която търси данни и извлича данни?

    Asen:

    Има ли друга подобна функция на vlookup, която търси данни и извлича данни?

    Силвия Данаилова:

    Здравейте, да има. Например, ако не искате да се ограничавате до търсене по най-лявата колона, може да се използва комбинация от функциите INDEX и MATCH. На вас за какво по-конкретно ви трябва?

    Никола Петров:

    Ето два примера: 1 Стрелки 4 и 5 сочат мястото, където е направена Data Validation 2 Тук стрелки от 1 до 4 сочат мястото на Data Valdation Промяната на стойност в клетка с Data Valdation премества оцветяването на колона и ред и оцветява пресечната клетка. Дано естанало ясно! Лек ден и успехи!

    И още един пример: Всички данни са измислени! Лек ден и успехи

    Никола Петров:

    И още един пример: Всички данни са измислени! Лек ден и успехи

    Никола Петров:

    Новите стойности след избор на други стойности от Data Vaidation Лек ден и успехи!

  • Защо само по име ли можем да търсим данни от базовият списък на таблицата с данни???? Можем ли примерно от да търсим данни от друг показател на таблицата с базови данни?

    Николай Ганев:

    Защо само по име ли можем да търсим данни от таблицата с базови данни? Може ли да търсим и по друг показател от базовите данни на таблицата?

    Стефка  Тодорова:

    Здравейте, функцията VLOOKUP търси на база зададен текст в една таблица с данни и ни връща резултата, който искаме. Ние задаваме във формулата =VLOOKUP(LOOKUP_VALUE;TABLE_ARRAY;COL_INDEX_NUM;[RANGE_LOOKUP]) ключа ,по който функцията да търси.Този показател може да е име на физическо лице или юридическо наименование на фирма (за фактура например), или нещо друго.

  • Дарина Иванова:

    Всяка функция има определен брой параметри (задължителни и незадължителни). VLOOKUP има 4 параметъра, както се вижда от общият й вид. За всяка функция можем да видим нейното действие (за съжаление на английски език), когато е избрана, както и пояснения за всеки параметър. Ако искаме да научим повече за нея можем да изберем HELP.

  • Как можем да ползваме VLOOKUP още по-интерактивно?

    Atanas Oshavkov:

    Здравейте. Можем ли да направим клетките още по-свързани и интерактивни? В примера при извикване на името автоматично се появява телефон, личен номер и кост център.Как може не само при извикването на име да ми излизат тел, номер и кост център, но и при извикване на телефон да ми излизат име, номер и кост център, а при извикване на номер да ми излизат име, тел. и кост център? Excel 2010

    Kocho:

    По принцип става много лесно с 4 Combo box-а, които командват една и съща клетка. Тогава каквото и да изберем в някой от тях, автоматично се попълват другите със съответните стойности.

    Каква е целта на въпроса обаче? Какво искате да постигнете

    Atanas Oshavkov:

    Ако имаме 4 или повече свързани данни, да мога да търся по всеки един вид от тези данни. Търся по телефон- получавам име, личен номер и кост център. Решавам да търся по личен номер, излиза ми име, телефон и кост център и т.н. Как работи този Combo box?

    Никола Петров:

    Трябва да имате активиран Developer. Ако го нямате , ето от къде може да го направите. Отваряме Developer, избираме бутона Insert и от прозореца избираме Combo Box Както е посочил колегата Kocho(благодарности за решението!), най-важно е да изберем ЕДНА И СЪЩА КЛЕТКА за Cell Link! Повтаряме Format Control за останалите Combo Box-a и при избор на КОЙТО И ДА Е Combo Box, данните в другите се променят, а в Cell Link-а се вижда на кой № по ред сме! Още веднъж благодарност за колегата Kocho! Лек ден и дано е това, което се търси! Успехи!

    Никола Петров:

    На първата снимка - за да се появи Developer(стрелка 4), трябва да натиснем ОК - мисля, че то се подразбира. Когато почнем да чертаем, може да натиснем бутона Alt и курсорът ще се прилепва(SNAP) към мрежата! Лек ден!

    Kocho:

    Аз само да допълня да се внимава с критерия за търсене. Примерно личният номер и ЕГН-то са уникални, но името и кост центъра - не са.

    Atanas Oshavkov:

    Благодаря, но не се получава. След като начертая произволен правоъгълник, какво трябва да направя? Защото в менюто на Format Control имам само четирите подопции, без Control. Подозирам че нещо пропускаме в тази последователност? Благодаря

    Никола Петров:

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

  • VLOOKUP не работи! Защо?

    Stela:

    Моля ви помогнете ми! Къде греша при изпoлзване на функцията VLOOKUP? Двете таблици (от изображенията) са в 2 съседни SHEETs. Благодаря ви! (Цените които се виждат над формулата, са нанесени ръчно, но бих искала да автоматизирам действията защото доста често ми се налага да правя това...

    Краси Кръстев:

    Виж да не би да има минимална разлика между това което е записано в Splr Part No в sheet 1 и sheet 2, като под минимална разлика визирам някои допълнителен интервал преди или след числото, който не виждаш. Това е доста често срещан пропуск. От прикачениете снимки виждам, че за по-горните редове (84, 83, 82 ) формулата работи.

    Stela:

    Много благодаря за бързия отговор, инж. Кръстев! Няма разлика в формата, защото с Find (Ctrl+F) ги намира и така го правя ръчно..(Първите 5 реда на Таблица 1) А възможно ли е проблема да идва от различните версии на EXCEL на двете ми таблици(едната е от клиент с Excel 2013), а моя е 2010?

    Краси Кръстев:

    Не би трябвало ти работиш в два sheet-a които са в един и същ workbook и съответно той е отворен през една и съща версия на Excel.

    Това че ги намираш с Find, не винаги означава че всичко е наред с даден надпис.Например в един документ може да имам името Петър, обаче след това да имам и *Петър * , ако потърся с Find _Петър_ следва и двете позиции (тази с интервала и тази без интервала)

    Провери наистина дали и на две места няма някой интервал. Влез в келтките с еднакви номера и виж горе в лентата (снимка 1 позиция 1) дали нямаш интервал отпред отзад.

    Снимка_1

    Stela:

    Да, прав сте за версията на EXCEL. Благодаря! Благодаря и за идеята с интервалите, явно е това, не ги виждам тези интервали, но заставайки с мишката след номера и прес DELETE, формулата проработваше - УРА!!! Проблема беше, че трябваше да направя това за всяка клетка с арт. номер... Важното е, че проработи, благодарение на вашето компетентно мнение! Още веднъж: Благодаря Ви за помоща!

    Краси Кръстев:

    Супер, проработило е.

    Много тънък момент е празния интервал, когато става за намиране на точни стойности :)

  • Може ли да използвам тази функция при данни в различни листи?

    Мая Петрова:

    Здравейте, възможно ли е да използвам тази функция vlookup при налични данни от няколко листа? Например: имам таблица с данни за разход в лв на материал по различни формати.Цената включва няколко компонента - бр формат,бр цвят и съответно разход за различните комбинации...Като начинаещ в работата с ексел съм въвела формули и компоненти за всеки формат на различен лист... Ще приложа един вариант :

  • Как да приложа коректно функцията vlookup?

    Мая Петрова:

    Здравейте, моля за насоки. Искам да ползвам vlookup за моите таблици като колоната с форматите искам да не се променя,колоната с цветовете се променя,а от там се повлиява крайната цена.Напр.:в един лист съм направила формули за променящи се цветове и крайни разходи,но за един формат. За всеки формат съм направила тези формули,но всеки е на отделен лист.Ще приложа пример с формат 2:

    Как мога да ползвам и двете функции на отделен лист - data validation & vlookup, за да е по-лесно и прегледно за ползване на информацията.Моля за по-конкректни насоки дали мога да го направя и как да стане?

    Димитринка Вълкова:

    Най-напред да уточня, дали правилно съм разбрала въпроса. Таблицата за Vlookup има 5 колони (от картинката: размер, формат, цвят, цена1 и цена 2). За data validation избирате размер и останалите колони трябва да се попълнят с Vlookup. Ако правилно съм разбрала, няма проблем двете таблици за data validation и за Vlookup да бъдат на отделни листове. За удобство списъкът с размерите, дефинирайте като наименован диапазон. Пишете, ако имате допълнителни въпроси.