Przygotowując wykresy, czasami zależy na aby dane były posortowane np. malejąco, a nie wskazywane od w kolejności alfabetycznej. Prezentując dane w ten sposób można od razu zwiększyć przejrzystość danych, bowiem od razu wiemy kolejność danych, a nie musimy samodzielnie jej się doszukiwać przez porównywanie kolejnych wartości słupków.
Zdecydowanie łatwiej czytać wykres zapotrzebowania na owoce, gdy wygląda on w ten sposób
Zdecydowanie łatwiej czytać wykres zapotrzebowania na owoce, gdy wygląda on w ten sposób
aniżeli tak
Oczywiście stworzenie posortowanego wykresu jest możliwe w sposób ręczny, ale nadawało by się w przypadku tworzenia tego wykresu jednorazowo bez konieczności późniejszej jego zmiany. Można to uzyskać przez:
- Stworzenie wykresu i ręczne ustawienie kolejności serii danych przez ich przesunięcie w górę i w dół w Zakresie danych
- sortowanie tabeli danych narzędziem sortowania dostępnym w zakładce Dane
A
|
B
|
|
1
|
Wydatki
|
Kwota
|
2
|
Banany
|
95
|
3
|
Jabłka
|
55
|
4
|
Ananasy
|
78
|
5
|
Kokosy
|
70
|
Jak można zauważyć dane są dość rozrzucone. Sortowanie rozpocznijmy od stworzenia w kolumnie D liczb porządkowych. W D2 wstawmy 1, a w komórki poniżej odpowiednio 2,3 i 4.
Następnie w komórce F2 przy pomocy formuły:
=MAX.K($B$2:$B$5;D2)
wyznaczymy największą z kwot w powyższej tabeli. Przeciągając formułę do końca zakresu danych otrzymamy Kwoty w kolejności od największej.
Brakuje nam tylko przypisania nazwy Wydatku do naszych nowo otrzymanych kwot. Wartości te otrzymamy formułą:
=INDEKS($A$2:$A$5;PODAJ.POZYCJĘ(F2;$B$2:$B$5;0)) Wpisaną w komórkę E2. W Efekcie przeciągnięcia formuły na dół otrzymamy taką tabelę
A
|
B
|
C
|
D
|
E
|
F
|
|
1
|
Wydatki
|
Kwota
|
Lp.
|
Wydatki
|
Kwota
|
|
2
|
Banany
|
95
|
1
|
Banany
|
95
|
|
3
|
Jabłka
|
55
|
2
|
Ananasy
|
78
|
|
4
|
Ananasy
|
78
|
3
|
Kokosy
|
70
|
|
5
|
Kokosy
|
70
|
4
|
Jabłka
|
55
|
Następnie wystarczy dla nowego zakresu danych (czyli komórek E1:F5) utworzyć wykres słupkowy, który po odpowiednim formatowaniu wygląda następująco:
Osiągnęliśmy już efekt sortowania danych, jednak w odróżnieniu od tego co chcieliśmy otrzymać, mamy kolejność rosnącą zamiast malejącej. Ostatni szlif do wykresu dokonujemy przez PPM na pionowej osi i zaznaczenie:
- Kategorie mają być wyświetlane w kolejności odwrotnej
- Przecięcie z osią poziomą w wartości maksymalnej.
Dodatkowo należy zaznaczyć, iż wyżej wspomniany sposób zadziała w sytuacji gdy Kwoty nie będą się powtarzać. Jeżeli jednak nasze dane mogą się w ten sposób kształtować w komórkę E1 należy wpisać formułę jak podano wyżej, czyli:
=INDEKS($A$2:$A$5;PODAJ.POZYCJĘ(F2;$B$2:$B$5;0))
Jednak dla komórki E3 należy użyć formuły tablicowej o następującej treści:
=INDEKS($A$2:$A$5;MIN(JEŻELI(($B$2:$B$5=F3)*(LICZ.JEŻELI($E$2:E2;$A$2:$A$5)=0);$D$2:$D$5;"")))
Którą należy przeciągnąć do końca naszej tabeli danych.
=INDEKS($A$2:$A$5;PODAJ.POZYCJĘ(F2;$B$2:$B$5;0))
Jednak dla komórki E3 należy użyć formuły tablicowej o następującej treści:
=INDEKS($A$2:$A$5;MIN(JEŻELI(($B$2:$B$5=F3)*(LICZ.JEŻELI($E$2:E2;$A$2:$A$5)=0);$D$2:$D$5;"")))
Którą należy przeciągnąć do końca naszej tabeli danych.
2 komentarze:
Przy zbudowaniu tabeli jak w poście (z powtarzającymi się danymi) i wpisaniu tej samej Kwoty dla Jabłka, Ananasy i Kokosy (np.B3 i B4 i B5 = 1), w posortowanych danych "giną" Kokosy a Jabłka pojawiają się dwukrotnie.
@mifras
bardzo słuszna uwaga. w formule zabrakło blokowania jednej komórki.
post został już poprawiony
Prześlij komentarz