בפוסט הקודם הראנו איך למנוע יצירת רשומות כפולות ב-Excel בעזרת אימות נתונים (Data Validation).

שם התייחסנו למצב בו כל רשומה מתייחסת רק לעמודה אחת. בפוסט זה נתייחס למצב בו הרשומה לאימות מורכבת משתי עמודות או יותר. דוגמה למצב זה יכולה להיות טבלה בת שני שדות: שם פרטי ושם משפחה. במקרה זה יש לבצע את בדיקת הכפילות על צירוף של שני השדות.

נתחיל בדוגמה פשוטה בה ידוע מראש מספר השורות (רשומות) שיהיו בטבלה. בהמשך נראה מצב של טבלה דינאמית בה מספר השורות אינו ידוע מראש.

הגדרנו מראש טבלה בת 15 שורות ושורת כותרת (B2:C17)

בוחרים את כל הטווח בעמודה C, פותחים את תפריט אימות הנתונים ובאופציה "מותאם אישי" כותבים את הנוסחה

=COUNTIFS($B$3:$B$17,B3,$C$3:$C$17,C3)=1

הפונקציה COUNTIFS היא פונקציה חדשה שקיימת רק בגרסאות 2007 ו-2010 של Excel. בגרסאות קלאסיות של Excel (97-2003) נשתמש בפונקציה SUMPRODUCT בנוסחה הזאת

=SUMPRODUCT(–($C$3:$C$17&$B$3:$B$17=C3&B3))=1

אם מספר השורות בטבלה אינו ידוע מראש אפשר, כמובן, להעתיק את אימות הנתונים לתאים החדשים או להשתמש בפונקציונאליות "טבלה" (Excel 2007/10, ב-Excel 2003 "רשימה") של התפריט "נתונים".

כאשר הופכים טווח של תאים ל"טבלה" (או "רשימה"), כל התכונות של העמודה מועתקות אוטומטית לתאים החדשים של העמודה. שיטה זאת מבטיחה שתמיד יופעל אימות נתונים בכל השורות של הטבלה ולא יפלו טעויות.

הסרטון הבא מדגים את תהליך יצירת הטבלה ב-Excel 2007. מכיוון שטווח הטבלה יגדל (או יקטן) נעשה שינוי קטן בנוסחה שלנו

=COUNTIFS($B$3:B6,B3,$C$3:C6,C3)=1

ב-Excel 2003: נתונים—רשימה

הורדת הקובץ עם הדוגמה.