דילוג לתוכן
  • דף הבית
  • קטגוריות
  • פוסטים אחרונים
  • משתמשים
  • חיפוש
  • חוקי הפורום
כיווץ
תחומים

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

💡 רוצה לזכור קריאת שמע בזמן? לחץ כאן!
  1. דף הבית
  2. תוכנה
  3. נוסחה בגוגל שיטס

נוסחה בגוגל שיטס

מתוזמן נעוץ נעול הועבר תוכנה
19 פוסטים 4 כותבים 1.1k צפיות
  • מהישן לחדש
  • מהחדש לישן
  • הכי הרבה הצבעות
התחברו כדי לפרסם תגובה
נושא זה נמחק. רק משתמשים עם הרשאות מתאימות יוכלו לצפות בו.
  • Y.Excel.AccessY מנותק
    Y.Excel.AccessY מנותק
    Y.Excel.Access
    השיב לבער ב נערך לאחרונה על ידי
    #2

    @בער אתה מתכוון שיש כמה תאים ובחלק מהם כל התא הוא המילה "משה"?
    כי אז התשובה היא:
    =COUNTIF(A:A,"=משה")

    אם התכוונת באותו תא, התשובה דומה:
    =COUNTIF(SPLIT(A1," "),"=משה")

    אם התכוונת לכל האיזכורים המופיעים בטווח תאים, התשובה יותר מורכבת:
    =COUNTIF(SPLIT(TEXTJOIN(" ",FALSE,A:A)," "),"=משה")

    Y.Excel.Access @ gmail.com

    ב תגובה 1 תגובה אחרונה
    5
    • ב מנותק
      ב מנותק
      בער
      השיב לY.Excel.Access ב נערך לאחרונה על ידי
      #3

      @Y-Excel-Access
      תודה רבה

      תגובה 1 תגובה אחרונה
      0
      • ב מנותק
        ב מנותק
        בער
        כתב ב נערך לאחרונה על ידי
        #4

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

        Y.Excel.AccessY תגובה 1 תגובה אחרונה
        0
        • Y.Excel.AccessY מנותק
          Y.Excel.AccessY מנותק
          Y.Excel.Access
          השיב לבער ב נערך לאחרונה על ידי Y.Excel.Access
          #5

          @בער כן, עם פונקציית 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 @ gmail.com

          ב תגובה 1 תגובה אחרונה
          1
          • ב מנותק
            ב מנותק
            בער
            השיב לY.Excel.Access ב נערך לאחרונה על ידי
            #6

            @Y-Excel-Access
            אבל יש לי כמה עמודות בין העמודה של השמות לעמודה של התרומה

            Y.Excel.AccessY תגובה 1 תגובה אחרונה
            0
            • Y.Excel.AccessY מנותק
              Y.Excel.AccessY מנותק
              Y.Excel.Access
              השיב לבער ב נערך לאחרונה על ידי Y.Excel.Access
              #7

              @בער אז ההפניה תתיחס לעמודות הרלוונטיות שלך.
              אם העמודה שלך של השמות היא ב G והעמודה של כמות התרומה היא ב R תחליף בפונקציה כל הפניה ל A ב G וכל B ב R.
              בהצלחה!

              • עריכה: טעות - זה רק בהפניות סמוכות.
                כתבתי את התשובה הנכונה להלן

              Y.Excel.Access @ gmail.com

              ב תגובה 1 תגובה אחרונה
              1
              • ב מנותק
                ב מנותק
                בער
                השיב לY.Excel.Access ב נערך לאחרונה על ידי
                #8

                @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.AccessY תגובה 1 תגובה אחרונה
                0
                • Y.Excel.AccessY מנותק
                  Y.Excel.AccessY מנותק
                  Y.Excel.Access
                  השיב לבער ב נערך לאחרונה על ידי Y.Excel.Access
                  #9

                  @בער אמר בנוסחה בגוגל שיטס:

                  @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 @ gmail.com

                  ב 2 תגובות תגובה אחרונה
                  3
                  • ב מנותק
                    ב מנותק
                    בער
                    השיב לY.Excel.Access ב נערך לאחרונה על ידי
                    #10

                    @Y-Excel-Access
                    קודם כל תודה רבה! ב"ה זה עובד.
                    דבר שני אני מודה לך על ההסבר, זה חסד עצום כי אתה נותן לי חכה לתפוס דגים ולא רק דג לאכול.....
                    בכל מקרה אשמח שתסביר את החלק השני של הנוסחה (את החלק הראשון הסברת שהוא בעצם מלקט את כל הטבלאות אחד מתחת השני) מה זה COL וכו'

                    nachN תגובה 1 תגובה אחרונה
                    1
                    • nachN מנותק
                      nachN מנותק
                      nach
                      השיב לבער ב נערך לאחרונה על ידי nach
                      #11

                      @בער אמר:

                      @Y-Excel-Access
                      בכל מקרה אשמח שתסביר את החלק השני של הנוסחה (את החלק הראשון הסברת שהוא בעצם מלקט את כל הטבלאות אחד מתחת השני) מה זה COL וכו'

                      זה הפעולה של הפונקציה עצמה, שני הפרמטרים הראשונים של הפונקציה זה 1: המידע 2: שאילתה שלפיה אתה רוצה לגשת לנתונים.
                      מדובר בפונקציה הכי חזקה לכאורה שקיימת בגוגל שיטס, היא מספקת כלים שמאוד מזכירים את SQL למרות שלא מדובר כלל באותה השפה אלא רק באותה גישה וסמנטיקה.
                      הפעולות שהציע @Y-Excel-Access בנוסחה שהביא ניתנות לכאורה לביצוע ע"י קינון מספר פונקציות אחת בתוך השניה ואחרי הרבה מחשבה שתשקיע בפתרון הבעיה בצורה טובה, הגישה שמציעה השפה הזו היא שאתה לא צריך להגיד לה איך לעשות אלא מה לעשות,
                      הנוסחה שהביא מאוד ברורה: תסתכל על העמודה הראשונה והשניה והשלישית, תסנן לפי עמודה ראשונה רק מה שלא ריק, אח"כ תסכום לי את הנתונים בעמודה השניה ותספור את כמות הנתונים בעמודה השלישית, כאשר החלוקה לקבוצות סיכום וספירה הינה בהתאם לנתונים ייחודיים בעמודה ראשונה.

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

                      ואגב, @Y-Excel-Access, השימוש בCol1 ומספרי עמודות במקום A B C וכו' הוא רק בגלל שאתה משתמש במערך כמובן, בעיקרון ניתן לקרוא להם בשמות העמודות אפילו (first name וכדו').
                      (SQL היא שפה הרבה הרבה יותר רחבה ומשמעותית ואין קשר ביניהם למרות שלמי שמכיר כבר SQL זה מאוד אינטואיטיבי ופשוט להבין את זה ג"כ)

                      תגובה 1 תגובה אחרונה
                      2
                      • Y.Excel.AccessY מנותק
                        Y.Excel.AccessY מנותק
                        Y.Excel.Access
                        כתב ב נערך לאחרונה על ידי
                        #12

                        @בער תוכל ללמוד עוד על הנוסחה הזו- https://coefficient.io/how-to-google-sheets-query-function/
                        (כמובן יש המון על זה באינטרנט, אבל הבאתי את הראשון שראיתי שיחסית מקיף)

                        Y.Excel.Access @ gmail.com

                        ב תגובה 1 תגובה אחרונה
                        2
                        • ב מנותק
                          ב מנותק
                          בער
                          השיב לY.Excel.Access ב נערך לאחרונה על ידי בער
                          #13

                          @Y-Excel-Access
                          הסתכלתי בקישור שם אבל לא הצלחתי להבין את הנוסחה שכתבת
                          col זה עמודה?
                          ומה הם החלק הבא של הנוסחה:

                          where Col1 <> '' group by Col1 "

                          Y.Excel.AccessY תגובה 1 תגובה אחרונה
                          0
                          • Y.Excel.AccessY מנותק
                            Y.Excel.AccessY מנותק
                            Y.Excel.Access
                            השיב לבער ב נערך לאחרונה על ידי Y.Excel.Access
                            #14

                            @בער כן Col זה עמודה - מהטבלה החדשה עם שלוש העמודות שנוצרה בסוגריים המסולסלות {}.
                            מה זה WHERE, מה זה GRUP BY?
                            זה משפט משפת SQL - שפת מסד נתונים מאוד שמישה.

                            מה זה עשה? סינן מכל השורות את השורה בה Col1 הוא שונה מ "" (לא ריק), וקיבץ לפי הנתונים ב Col1.

                            איך זה עושה את זה? SQL זה נושא גדול, אבל כאן - https://ramkedem.com/פקודת-group-by/
                            מוסבר נקודתית על זה.

                            Y.Excel.Access @ gmail.com

                            nachN תגובה 1 תגובה אחרונה
                            1
                            • ש מנותק
                              ש מנותק
                              שמחה זו הסיסמא
                              כתב ב נערך לאחרונה על ידי
                              #15

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

                              תגובה 1 תגובה אחרונה
                              2
                              • nachN מנותק
                                nachN מנותק
                                nach
                                השיב לY.Excel.Access ב נערך לאחרונה על ידי nach
                                #16

                                מה זה עשה? סינן מכל השורות את השורה בה Col1 הוא שווה "" (ריק), וקיבץ לפי הנתונים ב Col1.

                                ֲ
                                @Y-Excel-Access תתקן במקום שווה >> שונה

                                @בער בתרגום לעברית של הנוסחה:
                                תבחר את העמודות 1 2 ו3 תסנן מהם היכן שבהתאמה בעמודה 1 אינו ריק ואז תסכום את הנתונים לפי קיבוץ של נתונים בעמודה 1.

                                הפעולה של group by בקצרה מלמעלה:

                                • עובר על העמודה שביקשת לקבץ לפיה - במקרה הזה עמודה 1, ולוקח ממנה ערכים ייחודיים בלבד (מה שאפשרי לעשות עם הפונקציה unique)

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

                                Y.Excel.AccessY תגובה 1 תגובה אחרונה
                                2
                                • Y.Excel.AccessY מנותק
                                  Y.Excel.AccessY מנותק
                                  Y.Excel.Access
                                  השיב לnach ב נערך לאחרונה על ידי
                                  #17

                                  @nach אמר בנוסחה בגוגל שיטס:

                                  @Y-Excel-Access תתקן במקום שווה >> שונה

                                  תיקנתי, תודה.

                                  Y.Excel.Access @ gmail.com

                                  תגובה 1 תגובה אחרונה
                                  1
                                  • ב מנותק
                                    ב מנותק
                                    בער
                                    השיב לY.Excel.Access ב נערך לאחרונה על ידי
                                    #18

                                    @Y-Excel-Access אמר בנוסחה בגוגל שיטס:

                                    אם עדיין יש צורך - תוכל לפנות אלי גם במייל

                                    מה הכתובת

                                    Y.Excel.AccessY תגובה 1 תגובה אחרונה
                                    0
                                    • Y.Excel.AccessY מנותק
                                      Y.Excel.AccessY מנותק
                                      Y.Excel.Access
                                      השיב לבער ב נערך לאחרונה על ידי
                                      #19

                                      @בער כתוב לי למטה - מתחת כל פוסט שאני כותב יש הערה עם הכתובת מייל

                                      Y.Excel.Access @ gmail.com

                                      תגובה 1 תגובה אחרונה
                                      1

                                      בא תתחבר לדף היומי!
                                      • התחברות

                                      • אין לך חשבון עדיין? הרשמה

                                      • התחברו או הירשמו כדי לחפש.
                                      • פוסט ראשון
                                        פוסט אחרון
                                      0
                                      • דף הבית
                                      • קטגוריות
                                      • פוסטים אחרונים
                                      • משתמשים
                                      • חיפוש
                                      • חוקי הפורום