آموزش استفاده از تابع QUERY برای جستجو و فیلتر کردن بانک داده در Google Sheets
سرویس ترجمه‌ی گوگل یا Google Translate یکی از محبوب‌ترین سرویس‌های ترجمه است که از زبان‌های مختلفی پشتیبانی می‌کند و قابلیت‌های مفیدی مثل تلفظ صوتی کلمات را به صورت رایگان در اختیار عموم کاربران قرار می‌دهد. قابلیت استفاده از گوگل ترنسلیت در اپلیکیشن تحت وب Google Sheets نیز وجود دارد که برای علاقه‌مندان به اکسل بسیار مفید است.
در این مقاله به روش استفاده از گوگل ترنسلیت با فرمول‌نویسی در گوگل شیتس می‌پردازیم.
Translating Text in Google Sheets
گوگل اپلیکیشن‌های تحت وب مختلفی دارد و استفاده از برخی اپلیکیشن‌ها در دیگر اپلیکیشن‌ها امکان‌پذیر است. یکی از مصادیق این نوع یکپارچگی و مجتمع‌سازی، استفاده از سرویس ترجمه در اپلیکیشن تحت وب Google Sheets است. در واقع فرمولی ساده برای این مهم وجود دارد.
فرمول ترجمه به صورت زیر است:
=GOOGLETRANSLATE(“text”, “source language”, “target language”)
در این فرمول از تابعی به اسم GOOGLETRANSLATE استفاده شده است. این تابع سه آرگومان دارد.
- آرگومان اول متنی است که باید ترجمه شود.
- آرگومان دوم زبان متن است که در واقع زبان مبدأ محسوب می‌شود.
- آرگومان سوم زبان مقصد است.
به راهنمای این تابع و مثالی که گوگل ارایه کرده توجه کنید:
با توجه به تصویر فوق روشن است که می‌توانید در فرمول موردبحث، کلمه‌ای را برای ترجمه کردن وارد کنید یا به جای نوشتن یک کلمه، سلولی را آدرس‌دهی کنید تا محتوای آن ترجمه شود.
اما مثالی از فرمول‌نویسی: فرض کنید در ستون A چند کلمه به زبان انگلیسی موجود است و سوال این است که ترجمه‌ی آن به زبان چینی چیست؟ برای این مهم در ستون B ترجمه را با فرمول محاسبه می‌کنیم. لذا این مراحل را انجام بدهید:
سلول B2 را انتخاب کنید.
کلید = را فشار دهید.
نام تابع ترجمه یعنی GOOGLETRANSLATE را تایپ کنید. استفاده از حروف کوچک یا بزرگ نتیجه‌ی یکسانی دارد.
سپس پرانتز باز کنید.
روی سلول A2 کلیک کنید.
یک ویرگول تایپ کنید.
زبان مبدأ که English است با حروف en مشخص می‌شود. لذا “en” را به عنوان آرگومان دوم تایپ کنید.
یک , تایپ کنید و عبارت “zh” را به عنوان زبان مقصد یا آرگومان سوم تایپ کنید.
پرانتز را ببندید و Enter را فشار دهید.
همان‌طور که در تصویر زیر مشاهده می‌کنید ترجمه‌ی کلمات با مکثی کوتاه و به کمک گوگل ترنسلیت محاسبه و در سلول‌ها قرار می‌گیرد.
فرمول سلول B2 تکمیل شده است. اکنون می‌توانید فرمول این سلول را با روش درگ و دراپ مربع کوچک گوشه‌ی پایین آن در سلول‌های بعدی ستون B کپی کنید به گونه‌ای که هر سلول، ترجمه‌ی سلولی از همان ردیف در ستون اول را ارایه کند.
روش دیگر کپی کردن سلول B2 با شورت‌کات Ctrl + C و سپس پیست کردن در سلول‌های بعدی با Ctrl + V است. در این صورت نیز فرمول متناسب با شماره‌ی ردیف تغییر می‌کند.
howtogeekسیاره‌ی آی‌تی
در نرم‌افزار اکسل مجموعه آفیس مایکروسافت برای استفاده از مجموعه داده‌ها که از فایل‌ها و دیتابیس‌ها و منابع آنلاین دیگر تهیه شده، می‌توانید از Power Query استفاده کنید که قابلیت‌های زیادی دارد. به عنوان مثال می‌توانید اطلاعات موجود در دو فایل اکسل را به راحتی وارد کرده و به شکل جداولی بزرگ‌تر، ترکیب کنید.
در این مقاله به آشنایی با Microsoft Power Query در Excel و آموزش مقدماتی استفاده از آن در قالب یک مثال ساده می‌پردازیم.
آشنایی با Microsoft Power Query در اکسل
توانمندی Power Query این است که می‌توانید داده‌هایی را از چند منبع مختلف تهیه کنید و سپس داده‌ها را ویرایش و پردازش کرده و در نهایت در صفحه‌ای از یک فایل جدید اکسل وارد کنید.
ابزاری به اسم Microsoft Power Query در اکسل ۲۰۱۶ و نسخه‌های جدیدتر قابل استفاده است و در نسخه‌های قدیمی وجود ندارد.
یکی از منابع وارد کردن داده، خود اکسل است! در واقع می‌توانید دیتایی که در صفحات دیگر از یک فایل موجود است را به عنوان منبع داده‌ها انتخاب کنید یا از اطلاعاتی که در یک فایل دیگر موجود است، استفاده کنید.
در این مقاله برای توضیح استفاده از Power Query، اطلاعات موجود در دو فایل اکسل را به عنوان منبع یا سورس در نظر می‌گیریم و این اطلاعات را به کمک پاور کوئری ترکیب می‌کنیم.
فرض کنید دو فایل اکسل به نام‌های Cars.xlsx و Trucks.xslx داریم که در فایل اول مشخصات چند خودرو و در فایل دوم مشخصات چند کامیون ذکر شده است. مشخصات شامل برند، مدل، رنگ و سال تولید می‌شود که ساده و روشن است. به محتویات صفحهی اکسل در این دو فایل توجه کنید:
می‌خواهیم یک فایل دیگر Excel به اسم Vehicles.xslx بسازیم که در آن اطلاعات خودروها و کامیون‌ها جمع می‌شود.
البته برای ساده شدن مراحل کار فرض کرده‌ایم که هدرهای این دو سری داده یکسان است و در واقع مشخصات محصولات مشابه است. اما با استفاده از پاور کوئری می‌توانید داده‌های پیچیده‌تر که یه عنوان مثال ستون‌های سری‌های داده یکسان نیست را هم ترکیب کنید.
بارگذاری داده‌ها از فایل‌های اکسل
گام نخست این است که سری‌های داده را بارگذاری و ویرایش کنیم. برای این مهم می‌توانید از Power Query Editor استفاده کنید.
ابتدا فایل Vehicles.xlsx را باز کنید و سپس روی تب Data در نوار ابزار بالای صفحه کلیک کنید. در این تب روی گزینه‌ی Get Data کلیک کنید. با توجه به اینکه منبع داده‌ها، فایل اکسل است، گزینه‌ی From File و سپس From Workbook را انتخاب کنید.
همان‌طور که در تصویر زیر مشاهده می‌کنید، گزینه‌های منوی کرکره‌ای باز شده متعدد است. در واقع دریافت اطلاعات اولیه از منابع مختلف مثل دیتابیس، سرویس Azure مایکروسافت و دیگر منابع آنلاین و سایر منابع است.
پنجره‌ی انتخاب فایل باز می‌شود. فایل Cars.xlsx را به عنوان فایل اول انتخاب کنید. پاور کوئری مشغول به کار می‌شود و اطلاعات موجود در فایل را پردازش کرده و پیش‌نمایشی از آن ارایه می‌کند. در ستون کناری نام فایل و نام صفحات موجود در فایل را مشاهده می‌کنید و می‌توانید روی دیگر صفحات فایل انتخاب شده کلیک کنید و اطلاعات آن را مشاهده کنید. در نهایت پس از انتخاب کردن صفحهی موردنظر که در مثال ما Cars نام دارد، روی Transform Data کلیک کنید تا داده‌ها ایمپورت و در پنجره‌ی ادیتور باز شود.
کار روی داده‌ها در پنجره‌ی Power Query Editor
پنجره‌ی ویرایش داده‌های پاور کوئری گزینه‌های زیادی دارد و کارهایی زیادی می‌توان انجام داد. همان‌طور که در تصویر زیر مشاهده می‌کنید داده‌های وارد شده به صورت جدول و درست شبیه جداول Excel در ستون‌ها و ردیف‌ها چیده شده است. نام ستون‌ها نیز در ردیف بالایی موجود است. البته داده‌هایی که از دیگر منابع وارد می‌کنید هم به همین شکل نمایش داده می‌شود.
در سمت راست منویی به اسم Query Settings موجود است که در آن مراحل پردازش داده‌ها ذکر می‌شود. در واقع اگر تغییراتی در داده‌های اولیه داده‌اید که اشتباه بوده، می‌توانید مراحل و تغییرات را حذف کنید!
در سمت چپ آیکون Queries دیده می‌شود که با کلیک روی آن، منویی در سمت چپ باز خواهد شد. در این منو نام جدولی که روی آن کار می‌کنید نمایش داده می‌شود. اگر چند جدول داده داشته باشید هم نامشان در سمت چپ لیست می‌شود.
افزودن داده‌های مختلف از منابع بعدی
برای ترکیب کردن داده‌ها می‌بایست داده‌های منابع بعدی را هم ایمپورت کنیم. منبع دوم در این مثال، یک فایل اکسل دیگر است. برای افزودن داده‌های این منبع، در پنجره‌ی Power Query Editor روی دکمه‌ی New Source که در تب Home و در گوشه‌ی بالا و سمت راست واقع است، کلیک کنید.
گزینه‌ی File و سپس Excel را انتخاب کنید تا پنجره‌ی انتخاب فایل باز شود. حال فایل دوم که در مثال ما Trucks.xlsx است را انتخاب کنید. این بار هم پنجره‌ی Navigator باز می‌شود و می‌توانید نام فایل انتخاب شده و صفحه‌های موجود از آن را در ستون کناری و پیش‌نمایش داده‌های آن صفحه را در سمت راست مشاهده کنید. در نهایت با انتخاب کردن صفحهی موردنظر و کلیک روی OK، اطلاعات جدید را وارد ویرایشگر پاور کوئری کنید.
همان‌طور که مشاهده می‌کنید اکنون در نوار ابزار Queries در سمت چپ، دو جدول داده اضافه شده است.
ترکیب جداول داده در Power Query Editor
پس از آماده‌سازی داده‌ها نوبت می‌توانید از داده‌ها به شکل‌های مختلفی استفاده کنید و یکی از کارهای ساده، تلفیق داده‌ها است. برای ترکیب کردن اطلاعات، روی جدول cars کلیک کنید و سپس در نوار ابزار بالای صفحه و تب Home روی Append Queries که در بخش Combine قرار دارد، کلیک کنید.
به این ترتیب پنجره‌ی Append باز می‌شود و در مورد جدولی که می‌خواهید به جدول انتخاب شده اضافه کنید، سوال می‌شود. با توجه به اینکه روی جدول Cars کلیک کرده بودید، جدول جدید Trucks خواهد بود. بنابراین از منوی کرکره‌ای Table to append، گزینه‌ی trucks را انتخاب کنید و سپس روی OK کلیک کنید.
نتیجه‌ی ترکیب کردن این دو جدول را در تصویر زیر مشاهده می‌کنید:
همان‌طور که مشاهده می‌کنید اطلاعات تمام خودروها و کامیون‌ها در یک جدول بزرگ‌تر ترکیب شده است. در سمت راست و لیست Applied Steps هم یک مرحله‌ی جدید که Appended Query نام دارد، اضافه شده است.
استفاده از داده‌های پردازش شده توسط پاور کوئری در اکسل
آخرین گام این است که حاصل ویرایش کردن داده‌های وارد شده به کمک ادیتور پاور کوئری را در یک صفحهی اکسل قرار بدهید و برای کارهای بعدی آن را ذخیره کنید. برای این منظور در پنجره‌ی Power Query Editor روی گزینه‌ی Close & Load در سمت بالا و چپ کلیک کنید. نتیجه این است که اطلاعات در صفحهی Vehicles به شکل جدول قرار می‌گیرد.
با بستن پنجره‌ی پاور کوئری ادیتور، مراحل یا Query Steps ذخیره می‌شود و منویی به اسم Workbook Queries در سمت راست صفحهی اکسل باز می‌شود که در آن کوئری‌ها که در مثال ما دو فایل اکسل بوده، لیست می‌شود. با نگاهی به این بخش می‌توان منابع داده را شناسایی کرد.
makeuseofسیاره‌ی آی‌تی
تابع 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 را شامل می‌شود، ردیف 2 و 3 هر دو هدر است و حاوی اطلاعات نیست، می‌بایست به جای headers عدد 2 را قرار بدهید.
اما یک مثال ساده را بررسی کنیم تا روش فرمول‌نویسی و استفاده از این تابع روشن‌تر شود: فرض کنید جدولی شبیه تصویر زیر داریم که در آن دو ردیف ابتدایی، ردیف‌های عنوان است. در جدول اطلاعات کارمندان شامل شناسه، نام و نام خانوادگی و چند مورد دیگر ذکر شده است. در ستون 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 ذکر شده که در واقع سلول‌های حاوی عدد 1 و اعداد بزرگ‌تر را پوشش می‌دهد.
استفاده از 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سیاره‌ی آی‌تی