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

[יעקב ויצמן]

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

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

פורום תוכנות מייקרוסופט אופיס

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

www.prog.co.il

בהצלחה

 

[נ.ב.]

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

 

[• יצחק •]

אשמח לעזרה, איך אני יוצר כזה טבלה באקסל?

 

[יעקב ויצמן]

אשמח לעזרה, איך אני יוצר כזה טבלה באקסל?
צפה בקובץ המצורף 1526386

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

 

[• יצחק •]

ויש אפשרות שבשורה 5 הסכום יקושר מקובץ אקסל אחר?

 

[קודם כל-בן אדם.]

ויש אפשרות שבשורה 5 הסכום יקושר מקובץ אקסל אחר?

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

=SUM(VLOOKUP(A2, גליון1!A:B, 2, FALSE))

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

 

[HennyS]

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

יש למיקרוסופט מסד נתונים ענק בשם contoso בדיוק למטרה זו, באתר שלהם

 

[נ.ב.]

יש למיקרוסופט מסד נתונים ענק בשם contoso בדיוק למטרה זו, באתר שלהם

אפשר לקבל לינק למסד הנתונים?

 

[HennyS]

אפשר לקבל לינק למסד הנתונים?

סורי, אין לי
תעשי גוגל microsoft contoso

 

[יעקב ויצמן]

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

בחיפוש גוגל ניתן להתרשם ולהוריד קבצי אקסל דוגמא להנ"ל.
גם באתר הרשמי של מייקרוסופט 365
Microsoft Create ניתן למצוא שלל תבניות חינמיות לכל הנ"ל מלאות בפונקציות ועיצוב ויזואלי מרשים.
חשוב תמיד לזכור כי כל עסק הוא בסופו של דבר אינדיבידואלי...ואני מתכוון גם לניהול האקסל לעסק.אבל לצורך הדגמה עם "קוים כלליים" לגיליונות האקסל ,הנ"ל יכול להתאים לכך.
וכעצה טובה אוסיף במאמר מוסגר שאם כבר קורס אז כדאי שזה יהיה "עד הסוף וכמה שיותר כלים"...
ואני מתכוון ללימוד תכני אקסל למתקדמים ויותר מכך.הכולל שימוש בכלים לניתוח עסקי החל מ-pivot table,
dashboard ,power query, ועד power bi
בהצלחה!

 

[יעקב ויצמן]

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

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

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

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

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

בהצלחה רבה!

 

[יעקב ויצמן]

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

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

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

 

[• יצחק •]

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

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

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

 

[יעקב ויצמן]

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

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

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

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

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

 

[שלוות נפש]

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

חסום לי...

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

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

 

[• יצחק •]

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

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

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

 

[יעקב ויצמן]

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

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

 

[יעקב ויצמן]

חסום לי...

צירפתי כאן pdf

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

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

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

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

 

[• יצחק •]

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

 

[יעקב ויצמן]

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

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

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