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

מאכער · 5/12/23

נכתב ע"י יעקב ויצמן:
פונקציית xlookup או vlookup
הכי נוח "ופשוט"
בהצלחה

אפשר עזרה ע"ג הטבלה?

יעקב ויצמן · 5/12/23

נכתב ע"י מאכער:
אפשר עזרה ע"ג הטבלה?

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

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

אבי2021 · 5/12/23

נכתב ע"י arye izerwerk:
לצורך בניית קטלוג ספרים ברשותי טבלה עם מספר עמודות.
בכל עמודה הכותרת היא מספר המדף והתאים הם הספרים שבתוכה.
אני מעוניין ליצור גליון נוסף מבוסס על הטבלה הנ"ל ובה יופיע כל ספר ובמקביל יופיע מספר המדף.
יש דרך לעשות את זה?
מצרף גליון דוגמא
אני רוצה בעצם גליון חדש שבו יופיע לי כל ספר עם הכותרת של העמודה שלו, לדוגמא: ש"ע או"ח, 1.
הלכות בשר בחלב, 3.
וכן הלאה.
אודה לעזרתכם.

הציע כאן את דרכי לפיתרון הסוגיא.

שלב א
נחבר בתא אחד כל ספר עם מספור המדף, וכ"ז ע"י נוסחת >

קוד:

=A2&" "&A$1

(יש להקפיד לקבוע אך ורק את השורה ולא את הטור כך שנוכל לשרשר בקלות את הנוסחא)

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

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

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

נכתב ע"י אבי2021:
הציע כאן את דרכי לפיתרון הסוגיא.

שלב א
נחבר בתא אחד כל ספר עם מספור המדף, וכ"ז ע"י נוסחת >

קוד:

=A2&" "&A$1

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

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

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

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

אבי2021 · 6/12/23

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

ע"ז נאמר טובים השניים.
להתראות

איש יקר, ומפרגן.

אילנית · 6/12/23

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

יעקב ויצמן · 7/12/23

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

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

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

שאני ראיתי את הקובץ,לאחר הורדה הוספתי לו קצת צבע ועיצוב.(התעריפון כאן נכון לאוקטובר 2023...)
ואז שאלתי את עצמי.רגע רגע...כאן מדובר בקובץ המכיל כ2200 שורות!!!.ואם מישהו יחליט לחפש כאן מידע המלאכה רבה ומייגעת..
אז נכון שתאמרו אבל יש פונקציות לכך כמו filter או xlookup ועוד...ומה הבעיה כאן?
אבל הענין שבדרך כלל שם השירות הרפואי למשל... הוא ידוע למשתמש הגיליון באופן חלקי בלבד.פונקציות החיפוש האקסליסטיות "קפדניות ודייקניות" מאוד.וכאשר לא נמצא ערך התואם במדויק לתא החיפוש תוחזר שגיאת n/a(בדרך כלל) או value(בחלק מהפונקציות)
בסיטואציה כזו משתמש הגיליון שואל ביאוש היכן היא השורה שאני מחפש?היכן היא מסתתרת?..(זוכרים את הטקסט הזה משיר הסביבון בפתיחה....?)
יש למצוא כאן פיתרון לחיפוש בפורמט "מכיל טקסט x".
בעבר כתבתי פיתרון נפלא לבעיה זו עם טריק של הוספת כוכבית לפני ואחרי הערך לחיפוש ראו כאן
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-12111792
אך כעת נלמד שיטה נוספת וגאונית לכך.
בגיליון המצורף ערכתי בגיליון נפרד אפשרות לקבלת תוצאות על פי קוד שירות בעזרת xlookup (כמעט) קלאסית ללא חידוש מיוחד.

כמו כן.בגיליון נוסף ניתן לבצע חיפוש לפי שם השירות (גם בטקסט חלקי) בעזרת הפונקציות
filter.isnumber.search.ומיון אוטומטי בעזרת sort.
וכך זה נראה:

