Podświetl kluczowe dane za pomocą formatowania warunkowego w Excelu

Jeśli wejdziesz do jakiegokolwiek biura, prawdopodobnie znajdziesz użytkownika programu Microsoft Excel. Jest to przydatne narzędzie do analizy biznesowej, jeśli jest używane prawidłowo. Problem z wieloma arkuszami kalkulacyjnymi polega na tym, że wszystkie komórki wyglądają tak samo. Trzeba polować na kluczowe informacje. W tym poradniku pokażę, jak wyróżniać komórki w Excelu za pomocą kilku metod. (Pobierz przykładowy arkusz kalkulacyjny w sekcji Zasoby.)

Rutynowo spotykam się z takim scenariuszem. Dostajesz od kogoś arkusz kalkulacyjny z setkami wierszy danych, które wyglądają tak samo. Wszystko jest sformatowane w ten sam nudny sposób. Ale czy dane są takie same? Czy są wartości komórek, które różnią się od reszty? Czy coś jest poza średnią? Są to typowe pytania użytkowników, którzy otwierają arkusze kalkulacyjne.

Formatowanie komórek Excela na podstawie warunków

Zamiast skanować każdą komórkę, można zlecić programowi wykonanie pewnej pracy za pomocą formatowania warunkowego. Dzięki temu Excel może zastosować zdefiniowany format do komórek, które spełniają określone kryteria. Są to reguły, które możesz zdefiniować.

Celem jest wyróżnienie ważnych informacji, abyś mógł je łatwiej znaleźć.

Excel już wykonuje niektóre z tych czynności za Ciebie. Na przykład, podczas formatowania liczb, istnieją opcje wyświetlania liczb ujemnych w kolorze czerwonym. Jest to przykład formatu predefiniowanego.

Program pozwala nawet na stosowanie opcji formatowania w poszczególnych komórkach lub wierszach. Najprostszą metodą jest zastosowanie przez Excela formatowania warunkowego, jeśli spełnia ono określone warunki lub lokalizacje. Wykorzystuje to metodę „Wartość komórki jest”.

Niektóre przykłady podkreślania wartości komórki obejmują:

• Zastosowanie czerwonego wypełnienia, jeśli wartość jest mniejsza niż 50
• Zastosowanie stylu kursywy, pogrubienia czcionki, jeśli wartość mieści się w przedziale od 70 do 90
• Zastosowanie zielonego koloru czcionki, jeśli tekst zawiera „Montana.”
• Podkreśl wartości, które są równe 15
• Zastosuj żółte wypełnienie do zduplikowanych wartości
• Dodaj ikonę strzałki w górę do wartości komórek powyżej 10%

Excel pozwala również używać formuł do formatowania warunkowego. Jedną z zalet formuł Excela jest możliwość odwołania się do wartości w innym miejscu arkusza kalkulacyjnego. W poniższym przykładzie żądam, aby jeśli wartość w B2 jest większa niż wartość w C2, zastosować zielony kolor tła.

Microsoft znacznie ulepszył tę funkcję na przestrzeni lat i obecnie predefiniuje popularne przykłady, więc nie musisz tak często polegać na formułach. Poniżej znajduje się kilka popularnych przykładów.

Jak zastosować formatowanie warunkowe w programie Excel

Dodawanie nowej reguły formatowania jest łatwe. Najtrudniejszą częścią jest znalezienie tego, co chcesz podkreślić w swoim arkuszu kalkulacyjnym. Pomocne jest zastanowienie się nad potrzebami odbiorców i działaniami, które mogą podjąć.

Przykład 1: Podkreśl komórki powyżej określonej liczby

Jeśli chcesz śledzić, to jest to Przykład 1 z przykładowego arkusza kalkulacyjnego. Zamierzam podświetlić wybrane komórki, jeśli liczba ludności stanu przekracza lub jest równa 2% ludności USA.

1. Otwórz przykładowy arkusz kalkulacyjny state-counts-cf.xlsx i kliknij kartę Przykład 1.
2. Kliknij komórkę F2.
3. Zaznacz całą kolumnę, naciskając kombinację klawiszy Ctrl +Shift + ↓.
4. Z karty Strona główna kliknij przycisk Formatowanie warunkowe.
5. Z menu rozwijanego wybierz opcję Reguły podkreślania komórek.
6. Z menu bocznego wybierz opcję Większe niż…

