فرمول‌نویسی و توابع در اکسل

« مدیریت ورک‌شیت‌ها رسم نمودار »

---

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

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

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

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

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

فرمول متنی دارد و برای این‌که تمایز آن با داده مشخص باشد، همیشه با = شروع می‌شود؛ یعنی هرچه در سلولی وارد شده‌باشد و با علامت = شروع شده باشد، فرمول و در غیر این صورت داده در نظر گرفته می‌شود (این مسئله تبصره‌ای دارد که در ادامه در قسمت بیشتر بدانید توضیح داده شده‌است).

به شکل‌های زیر توجه کنید.

عبارت شکل سمت راست داده است، زیرا با علامت = شروع نشده‌است. عبارت شکل سمت چپ فرمول است؛ وقتی روی سلولی که فرمول دارد کلیک کنید، متن فرمول آن در نوار فرمول نشان داده می‌شود؛ در خودِ سلول نتیجه‌ی فرمول دیده می‌شود که در این مثال مقدار 5 است.

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

تمایز داده و فرمول

شاید به این فکر کرده باشید که اگر عبارت‌هایی که با = آغاز می‌شود همگی فرمول باشند، چگونه می‌توان محتوایی را در سلولی ذخیره کرد و نمایش داد که با = آغاز شود؟

پاسخ این است که باید پیش از = یک علامت ‘ (کوتیشن) که مشخصه‌ی متن است قرار دهید (شکل زیر).

علامت ‘ در خروجی سلول نمایش داده نمی‌شود و فقط در نوار فرمول دیده می‌شود؛ ولی به هر حال باعث می‌شود که آغازِ محتوای اصلی با = نباشد و فرمول به شمار نرود.

مرور فرمول‌ها

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

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

روشِ دیگر برای مرور فرمول‌ها، اجرای Show Formulas از تب Formulas گروە Formula Auditing است. با این کار به جای خروجی فرمول‌ها، متن آن‌ها در سلول‌ها نمایش داده می‌شوند (شکل زیر).

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

عملگرها

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

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

عملگرهای حسابی اکسل از این قرارند:

• عملگر +  ← جمع
• عملگر –  ← تفریق
• عملگر /  ← تقسیم
• عملگر *  ← ضرب
• عملگر ^  ← توان

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

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

=5.32+(6.1^8+1)/(32*8+3)

عملگرهای منطقی

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

عملگرهای منطقی اکسل از این قرارند:

• عملگر = ← برابر
• عملگر < ← بزرگ‌تر
• عملگر =<  ← بزرگ‌تر یا مساوی
• عملگر >  ← کوچک‌تر
• عملگر => ← کوچک‌تر یا مساوی
• عملگر <> ← نابرابر

اگر به عنوان مثال در سلولی عبارت =5=3 را وارد کنیم، خروجی آن False (نادرست) خواهد بود و خروجی عبارت =8<=9 مقدار True (درست) است.

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

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

=2+3*4

نتیجه‌ی این عبارت چیست؟ احتمالاً پاسختان 14 خواهد بود؛ زیرا معمول این است که اولویت ضرب بالاتر از جمع باشد و ابتدا 4*3 محاسبه شود و بعد نتیجه‌ی آن با 2 جمع شود.

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

1. توان
2. ضرب و تقسیم، هر کدام اول آمد
3. جمع و تفریق، هر کدام اول آمد
4. عملگرهای منطقی

عملگرهای یکسان و عملگرهایی که اولویتی نسبت به هم ندارند، اصولاً طوری هستند که ترتیب محاسبه‌ی آن‌ها تأثیری بر پاسخ ندارد.

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

توابع

مفهوم توابع

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

هر تابع ماهیتی است که می‌تواند ورودی‌هایی داشته باشد و حتماً یک خروجی نیز دارد.

گرامر توابع

به این عبارت توجه کنید:

=MAX(2,5,10)

این عبارت از تابع MAX برای محاسبه ماکزیمم (بزرگ‌ترین مقدار) استفاده می‌کند. آن‌چه در این تابع انجام می‌شود، انتخاب بزرگ‌ترین عدد بین اعداد 2، 5 و 10 است. راهنمای توابع مشخص می‌کنند که پارامترهای ورودی هر تابع چه هستند و به چه ترتیبی باید وارد شوند. در ادامه خواهید دید که توابع را می‌توان به طور دستی یا با کمک گرفتن از کادر محاوره‌ای درج تابع نوشت.

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

=(MAX(2,5,10)*2+1)/2

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

=(10*2+1)/2

=(20+1)/2

=21/2

=10.5

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

پارامترهای توابع

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

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

نوع دیگری پارامتر ورودی نیز وجود دارد: پارامترهای نامحدود. پارامترهای نامحدود می‌توانند ترکیبی از یک یا چند ورودی باشند. به عنوان مثال تابع SUM (جمع) پارامتر نامحدود دارد و می‌توانید آن را به صورت‌های زیر بنویسید:

=SUM(20,12,5,3,1)

=SUM(,5,3,1)

=SUM(3,1)

همان‌طور که می‌بینید تعداد ورودی‌های تابع SUM در مثال‌های مختلف متغیر است. نامحدود بودنِ پارامتر هم در راهنمای اکسل مشخص می‌شود

درج تابع

ساده‌ترین راه برای درج تابع، استفاده از Insert Function از تب Formulas گروە Function Library است.

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

تعداد توابع اکسل زیاد است و به همین خاطر در دسته‌های مختلفی قرار داده شده‌اند تا یافتن آن‌ها ساده شود. دسته‌بندی موردنظر خود را در لیست بازشوی select a category انتخاب کنید؛ اگر دسته‌بندی تابع را نمی‌دانید، گزینه‌ی All را انتخاب کنید.

بعد از این‌که دسته‌بندی را مشخص کردید، توابع موجود در آن دسته در قسمت میانی کادر محاوره‌ای (قسمت Select a function) نمایش داده می‌شوند. روی تابع موردنظر خود کلیک کنید. با این کار راهنمای تابع در پایین کادر محاوره‌ای نمایش داده می‌شود. برای درج تابع روی دکمه‌ی OK کلیک کنید. کادر محاوره‌ای مشابه آن‌چه در شکل زیر نمایش داده شده‌است باز خواهد شد.
در این کادر محاوره‌ای برای هر پارامتر ورودی تابع که باید وارد شود یک کادر در نظر گرفته می‌شود، به عنوان مثال تابع LOG انتخاب شده‌است که دو ورودی دارد و به همین خاطر دو کادر نمایش داده می‌شود. اگر عنوان کادر انتخاب پررنگ (Bold) باشد (مثل Number در شکل زیر) به معنی اجباری بودن آن و در غیر این صورت (مثل Base در شکل زیر) به معنی اختیاری بودن آن است.

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

به شکل زیر توجه کنید.

در این شکل سلول‌های C3 ،C4 و C5 برای ثبت و نگهداری سه مقدار ثابت در نظر گرفته شده‌اند. اکنون قصد داریم فرمولی در C5 بنویسیم که ماکزیمم آن سه مقدار را محاسبه کند.

برای انجام این کار ابتدا سلول C5 را انتخاب کنید و بعد از آن Insert Function را از تب Formulas گروە Function Library اجرا کنید. تابع MAX را انتخاب کرده، روی دکمه‌ی OK کلیک کنید. روی آیکن سمت راست کادر انتخاب Number1 کلیک کنید و بعد از آن سلول C3 را انتخاب کنید و در پایان کلید Enter صفحه کلید را بفشارید. در مرحله بعد روی آیکن سمت راست کادر انتخاب Number2 و بعد از آن سلول C4 کلیک کنید. سلول C5 را نیز در Number3 وارد کنید (شکل زیر).

وقتی روی دکمه‌ی OK کلیک کنید، تابع درج خواهد شد (شکل زیر).

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

آدرس‌دهی و ارجاع

آدرس‌دهی

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

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

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

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

مراحلی که در این کار انجام شده‌است به این صورت می‌باشد:

1. تایپ علامت =، در این مرحله برنامه می‌فهمد که قصد دارید فرمولی را وارد کنید.
2. کلیک روی سلول C3، برنامه متوجه می‌شود که قصد دارید به سلول C3 ارجاع دهید و در نتیجه آدرس آن را در محل نوشته شدن فرمول قرار می‌دهد. در این مرحله فرمول =C3 است.
3. تایپ عملگر +، در این مرحله فرمول به صورت =C3+ خواهد بود. پس از + نیاز به پارامتر است و در نتیجه برنامه منتظر است که پارامتر بعدی را وارد کنید.
4. کلیک روی C4، برنامه متوجه می‌شود که پارامتر بعدی C4 است و در نتیجه آدرس آن را در محل فرمول قرار می‌دهد. فرمول در پایان این مرحله به صورت =C3+C4 خواهد بود.
5. اکنون می‌توانید کلید Enter صفحه‌کلید را بفشارید تا نتیجه درج شود (شکل زیر).

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

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

ارجاع به ورک‌شیت‌های دیگر

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

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

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

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

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

ارجاع به سایر ورک‌بوک‌ها

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

شکل زیر روند ارجاع دادن خارجی را نشان می‌دهد.

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

اصلاح ارجاع‌ها

مثال قبل را در نظر بگیرید؛ فکر می‌کنید در صورتی که یک سطر بالای سطر شماره 3 درج کنیم چه اتفاقی خواهد افتاد؟ در این حالت C3 و C4 به ترتیب تبدیل به C4 و C5 خواهند شد. آیا فرمولی که در C5 وارد شده بود (که اکنون در C6 قرار خواهد داشت) دچار مشکل خواهد شد؟

شکل زیر وضعیت را بعد از درج سطری در بالای سطر شماره 3 نشان می‌دهد.

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

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

اکنون وضعیت دیگری را در نظر بگیرید؛ فرض کنید سلول C4 را انتخاب کرده و بعد از آن Cut را از تب Home گروه Clipboard اجرا کنید. سلول دیگری مانند D4 را انتخاب کرده، Paste را از تب Home گروه Clipboard اجرا کنید. به این ترتیب محتوای سلول C4 به D4 منتقل می‌شود. فکر می‌کنید در این حالت چه مسئله‌ای برای فرمول C6 به وجود آید؟ شکل زیر وضعیت را نشان می‌دهد.

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

مرور ارجاع‌ها

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

کپی کردن فرمول‌ها

فرض کنید جدولی از مقادیر مانند شکل زیر در اختیار داریم.

اکنون باید فرمولی برای سلول‌های ستون E بنویسیم که بهای کل سطرها را محاسبه کند. فرمول مناسب برای E3 به صورت D3*C3 =است (شکل زیر).

پس از این فرمول، سایر سطرها را چگونه خواهید نوشت؟ نوشتن فرمول برای تک‌تک سطرها سخت و وقت‌گیر است و احتمال اشتباه دارد؛ اگر هم لازم باشد که فرمول را تغییر دهید، باید تغییر را در تک‌تک آن‌ها تکرار کنید. اگر جدول چند هزار سطر داشته باشد، این کار عملاً ممکن نخواهد بود. برای رفع این مشکل قابلیت جالبی در اکسل قرار داده شده‌است. سلولی را که فرمول آن نوشته شده‌است، انتخاب کرده، Copy را از تب Home گروه Clipboard را اجرا کنید. بعد از آن سلول‌های باقی‌مانده را انتخاب کرده، Paste را از تب Home گروه Clipboard را اجرا کنید (شکل زیر).

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

آدرس‌دهی مطلق

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

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

علت بروز این مشکل این است که ارجاع به سلول بهای واحد نیز در زمان کپی جابه‌جا شده‌است، طوری که به عنوان مثال سلول D6 از فرمول =C6*C3 استفاده می‌کند، در حالی که باید به صورت =C6*C2 باشد. در واقع مقدار C2 باید در تمام فرمول‌ها ثابت باشد و به عبارت دیگر این ارجاع مطلق است، نه نسبی.

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

برای اصلاح مشکل، فرمول D5 (اولین سطر) را به صورت =C5*$C$2 بنویسید. اکنون اگر آن را کپی کنید، مشکلی به وجود نخواهد آمد (شکل زیر).

همان‌طورکه در شکل می‌بینید، ارجاع اول (C5) که نسبی است در فرمول‌های کپی شده اصلاح شده‌است (C6) ،ولی ارجاع دوم ($C$2) که مطلق است ثابت باقی مانده‌است. هر آدرس از دو جزء تشکیل شده‌است (به عنوان مثال A1)، جزء اول که ستون را مشخص می‌کند(A) و جزء دوم که سطر را مشخص می‌کند (1). هرکدام از این دو جزء را می‌توان مستقلاً نسبی یا
مطلق کرد. به عنوان مثال A1 در هر دو راستا نسبی است، $A1 فقط در راستای عمودی نسبی است و شماره ستون آن ثابت باقی می‌ماند، A$1 در راستای افقی نسبی است و شماره سطر آن تغییر نمی‌کند و در نهایت 1$A$ در هر دو راستا مطلق است.

سطر متغیر – $A1 ← ستون ثابت

سطر ثابت – A$1 ← ستون متغیر

سطر ثابت – 1$A$ ← ستون ثابت

متغیر – A1 ← متغیر

در فرمول مثال قبل می‌شد به جای =C5*$C$2 از =C5*$C$2 نیز استفاده کرد، چون در راستای افقی جابه‌جایی وجود ندارد.

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

فرمول A1 کاملاً نسبی است، در نتیجه هر دو جزء آن تغییر کرده‌اند. جزء اول A$1 نسبی و جزء دوم آن مطلق است، به همین خاطر جزء دوم همیشه 1 باقی‌مانده، ولی جزء اول اصلاح شده‌است. $A1 برعکس است، A ثابت باقی مانده و 1 اصلاح شده‌است. در نهایت 1$A$ کاملاً مطلق است و در نتیجه هیچ‌کدام از دو جزء آدرس آن تغییر نکرده‌اند.

برای مطالعه‌ی بیشتر در این زمینه پیشنهاد می‌کنیم مطلب مفید زیر را مطالعه بفرمایید: آدرس‌دهی نسبی و مطلق: علامت دلار ($) در اکسل

شیوه‌های کپی

شیوه سنتی

شیوه‌ی سنتی کپی این است که سلول یا محدوده مبدأ را انتخاب کرده، روی گزینه‌ی Copy از تب Home گروه Clipboard کلیک کنید. بعد از آن سلول یا محدوده مقصد را انتخاب کرده، Paste را از تب Home گروه Clipboard اجرا کنید.

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

کلیدهای ترکیبی دستورهای Copy و Paste به ترتیب Ctrl+C و Ctrl+V است. این کلیدها عملاً در تمام نرم‌افزارها وجود دارند و بهتر است آن‌ها را به خاطر بسپارید و به‌کارگیرید.

کپی در راستا

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

ابتدا محدوده‌ای را که باید محتوا در آن کپی شود، طوری برگزینید که سلولی که قرار است محتوای آن کپی شود در ابتدای آن باشد (شکل زیر) پس از آن Fill را از تب Home گروه Editing اجرا کنید تا کادر محاوره‌ای گزینه‌ها باز شود. در این کادر محاوره‌ای چهار گزینه‌ی Down (کپی رو به پایین)، Up (کپی رو به بالا)، Left (کپی به سمت چپ) و Right (کپی به سمت راست) وجود دارد؛ گزینه‌ی مناسب را انتخاب کنید تا کپی انجام شود (شکل زیر).

بیشتر بدانید در چهار نوع کپی در راستا، رایج‌ترین گزینه Down است. برای این گزینه میانبر Ctrl+D نیز وجود دارد. استفاده مناسب از کلید ترکیبی Ctrl+D برای کپی مقادیر، سرعت کار را در اکسل افزایش می‌دهد.

کپی با ماوس

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

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

گزینه‌ها از این قرارند:

• Copy Cells:محتویات و قالب‌بندی را کپی می‌کند.
• Fill Series: اگر محتوای سلول مبدأ عدد باشد، این گزینه نیز وجود خواهد داشت. اگر آن را انتخاب کنید، محدوده‌ی انتخاب‌شده با اعدادی پر می‌شود که از عدد مبدأ آغاز می‌شوند و هرکدام یک واحد از قبلی بزرگ‌ترند.
• Fill Formatting Only: فقط قالب‌بندی را منتقل می‌کند.
• Fill Without Formatting: فقط محتویات را منتقل می‌کند و قالب‌بندی مقصد تغییر نمی‌کند.

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

در اکثر حالت‌ها بر اساس دو مقدار اولیه ترتیبی در نظر گرفته می‌شود و سلول‌ها با همان ترتیب مقدار می‌گیرند.

نکته: بهتر است برای تمام جدولها ستونی با نام ”ردیف” در نظر بگیرید که به ترتیب از 1 به بعد شماره‌گذاری شده باشند و این ستون باید اولین ستون جدول باشد. جدول‌های اصولی همیشه ستون سطر یا ستونی مشابه آن دارند. بهترین راه برای مقدار دادن به ستون سطر این است که به دو سلول ابتدای آن مقدارهای 1 و 2 بدهید و با روشی که در این قسمت گفته شد، آن را با ماوس امتداد دهید.

توابع پرمصرف

تابع SUM

تابع SUM برای جمع کردن مقادیر است و عملکرد آن مشابه عملگر جمع است. اگر لازم باشد که تعداد زیادی عدد را با هم جمع بزنید، استفاده از عملگر جمع مشکل است، زیرا باید به تک‌تک سلول‌ها ارجاع دهید، ولی در تابع SUM می‌توانید محدوده تعریف کنید و این مسئله کارکرد را بسیار ساده می‌کند. به عنوان مثال =SUM(A1:A1000) هزار سلولی را که در فاصله‌ی A1 تا A1000 قرار دارند، جمع می‌زند.

برای انتخاب محدوده در کادر انتخابی که در کادر محاوره‌ای درج تابع وجود دارد، روی اولین سلول کلیک کنید و بدون رها کردن کلید ماوس، آن را تا آخرین سلول بکشید و رها کنید. با این کار عبارت A1:A1000 که آدرس محدوده است در کادر انتخاب نوشته می‌شود. می‌توانید عبارت را دستی نیز وارد کنید.

تابع AVERAGE

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

تابع MAX

تابع MAX حداکثر مقداری را که در یک یا چند سلول یا محدوده ورودی آن قرار دارد، تعیین می‌کند و کارکرد آن مشابه تابع SUM است. به عنوان مثال فرمول =MAX(A1:A10,B1,C10) حداکثر مقداری را که در سلول‌های C10 ،B1 و محدوده‌ی A1 تا A10 قرار دارد، نتیجه می‌دهد.

تابع MIN

تابع MIN مشابه MAX است و به جای حداکثر، حداقل را نتیجه می‌دهد.

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

پیگیری ارجاع‌ها

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

برای پیگیری ارجاع‌ها، ابتدا سلولی را که فرمول موردنظر شما در آن قرار دارد انتخاب کنید و بعد روی Precedents Trace از تب Formulas گروه Formula Auditing کلیک کنید. با این کار ارجاع‌ها با فلش‌هایی مشخص می‌شوند (شکل زیر).

برای پاک کردن فلش‌ها روی Remove Arrows از تب Formulas گروه Formula Auditing کلیک کنید.

پیگیری وابستگی‌ها

برای این‌که متوجه شوید چه فرمول‌هایی به سلول خاصی ارجاع داده‌اند، سلول را انتخاب کرده، روی Trace Dependents از تب Formulas گروه Formula Auditing کلیک کنید. با این کار وابستگی‌ها با فلش‌هایی مشخص می‌شوند (شکل زیر).

اجرای فرمول‌ها

گاهی ممکن است خروجی فرمول مطابق انتظار نباشد و با بررسی آن هم نتوانید علت خطا را پیدا کنید. علت چنین خطاهایی این است که در یکی از مراحل محاسبه‌ی نتیجه‌ی فرمول، رفتاری وجود دارد که با انتظار شما هماهنگ نیست. برای رفع این مشکل می‌توانید مراحل محاسبه فرمول را مرور کنید. برای مرور مراحل محاسبه فرمول روی Evaluate Formula از تب Formulas گروه Formula Auditing کلیک کنید. با این کار، کادر محاورهای باز می‌شود و هر بار که روی دکمه Evaluate کلیک کنید یک مرحله محاسبه را نشان می‌دهد.

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

پیگیری خطاها

در اکسل ابزاری برای پیگیری و رفع خطاها وجود دارد. برای این منظور روی Checking Error از تب Formulas گروه Formula Auditing کلیک کنید. با این کار ورک‌شیت مرور می‌شود و اگر یک یا چند خطا وجود داشته باشد، کادر محاوره‌ای مانند کادر محاوره‌ای شکل زیر باز خواهد شد.

ابزارهایی که در این کادر محاوره‌ای وجود دارد بسته به نوع خطا متفاوت است. به عنوان مثال رفع خطای فرمولی که کادر محاوره‌ای آن در شکل قبل وجود داشت نیاز به پیگیری ارجاع‌ها دارد، به همین خاطر دکمه‌ی Show Calculation Steps وجود دارد که با کلیک روی آن ارجاع‌ها رهگیری می‌شوند.

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

---

« مدیریت ورک‌شیت‌ها رسم نمودار »