توابع کاربردی و منحصر بفرد Google Sheets -بخش اول

توابع کاربردی گوگل شیت

روز به روز نیاز به استفاده از اکسل، بصورت آنلاین بیشتر احساس میشه. پس باید کم کم با نرم افزار اکسل آنلاین (گوگل شیت) آشنا بشیم و بهش مسلط بشیم. در این مقاله قصد داریم توابع کاربردی که در گوگل شیت وجود دارن و در اکسل موجود نیستند رو معرفی کنیم و با ارائه مثال، هر کدوم از این توابع رو توضیح بدیم. گوگل شیت این توابع رو براساس نحوه عملکردشون تقسیم بندی کرده که در این مقاله بر اساس همین تقسیم بندی توابع رو معرفی می کنیم.

گروه اول: توابع ویژه Google Sheets

1. توابع گوگل شیت: تابع ARRAYFORMULA

فرمول های گوگل شیت هر بار با یک سلول کار می کنن اما اگه بتونیم تمام سلول های موجود در محدوده مورد نظرمون رو در یک فرمول محاسبه کنیم قطعا در زمان صرفه جویی میشه. این کار در واقع همون روش فرمول نویسی آرایه ای در اکسل هست (که برای فعال کردن اون از کلید های ترکیبی Ctrl+Shift+Enter استفاده می کنیم) اما با این تفاوت که در گوگل شیت برای انجام این کار یک تابع منحصر به فرد داریم به نام ARRAYFORMULA. این تابع فقط یک آرگومان داره به شکل زیر:

=ARRAYFORMULA (array_formula)

در این تابع کافیه فرمول آرایه ای رو بنویسیم و بعد دکمه Enter رو بزنیم تا نتیجه نمایش داده بشه. برای مثال فرض کنید مطابق شکل زیر در یک شیت نام افراد و پاسخ (بله یا خیر) آن ها به سوال یک تحقیق نمایش داده شده (مطلب پرسشنامه آنلاین رو حتما نگاه کنید). حالا می خواهیم در یک شیت دیگه فقط اسامی افرادی نمایش داده بشه که به سوال جواب “بله” دادن.

شکل ۱- پاسخ افراد به سوالات در گوگل شیت

برای اینکار فرمول رو به صورت زیر می نویسیم:

=ARRAYFORMULA (IF (sheet1! $B$2: $B$9=”بله”, sheet1! $A$2: $A$9,””))

قسمت هایی که با رنگ آبی مشخص شدن در واقع همون بخش آرایه ای فرمول هستن. همان طور که مشخص هست هم در قسمت شرط (logical_test) تابع IF و هم در قسمت value_if_true از یک محدوده (به جای یک سلول) استفاده شده. کافیه بعد از نوشتن فرمول، دکمه Enter رو بزنیم تا نتیجه مطابق شکل زیر نمایش داده بشه.

شکل ۲- استفاده از تابع ARRAYFORMULA برای نمایش اسامی افراد با پاسخ “بله”

2. توابع گوگل شیت: تابع GOOGLEFINANCE

این تابع عملکرد جالبی داره که می تونه هر کاربری رو مجذوب خودش بکنه به خصوص کاربرانی که تحلیل های پولی و مالی انجام میدن. این تابع این امکان رو فراهم میکنه تا داده های بازار مالی و ارز رو در هر زمان مستقیماً به گوگل شیت منتقل کنیم. همچنین با استفاده از این تابع می تونیم اطلاعات مربوط به سهام رو ردیابی کنیم و حتی قیمت سهام رو در یک بازه زمانی استخراج کنیم. این تابع به سرورهای Google Finance متصل می شه و اطلاعات مالی رو مستقیماً به گوگل شیت منتقل می کنه (در اکسل هم ابزار Power Query برای دریافت اطلاعات از منابع مختلف داده ها استفاده میشه). آرگومان های این تابع به صورت زیر هست:

=GOOGLEFINANCE (ticker, [attribute], [start_date], [end_date|num_days], [interval])

همانطور که مشخص هست این تابع چهار آرگومان داره که فقط آرگومان اول الزامی هست و آرگومان های بعدی بسته به اینکه چه نوع اطلاعاتی می خواهیم استخراج کنیم مورد استفاده قرار می گیره.

ticker: این آرگومان پایگاه اطلاعاتی که تابع از اون استفاده می کنه رو مشخص می کنه. در این قسمت از فرمول باید اسم مخفف پایگاه داده مورد نظرمون رو بین دو علامت ” ” وارد کنیم. به عنوان مثال AAPLT،  GOOG MSFT به ترتیب نشانگرهای مربوط به Apple Inc ، Google و Microsoft Corporation هستند.

attribute: این آرگومان اختیاری هست و مشخص می کنه که چه نوع داده ای رو می خواهیم از پایگاه داده ای مشخص شده در قسمت ticker استخراج کنیم. مثلا اگر بخواهیم بالاترین قیمت روز جاری سهام رو بدست بیاریم عبارت “high” رو وارد می کنیم یا اگر بخواهیم آخرین قیمت روز قبل رو داشته باشیم عبارت “closeyest” رو می نویسیم.   attributeها مقادیر مشخصی دارن که به تعدادی از آن ها ها در مثال ها اشاره می کنیم اما لیست کامل رو می تونید از این صفحه مشاهده کنید. مقدار وارد شده به عنوان آرگومان attribute هم مثل آرگومان ticker چون به صورت متنی هست باید بین دو تا علامت ” ” باشه.

در صورتی که برای آرگومان attribute چیزی درج نکنیم فرمول مقدار “price” رو در نظر می گیره.

start_date و end_date|num_days: در صورتی که بخواهیم اطلاعات رو برای یک بازه زمانی مشخص استخراج کنیم (مثلا قیمت سهام رو در یک هفته گذشته) برای این آرگومان ها هم مقدار مشخص می کنیم. به این صورت که تاریخ ابتدای بازه رو درج می کنیم و برای انتخاب بازه هم می تونیم تاریخ درج کنیم و هم تعداد روز های مدنظرمون از ابتدای بازه رو وارد کنیم.

interval: این آرگومان توالی نمایش اطلاعات رو مشخص می کنه و دو مقدار یک (بازه روزانه) و ۷ (بازه هفتگی) برای این آرگومان تعریف شدن.

در ادامه مثال هایی از کاربردهای تابع GOOGLEFINANCE ارائه می کنیم.

مثال ۱: استخراج قیمت فعلی سهام

برای استخراج قیمت فعلی سهام گوگل از بورس آمریکا که به اختصار با Nasdaq نمایش داده میشه فرمول رو به صورت زیر می نویسیم:

=GOOGLEFINANCE (“NASDAQ: GOOG”, “price”)

همانطور که مشخص هست در این مثال فقط از دو آرگومان ابتدایی تابع GOOGLEFINANCE استفاده کردیم و هر دو آرگومان هم چون مقدار متنی دارن در داخل کوتیشن نوشته شدن. خروجی فرمول در شکل زیر نمایش داده شده:

شکل ۳- استخراج قیمت فعلی سهام گوگل با استفاده از تابع GOOGLEFINANCE

مثال ۲: استخراج قیمت سهام برای یک دوره مشخص

در صورتی که بخواهیم قیمت سهام گوگل رو برای یک دوره یک هفته ای به صورت روزانه استخراج کنیم فرمول رو به صورت زیر می نویسیم:

=GOOGLEFINANCE (“NASDAQ: GOOG”, “price”, “۹/۱۳/۲۰۱۹”, ۷, ۱)

در این مثال با توجه به صورت مسئله از همه ی آرگومان های تابع استفاده کردیم و چون اطلاعات رو به صورت روزانه می خواستیم به جای آرگومان interval عدد یک قرار دادیم.

شکل ۴- استخراج قیمت سهام گوگل برای یک دوره هفتگی با استفاده از تابع GOOGLEFINANCE

مثال ۳: استخراج نرخ تبدیل ارز فعلی

به طور مثال برای به دست آوردن نرخ تبدیل یورو به ریال از فرمول زیر استفاده می کنیم:

=GOOGLEFINANCE (“CURRENCY: EURIRR”)

یا برای به دست آوردن نرخ تبدیل دلار آمریکا به ریال از فرمول زیر استفاده می کنیم:

=GOOGLEFINANCE (“CURRENCY: USDIRR”)

خروجی دو فرمول بالا در شکل زیر نمایش داده شده. (همونطور که مشخصه، نرخ ها از بانک مرکزی استخراج میشه و همچنان قیمت دلار، همون ۴۲۰۰۰ اعلام میشه!)

شکل ۵- نمایش نرخ تبدیل ارز با استفاده از تابع GOOGLEFINANCE

مثال ۴: استخراج نرخ تبدیل ارز برای یک تاریخ مشخص

در صورتی که بخواهیم نرخ تبدیل دلار آمریکا به دلار کانادا رو در یک تاریخ مشخص بدست بیاریم، از فرمول به شکل زیر استفاده می کنیم:

=GOOGLEFINANCE (“CURRENCY: USDCAD”, “price”, “۹/۲۰/۲۰۱۸”)

شکل ۶- نمایش نرخ تبدیل ارز در یک تاریخ مشخص با استفاده از تابع GOOGLEFINANCE

برای دسترسی به لیست کامل کد های مربوط به ارز ها می تونید از این لینک استفاده کنید.

3. توابع گوگل شیت: تابع IMAGE

استفاده از تصاویر در فایل ها به خصوص فایل های آموزشی می تونه خیلی جذاب و مفید باشه. تابع IMAGE این امکان رو فراهم می کنه تا بتونیم با تنظیمات مختلف از تصاویر در گوگل شیت استفاده کنیم. آرگومان های این تابع به شکل زیر هست:

=IMAGE (url, [mode], [height], [width])

url: یک آرگومان اجباریه و نشان دهنده آدرس تصویر در اینترنت هست

Mode: این آرگومان تنظیمات مربوط به نحوه قرار گرفتن تصویر در سلول رو مشخص می کنه و اعداد ۱ تا ۴ رو می تونیم براش تعریف کنیم که هر عدد نشان دهنده تنظیم خاصی هست که در ادامه توضیح میدیم. در صورتی که این آرگومان خالی باشه به طور اتومات عدد یک در نظر گرفته می شه.

عدد ۱: در صورت قرار دادن عدد یک در این آرگومان، ابعاد تصویر با ابعاد سلول کاملا تنظیم میشه و اگه سلول بزرگتر بشه ابعاد تصویر تغییر نمیکنه.

عدد ۲: در این حالت ابعاد تصویر با ابعاد سلول تنظیم می شه و اگر سلول بزرگ یا کوچک بشه تصویر هم به همون نسبت تغییر می کنه.

عدد۳: این حالت ابعاد اصلی تصویر حفظ می شه و در صورتی که تصویر بزرگتر از اندازه سلول باشه فقط بخشی از اون نمایش داده میشه.

عدد ۴: در این حالت طول و عرض تصویر رو به اندازه دلخواه وارد می کنیم.

در شکل زیر مثال هایی از هر چهار حالت به ترتیب نمایش داده شده:

شکل ۷- حالت های مختلف استفاده از تابع IMAGE

در تصویر بالا اگر در حالت یک اندازه سلول رو تغییر بدیم ابعاد تصویر هیچ تغییری نمی کنه و ثابت باقی می مونه اما در حالت دو اگر ابعاد سلول تغییر کنه اندازه تصویر هم متناسب با اون تغییر خواهد کرد. در حالت سوم هم چون تصویر اندازه واقعی خودش رو داره فقط بخشی از اون در سلول نمایش داده شده. در حالت چهارم هم برای طول و عرض تصویر عدد ۱۰۰ درج شده.

4. توابع گوگل شیت: تابع QUERY

می تونیم بگیم تابع QUERY جامع ترین و قدرتمند ترین تابع در گوگل شیت هست و به روش های مختلفی می تونیم از این تابع استفاده کنیم. این تابع می تونه جایگزین تابع FILTER در گوگل شیت باشه همچنین به جای توابع SUM ،AVERAGE و COUNT هم قابل استفاده هست.

فرمول های ساخته شده با تابع QUERY این امکان رو میده تا مجموعه داده های بزرگ رو مدیریت کنیم برای این منظور از یک زبان اختصاصی کوئری استفاده می کنیم که این زبان دستوراتی (command) داره که برای تابع QUERY مشخص می کنه که چه کاری باید انجام بده.

آرگومان های تابع QUERY به شکل زیر هست:

=QUERY (data, query, [headers])

data: محدوده داده هایی هست که می خواهیم با استفاده از تابع QUERY، فرمان خاصی رو براش اجرا کنیم و یک آرگومان اجباری هست.

query: این آگومان مشخص می کنه که دقیقا چه کاری رو می خواهیم با داده ها انجام بدیم و آرگومان اجباری هست. تمام فرمان هایی که دراین قسمت نوشته میشه باید بین دو تا علامت کوتیشن (” “) باشه.

headers: این آرگومان اختیاری هست و تعداد ردیف های سربرگ رو در اون مشخص می کنیم. در صورتی که این آرگومان خالی بمونه، تابع به صورت پیش فرض مقدار ۱- رو در نظر می گیره. در این حالت تابعQUERY  سعی خواهد کرد تعداد سربرگ ها رو براساس محتوای سلول ها مشخص کنه.

تابع QUERY کاربرد های زیادی داره که امکان مطرح کردن همه اون ها در این مقاله وجود نداره لذا سعی می کنیم چند کاربرد متداول از این تابع رو با ارائه مثال توضیح بدیم:

مثال ۱: انتخاب داده ها با استفاده از تابع QUERY

فرض کنید در یکی از شیت ها (در اینجا شیت ۳) جدولی حاوی اطلاعات فروش مناطق مختلف داریم و می خواهیم این جدول در شیت دیگری نمایش داده بشه. برای این کار در شیت مقصد (که می خواهیم اطلاعات به اون منتقل بشه) فرمول زیر رو می نویسیم:

=QUERY (Sheet3! A1:D15, “select*”)

همان طور که مشخص هست در فرمول بالا به جای آرگومان data، محدوده داده و اسم شیت وارد شده و در قسمت query هم چون قصد انتخاب کل جدول رو داریم از فرمان select* استفاده شده که حتما باید بین دو تا کوتیشن باشه. نتیجه استفاده از فرمول بالا در شکل زیر نمایش داده شده:

شکل ۸- انتخاب کل اطلاعات یک محدوده با تابع QUERY

در صورتی که فقط بخشی از اطلاعات جدول رو نیاز داشته باشیم مثلا اطلاعات ستون A و C رو، فرمول به شکل زیر تغییر خواهد کرد:

=QUERY (Sheet1! A1:D15, “select A, C”)

خروجی فرمول در شکل زیر نمایش داده شده:

شکل ۹- انتخاب بخشی از اطلاعات یک محدوده با تابع QUERY

مثال ۲: نمایش شرطی اطلاعات با استفاده از عبارت where

در این حالت تابع QUERY در نقش فیلتر اطلاعات عمل می کنه و براساس شرطی که در آرگومان query می نویسیم اطلاعات رو نمایش می ده. در این حالت علاوه بر عبارت select از یک عبارت دیگر به نام where هم برای تعریف شرط استفاده می کنیم.

به طور مثال فرض کنید در جدول شکل ۸ فقط بخواهیم اطلاعات فروش محدوده غرب رو ببینیم فرمول رو به صورت زیر می نویسیم:

=QUERY (sheet3! A1:D15, “select * where A contains ‘غرب’ “)

خروجی فرمول بالا در شکل زیر نمایش داده شده:

شکل ۱۰- نمایش داده های فروش غرب با تابع QUERY

در صورتی که بخواهیم فقط اطلاعات فروش مبالغ بالای ۲۰ میلیون تومان نمایش داده بشه از فرمول زیر استفاده می کنیم:

=QUERY (sheet3! A1:D15, “select * where D > 20000000”)

خروجی فرمول بالا در شکل زیر نمایش داده شده:

شکل ۱۱- نمایش داده های فروش بالاتر از ۲۰ میلیون تومان با تابع QUERY

مثال ۳: مرتب کردن داده با استفاده از فرمان “order by” در تابع QUERY

در صورتی که بخواهیم جدول شکل ۸ رو براساس اطلاعات ستون D به صورت نزولی مرتب کنیم از فرمول زیر استفاده می کنیم:

=QUERY (sheet3! A1:D15, “select * order by D DESC”)

در صورتی که بخواهیم اطلاعات رو به صورت صعودی مرتب کنیم از عبارت ASC به جای DESC استفاده می کنیم.

خروجی فرمول بالا در شکل زیر نمایش داده شده:

شکل ۱۲- مرتب کردن داده ها براساس ستون فروش با استفاده از فرمان order by

5. توابع گوگل شیت: تابع SPARKLINE

SPARKLINE چارت های کوچک و ساده ای هستن که در یک سلول قرار می گیرن و برای بهبود جنبه گرافیکی گزارش ها و فهم سریعتر و بهتر روند داده ها از اون ها استفاده می شه. SPARKLINE در اکسل به عنوان یک ابزار شناخته می شه اما در گوگل شیت یک تابع اختصاصی به همین نام وجود داره. آرگومان های این تابع به شکل زیر هست:

=SPARKLINE (data, [options])

data: در این قسمت داده هایی که می خواهیم نمودار آن ها رو رسم کنیم انتخاب می کنیم.

Options: در این قسمت مشخصات نموداری که می خواهیم رسم کنیم رو وارد می کنیم و در صورتی که خالی باشه خروجی فرمول یک نمودار خطی خواهد بود.

برای آشنایی نحوه عملکرد این تابع مثال های زیر رو بررسی می کنیم.

مثال ۱: فرض کنید در جدول شکل ۸ که مربوط به اطلاعات فروش مناطق مختلف هست بخواهیم برای روند فروش، نمودار خطی رسم کنیم در این حالت از فرمول زیر استفاده می کنیم:

=SPARKLINE (D2:D15)

چون آرگومان دوم خالی هست خروجی فرمول نمودار خطی خواهد بود که در شکل زیر نمایش داده شده:

شکل ۱۳- رسم نمودار خطی با استفاده از تابع SPARKLINE

مثال ۲: در صورتی که بخواهیم در مثال قبل نمودار به صورت ستونی ترسیم کنیم فرمول به صورت زیر می نویسیم:

=SPARKLINE (D2:D15, {“charttype”,”column”})

همان طور که در فرمول بالا نوشته شده، مشخصات نمودار رو بین دو براکت می نویسیم و عبارت های charttype و column رو هم بین دو تا کوتیشن وارد کردیم.

خروجی فرمول بالا به شکل زیر خواهد شد:

شکل ۱۴- رسم نمودار ستونی با استفاده از تابع SPARKLINE

مثال ۳: در صورتی که بخواهیم علاوه بر نوع چارت، رنگ چارت رو هم مشخص کنیم از فرمول زیر استفاده می کنیم:

=SPARKLINE (D2:D15, {“charttype”, “column”; “color”, “orange”})

مشاهده می کنید که تمامی مشخصاتی که برای چارت تعریف می کنیم باید حتما بین دو تا کوتیشن باشن و بین مشخصات نوع چارت و رنگ چارت هم با یک علامت سمیکالن ; تفکیک ایجاد کردیم.

خروجی فرمول بالا به صورت زیر هست:

شکل ۱۵- رسم نمودار ستونی با تعیین رنگ با استفاده از تابع SPARKLINE

مشخصات دیگری هم می تونیم برای ترسیم نمودار به تابع SPARKLINE اضافه کنیم که لیست کامل دستورات مربوط به این بخش رو در این صفحه در دسترس هست.

در قسمت اول این مقاله تعدادی از توابع رو که در زیر مجموعه توابع ویژه گوگل شیت قرار دارن بررسی کردیم و در مورد هر تابع آرگومان ها رو توضیح دادیم و با ارائه مثال های متنوع عملکرد هر تابع رو بررسی کردیم. در بخش های بعدی این مقاله سایر توابع گوگل شیت رو بررسی خواهیم کرد. همچنین اگه با گوگل شیت آشنا نیستید حتما مطلب گوگل شیت، اکسل آنلاین رو ببینید.