Dostałem zapytanie jak wyciągnąć pierwsze i ostatnie wiersze (wartości z wybranej kolumny) po unikalnych wartościach w innych kolumnach.

rys. 1 – Dane i przykładowe wiersze, które chcemy wyciągnąć

rys. 1 – Dane i przykładowe wiersze, które chcemy wyciągnąć

Zadanie okazuje się prostsze niż z początku myślałem, ponieważ wartość do wyciągnięcia (w tym przykładzie czas) w ostatnim wierszu jest maksymalna dla unikalnych wartości w kolumnach z warunkami i minimalna w pierwszym. Dlatego rozwiązanie naszego problemu sprawdza się do znalezienia max i min po warunkach, a to jest dużo prostsze. Ponieważ chcemy znaleźć max i min po wszystkich unikalnych wartościach w kolumnach warunkowych najprostszym rozwiązaniem okazuje się skorzystanie z Tabeli Przestawnej.

W tym przykładzie potrzebujemy unikalnych wartości po dwóch kolumnach Data i Pracownik, dlatego oba te pola przeciągamy do obszaru etykiet wierszy

rys. 2 – Początek budowania unikalnych wierszy w tabeli przestawnej

rys. 2 – Początek budowania unikalnych wierszy w tabeli przestawnej

Tabela przestawna jeszcze nie prezentuje się tak jakbyśmy sobie tego życzyli – musimy pozmieniać domyślne ustawienia Excela. W pierwszej kolejności potrzebujemy zamienić kompaktowy układ tabeli przestawnej na układ tabelaryczny i przy okazji zaznaczyć opcję powtarzania elementów w tabeli przestawnej.

[rys. 3 – Style układów tabel przestawnych]

[rys. 3 – Style układów tabel przestawnych]

W dalszej kolejności nie potrzebujemy sum częściowych

[rys. 4 – Wyłączanie sum częściowych]

[rys. 4 – Wyłączanie sum częściowych]

I sum końcowych.

 [rys. 5 – Wyłączanie sum końcowych]

[rys. 5 – Wyłączanie sum końcowych]

Teraz możemy przeciągnąć 2 razy pole Czas do obszaru podsumowań wartości. Excel podsumowując czas zlicza ile razy pojawiły się wiersza dla konkretnych warunków.

[rys. 6 – Tabela przestawna z 2 podsumowaniami ilościowymi czasu]

[rys. 6 – Tabela przestawna z 2 podsumowaniami ilościowymi czasu]

My chcemy mieć Max i Min czas dlatego musimy zmienić sposób podsumowania w tabeli przestawnej. Najprościej kliknąć prawym przyciskiem myszy na kolumnę z podsumowaniem, a następnie z podręcznego menu rozwinąć opcję Podsumuj wartości według i wybrać odpowiednio Maksimum i Minimum.

Niestety w tabeli przestawnej domyślnie ustawiło mi się formatowanie ogólne, które źle pokazuje czas (jako liczbę).

[rys. 7 – Czas sformatowany ogólnie w tabeli przestawnej]

[rys. 7 – Czas sformatowany ogólnie w tabeli przestawnej]

Dlatego musimy zmienić formatowanie w kolumnach z podsumowaniem tabeli przestawnej – klikamy prawym przyciskiem myszy i wybieramy z podręcznego menu pozycję Format liczy i w oknie, które się otworzy wybieramy odpowiedni sposób formatowania czasu.

[rys. 8 – Format liczby w tabeli przestawnej]

[rys. 8 – Format liczby w tabeli przestawnej]

Znaleźliśmy maksymalny i minimalny czas (liczbę) po warunkach za pomocą tabeli przestawnej. Dla lepszej estetyki możemy jeszcze wyłączyć przyciski +/- z karty Analiza.

 [rys. 9 – Przyciski +/- na karcie Analiza]


[rys. 9 – Przyciski +/- na karcie Analiza]

Dodatkowym problem w dzisiejszym zadaniu jest wyznaczenie różnicy pomiędzy tymi wartościami. Niestety w zwykłej tabeli przestawnej (nie z dodatku Power Pivot) nie jesteśmy w stanie dodać pola obliczającego tą różnicę prawidłowo, dlatego obliczenia zrobimy poza tabelą przestawną. Formuła to proste odejmowanie, tylko Excel najprawdopodobniej będzie chciał domyślnie wstawiać funkcję WEŹDANETABELI, której nie chcemy, więc najprościej wpisać odwołania do komórek ręcznie:

=L3-K3

[rys. 10 – różnica pomiędzy wartością Max, a Min]

[rys. 10 – różnica pomiędzy wartością Max, a Min]

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP