תחומים - פורום חרדי מקצועי

@בער כמובן, אפשר לסנן את הטבלאות עם FILTER.
אפשר לסנן כל אחת בפנ"ע,
או את התוצאה של האיחוד,
או את התוצאה של הסינון.

לפי איזה נתונים אתה רוצה לסנן?

@בער כתב בכרטסת בגוגל שיטס:

למה מביא לי שגיאה
אולי אם אני יבין ממה הנוסחה בנויה אני יוכל לתרגם את זה לענין שלי

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

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

=ARRAYFORMULA({"כותרת1", "כותרת2", "כותרת3"; "שורה1 עמודה1", "שורה1 עמודה2", "שורה1 עמודה 3"; "שורה 2 עמודה1", "שורה2 עמודה2" ,"שורה 2 עמודה3"; "שורה3 עמודה1", "שורה3 עמודה2", "שורה3 עמודה 3"})

ובהפניות:

=ARRAYFORMULA({A1:A10,C1:C10})

פונקציית SORT ממיינת נתונים.
כך:

=SORT(פרמטר 1 ההפניה המלאה, פרמטר 2 מספר העמודה שעל פיה יש למיין, פרמטר 3 האם למיין בסדר עולה או בסדר יורד)

=SORT(A:C,1,TRUE)

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

@בער לא ממש הבנתי מה התוצאה הרצויה.

יש לי כמה הבנות בדבריך, נא לפרט יותר.

• לשים את העמודות אחת ליד השניה, ובאותה שורה תמיד יהיה את אותו מוצר? (כעין SELECT עם INNER JOIN ב SQL)

• לסכום את העמודות אחת ועוד השניה כך שתהיה טבלה אחת בה קולה = 6 וכו'? או לחילופין אחת פחות השניה כך קולה = 4 וכו' (כעין SUM GROUP BY ב SQL)

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

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

• לשרשר את הטבלאות - להציג את הראשונה ומיד לאחריה את השניה? - אפשר כמובן למיין את התוצאה לפי תאריך (כעין UNION ALL ב SQL)

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

אביא דוגמה:

יש 2 גליונות הוצאות, הכנסות
טווח הנתונים בגליון הוצאות הם עמודות A - C משורה 1 עד 100. עמודה D קיימת וריקה מנתונים.
עמודה A משמשת לתאריך, B לשם מוצר, C למחיר / כמות או כל דבר אחר חשבוני שצריך להציג.

טווח הנתונים בגליון הכנסות הם עמודות A - C משורה 1 עד 80. עמודה D קיימת וריקה מנתונים.
עמודה A משמשת לתאריך, B לשם מוצר, C למחיר / כמות או כל דבר אחר חשבוני שצריך להציג.

=ARRAYFORMULA(SORT({הכנסות!A1:D100 ; הוצאות!A1:B80, הוצאות!D1:D80, הוצאות!C1:C80*-1}),1,FALSE)

התוצאה - אמור להיות שיביא את כל הנתונים - הכנסות והוצאות בטבלה אחת בת 4 עמודות כשהם ממוינים לפי תאריך מהתאריך המאוחר למוקדם, המחיר מחולק ל2 עמודות - עמודה C להכנסות ועמודה D להוצאות (שיופיעו עם מינוס).
-כמובן אפשר לעשות עוד הרבה עם זה, SUM כותרות וכו' אבל זה הכיוון?

*לא בדקתי את זה על נתונים, פשוט כתבתי כך בע"פ.

@דוד-החדש אני מעלה קובץ אקסס כדוגמה.
דוגמה המרת תאריך לועזי לעברי.accdb

תוכל לקרוא בהערות הדי מפורטות בתוך הקוד VBA מה כל פונקציה עושה. אבל הפונקציות השמישות באמת הם GregDateToHeb, וHebDateToGreg.

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

@יצירה כתב בבירור על פיתוח תוכנה עבור מוסד ת"ת:

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

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

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

@בער אתה אולי מתכוון לטבלת Pivot?
יש גם בשיטס (חוץ מאקסל בו בוודאי אמור להיות הכל) אפשרות לטבלת הצלבה.
-לבחור בכרטיסיית 'הוספה' >> 'טבלת צירים'

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

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

@שואף-לדעת יעויין כאן

@שואף-לדעת כתב בנוסחה שלא רואים אותה:

אבל שני התאים יראו ריקים לפני הכתיבה

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

לזה הפתרון הכי פשוט - לכתוב את הנוסחה כך:

=IF(A1="","",GregToHeb(A1))

ושמא זו הייתה כוונת

@dovid כתב בנוסחה שלא רואים אותה:

תא שיש בו נוסחה איננו נראה ריק?

(דהרי תא שיש בו נוסחה עם פרמטר נדרש, והפרמטר איננו תואם = תאריך לועזי, הרי מתרחשת לה שגיאה)

• נ.ב. כרגיל @OdedDvir הקוד שלך מלמד הרבה.
  ובכל אופן לא משנה מה הייתה כוונת השואל מ"מ הרוויח מי שיראה את הפוסט.

@Y-Excel-Access כתב בGOOGL SHEETS עזרה ביצירת פונקציה:

אפשרות ב (נראה לי אופטימלית יותר)

=ArrayFormula(LAMBDA(MYARRAY,N_ROWS
,FILTER(MYARRAY,NOT(ISNA(MATCH(MAKEARRAY(ROWS(MYARRAY),1,LAMBDA(R,C,R)),N_ROWS,0))))
)(E41:F44,{1,2,4}))

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

כך שלאפשרות זו צריך תיקון / לבחור אפשרות אחרת.

@ארי כתב בשאלה באקסל:

איך מגדירים כטבלה שיציג את כל השורות יחיד ולא שם פרטי מתחת לשם משפחה?

אני כותב בלי צילומי מסך וכו' - מי שרוצה להוסיף אפשר כמובן הסבר מפורט יותר.

בקובץ שלך, בהגדרות PIVOT, לחץ על השדה הממוקם בתווית שורה, השדה שאתה לא רוצה שהבא אחריו יופיע כ'עץ' בחלוקה לרמות, אלא כטבלה.

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

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

כלומר היא מקבלת 2 פרמטרים: מערך נתונים, מערך מספרים
ומחזירה שורות מתוך המערך נתונים אם הם כתובות במערך המספרים.

דוגמה (שעובדת אצלי כעת לאחר היצירה):

=SELECT_ROWS({"A",2;3,"B";"C",6;7,"D"} , {2,3})

והתוצאה היא

3 | B
C | 6

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

אפשרות א

=LAMBDA(MYARRAY,N_ROWS
,TRANSPOSE( QUERY({TRANSPOSE(MYARRAY)},"SELECT Col"& TEXTJOIN(", Col",TRUE,N_ROWS),0))
)(E41:F44,{1,2,4})

אפשרות ב (נראה לי אופטימלית יותר)

=ArrayFormula(LAMBDA(MYARRAY,N_ROWS
,FILTER(MYARRAY,NOT(ISNA(MATCH(MAKEARRAY(ROWS(MYARRAY),1,LAMBDA(R,C,R)),N_ROWS,0))))
)(E41:F44,{1,2,4}))

אפשרות ג

=ArrayFormula(LAMBDA(MYARRAY,N_ROWS
,SPLIT(BYROW(TRANSPOSE(N_ROWS),LAMBDA(MYVALUE,TEXTJOIN(CHAR(999),FALSE,INDEX(MYARRAY,MYVALUE)))),CHAR(999),TRUE,FALSE)
)(E41:F44,{1,2,4}))

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

@שוהם307 כתב בשאלה באקסל:

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

לא הבנתי אז מה הצורך בדיוק. אולי POWER QUERY יעזור?
אבל הנראות של הPIVOT ייחודית לו (עם החלוקה לרמות וכו').

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

לדוגמה:
בטבלה יש 4 שדות- שם, פעולה, תאריך, מצב

השורות בטבלה:

יהודה, ריצה, כ' טבת, בריא
זבולון, מנוחה, כ' טבת, בריא
יהודה, ישיבה, ל' טבת, חולה
יששכר, ריצה, ל' טבת, בריא
זבולון, ישיבה , כ' טבת, בריא
יהודה, ריצה, כו טבת, בריא
זבולון, מנוחה, כה טבת, בריא

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

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

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

תודה רבה!

@yossiz כתב בהצעה לחוק חדש: הבאת פלט של ChatGPT מותרת רק כחלק מתשובה ולא במקום תשובה...:

זה לא מוסיף שום ערך לשואל, כמו שהמשיב שאל את ה-AI גם השואל יכול לשאול,

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

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

אבל טוב שהעלית שוב את המודעות לזה שהוא מדבר שטויות

מאגר "בני הישיבות", מאגר שידוכים חינמי - מתפקד כגמ"ח.

אחר שאלת רב, ההוראה היתה לנהל בצורה בטוחה ומחמירה (למרות הזהירות הכללית הקיימת כבר בשימוש ברשת):

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

ועוד פרטים בתקנון המאגר.

קישור לדף נחיתה המכיל הסבר על המאגר

קישור לפתיחה על ידי טלפון כשר בלבד

@OdedDvir תודה רבה! עובד מצוין.
כמו שהערת היה לי טעות בקוד. הקוד המעודכן -

function RunJS(){

  var MyCode = "var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange(\"A500\").setValue(505);"
  eval(MyCode);

}

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

Exception: You do not have permission to call setValue (שורה 1).

כלומר אין הרשאה לפונקציית UDF לקרוא לsetValue. ולא עזר העיקוף דרך eval.

@OdedDvir הקוד

function RunJS(){
  var TEXT = "var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange(a500).setvalue(\"Runing!\");";
  eval(TEXT);
}

מחזיר שגיאת

ReferenceError: a500 is not defined

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

האם שייך meta programming ב APP SCRIPT ?
(קוד JS שמריץ string כקוד JS)

האם אפשר ליצור פונקציה בשיטס (UDF או רק ב APP SCRIPT שלו) בשם "RUNJS(MyCode : string)"?

@yossiz כתב ביצירת טבלה הניתנת לעריכה מלאה ממש כמו אקסל/שיטס ב HTML:

(זו אותה ספרייה שהבאתי למעלה, אני רק מראה שהוא קוד פתוח והוא ב-JS)

תודה, וסליחה שלא עיינתי מספיק...