permisi kakak saya mau tanya, bagaimana rumusnya ketika B4 diisi tanggal, maka A8: A17 otomatis terisi no.nota yang sdh jatuh tempo berdasarkan tanggal di B4, data nota memanggil disheet sales 01, trims
-
6 tahun lalu
-
@Hima...
Terlampir contoh penyelesaian dari yang @Hima tanyakan di atas. Pada file terlampir, sheet CETAK-01 dan sheet CETAK-01 merupakan contoh penyelesaian dengan memanfaatkan kolom bantuan indeks baris data yang sesuai kriteria tanggal yang dicari. Bedanya:
— Pada sheet CETAK-01, kolom bantuan disusun langsung pada tabel data pada sheet SALES-01, dengan formula yang cukup sederhana dan mudah dipahami (baik formula pada sheet CETAK-01 maupun formula pada sheet SALES-01).
— Pada sheet CETAK-02, kolom bantuan disusun sejajar dengan tabel tagihan. Formula pada sheet CETAK-02 ini tidak memanfaatkan kolom bantuan pada sheet SALES-01. Sehingga, jika akan menggunakan contoh pada sheet CETAK-02, kolom INDEX BANTU pada sheet SALES-01 dapat dihapus karena tidak digunakan. Pada contoh ini, ada dua macam fungsi utama yang dapat digunakan, AGGREGATE dan SUMPRODUCT. Formula inti dari keduanya sama, hanya saja jika dengan fungsi SUMPRODUCT harus menggunakan fungsi SMALL untuk mengambil indeks baris berurutan mulai dari indeks terkecil.
Sedangkan pada sheet CETAK-03, merupakan contoh jika tanpa menggunakan kolom bantuan. Formulanya bisa menggunakan fungsi AGGREGATE atau fungsi SUMPRODUCT seperti pada contoh kolom bantuan pada sheet CETAK-02.
Pada file terlampir, sengaja saya gunakan Table Object agar @Hima mulai terbiasa menggunakan objek tersebut untuk menyusun sumber data.
Demikian, semoga sesuai dan semoga bermanfaat.
-
terima kasih kakak, luar biasa penyelesaiannya inyaallha bermanfaat ilmunya
-
aduh maaf ternyata pertanyaan saya kurang lengkap, yg seharusnya ditampilkan di sheet cetak adalah jatuh tempo yg belum lunas, gmn revisi rumusnya ya
-
Di sunting 6 tahun lalu oleh Caton
@Hima ...
... yg seharusnya ditampilkan di sheet cetak adalah jatuh tempo yg belum lunas ...
Maksudnya bagaimana? Bukankah pada contoh yang saya berikan (misalkan pada sheet CETAK-01) data yang ditampilkan adalah data dari sheet SALES-01 yang tanggal jatuh temponya sebelum tanggal cetak tagihan (sel C4 sheet CETAK-01). Pada sheet CETAK-01, semisal tanggal yang diinput adalah 16/07/2018, maka formula kolom INDEX BANTU (kolom J) pada sheet SALES-01 hanya akan menampilkan indeks baris untuk setiap baris data dimana tanggal pada kolom JATUH TEMPO < dari tanggal cetak tagihan dan kolom STATUS = BELUM. Coba lihat pada kolom INDEX BANTU pada sheet SALES-01. Yang memiliki nilai indeks baris data yang sesuai adalah:
0055 0059 0060 0064
karena baris-baris tersebut tanggal jatuh temponya adalah 15/07/2018 dengan status BELUM. Jika ingin kriterianya menjadi tanggal pada kolom JATUH TEMPO <= dari tanggal cetak tagihan, cukup tambahkan tanda sama dengan (=) pada formula kolom INDEX BANTU, yakni formula sebelumnya berupa:
=IF(AND([@E]<>"";[@E]<'CETAK-01'!$C$4;[@H]="BELUM");ROW(A1);"")
diubah (tambahkan tanda sama dengan) menjadi:
=IF(AND([@E]<>"";[@E]<='CETAK-01'!$C$4;[@H]="BELUM");ROW(A1);"")
Jika maksudnya adalah tanggal jatuh tempo yang sama dengan tanggal cetak tagihan, tinggal diubah tanda lebih besar (>) menjadi tanda sama dengan (=), seperti berikut:
=IF(AND([@E]<>"";[@E]='CETAK-01'!$C$4;[@H]="BELUM");ROW(A1);"")
Untuk formula pada sheet CETAK-02 dan sheet CETAK-03, revisinya kurang lebih sama, yakni pada tanda kondisional tersebut di atas, misalkan untuk formula pada sel J9 sheet CETAK-02:
... *(Tabel.Sales01[E]<$C$4) ... +(Tabel.Sales01[E]>=$C$4) ...
menjadi:
... *(Tabel.Sales01[E]<=$C$4) ... +(Tabel.Sales01[E]>$C$4) ...
Mungkin demikian yang dimaksud.
Coba diperiksa kembali. Kalau memang tidak sesuai, coba berikan contoh data dan hasil yang diinginkan yang berbeda dengan yang telah dilampirkan sebelumnya. Karena dalam menyusun formula tersebut saya mengacu kepada contoh hasil pada file yang @Hima lampirkan pertama di atas .... :)
-
Mohon ijin ikutan menjawab..
Barangkali jawaban saya agak terlalu banyak kolom bantuan, namun semoga membantu.
Terima kasih.Note :
- kolom bantuan saya beri warna biru
- saya gunakan sheet yang sudah ada, hanya saya tambahi kolom bantuan.
-
upz, setelah sy pelajari, kakak sdh benar, hanya tagihan yg belum lunas yg tampil, tapi gmn agar tanggal pencarian 7/16/2018 bisa format indonesia 16/7/2018 karena sy coba ganti format indonesia yg tampil tidak sesuai dan cenderung random, dan jika saya panggil 7/16/2018 seperti tutor asli dari kakak, yg tampil tanggal sebelumnya yaitu 15-7-2018 , karena yg sy butuhkan adalah tampilan tagihan hari ini( tgl sesuai cell c4) yang belum lunas yaitu 16/7/2018, trims
-
melanjutkan diskusi sebelum filter tdk hanya berdasarkan tgl, tapi juga ID dan status , persoalan lengkapnya di file, trims atas bantuannya
-
mbak @hima
coba edit kolom bantunya :
=IF(AND([@E]<>"",[@E]='CETAK-01'!$C$4,[@H]="BELUM"),ROW(A1),"")
apakah demikian yg dimaksud?
-
@hima ... tapi gmn agar tanggal pencarian bisa format indonesia ...
Atur Regional Setting PC yang @Hima gunakan menjadi Indonesia (Windows 7 : Control Panel › All Control Panel Items › Region and Language).
@hima ... filter tdk hanya berdasarkan tgl, tapi juga ID dan status ...
Jika penjelasan saya di atas dibaca secara cermat, jawabannya sudah ada:
Jika maksudnya adalah tanggal jatuh tempo yang sama dengan tanggal cetak tagihan, tinggal diubah tanda lebih besar (>) menjadi tanda sama dengan (=), seperti berikut:
=IF(AND([@E]<>"";[@E]='CETAK-01'!$C$4;[@H]="BELUM");ROW(A1);"")
Demikian.
-
terima kasih om @Caton , om @Hardjuno Prayogo dan om @manweljs_ , yg sdh bergabung didiskusi ini, mungkin pertanyaan saya yg belum jelas, jadi jawaban belum pas,
yg diinginkan begini :ID sy001 mempunyai 3 nota tagihan jatuh tempo tgl 15,16, dan 17
disitu filternya menggunakan tgl hari ini , anggap saja hari ini tgl 15, ok
sy gunakan formula :=IF(AND([@E]<>"";[@E]>='CETAK-01'!$C$4;[@H]="BELUM");ROW(A1);"")
kemudian hari ini sy cetak tagihan yaitu tgl 15, yg diharapkan semua nota tagihan ID sy001 yg belum lunas akan muncul/ akan di tagih semua, sekilas formula itu benar ID sy001 tgl 15,16,17, muncul, tetapi ...... iD yg lain muncul juga, pd contoh ini ID sy002 nota tgl 17 muncul , ini yg tidak diharapkan karena id sy002, tdk mempunyai tagihan tgl 15, jadi gmn formula yg benar kombinasi filter tgl, ID dan "belum lunas",?
-
Di sunting 6 tahun lalu oleh Caton
@hima ... sy gunakan formula :
=IF(AND([@E]<>"";[@E]>='CETAK-01'!$C$4;[@H]="BELUM");ROW(A1);"")
Jika ingin data yang ditampilkan sesuai kriteria Belum Lunas pada Tanggal Jatuh Tempo (sel C4 sheet CETAK-01) , khan formula yang disarankan adalah:
=IF(AND([@E]<>"";[@E]='CETAK-01'!$C$4;[@H]="BELUM");ROW(A1);"")
Lihat, apakah ada tanda >= pada formula yang disarankan tersebut! Baca kembali saran mas @manweljs_ atau penjelasan saya di atas ...
Jika ada tambahan kriteria data yang ingin ditampilkan, misalkan juga sesuai ID Sales, maka pada sheet CETAK-01 harus ada referensi dari ID Sales yang dimaksud.
-
betul sekali om @Caton , sebelumnya sy telah menggunakan formula dari si om, yg telah diajarkan dari awal diskusi , atau sama dengan sran om @manweljs_ , yaitu
=IF(AND([@E]<>"";[@E]='CETAK-01'!$C$4;[@H]="BELUM");ROW(A1);"")
tetapi pada kasus selanjutnya ada penumpukan nota tagihan pada ID sy001 tgl 15,16,17,semua nota tagihan harus ditagih pd tgl awal/filter atau dlm hal ini tgl 15, berarti memang harus ada penambahan kriteria ID pada formula, tapi saya belum dapat melakukannya -
@hima ... ada penumpukan nota tagihan pada ID sy001 tgl 15,16,17...
Saya tidak paham maksud dari penumpukan nota tersebut seperti apa. Jika ada contoh masalah yang dimaksud akan lebih mudah menganalisa pernyataan tersebut ...
Sekedar informasi, jika baris data pada sheet CETAK kurang dari dari jumlah data yang harusnya ditampilkan, maka sisa data tidak akan ditampilkan. Untuk mengatasi ini, tambah jumlah baris data pada sheet CETAK.
Saya kasih contoh pada file terlampir bagaimana formula untuk mendapatkan data atas 3 kriteria: BELUM LUNAS pada TANGGAL JATUH TEMPO sesuai SALES ID.
Demikian.
-
ok om @Caton , sy pelajari dulu contoh dari si om, nanti klo kurang pas, kita lanjut diskusinya
-
om @Caton ini saya sertakan file dan penampilan data yg diinginkan , smoga bisa dipahami
-
@hima ... ini saya sertakan file dan penampilan data yg diinginkan ...
Sudahkan @Hima membuka dan mempelajari file yang saya lampirkan terakhir di atas? Coba periksa kembali deh. Pada file tersebut:
— Sheet CETAK-01A sampai dengan sheet CETAK-03A adalah contoh untuk filter data berdasarkan kriteria tagihan BELUM LUNAS untuk TANGGAL JATUH TEMPO sesuai tanggal pada sel C4. Dalam hal ini, semua data yang tanggal jatuh temponya sesuai dengan tanggal pada sel C4, akan ditampilkan. Perbedaan dari ketiga sheet tersebut adalah tekniknya.
— Sheet CETAK-01B sampai dengan sheet CETAK-03B adalah contoh untuk filter data berdasarkan kriteria tagihan BELUM LUNAS pada TANGGAL JATUH TEMPO sesuai tanggal pada sel C4 dan SALES ID yang tertera pada sel C3. Dalam hal ini, semua data yang tanggal jatuh temponya sesuai dengan tanggal pada sel C5 dan sales ID-nya sesuai dengan id pada sel C3, akan ditampilkan. Perbedaan dari ketiga sheet tersebut adalah tekniknya.
@hima ... penagihan tgl terbaru harus diselesaikan pd tgl paling lama yaitu tgl 15 ...
Maksudnya, nilai tagihan tanggal jatuh tempo 16/08/2018 dan 17/08/2018 dijumlahkan ke dalam nilai tagihan pada tanggal jatuh tempo yang ditentukan (15/08/2018) tersebut?
Saya lampirkan saja beberapa bentuk penyelesaian yang mungkin sesuai, dimana:
— Sheet CETAK-01A: menampilkan seluruh data tagihan yang BELUM LUNAS untuk seluruh SALES, dengan ketentuan TANGGAL JATUH TEMPO harus dimulai dari tanggal yang ditentukan pada sel C4, baru kemudian diikuti tanggal setelahnya (tanggal terbaru).
— Sheet CETAK-01B: menampilkan seluruh data tagihan yang BELUM LUNAS untuk SALES ID sesuai id pada sel C3, dengan ketentuan TANGGAL JATUH TEMPO sama dengan tanggal yang ditentukan pada sel C5.
— Sheet CETAK-02A: menampilkan seluruh data tagihan yang BELUM LUNAS untuk seluruh SALES, dengan ketentuan TANGGAL JATUH TEMPO sama dengan tanggal yang ditentukan pada sel C4.
— Sheet CETAK-03A: menampilkan seluruh data tagihan yang BELUM LUNAS untuk seluruh SALES, dengan ketentuan TANGGAL JATUH TEMPO yang ditampilkan hanya tanggal yang ditentukan pada sel C4, dan nilai tagihan untuk tanggal jatuh tempo terbaru dijumlahkan pada TANGGAL JATUH TEMPO sesuai tanggal yang ditentukan pada sel C4.
— Sheet CETAK-03B: menampilkan seluruh data tagihan yang BELUM LUNAS SALES ID sesuai id pada sel C3, dengan ketentuan TANGGAL JATUH TEMPO yang ditampilkan hanya tanggal yang ditentukan pada sel C4, dan nilai tagihan untuk tanggal jatuh tempo terbaru dijumlahkan pada TANGGAL JATUH TEMPO sesuai tanggal yang ditentukan pada sel C4.
Jika ada yang sesuai, maka kolom bantu untuk sheet lainnya dapat dihapus (lihat header pada kolom bantu, untuk sheet CETAK-03A dan CETAK-03B menggunakan satu kolom bantu). Jika masih tidak sesuai, mending berikan contoh hasilnya langsung (isi datanya secara manual).
Demikian, semoga sesuai.
-
terima kasih om @Caton , cetak-01A seperti itulah yg saya harapkan, tinggal sy mengujinya pada data yg lebih besar, nanti klo ada error sy kesini lagi, sekali lagi terima kasih, untuk sementara solved pd maslah yg sy hadapi
-
-
Di sunting 6 tahun lalu oleh manweljs_
Mungkin begini
=IF(AND(COUNTIFS($C$8:C8,C8,$F$8:F8,"="&'CETAK-01'!$C$4),I8="Belum"),ROW(A1),"")
- Newer ›