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

ספרא רבא · 23/5/24

נכתב ע"י יעקב ויצמן:
מחילה.משהו פה לא מובן.
בעיקרון הפיתרון הפשוט הוא להציב פונקציית sumifs בתא c14.(או בעזרת filter או sumproduct ועוד)
צפה בקובץ המצורף 1660028
אבל שים לב שתז 4036 לא קיבל כלום בחודש אדר.מי שקיבל בחודש אדר זה ת.ז 4038.
במקרה הזה הפונקציות יחזירו שגיאת n/a
אשמח שתסביר...

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

הילוך גבוה · 23/5/24

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

מתן מאור · 23/5/24

נכתב ע"י תהילה מעטוף:
שלום וברכה.

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

תודה רבה!

יש אפשרות כזאת אבל היא מעט מורכבת.

תחילה צריך להשתמש עבור המאקרו בלחצן טופס, ולא בלחצן רגיל:

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

עכשיו ניתן לפתוח את עורך הקוד ולהעתיק לתוכו את הפונקציה הבאה:

קוד:

Sub test()
   
    Dim Button As Object, Co, Ro As Integer
    Set Button = ActiveSheet.Buttons(Application.Caller)
   
    With Button.TopLeftCell
        Co = .Column
        Ro = .Row
    End With
   
    Cells(Ro, Co + 1).Value = Cells(Ro, Co - 1).Value
    Cells(Ro, Co - 1).Value = ""

End Sub

חלקו הראשון שהקוד מקבל את הנתונים הנ"ל באיזו שורה ועמודה נמצאת פינתו הימנית העליונה של אותו כפתור.
מה שחשוב לענייננו הם שתי השורות האחרונות:
השורה Cells(Ro, Co + 1).Value = Cells(Ro, Co - 1).Value מציינת "תיגש אל התא שמימין ללחצן (עמודה + 1) והכנס לתוכו את הערך של התא שמשמאל ללחצן (עמודה - 1)".

לאחר מכן השורה "" = Cells(Ro, Co - 1).Value מציינת "תיגש אל התא שמשמאל ללחצן (עמודה - 1) ומחק ממנה את הנתון שהיה בה".

מתן מאור · 23/5/24

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

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

הילוך גבוה · 23/5/24

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

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

הילוך גבוה · 23/5/24

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

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

הילוך גבוה · 23/5/24

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

מתן מאור · 23/5/24

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

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

מתן מאור · 23/5/24

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

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

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

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

הילוך גבוה · 23/5/24

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

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

יעקב ויצמן · 23/5/24

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

יעקב ויצמן · 23/5/24

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

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

קוד:

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

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

בהצלחה!

ספרא רבא · 23/5/24

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

יעקב ויצמן · 23/5/24

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

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

ספרא רבא · 23/5/24

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

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

יעקב ויצמן · 23/5/24

נכתב ע"י ספרא רבא:
'SUMIF/S' יכול להחזיר טקסט?

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

ספרא רבא · 23/5/24

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

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

עימוד נקי · 23/5/24

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

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

יעקב ויצמן · 23/5/24

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

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

יעקב ויצמן · 23/5/24

נכתב ע"י עימוד נקי:
יש לי קובץ אקסל שבתוכו כמה גליונות
בכל גליון 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 הנפלאה.
המיזוג חל על הטבלה שהתקבלה משלב א
על עמודות שם ומשפחה יחדיו.לומר לך שרק אם גם השם וגם ומשפחה זהים-רק אז מבוצע מיזוג.

קוד: