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

[אשה כשרה]

שווי הנכס כפול 5 כפול 30
לא?

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

 

[קול תורה]

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

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

 

[יעקב ויצמן]

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

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

תראה בהגדרות של התראת שגיאה
ראה הסבר בלינק

מידע נוסף על אימות נתונים - תמיכה של Microsoft

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

support.microsoft.com

 

[קול תורה]

תראה בהגדרות של התראת שגיאה
ראה הסבר בלינק

מידע נוסף על אימות נתונים - תמיכה של Microsoft

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

support.microsoft.com

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

 

[קול תורה]

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

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

 

[יעקב ויצמן]

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

אני לא מול המחשב לבצע ניסוי.
אבל.נניח שעמודת המקור היא A עם תאים ריקים.
ובעמודה B תבצע קיבוץ עם פונקצייות unique +sort
בעמודה b יתקבל לך אוטומט רשימה ללא רווחים.
ואז את הטווח לאימות נתונים תבצע על נתוני עמודה b.
לכאורא זה אמור לעבוד.
תגיד לי אם זה פתר לך את הבעיה

 

[יעקב ויצמן]

מצרפת דוגמה בטור A זה מופיע כתאריך.
בטור B זה מופע כטקסט - רשמתי ידנית. אני רוצה להעתיק את כל טור A שכולו יהפך לטקסט כמו טור B.
בינתיים מה שמצאתי שזו עבודה שלוקחת זמן (יש לי מעל 6K שורות כאלו שהתאריך משתנה באמצע), זה להעתיק בגרירה+CTRL את התאריך הזהה, ואז לשנות ידנית תאריך ולהמשיך בגרירה וכו'.
יש לך רעיון לדרך קצרה יותר?
תודה

לא זכור לי כעת.סורי

 

[xl3391]

מצרפת דוגמה בטור A זה מופיע כתאריך.
בטור B זה מופע כטקסט - רשמתי ידנית. אני רוצה להעתיק את כל טור A שכולו יהפך לטקסט כמו טור B.
בינתיים מה שמצאתי שזו עבודה שלוקחת זמן (יש לי מעל 6K שורות כאלו שהתאריך משתנה באמצע), זה להעתיק בגרירה+CTRL את התאריך הזהה, ואז לשנות ידנית תאריך ולהמשיך בגרירה וכו'.
יש לך רעיון לדרך קצרה יותר?
תודה

אפשר להפוך את העמודה לטקסט ע"י טקסט לעמודות.
לסמן את עמודת התאריכים> נתונים> טקסט לעמודות> מופרד> הבא> להוריד את כל סימוני הV> הבא> לסמן 'טקסט'> סיום.

 

[xl3391]

תודה רבה

בדיוק זה מה שרציתי להגיע

אשמח להבין איך עשית את זה

שלב ראשון - רשימה של שבוע ושנה אבל שיופיעו רק פעם אחת ע"י פונקציית UNIQUE (הפונקציה בתא H1)
שלב שני - לשלוף את המקסימום לאותם שבוע ושנה ע"י פונקציית maxifs (הפונקציה בתא J2 ולגרור)
שלב שלישי - עבור כל שורה של שבוע שנה, לשלוף את כל הימים בהם השער היה המקסימלי לאותו שבוע, ע"י פונקציית FILTER ומכיוון שFILTER מחזירה מערך על עמודה, צריך לעטוף אותה בTRANSPOSE בכדי שהמערך יתפרס על שורה (הפונקציה בתא K2 ולגרור)
שלב רביעי - ליצור רשימה קטנה של הימים הרלוונטיים בשבוע (שני עד שישי) וע"י פונקציית FREQUENCY לחשב עבור כל יום כמה פעמים היה בו השער המקסימלי. (הפונקציה בתא Q2)
הערות:
1.שלב 4 אפשר גם ע"י countifs ולגרור. היתרון בFREQUENCY שרושמים אותה רק בתא אחד והיא עושה את העבודה במקרה הזה.
2.הפונקציה FREQUENCY במקור נועדה לתת את המענה לתדירות בטווח ערכים, אבל כאן היא מתאימה

 

[xl3391]

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

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

 

[beedebi]

אפשר להפוך את העמודה לטקסט ע"י טקסט לעמודות.
לסמן את עמודת התאריכים> נתונים> טקסט לעמודות> מופרד> הבא> להוריד את כל סימוני הV> הבא> לסמן 'טקסט'> סיום.
צפה בקובץ המצורף 1438622

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

 

[xl3391]

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

את הטקסט לעמודות מפעילים ישירות על עמודה A.
(את עמודות G ו F הוספתי רק בכדי להראות מה השתנה בעמודה A לאחר המרתה לטקסט ע"י טקסט לעמודות).

 

[beedebi]

את הטקסט לעמודות מפעילים ישירות על עמודה A.
(את עמודות G ו F הוספתי רק בכדי להראות מה השתנה בעמודה A לאחר המרתה לטקסט ע"י טקסט לעמודות).

תודה.
נתקלתי בעוד בעיה, מצרפת קובץ לדוגמה. עמודה A זה הנתון שקיבלתי. עמודה B לאחר המרה בעיצוב-מותאם אישית לתבנית תאריך בספרות MM/YYYY. עמודה C לאחר המרה לטקסט כפי שהראת לי, הוא מוסיף יום בתאריך. במקום שישאר רק חודש ושנה
איך אפשר להתגבר על זה שלא יתווסף יום?

 

[xl3391]

עמודה B לאחר המרה בעיצוב-מותאם אישית לתבנית תאריך בספרות MM/YYYY.

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

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

> הבא> עבור העמודה הראשונה לבחור לא לייבא עמודה:

>עבור העמודה השניה לבחור טקסט:

>סיום

 

[beedebi]

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

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

> הבא> עבור העמודה הראשונה לבחור לא לייבא עמודה:
צפה בקובץ המצורף 1438934

>עבור העמודה השניה לבחור טקסט:
צפה בקובץ המצורף 1438935

>סיום

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

 

[חד כתער]

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

אני מחפש נוסחה לגוגל שיטס. שכל תא בעמודה B שימולא בנתנוים, אוטומטית יתמלא בתא הימני אליו- בעמודה A התאריך של יום מילוי הנתונים. למשל, אם בתא B3 כתבתי ביום 18/01/23 נתון כלשהוא, אוטומטית בתא A3 התמלא על ידי הנוסחא תאריך 18/01/2023.

ובתא B4 מילאתי נתון בתאריך 01/05/23, אוטומטית התמלא בתא A4 התאיך 01/05/23

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

 

[xl3391]

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

אני מחפש נוסחה לגוגל שיטס. שכל תא בעמודה B שימולא בנתנוים, אוטומטית יתמלא בתא הימני אליו- בעמודה A התאריך של יום מילוי הנתונים. למשל, אם בתא B3 כתבתי ביום 18/01/23 נתון כלשהוא, אוטומטית בתא A3 התמלא על ידי הנוסחא תאריך 18/01/2023.

ובתא B4 מילאתי נתון בתאריך 01/05/23, אוטומטית התמלא בתא A4 התאיך 01/05/23

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

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

 

[קול תורה]

האם בנוסחאות מערך כמו פילטר אפשר למנוע שגיאות #SPILL! שנגרמת כי התאים איפה שהתוצאות היו אמורות לזלוג אינם ריקים, ע"י משהו שיגרום לאקסל להוסיף שורות אוטומטית "כשחסר לו מקום"?

 

[יעקב ויצמן]

האם בנוסחאות מערך כמו פילטר אפשר למנוע שגיאות #SPILL! שנגרמת כי התאים איפה שהתוצאות היו אמורות לזלוג אינם ריקים, ע"י משהו שיגרום לאקסל להוסיף שורות אוטומטית "כשחסר לו מקום"?

אתן לך דוגמא עם פונקציית sort והוא הדין לכל נוסחת מערך

יש כאן ערכי טקסט בעמודה a
הבקשה פה היא פשוטה למיין את הערכים.
אז כתבתי את פונקציית sort לטווח a1:a10
האקסל מבין שהשטח שכאן חסום על ידי תא עם נתונים "זרים".זהו כמובן תא b9.
ולכן מתריע על שגיאת spill
עכשיו שים לב .גם (הדגשתי זאת.כי בפועל אין סיבה אמיתית לאקסל לעשות זאת ואין זה מדרכי האקסל....וקל וחומר ובפרט שזה לא אפקטיבי.אבל רק לצורך ההבנה פה .זרמתי עם שאלתך שגם אם נניח שזה יקרה ...)אם האקסל יחליט לעזור למשתמש הגיליון "ולעשות לו טובה" להוסיף עוד 2 שורות וככה כל הנתונים יוכלו "להישפך"
תהיה כאן בעיה.כי המושג "פלגינן " שמור ללומדי התלמוד.וטרם הגיע לסטנדרו של האקסל הגאון....
אז שנוסיף 2 שורות מעל שורה 9 .זה כולל את כל העמודות כולל עמודה a.
מה שיקרה הוא שהטווח בפונקצייה יגדל באופן אוטומט לa12 במקום a10.

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

 

[קול תורה]

אתן לך דוגמא עם פונקציית sort והוא הדין לכל נוסחת מערך
צפה בקובץ המצורף 1439756
יש כאן ערכי טקסט בעמודה a
הבקשה פה היא פשוטה למיין את הערכים.
אז כתבתי את פונקציית sort לטווח a1:a10
האקסל מבין שהשטח שכאן חסום על ידי תא עם נתונים "זרים".זהו כמובן תא b9.
ולכן מתריע על שגיאת spill
עכשיו שים לב .גם (הדגשתי זאת.כי בפועל אין סיבה אמיתית לאקסל לעשות זאת ואין זה מדרכי האקסל....וקל וחומר ובפרט שזה לא אפקטיבי.אבל רק לצורך ההבנה פה .זרמתי עם שאלתך שגם אם נניח שזה יקרה ...)אם האקסל יחליט לעזור למשתמש הגיליון "ולעשות לו טובה" להוסיף עוד 2 שורות וככה כל הנתונים יוכלו "להישפך"
תהיה כאן בעיה.כי המושג "פלגינן " שמור ללומדי התלמוד.וטרם הגיע לסטנדרו של האקסל הגאון....
אז שנוסיף 2 שורות מעל שורה 9 .זה כולל את כל העמודות כולל עמודה a.
מה שיקרה הוא שהטווח בפונקצייה יגדל באופן אוטומט לa12 במקום a10.
זה יקרה גם אם יש קיבוע בפונקצייה.
כי אין דבר העומד בפני הוספת שורות בתוך טווח הפונקצייה.!שום קיבוע לא יעזור!
ושוב האקסל תקוע עם אותה בעיה בדיוק.
לכן אין מנוס פה,חוץ מלפנות את השטח ולמחוק את הטקסט שמפריע פה.

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