Tai gaisrininkų biudžeto problema. Gaisrinės žmonės „Excel“ programoje neteisingai daro savo biudžetą. Nuostabi „Power Query“ transformacija suteikia sprendimą.
Žiūrėti video
- Steve'as turi susumuoti skaičius, kurie buvo įrašyti į teksto stulpelį
- Kiekvienoje langelyje yra kelios eilutės, atskirtos alt = "" + Enter
- Reikia padalyti tas eilutes į eilutes, tada išanalizuoti dolerio sumą nuo kiekvienos langelio vidurio
- Apibendrinkite pagal išlaidų centrą
- Sukurkite paieškos lentelę
- Gaukite sumas iš paieškos lentelės, naudodami IFNA, kad nepaisytumėte klaidų tuščioje eilutėje
- Premija: pridėkite įvykio makrokomandą, kad atnaujintumėte darbalapį, kai jie pakeis langelį.
Vaizdo įrašo nuorašas
Sužinokite „Excel“ iš „Podcast“ serijos 2160: SUM duomenys, kurie buvo Alt + įvesti.
Ei. Sveiki sugrįžę į internetinę transliaciją. Aš esu Billas Jelenas. Aš to negalvoju. Gavau klausimą iš asmens, kuris turi duomenis - biudžeto duomenis - kuris atrodo taip. Dabar čia įdedu netikrus žodžius, kad neturėtume informacijos apie biudžetą, bet asmuo, kuris yra naujas apskaitos skyriuje, nuėjo į įmonę, o ši įmonė jau daugelį metų taip tvarko savo biudžetus. Jie nėra buhalteriai, darantys biudžetą, jie yra eiliniai žmonės, tačiau jie taip darė, ir jis negali priversti jų pasikeisti. Taigi, čia yra mūsų tikslas. Jis sako, kad tai yra taip blogai, kaip įrašyti biudžetą į „Word“.
Na, beveik, bet, laimei, energijos paklausos dėka tai išgelbės mūsų problemą. Čia yra mūsų tikslas. Kiekvienam čia esančiam COST CENTRUI norime pranešti visų šių skaičių sumą. Taigi, ten yra išlaidų pavadinimas, -, įprastai a -, tada $ ženklas ir tada, kad gyvenimas būtų įdomus, kiekvieną kartą, atsitiktinis užrašas po; ne visus kartus, o tik dalį laiko. Tuščia eilutė tarp kiekvienos. Tonų ir tonų duomenų.
Taigi, štai ką aš darysiu. Aš nusileisiu į patį dugną, į paskutinę langelį, atrinksiu visus šiuos dalykus, įskaitant antraštes. Aš ketinu sukurti PAVADINIMĄ. Aš tai vadinsiu „MyData“. „MyData“, tokia, gerai? Gerai. Dabar mes naudosime maitinimo užklausą, kuri yra nemokama 2010 m. Arba 2013 m., Integruota į 2016 m. Ir 2016 m. „Office 365“. Tai bus pateikta iš LENTELĖS ARBA diapazono. Gerai. Pirmas dalykas, bet kada, kai A kolonoje yra tų tuščių vietų, visi NULLAI, nuo kurių norime atsikratyti. Taigi nuimsiu NULL žymėjimą. Nuostabu. Gerai. Tikrai, šiuose duomenyse, šioje duomenų versijoje, nes aš ruošiu VLOOKUP, mums nereikia šio stulpelio. Taigi, aš dešiniuoju pelės mygtuku spustelėkite ir atsikratysiu to stulpelio, taigi REMOVE column.
Gerai. Štai čia bus keista magija. Pasirinkite šį stulpelį „SPLIT COLUMN BY A DELIMITER“ ir mes tikrai pereisime prie IŠPLĖSTINIŲ. Skiriamasis ženklas bus ypatingas simbolis, ir mes padalinsime kiekvieną skiriamąjį elementą. Taigi, manau, kad jie iš tikrųjų jau tai suprato, nes aš jį išplėtiau, bet aš jums parodysiu. ĮDĖKITE SPECIALIĄ CHARAKTERĮ. Aš pasakysiu, kad tai yra LINIJINIS PAŠARAS, gerai, taigi, kiekvienu eilutės pašaro atveju, aš eisiu į SKELBIMAS Į EILES. Gerai, ir tai, kas čia nutiks, yra 1, 2, 3, 4, 5, gausiu 5 eiles arba sakysiu 1001, bet kiekvienoje eilutėje ji bus kitokia linija nuo šios ląstelės. Tai yra nuostabu. Yra 1, 2, 3, 4, 5, 1001. Gerai. Dabar mums tiesiog reikia ištirti šį blogą berniuką. Gerai,Taigi, pasirinkite tą stulpelį: SPLIT COLUMN BY DELIMITER. Šį kartą ribotuvas bus $ ženklas. Tai yra puiku, kartą, kai randame pirmąjį $ ženklą, tik tuo atveju, jei būsimoje dalyje bus $ ženklas. Mes eisime į SKILTIS. Spustelėkite Gerai. Gerai. Taigi, yra detalių. Čia mūsų pinigai.
Dabar aš tai padalysiu į SPACE. Taigi, pasirinkite šį stulpelį „SPLIT COLUMN BY A DELIMITER“, o ribotuvas bus tarpas, taip, vieną kartą kairiajame-didžiausiame ribotuve spustelėkite Gerai, ir man tų komentarų nereikia, todėl tuos komentarus mes vėl einu į PAŠALINTI. Tiesą sakant, ir to nereikia, nes aš tiesiog stengiuosi gauti viso to dalyko, taigi aš nuimsiu.
Dabar transformuokis. GRUPĖ PAGAL KAINŲ CENTRĄ, NAUJAS SKILTIES PAVADINIMAS bus pavadintas IŠ VISO, VEIKLA bus SUMA, o į kurį stulpelį eisime SUM? 2.1. Graži. Spustelėkite Gerai, gerai, ir tai, ką mes gauname, yra viena eilutė kiekvienam COST CENTERIUI su TOTAL visų tų eilučių elementais. NAMAI, UŽDARYTI IR ĮKELTI. Tai tikriausiai ketina įterpti naują darbalapį. Tikiuosi, kad jis įdės naują darbalapį, ir jis yra, ir tas darbalapis vadinamas MYDATA_1. MYDATA_1.
Gerai. Dabar grįšime į pradinius duomenis ir atliksime šiuos veiksmus. Pirmajame = 1001 VLOOKUP į mūsų rezultatus. Tai panašu į tai, kaip nustatyti apskritą nuorodą, tačiau ji nesuteiks mums apskritos nuorodos. , 2, NETIESA. Noriu tikslios atitikties. Gerai, bet mes nenorėsime to padaryti tuščioms langeliams. Taigi, aš pasakysiu, kad iš tikrųjų, tiesiog nukopijuokime jį visą kelią. „CONTROL + C“ nusileiskite iki galo, kad pamatytumėte, ką gauname. Gal mes gauname N / As ir aš galiu to atsikratyti naudodamas IFNA. Taip, gražu, gerai. Taigi, tiesiog atsikratykime N / As. Jei nėra, tada mes tiesiog norime „“. Mes nieko ten nenorime. CONTROL + ENTER. Gerai. Dabar tai turėtų būti IŠ VISO. Pažiūrėkime, ar galime rasti trumpą ir tiesiog atlikti matematiką. = 627,37 + 7264,25 + 6066.01 + 4010,66 + 9773,94, o IŠ VISO 27742,23. Freaking Awesome. (= IFNA (VLOOKUP (A2, „MyData_1,2“, FALSE), „“))
Dabar čia yra sandoris. Taigi, turime tuos eilinius žmones, kurie čia keičiasi, gerai, ir sakykime, kad jie išgyvena ir keičia biudžetą, 40294,48, ir jie ateina čia ir keičia šį tą į 6000, ir panašiai naujas, ALT + ENTER, KAŽKAS - $ ženklas, ką tik pridėta 1000 USD. Gerai. Žinoma, dabar, kai paspaudžiu ENTER, šis numeris 40294.48 neketina atnaujinti, gerai, bet ką turime padaryti, tai eikite į skirtuką DUOMENYS ir norime ATNAUJINTI VISUS. Taigi, 40294,48. Žiūrėti, žiūrėti, žiūrėti, žiūrėti. ATNAUJINTI VISUS. Keista nuostabu.
Man patinka galios užklausa. Galios užklausa yra pats nuostabiausias dalykas. Šie duomenys, kurie iš esmės yra panašūs į žodžio duomenis langelyje, dabar juos atnaujiname. Jūs tikriausiai netgi galite sukurti tam tikrą makrokomandą, kurioje sakoma, kad kiekvieną kartą, kai kas nors pakeičia kažką C stulpelyje, mes einame į priekį ir spustelėsime ATNAUJINTI VISUS naudodami makrokomandą ir tiesiog turime tuos rezultatus nuolat, nuolat atnaujindami.
Koks siaubingas klausimas išsiųstas. Man blogai Steve'ui, kuriam tenka tai spręsti, tačiau dabar, naudodamiesi „PowerPoint“ užklausa „Office 365“ arba atsisiųsta 2010 ar 2013 m., Turite labai, labai lengvą būdą tai išspręsti.
Laukti. Gerai, priedas: padarykime tai dar geriau. Šis lapas vadinamas DATA ir aš išsaugojau darbaknygę kaip įgalintą makrokomandą, taigi xlsm. Jei esate xlsx, nepraleiskite išsaugojimo kaip xlsm. ALT + F11. Suraskite darbaknygę DATA, dukart spustelėkite, viršuje kairėje, WORKSHEET ir tada CHANGE, kai tik pakeisite darbalapį, ir sakysime ACTIVEWORKBOOK.REFRESHALL, tada uždarykite, gerai ir dabar pabandykime. Redaguokime ką nors. Taigi, mes paimsime tas avietes, kurių šiuo metu yra 8 000, ir pakeisime jas į 1000, taigi sumažinsime 7 000. Kai paspausiu ENTER, noriu pamatyti, kad 42 000 sumažėjo iki 35 000. Ak. Nuostabu.
Na, ei. Čia dažniausiai prašau jūsų nusipirkti mano knygą, bet šiandien paprašysiu jūsų nusipirkti mano draugų knygą - Ken Puls ir Miguel Escobar - M skirta (DUOMENYS) PELKINIUI. Viską, ką sužinojau apie energijos paklausą, sužinojau iš šios knygos. Tai nuostabi knyga. Patikrinkite tai.
Epizodo užbaigimas: Steve'as turi susumuoti skaičius, kurie buvo įvesti į teksto stulpelį; po kelias eilutes kiekvienoje langelyje, atskirtos ALT + ENTER; reikia padalyti tas eilutes į eiles, tada išanalizuoti dolerio sumą nuo kiekvienos langelio vidurio; apibendrinti COST CENTER; sukurti paieškos lentelę; gaukite sumas iš paieškos lentelės, naudodami IFNA, kad nepaisytumėte klaidų tuščioje eilutėje; tada premija, makrokomanda pabaigoje, įvykio makrokomanda, skirta atnaujinti darbalapį, kai jie pakeičia langelį.
Noriu padėkoti Steve'ui už išsiųstą šį klausimą ir labai džiaugiuosi, kad turiu atsakymą - prieš galios užklausą tai būtų buvę tikrai labai sunku, ir noriu padėkoti už tai, kad užsukote. Pamatysime kitą kartą kitai internetinei transliacijai iš.
Atsisiųsti failą
Atsisiųskite failo pavyzdį čia: Podcast2160.xlsm
