Assalamu’alaikum Mr. Kid,
Terima kasih, yang kemarin bisa digunakan hanya 1 row x 1kolom karena saya ingin dilakukan versi yang lainnya seperti lampiran email tadi. Wassalam, Samsudin, ST O: +62 542 875 860 Ext :61519 | M: +62 811 2810 068 | +62 813 6914 7150 | email: [email protected]<mailto:[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: Saturday, November 29, 2014 4:04 PM To: BeExcel Subject: Re: [belajar-excel] Jarak dan Jumlah Wa'alaikumussalam wr wb Bukannya tempo hari sudah ada formula sumifs ? Sesuaikan saja rujukan cells dalam formulanya. Wassalamu'alaikum wr wb Kid. On Sat, Nov 29, 2014 at 5:14 PM, Samsudin [email protected]<mailto:[email protected]> [belajar-excel] <[email protected]<mailto:[email protected]>> wrote: 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]<mailto:[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. ---------- Forwarded message ---------- From: Samsudin <[email protected]<mailto:[email protected]>> To: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> Cc: Date: Thu, 27 Nov 2014 15:52:26 +0700 Subject: RE: [belajar-excel] Jarak Tempuh/Work hour Assalamu’alaikum, Mr. Kid, Sekali terima kasih atas pencerahannya da nada pertanyaan lagi bagaimana kalau yang ingin diketahui week pada posisi kolom, bagaimana formulanya. Saya ingin menanyakan bagaimana cara untuk meringankan kerja excel karena saya mempunyai salah file terkait dashboard tetapi pada saya file tersebut dibuka akan lambat dan berikut contoh file dan mohon jika pada file tersebut ada saran atau cara yang lebih baik, dimana dapat saya jelaskan bahwa pada file ini yang menjadi ouput adalah sheet dashboard dan file yang lain merupakan pendukung yang di dapat dari file yang lain lagi. Wassalam, Samsudin, ST O: +62 542 875 860 Ext :61519 | M: +62 811 2810 068 | +62 813 6914 7150 | email: [email protected]<mailto:[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]> [mailto:[email protected]<mailto:[email protected]>] Sent: Thursday, November 27, 2014 1:45 PM To: BeExcel Subject: Re: [belajar-excel] Jarak Tempuh/Work hour Wa'alaikumussalam wr wb Coba formula : =SUMIFS(INDEX($E$3:$AI$98,MATCH($B$101,$B$3:$B$98,0),0),$E$2:$AI$2,"<="&RIGHT(D102)*7,$E$2:$AI$2,">="&RIGHT(D102)*7-6) Wassalamu'alaikum wr wb Kid. On Thu, Nov 27, 2014 at 4:31 PM, Samsudin [email protected]<mailto:[email protected]> [belajar-excel] <[email protected]<mailto:[email protected]>> wrote: Assalamu’alaikum Mr. Kid, Sorry nich sampai tidak mudeng dan berikut permasalahannya. Hormat saya, Samsudin, ST O: +62 542 875 860 Ext :61519 | M: +62 811 2810 068 | +62 813 6914 7150 | email: [email protected]<mailto:[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]> [mailto:[email protected]<mailto:[email protected]>] Sent: Wednesday, November 26, 2014 7:52 PM To: BeExcel Subject: Re: [belajar-excel] Jarak Tempuh/Work hour Wa'alaikumussalam wr wb Maaf, ndak mudeng maksudnya. Pakai contoh kerja manual saja supaya lebih jelas maksud nilai total itu apa. Wassalamu'alaikum wr wb Kid. 2014-11-26 19:34 GMT+11:00 Samsudin [email protected]<mailto:[email protected]> [belajar-excel] <[email protected]<mailto:[email protected]>>: Assalamu’alaikum Mr. Kid, Terima kasih atas pencerahannya dan berhasil sesuai dengan harapannya. Ada pertanyaan lagi bagiamana jika kasus/data tersebut dijadikan sebagai nilai total (bukan jarak tempuh), maksudnya data tersebut Week 1 adalah tanggal 1 s/d 7, dst, jika kita ingin mengetahui jumlah total (misalnya data tersebut diasumsikan nilai yang dikeluarkan oleh unit), bagaimana formulanya? Sebelumnya disampaikan terima kasih. wassalam, Samsudin, ST O: +62 542 875 860 Ext :61519 | M: +62 811 2810 068 | +62 813 6914 7150 | email: [email protected]<mailto:[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]> [mailto:[email protected]<mailto:[email protected]>] Sent: Wednesday, November 26, 2014 2:49 PM To: BeExcel Subject: Re: [belajar-excel] Jarak Tempuh/Work hour 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]<mailto:[email protected]> [belajar-excel] <[email protected]<mailto:[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]<mailto:[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]> [mailto:[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]<mailto:[email protected]> [belajar-excel] <[email protected]<mailto:[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<tel:%2B62%20811%202810%20068> | +62 813 6914 7150<tel:%2B62%20813%206914%207150> | email: [email protected]<mailto:[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. --------------------------------------------------------------------------------------------------- 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. --------------------------------------------------------------------------------------------------- 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.
