Celem dzisiejszego wpisu będzie przedstawienie sposobu utworzenia wykresu liniowego, którego kolor będzie się zmieniał po przekroczeniu ustalonych przez nas granic przedziałów. Jest to kontynuacja poprzedniego wpisu Dwukolorowy wykres liniowy. W tej chwili chcemy przygotować ten sam wykres, jednak dla kilku granic, w efekcie czego chcemy osiągnąć:
Jako dane do przygotowania wykresu wykorzystamy następujące dane:
|
|
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
|
oraz wstawimy w zakresie C13:D13 granice podziału. Granice przyjąłem jako 30 i 60 odpowiednio dla komórek C13 i D13. Dla celów niniejszego wpisu wyjaśniam utworzenie wykresu z dwoma granicami, Jeżeli jesteśmy zainteresowani większą ich liczbą, na końcu wpisu znajduje się informacja jak wykonać modyfikację schematu.
W dalszej kolejności musimy ustalić przedziały w których punkty naszego wykresu się znajdują. W tym celu w komórkę
W dalszej kolejności musimy ustalić przedziały w których punkty naszego wykresu się znajdują. W tym celu w komórkę
C2 wpiszemy formułę =JEŻELI(CZY.BŁĄD(PODAJ.POZYCJĘ(B2;$C$13:$D$13));0;PODAJ.POZYCJĘ(B2;$C$13:$D$13))+1
i przeciągniemy w dół. Aby osiągnąć ten sam efekt możemy zagnieżdżać kolejne funkcje JEŻELI, jednak dla wielu granic efekt ten będzie bardziej pracochłonny.
Powyższa formuła PODAJ.POZYCJĘ zwraca pozycję pierwszej liczby która jest mniejsza od wyszukiwanej przez nas. Innymi słowy nasza wyszukiwana liczba powinna się znaleźć w przedziale następnym od liczby zwróconej przez PODAJ.POZYCJĘ, stąd +1 na końcu funkcji. Ponieważ funkcja PODAJ.POZYCJĘ, dla liczb które powinny się znaleźć w pierwszym przedziale (w naszym przypadku dla liczb mniejszych od 30, czyli niższej granicy), zamiast zwrócić 0 zwróci błąd, konieczne było zastosowanie funkcji JEŻELI oraz CZY.BŁĄD, które w tym przypadku odpowiadają za zwrócenie wartości 0. Dla Excela 2007 i późniejszych możliwe jest wykorzystanie funkcji JEŻELI.BŁĄD w postaci:
=JEŻELI.BŁĄD(PODAJ.POZYCJĘ(B2;$C$13:$D$13);0)+1
Następnym krokiem jest przydzielenie liczb do odpowiednich przedziałów. Procedura jest analogiczna jak dla dwóch przedziałów z tymże mamy jedną serię więcej (dwie granice stąd trzy przedziały). Wpisujemy następujące formuły:
w E2 wpisujemy =A2
w F2 wpisujemy =JEŻELI(C2=1;B2;BRAK())
w G2 wpisujemy =JEŻELI(C2=2;B2;BRAK())
w H2 wpisujemy =JEŻELI(C2=3;B2;BRAK())
Dalszy etap prac polega na ustaleniu punktów przecięcia linii wykresu z wszystkimi granicami. W naszym przypadku mamy dwie granice (30 i 60) stąd czynność będziemy musieli wykonać dwukrotnie.
Punkty przecięcia linii wykresu z pierwszą granicą wyliczymy w zakresie I3:L11, a punkty przecięcia z granicą drugą w zakresie M3:P11. Podobnie jak przy wykonywaniu wykresu dwukolorowego, tak i tutaj, najważniejsza jest formuła wyliczająca wartość X punktu przecięcia podana poniżej. Dla komórki
I3 formuła =JEŻELI(LUB(ORAZ(C2<=1;C3>1);ORAZ(C2>1;C3<=1));(B2-$C$13)/(B2-B3)*(A3-A2)+A2;BRAK())
M3 formuła =JEŻELI(LUB(ORAZ(C2<=2;C3>2);ORAZ(C2>2;C3<=2));(B2-$D$13)/(B2-B3)*(A3-A2)+A2;BRAK())
Jak można zauważyć, kalkulując punkty przecięcia pomiędzy kolejnymi granicami zmianie ulega jedynie numer dolnego przedziału w funkcjach ORAZ (w pierwszym przypadku wartość 1, w drugim 2), jak również odniesienie do wartości granicznej (odpowiednio $C$13 i $D$13).
Pozostałe formuły dla przecięcia pomiędzy przedziałami 1 i 2 przedstawiam poniżej
J3 to =JEŻELI(CZY.LICZBA(I3);$C$13;BRAK())
K3 to =J3
L3 pozostawiamy puste, bowiem granica jest pomiędzy przedziałami 1 i 2, wyliczamy wartości jedynie dla tych serii, seria trzecia pozostaje pusta
Formuły dla przecięcia pomiędzy przedziałami 2 i 3 przedstawiają się następująco
N3 pozostawiamy puste, z analogicznego powodu jak wyżej
O3 to =JEŻELI(CZY.LICZBA(M3);$D$13;BRAK())
P3 to =O3
Finalnym efektem naszych obliczeń jest arkusz
i przeciągniemy w dół. Aby osiągnąć ten sam efekt możemy zagnieżdżać kolejne funkcje JEŻELI, jednak dla wielu granic efekt ten będzie bardziej pracochłonny.
Powyższa formuła PODAJ.POZYCJĘ zwraca pozycję pierwszej liczby która jest mniejsza od wyszukiwanej przez nas. Innymi słowy nasza wyszukiwana liczba powinna się znaleźć w przedziale następnym od liczby zwróconej przez PODAJ.POZYCJĘ, stąd +1 na końcu funkcji. Ponieważ funkcja PODAJ.POZYCJĘ, dla liczb które powinny się znaleźć w pierwszym przedziale (w naszym przypadku dla liczb mniejszych od 30, czyli niższej granicy), zamiast zwrócić 0 zwróci błąd, konieczne było zastosowanie funkcji JEŻELI oraz CZY.BŁĄD, które w tym przypadku odpowiadają za zwrócenie wartości 0. Dla Excela 2007 i późniejszych możliwe jest wykorzystanie funkcji JEŻELI.BŁĄD w postaci:
=JEŻELI.BŁĄD(PODAJ.POZYCJĘ(B2;$C$13:$D$13);0)+1
Następnym krokiem jest przydzielenie liczb do odpowiednich przedziałów. Procedura jest analogiczna jak dla dwóch przedziałów z tymże mamy jedną serię więcej (dwie granice stąd trzy przedziały). Wpisujemy następujące formuły:
w E2 wpisujemy =A2
w F2 wpisujemy =JEŻELI(C2=1;B2;BRAK())
w G2 wpisujemy =JEŻELI(C2=2;B2;BRAK())
w H2 wpisujemy =JEŻELI(C2=3;B2;BRAK())
Dalszy etap prac polega na ustaleniu punktów przecięcia linii wykresu z wszystkimi granicami. W naszym przypadku mamy dwie granice (30 i 60) stąd czynność będziemy musieli wykonać dwukrotnie.
Punkty przecięcia linii wykresu z pierwszą granicą wyliczymy w zakresie I3:L11, a punkty przecięcia z granicą drugą w zakresie M3:P11. Podobnie jak przy wykonywaniu wykresu dwukolorowego, tak i tutaj, najważniejsza jest formuła wyliczająca wartość X punktu przecięcia podana poniżej. Dla komórki
I3 formuła =JEŻELI(LUB(ORAZ(C2<=1;C3>1);ORAZ(C2>1;C3<=1));(B2-$C$13)/(B2-B3)*(A3-A2)+A2;BRAK())
M3 formuła =JEŻELI(LUB(ORAZ(C2<=2;C3>2);ORAZ(C2>2;C3<=2));(B2-$D$13)/(B2-B3)*(A3-A2)+A2;BRAK())
Jak można zauważyć, kalkulując punkty przecięcia pomiędzy kolejnymi granicami zmianie ulega jedynie numer dolnego przedziału w funkcjach ORAZ (w pierwszym przypadku wartość 1, w drugim 2), jak również odniesienie do wartości granicznej (odpowiednio $C$13 i $D$13).
Pozostałe formuły dla przecięcia pomiędzy przedziałami 1 i 2 przedstawiam poniżej
J3 to =JEŻELI(CZY.LICZBA(I3);$C$13;BRAK())
K3 to =J3
L3 pozostawiamy puste, bowiem granica jest pomiędzy przedziałami 1 i 2, wyliczamy wartości jedynie dla tych serii, seria trzecia pozostaje pusta
Formuły dla przecięcia pomiędzy przedziałami 2 i 3 przedstawiają się następująco
N3 pozostawiamy puste, z analogicznego powodu jak wyżej
O3 to =JEŻELI(CZY.LICZBA(M3);$D$13;BRAK())
P3 to =O3
Finalnym efektem naszych obliczeń jest arkusz
Następnie podobnie jak dla dwukolorowego wykresu wszystkie wyliczone zakresy danych, czyli E2:H11, I3:L11 oraz M3:P11 musimy posortować rosnąco wg wartości X. W tym celu:
- zakres E2:H11 wklejamy specjalnie jako wartości do komórki E15,
- zakres I3:L11 wklejamy poniżej, czyli do komórki E25,
- zakres M3:P11 do komórki E34.
Następnie cały zakres E15:H42 sortujemy rosnąco według wartości z kolumny E, a następnie czyścimy wszystkie komórki z błędami.
Finalnie na podstawie otrzymanej tabeli tworzymy wykres XY-punktowy z trzema seriami danych, gdzie każda seria ma wartości X w kolumnie E, a wartości Y odpowiednio w kolumnach F do H. Po drobnym formatowaniu wykresu otrzymamy zaprezentowany na wstępie wykres.
Wykorzystując opisaną w tym poscie metodę tworzenia wykresu należy pamiętać o ograniczeniu, iż wartości X danych wejściowych oraz punkty graniczne muszą być ułożone w kolejności rosnącej. Ominięcie tego ograniczenia przedstawię w kolejnym wpisie.
Na koniec należy również dodać, iż gdybyśmy chcieli przygotować wykres dla większej liczby przedziałów aniżeli przedstawione 3, powinniśmy:
- zdefiniować kolejną wartość przedziału w komórce E13 i ewentualnie kolejnych
- odpowiednio rozszerzyć zakres formuły w komórce C2 i poniższych
- przydzielić punkty z tabeli wejściowej dla odpowiednio większej liczby serii danych (będzie to oznaczało większą liczbę kolumn)
- obliczyć punkty przecięcia dla każdej granicy, za każdym razem odpowiednio zmieniając formułę obliczającą wartość X punktu przecięcia
Brak komentarzy:
Prześlij komentarz