خانه / درس دوم کلاس درس آموزش حسابداری در اکسل به زبان ساده
لقمه های انگلیسی

درس دوم کلاس درس آموزش حسابداری در اکسل به زبان ساده

همکلاسی و همراه خوب یو سی؛ سلام
به صفحه دوم کلاس درس آموزش حسابداری در اکسل به زبان ساده خوش آمدید.

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

سوال های درسی خود را نیز می توانید از انتهای صفحه مطرح نمایید.

لطفاً توجه داشته باشید که هر یک از درس ها برای یک روز در نظر گرفته شده اند.
بنابراین توصیه می شود برای هر درس یک روز کامل، زمان بگذارید و چندین بار آن درس را مطالعه کنید و تمرین نمایید تا نسبت به آن درس، مسلط شوید.

خب؛ درس دوم را شروع می کنیم.

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

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

نکته: توجه داشته باشید که مقطع تحصیلی مدرسه (ابتدایی، راهنمایی و دبیرستان یا هنرستان)، درس هایی که تدریس و بر اساس آنها کارنامه تهیه می شود و همچنین اسامی معلم ها، همگی اطلاعات مهم جدول های پایه را تشکیل می دهند و باید در طراحی لحاظ گردند.

خب؛ اولین کاری که باید انجام بدهیم این است که نام درس ها را در یک ستون زیر هم بنویسیم. بعد برای هر درس نمره ای را تعیین و روبروی نام آن تایپ می کنیم. تصویر ۱۹ چند درس و نمره فرضی را نشان می دهد.

تصویر 19 - چند درس و نمره به عنوان نمونه

تصویر ۱۹ – چند درس و نمره به عنوان نمونه

شرط اول فرمول نویسی در اکسل
فراموش نکنید که هر وقت می خواهید در یک سلول اکسل، دستوری را بنویسید ابتدا باید یک علامت مساوی ( = ) تایپ نمایید. با فشردن دکمه ی مساوی ( = ) اکسل متوجه می شود که قرار است در سلول مورد نظر، یک فرمول درج شود.

تابع جمع
برای جمع کردن محتویات چند سلول با یکدیگر ابتدا در یک سلول خالی (برای مثال زیر سلول نمره ی شیمی) یک علامت مساوی ( = ) قرار می دهیم. بعد اولین سلول نمره یعنی ۱۸ را انتخاب می کنیم. سپس یک علامت + قرار داده (دکمه جمع « + » روی صفحه کلید) و بعد سلول نمره ی بعدی یعنی ۹ را انتخاب می کنیم و مجدداً علامت + قرار داده و همین طور این کار را تا سلول نمره ی ۶ ادامه می دهیم. تصویر ۲۰ را ببینید. در نهایت Enter می زنیم.

تصویر 20 - انجام عمل جمع در اکسل به روش دستی

تصویر ۲۰ – انجام عمل جمع در اکسل به روش دستی

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

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

برای استفاده از تابع SUM ابتدا یک علامت مساوی قرار می دهیم. با این کار بلافاصله کادر Name Box نوار فرمول تغییر وظیفه داده و به جای نام سلول، نام تابع ها را نشان می دهد. روی مثلث رو به پایین آن کلیک می کنیم. تصویر ۲۱ مسیر را به شما نشان می دهد.

تصویر 21 - تغییر وظیفه کادر Name Box در نوار فرمول

تصویر ۲۱ – تغییر وظیفه کادر Name Box در نوار فرمول

با کلیک روی این مثلث کشویی رو به پایین، نام ۱۰ تابعی که آخرین بار از آنها استفاده کرده اید نمایش داده می شود. اگر تابع مورد نظرتان در این لیست نبود، می توانید از گزینه ی آخر یعنی …More Functions که همه ی تابع ها در آن موجود هستند به دنبال تابع مورد نظر خود بگردید.

خب در تصویر ۲۱ می بینید که تابع SUM در این لیست ۱۰ تایی قرار دارد. ولی ما فرض می کنیم که آن را ندیده ایم! بنابراین از قسمت …More Functions استفاده می کنیم. با انتخاب …More Functions پنجره ی Insert Function باز می شود. تصویر ۲۲ را ببینید.

تصویر 22 - پنجره ی Insert Function

