נוסחה בגוגל שיטס
-
@Y-Excel-Access
תודה רבה -
-
@בער כן, עם פונקציית QUERY.
אפשר ליצור שאילתת קיבוץ - נניח שיש לך 3 גליונות בהם שם תורם בעמודה A, וכמות תרומה בעמודה B.
אתה רוצה לקבל כמה פעולות תרומה התבצעו על ידי כל תורם, וכמה סך כל התרומות שלו.
אתה כותב את הפונקציה הבאה:=QUERY({'גיליון1'!A:B;'גיליון2'!A2:B;'גיליון3'!A2:B},"select Col1, count(Col2), sum(Col2) where Col1 <> '' group by Col1 ")
שנה את השמות וההפניות והכפל גליונות לפי הצורך.
שים לב: עדיף שרק ההפניה לגיליון הראשון יהיה בהפניה של A:B והשאר בהפניה של A2:B כדי שהכותרת לא תיתפס כתורם ( כי אז ייכתב לך הטקסט 'שם תורם' כאחד התורמים שהוא תרם 0 פעמים...)להרחבת הבנת הפונקציה תוכל לראות כאן (יש המון בגוגל, הבאתי את הראשון שיצא לי...)
-
@Y-Excel-Access
אבל יש לי כמה עמודות בין העמודה של השמות לעמודה של התרומה -
@בער אז ההפניה תתיחס לעמודות הרלוונטיות שלך.
אם העמודה שלך של השמות היא ב G והעמודה של כמות התרומה היא ב R תחליף בפונקציה כל הפניה ל A ב G וכל B ב R.
בהצלחה!- עריכה: טעות - זה רק בהפניות סמוכות.
כתבתי את התשובה הנכונה להלן
- עריכה: טעות - זה רק בהפניות סמוכות.
-
@Y-Excel-Access
ניסתי לעשות את זה ככה:=QUERY({'07.2021'!B:F;'08.2021'!B2:F2;'09.2021'!B2:F2},"select Col1, count(Col2), sum(Col2) where Col1 <> '' group by Col1 ")והוא עשה לי טור של כל השמות ובטור השני והשלישי הוא התייחס רק למה שמופיע בעמודה C ולא למה שמופיע בעמודה F
-
@בער אמר בנוסחה בגוגל שיטס:
@Y-Excel-Access
ניסתי לעשות את זה ככה:=QUERY({'07.2021'!B:F;'08.2021'!B2:F2;'09.2021'!B2:F2},"select Col1, count(Col2), sum(Col2) where Col1 <> '' group by Col1 ")והוא עשה לי טור של כל השמות ובטור השני והשלישי הוא התייחס רק למה שמופיע בעמודה C ולא למה שמופיע בעמודה F
@בער יש בנוסחה באמת בעיה גם עם שורות וגם עם העמודות
בהפניה השניה והשלישית רק מה שמופיע בשורה השניה ולא בשאר השורות מחושב, - זו טעות שלך,
וגם העמודות לא מחושבות נכון. - זו טעות שלי.הנוסחה הנכונה:
=QUERY({'07.2021'!B:B,'07.2021'!F:F;'08.2021'!B2:B,'08.2021'!F:F2;'09.2021'!B2:B,'09.2021'!F:F2},"select Col1, count(Col2), sum(Col2) where Col1 <> '' group by Col1 ")הסבר -
הסוגריים {} מקבצות את ההפניות לטבלה.
פסיק , מסדר הפניה ליד הפניה
נקודה-פסיק ; מסדר הפניה מתחת להפניהאז הפונקציה לוקחת את עמודה B:B מטבלה 07 ומסדרת לידה את עמודה F:F מטבלה 07 וזה נהיה טבלה ראשונה.
אח"כ לוקחת את התאים B2 עד סוף העמודה (ללא B1) ומסדרת לידה את התאים F2 עד סוף העמודה ואת שתי עמודות אלו מסדרת מתחת הטבלה הראשונה
ואותו דבר עם ההפניה השלישיתאח"כ הנוסחה מבצעת חישוב של SQL לסכום ולספור את הנתונים בתנאי שכתוב משהו בעמודה הראשונה, ולפי הטקסט של העמודה הראשונה (שם התורם)
אם עדיין יש צורך - תוכל לפנות אלי גם במייל (אם זה לא נתונים רגישים - אפשר גם לשתף איתי את הקובץ)
בהצלחה! -
@Y-Excel-Access
קודם כל תודה רבה! ב"ה זה עובד.
דבר שני אני מודה לך על ההסבר, זה חסד עצום כי אתה נותן לי חכה לתפוס דגים ולא רק דג לאכול.....
בכל מקרה אשמח שתסביר את החלק השני של הנוסחה (את החלק הראשון הסברת שהוא בעצם מלקט את כל הטבלאות אחד מתחת השני) מה זה COL וכו' -
@בער אמר:
@Y-Excel-Access
בכל מקרה אשמח שתסביר את החלק השני של הנוסחה (את החלק הראשון הסברת שהוא בעצם מלקט את כל הטבלאות אחד מתחת השני) מה זה COL וכו'זה הפעולה של הפונקציה עצמה, שני הפרמטרים הראשונים של הפונקציה זה 1: המידע 2: שאילתה שלפיה אתה רוצה לגשת לנתונים.
מדובר בפונקציה הכי חזקה לכאורה שקיימת בגוגל שיטס, היא מספקת כלים שמאוד מזכירים את SQL למרות שלא מדובר כלל באותה השפה אלא רק באותה גישה וסמנטיקה.
הפעולות שהציע @Y-Excel-Access בנוסחה שהביא ניתנות לכאורה לביצוע ע"י קינון מספר פונקציות אחת בתוך השניה ואחרי הרבה מחשבה שתשקיע בפתרון הבעיה בצורה טובה, הגישה שמציעה השפה הזו היא שאתה לא צריך להגיד לה איך לעשות אלא מה לעשות,
הנוסחה שהביא מאוד ברורה: תסתכל על העמודה הראשונה והשניה והשלישית, תסנן לפי עמודה ראשונה רק מה שלא ריק, אח"כ תסכום לי את הנתונים בעמודה השניה ותספור את כמות הנתונים בעמודה השלישית, כאשר החלוקה לקבוצות סיכום וספירה הינה בהתאם לנתונים ייחודיים בעמודה ראשונה.זו פונקציה מדהימה שחבל מאוד שלא כולם מכירים אותה, כי היא פותרת המון המון בעיות ומקצרת הרבה תהליכים. וגם הרבה יותר מובנת וברורה כי כאמור ההתמקדות היא לא בלהמציא איך המחשב יעשה את זה אלא להסביר במילים פשוטות מה אתה רוצה והמחשב כבר יסתדר לבד...
ואגב, @Y-Excel-Access, השימוש בCol1 ומספרי עמודות במקום A B C וכו' הוא רק בגלל שאתה משתמש במערך כמובן, בעיקרון ניתן לקרוא להם בשמות העמודות אפילו (first name וכדו').
(SQL היא שפה הרבה הרבה יותר רחבה ומשמעותית ואין קשר ביניהם למרות שלמי שמכיר כבר SQL זה מאוד אינטואיטיבי ופשוט להבין את זה ג"כ) -
@בער תוכל ללמוד עוד על הנוסחה הזו- https://coefficient.io/how-to-google-sheets-query-function/
(כמובן יש המון על זה באינטרנט, אבל הבאתי את הראשון שראיתי שיחסית מקיף) -
@Y-Excel-Access
הסתכלתי בקישור שם אבל לא הצלחתי להבין את הנוסחה שכתבת
col זה עמודה?
ומה הם החלק הבא של הנוסחה:where Col1 <> '' group by Col1 "
-
@בער כן Col זה עמודה - מהטבלה החדשה עם שלוש העמודות שנוצרה בסוגריים המסולסלות {}.
מה זה WHERE, מה זה GRUP BY?
זה משפט משפת SQL - שפת מסד נתונים מאוד שמישה.מה זה עשה? סינן מכל השורות את השורה בה Col1 הוא שונה מ "" (לא ריק), וקיבץ לפי הנתונים ב Col1.
איך זה עושה את זה? SQL זה נושא גדול, אבל כאן - https://ramkedem.com/פקודת-group-by/
מוסבר נקודתית על זה. -
ישנה נוסחה נוספת שיכולה להיות אפקטיבית בשבילך והיא תצריך אותך ליצור גיליון שיש בו את שמות (או יותר טוב מספר מזהה) של כל התורמים ואז בתא ליד להשתמש בפונקציה samifs וליצור תנאי בסכימה שרק התרומות של התורם הזה יסוכמו לך
-
מה זה עשה? סינן מכל השורות את השורה בה Col1 הוא שווה "" (ריק), וקיבץ לפי הנתונים ב Col1.
ֲ
@Y-Excel-Access תתקן במקום שווה >> שונה@בער בתרגום לעברית של הנוסחה:
תבחר את העמודות 1 2 ו3 תסנן מהם היכן שבהתאמה בעמודה 1 אינו ריק ואז תסכום את הנתונים לפי קיבוץ של נתונים בעמודה 1.הפעולה של group by בקצרה מלמעלה:
-
עובר על העמודה שביקשת לקבץ לפיה - במקרה הזה עמודה 1, ולוקח ממנה ערכים ייחודיים בלבד (מה שאפשרי לעשות עם הפונקציה unique)
-
לאחר שיש לו רשימה של ערכים ייחודיים מתוך העמודה הזו הוא הולך לשאר העמודות ומחפש עבור כל ערך ומסכם(או /כל פעולת צבירה אחרת) את כל השורות הרלוונטיות לאותו הערך.
-
-
-
