Šiame straipsnyje sužinosime, kaip ieškoti tikslių atitikčių naudojant „SUMPRODUCT“ funkciją programoje „Excel“.
Scenarijus:
Paprastais žodžiais tariant, kai dirbame su duomenų lentelėmis, kartais „Excel“ programoje turime ieškoti reikšmių didžiosiomis ir mažosiomis raidėmis. Paieškos funkcijos, tokios kaip VLOOKUP arba MATCH, neranda tikslios atitikties, nes tai nėra didžiosios ir mažosios raidės. Tarkime, kad dirbame su duomenimis, kuriuose 2 vardai yra atskirti pagal didžiųjų ir mažųjų raidžių jautrumą, ty Jerry ir JERRY yra du skirtingi vardai. Galite atlikti tokias užduotis kaip operacijos keliuose diapazonuose naudodami toliau pateiktą formulę.
Kaip išspręsti problemą?
Norėdami išspręsti šią problemą, turėsime naudoti funkciją SUMPRODUCT & EXACT. Dabar iš funkcijos sudarysime formulę. Čia mums pateikiama lentelė ir turime rasti reikšmes, atitinkančias tikslią atitiktį „Excel“ lentelėje. Funkcija SUMPRODUCT grąžina atitinkamų TRUE reikšmių SUM (kaip 1) ir ignoruoja reikšmes, kurios atitinka FALSE reikšmes (kaip 0) grąžintame masyve
Bendra formulė:
| = SUMPRODUCT ( - (EXACT (lookup_value, lookup_array)), (return_array)) |
lookup_value: ieškoma vertė.
lookup_array: paieškos masyvas, skirtas paieškos vertei.
return_array: masyvas, grąžinta vertė, atitinkanti paieškos masyvą.
-: neigimas (-) char pakeičia reikšmes TRUEs arba 1s į FALSEs arba 0s ir FALSEs arba 0s į TRUEs arba 1s.
Pavyzdys:
Visa tai gali būti painu suprasti. Taigi, išbandykime šią formulę, paleisdami ją žemiau pateiktame pavyzdyje. Čia mes turime duomenis apie gautų produktų kiekį ir kainą šiomis dienomis. Jei bet kuris produktas perkamas du kartus, jis turi 2 pavadinimus. Čia turime rasti visų būtiniausių prekių elementų skaičių. Taigi tam mes priskyrėme 2 sąrašus kaip gautą sąrašą ir būtinus dalykus, reikalingus formulės stulpelyje. Dabar mes naudosime žemiau pateiktą formulę, norėdami gauti „pieno“ kiekį iš lentelės.
Naudokite formulę:
| = SUMPRODUCT ( - (TIKSLAS (F5, B3: B11)), (C3: C11)) |
F6: ieškokite vertės F6 langelyje
B3: B11: ieškoti masyvo yra elementai
C3: C11: grąžinimo masyvas yra Kiekis
-: neigimas (-) char pakeičia reikšmes, TRUEs arba 1s į FALSEs arba 0s, o FALSEs arba 0s į TRUEs arba 1s.
Čia diapazonas pateikiamas kaip langelio nuoroda. Paspauskite „Enter“, kad gautumėte kiekį.
Kaip matote, 58 yra kiekis, atitinkantis „pieno“ vertę B5 ląstelėje, o ne „pienas“ B9 ląstelėje. Jei reikia C9 ląstelės kiekio „54“, turėsite ieškoti vertės „Pienas“.
Kainą, atitinkančią vertę „pienas“, galite sužinoti tiesiog naudodami žemiau pateiktą formulę.
Naudokite formulę:
| = SUMPRODUCT ( - (TIKSLAS (F5, B3: B11)), (D3: D11)) |
F6: ieškokite vertės F6 langelyje
B3: B11: ieškoti masyvo yra elementai
D3: D11: grąžinimo masyvas yra kaina
Čia 58 yra kaina, atitinkanti „pieno“ vertę B5 ląstelėje, o ne „pienas“ B9 ląstelėje. Turėsite ieškoti vertės „Pienas“, jei jums reikia kainos „15.58“ D9 langelyje.
Unikalios vertės paieškos sistemoje
Panašiai unikalias vertes galite rasti naudodami formulę. Čia kaip pavyzdys naudojama paieškos vertė „Kiaušiniai“.
Aukščiau pateiktame paveikslėlyje mes gavome tas pačias formules koreguojančias reikšmes. Tačiau problema kyla, jei ji turi unikalią vertę ir neieškote tinkamos paieškos vertės. Kaip ir čia, lentelėje ieškodami formulės vertės „Duona“.
Formulė grąžina 0 kaip kiekį ir kainą, tačiau tai nereiškia, kad duonos kiekis ir kaina yra 0. Tiesiog formulė grąžina 0 kaip vertę, kai nerandama jūsų ieškoma vertė. Taigi naudokite šią formulę tik norėdami rasti tikslią atitiktį.
Taip pat galite atlikti tikslios paieškos atitikmenis naudodami „Excel“ funkciją INDEX ir MATCH. Sužinokite daugiau apie tai, kaip atlikti didžiųjų ir mažųjų raidžių paiešką naudojant funkciją „INDEX & MATCH“ programoje „Excel“. Taip pat galite ieškoti dalinių atitikčių naudodami „Excel“ pakaitos simbolius.
Štai keletas stebėjimo pastabų, parodytų žemiau.
Pastabos:
- Formulė veikia tik norint rasti tikslią atitiktį.
- Funkcija SUMPRODUCT ne skaitines reikšmes laiko 0.
- Funkcija SUMPRODUCT loginę reikšmę TRUE laiko 1, o klaidingą - 0.
- Argumentų masyvas turi būti tokio paties dydžio, kitaip funkcija grąžina klaidą.
- Funkcija SUMPRODUCT grąžina reikšmę, atitinkančią TRUE reikšmes grąžintame masyve, paėmus atskirus produktus atitinkamame masyve.
- Operatoriams patinka lygūs ( = ), mažesnis nei lygus ( <= ), geresnis negu ( > ) arba nelygi () gali būti atlikta taikant formulę, naudojant tik skaičius.
Tikimės, kad šis straipsnis apie tai, kaip ieškoti tikslios atitikties naudojant „SUMPRODUCT“ funkciją „Excel“, yra aiškinamasis. Daugiau straipsnių apie formulių skaičiavimą rasite čia. Jei jums patiko mūsų tinklaraščiai, pasidalykite jais su savo draugais „Facebook“. Taip pat galite sekti mus „Twitter“ ir „Facebook“. Mes norėtume išgirsti jūsų nuomonę, praneškite mums, kaip galime patobulinti, papildyti ar naujovinti savo darbą ir padaryti jį geresnį. Parašykite mums el
Kaip naudoti „SUMPRODUCT“ funkciją programoje „Excel“: Grąžina SUM, padauginus reikšmes keliuose „Excel“ masyvuose.
Norėdami ieškoti vertės, naudokite INDEX ir MATCH : INDEX-MATCH formulė naudojama dinamiškai ir tiksliai ieškoti vertės lentelėje. Tai yra alternatyva funkcijai VLOOKUP ir ji pašalina VLOOKUP funkcijos trūkumus.
Suma pagal OFFSET grupes eilutėse ir stulpeliuose : Funkcija OFFSET gali būti naudojama dinamiškai susumuojant ląstelių grupes. Šios grupės gali būti bet kurioje lapo vietoje.
Kaip atkurti kiekvieną „N“ reikšmę diapazone „Excel“: Naudodami „Excel“ funkciją OFFSET, mes galime gauti reikšmes iš kintančių eilučių ar stulpelių. Ši formulė naudoja funkciją ROW, kad būtų galima keisti eilutes, ir funkciją COLUMN, kad kaitaliotų stulpelius.
Kaip naudoti „OFFSET“ funkciją „Excel“ : Funkcija OFFSET yra galinga „Excel“ funkcija, kurią daugelis vartotojų nepakankamai įvertina. Tačiau ekspertai žino OFFSET funkcijos galią ir tai, kaip mes galime naudoti šią funkciją kai kurioms stebuklingoms užduotims atlikti „Excel“ formulėje. Čia yra funkcijos OFFSET pagrindai.
Kaip „Excel“ naudoti pakaitos simbolius : Suskaičiuokite frazes atitinkančias frazes naudodami „Excel“ pakaitos simbolius
Populiarūs straipsniai
50 „Excel“ spartusis klavišas produktyvumui padidinti : Greičiau atlikite savo užduotį. Šie 50 sparčiųjų klavišų leis dar greičiau dirbti naudojant „Excel“.
Kaip naudotis t„VLOOKUP“ funkcija „Excel“ : Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama norint ieškoti vertės iš skirtingų „Excel“ diapazonų ir lapų.
Kaip naudoti funkciją „COUNTIF“ programoje „Excel“ : Skaičiuokite vertes su sąlygomis naudodami šią nuostabią funkciją. Norint suskaičiuoti konkrečias „Excel“ vertes, nereikia filtruoti duomenų. Funkcija COUNTIF yra būtina norint paruošti prietaisų skydelį.
Kaip naudoti „SUMIF“ funkciją „Excel“ : Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes pagal konkrečias sąlygas.