דרכים לספירת ערכים בגליון עבודה

וידאו: ספירת תאים באמצעות שורת המצב של Excel

צפה בסרטון הווידאו הבא כדי ללמוד כיצד להציג את הספירה בשורת המצב.

שימוש סכום אוטומטי

השתמש סכום אוטומטי על-ידי בחירת טווח תאים המכיל ערך מספרי אחד לפחות. לאחר מכן , בכרטיסיה נוסחאות , לחץ על סכום >ספור מספרים.

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

לראש הדף

הוספת שורת סכום ביניים

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

כמו כן, עיין במאמרים הבאים:

• חלוקה לרמות (קיבוץ) של נתונים בגליון עבודה

• הוספת סכומי ביניים ברשימת נתונים בגליון עבודה

לראש הדף

ספירת תאים ברשימה או בעמודה של טבלת Excel באמצעות הפונקציה SUBTOTAL

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

תחביר SUBTOTAL הולך כך:

SUBTOTAL(function_num,ref1,[ref2],...)‎

כדי לכלול ערכים מוסתרים בטווח, עליך להגדיר את function_num הארגומנט ל - 2.

כדי לא לכלול ערכים מוסתרים בטווח, הגדר את function_num ל- 102.

לראש הדף

וידאו: שימוש בפונקציות COUNT‏, COUNTIF ו- COUNTA

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

לראש הדף

ספירת תאים בטווח באמצעות הפונקציה COUNT

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

בדוגמה שלעיל, A2, A3 ו- A6 הם התאים היחידים המכילים ערכים מספריים בטווח, ולכן הפלט הוא 3.

לראש הדף

ספירת תאים בטווח בהתבסס על תנאי יחיד באמצעות הפונקציה COUNTIF

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

לראש הדף

ספירת תאים בעמודה בהתבסס על תנאי אחד או יותר באמצעות הפונקציה DCOUNT

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

בדוגמה הבאה, ברצונך למצוא את ספירת החודשים, כולל או מאוחר יותר ממרץ 2016 שנמכרו יותר מ- 400 יחידות. הטבלה הראשונה בגליון העבודה, מ- A1 עד B7, מכילה את נתוני המכירות.

הפונקציה DCOUNT משתמשת בתנאים כדי לקבוע מהיכן יש להחזיר את הערכים. תנאים מוזנים בדרך כלל בתאים בגליון העבודה עצמו ולאחר מכן אתה מפנה לתאים אלה בארגומנט criteria . בדוגמה זו, התאים A10 ו- B10 מכילים שני תנאים - תנאי המציין כי הערך המוחזר חייב להיות גדול מ- 400, והערך השני המציין שהחודש המסתיים צריך להיות שווה ל- 31 במרץ 2016 או גדול מ- 31 במרץ 2016.

עליך להשתמש בתחביר הבא:

=DCOUNT(A1:B7,"Month ending",A9:B10)

הפונקציה DCOUNT בודקת את הנתונים בטווח A1 עד B7, מחילה את התנאים שצוינו ב- A10 וב- B10 ומחזירה 2, מספר השורות הכולל הממלא את שני התנאים (שורות 5 ו- 7).

לראש הדף

ספירת תאים בטווח בהתבסס על תנאים מרובים באמצעות הפונקציה COUNTIFS

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

התחביר עבור COUNTIFS הוא:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)‎

הבט בדוגמה הבאה:

לראש הדף

ספירה המבוססת על קריטריונים באמצעות הפונקציות COUNT ו- IF יחד

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

לראש הדף

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

בדוגמאות שלהלן, אנו משתמשים בפונקציות IF ו- ‏SUM יחד. הפונקציה IF בודקת תחילה את הערכים בתאים מסוימים ולאחר מכן, אם תוצאת הבדיקה היא True, הפונקציה SUM מסכמת את הערכים שעוברים את הבדיקה.

דוגמה 1

הפונקציה לעיל מציינת כי C2:C7 מכילה את הערכים Buchanan ו- Dodsworth, ולאחר מכן הפונקציה SUM אמורה להציג את סכום הרשומות שבהן מתקיים התנאי. הנוסחה מוצאת שלוש רשומות עבור Buchanan ורשומה אחת עבור Dodsworth בטווח הנתון ומציגה 4.

דוגמה 2

הפונקציה לעיל מציינת כי D2:D7 מכיל ערכים נמוכים מ- ‎$9000 או גדולים מ- ‎$19,000 ולאחר מכן הפונקציה SUM אמורה להציג את סכום כל הרשומות שבהן מתקיים התנאי. הנוסחה מוצאת שתי רשומות D3 ו- D5 עם ערכים נמוכים מ- ‎$9000 ולאחר מכן את D4 ו- D6 עם ערכים גדולים מ- ‎$19,000 ומציגה 4.

דוגמה 3

הפונקציה לעיל מציינת אם D2:D7 כולל חשבוניות עבור Buchanan בפחות מ- $9000, הפונקציה SUM אמורה להציג את סכום הרשומות שבהן התנאי מתמלא. הנוסחה מגלה כי C6 ממלא את התנאי ומציגה 1.

עיין במאמרי Knowledge Base הבאים לקבלת עצות נוספות:

• XL: שימוש ב- SUM(IF()) כפונקציית מערך במקום בפונקציה COUNTIF() עם AND

• XL: כיצד לספור את ההתרחשויות של מספר או טקסט בטווח

לראש הדף

ספירת תאים בעמודה או בשורה ב- PivotTable

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

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

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

1. הזן את הנתונים הבאים בגיליון אלקטרוני של Excel.

   

2. בחר A2:C8

3. לחץ על הוסף > PivotTable.

4. בתיבת הדו-שיח 'יצירת PivotTable', לחץ על בחר טבלה או טווח, לחץ על גליון עבודה חדש ולאחר מכן לחץ על אישור.

   טבלת PivotTable ריקה נוצרת בגיליון חדש.

5. בחלונית 'שדות PivotTable', בצע את הפעולות הבאות:

   1. גרור את ספורט לאזור שורות.

   2. גרור את רבעון לאזור עמודות.

   3. גרור את מכירות לאזור ערכים.

   4. חזור על שלב c.

      שם השדה מוצג כ- SumofSales2 הן ב- PivotTable והן באזור 'ערכים'.

      בשלב זה, החלונית 'שדות PivotTable' נראית כך:

      

   5. באזור ערכים, לחץ על הרשימה הנפתחת לצד SumofSales2 ובחר הגדרות שדה ערך.

   6. בתיבת הדו-שיח הגדרות שדה ערך, בצע את הפעולות הבאות:

      1. במקטע סכם שדה ערך לפי, בחר ספירה.

      2. בשדה שם מותאם אישית, שנה את השם לספירה.

         

      3. לחץ על אישור.

   טבלת ה- PivotTable מציגה את ספירת הרשומות עבור הקטגוריות 'גולף' ו'טניס' ברבעון 3 וברבעון 4, יחד עם נתוני המכירות.

   

לראש הדף

ספירת תאים שאינם ריקים בטווח באמצעות הפונקציה COUNTA

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

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

הפונקציה COUNTA מתעלמת מהערכים הריקים ב- D3, D4, D8 ו- D11, ו סופרת רק את התאים המכילים ערכים בעמודה D. הפונקציה מוצאת שישה תאים בעמודה D המכילה ערכים ומציגה 6 כפלט.

לראש הדף

ספירת תאים שאינם ריקים ברשימה עם תנאים ספציפיים באמצעות הפונקציה DCOUNTA

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

הדוגמה הבאה משתמשת בפונקציה DCOUNTA כדי לספור את הרשומות במסד הנתונים הכלולות בטווח A1:B7 התואמות לתנאים שצוינו בטווח הקריטריונים A9:B10. תנאים אלה הם שהערך של מזהה המוצר חייב להיות גדול או שווה ל- 2000 והערך דירוגים חייב להיות גדול או שווה ל- 50.

הפונקציה DCOUNTA מוצאת שתי שורות התואמות לתנאים- שורות 2 ו- 4 ומציגה את הערך 2 כפלט.

לראש הדף

ספירת תאים ריקים בטווח רציף באמצעות הפונקציה COUNTBLANK

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

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

לראש הדף

ספירת תאים ריקים בטווח שאינו רציף באמצעות שילוב של הפונקציות SUM ו- IF

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

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

לראש הדף

ספירת הערכים הייחודיים בעמודת רשימה באמצעות סינון מתקדם

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

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

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

באיור הבא, עמודה E מכילה את הערכים שהועתקו מהטווח בעמודה D.

לקבלת מידע נוסף, ראה סינון באמצעות קריטריונים מתקדמים

לראש הדף

ספירת הערכים הייחודיים בטווח העונים על תנאי אחד או יותר באמצעות הפונקציות IF, SUM, FREQUENCY, MATCH ו- LEN

השתמש בשילובים שונים של הפונקציות IF‏, SUM‏, FREQUENCY‏, MATCH ו- LEN.

לקבלת מידע נוסף ודוגמאות, עיין בסעיף "ספירת הערכים הייחודיים באמצעות פונקציות" במאמר ספירת ערכים ייחודיים בין ערכים כפולים.

לראש הדף

ספירת מספר התאים הכולל בטווח באמצעות הפונקציות ROWS ו- COLUMNS

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

לראש הדף

ספירת מילים בטווח באמצעות שילוב של הפונקציות SUM, IF, LEN, TRIM ו- SUBSTITUTE

באפשרותך להשתמש בשילוב של הפונקציות SUM, IF, LEN, TRIM ו- SUBSTITUTE בנוסחת מערך. הדוגמה הבאה מציגה את התוצאה של שימוש בנוסחה מקוננת כדי למצוא את מספר המילים בטווח של 7 תאים (ש- 3 מהם ריקים). חלק מהתאים מכילים רווחים מובילים או נגררים — הפונקציות TRIM ו- SUBSTITUTE מסירות רווחים מיותרים אלה לפני שפעולות הספירה מתבצעות. הבט בדוגמה הבאה:

כעת, כדי שהנוסחה לעיל תפעל כראוי, עליך להפוך אותה לנוסחת מערך, אחרת הנוסחה מחזירה את #VALUE! שגיאת ‎#REF!‎. לשם כך, לחץ על התא המכיל את הנוסחה ולאחר מכן, בשורת הנוסחאות, הקש Ctrl + Shift + Enter. Excel מוסיף סוגריים מסולסלים בהתחלה ובסוף הנוסחה, ובכך הופכים אותה לנוחת מערך.

לקבלת מידע נוסף אודות נוסחאות מערך, ראה מבט כולל על נוסחאות ב- Excelוייצור נוסחת מערך.

לראש הדף