Cara Cepat Mengelompokkan Data Transaksi Pengeluaran Berdasarkan Kategori di Excel

oleh | 6 Des 2023 | Belajar Excel, Excel Beginner, Excel Best Practices, Tips Excel | 0 Komentar

Pengelolaan keuangan yang efektif memainkan peran kunci dalam kehidupan kita sehari-hari. Salah satu langkah penting dalam mencapai keuangan yang sehat adalah memahami bagaimana uang kita dihabiskan. Untuk mengetahui hal tersebut kita harus mengelompokkan data transaksi pengeluaran yang terjadi. Dan yang lebih penting lagi, kelompokkan data transaksi pengeluaran tersebut berdasarkan kategorinya agar lebih terstruktur dan mudah dianalisis.

Dalam artikel ini, kita akan membahas mengapa penting untuk mengelompokkan data transaksi pengeluaran berdasarkan kategorinya dan bagaimana hal ini dapat membantu kita mengoptimalkan pengeluaran.

Memahami Pola Pengeluaran

Mengelompokkan data transaksi pengeluaran berdasarkan kategori memungkinkan kita untuk melihat pola pengeluaran secara lebih terinci. Kategori umum meliputi makanan, transportasi, hiburan, cicilan, dan lainnya. Dengan memahami di mana uang kita sebagian besar dihabiskan, kita dapat membuat keputusan finansial yang lebih cerdas.

Contoh, jika kita menemukan bahwa sebagian besar uang kita habis untuk makan di luar, kita bisa mempertimbangkan untuk memasak lebih banyak di rumah atau mencari restoran dengan harga lebih terjangkau.

Mengidentifikasi Potensi Penghematan

Dengan mengelompokkan data transaksi pengeluaran berdasarkan kategori, kita dapat dengan mudah mengidentifikasi area di mana kita dapat menghemat uang. Mungkin kita menyadari bahwa kita menghabiskan jumlah yang signifikan untuk langganan layanan yang kurang diperlukan atau impulsive shopping yang tidak direncanakan.

Dengan meninjau kategori-kategori ini, kita dapat membuat keputusan yang lebih baik tentang bagaimana mengalokasikan uang kita dan mengurangi pengeluaran yang tidak perlu.

Perencanaan Anggaran yang Lebih Efektif

Dengan mengetahui berapa banyak uang yang kita alokasikan untuk setiap kategori, kita dapat membuat anggaran yang lebih efektif. Anggaran yang baik adalah alat penting untuk mengendalikan keuangan kita dan mencapai tujuan finansial jangka panjang.

Dengan menentukan batas anggaran untuk setiap kategori, kita dapat mengontrol pengeluaran kita dan memastikan bahwa kita tidak melebihi batas yang telah ditetapkan.

Baca Juga  Cara Mengambil Nama Depan, Nama Tengah dan Nama Belakang di Excel

Meningkatkan Kesadaran Finansial

Mengelompokkan data transaksi pengeluaran berdasarkan kategori juga dapat meningkatkan kesadaran finansial kita. Kita menjadi lebih sadar tentang keputusan pengeluaran kita dan dampaknya terhadap keuangan kita secara keseluruhan.

Dengan meningkatkan kesadaran finansial, kita dapat mengembangkan kebiasaan pengeluaran yang lebih bijak dan membuat keputusan yang lebih terinformasi tentang bagaimana kita menggunakan uang kita.

Memanfaatkan Spreadsheet Excel

Untuk mengakomodir itu semua, untuk memudahkan kita bisa menggunakan Microsoft Excel. Sebagai contoh kita akan membuatnya seperti pada tabel di bawah ini:

Dengan format tabel pengeluaran seperti gambar di atas, mari kita coba otomatisasi menggunakan rumus-rumus yang ada di Excel.

a. Mengelompokkan Data Transaksi Pengeluaran

1. Mengambil Tanggal Transaksi

Untuk mengelompokkan data transaksi pengeluaran berdasarkan posnya, untuk tanggal transaksi di sel G4 kita bisa gunakan formula array sebagai berikut:

=IFERROR(INDEX(B$3:B$21;SMALL(IF($D$3:$D$21=$G$3;ROW($D$3:$D$21)-ROW($D$3)+1);ROWS(G$4:G4)));"")

