Cara Menggunakan Power Query Dalam Excel: Panduan Lengkap

Power Query ialah alat risikan perniagaan yang berkuasa dalam Excel yang membolehkan anda mengimport, membersihkan dan mengubah data mengikut keperluan anda. Jika anda pernah menghabiskan masa berjam-jam membersihkan data (kami ada) atau bergelut untuk menggabungkan data daripada pelbagai sumber (kami juga ada), ciri ini akan menjadikan hidup anda lebih mudah.

Cara terbaik untuk mempelajari cara menggunakan Power Query dalam Excel ialah melaksanakan beberapa tugas biasa seperti mengimport data dan menggunakan Power Query Editor untuk mengubah jadual yang terhasil dan menggabungkan data.

Artikel ini membolehkan anda bermula dengan menunjukkan contoh Power Query yang jelas dengan panduan langkah demi langkah . Dengan menguasai Power Query, anda boleh meningkatkan kerja anda ke peringkat seterusnya.

Adakah anda bersedia? Mari lakukan ini.

Isi kandungan

• Cara Mengetahui sama ada Anda Boleh Mengakses Power Query
• Cara Mengimport Data Dengan Power Query
• Cara Menggunakan Editor Power Query
  • Pratonton Data
  • Hartanah
• Cara Bekerja Dengan Berbilang Sumber Data
• Cara Menambah Data Dengan Power Query
• Cara Menggabungkan Data Dengan Power Query
• 5 Cara untuk Mengubah Data Dengan Power Query
  • 1. Mengembangkan dan Memeluwap Data
  • 2. Menyusun Data
  • 3. Memformat Data
  • 4. Menambah Dan Mengalih Keluar Lajur
  • 5. Menukar Jenis Data
• Bekerja dengan Formula dan Bahasa M
  • Formula Bar
  • Menulis Dan Mengubahsuai Kod M
• Apakah Sintaks Bahasa M?
• Cara Menyegarkan Semula Sambungan Data
• Cara Mengintegrasikan Power Query Dengan Model Data
• Cara Mengautomasikan Dengan Power Query Dan VBA
• Ciri Lanjutan Power Query
• Kerjasama dan Perkongsian
• Mari Kita Selesaikan Ini

Cara Mengetahui sama ada Anda Boleh Mengakses Power Query

Sebelum anda mula menggunakan Power Query, anda perlu memastikan bahawa anda mempunyai versi Excel yang mempunyai ciri tersebut.

Berikut ialah gambaran keseluruhan ringkas Power Query mengikut versi:

• Excel 2010 dan Excel 2013: tersedia sebagai tambahan yang perlu dimuat turun dan dipasang.

• Excel 2016: disepadukan ke dalam Excel sebagai "Dapatkan & Ubah".

• Excel 2019: prestasi dan fungsi yang lebih baik.

• Excel 2021 dan Excel untuk Microsoft 365: penyepaduan dengan sumber data awan.

• Excel untuk Mac: hanya tersedia daripada Microsoft 365 versi 16.69 dan lebih baru.

Untuk mengesahkan bahawa alat itu disepadukan dalam buku kerja Excel anda (2016 atau lebih baru), pergi ke tab Data dan cari bahagian "Dapatkan & Ubah Data" dalam gambar ini:

Jika anda mempunyai Excel 2010 atau 2013 pada Windows, anda boleh memuat turun , memasang dan mendayakan tambahan Power Query.

Cara Mengimport Data Dengan Power Query

Alat Power Query membolehkan anda mengimport dan menyambung ke pelbagai sumber data:

• Fail, termasuk format CSV, XML dan JSON

• URL web atau titik akhir API

• Pangkalan data, termasuk Access, SQL Server, Oracle, dan kebanyakan pangkalan data moden

• Perkhidmatan Azure, fail SharePoint dan suapan OData

Sumber data yang paling mudah untuk dimulakan ialah fail CSV ringkas dengan beberapa baris data. Buat fail teks dan tambahkan empat nama pertama dan terakhir ini, dipisahkan dengan koma:

• Joe, Bloggs

• Anne, Ryan

• James, Stewart

• Mary, Brown

Pastikan anda menyimpan fail dengan sambungan .CSV. Fail anda akan kelihatan seperti ini:

Untuk memuatkan data dengan Power Query, ikuti langkah berikut:

1. Pergi ke tab Data.

2. Klik butang "Dapatkan Fail" dalam tab Dapatkan & Ubah Data.

3. Pilih fail sampel anda.

Anda mungkin perlu menunggu beberapa saat untuk alat menarik masuk data ke anak tetingkap pratonton. Power Query menggunakan cukup banyak sumber mesin walaupun untuk import yang kecil, jadi harap bersabar.

Petua: tutup aplikasi lain untuk mempercepatkan prestasi.

Power Query akan memaparkan tetingkap "Import Teks/CSV" dengan pratonton data anda.

Jika data kelihatan betul, anda boleh meneruskan untuk memuatkan atau mengubah data:

• butang Muat akan memuatkan data terus ke dalam Excel.

• butang Transform Data membolehkan anda mengubah suai data sebelum memuatkannya.

Adakah anda perasan bahawa fail sampel tidak mempunyai pengepala? Jika ia dimuatkan terus ke dalam Excel, pengepala akan menjadi "Lajur 1" dan "Lajur 2".

Sebaliknya, anda boleh menarik data ke dalam editor Power Query dan membuat beberapa perubahan. Untuk berbuat demikian, klik butang "Transform Data".

Editor Power Query kini dibuka, yang membawa kita ke bahagian seterusnya.

Cara Menggunakan Editor Power Query

Dalam bahagian ini, kami akan meneroka beberapa aspek penting untuk bekerja dengan Editor Power Query.

Pratonton Data

Dalam kawasan Pratonton Data, anda boleh melihat dan berinteraksi dengan data yang diimport anda. Anda boleh menapis baris, mengubah suai jenis data lajur dan membentuk semula data untuk memenuhi keperluan anda.

Apabila anda selesai, Excel akan memuatkan data yang diubah menjadi lembaran kerja.

Menggunakan fail sampel, anda boleh mengedit nama lajur terus dalam anak tetingkap ini.

Untuk berbuat demikian:

1. Klik kanan pengepala lajur.

2. Klik "Namakan semula" dalam menu lungsur.

3. Taipkan nama pilihan ke dalam kotak pengepala.

Semasa anda membuat perubahan, bahagian "Langkah Gunaan" di sebelah kanan anak tetingkap pratonton mengemas kini untuk mencerminkan setiap transformasi, membolehkan anda menjejak dan mengubah suai langkah pemprosesan data anda mengikut keperluan.

Hartanah

Menetapkan sifat untuk pertanyaan anda adalah penting untuk mengatur dan menjejak kerja anda.

Anak tetingkap Tetapan Pertanyaan berada di sebelah kanan tetingkap Power Query.

1. Klik pautan "Semua Harta".

2. Edit nama Pertanyaan menjadi deskriptif.

3. Tambahkan penerangan untuk memberikan maklumat lanjut.

Sangat mudah untuk melupakan apa pertanyaan minggu lepas telah disediakan untuk dilakukan. Dalam kes ini, nama pertanyaan telah ditukar dan penerangan telah ditambahkan yang memberikan tujuan pertanyaan.

Jika anda ingin mengetahui lebih lanjut tentang pilihan yang tersedia, lihat gambaran keseluruhan Editor Power Query ini .

Cara Bekerja Dengan Berbilang Sumber Data

Editor Power Query menawarkan dua kaedah utama untuk menggabungkan data daripada pelbagai sumber: menambahkan dan menggabungkan.

Menambah menggabungkan jadual secara menegak, menyusunnya di atas satu sama lain. Penggabungan membolehkan anda menyertai jadual berdasarkan lajur biasa.

