تعیین تعداد اعداد دارای رقم یکان مشابه در اکسل

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

فرض کنید در ستون A از سلول A1 تا A10 تعداد 10 عدد وجود داشته باشد، برای تعیین تعداد اعدادی که دارای رقم یکان مشابه مثلا عدد 5 می‌باشند می‌توان از روش‌های زیر استفاده کرد:

روش 1)  استفاده از توابع Right و  Countifدراکسل:

با استفاده از توابع Right و Countif که به ترتیب در معرفی شده‌اند می‌توان اینکار را انجام داد. در سلول B1 فرمول زیر را نوشته و برای سایر سلول‌های ستو اعمال کنید (B2 تا B10).

روش 2) فرمول نویسی آرایه‌ای:

بجای استفاده از فرمول نویسی معمولی می‌توان از فرمول نویسی آرایه‌ای استفاده کرد. نحوه استفاده از فرمول نویسی آرایه‌ای انجام میدهیم  در یک سلول خالی کلیدهای Ctrl + Shift + Enter را همزمان فشار دهید.

روش 3) با استفاده از توابع SUMPRODUCT و ISNUMBER

• تابع Isnumber: این تابع بررسی می‌کند که ورودی آن عدد هست یا خیر. در صورتی که عدد باشد خروجی آن TRUE و در غیر این صورت خروجی آن FALSE می‌باشد.

• تابع SUMPRODUCT: با استفاده از این تابع می‌توان دو یا چند مجموعه از اعداد را در هم ضرب کرده و نتیجه را با هم جمع نمود. تابع Sumproduct در این آموزش اکسل پیشرفته معرفی شده است.
  

در فرمول بالا اعداد با تعداد رقم بیشتر از 6 نیز در نظر گرفته می‌شود. در واقع چنانچه در اعداد ستون A از رقم ششم به بعد (از سمت راست) عدد 5 وجود داشته باشد آن عدد شمارش می‌شود.

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

مثال: فرض کنید جدولی داریم که در آن میزان مبلغ خرید هر شخص از شرکت نوشته شده و می خواهیم خریدهای بالاتر از 100000 تومان را به رنگ آبی در بیاوریم. چون تعداد داده های ما بسیار زیاد است و مشتریان بسیار زیادی داریم نمی توانیم تک تک آنها را چک کنیم و دستی رنگ آنهایی که خرید بالای صد هزار تومان داشته اند را آبی کنیم، بلکه باید سیستم هوشمندی را طراحی کنیم که بصورت خودکار این کار را انجام دهد. بهترین راه استفاده از “فرمت مشروط” در اکسل  می باشد.

حال میخواهیم مبلغ سفارشاتی که بیشتر از 50000 تومان هست را آبی کنیم.

برای اینکار داده های ستون مبلغ کل سفارشات را انتخاب می کنیم.

سپس در تب Home گزینه Conditional Formatting را انتخاب می کنیم و از زیر منوی آن، گزینه New Rule را می زنیم.

حال از پنجره ظاهر شده گزینه دوم را در قسمت  Select a Rule Type انتخای می کنیم.

سپس از لیست کشویی گزینه مورد نظر را براساس معانی زیر انتخاب می کنیم:

between: بین این دو عدد

Not between : بین این دو عدد نباشد

Equal To: دقیقا مساوی با این عدد

Not Equal To: مساوی با این عدد نباشد

Greater than: بزرگتر از این عدد

Less than: کمتر از این عدد

Greater than or equal to: بزرگتر و مساوی این عدد

less than or equal to: کوچکتر و مساوی این عدد

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

با توجه به مثال ما، گزینه greater than را انتخاب نموده و عدد 50000 را در باکس جلوی آن می نویسیم. (همانطور که در مثال گفته شد میخواهیم سفارشات بالای 50000 تومان را به رنگ آبی در بیاوریم.) و همچنین در قسمت Format، فرمت خروجی را تنظیم می کنیم. که در این مثال میخواهیم باکس های بالای 50000 تومان به رنگ آبی در بیایند.

امیدوارم از این آموزش اکسل پیشرفته  لذت برده باشید.

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

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

برای تجزیه محتویات یک سلول دو روش وجود دارد:

• نوشتن فرمول
• ابزار Text To Columns

 قبل از بیان این قسمت، نیاز است تعدادی از توابع متنی توضیح داده شود.

•  تابع Find:

این فرمول درون متن مورد جستجو موقعیت آغاز یک کاراکتر (حرف، عدد و کلمه) را پیدا می کند. لازم به ذکر است که هر فاصله (space) نیز یک کارکتر محسوب می شود.

=FIND(find_text , within_text , start_num)

این فرمول 3 آرگومان (پارامتر یا شیء) دارد:

1-   find_text (آرگومان 1): متنی که بایستی جستجو شود این متن می تواند یک حرف یا یک کلمه یا متن باشد.

2-  within_text (آرگومان 2): متنی که در آن به جستجو خواهیم پرداخت. این متن نیز می‌تواند یک حرف یا یک کلمه یا متن طولانی باشد.

3-   Start_num (آرگومان 3): نقطه آغاز جستجو است و در آرگومان 2 مشخص می کنیم که از چندمین کاراکتر شروع به جستجو کند که دراین صورت کاراکترهای قبلی جستجو نخواهند شد. این آرگومان از یک شروع و تا تعداد کارکتر موجود در آرگومان 2 می‌تواند باشد.

درصورتیکه عدد صفر یا عددی بیش از تعداد کارکتر موجود در آرگومان 2 به آن اختصاص یابد خطای #VALUE! را نشان خواهد داد.

اگر متن مورد جستجو (آرگومان 1) در متن قابل جستجو (آرگومان 2) موجود نباشد خطای #VALUE! را خواهیم دید.

• تابع :Search

=Search(find_text , within_text , start_num)

تمامی آرگومانها و همچنین عملکرد تابع  Searchبا تابع Find یکسان است و تنها تفاوتشان این است که تابع Find به بزرگی یا کوچکی حرف حساس است ولی تابع Search اینگونه نیست. 

• تابع Right:

=RIGHT(text,num_chars)

این تابع تعداد مشخصی از آخرین کاراکترهای یک رشته متنی را استخراج می‌کند (متن انگلیسی از سمت راست و متن فارسی از سمت چپ).

آرگومان text: این آرگومان، متنی است که می خواهید قسمتی از آن را استخراج کنید و می تواند ارجاع به یک سل و یا نوشتن متن باشد.

آرگومان Num_chars: تعداد کاراکترهایی است که می خواهید از انتهای رشته متن استخراج شود.

• تابع Left:

=LEFT(text,num_chars)

این تابع تعداد مشخصی از اولین کاراکترهای یک رشته متنی را استخراج می‌کند (متن انگلیسی از سمت چپ و متن فارسی از سمت راست).

آرگومان text: این آرگومان، متنی است که می خواهید قسمتی از آن را استخراج کنید و می تواند ارجاع به یک سل و یا نوشتن متن باشد.

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

• تابع Mid:

=MID(text,start_num,num_chars)

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

• تابع Len:

=LEN(text)

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

تجزیه متن یک سلول از طریق نوشتن فرمول در excel

با استفاده از فرمولهایی که در بالا توضیح داده شد می توان محتوای یک سلول را تجزیه نمود. ممکن است دو حالت پیش آید:

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

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

در تصویر زیر نیز نحوه جدا کردن نام و نام خانوادگی نشان داده شده است. همانطور که در این تصویر دیده می شود چند تابع درون تابعی دیگر نوشته شده است.
برای جدا کردن نام، از تابع LEFT استفاده می‌شود به طوریکه برای ورودی دوم این تابع (تعداد کاراکترهای مورد نظر که قرار است استخراج شوند یعنی تعداد کاراکترهای نام) تابع FIND نوشته می‌شود. این تابع موقعیت کاراکتر فاصله را درون رشته متن می یابد و سپس یک واحد از آن کم می گردد تا موقعیت آخرین حرف نام مشخص شود.
برای جدا کردن نام خانوادگی، تابع MID بکار گرفته می شود. برای ورودی دوم این تابع (موقعیت اولین کاراکتر نام خانوادگی) از تابع FIND استفاده می‌گردد. این تابع موقعیت کاراکتر فاصله را درون رشته متن می یابد و سپس یک واحد به آن اضافه می‌گردد تا موقعیت اولین حرف نام خانوادگی تعیین شود. برای ورودی سوم تابع MID می‌بایست به طریقی مشخص شود که از اولین حرف نام خانوادگی به بعد چه تعداد کاراکتر باید استخراج شود. این کار به راحتی با استفاده از تابع LEN امکانپذیر است.

تجزیه متن یک سلول از طریق ابزار Text to columns در اکسل

در اینجا نیز ممکن است همان دو حالت پیش آید.

برای حالت اول که تعداد کاراکترهای مورد نظر برای استخراج ثابت و معین هستند به طریق زیر عمل کنید:

1. سلول‌هایی که قرار است تجزیه شوند را انتخاب نمایید.
2. از منوی Data، بر روی گزینه Text to columns کلیک کنید.
3. در مرحله 1 از 3، گزینه Fixed Width را انتخاب نمایید.
4. در مرحله 2 از 3، بوسیله کلیک کردن در نقاطی که می خواهید کاراکترها از یکدیگر جدا شوند، ستونها را تجزیه کنید.
5. در مرحله 3 از 3 در فیلد Destination آدرس سلولی که می خواهید داده ها درج شوند را وارد کنید و بر روی Finish کلیک نمایید

 برای حالت دوم، به منظور جدا کردن نام و نام خانوداگی به طریق زیر عمل کنید:

1. سلولهایی که قرار است تجزیه شوند را انتخاب نمایید.
2. از منوی Data، بر روی گزینه Text to columns کلیک کنید.
3. در مرحله 1 از 3، گزینه Delimited را انتخاب نمایید.
4. در مرحله 2 از 3، گزینه Space را انتخاب کنید.
5. در مرحله 3 از 3 در فیلد Destination آدرس سلولی که می خواهید داده ها درج شوند را وارد کنید و بر روی Finish کلیک نمایید

ترکیب چند سلول در یک سلول در اکسل

برای این کار می توان به دو روش عمل نمود:

روش اول) در روش اول از کاراکتر & بین محتوای سلول‌ها استفاده می شود. فرض کنید می خواهیم نام و نام خانوادگی که در سلولهای جداگانه درج شده اند را در یک سلول ترکیب کنیم. برای اینکه فاصله ای نیز بین نام و نام خانوادگی درج شود، فاصله را داخل گیومه قرار دهید و به فرمول بیفزایید. در تصویر زیر چگونگی انجام این کار نشان داده شده است.

روش دوم) از تابع CONCATENATE استفاده می‌شود. ورودی های این تابع داده‌هایی است که باید با یکدیگر ترکیب شوند. برای مثال زیر این تابع سه ورودی دارد؛ ورودی اول نام، ورودی دوم یک فاصله و ورودی سوم نام خانوادگی می باشد.

 قفل کردن سلول های در اکسل

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

برای انجام این کار به ترتیب زیر عمل کنید:

 1- ابتدا با استفاده از Ctrl+A تمام سلول های کاربرگ را انتخاب کنید.

2- سپس بر روی کاربرگ کلیک راست کنید. و گزینه Format Cells  را انتخاب نمایید.

3- در پنجره Format Cells  بر روی سربرگ Protection کلیک کنید و تیک گزینه Locked را غیر فعال کنید و بر روی OK کلیک نمایید.

4- سلولهایی که حاوی فرمول هستند را انتخاب کنید و دوباره مرحله 3 را انجام دهید. ولی در این مرحله تیک گزینه Locked  را فعال کنید.

5- بر روی تب Review کلیک کنید. و بر روی گزینه Protect Sheet کلیک کنید.

6- در پنجره Protect sheet به جز گزینه ُSelect Lock Cells تیک تمام گزینه ها را فعال کنید و رمز عبور خود را وارد نمایید.

7- در پنجره بعدی هم رمز عبور خود را دوباره وارد کنید و بر روی Ok کلیک کنید.


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

عبارت AVERAGE به معنای میانگین و معدل گرفتن می باشد و از تابع AVERAGE اکسل برای گرفتن میانگین و معدل از بین تعدادی عدد استفاده می شود.

قاعده کلی تابع AVERAGE اکسل

AVERAGE (مقدار 3[اختیاری] , مقدار 2[اختیاری] , مقدار1 , …)

 تابع AVERAGE اکسل میانگین پارامترهایی که به آن داده شده را  محاسبه می کند.

تابع AVERAGE اکسل در واقع مترادف فرمول (SUM(X) / COUNT(X هست.

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

اگر بخوایم در سلول B10 معدل دانش آموز رو نمایش بدیم می تونیم از فرمول (AVERAGE(B3:B9 استفاده کنیم. که با قرار دادن این فرمول در سلول B10 مقدار این سلول برابر 16٫43 خواهد بود.

چند نکته در خصوص تایع AVERAGE:

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

نکته: در صورتی که بخوایم سلول های متنی که متن اونها قابل تبدیل به عدد هست یا سلول هایی که دارای مقادیر منطقی مثل TRUE هستند هم در میانگین  می توانیم از تابع AVERAGEA استفاده کنیم.

نکته: اگر بخواهیم برای گرفتن میانگین اعداد شرط خاصی تعیین کنیم می توانیم از تابع AVERAGEIF استفاده کنیم.

نکته: در صورتی که بخواهیم بیش از یک شرط برای میانگین گرفتن تعیین کنیم هم می توانیم از تابع AVERAGEIFS استفاده کنیم.                                                                                                   

تبدیل جدول اکسل به بانک اطلاعاتی اکسس

فرض کنید کاربرگی از اکسل دارید که حاوی اطلاعات مشتری می‌باشد و تصمیم گرفته‌اید سایتی بر اساس همان اطلاعات داشته باشید درنتیجه باید به سراغ برنامه Access که برنامه‌ای نسبتاً ساده برای ایجاد و مدیریت پایگاه داده هست، بروید. اما اینکه بخواهید این داده‌ها را دوباره در اکسس وارد کنید کاری بسیار سخت و زمان‌گیر محسوب می‌شود. اگر هم‌حجم داده‌ها بسیار بالا باشد این کار تقریباً غیرممکن می‌‌شود. اینجاست که خود مجموعه آفیس به کمک شما می‌آید و امکان این را فراهم می‌کند که داده‌ها را از یک نرم‌افزار وارد نرم‌افزار دیگر کنید؛ برای نمونه از Excel به Access یا برعکس. همچنین برای این ورود داده‌ها چندین روش درنظرگرفته شده است و می‌توان گفت دست شمارا باز گذاشته است.

برای این کار ابتدا یک بانک اطلاعاتی از نوع اکسس ایجاد نمایید.

سپس از تب External Data، گزینه‌ی Excel را انتخاب نمایید.

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

در این پنجره دو گزینه جهت انتخاب وجود دارد؛ گزینه اول اطلاعات مربوطه را اکسترکت می‌نماید، اما گزینه‌ی دوم لینکی به جدول اکسل ایجاد می‌نماید تا در صورت بروز هرگونه تغییری در اطلاعات جدول بانک ساخته‌شده نیز بروز گردد.

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

در مرحله‌ی بعد، اگر بخواهید عنوان سلول‌های جدول در بانک موجود باشد تیک گزینه First Row Contains Column Headings را بزنید و گزینه‌ی Next را انتخاب نمایید.

در این مرحله دقت داشته باشید که Sheet موردنظر انتخاب‌شده باشد و گزینه Finish را انتخاب نمایید.

در آخر گزینه‌ی OK را انتخاب کنید و کار به پایان می‌رسد.

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

تبدیل جدول اکسل به بانک اطلاعاتی SQL

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

سپس روی بانک موردنظر کلیک راست نموده و گزینه‌ی Task و سپس گزینه‌ی Export Data را انتخاب نمایید.

در پنجره‌ی بازشده گزینه Next را انتخاب نمایید.

در صفحه‌ی بعدی باید نوع دیتابیس را در قسمت Data Source و مسیر را در قسمت File Path انتخاب نمایید. سپس گزینه‌ی Next را انتخاب نمایید.

در صفحه‌ی بعدی گزینه Next را انتخاب نمایید.

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

در پنجره نمایش داده‌شده، کاربرگ موردنظر را که حاوی اطلاعات جدول می‌باشد انتخاب نمایید.

صفحات بعدی را Next و در نهایت گزینه‌ی Finish را انتخاب نمایید.                                                        همراهان گرامی امیدواریم از آموزش اکسل امروز نیز لذت برده باشید                                                    شادی هایتان مستدام

 PivotTables می توانند جهت خلاصه سازی و تجزیه و تحلیل انواع داده ها مورد استفاده قرار بگیرند. برای کمک به شما در زمینه مدیریت بهتر PivotTable و برای اینکه بینش و درک بهتری از داده هایتان پیدا کنید، اکسل سه ابزار دیگر نیز به شما ارائه می دهد. اسامی این ابزارها filters، slicers و PivotCharts می باشد. 

فیلترها (Filters) در PivotTable اکسل

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

روش افزودن فیلتر به یک PivotTable در اکسل

در مثال زیر، ما برخی از فروشندگان را فیلتر می کنیم، تا بدانیم فروش فردی آنها چه تاثیری در فروش ناحیه شان گذاشته است.

➊ یک فیلد را از قسمت فیلدها بکشید و در ناحیه فیلترها رها کنید. در این مثال ما از فیلد Salesperson استفاده می کنیم.

➋ فیلتر در بالای قسمت PivotTable ها نمایان می شود. ابتدا کادر مربوط به فیلتر را باز کنید و سپس گزینه Select Multiple Items را تیک بزنید.

➌ حالا تیک مواردی را که نمی خواهید در PivotTable مشاهده شوند را بردارید. در این مثال ما چند تا از فروشندگان را از حالت انتخاب خارج کرده ایم، در پایان OK کنید.

➍ جدول PivotTable خودش را تنظیم می کند و تغییرات ایجاد شده را منعکس می کند.

برش دهنده ها (Slicers) در اکسل

برش دهنده ها (Slicers) فیلتر کردن داده ها در PivotTables را از این هم ساده تر می کنند. برش دهنده ها در واقع همان فیلترها هستند، اما ساده تر هستند و استفاده از آنها سریعتر است، و به شما امکان می دهند تا فورا داده های خود را بچرخانید. اگر شما مکررا داده های PivotTables را فیلتر می کنید، ممکن است بخواهید که از برش دهنده ها استفاده کنید.

روش افزودن یک slicer (برش دهنده) در اکسل

➊ ابتدا یک سلول را به دلخواه در PivotTable انتخاب کنید.

➋ در تب Analyze بر روی دستور Insert Slicer کلیک کنید.

➌ یک کادر باز می شود. فیلدهای مورد نظرتان را تیک بزنید و سپس ok کنید. در این مثال ما فیلد Salesperson را تیک می زنیم.

➍ برش دهنده (slicer) در کنار PivotTable نمایان می شود. مواردی را که انتخاب کرده اید به رنگ آبی نمایش داده می شوند. در این مثال، برش دهنده شامل تمامی 8 فروشنده ما می باشد، اما 5 تایی که در حالت انتخاب هستند با رنگ آبی متمایز شده اند.

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

نمودارهای چرخشی (PivotCharts) در اکسل

نموارهای چرخشی (PivotCharts) دقیقا مانند نمودارهای معمولی هستند، با این استثنا که آنها داده های یک PivotTable را نمایش می دهند. درست مانند نمودارهای معمولی، شما می توانید از ویژگیهای نوع نمودار، سبک نمایش نمودار و... بهره مند گردید.

 PivotChart در اکسل

➊ ابتدا سلولی را به دلخواه در PivotTable انتخاب کنید.

➋ در تب Insert بر روی دستور PivotChart کلیک کنید.

➌ صفحه Insert Chart نمایان می شود. ابتدا نوع نموارد (chart type) و همینطور سبک نمایش (layout) مورد نظرتان را انتخاب کنید.

➍ نمودار PivotChart نمایان می شود.

ابزار Goal Seek در اکسل

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

روش استفاده از ابزار Goal Seek در اکسل:

فرض کنیم شما در یک کلاس ثبت نام کرده اید. نمره شما در حال حاضر 65 می باشد، و شما حداقل به نمره 70 نیاز دارید تا بتوانید آن کلاس را بگذرانید. خوشبختانه، شما یک امتحان نهایی دارید که می توانید به واسطه آن میانگین نمراتتان را بالاتر ببرید. شما می توانید از ابزار Goal Seek اکسل استفاده کنید تا متوجه شوید، در امتحان نهایی شما به چه نمره ای نیاز دارید تا بتوانید آن کلاس را با موفقیت بگذرانید (یا اصطلاحا پاس کنید).

نمرات 4 آزمون اول را می بینید. این نمرات 58، 70، 72 و 60 می باشند. اگر چه ما هنوز نمی دانیم نمره آزمون پنجم شما چه شده است، با این وجود می توانیم فرمولی (یا تابعی) بنویسیم که نمره آزمون نهایی شما را محاسبه کند. در این وضعیت، هز آزمون به یک اندازه در فرمول ما وزن خواهد داشت، بنابراین آنچه ما نیاز داریم اینست که میانگین 5 آزمون اول را با نوشتن دستور زیر محاسبه کنیم.

=AVERAGE(B2:B6)

وقتی ما از ابزار Goal Seek استفاده کنیم، سلول B6 حداقل نمره لازم را که ما باید در آن آزمون کسب کنیم را به ما نمایش می دهد.



➊ سلولی را که قصد ویرایش مقدارش را دارید انتخاب کنید. هر وقت که شما از ابزار Goal Seek اکسل استفاده می کنید، باید سلولی را که دارای یک فرمول یا یک تابع می باشد را انتخاب کنید. در این مثال ما سلول B7 را انتخاب می کنیم چون فرمول زیر داخل آن نوشته شده است:

=AVERAGE(B2:B6)

➋ در تب Data بر روی دستور What-If Analysis کلیک کنید و سپس از کادر باز شده گزینه Goal Seek را انتخاب نمایید.



➌ یک پنجره یا سه فیلد ظاهر می شود. اولین فیلد که Set cell می باشد، شامل نتایج مطلوب (ایده آل) می باشد. در مثال ما سلول B7 برای این مورد انتخاب شده است.

فیلد دوم To value نتیجه مورد نظر می باشد. در مثال ما مقدار 70 را در آن وارد می کنیم، زیرا ما باید در نهایت 70 نمره کسب کنیم تا بتوانیم این کلاس را پاس کنیم.

فیلد سوم By changing cell، سلولی است که ابزار Goal Seek پاسخ را در آن نمایش خواهد داد. در این مثال ما سلول B6 را انتخاب می کنیم، زیرا می خواهیم نمره مورد احتیاج ما در آزمون نهایی در آنجا نمایش داده شود.

➍ وقتی این کارها را انجام دادید ok کنید.



➎ اگر ابزار Goal Seek قادر باشد نتیجه را برای شما محاسبه کند، این موضوع را به شما اطلاع می دهد. ok را کلیک کنید.



➏ نتیجه در سلولی که برای نمایش نتیجه مشخص کرده اید، ظاهر می شود. در مثال ما، ابزار Goal Seek به ما نشان می دهد که برای پاس کردن کلاس باید در آزمون نهایی نمره 90 بگیریم.

روش استفاده از ابزار Goal Seek در اکسل :

فرض کنیم  مشغول برنامه ریزی برای یک مراسم هستید، و می خواهید تا جای ممکن افراد بیشتری را دعوت کنید، البته بودجه شما برای این مراسم 500 دلار می باشد. ما می توانیم از ابزار Goal Seek اکسل استفاده کنیم تا ببینیم، چند نفر را می توانیم دعوت کنیم. در مثال زیر، سلول B5 شامل فرمول زیر می باشد که مجموع هزینه رزرو اتاق به اضافه هزینه هر شخص می باشد.

=B2+B3*B4

➊ ابتدا سلولی را که می خواهید مقدارش را تغییر بدهید، انتخاب نمایید. در این مثال ما سلول B5 را انتخاب می کنیم.



➋ در تب Data، بر روی دستور What-If Analysis کلیک کنید، سپس از کادر باز شده گزینه Goal Seek را انتخاب نمایید.



➌ یک پنجره با سه فیلد نمایان می شود. فیلد اول Set cell حاوی نتایج مطلوب است. در مثال ما سلول B5 انتخاب شده است.

فیلد دوم To value شامل نتیجه لازم است، ما مقدار 500 را در آن وارد می کنیم زیرا فقط می خواهیم 500 دلار خرج کنیم.

فیلد سوم By changing cell محلی است که ابزار Goal Seek نتایج محاسبه شده را در آن نمایش خواهد داد. در این مثال، ما سلول B4 را انتخاب می کنیم، زیرا می خواهیم نتیجه اینکه ما چند مهمان را می توانیم دعوت کنیم، بدون اینکه بیش از 500 دلار خرج کنیم، در آنجا نمایش داده شود.

➍ وقتی کارتان تمام شد ok کنید.



➎ اگر ابزار Goal Seek قادر به محاسبه نتایج باشد، در این پنجره به شما خبر می دهد. ok کنید.

➏ نتایج محاسبه شده در سلول مربوطه نمایان می شود. در مثال ما، ابزار Goal Seek محاسبه کرده است که پاسخ تقریبا 18.62 می باشد. از آنجا که در این مورد خاص پاسخ ما باید یک عدد صحیح باشد، پس ما نیاز داریم تا این عدد را به سمت بالا و یا به سمت پایین گرد (رند) کنیم. و طبیعتا چون بودجه ما 500 دلار است و نمی خواهیم بیش از آن هزینه کنیم این عدد را به سمت پایین گرد می کنیم تا نتیجه تبدیل به 18 گردد.

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

سایر انواع what-if analysis در اکسل

در پروژه های خیلی پیشرفته تر، شما ممکن است از انواع دیگر دستور what-if analysis استفاده نمایید. این انواع شامل گزینه های scenarios و data tables می باشند. جای اینکه مشابه ابزار Goal Seek از نتایج مطلوب شروع کنیم و رو به عقب برگردیم، این موارد گزینه هایی به شما می دهند که مقادیر مختلف را آزمایش کنید و تاثیرش را در نتیجه نهایی ملاحظه کنید.

• Scenarios: این گزینه به شما امکان می دهد تا مقادیر سلولهای متعددی را (تا سقف 32 سلول)، هم زمان جایگزین کنید. شما می توانید سناریوهای مختلفی را با این ابزار بسازید و آنها را با هم مقایسه کنید و در عین حال از تغییر دادن دستی مقادیر پرهیز کنید. در تصویر زیر، ما از سناریوهایی استفاده می کنیم تا سالن های مختلف را برای یک مراسمی که در پیش رو داریم، با هم مقایسه کنیم.
  
  
• Data tables: این گزینه به شما این امکان را می دهد که یک یا دو متغیر در یک فرمول را در نظر بگیرید و آنها را با مقادیر متفاوت دیگری که می خواهید جایگزین کنید، و سپس نتایج را به شک یک جدول مشاهده کنید. این گزینه بسیار قدرتمند می باشد زیرا برخلاف دو ابزار قبلی، می تواند نتایج چندگانه ای را همزمان به شما نشان می دهد. در تصویر زیر ما می توانیم 24 نتیجه ممکن را برای یک وام خودرو مشاهده نماییم.
  
  

باما همراه باشید تا در ادامه با روش غیر فعال کردن آن آشنا شوید
Auto Fill بطور خودکار فعال است. جهت غیر فعال نمودن آن مراحل زیر را طی می کنیم:
1-    انتخاب منوی Tools
2-    انتخاب منوی Options
3-    انتخاب: Edit Tab
4-    کادر انتخاب Allow Cell Drag and Drop را غیر فعال می کنیم
 
آدرس دهی:
آدرس دهی نسبی:
فرض کنید سلولهای Excel را به صورت زیر پر کرده ایم:
در سلول B1 فرمول A1 A2 را می نویسیم. اگر این فرمول را copy کرده و در سلول B2. Paste کنیم و یا با استفاده از Auto fill محتوای سلولهای B2 تا B4 را پر کنیم.اعداد 5و7و4 به ترتیب برای سلولهای B2 تا B4 ظاهر می شوند. حال میخواهیم بدانیم این اعداد از کجا بدست آمده اند. وقتی ما در سلول B1 فرمول A1 A2 را تایپ می کنیم، در حقیقت سلول سمت چپی و یک سلول پایین آن با هم جمع می شوند. پس برای سلول B2، سلول سمت چپی (A2) و سلول پایینی آن (A3) با هم جمع می شوند که جواب 5 می شود. برای سلولهای بعدی هم به همین ترتیب محاسبات انجام می شود. به این نوع آدرس دهی، آدرس دهی نسبی گفته می شود چون نسبت به مکان هر سلول، فرمول سلول عوض می شود و در حقیقت فرمول درون سلول B2، A2 A3 می شود.
 
 
آدرس دهی مطلق در اکسل:
فرض کنید میخواهیم حقوق افراد یک اداره را حساب کنیم. حقوق با استفاده از فرمول زیر حساب می شود: حقوق پایه * 7% - حقوق پایه = حقوق حقوق پایه را در ستون B مینویسیم. برای محاسبه حقوق می توانیم در سلول C1 فرمول B1-B1*7% را بنویسیم و برای تمام افراد Auto fill کنیم. در این حالت در حقیقت از آدرس دهی نسبی استفاده کرده ایم.
ولی فرض کنید که درصد مالیات عوض شده و 10% شود. حالا باید دوباره فرمول جدیدی در سلول C1 نوشته و مجدد Auto fill کنیم. برای اینکه نخواهیم در هر بار عوض شدن درصد مالیات فرمول را عوض کنیم می توانیم درصد مالیات را در سلول جدا نوشته و از آدرس آن در فرمول استفاده کنیم. پس مثلاً در سلول A1 عدد 7% را مینویسیم و در سلول C1 فرمول B1-B1*A1 را مینویسیم حال Auto fill می کنیم. ولی می بینیم که برای بقیه سلولها جواب همان حقوق پایه می شود. چرا؟ چون با آدرس دهی نسبی میخواهد عمل کند و چون در سلول C1 فرمول B1-B1*A1 بوده، برای C2 فرمول B2-B2*A2 می شود که مقدار A2 صفر است. پس جواب همان مقدار حقوق پایه می شود. برای رفع این مشکل باید سلول A1 ثابت شود. یعنی در تمام فرمولها A1 در ستون حقوق پایه ضرب شود. در این حالت از آدرس دهی مطلق استفاده می کنیم. برای ثابت کردن سطر یا ستون در کنار حرف ستون یا عدد سطر علامت $ می گذاریم. اگر بخواهیم سلول را ثابت کنیم در کنار حرف ستون و عدد سطر هر دو علامت $ می گذاریم. یعنی در سلول C1 مینویسیم: B1-B1*$A$1
 
قالب بندی خانه‌ها در Excel
در Excel این توانایی را داریم که قالب بندی یا فرمت یک سلول را تغییر داده و فرمت آن سلول را مثلاً تاریخ یا درصد یا زمان یا متن یا.... کنیم. همچنین می توانیم دور سلول کادر بیاندازیم یا رنگ زمینه آنرا عوض کنیم.
برای قالب بندی خانه‌ها در Excel مراحل زیر را طی می کنیم:
روش اول:
1-    انتخاب منوی Format
2-    انتخاب گزینه Cells
روش دوم:
بر روی خانه‌های مورد نظرکلیک راست کرده و گزینه Format Cells را انتخاب می کنیم.
روش سوم:
فشردن کلیدهای ctrl 1
با اجرای یکی از سه روش بالا پنجره ای باز می شود که دارای Tab‌های زیر است:
 
الف - Number:
توسط این Tab می توانیم نوع اطلاعات ورودی را تعیین کنیم. این Tab شامل گزینه‌های زیر می باشد:
Sample: هر فرمتی را که انتخاب کنیم، بر روی محتوای سلول انتخابی نمایش می دهد.
 
الف) Category: در این قسمت نوع داده را مشخص می کنیم که شامل انواع زیر می باشد:
1- General:       این گزینه عددها را به صورت رشته ای از رقمهای متوالی و بدون هر گونه قالب بندی نشان می دهد. و اگر عددی در سلول جا نشودآنرا به صورت نمایی نشان می دهد.
2- Number:       مقادیر را بصورت رشته ای از رقمهای متوالی نشان می دهد. در این حالت اگر عدد در سلول نگنجد سلول بزرگتر می شود. همچنین در این قسمت می توانیم تنظیمات زیر را انجام دهیم:
�      Decimal Places: در این قسمت می توانیم تعداد ارقام بعد از اعشار را تعیین کنیم.
�      Negative Number: در این حالت می توانیم مشخص کنیم که عدد منفی به همان صورت نشان داده شود یا به رنگ قرمز یا سیاه با علامت منفی نشان داده شود. یا به رنگ قرمز بدون علامت منفی نشان داده شود. (توجه داشته باشید که این گزینه فقط روی اعداد منفی عمل می کند.)
�      Use 1000 Separator: اگر در کنار بگذاریم اعداد را سه رقم، سه رقم از سمت راست این گزینه می گذارد.
3- Currency:     مقادیر را همراه با سمبل جدا کرده و علامت (،) پول رایج نشان می دهد. در این حالت می توان تنظیمات زیر را انجام داد:
�      Symbol: نوع واحد پول را مشخص می کنید. در این قسمت واحد پول کشورهای مختلف نمایش داده شده و می توانیم واحد دلخواه خود را انتخاب کنیم.
�      Negative Number: اعداد منفی چطور نمایش داده شوند.
4- Accounting:    مانند حالت Currency است. این قالب بندی، قالب بندی حسابداری می باشد و علامت پولی در انتهای سمت چپ آن نوشته می شود.
5- Date:           تاریخ را با قالب بندی خاص تاریخ نشان می دهد. و ما می توانیم انواع قالب بندی‌های تاریخ را دیده و انتخاب کنیم. مثلاً مدلی را انتخاب کنیم که فقط روز و ماه را نشان دهد. و یا مدلی را انتخاب کنیم که روز را به عدد و ماه را به حروف نشان دهد.
6- Time:           زمان را با قالب بندی‌های خاص زمان نشان می دهد. انواع قالب زمانی در این قسمت وجود دارند. ما می توانیم مدلی را انتخاب کنیم که ساعت را از 1 تا 12 با برچسب صبح و بعد از ظهر نمایش دهد و یا مدلی را انتخاب کنیم که ساعت را از 1 تا 24 نمایش دهد. و یا....
7- Percentage:   عددها را همراه با علامت درصد نشان می دهد.
 
نکته: در تمام قالب بندی‌ها بجز Percentage فرقی نمی کند که ابتدا اطلاعات را در سلول وارد کنیم یا اول قالب بندی را تنظیم کنیم. ولی در حالت Percentage اگر ابتدا عدد را نوشته و سپس فرمت را Percentage کنیم، عدد را در 100 ضرب می کند. ولی اگر ابتدا فرمت را Percentage کنیم، و بعد عدد را بنویسیم، تغییری در عدد نمی دهد.
8- Fraction:       مقادیر را بصورت عدد صحیح نشان می دهد که بدنبال آن نزدیک‌ترین کسر به مقدار واقعی ظاهر می شود. برای نوشتن یک عدد مخلوط کافی است ابتدا قسمت صحیح را نوشته سپس یک فاصله بدهیم و بعد صورت کسر را نوشته و بعد علامت (/) را بگذاریم و بعد مخرج کسر را بنویسیم. اگر عدد 6 5/10 را بنویسیم، پس از Enter کردن مقدار سلول 6 1/2 می شود. اگر بخواهیم همان مقدار اولیه باقی بماند، کافی است، در قسمت Fraction مقدار 3/10 را انتخاب کنیم. در این حالت نگاه می کند ببیند مخرج اصلی باید درچند ضرب شود تا مخرج انتخابی شود سپس صورت را هم در همان عدد ضرب می کند.
9- Scientific:      مقادیر را با قالب بندی علمی نشان می دهد.
10- Text:         مقادیر را به همان صورتی که وارد شده اند نشان می دهد. اگر فرمولی را به صورت متن قالب بندی کرده باشیم، Excel آن را به صورت متن نشان می دهد و مقادیر آنرا محاسبه نمی کند.
11- Special:      مقادیر را با استفاده از قواعد قالب بندی خاص (مانند کد پستی، کد پستی به اضافه چهار رقم، یا شماره تلفن، شماره تامین اجتماعی) نشان می دهد. مثلاً اگر در یک سلول یک شماره تلفن (در حالت 10 رقمی) تایپ کنیم، با انتخاب این فرمت به صورت قالب بندی تلفن در می آید. مثلاً اگر شماره IT (3116681184) را وارد می کنیم، به فرمت تلفن در آمده (1184-668 (311)) و می فهمیم که 311 کد شهرستان، 668 کد محله و 1184 شماره تلفن IT می باشد.
12- Custom:     برای ایجاد یک قالب بندی جدید از این گزینه استفاده می کنیم که خارج از بحث ما می باشد.
 
ب) Alignment: توسط این Tab می توانیم جهت قرار گرفتن اطلاعات در سلول را تعیین کنیم. این Tab شامل قسمتهای زیر است:
1-     Horizontal: محل قرار گرفتن افقی متن را تعیین می کند. این قسمت شامل گزینه‌های زیر است:
�      General: هم ترازی پیش فرض
�      Left: هم ترازی داده‌ها را به سمت چپ (استفاده برای ارقام)
�      Center: هم ترازی داده‌ها در مرکز
�      Right: هم ترازی داده‌ها به سمت راست (برای متون)
�      Fill: تمام سلول را با متنی که در آن نوشته شده است، پر می کند.
�      Justify: یک تراز مناسب برای سلول در نظر می گیرد. معمولاً برای زمانی است که اطلاعات در سلول نگنجد.
�      Center Across Selection: وسط متن را در وسط سلولهای انتخابی می گذارد.
2-     Vertical: محل قرار گرفتن اطلاعات را بطور عمودی تنظیم می کند. این گزینه شامل قسمتهای زیر است:
�      Bottom: هم ترازی داده‌ها در پایین سلول
�      Top: هم ترازی داده‌ها در بالای سلول
�      Center: هم ترازی داده‌ها در وسط یا مرکز سلول
�      Justify: داده‌ها در داخل سلول هم تراز می شوند. (بدین معنی که داده‌ها در داخل سلول بصورت مساوی جاسازی می شوند. مانند متون موجود در روزنامه ها)
 
3-     Orientation: شامل قسمتهای زیر است:
�      زاویه متن را نسبت به افق بوسیله ماوس می توان تعیین کرد.
�      Degrees: زاویه متن را نسبت به افق بوسیله تایپ زاویه یا با کلید‌های Increase و Decrease تعیین کرد.
 
4-     Text Control: شامل قسمتهای زیر است:
�      Wrap Text: اگر اندازه متن بیشتر از سلول باشد توسط این گزینه می توان آنرا شکست. در نتیجه ارتفاع سطر افزایش می یابد.
�      Shrink to Fit: اگر اندازه متن از سلول بیشتر باشد با انتخاب این گزینه متن به اندازه ای کوچک می شود که در داخل سلول بگنجد.
�      Merge Cells: اگر اندازه متن از سلول بیشتر باشد می توان سلولهایی را که متن اشغال کرده را انتخاب نمود و سپس در کنار این گزینه تیک زد. این عمل باعث می شود که این سلول‌ها بهم پیوسته و یک سلول شون