Bagaimana cara menanyakan spreadsheet excel di sql?

Rentang Excel dapat berfungsi seperti tabel relasional. Anda dapat bergabung dengan mereka dengan bidang umum. Query mereka dengan SQL. Dan gunakan kueri di PivotTable. Begini caranya

Oleh

27080

Facebook

Twitter

Pinterest

Linkedin

Anda dapat mengunduh file basis data Marty di sini

Oleh penulis tamu. Marty Ryerson

Saya bekerja untuk perusahaan manufaktur dengan pabrik di lima negara bagian. Ketika saya mulai bekerja di sana, saya menggunakan Crystal Reports untuk menjawab pertanyaan orang tentang data mereka

Tetapi laporan Crystal butuh waktu lama untuk dibuat. Dan sekitar 95% dari waktu, setelah saya menulis laporan Crystal, orang yang telah saya buat akan meminta untuk melihatnya di Excel

Dengan lebih banyak pekerjaan, saya menemukan cara untuk mengekspor laporan dari Crystal ke Excel tanpa menghabiskan banyak waktu. Namun masih membutuhkan banyak waktu untuk membuat atau mengubah laporan baru

Beberapa tahun lalu, saya menemukan Microsoft Query, yang disertakan dengan Excel. Meskipun program ini kurang canggih dibandingkan program Office lainnya, program ini jelas dapat memberi pengguna Excel kekuatan yang signifikan dalam bekerja dengan data eksternal. Namun sayangnya, saya hanya dapat menemukan sedikit sekali dokumentasi tentang alat tersebut

Akhirnya, saya mengetahui bahwa Timothy Zapawa telah menulis tentang MS Query secara ekstensif dalam bukunya tahun 2005, Excel Advanced Report Development. Dengan informasinya, saya akhirnya bisa menggunakan MS Query di tempat kerja

MS Query memberi pengguna Excel kemampuan untuk mengakses database pihak ke-3, file teks, dan buku kerja Excel sebagai sumber data relasional. Dengan file teks, Anda dapat menempatkan semuanya dalam satu folder untuk membentuk database. Dengan Excel, Anda menentukan beberapa rentang bernama dalam satu buku kerja, lalu menggunakan rentang tersebut sebagai tabel database

MS Query tidak memberi Anda banyak fitur bawaan dari program kueri database "nyata", seperti Microsoft Query Analyzer atau TOAD dari Quest. Tapi Anda pasti bisa menggabungkan dua atau lebih tabel dengan kolom umum mereka. Anda dapat menggunakan kueri SQL untuk mengakses tabel ini. Dan Anda bisa mengirimkan hasil kueri SQL ke lembar kerja atau mengaksesnya dengan PivotTable

Jika Anda tahu SQL, Anda dapat mengiris dan memotong semua yang Anda inginkan. Jika Anda tidak tahu SQL, itu jauh lebih mudah dipelajari daripada VBA

Dalam artikel ini, saya akan menunjukkan kepada Anda cara menentukan tiga rentang dalam buku kerja Excel sebagai tabel relasional, lalu menampilkan kueri terhadap tabel ini dalam lembar kerja. Saya juga akan menjelaskan cara mengakses tabel menggunakan PivotTable

Contoh Database

Mini-database yang saya buat memiliki tiga rentang yang diatur sebagai tabel. Setiap tabel memiliki lebih banyak baris daripada yang digambarkan di bawah ini. Saya telah menyertakan gambar di sini untuk mengilustrasikan data dan cara mengaturnya. Tabelnya adalah

1. Tabel pelanggan bernama PELANGGAN

2. Tabel pesanan bernama ORD

3. Tabel perwakilan penjualan bernama SREP

Perhatikan bahwa seperti tabel relasional standar, ini memiliki bidang tertentu yang sama

Perhatikan juga bagaimana tabel kedua diformat. Format ini adalah beberapa di antara sedikit yang akan dikenali oleh MS Query. Untuk menetapkan format ini, pilih kolom B dan C lalu tetapkan format pertama yang tercantum dalam Format, Sel, Angka, Tanggal. Kemudian pilih kolom F dan tetapkan format pertama yang tercantum dalam Format, Sel, Angka, Mata Uang

