VLOOKUP. באמצעות VLOOKUP. Excel - CDF

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

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

איך Excel VLOOKUP

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

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

הצורך להשתמש

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

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

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

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

נוסחא אלגוריתם מילוי

CDF הנוסחה ממוקם "אשף הפונקציות" ו "הפניות מערכים." אפשרות Dialog היא כדלקמן:

בנוסחה של טיעונים מוזנים תור:

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

דוגמה של פונקציה

VLOOKUP שימוש למשל עשוי להיות כדלקמן: כאשר עוסק עסק סחר האלקטרוני של Excel בטור כתוב שם של המוצר, וגם בעמודה B - המחיר המקביל. עבור לגבש ולהציע הצעות ב- C בעמודה אתה צריך למצוא את העלות של מוצר מסוים שאתה רוצה להציג בטור ד

הנוסחא, כתוב D, תיראה כך: = VLOOKUP (C1, A1: B5, 2, 0), כלומר, = VLOOKUP (ערך בדיקה, טווח נתונים בטבלה, את המספר הסידורי של העמודה; 0). כתוצאת טיעון רביעי עשוי לשמש במקום 0 הוא FALSE.

כדי למלא נוסחה זו מציעה שולחן, עליך להעתיק את הטור כולו ד

אזור נתונים המאובטח של טווח העבודה אפשרי באמצעות ההפניות המוחלטות. כדי לעשות זאת באופן ידני נספר סימני $ מול ערכים אלפביתית מספריים ידי תאי שמאל וימין הקיצוניים של השולחן. במקרה זה את הנוסחה הופכת להיות: = CDF (C1; $ A $ 1: $ B $ 5, 2, 0).

באמצעות שגיאה

פונקצית CDF לא עובדת, ולאחר מכן תופיע הודעה בתוצאת פלט טור של שגיאה (# N / A או # N / A). זה קורה במקרים כאלה:

1. הנוסחא הציגה, וטור הקריטריונים הרצוי ריק (במקרה זה, בעמודת C).
2. בעמודה C עשה ערך כי הוא נעדר טור (נתוני טווח חיפוש). כדי לבדוק את הערך הנדרש צריך להיות מוקצה ואת קריטריוני עמודות בכרטיסיית התפריט "העריכה" - "מצא" להכניס התקליט הזה, להתחיל את החיפוש. אם התוכנית לא מוצא את זה, אז זה לא קיים.
3. פורמטי טור של התאים A ו- C (הקריטריונים הנדרשים) הם שונים, למשל, אחד - את הטקסט, והשני - המספר. לשנות את הפורמט של התא יכול להיות, אם אנחנו נכנסים עריכת התא (F2). בעיות מסוג זה מתרחשים בדרך כלל כאשר אתה מייבא נתונים מיישומים אחרים. כדי להימנע מטעויות כאלה בנוסחה CDF ניתן לשלב את התכונות הבאות: VALUE או טקסט. יישום אלגוריתמים אלה באופן אוטומטי להמיר את הפורמט של תאים.
4. קוד הפונקציה מכילה שאינם ניתנים להדפסה תווים או רווחים. ואז אתה צריך לבדוק את הנוסחה בקפידה בנוכחות שגיאות קלט.
5. הגדרת חיפוש בקירוב, כלומר הטיעון הרביעי VLOOKUP הוא 1 או נכון, אבל הטבלה לא ממוינות לפי שווי עולה. במקרה זה, על הקריטריונים הנדרשים העמודה שברצונך למיין בסדר עולה.

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

מאפיינים של שימוש כמו מרווח הצפייה 1 או כ- TRUE

שגיאה תחת №5 די נפוץ לראות בבירור באיור להלן.

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

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

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

ניואנסים אחרים של עבודה עם VLOOKUP

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

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

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

על מנת להשתמש בנתונים מושמים על גיליון נוסף של חוברת העבודה, באמצעות פונקצית PPS, יש צורך ב הטיעון השני של טווח נתוני מיקום קופת נוסחא. לדוגמה, CDF = (A1; גיליון 2 $ A $ 1: $ B $ 5; 2; 0), שבו גיליון 2! - פנייה אל רשימת הספרים המבוקשים, ו $ A $ 1: B $ 5 $ - כתובת של מרחב חיפוש נתונים.

דוגמא לתהליך החינוכי עם CDF

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

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

VLOOKUP מתמודד עם משימה זו. ב "סימני" בעמודה G שכותרתו הנוסחה המתאימה רשמה: = CDF (E4, B3: C13, 2, 0). שלה צריך להעתיק את כל העמודה של השולחן.

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

דוגמה של מנוע חיפוש עם CDF

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

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