מציאת ערכים חסרים

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

מציאת ערכים חסרים עם MATCH, ISNA ו-IF
מציאת ערכים חסרים עם VLOOKUP, ISNA ו-IF
מציאת ערכים חסרים עם COUNTIF ו-IF

מציאת ערכים חסרים עם MATCH, ISNA ו-IF

כדי לגלות אם כל המוצרים ברשימתך קיימים גם ברשימת הספק שלך כפי שמוצג בסcreenshot למעלה, אתה יכול להשתמש תחילה בפונקציית MATCH כדי לחלץ את המיקום של מוצר מהרשימה שלך (ערך של רשימה A) בתוך רשימת הספק (רשימה B). MATCH יחזיר את השגיאה #N/A כאשר המוצר לא נמצא. לאחר מכן, תוכל להעביר את התוצאה ל-ISNA כדי להמיר את השגיאות #N/A ל-TRUE, מה שאומר שהמוצרים חסרים. הפונקציה IF תחזיר את התוצאה שאתה מצפה לה.

תחביר כללי

=IF(ISNA(MATCH("lookup_value",lookup_range,0)),"חסרים","נמצאו")

√ הערה: באפשרותך לשנות את "חסרים", "נמצאו" לכל ערך אחר לפי הצורך.

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

=IF(ISNA(MATCH(30002,$B$6:$B$10,0)),"חסרים","נמצאו")

או, השתמש בהתייחסות לתא כדי להפוך את הנוסחה לדינמית:

=IF(ISNA(MATCH(G6,$B$6:$B$10,0)),"חסרים","נמצאו")

√ הערה: סימני הדולר ($) למעלה מציינים הפניות מוחלטות, מה שאומר שטווח ה-lookup_range בנוסחה לא ישתנה כאשר תעביר או תעתיק את הנוסחה לתאים אחרים. לעומת זאת, אין להוסיף סימני דולר ל-lookup_value מכיוון שאתה רוצה שהוא יהיה דינמי. לאחר הכנסת הנוסחה, גרור את הידית למלא כלפי מטה כדי להחיל את הנוסחה על התאים מטה.

הסבר על הנוסחה

כאן אנו משתמשים בנוסחה הבאה כדוגמה:

=IF(ISNA(MATCH(G8,$B$6:$B$10,0)),"חסרים","נמצאו")

• MATCH(G8,$B$6:$B$10,0): סוג ההתאמה 0 מכריח את פונקציית MATCH להחזיר ערך מספרי שמעיד על מיקום ההתאמה הראשונה של 3004, הערך בתא G8, במערך $B$6:$B$10. עם זאת, במקרה זה, MATCH לא הצליח למצוא את הערך במערך החיפוש, ולכן יחזיר את השגיאה #N/A.
• ISNA(MATCH(G8,$B$6:$B$10,0)) = ISNA(#N/A): ISNA עובד כדי לגלות האם הערך הוא שגיאת “#N/A” או לא. אם כן, הפונקציה תחזיר TRUE; אם הערך הוא משהו אחר מלבד שגיאת “#N/A”, היא תחזיר FALSE. אז, נוסחת ISNA זו תחזיר TRUE.
• IF(ISNA(MATCH(G8,$B$6:$B$10,0)),"חסרים","נמצאו") = IF(TRUE,"חסרים","נמצאו"): הפונקציה IF תחזיר חסרים אם ההשוואה שבוצעה על ידי ISNA ו-MATCH היא TRUE, אחרת תחזיר נמצאו. אז, הנוסחה תחזיר חסרים.

מציאת ערכים חסרים עם VLOOKUP, ISNA ו-IF

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

תחביר כללי

=IF(ISNA(VLOOKUP("lookup_value",lookup_range,1,FALSE)),"חסרים","נמצאו")

√ הערה: באפשרותך לשנות את "חסרים", "נמצאו" לכל ערך אחר לפי הצורך.

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

=IF(ISNA(VLOOKUP(30002,$B$6:$B$10,1,FALSE)),"חסרים","נמצאו")

או, השתמש בהתייחסות לתא כדי להפוך את הנוסחה לדינמית:

=IF(ISNA(VLOOKUP(G6,$B$6:$B$10,1,FALSE)),"חסרים","נמצאו")

√ הערה: סימני הדולר ($) למעלה מציינים הפניות מוחלטות, מה שאומר שטווח ה-lookup_range בנוסחה לא ישתנה כאשר תעביר או תעתיק את הנוסחה לתאים אחרים. לעומת זאת, אין להוסיף סימני דולר ל-lookup_value מכיוון שאתה רוצה שהוא יהיה דינמי. לאחר הכנסת הנוסחה, גרור את הידית למלא כלפי מטה כדי להחיל את הנוסחה על התאים מטה.

הסבר על הנוסחה

כאן אנו משתמשים בנוסחה הבאה כדוגמה:

=IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"חסרים","נמצאו")

• VLOOKUP(G8,$B$6:$B$10,1,FALSE): הפרמטר range_lookup FALSE מכריח את פונקציית VLOOKUP לחפש ולהחזיר את הערך התואם בדיוק ל-3004, הערך בתא G8. אם הערך 3004 קיים בעמודה הראשונה של המערך $B$6:$B$10, VLOOKUP יחזיר את אותו ערך; אחרת, הוא יחזיר את ערך השגיאה #N/A. כאן, 3004 אינו קיים במערך, אז התוצאה תהיה #N/A.
• ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)) = ISNA(#N/A): ISNA עובד כדי לגלות האם הערך הוא שגיאת “#N/A” או לא. אם כן, הפונקציה תחזיר TRUE; אם הערך הוא משהו אחר מלבד שגיאת “#N/A”, היא תחזיר FALSE. אז, נוסחת ISNA זו תחזיר TRUE.
• IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"חסרים","נמצאו") = IF(TRUE,"חסרים","נמצאו"): הפונקציה IF תחזיר חסרים אם ההשוואה שבוצעה על ידי ISNA ו-VLOOKUP היא TRUE, אחרת תחזיר נמצאו. אז, הנוסחה תחזיר חסרים.

מציאת ערכים חסרים עם COUNTIF ו-IF

כדי לגלות אם כל המוצרים ברשימתך קיימים גם ברשימת הספק שלך, אתה יכול להשתמש בנוסחה פשוטה יותר עם הפונקציות COUNTIF ו-IF. הנוסחה מנצלת את העובדה ש-Excel יעריך כל מספר שאינו אפס (0) כ-TRUE. אז אם ערך קיים ברשימה אחרת, הפונקציה COUNTIF תחזיר את מספר המופעים שלו ברשימה, ואז IF יקבל את המספר כ-TRUE; אם הערך אינו קיים ברשימה, הפונקציה COUNTIF תחזיר 0, ו-IF יקבל זאת כ-FALSE.

תחביר כללי

=IF(COUNTIF("lookup_range",lookup_value),"נמצאו","חסרים")

√ הערה: באפשרותך לשנות את "נמצאו", "חסרים" לכל ערך אחר לפי הצורך.

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

=IF(COUNTIF($B$6:$B$10,30002),"נמצאו","חסרים")

או, השתמש בהתייחסות לתא כדי להפוך את הנוסחה לדינמית:

=IF(COUNTIF($B$6:$B$10,G6),"נמצאו","חסרים")

√ הערה: סימני הדולר ($) למעלה מציינים הפניות מוחלטות, מה שאומר שטווח ה-lookup_range בנוסחה לא ישתנה כאשר תעביר או תעתיק את הנוסחה לתאים אחרים. לעומת זאת, אין להוסיף סימני דולר ל-lookup_value מכיוון שאתה רוצה שהוא יהיה דינמי. לאחר הכנסת הנוסחה, גרור את הידית למלא כלפי מטה כדי להחיל את הנוסחה על התאים מטה.

הסבר על הנוסחה

כאן אנו משתמשים בנוסחה הבאה כדוגמה:

=IF(COUNTIF($B$6:$B$10,G8),"נמצאו","חסרים")

• COUNTIF($B$6:$B$10,G8): הפונקציה COUNTIF סופרת כמה פעמים מופיע 3004, הערך בתא G8, במערך $B$6:$B$10. בבירור, 3004 אינו קיים במערך, אז התוצאה תהיה 0.
• IF(COUNTIF($B$6:$B$10,G8),"נמצאו","חסרים") = IF(0,"נמצאו","חסרים"): הפונקציה IF תעריך את 0 כ-FALSE. אז, הנוסחה תחזיר חסרים, הערך שצריך להחזיר כאשר הארגומנט הראשון מוערך כ-FALSE.

פונקציות קשורות

נוסחאות קשורות