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

ספרא רבא

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

קבצים מצורפים

  • קובץ לדוגמא.xlsx
    KB 8.7 · צפיות: 14

הילוך גבוה

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

מתן מאור

משתמש סופר מקצוען
כתיבה ספרותית
שלום וברכה.

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

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

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

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

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

מתן מאור

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

הילוך גבוה

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

הילוך גבוה

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

הילוך גבוה

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

מתן מאור

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

מתן מאור

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

הילוך גבוה

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

יעקב ויצמן

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

יעקב ויצמן

מנהל פורום מחשבים
מנהל
מנוי פרימיום
טעיתי בספרה. בכל אופן הבעיה היא שרציתי לא רק סינון למקרה אחד אלא לטבלה שלימה. מצ"ב שוב הדוגמא
אוקיי.
אז כפי שכתבתי לעיל, הפיתרון הפשוט הוא להציב sumifs בתא c14(שים לב לקיבועים השונים) ולגרור למטה
זה הפונקצייה לתא c14
קוד:
=IFERROR(SUMIFS($C$2:$C$8,$A$2:$A$8,A14,$B$2:$B$8,B14),"")

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

בהצלחה!
 

יעקב ויצמן

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

ספרא רבא

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

יעקב ויצמן

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

עימוד נקי

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

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

קבצים מצורפים

  • דוגמא.xlsx
    KB 10.6 · צפיות: 16

יעקב ויצמן

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

יעקב ויצמן

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

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

אסביר בקצרה:
בשלב א:
בתא 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)

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

בהצלחה לך
 

קבצים מצורפים

  • סיכום ממספר גיליונות.xlsx
    KB 148.1 · צפיות: 20
נערך לאחרונה ב:

אולי מעניין אותך גם...

הפרק היומי

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


תהילים פרק קכו

א שִׁיר הַמַּעֲלוֹת בְּשׁוּב יי אֶת שִׁיבַת צִיּוֹן הָיִינוּ כְּחֹלְמִים:ב אָז יִמָּלֵא שְׂחוֹק פִּינוּ וּלְשׁוֹנֵנוּ רִנָּה אָז יֹאמְרוּ בַגּוֹיִם הִגְדִּיל יי לַעֲשׂוֹת עִם אֵלֶּה:ג הִגְדִּיל יי לַעֲשׂוֹת עִמָּנוּ הָיִינוּ שְׂמֵחִים:ד שׁוּבָה יי אֶת (שבותנו) שְׁבִיתֵנוּ כַּאֲפִיקִים בַּנֶּגֶב:ה הַזֹּרְעִים בְּדִמְעָה בְּרִנָּה יִקְצֹרוּ:ו הָלוֹךְ יֵלֵךְ וּבָכֹה נֹשֵׂא מֶשֶׁךְ הַזָּרַע בֹּא יָבוֹא בְרִנָּה נֹשֵׂא אֲלֻמֹּתָיו:
נקרא  106  פעמים

אתגר AI

תאומים • אתגר 145

לוח מודעות

למעלה