Untuk melihat cara ini berfungsi, buat fail CSV kecil kedua dengan struktur yang sama seperti yang pertama. Kali ini, letakkan dua nama (pertama dan terakhir) ke dalam fail:

• Joe, Bloggs

• Laura, Cane

Perhatikan bahawa salah satu nama ini diulang dari fail sebelumnya.

Sekarang ikuti langkah ini untuk menarik data ke dalam buku kerja Microsoft Excel anda.

1. Tutup tetingkap Power Query Editor dan simpan perubahan anda.

2. Tambah lembaran kerja baharu.

3. Ikuti langkah dalam bahagian sebelumnya untuk mengimport fail kedua.

4. Klik butang Transform.

5. Namakan semula pengepala lajur sebagai "Nama Pertama" dan "Nama Akhir".

Anak tetingkap kanan dalam lembaran kerja Excel menunjukkan Pertanyaan & Sambungan semasa. Anda kini sepatutnya melihat dua pertanyaan dalam anak tetingkap.

Cara Menambah Data Dengan Power Query

Dalam bahagian ini, anda akan menambahkan fail CSV sampel kedua pada yang pertama.

Ikuti langkah ini untuk menambah data:

1. Klik kanan pada jadual pertama (Nama Pelanggan) dalam anak tetingkap Pertanyaan & Sambungan.

2. Pilih "Edit" untuk membuka jadual dalam Editor Pertanyaan Kuasa.

3. Klik pada Tambah Pertanyaan dalam bahagian Gabungkan.

4. Pilih jadual kedua daripada senarai juntai bawah jadual untuk ditambahkan.

Hasilnya ialah dua baris tambahan dalam jadual pertama.

Perhatikan bahawa satu baris diulang - ini adalah cara operasi tambah berfungsi. Ia menambah data dari satu jadual ke jadual lain, tidak kira sama ada data itu sudah wujud.

Anda boleh mengalih keluar data pendua dalam Editor Power Query dengan langkah berikut:

1. Klik pada pengepala lajur "Nama Pertama".

2. Tahan kekunci "Ctrl" (atau kekunci "Cmd" pada Mac).

3. Klik pada kedua-dua tajuk lajur.

4. Klik pada "Alih Keluar Baris" dalam tab Laman Utama pada reben Power Query Editor.

5. Pilih "Alih Keluar Pendua" daripada menu lungsur.

Cara Menggabungkan Data Dengan Power Query

Apabila anda menggabungkan data, anda mesti memilih lajur yang sepadan antara pertanyaan.

Katakan anda mempunyai dua fail yang dibatasi koma di mana satu mengandungi nombor telefon pelanggan dan satu lagi mengandungi alamat. Kedua-dua fail kelihatan seperti ini:

Ikut arahan dalam bahagian sebelumnya untuk memuatkan setiap fail ke dalam pertanyaan kuasanya sendiri. Kemudian anda boleh berlatih menggabungkan jadual.

Dalam kes data sampel kami, kedua-dua lajur (Nama Pertama dan Nama Keluarga) hendaklah dipilih sebagai lajur yang sepadan. Tahan kekunci Ctrl untuk memilih berbilang lajur.

Ikut langkah-langkah ini:

1. Klik kanan pada jadual pertama dalam anak tetingkap Pertanyaan & Sambungan.

2. Pilih "Edit" untuk membuka jadual dalam Editor Pertanyaan Kuasa.

3. Pilih "Gabungkan Pertanyaan" daripada kumpulan "Gabungkan".

4. Pilih jadual kedua dalam senarai juntai bawah jadual.

5. Klik pada lajur pertama dalam jadual pertama.

6. Tahan kekunci Ctrl dan klik pada lajur kedua dalam jadual pertama.

7. Tahan kekunci Ctrl dan klik pada kedua-dua lajur dalam jadual kedua.

8. Kekalkan operator gabungan lalai sebagai "Kiri Luar".

9. Klik "OK" untuk menggunakan perubahan.

Operator gabungan "Left Outer" memastikan data pendua dihapuskan.

