Včera večer v rozhlasovej show Computer America Craiga Crossmana mal Joe z Bostonu otázku:
Mám stĺpec s číslami faktúr. Ako môžem použiť program Excel na označenie duplikátov?
Navrhol som použiť podmienené formáty a vzorec COUNTIF. Tu sú podrobnosti, ako to dosiahnuť.
Chceme nastaviť podmienené formátovanie pre celý rozsah, ale je jednoduchšie nastaviť podmienený formát pre prvú bunku v rozsahu a potom tento podmienený formát skopírovať. V našom prípade má bunka A1 nadpis s číslom faktúry, takže vyberiem bunku A2 a z ponuky vyberiem Formát> Podmienené formátovanie. Dialógové okno Podmienené formátovanie sa začína úvodnou rozbaľovacou ponukou „Hodnota bunky je“. Ak sa dotknete šípky vedľa tejto položky, môžete zvoliť „Vzorec je“.
Po výbere možnosti „Formula Is“ sa zmení dialógové okno. Namiesto políčok pre výraz „Medzi x a y“ je teraz k dispozícii jediné políčko so vzorcom. Táto formula box je neuveriteľne silný. Môžete zadať ľubovoľný vzorec, ktorý si môžete vysnívať, pokiaľ bude tento vzorec vyhodnotený ako PRAVDA alebo NEPRAVDA.
V našom prípade musíme použiť vzorec COUNTIF. Vzorec na zadanie do poľa je
=COUNTIF(A:A,A2)>1
V angličtine to hovorí: „Prezrite si celý rozsah stĺpca A. Spočítajte, koľko buniek v tomto rozsahu má rovnakú hodnotu ako v A2. (Je skutočne dôležité, aby písmeno„ A2 “vo vzorci smerovalo k aktuálna bunka - bunka, v ktorej nastavujete podmienené formátovanie. Takže - ak sú vaše údaje v stĺpci E a nastavujete prvé podmienené formátovanie v E5, vzorec by bol =COUNTIF(E:E,E5)>0). Potom porovnáme, či sa tento počet počíta je> 1. V ideálnom prípade, bez duplikátov, bude počet vždy 1 - pretože bunka A2 je v rozsahu - v stĺpci A by sme mali nájsť presne jednu bunku, ktorá obsahuje rovnakú hodnotu ako A2.
Kliknite na tlačidlo Formátovať …
Teraz je čas zvoliť nepríjemný formát. V hornej časti tohto dialógového okna Formátovanie buniek sú tri karty. Karta Písmo je zvyčajne prvá, takže môžete zvoliť tučné červené písmo, ale páči sa mi niečo nepríjemnejšie. Spravidla kliknem na kartu Vzory a vyberiem buď jasne červenú alebo jasne žltú farbu. Vyberte farbu a potom kliknutím na tlačidlo OK zatvorte dialógové okno Formátovať bunky.
Vybraný formát sa zobrazí v poli „Náhľad formátu na použitie“. Kliknutím na tlačidlo OK zatvorte dialógové okno Podmienené formátovanie …
… a nič sa nedeje. Wow. Ak nastavujete podmienené formátovanie prvýkrát, bolo by naozaj pekné získať tu spätnú väzbu, že to fungovalo. Pokiaľ však nemáte to šťastie, že 1098 v bunke A2 je duplikátom nejakej inej bunky, podmienka nie je pravdivá a vyzerá to, že sa nič nestalo.
Podmienené formátovanie musíte skopírovať z formátu A2 dole do ďalších buniek vo vašom rozsahu. Pri parapete kurzora A2 urobte Upraviť> Kopírovať. Stlačením kombinácie klávesov Ctrl + medzerník vyberte celý stĺpec. Vykonajte úpravy> prilepiť špeciálne. V dialógovom okne Prilepiť špeciálne kliknite na Formáty. Kliknite na tlačidlo OK.
Týmto sa skopíruje podmienené formátovanie do všetkých buniek v stĺpci. Teraz - konečne - vidíte niektoré bunky s červeným formátovaním, čo naznačuje, že máte duplikát.
Je informatívne prejsť do bunky A3 a pozrieť sa po kópii na podmienený formát. Vyberte A3, stlačením OD vyvoláte podmienené formátovanie. Vzorec v poli Vzorec sa zmenil tak, aby počítal, koľkokrát sa A3 objaví v stĺpci A: A.
Poznámky
Na Joeovu otázku mal v rozsahu iba 1700 faktúr. Nastavil som 65536 buniek s podmieneným formátovaním a každá bunka porovnáva aktuálnu bunku s 65536 ďalšími bunkami. V programe Excel 2005 - s viac riadkami - bude problém ešte horší. Technicky vzorec v prvom kroku mohol byť:=COUNTIF($A$2:$A$1751,A2)>1
Pri kopírovaní podmieneného formátu do celého stĺpca ste tiež mohli namiesto vloženia špeciálnych formátov namiesto toho vybrať iba riadky s údajmi.
Viac
Ďalším problémom, ktorý som popísal po otázke, je, že stĺpec naozaj nemôžete triediť na základe podmieneného formátu. Ak potrebujete tieto údaje zoradiť tak, aby boli duplikáty v jednej oblasti, postupujte podľa týchto pokynov. Najskôr do položky B1 pridajte nadpis s názvom „Duplikovať?“. Sem tento vzorec v B2: =COUNTIF(A:A,A2)>1.
S ukazovateľom bunky v B2 kliknite na rukoväť automatického dopĺňania (malý štvorček v pravom dolnom rohu bunky) a skopírujte vzorec úplne dole v rozsahu.
Teraz môžete triediť podľa stĺpca B zostupne a A vzostupne, aby sa problémové faktúry zobrazovali navrchu rozsahu.
Toto riešenie predpokladá, že chcete zvýrazniť OBOJTO duplicitnú faktúru, aby ste mohli manuálne zistiť, ktorú z nich odstrániť alebo opraviť. Ak si nechcete označiť prvý výskyt duplicitné, môžete nastaviť vzorec, ktorý bude: =COUNTIF($A$2:$A2,A2)>1. Je dôležité zadávať znaky dolára presne tak, ako je to znázornené. Takto sa zobrazia iba všetky bunky od aktuálnej bunky nahor a vyhľadajú sa duplicitné položky.
Ďakujem Joeovi z Bostonu za otázku!
