Cyfra kontrolna EAN w Excelu i Arkuszach Google — gotowa formuła

Jak sprawdzić całą kolumnę kodów EAN w Excelu?

Kody wpisz w kolumnę A, od wiersza 2. Formułę wklej do B2 i przeciągnij w dół za kwadracik w rogu komórki albo kliknij go dwa razy. Żeby zaznaczyć całą formułę, kliknij w nią trzy razy.

=JEŻELI.BŁĄD(JEŻELI(A2="";"";JEŻELI(CZY.LICZBA(SZUKAJ.TEKST("E+";A2));"zapis naukowy";JEŻELI(CZY.BŁĄD(SUMA.ILOCZYNÓW(FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1)*1));"nie same cyfry";JEŻELI(LUB(DŁ(A2)=8;DŁ(A2)=12;DŁ(A2)=13;DŁ(A2)=14);JEŻELI(MOD(SUMA.ILOCZYNÓW(FRAGMENT.TEKSTU(PRAWY(POWT("0";14)&A2;14);WIERSZ(ADR.POŚR("1:14"));1)*(1+2*MOD(WIERSZ(ADR.POŚR("1:14"));2)));10)=0;"OK";"zła cyfra kontrolna");"zła długość: "&DŁ(A2)))));"nie same cyfry")

Wynik	Co znaczy
OK	8, 12, 13 albo 14 cyfr i cyfra kontrolna się zgadza, np. 5901234123457
zła cyfra kontrolna	ostatnia cyfra nie wynika z pozostałych — zwykle jedna cyfra jest przepisana źle, np. 5901234123450
zła długość: 11	inna liczba cyfr; 11 to najczęściej kod UPC-A, który zgubił zero z przodu, np. 12345678905
nie same cyfry	spacja, myślnik, litera, twarda spacja, wpis w rodzaju „brak” albo błąd innej formuły, np. 590-1234-12345
zapis naukowy	kod zapisany jako tekst w rodzaju 5,90123E+12 — ostatnich cyfr już w nim nie ma

OK znaczy, że kod jest dobrze zbudowany. Czy taki numer w ogóle istnieje, to osobna sprawa — o tym niżej.

Ta sama formuła działa w Arkuszach Google z polskimi ustawieniami. Arkusze przyjmują polskie nazwy funkcji i same też je tak pokazują.

Jak dopisać cyfrę kontrolną w Excelu?

Ta formuła jest dla kodów bez ostatniej cyfry: 7 cyfr dla EAN-8, 11 dla UPC-A, 12 dla EAN-13 i 13 dla GTIN-14. Wynik jest tekstem, więc Excel nie zamieni go na 5,90123E+12 ani nie zgubi zera z przodu.

=JEŻELI.BŁĄD(JEŻELI(A2="";"";JEŻELI(CZY.BŁĄD(SUMA.ILOCZYNÓW(FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1)*1));"nie same cyfry";JEŻELI(LUB(DŁ(A2)=7;DŁ(A2)=11;DŁ(A2)=12;DŁ(A2)=13);A2&MOD(-SUMA.ILOCZYNÓW(FRAGMENT.TEKSTU(PRAWY(POWT("0";13)&A2;13);WIERSZ(ADR.POŚR("1:13"));1)*(1+2*MOD(WIERSZ(ADR.POŚR("1:13"));2)));10);"zła długość: "&DŁ(A2))));"nie same cyfry")

W kolumnie A	Wynik
590123412345	5901234123457
9638507	96385074
01234567890	012345678905
1590123412345	15901234123454

Używaj jej tylko do kodów, które jeszcze nie mają cyfry kontrolnej. Pełny EAN-13 też ma 13 cyfr, więc formuła dopisze mu czternastą i wyjdzie numer, który nie jest kodem tego towaru.

Excel po angielsku: te same formuły z angielskimi nazwami

Excel w wersji angielskiej nie zna polskich nazw funkcji. Gdy ma polskie ustawienia regionalne (przecinek jako separator dziesiętny), wklej te wersje — angielskie nazwy, średniki:

=IFERROR(IF(A2="";"";IF(ISNUMBER(SEARCH("E+";A2));"zapis naukowy";IF(ISERROR(SUMPRODUCT(MID(A2;ROW(INDIRECT("1:"&LEN(A2)));1)*1));"nie same cyfry";IF(OR(LEN(A2)=8;LEN(A2)=12;LEN(A2)=13;LEN(A2)=14);IF(MOD(SUMPRODUCT(MID(RIGHT(REPT("0";14)&A2;14);ROW(INDIRECT("1:14"));1)*(1+2*MOD(ROW(INDIRECT("1:14"));2)));10)=0;"OK";"zła cyfra kontrolna");"zła długość: "&LEN(A2)))));"nie same cyfry")

