W poprzednim artykule o przydatnych funkcjach Excel zaprezentowałem ciekawe połączenia funkcji i dzięki temu ich dodatkowe możliwości. Tym razem pozostaję w temacie formuł, jednak będą to już pojedyncze formuły, ale jakże przydatne i wielofunkcyjne!

[grwebform url="https://app.getresponse.com/view_webform_v2.js?u=N3vX&webforms_id=9648602" css="on" center="off" center_margin="0"/]

WEŹDANETABELI

Jest to funkcja, która pozwala przechwycić dane z raportu tabeli przestawnej o ile są one w nim widoczne. Tzn. aby pobrać daną z tabeli przestawnej przy pomocy funkcji WEŹDANETABELI dane te powinny być w tej tabeli widoczne.

Funkcja jest generowana automatycznie (tu znajdziesz informację jak ją włączyć i wyłączyć) , gdy w formule chcę użyć jedną z wartości znajdujących się w raporcie tabeli przestawnej.

– pole_danych – argument wymagany, wskazuje pole danych z którego mają być wyodrębnione dane

– tabela_przestawna – argument wymagany – wskazuje na komórkę w której rozpoczyna się raport tabeli przestawnej

– pole1 – nazwa pola do którego następuje odwołanie

– element1 – jest to jeden z elementów pola1 do którego się odwołuję

Odwołując się do konkretnej wartości w tabeli przestawnej tworzę odwołanie, które jednoznacznie identyfikuje w jakim miejscu w tabeli przestawnej znajduje się dany element – formuła pokazuje na przecięciu jakich pól, dla jakich wartości elementów tych pól, znajduje się wybrana przeze mnie komórka.

Zastosowanie

Formuła tak jak większość prezentowanych w tym artykule oczywiście do zastosowania w budowie wszelkiego rodzaju raportów oraz analiz. W szczególności kiedy tabela przestawna stworzona w pliku służy do różnego rodzaju zestawień, zawiera wiele danych a do naszych celów potrzebne są tylko niektóre z nich. Warto zastosować tę funkcję by nie budować kolejnej tabeli przestawnej, która na pewno obciąży i powiększy plik.

Zatem budując tabelę przestawną, która będzie służyła celom raportowym, analitycznym wielu zestawień, należy przemyśleć jej budowę, tak by sprostała wszelkim wymogom informacyjnym.

Przykład

Powyżej tabela przestawna, zbudowana na danych z arkusza Dane. W komórce B18 znajduje się automatycznie wygenerowana formuła WEŹDANETABELI po kliknięciu w komórkę E12 raportu tabeli przestawnej. Widać, że wartość z komórki E12 znajduje się na przecięciu Pola Dealer o wartości PL088, Pola Miasto elementu o wartości Wrocław oraz pola Lata o wartości 2004.

W pliku znajdziecie rozwiązanie jak łatwo można przekształcić tę formułę by pokazywała dane dla poszczególnych Dealerów w zależności od wybranego roku.

SUMA.WARUNKÓW

Funkcja często używana przez analityków ponieważ pozwala na sumowanie wartości na podstawie kilku kryteriów (a dokładnie 127 kryteriów z odpowiadającymi im zakresami). Jako kryterium wyszukiwania mogę podać naprawdę wiele różnych kombinacji np. stosując znaki specjalne, mogę sumować wartości, które będą się zaczynały od danej litery, czy też będą większe/mniejsze/różne od pewnej podanej przeze mnie wartości.

– Suma_zakres – argument wymagany, jest to zakres komórek które po spełnieniu kryteriów będą zsumowane

– Kryteria_zakres1 i kolejne – zakres1 jest argumentem wymaganym , kolejne są opcjonalne, jest to zakres który jest przeszukiwany pod kątem kryterium1 i kolejnych. Każde Kryteria_zakres(nr) oraz Kryteria(nr) tworzą parę. Więc dane kryterium jest poszukiwane w zakresie ze swojej pary.

– Kryteria1i kolejne – Kryteria1 jest argumentem wymaganym, kolejne są opcjonalne jest to wartość która poszukiwana jest w kryterium_zakres. Może być zapisane przy pomocy symboli wieloznacznych, znaków większości i mniejszości

Zastosowanie

Do wszelkiego rodzaju zestawień, oczywiście do podsumowań 🙂

UWAGA!!!

Należy pamiętać aby zarówno Suma_zakres jak i Kryteria_zakres miały taki sam zakres wierszy (zaczynały i kończyły na tym samym numerze wiersza).

Przykład

Podsumowanie wartości sprzedaży dla poszczególnych dealarów.

Suma wartości sprzedaży aut typu Limuzyna, których wartość zakupu była większa niż 90000. Należy zwrócić uwagę na sposób zapisu warunku > 90000 (umieszczam go w cudzysłowie).

JEŻELI.BŁĄD

Ta funkcja jest o tyle magiczna, że ładnie pozwala na ukrycie wszystkich pojawiających się „krzaczków” czyli #N/D!, #ADR!, NAZWA itp. Pozwala na zachowanie porządku i estetyki we wszelkich raportach oraz zestawieniach. Jak widać poniżej funkcja posiada dwa argumenty :

Wartość – czyli ta część formuły, której wynik może być błędny

Wartość jeżeli błąd – co powinno się pojawić zamiast komunikatu błędu, innymi słowy co chcę wyświetlić w momencie gdy pojawi się błąd np. 0, „” (czyli nic), bądź komunikat słowny – tu wszystko zależy od twórcy formuły.

UWAGA!!!

Ponieważ funkcja pozwala na ukrycie błędu, warto na początku przetestować formułę, którą chcemy zagnieździć w JEŻELI.BŁĄD. Istnieje ryzyko, że sama konstrukcja formuły może być błędna. Zagnieżdżając ją od razu w JEŻELI.BŁĄD możemy nie dostrzec pomyłki. Także funkcję zagnieżdżamy w JEŻELI.BŁĄD dopiero po sprawdzeniu właściwej (zagnieżdżanej) formuły.

Ciekawostka

W Excelu istnieją tzw. funkcje zmienne – są obliczane przy każdym obliczaniu arkusza, nawet gdy wspomniane formuły zmienne nie są zaangażowane w to liczenie. Do funkcji tych zaliczyć można:

– LOS

– KOMÓRKA (zwraca informację o formatowaniu, lokalizacji lub zawartości pierwszej komórki w odwołaniu zgodnie z kolejnością odczytu arkusza)

– ADR.POŚR

– INFO (zwraca informacje na temat środowiska w jakim działa program, poniżej informacje, jakie dzięki tej funkcji możemy uzyskać)

– TERAZ

– PRZESUNIĘCIE

– DZIŚ

Użycie tych funkcji powoduje pojawienie się monitu o zapisaniu arkusza , przy każdorazowym jego zamknięciu, nawet w momencie gdy nie był on zmieniany po otwarciu.