Ay Başından Bugüne - Excel İpuçları

Bir pivot tabloda Ay Başından Bugüne satışlar nasıl gösterilir. Bu bir Düello Excel bölümüdür.

Video izle

Bill'in yöntemi
MTD formülüne sahip bir yardımcı hücre ekleyin
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))
Bu alanı bir Dilimleyici olarak ekleyin, burada = True
Bonus ipucu: Yıllara Kadar Grup Günlük Tarihleri
GetPivotData'dan kaçınarak pivot tablonun dışına bir hesaplama ekleyin
Mike'ın yaklaşımı:
Verileri Ctrl + T kullanarak bir tabloya dönüştürün. Bu, tabloya daha fazla veri eklenmesine ve formüllerin güncellenmesine izin verir.
TARİH, AY, GÜN işlevleriyle SUMIFS
F4'e üç kez basmak bir referansı yalnızca sütuna kilitler.
Dikkat edin - bir Tablo formülünü yana doğru sürüklerseniz sütunlar değişir. Kopyala ve Yapıştır - sorun yok
TEXT (tarih, biçim. Metne 1 rakamını eklemek için 1 ile güzel numara)

Video Metni

Bill Jelen: Hey, tekrar hoş geldiniz. Başka bir Düello Excel Podcast zamanı. Ben Bill Jelen'liyim. Excel Is Fun'dan Mike Girvin'e katılacağım.

Bu, Bölüm 181: Ay Başından Bugüne Özet Tablo'muz.

Pekala, hey, bugünün sorusu- bu düello için bugünün fikri Mike tarafından gönderiliyor. "Bir pivot tabloda Ay Başından Bugüne raporu oluşturabilir misiniz?" Diyor.

Pekala, gidelim. İşte elimizde olan şey şu, Ocak 2016'dan 2017'ye kadar iki yıllık tarihlerimiz var. Şimdi bunu Nisan'da kaydediyorum, tam şu anda düellodaki parçamı kaydederken 15 Nisan. Ve burada, sol tarafta Günleri, üstte Kategori'yi ve pivot tablonun kalbinde Geliri gösteren bir pivot tablomuz var.

Şimdi, bir Ay Başından Bugüne raporu oluşturmak için yapacağım şey, buraya orijinal verilerime yeni bir yardımcı sütun ekleyeceğim ve bu iki şeyi kontrol edecek. AND işlevini kullanacağım iki şeyi kontrol ettiğim için, Ay Başından Bugüne olması için her iki şeyin de Doğru olması gerekir. Ve burada BUGÜN adlı bir işlevi kullanacağım. BUGÜN, pekala, bu yüzden BUGÜNÜN () AYININ = A Sütunundaki o tarihin AYI = olup olmadığını bilmek istiyorum. Bu doğruysa, mevcut aysa, yani başka bir deyişle Nisan ise, o zaman A2'de o tarihin <= BUGÜN GÜNÜ mü olduğunu kontrol edin. Güzel olan şu ki, bu çalışma kitabını yarın veya bir hafta sonra açtığımızda, bugünün günü otomatik olarak güncellenecek ve bunu kopyalamak için çift tıklıyoruz.

Pekala, şimdi bu ekstra verileri pivot tablomuza almalıyız, bu yüzden buraya geliyorum Pivot Tablo, Analiz ve veri kaynağını değiştirmek o kadar da zor değil, sadece oradaki büyük düğmeyi tıklayın ve Sütun D'ye gitmek istediğimizi söyleyin. Tamam'ı tıklayın. Pekala, şimdi fazladan bir alana sahibiz, Ay Başından Tarih alanına göre bir Dilimleyici Ekleyeceğim ve yalnızca Ay Başından Tarihimizin ne kadar doğru olduğunu görmek istiyorum. Şimdi, Slice'ın bu kadar büyük olmasına ihtiyacımız var mı? Hayır, muhtemelen onu iki sütun haline getirebiliriz ve sadece orada sağ tarafta göze batmayacak şekilde yapabiliriz. Şimdi elimizde olan şey 2016'daki tüm tarihler ve 2017'deki tüm tarihler; ancak bunları yan yana karşılaştırmak gerçekten harika olurdu. Bu yüzden bu Tarih alanını alıp Analiz edeceğim. Saha Grubu'na gidiyorum, onu sadece Yıllara kadar gruplayacağım. YapmamAslında bireysel günleri önemsiyorum. Sadece Aydan Bugüne bilmek istiyorum. Şimdi neredeyiz? Bu yüzden, Yıllara kadar gruplayacağım ve bu 2 yılı orada bitireceğiz ve sonra bunu yeniden düzenleyeceğim, o Yılları geçecek, Kategoriler aşağıya gidecek. Ve şimdi geçen yıl nerede olduğumuzu ve bu yıl nerede olduğumuzu görüyorum. Pekala, gruplama yaptığım için artık pivot tablonun içinde hesaplanan bir alan oluşturmama iznim yok. Orada yıldan yıla bir miktar olmasını isteseydim, sağ tıklardım, Genel Toplamı Kaldır, tamam ve şimdi öyleyiz, yani% Değişim, pivot tablonun içini gösteren bir pivot tablonun dışındayız . GetPivotData'yı kapattığımızdan veya sadece şöyle bir formül oluşturduğumuzdan emin olmalıyız: = J4 / I4-1 ve bu, bunun gibi herhangi bir güçlük çekmeden kopyalayabileceğimiz bir formül oluşturur.Pekala Mike, bakalım neyin var.