تصویر ۲۲ – پنجره ی Insert Function

  • Search for a function: از این قسمت برای جستجوی نام تابع ها استفاده می شود. به این صورت که نام تابع را می نویسیم و روی دکمه ی Go کلیک می کنیم. سپس نام تابع در قسمت Select a function قابل مشاهده و انتخاب خواهد بود.
  • Select a category: در این قسمت تابع ها بر اساس ماهیت شان دسته بندی شده اند. با انتخاب هر گروه، تابع های موجود در آن گروه در قسمت Select a function قابل مشاهده و انتخاب خواهند بود. مهم ترین گروه های این بخش عبارتند از:
    • Most Recently Used: تابع هایی که اخیراً استفاده شده اند.
    • All: همه ی تابع های موجود در اکسل را با انتخاب این گروه می توانید مشاهده کنید.
    • Financial: همه ی تابع های مالی را در این گروه می توانید مشاهده کنید.
    • Date & Time: همه ی تابع های مربوط به تاریخ و زمان در این گروه قابل مشاهده هستند.
    • Math & Trig: همه ی تابع های مربوط به ریاضیات را در این گروه می توانید مشاهده کنید.
    • Statistical: همه ی تابع های آماری در این گروه قابل مشاهده هستند.
    • Lookup & Reference: تابع های مرجع در این گروه قرار دارند.
    • Logical: تابع های منطقی و استدلالی در این گروه قابل دسترسی هستند.
    • Engineering: همه ی تابع های مهندسی در این گروه قرار دارند.
  • Select a function: برای انتخاب تابع ها باید از این قسمت استفاده کنیم.

حالا در قسمت Search for a function نام تابع جمع یعنی SUM را تایپ و روی Go کلیک می کنیم. سپس از قسمت Select a function تابع SUM را انتخاب می نماییم. تصویر ۲۳ را ببینید.

تصویر 23 - پیدا کردن تابع SUM در پنجره ی Insert Function

تصویر ۲۳ – پیدا کردن تابع SUM در پنجره ی Insert Function

با انتخاب تابع SUM پنجره ی Function Arguments (تصویر ۲۴) برای معرفی محدوده ای که اکسل باید آنها را با هم جمع کند باز می شود.

تصویر 24 - پنجره معرفی محدوده ای که اکسل باید آنها را جمع کند.

تصویر ۲۴ – پنجره معرفی محدوده ای که اکسل باید آنها را جمع کند.

ما می خواستیم نمره های درس دینی تا شیمی با هم جمع شوند. این نمره ها در سلول های F3 تا F8 تایپ شده اند. این محدوده را به صورت F3:F8 در کادر مقابل Number1 می نویسیم. تصویر ۲۴ این مورد را نشان می دهد. سپس روی دکمه ی OK کلیک می کنیم.

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

در نهایت جمع نمرات مانند تصویر ۲۵ در سلول F9 قرار می گیرد.

تصویر 25 - نتیجه استفاده از تابع SUM

تصویر ۲۵ – نتیجه استفاده از تابع SUM

نکته: AutoSum ( ∑ ) چیست؟
اکسل در جعبه ی ابزار (تصویر ۲۶ را ببینید.) خود دکمه ای برای جمع خودکار قرار داده است که با استفاده از آن می توانید اعداد موجود در یک منطقه (تعدادی از سلول های به هم پیوسته) را انتخاب و با هم جمع کنید.

تصویر 26 - معرفی قابلیت AutoSum

تصویر ۲۶ – معرفی قابلیت AutoSum

تابع میانگین
حالا تصمیم داریم معدل دانش آموزان را محاسبه کنیم. در ساده ترین حالت برای محاسبه ی معدل باید همه ی نمره ها را با هم جمع و سپس تقسیم بر تعداد درس ها (با فرض یکسان بودن تعداد واحدهای هر درس) کنیم. خب؛ جمع نمرات را که بدست آورده ایم. پس کافی است در سلول F10 یک علامت مساوی ( = ) قرار داده و جمع نمرات (سلول F9) را تقسیم ( / ) بر تعداد درس ها ( ۶ ) کنیم. به نوار فرمول تصویر ۲۷ توجه نمایید.

