Докато да благодаря за 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).
Като се ползва този прозорец, веднага севижда какъв резултат ще се върне в клетката. Това е полезно, ако базата данни ес много колони и е трудно да се преброи от кой номер колона ще връща резултата, а по отговора може да се ориентирам дали съм въвела правилната колона.
Благодаря, наистина е много лесно по този начин!
Пробвах и data validation и table_array да маркирам с цели колони. Супер е. Всичко се получава без да има нужда от актуализиране в name manager.
Въпрос към Весела: как се отваря този прозорец? при мен при търсене на функцията , VLOOKUP я няма.Работя с версия 2010
появява се този прозорец и функцията VLOOKUP липсва
Здравей, Теменужка.
Благодаря.Доста се поизмъчих. В отговора ти липсваше това :долния прозорец ( снимка 1 позиция 4 ) .Докато загрея ,че това V трябва да натисна на клавиатурата, еле справих се.Още веднъж благодаря.
В прозореца Insert Function, заложената категория функции е Most resently used (последно използвани) и ако не е била използвана тази функция, тя не може да бъде в изброените по-долу. Може да изберем функцията Vlookup или от категория All или Lookup & Reference.
@Теменужка, добре че каза позицията, да така една позиция съм сложил повече реално 4 е 3, но сега вече го оправих да е по - разбираемо за другите.Радвам се че си се ориентирала и съответно ти е помогнал отговора.
Функцията наистина е много интересна и предлага много възможности. Благодаря на всички за споделения опит! Невероятни сте!
Вариянтът с функцията дава още по-голяма яснота, за което благодаря.
Може ли само да ми потвърдите още веднъж дали наистина е ок при въвеждането на table_array да се използват само колоните?Евентуални празни клетки не чупят ли нещата? Не разбирам също какво точно означава, че "така не се налага, когато дописвате данни в table_arra да актуализирате Data Validation"? Ако има вариант и списъка на падащото меню да се самоактуализира при допълване на базата данни, моля споделете:) Казусът ми е от коментарите въс форума по-долу.Благодаря за разяснението
Какво е...
Натъкнах се на следния проблем. Имам един въпрос за функцията VLOOKUP, която се опитах да напиша във въпросника, но ми дава, че е прекалено дълъг текст, така беше, но дори когато го смалих до две изречения пак не ми дава да си публикувам въпроса и това ми се струва доста неудобно! Както и да е, В една фирмена работна таблица имаме формулата: =+VLOOKUP(H3;$Z$2:$AB$11;3;TRUE)
Тя е предназначена да попълни данни от изминати километри на даден шофьор и да му покаже какъв бонус му се полага спрямо тях!
Пример м/у 12001 и 12500км 300лв. бонус 12501 и 13000км 400лв. бонус 13001 и 13500км 500лв. бонус H3 взима изминатите км. после взима диапазона $Z$2:$AB$11 и после от колона 3 казва, че трябва да вземе 300лв. бонус и накрая с TRUE
Въпроса ми е, каква е ролята на + в този случай и защо като дам False ми се появява грешка?
Поздрави, Борислав Белев
За последния аргумент на функцията VLOOKUP може да задаваме само логически стойности True или False. True извлича най-близката стойност от даден диапазон (както е във Вашия пример). False извлича точната стойност (Exact Value) за търсения критерий. Ето защо получавате грешка, когато изберете False, липсва точно съответствие.
Много хубав пример за първоначален сблъсък с Vlookup.
От средата на 2020 има доста по-удобна, функционална и лесна формула, която на практика направи VLOOKUP отживелица - това е формулата XLOOKUP. Синтаксиса ѝ е следния:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Чрез тази формула няма нужда да задавате table_array, трябват ви само търсената стойност (lookup_value) и 2 колони - тази, в която търсите съвпадение (lookup_array) и тази, която искате да ви върне при съвпадение (return_array) . Особено удобна е и за използване с таблици, защото директно можете да използвате наименованията на колоните от таблицата.
Като бонус, няма нужда да се въвежда FALSE или 0, направено е по подразбиране да търси точна стойност ([match_mode]=0).
Като допълнителен бонус, може да зададете какво да ви покаже, ако не намери никаква стойност, вместо да ви даде #N/A. Достатъчно е да зададете стойност в [if_not_found].
Изобщо не съм съгласен, че VLOOKUP е отживелица. ХLOOKUP е хубава и много по-гъвкава формула от VLOOKUP, но ... налична само за Ексел 365 (вече и за Ексел 2021). Огромна част от потребителите у нас все още работят на версии като 2016, 2013, 2010. Дори и на 2003!
Да не говорим, че има сигурно милиони файлове по различни фирми и организации, в които се използва VLOOKUP. Тези файлове все още са в употреба и подмяната на VLOOKUP с ХLOOKUP ще бъде ненужно пилеене на време, а от там и на пари. Следователно VLOOKUP още дълго ще си съществува и използва и всеки, който работи интензивно с Ексел трябва да познава и за използва свободно тази формула. Така както например все още се използва SUMIF, въпреки че SUMIFS, която е много по-удобна и лесна за употреба, беше въведена в Ексел 2010.
Съгласен съм, че VLOOKUP ще се ползва още доста дълго време, респективно всеки трябва да е запознат с формулата, но това не променя написаното от мен. Както XLOOKUP, така и всички други нови функции в Office 2021 и Office 365 облекчават живота на всеки, който може да ги използва. По-специално визирам функциите FILTER, SORT, UNIQUE и всички останали динамични формули, за които трябваше да се ползват далеч по-сложни заместители.
Зависи какво се разбира под облекчава. Аз започнах ентусиазирано да ги използвам, когато ми се появиха. Наистина са удобни. Обаче пращам един отчет на шефа и той ме вика след 5 минути да му обясня какво точно да гледа на празен екран :). Причината - той работи с версия 2019, където ги няма. Айде преправяй всичко наново по най-бързия начин
При 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
Tова е нещо уникално. Много благодаря. Аз също тъпча цялата информация в една обща база с данни и от там дърпам това, което ми е необходимо за различните справки. Много по-лесно е от колкото да се правят различни таблици и накрая се загубваш в тях. Още веднъж благодаря за този урок. Ще ми е от голяма полза.
И при функция VLOOKUP се получава ,ако се изберат всички колони =VLOOKUP(C5;Данни!A:D;3;false)
Аз се опитвам да направя упражнението дума по дума или с този израз=VLOOKUP('Отчет-шаблон'!A1:D9,2,FALSE),но версия 2010 ми дава,че има грешка и резултат нула.Пробвах с точка и запетая";" ,пробвах с тве точки":"не се получава по никой начин.Как да се справя?
Здравейте, Росица! Правилният израз е ето този : =VLOOKUP(C5,Данни!A1:D9,2,FALSE) . Първият критерий на функцията трябва да е в първа колона, уникален и да се посочи като име на клетка, а не като таблица както е при вас.
Росица, при мен също се получаваше нула докато не забелязах, че по подразбиране е избран последния ред, в който беше допълнено ново име, но срещу името няма данни за телефон и т.н. След като избрах който и да е друг от списъка (Ана, Георги и т.н.) и повторих, се получи без проблем.
Лек ден и успехи!
6+
Ето една таблица, в която са събрани двете функции HLOOKUP и VLOOKUP.
Здравейте, използвах формулата, за друга моя си бланка, които данни са от 17 колони, до 16 колона работи чудесно, но не мога да разбера защо на последната клетка, която по данните е 17 колона, ми излиза грешка?
Искам само да допълня, че съм с версия 2013.
Изпрати ми файла по имейл и ще видим от къде идва проблема.
Здравейте, и за много години с пожелания за всичко най-хубаво!
Ами, аз на ново си направих файла и вече не ми отчита грешката... А изобщо не ми дойде до акъла да запазя файла с грешката и да Ви го изпратя, и сега съжалявам, защото все още съм любопитна как се беше получило. Иначе дълго сменях опции, проследявах стъпките, формулата ясно се виждаше, че е правилно написана, бях филтрирала данните, реших, че може да е от филтъра, но не е и от него, гледах формата на клетките... доста "чоплих" по всевъзможните опции и накрая се вбесих и го направих на ново. Файла не е нещо особено, но го създавам за последваща обработка и попълване на данни. Знам ли, може пак да покаже грешка, за това ще Ви го изпратя да го видите.
ето снимка
изображение на sheet клиенти от където съм взел имената за data validation
версия на excel 2010
За Александър Йорданов. Доколкото виждам от изображението в таблицата с база данни не е включено името посочено във формулата за B6. Според мен ще трябва да се допълни базата данни с още една колона включваща имената на "Получател".
има колона ето я Колона F
Освен да се създаде нов файл нямам друга идея. Според това което виждам е правилно.
Здравей Alexander, за да работи коректно функцията VLOOKUP, колоната, от която взема данните, трябва да е първа. Просто я изрежи (cut) и премести (paste) като колона "А". Аз така успях да реша този проблем.
Има ли начин с VLOOKUP да се извадят от таблица, в която има критерий, който се повтаря? Например, едно населено място в България го има в 4 региона и има различни пощенски кодове. Ползвам VLOOKUP, за да ги извадя, но той хваща винаги първото и на следващите слага пощенския код на първия.
Според мен имаш два начина да се справиш с проблема. Първият е като промениш търсенето, не на населено място, а по пощенски код. Втория е да зададеш на дублираните населени места допълнителна номерация ( или нещо специфично само за това населено място ) и да го търсиш чрез него.
Така май пак си е трудоемко. Има населени места, които са 8 с едно и също име. Иска ми се да не ги "цъкам" едно по едно . Целта ми е да сложа точния пощенски код на съответния регион.
Съжалявам, но без да видя поне примерна таблица, не мога да помогна. Може би първо трябва да ги селектираш по област и след това по населено място.
Здравейте, аз също имам проблем с повтарящи се данни.... Има ли опция след извършване на Vlookup да може да се избират данните отново от падащ списък? В смисъл: Избираме от един списък определена информация, използваме Vlookup и в резултата да може да се избира точно каквото ни е необходимо...
@ Георги
Това мисля, че по-лесно би се получило с 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
@ Георги,
Здравейте имам следният проблем:
@ Георги
Това може би, по-лесно ще стане с 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 и как работят те. Може ли да ни дадете по-разбираем пример. Благодаря много предварително!
Добре мисля да дообесня горния пример защото е доста семпъл и ако дам по - голям пример следва да е още по объркващо.
Задачата е : в клетка 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 от диапзона ми запиши стойността която отговаря на ред на който пише Бургас.
Така стана ли по - ясно или трябва още малко пояснение.
Лек ден и успехи!
Здравейте, Може ли да извадят различни стойности на дублрани критерии.? Пример: в sheet 1 - колона A има номера на Договори (като има дублирани номера на договори) и в колона B различни суми за всеки договор (различни суми и за дублираните договори). В Sheet 2 има списък с Договори, за които трябва да се извадят отговарящите суми от sheet 1. С Vlookup не става, защото на дублираните договори вади първия резултат, които си намери. С pivot table сумира. И до тук ми се изчерпаха вариянтите. Благодаря предварително за съдействието!
Как да направя търсенето без да дава грешка? сн.1-база данни, сн.2 опит за vlookup. Искам да сложа номера на всеки магазин след адреса или преди него.
Здравей, Stasi.
Да, схванах логиката. Днес си поиграх малко повече и като копирах номерата на магазините след адреса се получи. Тогава схванах, че excel брои първа колона, от която се търси и в дясно от нея колоните се номерират 2, 3 , и т.н. Ще опитам и Index и Match
Бихте ли ми казали , кой е урокът с Index и Match ? Потърсих , но не можах да го открия
Г-н Кръстев, говорите за Index и Match. Можете ли да ни покажете как работят тези функции, моля?
@Соня Димитрова - тук : линк има един коментар в който съм дал разяснение за Index Match, ако не стане ясно пишете под темата в която Ви препратих ще дообесня.
Лек ден и успехи!
Защо при прилагане на формулата в работния файл, излиза грешка (работя с Office 2011 за Мак). Има ли вариант това да бъде коригирано или е необходимо колоната, от която искаме данните да бъдат взети да бъде винаги първа? Не значи ли това, че променяйки отново колоните за следващия критерий (фирмен номер), и тази формула ще даде грешка поради промяна в референтните колони?
Лиляна, дай моля те конкретен пример, за да мога да ти помогна.
Използвам примера от урока, като въвеждам формулата, за да излезе телефонния номер на съответното лице (Ани Петрова) като взимам данните за телефонния и номер от от лист 2 "Данни", но Excel разпознава формулата.
Това е единият проблем:
Ето и вторият проблем:
Здравей, Лиляна.
Мила е решила проблема ви, но ето едно видео, което ще ви е от помощ следващия път ---> https://www.youtube.com/watch?v=2oySQVVdc44
Относно втория проблем - какъв е вторият проблем според вас?
2013
За да разбера по-добре въпроса, имам един уточняващ въпрос:
Благодаря за въпросът Ви инж. Цукев. Зададоха ми следният въпрос по време на интервю: Как бихте направили анализ на продажбите от 2 различни файла (предполагам, че е с еднакви параметри по колони и редове, може би различни години, месеци)). Инстинктивно отговорих, че бих направила анализа с Vlookup... И се оказа вярно, но след срещата, опитах и всъщност не знам как да го направя с функцията Vlookup.
И аз да допълня въпроса: имам таблица с ЕГН на длъжници. Трябва да извадя от друга таблица със всички клиенти съвпадащите по ЕГН, техните данни по кредита от целия ред
Здравей,
Благодаря много за отговора , проблемът беше , че по време на изпита , аз предложих веднага да разтегля формулата като променя диапазона , но изпитващия категорично ми каза без дърпане на формулата ??? Аз не се сетих, и той тогава въведе допълнително данни в поле Col_index_num , но не успях да видя точно как ги въведе !
Добре, искаш да кажеш че е въвел данни само и единствено в полето col_index_num и формулата сама се е прехвърлила ( без да се раздърпва или копира в съседните колони в office phone и cost center )
Благодаря много за изчерпателния отговор ! Ще отработя предложението ! Предполагам , че има няколко решения въпроса , но условието беше зададено конкретно , което ме подлуди докато не намеря правилното решение ! Още веднъж благодаря !
Отработих примера и се получи , благодаря ! Само един въпрос относно клетка L13 , произволно ли я избираме и после извъждаме разликата до която ни е необходимо или .......?
От клетка L13 ни е необходим само и само и единствено за да вземем номера на колоната й като стойност... т.е може да е и L14, L15 и т.н. стойността като число която взимаме благодарение на фукнцията Column е една и съща = 12 .
ОК , благодаря !
Г-н Кръстев, говорите за Index и Match. Можете ли да ни покажете как работят тези функции, моля?
@Соня Димитрова - тук : линк има един коментар в който съм дал разяснение за Index Match, ако не стане ясно пишете под темата в която Ви препратих ще дообесня.
Лек ден!
Формулата =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)
И за едновременното въвеждане на формулата, маркирали сме областта, в която трябва да се въведе и завършваме въвеждането като натиснем Ctrl+Enter. Относно номера на колонката, най лесно е предварително да въведем номерацията на колонките и използваме адресите на тези клетки.
А таблицата с данните преобразувате в таблица (Ctrl+T) и използвате нейното име. Ако не е съвсем ясно пишете.
Защо вместо резултата в клетката ми излиза формулата след като натисна Enter (за подробности вижте прикаченото изображение)?
Виж да не би проблема да идва от това че си включила Show Formulas - Ctrl + ~
Защо не работи формулата 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) и намерените стойности директно да ги сумира. .
Здравей, Ариф.
Може да изпозлваш SUMIF. Не знам какъв ти е конкретния пример, но съм на 90% сигурен че функцията SUMIF, би ти свършила работа т.к. е една от най-често приложимите функции що се касае до сумиране на по даден критерии.
Как работи функцията?
Например искам да събера всички клетки (числа) в колона C (снимка 1 позиция 1), които обаче стойности са на един ред с името Иван (снимка 1 позиция 2), т.е. всички стойности оцветени в жълто.
Как би изглеждала формулата в такъв случай?
=SUMIF(A2:A15;"Иван";C2:C15)
A2:A15 - обхвата в който да се търси критерия
"Иван" - критерия. В случая е записано "Иван" в кавички, може да си го написал в клетка и да направиш абсолютна препратка към тази клетка.
C2:C15 - обхвата от който да прави сумирането на база критерия.
На снимка 3 е резултатът.
Надявам се да ти влезне в употреба, функцията.
Здравей Краси, Благодаря много за изчерпателния отговор. Точно това е което исках да разбера, вече ми влезе в употреба.
Супер, радвам се!
Ето това е абсолютната референция от името Иван, тя е осветена в зелено и е със знака за долар - $H$3 в този случай.
Стрелка 3 показва резултатът. Краси, благодаря!
Лек ден и успехи!
Благодаря!!! Информацията е полезна.
Функцията SUMIFS е по-гъвкава и позволява сумиране по няколко критерия.
Или това:
Лек ден и успехи!
Има ли друга подобна функция на 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(C5;Данни!А2;D10;2+3+4+5;false
Всяка функция има определен брой параметри (задължителни и незадължителни). VLOOKUP има 4 параметъра, както се вижда от общият й вид. За всяка функция можем да видим нейното действие (за съжаление на английски език), когато е избрана, както и пояснения за всеки параметър. Ако искаме да научим повече за нея можем да изберем HELP.
Здравейте. Можем ли да направим клетките още по-свързани и интерактивни? В примера при извикване на името автоматично се появява телефон, личен номер и кост център.Как може не само при извикването на име да ми излизат тел, номер и кост център, но и при извикване на телефон да ми излизат име, номер и кост център, а при извикване на номер да ми излизат име, тел. и кост център? Excel 2010
По принцип става много лесно с 4 Combo box-а, които командват една и съща клетка. Тогава каквото и да изберем в някой от тях, автоматично се попълват другите със съответните стойности.
Каква е целта на въпроса обаче? Какво искате да постигнете
Ако имаме 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) към мрежата! Лек ден!
Аз само да допълня да се внимава с критерия за търсене. Примерно личният номер и ЕГН-то са уникални, но името и кост центъра - не са.
Благодаря, но не се получава. След като начертая произволен правоъгълник, какво трябва да направя? Защото в менюто на Format Control имам само четирите подопции, без Control. Подозирам че нещо пропускаме в тази последователност? Благодаря
Лек де и успехи!
Моля ви помогнете ми! Къде греша при изпoлзване на функцията VLOOKUP? Двете таблици (от изображенията) са в 2 съседни SHEETs. Благодаря ви! (Цените които се виждат над формулата, са нанесени ръчно, но бих искала да автоматизирам действията защото доста често ми се налага да правя това...
Виж да не би да има минимална разлика между това което е записано в Splr Part No в sheet 1 и sheet 2, като под минимална разлика визирам някои допълнителен интервал преди или след числото, който не виждаш. Това е доста често срещан пропуск. От прикачениете снимки виждам, че за по-горните редове (84, 83, 82 ) формулата работи.
Много благодаря за бързия отговор, инж. Кръстев! Няма разлика в формата, защото с Find (Ctrl+F) ги намира и така го правя ръчно..(Първите 5 реда на Таблица 1) А възможно ли е проблема да идва от различните версии на EXCEL на двете ми таблици(едната е от клиент с Excel 2013), а моя е 2010?
Не би трябвало ти работиш в два sheet-a които са в един и същ workbook и съответно той е отворен през една и съща версия на Excel.
Това че ги намираш с Find, не винаги означава че всичко е наред с даден надпис.Например в един документ може да имам името Петър, обаче след това да имам и *Петър * , ако потърся с Find _Петър_ следва и двете позиции (тази с интервала и тази без интервала)
Провери наистина дали и на две места няма някой интервал. Влез в келтките с еднакви номера и виж горе в лентата (снимка 1 позиция 1) дали нямаш интервал отпред отзад.
Да, прав сте за версията на EXCEL. Благодаря! Благодаря и за идеята с интервалите, явно е това, не ги виждам тези интервали, но заставайки с мишката след номера и прес DELETE, формулата проработваше - УРА!!! Проблема беше, че трябваше да направя това за всяка клетка с арт. номер... Важното е, че проработи, благодарение на вашето компетентно мнение! Още веднъж: Благодаря Ви за помоща!
Супер, проработило е.
Много тънък момент е празния интервал, когато става за намиране на точни стойности :)
Здравейте,
възможно ли е да използвам тази функция vlookup при налични данни от няколко листа? Например: имам таблица с данни за разход в лв на материал по различни формати.Цената включва няколко компонента - бр формат,бр цвят и съответно разход за различните комбинации...Като начинаещ в работата с ексел съм въвела формули и компоненти за всеки формат на различен лист... Ще приложа един вариант :
да
Здравейте, бихте ли споделили в какви случаи би било удобно да се изполва иразът "TRUE" във функцията VLOOKUP. Благодаря предварително!
Благодаря, Kocho, за отговора! Примерът е красноречив. Сега вече ми е ясно кога би било удобно да се използва TRUE. Извинявам се на Мая Петрова, че й спамя въпроса, но нещо не успях със създаването на нов!
Здравейте,
моля за насоки. Искам да ползвам vlookup за моите таблици като колоната с форматите искам да не се променя,колоната с цветовете се променя,а от там се повлиява крайната цена.Напр.:в един лист съм направила формули за променящи се цветове и крайни разходи,но за един формат. За всеки формат съм направила тези формули,но всеки е на отделен лист.Ще приложа пример с формат 2:
Как мога да ползвам и двете функции на отделен лист - data validation & vlookup, за да е по-лесно и прегледно за ползване на информацията.Моля за по-конкректни насоки дали мога да го направя и как да стане?
Най-напред да уточня, дали правилно съм разбрала въпроса. Таблицата за Vlookup има 5 колони (от картинката: размер, формат, цвят, цена1 и цена 2). За data validation избирате размер и останалите колони трябва да се попълнят с Vlookup. Ако правилно съм разбрала, няма проблем двете таблици за data validation и за Vlookup да бъдат на отделни листове. За удобство списъкът с размерите, дефинирайте като наименован диапазон. Пишете, ако имате допълнителни въпроси.
Виждала съм го преди доста години това, но не знам как би станало, мисля, че имаше if и vlookup беше с някаква дълга формула, но може да има и по-лесен начин :) Благодаря предварително :)
Първо, трябва да има таблица с всички данни на лицето. А след това се използва функцията vlookup (или hlookup - в зависимост от това, как се търси дадена стойност - вертикално или хоризонтално). Например, =vlookup(Value;table, col_num;0). Тук, value е името, table е таблица с данни за лицето, col_num е номерът на колоната за посочения елемент - например, колоната с телефони. 0 - е търсене по точно съвпадение или False. Ако се пропусне този елемент и таблицата не е сортирана в определен ред, формулата няма да работи. True, в същност се използва, когато търсим в който точно интервал попада дадена стойност.
Здравей Tatiana и Благодаря за отговора. Взела съм файла от урока, таблица има, от нея съм направила падащото меню. С vlookup се оправих, но да кажем когато изпиша само част от името ми дава N/A като резултати. Аз бих искала когато напиша каквото и да е име, което фигурира в таблицата, ако да кажем не знам коя е фамилията, да ми излизат данните за него или подсказка какви фамилии има с името Ани :)
Благодаря, Kocho, направих всичко от примера, но нещо не ми се получи всичко... :)
Кое по-точно не се получава?
За да излезе фамилията, то трябва името и фамилията да са в различни колони. От примера виждам, че името и фамилията са в една колона и затова няма точно съвпадение с функцията VLOOKUP, т.е. получаваме грешката N/A. Може да се разделят името и фамилията в отделни колони от менюто Data, Text to Columns.
Чудесна идея! Благодаря, за което! Но ако да кажем в списъка има няколко Ани с различни фамилии тогава как не знам как ще излезнах нещата :)
Да, ако има еднакви имена, няма да се получи, ще взема винаги първо срещнатото. Друго, което си мисля е да се добави нов ред с номер и да се търси по номер
или което предлага Кочо с Dependent lists
Ако таблицата се сортира преди да се извърши търсенето, нещата ще се облекчат.
И това е чудесна идея, но ако да кажем са над 500 записа тогава с номера не знам как биха се получили нещата... :)
Как броят може д апопречи на сортирането? Би могло да се зададе втори ключ за сортиране при повторение в едната колона, да се зададе втора колона като втори критерий за сортиране, така областта от еднакви данни ще се подраздели на други няколко.
Добра идея, трябва само някак си да я приложа в табличен вид, че не ми стана ясно на практика как би станало :)
Задаването на втори критерий се прави по показания начин, но според мен това сортиране трябва да предхожда другите функции.
Може ли функцията да се използва при вградени таблици? При мен поне не се получава.
Здравей, Климент.
Пролбема по-скоро идва от това че
или в листа " данни " от който искате да вземете информация няма Доставчик с име "доставчик" и съответно като няма такава стойност ви изписва N/A . В този случай прегледайте много добре дали записа в базовата таблица (тази в която търсите) отговаря на записа в таблицата в която записвате стойност посредством vlookup формулата.
или другия вариант за проблем е в листа "данни" колоната "доставчик" да НЕ е първата колона от направения избор за Table array - данни!A3:C9 . Имам предвид че в случая ти търсиш за да намериш записа в C4 - "доставчик" (lookup value) в диапазона данни!A3:C9 (table array), то следва да " доставчик " от листа в който го търсиш да е в първата колона на данни!A3:C9, т.е. в данни!A3:C9 колона A трябва да съдържа някъде по редовете записа - " доставчик " . В този случай ако " доставчик е от втората колона в лист данни, то направете интервала - данни!B3:C9 . Това е едно най-неприятно условие при използване на Vlookup - търсената стойност Lookup value, винаги се търси в първата колона на избрания дипазон - table array.
Благодаря! Това от снимката е само пример за да мога да покажа каква е грешката. В моят случай колоните с данни не са две, а пет. Т.е. не мога да задам данни!B3:C9, тъй като в колона А също имам стойност, която трябва да включа. Проблемът се реши като подредих колоните с имената на стойностите да са идентични и в двата листа.
Супер е че проблема е разрешен и си споделил какво точно е проработило в твоя случай.
В бъдеще не се притеснявай да качваш снимка с конкретния казус, който се е появил при теб, освен ако нещата са "строго" съкретни , но пак може да видоизмениш данните във файла и така ще имаш и по-конкретен отговор.
Въпросът ми е подобен на въпроса от миналият урок. Тук в функцията vlookup, явно може да се зададе таблица, предполагам защото го възприема като масив, нo ,,col_index_num" не успя да го възприеме като колона от таблицата. Мисля, че е защото задължително трябва да е число равно поредният номер на колоната на масива като чете от ляво на дясно броят избрани колони, но все пак има ли начин да е името на колоната от таблицата?
каква ти е целта, друже? Какво се опитваш да направиш
Искам вместо числото 2,3, ... да е името на колоната от таблицата.
Ивайло, ето едно предложение:
Надявам се да съм помогнал!
Лек ден и успехи!
Друго предложение с използването на нещо полезно и рядко използвано - Create from Selection:
Лек ден и успехи!
Ето това целях да направя. Цел ясна визия от коя колона взимаме информацията.
Лек ден!
- форматираме данните като таблица - Ctrl+T (1)
- добавяме помощен ред с номерация на колоните (2)
- във формулата адресът на клетката с номера на колоната трябва да относителен по колона и постоянен по ред - В$1 (3)
- ако сме маркирали цялата област, в която ще въвеждаме формулата, въвеждаме я и завършваме с Ctrl+Enter, ще въведем формулата едновременно в цялата област.
Зддравей, Гергана.
Селектирай C5, след което избери разделя на аргументи във функцията, може да е ; или , след което ще можеш да избереш втория аргумент от функцията който е обхвата от A2 до D20 на лист 2.
В момента получаваш това съобщение, защото първо избираш C5, обаче след това при избрането на обхвата, съотвенто не използваш разделителя и обхвата се записва на мястото на C5, ОБАЧЕ първия аргумент на функцията не изисква обхват ами конкретна клетка ... и затова ти се появява съобщението, което си показал на сниката. Ползвай разделители за да въввеждаш всеки един от аргументите на даден функция.
Получи се много благодаря за помощта.
Гергана, при изписването на функцията се появява подсказка с агументите на функцията и
най-важното - разделителите между аргументите! Така ще си улесните работата и няма опасност да сгрешите или да пропуснете нещо! Лек ден и успехи!
най-важното - разделителите между аргументите! Така ще си улесните работата и няма опасност да сгрешите или да пропуснете нещо! Лек ден и успехи!
И още нещо:
Лек ден и успехи!
Защо ми изписва грешка? С версия 2016 на чешки език съм и правя всичко според указанията във видеото, но ми се получава като резултат #NENÍ_K_DISPOZICI(не е налично). Къде греша?
Здравей, Десислава.
Ако мога да те помоля за един sreenshot, който да включва и зиползваната формула и малко повече информация за това каквот точно искаш да направиш.
В момента предположенията ми са че анй-вероятно
ИЛИ търсиш нещо не съществува във Vlookup
ИЛИ не си заключила диапазона в който формулата Vlookup търси дадената стойност и така при разтеглянето на формулата в редовете в който диапзона се е изместил достатъчно че не съдържа клетки със запис, съответно изписва #N/A.
Малко повече информация за това къде точно прилагаш формулата и ще можем да дадем по-конкретно решение.
Ако не е проблем изпрати файла, така както ти дава проблем, на kr_krastev@abv.bg за да го погледна и да ти пиша. Ако пращаш имейл ако мога да те помоля, само напомни в имейла за какво става дума като проблем за да се сетя.
Здравейте! Не зная дали е коментирано в уроците или тук във форума, но имайте предвид, че функцията "vlookup" не търси наляво. Ако въведете аргументите така, че да търсите резултат вляво, няма да получите съобщение за грешка на въведената формула (като затворите скобата и натиснете enter), но винаги ще имате резултат "#N/A", макар че формулата изглежда (и всъщност е) правилна. Просто това е особеност/ограничение на тази функция. Ако се опитвате да направите нещо такова, ще трябва да използвате функциите index+match - комбинацията им е универсално решение на много проблеми.
Здравейте, как мога да обединя 2 файла с еднакви критерии? И в двата файла се съдържат едни и същи данни за клиенти, но в единия файл има допълнени клиенти, които липсват във втория файл. По какъв начин мога да обединя файловете така, че новите клиенти от единия файл да се прибавят към другия файл?
Александра, една снимка на екрана би дала повече възможности за отговор, но ето едно предложение: ако данните са във табличен вид, то маркирайте коя и да е клетка от първата таблица и натиснете комбинацията Ctrl+T. Ще се отвори прозорецът Create Table:
Отиваме на втората таблица и копираме данните, връщаме се на първата таблица и вмъкваме (Ctrl+V) данните под тези от първата таблица. Вече имаме една таблица със данни от двете таблици, събрани на едно място!
Дано да е станало ясно и да помогна!
Лек ден и успехи!
Благодаря Ви за отговора! Става въпрос за големи файлове с имена на клиенти, ЕГН-та и друга информация за тях. Файловете са големи и с Ctrl+T ще бъде доста трудно.
Друго предложение:
След натискане на Enter
Пишете дали това е помогнало, защото стана малко дълго!
Лек ден и успехи!
Здравейте,
Може ли да използвам командата vlookup, за да намеря и поставя определена формула в клетката, в която желая?
Не разбрах какво се опитвате да направите. Може ли някакъв пример и/или картинки?
Имам таблица, в която имам различни елементи, на които искам да изчисля периметъра и лицето. Както знаете, формулите за различните елементи са различни. Искам да си направя формулите в отделен sheet и да ги "извиквам" с Vlookup.
Да видя дали разбирам. На картинката виждам два различни елемента:
В1 Въздуховод Прав в-вод
В2 Въздуховод Пас парче
Представа си нямам какво е това, но да кажем те си имат дължина, ширина и височина. Например:
В1: в/д/ш 100/200/300
В2: в/д/ш 50/60/70
Вие искате да сметнете обиколката в Колона Q (заглавието е P)
Формулата не е ли просто 100+200+300 за В1 и 50+60+70 за В2?
И VLOOKUP ви трябва, за да намерите съответните в/д/ш в зависимост от това дали е В1 или В2
това ли е казусът?
Да. Искам в отделен sheet да имам формула, която ми намира лицето и периметъра на елементите. Те може да са: прав въздуховод, коляно, разклонител, пас и много други. В основната таблица, в колона F изписвам елемента. Искам с Vlookup, ексел да ми намира конкретната формула и да ми я качва в клетката. От там, формулата да си черпи информацията от различнити страни на елемента, за да получи крайния резултат.
По-правилно е да се направи друго. В отделен шийт да сметнете лицето и периметъра на всеки елемент и да си го извикате с VLOOKUP в таблицата.
Да разбирам ли, че един елемент може да има различни размери?
Точно така. Различните елементи могат да са с различни размери.
Лошо. А колко са на брой тези елементи? Има ли елементи, за които формулите да се повтарят?
Примерно за В1, В4 и В8 формулата за лице е една и съща
Има повтарящи се. Основната таблица расте постоянно. може да достигне няколко стотин или хиляди позиции. Елементите са 7-8 вида, повечето с различни размери.
Е, щом са 7-8 елементите, тогава може да се използва формулата CHOOSE.
Ще помисля малко и ще пиша как може да стане. Много ще ми помогне, ако ми изпратиш файла на kochomindev@gmail.com
Може и да не е целия, а само 20-30 реда, с измислени числа и имена.
Изпратен е вече. Благодаря предварително за усилието :)
Иконката горе дясно под камбанката за уведомления.
Здравейте, Натъкнах се на следния проблем. Имам един въпрос за функцията VLOOKUP, която се опитах да напиша във въпросника, но ми дава, че е прекалено дълъг текст, така беше, но дори когато го смалих до две изречения пак не ми дава да си публикувам въпроса и това ми се струва доста неудобно! Както и да е, В една фирмена работна таблица имаме формулата: =+VLOOKUP(H3;$Z$2:$AB$11;3;TRUE)
Тя е предназначена да попълни данни от изминати километри на даден шофьор и да му покаже какъв бонус му се полага спрямо тях!
Пример м/у 12001 и 12500км 300лв. бонус 12501 и 13000км 400лв. бонус 13001 и 13500км 500лв. бонус H3 взима изминатите км. после взима диапазона $Z$2:$AB$11 и после от колона 3 казва, че трябва да вземе 300лв. бонус и накрая с TRUE
Въпроса ми е, каква е ролята на + в този случай и защо като дам False ми се появява грешка?
Поздрави, Борислав Белев
Плюсът не играе абсолютно никаква роля в случая. Нито пречи, нито помага. Може преспокойно да го махнете.
На втория въпрос - За целите, за които използвате vlookup в този случай ТРЯБВА да използвате TRUE.
Когато използвате FALSE променяте цялата логика на формулата и затова ви връща грешка
Здравейте, при опит да направя упражнението се получава грешка: 
Къде греша. Пробвах формулата и с ; отново същото.
Благодаря.
Най-често #N/A грешка се получава, когато не може да намери клетките, към които се пренасочва. Понеже виждам, че С5 е ОК, много вероятно е да има проблем в диапазона, към който е насочен. Ето няколко насочващи въпроса. 1. Правилно ли е дефиниран A2:D9? 2. Има ли в него фразата, която търсим (Теодор Митков) - точна без правописни грешки и празни интервали ?
Аз бих искала да попитам как се извиква въпросното падащо меню, от което да избираме съответното име? Благодаря! p.s. Опитах се да прегледам коментарите за вече съществущ отговор на моя въпрос, но има толкова много и не успях, тъй че извинете ме ако вече сте покрили темата...
Прегледай урока още 1,2,3 или повече пъти, докато разбереш. И на мен ми беше трудно, но не чакай някой да те научи.
А, пардон... явно не съм разбрала идеята на Aula.bg ..аз глупавата реших, че тук всеки e свободен да зададе въпрос и ако някой има отговора би помогнал с удоволствие...ако вие нямате отговора, Диана, коментарът ви е ненужен...или поне безсмислен, защото ако не исках да се уча от хора с повече опит от моя нямаше изобщо да се записвам на курс ...айде лек ден и на вас...
Модул 7, урок 1 говори по този въпрос, само че съм гледала видеото април и паметта в случая ме подведе. Върпосът ми има отговор там, за който като мен иска да си припомни :)
Във формулата се вижда, че не е задължително да пишем True или False. Какво ще стане ако пропуснем да изберем някое от двете? Excel автоматично ще приеме, че е зададен False ли?
за коя формула става въпрос? ако е за VLOOKUP - ще избере True
За VlOOKUP става дума. Благодаря.
Работа с "жив" екселски файл със страшно много страници вътре и моята страница BG е със сипсък с фактури, които трябва да се актуализират всяка седмица с новоиздадените . Новите ги получавам о тдруго ммясто от друг екселски файл и след като го получа ме посъветваха да си "изкарвам " новите номера на фактури, дата, срок на плащане, сума , валута и тн . с функцията vLOOKUP, но не ми се получава, изгледах видеото ви много пъти, не знам къде греша и дали изобщо е възможно тази функция ад помогне. А губя страшно много време , за да вадя новите фактури ръчно
Здравейте, доколкото се ориентирам ситуацията е следната:
Имате две таблици: "Основна", в която са старите фактури с някакви данни за тях. и "Нова" - с новите фактури и (предполагам) същия тип данни за тях. За примера нека приемем, че са Номер, Дата и сума на фактурата, които са съответно в колони 1, 2 и 3 на новата таблица
Всеки ден/седмица/месец Вие ръчно копирате ред по ред (или по няколко реда) новите фактури от Новата таблица в Основната
Ето едно решение, което ще спести работа. То не е най-ефективното възможно, но без да видя файла - не мога да дам по-хубаво.
В Новата таблица създавате една помощна колона, в която с VLOOKUP извиквате някакви данни от Основната. Няма значение какви. Целта на тази колона е да проверите кои фактури от Основната таблица ги има в Новата (Ако излиза #N/A значи ги няма). На теория не би трябвало да има такива, но все пак
Филтрирате всички фактури от Новата таблица, които ги няма в старата (имат #N/A в помощната колона) и копирате номерата им в Основната таблица
с VLOOKUP извиквате нужните данни от Новата таблица в Основната.
В общи линии по-просто е, отколкото изглежда. Ако имате някакви въпроси - може да ми пишете на лично съобщение или на kochomindev@gmail.com
Добър ден, излиза ми грешката #N/A ,вкарвам всичко , както е показано ?
това е таблицата,която използвам за масив_таблица ;
а какво се опитвате да направите? Формулата е доста объркана
да,вкарам полето "Продажба№" да отговаря на съответните имена,както е показано в урока :)
не съм гледал урока и гадая малко какво се опитвате да направите.
Ако идеята е в клетка В2 да напишете име и в клетка В4 да излезе номера на съответната продажба - няма да стане с прост VLOOKUP, защото ключът за търсене (името) е вляво от това, което търсите (номера)
VLOOKUP работи по обратния начин - ключът трябва да е вдясно.
Страшно много Ви,благодаря,да точно исках в клетка B2 да напиша име и в клетка B4 номера продажба ,размених местата им и се получи !
Лек ден и успехи!
Лек ден и успехи!