כיצד להשתמש בקריטריונים מרובים ב- Excel COUNTIF ו- COUNTIFS

ל- Excel יש פונקציות רבות שבהן משתמש צריך לציין קריטריון בודד או מרובה כדי לקבל את התוצאה. לדוגמה, אם ברצונך לספור תאים המבוססים על מספר קריטריונים, תוכל להשתמש בפונקציות COUNTIF או COUNTIFS ב- Excel.

הדרכה זו מכסה דרכים שונות לשימוש בקריטריונים בודדים או מרובים בפונקציה COUNTIF ו- COUNTIFS ב- Excel.

בעוד שאני מתמקד בעיקר בפונקציות COUNTIF ו- COUNTIFS במדריך זה, ניתן להשתמש בכל הדוגמאות הללו גם בפונקציות Excel אחרות המקבלות מספר קריטריונים כקלטים (כגון SUMIF, SUMIFS, AVERAGEIF ו- AVERAGEIFS).

מבוא לפונקציות COUNTIF ו- COUNTIFS של Excel

בואו נתפוס תחילה שימוש בפונקציות COUNTIF ו- COUNTIFS ב- Excel.

פונקציית COUNTIF של Excel (לוקחת קריטריונים בודדים)

פונקציית COUNTIF של Excel מתאימה ביותר למצבים שבהם אתה רוצה לספור תאים על בסיס קריטריון אחד. אם אתה רוצה לספור על סמך מספר קריטריונים, השתמש בפונקציה COUNTIFS.

תחביר

= COUNTIF (טווח, קריטריונים)

טענות קלט

• טווח - טווח התאים שברצונך לספור.
• קריטריונים - הקריטריונים שיש להעריך מול טווח התאים כדי לספור תא.

פונקציית COUNTIFS של Excel (דורשת קריטריונים מרובים)

הפונקציה Excel COUNTIFS מתאימה ביותר למצבים שבהם אתה רוצה לספור תאים על סמך מספר קריטריונים.

תחביר

= COUNTIFS (קריטריונים_טווח 1, קריטריונים 1, [קריטריונים_טווח 2, קריטריונים 2] …)

טענות קלט

• טווח קריטריונים 1 - טווח התאים שעבורו אתה רוצה להעריך מול קריטריונים 1.
• קריטריונים 1 - הקריטריונים שברצונך להעריך עבור criteria_range1 כדי לקבוע אילו תאים לספור.
• [קריטריונים_טווח 2] - טווח התאים שעבורו אתה רוצה להעריך מול קריטריונים 2.
• [קריטריונים 2] - הקריטריונים שברצונך להעריך עבור criteria_range2 כדי לקבוע אילו תאים לספור.

כעת נסתכל על כמה דוגמאות לשימוש במספר קריטריונים בפונקציות COUNTIF ב- Excel.

שימוש בקריטריונים NUMBER בפונקציות COUNTIF של Excel

מספר 1 תאים כאשר הקריטריונים שווים לערך

כדי לקבל את ספירת התאים שבהם ארגומנט הקריטריונים שווה לערך שצוין, תוכל להזין ישירות את הקריטריונים או להשתמש בהפניה לתא המכילה את הקריטריונים.

להלן דוגמה שבה אנו סופרים את התאים המכילים את המספר 9 (כלומר ארגומנט הקריטריונים שווה ל -9). להלן הנוסחה:

= COUNTIF ($ B $ 2: $ B $ 11, D3)

בדוגמה לעיל (בתמונה), הקריטריונים נמצאים בתא D3. תוכל גם להזין את הקריטריונים ישירות בנוסחה. לדוגמה, תוכל גם להשתמש ב:

= COUNTIF ($ B $ 2: $ B $ 11,9)

מספר 2 לספור תאים כאשר הקריטריונים גדולים יותר מאשר ערך

כדי לקבל את ספירת התאים עם ערך גדול מערך שצוין, אנו משתמשים באופרטור הגדול יותר (">"). נוכל להשתמש בו ישירות בנוסחה או להשתמש בהפניה לתא שיש לה את הקריטריונים.

בכל פעם שאנו משתמשים באופרטור בקריטריונים ב- Excel, עלינו להכניס אותו למרכאות כפולות. לדוגמה, אם הקריטריונים גדולים מ -10, עלינו להזין "> 10" כקריטריונים (ראו תמונה למטה):

להלן הנוסחה:

= COUNTIF ($ B $ 2: $ B $ 11, "> 10 ″)

תוכל גם להכיל את הקריטריונים בתא ולהשתמש בהפניה לתא כקריטריונים. במקרה זה, אינך צריך לשים את הקריטריונים במרכאות כפולות:

= COUNTIF ($ B $ 2: $ B $ 11, D3)

יכול להיות גם מקרה שבו אתה רוצה שהקריטריונים יהיו בתא, אך אינך רוצה אותו עם המפעיל. לדוגמה, ייתכן שתרצה שלתא D3 יהיה המספר 10 ולא> 10.

במקרה זה, עליך ליצור ארגומנט קריטריונים שהוא שילוב של אופרטור והתייחסות לתא (ראה תמונה למטה):

= COUNTIF ($ B $ 2: $ B $ 11, ">" & D3)

הערה: כאשר אתה משלב אופרטור והתייחסות לתא, האופרטור תמיד נמצא במרכאות כפולות. לאופרטור ולהפניה לתא מצטרף אמפרסנד (&).

#3 ספירת תאים כאשר הקריטריונים הם פחות מאשר ערך

כדי לקבל את ספירת התאים עם ערך פחות מערך שצוין, אנו משתמשים באופרטור פחות מ ("<"). נוכל להשתמש בו ישירות בנוסחה או להשתמש בהפניה לתא שיש לה את הקריטריונים.

בכל פעם שאנו משתמשים באופרטור בקריטריונים ב- Excel, עלינו להכניס אותו למרכאות כפולות. לדוגמה, אם הקריטריון הוא שהמספר צריך להיות קטן מ -5, עלינו להזין את "<5" כקריטריונים (ראו תמונה למטה):

= COUNTIF ($ B $ 2: $ B $ 11, "<5 ″)

תוכל גם להכיל את הקריטריונים בתא ולהשתמש בהפניה לתא כקריטריונים. במקרה זה, אינך צריך לשים את הקריטריונים במרכאות כפולות (ראה תמונה למטה):

= COUNTIF ($ B $ 2: $ B $ 11, D3)

כמו כן, יכול להיות מקרה שבו אתה רוצה שהקריטריונים יהיו בתא, אך אינך רוצה אותו עם המפעיל. לדוגמה, ייתכן שתרצה שלתא D3 יהיה המספר 5 ולא <5.

במקרה זה, עליך ליצור ארגומנט קריטריונים שהוא שילוב של אופרטור והתייחסות לתא:

= COUNTIF ($ B $ 2: $ B $ 11, "<" & D3)

הערה: כאשר אתה משלב אופרטור והתייחסות לתא, האופרטור תמיד נמצא במרכאות כפולות. לאופרטור ולהפניה לתא מצטרף אמפרסנד (&).

#4 ספירת תאים עם קריטריונים מרובים - בין שני ערכים

כדי לקבל ספירת ערכים בין שני ערכים, עלינו להשתמש בקריטריונים מרובים בפונקציה COUNTIF.

להלן שתי שיטות לעשות זאת:

שיטה 1: שימוש בפונקציית COUNTIFS

הפונקציה COUNTIFS יכולה להתמודד עם קריטריונים מרובים כארגומנטים וסופרת את התאים רק כאשר כל הקריטריונים נכונים. כדי לספור תאים עם ערכים בין שני ערכים שצוינו (נניח 5 ו -10), נוכל להשתמש בפונקציה COUNTIFS הבאה:

= COUNTIFS ($ B $ 2: $ B $ 11, "> 5 ″, $ B $ 2: $ B $ 11," <10 ″)

הערה: הנוסחה שלמעלה אינה סופרת תאים המכילים 5 או 10. אם ברצונך לכלול תאים אלה, השתמש באופרטורים הגדולים (= =) ופחות משווים (<=). להלן הנוסחה:

= COUNTIFS ($ B $ 2: $ B $ 11, "> = 5 ″, $ B $ 2: $ B $ 11," <= 10 ″)

תוכל גם להכיל קריטריונים אלה בתאים ולהשתמש בהפניה לתא כקריטריונים. במקרה זה, אינך צריך לשים את הקריטריונים במרכאות כפולות (ראה תמונה למטה):

ניתן גם להשתמש בשילוב של הפניות לתאים ואופרטורים (כאשר המפעיל מוזן ישירות בנוסחה). כאשר אתה משלב אופרטור והתייחסות לתא, האופרטור תמיד נמצא במרכאות כפולות. לאופרטור ולהפניה לתא מצטרף אמפרסנד (&).

שיטה 2: שימוש בשתי פונקציות COUNTIF

אם יש לך מספר קריטריונים, תוכל להשתמש ב- COUNTIFS או ליצור שילוב של פונקציות COUNTIF. הנוסחה להלן גם תעשה את אותו הדבר:

= COUNTIF ($ B $ 2: $ B $ 11, "> 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,"> 10 ″)

בנוסחה שלעיל, אנו מוצאים תחילה את מספר התאים שיש להם ערך גדול מ -5 ואנו מפחיתים את ספירת התאים עם ערך גדול מ- 10. זה ייתן לנו את התוצאה כ -5 (שזה מספר התאים שיש להם ערכים יותר מ -5 ופחות משווים ל 10).

אם אתה רוצה שהנוסחה תכלול גם 5 וגם 10, השתמש במקום זאת בנוסחה הבאה:

= COUNTIF ($ B $ 2: $ B $ 11, "> = 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,"> 10 ″)

אם ברצונך שהנוסחה תכלול הן '5' והן '10' מהספירה, השתמש בנוסחה הבאה:

= COUNTIF ($ B $ 2: $ B $ 11, "> = 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,"> 10 ″)-COUNTIF ($ B $ 2: $ B $ 11,10)

אתה יכול להכיל קריטריונים אלה בתאים ולהשתמש בהפניות לתאים, או שאתה יכול להשתמש בשילוב של אופרטורים והתייחסויות לתאים.

שימוש בקריטריוני TEXT בפונקציות Excel

מספר 1 תאים כאשר הקריטריונים שווים לטקסט שצוין

כדי לספור תאים המכילים התאמה מדויקת של הטקסט שצוין, אנו יכולים פשוט להשתמש בטקסט זה כקריטריונים. לדוגמה, במערך הנתונים (המוצג להלן בתמונה), אם אני רוצה לספור את כל התאים עם השם ג'ו, אני יכול להשתמש בנוסחה שלהלן:

= COUNTIF ($ B $ 2: $ B $ 11, "ג'ו")

מכיוון שזו מחרוזת טקסט, אני צריך לשים את קריטריוני הטקסט במרכאות כפולות.

תוכל גם לקבל את הקריטריונים בתא ולאחר מכן להשתמש בהתייחסות התא הזו (כפי שמוצג להלן):

= COUNTIF ($ B $ 2: $ B $ 11, E3)

הערה: אתה יכול לקבל תוצאות שגויות אם יש רווחים מובילים/נגררים בטווח הקריטריונים או הקריטריונים. הקפד לנקות את הנתונים לפני השימוש בנוסחאות אלה.

מספר 2 תאים כאשר הקריטריונים אינם שווים לטקסט שצוין

בדומה למה שראינו בדוגמה לעיל, אתה יכול גם לספור תאים שאינם מכילים טקסט שצוין. לשם כך עלינו להשתמש באופרטור לא שווה לאופרטור ().

נניח שאתה רוצה לספור את כל התאים שאינם מכילים את השם JOE, הנה הנוסחה שתעשה זאת:

= COUNTIF ($ B $ 2: $ B $ 11, "ג'ו")

תוכל גם להכיל את הקריטריונים בתא ולהשתמש בהפניה לתא כקריטריונים. במקרה זה, אינך צריך לשים את הקריטריונים במרכאות כפולות (ראה תמונה למטה):

= COUNTIF ($ B $ 2: $ B $ 11, E3)

יכול להיות גם מקרה שבו אתה רוצה שהקריטריונים יהיו בתא אבל לא רוצים אותו עם המפעיל. לדוגמה, ייתכן שתרצה של תא D3 יהיה השם ג'ו ולא ג'ו.

במקרה זה, עליך ליצור ארגומנט קריטריונים שהוא שילוב של אופרטור והתייחסות לתא (ראה תמונה למטה):

= COUNTIF ($ B $ 2: $ B $ 11, "" E3)

כאשר אתה משלב אופרטור והתייחסות לתא, האופרטור תמיד נמצא במרכאות כפולות. לאופרטור ולהפניה לתא מצטרף אמפרסנד (&).

שימוש בקריטריוני DATE בפונקציות Excel COUNTIF ו- COUNTIFS

תאריך ושעה של חנות Excel כמספרים. כך שנוכל להשתמש בו באותו אופן בו אנו משתמשים במספרים.

מספר 1 תאים כאשר הקריטריונים שווים לתאריך שצוין

כדי לקבל את ספירת התאים המכילים את התאריך שצוין, נשתמש בשווה לאופרטור (=) יחד עם התאריך.

כדי להשתמש בתאריך, אני ממליץ להשתמש בפונקציית DATE, מכיוון שהוא נפטר מכל אפשרות לשגיאה בערך התאריך. כך, למשל, אם אני רוצה להשתמש בתאריך 1 בספטמבר 2015, אוכל להשתמש בפונקציה DATE כפי שמוצג להלן:

= DATE (2015,9,1)

נוסחה זו תחזיר את אותו התאריך למרות הבדלים אזוריים. לדוגמה, 01-09-2015 יהיה 1 בספטמבר 2015 על פי תחביר התאריך בארה"ב ו- 09 בינואר 2015 לפי תחביר התאריך בבריטניה. עם זאת, נוסחה זו תמיד תחזור ב -1 בספטמבר, 2105.

להלן הנוסחה לספירת מספר התאים המכילים את התאריך 09-09-2015:

= COUNTIF ($ A $ 2: $ A $ 11, DATE (2015,9,2))

מספר 2 תאים כאשר הקריטריונים הם לפני או אחרי לתאריך שצוין

כדי לספור תאים המכילים תאריך לפני או אחרי תאריך מוגדר, נוכל להשתמש באופרטורים הקטנים מ/גדולים מהאופרטורים.

לדוגמה, אם אני רוצה לספור את כל התאים המכילים תאריך שאחרי 02 בספטמבר 2015, אני יכול להשתמש בנוסחה:

= COUNTIF ($ A $ 2: $ A $ 11, ">" ותאריך (2015,9,2))

באופן דומה, אתה יכול גם לספור את מספר התאים לפני תאריך שצוין. אם ברצונך לכלול תאריך בספירה, השתמש באופרטור 'שווה' יחד עם אופרטור 'גדול מ/פחות מ'.

תוכל גם להשתמש בהתייחסות לתא המכילה תאריך. במקרה זה, עליך לשלב את האופרטור (בתוך מרכאות כפולות) עם התאריך באמצעות אמפרסנד (&).

ראה דוגמה למטה:

= COUNTIF ($ A $ 2: $ A $ 11, ">" & F3)

#3 ספירת תאים עם קריטריונים מרובים - בין שני תאריכים

כדי לקבל ספירת ערכים בין שני ערכים, עלינו להשתמש בקריטריונים מרובים בפונקציה COUNTIF.

אנו יכולים לעשות זאת באמצעות שתי שיטות - פונקציית COUNTIFS אחת או שתי פונקציות COUNTIF.

שיטה 1: שימוש בפונקציית COUNTIFS

הפונקציה COUNTIFS יכולה לקחת קריטריונים מרובים כארגומנטים ולספור את התאים רק כאשר כל הקריטריונים נכונים. כדי לספור תאים עם ערכים בין שני תאריכים שצוינו (למשל 2 בספטמבר ו -7 בספטמבר), נוכל להשתמש בפונקציה COUNTIFS הבאה:

= COUNTIFS ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,2), $ A $ 2: $ A $ 11, "<" & DATE (2015,9,7))

