דוגמאות מתקדמות של VLOOKUP: חיפוש ריבוי קריטריונים

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

• חפש באקסל לפי מספר קריטריונים
• אנו מחלצים את הערכים השני, השלישי וכו' באמצעות VLOOKUP
• אחזר את כל החזרות על הערך הרצוי
• חיפוש XNUMXD לפי שורה ועמודה ידועים
• שימוש במספר VLOOKUPs בנוסחה אחת
• החלפה דינמית של נתונים מטבלאות שונות

חפש באקסל לפי מספר קריטריונים

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

דוגמה 1: חפש לפי 2 קריטריונים שונים

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

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

=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)

- נוסחה זו תחזיר את התוצאה 15התואם למוצר תפוחים, כי זה הערך הראשון שמתאים.

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

אז אתה מוסיף עמודת עזר לטבלה ומעתיק את הנוסחה הבאה על כל התאים שלה: =B2&C2. אם אתה רוצה שהמחרוזת תהיה יותר קריאה, אתה יכול להפריד את הערכים המשולבים עם רווח: =B2&» «&C2. לאחר מכן, תוכל להשתמש בנוסחה הבאה:

=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)

=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)

or

=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)

איפה התא B1 מכיל את הערך המשורשר של הארגומנט lookup_value (ערך_חיפוש) ו 4 – טיעון col_index_num (column_number), כלומר מספר העמודה המכילה את הנתונים שיש לאחזר.

דוגמה 2: VLOOKUP לפי שני קריטריונים כשהטבלה מוצגת בגיליון אחר

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

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

אז הנוסחה עם VPR יכול להיות ככה:

=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)

=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)

כאן, עמודות B ו-C מכילות שמות לקוחות ושמות מוצרים, בהתאמה, ואת הקישור הזמנות!$A&$2:$D$2 מגדיר טבלה לחיפוש בגיליון אחר.

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

=VLOOKUP(B2&" "&C2,Orders,4,FALSE)

=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)

כדי שהנוסחה תעבוד, יש לשלב את הערכים בעמודה השמאלית ביותר של הטבלה בה אתה מסתכל בדיוק באותו אופן כמו בקריטריוני החיפוש. באיור שלמעלה, שילבנו את הערכים u2bu2band שמנו רווח ביניהם, באותו אופן שאתה צריך לעשות בארגומנט הראשון של הפונקציה (BXNUMX& "" & CXNUMX).

זכור! פונקציה VPR מוגבל ל-255 תווים, הוא לא יכול לחפש ערך שאורכו יותר מ-255 תווים. יש לזכור זאת ולוודא שאורך הערך הרצוי אינו חורג מהמגבלה הזו.

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

אנו מחלצים את הערכים השני, השלישי וכו' באמצעות VLOOKUP

אתה כבר יודע את זה VPR יכול להחזיר רק ערך תואם אחד, ליתר דיוק, הראשון שנמצא. אבל מה אם הערך הזה חוזר על עצמו כמה פעמים במערך הנצפים, ואתה רוצה לחלץ את ה-2 או ה-3 מהם? מה אם כל הערכים? הבעיה נראית מסובכת, אבל הפתרון קיים!

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

הדרך הקלה ביותר היא להוסיף עמודת עזר לפני העמודה שם לקוח ומלא אותו בשמות לקוחות עם מספר החזרה של כל שם, למשל, ג'ון דו 1, ג'ון דו 2 וכו' נעשה את הטריק עם המספור באמצעות הפונקציה COUNTIF (COUNTIF), בהינתן ששמות הלקוחות נמצאים בעמודה B:

=B2&COUNTIF($B$2:B2,B2)

=B2&СЧЁТЕСЛИ($B$2:B2;B2)

לאחר מכן תוכל להשתמש בפונקציה הרגילה VPRכדי למצוא את ההזמנה הנדרשת. לדוגמה:

• 2 פריט שהוזמן על ידי הלקוח דן בראון:

  =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ)

• 3 פריט שהוזמן על ידי הלקוח דן בראון:

  =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)

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

אם אתה רק מחפש 2-e חזרה, אתה יכול לעשות זאת ללא עמודת העזר על ידי יצירת נוסחה מורכבת יותר:

=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")

בנוסחה זו:

