פונקציית קיזוז Excel - דוגמאות לנוסחאות + סרטון חינם

פונקציית קיזוז Excel (דוגמה + וידאו)

מתי להשתמש בפונקציית OFFSET של Excel

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

מה זה מחזיר

הוא מחזיר את ההפניה שאליה מפנה הפונקציה OFFSET.

תחביר

= OFFSET (הפניה, שורות, cols, [גובה], [רוחב])

טענות קלט

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

הבנת יסודות פונקציית הקיזוז של Excel

פונקציית Excel OFFSET היא אולי אחת הפונקציות המבלבלות ביותר ב- Excel.

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

[באדיבות התמונה: ויקיפדיה נהדרת]

כעת, כמו כל שאר חלקי השחמט, ל- Rook יש נתיב קבוע עליו הוא יכול לנוע על הלוח. זה יכול ללכת ישר (ימינה/שמאלה או למעלה/למטה בלוח), אבל זה לא יכול ללכת באלכסון.

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

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

אתה תבקש ממנו לקחת שני צעדים כלפי מטה ושני צעדים ימינה … לא?

וזוהי קירבה קרובה לאופן בו פועלת פונקציית OFFSET.

כעת נראה מה זה אומר ב- Excel. אני רוצה להתחיל בתא D5 (שזה המקום בו נמצא ה- Rook) ואז לרדת שתי שורות למטה ושתי עמודות ימינה ולהביא את הערך מהתא שם.

הנוסחה תהיה:
= קיזוז (מאיפה להתחיל, כמה שורות למטה, כמה עמודות מימין)

כפי שאתה יכול לראות, הנוסחה בדוגמה שלעיל בתא J1 היא = OFFSET (D5,2,2).

זה התחיל ב D5, ואז ירד שתי שורות למטה ושתי עמודות ימינה והגיע לתא F7. לאחר מכן הוא החזיר את הערך בתא F7.

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

בואו נסתכל על דוגמא פשוטה כאן:

נניח שברצונך להשתמש בהתייחסות לתא A1 (בצהוב) וברצונך להתייחס לכל הטווח המודגש בכחול (C2: E4) בנוסחה.

איך היית עושה את זה באמצעות מקלדת? תחילה היית נכנס לתא C2 ולאחר מכן בחר את כל התאים ב- C2: E4.

כעת נראה כיצד לעשות זאת באמצעות נוסחת OFFSET:

= קיזוז (A1,1,2,3,3)

אם תשתמש בנוסחה זו בתא, היא תחזיר ערך #! שגיאה, אך אם תיכנס למצב העריכה ותבחר בנוסחה ותלחץ על F9, תראה שהיא מחזירה את כל הערכים המסומנים בכחול.

כעת נראה כיצד נוסחה זו עובדת:

= קיזוז (A1,1,2,3,3)

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

עכשיו שיש לך את ההתייחסות לטווח תאים (C2: E4), אתה יכול להשתמש בו בתוך פונקציה אחרת (כגון SUM, COUNT, MAX, AVERAGE).

יש לקוות שיש לך הבנה בסיסית טובה בשימוש בפונקציית Excel OFFSET. כעת נסתכל על כמה דוגמאות מעשיות לשימוש בפונקציית OFFSET.

פונקציית OFFSET של Excel - דוגמאות

להלן שתי דוגמאות לשימוש בפונקציית Excel OFFSET.

דוגמא 1 - מציאת התא שהתמלא אחרון בעמודה

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

כדי למצוא את התא האחרון בעמודה, השתמש בנוסחה הבאה:

= קיזוז (A1, COUNT (A: A) -1,0)

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

לדוגמה, במקרה זה, ישנם 8 ערכים, ולכן COUNT (A: A) מחזיר 8. קיזזנו את התא A1 ב- 7 כדי לקבל את הערך האחרון.

דוגמא 2 - יצירת נפתח דינאמי

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

הנה דוגמה:

שים לב שהתפריט הנפתח משתנה באופן אוטומטי כאשר מוסיפים או מסירים את השנה.

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

הנה איך לעשות זאת:

• בחר את התא שבו ברצונך להכניס את התפריט הנפתח.
• עבור אל נתונים -> כלי נתונים -> אימות נתונים.
• בתיבת הדו -שיח אימות נתונים, בכרטיסיה הגדרות בחר רשימה מהתפריט הנפתח.
• במקור, הזן את הנוסחה הבאה: = OFFSET (A1,0,0, COUNT (A: A), 1)
• לחץ על אישור.

בואו לראות כיצד נוסחה זו עובדת:

• שלושת הארגומנטים הראשונים של הפונקציה OFFSET הם A1, 0 ו- 0. זה בעצם אומר שהוא יחזיר את אותו תא ההתייחסות (שהוא A1).
• הארגומנט הרביעי הוא לגובה וכאן הפונקציה COUNT מחזירה את המספר הכולל של הפריטים ברשימה. הוא מניח כי אין ריקים ברשימה.
• הארגומנט החמישי הוא 1, המציין שרוחב העמודה צריך להיות אחד.

הערות נוספות:

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

חלופות פונקציות של קיזוז אופסל של Excel

בשל כמה מהמגבלות של פונקציית OFFSET של Excel, רבים רוצים לשקול חלופות לה:

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

פונקציית קיזוז Excel - הדרכת וידאו

• פונקציית VLOOKUP של Excel.
• פונקציית HLOOKUP של Excel.
• פונקציית Excel INDEX.
• פונקציית INDIRECT של Excel.
• פונקציית Excel MATCH.