piątek, 19 czerwca 2015

Wykorzystanie referencji R1C1 w praktyce (2/2)

Kontynuując tematykę praktycznego wykorzystania referencji R1C1 w pracy z formułami chciałbym wyjaśnić kiedy jej wykorzystanie ma największy sens. Z własnego doświadczenia wskażę na jedną ogólną sytuację- wprowadzanie do uporządkowanego zakresu komórek formuł, które ręcznie jesteśmy wstanie wprowadzić przez przeciągnięcie lub prostą operację Copy-Paste.

Przykład 1. 100 wierszy w kolumnach A:L zawiera przychody ze sprzedaży dla 12 miesięcy roku. Wprowadzenie sumy rocznej w kolumnie M można wykonać pętlą, ale można też wykonać prostą operacją z wykorzystaniem referencji względnej:

Wyjaśnienie: każda ze 100 kolejnych komórek w kolumnie M zawiera w praktyce tego samego typu referencję względną- sumuje obszar od 12 komórki w lewo do 1 komórki po lewej stronie względem kolumny wynikowej (M)

Przykład 2. Arkusz1 zawiera tabelę z listą produktów (kolumna A) i ich cenami (kolumna B)- łącznie 10 tyś wierszy. W Arkusz2 chcemy z pomocą funkcji WYSZUKAJ.PIONOWO odnaleźć ceny dla wybranych produktów, których nazwy znajdują się w kolumnie A (w 100 wierszach). W tym wariancie operacja ręczna również będzie polegała na przygotowaniu formuły w komórce B1 i przekopiowaniu jej w dół. Tak więc operację tą z poziomu kodu VBA możemy zapisać właśnie z wykorzystaniem referencji R1C1:

Wyjaśnienie: powyższa referencja zawiera zarówno adresowanie względne jak i bezwzględne. Pierwszy argument funkcji to adres względny- jedna komórka w lewo względem komórki wynikowej (w kolumnie B). Drugi argument to bezwzględna referencja do Arkusza2 i tabeli w obszarze A1:B10000. Tabela ta jest stała dla każdej komórki, do które wrzucamy formułę WYSZUKAJ.PIONOWO.

Na koniec chciałbym jeszcze zaznaczyć, że referencja R1C1 pojawia się nie tylko w pracy z właściwością obiektu Range.FormulaR1C1. W praktyce referencję tą można spotkać bardzo często w różnych (czasem zaskakujących) okolicznościach. W wątpliwych sytuacjach należy sięgnąć do pomocy lub na stronę Microsoft MSDN. Warto zwrócić też uwagę na fakt, że np. rejestrator makr preferuję taką właśnie formę zapisu dla wszelkiego rodzaju zawartości komórek. Niekiedy jednak referencja R1C1 może być stosowana zamiennie z referencją A1B1 lecz warto zweryfikować szczegóły i różnice w wykorzystaniu danego typu adresowania.

piątek, 12 czerwca 2015

Wykorzystanie referencji R1C1 w praktyce (1/2)

Jednym z ważnych tematów omawianych w czasie prowadzonych kursów jest zagadnienie związane z wykorzystaniem referencji do zakresów określanych jako R1C1. Doświadczenie trenerskie pokazuje również, że zagadnienie to niekoniecznie wydaje się być proste do przyswojenia choć jednocześnie, po chwili zastanowienia i wykonaniu kilku ćwiczeń adresowanie komórek w stylu R1C1 staje się łatwiejsze. Spróbuję w tym wpisie krótko podsumować zasady dot. wykorzystania tej formy referencji.

Na początku chciałbym jednak przypomnieć- referencja R1C1 nie oznacza wyłącznie względnego stylu referencji. Z wykorzystaniem tego typu adresowania możemy także utworzyć referencję bezwzględną. I tak:


1. Jeżeli nasz adres R1C1 zawiera nawiasy kwadratowe to z pewnością będzie to adresowane względne. W tym wariancie dopuszczalne jest także wykorzystanie wartości ujemnych oraz pominięcie wartości liczbowych po literach R lub C.
2. Jeżeli w adresie R1C1 nie ma nawiasów kwadratowych oraz po literach R i C występują liczby to z pewnością mówimy o adresowaniu bezwzględnym.
3. I najważniejsze, warianty względnego i bezwzględnego adresowania mogą być ze sobą łączone w ramach jednego adresu.

Poniższe prosty przykłady pozwolą zobrazować szereg dostępnych wariantów adresowania R1C1. Załóżmy, że komórka Excela o adresie E5 zawierać będzie prostą formułę w stylu =A1, sprawdzimy następnie jaką postać ma adres R1C1 dla danej formuły:


Przykład 1: adres względny Wyjaśnienie: komórka A1 jest o 4 kolumny i wiersze odpowiednio w lewo i do góry względem komórki E5

Przykład 2: adres bezwzględny Wyjaśnienie: komórka B10 traktowana bezwzględnie, R10 to 10 wiersz (R = Row), a C2 to druga kolumna arkusza (C = Column)

Przykład 3: adres względny dla komórki będącej w tej samej kolumnie Wyjaśnienie: komórka E1 jest w tej samej kolumnie co E5, zwracany adres R1C1 wskazuje ten fakt przez brak wartości po C

Przykład 4: adres częściowo względny i bezwzględny Wyjaśnienie: kwadratowe nawiasy sugerują względność, kolumna jest więc o 3 w prawo od kolumny E i będzie to kolumna H. Brak nawiasów po R sugeruje adres bezwzględny dla wierszy i wskazuje na wiersz 10

W kolejnym wpisie przedstawię kilka praktycznych przykładów zastosowania formuły R1C1.