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

[• יצחק •]

ואם שיניתי את השם של הגיליון לכתוב לפי זה?

 

[יעקב ויצמן]

ואם שיניתי את השם של הגיליון לכתוב לפי זה?

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

 

[יעקב ויצמן]

השאלה כאן היא עוד יותר מעניינת ומאתגרת מקודמתה...
זה הזכיר לי מעט את המעשה עם שק הנוצות כאן
אבל גם זה אפשרי עם עוד קצת יצירתיות ומניפולציות שונות.
צפה בקובץ המצורף 1526532
מפאת קוצר זמני אסקור את השלבים ממש בקצרה
בשלב א
יש להעתיק את הטבלה למעלה בצד שמאל ולהדביקה כערכים בלבד.
בשלב ב
יש להזין ידנית (יבוא סטנדרטי עם uniqu לא יהיה מושלם כאן.כי קיימים רווחים שאנו חפצים בהם..אבל גם פעולה כזו ניתנת בסופו של דבר.וזה עוד תהליך בפני עצמו עם מספר שלבים.ראה כאן דוגמא לטכניקה ביצועית לכך) את הערכים הצבועים בירוק.
אחכ לחשב את העמודות הסגלגלות והכתומות שצבעתי.יש לשים לב לקיבועים השונים
בסה"כ מדובר בטכניקות ומשחק עם פונקציות vlookup ו-xlookup
אחכ לחשב את עמודות שצבועות בתכלת
ואחכ לחשב את העמודות הצבועות באדום.
בשלב ג
בתא הצהוב יש להערים את טבלאות שלב ב בעזרת vstack
ולסיום בתאים הכחולים מבוצע יבוא נתונים בעזרת arryaformula עם עוד פיצ'ר קטן של הסרת מספר 1 בעזרת פונקציית substitute

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

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

בהצלחה רבה!

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

 

[• יצחק •]

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

התכוונתי על שתי חוברות עבודה שונות

 

[יעקב ויצמן]

התכוונתי על שתי חוברות עבודה שונות

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

 

[שלוות נפש]

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

איפה מוגדר שהיבוא הוא רק של 2 שורות ?

 

[יעקב ויצמן]

איפה מוגדר שהיבוא הוא רק של 2 שורות ?

בארגומנט השני של vlookup
שים לב שטווח השורות לחיפוש הוא שורות 21-22

 

[• יצחק •]

יש לי טבלה עם שתי עמודות שם פרטי ושם משפחה, אשמח לעזרה איך אפשר למזג את שתי העמודות שהשם והמשפחה יהיה בעמודה אחת

 

[יעקב ויצמן]

יש לי טבלה עם שתי עמודות שם פרטי ושם משפחה, אשמח לעזרה איך אפשר למזג את שתי העמודות שהשם והמשפחה יהיה בעמודה אחת

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

 

[• יצחק •]

ויש אפשרות לעשות ההפך? להוציא את שם המשפחה לעמודה נפרדת?

 

[יעקב ויצמן]

ויש אפשרות לעשות ההפך? להוציא את שם המשפחה לעמודה נפרדת?

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

הודעה בנושא 'רוצה לעזור לכם באקסל' https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-12003704

 

[minach]

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

מדובר על אופיס 2019

 

[יעקב ויצמן]

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

מדובר על אופיס 2019

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

בהצלחה רבה!

 

[minach]

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

בהצלחה רבה!

דבר ראשון תודה ענקית!!
אבל יש כאן מספר דברים שלא הצלחתי להבין ובכדי שאוכל להשתמש עם זה נכון אני חייב להבין את הדברים לעומקם.
1. =IFERROR(__xludf.DUMMYFUNCTION("TRANSPOSE(UNIQUE(B10:B22))"),"מחשב") אשמח אם תוכל בבקשה לפרט לי מה בעצם כתבת בנוסחה הזו האמת שחוץ מUNIQUE וIFERROR לא הבנתי מה זה כל השאר וכ"כ לא הבנתי למה השתמשת איתם. כנ"ל לגבי השורה בתכלת לא בנתי את הנוסחה שם.
2. בעיה שניה, אני חייב שהטבלה שרציתי תהיה דינמית ולא סטטית ובקובץ דוגמה ששלחת לי לא משנה אם אני מוריד מוצר או מוסיף הטבלה לא משתנה לי וזה לא עוזר לי. האם יש אפשרות להשיג את התוצאה הזו ושהנתונים יהיו חיים וישתנו בהתאם לטבלת האם?

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

 

[יעקב ויצמן]

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

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

1. =IFERROR(__xludf.DUMMYFUNCTION("TRANSPOSE(UNIQUE(B10:B22))"),"מחשב") אשמח אם תוכל בבקשה לפרט לי מה בעצם כתבת בנוסחה הזו האמת שחוץ מUNIQUE וIFERROR לא הבנתי מה זה כל השאר וכ"כ לא הבנתי למה השתמשת איתם. כנ"ל לגבי השורה בתכלת לא בנתי את הנוסחה שם.

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

קצת הסבר על השלבים:
שלב 1.
בעמודה A הצבועה בירוק (הוספתי אותה כעמודה ריקה) יש להזין בתא a1 את פונקציית vstack שמקבצת את כל ערכי עמודות המוצרים ומחזירה אותם בטור אנכי ובעמודה אחת.
את פונקצייה זו עוטפת פונקציית unique שממזגת את כל הכפילויות.
את עמודה A ניתן להסתיר ללא חשש.
שלב 2.
בתא h2 פונקציית transpose הופכת ומייבאת את ערכי עמודה A במאוזן ולא במאונך.
שלב 3.
פונקציית פילטר עם טריק מסוים בתאים הצבועים בתכלת.
אם ניקח דוגמא את תא h2.אז אנו אומרים לאקסל.תחזיר לנו את ערכי מספר הזמנה מעמודה B(עד כאן הארגומנט הראשון).בתנאי ש...
אז בפונקציית פילטר קלאסית אנו יכולים להזין מספר תנאים .רק שאז כל התנאים צריכים להתקיים כמו אפקט פונקציית and.
וכאן אנו רוצים שאם המילה "מחשב" תופיע או בעמודה c או d או e או f .(כמו באפקט פונקציית or שמספיק שתנאי אחד התקיים)
בכל מצב האקסל יחזיר לנו את מספר ההזמנה שבעמודה b.
ולכן .הרעיון הוא ליצור כמה תנאים בפועל "שנדמים" לאקסל כתנאי אחד ארוך. (תוכל לראות את הראיה וההוכחה לכך,בעובדה שלמרות כל תנאים אלו, זה עדיין מוגדר בשורת הפונקציית כ-condition1)
וזאת על ידי פעולת חיבור בין התנאים כאשר כל תנאי "מסוגר" בפני עצמו.
מקווה שכעת הרעיון מובן...

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

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

 

[המושלמת$$]

אני רוצה לעשות עמודה עם תאריכים עוקבים כל ה15 לחודש
לדוגמא:
15/05/2023
15/06/2023
איך ממלאים את זה בקלות?
@מאסטר באקסל

 

[יעקב ויצמן]

אני רוצה לעשות עמודה עם תאריכים עוקבים כל ה15 לחודש
לדוגמא:
15/05/2023
15/06/2023
איך ממלאים את זה בקלות?
@מאסטר באקסל

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

 

[המושלמת$$]

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

ניסיתי לעשות את זה כבר.
רשמתי לו שני תאריכים
15/05/2023
15/06/2023
והוא המשיך להשלים לי את זה כך:
15/05/2024
15/06/2024
15/05/2025
15/06/2025
איך מגדירים את העמודה לפורמט תאריך?

 

[יעקב ויצמן]

ניסיתי לעשות את זה כבר.
רשמתי לו שני תאריכים
15/05/2023
15/06/2023
והוא המשיך להשלים לי את זה כך:
15/05/2024
15/06/2024
15/05/2025
15/06/2025
איך מגדירים את העמודה לפורמט תאריך?

 

[minach]

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

טוב.אז הנה זה שוב קורה לנו...הקובץ במקור הוא של שיטס.וכאשר מעלים את הקובץ לאקסל נוספים באופן אוטומט כל מיני "עצמים בלתי מזוהים" אשר לא להם היתה כוונת המשורר...הסברתי זאת בעבר פעמים רבות במסגרת פורום זה.
בצילום מסך המצורף מפורטות הפונקציות בצורתן המקורית.תוכל לכותבן תו אחרי תו בגיליון שלך.
כבר בראשית דבריי.אבהיר ואדגיש שנדרשת כאן גרסת אקסל אופיס 365 עבור פונקציית vstack.
אם גרסה זו לא מותקנת אצלך.תיישם את הגיליון בגוגל שיטס בלבד(תעלה את הקובץ המצורף לדרייב ותפתח דרך השיטס)
צפה בקובץ המצורף 1530738
קצת הסבר על השלבים:
שלב 1.
בעמודה A הצבועה בירוק (הוספתי אותה כעמודה ריקה) יש להזין בתא a1 את פונקציית vstack שמקבצת את כל ערכי עמודות המוצרים ומחזירה אותם בטור אנכי ובעמודה אחת.
את פונקצייה זו עוטפת פונקציית unique שממזגת את כל הכפילויות.
את עמודה A ניתן להסתיר ללא חשש.
שלב 2.
בתא h2 פונקציית transpose הופכת ומייבאת את ערכי עמודה A במאוזן ולא במאונך.
שלב 3.
פונקציית פילטר עם טריק מסוים בתאים הצבועים בתכלת.
אם ניקח דוגמא את תא h2.אז אנו אומרים לאקסל.תחזיר לנו את ערכי מספר הזמנה מעמודה B(עד כאן הארגומנט הראשון).בתנאי ש...
אז בפונקציית פילטר קלאסית אנו יכולים להזין מספר תנאים .רק שאז כל התנאים צריכים להתקיים כמו אפקט פונקציית and.
וכאן אנו רוצים שאם המילה "מחשב" תופיע או בעמודה c או d או e או f .(כמו באפקט פונקציית or שמספיק שתנאי אחד התקיים)
בכל מצב האקסל יחזיר לנו את מספר ההזמנה שבעמודה b.
ולכן .הרעיון הוא ליצור כמה תנאים בפועל "שנדמים" לאקסל כתנאי אחד ארוך. (תוכל לראות את הראיה וההוכחה לכך,בעובדה שלמרות כל תנאים אלו, זה עדיין מוגדר בשורת הפונקציית כ-condition1)
וזאת על ידי פעולת חיבור בין התנאים כאשר כל תנאי "מסוגר" בפני עצמו.
מקווה שכעת הרעיון מובן...

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

שבוע טוב ומבורך!

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

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