Dwukolorowy wykres liniowy

Celem dzisiejszego wpisu będzie przedstawienie sposobu utworzenia wykresu liniowego, którego kolor będzie się zmieniał po przekroczeniu ustalonej przez nas granicy podziału. Efekt, który chcemy osiągnąć przedstawia wykres poniżej:

Przygotowywanie wykresu rozpoczniemy od wpisania w nasz arkusz przykładowych danych. Na wstępie należy zaznaczyć, iż pomimo faktu, iż wykres nazywamy liniowym, do jego przygotowania będziemy wykorzystywać wykres XY-punktowy, stąd będą nam potrzebne dwie serie danych, odpowiadające za wartości X oraz wartości Y każdego z punktów.

Przykładowa tabela danych znajduje się poniżej.

	A	B
1	X	Y
2	1	85
3	2	94
4	3	37
5	4	16
6	5	10
7	6	91
8	7	20
9	8	66
10	9	51
11	10	31

  

Kształt wykresu utworzonego na podstawie danych podanych powyżej będzie odpowiadał kształtem naszemu docelowemu wykresowi, jednak możliwość dodania kolorów wymaga wykonania kilku kroków.

Rozpocznijmy od zdefiniowania wartości granicznej, która będzie decydowała o miejscu podziału wykresu na kolory. Nasz wykres rozdzielimy przy wartości 50, którą definiujemy w komórce C13.

Następnie analogicznie jak robiliśmy to w Rozróżnianiu kolorami danych poniżej i powyżej progu musimy rozdzielić poszczególne punkty Y do odpowiednich serii, tj. mniejszych od wartości granicznej (Przedział 1), bądź większych lub równych (Przedział 2). W tym celu do komórki C2 wpisujemy formułę

=JEŻELI(B2<$C$13;1;2)

A następnie przeciągamy w dół. Formuła na podstawie wartości Y poszczególnych punktów przydziela je do odpowiednich przedziałów. Następnie przy pomocy przed chwilą wyliczonych wartości tworzymy dwa zakresy danych Y. W tym celu w komórkę 

E2 wpiszemy =A2

F2 wpiszemy =JEŻELI(C2=1;B2;BRAK())

G2 wpiszemy =JEŻELI(C2=2;B2;BRAK())

Przeciągnięcie formuł w dół spowoduje powstanie tabeli znanej z wyżej wspomnianego artykułu.

Stworzenie w tej chwili wykresu spowoduje, że już będziemy mieli dwa różne kolory, jednak efekt znacząco odbiega od oczekiwań.

Czas na kolejny krok. Musimy wyznaczyć punkty, w których linia naszego wykresu przecina granicę podziału, bowiem ten punkt będzie zakończeniem serii jednego koloru i jednocześnie rozpoczęciem serii drugiego koloru.

W celu wykonania tych obliczeń, wpisujemy odpowiednie formuły:

w I3 wpiszemy =JEŻELI(C2<>C3;(B2-$C$13)/(B2-B3)*(A3-A2)+A2;BRAK())

w J3 wpiszemy =JEŻELI(CZY.LICZBA(I3);$C$13;BRAK())

w K3 wpiszemy =J3

i przeciągniemy w dół do końca tabeli.

Najważniejszą formułą jest ta z kolumny I, która przez sprawdzenie, czy dla sąsiadujących punktów nie nastąpiła zmiana przedziału decyduje, czy dokonujemy obliczenia punktu zmiany koloru czy nie. W przypadku gdy nie ma konieczności wyliczania punktu zmiany koloru wyświetlony zostaje błąd #N/D!, w przeciwnym wypadku na zasadzie proporcji wyliczamy wartość X dla punktu przecięcia z granicą zdefiniowaną w komórce C13.

Pozostałe dwie funkcje odpowiadają za wstawienie wartości granicznej do obu serii danych w przypadku gdy taki punkt był wyliczany.

W efekcie naszej pracy uzyskaliśmy następujący arkusz:

W tej chwili należy przygotować dane, tak abyśmy mogli przenieść je do naszego wykresu.

W tym celu kopiujemy zakres E2:G11 i wklejamy specjalnie > wartości poniżej naszej tabeli, np. do komórki E15. Następnie pod powstałą po kopiowaniu tabelę (czyli w komórkę E25), w analogiczny sposób wklejamy zakres I3:K11. Cały powstały obszar E15:G33 sortujemy wg wartości z pierwszej kolumny, czyli wg wartości X.

Utworzenie wykresu na podstawie tych danych już znacząco zbliża nas do końca pracy (dla obu serii wykresu komórki z wartościami X dla punktów znajdują się w kolumnie E, z kolei wartości Y są odpowiednio w kolumnie G oraz H).

Ostatnim koniecznym szlifem jest wyczyszczenie w zakresie E15:G33  wszystkich komórek z błędami. Można to zrobić oczywiście ręcznie, jednak bardziej efektywny będzie poniższy sposób.

Zaznaczamy zakres E15:G33, naciskamy klawisz F5, a następnie Specjalne > Stałe i pozostawiamy zaznaczone jedynie Błędy. Po naciśnięciu klawisza OK w podanym przez nas zakresie zostaną zaznaczone wszystkie komórki z wartościami błędu, które teraz można wyczyścić przez naciśnięcie klawisza Delete. Ta czynność spowodowała, iż nasz wykres przybrał ostateczny kształt.

Na koniec należy wskazać jedno istotne ograniczenie przytoczonej metody: wartości X następujących po sobie punktów nie mogą być mniejsze od poprzedniego. Ograniczenie to można znieść, co zostanie pokazane w kolejnych wpisach.