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

[הילוך גבוה]

=COUNTIF(C2:C48,">0")

הטווח הוא אקראי הכוונה לטוח בו כתובות המלגות של החודש הרלוונטי

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

 

[אפר]

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

זה כמות המלגות
כדי לספור את כמות האברכים תשתמש ב COUNT על אותו הטווח

(הכל כתוב כבר בהודעה הראשונה)

 

[שימי .ב]

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

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

 

[יעקב ויצמן]

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

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

 

[יעקב ויצמן]

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

 

[שימי .ב]

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

ראשית תודה על העזרה
אני מכיר אופציה דומה אך לא לזה כוונתי
לפי האמור זה יהיה מקובע לפי הטווח שאני מגדיר מראש
אני צ' שכל פעם הוא ישאב נתונים משורה אחרת מאחר ומתווסף שורות במסד הנתונים
אני יסביר את עצמי
ולכן אם יש אפשרות ע"י פונקציה או INDIRECT כמו שמשתלב באקסל באימות נתונים כמו שמופיע כאן https://anyfit.biz/he/trainings/35-excel-data-validation
שבמיקום של הקריטריון יופיע את מה שמופיע ברשימה נפתחת
אולי לא מוסבר טוב
אשמח לשתף בפרטי

 

[יעקב ויצמן]

ראשית תודה על העזרה
אני מכיר אופציה דומה אך לא לזה כוונתי
לפי האמור זה יהיה מקובע לפי הטווח שאני מגדיר מראש
אני צ' שכל פעם הוא ישאב נתונים משורה אחרת מאחר ומתווסף שורות במסד הנתונים
אני יסביר את עצמי
ולכן אם יש אפשרות ע"י פונקציה או INDIRECT כמו שמשתלב באקסל באימות נתונים כמו שמופיע כאן https://anyfit.biz/he/trainings/35-excel-data-validation
שבמיקום של הקריטריון יופיע את מה שמופיע ברשימה נפתחת
אולי לא מוסבר טוב
אשמח לשתף בפרטי
צפה בקובץ המצורף 1331963

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

 

[שימי .ב]

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

שולח לך קישור https://docs.google.com/spreadsheets/d/1i3w7c0xHZ6lqLSQ3wZ4pZl4liwIMIrbSjqq8BnrmmB0/edit?usp=sharing
סימנתי לך את האימות נתונים שאני מדבר עליו זה צבוע בירוק שורה 20 רשמתי בהערות של שלב 1 שורה אחר שורה את הקושי
תודה רבה

 

[שלוות נפש]

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

 

[יעקב ויצמן]

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

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

 

[שלוות נפש]

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

