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

יעקב ויצמן

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

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

(לולא פתרון הולם זה ממש כך.נסו ותראו)

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

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

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

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


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

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

קבצים מצורפים

  • Consolidate data from multiple worksheets in a single worksheet in excel.xlsx
    KB 38.4 · צפיות: 19
נערך לאחרונה ב:

איזהו

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

- YH -

משתמש פעיל
מנוי פרימיום
בוגר/תלמיד פרוג
עיצוב גרפי

יעקב ויצמן

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

יעקב ויצמן

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

איזהו

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

יעקב ויצמן

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

הפונקצייה היא 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 שורות בלבד.אין צורך ביותר מכך.
תצבע בגיליון את האיזור שתרצה לבצע בו בדיקה.ותכתוב ידנית מהן התוצאות הרצויות.
שאבין את השאלה באופן מדויק.והמענה יהיה גם כן מדויק
ושוב ושוב תודה רבה
בטור א' ישם קוד ושם, בטור ב' יש רק שם, איך אוכל לדעת את השם בטור ב' ע"פ טור א'
 

קבצים מצורפים

  • דוגמה לשאלה.xlsx
    KB 9 · צפיות: 21

יעקב ויצמן

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


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

קבצים מצורפים

  • דוגמה לשאלה מתוקן (1).xlsx
    KB 7.9 · צפיות: 21
נערך לאחרונה ב:

יעקב ויצמן

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

בהצלחה לך
 

קבצים מצורפים

  • דוגמה לשאלה מתוקן-4 פתרונות.xlsx
    KB 50.6 · צפיות: 18
נערך לאחרונה ב:

ישראלי1

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



1704107837864.png
 

קבצים מצורפים

  • שאלה בquery.xlsx
    KB 5.2 · צפיות: 20

יעקב ויצמן

מנהל פורום מחשבים
מנהל
מנוי פרימיום
על פניו נראה שצריך להוציא את D13 מחוץ למרכאות ולשרשר אותו עם &
עריכה: נ.ב. לא נכנסתי לקובץ ממבט ראשוני כך נראה הגיוני.
האם התכוונת לכך?
כי כפי המתואר (בפיתרון שערכתי) בצילום מסך.זה אכן עובד בשיטס
Screenshot_20240101_215955_Sheets.jpg
 
נערך לאחרונה ב:

יעקב ויצמן

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

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
ולכן התוצאה הסופית היא כך
Screenshot_20240101_223846_Sheets.jpg

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

אולי מעניין אותך גם...

הפרק היומי

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


תהילים פרק קיט ע'

קכא עָשִׂיתִי מִשְׁפָּט וָצֶדֶק בַּל תַּנִּיחֵנִי לְעֹשְׁקָי:קכב עֲרֹב עַבְדְּךָ לְטוֹב אַל יַעַשְׁקֻנִי זֵדִים:קכג עֵינַי כָּלוּ לִישׁוּעָתֶךָ וּלְאִמְרַת צִדְקֶךָ:קכד עֲשֵׂה עִם עַבְדְּךָ כְחַסְדֶּךָ וְחֻקֶּיךָ לַמְּדֵנִי:קכה עַבְדְּךָ אָנִי הֲבִינֵנִי וְאֵדְעָה עֵדֹתֶיךָ:קכו עֵת לַעֲשׂוֹת לַיי הֵפֵרוּ תּוֹרָתֶךָ:קכז עַל כֵּן אָהַבְתִּי מִצְוֹתֶיךָ מִזָּהָב וּמִפָּז:קכח עַל כֵּן כָּל פִּקּוּדֵי כֹל יִשָּׁרְתִּי כָּל אֹרַח שֶׁקֶר שָׂנֵאתִי:
נקרא  6  פעמים

אתגר AI

חשיפה כפולה • אתגר 130

לוח מודעות

למעלה