Wyróżniamy komórki w Excelu, abyśmy mogli łatwo zidentyfikować obszary zainteresowania danych. Formatowanie komórek w programie Excel jest również wykonywane w celu wizualizacji wartości danych na podstawie koloru i stylu. Na przykład możesz chcieć skoncentrować się na 3 najmniejszych wartościach w swoich danych. Trudno to zrobić na dużych danych, ale gdybyś mógł powiedzieć programowi Excel, aby podświetlił 3 najniższe wartości, byłoby to całkiem proste. Na szczęście możemy to zrobić za pomocą opcji formatowania warunkowego w Excelu.
Łatwo jest po prostu wyróżnić 3 najmniejsze wartości w programie Excel, ale staje się to trochę trudne, gdy dodamy kryteria. W tym artykule dowiemy się, jak wyróżnić najmniejsze 3 wartości za pomocą warunku.
Formuła ogólna w formatowaniu warunkowym
=ORAZ(sprawdź komórka=kryteria, komórka wartości<=MAŁY(JEŻELI(zakres kryteriów=kryteria,zakres wartości),3))
Sprawdź komórkę: Jest to komórka, którą chcesz sprawdzić, czy spełnia pierwsze kryteria (kategoria).
Kryteria: Kryteria, które chcesz filtrować.
Komórka wartości: Jest to komórka zawierająca wartość liczbową. Porównamy go z trzecią najmniejszą wartością w swojej kategorii, w zakres wartości.
Zakres kryteriów: Jest to zakres bezwzględny zawierający kryteria.
Zakres wartości: Zakres zawierający wszystkie wartości.
Zobaczmy przykład, aby wszystko było jasne.
Przykład: Wyróżnij trzy najniższe sprzedaże wykonane przez Dział 1
Tutaj mam przykładowy plik roboczy, który zawiera sprzedaż wykonaną przez różne działy w różnych miesiącach.
Chcę podkreślić trzy najniższe wyniki sprzedaży zrealizowane przez Dział 1.
Zidentyfikujmy zmienne, które tutaj mamy.
Sprawdź komórkę: $B2 (chcemy sprawdzić, czy B2 zawiera „dealer 1”. Ponieważ nie chcemy zmieniać kolumny, używamy znaku $. To sprawia, że cały rząd jest podświetlony.)
Kryteria: "Dział 1"
Komórka wartości: $C2
Zakres kryteriów: $ B 2 $: $ B $ 12
Zakres wartości: $C$2:$C$12
Teraz, gdy znamy już wszystkie argumenty, które musimy przekazać do formuły, nadszedł czas, aby zaimplementować powyższą ogólną formułę na zbiorze danych.
- Wybierz dane. Idź do domu ? formatowanie warunkowe ? Nowa zasada
- Wybierz opcję „Użyj formuły, aby określić, którą komórkę należy sformatować”.
- W poniższym polu tekstowym wpisz tę formułę
=ORAZ($B2="Sprzedaż 1",$C2<=MAŁY(JEŻELI($B2:$B$12="Sprzedaż 1",$C$2:$C$12),3))
Naciśnij przycisk OK. I gotowe. Podświetlone są trzy dolne lub powiedzmy najmniejsze 3 sprzedaże z działu 1.
Jak to działa?
Rozłóżmy formułę od środka.
Ta formuła jest formułą tablicową, ale klawisze CTRL+SHIFT+ENTER nie są potrzebne w formatowaniu warunkowym.
Ponieważ mamy dwa warunki, pierwszy to ASO 1, a drugi to wartości mniejsze lub równe 3. najmniejszej wartości w ASO 1. Oba muszą być PRAWDZIWE. W tym celu używamy funkcji AND.
$B2="Dealer 1"
Pierwsze kryteria są łatwe do sprawdzenia. Po prostu umieszczamy to stwierdzenie $B2="Dealer 1". Będzie PRAWDA, jeśli bieżąca komórka B2 zawiera ASO 1.
$C2<=MAŁY(JEŚLI($ B$2:$B$12="Dealer 1",$C$2:$C$12),3)
W przypadku drugiego kryterium musimy przefiltrować Salon sprzedaży 1 i znaleźć w nim trzecią najmniejszą wartość. Następnie porównaj komórkę kryteriów z trzecią najmniejszą wartością. Odbywa się to za pomocą instrukcji $C2<=SMALL(IF($B$2:$B$12="Dealer 1",$C$2:$C$12),3)
Kiedy to ocenimy, ta formuła rozwiąże się w ten sposób:
Sprawdzamy zakres $B$2:$B$12 i pobieramy komórki, które zawierają "Dealer 1" za pomocą instrukcji $B$2:$B$12="Dealer 1". Będziemy mieli tablicę TRUE i FALSE.
$C2<=MAŁY(JEŻELI({PRAWDA;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;PRAWDA};$C$2:$C$12),3)
Dla każdej TRUE zostanie zwrócona wartość z zakresu wartości $C$2:$C$12 i będziemy mieli kolejną tablicę.
$C2<=MAŁY({12;FAŁSZ;25;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;18;FAŁSZ;FAŁSZ;14},3)
Teraz SMALL Function zwróci trzecią najmniejszą wartość z tej tablicy. (FLASE zostanie zignorowane).
Teraz mamy:
$C2<=18
Wreszcie formuła to
=ORAZ($B2="Sprzedaż 1",$C2<=18)
Teraz sprawdza, czy B2 zawiera ASO 1. To robi. Stąd to PRAWDA
=I(PRAWDA;$C2<=18)
Następnie sprawdza, czy C2 jest mniejsze lub równe 18 (trzecia najmniejsza sprzedaż w salonie 1). To PRAWDA (12).
=I(PRAWDA;PRAWDA)
Ponieważ oba stwierdzenia mają wartość PRAWDA, formuła zwraca PRAWDA i nasz pierwszy WIERSZ jest podświetlony kolorem. To samo dzieje się w każdym rzędzie.
Mało!!! Trudno było to wyjaśnić. Mam nadzieję, że wyjaśniłem to na tyle jasno, że zrozumiałeś tę metodę. W praktyce jest to łatwe.
Tutaj użyliśmy kryteriów zakodowanych na sztywno, ale możemy również podać odwołanie do komórki. Dzięki temu formuła będzie bardziej elastyczna.
Tutaj dla Kryteria Użyłem odwołania do komórki E1. Niezależnie od tego, który dealer masz w E1, trzy najmniejsze wartości z tego dealera zostaną podświetlone.
Formuła w formatowaniu warunkowym to:
=ORAZ($B2=$E$1,$C2<=MAŁY(JEŻELI($B$2:$B$12=$E$1,$C$2:$C$12),3))
Więc tak, w ten sposób możesz wyróżnić trzy najniższe wartości z warunkiem w programie Excel. Mam nadzieję, że artykuł był pomocny. Jeśli masz jakiekolwiek pytania, skorzystaj z sekcji komentarzy poniżej. Możesz zadawać pytania związane z tym artykułem lub dowolnym innym artykułem związanym z programem Excel. Chętnie Ci pomożemy.
Formatowanie warunkowe na podstawie innej wartości komórki
Formatowanie IF i warunkowe
Formatowanie warunkowe z formułą 2016
Formatowanie warunkowe przy użyciu VBA w programie Microsoft Excel
Zaznacz komórki zawierające określony tekst
Jak sumować wiele kolumn z warunkiem
Popularne artykuły:
50 skrótów Excela, aby zwiększyć wydajność
Funkcja WYSZUKAJ.PIONOWO w programie Excel
LICZ.JEŻELI w Excelu 2016
Jak korzystać z funkcji SUMIF w programie Excel?