تابع QUERY یکی از توابع جالب و کاربردی Google Sheets است. این تابع برای جستجو کردن داده در بانک داده طراحی شده است. بانک داده می‌تواند یک جدول کوچک و ساده باشد و در عین حال می‌تواند یک صفحه‌ی طولانی از اطلاعات باشد. استفاده از QUERY در کار با داده‌های بسیار زیاد، ضروری است. با کمک تابع کوئری علاوه بر جستجو کردن، می‌توانید داده‌ها را به شکل‌های مختلف فیلتر کنید و برای آنالیز و بررسی از داده‌ها استفاده‌ی بهینه کنید.
در ادامه به نحوه‌ی استفاده از تابع QUERY در گوگل شیتس می‌پردازیم. با ما باشید.
آموزش استفاده از QUERY در Google Sheets
تابع QUERY شاید به نظر تابع پیچیده و عجیبی باشد اما در واقع استفاده کردن از آن ساده است، به خصوص برای کسانی که با بانک اطلاعاتی با فرمت‌هایی نظیر SQL سروکار داشته‌اند. در واقع نحوه‌ی استفاده کردن از QUERY شبیه به کوئری‌هایی است که برای کار با بانک داده SQL اجرا می‌کنیم.
سینتکس عمومی استفاده از این تابع، به صورت زیر است:
=QUERY(data, query, headers)
در این فرمول ساده می‌بایست به جای data، محدوده‌ی سلول‌ها را قرار داد. به عنوان مثال برای کار با سلول‌های بین A2 تا D12 می‌بایست data را با A2:D12 جایگزین کنید یا برای جستجو در سلول‌های ستون A الی D می‌توانید عبارت A:D را به عنوان محدوده‌ی سلول‌ها تعریف کنید.
آرگومان بعدی Query است، همان چیزی که به دنبال آن هستید. آرگومان سوم اختیاری است و می‌توانید برای مشخص کردن ردیف‌های عنوان که در بالای جدول داده‌های اولیه قرار دارد، از آن استفاده کنید. شاید بیشتر اوقات فقط یک ردیف عنوان داشته باشید اما احتمال اینکه ردیف‌های عنوان بیشتر باشد، وجود دارد. به عنوان مثال اگر در سلول‌های جدول که محدوده‌ی A2:D12 را شامل می‌شود، ردیف ۲ و ۳ هر دو هدر است و حاوی اطلاعات نیست، می‌بایست به جای headers عدد ۲ را قرار بدهید.
اما یک مثال ساده را بررسی کنیم تا روش فرمول‌نویسی و استفاده از این تابع روشن‌تر شود: فرض کنید جدولی شبیه تصویر زیر داریم که در آن دو ردیف ابتدایی، ردیف‌های عنوان است. در جدول اطلاعات کارمندان شامل شناسه، نام و نام خانوادگی و چند مورد دیگر ذکر شده است. در ستون E وضعیت شرکت کردن در دوره‌ی آموزشی ذکر شده است.
و همین‌طور فرض کنید که در Sheet بعدی یا به عبارت دیگر در صفحه‌ی دیگر فایل گوگل شیتس، می‌خواهید از تابع QUERY استفاده کنیم و کارمندانی که در دوره‌ی آموزشی شرکت نکرده‌اند را لیست کنیم. در لیست ما قرار است اطلاعاتی مثل نام و شناسه‌ی کارمند موجود باشد. اگر نام صفحه‌ی اول را Staff List در نظر بگیریم، محدوده‌ی سلول‌ها یا در واقع آرگومان اول عبارت Staff List'!A2:E12 خواهد بود.
آرگومان بعدی QUERY است که با دستور SELECT یا انتخاب کردن و به صورت SELECT A, B, C, E WHERE E = 'No' نوشته می‌شود. به عبارت دیگر به همه‌ی ستون‌های A الی E نیازی داریم با این شرط که در ستون E عبارت NO درج شده باشد. در نهایت به فرمول زیر می‌رسیم:
=QUERY('Staff List'!A2:E12, "SELECT A, B, C, E WHERE E = 'No'")
این فرمول را در اولین سلول صفحه‌ی دوم وارد کرده و Enter را فشار می‌دهیم.
اگر محدوده‌ی سلول‌ها را گسترده‌تر تعریف کنیم، با اضافه شدن افراد جدید به لیست صفحه‌ی اول، تابع QUERY نیز عمل می‌کند و اگر شخص در دوره‌ی آموزشی شرکت نکرده باشد، نام و مشخصات وی را در صفحه‌ی دوم فایل گوگل شیتس نمایش می‌دهد. لذا نیازی به فرمول‌نویسی مجدد نیست. برای این حالت می‌بایست به جای A2 الی E12، از A2 الی E استفاده کنیم که به معنی شروع از سلول A2 و ادامه‌ی محدوده تا پایان ستون E است. لذا فرمول ما به این شکل تغییر می‌کند:
=QUERY('Staff List'!A2:E, "Select A, B, C, E WHERE E = 'No'")
اکنون اگر اطلاعات کارمندی جدید به مجموعه داده‌ها اضافه شود، در صفحه‌ی دوم نیز اضافه می‌شود.
فرمول‌نویسی پیشرفته با QUERY
تابع QUERY گاهی در کاربردها و فرمول‌های ساده استفاده می‌شود اما گاهی در ترکیب با توابع منطقی نظیر AND و OR و توابعی نظیر COUNT یا شمارش و شروطی مثل بزرگ‌تر و کوچک‌تر، یک فرمول پیچیده را تشکیل می‌دهد. در ادامه با چند مثال ساده، این نوع فرمول‌نویسی را توضیح می‌دهیم.
فیلتر کردن داده‌ها با شرط بزرگ‌تر یا کوچک‌تر در تابع QUERY
برای فیلتر کردن داده‌ها می‌توانید از شروطی مثل بزرگ‌تر و کوچک‌تر و نامساوی استفاده کنید. فرض کنید به جدول اطلاعات کارمندان یک ستون جدید اضافه کرده‌ایم که تعداد برنده شدن در قرعه‌کشی‌های ماهانه در آن ذکر شده است. برای ایجاد لیستی از افراد که لااقل یک مرتبه در قرعه‌کشی برنده شده‌اند، می‌توانید از فرمول زیر استفاده کنید:
=QUERY('Staff List'!A2:F12, "SELECT A, B, C, D, E, F WHERE F > 0")
در فرمول فوق شرط F > 0 ذکر شده که در واقع سلول‌های حاوی عدد ۱ و اعداد بزرگ‌تر را پوشش می‌دهد.
استفاده از AND و OR در تابع کوئری
همان‌طور که اشاره کردیم گزاره‌های منطقی هم برای فیلتر کردن داده‌ها کاربرد فراوانی دارد، به خصوص گزاره‌هایی شامل تابع AND به معنی و و شرط OR به معنی یا. با این دو تابع می‌توانید چند شرط را در یک مرحله بررسی کنید و فرمول‌ها را ساده‌تر بنویسید. به عنوان مثال اگر بخواهید سلول‌های حاوی عدد بزرگ‌تر از ۲۰۰۰ و در عین حال کوچک‌تر از ۳۰۰۰ را پیدا کنید، می‌توانید از AND استفاده کنید.
اما یک مثال ساده: فرض کنید که می‌خواهیم لیست کارمندانی که تاریخ تولدشان بین سال ۱۹۸۰ الی ۱۹۹۰ است را ایجاد کنیم. دو شرط بزرگ‌تر از اولین روز سال ۱۹۸۰ و آخرین روز سال ۱۹۸۹ می‌بایست بررسی شود و اگر هر دو برقرار بود، آن ردیف در لیست قرار بگیرد. بنابراین از فرمول زیر استفاده می‌کنیم:
=QUERY('Staff List'!A2:E12, "SELECT A, B, C, D, E WHERE D >= DATE '1980-1-1' and D <= DATE '1989-12-31'")
استفاده از تابع OR نیز به همین صورت است. به عنوان مثال می‌توانیم لیستی بسازیم که کارمندانی که در سال‌های ۱۹۸۰ الی ۱۹۹۰ متولد نشده‌اند، در آن قرار بگیرد.
=QUERY('Staff List'!A2:E12, "SELECT A, B, C, D, E WHERE D >= DATE '1989-12-31' or D <= DATE '1980-1-1'")
استفاده از COUNT در تابع کوئری
فرض کنید که می‌خواهیم تعداد کارمندانی که در دوره‌ی آموزشی شرکت نکرده‌اند و شرکت کرده‌اند را بدانیم. برای این منظور می‌توانید از فرمول زیر استفاده کنید که در آن تابع COUNT برای شمارش تعداد کارمندان استفاده شده است:
=QUERY('Staff List'!A2:E12, "SELECT E, COUNT(E) group by E")
howtogeekسیاره‌ی آی‌تی
سلام وقت بخیر
من از تابع query استفاده کردم
ولی مشکلم اینه که اگر تعداد سلول هایی که شرط کوئری رو دارا هستند از سلولهایی که شرط کوئری رو دارا نیستند، کم تر باشد به غیر از سرستونها دیگه چیزی رو نمایش نمیده
میشه لطفا راهنمایی کنید بنده رو