4 פונקציות חיפוש של Excel לחיפוש יעיל של גיליונות אלקטרוניים

4 פונקציות חיפוש של Excel לחיפוש יעיל של גיליונות אלקטרוניים

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





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



1. הפונקציה VLOOKUP

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





להלן תחביר הפונקציה:

=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])
  • [חיפוש_ערך] הוא פיסת המידע שכבר יש לך. לדוגמה, אם אתה צריך לדעת באיזה מדינה נמצאת עיר, זה יהיה שם העיר.
  • [table_array] מאפשר לך לציין את התאים שבהם הפונקציה תחפש את ערכי החיפוש והחזרה. בעת בחירת הטווח שלך, ודא שהעמודה הראשונה הכלולה במערך שלך היא זו שתכלול את ערך החיפוש שלך!
  • [col_index_num] הוא מספר העמודה המכילה את ערך ההחזרה.
  • [טווח_מבט] הוא ארגומנט אופציונלי, ולוקח 1 או 0. אם אתה מזין 1 או משמיט ארגומנט זה, הפונקציה מחפשת את הערך שהזנת או את המספר הנמוך ביותר הבא. אז בתמונה למטה, VLOOKUP שמחפש ציון SAT של 652 יחזיר 646, מכיוון שזה המספר הקרוב ביותר ברשימה שהוא פחות מ- 652, וברירת מחדל של [range_lookup] הוא 1.

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



=VLOOKUP('Winters', C2:F101, 4, 0)

מכיוון שציוני ה- SAT הם העמודה הרביעית מעבר לעמודה בשם המשפחה, 4 היא הארגומנט של אינדקס העמודות. שים לב שכאשר אתה מחפש טקסט, הגדרת [range_lookup] ל- 0 היא רעיון טוב. בלי זה אתה יכול להשיג תוצאות גרועות.

להלן התוצאה:



הוא החזיר 651, ציון SAT השייך לתלמיד בשם קנדי ​​ווינטרס, הנמצא בשורה 92 (מוצג בתוספת למעלה). היה לוקח הרבה יותר זמן לגלול בחיפוש אחר השם מאשר כדי להקליד במהירות את התחביר!

הערות על VLOOKUP

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



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

2. הפונקציה HLOOKUP

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

להלן תחביר הפונקציה:

=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])
  • [חיפוש_ערך] הוא הערך שאתה מכיר ורוצה למצוא לו ערך מתאים.
  • [table_array] הוא התאים שבהם ברצונך לחפש.
  • [row_index_num] מציין את השורה שממנה יגיע ערך ההחזרה.
  • [טווח_מבט] זהה לזה של VLOOKUP, השאר אותו ריק כדי לקבל את הערך הקרוב ביותר במידת האפשר, או הזן 0 כדי לחפש התאמות מדויקות בלבד.

גיליון אלקטרוני זה מכיל שורה לכל מדינה, יחד עם ציון SAT בשנים 2000–2014. תוכל להשתמש ב- HLOOKUP כדי למצוא את הציון הממוצע במינסוטה בשנת 2013. כך נעשה זאת:

=HLOOKUP(2013, A1:P51, 24)

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

Minnesotans עמד בממוצע על ציון 1014 בשנת 2013. שים לב כי 2013 אינו במרכאות מכיוון שהוא מספר, ולא מחרוזת. כמו כן, ה -24 מגיע ממינסוטה בשורה ה -24.

הנה כיצד לחשב את הממוצע המשוקלל ב- Excel .

הערות על HLOOKUP

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

3-4. פונקציות INDEX ו- MATCH

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

אך ראשית, התחביר של שתי הפונקציות:

=INDEX([array], [row_number], [column_number])
  • [מַעֲרָך] הוא המערך בו תחפשו.
  • [שורה_מספר] ו [טור_מספר] יכול לשמש לצמצום החיפוש שלך (נסתכל על זה עוד רגע.)
=MATCH([lookup_value], [lookup_array], [match_type])
  • [חיפוש_ערך] הוא מונח חיפוש שיכול להיות מחרוזת או מספר.
  • [lookup_array] הוא המערך שבו Microsoft Excel יחפש את מונח החיפוש.
  • [התאמה_סוג] הוא ארגומנט אופציונלי שיכול להיות 1, 0 או -1. 1 יחזיר את הערך הגדול ביותר שהוא קטן או שווה למונח החיפוש שלך. 0 יחזיר רק את המונח המדויק שלך, ו- -1 יחזיר את הערך הקטן ביותר שהוא גדול או שווה למונח החיפוש שלך.

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

INDEX, לעומת זאת, עושה את ההפך: הוא לוקח הפניה לתא ומחזיר את הערך שבו. אתה יכול לראות כאן שכאשר נאמר לך להחזיר את התא השישי בעמודה עיר, INDEX מחזיר 'עוגן', הערך משורה 6.

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

כיצד להכריח לסיים תהליך
=INDEX(F:F, MATCH('Waters', C:C, 0))

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

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

תן לחיפושים ב- Excel להתחיל

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

אם אתה באמת רוצה לשלוט ב- Microsoft Excel, אתה באמת יכול להפיק תועלת מהשמירה על גיליון הרמאים של Essential Excel בהישג יד!

קרדיט תמונה: Cico/ שוטרסטוק

לַחֲלוֹק לַחֲלוֹק צִיוּץ אימייל Canon לעומת ניקון: איזה מותג מצלמות עדיף?

קנון וניקון הם שני השמות הגדולים ביותר בתעשיית המצלמות. אבל איזה מותג מציע את המערך הטוב ביותר של מצלמות ועדשות?

קרא הבא נושאים קשורים
  • פִּריוֹן
  • גיליון אלקטרוני
  • Microsoft Excel
  • טריקים לחיפוש
על הסופר איאן באקלי(פורסמו 216 מאמרים)

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

עוד מאת איאן באקלי

הירשם לניוזלטר שלנו

הצטרף לניוזלטר שלנו לקבלת טיפים, סקירות, ספרים אלקטרוניים בחינם ומבצעים בלעדיים!

לחצו כאן להרשמה