Sumif di Helaian Google dengan contoh formula

Tutorial menunjukkan cara menggunakan fungsi SUMIF dalam hamparan Google ke sel -sel jumlah yang kondusif. Anda akan menemui contoh formula untuk teks, nombor dan tarikh dan belajar bagaimana untuk menyisihkan dengan pelbagai kriteria.

Beberapa fungsi terbaik dalam lembaran Google adalah yang membantu anda meringkaskan dan mengkategorikan data. Hari ini, kita akan melihat dengan lebih dekat salah satu fungsi sedemikian - SUMIF - instrumen yang kuat untuk sel -sel jumlahnya. Sebelum mengkaji contoh sintaks dan formula, izinkan saya bermula dengan beberapa ucapan penting.

Helaian Google mempunyai dua fungsi untuk menambah nombor berdasarkan syarat: sumif dan sumifs . Yang pertama menilai hanya satu keadaan manakala yang terakhir dapat menguji pelbagai keadaan pada satu masa. Dalam tutorial ini, kami akan memberi tumpuan semata -mata pada fungsi SUMIF, penggunaan SUMIF akan diliputi dalam artikel seterusnya.

Jika anda tahu cara menggunakan Sumif dalam Excel Desktop atau Excel Online, Sumif dalam Lembaran Google akan menjadi sekeping kek untuk anda kerana kedua -duanya pada dasarnya sama. Tetapi jangan tergesa -gesa untuk menutup halaman ini lagi - anda mungkin mendapati beberapa formula Sumif yang tidak dapat diobred tetapi sangat berguna yang anda tidak tahu!

• Sumif di Helaian Google - Sintaks dan Penggunaan Asas
• Sumif dengan kriteria teks (padanan tepat)
• Sumif dengan watak Wildcard (perlawanan separa)
• Formula Sumif sensitif kes
• Jumlah jika lebih besar daripada, kurang daripada, sama dengan, atau tidak sama dengan
• Formula sumif untuk tarikh
• Jumlah jika kosong atau tidak kosong
• Sumif dengan pelbagai kriteria (atau logik)
• Google Sheets Sumif - Perkara Yang Perlu Diingat

Lihat warna, jumlah nombor!

Jumlah sel dengan warna mengisi mereka - dengan mudah dan tepat

Cuba sekarang

Pendua? Gabungkan, jumlah, selesai!

Tempat pendua, menggabungkan butiran, dan jumlah tally

Cuba sekarang

Buka kunci potensi penuh Lembaran Google anda

Cari sidekick data anda - dipercayai oleh berjuta -juta!

Cari koleksi

Sumif di Helaian Google - Sintaks dan Penggunaan Asas

Fungsi SUMIF adalah Google Sheets direka untuk jumlah data angka berdasarkan satu keadaan. Sintaksinya adalah seperti berikut:

Sumif (julat, kriteria, [sum_range])

Di mana:

• Julat (diperlukan) - Julat sel yang harus dinilai oleh kriteria .
• Kriteria (diperlukan) - Keadaan yang perlu dipenuhi.
• Sum_range (pilihan) - julat di mana jumlah nombor. Jika ditinggalkan, maka julat disimpulkan.

Sebagai contoh, mari buat formula mudah yang akan merangkumi nombor dalam lajur B Jika lajur A mengandungi item yang sama dengan "item sampel".

Untuk ini, kami menentukan hujah -hujah berikut:

• Julat - Senarai Item - A2: A12.
• Kriteria - Sel yang mengandungi item yang menarik - E1.
• Sum_range - jumlah yang akan dijumlahkan - b2: b12.

Meletakkan semua hujah bersama, kami mendapat formula berikut:

=SUMIF(A2:A12,E1,B2:B12)

Dan ia berfungsi dengan tepat seperti yang sepatutnya:

Contoh Sumif Helaian Google

