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

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

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

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

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

فرمول نویسی در اکسل: مرور کلی

  • یک سلول را انتخاب کنید.
  • برای وارد کردن علامت مساوی، روی سلول کلیک کرده و = را تایپ کنید.
  • آدرس یک سلول را در سلول انتخاب شده وارد کنید یا یک سلول را از لیست انتخاب کنید.
  • باید یک اپراتور وارد کنید.
  • آدرس سلول بعدی را در سلول انتخاب شده وارد کنید.
  • Enter را فشار دهید.

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

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

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

Sample Formula: “=A2*B2” 

فرمول نویسی در اکسل

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

Sample Formula: “=PRODUCT(A2,B2)” 

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

فرمول نویسی در اکسل

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

در این مقاله 20 فرمول اکسل را بر اساس عملکرد آنها دسته‌بندی کرده‌ایم. بیایید با اولین فرمول نویسی در اکسل شروع کنیم.

فرمول نویسی در اکسل #1 :جمع  Sum

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

Sum “=SUM(C2:C4)” 

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

فرمول نویسی در اکسل #2: میانگین AVERAGE

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

AVERAGE =AVERAGE(C2, C3, C4)

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

فرمول نویسی در اکسل #3: شمارش COUNT

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

COUNT =COUNT(C1:C4)

همانطور که در بالا مشاهده شد، در اینجا، ما از C1 تا  C4، در حالت ایده آل، چهار سلول را می‌شماریم. اما از آنجایی که تابع COUNT تنها سلول‌های دارای مقادیر عددی را در نظر می‌گیرد، پاسخ 3 است زیرا سلول حاوی «فروش کل یا total sales» در اینجا حذف شده است.

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

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

فرمول نویسی در اکسل #4: جمع جزء SUBTOTAL

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

در مثال بالا، ما محاسبه جمع جزء را بر روی سلول های A2 تا A4 انجام داده‌ایم. همانطور که می‌بینید، تابع استفاده شده به شکل زیر است:

SUBTOTAL =SUBTOTAL(1, A2: A4)

در فهرست subtotal، “1” به میانگین اشاره دارد. از این رو تابع بالا میانگین A2: A4 را می‌دهد و پاسخ آن 11 است که در C5 ذخیره می‌شود. به همین ترتیب،

“=SUBTOTAL(4, A2: A4)”

این تابع سلول را با حداکثر مقدار از A2 تا A4 انتخاب می‌کند که 12 است. گنجاندن “4” در تابع حداکثر نتیجه را ارائه می‌دهد.

فرمول نویسی در اکسل #5:  MODULUS

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

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

        MODULUS =MOD(A2,3)

نتیجه در B2 ذخیره می‌شود. همچنین می‌توانیم مستقیماً “=MOD(10,3)” را تایپ کنیم زیرا همان پاسخ را می‌دهد.

به همین ترتیب، در اینجا 12 را بر 4 تقسیم کرده‌ایم. باقیمانده 0 است که در B3 ذخیره می‎شود.

فرمول نویسی در اکسل #6: قدرت Power

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

همانطور که در بالا می‌بینید، برای یافتن توان 10 ذخیره شده در A2 به 3، باید تایپ کنیم:

Power =POWER (A2,3)

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

فرمول نویسی در اکسل #7: سقف CEILING

تابع CEILING() عددی را تا نزدیکترین مضرب آن گرد می‌کند.

نزدیکترین مضرب 5 برای 35.316، 40 است.

فرمول نویسی در اکسل #8: کف FLOOR

برخلاف تابع سقف، تابع کف عددی را تا نزدیکترین مضرب آن به سمت پایین گرد می‌کند.

نزدیکترین مضرب پایین 5 برای 35.316، 35 است.

فرمول نویسی در اکسل #9:  الحاق CONCATENATE

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

در این مثال، ما با این فرمول پیش رفتیم:

  CONCATENATE  =CONCATENATE(A25, ” “, B25)

و در این مثال، فرمول را کمی تغییر دادیم:

        “=CONCATENATE(A27&” “&B27)”

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

فرمول نویسی در اکسل #10: LEN

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

فرمول نویسی در اکسل #11:  REPLACE

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

سینتکس آن به شکل زیر است:

   “=REPLACE( ،متن-قدیمی start_num، num_charsمتن جدید،  )”

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

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

در اینجا، می‌خواهیم A101 را با B101 جایگزین کنیم:

      REPLACE =REPLACE(A15,1,1,”B”)

سپس، A102  را با A2102 جایگزین می‌کنیم:

        “=REPLACE(A16,1,1, “A2”)” 

در نهایت، Adam  را با Saam جایگزین می‌کنیم:

        “=REPLACE(A17,1,2, “Sa”)” 

فرمول نویسی در اکسل #12: جایگزین SUBSTITUTE

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

و به این شکل نوشته می‌شود:

 “=SUBSTITUTE( ،متن-جدید ،متن-قدیمی ،متن [instance_num])” .
در اینجا، [instance_num به موقعیت نمایه متون حاضر بیش از یک بار اشاره دارد.

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

در اینجا، با تایپ: “I like” را با “He likes” جایگزین می‌کنیم.

        “=SUBSTITUTE(A20, “I like”,”He likes”)” 

در مرحله بعد، با تایپ کردن “=SUBSTITUTE(A21,2010, 2016,2) دومین 2010 را که در متن اصلی در سلول A21 وجود دارد با 2016 جایگزین می‌کنیم.

اکنون، با تایپ “=SUBSTITUTE(A22,2010,2016) هر دو نسخه 2010 را در متن اصلی با 2016 جایگزین می‌کنیم.

فرمول نویسی در اکسل #13: LEFT, RIGHT, MID

تابع LEFT() تعداد کاراکترها را از ابتدای یک رشته متنی نشان می‌دهد. در همین حال، تابع MID() کاراکترها را از وسط یک رشته متن، با توجه به موقعیت و طول شروع باز می‌گرداند. در نهایت، تابع right()  تعداد کاراکترهای انتهای یک رشته متن را برمی‌گرداند.

بیایید با چند مثال این توابع را درک کنیم.

در مثال زیر، از تابع چپ برای به دست آوردن سمت چپ‌ترین کلمه جمله در سلول A5 استفاده می‌کنیم:

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

در اینجا هم ما یک مثال از تابع راست داریم.

فرمول نویسی در اکسل #14: UPPER, LOWER, PROPER

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

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

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

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

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

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

فرمول نویسی در اکسل #15: NOW()

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

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

فرمول نویسی در اکسل #16: TODAY()

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

تابع DAY() برای برگرداندن روزِ ماه استفاده می‌شود و عددی بین 1 تا 31 خواهد بود. 1 اولین روز ماه است، 31 آخرین روز ماه است.

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

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

فرمول نویسی در اکسل #17: TIME()

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

فرمول نویسی در اکسل #18: HOUR, MINUTE, SECOND

تابع HOUR () ساعت را از یک مقدار زمانی به عنوان عددی از 0 تا 23 تولید می‌کند. در اینجا 0 به معنای 12 AM و 23، 11 PM است.

تابع  MINUTE()، دقیقه را از یک مقدار زمانی به عنوان عددی از 0 تا 59 برمی‌گرداند.

تابع  SECOND()، ثانیه را از یک مقدار زمانی به عنوان عددی از 0 تا 59 برمی‌گرداند.

فرمول نویسی در اکسل #19: DATEDIF

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

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

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

فرمول نویسی در اکسل #20:  VLOOKUP

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

در زیر مقادیر تابع VLOOKUP آمده است:

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

جدول – جدولی را نشان می‌دهد که مقدار از آن بازیابی می‌شود.

 col_index  – ستون موجود در جدول از مقدار باید بازیابی شود.

range_lookup اختیاری] TRUE = مطابقت تقریبی (پیش‌فرض)، مطابقت دقیق FALSE = .


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

اگر می‌خواهید دپارتمانی را که استوارت به آن تعلق دارد پیدا کنید، می‌توانید از تابع VLOOKUP مانند شکل زیر استفاده کنید:

در اینجا، سلول A11 دارای مقدار جستجو است، A2: E7  آرایه جدول، 3 شماره فهرست ستون با اطلاعات مربوط به دپارتمان‌ها، و 0 جستجوی محدوده است.

اگر Enter را فشار دهید، «Marketing»  را برمی‌گرداند که نشان می‌دهد استوارت از دپارتمان بازاریابی است.

نتیجه

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

به دنبال پیشرفت شغلی خود در علم داده یا تجزیه و تحلیل تجاری هستید؟ برنامه تحصیلات تکمیلی Caltech Simplilearn در علوم داده و تحلیلگر کسب و کار می تواند به شما در دستیابی به اهدافتان کمک کند! این برنامه های جامع برای ارائه مهارت ها و دانش مورد نیاز برای برتری در این زمینه طراحی شده اند. این دوره ها که توسط کارشناسان صنعت هدایت می شوند، آموزش عملی، پروژه های دنیای واقعی و مربیگری شخصی را ارائه می دهند. به علاوه، پس از اتمام، گواهینامه معتبری از یکی از برترین دانشگاه های جهان دریافت خواهید کرد. منتظر نباشید، همین الان ثبت نام کنید و حرفه خود را به سطح بعدی ببرید!

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

از همین نویسنده: