Maksymalna i minimalna wartość po warunku za pomocą tabeli przestawnej — widzowie #120

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

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

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.

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

I 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.

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ę).

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.

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.

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

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