Včera v noci v rozhlasové show Computer America Craiga Crossmana měl Joe z Bostonu otázku:
Mám sloupec čísel faktur. Jak mohu použít Excel k označení duplikátů?
Navrhl jsem použít podmíněné formáty a vzorec COUNTIF. Zde jsou podrobnosti o tom, jak zajistit, aby to fungovalo.
Chceme nastavit podmíněné formátování pro celý rozsah, ale je jednodušší nastavit podmíněný formát pro první buňku v rozsahu a poté tento podmíněný formát zkopírovat. V našem případě má buňka A1 záhlaví čísla faktury, takže vyberu buňku A2 az nabídky vyberte Formát> Podmíněné formátování. Dialog Podmíněné formátování začíná počátečním rozevíracím seznamem „Hodnota buňky je“. Pokud se dotknete šipky vedle tohoto, můžete zvolit „Vzorec je“.
Po výběru možnosti „Vzorec je“ se změní dialogové okno. Místo polí pro „mezi x a y“ je nyní k dispozici jediné pole se vzorcem. Tato formule je neuvěřitelně silná. Můžete zadat jakýkoli vzorec, který můžete snít, pokud bude tento vzorec vyhodnocen jako PRAVDA nebo NEPRAVDA.
V našem případě musíme použít vzorec COUNTIF. Vzorec pro psaní do pole je
=COUNTIF(A:A,A2)>1
V angličtině to říká: „Prohlédněte si celý rozsah sloupce A. Spočítejte, kolik buněk v tomto rozsahu má stejnou hodnotu jako v A2. (Je opravdu důležité, aby„ A2 “ve vzorci ukazovalo na aktuální buňka - buňka, ve které nastavujete podmíněné formátování. Takže - pokud jsou vaše data ve sloupci E a nastavujete první podmíněné formátování v E5, vzorec by byl =COUNTIF(E:E,E5)>0). Potom porovnáme, zda se tento počet počítá je> 1. V ideálním případě, bez duplikátů, bude počet vždy 1 - protože buňka A2 je v rozsahu - ve sloupci A bychom měli najít přesně jednu buňku, která obsahuje stejnou hodnotu jako A2.
Klikněte na tlačítko Format…
Nyní je čas zvolit nepříjemný formát. V horní části tohoto dialogového okna Formát buněk jsou tři karty. Karta Písmo je obvykle první, takže můžete vybrat tučné, červené písmo, ale líbí se mi něco nepříjemnějšího. Obvykle kliknu na kartu Vzory a vyberu buď jasně červenou nebo jasně žlutou. Vyberte barvu a kliknutím na OK zavřete dialogové okno Formátovat buňky.
Vybraný formát se zobrazí v poli „Náhled formátu k použití“. Kliknutím na OK zavřete dialogové okno Podmíněné formátování…
… A nic se neděje. Páni. Pokud nastavujete podmíněné formátování poprvé, bylo by opravdu hezké získat zde zpětnou vazbu, že to fungovalo. Pokud však nemáte to štěstí, že 1098 v buňce A2 je duplikát nějaké jiné buňky, podmínka není pravdivá a vypadá to, že se nic nestalo.
Musíte zkopírovat podmíněné formátování z A2 dolů do dalších buněk ve vašem rozsahu. U parapetu kurzoru A2 proveďte Úpravy> Kopírovat. Stisknutím Ctrl + mezerník vyberte celý sloupec. Proveďte Úpravy> Vložit jinak. V dialogovém okně Vložit jinak klikněte na Formáty. Klikněte na OK.
Tím se zkopíruje podmíněné formátování do všech buněk ve sloupci. Nyní - konečně - uvidíte některé buňky s červeným formátováním, což naznačuje, že máte duplikát.
Je informativní přejít do buňky A3 a po kopii se podívat na podmíněný formát. Vyberte A3, stisknutím tlačítka vyvoláte podmíněné formátování. Vzorec v poli Vzorec je změněn tak, aby spočítal, kolikrát se A3 zobrazí ve sloupci A: A.
Poznámky
Na Joeovu otázku měl v rozsahu pouze 1700 faktur. Nastavil jsem 65536 buněk s podmíněným formátováním a každá buňka porovnává aktuální buňku s 65536 dalšími buňkami. V aplikaci Excel 2005 - s více řádky - bude problém ještě horší. Technicky vzorec v prvním kroku mohl být:=COUNTIF($A$2:$A$1751,A2)>1
Při kopírování podmíněného formátu do celého sloupce jste místo toho mohli vybrat pouze řádky s daty, než provedete Vložit speciální formáty.
Více
Dalším problémem, který jsem popsal po otázce, je, že opravdu nemůžete řadit sloupec na základě podmíněného formátu. Pokud potřebujete seřadit tato data tak, aby byly duplikáty v jedné oblasti, postupujte podle těchto kroků. Nejprve přidejte do B1 nadpis s názvem „Duplikovat?“. Sem tento vzorec v B2: =COUNTIF(A:A,A2)>1.
S ukazatelem buňky v B2 klikněte na úchyt automatického vyplňování (malý čtverec v pravém dolním rohu buňky) a zkopírujte vzorec úplně dolů z rozsahu.
Nyní můžete řadit podle sloupce B sestupně a A vzestupně, abyste měli problémové faktury v horní části rozsahu.
Toto řešení předpokládá, že chcete zvýraznit OBOU duplikovanou fakturu, abyste mohli ručně zjistit, kterou z nich odstranit nebo opravit. Pokud si nechcete označit první výskyt duplicitní, můžete nastavit vzorec, který bude: =COUNTIF($A$2:$A2,A2)>1. Je důležité zadat znaky dolaru přesně podle obrázku. Tím se podíváme pouze na všechny buňky z aktuální buňky a vyhledáme duplicitní položky.
Díky Joeovi z Bostonu za otázku!
