Sum Data Alt-Entered - Excel Tips

Questo è il problema del budget dei vigili del fuoco. Le persone in una caserma dei pompieri hanno sbagliato i loro budget in Excel. Una straordinaria trasformazione di Power Query fornisce la soluzione.

Guarda un video

Steve deve sommare i numeri che sono stati inseriti in una colonna di testo
Ci sono più righe in ogni cella, separate da alt = "" + Invio
È necessario dividere quelle righe in righe, quindi analizzare l'importo in dollari dal centro di ogni cella
Riepiloga per centro di costo
Crea una tabella di ricerca
Ottieni i totali dalla tabella di ricerca, utilizzando IFNA per ignorare gli errori nella riga vuota
Bonus: aggiungi una macro Evento per aggiornare il foglio di lavoro quando cambiano una cella.

Trascrizione del video

Impara Excel da, Podcast Episode 2160: SUM Data that's been Alt + Enter.

Hey. Bentornato al netcast. Sono Bill Jelen. Non sto inventando. Ho ricevuto una domanda da qualcuno che dispone di dati - dati di budget - che assomigliano a questo. Ora, inserisco parole false qui in modo che non abbiamo le loro informazioni sul budget, ma la persona è nuova nel reparto contabilità, è andata in una società e questa società per anni ha fatto i propri budget in questo modo. Non sono contabili che fanno il budget, sono persone di linea, ma questo è il modo in cui lo hanno fatto e lui non può convincerli a cambiare. Quindi, ecco il nostro obiettivo. Dice che è pessimo quanto digitare il budget in Word.

Bene, quasi, ma fortunatamente, grazie alla query di alimentazione, salverà il nostro problema. Ecco il nostro obiettivo. Per ogni CENTRO DI COSTO qui, vogliamo riportare il totale di tutti quei numeri. Quindi, c'è il nome della spesa, un -, di solito un -, poi un segno $, e poi, solo per rendere la vita interessante, ogni tanto, una nota casuale dopo; non tutte le volte, solo alcune volte. Riga vuota tra ciascuna di esse. Tonnellate e tonnellate di dati.

Quindi, ecco cosa farò. Scenderò fino in fondo, l'ultima cella, selezionerò tutta questa roba, compresi i titoli. Creerò un NOME. Lo chiamerò MyData. MyData, così, va bene? Tutto a posto. Ora utilizzeremo la query di alimentazione gratuita nel 2010 o 2013, incorporata in Office 365 2016 e 2016. Proverrà da una TABELLA O DA UNA GAMMA. Tutto a posto. Per prima cosa, ogni volta che abbiamo quegli spazi nella COLONNA A, tutti i NULL di cui vogliamo sbarazzarci. Quindi deselezionerò NULL. Eccezionale. Va bene. In realtà, in questi dati, in questa versione dei dati, poiché creerò un CERCA.VERT, non abbiamo bisogno di questa colonna. Quindi, farò clic con il pulsante destro del mouse e mi libererò di quella colonna, quindi RIMUOVI colonna.

Tutto a posto. Ora, ecco dove accadrà la magia spaventosa. Scegli questa colonna, COLONNA DIVISA DA UN DELIMITATORE, e andremo sicuramente in AVANZATO. Il delimitatore sarà un carattere speciale e divideremo ogni occorrenza del delimitatore. Quindi, qui, penso che in realtà lo abbiano già capito perché l'ho espanso, ma te lo mostrerò. INSERIRE CARATTERE SPECIALE. Dirò che è un LINE FEED, va bene, quindi, ad ogni occorrenza di LINE FEED, e ho intenzione di SPLIT INTO ROWS. Va bene, e quello che succederà qui è, 1, 2, 3, 4, 5, otterrò 5 righe o dirò 1001, ma, in ogni riga, avrà un diverso linea da questa cella. Questo è fantastico. Ci sono 1, 2, 3, 4, 5, 1001. Va bene. Ora dobbiamo solo analizzare questo cattivo ragazzo. Tutto a posto,quindi, scegli quella colonna, COLONNA DIVISA PER UN DELIMITATORE. Questa volta, un delimitatore sarà un segno $. È perfetto, una volta, al primo segno $ che troviamo, nel caso ci sia un segno $ là fuori nella parte futura. Stiamo andando a SPACCARE IN COLONNE. Fare clic su OK. Tutto a posto. Quindi, ci sono dettagli. Ecco i nostri soldi.

Ora, lo dividerò nello SPACE. Quindi, scegli questa colonna, DIVIDI COLONNA DA UN DELIMITATORE, e il delimitatore sarà uno SPAZIO, sì, una volta a SINISTRA-DELIMITATORE PIÙ, fai clic su OK e non ho bisogno di quei commenti là fuori quindi quei commenti noi ' stai per RIMUOVERE. In realtà, non ne ho nemmeno bisogno perché sto solo cercando di ottenere un totale di tutta quella roba, quindi ho intenzione di RIMUOVERE.

Ora, trasforma. GRUPPO PER CENTRO DI COSTO, IL NOME DELLA NUOVA COLONNA si chiamerà TOTALE, l'OPERAZIONE sarà la SOMMA e quale colonna sarà la SOMMA? I DETTAGLI 2.1. Bellissimo. Fai clic su OK, d'accordo, e ciò che otteniamo è una riga per CENTRO DI COSTO con il TOTALE di tutti gli elementi pubblicitari. HOME, CLOSE & LOAD. Probabilmente inserirà un nuovo foglio di lavoro. Spero che inserisca un nuovo foglio di lavoro, e lo fa, e quel foglio di lavoro si chiama MYDATA_1. MYDATA_1.

Tutto a posto. Ora torneremo qui nei dati originali e faremo questi passaggi. Sul primo vero, = CERCA.VERT di 1001 nei nostri risultati. È un po 'come impostare un riferimento circolare, ma non ci darà un riferimento circolare. , 2, FALSE. Voglio la corrispondenza esatta. Va bene, ma non lo faremo per le celle vuote. Quindi, sto per dire, beh, in realtà, copiamolo fino in fondo. CONTROL + C, vai fino in fondo solo per vedere cosa stiamo ottenendo. Forse stiamo ottenendo N / A e posso sbarazzarmene con l'IFNA. Sì, bellissimo, va bene. Quindi, sbarazziamoci dell'N / As. Se N / A, allora vogliamo solo "". Non vogliamo niente lì dentro. CTRL + INVIO. Tutto a posto. Ora, dovrebbe essere il TOTALE. Vediamo se riusciamo a trovarne uno corto e fare i conti. = 627,37 + 7264,25 + 6066.01 + 4010,66 + 9773,94 e il TOTALE, 27742,23 è quello. Grandioso. (= IFNA (VLOOKUP (A2, MyData_1,2, FALSE), ""))

Ora, ecco l'affare. Quindi, abbiamo quelle persone di linea che sono qui fuori a cambiare roba, va bene, quindi diciamo che passano e cambiano il budget, 40294.48, e vengono qui e cambiano questo a 6000, così, e aggiungono uno nuovo, ALT + INVIO, QUALCOSA - $ segno, $ 1000 appena aggiunto. Tutto a posto. Ora, ovviamente, quando premo INVIO, questo numero, 40294.48, non verrà aggiornato, va bene, ma quello che dobbiamo fare è andare alla scheda DATI e vogliamo AGGIORNARE TUTTO. Quindi, 40294.48. Guarda, guarda, guarda, guarda. AGGIORNA TUTTO. Freaking incredibile.

Adoro le query di potere. La query di alimentazione è la cosa più sorprendente. Questi dati, che essenzialmente sono proprio come i dati delle parole in una cella, ora li abbiamo aggiornati. Probabilmente potresti anche creare una sorta di macro che dice che ogni volta che qualcuno cambia qualcosa nella COLONNA C, andiamo avanti e facciamo clic su AGGIORNA TUTTO usando la macro e abbiamo quei risultati costantemente, costantemente aggiornati.

Che domanda orribile inviata. Mi dispiace per Steve che ha a che fare con questo, ma ora, usando power query in Office 365 o scaricato per il 2010 o il 2013, hai un modo molto, molto semplice per risolverlo.

Aspettare. Va bene, un'appendice: rendiamola ancora migliore. Questo foglio si chiama DATA e ho salvato la cartella di lavoro come abilitata per le macro, quindi xlsm. Se sei xlsx, non saltare il salvataggio come xlsm. ALT + F11. Trova la cartella di lavoro denominata DATI, fai doppio clic, in alto a sinistra, FOGLIO DI LAVORO, quindi CAMBIA ogni volta che cambiamo il foglio di lavoro e diremo ACTIVEWORKBOOK.REFRESHALL, quindi chiudi, va bene, e ora proviamolo. Modifichiamo qualcosa. Quindi, prenderemo quei lamponi che sono attualmente 8.000 e li cambieremo in 1000, quindi stiamo riducendo di 7000. Quando premo INVIO, voglio vedere che 42.000 scendono a 35.000. Ah. Eccezionale.

Beh ciao. Qui è dove di solito ti prego di comprare il mio libro, ma oggi ti chiederò di comprare il libro dei miei amici - Ken Puls e Miguel Escobar - M sta per (DATA) MONKEY. Tutto quello che ho imparato sulla query di alimentazione, l'ho imparato da questo libro. È un libro fantastico. Controlla quello.

Conclusione dell'episodio: Steve ha numeri da sommare che sono stati inseriti in una colonna di testo; più righe in ogni cella, separate da ALT + INVIO; è necessario dividere quelle righe in righe, quindi analizzare l'importo in dollari dal centro di ogni cella; riassumere per COST CENTER; costruire una tabella di ricerca; ottenere i totali dalla tabella di ricerca, utilizzando IFNA per ignorare gli errori nella riga vuota; e poi, un bonus, macro alla fine, una macro di eventi per aggiornare il foglio di lavoro quando cambiano una cella.

Voglio ringraziare Steve per aver inviato questa domanda e sono così felice di avere una risposta - prima della domanda di potere, sarebbe stato davvero, davvero difficile - e voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.

Download file

Scarica il file di esempio qui: Podcast2160.xlsm