Számítás (például VLOOKUP) elvégzése minden olyan elemhez, amelyet Alt + bevitt egy cellába.
Videót nézni
- A megtekintő adatokat tölt le egy rendszerből, ahol az egyes elemeket az Alt + Enter billentyűk választják el egymástól
- Bill: Miért csinálod ezt? Néző: Így öröklöm az adatokat. Így akarom megtartani.
- Bill: Mit akarsz kezdeni a táblázatban nem szereplő értékek 40% -ával? Néző: Nincs válasz
- Bill: Ennek megoldására bonyolult módszer áll rendelkezésre, ha rendelkezik a legújabb Power Query eszközökkel.
- Ehelyett egy VBA makró a megoldáshoz - a makrónak egészen az Excel 2007-ig kell működnie
- A VLOOKUP helyett végezzen egy sor Keresést és cserét VBA-val
Videó átirat
Ismerje meg az Excel programot, Podcast 2150. epizód: VLOOKUP Minden Alt + bevitt érték minden cellában.
Hé. Üdvözöljük a netcaston. Bill Jelen vagyok. Ma az egyik bizarrabb kérdés. Valaki azt mondta: hé, szeretnék egy VLOOKUP-ot elvégezni a cella minden értékéhez, és amikor kinyitottam az Excel fájlt, az adatok ALT + Enter értéket kaptak. Tehát 4 tétel van ebben a sorrendben, és mindegyiket elválasztja az ALT + ENTER, majd csak 2 itt és 6 itt és így tovább.
Visszatértem ahhoz a személyhez, aki ezt beküldte. Úgy tetszett, nos, ez nagyon rossz módszer az adatok tárolására. Miért csinálod ezt? És olyan volt, mintha nem csinálnám. Így tölti le az adatokat. Azt mondtam, nem baj, ha külön sorokra osztom? Nem, ezt meg kell tartania.
Rendben. Tehát nincs jó módja az egyes elemek VLOOKUP-jának elkészítésére, és holnap, a holnapi 2151-es részben megmutatom, hogyan használhatjuk a Power Query vadonatúj funkcióját erre, de hogy az Office 365 rendelkezzen ezzel.
Tehát ma egy olyan módszert szeretnék használni, amely egészen vissza fog menni, és amit itt tettem, létrehoztam egy új munkalapot a LOOKUPTABLE-vel, tehát ezek a tételek. Azt is észrevettem, hogy egy csomó dolog van, a dolgok körülbelül 40% -a itt nem szerepel a LOOKUPTABLE-ben. Azt mondtam, mit akarsz csinálni ott, és nem válaszoltam erre a kérdésre, ezért csak otthagyom őket, ahogy vannak, ha nem találok párost.
Rendben, tehát itt van az, hogy van egy LOOKUPTABLE nevű lapom, és látni fogja, hogy a fájlom most xlsx néven van tárolva, és VBA makrót fogok használni. A VBA makró használatához nem lehet xlsx. Ez ellentétes a szabályokkal. Tehát meg kell mentenie AS-ként, és ezt el kell mentenie, az xlsm. FÁJL, MENTEN MINT, és állítsa át MUNKAKÖNYVRŐL MAKROKÉPESSÉGRE VONATKOZÓ MŰKÖNYVRE XLSM, vagy BINÁRIS MUNKAKÖNYVRE - bármelyik is működik -, és kattintson a MENTÉS gombra.
Rendben, szóval, most engedélyezhetjük a makrók futtatását. ALT + F11 a makrórögzítőhöz való eljutáshoz. Ezzel a nagy szürke képernyővel indul. INSERT, MODULE, és ott van a modulunk, és itt van a kód. Tehát neveztem ReplaceInPlace-nek és meghatároztam egy munkalapot. Ez a LookupTable. Helyezné a valódi keresőtábla munkalap nevét, majd a keresőtáblám az A oszlopban kezdődik, ami az 1. oszlop. Tehát az 1. oszlop legutolsó sorához lépek, megnyomom az END gombot és a FEL nyíl természetesen a CONTROL + FEL nyíl ugyanazt tenné, kitalálná, hogy mi ez a sor, és akkor minden sorból 2-ről a FinalRow-ra megyünk. Miért 2? Nos, mivel a címsorok az 1. sorban vannak. Tehát szeretném lecserélni, a 2. sortól kezdve egészen az utolsó sorig, és így minden 2-től a FinalRow-ig terjedő sor esetében a FromValue az, amit 's az A oszlopban, a ToValue pedig a B oszlopban található érték.
Ha valamilyen oknál fogva az adatai J-ben és K-ban voltak, akkor ez a J lenne a 10. oszlop, tehát oda tegyen egy 10-et, a K pedig a 11. oszlop lenne, majd a Kiválasztásban cserélni fogjuk a FromValue-tól a ToValue-ig. Ez itt nagyon fontos. xlPart, xlPart - és ez egy L, nem pedig 1-es szám - xlPart, amely szerint lehetővé teszi számunkra, hogy lecseréljük a cella egy részét, mert ezeket a részszámokat mind sortávolsági karakter választja el. Annak ellenére, hogy nem látja, ott van. Tehát ez lehetővé teszi számunkra, hogy véletlenül ne frissítsük a rossz dolgot, majd xlByRows, MatchCase, False, SearchFormat, False, ReplaceFormat, False, Next i.
Rendben. Szóval, ez a mi kis makrónk itt. Próbáljuk ki. Elfogjuk ezeket az adatokat, és nem akarok semmit semmisíteni, ezért csak az eredeti adatokat fogom átvenni és jobbra másolni. Rendben. Tehát ott van a választékunk. Tulajdonképpen ettől a ponttól kezdem. CONTROL + BACKSPACE, majd az ALT + F8 az összes makró listájának megtekintéséhez. Ott van a REPLACEINPLACE. Kattintson a FUTÁS gombra, és mindenhol, ahol talált egy elemet a LOOKUPTABLE-ben, lecserélte az elem számát az elemre, látszólag VLOOKUP-ot hajtott végre, bár egyáltalán nem VLOOKUP-tal oldjuk meg.
Rendben. Szóval, hé, a vadonatúj könyv jelent meg - Power Excel With, a 2017-es kiadás, a 617 Excel rejtélyek megoldva - mindenféle remek új tipp.
Mai összefoglalás: a néző adatokat tölt le egy rendszerből, ahol minden elemet elkülönít egy ALT + ENTER, majd VLOOKUP-ot kell végeznie minden elemnél, és tudod, miért csinálom ezt; szóval az illető azt mondta: nem csinálom, de így kell tartanom; és akkor az értékek 40% -a nincs a táblázatban, nos, nincs válasz; úgyhogy azt hiszem, ezeket az elemeket hozzáadják az asztalhoz; most, holnap, arról fogunk beszélni, hogyan lehet ezt megoldani a Power Query segítségével, de ma ez a makró egészen az Excel összes Windows verziójában működni fog, legalábbis az Excel 2007-re tér vissza; tehát VLOOKUP helyett csak egy sor keresés és helyettesítés a VBA-val.
Hát hello. Szeretném megköszönni, hogy megálltál. Legközelebb találkozunk egy újabb netcast-lal.
Fájl letöltése
Töltse le a minta fájlt innen: Podcast2150.xlsm
