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

بعد از ظهر خوب

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

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

و بگذار شروع کنیم ...

1. عملیات اساسی و اصول. آموزش اکسل

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

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

فرمول باید با علامت "=" شروع شود. این پیش نیاز است بعد، شما چیزی را که لازم است محاسبه کنید بنویسید: به عنوان مثال، "= 2 + 3" (بدون نقل قول) و مطبوعات را وارد کنید - به عنوان نتیجه شما خواهید دید که نتیجه در سلول "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. اضافه کردن مقادیر در رشته ها (فرمول SUM و SUMMESLIMN)

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

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

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

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

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

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

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

= SUMMESLIMN (C2: C5؛ B2: B5؛ "<100")کجا:

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

B2: B5 - ستون که وضعیت بررسی می شود (به عنوان مثال قیمت، به عنوان مثال، کمتر از 100)؛

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

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

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

3. شمارش ردیف های رضایت بخش (فرمول COUNTIFSLIMN)

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

و همینطور ... شروع میکنیم

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

برای محاسبه کالاها در سلول مورد نظر، فرمول زیر را بنویسید (نگاه کنید به بالا):

= کشور (B2: B5؛ "> 90")کجا:

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

">90" - وضعیت خود را در نقل قول است.

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

فرمول به صورت زیر است:

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

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

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

4. جستجو و تعویض مقادیر از یک جدول به دیگری (فرمول CDF)

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

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

و بگذار شروع کنیم ...

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

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

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

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

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

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

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

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

5. نتیجه گیری

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

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

PS

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