Excel предлага с SVerweis многофункционална функция, която претърсва първата колона на даден обхват за търсене.
Excel SVERWEIS – целенасочено използване, прецизна работа
SVERWEIS в Excel е една от най-често използваните функции, когато трябва да се извлекат отделни стойности от обширни таблици. За да се постигне това по надежден начин, е необходимо нещо повече от правилната формула – важна е структурата на данните и внимателният поглед към източниците на грешки.
- Матрицата трябва да започва отляво: SVERWEIS търси само в първата колона от избрания диапазон. Ако например клиентският номер е в колона D, търсенето трябва да започне оттам – т.е. не „A:F“, а „D:F“. Първата колона винаги се използва като база за сравнение. Ако това не се спази, резултатите ще бъдат грешни или изобщо няма да има резултати.
- Работете по-стабилно с таблични обекти: Веднага след като форматирате данните си като „таблица“ (клавишни комбинации: Ctrl + T), Excel автоматично реагира на последващи разширения. Формулата SVERWEIS остава валидна, дори ако добавите нови редове. Формулата =SVERWEIS(A2;Таблица1;3;FALSCH) след това достъпва актуализираните данни – идеално за растящи списъци.
- FALSCH вместо WAHR – целенасочено управление на поведението при търсене: Без точно съпоставяне рискувате да получите неточни резултати. Четвъртата цифра във формулата – така наречената област_позоваване – почти винаги трябва да бъде зададена на FALSCH. Само тогава Excel проверява за точно съвпадение. WAHR е подходящо само за сортирани данни – което в ежедневната работа рядко е гарантирано.
- Проверка и уеднаквяване на форматите на данните: Число в текстов формат или число в цифров формат – и двете изглеждат еднакво, но се държат по различен начин. Ако функцията SVERWEIS не дава никакъв резултат, въпреки че стойността е в списъка, почти винаги има конфликт на формати. С =ISTTEXT(A2) или =ISTZAHL(A2) можете бързо да откриете къде е проблемът. Инструменти като =GLÄTTEN() или =WERT() автоматично почистват.
- Целенасочено използване на абсолютни и относителни препратки към клетки: Когато копирате формули, препратките към клетките се адаптират. При матрични данни това може да има фатални последствия. Затова използвайте абсолютни препратки към клетки с $ (например $A$2:$D$100), за да премествате формулите надеждно отгоре надолу или напречно през таблицата, без областта да „се измества“.
- Елегантно отстраняване на грешки: Никой не иска да вижда „NV“ в серийни писма или табла. Комбинирайте SVERWEIS с WENNFEHLER, за да създадете алтернативен резултат:=WENNFEHLER(SVERWEIS(A2;Tabelle1;3;FALSCH); „Nicht gefunden“)
- Това не е козметично, а функционално – например при експортиране или автоматизирани процеси с данни.
- Опознайте ограниченията на SVERWEIS: Веднага щом колоните се преместят или структурата на таблицата се промени, SVERWEIS дава грешни резултати. Обратното търсене – т.е. отдясно наляво – също не е възможно. В такива случаи се препоръчва XVERWEIS или INDEX/VERGLEICH.
XVERWEIS, INDEX & Co. – алтернативи с по-голям контрол
От Microsoft 365 и Excel 2021 с XVERWEIS е налице по-стабилна версия, която преодолява много от слабостите на SVERWEIS. Комбинацията INDEX/VERGLEICH също се счита за гъвкава – особено при промяна на версиите на Excel или изискванията.
- XVERWEIS: Търсене назад без заобикалки: Най-голямата разлика: XVERWEIS позволява също връщане от колони вляво от търсения термин. Пример: =XVERWEIS(B2;Таблица1[Име];Таблица1[Клиентски номер];„Няма резултат“). По този начин могат да се търсят и таблици, чиято структура не е оптимално изградена. SVERWEIS би се провалил тук.
- Колонният индекс отпада – по-малко грешки: При XVERWEIS задавате две конкретни области от клетки: област за търсене и област за връщане. Не е необходимо да броите, както при SVERWEIS (3-та колона, 4-та колона и т.н.). Това намалява грешките и подобрява прегледността.
- Целенасочено управление на съобщенията за грешки: Четвъртият аргумент „ако_не_е_намерено“ в XVERWEIS замества обходния път през WENNFEHLER. Лесно четене, по-малко грешки при писане, директен достъп – функцията реагира по-ефективно на празни клетки или грешни стойности.
- Управление на посоката на търсене и сравнение: XVERWEIS предлага два допълнителни параметъра: „Режим на сравнение“ (0, -1, 1) и „Режим на търсене“ (1 отгоре, -1 отдолу). По този начин можете да търсите целево например ценови скали или дублирани записи – нещо, което е невъзможно с SVERWEIS.
- XVERWEIS на мобилни устройства и в уеб: В Microsoft Excel Mobile XVERWEIS работи по принцип, но сложните динамични формули са ограничени. В Excel Online както SVERWEIS, така и XVERWEIS работят стабилно, стига форматите на клетките да са правилни. На мобилни устройства се препоръчва да се използват прости структури, тъй като липсват някои елементи за управление.
- INDEX/VERGLEICH: Многофункционален и независим от версията: INDEX извлича стойността, VERGLEICH търси подходящото място. Пример за формула: =INDEX(D2:D100;VERGLEICH(A2;B2:B100;0))
За разлика от SVERWEIS, тази комбинация не се прекъсва, когато премествате колони. Тя е подходяща за сложни изчисления или когато XVERWEIS не е налична (например в Excel 2016).
Производителност при големи обеми данни: Който работи с хиляди записи, постига по-бързо целта си с INDEX/VERGLEICH или XVERWEIS, отколкото с SVERWEIS. И двете обработват по-ефективно големи количества данни – особено в динамични табла или при експортиране от трети системи.
Практически пример – SVERWEIS стъпка по стъпка
Мнозина използват Excel ежедневно, но при SVERWEIS често възникват недоразумения – особено ако не работите редовно с формули. Следващият пример показва как с чиста структура и няколко стъпки можете да създадете функциониращ SVERWEIS. Целта е автоматично да се извлече съответният телефонен номер от списък с клиенти.
- Структуриране на изходната таблица: Създайте таблица с три колони: „Клиентски номер“, „Име“ и „Телефонен номер“. Всеки ред съдържа точно един запис. Важно: Колоната с търсената стойност – в този случай клиентският номер – се намира най-вляво. Функцията SVERWEIS сравнява винаги само с първата колона в областта за търсене. Пример: „1002 Langer 0157 9876543“
- Създаване на втора таблица за заявката: По-надясно – или в друг лист на таблицата – създайте малък списък с две колони: „Клиентски номер“ и „Телефонен номер“. В колоната „Клиентски номер“ въведете стойността, която искате да търсите – например „1002“. Втората колона остава празна и ще се попълни автоматично по-късно.
- Създайте правилно SVERWEIS: Кликнете върху клетката, в която трябва да се появи резултатът – например клетка F2. Въведете следната формула:=SVERWEIS(E2;A2:C100;3;FALSCH)
- Тази формула означава: Намери стойността от клетка E2 (в случая „1002“) в първата колона от диапазона A2 до C100. След това върни стойността от третата колона (C) на същия ред. „FALSCH“ в края гарантира, че се приемат само точни съвпадения.
- Тестване на формулата и проверка на резултата: Веднага след като натиснете Enter, Excel автоматично показва в клетка F2 „0157 9876543“ – телефонният номер на търсения клиент. Ако промените стойността в E2 (например на „1003“), резултатът в F2 също се променя автоматично. Формулата се адаптира динамично към въведената стойност.
- Избягване на типични източници на грешки: Проверете дали в областта за търсене числата са форматирани като числа. Често причината за грешка „NV“ е, че стойността в E2 е форматирана като число, но в изходната таблица е форматирана като текст. Това е трудно забележимо, но има технически последствия. Можете да проверите това с „=ISTTEXT(A2)“. При необходимост „=WERT()“ помага да коригирате съдържанието на клетката.
- Задаване на клетки, когато се използват няколко реда: Плъзнете формулата надолу, за да потърсите други клиенти, и фиксирайте областта с доларов знак: =SVERWEIS(E2;$A$2:$C$100;3;FALSCH)
По този начин областта на търсене остава непроменена, дори ако копирате формулата или я плъзнете надолу.
Улавяне на съобщения за грешки, подобряване на изхода
За да изведете разбираемо съобщение като „Клиент не намерен“ вместо „NV“, допълнете формулата по следния начин: =WENNFEHLER(SVERWEIS(E2;$A$2:$C$100;3;FALSCH); „Клиент не намерен“)
Така таблицата изглежда по-подредена и потребителят веднага вижда, когато няма резултат.
