Nazywanie stałych i formuł

Do tej pory nazwa wskazywała blok komórek. Nazwa może jednak także przechowywać stałą wartość lub nawet całą formułę — bez wskazywania komórek.

Pozwala to przechowywać ważne stałe w jednym miejscu i nadawać złożonym obliczeniom krótkie, znaczące etykiety.

• TaxRate może mieć wartość liczbową 0.18.
• GrandTotal może zawierać formułę SUM używaną w wielu miejscach.

Aby zapisać wartość pod nazwą w Excelu, należy otworzyć Menedżera nazw na karcie Formuły i kliknąć Nowa.

• Proszę wpisać nazwę, na przykład TaxRate.
• W polu Odwołuje się do proszę wpisać =0.18.
• Proszę kliknąć OK.

Warto zauważyć, że wartość wpisuje się z początkowym znakiem równości. Od tej pory TaxRate zachowuje się jak komórka zawierająca wartość 0.18, ale znajduje się w definicjach skoroszytu, a nie w widocznej komórce.

Nazwane zakresy w Google Sheets zawsze wskazują komórki, dlatego najprościej jest umieścić wartość w komórce i nadać nazwę tej komórce.

• Proszę wpisać 0.18 w wolnej komórce, na przykład Z1.
• Proszę zaznaczyć Z1 i otworzyć Dane > Nazwane zakresy.
• Proszę nadać jej nazwę TaxRate.

Rezultat jest taki sam: formuła =Price*TaxRate działa wszędzie, a stawkę można zmienić, edytując tylko jedną komórkę.

Po zdefiniowaniu TaxRate formuły same wyjaśniają swoje znaczenie. Aby dodać do ceny w komórce A2 podatek w wysokości 18%, należy użyć:

=A2*(1+TaxRate)

Największa korzyść pojawia się po zmianie stawki. Wystarczy zaktualizować TaxRate w jednym miejscu, aby każda formuła w skoroszycie została natychmiast ponownie obliczona.

• Nie trzeba wyszukiwać zapisanych na stałe wartości 0.18.
• Nie ma ryzyka pominięcia jednej z nich i uzyskania niespójnych wyników.

Wpisanie na stałe wartości takiej jak 0.18 bezpośrednio w dziesiątkach formuł utrudnia późniejsze utrzymanie skoroszytu.

• Jeśli stawka zmieni się na 20%, trzeba będzie znaleźć i edytować każdą formułę.
• Pominięcie jednej formuły sprawi, że sumy będą się po cichu różnić.
• Czytelnicy nie będą wiedzieć, czy 0.18 oznacza stawkę podatku, rabat, czy literówkę.

Nazwana stała, taka jak TaxRate, rozwiązuje wszystkie trzy problemy: zapewnia jedno źródło prawdy i jasno opisuje wartość.

Nazwa może także przechowywać całą formułę. W Menedżerze nazw w Excelu należy utworzyć nazwę, na przykład SalesTotal, i ustawić pole Odwołuje się do na:

=SUM(Sheet1!$B$2:$B$13)

Od tej pory wpisanie =SalesTotal w dowolnej komórce zwróci tę sumę. Obliczenie zostało opakowane w jedną czytelną etykietę.

Należy używać odwołań bezwzględnych (znaków dolara), aby nazwana formuła zawsze wskazywała te same komórki.

Załóżmy, że często obliczają Państwo marżę zysku. Należy zdefiniować dwie nazwane formuły:

• TotalRevenue → =SUM(Revenue)
• TotalCost → =SUM(Costs)

Formuła marży wygląda wtedy bardzo przejrzyście:

=(TotalRevenue-TotalCost)/TotalRevenue

Każda nazwana formuła zawiera własną logikę, dzięki czemu końcowe obliczenie skupia się na znaczeniu biznesowym, a nie na operacjach na komórkach.

Zaawansowana technika: jeśli w nazwanej formule pozostawi się odwołania względne, nazwa zachowuje się inaczej w zależności od miejsca użycia.

Na przykład nazwa PrevRow zdefiniowana przy aktywnej komórce A2 jako =A1 będzie zawsze wskazywać komórkę znajdującą się bezpośrednio powyżej, niezależnie od miejsca wpisania =PrevRow.

• To rozwiązanie jest przydatne przy powtarzających się wzorcach w dół kolumny.
• Może być trudne do debugowania — należy go używać tylko po zrozumieniu zasady aktywnej komórki.

W miarę rozrastania się skoroszytu lista nazwanych stałych staje się uporządkowanym panelem ustawień. Do często używanych stałych należą:

• TaxRate, DiscountRate, ShippingFee
• CompanyName jako stała tekstowa, na przykład ="Acme Inc"
• StartDate przechowująca ustaloną datę

Pogrupowanie ich w Menedżerze nazw zapewnia jeden centralny panel zawierający wszystkie założenia modelu.

Aby zmienić nazwaną stałą w Excelu, należy otworzyć Menedżera nazw, wybrać nazwę i edytować pole Odwołuje się do. Po naciśnięciu klawisza Enter każda zależna formuła zostanie ponownie obliczona.

W Sheets wystarczy edytować komórkę, której nadano nazwę.

• Jedna zmiana propaguje się w całym skoroszycie.
• To najważniejsza korzyść: jedno źródło prawdy.

Zawsze należy zmieniać definicję zamiast rozprowadzać nowe liczby po formułach.

Warto zachować jasne rozróżnienie między tymi pojęciami:

• Nazwana stała odnosi się do ustalonej wartości, takiej jak =0.18 lub ="Acme Inc". Nie zmienia się, dopóki definicja nie zostanie edytowana.
• Nazwana formuła odnosi się do obliczenia, takiego jak =SUM(Revenue). Jest ponownie obliczana za każdym razem, gdy zmienią się jej dane wejściowe.

Oba rodzaje tworzy się w ten sam sposób w Menedżerze nazw — różnica sprowadza się do zawartości pola Odwołuje się do.

Zdefiniowano nazwę TaxRate, która odnosi się do =0.18. Jaka jest jej główna zaleta w porównaniu z wpisywaniem wartości 0.18 w każdej formule?

Poznali Państwo zasadę, że nazwy mogą przechowywać więcej niż zakresy:

• Nazwana stała, taka jak TaxRate=0.18, centralizuje założenie.
• Nazwana formuła, taka jak SalesTotal=SUM($B$2:$B$13), opakowuje obliczenie w etykietę.
• Jednorazowa aktualizacja definicji odświeża wszystkie zależne formuły.
• W nazwanych formułach należy używać odwołań bezwzględnych, aby pozostały zakotwiczone.

W następnej części poznają Państwo sposób kontrolowania tego, co użytkownicy mogą wpisywać, za pomocą sprawdzania poprawności danych.