• $F$2 – תא המכיל את שם הקונה (הוא ללא שינוי, שימו לב – הקישור הוא מוחלט);
• $ B $ - טור שם לקוח;
• טבקס - השולחן שלך (מקום זה יכול להיות גם טווח רגיל);
• $ C16 - תא הקצה של הטבלה או הטווח שלך.

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

אם אתה צריך רשימה של כל ההתאמות - הפונקציה VPR זה לא עוזר, מכיוון שהוא מחזיר רק ערך אחד בכל פעם - נקודה. אבל לאקסל יש פונקציה מדד (INDEX), שיכול להתמודד בקלות עם משימה זו. איך תיראה נוסחה כזו, תלמדו בדוגמה הבאה.

אחזר את כל החזרות על הערך הרצוי

כמוזכר לעיל VPR לא יכול לחלץ את כל הערכים הכפולים מהטווח הסרוק. לשם כך יש צורך בנוסחה קצת יותר מורכבת, המורכבת ממספר פונקציות של אקסל, כגון מדד (אינדקס), קָטָן (קטן) ו שׁוּרָה (קַו)

לדוגמה, הנוסחה שלהלן מוצאת את כל החזרות על הערך מתא F2 בטווח B2:B16 ומחזירה את התוצאה מאותן שורות בעמודה C.

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}

{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}

הזן את נוסחת המערך הזו למספר תאים סמוכים, כגון התאים F4: F8כפי שמוצג באיור למטה. מספר התאים חייב להיות שווה או גדול ממספר החזרות המקסימלי האפשרי של הערך בחיפוש. אל תשכח ללחוץ Ctrl + Shift + Enterכדי להזין את נוסחת המערך בצורה נכונה.

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

חלק 1:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")

ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")

$F$2=B2:B16 - השווה את הערך בתא F2 עם כל אחד מהערכים בטווח B2:B16. אם נמצא התאמה, אז הביטוי STRING(C2:C16)-1 מחזירה את המספר של השורה המתאימה (ערך -1 מאפשר לך לא לכלול את שורת הכותרת). אם אין התאמות, הפונקציה IF (IF) מחזירה מחרוזת ריקה.

תוצאת פונקציה IF (IF) יהיה מערך אופקי כזה: {1,"",3,"",5,"","","","","","",12,"","",""}

חלק 2:

ROW()-3

СТРОКА()-3

כאן הפונקציה שׁוּרָה (LINE) פועל כמונה נוסף. מכיוון שהנוסחה מועתקת לתאים F4:F9, אנו מפחיתים את המספר 3 מתוצאת הפונקציה לקבלת ערך 1 בתא F4 (שורה 4, הורידו 3) כדי לקבל 2 בתא F5 (שורה 5, הורידו 3) וכן הלאה.

חלק 3:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

פונקציה קָטָן (SMALL) חוזר לא אה הערך הקטן ביותר במערך הנתונים. במקרה שלנו, איזה מיקום (מהקטן) להחזיר נקבע על ידי הפונקציה שׁוּרָה (LINE) (ראה חלק 2). אז, עבור תא F4 פונקציה SMALL({מערך},1) החזרות 1 (הקטן ביותר) אלמנט מערך, כלומר 1. לתא F5 החזרות 2 האלמנט הקטן ביותר במערך, כלומר 3, וכו '

חלק 4:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

פונקציה מדד (INDEX) פשוט מחזיר את הערך של תא ספציפי במערך C2:C16. לתא F4 פונקציה INDEX($C$2:$C$16) יחזור תפוחיםבעד F5 פונקציה INDEX($C$2:$C$16) יחזור דִברֵי מְתִיקָה' וכן הלאה.

חלק 5:

IFERROR()

ЕСЛИОШИБКА()

לבסוף, שמים את הנוסחה בתוך הפונקציה טעות (IFERROR), כי לא סביר שתהיה מרוצה מהודעת השגיאה #בְּ (#N/A) אם מספר התאים שאליהם מועתקת הנוסחה קטן ממספר הערכים הכפולים בטווח הנצפה.

חיפוש XNUMXD לפי שורה ועמודה ידועים

ביצוע XNUMXD חיפוש ב-Excel כרוך בחיפוש אחר ערך לפי שורה ומספר עמודה ידועים. במילים אחרות, אתה מחלץ את ערך התא בצומת של שורה ועמודה מסוימת.

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

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

פונקציות VLOOKUP ו-MATCH

אתה יכול להשתמש בכמה פונקציות VPR (VLOOKUP) ו יותר חשוף (MATCH) כדי למצוא את הערך בצומת השדות שם המוצר (מחרוזת) ו חוֹדֶשׁ (עמודה) של המערך המדובר:

=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)

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

MATCH("Mar",$A$1:$I$1,0)

ПОИСКПОЗ("Mar";$A$1:$I$1;0)

בתרגום לשפה אנושית, נוסחה זו פירושה:

• אנחנו מחפשים את הדמויות "מאר" - טיעון lookup_value (ערך_חיפוש);
• הסתכלות בתאים מ-A1 עד I1 - ארגומנט מערך_חיפוש (מערך_חיפוש);
• החזרת התאמה מדויקת - ארגומנט התאמה_סוג (סוג_התאמה).

שימוש 0 בארגומנט השלישי, אתה אומר פונקציות יותר חשוף חפש את הערך הראשון שתואם בדיוק את הערך שאתה מחפש. זה שווה ערך לערך שֶׁקֶר (FALSE) עבור הטיעון הרביעי VPR.

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

פונקציית SUMPRODUCT

פונקציה SUMPRODUCT (SUMPRODUCT) מחזירה את סכום התוצרים של המערכים שנבחרו:

=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)

=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)

פונקציות INDEX ו-MATCH

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

=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))

טווחים בעלי שם ואופרטור הצומת

אם אינך בעניין של כל הנוסחאות המורכבות של Excel, אולי תאהב את הדרך הוויזואלית והבלתי נשכחת הזו:

1. בחר את הטבלה, פתח את הכרטיסייה נוסחות (נוסחאות) ולחץ צור מתוך בחירה (צור מתוך בחירה).
2. סמן את התיבות שורה עליונה (בשורה למעלה) ו עמודה שמאלית (בעמודה משמאל). Microsoft Excel יקצה שמות לטווחים מהערכים בשורה העליונה ובעמודה השמאלית של הגיליון האלקטרוני שלך. כעת אתה יכול לחפש באמצעות שמות אלה ישירות מבלי ליצור נוסחאות.
3. בכל תא ריק, כתוב =row_name column_name, למשל כך:

   =לימונים מר

   … או להפך:

   =מאר לימונים

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

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

4. חדשות ועדכונים זן ולבדוק את התוצאה

באופן כללי, כל אחת מהשיטות הנ"ל שתבחר, התוצאה של חיפוש דו מימדי תהיה זהה:

שימוש במספר VLOOKUPs בנוסחה אחת

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

בואו נסתכל על הדוגמה הבאה. יש לנו טבלה ראשית עם עמודה מק"ט (חדש), שבו ברצונך להוסיף עמודה עם המחירים המתאימים מטבלה אחרת. בנוסף, יש לנו 2 טבלאות חיפוש. הראשון (טבלת חיפוש 1) מכיל מספרים מעודכנים מק"ט (חדש) ושמות מוצרים, והשני (טבלת חיפוש 2) – שמות מוצרים ומספרים ישנים מק"ט (ישן).

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

1. כתוב פונקציה VPR, אשר מוצא את שם המוצר בטבלה טבלת חיפוש 1באמצעות מק"ט, כערך הרצוי:

   =VLOOKUP(A2,New_SKU,2,FALSE)

   =ВПР(A2;New_SKU;2;ЛОЖЬ)

   כאן חדש_מק"ט – טווח בשם $A:$B בשולחן טבלת חיפוש 1, 2 – זוהי עמודה B, המכילה את שמות הסחורה (ראה בתמונה למעלה)

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

   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

   =ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)

   כאן מחיר – טווח בשם $A:$C בשולחן טבלת חיפוש 2, 3 הוא עמודה C המכילה מחירים.

האיור שלהלן מציג את התוצאה שהוחזרה על ידי הנוסחה שיצרנו:

החלפה דינמית של נתונים מטבלאות שונות באמצעות VLOOKUP ו- INDIRECT

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

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

