אחת היכולות הרבות של Microsoft Excel היא היכולת להשוות בין שתי רשימות נתונים, לזהות התאמות בין הרשימות ולזהות אילו פריטים נמצאים ברשימה אחת בלבד. זה שימושי כאשר משווים רשומות פיננסיות או בודקים אם שם מסוים נמצא במסד נתונים. אתה יכול להשתמש בפונקציית MATCH לזיהוי וסימון רשומות תואמות או שאינן תואמות, או שאתה יכול להשתמש בעיצוב התניה עם הפונקציה COUNTIF. השלבים הבאים מראים לך כיצד להשתמש בכל אחד מהם כדי להתאים את הנתונים שלך.
צעדים
שיטה 1 מתוך 2: זיהוי רשומות עם פונקציית MATCH
שלב 1. העתק את רשימות הנתונים לגליון עבודה יחיד
Excel יכול לעבוד עם גליונות עבודה מרובים בתוך חוברת עבודה אחת או עם חוברות עבודה מרובות, אך תוכל להשוות את הרשימות קל יותר אם תעתיק את המידע שלהן לגליון עבודה יחיד.
שלב 2. תן לכל פריט רשימה מזהה ייחודי
אם שתי הרשימות שלך אינן חולקות דרך נפוצה לזהות אותן, ייתכן שיהיה עליך להוסיף עמודה נוספת לכל רשימת נתונים המזהה את הפריט הזה ל- Excel, כך שתוכל לראות אם פריט ברשימה נתונה קשור לפריט ברשימה השנייה. אופיו של מזהה זה יהיה תלוי בסוג הנתונים שאתה מנסה להתאים. תזדקק למזהה לכל רשימת עמודות.
- עבור נתונים פיננסיים הקשורים לתקופה מסוימת, כגון רישומי מס, זה יכול להיות תיאור של נכס, התאריך בו נרכש הנכס או שניהם. במקרים מסוימים, רשומה עשויה להיות מזוהה עם מספר קוד; עם זאת, אם אותה מערכת לא משמשת לשתי הרשימות, מזהה זה עשוי ליצור התאמות בהן אין או להתעלם מההתאמות שצריך לבצע.
- במקרים מסוימים, תוכל לקחת פריטים מרשימה אחת ולשלב אותם עם פריטים מרשימה אחרת ליצירת מזהה, כגון תיאור נכס פיזי והשנה שבה נרכש. כדי ליצור מזהה כזה, אתה מחבר (מוסיף, משלב) נתונים משני תאים או יותר באמצעות אמפרסנד (&). כדי לשלב תיאור פריט בתא F3 עם תאריך בתא G3, מופרד ברווח, תזין את הנוסחה '= F3 & "" & G3' בתא אחר בשורה זו, כגון E3. אם ברצונך לכלול רק את השנה במזהה (מכיוון שרשימה אחת משתמשת בתאריכים מלאים והשנייה משתמשת רק בשנים), היית כולל את הפונקציה YEAR על ידי הזנת '= F3 & "" & YEAR (G3)' בתא E3 במקום זאת. (אל תכלול את הציטוטים היחידים; הם שם רק כדי לציין את הדוגמה.)
- לאחר שיצרת את הנוסחה, תוכל להעתיק אותה לכל שאר התאים של עמודת המזהה על ידי בחירת התא עם הנוסחה וגרירת ידית המילוי מעל התאים האחרים של העמודה שבה ברצונך להעתיק את הנוסחה. כאשר אתה משחרר את לחצן העכבר שלך, כל תא שגררת מעלה יאוכלס בנוסחה, כאשר הפניות לתאים יותאמו לתאים המתאימים באותה שורה.
שלב 3. סטנדרטיזציה של נתונים במידת האפשר
בעוד שהמוח מזהה כי "Inc" ו- "Incorporated" פירושו אותו דבר, Excel לא אלא אם יש לך את זה מחדש מחדש מילה זו או אחרת. באופן דומה, ייתכן שתשקול ערכים כגון $ 11, 950 ו- $ 11, 999.95 כקרובים מספיק להתאמה, אך Excel לא תעשה זאת אלא אם תגיד זאת.
- תוכל להתמודד עם כמה קיצורים, כגון "Co" עבור "Company" ו- "Inc" עבור "Incorporated על ידי שימוש בפונקציית מחרוזת LEFT לקיצוץ התווים הנוספים. קיצורים אחרים, כגון" Assn "עבור" Association ", עשויים להיות הטובים ביותר טופל על ידי הקמת מדריך סגנון הזנת נתונים ולאחר מכן כתיבת תוכנית לחיפוש ולתיקון פורמטים לא תקינים.
- עבור מחרוזות מספרים, כגון מיקודים שבהם חלק מהערכים כוללים את הסיומת ZIP+4 ואחרים לא, תוכל שוב להשתמש בפונקציית המחרוזת LEFT כדי לזהות ולהתאים רק את המיקודים הראשיים. כדי ש- Excel יזהה ערכים מספריים קרובים אך לא זהים, תוכל להשתמש בפונקציה ROUND כדי לעגל ערכים סגורים לאותו מספר ולהתאים אותם.
- ניתן להסיר רווחים נוספים, כגון הקלדת שני רווחים בין מילים במקום אחת, באמצעות הפונקציה TRIM.
שלב 4. צור עמודות לנוסחת ההשוואה
בדיוק כפי שהיית צריך ליצור עמודות עבור מזהי הרשימות, יהיה עליך ליצור עמודות עבור הנוסחה שעושה את ההשוואה עבורך. תצטרך עמודה אחת לכל רשימה.
תרצה לתייג את העמודות האלה במשהו כמו "חסר?"
שלב 5. הזן את נוסחת ההשוואה בכל תא
לנוסחת ההשוואה, תשתמש בפונקציית MATCH המקוננת בתוך פונקציית Excel אחרת, ISNA.
- הנוסחה לובשת צורה של "= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))", כאשר תא בעמודה המזהה של הרשימה הראשונה מושווה מול כל אחד מהמזהים ברשימה השנייה ל תראה אם זה תואם לאחד מהם. אם הוא אינו תואם, רשומה חסרה והמילה "TRUE" תוצג בתא זה. אם הוא אכן תואם, הרשומה קיימת והמילה "FALSE" תוצג. (בעת הזנת הנוסחה, אל תכלול את הציטוטים המצורפים.)
- תוכל להעתיק את הנוסחה לתאים הנותרים בעמודה באותו אופן שבו העתקת את נוסחת מזהה התא. במקרה זה, רק הפניה לתא לתא המזהה משתנה, שכן הצבת סימני הדולר מול השורה והפניות העמודות לתאים הראשונים והאחרונים ברשימת מזהי התא השני הופכת אותם להפניות מוחלטות.
- אתה יכול להעתיק את נוסחת ההשוואה של הרשימה הראשונה לתא הראשון של העמודה עבור הרשימה השנייה. לאחר מכן יהיה עליך לערוך את הפניות התא כך ש- "G3" יוחלף בהתייחסות לתא המזהה הראשון של הרשימה השנייה ו" $ L $ 3: $ L $ 14 "יוחלף בתא המזהה הראשון והאחרון של רשימה שנייה. (השאר את סימני הדולר והמעי הגס בשקט.) לאחר מכן תוכל להעתיק נוסחה ערוכה זו לתאים הנותרים בשורת ההשוואה של הרשימה השנייה.
שלב 6. מיין את הרשימות כדי לראות ערכים שאינם תואמים בקלות רבה יותר, במידת הצורך
אם הרשימות שלך גדולות, ייתכן שיהיה עליך למיין אותן כדי לחבר את כל הערכים שאינם תואמים. ההוראות בשלבי המשנה להלן ימירו את הנוסחאות לערכים כדי להימנע משגיאות חישוב מחדש, ואם הרשימות שלך גדולות, תימנע זמן חישוב מחדש ארוך.
- גרור את העכבר מעל כל התאים ברשימה כדי לבחור אותו.
- בחר העתק מתפריט העריכה ב- Excel 2003 או מקבוצת הלוח של סרט הבית ב- Excel 2007 או 2010.
- בחר הדבק מיוחד מהתפריט 'עריכה' ב- Excel 2003 או מהלחצן הנפתח 'הדבק' בקבוצת הלוח של סרט הבית של Excel 2007 או 2010s.
- בחר "ערכים" מהרשימה הדבק בשם בתיבת הדו -שיח הדבקה מיוחדת. לחץ על אישור כדי לסגור את תיבת הדו -שיח.
- בחר מיין בתפריט הנתונים ב- Excel 2003 או בקבוצת המיון והסינון של סרט הנתונים ב- Excel 2007 או 2010.
- בחר "שורה בכותרת" מרשימת "טווח הנתונים שלי יש" בתיבת הדו -שיח מיין לפי, בחר "חסר?" (או השם שנתת למעשה לכותרת העמודה של ההשוואה) ולחץ על אישור.
- חזור על שלבים אלה עבור הרשימה האחרת.
שלב 7. השווה את הפריטים שאינם תואמים מבחינה ויזואלית כדי לראות מדוע הם אינם תואמים
כפי שצוין לעיל, Excel נועד לחפש התאמות נתונים מדויקות אלא אם כן הגדרת אותו לחיפוש התאמות משוערות. אי ההתאמה שלך יכולה להיות פשוטה כמו העברה מקרית של אותיות או ספרות. זה יכול להיות גם משהו שדורש אימות עצמאי, כגון בדיקה אם יש צורך לדווח על נכסים רשומים מלכתחילה.
שיטה 2 מתוך 2: עיצוב מותנה עם COUNTIF
שלב 1. העתק את רשימות הנתונים לגליון עבודה יחיד
שלב 2. החליט באיזו רשימה ברצונך להדגיש רשומות תואמות או שאינן תואמות
אם ברצונך להדגיש רשומות ברשימה אחת בלבד, סביר להניח שתרצה להדגיש את הרשומות הייחודיות לרשימה זו; כלומר רשומות שאינן תואמות רשומות ברשימה האחרת. אם ברצונך להדגיש רשומות בשתי הרשימות, תרצה להדגיש רשומות התואמות זו לזו. לצורך דוגמה זו, נניח שהרשימה הראשונה תופסת את התאים G3 עד G14 והרשימה השנייה תופסת את התאים L3 עד L14.
שלב 3. בחר את הפריטים ברשימה שבה ברצונך להדגיש פריטים ייחודיים או תואמים
אם ברצונך להדגיש פריטים תואמים בשתי הרשימות, יהיה עליך לבחור את הרשימות אחת אחת ולהחיל את נוסחת ההשוואה (המתוארת בשלב הבא) על כל רשימה.
שלב 4. החל את נוסחת ההשוואה המתאימה
לשם כך יהיה עליך לגשת לתיבת הדו -שיח עיצוב מותנה בגירסת Excel שלך. ב- Excel 2003, אתה עושה זאת על ידי בחירת עיצוב מותנה מתפריט העיצוב, ואילו ב- Excel 2007 ו- 2010, לחץ על הלחצן עיצוב מותנה בקבוצת הסגנונות של סרט הבית. בחר את סוג הכלל כ"נוסחה "והזן את הנוסחה שלך בשדה ערוך את תיאור הכלל.
- אם ברצונך להדגיש רשומות ייחודיות לרשימה הראשונה, הנוסחה תהיה "= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)", כאשר טווח התאים של הרשימה השנייה יוצג כערכים מוחלטים והפניה לתא הראשון של הרשימה הראשונה כערך יחסי. (אל תזין את הציטוטים הקרובים.)
- אם ברצונך להדגיש רשומות ייחודיות לרשימה השנייה, הנוסחה תהיה "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)", כאשר טווח התאים של הרשימה הראשונה יוצג כערכים מוחלטים והפניה לתא הראשון של הרשימה השנייה כערך יחסי. (אל תזין את הציטוטים הקרובים.)
- אם ברצונך להדגיש את הרשומות בכל רשימה שנמצאות ברשימה השנייה, תזדקק לשתי נוסחאות, אחת לרשימה הראשונה ואחת לשניה. הנוסחה לרשימה הראשונה היא "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", ואילו הנוסחה לרשימה השנייה היא COUNTIF ($ G $ 3: $ G $ 14, L3> 0) ". צוין קודם לכן, אתה בוחר ברשימה הראשונה להחלת הנוסחה שלה ולאחר מכן בחר ברשימה השנייה להחלת הנוסחה שלה.
- החל כל עיצוב שתרצה להדגיש את הרשומות המסומנות. לחץ על אישור כדי לסגור את תיבת הדו -שיח.
וידאו - על ידי שימוש בשירות זה, מידע מסוים עשוי להיות משותף עם YouTube
טיפים
- במקום להשתמש בהתייחסות לתאים בשיטת העיצוב המותנה COUNTIF, תוכל להזין ערך שיש לחפש ולסמן רשימה אחת או יותר עבור מופעים של ערך זה.
- כדי לפשט את טפסי ההשוואה, תוכל ליצור שמות לרשימה שלך, כגון "List1" ו- "List2". לאחר מכן, בעת כתיבת הנוסחאות, שמות רשימה אלה יכולים להחליף את טווחי התאים המוחלטים המשמשים בדוגמאות שלמעלה.
