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

[חד כתער]

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


נב, כל הAI למינהם לא מבינים מה אני רוצה מחייהם ומראשם, ומבלבלים אותי בעניין הנ"ל

 

[יעקב ויצמן]

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


נב, כל הAI למינהם לא מבינים מה אני רוצה מחייהם ומראשם, ומבלבלים אותי בעניין הנ"ל

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

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

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

 

[יעקוב ישראל]

.

 

[יעקוב ישראל]

יש תוסף לאקסל בשם 'אקסל פלוס' (מבית עסקית)
ניתן להורידו מהאתר שלהם: www.iskit.biz/excelplus.html
יש בו פונקציה של תאריך עברי [לדוגמא: בוחרים תא שכתוב בו 11/01/2019 והוא כותב בתא אחר שבחרנו: יום שישי ה' שבט תשע"ט]
וכן פונקציות נוספות חכמות ולעיתים שימושיות (הוספת תוים, מחיקת תוים, הפיכת טקסט, אנגלית לעברית ועוד)

תודה ענקית.
אפשר הסבר מפורט איך ממירים מלועזי לעברי בעזרת התוסף?

 

[חד כתער]

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


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

 

[יעקב ויצמן]

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


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

ראשית שמח לשמוע.
לגבי הספרות.sumif תסכום את התאים למרות הרווח.

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

 

[מדויק]

מקווה שהבנתי....



Sub Filter_CellValue1()
ActiveSheet.Range("B4:E15").AutoFilter field:=2,
Criteria1:=Cells(5, 7).Value
End Sub



זו דוגמא למאקרו פילטר
תא G7 שם ערך החיפוש
הrange הוא טווח התאים שיש להחיל עליהם את הפילטר.(תשנה לטווח המתאים)
2-היא העמודה מתחילת הטווח (b) במקרה הזה.העמודה השניה היא עמודה c.בעמודה c יחול הסינון בפועל
הפטנט פה.שאם תעשה בתא g7 אימות נתונים (של שמות הלקוחות) אז כל פעם שתבחר שם שם מסוים.המאקרו יבצע את הסינון בטווח שכתוב במאקרו.

מקווה שזה יתן מענה לשאלתך..

אני לא מתמצא בכתיבת מאקרו

אשמח אם תוכל קצת יותר להרחיב - תודה!

 

[חד כתער]

ראשית שמח לשמוע.
לגבי הספרות.sumif תסכום את התאים למרות הרווח.

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

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

 

[יעקב ויצמן]

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

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

 

[יעקב ויצמן]

אני לא מתמצא בכתיבת מאקרו

אשמח אם תוכל קצת יותר להרחיב - תודה!

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

 

[יעקב ויצמן]

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


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

כעת קראתי שוב את ההודעה
א.לא הבנתי את השאלה על הסימון בעמודה a
תא a1 כתבתי בהזנה ידנית במכוון עם רווח כדי להדגים.עם מילוי תא צהוב להדגשה.
עמודה b .מורידה רווחים אם ישנם.ואם אינם היא מחזירה את תוכן התא ככתבו.
ב.מה שכתבת בסוף.לספור כמה פעמים כתוב בעמודה a
123
צריך countif ולא soumif
וכפי שהזכרתי .שבספרות אין שום נפקא מינה לרווחים

 

[יעקב ויצמן]

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

שים לב @חד כתער שההפניה כאן היא לעמודה a בלבד ובכל זאת sumif סכם את תא a1 (הרווח קיים שם .לא שיניתי מאומה)
מה קרה פה?
אסביר:נוספה כאן כוכבית * לפני הקריטריון (אברהם)
מהו תפקידה של הכוכבית?

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

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

תהנה מהקסם!

 

[מדויק]

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

 

[יעקב ויצמן]

אבל אני עדיין חושב שיותר נכון להשתמש ב trim כי ניסתי עם הכוכביות וכתבתי אברהם משה - ובתא אחרת אברהם שלמה, שזה בעצם שני שמות שונים וסיכם לי את שניהם
צפה בקובץ המצורף 1365303

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

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

ושוב.תודה לך על ההערה הנכונה!

 

[מרובע]

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

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

 

[Yanky Itskowitz]

איך אני מחשב מע"מ?

 

[Yanky Itskowitz]

איך אני עושה בשיטס משותף
שגיליון 5 רק X יכול לראות?

 

[יעקב ויצמן]

איך אני מחשב מע"מ?

איך לחשב מע"מ באקסל - 17% * הסכום | אקסלנט הדרכות אקסל

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

www.excelent.co.il

 

[יעקב ויצמן]

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

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

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

 

[מרובע]

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

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

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

1	א
2	X
3	ב
4	X
5	X
6	ג
7	ד
8	X

נוסחה שתחפש X תביא לי את התוצאה: 2. 4. 5. 8.
האם יש דבר כזה?