اگر زمان زیادی را صرف کارهای تکراری داده در Google Sheets میکنید، تنها نیستید. من هر هفته ساعتها را به همان مراحل قدیمی میگرفتم—تا اینکه به یک تابع واحد برخورد کردم که تمام کار سنگین را برایم انجام میداد. بگذارید تابع QUERY را به شما معرفی کنم.
اگر زمان زیادی را صرف کارهای تکراری داده در Google Sheets میکنید، تنها نیستید. من هر هفته ساعتها را به همان مراحل قدیمی میدادم—تا اینکه به یک تابع واحد که کار سنگین را برای من انجام میداد، برخورد کردم. بگذارید تابع QUERY را به شما معرفی کنم.
5 مرتبسازی خودکار که بهصورت لحظهای بهروز میشود
سریعترین راهی که تابع QUERY صفحات گسترده من را بهبود داد، حذف سردردهای مرتبسازی مداوم بود. شما میدانید آن چرخهٔ ناامیدکننده که دادههایتان را مرتب میکنید، سپس یک ساعت بعد اطلاعات جدید میآید و فهرست مرتبشدهتان دوباره کاملاً بههم میریزد. همان همین است.
فرض کنید میخواهید موجودی شرکت خود را بر اساس قیمت ردیابی کنید. بهجای مرتبسازی بیپایان، میتوانید از QUERY به این صورت استفاده کنید:
=QUERY(A:G, "SELECT * ORDER BY E desc")
در این مثال، به Google Sheets میگویید که تمام دادهها از ستونهای A تا G را بگیرد و بر اساس ستون E (که در این مورد قیمتها را داراست) به ترتیب نزولی مرتب کند.
=QUERY(A:G, "SELECT * ORDER BY E desc")
فورا، گرانترین خودروهای شما مستقیماً به بالای لیست میرفتهاند.
چیزی که این را تبدیل به یک تغییر اساسی میکند این است که دیگر متوقف نمیشود. وقتی یک خودرو جدید به قیمت ۲۵۰٬۰۰۰ دلار به صفحهگسترده شما اضافه میشود، بلافاصله در بالای نتایج QUERY ظاهر میشود. نیازی به مرتبسازی دوباره نیست.
چیزی که قبلاً چندین دقیقه از کلیک و کشیدن در طول روز میبرد، اکنون بهصورت خودکار انجام میشود. مهمتر از همه، دیگر اقلام با اولویت بالا را از دست نمیدهم چون همیشه در جایی که باید باشند قرار میگیرند.
4 ترکیب چندین مرحله در یک فرمول
نمیتوانم بگویم چند بار خودم را مییابم که این رقص را انجام میدهم: فیلتر کردن دادهها، مرتبسازی آنها، مخفی کردن برخی ستونها، و شاید گروهبندی موارد. حالا، بهدلیل QUERY، میتوانم همه این کارها را در یک بار انجام دهم.
مثلاً میخواهم یک مرور فروش تهیه کنم و باید موارد زیر را انجام دهم:
- فقط خودروهای فروختهشده را فیلتر کنید
- تمام خودروهایی که زیر ۳۰٬۰۰۰ دلار هستند حذف کنید
- تسلاها را حذف کنید
- بر اساس قیمت مرتب کنید تا بزرگترین فروشها اول دیده شوند
اینها چهار مرحلهٔ جداگانه هستند. اگر مجبور بودم این کارها را بهصورت دستی انجام دهم و در هرجای مسیر اشتباه کنم، باید از ابتدا شروع میکردم. در عوض، من مینویسم:
=QUERY(Test!A:G, "SELECT * WHERE F = TRUE AND E > 30000 AND NOT B contains 'Tesla' ORDER BY E desc")
یک فرمول چهار مرحله را جایگزین میکند. چون من تابع QUERY را در یک برگهٔ خالی قرار دادم، نام برگه را با علامت تعجب (Test!) قبل از مشخص کردن ستونهای دادهام اضافه کردم.
=QUERY(Test!A:G, "SELECT * WHERE F = TRUE AND E > 30000 AND NOT B contains 'Tesla' ORDER BY E desc")
بهترین بخش این است که میتوانم حتی با عبارات PIVOT، LABEL و غیره پیچیدهتر شوم. برای مثال، وقتی میخواهم ببینم هر یک از عرضههای هر سال چگونه فروش رفتهاند، GROUP BY را اضافه میکنم:
=QUERY(Test!A:G, "SELECT D, SUM(E) WHERE F = TRUE GROUP BY D ORDER BY SUM(E) desc")
این به من درآمد تا به امروز از هر انتشار سالیانه را نشان میدهد که بهصورت خودکار از بالاترین به پایینترین مرتب شده است.
=QUERY(Test!A:G, "SELECT D, SUM(E) WHERE F = TRUE GROUP BY D ORDER BY SUM(E) desc")
صرفهجویی در زمان به سرعت جمع میشود. چیزی که قبلاً ۱۰ تا ۱۵ دقیقه از کلیک، فیلتر و سازماندهی میبرد، اکنون با نوشتن یک فرمول ۳۰ ثانیه انجام میشود. و برخلاف فرآیند چندمرحلهای قبلی، دیگر بهصورت تصادفی مرحلهای را از دست نمیدهم یا ترتیب را خراب نمیکنم.
چرا بهصورت دستی اعداد را پردازش کنیم وقتی Google Sheets میتواند کار سنگین را انجام دهد؟
3 مدیریت مجموعههای دادهٔ بزرگ بدون هیچ تاخیری
تصور کنید: میخواهید ۱۰۰ مشتری اخیر خود را از یک پایگاه داده با بیش از ۵۰٬000 ردیف پیدا کنید تا یک کمپین ایمیل هدفمند ارسال کنید. ساده بهنظر میرسد، نه؟
نادرست. هر بار که سعی میکنید مرتبسازی، فیلتر یا جابهجایی این حجم عظیم دادهها را انجام دهید، Google Sheets احتمالاً یخ میزند. من تا زمانی که متوجه شدم QUERY میتواند مجموعههای دادهٔ سنگین را مدیریت کند، دچار این مشکل شدم.
در اینجا یک مثال است:
=QUERY('50000 Sales Records'!A:N, "SELECT * ORDER BY H desc LIMIT 100")
فرمول اول ۱۰۰ بارنامهٔ اخیر را برمیگیرد، چرا که ستون H شامل تاریخهای بارنامه است. در عین حال، فرمول دوم سفارشهای ۶۱ تا ۱۶۰ را میگیرد.
=QUERY('50000 Sales Records'!A:N, "SELECT * ORDER BY H desc LIMIT 100")
بهجای اینکه کامپیوتر شما تمام ۵۰٬000+ ردیف را پردازش و نمایش دهد و زمان زیادی ببرد، فقط بهدنبال ۱۰۰ ردیف بالایی هستید، QUERY (با بند LIMIT و OFFSET) بهقدری هوشمند است که دقیقاً چیزی را که نیاز دارید میگیرد و بقیه را رها میکند.
حتی میتوانید از LIMIT و OFFSET همراه با دیگر ویژگیهای QUERY—گروهبندی، مرتبسازی، فیلتر—بدون مبارزه با مرورگر و هدر دادن زمان استفاده کنید.
اگر فقط این کار ساده بود.
2 تحلیل دادهها در چندین برگه یا فایل
آیا هنوز بهصورت دستی دادهها را بین صفحات گسترده یا کتابکارها کپی میکنید فقط برای یک گزارش؟ میتوانید همین الآن متوقف شوید. QUERY به شما امکان میدهد دادهها را در چندین برگه—یا حتی در فایلهای کاملاً متفاوت—تحلیل کنید بدون اینکه هرگز کلید Ctrl+C را فشار دهید.
ترکیب چندین تب در یک بار
فرض کنید دادههای فصلی خود را در تبهایی مانند Sales_Q1 و Sales_Q2 دارید، میتوانید آنها را با استفاده از آکولاد به یک مجموعهٔ دادهٔ واحد ترکیب کنید. سپس، همانند یک برگهٔ واحد، تجزیه و تحلیل خود را اجرا کنید.
=QUERY({Sales_Q1!A:N; Sales_Q2!A:N; Sales_Q3!A:N; Sales_Q4!A:N}, "SELECT Col3, Col1, SUM(Col9) WHERE Col5 = 'C' GROUP BY Col3, Col1")
فرض کنید ستون ۳ (Col3) نوع آیتم، ستون ۱ (Col1) منطقه، ستون ۹ (Col9) تعداد فروش و ستون ۵ (Col5) اولویت سفارش باشد. فقط مطمئن شوید ساختار (ستونها) هر برگه مطابقت داشته باشد و شما آمادهاید.
=QUERY({Sales_Q1!A:N; Sales_Q2!A:N; Sales_Q3!A:N; Sales_Q4!A:N}, "SELECT Col3, Col1, SUM(Col9) WHERE Col5 = 'C' GROUP BY Col3, Col1")
من فقط چهار برگه با دادههای فصلی مختلف را ترکیب کردم تا مجموع واحدهای فروختهشده بر اساس آیتم و منطقه برای سفارشهای اولویت C را بهدست آورم. خیلی ساده!
دریافت دادهها از یک Google Spreadsheet دیگر (بدون نیاز به دانلود)
اگر به دادهها از یک فایل کاملاً متفاوت نیاز دارید، میتوانید از IMPORTRANGE بههمراه QUERY استفاده کنید تا آن را وارد کنید. فرض کنید میخواهید دادههای فروش خودروهای ما را با دادههای چهار برگه ترکیب کنید، میتوانید این فرمول را بهکار ببرید:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/yoursheetID/edit", "Test!A:G"), "SELECT Col4, SUM(Col5) GROUP BY Col4")
قبل از اینکه بتوانید دادهها را از صفحهگستردهٔ خارجی بکشید، باید دسترسی را بدهید.
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/yoursheetID/edit", "Test!A:G"), "SELECT Col4, SUM(Col5) GROUP BY Col4")
پس از اعطای دسترسی، میتوانید دادهها را از صفحهگستردهٔ خارجی بهصورت زمان واقعی بگیرید و اگر دادههای منبع تغییر کنند، بهروزرسانی میشود.
QUERY به شما امکان میدهد دادهها را در تبها و فایلها تحلیل کنید بدون اینکه لازم باشد تب یا فایلی دوم را باز کنید.
1 مرتبسازی و فیلتر کردن بدون بازنویسی فرمولها
آیا میخواهید دادههای خود را بهصورت متفاوتی مرتب یا فیلتر کنید بدون اینکه هر بار کل فرمول QUERY را بازنویسی کنید؟ میتوانید با تنظیم سادهای که از علامتهای نقل قول دوگانه و علامتهای امپرسند استفاده میکند، این کار را انجام دهید:
=QUERY({Sales_Q1!A:N; Sales_Q2!A:N; Sales_Q3!A:N; Sales_Q4!A:N}, "SELECT * WHERE Col1 = '"&G21&"'", 1)
وقتی رئیستان میخواهد دادههای اروپایی را ببیند، فقط “Europe” را در سلول G21 وارد میکنید.
=QUERY({Sales_Q1!A:N; Sales_Q2!A:N; Sales_Q3!A:N; Sales_Q4!A:N}, "SELECT * WHERE Col1 = '"&G21&"'", 1)
وقتی او میخواهد دادههای آفریقا جنوب‑صحرایی را ببیند، “Sub-Saharan Africa” را تایپ میکنید. فقط اطمینان حاصل کنید که مقدار معتبر در ستون ۱ باشد. فرمول همانجا میماند، اما نتایج بلافاصله بهروز میشوند. وقتی مسلط شوید، این قابلیت فوقالعاده قدرتمند است.
جادوی واقعی زمانی رخ داد که من این را برای بازههای تاریخ استفاده کردم. تاریخها در فرمولهای QUERY معمولاً حساس هستند. آنها باید دقیقاً به فرمت YYYY-MM-DD باشند، وگرنه همه چیز خراب میشود. اما با ارجاع به سلولها، میتوانید کنترلهای کاربرپسند تاریخ تنظیم کنید:
=QUERY({Sales_Q1!A:N; Sales_Q2!A:N; Sales_Q3!A:N; Sales_Q4!A:N}, "SELECT * WHERE Col6 >= date '"&TEXT(G142, "yyyy-mm-dd")&"' AND Col6
در اینجا سلول G142 برای تاریخ شروع، سلول I142 برای تاریخ پایان، و ستون ۶ نشاندهنده ستون تاریخ سفارش است. وقتی کسی میپرسد فروش ما از ۱۵ مارس ۲۰۱۱ تا ۳۰ آوریل ۲۰۱۵ چقدر بوده، فقط آن دو سلول را تغییر میدهم بهجای اینکه با سینتکس فرمول مبارزه کنم.
=QUERY({Sales_Q1!A:N; Sales_Q2!A:N; Sales_Q3!A:N; Sales_Q4!A:N}, "SELECT * WHERE Col6 >= date '"&TEXT(G142, "yyyy-mm-dd")&"' AND Col6
بهترین بخش این است که میتوانم این صفحات گسترده را با همکارانی که متخصص فرمول نیستند به اشتراک بگذارم. آنها سلولهای ورودی تمیزی میبینند که میتوانند منطقه یا بازهٔ تاریخ را تغییر دهند، و هیچاشان نمیداند که یک QUERY پیچیده در پسزمینه در حال اجرا است.
بهمحض اینکه این فرمولها را یاد بگیرید، تعجب میکنید که چگونه قبلاً بدون آنها کار میکردید.
تابع QUERY در Google Sheets فقط یک فرمول نیست. این یک نیروی قدرتمند خودکارسازی داده است. چه در حال کار با دهها هزار ردیف باشید، چه دادهها را بین برگهها کپی کنید، یا فقط از مرتبسازی و فیلترهای تکراری خسته شده باشید، QUERY همهٔ اینها را بهراحتی (و با سبک) مدیریت میکند.
سریع است. انعطافپذیر است. و وقتی شروع به استفاده از تابع QUERY در Google Sheets میکنید، بهطور جدی تعجب میکنید که چگونه تا بهحال بدون آن کار میکردید.