Untitled conversation

  1. 4 bulan yang lalu

    dear suhu - suhu excel semua

    mohon bantuannya lagi yah. saya punya case seperti ini. mencari value retur barang. nilai value retur berdasarkan angka pembelian terakhir. contoh data dan criteria terlampir

    terima kasih sebelumnya

  2. manweljs_

    Jul 30 Terverifikasi + 5.718 Poin

    @alfri

    terlampir contohnya, silahkan diskusikan lagi jika tidak sesuai

  3. Caton

    Jul 30 Terverifikasi Indonesia + 12.523 Poin
    Di sunting 4 bulan yang lalu oleh Caton

    Mohon izinnya mas @manweljs_ ...

    Terlampir contoh utak-atik dari saya buat mas @alfri. Saya pakai kolom bantu.. Kalau masih banyak problemnya, silahkan didiskusikan sama mas @manweljs_ ... :D :D :D

    Demikian.

  4. @Caton @manweljs_

    Salute....

    dari kedua solusi yang diberikan, dua dua nya kereeenn . . .terima kasih atas solusi yang diberikan

  5. @Caton

    dari solusi yang diberikan, apakah bisa diberikan penjelasan (narasi) atas rumus rumus yang digunakan, Contoh : IF(LEN([@A]);VLOOKUP([@A];db.Return;2;0);"") yang bisa diterjemahkan menjadi : jika (bla. . .bla. . .bla. . ); maka vlookup( bla . . bla . .bla). #maapkeuncumangertiifdoang

    terima kasih atas penjelasannya. #biarbisadipelajari

    Regards
    Alfri

  6. Caton

    Jul 31 Terverifikasi Indonesia + 12.523 Poin
    Di sunting 4 bulan yang lalu oleh Caton

    @Alfri ...

    Mungkin ada beberapa formula yang agak mudah dinarasikan, namun ada juga yang sulit. Saya coba sebisa saya. Untuk formula:

    =IF(LEN([@A]);VLOOKUP([@A];db.Return;2;0);"")

    jika dinarasikan:

    Jika ada nilai pada sel [@A], maka lakukan pencarian nilai pada sel [@A] tersebut pada tabel db.Return. Jika nilai pada sel [@A] kosong, maka kembalikan nilai kosong.

    Catatan:
    Notasi [@A] digunakan untuk merujuk ke kolom dengan header A pada baris yang sama (Cell Reference), sedangkan notasi [A] digunakan untuk merujuk ke kolom dengan header A (Range Reference). Jadi notasi [@A] bukan kolom A pada Worksheet, namun header kolom dari objek tabel (header di bawah sel KODE ITEM). Gambarannya:

    Screenshoot001A.png

    Yang lainnya nyusul ... :D

  7. wuuihhh canggih.....mas @Caton

    terima kasih supportnya . . .
    oiya dari solusi yang diberikan diatas, ada 1 bug mas
    saya coba masukin item baru di 2 tabel sales dan retur
    saya masukin total pembelian 79 pcs (PP010358) dan nilai retur (qty) = 21 pcs
    dan hasil yang muncul di sheet tes untuk kode PP010358 banyak nya qty yang muncul tetap 72

    untuk di case ini, menurut saya seharusnya banyaknya qty (kolom D, sheet tes) tetap 21, karena nilai retur lebih kecil dari jumlah pembelian pertama yaitu yang sebesar 72 pcs

    saya lampirin lagi contohnya :D

    oiya ditunggu penjelasan narasinya, biar klo ada bug saya bisa solve sendiri masalahnya,

  8. Caton

    Jul 31 Terverifikasi Indonesia + 12.523 Poin

    @alfri ...

    Dicoba dahulu file terlampir mas, semoga sesuai revisinya...

  9. Caton

    Jul 31 Terverifikasi Indonesia + 12.523 Poin
    Di sunting 4 bulan yang lalu oleh Caton

    @Alfri ...

    Untuk formula:

    =IF(LEN([@G]);INDEX(db.Sales[F];SUMPRODUCT(MATCH([@G]&[@H];db.Sales[A]&db.Sales[B];0)));"")

    jika dinarasikan:

    Jika ada nilai pada sel [@G], maka lakukan pencarian pada range data db.Sales kolom [F], berdasarkan kriteria pada sel [@G] dan sel [@H]. Jika nilai pada sel [@G] kosong, maka kembalikan nilai kosong.

    Formula tersebut pada intinya untuk mendapatkan data NOMOR FAKTUR PAJAK berdasarkan KODE ITEM dan TANGGAL (kedua kriteria tersebut diambil dari kolom bantu yakni sel [@G] dan sel [@H]). Mengapa harus dengan 2 kriteria? Hal ini disebabkan oleh karena syaratnya data harus tersusun berdasarkan tanggal terakhir dahulu untuk setiap kode item yang sama. Contohnya, untuk kode item BR500101, tanggal terakhir adalah 30/01/2018 dan nomor fakturnya adalah 010.002-14.03274321.

    Masalahnya, pada tabel data db.Sales, terdapat duplikasi data untuk kolom KODE dan kolom TANGGAL. Jika mencari data berdasarkan salah satu kolom, kemungkinan akan dihasilkan nilai yang tidak sesuai. Untuk mengatasi hal tersebut, salah satu caranya adalah dengan membuat kode unik — misalkan dengan menggabungkan nilai dari kedua kolom. Meski tidak sepenuhnya efektif, namun melihat data yang ada, cara tersebut dapat digunakan.

    Meski fungsi MATCH mendukung tabel array, namun fungsi ini tidak mendukung array hasil penggabungan 2 kolom. Agar dapat berfungsi dengan array dari 2 kolom tersebut, maka fungsi MATCH harus bekerja sebagai Array Formula. Untuk membuatnya menjadi Array Formula, kita dapat menekan kombinasi tombol CONTROL SHIFT ENTER (CSE), atau menggunakan fungsi SUMPRODUCT.

    Sebagai contoh, formula pada sel D6 akan mencari nilai dari tabel db.Sales berdasarkan nilai pada sel H6 dan I6. Indeks baris dari data ditentukan oleh bagian formula:

    =SUMPRODUCT(MATCH([@G]&[@H];db.Sales[A]&db.Sales[B];0))

    Pada formula tersebut, masing-masing parameter akan menghasilkan nilai:

    [@G]&[@H]
    = "BR500101"&43130
    = "BR50010143130"
    
    db.Sales[A] =
    {"BR500101";"BR500101";"BR500101";"BR500101";"BR500102";"BR500102";"BR500102";"BR500103";"BR500104";"BR500105";"BR500105";"BR500105"}
    
    db.Sales[B] =
    {42370;42461;42896;43130;42370;42461;42896;42370;42370;42370;42461;42896}
    
    db.Sales[A]&db.Sales[B] = 
    {"BR50010142370";"BR50010142461";"BR50010142896";"BR50010143130";"BR50010242370";"BR50010242461";"BR50010242896";"BR50010342370";"BR50010442370";"BR50010542370";"BR50010542461";"BR50010542896"}

    Dari nilai-nilai tersebut, dapat diketahui nilai 010.002-14.03274321 berada pada indeks data ke 4. Sehingga, jika indeks baris tersebut dimasukkan ke fungsi INDEX pada formula awal maka akan terurai menjadi:

    = INDEX(db.Sales[F];SUMPRODUCT(MATCH([@G]&[@H];db.Sales[A]&db.Sales[B];0)))
    = INDEX(db.Sales[F];4;0)))
    = "010.002-14.03274321"

    Yang lain menyusul jika sempat... :) Demikian.

  10. 3 bulan yang lalu

    Caton

    Agu 1 Terverifikasi Indonesia + 12.523 Poin
    Di sunting 3 bulan yang lalu oleh Caton

    @alfri ...

    Untuk formula di sel E6:

    =IF(LEN([@G]);IF(LEN([@A])*(COUNTIF(db.Sales[A];[@A])>1);IF(LEN([@A])*([@I]>[@B]);[@B];[@I]);IF(SUMIF($H$5:$H6;[@G];$J$5:$J6)>VLOOKUP([@G];db.Return;2;0);[@I]-(SUMIF($H$5:$H6;[@G];$J$5:$J6)-VLOOKUP([@G];db.Return;2;0));[@I]));IF(LEN($H5)*(ROW(A1)<>1)*(COUNTIF($H$5:$H5;$H5)>1);SUMIF($H$5:$H5;$H5;$E$5:$E5);""))

    agak repot menjelaskannya. Jadi saya coba buat bloknya dahulu seperti berikut:

    IF (LEN([@G]);
       IF (LEN([@A])*(COUNTIF(db.Sales[A];[@A])>1);
          IF (LEN([@A])*([@I]>[@B]);
             [@B];
             [@I]
          );
          IF (SUMIF($H$5:$H6;[@G];$J$5:$J6)>VLOOKUP([@G];db.Return;2;0);
             [@I]-(SUMIF($H$5:$H6;[@G];$J$5:$J6)-VLOOKUP([@G];db.Return;2;0));
             [@I]
          )
       );
       IF(LEN($H5)*(ROW(A1)<>1)*(COUNTIF($H$5:$H5;$H5)>1);
          SUMIF($H$5:$H5;$H5;$E$5:$E5);
          ""
       )
    )

    Blok formula tersebut jika diuraikan ke dalam bentuk narasi, ± menjadi:

    [C1] : (H6 > 0) ?
    [T1] :
       [C2] : (B6 > 0) dan ((banyaknya data B6 pada db.Sales) > 1) ?
       [T2] :
          [C3] : (B6 > 0) dan (J6 > C6) ?
             [T3] : ambil nilai C6.
             [F3] : ambil nilai J6.
       [F2] :
          [C4] : (jumlah nilai range $J$5:$J6 untuk item = H6) > (jumlah item returnya) ?
             [T4] : J6 - ((jumlah nilai range $J$5:$J6 untuk item = H6) - (jumlah item returnya)).
             [F4] : J6.
    [F1] :
       [C5] : (H5 > 0) dan (indeks baris <> 1) dan ((banyaknya data H5 pada range H5:H5) > 1) ?
          [T5] : jumlahkan nilai range H5:H5 untuk item = E5.
          [F5] : kosongkan.
    
    Catatan:
    [C-n] : Kondisi yang akan diperiksa.
    [T-n] : Blok yang akan dikerjakan jika kondisi dari C-n bernilai TRUE.
    [F-n] : Blok yang akan dikerjakan jika kondisi dari C-n bernilai FALSE.
    
    Untuk notasi LEN[@G] saya ubah menjadi (H6 > 0). Notasi LEN[@G] pada dasarnya untuk memeriksa apakah pada sel H6 ada nilainya atau tidak. Karena jika sel H6 tidak ada isinya (kosong), maka notasi LEN[@G] akan menghasilkan nilai 0 (nol) yang oleh Excel diartikan sebagai FALSE.

    Blok di atas jika dibaca, kira-kira:

    Jika kondisi C1 bernilai TRUE, maka kerjakan blok T1 dan abaikan blok F1. Namun jika kondisi C1 benilai FALSE, maka kerjakan blok F1 dan abaikan blok T1. Demikian pula dengan yang lainnya!

    Untuk pertanyaan mengenai formula lainnya, coba disusun seperti yang saya jelaskan di atas. Kemudian coba pahami blok-blok formulanya. Saya kira demikian. Mudah-mudahan bisa dipahami.

  11. muaantabb ......

    terima kasih mas @Caton saya akan pelajari

  12. manweljs_

    Agu 1 Terverifikasi + 5.718 Poin
    Di sunting 3 bulan yang lalu oleh manweljs_

    @Caton
    ...Untuk formula di sel E6: ...

    yang di H6 belum mas, yang ada lookup nya :

    =IF(LEN([@A])*(COUNTIF($B$5:$B6,[@A])=1),[@A],IF(LEN($H5)*(COUNTIF($H$5:$H5,$H5)<COUNTIF(db.Sales[A],$H5))*(SUMIF($H$5:$H5,$H5,$J$5:$J5)<=INDEX($C$6:$C6,LOOKUP(2,1/($B$6:$B6<>""),ROW($A$1:$A1)))),$H5,""))

    ;)

  13. mas @Caton nanya lagi . . maaf ngerepotin lagi :D #mintatlngmulujadinya

    mas, misalnya di tabel report yang ada itu, di kolom A, sudah terdapat drop down list, nah misalnya drop down list nya dihilangkan gmana yah ? jadinya saya hanya tinggal input di tabel db.return masukin kode dan qty nanti secara otomatis data akan masuk di tabel report di kolom A & B #udhpegelbangetnginputribuanitem

    maaf beribu ribu maaf sudah merepotkan

    terima kasih

  14. Caton

    Agu 1 Terverifikasi Indonesia + 12.523 Poin
    Di sunting 3 bulan yang lalu oleh Caton

    @manweljs_ ...

    ... yang di H6 belum mas, yang ada lookup nya ...

    Untuk sel H6 (dan juga sel-sel lainnya), saya yakin mas @manweljs_ dengan sangat mudah memahami formulanya... :P :D

    Tapi okelah, saya jelaskan formulanya untuk mas @alfri ... Nilai pada kolom H ini merupakan kunci awal untuk formula pada sel-sel lainnya. Sebagai contoh, saya ambil formula pada sel H12:

    Screenshoot003.png

    =IF(LEN([@A])*(COUNTIF($B$5:$B12;[@A])=1);[@A];IF(LEN($H11)*(COUNTIF($H$5:$H11;$H11)<COUNTIF(db.Sales[A];$H11))*(SUMIF($H$5:$H11;$H11;$J$5:$J11)<=INDEX($C$6:$C12;LOOKUP(2;1/($B$6:$B12<>"");ROW($A$1:$A7))));$H11;""))

    — A. Pertama-tama, periksa apakah sel B12 (KODE ITEM) ada nilai atau isinya. Dan jika sel B12 ada nilainya, periksa apakah nilai atau kode item tersebut sudah ada pada range $B$5:$B12 (tujuannya untuk mencegah kode item yang sama tidak diproses lebih dari sekali). Jika kedua kondisi terpenuhi (TRUE, yakni kode itemnya ada dan kode item tersebut baru muncul pertama kali), maka ambil nilai atau kode item dari sel B12 tersebut! Formulanya:

    =IF(LEN([@A])*(COUNTIF($B$5:$B12;[@A])=1);[@A]; ...

    — B. Jika kondisi tersebut di atas tidak terpenuhi, maka untuk blok FALSE ini akan dilakukan proses pemeriksaan terhadap beberapa kondisi yang harus terpenuhi:

    [1] Apakah sel di atas (H11) ada isinya (kode itemnya). Formulanya:

    LEN($H11)

    [2] Apakah banyaknya data yang sudah diproses sebelumnya sesuai kriteria kode item pada sel H11 pada tabel db.Report (range $H$5:$H11) lebih kecil dari banyaknya data dengan kriteria yang sama tabel db.Sale (kolom [A]). Tujuannya agar kode item yang akan diproses tidak melebihi jumlah data sebenarnya. Formulanya:

    (COUNTIF($H$5:$H11;$H11)<COUNTIF(db.Sales[A];$H11))

    [3] Apakah jumlah barang (pada kolom I) retur dengan kode item sesuai dengan nilai pada sel H11 yang sudah diproses di tabel db.Report (range $H$5:$H11) lebih kecil atau sama dengan jumlah barang retur pada db. Return. Jika benar, artinya masih ada data lainnya yang akan diproses. Formulanya:

    (SUMIF($H$5:$H11;$H11;$J$5:$J11)<=INDEX($C$6:$C12;LOOKUP(2;1/($B$6:$B12<>"");ROW($A$1:$A7))))

    Pada formula di atas, notasi LOOKUP(2;1/($B$6:$B12<>"");ROW($A$1:$A7)) berguna untuk mendapatkan baris terakhir dari range $B$6:$B12 yang ada nilainya. Oleh karena sel B12 berisi kode item, maka indeks baris yang diambil dari notasi ROW($A$1:$A7) adalah 7. Maka nilai yang dihasilkan fungsi INDEX tersebut untuk data pada range $H$5:$H11 baris ke-7 adalah 45. Selain dengan formula tersebut, alternatifnya adalah dengan formula:

    (SUMIF($H$5:$H11;$H11;$J$5:$J11)<=IFERROR(INDEX(db.Return[B];MATCH(IF(LEN([@A]);[@A];$H11);db.Return[A];0));0))

    Jika ketiga kondisi tersebut terpenuhi, maka formula akan menghasilkan nilai yang sama dengan nilai pada sel sebelumnya yakni H11. Namun jika kondisi tersebut juga tidak terpenuhi, maka formula akan mengembalikan nilai kosong.

    IF(LEN($H11)*(COUNTIF($H$5:$H11;$H11)<COUNTIF(db.Sales[A];$H11))*(SUMIF($H$5:$H11;$H11;$J$5:$J11)<=IFERROR(INDEX(db.Return[B];MATCH(IF(LEN([@A]);[@A];$H11);db.Return[A];0));0));$H11;"")

    Demikian yang bisa saya jelaskan untuk formula kolom bantu yakni kolom [G] . Yang lain tinggal dipelajari saja ya mas @alfri. Jika masih kurang paham, bisa dikonsultasikan ke mas @manweljs_. Beliau pakarnya untuk formula-formula yang kompleks dan rumit... :P :D :D :D

    Mengenai yang mas @alfri tanyakan,

    ... misalnya di tabel report yang ada itu, di kolom A, sudah terdapat drop down list, nah misalnya drop down list nya dihilangkan gmana yah ? jadinya saya hanya tinggal input di tabel db.return masukin kode dan qty nanti secara otomatis data akan masuk di tabel report di kolom A & B ...

    Terlampir contoh yang dapat saya berikan. Perlu diperhatikan, jika saat menambah baris tabel db.Report terjadi kesalahan seperti:

    sehingga timbul semacam ini (bisa juga tidak muncul):

    Screenshoot008.png

    maka coba blok seluruh range data tabel db.Report (pastikan seluruh range, termasuk range kolom Helper — unhide dahulu kolom Helper):

    Screenshoot009.png

    lalu tekan tombol CONTROL+D 1 atau 2 kali untuk menyalin formula dari baris pertama. Jangan tanya saya mengapa terjadi saya juga tidak tahu... ;)

    Demikian, semoga sesuai dan bermanfaat.

  15. Caton

    Agu 1 Terverifikasi Indonesia + 12.523 Poin
    Di sunting 3 bulan yang lalu oleh Caton

    Ada yang tertinggal sedikit:

    Terlampir contoh yang dapat saya berikan. Perlu diperhatikan, jika saat menambah baris tabel db.Report terjadi kesalahan seperti:

    Screenshoot004.png

    sehingga timbul semacam ini (bisa juga tidak muncul):

    dan seterusnya... Demikian dengan ini kesalahan kami perbaiki... :D

  16. manweljs_

    Agu 2 Terverifikasi + 5.718 Poin

    Untuk sel H6 (dan juga sel-sel lainnya)... dst...

    ternyata benar point mas @Caton cuma hasil chit-chat aja :P :D :D ;)

  17. manweljs_

    Agu 2 Terverifikasi + 5.718 Poin
    Di sunting 3 bulan yang lalu oleh manweljs_

    edit - doble post

  18. Caton

    Agu 2 Terverifikasi Indonesia + 12.523 Poin
    Di sunting 3 bulan yang lalu oleh Caton

    ... ternyata benar point mas @Caton cuma hasil chit-chat aja :P :D :D ;)

    Wkwkwkwk... serasa mau menambah point lagi jadinya ... :D :D :D Mas @alfri mana nih, kok gak hadir-hadir... (^_^)/ Kasih komen dong mas @alfri ... :D

  19. hallo mas @Caton hehehehe

    maaf baru respon lagi . . . . mau nanya lagi nih #mintadirevisilagi

    dari solusi mas @caton berikan dan saya coba dengan data sesungguhnya, saya nemu kendala lagi mas. dimana ada case 1 item barang mempunyai 3 no faktur pajak yang berbeda namun di tanggal yang sama. dari penjelasan mas @caton diatas untuk formula :

    " =IF(LEN([@G]);INDEX(db.Sales[F];SUMPRODUCT(MATCH([@G]&[@H];db.Sales[A]&db.Sales[B];0)));"") " ini pemahaman saya kan harus ada primary key baru, yang mana primary key yang dibuat oleh mas @caton dalam solusi ini adalah gabungan dari item kode dengan tanggal maka akan didapat no faktur pajak, jadinya saya bingung deh bikin primary key nya :D

    yang ingin saya tanyakan, bagaimana membuat primary key yang baru untuk kasus 1 item kode mempunyai 3 nomor faktur pajak yang berbeda namun di tanggal yang sama.

    sample data terlampir

    terima kasih banyak mas @caton :D

  20. Caton

    Agu 7 Terverifikasi Indonesia + 12.523 Poin

    @alfri ...

    Apakah harus dengan 2 kunci pencarian? Aktualnya tidak. Pada tabel sheet RESULT, kolom H memang menjadi kunci utama untuk digunakan pada tabel tersebut. Dengan mendapatkan kunci tersebut, maka kita sudah menemukan jalan untuk mendapatkan posisi data yang dituju. Kalau dinarasikan:

    Untuk setiap KODE ITEM yang sesuai dengan yang ingin dicari, ambil indeks baris datanya.

    Kalau sebelumnya saya menggunakan kombinasi KODE ITEM dan TANGGAL sebagai kunci, adalah kurang tepat karena sebenarnya data langsung dapat diambil melalui indeks barisnya. Hanya saja, dalam hal ini TANGGAL pada tabel utama harus tersusun secara Ascending agar datanya sesuai (meski dapat diperbaikin dengan formula tambahan).

    Terlampir contoh solusinya. Silahkan dipelajari...

 

atau Mendaftar untuk ikut berdiskusi!