See artikkel on pühendatud VLOOKUP funktsioonid. See kaalub VLOOKUP funktsiooni samm-sammult juhised, pealkirjaga "". Selles artiklis vaatleme üksikasjalikult kirjeldust, süntaksit ja näiteid funktsiooni VLOOKUP kohta Excelis. Vaatame ka selle kasutamist ning peamisi vigu ja põhjuseid. VLOOKUP funktsioon ei tööta.

Funktsiooni VLOOKUP süntaks ja kirjeldus Excelis

Seega, kuna selle artikli teine ​​pealkiri on " VLOOKUP funktsioon Excelis mannekeenide jaoks", uurime alustuseks välja, mis on funktsioon VLOOKUP ja mida see teeb? VLOOKUP funktsioon inglise keeles VLOOKUP, otsib määratud väärtuse ja tagastab vastava väärtuse teisest veerust.

Kuidas funktsioon VLOOKUP töötab?? Exceli funktsioon VLOOKUP otsib teie andmete loendeid kordumatu identifikaatori alusel ja annab teile selle kordumatu identifikaatoriga seotud teabe.

VPR-täht "V" tähistab "vertikaalset". Seda kasutatakse eristamiseks Funktsioonid VLOOKUP ja GLOOKUP, mis otsib väärtust massiivi ülemisest reast ("H" tähendab "horisontaalset").

Funktsioon VLOOKUP on saadaval kõigis Excel 2016, Excel 2013, Excel 2010, Excel 2007 ja Excel 2003 versioonides.

Süntaks VLOOKUP funktsioonid järgnevalt:

VLOOKUP(otsingu_väärtus, tabel, veeru_number, [intervalli_otsing])

Nagu sa näed, VLOOKUP funktsioon sellel on 4 parameetrit või argumenti. Esimesed kolm parameetrit on kohustuslikud, viimane on valikuline.

1. otsingu_väärtus on otsingu väärtus.

See võib olla kas väärtus (number, kuupäev või tekst) või lahtri viide(viide otsinguväärtust sisaldavale lahtrile) või mõne muu Exceli funktsiooni tagastatud väärtus. Näiteks:

• Otsi numbrit: =VLOOKUP(40; A2:B15; 2) – valem otsib numbrit 40.
• Tekstiotsing: =VLOOKUP(“õunad”, A2:B15, 2) – valem otsib teksti “õunad”. Pange tähele, et lisate alati tekstiväärtused "jutumärkidesse".
• Väärtuse otsimine teisest lahtrist: =VLOOKUP(C2, A2:B15, 2) – valem otsib väärtust lahtrist C2.

1. tabel on kaks või enam andmeveeru.

mäleta seda VLOOKUP funktsioon otsib alati soovitud väärtust tabeli esimesest veerust. Teie tabel võib sisaldada erinevaid väärtusi, näiteks teksti, kuupäeva, numbreid või tõeväärtusi. Väärtused tõstutundetu, mis tähendab, et suur- ja väiketähti loetakse identseteks.

Nii et meie valem =VLOOKUP(40, A2:B15, 2) otsib lahtrites A2 kuni A15 väärtust "40", kuna A on tabeli A2:B15 esimene veerg.

1. veeru_number - tabeli veeru number, millest tuleks tagastada vastava rea ​​väärtus.

Määratud tabeli vasakpoolseim veerg on 1, teine ​​veerg on 2, kolmas on 3 jne.

4. intervall_vaade määrab, kas otsite täpset vastet (FALSE) või ligikaudset vastet (TRUE või välja jäetud). See viimane parameeter on valikuline, kuid väga oluline.

VLOOKUP funktsioon Exceli näidetes

Vaatame nüüd mõningaid kasutusjuhtumeid VLOOKUP funktsioonid tõeliste andmete jaoks.

VLOOKUP funktsioon erinevatel lehtedel

Praktikas kasutatakse VLOOKUP-i valemeid ühelt lehelt andmete otsimiseks harva. Enamasti peate otsima ja hankima asjakohased andmed teiselt lehelt.

