Çima INDEX û MATCH ji VLOOKUP di Excel de çêtir in

Me berê ji destpêkeran re rave kir ku meriv çawa fonksiyonên bingehîn ên VLOOKUP-ê bikar tîne (bi îngilîzî VLOOKUP, kurteya "fonksîyona lêgerîna vertîkal" radiweste). Û bikarhênerên bi tecrûbe gelek formulên tevlihevtir hatin nîşandan.

Û di vê gotarê de em ê hewl bidin ku agahdarî li ser rêbazek din a xebata lêgerîna vertîkal bidin.

Dibe ku hûn bipirsin: "Çima ev pêdivî ye?". Û ev hewce ye ku ji bo hemî rêbazên lêgerîna lêgerînê nîşan bide. Wekî din, gelek qedexeyên VLOOKUP bi gelemperî rê li ber bidestxistina encama xwestinê digirin. Di vî warî de, INDEX ( ) MATCH ( ) pir bikêrtir û cihêrengtir e, û di heman demê de kêmasiyên wan jî hene.

Bingehîn INDEX MATCH

Ji ber ku armanca vê rêbernameyê ev e ku nîşan bide ka ev taybetmendî çiqas baş e, em Ka em li agahdariya bingehîn li ser prensîbên xebata wê binêrin. Û em ê mînakan nîşan bidin, û her weha bifikirin ka çima, ew ji VLOOKUP () çêtir e.

INDEX Fonksiyon Syntax û Bikaranîna

Ev fonksiyon di dîtina nirxa xwestinê de di nav deverên lêgerînê yên diyarkirî de li ser bingeha stûn an jimareya rêzê dibe alîkar. Syntax:

=INDEX(array, hejmara rêz, hejmara stûnê):

• array - qada ku lê lêgerîn dê pêk were;
• jimareya rêzê - hejmara rêza ku di rêzika diyarkirî de tê gerîn. Ger hejmara rêzê nenas be, divê hejmara stûnê were diyar kirin;
• hejmara stûnê - hejmara stûna ku di rêzika diyarkirî de tê dîtin. Ger nirx nenas be, jimareyek rêzek pêdivî ye.

Mînakek formula hêsan:

=INDEX(A1:S10,2,3)

Fonksiyon dê di navbera A1 heta C10 de bigere. Hejmar nîşan dide ku ji kîjan rêz (2) û stûna (3) nirxa xwestinê nîşan bide. Encam dê hucreya C2 be.

Pir sade, rast? Lê gava ku hûn bi belgeyên rastîn re dixebitin, ne mimkûn e ku hûn di derheqê hejmarên stûnê an hucreyan de agahdar bin. Ji bo vê fonksiyona MATCH() ye.

Fonksiyon MATCH Hevoksazî û Bikaranîn

Fonksiyona MATCH() li nirxa xwestî digere û di qada lêgerînê ya diyarkirî de jimareya wê ya nêzîk nîşan dide.

Hevoksaziya searchpos() wiha xuya dike:

=MATCH (nirxa lêgerînê, rêza lêgerînê, celebê hevberdanê)

• nirxa lêgerînê - hejmar an nivîsa ku tê dîtin;
• array lêgerîn - qada ku lê lêgerîn dê pêk were;
• Tîpa lihevhatinê - diyar dike ka meriv li nirxa rastîn an nirxên herî nêzê wê bigere:
  • 1 (an jî nirxek nehatiye diyarkirin) - nirxa herî mezin vedigerîne ku ji nirxa ku hatî destnîşan kirin wekhev an kêmtir e;
  • 0 - lihevhatinek rastîn bi nirxa lêgerînê re nîşan dide. Di kombînasyona INDEX() MATCH() de hûn ê hema hema her gav hewceyê berhevokek rastîn bikin, ji ber vê yekê em 0 dinivîsin;
  • -1 - Nirxa herî piçûk a ku ji nirxa ku di formulê de hatî destnîşan kirin mezintir an wekhev e nîşan dide. Rêzkirin bi rêza daketinê tê kirin.

Mînakî, di rêza B1: B3 de New York, Parîs, London têne tomar kirin. Formula jêrîn dê hejmara 3 nîşan bide ji ber ku London di navnîşê de sêyemîn e:

=EXPOSE(London,B1:B3,0)

Meriv çawa bi fonksiyona INDEX MATCH re dixebite 

We belkî jixwe dest bi fêmkirina prensîba ku xebata hevpar a van fonksiyonan tê çêkirin kiriye. Bi kurtasî, paşê INDEX() di nav rêz û stûnên diyarkirî de li nirxa xwestî digere. Û MATCH() hejmarên van nirxan nîşan dide:

=INDEX(stûna ku nirx jê tê vegerandin, MATCH(nirxa lêgerînê, stûna ku tê de bigere, 0))

Hîn jî dijwariyek heye ku hûn fêm bikin ka ew çawa dixebite? Dibe ku mînakek çêtir rave bike. Bifikirin ku we navnîşek paytextên cîhanê û nifûsa wan heye:

Ji bo ku em mezinahiya nifûsa paytextek diyarkirî, mînakî paytexta Japonya, bibînin, em formula jêrîn bikar tînin:

=INDEX(C2:C10, MATCH(Japonya, A2:A10,0))

Daxûyanî:

• Fonksiyona MATCH() li nirxê digere - "Japonya" di rêza A2: A10 de û hejmara 3 vedigerîne, ji ber ku Japon di navnîşê de nirxa sêyemîn e. 
• Ev hejmar diçehejmara rêzê” di formula INDEX() de û ji fonksiyonê re dibêje ku nirxek ji vê rêzê çap bike.

Ji ber vê yekê formula jorîn dibe formula standard INDEX(C2:C10,3). Formul ji hucreyên C2 heya C10 digere û daneyan ji şaneya sêyemîn a vê rêzê, ango C4 vedigerîne, ji ber ku hejmartin ji rêza duyemîn dest pê dike.

Ma hûn naxwazin navê bajêr di formulê de destnîşan bikin? Dûv re wê di her hucreyê de binivîsin, bibêjin F1, û wê di formula MATCH () de wekî referans bikar bînin. Û hûn bi formula lêgerîna dînamîk re bi dawî bibin:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Giring! Hejmara rêzikên di nav de rêzî INDEX() divê wekî hejmara rêzên tê de be array hesibandin di MATCH (), wekî din hûn ê encamek xelet bistînin.

Deqeyekê bisekinin, çima tenê formula VLOOKUP () bikar neynin?

=VLOOKUP(F1, A2:C10, 3, Derew)

 Wateya wendakirina wextê ji bo fêhmkirina van hemî tevliheviyên INDEX MATCH çi ye?

Di vê rewşê de, ne girîng e ku kîjan fonksiyonê bikar bînin. Ev tenê mînakek e ku meriv fêm bike ka fonksiyonên INDEX () û MATCH () çawa bi hev re dixebitin. Nimûneyên din dê nîşan bidin ka van fonksiyonan di rewşên ku VLOOKUP bêhêz e de dikarin çi bikin. 

INDEX MATCH an jî VLOOKUP

Dema ku biryar didin ka kîjan formula lêgerînê bikar bînin, pir kes qebûl dikin ku INDEX () û MATCH () ji VLOOKUP-ê pir bilindtir in. Lêbelê, gelek kes hîn jî VLOOKUP (). Ya yekem, VLOOKUP () hêsantir e, û ya duyemîn jî, bikarhêner hemî avantajên xebata bi INDEX () û MATCH () re bi tevahî fam nakin. Bêyî vê zanînê, kes dê razî nebe ku dema xwe bi xwendina pergalek tevlihev derbas bike.

Li vir avantajên sereke yên INDEX () û MATCH () li ser VLOOKUP ():

• Ji rastê ber bi çepê ve bigerin. VLOOKUP() nikare ji rast ber bi çepê bigere, ji ber vê yekê nirxên ku hûn lê digerin divê her gav di stûnên herî çepê yên tabloyê de bin. Lê INDEX () û MATCH () dikarin vê yekê bêyî pirsgirêk bikin. Ev gotar dê ji we re vebêje ka ew di pratîkê de çawa xuya dike: meriv çawa nirxa xwestî li milê çepê bibîne.

1. Zêdekirina ewle an rakirina stûnan. Formula VLOOKUP() dema ku stûnan jêkirin an lê zêde bike encamên nerast nîşan dide ji ber ku VLOOKUP() ji bo serfiraziyê pêdivî bi jimareya stûnê ya rastîn heye. Bi xwezayî, dema ku stûn têne zêdekirin an jêbirin, hejmara wan jî diguhere. 

Û di formulên INDEX() û MATCH() de, rêzek stûnan têne diyar kirin, ne stûnên kesane. Wekî encamek, hûn dikarin bi ewlehî stûnan lê zêde bikin û jêbirin bêyî ku her carê formula nûve bikin.

1. Li ser cildên lêgerînê ti sînor tune. Dema ku VLOOKUP() bikar bînin, divê hejmara giştî ya pîvanên lêgerînê ji 255 tîpan derbas nebe an hûn ê #VALUE bistînin! Ji ber vê yekê heke daneyên we hejmareke mezin ji karakteran vedihewîne, INDEX () û MATCH () vebijarka çêtirîn in.
2. Leza pêvajoyê ya bilind. Ger tabloyên we nisbeten piçûk in, wê hingê hûn ne mimkûn e ku hûn cûdahiyek bibînin. Lê, heke tablo bi sedan an bi hezaran rêzan bigire, û li gorî vê yekê, bi sed û hezaran formula hene, INDEX () û MATCH () dê ji VLOOKUP () pir zûtir biqewimin. Rastî ev e ku Excel dê li şûna ku tevahiya tabloyê bişopîne, tenê stûnên ku di formulê de hatine destnîşan kirin pêvajoyê bike. 

Bandora performansê ya VLOOKUP () dê bi taybetî diyar bibe ger pelgeya weya xebatê hejmareke mezin formulên mîna VLOOKUP () û SUM () hebe. Kontrolên cihê yên fonksiyonên VLOOKUP() ji bo parskirina her nirxek di rêzek de hewce ne. Ji ber vê yekê Excel neçar e ku hejmareke mezin a agahdariyê pêvajoyê bike, û ev kar bi girîngî hêdî dike.

Nimûneyên Formula 

Me berê bikêrhatina van fonksiyonan fêhm kiriye, ji ber vê yekê em dikarin derbasî beşa herî balkêş bibin: sepana zanînê di pratîkê de.

Formula lêgerîna ji rastê ber bi çepê

Wekî ku berê jî behs kir, VLOOKUP nikare vê forma lêgerînê pêk bîne. Ji ber vê yekê, heke nirxên xwestî ne di stûna herî çepê de ne, VLOOKUP () dê encamek dernekeve. Fonksiyonên INDEX () û MATCH () pirtirkêmtir in, û cîhê nirxan ji bo xebitandina wan rolek mezin naleyze.

Mînak, em ê stûnek rêzê li milê çepê yê tabloya xwe zêde bikin û hewl bidin ku bibînin ka paytexta Welatê Me ji aliyê nifûsê ve di kîjan rêzê de ye.

Di hucreya G1 de, em nirxa ku were dîtin dinivîsin, û dûv re formula jêrîn bikar tînin da ku di navberê de C1:C10 bigerin û nirxa têkildar ji A2:A10 vegerînin:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Derhal. Ger hûn plan dikin ku vê formulê ji bo gelek hucreyan bikar bînin, pê ewle bin ku hûn bi karanîna navnîşana bêkêmasî rêzan rast bikin (mînak, $A$2: $A$10 û $C$2: 4C$10).

INDEX MORE DERXISTIN ZÊDE DIKE  ku di stûn û rêzan de bigerin