Saat menggunakan Excel sebagai sumber data, setiap tabel harus berupa rentang bernama, karena saat MS Query menggunakan buku kerja sebagai sumber data, MS Query hanya akan mengenali rentang bernama sebagai tabel. Saya biasanya menempatkan rentang pada lembar terpisah, tetapi itu tidak perlu

Setelah Anda menyiapkan tabel ini, simpan dan tutup buku kerja. Buku kerja harus ditutup saat diakses oleh MS Query

Buat Koneksi

Buka buku kerja lain tempat Anda akan membuat laporan Excel. Pilih Data, Impor Data Eksternal, Kueri Database Baru, yang meluncurkan kotak dialog Pilih Sumber Data. [Di Excel modern, di grup Data Pita, Dapatkan & Transformasi Data Anda, pilih Dapatkan Data, Dari Sumber Lain, Dari Microsoft Query. ]

(Jika MS Query tidak diinstal, sebuah pesan akan muncul menanyakan apakah Anda ingin menginstalnya. Untuk melakukannya, letakkan disk instalasi Anda di drive yang sesuai dan ikuti petunjuk di layar. )

The first time you access a database, including a workbook database, you’ll need to create a new Data Source. To do so, select the line, and then click OK.

Di kotak edit pertama dari dialog Buat Sumber Data Baru, beri nama sumber data Anda yang akan mengingatkan Anda apa yang terhubung dengannya. Ini adalah nama yang akan Anda pilih dari daftar saat membuat kueri baru nanti

Item tersebut meminta Anda untuk memilih jenis driver. Karena Excel adalah sumber data untuk latihan ini, pilih driver Excel yang ditampilkan dari daftar drop-down

Pilih tombol Hubungkan dan pilih versi Excel yang Anda gunakan. Perhatikan bahwa meskipun Anda menggunakan Excel 2003, versi terbaru Excel yang terdaftar adalah Excel 97-2000. [Driver terbaru adalah Versi 12. 0. ]

Pilih tombol Select Workbook, luncurkan dialog Select Workbook

Gunakan dialog ini untuk menavigasi ke buku kerja yang akan berfungsi sebagai sumber data Anda. Di sini, OEDATA. xls berisi Data Entri Pesanan saya. Pilih buku kerja dari daftar

Pilih OK untuk menerima pilihan Nama Database Anda. Dalam dialog ODBC Microsoft Excel Setup, pilih OK untuk kembali ke dialog Buat Sumber Data Baru. Dialog ini sekarang memperlihatkan jalur ke buku kerja Excel Anda yang berfungsi sebagai database Anda

Pilih OK untuk kembali ke dialog Pilih Sumber Data. Perhatikan bahwa Sumber Data yang baru saja Anda buat sudah dipilih dalam daftar

Pastikan kotak centang di bagian bawah dialog, "Gunakan Panduan Kueri untuk membuat/mengedit kueri," TIDAK dicentang. Wizard Kueri dapat membantu jika Anda melakukan kueri yang sangat sederhana, tetapi saya ingin menunjukkan kepada Anda fitur program yang lebih hebat. Anda dapat bereksperimen dengan Wizard Kueri nanti, jika Anda mau

Sekarang setelah Anda menetapkan buku kerja Excel sebagai database relasional, Anda bisa menggunakannya dalam kueri

Buat Kueri

Dialog Pilih Sumber Data sekarang menyertakan sumber data (MSQuery–Excel) yang telah kami tentukan untuk OEDATA. buku kerja xls. Pilih OK untuk menggunakan sumber data ini. Sumber data ini akan muncul setiap kali Anda mengakses dialog Pilih Sumber Data

Setelah Anda memilih OK, Excel menampilkan jendela aplikasi Microsoft Query layar penuh dan dialog Tambahkan Tabel. Anda akan menggunakan alat ini untuk menentukan data apa yang ingin Anda kembalikan, baik dengan menunjuk dan mengklik, atau dengan menempelkan pernyataan SQL ke dalam jendela SQL. Untuk contoh ini, kami akan menggunakan metode tunjuk dan klik

