۱۸ فرمول برتر اکسل که باید بدانید (با مثال)

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

یکی از ویژگی هایی که به اکسل اجازه می دهد متمایز شود – فرمول های اکسل است. در این مقاله، توابع و فرمول های مختلف مایکروسافت اکسل را مورد بحث قرار خواهیم داد. این فرمول ها و توابع شما را قادر می سازد تا محاسبات و تجزیه و تحلیل داده ها را سریعتر انجام دهید. در اینجا، ما ۱۸ فرمول برتر اکسل را که باید هنگام کار بر روی اکسل بدانیم، بررسی خواهیم کرد.

فرمول اکسل چیست؟

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

اصطلاح دیگری نیز وجود دارد که برای فرمول های اکسل بسیار آشناست و آن «تابع» است. دو کلمه “فرمول formulas” و “توابع functions” گاهی اوقات قابل تعویض هستند. آنها نزدیک به هم هستند، اما در عین حال متفاوت هستند. یک فرمول با علامت مساوی شروع می شود. در همین حال، از توابع برای انجام محاسبات پیچیده استفاده می شود که به صورت دستی قابل انجام نیستند. توابع در اکسل نام هایی دارند که کاربرد مورد نظر آنها را نشان می دهد.

مثال زیر نشان می دهد که چگونه از فرمول ضرب به صورت دستی با عملگر “*” استفاده کرده ایم.

این مثال زیر نشان می دهد که چگونه از تابع “PRODUCT” برای انجام ضرب استفاده کرده ایم. همانطور که می بینید، ما در اینجا از عملگر ریاضی استفاده نکردیم.

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

فرمول ها و توابع اکسل

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

اکنون به ۱۸ فرمول برتر اکسل که باید بدانید نگاه می کنیم. در این مقاله ۱۸ فرمول اکسل را بر اساس عملکرد آنها دسته بندی کرده ایم. بیایید با اولین فرمول اکسل در لیست خود شروع کنیم.

۱٫ SUM

تابع SUM() همانطور که از نام آن پیداست، مجموع محدوده انتخاب شده از مقادیر سلول را نشان می دهد. عملیات ریاضی که جمع است را انجام می دهد. در زیر نمونه ای از آن آورده شده است:

همانطور که در بالا می بینید، برای یافتن مقدار کل فروش برای هر واحد، باید به سادگی تابع “=SUM(C2:C4)” را تایپ کنیم. این به طور خودکار ۳۰۰، ۳۸۵ و ۴۸۰ را جمع می کند. نتیجه در C5 ذخیره می شود.

۲٫ AVERAGE

تابع AVERAGE() بر محاسبه میانگین محدوده انتخاب شده از مقادیر سلول تمرکز می کند. همانطور که در مثال زیر مشاهده می شود، برای یافتن میانگین کل فروش، باید به سادگی عبارت AVERAGE (C2, C3, C4) را تایپ کنید.

این تابع به طور خودکار میانگین را محاسبه می کند و شما می توانید نتیجه را در مکان مورد نظر خود ذخیره کنید.

۳٫ COUNT

تابع COUNT() تعداد کل سلول ها را در محدوده ای که شامل یک عدد است می شمارد. شامل سلول‌هایی که خالی است و آنهایی که داده‌ها را به غیر از عددی در قالب دیگری نگهداری می‌کنند، نمی‌شود.

همانطور که در بالا مشاهده شد، در اینجا، ما از C1 تا C4، در حالت ایده آل، چهار سلول را می شماریم. اما از آنجایی که تابع COUNT تنها سلول‌های دارای مقادیر عددی را در نظر می‌گیرد، پاسخ ۳ است زیرا سلول حاوی “Total Sales” در اینجا حذف شده است.

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

برای شمارش تعداد سلول‌های خالی موجود در محدوده‌ای از سلول‌ها، از COUNTBLANK() استفاده می‌شود.

۴٫ SUBTOTAL

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

در مثال بالا، ما محاسبه subtotal را بر روی سلول های A2 تا A4 انجام داده ایم. همانطور که می بینید، تابع استفاده شده “=SUBTOTAL(1، A2: A4) است، در لیست زیر مجموع “۱” به میانگین اشاره دارد. از این رو تابع بالا میانگین A2: A4 را می دهد و پاسخ آن ۱۱ است که در C5 ذخیره می شود.

به طور مشابه، “=SUBTOTAL(4، A2: A4)” سلولی را با حداکثر مقدار از A2 تا A4 که ۱۲ است انتخاب می کند. گنجاندن “۴” در تابع حداکثر نتیجه را ارائه می دهد.

۵٫ MODULUS

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

در مثال اول، ۱۰ را بر ۳ تقسیم کرده ایم. باقیمانده با استفاده از تابع “=MOD(A2,3)” محاسبه می شود. نتیجه در B2 ذخیره می شود. ما همچنین می توانیم مستقیماً “=MOD(10,3)” را تایپ کنیم زیرا همان پاسخ را می دهد.

به همین ترتیب، در اینجا ۱۲ را بر ۴ تقسیم کرده ایم. باقیمانده ۰ است که در B3 ذخیره می شود.

۶٫ POWER

تابع “Power()” نتیجه یک عدد افزایش یافته به یک توان خاص را برمی گرداند. بیایید به نمونه هایی که در زیر نشان داده شده است نگاهی بیندازیم:

همانطور که در بالا می بینید، برای یافتن توان ۱۰ ذخیره شده در A2 به ۳، باید “= POWER (A2,3)” را تایپ کنیم. تابع توان در اکسل به این صورت است.

۷٫ CEILING

بعد، ما تابع سقف یا ceiling را داریم. تابع CEILING() عددی را تا نزدیکترین مضرب آن گرد می کند.

نزدیکترین مضرب ۵ برای ۳۵٫۳۱۶ عدد ۴۰ است.

۸٫ FLOOR

برخلاف تابع CEILING، تابع کف یا FLOOR عددی را تا نزدیکترین مضرب گرد می کند.

نزدیکترین مضرب ۵ برای ۳۵٫۳۱۶ عدد ۳۵ است.

۹٫ CONCATENATE

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

در این مثال، ما با =CONCATENATE(A25, ” “, B25) کار کرده ایم.

در این مثال، ما با =CONCATENATE(A27&” “&B27) کار کرده ایم.

این دو راه برای اجرای عملیات الحاق یا CONCATENATE در اکسل بود.

۱۰٫ LEN

تابع LEN() تعداد کل کاراکترهای یک رشته را برمی گرداند. بنابراین، کاراکترهای کلی، از جمله فضاها و کاراکترهای خاص را شمارش می کند. در زیر مثالی از تابع Len آورده شده است.

۱۱٫ REPLACE

همانطور که از نام آن پیداست، تابع REPLACE() روی جایگزینی بخشی از یک رشته متنی با یک رشته متن متفاوت کار می کند.

نحوه کار عبارت است از : “=REPLACE(old_text, start_num, num_chars, new_text)” است. در اینجا start_num به موقعیت شاخصی اشاره دارد که می‌خواهید شروع به جایگزینی کاراکترها کنید. در مرحله بعد، num_chars تعداد کاراکترهایی را که می خواهید جایگزین کنید، نشان می دهد.

بیایید نگاهی به راه هایی که می توانیم از این تابع استفاده کنیم بیاندازیم.

در اینجا، ما A101 را با B101 با تایپ “=REPLACE(A15,1,1,1,B”) جایگزین می کنیم.

سپس، با تایپ “=REPLACE(A16,1,1, “A2”) A102 را با A2102 جایگزین می کنیم.

در نهایت، با تایپ “=REPLACE(A17,1,2, “Sa”)” عبارت adam را با Saam جایگزین می کنیم.

۱۲٫ SUBSTITUTE

تابع ()SUBSTITUTE متن موجود را با یک متن جدید در یک رشته متن جایگزین می کند.

نحوه کار عبارت است از : “=SUBSTITUTE(text, old_text, new_text, [instance_num])” است.

در زیر چند نمونه از این تابع آورده شده است :

در اینجا، با تایپ “I like” عبارت “He likes” را با تابع “=SUBSTITUTE(A20, “I like”,”He likes”)” جایگزین می کنیم.

سپس، با تایپ کردن «=SUBSTITUTE(A21,2010, 2016,2) دومین ۲۰۱۰ را که در متن اصلی در سلول A21 وجود دارد با ۲۰۱۶ جایگزین می کنیم.

۱۳٫ UPPER, LOWER, PROPER

تابع ()UPPER هر رشته متنی را به حروف بزرگ تبدیل می کند. در مقابل، تابع LOWER() هر رشته متنی را به حروف کوچک تبدیل می کند. تابع PROPER() هر رشته متنی را به حروف مناسب تبدیل می کند، یعنی حرف اول هر کلمه با حروف بزرگ و بقیه با حروف کوچک خواهد بود.

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

در اینجا، متن A6 را به یک بزرگ بزرگ در A7 تبدیل کرده ایم.

اکنون، همانطور که در A7 مشاهده می شود، متن A6 را به حروف کوچک تبدیل کرده ایم.

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

۱۴٫ NOW()

تابع NOW() در اکسل تاریخ و زمان فعلی سیستم را نشان می دهد.

۱۵٫ TODAY()

تابع TODAY() در اکسل تاریخ فعلی سیستم را ارائه می دهد.

تابع DAY() برای برگرداندن روز ماه استفاده می شود. عددی بین ۱ تا ۳۱ خواهد بود که ۱ اولین روز ماه است، ۳۱ آخرین روز ماه است.

تابع MONTH() ماه را برمی گرداند، عددی از ۱ تا ۱۲، که در آن ۱ ژانویه و ۱۲ دسامبر است.

تابع YEAR() همانطور که از نام آن پیداست، سال را از مقدار تاریخ برمی گرداند.

۱۶٫ TIME()

تابع TIME () ساعت، دقیقه، ثانیه داده شده به عنوان اعداد را به یک شماره سریال اکسل، فرمت شده با فرمت زمان تبدیل می کند.

۱۷٫ HOUR, MINUTE, SECOND

تابع HOUR() ساعت را از یک مقدار زمانی به عنوان عددی از ۰ تا ۲۳ تولید می کند. در اینجا ۰ به معنای ۱۲ AM و ۲۳ به معنای ۱۱ PM است.

تابع MINUTE()، دقیقه را از مقدار زمانی به صورت عددی از ۰ تا ۵۹ برمی گرداند.

تابع SECOND() دوم را از یک مقدار زمانی به عنوان عددی از ۰ تا ۵۹ برمی گرداند.

۱۸٫ DATEDIF

تابع DATEDIF() تفاوت بین دو تاریخ را بر حسب سال، ماه یا روز ارائه می کند.

در زیر نمونه ای از یک تابع DATEDIF آورده شده است که در آن ما سن فعلی یک فرد را بر اساس دو تاریخ معین، تاریخ تولد و تاریخ امروز محاسبه می کنیم.

 

نتیجه گیری

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

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