תוכן
מדריך קטן זה מסביר כיצד ליצור פונקציה VPR (VLOOKUP) תלוי רישיות, מציג מספר נוסחאות אחרות ש-Excel יכול לחפש בהן בצורה תלוית רישיות, ומצביע על החוזקות והחולשות של כל פונקציה.
אני מניח שכל משתמש אקסל יודע איזו פונקציה מבצעת חיפוש אנכי. נכון, זו פונקציה VPR. עם זאת, מעט אנשים יודעים זאת VPR אינו תלוי רישיות, כלומר התווים הקטנים והגדולים זהים עבורו.
הנה דוגמה מהירה המדגימה את חוסר היכולת VPR להכיר ברישום. נניח בתא A1 מכיל את הערך "bill" ואת התא A2 – "שטר", נוסחה:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... יפסיק את החיפוש שלו על "bill" מכיוון שהערך הזה מגיע ראשון ברשימה, ויחלץ את הערך מהתא B1.
בהמשך מאמר זה, אני אראה לך איך לעשות VPR רגיש רישיות. בנוסף, נלמד עוד כמה פונקציות שיכולות לבצע חיפושים תלויי רישיות באקסל.
נתחיל מהפשוט ביותר - נוף (חיפוש) ו SUMPRODUCT (SUMPRODUCT), אשר, למרבה הצער, יש להם מספר מגבלות משמעותיות. לאחר מכן, נסתכל מקרוב על הנוסחה המעט יותר מורכבת INDEX+MATCH (INDEX+MATCH), שעובד ללא דופי בכל מצב ועם כל מערך נתונים.
פונקציית VLOOKUP היא תלוית רישיות
כפי שאתה כבר יודע, הפונקציה הרגילה VPR אינו רגיש לאותיות גדולות. עם זאת, יש דרך להפוך אותו לרגיש לאותיות גדולות. לשם כך, עליך להוסיף עמודת עזר לטבלה, כפי שמוצג בדוגמה הבאה.
נניח בטור B יש מזהי מוצר (פריט) ואתה רוצה לחלץ מהעמודות את מחיר המוצר ואת ההערה המתאימה C и D. הבעיה היא שמזהים מכילים גם אותיות קטנות וגם אותיות רישיות. לדוגמה, ערכי תאים B4 (001Tvci3u) ו B5 (001Tvci3U) נבדלים רק במקרה של התו האחרון, u и U בהתאמה.
כפי שאתה יכול לדמיין, נוסחת החיפוש הרגילה
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
יחזור $ 90, מאז הערך 001Tvci3u נמצא בטווח החיפוש מוקדם יותר מ 001Tvci3U. אבל זה לא מה שאנחנו צריכים, נכון?
לחיפוש עם פונקציה VPR ברגישות רישיות של Excel, תצטרך להוסיף עמודת מסייעת ולמלא את התאים שלה בנוסחה הבאה (כאשר B היא עמודת החיפוש):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
נוסחה זו מפרקת את הערך הרצוי לתווים נפרדים, ומחליפה כל תו בקוד שלו (לדוגמה, במקום A בגיל 65, במקום זאת a קוד 97) ולאחר מכן משלב את הקודים הללו למחרוזת מספרים ייחודית.
לאחר מכן, אנו משתמשים בפונקציה פשוטה VPR לחיפוש תלוי רישיות:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
פעולה תקינה של הפונקציה VPR תלוי רישיות תלוי בשני גורמים:
- עמודת העזר חייבת להיות העמודה השמאלית ביותר בטווח הניתן לצפייה.
- הערך שאתה מחפש חייב להכיל קוד תו במקום הערך האמיתי.
כיצד להשתמש נכון בפונקציית CODE
הנוסחה שהוכנסה לתאים של עמודת העזר מניחה שלכל ערכי החיפוש שלך יש אותו מספר תווים. אם לא, אז אתה צריך לדעת את המספרים הקטנים והגדולים ביותר ולהוסיף כמה שיותר תכונות טעות (IFERROR) כמה תווים הוא ההבדל בין ערך החיפוש הקצר והארוך ביותר.
לדוגמה, אם ערך החיפוש הקצר ביותר הוא 3 תווים והארוך ביותר הוא 5 תווים, השתמש בנוסחה זו:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
לתפקוד PSTR (MID) אתה מספק את הטיעונים הבאים:
- טיעון ראשון - טֶקסט (טקסט) הוא הטקסט או הפניה לתא המכילים את התווים שיש לחלץ (במקרה שלנו זה B2)
- טיעון ראשון - start_num (start_position) הוא המיקום של התווים הראשונים שייחלצו. אתה נכנס 1 בפונקציה הראשונה PSTR, 2 – בפונקציה השנייה PSTR וכו '
- טיעון ראשון - num_chars (מספר_תווים) - מציין את מספר התווים לחלץ מהטקסט. מכיוון שאנחנו צריכים רק תו אחד כל הזמן, בכל הפונקציות אנחנו כותבים 1.
מגבלות: פונקציה VPR אינו הפתרון הטוב ביותר לחיפושים תלויי רישיות באקסל. ראשית, נדרשת הוספת עמודת עזר. שנית, הנוסחה עושה עבודה טובה רק אם הנתונים הומוגניים, או שהמספר המדויק של התווים בערכים שחיפשו ידוע. אם זה לא המקרה שלך, עדיף להשתמש באחד מהפתרונות שאנו מציגים להלן.
פונקציית LOOKUP לחיפוש תלוי רישיות
פונקציה נוף (חיפוש) קשור VPR, אולם התחביר שלו מאפשר חיפושים תלויי רישיות מבלי להוסיף עמודת עזר. כדי לעשות זאת, השתמש נוף בשילוב עם הפונקציה מְדוּיָק (מְדוּיָק).
אם ניקח את הנתונים מהדוגמה הקודמת (ללא עמודת עזר), אז הנוסחה הבאה תתמודד עם המשימה:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
חיפושי נוסחה בטווח A2: A7 התאמה מדויקת לערך התא F2 תלוי רישיות ומחזיר את הערך מעמודה B של אותה שורה.
כמו VPRפונקציה נוף עובד באופן שווה עם טקסט וערכים מספריים, כפי שניתן לראות בצילום המסך למטה:
חשוב! על מנת לתפקד נוף עבד כהלכה, יש למיין את הערכים בעמודת החיפוש בסדר עולה, כלומר מהקטן לגדול ביותר.
הרשו לי להסביר בקצרה כיצד פועלת הפונקציה מְדוּיָק בנוסחה המוצגת לעיל, שכן זו נקודת המפתח.
פונקציה מְדוּיָק משווה את שני ערכי הטקסט בארגומנט הראשון והשני ומחזיר TRUE אם הם זהים לחלוטין, או FALSE אם הם לא. חשוב לנו שהפונקציה מְדוּיָק רגיש לאותיות גדולות.
בואו נראה איך הנוסחה שלנו עובדת צפה+מדויק:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- פונקציה מְדוּיָק משווה את ערך התא F2 עם כל האלמנטים בעמודה A (A2:A7). מחזירה TRUE אם נמצאה התאמה מדויקת, אחרת FALSE.
- מכיוון שאתה נותן את ארגומנט הפונקציה הראשון נוף הערך TRUE, הוא מחלץ את הערך המתאים מהעמודה שצוינה (במקרה שלנו, עמודה B) רק אם נמצאה התאמה מדויקת, תלוית רישיות.
אני מקווה שההסבר הזה היה ברור ועכשיו אתה מבין את הרעיון המרכזי. אם כן, אז לא יהיו לך קשיים עם פונקציות אחרות שננתח עוד יותר, כי. כולם עובדים על אותו עיקרון.
מגבלות: יש למיין את הנתונים בעמודת החיפוש בסדר עולה.
SUMPRODUCT - מוצא ערכי טקסט, תלויי רישיות, אך מחזיר רק מספרים
כפי שכבר הבנתם מהכותרת, SUMPRODUCT (SUMPRODUCT) היא פונקציה נוספת של Excel שתעזור לך לבצע חיפוש תלוי רישיות, אך תחזיר רק ערכים מספריים. אם אפשרות זו אינה מתאימה לך, תוכל להמשיך מיד לצרור INDEX+MATCH, שנותן מענה לכל מקרה ולכל סוג נתונים.
ראשית, הרשו לי להסביר בקצרה את התחביר של פונקציה זו, זה יעזור לכם להבין טוב יותר את הנוסחה הרגישה לאותיות גדולות שלאחר מכן.
פונקציה SUMPRODUCT מכפיל את הרכיבים של המערכים הנתונים ומחזיר את סכום התוצאות. התחביר נראה כך:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
מכיוון שאנו זקוקים לחיפוש תלוי-רישיות, אנו משתמשים בפונקציה מְדוּיָק (מדויק) מהדוגמה הקודמת כאחד מהמכפילים:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
כזכור, מְדוּיָק משווה את ערך התא F2 עם כל האלמנטים בעמודה A. מחזירה TRUE אם נמצאה התאמה מדויקת, אחרת FALSE. בפעולות מתמטיות, Excel תופס TRUE as 1, ו-FALSE עבור 0עוד SUMPRODUCT מכפיל את המספרים הללו ומסכם את התוצאות.
אפסים לא נספרים כי כשמכפילים הם תמיד נותנים 0. בואו נסתכל מקרוב על מה קורה כאשר התאמה מדויקת בעמודה A נמצא והוחזר 1… פונקציה SUMPRODUCT מכפיל את המספר בעמודה B on 1 ומחזיר את התוצאה - בדיוק אותו מספר! הסיבה לכך היא שהתוצאות של המוצרים האחרים הן אפס, והן אינן משפיעות על הסכום המתקבל.
לצערי הפונקציה SUMPRODUCT לא יכול לעבוד עם ערכי טקסט ותאריכים מכיוון שלא ניתן להכפיל אותם. במקרה זה, תקבל הודעת שגיאה #ערך! (#VALUE!) כמו בתא F4 בתמונה למטה:
מגבלות: מחזירה רק ערכים מספריים.
INDEX + MATCH - חיפוש תלוי רישיות עבור כל סוג נתונים
לבסוף, אנו קרובים לנוסחת חיפוש בלתי מוגבלת ותלויה רישיות שעובדת עם כל מערך נתונים.
הדוגמה הזו מגיעה אחרונה, לא בגלל שהטוב ביותר נשאר לקינוח, אלא בגלל שהידע שנצבר מהדוגמאות הקודמות יעזור לכם להבין את הנוסחה הרגישה לאותיות רישיות טוב יותר ומהיר יותר. INDEX+MATCH (INDEX+MATCH).
כפי שבטח ניחשתם, שילוב הפונקציות יותר חשוף и מדד משמש ב-Excel כחלופה גמישה וחזקה יותר עבור VPR. המאמר באמצעות INDEX ו-MATCH במקום VLOOKUP יסביר בצורה מושלמת כיצד הפונקציות הללו פועלות יחד.
אני רק אסכם את נקודות המפתח:
- פונקציה יותר חשוף (MATCH) מחפש ערך בטווח נתון ומחזיר את מיקומו היחסי, כלומר את מספר השורה ו/או העמודה;
- לאחר מכן, הפונקציה מדד (INDEX) מחזירה ערך מעמודה ו/או שורה שצוינו.
לנוסחה INDEX+MATCH יכול לחפש תלוי רישיות, אתה רק צריך להוסיף לו פונקציה אחת. לא קשה לנחש מה זה שוב מְדוּיָק (מְדוּיָק):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
בנוסחה הזו מְדוּיָק פועל באותו אופן כמו בשילוב עם הפונקציה נוף, ונותן את אותה תוצאה:
שימו לב שהנוסחה INDEX+MATCH סגורה בסוגריים מסולסלים היא נוסחת מערך ועליכם להשלים אותה על ידי לחיצה Ctrl + Shift + Enter.
מדוע INDEX+MATCH הוא הפתרון הטוב ביותר לחיפוש תלוי-רישיות?
היתרונות העיקריים של החבילה מדד и יותר חשוף:
- לא מצריך הוספת עמודת עזר, בניגוד VPR.
- לא מחייב את מיון עמודת החיפוש, בניגוד נוף.
- עובד עם כל סוגי הנתונים - מספרים, טקסט ותאריכים.
הנוסחה הזו נראית מושלמת, לא? למעשה, זה לא. וזה למה.
נניח שהתא בעמודת ערך ההחזרה המשויך לערך הבדיקה ריק. איזו תוצאה תחזיר הנוסחה? לא? בוא נראה מה בעצם מחזירה הנוסחה:
אופס, הנוסחה מחזירה אפס! ייתכן שזו לא בעיה גדולה אם אתה עובד עם ערכי טקסט טהורים. עם זאת, אם הטבלה מכילה מספרים, כולל אפסים "אמיתיים", זה הופך לבעיה.
למעשה, כל נוסחאות הבדיקה האחרות (VLOOKUP, LOOKUP ו-SUMPRODUCT) שדנו בהן קודם מתנהגות באותה צורה. אבל אתה רוצה את הנוסחה המושלמת, נכון?
כדי להפוך נוסחה לרגישת רישיות INDEX+MATCH מושלם, שים את זה בפונקציה IF (IF) שיבדוק תא עם ערך החזרה ויחזיר תוצאה ריקה אם הוא ריק:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
בנוסחה זו:
- B הוא עמודה עם ערכי החזרה
- 1+ הוא מספר שהופך את המיקום היחסי של התא המוחזר על ידי הפונקציה יותר חשוף, לכתובת האמיתית של התא. למשל, בפונקציה שלנו יותר חשוף נתון מערך החיפוש A2: A7, כלומר, המיקום היחסי של התא A2 יצטרך 1, כי זה הראשון במערך. אבל המיקום האמיתי של התא A2 בעמודה הוא 2, אז אנחנו מוסיפים 1להשלים את ההבדל ולקבל את הפונקציה עקיף (INDIRECT) שלף את הערך מהתא הרצוי.
התמונות למטה מציגות את הנוסחה המתוקנת תלוית רישיות INDEX+MATCH בִּפְעוּלָה. הוא מחזיר תוצאה ריקה אם התא המוחזר ריק.
כתבתי מחדש את הנוסחה לעמודות ב: דכדי להתאים לשורת הנוסחאות בצילום המסך.
הנוסחה חוזרת 0אם התא המוחזר מכיל אפס.
אם אתה רוצה את הקישור מדד и יותר חשוף הוצגה הודעה כלשהי כאשר ערך ההחזרה ריק, אתה יכול לכתוב אותה במרכאות האחרונות ("") של הנוסחה, למשל, כך:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
