לְהִצטַיֵן

מצא ואחזר ערכים חסרים

Find Retrieve Missing Values

נוסחת Excel: מצא ואחזר ערכים חסריםנוסחה גנרית
= INDEX (complete, MATCH (TRUE, ISNA ( MATCH (complete, partial_expanding,0)),0))
סיכום

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

 
= INDEX (complete, MATCH (TRUE, ISNA ( MATCH (complete,$D:D11,0)),0))

איפה 'שלם' הוא טווח בשם B5: B15.



הערה: זהו נוסחת מערך ויש להזין אותו עם שליטה + shift + enter.



כיצד להיכנס לחלל באקסל
הֶסבֵּר

עובד מבפנים כלפי חוץ, הליבה של הנוסחה הזו היא ה- פְּנִימִי ביטוי התאמה:

מהו שולחן ציר?
 
 ISNA ( MATCH (complete,$D:D11,0)

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



התוצאה של MATCH היא מערך מספרים ושגיאות # N / A, כאשר המספרים מייצגים ערכים ברשימה המלאה הקיימים ברשימה החלקית ושגיאות מייצגות ערכים חסרים:

 
{1#N/A23#N/A456#N/A7#N/A}

ה פונקציית ISNA משמש להמרת תוצאות אלו למערך של ערכי TRUE ו- FALSE. במערך זה, TRUE תואם לערכים חסרים ו- FALSE תואם לערכים קיימים:

 
{FALSETRUEFALSEFALSETRUEFALSEFALSEFALSETRUEFALSETRUE}

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



בתא D12, הערך החסר הראשון שנמצא הוא 'קיווי' בשורה 2, כך שיש לנו:

איך יוצרים תרשים עמודות ב- Excel
 
= INDEX (complete,2) // returns 'kiwi'

ב- D13, 'קיווי' כלול כעת בהתייחסות המתרחבת, ולכן הערך החסר הראשון הוא 'אגס':

 
= INDEX (complete,5) // returns 'pear'

וכן הלאה. לאחר הוספת כל הערכים החסרים, הנוסחה תחזיר את השגיאה # N / A.

המחבר דייב ברונס


^