Cara mengubah data bulanan menjadi data tahunan di excel

Model dan video di halaman ini membahas cara mengatasi masalah menyakitkan dalam menyajikan data bulanan (atau triwulanan) dalam kolom terpisah dan kemudian juga menyajikan total tahunan setelah setiap tahun. Misalnya, mungkin ada data bulan demi bulan yang rinci yang diikuti dengan total tahunan. Masalahnya adalah saat menjumlahkan total tahunan dalam presentasi seperti itu, Anda tidak boleh mengubah rumus di seluruh kolom. Halaman ini menjelaskan bagaimana Anda dapat menggunakan INDEX, MATCH dan SUMIF untuk membuat penjumlahan tahunan menjadi proses yang cukup lancar

Penjelasan cara mengatasi masalah presentasi subtotal tahunan disertakan dalam file yang dapat Anda unduh dengan menekan tombol di bawah ini. File ini menyertakan tangkapan layar di bawah yang memandu melalui langkah-langkah berbeda yang melibatkan penghitungan dengan penambahan dalam model bulan demi bulan yang mendetail, menggunakan SUMIF dalam model mendetail untuk menemukan data tahunan;

File Model Keuangan yang Menjelaskan Cara Membuat Model dengan Subtotal Tahunan atau Triwulanan tanpa Mengubah Rumus

Pengaturan Waktu dalam Model Perusahaan dan Subtotal yang Mengumpulkan Data Bulanan atau Kuartalan

Saya tidak begitu tahu di mana harus meletakkan model yang menggambarkan bagaimana mengambil data bulanan dan kemudian membuatnya menjadi data triwulanan, semesteran, atau tahunan. Hal yang tidak boleh dilakukan adalah memasukkan sub-total secara manual dengan fungsi penjumlahan. Jika Anda melakukan ini, model Anda akan kikuk, tidak fleksibel, dan tidak akurat. Sebagai gantinya, Anda dapat menyertakan lembar terpisah yang meringkas informasi yang sama seperti dalam analisis terperinci Anda, tetapi menyertakan subtotal yang fleksibel. Anda perlu membuat penghitung dengan fungsi MOD setelah memulai dengan penghitung periode dasar. Kemudian gunakan fungsi SUMIF untuk menambahkan lintas bulan atau kuartal. Terakhir, gunakan MATCH (dengan nol di akhir) dan INDEX untuk menyatukan lembar dengan sub-total. Model ini menunjukkan pentingnya pengaturan garis waktu dalam model keuangan perusahaan. Garis waktu sangat penting untuk riwayat versus perkiraan serta jumlah fleksibel

Screenshot yang Mengilustrasikan Akhir Proses

Dua tangkapan layar di bawah menggambarkan apa yang Anda inginkan di akhir proses. Tangkapan layar pertama menunjukkan presentasi bulan demi bulan dengan data tahunan pada akhir tahun. Tangkapan layar kedua menunjukkan data bulanan dengan subtotal untuk setiap kuartal. Saya akan meringkas pendekatan langkah demi langkah dan kemudian menjelaskan setiap langkah secara rinci di bawah ini di bagian terpisah. Perhatikan nomor periode dan nomor kolom di bagian atas halaman. Nomor periode hanyalah penghitung. Nomor kolom dijelaskan di bawah ini dan menggunakan fungsi MATCH

Proses Langkah demi Langkah untuk Membuat Lembar Pelaporan dengan Ringkasan

Saya tahu bahwa sebagian besar dari Anda berkata, persetan, saya bisa melakukannya lebih cepat dengan tangan dengan formula berbeda. Proses langkah demi langkah mungkin tampak rumit, tetapi Anda dapat terbiasa dengan cepat dan itu tidak terlalu buruk. Poin utamanya adalah menjaga model Anda tetap bersih (mis. e. tanpa sub-total) dan memiliki rumus yang sama di setiap kolom. Itulah titik awalnya. Kemudian, lembaran yang menumpuk setiap periode ke-12 atau ke-4 hanyalah lembaran keluaran (seperti meringkas angka tahunan dalam model pembiayaan proyek bulanan). Setelah Anda memiliki model bulanan bersih dengan persamaan kunci, Anda dapat melakukan hal berikut

1. Siapkan variabel penghitung dengan EIS yang menghitung setiap bulan
2. Gunakan fungsi MOD untuk menunjukkan BENAR setiap kali nomor bulan sebelumnya habis dibagi 12 atau 4
3. Gunakan BENAR dari atas untuk mengembangkan penghitung kedua yang melompati angka saat akhir tahun tercapai
4. Gunakan penghitung SUMIF dan Tahun untuk membuat total tahunan di sebelah kanan model
5. Sertakan Penghitung pada total tahunan yang dimulai dengan 13 dan tingkatkan jumlahnya dengan 13 (atau dengan 5 dan kenaikan dengan 5)
6. Buat Lembar Subtotal Tahunan dan buat penghitung sederhana;
7. Gunakan INDEX dengan MATCH dan buat tombol TRUE/FALSE ketika total Tahunan (atau kuartalan) terjadi

