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

יעקב ויצמן

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

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

בהצלחה רבה!
 

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

  • countif and indirect function in Excel.xlsx
    KB 52.9 · צפיות: 24
נערך לאחרונה ב:

עושה גדולות

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

יעקב ויצמן

מנהל פורום מחשבים
מנהל
מנוי פרימיום
יש דרך לעשות את נוסחת UNIQUE על כמה טווחים מקבילים.
לצורך העניין, עמודה A,C,H שהנוסחה תביא את הערכים הייחודיים למערך אחד מכל הטווחים במקביל
אם זו טבלת המקור.ואנו רוצים למזג(הסרת כפילויות) את עמודת a וc בלבד תוך כדי "דילוג" על עמודה b
Screenshot_20231128_235719_Sheets.jpg

האם כוונתך לתוצאה כזו
Screenshot_20231129_003851_Sheets.jpg

או כזו?
Screenshot_20231129_003855_Sheets.jpg
 

יעקב ויצמן

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

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

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

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

בהצלחה רבה!
 

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

  • Unique values from multiple ranges in excel.xlsx
    KB 49.6 · צפיות: 27
נערך לאחרונה ב:

arye izerwerk

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

יעקב ויצמן

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

arye izerwerk

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

יעקב ויצמן

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

arye izerwerk

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

יעקב ויצמן

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

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

רוגעל'ה חם

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

יעקב ויצמן

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

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


Screenshot_20231130_164226_Sheets.jpg


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

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

בהצלחה רבה!
 

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

  • How to highlight odd or even numbers in Excel.xlsx
    KB 47.4 · צפיות: 26
נערך לאחרונה ב:

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

לוח לימודים

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

הפרק היומי

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


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

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

אתגר AI

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

לוח מודעות

למעלה