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

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

 

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

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

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

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

 

توابع IS و توابع ترکیبی IF در اشکال زدایی فرمول هادر excel

 توابع IS در اکسل شامل توابع ISBLANK، ISERR، ISERROR، ISLOGICAL، ISNA، ISNONTEXT، ISNUMBER، ISREF و ISTEXT می باشند. تمامی این توابع ساختار یکسانی دارند و خروجی آنها Boolean می باشد، توابع IS در اکسل تنها یک ورودی می گیرند و آن را برای شرط خاصی بررسی می کنند و خروجی تمام این توابع تنها TRUE یا FALSE می باشد.

توصیه می گردد حتماً مقاله ی مربوط به معرفی توابع IS را مطالعه نمایید.

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

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

با استفاده از تابع TYPE می توانیم نوع داده های وارد شده به سلول و یا خروجی فرمول ها را بررسی نماییم و در صورت مغایرت با نوع مورد نظر خود اقدام مناسب را صورت دهیم.

فرض کنید قرار است کاربر در سلول A1 تعداد کالای مورد درخواست را وارد کند و این عدد در فرمول های دیگر برای محاسبات پارامترهای مورد نیاز استفاده شود، حال اگر کاربر A1 را خالی بگذارد و یا سهواً داده ای غیر عددی در آن وارد نماید مسلماً فرمول های ما با خطا مواجه خواهند شد، برای این کار می توانیم در سلول A2 فرمول مشابه فرمول زیر بنویسیم:

=IF(OR(ISBLANK(A1);TYPE(A1)1);”Wrong Data, Number Only please”; “Let’s Do Next Step”)

در ساختار فرمول بالا از تابع OR (توابع منطقی در اکسل) و توابع TYPE و ISBLANK استفاده شده، به این ترتیب در صورتی که سلول A1 خالی باشد و یا داده ای غیر عددی در آن وارد گردد پیامی مبنی بر خطا بودن نوع داده ی ورودی به کاربر نشان داده می شود. اگر قرار بود عدد وارد شده در A1 به عنوان ورودی تابع POWER (توابع ریاضی) باشد، مسلماً غیر عددی بودن A1 باعث بروز خطا در فرمول می گردید، در چنین موردی ساختاری مانند ساختار زیر را می توان استفاده کرد:

=IF(OR(ISBLANK(A1);TYPE(A1)1);”Wrong Data in A1″;POWER(A1;2))

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

همانطور که از مثال های بالا متوجه شدیم از ترکیب تابع IF و توابع IS و دیگر توابع اطلاعات در اکسل می توان ساختارهای بسیار مفید در جلوگیری از ورود خطاها و داده های اشتباه در فرمول ها تشکیل داد. در این میان دو تابع ISERR و ISERROR از اهمیت ویژه ای برخوردارند، زیرا این دو تابع در نهایت می توانند از نشان داده شدن پیام های خطا در فایل اکسل شما جلوگیری کنند و به عبارتی خطاها را به دام اندازند. ساختارهای زیر کمک می کنند که با استفاده از این توابع و تابع IF از نشان داده شدن پیام خطا در فایل های اکسل خود جلوگیری نمایید:

=IF(ISERROR(A1);”This Formula Contains Error”; “Acceptable Outcome”)

در فرمول نویسی حرفه ای در اکسل  می توان به کاربر با توجه به نوع خطای اتفاق افتاده پیام متناسب داد، مثلاً در صورت بروز خطای N/A# در خروجی تابع VLOOKUPحتماً می توان این نتیجه را گرفت که عبارت مورد نظر در جدول مورد جستجو وجود نداشته، این پیام را می توان از ساختاری مشابه ساختار زیر به کاربر داد:

=IF(ERROR.TYPE(A1)=7;”The Required Item is not in Table”;””)

در فرمول بالا در صورت بروز خطای خطای N/A# پیامی برای کاربر نمایش داده می شود و او را در جریان قرار می دهد، حتی می توان ساختار زیر را مستقیماً در سلول A1 استفاده کرد:

=IF(ERROR.TYPE(VLOOKUP(“Excelpro”;A2:C13;2;FALSE))=7;”The Required Item is not in Table”;VLOOKUP(“Excelpro”;A2:C13;2;FALSE))

در ساختار بالا عبارت Excelpro در محدوده A2:C13 جستجو می شود و در صورت پیدا نشدن پیامی متناسب به کاربر داده می شود.

 

استفاده از گروه Formula Auditing در اشکال زدایی فرمول ها در  اکسل

گروه Formula Auditing در تب Formulas برای رصد کردن وضعیت فرمول ها در اکسل مورد استفاده قرار می گیرد،

شما احتمالا بارها با این خطا در Excel برخورد کرده‌اید اما این خطا را با این نام نمی‌شناسید

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

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

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

توضیح Circular Reference در Excel

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

 

Circular Reference در Excel چگونه ایجاد می‌شود

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

 

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

 

آوردن فلش آبی رنگ

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

 

خطای هنگام باز شدن این فایل

این فایل را ذخیره کنید و ببنید و سپس باز نمایید، مشاهده می‌کنید که در هر بار بازشدن فایل پیغام خطای Circular Reference نمایش داده می‌شود.

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

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

 

روش پیدا کردن همه Circular Referenceدر اکسل

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

 

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

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

 

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

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

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

برای فعال کردن وضعیت Iteration در Excel Options گروه Formula و سپس Enable iterative Calculation را در حالت فعال قرار می‌دهیم و تعداد دفعات اجرای را تعیین می‌کنیم.

 

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

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

تعیین ناحیه چاپ در EXCEL
گاهی اوقات در یک برگه اطلاعات زیادی وارد می شود که در مواردی مایل به چاپ همه آنها نیستیم. با استفاده از یک امکان اکسل، می توانیم ناحیه ای را به عنوان «ناحیه چاپ» مشخص کرده و فقط آن را چاپ کنیم. برای اینکار ابتدا ناحیه مورد نظر را از برگه انتخاب کرده و سپس از زبانه Page Layout و از بخش Page Setup روی دکمه Print Area کلیک کنید. سپس از دو گزینه موجود، گزینه Set Print Area را انتخاب کنید. بدین ترتیب ناحیه انتخابی چاپ مشخص می شود.

حذف ناحیه چاپ
برای حذف ناحیه چاپ شده نیز کافیست که مسیری که در بالا توضیح داده شد را طی کرده و از منوی Print Area گزینه Clear Print Area را انتخاب کنید.
جدا کردن صفحات با استفاده از امکان Page Break در ECXEL
همانطور که پیشتر اشاره شد، یک صفحه گسترده قادر به نگهداری حجم وسیعی از اطلاعات است که می تواند در قالب چندین برگه کاغذ چاپ شود. نرم افزار اکسل به صورت خودکار اطلاعات برگه ها را بر طبق ابعاد کاغذ تعیین شده و صفحه بندی می کند. بدین معنی که اگر بخشی از اطلاعات در کاغذ نخست جا نشود، در کاغذ دوم چاپ خواهد شد. مرز بین صفحات در اکسل، توسط امکان Page Break مشخص می شود. فرض کنید لیستی داریم که ممکن است کل اطلاعات آن به راحتی در یک صفحه چاپ شود اما می خواهیم آنها را در دو صفحه مجزا چاپ کنیم به طوری که در وضعیت نمایش آن تغییری حاصل نشود.
این کار را با مثالی توضیح می دهیم. فرض کنید لیست زیر را که در آن مشخصات گروهی از لپ تاپ ها درج شده، در دو صفحه چاپ کنی به طوری که از ستون Name تا ستون Hard در یک صفحه و ستون های دیگر در صفحه دوم چاپ شود.

 

برای اینکار نخست سلولی را که قرار است به عنوان اولین سلول صفحه درج شود را انتخاب می کنیم. در این مثال باید سلول «LCD» انتخاب شود. سپس از زبانه Page Layout و در بخش Page Setup روی دکمه Breaks کلیک کرده و از منوی باز شده، گزینه Insert Page Break را انتخاب می کنیم. به این ترتیب در این محل یک Page Break جدید ایجاد می شود.

 

برای حذف Page Break ایجاد شده، ابتدا سلول اول صفحه دوم را انتخاب می کنیم و سپس از منوی Breaks گزینه Remove Page Break را انتخاب می کنیم. گزینه Reset All Page Breaks کلیه Page Breakهای موجود را حذف می کند.

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

 

با استفاده از اکسل می توان اندازه این فضا را تعیین کرد. برای اینکار از زبانه Page Layout بخش Setup Page روی دکمه Margins کلیک کنید. این منو سه حاشیه از پیش تعیین شده را نشان می دهد. گزینه اول (Normal) بیانگر حاشیه عادی، گزینه دوم (Wide) بیانگر حاشیه پهن و گزینه سوم (Narrow) بیانگر حاشیه باریک است.
با انتخاب آخرین گزینه (Custom Margins…) نیز می توان حاشیه را به اندازه دلخواه تنظیم کرد.
یکی دیگر از تنظیمات صفحات در اکسل، تعیین جهت صفحه است. صفحات یک برگه هم به صورت عمودی و هم به صورت افقی قابل چاپ هستند. برای تعیین جهت صفحه، در زبانه Page Layout و از بخش Page Setup روی دکمه Orientation کلیک کنید. گزینه Portrait کاغذ را عمودی و گزینه Landscape آن را افقی می کند.

 

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

 

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

 

اکنون در کادر Rows to repeat at top کلیک کرده و سپس روی شماره سطر تیتر مورد نظر در برگه کلیک می کنیم. در این صورت آدرس سطر به طور خودکار در این کادر قرار می گیرد. بدین ترتیب یک سطر را به عنوان تیتر چاپ تعیین کرد‌ه ایم و هنگام چاپ برگه مشاهده می کنیم که این سطر در بالای لیست همه صفحات چاپ می شود.

خطوط راهنما
هنگام چاپ یک صفحه گسترده، خطوط خاکستری رنگی که به عنوان کادر اطراف سلول در برگه مشاهده می شوند، به طور پیش فرض چاپ نمی شوند. برای چاپ این خطوط از زبانه Page Layout گروه Sheet Options گزینه Print از قسمت Gridlines را انتخاب می کنیم.

 

تغییر اندازه کاغذ
نرم افزار اکسل به طور پیش فرض اندازه کاغذ را به حالتی که آن را «Letter» تعریف کرده، تعیین می کند. برای تغییر این اندازه و همچنین مشاهده اندازه های دیگر، از زبانه Page Layout و از بخش Page Setup گزینه Size را کلیک کنید.

 

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

 

برای خارج شدن از محیط Print Preview در زبانه Print Preview روی دکمه Close Print Preview کلیک کنید.

 

چاپ برگه
برای چاپ برگه، همان مسیر پیش نمایش را رفته و این بار به جای انتخاب گزینه Print Preview، گزینه Print را انتخاب کنید. در این صورت کادری مانند کادر زیر باز می شود.

 

در بخش Print range انتخاب گزینه All باعث می شود که تمام صفحات چاپ شود. اما با انتخاب بخش Page(s) می توانید صفحات مورد نظر را با شماره مشخص کنید. برای اینکار شماره اولین صفحه را در From و شمار آخرین صفحه را در To وارد کنید.
در بخش Print What می توان موضوع چاپ را تعیین کرد. بخش Selection برای چاپ سلول های انتخاب شده، بخش Active Sheet(s) برای چاپ برگه یا برگه های فعال، بخش Entire Workbook برای چاپ کل صفحه گسترده استفاده می شود.
با انتخاب گزینه Ignore print areas نیز می توان ناحیه چاپ تعیین شده را نادیده گرفت. با استفاده از گزینه Copies نیز می توان تعداد نسخه های چاپی را مشخص کرد.

 

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

 

در این مثال توضیح داده می شود که چگونه با استفاده از conditional formatting دو ستون از داده ها را با هم مقایسه کنیم.

مقایسه ی دولیست در excel

 

  برای این منظور به روش زیر عمل می کنیم.

ابتدا ناحیه A1:A6 را انتخاب نموده و نام FirstList را برای آن انتخاب می کنیم. (برای نام گذاری در کادر اسم نام مورد نظر را بدون فاصله تایپ کرده و کلید Enter را فشار می دهیم)

 

به همین ترتیب ناحیه B1:B6 را انتخاب نموده و برای آن نام SecondList را انتخاب می کنیم.

 

ناحیه A1:A6 را انتخاب می کنیم. (می توانیم از لیست کادر نام عبارت FirstList را انتخاب نماییم)

از سربرگ Home ابزار Conditional formatting را انتخاب نمایید و سپس بر روی گزینه New rule کلیک کنید.

از پنجره ظاهر شده عبارت “Use a formula to determine which cells to format” را انتخاب نمایید.

 

فرمول را به صورت زیر وارد نمایید.

=COUNTIF(SecondList,A1)=0

 بر روی دکمه Format کلید کنید و از پنجره ظاهر شده یک رنگ دلخواه انتخاب نمایید.

 

بر روی دکمه ok کلیک کنید. 

 چک باکس stop if true را در حالت انتخاب قرار داده و بر روی دکمه ok کلیک کنید.

 

نتیجه را مشاهده می کنید

 

 همانطور کهمی بینید Delhi Daredevils  و  Royal Challengers Bangalore منحصر به فرد هستند (در ناحیه SecondList قرار ندارند.)

برای مشخص کردن آیتم هایی که در SecondList باشند و در FirstList نباشند از فرمول زیر استفاده می کنیم.

=COUNTIF(FirstList,B1)=0

 

 در اینجا فرمول (COUNTIF(SecondList,A1 تعدادآیتم های ناحیه SecondList  که برابر آیتم  سلول A1 هستند را شمارش می کند (به عبارت دیگر آیا آیتم واقع درسلول A1 در SecondList هست یا خیر).

پس اگر =0(COUNTIF(SecondList,A1آیتم واقع در سلول A1 در ناحیه SecondList موجود نیست. و در پایان Conditional formatting آیتم های منحصر به فرد را به رنگ مشخص شده نمایش می دهد.

 راه دوم برای مقایسه لیست ها

یک کپی از صفحه موجود کپی نموده و سپس تمام قوائد conditional formatting  را از آن حذف کنید (انتخاب هر دو ستون –  انتخاب conditional formatting از سربرگ Home- انتخاب clear rules و سپس انتخاب clear rules from selected cells)

ابتدا ناحیه داده ها را انتخاب نمایید.

از سربرگ home ابزار conditional formatting و سپس گزینه Duplicate values را انتخاب نمایید.

 

 

 

از پنجره ظاهر شده زیر عبارت Format cells that contain گزینه Unique را انتخاب نمایید و سپس بر روی دکمه ok کلیک نمایید.

 

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

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

 

تابع Countدر اکسل:

 

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

 

=COUNT(A1:A7)

 

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

 

تابع COUNTA:

 

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

 

=COUNTA(A1:A7)

 

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

 

تابع COUNTBLANK:

 

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

 

=COUNTBLANK(A1:A7)

 

تابع COUNTIF:

 

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

 

COUNTIF(range,”criteria”)

 

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

 

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

 

=COUNTIF(A1:A7,”1000″)

 

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

 

=COUNTIF(A1:A7,”>1500″)

 

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

 

=COUNTIF(A1:A8,”ITPro*”)

 

تابع COUNTIFS:

 

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

 

COUNTIFS(criteria_range1, “criteria1”, criteria_range2, “criteria2″…)

 

 

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

 

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

با دو روش می‌توان وارد یادداشت‌ توضیحی در اکسل اضافه کرد.

از طریق منوی راست کلیک و با کلید میانبر Shift+F2

سلول مورد نظر را انتخاب کنید. کلید میانبر Shift+F2 را بزنید یا راست کلیک کرده و گزینه Insert Comment را انتخاب کنید. متن مورد نظر در گوشه سمت راست را تایپ کنید. در خارج مستطیل کلیک کنید.

به طور اتوماتیک نام کاربری، به یادداشت‌ توضیحی در اکسل اضافه می‌شود، برای تغییر نام کاربری مراحل زیر را انجام  دهید:

در قسمت FILE، بر روی گزینه Options کلیک کنید و به قسمت General منتقل شوید. در این قسمت به بخش Personalize your copy of Microsoft Office  رفته و نام کاربری خود را تغییر دهید.

 

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

برای اضافه کردن تاریخ یادداشت، پس از نوشتن آن، کلید های Ctrl و + را بفشارید. ساعت یادداشت نیز با فشردن کلیدهای Ctrl,Shift و + اضافه می‌شود.

ویرایش و حذف یادداشت توضیحی در اکسل

برای ویرایش یادداشت‌ توضیحی در اکسل، سلول مورد نظر‌ را انتخاب کنید. راست کلیک کرده و گزینه Edit Comment را انتخاب کنید. کادر محتوی یادداشت قابل ویرایش خواهد شد. متن را تغییر دهید.

برای کپی کردن یک یادداشت به دیگر سلول ها، ابتدا سلول حاوی یادداشت را انتخاب کرده و آن را کپی نمایید. به سلولی مقصد رفته و از گزینه‌های موجود در منوی Paste گزینه Paste Special را انتخاب کنید. در پنجره باز شده دکمه رادیویی Comment را بزنید.

نشان دادن یا پنهان کردن یادداشت‌ توضیحیدر excel

سلول مورد نظر‌ را انتخاب کنید. به منوی REVIEW بروید. در زیر منوی Comments اگر بر روی Show/Hide Comment یکبار کلیک نمایید، یادداشت نوضیحی نمایش داده می‌شود، اگر بار دوم کلیک نمایید یادداشت پنهان خواهد شد. در صورتیکه بر روی Show All Comments کلیک کنید، تمامی یادداشت ها نشان داده خواهد شد.

جابجا کردن و تغیر اندازه یادداشت توضیحی در اکسل

با توجه به حجم متن یادداشت‌ توضیحی در اکسل، بعضاً متن در مقابل داداه‌ها قرار می‌گیرد (متن هایی که همواره نشان داده می‌شوند). برای جابجا کردن یادداشت های توضیحی در اکسل، سلول مورد نظر‌ را انتخاب کنید. راست کلیک کرده و گزینه Edit Comment را انتخاب کنید. نشانگر ماووس را بر روی حاشیه کادر محتوی یادداشت نگه دارید، علامت چهار جهته حرکت نمایان خواهد شد. به مکان دلخواهتان درگ کنید.

 

جستجو در بین یادداشت های توضیحی در اکسل

بعضاً تعداد یادداشت های توضیحی به حدی زیاد است که پیدا کردن یک یادداشت خاص بسیار زمانبر می‌شود. برای جستجو در میان یادداشت ها به منوی HOME رفته در زیر منوی Editing کلیلک کنید. در زیر منوی Editing به بخش Find & Select  بروید. در این بخش Find را انتخاب کنید (یا کلید میانبر Ctrl+F را بزنید). در منوی باز شده دکمه Options را بزنید تا پنجره گسترده تر شود. در این پنجره در قسمت Look in گزینه Comment را انتخاب کنید. در قسمت Find what کلمه مورد نظر را تایپ کرده با زدن دکمه Find all تمامی یادداشت هایی که این کلمه را دارند، بیابید. با زدن دکمه Find next دربین سلول هایی که یادداشت توضیحی آنها این کلمه را دارند، حرکت خواهید کرد.

 

تغییر قالب یادداشت توضیحی در اکسل

برای تغییر قالب یک یادداشت توضیحی در اکسل، سلول حاوی یادداشت را انتخاب کنید. راست کلیک کرده و گزینه Edit Comment را انتخاب کنید. بر روی حاشیه یادداشت توضیحی راست کلیک کرده و گزینه Format Comment را انتخاب کنید. در پنجره بازشده قادر به تغییر دادن فرمت نوشته یادداشت خواهید بود. در تب های مختلف این پنجره می‌توانید عملیات مختلفی انجام دهید.

 

اگر در وسط کادر یادداشت راست کلیک کتید و Format Comment را انتخاب کنید، فقط می‌توانید فونت نوشته را تغییر دهید.

 

کدام سلول ها یادداشت توضیحی دارند

زمانی که یک یادداشت‌ توضیحی در اکسل اضافه می‌کنید، یک مثلث قرمز رنگ (نشانگر یادداشت توضیحی) در بالا گوشه سمت راست سلول ظاهر می‌شود. گزینه‌های مختلف چگونه نشان دادن یادداشت توضیحی یا نشانگر آن، در بخش Options قرار دارد. در قسمت FILE، بر روی گزینه Options کلیک کنید و به قسمت Advanced منتقل شوید. در این قسمت به بخش Display  رفته و یکی از گزینه های مربوط به نحوه نمایش یادداشت توضیحی را انتخاب کنید.

No comments or indicators: با انتخاب این گزینه نه نشانگرها نمایش داده می‌شود و نه خود یادداشت ها.

Indicators only, and comments on hover: در این حالت فقط نشانگر یادداشت نمایش داده می شود. همچنین یادداشت زمانی ظاهر می‌شود که ماوس را روی سلول ببرید.

Comments & indicators: هم نشانگرها و هم یادداشت ها نمایش داده می‌شوند.

 

در صورتیکه نه نشانگر و نه یادداشت نمایش داده نمی‌شوند، با استفاده از Go To Special تمامی سلول هایی که یادداشت توضیحی دارند را انتخاب و در صورت نیاز رنگ آنها را تغییر دهید. برای این کار به منوی HOME رفته در زیر منوی Editing کلیلک کنید. در زیر منوی Editing به بخش Go To Special بروید. در منوی باز شده دکمه رادیویی  Comment را انتخاب کرده و OK کنید. تمامی سلول هایی که یادداشت توضیحی دارند انتخاب خواهند شد. در زیر منوی Font  از منوی HOME رنگ مورد نظر برای سلول ها را انتخاب کنید. سلول ها رنگی شده‌اند.

 

پرینت کردن یادداشت‌ توضیحی در اکسل

دو مدل برای پرینت کردن یادداشت‌ توضیحی در اکسل وجود دارد. یک اینکه یادداشت ها را در همان مکانی که دیده می‌شوند پرینت کنید. دو اینکه تمامی یادداشت ها را در انتهای برگه و به صورت جداگانه پرینت کنید. برای پرینت یادداشت در مکان دیده شدن، از منوی  REVIEW بر روی Show All Comments کلیک کنید، تمامی یادداشت ها نشان داده خواهد شد. به منوی PAGE LAYOUT بروید. در زیر منوی Print Titles به تب Sheet منتقل شوید. در این تب به قسمت Comment  رفته و در منوی کرکره‌ای باز شونده گزینه As displayed in sheet را انتخاب کنید.

 

برای پرینت تمامی یادداشت ها در انتهای برگه، دستوررات فوق را دوباره اجرا کنید. ولی در منوی کرکره‌ای باز شونده گزینه At end of sheet را انتخاب کنید.

اضافه کردن یادداشت توضیحی به فرمول

برای اضافه کردن یادداشت توضیحی به یک فرمول از تابع N استفاده می‌شود. تابع N دارای خصوصیاتی است که اگر متنی در آرگومان آن اضافه شود، در نتیجه حاصله فرمول تاثیری نخواهد داشت. برای این منظور، پس از نوشتن فرمول یک علامت + در انتهای آن گذاشته و حرف N را تایپ کنید. اکسل تابع N را به شما پیشنهاد خواهد کرد. این تابع را انتخاب کنید. در آرگومان آن متن توضیحتان را در داخل دو علامت  ” ”  قرار داده و اینتر را بزنید.

اضافه کردن یادداشت توضیحی به کد ماکرودر EXCEL

اضافه کردن یادداشت در کد نویسی، به فهم بهتر آن کمک می‌کند. در مراجعات بعدی نیز اصلاح یا ویرایش کد را آسان تر خواهد کرد. بعضاً نیز قصد دارید بخشی از کد به صورت موقت اجرا نشود، در این موارد نیز تبدیل کردن آن به یادداشت توضیحی، بهتر از پاک کردن آن قسمت است! برای این منظور در خطی که می‌خواهید یادداشت اضافی را بنویسید، قبل از یادداشت، یک آپاستروف (‘) بگذارید. یا اینکه یادداشت را نوشته و از ابزار ویرایشی ویژوال بیسیک استفاده کنید.

 

 
P: بیان کننده این است که فرمول به صورت برداری عمل می‌کند.
N: بیان کننده این است که فرمول با محاسبه مجدد مقداری متفاوت خواهد داشت.(F9)
@: بیان کننده این است که برای استفاده از فرمول باید Analysis toolPak باید نصب شود.
 
تابع قدر مطلق در اکسل
شرح تابع
مثال
ABS
قدر مطلق یک عدد را می دهد.
=ABS(-2)
ACOS
Arcos یک عدد را می دهد. (رادیان)
=ACOS(-0.5)*180/PI()
ACOSH
آرک کوسینوس هیپربولیک درexcel
=ACOSH(10)
ATAN2
Arctan نقطه ای به مختصات X,Y را  روی دایره مثلثاتی  می دهد.(تبدیل به درجه *180/pi()
=ATAN2(-1, -1)
CEILING
عددی را به مقداری بالاتر،نسبت به صف روند می کند، که این مقدار ضریب عددی است که پارامتر دوم تابع است.
=CEILING(2.4;2)
FLOOR
عددی را به مقداری پایین تر از خودش، نسبت به صف روند می کند، که این مقدار ضریب عددی است که پارامتر دوم تابع است.
 
COMBIN
احتمال ترکیب ریاضی دو عدد در اکسل
=COMBIN(4;2)
COUNTIF
تعداد خانه هایی که دارای شرط خاصی هستند را می دهد.
=COUNTIF(B2:B5,">55")
DEGREES
رادیان را به درجه تبدیل می کند.
توجه: تمام فرمولها برحسب رادیان هستند.
=DEGREES(PI())
EVEN
عددی را به نزدیکترین عدد زوج بعداز خودش گرد می کند.
=EVEN(2.5)
EXP
عدد e  را به توان x  می رساند.
=EXP(1)
FACT
فاکتوریل یک عدد را محاسبه می کند. (N!)
=FACT(3)
FACTDOUBLE
اگر n زوج: n!=n(n-2)(n-4)…(4)(2)
اگر n فرد: n!=n(n-2)(n-4)…(3)(1)
=FACTDOUBLE(6)
GCD
بزرگترین مقسوم علیه مشترک
Greatest Common Divisor
=GCD(24, 36) à12
INR
جزء صحیح یک عدد را می‌دهد.
=INT(5.4) à 5
LCM
کوچکترین مضرب مشترک
Least Common Multiple
=LCM(36;24) à 72
LN
لگاریتم در مبنای e
=LN(2.7182818) à 1
LOG
لگاریتم در مبنای 10 یا دلخواه
=LOG(8;2) à 3
LOG10
لگاریتم در مبنای 10
=LOG10(10^5) à 5
MDETERM
دترمینال یک ماتریس (بردار) را می‌دهد.
=MDETERM({3,6;1,1})à -3
MINVERSE P
ماتریس معکوس را می‌دهد
=MINVERSE(A2:C4) àP
MMULT P
حاصلضرب دو ماتریس را می‌دهد.
=MMULT(array1;aray2) àP
MOD د
باقی مانده تقسیم دو عدد برهم در EXCEL
MOD(n, d) = n - d*INT(n/d)
=MOD(10;3) à1
MROUND
عددی را به مضربی دلخواه از عدد دیگر، گرد می‌کند.
=MROUND(10;3) à9
MULTINOMIA
نسبت فاکتوریل جمع به فاکتوریل حاصلضرب
= (a+b+c)! / a! b! c!
=MULTINOMIAL(2,3,4)à1260
ODD
عددی را به نزدیکترین عدد فرد بعد از خودش گرد می‌کند.
=ODD(1.5) à 3
PI()
عدد پی را می‌دهد.
=PI() à3.14159265358979
POWER
عدد را به توان داده شده می‌رساند.
=POWER(2;10) à1024
PRODUCT
حاصلضرب اعداد
=PRODUCT(2,3,5) à 30
QUOTIENT
خارج قسمت یک تقسیم را می‌‌دهد.
=QUOTIENT(15;3) à 5
RADIANS
درجه را به رادیان تبدیل می‌کند.
=RADIANS(90) à1.570796
RAND() N
عددی تصادفی بین 0 تا 1 تولید می‌شود.
=RAND()*(b-a)+a
=TRUNC(RAND()*100(
RANDBETWEENN@
عددی تصادفی بین محدوده ورودی داده شده می‌دهد.
=RANDBETWEEN(1,100)
ROMAN
اعداد را به اعداد یونانی تبدیل می‌کند.
=ROMAN(8) à VIII
ROUND
برای گرد کردن اعداد با دقت در تعداد رقم اعشار
=ROUND(2.15;1) à 2.1
ROUNDDOWN
گرد کردن اعداد به سمت صفر
=ROUNDDOWN(2.578;2)à2.57
ROUNDUP
گرد کردن اعداد دور از صفر
=ROUNDUP(2.578;2)à2.58
SERIESSUM در  اکسل
تابع SERIES را می‌دهد. ر.ک به راهنمای اکسل
 
SIGN
علامت یک تابع را می‌دهد. (مثبت 1 منفی 0)
=SING(-5) à 0
SIN
مقدار سینوس یک زاویه (رادیان) را می‌دهد.
=SIN(30*PI()/180) à 0.5
SINH
مقدار سینوس هیپربولیک را  می‌دهد.
 
SQRT در اکسل
محاسبه جذر یک عدد
=SQRT(36) à 6
SQRTPI
جذر مضارب عدد پی را می‌دهد.
=SQRTPI(2) à2.50
SUBTOTAL
تابعی که مجموعه‌ای از عملیاتها را برای لیست فراهم می‌کند. (نتیجه تابع بنابر سطرهای مخفی می‌تواند کنترل شود...)
ر.ک به راهنمای اکسل
SUM
محاسبه  حاصل جمع (مقدار TRUE برابر 1 است)
 
SUMIF
حاصل جمع با توجه به شرط خاصی محاسبه می‌شود.
=SUMIF(A2:A5,">160000",B2:B5)
SUMPRODUCT
عناصر نظیر به نظیر دو آرایه را درهم ضرب و سپس مجموع آنها را محاسبه می‌کند..
=SUMPRODUCT(A2:B4, C2 4)
=SUM(A2:B4*C2 4) àP =SUM(A2:B4^2) àP
SUMSQ
محاسبه مجموع توان 2 ورودی‌ها
=SUMSQ(3;5) à 34
SUMX2MY2 P  
 
SUMX2PY2 P  
 
SUMXMY2 P  
 
 TAN
محاسبه تانژانت یک زاویه
=TAN(RADIANS(45))
TANH
محاسبه تانژانت هیپربولیک یک زاویه
=TANH(-2)
TRUNC
قسمت اعشاری را حذف می‌کند.
=TRUNC(8.9) à 8

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

در این آموزش اکسل پیشرفته از تابع INDEX و ابزارهای CONDITIONAL FORMATTING و DATA VALIDATION استفاده می کنیم.

فرض کنید جدولی داریم  که از 6 ردیف و 5 ستون تشکیل شده است. تعداد 30 عدد مختلف در این جدول قرار دارد (داده ها می توانند تکراری هم باشند). می خواهیم سطر و ستون دلخواه را وارد نموده و با استفاده از تابع INDEX مقدار واقع در تقاطع این سطر و ستون را بدست آوریم. ضمناً عدد پیدا شده نیز باید با یک رنگ دلخواه در جدول مشخص گردد.

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

 

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

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

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

(INDE I5:M10,F5,G5=

در این فرمول I5:M10 محدوده جدول و F5 سلول حاوی شماره سطر و G5 سلول حاوی شماره ستون می باشد.

 

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

برای این کار ابتدا محدوده داده های جدول را انتخاب می کنیم. سپس مطابق تصاویر با استفاده از ابزار CONDITIONAL FORMATTING فرمت مورد نظر را برای محدوده داده های جدول تنظیم می کنیم.

 

 

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

(AND(ROW()=$F$10+4,COLUMN()=$G$10+8=

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

ضمناً $F$10  و $G$10 مقادیر سلول سطر و ستونی هستند که قبلاً وارد کرده ایم. (برای جلوگیری از تغییر سلول حاوی شماره سطر و ستون این آدر سها به صورت مطلق وارد شده اند)

اما چرا عدد 4 به مقدار سطر (ردیف)  و عدد 8 به مقدار ستون اضافه شده اند؟

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

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

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

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

مرحله اول: ایجاد و تنظیم باکس جستجو

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

در تب (سربرگ) developer  و در بخش ActiveX Control ابزار ComboBox را انتخاب می کنیم (اگر سربرگ Developer در نوار ریبون دیده نمی شود مسیر زیر را برای فعال کردن آن طی کنید.

File/Options/Customize Ribbon و چک باکس کنار گزینه Developer را فعال کنید)

در یک سلول دلخواه کلیک کرده تا کامبوباکس در آنجا قرا گیرد.

بر روی کامبوباکس راست کلیک کرده و Properties را انتخاب می کنیم

در پنجره ای که ظاهر می شود تغییرات را به صورت زیر اعمال می کنیم.

AutoWordSelect: False

LinkedCell: B3

ListFillRange: DropDownList  ((در گام دوم یک یک نام برای این مرحله ایجاد خواهیم کرد

MatchEntry: 2 – fmMatchEntryNon

 

سلول B3 به کامبوباکس لینک می شود به این معنی که هر مقداری که در کامبوباکس وارد شود در سلول B3 نیز ظاهر می شود.

به سربرگ Developer رفته و بر روی Design mode کلیک می کنیم تا بتوانیم متن خود را در کامبوباکس وارد کنیم.

از آنجا که سلول B3 به بامبوباکس لینک شده است هر مقداری که در کامبوباکس وارد شود در سلول B3 نمایش داده می شود.

مرحله دوم: تنظیم اطلاعات در excel

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

برای انجام این کار از سه ستون کمکی و یک دامنه اسم پویا (dynamic name range) استفاده می کنیم.

ستون کمکی 1

فرمول زیر را در سلول F3 تایپ کرده و آن را تا F22 کپی می کنیم.

=,,ISNUMBER(IFERROR(SEARCH($B$3,E3,1),””))

 

این فرمول در صورتی که متن وارد شده در کامبوباکس در ستون نام کشورها وجود داشته باشد عدد 1 را نمایش می دهد. مثلاً اگر شما حرف UNI را تایپ نمایید فقط در مقابل نام های United stats و United kingdom عدد 1 و در مقابل نام سایر کشورها عدد 0 قرار خواهد گرفت

 

ستون کمکی 2

فرمول زیر را در سلول G3 وارد کرده و تا سلول G22 کپی می کنیم.

=IF(F3=1,COUNTIF($F$3:F3,1),””)

این فرمول  مقدار وارد شده در کامبوباکس را بررسی کرده و در صورتی که این مقدار با لیست مورد جستجو مطابقت داشته باشد برای اولین مورد یافته شده عدد 1، برای دومین مورد یافته شده عدد 2 و به همین ترتیب در مقابل نام کشورهایی که با مقدار وارد شده در کامبوباکس مطابقت داشته باشند اعداد ترتیبی قرار می دهد.

برای مثال اگر شما عبارت UNI را در کامبوباکس وارد کنید در سلول G3 عدد 1 که مطابق با United States و سلول G9  عدد 2 را که مطابق با نام United kingdom و دومین مورد یافته شده است نمایش می دهد.در این حالت اگر هیچکدام از کلمات نام کشوری در باکس جستجو واردنشده باشد در مقابل نام آن چیزی قرار نمی گیرد و سلول مقابل آن نام خالی خواهد بود.

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

در سلول H3 فرمول زیر را قرار داده و تا  H22  آن را کپی نمایید.

=IFERROR(INDEX($E$3:$E$22,MATCH(ROWS($G$3:G3),$G$3:$G$22,0)),””)

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

مثلاً اگر شما UNI را در کامبوباکس (جعبه جستجو) وارد نمایید نام های United States و United Kingdom لیست شده و نام  بقیه کشورها نمایش داده نمی شوند.

 

ایجاد نام دامنه پویا (Dynamic range Name)

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

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

تذکر: همانطور که در گام اول  نام DropDownList در مقابل فیلد ListFillRange در خواص کامبوباکس وارد کردیم در اینجا نام دامنه پویا را مشابه همان نام ایجاد می کنیم.

برای ایجاد این نام دامنه مطابق مراحل زیر عمل نمایید.

به سربرگ Formulas و سپس Name Manager بروید

در کادر Name Manager بر روی New کلیک کنید تا پنجره نام جدید ظاهر شود

در فیلد نام عبارت DropDownList را وارد نمایید

در کادر Refer to فرمول زیر را وارد کنید.

=$H$3:INDE $H$3:$H$22,MAX($G$3:$G$22),1)

مرحله سوم

استفاده از کد VBA برای تکمیل گام آخر

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

در سربرگ Developer بر روی Design کلیک کنید

بر روی کامبوباکس راست کلیک کرده و گزینه View code را انتخاب نمایید

در پنجره ظاهر شده کد های نوشته شده را پاک کرده کدهای زیر را قرار دهید.

Private Sub ComboBox1_GotFocus()

ComboBox1.ListFillRange = “DropDownList“

Me.ComboBox1.DropDown

End Sub

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

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

 

 

 

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

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

تابع ضرب در اکسل | Product

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

 

 

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

تابع جمع در اکسل | Sum

این تابع عمل جمع کردن رو در اکسل انجام میده و آرگومان های آن مشابه تابع ضرب است.

 

خروجی این تابع صفر خواهد بود.

نکته:
با توجه به اینکه تفریق، همان جمع اعداد منفی است، تابعی برای تفریق نیز اختصاص داده نشده است. یا از – و یا اینکه اعداد مورد نظر رو در -1 ضرب می کنیم و در تابع Sum قرار می دهیم.

تابع توان در اکسل | Power

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

Number: پایه توان. عددی که قرار است به توان عددی برسد.

Power: توان. عددی که پایه به توان آن میرسد.

 

 

خروجی این تابع 64 خواهد بود. عدد 4 به توان 3 رسیده است.

تابع جذر | Sqrt

این تابع عملیات جذر گرفتن یا همان ریشه دوم عدد رو بر می گردونه.

 

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

=Power(64,1/3)

 

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

 

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

 

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

پنهان کردن یک یا چند ردیف در اکسل                                                                                                                                                                                                                                         برای پنهان کردن یک یا چند ردیف، سطر (s) را انتخاب کنید تا پنهان شوند.

 

بر روی یکی از ردیف ها راست کلیک کرده و گزینه Hide را انتخاب کنید.

 

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

 آشکار ساختن سطرها وستون هادر EXCEL

برای رویت یک ردیف، برای بار اول شما باید ردیف های هر دو بالا و پایین ردیف پنهان (s) را انتخاب کنید. سپس،  بر روی هدر ردیف انتخاب شده راست کلیک کرده و گزینه Unhide اتخاب کنید.

 

ردیف های مخفی دوباره  نمایش داده شد و همراه با ردیف اطراف آن برجسته است.

 

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

 

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

 

برای رویت ستون پنهان، درست مثل با ردیف پنهان، وانتخاب ستون ها هم به سمت چپ و راست از ستون پنهان،  بر روی یکی از ستون راست کلیک، و گزینه Unhide را انتخاب کنید.

 

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

 

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