Wa'alikumussalam wr wb Bukannya formula yang ada bisa di-copy ke baris selanjutnya ? Apakah ada yang salah dengan hasil copy formula ke baris lainnya ?
Wa'alaikumussalam wr wb Kid. On Sat, Nov 29, 2014 at 7:31 PM, Samsudin [email protected] [belajar-excel] <[email protected]> wrote: > > > 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] > > 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] > [belajar-excel] <[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] > > 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]> > To: "[email protected]" <[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] > > 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:* 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] > [belajar-excel] <[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] > > 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:* 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] [belajar-excel] > <[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] > > 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:* 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] > [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. > > > > --------------------------------------------------------------------------------------------------- > > > > 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. > > >
