Cara Join Tabel Menggunakan Microsoft Excel, Google Sheet, SQL, dan R Programming

Beda suku tentu beda bahasa, demikian halnya dalam penggunaan tools dalam dunia teknologi di bidang pengolahan data, untuk melakukan sebuah perintah, tentu akan berbeda-beda bahasa dan cara yang digunakan, meskipun sebenarnya perintah itu memiliki logika dan tujuan yang sama.

Pada tulisan ini saya akan berbagi bagaimana caranya menggabungkan 2 buah tabel berbeda dengan merujuk pada satu referensi yang memiliki kesamaan pada masing-masing tabel tersebut. Ini bukanlah hal yang sulit di lakukan, melainkan sebuah cara simpel, namun akan sangat bermanfaat dan banyak digunakan ketika kita bekerja di bidang pengolahan data pada sebuah perusahaan atau instansi, dimana saat ini data merupakan salah satu hal penting yang harus dimiliki oleh setiap perusahaan atau instansi, tanpa data tentu sebuah usaha akan sulit berkembang, karena data bisa di jadikan sebagai sebuah aset, sebuah catatan, sebuah informasi, sebuah tolok ukur kinerja, dan masih banyak lagi manfaat data yang dapat digunakan untuk kepentingan lain guna memperbaiki kinerja di masa yang akan datang, tak sesimpel itu karena data is amazing, data is everything, dan tak se alay itu juga hahaha

because everything is Allah :)

Tidak perlu basa basi terlalu panjang, langsung saja kali ini saya akan memberikan cara menggabungkan data pada 2 buah tabel menggunakan 4 tools yang berbeda, yakni Microsoft Excel, Google Sheet, SQL, dan R Programming. Sebenarnya dari ke empat tools tersebut, perintah yang digunakan pada Microsoft Excel dan Google Sheet hampir sama, namun hanya ada sedikit perbedaan yang mungkin dapat dinilai bahwa google sheet lebih simpel penggunaan formulanya di bandingkan dengan excel. Berikut contoh tabel yang berisi data simulasi yang akan saya gabungkan, dimana tabel 1 adalah tabel 'identitas' dan tabel 2 adalah tabel 'karyawan'.

Tabel 1 Identitas

ID	Nama	Domisili
112	Baron Setyo Utomo	Boyolali
234	Muhammad Mulajati	Bima
345	Khair Norasid	Palangkaraya
567	Slamet Abtohi	Subulussalam
789	Alfianisa Shafira	Sleman
890	Yenni Tria Paramitha	Tebing Tinggi
901	Feby Syafitri	Ternate

Tabel 2 Karyawan

Emp_id	Job_position	Location
901	Supervisor	Surabaya
234	Senior Manager	Jakarta
890	Data Analyst	Yogyakarta
789	HRD Director	Semarang
112	Senior Data Engineer	Jakarta

Berdasarkan tabel di atas, saya akan menggabungkan 2 buah tabel tersebut menjadi 1 buah tabel dimana ID pada tabel identitas sama dengan Emp_id pada tabel karyawan. Konten dalam tabel akan menyesuaikan dan mencari kecocokan ID antara tabel 1 dan tabel 2, sehingga output tabel yang dihasilkan akan berisi ID, Nama, Domisili, Job_position dan Location.

1. Microsoft Excel

Untuk menggabungkan dua buah tabel dalam Microsoft Excel dapat menggunakan fungsi VLOOKUP (Tabel Vertikal) dan HLOOKUP (Tabel Horizontal), selain itu juga dapat menggunakan fungsi INDEX MATCH. Pada kasus ini saya akan menggunakan fungsi VLOOKUP untuk menggabungkan tabel karyawan ke dalam tabel identitas.

Adapun perintah penggunaan fungsi VLOOKUP disini sebagai berikut:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]

lookup_value

berisi data referensi pada tabel identitas, dalam hal ini berisi ID, yakni ID pada tabel identitas yang nantinya akan merujuk pada Emp_id pada tabel karyawan. Berdasarkan data di atas, ID karyawan berada pada kolom A baris ke 4 hingga baris 10, sehingga untuk menampilkan data yang sesuai dengan ID pada baris ke 4 nilai lookup_value dapat dituliskan dengan A4 atau $A4 untuk mengunci kolom.

table_array

berisi range pada tabel karyawan (sebagai tabel rujukan), yang di dalamnya juga berisi range kolom Emp_id dan kolom lain yang berisi informasi yang akan ditampilkan pada tabel identitas. Berdasarkan tabel di atas, table_array diisi dengan menuliskan range data Emp_id, Job_position dan Location, dimana data tersebut berada pada posisi kolom A baris 14 hingga kolom C baris 18. Sehingga penulisan formulanya A14:C18 atau $A$14:$C$18, lebih baik digunakan tanda $ agar range yang terseleksi tidak berubah ketika formula ditarik atau di copy pada sel berikutnya.

