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

[אבי2021]

ראשית.תודה לשניכם על הפידבק ועל ההארות הנפלאות.

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

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

גוט שאבע'ס

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

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

 

[יעקב ויצמן]

גם ככה הם עובדים קשה, ועשרות מליונים ברחבי העולם מנצלים אותם מידי יום...

רק אציין כי על פי הצילום מסך המצורף פונקציית xlookup הפציעה לעולמינו בשעה טובה באוגוסט 2019.זאת לאחר 35 שנות קיומה של פונקציית vlookup
ומה לגבי הדירוג "והרייטינג"?
פונקציית vlookup דורגה במקום 3 אחרי פונקציות sum.ו-average

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

ירידה בדירוג? כן! אולי "ירידת הדורות" זו הסיבה..
ומה עם xlookup? לא מוזכרת כלל.
העיקר שאפשר לנהל עסקים ולספור מזומנים מצוין עם sum.average.count.min.max ועוד..סה טו..
לא לחינם נאמר שהכסף הוא המנוע וצירו המרכזי של העולם..
אז שהזכרת בדבריך על "עשרות מיליונים" של משתמשים בפונקציית xlookup לא נותר לאחל-ובאמת...= לו יהי...

 

[יעקב ויצמן]

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

@אבי2021 היקר!
ראה כאן דוגמא ל-vba עבור פונקציית vlookup להחזרת מספר תוצאות

Excel - How to vlookup to return multiple values?

I'm looking to use Excel to look up and return multiple reference values for a given key. VLookup does something very similar to what I need - but only returns a single match. I assume it'll invo...

superuser.com

כך נראות התוצאות

שים לב שהתוצאות בהיותן רבות,מוחזרות בתא אחד ומופרדות בפסיק.
המניע לכך.הוא למניעת שגיאה מהאקסל.
השגיאה היא כי הנתונים "רוצים" להשתלשל למטה.וזה יגרום "לדריסה" של נתונים בתאים למטה.
ואם יש את נפשך בפיתרון חילופי,ניתן להשתמש "בעטיפת" פונקציית vlookup בפונקציית transpose כפי שכתבתי בזמנו בסוגיא דומה של פונקציית indirect ותאי אימות נתונים,כאן (בשם @שימי .ב )
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-11896658

בהצלחה רבה

 

[BB1234]

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

 

[הילוך גבוה]

טור חמישי יתרה בכל שורה לידיעה מה המצב בקופה

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

 

[BB1234]

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

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

 

[הילוך גבוה]

@אבי2021 @קול תורה
תקשיבו סיפור.לפני מספר ימים חבר שלי פנה אלי עם בקשת עזרה באקסל.
יש לו טבלה של רשימת נבחנים עם ת.ז ,שם,ומשפחה .
בוצעו 3 מבחנים.ויש פילוח של תוצאות.או נבחן(ולא עבר) או עבר (בקושי) או עבר בהצלחה.
החבר רצה שאבצע נוסחא שתחזיר לפי תא חיפוש 3 תוצאות של עמודות (תז.שם.ומשפחה).
עד כאן יפה מאוד!אפשר להסתדר עם זה על ידי פונקציות vlookup או xlookup...
הבעיה הגדולה שהחבר לא הסתפק בתא חיפוש אחד עם קריטריון 1.אלא 3 קריטריונים!!!
דהיינו שהפונקציה תחזיר 3 תוצאות(ת.ז,שם,ומשפחה).של מי שעמד בכל 3 הקריטריונים.והם שבכל 3 המבחנים הוא עבר בהצלחה...
פה זה כבר היה בהחלט אתגר מורכב.
ובכן,כדרכי חיפשתי וחיפשתי עד שמצאתי את התשובה.
הפיתרון הוא באותה פונקציית xlookup עם כמה טריקים גאוניים שטרם הכרתי...
הפונקצייה מסוגלת "לעבד" 3 (ואף יותר)תאי חיפוש.
לגבי הטווח -אנו עוטפים אותו בפונקציית arrayformula.
והתוצאה לפניכם.
צפה בקובץ המצורף 1404562
בשטח הצבוע באדום תוצאות ת.ז 333.הוא זה שעבר את 3 המבחנים בהצלחה.
רבותי..הפונקצייה הזו גאונית במניפולציות שניתן לעשות איתה.מדובר בהחלט בפונקצייה "רבת עוצמה".

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

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

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

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

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

 

[יעקב ויצמן]

אני לא מכיר בכלל את פונקציית arrayformula וגם לא בדיוק הבנתי איך השתמשת בה
מה שכן ניסיתי לעשות זאת ללא arrayformula והצלחתי
הנה:
צפה בקובץ המצורף 1407454

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

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

 

[הצ'ייסר]

תכנות - הדרכות באקסל

אשכול סרטונים ללימוד אקסל של אקסליסט

www.prog.co.il

 

[יעקב ויצמן]

טור חמישי יתרה בכל שורה לידיעה מה המצב בקופה

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

שים לב לשורה 3 .האם נכון לומר שכעת מר x נמצא בגירעון של 500 ש"ח.מה עם 5000 שח!!!! של הכנסות שנכתבו שורה קודם???
איפה ההיגיון פה?
דברי @הילוך גבוה נאמרו ביושר ובצדק

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

תראה עכשיו בשטח הצהוב

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

 

[קול תורה]

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

אני אסביר.
"מאזן" כלכלי הן במשק הביתי והן בעסק.לא יכול להימדד על סמך כל פעימה בודדת של הכנסה והוצאה.
מאזן נכון מתחיל בס"ך הכנסות והוצאות יומי/חודשי/רבעון/חציון /שנתי וכו..
אני אתן דוגמא שנבין יחד את האבסורד שבשיטה.
צפה בקובץ המצורף 1407636
שים לב לשורה 3 .האם נכון לומר שכעת מר x נמצא בגירעון של 500 ש"ח.מה עם 5000 שח!!!! של הכנסות שנכתבו שורה קודם???
איפה ההיגיון פה?
דברי @הילוך גבוה נאמרו ביושר ובצדק

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

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

 

[יעקב ויצמן]

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

אם לטבלה הזו היתה הכוונה.זה כבר אחרת.
לא כך הבנתי בניסוח השאלה.סורי...

 

[קול תורה]

אם לטבלה הזו היתה הכוונה.זה כבר אחרת.
לא כך הבנתי בניסוח השאלה.סורי...

האם תוכל לתת פתרון גם לזה

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

זה יהיה לעזר גם לי ללמוד טכניקות ודרכים איך לעבוד עם אקסל

 

[דסי כהן]

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

 

[יעקב ויצמן]

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

הודעה בנושא 'רוצה לעזור לכם באקסל' https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-12148798
ראי כאן.
לשאלה נוספת נא לצרף גיליון גוגמא

 

[יעקב ויצמן]

האם תוכל לתת פתרון גם לזה

זה יהיה לעזר גם לי ללמוד טכניקות ודרכים איך לעבוד עם אקסל

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

 

[דסי כהן]

תודה רבה על המענה
לא הצלחתי להבין מזה
אשמח לעזרה לאיחוד הטור של משפחה ופרטי (מצו"ב)

 

[יעקב ויצמן]

תודה רבה על המענה
לא הצלחתי להבין מזה
אשמח לעזרה לאיחוד הטור של משפחה ופרטי (מצו"ב)

מצורף גיליון עם 3 שיטות לפיתרון

 

[דסי כהן]

וואו תודה
אשמח לדעת ולהבין את כל השלבים איך עושים זאת

 

[יעקב ויצמן]

וואו תודה
אשמח לדעת ולהבין את כל השלבים איך עושים זאת

מצורף בלינק הסבר מפורט

כיצד ניתן לחבר מספר תאי טקסט יחדיו באקסל? | אקסל מאסטר

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

www.excelmaster.co.il

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