Rumus Excel

Q: Bagaimana cara menentukan agama otomatis di Excel?

Gunakan fungsi MID dan SWITCH: =SWITCH(MID(C3;3;1);"I";"Islam";"N";"Nasrani";"B";"Budha";"H";"Hindu";"Lainnya")

Rumus Excel untuk Menentukan Status Pernikahan, Agama dan Status Karyawan Otomatis

noreply@blogger.com (Masdad Al-Falucky) — Sat, 25 Oct 2025 22:58:00 +0700

Rumus Excel [.] com - Mengelola data karyawan di Excel adalah tugas yang umum bagi HRD, admin personalia, atau staf administrasi. Biasanya, file berisi daftar nama, nomor pegawai, status pernikahan, agama, dan jenis karyawan (kontrak, tetap, atau lepas).

Sayangnya, banyak yang masih mengisi data ini secara manual, padahal Excel punya kemampuan luar biasa untuk mengotomatisasi proses tersebut.

Pada artikel ini, Kita akan belajar cara membuat rumus Excel otomatis untuk menentukan:

Status pernikahan (Kawin, Belum Kawin, Duda atau Janda).
Status Karyawan (Kontrak, Tetap, atau Lepas).
Agama Karyawan (Islam, Nasrani, Budha, Hindu dan Lainnya).

dengan menggunakan Fungsi IF dan Fungsi SWITCH sebagai rumus alternatifnya.

Untuk data yang akan kita cari status pernikahan, agama dan status karyawannya adalah sebagai barikut:

Yang dijadikan kunci pencarian adalah kolom C atau yang berisi Nomor Pegawai, dengan ketentuan:

Karakter Pertama menunjukkan Status Pernikahan:
- K = Kawin.
- B = Belum Kawin.
- J = Janda.
- D = Duda.
Karakter Kedua menunjukkan Status Karyawan:
- C = Kontrak.
- T = Tetap.
- L = Lepas.
Karakter Ketiga menunjukkan Agama Karyawan:
- I = Islam.
- N = Nasrani.
- B = Budha.
- H = Hindu.
- L = Lainnya.
untuk 4 karakter terakhir menunjukkan No. Induk Karyawan, diabaikan saja, karena fokus kita hanya mencari status pernikahan, agama dan status karyawan.

Rumus Excel untuk Status Pernikahan

Kita mulai dari sel D3 untuk menentukan status pernikahan, dengan ketentuan jika karakter pertama pada sel C3 adalah:

K maka hasilnya adalah Kawin.
B maka hasilnya adalah Belum Kawin.
J maka hasilnya adalah Janda.
D maka hasilnya adalah Duda.

untuk itu kita bisa memggunakan Fungsi IF Bertingkat, jadi rumus excel pada sel D3 adalah:

=IF(LEFT(C3)="K";"Kawin";IF(LEFT(C3)="B";"Belum Kawin";IF(LEFT(C3)="J";"Janda";"Duda")))

Penjelasan:

Fungsi IF digunakan untuk menentukan status pernikahan, dengan menggunakan Fungsi LEFT untuk mencari karakter pertama dari sel C3

Hasilnya adalah Kawin karena karakter pertama pada sel C3 adalah K.

Selain IF, kita juga bisa menggunakan Fungsi SWITCH yang lebih singkat dan lebih praktis. seperti ini:

=SWITCH(LEFT(C3); "K"; "Kawin"; "B"; "Belum Kawin"; "J"; "Janda"; "Duda")

Fungsi SWITCH ini tersedia mulai Excel 2019.

Rumus Excel untuk Status Karyawan

Selanjutnya adalah mencari status Karyawan, caranya sama dengan mencari status pernikahan, yaitu menggunakan fungsi IF atau fungsi SWITCH.

Rumus Excel pada sel E3 adalah:

=IF(MID(C3;2;1)="C";"Kontrak";IF(MID(C3;2;1)="T";"Tetap";"Lepas"))

atau jika menggunakan fungsi SWITCH:

=SWITCH(MID(C3;2;1);"C";"Kontrak";"T";"Tetap";"Lepas")

Penjelasan:

Rumus MID(C3;2;1) digunakan untuk menemukan karakter kedua dari sel C3, hasilnya adalah C.

Kemudian Fungsi IF atau SWITCH menggunakannya sebagai logical test atau Expression, yang selanjutnya dicoocokkan dengan value dan result yang sesuai.

Hasilnya adalah Kontrak karena karakter kedua pada sel C3 adalah C. Sesuai dengan ketentuan jika:

C Hasilnya adalah Kontrak.
T Hasilnya adalah Tetap.
FALSE Hasilnya adalah Lepas.

Rumus Excel untuk Agama Karyawan

Untuk kolom agama, rumus excel yang digunakan kurang lebih sama dengan menentukan status karyawan, yaitu menggunakan Fungsi IF dan MID.

bedanya untuk argumen start_num pada Fungsi MID di isi 3, karena kunci untuk mengetahui agama karyawan ada di karakter ke-3 dari Nomor Pegawai.

Dan Rumus Excel pada sel F3 adalah:

=IF(MID(C3;3;1)="I";"Islam";IF(MID(C3;3;1)="N";"Nasrani";IF(MID(C3;3;1)="B";"Budha";IF(MID(C3;3;1)="H";"Hindu";"Lainnya"))))

dan jika ingin menggunakan fungsi SWITCH:

=SWITCH(MID(C3;3;1);"I";"Islam";"N";"Nasrani";"B";"Budha";"H";"Hindu";"Lainnya")

Hasilnya adalah Islam.

Untuk rumus excel pada baris-baris di bawahnya, silahkan salin atau lakukan drag drop.

FAQ

1. Bagaimana cara menentukan status pernikahan otomatis di Excel?

Gunakan fungsi LEFT dan IF untuk membaca karakter pertama dari kode karyawan. Contoh:

=SWITCH(LEFT(C3); "K"; "Kawin"; "B"; "Belum Kawin"; "J"; "Janda"; "Duda")

2. Bagaimana cara menentukan agama otomatis di Excel?

Gunakan MID dan SWITCH:

=SWITCH(MID(C3;3;1);"I";"Islam";"N";"Nasrani";"B";"Budha";"H";"Hindu";"Lainnya")

3. Apa fungsi SWITCH di Excel?

Fungsi SWITCH membantu mengganti penggunaan IF bertingkat agar rumus lebih singkat dan mudah dibaca.

4. Fungsi SWITCH bisa dipakai di semua versi Excel

Fungsi SWITCH mulai tersedia di Excel 2019 atau Microsoft 365. Untuk versi lama, gunakan IF bertingkat sebagai alternatif.

Kesimpulan

Dengan memanfaatkan fungsi logika di Excel seperti IF, LEFT, MID, dan SWITCH, kita bisa meng-otomatisasi penentuan status-status berdasarkan kode karakter di suatu kolom.
Pendekatan ini mempercepat dan meningkatkan konsistensi dalam pengolahan data pegawai (termasuk data terkait status pernikahan, agama, dan jenis karyawan).
Penggunaan SWITCH memberi alternatif yang lebih sederhana dibandingkan menggunakan IF bertingkat, namun hanya tersedia di versi Excel tertentu (misalnya mulai Excel 2019).
Cara ini sangat berguna untuk admin personalia atau staf HRD yang rutin mengelola data pegawai dengan format kode serupa.

Lampiran

Fungsi Excel IF vs SWITCH Excel Workbook (xlsx)

Download

Sekian tutorial tentang Cara Membuat Rumus Excel untuk Menentukan Status Pernikahan, Agama dan Status Karyawan Otomatis, Semoga Bermanfaat...

Daftar Fungsi Date & Time di Excel, Lengkap dengan contohnya

noreply@blogger.com (Masdad Al-Falucky) — Fri, 10 Oct 2025 10:42:00 +0700

Rumus Excel [.] com - Fungsi-fungsi kategori Date & Time (Tanggal dan Waktu) adalah fungsi-fungsi Excel yang tersedia khusus untuk menangani pengolahan data Tanggal dan Waktu di Excel.

Fungsi-Fungsi ini sangat berguna dalam berbagai hal, misalnya untuk menghitung jam kerja, menghitung lama kerja, membuat kalender otomatis dan masih banyak lagi.

Sampai saat ini setidaknya ada 25 Fungsi Excel yang ada dalam kategori Date & Time, dan berikut nama-nama fungsi serta contoh penggunaannya:

01. Fungsi Excel DATE

Fungsi Excel DATE digunakan untuk membuat nilai tanggal berdasarkan tiga argumen utama: tahun, bulan, dan hari.

Sintaksnya adalah:

=DATE(tahun, bulan, hari)

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel E3 adalah =DATE(B3;C3;D3).

02. Fungsi Excel DATEDIF

Fungsi Excel DATEDIF digunakan untuk menghitung selisih antara dua tanggaldalam satuan tertentu, seperti tahun, bulan, atau hari.

Sintaksnya adalah:

=DATEDIF(start_date, end_date, unit)

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel E3 adalah =DATEDIF(B3;C3;D3).

03. Fungsi Excel DATEVALUE

Fungsi Excel DATEVALUE digunakan untuk mengubah teks yang berisi tanggal menjadi nilai serial tanggal yang dapat dikenali dan digunakan dalam perhitungan tanggal.

Sintaksnya adalah:

=DATEVALUE(date_text)

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel C3 adalah =DATEVALUE(B3).

04. Fungsi Excel DAY

Fungsi Excel DAY digunakan untuk mengambil atau mengekstrak nilai hari dari suatu tanggal.

Sintaksnya adalah:

=DAY(serial_number)

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel C3 adalah =DAY(B3).

05. Fungsi Excel DAYS

Fungsi Excel DAYS digunakan untuk menghitung jumlah hari antara dua tanggal.

Sintaksnya adalah:

=DAYS( end_date, start_date )

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel D3 adalah =DAYS(C3;B3).

06. Fungsi Excel DAYS360

Fungsi Excel DAYS360 digunakan untuk menghitung jumlah hari antara dua tanggal berdasarkan tahun 360 hari (12 bulan × 30 hari), bukan kalender sebenarnya.

Sintaksnya adalah:

=DAYS360( start_date, end_date, [method] )

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel D3 adalah =DAYS360(B3;C3).

07. Fungsi Excel EDATE

Fungsi Excel EDATE digunakan untuk menghitung tanggal baru berdasarkan penambahan atau pengurangan sejumlah bulan dari tanggal awal tertentu.

Sintaksnya adalah:

=EDATE( start_date, months )

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel D3 adalah =EDATE(B3;C3).

08. Fungsi Excel EOMONTH

Fungsi Excel EOMONTH digunakan untuk menghitung tanggal pada hari terakhir bulan tertentu berdasarkan tanggal awal dan jumlah bulan yang ditentukan.

Sintaksnya adalah:

=EOMONTH( start_date, months )

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel C3 adalah =EOMONTH(B1;-1)+1.
Rumus Excel Pada Sel C4 adalah =EOMONTH(B1;0).
Rumus Excel Pada Sel C5 adalah =EOMONTH(B1;1).

09. Fungsi Excel HOUR

Fungsi Excel HOUR digunakan untuk mengambil atau mengekstrak nilai jam dari sebuah data waktu.

Sintaksnya adalah:

=HOUR(serial_number)

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel C3 adalah =HOUR(B3).

10. Fungsi Excel ISOWEEKNUM

Fungsi Excel ISOWEEKNUM digunakan untuk menentukan nomor minggu dalam tahun berdasarkan standar ISO 8601, di mana minggu pertama dimulai pada Senin dan mencakup hari Kamis pertama dalam tahun tersebut.

Sintaksnya adalah:

=ISOWEEKNUM (date)

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel C3 adalah =ISOWEEKNUM(B3).

11. Fungsi Excel MINUTE

Fungsi Excel MINUTE digunakan untuk mengambil atau mengekstrak nilai menit dari suatu waktu.

Sintaksnya adalah:

=MINUTE(serial_number)

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel C3 adalah =MINUTE(B3).

12. Fungsi Excel MONTH

Fungsi Excel MONTH digunakan untuk mengambil atau mengekstrak nilai bulan dari sebuah tanggal.

Sintaksnya adalah:

=MONTH(serial_number)

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel C3 adalah =MONTH(B3).

13. Fungsi Excel NETWORKDAYS

Fungsi Excel NETWORKDAYS digunakan untuk menghitung jumlah hari kerja antara dua tanggal, dengan otomatis mengecualikan hari Sabtu dan Minggu.

Sintaksnya adalah:

=NETWORKDAYS(Start_date,End_date,[holidays])

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel L5 adalah =NETWORKDAYS(L3;L4).

14. Fungsi NETWORKDAYS.INTL

Fungsi Excel NETWORKDAYS.INTL digunakan untuk menghitung jumlah hari kerja antara dua tanggal dengan fleksibilitas lebih tinggi dibanding NETWORKDAYS.

Sintaksnya adalah:

=NETWORKDAYS.INTL(Start_date,End_date,[weekend],[holidays])

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel L5 adalah =NETWORKDAYS.INTL(L3;L4;11).

15. Fungsi Excel NOW

Fungsi Excel NOW digunakan untuk menampilkan tanggal dan waktu saat ini sesuai dengan pengaturan sistem komputer.

Sintaksnya adalah:

=NOW()

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel C3 adalah =NOW().

16. Fungsi Excel SECOND

Fungsi Excel SECOND digunakan untuk mengambil atau mengekstrak nilai detik dari sebuah data waktu.

Sintaksnya adalah:

=SECOND(serial_number)

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel C3 adalah =SECOND(B3).

17. Fungsi Excel TIME

Fungsi Excel TIME digunakan untuk membuat nilai waktu berdasarkan tiga komponen utama: jam, menit, dan detik.

Sintaksnya adalah:

=TIME(jam, menit, detik)

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel E3 adalah =TIME(B3;C3;D3).

18. Fungsi Excel TIMEVALUE

Fungsi Excel TIMEVALUE digunakan untuk mengonversi teks yang berisi waktu menjadi nilai desimal yang mewakili waktu dalam sistem tanggal dan waktu Excel.

Sintaksnya adalah:

=TIMEVALUE(time_text)

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel C3 adalah =TIMEVALUE(B3).

19. Fungsi Excel TODAY

Fungsi Excel TODAY digunakan untuk menampilkan tanggal hari ini secara otomatis sesuai dengan sistem komputer.

Sintaksnya adalah:

=TODAY()

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel C3 adalah =TODAY().

20. Fungsi Excel WEEKDAY

Fungsi Excel WEEKDAY digunakan untuk menentukan hari dalam seminggu dari suatu tanggal dan mengembalikannya dalam bentuk angka antara 1 hingga 7.

Sintaksnya adalah:

=WEEKDAY(serial_number, [return_type])

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel D3 adalah =WEEKDAY(B3;C3).

21. Fungsi Excel WEEKNUM

Fungsi Excel WEEKNUM digunakan untuk menentukan nomor minggu dalam satu tahun dari suatu tanggal tertentu.

Sintaksnya adalah:

=WEEKNUM(serial_number, [return_type])

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel D3 adalah =WEEKNUM(B3;C3).

22. Fungsi Excel WORKDAY

Fungsi Excel WORKDAY digunakan untuk menghitung tanggal kerja berdasarkan jumlah hari kerja tertentu dari suatu tanggal awal.

Sintaksnya adalah:

=WORKDAY(Start_date,Days,[holidays])

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel L5 adalah =WORKDAY(L3;L4).

23. Fungsi Excel WORKDAY.INTL

Fungsi Excel WORKDAY.INTL digunakan untuk menghitung tanggal kerja berdasarkan jumlah hari kerja tertentu dari suatu tanggal awal, dengan opsi akhir pekan (weekend) yang bisa ditentukan.

Sintaksnya adalah:

=WORKDAY.INTL(Start_date,Days,[weekend],[holidays])

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel L5 adalah =WORKDAY.INTL(L3;L4;11).

24. Fungsi Excel YEAR

Fungsi Excel YEAR digunakan untuk mengambil atau mengekstrak nilai tahun dari sebuah tanggal.

Sintaksnya adalah:

=YEAR(serial_number)

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel C3 adalah =YEAR(B3).

25. Fungsi Excel YEARFRAC

Fungsi Excel YEARFRAC digunakan untuk menghitung selisih antara dua tanggal dalam bentuk pecahan tahun.

Sintaksnya adalah:

=YEARFRAC(start_date, end_date, [basis])

Contohnya:

[ Unduh Contoh File-nya ]

Rumus Excel Pada Sel D3 adalah =YEARFRAC(B3;C3).

Itulah Daftar Lengkap dari Fungsi Date & Time Excel, agar lebih mudah memahami penggunaan semua fungsinya, Anda bisa mendownload File-File Conttohnya. Semoga Bermanfaat.

Fungsi Excel TRANSLATE dan Fungsi DETECTLANGUAGE

noreply@blogger.com (Masdad Al-Falucky) — Thu, 8 Aug 2024 10:31:00 +0700

Rumus Excel [.] com - Dua fungsi yang baru di tambahkan di Excel adalah Fungsi TRANSLATE dan Fungsi DETECTLANGUAGE, fungsi ini sementara baru tersedia untuk pengguna Beta Channel (Insider) Excel pada Windows versi 2407 (Build 16.0.17808.20000) dan Mac versi 16.87 (Build 24062430).

Fungsi TRANSLATE digunakan untuk menerjemahkan teks dari satu bahasa ke bahasa lain di Microsoft Excel dengan menggunakan sumber data dari Microsoft Translation Services.

Sedangkan Fungsi DETECTLANGUAGE digunakan untuk mengidentifikasi bahasa yang digunakan dalam teks atau rentang tertentu.

Sintaksis Fungsi TRANSLATE

Sintaks untuk fungsi TRANSLATE adalah:

=TRANSLATE(text; [source language]; [target language])

Keterangan:

Text: Teks yang akan diterjemahkan.
Nilai ini harus diapit dalam tanda kutip atau menjadi referensi ke sel yang berisi teks yang sesuai.
source_language (opsional): Kode bahasa dari bahasa sumber (misalnya: "en" untuk bahasa Inggris atau "id" untuk bahasa Indonesia).

Jika tidak ditentukan, bahasa akan terdeteksi secara otomatis berdasarkan teks yang disediakan.

Untuk kode bahasa yang dapat digunakan, Anda bisa melihat daftarnya disini.
target_language (opsional): Kode bahasa dari bahasa target (misalnya: "en" untuk bahasa Inggris atau "id" untuk Indonesia).

Jika tidak ditentukan, bahasa sistem akan digunakan sebagai bahasa target.

Contoh Fungsi TRANSLATE:

=TRANSLATE("Welcome"; "en"; "id") // hasilnya Selamat Datang

Menerjemahkan teks Welcome dari bahasa inggris menjadi bahasa Indonesia Selamat Datang.

atau seperti contoh dibawah ini dari bahasa inggris Hello, world! menjadi bahasa spanyol Hola mundo.

image source: insider.microsoft365.com

Sintaksis Fungsi DETECTLANGUAGE

Sintaks untuk fungsi DETECTLANGUAGE adalah:

=DETECTLANGUAGE(text)

Keterangan:

text - Teks atau referensi ke sel yang berisi teks untuk dievaluasi.

Contoh Fungsi DETECTLANGUAGE:

=DETECTLANGUAGE("Selamat Datang") // hasilnya id

Excel mendeteksi teks Selamat Datang sebagai bahasa Indonesia, dan menampilkan hasil kode untuk bahasa Indonesia yaitu id.

atau seperti contoh dibawah ini, teks Hola mundo di deteksi sebagai bahasa spanyol, dan menghasilkan kode bahasa es.

image source: insider.microsoft365.com

Sekian artikel tentang Fungsi TRANSLATE dan Fungsi DETECTLANGUAGE di Excel, Semoga bermanfaat.

Cara Membuat Nomor 1-100 di Excel dengan Fill Series

noreply@blogger.com (Masdad Al-Falucky) — Tue, 30 Jul 2024 10:04:00 +0700

Rumus Excel [.] com - Menuliskan angka 1-100 pada Excel bisa dilakukan dengan banyak cara, mulai dari cara manual dengan mengetikkan angka satu persatu, cara otomatis dengan menggunakan Fungsi SEQUENCE, ataupun menggunakan beberapa cara seperti pada artikel cara cepat membuat nomor otomatis.

Untuk kali ini kita akan belajar membuat nomor 1-100 di Excel dengan menggunakan salah satu fitur yang sudah tersedia di Excel, yaitu fitur Fill Series.

Fitur Fill Series dapat diakses melalui menu Home, pada grup Ribbon Editing pilih Fill - Series. atau bisa menggunakan Shortcut Alt, H, FI, S.

Membuat Nomor 1-100 di Excel ke bawah

Untuk memasukkan nomor otomatis dari baris pertama ke bawah sampai baris ke 100, caranya adalah:

Pada sel A1 ketikkan angka 1.
Kemudian buka menu Fill Series.
Pada pilihan Series in pilih Columns, Step Value 1 dan Stop Value 100. Kemudian klik OK.
Hasilnya pada sel A1 sampai dengan A100 akan terisi angka 1-100.

Membuat Nomor 1-100 di Excel ke samping

Untuk memasukkan nomor otomatis 1-100 dengan Fill Series mulai dari sel A1 ke samping atau dari kolom A sampai dengan kolom CV, caranya adalah:

Ketikkan angka 1 di kolom pertama atau sel A1.
Bukan fitur/menu Fill Series.
Pada pilihan Series in, pilih Rows, Step value isikan 1 dan Stop value isi 100, kemudian klik OK.
Hasilnya pada kolom A sampai kolom CV di baris pertama akan terisi angka 1-100.

Sekian tutorial cara membuat nomor 1-100 di Excel, yang bisa dijadikan alternatif pilihan untuk membuat nomor otomatis di Excel tanpa harus menggunakan rumus excel, Semoga Bermanfaat.

UDF TEXTJOIN, Fungsi TEXTJOIN untuk Excel 2007 - 2016

noreply@blogger.com (Masdad Al-Falucky) — Mon, 15 Jul 2024 12:03:00 +0700

Rumus Excel [.] com - Fungsi TEXTJOIN adalah salah satu fungsi baru di Excel 2019, fungsi ini dapat digunakan untuk menggabungkan data dari beberapa sel / range dengan pembatas tertentu, selengkapnya untuk cara penggunaannya, bisa Anda baca di artikel cara menggunakan Fungsi TEXTJOIN.

Karena baru ditambahkan mulai Excel 2019, maka untuk pengguna excel yang masih menggunakan versi dibawahnya, seperti Excel 2016, Excel 2013, Excel 2010 dan Excel 2007 otomatis tidak dapat menggunakan Fungsi TEXTJOIN ini karena memang belum tersedia.

Jika ingin di Excel 2016 ke bawah ada fungsi TEXTJOIN maka salah satu caranya adalah membuat sendiri fungsi tersebut dengan VBA atau istilahnya User Defined Function (UDF).

Namun saran saya baiknya sih upgrade Excelnya ke versi terbaru, jadi gak perlu repot bikin UDF seperti ini 😃

Contoh hasil dari UDF TEXTJOIN di Excel 2007

Sengaja untuk praktek bikin UDF-nya menggunakan Excel 2007, kalau di Excel 2007 berhasil, tentu di versi yang lebih baru (Excel 2010, Excel 2013 dan Excel 2016) pasti juga akan berhasil,

Cara Membuat UDF TEXTJOIN

Untuk membuat UDF (user defined function) TEXTJOIN dengan VBA Excel caranya adalah sebagai berikut:

Buka aplikasi Microsoft Visual Basic, bisa melalui Tab Developer - Visual Basic, atau bisa juga menggunakan shortcut Alt + F11.
Tambahkan Module baru pada VBA Project, dengan cara klik menu Insert - Module.

Pada Module masukkan script vba berikut:


Option Explicit

Function TEXTJOIN( Delimiter As String, _
    Ignore_empty As Boolean, _
    ParamArray Text() As Variant) As String
Dim x, i As Long, pemisah As String, teks As String
For i = LBound(Text) To UBound(Text)
    If IsArray(Text(i)) Then
        For Each x In Text(i)
            teks = IIf(IsMissing(x), "", x)
            If Not (Ignore_empty And teks = "") Then
                TEXTJOIN = TEXTJOIN & pemisah & teks
                pemisah = Delimiter
            End If
        Next x
    Else
        teks = IIf(IsMissing(Text(i)), "", Text(i))
        If Not (Ignore_empty And teks = "") Then
            TEXTJOIN = TEXTJOIN & pemisah & teks
            pemisah = Delimiter
        End If
    End If
Next i
End Function

Jika Anda memasukkan kode VBA ini di Excel 2019, Excel 2021 atau di Excel 365 maka Anda harus mengubah Nama UDF-nya, agar tidak bentrok dengan fungsi TEXTJOIN yang sudah ada di Excel 2019, Excel 2021 dan Excel 365, misalnya di ubah namanya menjadi JOINTEXT atau GABUNGTEKS dsb.

Sekarang harusnya UDF TEXTJOIN sudah bisa digunakan, bisa di coba mengetikkan =te harusnya fungsi TEXTJOIN-nya akan ditampilkan.

Sintaksis UDF TEXTJOIN

Untuk penulisan sintaksis UDF TEXTJOIN sama dengan Fungsi TEXTJOIN.

=TEXTJOIN (delimiter; ignore_empty; text1, [text2], …)

Keterangan:

delimiter : pemisah antar teks yang digabungkan.
ignore_empty : pilihan mengabaikan sel kosong, bisa di isi 1 atau TRUE dan 0 atau FALSE.
text1, text2 dst : teks, sel atau range yang isinya ingin digabungkan.

Contoh pertama penggunaan UDF TEXTJOIN:

=TEXTJOIN(" ";TRUE;A1:B1)

hasilnya:

Contoh kedua UDF TEXTJOIN:

=TEXTJOIN(" ";TRUE;A2:B3)

hasilnya:

Setelah Fungsi TEXTJOIN berhasil digunakan, Anda bisa menyimpan file Excel-nya dalam bentuk *.xls. *.xlsm atau *.xlsb agar kode VBA-nya bisa ikut tersimpan dan tidak hilang. Anda juga bisa menyimpannya dalam format *.xlam jika ingin menjadikan Fungsi TEXTJOIN ini sebagai Add-in Excel.

Sekian tutorial tentang cara membuat UDF TEXTJOIN di Excel, Semoga Bermanfaat.

Add-in Change Case, Mengubah Huruf Kapital di Excel tanpa rumus

noreply@blogger.com (Masdad Al-Falucky) — Wed, 10 Jul 2024 10:17:00 +0700

Rumus Excel [.] com - Jika Anda terbiasa menggunakan office Word, Anda tentu tidak terlalu asing dengan istilah/fitur Change Case. Fitur Change case dapat digunakan untuk mengubah huruf besar atau huruf kapital menjadi huruf kecil, mengubah huruf kecil menjadi huruf besar, mengubah awalan kata menjadi huruf besar dan mengubah awal kalimat berupa huruf besar.

Di Excel fitur Change case ini tidak tersedia, namun di Excel tersedia fungsi-fungsi yang dapat digunakan untuk mengolah kata, yaitu:

Fungsi UPPER : digunakan untuk mengubah huruf kecil menjadi huruf besar (kapital).
Fungi LOWER : digunakan untuk mengubah huruf kapital (besar) menjadi huruf kecil.
Fungsi PROPER : digunakan untuk mengubah huruf awal di setiap kata menjadi besar.