To kasutage funktsiooni VLOOKUP teiselt Exceli lehelt tuleb tabeli argumendis enne lahtrite vahemikku sisestada töölehe nimi ja hüüumärk, näiteks =VLOOKUP(40;Sheet2!A2:B15,2). Valem näitab, et otsinguvahemik A2:B15 on lehel 2.

Loomulikult ei pea te lehe nime käsitsi sisestama. Alustage lihtsalt valemi tippimist ja kui tegemist on tabeli argumendiga, lülituge otsingu töölehele ja valige hiirega vahemik.

Alloleval pildil olev valem otsib töölehe "Hinnad" veerus A (otsinguvahemiku A2:B9 esimene veerg) lahtris A2 ("Toode 3") olevat teksti:

VLOOKUP(A2,hinnad!$A$2:$B$8,2,FALSE)

VLOOKUP funktsioon Excelis - VLOOKUP funktsioon erinevatel lehtedel

Nimega vahemiku või tabeli kasutamine VLOOKUP-i valemites

Kui kavatsete kasutada sama otsinguvahemikku mitmes VLOOKUP-i valemis, saate sellele nime luua ja sisestada otse VLOOKUP-i valemi tabeliargumendis.

Nimega vahemiku loomiseks valige lihtsalt lahtrid ja sisestage suvaline nimi paneelist Valem vasakul asuvasse väljale Nimi.

Funktsioon VLOOKUP Excelis – vahemiku nimetamine

Nüüd saate toote 1 hinna saamiseks kirjutada järgmise VLOOKUP valemi:

VLOOKUP("Toode 1"; Tooted;2)

VLOOKUP funktsioon Excelis – vahemiku nimega funktsiooni VLOOKUP näide

Enamik Exceli vahemiku nimesid rakendub kogu töövihikule, nii et te ei pea määrama töölehe nime, isegi kui teie otsinguvahemik asub teisel töölehel. Sellised valemid on palju arusaadavad. Samuti võib nimega vahemike kasutamine olla hea alternatiiv lahtritele. Kuna nimega vahemik ei muutu valemi teistesse lahtritesse kopeerimisel, võite olla kindel, et teie otsinguvahemik jääb alati õigeks.

Kui olete teisendanud lahtrivahemiku täisfunktsionaalseks Exceli tabeliks (vahekaart Lisa -> tabel), saate valida hiirega otsinguvahemiku ja Microsoft Excel lisab veergude nimed või tabeli nimed automaatselt valemile. :

VLOOKUP funktsioon Excelis – VLOOKUP funktsiooni näide koos tabeli nimega

Täielik valem võib välja näha umbes selline:

VLOOKUP("Toode 1";Tabel 6[[Toode]:[Hind]];2)

või isegi =VLOOKUP("Toode 1";Tabel6;2).

Sarnaselt nimega vahemikele on veergude nimed püsivad ja lahtriviited ei muutu olenemata sellest, kuhu VLOOKUP valem kopeeritakse.

VLOOKUP funktsioon mitme tingimusega

Mõelgem VLOOKUP funktsiooni näide mitme tingimusega. Meil on järgmised sisendandmed:

VLOOKUP funktsioon Excelis – lähteandmete tabel

Oletame, et peame kasutama VLOOKUP funktsioon mitme tingimusega. Näiteks toote hinna otsimiseks kahe kriteeriumi järgi: toote nimetus ja selle tüüp.

1. Selleks, et kasutada VLOOKUP funktsioon mitme tingimusega alguses on vaja sisestada täiendav veerg, mis salvestab teabe koos toote nimetuse ja tüübiga.

Seega sisestage lehele "Hinnad" veerg ja sisestage lahtrisse A2 järgmine valem:

Selle valemi abil saame veergude "Toode" ja "Tüüp" väärtuse. Täitke kõik lahtrid.

Nüüd näeb otsingutabel välja selline:

Funktsioon VLOOKUP Excelis – abiveeru lisamine

1. Nüüd kirjutame lehel "Müük" lahtrisse C2 järgmise VLOOKUP-i valemi:

VLOOKUP (A2 hinnad! $ A $ 1: $ D $ 8,4, FALSE)

Täidame ülejäänud lahtrid ja selle tulemusel saame iga toote hinnad vastavalt tüübile:

VLOOKUP-i funktsioon Excelis – VLOOKUP-i näide mitme tingimusega

Nüüd vaatame seda Funktsiooni VLOOKUP vead.

Miks funktsioon VLOOKUP ei tööta?

Artikli selles osas vaatleme, miks VLOOKUP funktsioon ei tööta ja võimalik Funktsiooni VLOOKUP vead.

Vea tüüp	Põhjus	Lahendus
	Otsitava veeru asukoht on vale	Tabeli veerg, milles otsitakse, PEAB olema kõige vasakpoolsem. Liigutage otsitav veerg tabeli vasakusse serva. Või looge tabeli vasakus servas täiendav duplikaatveerg.
	Tabelivahemik pole fikseeritud	Kui esimest väärtust kuvati õigesti ja pärast VLOOKUP valemi venitamist ilmneb mõnes lahtris tõrge #N/A, siis tabelivahemik ei ole fikseeritud. Kasutage ($) tabelivahemiku lukustamiseks, nii et valem kasutaks täitmisel sama vahemikku. Või kasutada
	Täpset vastet ei leitud (kui sees aeglustatud vaatamine otsi täpset valitud väärtust (0)
	IN aeglustatud vaatamine sooritatakse lähima väärtuse (1) otsing ja tabelit, mille järgi otsitakse, ei sorteerita.	Sorteeri tabeli esimene veerg nimede kasvavas järjekorras.
		Kasutage funktsiooni TYPE või SPACE.
	Veeru numbri väärtus ületab tabeli veergude arvu	Kontrollige tagastatavat väärtust sisaldava veeru numbrit.
	Valemist puuduvad tsitaadid	Kui kasutate kui soovitud väärtust mitte lahtri link, vaid tekst, siis tuleb see panna jutumärkidesse. Näiteks: VLOOKUP("Toode 1"; Hinnad!$A$2:$B$8,2,0)

Loodan, et isegi nüüd mannekeenide jaoks VLOOKUP funktsioon Excelis saab selgeks.

Batjanov Denis Külalisautorina räägib ta selles postituses, kuidas ühest Exceli tabelist andmeid leida ja teise tabelisse ekstraktida, ning paljastab ka kõik vertikaalvaate funktsiooni saladused.

Funktsiooni COLUMN kasutamine ekstraheerimisveeru määramiseks

Kui tabelil, kuhu VLOOKUP-i abil andmeid hankite, on sama struktuur kui otsingutabelil, kuid see sisaldab lihtsalt vähem ridu, saab VLOOKUP kasutada funktsiooni COLUMN(), et arvutada automaatselt välja tootavate veergude numbrid. Sel juhul on kõik VLOOKUP valemid samad (kohandatud esimese parameetri järgi, mis muutub automaatselt)! Pange tähele, et esimesel parameetril on veeru absoluutne koordinaat.

Liitvõtme loomine kasutades &»|»&

Kui on vaja otsida korraga mitmest veerust, siis on vaja otsingu jaoks luua liitvõti. Kui tagastatav väärtus poleks tekst (nagu väljal “Kood”), vaid number, siis sobiks selleks mugavam SUMIFS-valem ja liitveeru võtit poleks üldse vaja.

See on minu esimene Lifehackeri artikkel. Kui teile see meeldis, kutsun teid külastama oma veebisaiti ja hea meelega loen kommentaarides ka teie VLOOKUP-funktsiooni ja muu sarnase kasutamise saladusi. Aitäh. :)

Kokkuvõtliku tabeliga töötamine hõlmab väärtuste tõmbamist teistest tabelitest. Kui tabeleid on palju, võtab käsitsi teisaldamine tohutult aega ja kui andmeid pidevalt uuendatakse, on see Sisyphose ülesanne. Õnneks on olemas funktsioon VLOOKUP, mis pakub võimalust andmeid automaatselt hankida. Vaatame selle funktsiooni toimimise konkreetseid näiteid.

Funktsiooni VLOOKUP nimi tähendab "vertikaalset vaatamise funktsiooni". Inglise keeles on selle nimi VLOOKUP. See funktsioon otsib andmeid uuritava vahemiku vasakpoolsest veerust ja tagastab seejärel saadud väärtuse määratud lahtris. Lihtsamalt öeldes võimaldab VLOOKUP teil väärtusi ühe tabeli lahtrist teise tabelisse ümber korraldada. Uurime, kuidas kasutada Excelis funktsiooni VLOOKUP.

Näide VLOOKUPi kasutamisest

Vaatame konkreetse näite abil, kuidas funktsioon VLOOKUP töötab.

Meil on kaks lauda. Esimene neist on ostutabel, kuhu on paigutatud toiduainete nimetused. Järgmises veerus pärast nime on ostmist vajava toote koguse väärtus. Edasi tuleb hind. Ja viimases veerus - konkreetse toote ostmise kogumaksumus, mis arvutatakse lahtrisse juba sisestatud valemi abil koguse korrutamiseks hinnaga. Kuid me peame lihtsalt hinda tõstma, kasutades funktsiooni VLOOKUP kõrvalolevast tabelist, mis on hinnakiri.

Nagu näha, on kartuli hind tabelisse lisatud hinnakirjast. Et mitte läbida nii keerulist protseduuri teiste tootenimetustega, seisame lihtsalt täidetud lahtri alumises paremas nurgas, nii et tekib rist. Me tõmbame selle risti tabeli alumisse ossa.

Seega tõmbasime funktsiooni VLOOKUP abil ühest tabelist teise kõik vajalikud andmed.

Nagu näete, pole funktsioon VLOOKUP nii keeruline, kui esmapilgul tundub. Selle kasutamise mõistmine pole kuigi keeruline, kuid selle tööriista valdamine säästab tabelitega töötamisel palju aega.

Microsoft Excelil on palju erinevaid funktsioone, mis muudavad kasutaja töö oluliselt lihtsamaks ja selles artiklis räägime ühest neist. Selle nimi on VLOOKUP ja kui inglise keeles, siis VLOOKUP.

Funktsioon VLOOKUP edastab väärtused ühest tabelist teise tabelisse. Lubage mul selgitada üksikasjalikumalt - esimeses tabelis valite väärtuse, mille peate leidma teise vasakpoolses veerus. Kui vasteid leidub, kannab VLOOKUP väärtuse selles reas määratud veerust esimesse tabelisse. Kuigi määratlus on veidi segane, pole funktsiooni kasutamine nii keeruline. Vaatame mõnda näidet.

Kuna funktsiooni kasutatakse kõige sagedamini veeru täitmiseks hinnaga, mis on märgitud üksikutes hinnakirjades, võtame järgmise näite. Näiteks on olemas tabel puuviljadega (oranž), kus igaühe kohta on märgitud, mitu kilogrammi soovime tellida. Vastavalt sellele peate iga puuvilja jaoks tarnija antud hinnakirjast (roheline) üles kirjutama hinna ja seejärel arvutama, kui palju ost maksab. Iga kauba vaatamine ja andmete edastamine on keeruline, eriti kui kaupadega ridu on tuhandeid, seetõttu kasutame VLOOKUP-i.

Funktsiooniviisardi akna avamiseks klõpsake veeru Hind esimese tabeli ülemist lahtrit ja seejärel valemiriba nuppu "fx".

Järgmisena kirjutame argumendid pakutud väljadele.

Pange väljale kaldkiri "Otsingu_väärtus" ja tõstke esimeses tabelis esile väärtus, mida otsime. Mul on see õun.

Real "Tabel" peate valima selle, kust andmed võetakse - ärge valige päist. Pange tähele, et kõige vasakpoolsem veerg peaks koosnema väärtustest, mida otsime. See tähendab, et mul on vaja õuna ja muid puuvilju, mis tähendab, et nende loend peaks olema valitud ala vasakus veerus.

