Mempelajari Excel itu tentang menambahkan lebih banyak formula dan function ke dalam perlengkapan tool-mu. Dengan kombinasi yang cukup, kamu secara praktis dapat melakukan apapun dengan spreadsheet.

Di dalam tutorial ini, kamu akan mempelajari bagaimana menggunakan tiga formula Excel yang hebat: SUMIF, COUNTIF, dan AVERAGEIF.

Di dalam screenshot di atas, kamu dapat melihat bahwa kita memiliki daftar transaksi pada sisi kiri. Jika saya ingin mengawasi pengeluaran saya, saya dapat menggunakan ketiga formula tersebut untuk mengontrolnya.

Pada sisi kanan, kotak Dining Out Expense menggunakan ketiga formula untuk membantu saya melacak pengeluaran.

• COUNTIF - Digunakan untuk menghitung jumlah kali "Restaurant" muncul di dalam daftar.
• SUMIF - Menghitung total pengeluaran untuk item berlabel "Restaurant".
  
• AVERAGEIF - Menghitung rata-rata semua pengeluaran "Restaurant" dalam daftar.
  

Lebih umum lagi, berikut apa yang dilakukan tiap formula itu untukmu, dan bagaimana kamu memanfaatkannya:

• SUMIF - Tambahkan nilai jika kondisinya dipenuhi, seperti menambahkan semua pembelian dari satu kategori.
• COUNTIF - Menghitung jumlah item yang memenuhi kondisi, seperti menghitung jumlah kali sebuah nama muncul di dalam daftar.
• AVERAGEIF - Secara kondisional menghitung rata-rata; misalnya kamu dapat menghitung rata-rata nilai ujian.

Formula tersebut mengijinkanmu untuk menambahkan logika ke dalam spreadsheet. Mari kita lihat bagaimana menggunakan tiap formula.

COUNTIF, SUMIF, dan AVERAGEIF dalam Excel (Tutorial Video Singkat)

Screencast adalah salah satu cara terbaik untuk menonton dan mempelajari skill yang baru, termasuk memulai dengan tiga formula kunci ini dalam Microsoft Excel. Periksa video di bawah untuk menonton bagaimana saya berkerja dalam Excel. Pastikan dan download workbook contoh gratis untuk digunakan dalam tutorial ini.

Jika kamu lebih suka belajar dengan instruksi tertulis tahap demi tahap, teruskan membaca. Saya akan membagikan tips bagaimana menggunakan formula tersebut dan ide mengapa mereka berguna.

Bagaimana Menggunakan SUMIF Dalam Excel

Gunakan tab berjudul SUMIF di dalam workbook contoh gratis untuk section tutorial ini.

Pikirkan SUMIF sebagai cara untuk menambahkan nilai yang memenuhi persyaratan. Kita dapat menambahkan daftar nilai yang berasal dari kategori tertentu, atau semua nilai yang lebih besar atau kurang dari jumlah tertentu.

Berikut bagaimana formula SUMIF berkerja:

=SUMIF(Cell untuk dicek, apa yang dicek, Jumlah cell yang memenuhi persyaratan)

Mari kita kembali ke contoh pengeluaran restoran saya untuk mempelajari formula SUMIF. Di bawah ini, saya menunjukkan daftar transaksi saya selama bulan itu.

Saya ingin mengetahui dua hal:

1. Total yang saya habiskan di restoran bulan itu.
2. Semua pembelian yang lebih besar dari $50 pada bulan itu, dari kategori apapun.

Alih-alih menambahkan data secara manual, kita dapat menuliskan dua formula SUMIF untuk otomatisasi prosesnya. Saya akan menempatkan hasilnya di dalam kotak Restaurant Expense di sisi kanan. Mari kita lihat bagaimana caranya.

Total Pengeluaran Restoran

Untuk menemukan pengeluaran total restoran saya, saya akan menambahkan semua nilai dengan jenis pengeluaran "Restaurant", yaitu dalam kolom B.

Berikut formula yang akan saya gunakan untuk contoh ini:

=SUMIF(B2:B17,"Restaurant",C2:C17)

Perhatikan bahwa tiap section dipisahkan oleh koma. Formula ini melakukan tiga hal:

• Cari apa yang ada di cell B2 hingga B17 untuk kategori pengeluaran
• Gunakan "Restaurant" untuk kriteria yang akan dijumlahkan
• Gunakan nilai dalam cell C2 hingga C17 untuk menjumlahkan total angka

Ketika saya menekan enter, Excel menghitung total pengeluaran restoran saya. Dengan menggunakan SUMIF, mudah untuk membuat statistik cepat ini untuk membantumu memonitor data jenis tertentu.

Pembelian Lebih Dari $50

Kita telah memeriksa kategori spesifik, namun sekarang mari kita jumlahkan semua nilai yang lebih besar dari jumlah dari kategori apapun. Dalam kasus ini, saya ingin menemukan semua pembayaran yang lebih dari $50.

Mari tuliskan sebuah formula sederhana untuk menemukan jumlah semua pembayaran yang lebih besar dari $50:

=SUMIF(C2:C17,">50")

Di dalam kasus ini, formulanya sedikit sederhana: karena kita akan menjumlahkan nilai yang sama yang kita coba (C2 hingga C17), kita hanya perlu menentukan cell tersebut secara spesifik. Kemudian, kita akan menambahkan sebuah koma dan ">50" untuk hanya menjumlahkan nilai yang lebih besar dari $50.

Contoh ini menggunakan tanda lebih besar dari, namun untuk poin bonus: coba menjumlahkan semua pembelian kecil, seperti semua pembelian sebesar $20 atau lebih kecil.

