Rumus Index untuk beberapa Sheet

  1. 3 bulan yang lalu

    Selamat sore suhu,
    Saya mau bertanya apakah bisa menggabungkan data di beberapa sheet menjadi dalam satu sheet saja dan ada beberapa kriteria..
    Jadi ada beberapa sheet : CR-QO, CR-PROD, CR-ENG, dst yang menjadi master data. Lalu dengan kriteria pada kolom status yg berisi "Darurat" dan pada kolom Ket. Logbook yg berisi "Published", yang memenuhi kriteria tersebut data harus masuk di Sheet berjudul PEMESANAN LOGBOOK sesuai kolom yg ada di sheet itu.. Saya lampirkan file berikut..

    Mohon bantuannya mas @Caton ..
    Terimaksih...

    Mbak @Fika56 ...

    Hadeuh mbak Fika ... -_-;

    Yang file C itu pada dasarnya hampir serupa dengan file B. Hanya saja file B menggunakan tabel bantu, sedangkan file C lebih mengandalkan Array Formula. Saya mulai dari definisi nama rangenya.

    [1]. db.Sheets

    Nama range ini merupakan daftar nama sheet yang akan diproses. Tujuannnya agar nama sheet dapat diambil berdasarkan indeks array. Baris data pada nama range tersebut jika diambil dengan fungsi INDEX untuk indeks array = 1, ± hasilnya akan terlihat sebagai berikut:

    = INDEX(db.Sheets; 1)
    = INDEX({"CR-PROD"\"CR-QO"\"CR-R&D"\"CR-ENG"\"CR-GUDANG"\"CR-OHSE"}; 1)
    = "CR-PROD"

    [2]. dbCount

    Nama range ini merupakan jumlah total data dari seluruh target sheet yang sesuai dengan kriteria. Kalkulasinya menggunakan Array Formula yang dirangkum dengan fungsi SUMPRODUCT. Oleh karena db.Sheets merupakan array teks nama sheet target, maka digunakan fungsi INDIRECT untuk mengalihkan kalkulasi ke alamat aktualnya.

    = SUMPRODUCT(COUNTIFS(INDIRECT("'"&db.Sheets&"'!F4:F100");"PUBLISHED";INDIRECT("'"&db.Sheets&"'!L4:L100");
      "DARURAT"))
    = 8

    [3]. db.Array
    Nama range ini array dari posisi baris maksimum dari setiap set data. Nama range ini bisa juga dianggap sebagai SUBTOTAL dari masing-masing jumlah data per sheet. Sebagai komparasi, mbak bisa lihat hasil dari nama range ini pada file B, sheet KALKULASI, range D7:D22. Jadi untuk setiap jumlah data per sheet, akan dihitung posisi aktualnya sebagai indeks baris maksimum.

    = MMULT(0+(ROW(INDIRECT("1:"&COUNTA(db.Sheets)))>=TRANSPOSE(ROW(INDIRECT("1:"&COUNTA(db.Sheets)))));
      TRANSPOSE(COUNTIFS(INDIRECT("'"&db.Sheets&"'!F4:F100");"PUBLISHED";INDIRECT("'"&db.Sheets&"'!L4:L100");
      "DARURAT")))

    Formulanya menggunakan kalkulasi antar array, dimana array pertama adalah:

    = 0+(ROW(INDIRECT("1:"&COUNTA(db.Sheets)))>=TRANSPOSE(ROW(INDIRECT("1:"&COUNTA(db.Sheets)))))
    = 0+({1;2;3;4;5;6}>={1\2\3\4\5\6})
    = {1\0\0\0\0\0;1\1\0\0\0\0;1\1\1\0\0\0;1\1\1\1\0\0;1\1\1\1\1\0;1\1\1\1\1\1}

    dan aray kedua adalah:

    = TRANSPOSE(COUNTIFS(INDIRECT("'"&db.Sheets&"'!F4:F100");"PUBLISHED";
      INDIRECT("'"&db.Sheets&"'!L4:L100");"DARURAT"))
    = TRANSPOSE({2\2\1\1\2\0})
    = {2;2;1;1;2;0}

    Perhatikan gambar berikut:

    [attachment:5b69ad1f6e921]

    Dari gambar di atas, dapat diketahui nama range tersebut berisi array:

    = {2;4;5;6;8;8}

    Demikian persiapan awalnya...

  2. Caton

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

    Mbak @Fika56 ...

    Terlampir contoh yang dapat saya berikan. Masing-masing:

    [1]. Tes - LIST LOGBOOK PUBLISHED A : dengan kolom bantu dan nama range;
    [2]. Tes - LIST LOGBOOK PUBLISHED B : dengan kolom bantu dan nama range juga;
    [3]. Tes - LIST LOGBOOK PUBLISHED C : dengan nama range dan Array Formula;
    [4]. Tes - LIST LOGBOOK PUBLISHED D : dengan VBA.

    Silahkan dipilih, diperiksa, dicoba dan dipelajari. Mohon maaf, untuk file A sampai dengan C, saya tidak bisa menjelaskan formulanya karena alur prosesnya ribet... :) Kalau untuk VBA, InsyaAllah bisa saya jelaskan karena baris scriptnya cuma sedikit (kalau mau) ... :D

    Demikian, semoga sesuai.

  3. Mas @Caton terimakasih bantuannya.. Keren lah ????
    Coba saya pelajari smoga bisa cepat paham.. hehe

  4. Caton

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

    Sama-sama mbak @Fika56 ... Satu lagi solusi, tanpa formula atau VBA, mbak bisa menggunakan Power Query — jika mbak menggunakan Excel mulai dari versi 2010. Saya pribadi lebih suka menggunakan VBA atau PQ untuk kasus seperti yang mbak tanyakan.

    Semoga cepat paham dengan formulanya... :)

  5. Mas @Caton mau jelasin yg Tes C atau gak y?
    Jika berkenan.. Biar sya ada sedikit gambaran.. ????

    Sbelumnya terima kasih..

  6. Caton

    Agu 7 Terverifikasi Jawaban Terpilih Indonesia + 12.523 Poin

    Mbak @Fika56 ...

    Hadeuh mbak Fika ... -_-;

    Yang file C itu pada dasarnya hampir serupa dengan file B. Hanya saja file B menggunakan tabel bantu, sedangkan file C lebih mengandalkan Array Formula. Saya mulai dari definisi nama rangenya.

    [1]. db.Sheets

    Nama range ini merupakan daftar nama sheet yang akan diproses. Tujuannnya agar nama sheet dapat diambil berdasarkan indeks array. Baris data pada nama range tersebut jika diambil dengan fungsi INDEX untuk indeks array = 1, ± hasilnya akan terlihat sebagai berikut:

    = INDEX(db.Sheets; 1)
    = INDEX({"CR-PROD"\"CR-QO"\"CR-R&D"\"CR-ENG"\"CR-GUDANG"\"CR-OHSE"}; 1)
    = "CR-PROD"

    [2]. dbCount

    Nama range ini merupakan jumlah total data dari seluruh target sheet yang sesuai dengan kriteria. Kalkulasinya menggunakan Array Formula yang dirangkum dengan fungsi SUMPRODUCT. Oleh karena db.Sheets merupakan array teks nama sheet target, maka digunakan fungsi INDIRECT untuk mengalihkan kalkulasi ke alamat aktualnya.

    = SUMPRODUCT(COUNTIFS(INDIRECT("'"&db.Sheets&"'!F4:F100");"PUBLISHED";INDIRECT("'"&db.Sheets&"'!L4:L100");
      "DARURAT"))
    = 8

    [3]. db.Array
    Nama range ini array dari posisi baris maksimum dari setiap set data. Nama range ini bisa juga dianggap sebagai SUBTOTAL dari masing-masing jumlah data per sheet. Sebagai komparasi, mbak bisa lihat hasil dari nama range ini pada file B, sheet KALKULASI, range D7:D22. Jadi untuk setiap jumlah data per sheet, akan dihitung posisi aktualnya sebagai indeks baris maksimum.

    = MMULT(0+(ROW(INDIRECT("1:"&COUNTA(db.Sheets)))>=TRANSPOSE(ROW(INDIRECT("1:"&COUNTA(db.Sheets)))));
      TRANSPOSE(COUNTIFS(INDIRECT("'"&db.Sheets&"'!F4:F100");"PUBLISHED";INDIRECT("'"&db.Sheets&"'!L4:L100");
      "DARURAT")))

    Formulanya menggunakan kalkulasi antar array, dimana array pertama adalah:

    = 0+(ROW(INDIRECT("1:"&COUNTA(db.Sheets)))>=TRANSPOSE(ROW(INDIRECT("1:"&COUNTA(db.Sheets)))))
    = 0+({1;2;3;4;5;6}>={1\2\3\4\5\6})
    = {1\0\0\0\0\0;1\1\0\0\0\0;1\1\1\0\0\0;1\1\1\1\0\0;1\1\1\1\1\0;1\1\1\1\1\1}

    dan aray kedua adalah:

    = TRANSPOSE(COUNTIFS(INDIRECT("'"&db.Sheets&"'!F4:F100");"PUBLISHED";
      INDIRECT("'"&db.Sheets&"'!L4:L100");"DARURAT"))
    = TRANSPOSE({2\2\1\1\2\0})
    = {2;2;1;1;2;0}

    Perhatikan gambar berikut:

    MMULT.png

    Dari gambar di atas, dapat diketahui nama range tersebut berisi array:

    = {2;4;5;6;8;8}

    Demikian persiapan awalnya...

  7. Maaf sbelumnya, hehe..
    Ini master sekali mas @Caton
    Smoga saya jdi pinter stelah membaca pnjelasan dri mas @Caton ????

    Terimakasih banyak

  8. manweljs_

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

    mas @Caton

    saya tertarik dengan file D nih mas khususnya bagian "replace", bisa tolong dijelaskan mengapa bisa demikian?

    sebelumnya terima kasih (^_^)/ (maaf kalo bikin repot)

  9. Caton

    Agu 7 Terverifikasi Indonesia + 12.523 Poin

    Mbak @Fika56 ...

    Saya lanjutkan dahulu bahasannya ya. Selanjutnya untuk formula pada sel B4:

    =IF(ROWS($1:1)>db.Count;"";INDEX(INDIRECT("'"&INDEX(db.Sheets;MATCH(TRUE;db.Array>=ROWS($1:1);0))&"'!B4:L100");SMALL(IF((INDIRECT("'"&INDEX(db.Sheets;MATCH(TRUE;db.Array>=ROWS($1:1);0))&"'!F4:F100")="PUBLISHED")*(INDIRECT("'"&INDEX(db.Sheets;MATCH(TRUE;db.Array>=ROWS($1:1);0))&"'!L4:L100")="DARURAT");ROW(INDIRECT("1:"&ROWS($A$2:$F$10))));IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1);1+db.Array);ROWS($1:1)));COLUMNS($A:A)))

    Formula di atas merupakan array formula, jadi harus ditutup dengan kombinasi tombol CONTROL SHIFT ENTER. Formula tersebut jika diurai:

    [1]. Jika indeks baris lebih besar dari jumlah total data, maka kembalikan hasil kosong (Empty String):

    =IF(ROWS($1:1)>db.Count;"";

    [2]. Jika indeks baris lebih kecil atau sama dengan jumlah total data, maka ambil data pada masing-masing sheet pada range B4:L100, dimana nama sheet dan range data diambil berdasarkan perbandingan indeks baris dimana formula berada dengan array pada db.Array dan indeks baris data didapat dari hasil kalkulasi berdasarkan kriteria yang ditentukan.

    INDEX(INDIRECT("'"&INDEX(db.Sheets;MATCH(TRUE;db.Array>=ROWS($1:1);0))&"'!B4:L100");SMALL(IF((INDIRECT("'"&INDEX(db.Sheets;MATCH(TRUE;db.Array>=ROWS($1:1);0))&"'!F4:F100")="PUBLISHED")*(INDIRECT("'"&INDEX(db.Sheets;MATCH(TRUE;db.Array>=ROWS($1:1);0))&"'!L4:L100")="DARURAT");ROW(INDIRECT("1:"&ROWS($A$2:$F$10))));IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1);1+db.Array);ROWS($1:1)));COLUMNS($A:A))

    Jika mbak lihat, formula utamanya adalah dengan fungsi INDEX dimana fungsi INDEX memiliki sintaks sebagai berikut:

    INDEX(Array, Row_Num, [Col_Num])

    Maka, jika kita urai formulanya:

    — Parameter Array :

    INDIRECT("'"&INDEX(db.Sheets;MATCH(TRUE;db.Array>=ROWS($1:1);0))&"'!B4:L100")

    Pada formula tersebut, fungsi INDEX bertujuan untuk mengambil nama sheet yang sudah ditentukan pada db.Sheets. Contohnya pada sel B4:

    = INDEX(db.Sheets;MATCH(TRUE;db.Array>=ROWS($1:1);0))
    = INDEX(db.Sheets;MATCH(TRUE;{2;4;5;6;8;8}>=1;0))
    = INDEX(db.Sheets;MATCH(TRUE;{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE};0))
    = INDEX({"CR-PROD"\"CR-QO"\"CR-R&D"\"CR-ENG"\"CR-GUDANG"\"CR-OHSE"};1;0))
    = "CR-PROD"
    
    = INDIRECT("'"&INDEX(db.Sheets;MATCH(TRUE;db.Array>=ROWS($1:1);0))&"'!B4:L100")
    = INDIRECT("'"&"CR-PROD"&"'!B4:L100")
    = INDIRECT("'CR-PROD'!B4:L100")
    = 'CR-PROD'!B4:L100

    Contohnya lain pada sel B6:

    = INDEX(db.Sheets;MATCH(TRUE;db.Array>=ROWS($1:3);0))
    = INDEX(db.Sheets;MATCH(TRUE;{2;4;5;6;8;8}>=3;0))
    = INDEX(db.Sheets;MATCH(TRUE;{{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE}};0))
    = INDEX({"CR-PROD"\"CR-QO"\"CR-R&D"\"CR-ENG"\"CR-GUDANG"\"CR-OHSE"};2;0))
    = "CR-QO"
    
    = INDIRECT("'"&INDEX(db.Sheets;MATCH(TRUE;db.Array>=ROWS($1:3);0))&"'!B4:L100")
    = INDIRECT("'"&"CR-QO"&"'!B4:L100")
    = INDIRECT("'CR-QO'!B4:L100")
    = 'CR-QO'!B4:L100

    — Parameter Row_Num :

    SMALL(IF((INDIRECT("'"&INDEX(db.Sheets;MATCH(TRUE;db.Array>=ROWS($A$1:$A4);0))&"'!F4:F100")="PUBLISHED")*(INDIRECT("'"&INDEX(db.Sheets;MATCH(TRUE;db.Array>=ROWS($A$1:$A4);0))&"'!L4:L100")="DARURAT");ROW(INDIRECT("1:"&ROWS($A$2:$F$10))));IFERROR(1+ROWS($A$1:$A4)-LOOKUP(ROWS($A$1:$A4);1+db.Array);ROWS($A$1:$A4)))

    Formula di atas tujuannya adalah mengambil indeks baris data mulai dari yang terkecil secara berurutan apabila kondisi terpenuhi. Untuk membandingkan kondisi setiap kriteria, digunakan fungsi IF dimana jika data pada kolom F = "PUBLISHED" dan data pada kolom L = "DARURAT", maka hasilnya adalah indeks baris data yang sesuai untuk masing-masing sheet. Pada formula di atas range datanya adalah B4:L100, maka indeks baris pertama yang diperoleh adalah 2 (hasilnya ± terlihat pada file B, sheet KALKULASI, kolom G (mulai sel G3). Bedanya, pada sel G3 bernilai 5 dikarenakan indeks baris diambil secara aktual bukan berdasarkan indeks baris range B4:L100. Nilai tersebut jika dikonversikan ke indeks baris range akan menjadi 5 - 3 = 2)

    — Parameter Col_Num :

    COLUMNS($A:A)

    Ini hanya formula biasa yang akan menghasilkan indeks kolom dimana formula berada.

    Sudah ya mbak, segitu saja. Semoga tambah pinter Excel-nya. Saran saya, jangan gunakan formula ini untuk data dengan jumlah yang banyak. Mending gunakan alternatif VBA atau PQ.

    Demikian.

  10. Caton

    Agu 7 Terverifikasi Indonesia + 12.523 Poin

    Mas @manweljs_ ...

    ... saya tertarik dengan file D nih mas, bisa tolong dijelaskan mengapa bisa demikian? ...

    Coba dilakukan pendekatan sama file D-nya mas. Siapa tahu sama-sama ada ketertarikan dan ada kecocokan, khan bisa lanjut ... (^_^)/ =P :D

  11. manweljs_

    Agu 7 Terverifikasi + 5.718 Poin

    @Caton Coba dilakukan pendekatan sama file D-nya mas

    maaf mas, saya tidak paham dengan apa yang mas @Caton sampaikan. saya hanya membaca tulisan ini :

    ...Kalau untuk VBA, InsyaAllah bisa saya jelaskan karena baris scriptnya cuma sedikit (kalau mau) ....

    nah kebetulan saya mau. :)

  12. Caton

    Agu 8 Terverifikasi Indonesia + 12.523 Poin

    Mas @manweljs_ ...

    ... saya tidak paham dengan apa yang mas @Caton sampaikan ...

    Kalau begitu, mas tentunya paham kalau tawaran tersebut untuk mbak @Fika56 ... :) :D

  13. Mas nya pada ngelawak..
    Haha

  14. menyambung diskusi ini mas @Caton , saya msih blm paham benar penulisan kombinasi rumusnya.. maafkan saya, hehe..

    Apabila ada kasus seperti ini lagi namun dengan kriteria lain yaitu yg harus masuk pada sheet List Musnah adalah sudah naik revisi dan terminated.. itu bagaimana ya?
    berarti harus membuat name range lagi?

    Terimakasih.

  15. manweljs_

    Agu 12 Terverifikasi + 5.718 Poin

    mbak @Fika56

    saya msih blm paham benar penulisan kombinasi rumusnya

    mengapa tidak dicoba pakai file-D ? karena menurut mas @Caton file-D tersebut lebih bisa beliau jelaskan, sehingga asumsi saya mungkin akan lebih mudah untuk mbak @Fika56 memahaminya. :)

  16. Caton

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

    Mbak @Fika56 ...

    Tidak perlu minta maaf mbak, wajar saja bertanya. Formulanya juga memang agak rumit dipahami ... :)

    Apabila ada kasus seperti ini lagi namun dengan kriteria lain yaitu yg harus masuk pada sheet List Musnah adalah sudah naik revisi dan terminated.. itu bagaimana ya?

    Kriterianya bagaimana? Kalau sebelumnya khan:

    (KET. LOGBOOK = PUBLISHED) AND (STATUS = DARURAT)

    Nah kali ini bagaimana kondisinya? Apakah seperti berikut:

    ((KET. LOGBOOK = SUDAH NAIK REVISI) OR (KET. LOGBOOK = TERMINATED)) AND (STATUS = DARURAT)

    berarti harus membuat name range lagi?

    Betul ... Khan kriteria atau kondisinya sudah berbeda ... :) Hanya saja, yang perlu ditambahkan adalah nama range untuk menghitung jumlah data per sheet (db.Array) dan nama range untuk menentukan estimasi jumlah datanya (db.Count). Sedangkan nama range untuk daftar sheetnya tetap menggunakan db.Sheets saja.

    Pada file terlampir, nama range db.Array dan db.Count saya ubah menjadi db.Array.A dan db.Count.A untuk kasus pertama, dan db.Array.B dan db.Count.B untuk kasus kedua. Dan masing-masing nama range tersebut scope-nya saya buat menjadi lokal terhadap masing-masing Worksheet sehingga tidak saling tertukar.

    Catatan: Filenya pake XLSB ya mbak, biar gak terlalu gede dan cepat unggahannya. Dan, ini filenya kok agak-agak mirip dengan diskusi di sini ... (^_^)/

    Demikian, semoga sesuai.

 

atau Mendaftar untuk ikut berdiskusi!