Excel: פונקציית הפניה למידע ספציפי

תוכן העניינים:

Anonim

כך נעשה שימוש נכון בפונקציית Excel

האם יש ברשותך כמות עצומה של נתונים וזקוק למידע דחוף על ערך מסוים המוזן בטבלה שלך? בשלב זה הגיוני להשתמש בפונקציית ההתייחסות של Excel. הסיבה לכך היא שהיא מאפשרת לקבוע את המידע הנדרש באמצעות קריטריון חיפוש ואזורי תאים בהם יש לחפש את הערך הנדרש. זה פועל באופן אוטומטי ומאפשר לבקש מידע במהירות. אם ברצונך לבצע חיפוש ממוקד יותר, עליך להשתמש בפונקציות ההתייחסות המשופרות Vreference, Wreference או XVreference. Excel מציעה חלופות נוספות לארגון ושליפת נתונים.

אופן השימוש בפונקציית ההתייחסות של Excel

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

כיצד בנויה פונקציית ההתייחסות של Excel בגרסת הווקטור?

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

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

חפש בעמודות - דוגמא

ברשותך רשימה של מספרי מאמרים בעמודה A עם פריטי הלבוש המתאימים בעמודה B. אתה מחפש מספר מאמר ספציפי ורוצה לברר עם איזה פריט לבוש עובר מספר המאמר.

בחר שורה ריקה.

הכנס את הנוסחה = reference ("ART7492031"; A2: A6; B2: B6).

לכן הפונקציה מחפשת את מספר המאמר בעמודה A ומוציאה את התוצאה מעמודה B, למשל "בסך הכל".

תחביר הפניה לנוסחה בגרסת המטריצה מכיל פחות ארגומנטים

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

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

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

חפש בעמודות - גרסת וקטור: דוגמא

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

בחר שורה ריקה.

הכנס את הנוסחה = reference ("ART7492031"; A2: B6).

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

יש לקחת זאת בחשבון בעת שימוש בפונקציית ההתייחסות

הערכים ב חיפוש וקטור יש לסדר את שתי הגרסאות בסדר עולה. זה אומר למשל למספרים -1, 0, 1, 2, 3, וכו ', לערכי טקסט א ב ג, וכו 'ולערכי אמת לא נכון בא אחריו נָכוֹן. הטקסט אינו רגיש לאותיות. אם לא ניתן למצוא ערך בהתאם לקריטריון החיפוש, פונקציית ההתייחסות של Excel לוקחת ערך חיפוש וקטורשמתקרב לערך בקריטריון החיפוש. אם הערך של קריטריון החיפוש קטן מזה שבאזור החיפוש, Excel מחזיר את ערך השגיאה #NV.

חיפוש מהיר עם שלושת פונקציות ההתייחסות של Excel

משתמשים שאינם משתמשים בפונקציית הקישור של Excel מטעמי תאימות, צריכים להשתמש ברפרנס, ב- Wreference או בגרסה המשופרת XLOOKUP. הם מאפשרים חיפוש מדויק יותר של ערכים באמצעות, למשל, תווים כלליים.

חפש עמודות או טבלה לפי רפרנס

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

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

השתמש בהפניה כפונקציית התייחסות של Excel וחפש בין השורות

בניגוד ל- VLOOKUP, HLOOKUP מחפש אופקית. לכן פונקציית הפניה זו של Excel מחפשת טור אחר עמודה בשורה מוגדרת אחר קריטריון החיפוש בגליון העבודה. הפונקציה HLOOKUP כוללת את התכונות הבאות:

  • 3 טיעוני חובה (קריטריון חיפוש; מַטרִיצָה; אינדקס קו)
  • טיעון אופציונלי אחד (אזור_התייחסות)
  • חיפוש ספציפי בטבלה ברמה
  • שימוש בתווים כלליים כקריטריוני חיפוש

חיפוש לכל הכיוונים מאפשר XVreference

ל- Microsoft 365 יש פונקציית התייחסות משופרת של Excel XLOOKUP, המשלבת את הפונקציות של VLOOKUP ו- HLOOKUP. בעזרת הנוסחה, ניתן לחפש בגליון עבודה קריטריון חיפוש ללא קשר למקום שבו נמצאת עמודת התוצאה. פונקציית ההתייחסות הזו קצת יותר מסובכת עם 6 ארגומנטים, אך היא מאפשרת למשתמשי Excel חיפוש מפורט ודינאמי לכל הכיוונים.

סקירה כללית של נוסחאות מטריצה אחרות להערכת טבלה

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

פונקציות Excel אחרות שיכולות לסייע בארגון והערכת המידע:

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

פונקציית ההתייחסות של Excel מאפשרת לחפש מידע במהירות

אם יש לקבוע נתונים מתוך עמודה מסוימת, שורות שחיפשו או ערך תוצאה עבור ערך החיפוש המשויך, יש להוציא את פונקציות ההתייחסות ב- Excel במגוון דרכים. משתמשים יכולים לבחור בין פונקציית ההתייחסות של Excel, התואמת תוכניות גיליון אלקטרוני אחרות, או הפונקציות הנרחבות יותר Vreference או Wreference. למשתמשים מתקדמים, XVerweis מתאים לחיפוש ממוקד יותר המשלב את הפונקציות של SVerweis ו- Wreweis. ניתן להשתמש בפונקציות אחרות של Excel לארגון נתונים, כגון מספרים אוטומטיים של שורות או החזרת מיקום התא של ערך.