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

[יעקב ויצמן]

נניח שלקחתי הלוואה של 400 שקל לארבעה חודשים צמודת מדד ובחודש הראשון שילמתי 105 ובשני 110 ובשלישי 112 וברביעי 116
איך אני מחשב כמה ריבית שילמתי סכ"ה על ההלוואה?

תראה בגיליון המצורף
בשטח הצהוב

 

[יעקב ויצמן]

איך אפשר לבנות קטלוג בגוגל שיטס?

ראה כאן

 

[סקרן בקטנה]

תראה בגיליון המצורף
בשטח הצהוב

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

4.145%

היא מה הנוסחא המתמטית
בעצם זה לא שאלה באקסל אלא סתם מתמטיקה

 

[עניין]

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

 

[בייסי]

ראה כאן

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

 

[יעקב ויצמן]

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

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

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

 

[יעקב ויצמן]

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

הדף תורגם אוטומטית

 

[יעקב ויצמן]

באמצעות indirect
זו משימה בלתי אפשרית
בצילום מסך המצורף תא d1 מוזן מאימות נתונים בתא a12
כאשר תא a12 מוזן מנתונים מתאים a1 עד a10
תא d1 יציג את התוצאות כמו המקור הראשון.כפי שהדגשתי שוב ושוב לעיל.
והואיל וברשימה המקורית הפריסה היא מa1 עד a10
חייבים לתת "ספייס" ושטח ריק מתחת לתא d1
על מנת לאפשר להציג את הנתונים בפריסה.
אם קיימת חסימה בדרך .תחטוף הודעת שגיאה.
לשבר את האוזן זה כמו שקיימת חניה פרטית לסמי טריילר .ונכנסו שם מכוניות פרטיות ותפשו לו את השטח.בעלי המכוניות היו אדיבים והותירו שטח פנוי של 2 מטר.נו מה יעשה נהג הסמי טריילר עם השטח הקטן הזה?
יצפור להם שיפנו את השטח!
זה בדיוק מה שהאקסל מתריע על הודעת שגיאה פה.זו צפירה לפנות את השטח!
מציאות של "לעייל פילא בקופא דמחטא" קיימת רק באימות נתונים סטנדרטי.אימות נתונים על אלגוריתם מתוחכם ויחודי וככה הוא בנוי.
צפה בקובץ המצורף 1333036
ההצעה לפיתרון לא רלוונטית בתא d1 בצילום מסך הנל

עברו מספר ימים ולאחר סיעור מוחות מתמשך בנושא זה. @שימי .ב מצא פתרון לבעיה שציינתי פה.
אני רק מעתיקו על הכתב ואומר דבר בשם אומרו(לזרז את גאולתינו ופדיון נפשינו..)
הפיתרון הוא -להקיף את פונקציית indirect בפונקציית transpose (תפקידה להפוך נתוני תאים ממאוזן למאונך וכן ההיפך)
כעת. בתא d1 האקסל כבר לא יתריע "בצפירה" כי נתוני המקור מוצגים במאוזן ולא במאונך.
פתרון יפה ומושלם לבעייה שהיתה כאן.
מצורף כאן צילום מסך.
ישר כח לך.החכמת אותי ואת כולנו.
יגעת ומצאת תאמין!!!

 

[בייסי]

הדף תורגם אוטומטית

תודה רבה!
אלמד את המסמך, ואעדכן!

 

[נבחר]

