כרטסת בגוגל שיטס

בער

@Y-Excel-Access
אני מדבר מצירוף של 2 או יותר טבלאות בגליונות נפרדים

כמו בדוגמא יש לי 2 טבלאות, לערבב אותם.......

Y.Excel.Access

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

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

לשים את העמודות אחת ליד השניה, ובאותה שורה תמיד יהיה את אותו מוצר? (כעין 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 כותרות וכו' אבל זה הכיוון?

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

בער

@Y-Excel-Access כן התכוונתי לאפשרות הרביעית, כמו בבנק

בער

@Y-Excel-Access

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

Y.Excel.Access

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

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

השגיאה היא לכאורה כי פונקציית 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 כדי שימויינו לפי תאריך מהגדול לקטן.

בער

@Y-Excel-Access
העברתי את הסוגריים לסוף והכל הסתדר ב"ה.

בער

@Y-Excel-Access
יש אפשרות להוסיף על זה גם פונקציית פילטר FILTER? שיתן לי את הנתונים אך מסוננים לפי כללים מסוימים
כרגע אני עושה את כשני טבלאות קודם הסינון ועל זה אני עושה את SORT

Y.Excel.Access

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

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

בער

@Y-Excel-Access
בדוגמא הנ"ל, לפי תאריך או לפי שם מוצר

Y.Excel.Access

@בער הנה אחת מהאפשרויות - לסנן את עמודה A יותר מ 5000.

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

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

במקרה כזה אפשר לסבך עם עוד קינון של תנאי או להשתמש עם QUERY. אני כולל כאן סינון עם שני תנאים - או לפי עמודה A או לפי עמודה C.

עם תנאי:

=ARRAYFORMULA(SORT(FILTER(
{הכנסות!A1:D100;הוצאות!A1:B80, הוצאות!D1:D80, הוצאות!C1:C80*-1}
,BITOR({הכנסות!A1:A100;הוצאות!A1:A80}>5000,{הכנסות!C1:C100;הוצאות!C1:C80}="מוצר")
),1,FALSE)

עם QUERY:

=QUERY({הכנסות!A1:D100 ; הוצאות!A1:B80, הוצאות!D1:D80, הוצאות!C1:C80*-1}, "SELECT * WHERE Col1 > 5000 OR Col3 = "מוצר" order by Col1 desc",0)

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

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

כרטסת בגוגל שיטס