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

[Yisrael]

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

 

[יעקב ויצמן]

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

נא לשלוח גיליון דוגמא

 

[למה?]

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

SUMIFS

 

[קול תורה]

SUMIFS

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

COUNTIF

 

[למה?]

COUNTIF

איך ב COUNTIF ?


אני חושב שהנוסחה לכך היא FILTER

 

[יעקב ויצמן]

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

ובכן רבותי.ראשית בואו נבין את השאלה ונעשה סדר בדברים ועם הנאמר לעיל.
יש כאן בעצם 2 שאלות וחישובים.
1.לחשב (הכוונה לפרט- ולכן counif לא תספק את המענה לכך...) את שמות האנשים שתאריך הפירעון בשבוע הקרוב
2.לחשב את ס"ך הפירעון של אותם בעלי חוב האמורים בסעיף 1.

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

לתשובותכם @למה? @קול תורה
אילולי שהיה צורך פה רק בחישוב הס"ך .היה נדרש sumif (ולא דווקא sumifs)
אבל.יש כאן נדבך נוסף שיש לתת עליו את הדעת והוא סיכום על פי תאריך ושבוע.-זהו למעשה התנאי !!!
ולכן יש כאן צורך להשתמש בפונקציית weeknum כפי שאכתוב לקמן.
בשאלה שכזו היה צורך להוסיף עוד עמודת עזר עם פונקציית unique
כפי שכתבתי בזמנו לשאלה שכזו https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-11941421
אבל כאן יש לנו גם את השאלה הראשונה.
אז זה נכון שיש להשתמש בפונקציית filter.אבל זה לא מספיק.כי בארגומנט השני של הפונקצייה אנו חייבים להציב תנאי של תאריך על פי שבוע.
וכפי שכתבתי, התנאי לאקסל מוצג בעזרת הפונקצייה של weeknum בשילוב today שזה אומר לאקסל בעצם תחפש את התאריך של השבוע שחל בו היום הזה.ובמילים אחרות -תחפש לי את התאריך של השבוע הנוכחי.
תראו את זה בדוגמא פה בצילום מסך
(אדגיש שאת עמודת העזר C ניתן להסתיר ללא חשש)
כמובן שניתן להוסיף את פונקציית sort למיון אוטומטי מא-ת.אם תרצו אין זו אגדה...

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

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

 

[למה?]

ובכן רבותי.ראשית בואו נבין את השאלה ונעשה סדר בדברים ועם הנאמר לעיל.
יש כאן בעצם 2 שאלות וחישובים.
1.לחשב (הכוונה לפרט- ולכן counif לא תספק את המענה לכך...) את שמות האנשים שתאריך הפירעון בשבוע הקרוב
2.לחשב את ס"ך הפירעון של אותם בעלי חוב האמורים בסעיף 1.

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

לתשובותכם @למה? @קול תורה
אילולי שהיה צורך פה רק בחישוב הס"ך .היה נדרש sumif (ולא דווקא sumifs)
אבל.יש כאן נדבך נוסף שיש לתת עליו את הדעת והוא סיכום על פי תאריך ושבוע.-זהו למעשה התנאי !!!
ולכן יש כאן צורך להשתמש בפונקציית weeknum כפי שאכתוב לקמן.
בשאלה שכזו היה צורך להוסיף עוד עמודת עזר עם פונקציית unique
כפי שכתבתי בזמנו לשאלה שכזו https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-11941421
אבל כאן יש לנו גם את השאלה הראשונה.
אז זה נכון שיש להשתמש בפונקציית filter.אבל זה לא מספיק.כי בארגומנט השני של הפונקצייה אנו חייבים להציב תנאי של תאריך על פי שבוע.
וכפי שכתבתי, התנאי לאקסל מוצג בעזרת הפונקצייה של weeknum בשילוב today שזה אומר לאקסל בעצם תחפש את התאריך של השבוע שחל בו היום הזה.ובמילים אחרות -תחפש לי את התאריך של השבוע הנוכחי.
תראו את זה בדוגמא פה בצילום מסך
(אדגיש שאת עמודת העזר C ניתן להסתיר ללא חשש)
כמובן שניתן להוסיף את פונקציית sort למיון אוטומטי מא-ת.אם תרצו אין זו אגדה...
צפה בקובץ המצורף 1424877
כעת נותר לחשב את הסכום
כאן מספיק לנו להריץ פונקציית sum פשוטה על עמודה I וזהו.
וכבר אין צורך לא בsumif ולא בuniqu
ראו כאן בדוגמא בצילום מסךצפה בקובץ המצורף 1424884

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

=FILTER(A3:C14,B3:B14<(TODAY()+8))

 

[יעקב ויצמן]

=FILTER(A3:C14,B3:B14<(TODAY()+8))

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

 

[יעקב ויצמן]

ובכן רבותי.ראשית בואו נבין את השאלה ונעשה סדר בדברים ועם הנאמר לעיל.
יש כאן בעצם 2 שאלות וחישובים.
1.לחשב (הכוונה לפרט- ולכן counif לא תספק את המענה לכך...) את שמות האנשים שתאריך הפירעון בשבוע הקרוב
2.לחשב את ס"ך הפירעון של אותם בעלי חוב האמורים בסעיף 1.

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

