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

[הילוך גבוה]

רבותיי.הערה קטנה.ציינתי לעיל את הנוסחא מותאמת אישית לגבי תקינות מייל והיא isemail.
והוספתי שפונקצייה זו פעילה הן באקסל והן בשיטס.
אני מתנצל...התבלבלתי לרגע (גם זה קורה...)אז סורי!סורי!ושוב סורי!
פונקצייה זו אכן קיימת בשיטס.אך לא קיימת באקסל.
באקסל יש להזין את הפונקציות isnumber בשילוב match במתכונת כזו בדיוק.
צפה בקובץ המצורף 1682776
וכפי שכתבתי בזמנו(לשאלה זו בדיוק) בלינק כאן
הודעה בנושא 'רוצה לעזור לכם באקסל' https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13203903
@rachelba

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

 

[הילוך גבוה]

שאלות מעניינות ומחכימות!
שלושה מי יודע..
אז ברוכים הבאים לעולם "הולידציות" באקסל..
מצורף כאן גיליון אקסל עם פיתרונות לכל 3 השאלות.ואסביר על ראשון ראשון וכו..
צפה בקובץ המצורף 1682380

ראשית הואיל וישנו מקף בהזנת הניידים שבעמודה,אנו עוקפים את כל בעיית וסוגיית השמטת אפסים שבתחילת המספר.יתרון?תלוי את מי שואלים ובמטרות..
ובכן כאן יש להציב בתא a2 (ואז לבצע העתקה>הדבקה מיוחדת >אימות נתונים..זה תקף לכל הפיתרונות לקמן) בנוסחא מותאמת אישית שבאימות נתונים את הנוסחא הזו
צפה בקובץ המצורף 1682390
הרעיון כאן שפונקציית len סופרת 11 תוים במספר נייד תקין (המקף נחשב כתו)
כאשר משמיטים מספר או מקף או כותבים מספר נוסף .האימות נתונים יתן את אותותיו כפי שניתן לראות בדוגמא שערכתי.
אני מאוד ממליץ לקרוא בלינק המצורף את אשר כתבתי בזמנו על יסוד ושורש המחלוקת/החילוק בין פונקציית len לבין פונקציית counta
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13613855


למזלינו,ולמזלם הטוב של בני האנושות,ישנה פונקצייה מובנית באקסל /שיטס שנקראת isemail .
תפקידה הוא פשוט לומר לנו בהחזר true או false כלומר האם המייל תקין או לא.
אנו "מנצלים" את ההזדמנות פז הזו...ופשוט מזינים את הנוסחא באימות נתונים בתא b2 ככה:
צפה בקובץ המצורף 1682403

באימות נתונים שערכתי בגיליון.אין "הכרח" למלא ערכים בעמודה D.אלא שישנו תנאי שאם לא הוזן ערך בעמודה c כלומר שהפונקצייה counta תחזיר 0 ולא 1. אז לא ניתן להזין שום ערך בעמודה d(כלומר- אפשר לנסות את מזלינו ולכתוב..אבל אז נקבל איתות וסימון אדום המורה על שגיאה)
יש בלוגיקה זו אריכות דברים ועומק (בהזדמנות ארחיב על כך בל"נ) רק אומר בקצרה -שהרעיון והאלגוריתם באימות נתונים בנויים כך-שכל עוד התא ריק ובעל הגיליון נשאר "בחיבוק ידיים"("בשב ואל תעשה...") .זה בסדר ותקין.אבל אם בעל הגיליון החליט לכתוב משהו,("בקום ועשה..")כאן האקסל "מתעורר ולא מוכן לקבל" ערך שאינו מותאם לכללי האימות.
מקווה שהובנתי...
צפה בקובץ המצורף 1682416

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

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

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

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

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

 

[יעקב ויצמן]

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

צפה בקובץ המצורף 1683001

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

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

צפה בקובץ המצורף 1683004

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

 

[זהבה ו-3 הדובים]

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

 

[יעקב ויצמן]

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

סודוקו אלכסון, אקסל ומה שביניהם ▪︎ ואיך זה קשור לרמזי האות א'?

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

www.prog.co.il

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

 

[הילוך גבוה]

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

זו לא היתה הנקודה, זה ברור

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

פחות אסתטי

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

וואלה!

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

שוב, האסתטיקה, לכן הייתי בוחר באפשרות של דחיית הקלט
שוב, תודה רבה לך

 

[הילוך גבוה]

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

 

[יעקב ויצמן]

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

כן.אלו הפונקציות שמותאמות לת.ז שנכתבת בתא A2

=MOD(MID(REPT(0,9-LEN(A2))&A2,1,1)+MID(“0246813579”,MID(REPT(0,9-LEN(A2))&A2,2,1)+1,1)+MID(REPT(0,9-LEN(A2))&A2,3,1)+MID(“0246813579”,MID(REPT(0,9-LEN(A2))&A2,4,1)+1,1)+MID(REPT(0,9-LEN(A2))&A2,5,1)+MID(“0246813579”,MID(REPT(0,9-LEN(A2))&A2,6,1)+1,1)+MID(REPT(0,9-LEN(A2))&A2,7,1)+MID(“0246813579”,MID(REPT(0,9-LEN(A2))&A2,8,1)+1,1)+MID(REPT(0,9-LEN(A2))&A2,9,1),10)=0

ההסבר לכך מצורף בלינק כאן
בהצלחה לך ידידי!

 

[יעקב ויצמן]

יש לי מסמך

מסמך אקסל?

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

תראי את המדריך בלינק המצורף כאן
זה נקרא "מיזוג דואר"
האם לזה התכוונת?

 

[זהבה ו-3 הדובים]

מסמך אקסל?

תראי את המדריך בלינק המצורף כאן
זה נקרא "מיזוג דואר"
האם לזה התכוונת?

את זה הכרתי וזה מה שעשיתי בסוף
רציתי משהו קל יותר

 

[יעקב ויצמן]

את זה הכרתי וזה מה שעשיתי בסוף

מצוין

רציתי משהו קל יותר

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

 

[זהבה ו-3 הדובים]

מצוין

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

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

 

[יעקב ויצמן]

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

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

 

[יעקב ויצמן]

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

הערה קטנה.אדייק את דבריי filter יכולה להועיל לטבלאות סטטיות בלבד.
בטבלאות דינמיות יש להשתמש בvstack הנפלאה(זמינה בשיטס או באופיס 365 באקסל).filter אינה רלוונטית במקרה כזה, כי יוחזרו שגיאות spill באקסל /ref בשיטס
עוד דבר חשוב ששכחתי להזכיר הוא שיש לעטוף את פונקציית vstack עם filter לצורך "דילוג" וסינון לכל התאים הריקים במערך.על ידי תנאי של ""<> כלומר אינו שווה לריק.כאשר ציינתי והדגמתי מספר פעמים בפורום זה.
הנה דוגמא בלינק כאן
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13661495
@זהבה ו-3 הדובים

 

[ה. שלמה]

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

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

השתמשתי עם פונקציית countif על קובץ אקסל עם הנתונים ההיסטוריים שמופיע באתר R הון.

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

 

[יעקב ויצמן]

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

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

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

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

 

[הילוך גבוה]

@יעקב ויצמן אני רוצה לרכז נתונים של כמה וכמה גיליונות בשיטס לגיליון אחד (הכל באותו קובץ)
לפי הקובץ דוג' נגיד שאני רוצה לרכז את כל השמות שכתובים בגיליון 1 2 ו3 לגיליון 4 (אפילו כפולים)
אבל זאת בתנאי שבעמודות A ו B יש את הספרה אחד
זה לא אקסלוקפ, היות ואין לי נתון שלפיו מושכים נתונים, אלא שבאופן דינמי כל שורה שהתנאים בו מתקיימים לא משנה באיזה גיליון הוא נמצא ולא משנה האם זה כפילו, הוא יביא אותו בגיליון 4 ברשימה אחת

 

[הילוך גבוה]

@יעקב ויצמן אני רוצה לרכז נתונים של כמה וכמה גיליונות בשיטס לגיליון אחד (הכל באותו קובץ)
לפי הקובץ דוג' נגיד שאני רוצה לרכז את כל השמות שכתובים בגיליון 1 2 ו3 לגיליון 4 (אפילו כפולים)
אבל זאת בתנאי שבעמודות A ו B יש את הספרה אחד
זה לא אקסלוקפ, היות ואין לי נתון שלפיו מושכים נתונים, אלא שבאופן דינמי כל שורה שהתנאים בו מתקיימים לא משנה באיזה גיליון הוא נמצא ולא משנה האם זה כפילו, הוא יביא אותו בגיליון 4 ברשימה אחת

שכחתי לצרף את הגיליון

 

[יעקב ויצמן]

@הילוך גבוה שאלתך שאלה יפה ומעניינת..
מצורף כאן גיליון מתוקן.
כמה מילים על מה שמתרחש פה:
ובכן.פתחתי גיליון נוסף ובו כצעד ראשון הערמתי (מלשון ערימה) את כל עמודות a:c מכל הגיליונות על ידי פונקציית vstack הנפלאה שהזכרתי אותה בעבר פעמים רבות בפורום זה.תוך כדי שימת דגש על סינון כל התאים הריקים על ידי פונקציית filter העוטפת את כל הפונקצייה ובה תנאי ""<> כלומר אינו שווה לריק.
וכך סדר הפונקציות בתא a2

=FILTER(VSTACK('גיליון1'!A1:C,'גיליון2'!A1:C,'גיליון3'!A1:C),VSTACK('גיליון1'!A1:A,'גיליון2'!A1:A,'גיליון3'!A1:A)<>"")

כך נראית התוצאה (בשטח מילוי בירוק)

עמודות שלב זה ניתנות להסתרה ללא חשש.
שלב ב:
מכאן הדרך פשוטה וקלה.ערכתי בתא e2 פונקציית filter עם 2 תנאים (כמו אפקט פונקציית and) התנאים שיהיה כתוב הספרה 1 גם בעמודה a וגם בעמודה b

=FILTER(A2:C,A2:A=1,B2:B=1)

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

סה טו!
בהצלחה לך ידידי היקר!

מצורף כאן גיליון שיטס מתוקן

 

[טראמפ 2024]

יש לי בעיה בgoogle sheets.

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

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