Hasil dari >1 variabel

  1. 2 bulan yang lalu

    Selamat pagi master sifu semua,

    Saya ada kasus seperti terlampir, mohon berkenan membantu.

    Terimakasih

    Mas @Argha24 ...

    Saya coba jelaskan sedikit mengenai masalah formulasi pada file yang mas lampirkan terakhir di atas sebagai berikut:

    [A] Formula Pada Sel C5

    =SUMPRODUCT((YEAR(TGL)=$C$3)*(MONTH(TGL)=$B5)*(INDEX(LOC;0;$C$2)))

    — Fungsi MONTH pada dasarnya akan menghasilkan nilai berupa angka (1 sampai dengan 12) sesuai dengan indeks bulan dari tanggal yang menjadi parameternya. Pada formula di atas, notasi MONTH(TGL)=$B5 akan selalu menghasilkan nilai FALSE oleh karena tipe nilai pada sel B5 adalah teks (sel B5 berisi teks JANUARY), bukan angka. Seharusnya, pada sel B5 berisi nilai angka (misalkan 42736 untuk tanggal 01/01/2017). Perlu diperhatikan, meskipun sel B5 sudah berisi nilai angka (tanggal), notasi MONTH(TGL)=$B5 akan tetap menghasilkan nilai FALSE oleh karena seharusnya pada notasi tersebut, nilai yang dibaca dari sel B5 adalah indeks bulannya, bukan tanggal secara keseluruhan. Sehingga, notasi yang tepat seharusnya adalah MONTH(TGL)=MONTH($B5).

    — Fungsi INDEX merujuk ke range data dan indeks kolom yang salah. Seharusnya, data yang akan diambil mengacu kepada range B2:R510 pada sheet DATA HUJAN. Sedangkan indeks kolom yang harusnya bertipe angka dirujuk ke sel C2 yang bukan berupa angka, sehingga terjadilah kesalahan #VALUE!. Untuk mendapatkan indeks kolom yang tepat sesuai header kolom pada sel C2, dapat menggunakan fungsi MATCH misalkan MATCH($C$2;LOC;0). Jika terjadi kesalahan dari fungsi MATCH ini (misalkan header tidak ada yang sesuai atau tidak ditemukan), biasanya hasil akhirnya adalah #N/A.

    Dengan demikian, pada sel C5, formula yang digunakan seharusnya adalah:

    =SUMPRODUCT((YEAR(TGL)=$C$3)*(MONTH(TGL)=MONTH($B5))*INDEX('DATA HUJAN'!$B$2:$R$510;0;MATCH($C$2;LOC;0)))

    sedangkan untuk sel B5 (range B5:B10), dapat menggunakan formula yang sudah saya sampaikan sebelumnya:

    =DATE($C$3;ROW($A1)+(($E$3=2)*6);1)

    [B] Range Data Pada Sheet DATA HUJAN

    Perbaikan dari formula pada poin A di atas tidak serta merta membuat hasilnya sesuai. Kesalahan tetap terjadi dikarenakan ada beberapa data pada range B2:R510 pada sheet DATA HUJAN yang berisi tipe nilai bukan angka (Non Numeric Value). Misalkan saja pada sel H105, H106, H116 dan sel lainnya. Solusinya, ubah nilai-nilai Non Numeric tersebut menjadi 0 (nol) atau kosongkan saja.

    Selain itu, untuk definisi nama range TGL (range A2:A1048576) sebaiknya dibuat secara dinamis sehingga tidak terlalu banyak baris data kosong yang ikut terkalkulasi dan dapat dibuat menjadi sinkron dengan baris data aktualnya. Pada file yang mas lampirkan terakhir di atas, aktualnya hanya ada 509 baris data saja.

    Demikian hal-hal yang dapat saya jelaskan. Terlampir contoh perbaikannya. Silahkan dipelajari dan dimodifikasi kembali. Untuk beberapa sel yang masih ada kesalahan, coba diperbaiki dahulu sendiri ya. Semoga bermanfaat.

  2. manweljs_

    Jul 14 Terverifikasi + 5.020 Poin

    @Argha24

    file nya belum terlampir

  3. Lhoh, hehe..siap suhu

  4. Caton

    Jul 14 Terverifikasi Indonesia + 11.474 Poin

    @Argha24 ...

    Terlampir contoh formula yang mungkin sesuai. Untuk kolom HUJAN MAKSIMUM (kolom D), jika mas @Argha24 menggunakan Excel 2016, formulanya bisa diubah dengan menggunakan fungsi MAXIFS dengan notasi serupa dengan fungsi SUMIFS (saya pake Excel 2010 yang belum mendukung fungsi MAXIFS). Sedangkan untuk sel A12, saya tidak paham apa yang akan dihitung...

    Demikian, semoga sesuai dan bermanfaat.

  5. manweljs_

    Jul 14 Terverifikasi + 5.020 Poin
    Di sunting 2 bulan yang lalu oleh manweljs_

    Ikutan ya mas @Caton

    saya rasa yang di A12 itu maksudnya agar bulannya berganti mas, dari Juli - Desember jika diganti semester 2

  6. Caton

    Jul 14 Terverifikasi Indonesia + 11.474 Poin
    Di sunting 2 bulan yang lalu oleh Caton

    @manweljs_ ....

    saya rasa yang di A12 itu maksudnya agar bulannya berganti mas, dari Juli - Desember jika diganti semester 2

    Oh gitu... -_-; Berarti di sel A4 formulanya bisa diisi dengan:

    =DATE($B$2;ROW($A1)+(($D$2>1)*6);1)

    Trims buat infonya... :)

  7. Suhu @Caton dan @manweljs_ terimakasih banyak suhu...sudah dibantu pecahkan masalahnya.

  8. suhu @Caton

    Mohon pencerahannya dong, kok jadinya #Value ya ini...
    atau index itu harus format angka, gak boleh huruf?

  9. Caton

    Jul 15 Terverifikasi Jawaban Terpilih Indonesia + 11.474 Poin

    Mas @Argha24 ...

    Saya coba jelaskan sedikit mengenai masalah formulasi pada file yang mas lampirkan terakhir di atas sebagai berikut:

    [A] Formula Pada Sel C5

    =SUMPRODUCT((YEAR(TGL)=$C$3)*(MONTH(TGL)=$B5)*(INDEX(LOC;0;$C$2)))

    — Fungsi MONTH pada dasarnya akan menghasilkan nilai berupa angka (1 sampai dengan 12) sesuai dengan indeks bulan dari tanggal yang menjadi parameternya. Pada formula di atas, notasi MONTH(TGL)=$B5 akan selalu menghasilkan nilai FALSE oleh karena tipe nilai pada sel B5 adalah teks (sel B5 berisi teks JANUARY), bukan angka. Seharusnya, pada sel B5 berisi nilai angka (misalkan 42736 untuk tanggal 01/01/2017). Perlu diperhatikan, meskipun sel B5 sudah berisi nilai angka (tanggal), notasi MONTH(TGL)=$B5 akan tetap menghasilkan nilai FALSE oleh karena seharusnya pada notasi tersebut, nilai yang dibaca dari sel B5 adalah indeks bulannya, bukan tanggal secara keseluruhan. Sehingga, notasi yang tepat seharusnya adalah MONTH(TGL)=MONTH($B5).

    — Fungsi INDEX merujuk ke range data dan indeks kolom yang salah. Seharusnya, data yang akan diambil mengacu kepada range B2:R510 pada sheet DATA HUJAN. Sedangkan indeks kolom yang harusnya bertipe angka dirujuk ke sel C2 yang bukan berupa angka, sehingga terjadilah kesalahan #VALUE!. Untuk mendapatkan indeks kolom yang tepat sesuai header kolom pada sel C2, dapat menggunakan fungsi MATCH misalkan MATCH($C$2;LOC;0). Jika terjadi kesalahan dari fungsi MATCH ini (misalkan header tidak ada yang sesuai atau tidak ditemukan), biasanya hasil akhirnya adalah #N/A.

    Dengan demikian, pada sel C5, formula yang digunakan seharusnya adalah:

    =SUMPRODUCT((YEAR(TGL)=$C$3)*(MONTH(TGL)=MONTH($B5))*INDEX('DATA HUJAN'!$B$2:$R$510;0;MATCH($C$2;LOC;0)))

    sedangkan untuk sel B5 (range B5:B10), dapat menggunakan formula yang sudah saya sampaikan sebelumnya:

    =DATE($C$3;ROW($A1)+(($E$3=2)*6);1)

    [B] Range Data Pada Sheet DATA HUJAN

    Perbaikan dari formula pada poin A di atas tidak serta merta membuat hasilnya sesuai. Kesalahan tetap terjadi dikarenakan ada beberapa data pada range B2:R510 pada sheet DATA HUJAN yang berisi tipe nilai bukan angka (Non Numeric Value). Misalkan saja pada sel H105, H106, H116 dan sel lainnya. Solusinya, ubah nilai-nilai Non Numeric tersebut menjadi 0 (nol) atau kosongkan saja.

    Selain itu, untuk definisi nama range TGL (range A2:A1048576) sebaiknya dibuat secara dinamis sehingga tidak terlalu banyak baris data kosong yang ikut terkalkulasi dan dapat dibuat menjadi sinkron dengan baris data aktualnya. Pada file yang mas lampirkan terakhir di atas, aktualnya hanya ada 509 baris data saja.

    Demikian hal-hal yang dapat saya jelaskan. Terlampir contoh perbaikannya. Silahkan dipelajari dan dimodifikasi kembali. Untuk beberapa sel yang masih ada kesalahan, coba diperbaiki dahulu sendiri ya. Semoga bermanfaat.

  10. suhu @Caton terimakasih banyak pencerahannya.
    Jangan bosan saya tanya2 terus sifu...

 

atau Mendaftar untuk ikut berdiskusi!