Selles artiklis õpime, kuidas tabelis puuduvaid väärtusi otsida, kasutades valemit Excelis.
Näiteks on meil väärtuste loend ja vajame kinnitust loendis oleva väärtuse asukoha kohta. Selleks kaalume olukorda ja soovitame mõnda valemit sama teha. Sõltuvalt tabeli_massiivi lihtsusest kasutame erinevaid funktsioone.
Kasutusel on järgmine funktsioon. Nii et tutvuge nendega veidi enne selle kasutamist.
- IF funktsioon
- Funktsioon COUNTIF
- ISNA funktsioon
- Funktsioon VLOOKUP
- MATCH funktsioon
Koostame sellest valemi. Esiteks otsitakse tabeli massiivi konkreetsest veerust otsinguväärtust. Seejärel annavad sobitatud väärtused meile IF -funktsiooni abil kinnituse. Funktsioon IF tagastab kinnituse, kasutades väärtusi "Kas on olemas" ja "Puudub".
Esimene meetod: Funktsiooni COUNTIF ja IF kasutamine
Kasutage üldist valemit
| = IF (COUNTIF (loend, lahtri_väärtus), "Kas on olemas", "Puudub") |
Selgitus:
- Funktsioon COUNTIF hoiab loendis lahtri_väärtus arvu ja tagastab selle funktsiooni IF.
- Funktsiooni IF puhul loe 0 väärtuseks VÄÄR ja muud täisarvud peale 0 tõeks.
- Funktsioon IF tagastab väärtuse "Kas on" väärtuse, kui see on tõene, ja "Puudub" väärtusena, kui see on vale.
Näide:
Läbime selle näite valemi abil.
Siin on meil tabel ja me peame sellest teavet ammutama.
Siin vajame kinnitust ID -loendi alusel. Seega kasutame ID -loendi jaoks nimega vahemikku. ID nimega vahemiku kasutamine C2: C14 jaoks.
Seega kasutame kogusumma saamiseks valemit
| = IF (COUNTIF (ID, G4), "Kas on olemas", "Puudub") |
Valemi selgitus:
- Funktsioon COUNTIF hoiab loendis ID 900 ja tagastab loendi funktsioonile IF.
- Funktsiooni IF puhul loe 0 väärtuseks VÄÄR ja muud täisarvud peale 0 tõeks
- Funktsioon IF tagastab väärtuseks "Kas on", kui see on tõene, ja "puuduva" väärtusena, kui see on vale.
Siin on funktsiooni argumentide massiivid antud lahtri viitena.
Nagu näete, tagastab valem ID -numbri 807 ja 953 väärtused. Kuid tagastab ID -numbri 900 puuduva.
Teine meetod: Funktsiooni ISNA & VLOOKUP kasutamine.
Valemi süntaks:
| = IF (ISNA (VLOOKUP (cell_value, list, 1, 0)), "Missing", "Is There") |
Valemi selgitus:
- Funktsioon VLOOKUP otsib lahtri väärtuse loendi table_array esimesest veerust. Funktsioon tagastab väärtuse, kui see leitakse, või tagastab vea #N/A.
- Funktsioon ISNA püüab vea #N/A ja tagastab tõe, kui tõrge #N/A on olemas või tagastab väärtuse VÄÄR.
- Funktsioon IF tagastab väärtuseks "Kas on" väärtuse VÄÄR ja "Puudub" väärtuseks, kui see on tõene.
Nagu ülaltoodud hetktõmmist näete. Valem tagastab sobitatud ID 807 ja 953 jaoks väärtuse "Kas on olemas". Kuid tasakaalustamata ID 900 korral tagastab "Puudub".
Kolmas meetod: Funktsiooni ISNA & MATCH kasutamine.
Valemi süntaks:
| = IF (ISNA (MATCH (cell_value, list, 0)), "Missing", "Is There") |
Valemi selgitus:
- Funktsioon MATCH otsib lahtri väärtuse loendist table_array. Funktsioon tagastab väärtuse, kui see leitakse, või tagastab vea #N/A.
- Funktsioon ISNA püüab vea #N/A ja tagastab tõe, kui tõrge #N/A on olemas või tagastab väärtuse VÄÄR.
- Funktsioon IF tagastab väärtuseks "Kas on" väärtuse VÄÄR ja "Puudub" väärtuseks, kui see on tõene.
Nagu ülaltoodud hetktõmmist näete. Valem tagastab sobitatud ID 807 ja 953 jaoks väärtuse "Kas on olemas". Kuid tasakaalustamata ID 900 korral tagastab "Puudub".
Ülaltoodud selgitas 3 näidet, et leida Exceli loendist puuduvad väärtused. Kõik kolm valemit töötavad hästi, kuid on mõned punktid, mida otsida.
Märkused:
- Funktsioon VLOOKUP ei vaata tabeli_massiivist vasakule.
- Funktsioon COUNTIF toetab metamärke ( * , ? ), mis aitab fraase sisaldavaid väärtusi eraldada.
- Mittearvulised väärtused tuleb esitada jutumärkides ("väärtus") või kasutage lahtri_viidet …
- Vaadake kogu loendit, kuna funktsioon tagastab väärtused kõikjal, kus see sobib.
- Funktsiooni massiivi argumendi saab anda lahtriviidena või nimetada vahemikke.
- Te saate neid valemeid vastavalt vajadusele kohandada, kasutades muud Exceli funktsiooni.
- Funktsioon tagastab kõikidele tingimustele vastavate väärtuste summa.
Loodetavasti mõistsite, kuidas Excelist puuduvaid väärtusi leida. Siit leiate rohkem artikleid Exceli funktsioonivalemite kohta. Palun esitage oma päring või tagasiside ülaltoodud artikli kohta. Me aitame teid.
INDEX-MATCH Excelis
VLOOKUP Mitu väärtust
VLOOKUP koos dünaamilise värvide indeksiga
Osaline vaste funktsiooniga VLOOKUP
Vlookup Excelis kuupäeva järgi
17 asja Exceli kohta VLOOKUP
Populaarsed artiklid
50 Exceli otsetee tootlikkuse suurendamiseks
Muutke ripploendit
Absoluutne viide Excelis
Kui tingimusvorminguga
Kui metamärkidega
Vlookup kuupäeva järgi