כיצד להשתמש בטווח דינמי ב-Excel עם COUNTIF ו- INDIRECT
2024 מְחַבֵּר: Abigail Brown | [email protected]. שונה לאחרונה: 2023-12-17 06:48
מה צריך לדעת
הפונקציה INDIRECT משנה את טווח ההפניות לתאים בנוסחה מבלי לערוך את הנוסחה.
השתמש ב-INDIRECT כארגומנט עבור COUNTIF כדי ליצור טווח דינמי של תאים העומדים בקריטריונים שצוינו.
הקריטריונים נקבעים על ידי הפונקציה INDIRECT, ורק תאים שעומדים בקריטריונים נספרים.
מאמר זה מסביר כיצד להשתמש בפונקציה INDIRECT בנוסחאות Excel כדי לשנות את טווח ההפניות לתאים בשימוש בנוסחה מבלי צורך לערוך את הנוסחה עצמה. זה מבטיח כי נעשה שימוש באותם תאים, גם כאשר הגיליון האלקטרוני שלך משתנה.המידע חל על Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel for Mac ו-Excel Online.
השתמש בטווח דינמי עם נוסחת COUNTIF - INDIRECT
ניתן להשתמש בפונקציה INDIRECT עם מספר פונקציות שמקבלות הפניה לתא כארגומנט, כגון הפונקציות SUM ו-COUNTIF.
שימוש ב- INDIRECT כארגומנט עבור COUNTIF יוצר טווח דינמי של הפניות לתאים שניתן לספור על ידי הפונקציה אם ערכי התא עומדים בקריטריונים. זה עושה זאת על ידי הפיכת נתוני טקסט, המכונה לפעמים מחרוזת טקסט, להפניה לתא.
Image
דוגמה זו מבוססת על הנתונים המוצגים בתמונה למעלה. הנוסחה COUNTIF - INDIRECT שנוצרה במדריך היא:
=COUNTIF(INDIRECT(E1&":"&E2), ">10")
בנוסחה זו, הארגומנט עבור הפונקציה INDIRECT מכיל:
הפניות התא E1 ו-E2, המכילות את נתוני הטקסט D1 ו-D6.
שני סימני אמפרסנד (&) המשמשים לשרשור, או לחיבור, של המעי הגס עם הפניות התא E1 ו-E2.
התוצאה היא ש- INDIRECT ממירה את מחרוזת הטקסט D1:D6 להפניה לתא ומעבירה אותה לפונקציה COUNTIF שתיספור אם התאים המוזכרים גדולים מ-10.
הפונקציה INDIRECT מקבלת כל קלט טקסט. אלה יכולים להיות תאים בגליון העבודה המכילים הפניות לתא טקסט או טקסט המוזנים ישירות לפונקציה.
שנה באופן דינמי את טווח הנוסחה
זכור, המטרה היא ליצור נוסחה עם טווח דינמי. ניתן לשנות טווח דינמי מבלי לערוך את הנוסחה עצמה.
על ידי שינוי נתוני הטקסט הממוקמים בתאים E1 ו-E2, מ-D1 ו-D6 ל-D3 ו-D7, ניתן לשנות בקלות את הטווח הכולל של הפונקציה מ-D1:D6 ל-D3:D7. זה מבטל את הצורך לערוך ישירות את הנוסחה בתא G1.
הפונקציה COUNTIF בדוגמה זו סופרת רק תאים המכילים מספרים אם הם גדולים מ-10. למרות שארבעה מתוך חמשת התאים בטווח של D1:D6 מכילים נתונים, רק שלושה תאים מכילים מספרים. הפונקציה מתעלמת מתאים ריקים או מכילים נתוני טקסט.
ספירת טקסט עם COUNTIF
פונקציית COUNTIF אינה מוגבלת לספירת נתונים מספריים. הוא גם סופר תאים המכילים טקסט על ידי בדיקה אם הם תואמים לטקסט מסוים.
כדי לעשות זאת, הנוסחה הבאה מוזנת בתא G2:
=COUNTIF(INDIRECT(E1&":"&E2), "two")
בנוסחה זו, הפונקציה INDIRECT מפנה לתאים B1 עד B6. הפונקציה COUNTIF כוללת את מספר התאים הכוללים את ערך הטקסט שני.
במקרה זה, התוצאה היא 1.
COUNTA, COUNTBLANK ו-INDIRECT
שתי פונקציות נוספות לספירת Excel הן COUNTA, שסופרת תאים המכילים כל סוג של נתונים תוך התעלמות רק מתאים ריקים או ריקים, ו-COUNTBLANK, שסופרת רק תאים ריקים או ריקים בטווח.
מכיוון שלשתי הפונקציות הללו יש תחביר דומה לפונקציה COUNTIF, ניתן להחליף אותן בדוגמה שלמעלה ב-INDIRECT כדי ליצור את הנוסחאות הבאות:
=COUNTA(INDIRECT(E1&":"&E2))
=COUNTBLANK(INDIRECT(E1&":"&E2)
עבור הטווח D1:D6, COUNTA מחזירה תשובה של 4, מכיוון שארבעה מתוך חמשת התאים מכילים נתונים. COUNTBLANK מחזירה תשובה של 1 מכיוון שיש רק תא ריק אחד בטווח.
למה להשתמש בפונקציה INDIRECT?
היתרון בשימוש בפונקציה INDIRECT בכל הנוסחאות הללו הוא שניתן להכניס תאים חדשים בכל מקום בטווח.
הטווח משתנה באופן דינמי בתוך הפונקציות השונות, והתוצאות מתעדכנות בהתאם.
Image
ללא הפונקציה INDIRECT, כל פונקציה תצטרך לעבור עריכה כך שתכלול את כל 7 התאים, כולל החדש.
היתרונות של הפונקציה INDIRECT הם שניתן להוסיף ערכי טקסט כהפניות לתאים ושהיא מעדכנת באופן דינמי טווחים בכל פעם שהגיליון האלקטרוני שלך משתנה.
זה מקל הרבה יותר על תחזוקת הגיליון האלקטרוני הכולל, במיוחד עבור גיליונות אלקטרוניים גדולים מאוד.