אין שם עיצוב מותנה זה רק צבעים שאני צבעתי (שינויים וכו')
ואני לא רוצה לאפס את הצבעים כי מאוד חשוב לי לדעת מה השינויים

 

[יעקב ויצמן]

האם הופעלה באקסל פקודת מאקרו?
האם אפשר לראות דוגמאות בצילום מסך?

 

[יעקב ויצמן]

שולח לך קישור https://docs.google.com/spreadsheets/d/1i3w7c0xHZ6lqLSQ3wZ4pZl4liwIMIrbSjqq8BnrmmB0/edit?usp=sharing
סימנתי לך את האימות נתונים שאני מדבר עליו זה צבוע בירוק שורה 20 רשמתי בהערות של שלב 1 שורה אחר שורה את הקושי
תודה רבה

אני אסכם את הפיתרון פה עם תוספת דוגמאות.
ראשית.אני מצרף כאן גיליון מיניאטורי לתירגול והבנה.את הכלים והטכניקות פה ניתן ליישם על כל טבלה גדולה/מורכבת ככל שתהיה.
אזכיר שוב את הלינק לאתר.זהו הבסיס להבנת המהלך פה.
חשוב לעבור כאן על כל השלבים.(יתכנו שגיאות ref בתאים לאחר העלאת הגיליון כאן.יש לתקן הכל לפי הסדר ולראות שהכל פועל כהלכתו)
בעמודה a- b ישנן טווחים בעלי שם.משורה 3 ואילך.
חשוב פה להגדיר מראש /להיכנס לנתונים>טווחים בעלי שם להגדיר את הטווח עד סוף העמודה.כך יהיה אפשרי להוסיף נתונים בטווח ללא שום בעיה.בדומה לאפקט "עצב כטבלה" באקסל.
נקודה נוספת חשובה.להקפיד לא לכתוב בעמודות אלו למטה ערכים שאינם כלולים בטווח נתונים.פשוט להשאיר את כל העמודה מוקדשת לערכי הטווח בלבד!

בעמודה c יש בתא c2 אימות נתונים שמוזן משמות הטווחים בתאים a2 -b2.
בעמודה d .תא d2 למעשה מפרט באימות נתונים את הרשימה מטווח של מה שנבחר בתא c2.
אם לדוגמא נבחר צבע בתא c2 .תא d2 "יחפש" במחסן טווח שנקרא "צבע" ואת הנתונים שלו יציג תוך כדי אימות נתונים.
בתא e2 .פונקצייה של indirect "פורסת לנו" את הטווח שנבחר בתא c2.
כך זה נראה

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

זהו הפיתרון עד כאן.
ועכשיו עוד תוספת עיונית "ולמדנית" על מהותה של פונקציית indirect.
הזכרתי את תוספת צבע הורוד בתא a10
ואפתח בכך.שכל "אימות נתונים מתוך טווח" משחק באופן אוטומט את תפקיד פונקציית unique .התוצאה =מיזוג כפיליות ודילוג על תאים ריקים.
כך האקסל וכלי אימות הנתונים מתוכנתים.!
שימו לב.שלולא שההיתי משלב בעמודה e את sort היתה מתקבלת תוצאה כזו

צבע הורוד במרחק שני תאים ריקים מתא e6.כמו שבטבלה a המרחק זהה.
אז נכון שבפונקצייה אני אכתוב indirect (c2)
(והרי בc2 באימות נתונים הכל מקובץ...).אבל הפונקצייה הזו מחזירה את תוכן התא מהמקור שלו (במידה ומדובר בהפניה)
וכאן המקור של תא c2 הוא בעמודה a.
הסתבכתם? לא נורא.אפשר לדלג על הקטע הסופי.
כך או כך.תהנו מהיופי והניתוח של הפונקצייה.
הניתוח הצליח והחולה הבריא!
בהצלחה!

 

[יעקב ויצמן]

עוד תוספת לשאלתך @שימי .ב
במידה וטווח הנתונים יהיה על יותר מעמודה אחת.בגיליון שצירפתי .יהיה צורך
א.להוסיף עמודה משמאל עמודה a
ב.להגדיר בטווח נתונים גם את עמודה b
ג.לקחת בחשבון שהנתונים של עמודה b יוצגו בעמודה G ולא בקיבוץ בעמודה F
(זה עוד הוכחה חותכת למה שחתמתי בסוף הודעתי הקודמת..)
פונקציית sort לא תספק את המענה לקיבוץ בעמודה אחת.
ניסיתי כמה פתרונות עם פונקציות נוספות.לא מצאתי פיתרון הולם .ואם מישהו יודע .אני אשמח לשמוע.

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

 

[יעקב ויצמן]

אין שם עיצוב מותנה זה רק צבעים שאני צבעתי (שינויים וכו')
ואני לא רוצה לאפס את הצבעים כי מאוד חשוב לי לדעת מה השינויים

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

מה שיתכן פה זה שעשית מילוי "בצבעים מתחלפים"
כמו כאן

לכל הפחות לפי הניסוי שעשיתי.הגיליון שצילמתי בשיטס לא פעל באקסל.
שיבושים כאלו הם אפשריים במקרים ספציפיים.
האלגוריתם שונה.קיימים גם הבדלים בשפת הקידוד למאקרו.(השיטס משתמש ב- Apps Script (גרסה של Javascript) ו- Excel משתמש ב- VBA)

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

 

[שימי .ב]

עוד תוספת לשאלתך @שימי .ב
במידה וטווח הנתונים יהיה על יותר מעמודה אחת.בגיליון שצירפתי .יהיה צורך
א.להוסיף עמודה משמאל עמודה a
ב.להגדיר בטווח נתונים גם את עמודה b
ג.לקחת בחשבון שהנתונים של עמודה b יוצגו בעמודה G ולא בקיבוץ בעמודה F
(זה עוד הוכחה חותכת למה שחתמתי בסוף הודעתי הקודמת..)
פונקציית sort לא תספק את המענה לקיבוץ בעמודה אחת.
ניסיתי כמה פתרונות עם פונקציות נוספות.לא מצאתי פיתרון הולם .ואם מישהו יודע .אני אשמח לשמוע.
צפה בקובץ המצורף 1332831

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

היי יעקב
ראשית תודה על העזרה והמסירות
יש לי בעיה באופן זה, כי זה מועיל ברגע שאני רוצה לעשות רשימה תלויה לפי שורה אחת
ברגע שארצה לעשות זאת למס' שורות אז אתקל בבעיה
לצורך הענין אם הבחירה בשורה הראשונה היא צבע אז הוא יוצר מחסן נתונים של צבע
ואז כשבשורה השניה אני בא ובוחר מידה הוא לא יוכל למשוך נתונים של צבע מאחר ומחסן הנתונים מוגדר לפי הבחירה הראשונה שזה צבע
כמו כאן https://docs.google.com/spreadsheets/d/1IEcg5B9ieot0gQb2s1uGg8SxY3tIWcsXlXDX7OpdhfM/edit?usp=sharing
לכן הלכתי בכיוון אחר תוכל לראות את העיקרון כאן https://youtu.be/14M3IGTDEfE
זה עובד טוב כל עוד לא מוסיפים שורות בגיליון הראשי
ברגע שמוסיפים שורות צ' כל פעם לעדכן את האימות נתונים שימשוך מהגביל שלו שביגליון השני וזה יוצר סירבול
אך מאחר ואני רוצה להוסיף שורות שם נתקעתי

 

[שימי .ב]

עוד תוספת לשאלתך @שימי .ב

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

תוכל לעשות זאת כמערך נתונים בתוך נוסחה לדוגמא ={A1:A10; B1:B10}

אז הוא יתן לך את האמור בעמוד A ואח"כ בהמשך את עמודה B ברצף , רק שאם יש שורות ריקות הוא יתן אותם

 

[אפר]

תוכל לעשות זאת כמערך נתונים בתוך נוסחה לדוגמא ={A1:A10; B1:B10}

אז הוא יתן לך את האמור בעמוד A ואח"כ בהמשך את עמודה B ברצף , רק שאם יש שורות ריקות הוא יתן אותם

אפשר להוסיף TRANSFORM ולהשתמש בגיליון אחר

 

[יעקב ויצמן]

היי יעקב
ראשית תודה על העזרה והמסירות
יש לי בעיה באופן זה, כי זה מועיל ברגע שאני רוצה לעשות רשימה תלויה לפי שורה אחת
ברגע שארצה לעשות זאת למס' שורות אז אתקל בבעיה
לצורך הענין אם הבחירה בשורה הראשונה היא צבע אז הוא יוצר מחסן נתונים של צבע
ואז כשבשורה השניה אני בא ובוחר מידה הוא לא יוכל למשוך נתונים של צבע מאחר ומחסן הנתונים מוגדר לפי הבחירה הראשונה שזה צבע
כמו כאן https://docs.google.com/spreadsheets/d/1IEcg5B9ieot0gQb2s1uGg8SxY3tIWcsXlXDX7OpdhfM/edit?usp=sharing
לכן הלכתי בכיוון אחר תוכל לראות את העיקרון כאן https://youtu.be/14M3IGTDEfE
זה עובד טוב כל עוד לא מוסיפים שורות בגיליון הראשי
ברגע שמוסיפים שורות צ' כל פעם לעדכן את האימות נתונים שימשוך מהגביל שלו שביגליון השני וזה יוצר סירבול
אך מאחר ואני רוצה להוסיף שורות שם נתקעתי

באמצעות indirect
זו משימה בלתי אפשרית
בצילום מסך המצורף תא d1 מוזן מאימות נתונים בתא a12
כאשר תא a12 מוזן מנתונים מתאים a1 עד a10
תא d1 יציג את התוצאות כמו המקור הראשון.כפי שהדגשתי שוב ושוב לעיל.
והואיל וברשימה המקורית הפריסה היא מa1 עד a10
חייבים לתת "ספייס" ושטח ריק מתחת לתא d1
על מנת לאפשר להציג את הנתונים בפריסה.
אם קיימת חסימה בדרך .תחטוף הודעת שגיאה.
לשבר את האוזן זה כמו שקיימת חניה פרטית לסמי טריילר .ונכנסו שם מכוניות פרטיות ותפשו לו את השטח.בעלי המכוניות היו אדיבים והותירו שטח פנוי של 2 מטר.נו מה יעשה נהג הסמי טריילר עם השטח הקטן הזה?
יצפור להם שיפנו את השטח!
זה בדיוק מה שהאקסל מתריע על הודעת שגיאה פה.זו צפירה לפנות את השטח!
מציאות של "לעייל פילא בקופא דמחטא" קיימת רק באימות נתונים סטנדרטי.אימות נתונים על אלגוריתם מתוחכם ויחודי וככה הוא בנוי.

ההצעה לפיתרון לא רלוונטית בתא d1 בצילום מסך הנל

 

[יעקב ויצמן]

אפשר להוסיף TRANSFORM ולהשתמש בגיליון אחר

הפונקצייה לא קיימת בשיטס.
ואשמח לדוגמא איך ניתנת לשילוב בתא d1 בגיליון הנל