Lompati ke konten utama

Memindahkan data dari Excel ke Access

Artikel ini memperlihatkan kepada Anda cara memindahkan data dari Excel untuk mengakses dan mengonversi data Anda ke tabel relasional sehingga Anda bisa menggunakan Microsoft Excel dan Access bersama-sama. Untuk meringkas, Access adalah yang terbaik untuk merekam, menyimpan, membuat kueri, 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, mendiskusikan program mana yang paling cocok untuk tugas tertentu dan cara menggunakan Excel dan akses bersama-sama untuk membuat solusi praktis.

Saat Anda memindahkan data dari Excel ke Access, ada tiga langkah dasar untuk prosesnya.

tiga langkah dasar

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 bisa jauh lebih lancar jika Anda meluangkan waktu untuk mempersiapkan dan membersihkan data Anda. Mengimpor data seperti berpindah ke rumah baru. Jika Anda membersihkan dan menata harta benda Anda sebelum berpindah, menetap di rumah baru Anda jauh lebih mudah.

Bersihkan data Anda sebelum Anda mengimpor

Sebelum Anda mengimpor data ke Access, di Excel, ada baiknya:

  • Mengonversi sel yang berisi data non-atom (yaitu, beberapa nilai dalam satu sel) ke beberapa kolom. Misalnya, sel dalam kolom "keterampilan" yang berisi beberapa nilai keterampilan, seperti "pemrograman C#," "pemrograman VBA," dan "desain web" harus dipisahkan untuk memisahkan kolom yang masing-masing berisi satu nilai keahlian.

  • Gunakan perintah TRIM untuk menghapus spasi utama, trailing, dan beberapa.

  • Menghapus karakter non-cetak.

  • Menemukan dan memperbaiki kesalahan ejaan dan tanda baca.

  • Hapus duplikat baris 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 ini:

Catatan: Jika kebutuhan pembersihan data Anda rumit, atau Anda tidak memiliki waktu atau sumber daya untuk mengotomatisasi proses sendiri, Anda mungkin mempertimbangkan untuk menggunakan vendor pihak ketiga. Untuk informasi selengkapnya, Cari "perangkat lunak pembersihan data" atau "kualitas data" oleh mesin pencarian favorit di browser web Anda.

Pilih tipe data terbaik saat Anda mengimpor

Selama operasi impor di Access, Anda ingin membuat pilihan yang baik sehingga Anda menerima sedikit (jika ada) kesalahan konversi yang memerlukan intervensi manual. Tabel berikut ini meringkas bagaimana format angka Excel dan tipe data Access dikonversi ketika Anda mengimpor data dari Excel ke Access, dan menawarkan beberapa tips pada tipe data terbaik untuk dipilih dalam panduan impor lembar bentang.

Format angka Excel

Tipe data Access

Komentar

Praktik terbaik

Teks

Teks, memo

Tipe data teks Access menyimpan data alfanumerik hingga 255 karakter. Tipe data memo Access menyimpan data alfanumerik hingga 65.535 karakter.

Pilih memo untuk menghindari memotong data apa pun.

Angka, persentase, pecahan, ilmiah

Angka

Access memiliki satu tipe data angka yang bervariasi tergantung pada 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 seri tanggal yang sama untuk menyimpan tanggal. Di Access, rentang tanggal lebih besar: dari 657.434 (1 Januari 100 m) ke 2.958.465 (31 Desember 9999 m).

Karena Access tidak mengenali sistem tanggal 1904 (digunakan di Excel untuk Macintosh), Anda perlu mengonversi tanggal di Excel atau Access untuk menghindari kebingungan.

Untuk informasi lebih lanjut, lihat mengubah interpretasi sistem tanggal, format, atau dua digit tahun dan mengimpor atau menautkan ke data dalam buku kerja Excel.

Pilih tanggal.

Waktu

Waktu

Access dan Excel menyimpan nilai waktu penyimpanan dengan menggunakan tipe data yang sama.

Pilih waktu, yang biasanya default.

Mata uang, akuntansi

Mata Uang

Di Access, tipe data mata uang menyimpan data sebagai angka 8 bita 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, sedangkan Excel menggunakan 1 untuk semua nilai TRUE dan 0 untuk semua nilai FALSE.

Pilih ya/tidak, yang secara otomatis akan mengonversi nilai yang mendasari.

Hyperlink

Hyperlink

Hyperlink di Excel dan Access berisi URL atau alamat web yang bisa Anda klik dan ikuti.

Pilih hyperlink, jika tidak, Access mungkin menggunakan tipe data teks secara default.

Setelah data di Access, Anda bisa menghapus data Excel. Jangan lupa untuk mencadangkan buku kerja Excel asli terlebih dahulu sebelum menghapusnya.

Untuk informasi selengkapnya, lihat mengimpor atau menautkan topik bantuan Access ke data dalam buku kerja Excel.

Menambahkan data secara otomatis dengan cara 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 namun kini telah berkembang untuk menyertakan file dari berbagai grup kerja dan Departemen. Data ini mungkin ada di 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 yang sama di Excel.

