ארכיון התגית 'אקסל'

מאי 08 2014

הצגת גליון בתוך טופס

מאת: שלומי מסורי נושאים: Excel

בעמוד הפייסבוק של אקסל מעל ומעבר עלתה שאלה,

כיצד ניתן להציג מידע מתאים בגליון בתצוגה דומה על UserForm, כלומר משהו דומה לזה:

,

אז יש מספר שיטות הצגה,

  1. הטמעת אוביקט גליון אמיתי בטופס.
  2. שימוש באוביקט מסוג FLAXGRID שיאפשר קישור של הנתונים והצגתם בצורת רשת.
  3. שימוש בLISTBOX להצגת הנתונים (אך ללא קוי רשת)

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

כך שהחלטתי להרים את הכפפה ולהציג עוד גישה,

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

הערה: הקוד להלן מבצע הצגה חד כיוונית ולא דינמית.

ראשית יש ליצור UserForm מספיק גדול שיכיל את הטבלה, יש פרוצדורה אחת כללית שבונה את הטבלה, ופונקציה שקוראת לה באתחול הטופס.

ולהלן הקוד:

Sub AddGrid(top As Integer, left As Integer, rng As String, Optional cellWidth As Integer = 80, Optional cellHeight As Integer = 16)
If rng = "" Then Exit Sub
‘אם אין הפניה לטווח – צא
Dim rang As Range
Set rang = Range(rng)        ` ‘בניית אוביקט טווח

Dim txtSampleTextBox As MSForms.TextBox
‘הגדרת האוביקט מסוג שדה טקסט

For x = 0 To rang.rows.Count – 1 ‘עבור כל שורת נתונים

If top + (x + 1) * (cellHeight – 2) < Me.InsideHeight Then
‘בדוק שאין חריגה משטח הטופס

For y = 0 To rang.Columns.Count – 1 ‘עבור כך עמודה בטווח

If left + (y + 1) * (cellWidth – 2) < Me.InsideWidth Then
‘בדוק שאין חריגה משטח הטופס

Set txtSampleTextBox = Controls.Add("Forms.TextBox.1″, "txtSampleTextBox", True(
‘הוסף אוביקט שדה טקסט
With txtSampleTextBox ‘הגדר את המשתנים של השדה טקסט
.
top = top + x * (cellHeight – 2) ‘גובה התחלתי של הפינה השמאלית עליונה
.
left = left + y * (cellWidth – 2)
.
Width = cellWidth
‘רוחב התא
.Height = cellHeight
‘גובה התא
.Locked = True
‘חסימת התא לעריכה
.BorderStyle = fmBorderStyleSingle
‘עיצוב גבול התא
.Name = "GridCell_" & x & "_" & y
‘הגדרת שם לתא
.Value = rang.Cells(x + 1, y + 1).Text ‘איכלוס התא במידע מהגליון

End With

End If

Next

End If

Next
End Sub

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

Private Sub UserForm_Initialize()
בנקודה 10 פיקסלים מלמעלה ו40 משמאל בנה את הטבלה והצג את הנתונים מטווח C5:E15
AddGrid 10, 40, "C5:E15″
End Sub

כל שנותר עכשיו זה לבנות את שאר הפקדים בטופס כראות עיניכם,

בהצלחה


עדיין אין תגובות

אפר' 21 2014

צביעת שורה בעזרת עיצוב מותנה ותיבת סימון

מאת: שלומי מסורי נושאים: Excel

משה העלה שאלה:

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

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

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


אני אוסיף לכל שורה תיבת סימון:

לשונית מפתחים ß הוסף ß תיבת סימון (בקבוצה העליונה)

אם לא מופיעה לכם לשונית מפתחים: לכו לקובץ ß אפשרויות ß סרגלים מותאמים ß סמנו שם v ליד Developer

הזיזו את התיבת סימון למקום הרצוי:


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


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

במקרה שלנו לתא E2.

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

עמדו על התא E2
ß קיצור מקלדת לתיבת דו שיח עיצוב תאים CTRL+1
ß מותאם אישית ß הקלידו ;;; בתא סוג    


 

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

נסמן את השורה בטבלה ß עיצוב מותנה ß כלל חדש ß השתמש בנוסחה:


בשורת הנוסחה נכתוב: $E2=

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

ושם נגדיר בלשונית מילוי את הצבע הרצוי:


נאשר


ונאשר שוב,

זהו, בסימון תיבת הסימון תצבע השורה כולה,

אפשר להסיר את תוכן הטקסט המוצמד לתיבה וזה יראה בסופו של דבר כך:


וכן על זו הדרך אפשר לעשות לכל שורה בנפרד…

 

בהצלחה.

עדיין אין תגובות

מרץ 24 2014

שיעורי אקסל בוידאו למתחילים

מאת: שלומי מסורי נושאים: Excel

שלום חברים,

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

אז אני ממליץ בחום למי שרוצה ללמוד את היסודות בוידאו לבדוק בערוץ YOUTUBE הבא:
הערוץ של אקסליסט

חברים, פרגנו לו…

נ.ב.: אני לא מכיר את היוצר…

עדיין אין תגובות

ינו' 01 2014

שימוש בVLOOKUP להשלמת פרטים

מאת: שלומי מסורי נושאים: Excel

רחלי שאלה שאלה שאני נתקל בה הרבה,

שלום
אשמח באם תוכל לעזור לי בהסבר איך אני בונה טבלה אם הנתונים הנ"ל :

יש לי טבלה 3 עמודות מקט, שם פרטי , מחיר

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

נתונים שיכולים לעזור

מק"ט 1300 = לפריט חולצה = מחיר 9 ש"ח

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

בתודה מכול הלב

רחלי עטר

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

וזה יראה כך:


בגליון הראשי מציבים נוסחה בעמודה B: וC

וזה יראה כך (בתצוגה הזאת הערכים בתאים מוצגים כנוסחאות)


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

וכך זה נראה בתצוגה רגילה:


אם רוצים להעלים את הודעת השגיאה N/A אפשר להעזר בנוסחה: ISNA

=ISNA(VLOOKUP(A2,$A$2:$C$5,3,FALSE))

בהצלחה,

8 תגובות

אוג' 13 2013

סיכום מופעים כפולים בעזרת טבלת ציר – PIVOT

מאת: שלומי מסורי נושאים: Excel

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

לדוגמה:

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

דוגמה נוספת, טבלה עם ציוני מבחנים לסטודנטים לפי ת.ז – ומעונינים לקבל מומצע ציונים לפי ת.ז.


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

אז נתחיל….

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

נקבל תיבת דו שיח: "יצרת טבלת ציר"


שם נוודא כי אנו מוכונים לטווח בתאים המלא של הנתונים בשדה הראשון, ובמקרה שלנו נעדיף ליצור את הטבלה בגליון חדש אז נשאיר את ההגדרות האחרות ללא שינוי, ונלחץ אישור.

נקבל גליון חדש עם מבנה שכזה:


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

עיקר העבודה היא בעורך השדות שבזרתו ניתן להגדיר אילו נתונים יוצגו ובאיזו צורה.

לצורך הדוגמה שלנו אנו נגרור את השדה מקט לקוביה של השורות (התא השמאלי תחתון) –נקבל מיד את רשימת המקטים בטבלה משמאל:


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


למעשה זהו!!! קיבלנו רשימת מקטים וסיכום של כל הכמויות מכל השורות .

ואם רוצים למשל לראות מומצע במקום סיכום?

אז יש ללחוץ על החץ הקטן לצד השדה בקובית הערכים ולבחור הגדרות שדה:



ושם לבחור במקום סיכום – מומוצע (Average),

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

ואישור.


הנה עוד משחק,

אם נחליף את המקט במוצר, נקבל את שם המוצר במקום המקט,

אם נוסיף את שם המוצר למקט בהנחה ויש התאמה מלאה בין שם המקט לבין שם המוצר נקבל את שניהם בצורה כזו:

לאחר עיצוב מחדש של הטבלה תחת טאב עיצוב טבלת ציר -> עימוד -> הצגה טבלאית



אפשר להוסיף פעם נוספת את הכמויות ולקבל גם מומצע וגם סיכום


אם בטבלת הנתונים המקורית נערכים שינויים, כל שיש לעשות הוא לרענן את הטבלת ציר (כפתור ימני על הטבלה – רענן)

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

בקיצור, עולם חדש נפרש לפניכם….

חיקרו אותו ללא מורא.

דרך צלחה.


עדיין אין תגובות

יול' 29 2013

קובץ מחולל תרגילי חשבון – חיבור/ חיסור/ כפל – משוואה/ נעלם ראשון/ נעלם שני

מאת: שלומי מסורי נושאים: Excel

לבקשת הקהל, אני מצרף את קובץ האקסל עם הקוד והכפתור להנאתכם, אבל, אני ממליץ לעבור את כל התהליך של הבניה – תלמדו ממנו הרבה!!!

קישור לקובץ

בהצלחה.

אם אתם עושים שימוש אנא הגיבו ותנו פידבק,

אם יש רעיונות לשיפורים / בקשות הוסיפו בהערות.

9 תגובות

מאי 08 2013

חישוב יום עסקים אחרון לחודש

מאת: שלומי מסורי נושאים: Excel

עודד שאל שאלה מעניינת,

האם יש פונקציה שיכולה להחזיר לי את יום העסקים האחרון לחודש מסויים? לדוגמה יום העסקים האחרון של חודש מרץ הוא לא ה- 31/3/2013 אלא 28/3/2013.
אני מניח שזו איזשהי הרכבה של פונקציה EOMONTH וWORKDAYS.INTL.

אז ככה,

יש פונקציה שנקראת WORKDAY – שלוקחת בחשבון ימי עבודה (לא כולל סופי שבוע) ומאפשרת לבחור תאריכי חופש/חגים, הבעיה עם הפונקציה הזו שימי סוף השבוע הם שבת וראשון.

בגירסאות אקסל 2010 ומעלה נוספה פונקציה חדשה, WORKDAY.INTL שהיא מאפשרת לבחור את ימי סוף השבוע. (שישי ושבת – אופציה 7).

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

לכן יש פונקציה תואמת נוספת שנקראת .NETWORKDAYS.INTL – שיודעת לחשב כמה ימי עבודה יש בין זוג תאריכים. (עם אותה החוקיות והפונקציונאליות של הפונקציה הקודמת) (וגם רק ב2010)

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

ועוד נקודה, יש להכין רשימת תאריכים של ימי החופש (אפשר להזין לבד, או ליצא רשימת חגים מOUTLOOK לאקסל ולעבד אותה משם, לשם הנוחות צרפתי טבלה עם התאריכים מ2008-2027 –ההסתמכות על נתונים אלו היא באחריותכם בלבד!!!) ולהכניס אותה לתוך טווח תאים (רק את התאריכים) ולשם הנוחות להגדיר לו שם, למשל Holydays.(ניתן להזכר כיצד מגדירים שם לטווח תאים בפוסט הזה)

אם בעמודה A נזין את תאריכי תחילת כל חודש, בעמודה B נשבץ את הנוסחה הבאה:

ה IF בירוק הוא תיקון לבאג שצינתי קודם

=WORKDAY.INTL(A1,IF(WEEKDAY(A1,1)>5,NETWORKDAYS.INTL(A1,EOMONTH(A1,0),7,Holydays),NETWORKDAYS.INTL(A1,EOMONTH(A1,0),7,Holydays)-1),7,Holydays(

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

בהצלחה.

Subject Location
Rosh Hashanah (1st day) Jewish Religious Holidays 30/09/2008
Rosh Hashanah (2nd day) Jewish Religious Holidays 01/10/2008
Yom Kippur Jewish Religious Holidays 09/10/2008
Sukkot (Israel) Jewish Religious Holidays 14/10/2008
Shmini Atzeret – Simchat Torah (Israel) Jewish Religious Holidays 21/10/2008
Pesach Jewish Religious Holidays 09/04/2009
Shvii of Pesach Jewish Religious Holidays 15/04/2009
Yom Haatzmaut Israel 29/04/2009
Shavuot Jewish Religious Holidays 29/05/2009
Rosh Hashanah (1st day) Jewish Religious Holidays 19/09/2009
Rosh Hashanah (2nd day) Jewish Religious Holidays 20/09/2009
Yom Kippur Jewish Religious Holidays 28/09/2009
Sukkot (Israel) Jewish Religious Holidays 03/10/2009
Shmini Atzeret – Simchat Torah (Israel) Jewish Religious Holidays 10/10/2009
Pesach Jewish Religious Holidays 30/03/2010
Shvii of Pesach Jewish Religious Holidays 05/04/2010
Yom Haatzmaut Israel 20/04/2010
Shavuot Jewish Religious Holidays 19/05/2010
Rosh Hashanah (1st day) Jewish Religious Holidays 09/09/2010
Rosh Hashanah (2nd day) Jewish Religious Holidays 10/09/2010
Yom Kippur Jewish Religious Holidays 18/09/2010
Sukkot (Israel) Jewish Religious Holidays 23/09/2010
Shmini Atzeret – Simchat Torah (Israel) Jewish Religious Holidays 30/09/2010
Pesach Jewish Religious Holidays 19/04/2011
Shvii of Pesach Jewish Religious Holidays 25/04/2011
Yom Haatzmaut Israel 10/05/2011
Shavuot Jewish Religious Holidays 08/06/2011
Rosh Hashanah (1st day) Jewish Religious Holidays 29/09/2011
Rosh Hashanah (2nd day) Jewish Religious Holidays 30/09/2011
Yom Kippur Jewish Religious Holidays 08/10/2011
Sukkot (Israel) Jewish Religious Holidays 13/10/2011
Shmini Atzeret – Simchat Torah (Israel) Jewish Religious Holidays 20/10/2011
Pesach Jewish Religious Holidays 07/04/2012
Shvii of Pesach Jewish Religious Holidays 13/04/2012
Yom Haatzmaut Israel 26/04/2012
Shavuot Jewish Religious Holidays 27/05/2012
Rosh Hashanah (1st day) Jewish Religious Holidays 17/09/2012
Rosh Hashanah (2nd day) Jewish Religious Holidays 18/09/2012
Yom Kippur Jewish Religious Holidays 26/09/2012
Sukkot (Israel) Jewish Religious Holidays 01/10/2012
Shmini Atzeret – Simchat Torah (Israel) Jewish Religious Holidays 08/10/2012
Pesach Jewish Religious Holidays 26/03/2013
Shvii of Pesach Jewish Religious Holidays 01/04/2013
Yom Haatzmaut Israel 16/04/2013
Shavuot Jewish Religious Holidays 15/05/2013
Rosh Hashanah (1st day) Jewish Religious Holidays 05/09/2013
Rosh Hashanah (2nd day) Jewish Religious Holidays 06/09/2013
Yom Kippur Jewish Religious Holidays 14/09/2013
Sukkot (Israel) Jewish Religious Holidays 19/09/2013
Shmini Atzeret – Simchat Torah (Israel) Jewish Religious Holidays 26/09/2013
Pesach Jewish Religious Holidays 15/04/2014
Shvii of Pesach Jewish Religious Holidays 21/04/2014
Yom Haatzmaut Israel 06/05/2014
Shavuot Jewish Religious Holidays 04/06/2014
Rosh Hashanah (1st day) Jewish Religious Holidays 25/09/2014
Rosh Hashanah (2nd day) Jewish Religious Holidays 26/09/2014
Yom Kippur Jewish Religious Holidays 04/10/2014
Sukkot (Israel) Jewish Religious Holidays 09/10/2014
Shmini Atzeret – Simchat Torah (Israel) Jewish Religious Holidays 16/10/2014
Pesach Jewish Religious Holidays 04/04/2015
Shvii of Pesach Jewish Religious Holidays 10/04/2015
Yom Haatzmaut Israel 23/04/2015
Shavuot Jewish Religious Holidays 24/05/2015
Rosh Hashanah (1st day) Jewish Religious Holidays 14/09/2015
Rosh Hashanah (2nd day) Jewish Religious Holidays 15/09/2015
Yom Kippur Jewish Religious Holidays 23/09/2015
Sukkot (Israel) Jewish Religious Holidays 28/09/2015
Shmini Atzeret – Simchat Torah (Israel) Jewish Religious Holidays 05/10/2015
Pesach Jewish Religious Holidays 23/04/2016
Shvii of Pesach Jewish Religious Holidays 29/04/2016
Yom Haatzmaut Israel 12/05/2016
Shavuot Jewish Religious Holidays 12/06/2016
Rosh Hashanah (1st day) Jewish Religious Holidays 03/10/2016
Rosh Hashanah (2nd day) Jewish Religious Holidays 04/10/2016
Yom Kippur Jewish Religious Holidays 12/10/2016
Sukkot (Israel) Jewish Religious Holidays 17/10/2016
Shmini Atzeret – Simchat Torah (Israel) Jewish Religious Holidays 24/10/2016
Pesach Jewish Religious Holidays 11/04/2017
Shvii of Pesach Jewish Religious Holidays 17/04/2017
Yom Haatzmaut Israel 02/05/2017
Shavuot Jewish Religious Holidays 31/05/2017
Rosh Hashanah (1st day) Jewish Religious Holidays 21/09/2017
Rosh Hashanah (2nd day) Jewish Religious Holidays 22/09/2017
Yom Kippur Jewish Religious Holidays 30/09/2017
Sukkot (Israel) Jewish Religious Holidays 05/10/2017
Shmini Atzeret – Simchat Torah (Israel) Jewish Religious Holidays 12/10/2017
Pesach Jewish Religious Holidays 31/03/2018
Shvii of Pesach Jewish Religious Holidays 06/04/2018
Yom Haatzmaut Israel 19/04/2018
Shavuot Jewish Religious Holidays 20/05/2018
Rosh Hashanah (1st day) Jewish Religious Holidays 10/09/2018
Rosh Hashanah (2nd day) Jewish Religious Holidays 11/09/2018
Yom Kippur Jewish Religious Holidays 19/09/2018
Sukkot (Israel) Jewish Religious Holidays 24/09/2018
Shmini Atzeret – Simchat Torah (Israel) Jewish Religious Holidays 01/10/2018
Pesach Jewish Religious Holidays 20/04/2019
Shvii of Pesach Jewish Religious Holidays 26/04/2019
Yom Haatzmaut Israel 09/05/2019
Shavuot Jewish Religious Holidays 09/06/2019
Rosh Hashanah (1st day) Jewish Religious Holidays 30/09/2019
Rosh Hashanah (2nd day) Jewish Religious Holidays 01/10/2019
Yom Kippur Jewish Religious Holidays 09/10/2019
Sukkot (Israel) Jewish Religious Holidays 14/10/2019
Shmini Atzeret – Simchat Torah (Israel) Jewish Religious Holidays 21/10/2019
Pesach Jewish Religious Holidays 09/04/2020
Shvii of Pesach Jewish Religious Holidays 15/04/2020
Yom Haatzmaut Israel 29/04/2020
Shavuot Jewish Religious Holidays 29/05/2020
Rosh Hashanah (1st day) Jewish Religious Holidays 19/09/2020
Rosh Hashanah (2nd day) Jewish Religious Holidays 20/09/2020
Yom Kippur Jewish Religious Holidays 28/09/2020
Sukkot (Israel) Jewish Religious Holidays 03/10/2020
Shmini Atzeret – Simchat Torah (Israel) Jewish Religious Holidays 10/10/2020
Rosh Hashanah (1st day) Jewish Religious Holidays 07/09/2021
Rosh Hashanah (2nd day) Jewish Religious Holidays 08/09/2021
Yom Kippur Jewish Religious Holidays 16/09/2021
Sukkot (Israel) Jewish Religious Holidays 21/09/2021
Shmini Atzeret – Simchat Torah (Israel) Jewish Religious Holidays 28/09/2021
Rosh Hashanah (1st day) Jewish Religious Holidays 26/09/2022
Rosh Hashanah (2nd day) Jewish Religious Holidays 27/09/2022
Yom Kippur Jewish Religious Holidays 05/10/2022
Sukkot (Israel) Jewish Religious Holidays 10/10/2022
Shmini Atzeret – Simchat Torah (Israel) Jewish Religious Holidays 17/10/2022
Rosh Hashanah (1st day) Jewish Religious Holidays 16/09/2023
Rosh Hashanah (2nd day) Jewish Religious Holidays 17/09/2023
Yom Kippur Jewish Religious Holidays 25/09/2023
Sukkot (Israel) Jewish Religious Holidays 30/09/2023
Shmini Atzeret – Simchat Torah (Israel) Jewish Religious Holidays 07/10/2023
Rosh Hashanah (1st day) Jewish Religious Holidays 03/10/2024
Rosh Hashanah (2nd day) Jewish Religious Holidays 04/10/2024
Yom Kippur Jewish Religious Holidays 12/10/2024
Sukkot (Israel) Jewish Religious Holidays 17/10/2024
Shmini Atzeret – Simchat Torah (Israel) Jewish Religious Holidays 24/10/2024
Rosh Hashanah (1st day) Jewish Religious Holidays 23/09/2025
Rosh Hashanah (2nd day) Jewish Religious Holidays 24/09/2025
Yom Kippur Jewish Religious Holidays 02/10/2025
Sukkot (Israel) Jewish Religious Holidays 07/10/2025
Shmini Atzeret – Simchat Torah (Israel) Jewish Religious Holidays 14/10/2025
Rosh Hashanah (1st day) Jewish Religious Holidays 12/09/2026
Rosh Hashanah (2nd day) Jewish Religious Holidays 13/09/2026
Yom Kippur Jewish Religious Holidays 21/09/2026
Sukkot (Israel) Jewish Religious Holidays 26/09/2026
Shmini Atzeret – Simchat Torah (Israel) Jewish Religious Holidays 03/10/2026
Rosh Hashanah (1st day) Jewish Religious Holidays 02/10/2027
Rosh Hashanah (2nd day) Jewish Religious Holidays 03/10/2027
Yom Kippur Jewish Religious Holidays 11/10/2027
Sukkot (Israel) Jewish Religious Holidays 16/10/2027
Shmini Atzeret – Simchat Torah (Israel) Jewish Religious Holidays 23/10/2027

2 תגובות

מאי 06 2013

בניית מחולל תרגילים – בניית טופס שלב 7 – ואחרון – הפעלת הטופס

מאת: שלומי מסורי נושאים: Excel

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

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

כמובן, שגם כאן יש מספר אפשרויות ואני אדגים 2 מתוכם.

  1. הוספת כפתור קיצור דרך ב"סרגל גישה מהירה"
  2. כפתור / אוביקט בגליון אקסל פשוט.
  3. יש אופציה נוספת של הוספת כפתור בסרגל כלים אישי או קיים אבל זה בהזדמנות אחרת.

קודם נבנה פרוצדורה שתקרא לטופס:

נוסיף מודל חדש ונכניס את הקוד הבא פנימה:

Sub RunForm()

frmMain.Show

End Sub


המשמעות של הקוד היא די ברורה – הצג את הטופס.

לפרוצדורה הזו נקרא כשנרצה להפעיל את הטופס.

נתחיל דווקא באפשרות השניה שהיא מאוד פשוטה.

  1. בחוברת העבודה ששמרנו את הקוד שלנו, נוסיף אוביקט צורה.
  2. לחציה ימנית עליו ונבחר: שייך מאקרו.
  3. ונבחר מתוך הרשימה את הפרוצדורה שיצרנו קודם. RunForm


  1. ונסיים באישור על OK

לחיצה על ה"כפתור" שייצרנו תפעיל את בטופס…

נחזור לאופציה הראשונה.

  1. לחיצה על החץ הקטן בסוף סרגל הגישה המהירה תפתח מספר אופציות, נבחר באפשרויות נוספות.


  1. יפתח לנו תיבת דו שיח: התאמה אישית


  1. שם נבחר ברשימה הכללית שמצד שמאל מאקרו.


  1. הפרוצדורה שבנינו מופיע שם, נבחר אותה ונלחץ על הכפתור "הוספה" שבין שתי הרשימות.


  1. ניתן להתאים את מראה הכפתור ושמו כרצוננו, ולאשר (פעמים).
  2. והנה לנו כפתור מהיר נוסף.


לבקשת הקהל, אני מצרף את קובץ האקסל עם הקוד והכפתור להנאתכם, אבל, אני ממליץ לעבור את כל התהליך של הבניה – תלמדו ממנו הרבה!!!

קישור לקובץ

בהצלחה.

עדיין אין תגובות

אפר' 28 2013

איסוף נתונים – עוד שיטות עבודה בOFFICE

מאת: שלומי מסורי נושאים: Excel

שלום חברים,

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

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

האחת שימוש בכלי של OFFICE שנקרא InfoPath, שהוא למעשה מחולל טפסים עם יכולות מאוד מתקדמות כולל שאלות מותנות בתשובות לשאלות מקדימות, ועוד.

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

דרך נוספת היא שילוב של Access וOutlook שניתן לראות מימוש בסירטון הבא: Data Collection with Outlook

2 תגובות

דצמ' 09 2012

בניית מחולל תרגילים – בניית טופס שלב 6 – קוד המחולל

מאת: שלומי מסורי נושאים: Excel

אז הגענו סוף סוף לשלב של המחולל עצמו.

המחולל בנוי כפונקציה שמחזירה ערך בוליאני כלומר TRUE/FALSE, בהתאם לסטאטוס ההצלחה של הפקת דף התרגילים, הרעיון שעומד מאחורי צורת ישום זאת נועד לקבל החלטה האם לסגור את הטופס (במידה ובדף תרגילים הצליח,) או לא (יש בעיה כלשהי בנתונים שבטופס).

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

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

הרעיון של הפונקציה פשוט לעבור על הקומבינציות האפשריות של משתנה 1 ו2 ולאתר האם יש תשובות שנמצאות בטווח התוצאות שהגדרנו.

המבנה הוא כך:

עבור על כל ערך ממשתנה 1 מהערך הנמוך עד לערך הגבוה,

עבור כל ערך זה עבור על כל ערך ממשתנה 2 מהערך הנמוך עד לערך הגבוה,

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

אם הערך תקין, החזרת ערך TRUE וצא מהפונקציה,

אם לא, עבור לערך הבא.

עבור לערך הבא,

אם לא נמצא כל ערך החזר ערך FALSE וסיים הפונקציה.

ובקוד זה יראה כך:

'בדיקת סבירות ערכים

Private Function CheckSanityValue(FirstValueMin As Integer, FirstValueMax As Integer, SecValueMin As Integer, SecValueMax As Integer, ResValueMin As Integer, ResValueMax As Integer, TestType As String) As Boolean

Dim i As Integer, s As Integer

For i = FirstValueMin To FirstValueMax

For s = SecValueMin To SecValueMax

If Evaluate(i & TestType & s) >= ResValueMin And Evaluate(i & TestType & s) <= ResValueMax Then

CheckSanityValue = True

Exit Function

End If

Next

Next

CheckSanityValue = False

End Function


נעשה שימוש בפונקציה: Evaluate שהיא משערכת את תוכן הטקסט שמוזן לערך מחושב מתמטית,

המשתנה TestType מתקבל מתיבת הבחירה סוג תרגיל. (+/-/*)

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

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

לפני שאציג את הקוד נסביר את סדר הפעולות:

  1. ישור הגליון לשמאל. – במידה ומוגדרת ברירת מחדל עם יישור לימין.
  2. כותרת לגליון. – מיזוג תאים ויישור ועיצוב של טקסט הכותרת.
  3. טיפול באזור ההוראות. – מיזוג תאים ויישור ועיצוב של טקסט ההוראות.
  4. טיפול באזור הציון. – במידה וצויין בטופס שיש להציג ציון נבנה נוסחה לחישוב הציון
  5. בניית התרגילים. – לולאה שתרוץ מספר פעמים ותקרא לפונקציה שתחולל ערכים אקראיים (GenValue) ותבנה תרגיל תקין חדש ותזין אותו ואת התשובה הנכונה בשורה חדשה בעזרת פונקצית עזר (InsertExercise).
  6. הסתרת עמודות העזר. – בכדי להסתיר את עמודת העזר שמכילות את התשובה הנכונה
  7. עיצוב הגיליון כולו. – הסתרת קוי הרשת של הגיליון, כותרות העמודות והשורות וכו’
  8. נעילת הגליון לשינויים עם סיסמה. – כדי למנוע חשיפה אקראית או מכוונת של תשובות התרגילים.

'פרוצדורת בניית גליון תרגול

Private Function BulidTest() As Boolean

Dim ExerciseNo As Integer

Dim FV As Integer, SV As Integer, RS As Integer

With frmMain

'בדיקת סבירות ערכים

If Not CheckSanityValue(Int(.txtMin1), Int(.txtMax1), Int(.txtMin2), Int(.txtMax2), Int(.txtMin3), Int(.txtMax3), .cbxType.Column(1)) Then

MsgBox "לא ניתן לחולל תרגילים על בסיס הנתונים שניתנו", vbCritical + vbMsgBoxRight + vbMsgBoxRtlReading

BulidTest = False

Exit Function

End If

End With

'הוסף חוברת עבודה חדשה

Workbooks.Add

Application.ScreenUpdating = False

'ישור הגליון לשמאל

ActiveSheet.DisplayRightToLeft = False

'כותרת לגליון

Range("A1:V1").Merge

With Range("A1:V1")

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.Name = "Arial"

With .Font

.Size = 22

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ColorIndex = xlAutomatic

End With

.Value = "תרגילים בחשבון"

End With

'אזור ההוראות

Range("G2:V3").Merge

With Range("G2:V3")

.HorizontalAlignment = xlRight

.VerticalAlignment = xlTop

.WrapText = True

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = True

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

End With

'אזור הציון

If Me.chbShowScore Then

With Range("A2:B3")

.Merge

.FormulaR1C1 = "=IF(COUNTIF(C[9]:C[20],""=X"")+COUNTIF(C[9]:C[20],""=V"")=" & Me.txtExercisesCount & ",COUNTIF(C[9]:C[20],""=V"")/" & Me.txtExercisesCount & ","""")"

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.Font.Size = 14

.Font.ColorIndex = 5

.Style = "Percent"

End With

End If

'בניית התרגילים

ExerciseNo = 0

Do While ExerciseNo < Me.txtExercisesCount

FV = GenValue(Int(Me.txtMin1), Int(Me.txtMax1))

SV = GenValue(Int(Me.txtMin2), Int(Me.txtMax2))

RS = Evaluate(FV & Me.cbxType.Column(1) & SV)

If RS >= CInt(Me.txtMin3) And RS <= CInt(Me.txtMax3) Then

'הדפסת תרגיל

InsertExercise FV, SV, RS, Me.cbxType.Column(1), Me.cbxStyle.Column(1), ExerciseNo

'החסרת 1 מכלל התרגילים

ExerciseNo = ExerciseNo + 1

End If

Loop

ActiveSheet.Cells.EntireColumn.AutoFit

'הסתרת עמודות

Columns("C:C").EntireColumn.Hidden = True

Columns("f:f").EntireColumn.Hidden = True

Columns("i:i").EntireColumn.Hidden = True

Columns("k:k").EntireColumn.Hidden = True

Columns("N:N").EntireColumn.Hidden = True

Columns("Q:Q").EntireColumn.Hidden = True

Columns("T:T").EntireColumn.Hidden = True

With ActiveWindow

.DisplayGridlines = False

.DisplayHeadings = False

.DisplayOutline = False

.DisplayWorkbookTabs = False

End With

ActiveSheet.DisplayAutomaticPageBreaks = False

' נעילת הגליון לשינויים עם סיסמה

ActiveSheet.Protect Password:="liat", DrawingObjects:=True, Contents:=True, Scenarios:=True

ActiveSheet.EnableSelection = xlUnlockedCells

Application.ScreenUpdating = True

BulidTest = True

End Function


נעשה שימוש רב בפונצריה RANGE שמשמעה פניה לטווח תא/ים לפי שמם (A1 לתא ספציפי או A1:b4 לטווח תאים)

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

הפונקציה הראשונה מיועדת לחילול מספר אקראי:

'מחולל ערכים על בסיס ערך עליון ותחתון

Private Function GenValue(MinVal As Integer, MaxVal As Integer) As Integer

Randomize

GenValue = Round(Rnd() * (MaxVal - MinVal), 0) + MinVal

End Function

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

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

פונקצית Round – נועדה לעגל את המספר לערך שלם.

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

גם פונקציה זו מורכבת ממספר שלבים:

  1. חישוב מיקום התרגיל בדף (יש שתי עמודות תרגלים)
  2. שיבוץ ערכים בתאים הנסתרים. – לצורך חישוב התוצאה הנכונה לשלב 4
  3. עיצוב התאים לפי סגנון התרגיל. – משתנה ראשון יצריך תא ראשון ריק וכו’
  4. בניית מנגנון בדיקת התשובה לתרגיל. – נוסחה לבדיקת התוצאה של התרגיל לעומת התשובה שתוזן לתא הרלוונטי.

Private Sub InsertExercise(FV As Integer, SV As Integer, RS As Integer, Sign As String, TestType As Integer, ExNo As Integer)
Dim ExLine As Integer
Dim ColN As Integer
'מספר שורה לשיבוץ
ExLine = 4 + ExNo
'עמודה שניה במספר תרגיל זוגי
If ExNo Mod 2 = 0 Then
ColN = 0
Else
ColN = 11
ExLine = ExLine - 1
End If
'שיבוץ ערכים בתאים הנסתרים
ActiveSheet.Cells(ExLine, 3 + ColN).Value = FV
ActiveSheet.Cells(ExLine, 6 + ColN).Value = SV
ActiveSheet.Cells(ExLine, 9 + ColN).Value = RS
'סימן
If Sign = "*" Then Sign = "x"
ActiveSheet.Cells(ExLine, 4 + ColN).Value = Sign
'סימן =
ActiveSheet.Cells(ExLine, 7 + ColN).Value = "="
' עיצוב התאים לפי סגנון התרגיל
Select Case TestType
Case 1 'נעלם ראשון
ActiveSheet.Cells(ExLine, 5 + ColN).Value = SV
ActiveSheet.Cells(ExLine, 8 + ColN).Value = RS
With ActiveSheet.Cells(ExLine, 2 + ColN)
.Locked = False
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
.Borders(xlEdgeBottom).Weight = xlThin
With .Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="0", Formula2:="999"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End With
Case 2 'נעלם שני
ActiveSheet.Cells(ExLine, 2 + ColN).Value = FV
ActiveSheet.Cells(ExLine, 8 + ColN).Value = RS
With ActiveSheet.Cells(ExLine, 5 + ColN)
.Locked = False
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
.Borders(xlEdgeBottom).Weight = xlThin
With .Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="0", Formula2:="999"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End With
Case 3 'משוואה
ActiveSheet.Cells(ExLine, 5 + ColN).Value = SV
ActiveSheet.Cells(ExLine, 2 + ColN).Value = FV
With ActiveSheet.Cells(ExLine, 8 + ColN)
.Locked = False
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
.Borders(xlEdgeBottom).Weight = xlThin
With .Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="0", Formula2:="999"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End With
End Select
'בניית מנגנון בדיקת התשובה לתרגיל
With ActiveSheet.Cells(ExLine, 10 + ColN)
.FormulaR1C1 = "=IF(AND(RC[-8]<>"""",RC[-5]<>"""",RC[-2]<>""""),IF(AND(RC[-8]=RC[-7],RC[-5]=RC[-4],RC[-2]=RC[-1]),""V"",""X""),"""")"
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""V"""
.FormatConditions(1).Interior.ColorIndex = 4
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""X"""
.FormatConditions(2).Interior.ColorIndex = 3
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With
Range(ExLine & ":" & ExLine).HorizontalAlignment = xlCenter
Range(ExLine & ":" & ExLine).VerticalAlignment = xlBottom
End Sub

עשינו שימוש בכמה פונקציות:

Mod – בדיקת שארית – במקרה שלנו נועד לברר האם המספר זוגי או או לא.

Select Case TestType

Case 1

..

End Select

פסקת תנאי החלטה – לפי הערך בTestType

ActiveSheet.Cells(x, y).Value – פניה לתא בצורה ישירה ע"י ערכים מספרים (X,Y) כיוון שמספר העמודה והשורה דינמי ביחס לפונקציה הקודמת.

With - End With – מסגור של משתנים השיכים לאוביקט ספציפי ופניה אליהם ללא צורך לשרשר את אוביקט האב כל הזמן מחדש. (מתחילים את השורה בסימן '.')

Borders – גבולות כל מה שקשור לעיצוב גבולות התא/ים

Validation- אימות נתונים

FormatConditions – עיצוב מותנה

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

טוב זהו בנתיים, אני מצרף את הקוד המלא של הטופס:

Private Sub UserForm_Initialize()

'איתחול נתונים

'ערכים לשדה סוג תרגיל

Dim SignArr(1, 2) 'הגדרת משתנה מסוג מערך דו ממדי

SignArr(0, 0) = "סכום (+)"

SignArr(1, 0) = "+"

SignArr(0, 1) = "הפרש (-)"

SignArr(1, 1) = "-"

SignArr(0, 2) = "מכפלה (x)"

SignArr(1, 2) = "*"

cbxType.Column = SignArr 'הכנסת המערך כערך להגדרת הפקד

'ערכים לשדה סגנון תרגיל

Dim TypeArr(1, 2) 'הגדרת משתנה מסוג מערך דו ממדי

TypeArr(0, 0) = "משוואה ( ___ = X + Y )"

TypeArr(1, 0) = "3"

TypeArr(0, 1) = "נעלם ראשון (Y = Z + ___ )"

TypeArr(1, 1) = "1"

TypeArr(0, 2) = "נעלם שני ( X + ___ = Z )"

TypeArr(1, 2) = "2"

cbxStyle.Column = TypeArr 'הכנסת המערך כערך להגדרת הפקד

'ערכי ברירת מחדל

'סיכום

cbxType.ListIndex = 0

'משוואה

cbxStyle.ListIndex = 0

'ערכי מינימום ומקסימום למשתנים

txtMax1.Value = 10

txtMin1.Value = 0

txtMax2.Value = 10

txtMin2.Value = 0

txtMax3.Value = 10

txtMin3.Value = 0

txtExercisesCount.Value = 10

chbShowScore.Value = True

End Sub

Private Sub cbxType_Change()

Select Case cbxType.Value

Case "+"

lblSign.Caption = "+"

Case "-"

lblSign.Caption = "-"

Case "*"

lblSign.Caption = "X"

Case Else

lblSign.Caption = "?"

End Select

End Sub

Private Sub cmdRun_Click()

'build sheet

If BulidTest Then Unload Me

End Sub

Private Sub txtExercisesCount_Change()

OnlyNumbers

End Sub

Private Sub txtMax1_Change()

OnlyNumbers

End Sub

Private Sub txtMax1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Cancel = ValidateGreaterThen(txtMin1)

End Sub

Private Sub txtMax2_Change()

OnlyNumbers

End Sub

Private Sub txtMax2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Cancel = ValidateGreaterThen(txtMin2)

End Sub

Private Sub txtMax3_Change()

OnlyNumbers

End Sub

Private Sub txtMax3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Cancel = ValidateGreaterThen(txtMin3)

End Sub

Private Sub txtMin1_Change()

OnlyNumbers

End Sub

Private Sub txtMin1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Cancel = ValidateSmallerThen(txtMax1)

End Sub

Private Sub txtMin2_Change()

OnlyNumbers

End Sub

Private Sub txtMin2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Cancel = ValidateSmallerThen(txtMax2)

End Sub

Private Sub txtMin3_Change()

OnlyNumbers

End Sub

Private Sub txtMin3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Cancel = ValidateSmallerThen(txtMax3)

End Sub

Private Sub OnlyNumbers()

If TypeName(Me.ActiveControl) = "TextBox" Then

With Me.ActiveControl

If Not IsNumeric(.Value) And .Value <> vbNullString Then

MsgBox "נא להזין ספרות בלבד"

.Value = vbNullString

End If

End With

End If

End Sub

Private Function ValidateGreaterThen(txtBox As Control) As Boolean

ValidateGreaterThen = False

If TypeName(Me.ActiveControl) = "TextBox" And TypeName(txtBox) = "TextBox" Then

If Me.ActiveControl.Value <> vbNullString And txtBox.Value <> vbNullString Then

If CInt(Me.ActiveControl.Value) < CInt(txtBox.Value) Then

MsgBox "הערך קטן מהמותר"

ValidateGreaterThen = True

End If

End If

End If

End Function

Private Function ValidateSmallerThen(txtBox As Control) As Boolean

ValidateSmallerThen = False

If TypeName(Me.ActiveControl) = "TextBox" And TypeName(txtBox) = "TextBox" Then

If Me.ActiveControl.Value <> vbNullString And txtBox.Value <> vbNullString Then

If CInt(Me.ActiveControl.Value) > CInt(txtBox.Value) Then

MsgBox "הערך גדול מהמותר"

ValidateSmallerThen = True

End If

End If

End If

End Function

'בדיקת סבירות ערכים

Private Function CheckSanityValue(FirstValueMin As Integer, FirstValueMax As Integer, SecValueMin As Integer, SecValueMax As Integer, ResValueMin As Integer, ResValueMax As Integer, TestType As String) As Boolean

Dim i As Integer, s As Integer

For i = FirstValueMin To FirstValueMax

For s = SecValueMin To SecValueMax

If Evaluate(i & TestType & s) >= ResValueMin And Evaluate(i & TestType & s) <= ResValueMax Then

CheckSanityValue = True

Exit Function

End If

Next

Next

CheckSanityValue = False

End Function

'מחולל ערכים על בסיס ערך עליון ותחתון

Private Function GenValue(MinVal As Integer, MaxVal As Integer) As Integer

Randomize

GenValue = Round(Rnd() * (MaxVal - MinVal), 0) + MinVal

End Function

'פרוצדורת בניית גליון תרגול

Private Function BulidTest() As Boolean

Dim ExerciseNo As Integer

Dim FV As Integer, SV As Integer, RS As Integer

With frmMain

'בדיקת סבירות ערכים

If Not CheckSanityValue(Int(.txtMin1), Int(.txtMax1), Int(.txtMin2), Int(.txtMax2), Int(.txtMin3), Int(.txtMax3), .cbxType.Column(1)) Then

MsgBox "לא ניתן לחולל תרגילים על בסיס הנתונים שניתנו", vbCritical + vbMsgBoxRight + vbMsgBoxRtlReading

BulidTest = False

Exit Function

End If

End With

'הוסף חוברת עבודה חדשה

Workbooks.Add

Application.ScreenUpdating = False

'ישור הגליון לשמאל

ActiveSheet.DisplayRightToLeft = False

'כותרת לגליון

Range("A1:V1").Merge

With Range("A1:V1")

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.Name = "Arial"

With .Font

.Size = 22

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ColorIndex = xlAutomatic

End With

.Value = "תרגילים בחשבון"

End With

'אזור ההוראות

Range("G2:V3").Merge

With Range("G2:V3")

.HorizontalAlignment = xlRight

.VerticalAlignment = xlTop

.WrapText = True

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = True

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

End With

'אזור הציון

If Me.chbShowScore Then

With Range("A2:B3")

.Merge

.FormulaR1C1 = "=IF(COUNTIF(C[9]:C[20],""=X"")+COUNTIF(C[9]:C[20],""=V"")=" & Me.txtExercisesCount & ",COUNTIF(C[9]:C[20],""=V"")/" & Me.txtExercisesCount & ","""")"

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.Font.Size = 14

.Font.ColorIndex = 5

.Style = "Percent"

End With

End If

'בניית התרגילים

ExerciseNo = 0

Do While ExerciseNo < Me.txtExercisesCount

FV = GenValue(Int(Me.txtMin1), Int(Me.txtMax1))

SV = GenValue(Int(Me.txtMin2), Int(Me.txtMax2))

RS = Evaluate(FV & Me.cbxType.Column(1) & SV)

If RS >= CInt(Me.txtMin3) And RS <= CInt(Me.txtMax3) Then

'הדפסת תרגיל

InsertExercise FV, SV, RS, Me.cbxType.Column(1), Me.cbxStyle.Column(1), ExerciseNo

'החסרת 1 מכלל התרגילים

ExerciseNo = ExerciseNo + 1

End If

Loop

ActiveSheet.Cells.EntireColumn.AutoFit

'הסתרת עמודות

Columns("C:C").EntireColumn.Hidden = True

Columns("f:f").EntireColumn.Hidden = True

Columns("i:i").EntireColumn.Hidden = True

Columns("k:k").EntireColumn.Hidden = True

Columns("N:N").EntireColumn.Hidden = True

Columns("Q:Q").EntireColumn.Hidden = True

Columns("T:T").EntireColumn.Hidden = True

With ActiveWindow

.DisplayGridlines = False

.DisplayHeadings = False

.DisplayOutline = False

.DisplayWorkbookTabs = False

End With

ActiveSheet.DisplayAutomaticPageBreaks = False

' נעילת הגליון לשינויים עם סיסמה

ActiveSheet.Protect Password:="liat", DrawingObjects:=True, Contents:=True, Scenarios:=True

ActiveSheet.EnableSelection = xlUnlockedCells

Application.ScreenUpdating = True

BulidTest = True

End Function

Private Sub InsertExercise(FV As Integer, SV As Integer, RS As Integer, Sign As String, TestType As Integer, ExNo As Integer)

Dim ExLine As Integer

Dim ColN As Integer

'מספר שורה לשיבוץ

ExLine = 4 + ExNo

'עמודה שניה במספר תרגיל זוגי

If ExNo Mod 2 = 0 Then

ColN = 0

Else

ColN = 11

ExLine = ExLine - 1

End If

'שיבוץ ערכים בתאים הנסתרים

ActiveSheet.Cells(ExLine, 3 + ColN).Value = FV

ActiveSheet.Cells(ExLine, 6 + ColN).Value = SV

ActiveSheet.Cells(ExLine, 9 + ColN).Value = RS

'סימן

If Sign = "*" Then Sign = "x"

ActiveSheet.Cells(ExLine, 4 + ColN).Value = Sign

'סימן =

ActiveSheet.Cells(ExLine, 7 + ColN).Value = "="

' עיצוב התאים לפי סגנון התרגיל

Select Case TestType

Case 1 'נעלם ראשון

ActiveSheet.Cells(ExLine, 5 + ColN).Value = SV

ActiveSheet.Cells(ExLine, 8 + ColN).Value = RS

With ActiveSheet.Cells(ExLine, 2 + ColN)

.Locked = False

.Borders(xlEdgeBottom).LineStyle = xlContinuous

.Borders(xlEdgeBottom).ColorIndex = xlAutomatic

.Borders(xlEdgeBottom).Weight = xlThin

With .Validation

.Delete

.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _

Operator:=xlBetween, Formula1:="0", Formula2:="999"

.IgnoreBlank = True

.InCellDropdown = True

.ShowInput = True

.ShowError = True

End With

End With

Case 2 'נעלם שני

ActiveSheet.Cells(ExLine, 2 + ColN).Value = FV

ActiveSheet.Cells(ExLine, 8 + ColN).Value = RS

With ActiveSheet.Cells(ExLine, 5 + ColN)

.Locked = False

.Borders(xlEdgeBottom).LineStyle = xlContinuous

.Borders(xlEdgeBottom).ColorIndex = xlAutomatic

.Borders(xlEdgeBottom).Weight = xlThin

With .Validation

.Delete

.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _

Operator:=xlBetween, Formula1:="0", Formula2:="999"

.IgnoreBlank = True

.InCellDropdown = True

.ShowInput = True

.ShowError = True

End With

End With

Case 3 'משוואה

ActiveSheet.Cells(ExLine, 5 + ColN).Value = SV

ActiveSheet.Cells(ExLine, 2 + ColN).Value = FV

With ActiveSheet.Cells(ExLine, 8 + ColN)

.Locked = False

.Borders(xlEdgeBottom).LineStyle = xlContinuous

.Borders(xlEdgeBottom).ColorIndex = xlAutomatic

.Borders(xlEdgeBottom).Weight = xlThin

With .Validation

.Delete

.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _

Operator:=xlBetween, Formula1:="0", Formula2:="999"

.IgnoreBlank = True

.InCellDropdown = True

.ShowInput = True

.ShowError = True

End With

End With

End Select

'בניית מנגנון בדיקת התשובה לתרגיל

With ActiveSheet.Cells(ExLine, 10 + ColN)

.FormulaR1C1 = "=IF(AND(RC[-8]<>"""",RC[-5]<>"""",RC[-2]<>""""),IF(AND(RC[-8]=RC[-7],RC[-5]=RC[-4],RC[-2]=RC[-1]),""V"",""X""),"""")"

.FormatConditions.Delete

.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""V"""

.FormatConditions(1).Interior.ColorIndex = 4

.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""X"""

.FormatConditions(2).Interior.ColorIndex = 3

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

End With

Range(ExLine & ":" & ExLine).HorizontalAlignment = xlCenter

Range(ExLine & ":" & ExLine).VerticalAlignment = xlBottom

End Sub

2 תגובות

נוב' 21 2012

בניית מחולל תרגילים – בניית טופס שלב 5 – קוד איתחול

מאת: שלומי מסורי נושאים: Excel

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

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

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

  1. בהפעלת הטופס אנו מעוניינים לאתחל נתונים ופרמטרים של הטופס והפקדים שלנו.
  2. במשתמש יבחר את סוג התרגיל שהוא מעוניין בו, ובכך ישפיע על התצוגה של פקד אחר בטופס. (תרגיל חיבור ישנה את הסימן ל+, חיסור ל – וכו’)
  3. הזנת נתונים לתאים השונים,
  4. לחיצה על הפעלת המחולל תרגילים
  5. אימות הנתונים שהוזנו לטופס.
  6. יצירת גיליון התרגילים
  7. סגירת הטופס.

אז מה זה איתחול נתונים? למה זה חשוב?

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

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

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


בחירת סוג התרגילים(cbxType) וסגנון תרגיל(cbxStyle).

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

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

במעבר לחלון הקוד (ALT+F12)
נפתח את הטופס שלנו מעץ הפרויקט + כתור עכבר ימני על איזור ריק בטופס + View Code (או פשוט לחיצת עכבר כפולה)

נשנה את סוג האירוע מClick שהוא ברירת המחדל לInitialize – איתחול.


ושם נכניס את הקוד איתחול שלנו
(הטקסט בירוק הוא הערות הסבר):

‘איתחול נתונים

‘ערכים לשדה סוג תרגיל

Dim SignArr(1, 2) ‘הגדרת משתנה מסוג מערך דו ממדי

SignArr(0, 0) = "סכום (+)"

SignArr(1, 0) = "+"

SignArr(0, 1) = "הפרש (-)"

SignArr(1, 1) = "-"

SignArr(0, 2) = "מכפלה (x)"

SignArr(1, 2) = "*"

cbxType.Column = SignArr ‘הכנסת המערך כערך להגדרת הפקד

‘ערכים לשדה סגנון תרגיל

Dim TypeArr(1, 2) ‘הגדרת משתנה מסוג מערך דו ממדי

TypeArr(0, 0) = "משוואה ( ___ = X + Y )"

TypeArr(1, 0) = "3″

TypeArr(0, 1) = "נעלם ראשון (Y = Z + ___ )"

TypeArr(1, 1) = "1″

TypeArr(0, 2) = "נעלם שני ( X + ___ = Z )"

TypeArr(1, 2) = "2″

cbxStyle.Column = TypeArr ‘הכנסת המערך כערך להגדרת הפקד

‘ערכי ברירת מחדל

‘סיכום

cbxType.ListIndex = 0

‘משוואה

cbxStyle.ListIndex = 0

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

מערך דו ממדי הוא למעשה טבלה עם שתי עמודות ומספר שורות,

במקרה שלנו זה נראה כך:

סכום(+) +
הפרש (-) -
מכפלה (X) *

העמודה הראשונה להצגת המשמעות של הבחירה, והעמודה השניה היא הערך שהקוד יעבד בהתאם לבחירה.

בכדי להציג נכון את המידע האצור בטבלה יש להגדיר את הפרמטרים הבאים לפקדים של תיבה משולבת (ComboBox) cbxType
ו
cbxStyle

BoundColumn – מגדיר לאיזו עמודה מתוך המערך הדו ממדי אנו נתיחס בהבאת הערך מתוך הפקד.

ColumnCountt – מגדיר כמה עמודות הפקד יאכלס.

ColumnWidths – רוחב כל עמודה מופרד ב; רוחב 0 – לא מוצג.

ControlTipText – תצוגת תקציר עזרה בריחוף מעל הפקד.

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

המאפיין ListIndex מייצג את המיקום של הערך הנבחר ברשימה הנפתחת על בסיס מיספור מ0. (האופציה הראשונה 0, השניה 1 וכו’)

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

קדימה, לא לפחד לנסות….

טוב, עכשיו נעבור לשלב 2 – בחירה של סוג תרגיל תשפיע על הפקד שמציג את הסימן.

במקום ? יציג +, – , X

לחיצה כפולה על הפקד סוג התרגילים, תפתח את שיגרת הפקד Change כלומר בכל פעם שנשנה ערך בפקד, השיגרה תתבצע.

Private Sub cbxType_Change()

Select Case cbxType.Value

Case "+"

lblSign.Caption = "+"

Case "-"

lblSign.Caption = "-"

Case "*"

lblSign.Caption = "X"

Case Else

lblSign.Caption = "?"

End Select

End Sub

ולהסברים:

Select Case – תנאי בקרה – מאפשר לכתוב מספר פקודות תנאי שרק אחת יכולה להתקיים. במקרה שתנאי מסויים מתקיים, מבוצעת הפקודה שלו, ולאחר מכן מתבצעת יציאה ממנו.

אם כך בהתאם לValue של cbxType נעדכן את ה Caption של lblSign בסימן המתאים.

lblSign – זה הפקד מסוג תוית שמוצג כתו -?

כמובן שיש לוודא שהName שלו באמת מוגדר lblSign.

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

אוקי, נשאר "רק" לבנות את המנגנון שמחולל את הגיליון:

ואת זה נעשה בפוסט הבא…..

תגובה אחת

נוב' 17 2012

בניית מחולל תרגילים – שימוש בפקדים שלב 4 שיפור ה(שלום) עולם

מאת: שלומי מסורי נושאים: Excel

שיפור התוכנית Hello World – הוספת פניה למידע בגיליון ובפקד תיבת טקסט

4 תגובות

נוב' 15 2012

בניית מחולל תרגילים – בניית טופס שלב 3- כתיבת קוד Hello World

מאת: שלומי מסורי נושאים: Excel

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

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

2 תגובות

נוב' 06 2012

בניית מחולל תרגילים – בניית טופס שלב 2

מאת: שלומי מסורי נושאים: Excel

בפוסט הקודם בנינו את התשתית הראשונית לטופס – שטח העבודה

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

יש שלושה סוגים עיקריים של פקדים:

  1. הזנת נתונים:
    1. הכנסה של טקסט חופשי – שדה טקסט(Text Box).
    2. בחירה יחידה מתוך רשימה – רשימה נפתחת (Combo Box)
    3. בחירה מרובה מתוך רשימה – רשימה נגללת (List Box)
    4. סימון אופציה בתיבת סימון בוליאנית – (Check Box)
    5. בחירה מתוך אופציות מוצגות – כפתור רדיו (Option Button)

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

  2. הצגת מידע:
    1. פקד תוית – (Label) – בדרך כלל מיועד לכותרות או תוויות לשדות ופקדים אחרים – מידע סטטי, ולעיתים להצגת מידע דינמי כמו הערות ותוצאות פעולה.
    2. מסגרת – מיועד למיסגור אזור/ים בטופס שעוסקים בנושא מסויים, ובעיקר לתחום את כפתורי הרדיו.
    3. חוצצים – (Multi Page) לצורך חלוקה של תהליך/ טופס לשלבים ניתן לרבד "דפים" כמו חוצצים על הטופס.
    4. תמונה – (Image) פקד המאפשר להציג תמונה

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

  3. הפעלה:
    1. כפתור הפעלה – (Command Button) יזימה של פעולה.

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

וכך זה נראה במצב עריכה:


וכך במצב ריצה:

יש סרגל עיצוב שיעזור לנו לסדר את הטופס בצורה אחידה ויפה יותר (אם הוא לא מוצג: view->Toolbars->User Form ):

טיפ קטן-גדול

יש מחשבים שלא מאפשרים להזין טקסט בעברית לפקדים, הפתרון לכך טמון במערכת ההפעלה:

Control Panel > Regional and Language Options > Administrative > Change system local… > Hebrew

תדרשו לאתחל את המחשב מחדש, אבל הכל יעבוד כמו שצריך….

לצורך המחולל שלנו, נבנה את הטופס הבא:


ובזמן ריצה זה נראה כך:


חשוב לדעת שאת הקובץ יש לשמור עם סיומת XLSM – כלומר קובץ עם מאקרו.

בפוסט הבא נדבר על הקוד מאחורה.

עדיין אין תגובות

נוב' 06 2012

בניית מחולל תרגילים – בניית טופס שלב 1

מאת: שלומי מסורי נושאים: Excel

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

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

במהלך הסדרה נלמד כיצד לבנות טופס, להפעיל פקדים בטופס, ולחולל גליונות עבודה.

בשלב הראשון נלמד לבנות טופס.

בשלבים הבאים נחבר אליהם קוד.

  1. נפתח קובץ אקסל חדש.
  2. נעבור לאזור הקוד (ALT+F11)
  3. תחת עץ הפרויקט של הקובץ החדש נוסיף טופס(כפתור ימין על אחד האלמנטים בעץ -> הוסף-> טופס)

    אז יפתח לנו "משטח עבודה" שהוא הבסיס של הטופס.
  4. נוכל להתאים את גודל הטופס כרצוננו ע"י גרירת הקוביות הלבנות שבקצהו.
  5. אני מעוניין בשלב זה בטופס קטן וקומפקטי, והוא יראה כך:
  6. אם ננסה להפעיל את הטופס (F5), הוא יפתח ויראה כך:
  7. לחיצה על הX בפינה תסגור את הטופס.
  8. אני מעוניין לשנות את כותרת הטופס למשהו משמעותי יותר, ולכן עלי לפנות להגדרות האוביקט, באזור שמתחת לעץ הפרויקט.
    אפשר להבחין מיד שתוכן ההגדרות משתנה בהתאם לאוביקט שיקבל פוקוס, לכן נסמן את הטופס שלנו, ונזין לשדה
    CAPTION ערך לכותרת שלנו – "הטופס שלי" וזה יראה כך לאחר ההפעלה של הטופס
  9. אם תשחקו עם ההגדרות השונות תמצאו הרבה דברים מעניינים, כמו אפשרות לשנות את כיוון הטופס לכתיבה עברית, צבעי הטופס, תמונת רקע וכו’.

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

8 תגובות

אוק' 31 2012

שבירת שורה בתא בפונקציה ובלעדיה

מאת: שלומי מסורי נושאים: Excel, טיפים

שוב אני לא מצליח למצוא זמן לכתוב פוסטים חדשים

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

בבוקר התקשר אלי חברי לחדר בעבודה הקודמת ושאל שאלה תמימה:

"איך אני מוסיף שורה חדשה בפוקנציה של שירשור"

כלומר אם יש לי בתא A1 טקסט ובתא A2 טקסט נוסף ואני רוצה לחבר את תוכן שתי התאים לתא B1 אבל שכל טקסט יופיע בשורה נפרדת בתא B1

=A1&A2

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

פונקצית CHAR מאפשרת להזין מספר קוד אסקי (ASCII) לתוך תא ואז הנוסחה נראית כך.

=A1 & char(10) & A2

חשוב מאוד, כדי לראות את ההשפעה של התוספת הזאת יש להגדיר גלישת טקסט על התא.(Wrap text)

אם אתם תוהים מהו צרוף המקרים המדובר, את אותה שאלה בדיוק נשאלתי בערב ע"י חבר אחר.

אהה, סתם לוודא שאתם זוכרים, אם רוצים בזמן כתיבת טקסט לרדת שורה בתוך תא יש להשתמש בצרוף ATL+ENTER (ואם זה לא עובד תנסו עם הALT השמאלי!!!!)

תהנו חברים

9 תגובות

יונ' 04 2012

פתרון תקלה מטרידה בחוברות מאקרו

מאת: שלומי מסורי נושאים: Excel, טיפים

לאחרונה פנו אלי כמה חברים והציגו לי את התקלה הבאה:

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


object library invalid or contains references to object definitions that could not be found

ניסיתי לעזור ולתת כל מיני רעיונות להגדרות האבטחה אך ללא הועיל.

עד שהצרה הגיע לפתחי או יותר נכון למחשבי.

גיגול קצר הביא אותי לפוסט הזה: קישור

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

הטיפול פשוט מאוד ואני אסכם אותו בקצרה,

  1. יש לסגור את אקסל.
  2. ולאתר את כל הקבצים עם סיומת EXD.
    ניתן למצוא אותם בנתיבים הבאים: יש להחליף את הה
    USER בשם המשתמש של המחשב
WIN XP WIN 7
c:\documents and seettings\USER\Application Data\Microsoft\Forms\ C:\Users\USER\AppData\Microsoft\Forms\ C:\Users\USER\AppData\Roaming\Microsoft\Forms\

C:\Users\USER\AppData\Local\Temp\VBE

  1. איתחול המחשב

אצלי זה הספיק,

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

2 תגובות

יונ' 04 2012

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

מאת: שלומי מסורי נושאים: Excel

חבר פנה אלי שבוע שעבר ושאל אם יש דרך לעשות סיכום ערכים לפי צבע רקע. בעבר כתבתי לו פונקצית VBA שתעזור לו, אך הוא רצה לברר האם יש פונקציה כזו מובנית בגרסת 2010. מברור שעשיתי, אין עדיין פונקציה מובנית שכזו. אך זה נותן לי הזדמנות להציג לכם פונקציה פרטית משלכם. UDF בלשון מיקרוסופט. זו גם הזדמנות לתת מענה לדרישה שעולה כל הזמן מסקר המשוב שעשיתי – הוספת התיחסות לקוד. פונקצית UDF נכתבת באזור הקוד מאקרו ובעלת מבנה סטנדרטי. להלן אציג פונקציה שכזו וננתח אות המבנה שלה ביחד.

Public Function SumByColor(PickColorRange As Range, SumRange As Range) הכרזת פונקציה בשם SumByColor המקבלת שני משתנים PickColorRange ו
SumRange מסוג טווח
Dim rCell As Range Dim iColorIndex As Integer Dim Result הגדרת משתנים מקומים משתנה מסוג טווח – מיועד לאיכלוס בלולאת הסריקה משתנה מסוג מספר – מיועד לאיכלוס עם קוד הצבע שעל פיו נסכום משתנה כללי – מיועד לסיכום ביניים בכל סבב של הלולאה
Application.Volatile הגדרת הפונקציה לחישוב בכל שינוי ועדכון בגליון
iColorIndex = PickColorRange.Interior.ColorIndex איכלוס קוד הצבע – לפי הטווח שמתקבל מפרמטר הפונקציה PickColorRange – אנו שולפים את הערך של צבע הרקע
For Each rCell In SumRange לולאה העוברת על כל טווח התאים המיועדים לסיכום מותנה
If rCell.Interior.ColorIndex = iColorIndex Then

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

Result = WorksheetFunction.Sum(rCell.Value, Result)

אם התא התורן עומד בתנאי – אנו סוכמים אותו למשתנה סיכום ביניים

End If

סגירת התנאי

Next rCell סגירת הלולאה
SumByColor = Result החזרת התוצאה לפונקציה – חשוב מאוד
End Function סגירת הפונקציה

והיכן משבצים את הקוד הזה?

פתיחת חלון הקוד בעזרת ALT+F11
מוספים מודול חדש לקובץ האקסל. (כפתור עכבר ימני על עץ הפרויקט ->
insert -> Module בחלון שנפתח מימין ניתן להזין את הקוד מספר דגשים:

  • את הקובץ יש לשמור עם סיומת xlsm – סיומת m מורה על קובץ המכיל קוד מאקרו.
  • הפונקציה תהיה זמינה רק בקובץ שבו היא נשמרה (בעיקרון אפשר לשמור את הקוד בחוברת מאקרו כללית PERSONAL.XLSB – אבל על כך בהזדמנות אחרת)
  • חשוב להגדיר את הפונקציה כPublic בכדי שניתן יהיה להשתמש בה בגליון.
  • הקריאה לפונקציה מתוך הגליון תעשה דרך ממשק הפונקציות (אם הפונקציה לא מופיע שם, יש לשמור את הקובץ, ולוודא שהפונקציה מוגדרת כPublic, לחפשה תחת קטגורית User defined או מוגדר על ידי המשתמש) במקרה שלנו זה אמור להראות כך: =SumByColor(A1,I3:I8)
  • לבקשת המגיב חגי צרפתי צילום מסך של דוגמת השימוש בפונקציה:

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

לשם הנוחות אצרף כאן את כל הקוד ברצף:

Public Function SumByColor(PickColorRange As Range, SumRange As Range)
Dim rCell As Range Dim iColorIndex As Integer Dim Result
Application.Volatile
iColorIndex = PickColorRange.Interior.ColorIndex
For Each rCell In SumRange
If rCell.Interior.ColorIndex = iColorIndex Then
Result = WorksheetFunction.Sum(rCell.Value, Result)
End If
Next rCell
SumByColor = Result
End Function

Public Function SumByColor(PickColorRange As Range, SumRange As Range)

Dim rCell As Range Dim iColorIndex As Integer Dim Result

Application.Volatile

iColorIndex = PickColorRange.Interior.ColorIndex

For Each rCell In SumRange

If rCell.Interior.ColorIndex = iColorIndex Then

Result = WorksheetFunction.Sum(rCell.Value, Result)

End If

Next rCell

SumByColor = Result

End Function

21 תגובות

פבר' 01 2012

איסוף נתונים

מאת: שלומי מסורי נושאים: Excel, בלוגים

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

איסוף הנתונים עצמם יכול להיות תהליך ארוך, מיגע, רווי שגיאות ומייאש.

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

ברמה גסה אני מחלק את נושא האיסוף לשתי קטגוריות

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

+ סקר קצר לטובת שיפור הבלוג.

3 תגובות

ינו' 31 2012

מחלקת אבידות ומציאות – VLOOKUP

מאת: שלומי מסורי נושאים: Excel

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

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

ראשית , יש שתי וריאציות לפונקציה: VLOOKUP, HLOOKUP

בעיקרון הן פועלות באותה השיטה אך אחת פועלת לרוחב ואחת לאורך.

אני אתרכז בפונקציה הנפוצה יותר: VLOOKUP.

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

אם לדוגמה יש לי טבלה עם שם מוצר, מחיר, קוד המוצר, כמות וכו’ לפי שם המוצר אני יכול לדעת פרטים נוספים כמו מחירו ו/או כמות במלאי וכו’


בואו ננתח את מבנה הפונקציה,

A10 – הפניה לערך שאותו אנו מחפשים בטבלה,

A1:E6 – טווח הטבלה בו יש לאתר את הערך המבוקש – חשוב לשים לב, הפונקציה תחפש את הערך רק בעמודה הראשונה!! אם ננסה למשל לחפש ערך לפי קוד מוצר לא נקבל תשובה.

2 – מספר העמודה מהטבלה שנקבל כתוצאה של הפונקציה במידה והיא תאתר את הערך המבוקש בטבלה. – במקרה זה העמודה השניה מהטווח שהגדרנו לחיפוש הוא המחיר.

FALSE – בדר"כ נשתמש באופציה הזו שמשמעותה איתור של ערך מדוייק בטבלה.

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

אם נרצה לאתר כמות לפי קוד מוצר נאלץ לשנות את הפונקציה בצורה משמעותית יותר =VLOOKUP(A10,C1:E6,2,FALSE)

טווח הטבלה (בצבע אדום) השתנה כדי שערך המבוקש – קוד ימצא בעמודה הראשונה שלו,

העמודה להחזרת הערך (בצבע כחול) -2 מספר העמודה הוא ביחס לטווח הטבלה כלומר אם הטבלה מתחילה עכשיו מעמודה C אז הכמות מעמודה D היא העמודה השניה בטווח הזה.

זהו, זו כל התורה על פוסט אחד, נכון לא מסובך?!

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

מכאן אפשר להשתמש בצורה יצירתית להמון שימושים,

טריק להשוואת שתי רשימות

לדוגמה, יש לי שתי רשימות ואני רוצה לברר מי משותף בשניהן ומי יחודי בכל רשימה.

אחת הדרכים לעשות זאת היא עם פונקצית VLOOKUP כאמור.

לדוגמה:


בצילום מופיעים שתי טבלאות, הימנית מכילה את רשימת לשנת 2012 והשמאלית את רשימת העובדים שנה קודם.

אם אנו רוצים לרכז את העובדים שהמשיכו משנת 2011 ל2012 לרשימה אחת, את העובדים שעזבו ואת העובדים החדשים שימוש בפונקצית VLOOKUP יכול לעזור מאוד.


נעשה שימוש בשתי פונקציות VLOOKUP אחת בכל טבלה,

בימנית

בשמאלית

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

בכל טבלה תוצאה מספרית מצביעה על ערכים משותפים בשתי הטבלאות – עובדים שהמשיכו מ2011 ל2012, תאים עם N/A בטבלה הימנית מציינים עובדים חדשים, ובטבלה השמאלית עובדים שעזבו.

כמובן שאפשר לשלב פונקציה כמו ISNA שבודקת אם תוצאה של פונקציה אחרת מחזירה N/A ופונקצית IF בכדי לפרמט את הדוח בצורה יפה יותר.

נקבל סיכום שכזה:


הנוסחאות המשופרות: ו בהתאמה

כמובן שאפר עכשיו למיין את התוצאות לקבל:


דוגמה נוספת: במיוחד לטוביה

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

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

להלן דוגמה:


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

(אם יש שם זהה הראשון שימצא מראש הטבלה יאותר וישויך פעמיים)

לצורך כך אנו נוסיף עמודה חדשה לטבלה השמאלית ביןB לC , בה נבנה נוסחה פשוטה שתשרשר את שם העובד לשם משפחתו (אני אוסיף גם תו מפריד ליתר ביטחון) ואגרור את הנוסחה עד סוף הטבלה.


ובעמודה של הטבלה הימנית אני אוכל לשלב את הפונקציה שלנו:

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

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

וזה נראה בסופו של דבר כך:


כמובן שאת עמודה C נוכל להסתיר (כפתור ימני על כותרת העמודה והסתר, או בקיצור CTRL+0 אם עומדים על אחד התאים בעמודה)

בהצלחה.

31 תגובות

ינו' 30 2012

על הגנות גליון

מאת: שלומי מסורי נושאים: Excel, טיפים

בעבר פרסמתי פוסט קצר על נעילת קבצים,

היום אפרט על הגנה מפני שינוי בתוך הגליון עצמו.

יש פעמים שאנו מעוניינים להגדיר אזור ספציפי המותר לשינוי למשתמשים ואת שאר התאים לחסום מפני שינוי.

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

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

נעילת תאים

כדי לסמן תא (או טווח תאים) המותר לשינוי יש לסמנו ולהגיע לעיצוב תא (CTRL+1) בלשונית האחרונה בחלון שיפתח ("הגנה") יש להסיר את הסימון מתיבת הסימון "נעול".

יש לחזור על פעולה זו על כל התאים שאנו מעוניינים לפתוח לעריכה ואז לנעול את הגליון.

נעילת גליון

בתפריט "סקירה" יש לבחור "הגן על הגליון", תפתח תיבת דו-שיח "הגנת גליון".


ניתן להוסיף סיסמה להגנה אך אפשר גם ללא סיסמה.

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

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

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

בלחיצה על אישור הגיליון ננעל ויאפשר שינוי רק בתאים ש"שיחררנו".

לביטול הנעילה יש ללחוץ שוב על אותו הכפתור בתפריט (רק שהפעם הוא יופיע בתור "הסרת הגנת גליון")

ואם הזנתם סיסמה תתבקשו להקישה.

הגנת חוברת עבודה

אם אנו רוצים להסתיר גליונות לחלוטין או לחסום אפשרות להוסיף או למחוק גליונות,

יש רמה נוספת של הגנה שניתן להוסיף והיא הגנת חוברת עבודה.

לאחר שהסתרנו את הגליונות החסויים (כפתור ימני על לשונית הגליון למטה -> הסתר)

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

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

פוסט זה הינו פוסט תגובה לשאלתה של עופרה כהן.

3 תגובות

אוק' 26 2011

ייבוא רשימות כתובות וטלפונים לחשבון Gmail

מאת: שלומי מסורי נושאים: Excel, טיפים

להלן פוסט תגובה לשאלתו של יעקב איך ניתן לייבא רשימת אנשי קשר מאקסל לGMAIL.

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

ייבוא הרשימה מתחלק לשני שלבים, שלב ראשון, יצירת קובץ CSV בפורמט המתאים.

והשלב השני טעינתו לGMAIL.

מה זה בכלל קובץ CSV? –(Comma-separated values) קובץ במבנה CSV הוא למעשה קובץ טקסט רגיל (שאפשר לפתוח אותו גם בNOTEPAD) כל שורה היא למעשה רשומה חדשה, וכל השדות מופרדים בפסיק. הזכרתי שימוש של קובץ זה בפוסט אחר (מי שטופס – טופס). פרטים נוספים והרחבות אפשר כמובן למצוא בWIKI.

ניתן לייצר כאמור קובץ שכזה בעזרת כל עורך טקסט בסיסי, אך מובן שעדיף להשתמש באקסל ולשמור את הקובץ עם סיומת CSV (לא לשכוח לשנות את סוג הקובץ במהלך שמירה בשם).

מבנה הקובץ שנדרש לGMAIL הוא כזה: שורה ראשונה מכילה חלק מהכותרות או כולן:

Name, Given Name, Additional Name, Family Name, Name Prefix, Name Suffix, Initials, Nickname, Short Name, Maiden Name, Birthday, Gender, Location, Billing Information, Directory Server, Mileage, Occupation, Hobby, Sensitivity, Priority, Subject, Notes, Group Membership, E-mail 1 – Type, E-mail 1 – Value, E-mail 2 – Type, E-mail 2 – Value, E-mail 3 – Type, E-mail 3 – Value, Phone 1 – Type, Phone 1 – Value, Phone 2 – Type, Phone 2 – Value, Address 1 – Type, Address 1 – Formatted, Address 1 – Street, Address 1 – City, Address 1 – PO Box, Address 1 – Region, Address 1 – Postal Code, Address 1 – Country, Address 1 – Extended Address, Organization 1 – Type, Organization 1 – Name, Organization 1 – Title, Organization 1 – Department, Organization 1 – Symbol, Organization 1 – Location, Organization 1 – Job Description, Website 1 – Type, Website 1 – Value

רק תבחרו.

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

לאחר שמולאו כל הנתונים, יש לשמור את הקובץ במבנה CSV, שמירה בשם ß שמור כסוג (לשנות לCSV), ולתת שם קובץ ומיקום ß ולחיצה על שמור בשם.

אז את השלב הראשון סימנו, יש קובץ מוכן.

בשלב השני יש להכנס לחשבון הGMAIL ולבחור בתגית האנשי קשר (Contacts).

תחת כפתור "עוד" ß "יבא…" ß בחר קובץ ß לבחור את הקובץ שיצרנו קודם. ß "ייבא".

וזהו, כל אנשי הקשר שהזנתם בקובץ אמורים להטען לחשבון הGMAIL שלכם.

אגב, למי ששם לב, יש גם כפתור ייצוא, מה שמאפשר לייצא את הרשימה הקימת בחשבון שלכם לחשבונות אחרים או לתוכנות אחרות. (למשל למיזוג דואר… – ועל כך, בהזדמנות אחרת)

בהצלחה.

20 תגובות

מאי 30 2011

טבלת ציר PIVOT ממספר גיליונות

מאת: שלומי מסורי נושאים: Excel

נתקלתי היום בשאלה מעניינת, איך אני בונה טבלת ציר (PIVOT) מכמה מקורות נתונים, זכרתי שבגרסת אקסל 2003 האופציה הופיע במסך הראשון של אשף טבלאות הציר, אך בגרסה 2007 לא מצאתי איך להפעיל את האשף הנ"ל!!!

איך יכול להיות שבאקסל 2003 היה ניתן לבנות טבלת ציר (PIVOT) ממספר טווחים /גליונות/ קבצים, ואילו ב2007 אי אפשר?!

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

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

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

ב2007 הוספת PIVOT מתבצעת בצורה ישירה מתפריט הוספה -> Pivot Table.

אז נפתח חלון קטן

אך החלון הזה אינו מספיק, אין בו את האופציה להוסיף טווחים.

לצורך כך יש להשתמש באשף ה Pivot Tabl.

וכאן לבחור את האופציה השלישית: "טווחי איחוד מרובים"

הבעיה היא איך מפעילים את האשף המדובר?

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

נלחץ על המשולש הקטן בקצה הסרגל ונבחר באופציה "פקודות נוספות"

במסך שנפתח:

יש לבחור לתיבת הבחירה "בחר פקודות מתוך" את האופציה "כל הפקודות" ולאתר ברשימה מתחת את הכפתור "אשף PivotTable ו-PivotChart" וללחוץ עליו לחיצת עכבר כפולה.

הוא יעבור לרשימה המקבילה, ולאחר לחיצה על האישור הוא יופיע בסרגל הכלים לגישה מהירה.

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

נ.ב.

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

7 תגובות

מאי 18 2011

סרגלי עזר לאקסל

מאת: שלומי מסורי נושאים: Excel, בלוגים

שלום חברים

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

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

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

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

תודה וביי

2 תגובות

מאי 17 2011

קיצורים לקישורים

מאת: שלומי מסורי נושאים: Excel, טיפים

200 קישורים אני צריך לעדכן במסמך המזורגג הזה!!

שמעתי את דוד לוחש לעצמו בזעם, למה אין דרך יותר הגיונית לעדכן את כל הקישורים האלו?!

דוד, מה הבעיה? שאלתי.

אוף תסתום כבר קרציה, אקסל כבר לא יוכל לעזור לי.

למה לא? שאלתי.

כי אני צריך לעדכן קובץ וורד, זה למה, ענה דוד ביאוש.

רוצה להסביר לי מה הבעיה? אולי אני כן יכול לעזור.

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

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

אז למה לא תבנה נוסחה באקסל ותעתיק את התוצרים שלך למסמך? הצעתי.

עיניו נפערו בתדהמה, ואיך באקסל נבנה את הלינק?

אהה, זה כבר באמת שטויות, תשתמש בנוסחה, לדוגמה

="http://myweb/index//File" &A1 & ".html"

ומה זה עוזר זה עדיין לא קישור, התחיל דוד להתעצבן.

טוב, בשביל זה יש גם פונקציה: HYPERLINK

שים את שם הקובץ בA1

את הנוסחה שבנינו בB1

ואת הפונקציה =HYPERLINK(B1,A1) בC1

אחרי גרירה של הנוסחה והפונקציה לפי מספר השורות(קבצים) שצריך

כל שנותר הוא להעתיק את תוכן עמודה C למסמך.

בהצלחה

4 תגובות

מאי 17 2011

טיפ קצר: איך למחוק את כל ההערות מהגיליון

מאת: שלומי מסורי נושאים: Excel, טיפים

שבוע שעבר הייתי צריך לשלוח תבנית של קובץ אקסל שאני עובד איתו.

הקובץ מכיל נתונים רבים שאין למקבל צורך בהם והם קלים למחיקה. הבעיה היא מה עושים עם ההערות?

הקובץ הכיל הרבה הערות מפוזרת ברחבי הגיליון.

לצורך כך עשיתי שימוש בפעולות הבאות לצורך מחיקה מבירה של כל ההערות בבת אחת.

יש לוודא כי אין תאים מסומנים בסמן ואז:

מקש F5 – פותח את מסך מעבר אל:

בלחיצה על כפתור "מיוחד" יפתח מסך עזר לבחירת מעברים מיוחדים

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

כעת כל התאים המכילים הערות מסומנים, מה שנותר לעשות הוא: למחוק את כל ההערות של התאים הנ"ל.

לחיצה ימנית עם העכבר על אחד התאים המסומנים ונבחר ב"מחק הערה"

וזהו כל ההערות בגיליון ימחקו.

בהצלחה

2 תגובות

פבר' 28 2011

סיכומים רוחביים – טיפ מהיר וקצר על גיליון סיכום

מאת: שלומי מסורי נושאים: Excel, טיפים

יוצא לי לא פעם לראות רעיונות או מוצרים שהדבר הראשון שאתה חושב עליהם זה:

"איך לא חשבתי על זה בעצמי".

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

הוא התחבר לי עם פתרון שחיפשתי לגיליון סיכום רוחבי.

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

והייתי צריך גיליון ראשי שמסכם את כלל הגיליונות.

כל הגיליונות שלי באותו מבנה ובכולם שורת הסיכום נמצאת בשורה 12 לדוגמה.

לצורך סכימה של כולם חשבתי לפתח UDF (פונקציית בהגדרת משתמש) ובסוף התברר לי שיש פתרון פשוט הרבה יותר.

לדוגמה:

=Sum(’*'!A12:E12)

זהו!!! פשוט ‘*’ מתייחס לכל הגיליונות (מלבד הנוכחי)

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

=Sum(sheet1:sheet12!A12:r12)

כל גיליון שמיקומו בין SHEET1 לSHEET12 יכללו בסיכום.

לא חייבים להגביל את עצמנו רק לסיכום אפשר כמובן למצוא את הערך העליון (Max) או התחתון (Min) או הממוצע (Average)

אז זהו להיום, קצר אבל נראה לי מועיל ביותר

3 תגובות

פבר' 23 2011

קבוצות חיתוך – בשלוש נוסחאות

מאת: שלומי מסורי נושאים: Excel, טיפים

דוד נכנס לחדר עם מבט נבוך על פניו.

שב שב, אמרתי, מה קרה מדוע נפלו פניך?

לאחר שכנוע קל הסביר לי שהוא מנסה לתאם בין שתי רשימות ערכים,

ולבנות 3 קבוצות המכילות פילוח שונה של האוכלוסיה,

קבוצה ראשונה – ערכים משותפים

קבוצה שניה – ערכים שנמצאים ברשימה א’ אבל לא ברשימה ב’

קבוצה שלישית – ערכים שנמצאים ברשימה ב’ ולא ברשימה א’

"מה הבעיה?! הסר דאגה מלבך ותוגה מעינייך" אמרתי לו.

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

בעמודה C – הקבוצה הראשונה: בנינו את הנוסחה =IF(COUNTIF(A:A,B2)>0,B2,"") כלומר, בדוק האם הערך בתא B2 מופיע לפחות פעם אחת בעמודה A, אם כן, הצג את הערך מB2.

בעמודה D – הקבוצה השניה: בנינו את הנוסחה =IF(COUNTIF(B:B,A2)=0,A2,"") כלומר, בדוק האם הערך בתא A2 לא מופיע כלל בעמודה B, אם כן, הצג את הערך מA2.

בעמודה E – הקבוצה השלישית : בנינו את הנוסחה =IF(COUNTIF(A:A,B2)=0,B2,"") כלומר, בדוק האם הערך בתא B2 לא מופיע כלל בעמודה A, אם כן, הצג את הערך מB2.

נשאר רק לגרור את הנוסחאות הללו לכל השורות מתחת, ובכך לקבל את תוצר הנ"ל.

ומה אם אני משנה ערך אחד מתוך הרשימה? הוא ידע להתעדכן?

מה אתם אומרים?

נסו ותיהנו.

מבוסס על סיפור אמיתי…

תגובה אחת

פבר' 15 2011

עוד אל אימותים – מניעת כפילויות

מאת: שלומי מסורי נושאים: Excel, טיפים

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

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

הבעיה היא, שרק בסיום המילוי אני שם לב ששיבצתי את יואב פעמיים.

יש לך רעיון איך אני מונע את זה?

כמובן שיש פתרון, והוא אפילו פשוט מאוד ליישום.

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

בעזרת נוסחה מתאימה אפשר להגביל את אפשרויות ההזנה בצורה יצירתית.

לדוגמה בטבלה הבאה, יש להגביל את הנתונים בתאים B2:B9 שלא יחזרו על עצמם:

לכן, יש לסמן טווח זה ולהוסיף אימות (נתונים –< אימות נתונים) לבחור באופציה מותאם אישית ולכתוב בנוסחה את השורה הבאה:

=COUNTIF($B$2:$B$9,B2)<=1

כדאי מאוד להוסיף הסבר להתראת השגיאה:

ובמקרה שגיאה תופיע ההודעה:

טוב, יש אפשרויות מעניינות נוספות, כמו למשל להגביל ל2 משימות לאדם (יש לכם רעיון איך עושים זאת?)

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

אזהרה:

מידע:

זהו להיום,

7 תגובות

פבר' 10 2011

מי שטופס – טופס. על טפסים ושמירת נתונים בWORD

מאת: שלומי מסורי נושאים: Excel, טיפים

נשאלתי שאלה מעניינת, האם אפשר לשמור נתוני טפסים בWord לקובץ אקסל.

התשובה היא – ברור.

רק שזה לא הפתרון האופטימלי לאיסוף נתונים.

את התשובה לשאלה הנ"ל אציג בפוסט להלן.

5 תגובות

פבר' 10 2011

טיפ קטן על זהירות בקבצים

מאת: שלומי מסורי נושאים: Excel, טיפים

פנה אלי היום חבר ושאל אותי אם אפשר לבנות לו מאקרו שבשעת שמירת הקובץ הוא אמור לוודא כי לא נדרס הקובץ המקורי.

לא הבנתי לרגע מה הוא מתכוון, ואז הוא הסביר לי:

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

לעיתים הוא שוכח לבצע את פעולת שמירה בשם ושומר בצורה רגילה!!! מה שכמובן גורם לשינוי הקובץ המקורי.

הפתרון לכך הוא פשוט ביותר.

במקום ללחוץ לחיצה כפולה על הקובץ במנהל הקבצים, בכדי לפתוח את הקובץ.

הצעתי לו ללחוץ כל כפתור עכבר ימני על הקובץ ולבחור "חדש".

כך יפתח לו קובץ חדש המכיל את תכולת הקובץ המקורי ללא חשש של דריסת קובץ המקור.

אלמנטרי, ווטסון ידידי

2 תגובות

ינו' 26 2011

על הצפנה ופענוח – גליון לשימוש אישי

מאת: שלומי מסורי נושאים: Excel, בלוגים

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

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

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

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

לטובת הקוראים אני חולק איתכם את הקובץ לשימושכם החופשי.

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

הגליון חסום לשינויים של הנוסחאות אך הן כולן גלויות לעיונכם ולימודכם.

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

אהה.. ואין שום שורת קוד אחת!!!!

הורדה מהנה

והצפנה מוצלחת

עדיין אין תגובות

נוב' 16 2010

מי מפחד ממאקרו?!

מאת: שלומי מסורי נושאים: Excel, כללי

איך מתחילים להקליט מאקרו.
סדנת גמילה מפחד קוד.

12 תגובות

נוב' 10 2010

הוספת הערת תמונה

הבוקר פנה אלי חבר בשאלה:
איך אפשר להוסיף הערת תמונה על תא.

חשבתי לעצמי וואו זה לא משהו שאפשרי.
אך מסתבר שטעיתי.

להלן סרטון קצר שמסביר איך לעשות זאת.

פשוט גדול סרטון הדרכה

עדיין אין תגובות

ספט' 07 2010

עוד על מסננים – פוסטגובה לחנה

מאת: שלומי מסורי נושאים: כללי

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

8 תגובות

ספט' 07 2010

הגנה מפני סקרנים – נעילת קבצים

מאת: שלומי מסורי נושאים: Excel, טיפים

איך להגן על הקבצים בסיסמה,

9 תגובות

מאי 11 2010

אמת או שקר – על אימות נתונים באקסל – חלק א’

מאת: שלומי מסורי נושאים: Excel, טיפים

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

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

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

אופציה זו נקראת בקצרה – "אימות נתונים"

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

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

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

אחת בעזרת הערות (כפתור ימני על תא –> הוספת הערה / שילוב SHIFT+ F2) שהיתרון והחסרון שלה שההערה מופיעה רק בריחוף עכבר מעל התא, כך שאם אני עובד רק עם הממקלדת אני לא אראה את ההערה (אלא אם היא מוצגת בצורה קבועה מה שעלול לגרום לעומס יתר במידע ל המסך)

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

עוד על אימותים בפוסט ההמשך

2 תגובות

ינו' 26 2010

עוד על שמות בגליון – והפעם רשימה משתנה

מאת: שלומי מסורי נושאים: Excel, טיפים

שלום לכולם,

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

הפעם אני אצרף קובץ דוגמה המציג אפשרות לקבל רשימה דינמית, כלומר עפ"י בחירת ערך בתא A רשימת הערכים האפשרית בתא B משתנה בהתאם.

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

ערים רחובות1 רחובות2

בקובץ המצורף אין אפילו שורת קוד אחת.

הגדרתי בגליון הגדרות את עמודה A את שמות הערים ובעמודות H ואילך את הרחובות.

גליון הגדרות

ונתתי שם לטווח זה: "ערים"  נוסחת השם של הרשימה הזאת מיוצגת בנוסחת OFFSET לפי המבנה הבא:

=OFFSET(הגדרות!$A$1,0,0,COUNTA(הגדרות!$A:$A))

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

החוכמה כאן היא ברשימה השניה!

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

הבחירה למעשה תלויה בתא שמימין לתא שאנו מעדכנים.

בקיצור הנוסחה של שם הטווח הזה היא: "רחובות" ונוסחת השם היא מורכבת יותר:

=OFFSET(הגדרות!$G$1,1,MATCH(’פרטי עובדים’!C9,הגדרות!$A:$A,0),COUNTA(OFFSET(הגדרות!$G$1,1,MATCH(’פרטי עובדים’!C9,הגדרות!$A:$A,0),500)))

והתרגום כמובן: התחל בתא G1, רד שורה אחת מטה וX שורות שמאלה (הX הוא למעשה נוסחה של MATCH – איתור הערך הרצוי בתוך רשימה קיימת. ובמקרה שלנו, מהתא שליד התא הנבחר במסגרת האימות נתונים בתוך רשימת הערים בעמודה A והחזרת המיקום שלו ברשימה)  ותפוס כמות שורות ששוה לכמות התאים שאינם ריקים בעמודה הנ"ל (כאן אני סופר את העמודה המתאימה לפי ההיסט שחושב בנוסחת OFFSET וMATCH).

חשוב ביותר לשים לב, יש כאן תאים מקובעים ותאים שאינם מקובעים (אותם הדגשתי במקרה שלנו C9)

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

7 תגובות

ינו' 19 2010

ואלה שמות – על שמות בגליון

שמות באקסל נועדו להקל על השימוש וההבנה של נוסחאות על טווחי נתונים.
לדוגמה, נוסחה שמחשבת מכפלה של שני תאים. תהיה מובנת יותר אם נגדיר שמות לכל אחד מהמרכיבים(Pay+Tax= כאשר Pay מוגדר לתא A1 וTax מוגדר לB1).

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

נתחיל באיך מגדירים שם לתא או לטווח נתונים.

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

אפשרות אחרת, ללחוץ על הקיצור Ctrl+F3, למלא בשורה למטה את הטווח (לדוג’ A$1=) ובשדה העליון (מסומן בצהוב) להגדיר את השם הרצוי ללא רווחים.

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

טוב, יש לי שם, מה זה עוזר לי?!

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

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

אופציות נוספות קשורות לVBA וזה ….

נחשתם נכון, בפוסט אחר.

ביי בנתיים.

ובהצלחה.

תגובה אחת

ינו' 12 2010

מציאת האחד והיחיד – או איך מוציאים רשימת ערכים יחודית

מאת: שלומי מסורי נושאים: Excel, טיפים

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

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

אפשרות אחת לחפש ידנית, נראה אותכם עם רשימה של אלפיים שמות.

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

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

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

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

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

בהנחה שהרשימה שלכם נמצאת בעמודה A החל משורה 1 ועד לשורה 1000, את הרשימת התוצאות אני מעוניין למקם בעמודה B, סדר הפעולות הוא כדלהלן:

בתא B1 יש להזין את הערך הראשון ברשימה מעמודה A (באותה מידה אפשר לקשור אותו בנוסחה "A1=")
בתא B2 להכניס את הנוסחה הבאה:
=OFFSET($A$1,MATCH(0,MMULT(–TRANSPOSE(TRANSPOSE($A$1:$A$130)=B$1:B1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)

קצת ארוך מה? יש לשים לב שהמספר המודגש בכחול מייצג את כמות הרשומות בעמודה A אם יש יותר צריך להגדיל בהתאם (המקסימום לפי בדיקה שלי על אקסל 2003 היא 5461 רשומות)
השלב האחרון הוא להפוך את הנוסחה לנוסחת מערך, מתוך העזרה של אקסל(נוסחת מערך: נוסחה המבצעת מספר חישובים בקבוצת ערכים אחת או יותר ולאחר מכן מחזירה תוצאה אחת או מספר תוצאות. נוסחאות מערך מוקפות בסוגריים מסולסלים { }, ומוזנות על-ידי הקשה על CTRL+SHIFT+ENTER.), Microsoft Excel מוסיף אוטומטית את הנוסחה בין { } (סוגריים מסולסלים).
מכאן מה שנותר הוא לגרור את הנוסחה מטה, התוצאה תהיה רשימת הערכים הייחודיים (אם עברנו את כמות הערכים היחודיים התאים יופיעו עם שגיאת נוסחה N/A#)
זהו, יש לכם נוסחה שמחשבת את הערכים הייחודיים.

בהצלחה

13 תגובות

ינו' 10 2010

טריק קצר על המרת טקסט למספר

מאת: שלומי מסורי נושאים: Excel, טיפים

טריק לטיפול במספרים עם בעיות זהות

יש פעמים שאתם מיבאים נתונים ממקורות חיצוניים או מדביקים רשימת מספרים אך שוד ושבר, אקסל לא מזהה את התוכן כמספר (אפשר להבחין בזאת אם הוא מצמיד את התוכן המספרי כאילו היה טקסט לועזי).
אפשרות אחת היא להכנס למצב עריכה של התא (F2) לצאת. (ENTER/TAB)
הבעיה מחמירה שיש טור/ים עם מאות ואלפי מספרים שלא מודעים לעצמם מספיק.
הטריק הוא פשוט ושימושי:
מסמנים את התאים הבעיתיים ואז בוחרים: נתונים –> טקסט לעמודות.
כשנפתח המסך לוחצים מייד על סיום.
ו… ואללה כל המספרים עם בעיות הזהות קיבלו טיפול יעיל ומהיר.

בהצלחה

22 תגובות

ינו' 10 2010

עוד על יבוא, או יבוא מהרשת

מאת: שלומי מסורי נושאים: Excel

עוד על יבוא נתונים והפעם נתונים מהאינטרנט, ובעיקר מאתר הבורסה.

8 תגובות

נוב' 16 2009

יבוא ללא מכס

מאת: שלומי מסורי נושאים: Excel, טיפים

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

2 תגובות

ספט' 04 2009

מסננים על קצה המזלג

מאת: שלומי מסורי נושאים: Excel

איך מסננים נתונים, בעזרת סינון אוטומטי, ואפילו בעזרת סינון מתקדם.
טיפ נוסף לסינון כפולים

13 תגובות

אוג' 13 2009

איך מבינים מה עושה פונקציה

מאת: שלומי מסורי נושאים: Excel

אחרי חצי שבוע של צוואר תפוס וגב בממתינה

אני חוזר בפוסט חדש

 

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

 

ונתחיל בקיצור:

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

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

 

אני רוצה לדון היום בקיצורים שקשורים במסגרת הקסומה הזאת.

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

 

שילוב של CTRL לפעולת הגרירה מעתיק את התאים למקום החדש.

שילוב של SHIFT….. "דוחף" את התאים למקום החדש!!!

שילוב של שניהם כמובן מעתיק את התוכן המקורי ו"דוחף" אותו למיקום החדש

דחיפה והוספה

עד כאן על הקיצורים.

 

 

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

 

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

=B$1*$A2

ועכשיו כל שנותר הוא לגרור את הנוסחה מטה ולאחר מכן שמאלה.

 

בהצלחה.

6 תגובות

אוג' 02 2009

נוסחאות ופונקציות חלק ב’

מאת: שלומי מסורי נושאים: Excel

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

19 תגובות

יול' 20 2009

נוסחאות – תחילת הדרך

מאת: שלומי מסורי נושאים: Excel

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

14 תגובות

יול' 09 2009

על קיצורים חלק ב’

מאת: שלומי מסורי נושאים: Excel, טיפים

שלום לכולם,

היום אציג עוד קיצורים בהקשר של תנועה וסימון בגליון:
  • CTRL+רווח – סימון כל העמודה
  • SHFT+רווח – סימון כל השורה
  • CTRL+SHFT+רווח – סימון טווח התאים הנוכחי (כלומר אם עומדים באמצע טבלה כלשהי, כל הטבלה תסומן)
  • CTRL+* – כנ"ל
לאחר שלמדנו לנוע ולסמן בגליון נדבר על קיצורים נוספים:
  • CTRL+’+’ (שילוב של CTRL ופלוס) כתלות בטווח המסומן, אם מסומן תא יוסיף תא, אם מסומנת שורה/ות יוסיף שורה/ות ואם מסומנת עמודה/ות יוסיף עמודה/ת. אם לדוגמה, העתקנו שורה ולחצנו על הצירוף הנ"ל יתווספו התאים המועתקים לגיליון.
  • CTRL + ‘-’ (שילוב של CTRL ומינוס) מחיקה כתלות בטווח המסומן, כמו בסעיף הקודם.
אם נחשוב על צרוף של קיצורים אפשר לדוגמה לצורך הוספת עמודה ללחוץ בשלב ראשון את השילוב CTRL+רווח ומייד לאחר מכן CTRL+’+’.
  • F2 – עריכת התא עליו עומדים
  • SHFT+F2 – הוספת הערה
  • SHFT+F3 – הפעלת אשף הפונקציות
  • F5 – מסך מעבר אל (נדבר בפוסט עתידי בהרחבה על נושא השמות)
  • SHFT+F5 – מסך חיפוש
  • CTRL+F – כנ"ל
  • CTRL+H – מסך החלפה
  • CTRL+F6 – מעבר בין חוברות עבודה פתוחות
  • CTRL+TAB – כנ"ל
  • ALT+F8 – הפעלת מאקרו
  • CTRL+F11 – פתיחת מסך עריכה של VBA (בVBA נעסוק בהרחבה בפוסטים הבאים)
  • SHFT+F11 – גליון עבודה חדש בחוברת הנוכחית
  • ALT+SHFT+F1 – כנ"ל
  • F12 – שמירה בשם
  • CTRL+1 – עיצוב תא
  • CTRL+2 – הפעלה/ביטול הדגשה (BOLD)
  • CTRL+3 – הפעלה/ביטול הטייה (ITALIC)
  • CTRL+4 – הפעלה/ביטול קו תחתי
  • CTRL+5 – הפעלה/ביטול קו חוצה
  • ALT+צ,ג,ש – שינוי שם גליון
  • ALT+ע,ן – מחיקת גליון.
  • ALT+ENTER – התחלת שורה חדשה בתוך התא
  • CTRL+ANTER – מילוי טווח בערך שהוזן (אם לדוגמה מסומן טווח A1:A5, ואני מעוניין להזין לתוך כל הטווח את הערך 5, אז לאחר שהזנתי 5 לתא הראשון שילוב CTRL+ENTER יזין את הערך לכל שאר התאים המסומנים מראש)
  • CTRL+D – העתקת הנתונים מהתא הראשון לכל התאים מתחתיו (בשונה מהשילוב הקודם ניתן לסמן את הטווח לאחר שכבר יש לי נתון בתא הראשון) – שימושי מאוד להעתקת נתונים סמוכים.
    לדוגמה: יש לי גליון המכיל שלוש עמודות נתונים כאשר שתי העמודות הראשונות מכילות ערכים ובעמודה השלישית אני מעוניין להוסיף נוסחה, הדרך המהירה ביותר לשכפל את הנוסחה לאחר שכבר בניתי אותה היא כדלהלן: שילוב CTRL+END להגיע לתא האחרון בגליון (לחילופין CTRL+חץ למטה אם יש יותר טבלה אחת בגליון) סימון התא האחרון בעמודה המיועדת להעתקה (אמור כבר להיות מסומן) וצירוף CTRL+SHFT+חץ למעלה לצורך סימון טווח כל התאים בעמודה המיועדת למילוי. ולבסוף CTRL+D לצורך שכפול הערך בתא העליון לכלל התאים המסומנים בטווח.
  • CTRL+R – מילוי ימינה הרעיון דומה לשילוב הקודם.
  • CTRL+; – הכנסת התאריך הנוכחי
  • CTRL+SHFT+; הכנסת השעה הנוכחית.
אלא הם הקיצורים העיקריים שאני משתמש בהם, יתכן ובהמשך הבלוג אביא קיצורים נוספים.
ועתה לתופעות "מוזרות" והסברן
תופעה:
  • בלחיצה על מקש החצים הסמן לא זז לי או לחילופין הגיליון כולו זז אבל הסמן לא זז ההתא עצמו.
הסבר:
  • כפי הנראה לחצן SCROLL LOCK במצב לחוץ, לחיצה נוספת תשחרר את הגליון לפעולה רגילה
תופעה:
  • לחיצה על מקש ENTER בד"כ מעבירה את הסמן לתא הבא מלמטה, אך לפעמים לחיצה על ENTER מביאה את הסמן לתא אחר לגמרי.
הסבר:
  • כאשר מנווטים בין התאים בעזרת TAB ולאחר מכן לוחצים ENTER אקסל מנחש כי סיימתם להזין שורת נתונים ואתם מעוניינים להתחיל להזין שורה חדשה, לכן הוא מחזיר את הסימון לתא מתחת לתא הראשון שהתחלתם בניווט הTAB.
אם אתם גם נתקלים בתופעות "מוזרות" שכאלה, שלחו לי ואני אוסיף אותם לפוסט הזה.
בפוסט הבא אני מעוניין להציג את נושא הנוסחאות והפונקציות.
אז נסיים להיום ולהתראות בפעם הבאה

עדיין אין תגובות

יול' 02 2009

על קיצורים ויעילות – ממש בקצרה חלק א’

מאת: שלומי מסורי נושאים: Excel, טיפים

קיצורי מקלדת לאקסל – שימושיים יותר ושימושיים עוד יותר. חלק – א

11 תגובות

Switch to our mobile site