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

efrat1992

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

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

אודה לעזרה
 

יעקב ויצמן

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

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

אודה לעזרה
זה ניתן לביצוע בקלות בעזרת פונקציית substitute
Screenshot_20240505_141644_Sheets.jpg

אנו אומרים לאקסל שיחליף צמד של טקסט כמו 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 וכדומה בכך שהיא מחזירה יותר מתוצאה אחת (אם יתר התוצאות רלוונטיות כמובן על פי התנאי/ים)
כך שניסיון לשנות "מהטבע שלה" ושתחזיר תוצאה אחת בלבד, הוא בגדר אותו משל שאמר ירמיהו הנביא(ירמיהו יג.כג) " הֲיַהֲפֹךְ כּוּשִׁי עוֹרוֹ וְנָמֵר חֲבַרְבֻּרֹתָיו...?"

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


עמודות 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 תוצאות ,יש לפנות עמודות ריקות בסמיכות וכפי הצורך..


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


בהצלחה רבה!
 

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

  • filter function #ref error.xlsx
    KB 48.3 · צפיות: 14
נערך לאחרונה ב:

ישראלי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
ולהבא, עדיף לכתוב את הקוד באמצעות תיבת קוד, ככה הקוד קריא יותר ונעים לעין:
1715325820917.png
 
נערך לאחרונה ב:

I believe

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

יעקב ויצמן

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

I believe

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

יעקב ויצמן

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

I believe

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

יעקב ויצמן

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

מתן מאור

משתמש סופר מקצוען
כתיבה ספרותית
איך משנים באקסל את השעות מ - 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 בשורה השנייה
בעמודה שבה מיוצגים השעות בחר תבנית עיצוב של שתיים עשרה שעות.​
1715584175305.png
לאחר מכן עבור ללשונית "מותאם אישית" העמד את סמן העכבר כפי שמוצג בתמונה - הקש Ctrl + j ולחץ על אישור, על מנת להעביר את סימון ה- AN/PM לשורה השנייה בתא:​
1715584306543.png
לאחר מכן בחר באופציה "גלישת שורות" והשאר את התא בגודלו המקורי. כל המידע יישמר בתא וניתן יהיה לבצע פעולות חשבוניות על הנתונים שבתא, אבל סימון ה- AM/PM לא יוצג לעיני המשתמש מחוסר מקום:​
1715584390919.png
 

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

הפרק היומי

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


תהילים פרק קיט ע'

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

אתגר AI

חשיפה כפולה • אתגר 130

לוח מודעות

למעלה