השתמש בפונקציה INDIRECT בנוסחאות Excel כדי לשנות את טווח הפניות התאים המשמשות בנוסחה מבלי לערוך את הנוסחה עצמה. זה מבטיח כי אותם תאים משמשים, גם כאשר הגיליון האלקטרוני שלך משתנה.
הערה: הוראות במאמר זה חלות על Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel for Mac ו- Excel Online.
השתמש בטווח דינמי עם הנוסחה הקבועה - COUNTIF
ניתן להשתמש בפונקציה INDIRECT עם מספר פונקציות המקבלות התייחסות לתא כארגומנט, כגון פונקציות SUM ו- COUNTIF.
שימוש ב- INDIRECT כארגומנט עבור COUNTIF יוצר טווח דינמי של הפניות תאים שניתן לספור על ידי הפונקציה אם ערכי התא עומדים בקריטריונים. זה עושה זאת על ידי הפיכת נתוני טקסט, המכונה לפעמים מחרוזת טקסט, לתוך הפניה התא.
דוגמה זו מבוססת על הנתונים שמופיעים בתמונה לעיל. הנוסחה הקונטיווית - העקיבה שנוצרה בערכת הלימוד היא:
= COUNTIF (עקיפות (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 (עקיפות (E1 & ":" & E2), "שני")
בנוסחה זו, הפונקציה INDIRECT מתייחסת לתאים B1 עד B6. הפונקציה COUNTIF מסכמת את מספר התאים בעלי ערך הטקסט שתיים בהם.
במקרה זה, התוצאה היא 1.
COUNTA, COUNTBLANK ועקף
שתי פונקציות אחרות של ספירת Excel הן COUNTA, אשר סופרות תאים המכילים כל סוג של נתונים, תוך התעלמות מתאים ריקים או ריקים בלבד, ו- COUNTBLANK, שמספור תאים ריקים או ריקים בטווח בלבד.
מאחר שלשני הפונקציות הללו יש תחביר דומה לפונקציה COUNTIF, ניתן להחליף אותן בדוגמה שלמעלה עם INDIRECT כדי ליצור את הנוסחאות הבאות:
= COUNTA (עקיפות (E1 & ":" & E2))= COUNTBLANK = (עקיפים (E1 & ":" & E2)
עבור טווח D1: D6, COUNTA מחזירה תשובה של 4, שכן ארבעה מתוך חמישה תאים מכילים נתונים. COUNTBLANK מחזיר תשובה של 1 מאחר שיש רק תא ריק אחד בטווח. היתרון של שימוש בפונקציה INDIRECT בכל הנוסחאות הללו הוא שניתן להכניס תאים חדשים לכל מקום בטווח. הטווח משתנה באופן דינמי בתוך הפונקציות השונות, והתוצאות מתעדכנות בהתאם. ללא הפונקציה INDIRECT, כל פונקציה תצטרך לערוך כדי לכלול את כל 7 תאים, כולל אחד חדש. היתרונות של הפונקציה INDIRECT הם שניתן להוסיף ערכי טקסט כהפניות לתא, וכי הוא מעדכן באופן דינמי את הטווחים בכל פעם שהגיליון האלקטרוני שלך משתנה. זה הופך את התחזוקה הכוללת של גיליון אלקטרוני לקלה הרבה יותר, במיוחד עבור גיליונות אלקטרוניים גדולים מאוד. מדוע להשתמש בפונקציה עקיבה?
