KHIZHNYAKOVPlan i dziennik kontroli wewnętrznych w Excelu: jak go zbudować
Plan i dziennik kontroli wewnętrznych w Excelu: harmonogram, nieprawidłowości, usuwanie — szablon — Zobacz szablon →
Plan kontroli zwykle powstaje w Wordzie, wyniki trafiają do protokołów, a lista uwag do usunięcia żyje w mailach. Po kwartale nikt nie wie, które kontrole się odbyły, a które nieprawidłowości nadal czekają na naprawę. Problem nie leży w braku dyscypliny, tylko w tym, że informacje są rozproszone.
Poniżej pokazuję, jak w Excelu zbudować jeden dziennik, w którym każda kontrola to jeden wiersz, a opóźnienia widać bez przeglądania dokumentów. Dostaniesz układ kolumn, formuły z polskimi nazwami funkcji, przykład z liczbami i listę typowych błędów. Na końcu opisuję gotowy szablon, jeśli nie chcesz składać wszystkiego sam.
Jak zaplanować dziennik kontroli
Zacznij od zasady: jedna kontrola to jeden wiersz. Wszystko, co dotyczy tej kontroli, ma być w tym samym wierszu, bo dopiero wtedy można filtrować i sumować.
Kolumny, które naprawdę są potrzebne
- rodzaj kontroli i dział, którego dotyczy,
- temat i wykonawca,
- planowany początek i czas trwania w dniach,
- planowane zakończenie (liczone, nie wpisywane),
- faktyczna data zakończenia,
- liczba wykrytych nieprawidłowości, termin ich usunięcia i liczba usuniętych,
- status.
Rodzaje kontroli, działy i wykonawców trzymaj na osobnym arkuszu ze słownikami i podpinaj je jako listy rozwijane. Dzięki temu nikt nie wpisze „Księgowość”, „księgowość” i „Dział księgowości” jako trzech różnych działów.
Formuły, na których to się opiera
Planowane zakończenie to początek plus czas trwania minus jeden dzień (jeśli liczysz dni kalendarzowe włącznie z pierwszym):
=B2+C2-1
Status zaległej kontroli sprawdzisz tak:
=JEŻELI(E2<>"";"Wykonana";JEŻELI(DZIŚ()>D2;"Zaległa";"Zaplanowana"))
Pozostałe do usunięcia nieprawidłowości to po prostu różnica dwóch kolumn: wykryte minus usunięte. Aby policzyć, ile uwag wciąż wisi w całym dziale, użyj SUMA.JEŻELI, a liczbę zaległych kontroli w danym miesiącu da LICZ.WARUNKI:
=LICZ.WARUNKI(F:F;"Zaległa";G:G;"Kowalski")
Przykład z liczbami
Załóżmy plan na kwartał: 12 kontroli, z czego 9 zakończono. Dwie są po terminie, jedna jeszcze się nie zaczęła. W zakończonych wykryto łącznie 14 nieprawidłowości, a usunięto 9. Realizacja planu to 9 / 12 = 75%, a do usunięcia zostaje 5 uwag. Jeśli termin usunięcia dla trzech z nich minął, tym trzem wierszom nadajesz czerwony kolor warunkowy.
| Wskaźnik | Wartość |
|---|---|
| Kontrole zaplanowane | 12 |
| Kontrole zakończone | 9 |
| Realizacja planu | 75% |
| Nieprawidłowości wykryte / usunięte | 14 / 9 |
| Pozostało do usunięcia | 5 |
Semafor opóźnień
Dobrze działa prosty podział na trzy kolory: czerwony dla przekroczonego terminu, żółty dla terminu, który upływa w ciągu np. 7 dni, zielony dla reszty. Liczbę dni „na ostrzeżenie” wpisz do osobnej komórki, żeby nie zmieniać formuł przy każdej zmianie zasad. Do kolorowania użyj formatowania warunkowego z formułą opartą na dacie.
Najczęstsze błędy
- Wpisywanie planowanego zakończenia ręcznie. Po zmianie terminu początku data się rozjeżdża.
- Brak osobnej kolumny na termin usunięcia uwag. Kontrola jest „zamknięta”, a nieprawidłowości dalej istnieją.
- Mieszanie dat i tekstu, np. „do końca marca”. Excel nie policzy takiego opóźnienia.
- Brak daty raportu. Warto móc zobaczyć stan na wybrany dzień, a nie tylko na dziś.
- Traktowanie dziennika jako zamiennika protokołów. To narzędzie do ewidencji i pilnowania terminów, a tryb przeprowadzania kontroli zawsze sprawdzaj w swoich dokumentach wewnętrznych.
Gotowy szablon: plan i dziennik kontroli wewnętrznych
Jeśli wolisz nie budować tego od zera, jest gotowy płatny szablon. Kontrolę wpisujesz jednym wierszem, a planowany termin zakończenia liczy się sam. Semafor pokazuje zaległe i „palące” kontrole, a osobny semafor pilnuje terminów usuwania nieprawidłowości.
Plik ma trzy arkusze:
- „Słowniki”: rok planu, rodzaje kontroli, działy, wykonawcy i parametr „termin pilny”.
- „Plan i dziennik”: do 500 kontroli z datami planowanymi i faktycznymi, nieprawidłowościami i usuwaniem.
- „Podsumowanie”: realizacja planu według miesięcy, statusy, obciążenie wykonawców, nieusunięte nieprawidłowości i dwa wykresy.
W zestawie dostajesz pustą tabelę oraz wypełniony przykład z fikcyjnymi danymi. To arkusz na zwykłych formułach, bez makr, działa w Excelu 2007 i nowszym oraz w LibreOffice, a plik jest po polsku. Progi, takie jak „termin pilny”, ustawiasz w parametrach, a obliczenia mają charakter orientacyjny.
FAQ
Jak często aktualizować dziennik?
Najlepiej na bieżąco, przy każdej zmianie statusu kontroli. Jeśli to niemożliwe, ustal stały dzień, np. piątek, kiedy wykonawcy uzupełniają daty i liczby usuniętych uwag.
Czy jeden plik wystarczy dla całej firmy?
Dla kilkuset kontroli rocznie tak, o ile każdy wiersz ma dział i wykonawcę. Przy większej skali lepiej prowadzić osobny plik na rok, żeby nie zwalniać arkusza.
Co zrobić z kontrolą, która została przesunięta?
Zmień planowany początek lub czas trwania, a termin zakończenia przeliczy się sam. Powód przesunięcia warto dopisać w osobnej kolumnie z uwagami, żeby historia nie zginęła.
Czy da się zobaczyć stan na koniec miesiąca?
Tak, jeśli formuły porównują daty nie z funkcją DZIŚ(), tylko z komórką „data raportu”. Wpisujesz datę, a statusy i opóźnienia liczą się na ten dzień.
