Co zrobić, jeżeli nie mamy danych za pewien okres? Proste pytanie daje wiele możliwości rozwiązania, zwłaszcza w kontekście pracy w programie Excel. Szczegółowo spojrzymy na ten problem, uważając na odstępy (stąd słynne „Mind the gap” z londyńskiego metra) i kładąc nacisk na obiektywne pokazywanie rzeczywistości. W artykule mowa też o schowanej opcji Ukryte i puste komórki.

[vc_message message_box_color=”grey”]

Aktualizacja z 2018 r.

Excel 365 otrzymał nową funkcję, która rozwiązuje opisany w poniższym artykule problem dla wykresów liniowych z formułą. Szczegóły znajdziesz w artykule nt. tej nowej funkcjonalności Excel 365.

[/vc_message]

Jak obiektywnie pokazać brak danych na wykresie?

Jeżeli brakuje nam danych, należy w tym miejscu zostawić puste miejsce. W poniższym przykładzie brakuje danych dla czwartku:

Jak Excel pokazuje brak danych na wykresie?

Excel pokazuje dane w zależności od tego, co zawiera komórka:

• wariant 1 – komórki zawierają po prostu dane wpisane ręcznie
• wariant 2 – komórki zawierają pusty tekst („”) w miejsce pustych elementów
• wariant 3 – komórki zawierają formułę BRAK(), która generuje błąd #N/D!

Dodatkowo ukryta jest ostatnia kolumna zbioru danych dla niedzieli.

Domyślnie powyższe opcje prezentują się na wykresie liniowym i kolumnowym następująco:

Wniosek: gdy posługujemy się danymi w komórkach, Excel działa poprawnie. W przypadku formuł, nie ma dobrego automatycznego rozwiązania. Mniejszym złem jest posłużenie się dla wykresu liniowego formułą zwracającą błąd, co niekorzystnie wygląda na poziomie tabeli z danymi, ale usuwa punkt z wykresu. Aby zachować pełny obiektywizm, należy dodatkowo ręcznie sformatować linię dla punktu danych Piątek.

Dla wykresu kolumnowego trzeba by ręcznie usunąć etykietę danych z wykresu, gdyż Excel traktuje tekst jak wartość = 0 i w ten sposób wyświetla na wykresie.

Korzystanie z opcji Ukryte i puste komórki

W Excelu mamy możliwość sterowania, w jaki sposób Excel ma traktować ukryte komórki. Opcję tę znajdziemy poprzez Zaznacz dane > Ukryte i puste komórki.

Opcja 1. jest domyślna i została przedstawiona powyżej.

Opcja 2. sprawia, że puste komórki (przy czym komórek z formułą Excel nie traktuje jak pustych) są widoczne jako wartość 0. W wariancie 2. i 3. nic się nie zmienia.

Opcja 3., dostępna tylko dla wykresu liniowego, pozwala pominąć brakujące punkty, co da efekt, jak w opcji 1. dla wariantu 3. z formułą BRAK().

Opcja 4. pozwala w każdym wariancie pokazać na wykresie ukryte komórki.

Podsumowując Ustawienia ukrytych i pustych komórek, opcja ta cały czas nie rozwiązuje problemu pokazywania na wykresie wyniku formuły, nad czym Microsoft pewnie będzie pracował w kolejnych odsłonach programu Excel. Nam zostaje poprawić taki wykres ręcznie.

Plik do pobrania

Tu możesz pobrać plik Excel z różnymi opcjami komórek ukrytych i pustych.