درج جداکننده هزارگان در اکسل

علائم ظاهری این خطا در Excel و نحوه بیان آن از طرف شما

* هر بار که Excel را باز می‌کنم یک پیغام می‌دهد و با OK کردن نیز رفع نمی‌شود و بار دیگر نشان داده می‌شود.
* یک فلش آبی رنگ روی صفحه Excel می‌آید و اصلا انتخاب و پاک نمی‌شود.
* نتیجه فرمول SUM عدد صفر می‌شود و این در حالی است که من کلی عدد در محدوده Sum دارم.

بله، شما به یکی از اشکال فوق سوالتان را می‌پرسید و جواب همه آنها در Excel رخ دادن خطای Circular Reference است که باید آنرا پاک یا کنترل نمایید.

توضیح Circular Reference در Excel

برای توضیح این خطا یک مثال ساده آورده می‌شود تا دقیقا بفهمید که چه بر سر Excel  می‌آید. فرض کنید که در یک اداره رفته‌اید و باید نامه‌ای امضا شود، آقای کارمند الف می‌گوید ” نامه شما ابتدا باید به  تایید آقای جیم برسید ” و وقتی که به اتاق آقای “جیم” می‌روید، می‌گوید “این نامه ابتدا باید توسط آقای الف کارشناسی و تایید شود”.
و نتیجه این کار پاس‌کاری شما یا قرار گرفتن در یک “دور باطل” یا به قول ما کامپیوتری‌ها Loop خواهد بود که در Excel به این حالت Circular Reference می‌گویند.

Circular Reference در Excel

 در خانه B2 فرمولی را بنویسید که اشاره به خانه E2 داشته باشد (مهم نیست چه فرمولی، اما در آن حتما E2 آمده باشد) سپس در خانه E2 فرمولی را بنویسید که اشاره به خانه B2 داشته باشد.
بعد زدن کلید Enter در سلول دوم Excel بلافاصله پیغام زیر را به شما نشان می‌دهد و اگر کلید OK را بزنید Help نرم افزار Excel باز شده و Circular Reference را توضیح می‌دهد.

البته حالت‌های دیگری نیز می‌توان مثال زد، مثلا شما در سلول B2 فرمولی مانند زیر را بنویسید. چون خود سلول در محدوده تابع SUM است در نتیجه Circular Reference ایجاد خواهد شد.

آوردن فلش آبی رنگ در اکسل

بعد اینکه این حالت را ایجاد کردید، اگر خانه E2 را ویرایش کنید (روی E2 کلید F2 را بزنید و سپس Enter) این فلش به صورت خودکار نمایش داده می‌شود و بیانگر ایجاد  Circular Reference در Excel است.

روش دیگری برای تشخیص Circular Reference

هموار در Status Bar شما می‌توانید آدرس اولین سلولی که دچارCircular Reference  شده است را ببنید. که به محض رفع کردن خطای این سلول، آدرس سلولهای بعدی را نشان می‌دهد.

از ریبون (به نوار ابزار Excel 2007 ریبون می‌گویند) گزینه Formula و سپس مانند شکل زیر اقدام نمایید:

نتیجه فرمولهایی که به سلول‌های Circular Reference اشاره دارند، همواره صفر است

تا اینجا با مفهوم Circular Reference در Excel آشنا شده‌اید و حال به نحوه رفع این خطای آشنا خواهیم شد.

روش رفع خطا در اکسل                                                                                                                

دو راه کلی برای رفع این خطا وجود دارد که راه اول پاک کردن یا اصلاح فرمولی است که باعث CR شده است که سلولی که باعث CR شده است را ویرایش کرده و فرمول آنرا طوری اصلاح می‌کنیم که باعث Loop نشود.
راه حل دیگر فعال کردن گزینه Iteration در Excel Options است. در این وضعیت ما CR را کنترل می‌کنیم و نه حذف. بدین ترتیب که به Excel می‌گوییم که اگر در جایی دچار Loop شد، این Loop را 100 بار اجرا کن (این حلقه را 100  بار ادامه بده) و سپس کار را تمام کن.
برای فعال کردن وضعیت Iteration در Excel Options گروه Formula و سپس Enable iterative Calculation را در حالت فعال قرار می‌دهیم و تعداد دفعات اجرای را تعیین می‌کنیم.

