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

[יעקב ויצמן]

כמו שכתבת שיעור כללי שלם עם "עמקות"...

אבל עוד לא הבנתי למה חיסור כפול זה true ?

סימן חיסור כפול שמוסיפים.זה לא true
אלא זה "מתווך" שעוזר לאקסל לתרגם את הביטוי לוגי true ל1.ואת false ל0

 

[מים אחרונים]

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

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

מה אני כותב בתכלס?

 

[מים אחרונים]

מה אני כותב בתכלס?

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

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

ואיפה אני מכניס את הנוסחה?

https://docs.google.com/spreadsheets/d/1yYb7156HawGik5bDgNMuFeUCtniLSQ9es7VH5cN6CIY/edit#gid=0

 

[יעקב ויצמן]

נא אשר גישה למסמך

 

[מים אחרונים]

קיבלתי את הקוד הבא מצ'אט GBT
מה אני עושה איתו?

בכיף! הנה דוגמה לקוד VBA באקסל שמסנן את המילים בעמודה D ומעביר אותן לגליון נפרד בשם "טופל בס"ד":

```vba
Sub סננת_מילים()
    Dim סורה As Range
    Dim צילום_סורה As Range
    Dim גליון_סורה As Worksheet
    Dim גליון_מקור As Worksheet
    Dim מילה As String
    
    ' הגדרת גליון מקור
    Set גליון_מקור = ThisWorkbook.Sheets("Sheet1") ' שנה את "Sheet1" לשם הגליון המתאים
    
    ' צור גליון חדש בשם "טופל בס"ד אם אינו קיים כבר
    On Error Resume Next
    Set גליון_סורה = ThisWorkbook.Sheets("טופל בס""ד")
    On Error GoTo 0
    If גליון_סורה Is Nothing Then
        Set גליון_

 

[מים אחרונים]

@יעקב ויצמן
תודה עד כה!
שאלה נוספת אם אני מעוניין בשליחת הודעה מ appsheet לווצאפ עם נתונים מסויימים בכל פעם מהאקסאל
וכשיחזרו מילים מסויימות מהווצאפ המערכת תעדכן את הנתונים בעמודה לפי המילים שחזרו

 

[יעקב ויצמן]

@יעקב ויצמן
תודה עד כה!

בשמחה רבה!

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

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

 

[יעקב ויצמן]

קיבלתי את הקוד הבא מצ'אט GBT
מה אני עושה איתו?

בכיף! הנה דוגמה לקוד VBA באקסל שמסנן את המילים בעמודה D ומעביר אותן לגליון נפרד בשם "טופל בס"ד":

```vba
Sub סננת_מילים()
    Dim סורה As Range
    Dim צילום_סורה As Range
    Dim גליון_סורה As Worksheet
    Dim גליון_מקור As Worksheet
    Dim מילה As String
   
    ' הגדרת גליון מקור
    Set גליון_מקור = ThisWorkbook.Sheets("Sheet1") ' שנה את "Sheet1" לשם הגליון המתאים
   
    ' צור גליון חדש בשם "טופל בס"ד אם אינו קיים כבר
    On Error Resume Next
    Set גליון_סורה = ThisWorkbook.Sheets("טופל בס""ד")
    On Error GoTo 0
    If גליון_סורה Is Nothing Then
        Set גליון_

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

 

[shzofa]

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

 

[יעקב ויצמן]

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

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

 

[shzofa]

כן! תודה רבה! חסכת לי זמן רב

 

[יעקב ויצמן]

כן! תודה רבה! חסכת לי זמן רב

מצוין.שמחתי לעזור

 

[יעקב ויצמן]

רבותי,חברי הפורום היקרים.כדרכנו -אפתח במילי דאורייתא.בפרשת השבוע שנקרא בש"ק "כי תשא" נאמר "ונתנו איש כופר נפשו..."(שמות ל.י"ב) וכותב על כך בעל הטורים: "ונתנו. אם תקראנו למפרע יהי' ג"כ ונתנו לומר לך כל מה שאדם נותן לצדקה יחזור אליו ולא יחסר לו בשביל זה כלום".
ולהבדיל בין קודש לחול…על המונח/מושג פָּלִינְדְרוֹם כבר שמעתם?מהו פלינדרום?
ובכן.זהו המונח למילה/משפט/מספר/רצף סמלים אחר,שקריאתו מימין לשמאל ומשמאל לימין זהה.
מקור המונח הוא מיוונית: πάλιν (פַּלִין - "שוב", "הפוך"), δρóμος (דרוֹמוֹס - "כיוון").
חשוב לזכור כי בפלינדרום מילולי נהוג בהיבט הפלינדרומי להתעלם מרווחים/סימני ניקוד/סימני פיסוק/הבדלים בין אותיות קטנות לגדולות בשפות הרלוונטיות, ובין אותיות רגילות לסופיות בשפות הרלוונטיות. בהמשך עוד אשוב לנקודה זו.

אני אתן מספר דוגמאות לפלינדרומים:
במילים:- אבא,ישי,דוד,מילים.
במספרים:- 11,999.606.(מספר חד-ספרתי הוא פלינדרום טריוויאלי…)
במשפטים:- ילד נתן דלי, מום תחת מום,
בתאריכים:- 29.11.1192 או 12.02.2021
אגב התאריך 02.02.2020 נחשב כיום פלינדרומי בינלאומי,(גם בצפון אמריקה,שם נוהגים לכתוב את מספר החודש לפני היום.וגם במדינות אחרות שנוהגים שם לכתוב את מספר השנה לפני החודש והיום)
באומנות:- הפלינדרום מהווה כלי ליצירות ספרותיות רבות מחכמי ישראל (החל "מתור הזהב" בספרד על ידי רבי אברהם אבן עזרא ורבי יהודה אלחריזי ועוד),בעולם ובישראל, (הפלינדרום הארוך ביותר בעברית -"גישה אל שיא איש לא השיג" באורך 2,552 מילים ו-8,668 אותיות נכתב על ידי ישראלי בשם נועם דובב ופורסם ב5/2022 ראו כאן) וכן ביצירות מוזיקליות שונות שנכתבו…
בתחום הביולוגיה המולקולרית/גנטיקה:- הפלינדרום מהווה כלי למחקר ופיתוח (הנדסה גנטית וכו…)
אתן דוגמא קטנה לכך: - רצף (חד גדילי) בשרשרת מולקולת הd.n.a(שמורכבת מרצפים של 4 "בסיסים חנקניים",אדנין -A,גואנין-G,תימין-T,ציטוזין-C)נקרא פלינדרום אם הוא שווה לרצף המשלים שלו בהיפוך. לדוגמה הרצף ACCTAGGT הוא פלינדרום כי המשלים שלו הוא TGGATCCA והוא שווה להיפוך שלו.

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

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

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

בגיליון המצורף בתא B5, יש לכתוב את הטקסט לבדיקה.
התוצאה מתקבלת בתא B8.(true או false)

=IF(LEN(B3)>2,ARRAYFORMULA(SUM(--(MID(B3,SEQUENCE(LEN(B3)),1)=MID(B3,1+LEN(B3)-SEQUENCE(LEN(B3)),1)))=LEN(B3)),"true")

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

ואני אסביר בקצרה על השלבים:
ראשית אומר כי ישנם מספר דרכים עבור חישוב ואימות פלינדרומי.חלקם מוגבלים לס"ך תוים, חלקם בעזרת סקריפט (vba),וחלקם בדרכים שונות.
אני השתמשתי בטכניקה מסוימת שאיננה מוגבלת לס"ך תווים וללא מאקרו.ועתה להסבר על השלבים:
שלב 1: פונקציית mid מהווה כאן כלי חישובי מרכזי לשליפת נתונים ותווים.
משפטים פלינדרומים מטבעם מכילים רווחים ועלולים להכיל תווים שונים כמו סלש / (בטקסט תאריך למשל).
לשם כך אנו צריכים להסיר את כל "המפריעים" הללו.
לגבי רווחים-פונקציית trim איננה הכתובת הפעם…כי פונקצייה זו מבחינה בין רווחים "נורמלים" לכאלו שמיותרים.(בהזדמנות בל"נ אכתוב בהרחבה על כל סוגיא זו לעומקה)
כאן אנו צריכים להסיר באופן טוטאלי את הרווחים.ולכן נעשה פה שימוש בפונקציית substitute.
הפונקצייה כאן היא "מקוננת".ומסירה גם כל תו של סלש /.
הזכרתי בפתיחת דבריי כי בכתיבת פלינדרום נהוג לא לייחס חשיבות בין אות גדולה לקטנה ובין אות רגילה לסופית(בעברית).
שפת הקידוד באקסל מטבעה גם איננה מבחינה בין שינויים שכאלו בכל הקשור "להשוואה לוגית" בין ערכים.ולכן אין צורך בפונקציית עזר לגבי נושא זה.
שלב 2:
פונקציית sequence (היא הוזכרה כאן רבות לאחרונה.זוכרים?) מבצעת "פריסה" למספר תאים כלפי מטה כעין "הרצה" ברצף של הטקסט מתחילתו לסופו (זו ברירת המחדל של הפונקצייה).מי "שנוקבת" את ס"ך התאים שיש לפרוס כלפי מטה היא פונקציית len.
וכל זה נכנס בארגומנט השני של פונקציית mid.כאשר ה1 בארגומנט השלישי אומר לפונקצייה שיש לשלוף בכל פעם תו אחד( בשלב 4 תבינו יותר שהשליפה הזו אחד כנגד אחד היא בעצם לצורך השוואה בין 2 ההחזרים בכל שורה)
שלב 3:
הטכניקה די דומה לשלב 2 אלא שכאן נוסף סימן חיסור.וזה גורם לכל מערך התאים שהוחזרו להיות מהסוף להתחלה "ברוורס"
שלב 4:
הוא למעשה שילוב של שלב 2 ושלב 3.כאשר בינהם מוצב סימון שווה=
מה אנו עושים פה בעצם?
אז תדמיינו 2 מבנים רבי קומות זהים בגודלם ובמספר קומות.שהם ניצבים זה מול זה.
גם כאן ניצבים "כביכול" ההחזרים של שלב 2 ושלב 3 זה מול זה.וההשוואה נעשה קומה ראשונה של שלב 2 מול קומה ראשונה של שלב 3 וכן הלאה.
על כל "קומה" מוחזר לאחר הבדיקה true או false.
שלב 5:
כעת הגיע הזמן לסכום כמה פעמים הוחזר true בכל התוצאות משלב 4.
רגע…יש פיתרון והוא לעטוף את כל הטוב הזה בפונקציית sum.פשוט…לא?
כאן יש להיזכר במה שכתבתי לעיל באריכות רבה בלינק הבא
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13319946
אז גם כאן פונקציית sum "לא יודעת" לבד להמיר את הערכים הלוגים (של true ) לערך מספרי..
ולשם כך נשתמש בטכניקה שהוזכרה בלינק לעיל על ידי הוספת פעולת חיסור כפול.(Double unary) שימו לב זה אכן מופיע כאן…
השלב הסופי:
הוא לערוך השוואה בין הסכום שהתקבל משלב 5 לבין הסכום שהתקבל בספירה רגילה של התווים בתא המקור .השוואה זו מבוצעת על ידי הוספת סימון = עם הפנייה לבדיקה לתוצאות פונקציית len פשוטה.
נקודה חשובה: במקרה בו הטקסט הוא 2 תוים (שאינם זהים) יוחזר false באופן אוטומט.
ולכן נוספה כאן פונקציית if עם תנאי שכל החישוב הנ"ל יהיה בתנאי שמספר התווים בתא המקור יהיה גדול מ2.
ועוד נקודה חשובה: הגיליון המצורף נערך על ידי בגוגל שיטס,בשיטס קיים הכרח לעטוף את החישובים הנ"ל בפונקציות עם פונקציית arryaformula.(מבלי להיכנס כעת לסיבות וכו…)
מה שזה אומר.שאם הקובץ יפתח באקסל תתקבל שגיאת name. ולתיקון -יש למחוק את שם הפונקצייה עם הסוגריים שבה.

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

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

אחתום בפלינדרום ידוע מרבי אברהם אבן עזרא ששאל(כעין משל "בדיאלוג" ספרותי...) את הקב"ה בזה הלשון: "אָבִי, אֵל חַי שִׁמְךָ, לָמָּה מֶלֶךְ מָשִׁיחַ לֹא יָבֹא?" ותשובתו: "דְּעוּ מֵאֲבִיכֶם כִּי לֹא בּוֹשׁ אֱבוֹש, שׁוּב אָשׁוּב אֲלֵיכֶם כִּי בָא מוֹעֵד!"
(השאלה וכן התשובה נקראים מתחילתם לסופם וכן ההיפך)

אשמח לשמוע שאלות/הארות/הערות על כל הנאמר לעיל.
בהצלחה רבה!

 

[ברוך ר]

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

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

 

[יעקב ויצמן]

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

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

 

[ברוך ר]

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

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

 

[יעקב ויצמן]

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

גם זה ניתן בעזרת מאקרו

Public Sub DuplicateSheetMultipleTimes()
  Dim n As Integer
  On Error Resume Next
  n = InputBox("How many copies of the active sheet do you want to make?")

  If n >= 1 Then
    For numtimes = 1 To n
      activeSheet.Copy After:=ActiveWorkbook.Sheets(Worksheets.Count)
    Next
  End If
End Sub

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

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

 

[ברוך ר]

גם זה ניתן בעזרת מאקרו

בגיליון הראשי.תוסיף מאקרו חדש .תעתיק את הקוד הנ"ל ותדביק בתוך עורך הvba את הסקריפט.
בעת "הרצת" הקוד (על ידי f5) או על ידי הוספת לחצן בגיליון (עם הקצאה למאקרו וכו)
תיפתח תיבת קלט כזו (InputBox) ובה תוצג שאלה כמה עותקים אתה רוצה לשכפל.צפה בקובץ המצורף 1590966
יש להזין מספר וללחוץ על ok
נ.ב לא ניסיתי בעצמי את הסקריפט הזה.אבל זה אמור לעבוד.
במטותא מינך.תבצע ניסוי קודם בהעתק של חוברת העבודה המקורית.

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

 

[יעקב ויצמן]

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

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

כאשר תלחץ עליו "תקפוץ" לך הודעה כזו במסך האקסל

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

 

[ברוך ר]

שבוע טוב @ברוך ר היקר
תשובתי לעיל היתה מבוססת מאופי הגיליון שבנוי עם מאקרו רבים.חשבתי שתוכל לבצע זאת לבד.סורי...
אני אסביר בפירוט...
מצורף לך קובץ אקסל שערכתי עם המאקרו הנ"ל.
תוכל להוריד אותו בלינק כאן
לאחר פתיחתו יוצג לך בגיליון חישוב שעות את הלחצן שסימנתי כאן
צפה בקובץ המצורף 1591032
כאשר תלחץ עליו "תקפוץ" לך הודעה כזו במסך האקסל
צפה בקובץ המצורף 1591033
השאלה כאן היא כמה עותקים של הגיליון הנוכחי תרצה לשכפל?
את התשובה אתה כותב היכן שסימנתי באדום.ואז ללחוץ על ok.
תעדכן אותי אם הכל עובד אצלך
בהצלחה

עובד מצוין !
שאלה קטנה
איך אני שולט על העיצוב של הכפתור הזה ?
נגיד אני רוצה מרובע, איפה אני משנה אותו ?