Gunakan Wizard Jumlah Bersyarat Untuk Memasukkan Formula CSE - Artikel TechTV

Isi kandungan

Salah satu soalan umum di Papan Mesej adalah bagaimana menggunakan fungsi SumIf dengan dua keadaan yang berbeza. Malangnya, jawapannya adalah bahawa SumIf tidak dapat menangani dua keadaan yang berbeza.

Untuk melakukan dua syarat, anda harus menggunakan formula susunan yang agak rumit. Penambahan wizard jumlah bersyarat membolehkan anda memasukkan formula rumit ini dengan mudah.

Berikut adalah lembaran kerja Excel dengan lajur untuk produk, wakil jualan dan penjualan. Data terdapat dalam sel A2: C29.

Sekiranya anda mahu jumlah penjualan, fungsi SUM () sederhana akan berfungsi. =SUM(C2:C29).

Banyak Exceller mengetahui fungsi SumIf. Dengan menggunakan fungsi ini, cukup mudah untuk mengetahui jumlah penjualan produk ABC.=SUMIF(A2:A29,E2,C2:C29)

Juga mudah untuk mengetahui jumlah penjualan yang dibuat oleh wakil jualan Joe dengan =SUMIF(B2:B29,E2,C2:C29).

Anda kemudian akan menganggap bahawa adalah mungkin untuk mengetahui jumlah penjualan produk ABC yang dibuat oleh Joe. Walau bagaimanapun, tidak ada cara untuk melakukan ini dengan fungsi SumIf. Ternyata anda perlu menggunakan formula array atau CSE yang agak rumit.

Mari kita hadapi - formula Sum adalah Excel 101. Formula SumIf tidak ketinggalan dalam kerumitan. Walau bagaimanapun, formula CSE untuk mengira jumlah penjualan ABC yang dibuat oleh Joe sudah cukup untuk menjadikan kepala saya berpusing.

Berita baik - Microsoft menawarkan Wizard Jumlah Bersyarat yang membolehkan bahkan seorang pemula memasuki formula bersyarat yang rumit berdasarkan 1, 2 atau lebih syarat. Wizard Jumlah Bersyarat adalah tambahan Untuk menambahkan fungsi ini ke Excel, pergi ke menu Alat dan pilih Tambah. Dalam kotak dialog Add-Ins, pilih kotak pilihan di sebelah Condisional Sum Wizard dan pilih OK. Anda mungkin memerlukan CD pemasangan anda pada ketika ini, kerana Microsoft tidak memasukkan wizard dalam pemasangan lalai.

Setelah add-in berjaya dihidupkan, akan ada pilihan Conditonal Sum… dekat bahagian bawah menu Tools.

Pilih satu sel dalam set data anda dan pilih Alat - Jumlah Bersyarat. Dengan mengandaikan bahawa data anda diformat dengan baik dengan satu baris tajuk, Excel akan meneka dengan tepat pada julat data anda. Pilih Seterusnya.

Pada langkah 2, pilih lajur untuk dijumlahkan. Dalam kes ini, penyihir sudah meneka bahawa anda ingin menjumlahkan lajur angka pertama (dan satu-satunya) - Penjualan. Di tengah kotak dialog terdapat tiga kawalan lungsur turun. Ini kebetulan betul untuk keadaan pertama - Produk sama dengan ABC, jadi pilih butang Tambah Keadaan.

Kemudian anda boleh menambah syarat kedua anda. Dalam kes ini, anda ingin menentukan bahawa Sales Rep adalah Joe. Pilih anak panah untuk menu lungsur pertama. Excel menawarkan senarai abjad nama lajur yang ada. Pilih Pegawai Jualan.

Lungsur turun tengah betul, tetapi untuk kelengkapan di sini, anda dapat melihat bahawa anda mungkin memilih sama, kurang daripada, lebih besar daripada, kurang daripada atau sama, lebih besar daripada atau sama, atau tidak sama.

