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

טיפים, רשימות נפתחות אין תגובות »
מרץ 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.

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

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

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

רשימות נפתחות תלויות עם טווח דינמי

רשימות נפתחות 2 תגובות »
מרץ 222011

כבר הראינו איך ליצור רשימות נפתחות תלויות ב-Excel באמצעות אימות נתונים והפונקציה INDIRECT.

נניח שתי רשימות של לקוחות: לקוחות שוק מקומי וייצוא. רשימת הלקוחות המקומיים נמצאת בטווח B3:B22; לקוחות ייצוא בטווח D3:D19; בוחרים אתת השוק בתא H2 ובתא H3 יפתח רשימת הלקוחות המתאימה

בתא H2 הגדרנו את אימות הנתונים כך:

ובתא H3 הגדרנו

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

כפי שראינו, אפשר להגדיר את טווחי הלקוחות בצורה דינאמית בעזרת הפונקציות OFFSET או INDEX. למשל, נוכל להגדיר את טווחי הרשימה של לקוחות כך

מקומי    =OFFSET(customers!$B$3,0,0,COUNTA(customers!$B:$B)-1,1)

ייצוא       =OFFSET(customers!$D$3,0,0,COUNTA(customers!$D:$D)-1,1)

אבל הרשימה התלויה בתא H3 לא תיפתח. כאמור, INDIRECT לא מקבלת נוסחאות כארגומנט אלא אך ורק טקסט המתאר טווח.

יש שתי דרכים להתגבר על המגבלה הזאת:

1 – הדרך הקלה: טבלאות (רק ב-Excel 2007/10 וב-Excel 2003)

2 – הדרך הקשה: נוסחאות (לכל הגרסאות)

פתרון בעזרת טבלאות.

"טבלאות" הוא כלי שהוכנס לראשונה בגרסה 2003 של Excel. את השימוש בטבלאות ליצירת טווחים דינמיים הראינו בפוסט "גרפים דינמיים ב-Excel באמצעות טבלה".

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

פתרון בעזרת נוסחאות.

על מנת להתגבר על המגבלה של הפונקציה INDIRECT נשתמש בחישוב ביניים ובטבלת עזר.

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

לטווח לקוחות השוק המקומי נשתמש בנוסחה

=CONCATENATE("B3",":",ADDRESS(COUNTA(B:B)+1,2))

לייצוא

=CONCATENATE("D3",":",ADDRESS(COUNTA(D:D)+1,4))

את התוצאות נארגן בטבלה שבטווח J4:K5 בדוגמה שלנו ובתא K2 נכניס את הנוסחה

=VLOOKUP(H2,$J$4:$K$5,2,0)

בהגדרת הרשימה הנפתחת בתא H3 נשתמש ב:

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