آغاز ثبت نام دوره آنلاین مقدماتی تا میانی اکسل (ظرفیت محدود)
سبد خرید
0

سبد خرید شما خالی است.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

توابع پیشرفته
نظر شما در مورد این آموزش

در بخش اول این سری مقالات با گروه اول توابع گوگل شیت (توابع ویژه گوگل شیت) آشنا شدیم در این بخش از مقاله می خواهیم گروه دیگه ای از توابع گوکل شیت رو مورد بررسی قرار بدیم. توابع پیشرفته گوگل شیت (مثل تابع Filter، تابع SORT، تابع UNIQUE، تابع COUNTUNIQUE …) که توانایی خوبی به کاربران گوگل شیت میده. اگر آشنایی اولیه با گوگل شیت دارید توصیه میکنم سری مقالات آشنایی با گوگل شیت رو ببینید.

گروه دوم توابع پیشرفته : توابع مربوط به مرتب سازی و فیلتر کردن در گوگل شیت

تابع FILTER گوگل شیت

همانطور که می دونیم در اکسل ویژگی فیلتر رو در قالب یک ابزار داریم (البته در ورژن های آینده تابع هم اضافه خواهد شد) اما در گوگل شیت یک تابع به نام فیلتر وجود داره. ویژگی اصلی که این تابع داره این هست که داده های اصلی رو حفظ می کنه و در درمحدوده ای دیگری نزدیک به محل داده های اصلی می تونیم اطلاعات فیلتر شده رو مشاهده کنیم (کاری شبیه Copy to another Location در ابزار Advanced Filter). آرگومان های تابع فیلتر به شکل زیر هست:

=FILTER (range, condition1, [condition2])

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

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

=FILTER (A1:D15, C1:C15=”تلویزیون”)

نکته مهمی که در این فرمول وجود داره این هست که شماره ردیف های محدوده داده باید با شماره ردیف های مربوط به شرط یکی باشه. برای مثال اگر محدوده داده (RANGE) از ردیف ۱ شروع شده تا ردیف ۱۵ محدوده شرط هم باید به همین شکل باشه در غیر این صورت فرمول با خطا مواجه می شه.

همان طور که در شکل ۱ نمایش داده شده تابع فیلتر با حفظ داده های اصلی، داده های فیلتر شده رو نمایش میده.

توابع پیشرفته - فیلتر داده ها در گوگل شیت با استفاده از تابع Filter و اعمال یک شرط

شکل ۱-  توابع پیشرفته – فیلتر داده ها در گوگل شیت با استفاده از تابع Filter و اعمال یک شرط

در صورتی بخواهیم علاوه بر شرط قبل شرط دیگری (مثلا مبالغ بالای ۱۴ میلیون) هم اعمال کنیم فرمول به شکل زیر خواهد بود که خروجی فرمول هم در شکل ۲ نمایش داده شده.

=FILTER (A1:D15, C1:C15=”تلویزیون”, D1:D15 > 14000000)

توابع پیشرفته - فیلتر داده ها در گوگل شیت با استفاده از تابع Filter و اعمال دو شرط

شکل ۲- فیلتر داده ها در گوگل شیت با استفاده از تابع Filter و اعمال دو شرط

تابع UNIQUE گوگل شیت

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

=UNIQUE (range)

در شکل زیر یک مثال از این تابع ارائه شده:

حذف داده های تکراری با استفاده از تابع UNIQUE

شکل ۳- حذف داده های تکراری با استفاده از تابع UNIQUE

همان طور که در شکل ۳ نمایش داده شده داده های غیرتکراری از محدوده A1:B17 استخراج شده و در سمت چپ نمایش داده شده.

تابع COUNTUNIQUE گوگل شیت

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

=COUNTUNIQUE (value1, [value2, …])

بر خلاف تابع UNIQUE که اطلاعات موجود در سطرهای یک محدوده رو با هم مقایسه می کنه و فقط سطرهایی رو نمایش میده که داده های غیر تکراری دارن، تابع COUNTUNIQUE با سلول ها سر و کار داره و تعداد سلول هایی که اطلاعات غیر تکراری دارن رو شمارش می کنه.

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

شمارش سلول های غیر تکراری با تابع COUNTUNIQUE

شکل ۴- توابع پیشرفته – شمارش سلول های غیر تکراری با تابع COUNTUNIQUE

همان طور که در شکل ۴ مشخص هست تعداد سلول های غیر تکراری در هر کدوم از ستون ها A و B، ۵ تا هست (چهار تا منطقه و یکی هم خود کلمه منطقه و در ستون B چهارتا محصول و یکی هم خود کلمه نام کالا) و در کل محدوده ۱۰ سلول غیر تکراری داریم (ترکیب منطقه و نام کلا).

تابع SORT گوگل شیت

تابع دیگه ای که در گوگل شیت وجود داره و فعلا در اکسل نیست (به صورت ابزار در اکسل وجود داره نه تابع) تابع SORT هست. آرگومان های تابع SORT به صورت زیر هست:

=SORT (range, sort_column, is_ascending, [sort_column2, is_ascending2, …])

range: در این قسمت محدوده داده هایی که می خواهیم مرتب کنیم رو انتخاب می کنیم؛

sort_column: شماره ستونی که در محدوده مورد نظر می خواهیم مرتب سازی رو براساس آن انجام بدیم؛

is_ascending: در این آرگومان مشخص می کنیم که می خواهیم داده ها رو به صورت صعودی مرتب کنیم یا نزولی. این آرگومان یکی از دو مقدار TRUE یا FALSE رو می گیره. در صورتی که بخواهیم داده ها رو به صورت صعودی مرتب کنیم عبارت TRUE رو قرار میدیم و در غیر این صورت (مرتب سازی به صورت نزولی) عبارت FALSE رو درج می کنیم.

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

در شکل زیر یک مثال از این تابع نمایش داده شده که در اون براساس اطلاعات فروش (که در ستون ۴ ام محدوده داده ای قرار داره) داده ها به صورت صعودی مرتب سازی کردیم.

مرتب سازی داده ها با استفاده از تابع SORT گوگل شیت

شکل ۵- توابع پیشرفته – مرتب سازی داده ها با استفاده از تابع SORT گوگل شیت

در صورتی که بخواهیم داده ها علاوه بر اینکه براساس ستون “مقدار فروش” مرتب سازی بشن، براساس ستون “نام کالا” هم به صورت نزولی مرتب سازی بشن، فرمول رو مطابق شکل زیر می نویسیم:

مرتب سازی داده ها با استفاده از تابع SORT گوگل شیت و اعمال دو ستون در فرمول

شکل ۶- مرتب سازی داده ها با استفاده از تابع SORT گوگل شیت و اعمال دو ستون در فرمول

همان طور که در شکل ۶ مشخص هست در دو سطر اول چون مقادیر فروش با هم برابر بوده و در فرمول ستون “نام کالا” رو هم برای مرتب سازی اضافه کردیم پس بین کالای “ماشین لباسشویی” و “یخچال” چون نحوه مرتب سازی رو نزولی در نظر گرفتیم اول اطلاعات مربوط به “یخچال” نمایش داده شده.

نکته کاربردی:

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

SORTN (range, [n], [display_ties_mode], [sort_column1, is_ascending1], …)

Range: در این قسمت محدوده داده ای رو قرار میدیم؛

[n]: این آرگومان اختیاری هست و به صورت پیش فرض عدد یک رو می گیره. در این آرگومان مشخص می کنیم که چند سطر از جدول بعد از مرتب سازی نمایش داده بشه؛

[display_ties_mode]: این آرگومان هم اختیاری هست و به صورت پیش فرض عدد صفر رو می گیره. در این آرگومان مشخص می کنیم که سطرهایی که با سطرهای مرتب شده یکسان هستند به چه صورتی نمایش داده بشن.

عدد ۰: حداکثر n ردیف اول را در محدوده مرتب شده نمایش میده؛

عدد ۱: حداکثر n ردیف اول را نمایش می ده، به علاوه هر ردیف اضافی را که مشابه ردیف n هست؛

عدد ۲: حداکثر اولین n ردیف رو بعد از حذف ردیف های تکراری ، نمایش میده؛

عدد ۳: حداکثر n ردیف اول منحصر به فرد رو نمایش میده، اما هر کپی از این ردیف ها رو هم نشون میده.

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

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

نمایش 4 سطر اول مرتب شده با استفاده از تابع SORTN گوگل شیت

شکل ۷- توابع پیشرفته – نمایش ۴ سطر اول مرتب شده با استفاده از تابع SORTN گوگل شیت

همان طور که در شکل بالا نمایش داده شده چون در فرمول به جای عدد n مقدار ۴ رو قرار دادیم، در خروجی فرمول ۴ سطر اول پس از مرتب سازی داده ها نمایش داده شده.

گروه سوم توابع پیشرفته : توابع مربوط به ترکیب یا تقسیم سلول ها در گوگل شیت

توابعی که برای ترکیب و تقسیم سلول ها در گوگل شیت استفاده می شن به ترتیبJOIN  و SPLIT نام دارن.

تابع SPLIT گوگل شیت

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

توابع پیشرفته - جدا کردن نام و نام خانوادگی با تابع SPLIT در گوگل شیت

شکل ۸- توابع پیشرفته – جدا کردن نام و نام خانوادگی با تابع SPLIT در گوگل شیت

در صورتی که به جای آدرس یک سلول، آدرس یک محدوده رو وارد کنیم باید از ترکیب تابع SPLIT با تابع ARREYFORMULA که در بخش اول این مقاله توضیح دادیم به صورت زیر استفاده کنیم.

توابع پیشرفته - ترکیب توابع ARREYFORMULA و SPLIT

شکل ۹- توابع پیشرفته – ترکیب توابع ARREYFORMULA و SPLIT

تابع JOIN گوگل شیت

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

ترکیب مقادیر سلول ها با تابع JOIN گوگل شیت

شکل ۱۰- توابع پیشرفته – ترکیب مقادیر سلول ها با تابع JOIN گوگل شیت

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

گروه چهارم توابع پیشرفته : توابع مربوط به انتقال داده ها از وب در گوگل شیت

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

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

=IMPORTRANGE (spreadsheet_url, range_string)

spreadsheet_url: در این قسمت آدرس URL سند گوگل شیت که می خواهیم از داده های اون استفاده کنیم رو وارد می کنیم.

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

یک نمونه از نحوه نگارش این تابع به صورت زیر هست:

=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1V8IjzfD9EiwfkV2wBx8KgJ9g3GQGQOyl3_P3Go/edit”, “Sheet1!A1:B10”)

نکته مهم:
اولین باری که به سند دیگه ای ارجاع میدیم، فرمول با خطا مواجه می شه. برای اینکه با این خطا مواجه نشیم باید مجوز دسترسی به صفحه گسترده دیگه ای رو به تابع بدیم. برای این کار کافیه موس رو روی پیغام خطا نگه داریم و گزینه Allow the function access another file انتخاب کنیم.

 

توابع IMPORTHTML و IMPORTDATA گوکل شیت

این دو تابع برای انتقال اطلاعات از صفحات اینترنت مورد استفاده قرار می گیرن.در صورتی که داده های صفحه مربوطه در قالب csv یاtsv  باشه از تابع IMPORTDATA استفاده می کنیم. آرگومان های این تابع به صورت زیر هست:

=IMPORTDATA (url)

به جای آرگومان url هم می تونیم آدرس url صفحه مورد نظر رو وارد کنیم و هم اینکه آدرس رو به یک سلول ارجاع بدیم و آدرس سلول رو به جای url قرار بدیم.

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

=IMPORTHTML (url, query, index)

url: در این قسمت آدرس url وب سایت رو وارد می کنیم؛

Query: برای این آرگومان دو مقدار list و table رو می تونیم وارد کنیم. اگر لیست خاصی در وب سایت هست عبارت list رو وارد می کنیم و اگر جدول مشخص رو بخواهیم عبارت table رو درج می کنیم؛

Index: اگر در وبسایت مربوطه چندین جدول یا لیست وجود داره، با وارد کردن عدد مربوطه به جدول یا لیست، داده های مورد نظرمون رو مشخص می کنیم؛

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

=IMPORTHTML (“https://travel.gc.ca/travelling/advisories”, “table”, 1)

گروه پنجم توابع پیشرفته : توابع تبدیل اعداد و انجام عملیات ریاضی در گوگل شیت

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

تابع TO_DATE: برای تبدیل عدد به تاریخ

تابع  TO_DOLLARS: برای تبدیل عدد به فرمت دلار

تابع TO_PERCENT: برای تبدیل عدد به فرمت درصد

تابع TO_PURE_NUMBER: این تابع فرمت (تاریخ، درصد و ….) رو از عدد حذف می کنه

تابع TO_TEXT: برای تبدیل عدد به فرمت متن

تمام توابع بالا یک آرگومان دارند و اون هم یک مقدار عددی هست.

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

توابع ADD, MINUS, DIVIDE, MULTIPLY که به ترتیب از چپ به راست برای جمع، تفریق، تقسیم و ضرب استفاده می شن؛

تابع EQ برای مقایسه اینکه مقادیر مختلف با هم برابر هستن (در واقع خلاصه Equal هست)

تابع NE برای مقایسه اینکه مقادیر مختلف با هم برابر نیستن. (در واقع خلاصه Not Equal هست)

تابع GT برای مقایسه اینکه آیا اولین مقدار بزرگتر هست یا نه. (در واقع خلاصه Greater Than هست)

تابع GTE برای مقایسه اینکه آیا اولین مقدار بزرگتر مساوی هست یا نه. (در واقع خلاصهGreater Than or Equal هست)

تابع LT برای مقایسه اینکه آیا اولین مقدار کوچکتر هست یا نه. (در واقع خلاصه Less Than هست)

تابع LTE برای مقایسه آیا اولین مقدار کوچکتر مساوی هست یا نه. (در واقع خلاصهLess Than or Equal هست)

همان طور که در بخش اول این مقاله هم گفتیم این توابع، توابعی هستند که در گوگل شیت موجود هستن اما فعلا تا این لحظه در اکسل وجود ندارن و سعی کردیم در این دو مقاله در قالب دسته بندی معرفی شده در گوگل شیت این توابع و نحوه عملکردشون رو توضیح بدیم. همچنین مطالب بیشتری در مورد توابع پیشرفته در گوگل شیت رو میتونید در مقاله شش تابع پیشرفته گوگل شیت بخونید.

127

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

دیدگاه کاربران
  • محمدحسین کلانتری ۱۲ تیر ۱۴۰۰ / ۸:۱۲ ب٫ظ

    با سلام و درود
    در مورد زمان و تاریخ در گوگل شیت چند سوال داشتم
    البته یک صفحه گسترده ایجاد شده و فقط در خروجی های زمان آن مشکلی پیش آمده است
    (مشکلات پیش آمده بابت تغییر زمانهایی است که هر کاربر وارد این صفحات می شود)
    ممنون می شوم که در صورت امکان مطالب را برایتان به صورت کامل توضیح داده و از راهنماییهایتان بیشتر استفاده کنم
    با کمال تشکر

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

توسط
تومان