1. dostosuj wartość w polu Formatuj komórki, które są WIĘKSZE NIŻ:.

Uwaga: Excel wstępnie wypełni to pole na podstawie istniejących wartości Twoich zaznaczonych komórek.

1. Wprowadź żądaną wartość. W naszym przypadku jest to 2,0%. Excel zacznie zaznaczać komórki za Ciebie.
2. Kliknij na klawiaturze_strzałka_w dół trójkąt, aby dokonać wyboru koloru.

Uwaga: Możesz utworzyć inny schemat, wybierając Format niestandardowy…

1. Kliknij OK.

Powinieneś teraz zobaczyć zastosowane wyniki.

Przykład 2: Podświetl 10 najlepszych wpisów

Jednym z popularnych żądań jest znalezienie najlepszych wpisów, niezależnie od tego, czy jest to określona liczba, czy procent. Jest to przykład jednego z predefiniowanych lub wbudowanych formatów Excela. Innymi słowy, nie musisz wprowadzać konkretnych wartości, jak to miało miejsce w przykładzie 1. Te ustawienia wstępne są zbudowane wokół typowych elementów, których ludzie szukają, takich jak Top 10 lub wyniki poniżej średniej. Zaletą jest to, że nie musisz zajmować się matematyką.

W tym przykładzie chcę zaznaczyć 10 najlepszych stanów według liczby miejsc w Izbie Stanów Zjednoczonych w kolumnie D.

1. Otwórz przykładowy arkusz kalkulacyjny state-counts-cf.xlsx i kliknij zakładkę Przykład 2.
2. Kliknij komórkę D2.
3. Zaznacz całą kolumnę, naciskając Ctrl +Shift + ↓.
4. Z karty Strona główna kliknij przycisk Formatowanie warunkowe.
5. Z menu rozwijanego Formatowanie warunkowe wybierz Reguły góra/dół.
6. Z menu bocznego wybierz opcję Top 10 Items…
7. W oknie dialogowym Top 10 Items dostosuj licznik lub schemat kolorów, jeśli to konieczne.
8. Kliknij przycisk OK.

Przykład 3: Pokaż paski danych zamiast cieniowania kolorami

Inną przydatną opcją jest użycie „pasków danych”. Jest to kolejny typ wizualizacji, który możesz zastosować. Kluczową różnicą formatu Pasek danych jest to, że pokazuje na wszystkich komórkach, zamiast spełniać określony warunek.

Domyślnie Excel będzie utrzymywał tło wartości tekstowych. Uważam, że może to być rozpraszające. W tym przykładzie zduplikowałem kolumnę % zmiany, ale powiedziałem Excelowi, aby nie umieszczał wartości danych w kolumnie I. Wynikiem jest tylko pasek danych.

Format paska danych nie działa dobrze ze wszystkimi typami danych. Jest to dobry przykład wypróbowania różnych formatów, aby zobaczyć, co działa najlepiej dla Twoich danych. Jest to szczególnie prawdziwe, jeśli masz wartości ujemne lub wąski zakres.

1. Otwórz przykładowy arkusz kalkulacyjny state-counts-cf.xlsx i kliknij kartę Przykład 3.
2. Kliknij komórkę I2.
3. Zaznacz całą kolumnę, naciskając kombinację klawiszy Ctrl +Shift + ↓.
4. Z karty Strona główna kliknij przycisk Formatowanie warunkowe.
5. Z menu rozwijanego Formatowanie warunkowe wybierz opcję Paski danych.
6. Z menu bocznego wybierz polecenie Więcej reguł…

1. W sekcji Edytuj opis reguły: zaznacz opcję Pokaż tylko pasek.

1. Kliknij przycisk OK.

Przykład 4: Podświetlanie wiersza

Podświetlanie komórek odstających jest świetne, ale czasami, jeśli masz duży arkusz kalkulacyjny, możesz nie widzieć kolorowych komórek, ponieważ znajdują się poza ekranem. W takich sytuacjach pomocne jest podświetlenie całego wiersza. W ten sposób, niezależnie od tego, które kolumny przeglądasz, wiesz, które wiersze są ważne. Działa to najlepiej, gdy masz 1 warunkowy.

Kluczowa różnica polega na tym, że nie będziemy podświetlać kolumny, lecz cały arkusz. W ten sposób uzyskamy efekt wiersza.

