Czyli kilka słów o funkcjach ZAOKR.DO.TEKST i KWOTA

Zadanie na dziś to stworzenie wykresu sprzedaży po miesiącach, z dodatkowymi informacjami o średniej, najwyższej najniższej sprzedaży miesięcznej. Natomiast te dodatkowe informacje chcemy przedstawić pod wykresem, w polach tekstowych. O tak:

Mało tego. Te dane mają się automatycznie wyliczać i pobierać z danych źródłowych do wykresu. No powiedzmy, że na podstawie nich :). Chodzi o to, by jak najmniej się narobić i aby ta formatka posłużyła nam do kolejnych analiz, np. na kolejny rok czy miesiąc (jeśli dane dochodziłyby miesięcznie).

… jakiś problem? 😉

To się zaraz okaże… Ale najpierw – formatka. Oto ona:

Formatka

W żółtych polach wpiszemy odpowiednie formuły dla średniej, min i max, a następnie ich wyniki wyświetlimy w polach tekstowych na wykresie.

Formuły wydawać się mogą proste: średnia, min i max – są przecież do tego odpowiednie funkcje. Tak, są i z nich właśnie skorzystamy. Ale to nie będzie koniec…

Zacznijmy od średniej – pozostałe zrobimy na takiej samej zasadzie.

Na wykresie chcemy wyświetlić: Avg: 1 234. Czyli połączenie tekstu z liczbą. Nie ma problemu – połączymy 2 ciągi tekstowe:

• „Avg: ” i
• wynik funkcji ŚREDNIA dla zakresu miesięcy (C4:C15)

Formuła, która to połączy wygląda następująco:

="Avg: "&ŚREDNIA(C4:C15)

Problem jest jednak taki, że w wyniku otrzymamy niesformatowaną wartość średniej, ponieważ formuły nie odczytują formatowania z komórek:

Chcemy inne formatowanie liczby…

Jak sobie z tym poradzić? Do niedawna pierwsza moja myśl była: „no pewnie, że funkcją TEKST„. Ale teraz, po obejrzeniu tego filmu Mike’a Girvina, mówię: ZAOKR.DO.TEKST albo KWOTA!

Obie te funkcje, a z TEKSTEM to i wszystkie trzy :), potrafią wyświetlić liczbę w pożądanym „formacie”. Każda zwróci w wyniku tekst i po prostu wstawi spacje tam, gdzie trzeba. A właśnie o to nam chodzi.

Funkcja ZAOKR.DO.TEKST

Zacznijmy od ZAOKR.DO.TEKST, która jest odpowiednikiem formatu liczbowego. Funkcja ta jednak, oprócz formatowania, potrafi też zaokrąglać. Jeśli więc trzeba byłoby zaokrąglić do pełnych np. setek – też możemy to zrobić tą funkcją i dodatkowo wyświetlić w ładnym „formacie”. My tutaj nie potrzebujemy aż tak zaokrąglać, wystarczy niewyświetlanie miejsc dziesiętnych. Formuła zatem będzie najprostsza z możliwych, czyli taka:

="Avg: "&ZAOKR.DO.TEKST(ŚREDNIA(C4:C15);0)

W pierwszym argumencie funkcji ZAOKR.DO.TEKST jest nasza średnia (ją chcemy wyświetlić z separatorem tysięcy) i ma być ona wyświetlona z zerem miejsc po przecinku, czyli drugi argument to zero. Jakby były jakieś dziesiętne – zostałyby zaokrąglone do pełnych złotych, dokładnie jak w funkcji ZAOKR, czyli matematycznie. Ostatni – opcjonalny – argument pominęłam, ponieważ w domyślnym działaniu tej funkcji – wyświetla ona separator tysięczny, czyli spację. Zdecydowanie ją chcemy, o to przecież chodzi w całej tej zabawie.

To samo oczywiście trzeba zrobić z dwoma pozostałymi wskaźnikami, czyli min i max:

Tak będzie dla MIN:

="Min: "&ZAOKR.DO.TEKST(MIN(C4:C15);0)

A tak dla MAX:

="Max: "&ZAOKR.DO.TEKST(MAX(C4:C15);0)

Funkcja KWOTA

Kolejnym moim odkryciem jest funkcja KWOTA, która co do zasady robi to samo, co ZAOKR.DO.TEKST, tylko dorzuca walutę: zł (zależy od ustawień regionalnych w systemie operacyjnym!). czyli jest funkcyjnym odpowiednikiem formatu walutowego. Też potrafi zaokrąglać, nawet przed przecinkiem (czyli np. do pełnych setek). Tutaj zawsze będziemy widzieli separatory tysięczne – tak, jak w pieniądzach lubimy widzieć.

Jej składnia jest bardzo prosta – potrzebuje od na liczby do zaokrąglenia/przedstawienia jako walutę i sposobu zaokrąglenia. My potrzebujemy wyświetlać pełne liczby, cyzli zaokrąglamy do 0 miejsc po przecinku. Dla średniej wygląda to tak:

="Avg: "&KWOTA(ŚREDNIA(C4:C15);0)

A dla min i max będziemy postępować analogicznie.

I na koniec, dla porządku, zrobimy to samo, tylko funkcją TEKST. Jest to funkcja, która może wyświetlić praktycznie dowolne formatowanie liczby (szerzej mówię o tym podczas tego webinaru). Potrafi więc w prosty sposób zaokrąglić. W prosty to znaczy, że poradzi sobie z miejscami dziesiętnymi, ale już z zaokrągleniem do pełnych setek nie (ale umie wyświetlić w pełnych tysiącach ;)). Tak, jak formatowanie. Też tego nie umie.

Funkcja ta technicznie wymaga od nas podania liczby, którą chcemy „formatować” i sposobu tego formatowania (jako tekst). Jej wynikiem też będzie tekst. Tak należy jej użyć w naszej sytuacji:

="Avg: "&TEKST(ŚREDNIA(C4:C15);"# ##0")

Powyższe to jest wersja, gdy chcemy samą liczbę. A poniżej, gdybyśmy chcieli jeszcze walutę:

="Avg: "&TEKST(ŚREDNIA(C4:C15);"# ##0 zł")

Wykres

Ok, formułę mamy, teraz już łatwiejsza część – wykres. Tworzymy go z zakresu B3:C15 – wystarczy się w nim ustawić. A następnie użyć skrótu klawiszowego Alt + F1. Powstawnie wtedy wykres domyślny: jeśli nic nie zmienialiśmy – będzie to wykres kolumnowy:

Domyślny wykres

Zdecydowanie trzeba usunąć z niego legendę.

Następnie przychodzi kolej na pola tekstowe, w których umieścimy wyniki naszych formuł. Pola tekstowe (menu Wstawianie/Kształty) wystarczy narysować w obrębie wykresu, w wybranym przez Ciebie czytelniku miejscu. O tak:

Rysowanie pola tekstowego wewnątrz wykresu

Następnie trzeba wykonać prace plastyczno-techniczne i upiększyć to pole tekstowe. Ja na pewno usuwam obramowanie i zmieniam kolor wypełnienia na biały lub brak (czyli przeźroczysty).

A potem już tylko podpiąć wartość pod to pole – robimy to identycznie jak dynamiczny tytuł wykresu, który opisywałam w tym artykule.

Takie gotowe pole tekstowe teraz kopiujemy dwa razy, podmieniamy odwołania – dla min i dla max.

Następnie ustawiamy w wybranym przez nas miejscu wykresu (można użyć narzędzi do wyrównywania kształtów: menu Formatowanie kształtu/ Wyrównaj), ewentualnie jeszcze formatujemy czcionkę (ja dałam na złoto ;)) i GOTOWE!

Taki efekt uzyskujemy:

WYNIK

I wszystko jest dynamiczne, więc jak dojdą dane czy zmienią się – wykres się pięknie zaktualizuje!

Enjoy!

Powiązane produkty

• Webinar: Formatowanie niestandardowe, podczas którego tłumaczę zasady rządzące formatowaniem, i których możemy użyć w funkcji TEKST.
• Webinar: Funkcje tekstowe – przegląd, podczas którego poznasz inne przydatne funkcje tekstowe, w tym takie, które mogą być świetną alternatywą dla… funkcji JEŻELI!

 Plik z gotowcem do pobrania:

MalinowyExcel Zachowaj format liczbowy w formule dw.xlsx

I wersja wideo na YT: