8 Messages
Digest #3208
Messages
Fri Nov 28, 2014 9:39 pm (PST) . Posted by:
andreas_kodyat
Tinggal diperhitungkan sbb:
Total net yg diterima: Rp 216.000
Ada PPh 2% sebelum total tsb, berarti angka sesudah kena potong tinggal 98%.
Angka awal sebelum dipotong adalah:
Rp 216.000 dibagi 98%
Yaitu: Rp 220.408,163265
PPh 2% : Rp 4.408,163265
Uang yg diterima: Rp 216.000
Regards,
Andreas.
"Reality is simply electrical signals interpreted by your brain."
-----Original Message-----
From: "'Wawan Juhana' wawanjuhana@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
Sender: belajar-excel@yahoogroups.com
Date: Sat, 29 Nov 2014 11:41:23
To: <belajar-excel@yahoogroups.com>
Reply-To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] Solusi Rumus Mark Up dengan PPH 23 pemotongan 2%
Dear para Suhu Exceller,
Mohon solusi/rumus dari perhitungan dibawah ini:
- Terima uang Net Rp 216.000.000,-
- Ketika di potong PPH 23 sebanyak 2%, maka dilakukan mark up untuk mengejar Net Rp 216.000.000
- Jika mark up 2 % = 216.000.000 + (2%*216.000.000)=216.000.000+4.320.000
- Total mark up 2% = Rp 220.320.000
Masalahnya adalah: jika ditagihkan ke customer Rp 220.320.000 lalu dipotong 2% untuk PPH 23, tetapi tidak sama dengan Rp 216.000.000,-
- 2% dari 220.320.000 adalah 4.406.400
- Confuse cari angka total mark up dipotong pajak 2% nilainya 216.000.000
Atas bantuan solusi agan-agan, saya ucapkan terima kasih.
Best Regards,
Wawan
.
<http://geo.yahoo.com/serv?s=97359714/grpId=20627678/grpspId=1708298821/msgId=34614/stime=1417230720>
<http://y.analytics.yahoo.com/fpc.pl?ywarid=515FB27823A7407E&a=10001310322279&js=no&resp=img>
Total net yg diterima: Rp 216.000
Ada PPh 2% sebelum total tsb, berarti angka sesudah kena potong tinggal 98%.
Angka awal sebelum dipotong adalah:
Rp 216.000 dibagi 98%
Yaitu: Rp 220.408,163265
PPh 2% : Rp 4.408,163265
Uang yg diterima: Rp 216.000
Regards,
Andreas.
"Reality is simply electrical signals interpreted by your brain."
-----Original Message-----
From: "'Wawan Juhana' wawanjuhana@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
Sender: belajar-excel@yahoogroups.com
Date: Sat, 29 Nov 2014 11:41:23
To: <belajar-excel@yahoogroups.com>
Reply-To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] Solusi Rumus Mark Up dengan PPH 23 pemotongan 2%
Dear para Suhu Exceller,
Mohon solusi/rumus dari perhitungan dibawah ini:
- Terima uang Net Rp 216.000.000,-
- Ketika di potong PPH 23 sebanyak 2%, maka dilakukan mark up untuk mengejar Net Rp 216.000.000
- Jika mark up 2 % = 216.000.000 + (2%*216.000.000)=216.000.000+4.320.000
- Total mark up 2% = Rp 220.320.000
Masalahnya adalah: jika ditagihkan ke customer Rp 220.320.000 lalu dipotong 2% untuk PPH 23, tetapi tidak sama dengan Rp 216.000.000,-
- 2% dari 220.320.000 adalah 4.406.400
- Confuse cari angka total mark up dipotong pajak 2% nilainya 216.000.000
Atas bantuan solusi agan-agan, saya ucapkan terima kasih.
Best Regards,
Wawan
.
<http://geo.yahoo.com/serv?s=97359714/grpId=20627678/grpspId=1708298821/msgId=34614/stime=1417230720>
<http://y.analytics.yahoo.com/fpc.pl?ywarid=515FB27823A7407E&a=10001310322279&js=no&resp=img>
Fri Nov 28, 2014 11:14 pm (PST) . Posted by:
"Wawan Juhana"
Dear Krisan,
Terima kasih banyak atas rumusnya, ternyata benar dan mudah.
Regards,
Wawan
From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
Sent: Saturday, November 29, 2014 12:10 PM
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Solusi Rumus Mark Up dengan PPH 23 pemotongan 2%
Dear Wawan,
Coba ini 216.000.000 = 98%, jadi rumusnya (100/98)x216.000.000 = 220.408.163 nah sekarang coba dukurangkan 2 % pasti jadi 216.000.000,-
Semoga betul,
Krisan
Pada Sabtu, 29 November 2014 12:46, "'Wawan Juhana' wawanjuhana@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
Dear para Suhu Exceller,
Mohon solusi/rumus dari perhitungan dibawah ini:
- Terima uang Net Rp 216.000.000,-
- Ketika di potong PPH 23 sebanyak 2%, maka dilakukan mark up untuk mengejar Net Rp 216.000.000
- Jika mark up 2 % = 216.000.000 + (2%*216.000.000)=216.000.000+4.320.000
- Total mark up 2% = Rp 220.320.000
Masalahnya adalah: jika ditagihkan ke customer Rp 220.320.000 lalu dipotong 2% untuk PPH 23, tetapi tidak sama dengan Rp 216.000.000,-
- 2% dari 220.320.000 adalah 4.406.400
- Confuse cari angka total mark up dipotong pajak 2% nilainya 216.000.000
Atas bantuan solusi agan-agan, saya ucapkan terima kasih.
Best Regards,
Wawan
.
<http://geo.yahoo.com/serv?s=97359714/grpId=20627678/grpspId=1708298821/msgId=34614/stime=1417230720>
<http://y.analytics.yahoo.com/fpc.pl?ywarid=515FB27823A7407E&a=10001310322279&js=no&resp=img>
Terima kasih banyak atas rumusnya, ternyata benar dan mudah.
Regards,
Wawan
From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
Sent: Saturday, November 29, 2014 12:10 PM
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Solusi Rumus Mark Up dengan PPH 23 pemotongan 2%
Dear Wawan,
Coba ini 216.000.000 = 98%, jadi rumusnya (100/98)x216.000.000 = 220.408.163 nah sekarang coba dukurangkan 2 % pasti jadi 216.000.000,-
Semoga betul,
Krisan
Pada Sabtu, 29 November 2014 12:46, "'Wawan Juhana' wawanjuhana@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
Dear para Suhu Exceller,
Mohon solusi/rumus dari perhitungan dibawah ini:
- Terima uang Net Rp 216.000.000,-
- Ketika di potong PPH 23 sebanyak 2%, maka dilakukan mark up untuk mengejar Net Rp 216.000.000
- Jika mark up 2 % = 216.000.000 + (2%*216.000.000)=216.000.000+4.320.000
- Total mark up 2% = Rp 220.320.000
Masalahnya adalah: jika ditagihkan ke customer Rp 220.320.000 lalu dipotong 2% untuk PPH 23, tetapi tidak sama dengan Rp 216.000.000,-
- 2% dari 220.320.000 adalah 4.406.400
- Confuse cari angka total mark up dipotong pajak 2% nilainya 216.000.000
Atas bantuan solusi agan-agan, saya ucapkan terima kasih.
Best Regards,
Wawan
.
<http://geo.yahoo.com/serv?s=97359714/grpId=20627678/grpspId=1708298821/msgId=34614/stime=1417230720>
<http://y.analytics.yahoo.com/fpc.pl?ywarid=515FB27823A7407E&a=10001310322279&js=no&resp=img>
Dear Bapak Kid
Selamat Siang Pak Kid
Gimana kabarnya dan maaf jika saya merepotkan bapak.
saya coba dengan cara A
saya open with file xml tsb dengan excel maka ada pilihan untuk buka file tsb yaitu :
1. As as XML table
2. As a Read-only workbook
3. Use the XML Source task pane
maka saya pilih no 1 yaitu As as XML table maka file xml tsb terbuka menjadi table di excel maka saya ubah nilai A1 menjadi 123 tetapi
pada saat ke ribbon Developer -> group XML -> klik Export maka muncul warning dari excel 2007 yaitu cannot save or export xml data. The XML maps in this workbook are not
exportable.
Tapi saya open with dengan notepad dan mengubahnya
INVOICENO ( FILE XML : DO-1 )
1001
1001
MENJADI
INVOICENO ( FILE XML : DO-2 )
1002
1002
QUANTITY ( FILE XML : DO-1 )
1
1
MENJADI
QUANTITY ( FILE XML : DO-2 )
2
5
dan file xml berhasil saya import dan menambah data update di form DELIVERY ORDER.
Maka dengan ini saya lampirkan FIle XML DO-1 dan DO-2 sebagai perbedaannya.
Aplikasi yang saya gunakan aplikasi akuntansi yang instalan.
Saya pengen update semua record data tabel dalam satu sheet tersebut untuk di excel dan menyimpannya beberapa record yang berasal
dari form yang sama atau dalam satu file data xml sesuai dengan
mapping xml aplikasinya atau seperti contoh file xml yang saya lampirkan yang yaitu berasal dari form delivery order yang dapat
menyimpan record item lebih dari 2 berdasarkan no do atau delivery order sama yang tersimpan dalam satu file xml saja dan kemudian
saya dapat mengimportnya ke aplikasi tsb.
Mohon bantuannya agar saya dapat mengejarkan di excel dan menyimpannya di data xml.
Terima Kasih
Rgrds
Kana
Selamat Siang Pak Kid
Gimana kabarnya dan maaf jika saya merepotkan bapak.
saya coba dengan cara A
saya open with file xml tsb dengan excel maka ada pilihan untuk buka file tsb yaitu :
1. As as XML table
2. As a Read-only workbook
3. Use the XML Source task pane
maka saya pilih no 1 yaitu As as XML table maka file xml tsb terbuka menjadi table di excel maka saya ubah nilai A1 menjadi 123 tetapi
pada saat ke ribbon Developer -> group XML -> klik Export maka muncul warning dari excel 2007 yaitu cannot save or export xml data. The XML maps in this workbook are not
exportable.
Tapi saya open with dengan notepad dan mengubahnya
INVOICENO ( FILE XML : DO-1 )
1001
1001
MENJADI
INVOICENO ( FILE XML : DO-2 )
1002
1002
QUANTITY ( FILE XML : DO-1 )
1
1
MENJADI
QUANTITY ( FILE XML : DO-2 )
2
5
dan file xml berhasil saya import dan menambah data update di form DELIVERY ORDER.
Maka dengan ini saya lampirkan FIle XML DO-1 dan DO-2 sebagai perbedaannya.
Aplikasi yang saya gunakan aplikasi akuntansi yang instalan.
Saya pengen update semua record data tabel dalam satu sheet tersebut untuk di excel dan menyimpannya beberapa record yang berasal
dari form yang sama atau dalam satu file data xml sesuai dengan
mapping xml aplikasinya atau seperti contoh file xml yang saya lampirkan yang yaitu berasal dari form delivery order yang dapat
menyimpan record item lebih dari 2 berdasarkan no do atau delivery order sama yang tersimpan dalam satu file xml saja dan kemudian
saya dapat mengimportnya ke aplikasi tsb.
Mohon bantuannya agar saya dapat mengejarkan di excel dan menyimpannya di data xml.
Terima Kasih
Rgrds
Kana
Fri Nov 28, 2014 11:14 pm (PST) . Posted by:
"wawan juhana"
Dear Krisan dan Andreas
Terimakasih atas solusinya. Ternyata tepat rumusnya dan mudah di terapkan.
Regards
Wawan
-----Pesan Asli-----
Dari: "Krisan Flower krisan_flower@rocketmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
Terkirim: 29/11/2014 12:12
Kepada: "belajar-excel@yahoogroups.com" <belajar-excel@yahoogroups.com>
Subjek: Bls: [belajar-excel] Solusi Rumus Mark Up dengan PPH 23 pemotongan 2%
Dear Wawan,
Coba ini 216.000.000 = 98%, jadi rumusnya (100/98)x216.000.000 = 220.408.163 nah sekarang coba dukurangkan 2 % pasti jadi 216.000.000,-
Semoga betul,
Krisan
Pada Sabtu, 29 November 2014 12:46, "'Wawan Juhana' wawanjuhana@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
Dear para Suhu Exceller,
Mohon solusi/rumus dari perhitungan dibawah ini:
- Terima uang Net Rp 216.000.000,-
- Ketika di potong PPH 23 sebanyak 2%, maka dilakukan mark up untuk mengejar Net Rp 216.000.000
- Jika mark up 2 % = 216.000.000 + (2%*216.000.000)=216.000.000+4.320.000
- Total mark up 2% = Rp 220.320.000
Masalahnya adalah: jika ditagihkan ke customer Rp 220.320.000 lalu dipotong 2% untuk PPH 23, tetapi tidak sama dengan Rp 216.000.000,-
- 2% dari 220.320.000 adalah 4.406.400
- Confuse cari angka total mark up dipotong pajak 2% nilainya 216.000.000
Atas bantuan solusi agan-agan, saya ucapkan terima kasih.
Best Regards,
Wawan
.
Terimakasih atas solusinya. Ternyata tepat rumusnya dan mudah di terapkan.
Regards
Wawan
-----Pesan Asli-----
Dari: "Krisan Flower krisan_flower@rocketmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
Terkirim: 29/11/2014 12:12
Kepada: "belajar-excel@yahoogroups.com" <belajar-excel@yahoogroups.com>
Subjek: Bls: [belajar-excel] Solusi Rumus Mark Up dengan PPH 23 pemotongan 2%
Dear Wawan,
Coba ini 216.000.000 = 98%, jadi rumusnya (100/98)x216.000.000 = 220.408.163 nah sekarang coba dukurangkan 2 % pasti jadi 216.000.000,-
Semoga betul,
Krisan
Pada Sabtu, 29 November 2014 12:46, "'Wawan Juhana' wawanjuhana@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
Dear para Suhu Exceller,
Mohon solusi/rumus dari perhitungan dibawah ini:
- Terima uang Net Rp 216.000.000,
- Ketika di potong PPH 23 sebanyak 2%, maka dilakukan mark up untuk mengejar Net Rp 216.000.000
- Jika mark up 2 % = 216.000.000 + (2%*216.000.
- Total mark up 2% = Rp 220.320.000
Masalahnya adalah: jika ditagihkan ke customer Rp 220.320.000 lalu dipotong 2% untuk PPH 23, tetapi tidak sama dengan Rp 216.000.000,
- 2% dari 220.320.000 adalah 4.406.400
- Confuse cari angka total mark up dipotong pajak 2% nilainya 216.000.000
Atas bantuan solusi agan-agan, saya ucapkan terima kasih.
Best Regards,
Wawan
.
Fri Nov 28, 2014 11:16 pm (PST) . Posted by:
"Samsudin"
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: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
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.
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: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
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.
Sat Nov 29, 2014 12:03 am (PST) . Posted by:
"Mr. Kid" nmkid.family@ymail.com
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 samsudin@bayan.com.sg
[belajar-excel] <belajar-excel@yahoogroups.com> 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: samsudin@bayan.com.sg
>
> 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 <samsudin@bayan.com.sg>
> To: "belajar-excel@yahoogroups.com" <belajar-excel@yahoogroups.com>
> 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: samsudin@bayan.com.sg
>
> 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:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *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 samsudin@bayan.com.sg
> [belajar-excel] <belajar-excel@yahoogroups.com> 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: samsudin@bayan.com.sg
>
> 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:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *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 samsudin@bayan.com.sg [belajar-excel]
> <belajar-excel@yahoogroups.com>:
>
>
>
> 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: samsudin@bayan.com.sg
>
> 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:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *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 samsudin@bayan.com.sg
> [belajar-excel] <belajar-excel@yahoogroups.com> 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: samsudin@bayan.com.sg
>
> 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:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *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 samsudin@bayan.com.sg
> [belajar-excel] <belajar-excel@yahoogroups.com> 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: samsudin@bayan.com.sg
>
> 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.
>
>
>
>
>
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 samsudin@bayan.com.sg
[belajar-excel] <belajar-excel@yahoogroups.com> 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: samsudin@bayan.com.sg
>
> 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 <samsudin@bayan.com.sg>
> To: "belajar-excel@yahoogroups.com" <belajar-excel@yahoogroups.com>
> 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: samsudin@bayan.com.sg
>
> 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:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *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 samsudin@bayan.com.sg
> [belajar-excel] <belajar-excel@yahoogroups.com> 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: samsudin@bayan.com.sg
>
> 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:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *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 samsudin@bayan.com.sg [belajar-excel]
> <belajar-excel@yahoogroups.com>:
>
>
>
> 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: samsudin@bayan.com.sg
>
> 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:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *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 samsudin@bayan.com.sg
> [belajar-excel] <belajar-excel@yahoogroups.com> 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: samsudin@bayan.com.sg
>
> 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:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *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 samsudin@bayan.com.sg
> [belajar-excel] <belajar-excel@yahoogroups.com> 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: samsudin@bayan.com.sg
>
> 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.
>
>
>
>
>
Sat Nov 29, 2014 12:38 am (PST) . Posted by:
"Samsudin"
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: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
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: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
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 samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg> [belajar-excel] <belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>> 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: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
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 <samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>>
To: "belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>" <belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>>
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: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
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: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com> [mailto:belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>]
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 samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg> [belajar-excel] <belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>> 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: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
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: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com> [mailto:belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>]
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 samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg> [belajar-excel] <belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>>:
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: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
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: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com> [mailto:belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>]
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 samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg> [belajar-excel] <belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>> 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: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
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: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com> [mailto:belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>]
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 samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg> [belajar-excel] <belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>> 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: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
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.
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: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
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: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
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 samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg> [belajar-excel] <belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>> 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: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
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 <samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>>
To: "belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>" <belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>>
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: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
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: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com> [mailto:belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>]
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 samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg> [belajar-excel] <belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>> 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: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
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: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com> [mailto:belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>]
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 samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg> [belajar-excel] <belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>>:
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: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
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: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com> [mailto:belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>]
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 samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg> [belajar-excel] <belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>> 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: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
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: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com> [mailto:belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>]
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 samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg> [belajar-excel] <belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>> 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: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
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.
Sat Nov 29, 2014 12:41 am (PST) . Posted by:
"Mr. Kid" nmkid.family@ymail.com
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 samsudin@bayan.com.sg
[belajar-excel] <belajar-excel@yahoogroups.com> 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: samsudin@bayan.com.sg
>
> 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:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *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 samsudin@bayan.com.sg
> [belajar-excel] <belajar-excel@yahoogroups.com> 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: samsudin@bayan.com.sg
>
> 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 <samsudin@bayan.com.sg>
> To: "belajar-excel@yahoogroups.com" <belajar-excel@yahoogroups.com>
> 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: samsudin@bayan.com.sg
>
> 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:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *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 samsudin@bayan.com.sg
> [belajar-excel] <belajar-excel@yahoogroups.com> 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: samsudin@bayan.com.sg
>
> 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:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *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 samsudin@bayan.com.sg [belajar-excel]
> <belajar-excel@yahoogroups.com>:
>
>
>
> 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: samsudin@bayan.com.sg
>
> 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:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *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 samsudin@bayan.com.sg
> [belajar-excel] <belajar-excel@yahoogroups.com> 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: samsudin@bayan.com.sg
>
> 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:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *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 samsudin@bayan.com.sg
> [belajar-excel] <belajar-excel@yahoogroups.com> 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: samsudin@bayan.com.sg
>
> 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.
>
>
>
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 samsudin@bayan.com.sg
[belajar-excel] <belajar-excel@yahoogroups.com> 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: samsudin@bayan.com.sg
>
> 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:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *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 samsudin@bayan.com.sg
> [belajar-excel] <belajar-excel@yahoogroups.com> 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: samsudin@bayan.com.sg
>
> 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 <samsudin@bayan.com.sg>
> To: "belajar-excel@yahoogroups.com" <belajar-excel@yahoogroups.com>
> 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: samsudin@bayan.com.sg
>
> 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:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *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 samsudin@bayan.com.sg
> [belajar-excel] <belajar-excel@yahoogroups.com> 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: samsudin@bayan.com.sg
>
> 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:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *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 samsudin@bayan.com.sg [belajar-excel]
> <belajar-excel@yahoogroups.com>:
>
>
>
> 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: samsudin@bayan.com.sg
>
> 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:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *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 samsudin@bayan.com.sg
> [belajar-excel] <belajar-excel@yahoogroups.com> 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: samsudin@bayan.com.sg
>
> 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:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *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 samsudin@bayan.com.sg
> [belajar-excel] <belajar-excel@yahoogroups.com> 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: samsudin@bayan.com.sg
>
> 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.
>
>
>
============================================================
Pojok Lowongan Kerja yang disediakan milis :
http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html
*** Posting lowongan kerja : ke link tersebut dan klik New Topic
============================================================
bergabung ke milis (subscribe), kirim mail kosong ke: belajar-excel-subscribe@yahoogroups.com
posting ke milis, kirimkan ke: belajar-excel@yahoogroups.com
berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages
melihat file archive / mendownload lampiran : http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/
menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com
keluar dari membership milis (UnSubscribe), kirim mail kosong ke : belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------
Pojok Lowongan Kerja yang disediakan milis :
http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html
*** Posting lowongan kerja : ke link tersebut dan klik New Topic
============================================================
bergabung ke milis (subscribe), kirim mail kosong ke: belajar-excel-subscribe@yahoogroups.com
posting ke milis, kirimkan ke: belajar-excel@yahoogroups.com
berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages
melihat file archive / mendownload lampiran : http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/
menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com
keluar dari membership milis (UnSubscribe), kirim mail kosong ke : belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------
Tidak ada komentar:
Posting Komentar