אימות נתונים כפול – פתרון באמצעות נוסחאות

טיפים, רשימות נפתחות אין תגובות »
מרץ 182012

אתמול הראינו שיטה כדי למנוע הכנסת של ערכים כפולים בתא בו רשימה נפתחת של אימות נתונים. בפוסט הזה נדגים פתרון אחר, מבוסס נוסחאות.

הפתרון מבוסס מקרו מחקה את אימות הנתונים. הפתרון עם נוסחאות מבוסס על גישה אחרת. במקום לשאול "איך אני מפעיל שתי שיטות שונות של אימות נתונים על אותו תא", אנו שואלים "איך אני מונע הכנסה בתא של ערכים שכבר קיימים בטבלה".

הפתרון מבוסס על בנייה של רשימה מתגלגלת עם אימות נתונים מבוססת על רשימה דינמי בה מופיעים רק שמות שטרם הוכנסו בטבלה.

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

המודל של הפתרון מבוסס על רשימה של כל השמות המותרים ורשימה נוספת בה מופיעים רק שמות שטרם שובצו בטבלה.

הטווח A5:A16 מכיל את כל השמות.

התאים הטווח B5:B16 ("aux1") מכיל את התוצאות של הנוסחה

=IF(COUNTIF($H$5:$H$7,$A$5:$A$16)=1,"",A5)

המשמעות: אם שם ברשימת השמות כבר מופיע בטבלה, התוצאה תהיה string באורך 0 (התא אינו ריק שהרי מכיל את הנוסחה); אם השם טרם מופיע בטבלה הוא יופיע ב-"aux1".

התאים בטווח C5:C16 מכילים את הנוסחה:

=COUNTIF($B$5:$B$16,"<"&B6)

הנוסחה מדרגת את הערכים של הטווח aux1; תאים ללא שם מקבלים את הערך 0, תאים אחרים מקבלים ערך לפי סדר האלף בית.

הטווח aux3 בנוי עם הנוסחה

=LARGE($C$5:$C$16,$B$1-ROW()+5)

הנוסחה מסדרת את הערכים שך aux2 בסדר עולה; תאים עם ערך 0 מחזירים ערך !NUM# טעות.

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

=INDEX($A$5:$A$16,MATCH(D5,$C$5:$C$16,0))

שמתרגם את רשימת המספרים של aux3 לשמות.

הצעד האחרון הוא ליצור שם (Defines Name) שיכלול את השמות של Available באופן דינמי

השם List_of_Names2 מפנה לנוסחה

=OFFSET(formula!$E$5,0,0,formula!$B$1,1)

הנוסחה משתמשת בתא  B1 שמכילה את הנוסחה

=COUNTIF($C$5:$C$16,">0")

כך השם המוגדר שלנו יכלול רק את השמות ולא את ערכי הטעות.

אפשר להסתיר את העמודות A:E

בטבלה שם מגדירים רשימה נפתחת עם אימות נתונים שמשתמש בשם List_of_Names2 כמקור הנתונים

ניתן להוריד את הקובץ, כולל גם את המקרו של הפוסט הקודם, כאן.

כולל את התגים:

אימות נתונים כפול – פתרון באמצעות מקרוס

Vba, רשימות נפתחות אין תגובות »
מרץ 172012

באחת התגובות בפוסט הקודם שלי בנושא רשומות כפולות ב-Excel שואלת אותי יעל  "האם ניתן למנוע רשומות כפולות גם ברשימה שהגדרתי כרשימה נפתחת ?"

התשובה היא כן, אבל הפתרון אינו טריוויאלי. יעל מבקשת להפעיל שני סוגים שונים של אימות נתונים:

1 – אימות נתונים – מותאם אישית-נוסחה, כפי שהראינו בפוסט הקודם, על מנת למנוע הכנסה של רשומה שכבר קיימת בטווח;

2 – אימות נתונים-רשימה, על מנת ליצור רשימה נפתחת של ערכים שמוצר לרשום בטווח.

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

נניח את המצב הזה

