Jak kontrolować ceny zakupu u dostawców na podstawie pozycji faktur z KSeF w Arkuszu Google?
Włącz w KSeF Import zakładkę „… - Pozycje” (plany Grupa i Biuro), a w nowej zakładce „Ceny” zbuduj formułę QUERY, która grupuje pozycje po dostawcy i towarze i pokazuje cenę najniższą, najwyższą i ostatnią. Kolumna „zmiana względem najniższej” z formatowaniem warunkowym podświetli towary droższe od najniższej ceny o więcej niż Twój próg. Pobranie pozycji robi KSeF Import, a formuły i próg alertu ustawiasz Ty.
- Zakładka „… - Pozycje” (plany Grupa i Biuro) daje cenę jednostkową netto każdej pozycji w osobnym wierszu – to surowiec do kontroli cen.
-
Formuła
QUERYw osobnej zakładce „Ceny” grupuje pozycje po dostawcy, towarze, jednostce i walucie oraz liczy cenę najniższą, najwyższą i liczbę faktur. -
Jedna formuła z
FILTERiSORTwyciąga ostatnią cenę towaru, a kolumna procentowa pokazuje, o ile jest wyższa od najniższej. - Formatowanie warunkowe z progiem (np. 5%) w osobnej komórce podświetla towary droższe od najniższej ceny – bez przeglądania setek wierszy.
- KSeF Import pobiera i zapisuje pozycje; formuły, próg alertu i decyzję o renegocjacji ceny wykonujesz Ty.
W tym artykule
Od problemu do efektu
PWEDostawca podnosi cenę jednego towaru o kilka procent, a na fakturze jest to jedna linia wśród kilkudziesięciu. Przy zakupach z wielu źródeł nikt nie porównuje cen z poprzednich faktur, więc podwyżkę widać dopiero w marży na koniec miesiąca.
KSeF Import zapisuje każdą pozycję faktury w osobnym wierszu z ceną jednostkową netto. Na tej bazie zbudujesz zakładkę „Ceny”, która z każdej serii faktur wyciąga cenę najniższą, najwyższą i ostatnią dla towaru.
Kolor w kolumnie zmiany ceny pokazuje, które towary kosztują dziś o więcej niż Twój próg powyżej najniższej ceny z okresu. Zamiast szukać podwyżek po fakcie, dostajesz krótką listę towarów do rozmowy z dostawcą.
Dlaczego ceny zakupu trzeba porównywać pozycja po pozycji
Suma faktury kosztowej niewiele mówi o cenach. Faktura od hurtowni może mieć trzydzieści pozycji, a o jej wartości decyduje ilość zamówiona w danym tygodniu. Podwyżka jednostkowej ceny o 4% na towarze kupowanym co tydzień jest realnym kosztem, ale w sumie faktury po prostu ginie.
Portal KSeF służy do przeglądania pojedynczych faktur, więc porównanie ceny tego samego towaru z kilku miesięcy wymagałoby otwierania kolejnych dokumentów i przepisywania cen do własnej tabeli. Tego rodzaju kontrola wymaga danych w układzie „jeden wiersz = jedna pozycja”.
- Kupujesz te same towary u kilku dostawców i chcesz wiedzieć, kto jest tańszy.
- Cena jednostkowa u stałego dostawcy rośnie po cichu, bez osobnej informacji.
- Przed rozmową o rabacie potrzebujesz twardych liczb: ile płaciłeś najmniej, a ile płacisz teraz.
- Marża zależy od kosztu zakupu, więc zmiana ceny musi być widoczna przy pierwszej fakturze, nie po zamknięciu miesiąca.
Zakładka „… - Pozycje” jest dostępna w planach Grupa i Biuro (okres próbny ma pełny zakres planu Biuro, więc możesz przetestować cały opisany układ). KSeF Import pobiera faktury z ostatnich 90 dni, a nowe dopisuje przy kolejnych synchronizacjach – seria cen rośnie z czasem.
Jak zbudować tabelę kontroli cen – krok po kroku
Poniższy układ zakłada domyślną kolejność kolumn zakładki pozycji: A – Nr KSeF, B – Nr faktury, C – Data wystawienia, E – Sprzedawca NIP, F – Sprzedawca, G – Nabywca NIP, I – Waluta, J – Typ faktury, N – Nazwa, O – Indeks, T – Jednostka, U – Ilość, V – Cena jedn. netto (dalej m.in. W – Cena jedn. brutto, X – Rabat/opust, Y – Wartość netto). Jeśli przestawiłeś kolumny, popraw litery w formułach. W arkuszu z polskimi ustawieniami regionalnymi argumenty rozdzielasz średnikami.
-
1
Włącz zakładkę pozycji
W ustawieniach Arkuszy Google w KSeF Import zaznacz opcję „Pozycje”. Po synchronizacji obok zwykłego arkusza pojawi się zakładka „… - Pozycje” z jednym wierszem na każdą pozycję faktury.
-
2
Utwórz zakładkę „Ceny” i tabelę zbiorczą
W komórce A1 nowej zakładki wpisz:
=QUERY('Faktury - Pozycje'!A:V; "select E, F, N, T, I, min(V), max(V), count(V) where N is not null and V is not null group by E, F, N, T, I order by N, min(V)"; 1). Zakładka nazywa się „nazwa arkusza - Pozycje”, więc zawsze ujmij ją w apostrofy (tu przykładowo „Faktury - Pozycje”) i podmień na własną nazwę w każdej formule. Dostaniesz listę par dostawca–towar (ten sam towar od różnych dostawców trafia do sąsiednich wierszy) z ceną najniższą, najwyższą i liczbą wierszy. Pozycje bez ceny jednostkowej netto są pomijane – patrz uwaga poniżej. -
3
Ogranicz dane do zakupów
Zakładka pozycji może zawierać także faktury sprzedażowe i korygujące. Żeby liczyć tylko zakupy i pominąć korekty (ich różnice zaburzają min i max), rozbuduj warunek w zapytaniu:
where G = 1234567890 and N is not null and V is not null and not J contains 'koryg' and not J contains 'Korekta'. Wpisz swój NIP bez cudzysłowów, bo w arkuszu zwykle jest liczbą; jeśli w kolumnie G jest wyrównany do lewej (tekst), ujmij go w apostrofy. Korekty porównuj osobno. -
4
Dodaj kolumnę „Ostatnia cena”
Obok tabeli, w pierwszym wierszu danych, wpisz:
=INDEX(SORT(FILTER({'Faktury - Pozycje'!C2:C\'Faktury - Pozycje'!V2:V}; 'Faktury - Pozycje'!E2:E=A2; 'Faktury - Pozycje'!N2:N=C2; 'Faktury - Pozycje'!T2:T=D2; 'Faktury - Pozycje'!I2:I=E2); 1; FALSE); 1; 2)i przeciągnij w dół. Formuła bierze wszystkie wiersze danego dostawcy i towaru, sortuje je po dacie wystawienia malejąco i zwraca cenę z najnowszej faktury (przy kilku fakturach z tego samego dnia wynik pochodzi z jednej z nich). Formułę przeciągasz w dół, gdy tabela urośnie, albo owijasz wARRAYFORMULA. -
5
Policz zmianę względem najniższej ceny
W kolejnej kolumnie wpisz:
=JEŻELI.BŁĄD(I2/F2-1)i ustaw format procentowy. Wynik 0,08 oznacza, że dziś płacisz o 8% więcej niż najmniej w analizowanym okresie. Próg alertu wpisz w osobnej komórce, np.L1(5%). -
6
Podświetl towary droższe niż najniższa cena z okresu
Zaznacz kolumnę ze zmianą, wybierz Formatuj → Formatowanie warunkowe → Formuła niestandardowa i wpisz
=$J2>$L$1. Wiersze, w których ostatnia cena przekracza najniższą o więcej niż Twój próg, zmienią kolor, a filtr po tej kolumnie da krótką listę towarów do rozmowy z dostawcą.
Cena jednostkowa na fakturze bywa podana przed rabatem. Jeśli dostawca rozlicza obniżki w kolumnie „Rabat/opust”, porównuj wartość netto podzieloną przez ilość (kolumny „Wartość netto” i „Ilość”) zamiast samej ceny jednostkowej – wtedy rabat nie zniekształci wyniku.
Tabela grupuje po jednostce i walucie, więc „kg” i „szt.” oraz EUR i PLN nie zostaną zmieszane. Gdy dostawca podaje ceny brutto, kolumna „Cena jedn. netto” bywa pusta – w takim przypadku oprzyj porównanie na „Cena jedn. brutto” albo na wartości netto podzielonej przez ilość. Różne nazwy tego samego towaru u dostawcy rozdzielą go na dwa wiersze; przy kodach pomaga grupowanie po kolumnie „Indeks” – zamień wtedy N na O w QUERY i w FILTER.
Zobacz, jak KSeF Import pobiera faktury kosztowe i układa je w gotowy arkusz.
Co robi KSeF Import, a co robisz Ty
Ten workflow to połączenie danych z KSeF Import z Twoimi formułami. Poniżej uczciwy podział, kto za co odpowiada:
| KSeF Import | Ty |
|---|---|
| Pobiera faktury z KSeF i zapisuje każdą pozycję w osobnym wierszu zakładki „… - Pozycje” | Tworzysz zakładkę „Ceny” i wpisujesz formuły QUERY, FILTER i SORT |
| Zapisuje nazwę, jednostkę, ilość, cenę jednostkową i wartość pozycji jako liczby | Wybierasz klucz porównania (nazwa albo indeks) i próg alertu |
| Dopisuje nowe pozycje przy kolejnych synchronizacjach | Rozszerzasz formuły w dół, gdy tabela urośnie, i oceniasz, czy różnica cen jest uzasadniona |
| Nie wylicza trendów cen ani nie ocenia dostawców | Decydujesz o renegocjacji, zmianie dostawcy albo korekcie cen sprzedaży |
Zakładka „Ceny” tylko czyta dane z zakładki pozycji. Nie edytuje faktur w KSeF i nie zastępuje rozmowy z dostawcą – pokazuje, o czym warto rozmawiać.
Podsumowanie: kontrola cen w firmie handlowej, gastronomii i produkcji
Ten układ najlepiej sprawdza się tam, gdzie kupujesz wiele podobnych towarów: w sklepie, restauracji, piekarni, warsztacie czy hurtowni. Raz ustawiona zakładka „Ceny” ogranicza potrzebę przeglądania faktur jedna po drugiej – otwierasz arkusz i widzisz, które towary kosztują dziś wyraźnie więcej niż najniższa cena z analizowanego okresu. Podstawą są pozycje faktur z KSeF w osobnych wierszach, które KSeF Import dopisuje przy każdej synchronizacji.
Pomysł możesz rozbudować o ranking dostawców, alert o nowym dostawcy albo kalendarz płatności. Wszystko opiera się na tych samych danych z tego samego skoroszytu, a zakładkę „Ceny” kopiujesz do kolejnych firm, podmieniając nazwę zakładki i NIP.
Najczęstsze pytania
Krótkie, konkretne odpowiedzi na pytania, które zadają użytkownicy i wyszukiwarki.
Zakładka „… - Pozycje” jest dostępna w planach Grupa i Biuro. Okres próbny ma zakres planu Biuro, więc kontrolę cen możesz przetestować przed wyborem planu. Aktualne warunki znajdziesz na stronie Cennik.
Częściowo. QUERY przelicza się sam, gdy KSeF Import dopisze nowe pozycje po synchronizacji, ale kolumny „Ostatnia cena” i „Zmiana” przeciągasz w dół, gdy tabela urośnie (albo owijasz w ARRAYFORMULA). Zakładkę „Ceny” i formuły tworzysz jednorazowo ręcznie.
Tabela z grupowaniem po NIP dostawcy zawiera osobny wiersz dla każdej pary dostawca–towar. Zapytanie QUERY z klauzulą order by N ustawia ten sam towar od różnych dostawców w sąsiednich wierszach, więc ceny porównasz od razu. Działa to, gdy towar ma tę samą nazwę (lub indeks) u obu dostawców.
Dostawca mógł podać cenę brutto albo tylko wartość pozycji. Wtedy porównuj wartość netto podzieloną przez ilość albo cenę jednostkową brutto – oba pola są w zakładce pozycji.
Zakładka „… - Pozycje” to funkcja Arkuszy Google. Formuły QUERY i FILTER opisane w artykule są składnią Arkuszy Google; w Excelu analogiczną tabelę zbudujesz przez tabelę przestawną, bo funkcje i separatory różnią się.
Nie. KSeF Import dostarcza pozycje faktur w arkuszu. Próg alertu i formatowanie warunkowe ustawiasz sam w Arkuszu Google, a o tym, co zrobić z podwyżką, decydujesz Ty.
Doradczyni biur rachunkowych. Wspiera obsługę wielu NIP-ów w KSeF i przygotowanie danych z faktur pod programy księgowe.