Et pärast valemi kirjutamist ja kogu veeru venitamist valitud vahemik allapoole ei liiguks, peame lingid absoluutseks muutma: valige väljal olevad andmed ja vajutage "F4". Nüüd on lahtrite aadress muutunud absoluutseks, neile on lisatud märk “$” ja vahemik ei nihku.

Kui veeru number on, sisestage teises tabelis vastav number veergu, kust soovite andmeid edastada. Minu hinnakiri koosneb puuviljast ja hinnast, vajan teist, seega panen numbri “2”.

IN "Aegvahe" Kirjutame "FALSE" - kui peate otsima täpseid vasteid, või "TRUE" - kui väärtused võivad olla ligikaudsed. Meie näite jaoks valime esimese. Kui te sellel väljal midagi ei määra, valitakse vaikimisi teine. Seejärel klõpsake "OK".

Siin pöörake tähelepanu järgmisele: kui töötate numbritega ja määrate "True", siis tuleb teine ​​tabel (see on meie hinnakiri) järjestada kasvavas järjekorras. Näiteks 5.25 otsides leitakse 5.27 ja võetakse sellelt realt andmed, kuigi võib olla ka number alla 5.2599 - aga valem ei hakka kaugemale vaatama, sest arvab, et allolevad numbrid on ainult suuremad .

Kuidas VLOOKUP töötab? See võtab soovitud väärtuse (õun) ja otsib selle määratud vahemiku (puuviljade loendi) vasakpoolsemast veerust. Sobivuse korral võetakse väärtus samast reast, ainult argumentides (2) määratud veerust ja kantakse üle meile vajalikku lahtrisse (C2). Valem näeb välja selline:

VLOOKUP (A2, $G$2:$H$12,2,FALSE)

Nüüd saate seda paremat alumist nurka tõmmates vajaliku arvu ridu alla venitada.

Kõik hinnad on vastavalt puuviljade nimetustele üle kantud hinnakirjast hanketabelisse.

Kui sul on esimeses tabelis toodete nimetused, mida hinnakirjas ei ole, minu jaoks on need köögiviljad, siis nende punktide vastas annab VLOOKUP valem vea #N/A.

Kui lisate töölehel veerge, muutuvad funktsiooni tabeli argumendi andmed automaatselt. Näites on hind nihutatud 2 veergu võrra paremale. Valime suvalise valemiga lahtri ja vaatame, et $G$2:$H$12 asemel on see nüüd $I$2:$J$14 .

Nüüd mõtleme välja, kuidas töötada Exceli funktsiooniga VLOOKUP ja ripploenditega. Kõigepealt peate koostama ripploendi. Valige lahter, avage ülaosas "Andmed" ja klõpsake nuppu "Andmete kontrollimine".

Avanevas aknas "Andmetüüp" on "Loend", allpool märgime lähteala - need on puuviljade nimed, see tähendab veerg, mis asub nii esimeses kui ka teises tabelis. Klõpsake "OK".

Valin F2 ja sisestan funktsiooni VLOOKUP. Esimene argument on tehtud loend (F1).

Teine on tarnijate tabel näidatud hindadega. Pidage meeles, et siin peab vasak veerg vastama ripploendi andmetele.

Selgus umbes nagu otsing: valime puuvilja ja VPR leiab selle hinna hinnakirjast.

Kui teil olid samad hinnad ja siis andsid tarnijad teile uue nimekirja, siis peate kuidagi nägema, mis on muutunud. Selle käsitsi tegemine võtab kaua aega, kuid kõnealuse funktsiooni abil saab kõik väga kiiresti tehtud. Peate lisama ühe veeru ja kandma sinna uued väärtused ning seejärel lihtsalt andmeid võrdlema.

Klõpsake veerus D mis tahes lahtrit ja sisestage uus.

