היי

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

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

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

ב- 2003: נתונים  > אימות > רשימה (בחוצץ "הגדרות")

ב- 2007: חוצץ "נתונים" > אימות נתונים > אימות נתונים > רשימה (בחוצץ "הגדרות")

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

1. בטווח נפרד, נניח D1:D3 נכין את הרשימה שלנו:

2. עתה נגדיר עיצוב מספר מותאם אישית, כך שלמרות שערך התא יהיה 0 או 1, בתא יופיעו המילים "עובד" "לא עובד" בהתאמה (ראו בלוג קודם שלי בנושא):

קליק ימני עם עכבר על תא D2 > עיצוב תאים > מותאם אישית

בחלון "סוג" רושמים (במקום מה שיהיה רשום שם. לא לדאוג, המחיקה של הטקסט לא מוחקת את הסוג) ברצף :

[=0]"לא עובד"; [=1]"עובד"

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

ניתן לראות שמה שמופיע עכשיו בתא זה "לא עובד" ו-"עובד", אך זו רק הצגה, כי ערך התא הוא 0 או 1.

עתה נעמוד בתא B2, נפתח חלון אימות נתונים ונבחר כרשימה את הטווח D2:D3 ולוחצים אישור:

ניתן לראות עכשיו שכאשר עומדים על תא B2, מופיע חץ ובלחיצה עליו ונפתחת רשימה. למרות שברשימה מופיע "עובד" "לא עובד", הרי שבחירה ב-"עובד " תכניס לתא B2 ערך 1. "לא עובד" יכניס ערך 0. על מנת שמה שיופיע בתא היה "עובד ו- "לא עובד", אנו נעתיק את העיצוב שיצרנו בתאים D2 ו- D3 גם לתא B2.

נעילה

עתה ניגש לנעילה.

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

בוחרים תא B3 > פותחים חלון אימות נתונים > מותאם אישית

רושמים את התנאי הבא:

=AND(B2=1,B3)

משמעות התנאי היא:

פונקציה AND בודקת התקיימות שני התנאים משני צידי הפסיק. אם שני התנאים נכונים, היא מחזירה TRUE. באקסל ערך 0 הוא FALSE, וערך 1 הוא TRUE. אך כל ערך שאינו 0 בתא B3 יתקבל אצלה כ- TRUE. גם ערך שלילי. במקרה זה אימות נתונים לא ימנע ממני להזין מה שארצה בתא B3, למעט 0.

לפיכך, אם ראובן עובד, ערך התא B2 הוא 1 והתנאי הראשון מתקיים וכל ערך שאזין בתא B3 יתקבל, למעט 0. אם ערך התא B2 הוא 0, אז תוצאה של AND היא מראש FALSE ואימות הנתונים ימנע ממני להזין ערך כלשהו לתא B3.

לדוגמה:

ראובן עובד

ראובן לא עובד וניסיתי להזין מספר לתא B3

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

ניתן לשפר ויזואלית את המניעה על ידי עיצוב מותנה. לדוגמה, אם ערך התא B2 הוא 0, תא B3 יהיה בצבע אפור.

עומדים על תא B3:

ב- 2003: עיצוב > עיצוב מותנה > הנוסחה היא

ב- 2007: חוצץ בית > עיצוב מותנה > כלל חדש > השתמש בנוסחה כדי לקבוע אילו תאים לעצב

במקרה זה, כאשר ראובן לא עובד

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

תיהנו.

מודעות פרסומת

היי

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

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

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

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

אציג את שניהם.

פתרון 1

נכתוב בתא C1 את הטקסט הבא:

= TRANSPOSE(A1:A5

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

עתה, נרשום אחרי הסימן שווה את המילה CONCATENATE ונחליף את הסוגריים המסולסלות {} בסוגריים רגילות (). התוצאה תהיה:

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

=TRANSPOSE(A1:A5)&", "

ולוחצים F9. היתר ממשיכים כמתואר לעיל. התוצאה תהיה:

פתרון 2

כתיבת פונקציה בקוד VBA.

לצורך הכניסה לעורך הקוד, לוחצים Alt+F11 > קליק ימני על VBAProject עם שם של הקובץ עליו עובדים > Insert > בוחרים Module > מתווספת ספריה Modules למטה ובה Module עם מספר. בוחרים ב- Module שהתווסף ורושמים את הקוד.

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

סיימנו.

תיהנו!


היי

נניח יש לנו עמודה של ערכים שנרצה לחלקה לקבוצות בהתאם לגודל. כגון קבוצה 1 ציונים מ-0 עד 40, קבוצה 2 ציונים מ-40 עד 60 וכד'. לאחר מכן נרצה לנתח כל קבוצה, כגון מספר ערכים בה, ממוצע, סטיית תקן וכו'.

לצורך ההדגמה נניח עמודה של יתרות חוב עם 564 ערכים:

הקבוצות יהיו כדקלמן:

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

עתה נקצה את הערכים שבטווח הנתונים לקבוצות באמצעות פונצקיה LOOKUP. מכיוון שמספרי הקבוצות הם מספרים שלמים רציפים המתחילים מ-1, אפשר להשתמש גם בפונצריה MATCH, עם פרמטר אחרון = 1. הפונקציה תראה כך:

אני מחפש את הערך שבעמודת גובה החוב, בעמודה "טווח" והתוצאה מוחזרת מעמודה "מס' קבוצה". התכונה השימושית של LOOKUP היא חיפוש מקורב, כאשר נמצא הערך הנמוך או שווה לערך שמחפשים. LOOKUP מצריכה סידור של ערכים בסדר עולה. התוצאה תהיה בהתאם למספר שבעמודת "טווח" השווה לערך שמחפסים או נמוך ממנו. ניתן לסדר את המספרים שבעמודת "טווח" בסדר יורד ולהשתמש בפונקציה MATCH עם פרמטר אחרון = 1-. במקרה זה התוצאה תהיה הערך השווה או הגבוה מהערך שמחפשים.

עתה לצורך ניתוח הקבוצות נשתמש בטבלת ציר PIVOT TABLE, על הטווח A1:C565. בשורות נשים את עמודת "קבוצה" בנתונים נשים פעמיים את "גובה החוב". פעם עם ספירה ופעם עם סכום. קיבלנו תוצאות של מספר ערכים בכל קבוצה וסכום החוב בכל קבוצה.

סיימנו.

תיהנו.


היי

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

1. גרף מתרחב אוטומטית

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

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

להלן בסיס הנתונים שלנו:

עתה נצור שם לבסיס נתונים זה, אשר יהיה דינאמי. זאת נעשה עם OFFSET ו- COUNTA:

הוספה > שם > הגדרה. נקרא לו data1. פונקציה OFFSET לוקחת טווח של נתונים. על מנת שהוא יהיה דינאמי, גובה הטווח יקבע על ידי פונקציה COUNTA על העמודה A (טווח A:A). ההבדל בין COUNT ל- COUNTA הוא שהראשונה אינה סופרת טקסט והשניה כן. אנו רוצים שהטווח יכלול את כל השורות, גם הכותרת וגם השורה האחרונה. לכן צריך COUNTA. תשימו לב ל-$ משמואל ל-A.

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

באשף טבלת ציר או תרשים ציר נרשום כבסיס הנתונים את השם data1:

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

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

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

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

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

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

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

2. תווית נקודה

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

להלן בסיס הנתונים שלי. העמודה ממוצע הינה פרי פונקציה  AVERAGE על הטווח C2:C10.

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

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

עתה נוסיף את התוויות לגרף שווי שוק. נעשה זאת על ידי קליק ימני על גרף שווי שוק > עיצוב סדרת נתונים >  תוויות נתונים > לשים דגל ב-"שם קטגוריה".

התוצאה נראית כך:

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

 

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

סיימנו.

תיהנו.

 


היי

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

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

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

וכאן רושמים פשוט:

 

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

 

תיהנו.


היי

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

כשלב ראשון, ייתכן שמספר זהות (ללא ספרת ביקורת) שברשותנו נמוך מ-8 ספרות. על מנת להמשיך הלאה, אנו נשלים את המספר ל-8 ספרות, על ידי הוספת אפסים מקדימים.

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

בתמונה להלן, בתא B1 מופיע מספר ת.ז. שברשותנו. בתא B2 נעשית ההשלמה ל-8 ספרות.

עתה, נסדר את ספרות תעודת זהות בת 8 ספרות ב-8 תאים, שכל תא יכלול ספרה אחת. לצורך סידור הספרות ועל מנת שאוכל לגרור, בניתי בשורה 4 מספר סידורי רץ של ספרות. בשורה 5 אני גוזר ספרה אחת מתוך מספר ת.ז. לפי מספר סידורי המופיע בשורה 4. הגזירה נעשית באמצעות פונקציה MID, כאשר הפרמטר השני שלה הוא המספר הסידורי שבשורה 4.

עתה כל אחד מהערכים שבשורה 5 יש להכפיל ב-1 או 2 לפי הסדר המופיע בשורה 6:

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

עתה יש לחבר את כל סכומי הספרות. הסיכום מופיע בתא B9. ספרת הביקורת היא המשלים של הסכום שהתקבל לכפולה של 10 הקרובה ביותר אליו והגדולה ממנו. לדוגמה, אם הסכום הוא 26, כפולה של 10 הקרובה אליו והגדולה ממנו היא 30. ההפרש בין השניים הוא 4. בפועל האם זה 26 ו-30 או 16 ו-20, אינו חשוב. מה שחשוב זה הספרה 6 וההפרש בינה לבין 10. ולכן הנוסחה להלן מבוססת על רעיון זה. ההסרה של הספרה 2 היא על ידי חישוב שארית של חלוקת הסכום ב-10.

סיימנו.

תיהנו.

מקורות:

  1. http://he.wikipedia.org/wiki/%D7%A1%D7%A4%D7%A8%D7%AA_%D7%91%D7%99%D7%A7%D7%95%D7%A8%D7%AA
  2. http://www.orianit.edu-negev.gov.il/elishevapc/sites/homepage/iris%5CregFiles%5C%D7%90%D7%9C%D7%92%D7%95%D7%A8%D7%99%D7%AA%D7%9D%D7%91%D7%93%D7%99%D7%A7%D7%AA%20%D7%AA%D7%A7%D7%99%D7%A0%D7%95%D7%AA%20%D7%A9%D7%9C%20%D7%A1%D7%99%D7%A4%D7%A8%D7%AA%20%D7%94%D7%91%D7%99%D7%A7%D7%95%D7%A8%D7%AA.doc

היי

שני פתרונות שמשמשים אותי ויעזרו לכם, אם טרם הכרתם:

1. אם יש לכם פונקציה שמקבלת טווח של תאים, כגון MATCH, אזי במקום לבחור את הטווח על ידי גרירה של העכבר, ניתן להשתמש בצירוף של SHIFT+CTRL+חץ לכיוון המתאים, או HOME או END. יש לשים לב לכך שקודם לוחצים על SHIFT, ממשיכים להחזיק ולוחצים על CTRL ולאחר מכן על החץ. אפשר להמשיך בהרחבת הטווח על ידי לחיצה נוספת על החץ.

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

סיימנו.

תיהנו.