To je težava s proračunom gasilcev. Ljudje v gasilskem domu so v Excelu napačno delali proračun. Izjemna preobrazba Power Query ponuja rešitev.
Oglejte si video
- Steve mora sešteti številke, ki so bile vnesene v besedilni stolpec
- V vsaki celici je več vrstic, ločenih z alt = "" + Enter
- Te vrstice morate razdeliti na vrstice in nato razčleniti dolarski znesek od sredine vsake celice
- Povzetek po stroškovnem mestu
- Izdelajte iskalno tabelo
- Pridobite vsote iz iskalne tabele z uporabo IFNA, da prezrete napake v prazni vrstici
- Bonus: dodajte makro dogodka, da posodobite delovni list, ko spremenijo celico.
Video zapis
Naučite se iz Excela, Podcast Episode 2160: SUM Podatki, ki so bili spremenjeni + vneseni.
Zdravo. Dobrodošli nazaj na netcast. Jaz sem Bill Jelen. Tega si ne izmišljujem. Od nekoga, ki ima podatke - podatke o proračunu - je videti takole. Zdaj sem dal lažne besede, tako da nimamo njihovih proračunskih informacij, ampak oseba, ki je nova v računovodstvu, je šla v podjetje in to podjetje že leta dela takšen proračun. Niso računovodje, ki delajo proračun, so redni ljudje, vendar to počnejo tako in jih ne more prepričati, da se spremenijo. Torej, tu je naš cilj. Pravi, da je to tako slabo kot vnos proračuna v Word.
No, skoraj, a na srečo bo zaradi poizvedbe o napaki rešil naš problem. Tukaj je naš cilj. Za vsak COST CENTER tukaj želimo sporočiti skupno število vseh teh številk. Torej, tu je ime stroškov, a -, rutinsko a -, nato znak $, nato pa samo, da življenje postane vsake toliko časa naključna opomba; ne ves čas, le nekaj časa. Prazna vrstica med vsakim. Tone in tone podatkov.
Torej, tukaj bom naredil nekaj. Spustil se bom na čisto dno, v zadnjo celico, izbral bom vse te stvari, vključno z naslovi. Ustvaril bom NAME. Poklical ga bom MyData. MyData, takole, v redu? Vredu. Zdaj bomo uporabili napajalno poizvedbo, ki je v letih 2010 ali 2013 brezplačna in je vgrajena v 2016 in 2016 v Office 365. To bo prišlo iz TABELE ALI RAZNOG. Vredu. Prva stvar, kadar koli imamo tiste praznine v STOLPCU A, vse NULLE, ki se jih želimo znebiti. Torej bom odkljukal NULL. Super V redu. Resnično, v teh podatkih, v tej različici podatkov, ker bom zgradil VLOOKUP, tega stolpca ne potrebujemo. Torej, z desno miškino tipko se znebim stolpca, zato ODSTRANI stolpec.
Vredu. Zdaj se bo tu zgodila čudna čarovnija. Izberite ta stolpec, RAZDELI STOLPEC Z OMEJITEV, in zagotovo bomo šli v NAPREDNO. Ločilo bo poseben znak in razdelili bomo vsak pojav ločila. Torej, mislim, da so to dejansko že ugotovili, ker sem jo razširil, vendar vam bom pokazal. VSTAVITE POSEBEN ZNAK. Rekel bom, da je LINE FEED, v redu, torej, ob vsakem pojavu LINE FEED in se bom razdelil v vrstice. V redu, in kaj vse se bo tukaj zgodilo, je 1, 2, 3, 4, 5, dobil bom 5 vrstic ali rekel 1001, v vsaki vrstici pa bo imel drugačen črta iz te celice. To je neverjetno. Obstaja 1, 2, 3, 4, 5, 1001. V redu. Zdaj moramo le razčleniti tega slabega fanta. Vredu,torej izberite ta stolpec, RAZDELI STOLPEC Z DELOMETOM. Tokrat bo ločilo znak $. To je popolno, enkrat ob prvem znaku $, ki ga najdemo, samo v primeru, da je v prihodnjem delu znak $. Razdelili se bomo na stolpce. Kliknite V redu. Vredu. Torej, obstajajo podrobnosti. Tu je naš denar.
Zdaj bom to razdelil na SPACE. Torej, izberite ta stolpec, RAZDELI STOLPEC Z DELIMERJEM in ločilo bo PROSTOR, ja, enkrat na LEVI-NAJBOLJŠI DELIMITER kliknite V redu in teh komentarjev ne potrebujem, tako da tiste komentarje greš ODSTRANITI. Pravzaprav tudi tega ne rabim, ker samo poskušam dobiti vse te stvari, zato bom ODSTRANIL.
Zdaj se preobrazi. SKUPINA PO STROŠKOVNEM SREDIŠČU, IME NOVEGA STOLPCA se bo imenovalo SKUPAJ, OPERACIJA bo SUM in v kateri stolpec bomo sešteli? PODROBNOSTI 2.1. Čudovito. Kliknite V redu, v redu, in na koncu dobimo eno vrstico na CENTRU STROŠKOV s SKUPAJ vseh teh postavk. DOM, ZAPRTO IN NAKLON. Verjetno bo vstavil nov delovni list. Upam, da vstavi nov delovni list, in to tudi stori, in ta delovni list se imenuje MYDATA_1. MYDATA_1.
Vredu. Zdaj se bomo vrnili sem k prvotnim podatkom in izvedli te korake. Že na prvem mestu = = VLOOKUP 1001 v naše rezultate. To je nekako tako kot postavitev krožne reference, vendar nam ne bo dala krožne reference. , 2, LAŽNO. Hočem natančno ujemanje. V redu, vendar tega ne bomo želeli narediti za prazne celice. Torej, rekel bom, no, pravzaprav, samo do konca ga prepišite. CONTROL + C, pojdite do konca navzdol, da vidite, kaj dobimo. Mogoče dobimo N / As in se ga lahko znebim z IFNA. Ja, čudovito, v redu. Torej, samo znebimo se N / As. Če ni, potem želimo samo “”. Tam nočemo ničesar. CONTROL + ENTER. Vredu. To bi moralo biti SKUPAJ. Poglejmo, ali lahko najdemo kratkega in samo izračunamo. = 627,37 + 7264,25 + 6066.01 + 4010,66 + 9773,94, SKUPAJ pa 27742,23 je to. Presneto super. (= IFNA (VLOOKUP (A2, MyData_1,2, FALSE), “”))
Zdaj je tukaj dogovor. Torej, imamo tiste ljudi iz vrst, ki so tu zunaj in se kaj spreminjajo. Recimo, da gredo skozi in spremenijo proračun, 40294,48, in pridejo sem in spremenijo tega na 6000, tako, in dodajo novo, ALT + ENTER, NEKAJ - znak $, 1000 $ pravkar dodano. Vredu. Zdaj, ko pritisnem ENTER, se ta številka, 40294.48, ne bo posodabljala, ampak kar moramo storiti, je, da gremo na zavihek PODATKI in želimo OSVEŽITI VSE. Torej, 40294,48. Pazi, glej, glej, glej. OSVEŽI VSE. Čudovito neverjetno.
Obožujem poizvedbe o moči. Poizvedba moči je najbolj neverjetna stvar. Ti podatki, ki so v bistvu tako kot besedni podatki v celici, jih zdaj posodabljamo. Verjetno bi lahko celo naredili nekakšen makro, ki pravi, da vsakič, ko nekdo kaj spremeni v STOLPCU C, nadaljujemo in s pomočjo makra kliknemo OSVEŽI VSE in te rezultate nenehno osvežujemo.
Kakšno grozljivo vprašanje je bilo poslano. Slabo se počutim Steveu, ki se mora spoprijeti s tem, toda zdaj lahko z uporabo poizvedbe o napajanju v storitvi Office 365 ali prenesene za leto 2010 ali 2013 to zelo enostavno rešite.
Počakaj. V redu, dodatek: naredimo to še boljše. Ta list se imenuje DATA in delovni zvezek sem shranil kot omogočen z makro, torej xlsm. Če ste xlsx, ne preskočite shranjevanja kot xlsm. ALT + F11. Poiščite delovni zvezek z imenom PODATKI, dvokliknite, zgoraj levo, DELOVNI LIST in nato SPREMENITE kadar koli spremenimo delovni list, in rekli bomo AKTIVNA DELAVNICA.REFRESHALL, nato zapremo, v redu, zdaj pa poskusimo. Urediva nekaj. Torej, vzeli bomo tiste maline, ki jih je trenutno 8000, in jih bomo spremenili na 1000, torej zmanjšamo za 7000. Ko pritisnem ENTER, želim, da se 42.000 spusti na 35.000. Ah. Super
No, hej. Tu vas ponavadi prosim, da kupite mojo knjigo, danes pa vas prosim, da kupite knjigo mojih prijateljev - Ken Puls in Miguel Escobar - M je za (DATA) MONKEY. Vse, kar sem se naučil o poizvedbah o moči, sem se naučil iz te knjige. To je neverjetna knjiga. Preveri to.
Zaključek epizode: Steve ima v seštevku vnesene številke, ki so bile vnesene v besedilni stolpec; več vrstic v vsaki celici, ločenih z ALT + ENTER; te vrstice morate razdeliti na vrstice, nato razčlenite dolarski znesek od sredine vsake celice; povzetek po COST CENTRU; izdelati iskalno tabelo; pridobite vsote iz iskalne tabele z uporabo IFNA, da prezrete napake v prazni vrstici; in nato bonus, makro na koncu, makro dogodka za posodobitev delovnega lista, ko spremenijo celico.
Želim se zahvaliti Steveu, da je poslal to vprašanje, in tako sem vesel, da imam odgovor - pred poizvedbo o napajanju bi bilo res zelo težko - in zahvaljujem se vam, da ste se ustavili. Se vidimo naslednjič za še eno oddajo iz.
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2160.xlsm