=IFERROR(IF(A2="";"";IF(ISERROR(SUMPRODUCT(MID(A2;ROW(INDIRECT("1:"&LEN(A2)));1)*1));"nie same cyfry";IF(OR(LEN(A2)=7;LEN(A2)=11;LEN(A2)=12;LEN(A2)=13);A2&MOD(-SUMPRODUCT(MID(RIGHT(REPT("0";13)&A2;13);ROW(INDIRECT("1:13"));1)*(1+2*MOD(ROW(INDIRECT("1:13"));2)));10);"zła długość: "&LEN(A2))));"nie same cyfry")

Arkusze Google z polskimi ustawieniami przyjmą także te wersje i pokażą je z polskimi nazwami. Excel z angielskimi ustawieniami regionalnymi (kropka dziesiętna) chce przecinków zamiast średników — zamień wszystkie, w napisach w cudzysłowie przecinków nie ma. Wersja z przecinkami jest też na stronie angielskiej.

Jak działa formuła?

Obie zaczynają od dopełnienia kodu zerami do 14 cyfr: PRAWY(POWT("0";14)&A2;14). Zera z przodu nie zmieniają cyfry kontrolnej, więc jedna formuła obsługuje EAN-8, UPC-A, EAN-13 i GTIN-14. FRAGMENT.TEKSTU z WIERSZ(ADR.POŚR("1:14")) bierze cyfry po kolei, a 1+2*MOD(WIERSZ(…);2) daje im wagi: 3 na pozycjach nieparzystych, 1 na parzystych. W kodzie 14-cyfrowym to zwykły przepis — od prawej na zmianę 3 i 1, zaczynając od cyfry tuż przed kontrolną. SUMA.ILOCZYNÓW mnoży i dodaje. Cyfra kontrolna też jest w sumie, z wagą 1, więc w dobrym kodzie suma dzieli się przez 10 — to sprawdza MOD(…;10)=0.

Druga formuła robi to samo na 13 pozycjach i dopisuje MOD(-suma;10): tyle, ile brakuje sumie do pełnej dziesiątki, a gdy suma już nią jest — 0. Sam przepis z rozpisanym przykładem jest na stronie sprawdzarki.

Formuły są długie z dwóch powodów. Nie ma w nich stałej tablicowej w rodzaju {3,1,3,1}, jaką mają formuły z angielskich stron: polski Excel chce w niej ukośników, {3\1\3\1}, a wersji z przecinkami nie przyjmie. Nie ma też funkcji LET ani SEKWENCJA, bo Excel 2019 i starsze ich nie mają. Najmłodsza funkcja w tych formułach to JEŻELI.BŁĄD z Excela 2007, a SUMA.ILOCZYNÓW nie wymaga Ctrl+Shift+Enter.

Zanim wkleisz kody do arkusza

Excel i Arkusze Google biorą kod za liczbę. Zera z przodu znikają, a liczbę z 13 cyframi Excel pokazuje jako 5,90123E+12. Dopóki w komórce jest liczba, nic nie przepadło: formuła czyta pełną wartość i pisze OK. Cyfry giną, gdy Excel zapisze arkusz do CSV, bo do pliku trafia wtedy tekst 5,90123E+12. Taki kod da się odzyskać tylko ze źródła — ze sklepu, z pliku od hurtowni, z opakowania.

Żeby kody zostały takie, jakie są, ustaw kolumnę jako tekst, zanim je wkleisz: w Excelu Narzędzia główne → Format liczb → Tekst, w Arkuszach Google Formatuj → Liczba → Zwykły tekst.

Czego formuły nie sprawdzą?

• Czy numer istnieje i należy do tego towaru. Wie to tylko rejestr GS1 — jak sprawdzić w nim kod, opisaliśmy na stronie o komunikacie Allegro „Podany EAN (GTIN) jest niepoprawny”.
• Zakresów, których nie przyjmą Google Merchant Center ani Allegro: kodów wewnętrznych zaczynających się od 2, 02 albo 04 i kuponów od 05, 98 albo 99 (w zapisie 13-cyfrowym). Formuła napisze przy nich OK.
• Duplikatów, także tego samego kodu zapisanego na dwa sposoby, np. 012345678905 i 0012345678905.
• Kodu z samych zer: 0000000000000 ma poprawną cyfrę kontrolną.
• ISBN-10, który ma własną cyfrę kontrolną i może kończyć się literą X. Formuła pokaże przy nim złą długość albo „nie same cyfry”. ISBN-13 to EAN-13 i sprawdza się jak EAN-13.

Jak sprawdzić cały plik naraz?

Sprawdzarka kodów EAN robi wszystko z tej listy poza rejestrem GS1, i to bez formuł: wklejasz kolumnę albo wybierasz plik z eksportu sklepu, CSV lub XLSX. Znajduje duplikaty także wtedy, gdy są zapisane inaczej, nazywa niewidoczny znak w kodzie i przy każdym błędzie podaje numer wiersza. Plik nie opuszcza przeglądarki.

W Arkuszach Google to samo zrobi darmowy dodatek GTIN & EAN Check, bez wychodzenia z arkusza: zaznaczasz kolumnę i wybierasz Rozszerzenia → GTIN & EAN Check → Sprawdź zaznaczone kody. Kody do poprawy trafiają do raportu w nowym arkuszu, z odnośnikiem do każdej komórki.