Ma nimetasin seda uueks hinnaks - uued hinnad on siin ja vanad hinnad kuvatakse vasakpoolses veerus. Minu uus tabel asub teisel lehel, et saaksite aru, kuidas VLOOKUP-i kasutada, kui andmed asuvad erinevatel lehtedel. Lisatud veerus valige esimene tühi lahter.

Sisestame funktsiooni ja täpsustame argumendid. Esiteks, mida me otsime, näites õuna (A2). Uuest hinnakirjast vahemiku valimiseks asetage kursor väljale "Tabel" ja minge soovitud lehele, mul on "Sheet1".

Valige hiirega vajalikud veerud ja read, ilma pealkirjadeta.

Järgmisena teeme absoluutsed viited lahtritele: “Sheet1!$A$2:$B$12” . Valige rida ja vajutage "F4", et lisada lahtri aadressidele dollarimärk. Märgime veeru (2) ja kirjutame "FALSE".

Lõpus klõpsake nuppu "OK".

Nüüd asuvad kaks veergu uue ja vana hinnaga kõrvuti ning saate teha kas visuaalset võrdlust või teatud valemeid rakendades või tingimuslikku vormindamist.

Loodan, et olen esitanud samm-sammult juhised funktsiooni VLOOKUP kasutamiseks ja rakendamiseks Excelis ning nüüd on kõik teile selge.

Hinda seda artiklit:

(13 hinnangud, keskmine: 5,00 5-st)

Veebihaldur. Kõrgharidus infoturbe alal. Enamiku artiklite ja arvutioskuse tundide autor

Me kõik teame, kui kasulik see on. Tõenäoliselt tehakse pooled kõigist Exceli toimingutest selle abil. Sellel funktsioonil on aga mitmeid piiranguid. Näiteks VLOOKUP vaatab ainult tabeli vasakpoolseimat veergu või otsib ainult ühte tingimust. Aga mis siis, kui peame tagastama väärtuse, mis vastab kahele tingimusele. Sel juhul peame kasutama mõningaid nippe. Nendest nippidest räägime tänases artiklis.

Niisiis, vaatleme nelja võimalust asendusfunktsiooni loomiseks kahe tingimusega:

1. Funktsiooni SUMPRODUCT kasutamine

Noh, alustame kõige lihtsamast.

Täiendava veeru kasutamine

Enamikul juhtudel muutuvad keerulised probleemid lihtsamaks ja paremini hallatavaks, kui jagate need väikesteks tükkideks. Sama kehtib ka Excelis valemite koostamisel.

Vaatame klassikalist näidet. Meil on tabel müügiga kuude ja linnade lõikes. Ja me peame kindlaks määrama müügiväärtuse, mis vastab kahele tingimusele: kuu - veebruar ja linn - Samara.

Funktsiooni VLOOKUP kasutamine selle klassikalisel kujul meid ei aita, kuna see saab tagastada ainult väärtuse, mis vastab ainult ühele tingimusele. Sellest olukorrast aitab meil välja tulla täiendav veerg, milles ühendame veergude Kuu ja linna väärtused. Selleks kirjutage lahtrisse A2 valem =B2&C2 ja laiendage see valem lahtrisse A13. Nüüd saame vajaliku väärtuse tagastamiseks kasutada veeru A väärtusi. Kirjutame lahtrisse G3 valemi:

VLOOKUP(G1A2:D13;4;0)

See valem ühendab lahtrite G1 ja G2 kaks tingimust ühte ritta ja vaatab seda veerus A. Kui soovitud tingimus on leitud, tagastab valem väärtuse tabeli A1:D13 neljandast veerust, s.t. veerg Müük.



Funktsiooni SELECT kasutamine uue otsingutabeli loomiseks

Kui mingil põhjusel ei ole lisaveeru kasutamine meie jaoks võimalik, võime kasutada .

Funktsiooni SELECT kasutamine hõlmab uue tabeli loomist, et vaadata veeru väärtusi Kuu Ja Linn juba liidetud. Meie valem näeb välja selline:

VLOOKUP(G1SELECT((1,2),B2:B13&C2:C13,D2:D13),2,0)

