Kontynuujemy temat wyciągania ostatnich i pierwszych wierszy po warunku z porady 307. Tym razem napiszemy formuły, które pozwolą nam wyciągnąć interesujące nas dane.
Zaczniemy od formuły, która jest dla mnie klasycznym podejściem w podobnych sytuacjach – w funkcji JEŻELI będziemy sprawdzać dwa interesujące nas warunki – dla dnia i firmy, czyli porównujemy całe kolumny danych do konkretnych wartości. Ponieważ musimy sprawdzić 2 kolumny, dlatego wykonujemy na wynikach operacji porównań mnożenie (pamiętając o kolejności działań dokładamy nawiasy). Jeśli warunki są spełnione to chcemy mieć wartość z kolumny liczba porządkowa, jeśli nie to chcemy domyślną wartość FAŁSZ, więc nic nie wpisujemy:
W formułach korzystamy z tego, że zakres danych jest zamieniony na tabelę Excela (tNotowania) oraz, że mamy kolumnę z liczbą porządkową. Jeśli nie mielibyśmy kolumny z liczbą porządkową musielibyśmy ją stworzyć w formule – odwołanie tNotowania2[L.p] musielibyśmy zamienić na formułę:
WIERSZ(tNotowania[Data])-WIERSZ(B1)
Tak stworzona funkcja JEŻELI zwraca tablicę z numerami porządkowymi, gdzie warunki były spełnione i wartościami FAŁSZ tam, gdzie nie były one spełnione:
Z takiej tablicy wystarczy odpowiednio wyciągnąć minimalną i maksymalną wartość, żeby uzyskać pierwszy i ostatni numer wiersza, który spełnia nasze warunki:
rys. 2 – Wyciąganie numeru pierwszego wiersza za pomocą funkcji MIN
Musimy pamiętać, że nasza formuła jest formuła tablicową, więc żeby zwracała prawidłowe wyniki musimy zatwierdzać ją za pomocą kombinacji klawiszy Ctrl + Shift + Enter.
Jak wyciągnęliśmy numer wiersza, to wystarczy już tylko funkcja INDEKS, żeby pobrać wartość z kolumny Cena:
rys. 3 – Obliczone pierwsze i ostatnie ceny po warunku
Mamy już wynik, ale chce przedstawić jeszcze inny sposób na znalezienie ostatniego wiersza. Pierwszy raz poznałem tą sztuczkę od Billa Szysz(kowskiego). Wykorzystuje ona funkcję WYSZUKAJ i najważniejsze w niej jest to, że nie musimy formuły zatwierdzać jako formuły tablicowej.
Zanim zaczniemy pisać formułę, musimy sobie powiedzieć 2 ważne rzeczy na temat funkcji WYSZUKAJ – zawsze dokonuje dopasowania przybliżonego oraz nie zwraca uwagi na błędy w kolumnie w której szuka.
Dlatego będziemy szukać liczby 2 i kolumnę po której będziemy szukać stworzymy jako wynik podzielenia 1 przez wynik naszych porównań po kolumnach (pamiętając o kolejności operacji matematycznych). Wektorem (tablicą) wynikową będzie kolumna Cena:
rys. 4 – Wyszukiwanie ostatniego wiersza po warunku za pomocą funkcji WYSZUKAJ
Czemu nasza funkcja działa? Przypomnij sobie co robi funkcja WYSZUKAJ.PIONOWO, gdy szukamy na zasadzie przybliżonej wartości, która jest większa od największej liczby jaką mamy w posortowanej kolumnie – zwraca ostatnią wartość. Analogicznie działa funkcja WYSZUKAJ – szuka 2 na liście 1 i błędów dzielenia przez zero:
Tak właśnie wygląda nasz przeszukiwany wektor (kolumna). Jeśli oba warunki są spełnione to mamy dwie wartości PRAWDA. Dowolna operacja matematyczna (np.: dzielenie) zamienia je na 1, czyli 1/(1*1)=1. Ale jeśli warunek nie jest spełniony otrzymujemy wartość FAŁSZ, która przy operacjach matematycznych zamienia się na 0, czyli np.: 1/(1*0) = błąd dzielenia przez zero (#DZIEL/0!). Ponieważ funkcja WYSZUKAJ ignoruje błędy pozostają jej same 1, czyli szukając 2, przy przeszukiwaniu przybliżonym, będzie przeszukiwała po ‘posortowanej’ liście 1, czyli odnajdzie pozycję ostatniej 1, a tym samym zwróci korespondującą do niej wartość z kolumny Cena.
Przedstawimy jeszcze inny sposób na znalezienie pierwszego wiersza. Niestety funkcja WYSZUKAJ tu nie zadziała i będziemy musieli wykorzystać funkcję PODAJ.POZYCJĘ. Będziemy sprawdzać kilka warunków – muszą się zgadzać wartości w kilku kolumnach. Jednym ze sposobów na to jest scalanie szukanych wartości i scalanie kolumn. W ten sposób uzyskamy jedną scaloną wartość, której będziemy szukać w scalonej kolumnie – funkcja PODAJ.POZYCJĘ idealnie się do tego nadaje. Dokładamy jeszcze dopasowanie dokładne i udaje nam się odnaleźć pierwszy wiersz z szukaną wartością, bo dopasowanie dokładne zwraca pierwszą wartość/pierwszy wiersz jaki znajdzie od początku danych:
Pamiętaj są to formuły tablicowe i musisz je zatwierdzić za pomocą kombinacji klawiszy Ctrl + Shift + Enter.
P.S. Po podejrzeniu (klawiszem F9) przykładowa szukana scalona wartość w funkcji PODAJ.POZYCJĘ będzie wyglądać tak:
"42738Acme"
Zamiast daty zobaczyć liczbę, ale nie przejmuj się, gdyż Excel bez problemu sobie z tym radzi, poza tym scalone ze sobą kolumny Data i Firma też mają w sobie liczby, które są odpowiednikiem dat:
{"42738Acme";"42738Małe Kucyki";"42738Puchatek i Przyjaciele";"42738Małe Kucyki";"42738Acme";"42738Małe Kucyki";"42738Acme";"42738Puchatek i Przyjaciele";"42738Acme";"42738Acme";"42738Acme";"42738Małe Kucyki";"42738Acme";"42738Puchatek i Przyjaciele";"42738Acme";"42738Małe Kucyki";"42738Acme";"42739Acme";"42739Puchatek i Przyjaciele";"42739Małe Kucyki";"42739Puchatek i Przyjaciele";"42739Acme";"42739Puchatek i Przyjaciele";"42739Puchatek i Przyjaciele";"42739Małe Kucyki";"42739Puchatek i Przyjaciele";"42739Acme";"42739Acme";"42739Małe Kucyki";"42739Puchatek i Przyjaciele";"42739Acme";"42739Puchatek i Przyjaciele";"42739Acme";"42739Acme";"42739Puchatek i Przyjaciele";"42740Acme";"42740Puchatek i Przyjaciele";"42740Małe Kucyki";"42740Puchatek i Przyjaciele";"42740Puchatek i Przyjaciele";"42740Małe Kucyki";"42740Acme";"42740Małe Kucyki";"42740Acme";"42740Acme";"42740Acme";"42740Acme";"42740Małe Kucyki";"42740Acme";"42740Acme";"42740Małe Kucyki"}
W pytaniu widzów 120 rozwiązaliśmy problem jak dla unikalnych wartości znaleźć minimalne i maksymalne wartości. Te wartości występowały odpowiednio jako pierwsze i ostatnie i w tym wpisie chcemy się zająć wyciąganiem wartości na podstawie ich kolejności, a nie wielkości (czy są min czy max).
Dziś posłużymy się jedynie prostymi komendami Excela jak usuwanie duplikatów i sortowanie. Ewentualnie przyda nam się jeszcze umiejętność stworzenia w Excelu kolumny indeksu (liczby porządkowej), ale to też będzie proste zadanie.
Zacznijmy od danych (standardowo zapisałem je jako tabela Excela, ale nie jest to konieczne do dziś wykonywanych przez nas operacji). Chcemy wyciągnąć pierwszy i ostatni kurs notowań w danym dniu dla danej firmy (unikalne wartości po tych kolumnach). Jak możemy to zrobić?
Dane z zaznaczonymi przykładowymi pierwszymi i ostatnimi wierszami danych dla konkretnych firm danego dnia
Wyciągnięcie pierwszych wartości jest bardzo proste wystarczy usunąć duplikaty po interesujących nas kolumnach, ale zanim to zrobimy stwórzmy sobie dodatkową kolumnę z indeksem (liczbą porządkową), żebyśmy wiedzieli jak to zrobić, gdyby nasze dane nie miały takiej kolumny.
Wystarczy w komórce F2 (czyli w kolumnie tuż obok danych) wpiszemy 1, a w F3 wpiszemy 2. Następnie zaznaczamy te komórki i klikamy dwa razy w prawy dolny róg zaznaczenia.
Tworzenie kolumny z indeksem
Excel stworzy rosnący ciąg do końca danych (nie ważne, czy dane są w tabeli Excela, czy nie).
My już jedną kolumnę z liczbą porządkową mamy, więc nie potrzebujemy drugiej, dlatego cofniemy naszą operację.
Teraz zrobimy coś dla nas ważniejszego – skopiujemy dane, w nich zaznaczamy dowolną pojedynczą komórkę w tych skopiowanych danych. Kolejnym krokiem będzie kliknięcie w polecenie Usuń duplikaty na karcie Dane.
Polecenie usuwania duplikatów na karcie Dane
W nim musimy zaznaczyć te kolumny, po których ma identyfikować duplikaty. W naszym przykładzie są to kolumny Data i Firma (rys. 4).
Okno usuwania duplikatów
Zatwierdzamy nasz wybór przyciskiem OK i Excel usuwa duplikaty, czyli jeśli wartości w kolumnach Data i Firma powtórzyły się we wcześniejszym wierszu, to Excel usuwa ten wiersz. Zostaje tylko jego pierwsze unikalne wystąpienie, czyli dokładnie ten pierwszy wiersz, którego szukaliśmy (rys. 5).
Podsumowania usuwania duplikatów
Ponownie kopiujemy nasze dane i sortujemy je w odwrotnej kolejności (najprościej po kolumnie z liczbą porządkową) i znów usuwamy duplikaty. Excel znów zostawia tylko pierwsze unikalne wystąpienie wiersza, ale ponieważ zmieniliśmy kolejność sortowania, to są to ostatnie wiersze w danych źródłowych. Możemy porównać wyniki (rys. 6).
Dane po usuwaniu duplikatów od początku i od końca
Jeszcze nam odrobinę brakuje do ich pełnego połączenie, ponieważ wiersze nie są w takiej samej kolejności, ale to wystarczy posortować dane, po unikalnych kolumnach (Data i Firma) oba zestawy danych. Jeszcze usunięcie zbędnych kolumn (np.: kolumny Czas) i może przysunąć dane do siebie (usunąć pustą kolumnę je rozdzielającą) (rys. 7).
Połączone pierwsze i ostatnie wiersze
Na ostatnim rysunku mamy jeszcze za dużo kolumn, ale to po to, żeby pokazać, że odpowiednie wiersze do siebie pasują. Wystarczy usunąć resztę zbędnych kolumn i zostawić tylko, te których potrzebujemy.
W tym przykładzie kopiowałem całą tabelę, ale nie zawsze jest to potrzebne. Ważne jest, żeby pilnować kolejności wiersze, a ile kolumn kopiujemy jest drugorzędną sprawą.
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ąć
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
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]
W dalszej kolejności nie potrzebujemy sum częściowych
[rys. 4 – Wyłączanie sum częściowych]
I 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]
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]
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]
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]
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]
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
W poradzie 305 wspominałem o funkcjach makr 4.0. Był to dla mnie nowy temat i zaciekawił na tyle, że chciałbym móc używać tych funkcji w VBA, ale oczywiście w prosty sposób się nie da. Podobnie jak funkcji makr 4.0 nie da się wpisać bezpośrednio do komórki arkusza, tak nie można ich użyć bezpośrednio w kodzie VBA. Potrzeba użyć formuły (ExecuteExcel4Macro) wywołującej funkcje makr 4.0, czyli np. takiego kodu:
Visual Basic
1
ActiveCell=ExecuteExcel4Macro("GET.CELL(42)")
Powyższy kod w aktywną komórkę (ActiveCell) wpisuje jej odległość od lewej krawędzi Excela (GET.CELL(42))
Funkcji makr 4.0 są dziesiątki i warto przejrzeć chociaż ich listę żeby wiedzieć, kiedy mogą się przydać. Taką listę z opisem ich funkcjonalności i przykładami użycia (niestety po angielsku) możesz pobrać na stronie: https://www.myonlinetraininghub.com/excel-4-macro-functions
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
Przed nami niezwykle trudne zadanie – potrzebujemy zrobić w Excelu listę wszystkich plików ze wskazanego katalogu. Spokojnie nie będzie, aż tak źle. Nawet nie użyjemy VBA, a przynajmniej nie zajrzymy do edytora VBA i nie będziemy tworzyć, żadnych nowych makr.
Na podstawie tego problemu chcę Ci przybliżyć sposób działania funkcji makr 4.0 i opowiedzieć o trudnościach, z nimi związanymi, jakie czekają polskiego użytkownika Excela (i innych nie angielskich narodowości).
Pierwsza trudność jest taka, że nie możesz ich wpisać bezpośrednio w komórki Excela. Może znasz funkcję DATA.RÓŻNICA, której Excel nie podpowiada, że istnieje, ale jak wpiszesz ją w komórkę Excela to ona działa. Funkcje makr 4.0 nie działają w komórkach arkusza Excela.
Żeby z nich skorzystać, chyba najprostszym sposobem, jest stworzenie na ich podstawie nazwy (Menadżer nazw karta dane).
Rys. 2 — Menadżer nazw
Przy tworzeniu tej nazwy pojawia się kolejny problem, bo ciężko znaleźć nazwę tych funkcji po polsku (ogólnie w innym języku niż angielski). Źródła, które podałem na początku podają potrzebną nam funkcję w języku angielskim – nazywa się FILES. Ale jeśli spróbujemy z niej skorzystać polski Excel jej nie rozpozna. Potrzebujemy jej nazwę po polsku. W wielu przypadkach zadziała bezpośrednie tłumaczenie – tak mi się udało znaleźć polski odpowiednik dla tej funkcji – PLIKI. O innych sposobach pomówimy na koniec wpisu. Teraz zajmiemy się rozwiązaniem postawionego problemu.
Wiemy już jak funkcja nazywa się po polsku (PLIKI), ale nie znamy jej składni. Excel nam jej nie podpowie. Na szczęście składnia tej funkcji jest prosta – wystarczy, że podasz pełną ścieżkę do katalogu, z którego chcesz uzyskać listę plików (pamiętaj na jej końcu dodać gwiazdkę, która oznacza, że bierzemy wszystko):
C:\Users\PC\Documents\Pliki\*
Ścieżkę do katalogu najlepiej, żebyś wstawił do komórki Excela, a dopiero w funkcji makr 4.0 PLIKI się do niej odwoływał. Ułatwi Ci to jej modyfikację.
rys. 3 – Wstawiona ścieżka do komórki A2
Czasami może być ciężko odnaleźć dokładną ścieżkę katalogu, bo nie widać jej na pierwszy rzut oka, ale wystarczy, że w ekspoatora Windowsa i klikniesz na jego górę, gdzie widać ścieżkę w formie bardziej graficznej.
rys. 4 – eksplorator windows i kopiowanie ścieżki
Wyświetli Ci się wtedy pełna ścieżka (nawet jeśli jesteś w swoich dokumentach) i będziesz mógł ją łatwo skopiować.
Mamy ścieżkę, mamy nazwę funkcji, więc możemy ją w końcu stworzyć w Menadżerze nazw (Ctrl + F3). Naciskamy przycisk nowy i przechodzimy do okna tworzenia nazwy:
rys. 5 – nowa nazwa
Jak stworzyliśmy nazwę możemy się do niej odwoływać w komórkach Excela. Ponieważ funkcja PLIKI zwraca listę, więc nie możemy jej bezpośrednio wstawić do komórki Excela, bo zobaczymy tylko pierwszą wartość (pierwszy plik) z listy. Na szczęście wystarczy skorzystać z funkcji INDEKS, której będziemy mówić, który element z listy ma wyświetlać. Najczęściej w podobnych sytuacjach korzystam z funkcji ILE.WIERSZY i dynamicznego zakresu danych, który się rozrasta jak przeciągamy formułę:
=INDEKS(WszystkiePliki;ILE.WIERSZY($A$4:A4))
rys. 6 – Lista plików za pomocą funkcji
Jeśli przeciągniemy formułę wystarczająco daleko w dół zaczną się pojawiąć błędy adresu – #ADR!, ponieważ nie ma już elementów na liście. Najprościej sobie z tym poradzić wstawiając naszą formułę do funkcji JEŻELI.BŁĄD, które karzemy zwracać pusty ciąg znaków (dwa podwójne cudzysłowy), jeśli wyjdziemy poza listę.
Możemy nawet policzyć ilość pliku w katalogu (w stworzonej przez nas nazwie) np. za pomocą funkcji ILE.NIEPUSTYCH:
=ILE.NIEPUSTYCH(WszystkiePliki)
Udało nam się rozwiązać postawione na początku zadanie i dochodzimy do zapisywania pliku. Pojawia się kolejny problem — funkcje makr 4.0, to jednak makra i skoroszyt je zawierający musi być zapisany w formacie, który obsługuje makra.
rys. 8 – błąd przy zapisywaniu pliku z funkcjami makr 4.0
Na koniec wpisu warto wspomnieć, że przy funkcji PLIKI działają proste filtry, a dokładniej symbole wieloznaczne. Musieliśmy napisać na końcu ścieżki gwiazdkę (rys. 3), żeby pobrać wszystkie pliki. W podobny sposób możemy ograniczyć wybór plików z katalogu tylko do plików Excela ustawiając końcówkę:
C:\Users\PC\Documents\Pliki\*.xls*
Końcówka (*.xls*) oznacza, że nazwa pliku może zaczynać się od dowolnego ciągu znaków, po którym pojawia się ciąg tekstu ‘.xls’, a za nim może się jest coś pojawić, ale nie musi (symbol wieloznaczny gwiazdka oznacza dowolny ciąg tekstowy – nawet pusty). Efekty tak nałożonego filtru widać od razu w wynikach:
rys. 9 – przefiltrowane pliki
Drugim, oprócz gwiazdki, symbolem wieloznacznym w Excelu jest znak zapytania ‘?’ – zastępuje on dowolny pojedynczy znak.
Mieliśmy problem z odnalezieniem nazwy funkcji makr 4.0 po polsku, ale mamy źródła, które podają te nazwy po angielsku. Wystarczy, że zmienimy język w Excelu na angielski (Menu Plik -> Opcje -> zakładka Język), zapiszemy plik z angielską nazwą pliku, a następnie zmienimy język Excela na polski i ponownie otworzymy plik – teraz już z polską nazwą funkcji.
Dodanie nowego języka do Excela różni się w zależności od wersji Excela, którą masz, dlatego podaję link do strony Microsoftu dokładnie opisujący proces w zależności od Twojej wersji Excela.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
Dzisiaj chce porozmawiać o problemie zliczania grup powyżej progu. Żeby lepiej zobrazować problem pokaże dane sformatowane warunkowo (komórki wypełnione na żółto) i ich prezentację na wykresie z linią progu.
Najlepiej można zobaczyć grupy (góry), które wyrastają ponad próg na wykresie. Łatwo jest je też policzyć – jest ich 4. Ale nie chcemy tego liczyć ręcznie za każdym razem, gdy dane się zmienią, albo gdy mamy dużo więcej danych niż w przykładzie. Co chcemy zrobić oczywiście wykorzystać formułę Excela, ale najpierw wyjaśnijmy jak sobie możemy wykryć grupę (górę).
Są 2 możliwości, albo patrzymy na zbocze wznoszące, albo opadające, czyli albo sprawdzamy, że wartość jest mniejsza od progu, a kolejna większa lub równa progowi (znak równości może być przy pierwszym lub drugim punkcie, który sprawdzamy w zależności, czy próg uznajemy już za naszą ‘górkę’ czy jeszcze nie), albo pierwsza wartość większa lub równa progowi, a kolejna mniejsza od progu. W tym przykładzie będziemy szukać zboczy wznoszących.
Wiemy co chcemy znaleźć, ale jak to znaleźć w Excelu za pomocą formuły? Mamy operacje porównania jako warunki i chcemy je zliczać, więc świetnie do tego nadaje się funkcja LICZ.WARUNKI. Wystarczy, że sprawdzimy, czy nasze dane, od pierwszej do przedostatniej wartości, są mniejsze od progu, a następnie sprawdzimy, czy dane od drugiej do ostatniej wartości są większe bądź równe progowi:
=LICZ.WARUNKI(A2:A14;""&E1;A3:A15;">="&E1)
Czyli sprawdzamy obszary, które w stosunku do siebie są przesunięte o 1 komórkę.
Niestety wynik jaki otrzymujemy jest błędny – brakuje nam jednej ‘górki’. Taka sytuacja występuje wtedy kiedy nasze dane zaczynają się już od wartości większej (lub równej) progowi. Na szczęście bardzo łatwo skorygować naszą formułę. Wystarczy, że dodamy warunek sprawdzający, czy pierwsza dane jest większa równa od wyznaczonego progu. Musimy pamiętać o kolejności działań i dodatkowe porównanie zapisać w nawiasach (dodatkowe obliczenie poza funkcją LICZ.WARUNKI):
Operacja porównania (A2>=$E$1) daje w wyniku wartość PRAWDA i FAŁSZ, które w wyniku dodawania są konwertowane odpowiednio na 1 i 0, czyli jeśli pierwsza wartość w danych przekracza wartość progu, to mamy jedną górkę ekstra i dodajemy ją do wyniku funkcji LICZ.WARUNKI, ale jeśli nie przekracza progu to nic nie dodajemy — nie ma górki na początku danych.
Odpowiednio wizualizując sobie problem udało nam się znaleźć proste rozwiązanie w Excelu 🙂
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
![[rys. 3 – Style układów tabel przestawnych]](https://exceliadam.pl/wp-content/uploads/2017/11/Widzowie-120-Maksymalna-i-minimalna-wartość-po-warunku-za-pomocą-tabeli-przestawnej-03.png)
![[rys. 4 – Wyłączanie sum częściowych]](https://exceliadam.pl/wp-content/uploads/2017/11/Widzowie-120-Maksymalna-i-minimalna-wartość-po-warunku-za-pomocą-tabeli-przestawnej-04.png)
![[rys. 5 – Wyłączanie sum końcowych]](https://exceliadam.pl/wp-content/uploads/2017/11/Widzowie-120-Maksymalna-i-minimalna-wartość-po-warunku-za-pomocą-tabeli-przestawnej-05.png)
![[rys. 6 – Tabela przestawna z 2 podsumowaniami ilościowymi czasu]](https://exceliadam.pl/wp-content/uploads/2017/11/Widzowie-120-Maksymalna-i-minimalna-wartość-po-warunku-za-pomocą-tabeli-przestawnej-06.png)
![[rys. 7 – Czas sformatowany ogólnie w tabeli przestawnej]](https://exceliadam.pl/wp-content/uploads/2017/11/Widzowie-120-Maksymalna-i-minimalna-wartość-po-warunku-za-pomocą-tabeli-przestawnej-07.png)
![[rys. 8 – Format liczby w tabeli przestawnej]](https://exceliadam.pl/wp-content/uploads/2017/11/Widzowie-120-Maksymalna-i-minimalna-wartość-po-warunku-za-pomocą-tabeli-przestawnej-08.png)
![[rys. 9 – Przyciski +/- na karcie Analiza]](https://exceliadam.pl/wp-content/uploads/2017/11/Widzowie-120-Maksymalna-i-minimalna-wartość-po-warunku-za-pomocą-tabeli-przestawnej-09.png)
![[rys. 10 – różnica pomiędzy wartością Max, a Min]](https://exceliadam.pl/wp-content/uploads/2017/11/Widzowie-120-Maksymalna-i-minimalna-wartość-po-warunku-za-pomocą-tabeli-przestawnej-10.png)