Bagaimana Menggunakan COUNTIF Dalam Excel

Gunakan tab berjudul SUMIF di dalam workbook contoh gratis untuk section tutorial ini.

Sementara SUMIF digunakan untuk menambahkan nilai yang memenuhi kondisi tertentu, COUNTIF akan menghitung berapa kali sesuatu muncul dalam sebuah set data.

Berikut format umum untuk formula COUNTIF:

=COUNTIF(cell untuk dihitung, kriteria untuk dihitung)

Dengan menggunakan set data yang sama, mari kita hitung dua bagian informasi utama:

• Jumlah pembelian pakaian dalam sebulan
• Jumlah pembayaran sebesar $100 atau lebih

Jumlah Pembelian Pakaian

COUNTIF pertama saya akan mencari jenis pengeluaran dan menghitung angka pembelian "Clothing" dalam transaksi saya.

Formula akhir akan menjadi:

=COUNTIF(B2:B17,"Clothing")

Formula itu mencari kolom "Expense Type", menghitung berapa kali itu melihat clothing, dan menghitungnya. Hasilnya adalah 2.

Jumlah Pembayaran $100+

Sekarang, mari kita hitung angka transaksi sebesar $100 atau lebih besar dalam daftar saya.

Berikut formula yang akan saya gunakan:

=COUNTIF(C2:C17,">100")

Itu adalah formula dua bagian yang sederhana: cukup arahkan Excel ke daftar data untuk dihitung, dan aturan menghitungnya. Dalam kasus ini, kita memeriksa cell C2 ke C17, untuk semua nilai lebih besar dari $100.

Bagaimana Menggunakan AVERAGEIF Dalam Excel

Gunakan tab berjudul AVERAGEIF di dalam workbook contoh gratis untuk section tutorial ini.

Terakhir, mari kita lihat bagaimana menggunakan formula AVERAGEIF. Sekarang ini, tidak mengherankan bahwa AVERAGEIF dapat digunakan untuk menghitung rata-rata spesifik, berdasarkan pada kondisi yang akan kita berikan pada Excel.

Format formula untuk AVERAGEIF adalah:

=AVERAGEIF(Cell untuk diperiksa, apa yang diperiksa, Nilai rata-rata cell yang memenuhi persyaratan)

Format formula AVERAGEIF paling mirip dengan formula SUMIF.

Mari gunakan formula AVERAGEIF untuk menghitung dua status utama tentang pengeluaran saya:

1. Rata-rata pengeluaran saya.
2. Rata-rata semua pengeluaran yang kurang dari $25.

Rata-Rata Pengeluaran Restoran

Untuk menghitung rata-rata pengeluaran restoran saya, saya akan menuliskan formula AVERAGEIF untuk mencari rata-rata semua pengeluaran berdasarkan kategori.

=AVERAGEIF(B2:B17,"Restaurant",C2:C17)

Ada tiga bagian utama dari formula ini, masing-masing dipisahkan oleh koma:

• B2:B17 mengkhususkan cell untuk diperiksa kondisinya. Karena jenis pengeluaran dikhususkan dalam kolom ini.
• "Restaurant" memberikan formula sesuatu untuk dicari.
• Terakhir, C2:C17 adalah cell untuk mencari rata-rata dari perhitungan kita.

Pada akhirnya, Excel menghitung rata-rata pengeluaran perjalanan restoran saya. Saya memberikan itu kepada formula.

Kamu dapat juga mencoba formula ini dengan mengganti "Restaurant" dengan kategori lain, seperti "Clothing."

Rata-rata Pengeluaran Kurang Dari $25

Jika saya memantau pembelian yang lebih kecil dan ingin mengetahui nilai rata-rata, saya dapat menuliskan AVERAGEIF untuk semua pembelian kurang dari angka tersebut.

Berikut formula yang akan saya gunakan untuk melakukan itu:

=AVERAGEIF(C2:C17,"<25")

Formula sederhana ini hanya memeriksa nilai di dalam kolom C, dan menghitung rata-rata semua nilai yang lebih besar dari $25.

Rekap dan Terus Belajar

Di dalam tutorial ini, kamu mempelajari bagaimana menggunakan formula matematis kondisional untuk mengulas datamu. Apakah kamu menjumlahkan, menghitung, atau mencari rata-rata data, function ini adalah skill Excel lanjutan yang dapat kamu manfaatkan.

Untuk semua formula "IF" dalam tutorial ini, pelajaran utamanya adalah bahwa kamu dapat menerapkan kondisi pada perhitungan dalam Excel.

Pembelajaran menurunkan pembelajaran. Berikut adalah tiga tutorial Excel lainnya untuk tetap bertumbuh:

• Sebagai tambahan pada COUNTIF, SUMIF, dan AVERAGEIF, ada juga pernyataan "IF" umum yang dapat digunakan untuk kondisi lainnya. Periksa tutorial kami tentang Bagaimana Menggunakan Pernyataan IF Sederhana.
• Pelajari bagaimana menggunakan Tanggal Dan Waktu Dalam Excel untuk digabungkan dengan formula ini untuk berkerja dengan nilai berdasarkan tanggal.
• Function VLOOKUP Excel dapat digunakan untuk mencocokkan nilai dalam daftar berlapis. Pelajari tentang Bagaimana Menggunakan Function VLOOKUP Excel di dalam tutorial ini.

Dapatkah kamu memikirkan cara lainnya untuk menggunakan formula ini untuk menambahkan logika dan kondisi pada spreadsheet-mu? Silahkan beritahu saya dalam komentar di bawah.