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

مطالبی که امروز یاد میگیریم شامل:

• ماهیت فرمول های گوگل شیتس
• انواع مرجع های سلول (cell references)
• مقادیر ثابت در فرمول های گوگل شیتس
• عملگرها در فرمول های گوگل شیتس

• • عملگرهای حسابی
  • عملگرهای مقایسه
  • عملگرهای الحاق متن
  • عملگرهای فرمولی
• ترتیب محاسبات و اولویت عملگرها

• محدوده های نامگذاری شده در گوگل شیتس

• انواع فرمول ها در گوگل شیتس

حالا بریم سراغ یادگیری مطالب:

ماهیت فرمول های گوگل شیتس

اولین چیزی که برای نوشتن فرمول لازم هست، اینه که شما به توابع و یک سری عبارات نیاز دارید.

یک تابع یک عباارت ریاضی هست. که هر کدام نام خاص خود را دارند. برای اینکه گوگل شیتس متوجه بشه که شما دارید یک فرمول رو داخل سلول مینویسید نه یک عدد یا متن، بعد از اینکه در سلول کلیک کردید اول یک علامت مساوی  (=) وارد کنید. سپس، اسم فرمول و بقیه فرمول را تایپ کنید.

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

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

• مراجع سلول
• محدوده داده نامگذاری شده
• مقادیر ثابت متنی یا عددی
• عملگرها
• توابع دیگر

خب بریم ببینیم این موارد دقیقا چی هستن.

انواع مرجع های سلول

هر تابعی برای عمل کردن به داده نیاز دارد و از مراجع سلولی برای نشان دادن آن داده استفاده میشود. وای یعنی چی؟ در ادامه برات مثال میزنم که کامل متوجه بشی.

برای ارجاع (Reference) به یک سلول، یک کد عدد-حروف استفاده میشه- حروف برای ستون ها و عدد برای ردیف ها. به عنوان مثال، A1 اولین سلول در ستون A هست.

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

• A1 (وابسته، یعنی اگر فرمول رو کپی کنی و توی سلول دیگه پیست کنی، تغییر میکنه. لطفا ناراحت نباش که متوجه نشدی توی فرمول نویسی ها کامل توضیح میدم و متوجه میشی، فقط فعلا یک آشنایی با اسم این عبارت ها داشته باش)
• ·         $A$1 (مستقل، با کپی و پیست کردن فرمول در یک سلول دیگه این مرجع سلولی تغییر نمیکنه یعنی ثابت میمونه)
• ·         $A1 یا A$1 (ترکیببی، با کپی و پیست کردن این فرمول در سلول دیگه قسمتی ثابت میمونه و قسمتی تغییر میکنه)

علامت $ مشخص میکنه که کدوم قسمت ثابت بمونه. مثلا در $A1 حرف A ثابت میمونه و تغییر نمیکنه.

وقتی فرمول رو در سلول جدید گسترش میدیم، ارجاع سلول با توجه به سلولی جدیدی که فرمول در اون قرار گرفت تغییر میکنه. مثلا مثلا توی سلول B1 تایپ کن =A1. حالا این فرمول رو در  سلول C2 کپی کن، تبدیل میشه به =B2. حالا چرا اینطوری میشه؟ چون فرمول یک دونه رفته به سمت راست (1 ستون زیاد شده) و یک ردیف رفته پایین تر (1 ردیف زیاد شده)، پس همه مختصات یکی زیاد میشه در نتیجه میشه B2. (این موارد توی فیلم راحت تر بیان میشه انشالله به زودی فیلم آموزشی رو ضبط  میکنیم)

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

در ردیف اول جدول زیر فرمول اصلی که در سلول B1 نوشته شده و همچنین تغییراتی که فرمول اصلی پس از کپی و پیست شدن در C2 داشته را مشاهده میکنید.

=A1

=A$1

=$A1

=$A$1

=B1

=B$1

=$A2

=$A$1

پس، برای جلوگیری از تغییر فرمول هنگام کپی و انتقال، از موارد مطلق (مواردی که $ ثابت شده اند) استفاده کنید.

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

فرض کن توی فرمولت تایپ کردی A1. حالا کلید F4 رو بزن میشه $A$1. یک بار دیگه F4 رو بزن میشه A$1. بار دیگه که F4 رو بزنی میشه $A1. یک بار دیگه که بزنی میشه همون حالت اصلی که اول بود: A1.

نکته: برای اینکه تمام مراجع یک باره تغییر کنند کل فرمول رو انتخاب کن و کلید F4 رو فشار بده.

محدوده داده

در گوگل شیتس هم میشه از تک تک سلول ها و هم از محدوده (گروهی) از سلول ها استفاده کرد. برای اینکه یک محدوده سلول داشته باشی فقط کافی هست که از گوشه پایین سمت راست سلولی که توش هستی به اطراف درگ کنی (کلیک رو به گوشه پایین سمت راست سلول ببری نشانگر موس که به + تبدیل شد کلیک رو نگه داری و به سلول های دیگه بکشی). مثلا در شکل زیر محدوده A1:B5 نشان داده شده است:

مقادیر ثابت در فرمول های گوگل شیتس

مقادیر ثابت در گوگل شیت مقادیری هستند که ثابت میمانند. آنها اعداد و متن هستند مانند: 250 (تعداد)، 17/07/1390  (تاریخ) و سود (متن). این موارد تماما ثابت هستند و میتوانیم آنها را با استفاده از عملگرها و توابع مختلف تغییر دهیم.

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

=50+5*3

یا میتواند محاسبات بر روی یک مقدار ثابت و یک مقدار که در سلول دیگر قرار دارد انجام شود:

=A2+500

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

بنابراین به عنوان مثال کافی است شما عدد 500 را در سلول B2 قرار دهید، و در فرمول به این سلول ارجاع دهید:

=A2+B2

حالا اگر بخواهید به جای 500 عدد 700 را قرار دهید، به راحتی میتوانید عدد داخل سلول B2 را تغییر دهید، نتیجه دوباره محاسبه خواهد شد.

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

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

• عملگرهای حسابی
• عملگرهای مقایسه
• عملگرهای الحاق متن
• عملگرهای فرمولی

عملگرهای حسابی

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

=5-5

=-5

=5*5

=5/5

عملگرهای مقایسه

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

=A1=B1

=A1>B1

=A1<B1

=A1>=B1

=A1<=B1

=A1<>B1

عملگرهای الحاق متن

علامت and انگلیسی (&) برای اتصال (الحاق) چند رشته متنی به یک مورد استفاده میشود. در مثال زیر نتیچه فرمول که نوشتیم «amir daee» خواهد شد:

=  “amir”&”daee”

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

=A1&”,”&B1

عملگرهای فرمولی

این عملگرها برای ساختن فرمول ها در گوگل شیتس و نشان دادن محدوده داده ها استفاده میشود:

=sum(B5:B15,D5:D15)

 این عملگر ها دارای الویت متفاوتی هستند که ترتیب محاسبات را مشخص میکند و اغلب روی مقادیری که نتیجه محاسبات است تأثیر میگذارد.

ترتیب محاسبات و اولویت عملگرها

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

نحوه استفاده از پرانتز برای تغییر در ترتیب محاسبات

برای تغییر ترتیب محاسبات در یک فرمول، بخشی که میخواهید ابتدا محاسبات روی آن انجام شود را داخل براکت بگذارید. بیاید یک مثال رو با هم ببینیم:

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

=5+4*3

در این فرمول ضرب حرف اول را میزند و سپس عمل جمع صورت میگیرد. به این ترتیب نتیجه 17 خواهد بود.

اگر پرانتز را اضافه کنیم، نتیجه تغییر میکند:

=(5+4)*3

در این فرمول ابتدا دو عدد با هم جمع میشوند و سپس در 3 ضرب میشوند. فرمول نتیجه 27 را برمیگرداند.

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

=(A2+25)/sum(D2:D4)

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

• ابتدا مقدار A2 را محاسبه میکند و سپس با 25 جمع میکند.
• مقادیر D2، D3 و D4 را با هم جمع میکند.
• سپس عدد اول را تقسیم بر مجموع مقادیر دوم میکند.

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

محدوده های نامگذاری شده در گوگل شیتس جهت فرمول نویسی در گوگل شیتس

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

فرض کنید که شما ستونی دارید که کل فروش رو برای هر محصول و مشتری محاسبه میکند. مانند ستون «فروش کل» در تصویر زیر که از آن در فرمول خود استفاده کرده ایم.

من مطمئنم که شما قبول میکنید که فرمول زیر

=SUM(total_sales)

خیلی روشن تر هست (و توی خوندن هم آسون تر) از فرمول زیر هست:

=SUM($E$2,$E$13)

نکته: برای نامگذاری محدوده، سلول ها حتما باید کنار هم باشن. به این معنی که نمیتونید سلول هایی که کنار هم نیستن رو انتخاب کنید و به عنوان محدوده نامگذاری کنید.

برای نامگذاری محدوده گام های زیر رو طی کنید:

1-سلول های مجاور هم رو انتخاب کنید.

2-به مسیر Data>Range named برید. یک پنجره سمت راست براتون ظاهر میشه. (یا اینکه میتونید راست کلیک کنید و گزینه define named range رو انتخاب کنید.)

3-نام محدوده رو تایپ کنید و روی done کلیک کنید.

نکته: این پنجره همچنین به شما امکان این رو میده که تمام نام هایی که ایجاد کردید رو ببینید، ویرایش کنید یا حتی حذف کنید:

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

نام گذاری محدوده ها باعث میشه فرمول ها واضح تر و قابل درک باشند. اما یکسری قانون های کوچکی وجود داره که باید اون ها رو موقع نامگذار رعایت کنید. نام:

فقط میتواند شامل حروف، اعداد و زیر خاط ها (_) باشد.

نباید با اعداد یا کلمات «true»  و «Flase» شروع بشه.

نباید در اون از فاصله ( )  یا سایر علائم نگارشی مثل علامت تعجب، ویرگول و .. استفاده بشه.

میتواند 1 تا 250 حرف داشته باشد.

نباید با محدوده انتخابی یکسان باشد. به عنوان مثال اگر بخواهید دامنه را A1:B2 نامگذاری کنید، ممکن است خطا رخ بدهد.

اگر مشکلی پیش آمد مثلا اگر شما نام محدوده را total sales تایپ کنید با خطا رو به رو میشوید. علتش هم به خاطر فاصله ای که بین total و sales است می باشد. باید total sales را به صورت TotalSales یا total_sales تایپ کنید.

توجه: محدوده هایی که در گوگل شیتس نامگذاری میکنید مانند مرجع سلول ثابت هستند. اگر سطرها و ستون هایی به جدول اضافه کنید، محدوده Total_sales تغییر نمیکند. محدوده را به هر مکانی که تغییر دهید، نتایج تغییر نمیکنند.

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

فرمول ها میتونن ساده یا پیچیده باشند.

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

=sum(A1:A10)

=A1+B1

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

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

چطوری به راحتی فرمول های پیچیده را بخوانیم؟

یک ترفند وجود داره که فرمول های شما رو قابل درک میکنه.

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

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

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

=ArrayFormula(MAX(IF(($B$2:$B$13=B18)*($C$2:$C$13=C18), $E$2:$E$13,"")))

شما هم موافقید که روش اول بهتره؟

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