אקסל האשכול המרכזי לשאלות באקסל/גוגל שיטס

[תגרין]

האקסל שלי 'השתגע'.
יש לו נוסחה כבר שנים רבות לחשב תשלומים, ועכשיו מכניסים לדוג' על תלמיד ששילם 500 ובסיכום כתוב 7833, מחליפים ל-5 נהיה 12440
האם יש באג בקובץ?
האם יתכן שזה קובץ מדי כבד?
דחוף ממש

 

[ישראלי1]

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

 

[יעקב ויצמן]

האקסל שלי 'השתגע'.
יש לו נוסחה כבר שנים רבות לחשב תשלומים, ועכשיו מכניסים לדוג' על תלמיד ששילם 500 ובסיכום כתוב 7833, מחליפים ל-5 נהיה 12440
האם יש באג בקובץ?
האם יתכן שזה קובץ מדי כבד?
דחוף ממש

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

 

[תגרין]

-

 

[יעקב ויצמן]

שלום וברכה
אשמח לעזרה
בעמודה B יש ערכי תאריך וערכים שונים
אני רוצה שיהיה בעמודה A מספור רץ שישתנה (במספר אחד קדימה) כל פעם שמופיע תאריך בעמודה B
כמו שמוצג בטווח שצבעתי בצהוב
מצו"ב צילום מסךצפה בקובץ המצורף 1562476

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

קצת הסבר על מה שקורה כאן:
אז ראשית בכל הפתרונות יש לכתוב בהזנה ידנית את התא העליון ביותר במספר בו נרצה להתחיל "בסדרה" .בדוגמא כאן זה המספר 1.
בטווח המסומן באדום קיימת פונקציית if+isnumber
התנאי הוא האם בתא שבאותו שורה כתוב ערך מספרי או לא.הבדיקה עצמה מופקדת בידיה של פונקציית isnumber.
הרעיון פה שתאריך שכתוב בפורמט תאריך הוא יש בו למעשה "מספר סידורי" משלו כפי שהסברתי בעבר פעמים רבות.
ולכן שכתוב תאריך מתקיים התנאי .ולכן מוחזר התא שמעל בקפיצה של עוד מספר קדימה.
שהתנאי לא מתקיים מוחזר המספר בתא מעל ללא קפיצה במספר.
קומבינצייה זו יעילה בתנאי שבעמודת התנאי אין סתם מספרים "משוטטים".
בטווח המסומן בצהוב קיימות פונקציות if+regexmatch (פונקציית REGEXMATCH זמינה בשיטס.באקסל היא אופצייה בvba וכו...זו פונקצייה מאוד מעניינת .בהזדמנות בל"נ אכתוב על הלוגיקה שבה)
הרעיון כאן שיש "סימן היכר" המאופיין בכתיבת תאריך והוא הסלש /.בתנאי רגיל כללי ההתאמה מאוד "קפדניים". וכיון שתאריך מכיל גם ערכי טקסט של יום,חודש,ושנה אז אם נחפש את הערך "/" בלבד .התוצאה תהיה תמיד false .
כאשר משתמשים בבדיקה זו על ידי פונקציית REGEXMATCH .הפונקצייה מזהה את הערך גם שהוא "דומה וממש קרוב" .כלומר גם שיש לפני ואחרי הסלש עוד כמה רכיבי טקסט זה גם "מאושר".
יתר התנאי בif זה כנל.
מה שיש להקפיד כאן.הוא על פורמט טקסט בעמודה בה התאריכים נמצאים.אחרת תוחזר שגיאה.
בטווח המסומן בירוק קיימות פונקציות if+isnumber+search
וזהו למעשה פיתרון הכי אידיאלי לכל פורמט של עמודה ולכל ערכי טקסט שישנם שם.
בסופו של דבר התנאי התקיים שבתא ישנו סימן סלש /.
על הלוגיקה כאן כתבתי בעבר באריכות רבה ראה כאן בלינק-קחנו משם....
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13016285

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

 

[xl3391]

שאלתך שאלה מאוד יפה ומעניינת.
מצורף כאן בגיליון 3 פיתרונות שונים.
צפה בקובץ המצורף 1562729
קצת הסבר על מה שקורה כאן:
אז ראשית בכל הפתרונות יש לכתוב בהזנה ידנית את התא העליון ביותר במספר בו נרצה להתחיל "בסדרה" .בדוגמא כאן זה המספר 1.
בטווח המסומן באדום קיימת פונקציית if+isnumber
התנאי הוא האם בתא שבאותו שורה כתוב ערך מספרי או לא.הבדיקה עצמה מופקדת בידיה של פונקציית isnumber.
הרעיון פה שתאריך שכתוב בפורמט תאריך הוא יש בו למעשה "מספר סידורי" משלו כפי שהסברתי בעבר פעמים רבות.
ולכן שכתוב תאריך מתקיים התנאי .ולכן מוחזר התא שמעל בקפיצה של עוד מספר קדימה.
שהתנאי לא מתקיים מוחזר המספר בתא מעל ללא קפיצה במספר.
קומבינצייה זו יעילה בתנאי שבעמודת התנאי אין סתם מספרים "משוטטים".
בטווח המסומן בצהוב קיימות פונקציות if+regexmatch (פונקציית REGEXMATCH זמינה בשיטס.באקסל היא אופצייה בvba וכו...זו פונקצייה מאוד מעניינת .בהזדמנות בל"נ אכתוב על הלוגיקה שבה)
הרעיון כאן שיש "סימן היכר" המאופיין בכתיבת תאריך והוא הסלש /.בתנאי רגיל כללי ההתאמה מאוד "קפדניים". וכיון שתאריך מכיל גם ערכי טקסט של יום,חודש,ושנה אז אם נחפש את הערך "/" בלבד .התוצאה תהיה תמיד false .
כאשר משתמשים בבדיקה זו על ידי פונקציית REGEXMATCH .הפונקצייה מזהה את הערך גם שהוא "דומה וממש קרוב" .כלומר גם שיש לפני ואחרי הסלש עוד כמה רכיבי טקסט זה גם "מאושר".
יתר התנאי בif זה כנל.
מה שיש להקפיד כאן.הוא על פורמט טקסט בעמודה בה התאריכים נמצאים.אחרת תוחזר שגיאה.
בטווח המסומן בירוק קיימות פונקציות if+isnumber+search
וזהו למעשה פיתרון הכי אידיאלי לכל פורמט של עמודה ולכל ערכי טקסט שישנם שם.
בסופו של דבר התנאי התקיים שבתא ישנו סימן סלש /.
על הלוגיקה כאן כתבתי בעבר באריכות רבה ראה כאן בלינק-קחנו משם....
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13016285

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

באקסל של מיקרוסופט אפשר לבדוק אם התא מעוצב כתאריך ע"י פונקציית cell בארגומנט הראשון "format". אם מחזיר D1 התא מעוצב כתאריך (בפורמט הנ"ל).
בגוגל שיטס יש פונקציה ISDATE בכדי לבדוק אם תא מכיל תאריך. אבל היא מחזירה TRUE גם עבור שעה.

 

[יעקב ויצמן]

באקסל של מיקרוסופט אפשר לבדוק אם התא מעוצב כתאריך ע"י פונקציית cell בארגומנט הראשון "format". אם מחזיר D1 התא מעוצב כתאריך (בפורמט הנ"ל).
בגוגל שיטס יש פונקציה ISDATE בכדי לבדוק אם תא מכיל תאריך. אבל היא מחזירה TRUE גם עבור שעה.

תודה על ההארה.
בדרך כלל אני תמיד שואף בכל פיתרון שאני עורך וכותב שיהיה מענה רלוונטי הן באקסל והן בשיטס.
כפי שכתבת סוג המידע format אינו זמין בשיטס בארגומנט הראשון info_type של פונקציית cell.
isdate בשיטס גם אינה רלוונטית שיש ערכי שעה בעמודה.
ולמעשה 2 הפיתרונות הראשונים שכתבתי גם אינם מושלמים "לכל תרחיש"..וכתבתי לעיל את המגבלות בכל אחת מהן.
הפיתרון בעזרת if+ isnumber+ search הוא הכי מעולה לכל תרחיש ולכל פורמט וכפי שהדגשתי בתשובתי לעיל.וזה עובד הן בשיטס והן באקסל.

מה שמעניין כאן בפיתרון הזה הוא שפורמט העיצוב של התא מאבד מהרלוונטיות שלו.ומדוע?
כי את פונקציית isnumber מעניין רק אחד מהשניים כאן.
או שיוחזר כאן על ידי search מספר כל שהוא (זהו למעשה המיקום של התו / מתחילת המחרוזת) או שיוחזר פה value(כי הפונקציה לא מצאה במחרוזת את התו /) שזהו טקסט יבש.
אם זה מספר יוחזר true על ידי isnumber
ואם יוחזר value זהו עוד טקסט ככל הטקסטים ולכן התשובה תהיה false.
שוב תודה רבה על ההארה ועל התזכורת ל2 הפונקציות הנפלאות שהזכרת.

 

[אחת שאוהבת להבין]

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

 

[xl3391]

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

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

Excel creates non-existent worksheets in VBA Project Explorer

Excel 2010 Professional Plus (32bit) seems to have created a new set of worksheets and renamed all the original worksheets in the VBA screen, e.g. Sheet10 becomes Sheet101, Sheet13 becomes Sheet131...

superuser.com

Remove a non-existant Sheet in VBA

I have sheets listed in an Excel Workbook that don't actually exist. The sheets are listed when you view code and are looking at the list of sheets in the VBA part, but they don't actually exist in...

stackoverflow.com

 

[נבחר]

כיצד עושים מיון מספרים באקסל אבל בצורה רגילה - שהמספרים הבודדים יופיעו בהתחלה ולא אחרי העשרות?
כרגע המספר 2 מופיע אחרי 10 והמספר 3 אחרי 20 וכן הלאה

 

[יעקב ויצמן]

לסמ

כיצד עושים מיון מספרים באקסל אבל בצורה רגילה - שהמספרים הבודדים יופיעו בהתחלה ולא אחרי העשרות?
כרגע המספר 2 מופיע אחרי 10 והמספר 3 אחרי 20 וכן הלאה

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

אקסל 365 - הפונקציה SORT למיון רשומות דינמי

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

excel-vba.co.il

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

 

[נבחר]

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

 

[יעקב ויצמן]

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

אהה.אני מכיר את הבעיה והתופעה הזו.
תנסה לסמן את העמודה להגדירה בפורמט כללי>תעתיק אותה >בעמודה ריקה ליד תדביק "בהדבקה מיוחדת" של ערכים בלבד.
זה אמור לעזור ב99% מהמקרים.
אם לא עזר תפתח עמודה ריקה ותזין בתא b1 (אם נניח עמודת המקור היא a) את הפונקצייה של (a1)value
וכמובן לבצע גרירה למטה
יש עוד דרך משעשעת "לכפות" על האקסל לבצע המרה למספר על ידי פעולה מתמטית.
אם עמודת המקור היא עמודה A
יש לכתוב בתא b1
a1×1=
או a1+0 =
התוצאות יהיו שוות בשתי העמודות אך הפורמט ישתנה..
אבל זה אומר שצריך להשאיר את עמודת המקור ולהסתירה.
בדרך כלל אין רצונו של משתמש הגיליון בעמודה מיותרת שכזו...אז זה לא ממש פרקטי.
הוספתי את זה רק כטריק ברמת הידיעה..וזה קצת משעשע ומעניין איך האקסל משנה פורמט שרואה "על הצג" פעולה מתמטית...הצדעה למתמטיקה..!כולנו כך...

 

[אחת שאוהבת להבין]

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

Excel creates non-existent worksheets in VBA Project Explorer

Excel 2010 Professional Plus (32bit) seems to have created a new set of worksheets and renamed all the original worksheets in the VBA screen, e.g. Sheet10 becomes Sheet101, Sheet13 becomes Sheet131...

superuser.com

Remove a non-existant Sheet in VBA

I have sheets listed in an Excel Workbook that don't actually exist. The sheets are listed when you view code and are looking at the list of sheets in the VBA part, but they don't actually exist in...

stackoverflow.com

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

 

[שמעון יוחאי]

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

 

[יעקב ויצמן]

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

בעבר הסברתי את הלוגיקה בשאלה מעין שאלתך למה זה לא אפשרי...תראה בלינק כאן
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13081029
אם אקח כדוגמא ואקרא לטבלת המקור -טבלה 1 ולטבלה המיובאת -טבלה 2.
האקסל וכן השיטס "מכבדים את רצונו" של בעל הגיליון .ולא יזיזו /יוסיפו שורה/ות(שנכתב בה משהו) סתם ככה (וגם שיש "מצוקת מקום/מרחב") בגיליון 2,(ובמקרה כזה יתקבל איתות של שגיאת ref בשיטס או spill באקסל - האיתות אומר כאן לבעל הגיליון ."תפנה את הדרך בבקשה..")
אא"כ זו פעולה יזומה של משתמש הגיליון ויש כאן אישור "וגילוי דעת" מפורש.

הזכרת את המילה קיבוע.ובכן זה פיתרון אחד כאופציה.והוא לקבע את טווח היבוא בפונקציית importrange על ידי תיחום הטווח עד לשורה ספציפית בגיליון 1.
ואני יודע שלא לפיתרון כזה התכוונת בשאלתך..
אז מה שניתן לעשות הוא להקצות שטח מסוים בגיליון 2 ושם לכתוב באותו מבנה של עמודות טקסט חופשי
ובגיליון 3 להערים (מלשון ערימה אחד על השני) את טבלה 2 מעל הטווח של הטקסט החופשי.על ידי פונקציית vstack בשילוב עם filter כדי "לצמצם" ולסנן את התאים הריקים כפי שהסברתי בעבר בלינק כאן (בפיתרון השלישי)
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13093177
ככה תוכל לכתוב כרצונך טקסט חופשי בטווח המיועד וכן בגיליון 1.
ובגיליון 3 תקבל את כל הנתונים בצורה מסודרת טווח מעל טווח.
בהצלחה רבה!

 

[נ.ב.]

קובץ אקסל שיתופי בדרייב
יש לי עמודה עם מספר שמהווה ממוצע נתונים מספריים של מס' עמודות
אני רוצה עמודה נוספת שנותנת לי דירוג באותיות לפי ערך המספר בעמודת הממוצע
אם הערך הממוצע בין 90-100 - שתכתב האות א
ממוצע 70-89- ב'
ממוצע 50-69 - ג'
והלאה

 

[יעקב ויצמן]

קובץ אקסל שיתופי בדרייב
יש לי עמודה עם מספר שמהווה ממוצע נתונים מספריים של מס' עמודות
אני רוצה עמודה נוספת שנותנת לי דירוג באותיות לפי ערך המספר בעמודת הממוצע
אם הערך הממוצע בין 90-100 - שתכתב האות א
ממוצע 70-89- ב' והלאה

יש להציב בעמודה פונקציית if מקונן ראו כאן מדריך
או פונקציית ifs ראו כאן מדריך
או פונקציית switch (יש להקפיד מאוד מאוד בסדר התנאים בפונקצייה זו)
כתבתי עליה בעבר באריכות עם דוגמא בלינק כאן
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-12766300
אם משהו לא מסתדר נא לשלוח גיליון דוגמא
בהצלחה רבה!

 

[נ.ב.]

יש להציב בעמודה פונקציית if מקונן ראו כאן מדריך
או פונקציית ifs ראו כאן מדריך

עזר מאוד
תודה רבה

 

[פ. גינזבורג]

צריכה שוב את עזרת המבינים
יש לי עמודה ורשום בה סך שעות עבודה יומית.
רוצה לדעת איך אני מחשבת את כל העמודה יחד עמ''נ שתצא לי תשובה של שעות
ז''א, נניח 5:45 שעות + 3:30 שעות = 9:15 שעות
איך אני עושה את זה?
תודה מראש!