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

[יעקב ויצמן]

גם אני ערכתי פה באמצעות נוסחת XLOOKUP, מצו"ב.

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

כדי לחסוך לזכור תמיד

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

ישר כח.פיתרון מעולה ורעיון יפה וגאוני.

האמת שלאחר שהצעתי את הפיתרון לעיל בעזרת xlookup.חיפשתי ופישפשתי למצוא פיתרון הולם עבור פונקציית filter.ומצאתי בס"ד 2 פיתרונות נוספים:
פיתרון 1:
בפיתרון זה פונקציית filter (בתוספת row)עטופה בפונקציית vlookup ומחזירה ערך בודד בלבד הכי אחרון ברשימה.ככה:

הלוגיקה כאן קשה להסבר.אבל בשורה תחתונה זה פשוט עובד!
לפיתרון זה נדרש מיון מהקטן לגדול בעמודת תאריך לעידכון מחיר
פיתרון 2:
בפיתרון זה יש קומבינצייה של פונקציות index + sequence(שדיברנו בה רבות לאחרונה..זוכרים?) וכמובן filter

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

FILTER on first or last n values

To FILTER and extract the first or last n values, you can use the FILTER function together with INDEX and SEQUENCE. In the example shown, the formula in D5 is: =INDEX(FILTER(data,data""),SEQUENCE(3,1,1,1)) where data is the named range B5:B15 and n is 3.

exceljet.net

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

לסיכום .יש כאן מגוון פיתרונות נפלאים .כל פיתרון עם יופי וסגנון יחודי.והבוחר יבחר....
תוצאות איכותיות של סיעור מוחות...
ושוב ישר כח לכם @קול תורה @I believe
ותודה לך @למה על שאלתך הנפלאה.
החכמנו כולנו!

מצורף גיליון שיטס

 

[יעקב ויצמן]

שלום!
יש לי שאלה יותר באקסל של גוגל שיטס.
עשיתי עמודה הניתנת לבחירה מתוך רשימה, לדוג' לא טופל/טופל/בטיפול.
אני מעוניין בעמודה נוספת שתעביר את הנתונים כך שאם אני אבחר טופל, יתועבר השורה עם הנתונים שלה, לגליון אחר.
או אפ' לקובץ שונה לגמרי.
מה עושים?
@מאסטר באקסל

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

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

 

[למה?]

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

ישר כח.פיתרון מעולה ורעיון יפה וגאוני.

האמת שלאחר שהצעתי את הפיתרון לעיל בעזרת xlookup.חיפשתי ופישפשתי למצוא פיתרון הולם עבור פונקציית filter.ומצאתי בס"ד 2 פיתרונות נוספים:
פיתרון 1:
בפיתרון זה פונקציית filter (בתוספת row)עטופה בפונקציית vlookup ומחזירה ערך בודד בלבד הכי אחרון ברשימה.ככה:
צפה בקובץ המצורף 1583325
הלוגיקה כאן קשה להסבר.אבל בשורה תחתונה זה פשוט עובד!
לפיתרון זה נדרש מיון מהקטן לגדול בעמודת תאריך לעידכון מחיר
פיתרון 2:
בפיתרון זה יש קומבינצייה של פונקציות index + sequence(שדיברנו בה רבות לאחרונה..זוכרים?) וכמובן filter
צפה בקובץ המצורף 1583340
בפיתרון זה נדרש מיון להיפך דהיינו מהגדול לקטן .כי הרעיון בפיתרון להחזיר תוצאה ראשונה ועליונה של filter
על הלוגיקה הגאונית פה ניתן ללמוד בלינק המצורף

FILTER on first or last n values

To FILTER and extract the first or last n values, you can use the FILTER function together with INDEX and SEQUENCE. In the example shown, the formula in D5 is: =INDEX(FILTER(data,data""),SEQUENCE(3,1,1,1)) where data is the named range B5:B15 and n is 3.

exceljet.net

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

לסיכום .יש כאן מגוון פיתרונות נפלאים .כל פיתרון עם יופי וסגנון יחודי.והבוחר יבחר....
תוצאות איכותיות של סיעור מוחות...
ושוב ישר כח לכם @קול תורה @I believe
ותודה לך @למה על שאלתך הנפלאה.
החכמנו כולנו!

מצורף גיליון שיטס

וואו!!!!!!

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

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


ותודה רבה לכם @קול תורה וכמובן ל @יעקב ויצמן ולכולם (ונפרגן גם למר 'אל ערפאת סידיק' שנתן דוגמא של "כל העולם לא נברא אלא בשבילי")

 

[למה?]

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

How to Use VLOOKUP with Multiple Criteria Including a Date Range: 2 Examples - ExcelDemy

This article illustrates 2 well-explained examples to VLOOKUP a value that fulfills multiple criteria including a date range in Excel.

www.exceldemy.com

העדפתי להשתמש בפונקציית xlookup (על פני vlookup) בעיקר בגלל התוספת בארגומנט 6 שאכתוב להלן.
צפה בקובץ המצורף 1582681
בנוסף לכל הקשיים של מספר קריטריונים.יש כאן קושי נלווה שבעמודה F יש (לפעמים)מספר תאריכים למוצר אחד.
כמו דגים שיש להם עדכון מחיר מינואר 2024 וכן ממרץ 2024.
פונקציות vlookup וxlookup כידוע,שישנם כמה תוצאות מתאימות הן מחזירות את התוצאה בשורה הכי עליונה.
כך שאם כתוב על רכישת דגים מחודש אפריל 2024 יוחזר מחיר חדש שיופיע עליון.
וכדי להתמודד עם הבעיה הזו.יש להקפיד בכל שינוי על מיון עמודת F מהקטן לגדול.
ומה התועלת בכך?
בדיוק לכך הוספתי בארגומנט השישי את 1-
כך שתמיד יוחזר המחיר החדש (הכי אחרון/תחתון בטווח)הכי רלוונטי.זהו טריק נפלא שלעצמו!
בחוסר התאמה יוחזר כאן תא ריק.

בהצלחה רבה!

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

 

[חיים5]

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

 

[למה?]

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

נוסחת 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

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

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

האקסל שלי 'השתגע'. יש לו נוסחה כבר שנים רבות לחשב תשלומים, ועכשיו מכניסים לדוג' על תלמיד ששילם 500 ובסיכום כתוב 7833, מחליפים ל-5 נהיה 12440 האם יש באג בקובץ? האם יתכן שזה קובץ מדי כבד? דחוף ממש

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
לפרטים נוספים ראה בלינק המצורף

XLSM Vs. XLSB Files - What’s The Difference? - Excel Me

Unlock the secrets of Excel file formats! Discover the differences between XLSM and XLSB files, and choose the best format for your needs. Read now!

www.excel-me.net

בהצלחה!

 

[I believe]

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

XLSM Vs. XLSB Files - What’s The Difference? - Excel Me

Unlock the secrets of Excel file formats! Discover the differences between XLSM and XLSB files, and choose the best format for your needs. Read now!

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

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

האקסל שלי 'השתגע'. יש לו נוסחה כבר שנים רבות לחשב תשלומים, ועכשיו מכניסים לדוג' על תלמיד ששילם 500 ובסיכום כתוב 7833, מחליפים ל-5 נהיה 12440 האם יש באג בקובץ? האם יתכן שזה קובץ מדי כבד? דחוף ממש

www.prog.co.il

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

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

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

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

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