Ako odčítať jeden zoznam od druhého (Excel, Google Tabuľky a online)

· čítanie na 5 minút

Ako odčítať jeden zoznam od druhého (Excel, Google Tabuľky a online)

Patrí to medzi najčastejšie úlohy v tabuľkách: máte zoznam všetkých zákazníkov a zoznam ľudí, ktorí sa odhlásili z odberu, katalóg produktov a zoznam vyradených položiek alebo účastníkov z tohto a minulého mesiaca. Potrebujete zoznam A bez všetkého, čo je v zozname B – alebo položky, ktoré majú oba zoznamy spoločné.

V reči množín ide o štyri operácie:

  • A mínus B – položky iba v zozname A (teda „odčítanie jedného zoznamu od druhého“).
  • B mínus A – položky iba v zozname B.
  • Prienik – položky v oboch zoznamoch.
  • Zjednotenie – všetky položky z oboch zoznamov, každá iba raz.

Najrýchlejší spôsob: porovnanie dvoch zoznamov online

  1. Otvorte Porovnanie dvoch zoznamov.
  2. Hlavný zoznam vložte do poľa Zoznam A – jednu položku na riadok, napríklad stĺpec skopírovaný z Excelu.
  3. Zoznam, ktorý chcete odčítať alebo porovnať, vložte do poľa Zoznam B.
  4. Vyberte výsledok: Iba v A (A − B), Iba v B (B − A), V oboch alebo Všetko (A ∪ B). Pri každej karte vidíte, koľko položiek obsahuje.
  5. Výsledok skopírujte alebo stiahnite.

Tri voľby vyriešia bežné nezhody: Ignorovať veľké a malé písmená (takže Tom@example.com sa zhoduje s tom@example.com), Orezať medzery (predvolene zapnuté, ignoruje zatúlané medzery skopírované z buniek) a Ignorovať prázdne riadky. Oba zoznamy sa porovnávajú vo vašom prehliadači – nič sa nenahráva, čo je dôležité, keď pracujete s e-mailovými adresami zákazníkov.

Ako odčítať jeden zoznam od druhého v Exceli

Zoznam A dajte do stĺpca A a zoznam B do stĺpca B, oba od riadku 2.

Ľubovoľná verzia Excelu – pomocný stĺpec:

  1. Do bunky C2 zadajte =COUNTIF($B$2:$B$1000; A2)=0 a potiahnite vzorec nadol.
  2. Zapnite Údaje → Filter a stĺpec C vyfiltrujte na TRUE (PRAVDA).
  3. Viditeľné hodnoty v stĺpci A sú A mínus B. Skopírujte ich do nového hárka.

Excel 365 – jeden vzorec:

=FILTER(A2:A1000; COUNTIF(B2:B1000; A2:A1000)=0)

Ak rozsahy vymeníte, dostanete B mínus A. Pre prienik vynechajte =0:

=FILTER(A2:A1000; COUNTIF(B2:B1000; A2:A1000))

A pre zjednotenie bez duplicít:

=UNIQUE(VSTACK(A2:A1000; B2:B1000))

V slovenskom Exceli sa argumenty oddeľujú bodkočiarkou; v anglickom čiarkou, napríklad =FILTER(A2:A1000, COUNTIF(B2:B1000, A2:A1000)=0).

Majte na pamäti, že COUNTIF ignoruje veľkosť písmen a znaky * a ? v hodnotách považuje za zástupné znaky – položka ako item* sa tak môže zhodovať s viac hodnotami, než čakáte. Ak vaše údaje takéto znaky obsahujú, použite v pomocnom stĺpci radšej =ISNA(XMATCH(A2; $B$2:$B$1000)) – XMATCH predvolene hľadá presnú zhodu bez zástupných znakov.

Ako porovnať dva zoznamy v Google Tabuľkách

A mínus B:

=FILTER(A2:A; A2:A<>""; COUNTIF(B2:B; A2:A)=0)

Položky v oboch zoznamoch:

