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

[יעקב ויצמן]

משום "אל תמנע טוב מבעליו" ולכבוד חודש אדר שמרבים בו בשמחה.(ולחובבי אקסל אקסטרים זה בהחלט גאונות שמשמחת את הלב)
אוסיף כאן עוד דרך מצוינת של סיכום עמודה עם מספר תנאים בפונקציה קצרה.תוך כדי שימוש ב3 פונקציות
sum
arrayformula
sumif
וסוגריים מסולסלים (תפקידם העיקרי לשמש "כבנק" תאים/טקסטים שניתן "לשלוף" מכל אחד מהם.מוכר וידוע גם בעולם התיכנות )
מצורף גיליון עם 3 דרכים לפיתרון עם אותה תוצאה.
תהנו!

 

[יהודי נחמד]

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

 

[יעקב ויצמן]

שלום לכולם, אשמח אם מישהו יוכל לעזור לי,
אני צריך להכין טבלת אקסל שתחשב לבד מלגות שכר לפי ציון במבחן,
מצורף דוגמא של הטבלא, איך אני עושה שבעמודה C יהיה חישוב אוטומטי, למשל מי שקיבל 85 עד 100 מקבל 80 שקל, ומי שמקבל 70 עד 84 מקבל 60.צפה בקובץ המצורף 1323227

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

 

[בחיפוש]

שאלה:
אני כותב קובץ ניהול מלאי ועשיתי כבר ממשק נוח להוספת וגריעת חומרים מהמלאי (בעזרה גדולה של @אש קודש! ).
אני מעוניין בעוד 2 דברים:
1. ליצור נוסחאות שיגרעו אוטומטית מוצרים מהמלאי.
לדוגמא: יש לי פיצריה ויש לי אפשרות להוסיף/לגרוע קמח/רסק/גבינה/זיתים וכו' אז במקום שעל כל מגש אצטרך להשתמש בפונקציית הגריעה כמה פעמים פעם אחת לקמח פעם לגבינה וכו' פשוט שיהיה לי טופס שבו אבחר איזה מוצר הכנתי והוא ידע לחשב מה וכמה צריך לגרוע מהמלאי.
2. אני רוצה גם ליצור ממשק קל (טופס) להוסיף עוד מוצר עם מרכיביו וכמויותיהם לדוגמא: מחר אני משיק פיצה מוצרלה אז שלא אצטרך לכתוב טבלה וכו' אלא פשוט אזין בטופס הוספת מוצר לרשימה: שם מוצר, מרכיב א=x כמות =y מרכיב ב =z וכו' (צריך בשביל זה בטופס כפתור שיתן להוסיף רכיבים כרצוני ושלא יהיה מוגבל למספר רכיבים מסויים) וכשאלחץ על שמור הוא כבר יכניס לבסיס הנתונים את מה שצריך על מנת שכשארצה להכין מגש מוצרלה פשוט אבחר הכנת מגש מוצרלה והוא יגרע לי לבד את הרכיבים.
מצ"ב טבלה לדוגמה כיום:

 

[יהודי נחמד]

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

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

מודה לך מאוד מאוד.

אני צריך עוד עזרה, אשמח אם תוכל אתה או כל מי מהצדיקים שעוזרים כאן שיעזרו לי עם נוסחה נכונה עבור זה.
אני עושה שני מבחנים בחודש,על כל מבחן אני מעניק מלגה מסוימת:
65 עד 74 = 35 שקל למבחן
75 עד 84 = 40 שקל למבחן
85 עד 100 = 50 שקל למבחן.
איך אני מחשב את זה כפול 2 זאת אומרת אני יכתוב משבצת ליד משבצת את הציון של המבחן הראשון ושל השני, ואני רוצה שיצא לי התוצאה על שני המבחנים במשבצת השלישית.
ובנוסף, אם הממוצע של שני המבחנים מעניק סכום גבוה יותר, אז המלגה תהיה כסכום הגבוה.
למשל אם הממוצע הוא 70 אז הוא יקבל 70 שקל,
דוגמא נוספת אם קיבל במבחן אחד 50 ובשני 70 הממוצע הוא נמוך מהנדרש אז יקבל רק 35 על המבחן השני.
מקווה שהייתי ברור,

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

 

[יעקב ויצמן]

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

מודה לך מאוד מאוד.

