VLOOKUP עם ערך לחיפוש משתנה, אפשרי בלי IF?
-
קובץ הנתונים (מקור) בנוי כך:
שם
כתובת
טלפון 1
טלפון 2
טלפון 4
טלפון 5דו"ח הקמפיין בנוי כך:
טלפון
תוצאהבעבר כשהיה לי רק 2 טלפונים ללקוח הייתי משתמש עם IFERRORE ופעמיים VLOOKUP
=IFERROR(VLOOKUP(AD:AD,'קמפיין'!B:C,2,0),VLOOKUP(AE:AE,'קמפיין'!B:C,2,0))
האם יש דרך לבצע את זה על 5 עמודות של טלפונים בלי IF שמתארך?
-
קובץ הנתונים (מקור) בנוי כך:
שם
כתובת
טלפון 1
טלפון 2
טלפון 4
טלפון 5דו"ח הקמפיין בנוי כך:
טלפון
תוצאהבעבר כשהיה לי רק 2 טלפונים ללקוח הייתי משתמש עם IFERRORE ופעמיים VLOOKUP
=IFERROR(VLOOKUP(AD:AD,'קמפיין'!B:C,2,0),VLOOKUP(AE:AE,'קמפיין'!B:C,2,0))
האם יש דרך לבצע את זה על 5 עמודות של טלפונים בלי IF שמתארך?
-
@OdedDvir צודק, סורי..
את התוצאה בקמפיין כמובן. הערך החד ערכי בקמפיין הוא טלפון, הבעיה שבקובץ מקור הטלפון יכול להיות ב1 מתוך 5 עמודות שונות, אפשר לעשות if קצת ארוך אבל זה מסורבל.@Sfsf3925 אפשר לחפש על ידי
MATCH
בכל עמודה, ולצרף את מערכי התוצאות. לדוגמא:
הנוסחא בתא
G2
היא:=IFERROR(INDEX(K:K,MATCH(1,(B2=J:J)+(C2=J:J)+(D2=J:J)+(E2=J:J),0)),"אין תוצאה")
הסבר:
INDEX
מאחזר ערך מתוך עמודה לפי אינדקס.MATCH
מחזיר את אינדקס הערך המבוקש בטווח.
הערך המבוקש כאן הוא1
(כפי שיוסבר מיד). הטווח כאן הוא חיבור של טווחים (מערכים בוליאנים), שמתקבלים על ידי השוואת העמודה של הטלפוניםJ:J
עם מספר מסויים, כאשרB2
הוא המספר הראשון,C2
הוא המספר השני וכו'.
החיבור הזה יצור מערך יחיד המכיל אפסים (כי חיבור של בוליאני נותן 0 או 1) מלבד האינדקס שבו נמצאה ההתאמה ובו יופיע הערך1
.
למשל עבור השורה של אברהם (הראשונה עם הנתונים), המערך שיתקבל בסוף הוא:
{0,0,1,0,0,0}
(יש 6 איברים כי לא התעלמתי משורת הכותרת)
ולכן הנוסחאMATCH(1,{0,0,1,0,0,0},0)
תחזיר את הערך 3 לפונקציהINDEX
, מה שיגרום לה לרוץ כך:INDEX(K:K,3)
ולהחזיר את הערך:
1000
. -
@Sfsf3925 אפשר לחפש על ידי
MATCH
בכל עמודה, ולצרף את מערכי התוצאות. לדוגמא:
הנוסחא בתא
G2
היא:=IFERROR(INDEX(K:K,MATCH(1,(B2=J:J)+(C2=J:J)+(D2=J:J)+(E2=J:J),0)),"אין תוצאה")
הסבר:
INDEX
מאחזר ערך מתוך עמודה לפי אינדקס.MATCH
מחזיר את אינדקס הערך המבוקש בטווח.
הערך המבוקש כאן הוא1
(כפי שיוסבר מיד). הטווח כאן הוא חיבור של טווחים (מערכים בוליאנים), שמתקבלים על ידי השוואת העמודה של הטלפוניםJ:J
עם מספר מסויים, כאשרB2
הוא המספר הראשון,C2
הוא המספר השני וכו'.
החיבור הזה יצור מערך יחיד המכיל אפסים (כי חיבור של בוליאני נותן 0 או 1) מלבד האינדקס שבו נמצאה ההתאמה ובו יופיע הערך1
.
למשל עבור השורה של אברהם (הראשונה עם הנתונים), המערך שיתקבל בסוף הוא:
{0,0,1,0,0,0}
(יש 6 איברים כי לא התעלמתי משורת הכותרת)
ולכן הנוסחאMATCH(1,{0,0,1,0,0,0},0)
תחזיר את הערך 3 לפונקציהINDEX
, מה שיגרום לה לרוץ כך:INDEX(K:K,3)
ולהחזיר את הערך:
1000
.@OdedDvir מדהים! חידש לי ממש.
הערה חשובה:
לפחות לגרסאות היותר ישנות, לחישוב נכון של הנוסחה צריך להקיש בסיום כתיבת הנוסחה CTRL+SHIFT+ENTER לקבלת החישוב כנוסחת מערך. -
@Sfsf3925 אפשר לחפש על ידי
MATCH
בכל עמודה, ולצרף את מערכי התוצאות. לדוגמא:
הנוסחא בתא
G2
היא:=IFERROR(INDEX(K:K,MATCH(1,(B2=J:J)+(C2=J:J)+(D2=J:J)+(E2=J:J),0)),"אין תוצאה")
הסבר:
INDEX
מאחזר ערך מתוך עמודה לפי אינדקס.MATCH
מחזיר את אינדקס הערך המבוקש בטווח.
הערך המבוקש כאן הוא1
(כפי שיוסבר מיד). הטווח כאן הוא חיבור של טווחים (מערכים בוליאנים), שמתקבלים על ידי השוואת העמודה של הטלפוניםJ:J
עם מספר מסויים, כאשרB2
הוא המספר הראשון,C2
הוא המספר השני וכו'.
החיבור הזה יצור מערך יחיד המכיל אפסים (כי חיבור של בוליאני נותן 0 או 1) מלבד האינדקס שבו נמצאה ההתאמה ובו יופיע הערך1
.
למשל עבור השורה של אברהם (הראשונה עם הנתונים), המערך שיתקבל בסוף הוא:
{0,0,1,0,0,0}
(יש 6 איברים כי לא התעלמתי משורת הכותרת)
ולכן הנוסחאMATCH(1,{0,0,1,0,0,0},0)
תחזיר את הערך 3 לפונקציהINDEX
, מה שיגרום לה לרוץ כך:INDEX(K:K,3)
ולהחזיר את הערך:
1000
. -
@OdedDvir אמר ב[VLOOKUP עם ערך לחיפוש משתנה, אפשרי בלי IF?]
גם אני נפעם...
מה זה התחביר הזה:
(B2=J:J)?
מה זה אומר?@dovid אמר בVLOOKUP עם ערך לחיפוש משתנה, אפשרי בלי IF?:
מה זה התחביר הזה:
(B2=J:J)?זו נוסחת מערך, פיצ'ר מאוד שימושי שנכנס בשנת 2018.
הרעיון הוא שנוסחא יכולה מעתה להחזיר מערך של תוצאות, ולא רק ערך יחיד.
הנוסחה המסויימת הזו היא להשוואה שלB2
מול כל העמודהJ
. הערך שיוחזר הוא מערך בוליאני של השוואתB2
מול כל ערך בעמודהJ
.
במקרה הזה הנוסחא לא כ"כ יעילה, כי לא צריך להשוות ממש את כל העמודה, אלא רק את 5 התאים בעלי הערך. אבל קיצרתי כדי שיהיה מובן. יותר טוב לכתוב כך:B2=J2:J6
וכן הלאה.
בגרסאות הראשונות של נוסחאות המערך, היה צורך להקליד אותן בצורה מתוסבכת עם
Shift+Enter
כמו שציין @Y-Excel-Access, והן הופיעו בשורת הנוסחא עם סוגריים מסולסלות. כיום אקסל חוסך מאיתנו את כאב הראש הזה ואפשר להקליד כמו נוסחא רגילה.תוכל לנסות על ידי הקלדה של הנוסחא
B2=J2:J6
בתוך תא כלשהו (מחוץ לטבלה) ולראות שמתקבל מערך בוליאני.