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

نرم افزار اکسل از جمله کاربردی ترین نرم افزارهای Microsoft Office می باشد،نرم افزار اکسل یک نرم افزار صفحه گسترده (Spread Sheet) است، به این معنی که در واقع اکسل جدولی بسیار بزرگ است که کاربر می تواند بنا به نیاز خود برای هر سلول از این جدول داده تعریف کند، فرمول نویسی کند، ظاهر هر سلول را به دلخواه تغییر دهد و یا محدودیت های دسترسی برای هر سلول تعریف کند.

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

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

 

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

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

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

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

ساختار فرمول های اکسل

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

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

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

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

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

نکاتی پیرامون طرز ساخت لیست کشویی دینامیک در اکسل

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

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

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

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

 

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

سپس در تب Data از منوی Data Validation بر روی Data Validation کلیک نمایید (برای این کار می‌‌توانید کلیدهای ترکیبی Alt+L را نیز نگه داشته و سپس کلید D را فشار دهید).

 

در پنجره‌ی باز شده، گزینه‌ی Allow را بر روی List تنظیم نمایید.

 

سپس بر روی دکمه‌ی مقابل فیلد Source کلیک نمایید.

 

ترفندهای اکسل

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

 

در نهایت بر روی دکمه‌ی ضربدر کلیک کرده و پنجره‌ی باز را OK کنید.

 

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

لازم به ذکر است برای غیرفعال کردن این لیست کشویی، پس از انتخاب سلول مجدد کادر محاوره‌ای Data Validation را باز کرده و گزینه‌ی Allow را بر روی Any value تنظیم نمایید.

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

نکاتی در مورد یکی از ترفندهای بسیار کاربردی در 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 بدهید

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

در نرم افزار اکسل در مورد جلوگیری از نمایش فرمول ها در Microsoft Excel 2013 ارائه شده است ،

 جلوگیری از نمایش فرمول ها در Microsoft Excel 2013

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

بدین منظور:
  ابتدا تمام سلول‌هایی که قصد پنهان سازی فرمول‌های موجود در آن را دارید انتخاب کنید.
در سربرگ Home بر روی Format کلیک کنید (ترفندستان) و Format Cells را انتخاب کنید.
به سربرگ Protection رفته و تیک گزینه‌ی Hidden را زده و بر روی OK کلیک کنید.
حال دوباره بر روی Format کلیک کرده و این‎‌بار Protect Sheet را انتخاب کنید.
یک رمز عبور در کادر Password to unprotect sheet وارد کرده و OK را بزنید.
یک بار دیگر نیز رمز عبور خود را وارد کنید.
اکنون مشاهده می‌کنید که دیگر اثری از فرمول‎‌ها نخواهد بود.
برای بازگشتن به حالت اولیه بر روی t کلیک کرده و Unprotect Sheet را بزنید.

 

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

در نرم افزار کسل گاهی ممکن است تجربه کار با فرمولهایی که دارای یک عملگر هستند را داشته باشید، مانند 7+9 . فرمولهای پیچیده تر ممکن است شامل چندین عملگر ریاضی باشند، مانند 5+2*8. هر وقت که تعداد عملگرهای یک فرمول بیش از یکی باشد، ترتیب عملگرها برای اکسل تعیین می کند که کدام عملیات را ابتدا انجام دهد. برای اینکه از فرمولهایتان نتیجه دلخواه را بگیرید لازم است تا با ترتیب عملگرها در اکسل آشنا شده باشید.

 

ترتیب انجام عملیات ریاضی در اکسل

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

➊ ابتدا مقادیر داخل پرانتزها محاسبه می شوند.

➋ محاسبات مربوط به توان برای مثال 3^2

➌ ضرب و تقسیم، هر کدام که جلوتر در فرمول آمده باشند.

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

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

• P = parentheses : پرانتزها
• E = Exponential : توان
• MD = Multiplication and division : ضرب و تقسیم
• AS = Addition and subtraction : جمع و تفریق

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

PEMDAS = Please Excuse My Dear Aunt Sally

 

ایجاد فرمولهای پیچیده در excel

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

=(D3+D4+D5)*0.075

این فرمول جمع اقلام فاکتور را محاسبه می کند، و سپس مجموع آنها را بر نرخ مالیات 7.5% (که بصورت 0.075 نوشته می شود) ضرب می کند، تا در نهایت بتواند نتیجه فرمول را محاسبه نماید.



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

(45.80+68.70+159.60) = 274.10

سپس حاصل جمع بدست آمده را بر نرخ مالیات ضرب می کند.

274.10*0.075

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

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

 

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

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

➊ سلول مربوط به فرمول را انتخاب کنید، در این مثال ما سلول D5 را انتخاب میکنیم.



➋ فرمول خود را وارد کنید. در این مثال ما فرمول زیر را وارد می کنیم :

=B3*C3+B4*C4

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

2.79*35 = 97.65

2.29*20 = 45.80

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

97.65+45.80

➌ برای اطمینان از درستی کار مجددا فرمول خود را بررسی کنید، سپس اینتر را بفشارید. فرمول محاسبه شده و نتایج را نمایش می دهد. در مثال ما نتیجه $143.45 می باشد.

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

 

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

 

 

➊ ابتدا فایل اکسل زیر را دانلود کنید.

 

➋ برگه Challenge را انتخاب کنید.

➌ در سلول D7 فرمولی بسازید که مالیات فاکتور را با نرخ 7.5% محاسبه کند.

➍ در سلول D8 فرمولی بسازید که مجموع اقلام فاکتور را محاسبه کند. بعبارت دیگر این فرمول باید مجموع دامنه سلولی D3:D7 را محاسبه کند.

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

➏ وقتی کار شما تمام شد برگه اکسل شما باید چیزی شبیه تصویر زیر باشد.

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

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

 

قاعده کلی تابع OFFSET اکسل به صورت زیر است:

(عرض[اختیاری]،ارتفاع [اختیاری]، تعداد ستون، تعداد سطر، سلول یا محدوده مرجع)OFFSET

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

فرض کنید در جدولی  ما میخواهیم از یک سلول A1 به یک سلول D2 برسیم. تابع OFFSET ما به شکل زیر میشود:

