cOUNTIFS

  1. 4 tahun lalu

    Kenapa Fungsi COUNTIFS, jika beda file (tidak satu file/worksheet) hasilnya jadi #value? berbeda jika menggunakan fungsi INDEX.
    bagaimana cara mengatasinya...?

    mohon para suhu bisa share ilmunya...

    Terimakasih.........

    @alnoerma ...

    Sebenarnya, sama saja. Coba perhatikan formula pada sheet TES1 berikut :

    [attachment:5dde41c7a8136]

    Jika file rujukannya berada pada folder yang cukup dalam, bayangkan panjangnya formula yang kita susun hanya untuk rujukan nama folder plus nama file plus range datanya. Jika formula yang kita buat hanya seperti gambar di atas, mungkin tidak terlalu panjang. Tapi bagaimana jika kita menyusun formula yang cukup kompleks dengan rujukan range data eksternal yang digunakan beberapa kali... :)

    Salah satu cara untuk menyingkat formula yang panjang adalah dengan menyingkat rujukan ke range dengan membuat nama range yang mewakili range atau formula tertentu. Perhatikan gambar berikut :

    [attachment:5dde435640308]

    melalui Name Manager, kita bisa mendefinisikan nama range yang akan mewakili range atau formula tertentu. Dalam hal ini, referensi ke range data eksternal pada formula yang digunakan pada sheet TES1 saya definisikan kembali sebagai dbRiwayat.Nama dan dbRiwayat.Negara. Formulanya sama saja khan?! Hanya saja, dengan memanfaatkan nama range khusus tadi, kita bisa menghemat panjang formula, sehingga dapat dibuat seperti berikut :

    [attachment:5dde44d78c104]

    Selain itu, dengan nama range tersebut, kita bisa lebih mudah mengetahui range data aktual dari nama range yang kita buat.

    Untuk membuat nama range, mungkin @alnoerma sudah tahu bagaimana. Kalau belum, coba dipelajari di sini . Atau diskusikan saja kembali jika masih mengalami kesulitan.

    Demikian.

  2. Caton

    26 Nov 2019 Terverifikasi Indonesia + 20.101 Poin

    @alnoerma ...

    Fungsi-fungsi SUMIF, COUNTIF dan COUNTBLANK merupakan fungsi-fungsi yang sering menghasilkan #VALUE! saat digunakan dengan referensi eksternal atau referensi ke Workbook tertutup. Untuk mengaktifkan kembali fungsi-fungsi tersebut, maka Workbook referensinya harus dalam keadaan terbuka. Solusinya antara lain dengan menggunakan fungsi SUMPRODUCT.

    Demikian.

  3. Di sunting 4 tahun lalu oleh alnoerma

    @Caton , jika kasusnya seperti file terlampir.. bagaimana rumusnya?
    terimakasih..

    tetapi berbeda file... (eksternal)

  4. Caton

    26 Nov 2019 Terverifikasi Indonesia + 20.101 Poin

    @alnoerma ...

    Terlampir contohnya dengan fungsi SUMPRODUCT. Semoga sesuai.

    Demikian.

  5. @Caton , terimakasih ilmunya. sangat bermanfaat,...

  6. =SUMPRODUCT((dbRiwayat.Nama=$D6)*(dbRiwayat.Negara=E$5))

    @Caton , saya belum mengerti caranya menggunakan fungsi rumus dbRiwayat.Nama? mohon penjelasannya...

  7. Caton

    27 Nov 2019 Terverifikasi Jawaban Terpilih Indonesia + 20.101 Poin

    @alnoerma ...

    Sebenarnya, sama saja. Coba perhatikan formula pada sheet TES1 berikut :

    al1.png

    Jika file rujukannya berada pada folder yang cukup dalam, bayangkan panjangnya formula yang kita susun hanya untuk rujukan nama folder plus nama file plus range datanya. Jika formula yang kita buat hanya seperti gambar di atas, mungkin tidak terlalu panjang. Tapi bagaimana jika kita menyusun formula yang cukup kompleks dengan rujukan range data eksternal yang digunakan beberapa kali... :)

    Salah satu cara untuk menyingkat formula yang panjang adalah dengan menyingkat rujukan ke range dengan membuat nama range yang mewakili range atau formula tertentu. Perhatikan gambar berikut :

    al3.png

    melalui Name Manager, kita bisa mendefinisikan nama range yang akan mewakili range atau formula tertentu. Dalam hal ini, referensi ke range data eksternal pada formula yang digunakan pada sheet TES1 saya definisikan kembali sebagai dbRiwayat.Nama dan dbRiwayat.Negara. Formulanya sama saja khan?! Hanya saja, dengan memanfaatkan nama range khusus tadi, kita bisa menghemat panjang formula, sehingga dapat dibuat seperti berikut :

    al2.png

    Selain itu, dengan nama range tersebut, kita bisa lebih mudah mengetahui range data aktual dari nama range yang kita buat.

    Untuk membuat nama range, mungkin @alnoerma sudah tahu bagaimana. Kalau belum, coba dipelajari di sini . Atau diskusikan saja kembali jika masih mengalami kesulitan.

    Demikian.

  8. Siapp @Caton ,,, ilmu yang sangat bermanfaat, sudah berbagi ilmu bagi saya yang sangat kurang faham.....
    terimakasih...

  9. Caton

    28 Nov 2019 Terverifikasi Indonesia + 20.101 Poin
    Di sunting 4 tahun lalu oleh Caton

    @alnoerma ...

    Sama-sama... Sedikit tambahan untuk memahami formulanya (kemarin belum sempat saya teruskan). Untuk formulanya sendiri, cukup mudah dipahami. Formula utamanya :

    =SUMPRODUCT((dbRiwayat.Nama=$D6)*(dbRiwayat.Negara=E$5))

    dimana, nama range dbRiwayat.Nama mewakili range D7:D32 dan nama range dbRiwayat.Negara mewakili range H7:H32 dari Worksheet DATA RIWAYAT SEMINAR dari file Data Riwayat Seminar.xlsx ...

    Pada formula di atas, ada 2 bagian yang akan dikalkulasi, yakni :

    dbRiwayat.Nama=$D6

    dan ...

    dbRiwayat.Negara=E$5

    Untuk bagian pertama yakni dbRiwayat.Nama=$D6, data pada range eksternal D7:D32 akan dibandingkan dengan nilai pada sel D6 yakni JOSEPHINE HILDA. Formula ini akan menghasilkan nilai TRUE atau FALSE seperti berikut :

    {FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}

    Demikian juga untuk bagian kedua yakni dbRiwayat.Negara=E$5, data pada range eksternal H7:H32 akan dibandingkan dengan nilai pada sel E5 yakni Spain. Formula ini akan menghasilkan nilai TRUE atau FALSE seperti berikut :

    {FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE}

    Kedua Array hasil dari kedua bagian formula di atas kemudian dikalkulasikan kembali dengan memanfaatkan operator AND yakni dengan simbol * (bintang/asterik). Dalam hal ini, hasil yang ingin dicapai adalah data yang memenuhi kriteria NAMA = JOSEPHINE HILDA DAN NEGARA = SPAIN. Hasil akhir kalkulasi dari kedua bagian tersebut akan menjadi :

    {0;0;0;1;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0}

    Oleh karena proses kalkulasi tersebut di atas melibatkan data berupa Array, maka kita manfaatkan fungsi SUMPRODUCT (atau bisa juga dengan fungsi SUM dengan memanfaatkan Array Formula) untuk menjumlahkan nilai pada Array hasil kalkulasi kedua bagian formula tersebut menjadi :

    =SUMPRODUCT({0;0;0;1;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0})

    Dan hasil akhir dari fungsi SUMPRODUCT di atas adalah 3. Untuk formula pada baris selanjutnya (E7 dan seterusnya), sama saja dengan yang saya jelaskan di atas. Semoga bisa dipahami.

    Demikian.

  10. apakah penamaan array tersebut hanya untuk fungsi SUMPRODUCT atau bisa diaplikasikan ke fungsi rumus yang lain nya?

  11. Caton

    28 Nov 2019 Terverifikasi Indonesia + 20.101 Poin

    @alnoerma ...

    ... apakah penamaan array tersebut hanya untuk fungsi SUMPRODUCT atau bisa diaplikasikan ke fungsi rumus yang lain nya ...

    Nama Range (Named Range) itu bisa diaplikasikan untuk mengacu atau mewakili sebuah sel, range atau formula. Named Range pada dasarnya akan mengembalikan nilai berupa Array. Sekedar contoh saja, coba proses berikut :

    [1]. Masukkan sembarang nilai (angka) pada sel A1.
    [2]. Kemudian, pindahkan kursor ke sel B1 lalu buka Name Manager (shortcut : CONTROL + F3).
    [3]. Pada jendela Name Manager, klik tombol New dan buat sembarang nama misalkan Hitung.Nilai.
    [4]. Pada kolom Refers To... ketik formula :

    =IF(AND(LEN($A1)>0;ISNUMBER($A1));$A1*2;"")

    [5]. Tutup jendela Name Manager.
    [6]. Pada sel B1, masukkan formula :

    =Hitung.Nilai

    [7]. Periksa apakah hasilnya tepat. Coba kembali nama range yang sudah dibuat dengan mengubah nilai pada sel A1.

    Demikian.

  12. Tepat..., semoga bermanfaat khususnya buat saya...
    terimakasih...
    saya harap kita bisa lanjutkan diskusi dengan kasus yang lain....

 

atau Mendaftar untuk ikut berdiskusi!