הנוסחה שלעיל אינה סופרת תאים המכילים את התאריכים שצוינו. אם ברצונך לכלול גם תאריכים אלה, השתמש באופרטורים העולים על שווים (> =) ופחות משווים (<=). להלן הנוסחה:

= COUNTIFS ($ A $ 2: $ A $ 11, "> =" & DATE (2015,9,2), $ A $ 2: $ A $ 11, "<=" & DATE (2015,9,7))

תוכל גם להכיל את התאריכים בתא ולהשתמש בהפניה לתא כקריטריונים. במקרה זה, לא תוכל לקבל את האופרטור עם התאריך בתאים. עליך להוסיף ידנית אופרטורים בנוסחה (במרכאות כפולות) ולהוסיף הפניה לתא באמצעות אמפרסנד (&). ראה את התמונה למטה:

= COUNTIFS ($ A $ 2: $ A $ 11, ">" & F3, $ A $ 2: $ A $ 11, "<" & G3)

שיטה 2: שימוש בפונקציות COUNTIF

אם יש לך מספר קריטריונים, תוכל להשתמש בפונקציה אחת של COUNTIFS או ליצור שילוב של שתי פונקציות COUNTIF. הנוסחה שלהלן תעשה גם את העבודה:

= COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,7))

בנוסחה שלעיל, אנו מוצאים תחילה את מספר התאים שיש להם תאריך אחרי ה -2 בספטמבר ואנו מפחיתים את ספירת התאים עם תאריכים לאחר ה -7 בספטמבר. הדבר יתן לנו את התוצאה כ -7 (שזה מספר התאים שיש להם תאריכים לאחר 2 בספטמבר ולפני 7 בספטמבר).

אם אינך רוצה שהנוסחה תספור את 2 בספטמבר ו -7 בספטמבר, השתמש במקום זאת בנוסחה הבאה:

= COUNTIF ($ A $ 2: $ A $ 11, "> =" & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ">" ותאריך (2015,9,7))

אם ברצונך להוציא את שני התאריכים מהספירה, השתמש בנוסחה הבאה:

= COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,7) -COUNTIF ($ A $ 2: $ 11 $, תאריך (2015,9,7)))

כמו כן, תוכל לקבל את תאריכי הקריטריונים בתאים ולהשתמש בהפניות לתאים (יחד עם אופרטורים במרכאות כפולות המצטרפות באמצעות אמפרסנד).

שימוש במאפייני WILDCARD בקריטריונים בפונקציות COUNTIF ו- COUNTIFS

יש שלוש תווים כלליים ב- Excel:

1. * (כוכבית) - הוא מייצג כל מספר תווים. לדוגמה, ex* יכול להיות אקסל, אקסל, דוגמא, מומחה וכו '.
2. ? (סימן שאלה) - הוא מייצג דמות אחת. לדוגמה, Tr? Mp יכול להיות טראמפ או טראמפ.
3. ~ (tilde) - הוא משמש לזיהוי תו תווים כלליים (~, *,?) בטקסט.

