Mengisih dan Mengumpulkan Dalam QUERY
Susun keputusan dan agregatkan hasilnya dengan ORDER BY dan GROUP BY
Mengisih dan Mengumpulkan Dalam QUERY ialah pelajaran Excel Formulas Academy percuma di CoddyKit. Ini ialah pelajaran 2 daripada 4. Sebanyak 3 pelajaran dalam laluan pembelajaran ini boleh dibaca sepenuhnya secara percuma — selepas itu, CoddyKit PRO membuka akses kepada semua pelajaran, serta latihan praktikal dengan penyunting kod terbina dalam dan tutor kecerdasan buatan yang tersedia 24/7. Pelajaran ini merupakan sebahagian daripada laluan pembelajaran Excel Formulas Academy, dan kemajuan anda disegerakkan merentas web serta aplikasi CoddyKit. Kursus Excel Formulas Academy merangkumi sejumlah 4 pelajaran.
Melangkaui Penapisan
Penapisan menunjukkan baris yang betul kepada anda, tetapi laporan sebenar memerlukan susunan dan jumlah. QUERY mengendalikan kedua-duanya dengan dua klausa tambahan bergaya SQL: ORDER BY dan GROUP BY.
Dengan kedua-duanya, anda boleh menjawab soalan seperti wilayah manakah yang menjual paling banyak atau senaraikan urus niaga daripada yang terbesar hingga yang terkecil, semuanya dalam satu formula.
Mengisih dengan ORDER BY
Klausa ORDER BY mengisih hasil anda berdasarkan satu atau lebih lajur. Klausa ini diletakkan selepas WHERE jika klausa tersebut ada.
Secara lalai, pengisihan dibuat secara menaik (yang terkecil dahulu, A hingga Z). Formula ini menyenaraikan setiap baris yang diisih mengikut jualan daripada rendah ke tinggi.
=QUERY(A1:D7, "SELECT A, B, D ORDER BY D", 1)Susunan Menurun
Tambahkan DESC selepas lajur untuk mengisih daripada yang tertinggi kepada yang terendah. Gunakan ASC untuk menyatakan susunan menaik dengan jelas.
Ini memaparkan jualan terbesar anda dahulu, sesuai untuk senarai prestasi terbaik. Gabungkannya dengan LIMIT untuk mendapatkan 3 teratas yang kemas.
=QUERY(A1:D7, "SELECT B, D ORDER BY D DESC LIMIT 3", 1)Mengisih Mengikut Berbilang Lajur
Senaraikan beberapa lajur dalam ORDER BY, dipisahkan dengan koma, untuk memecahkan seri. Sheets mengisih berdasarkan lajur pertama, kemudian menggunakan lajur seterusnya untuk menyusun baris yang sepadan.
Di sini, hasil dikumpulkan mengikut wilayah secara abjad, dan dalam setiap wilayah, jualan tertinggi dipaparkan dahulu.
=QUERY(A1:D7, "SELECT A, B, D ORDER BY A ASC, D DESC", 1)Memperkenalkan GROUP BY
GROUP BY menggabungkan baris yang berkongsi nilai menjadi satu baris ringkasan. Inilah cara anda membina jumlah mengikut kategori.
Untuk menggunakannya, SELECT anda menggabungkan lajur pengumpulan dengan fungsi agregat seperti SUM, COUNT atau AVG yang digunakan pada lajur lain.
Menjumlahkan Mengikut Kumpulan
Formula ini menjumlahkan jualan bagi setiap wilayah. SUM(D) menambah lajur Sales, manakala GROUP BY A menghasilkan satu baris bagi setiap wilayah.
Hasilnya ialah jadual pangsi kecil: Timur dengan jumlahnya, Barat dengan jumlahnya, semuanya daripada satu fungsi.
=QUERY(A1:D7, "SELECT A, SUM(D) GROUP BY A", 1)Mengira Mengikut Kumpulan
Gantikan fungsi tersebut dengan COUNT untuk mengira baris dan bukannya menjumlahkannya. Ini memberitahu anda bilangan urus niaga yang dimuktamadkan oleh setiap wilayah.
Anda boleh menggunakan COUNT(D) untuk mengira sel jualan yang tidak kosong, lalu mendapatkan kiraan baris pantas bagi setiap kumpulan.
=QUERY(A1:D7, "SELECT A, COUNT(D) GROUP BY A", 1)Mencari Purata Mengikut Kumpulan
Gunakan AVG untuk mendapatkan nilai min dalam setiap kumpulan. Di sini kita mendapatkan saiz purata urus niaga bagi setiap wilayah.
Anda juga boleh mencampurkan fungsi agregat: pilih kedua-dua SUM(D) dan AVG(D) dalam pertanyaan yang sama untuk memaparkan jumlah dan purata bersebelahan.
=QUERY(A1:D7, "SELECT A, SUM(D), AVG(D) GROUP BY A", 1)Setiap Nilai Bukan Agregat Mesti Dikumpulkan
Ralat yang biasa berlaku: setiap lajur dalam SELECT yang tidak diletakkan dalam fungsi agregat mesti muncul dalam GROUP BY.
SELECT A, B, SUM(D) GROUP BY A gagal kerana B tidak diagregatkan dan tidak dikumpulkan. Sama ada kumpulkan kedua-duanya atau keluarkan B daripada senarai pilihan.
=QUERY(A1:D7, "SELECT A, B, SUM(D) GROUP BY A, B", 1)Mengisih Hasil yang Dikumpulkan
Gabungkan klausa untuk menyusun kedudukan ringkasan anda. Selepas pengumpulan, isih mengikut nilai agregat untuk meletakkan kumpulan terbesar di bahagian atas.
Ini menyenaraikan setiap wilayah dengan jumlah jualannya, disusun daripada jumlah tertinggi kepada terendah, sebagai papan kedudukan yang terus boleh digunakan.
=QUERY(A1:D7, "SELECT A, SUM(D) GROUP BY A ORDER BY SUM(D) DESC", 1)Menamakan Lajur Agregat
Lajur yang dikumpulkan mendapat tajuk yang kurang kemas seperti jumlah Jualan. Tambahkan klausa LABEL untuk menamakannya semula demi laporan yang lebih kemas.
Ini menamakan semula lajur jumlah kepada Jumlah Jualan. Teks penamaan menggunakan tanda petik tunggal, sama seperti nilai penapis.
=QUERY(A1:D7, "SELECT A, SUM(D) GROUP BY A LABEL SUM(D) 'Total Sales'", 1)Semakan Ringkas
Semak pemahaman anda tentang pengisihan dan pengumpulan.
Ringkasan
Kini anda boleh membentuk hasil QUERY:
ORDER BY col [ASC|DESC]mengisih hasil, dengan lajur tambahan untuk memecahkan seriGROUP BYbersamaSUM,COUNTatauAVGmembina ringkasan mengikut kategori- Lajur terpilih yang bukan agregat mesti berada dalam
GROUP BY LABELmenamakan semula tajuk agregat
Kini satu formula menghasilkan laporan yang diisih, dikumpulkan dan dinamakan.
=QUERY(A1:D7, "SELECT A, SUM(D) GROUP BY A ORDER BY SUM(D) DESC LABEL SUM(D) 'Total Sales'", 1)
Pelajari Excel dengan tutor kecerdasan buatan — percuma
Tulis dan jalankan kod sebenar dalam pelayar anda, dapatkan bantuan segera daripada tutor kecerdasan buatan yang tersedia 24/7, dan sambung semula dari tempat anda berhenti di web atau dalam aplikasi.
- Kursus
- 30
- Pelajaran
- 120
Soalan Lazim
Adakah pelajaran “Mengisih dan Mengumpulkan Dalam QUERY” percuma?
Ya — sebanyak 3 pelajaran dalam laluan pembelajaran Excel Formulas Academy, termasuk “Mengisih dan Mengumpulkan Dalam QUERY”, boleh dibaca sepenuhnya secara percuma di web ini. Selepas itu, CoddyKit PRO membuka akses kepada semua pelajaran, serta latihan interaktif dengan penyunting kod terbina dalam dan tutor kecerdasan buatan yang tersedia 24/7. Kursus Excel Formulas Academy merangkumi sejumlah 4 pelajaran.
Apakah yang akan saya pelajari dalam “Mengisih dan Mengumpulkan Dalam QUERY”?
Susun keputusan dan agregatkan hasilnya dengan ORDER BY dan GROUP BY Anda berlatih Excel Formulas Academy menggunakan kod praktikal yang dijalankan terus dalam pelayar, manakala tutor kecerdasan buatan 24/7 menjawab soalan anda semasa anda mengikuti pelajaran.
Adakah saya memerlukan pengalaman untuk memulakan Excel Formulas Academy?
Tiada pengalaman terdahulu diperlukan. Pembelajaran Excel Formulas Academy di CoddyKit disusun untuk pelajar daripada peringkat pemula hingga lanjutan, jadi anda boleh bermula di sini atau dari awal dan belajar mengikut kadar anda sendiri. Ini ialah pelajaran 2 daripada 4.
Berapa lamakah pelajaran “Mengisih dan Mengumpulkan Dalam QUERY” diambil?
Kebanyakan pelajaran CoddyKit mengambil masa kira-kira 5–10 minit. Setiap pelajaran ringkas dan interaktif, jadi anda boleh membuat kemajuan secara berterusan dan menyambung tepat dari tempat anda berhenti di web atau aplikasi.
Bolehkah saya menulis dan menjalankan kod dalam pelajaran Excel Formulas Academy ini?
Ya. Setiap pelajaran Excel Formulas Academy menyertakan penyunting kod terbina dalam, jadi anda boleh menulis dan menjalankan kod sebenar terus dalam pelayar serta menerima maklum balas kecerdasan buatan serta-merta — tanpa memerlukan persediaan setempat.
Semua pelajaran dalam kursus ini
- Membuat Pertanyaan Data Dengan QUERY
- Mengisih dan Mengumpulkan Dalam QUERY
- Menggunakan Formula pada Lajur Dengan ARRAYFORMULA
- Mendapatkan Data Dengan IMPORTRANGE