לתשובותכם @למה? @קול תורה
אילולי שהיה צורך פה רק בחישוב הס"ך .היה נדרש sumif (ולא דווקא sumifs)
אבל.יש כאן נדבך נוסף שיש לתת עליו את הדעת והוא סיכום על פי תאריך ושבוע.-זהו למעשה התנאי !!!
ולכן יש כאן צורך להשתמש בפונקציית weeknum כפי שאכתוב לקמן.
בשאלה שכזו היה צורך להוסיף עוד עמודת עזר עם פונקציית unique
כפי שכתבתי בזמנו לשאלה שכזו https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-11941421
אבל כאן יש לנו גם את השאלה הראשונה.
אז זה נכון שיש להשתמש בפונקציית filter.אבל זה לא מספיק.כי בארגומנט השני של הפונקצייה אנו חייבים להציב תנאי של תאריך על פי שבוע.
וכפי שכתבתי, התנאי לאקסל מוצג בעזרת הפונקצייה של weeknum בשילוב today שזה אומר לאקסל בעצם תחפש את התאריך של השבוע שחל בו היום הזה.ובמילים אחרות -תחפש לי את התאריך של השבוע הנוכחי.
תראו את זה בדוגמא פה בצילום מסך
(אדגיש שאת עמודת העזר C ניתן להסתיר ללא חשש)
כמובן שניתן להוסיף את פונקציית sort למיון אוטומטי מא-ת.אם תרצו אין זו אגדה...
צפה בקובץ המצורף 1424877
כעת נותר לחשב את הסכום
כאן מספיק לנו להריץ פונקציית sum פשוטה על עמודה I וזהו.
וכבר אין צורך לא בsumif ולא בuniqu
ראו כאן בדוגמא בצילום מסךצפה בקובץ המצורף 1424884

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

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

How to Group Data by Week in Excel

This tutorial explains how to group data by week in Excel, including an example.

www.statology.org

 

[שלוות נפש]

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

 

[למה?]

שכל שם יופיע בנפרד עם השם של האב

תסביר יותר

 

[שלוות נפש]

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

 

[אייל?]

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

כל הכבוד

גיליון1 מי,כמה,האם שולם?,אם סומן וי אז בעמודה זו יופיע כל הכבוד שלום ,100 ניסים,100 ברוך,100

docs.google.com

 

[למה?]

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

כל הכבוד

גיליון1 מי,כמה,האם שולם?,אם סומן וי אז בעמודה זו יופיע כל הכבוד שלום ,100 ניסים,100 ברוך,100

docs.google.com

=IF(H2=TRUE,"כל הכבוד","")

 

[עמיק]

=FILTER(A3:C14,B3:B14<(TODAY()+8))

רצוי להוסיף עוד תנאי כדי שלא יחזיר תוצאות ישנות

=FILTER(A3:C14,(B3:B14<(TODAY()+8)*(B3:B14>=(TODAY()))))

 

[יעקב ויצמן]

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

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

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

חלק 1 הוא טבלת המקור.
בחלק 2 ישנה מערכת לסינון של אב הילדים לפי שם ומשפחה.
זהו שלב ראשוני להצגת הנתונים הסופיים.
בשטח הצהוב המסומן ישנו אימות נתונים לבחירה מתוך טווח.
הפונקצייה filter מחזירה את הנתונים של הילדים הרלוונטים כפי טבלת המקור.
בחלק 3 ישנה המרה אוטומטית לנתוני שלב 2 .
השתמשתי בפונקציות textjoin +split+transpose
וכן בפונקציות if+counta
(לא הצלחתי בשום דרך לשלב את שלב 2 ושלב 3 בטבלה אחת.זו בעיה קשה מאוד)

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

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

ושיהיה בהצלחה

 

[שלוות נפש]

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

ובכן ניסיתי וניסיתי להצמד לשאלתך ולבצע חישובים עם פונקציות.השתמשתי עם כל מיני קומבינוציות ועם ארסנל הכלים שאני מכיר ויודע.וזה לא צלח...
ובכל זאת- לפתור בלי כלום לא רציתי....
אז מה כן..???
תראה כאן בצילום מסך
צפה בקובץ המצורף 1425975
חלק 1 הוא טבלת המקור.
בחלק 2 ישנה מערכת לסינון של אב הילדים לפי שם ומשפחה.
זהו שלב ראשוני להצגת הנתונים הסופיים.
בשטח הצהוב המסומן ישנו אימות נתונים לבחירה מתוך טווח.
הפונקצייה filter מחזירה את הנתונים של הילדים הרלוונטים כפי טבלת המקור.
בחלק 3 ישנה המרה אוטומטית לנתוני שלב 2 .
השתמשתי בפונקציות textjoin +split+transpose
וכן בפונקציות if+counta
(לא הצלחתי בשום דרך לשלב את שלב 2 ושלב 3 בטבלה אחת.זו בעיה קשה מאוד)

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

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

ושיהיה בהצלחה

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

 

[יעקב ויצמן]

א. תודה רבה רבה!!

בשמחה רבה

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

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

לפי מה שהבנתי באמת אפשר לעשות את זה ב Pivot Table אבל לא ממש הבנתי איך ולא הצלחתי

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

וניסיתי לשנות באימות נתונים שעשית את השמות ולא השתנה השמות של הילדים

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

 

[שלוות נפש]

למה בשורת הנוסחאות זה מביא כזה נוסחה?

 

[יעקב ויצמן]

למה בשורת הנוסחאות זה מביא כזה נוסחה?צפה בקובץ המצורף 1426256

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