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

[efrat1992]

מחפשת דרך להסיר מס' תווים מתוך טקסט

דוג' יש לי מס' 5256584225456
ואני רוצה למחוק את הספרות 22

אודה לעזרה

 

[יעקב ויצמן]

מחפשת דרך להסיר מס' תווים מתוך טקסט

דוג' יש לי מס' 5256584225456
ואני רוצה למחוק את הספרות 22

אודה לעזרה

זה ניתן לביצוע בקלות בעזרת פונקציית substitute

אנו אומרים לאקסל שיחליף צמד של טקסט כמו 22
לכלום /ריק ""
בהצלחה

 

[רק שאלה!]

יש לי שני רשימות מיילים
איך אוכל לבדוק אילו מיילים יש רק ברשימה השניה ואין בראשונה
תודה!!!

 

[אבי2021]

יש לי שני רשימות מיילים
איך אוכל לבדוק אילו מיילים יש רק ברשימה השניה ואין בראשונה
תודה!!!

נניח שרשימה ראשונה בעמודה A.
ורשימה שניה בעמודה B.
תעמוד בעמודה C ותעתיק >>>

=IF(COUNTIF($A$1:$A$12,B1),"מופיע גם בעמודה שניה","מופיע רק בעמודה ראשונה")

 

[יעקב ויצמן]

נניח שרשימה ראשונה בעמודה A.
ורשימה שניה בעמודה B.
תעמוד בעמודה C ותעתיק >>>

=IF(COUNTIF($A$1:$A$12,B1),"מופיע גם בעמודה שניה","מופיע רק בעמודה ראשונה")

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

 

[ישראלי1]

פונקציה FILTER שמחזיר #REF! היות ויש 2 תוצאות והוא צריך 2 תאים לרשום את התוצאה
- והבעיה שבתא הסמוך יש בו טקסט (וא"א לפנות תא נוסף כי זה רץ על מאות תאים)

האם יש דרך לחבר את 2 התוצאות (כמובן שרק במקרה הנ"ל) לתא אחד- סוג של TEXTJOIN ולהגדיר שיתן כל פעם רק תוצאה אחת

 

[יעקב ויצמן]

שאלתך שאלה יפה,מעניינת ומאתגרת!

פונקציה FILTER שמחזיר #REF!

הארה: מדובר בגיליון שיטס.באקסל לעומת זאת השגיאה שתוחזר היא spill

היות ויש 2 תוצאות והוא צריך 2 תאים לרשום את התוצאה
- והבעיה שבתא הסמוך יש בו טקסט (וא"א לפנות תא נוסף כי זה רץ על מאות תאים)

להלן אציג את הבעיה בגיליון דוגמא המצורף ואציע מספר פתרונות.

האם יש דרך לחבר את 2 התוצאות (כמובן שרק במקרה הנ"ל) לתא אחד- סוג של TEXTJOIN ולהגדיר שיתן כל פעם רק תוצאה אחת

אקדים שני דברים:
א.פונקציית filter מתוכנתת כחלק מה-d.n.a שלה שתפרוס ("תשפוך") את כל טווח התא/ים הרלוונטי על פי התא/ים שהוזנו בפונקצייה.
ב.פונקצייה נבדלת מפונקציות כמו vlookup או xlookup וכדומה בכך שהיא מחזירה יותר מתוצאה אחת (אם יתר התוצאות רלוונטיות כמובן על פי התנאי/ים)
כך שניסיון לשנות "מהטבע שלה" ושתחזיר תוצאה אחת בלבד, הוא בגדר אותו משל שאמר ירמיהו הנביא(ירמיהו יג.כג) " הֲיַהֲפֹךְ כּוּשִׁי עוֹרוֹ וְנָמֵר חֲבַרְבֻּרֹתָיו...?"

כעת.אציג צילום מסך מתוך הגיליון המצורף ואסביר:

עמודות A וB
הן טבלת מקור.השם אברהם הוא השם היחיד שיש לו שני תוצאות של עיר מגורים ירושלים ובני ברק.

עמודה D
בתא d1 קיימת הפונקצייה:

=UNIQUE(A1:A)

ומתקיים כאן מיזוג נתונים.השם אברהם יופיע רק פעם אחת.

עמודה E
בתא e1 קיימת פונקציית filter .הפונקצייה נגררה כלפי מטה לתאים הסמוכים

=FILTER($B$1:$B,$A$1:$A=D1)

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

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

עמודה F
ובכן.הפיתרון הכי פרקטי ואידיאלי (לפחות בעיניי...) הוא לעטוף את הפונקצייה בפונקציית textjoin
כך הפונקציות נראות בתא f1 למשל:

=TEXTJOIN(CHAR(10),,FILTER($B$1:$B,$A$1:$A=D1))

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

עמודה G
בתא g1 למשל מוזנות שלל הפונקציות האלו:

=INDEX(FILTER($B$1:$B,$A$1:$A=D1),RANDBETWEEN(1,COUNTA(FILTER($B$1:$B,$A$1:$A=D1))))

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

עמודה H
בתא h1 למשל מוזנות הפונקציות דלהלן:

=TRANSPOSE(FILTER($B$1:$B,$A$1:$A=D1))

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


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


בהצלחה רבה!

 

[ישראלי1]

@יעקב ויצמן
ישר כח על התשובה שמוסבר בטוב טעם

לפי מה שכתבת לעיל - יש דרך בFILTER שנכניס מפריד בTEXTJOIN סוג של מקף -
ואז נחפש בעזרת FIND את מספר המיקום של המקף - הנ"ל
ובעזרת MID נשלוף רק את מה שצריך

 

[יעקב ויצמן]

@יעקב ויצמן
ישר כח על התשובה שמוסבר בטוב טעם

בשמחה רבה!

לפי מה שכתבת לעיל - יש דרך בFILTER שנכניס מפריד בTEXTJOIN סוג של מקף -
ואז נחפש בעזרת FIND את מספר המיקום של המקף - הנ"ל
ובעזרת MID נשלוף רק את מה שצריך

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

 

[y41]

שאלה במאקרו אקסל.
יש לי קובץ אקסל שאני מכניס בו נתונים ורוצה לשמור את הנתונים בקובץ CSV וששם הקובץ יהיה חתימת הזמן (תאריך+שעה).
יצרתי נוסחה בגיליון אחר שתתן לי את שם הקובץ (שמשתנה בכל פעם שיש רענון).
יצרתי פקודת מאקרו שאמורה לבצע את הפעולה של שמירת הקובץ בשם כקובץ CSV.
כאשר אני שולח את המאקרו לשדה בגיליון הפעיל הכל מצליח לי (אבל יש לי עם זה בעיה כי אני לא רוצה שבקובץ CSV יופיע השדה של שם הקובץ).
השאלה היא איך אני יכול לשלוח אותו לגליון עזר במקום לגיליון הנוכחי? להלן פקודת המאקרו:
Sub ùîéøä()
'

'

Dim strfolder As String
Dim strfilename As String

strfolder = "D:\11"
strfilename = Range("P7").Value
ActiveWorkbook.SaveAs Filename:=strfolder & "\" & strfilename, FileFormat:=22
End Sub

 

[מתן מאור]

שאלה במאקרו אקסל.
יש לי קובץ אקסל שאני מכניס בו נתונים ורוצה לשמור את הנתונים בקובץ CSV וששם הקובץ יהיה חתימת הזמן (תאריך+שעה).
יצרתי נוסחה בגיליון אחר שתתן לי את שם הקובץ (שמשתנה בכל פעם שיש רענון).
יצרתי פקודת מאקרו שאמורה לבצע את הפעולה של שמירת הקובץ בשם כקובץ CSV.
כאשר אני שולח את המאקרו לשדה בגיליון הפעיל הכל מצליח לי (אבל יש לי עם זה בעיה כי אני לא רוצה שבקובץ CSV יופיע השדה של שם הקובץ).
השאלה היא איך אני יכול לשלוח אותו לגליון עזר במקום לגיליון הנוכחי? להלן פקודת המאקרו:
Sub ùîéøä()
'

'

Dim strfolder As String
Dim strfilename As String

strfolder = "D:\11"
strfilename = Range("P7").Value
ActiveWorkbook.SaveAs Filename:=strfolder & "\" & strfilename, FileFormat:=22
End Sub

הצורה הזאת עובדת לי:

Sub שמירה()
'
'
    Dim WB As Workbook
    Set WB = Workbooks.Open("file path.xlsx")
   
    Dim strfolder As String
    Dim strfilename As String

    strfolder = "D:\11"
    strfilename = Range("P7").Value
    WB.SaveAs Filename:=strfolder & "\" & strfilename, FileFormat:=22

End Sub

ולהבא, עדיף לכתוב את הקוד באמצעות תיבת קוד, ככה הקוד קריא יותר ונעים לעין:

 

[I believe]

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

 

[יעקב ויצמן]

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

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

 

[I believe]

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

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

 

[יעקב ויצמן]

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

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

 

[I believe]

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

 

[יעקב ויצמן]

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

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

 

[רק שאלה!]

איך משנים באקסל את השעות מ - 13:00 ל 1:00 בלי לרשום 1:00 PM

 

[מתן מאור]

איך משנים באקסל את השעות מ - 13:00 ל 1:00 בלי לרשום 1:00 PM

נתקלתי בבעיה דומה בעבר ולא מצאתי לה פתרון מובנה באקסל.
ישנן שתי אפשרויות לפתור אותה בצורה עקיפה:

1. שימוש בעמודת עזר והמרת התאריך לטקסט​

נניח שבעמודה A נכתבים השעות (אפשר לכתוב אותן בכל פורמט שהוא), בעמודה B נוסיף את הנוסחה הבאה:​

=SUBSTITUTE(SUBSTITUTE(TEXT(A1,"h:mmAM/PM"),"AM",""),"PM","")

הנוסחה ממירה לטקסט את השעה שמופיעה בעמודה A לפורמט שתיים עשרה שעות ואז מוחקת את התוספת AM/PM מהטקסט.​

החסרונות: ​

א. מצריך שימוש בעמודת עזר.​

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

​

2. הסתרת הכיתוב AM/PM בשורה השנייה​

בעמודה שבה מיוצגים השעות בחר תבנית עיצוב של שתיים עשרה שעות.​

לאחר מכן עבור ללשונית "מותאם אישית" העמד את סמן העכבר כפי שמוצג בתמונה - הקש Ctrl + j ולחץ על אישור, על מנת להעביר את סימון ה- AN/PM לשורה השנייה בתא:​

לאחר מכן בחר באופציה "גלישת שורות" והשאר את התא בגודלו המקורי. כל המידע יישמר בתא וניתן יהיה לבצע פעולות חשבוניות על הנתונים שבתא, אבל סימון ה- AM/PM לא יוצג לעיני המשתמש מחוסר מקום:​

​

 

[רבישע נייעס]

קיים אקסל עם יומן הוצאות