Nekdo, ki je opravil preizkus certifikata Excel, je zamudil vprašanje. Vprašanje so skušali preveč razmisliti. Ne zahteva nore formule matrike. Preprosta IFNA jo bo rešila. Čeprav v Excelu obstaja veliko načinov, kako vse rešiti, so včasih najpreprostejši načini prezrti.
Oglejte si video
Samodejno ustvarjen prepis
- dobrodošli nazaj, čas je za drugo
- dvoboj excel podcast Sem Bill Jelen
- od se mi bo pridružil Mike
- Gerber iz Excela je ta, ki je naš
- epizoda 189 - iskanje po orisnih podatkih
- hej, urejam dele tega vprašanja
- ampak to je super vprašanje
- na žalost so moji prijatelji vzeli to
- ne morem ponovno opraviti izpita in sem ga še imel
- težava s tem vprašanjem, ki ga imamo
- ustvaril preglednico, ki zrcali
- vprašanje, da smo popolnoma nerazumljivi glede tega
- Formula odgovor mora biti ime
- država ni najbolj resnična ali lažna
- iskreno in da sem jim rediral ime
- toda pet jih poskuša
- da bi to dobro ugotovili, torej tukaj je
- vprašanje kako ustvariti stolpec 1 kako
- ustvari vlookup, ko je stolpec a
- prazna polja, s katerimi želim izpolniti stolpec B
- državo in tukaj je naša iskalna tabela
- od kode do države tako
- na primer a2 bo Afganistan in
- to se bo ponovilo vse do 10
- in potem bomo prišli do nove ene sekire
- otok OL Anandi 11 do 18 primer
- pričakovani rezultati so tukaj v redu
- oh hej, čutim tvojo bolečino, ampak ti si
- preveč razmišljati o vsem, kar se dogaja
- to vam ne more povedati imena izpita
- ne bo tako težko kot ti
- to delajo tako, da mora biti
- super preprosto enako vlookup pojdi, če si
- poiščite F v tej iskalni tabeli
- tukaj F 4 vejica 2 vejica tako neresnično
- popolna pravica in to deluje za to
- vrstici in za vrstico 11, vendar se ne bo
- delaj za ostale vrstice v redu tako pravično
- v resnično preprosti angleščini, hej, Excel
- če dobite n / a, preprosto zgrabite prejšnjega
- vrednost iz tega stolpca
- najdeno nad menoj, prinesi to dol,
- vrnemo se enaki, če ni to
- kaj preizkušajo, če je na popolnoma nov
- v Excelu 2013
- fantje, ki so napisali test, to želijo
- poskrbite, da boste vedeli, če na znam
- konec tega, če dobimo n / a kot
- hočem samo vrednost zgoraj
- meni v tem primeru ta celica b1 všeč
- tega in tega ne bomo kopirali vsem
- naših vrstic in prilepite, tako da
- prvi vlookup deluje drugi
- ena vlookup ne deluje, če na
- zgrabite Afganistan od mene in ga
- gre do konca in zagrabi vrednost
- od tik nad menoj, dokler ga ne dobi
- ki deluje in nato preklopi in
- stikala in stikala in stikala
- tukaj ni bilo nič težkega, kar ste poskušali
- naredite ta test pretežkim, da ga ne morem
- povem, da ime testa ni nikoli
- bo tako težko
- poiščite enostavno rešitev Mike
- poglej, kaj imaš
- hvala ja res če a in
- vlookup so pot do Excel 2013 ali
- kasneje mislim, da je to tisto, kar je izpit
- ljudje iščejo zdaj grem
- na naslednji list in naredite nekaj
- popolnoma smešna ta formula verjetno
- niti ne bo mogel razumeti
- ljudje, ki zdaj ocenijo vaš izpit, kaj
- V tem stolpcu bi rad med kopiranjem
- formula navzdol je uporaba običajnega
- vlookup, ampak želim interno znotraj
- formula Želim, da formula vedno dobi
- zadnji element besedila, ko kopiram formulo
- ko pridem do te celice in tega
- celica v tej celici je zadnje besedilo
- vrednost, ampak ko pridem sem, mora
- imajo to kot videz tega in potem
- boom boom boom iskalna vrednost še vedno
- se mora ozreti nazaj na to sekiro in
- način, kako bom to naredil, je, da uporabim
- prvotna funkcija iskanja je to
- originalna funkcija, ki sta jo Bricklin in
- Frank stoji v prvi različici
- od visie calc pred vlookupom in H
- lookup and the lookup function only does
- approximate match lookup it doesn't do
- exact or approximate like vlookup the
- advantage to approximate match lookup is
- in the lookup value if you put a value
- that's bigger than anything in the
- actual array or result vector it'll
- always get the last one so watch this
- for the lookup value I'm going to end
- double quotes and so I'm looking up text
- I'm zze now I'm using zze because I know
- none of the ISO
- country codes will ever start with Z Z Z
- so this is going to be bigger than
- anything ever entered into this column
- now comma and I'm using the array
- version I'm gonna click on cell a2 colon
- close parentheses click on the first a
- to hit the f4 key to lock it just the
- first cell reference now as I copy this
- down that blue range will expand since
- all of these cells are empty Z Z Z will
- always return a F because that's the
- last text item in the column control
- enter notice that's a lookup value as I
- copy down now inside of our formula we
- actually have the correct lookup value
- all the way down if we come down to some
- cell and hit f2 you can see there's the
- expandable range and sure enough lookup
- is getting the last text item entered
- now I'm going to come to the top f2 that
- whole thing is the lookup value that I'm
- going to use inside a vlookup now I come
- to the end comma table array I come over
- to a lookup table highlight the table
- I've up here in the formula bar and hit
- f4 comma 2 because the second column has
- the thing I need to get come 0 that
- means exact match close parentheses
- control enter and I'm gonna copy it all
- the way down and look at that now f2
- that's much too complicated that's a
- crazy formula when all you need is if
- and a and vlookup however there is a
- situation I'm gonna go over to XLS one
- two here there is a situation where this
- will get you into trouble and if I copy
- this down notice it's all Afghanistan
- and look at that what is going on there
- well we actually if we put this in edit
- mode there's an extra space there so
- there's some situations where we
- actually want to see the n/a air so if
- that was possibly the case then we'd
- need to use a slightly different f2
- logical test now I'm going to click on
- value and copy that because that's the
- vlookup we're still gonna use that ctrl
- C escape and I'm going to use a
- different logical test this is how we
- had to do it before Excel 2013 is blank
- now is blank only will deliver a true
- when it sees a cell is totally empty
- close parentheses if it is blank like it
- is down here comma then what do I want
- to look one cell above comma otherwise
- control V do the vlookup close
- parentheses control enter
- double click and send it down so in that
- case we are prompted we know that
- there's a problem here now I can come
- here backspace enter and now everything
- works and I'm gonna copy it down control
- home that formula is a little bit longer
- than just if an a we had to use three
- functions but in some circumstances you
- might want to take the extra time to
- create this longer formula then if n/a
- and vlookup I'm sure on the test that
- either one of those will work but this
- is the one you probably don't wanna use
- all right I'm gonna throw it back to mr.
- Excel well I ate Mike those were a
- couple of cool formulas but again I
- think the test was looking for the easy
- way the if na and like you said if you
- dejansko postavili tisto formulo, ki je ne bi
- imeti namig, da bi to dejansko
- delo, zahvaljujem se vsem, ki so se ustavili
- za ustavljanje preživetje se vidimo naslednjič
- za nov dvoboj excel podcast iz
- MrExcel excel je zabaven
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Duel189.xlsx