Model Bulanan Bersih

Dalam model bulanan bersih, buat penghitung berdasarkan bulan seperti yang ditunjukkan pada tangkapan layar di bawah. Jauh lebih baik untuk membuat model Anda bersih untuk persamaan yang konsisten dan juga untuk fleksibilitas dan juga untuk pembuatan grafik. Perhatikan penghitung periode di baris ketiga yang diberi label dengan jalan pintas EIS. Ini hanya menghitung bulan. Perhatikan bahwa penghitung yang disesuaikan dilompati setelah angka 12. Lewati ini didorong oleh BENAR di baris 5

Buat Penghitung Kedua yang Melewati Angka Setelah Tahun Selesai

Tangkapan layar menunjukkan kunci di balik proses ini. Yaitu membuat variabel penghitung kedua yang menyisakan baris kosong yang dapat digunakan untuk mengisi angka tahunan di laporan kedua. Untuk melakukan ini, pertama-tama buat saklar BENAR dan SALAH yang menguji apakah angka sebelumnya habis dibagi 12. Pernyataan benar dan salah yang ditunjukkan pada tangkapan layar di bawah ini dapat digunakan untuk melewati angka (catat di kolom R angka 12 melompat ke 14 di kolom S)

Dalam file contoh yang dapat Anda unduh, saya telah membuat laporan dengan subtotal triwulanan dan subtotal tahunan. Prosesnya sangat mirip kecuali Anda menggunakan MOD dengan 3 bukannya 12 dan Anda melewatkan setiap angka keempat. Fungsi MOD triwulanan diilustrasikan pada tangkapan layar di bawah ini

Pada contoh di bawah, BENAR atau SALAH adalah dari analisis triwulanan menggunakan 3 bulan dan ada lompatan counter setiap bulan ketiga

Tangkapan layar di bawah menunjukkan bagaimana Anda dapat membuat rumus yang berbeda tergantung pada apakah sakelar BENAR/SALAH yang dijelaskan di atas adalah BENAR atau SALAH. Jika sakelar BENAR artinya Anda telah sampai di akhir tahun, maka Anda dapat menambahkan 2 alih-alih 1 ke variabel penghitung sebelumnya. Ini akan menyisakan ruang di halaman pelaporan saat Anda menggunakan MATCH dan INDEX. Contoh di bawah menunjukkan bagaimana penghitung disesuaikan untuk melewatkan kolom setelah akhir tahun atau kuartal

Buat Total Tahunan dalam Model Terperinci dan Mulailah dengan 13 atau 5

Anda dapat membuat nomor tahun dengan benar dan Anda juga dapat membuat nomor tahunan menggunakan SUMIF. Untuk melakukan ini, masukkan tahun di bawah tanggal. Dengan tahun yang ditentukan, Anda dapat menggunakan fungsi SUMIF. Di atas tahun, Anda harus mengosongkan nomor yang tidak digunakan. Untuk melakukannya, masukkan angka pertama yang dilewati, lalu tambahkan 13 ke angka tersebut

Gunakan MATCH dan INDEX di Lembar Laporan Tahunan Baru

Tangkapan layar di bawah ini menunjukkan bahwa Anda harus menggunakan MATCH dengan nol di bagian akhir untuk menemukan nomor kolom yang benar dari model detail bulan demi bulan Anda. Ingatlah bahwa ada lompatan dalam penghitungan baris. Pemformatan bersyarat diturunkan dari TRUE/FALSE pada baris 7

Kolom Subtotal Pemformatan Bersyarat

Tangkapan layar di bawah mengilustrasikan cara menggunakan BENAR atau SALAH untuk membuat pemformatan bersyarat. Anda dapat memilih seluruh blok angka dan kemudian menggunakan Aturan Baru dan Gunakan Rumus. Anda kemudian pergi ke tangan kiri BENAR dan tekan tombol F4 beberapa kali hingga baris tetapi kolom tidak diperbaiki

Video yang Mendemonstrasikan Proses Subtotal Tahunan

Video di bawah ini bisa digunakan bersamaan dengan pembahasan di atas. Itu berjalan melalui proses langkah demi langkah yang saya gunakan di atas

Bagaimana Anda mengonversi indeks bulanan menjadi tahunan?

Bagaimana cara meringkas data menurut tahun di Excel?

Bagaimana cara mengonversi data bulanan menjadi data mingguan di Excel?