Bil dihantar dalam soalan Excel minggu ini.
Saya mempunyai pangkalan data peristiwa di Excel dan bos saya mahu saya merancang carta frekuensi mengikut bulan. Saya membaca helah anda untuk menukar tarikh harian menjadi tarikh bulanan dan mengenai formula Excel CSE. Saya telah mencuba setiap kriteria yang boleh saya fikirkan dalam formula Excel CountIf di bawah untuk melihatnya pada 2 kriteria.
Situasi anda mungkin dapat diselesaikan dengan mudah dengan jadual pangsi (XL95-XL2000) atau carta pangsi (hanya XL2000). Buat masa ini, mari kita jawab soalan yang anda ajukan. Di sebelah kiri adalah lembaran kerja anda. Nampaknya anda ingin memasukkan formula dalam sel B4406: D4415 untuk mengira jumlah peristiwa tertentu setiap bulan.
Fungsi CountIf adalah bentuk khusus dari formula array yang sangat bagus apabila anda mempunyai satu kriteria. Ia tidak berfungsi dengan baik apabila anda mempunyai pelbagai kriteria. Rumus contoh berikut akan mengira bilangan baris dengan Hujan dan jumlah peristiwa pada Januari 97:
=COUNTIF(B2:B4403,"=Rain")
=COUNTIF(A2:A4403,"="&A4406)
Tidak ada cara menggunakan CountIf untuk mendapatkan persilangan dua keadaan.
Bagi mana-mana pembaca yang tidak biasa dengan cara memasukkan formula larik, saya sangat mengesyorkan mengkaji semula Gunakan formula CSE untuk mengecas Excel.
Bill tidak menyatakannya dalam pertanyaannya, tetapi saya ingin membina formula yang dapat dimasukkannya sekali dalam sel B4406 yang dapat dengan mudah disalin ke sel lain dalam julatnya. Dengan menggunakan rujukan mutlak dan campuran dalam formula, anda dapat menyimpan kerumitan memasukkan formula baru untuk setiap persimpangan.
Berikut adalah tinjauan ringkas mengenai formula mutlak, relatif, dan campuran. Biasanya jika anda memasukkan formula seperti =SUM(A2:A4403)di D1 dan kemudian menyalin formula ke E2, formula anda di E2 akan berubah menjadi =SUM(B3:C4403). Ini adalah ciri lembaran kerja yang menarik yang disebut "menangani relatif", tetapi kadang-kadang kita tidak mahu perkara itu terjadi. Dalam kes ini, kami ingin setiap formula merujuk kepada julat A2: B4403. Semasa kita menyalin formula dari sel ke sel, ia harus selalu menunjuk ke A2: B4403. Semasa memasukkan formula, tekan F4 sekali setelah memasuki julat, dan formula anda akan berubah menjadi=SUM($A$2:$A$4403). Tanda dolar menunjukkan bahawa bahagian rujukan itu tidak akan berubah semasa anda menyalin formula. Ini dipanggil pengucapan mutlak. Hanya mungkin untuk mengunci lajur dengan $ dan membiarkan baris menjadi relatif. Ini disebut rujukan campuran dan akan dimasukkan sebagai =$A4406. Untuk mengunci baris tetapi membiarkan lajur menjadi relatif, gunakan =B$4405. Semasa anda memasukkan formula, gunakan F4 untuk beralih di antara empat rasa rujukan relatif, mutlak dan campuran.
Berikut adalah formula untuk sel B4406:
=SUM(IF($C$2:$C$4403=$A4406,IF($B$2:$B$4403=B$4405,1,0),0))
Taipkan formula. Apabila anda menyelesaikan formula, tahan Ctrl, Shift dan kemudian masukkan. Anda sekarang boleh menyalin formula ke C4406: D4406 dan kemudian menyalin ketiga sel ke setiap baris dalam jadual hasil anda.
Rumus menggunakan ketiga-tiga bentuk rujukan campuran dan mutlak. Ia bersarang 2 jika pernyataan kerana fungsi AND () nampaknya tidak berfungsi dalam formula larik. Untuk penjelasan yang lebih baik mengenai apa yang berlaku dengan fungsi array, baca semula Gunakan formula CSE untuk mengecas Excel yang disebutkan di atas.
