Bulan hingga Tanggal - Tips Excel

Cara menampilkan penjualan Bulan-Ke-Tanggal di tabel pivot. Ini adalah episode Dueling Excel.

Menonton video

  • Metode Bill
  • Tambahkan sel pembantu dengan rumus MTD
  • =AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))
  • Tambahkan bidang itu sebagai Pemotong di mana = True
  • Tip bonus: Grup Tanggal Harian hingga Tahun
  • Tambahkan perhitungan di luar tabel pivot sambil menghindari GetPivotData
  • Pendekatan Mike:
  • Ubah data menjadi tabel menggunakan Ctrl + T. Ini memungkinkan lebih banyak data untuk ditambahkan ke tabel dan rumus diperbarui.
  • SUMIFS dengan fungsi DATE, MONTH, DAY
  • Menekan F4 tiga kali mengunci referensi ke kolom saja.
  • Hati-hati - jika Anda menyeret rumus Tabel ke samping, kolom berubah. Salin & Tempel - tidak ada masalah
  • Menggunakan TEXT (tanggal, format. Trik bagus dengan 1 untuk memasukkan angka 1 dalam teks

Transkrip Video

Bill Jelen: Hei, selamat datang kembali. Saatnya untuk Podcast Excel Duel lainnya. Saya Bill Jelen dari. Saya akan bergabung dengan Mike Girvin dari Excel Is Fun.

Ini adalah Episode 181 kami: Tabel Pivot Bulan Sampai Saat Ini.

Nah, hei, pertanyaan hari ini- ide hari ini untuk duel ini dikirim oleh Mike. Dia berkata, "Bisakah Anda membuat laporan Bulan Sampai Tanggal di tabel pivot?"

Baiklah, ayo pergi. Jadi inilah yang kami miliki, kami memiliki tanggal selama dua tahun dari Januari 2016 hingga 2017. Sekarang tentu saja saya merekam ini di bulan April, sekarang tanggal 15 April saat saya merekam bagian dari duel saya. Dan di sini kami memiliki tabel pivot yang menampilkan Hari di sisi kiri, Kategori di bagian atas, dan Pendapatan di jantung tabel pivot.

Sekarang, untuk membuat laporan Bulan Sampai Tanggal, yang akan saya lakukan adalah mengatakan bahwa saya akan menambahkan kolom pembantu baru di sini ke data asli saya dan itu akan memeriksa dua hal. Dan karena saya memeriksa dua hal, saya akan menggunakan fungsi AND, kedua hal itu harus True agar menjadi Bulan Hingga Tanggal. Dan saya akan menggunakan fungsi yang disebut HARI INI. HARI INI, baiklah, jadi saya ingin tahu apakah BULAN HARI INI ()) adalah = ke BULAN dari tanggal itu di Kolom A. Jika itu benar, jika itu bulan saat ini, jadi dengan kata lain jika itu April, maka periksa dan lihat apakah hari pada tanggal itu di sana di A2 adalah <= DAY of TODAY. Hal yang indah adalah ketika kita membuka buku kerja ini besok atau seminggu dari sekarang, hari ini secara otomatis akan diperbarui dan kita klik dua kali untuk menyalinnya.

Baiklah sekarang, kita harus memasukkan data ekstra ini ke dalam tabel pivot kita jadi saya datang ke sini Tabel Pivot, Analisis dan tidak sulit untuk mengubah sumber data, cukup klik tombol besar itu di sana dan katakan bahwa kita ingin pergi ke Kolom D , klik OK. Baiklah, jadi sekarang kita memiliki bidang ekstra itu, saya akan menyisipkan Pemotong berdasarkan bidang Bulan Ke Tanggal itu dan saya hanya ingin melihat bagaimana Bulan Sampai Tanggal itu Benar. Sekarang, apakah kita membutuhkan Slice itu sebesar itu? Tidak, kita mungkin bisa membuatnya menjadi dua kolom dan membuatnya tidak mengganggu di sisi kanan. Jadi sekarang yang kita miliki adalah semua tanggal di 2016 dan semua tanggal di 2017; meskipun, akan sangat keren untuk membandingkan ini secara berdampingan. Jadi saya akan mengambil bidang Tanggal dan Analisis. Saya akan Mengelompokkan Lapangan, saya akan mengelompokkannya menjadi hanya Tahun. Saya tidakt benar-benar peduli dengan hari-hari individu. Saya hanya ingin tahu Month To Date. Sekarang kita dimana? Jadi saya akan mengelompokkannya menjadi Tahun dan kita akan berakhir dengan 2 tahun ini di sana dan saya akan mengatur ulang ini, meletakkan Tahun-tahun itu untuk diseberangi, Kategori untuk diturunkan. Dan sekarang saya melihat di mana kami tahun lalu dan di mana kami tahun ini. Baik sekarang, karena saya telah selesai mengelompokkan, saya tidak lagi diizinkan untuk membuat kolom kalkulasi di dalam tabel pivot. Jika saya ingin memiliki jumlah tahun ke tahun di sana, saya akan klik kanan, Hapus Total Keseluruhan, baiklah, dan sekarang kita, jadi,% Ubah, kita berada di luar tabel pivot yang menunjuk ke dalam tabel pivot . Kita harus memastikan untuk mematikan GetPivotData atau hanya membuat rumus seperti ini: = J4 / I4-1 dan itu membuat rumus yang dapat kita salin tanpa kerepotan sama sekali, seperti itu.Baiklah, Mike, mari kita lihat apa yang kamu punya.

Mike Girvin: Terima kasih ,. Ya, saya mengirim pertanyaan ke karena saya melakukannya dengan rumus dan saya tidak tahu bagaimana melakukannya dengan tabel pivot standar dan kemudian saya ingat melihat selama bertahun-tahun, membuat banyak video keren tentang kolom pembantu dan tabel pivot . Itu adalah formula yang indah dan solusi yang bagus. Jadi begitulah cara melakukannya dengan tabel pivot, mari kita lihat cara melakukannya dengan rumus.

Sekarang, saya melakukan ini dua hari setelah dia melakukannya. F2 Saya memiliki fungsi TODAY yang akan selalu menjadi informasi tanggal untuk tanggal hari ini yang akan digunakan oleh rumus di sini karena kami ingin memperbarui. Saya juga menggunakan tabel Excel dan diberi nama FSales. Jika saya Ctrl + Panah Bawah, saya melihatnya 4/14 tetapi saya ingin dapat menambahkan catatan terbaru dan menyertakan pembaruan rumus kami saat kami melompat ke bulan berikutnya. Ctrl + Panah Atas. Baiklah, saya memiliki Kriteria Tahun sebagai tajuk kolom, Kategori sebagai tajuk baris, dan kemudian detail untuk bulan dan hari akan datang dari sel itu. Jadi saya hanya akan menggunakan fungsi SUMIFS karena kami menambahkan dengan beberapa kondisi, kisaran jumlah di sini adalah pendapatannya, kami akan menggunakan trik hebat itu untuk tabel Excel.Tepat di atas kita melihat panah hitam menunjuk ke bawah, BAM! Itu menempatkan nama tabel yang tepat dan kemudian dalam tanda kurung siku nama bidang, koma. Rentang kriteria, kita harus menggunakan Tanggal dua kali, jadi saya akan mulai dengan Tanggal. Klik, ada Kolom Tanggal, koma. Sekarang saya berada di bulan April, jadi saya perlu membuat kondisi> = hingga 1 April. Jadi operator komparatif "> =" dalam tanda kutip ganda dan saya akan bergabung dengannya. Sekarang saya harus membuat beberapa rumus tanggal yang selalu terlihat di sini dan membuat tanggal pertama setiap bulan untuk tahun ini. Jadi saya akan menggunakan fungsi DATE. Tahun, saya memiliki Tahun tepat sebagai tajuk kolom dan saya akan menekan tombol F4 satu, dua kali untuk mengunci baris tetapi tidak kolomnya jadi ketika berpindah ke sini kita akan pindah ke 2017, koma, Bulan - I 'Saya akan menggunakan fungsi MONTH untuk mendapatkan bulan nomor 1 sampai 12. Itu adalah bulan berapa pun di sel itu, F4 untuk menguncinya ke segala arah, menutup tanda kurung dan kemudian koma, 1 itu akan selalu menjadi tanggal 1 dari bulan tidak peduli bulan apa ini, tutup kurung.

Baiklah, jadi itu kriterianya. Itu akan selalu menjadi> = yang pertama bulan itu, koma, kriteria rentang dua Saya akan mendapatkan kolom Tanggal saya, koma. Kriteria dua, nah, ini akan menjadi <= batas atas, jadi di "<=" dan &. Aku akan curang, lihat ini. Saya hanya akan menyalin ini dari sini karena itu adalah hal yang sama, Ctrl-C Ctrl-V kecuali untuk Hari, kita harus menggunakan fungsi DAY dan selalu mendapatkan batas atas kita apapun hari dari bulan khusus ini . F4 untuk menguncinya ke segala arah, tutup tanda kurung pada Tanggal. Baiklah, jadi itu kriteria kita dua: koma. Rentang kriteria 3, itu adalah Kategori. Itu dia, koma dan ada header baris kita. Jadi yang ini kita harus F4 satu dua tiga kali, kunci kolom tapi bukan barisnya jadi ketika kita menyalin rumus ke bawah, kita akan pindah ke Gizmo dan Widget,tutup kurung dan itulah rumusnya. Seret, klik dua kali dan kirimkan ke bawah. Saya bisa melihat ada masalah. Lebih baik saya datang ke sel terakhir secara diagonal terjauh. Tekan F2. Sekarang perilaku default untuk Tata Nama Rumus Tabel adalah saat Anda menyalin rumus ke samping, kolom sebenarnya bergerak seolah-olah merupakan referensi sel campuran. Sekarang kita bisa mengunci mereka tapi saya tidak akan melakukannya kali ini. Sekarang perhatikan ketika Anda menyalinnya, itu berfungsi dengan baik tetapi ketika Anda menyalin ke samping saat itulah kolom yang sebenarnya bergerak. Jadi perhatikan ini, saya akan Ctrl + C dan Ctrl + V dan kemudian itu menghindari F ke kolom bergerak saat Anda menyalinnya ke samping. Klik dua kali dan kirimkan ke bawah. Sekarang rumus% Ubah kami = jumlah akhir / jumlah awal -1, Ctrl + Enter, klik dua kali dan kirimkan.Seret, klik dua kali dan kirimkan ke bawah. Saya melihat ada masalah. Lebih baik saya datang ke sel terakhir secara diagonal terjauh. Tekan F2. Sekarang perilaku default untuk Tata Nama Rumus Tabel adalah saat Anda menyalin rumus ke samping, kolom sebenarnya bergerak seolah-olah merupakan referensi sel campuran. Sekarang kita bisa mengunci mereka tapi saya tidak akan melakukannya kali ini. Sekarang perhatikan ketika Anda menyalinnya, itu berfungsi dengan baik tetapi ketika Anda menyalin ke samping saat itulah kolom yang sebenarnya bergerak. Jadi perhatikan ini, saya akan Ctrl + C dan Ctrl + V dan kemudian itu menghindari F ke kolom bergerak saat Anda menyalinnya ke samping. Klik dua kali dan kirimkan ke bawah. Sekarang rumus% Ubah kami = jumlah akhir / jumlah awal -1, Ctrl + Enter, klik dua kali dan kirimkan.Seret, klik dua kali dan kirimkan ke bawah. Saya melihat ada masalah. Lebih baik saya datang ke sel terakhir secara diagonal terjauh. Tekan F2. Sekarang perilaku default untuk Tata Nama Rumus Tabel adalah saat Anda menyalin rumus ke samping, kolom sebenarnya bergerak seolah-olah merupakan referensi sel campuran. Sekarang kita bisa mengunci mereka tapi saya tidak akan melakukannya kali ini. Sekarang perhatikan ketika Anda menyalinnya, itu berfungsi dengan baik tetapi ketika Anda menyalin ke samping saat itulah kolom yang sebenarnya bergerak. Jadi perhatikan ini, saya akan Ctrl + C dan Ctrl + V dan kemudian itu menghindari F ke kolom bergerak saat Anda menyalinnya ke samping. Klik dua kali dan kirimkan ke bawah. Sekarang rumus% Ubah kami = jumlah akhir / jumlah awal -1, Ctrl + Enter, klik dua kali dan kirimkan.Lebih baik saya datang ke sel terakhir secara diagonal terjauh. Tekan F2. Sekarang perilaku default untuk Nomenklatur Rumus Tabel adalah saat Anda menyalin rumus ke samping, kolom aktual bergerak seolah-olah merupakan referensi sel campuran. Sekarang kita bisa mengunci mereka tapi saya tidak akan melakukannya kali ini. Sekarang perhatikan ketika Anda menyalinnya, itu berfungsi dengan baik tetapi ketika Anda menyalin ke samping saat itulah kolom yang sebenarnya bergerak. Jadi perhatikan ini, saya akan Ctrl + C dan Ctrl + V dan kemudian itu menghindari F ke kolom bergerak saat Anda menyalinnya ke samping. Klik dua kali dan kirimkan ke bawah. Sekarang rumus% Ubah kami = jumlah akhir / jumlah awal -1, Ctrl + Enter, klik dua kali dan kirimkan.Lebih baik saya datang ke sel terakhir secara diagonal terjauh. Tekan F2. Sekarang perilaku default untuk Tata Nama Rumus Tabel adalah saat Anda menyalin rumus ke samping, kolom sebenarnya bergerak seolah-olah merupakan referensi sel campuran. Sekarang kita bisa mengunci mereka tapi saya tidak akan melakukannya kali ini. Sekarang perhatikan ketika Anda menyalinnya, itu berfungsi dengan baik tetapi ketika Anda menyalin ke samping saat itulah kolom yang sebenarnya bergerak. Jadi perhatikan ini, saya akan Ctrl + C dan Ctrl + V dan kemudian itu menghindari F ke kolom bergerak saat Anda menyalinnya ke samping. Klik dua kali dan kirimkan ke bawah. Sekarang rumus% Ubah kami = jumlah akhir / jumlah awal -1, Ctrl + Enter, klik dua kali dan kirimkan.kolom aktual bergerak seolah-olah merupakan referensi sel campuran. Sekarang kita bisa mengunci mereka tapi saya tidak akan melakukannya kali ini. Sekarang perhatikan ketika Anda menyalinnya, itu berfungsi dengan baik tetapi ketika Anda menyalin ke samping saat itulah kolom yang sebenarnya bergerak. Jadi perhatikan ini, saya akan Ctrl + C dan Ctrl + V dan kemudian itu menghindari F ke kolom bergerak saat Anda menyalinnya ke samping. Klik dua kali dan kirimkan ke bawah. Sekarang rumus% Ubah kami = jumlah akhir / jumlah awal -1, Ctrl + Enter, klik dua kali dan kirimkan.kolom aktual bergerak seolah-olah merupakan referensi sel campuran. Sekarang kita bisa mengunci mereka tapi saya tidak akan melakukannya kali ini. Sekarang perhatikan ketika Anda menyalinnya, itu berfungsi dengan baik tetapi ketika Anda menyalin ke samping saat itulah kolom yang sebenarnya bergerak. Jadi perhatikan ini, saya akan Ctrl + C dan Ctrl + V dan kemudian itu menghindari F ke kolom bergerak saat Anda menyalinnya ke samping. Klik dua kali dan kirimkan ke bawah. Sekarang rumus% Ubah kami = jumlah akhir / jumlah awal -1, Ctrl + Enter, klik dua kali dan kirimkan.m pergi ke Ctrl + C dan Ctrl + V dan kemudian itu menghindari F ke kolom bergerak saat Anda menyalinnya ke samping. Klik dua kali dan kirimkan ke bawah. Sekarang rumus% Ubah kami = jumlah akhir / jumlah awal -1, Ctrl + Enter, klik dua kali dan kirimkan.m pergi ke Ctrl + C dan Ctrl + V dan kemudian itu menghindari F ke kolom bergerak saat Anda menyalinnya ke samping. Klik dua kali dan kirimkan ke bawah. Sekarang rumus% Ubah kami = jumlah akhir / jumlah awal -1, Ctrl + Enter, klik dua kali dan kirimkan.

Sekarang, sebelum kita mengujinya, sekarang tambahkan beberapa record baru. Saya sebenarnya ingin membuat label ini di sini agar dinamis. Dan cara saya akan melakukannya adalah saya akan mengatakan = tanda dan kita akan membuat rumus Teks jadi kapan pun kita menginginkan teks dan rumus, Anda harus memasukkannya: "dan saya akan mengetik Penjualan Antara, spasi ”& dan sekarang saya perlu mengekstrak dari satu tanggal di sana, tanggal satu bulan hingga akhir bulan. Saya akan menggunakan fungsi TEXT. Fungsi TEXT dapat menggunakan nomor tanggal atau nomor seri, koma dan menggunakan beberapa format nomor kustom dalam ”. Saya selalu ingin melihat singkatan tiga huruf untuk bulan itu, mmm, saya selalu ingin yang pertama. Sekarang jika saya meletakkan 1 di sini, spasi koma yyy, itu tidak akan berhasil. Ingin melihat yang memberi kita nilai atau karena tidak seperti itu 1. Tapi kita 'kembali diperbolehkan untuk memasukkan satu karakter jika kita menggunakan garis miring, itu dalam format Nomor Kustom. Mm dan yy akan dipahami oleh pemformatan Nomor Kustom sebagai bulan dan tahun dan sekarang format Nomor Kustom akan mengerti untuk memasukkan nomor 1. F2 dan sekarang kita hanya pergi ke: & "-" & TEXT dari koma itu dan sekarang kita Saya hanya akan menggunakan format angka langsung: “mmm spaceD, 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.

Baiklah, baiklah, saya ingin berterima kasih kepada semua orang karena telah mampir. Sampai jumpa di lain waktu untuk Dueling Excel Podcast dari dan Excel Is Fun.

Unduh berkas

Unduh file sampel di sini: Duel181.xlsm

Artikel yang menarik...