Menghitung Jumlah Customer

[ayuningtyas mendrofa]

Dear Master,

Mohon bantuannya bagaimana cara untuk menghitung total customer dari setiap sales. Saya sudah pivot tetapi terjadi pengulangan.

Contoh Kode sales 17BNF01 mempunyai 1 customer grosir dan 1 customer retail tetapi setelah di pivot hasilnya malah menjadi banyak.

Mohon bantuannya dari para Master.

Terima kasih,

Ayu

[Mr. Kid]

Hai Ayu....

Kasus ini ingin melakukan distinct count, yaitu menghitung cacah item yang berbeda. Jadi jika sebuah item ada banyak records, akan dihitung 1.

Excel Pivot Table dengan data source yang tidak berasal dari data model (Excel Power Pivot) tidak bisa menampilkan distinct count

Untuk Excel 2010 ke atas, sebaiknya meng-install Excel Add-in Excel Power Pivot. Free, silakan download dari Microsoft.

Pada Excel 2013 ke atas, add-in ini sudah disertakan bersama installer ms office.

Pada Excel 2016 ke atas, add-in ini sudah di-install saat meng-install ms office, tetapi belum diaktifkan.

Jadi, tergantung versi Excel yang digunakan, dimulai dari download -> install -> aktifkan -> digunakan.

Jika sudah di-install, berikut cara meng-aktifkannya :

1. ke menu File -> Options -> Add-ins -> lihat daftar add-ins disisi kanan -> pastikan ada item Microsoft Power Pivot for Excel atau yang ada bunyinya Power Pivot gitulah

2. lihat sisi bawah, ada combo box Manage -> pilih COM add-in -> klik Go -> muncul window -> pastikan item Microsoft Power Pivot for Excel tadi tercentang -> klik OK

3. pada tahap ini, di menu akan muncul menu baru bernama Power Pivot. Jika tidak muncul, ke menu File -> options -> Customize ribbon

    -> lihat sisi kanan, di area yang kanan (Main tabs) -> pastikan item Power Pivot tercentang -> OK

Power pivot hanya menerima object Excel Table (bukan sekadar range yang bentuknya tersusun data yang vertikal berkaidah tabel database) atau dari sumber data di luar Excel.

Cara membuat suatu area data (tabel di suatu range) menjadi Excel Table :

1. cek area data, pastikan header hanya di 1 baris excel dan tidak ada kolom kosong di baris header, nama header adalah unique

2. blok area tabel di suatu range, mulai dari header sampai baris terakhir yang terisi data

3. ke menu Home -> lihat grup menu Styles -> klik tombol Excel Table -> pilih sebuah styles apa saja (bisa diganti kemudian) -> muncul window -> centang has header -> ok

4. klik cells dalam Excel table yang baru saja terbentuk -> ke menu Table tools Design 

    -> lihat item menu paling kiri Table Name : beri nama tanpa spasi dimulai huruf (direkomendasikan, minimal 5 karakter) -> tekan Enter

Cara membuat Excel Table diangkut ke data model (Power Pivot) lalu ditampilkan sebagai Excel Pivot Table seperti biasanya :

1. klik cells dalam Excel table -> menu Table tools Design -> lihat paling kiri (dekat Table Name : ), dikanannya ada item Summarize with pivot table -> klik item menu Summarize with pivot table

    -> muncul window : 

        bagian Data Source atau Table/Range (biasanya sudah terisi nama Excel Table yang aktif tadi) 

        bagian Choose where : sila pilih dimana akan diletakkan si pivot table

        bagian Choose whether you blabla multiple tables : centang item Add this data to the data model (atau yang mirip begitulah)

        tekan OK -> muncul pivot cache (frame nya Excel Pivot Table)

2. atur layout pivot table bagian Page Filter, Rows Field, Columns Field seperti biasa sesuai kebutuhan

3. pasang kolom yang akan disummary (misal pada kasus ini untuk distinct count kode customer, maka letakkan field Kode Customer ke area Values)

4. untuk melakukan distinct count, klik field di area values yang akan di-distinct count (kasus ini adalah Kode Customer yang ada di area values) 

    -> pilih  Value Field Settings -> klik tab Summarize Values By -> pilih Distinct Count (ada di paling bawah, scrolldown saja dan pilih) -> tekan OK

Selamat mencoba

Regards,

Kid

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to milis-belajar-e...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOdApwwXLoo%3DRU6%3DaXoqS9RSjsQKgrzOSwsnwBEFGj7JV2mLAA%40mail.gmail.com.

[ayuningtyas mendrofa]

Hi Mr. Kid,

Thank you for your suggestion, saya akan coba seperti yang di sarankan.

Anyway, saya lampirkan kembali case yg sudah saya sederhanakan mungkin dari Mr. Kid atau dari rekan yang lain ada alternatif solusi.

Thanks a lot and regards,

Ayu

To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPuN4D3WMAwyqefYPyGbJ6TRCiJm%2B4actqYzfVd-SOjh%3Dw%40mail.gmail.com.

[Mr. Kid]

Hai Ayu,

Alternatif solusi ya....

1. Pakai VBA 

    > cara ini super duper tidak direkomendasikan untuk kasus seperti ini, apalagi dijaman now

    > jadi, cara ini gak usah dibahas ya... nanti biar dibahas yang masih suka VBA untuk segala hal (bukan VBA untuk jadi tools diwaktu yang tepat)

    > lagipula, untuk yang belum bisa VBA, kasus seperti ini pakai VBA bakal menghabiskan masa muda...

2. Array formula

    > cara ini super tidak direkomendasikan untuk kasus yang mengkomputasi langsung ke raw data (sumber data awal berisi banyak baris tanpa diawali transformasi data), apalagi di jaman now

    > cara ini dituntut untuk punya tabel unique berisi Kode Sales dan Grosir, lalu array formula unique count bisa dipakai

    > array formula tentang unique list dan unique count bisa dilihat disini. (jangan lupa beli obat pusing agak banyakan dulu ya)

    > untuk sisi menampilkan gabungan daftar Customer, bisa lihat caranya di alternatif berikut, lalu diterapkan dicara ini.

    > INGAT ya... kalau pakai cara ini, jangan sekali-kali suatu saat nanti bilang Excel-nya lemot, karena yang lemot bukan Excel-nya, tapi yang memutuskan pakai cara ini

3. Formula dikolom bantu lalu ditampilkan dengan Excel Pivot Table

    > cara ini sangat tidak direkomendasikan untuk jaman now

    > cara ini sangat ribet jika ada penambahan baris data, karena harus mengubah area range yang dirujuk formula, meng-copy formula ke baris berikutnya, sort ulang, dan sebagainya

    > ketika pivot table menggunakan seluruh baris excel di kolom-kolom data sebagai sumber, akan memunculkan item blank yang bisa bikin puyeng saat mengatur filter pivot table

       tapi, jika dirujuk langsung ke area data yang ada saja, akan ribet menggonta ganti pivot table data source setiap kali baris data berubah jumlah barisnya

    > btw, bolehlah dipakai, asal INGAT, jangan sekali-kali suatu saat nanti bilang Excel-nya lemot ya... karena banyak kolom bantu berformula dan jumlah baris data menjadi pertimbangan

    > biar pada cepet pinter, formulanya dibuat sendiri ya....

4. Pakai Excel Table untuk mengurangi beban ribet dari cara 3

    > cara ini kurang direkomendasikan di jaman now

    > terlalu banyak kolom bantu berformula yang perlu dipelihara dan berpotensi membebani kerja Excel (beresiko suatu saat akan bilang Excel-nya lemot)

    > cara membuat Excel Table :

       a. blok area data (termasuk header) -> menu Home -> klik Excel Table (lihat grup menu Styles) -> pilih salah satu style -> muncul window -> centang Has header -> OK

       b. beri nama table (ada di menu Table tools design item menu paling kiri bernama Table Name)

    > Keuntungan Excel Table antara lain :

       a. buat formula cukup di 1 cells, Excel table akan meng-copy-kan ke semua baris sepanjang masa setiap kali ada penambahan baris baru

       b. bisa dipakai sebagai sumber data untuk Excel Power Query, Excel Power Pivot, Excel Pivot Table yang dinamis

           * jadi, Excel Pivot Table yang data source-nya adalah Excel Table, akan otomatis dinamis.

             tambah baris data tidak perlu ubah data source Excel Pivot Table, cukup lakukan refresh saja 

           * ini berlaku juga di Excel Power Query dan Excel Power Pivot (Excel Power Pivot itu data model)

    > kalau mau pakai cara ini, pastikan sudah paham cara 3, lalu copy semua ke sheet baru dan set sebagai Excel Table (termasuk kolom berformulanya)

       setelah itu, periksa setiap formula (di baris data pertama saja), ubah rujukan range yang merujuk ke seluruh baris data agar pakai cara Excel Table

       Pakai gambar deh.... lihat formula yang perlu diubah... cuma dikit, tapi memudahkannya bisa seumur-umur

       Eman-eman kalau ada fitur bagus seperti ini kok gak dipakai.. 

       Bila perlu, membuat tabel menjadi Excel table dijadikan kebiasaan atau bahkan sop... 

