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