نویسنده
درباره نویسنده
بهنام خیرالهی
تعداد پست ها 53

درباره:

دانش آموخته مقطع کارشناسی دانشگاه Staffordshire University در رشته علوم کامپیوتر گرایش امنیت و مقطع کارشناسی دانشگاه Asia Pacific University در رشته فناوری اطلاعات هستم. از سال ۱۳۹۲ به ایران برگشتم و به صورت تخصصی در زمینه طراحی وب، طراحی گرافیک و برنامه نویسی NET. فعالیت دارم.

  • آدرس‌دهی سلول‌ها

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

فرمول‌ها و فرمول‌نویسی

در اکسل فرمول‌ها با علامت مساوی «=» شروع می‌شوند و هر فرمول می‌تواند یک یا چند قسمت داشته باشد. ثابت‌ها، آدرس‌ها (متغییرها)، عملگرها و توابع اجزای یک فرمول را تشکیل می‌دهند.

ثابت‌ها

ثابت‌ها مقادیری هستند که تغییر نمی‌کنند. مثل عدد ۵ یا عبارت «درآمد». اگر بخواهیم مقدار عدد ۱۰ را به جای ۵ استفاده کنیم، باید عدد را در فرمول بازنویسی کنیم.

آدرس‌ها (متغییرها)

آدرس‌ها (متغییرها) به آدرس سلولی گفته می‌شود که مقدار آن در فرمول استفاده می‌شود. مثلا می‌توانیم مقدار عدد ۵ را در سلول A1 وارد کنیم و از آدرس A1 در فرمول استفاده کنیم. در این صورت به جای بازنویسی فرمول فقط کافی است که مقدار سلول A1 را به مقدار ۱۰ تغییر دهیم.

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

آدرس‌دهی سطر و ستون

استفاده از ترکیب حروف برای نمایش ستون و عدد برای نمایش سطر است. حروف از A شروع شده و تا XFD ادامه پیدا می‌کنند، یعنی تعداد ۱۶،۳۸۴ ستون در اکسل وجود دارد. اعداد سطر نیز از ۱ شروع شده و تا ۱،۰۴۸،۵۷۶ ادامه پیدا می‌کنند.

#آدرس‌دهیمثال
۱سلول ستون A و سطر ۱A1
۲محدوده سلول‌های ستون A، از سطر ۱ تا سطر ۱۰A1:A10
۳محدوده سلول‌های سطر ۵، از ستون A تا ستون BA5:B5
۴تمام سلول‌های سطر ۱۰، شامل تمامی ستون‌ها10:10
۵تمام سلول‌های سطرهای ۵ تا ۱۰، شامل تمامی ستون‌ها5:10
۶تمام سلول‌های ستون AA:A
۷تمام سلول‌های ستون A تا F، شامل تمامی سطرهاA:F
۸محدوده سلول‌های ستون‌های A تا F و سطرهای ۵ تا ۱۰A5:F10

عملگر‌های آدرس سلول‌ها

آدرس‌دهی به شیت‌های دیگر

همانطور که در فرمول زیر می‌بینید، از شیت Sheet2 محدوده سلول‌های B1:B5 آدرس‌دهی شده است. علامت جداساز بین نام شیت و محدوده سلول‌های آن، علامت تعجب «!» است.

=AVERAGE(Sheet2!B1:B5)

عملگر‌ها

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

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

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

#عملگرشرح
۱%درصد
۲^توان‌رسانی
۳*ضرب
۴/تقسیم
۵+جمع
۶تفریق

جدول ۱ − عملگر‌های محاسبات ریاضی

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

مثال

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

=B3*4/B4^2+(A2-A5+2)

مراحل انجام محاسبات به صورت زیر است.

  • ابتدا مقدار داخل پرانتز محاسبه می‌شود.
  • مقدار A5 از A2 کم شده و سپس با ۲ جمع می‌شود.
  • مقدار B4 به توان ۲ می‌رسد.
  • مقدار B3 در عدد ۴ ضرب می‌شود.
  • حاصل ضرب تقسیم بر حاصل توان می‌شود.
  • حاصل تقسیم با حاصل پرانتز جمع می‌شود.

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

عملگرهای رشته‌ای

تنها عملگر رشته‌ای، عملگر & می‌باشد. می‌توانیم از این عملگر برای ترکیب رشته‌ها استفاده کنیم. اگر بخواهیم در فرمول از مقدارهای رشته‌ای استفاده کنیم باید آنها را بین دو علامت "" قرار دهیم.

مثال

فرض کنید میخواهیم مقدار سلول A1 و B1 را ترکیب کرده و نتیجه را با یک فاصله در سلول C1 نشان دهیم. برای این کار فرمول زیر را در سلول C1 تعریف می‌کنیم.

=A1 & " " & B1

تصویر ۱ − فرمول دارای عملگر رشته‌ای

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

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

#عملگرشرح
۱=مساوی با
۲>بزرگتر از
۳<کوچکتر از
۴>=بزرگتر از یا مساوی با
۵<=کوچکتر از یا مساوی با
۶<>نامساوی با

جدول ۲ − عملگر‌های مقایسه‌ای

عملگرهای آدرس سلول‌ها

برای تعیین محدوده‌ای از سلول‌ها، می‌توانیم از عملگر آدرس استفاده کنیم. اولویت عملگرهای آدرس از تمام عملگرهای دیگر بالاتر است. در اکسل ۳ حالت آدرس‌دهی محدوده وجود دارد.

  • آدرس‌دهی محدوده‌ای از سلول‌ها (A1:F5)
  • آدرس‌دهی ستونی (A1:F5)
  • آدرس‌دهی سطری (A1:F1)
#عملگرشرحمثال
۱:عملگر محدوده‌ای است که یک آدرس به تمام سلول‌های بین دو آدرس اشاره می‌کند.A1:A5
۲,عملگر ترکیب دو محدوده‌ای است که به تمام سلول‌های هر دو محدوده اشاره می‌کند.SUM(A1:A5,B1:B5)
۳فضای خالیعملگر اشتراک محدوده‌ها می‌باشد که به تمام سلول‌های مشترک دو محدوده اشاره می‌کند.A1:A5 B1:B5

جدول ۳ − عملگر‌های آدرس سلول‌ها

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

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

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

مثال

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

=2+5*3
=(2+5)*3

در فرمول اول، ابتدا ضرب بین ۵ و ۳ محاسبه می‌شود که نتیجه آن ۱۵ است، سپس نتیجه آن با عدد ۲ جمع می‌شود و عدد ۱۷ به عنوان نتیجه فرمول محاسبه می‌شود.

در فرمول دوم، ابتدا عبارت داخل پرانتز محاسبه می‌شود. داخل پرانتز فقط عملگر جمع بوده و نتیجه جمع دو عدد ۲ و ۵، عدد ۷ خواهد بود، سپس نتیجه آن در عدد ۳ ضرب می‌شود و عدد ۲۱ به عنوان نتیجه فرمول محاسبه می‌شود.

تابع

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

اجزای تابع

هر تابع داری ۲ قسمت اصلی است، نام تابع و آرگومان‌های تابع.

نام تابع کلید واژه‌ای است که نوع عملیات را نشان می‌دهد و در مواردی می‌تواند مخفف کلماتی باشد. آرگومان تابع نیز ورودی یا ورودی‌های تابع است. آرگومان‌ها با یک جداکننده که عموما نقطه ویرگول «;» یا ویرگول «,» می‌باشد از یکدیگر جدا می‌شوند، که در هر سیستم می‌تواند متفاوت ظاهر شود. برای تغییر این جداکننده می‌توانیم به مسیر زیر برویم.

  • Control Panel
  • Region
  • Formats
  • Additional Settings
  • Numbers
  • List Separator

توابع از نظر آرگومان‌هایشان به ۳ دسته تقسیم می‌شوند.

  • فاقد آرگومان
  • دارای تعدادی آرگومان مشخص
  • دارای حداکثر ۲۵۵ آرگومان

تابع فاقد آرگومان

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

RAND()

تابع دارای آرگومان مشخص

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

MOD(n,m)

تابع دارای حداکثر ۲۵۵ آرگومان

تابعی مانند تابع SUM می‌تواند ۲۵۵ ورودی داشته باشد. به عنوان مثال، برای جمع مقدار سلول‌های A1 تا A7 می‌توان حالت‌های زیر را در نظر گرفت.

=SUM(A1:A7)
=SUM(A1:A3, A4:A7)
=SUM(A1:A2, A3:A5, A6:A7)

آرگومان می‌تواند دارای یک سلول یا محدوده‌ای از سلول‌ها باشد، بنابراین ۲۵۵ آرگومان به معنی ۲۵۵ سلول نیست.

بعضی آرگومان‌ها اجباری هستند و باید در تابع تخصیص داده شوند، در غیر اینصورت تابع با خطا مواجه خواهد شد. اما بعضی آرگومان‌ها در بعضی توابع اختیاری هستند و می‌توانیم آنها را در تابع اختصاص ندهیم

بعضی آرگومان‌ها در تابع، درون کروشه «[]» نشان داده می‌شوند، که نشان دهنده این است که این آرگومان اختیاری است. بقیه آرگومان‌ها که در درون کروشه «[]» نیستند، اجباری بوده و باید در تابع تخصیص داده شوند.

تعریف تابع

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

تایپ تابع

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

جدول ۳ − عملگر‌های آدرس سلول‌ها

استفاده از Insert Function

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

جدول ۳ − عملگر‌های آدرس سلول‌ها

ترکیب توابع

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

=IF(AVERAGE(F2:F5)>50, SUM(G2:G5), 0)

دو تابع AVERAGE و SUM هر در یک سطح (سطح دوم) قرار دارند، یعنی هر دو آرگومان‌های تابع IF هستند. اگر تابعی را به جای آرگومان تابع AVERAGE یا SUM قرار دهیم، تابع سطح سوم خواهد شد.