כיצד לספור תאים צבעוניים ב- Excel (מדריך שלב אחר שלב + וידיאו)

צפה בוידאו - כיצד לספור תאים צבעוניים ב- Excel

האם זה לא יהיה נהדר אם תהיה פונקציה שיכולה לספור תאים צבעוניים ב- Excel?

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

אבל…

אפשר לעשות זאת בקלות.

כיצד לספור תאים צבעוניים ב- Excel

במדריך זה אראה לך שלוש דרכים לספור תאים צבעוניים ב- Excel (עם ובלי VBA):

1. שימוש במסנן ובפונקציה SUBTOTAL
2. שימוש בפונקציית GET.CELL
3. שימוש בפונקציה מותאמת אישית שנוצרה באמצעות VBA

מספר 1 מספר תאים צבעוניים באמצעות מסנן ו- SUBTOTAL

כדי לספור תאים צבעוניים ב- Excel, עליך להשתמש בשני השלבים הבאים:

• סנן תאים צבעוניים
• השתמש בפונקציה SUBTOTAL לספירת תאים צבעוניים הנראים לעין (לאחר סינון).

נניח שיש לך מערך נתונים כפי שמוצג להלן:

ישנם שני צבעי רקע המשמשים במערך הנתונים הזה (ירוק וכתום).

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

1. בכל תא מתחת למערך הנתונים, השתמש בנוסחה הבאה: = SUBTOTAL (102, E1: E20)
   
2. בחר בכותרות.
3. עבור אל נתונים -> מיין וסנן -> מסנן. פעולה זו תחיל מסנן על כל הכותרות.
4. לחץ על אחת מהתפריטים הנפתחים של המסנן.
5. עבור אל 'סינון לפי צבע' ובחר את הצבע. במערך הנתונים לעיל, מכיוון שיש שני צבעים המשמשים להדגשת התאים, המסנן מציג שני צבעים לסינון תאים אלה.

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

איך זה עובד?

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

אם הנתונים אם לא מסוננים הם מחזירים 19, אך אם הם מסוננים, הם רק מחזירים את ספירת התאים הגלויים.

נסה זאת בעצמך … הורד את קובץ הדוגמה

#2 ספירת תאים צבעוניים באמצעות פונקציית GET.CELL

GET.CELL היא פונקציה של Macro4 שנשמרה עקב סיבות תאימות.

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

עם זאת, הוא פועל בטווחים בשם Excel.

ראה גם: דע עוד על פונקציית GET.CELL.

להלן שלושת השלבים לשימוש ב- GET.CELL לספירת תאים צבעוניים ב- Excel:

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

בואו לצלול לעומק ולראות מה לעשות בכל אחד משלושת השלבים שהוזכרו.

יצירת טווח בשם

• עבור אל נוסחאות -> הגדר שם.
• בתיבת הדו -שיח שם חדש, הזן:
  • שם: GetColor
  • היקף: חוברת עבודה
  • הכוונה ל: = GET.CELL (38, גיליון 1! $ A2)
    בנוסחה שלעיל, השתמשתי גיליון 1! $ A2 כטענה השנייה. עליך להשתמש בהתייחסות של העמודה שבה יש לך את התאים עם צבע הרקע.

קבלת קוד הצבע לכל תא

בתא הסמוך לנתונים, השתמש בנוסחה = GetColor

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

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

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

אם תעקוב אחר התהליך לעיל, תהיה לך עמודה עם מספרים המתאימים לצבע הרקע.

כדי לקבל את ספירת הצבע הספציפי:

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

= COUNTIF ($ F $ 2: $ F $ 20, GetColor)

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

איך זה עובד?

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

הפונקציה COUNTIF משתמשת בטווח ($ F $ 2: $ F $ 18) המכיל את מספרי קוד הצבע של כל התאים ומחזיר את הספירה בהתאם למספר הקריטריונים.

נסה זאת בעצמך … הורד את קובץ הדוגמה

מספר 3 צבעוני באמצעות VBA (על ידי יצירת פונקציה מותאמת אישית)

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

אבל, אם אתה בסדר עם השימוש ב- VBA, זוהי הקלה מבין שלוש השיטות.

באמצעות VBA, היינו יוצרים פונקציה מותאמת אישית, שתעבוד כמו פונקציית COUNTIF ותחזיר את ספירת התאים עם צבע הרקע הספציפי.

הנה הקוד:

קוד שנוצר על ידי Sumit Bansal מתוך https://trumpexcel.com פונקציה GetColorCount (CountRange כטווח, CountColor כטווח) Dim CountColorValue כמכלול שלם Dim TotalCount כשלם CountColorValue = CountColor.Interior.ColorIndex Set rCell = CountRange עבור כל rCell In CountRange rCell.Interior.ColorIndex = CountColorValue ואז TotalCount = TotalCount + 1 End אם הבא rCell GetColorCount = TotalCount סוף פונקציה

כדי ליצור פונקציה מותאמת אישית זו:

• כאשר חוברת העבודה שלך פעילה, הקש Alt + F11 (או לחץ לחיצה ימנית על הכרטיסייה גליון העבודה ובחר הצג קוד). זה יפתח את עורך VB.
• בחלונית השמאלית, מתחת לחוברת העבודה שבה אתה עובד, לחץ באמצעות לחצן העכבר הימני על כל אחד מדפי העבודה ובחר הוספה -> מודול. זה יכניס מודול חדש. העתק והדבק את הקוד בחלון קוד המודול.
• לחץ פעמיים על שם המודול (כברירת מחדל שם המודול במודול 1) והדבק את הקוד בחלון הקוד.
• סגור את עורך VB.
• זהו זה! כעת יש לך פונקציה מותאמת אישית בגליון העבודה בשם GetColorCount.

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

תחביר: = GetColorCount (CountRange, CountColor)

• CountRange: הטווח שבו ברצונך לספור את התאים עם צבע הרקע שצוין.
• CountColor: הצבע שעבורו רוצים לספור את התאים.

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

הערה: מכיוון שיש קוד בחוברת העבודה, שמור אותו עם סיומת .xls או .xlsm.

נסה זאת בעצמך … הורד את קובץ הדוגמה

האם אתה מכיר דרך אחרת לספור תאים צבעוניים ב- Excel?

אם כן, שתף אותי בכך שתשאיר תגובה.