Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 


Poprzedni temat «» Następny temat
ID tematu: 57966 Skopiuj do schowka 63. Jak uzyskać przerwę w wykresie zamiast wartości 0?
Autor Wiadomość
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Posty: 4418
Wysłany: 07-07-2017, 12:35   63. Jak uzyskać przerwę w wykresie zamiast wartości 0?

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 160 raz(y) 54 KB

ID posta: 325823 Skopiuj do schowka
 
 
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.marketingNET.pl

Archiwum

Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe