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

[יעקב ויצמן]

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

שאלתך היא מעניינת ומאתגרת בו זמנית!
מה שניתן לביצוע הוא- להגדיר בפונקציית countif את הטווח כהפנייה לטקסט בתא אחר.
המניפולצייה הזו ניתנת בעזרת פונקציית indirect הגאונית והמופלאה.
כן,זו אותה פונקצייה שנראית במבט ראשוני- כל כך סתמית ואף מיותרת לחלוטין.
אז נכון,היא לא מוגדרת באופן רשמי בין הפונקציות "המתקדמות" כמו xlookup וכדומה..אבל שמעמיקים בלוגיקה שלה מבינים כמה שהיא סופר גאונית וסופר שימושית!
בעבר כתבתי על כך בפורום כמה פעמים ,הנה דוגמא אחת לכך:
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-11879444

ובחזרה לפיתרון והפרקטיקה,אז מצורף כאן גיליון עם הפיתרון:

יצרתי כאן בתאים e2 וe4 אימות נתונים למניעת שגיאות כתיב וכדומה.(זה מאוד חשוב כאן ואני ממליץ על כך)
האימות מתוך טווח בעמודה A.(שם הרצתי מספור מ1 עד 1000 לבנתיים...) את עמודה a ניתן להסתיר.
בכל פעם שתרצה לשנות את הטווח בעמודה c יש לבחור את מספר השורה בתא התחלה ובתא הסיום.וזהו!
@עושה גדולות תודה רבה לך על האתגר.ואני שמח שזו היתה הזדמנות חוזרת לכולנו להתעמק ,להתעסק ולהחכים עם פונקציית indirect.
וכאמור לעיל.זו עוד הוכחה נפלאה לכך שהפונקצייה פועלת על ידי חזרה לתא המקור והשורש (תא "הקצה")
"תן לחכם ויחכם עוד..."

בהצלחה רבה!

 

[עושה גדולות]

שאלתך היא מעניינת ומאתגרת בו זמנית!
מה שניתן לביצוע הוא- להגדיר בפונקציית countif את הטווח כהפנייה לטקסט בתא אחר.
המניפולצייה הזו ניתנת בעזרת פונקציית indirect הגאונית והמופלאה.
כן,זו אותה פונקצייה שנראית במבט ראשוני- כל כך סתמית ואף מיותרת לחלוטין.
אז נכון,היא לא מוגדרת באופן רשמי בין הפונקציות "המתקדמות" כמו xlookup וכדומה..אבל שמעמיקים בלוגיקה שלה מבינים כמה שהיא סופר גאונית וסופר שימושית!
בעבר כתבתי על כך בפורום כמה פעמים ,הנה דוגמא אחת לכך:
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-11879444

ובחזרה לפיתרון והפרקטיקה,אז מצורף כאן גיליון עם הפיתרון:צפה בקובץ המצורף 1510701יצרתי כאן בתאים e2 וe4 אימות נתונים למניעת שגיאות כתיב וכדומה.(זה מאוד חשוב כאן ואני ממליץ על כך)
האימות מתוך טווח בעמודה A.(שם הרצתי מספור מ1 עד 1000 לבנתיים...) את עמודה a ניתן להסתיר.
בכל פעם שתרצה לשנות את הטווח בעמודה c יש לבחור את מספר השורה בתא התחלה ובתא הסיום.וזהו!
@עושה גדולות תודה רבה לך על האתגר.ואני שמח שזו היתה הזדמנות חוזרת לכולנו להתעמק ,להתעסק ולהחכים עם פונקציית indirect.
וכאמור לעיל.זו עוד הוכחה נפלאה לכך שהפונקצייה פועלת על ידי חזרה לתא המקור והשורש (תא "הקצה")
"תן לחכם ויחכם עוד..."

בהצלחה רבה!

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

 

[יעקב ויצמן]

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

מצוין וכל הכבוד!
יגעת ומצאת תאמין!

תודה רבה רבה!

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

 

[arye izerwerk]

יש דרך לעשות את נוסחת UNIQUE על כמה טווחים מקבילים.
לצורך העניין, עמודה A,C,H שהנוסחה תביא את הערכים הייחודיים למערך אחד מכל הטווחים במקביל

 

[arye izerwerk]

יש דרך לעשות את נוסחת UNIQUE על כמה טווחים מקבילים.
לצורך העניין, עמודה A,C,H שהנוסחה תביא את הערכים הייחודיים למערך אחד מכל הטווחים במקביל

וכמו"כ, האם יש דרך להיפטר מתאים ריקים במערך שהפונקציה מציגה?

 

[יעקב ויצמן]

יש דרך לעשות את נוסחת UNIQUE על כמה טווחים מקבילים.
לצורך העניין, עמודה A,C,H שהנוסחה תביא את הערכים הייחודיים למערך אחד מכל הטווחים במקביל

אם זו טבלת המקור.ואנו רוצים למזג(הסרת כפילויות) את עמודת a וc בלבד תוך כדי "דילוג" על עמודה b

האם כוונתך לתוצאה כזו

או כזו?

 

[arye izerwerk]

האם כוונתך לתוצאה כזו

כוונתי לזו

 

[יעקב ויצמן]

כוונתי לזו

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

בצד ימין של הגיליון ישנה טבלה ללא תאים ריקים
ישנן 2 פתרונות.
פיתרון א -כולל את הפונקצייה vstack
ובאגב.בדיוק עם פונקצייה זו ביצעתי שימוש בגיליון הנ"ל ששלחת לגבי החזר משכנתא.
פיתרון ב-כולל את הפונקצייה המופלאה flatten (לא הזכרתי אותה כאן בעבר.אבל מדובר בהחלט בפונקצייה נהדרת עם ביצועיים יחודיים.וכדאי ללמוד עליה בהזדמנות זו)

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

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

בהצלחה רבה!

 

[יעקב ויצמן]

וכמו"כ, האם יש דרך להיפטר מתאים ריקים במערך שהפונקציה מציגה?

התייחסתי לשאלה זו בפיתרון לעיל ששלחתי

 

[arye izerwerk]

פיתרון ב-כולל את הפונקצייה המופלאה flatten (לא הזכרתי אותה כאן בעבר.אבל מדובר בהחלט בפונקצייה נהדרת עם ביצועיים יחודיים.וכדאי ללמוד עליה בהזדמנות זו)

ראשית תודה על העזרה! לא מובן מאליו בכלל.
דומני ששתי הפונקציות הנ"ל מצויות רק ב-365, ועל כן נבצר ממני להנות מנפלאות הפתרון הנ"ל.

 

[יעקב ויצמן]

ראשית תודה על העזרה! לא מובן מאליו בכלל.

בשמחה רבה.

דומני ששתי הפונקציות הנ"ל מצויות רק ב-365, ועל כן נבצר ממני להנות מנפלאות הפתרון הנ"ל.

פונקציית flatten קיימת בשיטס בלבד.שכחתי לציין זאת.סורי..
ואכן פונקציית vstack קיימת באופיס 365 בלבד או בשיטס.
אם גירסת האקסל שלך אינה של אופיס 365.אני באמת מציע לך לעבוד על השיטס,
השיטס חינמי ומעודכן ברשת.
וברוב המוחץ של המקרים זה יספיק לך דיי והותר...האמן לי מניסיון..!
(לשם הגילוי נאות אומר ,כי יש לי אקסל 365 וכן שיטס הן המחשב הביתי והן בנייד.ובכל אופן אני מתנהל בדרך כלל וב99% מהמקרים בשיטס בלבד,ולרוב דרך הנייד בלבד)
זה באמת חבל שלא תוכל להנות מכל הטוב הזה של פונקציות האקסל/השיטס החדשות.
כאמור.זו המלצתי האישית בלבד!
אם בכל אופן תרצה לבצע זאת באקסל(בגירסאות ישנות יותר) בלבד.זה עדיין ניתן בעזרת כלי הpowet query בדרך יותר מפותלת שדורשת יותר לימוד ומיומנות.

 

[arye izerwerk]

וברוב המוחץ של המקרים זה יספיק לך דיי והותר...האמן לי מניסיון..!

אכן, יש לי נסיון בינוני עם שיטס, הבעיה שאני עובד הרבה על לא מקוון

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

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

 

[יעקב ויצמן]

אכן, יש לי נסיון בינוני עם שיטס, הבעיה שאני עובד הרבה על לא מקוון

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

 

[arye izerwerk]

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

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

 

[יעקב ויצמן]

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

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

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

 

[רוגעל'ה חם]

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

 

[arye izerwerk]

איך אפשר לעשות עיצוב מותנה שיהיה צבע אחר למספרים הזוגיים/האי זוגי?

תכניס את הנוסחה ISEVEN

 

[רוגעל'ה חם]

תכניס את הנוסחה ISEVEN

איפה להכניס?
בעיצוב מותנה?

 

[arye izerwerk]

בעיצוב מותנה?

כן

 

[יעקב ויצמן]

תכניס את הנוסחה ISEVEN

ישר כח
ברשותך,אוסיף כאן נקודה מעניינת.
הפונקצייה iseven מחזירה אחד מ2 ערכים true או false דהיינו אמת/נכון או שקר/לא נכון.
כאשר נוסיף את הפונקצייה במסגרת "נוסחא מותאמת אישית" בעיצוב מותנה, מקובל יותר להשתמש בנוסחא הקלאסית ולהוסיף סימן = true/false עבור 2 כללים של עיצוב מותנה.
יש לזכור כאן פרט מעניין.כי ניתן להמיר את ערך הבדיקה למספר.
הערכים true /false נחשבים "ערכים בוליאנים".
מבלי להיכנס לעומק וללוגיקה שבטכניקה/ות דלהלן.אומר כי ניתן לבצע המרה של הערכים למספר 0 (השווה לtrue) או למספר 1 (השווה לfalse)
על ידי הוספת שני סימוני חיסור -- בין סימון השווה לתחילת הטקסט של הפונקצייה.
טכניקה הזו יותר מוכרת במקרה כמו הנ"ל ובעוד הרבה וריאציות של פונקציות עם תנאי.

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

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

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

בהצלחה רבה!