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

מרובע · 19/6/23

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

מרובע · 19/6/23

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

אם אף אחד לא עונה לי, אני אענה לעצמי:

How to Extract the First Two Words - ExcelNotes

To extract the first two words in the text string, you need to find the relative location of the second space, then use Left Function.

excelnotes.com

יעקב ויצמן · 19/6/23

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

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

אפתח בשאלה הראשונה-שנשאלה כאן ושאני נשאל בה לעיתים...
מדוע בפונקציות (או בנוסחאות פשוטות יותר כמו a4=)של יבוא נתונים לא נמשך גם עיצוב התא (הכולל פרטים רבים כמו אימות נתונים/עיצוב מותנה/גודל הכתב/צבע מילוי ועוד ועוד)?
האם אותם "חכמים ונבונים" מבית היוצר מייקרוסופט/גוגל לא יכלו להואיל בטובם לקודד אלגוריתם מותאם באותן פונקציות לטובת משתמש/ת האקסל/שיטס שחפצים בכך?האם מדובר בטרחה יתירה עבורם או שישנה (אולי...חלילה...)יד מכוונת לריבוי טרחה יתירה לאותו משתמש הגיליון שיחפוץ בעיצוב זהה למקור?
לפני שאגש להסבר אקדים שאלה נוספת...
מי שיתבונן בעומקם של דברים יראה שהשאלה נסובה בעצם על (כמעט) כל פונקצייה..ומה לנו כי נלין על פונקציות כמו importrange וכדומה...
ואסביר זאת בדוגמא הכי פשוטה עם הפונקצייה הכי פשוטה ("הגאונית בפשטותה" ליתר דיוק),פונקציית sum

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

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

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

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

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

עד כאן לפינת "הלומדע'ס" על קצה המזלג.

וזו עוד דוגמא "קטנה" לחכמה הנפלאה והלוגיקה המסתתרת באקסל/שיטס.
והמסקנות:-"תלמדו" את האקסל!
"תחקרו את האקסל"!

זה מחכים,זה מהנה,וזה בהחלט שווה...

בהצלחה

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

יעקב ויצמן · 19/6/23

נכתב ע"י מרובע:
אם אף אחד לא עונה לי, אני אענה לעצמי:

How to Extract the First Two Words - ExcelNotes

To extract the first two words in the text string, you need to find the relative location of the second space, then use Left Function.

excelnotes.com

"הוא מותיב והוא מפרק לה"

אז קודם כל -כל הכבוד ושאפו.

נקודה קטנה שמעניינת פה.שהבסיס פה היא פונקציית left .אם אנו מחפשים טקסט מצד שמאל של התא .זו בדיוק הפונקצייה המתאימה.
זה בדיוק מה שקורה בשורה 6 שהכיתוב הוא באנגלית.
אז למה בשורה 3 הפונקצייה לא מחזירה את "יצחק יעקב" שהטקסט הוא בעברית?
התשובה כי אלגוריתם הפונקציות של left ו-right בנוי לשפת האנגלית.
בטקסט בעברית יש לקחת בחשבון תמיד שהיפוכם הוא האמת.
לחילוץ מימין יש להשתמש בleft
ולחילוץ משמאל יש להשתמש בright
ולכן בשורה 3 מוחזרות 2 התיבות הימניות אע"פ שמדובר בחילוץ על ידי left.
על כל פנים.באתי רק לעורר את תשומת הלב על הנקודה החשובה הזו לכל שימוש עתידתי בשתי פונקציות אלו.

הצ'ייסר · 19/6/23

נכתב ע"י מרובע:
אם אף אחד לא עונה לי, אני אענה לעצמי:

How to Extract the First Two Words - ExcelNotes

To extract the first two words in the text string, you need to find the relative location of the second space, then use Left Function.

excelnotes.com

נכתב ע"י יעקב ויצמן:
"הוא מותיב והוא מפרק לה"
אז קודם כל -כל הכבוד ושאפו.
צפה בקובץ המצורף 1402145
נקודה קטנה שמעניינת פה.שהבסיס פה היא פונקציית left .אם אנו מחפשים טקסט מצד שמאל של התא .זו בדיוק הפונקצייה המתאימה.
זה בדיוק מה שקורה בשורה 6 שהכיתוב הוא באנגלית.
אז למה בשורה 3 הפונקצייה לא מחזירה את "יצחק יעקב" שהטקסט הוא בעברית?
התשובה כי אלגוריתם הפונקציות של left ו-right בנוי לשפת האנגלית.
בטקסט בעברית יש לקחת בחשבון תמיד שהיפוכם הוא האמת.
לחילוץ מימין יש להשתמש בleft
ולחילוץ משמאל יש להשתמש בright
ולכן בשורה 3 מוחזרות 2 התיבות הימניות אע"פ שמדובר בחילוץ על ידי left.
על כל פנים.באתי רק לעורר את תשומת הלב על הנקודה החשובה הזו לכל שימוש עתידתי בשתי פונקציות אלו.

IF כבר ב'איהו מותיב לה ואיהו מפרק לה' עסקינן, בסרטון דלהלן מבואר באופן נפלא, דבר דבור על אופנו כל הסוגיא הנ"ל. שפתיים ישק (עי' גיטין ט. וברש"י ותוס' שם).

לימוד אקסל, שיעור מספר 31 - LEFT + RIGHT + MID + LEN שילוב נוסחאות

בשיעור נלמד כיצד ניתן לשלוף חלק מטקסט מתוך טקסט משתנה. בשיעור יש שימוש בנוסחאות LEFT, RIGHT, MID, LEN.

www.excelist.co.il

(נכתב בהשראת ההודעה האחרונה של @יעקב ויצמן האלוף!)

יעקב ויצמן · 20/6/23

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

אני שמח לראות שהנך שואל ומשיב באשכול זה.המשך כך..

ושיהיה לך ולכל עמך בית ישראל חודש טוב ומבורך!

יעקב ויצמן · 20/6/23

נכתב ע"י Shlush's:
מחפש קובץ אקסל לחישוב כספי פיצויים, שהמעסיק מפריש 6 אחוז, ובזמן הפיטורין משלים עוד 2.33 אחוז

@Shlush's
האם ראית מה שהשבתי?

הילוך גבוה · 20/6/23

@יעקב ויצמן
יש דרך לעשות XLOOKUP עם תנאי
יש לי קובץ שיטס עם רשימת נרשמים + מספרי כרטיס חבר, יש קובץ נוסף שם כתוב על כל כרטיס האם הוא הופעל או לא
אני רוצה עם פונקציית IMPORTRANGE לשלוף מהקובץ של הפעלת הכרטיס את הנתון אם הוא הופעל או לא
אני לא רוצה XLOOKUP רגיל כי אז הוא יכתוב האם הוא הופעל או לא, אני רוצה שהוא יביא נתון רק אם הכרטיס הופעל, אם לא אז שלא יכתוב כלום
איך אפשר לשלב בין XLOOKUP לתנאי?

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

יעקב ויצמן · 20/6/23

נכתב ע"י הילוך גבוה:
@יעקב ויצמן
יש דרך לעשות XLOOKUP עם תנאי
יש לי קובץ שיטס עם רשימת נרשמים + מספרי כרטיס חבר, יש קובץ נוסף שם כתוב על כל כרטיס האם הוא הופעל או לא
אני רוצה עם פונקציית IMPORTRANGE לשלוף מהקובץ של הפעלת הכרטיס את הנתון אם הוא הופעל או לא
אני לא רוצה XLOOKUP רגיל כי אז הוא יכתוב האם הוא הופעל או לא, אני רוצה שהוא יביא נתון רק אם הכרטיס הופעל, אם לא אז שלא יכתוב כלום
איך אפשר לשלב בין XLOOKUP לתנאי?

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

נכתב ע"י הילוך גבוה:
ועוד משהו..
ראיתי את ה'חבורה' שהעלית כאן, מקווה שאתפנה בקרוב לעבור על זה בתשומת לב

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

יעקב ויצמן · 20/6/23

@עתיד
הסתדרת עם ההיסטוריות עריכות והגנה על טווח ?

הילוך גבוה · 20/6/23

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

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

לא הצלחתי לעשות את זה, האמת לא הבנתי מה ההיגיון שעומד מאחורי הקומבינציה הזאת
נסיתי לחכות כמו תוכי, והתוצאות בהתאם
אולי נעזוב לרגע את IMPORTRANGE?
בוא ננסה להבין האם יש אפשרות לעשות XLOOKUP בשילוב IF ואח"כ אשבור את הראש איך לשלב את זה עם ה IMPORTRANGE

