ספירת מספר תאים שאינם בין שני מספרים נתונים ב-Excel

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

• ספירת מספר תאים שאינם בין שני מספרים נתונים באמצעות פונקציית COUNTIF
• ספירת מספר תאים שאינם בין שני מספרים נתונים באמצעות פונקציית SUMPRODUCT

ספירת מספר תאים שאינם בין שני מספרים נתונים באמצעות פונקציית COUNTIF

הפונקציה COUNTIF ב-Excel עשויה לעזור לכם לפתור את המשימה הזו, התחביר הכללי הוא:

• range: טווח הנתונים שמכיל את הערכים שברצונכם לספור;
• low: התא המכיל את הגבול התחתון של הטווח;
• high: התא המכיל את הגבול העליון של הטווח;
• &: מפריד זה משמש לקשר את ערך התא עם התנאים.

1. אנא הזינו או העתיקו את הנוסחה הבאה לתא ריק:

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

הסבר על הנוסחה:

• COUNTIF(B2:H2,"<"&J2): פונקציית COUNTIF זו תקבל את מספר התאים מתחת לערך שב-J2, היא מחזירה את התוצאה: 3.
• COUNTIF(B2:H2,">"&K2): פונקציית COUNTIF זו תקבל את מספר התאים מעל הערך שב-K2, היא מחזירה את התוצאה: 1.
• COUNTIF(B2:H2,"<"&J2)+COUNTIF(B2:H2,">"&K2)=3+1: לחבר את שתי התוצאות הללו כדי לקבל את הלוגיקה הנדרשת: פחות מ-J2 או יותר מ-K2.

ספירת מספר תאים שאינם בין שני מספרים נתונים באמצעות פונקציית SUMPRODUCT

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

• range: טווח הנתונים שמכיל את הערכים שברצונכם לספור;
• low: התא המכיל את הגבול התחתון של הטווח;
• high: התא המכיל את הגבול העליון של הטווח;

1. הזינו או העתיקו את הנוסחה הבאה לתא ריק:

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

הסבר על הנוסחה:

• בנוסחה זו, אין צורך להשתמש במפריד & כדי לקשר את הפניות לתאים, והסימן חיבור (+) כאן יוצר לוגיקה מסוג OR.
• נוסחה זו תעריך כל מערך ותחזיר TRUE או FALSE לכל ערך, כך: =SUMPRODUCT({FALSE,TRUE,FALSE,FALSE,TRUE,TRUE,FALSE}+{TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE})
• והפעולה המתמטית תהפוך את הערכים TRUE ו-FALSE ל-1 ו-0 כך: =SUMPRODUCT({0,1,0,0,1,1,0}+{1,0,0,0,0,0,0})
• לבסוף, לחבר את שני המערכים לקבלת התוצאה: =SUMPRODUCT({1, 1, 0, 0, 1,1.0})=4.

פונקציות קשורות:

מאמרים נוספים: