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.
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.
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.
„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.”
Oczywiście, że jest mniejszym złem. A dla przyzwoitości warto dodać, że wygląd tabeli z danymi łatwo poprawić przy użyciu formatowań warunkowych (białe #N/D! na białym tle wygląda całkiem znośnie).
🙂