תאר לעצמך שיש לך דוחות מכירות עבור מספר אזורים עם אותם מוצרים ובאותו פורמט. ברצונך למצוא נתוני מכירות עבור אזור ספציפי:

אם יש לך רק שני דוחות כאלה, אז אתה יכול להשתמש בנוסחה פשוטה להחריד עם פונקציות VPR и IF (IF) כדי לבחור את הדוח הרצוי לחיפוש:

=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)

איפה:

• $ D $2 הוא תא המכיל את שם המוצר. שימו לב שאנו משתמשים בהפניות מוחלטות כאן כדי להימנע משינוי ערך הבדיקה בעת העתקת הנוסחה לתאים אחרים.
• $D3 הוא תא עם שם האזור. אנו משתמשים בהפניה מוחלטת לעמודה ובהפניה לשורה יחסית מכיוון שאנו מתכננים להעתיק את הנוסחה לתאים אחרים באותה עמודה.
• FL_Sales и CA_מכירות – שמות הטבלאות (או טווחים בעלי שם) המכילות את דוחות המכירות המתאימים. אתה יכול, כמובן, להשתמש בשמות הגיליון הרגילים והפניות לטווח תאים, למשל 'FL Sheet'!$A$3:$B$10, אבל טווחים בעלי שם הם הרבה יותר נוחים.

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

כפי שאתה בוודאי יודע, הפונקציה עקיף משמש להחזרת קישור שניתן על ידי מחרוזת טקסט, וזה בדיוק מה שאנחנו צריכים עכשיו. אז, החלף באומץ את הביטוי בנוסחה לעיל בפונקציה IF לקשר עם פונקציה עקיף. הנה שילוב VPR и עקיף עובד מצוין עם:

=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)

איפה:

• $ D $2 – זהו תא עם שם המוצר, הוא ללא שינוי עקב הקישור המוחלט.
• $D3 הוא התא המכיל את החלק הראשון של שם האזור. בדוגמה שלנו, זה FL.
• _מכירות – החלק המשותף של השם של כל הטווחים או הטבלאות הנקובים בשמות. בשילוב עם הערך בתא D3, הוא יוצר את השם המלא של הטווח הנדרש. להלן כמה פרטים למי שחדש בפונקציה עקיף.

איך INDIRECT ו-VLOOKUP עובדים

ראשית, הרשו לי להזכיר לכם את התחביר של הפונקציה עקיף (עקיף):

INDIRECT(ref_text,[a1])

ДВССЫЛ(ссылка_на_текст;[a1])

הארגומנט הראשון יכול להיות הפניה לתא (סגנון A1 או R1C1), שם טווח או מחרוזת טקסט. הארגומנט השני קובע איזה סגנון קישור כלול בארגומנט הראשון:

• A1אם הוויכוח הוא קוד אמיתי (TRUE) או לא צוין;
• R1C1, אם FAS E (שֶׁקֶר).

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

אז בואו נחזור לדוחות המכירות שלנו. אם אתה זוכר, אז כל דוח הוא טבלה נפרדת שנמצאת בגיליון נפרד. כדי שהנוסחה תפעל כהלכה, עליך לתת שמות לטבלאות (או לטווחים), ולכל השמות צריך להיות חלק משותף. לדוגמה, כך: CA_מכירות, FL_מכירות, TX_מכירות וכן הלאה. כפי שאתה יכול לראות, "_Sales" קיים בכל השמות.

פונקציה עקיף מחבר את הערך בעמודה D ואת מחרוזת הטקסט "_Sales", ובכך אומר VPR באיזו טבלה לחפש. אם תא D3 מכיל את הערך "FL", הנוסחה תחפש בטבלה FL_מכירות, אם "CA" - בטבלה CA_מכירות וכן הלאה.

התוצאה של הפונקציות VPR и עקיף יהיו הדברים הבאים:

אם הנתונים ממוקמים בספרי Excel שונים, עליך להוסיף את שם הספר לפני הטווח הנקוב, לדוגמה:

=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)

אם הפונקציה עקיף מתייחס לחוברת עבודה אחרת, חוברת עבודה זו חייבת להיות פתוחה. אם הוא סגור, הפונקציה תדווח על שגיאה. #REF! (#SSYL!).