V tem članku se bomo naučili, kako poiskati natančna ujemanja s funkcijo SUMPRODUCT v Excelu.
Scenarij:
Preprosto povedano, pri delu s podatkovnimi tabelami moramo včasih iskati vrednosti z velikimi in malimi črkami v Excelu. Iskalne funkcije, na primer funkcije VLOOKUP ali MATCH, ne najdejo natančnega ujemanja, saj to niso funkcije, ki razlikujejo med velikimi in malimi črkami. Predpostavimo, da delate na podatkih, pri katerih se 2 imena razlikujeta glede na velikost črk, tj. Jerry in JERRY sta dve različni imeni. Naloge, kot so operacije v več obsegih, lahko izvajate s spodnjo formulo.
Kako rešiti težavo?
Za to težavo bomo morali uporabiti funkcijo SUMPRODUCT & EXACT. Zdaj bomo iz funkcije naredili formulo. Tukaj dobimo tabelo in v tabeli v Excelu moramo poiskati vrednosti, ki ustrezajo natančnemu ujemanju. Funkcija SUMPRODUCT vrne SUM ustreznih vrednosti TRUE (kot 1) in prezre vrednosti, ki ustrezajo vrednosti FALSE (kot 0) v vrnjenem nizu
Splošna formula:
| = SUMPRODUCT ( - (EXACT (lookup_value, lookup_array)), (return_array)) |
lookup_value: vrednost za iskanje.
lookup_array: iskalno polje za iskalno vrednost.
return_array: matrika, vrnjena vrednost, ki ustreza matriki iskanja.
-: Negacija (-) char spremeni vrednosti, TRUE ali 1s v FALSE ali 0s in FALSEs ali 0s v TRUEs ali 1s.
Primer:
Vse to je lahko zmedeno za razumevanje. Torej, preizkusimo to formulo tako, da jo zaženemo na spodnjem primeru. Tu imamo podatke o količini in ceni izdelkov, prejetih na datume. Če kateri koli izdelek kupite dvakrat, ima dva imena. Tu moramo najti število predmetov za vse bistvene predmete. Zato smo za prejeti seznam dodelili 2 seznama in bistvene elemente, ki so potrebni v stolpcu za formulo. Zdaj bomo uporabili spodnjo formulo, da dobimo količino "mleka" iz tabele.
Uporabite formulo:
| = PODROČJE ( - (TOČNO (F5, B3: B11)), (C3: C11)) |
F6: poiščite vrednost v celici F6
B3: B11: matrika za iskanje so postavke
C3: C11: povratna matrika je količina
-: Negacija (-) char spremeni vrednosti, TRUE ali 1s v FALSE ali 0s in FALSEs ali 0s v TRUEs ali 1s.
Tukaj je obseg podan kot referenca celice. Pritisnite Enter, da dobite količino.
Kot lahko vidite, je 58 količina, ki ustreza vrednosti "mleko" v celici B5 in ne "mleko" v celici B9. Če potrebujete količino "54" v celici C9, boste morali poiskati vrednost "Mleko".
Ceno, ki ustreza vrednosti "mleko", lahko poiščete samo s spodnjo formulo.
Uporabite formulo:
| = PODROČJE ( - (TOČNO (F5, B3: B11)), (D3: D11)) |
F6: poiščite vrednost v celici F6
B3: B11: matrika za iskanje so postavke
D3: D11: povratna matrika je Cena
Tukaj imamo 58 je cena, ki ustreza vrednosti "mleko" v celici B5 in ne "mleko" v celici B9. Če potrebujete ceno "15,58" v celici D9, boste morali poiskati vrednost "Mleko".
Edinstvene vrednosti v matriki iskanja
Podobno lahko edinstvene vrednosti najdete s formulo. Tu je kot primer uporabljena iskalna vrednost "EGGS".
Na zgornji sliki imamo vrednosti, ki prilagajajo isto formulo. Težava pa nastane, če ima edinstveno vrednost in ne iščete ustrezne iskalne vrednosti. Tako kot tukaj uporabite vrednost "Kruh" v formuli za iskanje v tabeli.
Formula vrne 0 kot količino in ceno, vendar to ne pomeni, da sta količina in cena kruha 0. Samo, da formula vrne 0 kot vrednost, če vrednosti, ki jo iščete, ne najdete. Zato uporabite to formulo samo za iskanje natančnega ujemanja.
Poiščete lahko tudi natančna ujemanja s funkcijami INDEX in MATCH v Excelu. Preberite več o tem, kako v Excelu izvedete iskanje velikih in malih črk s funkcijo INDEX & MATCH. Delne ujemanja lahko poiščete tudi z nadomestnimi znaki v Excelu.
Spodaj je nekaj opazovalnih opomb.
Opombe:
- Formula deluje samo za iskanje natančnega ujemanja.
- Funkcija SUMPRODUCT šteje neštevilčne vrednosti kot 0.
- Funkcija SUMPRODUCT upošteva logično vrednost TRUE kot 1 in False kot 0.
- Niz argumentov mora biti enake velikosti, sicer funkcija vrne napako.
- Funkcija SUMPRODUCT vrne vrednost, ki ustreza vrednostim TRUE v vrnjenem nizu, potem ko vzamete posamezne izdelke v ustreznem nizu.
- Operaterji so enaki ( = ), manj kot enako ( <= ), večji kot ( > ) ali ni enako () je mogoče izvesti v uporabljeni formuli, samo s številkami.
Upam, da je ta članek o tem, kako poiskati natančno ujemanje s funkcijo SUMPRODUCT v Excelu, razložljiv. Več člankov o formulah štetja najdete tukaj. Če so vam bili naši blogi všeč, jih delite s svojimi začetniki na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletni strani elektronske pošte
Kako uporabljati funkcijo SUMPRODUCT v Excelu: Vrne SUM po množenju vrednosti v več matrikah v Excelu.
Za iskanje vrednosti uporabite INDEX in MATCH : Formula INDEX-MATCH se uporablja za dinamično in natančno iskanje vrednosti v dani tabeli. To je alternativa funkciji VLOOKUP in premaga pomanjkljivosti funkcije VLOOKUP.
Vsota po skupinah OFFSET v vrsticah in stolpcih : Funkcijo OFFSET lahko uporabite za dinamično seštevanje skupin celic. Te skupine so lahko kjer koli na listu.
Kako v Excelu pridobiti vsako n -to vrednost v obsegu: S funkcijo OFFSET v Excelu lahko pridobimo vrednosti iz izmeničnih vrstic ali stolpcev. Ta formula uporablja funkcijo ROW za zamenjavo vrstic in funkcijo COLUMN za izmenjavo v stolpcih.
Kako uporabljati funkcijo OFFSET v Excelu : Funkcija OFFSET je močna Excelova funkcija, ki jo številni uporabniki podcenjujejo. Toda strokovnjaki poznajo moč funkcije OFFSET in kako lahko to funkcijo uporabimo za čarobne naloge v formuli Excel. Tu so osnove funkcije OFFSET.
Kako uporabljati nadomestne znake v Excelu : Preštejte celice, ki se ujemajo z frazami, z uporabo nadomestnih znakov v Excelu
Priljubljeni članki
50 Bližnjica v Excelu za večjo produktivnost : Hitreje opravite svojo nalogo. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Kako uporabljati tFunkcija VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov v Excelu.
Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti v Excelu vam ni treba filtrirati podatkov. Funkcija COUNTIF je bistvena za pripravo vaše nadzorne plošče.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga pri seštevanju vrednosti z določenimi pogoji.