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

[מדויק]

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

נראה לי שכאן יש תשובה לשאלתך

 

[יעקב ויצמן]

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

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

 

[יעקב ויצמן]

@יעקב ויצמן אגב, אם תשים לב אני משתמש ב 9 ולא ב 109 ה 9 מחשב גם שורות מוסתרות (לא מחשב שורות שהוסתרו ע"י סינון)

צודק.וסורי על כך

 

[יעקב ויצמן]

נראה לי שכאן יש תשובה לשאלתך

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

 

[הילוך גבוה]

תודה ענקית! ל @למה? ול @יעקב ויצמן
עובד מעולה, לא מחשב אותו בסיכום הסופי כמו SUBTOTAL טוב...
יש לי בעיה אחרת עם זה שאינני מבין אותה, רשמתי SUBTOTAL 2 ולא 3 ועדיין הוא מחשב גם תאים בהם יש טקסט, אני לא רוצה שהוא יחשב טקסט אלא רק ספרות
מה זה יכול להיות?
גילוי נאות: אני לא כ"כ הבנתי את הרעיון של הקומבינה הזאת, רק הורדתי את קובץ בלינק ש @למה? צירף
העתקתי את הנוסחה ורק שיניתי את הטווחים

 

[יעקב ויצמן]

תודה ענקית! ל @למה? ול @יעקב ויצמן
עובד מעולה, לא מחשב אותו בסיכום הסופי כמו SUBTOTAL טוב...
יש לי בעיה אחרת עם זה שאינני מבין אותה, רשמתי SUBTOTAL 2 ולא 3 ועדיין הוא מחשב גם תאים בהם יש טקסט, אני לא רוצה שהוא יחשב טקסט אלא רק ספרות
מה זה יכול להיות?
גילוי נאות: אני לא כ"כ הבנתי את הרעיון של הקומבינה הזאת, רק הורדתי את קובץ בלינק ש @למה? צירף
העתקתי את הנוסחה ורק שיניתי את הטווחים

תשנה בsubtotal
קוד 2 ולא 3
ותראה ישועות ונפלאות.

ועכשיו ההסבר:
הסברתי כאן לא אחת את ההבדל בין count לcounta
count מחשבת ספירה רק לתאים עם מספר
counta מחשבת כל טקסט באשר הוא חוץ מתאים ריקים.
זה בקצרה.
אולי בהזדמנות אכתוב כאן "שיעור כללי" שלם בנושא זה .לאחר מחקר מקיף עם משפחת count המורחבת לצאצאיה...

 

[הילוך גבוה]

תשנה בsubtotal
קוד 2 ולא 3
ותראה ישועות ונפלאות.
צפה בקובץ המצורף 1345451
ועכשיו ההסבר:
הסברתי כאן לא אחת את ההבדל בין count לcounta
count מחשבת ספירה רק לתאים עם מספר
counta מחשבת כל טקסט באשר הוא חוץ מתאים ריקים.
זה בקצרה.
אולי בהזדמנות אכתוב כאן "שיעור כללי" שלם בנושא זה .לאחר מחקר מקיף עם משפחת count המורחבת לצאצאיה...

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

 

[יעקב ויצמן]

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

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

 

[הילוך גבוה]

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

=SUMPRODUCT((I3:I22>0)*(SUBTOTAL(2,OFFSET(I3,ROW(I3:I22)-MIN(ROW(I3:I22)),0))))

 

[הילוך גבוה]

=SUMPRODUCT((I3:I22>0)*(SUBTOTAL(2,OFFSET(I3,ROW(I3:I22)-MIN(ROW(I3:I22)),0))))

@יעקב ויצמן סליחה שאני מטרטר.. יש לך בשורה עבורי?

 

[high light]

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

והשאלה שלי.היא מה התנאי לעיצוב המותנה בצבע מסוים?
שאדע את התשובה.יהיה קל ופשוט לעשות חישוב בעזרת sumif
אם לא אכנס לשאלה הנל
הפתרונות הם:
א.פקודת מאקרו vba
ב.תשובה יותר פשוטה.היא לבצע סינון בעמודה על פי צבע.ולהכניס את פונקציית subtotal
טווח range יהיה על כל העמודה.וקוד 109
כך חישוב הסכום יתבצע על התאים המסוננים בלבד.
זהו טריק סופר מגניב
בהצלחה

תודה!
[אם זה מורכב מדי בשביל שאבין אז אין צורך]

 

[חד כתער]

ץ

 

[רבקה ברזל]

יש אפשרות להמרת אקסל לpdf או לקובץ תמונה?

 

[מרובע]

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

 

[xl3391]

יש אפשרות להמרת אקסל לpdf או לקובץ תמונה?

יש אפשרות יצוא לPDF
קובץ > יצא

 

[xl3391]

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

בחירה של שם הגיליון בתא מסוים, ולמשוך ממנו באמצעות indirect
דוגמא תא B2 בגיליון נוכחי מקבל את ערך תא B2 בגיליון שנבחר בתא A1

=INDIRECT($A$1&"!"&cell("address",B2))

 

[xl3391]

מצ"ב קובץ תאריכים עבריים. מבוסס על התאריך העברי המובנה של אקסל.

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

 

[xl3391]

שים לב, אני רוצה לדעת כמה אברכים פעילים יש בכולל וכמה מתוך הפעילים קיבלו מלגה
אלו שלא פעילים לא כתוב שום ספרה בתא שלהם
אלו שפעילים ולא קיבלו מלגה רשום 0
יש COUNT שמחשב בכמה תאים יש ספרה, כדי לדעת כמה פעילים יש בכולל, ויש COUNTIF שמחשב כמה קיבלו מלגה
ככה אני רואה כמה מתוך כמה קיבלו מלגה

ליתר חידוד, אדגיש שהסיבה שאני משתמש ב SUBTOTAL ולא ב SUM היא לא בגלל שורות מוסתרות אלא בגלל הסיכום הסופי

אם השימוש בsubtotal הוא רק בכדי שלא יכלל בסיכום הסופי, אפשר לרשום נוסחת subtotal שהתוצאה שלה תמיד 1 ולהכפיל בנוסחה הקיימת של ה countif .
למשל בתא A1 יש כותרת קבועה, ונוסחה של subtotal(3,$A$1) תחזיר תמיד 1, את הנוסחה הזאת להכפיל בcountif.

=SUBTOTAL(3,$A$1)*COUNTIF(D2:D20,">0")

 

[מרובע]

בחירה של שם הגיליון בתא מסוים, ולמשוך ממנו באמצעות indirect
דוגמא תא B2 בגיליון נוכחי מקבל את ערך תא B2 בגיליון שנבחר בתא A1

=INDIRECT($A$1&"!"&cell("address",B2))

ברוך שכיוונתי זה מה שעשיתי בסוף
אבל חשבתי אולי יש אופציה נוספת לעשות באמצעות נוסחה בתא אחד (כמו נוסחאות IMPORT)
בתודה

 

[xl3391]

ברוך שכיוונתי זה מה שעשיתי בסוף
אבל חשבתי אולי יש אופציה נוספת לעשות באמצעות נוסחה בתא אחד (כמו נוסחאות IMPORT)
בתודה

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

=INDIRECT($A$1&"!B2:H22")

עריכה:
לא צריך את הindex