Cara mendapatkan data bulanan di excel

PivotTable memiliki beberapa fitur "tersembunyi" berguna yang dapat mempermudah interpretasi data Anda. Salah satu fitur tersebut memungkinkan Anda untuk melihat data berdasarkan pengelompokan tanggal (seperti bulan, kuartal, bahkan minggu dan jam dalam sehari). Dengan mengelompokkan dalam PivotTable itu sendiri, Anda menghindari terus-menerus mengubah data sumber dan membuat beberapa PivotTable dari data yang sama

Untuk menggunakan fitur Grup dalam PivotTable, buat atau buka PivotTable yang berisi data yang diatur berdasarkan tanggal. Langkah-langkah yang ditunjukkan di bawah ini berlaku untuk Excel 2007 dan yang lebih baru. Gambar dalam artikel ini diambil menggunakan Excel 2013 di OS Windows 7. Untuk mengikuti penggunaan data contoh kami, unduh PivotTable Grup menurut Bulan. xlsx

Langkah pertama. Kelompokkan PivotTable Anda menurut Bulan

Klik kanan pada sel mana pun di dalam kolom Tanggal dan pilih Grup dari daftar terbang keluar. Kemudian pilih Bulan di kotak dialog. Menggunakan Mulai dari. dan Berakhir pada. bidang, Anda bahkan dapat menentukan rentang tanggal yang ingin Anda kelompokkan jika Anda tidak ingin mengelompokkan seluruh daftar

Langkah Kedua. Kelompokkan PivotTable Anda menurut Bulan dan Tahun

Perhatikan bahwa dalam contoh kita, ada data tiga tahun. Menggunakan Bulan sebagai filter menggabungkan data penjualan untuk setiap bulan menjadi satu baris, yang mewakili jumlah dari ketiga tahun tersebut. Kami mungkin menggunakan informasi ini untuk melihat apakah ada tren penjualan musiman sepanjang tahun, berdasarkan data beberapa tahun

Namun, jika Anda ingin melihat tren linier selama tiga tahun, cukup tambahkan Tahun ke pilihan Anda di kotak dialog Pengelompokan. Saat Anda menambahkan lebih dari satu Group by, Anda kemudian dapat menciutkan dan memperluas grup dengan mengklik ikon + atau – di samping grup

Langkah ketiga. Kelompokkan PivotTable Anda berdasarkan Minggu

Tidak ada pilihan "Minggu" di kotak dialog Pengelompokan. Untuk mengatur data Anda menurut minggu, pilih Hari, lalu masukkan 7 pada Jumlah hari. bidang

Petunjuk. Tetapkan tanggal mulai minggu Anda

Dalam contoh kami, Anda mungkin memperhatikan bahwa "minggu" pertama adalah 4 Juli-10 Juli, yang pada tahun 2013 adalah hari Kamis-Rabu. Ini terjadi karena tanggal rekaman pertama kami adalah 4 Juli. Untuk memperlihatkan minggu kerja Minggu-Sabtu tradisional di grup Anda, setel bidang Mulai dari secara manual ke hari Minggu yang dekat dengan tanggal pertama Anda yang tercatat, seperti 30 Juni

Langkah Empat. Tampilkan Total berdasarkan Selisih

Setelah Anda mengurutkan informasi berdasarkan bulan, Anda mungkin ingin mempelajari sesuatu yang berbeda tentang data Anda, seperti bagaimana total penjualan Anda berubah dari bulan ke bulan. PivotTable Anda dapat memperlihatkan angka penjualan bulanan Anda dalam hal perbedaan dari bulan sebelumnya

Untuk menampilkan total "Perbedaan dari", klik angka apa pun di kolom yang ingin Anda pengaruhi, dalam hal ini "Jumlah Jumlah Pesanan", lalu klik Pengaturan bidang nilai dari grup Bidang Aktif pada tab Analisis

Pada tab Perlihatkan Nilai Sebagai di kotak dialog, pilih Perbedaan Dari di menu tarik-turun Perlihatkan nilai sebagai, dan (sebelumnya) dari kotak pemilihan item Dasar. PivotTable Anda sekarang akan memperlihatkan kinerja setiap Tenaga Penjual dari waktu ke waktu dengan menunjukkan perbedaan penjualan dari bulan sebelumnya

Mengelompokkan data dalam PivotTable, dan khususnya data berbasis tanggal, memungkinkan Anda untuk menggabungkan kekuatan analisis PivotTable dengan alat yang sudah tidak asing lagi yang membuat pembacaan hasil jauh lebih berguna

Ini akan memformat tanggal 23-04-2020 sebagai 23 April 2020, sehingga Anda dapat melihat nama lengkap bulan dalam bahasa Inggris

Ini tidak mengubah nilai dasar di dalam sel. Tanggalnya masih sama, hanya formatnya yang berbeda

Format Kustom

Metode ini sangat mirip dengan format tanggal yang panjang, tetapi akan memungkinkan Anda untuk memformat tanggal agar hanya menampilkan nama bulan dan tidak menyertakan informasi hari atau tahun.

Pilih sel yang ingin Anda format ➜ klik kanan ➜ pilih Format Sel dari menu. Anda juga dapat menggunakan pintasan keyboard Ctrl + 1 untuk memformat sel

Dalam kotak dialog Format Sel

1. Buka tab Angka
2. Pilih Kustom dari opsi Kategori
3. Masukkan mmmm ke dalam kotak input Jenis
4. Tekan tombol OK

Ini akan memformat tanggal 23-04-2020 sebagai April, jadi Anda hanya akan melihat nama bulan penuh tanpa bagian hari atau tahun

Sekali lagi, pemformatan ini tidak akan mengubah nilai yang mendasarinya, hanya akan muncul berbeda di grid

Anda juga dapat menggunakan format khusus mmm untuk menghasilkan nama bulan yang disingkat dari tanggal seperti April, bukan April

Isi Flash

Setelah Anda memformat kolom nilai tanggal dalam format tanggal panjang yang ditunjukkan di atas, Anda dapat mengekstrak nama menjadi nilai teks menggunakan flash fill

Mulailah mengetik beberapa contoh nama bulan di kolom yang berdekatan dengan tanggal yang diformat

Excel akan menebak polanya dan mengisi tebakannya dengan warna abu-abu terang. Anda kemudian dapat menekan Enter untuk menerima nilai-nilai ini

Dengan cara ini nama bulan akan ada sebagai nilai teks dan bukan hanya tanggal yang diformat

Fungsi TEKS

Metode pemformatan sebelumnya hanya mengubah tampilan tanggal untuk menampilkan nama bulan

Dengan fungsi TEXT, Anda dapat mengonversi tanggal menjadi nilai teks

= TEXT ( B3, "mmmm" )

Rumus di atas akan mengambil nilai tanggal di sel B3 dan menerapkan pemformatan khusus mmmm. Hasilnya akan menjadi nilai teks dari nama bulan

Fungsi BULAN

Excel memiliki fungsi MONTH yang dapat mengekstrak bulan dari tanggal

BULAN dan BERALIH

Ini diekstraksi sebagai nilai numerik dari 1 hingga 12, jadi Anda juga perlu mengubah angka itu menjadi nama. Untuk melakukan ini, Anda dapat menggunakan fungsi SWITCH

= SWITCH (
    MONTH(B3),
    1,"January",
    2,"February",
    3,"March",
    4,"April",
    5,"May",
    6,"June",
    7,"July",
    8,"August",
    9,"September",
    10,"October",
    11,"November",
    12,"December"
)

Rumus di atas akan mendapatkan nomor bulan dari tanggal di sel B3 menggunakan fungsi BULAN

Fungsi SWITCH kemudian akan mengubah angka itu menjadi sebuah nama

BULAN dan PILIH

Ada cara yang lebih sederhana untuk menggunakan fungsi MONTH seperti yang ditunjukkan Wayne di komentar

