چگونه می توان فرمول را در اکسل نوشت؟ آموزش بیشترین فرمول مورد نیاز

عصر بخیر

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

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

بنابراین ، بیایید شروع کنیم ...

1. عملیات اساسی و اصول. اصول اولیه اکسل را بیاموزید.

کلیه اقدامات موجود در مقاله در نسخه اکسل 2007 نمایش داده خواهد شد.

جدول ما بعد از شروع برنامه اکسل - پنجره ای با سلولهای زیادی ظاهر می شود. ویژگی اصلی برنامه این است که می تواند فرمول های شما را که می نویسید (به عنوان یک ماشین حساب) بخواند. به هر حال ، شما می توانید یک فرمول به هر سلول اضافه کنید!

فرمول باید با علامت "=" شروع شود. این یک پیش نیاز است. سپس آنچه را که باید محاسبه کنید ، می نویسید: به عنوان مثال ، "= 2 + 3" (بدون نقل قول) و کلید Enter را فشار دهید - در نتیجه می بینید که نتیجه "5" در سلول ظاهر می شود. تصویر زیر را مشاهده کنید.

مهم! علیرغم اینکه شماره "5" در سلول A1 نوشته شده است ، با فرمول ("= 2 + 3") محاسبه می شود. اگر در سلول بعدی به سادگی متن "5" را بنویسید - سپس وقتی روی این سلول در ویرایشگر فرمول شناور هستید (خط بالا ، Fx) - شماره اصلی "5" را خواهید دید.

حال تصور کنید که در یک سلول می توانید نه تنها مقدار 2 + 3 بلکه تعداد سلولهایی که مقادیر مورد نیاز آنها را اضافه کنید بنویسید. بیایید بگوییم "= B2 + C2".

به طور طبیعی ، باید تعدادی در B2 و C2 وجود داشته باشد ، در غیر اینصورت اکسل در سلول A1 به ما نشان می دهد که نتیجه 0 است.

و یک نکته مهم دیگر ...

هنگامی که شما یک سلول را که در آن یک فرمول وجود دارد ، به عنوان مثال A1 - و آن را در یک سلول دیگر قرار دهید - کپی می کنید ، مقدار "5" نیست که کپی شده باشد ، بلکه خود فرمول است!

علاوه بر این ، فرمول به نسبت مستقیم تغییر خواهد کرد: یعنی. اگر A1 در A2 کپی شود ، فرمول موجود در سلول A2 "= B3 + C3" خواهد بود. اکسل به طور خودکار فرمول خود را تغییر می دهد: اگر A1 = B2 + C2 باشد ، منطقی است که A2 = B3 + C3 (همه اعداد با 1 افزایش یابد).

نتیجه ، به هر حال ، در A2 = 0 است ، زیرا سلول های B3 و C3 تعریف نشده اند ، بنابراین برابر با 0 است.

بنابراین ، می توانید یک بار فرمول را بنویسید ، و سپس آن را در تمام سلولهای ستون مورد نظر کپی کنید - و اکسل در هر ردیف جدول خود محاسبه می کند!

اگر نمی خواهید B2 و C2 در حین کپی کردن تغییر کنند و همیشه به این سلول ها وصل شوند ، کافیست نماد "$" را به آنها اضافه کنید. مثالی در زیر آمده است.

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

2. افزودن مقادیر در ردیف ها (فرمول های SUMM و SUMMESLIMN)

البته با ساختن فرمول A1 + A2 + A3 و غیره می توانید هر سلول را اضافه کنید. اما برای اینکه رنج نبرید ، یک فرمول ویژه در اکسل وجود دارد که تمام مقادیر را در سلولهایی که انتخاب می کنید اضافه می کند!

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

برای انجام این کار ، به سلولی بروید که در آن نتیجه نمایش داده می شود و فرمول را بنویسید: "= SUM (C2: C5)". تصویر زیر را مشاهده کنید.

در نتیجه ، تمام سلولهای موجود در محدوده انتخاب شده خلاصه می شوند و نتیجه را می بینید.

2.1. علاوه بر شرایط (با شرایط)

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

یک فرمول عالی برای این وجود دارد. "SUMMESLIMNبلافاصله یک مثال و سپس توضیحی در مورد هر نماد در فرمول.

= جمع (C2: C5؛ B2: B5؛ "<100")جایی که:

C2: C5 - آن ستون (آن سلول ها) که به آن اضافه می شوند.

B2: B5 - ستونی که با آن شرایط بررسی می شود (مثلاً قیمت ، مثلاً کمتر از 100).

"<100" - خود شرط ، توجه داشته باشید که شرط در سهمیه بندی نوشته شده است.

هیچ چیز پیچیده ای در این فرمول وجود ندارد ، نکته اصلی رعایت تناسب است: C2: C5 ؛ B2: B5 - درست؛ C2: C6 ؛ B2: B5 - اشتباه. من دامنه جمع و دامنه شرایط باید متناسب باشد ، در غیر این صورت فرمول خطایی را برمی گرداند.