تصویر 27 - محاسبه میانگین نمرات به روش دستی

تصویر ۲۷ – محاسبه میانگین نمرات به روش دستی

بله؛ می بینید که معدل این دانش آموز تنبل!! ۱۴.۲۵ شده است.

برای محاسبه ی میانگین راه دیگری هم وجود دارد و آن هم استفاده از تابع AVERAGE است. مسیر دسترسی به این تابع هم دقیقاً شبیه به تابع SUM می باشد.

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

تصویر 28 - پنجره معرفی محدوده ای که اکسل باید میانگین آنها را بدست آورد.

تصویر ۲۸ – پنجره معرفی محدوده ای که اکسل باید میانگین آنها را بدست آورد.

در کادر Number1 محدوده ای که نمره ها در آن قرار گرفته اند را مشخص کرده (F3:F8) و سپس روی دکمه ی OK کلیک می کنیم. همانطور که مشاهده می کنید دوباره به معدل ۱۴.۲۵ می رسیم.

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

تابع حداکثر، بزرگترین عدد را از منطقه ای که انتخاب شده پیدا کرده و در سلول مورد نظر شما می نویسد. برای مثال مانند تصویر ۲۹ محدوده ی F3:F8 را به تابع معرفی می کنیم.

تصویر 29 - استفاده از تابع MAX

تصویر ۲۹ – استفاده از تابع MAX

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

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

تابع حداقل، کوچکترین عدد را از منطقه ای که انتخاب شده پیدا کرده و در سلول مورد نظر شما می نویسد. برای مثال مانند تصویر ۳۰ محدوده ی F3:F8 را به تابع معرفی می کنیم.

تصویر 30 - استفاده از تابع MIN

تصویر ۳۰ – استفاده از تابع MIN

بله؛ نمره ی ۶ که متعلق به درس شیمی بود، پایین ترین نمره است.

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

تابع شمردن همانطور که از نامش پیداست برای شمارش تعداد سلول های عددی یک محدوده مورد استفاده قرار می گیرد. مثلاً در تصویر ۳۱ تعداد درس ها را با استفاده از این تابع بدست آورده ایم.

تصویر 31 - استفاده از تابع COUNT

تصویر ۳۱ – استفاده از تابع COUNT

نکته: جعبه ی ابزار اکسل بجز جمع خودکار، قادر به محاسبه ی اتوماتیک توابع Average – Count – Max – Min و… نیز می باشد.

تابع شرطی
در همه ی کارنامه ها یک قسمت برای ملاحظات وجود دارد که معمولاً در آن یکسری توضیحات نوشته می شود. مثلاً اگر دانش آموزی درسی را زیر ۱۰ گرفته باشد، در قسمت ملاحظات کلمه ی «مردود» را می نویسیم. الان می خواهیم این قسمت را تهیه کنیم.

برای اینکار لازم است یک فرمول شرطی به کمک تابع IF بنویسیم. در این فرمول شرطی باید بیان شود که اگر نمره ی درسی کوچکتر از ۱۰ بود، در آن درس مردود، و اگر بیشتر یا مساوی ۱۰ بود قبول شده ایم.

برای نوشتن این فرمول به زبان اکسل ابتدا یکی از سلول ها را انتخاب می کنیم. روی سلول G3 (روبروی سلول نمره ی درس دینی) کلیک می کنیم. علامت = را قرار داده و تابع IF را از قسمت تابع ها (More Functions) پیدا و انتخاب می کنیم تا تصویر ۳۲ باز شود.

تصویر 32 - پنجره معرفی شرط در تابع IF

تصویر ۳۲ – پنجره معرفی شرط در تابع IF

در این پنجره سه کادر وجود دارند که کادر اول یعنی Logical_test برای نوشتن شرط است. شرط ما این است که اگر نمره ی درس دینی کمتر از ۱۰ بود در سلول روبروی آن کلمه ی «مردود» نوشته شود.

شرط را به این صورت می نویسیم: F3<10
F3 نام سلولی است که در آن نمره ی درس قرار گرفته و در ادامه هم به کوچکتر از ۱۰ بودن اشاره کرده ایم.

