Mulailah dengan data beberapa tahun.
Katakanlah Anda memiliki catatan detail selama dua tahun. Setiap catatan memiliki tanggal harian. Saat Anda membuat tabel pivot dari laporan ini, Anda akan memiliki ratusan baris tanggal harian di tabel pivot. Ini bukan ringkasan.
Pilih salah satu sel tanggal tersebut di tabel pivot. Dari tab Analisis di pita, pilih Bidang Grup.
Karena Anda berada di bidang tanggal, Anda mendapatkan versi dialog Pengelompokan ini. Di dalamnya, batal pilih Bulan dan pilih Tahun.
Tanggal harian digulung hingga bertahun-tahun. Pindahkan bidang Tanggal dari ROWS ke COLUMNS.
Hasilnya hampir sempurna. Namun, alih-alih total keseluruhan di kolom D, Anda mungkin menginginkan varian persentase.
Untuk menghilangkan kolom Total Keseluruhan, pada tab Desain pilih Total Keseluruhan, Aktif untuk Kolom Saja. (Saya setuju, ini adalah salah satu susunan kata yang lebih canggung di Excel.)
Untuk membuat kolom varians, Anda perlu menulis rumus di luar tabel pivot yang mengarah ke dalam tabel pivot. Jangan sentuh mouse atau tombol panah saat membuat formula, atau fungsi GETPIVOTDATA yang buruk akan muncul. Sebagai gantinya, cukup ketik =C5/B5-1dan tekan Enter.
Terima kasih kepada Александр Воробьев karena telah menyarankan tip ini.
Menonton video
- Mulailah dengan data beberapa tahun
- Sisipkan, tabel Pivot
- Tarik bidang tanggal ke area baris
- Excel 2016: Tekan Ctrl + Z untuk memisahkan tanggal
- Tarik pendapatan ke area nilai
- Pilih tanggal mana pun di area baris
- Gunakan Bidang Grup
- Pilih Bulan & Tahun
- Bagaimana menambahkan subtotal ke bidang tahun setelah mengelompokkan tabel pivot
- Gunakan bentuk Tabular dalam tabel pivot untuk memberi setiap bidang baris kolomnya sendiri
- Ulangi Semua Label Baris untuk mengisi bagian yang kosong di tabel pivot
- Tarik tahun ke area kolom
- Klik kanan judul kolom Total Keseluruhan dan hapus
- Cara menghindari fungsi GetPivotData saat rumus mengarah ke tabel pivot
- Untuk membuat varian, ketikkan rumus tanpa mouse atau panah
- Terima kasih kepada Александр Воробьев karena telah menyarankan tip ini
Transkrip Video
Pelajari Excel dari podcast MrExcel, episode 1998. Tabel Pivot Tahun demi Tahun.
Saya akan membuat podcasting seluruh buku ini, lanjutkan, klik "i" di pojok kanan atas, untuk playlist MrExcel Excel.
Hai, selamat datang kembali di netcast, saya Bill Jelen. Hari ini topik pertama dalam buku tentang Tabel Pivot dan cara membuat laporan dari tahun ke tahun. Jadi kami memulai dengan kumpulan data, yang mencakup dua tahun, Anda lihat, kami mulai dari 2018 hingga 2019. Saya akan membuat Tabel Pivot, Sisipkan, PivotTable, Oke, tidak apa-apa, mari kita lanjutkan ke lembar kerja baru.
Pada lembar kerja baru kita akan mengambil bidang Tanggal dan menyeretnya ke area Baris. Dan kemudian, Anda lihat, di Excel 2016 secara otomatis mengelompokkan semuanya. Sekarang saya tahu kebanyakan orang masih menggunakan Excel 2013, mungkin Excel 2010, jadi saya akan menekan Ctrl + Z untuk membatalkan. Itulah pengalaman yang akan Anda dapatkan di tahun 2010 dan 2013, kami akan membuka bidang Pendapatan dan menyeretnya ke area Nilai. Jadi kami memulai dengan laporan yang buruk, akan ada ratusan dan ratusan baris, karena setiap hari kami melakukan penjualan. Tapi saya akan pergi ke bidang tanggal pertama, bidang tanggal pertama, pergi ke Alat PivotTable, Analisis, pada 2013 atau 2016 dan 2010 disebut Opsi, dan pilih Bidang Grup. Pastikan Anda memilih tanggal sebelum memilih Bidang Grup dan menggulung semuanya menjadi Bulan dan Tahun. Atau Quarters and Years,atau Bulan dan Kuartal dan Tahun, klik OK. Dan yang kami dapatkan, adalah bidang virtual baru yang disebut Bertahun-tahun di sini, di area Rows.
Sekarang beberapa gangguan. Sangat menjengkelkan karena mereka tidak memberi kami total Tahun, cukup mudah untuk memperbaikinya, meskipun dalam kasus saya, saya tidak perlu memperbaikinya. Kami mengklik Pengaturan Bidang dan Otomatis, karena saya tidak berencana meninggalkan Tahun di sini. Tentu saja setiap kali saya membuat PivotTable, saya pergi ke Design dan berkata Show in Tabular Form untuk memasukkan semuanya ke kolomnya sendiri. Dan kemudian Ulangi Semua Label Item untuk mengisi kekosongan di sepanjang sisi kiri. Sekali lagi, itu tidak perlu di sini, karena saya tidak berencana meninggalkan Tahun-tahun di tempatnya, saya akan mengambil Tahun-tahun itu dan menyeretnya ke Kolom, dan itulah laporan yang saya bidik.
Baiklah, jadi kita punya Bulan di sisi kiri, 2018 ke 2019. Di kolom D, apa yang benar-benar saya inginkan, saya tidak ingin Total Keseluruhan, saya akan klik kanan dan ucapkan Hapus Grand Total dan sekarang kita berada di luar PivotTable. Jadi,% Change, sangat menggoda untuk membuat formula ini menggunakan mouse atau tombol panah, tapi Anda tidak bisa, karena Anda akan mendapatkan… get fungsi Data Pivot, yang tidak mudah disalin. Jadi = C5 / B5-1, kami akan memformatnya sebagai persentase dan klik dua kali untuk menyalinnya, benar. Laporan indah yang menunjukkan di mana kami berada tahun lalu, di mana kami berada tahun ini dan kemudian persentase berubah dari tahun ke tahun.
Nah, tip ini dan 40 lainnya, semuanya ada di buku ini. Silakan, klik "i" di pojok kanan atas, dekat buku. Baiklah, jadi Anda memulai dengan kumpulan data dengan beberapa tahun, lakukan Sisipkan, tabel Pivot, seret bidang tanggal ke area baris. Dan di Excel 2016 tekan Ctrl + Z untuk membatalkan pembaruan pengelompokan otomatis mereka. Mengapa kita tidak meninggalkan ini di sini? Ya, sebenarnya tergantung. Jika Anda memiliki data dari, katakanlah, 2 Januari hingga 30 Desember, mereka tidak akan mengelompokkan data tersebut dengan benar. Itu harus menghabiskan satu tahun penuh bagi Anda untuk mendapatkan bidang tahun. Mungkin itu berhasil untuk Anda, saya sedikit gila kontrol, saya lebih suka hanya memiliki kendali, itulah mengapa saya membatalkan. Ada: seret pendapatan ke area nilai, pilih sel apa pun, tanggal mana pun di area baris lalu Grup Bidang, pilih Bulan & Tahun,seret tahun di atas area kolom, klik kanan Total Keseluruhan untuk menghapus dan lalu menyusun varian, ketik rumus, jangan gunakan tombol mouse atau panah.
Saya tidak tahu bagaimana mengucapkannya, tapi terima kasih kepada pembaca ini (Александр Воробьев) karena telah menyarankan tip ini dan terima kasih kepada Anda karena telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh berkas
Unduh file contoh di sini: Podcast1998.xlsx
