عملکرد QUERY به شما امکان می دهد با استفاده از دستورات پرس و جوی انعطاف پذیر اطلاعات را از یک محدوده یا کل صفحه داده استخراج کنید. یادگیری نحوه استفاده از عملکرد کاربرگ‌نگار Google QUERY به شما امکان می‌دهد به یک ابزار جستجوی قدرتمند دسترسی داشته باشید.
اگر تا به حال پرس و جوهای SQL را برای دریافت داده ها از پایگاه داده نوشته اید، تابع QUERY را می شناسید. اگر تجربه پایگاه داده ندارید، یادگیری تابع QUERY همچنان بسیار آسان است.
عملکرد QUERY چیست؟
این تابع دارای سه پارامتر اصلی است:
=QUERY(داده، پرس و جو، سرصفحه)
این پارامترها نسبتاً ساده هستند.
- داده: محدوده سلول هایی که حاوی داده های منبع هستند
- Query: عبارت جستجویی که نحوه استخراج آنچه می خواهید از داده های منبع را شرح می دهد
- Headers: یک آرگومان اختیاری که به شما امکان می دهد چندین سرصفحه در محدوده منبع را در یک سرصفحه واحد در برگه مقصد ترکیب کنید
انعطاف پذیری و قدرت تابع QUERY از آرگومان Query ناشی می شود، همانطور که در زیر می بینید.
چگونه یک فرمول QUERY ساده ایجاد کنیم
فرمول QUERY به ویژه زمانی مفید است که مجموعه داده های بسیار بزرگی دارید که باید داده ها را از آن استخراج و فیلتر کنید.
مثال های زیر از آمار عملکرد دبیرستان SAT ایالات متحده استفاده می کنند. در این مثال اول، یاد خواهید گرفت که چگونه یک فرمول QUERY ساده بنویسید که همه دبیرستان ها و داده های آنها را که "نیویورک" به نام مدرسه است، برمی گرداند.
-
یک برگه جدید برای قرار دادن نتایج پرس و جو ایجاد کنید. در سلول بالا سمت چپ نوع =Query(. هنگامی که این کار را انجام می دهید، یک پنجره بازشو با آرگومان های مورد نیاز، یک مثال، و اطلاعات مفید در مورد تابع خواهید دید.
Image -
بعد، با فرض اینکه داده منبع را در Sheet1 دارید، تابع را به صورت زیر پر کنید:
=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
از آنجایی که تابع Google Sheets QUERY برای درک و استفاده بسیار آسان است، می‌توانید هر داده‌ای را از هر مجموعه داده بزرگی با استفاده از عبارت‌های جستجوی ساده مانند موارد بالا بیرون بکشید.
استفاده از تابع QUERY با اپراتور مقایسه
عملگرهای مقایسه به شما امکان می دهند از تابع QUERY برای فیلتر کردن داده هایی که شرایطی را ندارند استفاده کنید.
شما به همه عملگرهای زیر در یک تابع QUERY دسترسی دارید:
- =: مقادیر با مقدار جستجو مطابقت دارند
- <: مقادیر کمتر از مقدار جستجو هستند
- >: مقادیر بیشتر از مقدار جستجو هستند
- <=: مقادیر کمتر یا مساوی با مقدار جستجو هستند
- >=: مقادیر بزرگتر یا مساوی با مقدار جستجوی هستند
- و !=: مقدار جستجو و مقادیر منبع برابر نیستند
با استفاده از همان مجموعه داده های مثال SAT در بالا، بیایید نگاهی به نحوه مشاهده میانگین ریاضیات در کدام مدارس بیندازیم.
-
در سلول سمت چپ بالای یک صفحه خالی، تابع 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 BY ODER BY MAX(E)")
Image