Mike Girvin: Teşekkürler. Evet, soruyu formüllerle yaptım ve bunu standart bir pivot tabloyla nasıl yapacağımı çözemediğim için gönderdim ve yıllar boyunca yardımcı sütunlar ve pivot tablolar hakkında bir sürü harika video yaptığımı hatırladım. . Bu güzel bir formül ve güzel bir çözüm. Bir pivot tabloyla nasıl yapılacağı bu, gidip bir formülle nasıl yapılacağına bakalım.

Şimdi, bunu o yaptıktan iki gün sonra yapıyorum. F2 Bugünün güncel tarihi için her zaman tarih bilgisi olacak olan BUGÜN fonksiyonuna sahibim ve buradaki formüller tarafından kullanılacak çünkü güncellenmesini istiyoruz. Ayrıca bir Excel tablosu kullandım ve adı FSales. Ctrl + Aşağı Ok yaparsam, 4/14 olduğunu görüyorum ama en son kayıtları ekleyebilmek ve bir sonraki aya atladığımızda formül güncellememizi dahil edebilmek istiyorum. Ctrl + Yukarı Ok. Pekala, sütun başlıkları olarak Yıl Ölçütlerim var, satır başlıkları olarak Kategori var ve ardından ay ve gün için ayrıntılar bu hücreden gelecek. Bu yüzden ben sadece SUMIFS işlevini kullanacağım, çünkü birden çok koşulla eklediğimiz için, buradaki toplam aralığı gelir, bu harika numarayı bir Excel tablosu için kullanacağız.Sağ üstte siyah aşağı bakan oku görüyoruz, BAM! Bu, uygun tablo adını ve ardından köşeli parantez içinde alan adını virgül koyar. Ölçüt aralığı, Tarih'i iki kez kullanmamız gerekecek, bu yüzden Tarih ile başlayacağım. Tıklayın, Tarih Sütunu var, virgül. Şimdi Nisan ayındayım, bu nedenle> = - 1 Nisan koşulunu oluşturmam gerekiyor. Yani karşılaştırmalı operatörler çift tırnak içinde "> =" ve ben buna katılacağım. Şimdi, her zaman buraya bakan ve bu yıl için ayın ilkini oluşturan bir tarih formülü oluşturmam gerekiyor. Bu yüzden TARİH işlevini kullanacağım. Yıl, sütun başlığı olarak Yıl hakkım var ve satırı kilitlemek için F4 tuşuna iki kez basacağım, ancak sütunu değil, böylece burada hareket ettiğinde 2017'ye geçeceğiz, virgül, Ay - Ben 'm, 1'den 12'ye kadar olan ayları elde etmek için AY işlevini kullanacağım. Bu, o hücrede hangi ay olursa olsun, F4 her yöne kilitlemek için, parantezleri kapatın ve sonra virgül, 1 her zaman ay hangi ay olursa olsun, parantezleri kapatın.

Pekala, kriter bu. Her zaman> = ayın ilk, virgül, ölçüt aralığı iki olacak, Tarih sütunumu virgül alacağım. Kriter iki, pekala, bu <= üst sınır olacak, yani “<=” ve &. Hile yapacağım, bunu izle. Bunu buradan kopyalayacağım, çünkü aynı şey, Ctrl-C Ctrl-V, Gün hariç, GÜN işlevini kullanmalıyız ve bu belirli ayın hangi günü olursa olsun her zaman üst sınırımız olsun . F4 her yöne kilitlemek için, Tarih üzerindeki parantezleri kapatın. Pekala, bu iki kriterimiz: virgül. Kriter aralığı 3, Kategori'dir. İşte virgül ve burada satır başlığımız var. Yani bu, F4 bir iki üç kez F4 yapmalıyız, sütunu kilitlemeliyiz ama satırı değil, böylece formülü aşağı kopyaladığımızda, Gizmo ve Widget'a geçeceğizkapat parantez ve formül budur. Üzerine sürükleyin, çift tıklayın ve aşağıya gönderin. Bir sorun olduğunu görebiliyorum. Çapraz olarak en uzaktaki son hücreye gelsem iyi olur. F2'ye basın. Şimdi, Tablo Formülü İsimlendirme için varsayılan davranış, formülleri yana kopyaladığınızda, gerçek sütunlar karışık hücre referansları gibi hareket ediyor. Şimdi onları kilitleyebiliriz ama bu sefer bunu yapmayacağım. Şimdi, kopyaladığınızda iyi çalıştığına dikkat edin, ancak kenara kopyaladığınızda, asıl sütunlar hareket ettiğinde. Bu yüzden şunu izleyin, Ctrl + C ve Ctrl + V'ye gidiyorum ve bu, F'yi sütunlara kopyaladığınızda F'nin sütunlara taşınmasını önlüyor. Çift tıklayın ve aşağıya gönderin. Şimdi% Değişim formülümüz = bitiş miktarı / başlangıç tutarı -1, Ctrl + Enter, çift tıklayın ve aşağıya gönderin.Üzerine sürükleyin, çift tıklayın ve aşağıya gönderin. Bir sorun olduğunu görebiliyorum. Çapraz olarak en uzaktaki son hücreye gelsem iyi olur. F2'ye basın. Şimdi, Tablo Formülü İsimlendirme için varsayılan davranış, formülleri yana kopyaladığınızda, gerçek sütunlar karışık hücre referansları gibi hareket ediyor. Şimdi onları kilitleyebiliriz ama bu sefer bunu yapmayacağım. Şimdi, kopyaladığınızda iyi çalıştığına dikkat edin, ancak yan tarafa kopyaladığınızda, asıl sütunlar hareket ettiğinde. Bu yüzden şunu izleyin, Ctrl + C ve Ctrl + V'ye gidiyorum ve bu, F'yi sütunlara kopyaladığınızda F'nin hareket etmesini engelliyor. Çift tıklayın ve aşağıya gönderin. Şimdi% Değişim formülümüz = bitiş miktarı / başlangıç tutarı -1, Ctrl + Enter, çift tıklayın ve aşağıya gönderin.Üzerine sürükleyin, çift tıklayın ve aşağıya gönderin. Bir sorun olduğunu görebiliyorum. Çapraz olarak en uzaktaki son hücreye gelsem iyi olur. F2'ye basın. Şimdi, Tablo Formülü İsimlendirme için varsayılan davranış, formülleri yana kopyaladığınızda, gerçek sütunlar karışık hücre referansları gibi hareket ediyor. Şimdi onları kilitleyebiliriz ama bu sefer bunu yapmayacağım. Şimdi, kopyaladığınızda iyi çalıştığına dikkat edin, ancak yan tarafa kopyaladığınızda asıl sütunlar hareket ettiğinde. Bu yüzden şunu izleyin, Ctrl + C ve Ctrl + V'ye gidiyorum ve bu, F'yi sütunlara kopyaladığınızda F'nin hareket etmesini engelliyor. Çift tıklayın ve aşağıya gönderin. Şimdi% Değişim formülümüz = bitiş miktarı / başlangıç tutarı -1, Ctrl + Enter, çift tıklayın ve aşağıya gönderin.Çapraz olarak en uzaktaki son hücreye gelsem iyi olur. F2'ye basın. Şimdi, Tablo Formülü İsimlendirme için varsayılan davranış, formülleri yana kopyaladığınızda, gerçek sütunlar karışık hücre referansları gibi hareket ediyor. Şimdi onları kilitleyebiliriz ama bu sefer bunu yapmayacağım. Şimdi, kopyaladığınızda iyi çalıştığına dikkat edin, ancak yan tarafa kopyaladığınızda asıl sütunlar hareket ettiğinde. Bu yüzden şunu izleyin, Ctrl + C ve Ctrl + V'ye gidiyorum ve bu, F'yi sütunlara kopyaladığınızda F'nin hareket etmesini engelliyor. Çift tıklayın ve aşağıya gönderin. Şimdi% Değişim formülümüz = bitiş miktarı / başlangıç tutarı -1, Ctrl + Enter, çift tıklayın ve aşağıya gönderin.Çapraz olarak en uzaktaki son hücreye gelsem iyi olur. F2'ye basın. Artık Tablo Formülü İsimlendirme için varsayılan davranış, formülleri yana kopyaladığınızda, gerçek sütunlar karışık hücre referansları gibi hareket ediyor. Şimdi onları kilitleyebiliriz ama bu sefer bunu yapmayacağım. Şimdi, kopyaladığınızda iyi çalıştığına dikkat edin, ancak yan tarafa kopyaladığınızda asıl sütunlar hareket ettiğinde. Bu yüzden şunu izleyin, Ctrl + C ve Ctrl + V'ye gidiyorum ve bu, F'yi sütunlara kopyaladığınızda F'nin hareket etmesini engelliyor. Çift tıklayın ve aşağıya gönderin. Şimdi% Değişim formülümüz = bitiş miktarı / başlangıç tutarı -1, Ctrl + Enter, çift tıklayın ve aşağıya gönderin.gerçek sütunlar, karışık hücre referanslarıymış gibi hareket eder. Şimdi onları kilitleyebiliriz ama bu sefer bunu yapmayacağım. Şimdi, kopyaladığınızda iyi çalıştığına dikkat edin, ancak yan tarafa kopyaladığınızda asıl sütunlar hareket ettiğinde. Bu yüzden şunu izleyin, Ctrl + C ve Ctrl + V'ye gidiyorum ve bu, F'yi sütunlara kopyaladığınızda F'nin sütunlara taşınmasını önler. Çift tıklayın ve aşağıya gönderin. Şimdi% Değişim formülümüz = bitiş miktarı / başlangıç tutarı -1, Ctrl + Enter, çift tıklayın ve aşağıya gönderin.gerçek sütunlar, karışık hücre referanslarıymış gibi hareket eder. Şimdi onları kilitleyebiliriz ama bu sefer bunu yapmayacağım. Şimdi, kopyaladığınızda iyi çalıştığına dikkat edin, ancak yan tarafa kopyaladığınızda asıl sütunlar hareket ettiğinde. Bu yüzden şunu izleyin, Ctrl + C ve Ctrl + V'ye gidiyorum ve bu, F'yi sütunlara kopyaladığınızda F'nin hareket etmesini engelliyor. Çift tıklayın ve aşağıya gönderin. Şimdi% Değişim formülümüz = bitiş miktarı / başlangıç tutarı -1, Ctrl + Enter, çift tıklayın ve aşağıya gönderin.m Ctrl + C ve Ctrl + V'ye gider ve bu, F'yi sütunlara kopyaladığınızda F'nin sütunlara taşınmasını önler. Çift tıklayın ve aşağıya gönderin. Şimdi% Değişim formülümüz = bitiş miktarı / başlangıç tutarı -1, Ctrl + Enter, çift tıklayın ve aşağıya gönderin.m Ctrl + C ve Ctrl + V'ye gider ve bu, F'yi sütunlara kopyaladığınızda F'nin sütunlara taşınmasını önler. Çift tıklayın ve aşağıya gönderin. Şimdi% Değişim formülümüz = bitiş miktarı / başlangıç tutarı -1, Ctrl + Enter, çift tıklayın ve aşağıya gönderin.

Şimdi, test etmeden önce, şimdi bazı yeni kayıtlar ekleyin. Aslında bu etiketi burada yaratmak istiyorum, bu yüzden dinamik. Ve bunu yapacağım yol şu ki = işaret diyeceğim ve bir Metin formülü yapacağız, bu yüzden ne zaman metin ve formül istersek, onu yazmanız gerekir: "ve ben Sales Between, boşluk ”yazacak ve şimdi oradaki tek tarihten, ayın ilkinden ayın sonuna kadar çıkarmam gerekiyor. METİN işlevini kullanacağım. METİN işlevi bir sayı tarihi veya seri numarası alabilir, virgül alabilir ve bazı özel sayı biçimlendirmelerini kullanabilir ”. Her zaman ayın üç harfli kısaltmasını görmek isterim, mmm, her zaman ilk olmasını istiyorum. Şimdi buraya 1, virgül boşluk yyy koyarsam, işe yaramaz. Bunun bize bir değer verdiğini veya bundan hoşlanmadığı için görmek istiyor 1. Ama biz 'Eğik çizgi kullanırsak tek bir karakter eklememize izin verilir, bu Özel Sayı biçimlendirmesindedir. Mm ve yy, Özel Numara biçimlendirmesi tarafından ay ve yıl olarak anlaşılacak ve şimdi Özel Sayı biçimi 1 sayısını eklemeyi anlayacaktır. F2 ve şimdi sadece o virgülün & "-" & METİNİ yapacağız ve şimdi Yalnızca düz sayı biçimlendirmesi kullanacağız: “mmm boşlukD, yyy”) 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.

Pekala, uğradıkları için herkese teşekkür etmek istiyorum. Bir dahaki sefere başka bir Dueling Excel Podcast ve Excel Is Fun için görüşürüz.

Dosyayı indir

Örnek dosyayı buradan indirin: Duel181.xlsm