Perkongsian kemahiran Excel Praktikal: Bagaimana untuk mencari orang nombor satu dengan pelbagai kriteria

Kedudukan adalah mudah; tetapi jika terdapat beberapa kategori projek dan mungkin terdapat prestasi yang sama, bagaimana untuk mengetahui dengan cepat orang yang menduduki tempat pertama dalam setiap bahagian? Ini memerlukan pemadanan dengan berbilang syarat untuk mencari nombor satu yang dikehendaki. Terdapat dua penyelesaian yang disediakan di sini, tetapi kedua-duanya tidak sempurna. Bolehkah anda memperbaikinya?

Mesyuarat pujian tahunan bakal bermula. Mari cari mereka bersama-sama!

Data jualan pelbagai peralatan elektrik pada platform e-dagang syarikat tertentu adalah seperti berikut:

Data hanya mempunyai nombor pesanan jualan, produk nama, nama kakitangan perniagaan dan jumlah jualan , kini adalah perlu untuk mengira juara jualan setiap jenis produk dalam format yang ditunjukkan di bawah.

Melihat soalan ini, saya tertanya-tanya apakah kaedah yang boleh anda fikirkan? Jadual pangsi, fungsi MAX atau VLOOKUP...

Veteran mengesyorkan dua kaedah: yang pertama ialah lajur tambahan + formula; yang kedua ialah jadual pangsi + formula.

Kaedah 1: Lajur + formula tambahan

Langkah 1: Tambah lajur tambahan

Mula dengan meringkaskan jualan setiap orang mengikut nama produk. Jumlahkan mengikut syarat, di sini kita menggunakan fungsi SUMIFS untuk statistik. Walaupun hasil yang sama boleh dicapai menggunakan jadual pangsi, jadual pangsi tidak dapat memperoleh kesan akhir yang diingini pada satu masa, jadi lebih mudah untuk menggunakan lajur tambahan.

Formula:

=SUMIFS(D:D,C:C,C2,B:B,B2)

Format formula: =SUMIFS(求和区域，条件区域1，条件1，条件区域2，条件2……)

SUMIFS ialah berbilang syarat carian Untuk fungsi jumlah, parameter pertama ialah lajur tempat data yang hendak dijumlahkan terletak, dan parameter berikut dikumpulkan secara berpasangan untuk membentuk satu set syarat. Dalam contoh ini, set syarat pertama ialah kakitangan perniagaan, jadi kawasan keadaan 1 ialah lajur C, syarat 1 ialah C2;

Dengan lajur tambahan, langkah seterusnya adalah untuk mengetahui jumlah jualan tertinggi dalam setiap kategori. Apa yang perlu diperhatikan di sini ialah dalam jadual keputusan statistik, nama juara jualan didahulukan dan volum jualan didahulukan. Dalam statistik sebenar, tidak perlu mengira dalam susunan ini Kami akan mengira mana yang lebih mudah dahulu.

Langkah 2: Kira volum jualan tertinggi

Biasanya bila bercakap tentang nilai maksimum, perkara pertama yang terlintas di fikiran ialah MAX fungsi. Penggunaan fungsi ini hampir sama dengan SUM Anda hanya perlu memberikan set nombor atau kawasan data untuk mendapatkan nilai maksimum dalam set nombor ini.

Dalam contoh hari ini, kerana apa yang kita ingin dapatkan adalah nilai maksimum dalam kategori yang sama, iaitu statistik nilai maksimum berdasarkan syarat, kita tidak boleh terus menggunakan fungsi MAX untuk mendapatkan hasilnya >

Ini Terdapat formula rutin tetap untuk mengira nilai maksimum berdasarkan syarat:

=MAX(数据区域*（条件区域1=条件1）*（条件区域2=条件2）……)

Dalam contoh ini, terdapat hanya satu syarat, iaitu nama produk , jadi formulanya ialah:

=MAX($E:$E0*($B:$B0=G2))

Apabila menggunakan formula ini, anda perlu memberi perhatian kepada tiga perkara:

(1) Julat mesti tepat, dan tidak disyorkan untuk memilih keseluruhan lajur sebagai kawasan pengiraan; (2) Formula melibatkan operasi tatasusunan Selepas memasukkan formula, anda perlu menekan kekunci Ctrl+Shift+Enter. Selepas menekan kekunci, sepasang kurungan kerinting akan ditambahkan secara automatik pada formula

(3) Kerana formula perlu ditarik ke bawah, untuk mengelakkan kawasan pengiraan Tukar, jadi julat yang terlibat memerlukan menggunakan rujukan mutlak.

Prinsip khusus formula ini melibatkan prinsip pengiraan nilai logik dan tatasusunan, yang akan kami terangkan secara khusus pada masa hadapan.

Pada ketika ini, ketahui kakitangan perniagaan yang sepadan dengan jualan tertinggi bagi setiap jenis produk, dan lengkapkan semua statistik.

