RUMUS KOMBINASI "INDEX.."

  1. 8 bulan yang lalu

    Selamat malam,
    saya mau bertanya pada mas @Caton nih. saya ada permasalahan, saya menggunakan rumus index yang akan saya tampilkan di excel berikut.. nah pertanyaannya bagaimana cara menyembunyikan nilai pada kolom yang tidak tertarik datanya (alias #NUM!) .. saya sudah menggunakan conditional formating namun masih gagal. apakah ada yang salah?
    dan apakah mas @Caton ada solusi yang baik dan penjelasan untuk data excel saya.
    terimakasih banyak.. mohon dibantu.

    Terimakasih atas jawabannya.
    Berikut ya mas @Caton filenya. dan di sheet Technical Service malah tidak ketarik rumusnya.. dan di sheet "CPRO" nilai #NUM nya masih gagal. no urut dan border nya masih terlihat.
    Mohon bantuannya.
    Terimakasih.

  2. Caton

    21 Mar 2018 Terverifikasi Indonesia + 12.569 Poin

    Mbak @Fika56 ...

    ... saya menggunakan rumus index yang akan saya tampilkan di excel berikut ...

    Bisa dilampirkan file contoh yang sudah dikerjakan?

    ... bagaimana cara menyembunyikan nilai pada kolom yang tidak tertarik datanya (alias #NUM!) ...

    Apakah dimungkinkan menggunakan fungsi IFERROR di dalam formula yang digunakan (jika Excel yang digunakan adalah versi 2007+)? Kalaupun ingin menggunakan Conditional Formatting, kita bisa menyembunyikan kesalahan tersebut dengan menyamarkan warna font (teks) selaras dengan warna latar selnya, dengan menggunakan Conditional Formula ISERROR (meski bukan merupakan opsi terbaik, mungkin dapat mencukupi), gambarannya:

    Hide Error.png

    Demikian.

  3. Fika56

    22 Mar 2018 Jawaban Terpilih + 175 Poin

    Terimakasih atas jawabannya.
    Berikut ya mas @Caton filenya. dan di sheet Technical Service malah tidak ketarik rumusnya.. dan di sheet "CPRO" nilai #NUM nya masih gagal. no urut dan border nya masih terlihat.
    Mohon bantuannya.
    Terimakasih.

  4. Caton

    22 Mar 2018 Terverifikasi Indonesia + 12.569 Poin

    Mbak @Fika56 ...

    Array Formula memang memiliki beberapa kelebihannya. Namun, saat berurusan dengan data yang sangat banyak, Array Formula kadang kala malah memberatkan. Pada kasus-kasus seperti ini, penggunaan kolom bantu pada bisa dijadikan solusi dengan pertimbangan lebih mudah menelusuri formula yang digunakan dan tidak terlalu memberatkan proses.

    Terlampir modifikasi dari formula yang sebelumnya mbak gunakan. Dalam hal ini saya coba gunakan kolom bantu untuk menggantikan Array Formula pada sheet CPRO dan sheet Technical Service. Untuk mengurangi rujukan langsung, saya gunakan Named Range sebagai pintasan rujukan range yang digunakan.

    Demikian, semoga sesuai dan dapat diaplikasikan.

  5. Terimakasih atas bantuannya dan solusi nya mas @Caton ..

    Lalu saya ingin bertanya tentang penjelasan rumus berikut :
    =INDEX('MASTERLIST PT'!$A$5:$A$1160,SMALL(IF(('MASTERLIST PT'!$M$5:$CX$1160=CPRO!$E$3)*('MASTERLIST PT'!$F$5:$F$1160="published"),ROW($1:$1152)),ROW()-8))

    1. Mengapa harus menggunakan kombinasi SMALL?
    2. Dan untuk bagian ROW($1:$1152)),ROW()-8)), apakah nilai 1:1152 dan nilai -8 bisa terserah?

    Terimakasih.

  6. Caton

    23 Mar 2018 Terverifikasi Indonesia + 12.569 Poin
    Di sunting 8 bulan yang lalu oleh Caton

    Mbak @Fika56...

    Pada formula:

    SMALL(IF(('MASTERLIST PT'!$M$5:$CX$1160=CPRO!$E$3)*('MASTERLIST PT'!$F$5:$F$1160="published"),
    ROW($1:$1152)),ROW()-8)

    fungsi IF pada dasarnya akan menghasilkan indeks baris (Numeric Value) jika benar, jika nilai sel pada range M5:CX1160 sama dengan "CPRO" DAN nilai sel pada range F5:F1160 sama dengan "Published". Selain itu, hasilnya adalah nilai FALSE (Logic Value) jika kondisi tidak terpenuhi. Jika diasumsikan nilai yang dikembalikan oleh fungsi IF pada Array Formula tersebut adalah:

    {FALSE, FALSE, 3, FALSE, FALSE, FALSE, 7, FALSE, FALSE, FALSE}

    maka fungsi SMALL dengan argumen posisi ke-1 akan mengembalikan nilai 3. Karena nilai FALSE tidak dianggap sebagai Numeric Value, maka setiap akan menghasilkan nilai #NUM!. Jika array di atas direpresentasikan ke dalam argumen array dari fungsi SMALL, maka diasumsikan akan terlihat lebih kurang seperti berikut:

    {3, 7, #NUM!, #NUM!, #NUM!, #NUM!, #NUM!, #NUM!, #NUM!, #NUM!}

    Kemudian jika array tersebut dimasukkan ke dalam fungsi INDEX, ilustrasinya seperti berikut:

    =INDEX(A11:A20, SMALL({FALSE,FALSE,3,FALSE,FALSE,FALSE,7,FALSE,FALSE,FALSE},ROW()-10))
    =INDEX(A11:A20, SMALL({3,7,#NUM!,#NUM!,#NUM!,#NUM!,#NUM!,#NUM!,#NUM!,#NUM!},ROW()-10))

    maka pada baris ke-3 (misalkan pada sel B13), fungsi SMALL di atas akan mengembalikan kesalahan berupa #NUM!, karena deretan nilai array dari fungsi SMALL tersebut hanya memiliki 2 elemen Numeric Value, sedangkan elemen ke-3 dan seterusnya berupa Error Value seperti digambarkan di atas. Fungsi SMALL juga akan mengembalikan nilai #NUM! jika argumen posisi indeks array melebih batas array yang diproses, contohnya:

    = SMALL({5, 6, 7}, 8) = #NUM!

    Penjelasan tersebut di atas juga untuk menjawab pertanyaan mbak mengapa data tidak dapat ditarik dan menghasilkan kesalahan #NUM!. Lalu:

    ... Mengapa harus menggunakan kombinasi SMALL? ...

    Gambarannya dapat mbak lihat pada file yang saya lampirkan, yakni pada sheet MASTERLIST PT, kolom EA dan kolom EC. Pada formula yang ditanyakan, bagian formula:

    IF(('MASTERLIST PT'!$M$5:$CX$1160=CPRO!$E$3)*('MASTERLIST PT'!$F$5:$F$1160="published"),ROW($1:$1152))

    representasinya adalah pada kolom EA. Jika pada formula di atas akan menampilkan nilai berupa indeks baris (ROW($1:$1152)) dan atau FALSE dalam bentuk array, maka formula yang saya gunakan pada kolom EA akan menghasilkan nilai berupa indeks baris dan atau nilai kosong yang langsung terisi ke dalam masing-masing sel pada setiap baris formula. Seperti yang dapat mbak lihat, baris awal (sel EA5) dan beberapa baris berikutnya hanya berupa sel kosong (meski ada formulanya). Pada Array Formula yang mbak gunakan sebelumnya, sel kosong tersebut sama dengan nilai FALSE yang dihasilkan oleh Array Formula tersebut.

    Notasi ROW($1:$1152) merupakan argumen TRUE PART dari fungsi IF jika kondisi:

    ('MASTERLIST PT'!$M$5:$CX$1160=CPRO!$E$3)*('MASTERLIST PT'!$F$5:$F$1160="published")

    terpenuhi (kondisi benar atau TRUE). Tujuannya untuk menghasilkan indeks baris dimana kondisi tersebut terpenuhi. Oleh karena tidak semua nilai yang dihasilkan berupa Numeric Value, maka perlu diproses kembali untuk menghasilkan urutan indeks barisnya saja. Untuk itu fungsi SMALL digunakan. Jika diasumsikan nilai yang dihasilkan fungsi IF adalah sebagai berikut:

    {FALSE, FALSE, 3, FALSE, FALSE, FALSE, 7, FALSE, FALSE, FALSE}

    tanpa fungsi SMALL, penggunaan fungsi INDEX secara langsung terhadap array diatas akan menghasilkan nilai yang tidak tepat, karena:

    =INDEX($A$11:$A$20, INDEX({FALSE,FALSE,3,FALSE,FALSE,FALSE,7,FALSE,FALSE,FALSE}, ROW(A1)))

    pada formula di atas, nilai pertama dari array {FALSE,FALSE,3,FALSE,FALSE,FALSE,7,FALSE,FALSE,FALSE} adalah FALSE, atau sama dengan 0 (nol). Alhasil, jika formula tersebut ada pada sel B11, maka nilai pada sel A11 yang akan dikembalikan oleh fungsi INDEX tersebut. Pada file yang saya lampirkan, nilai pertama muncul pada baris ke-375, sehingga jika nilai pada kolom EA tidak disortir terlebih dahulu, maka mulai baris pertama formula (baris ke-9 pada sheet CPRO) akan menghasilkan kesalahan #N/A, atau sel kosong atau nilai lainnya yang tidak sesuai. Tentunya bukan demikian yang diinginkan, bukan?

    Oleh karena itu, fungsi SMALL pada kolom EC bertujuan menyusun kembali indek baris yang dihasilkan pada kolom EA mulai dari yang terkecil (ascending). Jika pada Array Formula yang digunakan sebelumnya, fungsi SMALL akan menyusun indeks baris hasil dari fungsi IF. Dengan tersusunnya indeks baris yang dihasilkan pada kolom EA (atau dari fungsi IF pada Array Formula) tersebut, fungsi INDEX pada sheet CPRO dapat langsung merujuk ke baris dimana data ditemukan, misalkan baris ke-375, baris ke-379, baris ke-381 dan seterusnya berdasarkan deretan nilai pada pada kolom EC atau array dari fungsi SMALL berdasarkan posisi ke-n yang dihasilkan oleh notasi:

    ROW()-8

    ... Dan untuk bagian ROW($1:$1152)),ROW()-8)), apakah nilai 1:1152 dan nilai -8 bisa terserah? ...

    Sekali lagi, notasi ROW($1:$1152) merupakan indeks baris yang dihasilkan fungsi IF jika kondisinya terpenuhi. Biasanya, pada Array Formula, range indeks baris tersebut selaras dengan jumlah baris yng diuji. Misalkan saja Array Formula berikut:

    =INDEX(IF(ISNUMBER($A$1:$A$10),$A$1:$C$5,FALSE),ROW(A1))

    Jika formula tersebut berada pada kolom B, maka pada baris ke-6, jika sel A6 merupakan angka, maka fungsi IF akan menghasilkan kondisi TRUE. Namun, karena range nilai pada argumen TRUE PART hanya ada 5 baris, fungsi IF tidak menemukan nilai yang akan dikembalikan. Akibatnya, fungsi IF akan mengembalikan kesalahan #N/A karena nilai yang akan dikembalikan tidak tersedia (dalam hal ini, elemen array yang kurang akan diisi dengan nilai kesalahan #N/A). Demikian pula jika range nilai pada argumen TRUE PART melebihi baris range yang diuji (misalkan range $A$1:$C$15), kelebihan elemen akan diisi dengan nilai kesalahan #N/A. Jika ukuran range atau array yang akan dikembalikan pada argumen TRUE PART atau FALSE PART sama dengan jumlah baris array yang diuji oleh fungsi IF, namun berada pada range berbeda, misalkan range $A$21:$C$30, maka hal ini bisa saja dilakukan. Sedangkan untuk notasi:

    ROW()-8

    memiliki tujuan khusus. Oleh karena formula pada sheet CPRO dimulai dari baris ke-9, maka notasi ROW()-8 akan mengembalikan nilai 1 dikarenakan jika ROW digunakan tanpa argumen (yakni ROW()), maka fungsi ROW akan mengembalikan nilai baris dimana fungsi tersebut digunakan. Oleh karena jika fungsi ROW digunakan pada baris ke-9, maka notasi ROW() akan mengembalikan nilai 9. Ilustrasinya:

    A5 = ROW() - 4 = 5 - 4 = 1
    A6 = ROW() - 4 = 6 - 4 = 2
    A7 = ROW() - 4 = 7 - 4 = 3

    dan seterusnya. Kadangkala, notasi tersebut juga digunakan seperti berikut:

    = ROW() - ROW($A$4)

    dimana notasi $A$4 merupakan 1 baris sebelum formula tersebut digunakan (dalam contoh tersebut, maka formula ada pada sel A5). Lalu bagaimana jika nilai pengurangnya diisi sembarang? Misalkan ROW() - 10? Sah-sah saja. Namun, apakah hasilnya akan sesuai...?! Karena, misalkan pada sheet CPRO sel B9 ingin diambil indeks baris yang ada pada kolom EC sheet MASTERLIST PT, penggunaan formula ROW() - 10 akan menghasilkan kesalahan oleh karena tidak terdapat indeks -1 (ROW() - 10 = 9 - 10 = -1).

    Demikian penjelasan yang dapat saya berikan. Semoga tidak membuat bingung dan tentunya semoga bermanfaat... ;)

 

atau Mendaftar untuk ikut berdiskusi!