Ingat ya, ini adalah formula array. Jadi pastikan untuk mengakhiri penulisan rumus dengan menekan Ctrl+Shift dan Enter. Untuk memastikan bahwa penulisan formula array dilakukan dengan benar maka akan ada tanda kurung kurawal yang mengapit rumusnya seperti berikut:

{=IFERROR(INDEX(B$3:B$21;SMALL(IF($D$3:$D$21=$G$3;ROW($D$3:$D$21)-ROW($D$3)+1);ROWS(G$4:G4)));"")} 

Copy dan paste rumus tersebut ke sel di bawahnya. Dan berikut adalah penampakannya:

Penjelasan Rumus

Formula Array ini digunakan untuk mengekstrak nilai dari kolom B (dari sel B$3 hingga B$21) berdasarkan kondisi tertentu yang terdapat di kolom D (dari sel D$3 hingga D$21). Mari kita jelaskan setiap bagian dari formula ini:

  1. IFERROR: Ini adalah fungsi yang digunakan untuk menangani kesalahan. Jika ada kesalahan dalam bagian yang dievaluasi, fungsi ini akan mengembalikan nilai yang ditentukan (dalam hal ini, string kosong “”).
  2. INDEX(B$3:B$21; SMALL(…)): Ini adalah fungsi INDEX yang digunakan untuk mengambil nilai dari rentang B$3:B$21. Bagian dalam SMALL() berfungsi untuk menentukan posisi nilai yang akan diambil berdasarkan urutan kecil.
  3. IF($D$3:$D$21=$G$3; ROW($D$3:$D$21)-ROW($D$3)+1): Ini adalah kondisi yang mengevaluasi apakah nilai dalam kolom D (dari D$3 hingga D$21) sama dengan nilai yang terdapat di sel G$3. Jika benar, fungsi ini mengembalikan array berisi nomor baris relatif dari sel yang memenuhi kondisi.
  4. SMALL(IF(…); ROWS(G$4:G4)): Ini adalah fungsi SMALL yang mengembalikan nilai terkecil dari array yang dihasilkan oleh IF(). ROWS(G$4:G4) digunakan untuk menentukan urutan kecil yang akan diambil. Saat formula ini diisi ke sel lain (misalnya, dari sel G4 ke sel G5), ROWS(G$4:G4) akan menghasilkan nilai 2 (baris ke-2 dalam array SMALL).
  5. {” “}: Kurung kurawal dan tanda kutip ganda di bagian terakhir fungsi digunakan untuk membuat formula ini sebagai formula array. Formula ini dapat ditarik ke bawah sel untuk menghasilkan nilai yang sesuai dengan kriteria yang diberikan.
Baca Juga  Panduan Lengkap Merekap Omset Penjualan dari Tiap Cabang dengan Microsoft Excel

Jadi, secara keseluruhan, formula ini digunakan untuk mengekstrak nilai dari kolom B berdasarkan kondisi tertentu di kolom D, dan hasilnya akan muncul secara berurutan di sel yang sesuai di kolom G.

2. Mengambil Uraian Transaksi

Langkah berikutnya adalah mengambil uraian transaksinya. Untuk keperluan ini kita masih menggunakan formula array. Berikut adalah formula array di sel H4:

=IFERROR(INDEX(C$3:C$21;SMALL(IF($D$3:$D$21=$G$3;ROW($D$3:$D$21)-ROW($D$3)+1);ROWS(H$4:H4)));"")

Sebenarnya struktur rumus dari formula array ini sama saja hanya array untuk INDEX-nya digeser ke range C3:C21 dimana uraian pengeluaran dibuat. Dan setelah di copy paste ke sel dibawahnya maka penampakannya menjadi seperti berikut:

3. Mengambil Nominal Transaksinya

Rumus yang digunakan untuk kolom ini juga masih sama. Menggunakan formula array dan hanya menggeser array untuk INDEX-nya ke range E3:E21.

=IFERROR(INDEX(E$3:E$21;SMALL(IF($D$3:$D$21=$G$3;ROW($D$3:$D$21)-ROW($D$3)+1);ROWS(I$4:I4)));"")

Dan setelah copy dan paste ke sel di bawahnya beriut adalah penampakannya:

selisih pengeluaran

b. Menganalisis Kesesuaian dengan Budgeting

1. Menanalisis Kesesuaian

Untuk mengetahui apakah pengeluaran dari pos tersebut sesuai, lebih kecil atau lebih besar dari budgeting, di sel H12 rumus Excel yang bisa kita masukkan adalah sebagai berikut:

=IFERROR(IF(I8=VLOOKUP(G12;$G$16:$H$21;2;0);"Sesuai dengan anggaran";IF(I8<VLOOKUP(G12;$G$16:$H$21;2;0);"Ada sisa anggaran";"Boncos"));"")

Dan berikut adalah hasilnya:

mengelompokkan data transaksi pengeluaran
Penjelasan Rumus

Formula Excel ini adalah rumus yang menggabungkan beberapa fungsi untuk menentukan status keuangan berdasarkan nilai pada sel I8 dan hasil pencarian menggunakan VLOOKUP di rentang data G$16:$H$21. Mari kita jelaskan setiap bagian dari formula ini:

  1. IFERROR(…, “”): Fungsi IFERROR digunakan untuk menangani kesalahan. Jika ada kesalahan dalam bagian yang dievaluasi, maka fungsi ini akan mengembalikan nilai yang ditentukan dalam tanda kutip ganda kosong (“”).
  2. IF(I8=VLOOKUP(G12;$G$16:$H$21;2;0);”Sesuai dengan anggaran”; IF(I8: Ini adalah fungsi IF bersarang yang memiliki tiga kondisi: Kondisi pertama (I8=VLOOKUP(G12;$G$16:$H$21;2;0)) mengevaluasi apakah nilai di sel I8 sama dengan hasil pencarian menggunakan VLOOKUP dengan kriteria yang sesuai di rentang data G$16:$H$21. Jika benar, maka hasilnya adalah “Sesuai dengan anggaran”. Kondisi kedua (IF(I8<VLOOKUP(G12;$G$16:$H$21;2;0), "Ada sisa anggaran", "Boncos")) mengevaluasi apakah nilai di sel I8 lebih kecil dari hasil pencarian menggunakan VLOOKUP. Jika benar, maka hasilnya adalah “Ada sisa anggaran”. Jika tidak benar, hasilnya adalah “Boncos”. Jika kedua kondisi di atas tidak terpenuhi, maka fungsi IFERROR akan mengembalikan nilai dalam tanda kutip ganda kosong (“”), yang berarti tidak ada kesalahan.
Baca Juga  Cara Merubah Tabel Horizontal Menjadi Vertikal dengan Rumus Excel

Sebagai contoh, jika nilai di sel I8 sama dengan nilai yang ditemukan dengan VLOOKUP di sel G12 dan H12, maka hasilnya akan “Sesuai dengan anggaran”. Jika nilai di I8 lebih kecil, maka hasilnya akan “Ada sisa anggaran”. Jika kedua kondisi tidak terpenuhi atau terdapat kesalahan, maka hasilnya akan kosong (“”).

2. Menanalisis Selisih

Untuk melihat selisihnya apakah pas, lebih atau minus antara pengeluaran dengan budgeting, di sel I12 rumusnya adalah:

=IFERROR(VLOOKUP(G12;$G$16:$H$21;2;0)-I8;"")

Pada dasarnya ini adalah fungsi dasar VLOOKUP yang hasilnya dikurangi dengan nominal di pos budgeting. Dan berikut adalah hasilnya:

hasil akhir mengelompokkan data transaksi pengeluaran di Excel

Penutup

Mengelompokkan data transaksi pengeluaran berdasarkan kategorinya adalah langkah penting dalam mengelola keuangan kita dengan bijak. Dengan memahami pola pengeluaran, mengidentifikasi potensi penghematan, merencanakan anggaran yang lebih efektif, dan meningkatkan kesadaran finansial, kita dapat mencapai keuangan yang lebih sehat dan mencapai tujuan finansial kita.

Jadi, mulailah mengelompokkan data transaksi pengeluaran Anda dan buat perubahan positif dalam pengelolaan keuangan Anda hari ini!

Download

Untuk yang berminat memiliki contoh Excel mengelompokkan data transaksi pengeluaran di atas, file Excel bisa didownload melalui tautan di bawah ini:

0 Komentar

Trackbacks/Pingbacks

  1. Mengulas Keunggulan Layanan Travel Majalengka Jakarta dari Aslan Trans Travel - Aslan Trans Travel - […] Harga tiket yang terjangkau dan bersaing membuat layanan ini dapat diakses oleh berbagai kalangan masyarakat. Travel Majalengka Jakarta PP…

Kirim Komentar

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *

Join Our Newsletter