יצירת שאילתת פרמטר

בעת שאילתת נתונים ב-Excel, ייתכן שתרצה להשתמש בערך קלט-פרמטר-כדי לציין משהו לגבי השאילתה. לשם כך, עליך ליצור שאילתת פרמטר. האופן שבו אתה יוצר שאילתות פרמטר וכיצד הן מתנהגות תלויות בשאלה אם אתה משתמש ב-Microsoft Query או ב-Power Query.

עצה: פרמטרים של Power Query שונים מאוד מהפרמטרים הנמצאים בשימוש בשאילתות מבוססות SQL. בנוסף, באפשרותך להשתמש בשאילתה במקום בפרמטר בפועל אם כל מה שאתה צריך הוא לסנן נתונים. שקול לקרוא את מקטעי הדוגמה של Power Query לפני יצירת פרמטרים ב-Power Query.

Microsoft Query

Power Query

כיצד פרמטרים משפיעים על שאילתות

הפרמטרים משמשים בפסוקית WHERE של השאילתה – הם תמיד מתפקדים כמסנן לנתונים שאוחזרו.

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

אפשרויות קלט פרמטר

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

פרמטרים אינם מובילים בקשה לקלט. במקום זאת, באפשרותך לשנות את הערך שלהם באמצעות עורך השאילתות של Power Query. לחלופין, במקום פרמטר בתום-תוקף, באפשרותך להשתמש בשאילתה המפנה למיקום חיצוני עם ערך שניתן לערוך בקלות.

טווח פרמטרים

פרמטר הוא חלק מהשאילתה שהיא משנה, ולא ניתן להשתמש בו שוב בשאילתות אחרות.

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

  1. לחץ על data _GT_ קבל _AMP_ המרת נתונים _GT_ קבל את הנתונים > ממקורות אחרים > מ-Microsoft Query.

  2. בצע את שלבי אשף השאילתות. במסך של אשף השאילתות-סיום , בחר הצג נתונים או ערוך שאילתה ב-Microsoft Query ולאחר מכן לחץ על סיום. חלון השאילתה של Microsoft נפתח ומציג את השאילתה.

  3. לחץ על הצג את > SQL. בתיבת הדו SQL שמופיעה, אתר את פסוקית WHERE – שורה המתחילת במילה שם, בדרך כלל בסוף קוד ה-SQL. אם אין משפט WHERE, הוסף אחד על-ידי הקלדת המקום בשורה חדשה בסוף השאילתה.

  4. לאחר המיקום, הקלד את שם השדה, אופרטור השוואה (=, <, >, LIKE וכן הלאה) ואחת מהאפשרויות הבאות:

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

      תצוגת SQL של שאילתת MS המדגישה את פסוקית WHERE

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

      תצוגת SQL של שאילתת MS המדגישה את פסוקית WHERE

  5. לאחר שתסיים להוסיף תנאים עם פרמטרים לפסוקית WHERE, לחץ על אישור כדי להפעיל את השאילתה. Excel מבקש ממך לספק ערך עבור כל פרמטר, ולאחר מכן Microsoft Query מציג את התוצאות.

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

    תיבת הדו ' ייבוא נתונים ' ב-Excel

  7. כדי לסקור את הפרמטרים, לחץ על מאפיינים. לאחר מכן, בתיבת הדו מאפייני חיבור, בכרטיסיה הגדרה , לחץ על פרמטרים.

    תיבת הדו ' מאפייני חיבור '

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

    תיבת הדו ' פרמטר של שאילתת MS '

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

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

הערה: נושא זה מבוסס על ההנחה שאתה יודע כיצד ליצור חיבור למסד נתונים של Access באמצעות Power Query. לקבלת מידע נוסף, ראה התחברות למסד נתונים של Access.

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

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

בחוברת עבודה ריקה, אנו יוצרים חיבור למסד הנתונים של Access הכולל את רשומות תעבורת האינטרנט שאנו רוצים – כולל שדות המציינים מתי כל עמוד פורסם במקור. מוטען ב-Power Query, הוא נראה כך:

עורך השאילתה של Power Query המתאר נתונים שנטענו

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

עורך השאילתה של Power Query מציג תוצאות

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

תיבת הדו ' פרמטרים של Power Query '

לחץ על חדש, והטופס מציג פרמטר חדש בשם Parameter1 ללא מידע אחר.

נשנה מאפיינים מסוימים של פרמטרים:

  • שינוי שם ל- FirstPubD

  • שנה את התיאור לתאריך שבו פורסם העמוד לראשונה.Description

  • שינוי סוגלתאריך כך שהפרמטר מקבל רק ערכי תאריך

  • הגדר את הערך הנוכחי כך שהפרמטר לא יסנן את כל השורות כאשר לא סיפקנו קלט-אנו משתמשים ב-1/1/2010.

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

לחץ על אישור כדי ליצור את הפרמטר ולראות אותו בעורך השאילתות של Power Query.

עורך השאילתות של Power Query המציג פרמטר

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

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

עורך השאילתה של Power Query המציג תפריט מסנן תאריכים

בתיבת הדו סינון שורות, נבחר פרמטר מרשימת האפשרויות במסנן.

תיבת הדו ' סינון שורות '

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

תיבת הדו ' סינון שורות ' מציגה פרמטר שנבחר

נבחר אותה ולחץ על אישור. עורך השאילתות של Power יטען את השאילתה באמצעות הפרמטר החדש כמסנן.

עורך השאילתות של Power Query המציג תוצאות מסוננות

כדי לבדוק את הפרמטר, אנו משנים את הערך שלו ל- 1/1/2018.

עורך השאילתות של Power Query המציג פרמטר

אנו מרעננים את השאילתה, שמציגה כעת רק שורות הכוללות FirstPublishDate לאחר 1/1/2018.

עורך השאילתות של Power Query המציג תוצאות מסוננות

כעת יש לנו שאילתה המסננת לפי תאריך באמצעות פרמטר. כדי לסנן את התוצאות על-ידי FirstPublishDate, איננו צריכים עוד למצוא את השדה, לחץ על החץ סינון/מיון, בחר את התיבה לאחר... המסנן סוג והזן ערך תאריך – אנו יכולים פשוט לשנות את הערך של FirstPubD ולרענן את השאילתה. בנוסף, אנו יכולים לעשות שימוש חוזר בפרמטר החדש, לדוגמה, אם החלטנו למשוך קבוצה שונה של שדות ממקור הנתונים המקורי לגליון עבודה חדש, אך עדיין ברצונך לכלול את FirstPubDate ולהשתמש בו כדי לסנן תוצאות.

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

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

חוברת עבודה של Excel המציגה טבלת פרמטר ונתונים הטעונים מ-Power Query

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

נתוני טבלת Excel הטעונים בעורך השאילתות של Power Query

מאחר שהנתונים שנטענו כסוג הנתונים תאריך/שעה, אנו צריכים לשנות אותו לסוג הנתונים תאריך/שעה, כך שהוא תואם לפרמטר שלנו, ולאחר מכן לחץ על בית _GT_ המרה _GT_ Transformסוג נתונים > Date.

העכבר מעל הפקודה ' סוג נתונים ' בקבוצה ' המרה ' בכרטיסיה ' בית ' ברצועת הכלים של עורך השאילתות של Power Query.

אנו גם משנה את שם השאילתה שלנו למשהו בעל משמעות רבה יותר מ-Table2. כדי להבהיר מה משמעות הדבר, אנו קוראים לו FirstPubDate.

עורך השאילתה של Power Query כאשר התיבה ' שם ' מסומנת

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

תפריט תלוי הקשר של עורך השאילתות של Power Query עבור ערך שדה

התצוגה המקדימה מציגה כעת את הערך במקום הטבלה.

עורך השאילתות של Power Query המציג ערך תאריך בודד

אין צורך שהנתונים של השאילתה החדשה ייטענו בכל מקום-הנתונים שלה כבר נמצאים בגליון העבודה שבו אנו מעוניינים בכך. אנו זקוקים רק לחיבור כך ש-Power Query יוכל לקבל את ערך הפרמטר. לאחר מכן, אנו לוחצים על File _GT_ Close & Load to. .. כדי לפתוח את תיבת הדו ייבוא נתונים, ולאחר מכן בחר צור חיבור בלבד.

תיבת הדו ' ייבוא נתונים ' עם האפשרות ' צור חיבור בלבד ' נבחרה

כעת יש לנו שאילתה בשם "FirstPubDate" אשר מושכת ערך תאריך בודד מטבלה בגליון העבודה ממש מעל המקום שבו השאילתה הראשית שלנו נטענת. כעת עלינו רק להשתמש בשאילתה זו כפרמטר לסינון השאילתה הראשית שלנו. לאחר מכן, אנו פותחים את השאילתה הראשית ועורכים את השלב המסנן שורות באמצעות העמודה FirstPublishDate. אנו נרחיב את שורת הנוסחאות ונבחר את הפרמטר שיצרנו בעבר (FirstPubD). לאחר מכן, הקלד "a" לאחר FirstPubD -מאחר שהשם של השאילתה החדשה מתחיל עם אותן אותיות כמו הפרמטר, Power query מציג אותה כאפשרות לבחירה.

שורת הנוסחאות של עורך השאילתות של Power Query מורחבת

נבחר אותה ולאחר מכן לחץ מחוץ לשורת הנוסחאות כדי להחיל את השלב.

עורך השאילתה של Power Query עם נתונים שנטענו

הכל נראה נכון, ולכן אנו יוצאים מעורך השאילתה של Power Query ושומרים את השינויים שלנו. כדי לבדוק את הפרמטר, בגליון העבודה של הדוח נשנה את ערך התא בטבלה בחלק העליון של 5/4/2019, ולאחר מכן נרענן את החיבור כדי לראות את הנתונים המסוננים.

נתונים מסוננים ב-Excel

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

  1. לחץ על data _GT_ קבל _AMP_ המרת נתונים > קבל נתונים _GT_ הפעלה של Power Query Editor.

  2. בעורך Power Query, לחץ על Home > Parameters > נהל פרמטרים.

  3. בתיבת הדו פרמטרים, לחץ על חדש.

  4. הגדר את הפרטים הבאים בהתאם לצורך:

    • Name -זה אמור לשקף את הפונקציה של הפרמטר, אך לשמור אותו קצר ככל האפשר.

    • תיאור -פעולה זו עשויה להכיל פרטים שיעזרו לאנשים להשתמש בפרמטר כראוי.

    • נדרש -בחר כדי לגרום לפרמטר זה לדרוש ערך.

    • Type -פעולה זו מציינת את סוג הנתונים שאליו נדרש הפרמטר.

    • ערכים מוצעים -אם תרצה, הוסף רשימת ערכים או ציין שאילתה שתספק הצעות לקלט.

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

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

  5. לחץ על אישור כדי ליצור את הפרמטר.

  1. פתח שאילתה בעורך השאילתות של Power Query.

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

  3. בתיבת הדו סינון שורות, לחץ על הלחצן משמאל לתנאי המסנן ולאחר מכן בצע אחת מהפעולות הבאות:

    • כדי להשתמש בפרמטר קיים, לחץ על פרמטרולאחר מכן בחר את הפרמטר הרצוי מהרשימה שמופיעה בצד שמאל.

    • כדי להשתמש בפרמטר חדש, לחץ על פרמטר חדש..., ולאחר מכן צור פרמטר.

  1. בגליון העבודה שבו השאילתה שברצונך לסנן נטענת, צור טבלה עם שני תאים: כותרת עליונה וערך.

  2. לחץ על הערך ולאחר מכן לחץ על data _GT_ קבל _AMP_ המרת נתונים _GT_ מטבלה/טווח.

  3. בעורך Power Query, בצע שינויים כלשהם בחיבור הטבלה (לדוגמה, שינוי סוג הנתונים או השם) ולאחר מכן לחץ על Home > Close > Close & load > close &...

  4. בתיבת הדו ייבוא נתונים, לחץ על צור חיבור בלבד, באופן אופציונלי בחר הוסף למודל נתוניםולאחר מכן לחץ על אישור.

  5. פתח את השאילתה שברצונך לסנן בעורך השאילתות של Power Query.

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

  7. בצע את אחת מהפעולות הבאות:

    • בחר ערך מתוך הרשימה הנפתחת של ערכים (אלה מגיעים מנתוני השאילתה).

    • בחר ערך באמצעות הלחצן בקצה השמאלי של תנאי המסנן.

  8. לחץ על החץ בקצה השמאלי של שורת הנוסחאות כדי להציג את השאילתה כולה.

  9. תנאי המסנן עוקב אחר המילה כל אחת:

    • שם העמודה המסוננת מופיעה בסוגריים מרובעים.

    • אופרטור ההשוואה מתבצע מיד אחרי שם העמודה.

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

  10. התחל להקליד את השם של חיבור הטבלה שיצרת זה עתה ולאחר מכן בחר אותו מהרשימה שמופיעה.

  11. לחץ על Home > Close > close & Load.

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

למידע נוסף

יצירת רשימה נפתחת

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

שפר את הכישורים שלך ב- Office
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Office Insider

האם מידע זה היה שימושי?

תודה על המשוב!

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×