Mataupu
O lenei aʻoaʻoga laʻititi o loʻo faʻamatalaina pe faʻafefea ona fai se galuega VPR (VLOOKUP), faʻaalia le tele o isi fua faʻatatau e mafai e Excel ona suʻeina i se tulaga faʻapitoa, ma faʻaalia le malosi ma vaivaiga o galuega taʻitasi.
Ou te masalo e iloa uma e tagata faʻaoga Excel le galuega e faia ai suʻesuʻega i luga. E sa'o, ose galuega VPR. Peitaʻi, e toʻaitiiti tagata e iloa lenā mea VPR e le maaleale i mataitusi mataitusi, o lona uiga o mataitusi laiti ma mataitusi pito i luga e tutusa mo ia.
O se faʻataʻitaʻiga vave lea e faʻaalia ai le le mafai VPR iloa resitala. Faapea i totonu o se sela A1 o loʻo i ai le tau "pili" ma le sela A2 – “Pili”, fua faatatau:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… o le a taofi lana sailiga i le "pili" talu ai o lena tau e sau muamua i le lisi, ma aveese mai le tau mai le sela B1.
Mulimuli ane i lenei tusiga, o le a ou faʻaali atu ia te oe le auala e fai ai VPR mata'upu ma'ale'ale. E le gata i lea, o le a matou aʻoaʻoina ni nai galuega e mafai ona faia suʻesuʻega faʻapitoa i Excel.
O le a tatou amata i le faigofie - VIEW (LOOKUP) ma FAʻAMATALAGA (SUMPRODUCT), lea, o le mea e leaga ai, e tele ni tapulaʻa taua. O le isi, o le a tatou vaʻavaʻai totoʻa i le fua faʻatatau lavelave teisi INDEX+ MATIGA (INDEX+MATCH), lea e galue lelei i so'o se tulaga ma so'o se fa'amaumauga.
Ole galuega ole VLOOKUP ole mata'itusi maaleale
E pei ona uma ona e iloa, o le galuega masani VPR e le maaleale i mataitusi. Ae ui i lea, o loʻo i ai se auala e faʻamaonia ai mataʻitusi. Ina ia faia lenei mea, e tatau ona e faʻaopoopoina se koluma fesoasoani i le laulau, e pei ona faʻaalia i le faʻataʻitaʻiga o loʻo i lalo.
Faapea i se koluma B o loʻo i ai faʻamatalaga oloa (Item) ma e te manaʻo e aveese le tau o le oloa ma le faʻamatalaga tutusa mai koluma C и D. O le fa'afitauli o lo'o iai mata'itusi laiti ma mataitusi tetele i fa'amatalaga. Mo se faʻataʻitaʻiga, tau o sela B4 (001Tvci3u) ma B5 (001Tvci3U) e ese i le tulaga o le tagata mulimuli, u и U faasologa.
E pei ona mafai ona e mafaufauina, o le masani suʻesuʻe fua
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
o le a toe foi $ 90, talu mai le tau 001Tvci3u o lo'o i le va'aiga su'esu'e muamua atu 001Tvci3U. Ae e le o le mea lena tatou te manaomia, a ea?
E su'esu'e ma se galuega VPR i Excel tulaga maaleale, e tatau ona e faʻaopopoina se koluma fesoasoani ma faʻatumu ona sela i le faʻasologa o loʻo i lalo (lea o B o le koluma suʻe):
=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));"")
O lenei fua fa'atatau e fa'amavae ai le tau mana'omia i ni mata'itusi eseese, e sui ai uiga ta'itasi ma lona fa'ailoga (mo se fa'ata'ita'iga, nai lo le A i le 65, nai lo lena a code 97) ona tuʻufaʻatasia lea o nei tulafono i se manoa tulaga ese o numera.
A maeʻa lena, matou te faʻaaogaina se galuega faigofie VPR mo su'esu'ega fa'apitoa:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Fa'atonuga lelei o le galuega VPR mata'itusi fa'alagolago i mea e lua:
- O le koluma fesoasoani e tatau ona avea ma koluma pito agavale i le va'aiga va'aia.
- O le tau o lo'o e su'eina e tatau ona i ai se fa'ailoga tagata nai lo le tau moni.
Fa'afefea ona fa'aoga sa'o le galuega CODE
O le fua faʻapipiʻi i totonu o sela o le koluma fesoasoani e faʻapea o au suʻesuʻega uma e tutusa le numera o mataitusi. Afai e leai, ona tatau lea ona e iloa numera laʻititi ma tele ma faʻaopoopo le tele o foliga IFERROR (IFERROR) e fia mataitusi o le eseesega i le va o le tau sili ona puupuu ma le umi suʻesuʻe.
Mo se faʻataʻitaʻiga, afai o le tau suʻesuʻe sili ona puupuu o le 3 mataitusi ma le umi o le 5 mataitusi, faʻaaoga le faiga lenei:
=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));"")
Mo galuega PSTR (MID) E te tu'uina atu fa'amatalaga nei:
- 1st finauga - matua (tusitusi) o le faʻamatalaga poʻo le cell reference o loʻo i ai mataitusi e faʻapipiʻiina (i la matou tulaga o le B2)
- 2st finauga - start_num (start_position) o le tulaga o le muamua o na mataitusi e aveese. e te ulufale 1 i le galuega muamua PSTR, 2 – i le galuega lona lua PSTR ma isi tulaga faapena
- 3st finauga - num_chars (number_of_characters) – Fa'amaoti le numera o mataitusi e fa'asolo mai le tusitusiga. Talu ai tatou te manaʻomia naʻo le 1 uiga i taimi uma, i galuega uma tatou te tusia 1.
LIMITATIONS: galuega tauave VPR e le o se fofo sili mo suʻesuʻega faʻapitoa ile Excel. Muamua, e manaʻomia le faʻaopoopoga o se koluma fesoasoani. Lona lua, o le fua faʻatatau e faia se galuega lelei pe afai o faʻamaumauga e tutusa, pe o le numera saʻo o mataʻitusi i le suʻesuʻeina tau e iloa. Afai e le o lau mataupu lea, e sili atu le faʻaaogaina o se tasi o fofo o loʻo matou faʻaalia i lalo.
LOOKUP galuega mo su'esu'ega fa'apitoa
galuega tauave VIEW (LOOKUP) fesootai VPR, peita'i o lona syntax e mafai ai ona su'esu'eina mata'itusi e aunoa ma le fa'aopoopoina o se koluma fesoasoani. Ina ia faia lenei mea, faʻaaoga VIEW fa'atasi ma le galuega VAEGA (SA'O).
Afai tatou te ave faʻamatalaga mai le faʻataʻitaʻiga muamua (e aunoa ma se koluma fesoasoani), o le faʻatatau lea o le a feagai ma le galuega:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Su'esu'ega fua fa'atatau ile va'aiga A2:A7 fetaui tonu ma le tau cell F2 mata'itusi ma'ale'ale ma toe faafoi le tau mai le koluma B o le laina tutusa.
pei VPRgaluega tauave VIEW galue tutusa ma tusitusiga ma numera, e pei ona e vaʻai i le faʻamalama o loʻo i lalo:
Taua! Ina ia faia le galuega VIEW galue sa'o, o tau i le koluma su'e e tatau ona fa'avasega i luga o le fa'asologa, e pei o le la'ititi i le tele.
Se'i ou faamatala faapuupuu atu pe faapefea ona galue le galuega VAEGA i le fua o loʻo faʻaalia i luga, aua o le mea autu lea.
galuega tauave VAEGA fa'atusatusa ia fa'amaumauga e lua i le 1st ma le 2nd finauga ma toe fa'afo'i le TRUE pe afai e tutusa lelei, po'o le FALSE pe a leai. E taua ia i tatou le galuega VAEGA mata'upu ma'ale'ale.
Se'i tatou va'ai pe fa'afefea ona galue le tatou fua fa'atatau VA'AI + SA'O:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- galuega tauave VAEGA fa'atusatusa le tau o sela F2 ma elemene uma i se koluma A (A2:A7). Fa'afo'i le TRUE pe a maua se fetaui tonu, a leai, FALSE.
- Talu ai e te tuʻuina atu le finauga galuega muamua VIEW tau TRUE, e maua mai le tau tutusa mai le koluma faʻamaonia (i la matou tulaga, koluma B) pe a maua se fetaui tonu, mataʻutia mataʻutia.
Ou te faʻamoemoe na manino lenei faʻamatalaga ma o lea ua e malamalama i le manatu autu. Afai o lea, o le a leai sau faʻafitauli i isi galuega o le a matou iloiloina atili, aua. latou te galulue uma i luga o le mataupu faavae e tasi.
LIMITATIONS: O fa'amaumauga i le koluma su'e e tatau ona fa'avasega i le fa'asologa alu a'e.
SUMPRODUCT - su'e tau o tusitusiga, mata'itusi maaleale, ae toe faafoi na o numera
E pei ona e malamalama i ai mai le ulutala, FAʻAMATALAGA (SUMPRODUCT) o le isi Excel galuega e fesoasoani ia te oe e fai se suʻesuʻega faʻapitoa, ae o le a toe faʻafoʻi ai numera numera. Afai e le fetaui lenei filifiliga ia te oe, ona mafai loa lea ona e alu i le fusi INDEX+ MATIGA, lea e maua ai se fofo mo soʻo se mataupu ma mo soʻo se ituaiga faʻamatalaga.
Muamua, se'i ou fa'amatala fa'apuupuu le syntax o lenei galuega, o le a fesoasoani ia te oe e te malamalama atili ai i le fa'asologa o mataupu-maaleale o loʻo mulimuli mai.
galuega tauave FAʻAMATALAGA fa'ateleina elemene o fa'asologa o lo'o tu'uina atu ma toe fa'afo'i le aofa'i o fa'ai'uga. O le syntax e pei o lenei:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Talu ai matou te manaʻomia se suʻesuʻega faʻapitoa, matou te faʻaogaina le galuega VAEGA (SA'O) mai le fa'ata'ita'iga muamua o se tasi o fa'atele:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
E pei ona e manatuaina, VAEGA fa'atusatusa le tau o sela F2 ma elemene uma i se koluma A. Toe fa'afo'i le TRUE pe a maua se fetaui tonu, a leai, FALSE. I fa'agaioiga fa'a-matematika, Excel e fa'aaoga TRUE pei 1, ma SESE mo 0E le gata i lea FAʻAMATALAGA fa'atele nei fuainumera ma fa'aputuina i'uga.
Zeros e le faitauina aua a faʻateleina latou te tuʻuina atu i taimi uma 0. Se'i o tatou va'ai toto'a ile mea e tupu pe a fetaui tonu ile koluma A maua ma toe foi mai 1… Galuega FAʻAMATALAGA faatele le numera i le koluma B on 1 ma toe fa'afo'i mai le fa'ai'uga - tutusa tonu le numera! E mafua ona o taunuuga o isi oloa e leai se mea, ma latou te le afaina ai le aofaiga e maua.
Ae paga lea o le galuega FAʻAMATALAGA e le mafai ona galue i faʻamaumauga ma aso aua e le mafai ona faʻateleina. I lenei tulaga, o le ae mauaina se savali sese #VALUE! (#VALUE!) pei o totonu o se sela F4 i le ata i lalo:
LIMITATIONS: Toe fa'afo'i na'o tau numera.
INDEX + MATCH - suʻesuʻega faʻapitoa mo soʻo se ituaiga faʻamatalaga
Mulimuli ane, matou te latalata atu i se fa'asologa o su'esu'ega e le fa'atapula'aina ma mata'itusi e aoga ma so'o se seti fa'amaumauga.
O lenei faʻataʻitaʻiga e sau mulimuli, e le ona o le mea e sili ona lelei e totoe mo le suamalie, ae ona o le malamalama na maua mai faʻataʻitaʻiga muamua o le a fesoasoani ia te oe e te malamalama ai i le faʻataʻitaʻiga faʻapitoa e sili atu ma vave. INDEX+ MATIGA (INDEX+MATCH).
E pei ona e mateina, o le tuufaatasiga o galuega TELE FA'AALIGA и INDEX fa'aoga i Excel e sili atu ona fetu'una'i ma mamana isi mo VPR. Le tala Fa'aaogā INDEX ma MATCH nai lo le VLOOKUP o le a fa'amalamalama lelei pe fa'apefea ona galulue fa'atasi nei galuega.
O le a na ona ou toe taʻua manatu autu:
- galuega tauave TELE FA'AALIGA (MATCH) su'esu'e mo se tau i totonu o se va'aiga ma toe fa'afo'i lona tulaga fa'atatau, o lona uiga, le laina ma/po'o le koluma numera;
- Sosoo ai, le galuega INDEX (INDEX) toe fa'afo'i mai se tau mai se koluma ma/po'o se laina.
I fua INDEX+ MATIGA e mafai ona su'esu'e mata'upu, e na'o le tasi le galuega e te mana'omia e fa'aopoopo i ai. E le faigata ona toe mate po o le a VAEGA (SA'O):
=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))
I lenei fua fa'atatau VAEGA galue i le auala lava e tasi e pei o le faʻatasi ma le galuega VIEW, ma maua ai le tali tutusa:
Manatua o le fua fa'atatau INDEX+ MATIGA o lo'o fa'amauina i fusi fa'a'umi'umi o se fa'asologa fa'asologa ma e tatau ona e fa'atumuina i le oomiina Ctrl + Shift + Ulufale.
Aisea ua avea ai le INDEX+MATCH ma fofo sili mo su'esu'ega fa'apitoa?
O faʻamanuiaga autu o le fusi INDEX и TELE FA'AALIGA:
- E le manaʻomia le faʻaopoopoina o se koluma fesoasoani, e le pei VPR.
- E le mana'omia le koluma su'esu'e e fa'avasega, e le pei VIEW.
- E galue i ituaiga uma o faʻamaumauga - numera, tusitusiga ma aso.
E foliga mai e atoatoa lenei fua, a ea? O le mea moni, e leai. Ma o le mafuaaga lena.
Fa'apea o le sela o lo'o i le koluma tau fa'afo'i e feso'ota'i ma le tau su'esu'e e gaogao. O le a le taunuuga o le a toe foi mai le fua? Leai? Se'i tatou va'ai po'o le a tonu le toe fo'i mai o le fua fa'atatau:
Oi, o le fua e toe fo'i le zero! Atonu e le o se fa'afitauli tele pe afai o lo'o e galue i fa'amaumauga mama. Ae peitaʻi, afai o le laulau o loʻo i ai numera, e aofia ai "moni" zeros, e avea lea ma faʻafitauli.
O le mea moni, o isi su'ega su'esu'e uma (VLOOKUP, LOOKUP, ma SUMPRODUCT) na matou talanoaina muamua e amio tutusa. Ae e te manaʻo i le fua faʻatatau atoatoa, saʻo?
Ia fai se fua fa'atatau mata'upu INDEX+ MATIGA atoatoa, tuu i se galuega IF (IF) o le a suʻeina se sela ma se tau toe faʻafoʻi ma toe faʻafoʻi se iʻuga gaogao pe a gaogao:
=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));"")
I lenei fua fa'atatau:
- B ose koluma o lo'o i ai tau fa'afo'i
- 1+ o se numera e liliu ai le tulaga fa'atatau o le sela e toe fa'afo'i mai e le galuega TELE FA'AALIGA, i le tuatusi moni o le sela. Mo se faʻataʻitaʻiga, i la tatou galuega TELE FA'AALIGA tu'u su'esu'e A2:A7, o lona uiga, o le tulaga o le sela A2 o le a 1, aua o le mea muamua lea i le fa'asologa. Ae o le tulaga moni o le sela A2 i le koluma e 2, o lea matou te faaopoopo atu ai 1ia faia le eseesega ma ia i ai le galuega LAVA (INDIRECT) toe aumai le tau mai le sela mana'omia.
O ata o lo'o i lalo o lo'o fa'aalia ai le fa'atonuga fa'asa'o mata'itusi INDEX+ MATIGA I le faatinoga. E fa'afo'i mai se fa'ai'uga gaogao pe a gaogao le sela na toe fo'i mai.
Sa ou toe tusia le fua i koluma B:De fetaui ma le fua fa'atatau i le fa'amalama.
Fa'afo'i fua 0pe afai o le sela ua toe faafoi mai e leai se mea.
Afai e te mana'o i le so'oga INDEX и TELE FA'AALIGA faʻaalia se feʻau pe a gaogao le tau toe faafoi, e mafai ona e tusia i upusii mulimuli ("") o le fua, mo se faʻataʻitaʻiga, pei o lenei:
=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.")