Dalam Editor Power Query, anda akan melihat lajur baharu bernama "Jadual" atau "Jadual Bersarang" yang mengandungi data gabungan daripada pertanyaan kedua.

1. Klik pada ikon kembangkan (dua anak panah) dalam pengepala lajur ini.

2. Nyahtanda kotak "gunakan lajur asal sebagai awalan" (ini mengelakkan nama lajur yang panjang).

3. Klik “OK”.

4. Klik "Tutup dan Muatkan".

Jadual yang digabungkan mempunyai empat lajur, dengan butiran telefon dan alamat digabungkan. Anda mungkin masih mempunyai sedikit kerja untuk mengemas.

Sebagai contoh, anda mungkin ingin mengedit nama jadual (Gabung1) dan lajur alamat dalam gambar di bawah.

5 Cara untuk Mengubah Data Dengan Power Query

Mengubah data dalam fail Excel anda dipermudahkan dengan Power Query, membolehkan anda membersihkan, menyusun dan memanipulasi data anda dengan berkesan.

Beberapa cara yang paling biasa untuk mengubah data ialah:

• Memperluas dan memekatkan data

• Mengisih data

• Memformat data

• Menambah dan mengalih keluar lajur

• Menukar jenis data

1. Mengembangkan dan Memeluwap Data

Mengembangkan dan memekatkan data adalah penting untuk menjadikan set data anda lebih mudah diurus. Power Query membolehkan anda mengembangkan atau meruntuhkan jadual untuk paparan yang lebih teratur.

Bahagian sebelumnya menunjukkan kepada anda cara mengembangkan dengan memasukkan data daripada jadual berkaitan.

Anda juga boleh memekatkannya untuk hanya memasukkan data diringkaskan berdasarkan pengagregatan pilihan anda seperti jumlah, kiraan atau peratusan.

2. Menyusun Data

Untuk mengisih data, anda boleh menukar susunan dengan cepat dengan nilai menaik atau menurun.

Klik pada anak panah ke bawah di sebelah nama lajur dan pilih "Isih Menaik" atau "Isih Menurun".

3. Memformat Data

Tab Transform dalam Editor Pertanyaan Kuasa menyediakan banyak pilihan.

Sebagai contoh, menu lungsur Format membolehkan anda menukar semua data dalam lajur atau keseluruhan jadual kepada huruf besar atau huruf kecil.

4. Menambah Dan Mengalih Keluar Lajur

Power Query memudahkan proses menambah, memadam dan mengubah suai lajur.

To remove a column, simply right-click on the header of the undesired column and select “Remove.” This will permanently delete the column and all of its data from the table.

To add a new column, select the existing column where you’d like the new one to appear. Then use the “Add Column From Examples” feature to create a custom column or to perform calculations between two or more columns.

5. Changing Data Types

Working with consistent and accurate data types is crucial for accurate analysis. Power Query allows you to change data types easily:

1. Right-click on the column header.

2. Choose “Change Type”.

3. Select the desired data type from the list provided.

Changing data types can be particularly useful when dealing with percentage data.

Sebagai contoh, anda mungkin perlu menukar titik perpuluhan kepada peratusan atau menukar peratusan berasaskan teks kepada angka untuk pengiraan dan perbandingan yang lebih mudah.

Bekerja dengan Formula dan Bahasa M

Apabila menggunakan Power Query dalam Excel, anda akan menemui Bahasa M, yang merupakan nadi untuk mencipta formula lanjutan, memanipulasi data dan menghasilkan pertanyaan yang berkuasa. Dalam bahagian ini, anda akan belajar tentang:

• Formula Bar

• Kod M

• elemen penting dalam sintaks Bahasa M

Formula Bar

Bar Formula dalam editor Power Query Excel ialah kawasan di mana anda akan memasukkan formula dan kod M anda.

Untuk menggunakannya, pergi ke editor Power Query dengan memilih pertanyaan daripada anak tetingkap Pertanyaan & Sambungan. Bar Formula terletak di atas anak tetingkap pratonton data.

Menulis Dan Mengubahsuai Kod M

Untuk membuat pertanyaan yang serba boleh dan berkuasa dalam Excel, anda perlu menulis dan mengubah suai bahasa Power Query yang dikenali sebagai kod M. Berikut ialah beberapa petua tentang cara untuk bermula:

• Gunakan anak tetingkap Langkah Gunaan untuk menulis kod langkah demi langkah, menjadikannya mudah untuk menjejak dan mengubah suai logik pertanyaan anda.

• Gabungkan kod M dengan jadual Excel, jadual pangsi dan sumber data luaran, seperti Azure dan fail teks, untuk membuat laporan dinamik dan automatik.

• Laraskan Pilihan Pertanyaan untuk memperhalusi keutamaan pemuatan data anda dan meningkatkan prestasi.

Apabila anda membiasakan diri dengan kod M, anda akan mendapati anda boleh mengautomasikan dan mengoptimumkan banyak proses, menjadikan analisis data anda dalam Excel lebih cekap dan berkuasa.

Untuk melihat kod di sebalik pertanyaan, ikuti langkah berikut:

1. Buka pertanyaan dalam Editor Power Query.

2. Klik pada "Editor Lanjutan" dalam tab Laman Utama.

Ini ialah kod di sebalik jadual gabungan sampel kami (milik anda mungkin kelihatan berbeza):

Apakah Sintaks Bahasa M?

Memahami sintaks Bahasa M adalah penting untuk mencipta formula Power Query yang berkesan. Ingat perkara berikut:

• Bahasa M adalah sensitif huruf besar-besaran, jadi berhati-hati dengan penggunaan huruf besar.

• Gunakan kurungan segi empat sama [] untuk merujuk lajur, rekod dan senarai.

• Untuk merujuk langkah sebelumnya dalam pertanyaan anda, gunakan nama pembolehubahnya (cth, #”Nama Langkah”).

• Keluarkan langkah formula pertanyaan dengan pernyataan "dalam".

Apabila anda memperoleh pengalaman dengan aliran bahasa Power Query , anda akan menemui cara baharu untuk mengubah dan menganalisis data anda, menjadikan buku kerja Excel anda sebagai aset yang lebih berharga.

Tonton video di bawah, sudah tiba masanya untuk mengeluarkan berus penyental dan membersihkan data anda yang tidak kemas.

Cara Menyegarkan Semula Sambungan Data

Salah satu ciri utama Power Query ialah keupayaan untuk menyegarkan sambungan Power Query anda dengan mudah.

Apabila sumber data anda telah dikemas kini dan anda memerlukan maklumat terkini dalam buku kerja anda, anda boleh memuat semula sambungan data dalam anak tetingkap Pertanyaan & Sambungan.

Jika anda tidak melihat anak tetingkap di sebelah kanan lembaran kerja anda, anda boleh membukanya dengan pergi ke jadual Data dan mengklik pada “Pertanyaan & Sambungan”.

Apabila anak tetingkap Pertanyaan & Sambungan dibuka, ia menyenaraikan semua pertanyaan. Ikut langkah-langkah ini:

1. Klik kanan pada pertanyaan yang ingin anda muat semula.

2. Pilih Muat semula daripada menu konteks.

Ini akan mengemas kini buku kerja anda dengan data terkini daripada sumber data bersambung anda.

Cara Mengintegrasikan Power Query Dengan Model Data

Model data Excel ialah ciri berkuasa yang membolehkan anda mencipta alat risikan perniagaan, seperti jadual pangsi, berdasarkan perhubungan data.

Model data pada asasnya ialah pangkalan data tersembunyi yang menyimpan data yang diimport dan hubungan antara jadual.

Ia adalah sebahagian daripada ciri Power Pivot yang terbina dalam versi semasa Excel. Anda mungkin perlu memasang Power Pivot sebagai tambahan untuk mengaksesnya dalam versi sebelumnya).

Anda boleh menyepadukan sambungan data Power Query anda dengan model data dengan langkah berikut:

1. Muatkan data anda menggunakan Power Query seperti yang diterangkan dalam bahagian sebelumnya.

2. Daripada memuatkannya terus ke dalam buku kerja, pilih Muatkan Ke daripada menu Muatkan.

3. Pilih Tambah data ini pada kotak semak Model Data dalam kotak dialog Import Data.

Cara Mengautomasikan Dengan Power Query Dan VBA

Dengan penyepaduan VBA (Visual Basic for Applications), anda boleh membuat sambungan data tersuai dan mengautomasikan langkah transformasi data dalam Power Query.

Anda boleh memanfaatkan kemahiran VBA sedia ada anda untuk membina model data lanjutan dengan menggabungkan data daripada platform seperti Microsoft Access, SharePoint dan Perkhidmatan Analisis.

Walaupun Power Query tidak mempunyai sokongan asli untuk VBA, anda boleh menggunakan kod VBA untuk membuka, menyegarkan dan memanipulasi pertanyaan Power Query secara tidak langsung.

Ini membolehkan anda mencipta aliran kerja data automatik yang berkuasa dan menyelaraskan proses analisis data anda.

Berikut ialah beberapa contoh kod yang menyegarkan pertanyaan kuasa dalam buku kerja semasa.

:

Malapkan wb Sebagai Buku Kerja, pq Sebagai Objek

Set wb = Buku Kerja Ini

Set pq = wb.Queries(“Nama Pelanggan”)

 pq.Segarkan semula

Berikut ialah kod dalam makro Excel VBA:

Ciri Lanjutan Power Query

Power Query Excel menawarkan pelbagai ciri lanjutan untuk memudahkan dan meningkatkan proses analisis data anda. Dengan mempelajari cara menggunakan ciri ini dengan berkesan, anda boleh membawa kemahiran analisis data anda ke tahap yang baharu.

Alat analisis data terbina dalam termasuk:

• Lajur bersyarat untuk menggunakan logik kompleks pada transformasi data anda.

• Pengumpulan dan pengagregatan berfungsi untuk meringkaskan data anda dengan cara yang bermakna.

• Operasi Pivot dan Unpivot untuk membentuk semula data anda untuk analisis yang lebih mudah.

Anda akan menemui banyak ciri ini dalam tab Transform dalam Editor Pertanyaan Kuasa.

Selain itu, anda boleh menyambungkan Power Query kepada alatan Perisikan Perniagaan seperti LuckyTemplates untuk pilihan analisis data dan visualisasi yang lebih lanjut .

Kerjasama dan Perkongsian

Berkongsi sambungan data, pertanyaan dan transformasi anda dengan ahli pasukan anda adalah penting untuk kerjasama yang berkesan.

Anda boleh berkongsi buku kerja dengan pertanyaan dengan menyimpannya ke pemacu rangkaian, SharePoint atau beberapa lokasi storan dikongsi yang lain.

Anda juga boleh mengeksport kod M di belakang satu pertanyaan dengan langkah berikut:

1. Edit pertanyaan dalam Editor Power Query.

2. Klik pada "Editor Lanjutan" dalam tab Laman Utama.

3. Salin keseluruhan kod M dalam tetingkap Advanced Editor.

4. Buka editor teks (cth, Notepad) dan tampal kod M yang disalin ke dalam editor teks.

5. Simpan fail teks dan hantar kepada rakan sekerja anda.

Mari Kita Selesaikan Ini

Dengan pelbagai ciri dan antara muka mesra penggunanya, Power Query ialah alat penting untuk pengguna Excel yang ingin bekerja dengan berkesan dengan data luaran.

Ciri berharga ini membantu menyelaraskan proses membuat laporan, pembersihan data dan melakukan analisis yang kompleks pada jadual data anda. menggunakan

Power Query akhirnya menjimatkan masa anda dan meningkatkan kecekapan apabila bekerja dengan fail Excel.