فرمول ها چیزی هستند که به محبوبیت صفحات گسترده کمک کردند. با ایجاد فرمول، حتی اگر اطلاعات در سلول های مربوط به فرمول تغییر کند، می توانید محاسبات سریع انجام دهید. به عنوان مثال، شما می توانید یک سلول کل داشته باشید که تمام مقادیر را در یک ستون اضافه می کند.
اصول اولیه
همه فرمول های صفحه گسترده با علامت مساوی (=) شروع می شوند.
بعد از علامت مساوی، یک سلول یا تابع فرمول وارد می شود. تابع نوع فرمول را به صفحه گسترده می گوید.
اگر یک تابع ریاضی مانند جمع، تفریق و ضرب انجام شود، فرمول ریاضی در پرانتز احاطه شده است. توابع یا محاسبات ریاضی می توانند از عملگر استفاده کنند، از جمله به علاوه (+)، منهای (-)، ضرب (*)، تقسیم (/)، بزرگتر از (>)، و کمتر از (<).
استفاده از کولون (:) به شما امکان می دهد طیف وسیعی از سلول ها را برای فرمول بدست آورید. به عنوان مثال، A1:A10 سلول های A1 تا A10 است.
فرمول ها به طور پیش فرض با استفاده از مرجع سلول نسبی ایجاد می شوند و اگر علامت دلار ($) را در جلوی ستون یا ردیف اضافه کنید، به یک مرجع سلول مطلق تبدیل می شود.
وارد کردن فرمول صفحه گسترده
در زیر یک مثال تصویری متحرک از نحوه درج فرمول اکسل در صفحه گسترده آورده شده است. در اولین فرمول ما که در سلول “D1” وارد شده است، به صورت دستی فرمول =sum را وارد می کنیم تا 1+2 (در سلول های A1 و B2) را اضافه کنیم تا مجموع “3” را به دست آوریم. با مثال بعدی، ما از موس برای برجسته کردن سلول های A2 تا D2 استفاده می کنیم و سپس روی دکمه فرمول در اکسل کلیک می کنیم تا به طور خودکار فرمول ایجاد شود. در مرحله بعد، ما نشان می دهیم که چگونه می توانید به صورت دستی یک فرمول را وارد کنید، و سپس با استفاده از ماوس، مقادیر سلول را دریافت کنید (همچنین می توانید چندین سلول را برای ایجاد یک محدوده برجسته کنید). در نهایت، با استفاده از تابع sum به صورت دستی فرمول بار (*) را وارد می کنیم تا مقدار 5 * 100 را پیدا کنیم.
نمونه های فرمول
=
An = (برابر است) یک سلول برابر با دیگری ایجاد می کند. برای مثال، اگر بخواهید A1= را در B1 وارد کنید، هر مقداری که در A1 بود به طور خودکار در B1 قرار می گیرد. همچنین می توانید فرمولی ایجاد کنید که یک سلول را با بیش از یک مقدار برابر کند. به عنوان مثال، اگر سلول A1 نام کوچک و سلول B1 نام خانوادگی داشت، می توانید A1&” “&B1= را که ترکیبی از A1 با B1 است، با فاصله بین هر مقدار وارد کنید. همچنین می توانید از فرمول الحاقی برای ترکیب مقادیر سلول استفاده کنید.
میانگین
نمایش میانگین مقدار بین سلول ها. برای مثال، اگر می‌خواهید میانگین سلول‌های A1 تا A30 را بدست آورید، AVERAGE (A1:A30)= را تایپ کنید.
شمردن
تعداد سلول ها را در محدوده ای که فقط شامل اعداد است بشمارید. برای مثال، می توانید با استفاده ازCOUNT(A1:A15)= تعداد سلول های بین A1 و A15 را بیابید که دارای یک مقدار عددی هستند. اگر فقط سلول های A1 و A5 حاوی اعداد باشند، سلول حاوی این تابع “2” را به عنوان مقدار خود نشان می دهد.
COUNTA
تعداد سلول های یک محدوده حاوی هر متن (متن و اعداد، نه فقط اعداد) را بشمارید و خالی نیستند. برای مثال، می‌توانید تعداد سلول‌های حاوی متن در سلول‌های A1 تا A20 را با استفاده از =COUNTA(A1:A20) بشمارید. اگر هفت خانه خالی بود، فرمول عدد “13” (20-7=13) را برمی گرداند.
COUNTIF
سلول ها را با مقدار مشخصی بشمارید. به عنوان مثال، اگر در سلول A11 =COUNTIF(A1:A10,TEST”) داشته باشید، هر سلول بین A1 تا A10 با کلمه “test” به عنوان یک شمارش می شود. بنابراین، اگر پنج سلول در آن محدوده حاوی کلمه “test” دارید، مقدار “5” در سلول A11 نشان داده می شود (10-5=5).
IF
نحو دستور IF =IF(CELL=”VALUE”,”PRINT OR DO THIS”، “ELSE PRINT OR DO THIS”) است. برای مثال، فرمول =IF(A1=”””BLANK”””NOT BLANK”) باعث می‌شود هر سلولی غیر از A1 متن “BLANK” را نمایش دهد اگر A1 چیزی در آن نباشد. اگر A1 خالی نباشد، سلول ها متن “NOT BLANK” را نمایش می دهند. دستور IF کاربردهای پیچیده تری دارد، اما به طور کلی می توان آن را به ساختار بالا تقلیل داد.
استفاده از IF همچنین می تواند زمانی مفید باشد که بخواهید مقادیر را در یک سلول محاسبه کنید، اما فقط در صورتی که آن سلول ها دارای مقادیر باشند. به عنوان مثال، شما ممکن است مقادیر را بین دو سلول تقسیم کنید. با این حال، اگر چیزی در سلول ها وجود نداشته باشد، #DIV/0 را دریافت می کنید! خطا با استفاده از دستور IF، تنها زمانی می توانید یک سلول را محاسبه کنید که حاوی مقدار باشد. برای مثال، اگر فقط می‌خواهید یک تابع تقسیم را انجام دهید در صورتی که A1 حاوی یک مقدار باشد، نوع IF(A1=””,””,SUM(B1/A1))= را تایپ کنید، که اگر A1 حاوی مقدار باشد، تنها سلول B1 را بر A1 تقسیم می‌کند. . در غیر این صورت، سلول خالی می ماند.
INDIRECT
یک مرجع مشخص شده توسط یک رشته متنی را برمی گرداند. در مثال بالا، فرمول مقدار موجود در سلول A2 را برمی گرداند.
مقدار یک سلول تصادفی بین A1 و A10 را با استفاده از توابع غیرمستقیم و randbetween (در زیر توضیح داده شده) برمی‌گرداند.
MEDIAN
میانه مقادیر سلول های A1 تا A7 را پیدا کنید. به عنوان مثال، چهار میانه برای 1، 2، 3، 4، 5، 6، 7 است.
MIN AND MAX
Min و Max نشان دهنده حداقل یا حداکثر مقدار در سلول ها هستند. به عنوان مثال، اگر می خواهید حداقل مقدار را بین سلول های A1 و A30 به دست آورید، MIN(A1:A30)= یا MAX(A1:A30)= را تایپ کنید تا حداکثر را بدست آورید.
PRODUCT
دو یا چند سلول را با هم ضرب می کند. به عنوان مثال، PRODUCT(A1:A30)= چندین سلول از A1 تا A30 را با هم خواهد داشت (یعنی A1 * A2 * A3 و غیره).
RAND
یک عدد تصادفی بزرگتر از صفر، اما کمتر از یک ایجاد می کند. به عنوان مثال، “0.681359187” می تواند یک عدد به طور تصادفی تولید شده در سلول فرمول باشد.
RANDBETWEEN
یک عدد تصادفی بین دو مقدار تولید کنید. در مثال بالا، فرمول یک عدد صحیح تصادفی بین 1 تا 100 ایجاد می کند.
یک عدد را به یک عدد مشخص اعشار گرد کنید. X سلول حاوی عددی است که باید گرد شود. Y تعداد ارقام اعشار برای گرد کردن است. در زیر نمونه هایی آورده شده است.
عدد سلول A2 را به یک رقم اعشار گرد می کند. اگر عدد 4.7369 باشد، مثال بالا این عدد را به 4.74 تبدیل می کند. اگر عدد 4.7614 باشد، به 4.76 می رسد.
عدد سلول A2 را به صفر اعشار یا نزدیکترین عدد صحیح گرد می کند. اگر عدد 4.736 باشد، مثال بالا آن عدد را به 5 گرد می کند. اگر عدد 4.367 باشد، به 4 گرد می شود.
SUM
متداول ترین تابعی که برای افزودن، تفریق، چند برابر کردن یا تقسیم مقادیر در سلول ها استفاده می شود. در زیر نمونه های مختلفی از این تابع آورده شده است.
سلول های A1 و A2 را اضافه کنید.
سلول های A1 تا A5 را اضافه کنید.
سلول های A1، A2 و A5 را اضافه می کند.
سلول A1 را از A2 کم می کند.
سلول های A1 و A2 را ضرب می کند.
سلول A1 را بر A2 تقسیم می کند.
SUMIF
تابع SUM را فقط در صورتی انجام دهید که در اولین سلول های انتخاب شده مقدار مشخصی وجود داشته باشد. نمونه ای از این =SUMIF(A1:A6,TEST,B1:B6) است که فقط در صورتی مقادیر B1:B6 را اضافه می کند که کلمه “test” جایی بین A1:A6 وارد شده باشد. بنابراین اگر “test” (بدون حساس به حروف بزرگ) را در A1 وارد کرده باشید، اما اعدادی را در B1 تا B6 داشته باشید، فقط مقدار B1 را اضافه می کند زیرا “test” در A1 است.
TODAY
تاریخ جاری را در سلول حاوی فرمول چاپ می کند. هر بار که صفحه گسترده خود را باز می کنید، مقدار تغییر می کند تا تاریخ و زمان فعلی را منعکس کند. برای وارد کردن تاریخی که تغییر نمی کند، Ctrl و ; را فشار دهید. (نقطه ویرگول) برای وارد کردن تاریخ.
TREND
مقدار مشترک سلول ها را پیدا کنید. به عنوان مثال، اگر سلول های A1 تا A6 دارای 2،4،6،8،10،12 باشند و فرمول TREND(A1:A6)= را در سلول دیگری وارد کرده باشید، مقدار 2 را دریافت خواهید کرد زیرا هر عدد با دو واحد افزایش می یابد.
TRIM
فرمول trim به شما این امکان را می دهد که هر فضای غیر ضروری را در ابتدا یا انتهای یک مقدار در یک سلول حذف کنید.
VLOOKUP
فرمول جستجو، hlookup یا vlookup به شما امکان می دهد مقادیر مرتبط را برای نتایج برگشتی جستجو و پیدا کنید.