מספר שאלות:
1. כיצד ניתן לעשות עיצוב מותנה-ערכים כפולים אבל שיהיה ממספר עמודות, לדוג' שרק נתון שמופיע בכל העמודות המסומנות יופיע ולא מה שמופיע רק בחלק מהעמודות? (לדוג' כשאני רוצה לדעת מי מהתלמידים התמיד ועשה מבחן כל שבוע, מי ברוב השבועות ומי רק בחצי...)
2. בפונקציה סכום ביניים - ספירה, כיצד ניתן להגדיר שהסיכום יופיע רק עם שם הנתון והתוצאה ללא המילה "ספירה"?
לדוג' כשאני רוצה לדעת כמה בחורים יש בכל ישיבה הוא רושם לי: "חברון ספירה" תפרח ספירה" וכדו'?
3. יש לי מספר רשימות של בחורי ישיבה מאותה ישיבה (לדוג'), באחת מופיע 200 בחורים, באחת 150 (מתוכם) ובאחת מאה (מתוכם). אני רוצה שאוטומטית כל השמות הזהים שמופיעים בכל הרשימות יהיו באותה שורה. יש פונקציה כזאת?
תודה מראש וסליחה על ריבוי השאלות...

 

[משה שולמן]

איך אני מגדיל טבלה של A4 באקסל שיהיה על כל הדף

 

[יעקב ויצמן]

איך אני מגדיל טבלה של A4 באקסל שיהיה על כל הדף

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

 

[נבחר]

מספר שאלות:
1. כיצד ניתן לעשות עיצוב מותנה-ערכים כפולים אבל שיהיה ממספר עמודות, לדוג' שרק נתון שמופיע בכל העמודות המסומנות יופיע ולא מה שמופיע רק בחלק מהעמודות? (לדוג' כשאני רוצה לדעת מי מהתלמידים התמיד ועשה מבחן כל שבוע, מי ברוב השבועות ומי רק בחצי...)
2. בפונקציה סכום ביניים - ספירה, כיצד ניתן להגדיר שהסיכום יופיע רק עם שם הנתון והתוצאה ללא המילה "ספירה"?
לדוג' כשאני רוצה לדעת כמה בחורים יש בכל ישיבה הוא רושם לי: "חברון ספירה" תפרח ספירה" וכדו'?
3. יש לי מספר רשימות של בחורי ישיבה מאותה ישיבה (לדוג'), באחת מופיע 200 בחורים, באחת 150 (מתוכם) ובאחת מאה (מתוכם). אני רוצה שאוטומטית כל השמות הזהים שמופיעים בכל הרשימות יהיו באותה שורה. יש פונקציה כזאת?
תודה מראש וסליחה על ריבוי השאלות...

@יעקב ויצמן אתה יכול לעזור לי?

 

[יעקב ויצמן]

מספר שאלות:
1. כיצד ניתן לעשות עיצוב מותנה-ערכים כפולים אבל שיהיה ממספר עמודות, לדוג' שרק נתון שמופיע בכל העמודות המסומנות יופיע ולא מה שמופיע רק בחלק מהעמודות? (לדוג' כשאני רוצה לדעת מי מהתלמידים התמיד ועשה מבחן כל שבוע, מי ברוב השבועות ומי רק בחצי...)
2. בפונקציה סכום ביניים - ספירה, כיצד ניתן להגדיר שהסיכום יופיע רק עם שם הנתון והתוצאה ללא המילה "ספירה"?
לדוג' כשאני רוצה לדעת כמה בחורים יש בכל ישיבה הוא רושם לי: "חברון ספירה" תפרח ספירה" וכדו'?
3. יש לי מספר רשימות של בחורי ישיבה מאותה ישיבה (לדוג'), באחת מופיע 200 בחורים, באחת 150 (מתוכם) ובאחת מאה (מתוכם). אני רוצה שאוטומטית כל השמות הזהים שמופיעים בכל הרשימות יהיו באותה שורה. יש פונקציה כזאת?
תודה מראש וסליחה על ריבוי השאלות...

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

יצרתי טבלה קטנה עם סימוני כן למי שהשלים מבחן.יש כאן 3 מבחנים בעמודות b.c.d
בעמודה e קיים פילוח למי שעבר בהצלחה (השלים 3 מבחנים) או לא עבר.
הפונקצייה counta (ולא count לא להתבלבל) סופרת כמה תאים מכילים טקסט בשורה.וכל זה מוקף בif שהקריטריון הוא 3.במצב טרו הif מחזיר "עבר בהצלחה".
(מצב טרו ופולס הפוכים כאן.זה קורה ב if ובפרט בגיליון שיטס....)
שמעון היחיד בדוגמא שלנו שהשלים 3 מבחנים.
באותה שיטה ניתן לקיים פילוחים נוספים וניתוחים סטטיסטים כיד הדימיון ולפי הצורך.
העיצוב מותנה שהוחל פה הוא פשוט למדי .הכלל הוא שהטקסט יכיל את המילה "עבר בהצלחה".
וזה כל הסיפור.
מצורף כאן גם גיליון שתראה מקרוב את הפונקציה
לגבי שאלה 2
השאלה לא ברורה דיו.
האקסל מציע מספר חישובים אוטומטים בכל סימון תא/טווח.חלקם הם "ספירה" ו"ספירה מספרית"
ללא צורך להזין שום דבר ושום פונקצייה.

שים לב בדוגמא הקטנטונת שהכנתי .תוצאת "ספירה" היא 8 כמו התוצאה שהיתה בהזנה ישירה של הפונקצייה counta
"ספירה מספרית" היא 5 ,וזו בדיוק התוצאה לו היתה כאן הזנה ישירה של הפונקציייה count.
טווח הכתום המסומן הוא a1:a10
רשמתי כותרת דוגמא.להראות שזה(שם הכותרת) לא מוצג בסיכום האוטומטי.
כך שטרם הבנתי את השאלה ברור ודיו.
אם התשובה פה לא כיוונה לשאלה.אבקש לשלוח גיליון דוגמא עם יותר פירוט בשאלה.
באופן כללי .אין לייחס לסכום אוטומטי משמעויות מרחיקות לכת... זה נותן עזר לחישובים מהירים ולחלק מצומצם מאוד.
כל חישוב שנרצה שישאר ויתועד בגיליון -יש להזינו בפונקצייה מותאמת.אז באמת יש מקום לכל כך המון חישובים ומניפולציות שניתן לעשות.
לגבי שאלה 3
אתן לך דוגמא קטנה ביותר ותבין למה לוגית זה לא ניתן ולא אפשרי אם הבינותי נכון את שאלתך

שים לב שבעמודות a,b קיימת טבלה שמכילה שם ועיר.שניהן לא ממוינות בכלל.
השתמשתי בפונקציית sort לצורך המיון מא ועד ת.
בארגומנט השני sort_column
(האינדקס של הטווח-מהי העמודה למיון)
סימנתי 1.בטבלת מקור זו עמודה a הראשונה מימין.
קיבלנו מיון .שים לב שאברהם הוא מופיע עכשיו ראשון.האקסל "יודע" מה התאים (הרבה ככל שיהיו) היו צמודים אליו בטבלת המקור לאורך כל השורה. ואותם הוא מציג כעת לאחר המיון משמאל לתא של אברהם.(או תא מסוים בעמודה x כמו בפונקציית vlookup למשל)
(חייב להבין את הרציונאל פה.טמונה פה לוגיקה עצומה .וזה חוק בל יעבור טמוע ומושרש באלגוריתם של האקסל)
אברהם במקרה שלנו גר בגדרה (זו האמת לאמיתה)
בתא e2 יהיה כתוב גדרה ולא כמו תא b2 (ירושלים)
שמבינים את העיקרון.תבין שבטבלה שבכל עמודה יש ערכים שונים מעמודות האחרות והן לא זהות אחת לאחת.
כל ניסיון מיון -יחול על עמודה אחת (שתבחר)בלבד והאקסל "יזיז" את כל הערכים בתאים שבכל השורות כמו לפני המיון.
זה לא משנה אם נשתמש בפונקציית filter או sort או מיון בסרגל הכלים או בסנן כזה ואחר.
מצרף לך גיליון של המיון עם sort
נ.ב הדוגמאות הנל הם באמת לשבר את האוזן ואת המחשבה ולהבין את העקרונות.אם הכל ברור לך תיישם את הטכניקות בטבלאות שלך בקלות.
מקווה שהסברתי את עצמי דיו.ומקווה שעזרתי.
אם משהו לא ברור או שפיספסתי משהו .אשמח לשמוע
בהצלחה

 

[נבחר]

@יעקב ויצמן תודה, עזרת לי מאד
מה שרק חסר לי בתשובה הראשונה, זה שאין לי רשימה מסודרת שכל שם של תלמיד מופיע בשורה משלו עם פירוט.
אלא יש רשימה אחת של 950 תלמידים, רשימה שניה של 800 (מתוך ה900) ורשימה שלישית של 700 (כנ"ל). ולכן האופציה היחידה לדעת מי נמצא בכל הרשימות היא ע"י חיפוש ערכים כפולים.
בנושא השאלה השניה - גם אותה בעיה, יש לי רשימה (אחת ארוכה) של 1000 תלמידים מ150 מוסדות, והדרך שלי לדעת כמה תלמידים יש בכל מוסד הוא ע"י פונקציית סכום ביניים-ספירה, רק שאז מופיע לי סיכום עם הנתון: "ישיבת אביי ספירה - 15, ישיבת רבא ספירה - 16" וזה שאלתי איך ניתן למנוע את המילה "ספירה" (כי אני עושה העתק הדבק של כל הנתונים לטבלה חדשה מסודרת...)
מקוה שהובנתי, תודה.

 

[nevertheless]

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

 

[אפר]

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

למה נראה לך שאפשר?
זה אקסל אונליין?

 

[יעקב ויצמן]

@יעקב ויצמן תודה, עזרת לי מאד
מה שרק חסר לי בתשובה הראשונה, זה שאין לי רשימה מסודרת שכל שם של תלמיד מופיע בשורה משלו עם פירוט.
אלא יש רשימה אחת של 950 תלמידים, רשימה שניה של 800 (מתוך ה900) ורשימה שלישית של 700 (כנ"ל). ולכן האופציה היחידה לדעת מי נמצא בכל הרשימות היא ע"י חיפוש ערכים כפולים.
בנושא השאלה השניה - גם אותה בעיה, יש לי רשימה (אחת ארוכה) של 1000 תלמידים מ150 מוסדות, והדרך שלי לדעת כמה תלמידים יש בכל מוסד הוא ע"י פונקציית סכום ביניים-ספירה, רק שאז מופיע לי סיכום עם הנתון: "ישיבת אביי ספירה - 15, ישיבת רבא ספירה - 16" וזה שאלתי איך ניתן למנוע את המילה "ספירה" (כי אני עושה העתק הדבק של כל הנתונים לטבלה חדשה מסודרת...)
מקוה שהובנתי, תודה.

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

אתה צריך לעשות העתק לכל הרשימות של התלמידים.ולהדביק אותם בצורה מסודרת רשימה מתחת רשימה.כולן בעמודה אחת חדשה.כפי שעשיתי בצילום מסך בעמודה B
אתה יכול לעשות את זה אי שם בקצה הגיליון היכן שרק תבחר .
בעמודה הצמודה משמאל יש להזין countif כפי שמוצג בצילום מסך מתא c1.
לא לשכוח את הקיבוע בארגומנט הראשון range.זה הכרחי וחשוב מאוד!!!
ואז לגרור עם ידית המילוי עד סיום הרשימה בעמודה שמימין.
מה שיופיע לך .זה כמה פעמים מוזכר כל שם.
אחר כך תעשה עיצוב מותנה הכי פשוט שבעולם- של כלל "ערך גדול מ- 1".כפי שמופיע בגיליון דוגמא שעשיתי.
יש עוד כמה פתרונות עם vlookup או match בשילוב if.אבל זה לא המקרה המדויק שלך ואין בכך נפקא מינה כעת.
לגבי השאלה השניה
איני יודע עדיין איך זה נראה בפועל .אבל אגש ישירות לפיתרון בשורה תחתונה.
בהנחה שקיבלת רשימות בעמודות עם המילה ספירה.
כל שעליך לעשות הוא כדוגמת מה שעשיתי פה עם פונקציית substitute

הפונקצייה ממירה טקסט x בטקסט y
זה פתרון קסם לכל צורך מסוג זה.מבלי להיכנס לחשבונות למה הכתוב נכתב והאמור נאמר בטבלת המקור..
בתא d2 כתבתי את הפונקצייה .השינוי הוא חל על הטקסט בתא a1.
מה שנותר, זה למשוך את ידית המילוי שמאלה ולמטה כמידת הטבלה המקורית.
את הטבלה החדשה הזו אתה מבצע לה גזור ומדביקה בגיליון המעוצב והמהודר שאתה מכין.שים לב לבצע הדבקת ערכים בלבד!!!
השתמשתי בכוונה תחילה בפונקציית substitute ולא בחברתה replace .יש בה יתרון מובהק במקרה שלך ובכלל.אולי בהזדמנות אכתוב על כך.
בדוגמא החלפתי את המילה ישיבה בכלום/ריק.
( הישיבות נשארו ללא התלמידים.העיקר שהתנאים והאמוראים-ראשי הישיבות נשארו..זכותם תהא מגן עלינו)
זו רק דוגמא כמובן.ההחלפה תבוצע בכל טקסט שיכתב,ושימצא כתוב.
כמנהגי תמיד.אצרף כאן את הגיליונות כדוגמא.תבין את העקרונות והטכניקות ותיישם אותן בטבלה שלך.
בהצלחה

 

[יעקב ויצמן]

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

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

 

[נבחר]

ערב טוב @נבחר
הבנתי. אני אציע לך פתרונות לצורך השעתי שלך כפי שאפרט..
אני מניח שיש ענין זמני כעת לבצע פילוח בנתונים.ולכן הפתרון הוא לא יבוצע בטבלה בדינמיות אוטומטית בעתיד.
אם תרצה לבצע חישוב כזה בעתיד תבצע שוב את אותן פעולות בדיוק בגיליון.
לגבי השאלה הראשונה
שים לב למה שעשיתי פהצפה בקובץ המצורף 1337701
אתה צריך לעשות העתק לכל הרשימות של התלמידים.ולהדביק אותם בצורה מסודרת רשימה מתחת רשימה.כולן בעמודה אחת חדשה.כפי שעשיתי בצילום מסך בעמודה B
אתה יכול לעשות את זה אי שם בקצה הגיליון היכן שרק תבחר .
בעמודה הצמודה משמאל יש להזין countif כפי שמוצג בצילום מסך מתא c1.
לא לשכוח את הקיבוע בארגומנט הראשון range.זה הכרחי וחשוב מאוד!!!
ואז לגרור עם ידית המילוי עד סיום הרשימה בעמודה שמימין.
מה שיופיע לך .זה כמה פעמים מוזכר כל שם.
אחר כך תעשה עיצוב מותנה הכי פשוט שבעולם- של כלל "ערך גדול מ- 1".כפי שמופיע בגיליון דוגמא שעשיתי.
יש עוד כמה פתרונות עם vlookup או match בשילוב if.אבל זה לא המקרה המדויק שלך ואין בכך נפקא מינה כעת.
לגבי השאלה השניה
איני יודע עדיין איך זה נראה בפועל .אבל אגש ישירות לפיתרון בשורה תחתונה.
בהנחה שקיבלת רשימות בעמודות עם המילה ספירה.
כל שעליך לעשות הוא כדוגמת מה שעשיתי פה עם פונקציית substitute
צפה בקובץ המצורף 1337718
הפונקצייה ממירה טקסט x בטקסט y
זה פתרון קסם לכל צורך מסוג זה.מבלי להיכנס לחשבונות למה הכתוב נכתב והאמור נאמר בטבלת המקור..
בתא d2 כתבתי את הפונקצייה .השינוי הוא חל על הטקסט בתא a1.
מה שנותר, זה למשוך את ידית המילוי שמאלה ולמטה כמידת הטבלה המקורית.
את הטבלה החדשה הזו אתה מבצע לה גזור ומדביקה בגיליון המעוצב והמהודר שאתה מכין.שים לב לבצע הדבקת ערכים בלבד!!!
השתמשתי בכוונה תחילה בפונקציית substitute ולא בחברתה replace .יש בה יתרון מובהק במקרה שלך ובכלל.אולי בהזדמנות אכתוב על כך.
בדוגמא החלפתי את המילה ישיבה בכלום/ריק.
( הישיבות נשארו ללא התלמידים.העיקר שהתנאים והאמוראים-ראשי הישיבות נשארו..זכותם תהא מגן עלינו)
זו רק דוגמא כמובן.ההחלפה תבוצע בכל טקסט שיכתב,ושימצא כתוב.
כמנהגי תמיד.אצרף כאן את הגיליונות כדוגמא.תבין את העקרונות והטכניקות ותיישם אותן בטבלה שלך.
בהצלחה

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