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

[פנטה]

שלום רב
אשמח לדעת איך אפשר למלאות במחשב קובץ סגור( PDF) ?

אני חושבת שצריך תוכנה בתשלום

 

[יעקב ויצמן]

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

=IF(COUNTIF($A$1:$A$10,A1)<=1,1,0)

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

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

נ.ב שמחתי לראותך שוב כאן עימנו...וכל הכבוד לך על השיתוף!

 

[יעקב ויצמן]

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

https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13136027

 

[אחת שאוהבת להבין]

עשיתי, זה לא כ"כ ארוך, נראה לי שהכל מחושב אבל צריך לבדוק שוב
(אני עשיתי IF מקונן כי לצערי אין לי IFS באקסל כרגע)

לדעתי אפשר לעשות SOM פשוט מתוך פילטר מקונן אך אין לי זמן להאריך או לנסות כרגע, אולי מאוחר יותר

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

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

 

[קול תורה]

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

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

 

[יעקב ויצמן]

@נתיי
לשאלתך מצורף כאן גיליון עם מספור לפי עמודות עד דף שצב (ההמשך לא רלוונטי כפי שכתבתי לעיל בנושא "באג שצב בוורד")
הסבר קצר על המתרחש פה:
עמודה a נותרה כפי שהיתה.
בעמודה b ערכתי פונקציית substitute שממירה . ל:
צפה בקובץ המצורף 1570155
ובעמודה c קיימת פונקציית tocol בתא c1 שמחזירה מערך של שתי העמודות למערך חד מימדי בצורה מסודרת של תא מתחת לתא.
מה נותר פה? פשוט להעתיק את עמודה c ולהדביק כערכים היכן שתחפוץ...
צפה בקובץ המצורף 1570157
בעבר הזכרתי רבות את הפונקצייה הזו.היא פשוט גאונית ועוצמתית!(להזכיר,הפונקצייה זמינה באופיס 365 בלבד או בשיטס)
והנה עוד ראייה לכך...בלחיצת כפתור קטנה כל הקסם הזה קרה..
בהצלחה!

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

אסביר בקיצור נמרץ:
בעמודה A כנ"ל כל הערכים מיובאים מוורד.וכנ"ל הספירה כאן עד שצב בלבד.
ומתחת לטווח זה יש לכתוב ערך כל שהוא עד לסיום טבלה ואני השתמשתי כדוגמא בספרה 1.
(יש לכך הכרח משתי סיבות.
1.בכדי שיתאפשר פעולת מיון בטווח .ומוכרח שלא יהיו תאים ריקים.
2.הספרה 1 משמשת ככלי השוואה לפונקציית if בעמודה d)
בעמודה b -ביצעתי 2 סדרות של מספור אוטומטי עד 392.וזה בוצע על ידי שילוב פונקציות mod+sequence בטכניקה שכתבתי בזמנו (כחלק מסדרת המאמרים שכתבתי לאחרונה) בנושא פונקציית sequence
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13167983
בסופו של דבר צריך להעתיק את תכולת ערכי המספרים ולהדביקם כערכים בלבד בעמודה c.
מי שמעונין ביצירת מספור בהליך הידוע של העתקת שני תאי מספר וגרירה יכול בהחלט לדלג על עמודה b
בעמודה d קיים שילוב פונקציות if + substitue.(תראו בגיליון)
חשוב לזכור כי לאחר ההדבקת ערכים בעמודה c יש למחוק את עמודה b על מנת שיהיה ניתן לבצע מיון בטווח טבלה זו
ההליך האחרון(אחרון חביב...) כאן.הוא ביצוע מיון בעמודה c מהקטן לגדול.ואז כל עמודה d תסתדר בן רגע לפי מספר הדפים והעמודות.נסו ותראו!

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

בהצלחה!

 

[xl3391]

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

אפשר עם FILTER או עם SUMPRODUCT בשניהם על אותו רעיון של הכפלה ב1 כאשר אין ערך.

=SUM(FILTER(טבלה1[שכר],IF([@מתאריך]="",1,טבלה1[תאריך]>=[@מתאריך])*IF([@[עד תאריך]]="",1,טבלה1[תאריך]<[@[עד תאריך]])*IF([@עבודה]="",1,טבלה1[עבודה]=[@עבודה]),0))

=SUMPRODUCT(טבלה1[שכר]*IF([@מתאריך]="",1,טבלה1[תאריך]>=[@מתאריך])*IF([@[עד תאריך]]="",1,טבלה1[תאריך]<[@[עד תאריך]])*IF([@עבודה]="",1,טבלה1[עבודה]=[@עבודה]))

 

[אחת שאוהבת להבין]

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

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

 

[משכנתאות ncm]

