See on tuletõrjujate eelarveprobleem. Tuletõrjehoones olevad inimesed on Excelis oma eelarvet valesti teinud. Lahenduse pakub hämmastav Power Query teisendus.
Vaata videot
- Steve peab summeerima tekstiveergu sisestatud numbrid
- Igas lahtris on mitu rida, eraldatud alt = "" + Enter
- Vaja need read ridadeks jagada, seejärel sõeluge dollari summa iga lahtri keskelt
- Kokkuvõte kulukeskuse järgi
- Koostage otsingulaud
- Hankige otsingutulemuste kogusummad, tühja rea vigade eiramiseks IFNA abil
- Boonus: töölehe värskendamiseks lisage sündmuse makro, kui nad lahtrit muudavad.
Video ärakiri
Õppige Exceli kohta, Podcast Episode 2160: SUM-andmed, mis on olnud Alt + sisestatud.
Hei. Tere tulemast tagasi netisaate juurde. Olen Bill Jelen. Ma ei mõtle seda välja. Sain kelleltki, kellel on andmeid - eelarveandmeid - küsimus, mis näeb välja selline. Nüüd panin siia võltssõnad, et meil ei oleks tema eelarveteavet, vaid inimene oleks raamatupidamises uus, läksin ettevõttesse ja see ettevõte on aastaid oma eelarvet niimoodi teinud. Nad ei ole raamatupidajad, kes eelarvet teevad, nad on reainimesed, kuid nii nad on seda teinud ja ta ei saa neid muuta. Nii, siin on meie eesmärk. Tema sõnul on see sama halb kui eelarve Wordi sisestamine.
Noh, peaaegu, kuid õnneks säästab see tänu energiapäringule meie probleemi. Siin on meie eesmärk. Iga siinse COST CENTERi kohta tahame esitada kõigi nende arvude koguarvu. Niisiis on kulunimi, -, rutiinselt a -, siis $ märk ja siis lihtsalt selleks, et elu huvitav oleks, iga natukese aja tagant juhuslik märge pärast; mitte kõik korrad, vaid osa ajast. Igaühe vahel tühi rida. Tonni ja tonni andmeid.
Niisiis, mida ma teen. Ma tulen alla kõige viimasesse lahtrisse, ma valin kogu selle kraami, sealhulgas pealkirjad. Kavatsen luua NIMI. Kutsun seda MyData. MyData, selline, eks? Hästi. Nüüd hakkame kasutama toitepäringuid, mis on 2010. või 2013. aastal tasuta ning mis on sisse ehitatud 2016. ja 2016. aasta Office 365. See tuleb TABELIST VÕI VALIKUST. Hästi. Esimene asi, igal ajal, kui meil on veerus A need tühjad kohad, kõik NULLID, millest me tahame lahti saada. Nii et eemaldan märke NULL. Vinge. Okei. Tõesti, nendes andmetes, selles andmete versioonis, kuna ma kavatsen ehitada VLOOKUPi, pole seda veergu vaja. Niisiis, ma paremklõpsan ja saan sellest veerust lahti, nii et Eemalda veerg.
Hästi. Siin juhtub kohutav maagia. Valige see veerg SPLIT COLUMN by DELIMITER, ja kindlasti läheme edasi ADVANCED-i. Piiraja saab olema erimärk ja me jagame eraldaja iga esinemise. Nii et siin ma arvan, et nad on selle juba välja mõelnud, sest ma laiendasin seda, aga ma näitan teile. SISESTA ERITUNNE. Ma ütlen, et see on JOONISÖÖT, olgu, nii, et RIDASÖÖDA igal esinemisel ja ma lähen RIDADEKS RIDADEKS. Hea küll, ja just see, mis siin juhtuma hakkab, on 1, 2, 3, 4, 5, ma saan 5 rida või ütlen 1001, kuid igas reas on sellel erinev sellest lahtrist. See on hämmastav. On 1, 2, 3, 4, 5, 1001. Hästi. Nüüd peame selle halva poisi lihtsalt sõeluma. Hästi,nii et valige see veerg SPLIT COLUMN BY DIMITERER. Seekord saab eraldaja olema $ märk. See on ideaalne, üks kord, kui leiame esimese $ -märgi, siis igaks juhuks, kui tulevases osas on seal $ -märk väljas. Me läheme SPLIT IN COLUMNS. Klõpsake nuppu OK. Hästi. Niisiis, on üksikasju. Siin on meie raha.
Nüüd jagan selle SPACE-s. Niisiis, valige see veerg SPLIT COLUMN BY DIMITERER ja eraldaja saab olema tühik, jah, klõpsake üks kord VASAK-KÕIGE KINDLAKS, klõpsake nuppu OK ja ma ei vaja neid kommentaare, nii et need kommentaarid lähen minema EEMALDA. Tegelikult pole seda ka vaja, sest ma lihtsalt üritan kogu seda kraami kokku saada, nii et ma lähen EEMALDAMA.
Nüüd muundu. RÜHMA KULUKESKUSE JÄRGI nimetatakse UUELE VEERGU NIMELE KOKKU, TEGEVUS saab olema SUM ja millisesse veergu läheme SUM? 2.1. Ilus. Klõpsake nuppu OK, olgu, lõpuks on üks rida COST CENTERIS ja kõigi nende ridade KOKKU. KODU, SULETUD JA LAADI. Tõenäoliselt lisatakse uus tööleht. Loodan, et see lisab uue töölehe ja nii see ka on, ning seda töölehte nimetatakse MYDATA_1. MYDATA_1.
Hästi. Nüüd läheme siia tagasi algandmete juurde ja teeme need toimingud. Kõige esimesel = VLOOKUP 1001 meie tulemustesse. See on umbes nagu ringviite seadistamine, kuid see ei anna meile ümmargust viidet. , 2, VALE. Ma tahan täpset vastet. Hästi, aga me ei taha seda tühjade lahtrite jaoks teha. Niisiis, ma ütlen, et tegelikult kopeerime selle lihtsalt kogu tee alla. CONTROL + C, minge lõpuni alla, et näha, mida me saame. Võib-olla saame N / As-i ja saan IFNA-ga sellest lahti. Jah, ilus, olgu. Niisiis, vabaneme lihtsalt N / As-st. Kui pole, siis tahame lihtsalt “”. Me ei taha sinna midagi. KONTROLL + ENTER. Hästi. Nüüd peaks see olema KOKKU. Vaatame, kas leiame lühikese ja lihtsalt matemaatika. = 627,37 + 7264,25 + 6066.01 + 4010,66 + 9773,94 ja see on KOKKU 27742,23. Freaking Awesome. (= IFNA (VLOOKUP (A2, MyData_1,2, FALSE), ""))
Siin on tehing. Nii et meil on neid liini inimesi, kes siin käivad, muudavad asju, olgu, ja ütleme nii, et nad käivad läbi ja muudavad eelarvet, 40294,48, ja tulevad siia ja muudavad selle 6000-le, niimoodi ja lisavad uus, ALT + ENTER, MIDAGI - märk $, just lisati 1000 dollarit. Hästi. Muidugi, kui ma vajutan sisestusklahvi ENTER, ei hakka see number 40294,48 värskendama, olgu, aga peame minema vahekaardile ANDMED ja me tahame KÕIK VÄRSKENDADA. Niisiis, 40294,48. Vaata, vaata, vaata, vaata. VÄRSKENDAGE KÕIKI. Hirmus hämmastav.
Mulle meeldib jõupäring. Jõupäring on kõige hämmastavam. Need andmed, mis on sisuliselt täpselt nagu lahtris olevad sõnaandmed, on neid nüüd värskendatud. Võib-olla võiksite isegi teha mingisuguse makro, mis ütleb, et iga kord, kui keegi veerus C midagi muudab, jätkame ja klõpsame makro abil VÄRSKENDA KÕIK ja need tulemused on pidevalt värskendatavad.
Milline kohutav küsimus saadeti. Mul on halb Steve jaoks, kes peab sellega tegelema, kuid nüüd, kasutades Office 365-s olevat toitepäringut või laadides alla 2010. või 2013. aastaks, on teil selle lahendamiseks väga-väga lihtne viis.
Oota. Okei, lisa: teeme selle veelgi paremaks. Selle lehe nimi on DATA ja ma olen töövihiku salvestanud makro-toega, nii et xlsm. Kui olete xlsx, siis ärge jätke xlsm-na salvestamist vahele. ALT + F11. Leidke töövihik nimega DATA, topeltklõpsake, vasakul ülaservas, TÖÖLEHT ja siis MUUDA alati, kui muudame töölehte, ja ütleme, et ACTIVEWORKBOOK.REFRESHALL, ja seejärel sulgege, olgu ja proovime nüüd. Redigeerime midagi. Niisiis võtame need vaarikad, mida praegu on 8000, ja muudame selle väärtuseks 1000, seega vähendame 7000 võrra. Kui ma vajutan sisestusklahvi ENTER, tahan näha, et 42 000 langeb 35 000-ni. Ah. Vinge.
Noh, hei. Siit ma tavaliselt palun, et ostaksite minu raamatu, kuid täna palun teil osta minu sõprade raamat - Ken Puls ja Miguel Escobar - M on (DATA) MONKEY jaoks. Kõik, mida õppisin voolupäringute kohta, õppisin sellest raamatust. See on hämmastav raamat. Kontrollige seda.
Jagude kokkuvõte: Steve'il on summad, mis on tekstiveergu sisestatud; mitu rida igas lahtris, eraldatuna tähtedega ALT + ENTER; peate need read ridadeks jagama, seejärel sõeluge dollari summa iga lahtri keskelt; kokkuvõtte COST CENTER; ehitada otsingulaud; hankige otsingutulemuste kogusummad, kasutades IFNA-d tühja rea vigade ignoreerimiseks; ja seejärel boonus, makro lõpus, sündmuse makro töölehe värskendamiseks, kui nad lahtrit muudavad.
Ma tahan tänada Steve'i selle küsimuse saatmise eest ja mul on nii hea meel, et mul on vastus - enne voolupäringut oleks see olnud tõesti väga raske - ja ma tahan tänada teid peatumast. Järgmisel korral näeme järgmise võrguülekande jaoks.
Laadige fail alla
Laadige näidisfail alla siit: Podcast2160.xlsm
