Tutorial ini menyediakan demonstrasi terperinci tentang cara menggunakan fungsi SUMIF dan SUMIFS dalam Helaian Google dengan formula dan contoh.
SUMIF ialah salah satu fungsi matematik dalam Helaian Google, yang digunakan untuk menjumlahkan sel secara bersyarat. Pada asasnya, fungsi SUMIF mencari keadaan tertentu dalam julat sel dan kemudian menambah nilai yang memenuhi syarat yang diberikan.
Sebagai contoh, anda mempunyai senarai perbelanjaan dalam helaian Google dan anda hanya mahu merumuskan perbelanjaan yang melebihi nilai maksimum tertentu. Atau anda mempunyai senarai item pesanan dan jumlah yang sepadan, dan anda hanya ingin mengetahui jumlah pesanan item tertentu. Di situlah fungsi SUMIF berguna.
SUMIF boleh digunakan untuk menjumlahkan nilai berdasarkan keadaan nombor, keadaan teks, keadaan tarikh, kad liar serta berdasarkan sel kosong dan bukan kosong. Helaian Google mempunyai dua fungsi untuk meringkaskan nilai berdasarkan kriteria: SUMIF dan SUMIFS. Fungsi SUMIF menambah nombor berdasarkan satu syarat manakala SUMIFS menjumlahkan nombor berdasarkan berbilang syarat.
Dalam tutorial ini, kami akan menerangkan cara menggunakan fungsi SUMIF dan SUMIFS dalam Helaian Google untuk menjumlahkan nombor yang memenuhi syarat tertentu.
Fungsi SUMIF dalam Helaian Google – Sintaks dan Argumen
Fungsi SUMIF hanyalah gabungan fungsi SUM dan IF. Fungsi IF mengimbas melalui julat sel untuk keadaan tertentu, dan kemudian fungsi SUM menjumlahkan nombor yang sepadan dengan sel yang memenuhi syarat tersebut.
Sintaks Fungsi SUMIF:
Sintaks fungsi SUMIF dalam Helaian Google adalah seperti berikut:
=SUMIF(julat, kriteria, [julat_jumlah])Hujah:
julat - Julat sel tempat kita mencari sel yang memenuhi kriteria.
kriteria – Kriteria yang menentukan sel mana yang perlu ditambah. Anda boleh mendasarkan kriteria pada nombor, rentetan teks, tarikh, rujukan sel, ungkapan, pengendali logik, aksara kad bebas serta fungsi lain.
jumlah_julat – Hujah ini adalah pilihan. Ia ialah julat data dengan nilai untuk dijumlahkan jika masukan julat yang sepadan sepadan dengan keadaan. Jika anda tidak memasukkan hujah ini, maka 'julat' dijumlahkan sebaliknya.
Sekarang, mari kita lihat cara menggunakan fungsi SUMIF untuk menjumlahkan nilai dengan kriteria yang berbeza.
Fungsi SUMIF dengan Kriteria Nombor
Anda boleh menjumlahkan nombor yang memenuhi kriteria tertentu dalam julat sel, dengan menggunakan salah satu daripada operator perbandingan berikut untuk membuat kriteria.
- lebih besar daripada (>)
- kurang daripada (<)
- lebih besar daripada atau sama dengan (>=)
- kurang daripada atau sama dengan (<=)
- sama dengan (=)
- tidak sama dengan ()
Katakan anda mempunyai hamparan berikut dan anda berminat dengan jumlah jualan yang 1000 atau lebih tinggi.
Begini cara anda boleh memasukkan fungsi SUMIF:
Mula-mula, pilih sel di mana anda mahu keluaran jumlah itu muncul (D3). Untuk menjumlahkan nombor dalam B2:B12 yang lebih besar daripada atau sama dengan 1000, taip formula ini dan tekan 'Enter':
=SUMIF(B2:B12,">=1000",B2:B12)Dalam formula contoh ini, hujah julat dan sum_range (B2:B12) adalah sama, kerana nombor jualan dan kriteria digunakan pada julat yang sama. Dan kami memasukkan nombor sebelum pengendali perbandingan dan menyertakannya dalam tanda petikan kerana kriteria hendaklah sentiasa disertakan dalam tanda petikan berganda kecuali untuk rujukan sel.
Formula mencari nombor yang lebih besar daripada atau sama dengan 1000 dan kemudian menjumlahkan semua nilai yang dipadankan dan menunjukkan keputusan dalam sel D3.
Oleh kerana argumen julat dan sum_range adalah sama, anda boleh mencapai hasil yang sama tanpa argumen sum_range dalam formula, seperti ini:
=SUMIF(B2:B12,">=1000")
Atau anda boleh membekalkan rujukan sel (D2) yang mengandungi nombor dan bukannya kriteria nombor dan sertai operator perbandingan dengan rujukan sel tersebut dalam hujah kriteria:
=SUMIF(B2:B12,">="&D2)Seperti yang anda boleh lihat pengendali perbandingan masih dimasukkan dalam tanda petikan berganda dan pengendali dan rujukan sel disatukan oleh ampersand (&). Dan anda tidak perlu menyertakan rujukan sel dalam tanda petikan.
Nota: Apabila anda merujuk kepada sel yang mengandungi kriteria, pastikan anda tidak meninggalkan sebarang ruang hadapan atau belakang dalam nilai dalam sel. Jika nilai anda mempunyai ruang yang tidak diperlukan sebelum atau selepas nilai dalam sel yang dirujuk, maka formula akan mengembalikan '0' sebagai hasilnya.
Anda juga boleh menggunakan operator logik lain dengan cara yang sama untuk membuat syarat dalam hujah kriteria. Sebagai contoh, untuk menjumlahkan nilai kurang daripada 500:
=SUMIF(B2:B12,"<500")
Jumlahkan Jika Nombor Sama dengan
Jika anda ingin menambah nombor yang sama dengan nombor tertentu, anda boleh memasukkan nombor sahaja atau masukkan nombor dengan tanda sama dalam hujah kriteria.
Sebagai contoh, untuk menjumlahkan jumlah jualan yang sepadan (lajur B) untuk kuantiti (lajur C) yang nilainya bersamaan dengan 20, cuba mana-mana formula ini:
=SUMIF(C2:C12,"=20",B2:B12)
=SUMIF(C2:C12,"20",B2:B12)
=SUMIF(C2:C12,E2,B2:B12)
Untuk menjumlahkan nombor dalam lajur B dengan kuantiti tidak sama dengan 20 dalam lajur C, cuba formula ini:
=SUMIF(C2:C12,"20",B2:B12)
Fungsi SUMIF dengan Kriteria Teks
Jika anda ingin menambah nombor dalam julat sel (lajur atau baris) yang sepadan dengan sel yang mempunyai teks tertentu, anda boleh memasukkan teks itu atau sel yang mengandungi teks dalam argumen kriteria formula SUMIF anda. Sila ambil perhatian bahawa rentetan teks hendaklah sentiasa disertakan dalam petikan berganda (” “).
Sebagai contoh, jika anda mahukan jumlah jualan di wilayah 'Barat', anda boleh menggunakan formula di bawah:
=SUMIF(C2:C13,"Barat",B2:B13)Dalam formula ini, fungsi SUMIF mencari nilai 'Barat' dalam julat sel C2:C13 dan menambah nilai jualan yang sepadan dalam lajur B. Kemudian memaparkan keputusan dalam sel E3.
Anda juga boleh merujuk kepada sel yang mengandungi teks dan bukannya menggunakan teks dalam argumen kriteria:
=SUMIF(C2:C12,E2,B2:B12)
Sekarang, mari kita dapatkan jumlah hasil semua wilayah kecuali 'Barat'. Untuk melakukan itu, kami akan menggunakan tidak sama dengan operator () dalam formula:
=SUMIF(C2:C12,""&E2,B2:B12)
SUMIF dengan WildCards
Dalam kaedah di atas, fungsi SUMIF dengan kriteria teks menyemak julat terhadap teks yang ditentukan dengan tepat. Kemudian ia menjumlahkan nombor parrel kepada teks tepat dan mengabaikan semua nombor lain termasuk rentetan teks yang dipadankan separa. Untuk menjumlahkan nombor dengan rentetan teks padanan separa, anda perlu menyesuaikan salah satu daripada aksara kad bebas berikut dalam kriteria anda:
?(tanda soal) digunakan untuk memadankan mana-mana aksara tunggal, di mana-mana dalam rentetan teks.*(asterisk) digunakan untuk mencari perkataan yang sepadan bersama dengan sebarang urutan aksara.~(tilde) digunakan untuk memadankan teks dengan tanda soal (?) atau aksara asterisk (*).
Kami akan contoh hamparan untuk produk dan kuantitinya untuk menjumlahkan nombor dengan kad bebas:
Asterisk (*) Kad bebas
Contohnya, jika anda ingin menjumlahkan kuantiti semua produk Apple, gunakan formula ini:
=SUMIF(A2:A14,"Apple*",B2:B14)Formula SUMIF ini mencari semua produk dengan perkataan "Apple" pada permulaan dan sebarang bilangan aksara selepasnya (ditandakan dengan '*'). Sebaik sahaja perlawanan ditemui, ia merumuskan Kuantiti nombor yang sepadan dengan rentetan teks yang sepadan.
Anda juga boleh menggunakan berbilang kad bebas dalam kriteria. Dan anda juga boleh memasukkan aksara kad bebas dengan rujukan sel dan bukannya teks langsung.
Untuk melakukan itu, kad bebas mesti disertakan dalam tanda petikan berganda(“ “), dan digabungkan dengan rujukan sel:
=SUMIF(A2:A14,"*"&D2&"*",B2:B14)Formula ini menjumlahkan kuantiti semua produk yang mempunyai perkataan 'Redmi' di dalamnya, tidak kira di mana perkataan itu terletak dalam rentetan.
Tanda soal (?) Kad bebas
Anda boleh menggunakan tanda tanya (?) kad bebas untuk memadankan rentetan teks dengan mana-mana aksara tunggal.
Sebagai contoh, jika anda ingin mencari kuantiti semua varian Xiaomi Redmi 9, anda boleh menggunakan formula ini:
=SUMIF(A2:A14,"Xiaomi Redmi 9?",B2:B14)Formula di atas mencari rentetan teks dengan perkataan "Xiaomi Redmi 9" diikuti dengan mana-mana aksara tunggal dan menjumlahkan yang sepadan Kuantiti nombor.
Tilde (~) Kad bebas
Jika anda ingin memadankan tanda soal sebenar (?) atau aksara asterisk (*), masukkan aksara tilde (~) sebelum kad bebas dalam bahagian syarat formula.
Untuk menambah kuantiti dalam lajur B dengan rentetan sepadan yang mempunyai tanda asterisk pada penghujungnya, masukkan formula di bawah:
=SUMIF(A2:A14,"Samsung Galaxy V~*",B2:B14)
Untuk menambah kuantiti dalam lajur B yang mempunyai tanda soal (?) dalam lajur A dalam baris yang sama, cuba formula di bawah:
=SUMIF(A2:A14,"~?",B2:B14)
Fungsi SUMIF dengan Kriteria Tarikh
Fungsi SUMIF juga boleh membantu anda menjumlahkan nilai secara bersyarat berdasarkan kriteria tarikh – contohnya, nombor yang sepadan dengan tarikh tertentu, atau sebelum tarikh, atau selepas tarikh. Anda juga boleh menggunakan mana-mana operator perbandingan dengan nilai tarikh untuk mencipta kriteria tarikh untuk menjumlahkan nombor.
Tarikh mesti dimasukkan dalam format tarikh yang disokong helaian Google, atau sebagai rujukan sel yang mengandungi tarikh, atau menggunakan fungsi tarikh seperti DATE() atau TODAY().
Kami akan menggunakan hamparan contoh ini untuk menunjukkan kepada anda cara fungsi SUMIF dengan kriteria tarikh berfungsi:
Katakan anda ingin menjumlahkan amaun jualan yang berlaku pada atau sebelum (<=) 29 November 2019 dalam set data di atas, anda boleh menambah nombor jualan tersebut menggunakan fungsi SUMIF dalam salah satu cara ini:
=SUMIF(C2:C13,"<=November 29, 2019",B2:B13)Formula di atas menyemak setiap sel daripada C2 hingga C13 dan hanya sepadan dengan sel yang mengandungi tarikh pada atau sebelum 29 November 2019 (29/11/2019). Dan kemudian jumlahkan jumlah jualan yang sepadan dengan sel yang sepadan tersebut daripada julat sel B2:B13 dan memaparkan hasilnya dalam sel E3.
Tarikh boleh dibekalkan kepada formula dalam sebarang format yang diiktiraf oleh Helaian Google, seperti '29 November 2019', '29 Nov 2019' atau '29/11/2019', dsb. Ingat nilai tarikh dan pengendali mesti sentiasa disertakan dalam tanda petikan berganda.
Anda juga boleh menggunakan fungsi DATE() dalam kriteria sebaliknya direct date vale:
=SUMIF(C2:C13,"<="&TARIKH(2019,11,29),B2:B13)
Atau, anda boleh menggunakan rujukan sel dan bukannya tarikh dalam bahagian kriteria formula:
=SUMIF(C2:C13,"<="&E2,B2:B13)
Jika anda ingin menambah jumlah jualan bersama-sama berdasarkan tarikh hari ini, anda boleh menggunakan fungsi TODAY() dalam hujah kriteria.
Contohnya, untuk menjumlahkan mana-mana dan semua jumlah jualan untuk tarikh hari ini, gunakan formula ini:
=SUMIF(C2:C13,TODAY(),B2:B13)
Fungsi SUMIF dengan sel Kosong atau Bukan Kosong
Kadangkala, anda mungkin perlu menjumlahkan nombor dalam julat sel dengan sel kosong atau bukan kosong dalam baris yang sama. Dalam kes sedemikian, anda boleh menggunakan fungsi SUMIF untuk menjumlahkan nilai berdasarkan kriteria di mana sel kosong atau tidak.
Jumlahkan jika Kosong
Terdapat dua kriteria dalam Helaian Google untuk mencari sel kosong: “” atau “=”.
Contohnya, jika anda ingin menjumlahkan semua amaun jualan yang mengandungi rentetan panjang sifar (secara visual kelihatan kosong) dalam lajur C, gunakan tanda petikan berganda tanpa ruang di antara dalam formula:
=SUMIF(C2:C13,"",B2:B13)
Untuk menjumlahkan semua amaun jualan dalam lajur B dengan sel kosong yang lengkap dalam lajur C, masukkan “=” sebagai kriteria:
=SUMIF(C2:C13,"=",B2:B13)
Jumlah jika Tidak Kosong:
Jika anda ingin menjumlahkan sel yang mengandungi sebarang nilai (bukan kosong), anda boleh menggunakan "" sebagai kriteria dalam formula:
Contohnya, untuk mendapatkan jumlah jualan dengan mana-mana tarikh, gunakan formula ini:
=SUMIF(C2:C13,"",B2:B13)
SUMIF Berdasarkan Pelbagai Kriteria dengan Logik ATAU
Seperti yang telah kita lihat setakat ini, fungsi SUMIF direka bentuk untuk menjumlahkan nombor berdasarkan hanya satu kriteria, tetapi mungkin untuk menjumlahkan nilai berdasarkan berbilang kriteria dengan fungsi SUMIF dalam Helaian Google. Ia boleh dilakukan dengan menggabungkan lebih daripada satu fungsi SUMIF dalam satu formula dengan logik ATAU.
Contohnya, jika anda ingin merumuskan jumlah jualan di wilayah 'Barat' atau wilayah 'Selatan' (ATAU logik) dalam julat yang ditentukan (B2:B13), gunakan formula ini:
=SUMIF(C2:C13,"Barat",B2:B13)+SUMIF(C2:C13,"Selatan",B2:B13)Formula ini menjumlahkan sel apabila sekurang-kurangnya satu daripada syarat adalah BENAR. Oleh itu ia dikenali sebagai 'OR logik'. Ia juga akan menjumlahkan nilai apabila semua syarat dipenuhi.
Bahagian pertama formula menyemak julat C2:C13 untuk teks 'Barat' dan menjumlahkan nilai dalam julat B2:B13 apabila perlawanan dipenuhi. Bahagian saat menyemak nilai teks 'Selatan' dalam julat yang sama C2:C13 dan kemudian menjumlahkan nilai dengan teks yang sepadan dalam julat_sum_yang sama B2:B13. Kemudian kedua-dua jumlah ditambah bersama dan dipaparkan dalam sel E3.
Dalam kes hanya satu kriteria dipenuhi, ia hanya akan mengembalikan nilai jumlah tersebut.
Anda juga boleh menggunakan berbilang kriteria dan bukannya hanya satu atau dua. Dan jika anda menggunakan berbilang kriteria, lebih baik menggunakan rujukan sel sebagai kriteria daripada menulis nilai langsung dalam formula.
=SUMIF(C2:C13,E2,B2:B13)+SUMIF(C2:C13,E3,B2:B13)+SUMIF(C2:C13,E4,B2:B13)
SUMIF dengan OR logik menambah nilai apabila sekurang-kurangnya satu daripada kriteria yang ditentukan dipenuhi, Tetapi jika anda hanya mahu menjumlahkan nilai hanya apabila semua syarat yang ditentukan dipenuhi, anda perlu menggunakan fungsi SUMIFS() adik beradiknya yang baharu.
Fungsi SUMIFS dalam Helaian Google (Berbilang Kriteria)
Apabila anda menggunakan fungsi SUMIF untuk menjumlahkan nilai berdasarkan berbilang kriteria, formula mungkin menjadi terlalu panjang dan rumit, dan anda terdedah untuk melakukan kesilapan. Selain SUMIF akan membenarkan anda menjumlahkan nilai hanya pada julat tunggal dan apabila mana-mana satu syarat adalah BENAR. Di situlah fungsi SUMIFS masuk.
Fungsi SUMIFS membantu anda menjumlahkan nilai berdasarkan berbilang kriteria padanan dalam satu atau lebih julat. Dan ia berfungsi pada logik DAN, bermakna ia hanya boleh menjumlahkan nilai hanya apabila semua syarat yang diberikan dipenuhi. Walaupun satu syarat adalah palsu, ia akan mengembalikan '0' sebagai hasilnya.
Sintaks dan Argumen Fungsi SUMIFS
Sintaks fungsi SUMIFS adalah seperti berikut:
=SUMIFS(julat_jumlah, julat_kriteria1, kriteria1, [julat_kriteria2, ...], [kriteria2, ...])di mana,
- jumlah_julat – Julat sel yang mengandungi nilai yang anda ingin jumlahkan apabila semua syarat dipenuhi.
- kriteria_julat1 – Ia ialah julat sel tempat anda menyemak kriteria1.
- kriteria1 – Ia adalah syarat yang anda perlu semak terhadap criteria_range1.
- criteria_julat2, kriteria2, …– Julat dan kriteria tambahan untuk dinilai. Dan anda boleh menambah lebih banyak julat dan syarat pada formula.
Kami akan menggunakan set data dalam tangkapan skrin berikut untuk menunjukkan cara fungsi SUMIFS berfungsi dengan kriteria yang berbeza.
SUMIFS dengan Syarat Teks
Anda boleh menjumlahkan nilai berdasarkan dua kriteria teks yang berbeza dalam julat yang berbeza. Sebagai contoh, katakan anda ingin mengetahui jumlah jualan item Khemah yang dihantar. Untuk ini, gunakan formula ini:
=SUMIFS(D2:D13,A2:A13,"Khemah",C2:C13,"Dihantar")Dalam formula ini, kami mempunyai dua kriteria: "Khemah" dan "Dihantar". Fungsi SUMIFS menyemak item 'Khemah' (kriteria1) dalam julat A2:A13 (julat_kriteria1) dan menyemak status 'Dihantar' (kriteria2) dalam julat C2:C13 (julat_kriteria2). Apabila kedua-dua syarat dipenuhi, maka ia menjumlahkan nilai yang sepadan dalam julat sel D2:D13 (julat_jumlah).
SUMIFS dengan Kriteria Nombor dan Operator Logik
Anda boleh menggunakan operator bersyarat untuk mencipta keadaan dengan nombor untuk fungsi SUMIFS.
Untuk mencari jumlah jualan lebih daripada 5 kuantiti mana-mana item di negeri California (CA), gunakan formula ini:
=SUMIFS(E2:E13,D2:D13,">5",B2:B13,"CA")Formula ini mempunyai dua syarat: “>5” dan “CA”.
Formula ini menyemak kuantiti (Qty) lebih daripada 5 dalam julat D2:D13 dan menyemak keadaan 'CA' dalam julat B2:B13. Dan apabila kedua-dua syarat dipenuhi (bermaksud terdapat dalam baris yang sama), ia menjumlahkan jumlah dalam E2:E13.
SUMIFS dengan Kriteria Tarikh
Fungsi SUMIFS juga membolehkan anda menyemak berbilang keadaan dalam julat yang sama serta julat yang berbeza.
Katakan, anda ingin menyemak jumlah jualan item yang dihantar selepas 31/5/2021 dan sebelum tarikh 10/6/2021, kemudian gunakan formula ini:
=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)Formula di atas mempunyai tiga syarat: 31/5/2021, 10/5/2021, dan Dihantar. Daripada menggunakan nilai tarikh dan teks langsung, kami merujuk kepada sel yang mengandungi kriteria tersebut.
Formula menyemak tarikh selepas 31/5/2021 (G1) dan tarikh sebelum 10/6/2021 (G2) dalam julat yang sama D2:D13 dan menyemak status 'Diserahkan' antara dua tarikh tersebut. Kemudian, jumlahkan jumlah yang berkaitan dalam julat E2:E13.
SUMIFS dengan Sel Kosong dan Bukan Kosong
Kadangkala, anda mungkin ingin mencari jumlah nilai apabila sel yang sepadan kosong atau tidak. Untuk melakukan itu, anda boleh menggunakan salah satu daripada tiga kriteria yang kami bincangkan sebelum ini: “=”, “”, dan “”.
Contohnya, jika anda hanya ingin menjumlahkan amaun item ‘Khemah’ yang tarikh penghantarannya belum disahkan lagi (sel kosong), anda boleh menggunakan kriteria “=”:
=SUMIFS(D2:D13,A2:A13,"Khemah",C2:C13,"=")Formula mencari item ‘Khemah’ (kriteria1) dalam lajur A dengan sel kosong yang sepadan (kriteria2) dalam lajur C dan kemudian menjumlahkan amaun yang sepadan dalam lajur D. “=” mewakili sel kosong sepenuhnya.
Untuk mencari jumlah jumlah item 'Khemah' yang tarikh penghantarannya telah disahkan (bukan sel kosong), gunakan "" sebagai kriteria:
=SUMIFS(D2:D13,A2:A13,"Khemah",C2:C13,"")Kami baru sahaja menukar "=" untuk "" dalam formula ini. Ia mencari jumlah item Khemah dengan sel bukan kosong dalam lajur C.
SUMIFS dengan OR Logik
Oleh kerana fungsi SUMIFS berfungsi pada logik DAN, ia hanya menjumlahkan apabila semua syarat dipenuhi. Tetapi bagaimana jika anda ingin menjumlahkan nilai berdasarkan berbilang kriteria apabila mana-mana satu daripada kriteria itu dipenuhi. Caranya ialah menggunakan pelbagai fungsi SUMIFS.
Contohnya, jika anda ingin menambah jumlah jualan untuk sama ada 'Rak basikal' ATAU 'Beg galas' apabila statusnya 'Dipesan', cuba formula ini:
=SUMIFS(D2:D13,A2:A13,"Rak basikal",C2:C13,"Dipesan") +SUMIFS(D2:D13,A2:A13,"Beg galas",C2:C13,"Dipesan")Fungsi SUMIFS pertama menyemak dua kriteria "Rak basikal" dan "Dipesan" dan menjumlahkan nilai amaun dalam lajur D. Kemudian, SUMIFS kedua menyemak dua kriteria "Beg galas" dan "Dipesan" dan menjumlahkan nilai amaun dalam lajur D. Dan kemudian , kedua-dua jumlah ditambah bersama dan dipaparkan pada F3. Dengan kata mudah, formula ini dijumlahkan apabila sama ada 'Rak basikal' atau 'Beg galas' ditempah.
Itulah semua yang anda perlu tahu tentang fungsi SUMIF dan SUMIFS dalam Helaian Google.