XLOOKUP, FILTER, UNIQUE, dan TEXTSPLIT untuk Admin Pemula: Contoh Data Sekolah, Stok, dan Rekap UMKM
Diperbarui: 15 Mei 2026. Artikel ini membahas cara memakai rumus Excel modern seperti XLOOKUP, FILTER, UNIQUE, dan TEXTSPLIT untuk pekerjaan admin pemula, lengkap dengan contoh data sekolah, stok barang, dan rekap UMKM.
Banyak admin pemula masih mengandalkan copy-paste manual, filter manual, dan cari data satu per satu. Padahal, beberapa pekerjaan harian bisa dibuat jauh lebih cepat dengan rumus Excel modern. Misalnya mencari nama siswa dari NISN, menampilkan daftar transaksi yang masih piutang, membuat daftar kota unik, atau memecah data alamat yang masih tergabung dalam satu sel.
Empat rumus yang sangat membantu adalah XLOOKUP, FILTER, UNIQUE, dan TEXTSPLIT. XLOOKUP berguna untuk mengambil data dari tabel master. FILTER berguna untuk menampilkan baris yang memenuhi kriteria. UNIQUE berguna untuk membuat daftar nilai tanpa duplikat. TEXTSPLIT berguna untuk memecah teks menjadi beberapa kolom atau baris. Jika dipakai bersama, keempatnya bisa menghemat banyak waktu kerja admin.
- XLOOKUP dipakai untuk mencari data dan mengambil informasi terkait dari tabel lain.
- FILTER dipakai untuk menampilkan data yang memenuhi kriteria tertentu.
- UNIQUE dipakai untuk membuat daftar nilai unik tanpa duplikat.
- TEXTSPLIT dipakai untuk memecah teks berdasarkan pemisah seperti koma, tanda hubung, atau garis miring.
- Rumus ini paling nyaman di Excel Microsoft 365 atau versi Excel modern yang sudah mendukung dynamic array.
- Jika rumus tidak tersedia di Excel Anda, gunakan alternatif seperti VLOOKUP, Filter biasa, Remove Duplicates, atau Text to Columns.
Kenapa Admin Pemula Perlu Belajar Rumus Modern?
Pekerjaan admin sering berulang: mencari data, memfilter data, membersihkan data, dan membuat rekap. Jika semuanya dilakukan manual, pekerjaan menjadi lambat dan rawan salah. Satu kesalahan copy-paste bisa membuat nama siswa tertukar, stok barang salah, atau rekap omzet tidak cocok.
Rumus modern membantu pekerjaan itu menjadi lebih otomatis. Misalnya, admin sekolah cukup mengetik NISN, lalu nama siswa dan kelas muncul otomatis. Admin toko bisa menampilkan hanya barang yang stoknya menipis. Admin UMKM bisa membuat daftar pelanggan unik dan memecah alamat yang masih tergabung menjadi kota, kecamatan, dan kode pos.
Untuk latihan dasar dengan data UMKM, stok, dan rekap, Anda juga bisa membaca artikel Beginisob: Contoh Data Excel untuk Latihan: UMKM, Penjualan, Stok, dan Rekap.
Tabel Ringkas Fungsi XLOOKUP, FILTER, UNIQUE, dan TEXTSPLIT
| Fungsi | Kegunaan Utama | Contoh Pekerjaan Admin | Contoh Rumus Sederhana | Alternatif Jika Tidak Ada |
|---|---|---|---|---|
| XLOOKUP | Mencari nilai dan mengambil data terkait dari tabel lain. | Ambil nama siswa dari NISN, harga produk dari kode barang, nama pelanggan dari ID. | =XLOOKUP(A2;Master!A:A;Master!B:B;"Tidak ditemukan") |
VLOOKUP atau INDEX+MATCH. |
| FILTER | Menampilkan baris data sesuai kriteria. | Tampilkan siswa kelas 7A, barang stok menipis, transaksi piutang. | =FILTER(A2:E100;E2:E100="Piutang";"Tidak ada") |
Filter manual, Advanced Filter, atau Pivot Table. |
| UNIQUE | Mengambil daftar nilai unik tanpa duplikat. | Daftar kelas, daftar kota pelanggan, daftar produk yang pernah terjual. | =UNIQUE(C2:C100) |
Remove Duplicates atau Pivot Table. |
| TEXTSPLIT | Memecah teks berdasarkan pemisah. | Memecah kode “BRG-001-MAKANAN”, alamat “Kota/Kecamatan/Desa”, atau nama file. | =TEXTSPLIT(A2;"-") |
Text to Columns, LEFT/MID/RIGHT, atau Power Query. |
Contoh Data Latihan yang Dipakai
Agar mudah dipahami, artikel ini memakai tiga contoh data: data siswa, data stok, dan data transaksi UMKM.
1. Contoh data siswa
| NISN | Nama | Kelas | Kota | Status |
|---|---|---|---|---|
| 101001 | Ahmad | 7A | Jakarta | Aktif |
| 101002 | Fatimah | 7B | Bandung | Aktif |
| 101003 | Hasan | 7A | Jakarta | Pindah |
| 101004 | Aisyah | 8A | Surabaya | Aktif |
2. Contoh data stok
| Kode_Produk | Nama_Produk | Kategori | Stok | Harga_Jual |
|---|---|---|---|---|
| BRG-001 | Keripik Pisang 100 gr | Makanan | 25 | 15000 |
| BRG-002 | Kue Kering 250 gr | Makanan | 8 | 35000 |
| BRG-003 | Botol Minum | Peralatan | 3 | 25000 |
| BRG-004 | Sabun Cuci | Rumah Tangga | 40 | 12000 |
3. Contoh data transaksi UMKM
| Tanggal | No_Invoice | Pelanggan | Kode_Produk | Qty | Status_Bayar | Keterangan_Alamat |
|---|---|---|---|---|---|---|
| 15/05/2026 | INV-001 | Ani | BRG-001 | 3 | Lunas | Jakarta/Kebayoran/12210 |
| 15/05/2026 | INV-002 | Budi | BRG-002 | 2 | Piutang | Bandung/Cicendo/40171 |
| 16/05/2026 | INV-003 | Ani | BRG-003 | 1 | Lunas | Jakarta/Kebayoran/12210 |
Cara Memakai XLOOKUP untuk Mengambil Data Otomatis
XLOOKUP dipakai ketika Anda punya tabel master dan ingin mengambil data otomatis berdasarkan kode. Contoh paling umum: mencari nama produk berdasarkan kode produk.
Contoh kasus stok UMKM
Misalnya di sheet Master_Produk:
- Kolom A = Kode_Produk
- Kolom B = Nama_Produk
- Kolom C = Kategori
- Kolom D = Harga_Jual
Di sheet Transaksi, Anda mengetik kode produk di D2. Untuk mengambil nama produk otomatis, gunakan:
=XLOOKUP(D2;Master_Produk!$A:$A;Master_Produk!$B:$B;"Kode tidak ditemukan")
Untuk mengambil harga jual:
=XLOOKUP(D2;Master_Produk!$A:$A;Master_Produk!$D:$D;0)
Jika kode produk ditemukan, Excel akan mengambil data dari baris yang sama di tabel master. Jika tidak ditemukan, rumus menampilkan “Kode tidak ditemukan” atau angka 0 sesuai rumus yang dipakai.
Contoh kasus admin sekolah
Jika Anda punya daftar siswa di sheet Data_Siswa, lalu ingin mengambil nama siswa berdasarkan NISN:
=XLOOKUP(A2;Data_Siswa!$A:$A;Data_Siswa!$B:$B;"NISN tidak ditemukan")
Rumus ini sangat berguna untuk membuat formulir, rekap nilai, absensi, atau pencarian data siswa tanpa harus mencari manual.
Cara Memakai FILTER untuk Menampilkan Data Sesuai Kriteria
FILTER dipakai untuk menampilkan data yang memenuhi syarat tertentu. Berbeda dengan filter manual, FILTER menghasilkan daftar otomatis di area lain. Jika data sumber berubah, hasil FILTER ikut berubah.
Contoh: tampilkan transaksi piutang
Misalnya data transaksi ada di A2:G100, dan kolom F berisi Status_Bayar. Untuk menampilkan semua transaksi yang masih piutang:
=FILTER(A2:G100;F2:F100="Piutang";"Tidak ada piutang")
Rumus ini akan menampilkan semua baris yang statusnya Piutang. Jika tidak ada, hasilnya “Tidak ada piutang”.
Contoh: tampilkan barang stok menipis
Jika data stok ada di A2:E100 dan kolom D berisi stok, tampilkan barang dengan stok kurang dari 10:
=FILTER(A2:E100;D2:D100<10;"Tidak ada stok menipis")
Contoh: tampilkan siswa aktif kelas 7A
Jika data siswa ada di A2:E100, kolom C berisi kelas, dan kolom E berisi status:
=FILTER(A2:E100;(C2:C100="7A")*(E2:E100="Aktif");"Tidak ada data")
Tanda bintang * berarti semua syarat harus terpenuhi. Dalam contoh ini, siswa harus kelas 7A dan statusnya Aktif.
Cara Memakai UNIQUE untuk Membuat Daftar Unik
UNIQUE dipakai untuk mengambil daftar nilai tanpa duplikat. Ini sangat berguna untuk membuat daftar kelas, daftar kota, daftar pelanggan, daftar produk, atau daftar kategori.
Contoh: daftar kota siswa
Jika kolom D berisi kota siswa:
=UNIQUE(D2:D100)
Hasilnya adalah daftar kota yang muncul satu kali saja. Jika Jakarta muncul 30 kali, hasil UNIQUE tetap menampilkan Jakarta satu kali.
Contoh: daftar pelanggan UMKM
Jika kolom C di tabel transaksi berisi nama pelanggan:
=UNIQUE(C2:C100)
Contoh: daftar kategori produk
Jika kolom C di Master_Produk berisi kategori:
=UNIQUE(Master_Produk!C2:C100)
=SORT(UNIQUE(C2:C100)).
Jika masalah Anda adalah data dobel, baca panduan Beginisob: Cara Mencari Data Ganda di Excel.
Cara Memakai TEXTSPLIT untuk Memecah Teks
TEXTSPLIT dipakai untuk memecah teks berdasarkan pemisah tertentu. Ini berguna jika data masih tergabung dalam satu sel.
Contoh: memecah alamat
Misalnya A2 berisi:
Jakarta/Kebayoran/12210
Gunakan rumus:
=TEXTSPLIT(A2;"/")
Hasilnya akan terpecah menjadi tiga kolom: Jakarta, Kebayoran, dan 12210.
Contoh: memecah kode produk
Misalnya A2 berisi:
BRG-001-MAKANAN
Gunakan:
=TEXTSPLIT(A2;"-")
Hasilnya menjadi BRG, 001, dan MAKANAN.
Contoh: memecah daftar nama
Misalnya A2 berisi:
Ani, Budi, Cici, Doni
Gunakan:
=TEXTSPLIT(A2;", ")
Jika data dipisahkan dengan koma tanpa spasi, gunakan ",". Jika dipisahkan koma dan spasi, gunakan ", ". Pastikan pemisah yang Anda tulis sama dengan data asli.
Contoh Gabungan Rumus untuk Admin Sekolah, Stok, dan UMKM
Rumus modern menjadi jauh lebih kuat jika digabung. Berikut beberapa contoh praktis.
1. Daftar siswa aktif per kelas
Misalnya H2 berisi kelas yang ingin dicari, seperti 7A. Data siswa ada di A2:E100:
=FILTER(A2:E100;(C2:C100=H2)*(E2:E100="Aktif");"Tidak ada siswa aktif")
Jika H2 diganti menjadi 8A, hasil otomatis berubah menjadi siswa aktif kelas 8A.
2. Daftar produk stok menipis berdasarkan kategori
Misalnya H2 berisi kategori, seperti Makanan. Data produk ada di A2:E100:
=FILTER(A2:E100;(C2:C100=H2)*(D2:D100<10);"Tidak ada stok menipis")
3. Ambil harga produk lalu hitung total transaksi
Di sheet Transaksi, kode produk ada di D2 dan qty ada di E2. Ambil harga dari Master_Produk, lalu hitung total:
Harga_Jual: =XLOOKUP(D2;Master_Produk!$A:$A;Master_Produk!$E:$E;0) Total: =E2*XLOOKUP(D2;Master_Produk!$A:$A;Master_Produk!$E:$E;0)
4. Buat daftar pelanggan unik dari transaksi piutang
Jika kolom C berisi pelanggan dan kolom F berisi status bayar:
=UNIQUE(FILTER(C2:C100;F2:F100="Piutang";"Tidak ada piutang"))
5. Pecah alamat lalu ambil daftar kota unik
Jika kolom G berisi alamat dengan format Kota/Kecamatan/KodePos, pecah dulu dengan TEXTSPLIT di kolom bantu. Setelah kota berada di kolom H, gunakan:
=UNIQUE(H2:H100)
Untuk input yang konsisten, gunakan dropdown. Panduannya ada di Beginisob: Cara Membuat Dropdown List di Excel Pakai Data Validation.
Alternatif Jika Rumus Tidak Tersedia
Tidak semua versi Excel memiliki XLOOKUP, FILTER, UNIQUE, dan TEXTSPLIT. Jika rumus tidak dikenali, jangan panik. Gunakan alternatif berikut.
| Rumus Modern | Tanda Tidak Tersedia | Alternatif | Kapan Dipakai? |
|---|---|---|---|
| XLOOKUP | Muncul error #NAME? atau rumus tidak dikenal. |
VLOOKUP atau INDEX+MATCH. | Untuk mencari data dari tabel master. |
| FILTER | Rumus tidak dikenal atau hasil spill tidak muncul. | Filter manual, Advanced Filter, atau Pivot Table. | Untuk menampilkan data sesuai kriteria. |
| UNIQUE | Rumus tidak dikenal. | Remove Duplicates atau Pivot Table. | Untuk membuat daftar unik. |
| TEXTSPLIT | Rumus tidak dikenal. | Text to Columns atau rumus LEFT/MID/RIGHT. | Untuk memecah teks menjadi beberapa kolom. |
Jika file akan dipakai banyak orang dengan versi Excel berbeda, tulis catatan versi rumus di bagian atas file. Ini mencegah file rusak saat dibuka di Excel lama.
Checklist Agar Rumus Tidak Error
Sebelum menyalahkan rumus, cek dulu kualitas data. Banyak error bukan karena rumus salah, tetapi karena data sumber tidak rapi.
| Masalah | Tanda di Excel | Penyebab Umum | Solusi |
|---|---|---|---|
| Kode tidak ditemukan | XLOOKUP menghasilkan “Tidak ditemukan”. | Kode produk/NISN typo atau tidak ada di master. | Cek kode di tabel master dan transaksi. |
| Hasil FILTER kosong | Muncul pesan “Tidak ada data”. | Kriteria tidak sama persis dengan data. | Cek spasi, huruf besar-kecil, dan ejaan status. |
| UNIQUE tetap terlihat dobel | Nama terlihat sama tetapi muncul dua kali. | Ada spasi tersembunyi atau karakter berbeda. | Bersihkan data dengan TRIM/CLEAN jika perlu. |
| TEXTSPLIT tidak memecah data | Hasil tetap satu sel. | Pemisah di rumus berbeda dari data asli. | Cek apakah pemisahnya koma, spasi, garis miring, atau tanda hubung. |
| Muncul error spill | Muncul #SPILL!. |
Area hasil rumus terhalang data lain. | Kosongkan area tempat hasil rumus akan keluar. |
| Angka terbaca teks | Perhitungan tidak berjalan normal. | Data hasil copy dari sistem/website. | Ubah ke angka dengan VALUE/NUMBERVALUE atau fitur Text to Columns. |
Kesalahan Umum yang Harus Dihindari
| Kesalahan | Akibatnya | Langkah yang Benar |
|---|---|---|
| Tidak punya tabel master | XLOOKUP sulit dipakai karena data acuan tidak rapi. | Buat Master_Siswa, Master_Produk, atau Master_Pelanggan. |
| Mengetik kategori/status manual terus-menerus | FILTER dan UNIQUE menghasilkan data pecah karena typo. | Gunakan dropdown untuk status, kelas, kategori, dan metode bayar. |
| Mengisi data di area hasil rumus spill | Rumus FILTER/UNIQUE/TEXTSPLIT bisa error. | Biarkan area hasil rumus kosong. |
| Terlalu cepat menghapus data yang terlihat duplikat | Data penting bisa hilang. | Backup file dulu dan cek duplikat dengan rumus sebelum hapus. |
| Tidak mengunci range penting | Rumus berubah saat disalin. | Gunakan tanda dolar seperti $A:$A atau jadikan data sebagai Excel Table. |
| Mencampur data mentah dan hasil rekap | File sulit diaudit. | Pisahkan sheet Data_Mentah, Master, Rekap, dan Laporan. |
| Menggunakan rumus modern tanpa cek versi Excel | File error saat dibuka di komputer lain. | Tambahkan catatan versi dan sediakan alternatif rumus lama jika perlu. |
Kesimpulan
XLOOKUP, FILTER, UNIQUE, dan TEXTSPLIT adalah rumus modern yang sangat membantu admin pemula. XLOOKUP berguna untuk mengambil data otomatis dari tabel master. FILTER berguna untuk menampilkan data sesuai kriteria. UNIQUE berguna untuk membuat daftar nilai unik. TEXTSPLIT berguna untuk memecah teks yang masih tergabung dalam satu sel.
Untuk pekerjaan admin sekolah, rumus ini bisa membantu mencari data siswa, menampilkan siswa aktif per kelas, dan membuat daftar kota atau kelas unik. Untuk admin stok, rumus ini bisa membantu mengambil nama produk dari kode, menampilkan stok menipis, dan membuat daftar kategori. Untuk admin UMKM, rumus ini bisa membantu mencocokkan invoice, rekap pelanggan, transaksi piutang, dan alamat pelanggan.
Kunci utamanya bukan hanya hafal rumus, tetapi merapikan data sumber. Buat tabel master, gunakan kode unik, pakai dropdown, hindari typo, dan pisahkan data mentah dari rekap. Dengan begitu, rumus modern bisa bekerja stabil dan pekerjaan admin menjadi lebih cepat, rapi, dan mudah diaudit.
FAQ
1. Apa fungsi XLOOKUP di Excel?
XLOOKUP digunakan untuk mencari nilai dalam tabel atau range, lalu mengembalikan data terkait dari baris yang sama. Contohnya mengambil nama produk berdasarkan kode produk.
2. Apa bedanya XLOOKUP dan VLOOKUP?
XLOOKUP lebih fleksibel karena bisa mencari ke kiri atau ke kanan, memiliki opsi jika data tidak ditemukan, dan lebih nyaman untuk tabel modern. VLOOKUP tetap bisa dipakai jika Excel belum mendukung XLOOKUP.
3. Apa fungsi FILTER?
FILTER digunakan untuk menampilkan data yang memenuhi kriteria tertentu, misalnya transaksi piutang, siswa kelas tertentu, atau barang dengan stok kurang dari batas minimum.
4. Apa fungsi UNIQUE?
UNIQUE digunakan untuk mengambil daftar nilai unik dari suatu range, misalnya daftar pelanggan, daftar kota, daftar kelas, atau daftar kategori produk.
5. Apa fungsi TEXTSPLIT?
TEXTSPLIT digunakan untuk memecah teks berdasarkan pemisah tertentu, misalnya memecah alamat “Jakarta/Kebayoran/12210” menjadi beberapa kolom.
6. Kenapa rumus FILTER atau UNIQUE menghasilkan #SPILL?
Error #SPILL biasanya terjadi karena area hasil rumus terhalang data lain. Kosongkan sel di bawah atau kanan rumus agar hasil bisa keluar.
7. Kenapa XLOOKUP menampilkan “Tidak ditemukan”?
Biasanya karena nilai yang dicari tidak ada di tabel master, ada typo, ada spasi tersembunyi, atau kode di transaksi berbeda dari kode di master.
8. Apakah semua Excel punya XLOOKUP, FILTER, UNIQUE, dan TEXTSPLIT?
Tidak semua. Rumus ini tersedia di Excel modern tertentu. Jika Excel Anda belum mendukungnya, gunakan alternatif seperti VLOOKUP, filter manual, Remove Duplicates, Text to Columns, atau Pivot Table.
9. Apakah rumus ini cocok untuk admin pemula?
Cocok, asalkan dimulai dari contoh sederhana. Mulailah dari XLOOKUP untuk mengambil data master, lalu lanjut FILTER, UNIQUE, dan TEXTSPLIT.
10. Apa yang harus dirapikan sebelum memakai rumus modern?
Rapikan tabel master, gunakan kode unik, hindari sel kosong yang tidak perlu, gunakan dropdown untuk status/kategori, dan pastikan angka tidak tersimpan sebagai teks.
Baca Juga
- Cara Membuat Pembukuan Keuangan di Excel untuk UMKM Pemula
- Template Excel untuk Menghitung HPP UMKM: Otomatis untuk Usaha Dagang & Produksi
- Rumus Terbilang di Excel: Cara Mengubah Angka Jadi Tulisan Rupiah
- Cara Buat Invoice di Excel dengan Rumus Otomatis untuk UMKM dan Freelancer
Rujukan resmi yang perlu dipantau: Panduan XLOOKUP Microsoft, Panduan FILTER Microsoft, Panduan UNIQUE Microsoft, dan Panduan TEXTSPLIT Microsoft.
Comments
Post a Comment