Dari contoh di atas, anda mungkin mempunyai kesan bahawa menggunakan formula Sumif dalam hamparan Google sangat mudah sehingga anda dapat melakukannya dengan menutup mata anda. Dalam kebanyakan kes, ia benar-benar begitu :) tetapi masih terdapat beberapa helah dan kegunaan bukan remeh yang boleh menjadikan formula anda lebih berkesan. Contoh di bawah menunjukkan beberapa kes penggunaan biasa. Untuk membuat contoh lebih mudah untuk diikuti, saya menjemput anda untuk membuka sampel SUMIF SUMIF Google Sheet.

Formula Sumif dengan kriteria teks (padanan tepat)

Untuk menambah nombor yang mempunyai teks tertentu dalam lajur lain dalam baris yang sama, anda hanya membekalkan teks kepentingan dalam argumen kriteria formula SUMIF anda. Seperti biasa, mana -mana teks dalam sebarang argumen mana -mana formula hendaklah dilampirkan dalam "Petikan Double".

Sebagai contoh, untuk mendapatkan jumlah pisang , anda menggunakan formula ini:

=SUMIF(A5:A15,"bananas",B5:B15)

Atau, anda boleh meletakkan kriteria di beberapa sel dan merujuk kepada sel itu:

=SUMIF(A5:A15,B1,B5:B15)

Formula ini jelas, bukan? Sekarang, bagaimana anda mendapat semua item kecuali pisang? Untuk ini, gunakan yang tidak sama dengan pengendali:

=SUMIF(A5:A15,"bananas",B5:B15)

Jika "item pengecualian" adalah input dalam sel, maka anda melampirkan tidak sama dengan pengendali dalam petikan berganda ("") dan menggabungkan rujukan pengendali dan sel dengan menggunakan ampersand (&). Contohnya:

=SUMIF (A5:A15,""&B1, B5:B15)

Tangkapan skrin berikut menunjukkan kedua -dua "jumlah jika sama dengan" dan "jumlah jika tidak sama dengan" formula dalam tindakan:

Sila ambil perhatian bahawa Sumif dalam Helaian Google mencari teks yang ditentukan dengan tepat . Dalam contoh ini, hanya jumlah pisang yang disimpulkan, pisang hijau dan pisang Goldfinger tidak termasuk. Sehingga jumlah perlawanan separa, gunakan aksara Wildcard seperti yang ditunjukkan dalam contoh seterusnya.

Formula Sumif dengan watak Wildcard (perlawanan separa)

Dalam situasi apabila anda ingin jumlah sel dalam satu lajur jika sel dalam lajur lain mengandungi teks atau watak tertentu sebagai sebahagian daripada kandungan sel , masukkan salah satu daripada kad liar berikut dalam kriteria anda:

• Tanda Soalan (?) Untuk memadankan mana -mana watak tunggal.
• Asterisk (*) untuk memadankan sebarang urutan aksara.

Sebagai contoh, untuk merangkum jumlah pelbagai pisang, gunakan formula ini:

=SUMIF(A2:A13,"*bananas*",B2:B13)

Anda juga boleh menggunakan Wildcards bersama -sama dengan rujukan sel. Untuk ini, lampirkan watak Wildcard dalam tanda petikan, dan concatenate dengan rujukan sel:

=SUMIF(A2:A13, "*"&E1&"*", B2:B13)

Sama ada cara, formula Sumif kami menambah jumlah semua pisang:

Untuk memadankan tanda tanya sebenar atau asterisk, awalannya dengan watak Tilde (~) seperti "~?" atau "~*".

Sebagai contoh, jumlah nombor dalam lajur B yang mempunyai asterisk dalam lajur A dalam baris yang sama, gunakan formula ini:

=SUMIF(A2:A13, "~*", B2:B13)

Anda juga boleh menaip asterisk dalam beberapa sel, katakan B1, dan menggabungkan sel itu dengan tilde char:

=SUMIF(A2:A13, "~"&E1, B2:B13)

Sumif sensitif kes di Helaian Google

Secara lalai, SUMIF dalam Helaian Google tidak melihat perbezaan antara huruf kecil dan modal. Untuk memaksa ia untuk menggigit huruf besar dan huruf kecil secara berbeza, gunakan sumif dalam kombinasi dengan fungsi Find dan ArrayFormula:

Sumif (arrayformula (cari (" teks ", julat)), 1, sum_range)

Katakan anda mempunyai senarai nombor pesanan dalam A2: A12 dan jumlah yang sama dalam C2: C12, di mana nombor pesanan yang sama muncul dalam beberapa baris. Anda memasukkan ID pesanan sasaran di beberapa sel, katakan F1, dan gunakan formula berikut untuk mengembalikan jumlah pesanan:

=SUMIF(ARRAYFORMULA(FIND(F1, A2:A12)),1, C2:C12)

Bagaimana formula ini berfungsi

Untuk lebih memahami logik formula, mari kita memecahkannya ke bahagian yang bermakna:

Bahagian yang paling rumit adalah argumen pelbagai : ArrayFormula (cari (F1, A2: A12))

Anda menggunakan fungsi mencari sensitif kes untuk mencari ID pesanan yang tepat. Masalahnya ialah formula mencari biasa hanya boleh mencari dalam satu sel. Untuk mencari dalam julat, formula array diperlukan, jadi sarang anda mencari di dalam arrayformula.

Apabila gabungan di atas mendapati perlawanan yang tepat, ia kembali 1 (kedudukan watak pertama yang dijumpai), jika tidak, ralat #value. Oleh itu, satu -satunya perkara yang perlu anda lakukan ialah meringkaskan jumlah yang sepadan dengan 1. Untuk ini, anda meletakkan 1 dalam hujah kriteria , dan C2: C12 dalam hujah SUM_RANGE . Selesai!

Formula sumif untuk nombor

Untuk jumlah nombor yang memenuhi syarat tertentu, gunakan salah satu pengendali perbandingan dalam formula SUMIF anda. Dalam kebanyakan kes, memilih pengendali yang sesuai tidak menjadi masalah. Menanamnya dalam kriteria dengan betul boleh menjadi satu cabaran.

Jumlah jika lebih besar daripada atau kurang daripada

Untuk membandingkan nombor sumber ke nombor tertentu, gunakan salah satu pengendali logik berikut:

• lebih besar daripada (>)
• kurang daripada (
• lebih besar daripada atau sama dengan (> =)
• kurang daripada atau sama dengan (

Sebagai contoh, untuk menambah nombor dalam B2: B12 yang lebih besar daripada 200, gunakan formula ini:

=SUMIF(B2:B12, ">200", B2:B12)

Sila perhatikan sintaks yang betul dari kriteria: nombor yang diawali dengan pengendali perbandingan, dan keseluruhan pembinaan tertutup dalam tanda petikan.

Atau, anda boleh menaip nombor di beberapa sel, dan menggabungkan pengendali perbandingan dengan rujukan sel:

=SUMIF(B2:B12, ">"&E1, B2:B12)

Anda juga boleh memasukkan kedua -dua pengendali dan nombor perbandingan dalam sel -sel yang berasingan, dan menggabungkan sel -sel tersebut: =SUMIF(B2:B12, E1&F1)

Dengan cara yang sama, anda boleh menggunakan pengendali logik lain seperti:

Jumlah jika lebih besar daripada atau sama dengan 200:

=SUMIF(B2:B12, ">=200")

Jumlah jika kurang daripada 200:

=SUMIF(B2:B12, "

Jumlah jika kurang daripada atau sama dengan 200:

=SUMIF(B2:B12, "

Jumlah jika sama dengan

Untuk jumlah nombor yang sama dengan nombor tertentu, anda boleh menggunakan tanda kesamaan (=) bersama -sama dengan nombor atau menghilangkan tanda kesamaan dan hanya memasukkan nombor dalam hujah kriteria .

Sebagai contoh, untuk menambah jumlah dalam lajur B yang kuantiti dalam lajur C adalah sama dengan 10, gunakan mana -mana formula di bawah:

=SUMIF(C2:C12, 10, B2:B12)

atau

=SUMIF(C2:C12, "=10", B2:B12)

atau

=SUMIF(C2:C12, F1, B2:B12)

Di mana F1 adalah sel dengan kuantiti yang diperlukan.

Jumlah jika tidak sama dengan

Untuk jumlah nombor selain daripada nombor yang ditentukan, gunakan yang tidak sama dengan pengendali ().

Dalam contoh kami, untuk menambah jumlah dalam lajur B yang mempunyai kuantiti kecuali 10 dalam lajur C, pergi dengan salah satu formula ini:

=SUMIF(C2:C12, "10", B2:B12)

=SUMIF(C2:C12, ""&F1, B2:B12)

Tangkapan skrin di bawah menunjukkan hasilnya:

Formula Sumif Helaian Google untuk Tarikh

Untuk nilai jumlah yang sesuai berdasarkan kriteria tarikh, anda juga menggunakan pengendali perbandingan seperti yang ditunjukkan dalam contoh di atas. Titik utama ialah tarikh harus dibekalkan dalam format yang dapat difahami oleh lembaran Google.

Sebagai contoh, jumlah jumlah dalam B2: B12 untuk tarikh penghantaran sebelum 11-Apr-2024, membina kriteria dalam salah satu cara ini:

=SUMIF(C2:C12, "

=SUMIF(C2:C12, "

=SUMIF(C2:C12, "

Di mana F1 adalah tarikh sasaran:

Sekiranya anda ingin mengesan sel -sel berdasarkan tarikh hari ini , termasuk fungsi hari ini () dalam hujah kriteria .

Sebagai contoh, mari buat formula yang menambah jumlah untuk penghantaran hari ini:

=SUMIF(C2:C12, TODAY(), B2:B12)

Mengambil contoh lagi, kita dapat mencari sejumlah penghantaran masa lalu dan masa depan:

Sebelum hari ini: =SUMIF(C2:C12, "

Selepas hari ini: =SUMIF(C2:C12, ">"&TODAY(), B2:B12)

Jumlah berdasarkan sel kosong atau tidak kosong

Dalam banyak situasi, anda mungkin perlu jumlah nilai dalam lajur tertentu jika sel yang sama dalam lajur lain adalah atau tidak kosong.

Untuk ini, gunakan salah satu kriteria berikut dalam formula Sumif Helaian Google anda:

Jumlah jika kosong:

• "=" kepada sel -sel yang benar -benar kosong.
• "" Untuk jumlah sel kosong termasuk yang mengandungi rentetan panjang sifar.

Jumlah jika tidak kosong:

• "" Untuk menambah sel yang mengandungi sebarang nilai, termasuk rentetan panjang sifar.

Sebagai contoh, untuk merangkum jumlah yang mana tarikh penghantaran ditetapkan (sel dalam lajur c tidak kosong ), gunakan formula ini:

=SUMIF(C5:C15, "", B5:B15)

Untuk mendapatkan jumlah jumlah tanpa tarikh penghantaran (sel dalam lajur C kosong ), gunakan yang satu ini:

=SUMIF(C5:C15, "", B5:B15)

Helaian Google sumif dengan pelbagai kriteria (atau logik)

Fungsi SUMIF dalam Helaian Google direka untuk menambah nilai berdasarkan hanya satu kriteria. Sebagai jumlah dengan pelbagai kriteria, anda boleh menambah dua atau lebih fungsi SUMIF bersama -sama.

Sebagai contoh, jumlah epal dan jumlah oren , menggunakan formula ini:

=SUMIF(A2:A12, "apples", B2:B12) SUMIF(A2:A12, "oranges", B2:B12)

Atau, letakkan nama item dalam dua sel berasingan, katakan E1 dan E2, dan gunakan setiap sel tersebut sebagai kriteria:

=SUMIF(A2:A12, E1, B2:B12) SUMIF(A2:A12, E2, B2:B12)

Sila ambil perhatian bahawa formula ini berfungsi seperti SUMIF dengan atau logik - ia merangkumi nilai jika sekurang -kurangnya salah satu kriteria yang ditentukan dipenuhi.

Dalam contoh ini, kami menambah nilai dalam lajur B jika lajur A sama dengan "epal" atau "oren". Dalam erti kata lain, sumif () sumif () berfungsi seperti formula pseudo berikut (bukan yang sebenar, ia hanya menunjukkan logik!): Sumif (a: a, "epal" atau "oren", b: b).

Sekiranya anda mencari jumlah yang jelas dan logik , iaitu menambah nilai apabila semua kriteria yang ditentukan dipenuhi, gunakan fungsi Sumifs Helaian Google.

Google Sheets Sumif - Perkara Yang Perlu Diingat

Sekarang anda tahu kacang -kacangan dan bolt fungsi Sumif di Helaian Google, mungkin idea yang baik untuk membuat ringkasan ringkas tentang apa yang telah anda pelajari.

1. Sumif hanya dapat menilai satu syarat

Sintaks fungsi SUMIF membolehkan hanya satu julat , satu kriteria dan satu sum_range . Jumlahnya dengan pelbagai kriteria , sama ada menambah beberapa fungsi SUMIF bersama -sama (atau logik) atau gunakan formula SUMIFS (dan logik).

2. Fungsi sumif adalah kes-tidak sensitif

Jika anda mencari formula Sumif yang sensitif kes yang boleh membezakan antara huruf besar dan huruf kecil, gunakan Sumif dalam kombinasi dengan ArrayFormula dan cari seperti yang ditunjukkan dalam contoh ini.

3. Bekalan rangkaian bersaiz sama dan sum_range

Malah, hujah sum_range hanya menentukan sel paling kiri atas julat hingga jumlah, kawasan yang tinggal ditakrifkan oleh dimensi argumen julat .

Untuk meletakkannya secara berbeza, Sumif (A1: A10, "Apples", B1: B10) dan Sumif (A1: A10, "Apples", B1: B100) akan kedua -dua nilai jumlah dalam julat B1: B10 kerana ia adalah saiz yang sama seperti julat (A1: A10).

Jadi, walaupun anda tersilap membekalkan julat jumlah yang salah, Helaian Google masih akan mengira formula anda dengan betul, dengan syarat sel kiri atas SUM_RANGE adalah betul.

Yang mengatakan, ia masih disyorkan untuk menyediakan rangkaian bersaiz yang sama dan sum_range untuk mengelakkan kesilapan dan mencegah masalah ketidakkonsistenan.

4. Fikiran sintaks kriteria sumif

Untuk formula Sumif Helaian Google anda berfungsi dengan betul, nyatakan kriteria dengan cara yang betul:

• Jika kriteria termasuk teks , watak wildcard atau pengendali logik diikuti dengan nombor, teks atau tarikh, melampirkan kriteria dalam tanda petikan. Contohnya:

  =SUMIF(A2:A10, "apples", B2:B10)

  =SUMIF(A2:A10, "*", B2:B10)

  =SUMIF(A2:A10, ">5")

  =SUMIF(A5:A10, "apples", B5:B10)

• Jika kriteria termasuk pengendali logik dan rujukan sel atau fungsi lain, gunakan tanda petikan untuk memulakan rentetan teks dan ampersand (&) untuk menyusun dan menyelesaikan rentetan. Contohnya:

  =SUMIF(A2:A10, ">"&B2)

  =SUMIF(A2:A10, ">"&TODAY(), B2:B10)

5. Kunci berkisar dengan rujukan sel mutlak jika diperlukan

Jika anda merancang untuk menyalin atau memindahkan formula SUMIF anda pada titik kemudian, menetapkan julat dengan menggunakan rujukan sel mutlak (dengan tanda $) seperti dalam Sumif ($ A $ 2: $ A $ 10, "Apples", $ B $ 2: $ B $ 10).

Inilah cara anda menggunakan fungsi SUMIF dalam Helaian Google. Untuk melihat dengan lebih dekat pada formula yang dibincangkan dalam tutorial ini, anda dialu -alukan untuk membuka sampel SUMIF Google Sheet kami. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Spreadsheet dengan contoh formula

Contoh spreadsheet Sumif

Atas ialah kandungan terperinci Sumif di Helaian Google dengan contoh formula. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!