Dowiedz się, jak podświetlać rzędy w programie Excel na podstawie duplikatów

Jak zapewne wiesz, funkcja formatowania warunkowego programu Excel pozwala na identyfikację duplikatów w kolumnie. Ta technika wymaga pewnego poziomu zaawansowania technologicznego w niektórych wersjach programu Excel, ale tylko kilka kliknięć myszką w programie Excel 2007 i późniejszych. Czytelnik o nazwisku Chris zapytał mnie, czy możliwe jest rozszerzenie tej funkcji nie tylko o identyfikację duplikatów w jednej kolumnie, ale również o dalsze podświetlenie sąsiednich komórek w tym samym wierszu. W tym artykule najpierw pokażę, jak identyfikować duplikaty wartości w pojedynczej kolumnie, a następnie pokażę, jak rozszerzyć tę funkcjonalność w sposób, którego poszukuje Chris. Zrobię to najpierw dla Excela 2007 i później, a następnie przedstawię naprzemienne instrukcje dla Excela 2003 i Excela 2011 dla Maca.

Chris ma dane o strukturze nieco podobnej do rys. 1, więc najpierw zidentyfikujmy podwójne przypadki Joe w kolumnie A w programie Excel 2007, a następnie:

  1. Wybierz komórki A1:A8.
  2. Na karcie głównej programu Excel wybierz Formatowanie warunkowe, Reguły dla komórek podświetlanych, a następnie Duplikuj wartości.
  3. W tym przypadku program Excel wyświetla okno dialogowe Duplicate Values (Podwójne wartości), a także zapewnia podgląd formatowania, które ma być zastosowane do arkusza, jak pokazano na Rysunku 1. Zauważ, że okno dialogowe Duplikaty wartości ma podwójne zastosowanie, ponieważ zamiast Duplikatów można wybrać Unique Values.
  4. Jeśli domyślna kombinacja kolorów czerwonego i różowego nie odpowiada Twoim potrzebom, kliknij listę rozwijaną po prawej stronie, a następnie wybierz opcję Format niestandardowy, aby wyświetlić okno dialogowe Format Komórek, z którego można dokonać różnych wyborów. Kliknij, aby potwierdzić swój wybór.

Rysunek 1: Użyj formatowania warunkowego, aby zaznaczyć duplikaty w swoich danych.

Choć szybkie i proste, podejście to dotyczy tylko danego zakresu i nie może dotyczyć komórek spoza wybranego zakresu. Będziemy musieli zagłębić się nieco głębiej, aby osiągnąć cel Chrisa poprzez stworzenie własnej reguły. Stosowanie formatowania warunkowego w ten sposób jest bardzo zróżnicowane, więc czytaj uważnie:

  1. Wybierz komórki A1:D8. W tym przypadku chcemy wybrać całą tabelę danych, a nie pojedynczą kolumnę.
  2. Wybierz Formatowanie warunkowe z zakładki głównej, a następnie wybierz Nowa reguła.
  3. Wybierz opcję Użyj formuły, aby określić, które komórki mają być sformatowane z okna dialogowego Nowa reguła formatowania.
  4. Wprowadź następującą formułę w polu Format wartości, gdzie ta formuła jest prawdziwa:

=COUNTIF($A$1:$A$8,$A1)>1

Wpisz swój adres e-mail, aby zapisać się do naszego newslettera i otrzymywać co tydzień najlepsze informacje o AccountingWEB.Wpisz adres e-mail *Enter adres e-mailSign up

Funkcja COUNTIF ma dwa argumenty:

  • Zasięg: W tym zakresie są komórki $A$1:$A$8. Znaki dolara uczą Excela patrzeć na wszystkie wiersze w pierwszej kolumnie naszego zestawu danych.
  • Kryteria: Kryteria w tym przypadku to $A1. Zauważ, że przed wierszem nie ma znaku dolara. Chcemy, aby Excel zawsze patrzył na kolumnę A, ale aby przesuwał numer wiersza dla każdego wiersza naszego zestawu danych.

Część>1 formuły informuje Excel, że chcemy, aby reguła została zastosowana tylko wtedy, gdy wynik funkcji COUNTIF jest większy niż 1, co oznacza, że sformatowane zostaną tylko wiersze z duplikatem wartości w kolumnie A.

  1. Kliknij przycisk Format, dokonaj wyboru formatowania według własnych upodobań, a następnie kliknij dwukrotnie przycisk OK, aby zastosować formatowanie. Wynik końcowy powinien wyglądać jak na rysunku 2.

Rysunek 2: Wiersze z powieloną wartością w kolumnie A są teraz sformatowane.

:

Przy stosowaniu formatowania warunkowego w ten sposób krytyczne znaczenie mają znaki dolara wokół referencji komórki. Formatowanie warunkowe ma nieuchwytny aspekt, ponieważ wprowadzasz jedną formułę, która musi obejmować wiele wierszy. Tak więc, jeśli pominiesz którykolwiek ze znaków dolara znajdujących się powyżej zakresu, na który formatowanie warunkowe patrzy, może on ulec przesunięciu i w rezultacie albo nie znajdziesz zduplikowanych wartości, albo nie podświetlisz danych wierszy. Stary quote "Jeśli na początku nie uda Ci się, spróbuj ponownie" zdecydowanie dotyczy formatowania warunkowego. Jeżeli twoja reguła nie działa zgodnie z oczekiwaniami, wybierz Formatowanie warunkowe, Edytuj regułę, a następnie kliknij dwukrotnie na swojej regule i sprawdź swoją formułę.

Aby odtworzyć funkcję Duplikaty światła w programie Excel 2003, jak pokazano na rysunku 3:

1. 1. Wybierz komórki A1:A8.

2. Wybierz Formatowanie, a następnie Formatowanie warunkowe.

3. Zmień wartość komórki na Formułę Jest i następnie wpisz ten wzór: =COUNTIF($A$1:$A$8,$A1)>1

4. Kliknąć przycisk Format, wybrać żądane formatowanie, a następnie dwukrotnie kliknąć OK.

Kroki do podświetlenia całego rzędu są prawie identyczne, wystarczy wybrać komórki A1:D8, a następnie powtórzyć kroki od 2 do 4 powyżej.

Rysunek 3: Wykonaj poniższe kroki, aby zaznaczyć powielone wartości w programie Excel 2003.

:

Dostęp do funkcji Formatowanie warunkowe można uzyskać z paska menu lub z wstążki programu Excel w programie Excel dla komputerów Mac 2011. W przypadku korzystania z tej wstążki identyfikacja duplikatów wartości w programie Excel for Mac 2011 jest identyczna jak w programach Excel 2007 i nowszych. Kroki służące do wyróżniania wierszy z powielonymi wartościami są nieco inne:

  1. Wybierz komórki A1:D8.
  2. Wybierz Formatowanie warunkowe, a następnie Nowa reguła.
  3. W oknie dialogowym Nowa reguła formatowania zmień Styl na Klasyczny.
  4. Zmień formatowanie tylko górnych lub dolnych wartości rankingowych, aby użyć formuły do określenia, które komórki mają być sformatowane.
  5. Wpisz ten wzór: =COUNTIF($A$1:$A$8,$A1)>1
  6. Jeśli domyślna kombinacja kolorów czerwonego i różowego nie odpowiada Twoim potrzebom, kliknij na liście rozwijanej Formatuj z listą, a następnie wybierz Format niestandardowy, aby wyświetlić okno dialogowe Formatuj komórki, z którego można dokonać różnych wyborów. Kliknij dwukrotnie przycisk OK, aby potwierdzić wybór.

Rysunek 4: Tworzenie nowej reguły w celu podświetlenia wierszy, które zawierają podwójną wartość w kolumnie A.

Aby usunąć formatowanie warunkowe z szeregu komórek:

  • Excel 2007 i później: Metoda brute-force polega na wybraniu Home, Formatowanie Warunkowe, Clear Rules, a następnie Clear Rules z całego arkusza. Jeśli chcesz być bardziej zniuansowany w kwestii usuwania, wybierz zakres komórek, a następnie użyj opcji Wyczyść reguły z wybranych komórek.
  • Excel 2003 i wcześniejsze: Wybierz zakres komórek, które zawierają formatowanie warunkowe, a następnie wybierz Formatowanie, Formatowanie warunkowe, a następnie kliknij przycisk Usuń. Zaznaczyć pola wyboru dla warunków, które chcesz usunąć.
  • Excel 2011 dla komputerów Mac: Wybierz zakres komórek, które zawierają formatowanie warunkowe, a następnie wybierz Formatowanie, Formatowanie warunkowe, a następnie kliknij znak minus w lewym dolnym rogu okna dialogowego po wybraniu reguły, którą chcesz usunąć.

O autorze:

David H. Ringstrom, CPA, szef Accounting Advisors, Inc., firmy doradczej w zakresie oprogramowania i baz danych z siedzibą w Atlancie, świadczącej usługi szkoleniowe i doradcze w całym kraju. Skontaktuj się z Davidem pod adresem [chronione pocztą elektroniczną] lub śledź go na Twitter . David przemawia na konferencjach o programie Microsoft Excel, uczy webcastów dla i pisze niezależne artykuły w programie Excel dla AccountingWEB , et. al.