Bagi Anda yang tidak ingin mengolah kata menggunakan rumus, Anda bisa memasang Add-in Change Case, Add-in ini bukan resmi dari microsoft, melainkan hasil karya saya sendiri, yang memang saya buat untuk memudahkan mengubah huruf kapital tanpa perlu menggunakan rumus excel.

Add-in Change Case Excel ini bisa Anda pasang di Excel versi 2007, Excel 2010, Excel 2016, Excel 2019, Excel 2021 dan Excel 365.

Add-in Change Case ini sebenarnya sudah lama saya bagikan di Channel Youtube Rumus Excel, di deskripsi video Cara Memasang atau Menambahkan Add Ins Excel, jadi bagi Anda yang belum tahu cara memasang Add-in di Excel Anda bisa mengikuti tutorial pada video ini.

Download Add-in Change Case untuk Excel

Add-in Change Case Excel Add-in (xlam)

Download

Cara menggunakan Add-In Change Case

Setelah Anda berhasil melakukan pemasangan Add-in Change Case, maka di menu Home akan bertambah satu menu baru, yaitu menu change case.

Pada menu Change Case ini terdapat 4 Fitur utama:

UPPERCASE
Fitur ini dapat Anda gunakan untuk mengubah huruf kecil menjadi huruf kapital / huruf besar.

Cara penggunaannya adalah blok terlebih dahulu sel/range yang berisi huruf-huruf yang ingin di ubah menjadi huruf besar semua, setelah itu klik Change case - UPPERCASE dan tunggu prosesnya sampai selesai.
lowercase
Fitur lowercase ini fungsinya kebalikan dari UPPERCASE yaitu mengubah huruf kapital menjadi huruf kecil semua.

Cara penggunaannya: blok sel/range kemudian klik Change case - lowercase.
Capitalize Each Word.
Fitur ini untuk mengubah Awal huruf dari setiap teks menjadi besar, contoh rumus excel menjadi Rumus Excel.

Cara penggunaannya: blok sel/range kemudian klik Change case - Capitalize Each Word.
Sentence case
Fitur ini untuk mengubah Awal huruf dari teks pertama menjadi huruf besar, dan untuk teks selanjutnya menjadi huruf kecil semua. contoh rumus excel menjadi Rumus excel.

cara penggunaannya: blok sel/range kemudian klik Change case - Sentence case.

Agar proses mengubah huruf pada add-in change case ini tidak terlalu lambat, sengaja di Add-in ini di batasi maksimal 3 ribu sel yang dapat di pilih atau di blok untuk sekali pemrosesan.

Jadi jika Anda memiliki teks di lebih dari 3 ribu sel, Anda tidak dapat mengubahnya sekaligus, melainkan harus di ulang beberapa kali.

Sekian tutorial cara penggunaan fitur change case untuk mengubah huruf, baik itu huruf kecil maupun huruf kapital di excel tanpa rumus, semoga bermanfaat.

Fungsi TRIM, Rumus Excel Menghilangkan Spasi Berlebih

noreply@blogger.com (Masdad Al-Falucky) — Tue, 9 Jul 2024 11:52:00 +0700

Rumus Excel [.] com - Agar tampilan teks lebih terlihat rapi, tentu sebaiknya tidak boleh ada spasi yang berlebihan, misalnya saja antar kata ada dua spasi, tiga spasi dan seterusnya tentu akan terlihat tidak menarik.

Bagi Anda yang memiliki data dengan spasi berlebih, Anda tidak perlu khawatir karena di Excel sudah tersedia cara untuk menghilangkan spasi berlebih, yaitu dengan menggunakan Fungsi TRIM.

Deskripsi dan Sintaksis Fungsi TRIM

Fungsi TRIM digunakan untuk menghapus semua spasi dari teks kecuali spasi tunggal di antara kata.

Penulisan sintaksis fungsi TRIM adalah:

=TRIM(text)

Keterangan:

text adalah teks yang akan dihapus spasi berlebihnya.

Contoh Fungsi TRIM spasi di awal

=TRIM(" Belajar Excel")

hasilnya adalah Belajar Excel, spasi di awal teks dihilangkan dan spasi di antara kata Belajar dan Excel yang berisi banyak spasi di hapus dan hanya disisakan spasi tunggal.

Contoh Fungsi TRIM spasi normal

=TRIM("Rumus Excel")

hasilnya adalah Rumus Excel, karena memang di teks ini tidak ada spasi berlebih, maka tidak ada spasi yang di hapus oleh fungsi TRIM.

Contoh Fungsi TRIM spasi di akhir

=TRIM("Fungsi Excel ")

hasilnya adalah Fungsi Excel, semua spasi yang ada di akhir teks di hapus semua oleh fungsi TRIM.

Contoh Fungsi TRIM hasil salinan web

Ketika Anda menyalin teks dari web ke excel, terkadang mendapati teks dengan spasi berlebih, meski spasinya terlihat sama, namun sebenarnya berbeda.

Hasil salinan dari halaman web ini biasanya spasinya berupa entitas HTML,  . Entitas ini memiliki nilai karakter unicode 160, sedangkan fungsi TRIM hanya bisa menangani spasi ASCII 7-bit (nilai 32), jadi tidak bisa langsung di hapus dengan fungsi TRIM.

Untuk solusi masalah ini Anda harus mengubah dulu karakter spasi kode 160 dengan spasi ASCII 7-bit baru kemudian di eksekusi dengan fungsi TRIM.

Misal teks hasil salinan web ada di sel A1, maka rumus excel yang digunakan adalah:

=TRIM(SUBSTITUTE(A1;CHAR(160);" "))

Hasilnya adalah:

Fungsi SUBSTITUTE digunakan untuk mengubah semua spasi karakter 160 (char (160)) dengan spasi ASCII 7-bit, baru kemudian digunakan fungsi TRIM untuk menghapus spasi berlebihnya.

Sekian panduan tentang penggunaan fungsi TRIM untuk menghilangkan spasi berlebih di Excel, Semoga Bermanfaat.

Rumus Menghitung Persentase di Excel dan contoh-contohnya

noreply@blogger.com (Masdad Al-Falucky) — Mon, 8 Jul 2024 21:41:00 +0700

Rumus Excel [.] com - Persen atau persentase adalah bentuk bilangan yang mewakili sebagian atau keseluruhan nilai dengan membentuk rasio per seratus, besarnya persentase biasanya dilambangkan dengan simbol "%".

Persentase sendiri biasanya digunakan di banyak hal, misalnya menghitung persentase jumlah penduduk, mencari persentase diskon barang, hitung persentase suku bunga dan sebagainya.

Rumus menghitung persen dalam ilmu matematika yaitu:

Persentase (%) = (jumlah bagian) / (jumlah seluruhnya) x 100

Sedangkan jika menggunakan rumus persen di excel yaitu:

= (jumlah bagian) / (jumlah seluruhnya)

Pada Excel Anda tidak perlu mengalikan lagi dengan seratus (x 100) karena di Excel sudah disediakan format persen / percentage (%).

Hasil dari pembagian jumlah bagian dengan jumlah seluruhnya adalah berupa nilai/bilangan pecahan, Anda harus mengubah format selnya ke format persentase (percentage) untuk mengubahnya menjadi format persen.

Contoh menghitung angka 250 itu berapa persennya angka 1000. Pada contoh di sel B2 dimasukkan rumus excel:

=250/1000 //hasilnya adalah 0.25 (berupa bilangan pecahan)

sedangkan di sel C2 dengan rumus yang sama =250/1000, hasilnya tentu juga sama 0.25 namun kemudian pada format selnya di ubah ke format percentage (%) maka pada sel C2 hasilnya adalah berupa bilangan persen yaitu 25%, artinya angka 250 adalah 25% dari angka 1000.

Contoh Soal Hitung Persen 1

Rania membeli gula pasir seharga 17.500 dan hendak menjualnya kembali dengan mengambil keuntungan 20%. Berapakah harga jual gula pasir tersebut.

Untuk menghitung harga jual gula pasir dengan ketentuan untung 20% maka rumus excel yang digunakan adalah:

= 120%*17500 //hasilnya adalah 21.000

Jadi Rania harus menjual gula pasir per kilo seharga 21.000 agar mendapat keuntungan 20% atau 3.500 per kilo.

Contoh Soal Hitung Persen 2

Budi ingin membeli Laptop di Toko Berkah Komputer, harga laptop tersebut adalah 5 juta, namun karena Budi merupakan pelanggan setia, maka pihak toko memberikan diskon / potongan harga sebesar 10%. Berapakah yang harus di bayar oleh Budi untuk mendapatkan laptop tersebut??

Cara menghitung potongan harga di Excel, rumus excel yang digunakan adalah:

=harga barang - (diskon * harga barang)

atau

=5000000-(10%*5000000) //hasilnya adalah 4.500.000

Jadi harga yang harus di bayar oleh Budi untuk sebuah laptop adalah 4.500.000

Contoh Soal Hitung Persen 3

Jumlah penduduk di provinsi Jawa Tengah pada tahun 2023 menurut catatan dari BPS Jawa Tengah adalah 37.032.410, sedangkan pada tahun 2023 naik menjadi 37.540.962, berapakah persentase kenaikan jumlah penduduk di jawa tengah dalam kurun waktu 2022-2023?

Rumus Excel yang digunakan adalah:

=(37540962-37032410)/37032410 //hasilnya adalah 1,37%

Jadi jumlah penduduk di Jawa Tengah tahun 2023 ada kenaikan sebesar 1,37% atau naik sekitar 508.552 jiwa dibandingkan tahun 2022.

Contoh Soal Hitung Persen 4

Agus sedang mengikuti tes untuk menjadi admin di PT. Makmur Jaya, pada tes tersebut Agus harus mengerjakan soal pilihan ganda sebanyak 50 soal.

Dan pihak perusahaan hanya akan meloloskan untuk peserta tes yang menjawab dengan benar minimal 90% soal. Berapakah minimal soal yang harus di jawab dengan benar oleh Agus agar bisa lolos tes?

Penulisan rumus excelnya adalah:

=90%*50 //hasilnya adalah 45

Jadi agar lolos tes, Agus harus menjawab dengan benar minimal 45 soal.

Contoh Soal Hitung Persen 5

Pada sebuah kelas terdapat 50 orang siswa/siswi, jika pada kelas tersebut 40% adalah perempuan, berapakah jumlah siswa laki-laki di kelas tersebut?

Untuk menghitung berapa jumlah siswa laki-laki, rumus excelnya adalah:

=50-(40%*50) //hasilnya adalah 30

Jadi siswa laki-laki di kelas tersebut adalah sebanyak 30 orang.

Saya kira cukup untuk penjelasan mengenai cara hitung persen di Excel dan contoh-contohnya, semoga dapat mudah di fahami dan semoga bermanfaat.

Cara Menandai NIK Ganda / Nomor KTP Duplikat di Excel

noreply@blogger.com (Masdad Al-Falucky) — Wed, 3 Jul 2024 11:02:00 +0700

Rumus Excel [.] com - NIK atau Nomor Induk Kependudukan adalah ID Unik yang dimiliki oleh setiap penduduk di negara Indonesia, nomor pada NIK biasanya dibuat berdasarkan kode tertentu seperti kode provinsi, kode kota/kabupaten, kode kecamatan dan juga berdasarkan tanggal lahir dari pemilik NIK.

Karena bersifat unik makanya NIK banyak digunakan untuk keperluan administrasi kependudukan, misalnya saja untuk pendaftaran pemungutan suara (pilpres, pilkada, pilgub dan pilbup), yang nantinya akan masuk ke DPS (daftar pemilih sementara) atau DPT (daftar pemilih tetap).

Sebelum masuk DPS biasanya akan dilakukan pengecekan terlebih dulu apakah terdapat NIK Ganda atau tidak, penyebab terjadinya data ganda bisa karena adanya salah penulisan NIK, atau juga karena pindah tempat tinggal baru, tapi masih tetap terdaftar di tempat lama.

Untuk mengecek data ganda (data duplikat) dengan menggunakan Excel, caranya sebenarnya sangat mudah, ini karena di Excel sudah tersedia fitur Highlight Duplicate Values untuk menandai sel duplikat dan fitur Remove Duplicates untuk menghapus data duplikat.

Untuk menandai sel atau range yang ada data ganda / data duplicate, Anda bisa menggunakan fitur Conditional Formatting yang ada di Excel, caranya adalah:

Blok atau pilih dahulu data yang ingin di highlight atau di tandai sebagai data ganda.
Buka menu Conditional Formatting - Highlight Cells Rules - Duplicate Values...

Pada pilihan Duplicate Values, pada dropdown list bisa pilih yang Duplicate, untuk values with bisa pilih apa saja, misalnya pilih yang Light Red Fill with Dark Red Text, setelah itu klik OK.

Hasilnya data yang ganda akan ditandai dengan fill color warna merah.

Pada contoh hasil pengecekan duplicate values atau data ganda pada NIK ternyata hasilnya tidak sesuai, terlihat mulai sel A2 sampai A12 dianggap sebagai data ganda.

Padahal yang benar-benar duplikat hanya ada dua, yaitu di sel A2 dengan sel A9 dan sel A4 dengan sel A11.

Mengapa bisa begitu ???

Ini karena Excel hanya mampu membaca data sebanyak 15 digit, sedangkan pada NIK ada 16 digit, itulah sebabnya pada contoh data NIK di atas hampir semua dianggap data ganda, karena 15 digit angka awalnya memang sama semua.

Agar mendapatkan hasil yang sesuai, Anda bisa mengulang lagi membuat conditional Formatting dengan menggunakan pilihan Use a formula to determine which cells to format.

Blok terlebih dahulu data yang tadi sudah di tandai dengan Conditional Formatting, kemudian pilih/klik menu Conditional Formatting pilih Clear Rules - Clear Rules from Selected Cells untuk menghapus format yang telah dibuat.

Selanjutnya buka lagi menu Conditional Formatting dan pilih New Rule... dan pada pilihan Select a Rule Type pilih yang Use a formula to determine which cells to format.

Pada Format values where this formula is true masukkan rumus excel:

=SUMPRODUCT(N($A$2:$A$13=A2))>1

Perhatikan pada penulisan alamat range atau sel ada yang dijadikan alamat sel absolut dan relatif, silahkan sesuaikan dengan data yang Anda miliki.

Jangan lupa juga untuk menentukan Format dari Conditional Formattingnya, bagaimana warna fill, warna teks dan sebagainya melalui menu Format, jika sudah silahkan klik OK.

Hasilnya adalah sebagai berikut:

Terlihat pada gambar di atas, hasil sudah sesuai yang di harapkan. hanya sel yang berisi data ganda yang di tandai / di highlight.

Lampiran

CF Duplicate 15 Digit+ Excel Workbook (xlsx)

Download

Sekian tutorial mengenai bagaimana menandai data NIK yang ganda menggunakan Excel, Semoga bermanfaat.

Fungsi SORTBY, Rumus Excel Mengurutkan Data Berdasarkan Kolom

noreply@blogger.com (Masdad Al-Falucky) — Mon, 1 Jul 2024 16:13:00 +0700

Rumus Excel [.] com - Fungsi SORTBY adalah salah satu fungsi Excel yang dapat digunakan untuk mengurutkan data pada range atau array, sama fungsinya seperti Fungsi SORT. Meski sama kegunaannya namun ada perbedaan antara fungsi SORT dan SORTBY terutama di argumennya.

Salah satu yang membedakan antara Fungsi SORT dan SORTBY adalah pada SORTBY tidak ada argumen yang secara khusus mengontrol pengurutan data berdasarkan baris atau berdasarkan kolom.

Untuk menentukan orientasi pengurutan, di Fungsi SORTBY disediakan argumen by_array, jika by_array adalah rentang vertikal, maka fungsi SORTBY akan otomatis mengurutkan secara vertikal berdasarkan baris dan jika by_array adalah rentang horizontal, SORTBY akan mengurutkan data secara horizontal berdasarkan kolom.

Perbedaan lainnya adalah pada Fungsi SORTBY Anda dapat mengurutkan data yang bukan merupakan bagian dari data yang sedang diurutkan, misalnya mengurutkan isi data di kolom A berdasarkan isi data yang ada di kolom B, sedangkan fungsi SORT hanya dapat mengurutkan data berdasarkan pada range atau kolom yang sama.

Sintaksis Fungsi Excel SORTBY

Penulisan sintaksis pada Fungsi SORTBY adalah:

=SORTBY(array; by_array1; [sort_order1]; [by_array2]; [sort_order2]; ...)

Penjelasan:

array : array atau rentang yang ingin diurutkan.
by_array1 : array atau rentang yang digunakan untuk mengurutkan.
sort_order1 : urutan yang ingin digunakan.
by_array2 : array atau rentang yang digunakan untuk mengurutkan.
sort_order2 : urutan yang ingin digunakan.

argumen sort_order1, sort_order2 dst adalah argumen opsional, isinya adalah nilai 1 atau -1. Nilai 1 untuk mengurutkan data secara ascending atau dari nilai terkecil ke terbesar, sedangkan jika di isi -1 artinya data akan diurutkan secara descending atau dari nilai terbesar ke nilai terkecil.

Jika argumen sort_order tidak isi maka default-nya atau dianggap isinya adalah 1 atau diurutkan secara ascending, sedangkan jika argumen sort_order di isi selain angka 1 atau -1 maka akan menghasilkan kesalahan #VALUE.

Contoh Fungsi Excel SORTBY

Berikut beberapa contoh penggunaan Fungsi SORTBY, dengan data sumbernya ada di sel B2:C11:

Contoh Fungsi SORTBY satu kolom.

Pada sel E2 setelah dimasukkan rumus excel:

=SORTBY(B2:B11;B2:B11)
Hasilnya adalah:
Pada contoh penggunaan Fungsi SORTBY yang pertama ini, untuk argumen by_array-nya menggunakan kolom / range yang sama dengan argumen array-nya yaitu sama-sama B2:B11, terlihat hasil yang ditampilkan (range E2:E11) sudah berhasil mengurutkan nama sesuai abjad secara ascending (dari A - Z).
Contoh Fungsi SORTBY beda kolom.

Pada sel E2 setelah dimasukkan rumus excel:

=SORTBY(B2:B11;C2:C11)
Hasilnya adalah:

Pada contoh kedua ini, argumen by_array-nya adalah range kelas (C2:C11) terlihat pada hasil yang ditampilkan sudah diurutkan sesuai kelasnya masing-masing, tapi untuk namanya tidak diurutkan sesuai abjad, ini karena argumen by_array hanya 1 yaitu range kelas, jadi hasilnya hanya mengurutkan berdasarkan kelas.

Untuk menampilkan hasil yang ada kelasnya, Anda bisa mengubah rumus excel sebelumnya menjadi seperti berikut ini:

=SORTBY(B2:C11;C2:C11)
Hasilnya adalah:

Pada contoh diatas terlihat hasil yang ditampilkan ada 2 kolom yaitu kolom NAMA dan kolom KELAS, ini karena argumen array-nya tadi di ubah yang sebelumnya B2:B11 (satu kolom) menjadi B2:C11 (dua kolom), maka hasilnya juga dua kolom.
Contoh Fungsi SORTBY dengan 2 argumen by_array

Pada sel E2 setelah dimasukkan rumus Excel:

=SORTBY(B2:C11;C2:C11;1;B2:B11;1)
Keterangan:
argumen array-nya : B2:C11
argumen by_array1 : C2:C11 (kolom kelas)
argumen sort_order1 : 1 (di urutkan secara ascending)
argumen by_array2 : B2:B11 (kolom nama)
argumen sort_order2 : 1 (di urutkan secara ascending)

dengan menggunakan rumus excel diatas, Excel akan melakukan dua kali pengurutan, pertama mengurutkan berdasarkan kelas, kemudian hasilnya diurutkan lagi berdasarkan nama.

Dan hasilnya adalah:

Terlihat nama yang ditampilkan sudah urut sesuai abjad berdasarkan kelasnya.

Lampiran

Fungsi Excel SORTBY Excel Workbook (xlsx)

Download

Sekian panduan lengkap dan contoh penggunaan Fungsi Excel SORTBY, Semoga Bermanfaat.

Fungsi SEQUENCE, Rumus Excel Membuat Nomor Urut Otomatis

noreply@blogger.com (Masdad Al-Falucky) — Fri, 28 Jun 2024 11:14:00 +0700

Rumus Excel [.] com - Fungsi Excel SEQUENCE adalah salah satu fungsi Dynamic Array yang tersedia di Excel 2021 dan Excel Office 365. Fungsi SEQUENCE digunakan untuk menghasilkan daftar angka yang berurutan dalam larik (array) seperti 1, 2, 3, 4 dst.

Sintaksis Fungsi SEQUENCE

Pada fungsi Excel SEQUENCE terdapat 4 argumen, 1 argumen wajib dan 3 argumen opsional.

Penulisan sintaksisnya adalah:

=SEQUENCE(rows; [columns]; [start]; [step])

Penjelasan:

rows : jumlah baris yang ingin dihasilkan
columns : jumlah kolom yang ingin dihasilkan
start : angka pertama dalam urutan.
step : jumlah yang perlu ditambahkan pada setiap nilai berikutnya dalam array (larik).

Contoh: misalnya ingin menghasilkan angka 1-20 (4 baris 5 kolom) maka penulisan rumusnya adalah:

=SEQUENCE(4;5)

Hasilnya seperti terlihat pada gambar dibawah ini:

Karena pada argumen start dari fungsi SEQUENCE tidak isi, maka otomatis pembuatan urutan angkanya di mulai dari angka 1, jika ingin di mulai dari angka 5, maka argumen start bisa di isi angka 5.

=SEQUENCE(4;5;5)

atau misal hasilnya ingin selalu ditambahkan 5, maka untuk argumen step bisa di isi 5:

=SEQUENCE(4;5;5;5)

Hasilnya akan terlihat seperti gambar dibawah ini:

Membuat Nomor 1-100 di Excel

Materi belajar excel tentang membuat nomor urut di Excel sebenarnya sudah pernah saya bagikan di artikel Cara Cepat Membuat Nomor urut Otomatis di Excel, pada artikel tersebut setidaknya ada 3 cara membuat urut di Excel, kali ini kita akan menambah 1 lagi caranya dengan menggunakan Fungsi SEQUENCE.

Untuk membuat nomor 1-100 di Excel jika menggunakan fungsi Excel SEQUENCE caranya sangat mudah, Anda cukup menggunakan satu argumen saja, yaitu argumen rows.

Pada argumen rows fungsi SEQUENCE cukup isikan dengan angka 100, untuk membuat nomor urut 1 - 100. Misalnya saja membuat nomor urut mulai dari Sel A1, maka pada sel A1 dimasukkan rumus excel:

=SEQUENCE(100)

Hasilnya pada sel A1 s.d sel A100 akan terisi angka 1 s.d 100.

Jika ingin membuat nomor urut sampai 500, maka pada argumen rows fungsi SEQUENCE bisa di isi 500, pun begitu jika ingin sampai 1.000 tinggal masukkan angka 1000 dsb.

Membuat Nomor Otomatis di Excel

Agar lebih paham lagi tentang penggunaan fungsi excel SEQUENCE, kita akan belajar excel lagi dari contoh kasus membuat nomor otomatis berdasarkan kolom lain.

Misalnya di kolom A untuk kolom Nomor dan kolom B untuk kolom Nama, jika kolom B di isi, maka pada kolom A akan otomatis terisi angka sesuai urutannya.

Untuk kasusnya ini kita akan menggunakan 2 Fungsi Excel, yaitu Fungsi SEQUENCE dan Fungsi COUNTA.

Dan penulisan rumus excel di sel A2 adalah:

=SEQUENCE(COUNTA(B2:B1000))

Dengan menggunakan rumus excel di atas, ketika kolom nama (range B2:B1000) ada isinya maka pada kolom A atau nomor akan terisi angka otomatis, jadi lebih menghemat waktu karena tidak perlu menulis angka secara manual.

Lampiran

Fungsi Excel SEQUENCE Excel Workbook (xlsx)

Download

Sekian panduan lengkap tentang cara menggunakan fungsi SEQUENCE di Excel beserta contoh-contohnya, semoga bermanfaat.

Fungsi EOMONTH, Rumus Excel Mencari Tanggal Terakhir di setiap Bulan

noreply@blogger.com (Masdad Al-Falucky) — Thu, 27 Jun 2024 09:55:00 +0700

Rumus Excel [.] com - Anda tentu sudah mengetahui bahwa pada kalender masehi untuk tanggal akhir tiap bulan berbeda-beda, ada yang 28, 29, 30 dan 31.

Untuk memudahkan mendapatkan tanggal atau hari terakhir tiap bulan, di aplikasi Excel sudah disediakan fungsi khusus yaitu Fungsi EOMONTH (End of Month).

Fungsi EOMONTH digunakan untuk mengembalikan nomor seri dari hari terakhir bulan sebelum atau sesudah dari bulan yang ditentukan.

Jadi dengan fungsi EOMONTH ini Anda bisa menampilkan tanggal terakhir dari bulan ini, 1 atau 2 bulan kemudian atau 1 atau 2 bulan sebelumnya dan seterusnya.

Sintaksis Fungsi EOMONTH

Fungsi EOMONTH hanya memiliki 2 argumen dan kedua-duanya wajib di isi. Untuk penulisan sintaksisnya adalah:

=EOMONTH(start_date; months)

Penjelasan:

start_date : tanggal yang merupakan tanggal mulai.
months : jumlah bulan sebelum atau sesudah start_date. bisa di isi angka positif untuk menghasilkan tanggal yang akan datang dan nilai negatif untuk tanggal akhir bulan sebelumnya.

Argumen start_date EOMONTH

Untuk argumen start_date pada fungsi EOMONTH harus berupa tanggal yang valid menggunakan fungsi DATE atau referensi sel yang berisi tanggal valid. Jika start_date bukan merupakan tanggal yang valid misalnya berformat teks, maka EOMONTH akan menghasilkan pesan error #NUM.

Contoh menggunakan Fungsi DATE:

=EOMONTH(DATE(2024;2;2);0) //hasilnya adalah 45351 atau 29/02/2024

Sekedar mengingatkan Excel akan menyimpan tanggal sebagai nomor seri berurutan sehingga bisa digunakan di dalam perhitungan. Secara default, 1 Januari 1900 adalah nomor seri 1, maka 2 Februari 2024 adalah nomor seri 45351 karena 45.351 hari setelah 1 Januari 1900.

Argumen months EOMONTH

Pada argumen ini Anda bisa menentukan tanggal akhir bulan mana yang ingin Anda hasilkan, misalnya:

isikan 0 jika ingin menampilkan tanggal akhir pada bulan yang sama dengan sumber datanya.
isikan 1 untuk menampilkan tanggal akhir 1 bulan setelahnya.
isikan -1 untuk menghasilkan tanggal akhir 1 bulan sebelumnya.

Anda bebas mengisi argumen months ini sesuai dengan hasil yang Anda harapkan, jika ingin tambah 2 bulan bisa di isi 2 atau misal ingin tambah 5 bulan bisa di isi 5 dst.

Contoh Fungsi EOMONTH

Biar lebih mudah memahami penggunaan fungsi EOMONTH, mari langsung praktek saja di Excel. Silahkan perhatikan contoh di bawah ini:

Untuk argumen start_date-nya ada sel B1 yang berisi tanggal 27/06/2024 atau 27 Juni 2024.

Rumus Excel pada sel B3:

=EOMONTH(B1;-1) //hasilnya adalah 31/05/2024 atau 31 Mei 2024

karena argumen months di isi -1 maka akan menghasilkan tanggal akhir dari bulan sebelumnya.

