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

[הילוך גבוה]

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

וואו, תודה רבה!
לא הכרתי את זה

 

[הילוך גבוה]

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

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

 

[הילוך גבוה]

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

 

[מתן מאור]

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

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

 

[מתן מאור]

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

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

2. עד שמונה ספרות מעבר לאפס הוא מקבל, תשע ספרות הוא כבר לא מקבל, לא משנה כמה סולמיות כתבתי לו

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

 

[הילוך גבוה]

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

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

 

[יעקב ויצמן]

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

 

[יעקב ויצמן]

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

אוקיי.
אז כפי שכתבתי לעיל, הפיתרון הפשוט הוא להציב sumifs בתא c14(שים לב לקיבועים השונים) ולגרור למטה
זה הפונקצייה לתא c14

=IFERROR(SUMIFS($C$2:$C$8,$A$2:$A$8,A14,$B$2:$B$8,B14),"")

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

בהצלחה!

 

[ספרא רבא]

גאוני!.
ואם אני רוצה כיתוב ולא מספר?

 

[יעקב ויצמן]

גאוני!.
ואם אני רוצה כיתוב ולא מספר?

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

 

[ספרא רבא]

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

'SUMIF/S' יכול להחזיר טקסט?

 

[יעקב ויצמן]

'SUMIF/S' יכול להחזיר טקסט?

אוייש..התבלבלתי לרגע.סורי.סורי .סורי!!!
אתה צודק לגמרי בשאלתך
כי במקרה של טקסט (בעמודה/טווח של sum range) הפונקצייה תחזיר 0.
התשובה הנכונה שצריך להשתמש בפונקציית filter במקרה שכזה

 

[ספרא רבא]

התשובה הנכונה שצריך להשתמש בפונקציית filter

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

 

[עימוד נקי]

יש לי קובץ אקסל שבתוכו כמה גליונות
בכל גליון 3 טורים 1.שם 2.משפחה 3.סכום
אני רוצה לסכם את כל הגליונות
יצויין שיש שמות שמופעים רק בחלק מהגליונות
ומה שאני רוצה בעצם זאת נוסחאות ל:
1. שישלוף לי כל השמות ושמות המשפחה בלי כפליות
2. סיכום של כל הסכומים לפי השמות מכל הגליונות

מצורף דוגמא של הקובץ

 

[יעקב ויצמן]

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

כדאי שאבין במדויק את שאלתך להימנע מטעויות
נא שלח גיליון דוגמא

 

[יעקב ויצמן]

יש לי קובץ אקסל שבתוכו כמה גליונות
בכל גליון 3 טורים 1.שם 2.משפחה 3.סכום
אני רוצה לסכם את כל הגליונות
יצויין שיש שמות שמופעים רק בחלק מהגליונות
ומה שאני רוצה בעצם זאת נוסחאות ל:
1. שישלוף לי כל השמות ושמות המשפחה בלי כפליות
2. סיכום של כל הסכומים לפי השמות מכל הגליונות

מצורף דוגמא של הקובץ

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

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

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

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

בשלב ב:
בתא d2 קיים מיזוג נתונים על ידי פונקציית unique הנפלאה.
המיזוג חל על הטבלה שהתקבלה משלב א
על עמודות שם ומשפחה יחדיו.לומר לך שרק אם גם השם וגם ומשפחה זהים-רק אז מבוצע מיזוג.

=UNIQUE(A2:B)

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

=SUMIFS($C$2:$C,$A$2:$A, D2,$B$2:$B,E2)

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

בהצלחה לך

 

[ספרא רבא]

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

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

 

[י.כ.]

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

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

 

[יעקב ויצמן]

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

אז התשובה היא שכן
כמו בדוגמא המצורפת עם פעולת כפל והקפת כל תנאי בסוגריים (האפקט הוא כמו של פונקציית and דהיינו שצריך ששני התנאים התקיימו.
פעולת חיבור לעומת זאת,תגרום לאפקט פונקציית or כפי שהסברתי בעבר מספר פעמים בפורום זה.כאן למשל
הודעה בנושא 'רוצה לעזור לכם באקסל' https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13069957)

 

[עימוד נקי]

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

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

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

בשלב ב:
בתא d2 קיים מיזוג נתונים על ידי פונקציית unique הנפלאה.
המיזוג חל על הטבלה שהתקבלה משלב א
על עמודות שם ומשפחה יחדיו.לומר לך שרק אם גם השם וגם ומשפחה זהים-רק אז מבוצע מיזוג.

=UNIQUE(A2:B)

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

=SUMIFS($C$2:$C,$A$2:$A, D2,$B$2:$B,E2)

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

בהצלחה לך

אלוף