Langkah 3: Cari personel juara

Semak personel berdasarkan jualan, ini sebenarnya rujukan carian, gunakan VLOOKUP atau INDEX Ini boleh dilakukan oleh fungsi rujukan lain.

Hampir berjaya, kini tiba masanya untuk mengupas epal. Ciri mengelupas epal adalah nipis dan ketepatan.

Butiran pertama: Jualan kumulatif dalam sumber data terletak di sebelah kanan ahli perniagaan.

Jika kita menggunakan VLOOKUP, kita perlu menggunakan rutin carian terbalik, dan formulanya agak rumit. Anda boleh menggunakan gabungan INDEX dan MATCH, dan formulanya tidak sukar:

=INDEX($C:$C0,MATCH(I2,$E:$E0,0))

Perincian kedua: Kewujudan jualan tertinggi adalah sama.

Gabungan kedua-dua fungsi ini boleh dipanggil perkongsian klasik. Tetapi masih terdapat perincian: kami tidak boleh menolak kemungkinan jualan maksimum kedua-dua kategori produk adalah sama. Untuk mengelakkan kemungkinan ralat carian di mana jualan maksimum bagi kategori berbeza adalah sama, kami mesti memadankan berdasarkan dua syarat: nama produk dan jualan Formula menjadi:

=INDEX($C:$C0,MATCH(G2&I2,$B:$B0&$E:$E0,0))

. Salah satu rutin biasa untuk pemadanan berbilang keadaan ialah menggunakan simbol sambungan & untuk merangkai berbilang syarat untuk membentuk syarat baharu untuk pertanyaan Sudah tentu, kawasan pertanyaan juga perlu dirangkai bersama &.

Sudah tentu, jika anda tidak sanggup menggunakan Vlookup untuk membalikkan carian dengan berbilang syarat seperti ini, anda juga boleh menggunakan fungsi LOOKUP untuk melengkapkannya:

=LOOKUP(1,0/(($E:$E0=I2)*($B:$B0=G2)),$C:$C0)

Kaedah biasa kedua untuk pemadanan berbilang keadaan ialah menggunakan tanda sama dengan = untuk mencipta ungkapan dengan kawasan carian untuk berbilang syarat, dan kemudian darabkan ungkapan.

Rutin formula ialah: =LOOKUP(1,0/(条件区域=条件),目标区域) Jika terdapat beberapa syarat, anda boleh terus menaik taraf rutin kepada: =LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)*(条件区域3=条件3)……,目标区域)

<.> Kaedah 2: Jadual pangsi + formula

Langkah 1: Statistik dan kedudukan

Seret nama produk dan kakitangan perniagaan ke dalam kawasan baris, seret volum jualan dua kali ke kawasan nilai, dan kemudian ikuti tutorial tahun lepas daripada Tribe Nest Education "Hei, seret tetikus dua kali untuk mendapatkan statistik prestasi dan kedudukan sekali gus!" 》Tetapkan mod paparan nilai Jualan 2 kepada "Pesanan Menurun" dan medan asas kepada "Kakitangan Perniagaan" untuk mendapatkan statistik prestasi jualan dan kedudukan mengikut kategori produk.

Langkah 2, susun jadual pangsi

Klik jadual pangsi dan klik "Reka Bentuk" pilihan "Tunjukkan sebagai jadual" dan "Ulang semua label projek" dalam menu lungsur "Laporan Reka Letak" dalam kumpulan pilihan "Reka Letak" pada kad. Kemudian klik kanan pada jadual pangsi dan pilih "Subjumlah "Kakitangan Perniagaan"" untuk membatalkan subjumlah item dalam jadual. Jadual berubah kepada yang berikut:

Langkah 3, masukkan formula untuk mendapatkan nama dan prestasi juara

Masukkan formula dalam sel G2:

=INDEX(L:L0,MATCH($G2&1,$K:$K0&$N:$N0,0))

Tekan Ctrl+Shift+Enter untuk menamatkan.

Kemudian klik kanan dan lepaskan formula.

Dalam tutorial hari ini, kami mempelajari beberapa fungsi, iaitu SUMIFS, MAX, INDEX, MATCH, LOOKUP, dan juga mempelajari dua rutin untuk pemadanan berbilang keadaan Anda boleh menggunakannya secara langsung apabila menghadapi masalah yang sama.

Walau bagaimanapun, penyelesaian hari ini adalah tidak sempurna. Walaupun kami meminta diri untuk "kencing epal" dan memberi perhatian kepada butiran dalam tutorial, kami masih terlepas butiran yang sangat penting - jualan tertinggi produk serupa mungkin sama.

Cadangan pembelajaran berkaitan:

tutorial cemerlang

Atas ialah kandungan terperinci Perkongsian kemahiran Excel Praktikal: Bagaimana untuk mencari orang nombor satu dengan pelbagai kriteria. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!