David Ringstrom, CPA

Księgowi często otrzymują prozaiczne zadanie identyfikowania wariancji przekraczających określone granice tolerancji. Zamiast szukać wzrokiem kolumn liczb, można użyć funkcji formatowania warunkowego programu Excel, aby takie odchylenia rzucały się w oczy na ekranie. Pokażę, jak można wykorzystać formatowanie warunkowe Excela do identyfikacji różnic, które wynoszą zarówno 2000+/- dolarów, jak i 10%+/- budżetu.

Formatowanie warunkowe jest potężną funkcją, ale ma pewne niuanse, które mogą frustrować niewtajemniczonych użytkowników. W zależności od warunków, jakie chcesz zastosować, często najlepiej jest zastosować formatowanie do pojedynczej komórki, a następnie użyć funkcji Wklejanie formatowania specjalnego Excela, aby przenieść formatowanie do pozostałych powiązanych komórek. Formatowanie warunkowe oparte na formułach, które za chwilę zademonstruję, często nie daje pożądanych rezultatów, gdy próbuje się je zastosować do wielu wierszy lub kolumn jednocześnie.

Rysunek 1 przedstawia reprezentatywne porównanie stanu faktycznego z budżetem. Naszym celem jest zaznaczenie na żółto wszystkich różnic wynoszących zarówno +/- 2 000 USD, jak i +/- 10%. Konkretnie, wiersz powinien być podświetlony tylko wtedy, gdy spełnione są oba testy. Najpierw opiszę kroki, które należy wykonać w programie Excel 2007 i nowszych, a następnie przedstawię krótki opis działania programu Excel 2003. Aby wprowadzić taką regułę, należy wykonać następujące czynności:

Rysunek 1: Użyjemy formatowania warunkowego, aby zidentyfikować wariancje, które wynoszą zarówno +/- 2000 USD, jak i +/- 10%.

1. Excel 2007 i nowsze wersje: Kliknij komórkę D2, na karcie Strona główna wybierz polecenie Formatowanie warunkowe, a następnie kliknij polecenie Nowa reguła, jak pokazano na rysunku 1.

Excel 2003 i wcześniejsze:Kliknij komórkę D2, wybierz polecenie Formatuj, a następnie Formatowanie warunkowe.

2. Excel 2007 i nowsze: Wybierz opcję Użyj formuły, aby określić, które komórki należy sformatować, jak pokazano na rysunku 2.

Excel 2003 i wcześniejsze: Zmień opcję Wartość komórki jest na Wartość formuły jest.

Rysunek 2: Formatowanie warunkowe oferuje wiele opcji, w tym tworzenie własnych formuł.

3. Wszystkie wersje programu Excel: Wprowadź tę formułę:

=AND(ABS($D2)>2000,ABS($E2)>0.1)

4. Wszystkie wersje programu Excel: Kliknij przycisk Format, wybierz kartę Wypełnienie, kliknij żądany kolor, na przykład żółty pokazany na rysunku 3, a następnie kliknij dwukrotnie przycisk OK.

Rysunek 3: Kolor jest jednym z wielu rodzajów formatowania, które można zastosować za pomocą formatowania warunkowego.

5. Wszystkie wersje programu Excel: Zaznacz komórkę D2, kliknij przycisk Malarz formatów pokazany na rysunku 4 i zastosuj formatowanie do komórek od D2 do E10. Malarz formatów znajduje się na pasku narzędzi Standardowy w programie Excel 2003.

Rysunek 4: Użyj ikony Malarza formatów, aby przenieść formatowanie z komórki D2 do komórek od D2 do E10.

6. Wszystkie wersje programu Excel: Zastosuj ponownie formaty liczb procentowych w komórkach od E2 do E10, jak pokazano na rysunku 5. Excel nie oferuje możliwości samodzielnego przeniesienia formatowania warunkowego bez nadpisywania istniejących formatów liczbowych. Jednak o wiele łatwiej jest prawidłowo zastosować formatowanie warunkowe, stosując je najpierw w pojedynczej komórce, a następnie w razie potrzeby ponownie zastosować formaty liczbowe.

Rysunek 5: Może być konieczne przywrócenie niektórych formatów liczbowych po przeniesieniu formatowania warunkowego z jednej komórki do wielu.

Jeśli używasz programu Excel 2007 lub nowszego, możesz teraz kliknąć prawym przyciskiem myszy jedną z żółtych komórek, wybrać polecenie Filtr, a następnie Filtr według koloru wybranej komórki, jak pokazano na rysunku 6. W programie Excel 2007 i nowszych możesz również sortować według koloru. Rysunek 7 przedstawia przefiltrowaną listę. Aby usunąć filtr, należy kliknąć ikonę Filtr na karcie Dane w menu programu Excel znanym jako Wstążka.

Rysunek 6: W programie Excel 2007 i nowszych można filtrować (a także sortować) na podstawie koloru.

Rysunek 7: Wariancje zostały zidentyfikowane i wyodrębnione.

Jeśli chodzi o użytą przeze mnie formułę:

  • Funkcja ABS zwraca wartość bezwzględną liczby - innymi słowy (17 396 USD) zamienia się na 17 396 USD.
  • Funkcja AND pozwala na jednoczesne testowanie do 255 warunków. W tym przypadku testuję, czy wartość bezwzględna wariancji w kolumnie D jest większa niż 2 000 $, a wartość bezwzględna procentowej wariancji w kolumnie E jest większa niż 10%. Jeśli oba testy są prawdziwe, zostanie zastosowane formatowanie warunkowe.
  • Znaki dolara przed literami kolumn mają kluczowe znaczenie przy stosowaniu formatowania warunkowego w ten sposób, ponieważ w efekcie kopiujemy i wklejamy format do drugiej kolumny. Jeśli pominiesz znaki dolara przed odwołaniami do kolumn, Excel dostosuje odwołania do kolumn, co oznacza, że formatowanie warunkowe nie przyniesie pożądanego rezultatu.

Przeczytaj więcej artykułów Davida Ringstroma.

O autorze:

David H. Ringstrom, CPA kieruje firmą Accounting Advisors, Inc. z siedzibą w Atlancie, zajmującą się doradztwem w zakresie oprogramowania i baz danych, świadczącą usługi szkoleniowe i konsultingowe na terenie całego kraju. Skontaktuj się z Davidem pod adresem [email protected] lub śledź go na Twitterze. David jest prelegentem na konferencjach poświęconych Microsoft Excel, a także prowadzi webcasty dla kilku dostawców usług CPE, w tym dla partnera CPE Link w AccountingWEB.