فرمول‌های مهم و پرکاربرد در گوگل شیت

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

گوگل شیت یک ابزار فدرتمند برای سازماندهی، تجزیه و تحلیل و اشتراک‌گذاری داده‌ها است. یکی از ویژگی‌های مهم گوگل شیت، قابلیت استفاده از فرمول‌هاست. فرمول‌های گوگل شیت به شما کمک می‌کنند تا محسبات پیچیده را بدون نیاز به وارد کردن دستی داده‌ها انجام دهید.

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

• جمع، تفریق، ضرب و تقسیم
• محاسبه میانگین، انحراف استانداراد و سایر شاخص‌های آماری
• تقارن داده‌ها
• جست‌وجوی داده‌ها
• ایجاد نمودارها و جداول

فرمول‌های گوگل ‌شیت به چند دسته تقسیم می‌شوند:

• فرمول‌های پایه مانند AVERAGE, SUM و IF
• فرمول‌های آرایه مانند SUMPRODUCT و OFFSET
• فرمول‌های پیوند مانند VLOOKUP و HLOOKUP
• فرمول‌های توابع آماری مانند NORMDIST و TDIST

در گوگل شیت می‌توانید فرمول‌های خاص خودتان را ایجاد کنید. همچنین می‌توانید فرمول‌های آن را در سایر برنامه‌های صفحه‌گسترده مثل Microsoft Excel استفاده کنید اما ممکن است برخی از این ویژگی‌ها در این برنامه‌ها پشتیبانی نشود.

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

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

• فرمول SUM برای جمع سلول‌های یک محدوده استفاده می‌شود.
• فرمول AVERAGE برای محاسبه میانگین سلول‌های یک محدوده استفاده می‌شود.
• فرمول MAX برای یافتن بزرگترین مقدار در یک محدوده استفاده می‌شود.
• فرمول MIN برای یافتن کوچکترین مقدار در یک محدوده استفاده می‌شود.
• فرمول COUNT برای شمارش سلول‌های حاوی مقادیر عددی در یک محدوده استفاده می‌شود.
• فرمول COUNTA برای شمارش سلول‌های حاوی هر نوع داده در یک محدوده استفاده می‌شود.
• فرمول IF برای انجام یک عمل بر اساس یک شرط استفاده می‌شود.
• فرمول VLOOKUP برای یافتن مقدار یک سلول در یک جدول بر اساس مقدار یک سلول دیگر استفاده می‌شود.

از میان فرمول‌های ذکر شده فرمول‌های پایه مانند AVERAGE, SUM و IF برای اکثر کاربران ضروری است. همچنین فرمول‌های آرایه و پیوند می‌توانند بسیار مفید باشند.

1: فرمول COUNTIF

فرمول COUNTIF بر اساس فرمول رایج COUNT ساخته می‌شود. این فرمول برای شمارش سلول‌های یک محدوده مشخص، کاربرد دارد و بر اساس معیار و شرطی که برای آن تعیین می‌کنید، عمل شمارش را انجام می‌دهد. ساختار فرمول به شکل زیر است:

=COUNTIF(cellrange, criteria)

در این فرمول range محدوده سلول‌هایی است که می‌خواهید شمارش کنید.
criteria مقداری است که می‌خواهید سلول‌ها را با آن مقایسه کنید.

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

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

1: ابتدا یک سلول خالی انتخاب کنید و بعد از نوشتن علامت مساوی، COUNTIF را تایپ کنید:

=COUNTIF

2: محدوده سلولی موردنظر خود را که قصد دارید شمارش روی ان اعمال شود، انتخاب کنید یا آن را تایپ کنید.

3: بعد از تعیین محدوده سلولی، نوبت به تعیین معیار می‌رسد. داخل گیومه معیار و شرطی را که برای شمارش در نظر دارید بنویسید.

برای مثال ما اینجا قصد داریم ببینیم مجموع چند سلول با هم بیشتر از 20000 دلار هستند. برای این منظور فرمول =COUNTIF(F2:F352,”>$20,000″) را برای این دستور نوشتیم. لازم نیست حتماً فرمول را در همان ستون یا ردیف بنویسید.

2: فرمول VLOOKUP

یکی از مفیدترین فرمول‌های Google Sheets، فرمول VLOOKUP است. این تابع برای جمع‌آوری و ادغام اطلاعات از شیت‌های مختلف، کاربرد دارد. به عنوان مثال ممکن است فهرستی از شماره ID و اسم کارمندان را در یک جدول داشته باشید. همچنین مجموعه‌ای دیگر از داده‌ها را در اختیار دارید که شامل ID و نرخ پرداخت است. حالا برای اینکه بخواهید اسم کارمندان را به مجموعه دوم داده‌های خود اضافه کنید، می‌توانید این کار را به صورت دستی یا با استفاده از  VLOOKUP انجام دهید. این فرمول به صورت خودکار و با استفاده از ID کارمندان که در هر دو مجموعه داده، مشترک هستند انجام می‌شود.

می‌توان از VLOOKUP در همان برگه یا در برگه دیگری در همان فایل استفاده کنید. ممکن است چند VLOOKUP در برگه‌های دیگر ایجاد کرده باشید که بر اساس داده‌های یک صفحه اصلی، جست‌وجو می‌کنند. وقتی این داده‌ها را تغییر دهید، نتایج فرمول در برگه‌های دیگر تغییر می‌کند.

فرمول VLOOKUP به‌ صورت زیر است:

=VLOOKUP(lookup_value, table_array, column, range_lookup)

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

1: سلولی که می‌خواهید نتیجه را در آن ببینید، انتخاب کنید و شروع به نوشتن فرمول کنید:

VLOOKUP(=

2: در این مرحله سلولی را که جست‌وجو بر اساس آن انجام می‌شود، انتخاب کنید. در این مثال ما از ID کارمندان استفاده می‌کنیم:

3: محدوده موردنظر برای جست‌وجو را انتخاب کنید. تنها نقطه‌ضعف VLOOKUP این است که ستون شامل ID که جست‌وجو می‌شود باید اولین ستون در محدوده شما باشد و داده‌ها باید در سمت راست آن باشد.

4: حالا شماره ستونی (نه حرف) را که می‌خواهید اطلاعات از آن استخراج شود، وارد کنید. در این مثال ما اسم کوچک کارمندان را از ستون 3 استخراج می‌کنیم.

5: در این مرحله باید از بین True و False انتخاب کنید. True نتایج تقریبی و False نتایج دقیق‌تری را نمایش می‌دهد.

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

VLOOKUP(E2,$A2:$C14,3,FALSE).=

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

VLOOKUP(E2,FirstSheet!$A2:$C14,3,FALSE).=

3: فرمول IMPORTRANGE

برای انتقال اطلاعات از یک صفحه گسترده به صفحه دیگر می‌توان از فرمول IMPORTRANGE استفاده کرد. شاید فکر کنید با کپی و جای‌گذاری می‌توان این کار را انجام داد، اما برای برگه‌های طولانی این کار مقداری زمان بر خواهد بود. با این فرمول می‌توان این کار را به‌ راحتی بین فایل‌های  Google Sheets مختلف انجام دهید.

به راحتی با فرمول و مراحل زیر می‌توان این کار را انجام داد:

=IMPORTRANGE(spreadsheet_URL, range_string)

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

IMPORTRANGE(=

2: حالا URL سند صفحه گسترده‌ای را که می‌خواهید از اطلاعات آن استفاده کنید کپی و در فرمول جای‌گذاری کنید:

3: محدوده موردنظر برای واردکردن اطلاعات را انتخاب کنید. Google Sheets به‌صورت پیش‌فرض از اولین صفحه استفاده می‌کند اما می‌توان هر صفحه‌ای را به این منظور انتخاب کرد. اگر داده‌ها در صفحه اول هستند، به‌سادگی محدوده‌ای مثل A2:F100 را وارد کنید. اگر در برگه دیگری است، آن را به‌ صورت  AnotherSheet!A2:F100 وارد کنید.

در اینجا ما برای استخراج دیتای پیش‌بینی سال 2022 از برگه مالی، از فرمول زیر استفاده کرده‌ایم:

=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1uJEkIK6Qn3AibcQc27BXDX5zo_ECJCdccLIiuD6guNw/”,”A299:Q352″)

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

هر زمان که داده‌ها به‌روز شوند، برگه شما نیز به‌روز می‌شود.

4: فرمول IFERROR

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

برای این کار باید فرمول اصلی خود را داخل فرمول IFERROR قرار دهید به‌ صورت زیر:

=IFERROR(original_formula, value_if_error)

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

1: اولین سلولی که از فرمول اصلی خود استفاده می‌کنید را انتخاب کنید.

2: در کادر فرمول/تابع درست بالای داده‌های برگه، IFERROR را اضافه کنید (مستقیماً بعد از علامت =).

3: اولین پارامتر، فرمول خود شماست. برای فرمول‌های پیچیده شاید لازم باشد پرانتزهای اضافی، اضافه کنید.

4: حال نتیجه مورد نظر خود را که قصد دارید به‌ جای پیغام خطا، نمایش داده شود به‌ عنوان پارامتر دوم همراه با پرانتز وارد کنید. در این مثال ما می‌خواهیم سلول خالی باشد، پس به‌ عنوان پارامتر دوم فقط ” ” قرار می‌دهیم.

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

=IFERROR(A2/B2,” “)

5: فرمول ARRAYFORMULA

ARRAYFORMULA یا فرمول‌نویسی آرایه‌ای هم یکی از کاربردی‌ترین فرمول‌های گوگل شیت است به‌ویژه برای مواقعی که با داده‌های بزرگ‌تر سروکار دارید. سرعت گوگل شیت در مواقعی که با فرمول‌های متعدد سروکار دارد، کم می‌شود. برای حل این مشکل می‌توان یک فرمول واحد برای طیف وسیعی از سلول‌ها با استفاده از ARRAYFORMULA ایجاد کرد.

این یک روش سریع‌تر برای انجام محاسبات است و اگر نیاز به تغییر فرمول بود، این کار را با تغییر در ARRAYFORMULA انجام می‌دهید و نیازی به کپی‌ کردن آن در همه سلول‌ها نیست. حتی اگر ردیف دیگری به داده‌های خود اضافه کنید، این فرمول به طور خودکار آنها را هم در بر می‌گیرد.

برای این مثال می‌خواهیم حقوق و پاداش یک کارمند را به شیت اضافه کنیم و جمع آن را در یک ستون جدید قرار دهیم. می‌توانیم فقط از =B2+C2 استفاده کنیم و فرمول را به پایین ستون بکشیم. اما اگر با صدها یا هزاران ستون سروکار داشته باشیم، این کار به‌شدت خسته‌کننده می‌شود. پس از فرمول ARRAYFORMULA استفاده می‌کنیم.

1: اولین سلولی را که می‌خواهید از فرمول استفاده شود، انتخاب کنید. در مثال ما سلول D2 است.

2: حال شروع به نوشتن =ARRAYFORMULA کنید.

3: شروع به نوشتن فرمول کنید. اما به‌ جای یک سلول خاص، محدوده سلولی را بنویسید. به طور مثال در این مثال ما سلول B2 تا B14 را که محدوده سلولی مورد نظر ماست به‌ صورت B2:B14 می‌نویسیم.

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

فرمول نهایی ما برای جمع دو ستون B و C به این صورت بود:  ARRAYFORMULA(B2:B14+C2:C14).= این فرمول‌ها با توجه‌ به نیاز و حرفه‌ای بودن شما، می‌تواند بسیار پیچیده‌تر باشد؛ اما روش کار به همین صورت است. فقط کافی است این کار را در سلول اول انجام دهید تا روی بقیه سلول‌ها هم اعمال شود.

6: فرمول FILTER

داده‌های خود را به‌ راحتی از مسیر “Data -> Create a Filter” می‌توان فیلتر و مرتب‌سازی کرد اما از طریق فرمول FILTER، آسان‌تر می‌توان به نتایج خود رسید. فرمول و پارامترها هم بسیار ساده هستند:

=FILTER(range, condition1, otherconditions)

البته بخش otherconditions در فرمول بالا اختیاری است و اساساً مقایسه درست/ غلط برای فیلتر کردن بیشتر نتایج شما هستند. در ادامه مراحل کار با این فرمول را بررسی می‌کنیم.

1: در یک سلول خالی، فرمول خود را شروع کنید. بهتر است اولین سلول زیرعنوان را برای نوشتن انتخاب کنید. در این مثال ما قصد داریم ID کارمندانی با پرداختی بیشتر از 120000 دلار را فیلتر می‌کنیم.

2: محدوده و شرایط اولیه خود را در فرمول وارد کنید. می‌توانید فرمول را با گذاشتن پرانتز ببندید یا اینکه شرایط بیشتری را تعریف کنید. به‌وسیله کاما این شرایط را از هم جدا کنید. شرایطی که ما برای این مثال تعریف کرده‌ایم فقط انتخاب مقادیر بیشتر از 120000 دلار در محدوده F2:F14 است.

نتایج در زیر سرستون‌های جدید شما نمایش داده می‌شوند (البته اگر وجود داشته باشد).

یک نکته مهم در مورد این فرمول این است که اگر تغییری در داده‌های خود ایجاد کنید، نتایج به‌ صورت خودکار به‌روز می‌شوند.

7: فرمول JOIN

آیا تابه‌حال نیاز داشته‌اید که دو یا چند سلول را به هم متصل یا ترکیب کنید؟ با استفاده از تابع JOIN می‌توان به‌راحتی این کار را انجام داد به‌ویژه وقتی تعداد سلول‌ها زیاد باشد این تابع بسیار کاربردی است. فرمول کلی تابع به‌ صورت زیر است:

=JOIN(delimiter, value or array 1, value or array 2, etc.)

می‌توانید یک سلول تنها یا محدوده‌ای از سلول‌ها را با یک سلول دیگر ترکیب کنید. مراحل کار به‌صورت زیر است:

1: یک سلول خالی را برای نوشتن فرمول انتخاب کنید.

2: JOIN(= را تایپ کنید و کاراکتر جداکننده را بعد از آن بنویسید. جداکننده می‌تواند یک کاما، خط‌فاصله، فضای خالی یا هر چیز دیگری باشد. فقط باید این جداکننده را داخل گیومه قرار دهید مثلاً به‌صورت “,” or “-“.

3: حالا مقادیر آرایه‌های اول و دوم را که به‌وسیله کاما از هم جدا شده‌اند، وارد کنید. فرمول نهایی ما برای ترکیب نام و نام خانوادگی افراد به این صورت است: JOIN(” “,B2,C2)=

با استفاده از ARRAYFORMULA هم می‌توان برای صفحات بزرگ‌تر به این منظور استفاده کرد. این کار را برای همان مثال بالا انجام می‌دهیم:

ARRAYFORMULA(B2:B14 & ” ” & C2:C14)=

در این حالت محدوده‌ای را برای ستون اول اضافه می‌کنید و از علامت (&) برای پیوستن به آنها استفاده می‌کنید. بخش میانی هم برای جداکننده است.

8: فرمول SPLIT

این فرمول کاملاً برعکس فرمول JOIN عمل می‌کند و مواردی که در یک سلول وجود دارد را از هم جدا می‌کند. مثلاً شاید بخواهید مانند مثال بالا نام و نام خانوادگی افراد را از هم جدا کنید تا مرتب‌سازی داده‌ها بر اساس حرف الفبای نام خانوادگی، راحت‌تر باشد.

پارامترهای لازم برای فرمول SPLIT به‌صورت زیر است:

=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

“text” همان متن درج شده در سلول است که می‌خواهید جدا کنید. delimiter هم جداکننده کاراکتری است که برای تعیین محل تقسیم متن استفاده می‌شود. دو پارامتر آخر هم اختیاری هستند.

به دو یا چند سلول خالی نیاز دارید، برای هر قسمت از متن که تقسیم می‌شود. در مثال ما، اسم کامل هر شخص را به نام و نام خانوادگی تقسیم می‌کنیم پس به دوخانه خالی نیاز داریم.

1: در اولین سلول خالی خود، فرمول‌نویسی را با نوشتن  SPLIT(= شروع کنید.

2: سلولی را که می‌خواهید تقسیم کنید، وارد کنید.

3: جداکننده موردنظر خود را وارد کنید. ما از فضای خالی در فرمول خود استفاده کرده‌ایم، پس ” ” را تایپ می‌کنیم. می‌توانید نماد یا هر حرفی که بخواهید، در داخل گیومه قرار دهید.

4: حال فرمول خود را با نوشتن یک پرانتز، ببندید.

جمع‌بندی

گوگل شیت یکی از بهترین و راحت‌ترین برنامه‌های کاربردی است. این برنامه قابلیت‌های مختلفی را در اختیار کاربران قرار می‌دهد و انجام بسیاری از کارهای را برای آنها راحت کرده است. از مهم‌ترین ویژگی‌های گوگل شیت می‌توان به فرمول‌های کاربردی آن اشاره کرد که در این مقاله به بررسی فرمول‌های مهم و پرکاربرد در گوگل شیت و نحوه استفاده آنها پرداختیم.

اگر شما نیز با فرمول‌های دیگر گوگل شیت آشنایی دارید که در این مقاله بررسی نشده، می‌توانید از طریق کامنت‌ها با ما به اشتراک بگذارید.

میان‌تیتر: چند نکته برای یادگیری فرمول‌های گوگل شیت

• با فرمول‌های پایه شروع کنید. فرمول‌های پایه مانند SUM, AVERAGE و IF برای اکثر کاربران ضروی هستند.
• از منابع مختلف استفاده کنید. منابع مختلفی برای یادگیری فرمول‌های گوگل شیت وجود دارد، از جمله مستندات رسمی گوگل شیت، کتاب‌های الکترونیکی و مقالات آموزشی، دوره‌های آموزشی آنلاین و ویدیوهای آموزشی در YouTube.
• تمرین کنید. بهترین راه برای یادگیری فرمول‌های گوگل شیت این است که آنها را تمرین کنید.

سوالات متداول:

آیا می‌توان به‌ جای واردکردن دستی محدوده سطر و ستون‌ها، فقط آنها را های‌لایت کنیم؟

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

چطور می‌توان در مورد فرمول‌ها اطلاعات بیشتری کسب کرد؟

خارج از مثال‌های بالا، Google Sheets توضیحات کامل و عالی در مورد فرمول‌ها نیز ارائه می‌دهد. کافی است به مسیر “Insert -> Function” بروید و تابع فرمول خود را انتخاب کنید. وقتی نام فرمول در سلول ظاهر شد، روی علامت + را که در کنار نام فرمول مشاهده می‌کنید، کلیک کنید. برای مشاهد پارامترها و معنی هرکدام روی فلش کشویی کلیک کنید.

همچنین اگر روی Learn More کلیک کنید، جزئیات بیشتری را از راهنمای گوگل شیت دریافت می‌کنید.

آیا همه فرمول‌های گوگل شیت و مایکروسافت اکسل، قابل تعویض هستند؟

خیر مثلاً فرمول ARRAYFORMULA مختص به گوگل شیت است. اگر می‌خواهید فرمول‌هایی را پیدا کنید که در هر دو کار می‌کند، لیست فرمول‌های هر کدام را بررسی کنید تا از این موضوع مطمئن شوید.