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

[יעקב ויצמן]

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

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

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

ובכן יש כאן טבלת עזר.בתאים a4-a6 קיימת פונקציית counta.
ועל פיהם מתעדכנים הנתונים בתאים a8-a10.
בפונקציית vstack עצמה הטווחים ליבוא מייוצגים על ידי פונקציית indirect
ומה אנו מרויחים בכל המהלך הזה?
התשובה- שהטווח בפונקציית vstack הוא תמיד כאורך התאים בעלי טקסט בלבד וכך הטווח לא כולל אף פעם תאים ריקים.
(פונקציית counta לא סופרת תאים ריקים.זוכרים נכון?)
עצם בניית המהלך היצירתי והגאוני הזה ראויה להערכה ולשבח.אז באמת שאפו גדול...
אני נהנתי מאוד ממנו.ומקווה שגם אתם תהנו.

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

עוד פיתרון שישנו כאן.הוא תוך כדי ישום טכניקה שהזכרתי אותה פעמים רבות (וממש כאן לעיל בנושא של פונקציית unique)
הטכניקה היא לעטוף את פונקציית vstack בפונקציית filter ולהציב תנאי של "<> דהיינו אינו שווה לכלום/0.

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

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

 

[איזהו]

@מאסטר באקסל
יש לי רשימה ארוכה של מספרים, מתוכם אני בוחר 10% מהרשימה באופן אקראי. יש לי אותם בצד.
יש כזה מושג למצוא את ה-10% בלחיצת מקש.
בתוספת הסבר: יש לי רשימה, בא נאמר מ-1 עד 10, מתוכם אני בוחר את 3 ואת 7, עכשיו אני רוצה שהאקסל ימצא לי מיד את 3 ואת 7 (כמובן לא חיפוש....)
אשמח לדעת?
תודה רבה

 

[- YH -]

לכתוב בתא שבגיליון 1 ככה
צפה בקובץ המצורף 1526951
תא a1 הוא רק כדוגמא
תזין את התא הרלוונטי

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

 

[יעקב ויצמן]

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

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

 

[יעקב ויצמן]

משהו כאן לא ברור .סורי

@מאסטר באקסל
יש לי רשימה ארוכה של מספרים, מתוכם אני בוחר 10% מהרשימה באופן אקראי.

אם הערך הוא אקראי

יש לי אותם בצד.
יש כזה מושג למצוא את ה-10% בלחיצת מקש.
בתוספת הסבר: יש לי רשימה, בא נאמר מ-1 עד 10, מתוכם אני בוחר את 3 ואת 7,

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

 

[איזהו]

@יעקב ויצמן
תודה על הרצון לעזור
יש לי רשימה של כ-200 ילדים שלכל ילד יש קוד!
מי שעושה כך וכך במשך השבת, משאיר פתק עם הקוד שלו ללא השם (בכדי שזה יהיה אנונימי)
עכשיו אני צריך למצוא את שמות הילדים לפי הקודים מרשימת האקסל
מה עושים?

 

[יעקב ויצמן]

@יעקב ויצמן
תודה על הרצון לעזור
יש לי רשימה של כ-200 ילדים שלכל ילד יש קוד!
מי שעושה כך וכך במשך השבת, משאיר פתק עם הקוד שלו ללא השם (בכדי שזה יהיה אנונימי)
עכשיו אני צריך למצוא את שמות הילדים לפי הקודים מרשימת האקסל
מה עושים?

תראה בצילום מסך דוגמא לכך.

הפונקצייה היא xlookup "קלאסית" (כדאי ללמוד על הפונקצייה בלינק כאן).באותה מידה ניתן להשתמש בvlookup או filter.(תלוי בסדר העמודות)או בעזרת index+match
תוכל לראות בלינק המצורף שימוש בטכניקה זו במקרה דומה.וכן שימוש להיפך מזה על ידי חיפוש לפי שם -גם שאינו כל כך מדויק ומאוית נכון.
הודעה בנושא 'רוצה לעזור לכם באקסל' https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13016285
בהצלחה לך

 

[איזהו]

@יעקב ויצמן תודה על הרצון לעזור
עדיין לא זה מה שאני צריך
כי כנ"ל אני צריך למצוא 50 שמות מתוך 200 ואני רוצה בבת אחת
זה צריך להיות - לכאורה - משהו פשוט
בטור א' יש רשימה של 200 מספרים מ-1000 עד 1200
בטור ב' יש רשימה של 50 מספרים מתוך ה-200 הנ"ל, תסדר לי אותם אחד מול אחד

 

[יעקב ויצמן]

@יעקב ויצמן תודה על הרצון לעזור
עדיין לא זה מה שאני צריך
כי כנ"ל אני צריך למצוא 50 שמות מתוך 200 ואני רוצה בבת אחת
זה צריך להיות - לכאורה - משהו פשוט
בטור א' יש רשימה של 200 מספרים מ-1000 עד 1200
בטור ב' יש רשימה של 50 מספרים מתוך ה-200 הנ"ל, תסדר לי אותם אחד מול אחד

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

 

[איזהו]

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

ושוב ושוב תודה רבה
בטור א' ישם קוד ושם, בטור ב' יש רק שם, איך אוכל לדעת את השם בטור ב' ע"פ טור א'

 

[יעקב ויצמן]

ושוב ושוב תודה רבה
בטור א' ישם קוד ושם, בטור ב' יש רק שם, איך אוכל לדעת את השם בטור ב' ע"פ טור א'

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

מצורף גיליון מתוקן
בהצלחה

 

[איזהו]

הרעיון בדיוק אותו דבר .והוא אשר דיברתי לעיל...

ווואווי איזה צדיק
אין מילים

 

[יעקב ויצמן]

ווואווי איזה צדיק
אין מילים

בשמחה רבה.שמחתי לעזור
תודה לך על ההזדמנות לסייע במעט ממה שחנני אלוקים.
ובאגב.הזכרתי לעיל עוד שיטות לפיתרון,אז כממתק קטן אציג את השיטות הנוספות רק בכדי לעורר את הרצון והלב (כדרכם של מיני תרגימא ומתיקה...) ללמוד על פונקציות אלו ובעיקר על ההבדלים שבינהם.
יש לזכור,כי לכל פונקצייה יש D.N.A משלה עם לוגיקה רבה.צריך רק לחקור וללמוד.
זו רק דוגמא קטנה לעולמו הרחב והנפלא של האקסל...
להרחבה על פונקציית filter ראה כאן
להרחבה על פונקצית vlookup ועל פונקציות index+match ומעט מההבדלים שבינהן ראה כאן
מצורף כאן גיליון עם פתרונות זהים.אך עם 4 "מהלכים" שונים של חישוב.

בהצלחה לך

 

[ישראלי1]

אשמח לעזרה בנוסחת QUERY למה לא עובד לי הפניה לתא אחר

 

[xl3391]

אשמח לעזרה בנוסחת QUERY למה לא עובד לי הפניה לתא אחר



צפה בקובץ המצורף 1540113

על פניו נראה שצריך להוציא את D13 מחוץ למרכאות ולשרשר אותו עם &
עריכה: נ.ב. לא נכנסתי לקובץ ממבט ראשוני כך נראה הגיוני.

 

[יעקב ויצמן]

על פניו נראה שצריך להוציא את D13 מחוץ למרכאות ולשרשר אותו עם &
עריכה: נ.ב. לא נכנסתי לקובץ ממבט ראשוני כך נראה הגיוני.

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

 

[xl3391]

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

כן. השרשור האחרון אחרי הD10 נראה מיותר.

 

[יעקב ויצמן]

כן. השרשור האחרון אחרי הD10 נראה מיותר.

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

1.אמפרסנד המסומן בחץ האדום
2.גרשיים המסומנים בחץ כחול
3.ושוב גרשיים המסומנים בחץ ירוק.
לא ניתן להחסיר אף אחד מהאופרטורים הללו.!
לכל החסרה כזו תוחזר שגיאת value
תוכלו לנסות ולראות..

 

[יהודי פושט]

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

תודה לכולם!!

 

[יעקב ויצמן]

לאחר הזנת ההפנייה לתא קיימים: (על פי סדר כתיבתם בפונקציה.בצילום מסך הסדר הוא משמאל לימין)
צפה בקובץ המצורף 1540794
1.אמפרסנד המסומן בחץ האדום
2.גרשיים המסומנים בחץ כחול
3.ושוב גרשיים המסומנים בחץ ירוק.
לא ניתן להחסיר אף אחד מהאופרטורים הללו.!
לכל החסרה כזו תוחזר שגיאת value
תוכלו לנסות ולראות..

@ישראלי1
אנסה להסביר את ההיגיון כאן.
(זה לפחות המהלך שבניתי פה ולאחר מחשבה רבה...בעיניי, הדברים- של טעם הם.אם כי יתכן שלא דייקתי מספיק בחלק הלוגי...)
ובכן, באופן כללי ניתן לומר כי פונקציית query בשיטס היא "יקום בפני עצמו" עם שפה מיוחדת משלה.
בארגומנט השני -query string ניתן לבצע 10 סוגי שאילתות.דהיינו מחרוזות "פקודה" אותן השיטס צריך "לקרוא" ולבצע.
חלק מחוקיות ההזנה,בארגומנט זה יש לעטוף את כל השאילתא במרכאות כפולות בתחילה ובסוף.
כל השאילתא נכתבת בצורת מחרוזת טקסט,ומחרוזת טקסט(מספרים בלבד בדרך כלל לא.אם כי זה משתנה בין אקסל לשיטס וכן בסוג הפונקציה) בפונקציות האקסל והשיטס הרגילות תמיד עטופה במרכאות.

בסוג השאילתא הנ"ל =select A where B
אנו כותבים פקודה לבחור(select) מתוך עמודה A מתוך ערך.
ואיפה(where) נחפש אותו ? בעמודה b .
ומהו הערך שאנו רוצים לחפש? את זה מזינים לאחר סימון =.
כשמדובר במספר ניתן לכותבו כך ללא שום גרש/גרשיים.(מספר מעצם מהותו נבדל מסתם טקסט של "מילים")
כאשר מדובר בטקסט רגיל כמו משה.יש לעוטפו בגרש בודד.ככה 'משה'
אך כאשר אנו רוצים לכתוב הפנייה לתא ששם כתוב אותו טקסט/מספר .לא מספיק (וזה גם לא יעבוד באופן הזה) לעטוף בגרש בודד.
אם נכתוב ככה 'D10' השיטס "חושב" שמדובר בטקסט שכך קוראים לו.
אנו רוצים לומר בפקודה לשיטס.תסתכל על הטקסט d10 כשם של תא בגיליון.ותסתכל מה כתוב בתוך התא הזה.ולכן יש "להבדילו" בגרשיים כפולים תחילה וסוף.
לסיום מגיע האמפרסנד & שתפקידו כידוע להיות "דבק" בין 2 מחרוזות/טקסטים.במקרה הנ"ל צריך גרשיים כפולים להגדיר שמדובר בהפנייה לטקסט שכתוב בתא.והאמפרסנד מדביק ומשייך את המרכאות לd10
ולכן התוצאה הסופית היא כך

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