פונקציית QUERY מאפשרת לך למשוך מידע מטווח או מגיליון שלם של נתונים באמצעות פקודות שאילתה גמישות. למד כיצד להשתמש בפונקציית ה-QUERY של Google Sheets נותן לך גישה לכלי חיפוש רב עוצמה.
אם אי פעם כתבת שאילתות SQL כדי להוציא נתונים ממסד נתונים, אז תזהה את הפונקציה QUERY. אם אין לך ניסיון במסד נתונים, עדיין קל מאוד ללמוד את הפונקציה QUERY.
מהי פונקציית ה-QUERY?
לפונקציה יש שלושה פרמטרים עיקריים:
=QUERY(נתונים, שאילתה, כותרות)
הפרמטרים האלה פשוטים למדי.
- Data: טווח התאים המכילים את נתוני המקור
- Query: הצהרת חיפוש המתארת כיצד לחלץ את מה שאתה רוצה מנתוני המקור
- Headers: ארגומנט אופציונלי המאפשר לשלב מספר כותרות בטווח המקור לכותרת אחת בגיליון היעד
הגמישות והעוצמה של הפונקציה QUERY מגיעות מארגומנט ה-Query, כפי שתראה להלן.
איך ליצור נוסחת שאילתה פשוטה
נוסחת ה-QUERY שימושית במיוחד כאשר יש לך מערך נתונים גדול מאוד שממנו אתה צריך לחלץ ולסנן נתונים.
הדוגמאות הבאות משתמשות בסטטיסטיקה של ביצועי תיכון SAT בארה"ב. בדוגמה הראשונה הזו, תלמד כיצד לכתוב נוסחת QUERY פשוטה שמחזירה את כל בתי הספר התיכוניים והנתונים שלהם כאשר "ניו יורק" הוא בשם בית הספר.
-
צור גיליון חדש להצבת תוצאות השאילתה. בתא השמאלי העליון הקלד =שאילתה(. כשתעשה זאת, תראה חלון מוקפץ עם ארגומנטים דורשים, דוגמה ומידע מועיל על הפונקציה.
Image -
הבא, בהנחה שיש לך את נתוני המקור בגיליון1, מלא את הפונקציה באופן הבא:
=Query(Sheet1!A1:F460, "SELECT B, C, D, E, F WHERE B LIKE '%New York%'")
נוסחה זו כוללת את הארגומנטים הבאים:
- טווח תאים: טווח הנתונים ב-A1 עד F460 ב-Sheet1
- SELECT Statement: משפט SELECT הקורא לנתונים כלשהם בעמודות B, C, D, E ו-F כאשר עמודה B מכילה טקסט עם המילה "New York " בו.
Image תו "%" הוא תו כללי שבו אתה יכול להשתמש כדי לחפש חלקים של מחרוזות או מספרים בכל מערך נתונים. השארת "%" בקדמת המחרוזת תחזיר כל שם בית ספר שמתחיל בטקסט "ניו יורק".
-
אם תרצה למצוא את השם של בית ספר מדויק מהרשימה, תוכל להקליד את השאילתה:
=Query(Sheet1!A1:F460, "SELECT B, C, D, E, F WHERE B='New York Harbor High School'")
שימוש באופרטור =מוצא התאמה מדויקת וניתן להשתמש בו כדי למצוא טקסט או מספרים תואמים בכל עמודה.
Image
מכיוון שפונקציית ה-QUERY של Google Sheets היא קלה מאוד להבנה ולשימוש, אתה יכול לשלוף כל נתונים מכל מערך נתונים גדול באמצעות הצהרות שאילתות פשוטות כמו אלה שלמעלה.
השתמש בפונקציית QUERY עם אופרטור השוואה
אופרטורי השוואה מאפשרים לך להשתמש בפונקציית QUERY כדי לסנן נתונים שאינם עומדים בתנאי.
יש לך גישה לכל האופרטורים הבאים בפונקציית QUERY:
- =: הערכים תואמים לערך החיפוש
- <: הערכים נמוכים מערך החיפוש
- >: הערכים גדולים מערך החיפוש
- <=: הערכים נמוכים או שווים לערך החיפוש
- >=: הערכים גדולים או שווים לערך החיפוש
- ו-!=: ערכי החיפוש וערכי המקור אינם שווים
באמצעות אותם נתוני SAT לדוגמה שלמעלה, בואו נסתכל כיצד לראות לאילו בתי ספר היה ממוצע מתמטי של מעל 500 נקודות.
-
בתא השמאלי העליון של גיליון ריק, מלא את הפונקציה QUERY באופן הבא:
=Query(Sheet1!A1:F460, "SELECT B, C, D, E, F WHERE E > 500")
נוסחה זו דורשת כל נתונים שבהם עמודה E מכילה ערך הגדול מ-500.
Image -
אתה יכול גם לכלול אופרטורים לוגיים כמו AND ו-OR כדי לחפש תנאים מרובים. לדוגמה, כדי לקבל ציונים רק עבור בתי ספר עם יותר מ-600 נבחנים וממוצע קריאה קריטי בין 400 ל-600, תקליד את הפונקציה QUERY הבאה:
=Query(Sheet1!A1:F460, "SELECT B, C, D, E, F WHERE C > 600 AND D > 400 AND D < 600")
Image - השוואה ואופרטורים לוגיים מספקים לך דרכים רבות ושונות למשוך נתונים מגיליון אלקטרוני של מקור. הם מאפשרים לך לסנן פיסות מידע חשובות אפילו ממערכי נתונים גדולים מאוד.
שימושים מתקדמים בפונקציית QUERY
ישנן כמה תכונות נוספות שתוכל להוסיף לפונקציית QUERY עם כמה פקודות נוספות. פקודות אלו מאפשרות לך לצבור ערכים, לספור ערכים, להזמין נתוני ולמצוא ערכים מקסימליים.
-
שימוש ב-GROUP בפונקציה QUERY מאפשר לך לצבור ערכים במספר שורות. לדוגמה, ניתן לבצע ממוצע ציוני מבחן עבור כל תלמיד באמצעות הפונקציה GROUP. כדי לעשות זאת, הקלד:
=Query(Sheet1!A1:B24, "SELECT A, AVG(B) GROUP BY A")
Image -
באמצעות COUNT בפונקציית QUERY, תוכל לספור את מספר בתי הספר עם ציון כתיבה ממוצע מעל 500 באמצעות הפונקציה QUERY הבאה:
=QUERY(Sheet1!A2:F460, "SELECT B, COUNT (F) GROUP BY B")
Image -
באמצעות ORDER BY בפונקציית QUERY, אתה יכול למצוא בתי ספר עם ציונים ממוצעים במתמטיקה מקסימליים ולסדר את הרשימה לפי הציונים האלה.
=QUERY(Sheet1!A2:F460, "SELECT B, MAX (E) GROUP BY B ORDER BY MAX(E)")
Image
