Google Sheets: Jak zwrócić wartość warunku powodującego błąd

krzychu.m napisał:

Mam taką formułę
=JEŻELI(LUB(LICZ.WARUNKI(A:A;"a";B:B;"1")<>LICZ.WARUNKI(D:D;"a";E:E;"1");LICZ.WARUNKI(A:A;"a";B:B;"2")<>LICZ.WARUNKI(D...

Szybka odpowiedź
Zastąp frazę „NOK” konstrukcją, która zwraca „1” lub „2” zależnie od tego, który z dwóch liczników jest różny, a „OK” gdy oba się zgadzają:

Najprostsza wersja (pokazuje pierwszy napotkany błąd):

Wersja pełna – jeśli niespójne są oba warianty wyświetli „1, 2”:

Jeżeli Twoje Arkusze nie obsługują jeszcze funkcji LET, skorzystaj z wariantu „IFS + TEXTJOIN” podanego niżej.

---

Szczegółowa analiza problemu

1. Logika źródłowej formuły
1. Zliczamy w kolumnach A:B oraz D:E rekordy, w których:
• kolumna A lub D zawiera stałą wartość „a”,
• kolumna B lub E – wartość „1” bądź „2”.
2. Gdy choć jeden z wyników par („a”+„1” lub „a”+„2”) jest różny pomiędzy blokami A:B i D:E, funkcja `LUB` zwraca PRAWDA i `JEŻELI` wypisuje „NOK”.

2. Zamiana „NOK” na kod niezgodności
Chcemy, aby w razie rozbieżności formuła wskazała który z drugich warunków („1” lub „2”) jest przyczyną. Rozwiązania:

2.1. Zagnieżdżone JEŻELI (wersja podstawowa)
Zalety:
• działa w każdej wersji Arkuszy Google,
• nie wymaga kolumn pomocniczych.


Wadą jest to, że gdy obie pary są niespójne, zwrócone zostanie tylko „1”.

2.2. LET + FILTER + TEKSTJOIN (wersja rozszerzona)
Dostępna w Arkuszach Google od wiosny 2024 (jeżeli w Twojej domenie funkcja `LET` jeszcze się nie pojawiła – przejdź do pkt 2.3). Atuty:
• każdą część liczysz raz, więc arkusz jest szybszy,
• dostajesz listę wszystkich niezgodności („1”, „2”, „1, 2”, …).


• `FILTER` tworzy pionową tablicę tylko z tych etykiet („1”, „2”), dla których `diffN` = PRAWDA.
• `ILE` (COUNTA) sprawdza, czy tablica jest pusta.
• Gdy pusta → „OK”, w przeciwnym razie sklej listę przecinkiem.

2.3. Alternatywa bez LET (IFS)
Jeżeli `LET` nie działa, tę samą logikę można zbudować tak:


`TEKSTJOIN` ignoruje puste ciągi, więc sklei dokładnie te etykiety, które są błędne.

3. Wydajność i dobre praktyki
1. `LICZ.WARUNKI` skanowane po całych kolumnach przy dużych arkuszach → wolno.
Rozważ zawężenie zakresu (np. `A2:A1000`) lub użyj dynamicznego zakresu `A2:INDEX(A:A;ILE.NIEPUSTYCH(A:A))`.
2. Jeżeli liczników jest więcej niż dwa, warto przejść na tabelę pomocniczą i dodać prostą kolumnę „różnica”.
3. Przy większej liczbie warunków (np. 10 kodów) budujemy jedną tablicę:


4. Testowanie
1. Wprowadź wiersz testowy, w którym kolumny A = D = „a”, B i E zawierają różne kody – sprawdź, czy formuła wskazuje właściwy.
2. Skrajny przypadek: zmień oba kody – powinny pojawić się oba numery bądź pierwszy, zależnie od wybranej wersji.
3. Wróć wszystkie wartości do zgodnych – wynik powinien być „OK”.

5. Co dalej?
• Jeśli w przyszłości dojdą kolejne kody („3”, „4”, …), budowanie tablicowego rozwiązania z LET-em lub z kolumną pomocniczą ułatwi utrzymanie arkusza.
• Gdy arkusz ma służyć raportowaniu, można użyć warunkowego formatowania: koloruj komórkę na czerwono, gdy wynik <> „OK”.
• Dla dużych zbiorów danych rozważ funkcję QUERY lub przeniesienie agregacji do BigQuery – unikniesz wielokrotnego skanowania kolumn przez `LICZ.WARUNKI`.

---

Podsumowanie
• Najmniejsza zmiana (formuła z dwoma `JEŻELI`) już spełnia wymaganie.
• Wersja z `LET` jest czytelniejsza i skalowalna, a przy okazji odciąża arkusz.
• Pamiętaj o ograniczeniu zakresów i o testach granicznych, żeby formuła pozostała szybka i pewna w działaniu.