سبد خرید
0

هیچ محصولی در سبد خرید نیست.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

قواعد فرمول نویسی حرفه ای در اکسل | قسمت پایانی

آموزش فرمول نویسی در اکسل
۵/۵ - (۱۴ امتیاز)

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

نکته اول: مشاهده فرمول (بجای مشاهده نتیجه فرمول)

حتما تا حالا زیاد پیش اومده که بخواید فرمول داخل سلول رو مثل شکل ۱ ببینید.
مشاهده فرمول ها

شکل ۱- آموزش فرمول نویسی در اکسل – مشاهده فرمول های نوشته شده

برای این کار ۲ راه وجود داره:

-روش ۱: استفاده از Show Formulas که مسیر آن را در زیر می بینید:

Formula/ Formula Auditing/ Show Formulas

کلید میانبر این گزینه کلید ترکیبی Ctrl+` (دکمه زیر دکمه Esc) که با زدن این ترکیب، همه فرمول های نوشته در شیت جاری، نمایان می شوند.

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

 

-روش ۲: بعضی مواقع هست میخواهیم فقط فرمول سلول های مشخصی رو ببینیم. برای این موضوع، هم می تونیم از تابع Formulatext استفاده کنیم و هم اینکه با گذاشتن یک ‘ اول فرمول، فرمول اون سلول رو تبدیل به یک متن کنیم.

همونطور که در شکل ۲ مشاهده می کنید، تابع Formulatext یک آرگومان داره و هر سلولی رو که بگیره، محتوای داخل سلول رو بر میگردونه.

 تابع FormulaText

شکل ۲- آموزش فرمول نویسی در اکسل – تابع FormulaText

نکته دوم: مشاهده سلول های تاثیرگذار و تاثیر پذیر یک فرمول

فرض کنید می خواهیم فرمولی رو حذف کنیم یا تغییر بدیم. قبل از اینکه این کار رو بکنیم بادی ببینیم این فرمول روی چه سل های دیگه ای تاثیر میذاره که بتونیم این حذف یا تغییر رو مدیریت کنیم. یا برعکس، سلولی رو میخوایم حذف کنیم یا تغییر بدیم، می خوایم ببینیم که این سلول روی چه فرمول هایی اثرگذار هست که بتونیم این تغییرات رو مدیریت کنیم و نکته ای از قلم نیفته.

اگر روی سلولی که حاوی فرمول هست کلیک کنیم و روی گزینه Trace Precedents رو از تب Formula کلیک کنیم، مطابق شکل ۳ همه سل هایی که بر این فرمول اثرگذار هستند با فلش نمایش داده می شوند.

 نمایش سلول های تأثیرگذار

شکل ۳- آموزش فرمول نویسی در اکسل – نمایش سلول های تاثیرگذار بر فرمول

همانطور که در شکل ۳ مشاهده می کنید، ارتباطات با فلش آبی نمایش داده شده. اون خط چین نشان داده شده هم بیانگر این هست که شیت های دیگه هم روی این فرمول اثر میذارن. پس باید حواسمون به تغییراتی که میدیم باشه.

حالا اگر روی یک سلول کلیک کنیم و گزینه Trace Dependents رو از تب Formulas کلیک کنیم، از این سل به سمت همه سلول هایی که از این سلول تغذیه می شوند، فلش آبی ظاهر می شود. در واقع همه سلول هایی که این سلول روی آنها اثر گذاشته، مشخص می شوند.

نمایش سلول های تأثیرپذیر

شکل ۴- آموزش فرمول نویسی در اکسل – نمایش سلول های تاثیرپذیر از یک سلول

همانطور که می بینید، شکل ۴ نشان دهنده این است که سلول A2 بر سلول C2 و شیت های دیگه ای اثر میگذارد (فلش نقطه چین نشانه ارتباط با سایر شیت هاست).

همه این فلش ها رو می تونیم از مسیر زیر حذف کنیم.

Formulas/ Formula Auditing/ Remove Arrows

نکته:
اگر سلولی که انتخاب کردیم، روی سلول ها یا شیت های دیگه اثر نداشته باشه و یا از هیچ سلول یا شیت دیگه ای تاثیر نگیره، با خطای شکل ۵ مواجه می شویم.

 

آموزش فرمول نویسی در اکسل - خطا در صورت نبود ارتباط بین سلول ها

شکل ۵- آموزش فرمول نویسی در اکسل – خطای نمایش داده شده در صورت نبودن ارتباط بین سلول انتخاب شده و سایر سلول ها

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

اینکه محاسبات روی حالت دستی باشه یا اتومات، از مسیر زیر قابل تنظیم هست:

Formulas/ Calculation/ Calculation Options/ (Automatic/ Manual)

حالا مسئله ای که مطرح میشه اینه که کاربرد این نکته چی هست؟ چرا همیشه نمیذاریم روی حالت خودکار؟

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

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

نکته چهارم: نامگذاری محدوده ها

نامگذاری محدوده ها از مباحث بسیار مهم  و کاربردی در فرمول نویسی حرفه ای بشمار میره. برای نامگذاری محدوده بر روی  Name Manager از تب Formula کلیک میکنیم و New رو میزنیم. در پنجره ای که باز می شه مطابق شکل ۶ در قسمت Name نام مورد نظر محدوده را تایپ میکنیم و در قسمت Refers To محدوده مورد نظر را وارد می کنیم.

آموزش فرمول نویسی در اکسل - نامگذاری محدوده ها

شکل ۶- آموزش فرمول نویسی در اکسل – نامگذاری محدوده

همانطور که در شکل ۶ می بینید، محدوده $D$2:$J$19 به نام Data نامگذاری شده و از این به بعد کلمه Data در اکسل یک متن شناخته شده است.

از این به بعد می تونیم مثلا بجای اینکه بزنیم =Sum($D$2:$J$19) بزنیم =Sum (Data) .

دقت کنید که کلمه Data متن نیست و نباید در “” قرار بگیره. چون این محدوده به عنوان $D$2:$J$19 به اکسل معرفی شده و متن نیست.

استفاده از محدوده های نامگذاری در فرمول نویسی مزایای زیادی داره که مهم ترین آن، قابلیت ویرایش محدوده ها و مدیریت فرمول های نوشته شده در کل فایل است. مثلا فرض کنید از این محدوده $D$2:$J$19 در کل فایل استفاده شده و حالا میخوایم این محدوده رو به $D$2:$F$40 تغییر بدیم. کافیه از طریق Name Manager این نام رو ویرایش کنیم. به این ترتیب همه فرمول هایی که کلمه Data در آنها استفاده شده بروز می شن. درواقع نیازی نیست همه فرمول هایی که از این محدوده استفاده کردن، تک به تک اصلاح بشن.

Name Manager نکات بسیار زیادی داره که در آموزش های بعدی حتما به آن خواهیم پرداخت.

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

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

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

آواتار
144

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

دیدگاه کاربران
  • علیرضا رضایی ۸ شهریور ۱۳۹۹ / ۲:۴۶ ب٫ظ

    بسیار ممنون از سایت خوبتون

  • javad ۲۰ فروردین ۱۳۹۹ / ۴:۲۰ ب٫ظ

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

  • امیرهوشنگ بیگدلی ۳ شهریور ۱۳۹۸ / ۹:۰۶ ق٫ظ

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

    • آواتار
      حسنا خاکزاد ۵ شهریور ۱۳۹۸ / ۱۰:۵۸ ق٫ظ

      درود بر شما
      سوالاتتون و میتونید داخل گروه تلگرامی که لینکش در فوتر سایت هست مطرح کنید

  • سید ۲۲ اردیبهشت ۱۳۹۸ / ۹:۲۰ ق٫ظ

    با سلام و عرض خسته نباشید
    ممنون از مطالبی که داخل سایت قرار دادین
    یه سوال داشتم
    من یه سری داده دارم میخواستم بدونم امکان فرمول نوشتن به صورت زیر هست یا نه
    با سرچ ستون ( مثلا a) اگر مقدار سلول برابر صفر باشد مقدار سلول دیگری از همان ردیف را ( مثلا b) را با مقدار سلول b ردیف بالای آن جمع کند و کل ردیف را حذف نماید
    اگر هم که مقدار سلول a صفر نباشد که ردیف بعدی را چک کند

    • آواتار
      حسنا خاکزاد ۲۲ اردیبهشت ۱۳۹۸ / ۱۰:۵۴ ب٫ظ

      درود بر شما
      اگر بخواید ردیفی حذف یا اضافه بشه باید کد نویسی کنید
      VBA

  • m.khalili ۲۸ فروردین ۱۳۹۸ / ۴:۳۵ ب٫ظ

    با سلام و خسته نباشید از زحمات شما
    مطالب گفته شده خیلی مفید و کاربردیه ممنون

  • علی بابایی ۱۳ بهمن ۱۳۹۷ / ۴:۴۰ ب٫ظ

    با سلام و تشکر از مطالب مفیدتون
    آیا در اکسل فرمولی هست که بتوان اعداد زیر ۲۵/. را به ۲۵/. و زیر ۵/. را به نیم و بین ۵/ تا ۷۵/ را به ۷۵/. و بالاتر از ۷۵/. را به ۱ رند نماید.

  • مصطفي كيان ۶ دی ۱۳۹۷ / ۳:۴۲ ب٫ظ

    با تشکر از اشتراک گذاری lمطالب مفیدتون.
    در خصوص چگونگی نمایش فرمول داخل سلول علاوه بر ۲ راهکاری که فرمودید، راهکار سومی هم وجود داره :
    Excel Option => Advanced => Display Option For this Worksheet => Show Formula in Cells …

  • احمد ۱۹ فروردین ۱۳۹۶ / ۱۲:۰۳ ب٫ظ

    عالی بود

  • سیما ۱۴ فروردین ۱۳۹۶ / ۱:۵۱ ب٫ظ

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

    • سامان چراغی ۱۴ فروردین ۱۳۹۶ / ۲:۰۷ ب٫ظ

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

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان