Catatan
Microsoft Access tidak mendukung pengimporan data Excel dengan label sensitivitas yang diterapkan. Sebagai solusinya, Anda dapat menghapus label sebelum mengimpor, lalu menerapkan kembali label setelah mengimpor. Untuk informasi selengkapnya, lihat Menerapkan label sensitivitas ke file dan email Anda di Office.
Artikel ini menunjukkan cara memindahkan data dari Excel ke Access dan mengonversi data Anda ke tabel relasional sehingga Anda dapat menggunakan Microsoft Excel dan Access secara bersamaan. Singkatnya, Access adalah yang terbaik untuk mengambil, menyimpan, mengkueri, dan berbagi data, dan Excel adalah yang terbaik untuk menghitung, menganalisis, dan memvisualisasikan data.
Dua artikel, Menggunakan Access atau Excel untuk mengelola data Anda dan 10 alasan teratas untuk menggunakan Access dengan Excel, membahas program mana yang paling cocok untuk tugas tertentu dan cara menggunakan Excel dan Access bersama-sama untuk menciptakan solusi praktis.
Saat Anda memindahkan data dari Excel ke Access, ada tiga langkah dasar untuk proses tersebut.
Catatan
Untuk informasi tentang pemodelan dan hubungan data di Access, lihat Dasar-dasar desain database.
Langkah 1: Mengimpor data dari Excel ke Access
Mengimpor data adalah operasi yang dapat berjalan jauh lebih lancar jika Anda meluangkan waktu untuk menyiapkan dan membersihkan data. Mengimpor data itu seperti pindah ke rumah baru. Jika Anda membersihkan dan mengatur harta benda Anda sebelum Anda pindah, menetap di rumah baru Anda jauh lebih mudah.
Bersihkan data Anda sebelum mengimpor
Sebelum mengimpor data ke Access, di Excel, sebaiknya bagi:
- Mengonversi sel yang berisi data non-atom (yaitu, beberapa nilai dalam satu sel) menjadi beberapa kolom. Misalnya, sel dalam kolom "Keterampilan" yang berisi beberapa nilai keterampilan, seperti "Pemrograman C#," "Pemrograman VBA", dan "Desain web" harus dipecah menjadi kolom terpisah yang masing-masing hanya berisi satu nilai keterampilan.
- Gunakan perintah TRIM untuk menghapus ruang sebelumnya, akhir, dan beberapa spasi yang disematkan.
- Hapus karakter non-cetak.
- Temukan dan perbaiki kesalahan ejaan dan tanda baca.
- Menghapus baris duplikat atau bidang duplikat.
- Pastikan bahwa kolom data tidak berisi format campuran, terutama angka yang diformat sebagai teks atau tanggal yang diformat sebagai angka.
Untuk informasi selengkapnya, lihat topik bantuan Excel berikut:
- Sepuluh cara teratas untuk membersihkan data Anda
- Memfilter untuk mendapatkan nilai unik atau menghapus nilai duplikat
- Mengonversi angka yang disimpan sebagai teks menjadi angka
- Mengonversi tanggal yang disimpan sebagai teks menjadi tanggal
Catatan
Jika kebutuhan pembersihan data Anda rumit, atau Anda tidak memiliki waktu atau sumber daya untuk mengotomatiskan proses sendiri, pertimbangkan untuk menggunakan vendor pihak ketiga. Untuk informasi selengkapnya, cari "perangkat lunak pembersih data" atau "kualitas data" oleh mesin pencari favorit Anda di browser Web.
Pilih tipe data terbaik saat Anda mengimpor
Selama operasi impor di Access, Anda ingin membuat pilihan yang baik sehingga menerima sedikit (jika ada) kesalahan konversi yang memerlukan intervensi manual. Tabel berikut ini merangkum cara format angka Excel dan tipe data Access dikonversi saat Anda mengimpor data dari Excel ke Access, dan menawarkan beberapa tip tentang tipe data terbaik yang dapat dipilih di Panduan Impor Lembar Bentang.
| Format angka Excel | Tipe data Access | Komentar | Praktik terbaik |
|---|---|---|---|
| Teks | Teks, Memo | Tipe data Teks Akses menyimpan data alfanumerik hingga 255 karakter. Tipe data Access Memo menyimpan data alfanumerik hingga 65.535 karakter. | Pilih Memo untuk menghindari pemotongan data apa pun. |
| Angka, persentase, pecahan, ilmiah | Angka | Access memiliki satu tipe data Angka yang bervariasi berdasarkan properti Ukuran Bidang (Byte, Bilangan Bulat, Bilangan Bulat Panjang, Tunggal, Ganda, Desimal). | Pilih Ganda untuk menghindari kesalahan konversi data. |
| Tanggal | Tanggal | Access dan Excel keduanya menggunakan nomor tanggal seri yang sama untuk menyimpan tanggal. Di Access, rentang tanggalnya lebih besar: dari -657.434 (1 Januari 100 M) hingga 2.958.465 (31 Desember 9999 M). Karena Access tidak mengenali sistem penanggalan 1904 (digunakan di Excel untuk Macintosh), Anda perlu mengonversi tanggal baik di Excel maupun Access untuk menghindari kebingungan. Untuk informasi selengkapnya, lihat Mengubah sistem tanggal, format, atau interpretasi tahun dua digit dan Mengimpor atau menautkan ke data di buku kerja Excel. |
Pilih Tanggal. |
| Waktu | Waktu | Access dan Excel menyimpan nilai waktu dengan menggunakan tipe data yang sama. | Pilih Waktu, yang biasanya merupakan default. |
| Mata uang, Akuntansi | Mata Uang | Di Access, tipe data Mata Uang menyimpan data sebagai angka 8 byte dengan presisi hingga empat tempat desimal, dan digunakan untuk menyimpan data keuangan dan mencegah pembulatan nilai. | Pilih Mata Uang, yang biasanya merupakan default. |
| Boolean | Ya/Tidak | Access menggunakan -1 untuk semua nilai Ya dan 0 untuk semua nilai Tidak, sementara Excel menggunakan 1 untuk semua nilai TRUE dan 0 untuk semua nilai FALSE. | Pilih Ya/Tidak, yang secara otomatis mengonversi nilai dasar. |
| Hyperlink | Hyperlink | Hyperlink di Excel dan Access berisi URL atau alamat Web yang dapat Anda klik dan ikuti. | Pilih Hyperlink, jika tidak, Access dapat menggunakan tipe data Teks secara default. |
Setelah data berada di Access, Anda dapat menghapus data Excel. Jangan lupa untuk mencadangkan buku kerja Excel asli terlebih dahulu sebelum menghapusnya.
Untuk informasi selengkapnya, lihat topik bantuan Access Mengimpor atau menautkan ke data di buku kerja Excel.
Tambahkan data secara otomatis dengan cara yang mudah
Masalah umum yang dimiliki pengguna Excel adalah menambahkan data dengan kolom yang sama ke dalam satu lembar kerja besar. Misalnya, Anda mungkin memiliki solusi pelacakan aset yang dimulai di Excel, tetapi sekarang telah berkembang untuk menyertakan file dari banyak kelompok kerja dan departemen. Data ini mungkin ada dalam lembar kerja dan buku kerja yang berbeda, atau dalam file teks yang merupakan umpan data dari sistem lain. Tidak ada perintah antarmuka pengguna atau cara mudah untuk menambahkan data serupa di Excel.
Solusi terbaik adalah dengan menggunakan Access, tempat Anda dapat dengan mudah mengimpor dan menambahkan data ke dalam satu tabel dengan menggunakan Panduan Impor Lembar Bentang. Selain itu, Anda dapat menambahkan banyak data ke dalam satu tabel. Anda dapat menyimpan operasi impor, menambahkannya sesuai jadwal tugas Microsoft Outlook, dan bahkan menggunakan makro untuk mengotomatiskan proses.
Langkah 2: Normalkan data menggunakan Panduan Penganalisis Tabel
Sekilas, melangkah melalui proses normalisasi data Anda mungkin tampak sebagai tugas yang menakutkan. Untungnya, menormalkan tabel di Access adalah proses yang jauh lebih mudah, berkat Panduan Penganalisis Tabel.
1. Seret kolom yang dipilih ke tabel baru dan buat hubungan secara otomatis
2. Gunakan perintah tombol untuk mengganti nama tabel, menambahkan kunci primer, menjadikan kolom yang sudah ada sebagai kunci primer, dan membatalkan tindakan terakhir
Anda dapat menggunakan panduan ini untuk melakukan hal berikut:
- Konversikan tabel menjadi sekumpulan tabel yang lebih kecil dan buat hubungan kunci utama dan asing secara otomatis di antara tabel.
- Tambahkan kunci primer ke bidang yang sudah ada yang berisi nilai unik, atau buat bidang ID baru yang menggunakan tipe data AutoNumber.
- Buat hubungan secara otomatis untuk menegakkan integritas referensial dengan pembaruan bertingkat. Penghapusan kaskade tidak ditambahkan secara otomatis untuk mencegah penghapusan data secara tidak sengaja, tetapi Anda dapat dengan mudah menambahkan penghapusan kaskade nanti.
- Cari tabel baru untuk data yang berlebihan atau duplikat (seperti pelanggan yang sama dengan dua nomor telepon yang berbeda) dan perbarui sesuai keinginan.
- Cadangkan tabel asli dan ganti namanya dengan menambahkan "_OLD" ke namanya. Kemudian, Anda membuat kueri yang merekonstruksi tabel asli, dengan nama tabel asli sehingga formulir atau laporan yang ada berdasarkan tabel asli akan berfungsi dengan struktur tabel baru.
Untuk informasi selengkapnya, lihat Menormalkan data Anda menggunakan Penganalisis Tabel.
Langkah 3: Sambungkan ke Access data dari Excel
Setelah data dinormalisasi di Access dan kueri atau tabel dibuat yang merekonstruksi data asli, masalah sederhana untuk menyambungkan ke data Access dari Excel. Data Anda sekarang berada di Access sebagai sumber data eksternal, sehingga dapat disambungkan ke buku kerja melalui koneksi data, yang merupakan wadah informasi yang digunakan untuk menemukan, masuk, dan mengakses sumber data eksternal. Informasi koneksi disimpan dalam buku kerja dan juga dapat disimpan dalam file koneksi, seperti file Koneksi Data Office (ODC) (ekstensi nama file .odc) atau file Nama Sumber Data (ekstensi .dsn). Setelah tersambung ke data eksternal, Anda juga dapat me-refresh (atau memperbarui) buku kerja Excel secara otomatis dari Access setiap kali data diperbarui di Access.
Untuk informasi selengkapnya, lihat Mengimpor data dari sumber data eksternal (Power Query).
Masukkan data Anda ke Access
Bagian ini memandu Anda melalui fase normalisasi data berikut: Memecah nilai di kolom Tenaga Penjualan dan Alamat menjadi bagian-bagian yang paling atomik, memisahkan subjek terkait ke dalam tabelnya sendiri, menyalin dan menempelkan tabel tersebut dari Excel ke Access, membuat hubungan kunci antara tabel Access yang baru dibuat, dan membuat dan menjalankan kueri sederhana di Access untuk mengembalikan informasi.
Contoh data dalam bentuk yang tidak dinormalisasi
Lembar kerja berikut berisi nilai non-atom dalam kolom Tenaga Penjualan dan kolom Alamat. Kedua kolom harus dibagi menjadi dua kolom terpisah atau lebih. Lembar kerja ini juga berisi informasi tentang tenaga penjualan, produk, pelanggan, dan pesanan. Informasi ini juga harus dibagi lebih lanjut, berdasarkan subjek, ke dalam tabel terpisah.
| Tenaga penjualan | ID Pesanan | Tanggal Pemesanan | Product ID | Jumlah | Harga | Nama Pelanggan | Alamat | Telepon |
|---|---|---|---|---|---|---|---|---|
| Li, Yale | 2349 | 3/4/09 | C-789 | 3 | $7.00 | Fourth Coffee | 7007 Cornell St Redmond, WA 98199 | 425-555-0201 |
| Li, Yale | 2349 | 3/4/09 | C-795 | 6 | $ 9.75 | Fourth Coffee | 7007 Cornell St Redmond, WA 98199 | 425-555-0201 |
| Adams, Ellen | 2350 | 3/4/09 | A-2275 | 2 | $ 16.75 | Pekerjaan Petualangan | 1025 Columbia Circle Kirkland, WA 98234 | 425-555-0185 |
| Adams, Ellen | 2350 | 3/4/09 | F-198 | 6 | $5.25 | Pekerjaan Petualangan | 1025 Columbia Circle Kirkland, WA 98234 | 425-555-0185 |
| Adams, Ellen | 2350 | 3/4/09 | B-205 | 1 | $ 4.50 | Pekerjaan Petualangan | 1025 Columbia Circle Kirkland, WA 98234 | 425-555-0185 |
| Hance, Jim | 2351 | 3/4/09 | C-795 | 6 | $ 9.75 | Contoso, Ltd. | 2302 Harvard Ave Bellevue, WA 98227 | 425-555-0222 |
| Hance, Jim | 2352 | 3/5/09 | A-2275 | 2 | $ 16.75 | Pekerjaan Petualangan | 1025 Columbia Circle Kirkland, WA 98234 | 425-555-0185 |
| Hance, Jim | 2352 | 3/5/09 | D-4420 | 3 | $ 7.25 | Pekerjaan Petualangan | 1025 Columbia Circle Kirkland, WA 98234 | 425-555-0185 |
| Koch, Reed | 2353 | 3/7/09 | A-2275 | 6 | $ 16.75 | Fourth Coffee | 7007 Cornell St Redmond, WA 98199 | 425-555-0201 |
| Koch, Reed | 2353 | 3/7/09 | C-789 | 5 | $7.00 | Fourth Coffee | 7007 Cornell St Redmond, WA 98199 | 425-555-0201 |
Informasi di bagian terkecilnya: data atom
Bekerja dengan data dalam contoh ini, Anda dapat menggunakan perintah Teks ke Kolom di Excel untuk memisahkan bagian "atom" dari sel (seperti alamat jalan, kota, negara bagian, dan kode pos) ke dalam kolom terpisah.
Tabel berikut ini memperlihatkan kolom baru dalam lembar kerja yang sama setelah dipisahkan untuk membuat semua nilai menjadi atomik. Perhatikan bahwa informasi dalam kolom Tenaga Penjualan telah dibagi menjadi kolom Nama Belakang dan Nama Depan dan informasi dalam kolom Alamat telah dibagi menjadi kolom Alamat Jalan, Kota, Negara Bagian, dan Kode POS. Data ini dalam "bentuk normal pertama."
| Nama Belakang | Nama Depan | Nama jalan | Kota | Negara Bagian | Kode Pos |
|---|---|---|---|---|---|
| Li | Yale | 2302 Harvard Ave | Bellevue | WA | 98227 |
| Adams | Ellen | Lingkaran Columbia 1025 | Kediri | WA | 98234 |
| Hance | Jim | 2302 Harvard Ave | Bellevue | WA | 98227 |
| Koch | Buluh | 7007 Cornell St Redmond | Redmond | WA | 98199 |
Membagi data menjadi subjek terorganisir di Excel
Beberapa tabel contoh data berikut ini memperlihatkan informasi yang sama dari lembar kerja Excel setelah dibagi menjadi tabel untuk tenaga penjualan, produk, pelanggan, dan pesanan. Desain meja belum selesai, tetapi berada di jalur yang benar.
Tabel Tenaga Penjualan hanya berisi informasi tentang tenaga penjualan. Perhatikan bahwa setiap catatan memiliki ID (ID SalesPerson) yang unik. Nilai ID SalesPerson akan digunakan dalam tabel Pesanan untuk menghubungkan pesanan ke tenaga penjualan.
| Tenaga penjual | ||
|---|---|---|
| ID Tenaga Penjualan | Nama Belakang | Nama Depan |
| 101 | Li | Yale |
| 103 | Adams | Ellen |
| 105 | Hance | Jim |
| 107 | Koch | Buluh |
Tabel Produk hanya berisi informasi tentang produk. Perhatikan bahwa setiap catatan memiliki ID (ID Produk) yang unik. Nilai ID Produk akan digunakan untuk menghubungkan informasi produk ke tabel Detail Pesanan.
| Produk | |
|---|---|
| Product ID | Harga |
| A-2275 | 16.75 |
| B-205 | 4.50 |
| C-789 | 7.00 |
| C-795 | 9.75 |
| D-4420 | 7.25 |
| F-198 | 5.25 |
Tabel Pelanggan hanya berisi informasi tentang pelanggan. Perhatikan bahwa setiap catatan memiliki ID (ID Pelanggan) yang unik. Nilai ID Pelanggan akan digunakan untuk menghubungkan informasi pelanggan ke tabel Pesanan.
| Pelanggan | ||||||
|---|---|---|---|---|---|---|
| ID Pelanggan | Nama | Nama jalan | Kota | Negara Bagian | Kode Pos | Telepon |
| 1001 | Contoso, Ltd. | 2302 Harvard Ave | Bellevue | WA | 98227 | 425-555-0222 |
| 1003 | Pekerjaan Petualangan | Lingkaran Columbia 1025 | Kediri | WA | 98234 | 425-555-0185 |
| 1005 | Fourth Coffee | 7007 Cornell St | Redmond | WA | 98199 | 425-555-0201 |
Tabel Pesanan berisi informasi tentang pesanan, tenaga penjualan, pelanggan, dan produk. Perhatikan bahwa setiap catatan memiliki ID (ID Pesanan) yang unik. Beberapa informasi dalam tabel ini perlu dibagi menjadi tabel tambahan yang berisi detail pesanan sehingga tabel Pesanan hanya berisi empat kolom — ID pesanan unik, tanggal pesanan, ID tenaga penjualan, dan ID pelanggan. Tabel yang ditampilkan di sini belum dibagi menjadi tabel Detail Pesanan.
| Pesanan | |||||
|---|---|---|---|---|---|
| ID Pesanan | Tanggal Pemesanan | SalesPerson ID | ID pelanggan | Product ID | Jumlah |
| 2349 | 3/4/09 | 101 | 1005 | C-789 | 3 |
| 2349 | 3/4/09 | 101 | 1005 | C-795 | 6 |
| 2350 | 3/4/09 | 103 | 1003 | A-2275 | 2 |
| 2350 | 3/4/09 | 103 | 1003 | F-198 | 6 |
| 2350 | 3/4/09 | 103 | 1003 | B-205 | 1 |
| 2351 | 3/4/09 | 105 | 1001 | C-795 | 6 |
| 2352 | 3/5/09 | 105 | 1003 | A-2275 | 2 |
| 2352 | 3/5/09 | 105 | 1003 | D-4420 | 3 |
| 2353 | 3/7/09 | 107 | 1005 | A-2275 | 6 |
| 2353 | 3/7/09 | 107 | 1005 | C-789 | 5 |
Detail pesanan, seperti ID produk dan kuantitas, dipindahkan dari tabel Pesanan dan disimpan dalam tabel bernama Detail Pesanan. Perlu diingat bahwa ada 9 pesanan, jadi masuk akal jika ada 9 catatan dalam tabel ini. Perhatikan bahwa tabel Pesanan memiliki ID unik (ID Pesanan), yang akan dirujuk dari tabel Detail Pesanan.
Desain akhir tabel Pesanan akan terlihat seperti berikut:
| Pesanan | |||
|---|---|---|---|
| ID Pesanan | Tanggal Pemesanan | SalesPerson ID | ID pelanggan |
| 2349 | 3/4/09 | 101 | 1005 |
| 2350 | 3/4/09 | 103 | 1003 |
| 2351 | 3/4/09 | 105 | 1001 |
| 2352 | 3/5/09 | 105 | 1003 |
| 2353 | 3/7/09 | 107 | 1005 |
Tabel Detail Urutan tidak berisi kolom yang memerlukan nilai unik (yaitu, tidak ada kunci utama), sehingga tidak apa-apa jika salah satu atau semua kolom berisi data "berlebihan". Namun, tidak ada dua catatan dalam tabel ini yang harus sepenuhnya identik (aturan ini berlaku untuk tabel apa pun dalam database). Dalam tabel ini, harus ada 17 catatan — masing-masing sesuai dengan produk dalam urutan individu. Misalnya, dalam urutan 2349, tiga produk C-789 terdiri dari salah satu dari dua bagian dari seluruh pesanan.
Oleh karena itu, tabel Detail Pesanan akan terlihat seperti berikut:
| Detail Pemesanan: | ||
|---|---|---|
| ID Pesanan | Product ID | Jumlah |
| 2349 | C-789 | 3 |
| 2349 | C-795 | 6 |
| 2350 | A-2275 | 2 |
| 2350 | F-198 | 6 |
| 2350 | B-205 | 1 |
| 2351 | C-795 | 6 |
| 2352 | A-2275 | 2 |
| 2352 | D-4420 | 3 |
| 2353 | A-2275 | 6 |
| 2353 | C-789 | 5 |
Menyalin dan menempelkan data dari Excel ke Access
Setelah informasi tentang tenaga penjualan, pelanggan, produk, pesanan, dan detail pesanan dipecah menjadi subjek terpisah di Excel, Anda dapat menyalin data tersebut langsung ke Access, di mana data akan menjadi tabel.
Membuat hubungan antara tabel Access dan menjalankan kueri
Setelah memindahkan data ke Access, Anda dapat membuat hubungan antar tabel, lalu membuat kueri untuk mengembalikan informasi tentang berbagai subjek. Misalnya, Anda dapat membuat kueri yang mengembalikan ID Pesanan dan nama tenaga penjualan untuk pesanan yang dimasukkan antara 3/05/09 dan 3/08/09.
Selain itu, Anda dapat membuat formulir dan laporan untuk mempermudah entri data dan analisis penjualan.
Perlu bantuan lainnya?
Anda selalu dapat bertanya kepada ahli di Komunitas Teknologi Excel atau mendapatkan dukungan di Komunitas.