مهم! برای مبلغ می تواند شرایط زیادی وجود داشته باشد ، یعنی شما می توانید با ستون 1 ، بلکه در 10 مورد را فوراً چک کنید و شرایط زیادی را تنظیم کنید.

3. شمارش تعداد ردیف هایی که شرایط را برآورده می کنند (فرمول COUNTIFLY است)

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

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

در همان مثال ، سعی می کنیم تعداد کالاها را با قیمتی بالاتر از 90 محاسبه کنیم (اگر نگاه کنید می توانید بگویید که 2 محصول وجود دارد: نارنگی و پرتقال).

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

= حسابداری (B2: B5؛ "> 90")جایی که:

B2: B5 - محدوده ای که با توجه به شرایط تعیین شده توسط ما ، آنها را بررسی خواهیم کرد.

">90" - خود شرط در نقل قول ها محصور شده است.

حال بیایید کمی مثال خود را پیچیده کنیم و با توجه به یک شرط دیگر یک حساب اضافه کنیم: با قیمت بیش از 90 + مقدار موجود در انبار کمتر از 20 کیلوگرم است.

فرمول شکل می گیرد:

= COUNTIFLY (B2: B6؛ "> 90"؛ C2: C6؛ "<20")

در اینجا همه چیز یکسان است ، به جز یک شرط دیگر (C2: C6؛ "<20") به هر حال ، چنین شرایط زیادی می تواند وجود داشته باشد!

مشخص است که هیچ کس چنین فرمولهایی را برای چنین جدول کوچک نمی نویسد ، اما برای جدول چند صد ردیف ، این یک موضوع کاملاً متفاوت است. به عنوان مثال ، این جدول بیش از تصویری است.

4- جستجو و جایگزینی مقادیر از جدول به جدول دیگر (فرمول VLOOKUP)

تصور کنید که جدول جدیدی به ما رسیده است ، با قیمت های جدید برای این محصول. خوب ، اگر موارد 10-20 باشد ، می توانید همه آنها را به صورت دستی تنظیم مجدد کنید. و اگر صدها مورد از این قبیل وجود دارد؟ خیلی سریع تر است اگر Excel به طور مستقل در نام های منطبق از یک جدول به جدول دیگر پیدا شود ، و سپس برچسب های جدید قیمت را در جدول قدیمی ما کپی کند.

برای چنین کاری از فرمول استفاده می شود VPR. در یک زمان ، او با فرمول های منطقی "IF" "عاقل" بود تا اینکه این چیز خارق العاده را ملاقات کرد!

بنابراین ، بیایید شروع کنیم ...

در اینجا مثال ما + یک جدول جدید با برچسب قیمت است. حال باید به طور خودکار برچسب قیمت جدید را از جدول جدید در جدول قدیمی جایگزین کنیم (برچسب قیمت جدید قرمز است).

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

= VLOOKUP (A2؛ D D $ 2: $ 5 $؛ 2)کجا

A2 - ارزشی که ما در جستجوی برچسب قیمت جدید خواهیم داشت. در مورد ما ، ما در جدول جدید به دنبال کلمه "سیب" هستیم.

D $ 2 دلار: $ 5 $ - جدول جدید ما را کاملاً انتخاب کنید (D2: E5 ، انتخاب از گوشه بالا سمت چپ به مورب سمت راست پایین می رود) ، یعنی. جایی که جستجو انجام خواهد شد علامت "$" در این فرمول ضروری است به طوری که وقتی این فرمول را در سلولهای دیگر کپی می کنید - D2: E5 تغییر نکند!

مهم! جستجوی کلمه "سیب" فقط در ستون اول جدول انتخابی شما انجام خواهد شد ، در این مثال "سیب" در ستون D جستجو می شود.

2 - هنگامی که کلمه "سیب" یافت شد ، عملکرد باید بداند که از کدام ستون جدول انتخاب شده (D2: E5) برای کپی کردن مقدار مورد نظر. در مثال ما ، از ستون 2 (E) کپی کنید ، زیرا در ستون اول (D) جستجو کردیم. اگر جدول انتخابی شما برای جستجو شامل 10 ستون باشد ، سپس ستون اول جستجو می شود ، و از 2 تا 10 ستون - می توانید شماره ای را برای کپی انتخاب کنید.

به فرمول = VLOOKUP (A2؛ D D $ 2: $ 5 $؛ 2) مقادیر جدید را برای نام های دیگر محصولات جایگزین کنید - فقط آن را با برچسب های قیمت محصول در ستون های دیگر در ستون کپی کنید (در مثال ما ، سلول های B3: B5 را کپی کنید). فرمول به طور خودکار مقدار را از ستون جدول جدید مورد نظر خود جستجو و کپی می کند.

5- نتیجه گیری

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

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

PS

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