Tegnap este Craig Crossman Computer America rádióműsorában a bostoni Joe-nak feltett egy kérdést:
Van egy oszlopom a számlaszámokról. Hogyan használhatom az Excel-t a másolatok megjelölésére?
Javasoltam a feltételes formátumok és a COUNTIF képlet használatát. Itt vannak a részletek arról, hogyan lehet ezt a munkát megvalósítani.
Feltételes formázást szeretnénk beállítani az egész tartományhoz, de könnyebb beállítani egy feltételes formátumot a tartomány első cellájához, majd átmásolni azt a feltételes formátumot. Esetünkben az A1 cellának van egy címe a számlaszámhoz, ezért kiválasztom az A2 cellát, és a menüből válassza a Formátum> Feltételes formázás lehetőséget. A Feltételes formázás párbeszédpanel a kezdeti legördülő menüvel kezdődik, és azt mondja, hogy "Cella értéke". Ha megérinti a mellette lévő nyilat, kiválaszthatja a "Formula Is" lehetőséget.
A "Formula Is" kiválasztása után a párbeszédpanel megváltoztatja a megjelenését. Az "x és y közötti" mező helyett most egyetlen képletmező található. Ez a képletdoboz hihetetlenül hatékony. Bármelyik képletet beírhat, amelyet megálmodhat, amennyiben ez a képlet IGAZ vagy HAMIS.
Esetünkben COUNTIF képletet kell használnunk. A mezőbe beírandó képlet a
=COUNTIF(A:A,A2)>1
Angolul ez azt mondja: "nézze át az A oszlop teljes tartományát. Számolja meg, hogy ebben a tartományban hány cella van-e ugyanazzal az értékkel, mint ami az A2-ben van. (Nagyon fontos, hogy a képletben az" A2 "a aktuális cella - az a cella, amelyben beállítja a feltételes formázást. Tehát - ha az adatai az E oszlopban vannak, és az első feltételes formázást az E5-ben állítja be, akkor a képlet a következő lenne =COUNTIF(E:E,E5)>0). Ezután összehasonlítjuk, hogy lássuk, ez a szám értéke> 1. Ideális esetben duplikátumok nélkül a számlálás mindig 1 lesz - mivel az A2 cella a tartományban van - pontosan egy cellát kell találnunk az A oszlopban, amely ugyanazt az értéket tartalmazza, mint az A2.
Kattintson a Formátum… gombra
Itt az ideje, hogy kiválasszon egy kellemetlen formátumot. A cellák formázása párbeszédpanel tetején három fül található. A Betűtípus fül általában az első, így kiválaszthat egy félkövér, piros betűtípust, de nekem valami kellemetlenebb tetszik. Általában a Minták fülre kattintok, és vagy élénkvöröset vagy élénksárgát választok. Válassza ki a színt, majd kattintson az OK gombra a Cella formázása párbeszédpanel bezárásához.
A kiválasztott formátumot a "Használandó formátum előnézete" mezőben látja. Kattintson az OK gombra a Feltételes formázás párbeszédpanel bezárásához …
… És semmi sem történik. Azta. Ha először állítja be a feltételes formázást, akkor nagyon jó lenne itt visszajelzést kapni arról, hogy ez működött. De hacsak nincs szerencséje, hogy az A2 cellában szereplő 1098 más másolat másolata, a feltétel nem igaz, és úgy tűnik, hogy semmi sem történt.
Le kell másolnia a feltételes formázást A2-ről a tartomány többi cellájára. Az A2 kurzorpárkányon végezze el a Szerkesztés> Másolás parancsot. A teljes oszlop kijelöléséhez nyomja meg a Ctrl + szóköz billentyűkombinációt. Tegye a Szerkesztés> Különleges beillesztés lehetőséget. A Speciális beillesztés párbeszédpanelen kattintson a Formátumok elemre. Kattintson az OK gombra.
Ez átmásolja a feltételes formázást az oszlop összes cellájába. Most - végül - lát néhány piros formázású cellát, jelezve, hogy van duplikátuma.
Informatív, ha az A3 cellába megy, és a másolat után megnézi a feltételes formátumot. Válassza az A3 elemet, nyomja meg az od gombot a feltételes formázás megjelenítéséhez. A Formula Is mezőben lévő képlet megváltozott, hogy megszámolja, hányszor jelenik meg A3 az A oszlopban: A.
Megjegyzések
Joe kérdésében csak 1700 számla volt a tartományban. 65536 cellát állítottam fel feltételes formázással, és mindegyik cella összehasonlítja az aktuális cellát 65536 más cellával. Az Excel 2005-ben - több sorral - a probléma még rosszabb lesz. Technikailag az első lépés képlete a következő lehetett:=COUNTIF($A$2:$A$1751,A2)>1
Továbbá, amikor a feltételes formátumot átmásolja az egész oszlopba, ehelyett kiválaszthatta volna csak az adatokat tartalmazó sorokat a Speciális formátumok beillesztése előtt.
Több
A másik kérdés, amelyet a kérdés után írtam le, az az, hogy valóban nem rendezhet oszlopot feltételes formátum alapján. Ha ezeket az adatokat úgy kell rendezni, hogy a másolatok egy területen legyenek, kövesse ezeket a lépéseket. Először adjon hozzá egy címet a B1-hez "Másolat?" Néven. Írja ezt a képletet a B2: =COUNTIF(A:A,A2)>1.
Ha a cellamutató a B2-ben van, kattintson az automatikus kitöltési fogantyúra (a cella jobb alsó sarkában található kis négyzetre), hogy a képletet a tartomány teljes tartományába másolja.
Most rendezheti a B oszlop csökkenő és az A növekvő oszlopait, hogy a problémás számlák a tartomány tetején legyenek.
Ez a megoldás feltételezi, hogy mind a kettő számlát ki akarja emelni, hogy kézzel kitalálhassa, hogy melyiket törölje vagy javítsa. Ha nem szeretné jelölni az első előfordulása az ismétlődő, akkor módosítsa a képlet a következő: =COUNTIF($A$2:$A2,A2)>1. Fontos, hogy pontosan a bemutatott módon írja be a dollárjeleket. Ez csak az összes cellát fogja megnézni az aktuális cellától felfelé, ismétlődő bejegyzéseket keresve.
Köszönet a kérdésnek a bostoni Joe-nak!