Di mînakên jorîn de, me van fonksiyonan wekî şûna VLOOKUP () bikar aniye da ku nirxan ji rêzek rêzikan vegerîne. Lê heke hûn hewce ne ku matrixek an lêgerînek du-alî bikin?

Ew tevlihev xuya dike, lê formula ji bo hesabên weha mîna formula standard INDEX () MATCH () ye, bi tenê cûdahiyek: formula MATCH () divê du caran were bikar anîn. Cara yekem ji bo bidestxistina jimareya rêzê, û cara duyemîn ku jimara stûnê digire:

=INDEX(array, MATCH (nirxa lêgerîna vertîkal, stûna lêgerînê, 0), MATCH (nirxa lêgerîna asoyî, rêza lêgerînê, 0))

Ka em li tabloya jêrîn binêrin û hewl bidin ku formula çêbikin INDEX() EXPRESS() EXPRESS() ji bo ku ji bo salek hilbijartî li welatek taybetî demografîk nîşan bide.

Welatê mebest di hucreya G1 de ye (lêgerîna vertîkal) û sala armanc di şaneya G2 de ye (lêgerîna asoyî). Formul dê wiha xuya bike:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Çawa ev formula dixebite

Mîna her formulên tevlihev ên din, bi veqetandina wan di hevkêşeyên kesane de têne fam kirin. Û hingê hûn dikarin fêm bikin ka her fonksiyonek kesane çi dike:

• MATCH(G1,A2:A11,0) - di rêza A1:A2 de li nirxek (G11) digere û hejmara vê nirxê nîşan dide, di rewşa me de ew 2 ye;
• SEARCH(G2,B1:D1,0) - li nirxek (G2) di rêza B1: D1 de digere. Di vê rewşê de, encam bû 3.

Hejmarên rêz û stûnên hatine dîtin di formula INDEX() de ji nirxa têkildar re têne şandin:

=INDEX(B2:D11,2,3)

Wekî encamek, nirxek me heye ku di hucreyek li hevberdana 2 rêz û 3 stûnên di rêza B2: D11 de ye. Û formula nirxa xwestî nîşan dide, ku di hucreya D3 de ye.

Bi INDEX û MATCH re li gorî şertên pirjimar bigerin

Ger we rêbernameya me ya VLOOKUP() xwendiye, belkî we gelek formulên lêgerînê ceribandiye. Lê ev rêbaza lêgerînê yek sînordariyek girîng heye - hewcedariya lê zêdekirina stûnek alîkar.

Lê nûçeyên baş ew e Bi INDEX() û MATCH() re hûn dikarin li gelek şertan bigerin bêyî ku hûn pelgeya xebata xwe biguherînin an biguhezînin.

Li vir formula lêgerîna pir-rewşa giştî ya ji bo INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Nîşe: divê ev formula bi kurteya klavyeyê re bi hev re were bikar anîn CTRL+SHIFT+ENTER.

Bifikirin ku hûn hewce ne ku nirxa ku hûn lê digerin li ser bingeha 2 şertan bibînin: Kirrîvan и Mal.

Ev formula jêrîn hewce dike:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

Di vê formulê de, C2: C10 rêza ku lê lêgerîn dê pêk were ye, F1 - ev rewş, A2: A10 - Rêjeya berhevkirina rewşê ye, F2 - şertê 2, V2: V10 - range ji bo berhevdana rewşa 2.

Ji bîr nekin ku di dawiya xebatê de bi formula re hevberdanê çap bikin CTRL+SHIFT+ENTER - Excel dê bixweber formulê bi pêlên çîçek ve bigire, wekî ku di nimûneyê de tê xuyang kirin:

Ger hûn nexwazin formulek array ji bo xebata xwe bikar bînin, wê hingê INDEX() din li formulê zêde bikin û ENTER bikirtînin, ew ê di nimûneyê de xuya bike:

Ev formula çawa dixebitin

