Használjon Excel-adatok érvényesítését az oszlopban lévő párhuzamos értékek megelőzéséhez

Kép: flukyfluky, Getty Images / iStockphoto

Az Excel beépített eszközökkel rendelkezik a másolatok feltárására és akár törlésére is, ám a tény után a meglévő adatokon dolgoznak. Ha azt szeretné biztosítani, hogy az ismétlődések először soha ne forduljanak elő, fontolóra veheti az Excel adatérvényesítő funkciójának használatát. Ez a szolgáltatás ellenőrzi az értékeket, amikor beírja őket, és a megadott szabályoktól függően elutasítja vagy elfogadja az értéket. Sajnos nincs beépített érvényesítési szabály, amely felismeri a másolatot, ezért a szolgáltatást össze kell kombinálnia az Excel COUNTIF () funkciójával.

Ebben a cikkben megmutatom, hogyan kell ezt csinálni egy táblázatobjektumban strukturált hivatkozások és megnevezett tartományok segítségével.

További információ az Office-ról

  • 18 Excel tipp minden felhasználónak elsajátítania kell
  • Microsoft Office, adatelemzés, virtuális asszisztens a legkeresettebb munkaképesség
  • Bérleti készlet: Microsoft Power BI fejlesztő (TechRepublic Premium)
  • Feliratkozás a Microsoft Heti hírlevelünkre

Az Office 365 Excel 2016 (asztali) verzióját használom 64 bites Windows 10 rendszeren, de mindkét technika a korábbi verziókban és a böngésző kiadásban is működni fog. Dolgozhat saját adataival, vagy letöltheti a demonstrációs .xlsx és .xls fájlokat.

A COUNTIF-ről ()

Nincs beépített duplikált szabály az Excel adatérvényesítési szolgáltatásához, de a funkció elvégzéséhez kombinálhatja a funkciót a COUNTIF () funkcióval. Ahhoz, hogy hozzáértő módon tudja megtenni, tudnia kell a COUNTIF () funkcióról. (Nyugodtan hagyja ki ezt a részt, ha tudja, hogyan kell használni ezt a funkciót.)

A COUNTIF () függvény kiszámítja a cellák számát egy tartományban, amely megfelel egy adott feltételnek. A tartományt és a feltételt argumentumként adja meg a következő szintaxissal:

 COUNTIF (tartomány, állapot) 

Használjuk ezt a funkciót annak számításához, hogy hányszor jelenik meg a tagsági szám az A. ábrán látható lap B. oszlopában, a tagsági szám oszlopban. Jelenleg ez az oszlop lehetővé teszi a másolatokat.

A ábra

Az adatok érvényesítését használjuk annak érdekében, hogy megakadályozzuk a párhuzamos számokat a tagsági szám oszlopban.

Először írja be a következő függvényt a K3 cellába:

 = COUNTIF (1. táblázat tagsági szám, B3) 

A függvény strukturált hivatkozást használ, mivel az adatok táblázati objektumként vannak formázva. Mivel a 100 érték csak egy alkalommal fordul elő az oszlopban, a függvény visszatér 1. Másolja a maradék cellákra, hogy megnézze, hogy mind 1-t adnak vissza (A ábra ). Ha valamelyik értéket megismétli, a megfelelő függvények visszatérnek a 2-es értékhez, ahogy a B ábra mutatja. Feltételes formázással is figyelmeztetheti Önt, hogy létezik másolat, de nem lenne jobb, ha elkerülné a másolatot?

B. ábra

A függvény a feltétel teljesítésének számát adja vissza.

Asztali szállások

A COUNTIF () függvény hozzáadásával az adatérvényesítési beállításokhoz használhatja ezt a funkciót egy érték elutasításához, ha az már létezik a tartományon belül. Mielőtt elkezdené, ellenőrizze, hogy a kérdéses oszlopban nincs-e másolat. Ezt a technikát illusztrálhatjuk, ha hozzáadunk egy ilyen érvényesítési adat-vezérlést a tagsági szám oszlophoz az alábbiak szerint:

  1. Válassza ki az összes létező adatcellát a kérdéses oszlopban. Ebben az esetben ez a B3: B6.
  2. Kattintson az Adat fülre, és válassza az Adat érvényesítés lehetőséget az Adat ellenőrzés legördülő menüből (az Adat eszközök csoportjában).
  3. A megjelenő párbeszédpanelen válassza az Egyedi lehetőséget az Engedélyezés legördülő menüből.
  4. A Képlet vezérlőbe írja be a képletet ( C ábra )
    = COUNTIF (közvetett ("1. táblázat tagsági szám"), B3) <= 1
    ügyeljen arra, hogy egyenes (nem göndör idézőjelek) kifejezést használjon. Ha saját adataival dolgozik, feltétlenül frissítse a Táblázat és az oszlop nevét.
  5. Kattintson az OK gombra.

