לאקסל יש פונקציות רבות שבהן המשתמש צריך לציין תנאי אחד או יותר כדי לקבל את התוצאה.לדוגמה, אם ברצונך לספור תאים על סמך מספר קריטריונים, תוכל להשתמש בפונקציות COUNTIF או COUNTIFS באקסל.
מדריך זה מכסה דרכים שונות לשימוש בקריטריונים בודדים או מרובים בפונקציות COUNTIF ו-COUNTIFS של Excel.
בעוד שאתמקד בעיקר בפונקציות COUNTIF ו-COUNTIFS במדריך זה, ניתן להשתמש בכל הדוגמאות הללו גם עם פונקציות Excel אחרות שלוקחות קריטריונים מרובים כקלט (כגון SUMIF, SUMIFS, AVERAGEIF ו-AVERAGEIFS).
תוכן
מבוא לפונקציות Excel COUNTIF ו-COUNTIFS
בואו נבין תחילה כיצד להשתמש בפונקציות COUNTIF ו-COUNTIFS באקסל.
פונקציית COUNTIF של Excel (עם קריטריון יחיד)
הפונקציה COUNTIF של Excel פועלת בצורה הטובה ביותר כאשר ברצונך לספור תאים על סמך קריטריון יחיד.אם ברצונך לספור על סמך מספר קריטריונים, השתמש בפונקציה COUNTIFS.
תחביר
=COUNTIF(טווח, מצב)
קלט פרמטרים
- תְחוּם - טווח התאים שברצונך לחשב.
- מַצָב -קריטריונים שיש להעריך מול טווח התאים שיש לספור.
פונקציית COUNTIFS של Excel (עם מספר קריטריונים)
הפונקציה COUNTIFS של Excel פועלת בצורה הטובה ביותר כאשר ברצונך לספור תאים על סמך מספר קריטריונים.
תחביר
=COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]...)
קלט פרמטרים
- טווח_קריטריונים1 – טווח התאים שברצונך להעריך על פי קריטריון 1.
- קריטריונים 1 – הקריטריון שאתה רוצה להעריך הוא criterion_range1 כדי לקבוע אילו תאים להעריך.
- [טווח_קריטריונים2] – טווח התאים שברצונך להעריך על פי קריטריון 2.
- [קריטריונים2] – הקריטריונים שאתה רוצה להעריך הוא criterion_range2 כדי לקבוע אילו תאים לספור.
כעת נסתכל על כמה דוגמאות לשימוש בקריטריונים מרובים בפונקציית COUNTIF של Excel.
השתמש בתנאי NUMBER בפונקציה COUNTIF של Excel
#1 ספירת תאים כאשר הקריטריון שווה לערך
כדי לקבל את ספירת התאים שבהם פרמטר התנאי שווה לערך שצוין, ניתן להזין את התנאי ישירות או להשתמש בהפניה לתא שמכילה את התנאי.
להלן דוגמה שבה אנו סופרים תאים המכילים את המספר 9 (מה שאומר שפרמטר התנאי שווה ל-9).הנה הנוסחה:
=COUNTIF($B$2:$B$11,D3)
בדוגמה למעלה (בתמונה), הקריטריון נמצא בתא D3.ניתן גם להזין תנאים ישירות לנוסחאות.לדוגמה, אתה יכול גם להשתמש ב:
=COUNTIF($B$2:$B$11,9)
#2 ספירת תאים כאשר המצב גדול מהערך
כדי לקבל את ספירת התאים עם ערך גדול מערך שצוין, אנו משתמשים באופרטור גדול מ- (">").אנחנו יכולים להשתמש בו ישירות בנוסחאות או שאנחנו יכולים להשתמש בהפניות לתאים עם תנאים.
בכל פעם שאנו משתמשים באופרטור במצב באקסל, עלינו לשים אותו בתוך מרכאות כפולות.לדוגמה, אם התנאי גדול מ-10, עלינו להזין ">10" כתנאי (ראה תמונה למטה):
הנה הנוסחה:
=COUNTIF($B$2:$B$11,">10 אינץ')
ניתן גם לכלול תנאים בתאים ולהשתמש בהפניות לתאים כתנאים.במקרה זה אין צורך לשים את התנאי במירכאות כפולות:
=COUNTIF($B$2:$B$11,D3)
זה יכול להיות גם כאשר אתה רוצה שתנאי יהיה בתא אבל לא רוצה להשתמש בו עם אופרטור.לדוגמה, ייתכן שתרצה שהמספר בתא D3 יהיה 10 במקום >10.
במקרה זה, עליך ליצור פרמטר מותנה, שהוא שילוב של אופרטור והפניה לתא (ראה תמונה למטה):
=COUNTIF($B$2:$B$11,">"&D3)
הערה: כאשר אתה משלב אופרטורים והפניות לתאים, האופרטורים תמיד מוקפים במירכאות כפולות.אופרטורים והפניות לתאים מחוברים באמצעות אמפרסנד (&).
#3 ספירת תאים כאשר הקריטריון קטן מערך
כדי לקבל את ספירת התאים עם ערך נמוך מערך שצוין, אנו משתמשים באופרטור less than ("<").אנחנו יכולים להשתמש בו ישירות בנוסחאות או שאנחנו יכולים להשתמש בהפניות לתאים עם תנאים.
בכל פעם שאנו משתמשים באופרטור במצב באקסל, עלינו לשים אותו בתוך מרכאות כפולות.לדוגמה, אם הקריטריון הוא שהכמות צריכה להיות פחות מ-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.
להלן שתי דרכים לעשות זאת:
שיטה XNUMX: השתמש בפונקציה COUNTIFS
הפונקציה COUNTIFS יכולה לטפל במספר תנאים כארגומנטים ולספור תאים רק אם כל התנאים הם TRUE.כדי לספור תאים עם ערכים בין שני ערכים שצוינו, כגון 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,"Joe")
מכיוון שזו מחרוזת טקסט, אני צריך לשים את תנאי הטקסט במירכאות כפולות.
אתה יכול גם לכלול תנאי בתא ולאחר מכן להשתמש בהפניה לתא (כפי שמוצג להלן):
=COUNTIF($B$2:$B$11,E3)
הערה: אתה עלול לקבל תוצאות שגויות אם יש רווחים מובילים/נגררים בטווח המצב או המצב.הקפד לנקות את הנתונים לפני השימוש בנוסחאות אלה.
#2 ספירת תאים כאשר התנאי אינו שווה לטקסט שצוין
בדומה למה שראינו בדוגמה למעלה, אתה יכול גם לספור תאים שאינם מכילים טקסט שצוין.לשם כך, עלינו להשתמש באופרטור לא שווה (<>).
בהנחה שאתה רוצה לספור את כל התאים שאינם מכילים את השם JOE, הנה נוסחה שיכולה לעשות זאת:
=COUNTIF($B$2:$B$11,"<>Joe")
ניתן גם לכלול תנאים בתאים ולהשתמש בהפניות לתאים כתנאים.במקרה זה אינך צריך לשים את התקן במירכאות כפולות (ראה תמונה למטה):
=COUNTIF($B$2:$B$11,E3)
זה יכול להיות גם כאשר אתה רוצה שתנאי יהיה בתא אבל לא רוצה להשתמש בו עם אופרטור.לדוגמה, ייתכן שתרצה שלתא D3 יהיה השם Joe במקום <>Joe.
במקרה זה, עליך ליצור פרמטר מותנה, שהוא שילוב של אופרטור והפניה לתא (ראה תמונה למטה):
=COUNTIF($B$2:$B$11,"<>"&E3)
בעת שילוב של אופרטורים והפניות לתאים, האופרטורים תמיד מוקפים במירכאות כפולות.אופרטורים והפניות לתאים מחוברים באמצעות אמפרסנד (&).
שימוש בתנאי DATE בפונקציות COUNTIF ו- COUNTIFS של Excel
אקסל מאחסן תאריכים ושעות כמספרים.אז אנחנו יכולים להשתמש בו כמו מספר.
#1 ספירת תאים כאשר התנאי שווה לתאריך שצוין
כדי לקבל את ספירת התאים המכילים תאריך מוגדר, נשתמש באופרטור שווה (=) יחד עם התאריך.
כדי לעבוד עם תאריכים, אני ממליץ להשתמש בפונקציית DATE, מכיוון שהיא מבטלת כל סיכוי שערך התאריך שגוי.אז, למשל, אם אני רוצה להשתמש בתאריך 2015 בספטמבר 9, אוכל להשתמש בפונקציית DATE בצורה הבאה:
=DATE(2015,9,1)
למרות ההבדלים האזוריים, נוסחה זו תחזיר את אותו תאריך.לדוגמה, 01-09-2015 יהיה 2015 בספטמבר 9 תחת תחביר תאריך בארה"ב, ו-1 בינואר 2015 תחת תחביר תאריך בבריטניה.עם זאת, נוסחה זו תמיד תחזור ב-2105 בספטמבר 9.
הנה הנוסחה לספירת מספר התאים המכילים את התאריך 02-09-2015:
=COUNTIF($A$2:$A$11,DATE(2015,9,2))
#2 ספירת תאים כאשר המצב הוא לפני או אחרי התאריך שצוין
כדי לספור תאים המכילים תאריכים לפני או אחרי תאריך מוגדר, נוכל להשתמש באופרטורים בפחות מ/גדול יותר.
לדוגמה, אם אני רוצה לספור את כל התאים המכילים תאריכים לאחר 2015 בספטמבר 9, אוכל להשתמש בנוסחה הבאה:
=COUNTIF($A$2:$A$11,">"&DATE(2015,9,2))
באופן דומה, אתה יכול גם לספור את מספר התאים לפני תאריך מוגדר.אם אתה רוצה לכלול תאריכים בספירה, השתמש באופרטור "שווה" יחד עם האופרטורים "גדול מ/פחות מ".
אתה יכול גם להשתמש בהפניות לתאים המכילות תאריכים.במקרה זה, עליך להשתמש באמפרסנד (&) כדי לשלב את האופרטור (במרכאות כפולות) עם התאריך.
ראה את הדוגמה למטה:
=COUNTIF($A$2:$A$11,">"&F3)
#3 ספירת תאים עם מספר קריטריונים - בין שני תאריכים
כדי לקבל את ספירת הערכים בין שני ערכים, עלינו להשתמש במספר תנאים בפונקציה COUNTIF.
אנחנו יכולים לעשות זאת בשתי דרכים - פונקציית COUNTIFS בודדת או שתי פונקציות COUNTIF.
שיטה XNUMX: השתמש בפונקציה COUNTIFS
הפונקציה COUNTIFS יכולה לקחת תנאים מרובים כארגומנטים ולספור תאים רק אם כל התנאים הם TRUE.כדי לספור תאים עם ערכים בין שני תאריכים שצוינו, כגון 9 בספטמבר ו-2 בספטמבר, נוכל להשתמש בפונקציית 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))
בנוסחה שלמעלה, אנו מוצאים תחילה את מספר התאים שהתאריך שלהם הוא אחרי 9 בספטמבר, ולאחר מכן נפחית את מספר התאים שהתאריך שלהם הוא אחרי 2 בספטמבר.זה ייתן תוצאה של 9 (כלומר, מספר התאים עם תאריכים אחרי 7 בספטמבר ובתאריך או לפני 7 בספטמבר).
אם אינך רוצה שהנוסחה תחשב גם את ה-9 בספטמבר וגם את ה-2 בספטמבר, השתמש בנוסחה הבאה במקום זאת:
=COUNTIF($A$2:$A$11,”>=”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))
אם ברצונך לא לכלול שני תאריכים מהספירה, השתמש בנוסחה הבאה:
=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7)-COUNTIF($A $2:$A$11,日期(2015,9,7)))
בנוסף, ניתן להגדיר תאריך סטנדרטי בתא ולהשתמש בהפניות לתאים (ובאופרטורים במירכאות כפולות עם אמפרסנד).
שימוש בתווים כלליים בתנאים של הפונקציות COUNTIF ו-COUNTIFS
ישנם שלושה תווים כלליים לחיפוש ב-Excel:
- *(כּוֹכָבִית) - הוא מייצג כל מספר של תווים.לדוגמה, אקס* עשוי להתכוון לאקסל, אקסל, דוגמה, מומחה וכו'.
- ? (סימן שאלה) - זה מייצג דמות אחת.לדוגמה, טרמפ עשוי להתכוון לטראמפ או לטראמפ.
- ~ (טילדה) - משמש לזיהוי תווים כלליים (~, *, ?) בטקסט.
אתה יכול להשתמש בפונקציה 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))
בואו נבין את הנוסחה הזו על ידי פירוקה:
#3 ספירת תאים המכילים טקסט ספציפי
נניח שאנו רוצים לספור את כל התאים שבהם שם נציג המכירות מתחיל ב-J.ניתן להשיג זאת בקלות על ידי שימוש בתווים כלליים בפונקציית COUNTIF.הנה הנוסחה:
=COUNTIFS($C$2:$C$11,"J*")
התנאי J* מציין שהטקסט בתא צריך להתחיל ב-J ויכול להכיל כל מספר של תווים.
אם ברצונך לספור תאים המכילים אותיות בכל מקום בטקסט, הקף אותם בכוכבית.לדוגמה, אם ברצונך לספור תאים המכילים את האות "a", השתמש ב-*a* כתנאי.
בהשוואה למאמרים אחרים שלי, המאמר הזה ארוך בצורה יוצאת דופן.מקווה שאתה אוהב את זה.ספר לי מה אתה חושב על ידי השארת תגובה.
