Contents
Vê dersa piçûk rave dike ka meriv çawa fonksiyonek çêdike VPR (VLOOKUP) hesas bi dozê, çend formulên din nîşan dide ku Excel dikare bi şêwazek hesas-doze bigere, û hêz û qelsiyên her fonksiyonê destnîşan dike.
Ez texmîn dikim ku her bikarhênerek Excel dizane ku kîjan fonksiyonê lêgerîna vertîkal dike. Rast e, ew fonksiyonek e VPR. Lêbelê, hindik kes pê dizanin VPR ne hesas e, ango tîpên biçûk û yên mezin ji bo wê wek hev in.
Li vir mînakek bilez heye ku bêkêmasî nîşan dide VPR qeydê nas bike. Bifikirin di hucreyekê de A1 nirxa "bill" û şaneyê dihewîne A2 - "Bill", formula:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… dê lêgerîna xwe li ser "bill" rawestîne ji ber ku ew nirx di navnîşê de yekem e, û nirxê ji hucreyê derxe B1.
Dûv re di vê gotarê de, ez ê nîşanî we bidim ka hûn çawa bikin VPR doza hesas. Wekî din, em ê çend fonksiyonên din fêr bibin ku dikarin li Excel-ê lêgerînên hesas-hesas bikin.
Em ê bi ya herî hêsan dest pê bikin - DÎTINÎ (BINÊRE) û SUMPRODUCT (SUMPRODUCT), ku, mixabin, çend sînorên girîng hene. Dûv re, em ê ji nêz ve li formula hinekî tevlihevtir binêrin INDEX+MAÇE (INDEX + MATCH), ku di her rewşê de û bi her databasê re bêkêmasî dixebite.
Fonksiyona VLOOKUP-ê hesas e
Wekî ku hûn jixwe dizanin, fonksiyona gelemperî VPR doz bêhesas e. Lêbelê, rêyek heye ku meriv wê hestiyar bike. Ji bo vê yekê, hûn hewce ne ku stûnek alîkar li tabloyê zêde bikin, wekî ku di mînaka jêrîn de tê xuyang kirin.
Bifikirin di stûnekê de B Nasnameyên hilberê (Tiştek) hene û hûn dixwazin bihayê hilberê û şîroveya têkildar ji stûnan derxînin C и D. Pirsgirêk ev e ku nasnameyan hem tîpên piçûk û hem jî yên mezin dihewîne. Ji bo nimûne, nirxên hucreyê B4 (001Tvci3u) û B5 (001Tvci3U) tenê di rewşa karaktera paşîn de cûda dibe, u и U herwiha.
Wekî ku hûn dikarin bifikirin, formula lêgerîna gelemperî
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
vegerin $ 90, ji ber ku nirx 001Tvci3u di nav rêza lêgerînê de berê ye 001Tvci3U. Lê ew ne ew e ku em hewce ne, ne wusa?
Ji bo lêgerîna bi fonksiyonek VPR di rewşa hesas a Excel de, hûn neçar in ku stûnek arîkar lê zêde bikin û hucreyên wê bi formula jêrîn dagirin (ku B stûna lêgerînê ye):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Ev formula nirxa xwestî di nav tîpên cihêreng de vediqetîne, li şûna her karakterê koda xwe digire (mînak, li şûna A li 65, li şûna a koda 97) û dûv re van kodan di nav rêzek yekta ya hejmaran de berhev dike.
Piştî wê, em fonksiyonek hêsan bikar tînin VPR ji bo lêgerîna hesas a dozê:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Operasyona rast ya fonksiyonê VPR hesasiya dozê bi du faktoran ve girêdayî ye:
- Divê stûna arîkar stûna herî çepê be di navberê de ku tê dîtin.
- Nirxa ku hûn lê digerin divê li şûna nirxa rastîn kodek karakterek hebe.
Meriv çawa fonksiyona CODE rast bikar tîne
Formula ku di nav hucreyên stûna alîkar de hatî danîn destnîşan dike ku hemî nirxên lêgerîna we xwedî heman hejmarê karakteran in. Heke ne, wê hingê hûn hewce ne ku hejmarên herî piçûk û herî mezin zanibin û wekî gelek taybetmendiyan lê zêde bikin IFERROR (IFERROR) ferqa di navbera nirxa lêgerînê ya herî kurt û dirêj de çend tîpan e.
Mînakî, heke nirxa lêgerînê ya herî kurt 3 tîp û ya herî dirêj 5 tîp e, vê formula bikar bînin:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
Ji bo fonksiyonê PSTR (MID) Hûn argumanên jêrîn pêşkêş dikin:
- Argumana 1mîn - nivîstok (nivis) referansa nivîsê an hucreyê ye ku karakterên ku têne derxistin vedihewîne (di rewşa me de ew B2 ye)
- Argumana 2mîn - start_num (start_position) pozîsyona yekem ji wan tîpan e ku tê derxistin. tu bikevî 1 di fonksiyona yekem de PSTR, 2 - di fonksiyona duyemîn de PSTR hwd.
- Argumana 3mîn - num_chars (hejmara_karakteran) - Hejmara tîpan diyar dike ku ji nivîsê derxe. Ji ber ku em her dem tenê 1 karakterek hewce ne, di hemî fonksiyonan de em dinivîsin 1.
JI Karî VPR Ji bo lêgerînên hesas ên dozê yên li Excel ne çareseriya çêtirîn e. Pêşîn, pêvekirina stûnek alîkar hewce ye. Ya duyemîn, formula karekî baş dike tenê heke danehev homojen be, an jî hejmara rastîn a tîpan di nirxên lêgerînê de were zanîn. Ger ev ne doza we be, çêtir e ku hûn yek ji çareseriyên ku em li jêr destnîşan dikin bikar bînin.
Fonksiyona LOOKUP ji bo lêgerîna hesas a dozê
Karî DÎTINÎ (LOOKUP) têkildar VPR, di heman demê de hevoksaziya wê destûrê dide lêgerînên hesas ên dozê bêyî ku stûnek alîkar lê zêde bike. Ji bo vê yekê, bikar bînin DÎTINÎ bi fonksiyonê ve girêdayî ye TAM (TAM).
Ger em daneyên ji mînaka berê (bê stûnek alîkar) bigirin, wê hingê formula jêrîn dê bi peywirê re mijûl bibe:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Formula di nav rêzê de digere A2:A7 lihevhatina rastîn bi nirxa hucreyê re F2 doza hesas e û nirxê ji stûna B ya heman rêzê vedigerîne.
Çawa VPRkarî DÎTINÎ Wekî ku hûn di dîmena jêrîn de dibînin, bi nivîs û nirxên hejmarî re wekhev dixebite:
Giring! Ji bo fonksiyonê DÎTINÎ rast hatî xebitandin, divê nirxên di stûna lêgerînê de bi rêza hilkişînê werin rêz kirin, ango ji piçûktir berbi herî mezin.
Bila ez bi kurtasî rave bikim ka fonksiyon çawa dixebite TAM di formula ku li jor hatî destnîşan kirin, ji ber ku ev xala sereke ye.
Karî TAM du nirxên nivîsê yên di argumanên 1-emîn û 2-an de berhev dike û heke ew tam yek bin, TRUE vedigerîne, heke ne wusa bin, FALSE vedigerîne. Ji bo me fonksiyonek girîng e TAM doza hesas.
Ka em bibînin ka formula me çawa dixebite BİXWÎNE + TEQM:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Karî TAM nirxa hucreyê dide ber hev F2 bi hemû hêmanên di stûnekê de A (A2: A7). Ger rasthevhatinek rast were dîtin RAST vedigerîne, wekî din DEREW.
- Ji ber ku hûn argumana fonksiyona yekem didin DÎTINÎ nirxa RAST, ew nirxa têkildar ji stûna diyarkirî derdixe (di rewşa me de, stûna B) tenê heke lihevhatinek tam were dîtin, hesas bi haletê.
Ez hêvî dikim ku ev ravekirin zelal bû û naha hûn ramana sereke fam dikin. Ger wusa be, wê hingê hûn ê bi fonksiyonên din ên ku em ê bêtir analîz bikin re nebin dijwariyek, ji ber ku. ew hemî li ser heman prensîbê dixebitin.
JI Daneyên di stûna lêgerînê de divê bi rêza hilkişînê werin rêz kirin.
SUMPRODUCT - nirxên nivîsê dibîne, bi hesasiyet, lê tenê hejmaran vedigerîne
Wekî ku we berê jî ji sernavê fêm kir, SUMPRODUCT (SUMPRODUCT) fonksiyonek din a Excel-ê ye ku dê ji we re bibe alîkar ku hûn lêgerînek hesas a dozê bikin, lê dê tenê nirxên hejmarî vegerîne. Ger ev vebijark ne li gorî we be, wê hingê hûn dikarin tavilê berbi pakêtê ve biçin INDEX+MAÇE, ku ji bo her rewşê û ji bo her cûreyên daneyê çareseriyek dide.
Pêşîn, bila ez bi kurtasî hevoksaziya vê fonksiyonê rave bikim, ev ê ji we re bibe alîkar ku hûn formula hesas-hesas a ku li pey tê çêtir fam bikin.
Karî SUMPRODUCT hêmanên rêzikên diyarkirî zêde dike û berhevoka encaman vedigerîne. Syntax wiha xuya dike:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Ji ber ku hewcedariya me bi lêgerînek hesas heye, em fonksiyonê bikar tînin TAM (DÎMK) ji mînaka berê wekî yek ji pirjimarkeran:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Wekî ku tê bîra we, TAM nirxa hucreyê dide ber hev F2 bi hemû hêmanên di stûnekê de A. Ger rasthevhatinek rast were dîtin RAST vedigerîne, wekî din DEREW. Di operasyonên matematîkî de, Excel wekî TRUE digire 1, û FALSE ji bo 0Berdewam SUMPRODUCT van hejmaran zêde dike û encaman berhev dike.
Sifir nayên jimartin ji ber ku dema pir dibin her tim didin 0. Werin em ji nêz ve binihêrin ka çi diqewime dema ku lihevhatinek rastîn di stûnekê de çêdibe A dît û vegeriya 1… Fonksiyon SUMPRODUCT hejmara di stûnê de zêde dike B on 1 û encamê vedigerîne - tam heman hejmar! Ev e ji ber ku encamên hilberên din sifir in, û ew bandorê li berhevoka encam nakin.
Mixabin fonksiyona SUMPRODUCT nikare bi nirx û tarîxên nivîsê re bixebite ji ber ku ew nayên zêde kirin. Di vê rewşê de, hûn ê peyamek çewtiyê bistînin #GIRANÎ! (#NIRX!) wekî di hucreyê de F4 di wêneya jêrîn de:
JI Tenê nirxên hejmarî vedigerîne.
INDEX + MATCH - lêgerîna hesas a dozê ji bo her celeb daneyê
Di dawiyê de, em nêzî formulek lêgerîna bêsînor û hesas a dozê ne ku bi her komek daneyê re dixebite.
Ev mînaka dawî tê, ne ji ber ku ya herî baş ji bo şîrînê tê hiştin, lê ji ber ku zanîna ku ji mînakên berê hatî bidestxistin dê ji we re bibe alîkar ku hûn formula hesas a dozê çêtir û zûtir fam bikin. INDEX+MAÇE (INDEX+MAÇE).
Wekî ku we guman texmîn kir, tevliheviya fonksiyonan PÊKIRÎ DERKETIN и NAVEROK di Excel de wekî alternatîfek maqûltir û bi hêztir tê bikar anîn VPR. Gotara Bikaranîna INDEX û MATCH li şûna VLOOKUP dê bi tevahî rave bike ka van fonksiyonan çawa bi hev re dixebitin.
Ez ê tenê xalên sereke dubare bikim:
- Karî PÊKIRÎ DERKETIN (MATCH) li nirxek di nav rêzek diyarkirî de digere û pozîsyona wê ya têkildar vedigerîne, ango jimareya rêz û/an stûnê;
- Piştre, fonksiyonê NAVEROK (INDEX) nirxek ji stûnek û/an rêzek diyarkirî vedigerîne.
To formula INDEX+MAÇE dikare bi hesasiyeta dozê bigere, hûn tenê hewce ne ku fonksiyonek lê zêde bikin. Ne zehmet e ku meriv texmîn bike ka ew dîsa çi ye TAM (TAM):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
Di vê formula TAM bi heman awayî wekî bi fonksiyonê re dixebite DÎTINÎ, û heman encamê dide:
Têbînî ku formula INDEX+MAÇE ku di nav brayên çîçek de hatine vegirtin, formulek rêzê ye û divê hûn bi pêlkirina wê temam bikin Ctrl + Shift + Enter.
Çima INDEX + MATCH ji bo lêgerîna hesas a dozê çareseriya çêtirîn e?
Avantajên sereke yên bundle NAVEROK и PÊKIRÎ DERKETIN:
- Ne hewce ye ku stûnek alîkar lê zêde bike, berevajî VPR.
- Ne hewce ye ku stûna lêgerînê were rêz kirin, berevajî DÎTINÎ.
- Bi her cûre daneyan re dixebite - hejmar, nivîs û tarîx.
Ev formula bêkêmasî xuya dike, ne wusa? Bi rastî, ne wisa ye. Û ji ber vê yekê ye.
Bihesibînin ku hucreya di stûna nirxa vegerê de ku bi nirxa lêgerînê ve girêdayî ye vala ye. Dê formula çi encam vegere? Na? Ka em bibînin ka formula bi rastî çi vedigere:
Oops, formula sifir vedigere! Heke hûn bi nirxên nivîsê yên paqij re dixebitin ev dibe ku pirsgirêkek mezin nebe. Lêbelê, heke di tabloyê de hejmaran hebin, di nav de sifirên "rastîn" hene, ev dibe pirsgirêk.
Bi rastî, hemî formulên lêgerînê yên din (VLOOKUP, LOOKUP, û SUMPRODUCT) ku me berê nîqaş kir bi heman rengî tevdigerin. Lê hûn formula bêkêmasî dixwazin, rast?
Ji bo ku formûlek hesas bikin INDEX+MAÇE bêkêmasî, wê bixin nav fonksiyonek IF (HER) ku dê hucreyek bi nirxek vegerê biceribîne û heke vala be encamek vala vegerîne:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
Di vê formula de:
- B stûnek bi nirxên vegerê ye
- 1+ jimareyek e ku pozîsyona têkildar a şaneyê ku ji hêla fonksiyonê ve hatî vegerandin vedigire PÊKIRÎ DERKETIN, ji bo navnîşana rastîn ya hucreyê. Mînakî, di fonksiyona me de PÊKIRÎ DERKETIN array lêgerînê hatiye dayîn A2:A7, ango pozîsyona nisbî ya şaneyê A2 xwestek 1, ji ber ku ew di rêzê de yekem e. Lê pozîsyona rastîn ya hucreyê A2 di stûnê de ye 2, ji ber vê yekê em lê zêde dikin 1ku cudahiyê çêbikin û fonksiyonê hebe NADIREKT (NEDIREKT) nirxa ji şaneya xwestî stand.
Wêneyên jêrîn formula rast-hesas-doze nîşan didin INDEX+MAÇE Di çalakiyê de. Ger şaneya vegerî vala be encamek vala vedigerîne.
Min formula li stûnan ji nû ve nivîsand B:Dji bo ku barê formula li ser dîmenderê bicîh bikin.
Formula vedigere 0heke şaneya vegerî sifir hebe.
Ger hûn girêdanê dixwazin NAVEROK и PÊKIRÎ DERKETIN Gava ku nirxa vegerê vala ye, hin peyamek nîşan dide, hûn dikarin wê di nav tîpên paşîn ("") yên formula de binivîsin, mînakî, bi vî rengî:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