1. Otwórz przykładowy arkusz kalkulacyjny state-counts-cf.xlsx i kliknij kartę Przykład 4.
2. Kliknij komórkę I2.
3. Zaznacz wszystkie wiersze, naciskając klawisze Ctrl +Shift + ↓ + →.
4. Na karcie Strona główna kliknij przycisk Formatowanie warunkowe.
5. Wybierz opcję Nowa reguła…
6. W module Edytuj regułę formatowania wybierz opcję Użyj formuły do określenia komórek do sformatowania.
7. W polu Formatuj wartości, w których ta formuła jest prawdziwa: wpisz swoje kryteria.

1. Kliknij przycisk Formatuj…, aby zastosować swój kolor podświetlenia.
2. Kliknij przycisk OK, aby zaakceptować kolor.

W powyższym przykładzie proszę o zaznaczenie każdego wiersza, w którym wartość w kolumnie F (Procent populacji USA) jest większa niż 2%. Kolor podświetlenia to zielony. Używam również mieszanego odwołania do komórek, umieszczając znak $ przed F2 w formule.

outlined_flag Wskazówka: Jeśli nie jesteś pewien, że masz poprawną formułę, zawsze możesz ją przetestować w innej kolumnie. Na przykład w komórce H2 mogę wpisać formułę =$F2>20%. Komórka H2 zostałaby oceniona jako TRUE lub FALSE. Następnie możesz skopiować formułę w dół kolumny, aby upewnić się, że uzyskałeś oczekiwane wyniki.

1. Kliknij OK.

Znajdowanie reguł formatowania warunkowego

Czasami dostajemy arkusz kalkulacyjny i widzimy wyróżnione komórki lub wiersze. Ale… gdzie są reguły warunkowe, czy ktoś ręcznie sformatował te komórki? Na szczęście Excel udostępnia sposób, aby zobaczyć reguły formatowania warunkowego. Możesz użyć przykładowego arkusza kalkulacyjnego do naśladowania lub użyć własnego.

1. Otwórz swój arkusz kalkulacyjny.
2. Na karcie Strona główna kliknij polecenie Formatowanie warunkowe.
3. Wybierz polecenie Zarządzaj regułami… z menu rozwijanego.

1. W Menedżerze reguł formatowania warunkowego wybierz swój arkusz.
2. Kliknij przycisk OK.

W powyższym przykładzie widać regułę, którą zastosowałem do wyróżnienia wiersza. Z tego miejsca możesz dodać regułę, edytować regułę lub ją usunąć.

Porady &Tricki

Ten artykuł tylko dotknął niektórych sposobów, w jakie możesz używać formatowania warunkowego. Najlepszym sposobem na naukę jest eksperymentowanie z danymi, czy to własnymi, czy z przykładowym arkuszem kalkulacyjnym. Oto kilka innych wskazówek dla Ciebie:

• Możesz mieć wiele reguł w tej samej kolumnie. Na przykład, możesz chcieć, aby różne wartości zakresu miały różne kolory.
• Zabaw się różnymi formatami, aby zobaczyć, który typ działa najlepiej dla twoich danych.
• Jeśli otrzymujesz nieoczekiwane wyniki, sprawdź, czy uruchamianych jest wiele warunków i w jakiej kolejności. Być może trzeba będzie zmienić kolejność.
• Możesz ustawić zakresy na paskach danych. Na przykład jeśli masz kolumnę % wypełnienia, możesz chcieć ustawić zakres od 0 do 1.
• Możesz usunąć reguły formatowania warunkowego za pomocą opcji Wyczyść reguły.
• Stosuj reguły po wprowadzeniu danych. Czasami podczas wstawiania wierszy tworzone są zduplikowane reguły.

Formatowanie warunkowe Excela może sprawić, że arkusze kalkulacyjne staną się bardziej użyteczne, jeśli zostaną odpowiednio wykorzystane. Ludzie nie będą tracić czasu na szukanie ważnych zmian lub tej „igły w stogu siana”.”

Dodatkowe zasoby

• Przewodnik dla początkujących po programie Excel XLOOKUP
• Jak korzystać z funkcji Excel VLOOKUP
• Jak korzystać z funkcji IF w Excelu
• How to Create an Excel Pivot Table

Disclaimer: Images from Amazon Product Advertising API. I może otrzymać prowizję partnerską na tych produktach, jeśli kupisz. Zaktualizowano: 2021-03-25