Solusi terbaik adalah menggunakan Access, di mana Anda bisa dengan mudah mengimpor dan menambahkan data ke dalam satu tabel dengan menggunakan panduan impor lembar bentang. Selain itu, Anda bisa menambahkan banyak data ke dalam satu tabel. Anda dapat menyimpan operasi impor, menambahkannya sebagai tugas terjadwal Microsoft Outlook, dan bahkan menggunakan makro untuk mengotomatisasi proses.

Langkah 2: menormalkan data menggunakan panduan Penganalisis tabel

Sekilas, proses normalisasi data Anda mungkin terlihat sebagai tugas yang menakutkan. Untungnya, normalisasi tabel di Access adalah proses yang jauh lebih mudah, berkat panduan Penganalisis tabel.

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 utama, membuat kolom yang sudah ada menjadi kunci utama, dan membatalkan tindakan terakhir

Anda bisa menggunakan panduan ini untuk melakukan hal berikut:

  • Mengonversi tabel menjadi sekumpulan tabel yang lebih kecil dan secara otomatis membuat hubungan kunci utama dan asing antar tabel.

  • Tambahkan kunci utama ke bidang yang sudah ada yang berisi nilai unik, atau buat bidang ID baru yang menggunakan tipe data AutoNumber.

  • Buat hubungan secara otomatis untuk menerapkan integritas referensial dengan pembaruan bertingkat. Penghapusan bertingkat tidak secara otomatis ditambahkan untuk mencegah penghapusan data secara tidak sengaja, tapi Anda bisa dengan mudah menambahkan penghapusan bertingkat nanti.

  • Cari tabel baru untuk data yang berlebihan atau duplikat (seperti Pelanggan yang sama dengan dua nomor telepon yang berbeda) dan perbarui seperti yang diinginkan.

  • Cadangkan tabel asli dan ganti namanya dengan menambahkan "_OLD" ke namanya. Lalu, Anda membuat kueri yang menyusun ulang tabel asli, dengan nama tabel asli sehingga setiap formulir atau laporan yang sudah ada yang didasarkan pada tabel asli akan berfungsi dengan struktur tabel baru.

Untuk informasi selengkapnya, lihat menormalkan data Anda menggunakan Penganalisis tabel.

Langkah 3: menyambungkan ke mengakses data dari Excel

Setelah data telah dinormalkan di Access dan kueri atau tabel telah dibuat yang merekonstruksi data asli, ini adalah masalah sederhana dalam menghubungkan ke data Access dari Excel. Data Anda sekarang berada di Access sebagai sumber data eksternal, dan sehingga dapat tersambung ke buku kerja melalui koneksi data, yang merupakan wadah informasi yang digunakan untuk menemukan, masuk ke, dan mengakses sumber data eksternal. Informasi koneksi disimpan dalam buku kerja dan juga dapat disimpan dalam file koneksi, seperti file koneksi data Office (ODC) file (. ODC ekstensi nama file) atau file nama sumber data (. ekstensi DSN). Setelah Anda tersambung ke data eksternal, Anda juga bisa secara otomatis melakukan refresh (atau memperbarui) buku kerja Excel dari Access setiap kali data diperbarui di Access.

Untuk informasi selengkapnya, lihat mengimpor data dari sumber data eksternal (Power query).

Mengakses data Anda

Bagian ini memandu Anda melewati fase-fase berikut normalisasi data Anda: memecah nilai dalam kolom tenaga penjual dan alamat ke dalam bagian paling atomnya, memisahkan subjek terkait ke dalam tabel mereka sendiri, menyalin dan menempelkan tabel tersebut dari Excel ke dalam Access, membuat hubungan utama antara tabel akses yang baru dibuat, dan membuat dan menjalankan kueri sederhana di Access untuk mengembalikan informasi.

Contoh data dalam formulir yang tidak dinormalkan

Lembar kerja berikut ini berisi nilai non-atom di kolom tenaga penjual dan kolom alamat. Kedua kolom harus dibagi menjadi dua atau beberapa kolom terpisah. Lembar kerja ini juga berisi informasi tentang tenaga penjual, produk, pelanggan, dan pesanan. Informasi ini juga harus dibagi lagi, menurut subjek, ke dalam tabel terpisah.

Tenaga penjual

ID Pesanan

Tanggal Pemesanan

Product ID

Qty

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

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Adams, Ellen

2350

3/4/09

F-198

6

$5,25

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Adams, Ellen

2350

3/4/09

B-205

1

$4,50

Adventure Works

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

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Hance, Jim

2352

3/5/09

D-4420

3

$7,25

Adventure Works

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 terkecil: data atom

Bekerja dengan data dalam contoh ini, Anda bisa menggunakan perintah teks ke kolom di Excel untuk memisahkan bagian "Atomic" dari sebuah sel (seperti alamat jalan, kota, negara bagian, dan kode pos) ke dalam kolom diskrit.

Tabel berikut ini memperlihatkan kolom baru dalam lembar kerja yang sama setelah dibagi menjadi nilai atom. Perhatikan bahwa informasi dalam kolom tenaga penjual telah dipecah menjadi nama belakang dan kolom nama depan dan bahwa informasi dalam kolom alamat telah dipisahkan ke dalam kolom alamat, kota, negara bagian, dan kode pos. Data ini berada dalam "formulir normal pertama."

Nama Belakang

Nama Depan

 

Alamat Jalan

Kota

Negara Bagian

Kode pos

Li

Iale

2302 Harvard Ave

Bellevue

WA

98227

Adams

En's

Lingkaran 1025 Columbia

Kediri

WA

98234

Hance

Jim

2302 Harvard Ave

Bellevue

WA

98227

Ku

Bul

7007 Cornell St Redmond

Redmond

WA

98199

Melanggar data keluar ke dalam subjek yang terorganisir di Excel

Beberapa tabel data contoh yang mengikuti Perlihatkan informasi yang sama dari lembar kerja Excel setelah telah dipecah menjadi tabel untuk tenaga penjual, produk, pelanggan, dan pesanan. Desain tabel tidak final, tetapi berada di jalur yang tepat.

Tabel tenaga penjual hanya berisi informasi tentang personel penjualan. Perhatikan bahwa setiap rekaman memiliki ID unik (ID tenaga penjual). Nilai ID tenaga penjual akan digunakan dalam tabel pesanan untuk menyambungkan pesanan ke tenaga penjualan.

Tenaga Penjual

ID tenaga penjual

Nama Belakang

Nama Depan

101

Li

Iale

103

Adams

En's

105

Hance

Jim

107

Ku

Bul

Tabel Products hanya berisi informasi tentang produk. Perhatikan bahwa setiap rekaman memiliki ID unik (ID Produk). Nilai ID Produk akan digunakan untuk menyambungkan informasi produk ke tabel detail pesanan.

Produknya

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 rekaman memiliki ID unik (ID pelanggan). Nilai ID pelanggan akan digunakan untuk menyambungkan informasi pelanggan ke tabel pesanan.

Pelanggan

ID Pelanggan

Nama

Alamat Jalan

Kota

Negara Bagian

Kode pos

Telepon

1001

Contoso, Ltd.

2302 Harvard Ave

Bellevue

WA

98227

425-555-0222

1003

Adventure Works

Lingkaran 1025 Columbia

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 penjual, pelanggan, dan produk. Perhatikan bahwa setiap rekaman memiliki ID unik (ID pesanan). Beberapa informasi dalam tabel ini harus dibagi menjadi tabel tambahan yang berisi detail pesanan sehingga tabel pesanan hanya berisi empat kolom — ID pesanan unik, tanggal pesanan, ID Penjual, dan ID pelanggan. Tabel yang diperlihatkan di sini belum dibagi ke dalam tabel detail pesanan.

Pesanan

ID Pesanan

Tanggal Pemesanan

ID tenaga penjual

ID pelanggan

Product ID

Qty

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. Ingatlah bahwa ada 9 pesanan, jadi masuk akal bahwa 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 Orders akan terlihat seperti berikut ini:

Pesanan

ID Pesanan

Tanggal Pemesanan

ID tenaga penjual

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 pesanan tidak berisi kolom yang memerlukan nilai unik (yaitu, tidak ada kunci utama), jadi tidak apa-apa untuk setiap atau semua kolom untuk memuat data "berlebihan". Namun, tidak ada dua rekaman dalam tabel ini yang harus benar-benar identik (aturan ini berlaku untuk setiap tabel dalam database). Dalam tabel ini, harus ada 17 Catatan — masing-masing terkait dengan produk dalam urutan individu. Misalnya, di 2349, tiga produk C-789 terdiri dari dua bagian dari seluruh pesanan.

Tabel detail pesanan seharusnya terlihat seperti berikut ini:

Detail pesanan

ID Pesanan

Product ID

Qty

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 penjual, pelanggan, produk, pesanan, dan detail pesanan telah dipecah menjadi subjek terpisah di Excel, Anda bisa menyalin data tersebut langsung ke Access, yang akan menjadi tabel.

Membuat hubungan antara tabel Access dan menjalankan kueri

Setelah memindahkan data ke Access, Anda dapat membuat hubungan di antara tabel lalu membuat kueri untuk mengembalikan informasi tentang berbagai subjek. Misalnya, Anda bisa membuat kueri yang mengembalikan ID pesanan dan nama Penjual untuk pesanan yang dimasukkan antara 3/05/09 dan 3/08/09.

Selain itu, Anda dapat membuat formulir dan laporan untuk membuat analisis data dan penjualan lebih mudah.

Perlu bantuan lainnya?

Anda selalu dapat bertanya kepada pakar di Komunitas Teknologi Excel, mendapatkan dukungan di Komunitas Jawaban, atau menyarankan fitur maupun fitur baru di Suara Pengguna Excel.

Kembangkan keterampilan Office Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung ke Office Insiders

Apakah informasi ini bermanfaat?

Terima kasih atas umpan balik Anda!

Terima kasih atas umpan balik Anda! Sepertinya menghubungkan Anda ke salah satu agen dukungan Office kami akan sangat membantu.

×