استفاده از این تکنیک برای ساختن Loop در اکسل اصلا توصیه نمی‌شود زیرا شما باید Calculation رادر حالت Manual قرار دهید و … و در ضمن ابزارهای کنترلی برای خارج شدن از Loop به سختی ساخته می‌شوند و این در حالی است که در VBA این کار به سادگی با دستوراتی مانند DO-LOOP  /  FOR-NEXT قابل اجراست و اگر شما در کتابهای مرجع Excel و یا اینترنت جستجو کنید خواهید دید که از این قابلیت سخنی به میان نرفته است و دلیل آن همان است که ذکر شد.

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

10٫4 به صورت عدد 10 روند می شود.
10٫6 به صورت عدد 11 روند می شود.
10٫5 به صورت عدد 11 روند می شود

=Round(12.3456,1)

Result: 12.3

آرگومان دوم عدد 1 است. این عدد با یک رقم اعشار روند می شود.

=Round(12.3456,2)

Result: 12.34

آرگومان دوم عدد2 است.این عدد با دو رقم اعشاری روند می شود. توجه داشته باشید که عدد اعشاری.3456 تبدیل به.35 می شود. این امر به این دلیل است که قسمت.456 مربوط به مقدار اعشاری. با توجه به نزدیکترین رقم با رقم اعشاری دوم که.05 است. روند می شود.

روند کردن در یک جهت – توابع (Roundup – RoundDwon)

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

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

=Roundup(150.255,0)

Result:151

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

=Roundup(150.255,1)

Result: 150.3

آرگومان دوم عدد 1 است. نتیجه به این صورت یک رقم اعشاری روند می شود. توجه داشته باشید که مقدار اعشاری کامل.255 به مقدار.3 روند بسته می شود. این امر در تابع استاندارد Round هم اتفاق می افتد.

=RoundDown(155.798,2)

Result: 155.79

آرگومان دوم عدد 2 است. نتیجه به صورت عددی با یک رقم اعشاری روند بسته می شود. توجه داشته باشید که قسمت اعشاری.798 تبدیل به.79 می شود. تابع استاندارد Round این رقم اعشاری را به صورت.8 روند می کند.

روند کردن با عدد زوج یا عدد فرد بعدی – توابع (Even – Odd)

توابع Even و ODD اعداد را با دور کردن آنها از 0 روند می کنند. تابع Even یک عدد را به بالاترین عدد صحیح زوج بعدی روند خواهد کرد و تابع ODD یک عدد را به بالاترین عدد صحیح فرد بعدی روند خواهد کرد.

=Even(3)

Result: 4

با دور شدن از 0 روند کردن را به طرف عدد صحیح زوج بعدی. انجام می دهد.

=Even(4)

Result: 4

چون 4 یک عدد زوج است. روند کردن اتفاق نمی افتد. خود عدد 4 برگردان می شود

=Even(4.01)

Result: 6

با دور شدن از 0. روند کردن را به طرف عدد صحیح زوج بعدی انجام می دهد.

=ODD(4)

Result: 5

با دور شدن از 0. روند کردن را به طرف عدد صحیح فرد بعدی انجام می دهد.

حذف تمام ارقام اعشاری به وسیله تابعINT   در excel

تابع INT یک عدد ره به طرف پایین به سوی کم ترین عدد صحیح بعدی روند می کند. اثر آن این است که به نظر می رسد که قسمت اعشاری حذف شده است و غالبا INT برای راحت کردن این کار به کار می رود.
INT هنگامی مورد استفاده قرار می کیرد که تمام چیزی که نیاز به دانستن آن دارید. قسمت عدد صحیح یک عدد یا قسمت عدد صحیح یک محاسبه است.

=INT(25.27)

Result: 25

حذف برخی از ارقام اعشاری به وسیله تابع TRUNC

تابع TRUNC قسمتی از یک عدد را حذف می کند. این تابع دو آرگومان می گیرد. آرگومان اول. عددی است که باید تغییر کند. آرگومان دوم مشخص می کند که چه تعداد ارقام این عدد باید باقی بماند. مقدار تعیین شده 2 برای آرگومان دوم بیان می کند که 2 رقم اعشاری باید باقی بماند. مقدار 1 برای این آرگومان به این معنی است که 1 رقم اعشاری باید باقی بماند. این تابع اعداد را روند نمی کند. زیرا اعداد را کوتاه می کند.

=TRUNC(212.65,2)

Result: 212.65

=TRUNC(212.65,1)

Result: 212.6

روند تا دو رقم اعشار

Example: 1.2649 to 1.26
Round(A1,2)

روند به بالا تا دو رقم اعشار

Example: 1.2649 to 1.27
Roundup(A1,2)

روند به نزدیکترین عدد صحیح

Example: 1.2649 to 1
=Round(A1,0)

روند به نزدیکترین مضرب 10

Example: 544.234 to 550
Roundup(A1,-1)

روند به نزدیکترین مضرب عدد 2

Example: 43 to 44
MRound(A1,2)

جدا کردن مقدار صحیح یک عدد اعشاری

Example: -23.34 to -23
TRUNC(A1,0)

روند به نزدیکترین یک چهارم

Example: 19.14 to 19.25

MRound(A1,0.25)

اگر شما جز کاربران نرم فزار اکسل هستید حتما بارها و بارها مجبور به انجام عملیات ضرب در اکسل شده اید و برای انجام عمل ضرب در اکسل از نماد ریاضی * استفاده کرده اید. اما اگر مجبور باشید عمل ضرب را بر روی سلول های زیادی انجام دهید یا به عبارت دیگر اگر قصد داشته باشید اعداد موجود در محدوده A1 تا A100 را در یکدیگر ضرب نمایید. آیا استفاده از نماد * و انتخاب تک تک سلول ها خسته کننده و زمان بر نخواهد بود؟ اکسل برای حل این مشکل تابعی را به شما معرفی خواهد کرد که می توانید با استفاده از این تابع عمل ضرب در اکسل را به آسانی و در سریع ترین زمان ممکن انجام دهید.

ضرب اعداد به وسیله تابع Product

تابع Product تمام اعدادی را که به صورت آرگومان به آن داده شده است را در یکدیگر ضرب خواهد کرد و نتیجه را برگردان خواهد کرد. برای مثال اگر سلول A1 و A2 حاوی عدد باشند شما می توانید با استفاده از فرمول زیر این دو عدد را در یکدیگر ضرب نمایید.

=Product(A1,A2)

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

=A1*A2

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

PRODUCT(number1, [number2], …)

Number 1: اولین عدد یا محدوده برای انجام عملیات ضرب.

Number 2: عدد یا محدوده های اضافی برای انجام عملیات ضرب. می توانید تا 255 آرگومان در این تابع استفاده کنید.

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

=PRODUCT(A1:A3)

=PRODUCT(A1,A2,A3)

=PRODUCT(A1:A3,B1:B2)

=PRODUCT(A1,B2,C2)

=A1*A2*A3

استفاده از تابع Sum Product برای ضرب و جمع زدن مقادیر هم موقعیت در دامنه های مختلف درEXCEL

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

SumProduct(َArray1,Array2,Array3,…..)

