Menampilkan hasil secara otomatis

  1. tahun lalu

    Mohon bantuan para master excel.
    saya ada soal, mungkin buat para master soal ini sudah tidak asing lagi
    karena soal ini pernah saya tanyakan pada pertemuan sebelumnya.
    namun kali ini saya akan menanyakan bagaimana cara menampilkan hasil secara otomatis pada sheet yang telah tersedia tanpa menginputkan tanggal.

    untuk lebih jelasnya saya lampirkan contoh soalnya..

    Keterangan isi file:
    Sheet Resume
    soal yang sudah pernah/selesai dibahas sebelumnya oleh para master di forum ini.
    terdapat kolom input tanggal, dimana jika diinputkan tanggal yang diinginkan
    maka akan tampil Total pemakaian yang didapat dari sheet "Data".

    Sheet Data
    Tempat dimana sumber data berada.

    Sheet Januari, Februari, Maret, April, Mei
    Jika pada Sheet "Resume" harus diinputkan tanggal terlebih dahulu
    maka baru tampil data. namun disheet ini saya menginginkan
    perubahan yaitu, kolom akan terisi nilai secara otomatis,
    sesuai Sheet bulan masing-masing.

    Terima Kasih untuk sebelumnya untuk para master di forum ini.

    NB:
    Adakah formula lain tanpa menggunakan Sumproduct untuk soal ini?
    karena mengingat sumproduct cukup berat sehingga membuat kerja excel menjadi lambat.

  2. Caton

    8 Okt 2017 Terverifikasi Indonesia + 13.255 Poin

    Mas @awraabadi ... silahkan diperiksa dahulu file terlampir. Agar dapat memahami konsep yang saya tawarkan, beberapa hal berikut perlu diperhatikan:

    + Nama Sheet
    Pada file terlampir, sheet TEMPLATE merupakan master yang nantinya dapat disalin (dicopy) sesuai kebutuhan dalam membuat sheet "Resume" lainnya (misalkan untuk Januari, Februari, Maret dan seterusnya). Namun perlu diingat, otomatisasi proses kalkulasi dan referensi dalam sheet TEMPLATE (dan salinannya) sangat bergantung kepada nama sheet yang diberikan. Keyword yang digunakan dalam menentukan periode kalkulasi (bulan dan tahun) pada setiap sheet "Resume" adalah 6 karakter terakhir dari nama sheet. Tidak masalah apa nama awal yang diberikan, namun 6 karakter terakhir dari nama sheet harus terdiri dari gabungan bulan dan tahun. Contoh, nama sheet = BULAN JANUARI 012017 akan ditentukan sebagai periode bulan Januari, nama sheet = RESUME 013017 akan ditentukan sebagai periode bulan Maret dan sebagainya. Ini adalah kunci utama dari proses otomatisasinya.

    + Nama Range
    Nama range juga sangat menentukan proses otomatisasinya. Sheet TEMPLATE (dan salinannya) masing-masing memiliki 3 private name yakni: Sheet.Period.Month, Sheet.Period.Year dan Sheet.Period.Date. Dua nama pertama digunakan untuk mendapatkan bulan dan tahun dari nama sheet, dan nama range Sheet.Period.Date digunakan untuk menggabungkan kedua nama pertama menjadi tanggal acuan. Nama range lainnya yang diawali dengan prefiks Data. merupakan nama range yang sifatnya dinamis dan digunakan sebagai acuan data per kolom dari sheet DATA.

    Untuk masalah formula, selain menggunakan fungsi SUMPRODUCT, bisa juga menggunakan fungsi INDEX atau SUMIF, dengan tambahan kolom bantu pada sheet DATA (kolom A) untuk mencari baris data berdasarkan gabungan kata kunci kategori, tipe dan lantai. Namun oleh karena fungsi INDEX tidak bisa digunakan dalam menjumlahkan kata kunci yang sama (duplikat), maka pada sheet TEMPLATE (dan salinannya) saya gunakan fungsi SUMIF sebagai alternatif. Saya tidak tahu apakah formulasi tersebut dapat dijadikan alternatif untuk mengurangi lambatnya proses kalkulasinya nanti... ^^; namun pastinya mas @awraabadi coba dahulu saja dengan data sebenarnya. Menurut saya sih, jika datanya banyak, ya proses kalkulasi tentunya akan tetap memakan waktu... :D Dan karena kemungkinan kesalahan formulasi masih ada, jadi nanti silahkan didiskusikan ;)

  3. Jurus sakti apa lagi ini mas caton, seakan ga kehabisan akal mas caton ini,
    luar biasa mas..

    tapi, jujur sedikitnya saya agak lbh paham sumproduct ya mas..
    lumayan agak rumit dimengerti buat saya formula barunya mas caton ini..
    oke deh mas.. coba saya pahami pelan-pelan mas, ilmu baru yang dishare ke saya ini.

    nanti saya tanya2 lagi ya mas klo ada kesulitan..

    terima kasih mas caton :)

  4. Caton

    11 Okt 2017 Terverifikasi Indonesia + 13.255 Poin
    Di sunting tahun lalu oleh Caton

    Wah... saya tidak menggunakan jurus apapun mas @awraabadi... :D

    Untuk konsep proses otomasinya, sebenarnya sama saja dengan yang mas @awraabadi gunakan sebelumnya. Yang membedakan hanyalah proses pengisian tanggalnya (periodenya) saja yang diotomatiskan. Kalau dilihat tahapan proses sebelumnya, maka kita harus 1) menyalin sheet RESUME menjadi sheet baru, 2) kemudian mengubah tanggal (bulan) pada sel L7 dalam salinan sheet RESUME tadi, 3) dan kemudian mengubah nama sheet menjadi nama sesuai bulan yang dikehendaki. Nah, saya hanya mengganti tahapan ke-2 di atas menjadi otomatis (disesuaikan dengan keyword pada nama sheet, sesuai proses ke-3)... Prosesnya sih bisa dibilang masih manual juga... :) Kalo memang mau full otomatis, bisa dengan menggunakan macro. Tinggal klik, sheet tercopy, periode ditetapkan dan formula langsung terkalkulasi... :D

    Kuncinya dari konsep yang saya gunakan ada pada definisi nama Sheet.Period.Date. Sebenarnya, definisi nama range Sheet.Period.Month dan Sheet.Period.Year tidak mutlak diperlukan (awalnya kedua nama tersebut saya gunakan untuk konsep lain), jadi sel pada acuan Sheet.Period.Date sebenarnya bisa langsung diisi dengan formulasi yang digunakan pada nama range Sheet.Period.Month dan Sheet.Period.Year. Jika sebelumnya pada sel L4 sheet TEMPLATE berisi formula:

    =IFERROR(DATE(Sheet.Period.Year;Sheet.Period.Month;20);"")

    maka untuk argumen Sheet.Period.Year bisa langsung diganti dengan formula:

    =IFERROR(MID(CELL("filename";A1);LEN(CELL("filename";A1))-6+3;4)*1;0)

    dan untuk argumen Sheet.Period.Month bisa langsung diganti dengan formula:

    =IFERROR(MID(CELL("filename";A1);LEN(CELL("filename";A1))-6+1;2)*1;0)

    Dengan demikian, definisi nama Sheet.Period.Month dan Sheet.Period.Year bisa dihapus.

    Sedangkan mengenai formulanya, fungsi SUMPRODUCT memang jauh lebih fleksible, dan sangat bermanfaat saat digunakan pada proses kalkulasi antar workbook. Namun perlu dipahami, terkadang SUMPRODUCT bukanlah solusi yang tepat saat berurusan dengan jumlah data yang banyak. Sedangkan fungsi SUMIF, secara umum prosesnya lebih cepat dibandingkan SUMPRODUCT. Namun, dalam kasus kondisional seperti punya mas @awraabadi, harus ada "tindakan khusus" agar fungsi SUMIF dapat digunakan, yakni dengan menambahkan kolom bantu kriterianya.

    Jika tetap ingin menggunakan fungsi SUMPRODUCT, saya pikir solusi lainnya untuk mengurangi lambatnya proses kalkulasi, coba gunakan kolom bantu untuk kata kunci pencarian (seperti fungsi SUMIF yang saya gunakan). Jadi, untuk sel B3 pada sheet RESUME, dari formula:

    =SUMPRODUCT((DATA!$B$3:$B$31="LISTRIK")*(DATA!$C$3:$C$31=$A3)*(DATA!$D$3:$D$31=B$2); OFFSET(DATA!$D$2;1;MATCH($L$7;DATA!$E$2:$AC$2;0)+1;29;1))

    diubah menjadi:

    =SUMPRODUCT((DATA!$A$3:$A$31="LISTRIK"&$A3&B$2)*1;OFFSET(DATA!$D$2;1;MATCH(L7;Data.Date;1)+1;COUNTA(Data.Category);1))

    Karena, pada formula sebelumnya, Excel harus mengevaluasi 3 nilai (LISTRIK, KS dan LD1) pada 3 range berbeda (DATA!$B$3:$B$31, DATA!$C$3:$C$31 dan DATA!$D$3:$D$31), maka ketika jumlah baris range yang akan dievaluasi bertambah banyak, dapat dipastikan proses akan semakin lambat. Misalkan satu range ada 1000 baris, maka untuk mengevaluasi 3 nilai tersebut, maka total ada 3 x 1000 = 3000 baris proses evaluasi. Bandingkan jika menggunakan kolom bantu, karena hanya ada 1 range evaluasi, maka Excel hanya mengevaluasi 1000 baris saja... lumayan, hemat 2000... :D

  5. waaahh sungguh jelas ini keterangannya mas.. kemarin2 lagi banyak kesibukan saya mas, jd skrng baru bisa buka thread ini.. jadi ga sabar mau langsung praktek. terima kasih banyak pencerahannya mas @Caton :) Super sekali

 

atau Mendaftar untuk ikut berdiskusi!