אני צריך עוד עזרה, אשמח אם תוכל אתה או כל מי מהצדיקים שעוזרים כאן שיעזרו לי עם נוסחה נכונה עבור זה.
אני עושה שני מבחנים בחודש,על כל מבחן אני מעניק מלגה מסוימת:
65 עד 74 = 35 שקל למבחן
75 עד 84 = 40 שקל למבחן
85 עד 100 = 50 שקל למבחן.
איך אני מחשב את זה כפול 2 זאת אומרת אני יכתוב משבצת ליד משבצת את הציון של המבחן הראשון ושל השני, ואני רוצה שיצא לי התוצאה על שני המבחנים במשבצת השלישית.
ובנוסף, אם הממוצע של שני המבחנים מעניק סכום גבוה יותר, אז המלגה תהיה כסכום הגבוה.
למשל אם הממוצע הוא 70 אז הוא יקבל 70 שקל,
דוגמא נוספת אם קיבל במבחן אחד 50 ובשני 70 הממוצע הוא נמוך מהנדרש אז יקבל רק 35 על המבחן השני.
מקווה שהייתי ברור,

ממש תודה רבה מראש. זה יהיה עזרה ענקית עבורי!!
מצורף צילום של האקסל לדוגמאצפה בקובץ המצורף 1323717

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

 

[יהודי נחמד]

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

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

 

[high light]

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

ניסיתי. הגעתי לחלונית הזו:
בחרתי [ע''י f5] את העמודה שמכילה 0
וכאן צריך לעשות הפעל
לא מצאתי איפה מפעילים....
@יעקב ויצמן תודה!

 

[יעקב ויצמן]

ניסיתי. הגעתי לחלונית הזו:
בחרתי [ע''י f5] את העמודה שמכילה 0
וכאן צריך לעשות הפעל
לא מצאתי איפה מפעילים....
@יעקב ויצמן תודה!
צפה בקובץ המצורף 1324554

אולי לבדוק בקובץ>אפשרויות>הגדרות אבטחה>רמות הגנה ללחוץ על האפשרןת השניה ואז ok
ואז לנסות להריץ שוב עם f5

 

[high light]

הצלחתי!
תודה
2 שאלות נוספות:
1. האם זה יוכל לעבוד גם בגוגל שיטס?
2. האם אפשר שהשורות שיתווספו אוטומטית יכללו כיתוב שמסוים בעמודה B?

 

[יעקב ויצמן]

הצלחתי!
תודה
2 שאלות נוספות:
1. האם זה יוכל לעבוד גם בגוגל שיטס?
2. האם אפשר שהשורות שיתווספו אוטומטית יכללו כיתוב שמסוים בעמודה B?

שמח לשמוע.ובשמחה
1.לדעתי לא.בכל הקשור למאקרו השיטס לא מתרגם פקודות אקסל.האלגוריתם שונה.צריך להדביק את הפקודה דרך google script
2.יש בלינק שצרפתי בהמשכו פקודה דומה.בא נגיד ככה.שאחרי שמתווספים עמודה או שורה ריקה אפשר להתמודד עם זה גם בפונקציות רגילות .if+len+counta ועוד מיני ממתקים שכאלה..

 

[יהודי נחמד]

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

אשמח לעזרה....
@יעקב ויצמן המתחיל במצווה....

 

[יעקב ויצמן]

אשמח לעזרה....
@יעקב ויצמן המתחיל במצווה....

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

 

[יעקב ויצמן]

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

ערב טוב @יהודי נחמד
הבטחתי שאכתוב היום .אז אני גם אקיים.
אתחיל בזה שבאינסטיקנט ראשוני האצבעות דיגדגו להקליד =xlookup (זו- התאומה לא זהה של vlookup "מלכת האקסל") אבל היה צורך פה לבצע שינויים בסדר הטבלה .והמלל בפונקציה/ות היה רב ומסורבל.אז ויתרתי על האופציה הזו.
השתמשתי בפונקציות מוכרות אבל עם תחכום.לאחר הרבה ניסיונות וקומבינציות.
על קצה המזלג אסביר מה שמתרחש בחישובים כאן:
בעמודה e אני אומר לאקסל שיבדוק אם הרף של הציון במבחן 1 בעמודה b לא עובר את רף ציון 65.(אם כן.אז יוחזר לי false.פולס כזה יוצג בפועל בעמודה e)
אז יבדוק את המבחן השני בעמודה c ויכתוב לי את המלגה על סמך מבחן אחד.אם יש סכום בעמודה e זה בגלל שישנה זכאות במבחן 2.אם התקבל false false בתא בעמודה e זה בגלל שהמבחן השני בעמודה d מזכה למלגה על סמך מבחן בודד.
בעמודה e זה אותו פורמט של פונקציה אבל ההצלבה הפוכה במקום b זה c .ובמקום e זה יהיה f.

