הפונקציה COUNTIF של Excel מאפשרת לך לספור את מספר התאים העומדים בקריטריונים או תנאים ספציפיים בטווח הנתון.
פונקציית COUNTIF היא אחת הפונקציות הסטטיסטיות באקסל שהיא שילוב של פונקציות COUNT ו-IF או הפונקציה COUNTA. בשימוש בפורמולה, הפונקציה סופרת את מספר התאים התואמים קריטריונים או תנאים ספציפיים באותם טווחים או מרובים. הפונקציה COUNTIF עוזרת לספור תאים המכילים טקסט, מספרים או תאריכים העומדים בקריטריונים ספציפיים.
אתה יכול לספור תאים באמצעות פונקציות COUNTIF או COUNTIFS ב- Excel. ההבדל בין פונקציות COUNTIF ו-COUNTIFS הוא ש-COUNTIF משמש לספירת תאים העומדים בקריטריון אחד בטווח אחד, בעוד ש-COUNTIFS סופרת תאים הממלאים מספר תנאים באותו טווח או במספר טווחים.
מאמר זה ידגים לך כיצד להשתמש בשתי הפונקציות COUNTIF ו-COUNTIFS באקסל.
פונקציית COUNTIF של Excel
הפונקציה COUNTIF מאפשרת לך לבצע ספירת נתונים על סמך קריטריון או תנאי ספציפיים. התנאי המשמש בפונקציה עובד עם אופרטורים לוגיים (, , =, >=, <=) ותווים כלליים (*, ?) עבור התאמה חלקית.
תחביר של פונקציית COUNTIF
המבנה של פונקציית COUNTIF הוא:
=COUNTIF(טווח,קריטריונים)פרמטרים:
טווח– טווח התאים שיש לספור.קריטריונים– התנאי קובע אילו תאים צריכים להיכלל בספירה בטווח שצוין. קריטריונים יכולים להיות ערך מספרי, טקסט, הפניה לכתובת תא או משוואה.
שימוש בפונקציית COUNTIF לספירת ערכים מספריים
כפי שדיברנו למעלה, הקריטריונים (ארגומנט שני) בפונקציה COUNTIF מגדירים את התנאי שאומר לפונקציה אילו תאים לספור.
פונקציה זו עוזרת לך לספור את מספר התאים עם ערכים העומדים בתנאים לוגיים כגון שווה, גדול מ-, קטן מ- או לא שווה לערך שצוין וכו'.
בדוגמה למטה, הנוסחה סופרת תאים המכילים ערך השווה ל-5 (קריטריונים). אתה יכול להוסיף ישירות '5 בנוסחה או להשתמש בהתייחסות לכתובת התא בעלת הערך (תא D2 בדוגמה למטה).
=COUNTIF(B2:B11,D2)
הנוסחה לעיל סופרת את מספר התאים בטווח התאים (B2:B11) המכילים את הערך השווה לערך בתא D2.
הנוסחה הבאה סופרת את התאים בעלי ערך קטן מ-5.
=COUNTIF(B2:B11,"<5")האופרטור הקטן מ-(<) אומר לנוסחה לספור תאים עם ערך קטן מ-'5' בטווח B2:B11. בכל פעם שאתה משתמש באופרטור במצב, הקפד לכלול אותו במירכאות כפולות ("").
לפעמים כשרוצים לספור את התאים על ידי בחינתם מול קריטריון (ערך) בתא. במקרים כאלה, צור קריטריון על ידי הצטרפות לאופרטור והפניה לתא. כאשר אתה עושה זאת, עליך לכלול את אופרטור ההשוואה במירכאות כפולות (""), ולאחר מכן למקם אמפרסנד (&) בין אופרטור ההשוואה להפניה לתא.
=COUNTIF(B2:B11,">="&D2)
התמונה למטה מציגה כמה נוסחאות לדוגמה ואת התוצאה שלהן.
שימוש בפונקציית COUNTIF לספירת ערכי טקסט
כדי לספור תאים המכילים מחרוזות טקסט מסוימות, השתמש במחרוזת הטקסט הזו כארגומנט הקריטריונים או בתא שמכיל מחרוזת טקסט. לדוגמה, בטבלה למטה, אם נרצה לספור את כל התאים בטווח (B21:D27) עם ערך הטקסט בתא B21 (sam), נוכל להשתמש בנוסחה הבאה:
=COUNTIF(B21:D27,B21)
כפי שדיברנו קודם, נוכל להשתמש בטקסט 'sam' ישירות בנוסחה או להשתמש בהפניה לתא שיש לה את הקריטריונים (B21). מחרוזת טקסט צריכה תמיד להיות מוקפת במירכאות כפולות ("") כאשר היא משמשת בנוסחה באקסל.
=COUNTIF(B21:D27,"sam")
כדי לספור תאים שאינם מכילים טקסט שצוין, השתמש בנוסחה הבאה:
=COUNTIF(B21:D27,""&B21)הקפד לצרף את ה'לא שווה ל' "" מפעיל במירכאות כפולות.
אם אתה משתמש בטקסט 'sam' ישירות בנוסחה, עליך לצרף את האופרטור '' ואת מחרוזת הטקסט יחד ("סאם") במירכאות כפולות.
=COUNTIF(B21:D27,"sam") 
שימוש בתווים כלליים בפונקציית COUNTIF של Excel (התאמה חלקית)
אתה יכול להשתמש בנוסחת COUNTIF עם תווים כלליים כדי לספור תאים המכילים מילה, ביטוי או אותיות ספציפיים. ישנם שלושה תווים כלליים שבהם אתה יכול להשתמש בפונקציה COUNTIF של Excel:
*(כוכבית) - הוא משמש לספירת תאים עם כל מספר של תווים/אותיות התחלה וסיום. (למשל, St* יכול להיות סטארק, Stork, Stacks וכו'.?(סימן שאלה) - הוא משמש כדי למצוא תאים עם כל תו בודד. (למשל, St?rk יכול להיות Stark או Stork.~(tilde) - הוא משמש כדי למצוא ולספור את מספר התאים המכילים סימן שאלה או תו כוכבית (~, *, ?) בטקסט.
ספירת תאים שמתחילים או מסתיימים עם תווים מסוימים
כדי לספור את התאים שמתחילים או מסתיימים בטקסט ספציפי עם כל מספר של תווים אחרים בתא, השתמש בתו כללי של כוכבית (*) בארגומנט השני של הפונקציה COUNTIF.
השתמש בנוסחה לדוגמה הבאה:
=COUNTIF(A1:A10,"A*") - לספור תאים שמתחיל ב-"A".
=COUNTIF(A19:A28,"*er") - לספור את מספר התאים המסתיימים בתווים "er".
=COUNTIF(A2:A12,"*QLD*") - לספירת התאים המכילים את הטקסט "QLD" בכל מקום במחרוזת הטקסט.
א ? מייצג בדיוק תו אחד, השתמש בתו כללי זה בפונקציה COUNTIF למטה כדי לספור את מספר התאים המכילים בדיוק תו +1 כאשר '?' משמש.
=COUNTIF(A1:A10,"Par?s")
ספירת תאים ריקים ולא ריקים עם פונקציית COUNTIF
הנוסחה COUNTIF מועילה גם כשמדובר בספירת מספר התאים הריקים או הלא ריקים בטווח נתון.
ספירת תאים לא ריקים
אם ברצונך לספור רק תאים המכילים ערכי 'טקסט', השתמש בנוסחה שלהלן. נוסחה זו מחשיבה תאים עם תאריכים ומספרים כתאים ריקים ולא תכלול אותם בספירה.
=COUNTIF(A1:B12,"*")התו הכללי * מתאים לערכי הטקסט בלבד ומחזיר את הספירה של כל ערכי הטקסט בטווח הנתון.
אם ברצונך לספור את כל התאים שאינם ריקים בטווח נתון, נסה את הנוסחה הזו:
=COUNTIF(A1:B12,"")
ספור תאים ריקים
אם ברצונך לספור תאים ריקים בטווח מסוים, השתמש בפונקציה COUNTIF עם ה- * תו כללי ו אופרטור בארגומנט הקריטריונים לספירת תאים ריקים.
נוסחה זו סופרת תאים שאינם מכילים ערכי טקסט:
=COUNTIF(A1:B12,""&"*")מאז * תואם כללי עם ערך טקסט כלשהו, הנוסחה שלמעלה תספור את כל התאים שאינם שווים להם *. זה סופר תאים עם תאריכים ומספרים גם כחסר.
כדי לספור את כל החסר (כל סוגי הערכים):
=COUNTIF(A1:B12,"")פונקציה זו סופרת רק תאים ריקים בטווח.
שימוש בפונקציית COUNTIF לספירת תאריכים
אתה יכול לספור תאים עם תאריכים (כמו שעשית עם קריטריוני מספר) העומדים בתנאי לוגי או בתאריך או התאריך שצוינו בתא ההפניה.
כדי לספור את התאים המכילים את התאריך שצוין (05-05-2020), נשתמש בנוסחה זו:
=COUNTIF(B2:B10,"05-05-2020")
אתה יכול גם לציין תאריך בפורמטים שונים כקריטריונים בפונקציית COUNTIF כפי שמוצג להלן:
אם ברצונך לספור תאים המכילים תאריכים לפני או אחרי תאריך מסוים, השתמש באופרטורים הקטן מ-(לפני) או גדול מ-(אחרי) יחד עם התאריך הספציפי או הפניה לתא.
=COUNTIF(B2:B10,">=05/05/2020")
ניתן גם להשתמש בהפניה לתא המכילה תאריך על ידי שילובו עם האופרטור (במרכאות כפולות).
כדי לספור את מספר התאים בטווח A2:A14 עם תאריך לפני התאריך ב-E3, השתמש בנוסחה שלהלן, כאשר האופרטור גדול מ-(<) פירושו לפני התאריך ב-E3.
=COUNTIF(A2:A14,"<"&E3)
כמה נוסחאות לדוגמה והתוצאה שלהן:
תאריך ספירה מבוסס על תאריך נוכחי
אתה יכול לשלב את הפונקציה COUNTIF עם פונקציות תאריך ספציפיות של Excel, כלומר TODAY() כדי לספור תאים בעלי התאריך הנוכחי.
=COUNTIF(A2:A14,">"&TODAY())פונקציה זו סופרת את כל התאריכים מהיום בטווח (A2:A14).
ספירת תאריכים בין טווח תאריכים ספציפי
אם ברצונך לספור את כל התאריכים בין שני תאריכים, עליך להשתמש בשני קריטריונים בנוסחה.
אנו יכולים לעשות זאת באמצעות שתי שיטות: פונקציות COUNTIF ו-COUNTIFS.
שימוש בפונקציית COUNTIF של Excel
עליך להשתמש בשתי פונקציות COUNTIF כדי לספור את כל התאריכים בין שני התאריכים שצוינו.
כדי לספור את התאריכים בין '09-02-2020' ל-'20-08-2021', השתמש בנוסחה זו:
=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">20-08-2021")נוסחה זו מוצאת תחילה את מספר התאים שיש להם תאריך אחרי 2 בפברואר ומפחיתה את ספירת התאים עם תאריכים אחרי 20 באוגוסט. כעת נקבל את ה-No. של תאים שיש להם תאריכים שמגיעים אחרי 2 בפברואר ובתאריך או לפני 20 באוגוסט (הספירה היא 9).
אם אינך רוצה שהנוסחה תיספר גם ב-2 בפברואר וגם ב-20 באוגוסט, השתמש בנוסחה הזו במקום זאת:
=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">=20-08-2021")פשוט החלף את האופרטור '>' ב-'>=' בקריטריונים השניים.
שימוש בפונקציית COUNTIFS של Excel
גם הפונקציה COUNTIFS תומכת במספר קריטריונים, ובניגוד לפונקציית COUNTIF, היא סופרת את התאים רק לאחר שכל התנאים מתקיימים. אם ברצונך לספור תאים עם כל התאריכים בין שני תאריכים שצוינו, הזן את הנוסחה הזו:
=COUNTIFS(A2:A14,">"&A11,A2:A14,"<"&A10)
אם ברצונך לכלול את התאריכים שצוינו בספירה, השתמש באופרטורים '>=' ו-'<='. הנה, לך עם הנוסחה הזו:
=COUNTIFS(A2:A14,">=09-02-2020",A2:A14,"<=20-08-2021")השתמשנו בתאריך ישירות בקריטריונים במקום בהפניה לתא עבור דוגמה זו.
כיצד לטפל ב-COUNTIF וב-COUNTIFS עם קריטריונים מרובים באקסל
הפונקציה COUNTIF משמשת בעיקר לספירת תאים עם קריטריונים בודדים (תנאי) בטווח אחד. אבל אתה עדיין יכול להשתמש ב-COUNTIF כדי לספור תאים התואמים למספר תנאים באותו טווח. עם זאת, ניתן להשתמש בפונקציה COUNTIFS כדי לספור תאים העומדים במספר תנאים בטווחים זהים או שונים.
כיצד לספור מספרים בטווח
אתה יכול לספור תאים המכילים מספרים בין שני המספרים שצוינו באמצעות שתי פונקציות: COUNTIF ו-COUNTIFS.
COUNTIF כדי לספור מספרים בין שני מספרים
אחד השימושים הנפוצים בפונקציית COUNTIF עם מספר קריטריונים הוא ספירת המספרים בין שני מספרים שצוינו, למשל. כדי לספור מספרים גדולים מ-10 אך פחות מ-50. כדי לספור מספרים בטווח, חבר שתי פונקציות COUNTIF או יותר יחד בנוסחה אחת. תן לנו להראות לך איך.
נניח שאתה רוצה לספור תאים בטווח B2:B9 שבהם הערך גדול מ-10 וקטן מ-21 (לא כולל 10 ו-21), עבור עם הנוסחה הזו:
=COUNTIF(B2:B14,">10")-COUNTIF(B2:B14,">=21")ההבדל בין שני מספרים נמצא על ידי הפחתת נוסחה אחת מהשנייה. הנוסחה הראשונה סופרת את המספרים הגדולים מ-10 (שהם 7), הנוסחה השנייה מחזירה את ספירת המספרים הגדולים או שווה ל-21 (שהם 4), והתוצאה של הנוסחה השנייה מופחתת מהנוסחה הראשונה (7). -4) כדי לקבל את ספירת המספרים בין שני מספרים (3).
אם ברצונך לספור תאים עם מספר גדול מ-10 וקטן מ-21 בטווח B2:B14, כולל המספרים 10 ו-21, השתמש בנוסחה זו:
=COUNTIF(B2:B14,">=10")-COUNTIF(B2:B14,">21")
COUNTIFS כדי לספור מספרים בין 2 מספרים
כדי לספור מספרים בין 10 ל-21 (למעט 10 ו-21) הכלולים בתאים B2 עד B9, השתמש בנוסחה זו:
=COUNTIFS(B2:B14,">10",B2:B14,"<21")
כדי לכלול 10 ו-21 בספירה, פשוט השתמש ב'גדול מ- או שווה ל' (>=) במקום 'גדול מ' ו'קטן מ- או שווה ל' (<=) במקום באופרטורים 'קטן מ' בנוסחאות .
COUNTIFS לספירת תאים עם קריטריונים מרובים (וקריטריונים)
הפונקציה COUNTIFS היא המקבילה ברבים של הפונקציה COUNTIF הסופרת תאים על סמך שני קריטריונים או יותר באותם טווחים או מרובים. זה ידוע בשם 'AND logic' מכיוון שהפונקציה מיועדת לספירת תאים רק כאשר כל התנאים הנתונים נכונים.
לדוגמה, אנו רוצים לברר כמה פעמים (ספירת התאים) אותו לחם (ערך בעמודה א') נמכר פחות מ-5 (ערך בעמודה ג').
נוכל להשתמש בנוסחה הזו:
=COUNTIFS(A2:A14,"Bread",C2:C14,"<5")
COUNTIF כדי לספור תאים עם קריטריונים מרובים (או קריטריונים)
אם ברצונך לספור את מספר התאים העומדים במספר קריטריונים באותו טווח, חבר שתי פונקציות COUNTIF או יותר יחד. לדוגמה, אם ברצונך לגלות כמה פעמים חוזרים על 'לחם' או 'גבינה' בטווח שצוין (A2:A14), השתמש בנוסחה הבאה:
=COUNTIF(A2:A14,"Bread")+COUNTIF(A2:A14,"Cheese")נוסחה זו סופרת תאים שעבורם לפחות אחד מהתנאים הוא TRUE. לכן זה נקרא 'OR logic'.
אם ברצונך להעריך יותר מקריטריון אחד בכל אחת מהפונקציות, עדיף להשתמש ב-COUNTIFS במקום ב-COUNTIF. בדוגמה שלהלן, אנו רוצים לקבל את הספירה של סטטוס "מוזמן" ו"נמסר" עבור 'לחם', אז נשתמש בנוסחה הזו:
=COUNTIFS(A2:A14,"Bread",C2:C14,"Ordered")+COUNTIFS(A2:A14,"Bread",C2:C14,"Delivered")
אנו מקווים שזה קל, אבל מדריך ארוך ייתן לך מושג על אופן השימוש בפונקציות COUNTIF ו-COUNTIF באקסל.