Clàr-innse
Tha an oideachadh beag seo a’ mìneachadh mar a nì thu gnìomh VPR (VLOOKUP) mothachail air cùis, a’ sealltainn grunn fhoirmlean eile as urrainn do Excel a sgrùdadh ann an dòigh a tha mothachail air cùis, agus a’ comharrachadh neartan agus laigsean gach gnìomh.
Tha mi creidsinn gu bheil fios aig a h-uile neach-cleachdaidh Excel dè an gnìomh a nì sgrùdadh dìreach. Tha sin ceart, is e gnìomh a th’ ann VPR. Ach, chan eil mòran dhaoine eòlach air sin VPR chan eil e mothachail air cùis, ie tha na litrichean beaga is àrda co-ionann air a shon.
Seo eisimpleir sgiobalta a’ sealltainn neo-chomas VPR ag aithneachadh clàr. A rèir coltais ann an cealla A1 anns a bheil an luach “bile” agus an cealla A2 - “Bile”, foirmle:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
stadaidh an rannsachadh aige air “bile” leis gu bheil an luach sin a’ tighinn an toiseach air an liosta, agus thoir a-mach an luach bhon chill B1.
Nas fhaide air adhart san artaigil seo, seallaidh mi dhut mar a nì thu VPR mothachail air cùis. A bharrachd air an sin, ionnsaichidh sinn beagan a bharrachd ghnìomhan as urrainn rannsachaidhean a tha mothachail air cùis a dhèanamh ann an Excel.
Tòisichidh sinn leis an fheadhainn as sìmplidh - VIEW (LOOKUP) agus RO-RÀDH (SUMPRODUCT), aig a bheil, gu mì-fhortanach, grunn chuingealachaidhean mòra. An ath rud, bheir sinn sùil nas mionaidiche air an fhoirmle beagan nas iom-fhillte INDEX+MATCH (INDEX + MATCH), a bhios ag obair gun sgur ann an suidheachadh sam bith agus le dàta sam bith.
Tha gnìomh VLOOKUP mothachail air cùis
Mar a tha fios agad mu thràth, an gnìomh àbhaisteach VPR tha cùis neo-mhothachail. Ach, tha dòigh ann air cùis a dhèanamh mothachail. Gus seo a dhèanamh, feumaidh tu colbh taiceil a chur ris a 'chlàr, mar a chithear san eisimpleir a leanas.
A rèir coltais ann an colbh B tha aithnichearan toraidh ann (Nì) agus tha thu airson prìs an toraidh agus am beachd co-fhreagarrach a thoirt a-mach às na colbhan C и D. Is e an duilgheadas a th’ ann gu bheil an dà chuid caractaran beaga is mòra ann an aithnichearan. Mar eisimpleir, luachan cealla B4 (001Tvci3u) agus B5 (001Tvci3U) eadar-dhealaichte a-mhàin ann an cùis a 'charactar mu dheireadh, u и U fa leth.
Mar a shaoileadh tu, tha am foirmle sgrùdaidh àbhaisteach
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
tillidh e $ 90, oir an luach 001 Tvci3u tha e san raon sgrùdaidh nas tràithe na 001 Tvci3U. Ach chan e sin a tha a dhìth oirnn, a bheil?
Gus lorg le gnìomh VPR ann an cùis Excel mothachail, feumaidh tu colbh cuideachaidh a chuir ris agus na ceallan aige a lìonadh leis an fhoirmle a leanas (far a bheil B na cholbh sgrùdaidh):
=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));"")
Bidh am foirmle seo a’ briseadh an luach a tha thu ag iarraidh gu caractaran fa leth, a’ cur a chòd an àite gach caractar (mar eisimpleir, an àite A aig 65, an àite sin a còd 97) agus an uairsin cuiridh e na còdan sin còmhla ann an sreath àireamhan sònraichte.
Às deidh sin, bidh sinn a 'cleachdadh gnìomh sìmplidh VPR airson rannsachadh cùis-mhothachail:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Obrachadh ceart a 'ghnìomh VPR Tha mothachadh cùise an urra ri dà fheart:
- Feumaidh colbh an neach-cuideachaidh a bhith mar an colbh as fhaide air chlì san raon a chithear.
- Feumaidh còd caractar a bhith anns an luach a tha thu a’ lorg an àite an fhìor luach.
Mar a chleachdas tu gnìomh CODE gu ceart
Tha am foirmle a chaidh a chuir a-steach do cheallan a’ cholbh taice a’ gabhail ris gu bheil an aon àireamh de charactaran aig a h-uile luach rannsachaidh agad. Mura h-eil, feumaidh fios a bhith agad air na h-àireamhan as lugha agus as motha agus cuir ris na h-uimhir de fheartan IFERROR (IFERROR) cia mheud caractar a tha an diofar eadar an luach as giorra agus as fhaide a chaidh a sgrùdadh.
Mar eisimpleir, mas e 3 caractaran an luach sgrùdaidh as giorra agus gur e 5 caractaran am fear as fhaide, cleachd am foirmle seo:
=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));"")
Airson gnìomh PSTR (MID) Bheir thu seachad na h-argamaidean a leanas:
- 1d argamaid - teacsa (teacsa) an t-iomradh teacsa no cealla anns a bheil na caractaran a thèid a thoirt a-mach (anns a’ chùis againn is e B2 a th’ ann)
- 2d argamaid - toiseach_num (start_position) suidheachadh a’ chiad fhear de na caractaran sin a chaidh a thoirt a-mach. thu a-steach 1 anns a' chiad ghnìomh PSTR, 2 - anns an dàrna gnìomh PSTR msaa
- 3d argamaid - àireamh_chars (àireamh_of_characters) - Sònraich an àireamh de charactaran a thèid a tharraing às an teacsa. Leis nach fheum sinn ach 1 caractar fad na h-ùine, anns a h-uile gnìomh bidh sinn a’ sgrìobhadh 1.
CUNNTASAN: Dleastanas VPR chan e am fuasgladh as fheàrr airson rannsachaidhean a tha mothachail air cùis ann an Excel. An toiseach, tha feum air colbh taiceil a chur ris. San dàrna h-àite, bidh am foirmle a’ dèanamh obair mhath a-mhàin ma tha an dàta aon-ghnèitheach, no ma tha fios air an dearbh àireamh de charactaran anns na luachan a chaidh a sgrùdadh. Mura h-eil seo mar chùis dhut, tha e nas fheàrr aon de na fuasglaidhean a tha sinn a’ sealltainn gu h-ìosal a chleachdadh.
Gnìomh LOOKUP airson sgrùdadh cùis-mhothachail
Dleastanas VIEW (LOOKUP) co-cheangailte VPR, ach tha an co-chòrdadh aige a’ ceadachadh rannsachaidhean a tha mothachail air cùis gun a bhith a’ cur colbh taiceil ris. Gus seo a dhèanamh, cleachd VIEW còmhla ris a 'ghnìomh MARBHRANN (EXACT).
Ma ghabhas sinn an dàta bhon eisimpleir roimhe (às aonais colbh taiceil), bidh an fhoirmle a leanas a’ dèiligeadh ris a’ ghnìomh:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Rannsachadh foirmle ann an raon A2: A7 maids ceart le luach cealla F2 cùis mothachail agus a’ tilleadh an luach bho cholbh B den aon sreath.
mar VPRghnìomh VIEW ag obair co-ionann ri teacsa agus luachan àireamhach, mar a chì thu san dealbh gu h-ìosal:
Cudromach! Airson an gnìomh VIEW air obrachadh gu ceart, bu chòir na luachan sa cholbh lorg a bhith air an òrdachadh ann an òrdugh dìreadh, ie bhon fheadhainn as lugha chun as motha.
Leig dhomh mìneachadh goirid mar a tha an gnìomh ag obair MARBHRANN anns an fhoirmle gu h-àrd, oir is e seo am prìomh phuing.
Dleastanas MARBHRANN a’ dèanamh coimeas eadar an dà luach teacsa anns na h-argamaidean 1d is 2na agus a’ tilleadh TRUE ma tha iad dìreach mar an ceudna, no FALSE mura h-eil iad. Tha e cudromach dhuinn gu bheil an gnìomh MARBHRANN mothachail air cùis.
Chì sinn mar a tha am foirmle againn ag obair VIEW+EXACT:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Dleastanas MARBHRANN a’ dèanamh coimeas eadar luach cealla F2 leis na h-eileamaidean uile ann an colbh A (A2: A7). Tillidh e TRUE ma lorgar maids ceart no FALSE.
- Leis gu bheil thu a 'toirt seachad a' chiad argamaid gnìomh VIEW luach TRUE, bidh e a’ toirt a-mach an luach co-fhreagarrach bhon cholbh ainmichte (sa chùis againn, colbh B) dìreach ma lorgar maids ceart, cùis-mhothachail.
Tha mi an dòchas gun robh am mìneachadh seo soilleir agus a-nis tha thu a 'tuigsinn a' phrìomh bheachd. Ma tha, cha bhith duilgheadas sam bith agad le gnìomhan eile a nì sinn sgrùdadh nas fhaide, oir. bidh iad uile ag obair air an aon phrionnsapal.
CUNNTASAN: Feumaidh an dàta sa cholbh lorg a bhith air a sheòrsachadh ann an òrdugh dìreadh.
SUMPRODUCT - lorg luachan teacsa, cùis-mhothachail, ach cha till e ach àireamhan
Mar a thuig thu mu thràth bhon tiotal, RO-RÀDH (SUMPRODUCT) na ghnìomh Excel eile a chuidicheas tu gus sgrùdadh a tha mothachail air cùis a dhèanamh, ach nach till ach luachan àireamhach. Mura h-eil an roghainn seo freagarrach dhut, faodaidh tu a dhol air adhart sa bhad chun phasgan INDEX+MATCH, a bheir fuasgladh airson cùis sam bith agus airson seòrsa dàta sam bith.
An toiseach, leig dhomh mìneachadh goirid a dhèanamh air co-chòrdadh na gnìomh seo, cuidichidh seo thu gus tuigse nas fheàrr fhaighinn air an fhoirmle a tha mothachail air cùis a leanas.
Dleastanas RO-RÀDH ag iomadachadh eileamaidean nan arrays a chaidh a thoirt seachad agus a’ tilleadh suim nan toraidhean. Tha an syntax a’ coimhead mar seo:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Leis gu bheil feum againn air sgrùdadh a tha mothachail air cùis, bidh sinn a’ cleachdadh a’ ghnìomh MARBHRANN (EXACT) bhon eisimpleir roimhe mar aon de na h-iomadadairean:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Mar a chuimhnicheas tu, MARBHRANN a’ dèanamh coimeas eadar luach cealla F2 leis na h-eileamaidean uile ann an colbh A. Tillidh e TRUE ma lorgar maids ceart no FALSE. Ann an gnìomhachd matamataigeach, tha Excel a’ gabhail TRUE mar 1, agus FALSE airson 0A bharrachd RO-RÀDH ag iomadachadh nan àireamhan sin agus a’ toirt cunntas air na toraidhean.
Chan eil neoni air an cunntadh oir nuair a thèid an iomadachadh bidh iad an-còmhnaidh a’ toirt seachad 0. Bheir sinn sùil nas mionaidiche air na thachras nuair a thèid maids ceart ann an colbh A lorg agus air ais 1… gnìomh RO-RÀDH ag iomadachadh an àireamh sa cholbh B on 1 agus tillidh e an toradh - dìreach an aon àireamh! Tha seo air sgàth gu bheil toraidhean nan toraidhean eile neoni, agus chan eil iad a’ toirt buaidh air an t-suim a thig às.
Gu mì-fhortanach, an gnìomh RO-RÀDH chan urrainn dhaibh obrachadh le luachan teacsa agus cinn-latha oir chan urrainnear an iomadachadh. Anns a 'chùis seo, gheibh thu teachdaireachd-mearachd #LUACH! (#VALUE!) mar ann an cealla F4 san dealbh gu h-ìosal:
CUNNTASAN: A’ tilleadh dìreach luachan àireamhach.
INDEX + MATCH - sgrùdadh cùis-mhothachail airson seòrsa dàta sam bith
Mu dheireadh, tha sinn faisg air foirmle sgrùdaidh gun chrìoch agus mothachail air cùis a bhios ag obair le seata dàta sam bith.
Tha an eisimpleir seo a’ tighinn mu dheireadh, chan ann air sgàth ‘s gu bheil am fear as fheàrr air fhàgail airson milseag, ach leis gu bheil an t-eòlas a fhuaireadh bho na h-eisimpleirean roimhe seo gad chuideachadh gus am foirmle a tha mothachail air cùis a thuigsinn nas fheàrr agus nas luaithe. INDEX+MATCH (INDEX+MATCH).
Mar a shaoileadh tu is dòcha, am measgachadh de ghnìomhan TUILLEADH DHUILLEAG и INDEX air a chleachdadh ann an Excel mar roghainn eile nas sùbailte agus nas cumhachdaiche airson VPR. Bidh an artaigil A’ cleachdadh INDEX agus MATCH an àite VLOOKUP a’ mìneachadh gu foirfe mar a bhios na gnìomhan sin ag obair còmhla.
Bheir mi dìreach ath-aithris air na prìomh phuingean:
- Dleastanas TUILLEADH DHUILLEAG (MATCH) a’ lorg luach ann an raon sònraichte agus a’ tilleadh a shuidheachadh càirdeach, is e sin, àireamh na loidhne agus/no colbh;
- An ath rud, an gnìomh INDEX (INDEX) a’ tilleadh luach bho cholbh agus/no sreath ainmichte.
Gu foirmle INDEX+MATCH b’ urrainn dhut cùis a sgrùdadh gu mothachail, chan fheum thu ach aon ghnìomh a chuir ris. Chan eil e doirbh tomhas dè a th’ ann a-rithist MARBHRANN (EXACT):
=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))
Anns an fhoirmle seo MARBHRANN ag obair anns an aon dòigh 'sa ann an co-bhonn ris a' ghnìomh VIEW, agus a' toirt an aon toradh :
Thoir an aire gu bheil am foirmle INDEX+MATCH dùinte ann am braces lùbach tha foirmle rèite agus feumaidh tu a chrìochnachadh le bhith a’ putadh Ctrl + Shift + Cuir a-steach.
Carson is e INDEX + MATCH am fuasgladh as fheàrr airson sgrùdadh a tha mothachail air cùis?
Na prìomh bhuannachdan a tha aig a 'phacaid INDEX и TUILLEADH DHUILLEAG:
- Chan eil feum air colbh taiceil a chur ris, eu-coltach VPR.
- Chan fheum an colbh sgrùdaidh a bhith air a sheòrsachadh, eu-coltach VIEW.
- Ag obair le gach seòrsa dàta - àireamhan, teacsa agus cinn-latha.
Tha coltas gu bheil am foirmle seo foirfe, nach eil? Gu fìrinneach, chan eil. Agus air an adhbhar sin.
Thoir an aire gu bheil an cealla sa cholbh luach tilleadh co-cheangailte ris an luach lorg falamh. Dè an toradh a bheir am foirmle air ais? Chan eil? Feuch sinn a-mach dè a tha am foirmle a 'tilleadh:
Oops, tha am foirmle a’ tilleadh neoni! Is dòcha nach bi seo na dhuilgheadas mòr ma tha thu ag obair le luachan teacsa fìor. Ach, ma tha àireamhan anns a’ chlàr, a’ gabhail a-steach neoni “fìor”, bidh seo na dhuilgheadas.
Gu dearbh, bidh na foirmlean sgrùdaidh eile (VLOOKUP, LOOKUP, agus SUMPRODUCT) air an do bhruidhinn sinn na bu thràithe gan giùlan fhèin san aon dòigh. Ach tha thu ag iarraidh am foirmle foirfe, ceart?
Gus cùis foirmle a dhèanamh mothachail INDEX+MATCH foirfe, cuir ann an gnìomh e IF (IF) a nì deuchainn air cealla le luach tilleadh agus a thilleas toradh falamh ma tha e falamh:
=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));"")
Anns an fhoirmle seo:
- B tha colbh le luachan tilleadh
- 1+ 'S e àireamh a tha a' tionndadh suidheachadh coimeasach na cealla air a thilleadh leis a' ghnìomh TUILLEADH DHUILLEAG, gu fìor sheòladh na neul. Mar eisimpleir, nar gnìomh TUILLEADH DHUILLEAG raon sgrùdaidh air a thoirt seachad A2: A7, is e sin, suidheachadh coimeasach na cealla A2 bidh 1, oir is e a' chiad fhear anns an raon. Ach suidheachadh fìor na cealla A2 anns a' cholbh tha 2, mar sin bidh sinn a 'cur ris 1gus an eadar-dhealachadh a dhèanamh suas agus an gnìomh a bhith agad URNUIGH (INDIRECT) an luach fhaighinn air ais bhon chill a bha thu ag iarraidh.
Tha na dealbhan gu h-ìosal a’ sealltainn na foirmle ceartaichte a tha mothachail air cùis INDEX+MATCH Ann an gnìomh. Tillidh e toradh falamh ma tha an cealla air ais falamh.
Rinn mi ath-sgrìobhadh air an fhoirmle gu colbhan B:Dgus am bàr fhoirmeil a shuidheachadh air an ath-sgrìn.
Formula a 'tilleadh 0ma tha neoni anns a' chill air ais.
Ma tha thu ag iarraidh an ceangal INDEX и TUILLEADH DHUILLEAG teachdaireachd a thaisbeanadh nuair a tha an luach tilleadh falamh, faodaidh tu a sgrìobhadh anns na luachan mu dheireadh (“”) den fhoirmle, mar eisimpleir, mar seo:
=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.")