آرگومان های مربوط به تابع Sumproduct باید دامنه ها باشند. هر چند که یک دامنه می تواند تنها یک سل تکی باشد. آنچه مورد نیاز است. این است که تمام این دامنه ها دارای یک اندازه باشند. یعنی تعداد ردیف ها و ستون های آنها برابر باشد. تا 30 دامنه و حداقل 2 دامنه مورد نیاز است.این تابع ابتدا با ضرب کردن اجزا از طریق موقعیت آنها در طول دامنه ها کار خود را انجام می دهد و سپس نتایج را با هم جمع می زند. برای مشاهده چگونکی عملکرد این تابع نگاهی به شکل زیر داشته باشید. سه دامنه از مقادیر وجود دارد. برای ساده تر کردن توضیح این مطلب به جای اعداد از حروف الفبا استفاده کرده ایم. تصور کنید که فرمول زیر را در تابع وارد کرده اید.

SumProduct(B2:C4,E2:F4,H2:I4)

نتیجه از طریق موارد زیر محاسبه خواهد شد:

1- ضرب A در H در N و ذخیره نتیجه.

2- ضرب D در K در Q و ذخیره نتیجه.

3- ضرب E در L در O و ذخیره نتیجه.

4- و ……….

5- و در آخر جمع زدن نتایج برای به دست آوردن پاسخ نهایی.

اکسل این امکان را فراهم می‌کند که داده‌ها را به صورت یک شکل به نمایش درآوریم. این ابزار مایکروسافت امکان درج فایل PDF را فراهم می‌آورد.

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

درج فایل PDF در صفحه اکسل
به تب Insert بروید و در گروه Text بروی Object کلیک کنید.

در جعبه محاوره‌ای Object، در تب Create New، زیر قسمت Object Type گزینه Adobe Acrobat Document را انتخاب کنید. برای انجام این کار نیاز دارید که Adobe Acrobat را از قبل بر روی سیستم خود نصب کرده باشید. در غیر این صورت در لیست Object Type این گزینه را مشاهده نخواهید کرد.
ممکن است شما نرم‌افزارهایی که امکان باز کردن و خواندن فایل PDF را فراهم می‌کند بر روی سیستم خود نصب کرده باشید. می‌تواند آن را انتخاب کنید. مطمئن باشید که گزینه Display as Icon را انتخاب کرده باشید.

گزینه Ok را انتخاب کنید و یک جعبه محاوره‌ای باز می‌شود. فایل PDF موردنظرتان را انتخاب کنید و گزینه Open را کلیک کنید. اکنون فایل PDF باز می‌شود و شما باید آن را ببندید.
اکنون فایل PDF به اکسل اضافه شده است. می‌توانید آن را درگ کنید و یا تغییر اندازه دهید. این مراحل را برای درج سایر فایل‌های PDF تکرار کنید.

تنظیم فایل PDF در سلول اکسل
می‌توان اندازه فایل PDF را به گونه‌ای که کاملا متناسب با سلول باشد تغییر داد. فایل PDF مخفی نمی‌شود و با سلول‌های دیگر فیلتر نمی‌شود. یک راه وجود دارد که می‌توان مطمئن شد که فایل متناسب با سلول‌ باشد.

بر روی فایل درج شده راست کلیک کنید و Format Object را انتخاب کنید. اگر چندین فایل PDF دارید، می‌توانید همه فایل‌ها را انتخاب کنید، راست کلیک کنید و در آخر Format Object را انتخاب کنید.

جعبه‌ Format Object را باز کنید. تب Properties را انتخاب کنید و گزینه Move and size with cells را انتخاب کنید. Ok را کلیک کنید.

تغییر نام فایل PDF
اگر توجه کرده باشید، فایل PDF همان نام پیش‌فرض خود را دارد. شما می‌توانید نام دیگری به فایل بدهید.

برای تغییر نام بر روی فایل PDF راست کلیک کنید و گزینه Convert را انتخاب کنید.

در جعبه Convert بر روی Change Icon کلیک کنید.

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

یکی از قابلیت‌های نرم‌افزار اکسل جستجو و جایگزینی کلمات و عبارت‌ها است. اما فرض کنید که فایل اکسلی با 1000 ردیف و 20 ستون دارید که می‌خواهید بیش از 20 کلمه یا عدد را به طور همزمان با 20 کلمه یا عدد دیگر جایگزین کنید. شاید به ذهنتان برسد که عملیات جستجو و جایگزینی را برای تک تک این کلمات یا اعداد بصورت جداگانه انجام دهید. اما این کار بسیار زمان‌بر و همراه با خطا است. پس چاره کار چیست؟ در ادامه مطلب با آموزش اکسل امروز  همراه باشید تا با نحوه انجام اینکار آشنا شوید.

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

برای جستجو و جایگزینی گروهی کلمات در اکسل، 3 روش وجود دارد:

روش اول) ماکرونویسی دراکسل

برای ایجاد ماکرو، کلیدهای Alt + F11 را فشار دهید یا از تب Developer قسمت code گزینه Visual Basic را انتخاب کنید. پنجره Microsoft Visual Basic ظاهر می‌شود. در این پنجره از تب Insert گزینه Module را انتخاب نمائید.

پس از کلیک روی OK، باید محدوده‌ی جایگزینی (دو ستون واژه اصلی و واژه جایگزین) را انتخاب کنید.

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

برای اینکار 3 روش وجود دارد:

روش اول) روش آسان‌تر ولی غیرمستقیم

برای مثال اگر داده‌ها بصورت زیر باشد، طبق مراحل زیر عمل کنید:

1) در کنار داده‌ها به یک ستون خالی نیاز داریم. بطور مثال برای داد‌های بالا از ستون F استفاده می‌کنیم.

2) در سلول F1 عدد 1 و در سلول F2 عدد 2 را درج می‌کنیم.

3) اعداد 1 و 2 را انتخاب می‌کنیم و روی علامت fill handle دو بار کلیک می‌کنیم. 

اکسل بطور خودکار سلول‌های ستون F را پر می‌کند. بطور مثال از 1 تا 9

4) سپس از سلول‌های پر ستون F (از F1 تا F9) کپی می‌گیریم و در سلول F10 پیست می‌کنیم.

5) اکنون سلول‌های ستون F (از F1 تا F18) را انتخاب می‌کنیم و از تب Data روی دکمه Sort A to Z کلیک می‌کنیم.

6) پس از کلیک پیغام زیر ظاهر می‌شود گزینه Expand the selection راتیک می‌زنیم.

7) در پایان روی دکمه Sort کلیک کنیم. نتیجه زیر حاصل می‌شود.

* اگر می‌خواهید بجای یک ردیف خالی، 2 یا 3 ردیف خالی اضافه کنید باید 2 یا 3 بار اعدادی که در ابتدا در ستون F درج کردید را کپی کنید.

روش دوم) با استفاده از ماکرو نویسی در اکسل (قابل استفاده برای حالت ستونی)

1) کلیدهای Alt + F11 را فشار دهید یا از تب Developer قسمت code گزینه Visual Basic را انتخاب کنید. پنجره Microsoft Visual Basic ظاهر می‌شود. در این پنجره از تب Insert گزینه Module را انتخاب نمائید.

) روی کلید F5 را فشار دهید. با انجام اینکار پنجره زیر ظاهر می‌شود. سپس داد‌ه‌های مدنظرتان را انتخاب کنید.

3) در پایان روی ok کلیک کنید تا مشابه روش قبل بین تمام ردیف‌ها یک ردیف خالی ایجاد شود.

روش سوم)  ابزار Kutools for Excel

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

تابع Countدر اکسل  

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

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

تابع Countifدرexcel

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

تابع Countifs

این تابع مانند تابع Countif است اما چندین شرط بر چندین رنج می توانید وارد کنید، یعنی بگویید خانه هایی که در رنج فلان هستند با شرط فلان و در رنج بهمان هستند با شرط بهمان.