@נתיי
אחרי הודיעי אותך את כל זאת,נזכרתי שניתן להגיע כאן לפיתרון שווה בדרך מעט שונה ומעניינת מאוד(לא פחות...)
מה שמעניין בדרך זו הוא הטריק "במהלך הכללי" של הפתרון.
חובבי אקסל ושות' נוהגים להשתמש בטריק זה כאשר רוצים להוסיף בטבלה גדולה שורה ריקה בין שורה לשורה (קיימת).
ניתן ללמוד על טריק זה בלינק המצורף כאן ובסרטון הדרכה כאן
וכאמור בפיתרון להלן,בוצע שימוש בטכניקה זו ויש גם עוד כמה תוספות...
צפה בקובץ המצורף 1570996
אסביר בקיצור נמרץ:
בעמודה A כנ"ל כל הערכים מיובאים מוורד.וכנ"ל הספירה כאן עד שצב בלבד.
ומתחת לטווח זה יש לכתוב ערך כל שהוא עד לסיום טבלה ואני השתמשתי כדוגמא בספרה 1.
(יש לכך הכרח משתי סיבות.
1.בכדי שיתאפשר פעולת מיון בטווח .ומוכרח שלא יהיו תאים ריקים.
2.הספרה 1 משמשת ככלי השוואה לפונקציית if בעמודה d)
בעמודה b -ביצעתי 2 סדרות של מספור אוטומטי עד 392.וזה בוצע על ידי שילוב פונקציות mod+sequence בטכניקה שכתבתי בזמנו (כחלק מסדרת המאמרים שכתבתי לאחרונה) בנושא פונקציית sequence
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13167983
בסופו של דבר צריך להעתיק את תכולת ערכי המספרים ולהדביקם כערכים בלבד בעמודה c.
מי שמעונין ביצירת מספור בהליך הידוע של העתקת שני תאי מספר וגרירה יכול בהחלט לדלג על עמודה b
בעמודה d קיים שילוב פונקציות if + substitue.(תראו בגיליון)
חשוב לזכור כי לאחר ההדבקת ערכים בעמודה c יש למחוק את עמודה b על מנת שיהיה ניתן לבצע מיון בטווח טבלה זו
ההליך האחרון(אחרון חביב...) כאן.הוא ביצוע מיון בעמודה c מהקטן לגדול.ואז כל עמודה d תסתדר בן רגע לפי מספר הדפים והעמודות.נסו ותראו!
צפה בקובץ המצורף 1571092
לסיכום.הפיתרון כאן הוא מעט יותר ארוך מקודמו.אך יש בו הזדמנות ללימוד כדרך אגב (אגב אורחא קמ"ל..) על טריק נפלא ויפה ששווה ללומדו...

בהצלחה!

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

 

[אחת שאוהבת להבין]

אפשר עם FILTER או עם SUMPRODUCT בשניהם על אותו רעיון של הכפלה ב1 כאשר אין ערך.

=SUM(FILTER(טבלה1[שכר],IF([@מתאריך]="",1,טבלה1[תאריך]>=[@מתאריך])*IF([@[עד תאריך]]="",1,טבלה1[תאריך]<[@[עד תאריך]])*IF([@עבודה]="",1,טבלה1[עבודה]=[@עבודה]),0))

=SUMPRODUCT(טבלה1[שכר]*IF([@מתאריך]="",1,טבלה1[תאריך]>=[@מתאריך])*IF([@[עד תאריך]]="",1,טבלה1[תאריך]<[@[עד תאריך]])*IF([@עבודה]="",1,טבלה1[עבודה]=[@עבודה]))

זה ממש מרשים איך שהצלחת לקצר את כל הנוסחה הארוכה הזו באמצעות הנוסחאות הללו, בזכותך למדתי על עולם ה-FILTER.
אז בעצם נוסחת SUM עם FILTER מייתרת את הצורך בפונקציות SUMIF ו-SUMIFS לכאורה, לא?
אשמח רק להבין מה הרעיון בהשמת הספרה 1 במקום תא ריק, איך שאני מבינה זה סתם מספר אקראי ואפשר לשים כל מספר שרוצים, אבל עדיין לא ממש הבנתי מה הרעיון בזה.

 

[יעקב ויצמן]

ישר כח גדול עזרת לי להתקדם מאד!

בשמחה רבה.שמח לשמוע

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

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

בכל אופן אם הבנתי נכון אין פתרון ל'תסמונת השצ"ב'

אין פיתרון מובנה לכך מבית מיקרוסופט.(קשה להבין למה...אבל זה המצב,וחבל שכך...)
בלינק לעיל ששלחתי הוצע פיתרון דרך האקסל עם vba-מאקרו.
ישנו פיתרון להתחיל את המספור בוורד מאות ת(ובין לבין להשלים בהזנה ידנית).אבל גם כאן המספור תם באותיות תתצב.(והדרא קושיא לדוכתא...)
וישנו תוסף לוורד שהוצע ונבנה כאן על ידי @רחמיםR .ניתן לדבר איתו על כך אם זה רלוונטי.
https://www.prog.co.il/threads/באג-שצג.492596/post-7032798

 

[נ.ב.]

יש להציב בעמודה פונקציית if מקונן ראו כאן מדריך
או פונקציית ifs ראו כאן מדריך
או פונקציית switch (יש להקפיד מאוד מאוד בסדר התנאים בפונקצייה זו)
כתבתי עליה בעבר באריכות עם דוגמא בלינק כאן
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-12766300
אם משהו לא מסתדר נא לשלוח גיליון דוגמא
בהצלחה רבה!

חוזרת...
פונקציית IF עזרה כשנתתי 2 דירוגים
אני צריכה 6 דירוגים, אודה לעזרה
א=90-100
ב'1=85-90
ב'2=80-85
ג'1=75-80
ג'2=70-75
ד'=0-70

הנה לינק לקובץ>>

 

[עריכה מקצועית]

אני מנסה להעתיק מקובץ לקבוץ שמות
ומביא לי את השגיאה הזו
משהו יודע לעזור לי?

 

[עריכה מקצועית]

אני מנסה להעתיק מקובץ לקבוץ שמות
ומביא לי את השגיאה הזו
משהו יודע לעזור לי?
צפה בקובץ המצורף 1571602

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

 

[יעקב ויצמן]

חוזרת...
פונקציית IF עזרה כשנתתי 2 דירוגים
אני צריכה 6 דירוגים, אודה לעזרה
א=90-100
ב'1=85-90
ב'2=80-85
ג'1=75-80
ג'2=70-75
ד'=0-70

הנה לינק לקובץ>>

שלחתי בקשה לעריכה
נא אשרי

 

[save as]

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

העתק > הדבק ערכים

 

[נ.ב.]

שלחתי בקשה לעריכה
נא אשרי

פתחתי הרשאת עריכה

 

[xl3391]

זה ממש מרשים איך שהצלחת לקצר את כל הנוסחה הארוכה הזו באמצעות הנוסחאות הללו, בזכותך למדתי על עולם ה-FILTER.
אז בעצם נוסחת SUM עם FILTER מייתרת את הצורך בפונקציות SUMIF ו-SUMIFS לכאורה, לא?
אשמח רק להבין מה הרעיון בהשמת הספרה 1 במקום תא ריק, איך שאני מבינה זה סתם מספר אקראי ואפשר לשים כל מספר שרוצים, אבל עדיין לא ממש הבנתי מה הרעיון בזה.

כשאת מסכמת רק מה שסיננת אין צורך בSUMIF/S.
לגבי ה1, משמעותו TRUE ו0 משמעותו FALSE. זה נכון שאקסל מתייחס לכל המספרים חוץ מ0 כTRUE אז בפילטר את יכולה לכאורה לשים כל מספר, אבל בsumproduct לא, מכיוון ששם יש הכפלה של הטווחים וסיכום שלהם. כשהתנאי לא מתקיים יש הכפלה ב0 ולכן אותו ערך נכלל בסיכום כ0. הכפלה ב1 משאירה את הערך כמו שהוא, אבל אם תכפילי במספר אחר תקבלי ערך אחר שיכלל בסיכום.

 

[xl3391]

חוזרת...
פונקציית IF עזרה כשנתתי 2 דירוגים
אני צריכה 6 דירוגים, אודה לעזרה
א=90-100
ב'1=85-90
ב'2=80-85
ג'1=75-80
ג'2=70-75
ד'=0-70

הנה לינק לקובץ>>

אפשר בXLOOXUP פרמטר אחרון 1

 

[אחת שאוהבת להבין]

כשאת מסכמת רק מה שסיננת אין צורך בSUMIF/S.
לגבי ה1, משמעותו TRUE ו0 משמעותו FALSE. זה נכון שאקסל מתייחס לכל המספרים חוץ מ0 כTRUE אז בפילטר את יכולה לכאורה לשים כל מספר, אבל בsumproduct לא, מכיוון ששם יש הכפלה של הטווחים וסיכום שלהם. כשהתנאי לא מתקיים יש הכפלה ב0 ולכן אותו ערך נכלל בסיכום כ0. הכפלה ב1 משאירה את הערך כמו שהוא, אבל אם תכפילי במספר אחר תקבלי ערך אחר שיכלל בסיכום.

תודה רבה על ההסבר המפורט.