#033 R1C1 адреси и преобразуване на имена на колони в номера и обратно...

За по-лесното боравене с адреси по подразбиране Excel борави с така наречената A1 нотация... Т.е. "име на колоната и номер на реда". Съществува и втори вид наречен R1C1... При не го се посочва номер на реда(първо е ред!) и НОМЕР на колоната. Преди числата се постановя R и C. Например R1C1 е клетката $A$1 (първа колона първи ред). R3C2 е $B$3 (ВТОРА колона и ТРЕТИ ред!). Обърнете внимание и на още една особеност. Не случайно дадох отговорите като абсолютни адреси ($A$1 и $B$3)!  Задаването на относителни адреси е по-особено. Например ако адресът е R[-1]C означава "клетката с адрес един ред нагоре спрямо текущата клетка и същата колона"! Например ако сме в клетка G5 става въпрос за клетката G4! Адресът R[+1]C[-1] ако сме B3 е еквивалентен на A4 (ред надолу и колона на ляво)... И т.н... За повече информация  прочетете тук:

http://office.microsoft.com/bg-bg/excel-help/HP005198323.aspx където има информация и как да си включите/изключите този режим на показване на работните листи (не че има някакъв смисъл де):) Използването на R1C1 адреси е доста често използван в макросите за това и "подгрявам" публиката с тази тема:)

Вчера ми се наложи да правя преобразуване на номер на колона в име и преобразуване на име на колона в номер... Покрай тези примери ще разгледам и  две "екзотични" функции:)

Пример 1. Да се преобразува име на колона в число... Името се намира клетка A1 (например XZ).

=COLUMN(INDIRECT(A1&":"&A1)) Тук няма никаква магия. Създава се валиден адрес и се взема неговата колона. Адресът се получава като се долепят името на колоната сама до себе си (получава се  "XZ:XZ"), чрез Indirect се преобразува в зона и чрез Column се преобразува в число. Със същия успех може да се "сглоби" адрес като се долепи към името на колонката някакво число за номер на ред. Например A1&1 което е и по-кратко!

=COLUMN(INDIRECT(A1&1))

Пример 2. Да се преобразува номер на колона в нейно име... Номера се намира в клетка А1 (например 156).
=MID(ADDRESS(1,A1),2,FIND("$",ADDRESS(1,A1),2)-2)
Тук "магията" е във функцията ADDRESS. Пълното описание на български може да намерите тук: http://office.microsoft.com/bg-bg/excel-help/HP010062407.aspx

ADDRESS(1,A1) За разлика от Indirect, която преобразува текст в зона, функцията Addrеss преобразува числа (ред и колона) в адрес. В нашия случай получавам адресът на клетка с ред едно (използваното число няма значение) и НОМЕРА на колоната посочен в клетка A1. Например за числото 156 функцията ще върне $EZ$1

(по подразбиране връща абсолютен адрес) след което извличаме с Mid знаците от втория (първия е $!) до следващия (обърнете внимание на Find) намерен знак $ (ако имате проблеми с Mid/Find комбинирането пишете да за да спретна някоя темичка):)

Пример 3. Да се намери адресът на средната клетка в листа. Да не се влияе от версията на Excel (2003 и 2007 имат различен брой редове и колони!)

=ADDRESS(SUMPRODUCT(MAX(ROW(A:A))) /2, SUMPRODUCT(MAX(COLUMN(1:1))) / 2) или CSE {=ADDRESS(MAX(ROW(A:A)) / 2, MAX(COLUMN(1:1)) / 2)}
"Пакетират" се функциите Max със Sumproduct за да се намери най-големия ред (колона) на дадена зона... Ако премахнем Sumproduct трябва да въведем формулата като CSE! Номерата и колоните се делят на две (търсим средата) и се дават като параметри на Address....

----
Забележка под линия:):) Има различни варианти за правенето на нещо... Често и аз бъркам и не винаги ви давам най-добрия вариант забравяйки някоя функция на Excel:):) Когато за първи път правих примерът със средата бях изключил за Address... И го направих по много екзотичен начин:)
=CELL("address",INDIRECT("R" & SUMPRODUCT(MAX(ROW(A:A))) / 2 & "C" & SUMPRODUCT(MAX(COLUMN(1:1))) / 2,FALSE))
Използвах два "трика". Първия трик е възможността Indirect да работи с R1C1 зони (обърнете  внимание на параметърът False. (просто "слепвам" числа и буквите R и C!). Зоната получена от Indirect я давам като параметър на функцията Cell за да превърне зона в адрес. Функцията Cell е много екзотична и много рядко се използва:) http://office.microsoft.com/bg-bg/excel-help/HP010062392.aspx . Както споменах посоченото решение е много тромаво и объркано и ме е леееко срам от него, но нали в крайна сметка работи:):):)
Но както казват хората ... Many (not All) Roads Lead to Rome  :):)

Бързата кучка слепи ги ражда:) Вместо да се правя на умен вместо MAX(ROW(A:A) просто може да се напише Rows(A:A) и съответно Columns(1:1)...