=FILTER(A2:A; A2:A<>""; COUNTIF(B2:B; A2:A)>0)

Zjednotenie bez duplicít:

=UNIQUE(FILTER({A2:A; B2:B}; {A2:A; B2:B}<>""))

Ak chcete rozdiely namiesto vyťahovania len zvýrazniť, označte A2:A, otvorte Formát → Podmienené formátovanie, zvoľte Vlastný vzorec je a zadajte =COUNTIF($B$2:$B; A2)=0.

So slovenským nastavením tabuľky sa argumenty oddeľujú bodkočiarkou; ak máte tabuľku v anglickom nastavení, použite čiarku (bodkočiarka v zložených zátvorkách {A2:A; B2:B} zostáva, pretože skladá stĺpce pod seba).

Ako odčítať zoznamy v príkazovom riadku

Pri comm (Linux a macOS) musia byť oba vstupy zoradené:

comm -23 <(sort -u a.txt) <(sort -u b.txt)   # iba v A
comm -13 <(sort -u a.txt) <(sort -u b.txt)   # iba v B
comm -12 <(sort -u a.txt) <(sort -u b.txt)   # v oboch

Ak chcete zachovať pôvodné poradie zoznamu A, použite grep:

grep -vxFf b.txt a.txt

-F berie riadky ako obyčajný text, -x vyžaduje zhodu celého riadku, -f b.txt načíta vzory zo zoznamu B a -v zhodu obráti. Pridajte -i, ak chcete ignorovať veľkosť písmen. -x nevynechávajte: bez neho by sa prázdny riadok v b.txt zhodoval s každým riadkom a.txt – a všetky by ich odstránil.

V PowerShelli:

$b = Get-Content b.txt
Get-Content a.txt | Where-Object { $b -notcontains $_ }

-notcontains nerozlišuje veľké a malé písmená; pre porovnanie s rozlišovaním použite -cnotcontains.

Najčastejšie chyby

  • Medzery na konci. anna@example.com a anna@example.com␣ vyzerajú rovnako, no nezhodujú sa. Oba zoznamy orežte.
  • Veľké a malé písmená. Pri e-mailových adresách na veľkosti písmen v praxi nezáleží, pri kódoch produktov môže. Rozhodnite sa vedome.
  • Konce riadkov z Windows. Súbor uložený vo Windows ukončuje riadky znakmi \r\n. Nástroje v príkazovom riadku potom vidia item\r a nič sa nezhoduje. Preveďte ho pomocou dos2unix alebo tr -d '\r'.
  • Diakritika. „Jan“ a „Ján“ sú rôzne reťazce. Ak sa majú zhodovať, najprv odstráňte diakritiku odstraňovačom diakritiky.
  • Duplicity v rámci zoznamu. Rozhodnite sa, či ich chcete vo výsledku. Online nástroj vypíše každú položku iba raz; jeden zoznam vyčistí odstraňovač duplicít.

Časté otázky

Aký je rozdiel medzi „A mínus B“ a „B mínus A“?

A mínus B je všetko z prvého zoznamu, čo v druhom chýba. B mínus A je opak. Ak chcete všetky rozdiely naraz, vezmite oba výsledky – spolu tvoria symetrický rozdiel.

Dajú sa porovnať zoznamy s tisíckami položiek?

Áno. Online porovnanie zoznamov zvládne stovky tisíc riadkov za zlomok sekundy, pretože používa hašovacie množiny namiesto porovnávania každej dvojice.

Ako porovnám zoznamy, ktoré nemajú jednu položku na riadok?

Najprv ich rozdeľte. Ak sú položky oddelené čiarkami, preveďte ich na riadky prevodom oddeľovačov, alebo nechcené riadky odstráňte nástrojom Odstránenie riadkov obsahujúcich text.

Ukladajú sa moje údaje?

Nie. Porovnanie prebieha lokálne vo vašom prehliadači. Zoznamy si pamätá iba úložisko vášho vlastného prehliadača, aby ste o ne neprišli po obnovení stránky; vymazaním polí ich odstránite.