کادر دوم یعنی Value_if_true برای نوشتن شرط درست است. در این کادر باید مشخص کنیم که اگر نمره ی درس کمتر از ۱۰ بود چه اتفاقی بیفتد؟ در این شرط می خواهیم نمره های کوچکتر از ۱۰، مردود اعلام شوند. پس کلمه ی مردود را به صورت « “مردود” » می نویسیم.

نکته: اکسل هر چیزی را که بین دو علامت قرار بدهیم عیناً در سلول می نویسد و هیچ محاسبه ی ریاضی روی آن انجام نمی دهد.

کادر Value_if_false هم برای نوشتن شرط نادرست است. در اینجا باید مشخص کنیم که اگر نمره ی یک درس، ۱۰ یا بیشتر از آن بود چه اتفاقی بیفتد؟ طبیعتاً باید در مقابل نمره های بالاتر از ۱۰، کلمه ی «قبول» نوشته شود. پس در این کادر کلمه ی قبول را بین دو علامت ” می نویسیم. تصویر ۳۳ را ببینید.

تصویر 33 - یک نمونه تعریف شرط برای تابع IF

تصویر ۳۳ – یک نمونه تعریف شرط برای تابع IF

روی دکمه ی OK کلیک می کنیم تا نتیجه ی کار را با هم در تصویر ۳۴ مشاهده نماییم.

تصویر 34 - نتیجه شرط تعریف شده

تصویر ۳۴ – نتیجه شرط تعریف شده

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

اگر دقت کنید می بینید که گوشه ی پایین سمت چپ سلول قبول (حاوی فرمول) یک مربع قرار دارد (تصویر ۳۵) که اگر اشاره گر ماوس روی آن برود، به شکل + تبدیل می شود.

تصویر 35 - محل بسط فرمول یا محتویات سلول

تصویر ۳۵ – محل بسط فرمول یا محتویات سلول

زمانی که اشاره گر به شکل + تبدیل شد، دکمه ی سمت چپ ماوس را پایین نگه داشته (کلیک کنید) و آن را تا سلول مقابل نمره ی درس شیمی (سلول G8) بکشید. تصویر ۳۶ را ببینید.

تصویر 36 - بسط فرمول

تصویر ۳۶ – بسط فرمول

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

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

فرض کنید ۵ کارمند به شرح جدول زیر داریم:

ردیف

نام و نام خانوادگی

حقوق

بیمه

مالیات

کسورات

حقوق پرداختنی

۱

الف

۶۰۰۰۰۰

۲

ب

۴۷۰۰۰۰

۳

ج

۳۸۰۰۰۰

۴

د

۶۲۰۰۰۰

۵

و

۳۵۰۰۰۰

مطلوب است:

  1. بیمه معادل ۷ درصد حقوق می باشد.
  2. حقوق های بیشتر از ۴۰۰,۰۰۰ باید ۵ درصد مالیات بدهند. (خود ۴۰۰,۰۰۰ هم مشمول مالیات می شود.)
  3. جمع بیمه و مالیات در سلول کسورات قرار گیرد.
  4. اختلاف حقوق و کسورات مبلغ حقوق پرداختنی را مشخص می کند.
  5. بیشترین و کمترین حقوق پرداختنی را محاسبه کنید.
  6. میانگین حقوق پرداختنی شرکت را بدست آورید.
  7. برای اعداد جداکننده سه رقم قرار دهید.

خب؛ ابتدا جدول بالا را در اکسل مانند تصویر ۳۷ می سازیم.

تصویر 37 - وارد کردن اطلاعات مثال در اکسل

تصویر ۳۷ – وارد کردن اطلاعات مثال در اکسل

حالا؛

۱) برای محاسبه ی بیمه کافی است مبلغ حقوق را در ۷ درصد ضرب کنیم. به این صورت که برای فرد الف خواهیم داشت: D3*7%=

در محاسبه ی سایر بیمه ها هم می توانید از روش بسط دادن استفاده کنید. به تصویر ۳۸ توجه نمایید.

تصویر 38 - محاسبه بیمه

تصویر ۳۸ – محاسبه بیمه

۲) برای محاسبه ی مالیات باید از تابع IF استفاده کنیم. اگر حقوق ۴۰۰,۰۰۰ یا بیشتر بود باید ۵ درصد مالیات پرداخت شود. پس مبلغ حقوق را در ۵ درصد ضرب می کنیم و اگر حقوق کمتر از ۴۰۰,۰۰۰ بود از پرداخت مالیات معاف است. به تصویر ۳۹ توجه کنید.

تصویر 39 - نحوه نوشتن فرمول محاسبه مالیات با تابع IF در اکسل

تصویر ۳۹ – نحوه نوشتن فرمول محاسبه مالیات با تابع IF در اکسل

توجه داشته باشید که در تصویر ۳۹ به دلیل اینکه حقوق ۴۰۰,۰۰۰ هم مشمول مالیات می شود، بعد از علامت بزرگتر یک = قرار می دهیم. همچنین در کادر دوم، D3*5% را بین ” ” قرار نداده ایم. چون D3*5% یک فرمول است و باید محاسبه شود.

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

حالا در تصویر ۴۰ محاسبه مالیات را مشاهده می کنید.

تصویر 40 - محاسبه مالیات

تصویر ۴۰ – محاسبه مالیات

۳) محاسبه ی جمع که خیلی ساده است. هم می توانیم از تابع SUM استفاده کنیم و هم اینکه به روش معمولی دو سلول را با هم جمع کنیم. مثلاً E3+F3=

به تصویر ۴۱ توجه کنید.

تصویر 41 - محاسبه کسورات

تصویر ۴۱ – محاسبه کسورات

۴) حالا کافی است تا کسورات را از حقوق کم کنیم تا حقوق پرداختنی بدست آید. به این صورت: D3-G3=

به تصویر ۴۲ توجه کنید.

تصویر 42 - محاسبه حقوق پرداختنی

تصویر ۴۲ – محاسبه حقوق پرداختنی

۵) برای محاسبه ی بیشترین و کمترین حقوق پرداختنی، به ترتیب از تابع های MAX و MIN (تصویر ۴۳) استفاده می کنیم.

تصویر 43 - محاسبه بیشترین و کمترین حقوق

تصویر ۴۳ – محاسبه بیشترین و کمترین حقوق

۶) برای محاسبه ی میانگین حقوق پرداختنی از تابع AVERAGE (تصویر ۴۴) استفاده می کنیم.

تصویر 44 - محاسبه میانگین حقوق

تصویر ۴۴ – محاسبه میانگین حقوق

۷) برای جدا کردن سه رقم سه رقم عددها ابتدا همه ی اعداد را انتخاب می کنیم و سپس از منوی کلیک راست، گزینه ی …Format Cells را انتخاب می نماییم. زبانه ی Number در پنجره ی Format Cells از دو قسمت Category (دسته بندی) و Sample (نمونه) تشکیل شده است. تصویر ۴۵ را ببینید.

تصویر 45 - پنجره Format Cells در اکسل

تصویر ۴۵ – پنجره Format Cells در اکسل

از قسمت Category، گزینه ی Number را انتخاب می کنیم. (تصویر ۴۶)

تصویر 46 - جدا کردن سه رقم سه رقم عددها در اکسل

تصویر ۴۶ – جدا کردن سه رقم سه رقم عددها در اکسل

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

اگر Use 1000 Separator را تیک بزنید، اعداد انتخابی سه رقم سه رقم از هم جدا می شوند. حالا نتیجه ی کار را در تصویر ۴۷ می توانید مشاهده کنید.

تصویر 47 - نتیجه حل تمرین

تصویر ۴۷ – نتیجه حل تمرین

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

یکی از ویژگی های ما انسان ها این است که اگر به ما بگویند در این قسمت حتماً یک عدد کمتر از ۳۰ وارد کنید؛ برای یکبار هم که شده عددی بیشتر از ۳۰ وارد می کنیم، ببینیم چه اتفاقی می افتد!! در یک کارنامه اعداد کوچکتر از صفر (منفی) و بزرگتر از ۲۰ معنی ندارند. برای جلوگیری از این کنجکاوی انسان ها می توانید از قابلیت Data Validation یا اعتبارسنجی داده ها در اکسل استفاده کنید.

برای اینکار ابتدا همه ی نمره ها از درس دینی تا درس شیمی را انتخاب و بعد از زبانه ی Data روی گزینه ی Data Validation (تصویر ۴۸) کلیک می کنیم.

