Włączasz filtr o 9:05, a zarząd widzi go na swoim ekranie. Prywatne są dwa z trzech narzędzi w Arkuszach

Filtr nałożony na zestawienie w Arkuszach Google nie jest ustawieniem Twojej przeglądarki. Jest zapisany w pliku: pomoc Google ostrzega, że po dodaniu filtra znajdzie go każdy, kto ma dostęp do arkusza, a zmienić go może każda osoba z prawem edycji. Zarząd, który wchodzi w ten sam plik kwadrans po Tobie, dostaje zestawienie przefiltrowane Twoim kryterium i nie ma żadnego sygnału, że patrzy na wycinek.

Drugi mechanizm siedzi w komórce z sumą. Cztery zapisy tej samej sumy — SUM, SUBTOTAL z kodem 9, SUBTOTAL z kodem 109 i AGGREGATE — liczą inny zestaw wierszy, bo inaczej traktują wiersze odcięte filtrem i wiersze ukryte ręcznie. Na czternastu zleceniach różnica między skrajnymi odczytami wychodzi 55 160 zł.

Które z trzech narzędzi filtrowania w Arkuszach zobaczy reszta zespołu?

W Arkuszach Google są trzy osobne mechanizmy i tylko dwa z nich są prywatne. Zwykły filtr z menu Dane zapisuje się w pliku i działa na wszystkich. Widok filtra, zgodnie z tą samą dokumentacją Google, filtruje dane wyłącznie w Twoim widoku arkusza, zapisuje się automatycznie, może być kilka takich widoków naraz dla różnych osób, ale jednocześnie aktywny jest tylko jeden. Osoba z dostępem wyłącznie do odczytu dostaje tymczasowy widok filtra, którego nikt inny nie widzi i który nie zostaje w pliku.

Trzeci mechanizm to fragmentator (slicer). Dokumentacja mówi, że zmiany filtra na fragmentatorze widzisz tylko Ty, dopóki nie ustawisz ich jako domyślnych, a ustawienie domyślne zostaje zastosowane dla każdego, kto ma dostęp do arkusza. To jest moment, w którym prywatne ustawienie przestaje być prywatne, i nie ma po nim śladu w historii zmian liczb — zmieniła się selekcja, nie dane.

Wniosek operacyjny dla pliku, z którego wychodzi raport: zwykły filtr na zakładce źródłowej jest pozycją do usunięcia, nie do ustawienia „porządnie”. Dopóki tam jest, każda osoba z prawem edycji może przestawić to, co zobaczy następny czytelnik.

Dlaczego Excel pyta, czy filtr ma działać tylko dla Ciebie, i czego to pytanie nie obejmuje?

Excel rozwiązuje ten sam problem widokami arkusza. Przy sortowaniu lub filtrowaniu pliku, nad którym pracuje ktoś jeszcze, program pyta, czy zastosować zmianę tylko dla Ciebie, czy dla wszystkich; sortowanie i filtrowanie wewnątrz własnego widoku nie zmienia widoku pozostałych osób, a dokumentacja Microsoftu dodaje, że sens to ma dopiero wtedy, gdy widoków używają wszyscy — kto nie jest w widoku arkusza, dostaje cudze sortowania i filtry na swój ekran.

Ta sama strona wymienia ograniczenia, które przesądzają, czy w ogóle da się na tym oprzeć proces raportowy: widoki arkusza działają tylko na plikach w SharePoint lub OneDrive, a w lokalnej kopii opcje są wygaszone i istniejące widoki stają się niedostępne; obsługuje je Excel dla Microsoft 365 i Excel 2021; na jednym arkuszu można utworzyć do 256 widoków; aktywny widok sygnalizuje ikona oka obok nazwy zakładki. Widoki nie są prywatne w sensie dostępu — inne osoby widzą ich listę w menu.

Istotniejsze jest to, czego to pytanie nie obejmuje. Zgodnie z tą samą dokumentacją ukrycie i odkrycie wierszy oraz kolumn w widoku domyślnym przenosi się na wszystkie widoki arkusza. Ręczne ukrycie wiersza nie jest więc nigdy operacją lokalną: jedna osoba chowa dwa wiersze „bo to zlecenia wewnętrzne”, a liczby zmieniają się każdemu, kto sumuje kolumnę w sposób opisany niżej.

Ile wynosi suma 14 zleceń, gdy filtr odcina 4 wiersze, a 2 są ukryte ręcznie?

Przykład liczony na zamkniętej liście czternastu zleceń z jednego miesiąca. Suma marży wszystkich czternastu to 229 910 zł. Filtr „marża od 10 000 zł” odcina cztery zlecenia o łącznej marży 31 870 zł. Dwa inne wiersze, o łącznej marży 23 290 zł, ktoś kiedyś ukrył ręcznie i nie ma tego w żadnej notatce. Przychód miesiąca, do którego odnosimy marżę, to 1 040 000 zł.

Zachowanie funkcji jest opisane w dokumentacji obu arkuszy. Pomoc Google do funkcji SUBTOTAL stwierdza, że komórki ukryte kryterium autofiltra nigdy nie wchodzą do wyniku, niezależnie od użytego kodu funkcji, a wartości ukryte da się pominąć przez dopisanie 10 przed kod jednocyfrowy — stąd 9 dla sumy i 109 dla sumy pomijającej ukryte. Dokumentacja Excela rozdziela to jeszcze ostrzej: kody od 1 do 11 uwzględniają wartości wierszy ukrytych poleceniem ukrycia wierszy, kody od 101 do 111 je pomijają, a wiersze odrzucone przez filtr są pomijane przy każdej wartości argumentu.

Rozrzut między skrajnymi odczytami to 55 160 zł, czyli 24,0 proc. sumy i 5,3 punktu procentowego marży. Żadna z tych liczb nie jest błędem arytmetycznym: każda poprawnie sumuje inny zbiór wierszy. W raporcie dla zarządu widać tylko jedną z nich i nie widać, która.

W Excelu dochodzi jeszcze pułapka w samym AGGREGATE. Dokumentacja tej funkcji wymienia opcje od 0 do 7 (pominięcie ukrytych wierszy to 1, 3, 5 i 7) i jednocześnie ostrzega, że przy argumencie tablicowym zawierającym obliczenie, na przykład zakres pomnożony przez warunek, funkcja przestaje pomijać ukryte wiersze i zagnieżdżone sumy częściowe. Zapis, który wyglądał na odporny, przestaje nim być po dopisaniu jednego warunku w nawiasie.

Czego dokumentacja nie rozstrzyga i jak to zmierzyć na dwóch kontach?

Pozostaje pytanie, na które żadna z powyższych stron nie odpowiada: czy komórka z SUBTOTAL pokaże inną liczbę osobie, która ma włączony własny widok filtra. Strona o widokach filtra mówi o widoku arkusza, strona o funkcji SUBTOTAL o kryteriach autofiltra, i nigdzie nie napisano, czy prywatny widok filtra jest dla formuły tym samym co filtr wspólny. Odpowiedź zależy od konkretnego pliku i wersji, więc trzeba ją sprawdzić u siebie, zamiast zakładać.

Pomiar zajmuje kilka minut i wymaga dwóch kont albo drugiej przeglądarki z osobnym logowaniem:

• Na zakładce testowej wpisz dwadzieścia liczb i dwie komórki: SUM zakresu oraz SUBTOTAL z kodem 9 na tym samym zakresie.
• Konto A tworzy nazwany widok filtra odcinający połowę wierszy i zostawia go włączony.
• Konto B otwiera ten sam plik bez żadnego filtra i zapisuje obie liczby ze swojego ekranu.
• Konto A zapisuje obie liczby ze swojego ekranu. Jeśli wartości SUBTOTAL się różnią, formuła reaguje na widok prywatny i żadna taka komórka nie może trafić do wysyłanej wersji raportu.
• Konto A wyłącza widok filtra i zakłada zwykły filtr z menu Dane. Konto B odświeża plik i porównuje liczby ponownie.
• Powtórz ostatni krok po ręcznym ukryciu dwóch wierszy, osobno dla kodu 9 i 109.

