Cum se afișează vânzările lunare într-un tabel pivot. Acesta este un episod Dueling Excel.
Urmăriți videoclipul
- Metoda lui Bill
- Adăugați o celulă asistentă cu o formulă MTD
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))- Adăugați câmpul respectiv ca Slicer unde = Adevărat
- Sfat bonus: Date zilnice de grup până la ani
- Adăugați un calcul în afara tabelului pivot evitând în același timp GetPivotData
- Abordarea lui Mike:
- Transformați datele într-un tabel folosind Ctrl + T. Acest lucru permite adăugarea mai multor date în tabel și actualizarea formulelor.
- SUME cu funcții DATĂ, LUNĂ, ZI
- Apăsând F4 de trei ori se blochează o referință doar la coloană.
- Atenție - dacă trageți o formulă de tabel lateral, coloanele se schimbă. Copiere și lipire - fără probleme
- Folosind TEXT (dată, format. Trucul frumos cu 1 pentru a introduce numărul 1 în text
Transcriere video
Bill Jelen: Hei, bine ai revenit. Este timpul pentru un alt podcast Dueling Excel. Sunt Bill Jelen din. Mi se va alătura Mike Girvin de la Excel Is Fun.
Acesta este episodul nostru 181: Tabelul pivot lunar până în prezent.
Ei bine, întrebarea de astăzi - ideea de astăzi pentru acest duel este trimisă de Mike. El spune: „Puteți crea un raport lunar până într-un tabel pivot?”
Bine, să mergem. Iată ce avem, avem date în valoare de doi ani din ianuarie 2016 până în 2017. Bineînțeles că înregistrez asta în aprilie, este 15 aprilie chiar acum când îmi înregistrez bucata din duel. Așadar, aici avem un tabel pivot care afișează Zile în partea stângă, Categorie în partea de sus și Venituri în inima tabelului pivot.
Acum, pentru a crea un raport lunar până în prezent, ceea ce voi face este să spun că voi adăuga o nouă coloană de asistență aici la datele mele originale și că voi verifica două lucruri. Și pentru că verific două lucruri, voi folosi funcția ȘI, ambele lucruri trebuie să fie adevărate pentru ca aceasta să fie lună până în prezent. Și voi folosi o funcție aici numită AZI. ASTĂZI, în regulă, așa că vreau să știu dacă LUNA DE ASTĂZI ()) este = până la LUNA acelei date acolo în coloana A. Dacă este adevărat, dacă este luna curentă, deci cu alte cuvinte dacă este aprilie, atunci verificați și vedeți dacă ziua acelei date de acolo, în A2, este <= DAY of TODAY. Lucru frumos este că atunci când deschidem acest registru de lucru mâine sau peste o săptămână, ziua de astăzi se va actualiza automat și facem dublu clic pentru a copia acest lucru.
Bine acum, trebuie să introducem aceste date suplimentare în tabelul nostru pivot, așa că vin aici Tabel pivot, Analizează și nu este atât de greu să schimbi sursa de date, trebuie doar să dai clic pe butonul mare de acolo și să spui că vrem să trecem la Coloana D , faceți clic pe OK. Bine, așa că acum avem acel câmp suplimentar, voi introduce o secțiune bazată pe acel câmp lunar până în prezent și vreau doar să văd cum este adevărată luna noastră până în prezent. Acum, avem nevoie ca Slice să fie atât de mare? Nu, probabil o putem face să fie două coloane și să o facem discretă pe partea dreaptă. Deci, acum avem toate datele din 2016 și toate datele din 2017; deși, ar fi foarte interesant să le comparăm unul lângă altul. Deci, voi lua acel câmp Data și voi analiza. Voi grupa câmpul, o voi grupa până la doar ani. Eu nuDe fapt, nu îmi pasă de zilele individuale. Vreau doar să știu luna până în prezent. Acum, unde suntem? Așa că o voi grupa până la Ani și vom termina cu acești 2 ani acolo și voi rearanja apoi acest lucru, voi pune acei Ani de parcurs, Categorii pentru a coborî. Și acum văd unde am fost anul trecut și unde am fost anul acesta. Bine acum, pentru că am terminat gruparea, nu mai am voie să creez un câmp calculat în interiorul tabelului pivot. Dacă aș vrea să am o sumă de la un an la altul acolo, aș face clic dreapta pe Remove Total Total, bine, iar acum suntem, deci,% Change, suntem în afara unui tabel pivot care arată în interiorul tabelului pivot . Trebuie să ne asigurăm fie să dezactivăm GetPivotData, fie să construim o formulă de genul acesta: = J4 / I4-1 și care creează o formulă pe care o putem copia fără niciun fel de probleme, așa.Bine, Mike, hai să vedem ce ai.
Mike Girvin: Mulțumesc. Da, am trimis întrebarea la pentru că am făcut-o cu formule și nu mi-am putut da seama cum să o fac cu un tabel pivot standard și apoi mi-am amintit că am văzut de-a lungul anilor, fac o grămadă de videoclipuri interesante despre coloanele de ajutor și tabelele pivot . Aceasta este o formulă frumoasă și o soluție frumoasă. Deci, așa se face cu un tabel pivot, să vedem cum se face cu o formulă.
Acum, fac asta la două zile după ce a făcut-o. F2 Am funcția TODAY, care va fi întotdeauna informațiile de dată pentru data curentă de astăzi, care vor fi utilizate de formulele de aici, deoarece dorim să se actualizeze. De asemenea, am folosit un tabel Excel și se numește FSales. Dacă Ctrl + Săgeată în jos, văd că este 4/14, dar vreau să pot adăuga cele mai recente înregistrări și să includ actualizările formulelor noastre atunci când trecem la luna următoare. Ctrl + Săgeată sus. În regulă, am Criterii de An ca anteturi de coloană, Categorie ca anteturi de rând și apoi datele din luna și ziua vor veni din acea celulă. Așa că voi folosi pur și simplu funcția SUMIFS, deoarece adăugăm cu condiții multiple, intervalul sumă aici este venitul, vom folosi acel truc excelent pentru un tabel Excel.Chiar în partea de sus vedem acea săgeată neagră orientată în jos, BAM! Aceasta pune numele corect al tabelului și apoi între paranteze pătrate numele câmpului, virgulă. Intervalul de criterii, va trebui să folosim Data de două ori, așa că voi începe cu Date. Faceți clic, există coloana dată, virgulă. Acum sunt în aprilie, așa că trebuie să creez condiția> = până la 1 aprilie. Deci, operatorii comparativi „> =” în ghilimele duble și mă voi alătura acestuia. Acum trebuie să creez o formulă de dată care arată întotdeauna aici și creează prima lună pentru acest an. Deci, voi folosi funcția DATE. An, ei bine, am Anul drept antetul coloanei și voi apăsa tasta F4 de două ori pentru a bloca rândul, dar nu coloana, așa că, atunci când se deplasează aici, vom trece la 2017, virgulă, Luna - eu 'Voi folosi funcția MONTH pentru a obține luna de la 1 la 12. Aceasta este orice lună este în acea celulă, F4 pentru a o bloca în toate direcțiile, închide paranteze și apoi virgulă, 1 va fi întotdeauna prima din lună indiferent de luna care este aceasta, închideți parantezele.
Bine, deci acesta este criteriul. Va fi întotdeauna> = prima lunii, virgulă, intervalul de criterii două Voi primi coloana mea de dată, virgulă. Criteriile două, ei bine, aceasta va fi <= limita superioară, deci în „<=” și &. Voi înșela, uită-te la asta. Voi copia doar aici de sus, deoarece este același lucru, Ctrl-C Ctrl-V, cu excepția zilei, trebuie să folosim funcția ZI și să obținem întotdeauna ca limită superioară indiferent de ziua din această lună. . F4 pentru al bloca în toate direcțiile, închideți parantezele la Data. Bine, deci acesta este criteriul nostru doi: virgulă. Intervalul de criterii 3, este categoria. Iată, virgulă și iată antetul rândului nostru. Deci acesta trebuie să F4 unul de trei ori, să blocăm coloana, dar nu rândul, așa că atunci când copiem formula, vom trece la Gizmo și Widget,închideți paranteze și aceasta este formula. Trageți deasupra, faceți dublu clic și trimiteți-l în jos. Văd că sunt probleme. Mai bine ajung la ultima celulă aflată în diagonală. Apăsați F2. Acum, comportamentul implicit pentru Nomenclatura formulelor de tabel este atunci când copiați formulele în lateral, coloanele reale se mișcă de parcă ar fi referințe de celule mixte. Acum le-am putea bloca, dar de data asta nu am de gând să fac asta. Acum, observați când îl copiați, funcționează bine, dar când copiați în lateral, atunci se mișcă coloanele reale. Așadar, urmăriți acest lucru, voi merge la Ctrl + C și Ctrl + V și apoi se va evita ca F coloanelor să se miște atunci când îl copiați în lateral. Faceți dublu clic și trimiteți-l în jos. Acum formula noastră% Change = suma finală / suma inițială -1, Ctrl + Enter, faceți dublu clic și trimiteți-o în jos.Trageți deasupra, faceți dublu clic și trimiteți-l în jos. Văd că sunt probleme. Mai bine ajung la ultima celulă aflată în diagonală. Apăsați F2. Acum, comportamentul implicit pentru Nomenclatura formulelor de tabel este atunci când copiați formulele în lateral, coloanele reale se mișcă de parcă ar fi referințe de celule mixte. Acum le-am putea bloca, dar de data asta nu am de gând să fac asta. Acum, observați când îl copiați, funcționează bine, dar când copiați în lateral, atunci se mișcă coloanele reale. Așadar, urmăriți acest lucru, voi merge la Ctrl + C și Ctrl + V și apoi se va evita ca F coloanelor să se miște atunci când îl copiați în lateral. Faceți dublu clic și trimiteți-l în jos. Acum formula noastră% Change = suma finală / suma inițială -1, Ctrl + Enter, faceți dublu clic și trimiteți-o în jos.Trageți deasupra, faceți dublu clic și trimiteți-l în jos. Văd că sunt probleme. Mai bine ajung la ultima celulă aflată în diagonală. Apăsați F2. Acum, comportamentul implicit pentru Nomenclatura formulelor de tabel este atunci când copiați formulele în lateral, coloanele reale se mișcă de parcă ar fi referințe de celule mixte. Acum le-am putea bloca, dar de data asta nu am de gând să fac asta. Acum, observați când îl copiați, funcționează bine, dar când copiați în lateral, atunci se mișcă coloanele reale. Așadar, urmăriți acest lucru, voi merge la Ctrl + C și Ctrl + V și apoi se va evita ca F coloanelor să se miște atunci când îl copiați în lateral. Faceți dublu clic și trimiteți-l în jos. Acum formula noastră% Change = suma finală / suma inițială -1, Ctrl + Enter, faceți dublu clic și trimiteți-o în jos.Mai bine ajung la ultima celulă aflată în diagonală. Apăsați F2. Acum, comportamentul implicit pentru Nomenclatura formulelor de tabel este atunci când copiați formulele în lateral, coloanele reale se mișcă de parcă ar fi referințe de celule mixte. Acum le-am putea bloca, dar de data asta nu am de gând să fac asta. Acum, observați când îl copiați, funcționează bine, dar când copiați în lateral, atunci se mișcă coloanele reale. Așadar, urmăriți acest lucru, voi merge la Ctrl + C și Ctrl + V și apoi se va evita ca F coloanelor să se miște atunci când îl copiați în lateral. Faceți dublu clic și trimiteți-l în jos. Acum formula noastră% Change = suma finală / suma inițială -1, Ctrl + Enter, faceți dublu clic și trimiteți-o în jos.Mai bine vin la ultima celulă aflată în diagonală. Apăsați F2. Acum, comportamentul implicit pentru Nomenclatura formulelor de tabel este atunci când copiați formulele în lateral, coloanele reale se mișcă de parcă ar fi referințe de celule mixte. Acum le-am putea bloca, dar de data asta nu am de gând să fac asta. Acum, observați când îl copiați, funcționează bine, dar când copiați în lateral, atunci se mișcă coloanele reale. Așadar, urmăriți acest lucru, voi merge la Ctrl + C și Ctrl + V și apoi se va evita ca F coloanelor să se miște atunci când îl copiați în lateral. Faceți dublu clic și trimiteți-l în jos. Acum formula noastră% Change = suma finală / suma inițială -1, Ctrl + Enter, faceți dublu clic și trimiteți-o în jos.coloanele reale se mișcă de parcă ar fi referințe de celule mixte. Acum le-am putea bloca, dar de data asta nu am de gând să fac asta. Acum, observați când îl copiați, funcționează bine, dar când copiați în lateral, atunci se mișcă coloanele reale. Așadar, urmăriți acest lucru, voi merge la Ctrl + C și Ctrl + V și apoi se va evita ca F coloanelor să se miște atunci când îl copiați în lateral. Faceți dublu clic și trimiteți-l în jos. Acum formula noastră% Change = suma finală / suma inițială -1, Ctrl + Enter, faceți dublu clic și trimiteți-o în jos.coloanele reale se mișcă de parcă ar fi referințe de celule mixte. Acum le-am putea bloca, dar de data asta nu am de gând să fac asta. Acum, observați când îl copiați, funcționează bine, dar când copiați în lateral, atunci se mișcă coloanele reale. Așadar, urmăriți acest lucru, voi merge la Ctrl + C și Ctrl + V și apoi se va evita ca F coloanelor să se miște atunci când îl copiați în lateral. Faceți dublu clic și trimiteți-l în jos. Acum formula noastră% Change = suma finală / suma inițială -1, Ctrl + Enter, faceți dublu clic și trimiteți-o în jos.M merg la Ctrl + C și Ctrl + V și apoi asta evită F la coloane să se miște atunci când îl copiați în lateral. Faceți dublu clic și trimiteți-l în jos. Acum formula noastră% Change = suma finală / suma inițială -1, Ctrl + Enter, faceți dublu clic și trimiteți-o în jos.M merg la Ctrl + C și Ctrl + V și apoi asta evită F la coloane să se miște atunci când îl copiați în lateral. Faceți dublu clic și trimiteți-l în jos. Acum formula noastră% Change = suma finală / suma inițială -1, Ctrl + Enter, faceți dublu clic și trimiteți-o în jos.
Acum, înainte de a-l testa, adăugați câteva înregistrări noi. Vreau să creez această etichetă aici, astfel încât să fie dinamică. Și modul în care voi face acest lucru este că voi spune = semn și vom face o formulă de text, astfel încât oricând dorim text și o formulă, trebuie să o introduceți: „și eu sunt voi tasta Vânzări între, spațiu ”și acum trebuie să extrag din acea singură dată acolo, prima lună până la sfârșitul lunii. Voi folosi funcția TEXT. Funcția TEXT poate lua un număr de date sau numere de serie, virgulă și poate utiliza o anumită formatare a numărului personalizat în ”. Întotdeauna vreau să văd abrevierea de trei litere a lunii, mmm, o vreau întotdeauna ca prima. Acum, dacă pun un 1 aici, spațiu virgulă aaaa, asta nu va funcționa. Vrea să vadă că asta ne oferă o valoare sau pentru că nu-i place asta 1. Dar noi 'ni se permite să inserăm un singur caracter dacă folosim o bară directă, care este în formatarea numărului personalizat. MM și yy vor fi înțelese prin formatarea numărului personalizat ca lună și an, iar acum formatul Custom Number va înțelege să introducem numărul 1. F2 și acum vom pur și simplu să: & „-” & TEXTul acelei virgule și acum vom Voi folosi doar formatarea numerelor drepte: „mmm spațiuD, aaaa”) Ctrl + Enter.
Now let's just, before we add some data, let's just change this. Pretending that today was showing: 3/15/2017 just like that, all the formulas are updating and our text formula is also, Ctrl+Z. Now, let's go down to the bottom of the data set, Ctrl+Down Arrow I want to add one new record. I'm in the last cell of the data set, I hit Tab to add a new record to our data set. I'm simply going to copy this record over here, Ctrl+Up Arrow, and there we can see the difference. If we wanted to check these formula values against the ones did: =relative cell reference= click on the sheet, we're going to click right in I4. We could see our formula up there, Ctrl+Enter. Actually I'm going to drag it down. Ctrl+Enter just populated everything I had highlighted. And of course, FALSE FALSE. Well guess what? = that amount right there -, click Ctrl+Down Arrow, Ctrl+Backspace, so I'm going to subtract that just to check and sure enough that was the exact amount we could look back there.
That is a little fun with some IFS and some date calculations, TODAY and even some text formula fun. Alright, throw back to.
Bill Jelen: Alright, Mike, that's awesome. So to wrap it up, Mike took the data and turned it into a table using Ctrl+T that allows more data to be added to the table, and the formulas will update, create that great little formula with SUMIFS, DATE, MONTH and DAY functions. Remember pressing F4 three times, locks the reference to just the column though watch out if you drag a table formula sideways using the Fill Handle, the columns change but Copy and Paste alleviates that problem. I never knew that one.
And then nice trick there using the heading with the text date format and that 1 to insert a number 1 in the text, in any character. I would allow to insert something, so you might have to do something like COOL to get an entire word in there but it would work.
Alright, my method was using a pivot table, I added a helper column with a MONTH TO DATE formula that one there using =AND checking if the MONTH and the DAY match. Add that field as a Slicer, set the Slicer =True. And then bonus tip: Group Daily Dates up to Years and then added a calculation outside of the pivot table while avoiding GetPivotData. And I'm interested, I still don't know how Mike did it with his formula. He managed to use the mouse to point to this equal to something, it’s on my pivot table and didn't get GetPivotData. Maybe, maybe he's turned it off.
Bine, bine, hei, vreau să le mulțumesc tuturor că au trecut pe aici. Ne vedem data viitoare pentru un alt Dueling Excel Podcast de la și Excel Is Fun.
Descărcare fișier
Descărcați exemplarul de fișier aici: Duel181.xlsm
