Когато използвате формули за търсене в Excel (като VLOOKUP, XLOOKUP или INDEX/MATCH), намерението е да се намери съответстващата стойност и да се получи тази стойност (или съответната стойност в същия ред/колона) като резултат.
Но в някои случаи, вместо да получите стойността, може да искате формулата да върне адреса на клетката на стойността.
Това може да бъде особено полезно, ако имате голям набор от данни и искате да разберете точната позиция на резултата от формулата за търсене.
Има някои функции в Excel, предназначени да направят точно това.
В този урок ще ви покажа как можете намерете и върнете адреса на клетката вместо стойността в Excel, използвайки прости формули.
Търсене и връщане на клетъчен адрес с помощта на функцията АДРЕС
Функцията ADDRESS в Excel е предназначена точно за това.
Той взема реда и номера на колоната и ви дава адреса на клетката на тази конкретна клетка.
По -долу е синтаксисът на функцията ADDRESS:
= АДРЕС (номер на ред, номер на колона, [номер на абс], [a1], [текст на лист])
където:
- row_num: Номер на ред на клетката, за която искате адреса на клетката
- column_num: Номер на колона на клетката, за която искате адреса
- [abs_num]: Незадължителен аргумент, където можете да посочите дали препратката към клетката да бъде абсолютна, относителна или смесена.
- [a1]: Незадължителен аргумент, където можете да посочите дали искате препратката в стил R1C1 или A1
- [sheet_text]: Незадължителен аргумент, където можете да посочите дали искате да добавите името на листа заедно с адреса на клетката или не
Сега нека вземем пример и да видим как работи това.
Да предположим, че има набор от данни, както е показано по -долу, където имам идентификационния номер на служителя, неговото име и техния отдел и искам бързо да знам адреса на клетката, който съдържа отдела за идентификационен номер на служител KR256.
По -долу е формулата, която ще направи това:
= АДРЕС (MATCH ("KR256", A1: A20,0), 3)
В горната формула използвах функцията MATCH, за да разбера номера на реда, който съдържа дадения идентификационен номер на служител.
И тъй като отделът е в колона C, използвах 3 като втори аргумент.
Тази формула работи чудесно, но има един недостатък - няма да работи, ако добавите реда над набора от данни или колона вляво от набора от данни.
Това е така, защото когато посоча втория аргумент (номера на колоната) като 3, той е кодиран трудно и няма да се промени.
В случай, че добавя някоя колона вляво от набора от данни, формулата ще брои 3 колони от началото на работния лист, а не от началото на набора от данни.
Така че, ако имате фиксиран набор от данни и се нуждаете от проста формула, това ще работи добре.
Но ако имате нужда от това, за да бъдете по-защитени от глупости, използвайте този, обхванат в следващия раздел.
Търсене и връщане на клетъчен адрес с помощта на функцията CELL
Докато функцията ADDRESS е създадена специално, за да ви даде препратка към клетката на посочения номер на ред и колона, има друга функция, която също прави това.
Нарича се функцията CELL (и може да ви даде много повече информация за клетката, отколкото функцията ADDRESS).
По -долу е синтаксисът на функцията CELL:
= КЛЕТКА (info_type, [справка])
където:
- info_type: информацията за клетката, която искате. Това може да бъде адресът, номерът на колоната, името на файла и т.н.
- [справка]: Незадължителен аргумент, където можете да посочите препратката към клетката, за която имате нужда от информацията за клетката.
Сега, нека да видим пример, където можете да използвате тази функция, за да потърсите и да получите справка за клетката.
Да предположим, че имате набор от данни, както е показано по -долу, и искате бързо да знаете адреса на клетката, който съдържа отдела за идентификационен номер на служител KR256.
По -долу е формулата, която ще направи това:
= КЛЕТКА ("адрес", ИНДЕКС ($ A $ 1: $ D $ 20, MATCH ("KR256", $ A $ 1: $ A $ 20,0), 3))
Горната формула е доста ясна.
Използвах формулата INDEX като втори аргумент, за да получа отдела за идентификационен номер на служителя KR256.
След това просто го обгърнах във функцията CELL и го помолих да върне адреса на клетката с тази стойност, която получавам от формулата INDEX.
Сега тук е тайна защо работи - формулата INDEX връща стойността за търсене, когато й дадете всички необходими аргументи. Но в същото време, той също ще върне референтната клетка на тази получена клетка.
В нашия пример формулата INDEX връща „Продажби“ като получена стойност, но в същото време можете също да я използвате, за да ви даде препратка към клетката на тази стойност вместо самата стойност.
Обикновено, когато въведете формулата INDEX в клетка, тя връща стойността, защото това се очаква да направи. Но в сценарии, където се изисква препратка към клетка, формулата INDEX ще ви даде препратка към клетка.
В този пример точно това прави.
Най -хубавото при използването на тази формула е, че тя не е свързана с първата клетка в работния лист. Това означава, че можете да изберете всеки набор от данни (който може да бъде навсякъде в работния лист), да използвате формулата INDEX, за да проверявате редовно и все пак ще ви даде правилния адрес.
И ако вмъкнете допълнителен ред или колона, формулата ще се коригира съответно, за да ви даде правилния адрес на клетката.
Това са две прости формули, които можете да използвате, за да потърсите и намерете и върнете адреса на клетката вместо стойността в Excel.
Надявам се, че сте намерили този урок за полезен.
