Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - 63. Jak uzyskać przerwę w wykresie zamiast wartości 0?

Maciej Gonet · Excel Expert Wersja: Win Office 365 Posty: 10323

Temat ten powraca co jakiś czas jak bumerang, gdyż jest to jedna z bardziej uciążliwych niedoróbek Excela. Spotkał się z nim prawie każdy, kto wykonywał wykres na podstawie danych zależnych, czyli opisanych formułami. Jeżeli, z różnych powodów, formuła daje wynik niepożądany (tekst, błąd, 0 będące wynikiem odwołania do pustej komórki), to na wykresie jest on przedstawiany jako zero, co zazwyczaj nie odpowiada naszym intencjom. Wyjątkiem jest wartość błędu #N/D!, która zazwyczaj powoduje interpolację sąsiednich danych, bez przerywania ciągłości wykresu. W przypadku danych nieliczbowych naturalne byłoby raczej zrobienie przerwy w wykresie, ale taki efekt można uzyskać tylko w jeden sposób: przez umieszczenie w zakresie danych wykresu odwołania do pustej komórki.
Powszechną wiedzę na ten temat zebrał i podsumował m.in. Jon Peltier w artykule Mind the Gap - Charting Empty Cells. Wynika z niego jeden pocieszający wniosek, że po apelach użytkowników M$ zapisał ten problem na liście planowanych do rozwiązania w kolejnej wersji Excela. Ale zanim doczekamy się magicznej funkcji NULL(), można próbować jakoś ten problem obejść dysponując tym co mamy obecnie. I, wbrew pozorom, sposobów rozwiązania jest co najmniej kilka, chociaż nie są one najwygodniejsze w użyciu.
Zanim przejdę do ich omówienia trzeba sprecyzować warunki graniczne, to znaczy określić jakie jest pole manewru przy przekazywaniu danych do utworzenia wykresu. Źródłem danych do wykresu może być zarówno tablica danych, jak i zakres komórek, w tym zakres złożony (nieciągły), z tym że nie można mieszać tych dwóch rodzajów danych. Ponieważ efekt przerwy w wykresie może dać tylko odwołanie do pustej komórki, a nie żadna formuła zwracająca wartość, więc wynika stąd, że źródłem danych do takiego wykresu musi być odwołanie do zakresu komórek. Ponadto należy pamiętać, że wszystkie odwołania w definicji danych wykresu muszą być kwalifikowane nazwą arkusza lub skoroszytu. Idea byłaby więc taka: testujemy dane do wykresu komórka po komórce i jeśli napotkamy wartość nieliczbową (ewentualnie z wyjątkiem błędu #N/D!), przekierowujemy źródło danych na pustą komórkę.
Wydawać by się mogło, że wystarczy użyć formuły:

Kod:

= JEŻELI(CZY.LICZBA(dane)+CZY.BRAK(dane); dane; pusta)

Oznaczenie dane reprezentuje zakres danych, natomiast pusta oznacza odwołanie do dowolnej pustej komórki w tym samym arkuszu. Niestety, aby ta formuła zadziałała zgodnie z oczekiwaniem musiałaby zwrócić odwołanie do komórki pustej, a nie jej wartość. Tymczasem gdy pierwszym argumentem funkcji JEŻELI jest tablica, funkcja zwraca tablicę wartości, a nie odwołania. Aby w wyniku uzyskać odwołania, pierwszy argument musi być pojedynczą wartością. Można to osiągnąć, dzieląc przedział danych na pojedyncze komórki i wykonując sprawdzenia osobno w każdej z nich. Tu właśnie przydaje się koncepcja zakresu złożonego, którego zakresy składowe są określone formułami. W praktyce wygodniej jest nadać tym formułom nazwy.
Zakres dane można podzielić na elementy wypisując wprost odpowiednie adresy, posługując się funkcją INDEKS lub funkcją PRZESUNIĘCIE. Następnie formuły reprezentujące wyniki należy połączyć w jeden zakres złożony.
Idea byłaby taka. Każda formuła będzie miała nazwę y.<numer_punktu>. Teksty tych nazw te wygenerujemy za pomocą formuł:

Kod:

="y."&WIERSZ(A1)

z kopią w dół. W sąsiedniej kolumnie wygenerujemy formułami teksty formuł, które następnie przypiszemy do tych nazw. Będą to formuły o strukturze podobnej do podanej poprzednio.

Kod:

= "=JEŻELI(CZY.LICZBA(INDEKS(dane;"&WIERSZ(A1)&"))+CZY.BRAK(INDEKS(dane; "&WIERSZ(A1)&")); INDEKS(dane;"&WIERSZ(A1)&"); pusta)"

Formułę kopiujemy w dół tyle razy, ile danych mamy przedstawić na wykresie. W następnej kolumnie tworzymy tekst odwołania złożonego, wykorzystującego przygotowane nazwy. W pierwszym wierszu będzie to:

Kod:

= "=" & nazwa & ";"

w następnym:

Kod:

= poprzednia & nazwa & ";"

w ostatnim:

Kod:

= poprzednia & nazwa

W tych formułach nazwa oznacza względne odwołanie do komórki z tekstem nazwy (z tego samego wiersza), poprzednia — jest odwołaniem do komórki powyżej, zawierającej utworzony dotychczas fragment tekstu odwołania. W ostatnim wierszu otrzymamy pełny tekst odwołania złożonego, któremu również nadamy nazwę, na przykład y0.
Niestety funkcja wspomagająca nadawanie nazw Utwórz z zaznaczenia jest tu nieprzydatna, gdyż pozwala ona tylko na nadawanie nazw odwołaniom do komórek, a tu trzeba nadać nazwy formułom, których teksty wygenerowano w komórkach. Można to oczywiście zrobić ręcznie, za pomocą Menedżera nazw, ale wygodniej w tym przypadku napisać proste makro, ułatwiające wykonanie tego zadania. Należy tylko zwrócić uwagę, że jeśli chodzi o teksty nazw, to są one zawsze pobierane jako wartości, natomiast definicje nazw mogą być pobrane jako wartości lub jako formuły. Tu muszą być pobrane jako wartości, gdyż te wartości dopiero stanowią teksty formuł, które chcemy wykorzystać.
Wspomniane makro może wyglądać tak:

Kod:

Sub NazwijFormuły()
With Selection
For i = 1 To .Rows.Count
Names.Add .Cells(i,1), RefersToLocal:=.Cells(i,2).Value
Next i
End With
End Sub

Przed uruchomieniem makra należy zaznaczyć w arkuszu zakres komórek obejmujący teksty nazw w pierwszej kolumnie i treści ich definicji w drugiej kolumnie. Ze względu na błąd VBA dotyczący interpretacji adresów w formułach zapisanych w języku narodowym, w treści formuły nie należy używać adresów, a wyłącznie nazwy.
Pozostaje już tylko wykorzystać zdefiniowaną nazwę y0 do definicji wykresu.

To była pierwsza propozycja, która wykorzystywała klasyczny zakres złożony. Jest ona dość skomplikowana, ale wydaje się dość uniwersalna, jeśli chodzi o używane wersje Excela.
Wygodniejszym rozwiązaniem byłoby przeniesienie całego tworzenia zakresu złożonego do VBA i wykorzystanie do tego metody Union. Należy jednak pamiętać o różnicach między klasycznym zakresem złożonym, w którym wymieniamy kolejno jego elementy, oddzielając je separatorami listy, a zakresem złożonym tworzonym za pomocą metody Union. W klasycznym rozwiązaniu elementy składowe zakresu mogą się powtarzać i w związku z tym wystarczy jedna "dyżurna" pusta komórka, której adres możemy wykorzystać kilkukrotnie. Metoda Union "optymalizuje" tworzony zakres łącząc przylegające obszary i eliminując powtórzenia, w związku z tym tu musimy mieć przygotowany zapas pustych komórek, każdą z nich można wykorzystać tylko raz. W związku z tym przyjąłem, że obok kolumny z danymi wyjściowymi będzie pusta kolumna, do komórek której można się zawsze odwołać w razie potrzeby.
Proponuję rozwiązanie w postaci funkcji UDF, której argumentem będzie źródłowy zakres danych do wykresu. Funkcja zwraca zakres, z reguły złożony, w którym odwołania do komórek z nieodpowiednią zawartością zostały podmienione. Temu wynikowemu zakresowi należy nadać nazwę, a następnie użyć tej nazwy do definicji wykresu.
Treść funkcji UDF i pomocniczego podprogramu:

Kod:

Function Newy(y As Range) As Range
Dim cel As Range
For Each cel In y
AddCell IIf(Application.IsNumber(cel) Or IsEmpty(cel) Or _
Application.IsNA(cel), cel, cel.Offset(, 1)), Newy
Next cel
End Function
Private Sub AddCell(what As Range, res As Range)
If res Is Nothing Then
Set res = what
Else
Set res = Union(res, what)
End If
End Sub

Niestety, ta metoda nie jest tak uniwersalna jak poprzednia. W Excelu 2007+ wystarczy zdefiniować nazwę w Menedżerze nazw i przypisać jej wywołanie funkcji Newy z zakresem danych jako argumentem, a następnie użyć tej nazwy (kwalifikowanej nazwą skoroszytu) w definicji danych do wykresu. W Excelu 2003 (wcześniejszych nie sprawdzałem) to tak nie działa. Jest jakaś niekompatybilność w formatach zapisu zakresów nieciągłych. Tu trzeba do nadania nazwy wykorzystać małe makro, na przykład:

Kod:

Sub NazwijZakres()
Names.Add "yd", Newy(Range("zy"))
End Sub

To makro nadaje nazwę yd nowemu zakresowi utworzonemu z oryginalnego zakresu danych zy. Tę nazwę należy wykorzystać do konstrukcji wykresu. W tym przypadku po każdej zmianie danych wymagającej korekty zakresu należy uruchomić to makro, ewentualnie można napisać stosowną procedurę obsługi zdarzenia Change.
Istnieją jeszcze inne sposoby z wykorzystaniem VBA. Polegają one na użyciu makroinstrukcji, która po uruchomieniu tworzy kopię danych do wykresu w innym ustalonym miejscu arkusza, pomijając dane nieliczbowe. Można również rozważać wariant usuwający zawartość komórek z niewłaściwymi danymi wprost z zakresu danych wykresu, ale to gorsze rozwiązanie, bo później trzeba odtwarzać skasowane formuły.

Wykres_z_przerwą_forum.xls

Pobierz Plik ściągnięto 566 raz(y) 54 KB