סיכומי ביניים בגיליונות מוגנים

Vba, ניהול נתונים אין תגובות »
יולי 072011

אחד הכלים היעילים לניתוח וסיכום נתונים ב-Excel הוא "סיכום ביניים"

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

1 – בוחרים אחד התאים בטבלה

2 – מגדירים את שדה השינוי והפונקציה לסיכום

3 – לוחצים על "אישור"

לחיצה על כפתור "2" תציג את הטבלה כך:

לחיצה על סימן ה-"+" ליד כל סיכום ביניים או על כפתור   "3"  תחשוף את השורות המוסתרות

בגיליונות מוגנים לא ניתן לחשוף שורות מוסתרות או להסתיר שורות ולכן לא נוכל להשתמש בסכום ביניים. אבל יש דרך לעקוף את המגבלה הזאת.

במקום להגן על הגיליון באמצעות התפריט "סקירה-הגנת הגיליון"

נשתמש ב-event מסוג WorkBook_Open.

Event הוא מקרו שרץ כאשר אירוע מסוים (event) מתרחש. במקרה שלנו, כאשר החוברת נפתח.

הקוד של ה-event יש להכניס במודול של האובייקט ThisWorkbook  (חוברת_עבודה_זו)

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

חילוץ מספרים או אותיות של קודים אלפאנומריים ב-Excel

Vba, פונקציות ונוסחאות 2 תגובות »
דצמ' 042010

לפני כמה ימים נשאלתי ע"י קולגה בעבודה איך לחלץ את המספרים או את האותיות מקוד אלפאנומרי (כגון, ABC123). הדרך לבצע את המשימה תלויה האם המספרים מופיעים בקוד מקובצים בקבוצה אחת או אם יש מספר קבוצות מספרים בקוד.

1 – פתרון עם "טקסט לעמודות"

אם לקוד האלפאנומרי תבנית קבועה

num_in_str_heb01

נוכל להשתמש ב"טקסט לעמודות":

א.      בוחרים את הטווח

ב.      ב"טקסט לעמודות" בוחרים באופציה "רוחב קבוע"

ג.       מקליקים במקום הרצוי על מנת ליצור את ההפרדה

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

2 – פתרון עם נוסחת מערך

אם אין לקוד תבנית קבועה, נוכל להשתמש בנוסחת מערך זו שהציע Ashish Mathur

=MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$8),1)),0),COUNT(1*MID(A2,ROW($1:$8),1)))

num_in_str_heb02

זאת נוסחת מערך שמכניסים לתא ע"י לחיצה על המקשים Ctrl+Shift+Enter

כדי לחלץ את האותיות במקום המספרים, יש להחליף את הארגומנט TRUE בפונקציה MATCH בערך FALSE

num_in_str_heb03

לנוסחה זו מספר חסרונות:

  • היא מסורבלת ויש להתאים אותה לגודל הקוד האלפאנומרי;
  • במקרה של מספר קבוצות מספרים או אותיות התוצאה יכול להיות לא נכונה

num_in_str_heb04

3 – פתרון עם UDF (פונקציה מוגדרת ע"י המשתמש – מקרו)

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Function ext_num_let(rngCell As Range, tType As Boolean)
Dim iX As Integer, strResult As String, temp As String

Select Case tType
Case 1
For iX = 1 To Len(rngCell)
temp = Mid(rngCell, iX, 1)
If IsNumeric(temp) Then
strResult = strResult & temp
End If
Next iX
Case Else
For iX = 1 To Len(rngCell)
temp = Mid(rngCell, iX, 1)
If Not IsNumeric(temp) Then
strResult = strResult & temp
End If
Next iX
End Select

ext_num_let = strResult

End Function

לפונקציה שני ארגומנטים
num_in_str_heb05

RngCell: התא המכיל את הקוד האלפאנומרי

TType: על מנת לחלץ את המספרים מקלידים 1; 0 על מנת להוציא את האותיות

להלן מספר דוגמאות

num_in_str_heb06

ניתן להוריד את החוברת עם הדוגמאות והקוד של ה-UDF כאן

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

שליפת נתונים עם VLOOKUP ממספר טבלאות לא ידוע

Vba, פונקציות ונוסחאות אין תגובות »
ספט' 122010

בפוסטים קודמים  הראנו איך להשתמש ב-VLOOKUP עם מספר טבלאות או עם טבלה מרובת עמודות.

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

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

vlmt01

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

אציג שני פתרונות: עם פונקציות Excel ועם פונקציה מוגדרת ע"י המשתמש (UDF – user defined function)

פונקציות Excel

בשלב הראשון (ולא הכרחי) נגדיר את טווחי הטבלאות בתוך שמות (names)

vlmt02

ב-Excel "קלאסי" (גרסאות 97-2003) נשתמש בשילוב של פונקציות IF, ISERROR ו-VLOOKUP

=IF(ISERROR(VLOOKUP(C3,ייצור,2,0)),IF(ISERROR(VLOOKUP(C3,לוגיסטיקה,2,0)),

VLOOKUP(C3,אחזקה,2,0),VLOOKUP(C3,לוגיסטיקה,2,0)),VLOOKUP(C3,ייצור,2,0))

vlmt03

ב-Excel 2007 החיים שלנו יהיו יותר קלים ונוכל להשתמש ב-IFERROR

=IFERROR(VLOOKUP(C7,ייצור,2,0),IFERROR(VLOOKUP(C7,לוגיסטיקה,2,0),VLOOKUP(C7,אחזקה,2,0)))

vlmt04

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

פתרון עם UDF (פונקציה מוגדרת ע"י המשתמש)

במודול רגיל של עורך ה-Vba כותבים את הקוד הבא

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _
Col_num As Integer, Optional Range_look As Boolean)

''''''''''''''''''''''''''''''''''''''''''''''''
'Published by OzGrid.com
'''''''''''''''''''''''''''''''''''''''''''''''''
Dim wSheet As Worksheet
Dim vFound

On Error Resume Next

For Each wSheet In ActiveWorkbook.Worksheets
With wSheet
Set Tble_Array = .Range(Tble_Array.Address)
vFound = WorksheetFunction.VLookup _
(Look_Value, Tble_Array, _
Col_num, Range_look)
End With
If Not IsEmpty(vFound) Then Exit For
Next wSheet

Set Tble_Array = Nothing
VLOOKAllSheets = vFound
End Function

הפונקציה תופיע באשף הפונקציות, תחת  הקטגוריה

vlmt05

מכיוון שכמות השורות בכל טבלה אינה ידועה לנו, נשתמש בטווח העמודות  B:C כארגומנט ה-table_array

vlmt06

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