יעקב ויצמן · 20/6/23

נכתב ע"י הילוך גבוה:
לא הצלחתי לעשות את זה, האמת לא הבנתי מה ההיגיון שעומד מאחורי הקומבינציה הזאת
נסיתי לחכות כמו תוכי, והתוצאות בהתאם
אולי נעזוב לרגע את IMPORTRANGE?
בוא ננסה להבין האם יש אפשרות לעשות XLOOKUP בשילוב IF ואח"כ אשבור את הראש איך לשלב את זה עם ה IMPORTRANGE

תראה כאן בשטח התכלת

בעמודה f .הif בוחן אם כתוב כן בעמודה d אז הוא "מפעיל" את xlookup "שיכנס לפעולה"
הxlookup עושה את עבודתו נאמנה .ומסתכל על תא a12
ולפי זה מחזיר את התוצאה בעמודה a דהיינו את שם העובד הרלוונטי
ואם כתוב לא .הxlookup ולא נכתב כלום בתא
זו דוגמא יבשה בלבד
מצרף לך את הגיליון

הילוך גבוה · 20/6/23

נכתב ע"י יעקב ויצמן:
תראה כאן בשטח התכלת
צפה בקובץ המצורף 1402770
בעמודה f .הif בוחן אם כתוב כן בעמודה d אז הוא "מפעיל" את xlookup "שיכנס לפעולה"
הxlookup עושה את עבודתו נאמנה .ומסתכל על תא a12
ולפי זה מחזיר את התוצאה בעמודה a דהיינו את שם העובד הרלוונטי
ואם כתוב לא .הxlookup ולא נכתב כלום בתא
זו דוגמא יבשה בלבד
מצרף לך את הגיליון

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

יעקב ויצמן · 20/6/23

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

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

קול תורה · 21/6/23

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

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

בתודה מראש לכל העונים והמסייעים!

יעקב ויצמן · 21/6/23

נכתב ע"י קול תורה:
בלת"ק
יש לי טבלה עם שמות משפחה ושמות פרטיים ועוד נתונים השמות הפרטיים והמשפחות הם כל אחד בעמודה נפרדת, אני רוצה לעשות חיפוש (vlookup) אבל יש לי הרבה שמות משפה זהים שרק השמות הפרטיים שונים איך אני עושה את זה

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

בתודה מראש לכל העונים והמסייעים!

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

קול תורה · 21/6/23

נכתב ע"י קול תורה:
בלת"ק
יש לי טבלה עם שמות משפחה ושמות פרטיים ועוד נתונים השמות הפרטיים והמשפחות הם כל אחד בעמודה נפרדת, אני רוצה לעשות חיפוש (vlookup) אבל יש לי הרבה שמות משפה זהים שרק השמות הפרטיים שונים איך אני עושה את זה

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

בתודה מראש לכל העונים והמסייעים!

נכתב ע"י יעקב ויצמן:
נא לצרף גיליון דוגמא עם השאלה בגוף הגיליון
מספיק 5-10 שורות.אין צורך ביותר
הפרטים יכולים להיות בדויים.
תודה רבה

אבי2021 · 21/6/23

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

מרובע · 21/6/23

הטבלה שנמצאת כאן https://www.nadlan.gov.il/?search=קרית משה ירושלים
כשעושים לה העתק הדבק, מקבלים רשימה ולא טבלה.
האם יש דרך כלשהי להדביק אותה כטבלה?
כך יוצאת ההדבקה:
24.05.2023
קרית משה 8
30157-41-2
דירה בבית קומות
2
קרקע
46
2,100,000
39% ב 3.4 שנים

24.05.2023
הברון הירש 12
30153-66-9
דירה בבית קומות
4
רביעית
115
3,880,000

21.05.2023
המאירי 14
30157-160-12
דירה בבית קומות
3.5
שלישית
62
2,800,000
133% ב 10.7 שנ

יעקב ויצמן · 21/6/23

נכתב ע"י אבי2021:
מצורף צילום מסך.
נעשה שימוש בפונקציית filter.
עובד בשיטס, ובאקסל החל מגירסא 365.
בהצלחה.צפה בקובץ המצורף 1403522

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