col_index_num

berisi angka/index yang menunjukkan urutan kolom pada tabel karyawan terseleksi, yang akan ditampilkan/digabungkan ke dalam tabel identitas, Job_position pada tabel karyawan berada pada urutan nomor 2, dari range yang diseleksi, sehingga pada col_index_num dituliskan 2.

[range_lookup]

berisi fungsi logika TRUE dan FALSE, FALSE digunakan ketika ingin menggabungkan data yang benar-benar sama antara ID dan Emp_id. Sedangkan TRUE digunakan untuk menggabungkan data ketika kita hanya ingin mencari nilai yang paling mendekati dengan referensi yang kita gunakan, sehingga dalam hal ini digunakan fungsi FALSE.

Formula yang digunakan untuk menambahkan data pada kolom Job_position adalah:

=VLOOKUP($A5,$A$14:$C$18,2,FALSE)

Untuk menghilangkan tanda #N/A pada tabel, maka cukup digunakan fungsi IF dan ISNA seperti berikut:

=IF(ISNA(VLOOKUP($A5,$A$14:$C$18,2,FALSE)),"",VLOOKUP($A5,$A$14:$C$18,2,FALSE))

Kemudian, untuk menambahkan data Job_position pada baris berikutnya cukup dilakukan dengan menarik ke bawah pointer pada sel yang berisi formula, atau dapat juga dilakukan dengan cara mencopy formula.

Seperti cara sebelumnya, untuk menambahkan data Location pada tabel identitas juga dilakukan dengan cara yang sama, hanya saja col_index_num di ubah menjadi 3, karena kolom Location berada pada urutan ketiga dari range yang di seleksi. Sehingga diperoleh hasil seperti berikut:

Catatan:

Fungsi VLOOKUP hanya bisa digunakan ketika data yang dijadikan referensi berada pada sebelah kiri tabel, ketika data referensi yang digunakan tidak berada pada sebelah kiri, maka solusi alternatif dapat digunakan adalah fungsi INDEX MATCH. Pada fungsi INDEX MATCH tidak berlaku syarat posisi data referensi harus berada di sebelah kiri, baik di kanan maupun di kiri bisa digunakan, tapi dalam postingan ini saya tidak akan membahas fungsi INDEX MATCH, mungkin di lain kesempatan hehehe :)

2. Google Sheet

Tidak beda jauh dengan fungsi pada formula Excel, bahkan sama persis, untuk menggabungkan tabel pada Google Sheet juga menggunakan fungsi VLOOKUP, karena memang Google Sheet di desain kompatibel dengan formula Excel. Namun pada Google Sheet terdapat fungsi ARRAYFORMULA yang dapat digunakan untuk memudahkan dan mempercepat kinerja. ARRAYFORMULA berfungsi untuk data range atau multi cell, sehingga cukup dengan menuliskan satu formula, maka jika ditambahkan dengan fungsi ARRAYFORMULA operasi dapat dilakukan pada beberapa range atau multi cell.

Ketika menggunakan fungsi VLOOKUP pada Excel, untuk mengotomatisasi pada sel berikutnya kita harus menarik pointer atau mencopy formula. Dengan menambahkan ARRAYFORMULA pada baris teratas, maka akan secara otomatis melakukan operasi fungsi VLOOKUP pada sel berikutnya.
Jadi, lebih simpel bukan?

Berikut formula yang dapat kita gunakan (dengan asumsi posisi kolom dan baris data pada google sheet sama dengan posisi data pada Excel yang kita bahas sebelumnya).

=ARRAYFORMULA(VLOOKUP({$A$4:$A$10},$A$14:$C$18,{2,3},FALSE))

Dengan menambahkan perintah ARRAYFORMULA, kita juga harus merubah lookup_value menjadi sebuah range, yakni A4 hingga A10, demikian halnya kita juga bisa secara langsung menampilkan kolom Location, tanpa harus dua kali kerja, sehingga pada col_index_num bisa di ubah menjadi {2,3} sesuai urutan kolom yang diseleksi, yakni Job_position pada urutan ke-2 dan Location pada urutan ke-3.

3. SQL

Untuk menggabungkan tabel menggunakan SQL digunakan fungsi JOIN, dimana pada SQL terdapat beberapa fungsi JOIN diantaranya LEFT JOIN, INNER JOIN, RIGHT JOIN dan FULL JOIN. Berikut perbedaannya :

