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

[יעקב ויצמן]

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

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

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

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

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

בהצלחה רבה!

 

[יעקב ויצמן]

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

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

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

 

[- YH -]

כן. באמצעות הפונקציה VLOOKUP.

• A2 הוא הערך שאתה רוצה לחשב את הסכום שלו.
• גליון1 הוא שם הגליון הראשון שבו אתה מחפש את הערך.
• A:B מציין את הטווח בו אתה מחפש את הערך ואת הערך שברצונך לחשב ממנו סכום.
• 2 הוא מספר העמודה בה אתה מחפש את הערך שתרצה לחשב ממנו סכום.
• FALSE מציין שאתה מחפש התאמה מדויקת ולא התאמה חלקית.

בסוף הקוד צריך 2 ( ?

 

[יעקב ויצמן]

כן. באמצעות הפונקציה VLOOKUP.

• A2 הוא הערך שאתה רוצה לחשב את הסכום שלו.
• גליון1 הוא שם הגליון הראשון שבו אתה מחפש את הערך.
• A:B מציין את הטווח בו אתה מחפש את הערך ואת הערך שברצונך לחשב ממנו סכום.
• 2 הוא מספר העמודה בה אתה מחפש את הערך שתרצה לחשב ממנו סכום.
• FALSE מציין שאתה מחפש התאמה מדויקת ולא התאמה חלקית.

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

מצורף כאן הגיליון דוגמא.

אם הגיליון "האחר" נמצא בחוברת עבודה שונה אז:-
בשיטס ניתן להזין את אותו טווח בגיליון בעזרת פונקציית importrange ראה כאן
באקסל זה ניתן בעזרת power query ראה כאן

 

[שלוות נפש]

זה הזכיר לי מעט את המעשה עם שק הנוצות כאן

חסום לי...

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

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

 

[- YH -]

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

אם הגיליון "האחר" נמצא בחוברת עבודה שונה אז:-
בשיטס ניתן להזין את אותו טווח בגיליון בעזרת פונקציית importrange ראה כאן
באקסל זה ניתן בעזרת power query ראה כאן

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

 

[יעקב ויצמן]

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

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

 

[יעקב ויצמן]

חסום לי...

צירפתי כאן pdf

הרבה עבודה - ובפרט שיש לי טבלה גדולה
יש דרך לעשות את זה יותר קצר?

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

וכן אם אני שם בטווח את כל הטבלה זה לא מביא נתונים נכונים - רק כשאני לוקח 2 שורות

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

 

[- YH -]

יש לי תא בגיליון 2 שאני רוצה שיקושר לגליון 1 (בכל שינוי בגליון 2 ישתנה גם בגיליון 1), איזה נוסחה צריך?

 

[יעקב ויצמן]

יש לי תא בגיליון 2 שאני רוצה שיקושר לגליון 1 (בכל שינוי בגליון 2 ישתנה גם בגיליון 1), איזה נוסחה צריך?

לכתוב בתא שבגיליון 1 ככה

תא a1 הוא רק כדוגמא
תזין את התא הרלוונטי

 

[- YH -]

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

 

[יעקב ויצמן]

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

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

 

[יעקב ויצמן]

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

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

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

בהצלחה רבה!

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

 

[- YH -]

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

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

 

[יעקב ויצמן]

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

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

 

[שלוות נפש]

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

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

 

[יעקב ויצמן]

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

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

 

[- YH -]

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

 

[יעקב ויצמן]

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

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

 

[- YH -]

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