Aceasta este problema bugetului pompierilor. Oamenii dintr-o casă de pompieri și-au făcut greșit bugetele în Excel. O transformare uimitoare a Power Query oferă soluția.
Urmăriți videoclipul
- Steve trebuie să însumeze numerele care au fost introduse într-o coloană de text
- Există mai multe linii în fiecare celulă, separate prin alt = "" + Enter
- Trebuie să împărțiți aceste rânduri în rânduri, apoi analizați suma în dolari din mijlocul fiecărei celule
- Rezumați în funcție de centrul de costuri
- Construiți un tabel de căutare
- Obțineți totaluri din tabelul de căutare, folosind IFNA pentru a ignora erorile din rândul gol
- Bonus: Adăugați un macro eveniment pentru a actualiza foaia de lucru atunci când schimbă o celulă.
Transcriere video
Aflați Excel din, Podcast Episodul 2160: SUM Date care au fost Alt + introduse.
Hei. Bine ați venit înapoi pe netcast. Eu sunt Bill Jelen. Nu inventez asta. Am primit o întrebare de la cineva care are date - date bugetare - care arată astfel. Acum, am introdus cuvinte false aici, astfel încât să nu avem informațiile despre bugetul lor, dar persoana nouă în departamentul de contabilitate, a mers la o companie, iar această companie de ani de zile își face bugetele astfel. Nu sunt contabili care fac bugetul, sunt oameni de linie, dar așa au făcut-o, iar el nu le poate determina să se schimbe. Deci, iată scopul nostru. El spune că acest lucru este la fel de rău ca introducerea bugetului în Word.
Ei bine, aproape, dar din fericire, datorită interogării de energie, ne va salva problema. Iată obiectivul nostru. Pentru fiecare COST CENTER de aici, vrem să raportăm totalul acestor numere. Deci, există numele cheltuielilor, un -, în mod obișnuit un -, apoi un semn $ și apoi, doar pentru a face viața interesantă, din când în când, o notă aleatorie după; nu de fiecare dată, doar o parte din timp. Rând gol între fiecare. Tone și tone de date.
Deci, iată ce voi face. Voi coborî până la capăt, până la ultima celulă, voi selecta toate aceste lucruri, inclusiv titlurile. Voi crea un NUME. O voi numi MyData. MyData, așa, bine? Bine. Acum vom folosi interogarea de energie care este gratuită în 2010 sau 2013, încorporată în 2016 și 2016 Office 365. Aceasta va proveni dintr-un TABEL SAU GAMĂ. Bine. Primul lucru, de fiecare dată când avem aceste spații libere în COLONNA A, toate NULL-urile de care vrem să scăpăm. Așa că am să debifez NULL. Minunat. Bine. Într-adevăr, în aceste date, în această versiune a datelor, pentru că voi construi un VLOOKUP, nu avem nevoie de această coloană. Deci, voi face clic dreapta și voi scăpa de acea coloană, deci ÎNLĂTURAȚI coloana.
Bine. Acum, aici se va întâmpla magia ciudată. Alegeți această coloană, SPITĂ CULOANĂ CU UN DELIMITATOR, și cu siguranță vom intra în AVANSAT. Delimitatorul va fi un personaj special și vom împărți fiecare apariție a delimitatorului. Deci, aici, cred că de fapt și-au dat seama deja pentru că l-am extins, dar am să vă arăt. INSERAȚI CARACTER SPECIAL. Voi spune că este o LINE FEED, bine, deci, la fiecare apariție a LINE FEED, și voi merge la SPLIT INTO ROWS. Bine, și ceea ce se va întâmpla aici este, 1, 2, 3, 4, 5, voi primi 5 rânduri sau voi spune 1001, dar, în fiecare rând, va avea un alt linie din această celulă. Este uimitor. Există 1, 2, 3, 4, 5, 1001. Bine. Acum trebuie doar să-l analizăm pe acest băiat rău. Bine,deci, alegeți coloana respectivă, SPALATĂ CULOANA CU UN DELIMITATOR. De data aceasta, un delimitator va fi un semn $. Este perfect, odată, la primul semn $ pe care îl găsim, doar în cazul în care există un semn $ acolo în partea viitoare. Mergem la SPLIT INTO COLUMNS. Faceți clic pe OK. Bine. Deci, există detalii. Iată banii noștri.
Acum, voi împărți acest lucru la SPACE. Așadar, alegeți această coloană, SPITĂ CULOANĂ CU UN DELIMITATOR, iar delimitatorul va fi un spațiu, da, o dată la STÂNGA-CEL MAI DELIMITATOR, faceți clic pe OK și nu am nevoie de acele comentarii acolo, așa că acele comentarii sunt Voi elimina. De fapt, nici nu aveți nevoie de asta, pentru că încerc doar să obțin un total din toate aceste lucruri, așa că voi elimina.
Acum, transformă-te. GRUPUL PE CENTRUL DE COSTURI, NUMELE NOUĂ DE COLOANĂ se va numi TOTAL, OPERAȚIA va fi SUMĂ și la ce coloană vom merge la SUMĂ? DETALII 2.1. Frumos. Faceți clic pe OK, în regulă, iar cu ce ajungem este un rând pe COST CENTER cu TOTALUL tuturor acelor elemente rând. HOME, CLOSE & LOAD. Probabil va introduce o nouă foaie de lucru. Sper că va introduce o nouă foaie de lucru și o va face, iar foaia de lucru respectivă se numește MYDATA_1. MYDATA_1.
Bine. Acum vom reveni aici în datele originale și vom face acești pași. Pe primul, = VLOOKUP of 1001 în rezultatele noastre. Acest lucru este ca și cum ai configura o referință circulară, dar nu ne va oferi o referință circulară. , 2, FALS. Vreau potrivirea exactă. Bine, dar nu vom dori să facem asta pentru celulele goale. Deci, am să spun că, de fapt, hai să o copiem până la capăt. CONTROL + C, mergeți până jos doar pentru a vedea ce primim. Poate primim N / As și pot scăpa de asta cu IFNA. Da, frumos, în regulă. Deci, să scăpăm de N / As. Dacă N / A, atunci vrem doar „”. Nu vrem nimic acolo. CONTROL + ENTER. Bine. Acum, acesta ar trebui să fie TOTAL. Să vedem dacă putem găsi unul scurt și doar să facem calculele. = 627,37 + 7264,25 + 6066.01 + 4010,66 + 9773,94, iar TOTAL, 27742,23 este acela. Freaking Awesome. (= IFNA (VLOOKUP (A2, MyData_1,2, FALSE), „”))
Acum, iată afacerea. Deci, avem acei oameni de linie care sunt aici schimbând lucruri, bine, și așa să spunem că trec și schimbă bugetul, 40294,48, și vin aici și îl schimbă pe acesta la 6000, așa, și adaugă una nouă, ALT + ENTER, Ceva - semn $, tocmai s-au adăugat 1000 $. Bine. Acum, bineînțeles, când apăs ENTER, acest număr, 40294.48, nu se va actualiza, bine, dar ceea ce trebuie să facem este să mergem la fila DATA și vrem să REFRESH ALL. Deci, 40294,48. Privește, privește, privește, privește. ÎMPĂRȚIȚI TOATE. Freaking uimitor.
Ador interogarea de putere. Interogarea cu energie este cel mai uimitor lucru. Aceste date, care în esență sunt exact ca datele de cuvinte dintr-o celulă, le avem acum actualizate. Puteți face chiar și un fel de macro care spune că de fiecare dată când cineva schimbă ceva în CULOANA C, mergem mai departe și facem clic pe REFRESH ALL folosind macro-ul și avem doar aceste rezultate în mod constant, reîmprospătând în mod constant.
Ce întrebare oribilă a fost trimisă. Mă simt rău pentru Steve care trebuie să se ocupe de acest lucru, dar acum, folosind interogarea de alimentare în Office 365 sau descărcată pentru 2010 sau 2013, aveți un mod foarte, foarte ușor de a rezolva acest lucru.
Aștepta. Bine, un act adițional: hai să-l îmbunătățim și mai bine. Această foaie se numește DATA și am salvat registrul de lucru ca macro-activat, deci xlsm. Dacă sunteți xlsx, nu omiteți salvarea ca xlsm. ALT + F11. Găsiți registrul de lucru numit DATA, faceți dublu clic, în stânga sus, FIȘĂ DE LUCRU, apoi SCHIMBAȚI de fiecare dată când schimbăm foaia de lucru și vom spune ACTIVEWORKBOOK.REFRESHALL, apoi închideți, bine și acum să încercăm. Să edităm ceva. Deci, vom lua acele zmeură care sunt în prezent 8.000 și o vom schimba la 1000, deci reducem cu 7000. Când apăs ENTER, vreau să văd că 42.000 scad la 35.000. Ah. Minunat.
Ei bine, hei. Aici vă rog, de obicei, să cumpărați cartea mea, dar, astăzi, vă voi cere să cumpărați cartea prietenilor mei - Ken Puls și Miguel Escobar - M este pentru (DATA) MONKEY. Tot ce am învățat despre interogarea puterii, l-am învățat din această carte. Este o carte uimitoare. Verifica asta.
Încheierea episodului: Steve are numere de sumat care au fost introduse într-o coloană de text; mai multe linii în fiecare celulă, separate prin ALT + ENTER; trebuie să împărțiți acele linii în rânduri, apoi analizați suma în dolari din mijlocul fiecărei celule; rezumați după COST CENTER; construiți un tabel de căutare; obțineți totaluri din tabelul de căutare, folosind IFNA pentru a ignora erorile din rândul gol; și apoi, un bonus, macro la final, un macro eveniment pentru a actualiza foaia de lucru atunci când schimbă o celulă.
Vreau să-i mulțumesc lui Steve pentru că mi-a trimis acea întrebare și mă bucur atât de mult că am un răspuns - înainte de o interogare de energie, ar fi fost foarte, foarte greu - și vreau să vă mulțumesc că ați trecut pe aici. Ne vedem data viitoare pentru un alt netcast de la.
Descărcare fișier
Descărcați exemplarul de fișier aici: Podcast2160.xlsm
