Selles artiklis õpime, kuidas otsida täpseid vasteid Exceli funktsiooni SUMPRODUCT abil.
Stsenaarium:
Lihtsamalt öeldes peame andmetabelitega töötades mõnikord otsima Excelis väärtusi väiketundlike tähtedega. Otsimisfunktsioonid nagu VLOOKUP või MATCH ei leia täpset vastet, kuna need ei ole tõstutundlikud funktsioonid. Oletame, et töötate andmete kallal, kus 2 nime eristatakse väiketähtede alusel, st Jerry ja JERRY on kaks erinevat eesnime. Alloleva valemi abil saate teha selliseid ülesandeid nagu toimingud mitmes vahemikus.
Kuidas probleemi lahendada?
Selle probleemi lahendamiseks peame kasutama funktsiooni SUMPRODUCT ja EXACT. Nüüd teeme funktsioonist valemi. Siin antakse meile tabel ja peame leidma Exceli tabelist täpsele vastele vastavad väärtused. Funktsioon SUMPRODUCT tagastab vastavate tõeväärtuste SUM (kui 1) ja ignoreerib väärtusi, mis vastavad tagastatud massiivi väärtustele (nagu 0)
Üldine valem:
| = SUMPRODUCT ( - (EXACT (lookup_value, lookup_array)), (return_array)) |
lookup_value: otsitav väärtus.
lookup_array: otsinguväärtuste otsingumassiiv.
return_array: massiiv, tagastatud väärtus, mis vastab otsingumassiivile.
-: eitus (-) sümbol muudab väärtused, tõesed või 1-d väärtused VÄÄRAD või 0-d ja valed või 0-d väärtuseks tõed või 1-d.
Näide:
Kõiki neid võib segadusse ajada. Niisiis, katsetame seda valemit, kasutades seda allpool näidatud näitel. Siin on meil andmed kuupäevadel saadud toodete koguse ja hinna kohta. Kui mõni toode ostetakse kaks korda, on sellel 2 nime. Siin peame leidma kõigi oluliste esemete esemete arvu. Seega oleme selleks määranud vastuvõetud loendiks 2 loendit ja valemi veerus nõutavad olulised asjad. Nüüd kasutame tabelist "piima" koguse saamiseks allolevat valemit.
Kasutage valemit:
| = SUMPRODUCT ( - (TÄPSELT (F5, B3: B11)), (C3: C11)) |
F6: otsige väärtust F6 lahtrist
B3: B11: otsimismassiiv on üksused
C3: C11: tagastamismassiiv on Kogus
-: eitus (-) sümbol muudab väärtused, tõesed või 1-d väärtused VÄÄRAD või 0-d ja valed või 0-d väärtuseks tõed või 1-d.
Siin antakse vahemik lahtri viitena. Koguse saamiseks vajutage sisestusklahvi.
Nagu näete, on 58 kogus, mis vastab väärtusele "piim" B5 lahtris, mitte "piim" B9 lahtris. Kui vajate C9 lahtris kogust "54", peate otsima väärtust "Piim".
Hinnale, mis vastab väärtusele "piim", saate vaadata lihtsalt alloleva valemi abil.
Kasutage valemit:
| = SUMPRODUCT ( - (TÄPSELT (F5, B3: B11)), (D3: D11)) |
F6: otsige väärtust F6 lahtrist
B3: B11: otsimismassiiv on üksused
D3: D11: tagastamismassiiv on Hind
Siin on 58 hind, mis vastab väärtusele "piim" B5 lahtris, mitte "piim" B9 lahtris. Peate otsima väärtust "Piim", kui vajate D9 lahtris hinda "15,58".
Unikaalsed väärtused otsimismassiivis
Sarnaselt leiate unikaalsed väärtused valemi abil. Siin on näitena kasutatud otsinguväärtus "MUNAD".
Ülaltoodud pildil saime väärtused, mis reguleerivad sama valemit. Kuid probleem ilmneb siis, kui sellel on ainulaadne väärtus ja te ei otsi õiget otsinguväärtust. Nagu siin, kasutades tabelis leidmiseks valemis väärtust "Leib".
Valem tagastab koguse ja hinna 0, kuid see ei tähenda, et leiva kogus ja hind oleksid 0. Lihtsalt see, et valem tagastab väärtuse 0, kui otsitavat väärtust ei leita. Nii et kasutage seda valemit ainult täpse vaste otsimiseks.
Täpseid vasteid saate teha ka Exceli funktsiooni INDEX ja MATCH abil. Lugege lisateavet selle kohta, kuidas teha Exceli rakenduses INDEX & MATCH funktsiooni tõstutundlik otsing. Osalisi vasteid saate otsida ka Exceli metamärkide abil.
Siin on mõned allpool toodud vaatlusmärkused.
Märkused:
- Valem töötab ainult täpse vaste otsimiseks.
- Funktsioon SUMPRODUCT loeb mitte-numbrilised väärtused 0-deks.
- Funktsioon SUMPRODUCT loeb loogilise väärtuse TRUE väärtuseks 1 ja vale väärtuseks 0.
- Argumendimassiiv peab olema sama suur, vastasel juhul tagastab funktsioon vea.
- Funktsioon SUMPRODUCT tagastab tagastatud massiivi TRUE väärtustele vastava väärtuse pärast üksikute toodete võtmist vastavas massiivis.
- Operaatoritele meeldib võrdne ( = ), väiksem kui ( <= ), suurem kui ( > ) või ei ole võrdne () saab teostada rakendatud valemis, ainult numbritega.
Loodetavasti on see artikkel, kuidas Excelis funktsiooni SUMPRODUCT abil täpne vaste otsida, selgitav. Siit leiate rohkem artikleid valemite loendamise kohta. Kui teile meeldisid meie ajaveebid, jagage seda oma sõpradega Facebookis. Samuti saate meid jälgida Twitteris ja Facebookis. Tahaksime sinust kuulda, andke meile teada, kuidas saaksime oma tööd täiustada, täiendada või uuendada ning muuta see teie jaoks paremaks. Kirjuta meile meilisaidile
Funktsiooni SUMPRODUCT kasutamine Excelis: Tagastab SUM -i pärast väärtuste korrutamist mitmes Exceli massiivis.
Väärtuse otsimiseks kasutage INDEX ja MATCH : Valemit INDEX-MATCH kasutatakse antud tabeli väärtuse dünaamiliseks ja täpseks otsimiseks. See on alternatiiv funktsioonile VLOOKUP ja see kõrvaldab funktsiooni VLOOKUP puudused.
Summa OFFSET rühmade kaupa ridades ja veergudes : Funktsiooni OFFSET saab kasutada lahtrirühma dünaamiliseks summeerimiseks. Need rühmad võivad olla lehel kõikjal.
Kuidas saada Excelis vahemikus iga n -ö väärtus: Kasutades Exceli funktsiooni OFFSET, saame vahelduvatest ridadest või veergudest väärtusi hankida. See valem kasutab ridade vaheldumiseks funktsiooni ROW ja veergudes vaheldumiseks funktsiooni COLUMN.
Kuidas kasutada Excelis funktsiooni OFFSET : Funktsioon OFFSET on võimas Exceli funktsioon, mida paljud kasutajad alahindavad. Kuid eksperdid teavad funktsiooni OFFSET võimsust ja seda, kuidas me saame seda funktsiooni kasutada Exceli valemis mõne maagilise ülesande täitmiseks. Siin on funktsiooni OFFSET põhitõed.
Kuidas kasutada Excelis metamärke : Loendage fraasidele vastavaid lahtreid, kasutades Excelis metamärke
Populaarsed artiklid
50 Exceli otsetee tootlikkuse suurendamiseks : Saate oma ülesandega kiiremini hakkama. Need 50 otseteed aitavad teil Excelis veelgi kiiremini töötada.
Kuidas kasutada tta funktsioon VLOOKUP Excelis : See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse Exceli erinevate vahemike ja lehtede väärtuse otsimiseks.
Funktsiooni COUNTIF kasutamine Excelis : Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Excelis konkreetsete väärtuste loendamiseks ei pea te oma andmeid filtreerima. Funktsioon COUNTIF on teie armatuurlaua ettevalmistamiseks hädavajalik.
Funktsiooni SUMIF kasutamine Excelis : See on veel üks armatuurlaua oluline funktsioon. See aitab teil väärtusi konkreetsete tingimustega kokku võtta.