ברגע שאתה יודע איך להשתמש בפונקציה העקיפה ב-Excel, אתה יכול לתפוס מידע מגיליונות אחרים, להתייחס לטווחים בעלי שם, ולשלב אותו עם פונקציות אחרות כדי ליצור כלי ורסטילי באמת. זה יכול לדרוש מעט תרגול כדי להבין, אבל עם הפונקציה העקיפה, אתה יכול לעשות יותר ממה שאתה חושב.
ההוראות במאמר זה חלות על Excel עבור Microsoft 365, Excel 2019 ו-Excel 2016.
מהי הפונקציה העקיפה?
הפונקציה העקיפה היא דרך להמיר מחרוזת טקסט להפניה. כלומר, הוא שואב מידע מהפניה לתא או טווח אחר. הוא יוצר הפניה מטקסט, ואינו משתנה כאשר תאים, שורות או עמודות משתנים, מוסיפים או מסירים מטווח מצוטט.ההפניות שהוא יוצר מוערכים בזמן אמת, כך שההפניה תמיד מדויקת לנתונים שמהם היא שואבת.
אם זה מרגיש קצת מבלבל, אל תדאג. הנוסחה העקיפה יכולה להיות קלה יותר להבנה עם דוגמאות תקפות ובפועל. אם יש לך ספק, בצע את השלבים הבאים, ובקרוב תוכל להבין את זה.
שימוש בפונקציה העקיפה עם טווחים בעלי שם
טווחים בעלי שם ב-Excel הם דרך מצוינת לאסוף נתונים תחת הפניה אחת, והפונקציה העקיפה מקלה במעט על הוצאת המידע הזה מהם. הנה איך לעשות את זה:
-
פתח מסמך Excel עם טווחים בעלי שם שכבר הוחלו. בדוגמה שלנו, יש לנו מידע מכירות ממאכלים ומשקאות שונים, כשהכסף שהרוויח בכל יום בשבוע נאסף תחת טווחים בעלי שם המכונים על שם המוצרים.
Image -
בחר תא עבור הטווח הנקוב שלך, והזן אחד מהם לתוכו. בדוגמה שלנו, השתמשנו ב- Burgers. אם תרצה, הוסף כותרות מכוונות אחרות וצביעה.
Image -
בחר תא אחר שאליו תרצה שהפלט העקיף יעבור. מכיוון שאנו מחפשים לחבר את כל נפח המכירות מהשבוע עבור מזון ספציפי, במקרה זה בורגרים, נקליד את הדברים הבאים בתא:
=SUM(INDIRECT(G5)
-
זה מציין פונקציה SUM, שתשתמש בפונקציה העקיפה כדי לצייר את המידע מהטווח הנקוב בתא G5, במקרה זה Burgers. התפוקה היא 3781, סך כל המכירות לשבוע עבור בורגרים.
בדוגמה שלנו, אנחנו יכולים להחליף את המבורגרים בתא G5 בלימונדה או קינוחים, שני הטווחים האחרים בעלי השם, והפלט ישתנה ל-SUM הכולל שלהם במקום זאת.
Image
שימוש בפונקציה העקיפה בגיליונות מרובים
הנוסחה העקיפה חזקה עוד יותר כאשר אתה משתמש בה כדי לשלוף מידע מגיליונות אחרים. אתה גם לא צריך להשתמש בטווחים בעלי שם כדי לעשות זאת.
- פתח את מסמך ה-Excel שלך עם גיליונות מרובים, או צור אותם עם כל המידע הדרוש.
-
בגיליון שבו אתה רוצה שהפלט העקיף יעבור, צור תא עם שם הגיליון שממנו אתה רוצה לשאוב מידע. בדוגמה שלנו, זה FoodSales.
-
מכיוון שאנו רוצים לשלוף מידע מהגיליון FoodSales שלנו כדי לסכם את המספר של Burgers שנמכרו, הקלדנו את הדברים הבאים (החלף את טווח תאים ושמות גיליון עם שמות שלך:
=SUM(INDIRECT(B4&"!B4:B10"))
Image - זה מייעד אותה כפונקציית SUM, מכיוון שאנו מנסים למצוא סכום כולל. לאחר מכן הוא מייעד את תא B4 כטקסט ההתייחסות לפונקציה העקיפה. ה- & מפגיש את הרכיבים של פונקציה זו, ואחריו ציטוט וסימן קריאה, ולאחר מכן את טווח התאים שמהם אנחנו רוצים לצייר את הנתונים. B4 עד B10
- הפלט הוא סך המכירות של בורגר לאותו שבוע. כאשר אנו יוצרים גיליון FoodSales2 חדש לשבוע חדש עם מספרים שונים, עלינו להתאים רק את תא B4 כדי לומר FoodSales2 כדי לקבל את הנתונים על מכירות בורגר לאותו שבוע.
שימוש בפונקציה העקיפה עם התייחסות לסגנון R1C1
עבור גיליונות שמתרחבים ללא הרף, שבהם ההפניה שבה ברצונך להשתמש לא תהיה תמיד באותו תא, ניתן להשתמש בהפניות בסגנון R1C1 עם הנוסחה העקיפה כדי לתת לך את המידע שאתה צריך.נמשיך להשתמש בדוגמאות של מכירות המזון שלנו כאן, אבל תארו לעצמכם שזה עבור גיליון עבודה ברמה גבוהה יותר שמסתכל על סך המכירות השבועיות.
- פתח את מסמך ה-Excel עם כל הנתונים שמהם ברצונך לצייר ובחר תא לפלט הפונקציה העקיפה שלך. בדוגמה שלנו, אנו מסתכלים על סכומי מכירות מזון חודשיים ואנו רוצים לדעת את סך המכירות האחרון עבור החודש.
-
בדוגמה שלנו, הנוסחה נראית כך:
=INDIRECT("R12C"&COUNTA(12:12), FALSE)
Image - הפונקציה העקיפה משתמשת ב-R12 (שורה 12) ואחריה C כדי לציין עמודה, מוקפת במירכאות. ה- & מחבר את שני חלקי הפונקציה יחד. אנו משתמשים בפונקציה COUNTA כדי לספור את כל התאים הלא ריקים בשורה 12 (בחירת השורה או הקלדת 12:12), ואחריהם פסיק. FALSE מציין זאת כהפניה R1C1.
-
הפלט הוא אז הערך האחרון בטבלה שלנו, במקרה זה 8102, או $8, 102. כאשר בסופו של דבר נוסיף את נתוני המכירות של אפריל, מספר המכירות האחרון יתעדכן אוטומטית בזמן אמת.
Image
