buat lap. penjulan bulanan

  1. 3 minggu lalu

    Assalamu'alaikum...
    para master excel mohon bantuan, aku lg butuh laporan yang praktis, sudah sempat buat pake excel tapi pada laporan bulan masih bingung rumusnya...
    mohon bantuan para master
    sebelumnya banyak terima kasih

  2. manweljs_

    Agu 30 Terverifikasi + 5.020 Poin

    @waa

    coba formula ini:

    =SUMIF(INDIRECT("LAHAR!"&ADDRESS(5,MATCH($D$2,LAHAR!$2:$2,0)+2)&":"&ADDRESS(1000,MATCH($D$2,LAHAR!$2:$2,0)+2)),$C9,INDIRECT("LAHAR!"&ADDRESS(5,MATCH($D$2,LAHAR!$2:$2,0)+3)&":"&ADDRESS(1000,MATCH($D$2,LAHAR!$2:$2,0)+3)))

  3. Caton

    Agu 30 Terverifikasi Indonesia + 11.463 Poin

    @manweljs_

    Izin ikut menambahkan ... :)

    @waa ... Assalamu'alaikum ...

    Wa'alaikumsalam mas @waa ...

    Selain dengan formula langsung seperti yang mas @manweljs_ di atas sampaikan, alternatif lainnya dengan memanfaatkan nama range dinamis. Untuk formula intinya menggunakan fungsi yang sama, yakni fungsi SUMIF. Contoh terlampir.

  4. para master Terima kasih semua @manweljs_ & @Caton
    semoga Tuhan membalas segala kebaiknnya
    Amiiin

    ini sangat bermanfaat

  5. mas @manweljs_ boleh diterangkan untuk rumusnya... maklum masih awam..
    untuk mas @Caton rumus panjengan sama sekali saya belum kenal tapi jos lebih singkat boleh dong aku belajar kalo berkenan saya minta penjelasannya dan cara membuatnya
    terus terang saya suka sekali excel tapi belum ada guru sebagai penunjuknya...
    sebelumnya terima kasih

  6. manweljs_

    Agu 31 Terverifikasi + 5.020 Poin

    @waa ...terus terang saya suka sekali excel tapi belum ada guru sebagai penunjuknya...

    wah kebetulan sekali, mas @Caton buka kursus privat excel tuh, dan lagi formula yg saya buat sebenarnya sama aja dengan punyanya beliau. jadi biar beliau aja yg menjawab yaa... ;)

    silahkan mas

  7. @manweljs_ wah kebetulan sekali, mas @Caton buka kursus privat excel tuh, dan lagi formula yg saya buat sebenarnya sama aja dengan punyanya beliau. jadi biar beliau aja yg menjawab yaa... ;)

    silahkan mas

    maaf lokasi di mana ya @Caton ?

  8. atau bisa online?

  9. Caton

    Sep 1 Terverifikasi Indonesia + 11.463 Poin

    @waa ... rumus panjengan sama sekali saya belum kenal ...

    Mas @waa ...

    Beneran belum kenal? Khan formula yang saya gunakan sebenarnya sama saja dengan formula yang digunakan oleh mas @manweljs_ , yakni sama-sama menggunakan fungsi SUMIF. Biasanya, saya menggunakan fungsi SUMPRODUCT sebagai alternatif formulanya, namun kali ini saya hanya ingin menunjukkan alaternatif pemanfaatan nama range dinamis. Kalaupun formula yang saya gunakan berbeda dengan formula yang digunakan oleh mas @manweljs_ , perbedaannya hanya pada:

    — Cara penyusunannya, dimana formula yang disusun oleh mas @manweljs_ dibuat secara langsung ke dalam sel target. Sedangkan formula yang saya gunakan memanfaatkan nama range guna menghindari formula yang terlalu panjang.

    — Mas @manweljs_ memanfaatkan fungsi INDIRECT dan fungsi ADDRESS untuk menentukan range rujukan yang digunakan dalam fungsi SUMIF untuk membuat formula menjadi dinamis, karena akan terjadi perubahan lokasi atau range rujukan saat nama bulan (pada sel D2) berubah. Formula yang saya gunakan juga memanfaatkan fungsi rujukan, yakni fungsi OFFSET, yang saya buat menjadi sebuah shortcut dalam bentuk nama range, yakni dt.Laporan.Tabel.

    Untuk penjelasan mengenai formula yang digunakan oleh mas @manweljs_ , saya serahkan kepada beliau untuk menjelaskannya karena formula tersebut merupakan hasil pemikiran beliau dan beliau yang lebih paham — bukankah begitu mas @manweljs_ ... Lagi pula, meskipun sama-sama menggunakan fungsi SUMIF, namun tidaklah serupa penggunaan antara fungsi INDIRECT dengan fungsi OFFSET ... ;)

    Demikian.

  10. Caton

    Sep 1 Terverifikasi Indonesia + 11.463 Poin

    @waa ... kalo berkenan saya minta penjelasannya dan cara membuatnya ...

    Dari soal yang ditanyakan, target yang diharapkan adalah mendapatkan jumlah (QTY) penjualan per produk berdasarkan KODE produk dari tabel data penjualan pada sheet LAHAR. Oleh karena targetnya adalah penjumlahan (SUM) dengan kriteria tertentu (IF), maka fungsi yang dapat digunakan adalah kombinasi fungsi SUM dan fungsi IF, atau cukup dengan fungsi SUMIF saja. Dengan demikian, formula dasarnya dapat disusun sebagai berikut:

    = SUMIF(LAHAR!$B$5:$B$24, $B9, LAHAR!$D$5:$D$24)

    Masalahnya adalah data penjualan yang akan dijumlahkan harus diambil dari tabel terpisah — yang ditentukan berdasarkan nama bulan yang akan ditampilkan. Dengan demikian, dibutuhkan cara atau fungsi khusus untuk dapat memindahkan range tabel rujukan (range B5:B24 dan range D5:D24 pada sheet LAHAR) secara otomatis. Untuk hal ini, salah satu fungsi Excel yang dapat digunakan untuk menentukan sel atau range rujukan melalui formula adalah fungsi OFFSET. Sintaks dari fungsi OFFSET sendiri adalah:

    = OFFSET(referensi, baris, kolom, [tinggi], [lebar])

    Lalu bagaimana menerapkan fungsi OFFSET tersebut ke dalam fungsi SUMIF yang sudah ditetapkan di atas? Untuk itu ada beberapa tahapan proses yang harus dilakukan:

    [1]. Periksa bentuk atau layout tabel data pada sheet LAHAR. Usahakan bentuk tabel data tersebut mudah untuk dirujuk. Oleh karena bentuk atau susunan setiap tabel data pada sheet LAHAR sudah disusun secara horizontal dan jumlah kolomnya pun sama, maka dapat ditetapkan:

    — Target kolom adalah kolom KODE, NAMA PRODUK dan kolom QTY. Jadi parameter lebar akan berisi nilai 3. Oleh karena data akan jumlah baris data tidak dapat dipastikan, maka untuk parameter tinggi diisi sembarang nilai yang diperkirakan mencukupi, misalkan 1000.

    — Oleh karena baris data dimulai dari baris ke-5, maka parameter referensi kita tentukan 1 baris sebelum baris awal data yakni pada sel A4. Sel tersebut akan digeser ke bawah 1 baris melalui parameter baris.

    Dari ketetapan di atas, maka fungsi OFFSET yang akan digunakan tersusun menjadi:

    = OFFSET($A$4, 1, kolom, 1000, 3)

    — Karena perpindahan range rujukan dilakukan secara horizontal, sehingga yang akan diubah adalah parameter kolom dari fungsi OFFSET tersebut. Namun masalahnya, bagaimana menentukan jarak kolom dari kolom asal (yakni kolom A)?

    [2]. Untuk menentukan indeks kolom awal dari setiap tabel data yang akan dijadikan target adalah dengan menentukan apa yang dapat dijadikan acuan. Oleh karena sel D2 pada sheet LABUL akan berisi nama bulan yang dijadikan pemicu (trigger) untuk mengubah target tabel rujukan, dan pada baris ke-2 pada sheet LAHAR sudah terisi nama-nama bulan dari setiap tabel, maka fungsi yang cocok untuk mendapatkan kolom awal dari setiap tabel adalah fungsi MATCH. Sehingga jika diformulasikan akan menjadi ± seperti berikut:

    = MATCH($D$2, LAHAR!$2:$2, 0)

    Sampai di sini, maka fungsi OFFSET yang akan digunakan ± tersusun menjadi:

    = OFFSET($A$4, 1, MATCH($D$2, LAHAR!$2:$2, 0), 1000, 3)

    [3]. Dari 2 tahapan di atas, sudah dapat dilakukan proses perpindahan antar tabel rujukan. Masalahnya, bagaimana mengambil range data KODE dan range data QTY dari tabel rujukan tersebut? Fungsi INDEX jawabannya. Dengan fungsi INDEX, kita dapat mengambil data dari seluruh baris data pada sebuah range per kolomnya saja. Oleh karena fungsi OFFSET pada poin ke-2 di atas akan mengembalikan hasil berupa range data 1000 baris 3 kolom, maka untuk merujuk kepada kolom pertama (KODE) dapat digunakan formula:

    = INDEX(OFFSET($A$4, 1, MATCH($D$2, LAHAR!$2:$2, 0), 1000, 3), 0, 1)

    dan untuk merujuk kepada kolom ketiga (QTY) dapat digunakan formula:

    = INDEX(OFFSET($A$4, 1, MATCH($D$2, LAHAR!$2:$2, 0), 1000, 3), 0, 3)

    Sehingga, jika hasil dari poin ke-3 di atas jika disisipkan ke dalam fungsi SUMIF yang akan digunakan, maka akan tersusun ± seperti berikut ini:

    = SUMIF(INDEX(OFFSET($A$4, 1, MATCH($D$2, LAHAR!$2:$2, 0), 1000, 3), 0, 1), $B9,
      INDEX(OFFSET($A$4, 1, MATCH($D$2, LAHAR!$2:$2, 0), 1000, 3), 0, 3))

    Meskipun formulanya tidak terlalu panjang, namun kita bisa membuatnya lebih pendek dengan menggunakan nama range. Pada formula yang saya buat, masing-masing nama range merujuk kepada sel atau formula sebagai berikut:

    dt.Laporan.Bulan = LABUL!$D$2
    dt.Laporan.Tabel = OFFSET(LAHAR!$A$4;1;MATCH(dt.Laporan.Bulan;LAHAR!$2:$2;0);1000;3)

    Lalu bagaimana membuat nama range tersebut. Berikut gambarannya:

    Nama Range.gif

    Catatan:

    — Formula alternatif untuk nama range dt.Laporan.Tabel:

    =OFFSET(LAHAR!$A$5:$C$1000;0;MATCH(dt.Laporan.Bulan;LAHAR!$2:$2;0))

    — Artikel rujukan: Fungsi INDIRECT , fungsi OFFSET , fungsi MATCH , fungsi SUMIF .

    Demikian yang dapat saya jelaskan. Semoga bermanfaat.

  11. Caton

    Sep 1 Terverifikasi Indonesia + 11.463 Poin

    @waa ... atau bisa online ...

    Mendingan cari lembaga kursus di wilayah mas @waa saja ... ;)

  12. @Caton Dari soal yang ditanyakan, target yang diharapkan adalah mendapatkan jumlah (QTY) penjualan per produk berdasarkan KODE produk dari tabel data penjualan pada sheet LAHAR. Oleh karena targetnya adalah penjumlahan (SUM) dengan kriteria tertentu (IF), maka fungsi yang dapat digunakan adalah kombinasi fungsi SUM dan fungsi IF, atau cukup dengan fungsi SUMIF saja. Dengan demikian, formula dasarnya dapat disusun sebagai berikut:

    = SUMIF(LAHAR!$B$5:$B$24, $B9, LAHAR!$D$5:$D$24)

    Masalahnya adalah data penjualan yang akan dijumlahkan harus diambil dari tabel terpisah — yang ditentukan berdasarkan nama bulan yang akan ditampilkan. Dengan demikian, dibutuhkan cara atau fungsi khusus untuk dapat memindahkan range tabel rujukan (range B5:B24 dan range D5:D24 pada sheet LAHAR) secara otomatis. Untuk hal ini, salah satu fungsi Excel yang dapat digunakan untuk menentukan sel atau range rujukan melalui formula adalah fungsi OFFSET. Sintaks dari fungsi OFFSET sendiri adalah:

    = OFFSET(referensi, baris, kolom, [tinggi], [lebar])

    Lalu bagaimana menerapkan fungsi OFFSET tersebut ke dalam fungsi SUMIF yang sudah ditetapkan di atas? Untuk itu ada beberapa tahapan proses yang harus dilakukan:

    [1]. Periksa bentuk atau layout tabel data pada sheet LAHAR. Usahakan bentuk tabel data tersebut mudah untuk dirujuk. Oleh karena bentuk atau susunan setiap tabel data pada sheet LAHAR sudah disusun secara horizontal dan jumlah kolomnya pun sama, maka dapat ditetapkan:

    — Target kolom adalah kolom KODE, NAMA PRODUK dan kolom QTY. Jadi parameter lebar akan berisi nilai 3. Oleh karena data akan jumlah baris data tidak dapat dipastikan, maka untuk parameter tinggi diisi sembarang nilai yang diperkirakan mencukupi, misalkan 1000.

    — Oleh karena baris data dimulai dari baris ke-5, maka parameter referensi kita tentukan 1 baris sebelum baris awal data yakni pada sel A4. Sel tersebut akan digeser ke bawah 1 baris melalui parameter baris.

    Dari ketetapan di atas, maka fungsi OFFSET yang akan digunakan tersusun menjadi:

    = OFFSET($A$4, 1, kolom, 1000, 3)

    — Karena perpindahan range rujukan dilakukan secara horizontal, sehingga yang akan diubah adalah parameter kolom dari fungsi OFFSET tersebut. Namun masalahnya, bagaimana menentukan jarak kolom dari kolom asal (yakni kolom A)?

    [2]. Untuk menentukan indeks kolom awal dari setiap tabel data yang akan dijadikan target adalah dengan menentukan apa yang dapat dijadikan acuan. Oleh karena sel D2 pada sheet LABUL akan berisi nama bulan yang dijadikan pemicu (trigger) untuk mengubah target tabel rujukan, dan pada baris ke-2 pada sheet LAHAR sudah terisi nama-nama bulan dari setiap tabel, maka fungsi yang cocok untuk mendapatkan kolom awal dari setiap tabel adalah fungsi MATCH. Sehingga jika diformulasikan akan menjadi ± seperti berikut:

    = MATCH($D$2, LAHAR!$2:$2, 0)

    Sampai di sini, maka fungsi OFFSET yang akan digunakan ± tersusun menjadi:

    = OFFSET($A$4, 1, MATCH($D$2, LAHAR!$2:$2, 0), 1000, 3)

    [3]. Dari 2 tahapan di atas, sudah dapat dilakukan proses perpindahan antar tabel rujukan. Masalahnya, bagaimana mengambil range data KODE dan range data QTY dari tabel rujukan tersebut? Fungsi INDEX jawabannya. Dengan fungsi INDEX, kita dapat mengambil data dari seluruh baris data pada sebuah range per kolomnya saja. Oleh karena fungsi OFFSET pada poin ke-2 di atas akan mengembalikan hasil berupa range data 1000 baris 3 kolom, maka untuk merujuk kepada kolom pertama (KODE) dapat digunakan formula:

    = INDEX(OFFSET($A$4, 1, MATCH($D$2, LAHAR!$2:$2, 0), 1000, 3), 0, 1)

    dan untuk merujuk kepada kolom ketiga (QTY) dapat digunakan formula:

    = INDEX(OFFSET($A$4, 1, MATCH($D$2, LAHAR!$2:$2, 0), 1000, 3), 0, 3)

    Sehingga, jika hasil dari poin ke-3 di atas jika disisipkan ke dalam fungsi SUMIF yang akan digunakan, maka akan tersusun ± seperti berikut ini:

    = SUMIF(INDEX(OFFSET($A$4, 1, MATCH($D$2, LAHAR!$2:$2, 0), 1000, 3), 0, 1), $B9,
      INDEX(OFFSET($A$4, 1, MATCH($D$2, LAHAR!$2:$2, 0), 1000, 3), 0, 3))

    Meskipun formulanya tidak terlalu panjang, namun kita bisa membuatnya lebih pendek dengan menggunakan nama range. Pada formula yang saya buat, masing-masing nama range merujuk kepada sel atau formula sebagai berikut:

    dt.Laporan.Bulan = LABUL!$D$2
    dt.Laporan.Tabel = OFFSET(LAHAR!$A$4;1;MATCH(dt.Laporan.Bulan;LAHAR!$2:$2;0);1000;3)

    Lalu bagaimana membuat nama range tersebut. Berikut gambarannya:

    [attachment:5b8a15f298ae1]

    Catatan:

    — Formula alternatif untuk nama range dt.Laporan.Tabel:

    =OFFSET(LAHAR!$A$5:$C$1000;0;MATCH(dt.Laporan.Bulan;LAHAR!$2:$2;0))

    — Artikel rujukan: Fungsi INDIRECT , fungsi OFFSET , fungsi MATCH , fungsi SUMIF .

    Demikian yang dapat saya jelaskan. Semoga bermanfaat.

    waow.... mantab.... sangat baik hati... mudah2 Alloh memudahkan jalan mas @caton..
    jadi pengin copy darat sama mas@caton... he he
    جزاكم الله أحسن الجزا

  13. Caton

    Sep 2 Terverifikasi Indonesia + 11.463 Poin

    @waa ... mudah2 Alloh memudahkan jalan mas @caton ...

    Semoga kemudahan juga bagi mas @waa . Wa jazakallahu khairan.

  14. manweljs_

    Sep 2 Terverifikasi + 5.020 Poin
    Di sunting 3 minggu lalu oleh manweljs_

    khusus untuk diskusi yg ini, saya kok gak bisa baca komentar2 sebelumnya ya ?
    khususnya komentar2 mas @Caton

    mungkin komputer saya kena virus nih (^_^)/

  15. Caton

    Sep 2 Terverifikasi Indonesia + 11.463 Poin

    @manweljs_ ... khusus untuk diskusi yg ini, saya kok gak bisa baca komentar2 sebelumnya ya ? khususnya komentar2 mas @Caton. mungkin komputer saya kena virus nih ...

    Msa sih mas? Kena virus cuma di diskusi ini saja ... :D (^_^)/

  16. manweljs_

    Sep 2 Terverifikasi + 5.020 Poin
    Di sunting 3 minggu lalu oleh manweljs_

    @Caton Msa sih mas? Kena virus cuma di diskusi ini saja ... :D (^_^)/

    iya mas, akhir2ini komputer saya kena virus PENJELASAN, jadi kalau ada kata2 "penjelasan" disekitarnya postingannya tidak bisa terlihat :P :D

  17. Caton

    Sep 2 Terverifikasi Indonesia + 11.463 Poin

    @manweljs_ ... akhir2ini komputer saya kena virus PENJELASAN ...

    Oh ... Mungkin karena seharusnya mas @manweljs_ yang memberikan penjelasan-penjelasan, bukan minta dijelaskan ... :D :P

  18. 2 minggu lalu

    @Caton Oh ... Mungkin karena seharusnya mas @manweljs_ yang memberikan penjelasan-penjelasan, bukan minta dijelaskan ... :D :P

    betul...betul.... :D :P

 

atau Mendaftar untuk ikut berdiskusi!