Wynik tego testu jest wiedzą o konkretnym pliku, nie o arkuszach kalkulacyjnych w ogóle. Zapisz go w tej samej zakładce, w której stoi test, razem z datą i wersją arkusza.

Które liczby w raporcie nie mogą zależeć od tego, co ktoś ma włączone?

Reguła, która to zamyka, jest jednozdaniowa: liczba, która wychodzi z firmy, pochodzi z formuły z jawnym kryterium, a nie ze stanu widoku. Kryterium ma siedzieć w komórce widocznej w raporcie, nie w ustawieniu filtra. Operacyjnie oznacza to trzy rzeczy.

Pierwsza: na zakładce raportowej nie ma SUBTOTAL ani AGGREGATE na zakresie, który ktokolwiek filtruje. Suma marży to SUMIFS albo SUMA.WARUNKÓW z zakresem dat i statusem pobranym z komórek parametrów, albo QUERY z warunkiem zbudowanym z tych samych komórek. Druga: zakładka robocza, na której ktoś przegląda dane, ma własne sumy na SUBTOTAL i nikt z niej nie kopiuje liczb do raportu. Trzecia: lista wykluczeń jest listą, nie ukryciem wiersza — numery zleceń wyłączonych z zestawienia stoją w kolumnie pomocniczej i wchodzą do kryterium formuły.

Przy przepisywaniu formuł sprawdź separator argumentów: w części plików jest nim średnik, w części przecinek, zależnie od ustawień regionalnych arkusza. Formuła skopiowana z dokumentacji w innym języku nie zadziała bez tej zamiany.

Jak zbudować pięć kontroli, które wyłapią to przed wysłaniem?

Blok kontrolny wchodzi na pierwszą zakładkę pliku i zajmuje pięć wierszy. Każda pozycja ma pokazywać zero albo liczbę, którą da się wyjaśnić jednym zdaniem.

Lista kontrolna przed wysłaniem raportu, do odhaczenia w tej kolejności:

• Na zakładce źródłowej nie ma aktywnego filtra z menu Dane; przeglądanie odbywa się przez widoki filtra albo widoki arkusza.
• Żaden wiersz nie jest ukryty ręcznie; wykluczenia są kolumną, nie ukryciem.
• Trzy pierwsze kontrole z tabeli pokazują zero.
• Rejestr kryteriów trafia do stopki raportu, w tej samej wersji, która wychodzi z firmy.
• Jeśli plik stoi w SharePoint lub OneDrive, wszystkie osoby pracujące nad nim mają założony własny widok arkusza, nie tylko jedna.
• Selekcje na fragmentatorach nie są ustawione jako domyślne, chyba że to świadoma decyzja zapisana w rejestrze kryteriów.

Ta lista jest częścią procedury zamknięcia miesiąca, nie jednorazowym porządkiem w pliku. Jeśli przygotowanie raportu przechodzi na kogoś innego, ten sam zestaw punktów trzeba przekazać razem z zadaniem — osobnym tematem jest wtedy lista kontrolna przed przekazaniem zadania, bo plik bez niej wraca po tygodniu.

Co przestawić w pliku przed najbliższym wydaniem raportu?

Jedno przejście po pliku, od zakładki raportowej do źródłowej. Na raportowej wymień każdą sumę opartą o SUBTOTAL albo AGGREGATE na sumę z jawnym kryterium i przenieś parametry okresu oraz statusu do widocznych komórek. Na źródłowej zdejmij zwykły filtr i odkryj wszystkie wiersze, a to, co miało zostać poza zestawieniem, przepisz do kolumny wykluczeń. Na końcu uruchom test z dwóch kont i zapisz jego wynik w pliku. Jeżeli to przejście ma się nie powtarzać co miesiąc od zera, zamknięcie raportu trafia na listę czynności do opisania — kolejność porządkuje zestawienie tego, które procesy opisać najpierw.

Jeżeli po tym przejściu trzecia kontrola z tabeli wciąż pokazuje wartość różną od zera, raport nie jest gotowy do wysłania — niezależnie od tego, jak wygląda. Liczba, która zmienia się wraz z tym, kto co ma włączone, nie jest wynikiem miesiąca, tylko zdjęciem czyjegoś ekranu.