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

[natand]

הסתדרתי תודה!

 

[יהההונתן]

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

 

[יעקב ויצמן]

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

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

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

3.לבצע זאת בפקודת מאקרו vba ראה כאן

VBA Express : Excel - "Autocomplete" using cells from different column

"Autocomplete" using cells from different column Simulates the AutoComplete feature, but allows use of data from a different column or worksheet as the source.

www.vbaexpress.com

בהצלחה!

 

[יחיאל מ.]

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

 

[arye izerwerk]

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

נסה בקובץ>מידע>נהל את חוברת העבודה.

 

[arye izerwerk]

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

 

[יעקב ויצמן]

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

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

ועכשיו קצת הסבר על כל הטוב שמתרחש כאן:
אז בעמודות a.b.c מוצגת טבלת המקור כידוע.
כל ההמשך מכאן ואילך יכול להיות בגיליון נפרד כפי ששאלת אם רק תעשה זאת בזהירות רבה ובהקפדה על כללי הפניית ערכים בין גיליונות וכן שמירת הקיבועים במקומם הרצוי ובאופן מדויק!
ובכן בעמודה e כהכנה לפיתרון כל טבלת המקור ממוינת (בעזרת sort),מקובצת בעמודה אחת (בעזרת vstack) תוך כדי הסרת הרווחים הריקים בטבלת המקור(בעזרת filter)
למעשה,טכניקה זו כבר הוזכרה לעיל על ידי בלינק כאן
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-12984676
וכמעשהו התם כך מעשהו הכא...

בעמודות f.g.h מתבצע חיפוש והחזרת מספר המדף של כל ספר.כל עמודה מתייחסת לטווח עמודה שונה בטבלת המקור.
בעזרת פונקציות MATCH .IFERROR.IF
אני ממליץ ללמוד על פונקציית MATCH המופלאה להבנת כל החשבון כאן.

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

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

מצורף כאן גיליון לביצוע העתק הדבק וללימוד הפונקציות "מבפנים".

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

בהצלחה רבה לך!

 

[יעקב ויצמן]

באגב.אזכיר בהזדמנות זו את שתי הפונקציות row ואחותה column

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

 

[מאכער]

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

 

[יעקב ויצמן]

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

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

 

[מאכער]

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

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

 

[יעקב ויצמן]

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

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

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

 

[אבי2021]

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

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

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

=A2&" "&A$1

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

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

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

 

[יעקב ויצמן]

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

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

=A2&" "&A$1

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

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

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

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

 

[אבי2021]

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

ע"ז נאמר טובים השניים.
להתראות איש יקר, ומפרגן.

 

[אילנית]

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

 

[יעקב ויצמן]

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

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

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

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

 

[יעקב ויצמן]

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

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

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

www.prog.co.il

 

[אילנית]

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

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

www.prog.co.il

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

 

[יעקב ויצמן]

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

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