Bill mengirimkan pertanyaan Excel minggu ini.
Saya memiliki database kejadian di Excel dan bos saya ingin saya memplot grafik frekuensi berdasarkan bulan. Saya membaca trik Anda untuk mengubah tanggal harian menjadi tanggal bulanan dan tentang rumus Excel CSE. Saya telah mencoba setiap kriteria yang dapat saya pikirkan dalam rumus CountIf Excel di bawah ini untuk membuatnya melihat 2 kriteria.
Situasi Anda mungkin dapat diselesaikan dengan mudah dengan tabel pivot (XL95-XL2000) atau diagram pivot (hanya XL2000). Untuk saat ini, mari kita bahas pertanyaan yang Anda ajukan. Di sebelah kiri adalah lembar kerja Anda. Sepertinya Anda ingin memasukkan rumus dalam sel B4406: D4415 untuk menghitung jumlah kejadian tertentu setiap bulan.
Fungsi CountIf adalah bentuk khusus dari rumus array yang sangat bagus bila Anda memiliki satu kriteria. Ini tidak berfungsi dengan baik bila Anda memiliki beberapa kriteria. Rumus sampel berikut akan menghitung jumlah baris dengan Hujan dan jumlah peristiwa di 97 Januari:
=COUNTIF(B2:B4403,"=Rain")
=COUNTIF(A2:A4403,"="&A4406)
Tidak ada cara menggunakan CountIf untuk mendapatkan perpotongan dua kondisi.
Untuk setiap pembaca yang tidak terbiasa dengan cara memasukkan rumus array, saya sangat menyarankan untuk meninjau Gunakan rumus CSE untuk menambah biaya Excel.
Bill tidak menyatakannya dalam pertanyaannya, tetapi saya ingin membuat formula yang dapat dia masukkan hanya sekali di sel B4406 yang dapat dengan mudah disalin ke sel lain dalam jangkauannya. Dengan menggunakan referensi absolut dan campuran dalam rumus, Anda tidak perlu repot memasukkan rumus baru untuk setiap persimpangan.
Berikut ini ulasan singkat tentang rumus absolut, relatif, dan campuran. Biasanya jika Anda memasukkan rumus seperti =SUM(A2:A4403)di D1 lalu menyalin rumus ke E2, rumus Anda di E2 akan berubah menjadi =SUM(B3:C4403). Ini adalah fitur keren dari lembar kerja yang disebut "pengalamatan relatif", tetapi terkadang kita tidak ingin hal itu terjadi. Dalam hal ini, kami ingin setiap rumus merujuk ke rentang A2: B4403. Saat kita menyalin rumus dari sel ke sel, itu harus selalu mengarah ke A2: B4403. Saat memasukkan rumus, tekan F4 sekali setelah memasukkan kisaran, dan rumus Anda akan berubah menjadi=SUM($A$2:$A$4403). Tanda dolar menunjukkan bahwa bagian dari referensi tidak akan berubah saat Anda menyalin rumus. Ini disebut pengalamatan absolut. Dimungkinkan untuk mengunci hanya kolom dengan $ dan memungkinkan baris menjadi relatif. Ini disebut referensi campuran dan akan dimasukkan sebagai =$A4406. Untuk mengunci baris tetapi membiarkan kolom menjadi relatif, gunakan =B$4405. Saat Anda memasukkan rumus, gunakan F4 untuk beralih di antara empat rasa referensi relatif, absolut, dan campuran.
Berikut rumus untuk cell B4406:
=SUM(IF($C$2:$C$4403=$A4406,IF($B$2:$B$4403=B$4405,1,0),0))
Ketikkan rumusnya. Saat Anda menyelesaikan rumus, tahan Ctrl, Shift lalu enter. Anda sekarang dapat menyalin rumus ke C4406: D4406 lalu menyalin ketiga sel tersebut ke setiap baris di tabel hasil Anda.
Rumusnya menggunakan ketiga bentuk referensi campuran dan absolut. Ini bersarang 2 jika pernyataan karena fungsi AND () tampaknya tidak berfungsi dalam rumus array. Untuk penjelasan yang lebih baik tentang apa yang terjadi dengan fungsionalitas array, baca ulang Menggunakan rumus CSE untuk melengkapi Excel yang disebutkan di atas.