Selle valemi põhipunkt on osa VALIK((1;2);B2:B13&C2:C13;D2:D13), mis teeb kahte asja:

1. Kombineerib veergude väärtused Kuu Ja Linnühes massiivis: JanMoscow, FebMoscow...
2. Ühendab kaks massiivi kaheveeruliseks tabeliks.

Selle funktsiooni tulemuseks on tabel, mis näeb välja järgmine:



Nüüd on valem muutunud selgemaks.

TÄHTIS. Kuna kasutasime massiivi valemit, vajutage valemi sisestamise lõpetamisel klahvikombinatsiooni Ctrl+Shift+Enter, et anda programmile teada meie kavatsustest. Pärast selle klahvikombinatsiooni vajutamist installib programm automaatselt lokkis sulgud valemi algusesse ja lõppu.

Funktsioonide INDEX ja MATCH kasutamine

Kolmas meetod, mida me käsitleme, hõlmab samuti massiivivalemi kasutamist ja kasutab funktsioone INDEX ja SEARCH.

Valem näeb välja selline.

INDEKS(D2:D13,MATCH(1,(B2:B13=G1)*(C2:C13=G2),0))

Vaatame, mida selle valemi iga osa teeb.

Esiteks kaaluge funktsiooni MATCH(1;(B2:B13=G1)*(C2:C13=G2);0). Sel juhul võrreldakse lahtri G1 väärtust järjestikku iga lahtri väärtusega vahemikus B2:B13 ja tagastab väärtuse TRUE, kui väärtused ühtivad, ja FALSE, kui need ei kattu. Sama võrdlus tehakse lahtri G2 väärtuse ja vahemikuga C2:C13. Järgmisena võrdleme mõlemat massiivi, mis koosnevad TRUE ja FALSE. Kombinatsioon TRUE * TRUE annab meile tulemuse 1 (TRUE). Vaatame allolevat pilti, mis aitab tööpõhimõtet selgemalt selgitada.



Nüüd saame öelda, kus asub string, mis vastab mõlemale tingimusele. Funktsioon MATCH leiab tulemuste massiivist 1 positsiooni ja tagastab 6, kuna 1 esineb kuuendas reas. Järgmisena tagastab funktsioon INDEX vahemiku D2:D13 kuuenda rea ​​väärtuse.

SUMPRODUCT kasutamine

Üks võimsamaid Exceli valemeid. Mul on sellele valemile pühendatud isegi eraldi artikkel. Meie neljas viis mitme tingimuse kasutamiseks on kirjutada valem funktsiooniga SUMPRODUCT. Ja see näeb välja selline:

SUMMADE((B2:B13=G1)*(C2:C13=G2);D2:D13)

Selle valemi tööpõhimõte on sarnane eelmise lähenemisviisi tööpõhimõttega. Luuakse virtuaalne tabel, milles võrreldakse lahtrite G1 ja G2 väärtusi vastavalt vahemikega B2:B13 ja C2:C13. Järgmisena võrreldakse mõlemat massiivi ja saadakse ühtede ja nullide massiiv, kus üks määratakse reale, milles mõlemad tingimused kattuvad. Järgmisena korrutatakse see virtuaalne massiiv vahemikuga D2:D13. Kuna meie virtuaalmassiivis on kuuendas reas ainult üks 1, tagastab valem tulemuse 189.

See funktsioon ei tööta, kui tekstiväärtused on vahemikus D2:D13.

Selle valemi toimimise mõistmiseks soovitan lugeda artiklit funktsiooni SUMPROIZ kohta.

TULEMUS

Niisiis, millist meetodit peaksite kasutama? Kuigi need kõik töötavad usaldusväärselt, eelistan ma esimest meetodit. Oma igapäevatöös eelistan töötada failidega, mis on kergesti mõistetavad ja muudetavad. Mõlemad nõuded vastavad esimese lähenemisviisi tingimustele.

Valemite paremaks mõistmiseks võite neid vaadata tänases artiklis.