5. Pakai Excel Power Query (data transformations)

    > untuk kasus ini, cara dengan Power Query sekedar cukup direkomendasikan saja... 

       tapi sebagian besar pengolahan data jaman now meletakkan Power Query pada posisi sangat direkomendasikan

    > Fitur ini membutuhkan add-in (add-in yang butuh di-install [sekali doang proses install-nya] itu bikin ribet ? gak salah tuh arah mikirnya ?])

       Mulai Excel 2016, Power Query sudah built-in saat install ms-office. Ada di menu Data bernama Get & Transform

    > Sumber data harus dalam bentuk Excel Table (jika di workbook yang sama). 

       Jika sumber data ada di file lain dalam bentuk xls, xlsx, xlsm, xlsb, json, xml, html, txt, csv, tsv, (sebut deh sampai capek)

       apalagi di database (baik file based seperti ms access atau server based seperti sql server, oracle, mysql, postgresql, dan sebagainya), power query akan mengambilnya dan mengolahnya

    > cukup bikin sekali saja alur prosesnya di power query, dan setelah itu, sepanjang masa cukup refresh all dari worksheet, 

       sampai ada perubahan kebijakan dari atasan si user tentang cara hitungnya

    > hasil dari power query bisa dibawa ke :

             - worksheet (jadi Excel Table seperti gambar dibawah), 

             - langsung ke Power Pivot (jadi data model dulu baru ditampilkan ke Excel Pivot Table)

             - langsung ke Excel Pivot Table (klasik Excel Pivot Table, gak pakai model-modelan)

             - ke database, ke text file, bikin html-nya ? bisa... tapi ndak perlu mimpi yang kejauhan sampai sini ya... kenalan dulu saja dengan power query

    > input data baru langsung tersimpan di-tabel data dengan cukup klik refresh all ? Power Query bisa melakukannya, gak perlu ruwet programming VBA

       tapi, kasihan VBA kalau gak dipakai ya... boleh deh... VBA bisa dimanfaatkan untuk mengatur tombol saja. Jika user klik tombol, suruh VBA untuk melakukan refresh all atau refresh tabel tertentu

    > ini contohnya : 

       * data dari excel table yang ada di worksheet di workbook yang sama

         hasil power query dibawa kembali ke worksheet jadi excel table lain

6. Pakai Excel Power Pivot (Excel Data Model)

    > untuk kasus ini, sangat direkomendasikan

       untuk sebagian besar pengolahan data jaman now, 

                  sangat direkomendasikan untuk dilakukan transformasi data dengan Excel Power Query lebih dulu 

                  barulah hasilnya dikirim ke Excel Power Pivot (Excel Data Model) 

    > Fitur ini selalu membutuhkan add-in (paling tidak sampai saat ini diketik) sila lihat email yang lalu atau search di-internet tentang cara install dan mengaktifkannya

    > contohnya seperti ini :

Berhubung jarinya dah pada bengkak akibat berantem dengan keyboard 

n yang baca dah pada bosen atau pening, maka sekian dulu ya...

Regards,

Kid

[ayuningtyas mendrofa]

HI Mr. Kid,

Thanks untuk berbagai solusi yang diberikan. memang butuh pengorbanan untuk bisa solved dari case yang ada didepan mata hehe..

Oke, saya akan coba dulu untuk option no 6..

Again, Big Thanks to you

Best Regards,

Ayu

To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPvJDAYS%2BV1Kr54RHCqbhMcE8-Xp%3DTkSy2T7KYVf%2BXasqg%40mail.gmail.com.