יש כאן מהלך נפלא,להבין זאת -יש צורך כהקדמה ללמוד ביסודיות את הפונקציות הנזכרות.
בקצרה אומר.כי התנאי של filter מתקיים על כל מה שמוחזר true על ידי isnumber.
ופונקציית search תמיד תחזיר מספר (למעשה הכוונה "למיקום" אותו תו/מחרוזת מתחילת הטקסט)גם שמדובר בטקסט חלקי...
זה באמת נפלא וגאוני.
והנה עוד "ממתק של אקסל" טעים ואיכותי לכבוד חג החנוכה...

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

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

יעקב ויצמן · 7/12/23

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

מעשה ב-Sheet פרק ב' - ובו נספר מה עשינו כדי להגן על הנתונים.

הגנת גיליון ב-Google Sheet. איך להגן על הנתונים מדריסה ושינוי לא רצוי.

www.prog.co.il

אילנית · 7/12/23

נכתב ע"י יעקב ויצמן:
מעשה ב-Sheet פרק ב' - ובו נספר מה עשינו כדי להגן על הנתונים.

הגנת גיליון ב-Google Sheet. איך להגן על הנתונים מדריסה ושינוי לא רצוי.

www.prog.co.il

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

יעקב ויצמן · 7/12/23

נכתב ע"י אילנית:
עברתי על השרשור שם ועשיתי הגנת גליון אבל כאשר שלחתי את הקובץ במייל למישהו אחר ההגנה נעלמה.
מה הפתרון?

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

ירחמיאל שני · 9/12/23

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

45242
45242
45242
45244
45244
45244
45246
45246
45250
45250
45250

מה אני עושה ?
אני יכול לשחזר את התאריכים בעריכו קודמות שאני עומד על כל תא
אבל זה המון עבודה

מה אפשר לעשות על מה שקרה
ואיך אפשר למנוע את הבעיה בעתיד

אבי2021 · 9/12/23

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

45242
45242
45242
45244
45244
45244
45246
45246
45250
45250
45250

מה אני עושה ?
אני יכול לשחזר את התאריכים בעריכו קודמות שאני עומד על כל תא
אבל זה המון עבודה

מה אפשר לעשות על מה שקרה
ואיך אפשר למנוע את הבעיה בעתיד

לבחור את כל הטור של התאריך.
ולבחור > עיצוב > מספר > תאריך.
הכל מסתדר.

ירחמיאל שני · 9/12/23

נכתב ע"י אבי2021:
לבחור את כל הטור של התאריך.
ולבחור > עיצוב > מספר > תאריך.
הכל מסתדר.

עשיתי
הסתדר

יעקב ויצמן · 9/12/23

נכתב ע"י ירחמיאל שני:
עשיתי
הסתדר

להסבר על המספרים "המוזרים" שהיו לפניך.
ראה בלינק המצורף.הסברתי בעבר על נושא המספר "הסידורי" של התאריך באקסל ובשיטס
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-12161520

טובה פרנקל · 10/12/23

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

יעקב ויצמן · 10/12/23

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

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

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

שאני ראיתי את הקובץ,לאחר הורדה הוספתי לו קצת צבע ועיצוב.(התעריפון כאן נכון לאוקטובר 2023...)
ואז שאלתי את עצמי.רגע רגע...כאן מדובר בקובץ המכיל כ2200 שורות!!!.ואם מישהו יחליט לחפש כאן מידע המלאכה רבה ומייגעת..
אז נכון שתאמרו אבל יש פונקציות לכך כמו filter או xlookup ועוד...ומה הבעיה כאן?
אבל הענין שבדרך כלל שם השירות הרפואי למשל... הוא ידוע למשתמש הגיליון באופן חלקי בלבד.פונקציות החיפוש האקסליסטיות "קפדניות ודייקניות" מאוד.וכאשר לא נמצא ערך התואם במדויק לתא החיפוש תוחזר שגיאת n/a(בדרך כלל) או value(בחלק מהפונקציות)
בסיטואציה כזו משתמש הגיליון שואל ביאוש היכן היא השורה שאני מחפש?היכן היא מסתתרת?..(זוכרים את הטקסט הזה משיר הסביבון בפתיחה....?)
יש למצוא כאן פיתרון לחיפוש בפורמט "מכיל טקסט x".
בעבר כתבתי פיתרון נפלא לבעיה זו עם טריק של הוספת כוכבית לפני ואחרי הערך לחיפוש ראו כאן
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-12111792
אך כעת נלמד שיטה נוספת וגאונית לכך.
בגיליון המצורף ערכתי בגיליון נפרד אפשרות לקבלת תוצאות על פי קוד שירות בעזרת xlookup (כמעט) קלאסית ללא חידוש מיוחד.
צפה בקובץ המצורף 1518221
כמו כן.בגיליון נוסף ניתן לבצע חיפוש לפי שם השירות (גם בטקסט חלקי) בעזרת הפונקציות
filter.isnumber.search.ומיון אוטומטי בעזרת sort.
וכך זה נראה:
צפה בקובץ המצורף 1518226
יש כאן מהלך נפלא,להבין זאת -יש צורך כהקדמה ללמוד ביסודיות את הפונקציות הנזכרות.
בקצרה אומר.כי התנאי של filter מתקיים על כל מה שמוחזר true על ידי isnumber.
ופונקציית search תמיד תחזיר מספר (למעשה הכוונה "למיקום" אותו תו/מחרוזת מתחילת הטקסט)גם שמדובר בטקסט חלקי...
זה באמת נפלא וגאוני.
והנה עוד "ממתק של אקסל" טעים ואיכותי לכבוד חג החנוכה... תהנו מהקסם!
כמובן,שלאחר הבנת הטכניקה ניתן למנף אותה לכל גיליון ובכל סיטואצייה שתהיה.
וכאמור, מצורף כאן הגיליון לצורך התבוננות במהלך הדברים/לצורך העתק הדבק/לכל שימוש אישי.

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

מורי ורבותי
אֲלֵיכֶם עֵדָה קְדוֹשָׁה אֶשְׁאַל מִכֶּם שְׁאֵלוֹת...
בגיליון המצורף ישנה הפתעה נוספת.
אם מישהו מכם ינסה לבצע ניסוי בגיליון "חיפוש על פי שם השירות"..
הניסוי הוא להשאיר תא ריק בתא החיפוש.
התוצאה שתתקבל היא יבוא כל נתוני גיליון המקור ככתבם וכלשונם.
ככה:

השאלה היא מה קורה כאן?
אם ביקשנו כתנאי לפונקציית filter שתחפש ערך שכתוב בתא A2.והתא הזה הוא ריק היה לפונקצייה להחזיר שגיאת n/a או value
ולמה כל התוצאות של גיליון המקור מוחזרות?
(למי שתוהה אין כאן שייכות לרווחים "מקובלים" או "מיותרים" כך שפונקציית trim לא תשנה מאומה מהתוצאה..נסו ותראו)
מי שינסה לבצע עבודת מחקר מקיפה יגלה בסוף (ואני כבר יגלה לכם בסוד את המסקנות...) שמי שאחראית כאן על "המחדל" הזה היא פונקציית search.
אם נעשה רגע "זום in" לפונקציית search נגלה שכאשר תא החיפוש ריק .הפונקצייה מחזירה את המספר 1.
(וזו הסיבה שפונקציית filter מתזירה תוצאות..כי התנאי הוא שתוצאת search תהיה מספר.כדי שפונקציית isnumber תחזיר true .והנה התקיים כאן התנאי...)
סיכמתי זאת בגיליון המצורף ובצילום מסך הבא

ניתן לראות בשטח הירוק שכאשר נכתב בתא חיפוש ערך שלא קיים במחרוזת מוחזרת שגיאה של value.
אבל בשטח שצבוע בתכלת בתא c18 מוחזרת הספרה 1.למרות שתא A18 ריק לחלוטין
השאלה הגדולה כאן היא מדוע? הרי התו הראשון בתא b18 הוא "א" (של אברהם) .למה הפונקצייה מתייחסת לתו הריק כראשון?
מה ההיגיון כאן???

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