اگر سال‌ها با مایکروسافت اکسل کار کرده باشید، کار کردن با Google Sheets برایتان خیلی سخت نخواهد بود. با این حال، بعضی از توابع منحصربه‌فرد در Google Sheets وجود دارند که وقتی یادشان بگیرید، کارتان را به شکل چشمگیری راحت‌تر می‌کنند.
در این مقاله سه تابع مهم و پرکاربرد را بررسی می‌کنیم که می‌توانند تجربه شما در Google Sheets را به سطح بالاتری ببرند.
1- تابع QUERY: تجزیه و تحلیل سریع داده‌ها
تابع QUERY در Google Sheets درست به اندازه ابزار Power Query در اکسل مفید است. تفاوت در اینجاست که Power Query در یک پنجره جداگانه اجرا می‌شود، اما در گوگل شیت همه‌چیز با کمک یک فرمول انجام می‌گیرد.
نحوۀ نوشتن تابع QUERY
ساختار این تابع به شکل زیر است:
=QUERY(a,b,c)
-
a: محدوده داده‌هایی که می‌خواهید پردازش کنید
-
b: کوئری نوشته‌شده با زبان Google Visualization API Query
-
c: (اختیاری) تعداد ردیف‌های عنوان در ابتدای داده‌ها. اگر ننویسید یا مقدار -1 بدهید، گوگل شیت به صورت خودکار تشخیص می‌دهد.
مثال‌های کاربردی QUERY
اگر تا حالا با SQL کار نکرده باشید، شاید در ابتدا کمی پیچیده به نظر برسد. اما وقتی اولین بار امتحانش کنید، متوجه می‌شوید که چقدر قدرتمند است.
فرض کنید می‌خواهید لیست کارمندانی را که آموزش را کامل کرده‌اند (یعنی در ستون D مقدار “Y” دارند) استخراج کنید.
فرمول به این شکل است:
=QUERY(T_Staff,"SELECT B, C WHERE D = 'Y'")
-
T_Staff: نام جدول داده‌ها
-
SELECT B, C: انتخاب داده‌های ستون‌های B و C
-
WHERE D = ‘Y’: فقط ردیف‌هایی که در ستون D مقدار “Y” دارند
نکته: کل کوئری باید داخل کوتیشن دوتایی (” “) باشد و مقادیر متنی داخل کوتیشن تک‌تایی (‘ ‘).
حالا اگر بخواهید کارمندانی را پیدا کنید که هم آموزش دیده‌اند و هم بیشتر از 30000 دلار سود داشته‌اند:
=QUERY(T_Staff,"SELECT B, C WHERE D='Y' AND E>30000")
کوئری‌های پیشرفته‌تر
فرض کنید می‌خواهید تعداد افرادی که آموزش دیده‌اند و ندیده‌اند را جداگانه بشمارید:
=QUERY(T_Staff,"SELECT D, COUNT(D) GROUP BY D")
یا می‌خواهید بررسی کنید میانگین سود کسانی که آموزش دیده‌اند بیشتر است یا خیر:
=QUERY(T_Staff,"SELECT D, AVG(E) GROUP BY D")
نتیجه نشان می‌دهد افرادی که آموزش دیده‌اند، به طور میانگین حدود 5000 دلار بیشتر سود کرده‌اند.
نکته: توابع تجمیعی در QUERY شامل avg(), count(), max(), min(), sum() هستند.
2- تابع IMPORTRANGE: وارد کردن داده از فایل دیگر
در اکسل برای وارد کردن داده از یک فایل به فایل دیگر معمولا از Power Query استفاده می‌کنید. در گوگل شیت، این کار با تابع IMPORTRANGE انجام می‌شود.
نحوۀ نوشتن تابع IMPORTRANGE
=IMPORTRANGE(a,b)
- a: آدرس فایل (URL) یا سلولی که URL در آن قرار دارد
- b: محدوده داده‌ها در آن فایل (مثل A1:A10 یا Sheet2!A1:C10)
مثال‌های IMPORTRANGE
فرض کنید می‌خواهید لیست اسامی از فایل 1 به فایل 2 وارد شود.
فرمول:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d...","A2:A11")
اگر اولین بار باشد که این ارتباط را برقرار می‌کنید، احتمالا خطای #REF! می‌بینید. در این صورت باید روی Allow Access کلیک کنید. اگر دسترسی نداشته باشید باید از صاحب فایل درخواست دسترسی کنید.
مزیت بزرگ این تابع این است که اگر داده‌ها در فایل مبدأ تغییر کنند، داده‌های واردشده در فایل مقصد هم به‌روزرسانی می‌شوند.
مثال دیگر:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d...","Sheet2!A2:A11")
یا اگر آدرس فایل در سلول A1 و محدوده داده در سلول B1 باشد:
=IMPORTRANGE(A1,B1)
3- تابع GOOGLETRANSLATE: ترجمه متن
همان‌طور که از نامش پیداست، تابع GOOGLETRANSLATE متن را از یک زبان به زبان دیگر ترجمه می‌کند.
نحوۀ نوشتن GOOGLETRANSLATE
=GOOGLETRANSLATE(a,b,c)
-
a: متن یا سلولی که متن در آن است
-
b: کد زبان مبدأ (اختیاری)
-
c: کد زبان مقصد (اختیاری)
اگر b را ننویسید، گوگل شیت زبان را خودش تشخیص می‌دهد. اگر c را ننویسید، متن به زبان پیش‌فرض سیستم ترجمه می‌شود.
مثال‌های GOOGLETRANSLATE
فرض کنید فایل گوگل شیت دارید که در ستون A چند جمله به زبان‌های مختلف نوشته شده است و می‌خواهید همه به انگلیسی ترجمه شوند.
=GOOGLETRANSLATE(A1)
تابع به‌طور خودکار زبان را تشخیص داده و ترجمه می‌کند. کافی است فرمول را برای بقیه سلول‌ها کپی کنید.
سناریوی عملی: فرم چندزبانه
فرض کنید در ستون C افراد اطلاعات خود را وارد می‌کنند و شما می‌خواهید بر اساس انتخاب زبان در سلول B1، آن‌ها ترجمه شوند.
سپس با کمک Data Validation یک لیست کشویی از زبان‌ها می‌سازید:
فرمول نهایی برای ترجمه:
=IFERROR( GOOGLETRANSLATE(A4,"en", XLOOKUP($B$1,Codes!$C$1:$C$55,Codes!$B$1:$B$55)), "")
از این سه تابع قدرتمند غافل نشوید!
با این سه تابع قدرتمند:
-
QUERY برای تحلیل سریع داده‌ها
-
IMPORTRANGE برای ادغام فایل‌های مختلف
-
GOOGLETRANSLATE برای ترجمه خودکار
کار با Google Sheets نه تنها جایگزینی برای اکسل است، بلکه در بسیاری از سناریوها حتی راحت‌تر و سریع‌تر عمل می‌کند.