fbpx

Wyróżnianie najmniejszej wartości w wierszu

11.04.2018 | ECP2, Formatowanie warunkowe

Czyli sprytne użycie formatowania warunkowego…

Załóżmy, że chcemy dla w każdym wierszu tabeli wyróżnić najmniejszą wartość. Oczywiście chcemy to zrobić możliwie szybko, małym nakładem pracy i jeszcze tak, żeby rozwiązanie było dynamiczne, czyli jeśli zmienimy jakąś wartość – wyróżnienie się do tego dostosuje i na bieżąco sprawdzi, czy owa zmieniona nie jest najmniejsza. Mamy kilka magazynów (może być kilkanaście albo kilkaset dla większego dramatyzmu;)) i w każdym z nich, chcemy wyróżnić najmniejszą wartość w tygodniu:

Formatka 1

Formatka 1

Albo druga sytuacja, na tych samych danych: chcemy zaznaczyć cały wiersz, jeśli w tym wierszu znajdzie się najmniejsza wartość z wybranej kolumny (np. 4). Załóżmy, że w czwartek przychodzi kontrola do magazynu i chcemy wiedzieć, który magazyn tego dnia miał najmniejsze stany. I chcemy podświetlić cały wiersz dla tego magazynu, aby analizować stany jego magazynowe w całym tygodniu:

Formatka 2

Formatka 2

Oczywiście bez formatowania warunkowego tutaj się nie obejdzie. Formatowanie to będzie wymagało też napisania formuły, która zdefiniuje warunek. Czyli coś bardziej skomplikowanego, niż „wyklikanie” formatowania, jak to w wieeelu przypadkach wystarczy…

1. Wyróżnianie najmniejszej wartości w wierszu

W tym przypadku, w każdym wierszu naszego zakresu (C4:I9) chcemy zaznaczyć najmniejszą wartość w danym wierszu. Problem jest taki, że piszemy w tym celu jedną regułę formatowania warunkowego. Musi zatem być uniwersalna dla każdego wiersza zakresu.

W takich sytuacjach koniecznie będziemy musieli działać z adresowaniem komórek, czyli dolarami ($). To tak, jak pisanie jednej formuły dla dwuwymiarowego zakresu, czyli takiego, który składa się z kilku wierszy i kilku kolumn (klasyczny przykład takiego: tabliczka mnożenia).

Tutaj chcemy, aby dla każdej komórki w wierszu, w odpowiednich komórce wierszach, ale zawsze w obrębie kolumn od C do I (czyli np. w zakresie: C4:I4) Excel sprawdzał czy wartość konkretnej komórki jest najmniejszą w tym zakresie. I to mamy sprawdzić dla każdej komórki (stąd analogia do kopiowania formuły do dwuwymiarowego zakresu). W praktyce oznacza to, że każdą komórkę trzeba porównać do wartości minimalnej tego zakresu. Jeśli warunek ten będzie prawdziwy – formatowania warunkowe pokoloruje komórkę. Wartość najmniejszą zbadamy oczywiście funkcją MIN.

Czyli każdą komórkę w całym zakresie będziemy porównywać: niezależnie od tego, gdzie ona w tym zakresie się znajduje. Będziemy porównywać ją natomiast do wartości minimalnych konkretnych wierszy. Czyli warunek formatowania warunkowego będzie wyglądał tak:

=C4=MIN($C4:$I4)

Warto zauważyć, że funkcja MIN szuka najmniejszej wartości w zakresie z zablokowanymi kolumnami i odblokowanymi wierszami. Kolumny są zablokowane, ponieważ funkcja MIN ma szukać wartości minimalnej zawsze od C do I. Zakres tych kolumn jest stały i nie może się przesunąć w prawo (a tak własnie by się stało, gdybyśmy nie blokowali kolumn): czyli zarówno komórkę C4 jak i G4 mamy porównywać do najmniejszej wartości zakresu $C4:$I4. Natomiast dla komórki C5 i G5 ma to być już zakres $C5:$I5. Stąd z kolei zmieniają się wiersze.

To jak już formułę mamy omówioną, to kwestia nadania formatowania warunkowego:

1. Zaznacz zakres C4:I9, zaczynając zaznaczenie od komórki C4 – to bardzo ważne!

 

2. Wybierz z menu: Narzędzia główne/ Formatowanie warunkowe/ Użyj formuły do określenia komórek, które należy sformatować

3. W pole Edytuj opis reguły wpisz wcześniej ustaloną formułę. Jak na obrazku:

Reguła formatowania 1

Reguła formatowania 1

I tyle. Efekt mamy taki:

