שימוש משולב בסומיף (vlookup)
Sumif עם VLOOKUP הוא שילוב של שתי פונקציות מותנות שונות, SUMIF משמש לסיכום התאים על סמך תנאי כלשהו שלוקח טיעונים של טווח שיש להם את הנתונים ואז הקריטריונים או התנאי והתאים לסיכום, במקום הקריטריונים בהם אנו משתמשים VLOOKUP כקריטריונים כשיש כמות גדולה של נתונים זמינה במספר עמודות.
SUMIF הוא פונקציה המוצגת ב- Excel מגרסת 2007 לסיכום הערכים השונים המתאימים לקריטריונים. VLOOKUP היא אחת הנוסחאות הטובות ביותר לאיסוף נתונים מטבלאות אחרות. כאשר קיימים מספר תנאים ועמודות, sumif (vlookup) משמש לביצוע החישובים המרובים בגליון ה- Excel. החיסרון של פונקציית SUMIF המחזירה את המספר היחיד מתגבר על ידי שימוש ב- VLOOKUP. VLOOKUP עוזר להחזיר כל סוג של נתונים מטבלה בהתבסס על הקריטריונים המותאמים.
הֶסבֵּר
פונקציית SUMIF: פונקציית טריגונומטריה ומתמטיקה לסכם את הערכים כאשר התנאי שנקבע נכון. ערך הסכום מתקבל על סמך קריטריון אחד בלבד.
כאשר אנו מתמודדים עם פונקציית SUMIF ב- Excel, נעשה שימוש בנוסחה הבאה
- טווח: זהו טווח התאים המשמש להערכת הקריטריונים שנקבעו
- קריטריונים: זה התנאי לסכם את הערכים. זה יכול להיות הפניה לתא, מספר ופונקציה מצוינת אחרת. כאשר אנו רוצים לשלב SUMIF ו- VLOOKUP, פונקציית vlookup תוזן במקום הקריטריונים
- טווח סכומים : זהו טווח התאים שצוין לסיכום הערכים המספריים.
כעת, הנוסחה שונה ל
נוסחה = SUMIF (טווח, Vlookup (ערך בדיקה, מערך טבלה, מספר אינדקס העמודות, [טווח בדיקה]), [טווח סכומים])
- ערך בדיקה: הוא מציין את הערך שיש לחפש בטבלה. זה יכול להיות התייחסות או ערך.
- מערך טבלה: זהו טווח הטבלה המכיל שניים או יותר משתי עמודות.
- מספר אינדקס עמודות: זהו האינדקס היחסי של עמודה שצוינת להחזרת נתונים נדרשים מעמודה ספציפית.
- [בדיקת טווח]: זה 0 או 1 כדי לציין אם יש להחזיר את הערך המדויק או להחזיר את הערך המשוער. אבל, זה אופציונלי למשתמש. 0 מציין התאמה מדויקת ו -1 מציין התאמה משוערת.
כיצד להשתמש ב- SUMIF עם פונקציית VLOOKUP?
השימוש המשולב ב- sumif (vlookup) מועיל בחיפוש נתונים על פי הקריטריונים היחידים. אלה משמשים בדרכים רבות ב- Excel לביצוע החישובים על ידי חיפוש נתונים. אלה משמשים באופן קולקטיבי בסביבה העסקית לביצוע משימות שונות לקבלת החלטות טובות. כדי להשתמש ביעילות בפונקציות אלה יחד,
ראשית, יש להזין את הפונקציה SUMIF תוך שימוש בשתי השיטות.
שיטה ראשונה: הנוסחה צריכה להקליד מהמקלדת כפי שמוצג בתמונה למטה.
שיטה שנייה: ניתן להכניס את פונקציית SUMIF מהכרטיסייה 'פורמולה' כפי שמוצג בתמונה.
לאחר הכניסה לפונקציה SUMIF, הנוסחה של VLOOKUP מוזנת בתוך פונקציית SUMIF על ידי החלפת האלמנט 'קריטריונים'. כל הפרמטרים של ה- VLOOKUP כולל ערך החיפוש, מערך הטבלה, מספר האינדקס של העמודה וחיפוש הטווח. אלה צריכים להיות כלואים בסוגריים כדי למנוע שגיאות בנוסחה. טווח הערכים שיש לסכם כלול באלמנטים של טווח הסכומים בפונקציה SUMIF. לבסוף, מקשי CTRL, SHIFT ו- ENTER נלחצו יחד כדי להקל על הערכים כמערך.
דוגמאות
אתה יכול להוריד את SUMIF With VLOOKUP תבנית Excel כאן - SUMIF With VLOOKUP Excel Templateדוגמה מס '1 - שימוש ב- sumif (vlookup) יחד כדי לקבוע ערך כלשהו
דוגמה זו מראה כיצד להשתמש ב- sumif (vlookup) יחד כדי למצוא את סכום המכירות באותו חודש בשנים שונות. הנתונים הבאים נחשבים לדוגמא זו כפי שמוצג בתמונת המסך.
טבלת הבדיקה הנחשבת מוצגת כאמור להלן. הוא כלל ערכי התייחסות לחודשים ינואר עד דצמבר.
לאחר הזנת נתונים לטבלה הראשית ולטבלת החיפוש, פונקציית SUMIF משמשת לקביעת סך המכירות שנוצרו בחודשים שונים בשנה. כאן, ערך ה- Lookup הנחשב הוא החודש. הנוסחה המשלבת את sumif (vlookup) מוצגת כ,
סך המכירות בחודש ינואר נקבע כ- 17263.3 . כאשר שינינו את ערך המראה לחודש נוסף, סך המכירות המתאימות נוצרות.
דוגמה מס '2 - קביעת סכום על סמך קריטריונים תואמים בגליונות עבודה שונים
בדוגמה זו, טבלת החיפוש והשולחן הראשי נלקחים בגיליונות שונים ולא בגיליון יחיד. נתוני טבלת החיפוש מוצגים בצילום המסך שלהלן.
נתוני הטבלה הראשיים מוצגים בצילום המסך שלהלן.
כדי לקבוע את סך המכירות, שם איש המכירות נלקח כערך בדיקה ותעודות עובד משמשות למטרות הפניה. הנוסחה מוזנת כפי שמוצג להלן ומעט שינויים נצפו בנוסחה בהשוואה לדוגמא הראשונה.
בדוגמה זו, במקום לבחור מערך חיפושים מוזכר רק Lookup_table. על ידי לחיצה על שלושת המקשים כולל CTRL, SHIFT ו- ENTER מיוצרים תוצאות מדויקות.
כאשר שמו של איש המכירות שונה לשמות המוזכרים בחיפוש, סכום המכירות מגוון ומייצר תוצאה חדשה.
יתרונות
להלן היתרונות של שימוש בפונקציות אלה.
- אנו יכולים לחלץ את הערכים בקלות מטבלה אחרת כדי לבצע חישובים.
- קביעת סכום הערכים המוצגים בטווח העומד בקריטריונים המוזכרים בהיבטים שונים של עסק.
דברים שיש לזכור
- בעת שימוש בפונקציה VLOOKUP, מספר אינדקס העמודות לא צריך להיות נמוך מ -1 כדי למנוע שגיאות.
- יש לתת אינדקסים לעמודות טבלת החיפוש על ידי ציון עם המספרים 1, 2, 3 וכן הלאה.
- יש להשתמש ב- CTRL + SHIFT + ENTER ב- excel במקום להזין את מפתח שכן VLOOKUP מוזן כנוסחת מערך.
- יש צורך בהגדרת שתי טבלאות כולל Main ו- Lookup כדי לחלץ ערכים ולקבוע את סכום ערכי המערך.
- SUMIF מספק תוצאות מדויקות עבור נתונים מספריים בלבד, הוא לא יעבוד עבור סוג אחר של נתונים.