Hevitra ato Anatiny
Ity lesona kely ity dia manazava ny fomba hanaovana asa iray VPR (VLOOKUP), dia mampiseho formulas hafa izay azon'ny Excel hikaroka amin'ny fomba saro-pady, ary manondro ny tanjany sy ny fahalemen'ny asa tsirairay.
Heveriko fa fantatry ny mpampiasa Excel rehetra hoe inona no asa manao fikarohana mitsangana. Marina izany, fiasa izany VPR. Vitsy anefa no mahalala izany VPR tsy saro-pady, izany hoe ny litera ambany sy ambony dia mitovy aminy.
Ity misy ohatra haingana mampiseho ny tsy fahaizana VPR manaiky rejisitra. Aoka hatao hoe ao anaty sela A1 misy ny sandan'ny "bill" sy ny sela A2 - "Bill", formula:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… dia hampiato ny fikarohana momba ny “bill” satria io sanda io no voalohany amin'ny lisitra, ary hanesorana ny sanda avy amin'ny sela B1.
Any aoriana ato amin'ity lahatsoratra ity dia hasehoko anao ny fomba hanaovana izany VPR manaja sorabaventy sy soramadinika. Ho fanampin'izany, hianatra asa vitsivitsy hafa izay afaka manao fikarohana saro-pady amin'ny Excel isika.
Hanomboka amin'ny tsotra indrindra isika – VIEW (LOOKUP) ary SUMPRODUCT (SUMPRODUCT), izay, indrisy, manana fetra lehibe maromaro. Manaraka izany dia hojerentsika akaiky ny formulaire somary sarotra kokoa INDEX+MATCH (INDEX+MATCH), izay miasa tsara amin'ny toe-javatra rehetra sy amin'ny angon-drakitra rehetra.
Ny fiasa VLOOKUP dia saro-pady
Araka ny efa fantatrao, ny asa mahazatra VPR dia case insensitive. Na izany aza, misy fomba iray hahatonga azy ho saro-pady. Mba hanaovana izany dia mila manampy tsanganana fanampiny amin'ny latabatra ianao, araka ny aseho amin'ity ohatra manaraka ity.
Eritrereto hoe ao anaty tsanganana B misy famantarana ny vokatra (Item) ary tianao ny haka ny vidin'ny vokatra sy ny fanehoan-kevitra mifanaraka amin'izany avy amin'ny tsanganana C и D. Ny olana dia misy tarehintsoratra kely sy lehibe ny identifiers. Ohatra, ny sandan'ny sela B4 (001Tvci3u) ary B5 (001Tvci3U) dia tsy mitovy afa-tsy amin'ny tarehin-tsoratra farany, u и U tsirairay avy.
Araka ny azonao an-tsaina, ny fomba fikarohana mahazatra
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
dia hiverina $ 90, hatramin'ny sandany 001Tvci3u dia eo amin'ny sehatry ny fikarohana aloha kokoa noho ny 001Tvci3U. Tsy izany anefa no ilaintsika, sa tsy izany?
Hikaroka miaraka amina asa iray VPR amin'ny tranga saro-pady Excel, tsy maintsy manampy tsanganana mpanampy ianao ary fenoy ny selany amin'ity formula manaraka ity (izay misy B ny tsanganana fitadiavana):
=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));"")
Ity formula ity dia manapaka ny sanda irina ho tarehintsoratra misaraka, manolo ny tarehintsoratra tsirairay amin'ny kaody (ohatra, fa tsy ny A amin'ny 65, fa a code 97) ary avy eo dia manambatra ireo kaody ireo ho andian-tarehimarika miavaka.
Aorian'izany dia mampiasa fiasa tsotra izahay VPR ho an'ny fikarohana saro-pady:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Fampandehanana araka ny tokony ho izy ny asa VPR miankina amin'ny lafin-javatra roa ny case-sensitive:
- Ny tsanganana mpanampy dia tsy maintsy ny tsanganana ankavia indrindra amin'ny faritra azo jerena.
- Ny sanda tadiavinao dia tsy maintsy misy kaody tarehintsoratra fa tsy ny tena sandany.
Ahoana ny fampiasana tsara ny fiasan'ny CODE
Ny raikipohy ampidirina ao amin'ny selan'ny tsanganana fanampiny dia mihevitra fa mitovy ny isan'ny tarehin-tsoratra ny soatoavin'ny fikarohanao rehetra. Raha tsy izany dia mila mahafantatra ny isa kely sy lehibe indrindra ianao ary ampio endri-javatra maro IFERROR (IFERROR) firy ny tarehin-tsoratra ny fahasamihafan'ny sanda karohina fohy indrindra sy lava indrindra.
Ohatra, raha tarehin-tsoratra 3 ny sandan'ny fikarohana fohy indrindra ary tarehintsoratra 5 ny lava indrindra, ampiasao ity lamina ity:
=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));"")
Ho an'ny asa PSTR (MID) Manome ireto hevitra manaraka ireto ianao:
- hevitra 1- lahatsoratra (lahatsoratra) dia ny lahatsoratra na ny cellule reference misy ny tarehintsoratra ho alaina (amin'ny tranga misy antsika dia B2)
- hevitra 2- start_num (start_position) dia ny toeran'ny voalohany amin'ireo endri-tsoratra ireo ho alaina. miditra ianao 1 amin'ny asa voalohany PSTR, 2 – amin'ny asa faharoa PSTR etc.
- hevitra 3- num_chars (number_of_characters) – Manondro ny isan'ny tarehin-tsoratra alaina avy amin'ny lahatsoratra. Satria tarehintsoratra 1 ihany no ilaintsika amin'ny fotoana rehetra, amin'ny asa rehetra dia manoratra isika 1.
fetra: asa VPR dia tsy vahaolana tsara indrindra ho an'ny fikarohana saro-pady amin'ny Excel. Voalohany, ilaina ny manampy tsanganana fanampiny. Faharoa, ny raikipohy dia manao asa tsara raha tsy mitovy ny angon-drakitra, na ny tena isan'ny tarehin-tsoratra amin'ny soatoavina tadiavina dia fantatra. Raha tsy izany no raharahanao, dia tsara kokoa ny mampiasa ny iray amin'ireo vahaolana aseho eto ambany.
LOOKUP miasa ho an'ny fikarohana saro-pady
asa VIEW (LOOKUP) mifandraika VPR, na izany aza, ny syntax dia mamela ny fikarohana saro-pady tsy misy tsanganana fanampiny. Mba hanaovana izany, ampiasao VIEW miaraka amin'ny asa marina (EXACT).
Raha maka ny angon-drakitra avy amin'ny ohatra teo aloha isika (tsy misy tsanganana fanampiny), dia ity formula manaraka ity dia hiatrika ilay asa:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Fikarohana raikipohy amin'ny faritra A2:A7 mifanaraka tsara amin'ny sandan'ny sela F2 case sensitive ary mamerina ny sanda avy amin'ny tsanganana B amin'ny laharana mitovy.
toy ny VPRfunction VIEW miasa mitovy amin'ny lahatsoratra sy sanda isa, araka ny hitanao amin'ny pikantsary etsy ambany:
Zava-dehibe! Mba hanaovana ny asa VIEW niasa tsara, ny soatoavina ao amin'ny tsanganana fitadiavana dia tokony halamina amin'ny filaharana miakatra, izany hoe manomboka amin'ny kely indrindra ka hatramin'ny lehibe indrindra.
Mamelà ahy hanazava fohifohy ny fomba fiasan'ny asa marina ao amin'ny formula aseho etsy ambony, satria io no hevi-dehibe.
asa marina mampitaha ny soatoavina lahatsoratra roa ao amin'ny tohan-kevitra 1 sy 2 ary mamerina TRUE raha mitovy tanteraka izy ireo, na DISO raha tsy izany. Zava-dehibe ho antsika ny asa marina manaja sorabaventy sy soramadinika.
Andeha hojerentsika ny fomba fiasantsika JEREO + EXACT:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- asa marina mampitaha ny sandan'ny sela F2 miaraka amin'ny singa rehetra ao anaty tsanganana A (A2:A7). Mamerina TRUE raha misy mifanandrify marina hita, raha tsy izany FALSE.
- Satria omenao ny argument function voalohany VIEW sanda TRUE, dia maka ny sanda mifanaraka amin'izany avy amin'ny tsanganana voatondro (amin'ny tranga misy antsika, tsanganana B) raha tsy misy ny fifanandrinana marina hita, saro-pady.
Manantena aho fa nazava io fanazavana io ary azonao izao ny hevi-dehibe. Raha izany no izy, dia tsy hanana fahasarotana amin'ny fiasa hafa izay hodinihinay bebe kokoa, satria. izy rehetra dia miasa amin'ny foto-kevitra iray ihany.
fetra: Ny angona ao amin'ny tsanganana fitadiavana dia tsy maintsy alamina amin'ny filaharana miakatra.
SUMPRODUCT - mahita ny soatoavina lahatsoratra, saro-pady, fa isa ihany no mamerina
Araka ny efa azonao avy amin'ny lohateny, SUMPRODUCT (SUMPRODUCT) dia fiasa Excel iray hafa izay hanampy anao hanao fikarohana saro-pady, fa hamerina ny sanda isa ihany. Raha tsy mety aminao io safidy io, dia azonao atao ny miroso avy hatrany amin'ny fonosana INDEX+MATCH, izay manome vahaolana ho an'ny tranga rehetra sy ny karazana data rehetra.
Voalohany, mamelà ahy hanazava fohifohy ny syntax an'ity fiasa ity, hanampy anao hahatakatra bebe kokoa ny raikipohy saro-pady manaraka.
asa SUMPRODUCT dia mampitombo ny singa amin'ny laharan-tariby nomena ary mamerina ny fitambaran'ny vokatra. Toy izao ny syntax:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Satria mila fikarohana saro-pady izahay, dia mampiasa ny fiasa izahay marina (EXACT) avy amin'ny ohatra teo aloha ho iray amin'ireo fampitomboana:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Araka ny tsaroanao, marina mampitaha ny sandan'ny sela F2 miaraka amin'ny singa rehetra ao anaty tsanganana A. Mamerina TRUE raha misy mifanandrify marina hita, raha tsy izany FALSE. Amin'ny asa matematika, Excel dia maka TRUE ho 1, ary DISO ho an'ny 0koa SUMPRODUCT ampitomboina ireo isa ireo ary mamintina ny vokatra.
Tsy isaina ny aotra satria rehefa ampitomboina dia manome foana 0. Andeha hojerentsika akaiky ny zava-mitranga rehefa mifanitsy amin'ny tsanganana iray A hita dia niverina 1… Asa SUMPRODUCT dia mampitombo ny isa ao amin'ny tsanganana B on 1 ary mamerina ny valiny - mitovy ny isa! Izany dia satria ny vokatry ny vokatra hafa dia aotra, ary tsy misy fiantraikany amin'ny vola azo.
Indrisy fa ny asa SUMPRODUCT tsy afaka miasa amin'ny soatoavina lahatsoratra sy daty satria tsy azo ampitomboina. Amin'ity tranga ity dia hahazo hafatra diso ianao #VALUE! (#VALUE!) toy ny ao anaty sela F4 amin'ny sary eto ambany:
fetra: Mamerina sanda isa ihany.
INDEX + MATCH - fikarohana saro-pady amin'ny karazana data rehetra
Farany, manakaiky ny raikipohy fikarohana tsy misy fetra sy saro-pady izay miasa miaraka amin'ny angon-drakitra rehetra.
Ity ohatra ity dia tonga farany, tsy hoe satria ny tsara indrindra no avela ho tsindrin-tsakafo, fa noho ny fahalalana azo avy amin'ireo ohatra teo aloha dia hanampy anao hahatakatra tsara kokoa sy haingana kokoa ny formulaire-sensitive. INDEX+MATCH (INDEX+MATCH).
Araka ny efa noeritreretinao angamba, ny fitambaran'ny asa MISEHO BETSAKA и INDEX ampiasaina amin'ny Excel ho safidy mora kokoa sy mahery kokoa ho an'ny VPR. Ny lahatsoratra Mampiasa INDEX sy MATCH fa tsy VLOOKUP dia hanazava tsara ny fomba iarahan'ireo fiasa ireo.
Hameriko fotsiny ireo hevi-dehibe:
- asa MISEHO BETSAKA (MATCH) dia mikaroka sanda amin'ny faritra iray ary mamerina ny toerana misy azy, izany hoe ny laharana sy/na tsanganana;
- Manaraka, ny asa INDEX (INDEX) dia mamerina sanda avy amin'ny tsanganana sy/na andalana voafaritra.
To formula INDEX+MATCH afaka mikaroka amin'ny toe-javatra saro-pady, mila manampy asa iray fotsiny ianao. Tsy sarotra ny maminavina hoe inona indray izany marina (MARINA):
=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))
Amin'ity formula ity marina miasa amin'ny fomba mitovy amin'ny miaraka amin'ny asa VIEW, ary manome vokatra mitovy:
Mariho fa ny formula INDEX+MATCH voahidy ao anaty braces olioly dia raikipohy array ary tsy maintsy vitanao izany amin'ny fanerena Ctrl + Shift + Enter.
Nahoana ny INDEX+MATCH no vahaolana tsara indrindra amin'ny fikarohana saro-pady?
Ny tombony lehibe amin'ny fonosana INDEX и MISEHO BETSAKA:
- Tsy mila manampy tsanganana fanampiny, tsy mitovy VPR.
- Tsy mitaky ny tsanganana fikarohana mba halamina, tsy mitovy VIEW.
- Miasa amin'ny karazana data rehetra - isa, lahatsoratra ary daty.
Toa tonga lafatra io raikipohy io, sa tsy izany? Raha ny marina dia tsy izany. Ary izay no antony.
Hevero fa foana ny sela ao amin'ny tsanganana sanda miverina mifandray amin'ny sanda fitadiavana. Inona no vokatra hiverina ny formula? Tsia? Andeha hojerentsika hoe inona no tena miverina amin'ny formula:
Oops, miverina aotra ny formula! Mety tsy ho olana lehibe izany raha toa ka miasa amin'ny soatoavina lahatsoratra madio ianao. Na izany aza, raha misy isa ny latabatra, anisan'izany ny aotra "tena izy", dia lasa olana izany.
Raha ny marina, ny raikipohy fitadiavana hafa rehetra (VLOOKUP, LOOKUP, ary SUMPRODUCT) noresahinay teo aloha dia manao fihetsika mitovy. Saingy mila ny raikipohy tonga lafatra ianao, sa tsy izany?
Mba hahatonga ny formula ho saro-pady INDEX+MATCH tonga lafatra, apetraho amin'ny asa iray IF (IF) izay hizaha sela misy sanda miverina ary mamerina valiny foana raha tsy misy izany:
=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));"")
Amin'ity formula ity:
- B dia tsanganana misy sanda miverina
- 1+ dia isa iray mamadika ny toeran'ny sela naverin'ny asa MISEHO BETSAKA, mankany amin'ny tena adiresin'ny sela. Ohatra, amin'ny asantsika MISEHO BETSAKA laha-pikarohana nomena A2:A7, izany hoe ny toeran’ny sela A2 dia 1, satria io no voalohany amin'ny array. Fa ny tena toerana misy ny sela A2 ao amin'ny tsanganana dia 2, ka ampianay 1mba hamenoana ny fahasamihafana ary hanana ny asany ankolaka (INDIRECT) dia naka ny sanda avy amin'ny sela irina.
Ny sary etsy ambany dia mampiseho ny raikipohy ahitsy saro-pady INDEX+MATCH In action. Mamerina vokatra poakaty izany raha banga ny sela naverina.
Nosoratako ho tsanganana ilay formula B:Dmba hifanaraka amin'ny bara formula amin'ny pikantsary.
Miverina ny formula 0raha misy aotra ny sela naverina.
Raha tianao ny rohy INDEX и MISEHO BETSAKA Naseho hafatra rehefa foana ny sanda miverina, azonao atao ny manoratra izany amin'ny teny farany ("") amin'ny formula, ohatra, toy izao:
=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.")
