اگر بیش از حد برای انجام کارهای داده های تکراری در برگه های Google صرف می کنید ، تنها نیستید. من هر هفته ساعت ها را در همان مراحل قدیمی از دست می دادم – تا اینکه روی یک عملکرد واحد که بلند کردن سنگین را برای من انجام می داد ، گیر افتادم. بگذارید شما را با عملکرد پرس و جو آشنا کنم.
اگر بیش از حد برای انجام کارهای داده های تکراری در برگه های Google صرف می کنید ، تنها نیستید. من هر هفته ساعت ها را در همان مراحل قدیمی از دست می دادم – تا اینکه روی یک عملکرد واحد که بلند کردن سنگین را برای من انجام می داد ، گیر افتادم. بگذارید شما را با عملکرد پرس و جو آشنا کنم.
5
مرتب سازی خودکار که خود را در زمان واقعی به روز می کند
فوری ترین روش عملکرد پرس و جو ، صفحات گسترده من را با از بین بردن سردردهای مرتب سازی مجدد من بهبود بخشید. شما می دانید که چرخه ناامید کننده در جایی که داده های خود را مرتب می کنید ، پس از یک ساعت بعد ، اطلاعات جدید وارد می شود و ناگهان لیست مرتب شده شما دوباره کاملاً خارج است. این یکی است
بگویید می خواهید موجودی شرکت خود را بر اساس قیمت ردیابی کنید. به جای مرتب سازی مجدد بی وقفه ، می توانید از یک پرس و جو مانند این استفاده کنید:
= پرس و جو (الف: g ، “انتخاب * سفارش توسط e desc”)
=QUERY(A:G, "SELECT * ORDER BY E desc")
در این مثال ، شما به Google Sheets می گویید که همه چیز را از ستون های A تا G بکشید و آن را بر اساس ستون E (که در این حالت قیمت ها را در اختیار دارد) به ترتیب نزولی مرتب کنید.
بلافاصله ، گرانترین اتومبیل های شما مستقیماً به بالا شناور می شوند.
آنچه این یک تغییر دهنده بازی را می سازد این است که کار را متوقف نمی کند. هنگامی که یک ماشین 250،000 دلاری جدید به صفحه گسترده شما اضافه می شود ، فوراً در بالای نتایج پرس و جو شما ظاهر می شود. نیازی به مرتب سازی مجدد نیست.
چیزی که قبلاً چندین دقیقه از کلیک و کشیدن در طول روز برای من طول می کشد ، اکنون به طور خودکار اتفاق می افتد. مهمتر از همه ، من دیگر هرگز موارد اولویت بالایی را از دست نمی دهم زیرا آنها همیشه در جایی که باید باشند درست هستند.
4
ترکیب چندین مرحله در یک فرمول
من نمی توانم به شما بگویم که چند بار برای پیدا کردن خودم این رقص را پیدا کردم: فیلتر کردن داده ها ، مرتب سازی آن ، پنهان کردن ستون های خاص و شاید گروه بندی کارها را با هم. اکنون ، به لطف پرس و جو ، می توانم همه این کارها را در یک عکس انجام دهم.
بیایید بگوییم که من می خواهم یک بررسی فروش را تهیه کنم ، و باید موارد زیر را انجام دهم:
- فقط اتومبیل های فروخته شده را فیلتر کنید
- هر اتومبیل زیر 30،000 دلار را بردارید
- تسلا را حذف کنید
- مرتب سازی بر اساس قیمت برای دیدن بزرگترین فروش اول
این چهار مرحله جداگانه است اگر مجبور شدم این کار را به صورت دستی انجام دهم و در هر کجا اشتباه کنم ، باید دوباره شروع کنم. در عوض ، من تایپ می کنم:
= پرس و جو (تست! a: g ، “انتخاب کنید * جایی که f = true و e> 30000 و نه b حاوی سفارش” تسلا “توسط e desc است”)
=QUERY(Test!A:G, "SELECT * WHERE F = TRUE AND E > 30000 AND NOT B contains 'Tesla' ORDER BY E desc")
یک فرمول چهار مرحله را جایگزین می کند. از آنجا که من عملکرد پرس و جو را روی یک برگه خالی وارد کردم ، قبل از مشخص کردن ستون ها با داده های خود ، نام برگه را با علامت تعجب (تست!) اضافه کردم.
بهترین بخش این است که من می توانم حتی با بندهایی مانند Pivot ، Label و غیره حتی جذاب تر شوم.
= پرس و جو (تست! A: G ، “انتخاب D ، SUM (e) که در آن f = گروه واقعی توسط d ترتیب توسط جمع (e) desc”)
=QUERY(Test!A:G, "SELECT D, SUM(E) WHERE F = TRUE GROUP BY D ORDER BY SUM(E) desc")
این درآمد را به من نشان می دهد که تاکنون از نسخه های هر سال ، به طور خودکار از بالاترین تا پایین ترین طبقه بندی شده است.
صرفه جویی در وقت به سرعت اضافه می شود. آنچه قبلاً 10 تا 15 دقیقه از کلیک ، فیلتر کردن و سازماندهی من در حال حاضر 30 ثانیه طول می کشد تا یک فرمول را تایپ کنم. و بر خلاف روند چند مرحله ای قدیمی من ، من هرگز به طور تصادفی یک قدم را نمی گذرانم و نظم را آشفته نمی کنم.
چرا وقتی Google Sheets می تواند وزنه برداری سنگین را انجام دهد ، شماره های خرد شده را به صورت دستی انجام می دهد؟
3
دست زدن به مجموعه داده های عظیم و بدون هیچ گونه تاخیر
این را تصویر کنید: شما در حال تلاش برای پیدا کردن 100 مشتری جدید خود از یک پایگاه داده بیش از 50،000 ردیف برای ارسال یک کمپین ایمیل هدفمند هستید. این به اندازه کافی ساده است ، درست است؟
اشتباه هر بار که سعی می کنید مرتب سازی ، فیلتر یا مانور آن نوع مجموعه داده های عظیم را انجام دهید ، احتمالاً برگه های Google یخ می زنند. من به این موضوع رسیدگی کردم تا اینکه فهمیدم که پرس و جو می تواند مجموعه داده های حجیم را اداره کند.
در اینجا یک مثال وجود دارد:
= پرس و جو (‘50000 سوابق فروش’! a: n ، “انتخاب * سفارش توسط h desc limit 100”)
=QUERY('50000 Sales Records'!A:N, "SELECT * ORDER BY H desc LIMIT 100")
فرمول اول 100 محموله برتر را به دست می آورد ، زیرا ستون H شامل تاریخ حمل و نقل است. در همین حال ، مورد دوم سفارشات 61 تا 160 را به دست می آورد.
به جای اینکه فرآیند رایانه خود را بسازید و تمام 50،000+ ردیف را نمایش دهید ، در حالی که وقت شیرین خود را می گیرید ، فقط می توانید به 100 مورد برتر نگاه کنید ، پرس و جو (با محدوده محدودیت و افست) به اندازه کافی هوشمند است که دقیقاً آنچه را که لازم دارید بدست آورید و بقیه را تنها بگذارید.
شما حتی می توانید با تمام ویژگی های پرس و جو دیگر – گروه بندی ، مرتب سازی ، فیلتر کردن – بدون مبارزه با مرورگر و اتلاف وقت ، از محدودیت و جبران استفاده کنید.
اگر فقط ساده بود.
2
تجزیه و تحلیل داده ها در چندین برگه یا پرونده
آیا هنوز هم برای انجام یک گزارش ، هنوز به صورت دستی داده ها را بین صفحه گسترده یا کتاب های کاری کپی می کنید؟ اکنون می توانید متوقف شوید. Query شما را قادر می سازد تا داده ها را در چندین برگه – یا حتی در پرونده های کاملاً متفاوت – تجزیه و تحلیل کنید ، بدون اینکه Ctrl+C را لمس کنید.
چندین زبانه را به صورت یک حرکت ترکیب کنید
بگویید که داده های سه ماهه ای را در میان زبانه هایی مانند sales_q1 و sales_q2 تقسیم کرده اید ، می توانید با استفاده از براکت های مجعد ، آنها را در یک مجموعه داده واحد ادغام کنید. سپس ، تجزیه و تحلیل خود را درست مثل خود در یک برگه انجام دهید.
= پرس و جو ({sales_q1! a: n ؛ sales_q2! a: n ؛ sales_q3! a: n ؛ sales_q4! a: n} ، “select col3 ، col1 ، sum (col9) که col5 = ‘c’ توسط col3 ، col1”)
=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")
بیایید بگوییم ستون 3 (col3) نوع مورد ، ستون 1 منطقه ، ستون 9 واحدهای فروخته شده و ستون 5 اولویت سفارش است. فقط اطمینان حاصل کنید که ساختار (ستون) هر ورق مطابقت دارد ، و خوب است که بروید.
من فقط چهار برگه را با داده های مربوط به محله های مختلف ترکیب کردم تا کل واحدهای فروخته شده در هر کالا و منطقه را برای سفارشات اولویت C بدست آورم. آسان peasy!
داده ها را از صفحه گسترده دیگر Google بکشید (بدون بارگیری لازم نیست)
اگر به یک پرونده کاملاً متفاوت نیاز دارید ، می توانید از Importrange با پرس و جو برای وارد کردن آن استفاده کنید. بگویید می خواهید داده ها را از فروش خودروهای ما وارد کنید و آن را با داده هایی که از چهار برگه بدست آورده ایم ، آن را اضافه کنید ، می توانید از این فرمول استفاده کنید:
= پرس و جو (Importrange (“https://docs.google.com/spreadsheets/d/yoursheetid/edit” ، “تست! A: G”) ، “گروه Col4 ، SUM (COL5) گروه COL4”)
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/yoursheetID/edit", "Test!A:G"), "SELECT Col4, SUM(Col5) GROUP BY Col4")
قبل از اینکه بتوانید داده ها را از صفحه گسترده خارجی بکشید ، باید مجوز اعطا کنید.
پس از دسترسی ، می توانید داده ها را از صفحه گسترده خارجی در زمان واقعی بگیرید و در صورت تغییر داده های منبع ، به روز می شود.
Query به شما امکان می دهد بدون نیاز به باز کردن یک برگه یا پرونده دوم ، داده ها را در زبانه ها و پرونده ها تجزیه و تحلیل کنید.
1
مرتب سازی و فیلتر بدون بازنویسی فرمول ها
آیا می خواهید داده های خود را به طور متفاوتی مرتب یا فیلتر کنید بدون اینکه هر بار کل فرمول پرس و جو خود را بازنویسی کنید؟ شما می توانید با یک تنظیم ساده با استفاده از دو برابر و ampersands:
= پرس و جو ({sales_q1! a: n ؛ sales_q2! a: n ؛ sales_q3! a: n ؛ sales_q4! a: n} ، “انتخاب کنید * جایی که col1 = ‘” & g21 & “” “، 1)
=QUERY({Sales_Q1!A:N; Sales_Q2!A:N; Sales_Q3!A:N; Sales_Q4!A:N}, "SELECT * WHERE Col1 = '"&G21&"'", 1)
وقتی رئیس شما می خواهد داده های اروپایی را ببیند ، شما فقط “اروپا” را در سلول G21 تایپ می کنید.
وقتی او داده های زیر صحرای آفریقا را می خواهد ، شما “زیر صحرای آفریقا” را تایپ می کنید. فقط اطمینان حاصل کنید که این یک ورودی معتبر در ستون 1 است. فرمول همان باقی می ماند ، اما نتایج فوراً به روز می شود. هنگامی که آن را آویزان کردید ، فوق العاده قدرتمند است.
جادوی واقعی وقتی اتفاق افتاد که من از این کار برای محدوده تاریخ استفاده کردم. تاریخ در فرمول های پرس و جو بسیار بدنام است. آنها باید دقیقاً در قالب مناسب (yyyy-mm-dd) باشند یا همه چیز شکسته می شود. اما با منابع سلولی ، می توانید کنترل تاریخ کاربر پسند را تنظیم کنید:
= پرس و جو ({sales_q1! a: n ؛ sales_q2! a: n ؛ sales_q3! a: n ؛ sales_q4! a: n} ، “انتخاب کنید * جایی که col6> = تاریخ” “و متن (g142 ،” yyyy-mm-dd “) و” “و col6
=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 را برای تاریخ پایان و ستون 6 نشان می دهم که ستون تاریخ را نشان می دهد. وقتی شخصی می پرسد فروش ما از 15 مارس 2011 ، تا 30 آوریل 2015 چه بوده است ، من فقط آن دو سلول را به جای کشتی گیری با نحو فرمول تغییر می دهم.
بهترین بخش این است که می توانم این صفحات گسترده را با همکاران که متخصص فرمول نیستند به اشتراک بگذارم. آنها سلولهای ورودی تمیز را می بینند که در آن می توانند منطقه را تغییر دهند یا محدوده تاریخ را تنظیم کنند ، و هیچ ایده ای ندارند که یک پرس و جو پیچیده در پشت صحنه اجرا شود.
هنگامی که این فرمول ها را یاد گرفتید ، تعجب خواهید کرد که چگونه بدون آنها کار کرده اید.
عملکرد پرس و جو در برگه های Google فقط یک فرمول نیست. این یک نیروگاه اتوماسیون داده تمام عیار است. این که آیا شما ده ها هزار ردیف را درگیر کرده اید ، کپی کردن داده ها بین ورق ها ، یا فقط بیمار از مرتب سازی و فیلتر تکراری ، پرس و جو همه را با سهولت (و سبک) انجام می دهد.
سریع است انعطاف پذیر است و هنگامی که شروع به استفاده از عملکرد پرس و جو در برگه های Google کردید ، به طور جدی تعجب خواهید کرد که چگونه بدون آن مدیریت کرده اید.