(OFFSET(A1,1,3

در اینحالت نتیجه عدد 20 خواهد بود.

حالا اگر بخواهیم از سلول A1 به محدوده C2:D4 برسیم، باید فرمول رابه صورت زیر بنویسیم:

(OFFSET(A1,1,2,3,2

در این حالت چون نتیجه تابع یک سلول نیست و یک محدوده شامل شش سلول است درون سلول خطای !VALUE# در اکسل نمایش داده میشود. این حالت زمانی استفاده میشود که میخواهیم  برای نتیجه تابع OFFSET از توابعی مثل SUM یا AVERAGE استفاده کنیم. مثلا نتیجه فرمول ((SUM(OFFSET(A1,1,2,3,2 برابر عدد 82 هست که جمع شش سلول C2 تا D4 هست.

اکنون  تا حدودی با نحوه کار با تابع OFFSET اکسل آشنا شدیم 

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

• سلول یا محدوده مرجع در excel 
  
• این پارامتر سلول یا محدوده ای را  مشخص میکند که میخواهیم از آن  شروع کنیم و با حرکت کردن به تعداد سطر و ستون مشخص به سلول یا محدوده جدید برسیم. این پارامتر میتواند یک سلول مثل A1 یا یک محدوده مثل A1:B3 باشد.
• تعداد سطر: این پارامتر مشخص میکند که ما از سلول یا محدوده مرجع میخواهیم چند سطر و در چه جهتی جابجاشود. در صورتی که این عدد مثبت باشد محدوده مرجع به سمت پایین و در صورتی که این عدد منفی باشدبه سمت بالا جابجا میشود. در صورتی که محدوده مرجع  بیش از یک سلول باشد (مثلا A1:B6)، جابجایی از اولین سلول موجود در محدوده یعنی A1 محاسبه میشود.
• تعداد ستون: این پارامتر مشخص میکند که از سلول یا محدوده مرجع میخواهیم چند ستون و در چه جهتی جابجا شود. در در حالت راست به چپ در صورتی که این عدد مثبت باشد محدوده مرجع به سمت چپ و در صورتی که این عدد منفی باشد به سمت راست جابجا میشود.در صورتی که محدوده مرجع  بیش از یک سلول باشد ، جابجایی از اولین سلول موجود در محدوده (A1) محاسبه میشود. به
• ارتفاع [اختیاری]: این پارامتر مشخص میکند که بعد از جابجا شدن از محدوده مرجع به تعداد سطر و ستون مشخص، ارتفاع محدوده ای که به عنوان نتیجه تابع برگردانده میشود باید چند سطر باشد. تعیین این پارامتر اختیاری هست و در صورتی که ما پارامترهای ارتفاع و عرض رو تعیین نکنیم، تابع OFFSET اکسل به صورت خودکار پارامترهای ارتفاع و عرض را مساوی با ارتفاع و عرض محدوه مرجع ما قرار میدهد. به عنوان مثال اگر ما ارتفاع و عرض رو تعیین نکنیم و سلول A1 رو به عنوان پارامتر اول تابع OFFSET اکسل انتخاب کنیم ارتفاع و عرض هر دو برابر 1 و اگر  A1:B3 رو به عنوان محدوده مرجع انتخاب کنیم، ارتفاع برابر  3 و عرض برابر 2 خواهد بود.
• عرض[اختیاری]: این پارامتر مشخص میکند که بعد از جابجا شدن از محدوده مرجع به تعداد سطر و ستون مشخص، عرض محدوده ای که به عنوان نتیجه تابع برگردانده میشود باید چند ستون باشد. تعیین این پارامتر هم مثل پارامتر ارتفاع اختیاری ست.

نکته: تابع OFFSET اکسل یک تابع VOLATILE است بدین معنی که با هر تغییر در ورکبوک شما، فارغ از اینکه تغییر موردنظر تاثیری را نتیجه این تابع  داشته باشد یا نه، این تابع مجدداً محاسبه میشود. بنابراین در استفاده از این تابع دقت کنید چون استفاده نابجا و زیاد از این تابع میتواند فایل اکسلراخیلی کند و سنگین کند.

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

 

یز همچون گذشته لذت برده باشید

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

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

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

۱- با دابل کلیک روی عنوان ریبان آن را پنهان و با تکرار این کار آن را ظاهر نمایید.

 

۲- تکنیک چرخاندن غلطک (اسکرول)

 

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

 

 

۴٫ انتخاب چند شیت با نگهداشتن کلید Ctrl و جابجا کردن آن

 

۵٫ انتخاب سریع چندین شیت با نگهداشتن کلید

 

Shift    ۶- کپی قالب بندی مورد نظر و اعمال آن روی سایر سلول ها با دابل کلیک روی گزینه Format Painter

 

۷- بزرگ و کوچک کردن ناحیه فرمولا بار با موس 

 

۸- دابل کلیک روی یک کلمه برای انتخاب کامل کلمه

 

۹- استفاده از ابزار پرکردن (Fill handle) با استفاده از کلیک راست در اکسل

 

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

• Copy Cells
• Fill Series
• Fill Formatting Only
• Fill Without Formatting
• Fill Days
• Fill Weekdays
• Fill Months
• Fill Years
• Linear Trend
• Growth Trend
• Serie
•  
• ۱۰٫ پر کردن خودکار با دابل کلیک   
• ۱۱٫ ویرایش محدوده فرمول

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

۱۲٫ پرش به اولین و آخرین سلول حاوی داده

 

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

۱۳٫ انتخاب سریع یک محدوده به کمک کلید Shift

 

اولین سلول را انتخاب کرده و با نگهداشتن کلید Shift و کلیک روی سلول انتهایی آن محدوده را انتخاب نمایید.

۱۴٫ انتخاب محدوده چندگانه با نگهداشتن کلید Ctrl

 

۱۵٫ جابجا کردن محدوده انتخاب شده به وسیله موس

 

۱۶٫ جابجا کردن یک ستون یا محدوده انتخابی با نگهداشتن کلید Shift

 

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

 

  SHIFT + Drag   ۱۷٫ کپی کردن محدوده انتخابی با نگهداشتن کلید Ctrl   

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

 

 

 

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

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

 

” ستون های اکسل به صورت پیش فرض چپ به راست هستند. “

 

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

همانطور که می دانید به صورت پیش فرض ، شیت های اکسل به صورت چپ به راست هستند. یعنی همیشه ستون A در سمت چپ صفحه وجود داشته و به ترتیب B و C و سایر ستون ها در سمت راست آن پدیدار می شوند. اما در فایل های فارسی نیاز داریم که این ترتیب بر عکس شود یعنی شیت اکسل راست به چپ شده و ستون A در سمت راست قرار گیرد.
برای انجام این کار باید وارد سربرگ Page Layout شوید و گزینه Sheet Right To Left را  انتخاب کنید:

 

نحوه راست به چپ کردن نمودار ها در اکسل

یکی دیگر از مشکلات فارسی زبانان ، راست چین کردن نمودارهای ستونی و خطی در اکسل است. یعنی کاری کنیم که نقاط نمودار از سمت راست نمودار شروع شوند و به ترتیب به سمت چپ ادامه پیدا کنند. برای انجام این کار باید روی محور افقی نمودار خود دو بار کلیک کنید و در گزینه های سمت راست به دنبال گزینه Categories in reverse orders بگردید. با زدن تیک این گزینه ، نمودار ستونی یا خطی شما برعکس می شود.

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

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

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

 

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

 

انواع نمودار در اکسل

1- نمودار ستونی (Column): در این نمودار می توانید عنصری را با عنصر دیگر مقایسه کنید. مثلا میزان بارش باران در یک سال در ماه های مختلف.
2-نمودار خطی (Line): برای نشان دادن تغییرات یک متغیر نسبت به زمان استفاده می شود، مثلا میزان رشد قد یک کودک در یک سال.
3- نمودار دایره ای (Pie): برای نشان دادن متغیرهای یک هدف خاص به کار می رود، مثلا نشان دادن حجم استفاده شده درایوها در یک کامپیوتر.
4- نمودار میله ای (Bar): همان نمودار ستونی است با این تفاوت که ستون ها به صورت سطری استفاده می شوند.
5- نمودار مساحتی (Area): بیشتر برای حساب کردن انتگرال بین دو بازه زمانی کاربرد دارد.
6- نمودار پراکندگی (Scatter): به نمودار (X-Y) معروف است و برای نشان دادن تاثیرات دو متغیر روی هم کاربرد دارد. مثل نمودار فشار بر حسب دما.

 

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

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

 
1- ابتدا داده ها را در اکسل وارد کرده و آن ها را انتخاب می کنیم.
2- از تب Insert و از قسمت Charts نمودار ستونی یعنی Column را انتخاب می کنیم.
3- در پنجره باز شده انواع نمودارهای ستونی دیده می شوند.
4- بر روی نخستین نمودار ستونی دو بعدی کلیک کنید.
5- بعد از رسم نمودار سه زبانه  Format، Layout ، Design به زبانه های اکسل اضافه می شوند.

 

” نمودار خطی اکسل برای مقایسه تغییرات یک متغیر در چند زمان نیز میتواند استفاده شود. مثلا روند فروش در دو سال متوالی و در ماه های مختلف. “

 

نحوه رسم نمودار خطی در اکسل

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

2- داده های وارد شده را انتخاب کنید. از منوی INSERT نمودار Line را از قسمت D-2 انتخاب کنید.

3- پس از این مرحله می توانید تنظیمات گرافیکی دلخواه را اعمال کنید.

 

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

 

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

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

۱. استفاده از میانبر Ctrl + PgDn و Ctrl + PgUp برای رفتن از یک کاربرگ (worksheet) به کاربرگ دیگر د ر اکسل

 

۲. با استفاده از کلیدهای جهت دار + Ctrl بین سلول‌ها جابه جا شوید

 

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

۳. برای انتخاب داده‌ها از کلید Shift استفاده کنید

 

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

۴برای کپی دوبار کلیک کنید

 

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

۵.  تغییر فرمت سلول با استفاده از کلیدهای میانبر در erxcel

 

با استفاده از کلیدهای ترکیبی !+Ctrl + Shift می‌توانید فرمت عدد را به دو رقم اعشار تبدیل کنید. برای تبدیل به فرمت دلار کافی است از کلیدهای ترکیبی $+Ctrl + Shift و برای تبدیل به درصد از کلیدهای ترکیبی %+Ctrl + Shift استفاده کنید.

۶. برای قفل سلول‌ها از کلید F4 استفاده کنید

 

گاهی اوقات برای کپی داده‌ی یک سلول می‌خواهید فرمول آن هم کپی شود. گاهی هم می‌خواهید که این کپی صورت نگیرد. وقتی می‌خواهید ورودی سلول را قفل کنید می‌توانید یک علامت $ قبل از اسم سلول (مثلا B1) قرار دهید. تایپ کردن $ قبل از نام هم سلول کار وقت‌گیر و آزاردهنده‌ای است. در عوض می‌توانید به جای تایپ $ از کلید F4 استفاده کنید تا علامت $ ایجاد شود. با هر بار فشار دادن F4 حالت‌های قفل سلول، قفل ردیف، قفل ستون و حالت بدون قفل فعال می‌شود.

۷. خلاصه کردن داده‌ها با توابع CountIF و  SumIF در اکسل

 

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

 

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

۸. استخراج داده‌ها با تابع VLOOKUP در excel

 

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

تابع VLOOKUP چهار ورودی دارد. ورودی اول مربوط به داده‌ی مورد نظر است. در این مثال ما به دنبال عدد ۱ که رتبه‌ی دانشگاه است، هستیم. ورودی دوم مربوط به محدوده‌ی داده‌ها است که در این مثال کل ردیف A تا F انتخاب شده است. ورودی سوم مربوط به شماره ستون داده‌ی مورد نظر است. چون ما به دنبال اسم دانشگاه هستیم ورودی را ستون ۲ در نظر می‌گیریم. ورودی آخر مربوط به وقتی است که داده‌ی مورد نظر (مثلا عدد ۱) پیدا نشود که به جای آن عدد ۰ یا عبارت False را قرار می‌دهیم.

۹. استفاده از & برای ترکیب رشته‌های متنی

 

در اینجا دو ستون داریم که یکی شامل نام و دومی شامل نام خانوادگی است. با استفاده از علامت & می‌توان ستونی ایجاد کرد که نام و نام خانوادگی را در کنار هم داشته باشد. در نرم‌افزار اکسل با کمک & می‌توانید دو قسمت از متن را با هم ترکیب کنید. البته دقت کنید که بین نام و نام خانوادگی حتما فاصله قرار دهید. روش کار به این صورت است: [انتخاب سلول نام خانوادگی]& ” ” & [ّانتخاب سلول نام].

۱۰. پاک کردن متن با توابع LEFT، RIGHT وLEN  در اکسل

 

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

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

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

 

تابع LEN کل کاراکترهای یک رشته‌ی متنی را شمارش می‌کند و تنها یک ورودی دارد که آن هم سلول مربوط به رشته‌ی متنی است.

حالا با کمک تعداد کاراکترهای به دست آمده از تابع LEN می‌توانید از تابع RIGHT استفاده کنید.

 

از آن جا که ما می‌خواهیم حروف اختصاری و خط فاصله را حذف کنیم، وردی تابع RIGHT را عدد تابع LEN منهای ۳ قرار می‌دهیم.

۱۱. ایجاد اعداد تصادفی با تابع   RANDدر اکسل

 

تابع ()RAND می‌تواند اعداد تصادفی بین صفر تا ۱ ایجاد کند. این تابع هیچ گونه ورودی لازم ندارد و داخل پرانتز خالی است. با هر بار فشردن کلید F9 این تابع عدد جدیدی تولید می‌کند. دقت داشته باشید که با ایجاد هر گونه تغییر در فضای کار، یک عدد جدید ایجاد خواهد شد.

نکاتی در موردغیر فعال کردن Auto Fill Auto Fill در اکسل

یر فعال کردن Auto Fillیکی ازمشکلات در اکسل میباشید

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

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

نحوه ورود تاریخ در سلولهای اکسل
در اکسل برای ورود تاریخ باید بین اعداد روز، ماه و سال کاراکتر “/” و یا “-” آورده شود. برای وارد کردن ساعت هم از کاراکتر “:” استفاده می شود.  می توانید در یک سلول هم تاریخ و هم ساعت را وارد کنید.

 

توجه کنید که فرمت تاریخ اکسل بر اساس تنظیمات regional settings کنترل پنل ویندوز است. فرمت پیش فرض سیستم های ویندوزی اکثراً به شکل فرمت استاندارد آمریکایی بوده که اول ماه، بعد تاریخ روز و در نهایت سال وارد می شود.

 

تابع YEAR در اکسل

اگر بخواهیم از عدد سال یک سلول تاریخی استفاده کنیم باید از تابع YEAR کمک بگیریم. به همین ترتیب توابع DAY و month عدد روز و ماه را بر می گردانند.

تابع date در اکسل


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

تابع HOUR در excel

این تابع مقدار ساعت را بر می گرداند. توابع MINUTE عدد دقیقه و SECOND عدد ثانیه را برمی گرداند.

تابع TIME در اکسل


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


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

نکاتی در مورد طراحی خطوط یک جدول و چگونگی تغییر خطوط جدول در اکسل

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

باما همراه باشید تا با دو روش از آن آشنا شویم

 

روش اول

۱- جدول مورد نظر رو انتخاب کنید
۲- روی منوی Home کلیک کنید
۳- در قسمت Font ابزار Border رو پیدا کنید
۴- روی فلش کنار ابزار کلیک کنین
۵- روی فلش کنار گزینه‌ی Line Style کلیک کنید
۶- خط نقطه چین مورد نظر رو انتخاب کنید
۷- دوباره روی فلش کنار ابزار کلیک کنید
۸- گزینه‌ای که همه‌ی خطوط جدول رو مشخص می‌کنه (All Borders) رو انتخاب کنید
۹- دوباره روی فلش کنار ابزار کلیک کنید
۱۰- روی فلش کنار گزینه‌ی Line Style کلیک کنید
۱۱- خط دوتایی رو انتخاب کنید
۱۲- دوباره روی فلش کنار ابزار کلیک کنید
۱۳- گزینه‌ای که خط دور جدول رو بدن خطوط داخلی رامشخص مي‌کند (Outside Borders) را انتخاب کنید

روش دوم

۱- جدول مورد نظر رو انتخاب کنید
۲- روی منوی Home کلیک کنید
۳- در قسمت Font ابزار Border را پیدا کنید
۴- روی فلش کنار ابزار کلیک کنید
۵- گزینه‌ی More Borders را انتخاب کنید
۶- در قسمت Line و Style خطوط دوتایی رااتخاب کنید
۷- از قسمت Presets گزینه‌ی Outline را انتخاب کنید
۸- حالا از قسمت Line و Style خطوط نقطه چین رو انتخاب کنید
۹- از قسمت Presets گزینه‌ی Inside را اتنخاب کنید
۱۰- OK کنین و تغییرات را ببینید

چگونگی تغییر  خطوط جدول در اکسل

جدول، انعطاف پذیری زیادی دارد. شما می‌توانید خطوط جدول را تنظیم كنید و نیز رنگ آن را تغییر دهید. برای كل جدول، یك رنگ انتخاب كنید و یا دو رنگ انتخاب كنید، یكی برای خطوط داخلی و یكی برای خطوط خارجی تا به این صورت، یك جلوه سه بعدی ایجاد كنید.

 

جدول را انتخاب كنید  

 

 

نقطه درج را در جدول قرار دهید تا جدول برای ویرایش انتخاب شود.

 

 Table Properties را باز كنید

  

 

روی جدول كلیك راست كنید و گزینه ‏Table Properties را انتخاب كنید تا كادر محاوره‌ای Table Properties باز شود.

   

 اندازه خطوط جدول را مشخص كنید  

 

 

در قسمت Borders اندازه خطوط جدول را بر حسب پیكسل در كادر متنیSize وارد كنید. برای خطوط رنگی، باید حداقل یك پیكسل، در نظر بگیرید وگرنه نمی‌توانید رنگ خطوط را مشاهده كنید.

 

 انتخاب رنگ در excel

 

از منوی Color، یك رنگ برای خطوط جدول انتخاب كنید. برای ایجاد خطوط دو رنگی، از منوهای Light Border و Dark Border استفاده كنید. بعد از اتمام كار، روی OK كلیك كنید بخاطر داشته باشید كه اگر از یك زمینه برای صفحه استفاده كنید، این رنگ‌ها بصورت خودكار تنظیم می‌شوند.

  

 خطوط سلول را تغییر دهید  

 

 

همچنین می‌‌توانید خطوط تك تك سلول‌ها را تغییر دهید. برای این كار، داخل سلول مورد نظر كلیك كنید.

   

 Cell Properties را انتخاب كنید  

 

 

روی سلول انتخاب شده كلیك راست كنید و Cell Properties را انتخاب كنید. كادر محاوره‌ای Cell Properties كه شبیه كادر محاوره‌ای Table Properties در مرحله 3 است باز می‌شود.

   

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

 

در قسمت Borders، منوهای رنگ، درست مانند منوهای رنگ در مرحله 4 كار می‌كنند. برای اینكه خطوط، فقط یك رنگ داشته باشند یكی از رنگ‌های منوی Color را انتخاب كنید. و برای اینكه خطوط، دو رنگی شوند، رنگ‌های منوهای Light Border و Dark Border را انتخاب كنید. بعد روی OK كلیك كنید.

 

چگونگی در یافتچ خروجی csv با پشتیبانی فارسی در اکسل

یکی از بزرگ‌ترین مشکلات در کار با  Excel این است که تا این لحظه که سال ۲۰۱۳ است در این نرم افزار امکان خروجی گرفتن با پسوند csv که در آن حروف فارسی پشتیبانی شود هنوز تعبیه نشده!

۱- استفاده از Google Docs:

 داده‌های فارسی خود را در اکسل تایپ کنید و با پسوند xls یا xlsx (همان پسوند پیش‌فرض) ذخیره کنید.

 

- یک فایل SpreadSheet ایجاد کنید.

- از منوی File گزینه import را انتخاب کنید و فایل اکسل را انتخاب و وارد کنید.

- پس از لود شدن فایل، از منوی File گزینه Download as را انتخاب کنید و فرمت csv را انتخاب کنید...

- همین!

۲- استفاده از Open Office در اکسل:

روش اول به خاطر لود نشدن Google Docs و نیاز به اینترنت ممکن است چندان کاربردی نباشد.

 

- سپس فایل اکسلی که طراحی کرده‌اید را در Calc که همان اکسل اما برای Open Office است، باز کنید.

- از منوی File گزینه Save as را انتخاب کنید.

- یک پنجره باز خواهد شد، که باید Keep current settings را انتخاب کنید.

-  Character set را Unicode UTF-8 انتخاب کنید.

 

- و بقیه را بدون دستکاری رها کنید و OK کنید.

- همین!

خواهید دید که یک فایل csv با کاراکترهای فارسی خواهید داشت ;)

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

 

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

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

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

مثال برای این موارد زیاد می باشد اما بهتر است به سراغ تنظیمات قالب بندی خانه ها برویم

. برای قالب بندی خانه ها در Excel مراحل زیر را طی میکنیم :

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

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

 

روش اول :

1- انتخاب منوی Format از تب home
2- انتخاب گزینه Cells format

 

روش دوم :

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

 

روش سوم :

فشردن کلیدهای ctrl 1

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

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

 

• General

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

• Number 

   

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

  1. – Decimal Places : در این قسمت میتوانیم تعداد ارقام بعد از اعشار را تعیین کنیم.

  2. – Negative Number : در این حالت میتوانیم مشخص کنیم که عدد منفی به همان صورت نشان داده شود .یا به رنگ قرمز .یا سیاه با علامت منفی . یا به رنگ قرمز بدون علامت منفی نشان داده شود. ( توجه داشته باشید که این گزینه فقط روی اعداد منفی عمل میکند. )

  3. – Use 1000 Separator : با انتخاب این گزینه اعداد وارد شده از سمت راست سه رقم سه رقم با علامت کاما (,) جدا می نماید .

      

• Currency: مقادیر را همراه با سمبل پول رایج نشان میدهد. در این حالت میتوان تنظیمات زیر را انجام داد :

  1. Decimal Places: در این قسمت میتوانیم تعداد ارقام بعد از اعشار را تعیین کنیم.

  2. Symbol: نوع واحد پول را مشخص میکنید. در این قسمت واحد پول کشورهای مختلف نمایش داده شده و میتوانیم واحد دلخواه خود را انتخاب کنیم.

  3. – Negative Number : اعداد منفی چطور نمایش داده شوند.

      

• Accounting: مانند حالت Currency است . این قالب بندی ، قالب بندی حسابداری میباشد و علامت پولی در منتهاالهیه سمت چپ آن نوشته میشود. تفاوت عمده آن با currency در این است که بخش Negative Number در این فرمت وجود ندارد چون در حسابداری ماهیت اعداد هستند که باعث کاهندگی آن ها می شود نه منفی بودن آنها به طور مثال هزینه ها اعداد مثبتی هستند که بخاطر ماهیت آنها از درآمد کسر می شوند نه بخاطر منفی بودن آنها

   

• Date: تاریخ را با قالب بندی خاص تاریخ نشان میدهد. و ما میتوانیم انواع قالب بندی های تاریخ را دیده و انتخاب کنیم.

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

• انتخاب منطقه جغرافیایی یا location وجود دارد و با انتخاب منطقه جغرافیایی مورد نظر فرمتهای مختلف در بخش بعدی فعال می گردد

• در بخش TYPE با انتخاب منطقه جغرافیایی در بخش location انواع فرمت تاریخی در این بخش فعال شده و به دلخواه می توانید یکی از آنها را انتخاب نمایید 

   

6. Time : زمان را با قالب بندی های خاص زمان نشان میدهد. انواع قالب زمانی در این قسمت وجود دارند. ما میتوانیم مدلی را انتخاب کنیم که ساعت را از 1 تا 12 با برچسب صبح و بعد از ظهر نمایش دهد و یا مدلی را انتخاب کنیم که ساعت را از 1 تا 24 نمایش دهد

   در این قالب بندی نیز 2 امکان در دسترس می باشد .

• انتخاب منطقه جغرافیایی یا location  که با انتخاب منطقه جغرافیایی مورد نظر فرمتهای مختلف در بخش بعدی فعال می گردند

• در بخش TYPE با انتخاب منطقه جغرافیایی در بخش location انواع فرمت زمان  در این بخش فعال شده و به دلخواه می توانید یکی از آنها را انتخاب نمایید

  
  7- Percentage : عددها را همراه با علامت درصد نشان میدهد.

باامکان انتخاب تعداد اعشار در بخش decimal places

نکته

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

7. Fraction: مقادیر را بصورت عدد صحیح نشان میدهد که بدنبال آن نزدیک ترین کسر به مقدار واقعی ظاهر میشود. برای نوشتن یک عدد مخلوط کافی است ابتدا قسمت صحیح را نوشته سپس یک فاصله بدهیم و بعد صورت کسر را نوشته و بعد علامت (/) را بگذاریم و بعد مخرج کسر را بنویسیم. اگر عدد 6 5/10 را بنویسیم، پس از Enter کردن مقدار سلول 6 1/2 میشود. اگر بخواهیم همان مقدار اولیه باقی بماند، کافی است، در قسمت Fraction مقدار 3/10 را انتخاب کنیم. در این حالت نگاه میکند ببیند مخرج اصلی باید درچند ضرب شود تا مخرج انتخابی شود سپس صورت را هم در همان عدد ضرب میکند

8. Scientific : مقادیر را با قالب بندی علمی نشان میدهد

   باامکان انتخاب تعداد اعشار در اک در بخش decimal places

    

8. Text : مقادیر را به همان صورتی که وارد شده اند نشان میدهد. اگر فرمولی را به صورت متن قالب بندی کرده باشیم، Excel آن را به صورت متن نشان میدهد و مقادیر آنرا محاسبه نمیکند

    

10. Special

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

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

• Custom: در صورتی که فورمت های گفته شده پاسخ گوی نیاز های ما نباشند می توانیم در این بخش با استفاده از علائم و نشانه های قابل شناسایی برای اکسل فرمت های دلخواه خود را ایجا نموده یا فرمت انتخاب شده را تغییر دهیم .

  مثال در صورتی که در واحد پولی ریال قصد تغییر واخد به تومان را داشته باشیم بعد از انتخاب واحد ریال و تایید آن در سلول دوباره با فعال کردن پنجره format cells   و انتخاب گزینه custom  می توانیم کد نوشته شده برای واحد ریال را مشاهده نموده و به راحتی کلمه ریال را به تومان تغییر دهیم .

   

 

 

نکاتی در موردشمارش سلول‌های خاص در اکسل

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

کاربرد تابع Count

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

=COUNT(A1:A7)

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

کاربرد تابع COUNTA

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

=COUNTA(A1:A7)

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

کاربرد تابع COUNTBLANK

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

=COUNTBLANK(A1:A7)

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

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

COUNTIF(range;"criteria")

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

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

=COUNTIF(A1:A7;"20")

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

=COUNTIF(A1:A7;">18")

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

=COUNTIF(A1:A8;"X")

یک مثال: شما جدول اکسل حضور و غیاب تنظیم کرده‌اید و برای غیبت‌ها از «غ» و برای غیبت‌های موجه از «م» و برای تأخیر از «ت» استفاده کرده‌اید، حال می‌خواهید مجموع غیبت‌های افراد را بدانید، پس می‌توانید شبیه فرمول بالا را به‌طور دستی برایش بنویسید و نیز می‌توانید مانند تصویر زیر از جعبه فانکشن اکسل استفاده کنید.

 

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

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

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

به‌عنوان نمونه جهت یافتن سلول‌هایی که با مقداری بزرگ‌تر از ۱۵۰۰ در ناحیه A1:A7 از سال ۱۳۹۲ به‌بعد در ناحیه B1:B7) شروع می‌شوند، فرمول زیر را استفاده می‌کنید:

=COUNTIFS(A1:A7,">1500",B1:B7,">1392")

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

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

 

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

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

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

 

 

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.قابلیت نه چندان کارآمد معتبر سازی داده ها در  اکسل.

 

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

 

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

 

 

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

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

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

 تعریف header در یک جدول در exel

تعریف header در یک جدول مراحل زیر را دنبال کنید:فرض کنید فایل اکسل  را بازکرده‌اید و در sheet جدول هستید و مراحل را از آن به بعد شروع میکنم.
1. سطرهایی که میخواهید به عنوان سرتیر header باشند را انتخاب کنید سپس در بالای صفحه به تب Page Layout بروید و روی Print Titles کلیک کنید.
 
در صفحه بازشده که Page setup نام دارد ردیف سوم rows to repeat at top را انخاب کنید و روی فلش کنار کادر کلیک کنید در صفحه بازشده مثل سایر کادرهای محاوره اکسل با انتخاب سطرهای مورد نظر مقدار دهی کنید 

  برای اینکه یک سطر همواره در بالای صفحه باقی بماند راه های دیگری نیز هست از جمله دو روش زیر البت در صورت استفاده از این روشها تنها در نرم افزار اکسل این سطرها بالای صفاحات دیده می‌شوند و در صورت چاپ تنها در صفحه اول خواهند آمد:
برای این منظور شما میتوانید از Freeze Panes استفاده کنید. با استفاده از Freeze Panes میتوانید تعدادی از سطرها یا ستونها و یا هردو را همزمان Freeze کنید و به این ترتیب مقادر Freeze شده همواره در محل قرارگری فعلی در صفحه باقی خواهند ماند و اصطلاحا سایر سطرها و ستونها در صورت Scroll کردن سطرها یا ستونها، سطرها یا ستونها به زیر سطرها و ستونهای Freeze شده می‌روند. برای Freeze کردن سطرها یا ستونها من فرض میکنم شما یک جدول مثل جدول زیر در اختیار دارید:
برای Freeze سطر اول آن کافی است طر اول را انتخاب "select" کنید سپس به تب View رفته روی Freeze Panes کلیک کنید تا منوی آبشاری آن باز شود.Freeze Top Row را انتخاب کنید.
 
حال با اتفاده از ماوس یا صفحه کلید جدول خود را به سمت سطرهای پایین مرور کنید خوب به این ترتیب هنگام Scroll کردن صفحه روبه پایین سطر انتخاب شده شما همواره بالای صفحه خواهد ماند.

• Freeze First Column : که برای ثابت کردن سطر و ستون به صورت همزمان استفاده می‌شود.
• Freeze Top Row : که برای ثابت کردن سطرهای بالای سطر انتخاب شده استفاده می‌شود.
• Freeze First Column : که برای ثابت کردن ستونهای ماقبل ستون انتخاب شده استفاده می‌شود.

  ممکن است بخواهید سطرها و ستونهای Freeze شده را unfreeze کنید برا ی این کار نیز کافی است مسیر قبلی را رفته این منوی Freeze Panes را باز کنید و .... unfreeze را ابسته به نوع Freeze نتخاب شده قبلی unfreeze کنید

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

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

برنامه ی 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 به قسمت راست بروند در حالی که اعداد به سمت چپ برده می شوند.

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

در بسیاری از کشور های جهان  از نقطه به عنوان جداکننده اعداد اعشاری استفاده می کنند ( 19.75)  . این در حالیست که در بعضی از کشور های اروپایی و آسیایی از ویرگول به عنوان جداکننده اعداد اعشاری استفاده می شود ( 19,75 ) . بعضی از این علائم نیز به عنوان جداکننده هزارتایی اعداد استفاده می شود مانند عدد ( 1.000 ).
فرض کنید  می خواهید لیستی از قیمت های مختلف برای کشور های مختلف تهیه کنید . برای نشان دادن قیمت درست برای هر کشور  اگر قادر باشیم که علائمی را که برای برای جداکردن اعداد اعشاری استفاده می شود را از علائمی که برای جدا کردن هزار تایی اعداد استفاده می شود را متمایز و مشخص کنیم می تواند برای ما مفید واقع بشود . ما در این جا به شما نشان می دهیم که چگونه می توان علائم جداکننده اعداد اعشاری و علائم جداکننده هزار تایی اعداد را در اکسل تغییر داد .

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

برای تغییر نوع جدا کننده اعداد اعشاری و جداکننده هزارتایی اعداد در اکسل بر روی زبانه File کلیک کنید .

 

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

 
کادر محاوره ای Excel Options به نمایش در می آید. در سمت چپ این کادر بر روی Advanced کلیک کنید .

 
در سمت راست کادر در قسمت Editing options بر روی مربع کوچک Use system separators کلیک کرده تا به صورت خالی و بدون تیک در بیاید .

 
در پایین قسمت Use system separators دو گزینه Decimal separator ( جداکننده اعشاری ) و Thousands separator ( جداکننده هزار تایی ) به حالت ویرایش فعال می شوند . علائم مورد نظر خود را در کادر روبروی هر کدام از آن ها مشخص کنید . سپس بر روی ok کلیک کنید . برای مثال ما از ویرگول برای جداکننده اعشار و از نقطه برای جداکننده هزارتایی اعداد استفاده می کنیم .

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

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

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

براي اين کار پس از باز کردن اکسل روي File در گوشه بالا، سمت چپ صفحه کليک و آخرين گزينه يعني Options را انتخاب کنيد تا پنجره Excel Option ظاهر شود. در قسمت سمت چپ اين پنجره، عنوان Customize Ribbon را انتخاب کنيد.

هنگام قراردادن توضيحات در سلول هاي اکسل (insert comment) هميشه يک مستطيل ساده ظاهر مي شود، اما شما با دنبال کردن ترفندي ساده، مي توانيد ظاهر اين کادر را تغيير دهيد.

براي اين کار پس از باز کردن اکسل روي File در گوشه بالا، سمت چپ صفحه کليک و آخرين گزينه يعني Options را انتخاب کنيد تا پنجره Excel Option ظاهر شود. در قسمت سمت چپ اين پنجره، عنوان Customize Ribbon را انتخاب کنيد.

در کادر سمت چپ تعدادي از دستورات و ابزارهاي اکسل فهرست شده است و در بالاي اين کادر عبارت Popular Commands ديده مي شود. روي مثلث کوچک سمت راست اين کادر کليک و عبارت All Commands را انتخاب کنيد. نوار لغزنده اين کادر را درگ کنيد تا به دستور Change Shape برسيد.

اکنون بايد اين دستور را به کادر سمت راست با استفاده از دکمه Add اضافه کنيد. قبل از اين که بتوانيد هر دستور يا ابزاري را به کادر سمت راست اضافه کنيد، بايد در يکي از زيرگروه هاي منوهاي فعلي (مثل Home-Insert-Design) يک گروه با تب جديد اضافه کنيد.

براي انجام اين کار در قسمت پايين کادر سمت راست چند دکمه وجود دارد. براي قرار دادن دستور در گروه Home ابتدا از کادر سمت راست روي علامت + کنار گزينه Home کليک کنيد تا اين گروه بسط داده شود.

سپس روي دکمه New Group در پايين همين صفحه کليک کنيد تا يک گروه جديد در منوي Home ايجاد شود؛ در ادامه روي دکمه Add که بين 2 کادر قرار گرفته است، کليک کنيد تا گروه ابزار Change Shape به انتهاي منوي Home اضافه شود و سپس دکمه Ok را انتخاب کنيد تا اکسل بسته شود.

اکنون مشاهده مي کنيد که عبارت Change Shape در قسمتي به نام New Group در انتهاي منوي Home قرار گرفته است.

 

تغيير شکل و رنگ زمينه در اکسل 

حال براي تغيير شکل و رنگ زمينه توضيحات روي يک سلول دلخواه راست کليک و از کادر ظاهر شده عبارت Insert Comment را انتخاب کنيد. عبارتي به عنوان توضيح را وارد و سپس روي سلول ديگري کليک کنيد. مثلث کوچک قرمز رنگ، نشان مي دهد که سلول داراي توضيحات است. دوباره روي اين سلول راست کليک و اين بار عبارت Edit Comment را انتخاب کنيد.

روي کادر مستطيل توضيحات کليک کنيد و از گروه جديد که در منوي Home به نام Change Shape ايجاد کرديد، کليک کنيد و شکل دلخواه را برگزينيد.

مطالبی پیرامون روش ایجاد و استفاده از 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 برای نشان دادن داده های جدید تنظیم خواهد شد. در این مثال، مقدار فروش در هرناحیه نشان داده می شود. 

 

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

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

ابزار Data Tables در اکسل

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

خانه B12 را انتخاب کنید و آن را به خانه نارنجی رنگ متصل کنید، یعنی درون آن بنویسید =D10

در ستون a شروع به نوشتن درصد های دلخواه کنید!

سپس  A12:B17 را انتخاب کنید

 

 روی Data Table کلیک کنید:

 

پنجره باز شده دو پارامتر میخواد!

 

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

از اکسل میخواهیم مقادیری که در ستون اول جدول وارد کرده ایم را درون C4 قرار دهد و خروج را به ما نشان دهد! اما خروجی چیست؟!؟ کدام خروجی!! همان چیزی که در خانه B12 گذاشتیم!! به عنوان سر ستون! اکسل خودش اتوماتیک مقادیر هر ردیف را در C4 قرار میدهد و میبینید سر ستون چه تغییر میکند و آن را رو بروی هر ردیف می نویسد!

 

جدول دو بعدی در excel

خب ما میتوانید کار را توسعه داده و از سطر و ستون خود در Data Table استفاده کنیم! در سطر ها درصد کتاب های گران را تغییر میدادیم و حالا در ستون ها میخواهیم میزان سود کتاب های گران را تغییر دهیم و به صورت دو بعدی آنالیز کنیم!

اینبار باید خانه A12 را به خانه نارنجی رنگ وصل کنیم! چرا A12 ؟ چون این خانه طلاقی بین سطر ها و ستون های جدول ماست و اکسل مبنای خروجی را آن خانه میداند و با قرار دادن مقادیر سطر و ستون در خانه هایی که ما به او می گوییم مقدار خانه A12 را آنالیز میکند و در جدول قرار می دهد.

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

 

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

 

ستون اول جدول را درون خانه C4 میریزیم و ردیف اول جدول (یا همان سر ستون ها را ) درون خانه D7 که میزان سود کتاب های گران بود می ریزیم

 

 

 

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

ایجاد چک باکس در اکسل بسیار آسان است گزینه چک باکس در تب Developer و قسمت Tools option موجود است. چک باکس و موارد دیگر موجود در این قسمت مانند لیست کشویی، ابزار های مفیدی در هنگام طراحی یک فرم در اکسل می باشد.

این ابزار به کاربران اجازه می دهد که یک عمل را فقط با گذاشتن یک تیک انجام دهند.

 

 ایجاد یک چک باکس در اکسل :

 ابزار چک باکس در تب Developer موجود است اما این تب به صورت پیش فرض در اکسل پنهان می باشد جهت نمایش این تب (زبانه) میبایست مراحل زیر را دنبال کنید:

• پس از باز کردن یک صفحه اکسل به ترتیب این مراحل را دنبال کنید. File > Options > Customize Ribbon tab.

 

 

• سپس به صورت نمایش داده شده در شکل فوق تیک مربوط به تب Developer را بگذارید.

• پس از ظاهر شدن این تب آن را فعال کرده و با انتخاب گزینه Insert  از لیست باز شده چک باکس را انتخاب نمایید. توجه داشته باشید که از گزینه های زیر مجموعه Form Controls استفاده نمایید.

 

 

• اکنون می توانید چک باکس خود را در هرکجای صفحه که میخواهید ترسیم نمایید

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

 

• در پنجره Format Control می توانید حالت پیش فرض را برای فعال یا غیر فعال بودن چک باکس در اکسل  مشخص نمایید.همچنین در قسمت Cell link میتوانید سلولی که میخواهید نتیجه فرمان تیک گذاشتن در آن نمایش داده شود را مشخص نمایید. نتیجه فعال بودن تیک عبارت True و نتیجه غیر فعال بودن آن عبارت False خواهد بود.

 

ابزار استفاده از چک باکس در اکسل

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

 

 

یک لیست با عناوین اقلام انبار (Grocery Items) با یک چک باکس جلوی هر آیتم آن ایجاد شده است . این چک باکس تأیید کننده در دسترس بودن (Available) یا خارج از انبار بودن (Out of Stock) اقلام ذکر شده می باشد. به محض این که تیک مربوطه گذاشته شود عبارت در دسترس (Available) جلوی آیتم ظاهر میشود و اگر بدون تیک باشد نشان دهنده خارج از انبار بودن (Out of Stock) اقلام خواهد بود و این عبارت نشان داده خواهد شد.

در ادامه طریقه انجام این کار را توضیح می دهیم.

برای انجام این کار ابتدا باید برای هر یک از سطرها یک چک باکس به صورتی که توضیح داده شد ایجاد کنیم. و در Cell link آن را به سلول وضعیت آن (Abailability) که برای هر سطر خانه مجاور آن می باشد، مرتبط کنیم.

به عنوان مثال برای Cell link مربوط به چک باکس سطر دوم (B2) ، میبایست سلول متناظر در ستون E انتخاب گردد. و برای بقیه سطرها نیز همین روال را میتوانید ادامه دهید. این بدان معنی است که وقتی تیک چک باکس گذاشته می شود باید عبارت True در همان سطر در ستون E نمایش داده شود و در صورتی که تیک گذاشته نشود عبارت False نشان داده شود.(به صورتی که در تصویر مشاهده می کنید).

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

 =IF(E2=TRUE,"Available","Out of Stock")

این فرمول بدان معنی است که در صورتی که در ستون E که خود از طریق گذاشتن و برداشتن تیک ستون B تغییر میکند، اگر عبارت True بود در نتیجه در ستون C عبارت “Available” و در غیر این صورت عبارت “Out of Stock” نمایش داده شود.

برای مثال همانطور که در شکل زیر می بینید با گذاشتن تیک مربوط به سطر دوم در نتیجه عبارت True در ستون E ظاهر شده و همچنین با توجه به فرمول IF که قرار داده ایم در ستون C  عبارت Available ظاهر می شود.

سپس با استفاده از فرمول COUNTIF به صورتی که در زیر نمایش داده میشود میتوانید تعداد اقلام در دسترس (Items Available) و تعداد اقلامی که در دسترس نیست (Unavailable) را مشخص کنید.همچنین تعداد کل اقلام (Total Items) نیز با استفاده از تابع ساده Count مشخص خواهند شد.

فرمول مربوط به سلول D14 :

=COUNT(C2:C11)

فرمول مربوط به سلول D15 :

=COUNTIF(C2:C11,"Available")

فرمول مربوط به سلول D16 :

=COUNTIF(C2:C11,"Out of Stock")

نکات مهم:

• برای ایجاد چک باکس در چندین سطر این کار را در یک سلول انجام دهید و سپس با استفاده از ابزار Fill تا محدوده ای که میخواهید آن را بسط دهید. فقط دقت کنید که پس از این کار میبایست Cell link هر چک باکس را جداگانه آدرس دهی کنید.
• برای حذف همزمان چندین چک باکس میتوانید مراحل زیر را دنبال کنید.

‘Home’ تب> ‘Find and Select’ > ‘Go To Special’> ‘Objects’> Delete

نکته : برای حذف چند مورد از چک باکس ها می بایست به صورت زیر عمل نمایید:

‘Home’ تب> ‘Find and Select’ > ‘Selection Pane’. > انتخاب موارد> delete

 

نکاتی پیرامون توابع: Right و Left وMID در اکسل

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

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

---

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

= RIGHT(address or text, number of characters)

به عنوان مثال عبارت I Love ITPro را در سلول A1 وارد میکنیم. حالا به منظور استخراج کلمه ITPro،  از فرمول Right استفاده میکنیم، بدین معنی که از سمت راست سلول A1 ، تعداد 5 کاراکتر را استخراج می کند:

 

تابع Left در excel:

---

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

=LEFT(address or text, number of characters)

به عنوان نمونه اگر المانهای مثال فوق را در این تابع به کار ببریم؛

 

 

---

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

---

تابع  MID در EXCEL:

---

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

=MID(address or text, start number, number of characters)

به عنوان نمونه، کارکتر 8 از سلول A1، حرف I از کلمه ITPro است که با مشخص نمودن تعداد 5 کاراکتر، کلمه ITPro با استفاده از تابع MID استخراج می گردد:

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

لحظه هایتان آسمانی

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

 در نرم افزار excel فهرست شماره تلفن، کد ملی، نام و نام‌خانوادگی افراد و… 

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

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

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

 

اطلاعات تکراری در اکسل :

۱ـ برای مشخص کردن اطلاعات تکراری در هر ستون یا سطر از نرم‌افزار اکسل باید ابتدا سلول‌های موردنظر (کل ستون یا سطر موردنظر) را به‌حالت انتخاب درآورید.

۲ـ از ریبون بالای نرم‌افزار به تب Home بروید و روی گزینه Conditional Formatting کلیک کنید.

۳ـ از منوی به‌نمایش درآمده گزینه Highlight Cells Rules را انتخاب کرده و در نهایت روی duplicate Values کلیک کنید.

۴ـ در پنجره به‌نمایش درآمده دو گزینه قابل انتخاب است. در کادر اول از سمت چپ، مقدار Duplicate را انتخاب کرده و در کادر دوم، یکی از مقادیر را برای ایجاد تمایز بین سلول‌های عادی و سلول‌های دارای مقادیر تکراری انتخاب کنید.

۵ـ با کلیک روی گزینه OK مشاهده می‌کنید که سلول‌های دارای مقادیر تکراری با توجه به علامت تمایزی که در مرحله ۴ مشخص کرده‌اید، از سلول‌های دیگر جدا شده‌اند.

 

اطلاعات منحصر‌به‌فرد در excel :

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

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

نکاتی در مورد قالب‌بندی شرطی در Excel

فرض كنید می‌خواهید با استفاده از كاربرگ‌های‌ ‌برنامه اكسل، نمرات دانش آموزان را كنترل كنید. به این صورت كه نمرات خوب از نمرات متوسط و ضعیف به‌راحتی قابل تفكیك باشند. باید دید برای این امر چه‌كار باید بكنید؟ می‌توانید از قالب بندی شرطی یا conditional formatting استفاده نموده و تغییرات مختلفی را در ظاهر یك سل (Cell)، بر اساس مقدار آن، ایجاد نمایید.
با قالب بندی شرطی قادر خواهید بود برای هر سل، سه شرط تعیین كنید كه با فرمت فعلی آن سل، مجموعاً چهار شرط می‌شود. با اعمال هر شرط، می توانید نوع فونت، خطوط حاشیه، و نوع سایه‌گذاری ‌‌(‌shading) هر سل را تعیین كنید كه البته این تغییرات، شامل فرمت عددی و نوع چیدمان سل نمی‌شود. برای استفاده از قالب‌بندی شرطی در اکسل ، باید شروطی را انتخاب كنید كه با مقادیر false و ‌true قابل ارزیابی باشند. مثلا جمله "آیا عدد بزرگ‌تر از ۱۰ است؟" را می‌توان با false یا true ارزیابی كرد.
اما جمله "مقدار عدد چقدر است؟" به این روش قابل ارزیابی نیست. بدین ترتیب، اگرنتیجه شرط، true باشد، تغییرات در سل انجام می شود وگرنه تغییری در آن ایجاد نخواهد شد. همچنین می توان به ترتیب تا سه شرط برای یك سل تعیین كرد كه اگر شرط اول، ارزش ‌true داشت، تغییرات انجام شده و بقیه شرط‌ها نادیده گرفته خواهند شد.
حال این معلم می خواهد رنگ هر سل را بر اساس نمره موجود در آن، تعیین كند. بدین صورت كه خانه‌های دارای نمره كمتر از ۵۰ به‌رنگ‌قرمز، بین ۵۰ و ۶۵ به‌رنگ ‌سبز، بین ۶۵ و ۷۵ به‌رنگ ‌بنفش، و نمرات بالاتر از ۷۵، به‌رنگ آبی درآیند.
برای این‌كار، ابتدا رنگ فونت كلیه سل‌های حاوی نمرات را آبی كنید. سپس آن‌ها را انتخاب نموده و در منوی Format، روی Conditional Formating كلیك كنید. در پنجره باز شده، كادر اول را در حالت Cell Value Is باقی بگذارید. از كادر دوم، گزینه ‌less than را انتخاب و در كادر روبه‌روی آن، عدد ۵۰ را تایپ كنید. سپس دكمه ‌Format واقع در سمت راست را كلیك كرده ودر زبانه Font، رنگ قرمز را برای اعداد كمتر از ۵۰ انتخاب نمایید. با زدن ‌OK، به كادر قبلی برگشته ودكمه Add در پایین صفحه را كلیك كنید.
می توانید تا سه شرط را برای هر سل تعیین كنید.
حالا برای تعیین شرط دوم، كادر مقابل Cell Value Is را به ‌less than، و در كادر روبه‌روی آن، عدد ۶۵ را بنویسید. سپس دكمه Format را زده و در كادر باز شده، رنگ سبز را انتخاب كنید. یك‌بار دیگر بر روی Add كلیك كنید و شرط آخر را نیز بدین ترتیب تعیین نمایید: در كادر اول Cell Value Is، در كادر دوم less than، و در كادر روبه‌روی آن، عدد ۷۵ را تایپ كنید. سپس دكمه ‌Format را كلیك كرده و برای این شرط، رنگ بنفش را انتخاب نمایید. اگر دقت كنید، می‌بینید كه دكمه Add بعد از اعمال سومین شرط، غیر فعال می‌شود. چرا كه شما نمی‌توانید بیش از سه شرط برای یك سل تعیین كنید. OK را بزنید و نتیجه را ملاحظه نمایید. ‌
راه ساده‌تر آن‌است كه قالب‌بندی‌های مورد نظرتان را به‌صورت یكجا بر تمامی سل‌ها اعمال كنید. اما اگر خواستید، می‌توانید بااستفاده از دكمه ‌FormatPainter، قالب‌های شرطی و فرمت اولیه یك سل را به سل‌های دیگر كپی نمایید.
در ضمن برای این‌كه در آینده بدانید قالب‌بندی‌های شرطی بر روی كدام‌یك از سل‌های كاربرگتان اعمال شده است، از منوی Edit،‌ گزینه Go To را كلیك و در كادر باز شده، دكمه ‌Special را كلیك كنید. در پنجره باز شده، دكمه رادیویی ‌Conditional Formats را علا‌مت زده و ‌OK كنید. با این‌كار كلیه سل‌های دارای قالب‌بندی شرطی، با رنگ جداگانه نمایش داده می‌شوند.‌
می‌توانید فرمتی را روی یك سطر با توجه به تعداد یك سل خاص، اعمال نمایید.
همچنین می‌توان فرمت یك ردیف را بر اساس داده های یك سل در آن ردیف تعیین كرد. فرض كنید شماره ستون‌ها را در ردیف یك می‌نویسید، نام دانش آموزان را در ستون‌‌A ، و نمره آن‌ها را نیز در ستونB. كلیه سل‌ها از خانه ‌‌۲ A تا آخرین آن‌ها در ستون B را انتخاب كنید. از مسیر Format / ConditionalFormatting در لیست اول، گزینه ‌Formula Is و در لیست دوم، شرط ‌‌۵۰>۲=‌‌$B را نوشته و رنگ متن را نیز تنظیم كنید. با استفاده از دكمه Add، شروط دوم و سوم را به ترتیب ۶۵> B۲ $= و ۷۵ >‌‌۲‌B $= تعیین نمایید. برای كنترل مجدد تغییرات هر سل، ابتدا سل ‌‌A۲ را انتخاب كنید و به مسیر Format / Conditional Formatting بروید.
این سل بر اساس مقدار موجود در سل B۲ تغییر خواهد كرد. فرمت سل ‌‌‌A۳ نیز براساس سل B۳ تغییر خواهد نمود و به همین ترتیب تا آخر. بخش‌ B$ فرمول برای مقایسه دوبه‌دو بین ستون‌ها تنظیم می‌شود تا بتواند تغییرات هر ردیف را به‌طور جداگانه انجام دهد. همچنین می‌توانید از شرط Formula Is برای دیگر ردیف‌ها نیز استفاده كنید. مثلاً بعد از انتخاب ردیف‌های موردنظر از فرمول‌های (Mod(Row(),۲= و ((Not(Mod(Row(),۲= استفاده نمایید. برای این‌كه به‌جای ردیف‌ها، در ستون‌ها تغییرات ایجاد كنید، در فرمول آن‌ها به‌جای ()‌ Row از () Column استفاده نمایید. اگر هم خواستید، می‌توانید در شروط ایجاد شده در ردیف‌های دیگر، از تغییراتی مانند رنگ و سایه گذاری نیز استفاده كنید.
استفاده از فرمول‌های پیچیده‌تر در ایجاد تغییرات شرطی، امكانات قوی‌تری را در اختیار شما قرار می‌دهد. مثلاً برای این‌كه در محدوده‌‌ B۲:B۵۰ اعدادی كه بیش از یك‌بار آمده‌اند مشخص شوند، می‌توان در جلو لیست Formula Is فرمول‌‌ ۱<(COUNTIF($B$۲:$B$۵۰;$B۲= را نوشت. برای پیدا كردن سل حاوی بزرگ‌ترین مقدار (یا ردیف حاوی سل بزرگ‌ترین مقدار) نیز می‌توان از فرمول (۵۰$B۲=MAX($B$۲:$B$=استفاده نمود. اگر هم خواستید فرمولتان محدوده وسیع‌تری را در یك ستون پوشش دهد، در این فرمول به‌جای ‌‌۵۰$B۲:$B$ از B:$B$ استفاده كنید.‌

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

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

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

 

 

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.قابلیت نه چندان کارآمد معتبر سازی داده ها.

 

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

 

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

 

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

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

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

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

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

اسناد و مدارک قلب هر کسب ‌ و کار خوب است، مخصوصاً اسناد حسابداری و امور مالی. اکسل وسیلهٔ مهمی است که می‌تواند به کارشناسان امور مالی و حسابداری کمک کند تا گزارش‌ها را درست کنند، داده‌ها را تحلیل نمایند و راهبردهای مالی را آماده کنند. اگرچه شما ممکن است دانشی مقدماتی از اکسل داشته باشید، دربارهٔ توابع ویژه‌ای که می‌تواند کار شما آسان‌تر کند ممکن است اطلاع نداشته باشید.

 

چرا مهارت بالا در اکسل برای حسابداران مهم است؟

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

البته وانمود کردن به استادی در اکسل سخت است. فرق بزرگی است بین مهارت یک حسابدار متوسط در این نرم‌افزار و یک حسابدار سطح بالا. استاد شدن در اکسل یک حرکت عالی در حرفهٔ حسابداری است. اگر چه شما لازم است که صبور باشید و وظائف حرفه‌ای‌تان را به‌ خوبی انجام دهید تا بتوانید از پله‌های موفقیت بالا بروید – درست مثل هرکس دیگر – با این حال داشتن مهارت های بالا در اکسل می‌تواند به شما کمک کند که در نهایت به شغلی که واقعاً می‌خواهید برسید.

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

اکسل همچنان ابزار مورد انتخاب در دنیای حسابداری و سرمایه‌گذاری برای فهم و تحلیل داده‌های مالی، یعنی پردازش اعداد و تدوین اطلاعات غیر عددی است. اکسل در سال ۱۹۸۵ درست شده است و با وجود تغییرات فراوان در تکنولوژی، هنوز هم در بیشتر صنایع کارها را با آن انجام می‌دهند. اکسل با استفاده‌های متنوعی که در تحلیل داده، مدیریت درآمدها و مخارج و پیش‌بینی و مدلسازی عملکرد مالی دارد بخش مهمی از صنعت امروز است.

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

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

 

 تحلیل‌های سنگین با اکسل

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

از حسابداران در سطح متوسط انتظار می‌رود چیزهایی مانند اینکه چطور از توابع Vlookups و Hlookup و جدول های محوری استفاده کنند را بلد باشند. از سوی دیگر حسابداران سطح بالا باید بدانند چطور ماکرو بنویسند و به زبان Visual Basic برنامه یا اسکریپت بنویسند.

«در دنیای تجارت هر جا را که نگاه کنی اکسل حضور دارد. اکسل پراستفاده ترین نرم‌افزار در دنیای تجارت است.»

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

اکسل ابزار اصلی در بیشتر شرکت های حسابداری است، از آن برای پیش‌بینی و تسهیل رشد شرکت استفاده می‌شود، و همچنین برای کمک به تصمیم گیران که تعیین کننده یک سیستم به چه چیزهایی نیاز دارد و چه تغییراتی می‌باید اعمال شود. حتی اگر از اکسل متنفر هستید باید بدانید که جزء ابزاری است که در دنیای تجارت هر قدر هم موقعیت شغلیتان پایین باشد نمی‌توانید از آن اجتناب کنید. از تحلیل‌گران حسابداری، مدیران و حتی رؤسا انتظار می‌رود که بتوانند با حداکثر کارایی از آن استفاده کنند. برای ماندن در دنیای حسابداری، باید یاد بگیرید اکسل را دوست داشته باشید – یا دستکم به شکل مناسب آن را به کار بگیرید.

این مهارتها به تمرین و تجربه نیاز دارد؛ یک حسابدار تازه‌کار حقیقتاً نمی‌تواند ادای کاری که هنوز باید یاد بگیرد را در بیاورد. خبر خوب چیست؟ اگر مقداری زمان و انرژی (و احتمالاً پول) برای یاد گرفتن توابع پیچیده‌تر اکسل داشته باشید، آنگاه شما برای شرکت های دولتی و خصوصی تبدیل به یک دارایی بسیار با ارزش خواهید شد. منابع بیشماری هست – کتابها، فیلم‌های موجود در اینترنت، و مانند اینها – که می‌تواند به شما کمک کند در ریزه‌کاری های این نرم‌افزار استاد شوید. فقط دوره های آموزش حسابداری کافی نیست، اگر لازم بود ثبت نام در دوره‌های آموزش اکسل را هم در نظر داشته باشید. اینها به شما کمک خواهد کرد که هم به سطح بالاتری از تجربه دست پیدا کنید، و هم در طول زمان فرصتهای شغلی بهتری داشته باشید.

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

نکاتی در رمورد تابع VLOOKUP و نحوه رفع محدودیت این تابع در اکسل

 

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

 

ساختار تابع:

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

اجزاء:

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

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

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

range_lookup
این قسمت تابع اختیاری می باشد. در صورتی که این قسمت را وارد نکنید مقدار پیش فرض (TRUE) در نظر گرفته خواهد شد.

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

اگر مقدار FALSE یا ۰ را وارد کنید دقیقا همان عبارت مورد جستجو را پیدا می کند و در صورتی که نتیجه ای در برنداشت خطای #N/A را نشان می دهد.

 

در آموزش اکسل  امروز مبحث جدیدی را مورد بررسی قرار میدهیم

 

رفع محدودیت تابع  Vlookupدر اکسل :

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

 

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

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

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

 

روش کار:

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

=choose({1,2},lookup column,result column)

در فرمول فوق بجای lookup column ستونی که کد مورد جستجو درون آن قرار دارد را انتخاب می کنید و بجای result column ستونی که نتیجه درون آن قرار دارد را انتخاب می کنید.

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

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