Nomor Urut Hasil Filter Pivot Table

  1. 6 tahun lalu

    Dear Pak Caton,

    Bagaimana cara membuat nomor urut untuk hasil filter di pivot table ?

    Terima kasih

  2. Caton

    7 Jul 2018 Terverifikasi Indonesia + 20.101 Poin

    Pak @suarto priyandono ...

    Saya tidak tahu apakah Pivot Table memilki fitur tersebut, setidaknya pada Excel 2010 saya tidak menemukan fitur semacam itu. Entah pada versi Excel 2013~2016 ... (^_^)/

    Sejauh ini, yang saya tahu, paling tidak harus menggunakan kolom bantu untuk menyusun indeks nomor urutan yang diinginkan. Sehingga, saat terjadi perubahan kriteria (filter) pada Pivot Table-nya, nomor indeks urutan tetap sesuai. Tentunya formula untuk menyusun nomor indeks tersebut disesuaikan dengan kondisi kriteria data (filter) pada Pivot Table-nya. Sayangnya, bapak tidak melampirkan contoh sumber data dan Pivot Table yang sedang bapak kerjakan, sehingga saya tidak tahu bagaimana bentuk formula yang tepat dengan kondisi kriteria (filter) pada Pivot Table yang bapak kerjakan. Selain dengan kolom bantu, bisa juga menggunakan bantuan script VBA, atau menggabaungkan antara kolom bantu dengan VBA... :)

    Agar lebih paham dengan apa yang saya maksudkan, silahkan dipelajari contoh yang dapat saya berikan pada file terlampir.

    [1]. Pada sheet CONTOH 11, formula untuk membuat indeks nomor baris sudah disusun pada tabel sumber data (pada sheet DATA) kolom IDX11 atau kolom K).

    [2]. Sedangkan pada sheet CONTOH 21, pada dasarnya menggunakan kolom bantu juga yakni pada sheet DATA) kolom IDX21 dan kolom IDX22 — atau pada kolom L dan kolom M), hanya saja digunakan script VBA (macro) untuk me-refresh Pivot Table saat terjadi perubahan kriteria (sehingga data yang dibaca oleh Pivot Table tersebut tetap aktual).

    [3]. Untuk proses menggunakan script VBA secara keseluruhannya, tidak saya sertakan...

    Demikian yang bisa saya jelaskan. Silahkan bagi rekan-rekan lainnya yang ingin berbagi jika memiliki solusi berbeda atau yang lebih baik.

  3. Terima kasih untuk penjelasannya.

    Ada hal yang akan saya tanyakan lagi (file terlampir),
    Data awal ada pada "sheet master", data tersebut akan di filter berdasarkan kode kota, dan hasil filter tersebut akan berada di sheet baru, sesuai kotanya masing-masing (contoh hasil filter di sheet berwarna biru).
    Pada hasil filter terdapat pula judul atas dan kolom tanda tangan.
    Mohon dapat dibantu dengan rumusnya.
    Terima kasih banyak

  4. Caton

    14 Jul 2018 Terverifikasi Indonesia + 20.101 Poin

    Terlampir contoh formulasi dari saya (lihat sheet CONTOH). Acuan KODE KOTA ada pada Drop Down Validation List pada sel C2. Untuk mengubah kriteria acuan, pilih salah satu item pada daftar atau bisa juga langsung diketikkan pada sel tersebut.

    Dalam hal ini, formula pada range C13:L22 mengandalkan bantuan indeks baris pada kolom N. Kalau maunya tanpa kolom bantu, ubah notasi yang mengacu ke kolom N dengan formula pada kolom N pada baris terkait. Hanya saja, formulanya mungkin akan sedikit lebih panjang dan (mungkin) agak membingungkan untuk dipelajari. Sebagai gambaran:

    — Formula pada sel C13:

    =IF(ISNUMBER($N13);INDEX(MASTER.DATA;$N13;COLUMN()-COLUMN($B$1)+1);"")

    — Formula pada sel N13:

    =IF(ISNUMBER($B13);SUMPRODUCT(SMALL(((MASTER.KODE=$C$2)*ROW(INDIRECT("A1:A"&ROWS(MASTER.DATA))))+((MASTER.KODE<>$C$2)*(ROWS(MASTER.DATA)+10));$B13));"")

    — Jika formula pada sel N13 dimasukkan (digabungkan) ke dalam formula pada sel C13, maka formula pada sel C13 lebih kurang dapat berisi formula sebagai berikut:

    =IFERROR(INDEX(MASTER.DATA;IF(ISNUMBER($B13);SUMPRODUCT(SMALL(((MASTER.KODE=$C$2)*ROW(INDIRECT("A1:A"&ROWS(MASTER.DATA))))+((MASTER.KODE<>$C$2)*(ROWS(MASTER.DATA)+10));$B13));"");COLUMN()-COLUMN($B$1)+1);"")

    Silahkan dipelajari dan dimodifikasi sesuai kebutuhan. Demikian dan semoga bermanfaat.

  5. Dear Pak Caton...

    Untuk "sheet contoh", bagaimana formulanya kl yang data ditampilkan di sheet tersebut hanya yang nominal lemburnya "tidak sama dengan nol (<>0)"..

    Terima kasih

  6. Caton

    17 Jul 2018 Terverifikasi Indonesia + 20.101 Poin

    [1] Buat nama range dinamis MASTER.LEMBUR dengan formula:

    =OFFSET(MASTER!$J$4;1;0;COUNTA(MASTER!$K$5:$K$1000);1)

    Screenshoot001.png

    [2] Pada sheet CONTOH, sel B13, ubah formulanya menjadi:

    =IF(COUNTIFS(MASTER.KODE;$C$2;MASTER.LEMBUR;"<>0")>=ROW(A1);ROW(A1);"")

    Salin formula dari sel B13 tersebut ke sel-sel di bawahnya.

    Demikian semoga sesuai.

  7. Dear Pak Caton,

    Terlampir File yg sudah sy coba terapkan sesuai instruksi diatas, tapi masih belum jalan ?
    Data pegawai yang tidak memiliki lembur (kolom nominal lembur = 0) seharusnya tidak muncul

    Terima kasih sebelumnya untuk koreksinya

  8. Caton

    17 Jul 2018 Terverifikasi Indonesia + 20.101 Poin

    Oia pak... satu lagi yang tertinggal.... :D Pada sheet CONTOH, sel N13, ubah formulanya menjadi:

    =IF(ISNUMBER($B13);SUMPRODUCT(SMALL(((MASTER.KODE=$C$2)*(MASTER.LEMBUR<>0)*ROW(INDIRECT("A1:A"&ROWS(MASTER.DATA))))+(((MASTER.KODE<>$C$2)+(MASTER.LEMBUR=0))*9999);$B13));"")

    Salin formula dari sel N13 tersebut ke sel-sel di bawahnya.

    Demikian, semoga sesuai...

  9. Dear Pak Caton,

    sepertinya masih belum sesuai (file terlampir)

  10. Caton

    17 Jul 2018 Terverifikasi Indonesia + 20.101 Poin
    Di sunting 6 tahun lalu oleh Caton

    Pak Suarto....

    Pada sheet MASTER, kolom NOMINAL LEMBUR (kolom J) jangan dikosongkan dong... :D Contohnya pada sel J9, masukkan nilai 0 (nol) langsung. Atau kalau selnya dikosongkan begitu, ubah formula sel B13 sheet CONTOH menjadi:

    =IF(COUNTIFS(MASTER.KODE;$C$2;MASTER.LEMBUR;"<>0";MASTER.LEMBUR;"<>"&"")>=ROW(A1);ROW(A1);"")

    Begitu... ;)

  11. Ooh iya Pak.. Maaf saya yg salah..
    Skr sy mau coba terapkan ke data sy formulanya..

    Terima kasih banyak Pak

  12. Dear Pak Caton,

    Untuk formula-formula dibawah ini cara bacanya gimana yah .. saya masih gagal paham

    =IFERROR(VLOOKUP($C$2;REF!$A$2:$B$15;2;0);"—")

    =IF(ISNUMBER($B18);SUMPRODUCT(SMALL(((MASTER.KODE=$C$2)*(MASTER.LEMBUR<>0)*ROW(INDIRECT("A1:A"&ROWS(MASTER.DATA))))+(((MASTER.KODE<>$C$2)+(MASTER.LEMBUR=0))*9999);$B18));"")

    =IF(ISNUMBER($N13);INDEX(MASTER.DATA;$N13;COLUMN()-COLUMN($B$1)+1);"")

    Terima kasih

  13. Caton

    19 Jul 2018 Terverifikasi Indonesia + 20.101 Poin
    Di sunting 6 tahun lalu oleh Caton

    Pak Suarto ...

    Untuk formula kedua, saya ambil contoh dari data awal diskusi, lebih kurang seperti berikut:

    Screenshoot001A.png

    dimana formula yang digunakan pada sel N13 adalah:

    =IF(ISNUMBER($B13);SUMPRODUCT(SMALL(((MASTER.KODE=$C$2)*(MASTER.LEMBUR<>0)*ROW(INDIRECT("A1:A"&ROWS(MASTER.DATA))))+(((MASTER.KODE<>$C$2)+(MASTER.LEMBUR=0))*9999);$B13));"")

    Saya pribadi agak sulit menarasikan formula tersebut. Namun inti dari formula tersebut secara logika lebih kurang dapat dijabarkan sebagai berikut:

    1 — Jika tipe nilai pada sel B13 adalah numerik (angka atau bilangan), maka:

    [A] Susun Indeks Baris Data. Kriteria utamanya adalah untuk setiap nilai pada range MASTER.KODE (kolom KODE KOTA sheet MASTER) sama dengan nilai KODE (pada sel C2 sheet CONTOH, misalkan DPSGA) dan nilai pada range MASTER.LEMBUR (kolom NOMINAL LEMBUR sheet MASTER) tidak sama dengan 0 (nol). Formula yang digunakan:

    ((MASTER.KODE=$C$2)*(MASTER.LEMBUR<>0)*ROW(INDIRECT("A1:A"&ROWS(MASTER.DATA))))

    Dari formula tersebut, untuk setiap baris data pada range MASTER.KODE yang sama dengan nilai KODE (DPSGA) akan tersusun hasil masing-masing seperti berikut:

    Notasi (MASTER.KODE=$C$2) akan menghasilkan array:

    {FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

    Notasi (MASTER.LEMBUR<>0) akan menghasilkan array:

    {FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

    Notasi ROW(INDIRECT("A1:A"&ROWS(MASTER.DATA))) akan menghasilkan array:

    {1;2;3;4;5;6;7;8;9;10;11;12}

    Oleh karena setiap notasi dihubungkan dengan operator perkalian (tanda *), dimana nilai TRUE akan menjadi 1 dan nilai FALSE akan menjadi 0, maka ketiga array tersebut akan terkalkulasi menjadi:

    = {0;0;0;1;1;1;1;0;0;0;0;0} * {0;0;0;1;1;1;1;0;0;0;0;0} * {1;2;3;4;5;6;7;8;9;10;11;12}
    = {0;0;0;1;1;1;1;0;0;0;0;0} * {1;2;3;4;5;6;7;8;9;10;11;12}
    = {0;0;0;4;5;6;7;0;0;0;0;0}

    Dari hasil tersebut di atas, hasil yang ingin diambil adalah nilai 4, 5, 6 dan 7. Masalahnya, bagaimana mengambil indeks baris tersebut dari deret array {0;0;0;4;5;6;7;0;0;0;0;0}? Karena nilai terkecil pada deret nilai tersebut adalah 0 (nol).

    Untuk mengatasi masalah ini, maka yang harus dilakukan adalah mengubah nilai 0 tersebut menjadi nilai lebih besar dari batas indeks baris data. Intinya, formulanya harus berupa kebalikan dari logika sebenarnya. Dengan formula:

    (((MASTER.KODE<>$C$2)+(MASTER.LEMBUR=0))*9999)

    maka disusun array indeks baris yang nilai pada range MASTER.KODE tidak sama dengan nilai KODE pada sheet CONTOH (DPSGA) atau nilai pada range MASTER.LEMBUR sama dengan 0 (nol). Dari formula tersebut, maka tersusun hasil masing-masing:

    Notasi (MASTER.KODE<>$C$2) akan menghasilkan array:

    {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE}

    Notasi (MASTER.LEMBUR=0) akan menghasilkan array:

    {FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE}

    Oleh karena setiap notasi dihubungkan dengan operator penjumlahan (tanda +) kemudian baru dihubungkan dengan operator perkalian, dimana nilai TRUE akan menjadi 1 dan nilai FALSE akan menjadi 0, maka kedua array tersebut akan terkalkulasi menjadi:

    = ({1;1;1;0;0;0;0;1;1;1;1;1} + {0;1;1;0;0;0;0;1;1;1;1;1}) * 9999
    = ({1;2;2;0;0;0;0;2;2;2;2;2}) * 9999
    = {9999;19998;19998;0;0;0;0;19998;19998;19998;19998;19998}

    Pada akhirnya, kedua formula yang sudah dibahas di atas jika digabungkan seperti berikut:

    ((MASTER.KODE=$C$2)*(MASTER.LEMBUR<>0)*ROW(INDIRECT("A1:A"&ROWS(MASTER.DATA))))+(((MASTER.KODE<>$C$2)+(MASTER.LEMBUR=0))*9999)

    akan menghasilkan array berupa:

    = {0;0;0;4;5;6;7;0;0;0;0;0} + {9999;19998;19998;0;0;0;0;19998;19998;19998;19998;19998}
    = {9999;19998;19998;4;5;6;7;19998;19998;19998;19998;19998}

    [B] Ambil Indeks Baris Data. Dari hasil formula di atas, sudah berhasil disusun indeks baris berupa array yang akan diambil nilai terkecilnya secara berurutan. Dalam hal ini, yang digunakan adalah fungsi SMALL karena fungsi ini dapat mengambil nilai terkecil secara berurutan dari sebuah array. Indeks nilai terkecil dari deret array yang sudah disusun tersebut ditentukan oleh nilai pada sel B13. Jika nilai pada sel B13 adalah 1, maka formula dengan fungsi SMALL yang kita gunakan akan menghasilkan:

    = SMALL({9999;19998;19998;4;5;6;7;19998;19998;19998;19998;19998};$B13)
    = SMALL({9999;19998;19998;4;5;6;7;19998;19998;19998;19998;19998};1)
    = 4

    Dengan demikian, jika formula berada pada sel N14, maka indeks nilai terkecil dari fungsi SMALL akan dirujuk ke sel B14. Jika tipe nilai sel B14 adalah numerik (misalkan angka 2), maka formula dengan fungsi SMALL tersebut akan menghasilkan:

    = SMALL({9999;19998;19998;4;5;6;7;19998;19998;19998;19998;19998};$B14)
    = SMALL({9999;19998;19998;4;5;6;7;19998;19998;19998;19998;19998};2)
    = 5

    Demikian seterusnya.

    [C]. Hindari penggunaan Array Formula. Untuk menghindari proses kalkulasi dengan menggunakan teknik Array Formula, maka digunakan fungsi SUMPRODUCT. Oleh karena pada formula di atas fungsi SMALL akan memanfaatkan array sebagai bagian dari data yang akan diproses, maka kita dapat memanfaatkan fungsi SUMPRODUCT untuk memproses notasi formula:

    ((MASTER.KODE=$C$2)*(MASTER.LEMBUR<>0)*ROW(INDIRECT("A1:A"&ROWS(MASTER.DATA))))+(((MASTER.KODE<>$C$2)+(MASTER.LEMBUR=0))*9999)

    menjadi sebuah array dan menyisipkankan ke dalam fungsi SMALL. Perlu dipahami bahwa fungsi SUMPRODUCT tidak dapat digunakan dengan fungsi-fungsi yang tidak mendukung data berupa array seperti fungsi IF.

    2 — Jika tipe nilai pada sel B13 adalah bukan numerik (teks, alpanumerik atau sel kosong), maka nilai yang dihasilkan adalah sel kosong.

    CATATAN
    Formula di atas pada dasarnya juga dapat diselesaikan dengan memanfaatkan Array Formula yakni dengan formula di bawah ini (pada sel N13):

    =IF(ISNUMBER($B13);SMALL(IF((MASTER.KODE=$C$2)*(MASTER.LEMBUR<>0);ROW(MASTER.DATA)-4;9999);ROW(A1));"")

    Pastikan menutup formula dengan menekan tombol CONTROL + SHIFT + ENTER secara bersamaan. Salin formula pada sel N13 tersebut ke baris di bawahnya.

    Demikian penjelasan untuk formula yang kedua. Semoga bermanfaat... :)

  14. Caton

    19 Jul 2018 Terverifikasi Indonesia + 20.101 Poin

    Untuk formula berikut:

    =IFERROR(VLOOKUP($C$2;REF!$A$2:$B$15;2;0);"—")

    dapat dibaca:

    Jika formula VLOOKUP($C$2;REF!$A$2:$B$15;2;0) menghasilkan kesalahan (seperti #VALUE!, #N/A, #REF! dan sebagainya), maka hasil akhir atau nilai yang ditampilkan adalah karakter .

    Formula tersebut sama saja seperti formula berikut:

    =IF(ISERROR(VLOOKUP($C$2;REF!$A$2:$B$15;2;0));"—";VLOOKUP($C$2;REF!$A$2:$B$15;2;0))

    Dan untuk formula berikut:

    =IF(ISNUMBER($N13);INDEX(MASTER.DATA;$N13;COLUMN()-COLUMN($B$1)+1);"")

    dapat dibaca:

    Jika tipe nilai pada sel N13 merupakan numerik (angka/bilangan), maka ambil datanya pada range MASTER.DATA dengan indeks baris sesuai nilai pada sel N13 dan indeks kolom sesuai hasil kalkulasi formula COLUMN()-COLUMN($B$1)+1. Jika tipe nilai pada sel N13 BUKAN merupakan numerik (bisa teks, alphanumerik atau sel kosong), maka hasil akhir yang ditampilkan adalah sel kosong.

    Pada formula tersebut, jika formula berada pada sel C13, maka:

    = COLUMN() - COLUMN($B$1) + 1 = 3 - 2 + 1 = 2 

    Oleh karena fungsi COLUMN() akan menghasilkan nilai indeks kolom dimana fungsi tersebut digunakan, maka jika fungsi tersebut digunakan pada sel C13, hasilnya adalah 3 oleh karena indeks kolom C merupakan 3 (kolom A = 1, kolom B = 2, kolom C = 3 dan seterusnya).

    Jadi, jika formula tersebut berada pada sel C13 dan nilai N13 = 1, maka formula dengan fungsi INDEX tersebut dapat disederhanakan menjadi:

    = INDEX(MASTER.DATA;$N13;COLUMN()-COLUMN($B$1)+1)
    = INDEX(MASTER.DATA;1;3-2+1)
    = INDEX(MASTER.DATA;1;2)

    Pada formula tersebut, jika nama range MASTER.DATA merujuk ke range A5:K16 pada sheet MASTER, maka formula tadi akan mengambil hasil akhirnya berupa nilai dari sel B5 pada sheet MASTER.

    Demikian.

  15. Tks Pak Caton.. akan saya coba pelajari.
    Smoga tidak gagal paham lagi walau sepertinya terlihat cukup rumit.

  16. Dear Pak Caton,

    Mau tanya untuk membuat format tanggal versi bahasa indonesia (pada "Sheet BKS", Cell B6 dan B90).
    Padahal di cell referensinya (Sheet REFF Cell F2) sdh saya set versi Bhs Indonesia.

    Dan untuk rumus pada judul tabel yang saya buat sudah benar belum ya ? (sheet BKS, cell B5 dan B6)
    Terima kasih

  17. Dear Pak Caton,

    Koreksi sedikit, maksud sy sesuai pertanyaan diatas, bukan format tanggal, tapi format bulan.
    Terima kasih

  18. Caton

    22 Jul 2018 Terverifikasi Indonesia + 20.101 Poin

    Pak Suarto...

    Formulanya sudah tepat, tinggal tambahkan saja notasi [$-421] jika ingin memaksa teksnya mengikuti format tanggal (termasuk nama bulan) Indonesia. Jadinya seperti:

    B6  =REF!D2&" "&TEXT(REF!F2;"[$-421]mmmm yyyy")
    B90 =IF(LEN($D$9)>1;$D$9&",  "&TEXT(REF!F2;"[$-421]mmmm yyyy");"")

    Demikian.

  19. Dear Pak Caton,

    Terima kasih Pak.. Hasil sudah sesuai

  20. Dear Pak @Caton,

    Saat ini sy sedang mereview kembali diskusi2 kita pada sebelumnya, agar sy bisa benar2 paham.

    Melanjutkan diskusi 8 mgg lalu, pada sheet contoh cellB13 terdapat rumus :

    =IF(COUNTIFS(MASTER.KODE;$C$2;MASTER.LEMBUR;"<>0")>=ROW(A1);ROW(A1);"")


    Mohon dapat dibantu menarasikan-nya ?

    Cara meng-quote "contoh tabel" agak bisa masuk ke kolom komentar ini gmn ya ????

  21. Newer ›
 

atau Mendaftar untuk ikut berdiskusi!