Najmniejsze wartości w wierszach - wynika

Najmniejsze wartości w wierszach – wynik

2. Wyróżnianie całego wiersza

Drugi case już pójdzie szybciej, po zrozumieniu tego wyżej. Tutaj mamy zaznaczyć cały wiersz, jeśli występuje w nim najmniejsza wartość wybranej kolumny. U nas będzie to kolumna F, czyli czwartek (trochę niefortunnie nazwałam ją cyfrą, ech).

Czyli będziemy teraz szukali wartości minimalnej w kolumnie F, konkretnie w zakresie $F$14:$F$19. Zawsze w nim, dlatego zablokowałam go bezwzględnie. Czyli będzie MIN($F$14:$F$19).

I do tego będziemy porównywać tylko jedną komórkę z wiersza: tę w kolumnie F. Ale, żeby podświetlić cały wiersz, musimy dla każdej komórki sprawdzić ten warunek. Ten, czyli porównujący komórkę w kolumnie F z tego wiersza do wartości minimalnej wspomnianego zakresu. Reguła formatowania warunkowego będzie więc taka:

=$F14=MIN($F$14:$F$19)

A wszystko należy wrzucić do nowej reguły formatowania warunkowego, czyli jeszcze raz:

1. Zaznacz zakres B14:I19 (lub jeśli chcesz wyróżniać też sumę to zakres: B14:J19), zaczynając zaznaczenie od komórki B14 – to bardzo ważne!

2. Wybierz z menu: Narzędzia główne/ Formatowanie warunkowe/ Użyj formuły do określenia komórek, które należy sformatować

3. W pole Edytuj opis reguły wpisz wcześniej ustaloną formułę. Jak na obrazku:

Reguła formatowania 2

Reguła formatowania 2

Po zatwierdzeniu wszystkich okienek otrzymamy taki wynik:

Wiersz z najmniejszą wartością w kolumnie - wynik

Wiersz z najmniejszą wartością w kolumnie – wynik

I wszystko :). Mam nadzieję, że Ci się przyda!

 

Tutaj plik z gotowcem do pobrania:

Malinowy Excel Najmniejsza wartość w wierszu format war dw.xlsx

 

A tutaj wersja wideo:

 

 

Jeżeli chcesz lepiej poznać Excela na poziomie średniozaawansowanym zapisz się na listę zainteresowanych kursem Excel w codziennej pracy cz. 2! Podczas oczekiwania na kurs będę Ci wysyłała informacje, ciekawostki i excelowe triki. Bezpłatnie! 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

11 komentarzy

  1. Super tłumaczysz!

    Reply
    • Bardzo się cieszę, dziękuję! 🙂

      Reply
  2. czy możesz pomóc w formule, która sformatuje wartości minimalne ale z pominięciem wartości 0?

    Reply
    • Hej, pewnie. A co dokładnie trzeba zrobić? Czy chcesz wyświetlić wartość minimalną (jedną), ale nie może to być zero? Dobrze rozumiem?

      Reply
      • Cześć, właśnie borykam się z takim samym problemem. Chciałbym stworzyć formułę, które będzie zaznaczać(wyświetlać) wartość min. ale z pominięciem zera. Czy mogę prosić o pomoc?

        Reply
        • Hej Piotrek, ja bym tutaj użyła kombinacji MIN.K i JEŻELI. Napiszę o tym wpis w przyszłym tygodniu, a Ty daj znać, do czego Ci to potrzebne 🙂

          Reply
      • Cześć. borykam się z tym samym problemem. czy możesz pomóc?

        Reply
      • Witaj,

        Dokładnie tak jak koleżanka wyżej chciałbym podświetlić najniższą wartość (poza zerem) dla danej kolumny. Z góry dziękuję za odpowiedź.

        Reply
        • Hej,
          to ja bym zastosowała taką regułę formatowania warunkowego: =MIN.WARUNKÓW(C$27:C$32;C$27:C$32;”<>0″)=C27
          Zakładając, że zakresy w tej formule wskazują konkretną kolumnę, a C27 to pierwsza komórka formatowanego zakresu.

          Reply
  3. witam, mam pytanie: utworzyłam kolejną kolumnę w której chcę dodać marżę do kwoty najniższej która mi się podświetliła, jak mam to zapisać w formule? bardzo proszę o pomoc;)

    Reply
    • Hej,
      ja bym zrobiła w tej nowej kolumnie zwykłą funkcję MIN z odpowiedniego wiersza i do niej dodała marżę. Czyli np. MIN(C4:I4)+marża.
      O to chodziło?

      Reply

Submit a Comment

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Pin It on Pinterest