כאן נבוא לשאלה.
מה בעצם קורה שאנו מוצאים false בעמודה e וגם בf?
זה אומר ששני המבחנים עברו את רף ציון 65.והופה..מגיע מלגה מחישוב הממוצעים שמחושב בנפרד בעמודה d.
זה מוביל לתיחכום שמסתתר לו בעמודה g בחישוב ה-total .
יש תנאי שאם count של שתי העמודות הוא 0.(זה קורה רק במצב ששני המבחנים עברו את רף 65 ואז כתוב פעמיים false .זוכרים נכון?) ולמה 0.?כי count יודעת לספור תאים רק עם מספרים .ומתעלמת מטקסטים.(זו הסיבה שהפונקציה הזו נבחרה בקפידה.לעומת אחותה למשפחה- counta שכן מכירה וסופרת גם תאים עם טקסט)
אז הממוצע יחושב כמו בעמודה d החישוב לפי ממוצע.ואם התוצאה לא 0 סימן שיש סכום אחד בעמודה e או f זה אומר לנו שאחד המבחנים לא עבר .ולכן ההמשך זה לסכם את הסכומים מעמודות e וf.
סה טו...
סורי על האריכות.חרגתי קצת ממנהגי.אבל השקפת עולמי שאת הפתרונות באקסל יש להבין .רק אז נהנים באמת מהיופי וההדר וככה .וזו רק דוגמא.
זכרו.שאת האקסל יש "ללמוד".וכמו שאמרו חזל יגעת ומצאת -תאמין

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

 

[Cהרשי]

שאלה: אם אני רוצה לדוג' חודש "ינואר" 50 שורות, וכן חודש פברואר 50 שורות. איך אני עושה את זה אוטומטי.?.
תודה

 

[יעקב ויצמן]

שאלה: אם אני רוצה לדוג' חודש "ינואר" 50 שורות, וכן חודש פברואר 50 שורות. איך אני עושה את זה אוטומטי.?.
תודה

כמו בדוגמא בעמודה a או תאריך עוקב כמה עמודה b?

 

[אש קודש!]

שאלה:
אני כותב קובץ ניהול מלאי ועשיתי כבר ממשק נוח להוספת וגריעת חומרים מהמלאי (בעזרה גדולה של @אש קודש! ).
אני מעוניין בעוד 2 דברים:
1. ליצור נוסחאות שיגרעו אוטומטית מוצרים מהמלאי.
לדוגמא: יש לי פיצריה ויש לי אפשרות להוסיף/לגרוע קמח/רסק/גבינה/זיתים וכו' אז במקום שעל כל מגש אצטרך להשתמש בפונקציית הגריעה כמה פעמים פעם אחת לקמח פעם לגבינה וכו' פשוט שיהיה לי טופס שבו אבחר איזה מוצר הכנתי והוא ידע לחשב מה וכמה צריך לגרוע מהמלאי.
2. אני רוצה גם ליצור ממשק קל (טופס) להוסיף עוד מוצר עם מרכיביו וכמויותיהם לדוגמא: מחר אני משיק פיצה מוצרלה אז שלא אצטרך לכתוב טבלה וכו' אלא פשוט אזין בטופס הוספת מוצר לרשימה: שם מוצר, מרכיב א=x כמות =y מרכיב ב =z וכו' (צריך בשביל זה בטופס כפתור שיתן להוסיף רכיבים כרצוני ושלא יהיה מוגבל למספר רכיבים מסויים) וכשאלחץ על שמור הוא כבר יכניס לבסיס הנתונים את מה שצריך על מנת שכשארצה להכין מגש מוצרלה פשוט אבחר הכנת מגש מוצרלה והוא יגרע לי לבד את הרכיבים.
מצ"ב טבלה לדוגמה כיום:

אולי אם תכתוב לי נתונים מדויקים, אוכל לעשות זאת.

 

[Cהרשי]

כמו בדוגמא בעמודה a או תאריך עוקב כמה עמודה b?

כמו עמודה A

 

[ג. בן שקד]

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

 

[יעקב ויצמן]

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

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