Изтеглете примерната работна книга
Този урок демонстрира как да използвате Функция АДРЕС на Excel в Excel за връщане на клетъчен адрес като текст.
АДРЕС Функция Преглед
Функцията ADDRESS Връща адреса на клетката като текст.
За да използвате функцията ADDRESS Excel Worksheet, изберете клетка и въведете:
(Забележете как се появяват входовете на формулата)
Функция АДРЕС Синтаксис и входове:
| 1 | = АДРЕС (номер на ред, номер на колона, номер на абсорбция, C1, текст на лист) |
ред_ номер - Номерът на реда за справка. Пример: 5 за ред 5.
col_num - Номерът на колоната за справка. Пример: 5 за колона E. Не можете да въведете „E“ за колона E
abs_num - [по избор] Номер, представляващ дали препратката трябва да има абсолютни или относителни препратки към ред/колона. 1 за абсолютно. 2 за абсолютен ред/относителна колона. 3 за относителен ред/абсолютна колона. 4 за роднина.
а1 - [по избор]. Число, показващо дали да се използва стандартен (A1) референтен формат на клетка или R1C1 формат. 1/TRUE за стандарт (по подразбиране). 0/НЕВЯРНО за R1C1.
лист_текст - [по избор] Името на работния лист, който да се използва. По подразбиране текущият лист.
Каква е функцията АДРЕС?
Функцията ADDRESS е малко уникална функция. През повечето време в електронна таблица казваме на компютъра препратка към клетка и тя ни дава стойността от тази клетка. С ADDRESS вместо това ще изградим името на клетка. Този адрес може да бъде относителен или абсолютен, в стил A1 или R1C1, и може или не може да включва името на листа. В по -голямата си част тази функция няма много практически приложения, но може да бъде забавна за научаване. Когато се използва, обикновено е в комбинация с други функции, както ще видим по -долу.
Основен пример
Да кажем, че искаме да изградим препратка към клетката в 4th колона и 1ул ред, известен също като клетка D1. Можем да използваме оформлението, изобразено тук:
Нашата формула в A3 е проста
| 1 | = АДРЕС (B1, B2) |
Имайте предвид, че като не посочваме конкретен аргумент за относително/абсолютно, всички получаваме абсолютни. Също така получихме стила по подразбиране на препратки от тип A1 без име на лист.
В комбинация с INDIRECT
Както казахме, функцията ADDRESS сама по себе си не ни даде нищо полезно. Бихме могли да го комбинираме с функцията INDIRECT, за да получим известна функционалност. Помислете за това оформление, където имаме списък с елементи в колона D. Ако запазим същата формула, както преди, ще генерираме препратка към D1 така
| 12 | = АДРЕС (B1, B2)= $ D $ 1 |
Поставяйки функцията адрес в функция INDIRECT, ще можем да използваме генерираната препратка към клетка и да я използваме по практически начин. INDIRECT ще вземе препратката на „$ D $ 1“ и ще я използва за извличане на стойността от тази клетка.
| 123 | = ИНДИРЕКТНО (АДРЕС (B1, B2)= ИНДИРЕКТНО ($ D $ 1)= "Apple" |
Забележка: Докато горното дава добър пример за това, че функцията ADDRESS е полезна, това не е добра формула за нормално използване. Изискваше две функции и поради ИНДИРЕКТНОТО ще има нестабилен характер. По -добра алтернатива би била използването на INDEX по следния начин: = INDEX (1: 1048576, B1, B2)
Адрес със специфична стойност
Понякога, когато имате голям списък с елементи, трябва да сте в състояние да знаете къде в списъка се намира елемент. Помислете за тази таблица с резултати от ученици. Продължихме напред и изчислихме минималните, средните и максималните стойности на тези резултати в клетки E2: G2.
Бихме могли да филтрираме нашата таблица за всеки от тези елементи, за да намерим къде се намира (отново, представете си, че това е много по -голям списък), или дори можем да приложим условно форматиране, така че визуално да се появи на потребителя. Но ако списъкът е хиляди редове, не искаме да се налага да превъртаме толкова далеч, за да видим какво искаме. Вместо това ще използваме стойностите в E2: G2, за да определим адреса на клетките, съдържащи нашите стойности. За да направим това, ще използваме функцията MATCH с ADDRESS. Не забравяйте, че MATCH ще върне относителната позиция на стойност в диапазон.
Тогава нашата формула в E3 е:
| 1 | = АДРЕС (МАТЧ (E2, $ B: $ B, 0), 2) |
Можем да копираме същата формула в G3 и само препратката E2 ще се промени, тъй като това е единствената относителна препратка. Поглеждайки назад към E3, функцията MATCH успя да намери стойността на 98 в 5th ред от колона Б. Нашата функция ADDRESS след това използва това за изграждане на пълния адрес на „$ B $ 5“.
Преведете букви от колони от цифри
До този момент всички наши примери позволяват на функцията ADDRESS да върне абсолютна препратка. Следващият пример ще върне относителна препратка. В тази таблица искаме да въведем число в колона А и да върнем съответното име на буквата на колоната.
За да постигнем целта си, ще накараме функцията ADDRESS да върне препратка към ред 1 в относителен формат и след това ще премахнем „1“ от текстовия низ, така че да имаме само буквата (ите). Помислете в нашата таблица ред 3, където нашият вход е 13. Нашата формула в B3 е
| 1 | = ЗАМЕСТНИК (АДРЕС (1, A3, 4), "1", "") |
Обърнете внимание, че сме дали 3rd аргумент в рамките на функцията ADDRESS, която контролира относителното спрямо абсолютното препращане. Функцията ADDRESS ще изведе „M1“, а след това функцията SUBSTITUTE премахва „1“, така че оставаме само с „M“.
Намерете адреса на Named Ranges
В Excel можете да наименувате диапазон или диапазони от клетки, което ви позволява просто да се позовавате на именования диапазон вместо на препратката към клетката.
Повечето именовани диапазони са статични, което означава, че винаги се отнасят до един и същ диапазон. Можете обаче да създавате динамични именовани диапазони, които се променят по размер въз основа на някои формули (и).
С динамичен именов диапазон може да се наложи да знаете точния адрес, към който сочи посоченият ви диапазон. Можем да направим това с функцията ADDRESS.
В този пример ще разгледаме как да дефинираме адреса за нашия именен диапазон, наречен „Оценки“.
Нека да върнем нашата маса от преди:
За да получите адреса на диапазон, трябва да знаете горната лява клетка и долната лява клетка. Първата част е достатъчно лесна за изпълнение с помощта на функцията ROW и COLUMN. Нашата формула в E1 може да бъде
| 1 | = АДРЕС (ROW (Оценки), COLUMN (Оценки)) |
Функцията ROW ще върне реда от първата клетка в нашия диапазон (който ще бъде 1), а COLUMN ще направи същото по същия начин за колоната (също 1).
За да получим долната дясна клетка, ще използваме функцията ROWS и COLUMNS. Тъй като можем да разберем началната точка на нашия диапазон, ако изчислим колко висок е диапазонът и извадим началната точка, ще получим правилната крайна точка. Формулата за това изглежда така
| 1 | = АДРЕС (ROWS (Степени) -ROW (Оценки) +1, COLUMNS (Оценки) -COLUMN (Оценки) +1) |
И накрая, за да обединим всичко в един низ, можем просто да обединим стойностите заедно с двоеточие в средата. Формула в Е3 може да бъде
| 1 | = E1 & ":" & E2 |
Забележка: Докато успяхме да определим адреса, диапазона, нашата функция ADDRESS определи дали да посочим препратките като относителни или абсолютни. Вашите динамични диапазони ще имат относителни препратки, които тази техника няма да възприеме.
2nd Забележка: Тази техника работи само в непрекъснат диапазон от имена. Ако сте имали именен диапазон, който е дефиниран като нещо като тази формула
| 1 | = A1: B2, A5: B6 |
тогава горната техника би довела до грешки.
допълнителни бележки
Използвайте функцията АДРЕС, за да генерирате адрес от даден номер на ред и колона. Важно: Трябва да въведете колоната номер. Въвеждането на буквата на колоната ще генерира грешка. Ако е необходимо, можете да използвате функцията за колона, за да изчислите номера на колоната за препратка към клетка.
Abs_num ви позволява да превключвате абсолютните и относителните препратки към клетки.
1,2,3,4 a1, $ a $ 2… относително/абсолютно и т.н.
След това посочете дали да използвате a1 или R1C1. a1 режимът е стандартният режим, при който клетките се препращат с буквата на колоната и номера на реда (напр. a4). Режимът R1C1 е мястото, където клетките се препращат по техните редове и колони (напр. R4C1). a1 е режимът по подразбиране. Използвайте това, освен ако нямате основателна причина да не го правите.
В последния спор можете да въведете име на лист, ако препратката към клетката ще бъде на друг работен лист. Въведете името на листа, заобиколено от паратеза (напр. „Лист 3“).
Връщане към списъка с всички функции в Excel
АДРЕС функция в Google Таблици
Функцията ADDRESS работи абсолютно същото в Google Sheets, както и в Excel.
