Wa'alaikumussalam wr wb :( susunan datanya membuat susah diolah. apa ndak ada yang lebih gampang didunia ini ya....
btw, mungkin memang kelihatan cakep formulanya kalo disusun yang susah diolah, seperti formula berikut : =LOOKUP(RIGHT(H102)*7+0.5,$E$2:$AI$2/(INDEX($E$3:$AI$98,MATCH($B$101,$B$3:$B$98,0),0)<>""),INDEX($E$3:$AI$98,MATCH($B$101,$B$3:$B$98,0),0))-INDEX($E$3:$AI$98,MATCH($B$101,$B$3:$B$98,0),MATCH(1,INDEX((INT(($E$2:$AI$2-1)/7)=RIGHT(H102)-1)*(INDEX($E$3:$AI$98,MATCH($B$101,$B$3:$B$98,0),0)<>""),0),0)) copy formula ke baris lainnya. *** jika regional setting komputer setempat adalah Indonesian, ganti seluruh karakter koma dalam formula menjadi titik koma Bagian : 1. untuk ambil nilai akhir yang ada nilainya per week LOOKUP(RIGHT(H102)*7+0.5,$E$2:$AI$2/(INDEX($E$3:$AI$98,MATCH($B$101,$B$3:$B$98,0),0)<>""),INDEX($E$3:$AI$98,MATCH($B$101,$B$3:$B$98,0),0)) 2. untuk ambil nilai pertama yang ada nilainya per week INDEX($E$3:$AI$98,MATCH($B$101,$B$3:$B$98,0),MATCH(1,INDEX((INT(($E$2:$AI$2-1)/7)=RIGHT(H102)-1)*(INDEX($E$3:$AI$98,MATCH($B$101,$B$3:$B$98,0),0)<>""),0),0)) susunan formula =Bagian1-Bagian2 Part : 1. berbunyi INDEX($E$3:$AI$98,MATCH($B$101,$B$3:$B$98,0),0) untuk menyusun daftar (array) nilai sebaris (1 baris x n kolom) 2. proses perbandingan dalam formula sebagai proses pemilihan data yang layak diproses. 3. yang menyertakan bunyi seperti RIGHT(H102) untuk konversi nilai week menjadi angka yang dianggap tanggal (bertipe numerik dan bukan bertipe datetime sesuai data yang ada) disesuaikan dengan kebutuhan setiap bagian (butuh batas bawah atau batas atas). moga2 ada yang bersedia menyusun bahasa manusianya :( [repot translate nya] Selamat mencoba Wassalamu'alikum wr wb Kid. On Wed, Nov 26, 2014 at 4:22 PM, Samsudin [email protected] [belajar-excel] <[email protected]> wrote: > > > Assalamu’alaikum, > > > > Mr. Bagus, > > > > Terima kasih atas pencerahananya, dengan formula tersebut memang bisa > digunakan tetapi mempunyai kelemahan jika pada tanggal tertentu data > tersebut kosong atau tidak terisi. > > > > Bagaimana untuk mengatasi masalah ini? > > > > Sebelumnya saya mengucapkan terima kasih. > > > > *Wassalam,* > > > > *Samsudin, ST* > > > > O: +62 542 875 860 Ext :61519 | > > M: +62 811 2810 068 | +62 813 6914 7150 | email: [email protected] > > P Save a tree & save energy. Don't print this e-mail unless it's > really necessary. Go Green !!! > > ISO 9001 : 2008| ISO 14001 : 2004 | OHSAS 18001 : 2007 > > > > *From:* [email protected] [mailto: > [email protected]] > *Sent:* Tuesday, November 25, 2014 2:24 PM > *To:* BExcel > *Subject:* Re: [belajar-excel] Jarak Tempuh/Work hour [1 Attachment] > > > > > > Coba gunakan formula: > > {=MAX(IF($B$101=$B$3:$B$98;$E$3:$K$98;0))-MIN(IF(($B$101=$B$3:$B$98)*($B$101>1);$E$3:$K$98;9^99))} > > sayangnya array formula dan hanya berlaku untuk minggu 1, minggu 2 harus > mengubah E3:K98 menjadi kolom selanjutnya > > > > On Mon, Nov 24, 2014 at 4:24 PM, Samsudin [email protected] > [belajar-excel] <[email protected]> wrote: > > > > *Assalamu’alaikum* > > > > *Dear All Master,* > > > > *Mohon bantuan dan pencerahannya untuk masalah yang saya hadapi seperti > terlampir pada file attachement.* > > > > *Atas perhatian dan pencerahannya.* > > > > > > *Wassalam,* > > > > *Samsudin, ST* > > > > O: +62 542 875 860 Ext :61519 | > > M: +62 811 2810 068 | +62 813 6914 7150 | email: [email protected] > > P Save a tree & save energy. Don't print this e-mail unless it's > really necessary. Go Green !!! > > ISO 9001 : 2008| ISO 14001 : 2004 | OHSAS 18001 : 2007 > > > > --------------------------------------------------------------------------------------------------- > > > > > > This message (including any attachments) contains confidential information > intended > > for a specific individual and purpose, and is protected by law. If you are > not the > > intended recipient, you should delete this message and hereby notified that > any > > disclosure, copying, or distribution of this message (or any part thereof), > or the > > taking of any action based on it, is strictly prohibited. No liability or > responsibility > > is accepted if information or data is, for whatever reason corrupted or does > not reach > > its intended recipient. No warranty is given that this email is free of > viruses. The > > views expressed in this email are, unless otherwise stated, those of the > author and > > not those of the Company or its management. The Company reserves the right to > monitor, > > intercept and block emails addressed to its users or take any other action in > accordance > > with its email use policy. > > > > > --------------------------------------------------------------------------------------------------- > > This message (including any attachments) contains confidential information > intended > for a specific individual and purpose, and is protected by law. If you are > not the > intended recipient, you should delete this message and hereby notified that > any > disclosure, copying, or distribution of this message (or any part thereof), > or the > taking of any action based on it, is strictly prohibited. No liability or > responsibility > is accepted if information or data is, for whatever reason corrupted or does > not reach > its intended recipient. No warranty is given that this email is free of > viruses. The > views expressed in this email are, unless otherwise stated, those of the > author and > not those of the Company or its management. The Company reserves the right to > monitor, > intercept and block emails addressed to its users or take any other action in > accordance > with its email use policy. > > >