Ev formula bi heman rengî wekî formula standard INDEX () MATCH () dixebite. Ji bo lêgerîna pir şert û mercan, hûn tenê çend şertên Derew û Rast diafirînin ku şert û mercên kesane yên rast û nerast temsîl dikin. Û dûv re ev şert li ser hemî hêmanên têkildar ên rêzê derbas dibin. Formul bi rêzdarî argumanên False û Rast vediguhezîne 0 û 1, û rêzek derdixe ku 1 nirxên lihevhatî yên ku di rêzê de hatine dîtin e. MATCH() dê nirxa yekem a ku bi 1-ê re têkildar dibîne bibîne û wê bi formula INDEX() re derbas bike. Û ew, di encamê de, dê ji stûna xwestî nirxa ku jixwe tê xwestin di rêza diyarkirî de vegerîne.

Formulek bêyî array bi kapasîteya INDEX() ve girêdayî ye ku wan bi xwe bi rê ve bibe. Di formulê de INDEX() ya duyemîn bi derewîn (0) re têkildar e, ji ber vê yekê ew tevahiya rêza bi wan nirxan re derbasî formula MATCH () dike. 

Ev ravekirineke pir dirêj a mantiqa li pişt vê formulê ye. Ji bo bêtir agahdarî gotara "MATCH INDEX bi gelek şert û mercan".

AVERAGE, MAX û MIN di INDEX û MATCH de

Excel ji bo dîtina navînî, herî zêde û hindiktirîn fonksiyonên xwe yên taybetî hene. Lê heke hûn dixwazin daneyên ji hucreya ku bi wan nirxan re têkildar in bistînin? Di vê rewşê de AVERAGE, MAX û MIN divê bi INDEX û MATCH re bêne bikar anîn.

INDEX MATCH û MAX

Ji bo ku di stûna D de nirxa herî mezin bibînin û wê di stûna C de nîşan bidin, formula bikar bînin: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH û MIN

Ji bo ku di stûna D de nirxa herî piçûk bibînin û wê di stûna C de nîşan bidin, formula jêrîn bikar bînin:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

LÊGERÎNÊN ENDEKS Û SERPENT

Ji bo ku di stûna D de nirxa navîn bibînin û vê nirxê di C de nîşan bidin:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Li gorî daneyên we çawa têne nivîsandin, argumana sêyemîn a MATCH () 1, 0, an -1 e:

• heke stûn bi rêza hilkişînê werin rêz kirin, 1 destnîşan bikin (wê demê formula dê nirxa herî zêde hesab bike, ku ji nirxa navîn kêmtir an wekhev e);
• heke celeb daketî be, wê hingê -1 (formûl dê nirxa herî kêm a ku ji navînî mezintir an wekhev e derxîne);
• heke rêzika lêgerînê nirxek ku tam bi navînî re wekhev e dihewîne, wê hingê wê bike 0. 

 Di mînaka me de, nifûs li gorî rêza daketinê tê rêz kirin, ji ber vê yekê em -1 danîn. Û encam Tokyo ye, ji ber ku nirxa nifûsê (13,189) herî nêzîk nirxa navîn (000) ye.

VLOOKUP() di heman demê de dikare hesabên weha pêk bîne, lê tenê wekî formula array: VLOOKUP bi AVERAGE, MIN û MAX.

INDEX MATCH û ESND / IFERROR

Dibe ku we berê jî dît ku heke formula nikaribe nirxa xwestî bibîne, ew xeletiyek derdixe # N / A. Hûn dikarin peyama xeletiya standard bi tiştek agahdartir biguhezînin. Mînakî, argumana di formula de bicîh bikin Di XNUMXth de:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Bi vê formulê, heke hûn daneyên ku di tabloyê de ne têkevin, form dê peyama diyarkirî bide we.

Heke hûn dixwazin hemî xeletiyan bigirin, wê hingê ji bilî Di XNUMXth de dikarin bikar bînin IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "Tiştek xelet derket!")

Lê ji bîr mekin ku bi vî rengî veşartina xeletiyan ne ramanek baş e, ji ber ku xeletiyên standard binpêkirinên di formula de radigihînin.

Em hêvî dikin ku we rêbernameya me ya karanîna fonksiyona INDEX MATCH() arîkar dît.