LEFT JOIN digunakan untuk menggabungkan tabel dimana data akan ditampilkan secara keseluruhan pada tabel pertama (kiri) namun record pada tabel kedua (kanan) yang kosong akan ditampilkan dengan isi NULL.

RIGHT JOIN memiliki fungsi yang bertolak belakang dengan LEFT JOIN, dimana right join akan menampilkan data secara keseluruhan pada tabel kedua (kanan), namun NULL pada tabel pertama (kiri).

INNER JOIN berguna untuk menggabungkan tabel dan harus membutuhkan satu kolom unik yang ada pada primary key maupun foreign key. Apabila ada foreign key maupun primary key yang kosong, penggunaan fungsi ini hanya akan memunculkan data yang foreign key-nya memiliki record yang isi saja.

FULL JOIN berfungsi untuk menggabungkan tabel dimana data akan ditampilkan secara keseluruhan antara tabel pertama dengan tabel kedua.

Pada postingan ini, saya hanya akan menggunakan contoh penggunaan INNER JOIN, yakni untuk menampilkan data yang cocok antara kedua tabel, sedangkan data yang tidak sesuai/tidak cocok tidak akan di tampilkan.

Langkah pertama buat tabel identitas dan tabel karyawan terlebih dahulu dalam sebuah database

INSERT INTO karyawan(Emp_id,Job_position,Location)

VALUES (901,'Supervisor','Surabaya'),

(234,'Senior Manager', 'Jakarta'),

(890,'Data Analyst', 'Yogyakarta'),

(789,'HRD Director','Semarang'),

(112,'Senior Data Engineer','Jakarta')

INSERT INTO identitas(ID, Nama, Domisili)

VALUES (112,'Baron Setyo Utomo','Boyolali'),

(234,'Muhammad Mulajati','Bima'),

(345,'Khair Norasid','Palangkaraya'),

(567,'Slamet Abtohi','Subulussalam'),

(789,'Alfianisa Shafira','Sleman'),

(890,'Yenni Tria Paramitha','Tebing Tinggi'),

(901,'Feby Syafitri','Ternate')

Setelah masing-masing tabel dibuat, lakukan join tabel menggunakan perintah INNER JOIN seperti berikut:

SELECT identitas.ID, identitas.Nama, identitas.Domisili, karyawan.Job_position, karyawan.Location

FROM identitas

INNER JOIN karyawan

ON identitas.ID=karyawan.Emp_id

Sehingga akan tampil output tabel hasil join seperti berikut:

Mudah bukan?

4. R-Programming

Ada banyak cara yang dapat digunakan untuk menggabungkan dua atau lebih tabel menggunakan program R, apalagi saat ini bahasa pemrograman R sudah di dukung oleh banyaknya packages yang tersedia dan dibuat untuk memudahkan penggunanya dalam melakukan berbagai analisis dan pengolahan data. Adapun langkah-langkahnya sebagai berikut:

Definisikan table (data frame) terlebih dahulu agar memudahkan proses pengolahan, dalam kasus ini tabel identitas saya definisikan sebagai 'identitas', dan tabel karyawan saya definisikan sebagai 'karyawan'.

Setelah masing-masing tabel terdefinisi, kemudian untuk melakukan join tabel dapat dilakukan dengan menggunakan perintah merge atau dapat juga dilakukan dengan menggunakan packages "dplyr", "data.table", atau packages "sqldf" dengan tujuan agar lebih memudahkan penggunaan, berikut masing-masing caranya.

Menggunakan perintah 'merge'

merge(x=identitas, y=karyawan, by.x="ID", by.y="Emp_id", all=FALSE)

Menggunakan packages dplyr dengan perintah 'inner_join'

library(dplyr)
inner_join(identitas, karyawan, by=c("ID"="Emp_id"))

Menggunakan packages 'data.table'

library(data.table)
dt1 <- data.table(identitas, key = "ID")
dt2 <- data.table(karyawan, key = "Emp_id")
dt1[dt2]

Menggunakan packages "sqldf"

library(sqldf)
df3 <- sqldf("SELECT ID, Nama, Domisili, Job_position, Location
FROM identitas
INNER JOIN karyawan ON ID=Emp_id")

Dari berbagai cara diatas, akan menghasilkan output yang sama seperti terlihat pada gambar berikut:

Demikian postingan ini saya buat, buat para master tentu ini hal yang sangat biasa, saya juga masih belajar, tujuannya hanya berbagi apa yang saya ketahui, tidak bermaksud menggurui, semoga bermanfaat dan happy weekend :)

- Thank You -