Dalam dialog Tambahkan Tabel, klik dua kali pada setiap tabel yang ingin Anda tambahkan. Perhatikan bahwa semua rentang bernama muncul di sini

Untuk contoh ini, mari tambahkan semua tabel. Untuk melakukannya, pilih setiap tabel secara bergiliran, lalu pilih Tambahkan atau klik dua kali. Melakukannya menampilkannya di MS Query, seperti yang ditunjukkan di sini. Setelah Anda menambahkan setiap tabel, tutup dialog Tambahkan Tabel

Panel abu-abu di dekat bagian atas gambar ini disebut panel Tabel. Area putih di bagian bawah disebut panel Data. Saat Anda menjalankan kueri, data akan dikembalikan ke kisi di panel Data

Sekarang, mari bergabung dengan tabel yang ditampilkan di panel Tabel

Kolom yang cocok di CUST dan ORD adalah CustNum. Klik CustNum di CUST, dan seret ke CustNum di ORD. Saat Anda menjatuhkan, sebuah garis akan muncul, menggabungkan kedua tabel

Bidang yang cocok di ORD dan SREP adalah SalID. Dengan cara yang sama, sambungkan bidang SalID antara ORD dan SREP

Sekarang, mari gunakan tabel ini untuk membuat kueri

Misalkan kita hanya tertarik pada penjualan di West Virginia. Dalam hal ini, kami akan membatasi kumpulan data yang dikembalikan hanya ke catatan di mana bidang ST (kode negara bagian) dalam tabel CUST sama dengan WV

Kami menyiapkan filter ini dengan menyeret bidang ST dari tabel CUST ke sel kiri atas panel Kriteria, lalu dengan mengekspresikan filter yang ingin kami gunakan. Anda memberi tahu MS Query nilai apa yang Anda inginkan agar bidang ini sama dengan dengan mengetikkan nilai di baris kedua panel kriteria. Dalam hal ini, kami mengetikkan WV. (MS Query menambahkan tanda kutip tunggal di sekitar WV saat Anda keluar dari sel. )

Di sisi lain, jika kita ingin menampilkan penjualan di mana saja kecuali Virginia Barat, kita dapat memasukkan ekspresi, <> WV di sel ini. Ini akan mengembalikan semua rekaman yang kode negara bagiannya tidak memiliki nilai WV

Perhatikan bahwa kriteria ini tidak peka huruf besar/kecil saat Anda membuat kueri file Excel, tetapi mungkin peka huruf besar/kecil saat Anda menanyakan sumber data lain. Misalnya, kueri terhadap database Oracle atau SQL Server mungkin peka terhadap huruf besar-kecil, tergantung bagaimana database Anda disiapkan

Selanjutnya, kita perlu memberi tahu MS Query kolom mana yang ingin kita lihat di laporan Excel kita. Untuk latihan ini, mari pilih untuk melihat nomor pelanggan, nama belakang pelanggan, jenis pelanggan (tunai atau kredit), jumlah pesanan, tanggal pengiriman, dan nama perwakilan penjualan. Untuk melakukan ini, klik dua kali bidang dalam tabel yang diperlihatkan dalam gambar berikut, dan bidang tersebut akan muncul sebagai judul di kisi data

Perhatikan bahwa petak data tidak terbatas pada 65.536 baris. Jika Anda menduga kumpulan data yang Anda kembalikan berukuran lebih besar, Anda dapat memeriksanya dengan mengeklik tombol “Last Record”

Sekarang saat yang tepat untuk menyimpan kueri Anda. Ini akan memungkinkan Anda atau orang lain untuk menggunakan kueri yang sama nanti di buku kerja baru, dengan data tambahan, atau keduanya. Untuk menyimpan kueri, pilih File Save As di jendela Microsoft Query lalu beri nama kueri apa pun yang Anda inginkan. Dalam dialog File Save As Anda akan melihat dua format file, dqy dan qry. Jika dqy ditentukan sebagai default, gunakan format tersebut. Format file qry digunakan di versi alat sebelumnya

Pada titik ini, Anda mungkin ingin tahu seperti apa pernyataan SQL yang baru saja Anda buat

Ekspor Data ke Excel

