Ako v programe Excel sa môžem ubezpečiť, že v konkrétnom stĺpci programu Excel nie sú zadané duplicitné čísla faktúr?
V programe Excel 97 môžete na to použiť novú funkciu overenia údajov. V našom príklade sa čísla faktúr zadávajú do stĺpca A. Tu je postup, ako to nastaviť pre jednu bunku:
- Ďalšia bunka, ktorá sa má zadať, je A9. Kliknite do bunky A9 a v ponuke vyberte položku Údaje> Overenie.
- V rozbaľovacom zozname „Povoliť:“ vyberte možnosť „Vlastné“
- Zadajte tento vzorec presne tak, ako vyzerá:
=ISNA(VLOOKUP(A9,A$1:A8,1,FALSE)) - Kliknite na kartu Chybové upozornenie v dialógovom okne Overenie údajov.
- Skontrolujte, či je začiarknuté políčko „Zobraziť výstrahu“.
- Pre štýl :, vyberte Stop
- Zadajte nadpis „Nejedinečná hodnota“
- Zadajte správu „Musíte zadať jedinečné číslo faktúry.“
- Kliknite na „OK“
Môžete si to vyskúšať. Do bunky A9 zadajte novú hodnotu, napríklad 10001. Žiaden problém. Skúste však zopakovať hodnotu, povedzme 10088, a zobrazí sa nasledujúci text:
Posledná vec, ktorú musíte urobiť, je skopírovať toto overenie z bunky A9 do ďalších buniek v stĺpci A.
- Kliknite na stĺpec A a vyberte položky Upraviť> kopírovať a skopírujte bunku.
- Vyberte veľký rozsah buniek v stĺpci A. Možno A10: A500.
- Vyberte možnosť Upraviť, Prilepiť špeciálne. V dialógovom okne Prilepiť špeciálne vyberte možnosť Overenie a kliknite na tlačidlo OK. Pravidlo overovania, ktoré ste zadali z bunky A9, sa skopíruje do všetkých buniek až po A500.
Ak kliknete na bunku A12 a vyberiete Overenie údajov, uvidíte, že program Excel zmenil overovací vzorec na =ISNA(VLOOKUP(A12,A$1:A11,1,FALSE))To je všetko, čo potrebujete vedieť, aby to fungovalo. Pre tých z vás, ktorí sa chcú dozvedieť viac, vysvetlím v angličtine, ako vzorec funguje.
=ISNA(VLOOKUP(A9,A$1:A8,1,FALSE))
Sedíme v cele A9. Hovoríme funkcii Vlookup, aby zobrala hodnotu bunky, ktorú sme práve zadali (A9), a aby sa pokúsila nájsť zhodu v bunkách, ktoré sa pohybujú od A $ 1 do A8. Nasledujúci argument, 1, hovorí Vlookupovi, že keď sa nájde zhoda, oznámi nám údaje z prvého stĺpca. Nakoniec False vo vlookupe hovorí, že hľadáme iba presné zhody. Tu je trik č. 1: Ak VLOOKUP nájde zhodu, vráti hodnotu. Ak však nenájde zhodu, vráti špeciálnu hodnotu „# N / A“. Normálne sú tieto hodnoty # N / A zlé veci, ale v takom prípade CHCEME # N / A. Ak dostaneme # N / A, potom viete, že tento nový záznam je jedinečný a nezhoduje sa s ničím nad ním. Jednoduchým spôsobom, ako otestovať, či je hodnota # N / A, je použitie funkcie ISNA (). Ak sa vo vnútri ISNA () vyhodnotí hodnota # N / A, dostanete hodnotu PRAVDA. Takžekeď zadajú nové číslo faktúry a nenájde sa v zozname nad bunkou, vlookup vráti # N / A, čo spôsobí, že ISNA () bude pravdivá.
Druhý kúsok triku je v druhom argumente pre funkciu Vlookup. Pri zadávaní A $ 1: A8 som si dal pozor. Znak dolára pred číslom 1 hovorí programu Excel, že keď skopírujeme toto overenie do iných buniek, malo by sa vždy začať hľadať v bunke aktuálneho stĺpca. Toto sa nazýva absolútna adresa. Rovnako opatrne som dával znak dolára pred 8 v A8. Toto sa nazýva relatívna adresa a informuje program Excel, že keď túto adresu skopírujeme, mala by prestať hľadať v bunke hneď nad aktuálnou bunkou. Keď potom skopírujeme validáciu a pozrieme sa na validáciu pre bunku A12, druhý argument vo vlookup správne ukazuje A $ 1: A11.
S týmto riešením sú dva problémy. Po prvé, v programe Excel 95 to nebude fungovať. Po druhé, overenia sa vykonajú iba v bunkách, ktoré sa zmenia. Ak v bunke A9 zadáte jedinečnú hodnotu a potom sa vrátite späť a upravíte bunku A6 tak, aby bola rovnakou hodnotou, akú ste zadali v A9, logika overenia v A9 sa nevyvolá a v pracovnom hárku skončíte s duplicitnými hodnotami.
Staromódna metóda použitá v programe Excel 95 bude riešiť obidve tieto problémy. Pri starej metóde by ste mali logiku overovania v dočasnom stĺpci B. Ak to chcete nastaviť, zadajte do bunky B9 nasledujúci vzorec: =ISNA(VLOOKUP(A9,A$1:A8,1,FALSE))Skopírujte tento vzorec z B9. Vložte ho do buniek B2: B500. Keď teraz zadáte čísla faktúr do stĺpca A, v stĺpci B sa zobrazí PRAVDA, ak je faktúra jedinečná, a FALSE, ak nie je jedinečná.
