Rumus IF dgn Array

  1. 3 minggu lalu

    Dear Pak @Caton ,

    Ada yg ingin sy tanyakan lagi,
    Pada cell F8 (sheet tgl_1), dalam hal ini dgn contoh kasus employee dgn NIP : 540001,
    apabila tanggal pd cell E5 (sheet tgl_1) = deret data "G2:K2" (sheet database), maka hasilnya "blank",
    dan apabila cell E5 (sheet tgl_1) <> deret data "G2:K2" (sheet database), maka hasilnya "v".

    Mohon dibantu untuk rumus excelnya ?
    Terima kasih sebelumnya

  2. Caton

    Sep 3 Terverifikasi Indonesia + 11.463 Poin

    @suarto priyandono ... Pada cell F8 (sheet tgl_1), dalam hal ini dgn contoh kasus employee dgn NIP : 540001, apabila tanggal pd cell E5 (sheet tgl_1) = deret data "G2:K2" (sheet database), maka hasilnya "blank", dan apabila cell E5 (sheet tgl_1) <> deret data "G2:K2" (sheet database), maka hasilnya "v" ...

    Pak Suarto, apakah yang dimaksud dengan sama dengan atau tidak sama dengan deret data range G2:K2 itu, adalah nilai (tanggal) pada sel E5 itu ada pada range G2:K2 sheet DATABASE? Jika benar demikian, maka gunakan saja fungsi COUNTIF. Contohnya:

    =IF(COUNTIF(database!$G$2:$K$2, $E$5), "" , "v")

    Selain itu, fungsi MATCH juga bisa digunakan. Coba lihat contohnya pada file terlampir. Jika tidak sesuai yang dimaksud, silahkan diskusikan kembali ...

    Demikian.

  3. Dear Pak @Caton

    Iya benar Pak maksud sy itu nilai tanggal.
    Sy akan coba untuk rumus yg Bapak berikan.
    Terima kasih

  4. Dear Pak @Caton

    Rumus yg Bapak berikan sdh sesuai.

    Tp rasanya gak efektif kl sy hrs buat 30 sheet (tgl_1, tgl_2, tgl_3.......tgl_30), dan itu hanya untuk 1 employee,
    bgmn kl ada 100 employee.

    Apakah Pak @Caton ada solusi ?

    Terima kasih sebelumnya

  5. Caton

    Sep 3 Terverifikasi Indonesia + 11.463 Poin
    Di sunting 3 minggu lalu oleh Caton

    @suarto priyandono ... rasanya gak efektif kl sy hrs buat 30 sheet (tgl_1, tgl_2, tgl_3.......tgl_30), dan itu hanya untuk 1 employee, bgmn kl ada 100 employee ...

    Memang tidak efektif pak ... :D

    Karena itu saya berikan alternatif contoh menggunakan formula dinamis. Coba saja ganti NIP pada sel D1 dan PERIODE akhir pada sel E5, lalu lihat perubahan pada sel K8 atau L8. Untuk formula yang digunakan, contohnya pada sel K8:

    =IF(COUNTIF(OFFSET(database!$G$1:$K$1, MATCH($D$1, database!$B$2:$B$4, 0), 0), $E$5), "", "v")

    Pada script di atas, formula:

    ... MATCH($D$1, database!$B$2:$B$4, 0) ...

    akan menjadi penentu baris data yang sesuai dengan sel D1 (NIP). Jika ada yang sesuai, range data akan bergeser dari range asal (G1:K1) sebanyak indeks baris yang ditentukan oleh fungsi MATCH tersebut. Apabila kolom tanggal pada tabel data sheet DATABASE juga bersifat dinamis (misalkan terus bertambah), maka bisa diatur melalui formula tertentu.

    Dengan demikian, sheet laporan tersebut cukup dibuat 1 sheet saja. Perubahan data dapat dilihat hanya dengan mengganti setiap entiti tertentu (misalkan NIP atau TANGGAL). Saya kira, pak Suarto dapat mencoba memodifikasi terlebih dahulu formula yang sudah ada. Nanti jika ada kesulitan bisa didiskusikan kembali.

    Demikian.

  6. Dear Pak @Caton,

    Terdapat perubahan sedikit, yaitu tambahan field "gender" (sheet database) adalah sbb :

    Pada cell F10 (sheet tgl_1), dalam hal ini dgn contoh kasus employee dgn NIP : 540001,
    Jika :
    a. "nilai_tanggal" pd cell E6 (sheet tgl_1) = "nilai_tanggal" deret data "H2:L2" (sheet database), maka hasilnya "blank",
    b. "nilai_tanggal" cell E6 (sheet tgl_1) <> deret data "H2:L2" (sheet database) AND gender (sheet database)=Laki Laki, maka hasilnya "v".
    c. "nilai_tanggal" cell E6 (sheet tgl_1) <> deret data "H2:L2" (sheet database) AND gender (sheet database)=Wanita, maka hasilnya "blank".

    Mohon dibantu kembali untuk rumus excelnya ?
    Terima kasih sebelumnya

  7. Caton

    Sep 3 Terverifikasi Indonesia + 11.463 Poin

    @suarto priyandono ... Terdapat perubahan sedikit ...

    Pada sel F10, masukkan dahulu formula yang sudah saya sampaikan di atas dengan perubahan range TANGGAL pada sheet DATABASE menjadi:

    =IF(COUNTIF(OFFSET(database!$H$1:$L$1, MATCH($D$1, database!$B$2:$B$4, 0), 0), $E$5), "", "v")

    Sampai di sini untuk menjawab pertanyaan poin ke-1. Untuk poin ke-2 dan ke-3, hanya akan diproses jika KONDISI dari formula tersebut adalah FALSE, sehingga blok yang akan dieksekusi adalah blok:

    =IF( ... , "v")

    Dalam hal ini, tambahkan saja formula kondisional yang akan memeriksa apakah GENDER dari data tersebut LAKI-LAKI atau WANITA. Oleh karena fungsi OFFSET pada formula awal yakni:

    ... OFFSET(database!$H$1:$L$1, MATCH($D$1, database!$B$2:$B$4;0), 0) ...

    digunakan untuk mengambil data secara dinamis, maka formula tersebut dapat dimanfaatkan kembali, namun pada range berbeda. Hanya saja, jika pada formula awal digunakan untuk mengambil data berupa range data (beberapa sel), kali ini yang diambil cukup 1 sel data saja. Sehingga formula tersebut jika dimodifikasi akan menjadi ± seperti berikut:

    ... OFFSET(database!$D$1, MATCH($D$1, database!$B$2:$B$4;0), 0) ...

    Hasil dari formula tersebut akan berupa nilai teks LAKI-LAKI atau nilai teks WANITA (bergantung kepada baris data yang ditemukan oleh fungsi MATCH tersebut. Nah, tinggal membandingkan nilai tersebut dengan nilai komprasi yang diinginkan. Jadi untuk poin ke-2 formulanya:

    ... OFFSET(database!$D$1, MATCH($D$1, database!$B$2:$B$4;0), 0) = "LAKI-LAKI"...

    Formula tersebut, jika dimasukkan ke dalam fungsi IF akan menghasilkan TRUE jika nilai pada kolom GENDER (kolom D) sheet DATABASE = LAKI-LAKI. Sehingga, hasil akhirnya jika TRUE adalah "V". Sedangkan untuk pertanyaan poin ke-3, masukkan ke dalam parameter FALSE dari fungsi IF tersebut (artinya dibuat BLANK saja, karena jika nilai pada kolom GENDER sheet DATABASE <> LAKI-LAKI, akan menghasilkan nilai FALSE). Formula seelengkapnya:

    = IF(OFFSET(database!$D$1, MATCH($D$1, database!$B$2:$B$4;0), 0) = "LAKI-LAKI", "v", "") ..

    Langkah terakhir adalah menggabungkan kedua formula menjadi satu menjadi:

    =IF(COUNTIF(OFFSET(database!$H$1:$L$1, MATCH($D$1, database!$B$2:$B$4, 0), 0), $E$5), "", 
     IF(OFFSET(database!$D$1, MATCH($D$1, database!$B$2:$B$4;0), 0) = "LAKI-LAKI", "v", ""))

    Untuk sel F15, dibuat formula serupa dengan perubahan pada nilai yang dikomparasi (LAKI-LAKI diubah menjadi WANITA). Silahkan dicoba dahulu tanpa membuka file terlampir.

    Demikian.

  8. Dear Pak @Caton

    Sepertinya belum sesuai atau saya yang kurang tepat me-narasikannya.
    Untuk nilai di cell F12,F14,F15,F17 yg seharusnya tampil, saya simulasikan
    pada "sheet simulasi" file terlampir.

  9. Caton

    Sep 4 Terverifikasi Indonesia + 11.463 Poin

    @suarto priyandono ... Sepertinya belum sesuai atau saya yang kurang tepat menarasikannya ...

    Pak Suarto, formula yang saya berikan di atas yakni:

    =IF(COUNTIF(OFFSET(database!$H$1:$L$1, MATCH($D$1, database!$B$2:$B$4, 0), 0), $E$5), "", 
     IF(OFFSET(database!$D$1, MATCH($D$1, database!$B$2:$B$4;0), 0) = "LAKI-LAKI", "v", ""))

    adalah untuk sel F10 dan sel F12. Untuk sel F15 dan sel F17, coba baca instruksi saya pada kalimat saya yang terakhir:

    Untuk sel F15, dibuat formula serupa dengan perubahan pada nilai yang dikomparasi (LAKI-LAKI diubah menjadi WANITA). Silahkan dicoba dahulu tanpa membuka file terlampir.

    Jadi, nilai komparasinya diubah, dari LAKI-LAKI menjadi WANITA. Selengkapnya:

    =IF(COUNTIF(OFFSET(database!$H$1:$L$1, MATCH($D$1, database!$B$2:$B$4, 0), 0), $E$5), "", 
     IF(OFFSET(database!$D$1, MATCH($D$1, database!$B$2:$B$4;0), 0) = "WANITA", "v", ""))

    Pak suarto sudah mencoba file yang saya lampirkan?

  10. 2 minggu lalu

    Dear Pak @Caton

    File yang Pak @Caton lampirkan kemarin sudah saya coba, namun saya melakukan kesalahan krn blm merubah nilai komparasi.

    Formula dari Pak @Caton saya coba terapkan pada tabel lain (file terlampir), namun masih terjadi error (sheet tgl_1, kolom F, cell warna kuning).
    Error terjadi apabila nilai tanggal (sheet tgl_1, cell H7) saya rubah dgn nilai tanggal selain nilai "tanggal OFF" (sheet database).

    Mohon bantuannya kembali Pak @Caton
    Terima kasih

  11. Caton

    Sep 5 Terverifikasi Indonesia + 11.463 Poin
    Di sunting 2 minggu lalu oleh Caton

    @suarto priyandono ... Formula dari Pak @Caton saya coba terapkan pada tabel lain (file terlampir), namun masih terjadi error (sheet tgl_1, kolom F, cell warna kuning). Error terjadi apabila nilai tanggal (sheet tgl_1, cell H7) saya rubah dgn nilai tanggal selain nilai "tanggal OFF" (sheet database) ...

    Pak Suarto,

    Formulanya tidak salah, yang salah adalah referensi sel blok FALSE dari fungsi IF yang pertama. Pada blok tersebut, nilai yang akan dicari tidak akan ditemukan pada kolom NIP pada sheet DATABASE. Coba dipelajari kembali formulanya:

    = IF(COUNTIF(OFFSET(database!$G$1:$K$1, MATCH($E$9. database!$A$2:$A$186, 0), 0), $H$7), "",
      IF((OFFSET(database!$C$1, MATCH($C$1, database!$A$2:$A$186, 0), 0)="WANITA"), "V", ""))

    Pada baris kedua dari formula tersebut, fungsi MATCH merujuk ke sel C1. Apakah benar rujukannya ke sel C1? Apakah sel C1 ada isinya? Bukankah seharusnya fungsi MATCH merujuk kepada NIP dari data data yang dicari (sel E9). Sehingga formula tersebut seharusnya:

    = IF(COUNTIF(OFFSET(database!$G$1:$K$1, MATCH($E$9. database!$A$2:$A$186, 0), 0), $H$7), "",
      IF((OFFSET(database!$C$1, MATCH($E$9, database!$A$2:$A$186, 0), 0)="WANITA"), "V", ""))

    Alternatif formulanya:

    = IF(COUNTIF(OFFSET(database!$G$1:$K$1, MATCH($E$9, database!$A$2:$A$186, 0), 0), $H$7), "",
      IF(INDEX(database!$C$2:$C$186, MATCH($E$9, database!$A$2:$A$186, 0))="WANITA", "V", ""))

    Demikian ... :)

  12. Dear Pak @Caton

    Sdh sesuai Pak..

    Terlampir format rekap dari tabel saya diatas.
    Bagaimana cara menghilangkan error nilai penjumlahan (cell berwarna pink).

    Terima kasih

  13. Caton

    Sep 7 Terverifikasi Indonesia + 11.463 Poin

    @suarto priyandono ... Bagaimana cara menghilangkan error nilai penjumlahan (cell berwarna pink) ...

    Pak Suarto ... Paling mudah, ya gunakan fungsi IFERROR seperti berikut:

    =IFERROR(COUNTIF(B27:B27, "YA")/(COUNTA(B27:B27)-COUNTBLANK(B27:B27)), "")

    atau dengan cara lama:

    =IF(ISERROR(COUNTIF(B27:B27, "YA")/(COUNTA(B27:B27)-COUNTBLANK(B27:B27))), "",
     COUNTIF(B27:B27, "YA")/(COUNTA(B27:B27)-COUNTBLANK(B27:B27)))

    Demikian.

 

atau Mendaftar untuk ikut berdiskusi!