در مثال زیر ما می گوییم سبز و بزرگتر از 9

در واقع موارد یافته شده باید در یک ردیف باشند، مثلا 10 هم شرط دوم را دارد اما هم ردیفش red شرط یک را ندارد.

در واقع فکر کنید، در ستون اول نام فرد و ستون دوم سمت و ستون سوم نمره هر فرد ذخیره شده، برای اینکه بفهمیم مدیر هایی که نمره بالای ده داشتند چند نفر هستند یا آبدارچی هایی که نمره بالای 11 داشتند چند نفر هستند از این تابع باید استفاده کنیم.

تابع Sum

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

تابع Sumif

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

اگر میخواهید شرط را روی یک ستون بگزارید اما جمع ها روی یکستون دیگر انجام شود به این گونه انجام دهید:

جمع روی ستون B زده می شود اما شرط در تابع روی دو ورودی اول مشخص شده است، یعنی ستون A جایی که نوشته شده باشد green.

تابع Sumifs

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

سلول ها در اکسل  به کاربر  این قابلیت را می دهد که با دادن مقادیر مختلف به آن انواع و اقسام نمودار ها را در ظاهر های مختلف برای خود تولید کنید، رسم این نمودار ها نکاتی دارد که در این آموزش اکسل  به آن ها خواهیم پرداخت.

ساخت نمودار ساده

برای تهیه یک نموار خطی مراحل زیر را دنبال کنید.

ابتدا مقادیری را باید به عنوان داده های مورد نظر خود وارد حداول اکسل کنید و سپس آن ها را انتخاب کنید، مثلا در شکل زیر رنج A1:D7 را  انتخاب کنید.

در منوی Insert و در گروه Chart گزینه Line را انتخاب کنید و سپس Line with Markers را انتخاب کنید، ظاهر تمامی موارد از شکل یا آیکن آن ها پیداست.

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

تغییر نوع جدول دراکسل

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

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

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

تغییر مبنای جدول در excel

نمودار خود را پس از ساخته شدن انتخاب کنید.

منو هایی به نوار ریبون اضافه می شوند.

در تب Design روی دکمه Switch Row/Column کلیک کنید تا مقادیر جا به جا شوند.

تیتر جدول در اکسل

نمودار خود را پس از ساخته شدن انتخاب کنید. و سپس وارد تب Layout شوید و روی Chart Title کلیک کنید، و مطابق آیکون های نشان داده شده مکانی برای نمایش تیتر خود انتخاب کنید.

نمایش راهنمای نمودار (رنگ بندی نمودار)

برای اینکه مشخص کنید راهنمای رنگ بندی نمودار در کجا نمایش داده شود یه تب Layout مطابق انتچه بالا تر ذکر شود وارد شوید و سپس روی Legend کلیک کنید و مطابق آیکن هر گزینه یک مورد به دلخواه خود انتخاب کیند، ب

برچسب داده ها (Data Labels)

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

=COUNTA(value1,[value2],...)

=COUNTA(a,100,ش)

=COUNTA(range)

=COUNTA(B2:B10)

=COUNT(value1,[value2],...)

=COUNT(range)

3- توابع COUNTIF و COUNTIFS: شمارش شرطی در اکسل 

=COUNTIF(range, criteria)

=COUNTIF(B2:B7,200)

=COUNTIF(B2:B7,E2)

=COUNTIF(B2:B7,">200")

=COUNTIF(E2:E7,")

=COUNTIF(E2:E7,">"&B7)

=COUNTIF(E2:E7,"&B7)

=COUNTIF(B2:B25,"Excel")

=COUNTIFS(criteria_range1, criteria1,[criteria_range2, criteria2]…)

=COUNTIFS(E2:E7,100,E2:E7,300)

=SUM(COUNTIF(E2:E7,{100,300}))

=COUNTIF(E2:E7,100)+COUNTIF(E2:E7,300)

=COUNTBLANK(range)