בטווח C2:C8 יצרנו טבלה (Table) ובה תאים שלה יצרנו רשימה נפתחת שניזונה מהערכים שבטווח A3:A14. על מנת להקל על העבודה שלנו, יצרנו שם name)(("List_of_Names" שמתייחס לטווח A3:A14; ושינינו את השם של הטבלה בטווח C2:C8 ל"tblWorkers".

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

ה-events הם סוג מיוחד של מקרוס שמופעלות כאשר מתרחש אירוע מסוים בגיליון. הקוד של ה-event יש לכתוב במודול של הגיליון הרלוונטי  ולא במודול כללי של העורך Vbe.

על מנת לגשת של מודול של הגיליון מצביעים על לשונית השם של הגיליון, לוחצים על כפתור ימין של העכבר ובוחרים באופציה "הצג קוד"

במודל של הגילייון כותבים את הקוד הזה

1
2
3
4
5
6
7
8
9
10
11
12
(Private Sub Worksheet_Change(ByVal Target As Range
Dim bcheckDup As Integer
If Union(Target, Range("tblWorkers")).Address = Range("tblWorkers").Address Then
Application.EnableEvents = False
(bcheckDup = WorksheetFunction.CountIf(Range("tblWorkers"), Target
If bcheckDup &gt; 1 Then
"השם כבר מופיע בטבלה" MsgBox
Target.ClearContents
End If
Application.EnableEvents = True
End If
End Sub

ה-event מופעל כאשר מתרחש שינוי באיד התאים בגיליון (Worksheet_Change). בצעד הראשון הקוד בודק האם השינוי התרחש באחד התאים של הטבלה; אם השינוי היה באחד התאים של הטבלה הקוד בודק האם השם כבר מופיע באמצעות הפונקציה COUNTIF; עם התוצאה של COUNTIF גדולה מ-1, השם כבר קיים והקוד יוצר הודעה (MsgBox) ומוחק את הערך שהוכנס.

אפשר להסתיר את עמודת A.

הסירטון הבא מדגים את הטכניקה

בפוסט הבא נדגים פתרון באמצעות נוסחאות.

כולל את התגים:

עדכון אוטומטי של טבלאות ציר ב-Excel

Vba, טבלאות ציר אין תגובות »
ספט' 232011

ל-Excel אין שיטה מובנית לעדכון אוטומטי של טבלאות ציר. לכן, על מנת לעדכן טבלאות ציר כשחלים שינוים בבסיס הנתונים צריך ללחוץ על הכפתור ה-Refresh (או על הקיצור Alt+F5) או על Refresh All (Ctrl+Alt+F5) אם רוצים לרענן את כל טבלאות הציר בחוברת.

אפשר לגרום לעדכון אוטומטי של טבלאות הציר בחוברת באמצעות event פשוט. Event הינו מקרו שרץ כאשר מתרחש אירוע (event) מסוים בגיליון או חוברת הפעיל. הקוד של ה-event יש לכתוב במודול מיוחד של הגיליון.

נניח חוברת המכילה שני גיליונות: data (בסיס הנתונים של טבלת הציר) ו-report (טבלת הציר)

בלשונית של הגיליון data פותחים את התפריט ומפעילים את האופציה "view code".

בחלון של עורך ה-Vba שנפתח בוחרים ב"Worksheet" בחלון השמאלי של אזור העריכה

בחלון הימני בוחרים ב-Deactivate

הקוד, כאמור, פשוט

1
2
3
Private Sub Worksheet_Deactivate()
ActiveWorkbook.RefreshAll
End Sub

אפשר למחוק את ה- Worksheet_SelectionChange (זוהי ברירת המחדל של Excel)

ה-event גורם לכך שכל פעם שעוברים מ-data לגיליון אחר, כל טבלאות הציר של החוברת מתעדכנים.

אם טבלת הציר מבוססת על נתונים חיצוניים, נשתמש ב-Worksheet_Activate של הגיליון המכיל את טבלת הציר (report בדוגמה שלנו).

כולל את התגים: ,
רשומות ישנות יותר
© 2013 LookUp - פתרונות Excel והדרכה מתקדמת Suffusion WordPress theme by Sayontan Sinha