Anda akan dikembalikan ke Excel, dan jendela Impor Data akan membiarkan Anda memutuskan di mana Anda ingin meletakkan data. Untuk contoh ini, saya akan menerima default, dan meletakkan data di lembar kerja yang ada di Kolom A, Baris 1, dengan mengklik tombol OK

Anda sekarang dapat menerapkan format, rumus, dan sebagainya apa pun, yang Anda inginkan

Karena ini hanya pengantar, saya akan berhenti di situ. Anda dapat melakukan lebih banyak lagi

• Anda bisa me-refresh kueri ini dengan mengklik tombol, seandainya data di tabel asli telah berubah
• Anda dapat menambahkan rumus dan membuatnya secara otomatis "salin" setiap kali Anda menyegarkan kueri
• Anda bisa menambahkan parameter, dan membuatnya merujuk ke sel di lembar kerja, sehingga Anda bisa melihat subkumpulan data yang berbeda

Anda dapat membuat kueri serupa pada file teks dan database. Untuk setiap sumber data baru, Anda perlu membuat "Nama Sumber Data". Setelah melakukannya, Anda dapat menggunakan sumber data berulang kali untuk membuat sejumlah kueri terhadap database tersebut. Anda bisa menyimpan kueri dan menggunakannya di buku kerja baru

Kembalikan Data ke Tabel Pivot

Izinkan saya menunjukkan satu trik terakhir, yang memungkinkan Anda menganalisis data saat kumpulan data yang ingin Anda lihat terlalu besar untuk muat di spreadsheet Excel

Dari menu Data, pilih Tabel Pivot dan Bagan Pivot. Pilih Sumber Data Eksternal saat Wizard muncul

Pilih Berikutnya

Dalam dialog “Langkah 2 dari 3” di atas, pilih Dapatkan Data

Pilih sumber data Anda dan lanjutkan seperti sebelumnya, atau buat kueri yang benar-benar baru. Saat Anda kembali, Anda akan memiliki PivotTable dengan semua data di cache pivot, tetapi tidak di spreadsheet. Bahkan jika data tidak muat di spreadsheet, ini akan memungkinkan Anda untuk membuat semua laporan pivot yang Anda butuhkan

Hal yang menurut saya paling menarik tentang pendekatan ini adalah relatif mudah dipelajari jika Anda memiliki dokumentasi yang bagus. Saya dapat mengurangi beban kerja dan stres saya secara signifikan dengan mengajari orang-orang yang menginginkan laporan satu kali yang relatif sederhana, atau mereka yang ingin melihat data dalam berbagai konfigurasi berbeda, cara menggunakan alat ini

Jika menurut Anda ini bisa bermanfaat bagi Anda, saya sangat merekomendasikan Tn. Buku Zapawa, “Excel Advanced Report Development” tersedia sekarang dari Wiley Publishing, Inc

Anda dapat mengunduh file basis data Marty di sini

• TAG
• Koneksi
• Permintaan Microsoft
• Permintaan MS
• Tabel pivot
• Tabel pivot
• Data Relasional
• SQL
• Memperbarui

Facebook

Twitter

Pinterest

Linkedin

Artikel sebelumnya Menggunakan Alat Menggambar Excel untuk Membuat Kartun Anda Sendiri

Artikel berikutnya Menggunakan MS Query dengan File Teks untuk Pelaporan Excel Dinamis

Charley Kyd

Di awal karir saya, saya bekerja hampir 20 tahun sebagai CFO turnaround dan startup. Tapi saya akhirnya kehabisan tenaga karena berjuang terus-menerus dengan arus kas. Saat itulah saya mulai menulis tentang Lotus 1-2-3, perangkat lunak spreadsheet yang paling sering saya gunakan selama hari-hari CFO. Ketika Excel akan diperkenalkan untuk PC, salah satu editor majalah saya mengatur pertemuan untuk melihat produk, berbicara dengan pengembang, dan menulis cerita sampul tentang Excel. Jadi saya menggunakan versi Excel pertama sebelum diluncurkan. Dan saya juga telah menggunakan VisiCalc versi pertama sebelum diluncurkan. Kemudian,

Bisakah Anda menanyakan Excel dengan SQL?

Bagaimana cara membaca spreadsheet Excel di SQL?