Rumus Excel pada sel B5:

=EOMONTH(B1;0) //hasilnya adalah 30/06/2024 atau 30 Juni 2024

karena argumen months di isi 0 yang berarti akan menghasilkan tanggal akhir dari bulan yang sama dengan tanggal di start_date.

Rumus Excel pada sel B7:

=EOMONTH(B1;1) //hasilnya adalah 31/07/2024 atau 31 Juli 2024

karena argumen months di isi 1 maka hasilnya adalah menampilkan tanggal akhir pada bulan selanjutnya.

Dengan adanya fungsi EOMONTH pada Excel ini, Kita bisa dengan mudah membuat laporan transaksi yang otomatis berdasarkan bulan aktif, untuk menghasilnya tanggal awal bulan aktif Anda bisa menggunakan rumus:

=EOMONTH(TODAY(); -1)+1

Anda bisa menggabungkan dengan fungsi FILTER atau fungsi SUMIFS untuk mendapatkan hasil penjumlahan di antara 2 tanggal, atau bisa juga menggabungkan dengan fungsi COUNTIFS. Misalnya:

= SUMIFS($B$2:$B$300;
$C$2:$C$300;">="&EOMONTH(TODAY();-1)+1;
$C$2:$C$300;"<="&EOMONTH(TODAY();0))

Pada range $B$2:$B$300 berisi nilai transaksi yang dingin dijumlahkan.

Pada range $C$2:$C$300 berisi tanggal transaksi.

Dengan menggunakan rumus excel di atas akan menghasilkan total nilai transaksi berdasarkan tanggal awal dan tanggal akhir bulan yang aktif. Misal hari ini bulan Juni 2024 maka tanggal awal adalah 01 Juni 2024 dan tanggal akhir adalah 30 Juni 2024.

Lampiran

Fungsi Excel EOMONTH Excel Workbook (xlsx)

Download

Sekian penjelasan tentang penggunaan Fungsi Excel EOMONTH, semoga bermanfaat.

Fungsi FILTER, Rumus Excel untuk Filter Data di Excel

noreply@blogger.com (Masdad Al-Falucky) — Mon, 24 Jun 2024 13:02:00 +0700

Rumus Excel [.] com - Fungsi FILTER adalah salah satu fungsi dynamic array yang tersedia di Office 365 dan Office 2021, karena itu penulisan rumusnya hanya perlu pada satu sel saja, dan akan otomatis mengisi sel-sel dalam jangkauan datanya, karena dynamic array (array dinamis) ini akan menyesuaikan ukuran rangenya sesuai data yang ada.

Bagi Anda yang bukan pengguna Office 365 atau Office 2021, Anda tetap bisa menggunakan fungsi FILTER ini di Excel Online, Excel Android dan Excel IOS.

Deskripsi Fungsi FILTER Excel

Fungsi Excel FILTER adalah fungsi pada Microsoft Excel yang dapat digunakan untuk memfilter rentang data dengan menggunakan kriteria tertentu.

Sintaksis Fungsi FILTER Excel

Penulisan Syntax/Sintaksis dari fungsi FILTER di Excel adalah:

=FILTER ( array; include; [if_empty] )

Keterangan:

Argumen	Keterangan
array	Merupakan range atau Array yang akan di filter.
include	Array boolean, yang menjadi kriteria.
if_empty	[opsional] nilai yang dikembalikan saat tidak ada hasil yang ditemukan.

Contoh Fungsi FILTER Excel

Untuk lebih memahami penggunaan fungsi Excel silahkan perhatikan contoh berikut ini:

Pada contoh diatas data yang akan di filter adalah data di range A2:A10, dan akan di filter berdasarkan jenisnya. Ketika di sel G2 dimasukkan rumus excel:

=FILTER(B2:C10;A2:A10=E2)

Maka hasilnya terlihat pada range G2:H4, yang menampilkan data buah-buahan. karena pada sel E2 yang merupakan argumen include filter di pilih jenis buah. Dan semisal pada sel E2 di ubah menjadi Sayuran, maka hasilnya adalah seperti terlihat pada gambar dibawah ini:

Anda juga bisa memasukkan dua kriteria atau lebih pada argumen include fungsi FILTER, untuk format penulisannya adalah:

=FILTER (range_array; (range1=kriteria 1)*(range2=kriteria 2))

Contoh ingin menampilkan daftar nama hewan yang harganya diatas 5 juta, maka pada kriteria ke-1 di isi Hewan dan pada kriteria ke-2 di isi >5000000 jadi penulisan rumusnya adalah:

=FILTER(B2:C10;(A2:A10="Hewan")*(C2:C10>5000000))

atau misal kriteria ke-1 ada di sel E2 dan kriteria ke-2 ada di sel E5, maka penulisan rumus excelnya adalah:

=FILTER(B2:C10;(A2:A10=E2)*(C2:C10>E5))

Dan hasilnya seperti terlihat pada gambar di bawah ini:

Terlihat hasil Filter yang ditampilkan sesuai kriteria yang telah ditentukan, yakni menampilkan nama hewan yang harganya di atas 5 juta.

Bagaimana jika tidak ada data yang ditemukan?

Ketika hasil FILTER tidak ditemukan, maka Excel akan menampilkan pesan kesalahan / error #CALC!, misalnya pada contoh tadi di ubah kriterianya menjadi hewan yang harganya sama dengan 5 juta, maka excel akan menampilkan hasil error #CALC! karena pada sumber datanya tidak ada hewan yang harganya 5 juta.

Jika tidak ingin ada hasil error #CALC maka Anda bisa mengisi argumen ke-3 dari fungsi FILTER yakni argumen if_empty dengan teks yang Anda ingin tampilkan ketika hasil filter tidak ditemukan. Misalnya adalah:

=FILTER(B2:C10;(A2:A10=E2)*(C2:C10=E5);"Tidak Ditemukan")

Maka ketika fungsi FILTER tidak menemukan hasil yang cocok dengan kriteria yang ditentukan maka akan menampilkan isi pesan dari argumen if_empty yaitu teks Tidak Ditemukan.

Lampiran

Fungsi Excel FILTER Excel Workbook (xlsx)

Download

Sekian tutorial tentang penggunaan fungsi FILTER beserta contohnya di Excel, Semoga Bermanfaat

Cara memperbaiki Kolom Excel berubah menjadi angka

noreply@blogger.com (Masdad Al-Falucky) — Mon, 24 Jun 2024 09:45:00 +0700

Rumus Excel [.] com - Tampilan headings pada aplikasi Microsoft Excel defaultnya adalah untuk baris berupa angka dari angka 1 sampai baris terakhir yaitu 1.048.576 dan untuk tampilan kolom biasanya berupa abjad mulai dari kolom A sampai kolom XFD.

Selain tampilan abjad pada kolom Excel sebenarnya Anda juga bisa menggunakan tampilkan berupa angka pada kolom Excel, istilah di Excelnya adalah R1C1 References Style.

Namun karena tampilan ini tidak umum, ketika tiba-tiba tampilan kolom Excelnya berubah, yang biasanya abjad (ABCD dst) berubah menjadi angka (1234 dst), sebagian user excel mengira Excelnya rusak atau error, bahkan sampai ada yang di install ulang Officenya.

Bagi Anda yang mengalami hal ini, jangan panik ya. Berikut cara memperbaiki atau mengembalikan tampilan kolom Excel yang berubah jadi angka, menjadi normal lagi seperti semula.

Pada Excel, buka menu File - Options / Excel Options. Jika Anda masih menggunakan Office Excel 2007 silahkan buka lewat menu Office Button - Excel Options.
Pada Excel Options, silahkan pilih menu/tab Formulas dan bagian Working with formulas, silahkan lakukan uncek / hapus tanda centang pada pilihan R1C1 reference style. Kemudian klik OK.

Sekarang tampilan Excelnya harusnya sudah normal seperti biasanya, sekian tutorial untuk memperbaiki kolom excel yang menjadi angka, semoga bermanfaat.

Cara Membuat Titik Dua Sejajar di Excel

noreply@blogger.com (Masdad Al-Falucky) — Fri, 21 Jun 2024 10:52:00 +0700

Rumus Excel [.] com - Bagi Anda yang sedang ingin membuat formulir pendaftaran peserta didik baru (PPDB) dengan Excel, atau pun membuat formulir-formulir lain seperti formulir pengajuan bikin KTP dsb, Tips Excel kali ini mungkin sangat berguna.

Salah satu komponen yang ada di formulir biasanya adalah adanya titik dua (:), agar penulisan titik dua ini terlihat rapi dan sejajar, beberapa pengguna Excel biasanya menggunakan satu kolom khusus yang isinya hanya titik dua saja.

Bagi Anda yang tidak ingin menggunakan satu kolom tambahan yang hanya di isi titik dua saja, di aplikasi Excel Anda bisa memanfaatkan fitur Format Cells, dengan membuat custom format khusus, Anda bisa membuat titik dua sejajar dengan cepat dan mudah.

Untuk membuat titik dua sejajar pada Excel, caranya adalah sebagai berikut:

Blok Terlebih dahulu data yang akan diberi titik dua sejajar, misalnya blok sel D6:D8.
Kemudian Klik Kanan dan pilih Fomat Cells, atau bisa juga menggunakan keyboard, dengan menekan tombol Ctrl + 1.
Pada pengaturan Format Cells, pilih Custom dan pada kotak isian Type: masukkan teks:

@ * ":"
Selanjutnya klik OK, untuk menyimpan pengaturan yang sudah dibuat pada custom format cells.

Dengan menggunakan custom format cells seperti contoh diatas, maka pada sel yang telah di format akan otomatis menampilkan titik dua di bagian akhir teks, jadi Anda tidak perlu lagi menuliskan titik dua, selain itu posisi titik duanya akan tampak sejajar dan rapi.

Begitulah cara membuat titik dua sejajar yang cepat dan mudah di Excel, Semoga bermanfaat.

Cara Menggunakan Fungsi TEXTBEFORE di Excel

noreply@blogger.com (Masdad Al-Falucky) — Mon, 17 Jun 2024 09:46:00 +0700

Rumus Excel [.] com - Fungsi TEXTBEFORE saat ini baru tersedia untuk pengguna Excel 365, jadi jika Anda ingin menggunakan Fungsi TEXTBEFORE ini, Anda harus menggunakan Excel 365, baik itu yang versi desktop, Android maupun versi Excel Online.

Deskripsi Fungsi TEXTBEFORE

Fungsi Excel TEXTBEFORE digunakan untuk mengembalikan teks yang muncul sebelum pembatas tertentu. Jika terdapat beberapa pembatas yang ada pada teks, TEXTBEFORE dapat mengembalikan teks sebelum kemunculan pembatas yang ke-n.

Sintaksis Fungsi TEXTBEFORE

=TEXTBEFORE(text; delimiter; [instance_num]; [match_mode]; [match_end]; [if_not_found])

Penjelasan:

text - teks yang akan di ekstrak.
delimiter - teks atau karakter yang membatasi teks.
instance_num (opsional) - contoh pembatas dalam teks.

argumen instance_num ini dapat digunakan untuk menentukan pembatas mana yang akan digunakan, misalnya untuk mengembalikan teks sebelum pembatas kedua, maka untuk instance_num ini bisa di isi 2, jika tidak di isi maka defaulnya adalah 1.
match_mode (opsional) - menentukan sensitivitas huruf besar/kecil.

secara default, fungsi Excel TEXTBEFORE peka terhadap huruf besar kecil (match_mode = 0) dan akan mencocokkan huruf besar kecil saat mencari pembatas. Jika tidak ingin peka terhadap huruf besar kecil maka untuk match_mode ini bisa di isi 1.
match_end (opsional) - memperlakukan akhir teks sebagai pembatas. Defaultnya adalah 0.
- 0 : jangan cocokkan pemisah terhadap akhir teks.
- 1 : mencocokkan pemisah terhadap akhir teks.
if_not_found (opsional) - hasil yang dikembalikan ketika tidak mendapat kecocokan.

Fungsi Excel TEXTBEFORE akan mengembalikan #N/A ketika tidak dapat menemukan pembatas yang ditentukan, agar tidak muncul hasil #N/A, Anda bisa mengisi argumen if_not_found ini, yang nantinya akan ditampilkan ketika Fungsi TEXTBEFORE tidak mendapat hasil yang sesuai.

Contoh Fungsi TEXBEFORE

Untuk mengembalikan hasil sebelum karakter tertentu, Anda cukup mengisi argumen delimiter sebagai pembatas yang ingin di cari. Misal untuk mendapatkan nama depan dari nama lengkap yang dipisahkan dengan spasi, maka untuk delimiternya di isi spasi. contohnya:

=TEXTBEFORE(A2;" ")

hasilnya seperti terlihat pada gambar dibawah ini, hanya teks sebelum spasi yang ditampilkan.

Argumen instance_num TEXBEFORE

Instance_num pada fungsi TEXTBEFORE digunakan untuk menentukan posisi dari delimiter dimulai dari kiri, defaultnya adalah 1. Jadi misal Anda ingin mengambil teks mulai dari posisi kedua maka Instance_num bisa di isi 2 dan seterusnya.

Anda juga bisa memasukkan nilai negatif pada argumen Instance_num dan perhitungan akan berubah dari kanan atau dimulai dari akhir data.

Lebih jelasnya bisa dilihat pada contoh dibawah ini:

Pada contoh diatas, sel C2 sampai C5 untuk instance_num dimasukkan angka 1 sampai 4, dan hasilnya yang ditampilkan sesuai batas spasi yang ditentukan pada instance_num.

Pada sel C6 hasilnya #N/A karena instance_num di isi 5, sedangkan jumlah spasi pada teks SAYA SEDANG BELAJAR FUNGSI EXCEL hanya ada 4, karena spasi yang ke 5 tidak ditemukan maka hasil yang ditampilkan adalah error #N/A.

Pada sel C8 sampai C11 adalah contoh hasil dari instance_num yang isi dengan angka negatif, karena menggunakan angka negatif maka perhitungan untuk instance_num dimulai dari kanan atau dari bagian akhir.

Argumen match_mode TEXTBEFORE

Argumen ketiga dari fungsi TEXTBEFORE adalah match_mode, argumen match_mode ini bersifat opsional, jika tidak di isi maka defaultnya adalah 0.

Argumen match_mode ini adalah penentu apakah TEXTBEFORE ini peka terhadap huruf besar kecil atau tidak, jika match_mode = 0 maka TEXTBEFORE peka terhadap huruf besar kecil, dan jika match_mode = 1 maka tidak peka terhadap huruf besar kecil.

Untuk contohnya silahkan perhatian gambar dibawah ini:

Pada sel C2 dan sel C3 setelah dimasukkan rumus excel:

=TEXTBEFORE(A2;" X ";1) dan =TEXTBEFORE(A3;" X ";1;0)

Hasilnya ada pesan kesalahan #N/A, ini karena pada argumen match_mode tidak di isi dan atau di isi dengan 0, maka ketika pada delimiter atau pembatas kita mencari " X " (huruf besar) sedangkan pada sumber datanya hanya ada x (huruf kecil) hasilnya tidak ditemukan kecocokan.

Untuk sel C4 dan sel C5 pada pengaturan argumen match_mode di isi dengan 1 yang artinya untuk pencarian berdasarkan pembatas tidak peka terhadap huruf besar kecil, makanya meskipun di sumber datanya (sel A4 dan sel A5) ada huruf X dan x atau ada huruf besar dan kecil hasilnya tetap cocok.

Argumen match_end TEXTBEFORE

match_end pada fungsi TEXTBEFORE digunakan untuk menentukan kecocokan akhir teks sebagai pembatas, defaultnya ada 0 atau tidak mencocokkan pemisah dengan akhir teks dan jika di isi 1 artinya mencocokkan pemisah dengan akhir teks.

Untuk contohnya bisa dilihat pada contoh gambar berikut ini:

Argumen if_not_found TEXTBEFORE

Untuk menghilangkan tampilan error #N/A ketika fungsi TEXTBEFORE tidak mendapatkan kecocokan hasil, maka Anda bisa mengisi pada argumen if_not_found dengan pesan apa saja yang Anda inginkan.

Lebih jelasnya silahkan perhatikan contoh fungsi excel TEXTBEFORE dan hasilnya yang berbeda ketika argumen if_not_found-nya dibiarkan kosong dan yang ada isinya:

Sekian tutorial cara menggunakan fungsi TEXTBEFORE di Excel, Semoga Bermanfaat.

Cara Memisahkan Nama Depan dan Nama Belakang di Excel

noreply@blogger.com (Masdad Al-Falucky) — Sat, 15 Jun 2024 14:19:00 +0700

Rumus Excel [.] com - Untuk memisahkan nama depan dan nama belakang pada Excel ada banyak cara yang bisa digunakan, seperti menggunakan fungsi atau rumus excel, menggunakan menu Text to Columns atau Split Text to Columns, dan bisa juga memisahkan nama mengunakan Power Query.

Pada kesempatan kali ini kita akan belajar bagaimana memisahkan nama depan dan nama belakang menggunakan fungsi atau rumus excel, baik itu di Excel versi 365 dengan menggunakan fungsi excel TEXTBEFORE dan TEXTAFTER, maupun di versi Excel lain dengan kombinasi Fungsi LEFT, Fungsi RIGHT, dan Fungsi FIND atau Fungsi SEARCH.

Memisahkan Nama Depan

Untuk mempelajari bagaimana memisahkan nama depan atau nama pertama dari nama lengkap di Excel, silahkan perhatikan gambar contoh berikut ini:

Untuk sumber data yang berisi nama lengkap ada di kolom pertama atau kolom Nama, yakni di range A2 sampai dengan A11, sedang kolom kedua untuk nama depan dan kolom ketiga untuk nama belakang.

Untuk memisahkan nama depan, rumus yang digunakan pada sel B2 adalah:

=TEXTBEFORE(A2; " ")

Fungsi TEXTBEFORE digunakan untuk mengembalikan teks yang muncul sebelum karakter atau string tertentu, seperti pada contoh di nama lengkap terdapat spasi antara nama depan dan nama belakang maka untuk argumen delimiter pada fungsi TEXTBEFORE di isikan spasi (" "), yang berarti menghasilkan teks sebelum spasi.

Bagi Anda yang Excel-nya belum ada fungsi TEXTBEFORE, Anda bisa menggunakan fungsi alternatif lain untuk memisahkan nama depan.

Untuk menghasilkan nama pertama dari nama lengkap di excel, rumus excel yang digunakan pada sel B2 adalah:

=LEFT(A2; FIND(" "; A2)-1)

Fungsi LEFT digunakan untuk mengembalikan karakter paling kiri dari nilai teks, dan untuk menentukan jumlah karakter teks yang ingin dikembalikan digunakan fungsi FIND, yang berguna untuk menemukan posisi karakter spasi pada data nama lengkap, yang kemudian hasilnya dikurangi 1.

Memisahkan Nama Belakang

Untuk mendapatkan nama belakang dari data nama lengkap di Excel dapat menggunakan fungsi Excel TEXTAFTER, dan untuk penulisan rumusnya adalah:

=TEXTAFTER(A2; " ")

Fungsi TEXTAFTER digunakan untuk mengembalikan teks yang muncul setelah karakter atau string tertentu, atau kebalikan dari fungsi TEXTBEFORE.

Alternatif rumus lain untuk menampilkan nama belakang dari data nama lengkap di Excel, yakni menggunakan gabungan fungsi RIGHT, Fungi LEN dan Fungsi FIND, penulisannya seperti berikut ini:

=RIGHT(A2; LEN(A2)-FIND(" "; A2))

Fungsi RIGHT digunakan untuk mengembalikan karakter paling kanan dari nilai teks, sedangkan fungis LEN digunakan untuk mengembalikan jumlah karakter dalam string teks, dan fungsi FIND digunakan untuk menemukan satu nilai teks di dalam teks lain, dalam contoh digunakan untuk menemukan posisi spasi.

Sekian tutorial belajar excel cara memisahkan nama depan dan nama belakang dari data nama lengkap pada excel, semoga bermanfaat.

Fungsi UNIQUE, Rumus Excel menampilkan Data Unik di Excel

noreply@blogger.com (Masdad Al-Falucky) — Tue, 11 Jun 2024 13:04:00 +0700

Rumus Excel [.] com - Fungsi Excel UNIQUE adalah salah satu fungsi baru yang ada di Excel 2021, bagi Anda yang sudah memakai Excel 2021 dan Excel 365 bisa menggunakan fungsi UNIQUE ini, sedangkan untuk versi dibawahnya seperti Excel 2019, Excel 2016 dst belum bisa menggunakan fungsi ini.

Fungsi Excel UNIQUE digunakan untuk menghasilkan daftar nilai yang unik dalam daftar atau rentang. Nilai dapat berupa teks, angka, tanggal, waktu dan lain-lain.

Untuk format penulisan sintaksisnya adalah

=UNIQUE(array; [by_col]; [exactly_once])

Keterangan:

array : rentang atau array data yang akan di ekstrak nilai uniknya.
by_col : pilihan untuk mengekstak data berdasarkan kolom atau baris.
Pilih TRUE jika ingin menampilkan data unik berdasarkan kolom dan FALSE jika ingin berdasarkan baris.
Default pilihannya adalah FALSE atau berdasarkan baris.
exactly_once : pilihan hanya menampilkan data yang jumlahnya satu saja. Defaultnya adalah FALSE atau menampilkan semua.

Contoh Fungsi Excel UNIQUE

Untuk lebih memahami penggunaan fungsi Excel UNIQUE, silahkan perhatikan contoh pada gambar di bawah ini:

Data yang ingin di cari data uniknya adalah data di range atau rentang A2:A10. Maka untuk menampilkan data yang unik, di sel B2 dimasukkan rumus excel:

UNIQUE(A2:A10)

Hasilnya ditampilkan data uniknya. Karena fungsi Excel UNIQUE termasuk dalam fungsi Dynamic Array maka kita hanya perlu menuliskan rumus excel di awal sel saja, yaitu di sel B2.

Argumen by_col pada Fungsi UNIQUE

Argumen by_col di Fungsi Excel UNIQUE merupakan argumen opsional alias tidak harus di isi, defaultnya atau jika dikosongkan adalah FALSE, yang berarti fungsi Excel UNIQUE akan menampilkan data berdasarkan baris, sedangkan jika di isi TRUE maka fungsi Excel UNIQUE akan mencari dari berdasarkan kolom.

Untuk contohnya bisa dilihat digambar dibawah ini:

Pada sel C2 dimasukkan rumus excel:

=UNIQUE(A2:A10;TRUE)

argumen opsional by_col di isi nilai TRUE yang berarti data unik yang ingin ditampilkan berdasarkan kolom, terlihat hasilnya tidak berbeda dengan data aslinya yang di range A2:A10, ini karena data Arraynya berada di 1 kolom yang sama, bukan beberapa kolom, karena hasilnya sama persis seperti data range sumber datanya.

Argumen exactly_once di Fungsi UNIQUE

Untuk argumen opsional selanjutnya pada fungsi UNIQUE adalah exactly_once atau kalau diterjemahkan ke bahasa Indonesia berarti tepat sekali.

Argumen exactly_once di fungsi UNIQUE ini bisa digunakan sebagai penentu untuk menghasilnya nilai data yang unik maupun yang benar-benar unik (hanya ada satu saja) dari data yang cari.

Untuk lebih jelasnya perhatikan contoh pada gambar berikut ini:

Pada sel D2 setelah dimasukkan rumus excel:

=UNIQUE(A2:A10;FALSE;TRUE)

atau misal tanpa argumen by_col seperti ini:

=UNIQUE(A2:A10;;TRUE)

Pada gambar diatas ketika argumen opsional exactly_once pada fungsi UNIQUE di isi TRUE maka hasilnya adalah Anggur dan Mangga, karena pada sumber datanya nilai Anggur dan Mangga hanya ada satu saja, sedangkan untuk data yang lain ada yang dua atau lebih.

Lampiran

REDC - Fungsi Excel UNIQUE Excel Workbook (xlsx)

Download

Sekian tutorial tentang Fungsi Excel UNIQUE, Jika ada yang ingin di diskusikan bisa melalui kolom komentar. Semoga Bermanfaat.

Fungsi SORT, Rumus Excel untuk mengurutkan data di Excel

noreply@blogger.com (Masdad Al-Falucky) — Sat, 16 May 2020 08:38:00 +0700

Rumus Excel [.] com - Fungsi SORT adalah salah satu fungsi baru yang tersedia di Excel 365 (Office 365), fungsi SORT dapat digunakan untuk melakukan sortir data Excel atau mengurutkan data di Excel, sehingga Anda tidak perlu lagi mensortir data excel dengan cara manual melalui menu Sort & Filter.

Selain terdapat di Excel Office 365, fungsi SORT juga tersedia pada Excel Online, baik yang versi web maupun versi mobile baik itu Android maupun iOS.

Deskripsi Fungsi SORT Excel

Fungsi SORT digunakan untuk mengurutkan konten atau data dalam rentang/range atau Array.

Jadi dengan menggunakan fungsi SORT ini bisa mengurutkan data secara otomatis dari rentang data, dan karena Fungsi SORT ini merupakan salah satu dari fungsi Dynamic Array, Anda cukup menuliskan fungsi ini pada baris / kolom pertama saja, tidak perlu menyalin rumus ke baris bawahnya atau ke kolom sampingnya, jadi lebih hemat waktu.

Sintaksis Fungsi SORT Excel

Penulisan Syntax / sintaksis Fungsi Excel SORT adalah:

=SORT(array; [sort_index]; [sort_order]; [by_col])

Keterangan:

Argumen	Keterangan
array	Merupakan range atau Array yang akan diurutkan.
sort_index	Angka yang menunjukan kolom atau baris sebagai dasar pengurutan.
sort_order	Pilihan 1 dan -1, 1 atau jika dikosongkan untuk mengurutkan data secara Ascending (A to Z) dan angka -1 untuk mengurutkan data secara Descending (Z to A)
by_col	Menunjukkan arah pengurutan, TRUE untuk mengurutkan menurut kolom dan FALSE atau jika dikosongkan untuk mengurutkan menurut baris.

Argumen Array Fungsi SORT Excel

Argumen pertama dari Fungsi SORT adalah argumen Array, argumen ini sifatnya wajib di isi, yang merupakan range referensi yang ingin diurutkan.

Untuk lebih jelasnya, silahkan perhatikan gambar contoh fungsi SORT berikut ini:

Pada gambar diatas rentang / range data yang ingin diurutkan adalah range A2:A10, maka rumus excel yang dimasukkan pada sel B2 adalah =SORT(A2:A10).

Hasilnya terlihat pada sel B2:B10 sudah diurutkan secara ascending (A to Z).

Bagaimana jika ingin hasilnya urut secara descending (Z to A)?

Anda harus mengisi argumen sort_order dengan nilai -1, selengkapnya silahkan lanjutkan membaca tutorial ini.

Argumen Sort_Index Fungsi SORT Excel

Argumen Fungsi SORT yang kedua adalah sort_index, argumen ini sifatnya opsional. Fungsinya untuk menentukan kolom atau baris mana yang dijadikan rujukan dalam mengurutkan data.

Jika argumen sort_index tidak di isi atau dikosongkan maka otomatis kolom atau baris pertama yang akan dijadikan rujukan.

Untuk lebih jelasnya perhatikan contoh di bawah ini:

Pada gambar di atas range rujukan atau argumen Array dari fungsi SORT adalah range A3:C11.

Ada 4 contoh penggunaan fungsi SORT dengan menyertakan argumen sort_index-nya.