C. ábra

Ez az egyéni szabály elutasítja a másolatokat a tagsági szám oszlopban.

Nem kell pontosan tudnia, hogyan működik az INDIRECT () függvény, de röviden visszaadja a hivatkozást mint szöveget. Ennek a funkciónak a hiányában a szolgáltatás elutasítja a függvényt, mert a táblázati objektumok elhelyezéséhez szükséges strukturált referenciák szükségesek. (Megadhatja a tényleges tartományt, de először meg kell adnia a tartomány nevét. A következő szakaszban megmutatom, hogyan kell ezt csinálni.)

A Táblázatnak és az INDIRECT () funkciónak köszönhetően a tartomány minden sor hozzáadásakor növekszik. Ha meglévő értéket ad meg a tartomány bármelyik cellájában, akkor a szolgáltatás elutasítja azt, ahogy a D ábra mutatja. A hibaüzenet nem különösen hasznos, hacsak nem ismeri a másolatok szabályát; használhatja a szolgáltatás Beviteli üzenet és Hiba riasztás lapjait (lásd a B. ábrát ), hogy értelmes információkat jelenítsen meg a felhasználók számára.

D. ábra

Az egyéni szabály elutasítja az értéket, ha az már létezik a tartományban .

Nevű tartomány

Nincs indok az INDIRECT () elkerülésére, de a nevű tartományokat használhatja. A következőképpen adhat nevet a meglévő adatcelláknak:

  1. Válassza a B3: B6 lehetőséget.
  2. Írja be az MNumber nevet a vezérlőbe ( E ábra ).
  3. Nyomd meg az Entert. A név beírásához meg kell nyomnia az Enter billentyűt.

E ábra

A Név vezérlővel adjon meg egy tartományt.

Ezután hozzon létre egy érvényesítési szabályt, mint korábban, de írja be a következő függvényt, amely az INDIRECT () használata helyett a megnevezett tartományra hivatkozik:

 = COUNTIF (MNumber, B3) <= 1 

A két szabály ugyanúgy működik, de az egyik a Táblázat szerkezettel működik, az egyik a megnevezett tartományt használja.

Microsoft Heti Hírlevél

Legyen Ön a vállalata Microsoft bennfentese ezen Windows és Office útmutatók, valamint a Microsoft vállalati termékeinek elemzői által végzett elemzés segítségével. Hétfőn és szerdán szállítjuk

Regisztrálj még ma

Küldje el nekem az Office iránti kérdését

Amikor tudok, válaszolok az olvasók kérdéseire, de nincs garancia. Ne küldjön fájlokat, kivéve, ha azt kéri; A mellékelt fájlokkal érkező kezdeti segítségkéréseket olvasatlanul törlik. Küldhet képernyőképeket adataival a kérdés tisztázása érdekében. Ha kapcsolatba lép velem, légy a lehető legpontosabb. Például: "Kérjük, javítsa ki a munkafüzetet, és javítsa ki a hibát" valószínűleg nem fog választ kapni, de "Meg tudja mondani, hogy ez a képlet miért nem adja meg a várt eredményeket?" esetleg. Kérjük, említse meg az Ön által használt alkalmazást és verziót. A TechRepublic nem téríti meg az olvasómnak nyújtott segítségért járó idemet vagy szakértelmet, és nem kérnek díjat az általam segített olvasóktól. Vegye fel a kapcsolatot velem a következő címen: email protected

Lásd még

  • Rendkívül egyszerű módszer új rekordok létrehozására többértékű oszlopokból az Excel Power Query (TechRepublic) használatával
  • Három módszer a szószedetek hozzáadásához a Microsoft Word 2016 dokumentumhoz (TechRepublic)
  • Három futó kifejezés az Excel számára (TechRepublic)
  • Excel hibák: Hogyan veszélyes lehet a Microsoft táblázata az egészségre (ZDNet)
  • Öt tipp az Outlook 2016 automatikus kiegészítés listájának hatékony használatához (TechRepublic)
  • A VBA használata az Excel tartomány kiválasztásához (TechRepublic)

© Copyright 2021 | mobilegn.com