יש לך את האפשרויות האלה
הדרך המהירה והטובה ביותר לחפש קריטריונים ספציפיים היא להשתמש בפונקציית VLOOKUP. בעזרתו אפשר למצוא ערך ב- Microsoft Excel באלפיות השנייה.
ה- VLOOKUP - כך זה עובד עם קריטריון חיפוש
כדי להבין כיצד VLOOKUP פועל עם קריטריוני חיפוש מרובים, משתמשי Excel צריכים קודם כל להבין כיצד הפונקציה VLOOKUP בנויה בעצם. הדוגמה הבאה מציגה את מספר העובדים הזמניים המועסקים בחברה בחודשים השונים של שנה קלנדרית.
יש להשתמש ב- VLOOKUP כדי להראות כמה עובדים זמניים הועסקו בחודשים מרץ ודצמבר.
נוסחת VLOOKUP בדוגמה לדצמבר היא:
חיפוש (D8, A: B, 2, 0)
הנוסחה מחפשת את המספרים המתאימים לעובדים זמניים כדלקמן:
- מצא את הערך של D8 (חודש דצמבר) בעמודות A ו- B.
- מצא את הערך של עמודה B. זה מיוצג על ידי 2 בנוסחה.
- ה 0 בנוסחה מציין שיש לחפש ערך מדויק.
הפונקציה VLOOKUP מחפשת קריטריון חיפוש יחיד. המטריצה של הנוסחה היא האזור בו נמצאים הערכים הרלוונטיים. בנוסף, העמודה VLOOKUP חייבת להכיל את הערך שאתה מחפש.
חָשׁוּב:
עם הפונקציה VLOOKUP, קריטריון החיפוש חייב תמיד להיות בעמודה 1. הערכים המשויכים לחיפוש חייבים להיות מוצגים גם מימין לקריטריון החיפוש. אם הגיליון האלקטרוני בנוי בצורה שונה, VLOOKUP לא יפעל. במקרה זה, שילוב של הפונקציות INDEX ו- COMPARE יכול להיות שימושי ביצירת השאילתה הרצויה.
VLOOKUP עם קריטריוני חיפוש מרובים - כיצד לשלב את הפונקציה IF
בדוגמה המוצגת למעלה נשאל כמה עובדים זמניים עובדים בחברה בחודש מסוים. בעזרת פונקציית VLOOKUP ניתן בקלות להפיק את הערכים הספציפיים. בדוגמה הבאה אתה יכול לראות רשימת מאמרים נרחבת עם 36 ערכים [1], שממנו אתה רוצה לסנן במהירות ובקלות מספרי מאמרים מתאימים המבוססים על מספר קריטריונים.
קריטריוני החיפוש מופיעים בגיליון האלקטרוני מימין. מספר המאמר שנמצא אמור להופיע בתא H8.
השלב הראשון הוא לכלול נוסחה עם VLOOKUP שתוכל להשתמש בה לאיתור מספר פריט המבוסס על קריטריון חיפוש. לאחר מכן אתה מרחיב נוסחה זו צעד אחר צעד עבור קריטריוני החיפוש האחרים.
השתמש בנוסחה הבאה בתא H8 כדי למצוא את מספר הפריט לגודל מתא H3: [2]
= VLOOKUP (H3, A3: E40, 5
הנוסחה מחפשת את מספר המאמר המתאים בצורה הבאה:
- מצא את הערך של H3 (גודל 142) בעמודות A עד E.
- מצא את הערך בעמודה E (מספר המאמר). זה מיוצג על ידי ה -5 בנוסחה.
לסיכום, הפונקציה VLOOKUP מעבירה את הרשימה המלאה שבה אתה מחפש תוכן כארגומנט הראשון. קריטריון החיפוש אותו יש לחפש בעמודה הראשונה של האזור המועבר מתמקד כטיעון השני. הארגומנט השלישי מגדיר את העמודה שממנה יש להחזיר את התוצאה.
שלח את הרשימה ממוינת בסדר עולה בהתאם לעמודה הראשונה, כך שאקסל תוכל למצוא את הערך הנמוך הבא. הנוסחה מוצאת את מספר המאמר 2.253 מתא E16. מכיוון שהגודל 142 מתא H3 אינו מופיע ברשימה, הערך התחתון הבא 139 נמצא.
כלול קריטריון חיפוש שני
בשלב הבא אתה גם רוצה לחפש את הקבוצה בתא H4 כקריטריון שני. יש למצוא רק מספר מאמר בו מתרחשת הקבוצה שאתה מחפש.
לשם כך, הוסף פונקציית IF לנוסחה בתא H8 והכנס את הנוסחה כנוסחת מערך:
= VLOOKUP (H3; IF (B3: B40 = H4; A3: E40; ""); 5)
מידע:
ב- Microsoft Excel מבדילים בין נוסחאות קונבנציונאליות לנוסחאות המטריצה החזקות יותר. נוסחאות מטריצה מאפשרות למשתמשי Excel לבצע חישובים מורכבים. לא ניתן לבצע אותן בעזרת הנוסחאות הסטנדרטיות. מכיוון שיש ללחוץ על צירוף המקשים "CTRL + SHIFT + ENTER" לאחר הזנת הנוסחה, נוסחאות מטריצה ידועות בינלאומיות או נוסחאות CSE (CTRL + SHIFT + ENTER).
אשר את הנוסחה לאחר הזנתה באמצעות שילוב המקשים Ctrl + Shift + Enter כנוסחת מטריצה. [3]
מספר הפריט 1.188 נמסר כתוצאה מכך. הנוסחה מוצאת את הערך 126 בעמודה "גודל". זהו הערך הגדול ביותר בגיליון האלקטרוני. הוא פחות או שווה לערך החיפוש 142 ויש לו את התוכן C2 ביחס לקבוצה.
הפונקציה IF בנוסחה בודקת כל תא בטווח B3: B40 להתאמה לתוכן תא H4. ברגע שיש התאמה, השורה המתאימה באזור B3: E40 מועברת לפונקציית VLOOKUP.
הוסף בקלות קריטריוני חיפוש נוספים בכל עת
תוכל להוסיף קריטריוני חיפוש נוספים באותו אופן. אתה עושה זאת על ידי קינון פונקציות IF נוספות בנוסחה. כדי להתאים את כל ארבעת קריטריוני החיפוש, כלול עוד שתי פונקציות IF מקוננות בנוסחה בתא H8:
= VLOOKUP (H3; IF (B3: B40 = H4; IF (C3: C40 = H5; IF (D3: D40 = H6; A3: E40; ""); ""); ""); 5)
לאחר אישור נוסחת המטריצה באמצעות צירוף המקשים Ctrl + Shift + Enter, ה- VLOOKUP מחזיר את מספר המאמר 1.748. זהו המספר משורה 14 לגודל 125. זו השורה בעלת הערך הגבוה ביותר בעמודה "גודל", בה מתקיימים כל שלושת הקריטריונים הנוספים. [4]
הפונקציה IF הראשונה לא מעבירה מיד את השורה המתאימה לפונקציית VLOOKUP אם הבדיקה חיובית. במקום זאת, פונקציה נוספת של IF בודקת אם יש התאמה עם הקריטריון הנוסף השני. אם גם בדיקה זו חיובית, נעשה שימוש בפונקציית ה- IF השלישית. רק כאשר כל שלושת הפונקציות IF מחזירות בדיקה חיובית, פונקציית ה- IF השלישית מעבירה את השורה המתאימה לפונקציית VLOOKUP. בדרך זו תוכלו לקנן עד שבע פונקציות IF וכך לבדוק עד שמונה קריטריונים.
סיכום: להלן דרך יעילה לחפש מספר קריטריונים ב- Excel
משתמשי Microsoft Excel מתמודדים ללא הרף עם השאלה כיצד לקרוא נתונים ומידע מגיליונות אלקטרוניים נרחבים או מקוננים בעבודתם היומיומית. משתמשי Excel ימצאו את פונקציית ההתייחסות היעילה ביותר בפונקציית VLOOKUP. יתרון עיקרי של פונקציית VLOOKUP ופונקציות הפניה אחרות הוא שבברירת מחדל הם מחפשים את הערך הנמוך הבא ברגע שהערך שחיפשת לא נמצא.
מאחר שפונקציית VLOOKUP מוגבלת למונח חיפוש אחד כברירת מחדל, יש להשתמש בנוסחה מורחבת לקריטריוני חיפוש מרובים. כדי למצוא מספר מונחי חיפוש באמצעות הפונקציה VLOOKUP, נוסחת מערך עם פונקציות IF מתאימה. הוא בודק את פונקציות ה- IF הבודדות אחת אחרי השנייה, ואם התוצאה חיובית, היא מתייחסת ל- VLOOKUP. זה מבטיח שהתוצאה הנכונה תיפלט בגיליונות אלקטרוניים נרחבים עם מונחי חיפוש שונים.
שאלות נפוצות
מה יכולה הפונקציה VLOOKUP לעשות?
הפונקציה VLOOKUP ב- Microsoft Excel היא אחת הפונקציות החשובות והיעילות ביותר ב- Excel. באמצעות VLOOKUP תוכל לחפש אוטומטית בגיליון אלקטרוני נרחב אחר ערך ספציפי. התוצאה ערוכה ומוציאה בתא אחר.
מתי אתה משתמש בפונקציה IF ב- EXCEL?
הפונקציה IF היא אחת הפונקציות הנפוצות ביותר ב- Microsoft Excel. בשלב הראשון, הפונקציה IF משמשת להגדרת מצב. בשלב השני, אתה מקשר בין IF לבין הפונקציה THEN, למשל, ומציין איזה מידע יש להוציא. ניתן להשתמש בפונקציית ELSE גם לקביעת התוצאה החלופית. דוגמה מעשית: אם עובד א 'עבד יותר מ -240 ימים בשנה ואז בונוס בונוס אחר.
מה ההבדל בין נוסחאות מערך לנוסחאות סטנדרטיות ב- Excel?
הידועות ביותר ב- Excel הן הנוסחאות הסטנדרטיות המקובלות. ב- Excel, נוסחאות מטריצה הן נוסחאות עוצמתיות שאיתן ניתן לבצע חישובים מורכבים. על מנת לבצע נוסחת מערך, יש לאשר את נוסחת המערך לאחר הזנתה בעזרת צירוף המקשים "CTRL + SHIFT + ENTER". קיצור המקשים מראה ל- Excel שמדובר בנוסחת מערך.