Anda dapat menggunakan fungsi CHOOSE karena angka bulan dapat sesuai dengan pilihan fungsi CHOOSE

= CHOOSE (
    MONTH(B3),
    "January",
    "February",
    "March",
    "April",
    "May",
    "June",
    "July",
    "August",
    "September",
    "October",
    "November",
    "December"
)

Rumus di atas akan mendapatkan nomor bulan dari tanggal di sel B3 menggunakan fungsi MONTH, CHOOSE kemudian mengembalikan nama bulan yang sesuai berdasarkan nomor tersebut

Permintaan Daya

Anda juga dapat menggunakan power query untuk mengubah tanggal menjadi nama

Pertama, Anda perlu mengubah data Anda menjadi tabel Excel

Kemudian Anda dapat memilih tabel Anda dan pergi ke tab Data dan menggunakan perintah From Table/Range

Ini akan membuka editor kueri daya tempat Anda dapat menerapkan berbagai transformasi ke data Anda

Ubah kolom tanggal menjadi nama bulan

1. Pilih kolom tanggal yang akan diubah
2. Buka tab Transform di perintah pita editor kueri daya
3. Klik tombol Tanggal di bagian Kolom Tanggal & Waktu
4. Pilih Bulan dari Menu
5. Pilih Nama Bulan dari sub-menu

Ini akan mengubah kolom tanggal Anda menjadi nilai teks dari nama bulan penuh

= Table.TransformColumns ( #"Changed Type", {{"Date", each Date.MonthName(_), type text}} )

Ini secara otomatis akan membuat rumus kode M di atas untuk Anda dan tanggal Anda akan diubah

Anda kemudian dapat membuka tab Beranda dan menekan Tutup & Muat untuk memuat kembali data yang diubah ke dalam tabel di buku kerja Excel

Nilai Tabel Pivot

Jika Anda memiliki tanggal dalam data dan ingin meringkas data berdasarkan bulan, maka tabel pivot adalah pilihan yang tepat

Pilih data Anda lalu buka tab Sisipkan dan klik pada perintah Tabel Pivot. Anda kemudian dapat memilih lembar dan sel untuk menambahkan tabel pivot

Setelah Anda membuat tabel pivot, Anda perlu menambahkan bidang ke dalam area Baris dan Nilai di jendela Bidang PivotTable

Seret dan lepas bidang Tanggal ke dalam area Baris dan bidang Penjualan ke dalam area Nilai

Ini akan secara otomatis membuat bidang Bulan yang meringkas penjualan per bulan di tabel pivot Anda dan nama bulan yang disingkat akan muncul di tabel pivot Anda

Kolom Perhitungan Power Pivot

Anda juga dapat membuat kolom terhitung dengan tabel pivot dan model data

Ini akan menghitung nilai untuk setiap baris data dan membuat bidang baru untuk digunakan dalam daftar Bidang PivotTable

Ikuti langkah yang sama seperti di atas untuk menyisipkan tabel pivot

Di kotak dialog Buat Tabel Pivot, centang opsi Tambahkan data ini ke Model Data, lalu tekan tombol OK

Setelah membuat tabel pivot, buka tab Data dan tekan perintah Manage Data Model untuk membuka editor power pivot

= FORMAT ( Table1[Date], "mmmm" )

Buat kolom baru dengan rumus di atas di dalam editor power pivot

Tutup editor dan kolom baru Anda akan tersedia untuk digunakan dalam daftar Bidang PivotTable

Kolom terhitung ini akan muncul sebagai bidang baru di dalam jendela Bidang PivotTable dan Anda dapat menggunakannya seperti bidang lainnya di data Anda

Kesimpulan

Itulah 8 cara mudah mendapatkan nama bulan dari tanggal di Excel

Jika Anda hanya ingin menampilkan nama, opsi pemformatan mungkin cukup

Jika tidak, Anda dapat mengonversi tanggal menjadi nama dengan isian cepat, rumus, kueri daya, atau bahkan di dalam tabel pivot