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

[מדויק]

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

נוסחת SUMIF

 

[חיים5]

@למה? תודה רבה רבה, עזרת לי מאוד

 

[יעקב ויצמן]

לא מצליח להבין מה ה 1 בהתחלה אומר לאקסל?

יפה שאלת...
שאני מזכיר מידי פעם בפורום זה שאת האקסל צריך ללמוד ולחקור,בדיוק לכך הכוונה...
המטרה היא לא רק לחפש פיתרון מהיר אלא להבין ולהעמיק בלוגיקה ובעומק של האקסל עד כמה שיד שכלינו מגעת...
ולעצם שאלתך..(אני לא סגור על ההסבר במאת האחוזים אבל)עד כמה שהעמקתי בנושא נראה לי שההסבר הוא כך:
בעבר,הזכרתי כאן מספר פעמים את המושג "לוגיקה/נוסחא בוליאנית".
המשמעות היא -שבפונקצייה עם תנאי מוחזר true או false דהיינו אמת או שקר.
בלוגיקה הבוליאנית האקסל מתרגם את ההחזרים הנ"ל "מאחורי הקלעים/subtext" כמספר/שווה ערך ל- 1 או 0.
true פירושו 1
false פירושו 0
ולכן בארגומנט השני מתקיים מבדק התנאי/ים .
במקרה כאן קיימים שני תנאים וכל אחד מסוגר בסוגריים.
אם מתקיים תנאי מוחזר true =1
ואם לא מתקיים תנאי מוחזר 0=false.
יש כאן פעולת כפל.ומה משמעותה?
שאם נכפיל 1 ב0 (וק"ו 0 ב0)תמיד יוחזר 0.
ואם נכפיל 1 ב1 (כלומר שהוחזר פה פעמיים true) אז התשובה תהיה 1.
(הטכניקה המתאמטית הזו פועלת בנוסחאות מערך ותנאי רבות,בעבר הסברתי את הטכניקה הזו בקשר למכפלה שמבוצעת בפונקציית sumproduct)

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

 

[יעקב ויצמן]

@למה
בהשגחה עליונה ובתזמון מושלם נשאלה השאלה הנ"ל על ידי @חיים 5 וענית תשובה מצוינת.
אך תראה כאן תוספת מעניינת.
הסברתי לעיל על הלוגיקה בולייאנית ומשמעות true כשווה ערך ל1 בפונקציית xlookup
והזכרתי ברמיזה את פונקציית sumproduct
תראה בצילום מסך מתוך הגיליון המצורף שערכתי

התוצאה בעזרת sumproduct זהה לתוצאת חיבור sumif+sumif
הגאונות שבה הופכת את המלל ליותר קצר בשורת הפונקציות.
כאן מדובר בסה"כ בתנאי אחד ופעולת חיבור של 2 sumif. אך שמדובר במספר תנאים החלות על עמודות שונות הנוסחא הופכת כבר לארוכה...
sumproduct הופכת את הנוסחא למינימליסטית וקומפקטית כפי שהוכחתי בלינק כאן
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13271482
גם שמדובר בפעולת חיסור של חישוב שעות עבודה הנוסחא והדרך הופכים לקצרים ביותר וכפי שהסברתי בלינק כאן
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13235309
כאמור.ברירת המחדל בפונקצייה היא פעולת כפל.אך ניתן לבצע בה כל אחת מ4 פעולות חשבון הבסיסיות.
לוגיקה אחת בכולן.וזה מדהים ויפה!
מצורף כאן גיליון אקסל.
הוספתי בשם הגיליון vs כי אכן מדובר כאן בקרב על "המינימליזם" של הטקסט.מי המנצחת?
זה ברור...!
בהצלחה רבה ושבת שלום

 

[מדויק]

יפה שאלת...
שאני מזכיר מידי פעם בפורום זה שאת האקסל צריך ללמוד ולחקור,בדיוק לכך הכוונה...
המטרה היא לא רק לחפש פיתרון מהיר אלא להבין ולהעמיק בלוגיקה ובעומק של האקסל עד כמה שיד שכלינו מגעת...
ולעצם שאלתך..(אני לא סגור על ההסבר במאת האחוזים אבל)עד כמה שהעמקתי בנושא נראה לי שההסבר הוא כך:
בעבר,הזכרתי כאן מספר פעמים את המושג "לוגיקה/נוסחא בוליאנית".
המשמעות היא -שבפונקצייה עם תנאי מוחזר true או false דהיינו אמת או שקר.
בלוגיקה הבוליאנית האקסל מתרגם את ההחזרים הנ"ל "מאחורי הקלעים/subtext" כמספר/שווה ערך ל- 1 או 0.
true פירושו 1
false פירושו 0
ולכן בארגומנט השני מתקיים מבדק התנאי/ים .
במקרה כאן קיימים שני תנאים וכל אחד מסוגר בסוגריים.
אם מתקיים תנאי מוחזר true =1
ואם לא מתקיים תנאי מוחזר 0=false.
יש כאן פעולת כפל.ומה משמעותה?
שאם נכפיל 1 ב0 (וק"ו 0 ב0)תמיד יוחזר 0.
ואם נכפיל 1 ב1 (כלומר שהוחזר פה פעמיים true) אז התשובה תהיה 1.
(הטכניקה המתאמטית הזו פועלת בנוסחאות מערך ותנאי רבות,בעבר הסברתי את הטכניקה הזו בקשר למכפלה שמבוצעת בפונקציית sumproduct)

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

הסברת את זה ממש בצורה שרק אתה יכול! ממש ברור! כל הכבוד!

 

[יעקב ויצמן]

הסברת את זה ממש בצורה שרק אתה יכול! ממש ברור! כל הכבוד!

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

 

[מדויק]

בשמחה רבה!

ההערה במקומה.אתה צודק לחלוטין.
"האינרציה" עם הפילטר עשתה את שלה ולכן המשכתי את הסיכומים עם פילטר.(החוק הראשון של אייזק ניוטון גם פועל עלינו כבני אדם..)
אבל אם כבר דרכים שונים לפיתרון אחד.
אז תראה כאגב עוד שיטת סיכום עם פונקציית sumproduct המופלאה והגאונית עם טכניקת ניסוח של Double unary
בנוסחה "בוליאנית".
כך זה נראה
צפה בקובץ המצורף 1561382
בהזדמנות בל"נ ארחיב יותר על טכניקה זו והשימושים שבה.
אבל כעת אומר בקצרה.כי בנוסחאות מערך שמוחזר בהן true/false .ולצורך חישוב של סה"כ נדרש להמיר את הערכים למספר 0 או 1.
לצורך כך משתמשים עם תוספת של פעולת חיסור כפולה ככה - -
וכעת תסתכל על עמודה a בכל פעם שכתוב לדוגמא "אברהם" הפונקצייה תמיר זאת ל1.ותכפיל אותו בערך הכתוב בו באותה שורה בעמודה e.
(אם התנאי לא מתקיים מוחזר 0.ומכפלה של כל מספר ב0 שווה ל0.... אז בחישוב סך הכללי, 0 נשאר 0.לא מעלה ולא מוריד...)
לאחר מכן כל המכפלות שנוצרו מסוכמים לסכום אחד על ידי פונקציית sumproduct.
אז שוב "נשאבתי" לטכניקה עם לוגיקה בוליאנית כמו של filter.רק שהגאונות כאן של תבנית הפונקצייה כפולה ומכופלת...
בקיצור.הנה ממתק טעים מבית היוצר -אקסל ..תהנה!

אכן כן.הנך צודק לחלוטין..שכחתי להוסיף פרט חשוב זה.
ישר כח!

אתה מנסה להסביר כאן למה צריך כאן חיסור כפולה --
אבל לא הבנתי 3 דברים:

1 ניסיתי בלי --

SUMPRODUCT(A:A=C1,B:B)

וזה עובד מעולה נותן התוצאה הרצויה!

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

3 למה פה אתה לא יכול להשתמש עם 0 ו 1

 

[I believe]

שלום לכל החברים ובראשם ל @יעקב ויצמן, רציתי לשאול אם מישהו יוכל להסביר לי מהי האופציה השלישית ברשימה המוצעת לשמירת כל קובץ אקסל-
Excel Binary Workbook ( *. xlsb), ופירושו כפי שיצא לי בגוגל- קובץ בינארי של Excel, המבטיח ביצועים מהירים יותר מקבצי XLSX.
יש לי קובץ עם הרבה פקודות מאקרו ושמתי לב שכששמרתי אותו בסוג זה הוא באמת נפתח ונסגר לי יותר מהר.
השאלה היא מה באמת המשמעות של הסוג הזה, מה בינו לבין קובץ מאקרו רגיל, למה הוא מתאים, והאם יש סיכון לתקלות בשמירה בסוג הזה.
אשמח לכל מידע בנושא, תודה רבה.

 

[יעקב ויצמן]

אתה מנסה להסביר כאן למה צריך כאן חיסור כפולה --
אבל לא הבנתי 3 דברים:

1 ניסיתי בלי --

SUMPRODUCT(A:A=C1,B:B)

וזה עובד מעולה נותן התוצאה הרצויה!

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

3 למה פה אתה לא יכול להשתמש עם 0 ו 1

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

בתא a8 אנו מבקשים מפונקציית len לספור את ס"ך התוים (מבלי להיכנס עכשיו להבדלים בין פונקצייה זו לפונקציות דומות כמו counta בשאלת אילו תוים נכללים ואלו לא...)שבכל תא מתא a1 עד a5
עד כאן הכל טוב,יפה ומובן.
סעיף 2:
כעת אנו נוסיף בתא a15 תנאי למשוואה ,התנאי הוא לכלול בספירה רק תאים שמכילים מעל 3 תוים

כאן כללי הספירה כבר שונים.יש כאן תנאי!
תנאי נמדד בהחזר של true או false.סה טו!
ולכן אין כאן החזר של מספר התוים בכל תא אלא תשובה כלפי השאלה של התנאי.(אני מדגיש זאת וחשוב לזכור את הניואנס הזה)
הפונקצייה מחזירה בשרשור את סדר התאים והתשובה האם מדובר בtrue או false
הלאה,הבה נתקדם שלב...
סעיף 3:
אם נרצה לדעת במספר כמה תאים בסה"כ הוחזר true
לכאורא הפיתרון הוא לעטוף את כל הסיפור הזה בפונקציית sumproduct
אז הנה עשיתי זאת בתא a22

הופ!! התשובה היא 0
מה קרה פה בדיוק?
ראשית.צריך להזכיר שלמרות שפונקצייה זו בדרך כלל מקבלת שני(או יותר) טווחים/מערכים ,מכפילה(כברירת מחדל) בין המערכים (תא כנגד תא באותה שורה) ואז מסכמת את התוצאות,
כאשר יש רק מערך אחד היא פשוט מסכמת את כל התאים שבו.
ומה קרה פה ?
התשובה היא שההחזר שהתקבל פה הוא ביטוי לוגי.הביטוי אמנם שווה ערך בפוטנציאל שלו לערך מספרי של 0 או 1
אבל הפונקצייה "לא יודעת" לבצע לבד את ההמרה הזו מביטוי לוגי לערך מספרי.
שהפונקצייה עושה חיבור של כל התוצאות ,אז כל התוצאות שוות ל0 .
0+0+0+0+0=0
סעיף 4:
אנו צריכים "לעזור לפונקצייה" ולתת לה כלי שיתווך לה שתוכל לבצע את ההמרה הזו בקלות-איזה push קטן...

כאן בתא a25 זה קורה סוף סוף בעזרת טכניקת Double unary , התוצאה היא 3
3 פעמים של החזר true . כל true שכזה שווה ערך ל1
1+1+1=3
אדגיש כאן נקודה חשובה כי כל ההסבר כאן תקף לגיליון אקסל בלבד.
בגיליון שיטס ישנם חשבונות אחרים והכרח לעטוף את הפונקציות בפונקציית arryformula וזה כבר משנה את כל כללי המשחק והלוגיקה...

@למה כעת אמשיך ואתייחס לשאלותיך
שאלה 1:
בגיליון אקסל ללא הוספת דאבל מינוס התוצאה תהיה 0

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

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

בחשבון של הפונקצייה
הואיל והתקיים התנאי וכתוב בתא a2 אברהם
זה כאילו כתוב בתא a2 את הספרה 1.ואת ערך זה הפונקצייה כופלת בתא שבעמודה e מאותה שורה, קרי תא e2
שכתוב בה 30 ש"ח
אז 1*30= 30
וכן הלאה והלאה בכל השורות למטה.לאחר מכן הפונקצייה סוכמת את כל המכפלות והתוצאה 431

שאלה 3:
בנידון כאן אין נפקא מינה לתוספת ארגומנט שיהיה מופנה לעמודה שכתוב בה 1
כי יש כאן פלט של 1 כאשר מתקיים התנאי ומה התועלת במכפלה נוספת ב1
אם אחזור לדוגמא בשאלה 2 אז
1*30=30
וכן 1*1*30=30
וכן 1*1*1*30=30

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

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

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

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

www.prog.co.il

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

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

מצורף גיליון אקסל עם הדוגמאות שהבאתי לעיל

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

 

[מליה]

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

 

[I believe]

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

אפשר באמצעות נוסחה זו-

=SUMIF(A1:A10,">0",A1:A10)

 

[יעקב ויצמן]

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

אפשר באמצעות נוסחה זו-

=SUMIF(A1:A10,">0",A1:A10)

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

הפיתרונות(בטווח הירוק) כאן הם קומבינציות ומשחק של פונקציות (בינהן not,isnumber,isna והמוכרות יותר sumif/sumifs/vlookup)
התייחסתי כאן לשגיאת n/a בעיקר בגלל המסר בפיתרון 6 האחרון והכי חביב...כפי שאסביר בהמשך.
במידה ומדובר בשגיאת value /ref ועוד ניתן להשתמש בפונקציית iserror שהיא כוללת את כל סוגי השגיאות.
ישנם עוד כמה פיתרונות...אבל דומני ש6 פיתרונות זה די והותר...נכון?
הוספתי כאגב חישוב למספר התאים שבהם התקבל סכום ולא שגיאת n/a

בטווח התכלת החישוב מתקיים עם 2 פתרונות.
פיתרון 2 הוא הפשוט והידוע שבהן.
כידוע פונקציית count סופרת תאים עם ערכי מספר בלבד ומתעלמת מערכי טקסט (ולא משנה מאיזה סוג ואופן)
אבל פיתרון 1 הוא היותר מעניין .וזה אפרופו דבריי לעיל בהרחבה על שימוש בטריק "החיסור הכפול"
גם כאן מתקבלת רשימת ערכים לוגים של true /false.
בעזרת עטיפת הפונקצייה ב-- מבוצעת המרה לערכים מספרים והתוצאה מדויקת.
נפלא...נכון?

טוב...בשולי הדברים(בצילום מסך למטה בחלק המוקף בעיגול) הוספתי נספח ,אפרופו השימוש בפיתרון 6 .מה בעצם קורה בפיתרון הזה?
אז אנו מורגלים לכתוב בארגומנט הראשון של פונקציית vlookup הפניה לתא בודד או ערך בודד מסוים.
אך אגלה לכם בסוד(ששש...) שפונקצייה זו יכולה גם לקבל טווחים (בתנאים ספציפיים)
היכן זה פוגש אותנו?
אם נניח ישנם 2 עמודות זהות בגובהן ואנו רוצים לבצע השוואה בינהן .כלומר שתוחזר לנו עמודה נוספת עם רשימה של הערכים שמופיעים בעמודה x וגם מופיעים בעמודה y וכן להיפך..
אז נכון שניתן לבצע זאת בעזרת מספר טכניקות (countif,עיצוב מותנה,power query) אבל יש כאן עוד טכניקה חכמה מאוד ומעניינת לא פחות...

בטווח המסומן אנו מקבלים את רשימת השמות שמופיעים בטווח הצהוב וגם מופיעים בטווח האדום.השמות בטווח הצהוב שאינם מופיעים בטווח האדום יוחזר בהם שגיאת n/a
אם נחזור לפיתרון 6 לעיל.
אנו למעשה לא מחפשים בסתם לסכום את ערכי מספר בלבד.אלא "חותרים" לגורם השגיאה של n/a פה.ולכן עורכים פונקצייה שלמה של vlookup ואז אומרים לפונקציית filter להחזיר לנו רק תוצאות שלא התקבל (not) בהן שגיאת n/a (כלומר שלא התקיים התנאי של isna והתקבל false)
וכל התאים האלו מסוכמים לבסוף עם פונקציית sum שעוטפת את כל הטוב הזה.
אני יודע שהתהליך פה הוא ארוך...הצגתי את הפיתרון כאן רק להדגים את הטכניקה והיופי שבשיטה זו.(ובאגב השבוע נשאלתי על ידי חבר בשאלה דומה.אך הוא חיפש לבצע סינון לתאים שהתקבלו בהם n/a בלבד .והמניפולציה פה היא אחת הדרכים..וכדאי להכיר אותה)
וכפי שאני נוהג לומר תמיד "תהנו גם מהדרך ולא רק מהתוצאה..."
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-12372336
@מליה אם משהו לא ברור פה.נא לשאול ואשיב בשמחה

בהצלחה רבה!

 

[יעקב ויצמן]

שלום לכל החברים ובראשם ל @יעקב ויצמן, רציתי לשאול אם מישהו יוכל להסביר לי מהי האופציה השלישית ברשימה המוצעת לשמירת כל קובץ אקסל-
Excel Binary Workbook ( *. xlsb), ופירושו כפי שיצא לי בגוגל- קובץ בינארי של Excel, המבטיח ביצועים מהירים יותר מקבצי XLSX.
יש לי קובץ עם הרבה פקודות מאקרו ושמתי לב שכששמרתי אותו בסוג זה הוא באמת נפתח ונסגר לי יותר מהר.
השאלה היא מה באמת המשמעות של הסוג הזה, מה בינו לבין קובץ מאקרו רגיל, למה הוא מתאים, והאם יש סיכון לתקלות בשמירה בסוג הזה.
אשמח לכל מידע בנושא, תודה רבה.

באופן כללי .פורמט xlsb מיועד לקבצים עם נפח רב העולה על 10 מגה-בייט
אם משום "העומס" בנוסחאות או בגלל ריבוי פקודות מאקרו או שמדובר בקובץ "כבד" של עשרות אלפי שורות.
קובץ עם סיומת xlbs גורם "לכיווץ" בנפח .וכך הקובץ תופס פחות מקום באחסון ובעיקר הקובץ יותר נפתח /נטען במהירות.
בקבצי מאקרו קטנים שלא נתקעים ניתן בהחלט להשתמש בפורמט הרגיל עם סיומת xlsm
אם הנך מרגיש שהקובץ תקוע תבצע לו שמירה בשם עם סיומת xlsb
לפרטים נוספים ראה בלינק המצורף

excel-me.net

This domain may be for sale!

www.excel-me.net

בהצלחה!

 

[I believe]

באופן כללי .פורמט xlsb מיועד לקבצים עם נפח רב העולה על 10 מגה-בייט
אם משום "העומס" בנוסחאות או בגלל ריבוי פקודות מאקרו או שמדובר בקובץ "כבד" של עשרות אלפי שורות.
קובץ עם סיומת xlbs גורם "לכיווץ" בנפח .וכך הקובץ תופס פחות מקום באחסון ובעיקר הקובץ יותר נפתח /נטען במהירות.
בקבצי מאקרו קטנים שלא נתקעים ניתן בהחלט להשתמש בפורמט הרגיל עם סיומת xlsm
אם הנך מרגיש שהקובץ תקוע תבצע לו שמירה בשם עם סיומת xlsb
לפרטים נוספים ראה בלינק המצורף

excel-me.net

This domain may be for sale!

www.excel-me.net

צפה בקובץ המצורף 1585762
בהצלחה!

תודה רבה על התשובה המפורטת.

 

[מדויק]

חשבתי רבות לסדר את כל הסוגיא כאן דבר דבור על אופניו, אני אדגים את מהלך הדברים בדוגמאות וצילומי מסך עם ההסבר כמובן:
סעיף 1:
בצילום מסך כאן
צפה בקובץ המצורף 1585413
בתא a8 אנו מבקשים מפונקציית len לספור את ס"ך התוים (מבלי להיכנס עכשיו להבדלים בין פונקצייה זו לפונקציות דומות כמו counta בשאלת אילו תוים נכללים ואלו לא...)שבכל תא מתא a1 עד a5
עד כאן הכל טוב,יפה ומובן.
סעיף 2:
כעת אנו נוסיף בתא a15 תנאי למשוואה ,התנאי הוא לכלול בספירה רק תאים שמכילים מעל 3 תוים
צפה בקובץ המצורף 1585417
כאן כללי הספירה כבר שונים.יש כאן תנאי!
תנאי נמדד בהחזר של true או false.סה טו!
ולכן אין כאן החזר של מספר התוים בכל תא אלא תשובה כלפי השאלה של התנאי.(אני מדגיש זאת וחשוב לזכור את הניואנס הזה)
הפונקצייה מחזירה בשרשור את סדר התאים והתשובה האם מדובר בtrue או false
הלאה,הבה נתקדם שלב...
סעיף 3:
אם נרצה לדעת במספר כמה תאים בסה"כ הוחזר true
לכאורא הפיתרון הוא לעטוף את כל הסיפור הזה בפונקציית sumproduct
אז הנה עשיתי זאת בתא a22
צפה בקובץ המצורף 1585426
הופ!! התשובה היא 0
מה קרה פה בדיוק?
ראשית.צריך להזכיר שלמרות שפונקצייה זו בדרך כלל מקבלת שני(או יותר) טווחים/מערכים ,מכפילה(כברירת מחדל) בין המערכים (תא כנגד תא באותה שורה) ואז מסכמת את התוצאות,
כאשר יש רק מערך אחד היא פשוט מסכמת את כל התאים שבו.
ומה קרה פה ?
התשובה היא שההחזר שהתקבל פה הוא ביטוי לוגי.הביטוי אמנם שווה ערך בפוטנציאל שלו לערך מספרי של 0 או 1
אבל הפונקצייה "לא יודעת" לבצע לבד את ההמרה הזו מביטוי לוגי לערך מספרי.
שהפונקצייה עושה חיבור של כל התוצאות ,אז כל התוצאות שוות ל0 .
0+0+0+0+0=0
סעיף 4:
אנו צריכים "לעזור לפונקצייה" ולתת לה כלי שיתווך לה שתוכל לבצע את ההמרה הזו בקלות-איזה push קטן...צפה בקובץ המצורף 1585450
כאן בתא a25 זה קורה סוף סוף בעזרת טכניקת Double unary , התוצאה היא 3
3 פעמים של החזר true . כל true שכזה שווה ערך ל1
1+1+1=3
אדגיש כאן נקודה חשובה כי כל ההסבר כאן תקף לגיליון אקסל בלבד.
בגיליון שיטס ישנם חשבונות אחרים והכרח לעטוף את הפונקציות בפונקציית arryformula וזה כבר משנה את כל כללי המשחק והלוגיקה...

@למה כעת אמשיך ואתייחס לשאלותיך
שאלה 1:
בגיליון אקסל ללא הוספת דאבל מינוס התוצאה תהיה 0
צפה בקובץ המצורף 1585455
כאן זה עם דאבל מינוס -כאשר הדאבל מינוס מופיע לפני הסוגריים עם התנאי
רק כך זה עובד!

שאלה 2:
אני אדגים ואסביר שורה אחת במערך פה ותקיש על כל יתר השורות
צפה בקובץ המצורף 1585457
בחשבון של הפונקצייה
הואיל והתקיים התנאי וכתוב בתא a2 אברהם
זה כאילו כתוב בתא a2 את הספרה 1.ואת ערך זה הפונקצייה כופלת בתא שבעמודה e מאותה שורה, קרי תא e2
שכתוב בה 30 ש"ח
אז 1*30= 30
וכן הלאה והלאה בכל השורות למטה.לאחר מכן הפונקצייה סוכמת את כל המכפלות והתוצאה 431

שאלה 3:
בנידון כאן אין נפקא מינה לתוספת ארגומנט שיהיה מופנה לעמודה שכתוב בה 1
כי יש כאן פלט של 1 כאשר מתקיים התנאי ומה התועלת במכפלה נוספת ב1
אם אחזור לדוגמא בשאלה 2 אז
1*30=30
וכן 1*1*30=30
וכן 1*1*1*30=30

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

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

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

www.prog.co.il

ולכן כל הפלט בארגומנט השני שבפונקציית xlookup כאשר מתקיים התנאי שווה ל1
הxlookup בסופו של ענין "מסתכל" על הערך שכתוב בארגומנט הראשון שכתוב בו 1
"ורושם לעצמו בצד" את כל התוצאות שהתקבל בהן הערך 1 מהארגומנט השני ומחזיר את הערך התחתון ביותר(תוצאה בודדת)
בדוגמא ששאלת לא התקיים מיסגור שני המערכים בסוגריים עם סימון כפל ממשי בינהם
ואם ההינו עושים כך. זה היה "כופה" על הפונקצייה את ההמרה הזו
רוצה הוכחה? הנה...
תראה כאן שוב את תא g25 לאחר עריכה מחודשת
צפה בקובץ המצורף 1585489
הכל עובד פתאום! ללא סימון חיסור כפול...
ג. יש כאן שוני במבנה הארגומנטים שבפונקציות וכן בפעולה הבסיסית של הפונקציות.
פונקציית xlookup היא לא נחשבת פונקצייה "מתאמטית"
תפקידה היא להחזיר תוצאה (בארגומנט 3) כאשר מותאמת השוואה בין ארגומנט 1 לארגומנט 2
אבל פונקציית sumproduct בבסיסה היא פונקצייה שמבצעת פעולות מתאמטיות
וכאשר מתקיים תנאי הפונקצייה מחזירה 1
מכאן ולהבא אין צורך להשוות את ההחזר שהתקבל באיזה ערך, הפונקצייה פשוט סוכמת את התוצאה וזהו,
ולכן להוסיף עוד ארגומנט שכתוב בו 1 בכל הטווח זה מיותר לחלוטין ולא מוסיף שום דבר.

מצורף גיליון אקסל עם הדוגמאות שהבאתי לעיל

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

כמו שכתבת שיעור כללי שלם עם "עמקות"...

אבל עוד לא הבנתי למה חיסור כפול זה true ?

 

[יעקב ויצמן]

כמו שכתבת שיעור כללי שלם עם "עמקות"...

אבל עוד לא הבנתי למה חיסור כפול זה true ?

סימן חיסור כפול שמוסיפים.זה לא true
אלא זה "מתווך" שעוזר לאקסל לתרגם את הביטוי לוגי true ל1.ואת false ל0

 

[מים אחרונים]

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

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

מה אני כותב בתכלס?

 

[מים אחרונים]

מה אני כותב בתכלס?

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

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

ואיפה אני מכניס את הנוסחה?

https://docs.google.com/spreadsheets/d/1yYb7156HawGik5bDgNMuFeUCtniLSQ9es7VH5cN6CIY/edit#gid=0

 

[יעקב ויצמן]

נא אשר גישה למסמך

 

[מים אחרונים]

קיבלתי את הקוד הבא מצ'אט GBT
מה אני עושה איתו?

בכיף! הנה דוגמה לקוד VBA באקסל שמסנן את המילים בעמודה D ומעביר אותן לגליון נפרד בשם "טופל בס"ד":

```vba
Sub סננת_מילים()
    Dim סורה As Range
    Dim צילום_סורה As Range
    Dim גליון_סורה As Worksheet
    Dim גליון_מקור As Worksheet
    Dim מילה As String
    
    ' הגדרת גליון מקור
    Set גליון_מקור = ThisWorkbook.Sheets("Sheet1") ' שנה את "Sheet1" לשם הגליון המתאים
    
    ' צור גליון חדש בשם "טופל בס"ד אם אינו קיים כבר
    On Error Resume Next
    Set גליון_סורה = ThisWorkbook.Sheets("טופל בס""ד")
    On Error GoTo 0
    If גליון_סורה Is Nothing Then
        Set גליון_