مطالبی در مورد شمارش سلول‌های خاص در اکسل

تابع Count تحت عنوان‌های: COUNTA-COUNT-COUNTIFS -COUNTIF – COUNTBLANK. به‌صورت کلی تابع Count به‌منظور شمارش تعداد سلول‌ها در ناحیه‌ای مشخص، استفاده می‌شود.

کاربرد تابع Count

با استفاده از این تابع، قادر به شمارش تعداد سلول‌ها در ناحیه‌ای مشخص‌شده می‌باشید؛ اما نکته‌ای که وجود دارد این است که این تابع صرفاً سلول‌های حاوی اعداد را می‌شمارد. مثلا با درج تابع:

=COUNT(A1:A7)

از بین سلول A1 تا A7، تعداد سلول‌هایی که حاوی مقادیر عددی باشند را می‌شمارد.

کاربرد تابع COUNTA

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

=COUNTA(A1:A7)

که تمام سلول‌های حاوی کارکتر را می‌شمارد.

کاربرد تابع  در excel COUNTBLANK

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

=COUNTBLANK(A1:A7)

کاربرد تابع  COUNTIF در  اکسل

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

COUNTIF(range;"criteria")

در قسمت Range، همانند توابع بالا، محدوده سلول‌ها جهت شمارش مشخص می‌شود و در قسمت criteria شرط خود را برای شمارش سلول‌ها درج می‌کنید. مثلاً می‌توانید مشخص کنید که صرفاً سلول‌های حاوی یک مقدار خاص (یک عدد، کلمه و…) شمارش شوند، یا صرفاً سلول‌هایی که ابتدا یا انتهای آنها یک مقدار خاص باشد، شمارش شود. مثلا:

جهت یافتن سلول‌هایی که مقداری برابر با ۲۰ دارند، از فرمول زیر استفاده می‌کنیم:

=COUNTIF(A1:A7;"20")

جهت یافتن سلول‌های حاوی مقداری بزرگ‌تر از ۱۸، از فرمول زیر را استفاده می‌کنیم:

=COUNTIF(A1:A7;">18"

نکاتی پیرامون توابع (Functions) اکسل

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

قسمتهای مختلف توابع اکسل

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

کار با پارامترهادر EXCEL

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

برای مثال، تابع زیر میانگین دامنه سلولی B1:B9 را محاسبه می کند. این تابع تنها یک پارامتر دارد.

=AVERAGE(B1:B9)

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

=SUM(A1:A3, C1:C2, E1)

ایجاد توابع

توابع خیلی متنوعی در اکسل وجود دارند. در ادامه برخی از پرکاربردترین های آنها را ذکر می کنیم:

• SUM : این تابع مقادیر سلولها را با هم جمع می زند و حاصلجمع را نمایش میدهد.
• AVERAGE : این تابع میانگین سلولها را محاسبه میکند. در واقع ابتدا مقادیر کلیه سلولها را با هم جمع می کند و حاصل جمع بدست آمده را بر تعداد سلولها تقسیم می کند.
• COUNT : این تابع تعداد سلولها را محاسبه می کند.
• MAX : این تابع سلولی را که بیشترین (بزرگترین) مقدار را دارد به عنوان خروجی نمایش می دهد.
• MIN : این تابع سلولی را که کمترین (کوچکترین) مقدار را دارد به عنوان خروجی نمایش می دهد.

 

ایجاد یک تابع با استفاده از دستورAutoSum در اکسل

دستور AutoSum به شما این امکان را می دهد تا خیلی سریع پر کاربردترین توابع اکسل شامل SUM،AVERAGE،COUNT،MIN و MAX را بتوانید استفاده نمایید، در مثال زیر ما با استفاده از ویژگی AutoSum تابع SUM را ایجاد می کنیم.

➊ ابتدا سلولی را که می خواهید تابع را در آن ایجاد کنید را انتخاب کنید. در این مثال ما سلول D13 را انتخاب می کنیم.



➋ در تب Home و در قسمت Editing ، بر روی فلش کوچکی که کنار دستور AutoSum قرار دارد کلیک کنید. سپس از گزینه های باز شده تابع مورد نظرتان را انتخاب کنید. در این مثال ما تابع Sum را انتخاب می کنیم.



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



➍ دکمه اینتر را بفشارید. تابع محاسبه شده و نتایجش نمایش داده می شود. در مثال ما مجموع دامنه سلولی D3:D12 مقدار $765.29 می باشد.

دستور AutoSum در تب Formulas نیز موجود است و از آن طریق نیز می توانید به آن دسترسی داشته باشید.

 

وارد کردن توابع بصورت دستی در اکسل

اگر نام تابع را بدانید خودتان می توانید مستقیما آن را تایپ کنید. در مثال زیر ما از تابع AVERAGE بصورت دستی استفاده خواهیم کرد.

➊ سلولی را که می خواهید تابع را در آن ایجاد کنید، انتخاب نمایید، در این مثال ما سلول C10 را انتخاب می کنیم.



➋ ابتدا علامت مساوی (=) و سپس نام تابع مورد نظرتان را تایپ کنید. همچنین درهنگام تایپ یکسری نام بصورت اتوماتیک ظاهر می شود که به جای تایپ می توانید از بین آن گزینه ها نیز تابع مورد نظرتان را انتخاب کنید. در این مثال ما عبارت زیر را تایپ می کنیم :

=AVERAGE

➌ دامنه سلولی مورد نظر را در بین دو پرانتر وارد کنید. در این مثال ما دامنه سلولی (C3:C9) را وارد می کنیم.



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

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

 

کتابخانه توابع اکسل

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

افزودن یک تابع با استفاده از کتابخانه توابع اکسل

در مثال زیر ما از تابع COUNTA استفاده می کنیم تا تعداد سلولها را بدست بیاوریم. برخلاف تابع COUNT که برای محاسبه تعداد سلولهای با نوع داده عددی کاربرد داشت، تابع COUNTA تعداد سلولهای هر نوع داده ای را محاسبه می کند.

➊ سلولی را که قصد ایجاد تابع در آن را دارید انتخاب کنید، در این مثال ما سلول B17 را انتخاب می کنیم.



➋ تب Formulas را انتخاب کنید تا به کتابخانه توابع دسترسی داشته باشید.

➌ در کتابخانه توابع دسته بندی مورد نظر و سپس تابع مورد نظر را انتخاب نمایید. در این مثال ما دسته بندی More Functions را انتخاب می کنیم و سپس نشانگر ماوس را بر روی گزینه Statistical می بریم.



➍ تابع مورد نظرتان را از لیست انتخاب کنید. در این مثال ما تابع COUNTA را انتخاب می کنیم، این تابع تعداد سلولهایی که مقدار داشته باشند را محاسبه خواهد کرد، صرفنظر از اینکه نوع داده آن سلول چه نوعی باشد.



➎ پنجره پارامترهای تابع نمایش داده می شود. ابتدا فیلد Value1 را انتخاب کنید و سپس سلولهای مورد نظرتان را تایپ و یا انتخاب کنید. در این مثال ما دامنه سلولی A3:A12 را وارد می کنیم. شما ممکن است بخواهید با وارد کردن مقادیری در فیلد Value2 پارامترها را بیشتر کنید اما ما در این مثال به همان دامنه سلولی A3:A12 اکتفا می کنیم.

➏ وقتی پارامترها را وارد کردید OK کنید.



➐ تابع محاسبه شده و نتایجش نمایش داده می شود.

استفاده از دستور Insert Function

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

روش استفاده از دستور Insert Function در اکسل

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

➊ سلول مورد نظر برای ایجاد تابع را انتخاب کنید، در این مثال ما سلول G3 را انتخاب می کنیم.



➋ تب Formulas و سپس دستور Insert Function را انتخاب کنید.



➌ صفحه دستور Insert Function باز می شود.

➍ کلمه کلیدی که نمایانگر دستور مورد نظرتان است را تایپ کنید، در این مثال ما عبارت count days را تایپ می کنیم. و سپس دکمه Go را می زنیم.



➎ در بین نتایج دنبال نتیجه مورد نظرتان باشید، آن را انتخاب کنید و سپس OK کنید. در این مثال ما تابع NETWORKDAYS را انتخاب می کنیم، که تعداد روزهای کاری (غیر تعطیل) بین دو تاریخ را محاسبه می کند.



➏ پنجره Function Arguments باز می شود. در این پنجره باید پارامترهای تابع را تعیین نمایید. در این مثال ما برای پارامتر Start_date سلول E3 و برای پارامتر End_date سلول F3 را وارد می کنیم.

➐ وقتی پارامترها را وارد کردید OK کنید.



➑ تابع محاسبه شده و نتایج آن نمایش داده می شود.

درست مانند فرمولها، توابع نیز می توانند به سلولهای مجاورشان کپی شوند. شما می توانید با استفاده از ویژگی fill handle  این کار را انجام بدهید.

نکاتی در مورد کپی کردن جدول SQL در اکسل

پایگاه داده SQL Server یک سیستم کامل دیتابیس است که توسط شرکت مایکروسافت اکسل ارائه می گردد.

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

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

کپی کردن جدول SQL در Excel

1- ابتدا SQL Server Management را باز کنید

2- سپس بر روی جدول مورد نظر کلیک راست کرده و گزینه Select Top 1000 Rows را انتخاب نمایید.

3- با کلیک بر روی گوشه سمت چپ جدول تمام داده‌ها را انتخاب نمایید.

 

4- بر روی جدول کلیک راست کرده و گزینه Copy را انتخاب نمایید.

برای کپی کردن از کلیدهای ترکیبی Ctrl + C نیز می توان استفاده کرد.

 

5- سپس یک سند اکسل ایجاد نمایید.

6- بر روی آن کلیک راست کرده و گزینه Paste را همانند تصویر انتخاب نمایید.

برای Paste کردن در فایل اکسل ، می توانید از کلیدهای ترکیبی Ctrl + V استفاده نمایید.

 

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

 

دقت داشته باشید این روش ، یک راه حل سریع است و برای جداولی بیشتر استفاده می شود که تعداد سطر های کمتر 1000 ردیف داشته باشند؛

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

نکاتی در مورد یکی از ترفندهای بسیار کاربردی در Excel

مطمئنأ شما نیز پس از نصب بسته نرم افزاری آفیس ، با نرم افزار Microsoft Office Excel برخورد داشته اید.

اکسل نرم افزاری قدرتمند برای محاسبه ، مدیریت و تحلیل داده ها می باشد. بسیاری از کاربران عملیات آماری و اعدادی خود را با این نرم افزار انجام میدهند. اکنون قصد داریم به معرفی 3 ترفند بسیار کاربردی در اکسل بپردازیم. این ترفندها عبارتند از "کشیدن چارت در چند ثانیه" ، "کپی کردن Chart Formats" و "لینک کردن Autoshape و Textbox با اطلاعات داخل سلول". یقینأ استفاده از این ترفند میتواند کار با اکسل را برای شما آسان تر و بهینه تر نماید.

كشیدن چارت در اكسل در چند ثانیه
در واقع در دو مرحله بسیار ساده شما میتوانید به سرعت یك چارت در اكسل بكشید:
1- یك سلول را در میان جدولی كه میخواهید اطلاعات آن به شكل چارت رسم شود انتخاب كنید.
2- كلید F11 را فشار دهید.
چارت شما آماده است ! اكسل چارت شما را در یك Chart Sheet جدید ایجاد میكند. شما میتوانید سایر تنظیمات را طبق روش معمول انجام دهید.

كپی كردن Chart Formats در اكسل
همانگونه كه میدانید تنظیمات مربوط به فرمت چارتها در اكسل بسیار وقت گیر و گاه كسل كننده است. آیا تا به حال چارتی را در اكسل ایجاد كرده اید كه به نظرتان فرمت مناسب و ایده آلی داشته باشد و دوست داشته باشید سایر چارتهایی كه ایجاد میكنید یا قبلا كشیده اید همین فرمت را داشته باشند؟ در این ترفند روش بسیار ساده ای را برای كپی كردن Chart Formats حتی بین فایلهای مختلف معرفی خواهیم کرد.
1- چارتی كه فرمت دلخواهتان را دارد انتخاب (select) كنید.
2- با فشردن دو كلید ctrl+C چارت را كپی كنید.
3- چارت مقصد كه میخواهید فرمت آن را تغییر دهید را انتخاب (select) كنید.
4- از منوی edit بالای صفحه گزینه Paste Special را انتخاب كنید.
5- در پنجره ظاهر شده گزینه Formats را انتخاب كرده و Ok كنید. به همین سادگی كلیه فرمتهای چارت شما كپی شد.

لینک کردن Autoshape و Textbox با اطلاعات داخل سلول در اکسل
آیا تا به حال به مرتبط كردن یك text box یا یك AutoShape به یك سلول نیاز پیدا كرده اید؟ با این ترفند شما می توانید اطلاعات یك سلول را به یك text box یا یك AutoShape بصورت لینك منتقل كنید.
1- اولا یك text box یا یك AutoShape رسم كنید.
2- حالا text box یا AutoShape ایجاد شده را انتخاب (select) كنید.
3- روی formula bar (نوار بالای صفحه كه فرمولها در آن نمایش داده میشوند) كلیك كنید و علامت = را تایپ كنید.
4- حال بر روی سلول مورد نظرتان كلیك كنید و كلید enter را فشار دهید. همانطور كه مشاهده می كنید اطلاعات سلول مور نظرتان به text box یا AutoShape بصورت لینك منتقل شده است. حالا میتوانید از روشهای معمول format دلخواهتان را به text box یا AutoShape بدهید

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

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

 

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

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

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

 

 

1. مزایای استفاده از اکسل:

 

1. امکان برقراری ارتباط بابقیه نرم افزارهای مجموعه آفیس:

مایکروسافت Microsoft Excel به خوبی توان ارسال و دریافت اطلاعات به دیگر نرم افزارها مانند

Microsoft Word, Microsoft Outlook, Microsoft PowerPoint, Microsoft Access و همچنین SQL Microsoft و بقیه پایگاههای داده را دارد .

 

2. انعطاف پذیری بالا و قابلیت تطابق بالا:

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

 

3. توان بالا در تجزیه و تحلیل داده ها:

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

 

4. قابلیت استاندارد سازی و فرموله کردن فرآیندها در اکسل :

با استفاده از توابع موجود و قابلیت فرمول نویسی میتوان با اطمینان محاسباتی مانند محاسبه میزان دستمزد بر اساس ساعات کاری پرسنل پروژه را استاندارد سازی کرد با سرعت و دقت بالا محاسبات را بروز کرد.

 

5. قابلیت بی نظیر ساخت نمودارها:

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

 

6. فرمت بندی متغیر Conditional formatting  در excel :

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

 

7. استفاده آسان و در دسترس بودن:

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

 

8. هوشمندی کافی در کمک به کاربر:

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

 

9. قابلیت ماکرو نویسی و صرفه جویی در زمان:

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

 

10. قابلیت برنامه نویسی به کمک ویژوال بیسک:

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

 

 

1. معایب استفاده از اکسل:

1.امنیت پایین:

 

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

 

2. امکانات تحت شبکه ضعیف:

 

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

 

3.دشواری خطایابی در فرمولهای طولانی:

 

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

 

4.بالا بودن امکان بروز خطاهای سهوی کاربران:

 

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

 

5.عدم دستیابی کامل به امکانات اکسل در گوشی های همراه :

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

 

6.نیاز به تسلط بالا در کاربری های پیچیده:

 

اگر پروژه شما پروژه به نسبه بزرگی باشد و شما تصمیم داشته باشید که برخی کارها را با اکسل انجام دهید به یک متخصص اکسل نیاز دارید تا بتواند فایلهای کاربردی و بدون مشکلات جانبی فراهم کند.

 

7.احتمال بالای از دست رفتن اطلاعات:

 

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

 

8.امکانات ضعیف کنترلی :

کنترل بر روی کار کا ربر و یا پیدا کردن کسی که محتوای برخی سلولها را تغییر داده است به راحتی امکانپذیر نیست وکاربری تحت شبکه مشکلات فوق را چند برابر میکند.

 

9.عدم سازگاری با سیستم عامل های دیگر:

 

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

10.قابلیت نه چندان کارآمد معتبر سازی داده ها در  اکسل.

 

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

 

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

 

 

نکاتی در مورد ایجاد فرمت های عددی (دلخواه) اکسل

برنامه ی Excel دارای فرمت های عددی متعددی است اما شرایطی وجود دارند که مجبور به استفاده از فرمت هایی خارج از فرمت های استاندارد برنامه می شوید. در این قسمت راهی به شما نشان داده می شود که با استفاده از آن می توانید فرمت های مورد نظر خود را ایجاد کنید.
قبل از به کار بردن این هک (روش) بهتر است بببینیم که فرمت های خانه در Excel چگونه در نظر گرفته می شوند. برنامه ی Excel، یک فرمت خانه را به عنوان داشتن این چهار بخش (از چپ به راست) در نظر می گیرد: اعداد مثبت، اعداد منفی، مقادیر صفر و مقادیر متن. هر بخش با یک نقطه و ویرگول (;) جدا شده است.
وقتی یک فرمت دلخواه عددی را ایجاد می کنید، لزومی ندارد هر چهار بخش را مشخص کنید. به عبارت دیگر، اگر فقط دو بخش را مشخص کنید، بخش اول برای اعداد مثبت و مقادیر صفر و بخش دوم برای اعداد منفی در نظر گرفته می شود. اگر فقط یک بخش را مشخص کنید، تمامی بخش های عددی از همان یک بخش پیروی می کنند. مقادیر متن تنها زمانی تغییر می کند که هر چهار بخش را مشخص کرده باشید؛ بخش متنی از آخرین قسمت استفاده می کند.
تنها کلمه ی number (عدد) را به معنی فرمتهای دلخواه به کار رفته برای داده های عددی استفاده نکنید. فرمت های عددی در متن هم به کار می روند.
فرمت عددی دلخواه نشان داده شده در شکل ۱۱، فرمت پولی (ارزی) استاندارد Excel است که مقادیر منفی پولی را با رنگ قرمز نشان می دهد. ما با اضافه کردن یک فرمت مجزا برای مقادیر صفر و یک فرمت دیگر برای مقادیر متنی، آن را تغییر (modify) داده ایم. اگر یک مقدار مثبت را برای مقدار پولی وارد کنید، Excel آن را به شکلی در می آورد که یک ویرگول های جدا کننده به ازای هر سه عدد و دو عدد دهگانی ی اعشاری (deciaml) برای آن نشان داده شود. همین تغییرات برای اعداد منفی نیز انجام می شود با این تفاوت که آن ها به رنگ قرمز خواهند بود. مقدار صفر بدون علامت واحد پولی بوده و دو مقدار دهگانی را نشان خواهد داد. اگر متنی را در یکخانه وارد کنید، برنامه ی Excel بدون توجه به درستی متن وارد شده، عبارت «No Text Please» را نشان می دهد.
توجه به این نکته اهمیت دارد که فرمت کردن مقدار یک خانه بر روی مقدار صحیح اساسی آن اثر نمی گذارد. برای مثال، یک عدد دلخواه در خانه A1 وارد کنید. روی این خانه کلیک راست کرده و سپس مسیر Format Cells → Number → Custom را دنبال و فرمت مورد نظر در ابتدای این قسمت را انتخاب کرده و «Hello» را (به همراه علامت های «») در آن تایپ و روی OK کلیک کنید.
اگرچه این خانه عبارت Hello را نشان می دهد اما شما می توانید مقدار صحیح آن را با انتخاب خانه و مشاهده ی نوار فرمول (Formula bar) یا فشار دادن F2 ببینید. اگر این خانه را مرجع قرار دهید، برای مثال =A1+20، خانه ی مورد نظر نیز از فرمت ایجاد شده پیروی می کند. اگر این خانه (A1) را با تعداد دیگری از خانه های دارای فرمت استاندارد Excel به عنوان مرجع قرار دهید، برای مثال =SUM(A1:A10)، خانه ی مورد نظر باز هم از فرمت ایجاد شده پیروی می کند. فرآیند برنامه ی Excel به این ترتیب است که خانه ی مورد نظر را در فرمت خانه (های) ارجاع داده شده قرار می دهد. اگر یک خانه های متفاوت با فرمت های مختلف ارجاع داده شده باشند، فرمت ایجاد شده تسط کاربر مقدم است.
این نشان می دهد که Excel همیشه یک مقدار صحیح و نه مقدار نمایش داده شده را برای محاسبات استفاده می کند. این مطلب می تواند در شرایطی مانند زمانی که Excel محاسباتش را بر اساس خانه های فرمت شده برای قسمت های دهگانی (decimal) اندک یا بدون قسمت های دهگانی انجام می دهد، غافلگیر کننده باشد. برای مشاهده ی این مطلب، عدد ۱٫۴ را در خانه های A1 و A2 وارد کرده، هر دو خانه را برای نشان دادن قسمت های دهگانی فرمت کرده و سپس =A1+A2 را در یک خانه قرار دهید. بدون شک نتیجه ۳ خواهد بود چرا که Excel آن را گِرد می کند.
در Excel گزینه ای به نام «Precision as Displayed» (به همان دقت نمایش داده شده) وجود دارد که می توانید آن را با دنبال کردن مسیر Office button → Excel Option → Advanced (برای نسخه های قبل از ۲۰۰۷، با استفاده از مسیر Tools → Options → Calculation) پیدا کنید اما باید توجه داشته باشید که این گزینه برای همیشه مقادیر ذخیره شده در خانه ها را از بالاترین دقت (۱۵ رقم) به هر فرمت دیگر نشان داده شده، از جمله قسمت های دهگانی، تغییر می دهد. به بیان دیگر، یک بار که تیک آن فعال و تایید شد، راه برگشتی وجود نخواهد داشت (می توانید امتحان کنید اما اطلاعات دقت اضافی برای همیشه از بین رفته است).
فرمت پیش فرض (default) برای هر خانه، General است. اگر عددی را در یک خانه وارد کنید، برنامه Excel به صورت خودکار، مناسب ترین فرمت از نظر تنظیمات خودش را در نظر می گیرد. برای مثال، اگر ۱۰% را در یک خانه وارد کنید، Excel آن خانه را به صورت درصد فرمت می کند. در بیشتر موارد Excel درست حدس می زند اما مواردی هم پیش می آید که مجبور به تغییر تنظیمات بشوید.

نقش اعداد در اکسل

وقتی از Format Cells استفاده می کنید، وسوسه نشوید که یک فرمت چپ، راست یا افقی مرکزی را انجام دهید! پیش فرض Excel این است که اعداد از راست به چپ و حروف از چپ به راست باشند. اگر این پیش فرض ها را تغییر ندهید، با یک نگاه می توانید متنی یا عددی بودن داده ی یک خانه را تشخیص دهید. همانطور که در مثال ابتدایی دیدیم، به نظر می رسد A1 متنی باشد اما در حقیقت شامل یک عدد است. هر بخش از یک فرمت داده شده، از کد های فرمت کردن خودش استفاده می کند. این کد ها، Excel را مجبور می کنند تا داده ها را مطابق خواسته ی شما نمایش بدهد. بنابراین، برای مثال، فرض کنیم، می خواهیم اعداد منفی داخل پرانتز و تمام اعداد مثبت، منفی و صفر، با دو رقم دهگانی نشان داده شوند. برای این کار از این فرمت استفاده کنید:
۰٫۰۰_ ;(-۰٫۰۰)
اگر می خواهید اعداد منفی قرمز باشند، از این فرمت استفاده کنید:
۰٫۰۰_ ;[Red](-0.00)
به استفاده از براکت در کد توجه کنید. این کد به Excel می گوید که اعداد را با رنگ قرمز نمایش بدهد.
شما می توانید از کدهای مختلفی در بخش های فرمت مورد نظرتان استفاده کنید. جدول های ۱ تا ۵ که از مدارک رسمی Microsoft انتخاب شده اند، این کد ها را تشریح می کنند. جدول ۱، کدهای فرمت کردن، جدول ۲، کد های متن، جدول ۳، کدهای تاریخ، جدول ۴، کدهای زمان و جدول ۵، برخی کدهای مختلف دیگر را فهرست کرده است.
لازم است توجه ویژه ای به آخرین نوع کد های فرمت کردن لیست شده در جدول ۵ داشته باشیم: عملگر های مقایسه ای. فرض کنیم می خواهیم از فرمت ۰٫۰۰_ ;[Red](-0.00) برای نشان دادن اعداد منفی کمتر از -۱۰۰ به رنگ قرمز و داخل براکت استفاده کنید. برای این کار از کد زیر استفاده کنید:
۰٫۰۰_ ;[Red][<-100](-0.00);0.00
کد های [Red] [<-100] (-0.00)که در بخش مربوط به اعداد منفی نشانده شده اند، این کار را ممکن می کنند. با استفاده از این کد و فرمت کردن شرطی می توانید تعداد شرایط فرمت شرطی را دو برابر کرده و از ۳ به ۶ برسانید.
اغلب، کاربران می خواهند مقادیر دلار را با حروف نشان دهند. برای انجام این کار، از فرمت زیر استفاده کنید:
۰ «Dollars and» .۰۰ «Cents»
با استفاده از این فرمت، برای مثال، عدد ۵۵٫۲۵ به شکل
۵۵ Dollars and .25 Cents
نشان داده می شود. اگر می خواهید اعداد را به دلار و سنت تبدیل کنید،

  می توانید از یک فرمت تغییر داده شده برای نمایش کلمات Low، Average یا High (به معنی پایین، متوسط و بالا) در کنار اعداد وارد شده استفاده کنید. به سادگی از کد فرمت زیر استفاده کنید:
[<11]»Low»* ۰;[>20]»High»* ۰;»Average»* ۰
به استفاده از * توجه ویژه ای داشته باشید. این کد، کاراکتر بعدی فرمت را برای پر کردن عرض ستون تکرار می کند که باعث می شود متن های Low، Average یا High به قسمت راست بروند در حالی که اعداد به سمت چپ برده می شوند.

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

در اکسل پیرامون ایجاد و استفاده از PivotTableها  راهکار های متنوعی وجود دارد باماهمراه باشید تاباهم با آنها آشنا شویم

1. یک جدول یا تعدادی سلول (شامل عناوین ستون ها) را که میخواهید در PivotTable شما باشند را انتخاب کنید.

 

2. ازتب Insert بر روی فرمان PivotTable کلیک کنید. 

 

3. کادر محاوره ای Create PivotTable ظاهر خواهد شد. تنظیمات مد نظرتان را انتخاب کنید و بر روی OK کلیک کنید. در اینجا ما از نام Table1 به عنوان منبع داده هایمان استفاده می کنیم و PivotTable را با کلیک بر روی new worksheet در یک ورک شیت جدید قرار می دهیم . 

 

4. یک PivotTable و یک Field List بر روی ورک شیت جدید ظاهر خواهند شد. 

 

5. پس از آنکه یک PivotTable را انتخاب کردید باید تصمیم بگیرید که قصد دارید کدام فیلدها را اضافه کنید. هر فیلد (field) عنوان یکی از ستون های منبع داده ها است. در PivotTable Field List، کادری را که میخواهید فیلد آن به PivotTable شما اضافه شود را تیک بزنید. در این مثال ما می خواهیم مقدار کل فروش هر فروشنده را پیدا کنیم در نتیجه کادرهای مربوط به فیلدهای Salesperson و Order Amount را تیک بزنید. 

 

6. این فیلدها به یکی از چهار قسمت زیر اضافه خواهند شد. در این مثال  می خواهیم  فروشنده (Salesperson) به قسمت سطرها اضافه کنیم و مقدار سفارش را به قسمت مقادیر اضافه کنیم . در نتیجه Salesperson را به Rows و Order Amount را به Values می کشیم و رها (درگ اند دراپ) می کنیم. 

 

7. PivotTable فیلد های انتخاب شده را خلاصه و محاسبه می کند. در این مثال PivotTable مقدار فروش هر فروشنده را نشان می دهد. 

 

 

*دقیقا مانند صفحه گسترده های معمولی می توانید با استفاده از فرمان Sort & Filter بر روی تب Home داده هارا دسته بندی کنید. همچنین می توانید ازهرنوع قالب عددی استفاده کنید. مثلا می توانید فرمت (قالب) اعداد را به Currency تبدیل کنید. با این حال در نظر داشته باشید که بعضی از انواع قالب بندی ها ممکن است در زمان تغییر PivotTable ها ناپدید شوند. 

 

 

* اگر در ورک شیت منبع داده ها را تغییر دهید PivotTable بصورت خودکار آپدیت نمی شود و برای آپدیت کردن آن PivotTable را انتخاب کنید و سپس به Analyze Refresh بروید. 

یکی از بهترین ویژگی های PivotTable ها این است که به سرعت می توانند Pivot ( یا سازمان یافته ) شوند تا بتوانید ورک شیت خود را به روش های مختلف امتحان کنید. Pivot کردن داده ها میتواند برای پاسخ به سوال های مختلف به شما کمک کند و می توانید با امتحان کردن روش های مختلف، روندها و الگوهای مختلف داده ها را کشف کنید. 

اضافه کردن ستون ها در اکسل

تا کنون PivotTable ما تنها یک ستون را نشان میدهد. اما برای داشتن ستون های مختلف باید یک ستون را به قسمت Columns اضافه کنید. 

1. یک فیلد را از Field List به قسمت Columns اضافه کنید. در این مثال ما از فیلد Month استفاده می کنیم. 

 

 

2. PivotTable شما شامل چند ستون خواهد شد. در این مثال برای فروش ماهیانه نیز یک ستون داریم که جمع کل را هم نشان می دهد. 

 

 

تغییر یک سطر یا ستون در    PivotTable در اکسل

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

1. فیلدی را که می خواهید حذف کنید از قسمتی که در آن قرار دارد درگ کنید. همچنین می توانید در Field List تیک کادر مقابل آن فیلد را بردارید. در این مثال ما فیلدهای Month و Salesperson را حذف می کنیم. 

 

2. یک فیلد جدید را به قسمت های مد نظرتان اضافه کنید. در این مثال ما Region را در قسمت Rows قرار داده ایم. 

 

3. PivotTable برای نشان دادن داده های جدید تنظیم خواهد شد. در این مثال، مقدار فروش در هرناحیه نشان داده می شود. 

 

نکاتی پیرامون مزایا ومعایب اکسل

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

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

 

 

1. مزایای استفاده از اکسل:

 

1. امکان برقراری ارتباط بابقیه نرم افزارهای مجموعه آفیس:

مایکروسافت Microsoft Excel به خوبی توان ارسال و دریافت اطلاعات به دیگر نرم افزارها مانند

Microsoft Word, Microsoft Outlook, Microsoft PowerPoint, Microsoft Access و همچنین SQL Microsoft و بقیه پایگاههای داده را دارد .

 

2. انعطاف پذیری بالا و قابلیت تطابق بالا در اکسل :

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

 

3. توان بالا در تجزیه و تحلیل داده ها:

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

 

4. قابلیت استاندارد سازی و فرموله کردن فرآیندها:

با استفاده از توابع موجود و قابلیت فرمول نویسی میتوان با اطمینان محاسباتی مانند محاسبه میزان دستمزد بر اساس ساعات کاری پرسنل پروژه را استاندارد سازی کرد با سرعت و دقت بالا محاسبات را بروز کرد.

 

5. قابلیت بی نظیر ساخت نمودارها:

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

 

6. فرمت بندی متغیر Conditional formatting:

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

 

7. استفاده آسان و در دسترس بودن:

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

 

8. هوشمندی کافی در کمک به کاربر:

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

 

9. قابلیت ماکرو نویسی و صرفه جویی در زمان:

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

 

10. قابلیت برنامه نویسی به کمک ویژوال بیسک:

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

 

 

1. معایب استفاده از اکسل:

1.امنیت پایین:

 

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

 

2. امکانات تحت شبکه ضعیف:

 

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

 

3.دشواری خطایابی در فرمولهای طولانی:

 

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

 

4.بالا بودن امکان بروز خطاهای سهوی کاربران:

 

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

 

5.عدم دستیابی کامل به امکانات اکسل در گوشی های همراه :

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

 

6.نیاز به تسلط بالا در کاربری های پیچیده:

 

اگر پروژه شما پروژه به نسبه بزرگی باشد و شما تصمیم داشته باشید که برخی کارها را با اکسل انجام دهید به یک متخصص اکسل نیاز دارید تا بتواند فایلهای کاربردی و بدون مشکلات جانبی فراهم کند.

 

7.احتمال بالای از دست رفتن اطلاعات:

 

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

 

8.امکانات ضعیف کنترلی :

کنترل بر روی کار کا ربر و یا پیدا کردن کسی که محتوای برخی سلولها را تغییر داده است به راحتی امکانپذیر نیست وکاربری تحت شبکه مشکلات فوق را چند برابر میکند.

 

9.عدم سازگاری با سیستم عامل های دیگر:

 

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

10.قابلیت نه چندان کارآمد معتبر سازی داده ها.

 

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

 

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

 

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

نکاتی پیرامون تغيير سبک سلول ها در اکسل

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

 اما مي توانيد به عنوان کاربر جديد، در هر کارپوشه (که به طور پيش فرض با نام Book1,2,3 نام گذاري شده است) سبک پيش فرض اکسل را تغيير دهيدو تنظيمات جديد را روي تمامي سلول ها و برگه ها اعمال کنيد.

نکاتی پیرامون تغيير سبک سلول ها در اکسل
 براي اين کار از منوي Format گزينه Style و سپس در منوي Style name سبک Normal را انتخاب کنيدو در ادامه دکمه Modify را برگزينيد. حال پنجره Format Cells باز خواهد شد. اکنون بايد سبک خود را با استفاده از گزينه هاي موجود در زبانه هاي اين پنجره اعمال کنيد و سپس OK را بزنيد.

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

 براي اين کار ابتدا در نوار ابزار راست کليک و Customize را انتخاب کنيد. سپس در زبانه Commands  در اکسل از سمت چپ گزينه Format را انتخاب کنيد و در سمت راست نوار ابزار باز شونده، Style را به کمک ماوس در کنار نوار ابزارهاي ديگر قرار دهيد.

فرض کنيد که قصد داريد سبک سلول A1 را ذخيره کنيد؛ ابتدا سلول A1 را انتخاب و سپس در نوار ابزار Style نام سبک را تايپ کنيد و کليد Enter را بزنيد تا سبک ذخيره شود. حال مي توانيد بعد از انتخاب سلولي ديگر، سبک موجود در اين نوار ابزار را انتخاب کنيد تا روي سلول بعدي اعمال شود.

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

نکاتی پیرامون درج فرمول در سلول ها در اکسل

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

مقادیر ثابت
مقادیر ثابت مقادیری هستند که ثابت بوده و تغییر نمی کنند. این مقادیر می توانند عددی یا متنی باشند. در فرمول نویسی باید مقادیر ثابت متنی را بین دو علامت " قرار دهیم. در این صورت محاسبه ای روی این مقادیر انجام نمی شود.

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

عملگرها در اکسل
به طور کلی عملگرها به چهار گروه تقسیم می شوند. این گروه ها را در زیر بررسی می کنیم.

عملگرهای محاسباتی: عملگرهای محاسباتی، عملگرهایی هستند که از آنها برای محاسبات عددی استفاده می شود. این عملگرها عبارتند از + و - و * و / و ٪ و ^ که به ترتیب و از راست به چپ برای محاسبه جمع، تفریق، ضرب، تقسیم، درصد و توان به کار می روند.

عملگرهای مقایسه ای: از عملگرهای مقایسه ای برای مقایسه مقادیر استفاده می شود. این عملگرها عبارتند از =، <، =<، >، => و <>. این عملگرها به ترتیب و از راست به چپ مساوی، بزرگ تر، بزرگ تر مساوی، کوچک تر، کوچک تر مساوی و نامساوی بودن دو عدد را مقایسه می کند. نتیجه حاصل از عملیات این عملگرها می تواند مثبت (درست) یا منفی (نادرست) باشد.

عملگرهای رشته ای: از این عملگر برای چسباندن دو رشته به هم استفاده می شود. استفاده از عملگر & در سلول ها، برای اتصال یا الحاق داده های متنی است.

عملگرهای آدرس: از عملگرهای آدرس برای تعیین محدوده آدرس استفاده می شود. این عملگرها عبارتند از : و ; . از عملگر نخست برای معرفی محدوده متوالی سلول ها و از عملگر دوم برای معرفی محدوده نامتوالی سلول ها استفاده می شود.

اولویت انجام محاسبات ریاضی: اگر با ریاضی آشنایی داشته باشید، عملگرها از اولویتی برای انجام محاسبات برخوردارند. این اولویت ها در نرم افزار اکسل نیز اجرا می شوند و در فرمول نویسی از اهمیت بالایی برخوردار هستند. اولویت ها در ریاضی به ترتیب پرانتز، درصد، توان، ضرب و تقسیم، جمع و تفریق و الحاق متن است که با علامت های زیر نشان داده می شوند:
()، ٪، ^، * /، + -، &
مثال: فرض کنید می خواهیم عبارت ۴*۲+۱۰ را به دو حالت مختلف اجرا کنیم و نتیجه را بر اساس اولویت عملگرها مشاهده کنیم. در حالت اول حاصل عبارت ۴*۲+۱۰ عدد ۱۸ خواهد شد. چرا که عملگر ضرب، از اولویت بالاتری نسبت به عملگر تقسیم برخوردار است. اما اگر این عبارت را به صورت (۱۰+۲)*۴ بنویسیم، حاصل ۴۸ خواهد شد. چرا که عملگر پرانتز، اولویت بالاتری نسبت به عملگر ضرب دارد.

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

ساختار توابع: هر تابع دارای یک نام و معمولا تعدادی ورودی است و ساختاری به صورت زیر دارد: (.... ; ورودی ۳; ورودی ۲; ورودی ۱) نام تابع
مثال از یک تابع: برای اینکه با عملکرد یک تابع به صورت عملی آشنا شوید، بهتر است یک مثال را در اکسل اجرا کنید. برای اینکار از تابع SUM یا جمع استفاده کنید. این تابع، سلول های انتخاب شده را با یکدیگر جمع می کند.

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

روش درج فرمول با استفاده از Function Wizard: اکسل فرمول های زیادی دارد و همانطور که پیشتر اشاره شد، هر تابع نیاز به «ورودی هایی» برای محاسبه دارد. با استفاده از امکان Function Wizard می توانید از تمام توابع اکسل استفاده کنید و نتیجه محاسبات را در سلول مورد نظر مشاهده کنید.
برای این کار از روی نوار فرمول، بر روی fx کلیک کنید تا کادر Insert Function باز شود.

توضیح کادر Insert Function: در بخش Search for a function می توانید نام تابع مورد نظرتان را تایپ کنید. در بخش Or select a category می توانید یکی از بخش های موجود را انتخاب کنید. این بخش ها توابع اکسل را دسته بندی کرده است. مثلا اگر بخش Financial را انتخاب کنید، توابع مالی در کادر Select a function نشان داده خواهند شد.
با انتخاب گزینه All همه توابع اکسل و با انتخاب گزینه Most Recently used نیز توابعی که اخیرا استفاده کرده اید نشان داده می شوند. اکنون در بخش Search for a function می توانید نام تابع مورد نظرتان را تایپ کنید و از بخش Select a function آن را انتخاب کنید.
پس از انتخاب تابع SUM در اکسل  بر روی دکمه OK کلیک می کنیم. در پنجره جدید که Function Arguments نام دارد، باید ورودی های تابع را وارد کنیم.

 

اکنون با کلیک بر روی OK نتیجه این تابع که عدد ۳۰ است در سلول انتخاب شده نمایش داده می شود.

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

یکی از کاربرد های پرکاربرد اکسل انجام محاسبات پیچیده ریاضی میباشد، یکی از این ابزارها تجزیه و تحلیل what-if می باشد. این ابزار به شما کمک می کند، تا سوالها و پاسخهایی را با داده های خود آزمایش کنید، حتی در زمانیکه داده های شما تکمیل نشده باشند هم این ابزار کار می کند. در این درس، شما یاد خواهید گرفت که چگونه از ابزار تجریه و تحلیل what-if اکسل استفاده کنید، نام این ابزار Goal Seek (جستجوی هدف) می باشد.

 

ابزار 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 نتیجه ممکن را برای یک وام خودرو مشاهده نماییم.
  

 

 

 

 

نکاتی در مورد ساختار جداول چرخشی (PivotTables) در اکسل

استفاده از ویژگی PivotTables اکسل

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



خوشبختانه، ویژگی PivotTable می تواند فورا محاسبه و خلاصه سازی داده ها را برای ما انجام دهد تا خواندن داده ها برای ما خیلی ساده تر شود.


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

روش ایجاد یک PivotTable در اکسل

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



➋ ابتدا تب Insert را انتخاب کرده و سپس بر روی دستور PivotTable کلیک کنید.



➌ پنجره Create PivotTable نمایان می شود. تنظیمات مورد نظرتان را انتخاب کنید، و سپس ok را کلیک کنید. در اینجا ما از جدول Table1 به عنوان منبع داده استفاده می کنیم و PivotTable را در یک برگه جدید ایجاد می کنیم.



➍ یک PivotTable خالی و یک لیست از فیلدها در یک برگه جدید ظاهر می شوند.



➎ وقتیکه شما PivotTable را می سازید، باید تصمیم بگیرید که کدام فیلدها را می خواهید به PivotTable اضافه کنید. هر فیلد با توجه به منبع داده اصلی دارای یک هدر ستون می باشد. فیلدهایی را که قصد اضافه کردنشان به PivotTable را دارید تیک بزنید. در این مثال، ما می خواهیم جمع مبلغ فروشی که توسط هر فروشنده انجام شده است را بدانیم، پس فیلدهای Salesperson و Order Amount را تیک می زنیم.



➏ فیلدهای انتخاب شده توسط شما به یکی از 4 ناحیه زیر اضافه می شوند. در مثال ما، فیلد Salesperson به ناحیه Rows (ردیف ها) اضافه می شود و فیلد Order Amount به ناحیه Values (مقادیر) اضافه می گردد. البته شما خودتان نیز امکان چرخش و تغییر محل این فیلدها را دارید. کافیست فیلدها را با ماوس و روش نگهداشتن و کشیدن در بین این 4 ناحیه جابجا کنید.



➐ ویژگی PivotTable فیلدهای انتخاب شده را محاسبه و خلاصه سازی می کند. در این مثال، PivotTable جمع مبلغ فروش هر فروشنده را به صورت جداگانه به ما نمایش می دهد.

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

 

اگر هر کدام از سلولهای اکسل را که در PivotTable استفاده شده است را تغییر بدهید، بصورت اتوماتیک PivotTable شما هم متناسب با آن تغییر بروز رسانی می شود. برای اینکه بصورت دستی بتوانید PivotTable را بروز رسانی کنید می توانید از Analyze و سپس Refresh استفاده کنید.

 

چرخش داده ها در PivotTables اکسل

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

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

تا حالا، PivotTable ما می توانست تنها یک ستون را در یک زمان نمایش دهد. برای اینکه بتوانیم چندین ستون را همزمان نمایش بدهیم، باید به ناحیه Columns (ستونها) فیلدهایی را اضافه کنیم.

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



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

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

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

➊ برای حذف هر فیلد آن را با ماوس بکشید و به ناحیه ای خارج از ناحیه خودش ببرید. همچنین برای حذف یک فیلد می توانید در قسمت فیلدها تیک آن را بردارید. در این مثال ما فیلدهای Month و Salesperson را حذف می کنیم.



➋ حالا یک فیلد جدید را به ناحیه مورد نظرتان بکشید. در این مثال ما فیلد Region را به ناحیه Rows می کشیم.



➌ جدول PivotTable ما تنظیم می شود (یا می چرخد) تا داده های جدید را نمایش بدهد. در این مثال هم اکنون ما می توانیم جمع مبلغ فروش مربوط به هر ناحیه را مشاهده کنیم.

کاربرد توابع ریاضی و فرمول های نرم افزار اکسل

همانطور که می دانیم اکثر کاربران نرم افزار اکسل (excel) برای انجام کارهای محاسباتی ریاضی نیازمند استفاده از فرمول نویسی در سلول ها هستند. در این مقاله قصد داریم تا حدودی نحوه کار کردن با امکانات محاسباتی اکسل و کار کردن با فرمول های آن آموزش داده شود.

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

 

عناصرتشکیل دهنده فرمول ها در اکسل

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

 

نحوه درج فرمول در سلول ها

برای درج فرمول در یک سلول، ابتدا کاراکتر مساوی = را تایپ می کنیم و سپس فرمول را طبق قواعدی که در این درس خواهیم آموخت، به سلول وارد می کنیم.

همانطور که در تصویر زیر می بینید، پس از وارد کردن کاراکتر مساوی در سلول E13 می توان از نوار فرمول، یکی از فرمول های پیش فرض اکسل را انتخاب کرد.

 

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

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

مقادیر ثابت

مقادیر ثابت مقادیری هستند که ثابت بوده و تغییر نمی کنند. این مقادیر می توانند عددی یا متنی باشند. در فرمول نویسی باید مقادیر ثابت متنی را بین دو علامت ”  قرار دهیم. در این صورت محاسبه ای روی این مقادیر انجام نمی شود.

آدرس سلول ها

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

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

عملگرها

به طور کلی عملگرها به چهار گروه تقسیم می شوند. این گروه ها را در زیر بررسی می کنیم.

عملگرهای محاسباتی: عملگرهای محاسباتی، عملگرهایی هستند که از آنها برای محاسبات عددی استفاده می شود. این عملگرها عبارتند از و – و * و / و ٪ و ^ که به ترتیب و از راست به چپ برای محاسبه جمع، تفریق، ضرب، تقسیم، درصد و توان به کار می روند.

عملگرهای مقایسه ای: از عملگرهای مقایسه ای برای مقایسه مقادیر استفاده می شود.

این عملگرها عبارتند از =، <، =<، >، => و <>. این عملگرها به ترتیب و از راست به چپ مساوی، بزرگ تر، بزرگ تر مساوی، کوچک تر، کوچک تر مساوی و نامساوی بودن دو عدد را مقایسه می کند.

نتیجه حاصل از عملیات این عملگرها می تواند مثبت (درست) یا منفی (نادرست) باشد.

به طور مثال نتیجه حاصل از عملیات 5=4 یک نتیجه �نادرست� است چرا که عدد ۵ مساوی با عدد ۴ نیست.

عملگرهای رشته ای: از این عملگر برای چسباندن دو رشته به هم استفاده می شود. استفاده از عملگر & در سلول ها، برای اتصال یا الحاق داده های متنی است.

به طور مثال اگر مقدار سلول B3  =”سلام” باشد و سلول C3 =”جهان”، می توانیم در سلول D3 عبارت B3&C3 را به عنوان فرمول یعنی پس از تساوی وارد کنیم تا در این سلول عبارت “سلام جهان” درج شود.

عملگرهای آدرس: از عملگرهای آدرس برای تعیین محدوده آدرس استفاده می شود. این عملگرها عبارتند از : و ; . از عملگر نخست برای معرفی محدوده متوالی سلول ها و از عملگر دوم برای معرفی محدوده نامتوالی سلول ها استفاده می شود.

به طور مثال نتیجه عبارت A1:A10 سلول های A1 تا A10 است اما برای اشاره به سلول های خاص از عبارت A1;A5;A7 استفاده می شود.

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

()، ٪، ^، * /، -، &

مثال: فرض کنید می خواهیم عبارت 4*2 10 را به دو حالت مختلف اجرا کنیم و نتیجه را بر اساس اولویت عملگرها مشاهده کنیم.

در حالت اول حاصل عبارت 4*2 10 عدد 18 خواهد شد. چرا که عملگر ضرب، از اولویت بالاتری نسبت به عملگر تقسیم برخوردار است.

اما اگر این عبارت را به صورت (10 2)*4 بنویسیم، حاصل 48 خواهد شد. چرا که عملگر پرانتز، اولویت بالاتری نسبت به عملگر ضرب دارد.

توابع

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

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

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

• توابع مالی

• توابع تاریخ و ساعت

• توابع ریاضی و مثلثاتی

• توابع آماری

• توابع جستجو و مرجع

• توابع پایگاه داده

• توابع متنی

• توابع منطقی

• توابع اطلاعاتی

• توابع مهندسی

ساختار توابع: هر تابع دارای یک نام و معمولا تعدادی ورودی است و ساختاری به صورت زیر دارد: (…. ; ورودی ۳; ورودی ۲; ورودی ۱) نام تابع

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

در تصویر زیر، نام تابع SUM است و ورودی های تابع نیز سلول های A1 و A2 هستند.

 

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

درج فرمول با استفاده از Function Wizard در اکسل

اکسل فرمول های زیادی دارد و همانطور که پیشتر اشاره شد، هر تابع نیاز به �ورودی هایی� برای محاسبه دارد. با استفاده از امکان Function Wizard می توانید از تمام توابع اکسل استفاده کنید و نتیجه محاسبات را در سلول مورد نظر مشاهده کنید.

برای این کار از روی نوار فرمول، بر روی fx کلیک کنید تا کادر Insert Function باز شود.

 

توضیح کادر Insert Function: در بخش Search for a function می توانید نام تابع مورد نظرتان را تایپ کنید.

در بخش Or select a category می توانید یکی از بخش های موجود را انتخاب کنید.

این بخش ها توابع اکسل را دسته بندی کرده است. مثلا اگر بخش Financial را انتخاب کنید، توابع مالی در کادر Select a function نشان داده خواهند شد.

با انتخاب گزینه All همه توابع اکسل و با انتخاب گزینه Most Recently used نیز توابعی که اخیرا استفاده کرده اید نشان داده می شوند.

اکنون در بخش Search for a function می توانید نام تابع مورد نظرتان را تایپ کنید و از بخش Select a function آن را انتخاب کنید.

حال فرض کنید می خواهیم در کادر Insert Function تابع جمع را درج کنیم. همانطور که در مثال درج فرمول از طریق وارد کردن علامت = نیز گفتیم، این تابع با نام SUM شناخته می شود.

پس از انتخاب تابع SUM بر روی دکمه OK کلیک می کنیم. در پنجره جدید که Function Arguments نام دارد، باید ورودی های تابع را وارد کنیم.

در این مثال سه عدد  ۱۲ و ۱۰ و ۸ را وارد می کنیم.

 

اکنون با کلیک بر روی OK نتیجه این تابع که عدد ۳۰ است در سلول انتخاب شده نمایش داده می شود.

نکاتی در مورد چگونگی کپی کردن Worksheet بدون کپی فرمول ها در اکسل

در هنگام کار با اکسل شاید برای شما هم زیاد پیش آمده باشد که بخواهید یک worksheet را به یک workbook دیگر کپی کنید اما نخواهید فرمول ها قابل مشاهده شود.

 

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

برای این کار فایل اصلی را باز کنید، و در sheet که داده ها به همراه فرمول ها در آن موجود هستند بروید. روی sheet راست کلیک کرده و “Move Or Copy” را بزنید.

 

در صفحه ای که باز می شود، از منوی انتخاب “To book”، “(new book)” را انتخاب کنید و بعد تیک  “Create a copy” را هم بزنید. در پایان هم روی دکمه OK کلیک کنید.

 

بدین ترتیب یک  workbook جدید باز خواهد شد و داده های که در فایل قبلی داشتید، اینجا paste خواهند شد.

حالا برای انتخاب همه سلول های حاوی داده ها، از کلید ترکیبی Ctrl+A  استفاده کنید، یا مطابق تصویر روی گوشه ی بالایی سلول ها کلیک کنید.

 

حالا در زبانه ی Home، روی Paste کلیک کنید، و بعد مطابق تصویر “Values” را انتخاب کنید.

 

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

 

مطالبی ارزنده پیرامون رمزگذاری روی فایل‌های اکسل

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

 

برای اینکار کافیست روی دکمه File در گوشه بالا سمت راست کلیک کنید. پس از باز شدن بخش BackStage  روی دکمه Protect Workbook کلیک کنید و از منوی باز شده گزینه Encrypt With Password راکلیک کنید.

 

در پنجره باز شده پسورد مورد نظررا تایپ کنید و پس از تایید رمز در پنجره ای که مجددا باز میشود رمز رادوباره تایپ کنید و  ok را کلیک کنید.

 

حالا فایل با پسورد محافظت میشود و کسی که  بخواهداین فایل را باز کند باید پسوردی که  روی فایل گذاشتید رادر اختیار داشته باشید

نکاتی در مورد رنگ کردن یک در میان سطر هادر اکسل

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

 

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

 

سپس به مسیر Home → Styles → Conditional Formatting → New rule رفته تا پنجره New Formatting Rule برای شما باز شود. در این پنجره بر روی گزینه آخر یعنی Use a formula to determine which cells to format  کلیک کنید.

 

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

 

تابع ([Row([reference شماره سطر مرجعی که درون پرانتز به آن معرفی می شود را نشان می دهد. البته مشخص کردن مرجع برای آن اختیاری است و در صورت خالی گذاشتن درون پرانتز (()Row) شماره سطر همان سلولی که درون آن این فرمول تایپ شده است را بر می گرداند.

 

تابع (ISODD(number در اکسل بررسی می کند که عدد درون آن یک عدد فرد است یا نه . اگر فرد باشد مقدار  True و اگر نباشد مقدار False را بر می گرداند.

 

حال اگر از فرمول ترکیبی (()ISODD(Row استفاده کنیم ، بررسی می شود که آیا شماره سطر سلولی که در آن این فرمول تایپ می شود فرد است یا نه ، که اگر فرد باشد True و اگر نباشد پاسخ آن False  خواهد بود.

 

به Conditional Formatting بر می گردیم. همان گونه که در تصویر زیر مشاهده می کنید ، باید فرمول (()ISODD(Row= را در قسمت  :Format values where this formula is true تایپ کنیم. در این قسمت بررسی می شود که آیا پاسخ این فرمول True هست یا نه ، اگر باشد Format مشخص شده به آن اختصاص داده می شود.

 

در قسمت Format به طور مثال در زبانه Fill یک رنگ دلخواه را انتخاب کنید و در نهایت Ok

 

مشاهده می کنید که برای تمام فضای انتخابی شما ( یا کل شیت ) سطرها یکی در میان رنگی شده اند.

 

 

 

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

در بعضی از مواقع در نرم افزار اکسل  لازم است که داداه های یک ستون به چند ستون برای استفاده بهتر مجزا شوند

به عنوان مثال، داده های یک ستون از جدول مشخصات کارمندان بصورت تاریخ ثبت شده اند، که نیاز است برای جستجو و گروه بندی از (شماره ماه) تاریخ ثبت شده استفاده کنیم، در صورتی که ستون تاریخ به فرمت و شکل (##/##/####) درج شده است و امکان فیلتر کردن به تفکیک ماه وجود ندارد.

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

 با آموزش اکسل امروز همراه باشید 

نکته: از این روش فقط برای داده هایی که یک کاراکتر مشترک و مشخص دارند می توان استفاده کرد (مانند Tab,Comma,Space,Slash,Back Slash,Semicolon)
1- ابتدا ستونی را که می خواهیم تبدیل را انجام دهیم انتخاب می کنیم

 

 

2- به تب Data رفته و گزینه Text to Colunms را کلیک می کنیم طبق تصویر زیر

 

3- در مرحله یک گزینه Delimited را انتخاب کرده و بر روی Next کلیک می کنیم

 

4- در مرحله دوم باید کاراکتر مشترک برای جداسازی داده ها در فیلد Other مشخص کنید کهکاراکتر اسلش (/) کاراکتر جدا کننده محسوب می شود و سپس بر روی Next کلیک کنید

 

 

5- و در مرحله آخر باید نوع داده جداشده در ستون های دیگر را مشخص کنید، که بهترین گزینه برای داده های تاریخ (Text) می باشد، به روش که باید هر ستون را (که با شماره های قرمز رنگ از 1 تا 3 نشان داده شده) ابتدا کلیک کنید و سپس نوع داده را Text انتخاب کنید و بعد از این کار بر روی گزینه Finish کلیک کنید

 

و در انتها خواهید دید که ستون C در تصویر اول به سه ستون تاریخ سال/تاریخ ماه/تاریخ روز تبدیل می شود

 

 

---

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

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

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

1. استفاده بیش از حد از توابع Volatile  در اکسل : توابع Volatile توابعی هستند که با هر بار محاسبه مجدد ورکبوک آنها هم مجدداً محاسبه میشوند . برخی از این توابع شامل RAND، RANDBETWEEN ،NOW ،TODAY ،OFFSET ،CELL ،INDIRECT میشود. حالا اگر از این توابع در فایل زیاد استفاده کرده باشید احتمالاً میتوانید حدس بزنید با هر بار تغییر دادن مقدار یک سلول چه بلایی سر فایلتون میا ید. بنابراین بهتراست تا حد امکان از این توابع زیاد استفاده نشود و از توابع   Index، Sumif در اکسل  و … استفاده شود
2. استفاده زیاد از Conditional formatting در EXCEL   : فرمول های موجود در Conditional formatting ها هم با هر بار محاسبه ورکشیت دوباره محاسبه میشود. بنابراین باید در استفاده از آنها هم دقت زیادی کرد.
3. وجود سلول های بدون استفاده در شیت ها : گاهی ممکن است  سلول هایی که توسط اکسل به عنوان سلول های فعال یک شیت لحاظ میشود با سلول هایی که در شیت فعال هستند متفاوت باشد.
   
   برای اینکه متوجه شویم اکسل کدام سلول را به عنوان آخرین سلول فعال شیت در نظر گرفته است میتوانیم از کلید ترکیبی ctrl+end استفاده کنیم. اگر سلولی که اکسل به عنوان سلول آخر در نظر گرفته اشتباه باشد باعث میشود حجم فایل بدون دلیل بالا رود .                                            برای حل این مشکل  باید سطرها و ستون های اضافه را انتخاب و پاک کنید. بعد فایل را ذخیره نماییم. این مساله بخصوص در مورد افزایش حجم فایل ها خیلی اثرگذار است.
4. لینک کردن به سایر ورکبوک ها: لینک کردن یک ورکبوک به ورکبوک های دیگر باعث کاهش سرعت میشود. بهتراست تا جایی که امکان دارد اطلاعات را در یک ورکبوک قرار دهید یا تا جایی که امکان دارد به ورکبوک های خارجی کمتری لینک کنید.
5. وجود شیت های بیش از حد: بهترست  تا حد ممکن ورکبوک هایی که میسازیم تعداد شیت های کمتری داشته باشد. این مساله هم در افزایش سرعت فایل اکسل بی تاثیر نیست.

رعایت موارد بالا در تهیه فایل های اکسل به خصوص موارد 1 و 3 تا حدود زیادی میتواند به افزایش سرعت فایل های اکسل و کاهش حجمبسیار تاثیر گذار باشد .

نکاتیدر مورد معانی خطاهای فرمول نویسی اکسل

در آموزش های گذشته در مورد خطا های اکسل به صورت کامل توضیح داده ایم با آموزش اکسل  امروز همراه باشید تا با معانی خطاها نیز اشنا شویم                                                                                          

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

!VALUE# : 
این خطا به این معنیاست که شما نوع اشتباهی از داده رو به عنوان ورودی فرمول استفاده کردید. به عنوان مثال ممکنن است د ر تابعی که پارامتری رو از نوع یک سلول میگیره یک محدوده رو وارد کرده باشید و یا در فرمول جایی که باید به عدد ارجاع داده شود متن به جای عدد وجود داشته باد. به عنوان مثال در فرمول A1/A2 شما مقدار موجود در سلول A2 یک متن باشه.  ?NAME# : 
این خطا زمانی رخ میده که شما نام تابعی رو در فرمولتون اشتباه نوشته باشید، مقادیر متنی استفاده شده در فرمول رو داخل علامت ” نگذاشته باشید و یا پرانتزهای خالی مربوط به تابع رو جا گذاشته باشید.

!NUM# : این خطا یعنی یکی از اعدادی که در فرمول شما وجود دار د مشکل دارد. به عنوان مثال عدد خیلی خیلی بزرگ یا خیلی خیلی کوچک هست.

DIV/0 # : این خطا نشان دهنده ی  این هست که  جایی از فرمولتون مقداری رابر صفر تقسیم کرده باشید. ممکنه مقدار یکی از سلول هایی که در فرمولتون استفاده کردید صفر باشه یا فاقد مقدار باشد. اکسل سلول هایی رو که فاقد مقدار هستند در محاسبات صفر در نظر میگیره و اگر جایی عددی رو بر اونها تقسیم کرده باشید این خطا نشون داده میشود.

!REF# : این خطا به معنی این است که شما در فرمول به سلولی اشاره کردید که وجود ندارد. این خطا معمولا زمانی رخ میدهد که شما سلول هایی رو پاک کنید یا به عنوان مثال فرمول را  که به صورت نسبی نوشته شده و در آن به سلول A3 اشاره شده، از سلول A4 اون شیت به سلول A2 شیت دیگه کپی کنید.

N/A# : این خطا معمولا زمانی نشان داده میشود که مقدار مورد نظر شما پیدا نشود. به عنوان مثال شما مقدار موردنظر شما در یک تابع LOOKUP در هیچ یک از سلول های محدوده ای که برای تابع تعریف کردید وجود نداشته باشه.

!NULL# : این خطا بیانگر این است که در فرمول جایی که باید از علایم ریاضی استفاده کنید از فاصل استفاده کردید . به عنوان مثال به جای فرمول  =A1+A2+A3  اشتباها مقدار  =A1+A2 A3 راوارد کردید. یکی از دلایل دیگری که باعث میشود  این فرمول نمایش داده شوند این است که  جایی در فرمول برای مشخص کردن یک محدوده بین دو سلول علامت : رو جا انداخته اید.

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

ترسیم فرمول های پیچیده و ترکیبی و چگونگی استفاده از توابع در اکسل

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

با ذکر یک مثال این مسئله بیشتر روشن می شود.

فرض کنید در یک ستون تعدادی عدد را وارد کرده اید. اکنون می خواهیم فرمولی را بنویسیم که مشخص نماید آیا اعداد وارد شده تکراری دارند یا خیر. (توجه داشته باشید هدف این مثال فهمیدن منطق این فرمول ترکیبی نیست بلکه هدف فقط روش ترکیب و بدست آوردن یک فرمول پیچیده تر و حذف ستون های کمکی است)
برای انجام این کار آن را به چند مرحله تقسیم می کنیم.
اعداد را در ناحیه B1 تا B10 وارد می کنیم.

 

ناحیه داده ها را نامگذاری می کنیم.(در این مثال Data)
الف ) تابعی می نویسیم که در صورت وجود اعداد تکراری، اولین عدد تکراری را مشخص کند. Mode (Data) (این فرمول در سلول کمکی E2 نوشته شده است)

تابع شمارش شرطی در اکسل
ب)  تابع شمارش شرطی را می نویسیم تا مشخص نماید که آیا نتیجه اولین تابعی که نوشتیم true یا false است COUNTIF(Data,E2)>1 (این فرمول در سلول کمکی F2 نوشته شده است)

ج) تابع شرطی را می نویسیم که در صورت true بودن نتیجه شرط قسمت (ب) عبارت "لیست با تکرار" و در صورت false بودن نتیجه قسمت (ب) عبارت "لیست بدون تکرار" را نمایش دهد.
IF)F2,"لیست با تکرار","لیست بدون تکرار")
(این فرمول در سلول کمکی G2 نوشته شده است)
اکنون در سلول نهایی تابع ترکیبی را به صورت
IF(COUNTIF(Data,MODE(Data))>1(,"لیست با تکرار","لیست بدون تکرار"
می نویسیم که این تابع مستقل از سلول های کمکی است (یعنی آدرس های E2 و F2 در آن وجود ندارد) و با حذف آنها در نتیجه تغییری حاصل نمی شود.
ما در تابع نهایی به جای آدرس های مرجع E2 و F2 فرمول واقع در این سلول ها را نوشته ایم.                                                                 چگونگی استفاده از توابع در اکسل                                                                                    تابع یك فرمول از پیش نوشته شده و نهادین است كه در وقت شما صرفه جویی می كند.excel بیش از 300 تابع دارد بنابراین مطمئناً می توانید تابع ریاضی مورد نظر خود را پیدا كنید. برای استفاده از یك تابع، یك = ، اسم تابع و آرگومان های تابع را داخل پرانتز تایپ كنید. مانند = SUM (C4 : C22) . بین آرگومان ها یك كاما یا ویرگول بگذارید. وقتی كه اسم تابع را تایپ می كنید، آرگومان های آن در یك Screen Tip ظاهر می شود تا به شما كمك كند تا آنها را به ترتیب درست وارد كنید. اگر به كمك بیشتری نیاز دارید، كادر محاوره ای Insert Functions در اختیار شماست.

 

 روی دكمه Insert Functions كلیك كنید  

 

 روی سلولی كه می خواهید نتیجه تابع در آن ظاهر شود كلیك كنید. بعد روی دكمه Insert Functions در نوار فرمول كلیك كنید تا كادر محاوره ای Insert Functions باز شود.

یكی از توابع را انتخاب كنید  

 

این كادر محاوره ای دو راه برای پیدا كردن یك تابع در اختیار شما قرار می دهد: شرح تابع مورد نظر را تایپ كنید و روی Go كلیك كنید. توابع مربوطه در قسمت Select a Functions لیست می شوند. یا اینكه، از لیست كشویی، یك دسته یا گروه را انتخاب كنید. لیست توابع مربوط به این دسته در كادر پایین ظاهر می شوند. در این لیست اسكرول كنید و روی یك تابع كلیك كنید. شرح تابع انتخاب شده در پایین كادر محاوره ای ظاهر می شود. وقتی كه تابع مورد نظر را پیدا كردید، روی Ok كلیك كنید.

 

 آرگومان اول را وارد كنید  

 

كادر محاوره ای Functions Arguments مطابق با تابعی كه در مرحله 2 انتخاب كردید باز می شود. مراحل این كار عملی، مربوط به تابع IF می باشند. این تابع به سه آرگومان نیاز دارد: عبارتی كه باید ارزیابی شود، قسمتی كه در صورت درست بودن عبارت باید انجام شود و قسمتی كه در صورت نادرست بودن عبارت باید انجام شود. برای وارد كردن آرگومان ها، روی سلول های مورد نظر كلیك كنید (اگر لازم باشد كادر محاوره ای را به قسمتی بكشید كه بتوانید سلول ها را به راحتی انتخاب كنید).

 

 مقداری كه در صورت درست بودن عبارت باید وارد شود را مشخص كنید  

 

 در كادر آرگومان دوم كلیك كنید و مقداری كه می خواهید در صورت درست بودن عبارت ظاهر شود را تایپ كنید. من می خواهم كه اگر میزان فروش بیشتر از یا مساوی میزان هدف باشد كلمه Over نوشته شود. توجه داشته باشید كه متن باید در علامت كوتیشن قرار گیرد: ًOverً .

 

 مقداری كه در صورت نادرست بودن عبارت باید وارد شود را مشخص كنید  

 

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

 

 فرمول را به پایان برسانید  

 

 روی Ok كلیك كنید تا كادر محاوره ای Function Arguments بسته شود و فرمول تكمیل شود. نتیجه فرمول در برگه كاری ظاهر می شود و خود فرمول (تابع) در نوار فرمول نوشته می‌شود.

*توجه  *

تست منطقی

تست منطقی مورد استفاده، توسط توسط تابع IF، مقایسه ای بین دو مقدار است. می توانید دو سلول را با هم مقایسه كنید یا یك سلول را با یك مقدار یا نتیجه فرمول مقاسیه كنید. برای مقایسه ها می توانید از عملگرها مثلاً > (بزرگتر از)، < (كوچكتر از)، = (مساوی با)، < > (نامساوی)، > = (بزرگتر یا مساوی) و < = (كوچكتر یا مساوی) استفاده كنید.

عدم استفاده از كادر های محاور های

اگر با یك تابع آشنا هستید، لازم نیست كه از كادرهای محاوره‌ای نشان داده شده در اینجا استفاده كنید. و می توانید از تابع به همان راحتی فرمول، استفاده كنید. یك Screen Tip ، پایین سلول نتیجه ظاهر می شود تا آرگومان هایی كه ممكن است فراموش كرده باشید را به شما یادآوری كند.

 

نکاتی ارزنده پیرامون کاربرد تابع CONCATENATE و جداسازی یک ستون در اکسل

کاربرد تابع CONCATENATE

فرض کنید یک فایل اکسل دارید که در  ستون A نام و در  ستون B نام خانوادگی رانوشته اید و واکنون میخواهیدکه در ستون C نام و نام خانوادگی را با هم داشته باشید به صورتی که بین نام و نام خانوداگی هم فاصله وجود داشته باشد.                                                                                                       در ادامه باما   همراه باشید                     

در ابتدا فرمول زیر را در  سلول C1 وارد مکنیم :

=A1&” “&B1

این فرمول به راحتی برای ما مقادیر موجود توی سلول A1 و B1 را با در نظر گرفتن یک فاصله بین اونها به ما نمایش میده . ولی باید بدونید که برای این کار یک تابع مخصوص وجود دارد به نام CONCATENATE

کاربرد تابع  CONCATENATE در اکسل

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

جدا کردن نوشته های یک ستون و تقسیم آن به دو ستون در excel

فرض کنید در سلولی  ستون A1:A10 تعداد ۱۰ تا نام نام خانوادگی وارد کردید که نام با فاصله از نام خانوادگی جدا شده اند ولی همراه سلول قرار دارند و هدف ما این است که  کاری کنیم که نامها در ستون B1:B10 قرار بگیرn و نام خانوادگی در ستون C1:C10 نوشته بشه برای این کار اکسل یک قابلیتی در نظر گرفته به نام Text To Columns که متن  را از ستونها جدا میکنه .

برای استفاده از این قابلیت باید به تب Data برید و گزینه Text To Columns رو انتخاب کنید  گزینه Next رو بزنید .

 

در قسمت بعدی بالا سمت چپ از شما میخواهد که جدا کننده متن راانتخاب کنید که باید Space را انتخاب کنیدو Next رو بزنید.در پنجره بعدی از شما میخواهد که فرمت جدید و محل و دیتای جدا شده را مشخص کنید کهدر این مثال ما فرمت General و سلول B1 را انتخاب می کنیم که اکسل به صورت خودکار نامها را در  ستون B و نام خانوادگی ها را در ستون C قرار میده .

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

اصطلاحات کاربردی نمودار ها دئر اکسل

اصطلاحات پر کاربرد نمودارها در اکسل   :
1-         Gird Line : خطوطی هستند که در امتداد مقادیر محور X , Y ظاهر میشوند. این خطوط در تعیین مقدار دقیق یک نقطه به ما کمک میکنند.
2-         Tick : خطوط کوتاهی هستند که با محور متقاطع بوده و قسمتهایی از یک مقیاس سری با گروه را جدا میکنند.
3-         Chart Title : متن بکار برده شده برای شناسایی عنوان نمودار میباشد.
4-         Axis : به محور‌ها در نمودار‌ها گفته میشود.
5-         Legend : هر نمودار دارای راهنمایی است كه نشان دهنده این است كه هر رنگ مربوط به كدام سری است.
 
روشهای ایجاد نمودار  در  excel:
روش اول :
1-                انتخاب منوی Insert
2-                انتخاب گزینه Chart
روش دوم :
1-    انتخاب آیکون Chart Wizard
نکته: در زمان ایجاد نمودار با کلیک بر روی دکمه Next به صفحات بعدی میرویم و کلیک بر روی دکمه Back باعث برگشت به صفحه قبلی میشود و با کلیک بر روی Finish کادر نمودار‌ها بسته میشود. نکته میتوانیم اطلاعاتی را كه میخواهیم بر اساس آن نمودار رسم كنیم ابتدا انتخاب كنیم بعد به یكی از دو روش بالا عمل كنیم. میتوانیم هم در حین كار انتخاب را انجام دهیم.
با انجام یکی از دو راه بالا پنجرهای زیر به ترتیب باز میشود :
ü          پنجره Chart Type : این پنجره شامل دو Tab زیر میباشد :
1)         Standard Type : که شامل قسمتهای زیر است :
الف)  Chart Type : که در این قسمت نوع اصلی نمودار را انتخاب میکنیم.
ب)   Chart Sub-Type : در این قسمت نوع فرعی نمودار را مشخص میکنیم.
ج)    Press and Hold to View Sample : در این قسمت پیش نمایشی از نمودار با اطلاعاتی که خودمان داده ایم نشان میدهد. برای این کار کافی است روی این دکمه Click کرده و نگه داریم.
2)         Custom Type : در این قسمت میتوان از نمودارهای سفارشی استفاده کرد یا یك نوع نمودار ایجاد كرد.
 
ü          پنجره Chart Source Data : این پنجره شامل دو Tab زیر میباشد :
1)         Data Range : که شامل قسمتهای زیر است :
الف) Data Range : در این قسمت آدرس داده هایی را که قرار است نمودار برای آنها کشیده شود، میتوان تغییر داد همچنین میتوانیم آدرس را با كلیك بر روی Collapse Dialog و با Drag كردن وارد كنیم. و یا میتوانیم با استفاده از فرمت زیر آدرس را تایپ كنیم. شماره سطر پایان $ نام ستون پایان$ شماره سطر شروع$ نام ستون شروع$! نام Sheet =
ب) Series in : این قسمت جهت نمایش داده‌ها را مشخص میکند. یعنی کدام یک از این دو برچسب از داده‌ها در پایین نمودار قرار خواهد گرفت. اگر Row را انتخاب كنیم سطر‌ها به عنوان سری و اگر Column را انتخاب كنیم ستونها به عنوان سری قرار میگیرند.
2)         Series : که شامل قسمتهای زیر است :
الف) Series : در این قسمت نام سری‌های موجود نوشته شده است.
ب)   Name : توسط این قسمت میتوانیم نام سری را عوض کنیم.
ج)    Value : در این قسمت میتوانیم آدرس مقادیر هر سری را مشخص كنیم. همچنین میتوانیم مقادیر را تایپ كنیم. برای تایپ مقادیر باید مقدار سری را در علامت { } وارد كنیم.
د)    Category(x) Axis labels : در این قسمت میتوان آدرس خانه هایی را داد که محتوای آنها بر روی محور X نوشته میشوند.
ه)     دکمه Add : یک سری جدید ایجاد میکند.
و)     دکمه Remove : بر روی نام هر سری در قسمت Series کلیک کنیم و سپس این دکمه را بزنیم ، سری حذف میشود.
ü          پنجره Chart Option : این پنجره شامل 6 Tab زیر است :
1)             Titles : این قسمت شامل موارد زیر است :
الف) Chart Title : در این قسمت عنوان نمودار را مینویسیم.
ب)   Category(x) Axis labels : در این قسمت عنوان محور X را مینویسیم.
ج)    Value (Y) Axis : در این قسمت عنوان محور Y را مینویسیم.
2)             Axes : این قسمت شامل گزینه‌های زیر است :
الف)  Category(x) Axis اگر در کنار این کادر تیک خورده باشد ، مقادیر روی محور X نشان داده میشوند و اگر تیک نخورده باشد نشان داده نمیشوند.
ب)   Value (Y) Axis اگر در کنار این کادر تیک خورده باشد، مقادیر روی محور Y نشان داده میشوند در غیر این صورت نمایش داده نمیشوند.
3)             Grid Line : که این Tab شامل قسمتهای زیر است :
الف)  Category(x) Axis که خود شامل دو قسمت است :
×      Major Gridline : اگر این قیمت تیک خورده باشد ، خطوط شبکه رسم میشوند ( تمامی خطوط رسم نمیشوند)
×      Minor Gridline : اگر این قسمت تیک خورده باشد خطوط دیگری بین خطوط بالا رسم میشوند. این دو قسمت برای خطوط شبکه موازی محور Y‌ها هستند.
ب)   Value (Y) Axis : که شامل دو قسمت زیر است :
×      - Major Gridline : خطوط شبکه را به موازات محور X رسم میکند.
×      - Minor Gridline : خطوط دیگری را به موازات محور X بین خطوط بالا رسم میکند.
4)             Legend : این Tab شامل قسمتهای زیر است :
الف)  Show Legend : اگر این گزینه تیک خورده باشد راهنما نمایش داده میشود.
ب)   Placement : مکان راهنما را نشان میدهد. Bottom : پایین ، Corner : گوشه بالا سمت راست، Top : بالا، Right : سمت راست، Left : سمت چپ
5)             Data Label : شامل قسمتهای زیر است :
الف) None : هیچ مقداری را بر روی ستونها نمایش نمیدهد.
ب)   Show Value : مقدار هر سری رابر روی ستون آن نشان میدهد.
ج)    Show Label : برچسب هر سری را روی آن نشان میدهد.
د)    Legend Key Next to Label : اگر یکی از حالتهای ب و ج را انتخاب میکنیم. این گزینه فعال شده و در کنار هر مقدار رنگ سری نمایش داده میدهد.
6)              Data Table : شامل قسمتهای زیر است :
الف)  Show Data Table : اگر این گزینه فعال شود ، جدولی مشابه آنچه که ما ، در Sheet کشیده ایم در زیر نمودار ظاهر میشود.
ب)   Show Legend Keys : اگر گزینه الف فعال باشد ، این گزینه نیز فعال میشود. و اگر در کادر آن تیک بزنیم، رنگ هر سری را در کنار اطلاعات آن سری در جدول نشان میدهد.
نکته:   باید توجه داشته باشیم كه كه با توجه به نوع نمودار گزینه‌های موجود در این پنجره میتوانند متفاوت باشند.
ü           پنجره Chart Location :
الف)  As New Sheet : این گزینه نمودار را در یک Sheet جدید با اسم دلخواه ( اسم پیش فرض Chart 1 است) ایجاد میکند. نموداری که در این حالت ایجاد میشود، قابل جابجا شدن و تغییر سایز نیست ولی هر گونه تغییرات بر روی اطلاعات اصلی بر روی نمودار تاثیر دارد.
ب)   As Object in : این گزینه نمودار را در هر Sheet که ما انتخاب میکنیم ، ( از Sheet‌های موجود ) رسم میکند و قابل جابجا کردن و تغییر سایز نیز میباشد.
 
تغییر بر روی نمودار ایجاد شده :
برای تغییر نمودار ایجاد شده باید ابتدا نمودار را انتخاب کرد. سپس مجدداً دکمه Chart Wizard راکلیک کرد. یا از روی Toolbar ، Chart که باز میشود، تغییرات را اعمال کرد.
 
نوار ابزار Chart در اکسل :
این نوار ابزار را میتوانیم در صورتی که فعال نبود با Right Click بر روی نوار ابزار‌ها و انتخاب گزینه Chart فعال کنیم این نوار ابزار دارای آیکونهای زیر است :
×      Chart Object : در این قسمت میتوانیم قسمتی از نمودار را كه میخواهیم بر روی آن تغییر دهیم انتخاب كنیم. با انتخاب هر گزینه قسمت مربوط به آن روی نمودار انتخاب میشود.
×      Format : با توجه به اینكه در Chart Object چه انتخاب شده باشد این آیكون پنجره Properties آن را باز میكند.
نکته: با Double كلیك كردن بر روی هر موضوع از نمودار نیز پنجره Properties آن باز میشود.
×      Chart Type : در این قسمت میتوانیم نوع نمودار انتخاب شده را تغییر دهیم.
×      legend : توسط این آیکون کادر راهنما را ظاهر یا پنهان میکنیم.
×      Data Table : توسط این آیکون میتوانیم جداول داده‌ها را فعال یا غیر فعال کنیم.
×      By Rows :با انتخاب این آیكون سطر‌ها به عنوان سری انتخاب میشوند.
×      By Column : با انتخاب این آیكون ستونها به عنوان سری انتخاب میشوند.
×      Angle Text up : جهت متن نمودار را تغییر میدهد. یعنی متن از بالا به پایین ، کج نوشته میشود. به شرطی این آیكون فعال است كه قسمتهای متنی نمودار انتخاب شده باشد.
×      Angle Text down : متن نمودار را تغییر جهت میدهد. یعنی متن از پایین به بالا و کج نوشته میشود. به شرطی این آیكون فعال است كه قسمتهای متنی نمودار انتخاب شده باشد.
تغییر اندازه :
1)         روی نمودار کلیک میکنیم تا انتخاب شود.
2)         روی یکی از مربع‌های سیاه اطراف نمودار کلیک میکنیم.
3)         مربع را تا زمانی که به اندازه دلخواه تغییر اندازه دهد Drag میکنیم.

نکاتی پپیرامون تبدیل اعداد منفی به صفر با استفاده از تابع MAX در اکسل

بر ای تبدیل اعداد منفی به صفر بدون آنکه تأثیری روی اعداد مثبت داشته باشد می توانیم از تابع MAX استفاده کنیم

 

برای مثال

=MAX(C3-B3,0)

مزیت فرمول MAX در آن است که با داده های کوچک (حتی دو مقدار) به خوبی کار می کند

این فرمول چگونه کار می کند؟

تابع MAX دو یا چند مقدار را با هم مقایسه کرده و بیشترین آنها را نمایش می دهد.

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

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

زمانی که بخواهیم بزرگترین و کوچکترین مقادیر را محاسبه کنیم می توانیم به جای تابع IF از توابع MAX و MIN در execl استفاده کنیم.

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

همانطور که میدانید بحث امنیت در دنیای فن آوری اطلاعات از مباحث بسیار مهم است

امنیت اکسل

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

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

روی شیت مد نظرتون راست کلیک میکنید و از گزینه های موجود گزینه Protect Sheet رو انتخاب میکنید.

 

امنیت در کسل

توی قسمت بعد کافیه که برای فایلتون یه پسورد انتخاب کنید و دوباره اون رو وارد کنید .

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

خب حالا اگه کدنویسی داشتیم برای فایلمون چی ؟ با زدن کلیدهای Alt F11 به کدنویسی دست پیدا خواهند کرد .

قفل کردن قسمت کد نویسی:

با زدن کلیدهای Alt F11 وارد قسمت کدنویسی میشید و در اون قسمت به منوی Tools میرید.

از منوی Tools گزینه VBA Project Properties رو انتخاب می کنید .

پنجره ای مثل شکل زیر باز میشه که باید به تب Protection برید و رمز خودتون رو دوبار وارد کنید .

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

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

به محیط کدنویسی میرید با زدن Alt F11

به منوی Tools رفته و گزینه Vba project properties رو انتخاب می کنید.

و پسوردی رو گذاشتید رو بردارید.

 

 

نکاتی پیرامون فیلتر کردن اطلاعات در اکسل با استفاده از دستور Contains

 

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

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

 

2. سپس گزینه Text Filters > Contains  در اکسل را انتخاب کنید .

3. یک پنجره همانند تصویر زیر نمایش داده می شود . 

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

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

 

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

 

 

نکاتی پیرامون وارد کردن و رسم نمودار در اکسل

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

یکی از نرم افزارها و برنامه‌های خوب برای ایجاد و رسم نمودار، نرم افزار مایکروسافت اکسل (Microsoft Excel) می‌باشد ولی شاید شما هم جزو افرادی باشید که نحوه‌ی کار و رسم نمودار در اکسل را ندانید و نیاز داشته باشید تا آنرا یاد بگیرید.

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

 

مراحل گام به گام رسم نمودار در اکسل

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

گام اول: وارد کردن اطلاعات و داده‌ها

در گام نخست باید اطلاعات و داده‌های مورد نظرتان را در برنامه اکسل وارد نمایید و یا درون ریزی (Import) کنید. بهتر است اولین ستون عمودی و اولین ردیف افقی را به عنوان سرتیتر نگه‌دارید.

 

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

گام دوم: انتخاب کردن اطلاعات مورد نظر در EXCEL

برای اینکه بتوانید اطلاعات جدول را به صورت نموداری نمایش دهید، باید ابتدا آنها را انتخاب کنید. برای انتخاب کردن سلول‌ها، ماوس را بر روی سلول A1 گذاشته و کلیک کنید و بدون رها کردن کلیک، ماوس را تا سلول D7 بکشید. به این ترتیب، رنگ ناحیه‌ی انتخابی تغییر می‌کند.

 

گام سوم: انتخاب نوع نمودار و وارد کردن آن در اکسل

پس از انتخاب کردن داده‌های مورد نظر، از منوی اکسل وارد بخش Insert شوید و جعبه‌ی Charts را پیدا کنید.

 

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

 

همانطور که مشاهده می‌کنید، اطلاعاتی که به عنوان سرتیتر در ستون A و همچنین ردیف ۱ وارد شده‌اند، به ترتیب در محور افقی و راهنمای نمودار (Legend) مشخص شده اند.

 

همچنین می‌توانید اطلاعات و نوع نمودار را ویرایش نمایید و تغییراتی را به دلخواه در آن اعمال کنید

نکاتی پیرامن استفاده AutoFill در اکسل

 قابلیت AutoFill با پر كردن خودكار مدخل های تكراری یا یك سری داده (مانند Apri ،May ، June) می تواند ورود داده ها را به میزان چشمگیری سرعت بخشد. با AutoFill به سرعت می توانید لیستی از اسم روزها یا ماه ها، یك سری اعداد یا یك لیست از متن های مشابه ایجاد كنید. اسم روزها و ماه ها و مخفف های سه حرفی آنها در Excel وجود دارند. می‌توانید لیست های سفارشی(مثلاً از اسم افراد یا محصولات) ایجاد كنید و AutoFill آنها را پر كند.

 

 یك لیست از ماه‌ها ایجاد كنید  

 

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

 

 دسته پر كردن را بكشید  

 

اشاره گر ماوس را روی دسته پر كردن (مربع سیاه در گوشه سمت راست .و پایین سلول) حركت دهید تا به شكل علامت عدسی میكروسكوپ در آید. در حالی كه در ردیف ها یا ستون ها می كشید دكمه سمت چپ ماوس را نگه دارید. (می توانید سلول ها را در هر جهتی پر كنید). یك Screentip به شما نشان می دهد كه چه چیزی در هر سلولی كه می كشید پر می شود تا بفهمید كه چه زمانی به حد كافی، ماوس را كشیده اید.

 

  AutoFill Options را انتخاب كنید  

 

دكمه ماوس را در انتهای سطر یا ستونی كه می خواهید پر كنید رها كنید. سری مورد نظر به ترتیب لازم در سلول ها درج می شود. دكمه Auto Fill Options ظاهر می شود. روی فلش آن كلیك كنید تا لیستی از گزینه ها باز شود. از این لیست برای انتخاب چگونگی پُر شدن سلول ها استفاده كنید. برای مثال، می توانید به Excel دستور دهید كه بدون كپی كردن فرمت، یك سری ایجاد كنید. برای این كار، گزینه Fill Without Formatting را انتخاب كنید.

 

 یك سری متنی ایجاد كنید  

 

برای ایجاد یك سری متنی مانند Division 1 ، Division 2 وـــ ، داخل یك سلول كلیك كنید و اولین مورد در این سری را تایپ كنید. سلول را انتخاب كنید و دسته پر كردن را در جهتی كه می خواهید بكشید.

 

 دسته را رها كنید  

 

دسته را رها كنید تا سری متنی در سلول‌های انتخاب شده پر شود. اگر لازم باشد، روی فلش دكمه AutoFill Options كلیك كنید و یكی از گزینه ها مانند Copy Cells را انتخاب كنید تا داده ها را بدون ایجاد سری، كپی كند.

 

 یك سری عددی ایجاد كنید  

 

برای ایجاد یك سری عددی، دو عدد اول را در سلول های مجاور تایپ كنید، این دو سلول را انتخاب كنید و دسته را برای پر كردن سری، بكشید. AutoFill بصورت خودكار، بقیه سری را بر اساس دو عدد اول پر می كند. برای مثال، اگر در سلول اول تایپ كنید5 و در سلول دوم تایپ كنید. 15، یك سری ایجاد خواهید كرد كه در هر سلول، 5 عدد به عدد قبلی اضافه می شود.

 

*توجه *

 كپی كردن داده ها

برای پر كردن یك لیست با یك مدخل كپی شده، آن مدخل را تایپ كنید، آن را انتخاب كنید و دسته را برای كپی كردن آن مدخل بكشید. اگر Excel یك سری ایجاد كند، روی دكمه AutoFill Options كلیك كنید و Copy Cells را انتخاب كنید.

كپی كردن یك فرمول با استفاده از  AutoFill در excel

از AutoFill می توان برای كپی كردن یك فرمول در یك جدول استفاده كرد. آدرس سلول ها در فرمول طوری تنظیم می شوند كه فرمول، بدرستی سلول ها را محاسبه كند
ایجاد یك لیست سفارشی در اکسل

برای ایجاد یك لیست سفارشی، كل لیست را در یك برگه كاری تایپ كنید. این لیست را انتخاب كنید و از منوی Tools ، گزینه Options را انتخاب كنید. در برگه Custom Lists ، گزینه Import را انتخاب كنید و روی Ok كلیك كنید. لیست در پنجره Custom Lists ذخیره می شود. حال با تایپ كردن یكی از مدخل های لیست و كشیدن دسته پر كردن، می‌توانید این لیست را در یك صفحه كاری پر كنید.                                                         امید واریم از آموزش اکسل پیشرفته امروز نیز لذت برده باشید.                                                              موفقیت هایتان مستدام  

پنج ترفندکابردی اکسل

ترفند شماره ۱  : جمع زدن سریع با کلید ALT  و =در excel

کافی است که در انتهای یک سطر یا ستونی سلولی را انتخاب کنید و این کلید = +  ALT را بزنید تا تابع SUM برای شما نوشته شود.

 

ترفند شماره ۲ : کلیدهای تنظیم فرمت اعداد در اکسل

جالب است بدانید که کلیدهای CTRL + SHIFT و اعداد ۱ تا ۵ برای فرمت سل اعداد بکار می‌روند. خود من بیشترین استفاده را از کلید CTRL+SHIFT+1 می‌کنم که این کلید باعث نمایش یک عدد در حالت Number می‌شود.

 

ترفند شماره ۳: نمایش همه فرمول‌هادر EXCEL

با زدن کلید    `  +  CTRL حالت شیت اکسل عوض می‌شود و تمامی فرمول ها را به شما نماش می‌دهد. فراموش نکنید که با دوباره زدن همین کلید مجدد به حالت عادی شیت بر خواهید گشت.

نکته: علامت    `    را در سمت چپ عدد ۱ کیبورد کامیپوتر باید پیدا کنید. روی این کلید علامت ~ هم می بینید.)

 

ترفند شماره ۴ : پرش به ابتدای یک سطر یا ستون از لیست با کیبورد در اکسل

اگر فایل شما پر از اطلاعات باشد و مرتبا بخواهید به انتهای یا ابتدای لیست خود بروید شک نکنید که باید از کلیدهای ↓  + CTRL  برای رفتن به انتهای ستون (سطر) و کلید ↑  + CTRL برای رفتن به ابتدای لیست هایتان استفاده کنید.

 

ترفند شماره ۵ : کپی کردن سریع فرمول در یک ستون از لیست

شک نکنید که کپی کردن و یا درگ کردن زیادی کاری سخت برای من حساب می‌شود. اگر یک لیست دارید و یک فرمول هم در آن لیست استفاده شده است که باید در سایر سطرها کپی شود، خوب کافیست که Double-Click کنید روی اون نقطه کوچیک!! (به نقطه مربع شکل کوچکی که هنگام انتخاب یک سلول می بینید اصطلاحا Fill Handle می گویند)

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

 

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

در برنامه Word هنگام استفاده از اعداد (یا علائم دیگر) در شروع خط و زدن کلید Enter شماره خط به صورت اتوماتیک اضافه می شود و اگر یکی از این خطوط شماره دار را خذف کنید، شماره بقیه خطوط متناسب با تعداد شماره ها اصلاح می شوند.این اعداد Auto Number هستند.

تکنیک اول:

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

برای این منظور ابتدا جدول مورد نظر را ایجاد می کنیم.

برای ایجاد شماره ردیف خودکار از فرمول ("",=IF(B3<>””,MAX($A$1:A2)+1= استفاده می کنیم. قسمت MAX($A$1:A2)+1 برای تولید اعداد اتوماتیک استفاده میشود. در اینجا ما قصد داریم چنانچه در ستون شرح یعنی سلول B3 مقداری را وارد کردیم آنگاه شماره ردیف ایجاد گردد و اگر ستون شرح خالی بماند شماره ردیف نیز خالی باشد. پس ما در اینجا فقط شرح اقلام را نوشته و مشاهده میگردد که به محض وارد کردن شرح، شماره ردیف نیز ایجاد میگردد.

توجه داشته باشید در فرمول ذکر شده سلول A1 به صورت مطلق اما سلول A2 به صورت نسبی تعریف شده است.                                                                                                                         خطوط جدول (Border) در اکسل

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

برای این منظور از سربرگ Conditional Formatting استفاده می کنیم. ابتدا تب Conditional Formatting را باز کرده و بر روی New Rule…  کلیک می کنیم. پنجره New Formatting Rule ظاهر میشود.

از قسمت   Select a rule type

عبارت

  Use a formula to determine which cells to format

را انتخاب کرده و در کادر پایین Format values where this formula is true شرط مورد نظر را به این صورت تایپ کرده""<>$A3=  (یعنی اگر سلول A3 خالی نباشد) و بر روی دکمه Format کلیک می کنیم. پنجره format cell در اکسل باز می شود. از تب Border دکمه Outline را انتخاب کرده و بر روی Ok کلیک می کنیم.

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

مطالبی پیرامون جستجوی بانک‌های اطلاعاتی با استفاده از توابع اکسل

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

 

۱- تابع  LOOKUP  در اکسل

این تابع دو فرم آرایه‌ای و برداری (Vector) دارد که فرم برداری آن مدنظر ما است. در اکسل به یک محدوده از سلول‌ها که تنها یک سطر یا یک ستون داشته باشد، Vector می‌گویند مثلاً محدوده‌های A1:A88 یا A1:M1 هر دو Vector هستند.

تابع LOOKUP، یک عبارت را در یک Vector جستجو می‌کند و محتوای سلول هم موقعیت با سلول پیدا شده در Vector دیگر را به عنوان خروجی به کاربر می‌دهد.

ساختار تابع LOOKUP به شکل زیر می‌باشد:

=LOOKUP (lookup_value, lookup_vector, result_vector)

آرگومان اول: وارد کردن این آرگومان اجباری است چون بیانگر عبارت مورد نظر برای جستجو می‌باشد. این آرگومان می‌تواند عدد، رشته متنی، Logical Values (شامل صفر و یک یا True و False) یا آدرس یک سلول حاوی عبارت مورد نظر باشد.
آرگومان دوم: این آرگومان بیانگر Vector محل جستجو می‌باشد که وارد کردن آن نیز اجباری است. این آرگومان یک محدوده از اکسل شامل یک سطر یا یک ستون می‌باشد که قرار است آرگومان اول در آن جستجو شود. سلول‌های محدوده‌ی Vector هم می‌توانند حاوی اعداد یا رشته‌های متنی یا Logical Values (شامل صفر و یک یا True و False) باشند.

آرگومان سوم: یک Vector مانند آرگومان دوم و به همان اندازه می‌باشد، مثلاً اگر آرگومان دوم یک بردار افقی با ۱۰ سلول باشد، آرگومان سوم هم باید یک بردار افقی با ۱۰ سلول باشد. در واقع پس از یافتن عبارت مورد جستجو در lookup_vector، تابع LOOKUP محتوای سلول هم تراز با سلول حاوی عبارت جستجو در result_vector را به عنوان خروجی نمایش می‌دهد.

اگر تابع LOOKUP نتواند عبارت آرگومان اول را در vector مورد جستجو پیدا کند، آخرین (بزرگترین) مقدار در lookup_vector را که برابر یا کوچکتر از عبارت مورد جستجو می‌باشد را به عنوان نتیجه‌ی جستجو می‌پذیرد. بنابراین برای اینکه خروجی تابع، صحیح باشد، Vector باید به صورت صعودی مرتب شده باشد. در غیر اینصورت ممکن است تابع LOOKUP جواب صحیح را به ما ندهد. پس به صعودی بودن lookup_vector دقت کنید.

اگر عبارت مورد جستجو، کوچکتر از کوچکترین عضو lookup_vector باشد در خروجی تابع خطای N/A# ظاهر می‌شود.

به مثال زیر دقت کنید:

 

دقت داشته باشید که لزومی ندارد دو محدوده lookup_vector و  result_vector مانند مثال بالا، حتماً مجاور هم باشند، بلکه صرفاً هم اندازه بودن این دو محدوده (Vector) کافیست.

---

۲- تابع VLOOKUP:

تابع VLOOKUP یا Vertical LOOKUP (جستجوی عمودی) در excel مانند تابع LOOKUP عمل می‌کند. درواقع اگر با تابع LOOKUP آشنا باشید درک VLOOKUP برای شما آسان‌تر خواهد بود، لذا توصیه می‌شود قبل از مشاهده توضیحات تابع VLOOKUP، بخش تابع LOOKUP که در بالا به آن اشاره شده است را  مطالعه کنید.

تابع VLOOKUP می‌تواند یک عبارت را در اولین ستون یک محدوده جستجو کند و در هر یک از ستون‌های موجود در محدوده‌ مورد جستجو، محتوای سلول هم تراز (هم ردیف) با سلول پیدا شده را به عنوان خروجی اعلام نماید. در واقع کلمه Vertical در نام این تابع به جستجو در ستون اشاره می‌کند.

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

 

ساختار این تابع به صورت زیر است:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

تابع VLOOKUP چهار آرگومان دارد، سه آرگومان اول اجباری و آرگومان آخر اختیاری می‌باشد.

آرگومان اول: این آرگومان عبارتی است که کاربر می‌خواهد جستجو کند، در مثال بالا این آرگومان شماره پرسنلی شخص می‌باشد، این آرگومان همانند آنچه در تابع LOOKUP وجود داشت، می‌تواند عدد، رشته متنی، آدرس سلول و یا یک مقدار منطقی (Logical Value) باشد.

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

آرگومان دوم: این آرگومان یک محدوده از اکسل می‌باشد، تمام جدول داده‌ها به عنوان این آرگومان به تابع معرفی می‌گردد، در مثال بالا محدوده‌ی A2:C10 نشانگر آرگومان دوم می‌باشد، همینطور می‌توان نام محدوده را به عنوان آرگومان دوم درج کرد

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

آرگومان سوم: این آرگومانیک عدد می‌باشد و شماره ستون داده‌ی مورد نظر برای استخراج از جدول است، ستون شماره ۱ همان ستون یا Vector جستجو شده و ستون شماره ۲ ستون مجاور می‌باشد و به همین ترتیب. در مثال بالا، این آرگومان عدد ۳ می‌باشد، زیرا ستون حاوی نام شخص ستون سوم از جدول است.

نکته ۲) اگر آرگومان سوم تابع VLOOKUP کمتر از یک باشد خروجی تابع خطای !VALUE# و اگر این عدد بزرگتر از تعداد کل ستون‌ها باشد خروجی تابع خطای !REF# خواهد بود.

آرگومان چهارم: اگرچه وارد کردن این آرگومان، اختیاری است اما بسیار مهم می‌باشد. این آرگومان می‌تواند True یا False باشد.

اگر این آرگومان True باشد یا نادیده گرفته شود، در اینصورت تابع VLOOKUP رفتار زیر را انجام می‌دهد:

• اولاً، داده‌های محدوده مورد جستجو (ستون اول) باید همانند آنچه در تابع LOOKUP وجود داشت به صورت صعودی مرتب شده باشند تا مطمئن باشیم که خروجی تابع قابل اعتماد است.
• دوماً، در صورت نیافتن عبارت مورد جستجو در ستون اول، دقیقاً مشابه تابع LOOKUP، تابع VLOOKUP نیز بزرگترین مقدار کوچکتر از عبارت مورد جستجو را به عنوان پاسخ می‌پذیرد.
• سوماً، مجدداً مشابه تابع LOOKUP، اگر تابع VLOOKUP نتواند عبارت مورد جستجو را در ستون اول پیدا کند، آخرین (بزرگترین) مقدار در آن Vector که برابر یا کوچکتر از عبارت مورد جستجو می‌باشد را به عنوان نتیجه‌ی جستجو می‌پذیرد (به صعودی بودن داده‌ها دقت کنید).

حال اگر آرگومان چهارم False باشد، False برای تابع به معنی Exact Match است یعنی در این حالت تابع تنها داده‌ای را به عنوان پاسخ می‌پذیرد که دقیقاً مانند عبارت مورد جستجو باشد و اگر آن را پیدا نکرد خروجی تابع برابر خطای N/A# خواهد بود.

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

آرگومان چهارم هرچه که باشد (True یا False)، مانند تابع LOOKUP، اگر عبارت مورد جستجو، کوچکتر از کوچکترین عضو ستون مورد جستجو (Vector) باشد خروجی تابع خطای N/A# است.

---

۳- تابع HLOOKUP:

تابع HLOOKUP یا Horizontal LOOKUPn (جستجوی افقی) در اکسل ، در ساختار و طریقه عملکرد هیچ تفاوتی با تابع VLOOKUP ندارد، تنها تفاوت این دو تابع در افقی و عمودی بودن داده‌ها است، تابع HLOOKUP برای جدول‌های افقی کاربرد دارد و سطر اول داده‌ها را جستجو می‌کند. با یادگیری و فهم توابع LOOKUP و VLOOKUP مشکلی در کار با تابع HLOOKUP نخواهید داشت.