Hai Selvia,
Selamat bergabung dengan milis Belajar-Excel.
Misalkan, telah diketahui pasti bahwa pada tanggal 1 Maret 2016, jabatannya
adalah A.
Nilai 2016-03-01 ini menjadi acuan kalkulasi.
Pergantian jabatan dilakukan per 6 hari.
Pada tanggal :
2016-03-01, jabatannya menjadi A. Selisih terhadap acuan adalah 0 hari,
yang berarti sudah terjadi 0 kali pergantian
2016-03-02, jabatannya tetap masih A. Selisih terhadap acuan adalah 1 hari,
yang berarti terjadi 0 kali pergantian
2016-03-07, jabatannya menjadi B. Selisih terhadap acuan adalah 6 hari,
yang berarti sudah terjadi 1 kali pergantian
2016-03-10, jabatannya masih B. Selisih terhadap acuan adalah 9 hari, yang
berarti terjadi 1 kali pergantian
Pada selisih hari terhadap acuan adalah 36 hari, berarti telah terjadi 6
kali pergantian alias kembali ke putaran semula.
selisih (hari) x pergantian jabatan menjadi
0 0 A
6 1 B
12 2 C
18 3 D
24 4 E
30 5 F
36 6 A (kembali ke semula)
selisih (hari) didapat dari :
tanggal_data - tanggal_acuan
*** tanggal_data berubah-ubah setiap hari (misal menggunakan fungsi Today()
atau di-input manual sesuka hati)
*** tanggal_acuan adalah tetap (pada contoh di atas, diketahui kondisi
terakhir yang pasti, jabatan berubah menjadi A saat 2016-03-01)
x pergantian adalah jumlah berapa kali pergantian yang telah terjadi pada
tanggal_data sejak tanggal_acuan.
Karena pergantian per 6 hari, maka x pergantian di dapat dari :
pembulatan kebawah terdekat dari [selisih (hari) / 6]
yang di Excel, berbentuk :
=Int( selisih / 6 )
Hasil x pergantian adalah bilangan bulat dari -N sampai +N (termasuk ada 0
disana)
Karena x pergantian bisa menghasilkan nilai > 6, padahal jabatan yang ada
hanyalah 6, maka setiap kali x pergantian bernilai kelipatan 6 harus
menghasilkan nilai x pergantian 0 (setara dengan kembali ke semula).
Sedangkan untuk x pergantian yang bukan kelipatan 6, akan menghasilkan sisa
bagi x pergantian dengan 6.
Contoh :
x pergantian harus menjadi x pergantian supaya jabatannya
menjadi
6
0 A
7 1 = 7 dibagi 6 bersisa 1 B
12
0 A
15 3 = 15 dibagi 6 bersisa 3 D
dst
Bagian [harus menjadi x pergantian] bisa dibahasakan oleh Excel berbunyi :
=*Mod( x pergantian , 6 )*
Jadi, bagian [harus menjadi x pergantian] akan berupa nilai-nilai 0 sampai
5.
Padahal, ada daftar jabatan yang berindeks mulai 1 seperti :
Jabatan ke-1 adalah A
Jabatan ke-2 adalah B
dst
Artinya, supaya bisa merujuk ke Jabatan ke-sekian, maka hasil Mod harus
ditambah 1.
Maka formula :
=*Mod( x pergantian , 6 )*
yang berarti pergantian ke sekiun
berubah menjadi :
=*Mod( x pergantian , 6 )* *+ 1*
yang berarti jabatan ke-sekian
Sekarang, dikumpulkan dulu dari selisih tanggal sampai didapat jabatan
ke-sekian. Formulanya menjadi :
=*Mod( Int( ( tanggal_data - tanggal_acuan ) / 6 ) , 6 ) + 1*
Sudah punya formula untuk mendapatkan jabatan ke-sekian deh...
Tinggal pakai saja...
1. Ada daftar Nama Jabatan yang teurut dari jabatan ke-1 sampai ke-6 di
A1:A6, maka Nama Jabatan di tanggal tertentu (tanggal data) adalah :
=Index( $a$1:$a$6 , *Mod( Int( ( tanggal_data - tanggal_acuan ) / 6 )
, 6 ) + 1* )
2. yah... pengen pakai cara nomor 1, tapi daftar nama jabatannya ditulis
manual aja, maka menjadi :
=Index( { "A" , "B" , "C" , "D" , "E" , "F" } , *Mod( Int( (
tanggal_data - tanggal_acuan ) / 6 ) , 6 ) + 1* )
3. pakai cara nomor 2 kepanjangan, ada cara lainnya gak ?
>> ada, tapi ribet, jadi pakai cara nomor 1 kalau bisa ya...
>> btw, misal PASTI bahwa nama jabatan hanya 1 karakter saja :
=Mid( "ABCDEF" , *Mod( Int( ( tanggal_data - tanggal_acuan ) / 6 ) , 6
) + 1* , 1 )
4. ya sudah, pakai nomor 1 deh. Tapi kan tanggal acuannya dah tetap....
>> begini ?
=Index( $a$1:$a$6 , *Mod( Int( ( tanggal_data - "2016-03-01" ) / 6 ) ,
6 ) + 1* )
atau begini ?
=Index( $a$1:$a$6 , *Mod( Int( ( tanggal_data - Date(2016,3,1) ) / 6
) , 6 ) + 1* )
5. hmmm... kalau tanggal datanya ada di cells G2, G3, G4, dst gimana ?
>> kayanya seh begini....
=Index( $a$1:$a$6 , *Mod( Int( ( G2 - Date(2016,3,1) ) / 6 ) , 6 ) +
1* )
6. ic... tambah pinter saya neh... tapi di sheet lain, tanggal_data nya
ngikutin tanggal hari ini tuh... gimana hayo ?
>> waaah... kalo ini sih beneran sulit... tapi kayanya begini deh.
=Index( $a$1:$a$6 , *Mod( Int( ( Today() - Date(2016,3,1) ) / 6 ) , 6
) + 1* )
*** ingat, JANGAN pakai NOW() karena ketelitian yang dibutuhkan adalah
sampai level hari saja.
7. wow... keren... kalo tanggal_data ngambil dari sheet lain yang berdasar
produk tertentu gimana ?
>> oh begini : [enaknya belajar jadi orang sabar tuh disini]
=Index( $a$1:$a$6 , *Mod( Int( ( vLookUp( kode_produk ,
tabel_tanggal_produk , 2 , 0 ) - Date(2016,3,1) ) / 6 ) , 6 ) + 1* )
8. yaaa.... hasil nomor 7 error tuh... gimana dong..
>> panggil si IFError aja...
=IFError( Index( $a$1:$a$6 , *Mod( Int( ( vLookUp( kode_produk ,
tabel_tanggal_produk , 2 , 0 ) - Date(2016,3,1) ) / 6 ) , 6 ) + 1* ) , "")
9. Oh ok, ok, hehehe... sheet output pertama cuman butuh formula nomer 1
doink kok...
>> gubrak...
10. yaaah... jangan pingsan dulu... pingsannya abis satu ini aja deh...
tanggal data ada yang sebelum tanggal acuan tuh... nomer 1 nya
digimanain ?
>> dipakai ajah apa adanya begono... dah ya.. dah boleh pingsan nih...
gubrak
;)
Kid
2016-02-29 14:40 GMT+07:00 Selvia Pooh [email protected] [belajar-excel]
<[email protected]>:
>
>
> Dear Master,
>
> Perkenalkan saya Selvi, Baru bergabung di grup ini. Mohon bimbingan dan
> pencerahannya mengenai situasi yang sedang saya hadapi :
>
> Di lembar kolom excel, terdapat kolom isi jabatan, dimana pengsian
> departemen itu akan berubah setiap 6 hari sekali. contoh
> departemen A ke B, B ke C dan C ke D yang otomatis berubah setiap 6 hari
> sekali. begitu pula sebaliknya jika sudah ke D akan kembali ke A.
>
> mohon bantuannya Master. Atas segala bantuannya saya ucapkan terimakasih
> banyak
>
>
> *Selvia Liem*
>
>
>
