שיטות שונות להתאמת נתונים ב- Excel
ישנן שיטות שונות להתאמת נתונים ב- Excel, אם ברצוננו להתאים את הנתונים באותה עמודה נניח שאנחנו רוצים לבדוק כפילויות נוכל להשתמש בעיצוב מותנה מכרטיסיית הבית או אחרת אם אנו רוצים להתאים את הנתונים בשניים או בעמודות שונות יותר נוכל להשתמש בפונקציות מותנות כמו פונקציה.
- שיטה מס '1 - שימוש בפונקציית Vlookup
- שיטה מס '2 - שימוש באינדקס + פונקציית התאמה
- שיטה מס '3 - צור ערך בדיקה משלך
כעת נדון בפירוט בכל אחת מהשיטות
אתה יכול להוריד תבנית Excel זו של נתוני התאמה כאן - תבנית Excel של נתוני התאמהמספר 1 - נתוני התאמה באמצעות פונקציית VLOOKUP
VLOOKUP משמש לא רק כדי לקבל את המידע הנדרש מטבלת הנתונים אלא הוא יכול לשמש גם ככלי פיוס. כשמדובר בפיוס או התאמת הנוסחה הנתונים VLOOKUP מובילה את הטבלה.
לדוגמא, עיין בטבלה הבאה.
יש לנו שתי טבלאות נתונים כאן, הראשונה היא נתונים 1 והשנייה היא נתונים 2.
כעת עלינו ליישב אם הנתונים בשתי טבלאות תואמים או לא. הדרך הראשונה להתאים את הנתונים היא פונקציית SUM ב- Excel לשתי טבלאות כדי להשיג את סך המכירות.
נתונים 1 - טבלה
נתונים 2 - טבלה
יישמתי את הפונקציה SUM עבור העמודה 'סכום מכירה' בטבלה. בשלב ההתחלה עצמו, קיבלנו את ההבדל בערכים. 1 נתונים טבלה המציגה את סך המכירות של 2,16,214 ו Data 2 טבלה המציגה את סך המכירות של 2,10,214 .
כעת עלינו לבחון זאת בפירוט. אז בואו להחיל את פונקציית VLOOKUP לכל תאריך.
בחר את מערך הטבלה כטווח נתונים 1 .
אנו זקוקים לנתונים מהעמודה השנייה וטווח החיפוש הוא FALSE כלומר התאמה מדויקת.
התפוקה מוצגת להלן:
בתא הבא מנכים את הערך המקורי עם ערך ההגעה.
לאחר ניכוי אנו מקבלים את התוצאה כאפס.
כעת העתק והדבק את הנוסחה לכל התאים כדי לקבל את ערכי השונות.
בתא G6 ו- G12 קיבלנו את ההבדלים.
בשנת הנתונים 1 יש לנו 12,104 עבור התאריך 04-Mar-2019 ובשנת הנתונים 2 יש לנו 15,104 לאותו מועד, כך יש הבדל של 3000.
באופן דומה, לתאריך 18 במרץ 2019 בנתונים 1, יש לנו 19351 ובנתונים 2 יש לנו 10351, כך שההפרש הוא 9000.
# 2 - נתוני התאמה באמצעות פונקציית INDEX + MATCH
עבור אותם נתונים, אנו יכולים להשתמש בפונקציה INDEX + MATCH. אנו יכולים להשתמש בזה כחלופה לפונקציה VLOOKUP.
פונקציית INDEX משמשת לקבלת הערך מהעמודה שנבחרה בהתבסס על מספר השורה שסופק. כדי לספק את מספר השורה עלינו להשתמש בפונקציית MATCH בהתבסס על ערך LOOKUP.
פתח את פונקציית INDEX בתא F3.
בחר את המערך כטווח עמודות התוצאה כלומר B2 עד B14.
על מנת לקבל את מספר השורה פתח את פונקציית MATCH כעת כארגומנט הבא.
בחר את ערך החיפוש כתא D3.
לאחר מכן בחר מערך בדיקת מידע כעמודה תאריך מכירה בנתונים 1.
בסוג ההתאמה בחר "0 - התאמה מדויקת".
סגור שתי סוגריים ולחץ על מקש Enter כדי לקבל את התוצאה.
זה גם נותן את אותה התוצאה כמו VLOOKUP בלבד. מכיוון שהשתמשנו באותם נתונים, קיבלנו את המספרים כפי שהם
# 3 - צור ערך בדיקה משלך
כעת ראינו כיצד להתאים נתונים באמצעות פונקציות Excel. כעת נראה את התרחיש השונה של זמן אמת. לדוגמא זו עיין בנתונים הבאים.
בנתונים שלעיל, יש לנו נתוני מכירות של Zone-Wise ו- Data, כפי שמוצג לעיל. עלינו לבצע שוב את תהליך התאמת הנתונים. בואו להחיל את פונקציית VLOOKUP לפי הדוגמה הקודמת.
יש לנו שונות רבה. בואו נבחן כל מקרה לגופו.
בתא I5 קיבלנו את השונות של 8300. בואו נסתכל על הטבלה הראשית.
למרות שערך הטבלה הראשי הוא 12104 קיבלנו את הערך 20404 מפונקציית VLOOKUP. הסיבה לכך היא ש- VLOOKUP יכול להחזיר את ערך ערך החיפוש הראשון שנמצא.
במקרה זה, ערך הבדיקה שלנו הוא תאריך כלומר 20 במרץ 2019. בתא הנ"ל עבור אזור הצפון לאותו תאריך יש לנו ערך של 20404, ולכן VLOOKUP החזיר ערך זה גם עבור אזור מזרח.
כדי להתגבר על נושא זה עלינו ליצור ערכי בדיקה ייחודיים. מערבבים Zone, תאריך ומכירות הסכום בשני נתונים 1 & Data 2.
נתונים 1 - טבלה
נתונים 2 - טבלה
כעת יצרנו ערך ייחודי לכל אזור עם הערך המשולב של אזור, תאריך מכירה וסכום מכירה.
שימוש בערכים ייחודיים אלה מאפשר להחיל את פונקציית VLOOKUP.
החל את הנוסחה על כל התאים, נקבל את השונות של אפס בכל התאים.
ככה, באמצעות פונקציות Excel אנו יכולים להתאים את הנתונים ולמצוא את השונות. לפני החלת הנוסחה עלינו לבדוק את הכפילויות בערך החיפוש לצורך התאמה מדויקת. הדוגמה לעיל היא המחשה הטובה ביותר של ערכים כפולים בערך בדיקה. בתרחישים כאלה, עלינו ליצור ערכי בדיקה ייחודיים משלנו ולהגיע לתוצאה.