تصویر 48 - محل قرارگیری Data Validation در نوار ابزار اکسل

تصویر ۴۸ – محل قرارگیری Data Validation در نوار ابزار اکسل

پنجره ی Data Validation مانند تصویر ۴۹ باز می شود.

تصویر 49 - پنجره Data Validation

تصویر ۴۹ – پنجره Data Validation

چون می خواهیم در سلول ها مقادیر عددی (نمره های درسی) وارد کنیم پس در زبانه ی اول یعنی تنظیمات (Settings)، قسمت Allow را در حالت عددی (Whole number) قرار می دهیم. با این کار قسمت Data فعال می شود. نمره ی هر درس، عددی بین صفر تا ۲۰ است. بنابراین در قسمت Data گزینه ی between (بین) را انتخاب و عدد صفر را در کادر Minimum (کمترین) و عدد ۲۰ را در کادر Maximum (بیشترین) می نویسیم. تصویر ۵۰ را ببینید.

تصویر 50 - ایجاد محدودیت با Data Validation

تصویر ۵۰ – ایجاد محدودیت با Data Validation

قبل از کلیک روی دکمه ی OK، نگاهی هم به دو زبانه ی دیگر می اندازیم. زبانه ی دوم (Input Message) مخصوص نوشتن یک راهنما یا شرح کوتاهی درباره ی فعالیت سلول است. تصویر ۵۱ را ببنید.

تصویر 51 - نوشتن پیام راهنما برای یک یا چند سلول در اکسل

تصویر ۵۱ – نوشتن پیام راهنما برای یک یا چند سلول در اکسل

زبانه ی آخر (Error Alert) هم مخصوص آن دسته از افرادی است که با وجود توضیح ما، باز هم عددی کمتر از صفر یا بیشتر از بیست وارد می کنند!! (تصویر ۵۲) در اصل این زبانه برای نوشتن یک «پیغام خطا» در نظر گرفته شده است.

تصویر 52 - نوشتن پیام خطا برای یک یا چند سلول در اکسل

تصویر ۵۲ – نوشتن پیام خطا برای یک یا چند سلول در اکسل

حالا روی دکمه ی OK کلیک می کنیم. الان سلول فعال مانند تصویر ۵۳ است.

تصویر 53 - نمونه ای از پیام راهنما در اکسل

تصویر ۵۳ – نمونه ای از پیام راهنما در اکسل

اگر برای نمونه در این سلول به جای نمره ی ۱۸ عدد ۲۱ را وارد کنیم، پیغام تصویر ۵۴ ظاهر خواهد شد.

تصویر 54 - نمونه ای از پیام خطا در اکسل

تصویر ۵۴ – نمونه ای از پیام خطا در اکسل

تابع جمع شرطی
فرض کنید جدول زیر هزینه های ماه فروردین پورتال یو سی (شما می توانید) باشد.

ردیف

تاریخ

هزینه

واحد (بخش)

مبلغ

۱

۱۳۸۹/۱/۱۹

هزینه پذیرایی

فرهنگی

۵۰۰,۰۰۰

۲

۱۳۸۹/۱/۲۳

هزینه تبلیغات

بازاریابی

۶۵۰,۰۰۰

۳

۱۳۸۹/۱/۲۳

هزینه ایاب و ذهاب

بازاریابی

۲۵,۰۰۰

۴

۱۳۸۹/۱/۲۶

هزینه بیمه

مالی

۱,۴۰۰,۰۰۰

۵

۱۳۸۹/۱/۲۷

هزینه نظافت

تدارکات

۱۵۰,۰۰۰

۶

۱۳۸۹/۱/۲۹

هزینه تبلیغات

بازاریابی

۴۲۶,۰۰۰

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

خب؛ در این جدول کلاً ۶ مورد هزینه داریم که دو مورد از آنها مربوط به تبلیغات هستند. پس بدون استفاده از اکسل و با کمک یک ماشین حساب ساده هم می توانیم به راحتی جمع هزینه ها را بدست آوریم. اگر هم بخواهیم حتماً از اکسل استفاده کنیم می توانیم بلافاصله اطلاعات را وارد کرده و با کمک تابع SUM مجموع هزینه ها را محاسبه کنیم.

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

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

برای نوشتن این فرمول در اکسل ابتدا در یکی از سلول ها کلیک و علامت = را قرار می دهیم. سپس تابع SUMIF را از قسمت More Functions پیدا کرده و انتخاب می کنیم تا تصویر ۵۵ باز شود.

تصویر 55 – پنجره تابع SUMIF

تصویر ۵۵ – پنجره تابع SUMIF

در این پنجره سه کادر دیده می شود. در کادر Range باید محدوده ی شرط را مشخص کنیم. در این کادر، اکسل از ما می خواهد که مشخص کنیم هزینه ی تبلیغات را باید از کدام ستون جدول پیدا کند. مطابق تصویر ۵۶ عبارت D3:D8 را وارد می کنیم.

تصویر 56 - مشخص کردن محدوده شرط در تابع SUMIF

تصویر ۵۶ – مشخص کردن محدوده شرط در تابع SUMIF

در کادر Criteria باید مشخص کنیم که اکسل در محدوده ی شرط به دنبال چه چیزی بگردد. در اصل باید شرط را مشخص کنیم. در اینجا هزینه ی تبلیغات مد نظر است. پس مطابق تصویر ۵۷ عبارت “هزینه تبلیغات” را تایپ می کنیم.

تصویر 57 - مشخص کردن شرط در تابع SUMIF

تصویر ۵۷ – مشخص کردن شرط در تابع SUMIF

در کادر Sum_range باید محدوده ی جمع را مشخص کنیم. اکسل عبارت «هزینه تبلیغات» را از محدوده ی شرط پیدا کرده و در این قسمت از ما می خواهد مشخص کنیم که در کدام ستون باید به دنبال مبلغ های این هزینه بگردد. مطابق تصویر ۵۸ عبارت F3:F8 را در کادر Sum_range وارد می کنیم.

تصویر 58 - معرفی محدوده جمع در تابع SUMIF

تصویر ۵۸ – معرفی محدوده جمع در تابع SUMIF

حالا روی دکمه ی OK کلیک می کنیم تا نتیجه ی کارمان را در تصویر ۵۹ ببینیم.

تصویر 59 - نتیجه مثال تابع SUMIF

تصویر ۵۹ – نتیجه مثال تابع SUMIF

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

برای محاسبه ی جمع هزینه های واحد بازاریابی ابتدا تابع SUMIF را انتخاب می کنیم. سپس در کادر Range ستون «واحد (بخش)» را به عنوان محدوده ی شرط معرفی می کنیم. در کادر Criteria هم عبارت مورد جستجو که «بازاریابی» است را می نویسیم. در کادر آخر «مبلغ» را به عنوان محدوده ی جمع مشخص می نماییم. به تصویر ۶۰ توجه کنید.

تصویر 60 - نحوه پر کردن فرم تابع SUMIF برای محاسبه هزینه های واحد بازاریابی

تصویر ۶۰ – نحوه پر کردن فرم تابع SUMIF برای محاسبه هزینه های واحد بازاریابی

اگر توجه کرده باشید قبل از کلیک روی دکمه ی OK نتیجه ی فرمول در قسمت Formula result (گوشه ی پایین سمت چپ) پنجره نشان داده شده است. تصویر ۶۱ را مشاهده کنید.

تصویر 61 - نتیجه ی فرمول در قسمت Formula result در اکسل

تصویر ۶۱ – نتیجه ی فرمول در قسمت Formula result در اکسل

تابع انتخاب
آیا تا به حال شده بخواهید با وارد کردن عددی در یک سلول، متن دلخواهی در سلول مجاور آن قرار بگیرد؟ مثلاً در دفاتر قانونی با وارد کردن شماره حساب، نام حساب بطور اتوماتیک در سلول مقابل نوشته شود. تابع CHOOSE در اکسل برای همین کار در نظر گرفته شده است. در این تابع می توانید از عدد ۱ تا ۲۵۴ را با نام دلخواه تان معرفی کنید. بطور مثال با استفاده از این تابع می توانید کاری کنید که هر وقت در سلول A1 عدد ۱ تایپ شد در سلول B1 عبارت «فروش» نوشته شود و یا هر وقت عدد ۲ تایپ شد عبارت «خرید» نوشته شود.

برای اینکار ابتدا در سلول B1 یک علامت = قرار می دهیم و سپس از قسمت تابع ها (More Functions)، تابع CHOOSE را پیدا و انتخاب می کنیم. پنجره ی تصویر ۶۲ باز می شود.

تصویر 62 - پنجره تابع CHOOSE

تصویر ۶۲ – پنجره تابع CHOOSE

در کادر Index_num باید سلولی را که قرار است در آن عدد نوشته شود (در اینجا A1) مشخص کنیم. Value1 همان عدد ۱ است. در کادر روبروی آن کلمه ی «فروش» را می نویسیم. به این ترتیب اگر در سلول A1 عدد ۱ نوشته شد، اکسل به سراغ Value1 می رود و متن روبروی آن را در سلول B1 می نویسد. Value2 هم همان عدد ۲ است. در کادر روبروی آن کلمه ی «خرید» را می نویسیم. تصویر ۶۳ را مشاهده کنید.

تصویر 63 - نحوه پر کردن تابع CHOOSE

تصویر ۶۳ – نحوه پر کردن تابع CHOOSE

نکته: اگر فرمولی را به درستی نوشتید ولی در سلول، پیغام #VALUE! ظاهر شد شاید مشکل این باشد که در سلول معرفی شده عددی قرار داده اید که در هنگام نوشتن فرمول تعریف نشده است.

تصویر ۶۴ نتیجه ی کارمان را نشان می دهد.

تصویر 64 - نتیجه استفاده از تابع CHOOSE

تصویر ۶۴ – نتیجه استفاده از تابع CHOOSE

سایر تابع های پُر کاربرد
در جدول زیر شرح مختصری از سایر تابع های مهم را ملاحظه می کنید.

تابع

شرح

INT

برای محاسبه ی قسمت صحیح یک عدد یا تبدیل عدد اعشاری به صحیح

مثال: =INT(8.9) → ۸

POWER

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

مثال: =POWER(3;3) → ۲۷

MOD

برای تعیین باقیمانده ی تقسیم

مثال: =MOD(9;2) → ۱

ODD

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

مثال: =ODD(14) → ۱۵

EVEN

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

مثال: =EVEN(15) → ۱۶

NOW

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

مثال: =NOW() → ۲۰۱۶/۱۱/۲۸ ۲۳:۰۸

TODAY

تاریخ روز را در سلول فعال درج می کند.

مثال: =TODAY() → ۲۰۱۶/۱۱/۲۸

HYPERLINK

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

مثال: =HYPERLINK(“D:\BOOK”)

ABS

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

مثال: =ABS(-10) → ۱۰

PRODUCT

این تابع اعداد وارد شده را در هم ضرب می کند.

مثال: =PRODUCT(1;2;3) → ۶

ROUNDDOWN

این تابع اعداد را رو به پایین گرد می کند.

مثال: =ROUNDDOWN(4.1;0) → ۴

TRUNC

این تابع فقط قسمت صحیح اعداد اعشاری را نشان می دهد.

مثال: =TRUNC(4.1) → ۴

MEDIAN

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

مثال: =MEDIAN(1;2;3;4;5;6) → ۳.۵

MODE

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

مثال: =MODE(1;2;3;2;4;2) → ۲

SQRT

این تابع جذر عدد را محاسبه می کند.

مثال: =SQRT(81) → ۹

YEAR

این تابع سال تاریخ درج شده را مشخص می کند.

مثال: =YEAR(2009/08/21) → ۲۰۰۹

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

درج تاریخ با: کنترل ( Ctrl ) + سیمیکالن ( ; )
درج زمان با: کنترل ( Ctrl ) + شیفت ( Shift ) + سیمیکالن ( ; )

تفاوت این روش با تابع های NOW و TODAY در این است که در این روش تاریخ و زمان درج شده، اعدادی مرده هستند. یعنی با گذشت زمان تغییر نمی کنند و این می تواند بسیار مفید باشد.

درس سوم »

لقمه های انگلیسی

دیدگاه خود را بنویسید

آدرس ایمیل شما منتشر نمی شود. بخش های الزامی با * مشخص شده اند. *

*