אתה יכול להשתמש בפונקציית COUNTIF עם תווים כלליים לספירת תאים כאשר פונקציית ספירה מובנית אחרת נכשלת. לדוגמה, נניח שיש לך מערך נתונים כפי שמוצג להלן:

כעת ניקח דוגמאות שונות:

מספר 1 מספר תאים המכילים טקסט

כדי לספור תאים עם טקסט, נוכל להשתמש בתו התווים * (כוכבית). מכיוון שכוכבית מייצגת מספר תווים כלשהו, ​​היא תספור את כל התאים שיש בהם טקסט כלשהו. להלן הנוסחה:

= COUNTIFS ($ C $ 2: $ C $ 11, "*")

הערה: הנוסחה שלמעלה מתעלמת מתאים המכילים מספרים, תאים ריקים וערכים לוגיים, אך אם תספור התאים מכילים אפוסטרוף (ומכאן שנראים ריקים) או תאים המכילים מחרוזת ריקה (= "") שאולי הוחזרו כ- חלק מנוסחה.

להלן הדרכה מפורטת על טיפול במקרים בהם יש מחרוזת ריקה או אפוסטרופה.

להלן הדרכה מפורטת לטיפול במקרים בהם יש מחרוזות ריקות או אפוסטרופים.

להלן סרטון המסביר תרחישים שונים של ספירת תאים עם טקסט בתוכו.

מספר 2 תאים שאינם ריקים

אם אתה חושב להשתמש בפונקציית COUNTA, תחשוב שוב.

נסה את זה וזה עלול להכשיל אותך. COUNTA תספור גם תא המכיל מחרוזת ריקה (המוחזר לעתים קרובות על ידי נוסחאות כ = ”” או כאשר אנשים נכנסים רק אפוסטרוף בתא). תאים המכילים מחרוזות ריקות נראים ריקים אך אינם, ולכן נספרים לפי הפונקציה COUNTA.

COUNTA תספור גם תא המכיל מחרוזת ריקה (המוחזר לעתים קרובות על ידי נוסחאות כ = ”” או כאשר אנשים נכנסים רק אפוסטרוף בתא). תאים המכילים מחרוזות ריקות נראים ריקים אך אינם, ולכן נספרים לפי הפונקציה COUNTA.

אז אם אתה משתמש בנוסחה = COUNTA (A1: A11), היא מחזירה 11, בעוד שהיא צריכה להחזיר 10.

להלן התיקון:

= COUNTIF ($ A $ 1: $ A $ 11, "?*")+COUNT ($ A $ 1: $ A $ 11)+SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11))

בואו להבין את הנוסחה הזו על ידי פירוק שלה:

• COUNTIF ($ N $ 8: $ N $ 18, "?*") - חלק זה של הנוסחה מחזיר 5. זה כולל כל תא שיש בו תו טקסט. א? מייצג תו אחד ו- * מייצג כל מספר תווים. מכאן שהצירוף?* בקריטריונים מאלץ לספור תאים שיש בהם לפחות תו טקסט אחד.
• COUNT ($ A $ 1: $ A $ 11) - זה סופר את כל התאים המכילים מספרים. בדוגמה שלמעלה היא מחזירה 3.
• SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11) - זה מונה את כל התאים המכילים ערכים לוגיים. בדוגמה למעלה הוא מחזיר 2.

#3 ספירת תאים המכילים טקסט ספציפי

נניח שאנחנו רוצים לספור את כל התאים שבהם שם נציג המכירות מתחיל ב- J. ניתן להשיג זאת בקלות באמצעות תו תווים בפונקציה COUNTIF. להלן הנוסחה:

= COUNTIFS ($ C $ 2: $ C $ 11, "J*")

הקריטריונים J* מציינים שהטקסט בתא צריך להתחיל ב- J ויכול להכיל כל מספר תווים.

אם ברצונך לספור תאים המכילים את האלף בית בכל מקום בטקסט, הקף אותו בכוכבית משני הצדדים. לדוגמה, אם ברצונך לספור תאים המכילים בתוכו את האלף בית "a", השתמש * a * כקריטריונים.

מאמר זה ארוך באופן יוצא דופן בהשוואה למאמרים אחרים שלי. מקווה שנהניתם. ספר לי את דעתך על ידי השארת הערה.

ייתכן שתמצא גם את הדרכות האקסל הבאות שימושיות:

• ספור את מספר המילים ב- Excel.
• ספירת תאים המבוססים על צבע הרקע ב- Excel.
• כיצד לסכם טור באקסל (5 דרכים פשוטות באמת)