Heloo para suhu, mohon bantuannya ya, saya mau buat rangkuman dari data absensi, dimana data yang saya inginkan adalah merangkum jam telat selama satu bulan saja
mohon bantuannya ya
Thx u
Yulius
Heloo para suhu, mohon bantuannya ya, saya mau buat rangkuman dari data absensi, dimana data yang saya inginkan adalah merangkum jam telat selama satu bulan saja
mohon bantuannya ya
Thx u
Yulius
@Yulius... Mohon dibantu dulu, contoh file datanya bagaimana. Klo bisa berikan juga detil informasi atau target yang diharapkan. Mudah-mudahan dengan adanya contoh file datanya, rekan-rekan akan mencoba membantu mencarikan solusinya... ;)
oke tolong di bantu ya, thx u
Menurut saya, untuk menggabungkan nilai waktu dari kolom C sampai dengan kolom AG dengan ketentuan hanya jika nilai waktu pada setiap sel lebih besar dari ketentuan (09.00), bisa dilakukan dengan beberapa cara. Namun sebelumnya, harus dibuatkan dahulu formula untuk memeriksa apakah nilai setiap sel tersebut masuk ke dalam ketentuan. Formula sederhananya:
=IF(C7>$AH$3;TEXT(C7;"hh:mm");"")
Formula di atas kemudian digabungkan menjadi satu dengan fungsi CONCATENATE atau cukup dengan menggunakan operator & (simbol dan, sebagaimana yang mas @Yulius gunakan) menjadi:
=IF(C7>$AH$3;TEXT(C7;"hh:mm");"") &" "& ... &" "& IF(AG7>$AH$3;TEXT(AG7;"hh:mm");"")
atau
=CONCATENATE(IF(C7>$AH$3;TEXT(C7;"hh:mm ");""); ... ;IF(AG7>$AH$3;TEXT(AG7;"hh:mm ");""))
Note: tanda ... (triple dot) dalam formulasi di atas hanya sebagai penanda bahwa formulasi di atas dipotong, karena terlalu panjang. Notasi triple dot tersebut harusnya berisi perulangan formula IF(SEL>$AH$3;TEXT(SEL;"hh:mm");"")
, dimana notasi SEL diganti dengan referensi sel D7 sampai dengan AF7. Lihat gambar....
Demikian cara pertama...
Cara kedua, dilakukan dengan menggunakan Array Formula untuk mendapatkan hasil awal. Sedangkan hasil akhir harus dilakukan secara manual. Tahapannya:
[1]. Pada sel BO7 isi dengan formula berikut:
=CONCATENATE(TRANSPOSE(IF(C7:AG7>$AH$3;TEXT(C7:AG7;"hh:mm ");"")))
[2]. Agar formulanya berfungi, tekan kombinasi tombol CONTROL + SHIFT + ENTER (CSE) untuk mengakhiri proses penulisan formula. Sehingga pada formulanya akan terlihat menjadi seperti berikut:
{=CONCATENATE(TRANSPOSE(IF(C7:AG7>$AH$3;TEXT(C7:AG7;"hh:mm ");"")))}
Note: tanda {} akan muncul secara otomatis setelah tombol CSE ditekan. Kemudian salin formula pada sel BO7 tersebut ke sel-sel dibawahnya.
[3]. Blok parameter di dalam fungsi CONCATENATE.
[4]. Tekan tombol F9 untuk mengubah formulasi menjadi nilai akhir kalkulasi.
[5]. Buang tanda {} pada awal dan akhir nilai akhir kalkulasi tersebut. Akhiri dengan menekan tombol ENTER, atau bisa juga dengan:
[6]. Menekan tombol F9 terlebih dahulu sebelum menekan tombol ENTER.
Gambarannya sebagai berikut:
Demikian cara kedua...
Cara ketiga, dengan menggunakan macro. Saya lebih prefer dengan cara ini karena lebih ringkas. Scriptnya dibuat dalam modul Standard sebagai berikut:
Option Explicit Public Function ConcatDate(Target As Range, CompDate As Date) As Variant If Target.Rows.Count = 1 And IsDate(CompDate) Then Dim xlCell As Range Dim sResult As String For Each xlCell In Target.Columns If xlCell > CompDate Then sResult = sResult & Format$(xlCell, "hh:mm") & String(2, Chr$(32)) End If Next ConcatDate = Trim$(sResult) Else ConcatDate = CVErr(xlErrValue) End If End Function
Cara menggunakannya, sama dengan mengisi formula biasa, masukkan formula berikut pada sel BO7:
=ConcatDate(C7:AG7;$AH$3)
dan salin formula sel BO7 tersebut ke sel-sel dibawahnya... Terlampir file untuk cara pertama dan cara ketiga. Demikian... ;)
Master @Caton... thx u ya atas bantuannya,
Cara ke tiga lebih simple tapi saya tidak paham dengan marco (kalau tidak keberatan mohon ajarin ya master), jadi saya pilih cara yang pertama saja. (saya mohon izin untuk copy paste yang cara satu ya )
Thx u
Silahkan digunakan mas @Yulius, gak perlu izin kok... :) Untuk yang menggunakan macro, khan tinggal digunakan saja, gak harus paham scriptingnya... :) Klo mau belajar scripting VBA Excel, silahkah diskusikan di forum ini, khan di sini memang forum belajar Excel. Bisa dimulai dari diskusi di sini dahulu...
Thx U Master @Caton,
Master saya mau minta bantuannya lagi ya, selama ini saya buat report mingguan secara manual, apakah bisa dibuatan secara lebih singkat. data terlampir
Thx U
Oleh karena mas Yulius menggunakan formula, maka modifikasi saja formula pada sel BP7. Untuk setiap blok Text dalam fungsi CONCATENATE, modifikasi fungsi IF dari:
IF(D7>$AI$3;TEXT(D7;"hh:mm ");"")
menjadi:
IF((D7>$AI$3)*CELL("width";D7);TEXT(D7;"hh:mm ");"")
Jadi yang dilakukan adalah memodifikasi blok Logical Test dari setiap fungsi IF dengan menambahkan tanda buka dan tutup kurung pada formula sebelumnya (yakni D7>$AI$3
menjadi (D7>$AI$3)
) dan kemudian menambahkan formulasi *CELL("width";D7)
. Notasi sel D7 disesuaikan dengan sel acuan pada setiap fungsi IF yang ada di dalam fungsi CONCATENATE tersebut. Jadi untuk blok Text fungsi CONCATENATE akan terlihat seperti:
Yang perlu diperhatikan, setelah menyembunyikan kolom, tekan tombol F9 atau tombol SHIFT+F9 untuk melakukan proses kalkulasi terhadap Workbook atau Worksheet. Hal ini perlu dilakukan karena fungsi CELL tidak otomatis terupdate.
Untuk pertanyaan kedua, saya tidak tahu dari mana acuan W0, W1 atau P0, P1 di dapat. Mungkin mas @Yulius bisa mencoba menggunakan fungsi IF bertingkat untuk formulasinya. Demikian... ;)
Oke, aku coba dulu ya master @Caton
Thx u
Master @Caton, saya sudah tambahkan seperti yang master instruksikan, tapi mengapa tidak bisa sampai dengan kolom AH, maskudnya rumus hanya bisa berfungsi sampai dengan dikolom L.
IF((D7>$AI$3)*CELL("width",D7),TEXT(D7,"hh:mm "),""),
IF((E7>$AI$3)*CELL("width",E7),TEXT(E7,"hh:mm "),""),
IF((F7>$AI$3)*CELL("width",F7),TEXT(F7,"hh:mm "),""),
IF((G7>$AI$3)*CELL("width",G7),TEXT(G7,"hh:mm "),""),
IF((H7>$AI$3)*CELL("width",H7),TEXT(H7,"hh:mm "),""),
IF((I7>$AI$3)*CELL("width",I7),TEXT(I7,"hh:mm "),""),
IF((J7>$AI$3)*CELL("width",J7),TEXT(J7,"hh:mm "),""),
IF((K7>$AI$3)*CELL("width",K7),TEXT(K7,"hh:mm "),""),
IF((L7>$AI$3)*CELL("width",L7),TEXT(L7,"hh:mm "),""),
IF((M7>$AI$3)*CELL(“width",M7),TEXT(M7,"hh:mm "),""),
IF((N7>$AI$3)*CELL(“width",N7),TEXT(N7,"hh:mm "),""),
IF((O7>$AI$3)*CELL(“width",O7),TEXT(O7,"hh:mm "),""),
IF((P7>$AI$3)*CELL(“width",P7),TEXT(P7,"hh:mm "),""),
IF((Q7>$AI$3)*CELL(“width",Q7),TEXT(Q7,"hh:mm "),""),
IF((R7>$AI$3)*CELL(“width",R7),TEXT(R7,"hh:mm "),""),
IF((S7>$AI$3)*CELL(“width",S7),TEXT(S7,"hh:mm "),""),
IF((T7>$AI$3)*CELL(“width",T7),TEXT(T7,"hh:mm "),""),
IF((U7>$AI$3)*CELL(“width",U7),TEXT(U7,"hh:mm "),""),
IF((V7>$AI$3)*CELL(“width",V7),TEXT(V7,"hh:mm "),""),
IF((W7>$AI$3)*CELL(“width",W7),TEXT(W7,"hh:mm "),""),
IF((X7>$AI$3)*CELL(“width",X7),TEXT(X7,"hh:mm "),""),
IF((Y7>$AI$3)*CELL(“width",Y7),TEXT(Y7,"hh:mm "),""),
IF((Z7>$AI$3)*CELL(“width",Z7),TEXT(Z7,"hh:mm "),""),
IF((AA7>$AI$3)*CELL(“width",AA7),TEXT(AA7,"hh:mm "),""),
IF((AB7>$AI$3)*CELL(“width",AB7),TEXT(AB7,"hh:mm "),""),
IF(AC7>$AI$3)*CELL(“width",AC7),TEXT(AC7,"hh:mm "),""),
IF((AD7>$AI$3)*CELL(“width",AD7),TEXT(AD7,"hh:mm "),""),
IF((AE7>$AI$3)*CELL(“width",AE7),TEXT(AE7,"hh:mm "),""),
IF((AF7>$AI$3)*CELL(“width",AF7),TEXT(AF7,"hh:mm "),""),
IF((AG7>$AI$3)*CELL(“width",AG7),TEXT(AG7,"hh:mm "),""),
IF((AH7>$AI$3)*CELL(“width",AH7),TEXT(AH7,"hh:mm "),""))
apa ada batasannya ?
mohon penjelasannya ya master
Thx U
Batasan argumen/parameter fungsi CONCATENATE adalah 255 argumen/parameter. Jadi, pada dasarnya tidak ada masalah batas argumen yang digunakan pada fungsi CONCATENATE tersebut. Demikian pula dengan fungsi IF yang digunakan, tidak ada formulasi IF bertingkat (nested).
Kesalahan pertama ada pada tanda kutip ganda yang digunakan pada argumen Info_Type fungsi CELL. Perhatikan perbedaan tanda kutip ganda yang digunakan diawal notasi "width" untuk kolom L dan kolom M. Kesalahan dimulai dari blok:
IF((M7>$AI$3)*CELL(“width",M7),TEXT(M7,"hh:mm "),"")
sampai dengan blok:
IF((AH7>$AI$3)*CELL(“width",AH7),TEXT(AH7,"hh:mm "),"")
Kemudian kesalahan kedua terletak argumen Logical_Test pada fungsi IF berikut, yakni kekurangan tanda buka kurung:
IF(AC7>$AI$3)*CELL("width",AC7),TEXT(AC7,"hh:mm "),"")
yang seharusnya:
IF((AC7>$AI$3)*CELL("width",AC7),TEXT(AC7,"hh:mm "),"")
Coba perbaiki dahulu sebagaimana yang saya jelaskan di atas. Silahkan diskusikan kembali jika ternyata masih ada masalah atau kendala. Demikian penjelasannya... ;)
Iya betul saya salah, thx u ya master @Caton atas koreksinya
ternyata tanda kutip ganda itu berbeda antara exceldengan yang di word.