Dari menu lungsur ketiga, pilih Joe.

Pilih butang Tambah Keadaan.

Anda kini sudah bersedia untuk pergi ke Langkah 3. Tekan butang Next.

Pada langkah 3, anda mempunyai dua pilihan. Dalam pilihan pertama, Wizard akan memasukkan formula tunggal dengan nilai "ABC" dan "Joe" yang dimasukkan ke dalam formula. Ini akan memberi anda jawapan, tetapi tidak akan ada peluang untuk menukar formula dengan mudah. Dengan pilihan kedua, Excel akan menubuhkan sel baru dengan nilai "ABC" dan sel baru dengan nilai "Joe". Sel ketiga akan mengandungi formula yang melakukan jumlah bersyarat berdasarkan kedua nilai tersebut. Dengan pilihan ini, anda boleh memasukkan nilai baru ke dalam sel untuk melihat jumlah XYZ yang dijual oleh Adam.

Wizard kemudian akan bertanya di mana anda mahukan nilai untuk ABC. Pilih sel dan pilih Seterusnya. Ulangi ketika Wizard meminta anda memilih sel untuk Joe dan formula.

Apabila anda memilih Selesai pada langkah terakhir, Excel akan membuat versi formula CSE yang sedikit berbeza (tetapi sah).

Formula ini mengira bahawa Joe menjual ABC $ 33,338.

Sekiranya anda menukar sel input produk dari ABC menjadi DEF, formula akan dikira semula untuk menunjukkan bahawa Joe menjual DEF sebanyak $ 24,478.

The Conditional Sum Wizard meletakkan formula rumit yang dapat dijangkau oleh semua pemilik Excel.

Maklumat tambahan:Sekiranya anda ingin membina jadual yang akan menunjukkan penjualan setiap produk oleh setiap wakil jualan, ada beberapa "penjagaan dan memberi makan" khas yang perlu anda ketahui mengenai formula ini. Taip setiap wakil jualan di bahagian atas julat. Taip setiap produk di lajur kiri julat. Edit formula yang disediakan oleh ahli sihir. Dalam gambar di bawah, formula menunjukkan produk di sel E6. Rujukan ini semestinya bernilai $ E6. Sekiranya anda meninggalkan rujukan sebagai E6 dan menyalin formula ke lajur G, formula akan melihat F6 dan bukan E6 dan ini akan salah. Menambah tanda dolar sebelum E di E6 akan memastikan bahawa formula selalu melihat produk di lajur E. Rumus itu juga menunjukkan wakil penjualan di sel F5. Rujukan ini semestinya bernilai F $ 5. Sekiranya anda meninggalkan rujukan sebagai F5 dan menyalin ke baris 7,rujukan F5 akan berubah menjadi F6 dan ini tidak betul. Menambah tanda dolar sebelum nombor baris akan mengunci nombor baris dan rujukan akan selalu menunjuk ke baris 5.

Dalam mod Edit (pilih sel dan tekan F2 untuk mengedit), taipkan $ sebelum E. Ketik tanda dolar sebelum tanda 5 di F5. Jangan tekan Enter lagi!

Formula ini adalah jenis formula khas. Sekiranya anda menekan Enter, anda akan mendapat 0, yang tidak betul.

Daripada mengetik Enter, tahan kekunci Ctrl dan Shift semasa anda menekan Enter. Kombinasi keajaiban C TRL + S hift + E NTER sebabnya saya memanggilnya formula CSE.

Terdapat satu pertimbangan terakhir sebelum menyalin formula ke jadual yang lain. Kecenderungan anda mungkin menyalin F6 dan menampal ke F6: G8. Sekiranya anda mencuba ini, Excel akan memberi anda mesej "Anda Tidak Boleh Mengubah Sebahagian daripada Array" yang membingungkan. Excel mengeluh bahawa anda tidak dapat menampal formula CSE ke dalam julat yang mengandungi formula CSE yang asal.