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

יעקב ויצמן

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

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

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

  • טבלה לבדיקה מתוקן.xlsx
    KB 51.1 · צפיות: 25

אבי2021

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

שלב א
נחבר בתא אחד כל ספר עם מספור המדף, וכ"ז ע"י נוסחת >
קוד:
=A2&" "&A$1
(יש להקפיד לקבוע אך ורק את השורה ולא את הטור כך שנוכל לשרשר בקלות את הנוסחא)
שלב א.png

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

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

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

  • מדפים.xlsx
    KB 12 · צפיות: 21

יעקב ויצמן

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

שלב א
נחבר בתא אחד כל ספר עם מספור המדף, וכ"ז ע"י נוסחת >
קוד:
=A2&" "&A$1
(יש להקפיד לקבוע אך ורק את השורה ולא את הטור כך שנוכל לשרשר בקלות את הנוסחא)
צפה בקובץ המצורף 1517128
שלב ב.
נעתיק ונדביק בצורה ידני את כל הערכים לטור אחד גדול (נקפיד להדביק בשיטת ערכים).
צפה בקובץ המצורף 1517129
שלב ג.
ע"י אשף מילוי מהיר נפריד בשני תאים את שם הספר ואת המספור.
ידידי היקר
ראשית שאפו גדול.נהנתי מאוד מהמהלך המבריק שעשית פה.
כהארה אומר לך."הבעיה" היחידה פה זו "הדינמיות" בשלב ב מדובר בהדבקה ידנית.וכן בשלב ג יש לבצע גרירה עם טכניקת מילוי מהיר.
אם התווסף ספר בטבלת המקור הוא לא יוצג באופן אוטומטי בשלב- ב ו-ג.
ולכן ברשותך אציע כאן.להשתמש בשלב ב עם שילוב הפונקציות שהצעתי לעיל.
Screenshot_20231205_090916_Sheets.jpg

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

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

אבי2021

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

אילנית

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

יעקב ויצמן

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

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

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

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

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

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

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

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

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

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

  • קוד שירות ותעריפון-משה-ב 10.23.xlsx
    KB 170.1 · צפיות: 40

יעקב ויצמן

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

אילנית

משתמש מקצוען
צילום מקצועי

יעקב ויצמן

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

ירחמיאל שני

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

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

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

אבי2021

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

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

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

יעקב ויצמן

מנהל פורום מחשבים
מנהל
מנוי פרימיום

טובה פרנקל

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

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

  • ‏‏לכידה.JPG
    ‏‏לכידה.JPG
    KB 19.6 · צפיות: 25

יעקב ויצמן

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

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

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

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

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

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

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

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

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

  • Search function in Excel.xlsx
    KB 50.4 · צפיות: 28
נערך לאחרונה ב:

יעקב ויצמן

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

טובה פרנקל

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

יעקב ויצמן

מנהל פורום מחשבים
מנהל
מנוי פרימיום

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

לוח לימודים

מסלולי לימוד שאפשר לההצטרף
אליהם ממש עכשיו:

הפרק היומי

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


תהילים פרק קכג

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

אתגר AI

השתקפות מרהיבה • אתגר 137

לוח מודעות

למעלה