Czyli jak zmusić Excela, by odwoływał się do wybranej kolumny przy kopiowaniu

Na pewno spotkaliście się z narzędziami tabel w Excelu. Mają one świetne właściwości, choćby takie, że umieją powiększać swoje rozmiary. Dodatkowo jak wpiszemy do nich formuły – nie musimy ich kopiować w dół – tabele robią to za nas. Natomiast odnośnie formuł to robią coś jeszcze, niekoniecznie lubianego przez użytkowników… stosują odwołania strukturalne, czyli odwołania do nazw elementów tabeli, jak np. kolumna, czy nagłówek. Dla prostych operacji to super sprawa, jednak dla trudniejszych może okazać się problemem…

Pierwszy raz na ten problem natknęłam się, gdy tworzyłam plik dla przedszkola moich dzieci, którego zadaniem było rozliczanie opłat za pobyt dzieci po godzinie 13:00. Wszystkie dane trzymałam w tabeli i wyliczałam tam kilka wartości na podstawie danych w jednej kolumnie. Jakież to było dla mnie irytujące, kiedy okazywało się, że podczas kopiowania formuły, Excel za każdym razem zmienia adresy kolumn (czyli zachowuje się tak, jak normalna kopiowana komórka!), podczas gdy ja chciałam, aby zmieniał mi adres tylko jednej (ale w żaden sposób mu tego nie powiedziałam :))! Nie umiałam tego zrobić, więc pisałam każdą formułę oddzielnie. Oh, dear God! BTW: teraz tak sobie o tym myślę i śmieję się z siebie, no bo niby skąd Excel miał wiedzieć, którą kolumnę Malina chciała zablokować? Chciałam, żeby Excel mi to wyczytał w myślach najwyraźniej 😉 Tego akurat nie umie, ale na szczęście umie „blokować” kolumny tabel w formułach. W tym wpisie zobaczysz jak.

W tym wpisie postanowiłam omówić prosty przykład, aby jak najlepiej pokazać metodę na blokowanie kolumn w odwołaniach strukturalnych. Będziemy chcieli obliczyć strukturę kosztów mediów w kwartale, a potem na każdego najemcę naszego centrum handlowego (a co!:)). Dane mamy następujące:

Formatka

Matematycznie formuły to proste dzielenie sumy zużycia styczniowego przez sumę zużycia kwartalnego, przy czym w formule będziemy chcieli zablokować sumę kwartalną, gdyż każdy kolejny miesiąc będzie się do niej odwoływał. Dla struktury zużycia mediów na klienta będziemy robić to samo, tylko w kilku wierszach. A więc:

Struktura zużycia mediów

Informację o tym chcemy mieć w wierszu 3., więc do komórki C3 wpiszmy następującą formułę :

=SUMA(Media[Sty])/SUMA(Media[[Q1]:[Q1]])

Cały trik polega tutaj na zapisie kolumny Q1. Zauważcie, że jest on wskazana jako zakres kolumn i wskazana jest nazwa tabeli (koniecznie!). Nie jest to nic dziwnego, ponieważ dla stycznia też jest wskazana. Dzieje się tak dlatego, że formuła istnieje poza tabelą, stąd Excel musi wiedzieć, do jakiej tabeli ma się odwołać. Natomiast gdyby formuła była w tej tabeli – nadal musielibyśmy odwoływać się do tabeli podczas wskazywania zakresu kolumn. Wtedy dopiero uzyskamy efekt blokowania kolumny.

Można to oczywiście wpisać z palca, ale polecam inną metodę: oprócz zaznaczenia naszej docelowej kolumny (Q1), zaznacz jakąś kolumnę obok. Excel sam w ten sposób wstawi zakres, w którym wystarczy tylko podmienić nazwę „tej drugiej” kolumny na Q1:

Wskazywanie zakresu kolumn

I podmianka na właściwą nazwę kolumny:

Zmiana zakresu kolumn

Tak napisaną formułę trzeba teraz skopiować, ale uwaga! ZA POMOCĄ PRZECIĄGANIA!!!!! Niestety tylko tak, bo inaczej po prostu nie działa – skopiujemy tym sposobem formułę 1:1 (normalnie, żeby to uzyskać trzeba wejść do edycji formuły i skopiować tekst, który ją tworzy!). Strasznie nad tym ubolewam, ponieważ nie działa tutaj moje ukochane Ctrl + Enter ;(

Kopiowanie za pomocą PRZECIĄGANIA

I to cała filozofia 🙂 Zobaczmy teraz jak to zrobić na klientów.

Struktura zużycia mediów na klienta

Matematyka jest identyczna – dzielenie, natomiast będziemy dzielić pojedyncze wiersze, a nie sumy kolumn. Dlatego Excel doda sobie znaczek @ przy nazwie kolumny Znaczek ten oznacza, że ma być brana wartość z wiersza, w którym jest formuła.

Wpiszmy do komórki H6 taką formułę:

=Media[@Sty]/Media[@[Q1]:[Q1]]

A tak to wygląda podczas wpisywania:

To samo dla najemców

Trik identyczny. Po skopiowaniu PRZECIĄGANIEM w prawo i w dół otrzymujemy następujący efekt (pamiętaj o wypełnianiu bez formatowania!):

Wynik

Mam nadzieję, że odczarowałam :)Tabele nie takie straszne, choć przyznam bez bicia, że sama za nimi nie przepadam. Chyba dlatego, że formuły o nie oparte są mega długie 😉