Pengekstrakan jadual silang Excel, Microsoft Query KO semua fungsi

Untuk mengekstrak data merentas jadual, reaksi pertama ramai rakan kongsi ialah fungsi seperti VLOOKUP, atau formula INDEX+SMALL+IF. Malah, jika anda mengekstrak berbilang lajur data, Microsoft Query, yang telah lama ditinggalkan di sudut oleh ramai orang, adalah raja! Ia bukan sahaja mudah dikendalikan dan mudah menyelesaikan masalah "satu-ke-banyak", tetapi jadual hasil yang dijananya boleh membentuk pautan dinamik dengan sumber data Jika sumber data berubah, hasilnya akan dikemas kini secara dinamik!

Hari ini saya ingin berkongsi dengan anda fungsi yang jarang digunakan tetapi mempunyai kesan ajaib---Microsoft Query untuk membantu anda menyelesaikan "satu-ke-banyak" pengekstrakan data daripada dua jadual Atau untuk menyelesaikan masalah menggunakan satu jadual untuk memadankan jadual lain untuk menghasilkan data tertentu.

Seperti yang ditunjukkan dalam rajah di bawah, terdapat dua lembaran kerja dalam buku kerja yang sama "Jadual Maklumat Kakitangan Jabatan" menyenaraikan nama pekerja dan penyelia yang sepadan dalam setiap jabatan, dan "Jadual Data Jualan Wilayah" menyenaraikan. Berbilang wilayah yang ditanggung oleh setiap pekerja dan data jualan tiga bulan wilayah yang sepadan. Kini ia dikehendaki meringkaskan dua jadual menjadi satu jadual berdasarkan lajur nama.

Jadual asal

Keputusan yang diperlukan

Bagaimana untuk menggunakan Microsoft Query?

LANGKAH 01 Dayakan Microsoft Query dan muatkan data

(1) Buat buku kerja baharu dan klik Di bawah tab [Data], dalam kumpulan [Dapatkan Data Luaran], pilih "Dari Microsoft" dalam menu lungsur turun "Dari Sumber Lain". Pertanyaan".

Klik "Fail Excel" di bawah pilihan "Pangkalan Data" dalam tetingkap [Pilih Sumber Data] dan tandai kotak di bawah "Gunakan [Wizard Pertanyaan] untuk buat/edit pertanyaan ” dan klik OK

Cari lokasi sumber data dalam direktori di sebelah kanan tetingkap [Pilih Buku Kerja], cari fail dalam. nama pangkalan data di sebelah kiri, dan klik OK.

(2) Kadangkala sistem akan menggesa tetingkap berikut: "Sumber data tidak mengandungi jadual yang boleh dilihat", jangan Jangan risau tentang perkara ini, klik OK >

Masukkan tetingkap [Query Wizard] di sebelah kiri di bawah, klik butang "Options" di bawah, buka tetingkap [Table Options] di sebelah kanan, tandakan "System. Jadual" dan klik OK.

Dengan cara ini, lembaran kerja dalam sumber data akan muncul dalam tetingkap [Query Wizard]. Ini kerana Excel memanggil lembaran kerjanya sebagai "jadual sistem", dan anda boleh semaknya dalam tetingkap pertanyaan. Lihatnya " di sebelah kiri ke kanan. "Lajur pertanyaan dalam hasil", klik Seterusnya.

Tetingkap akan muncul, menggesa "Wizard Pertanyaan tidak boleh diteruskan kerana jadual tidak boleh dipautkan ke." Pertanyaan anda sedang dijalankan. Anda mesti berada di Microsoft Seret medan antara jadual dalam Pertanyaan untuk dipautkan secara manual. "Jangan risau tentang perkara ini, klik OK.

LANGKAH 02

Padankan data mengikut item yang diperlukan (1) Gunakan tetikus untuk memilih "nama" dalam "Jadual Maklumat Kakitangan Jabatan" di sebelah kiri, seret ke "nama" dalam "Jadual Data Jualan Wilayah" pada jadual kanan, dan kemudian lepaskan tetikus. Pada masa ini, garis penghubung dengan nod kecil di kedua-dua hujung muncul di antara medan "nama" kedua-dua jadual. Kawasan data di bawah dikemas kini serta-merta.

(2) Memandangkan terdapat dua lajur dengan nama yang sama, kami memilih salah satu lajur dan klik "Padam Lajur" di bawah [Rekod] dalam bar menu.

LANGKAH 03 Kembalikan data hasil ke lembaran kerja Excel

Akhir sekali Apa yang anda perlu lakukan ialah mengembalikan keputusan kepada EXCEL.

(1) Klik butang di sebelah kiri "SQL" dalam bar menu untuk mengembalikan data kepada Excel.

(2) Tetingkap [Import Data] muncul dalam EXCEL Kami memilih untuk memaparkannya sebagai "Jadual" dan meletakkannya dalam lembaran kerja sedia ada.

Hasil yang dikembalikan adalah seperti berikut:

Dalam 3 langkah mudah ini, kami telah melengkapkan pemadanan data yang diperlukan dan menghasilkan lembaran data baharu.

Kegembiraan tambahan

Kami mendapati bahawa data yang dijana oleh Microsoft Query ialah jadual super dan anda juga boleh terus membuat jadual pangsi atau carta pangsi.

Pada masa yang sama, jadual ini dipautkan secara dinamik kepada sumber data. Contohnya, jika kami mengubah suai data asal, klik Simpan untuk menutup.

Klik kanan pada hasil yang dikembalikan untuk memuat semula.

Data akan disegerakkan.

Syarat permohonan

Perlu diambil perhatian bahawa apabila menggunakan kaedah ini, penyeragaman sumber data mesti dipastikan. Adalah dikehendaki bahawa lembaran kerja tidak boleh mengandungi data yang tidak berkaitan dengan sumber data dan baris pertama jadual mestilah pengepala lajur. Jika anda ingin melaksanakan pemautan dinamik, nama dan lokasi buku kerja dan lembaran kerja tidak boleh diubah suai.

Bagaimana pula, adakah semua orang telah mempelajarinya? Adakah ia lebih mudah daripada PQ atau lebih ringkas daripada fungsi?

Cadangan pembelajaran berkaitan: tutorial cemerlang

Atas ialah kandungan terperinci Pengekstrakan jadual silang Excel, Microsoft Query KO semua fungsi. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!