💡 برای استفاده کامل از اکسل ، باید درک خوبی از فرمولهای آن داشته باشید. این نوشتار به معرفی مفاهیم اولیهای میپردازد که برای تسلط بر فرمول نویسی در اکسل باید بدانید.
📖 آنچه در این صفحه می خوانید
#1یکی از مهم ترین دلایل استفاده از اکسل ، قابلیت بالای آن در فرمولنویسی و انجام محاسبات است.
فرمول عبارتی است که یک نتیجه خاص را بر میگرداند (حتی زمانی که نتیجه یک خطا باشد) . مثلا:
= 1 + 2 // (مقدار 3 را باز میگرداند)
= 6 / 3 // (مقدار 2 را باز میگرداند)
در اکسل میتوانید برای نوشتن فرمول در سلول ، روی آن قرار گرفته و سپس مستقیما روی همان سلول یا در قسمت نوار فرمول (FORMULA BAR) شروع به تایپ فرمول کنید.
موقعیت نوار Formula Bar در محیط اکسل — جایی که فرمولها را مینویسید و ویرایش میکنید.
فرمولنویسی در اکسل با علامت = شروع میشود.
یعنی هرگاه بخواهید فرمولی بنویسید ، با تایپ علامت = در یک سلول خالی ، به اکسل تفهیم میکنید که قصد نوشتن فرمول در آن سلول را دارید. پس از این علامت میتوانید فرمول خود را تایپ کنید.
در مثالهای فوق ، مقادیر ، "هاردکد" هستند. این بدان معناست که نتایج تغییر نخواهند کرد ، مگر اینکه دوباره فرمول را ویرایش کنید و مقادیر را به صورت دستی تغییر دهید.
به طور کلی ، این شکل فرمول نویسی بدترین حالت در نظر گرفته میشود ، زیرا اطلاعات را پنهان میکند و نگهداری فایل اکسل را سختتر میکند. در عوض ، میتوان با استفاده از آدرس دهی سلولها ، مقادیر را به آسانی هر زمانی تغییر داد.
هر سلول در اکسل دارای یک نام (آدرس) است که از تلاقی یک سطر و یک ستون تشکیل میشود :
شماره سطر نام ستون = نام سلول
برای نمونه عنوان سلول سطر اول ستون اول یک برگه اکسل به شکل زیر است:
A1
آدرس دهی سلولها در فرمول نویسی — سلول C1 حاوی جمع سه سلول A1، A2 و A3 است.
در مثال فوق ، سلول C1 حاوی فرمول زیر است:
= A1 + A2 + A3 // (مقدار 9 را باز میگرداند)
توجه داشته باشید ، چون از آدرس سلولهای A2 ، A1 و A3 در فرمول استفاده کردهایم ، مقادیر آن را میتوان هر زمان تغییر داد و سلول C1 همچنان نتیجه دقیق را نشان میدهد.
#6فرمولها را کپی و پیست کنید
زیبایی آدرسهای سلول این است که وقتی فرمول حاوی این آدرس ها در مکان (سلول) جدید کپی میشود ، آدرسها به طور خودکار به روز میشود.
این بدان معناست که نیازی نیست بارها و بارها همان فرمول اصلی را تایپ کنید. در مثال زیر فرمول موجود در سلول E1 با استفاده از کلیدهای Ctrl + C در حافظه موقت کپی شده است:
کپی کردن فرمول از سلول E1 با استفاده از کلیدهای Ctrl+C در اکسل
در تصویر زیر فرمول با استفاده از کلیدهای Ctrl + V در سلول E2 جایگذاری شده است. توجه کنید آدرس سلولها تغییر کرده است:
چسباندن فرمول در سلول E2 با Ctrl+V — توجه کنید که آدرس سلولها به طور خودکار تغییر کرده است.
در تصویر زیر فرمول در سلول E3 کپی شده است. آدرس سلولها دوباره به روز می شوند:
کپی مجدد فرمول در سلول E3 — آدرسها دوباره بهروز شدند. این قدرت آدرس نسبی است.
#2آدرس نسبی
به آدرس سلول در مثالهای فوق ، آدرس نسبی گفته میشود. این بدان معنی است که آدرس وابسته به سلولی است که در آن قرار دارد.
مفهوم آدرس نسبی — فرمول سلول E1 جمع سه سلول سمت چپ خود را محاسبه میکند.
فرمول سلول E1 مثال فوق به این صورت است:
= B1 + C1 + D1 // (E1 فرمول سلول)
مفهوم فرمول این است:
"سلول اولین ستون سمت چپ "+ "سلول دومین ستون سمت چپ" + "سلول سومین ستون سمت چپ "=
به همین دلیل است که وقتی فرمول در سلول E2 کپی میشود، به همان روش به کار خود ادامه میدهد.
برای درک بیشتر ، عبارت "خانه همسایه سمت راست" را در نظر بگیرید. شما تنها در صورتی میتوانید آدرس این خانه را پیدا کنید که نقطه شروع را بدانید ، زیرا مکان به صورت نسبی توصیف شده است.
تغییر خودکار آدرس نسبی هنگام کپی — فرمول از C4*D4 به C5*D5 تغییر میکند.
در مثال نشان داده شده ، فرمول سلول E4 شامل دو آدرس نسبی است که با کپی در سلولهای زیرین ستون E به صورت زیر تغییر میکند:
= C4 * D4
= C5 * D5
= C6 * D6
= C7 * D7
= C8 * D8
آدرسهای نسبی بسیار مفید هستند و به طور پیش فرض ، همه آدرسها در فرمولهای اکسل نسبی هستند.
📝 تمرین 1 — (آدرس نسبی) – بودجه ماهانه ساده
سناریو: شما یک بودجه ماهانه برای ۶ ماه دارید. درآمد ثابت ماهانه ۲۰ میلیون تومان است و هزینههای متغیر هر ماه در ردیفهای B2 تا B7 وارد شده است.
جدول بودجه ماهانه
| |
A |
B |
| 1 |
ماه |
هزینه (میلیون) |
| 2 |
فروردین |
12 |
| 3 |
اردیبهشت |
15 |
| 4 |
خرداد |
10 |
| 5 |
تیر |
18 |
| 6 |
مرداد |
14 |
| 7 |
شهریور |
13 |
سوالات:
- در سلول C2 چه فرمولی بنویسید تا مانده باقیمانده (درآمد منهای هزینه) برای فروردین محاسبه شود؟ (درآمد ثابت ۲۰ میلیون را مستقیماً در فرمول وارد کنید)
- اگر فرمول سلول C2 را تا C7 کپی کنید، چه مشکلی ایجاد میشود؟ چرا؟
- این مشکل را چگونه حل میکنید؟ (به تمرین بخش آدرس مطلق مراجعه کنید)
💡 پاسخ تمرین را در بخش «پاسخ تمرینها» ببینید.
#3آدرس مطلق
مواقعی وجود دارد که نمیخواهید آدرس سلول تغییر کند. آدرس سلولی که هنگام کپی تغییر نمیکند، آدرس مطلق نامیده می شود. برخلاف آدرس نسبی ، آدرس مطلق به یک سلول ثابت در برگه اکسل اشاره دارد.
میتوانید با استفاده از نویسه دلار ($) آدرس نسبی را به آدرس مطلق تبدیل کنید.
به عنوان مثال ، آدرس مطلق سلول A1 به شکل زیر است:
= $A$1
آدرس مطلق برای محدوده A1:A10 هم به این صورت است:
= $A$1:$A$10
آدرس مطلق با علامت $ — سلول C2 (نرخ ساعتی) قفل شده و هنگام کپی تغییر نمیکند.
در مثال نشان داده شده ، فرمول سلول D5 با کپی در سلولهای ستون D به شکل زیر تغییر میکند:
= C5 * $C$2
= C6 * $C$2
= C7 * $C$2
= C8 * $C$2
= C9 * $C$2
توجه داشته باشید که آدرس مطلق سلول C2 که نرخ ساعتی را نگه میدارد در سلولهای دیگر تغییر نمیکند ، در حالی که آدرس C5 با هر سطر جدید تغییر میکند.
#5کلید میانبر تغییر آدرس نسبی به مطلق و بلعکس
هنگام وارد کردن فرمولها ، میتوانید از کلیدهای میانبر صفحهکلید زیر برای جابجایی بین گزینههای آدرس نسبی و مطلق بدون تایپ دستی نویسه دلار ($) استفاده کنید.
کلید میانبر F4 — سریعترین راه برای تغییر آدرس نسبی به مطلق و بالعکس.
اگر هنگام درج فرمول بخواهید سلولی را مطلق آدرسدهی کنید ،کافی است نام سلول را بنویسید و بعد دکمه F4 را فشار دهید. مشاهده میکنید که با هربار فشردن این دکمه ، یکی از حالات آدرس دهی روی سلول مورد نظر در فرمول اعمال می شود.
A1 --> $A$ 1--> A$1--> $A1--> A1
بسته به اینکه سطر یا ستون از کدام نوع باشد (نسبی یا مطلق) در کل 4 حالت پیش می آید:
شماره سطر $ نام ستون = نام سلول
شماره سطر نام ستون $ = نام سلول
شماره سطر $ نام ستون $ = نام سلول
شماره سطر نام ستون = نام سلول
استفاده از این کلید میانبر بسیار سریعتر و آسانتر از تایپ دستی نویسه $ است. برای تبدیل آدرسهای نسبی فرمول موجود به آدرسهای مطلق ، وارد حالت ویرایش سلول شوید ، مکان نما را داخل یا کنار آدرسی که میخواهید تبدیل کنید قرار دهید سپس از میانبر استفاده کنید.
📝تمرین 2 (آدرس مطلق) – اصلاح بودجه با سلول مرجع
سناریو (ادامه تمرین 1): عدد ۲۰ (میلیون تومان) را در سلول $E$1 قرار دهید.
سوالات:
- حالا چه فرمولی در C2 باید بنویسید که هنگام کپی شدن تا C7، همیشه به سلول E1 اشاره کند؟
- پس از کپی فرمول، سلول C3 چه شکلی میشود؟
- اگر درآمد ماه دوم به ۲۲ میلیون افزایش یابد و شما فقط سلول E1 را به ۲۲ تغییر دهید، چه اتفاقی میافتد؟
💡 پاسخ تمرین را در بخش «پاسخ تمرینها» ببینید.
#7نحوه ویرایش فرمول
برای ویرایش فرمول ، 3 گزینه دارید:
- سلول را انتخاب کنید، در نوار فرمول ویرایش کنید
- روی سلول دوبار کلیک کنید، مستقیما ویرایش کنید
- سلول را انتخاب کنید ،دکمه F2 صفحه کلید را فشار دهید و مستقیما ویرایش کنید
مهم نیست از کدام گزینه استفاده می کنید ، Enter را فشار دهید تا پس از انجام ویرایش ، تغییرات را تأیید کنید.
اگر میخواهید ویرایش را لغو کنید و فرمول را بدون تغییر رها کنید، روی کلید Esc کلیک کنید.
#4آدرس مختلط
آدرس مختلط آدرسی است که بخشی از آن مطلق و بخشی دیگر نسبی است. به عنوان مثال ، آدرسهای زیر دارای اجزای نسبی و مطلق هستند:
= $A1 // (ستون قفل شده است)
= A$1 // (سطر قفل شده است)
= $A$1:A2 // (اولین سلول قفل شده است)
از آدرسهای مختلط میتوان برای تنظیم فرمولهایی استفاده کرد که بدون نیاز به ویرایش دستی در سطرها یا ستونها کپی شوند. در برخی موارد (نمونه سوم مثال فوق) میتوان از آنها برای ایجاد آدرسی استفاده کرد که هنگام کپی گسترش مییابد.
آدرسهای مختلط یک ویژگی رایج در برگههایی است که به خوبی طراحی شدهاند. تنظیم آنها سخت است اما ورود فرمولها را بسیار آسان میکند. علاوه بر این ، به طور قابل توجهی خطاها را کاهش میدهد ، زیرا امکان میدهد فرمول یکسانی در بسیاری از سلولها بدون ویرایش دستی کپی شود.
آدرس مختلط — ترکیب $C5 (قفل ستون) و E$4 (قفل سطر) برای کپی هوشمند فرمول.
در مثال نشان داده شده فرمول سلول E5 به این صورت است:
= $C5 * (1 – E$4)
این فرمول با دو آدرس مختلط ساخته شده است تا بتوان آن را در محدوده E5:G7 بدون ویرایش دستی کپی کرد. ارجاع به $C5 ستون را قفل میکند تا مطمئن شود که فرمول همچنان که کپی میشود قیمت را از ستون C دریافت میکند. در آدرس E$4 سطر قفل شده است به طوری که با کپی شدن فرمول از سطر 5 به سطر 7 فرمول همچنان مقدار درصد را از سطر 4 انتخاب میکند.
📝تمرین 3 (آدرس مختلط) – جدول ضرب ۵×۵
سناریو: میخواهید یک جدول ضرب از ۱ تا ۵ بسازید. اعداد سطرها در ستون A (A2 تا A6) و اعداد ستونها در ردیف ۱ (B1 تا F1) قرار دارند.
جدول تمرین آدرس مختلط - اکسل
| |
A |
B |
C |
D |
E |
F |
| 1 |
|
1 |
2 |
3 |
4 |
5 |
| 2 |
1 |
? |
|
|
|
|
| 3 |
2 |
|
|
|
|
|
| 4 |
3 |
|
|
|
|
|
| 5 |
4 |
|
|
|
|
|
| 6 |
5 |
|
|
|
|
|
✅ سلول ? (محل تقاطع سطر ۲ و ستون B) همان B2 است.
سوال: یک فرمول در سلول B2 بنویسید که با یک بار نوشتن و سپس کپی کردن به کل محدوده B2:F6، جدول ضرب را کامل کند.
💡 پاسخ تمرین را در بخش «پاسخ تمرینها» ببینید.
#8عملگرهای ریاضی و منطقی
جدول زیر عملگرهای ریاضی استاندارد موجود در اکسل را نشان میدهد:
جدول عملگرهای ریاضی در اکسل — جمع، تفریق، ضرب، تقسیم، توان و درصد.
عملگرهای منطقی از مقایسههایی مانند "بیشتر از"، "کمتر از" و .... پشتیبانی میکنند. عملگرهای منطقی موجود در اکسل در جدول زیر نشان داده شده است:
جدول عملگرهای منطقی در اکسل — مقایسه مقادیر با بزرگتر، کوچکتر، مساوی و نامساوی.
معروفترین کاربرد عملگرهای منطقی در تابع IF است.
مثال ( ترکیب عملگرهای منطقی با تابع IF) : فرض کنید نمره قبولی 12 است. میخواهید اگر نمره در سلول A1 بزرگتر یا مساوی 12 بود، در سلول B1 «قبول» و در غیر این صورت «مردود» نمایش داده شود:
برای این کار در سلول B1 فرمول زیر باید نوشته شود:
= IF ( A1 >= 12 , "مردود" , "قبول" )
#9ترتیب عملیات ها
هنگام محاسبه یک فرمول ، اکسل دنبالهای به نام "ترتیب عملیات" را پی میگیرد.
ابتدا عبارت داخل پرانتز ها ارزیابی میشود. در مرحله بعدی توانها را حل میکند. پس از توان ، ضرب و تقسیم و سپس جمع و تفریق را انجام میدهد. اگر فرمول شامل عملگر الحاق (&) باشد، این عمل پس از عملیات ریاضی استاندارد اتفاق میافتد.
در نهایت ، عملگرهای منطقی را در صورت وجود ارزیابی خواهد کرد.
ترتیب عملیات به صورت زیر است:
1. پرانتز
2. توان
3. ضرب و تقسیم
4. جمع و تفریق
5. عملگر الحاق
6. عملگرهای منطقی
📝#12تمرین 4 (ترتیب عملیات) – محاسبه نمرات با وزنهای متفاوت
سناریو: نمرات یک دانشجو در ۳ درس با وزنهای متفاوت:
جدول نمرات و وزن دروس
| درس |
نمره (سلول) |
وزن (سلول) |
| ریاضی |
18 (B2) |
0.4 (C2) |
| فیزیک |
15 (B3) |
0.35 (C3) |
| شیمی |
17 (B4) |
0.25 (C4) |
سوالات:
1- فرمول محاسبه نمره نهایی وزنی را بنویسید:
(نمره ریاضی × وزن ریاضی) + (نمره فیزیک × وزن فیزیک) + (نمره شیمی × وزن شیمی)
2- اگر فرمول را بدون پرانتز بنویسید = B2*C2 + B3*C3 + B4*C4 آیا جواب درست است؟ چرا؟
3- حالا فرض کنید میخواهید میانگین ساده (بدون وزن) را محاسبه کنید. کدام یک درست است؟
گزینه ۱: = B2+B3+B4 / 3
گزینه ۲: = (B2+B3+B4) / 3
💡 پاسخ تمرین را در بخش «پاسخ تمرینها» ببینید.
📝تمرین 5 (ترکیبی: مختلط + ترتیب عملیات) – محاسبه سود در سناریوهای مختلف
سناریو : یک مهندس صنایع میخواهد سود یک محصول را در ۴ سناریوی قیمت و ۳ سناریوی نرخ مالیات محاسبه کند.
جدول:
- ستون A: سناریوهای قیمت (A2 تا A5) شامل اعداد 100، 150، 200، 250 هزار تومان
- سطر 1: سناریوهای نرخ مالیات (B1 تا D1) شامل 0.1، 0.2، 0.3 (یعنی ۱۰٪، ۲۰٪، ۳۰٪)
- سلول G1: هزینه ساخت ثابت = 50 هزار تومان
فرمول سود:
سود = قیمت × (1 - نرخ مالیات) - هزینه ساخت
سوال:
یک فرمول در سلول B2 بنویسید که پس از کپی شدن در کل محدوده B2:D5، سود را برای همه ترکیبها محاسبه کند.
(راهنمایی: به قفل کردن قیمت (ستون A) و قفل کردن نرخ مالیات (سطر 1) و استفاده از آدرس مطلق برای G1 توجه کنید)
💡 پاسخ تمرین را در بخش «پاسخ تمرینها» ببینید.
📝تمرین 6 (چالش نهایی) – گزارش صورتحساب پیمانکاری
سناریو : یک شرکت پیمانکاری در یک پروژه ساختمانی، مقادیر اجرای ماهانه را در ستون B (B2 تا B13 برای ۱۲ ماه) ثبت کرده است.
نرخ هر واحد کار در سلول $F$1 = ۱.۵ میلیون تومان است.
مالیات بر ارزش افزوده ۹٪ در سلول $F$2 = 0.09 تعریف شده است.
فرمول درآمد هر ماه:
(مقدار اجرا × نرخ واحد) × (1 + مالیات)
سوالات:
- فرمول سلول C2 را بنویسید.
- اگر فرمول را تا C13 کپی کنید، آیا درست کار میکند؟ چرا؟
- چالش ویژه: مدیر پروژه میخواهد بداند اگر نرخ واحد به ۱.۷ میلیون تومان افزایش یابد، کل درآمد سالانه چقدر میشود. کاربر چقدر زمان نیاز دارد تا گزارش جدید را محاسبه کند؟
💡 پاسخ تمرین را در بخش «پاسخ تمرینها» ببینید.
#10فرمولها را به مقدار تبدیل کنید
گاهی اوقات میخواهید از شر فرمولها خلاص شوید و فقط مقادیر را به جای آنها بگذارید.
سادهترین راه در اکسل برای انجام این کار این است که فرمول را کپی کنید، سپس با استفاده از Paste Special > Values پیست کنید.
این کار ، فرمولها را با مقادیری که بر میگردانند بازنویسی میکند. میتوانید از میانبر صفحهکلید Ctrl + V نیز برای چسباندن مقادیر استفاده کنید ، یا از منوی Paste در زبانه HOME روبان اکسل استفاده کنید.
#11جمع بندی
- مرور سریع برای مبتدیان: سه اصل طلایی که امروز یاد گرفتید
اگر این اولین بار است که با فرمولنویسی در اکسل آشنا میشوید، همین سه قانون را به خاطر بسپارید:
- همیشه با «=» شروع کنید: به اکسل بفهمانید که میخواهید محاسبه یا منطقی را اجرا کنید.
- به جای اعداد، از آدرس سلول استفاده کنید: بنویسید =A1+B1 نه =5+3. این کار باعث میشود فایل شما «هوشمند» شود و با تغییر اعداد، نتایج بهروز شوند.
- قبل از کپی کردن، به فکر قفل کردن باشید: اگر نمیخواهید آدرس یک سلول (مثل نرخ ارز، مالیات، تعداد روزهای ماه) هنگام کپی شدن فرمول تغییر کند، با کلید F4 آن را مطلق ($A$1) کنید.
- نکته کلیدی برای کاربران حرفهایتر: جادوی آدرسهای مختلط
فرمولهای قدرتمند معمولاً از ترکیب آدرسهای نسبی و مطلق سود میبرند.
اگر میخواهید با یک بار نوشتن فرمول، کل یک جدول (مثل ماتریس ضرب یا محاسبه سود در سناریوهای مختلف) را پر کنید، از آدرسهای مختلط ($A1 یا A$1) استفاده کنید. به خاطر داشته باشید:
$A1 یعنی: ستون قفل است (هنگام کپی به راست تغییر نمیکند)، سطر آزاد است.
A$1 یعنی: سطر قفل است (هنگام کپی به پایین تغییر نمیکند)، ستون آزاد است.
حرف آخر:
فرمولنویسی در اکسل یک زبان ساده برای صحبت کردن با دادههاست. تسلط بر مفاهیم «آدرس دهی» (نسبی، مطلق، مختلط) و «ترتیب عملیات» شما را از یک کاربر معمولی به یک تحلیلگر داده تبدیل میکند. از همین امروز با ساختن مثالهای کوچک و شخصی (مثل بودجه ماهانه یا محاسبه نمرات) تمرین کنید.
💡 اگر در اجرای هر کدام از توابع و تمرینها به مشکل خوردید یا سوالی دارید، در بخش دیدگاه مطرح کنید. پاسخ شما ظرف ۲۴ ساعت داده میشود.
📋 #13پاسخ تمرینها
(برای مشاهده پاسخ هر تمرین، روی آن کلیک کنید)
پاسخ تمرین 1 — آدرس نسبی (بودجه ماهانه) +
= 20 - B2
مشکل: در سلول C3 فرمول میشود = 20 - B3 (صحیح است) اما اگر درآمد ثابت در سلول جداگانهای نبود، تغییر آن در همه جا سخت میشد.
راه حل: با قرار دادن عدد ۲۰ در یک سلول جداگانه (مثلاً E1) و استفاده از آدرس مطلق.
پاسخ تمرین 2 — آدرس مطلق (اصلاح بودجه) +
= $E$1 - B2
= $E$1 - B3 (آدرس E1 ثابت میماند)
تمام محاسبات بودجه در ستون C بهطور خودکار با عدد جدید بهروز میشوند.
پاسخ تمرین 3 — آدرس مختلط (جدول ضرب) +
فرمول صحیح در B2: = $A2 * B$1
• $A2: ستون A ثابت (مطلق)، سطر نسبی (با کپی به پایین تغییر میکند)
• B$1: سطر ۱ ثابت (مطلق)، ستون نسبی (با کپی به راست تغییر میکند)
پاسخ تمرین 4 — ترتیب عملیات (نمرات وزنی) +
= (B2*C2) + (B3*C3) + (B4*C4) (پرانتزها اضافی ولی بیاشکال هستند)
بله، چون عملگر * اولویت بالاتری نسبت به + دارد و اکسل ابتدا همه ضربها و سپس جمع را انجام میدهد.
گزینه ۲ درست است: = (B2+B3+B4) / 3 (بدون پرانتز، ابتدا تقسیم انجام میشود)
پاسخ تمرین 5 — مختلط + ترتیب عملیات +
فرمول در B2: = $A2 * (1 - B$1) - $G$1
• $A2: قیمت (ستون ثابت، سطر نسبی)
• B$1: نرخ مالیات (سطر ثابت، ستون نسبی)
• $G$1: هزینه ثابت (کاملاً مطلق)
پاسخ تمرین 6 — چالش نهایی +
= B2 * $F$1 * (1 + $F$2)
بله، زیرا $F$1 و $F$2 مطلق هستند و B2 نسبی است.
کمتر از ۵ ثانیه فقط کافی است سلول F1 را از 1.5 به 1.7 تغییر دهید. بقیه فرمولها خودکار بهروز میشوند.
📘
آموزش اکسل از صفر تا صد
۲۸ مقاله تخصصی | دستهبندی شده از مبتدی تا پیشرفته | بهروزرسانی ماهانه
مشاهده همه آموزشها
✅ رایگان | دسترسی فوری | بدون نیاز به ثبتنام