Mencari nama barang

  1. 6 bulan yang lalu

    Selamat Siang Master,

    saya punya contoh kasus, untuk mencari nama barang dari stok barang yang terkecil.
    kiranya para master, dapat membantu memecahkan contoh kasus yang saya kerjakan saat ini.
    terima kasih.

  2. Caton

    Apr 25 Terverifikasi Indonesia + 12.014 Poin

    Pak @Marcos...

    Terlampir contoh penyelesaian yang dapat saya berikan. Semoga sesuai harapan.

    Demikian.

  3. Pak @Caton
    terima kasih atas penyelesaiannya..
    saya mau tanya, tanda *10^5 pada BOM dan *Row(xxxx))-4 pada Available, fungsinya untuk apa ya?
    Maaf, masi pemula pak..

  4. Caton

    Apr 25 Terverifikasi Indonesia + 12.014 Poin

    Sama-sama pak. Untuk hal yang bapak tanyakan, saya coba jelaskan sebagai berikut:

    [1]. Target kolom I pada sheet BOM adalah mendapatkan jumlah stok minimum dari kelompok barang tertentu (sesuai kolom B). Untuk itu, ada 2 kondisi yang harus dipenuhi yakni pilih jumlah stok setiap kode barang yang sesuai dengan kode barang yang dicari dan tentukan nilai minimumnya. Maka:

    — Untuk setiap barang dengan KODE FG yang sama. Pada tahap ini, dilakukan pengujian untuk setiap KODE FG pada range B5:B20 apakah sesuai dengan KODE FG yang diuji pada baris ke-5 (yakni pada sel B5). Ilustrasinya:

    = ($B$5:$B$20=B5)
    = {TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

    Hasil dari pengujian tersebut kemudian dikalikan dengan nilai pada kolom BALANCE MATERIAL (kolom H) untuk mendapatkan nilai jumlah stok. Oleh karena nilai TRUE setara dengan 1 dan nilai FALSE setara dengan 0, maka untuk setiap nilai TRUE jika dikalikan dengan nilai pada kolom H akan mengembalikan nilai pada kolom H itu sendiri, sedangkan jika nilainya FALSE maka hasilnya akan menjadi 0 (nol). Ilustrasinya:

    = ($B$5:$B$20=B5)*($H$5:$H$20)
    = {TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*
      {512;1;20;9;7;125;296;47;57;22;221;350;360;95;210;405}
    = {512;1;20;9;7;0;0;0;0;0;0;0;0;0;0;0}

    — Dari hasil kalkulasi proses pertama tersebut, ambil nilai minimum sesuai kelompok KODE FG. Jika nilai {512;1;20;9;7;0;0;0;0;0;0;0;0;0;0;0} diacu langsung ke dalam fungsi MIN, maka akan terjadi kesalahan hasil dikarenakan nilai terkecil adalah 0 (nol). Penggunaan fungsi SMALL juga sulit diterapkan oleh karena kemungkinan nilai-nilai tersebut akan bersifat dinamis. Karenanya, untuk menghindari hasil pengujian setiap KODE FG yang tidak sama dengan nilai yang dicari pada sel B5 menghasilkan nilai 0 (nol), nilai-nilai (FALSE) tersebut harus dibuat lebih besar dari nilai-nilai yang ada. Ilustrasinya:

    = (($B$5:$B$20<>B5)*10^5)
    = {FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}*100000
    = {0;0;0;0;0;100000;100000;100000;100000;100000;100000;100000;100000;100000;100000;100000}

    Nilai kedua tahap ini jika dijumlahkan akan menjadi:

    = (($B$5:$B$20=B5)*($H$5:$H$20))+(($B$5:$B$20<>B5)*10^5)
    = {512;1;20;9;7;0;0;0;0;0;0;0;0;0;0;0}+ 
      {0;0;0;0;0;100000;100000;100000;100000;100000;100000;100000;100000;100000;100000;100000}
    = {512;1;20;9;7;100000;100000;100000;100000;100000;100000;100000;100000;100000;100000;100000}

    Nilai yang dihasilkan pada tahap ini, jika diacu ke dalam fungsi MIN akan menghasilkan nilai 1 sebagai nilai terkecil. Hanya saja, dikarenakan argumen fungsi MIN tidak dapat menggunakan array secara langsung, kita perlu mengubahnya menjadi Array Formula. Namun, saya sendiri lebih suka menggunakan fungsi SUMPRODUCT oleh karena fungsi ini memiliki kemampuan untuk mengkalkulasikan argumen yang diberikan kepada fungsi ini sebagai array.

  5. Caton

    Apr 25 Terverifikasi Indonesia + 12.014 Poin

    [2]. Hampir serupa dengan poin pertama, hanya saja dalam hal ini target hasil yang diinginkan adalah nama barang dari setiap kode barang dengan stok minimum. Formula pada sel J5 sheet AVAILABLE pada dasarnya bergantung kepada nilai yang dihasilkan oleh formula pada kolom I pada sheet yang sama. Bagian formula:

    (BOM!$B$5:$B$20=$B5)*(BOM!$H$5:$H$20=$I5)*ROW(BOM!$B$5:$B$20)

    akan melakukan pencarian hasil berdasarkan pengujian 2 kondisi, yakni untuk setiap KODE FG yang sama dengan KODE BARANG, dan BALANCE MATERIAL sama dengan MATERIAL AVAILABLE. Karena hasil yang diinginkan ada pada kolom NAMA BARANG RM sheet BOOM, dan tipe nilai pada kolom tersebut adalah teks, maka nilai-nilai tersebut tidak dapat diproses secara langsung. Dalam hal ini, proses yang dapat dilakukan adalah mengambil indeks baris dimana nilai yang diuji berkesesuaian. Sehingga, digunakanlah fungsi ROW untuk proses tersebut. Ilustrasinya:

    = (BOM!$B$5:$B$20=$B5)*(BOM!$H$5:$H$20=$I5)*ROW(BOM!$B$5:$B$20)
    = {TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*
      {FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*
      {5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}
    = {0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0}*{5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}
    = {0;6;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

    Hasil kalkulasi di atas, dikarenakan berupa array, daripada dijumlahkan dengan cara Array Formula, maka lebih baik menggunakan fungsi SUMPRODUCT langsung. Sehingga:

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

    Oleh karena nilai yang dicari berada pada range E5:E20 sheet BOOM, maka digunakan fungsi INDEX untuk mengambil nilai pada range tersebut dengan indeks baris berupa nilai yang dihasilkan oleh fungsi SUMPRODUCT sebelumnnya. Hanya saja, oleh karena fungsi INDEX akan mengubah sebuah range menjadi array, maka indeks nilai pertamanya akan menjadi 1 (jadi range E5:E20 akan berubah indeks barisnya menjadi 1 sampai dengan 16). Dengan demikian, jika nilai yang dihasilkan oleh fungsi SUMPRODUCT tersebut diacu langsung ke dalam fungsi INDEX, maka hasilnya adalah indeks baris ke-6 pada range E5:E20, yakni Terminal SM-112 bukan Socket JF03. Untuk itu, nilai yang dihasilkan oleh fungsi SUMPRODUCT tersebut harus disesuaikan dengan indeks baris pada fungsi INDEX dengan mengurangi indeks baris aktual dengan indeks baris sebelum range E5:E20, yakni E4. Oleh karena telah diketahui indeks baris aktual sebelum range E5:E20 adalah 4, maka ditambahkan notasi -4 untuk menyesuaikan nilai yang dihasilkan oleh fungsi SUMPRODUCT tersebut menjadi indeks baris pada fungsi INDEX.

    Saya kira itu saja yang dapat saya jelaskan. Semoga pak @Marcos tidak bertambah bingun dengan penjelasan tersebut dan tentunya, semoga bermanfaat.

    Demikian.

  6. terima kasih Pak @Caton atas penjelasan ya yang begitu detil.
    tapi masih agak bingung sedikit tentang rumus array, karena sehari-hari saya menggunakan rumus excel yang biasa saja.
    Adakah link seperti yang pak @Caton jelaskan, agar saya dapat memperdalam rumus-rumus. terima kasih

  7. Caton

    Apr 26 Terverifikasi Indonesia + 12.014 Poin

    Sama-sama pak @Marcos. Untuk mengenal Array Formula, bapak bisa mulai membacanya di sini , selebihnya bapak bisa melakukan pencarian langsung artikel mengenai Array Formula.

    Demikian.

  8. terima kasih atas infonya pak @Caton

  9. Pak @Caton minta tolong kembali, untuk kasus yang ini. ketika ada RM Available sama, maka hasil dikeluarkan error (#Ref). Bisa tidak, hasilnya semua nama barang RM Available ?
    terima kasih sebelumnya.

  10. Caton

    Apr 28 Terverifikasi Indonesia + 12.014 Poin

    Pak @Marcos, coba lampirkan contoh kasusnya.

  11. Berikut contohnya pak @Caton saya blok warna hijau.

  12. Caton

    Apr 28 Terverifikasi Indonesia + 12.014 Poin

    Pak @Marcos...

    Terlampir solusi yang bisa digunakan. Dalam hal ini saya gunakan kolom bantu (kolom L sampai kolom Q) agar formulanya tidak menjadi terlalu panjang (jadi lebih mudah dipelajari).

    Demikian.

  13. terima kasih Pak @Caton atas penyelesaiannya.

 

atau Mendaftar untuk ikut berdiskusi!