Menggabungkan beberapa data dari kolom yang berbeda

  1. 9 bulan yang lalu

    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

  2. Caton

    6 Jan 2018 Terverifikasi Indonesia + 11.522 Poin

    @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... ;)

  3. oke tolong di bantu ya, thx u

  4. Caton

    8 Jan 2018 Terverifikasi Indonesia + 11.522 Poin

    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....

    formula2.png

    Demikian cara pertama...

  5. Caton

    8 Jan 2018 Terverifikasi Indonesia + 11.522 Poin
    Di sunting 9 bulan yang lalu oleh Caton

    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:

    Shoot031.png

    Demikian cara kedua...

  6. Caton

    8 Jan 2018 Terverifikasi Indonesia + 11.522 Poin

    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... ;)

  7. 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

  8. Caton

    9 Jan 2018 Terverifikasi Indonesia + 11.522 Poin

    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...

  9. 8 bulan yang lalu

    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

  10. Caton

    17 Jan 2018 Terverifikasi Indonesia + 11.522 Poin

    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:

    Shoot032.png

    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... ;)

  11. Oke, aku coba dulu ya master @Caton
    Thx u

  12. 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

  13. Caton

    19 Jan 2018 Terverifikasi Indonesia + 11.522 Poin
    Di sunting 8 bulan yang lalu oleh Caton

    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... ;)

  14. Iya betul saya salah, thx u ya master @Caton atas koreksinya
    ternyata tanda kutip ganda itu berbeda antara exceldengan yang di word.

 

atau Mendaftar untuk ikut berdiskusi!