Fungsi SORT untuk mengurutkan data berdasarkan kolom pertama, rumus excel yang digunakan adalah =SORT(A3:C11; 1). Angka 1 merupakan argumen sort_order dari fungsi SORT untuk menentukan kolom 1 sebagai acuan untuk mengurutkan data di Excel.
Mengurutkan data di Excel berdasarkan acuan data dari kolom kedua, rumus excel yang digunakan adalah =SORT(A3:C11; 2).
Contoh ketiga, menjadikan kolom ketiga sebagai acuan untuk mengurutkan data dengan fungsi SORT, rumus excel yang digunakan adalah =SORT(A3:C11; 3).
Contoh terakhir adalah menggunakan dua kolom sebagai data acuan mengurutkan data di excel menggunakan fungsi SORT, penulisannya harus di apit tanda kurung kurawal "{...}".

Jadi untuk menjadikan kolom pertama dan kedua sebagai rujukan dalam mengurutkan data di excel, penulisan rumus excelnya adalah =SORT(A3:C11; {2;1}).

Mudah bukan?? kalau sudah faham bisa melanjutkan membaca tutorial ini.

Argumen Sort_Order Fungsi SORT Excel

Argumen opsional pada fungsi excel SORT selanjutnya atau yang ketiga adalah sort_order, argumen ini bisa Anda gunakan untuk menentukan pilihan pengurutan, apakah mau menggunakan ascending atau menggunakan descending.

Ascending (A to Z) adalah mengurutkan data menaik, dari yang terkecil ke yang terbesar (Sort Smallest to Largest).

Descending (Z to A) adalah kebalikan dari ascending yaitu mengurutkan data menurun, dari nilai terbesar ke terkecil (Sort Largest to Smallest).

Default dari argumen sort_order ini adalah ascending, jadi jika argumen sort_order ini tidak disertakan di fungsi SORT maka data akan diurutkan secara ascending.

Untuk menentukan pilihan ascending atau descending pada argumen sort_order fungsi SORT hanya perlu memasukkan angka berikut.

angka 1 jika ingin diurutkan secara ascending (A to Z).
angka -1 untuk menentukan urutan secara descending (Z to A).

Contoh penggunaan fungsi SORT Excel yang menggunakan argumen sort_order:

Sumber data referensi yang ingin diurutkan adalah range A2:A10 dan karena datanya cuma satu kolom maka untuk argumen sort_index-nya bisa dikosongkan saja.

Untuk mengurutkan secara Ascending (range C2:C10) rumus excel yang digunakan adalah =SORT(A2:A10;;1).

Dan untuk mengurutkan secara Desscending (range D2:D10) rumus excel yang digunakan adalah =SORT(A2:A10;;-1).

Contoh penggunaan argumen sort_order + sort_index:

Sumber data referensi yang ingin diurutkan adalah range F2:H10.

Misalnya saja Anda ingin mengurutkan data menurun berdasarkan kolom harga, dari harga yang tertinggi ke harga yang terendah, maka rumus excelnya adalah =SORT(F2:H10;3;-1).

Nilai argumen sort_index-nya adalah 3, karena kolom harga terletak di kolom ke-3.

Sedangkan argumen sort_order-nya di isi -1, karena ingin mengurutkan secara descending.

Argumen By_Col Fungsi SORT Excel

Argumen by_col pada fungsi SORT digunakan untuk menentukan arah dari pengurutan data, argumen ini berupa nilai logika TRUE atau FALSE.

Default arah pengurutan data menggunakan fungsi excel SORT adalah menurut baris, dan dengan menggunakan argumen by_col ini anda bisa mengubah arahnya menjadi menurut kolom.

Anda hanya perlu memasukkan nilai logika TRUE atau angka 1 pada argumen by_col pada fungsi SORT untuk mengubah arahnya menurut kolom.

Jika argumen by_col dikosongkan atau tidak dimasukkan, maka nilainya dianggap FALSE dan arah urutan sortirnya menjadi menurut baris.

Untuk lebih jelasnya, silahkan perhatikan contoh berikut ini:

Sumber data referensi yang ingin diurutkan adalah range A2:C10.

Pada gambar diatas terdapat 3 contoh dengan penggunaan argumen by_col Fungsi Excel SORT.

Untuk range E2:G10 rumus excel yang dimasukkan ke sel E2 adalah =SORT(A2:C10;;;FALSE), hasilnya arah pengurutan datanya menurut baris, tepatnya pada kolom pertama.
Pada range I2:K10 rumus excel yang dimasukkan ke sel I2 adalah =SORT(A2:C10;;;TRUE), hasilnya urutan kolomnya berubah, kolom pertama adalah kolom harga, kemudian kolom barang dan terakhir kolom jenis.
Karena argumen by_col-nya diisikan nilai TRUE, maka arah sortir-nya menjadi menurut kolom, tepatnya berdasarkan data pada baris pertama karena itu posisi kolomnya berubah.
Contoh ke-3 yakni pada range M2:O10 rumus excel yang dimasukkan ke sel M2 adalah =SORT(A2:C10;5;;TRUE), hasilnya urutan kolomnya berubah lagi, yaitu kolom harga, kolom jenis dan baru kolom barang.

Mengapa bisa begitu? ini karena pada argumen sort_index dimasukkan angka 5, dan argumen by_col-nya TRUE, maka excel akan mengurutkan data menurut kolom, berdasarkan data yang ada pada baris ke-5.

Sekian tutorial tentang penggunaan fungsi Excel SORT, semoga bermanfaat...

Lampiran

REDC - Fungsi SORT Excel Workbook (xlsx)

Download

Cara Impor Hasil Google Form / Spreadsheet ke Excel

noreply@blogger.com (Masdad Al-Falucky) — Tue, 14 Apr 2020 09:06:00 +0700

Rumus Excel [.] com - Google Form atau google formulir adalah alat yang berguna untuk mengumpulkan informasi yang mudah dengan cara yang efisien, baik itu untuk merencanakan acara, mengirim survei, membuat soal untuk siswa, membuat PPDB Online atau pun membuat kuis. Hasil dari Google Form ini biasanya di simpan dalam bentuk Google Spreadsheet.

Untuk memindahkan hasil dari Google Form ke Excel, biasanya adalah dengan mendownloadnya dalam bentuk Excel (xlsx) atau bisa juga format csv, baru kemudian di buka dan di olah menggunakan aplikasi Microsoft Excel, jika ada update / data baru pada Google Form maka Anda harus mengulang untuk mendownloadnya lagi.

Pada tutorial tentang Power Query kali ini, kita akan belajar memindahkan data hasil Google Form ke Excel sehingga nantinya tidak perlu lagi mendownload berulang kali setiap kali ada data baru, tapi cukup melakukan refresh pada data yang dibuat dengan Power Query Excel.

Hasil dari Google Form ini nantinya bisa juga di olah menjadi bentuk Pivot Table ataupun Tabel Excel sesuai dengan kebutuhannya. Contoh hasil olahan data dari Google Form ke Excel bisa dilihat pada gambar animasi berikut ini:

Untuk memudahkan memahami tutorial impor hasil Google Form / Spreadsheet ke Excel, pada akhir tutorial ini saya sudah menyediakan tutorial dalam format video.

Dan berikut langkah-langkah untuk memindahkan hasil Google Form / Spreadsheet ke Excel:

Buka kembali Google Form yang Anda buat, kemudian buka tab Respons, kemudian klik pada icon Google Spreadsheet untuk membuat Spreadsheet baru hasil dari Google Form, dan berinama sesuai keinginan.

Selanjutnya pada tampilan Google Spreadsheet hasil tanggapan dari Google Form, pilih menu Bagikan, kemudian aktifkan tautan untuk membagikan Google Spreadsheet, tujuannya agar nantinya Google Spreadsheet bisa di buka tanpa harus melakukan login.

Selanjutnya pilih menu File - Download - Nilai yang dipisahkan koma (.csv, sheet saat ini). Tujuannya untuk mengambil link download spreadsheet format csv yang nantinya akan dijadikan url source pada Power Query.

Pada browser yang Anda gunakan, silahkan buka menu Download atau Unduhan, kemudian klik kanan pada file csv hasil download tadi, kemudian klik Salin Alamat Link.

Sampai disini, untuk di proses Google Spredsheetnya sudah selesai, selanjutnya tinggal di olah di Microsoft Excel.

Update Juli 2020

Beberapa waktu yang lalu ternyata ada update pada link download csv pada google spreadsheet, yang menyebabkan gagal atau error ketika dimasukkan langsung ke Power Query Excel.

Untuk mengatasi hal ini ada trik khusus yang bisa dilakukan.

Misalnya url Spreadsheet-nya adalah:

https://docs.google.com/spreadsheets/d/1kH8uixNiKZNBnnNdPSvKqJ62qjFThBw-nWOHsdTAr_c/edit#gid=1324371342

hapus url yang berwarna merah atau url setelah ID Spreadsheetnya dan ganti dengan export?format=csv

Jadi url yang dimasukkan ke Power Query adalah:

https://docs.google.com/spreadsheets/d/1kH8uixNiKZNBnnNdPSvKqJ62qjFThBw-nWOHsdTAr_c/export?format=csv

Buka aplikasi Microsoft Excel, kemudian pilih tab Data - From Web dan paste-kan hasil copy url tadi ke kotak URL kemudian klik OK.

Jika tampil pilihan Access Web Content. lanjut klik Connect saja. Ini biasanya terjadi jika pertama kali mengakses url baru dengan Power Query.

Pada tampilan selanjutnya pilih Transform Data untuk mengolah datanya dengan Power Query.

Pada Power Query Editor, misalnya saja Anda tidak membutuhkan kolom TimeStamp maka Anda bisa langsung menghapusnya dengan cara klik kanan, dan pilih Remove.

Selanjutnya Anda bisa menambahkan kolom nomor otomatis dengan cara pilih menu Add Column - Index Column - From 1.

Ubah nama kolomnya (klik kanan pilih rename) dari Index menjadi NO, kemudian geser ke kolom pertama.
Jika pengolahan data sudah selesai, selanjutnya tinggal memindahkannya ke sheet Excel dengan cara pilih menu Home - Close & Load - Close & Load To.

Pada pilihan Impor Data, pilih Table dan pilih Existing worksheet itu jika ingin datanya ditampilkan dalam bentuk tabel pada worksheet, tapi jika ingin langsung di olah sebagai Pivot Table, Anda bisa memilih PivotTable Report.

Hasilnya akan tampil tabel hasil olahan Power Query yang datanya bersumber dari Google Spreadsheet hasil tanggapan Google Form.

Jika ada data terbaru pada Google Spreadsheet maka Anda tinggal me-refresh excelnya saja, maka data terbaru akan otomatis di download, tanpa perlu membuka terlebih dahulu Google Spreadsheet-nya jadi Anda lebih hemat waktu.

Lampiran

Impor dari Google Form Excel Workbook (xlsx)

Download

Sekian tutorial cara impor hasil Google Form / Spreadsheet ke Excel, Semoga Bermanfaat...

Video Tutorial

Rumus VLOOKUP Dengan 2 Lookup Value atau lebih di Excel

noreply@blogger.com (Masdad Al-Falucky) — Mon, 6 Apr 2020 14:01:00 +0700

Rumus Excel [.] com - Fungsi atau rumus VLOOKUP digunakan untuk mencari nilai atau data dari data tabel atau range, hanya saja fungsi VLOOKUP ini memiliki kekurangan yaitu ketika nilai yang di cari (Lookup Value) ada yang sama atau duplikat (ganda) maka yang ditampilkan adalah data yang paling atas yang pertama kali ditemukan.

Contohnya seperti gambar berikut ini:

Pada tabel rujukan / range referensi terdapat 3 nama Andi, yaitu Andi dari Jakarta, Andi dari Surabaya dan Andi dari Bandung. Dan ketika menggunakan rumus VLOOKUP untuk mencari alamat Andi, ternyata hasilnya adalah Jakarta semua, ini karena ketika VLOOKUP mencari nama Andi yang pertama kali ditemukan adalah alamat Jakarta.

Agar mendapatkan hasil pencarian yang sesuai, jika memang tetap ingin menggunakan rumus VLOOKUP maka diperlukan kolom bantuan (dummy / helper).

Caranya adalah tambahkan 1 kolom baru pada tabel rujukan, melalui tab Home - Insert - Insert Sheet Columns. Kemudian isikan nama header datanya misalnya saja Helper.

Kemudian pada baris selanjutnya (sel A2) masukkan rumus excel:

= B2 & "-" & COUNTIF ( $B$2:B2; B2 )

Dan salin rumus tersebut ke baris selanjutnya. Hasilnya kurang lebih seperti ini:

Dengan menggunakan fungsi COUNTIF, hasilnya jika terdapat nama yang sama maka akan ditambahkan angka sesuai jumlah data yang sama, seperti pada contoh ada 3 nama Andi, setelah menggunakan fungsi COUNTIF hasilnya ada Andi-1, Andi-2 dan Andi-3 jadi namanya tidak lagi duplikat / ganda tetapi menjadi unik sehingga bisa dijadikan lookup value.

Selanjutnya rumus VLOOKUP yang digunakan harus disesuaikan juga menjadi seperti ini:

=VLOOKUP( E2 &"-"& COUNTIF($E$2:E2 ;E2 ); $A$2:$C$7; 3; 0 )

Hasilnya adalah sebagai berikut:

Terlihat sekarang data hasil VLOOKUP-nya sudah sesuai, meski terdapat 3 nama yang sama namun tetap ditampilkan dengan benar sesuai alamat-nya dari yang paling atas.

Begitulah solusi untuk menampilkan data yang sama menggunakan rumus VLOOKUP, meski sebenarnya tabel model seperti di atas susunannya kurang baik. Model Tabel yang baik sesuai standar database harusnya memiliki setidaknya satu kolom data unik sebagai Primary Key sehingga memudahkan untuk melakukan pencarian.

Selain itu untuk menyelesaikan kasus pencarian data ganda seperti tabel data pada contoh di atas, tidak harus menggunakan VLOOKUP dengan kolom bantuan, bisa juga tanpa kolom bantuan dengan menggunakan rumus excel yang lain. Misalnya menggunakan rumus INDEX dsb.

Lampiran

VLOOKUP 2 Lookup Value Excel Workbook (xlsx)

Download

Unpivot Columns, Mengubah Data Kolom Menjadi Baris di Excel

noreply@blogger.com (Masdad Al-Falucky) — Fri, 20 Mar 2020 13:09:00 +0700

Rumus Excel [.] com - Pada Power Query ada satu fitur yang sangat berguna untuk mengubah data bentuk kolom menjadi data bentuk baris, yaitu Unpivot Columns. Selain menggunakan Power Query untuk mengubah data kolom menjadi baris sebenarnya bisa juga dilakukan dengan menggunakan rumus excel INDEX dan juga bisa juga menggunakan Fungsi VLOOKUP.

Untuk mengubah data kolom menjadi baris dengan menggunakan Power Query Excel, caranya adalah sebagai berikut:

Pilih tabel yang menjadi sumber datanya, kemudian pada tab Data grup Get & Transform Data pilih menu Form Table/Range seperti contoh pada gambar di bawah ini.

Setelah Power Query Editor terbuka, lakukan blok pada kolom NIS dan Nama, kemudian klik kanan dan pilih menu Unpivot Other Columns.

Pada Formula Bar Power Query Editor, ubah kata Attribute dan Value menjadi Mapel dan Nilai agar nama header pada 2 kolom terakhir menjadi Mapel dan Nilai.

Jika sudah selesai, tinggal impor data hasil olahan Power Query ke sel tujuan, pada Power Query Editor pilih menu Close & Load - Close & Load to...

Pilih target sel atau sel tujuan hasil Impor Power Query.

Maka hasilnya akan tampak kurang lebihnya seperti gambar dibawah ini:

Sekian pembahasan tentang mengubah data kolom menjadi baris di Excel dengan Power Query Unpivot Columns, Semoga Bermanfaat...

Aplikasi Excel Gratis dari Microsoft (Excel Online)

noreply@blogger.com (Masdad Al-Falucky) — Wed, 18 Mar 2020 09:59:00 +0700

Rumus Excel [.] com - Excel Online adalah aplikasi Excel gratis berbasis web jadi tidak perlu di install dan dapat di akses darimana saja asalkan terdapat koneksi internet, secara tampilan Excel Online ini pun sama seperti Excel versi desktop, hanya saja yang tersedia hanya fitur-fitur yang umum digunakan.

Selain tersedia melalui web, aplikasi Excel Online juga tersedia untuk ponsel Android dan IOS, Anda bisa mengunduhnya melalui Google Play Store atau Apple App Store, selain itu Excel Online juga tersedia pada Add-on Google Chrome.

Untuk dapat menggunakan Excel Online Anda terlebih dahulu harus memiliki akun Microsoft, jika belum punya maka Anda harus membuat akun baru terlebih dahulu, dan berikut langkah-langkah selengkapnya.

Buka tautan official Excel Online:

https://office.live.com/start/excel.aspx

Setelah itu ikuti 4 langkah seperti pada gambar berikut ini untuk membuat akun baru:

Pilih Buat akun baru.
Pilih Gunakan email Anda sebagai gantinya, jika ingin membuat akun baru menggunakan Email tapi jika ingin menggunakan Nomor Ponsel maka tinggal isikan nomor hp Anda.
Masukkan Email Anda, boleh email apa saja yang penting masih aktif dan bisa menerima email, kemudian klik Berikutnya.
Buat kata sandi untuk nantinya masuk ke akun Microsoft yang Anda buat, kemudian klik Berikutnya.

Selanjutnya Anda akan diminta untuk menverifikasi Email yang Anda gunakan untuk membuat akun Microsoft, silahkan buka Email Anda dan cari email yang di kirim oleh Tim Microsoft untuk melihat kode verifikasi yang dikirimkan.

Selanjutnya masukkan kode verifikasi sesuai Email yang Anda terima, kemudian klik Berikutnya. Dan selanjutnya akan tampil verifikasi Captcha, silahkan isikan teks sesuai gambar yang ditampilkan dan kemudian klik Berikutnya.

Jika Anda sampai langkah ini, berarti Anda sudah berhasil membuat akun Microsoft yang nantinya digunakan untuk membuka aplikasi Excel Online. Selanjutnya akan tampil seperti gambar di bawah ini, silahkan pilih YA saja.

Pada tampilan selanjutnya, pilih Buku kerja kosong baru, untuk membuat file Excel yang baru.

Maka aplikasi Excel Online akan ditampilkan.

Cara penggunaan Excel Online relatif sama seperti menggunakan aplikasi Excel desktop, jadi tidak perlu saya jelaskan lebih lanjut. Khusus untuk penyimpanan file excelnya akan otomatis disimpan di OneDrive, jadi Anda tidak perlu bingung mencari tombol Save / Simpan di Excel Online karena akan otomatis di simpan.

OneDrive atau sebelumnya bernama SkyDrive adalah cloud storage atau penyimpanan online gratis hingga 5 GB dari Microsoft yang dapat Anda gunakan untuk menyimpan file apapun, dengan memiliki akun Microsoft selain bisa digunakan untuk akses Excel Online, Anda juga mendapat akses ke OneDrive, Word Online, PowerPoint Online dan masih banyak lagi.

Salah satu kelebihan dari Excel Online adalah kemudahan untuk melakukan sharing file Excel, selain itu pada Excel Online Anda bisa menggunakan fungsi-fungsi baru yang biasanya hanya terdapat di Excel terbaru, seperti misalnya Fungsi CONCAT, Fungsi TEXTJOIN, Fungsi XLOOKUP, Fungsi Filter dan lain-lain.

Selain memiliki kelebihan, Excel Online tentu saja juga memiliki kekurangan, yakni tidak semua fitur yang terdapat di Excel desktop ada di Excel Online, seperti salah satunya tidak dapat membuat atau menjalankan Macro atau VBA.

Sekian pembahasan tentang Excel Online dan cara menggunakannya, Semoga bermanfaat...

Fungsi TEXTJOIN, Rumus Excel Menggabungkan Teks, isi sel atau range

noreply@blogger.com (Masdad Al-Falucky) — Tue, 17 Mar 2020 09:25:00 +0700

Rumus Excel [.] com - Fungsi TEXTJOIN Excel adalah fungsi excel yang digunakan untuk menggabungkan dua atau beberapa teks menjadi satu teks utuh dengan tambahan pemisah (delimiter). Selain menggabungkan teks, fungsi ini juga biasa digunakan untuk menggabungkan data dari dua sel atau range ke dalam satu sel Excel.

Fungsi TEXTJOIN merupakan salah satu fungsi excel yang tersedia di Excel 2019, Excel Online dan juga Excel 365, fungsi TEXTJOIN ini secara penggunaan fungsi serupa dengan Fungsi CONCAT ataupun Fungsi CONCATENATE Excel, hanya saja pada fungsi TEXTJOIN Anda dapat menentukan pemisah / pembatas antar teks yang digabungkan.

Penulisan Sintaksis dari Fungsi TEXTJOIN adalah:

=TEXTJOIN (delimiter; ignore_empty; text1, [text2], …)

Keterangan:

delimiter merupakan argumen wajib, adalah pemisah berupa string teks, atau satu atau beberapa karakter diapit oleh tanda kutip ganda, atau referensi ke sebuah string teks yang valid.
ignore_empty merupakan argumen wajib, fungsinya untuk mengabaikan referensi sel kosong, jika di isi TRUE maka sel kosong akan diabaikan dan jika FALSE maka sel kosong akan disertakan.
text1 merupakan argumen yang wajib di isi, yang merupakan item teks yang akan digabungkan. String, atau array string, seperti rentang sel.
text2 merupakan argumen opsional, sebagai item tambahan dan maksimal sampai 253 argumen.
Jika string yang dihasilkan melebihi 32.767 karakter, TEXTJOIN akan mengembalikan kesalahan #VALUE!.

Contoh Fungsi TEXTJOIN Excel

Berikut 5 contoh penggunaan fungsi / rumus TEXTJOIN.

Pada contoh gambar diatas, terdapat 5 contoh penggunaan rumus TEXTJOIN, meski model data sumbernya hanya ada 2, yaitu baris 1-3 tanpa ada sel kosong dan 4-5 dengan sel kosong.

Rumus Excel pada sel F1

=TEXTJOIN(;;A1:E1)

Pada contoh pertama ini, argumen1 (delimiter) dan argumen2 (ignore_empty) tidak di isi, tapi karena 2 argumen ini wajib ada maka untuk separator-nya (titik koma / koma) harus tetap disertakan. hasilnya adalah ABCDE.

Rumus Excel pada sel F2

=TEXTJOIN(" ";;A2:E2)

Pada contoh kedua, argumen delimiter di isi dengan spasi dan argumen ignore_empty dibiarkan saja yang sama artinya bernilai TRUE. hasilnya adalah A B C D E terdapat spasi diantara teks yang digabungkan.

Rumus Excel pada sel F3

=TEXTJOIN(", ";;A2:E2)

Pada contoh ketiga, argumen delimiter di isi dengan koma dan spasi dan argumen ignore_empty dibiarkan saja. hasilnya adalah A, B, C, D, E terdapat koma dan spasi diantara teks yang digabungkan.

Rumus Excel pada sel F4 dengan sumber referensi sel yang mengandung sel kosong.

=TEXTJOIN(" ";TRUE;A4:E4)

Pada contoh keempat, argumen delimiter di isi dengan spasi dan argumen ignore_empty di isi TRUE. hasilnya adalah A B C karena argumen ignore_empty bernilai TRUE maka sel kosong yang ada pada sel C4 dan D4 tidak ikut digabungkan alias diabaiakan.

Rumus Excel pada sel F4 dengan sumber referensi sel yang mengandung sel kosong.

=TEXTJOIN(" ";FALSE;A4:E4)

Pada contoh kelima, argumen delimiter di isi dengan spasi dan argumen ignore_empty di isi FALSE. hasilnya adalah A B Ckarena argumen ignore_empty bernilai FALSE maka sel kosong yang ada pada sel C5 dan D5 ikut digabungkan dan dijadikan spasi.

Lampiran

Fungsi Excel TEXTJOIN Excel Workbook (xlsx)

Download

Sekian pembahasan tentang cara menggunakan fungsi TEXTJOIN Excel, Semoga Bermanfaat...

Cara Menggunakan Fungsi CONCAT di Excel 2019

noreply@blogger.com (Masdad Al-Falucky) — Mon, 16 Mar 2020 12:29:00 +0700

Rumus Excel [.] com - Fungsi CONCAT adalah salah satu fungsi baru yang ada di Excel 2019, Fungsi CONCAT tersedia juga untuk pengguna Excel 365 dan Excel Online. Fungsi CONCAT ini menggantikan fungsi excel CONCATENATE, meski begitu penggunaan fungsi CONCATENATE akan tetap ada untuk kompatibilitas dengan versi Excel yang lebih lama.

Fungsi CONCAT digunakan menggabungkan teks dari beberapa rentang atau string, namun tidak memberikan argumen pemisah atau IgnoreEmpty.

Sintaksis Fungsi CONCAT

Penulisan Sintaksis dari rumus excel CONCAT / fungsi CONCAT adalah:

=CONCAT (text1; [text2]; …)

Keterangan:

text1 merupakan argumen yang wajib di isi, yang merupakan item teks yang akan digabungkan. String, atau array string, seperti rentang sel.
text2 merupakan argumen opsional, sebagai item tambahan dan maksimal sampai 253 argumen.
Jika string yang dihasilkan melebihi 32.767 karakter, CONCAT akan mengembalikan kesalahan #VALUE!.

Sebagai pengganti dari fungsi CONCATENATE, fungsi CONCAT tentu memiliki kelebihan yaitu menggabungkan data berdasarkan range, bukan hanya per sel.

Fungsi CONCATENATE vs Fungsi CONCAT

Misalnya saja ingin menggabungkan isi dari sel A1 sampai A5, jika menggunakan fungsi CONCATENATE penulisan rumus excel-nya:

=CONCATENATE ( A1; A2; A3; A4; A5 )

Sedangkan jika menggunakan fungsi CONCAT:

=CONCAT ( A1:A5 )

Lebih simpel dan tentu saja lebih cepat penulisan rumus excel-nya.

Contoh Fungsi CONCAT Excel

Berikut contoh gambar dari fungsi / rumus CONCAT Excel.

Perhatikan!! pada hasil CONCAT Excel pada sel F1 sampai F5.

Rumus Excel yang digunakan:
- Pada sel F1 rumus CONCAT Excel-nya =CONCAT(A1:E1)
- Pada sel F2 rumus CONCAT Excel-nya =CONCAT(A2:E2)
- Pada sel F3 rumus CONCAT Excel-nya =CONCAT(A3:E3)
- Pada sel F4 rumus CONCAT Excel-nya =CONCAT(A4:E4)
- Pada sel F5 rumus CONCAT Excel-nya =CONCAT(A5:E5)

Seperti penjelasan diawal bahwa fungsi CONCAT Excel ini mengabaikan sel yang kosong (Ignore Empty) maka dari itu hasil rumus CONCAT Excel seperti terlihat pada gambar di atas mengabaikan semua sel kosong.

Selain itu fungsi CONCAT juga tidak menggunakan argumen pemisah / delimiter, jika Anda ingin menggabungkan isi data dari sebuah range dengan menggunakan pemisah, Anda bisa menggunakan fungsi TEXTJOIN.

Lampiran

Fungsi Excel CONCAT Excel Workbook (xlsx)

Download

Sekian pembahasan tentang fungsi CONCAT Excel, Semoga Bermanfaat...