Cara menunjukkan jualan Bulan ke Tarikh dalam jadual pangsi. Ini adalah episod Dueling Excel.
Tonton Video
- Kaedah Bill
- Tambahkan sel pembantu dengan formula MTD
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))- Tambahkan medan itu sebagai Pemotong di mana = Benar
- Petua bonus: Tarikh Harian Kumpulan hingga Tahun
- Tambahkan pengiraan di luar jadual pangsi sambil mengelakkan GetPivotData
- Pendekatan Mike:
- Ubah data menjadi jadual menggunakan Ctrl + T. Ini membolehkan lebih banyak data ditambahkan ke jadual dan formula dikemas kini.
- SUMIF dengan fungsi DATE, BULAN, HARI
- Menekan F4 tiga kali mengunci rujukan hanya pada lajur.
- Awas - jika anda menyeret formula Jadual ke sisi, lajur akan berubah. Salin & Tampal - tidak ada masalah
- Menggunakan TEXT (tarikh, format. Trik yang bagus dengan 1 untuk memasukkan nombor 1 dalam teks
Transkrip Video
Bill Jelen: Hei, selamat datang kembali. Sudah tiba masanya untuk Dueling Excel Podcast yang lain. Saya Bill Jelen dari. Saya akan disertai oleh Mike Girvin dari Excel Is Fun.
Ini adalah Jadual Pangsi Episod 181: Bulan Hingga Tarikh.
Baiklah, hei, soalan hari ini - idea hari ini untuk pertarungan ini dihantar oleh Mike. Dia berkata, "Bolehkah anda membuat laporan Bulan Hingga Tarikh di jadual pangsi?"
Baiklah, mari kita pergi. Jadi inilah yang kita ada, kita mempunyai tarikh dua tahun dari Januari 2016 hingga tahun 2017. Sekarang sudah tentu saya merakamnya pada bulan April, ia adalah 15 April sekarang ketika saya merakam karya saya. Dan di sini kami mempunyai jadual pangsi yang menunjukkan Hari di sebelah kiri, Kategori di bahagian atas, dan Pendapatan di tengah-tengah jadual pangsi.
Sekarang, untuk membuat laporan Bulan Hingga Tarikh, apa yang akan saya lakukan ialah saya akan mengatakan bahawa saya akan menambahkan lajur pembantu baru di sini pada data asal saya dan itu akan memeriksa dua perkara. Dan kerana saya memeriksa dua perkara, saya akan menggunakan fungsi AND, kedua-dua perkara itu mesti benar untuk menjadi Bulan Sehingga Tarikh. Dan saya akan menggunakan fungsi di sini yang dipanggil HARI INI. HARI INI, baiklah, jadi saya ingin tahu sama ada BULAN HARI INI ()) = hingga BULAN tarikh itu di sana di Lajur A. Sekiranya itu benar, jika ia adalah bulan semasa, jadi dengan kata lain jika ia adalah bulan April, maka periksa dan lihat apakah hari tarikh di sana di A2 adalah <= HARI HARI INI. Perkara yang indah adalah apabila kita membuka buku kerja ini esok atau seminggu dari sekarang, hari ini akan dikemas kini secara automatik dan kita klik dua kali untuk menyalinnya.
Baiklah sekarang, kita harus memasukkan data tambahan ini ke dalam jadual pangsi kita jadi saya datang ke sini Pivot Table, Analisis dan tidak sukar untuk menukar sumber data, cukup klik butang besar di sana dan katakan bahawa kami ingin pergi ke Lajur D , klik OK. Baiklah, jadi sekarang kita mempunyai medan tambahan itu, saya akan Masukkan Slicer berdasarkan bidang Bulan Hingga Tarikh itu dan saya hanya ingin melihat bagaimana Bulan Tarikh Kita Benar. Sekarang, adakah kita memerlukan Slice yang besar? Tidak, kita mungkin boleh menjadikannya dua lajur dan semacamnya tidak mengganggu di sebelah kanan. Jadi sekarang yang kita ada adalah semua tarikh pada tahun 2016 dan semua tarikh pada tahun 2017; walaupun, sangat senang membandingkannya secara berdampingan. Oleh itu, saya akan mengambil bidang Tarikh dan Menganalisis. Saya akan mengelompokkan Padang, saya akan mengumpulkannya hingga bertahun-tahun. Saya tidak 'Saya sebenarnya mengambil berat tentang setiap hari. Saya cuma nak tahu Bulan Hingga Tarikh. Sekarang, di mana kita berada? Oleh itu, saya akan mengumpulkannya hingga bertahun-tahun dan kami akan berakhir dengan 2 tahun ini di sana dan saya akan kemudian menyusunnya semula, meletakkan Tahun-tahun yang akan dilalui, Kategori untuk turun. Dan sekarang saya melihat di mana kita berada tahun lalu dan di mana kita berada tahun ini. Baiklah sekarang, kerana saya sudah selesai mengumpulkan, saya tidak lagi dibenarkan membuat medan yang dikira di dalam jadual pangsi. Sekiranya saya ingin mempunyai jumlah tahun ke tahun di sana, saya akan mengklik kanan, Buang Grand Total, baiklah, dan sekarang kita, jadi,% Ubah, kita berada di luar meja pangsi yang menunjuk ke dalam jadual pangsi . Kita harus memastikan sama ada mematikan GetPivotData atau hanya membina formula seperti ini: = J4 / I4-1 dan itu membuat formula yang dapat kita salin tanpa kerumitan sama sekali, seperti itu.Baiklah, Mike, mari lihat apa yang anda ada.
Mike Girvin: Terima kasih,. Ya, saya telah menghantar soalan itu kerana saya melakukannya dengan formula dan saya tidak dapat mengetahui cara melakukannya dengan jadual pangsi standard dan kemudian saya teringat melihat selama bertahun-tahun, membuat banyak video menarik mengenai tiang penolong dan jadual pangsi . Itu adalah formula yang indah dan penyelesaian yang indah. Oleh itu, cara melakukannya dengan jadual pangsi, mari kita lihat bagaimana melakukannya dengan formula.
Sekarang, saya melakukan ini dua hari selepas dia melakukannya. F2 Saya mempunyai fungsi HARI INI yang selalu menjadi maklumat tarikh untuk tarikh semasa hari ini yang akan digunakan oleh formula di sini kerana kami mahu ia dikemas kini. Saya juga pernah menggunakan jadual Excel dan dinamakan FSales. Sekiranya saya Ctrl + Down Arrow, saya melihatnya adalah 4/14 tetapi saya mahu dapat menambahkan rekod terkini dan memasukkan formula kami kemas kini ketika kita melonjak ke bulan berikutnya. Ctrl + Anak Panah Atas. Baiklah, saya mempunyai Kriteria Tahun sebagai tajuk lajur, Kategori sebagai tajuk baris, dan kemudian butiran untuk bulan dan hari akan datang dari sel itu. Oleh itu, saya hanya akan menggunakan fungsi SUMIFS kerana kita menambah dengan pelbagai syarat, jumlah penjumlahan inilah hasilnya, kita akan menggunakan trik hebat itu untuk jadual Excel.Tepat di bahagian atas kita melihat panah yang menunjuk ke bawah hitam, BAM! Itu meletakkan nama jadual yang betul dan kemudian dalam tanda kurung persegi nama medan, koma. Julat kriteria, kita mesti menggunakan Tarikh dua kali, jadi saya akan mulakan dengan Tarikh. Klik, ada Column Date, koma. Sekarang saya berada di bulan April, jadi saya perlu membuat syarat> = hingga 1 April. Oleh itu, operator perbandingan "> =" dalam petikan ganda dan saya akan menyertainya. Sekarang saya harus membuat beberapa formula tarikh yang selalu kelihatan di sini dan mencipta bulan pertama untuk tahun tertentu ini. Jadi saya akan menggunakan fungsi DATE. Tahun, betul saya mempunyai Tahun yang tepat sebagai tajuk lajur dan saya akan menekan kekunci F4 satu, dua kali untuk mengunci baris tetapi bukan lajur jadi apabila bergerak di sini kita akan beralih ke 2017, koma, Bulan - Saya 'Saya akan menggunakan fungsi BULAN untuk mendapatkan bulan nombor 1 hingga 12. Itulah bulan yang ada dalam sel itu, F4 untuk menguncinya ke semua arah, tutup tanda kurung dan kemudian koma, 1 ia akan selalu menjadi yang pertama bulan tidak kira bulan apa ini, tutup kurungan.
Baiklah, jadi itu kriteria. Selalu> = pertama bulan, koma, kriteria berkisar dua Saya akan mendapatkan lajur Tarikh, koma. Kriteria dua, baik, ini akan menjadi <= had atas, jadi dalam "<=" dan &. Saya akan menipu, menonton ini. Saya hanya akan menyalinnya dari sini kerana ia adalah perkara yang sama, Ctrl-C Ctrl-V kecuali untuk Hari, kita harus menggunakan fungsi HARI dan selalu mendapat had atas kita apa sahaja hari dari bulan tertentu ini . F4 untuk menguncinya ke semua arah, tutup tanda kurung pada Tarikh. Baiklah, jadi itulah kriteria kami dua: koma. Kriteria merangkumi 3, itu adalah Kategori. Itu dia, koma dan ada tajuk baris kami. Oleh itu, kita harus F4 satu dua tiga kali, mengunci lajur tetapi bukan baris jadi apabila kita menyalin formula ke bawah, kita akan beralih ke Gizmo dan Widget,kurung tutup dan itulah formula. Seret, klik dua kali dan hantar ke bawah. Saya dapat melihat ada masalah. Saya lebih baik datang ke sel terakhir secara menyerong jauh. Tekan F2. Sekarang tingkah laku lalai untuk Nomenklatur Formula Jadual adalah apabila anda menyalin formula ke sisi, lajur sebenar bergerak seolah-olah mereka adalah rujukan sel campuran. Sekarang kita dapat mengunci mereka tetapi kali ini saya tidak akan melakukannya. Sekarang perhatikan apabila anda menyalinnya, ia berfungsi dengan baik tetapi apabila anda menyalinnya ke sisi itulah lajur sebenar bergerak. Oleh itu, perhatikan ini, saya akan pergi ke Ctrl + C dan Ctrl + V dan kemudian mengelakkan F ke lajur tidak bergerak semasa anda menyalinnya ke sisi. Klik dua kali dan hantarkan. Sekarang formula% Change kami = jumlah akhir / jumlah permulaan -1, Ctrl + Enter, klik dua kali dan hantarkan.Seret, klik dua kali dan hantar ke bawah. Saya dapat melihat ada masalah. Saya lebih baik datang ke sel terakhir secara menyerong jauh. Tekan F2. Sekarang tingkah laku lalai untuk Nomenklatur Formula Jadual adalah apabila anda menyalin formula ke sisi, lajur sebenar bergerak seolah-olah mereka adalah rujukan sel campuran. Sekarang kita dapat menguncinya tetapi kali ini saya tidak akan melakukannya. Sekarang perhatikan apabila anda menyalinnya, ia berfungsi dengan baik tetapi apabila anda menyalinnya ke sisi itulah lajur sebenar bergerak. Oleh itu, perhatikan ini, saya akan pergi ke Ctrl + C dan Ctrl + V dan kemudian itu mengelakkan F ke lajur daripada bergerak apabila anda menyalinnya ke sisi. Klik dua kali dan hantarkan. Sekarang formula% Change kami = jumlah akhir / jumlah permulaan -1, Ctrl + Enter, klik dua kali dan hantarkan.Seret, klik dua kali dan hantar ke bawah. Saya dapat melihat ada masalah. Lebih baik saya datang ke sel terakhir secara menyerong jauh. Tekan F2. Sekarang tingkah laku lalai untuk Nomenklatur Formula Jadual adalah apabila anda menyalin formula ke sisi, lajur sebenar bergerak seolah-olah mereka adalah rujukan sel campuran. Sekarang kita dapat mengunci mereka tetapi kali ini saya tidak akan melakukannya. Sekarang perhatikan apabila anda menyalinnya, ia berfungsi dengan baik tetapi apabila anda menyalinnya ke sisi itulah lajur sebenar bergerak. Oleh itu, perhatikan ini, saya akan pergi ke Ctrl + C dan Ctrl + V dan kemudian itu mengelakkan F ke lajur daripada bergerak apabila anda menyalinnya ke sisi. Klik dua kali dan hantarkan. Sekarang formula% Change kami = jumlah akhir / jumlah permulaan -1, Ctrl + Enter, klik dua kali dan hantarkan.Lebih baik saya datang ke sel terakhir secara menyerong jauh. Tekan F2. Sekarang tingkah laku lalai untuk Nomenklatur Formula Jadual adalah apabila anda menyalin formula ke sisi, lajur sebenar bergerak seolah-olah mereka adalah rujukan sel campuran. Sekarang kita dapat mengunci mereka tetapi kali ini saya tidak akan melakukannya. Sekarang perhatikan apabila anda menyalinnya, ia berfungsi dengan baik tetapi apabila anda menyalinnya ke sisi itulah lajur sebenar bergerak. Oleh itu, perhatikan ini, saya akan pergi ke Ctrl + C dan Ctrl + V dan kemudian itu mengelakkan F ke lajur daripada bergerak apabila anda menyalinnya ke sisi. Klik dua kali dan hantarkan. Sekarang formula% Change kami = jumlah akhir / jumlah permulaan -1, Ctrl + Enter, klik dua kali dan hantarkan.Lebih baik saya datang ke sel terakhir secara menyerong jauh. Tekan F2. Sekarang tingkah laku lalai untuk Nomenklatur Formula Jadual adalah apabila anda menyalin formula ke sisi, lajur sebenar bergerak seolah-olah mereka adalah rujukan sel campuran. Sekarang kita dapat mengunci mereka tetapi kali ini saya tidak akan melakukannya. Sekarang perhatikan apabila anda menyalinnya, ia berfungsi dengan baik tetapi apabila anda menyalinnya ke sisi itulah lajur sebenar bergerak. Oleh itu, perhatikan ini, saya akan pergi ke Ctrl + C dan Ctrl + V dan kemudian itu mengelakkan F ke lajur daripada bergerak apabila anda menyalinnya ke sisi. Klik dua kali dan hantarkan. Sekarang formula% Change kami = jumlah akhir / jumlah permulaan -1, Ctrl + Enter, klik dua kali dan hantarkan.lajur sebenar bergerak seolah-olah mereka adalah rujukan sel campuran. Sekarang kita dapat mengunci mereka tetapi kali ini saya tidak akan melakukannya. Sekarang perhatikan apabila anda menyalinnya, ia berfungsi dengan baik tetapi apabila anda menyalinnya ke sisi itulah lajur sebenar bergerak. Oleh itu, perhatikan ini, saya akan pergi ke Ctrl + C dan Ctrl + V dan kemudian itu mengelakkan F ke lajur daripada bergerak apabila anda menyalinnya ke sisi. Klik dua kali dan hantarkan. Sekarang formula% Change kami = jumlah akhir / jumlah permulaan -1, Ctrl + Enter, klik dua kali dan hantarkan.lajur sebenar bergerak seolah-olah mereka adalah rujukan sel campuran. Sekarang kita dapat mengunci mereka tetapi kali ini saya tidak akan melakukannya. Sekarang perhatikan apabila anda menyalinnya, ia berfungsi dengan baik tetapi apabila anda menyalinnya ke sisi itulah lajur sebenar bergerak. Oleh itu, perhatikan ini, saya akan pergi ke Ctrl + C dan Ctrl + V dan kemudian itu mengelakkan F ke lajur daripada bergerak apabila anda menyalinnya ke sisi. Klik dua kali dan hantarkan. Sekarang formula% Change kami = jumlah akhir / jumlah permulaan -1, Ctrl + Enter, klik dua kali dan hantarkan.m pergi ke Ctrl + C dan Ctrl + V dan kemudian itu mengelakkan F ke lajur daripada bergerak semasa anda menyalinnya ke sisi. Klik dua kali dan hantarkan. Sekarang formula% Change kami = jumlah akhir / jumlah permulaan -1, Ctrl + Enter, klik dua kali dan hantarkan.m pergi ke Ctrl + C dan Ctrl + V dan kemudian itu mengelakkan F ke lajur daripada bergerak semasa anda menyalinnya ke sisi. Klik dua kali dan hantarkan. Sekarang formula% Change kami = jumlah akhir / jumlah permulaan -1, Ctrl + Enter, klik dua kali dan hantarkan.
Now, before we go test it, now add some new records. I actually want to create this label up here so it's dynamic. And the way I'm going to do that is I'm going to say = sign and we're going to do a Text formula so anytime we want text and a formula, you have to put it in: “ and I'm going to type Sales Between, space”& and now I need to extract from that single date there, the first of the month to the end of the month. I'm going to use the TEXT function. The TEXT function can take a number dates or serial numbers, comma and use some custom number formatting in ” . I always want to see three-letter abbreviation for the month, mmm, I always want it as the first. Now if I put a 1 here, comma space yyy, that won't work. Wants to see that that gives us a value or because it doesn't like that 1. But we're allowed to insert a single character if we use forward slash, that's in Custom Number formatting. The mm and the yy will be understood by Custom Number formatting as month and year and now Custom Number format will understand to insert the number 1. F2 and now we're simply going to: &“ - ”&TEXT of that comma and now we’ll just use straight number formatting: “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, hei, saya ingin mengucapkan terima kasih kepada semua orang kerana mampir. Kami akan berjumpa anda di lain masa untuk Dueling Excel Podcast lain dan Excel Is Fun.
Muat turun fail
Muat turun fail sampel di sini: Duel181.xlsm
