ایجاد فرمول بر اساس ارتباط خانه ها
یکی از اصلی ترین کاربردهای اکسل، امکان ایجاد و استفاده از فرمولهاست. با ایجاد فرمول توسط اطلاعات موجود در فایل، میتوانید محاسبات تکراری و گاها پیچیده را به اکسل بسپارید.
جهت محاسبه چهار عمل اصلی، از عملگرها استفاده می گردد که علائمی مثل + – * و / هستند و کاربرد هر کدام، همانند کاربردشان در ریاضی است که همه با آن آشنا هستیم. فصل مشترک تمام روابط برای شروع نوشتن فرمول، علامت مساوی = است. این علامت به برنامه اطلاع می دهد که شما در حال درج یک فرمول هستید و این اولین جزء هر فرمول است.
ضرب
با ضرب مقدار خانه B3 در مقدار خانه C3 شروع کنیم. برای اینکار در خانه ای که می خواهید نتیجه در آن نشان داده شود، در این مثال خانه D3 کلیک کرده و علامت = را تایپ کنید. حالا روی اولین خانه مورد نظر کلیک کرده و کلید ستاره * را از روی کیبورد کلیک کنید که علامت ضرب است. سپس در خانه دوم کلیک کرده و در پایان اینتر زده و یا روی علامت تیک سبز رنگ در نوار فرمول کلیک کنید. حال در خانه انتخاب شده، نتیجه فرمول خود را مشاهده خواهید کرد. جهت مشاهده خود فرمول، برروی خانه نتیجه دوبار کلیک کنید و یا با یکبار کلیک برروی خانه نتیجه، در نوار فرمول واقع درناحیه بالای صفحه، فرمول نمایش داده میشود.
تقسیم
این بار می خواهیم محتوای خانه B5 را بر محتوای خانه C5 تقسیم کنیم. با کلیک در خانه ای که می خواهیم نتیجه در آن مشاهده شود، و تایپ علامت = شروع می کنیم. حالا روی خانه اول کلیک کرده و علامت تقسیم را تایپ کنید. این علامت به این شکل / دیده می شود. حال روی خانه دوم کلیک کرده و در پایان، روی علامت تیک سبز رنگ در کنار نوار فرمول کلیک کنید.
کسر
جهت کسر کردن مقدار خانه C7 از مقدار خانه B7، ابتدا با کلیک درخانه ای که می خواهید حاصل از نمایش دهد و تایپ علامت = شروع کنید. حالا در اولین خانه مورد نظر کلیک کرده و علامت تفریق را نشان دهید. این علامت به این شکل – است. سپس در خانه دوم کلیک کرده و در پایان روی علامت تیک سبز رنگ در سمت چپ نوار فرمول کلیک کنید.
جـمع
برای جمع کردن مقادیر تعداد کمی خانه، ابتدا در خانه ای که می خواهید نتیجه را نشان دهد کلیک کنید و علامت = را در آن تایپ نمائید. در بین خانه های مورد نظر از علامت جمع + استفاده کرده و اینتر بزنید.
درصورتیکه بخواهید مقادیر چندین خانه در یک ستون را با هم جمع کنید، از تابعی به نام Sum استفاده می کنیم. این تابع تمام مقادیر خانه های متوالی یک سطر یا ستون را بدون نیاز به درج تک تک آنها، با هم جمع می کند. با تایپ = و سپس کلمه Sum و یک علامت پرانتز باز شروع کنید. در خانه D3 کلیک کرده و یک علامت دو نقطه : تایپ کرده و در خانه D8 کلیک کرده و علامت پرانتز بسته (D3:D8) تایپ کنید. علامت : به اکسل می گوید که خانه های انتخاب شده و تمام خانه های مابین آنها را با هم جمع کند. پرانتزها برای گروه بندی کردن بخشهای مختلف یک فرمول بکار می رود. بعنوان مثال ← (D3:D8) Sum =
نکته:
- وقتی فرمولی را وارد کردید، نتیجه آن، متناسب با مقادیر خانه های مرتبط با فرمول، تغییر می کند. بدین معنی که اگر عدد درج شده در هر کدام از خانه ها را تغییر دهید، حاصل فرمول در خانه نتیجه، تغییر خواهد کرد.
- در نظر داشته باشید که درصورتیکه در خانه نتیجه، عددی تایپ کنید، فرمول داده شده از بین میرود.
فرمول درصد در اکسل
جهت محاسبه درصدی در اکسل، ابتدا در خانه نتیجه کلیک کرده و از منوی Home قسمت Number، روی علامت درصد % کلیک کنید تا فرمت درصد اعمال گردد.
سپس در خانه نتیجه، فرمول محاسبه درصد را تایپ کنید که در اکسل بدینصورت است: بخش تقسیم بر کل = درصد
اگر آن را با فرمول ریاضی پایه برای درصد مقایسه کنید، متوجه خواهید شد که فرمول درصد اکسل فاقد قسمت * ۱۰۰ است. هنگام محاسبه درصدی در اکسل، لازم نیست کسر حاصل را در ۱۰۰ ضرب کنید، زیرا اگر فرمت درصد در سلول اعمال شده باشد، اکسل به طور خودکار این کار را انجام میدهد.
بعنوان مثال، در ستون B تعداد “مقدار سفارش‌داده شده” و در ستون C “مقدار تحویل‌شده” درج شده است. برای اطلاع از درصد مقدار محصولات تحویل‌شده، فرمول C2/B2= را در سلول D2 وارد کنید و آن را به تعداد ردیف مورد نیاز کپی کنید. سپس از سرپوشه Home قسمت Number روی علامت درصد % کلیک کنید (این کار را در ابتدای کار نیز می توانید انجام دهید).
آدرس دهی نسبی
Microsoft Excel هنگام کپی کردن خانه ها، بصورت پیش فرض، از آدرس دهی نسبی در فرمول ها استفاده می کند. به این معنی که وقتی در خانه ای یک فرمول ایجاد کرده و آن خانه را کپی می کنید، در خانه ای که اطلاعات Paste می شود، فرمول بر اساس موقعیت این خانه نسبت به خانه اولیه حاوی فرمول، کپی می شود.
در این مثال در خانه B2 فرمول A1= درج شده است. یعنی مقدار خانه B2 برابر است با خانه ای در یک ردیف بالاتر و یک ستون سمت چپ آن.
با کپی کردن این خانه در خانه B3، اکسل بطور خودکار، فرمول را به صورت A2= ثبت می کند، بدین معنی که پس از کپی کردن خانه حاوی فرمول، فرمول نتیجه به همان نسبتی که این خانه جابجا شده است، تغییر می کند.
آدرس دهی مطلق
در صورت نیاز به اینکه پس از کپی یا جابجایی خانه فرمول، آدرس خانه مبدا تغییر نکند، می توانیم از آدرس دهی مطلق استفاده کنیم. آدرس دهی مطلق با درج علامت $ در فرمول، قبل از قسمتی که می خواهید تغییر نکند، انجام می شود. ارجاع مطلق در این مثال به این شکل خواهد بود ← 1$A$=
وقتی ارجاع به حالت مطلق تغییر یافت، می توانید فرمول را بدون آنکه تغییر کند، به هر جای صفحه منتقل کنید و مقدار آن هیچ تغییری نخواهد کرد و همچنین می توانید یک خانه را بصورت ثابت در تمامی فرمول های کپی شده نگه دارید. در این مثال در فرمول درج شده در ستون C، جهت درصد سفارش هر محصول، بایستی مقدار هر سفارش بر عدد جمع کل (خانه B5) تقسیم گردد، پس مقدار این خانه، در تمامی فرمولها ثابت است و به شکل 5$B$ نوشته می شود ولی شماره ردیف های ستون C به ترتیب تغییر کرده است.
آدرس دهی مختلط
در آدرس دهی مختلط درفرمول، یا ستون و یا ردیف بصورت مطلق و دیگری بصورت نسبی نوشته می شود. بدین صورت که اگر قبل از نام ستون علامت $ گذاشته شود، ستون ثابت خواهد شد و اگر قبل از شماره ردیف این علامت قرار گیرد، شماره ردیف ثابت خواهد ماند.
بعنوان مثال اگر در خانه B3 مقدار A$2= را وارد کنید، مقدار این خانه با مقدار خانه یک ردیف بالاتر و یک ستون به سمت چپ آن برابر می شود. از آنجاییکه علامت $ فقط قبل از شماره ردیف قرار گرفته، ردیف شماره 2 در همه کپی ها از این خانه، ثابت خواهد ماند و فقط نام ستون ها تغییر خواهد کرد.
در این مثال، اگر فرمول خانه B3 را کپی کرده و در خانه C4 بازیابی (Paste) کنیم، از آنجاییکه مقدار ستون نسبی است، در فرمول بازیابی شده در خانه C4، ستون تغییر میکند و تبدیل به ستون B میشود، ولی بدلیل وجود علامت $ قبل از شماره ردیف، این مقدار ثابت می ماند.
حالا شما با کمک این مثال، استفاده از یک ارجاع مختلط را امتحان کنید.
ایجاد فرمول با استفاده از توابع اولیه
عملگرها، عملیات اولیه و محدودی را انجام میدهند و محاسبات پیچیده‌تر با کمک توابع انجام می‌شوند. در ادامه، تعدادی از توابع پرکاربرد معرفی می گردند:
تابع Sum در Excel، روش سریعی برای محاسبۀ جمع اعداد واقع در یک محدوده است. برای استفاده از این تابع، محدودۀ مورد نظر را انتخاب کرده و آیکون جمع را از نوار ابزار استاندارد کلیک کنید. حالا سعی کنید با استفاده از تابع Sum، اعداد ستون C، ردیف 2 تا 4 را جمع کنید.
تابع AVERAGE میانگین اعداد موجود در یک بازه را مشخص می کند، مثل متوسط میزان فروش به یک شرکت. برای اینکار در خانه ای که قرار است حاوی فرمول باشد کلیک کرده و = را تایپ کنید. بعد از آن کلمه AVERAGE را تایپ کرده و پرانتز را باز کنید. آدرس محدودۀ مورد نظر را تایپ کرده و پرانتز را ببندید. در پایان Enter را بزنید. حالا سعی کنید متوسط مقادیر ستون C، ردیف 2 تا 4 را بیابید.
تابع Max بیشترین مقدار یک محدوده را نمایش میدهند. ابتدا خانه ای که قرار است حاوی فرمول باشد را کلیک کرده و علامت = و پس از آن کلمه MAX را تایپ کنید. پرانتز را باز کنید، آدرس محدوده را تایپ کنید و پرانتز را ببندید و Enter را بزنید. حالا سعی کنید حداکثر مقدار ستون C، ردیف 2 تا 4 را تعیین کنید.
تابع Min کمترین مقدار یک محدوده را نمایش میدهد. ابتدا خانه ای که قرار است حاوی فرمول باشد را کلیک کرده و علامت = را تایپ کنید، بعد کلمه MIN را تایپ کرده و پرانتز باز کنید و آدرس محدده را درج کرده و پرانتز را ببندید و Enter را بزنید. حالا سعی کنید حداقل مقدار ستون C، ردیف 2 تا 4 را مشخص کنید.
تابع Count می تواند برای شمارش تعداد داده ها در محدوده ای از خانه ها به کار رود. اگر در محدوده انتخابی، خانه ای مقدار نداشته باشد، آن خانه در شمارش به حساب نخواهد آمد. حالا خانه ای که قرار است حاوی فرمول باشد را انتخاب کنید. علامت = و کلمه Count را تایپ کنید، پرانتز باز کنید. آدرس محدوده را تایپ کنید و پرانتز را ببندید. کلیدEnter را بزنید. حالا جهت مشاهده تعداد داده های ستون C، ردیف 2 تا 4 فرمول بنویسید.
نکته: همیشه پس از انتخاب محدوده ای از خانه ها، در نوار پایین صفحه، مقدار Count , Average و Sum مربوط به آن محدوده نمایش داده می شود، که به محض اینکه محدوده از حالت انتخاب خارج شود، این مقدار نیز پاک می شوند.
ایجاد فرمول با استفاده از تابع IF
گاهی می خواهید مقادیری که نشان داده می شوند، بسته به شرایط، مختلف باشد، مثلاً اگر مقادیر فروش مورد انتظار محقق شد، در خانه ی مورد نظر یک پیام “تبریک” نمایش داده شود و اگر مقدار فروش به میزان مورد نظر نبود، پیام “موقعیت از دست رفته” نشان داده شود. بعنوان مثال اگر جمع مقدار سفارش به بالاتر از 160 کیلو رسید، پیغام Well Done! نشان داده شود و اگر نه، پیغام Target Missed نشان داده شود.
ابتدا خانه ای که باید پیام را نشان دهد و حاوی فرمول باشد را انتخاب کرده و علامت = را تایپ نموده و سپس کلمه IF را تایپ نمایید. بلافاصله پرانتز باز، جمع مقدار محدوده مورد نظر را نوشته و پرانتز را ببندید، علامت بزرگتر از عدد جمع; را نوشته، پیام مورد نظر برای تحقق مقدار فروش “Well Done! ” را نوشته ; سپس پیام “Target Missed” برای وقتی که جمع فروش کمتر از مقدار مورد نظر باشد و در آخر پرانتز را ببندید.
=IF(SUM(B2:B4)>160;”Well Done!”;”Target Missed”)
حالا شما سعی کنید با استفاده از تابع IF با یک مثال مشابه بالا، با توجه به مقدار جمع، پیام های متفاوت خروجی بگیرید.