کلاس های حضوری طراحی داشبورد و اکسل پیشرفته

مطالبی جامع پیرامون Data Table در اکسل

اکسل یکی از ابزارهای تحلیل حساسیت در اکسل به شمار میرود . در این آموزش  با نحوه استفاده از ابزار Data Table اکسل آشنا میشویم و میبینیم که چطور میشود به کمک این ابزار نتیجه تغییرات متغیرهای مختلف را بررسی کنیم.

با استفاده ابزار Data Table اکسل میتوانیم تاثیر تغییرات یک یا دو متغیر رو روی متغیر وابسته مدنظرمون بررسی کنیم. در ادامه با چگونگی استفاده از ابزار Data Table اکسل برای تحلیل تک متغیره و دو متغیره آشنا میشویم.

 

تحلیل تک متغیره در Data Table اکسل:

از این حالت زمانی استفاده میکنیم که بخواهیم نتیجه تغییرات صرفاً یک متغیر رادر فرمول  مشاهده کنیم.فرض کنید هزینه سوخت خودرو به ازای هر کیلومتر 240 واحد هست و ما میخواهیم تاثیرات مسافت طی شده خودرو را بر هزینه سوخت آن به ازای مسافت های یک تا ده کیلومتر تحلیل کنیم. برای اینکار ابتدا فرمول A1*240= رو در سلول A2 مینویسیم. سپس اعداد 1 تا 10 رو در سلول های B1 تا K1 قرار میدهیم. و از تب DATA و بخش Data Tools رو گزینه What-If Analysis کلیک میکنیم و در منویی که باز میشود  گزینه Data Table را انتخاب میکنیم.

 

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

 

حالا روی فلش قرمز رنگ گزینه Row Input Cell کلیک میکینم. با کلیک روی این گزینه پنجره زیر باز میشود که ما باید در  سلول مربوط به متغیر مورد نظررا  انتخاب کنیم که این سلول همون سلول A1 هست.

 

پس از اینکه سول A1 را انتخاب کردیم با کلیک مجدد روی فلش قرمز رنگ به پنجره اصلی برمیگردیم و OK رو کلیک میکنیم. همونطور که مشاهد میکنید هزینه مربوط به کیلومترهای مختلف در سلولهای B2 تا K2 نمایش داده میشود.

 

تحلیل دو متغیره در Data Table اکسل:

در بخش قبل حالت تک متغیره ابزار Data Table اکسل رو بررسی کردیم. حالا میخوایم حالت دو متغیره Data Table رو امتحان کنیم بنابراین به یک مثال کاملتر احتیاج داریم. فرض کنید رشد یا کاهش فروش سازمان ما تابع دو متغیر مقدار افزایش تبلیغات و میزان افت کیفی محصول باشه به طوری که با هر واحد افزایش تبلیغات 17درصد به فروش سازمان افزوده بشه و با هر واحد کاهش کیفیت محصول 30درصد  از فروش سازمان کم بشه. میخوایم اثرات تغییر این دو متغیر رو روی افزایش یا کاهش فروش سازمان بررسی کنیم. دامنه بررسی ما برای متغیر کاهش کیفیت محصول بین 0.5 تا 3 واحد و برای متغیر افزایش تبلیغات بین 0 تا 5 واحد هست. برای شروع ابتدا در سلول A3 فرمول افزایش فرمول تغییرات فروش سازمان رو که A1*-0.3+A2*0.17= مینویسیم. با توجه به فرمولی که ما نوشتیم سلول A1 معرف متغییر کاهش کیفیت و سلول A2 معرف متغییر افزایش تبلیغات هست.

 

حالا مثل حالت تک متغیره از تب DATA گزینه Data Table رو انتخاب میکنیم و در بخش Row Input Cell سلول A1 و در بخش  Column Input Cell سلول A2 رو قرار میدیم و پنجره Data Table رو OK میکنیم. با اینکار درصد تغییرات فروش برای مقادیر مختلف افزایش تبلیغات و کاهش کیفیت محصول در جدول نمایش داده میشه.

 

به عنوان مثال اگر کیفیت محصول یک واحد افت کنه و ما 4 واحد تبلیغاتمون رو افزایش بدیم فروش سازمان 38درصد افزایش پیدا میکنه یا اگر کیفیت محصول ما 3 واحد افت کنه، علیرغم افزایش 5 واحدی تبلیغات هم فروش سازمان 5درصد افت خواهد داشت

نکاتی پیرامون توابع ریاضی و ساختار آن در اکسل

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

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

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

 

روش درج فرمول در سلول ها excel

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

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

 

 

 

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

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

 

مقادیر ثابت

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

 

آدرس سلول ها

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

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

 

 

 

عملگرها

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

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

 

عملگرهای مقایسه ای در اکسل

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

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

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

 

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

 

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

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

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

 

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

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

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

 

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

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

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

 

توابع

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

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

 

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

•توابع مالی

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

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

•توابع آماری

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

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

•توابع متنی

•توابع منطقی

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

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

 

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

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

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

 

 

 

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

 

 

 

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

 

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

 

 

 

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

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

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

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

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

 

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

 

 

 

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

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

 

 

 

 

 

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

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

 

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

 اگر یك ستون یا سطر از داده‌ها را از جدول منبع نمودار حذف كنید، نمودار بصورت خودكار، خود را تنظیم می‌كند. ولی، اگر داده‌هایی را به جدول منبع اضافه كنید (مثلاً اگر ارقام فروش یك ماه دیگر را اضافه كنید) باید این داده‌های جدید را بصورت دستی به نمودار اضافه كنید. اگر یك نمودار ایجاد كرده‌اید كه به خوبی فرمت دهی شده است و می‌خواهید از این نمودار برای نمایش دادن داده‌های جدید استفاده كنید (بجای ایجاد یك نمودار جدید) می‌توانید محدوده داده‌های منبع نمودار را به یكی از روش‌های ممكن تغییر دهید.

 محدوده منبع را انتخاب كنید 

 اروی نمودار كلیك كنید تا انتخاب شود. یك خط مرزی رنگی دور داده‌ها را فرا می‌گیرد.

 خط مرزی را با ماوس بكشید 

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

 دكمه ماوس را رها كنید 

به محض اینكه دكمه ماوس را رها می‌كنید، محدوده داده‌های منبع دوباره رسم می‌شوند و نمودار، داده‌های جدید را نشان می‌دهد. توجه كنید كه خط مرزی رنگی، محدوده بزرگتری را نسبت به مرحله 1، احاطه كرده است.

 از گزینه Add Data استفاده كنید 

 یك روش دیگر برای اضافه كردن داده‌های جدید به نمودار، استفاده از گزینه Add Data است، از منوی Chart، گزینه Add Data را انتخاب كنید تا كادر محاوره‌ای Add Data باز شود.

 داده‌های جدید را انتخاب كنید 

در حالی كه كادر محاوره‌ای Add Data باز است، داده‌هایی از برگه كاری را كه می‌خواهید به نمودار اضافه كنید متمایز كنید (اگر لازم باشد، می‌توانید كادر محاوره‌ای را از سر راه بردارید فقط كافی است كه با ماوس، نوار عنوان آن را بكشید) بعد از اینكه داده‌های مورد نظر را انتخاب كردید، محدوده آن در كادر متنی Range درج می‌شود.

 

 داده‌ها را اضافه كنید 

 روی Ok كلیك كنید تا كادر محاوره‌ای Add Data بسته شود و داده‌های جدید به نمودار اضافه شود.

* توجه  *

 تغییر در داده‌های منبع

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

اگر محدوده‌ای كه برای نمودار انتخاب كردید اشتباه باشد، می‌توانید با دوباره انتخاب كردن، آن را تغییر دهید. از منوی Chart، گزینه Sonrce Data را انتخاب كنید تا كادر محاوره‌ای Sonrce Data باز شود. محدوده داده‌های جدید را در كادر متنی Data Range تایپ كنید و روی Ok كلیك كنید.
چاپ كردن نمودارهادر excel

برای اینكه فقط نمودار را چاپ كنید و برگه كاری چاپ شود، ابتدا نمودار را انتخاب كنید و از منوی File، گزینه Print را انتخاب كنید. در كادر محاوره‌ای Print، اطمینان حاصل كنید كه گزینه Selected Chart انتخاب شده باشد و روی Ok كلیك كنید.

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

 خطوط شبكه ای كه در برگه های كاری Excel مشاهده می كنید كمی گمراه كننده می باشند. معمولاً، این خطوط چاپ نمی شوند و اگر هم آنها را چاپ كنید كمرنگ به نظر می رسند. برای اینكه سلول ها خطوط مرزی داشته باشند از گزینه هایBorder استفاده كنید. می توانید خط مرزی را به یك سلول یا به محدوده ای از سلول ها اضافه كنید. می توانید خط مرزی را در فقط یك سمت سلول قرار دهید و یا پیرامون تمام جهات سلول. اگر خطوط مرزی سلول ها به دلخواه شما نباشند از یك الگوی پس زمینه مانند تركیب رنگ، جلوه ویژه یا رنگ آمیزی استفاده كنید، ولی به یاد داشته باشید كه پس زمینه ای كه زیاد شلوغ باشد، باعث می شود كه خواننده به سختی داده ها را ببیند.

 

 كادر محاوره ای Format Cells را باز كنید  

 

سلول یا محدوده ای كه می خواهید خط مرزی یا الگو را به آن اضافه كنید انتخاب كنید و از منوی Format ، گزینه Cellsرا انتخاب كنید تا كادر محاوره ای Format Cells باز شود.

 

 انتخاب شیوه خط  در اکسل 

 

روی برگه Border كلیك كنید. از لیستStyle ، یك شیوه انتخاب كنید و از لیستColor برای انتخاب رنگ برای خط مرزی استفاده كنید.

 

 از Presets استفاده كنید  

 

وقتی كه شیوه خط مشخص شد، یكی از گزینه‌های Presets را كه در بالای برگه قرار دارند انتخاب كنید. برای بكاربردن یك خط مرزی حول لبه های خارجی سلول یامحدوده انتخاب شده، روی Outline كلیك كنید. برای بكار بردن خطوط شبكه، داخل سلول‌های داخلی محدوده، رویInside كلیك كنید.

 

 یك خط مرزی سفارشی ایجاد كنید  

 

برای سفارشی كردن خط مرزی، از دكمه های Border استفاده كنید و مشخص كنید كه كدام قسمت های سلول های انتخاب شده باید خط مرزی داشته باشند. برای اضافه كردن یك خط مرزی به آن قسمت، روی دكمه مورد نظر كلیك كنید. برای مشاهده نتیجه، به قسمت پیش‌نمایش دقت كنید، در صورت نیاز، بقیه خطوط مرزی را نیز اضافه كنید.

 

 از برگه Patterns استفاده كنید  

 

برای بكار بردن الگو روی سلول های انتخاب شده، روی برگه Patterns كلیك كنید. برای پر كردن سلول های انتخاب شده با رنگ، از صفحه Color یك رنگ انتخاب كنید. برای تركیب كردن دو رنگ در الگو، روی دكمه لیست كشویی Pattern كلیك كنید و رنگ دوم را انتخاب كنید. دوباره لیست را باز كنید تا یك الگو انتخاب كنید. برای مشاهده نتیجه انتخاب ها، به قسمتSample نگاه كنید.

 

انتخاب های فرمت دهی را اعمال كنید  

 

روی Ok كلیك كنید تا كادر محاوره‌ای Format Cells بسته شود و تنظیمات جدید در برگه كاری اعمال شوند.

 

*توجه *

چاپ كردن خطوط شبكه در اکسل

بصورت پیش فرض، خطوط شبكه Excel چاپ نمی‌شوند. برای اینكه آنها را چاپ كنید، از منوی File ، گزینه Page Setup را انتخاب كنید. در كادر محاوره ای Page Setup ، روی برگهSheet كلیك كنید. در قسمت Print ، گزینه Gridlines را انتخاب كنید و روی Ok كلیك كنید. حال برگه كاری را چاپ كنید تا خطوط شبكه را مشاهده كنید.

استفاده از دكمه های Formatting در اکسل

برای اضافه كردن خط مرزی به یك سلول، آن را انتخاب كنید، روی فلش دكمه Borders در نوار ابزار Formatting كلیك كنید و یك شیوه برای خط مرزی انتخاب كنید. برای اضافه كردن رنگ به پس زمینه سلول، روی فلش دكمه Fill Color كلیك كنید و از صفحه رنگ، یك رنگ انتخاب كنید. اگر بخواهید رنگ فونت را تغییر دهید، روی فلش دكمه Fill Color كلیك كنید و یك رنگ انتخاب كنید.

 

نکاتی در مورد تنظیم طول وعرض ستون ها در اکسل

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

 

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

 

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

 

 تغییر دادن پهنای بیش از یك ستون  در اکسل

 

برای اینكه چند ستون را به یك اندازه در آورید، با كشیدن ماوس روی عنوان آنها، همه را انتخاب كنید و بعد پهنای یكی از ستون ها را تغییر دهید تا تمام ستون های انتخاب شده به همین پهنا در آیند.

 

 كادر محاوره ای Column Width را باز كنید  

 

برای تعیین كردن پهنای دقیق ستون، آن را انتخاب كنید و از منوی Format ، گزینه Column و بعد گزینه Width را انتخاب كنید تا كادر محاوره ای Column Width باز شود.

 

 اندازه دقیق را مشخص كنید  

 

اندازه پهنای ستون را (برحسب تعداد كاراكترها باتوجه به پهنای فونت) تایپ كنید و روی Ok كلیك كنید.

 

 تغییر دادن اندازه طول سطر با ماوس  

 

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

 

 كادر محاوره ای Row Height را باز كنید  

 

اندازه دقیق طول سطر را نیز می توان مشخص كرد.سطریا سطرهای مورد نظر را انتخاب كنید و از منوی Format ، گزینه Row و بعد گزینه Height را انتخاب كنید. مقدار جدید را وارد كنید (برحسب نقطه. درهر اینچ، 72 نقطه وجود دارد). و روی Ok كلیك كنید تا تغییرات اعمال شوند.

*توجه  *

 بهترین تنظیم در اکسل

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

بیش از یك ستون غیر مجاور

به همان روشی كه ستون های مجاور را تنظیم می كنید (مرحله2) می توانید پهنای ستون های غیر مجاور را نیز تنظیم كنید. برای انتخاب ستون های غیر مجاور، اولین ستون را انتخاب كنید، كلیدCtrl را نگه دارید و بعد ستون‌های دیگر را انتخاب كنید.

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

وقتی كه داده ای را Paste می كنید، ممكن است بخواهید پهنای ستون های اصلی را نیز علاوه بر داده كپی كنید. برای این كار، از منوی Edit ، گزینه Paste و بعد گزینه PasteSpecial را انتخاب كنید. و در كادر محاوره ای كه باز می شود، Column Width را انتخاب كنید و روی Ok كلیك كنید.

 

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

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

 

 

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

 

ابتدا تیترها را انتخاب کنید.

 

سپس به مسیر Home → Alignment → Orientation →Angle Counterclockwise  بروید تا تیترها زاویه 45 درجه در خلاف جهت عقربه ساعت پیدا کنند.

 

 

 

سپس به مسیر Home → Font → Borders →All Borders  رفته تا برای تیترها Border  اختصاص داده شود( البته می توانید به تمام فضای سلول ها Border  اختصاص دهید)

 

 

 

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

 

 

 

 

البته می توانستیم تمام مراحل بالا را در قسمت Format cells و با زدن کلیدهای ترکیبی Ctrl + 1 انجام دهیم.                                                                       

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

 

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

 

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

 

پنهان کردن یک برگه در اکسل

 

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

 

پنهان کردن یک سلول از جدول در EXCEL

 

پنهان شدن یکی از چهار خانه‌های جدول (Cell) مثل یک برگه نیست که آن را پنهان کنید و سپس هر گاه خواستید آن را بازگردانید. اما واقعاً راهی ندارد چون اگر مخفی شود چیزی جای آن قرار نمی گیرد؟
در پایین بیشتر می آموزید اما بدانید شما می‌توانید هر تعداد ردیف یا ستون را که می‌خواهید خالی نمایش دهید در حالتی که آن‌ها واقعاً خالی نیستند.
برای این کار روی یک یا چند سلول راست کلیک کنید و Format Cells را انتخاب کنید.
در بخش Number، گزینه Custom را از پایین کلیک کنید و سه سمیکالن (,,,) بدون پرانتز در فضای Type وارد کنید.
سپس OK را بزنید و در کمال تعجب می‌بینید که همه بخش‌های انتخابی خالی شده اند! با کلیک کردن بر روی آن‌ها همچنان خالی هستند اما در نوار فرمول ها محتوای آن دیده خواهند شد.
برای دوباره نمایان کردن اطلاعات کافی است مراحل بالا را طی کرده و به جای Custom، گزینه Original را انتخاب کنید. دقت داشته باشید اگر در سلول‌ها هر متنی نوشته شده باشد، با زدن اینتر محو خواهد شد. همچنین با تایپ بر روی سلول‌های مخفی، هر چه پنهان کرده‌اند با متن جدید تایپ شده جایگزین خواهند شد.

 

پنهان کردن خطوط

 

 

 

برای پنهان شدن خطوط فاصل، کافی است زبانه VIEW را انتخاب کرده و تیک Gridlines را بردارید. همچنین می‌توانید به Page Layout رفته و تیک View زیر بخش Gridlines را نیز بردارید.

 

پنهان کردن ستون‌ها و ردیف ها

 

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

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

 

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

 

پنهان کردن فرمول ها کمی دشوار تر از بقیه مراحل است. اگر می خواهید فرمولی را پنهان کنید ابتدا باید آن را پنهان کنید و سپس از آن محافظت کنید.
برای این کار ابتدا، بر روی ستون F، راست کلیک کرده و Format Cells را انتخاب کنید.
اکنون روی زبانه Protection کلیک کنید و تیک گزینه Hidden را بزنید. سپس برای محافظت از سلول، بر روی Review کلیک کرده و Protect Sheet را انتخاب کنید.
اگر مایل هستید تا همه نتوانند فرمول های شما را از پنهانی خارج کنند، می‌توانید برای آن گذرواژه ای تعیین کنید. با فشردن دکمه های Ctrl+~ (همان پ بالای تب)  با کلیک بر روی Show Formulas در تب Formulas دیگر قابل مشاهده نخواهند بود.

 

پنهان کردن کامنت ها

 

کامنت ها یا همان اطلاعات اضافی مربوط به هر سلول، به طور خودکار با نوشته شدن،  به صورت یک نشانه قرمز در گوشه راست هر سلول قرار خواهند گرفت. برای تغییر حالت خودکار بر روی  File  و سپس Option،  و زبانه Advanced، روی No comments or indicators کلیک کنید. برای نمایش دادن کافی است Comments and indicators را مجدداً بزنید یا از Show All Comments استفاده کنید.

 

 

اگر هم در یک سلول یک متن بلند بالا نوشتید و به نوعی از اندازه خود خارج تر بود، کافی است روی آن راست کلیک کرده و Format Cells را بزنید. سپس از Alignment، در زیر Horizontal، گزینه Fill را انتخاب کنید. اکنون می بینیم که مشکل طولانی بودن برطرف شده است.

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

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

*توجه *

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

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

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

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

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

چگونگی استفاده ازAutoSum در اکسل

تابع، یك فرمول از پیش نوشته شده در برنامه است. آرگومان های (متغییرهای) مورد نیاز به تابع داده می شود و تابع، نتیجه را محاسبه می كند. برای تابعSUM ، تنها چیزی كه باید مشخص كنید محدوده ای است كه می خواهید جمع كنید نوار ابزارStandard ، امكان دستیابی سریع به SUM و نیز توابع پر استفاده دیگر مانند AVERAGE (محاسبه معدل)، MIN (كوچكترین عدد)، MAX (بزرگترین عدد) وCount (محاسبه تعداد آیتم ها) را فراهم می كند. در كار عملی16 «چگونه از توابع استفاده كنیم» یاد می‌گیرید كه چگونه از توابع دیگر استفاده كنید.

 

 یك محل برای نتیجه، انتخاب كنید  

 

سلولی كه می خواهید نتیجه تابع در آن ظاهر شود را انتخاب كنید (معمولاً در پایان یك سطر یا ستون از اعداد).

 

 روی AutoSum كلیك كنید  

 

در نوار ابزار Standard ، روی دكمه AutoSum كلیك كنید.

 

 فرمول SUM درج می شود  

 

دكمه AutoSum، تابع SUM را درج می كند و یك خط چشمك زن، پیرامون سلول هایی كه با هم جمع می شوند قرار می دهد. یك Screen Tip زرد رنگ، آرگومان های مورد استفاده در تابع SUM را لیست می كند.

 

 درج داده ها را تكمیل كنید  

 

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

 

 با استفاده از AutoSum ، معدل حساب كنید  

 

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

 

 ورود اطلاعات را كامل كنید  

 

یك نوار چشمك زن، دور محدوده ای كه Excel فكر می كند می خواهید در این تابع استفاده كنید را فرا می گیرد. اگر لازم باشد با كشیدن ماوس، محدوده دیگری را انتخاب كنید وبعدEnter را بزنید. نتیجه در سلول مورد نظر نشان داده می شود و فرمولی كه برای محاسبه این نتیجه بكار رفت در نوار فرمول ظاهر می شود.

*توجه  *

 جمع كردن كل سلول های یك جدول در excel

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

 

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

برای جمع كردن مقادیر بیش از یك محدوده، روی دكمه AutoSum كلیك كنید، محدوده اول را انتخاب كنید، كلید كاما (,) را بفشارید و بعد محدوده بعدی را انتخاب كنید. بین تمام محدوده های بعدی هم باید كاما تایپ كنید.

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

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

 

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

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

به تب FORMULAS رفته و show formulas را کلیک کنید.

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

به تب Home بروید وقسمت Format در منوی کشویی Format Cells را کلیک کنید.

پنجره Format Cells باز می شود و برای مخفی کردن آن به تب Protection بروید و تیک Hidden بزنید و دکمه OKرا کلیک کنید.

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

پنجره Protect Sheet باز می شود و اگر می خواهید کاربران به فرمول ها دسترسی کند و می خواهید آن فرمول رمز گذاری کنید شما باید در قسمت Password to unprotect sheet رمز عبور را تعیین کنید و دکمه OK را کلیک کنید.

رمز عبور را دوباره وارد کنید و دکمه OK را کلیک کنید.

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

برای نشان دادن فرمول در نوار فرمول دیگر،قسمت Format در منوی کشویی Unprotect Sheet را کلیک کنید.

پنجره Unprotect Sheet باز می شود و رمز عبور را وارد کنید.

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

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

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

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

 

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

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

 

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

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

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

 

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

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

 

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

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

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

 

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

 

ویژگی مرتب (sort) کردن در برنامه Excel این محدودیت را دارد که اجازه مشخص کردن بیش از سه داده برای مرتب کردن (سورت کردن) را نمی دهد. در بیشتر موارد همین سه داده کافیست اما شرایطی پیش می آید که امکان مرتب کردن با بیش از سه داده بسیار مفید خواهد بود. در این قسمت راهی برای دور زدن این محدودیت به شما نشان داده می شود.
برای این مثال، فرض می کنیم داده های مرتبطی در ستون های A، B، C، D و E داریم و می خواهیم آن ها را ابتدا بر طبق ستون A، سپس B، سپس C، سپس D و پس از آن E مرتب کنیم. برای انجام این کار، لازم است امکان سورت کردن وارونه (Backward) را داشته باشیم. به عبارت دیگر، ابتدا بر طبق آخرین مورد سورت کرده و سپس به ترتیب تا اولین داده سورت می کنیم.
ستون های A تا E را انتخاب کرده و سپس مسیرData → Sortرا دنبال کنید. سورت کردن را با این ترتیب مشخص کنید که ابتدا ستون C، پس از آن D و سپس E مرتب شوند. روی sort کلیک کنید. اکنون ستون های A تا E را انتخاب کرده و مسیر Data → Sort را دنبال کنید. این بار ابتدا ستون A و سپس ستون B را مرتب کنید. روی Sort کلیک کرده و پس از آن همه چیز مرتب می شود. با این کار برنامه Excel به جای سه مورد داده، مرتب کردن را با پنج مورد داده انجام می دهد.

اگر می خواهید این کار به صورت خودکار انجام شود، می توانید از یک ماکرو استفاده کنید که هم داده های انتخابی را سورت کرده و هم بر اساس فرمت کردن ردیف اول ستون های انتخاب شده، تخمین می زند که داده های شما دارای عنوان ستون هستند یا نیستند. اگر عنوان ها Bold باشند، برنامه Excel تشخیص می دهد که آن ها عنوان ستون ها هستند و مرتبشان نمی کند. در عوض، از اولین ستون سمت چپ تا آخرین ستون سمت راست، تا 256 ستون، مرتب می شود. کُد ماکرو مورد نیاز شما باید در مدول استاندارد قرار داده شود. برای رفتن به این قسمت، مسیر Tools → Macro → Visual Basic Editorرا دنبال کرده (Alt/Options-F11)، سپس Insert → Module را انتخاب و کد زیر را وارد کنید:
 Sub SortByX()
Dim l As Long
For l = Selection.Columns.Count To 1 Step -1
    Selection.Sort Key1:=Selection.Cells(2, l), _
     Order1:=xlAscending, Header:=xlGuess, Orientation:=xlTopToBottom
Next l
End Sub
برای برگشتن به فایل، روی آیکون Excel کلیک کرده یا Alt+Q را فشار دهید. با یک بار وارد کردن این کد می توانید در هر بار استفاده از Excel، تعداد ستون بیشتری از حد استاندارد را مرتب کنید.

مرتب کردن تصادفی (Random)در اکسل
شما می توانید با استفاده از برنامه ی Excelسه برندهیکم، دوم و سوم را به طور تصادفی (random) و از یک لیست دلخواه در صفحه ی گسترده ی خودتان انتخاب کنید. آسان ترین و بهترین روش برای این کار استفاده از تابع RAND و ترکیب آن با امکانات مرتب کردن (سورت کردن) است.
فرض کنیم یک جدول سه ستونی در صفحه گسترده ی خود دارید که از ستون Bشروع شده و به ترتیب شامل نام، سن و شماره شناسنامه است. می توانید تابع RAND در اکسل را در خانهA2 قرار داده و در خانه های پایینی آن به تعداد دلخواه، تا پایان جدول، کپی کنید. به محض انجام این کار، هر خانه در ستون A دارای تابع RAND خواهد بود که به شکل خودکار (اتوماتیک) یک عدد تصادفی (رندوم) را بر می گرداند که با استفاده از آن می توان جدول را سورت کرد. به عبارت دیگر، می توان ستون های A، B، C و D را به ترتیب صعودی یا نزولی و با استفاده از ستون A، مرتب کرد که به این ترتیب سه داده اول، سه برنده ی مورد نظر خواهند بود.
تابع RAND یک تابع به اصطلاح نا پایدار است که با انجام هر تغییری در فایل، به عنوان مثال وارد کردن داده ها در هر قسمت یا تکرار یک سری محاسبات با فشار دادن F9، محاسبات را دوباره انجام می دهد. پس بهتر است قبل از هر کار دیگر، برنده ها را در جایی دیگر یادداشت کنید.
البته می توانید از این خصوصیت تابع RAND به نفع خودتان استفاده کرده یک ماکرو را ثبت کنید که داده هایی را بی درنگ بعد از هر محاسبه مجدد، مرتب کرده  و تابع RAND را مجبور کند تا به مجموعه دیگری از اعداد رندوم برگردد. پس از آن شما می توانید این ماکرو را به یک دکمه پیوست (attach) کرده و هر بار که بخواهید سه برنده را نشان دهید، فقط باید روی همان دکمه کلیک کرده و سه نام بالایی را انتخاب کنید.
برای مثال، فرض کنیم در ستون های B، C و D داده هایی داریم و در ردیف 1، عنوان های ستون ها را قرار داده ایم. در ابتدا، عنوان RAND را در خانهA1 قرار می دهیم. عبارت =RAND() را در خانهA2 وارد کرده و به تعداد نیاز در خانه های پایین آن کپی می کنیم. سپس هر خانه را انتخاب و مسیر Developer → Code → Record Macro (در نسخه های قبل از 2007، مسیر Tools → Macro → Record New Macro …) را دنبال می کنیم.
ستون های A، B، C و D را انتخاب کرده و F9 را فشار می دهیم (برای محاسبه ی مجدد در Mac از علامت Office استفاده کنید). با دنبال کردنSort & Filter options → Data → Sort، داده ها بر طبق ستون A مرتب می شوند. پس از آن باید ثبت کردن ماکرو را متوقف کنیم.
سپس، مسیر Control Options → Developer → Insert (برای نسخه های قبل از 2007، مسیر View → Toolbars → Forms) را دنبال می کنیم. یک دکمه را از نوار ابزار Forms انتخاب کرده و در یک جای دلخواه صفحه (worksheet) قرار می دهیم. ماکرویی که ثبت کرده ایم را به این دکمه اختصاص داده و روی OK کلیک می کنیم (اگر می خواهید می توانید نام دکمه را از Button 1 به هر نام دیگری تغییر دهید).
می توانید ستون A را انتخاب و آن را مخفی (Hide) کنید. لزومی ندارد که هر کاربری این ستون را ببیند. هر بار که روی این دکمه کلیک کنید، داده های شما به طور تصادفی مرتب شده و تنها کافیست مطابق شکل 6، سه داده ی بالایی (برنده ها) را بخوانید.
 تابع RAND در Excel 2003 و Excel 2007 یک ایراد بزرگ دارد. اگرچه در فایل Help این برنامه ها به روشنی بیان شده که عدد تصادفی برگشت داده شده بین 0 و 1 خواهد بود، همیشه در مواردی که این تابع برای چندینخانه استفاده می شود، چنین نیست. در برخی موارد تابع RAND عددی کمتر از 0 را بر می گرداند. در سایت http://support.microsoft.com/default.aspx?kbid=828795، دلیل Microsoft برای تغییر الگوریتم آورده شده است.
دستکاری (Manipulate) داده ها با فیلتر پیشرفته (Advanced Filter)
اگر با ابزار AutoFilter برنامه ی Excel آشنایی دارید، با محدودیت های آن نیز آشنا هستید. اگر به دستکاری داده ها در مقیاس وسیعی نیاز دارید، استفاده از ابزار فیلتر پیشرفته (Advanced Filter)، همان راهی است که باید انتخاب کنید.
با تمام محدودیت ها، AutoFilters یک راه مناسب برای نمایش داده هایی است که در یک محدوده ی مشخص قرار دارند (در معیارهای مشخصی صدق می کنند). اگرچه در برخی موارد نمی توانید با استفاده از امکانات استاندارد AutoFilters، اطلاعات مورد نظرتان را جمع آوری کنید. ابزار Advanced Filter که یکی از امکانات متنوع و چند کاره در Excel است، امکان دستکاری بیشتر در داده ها را برای شما فراهم می کند. وقتی از ابزار Advanced Filter استفاده می کنید، جدول شماباید در فرمت جدول کلاسیک تنظیم شده باشد.
برای استفاده از ابزار Advanced Filter لازم است یک کپی از عنوان های ستون های جدول خود را در جایی بالای داده ها داشته باشید. همیشه باید دست کم سه ردیف خالی بالای جدول داده ها داشته باشید. برای اطمینان از اینکه عنوان های شما به درستی یکسان بوده و بدون توجه به تغییر عنوان های ستون ها یکسان باقی می مانند، همیشه عنوان ستون ها را با یک فرمول مرجع ساده مانند =A4 که در آن A4 شامل یک عنوان ستون است، ارجاع بدهید. این فرمول را به تعداد عنوان های ستون موجود در جدول خود کپی کنید. این فرمول نویسی ساده شما را از پویا (دینامیک) بودن عنوان ها در فیلتر پیشرفته مطمئن می کند. درست زیر این عنوان ها، معیارهای استفاده از فیلتر پیشرفته را قرار دهید. برای اطلاع بیشتر به قسمت Advanced Filters Criteria در Excel Help مراجعه کنید.
در زمان کار با فیلتر پیشرفته به خاطر داشته باشید که برای دو یا چند معیاری که به طور مستقیم در زیر عنوان قرار گرفته اند باید از OR استفاده کرد. اگر می خواهید از AND استفاده کنید، عنوان ستون ها و معیار باید دو بار، پهلو به پهلو، ظاهر شوند. شکل 7 چگونگی استفاده از عملگر OR و شکل 8 چگونگی استفاده از عملگر AND برای فیلتر کردن داده ها را نشان می دهد.
استفاده ی ساده ای از فیلتر پیشرفته در هر دو مثال نشان داده شده است و در هر دو مورد می توان به جای فیلتر پیشرفته از AutoFilter استفاده کرد. در ادامه مثال هایی زده می شوند که در آن ها امکان استفاده از AutoFilter وجود ندارد و باید از فیلتر پیشرفته استفاده شود.
توجه به این نکته مهم است که در زمان استفاده از یک فرمول برای معیار، نباید در بالای معیار از یک عنوان یکسان با داده های داخل جدول استفاده کرد. برای مثال اگر لیستی از داده های عددی در ستون A دارید که از خانه A5 شروع می شود (یعنی خانهA4 عنوان ستون است) و می خواهید تمام عدد های بیشتر از متوسط را از آن خارج کنید، باید از معیاری مانند:
 =A5>AVERAGE($A$5:$A$500)
استفاده کنید. اگر معیار در خانهA2 قرار گرفته، محدوده ی داده عبارت از $A$1:$a$2 است اما $A$1 نمی تواند شامل عنوان یکسانی با آنچه برای یکی از لیست ها استفاده شده، باشد. یا باید خالی بوده یا به کلی متفاوت باشد.
همچنین توجه به این نکته نیز اهمیت دارد که تمام فرمول های مورد استفاده باید بازگشت TRUE یا FALSE داشته باشند. محدوده ی تابع متوسط با اضافه کردن علامت دلار ($) قطعی می شود در حالی که ارجاع به خانه A5، یک مرجع وابسته (نسبی) است. توجه به این نکته ضروری است چرا که در زمان استفاده از فیلتر پیشرفته (Advanced Filter)، برنامه Excel، خانهA5 را یک مرجع نسبی در نظر گرفته و لیست را در هر زمان به اندازه ی یک ورودی پایین برده و آن راTRUE یا FALSE نشان می دهد. اگر TRUE برگردانده شود، متوجه می شود که باید نشان داده شود اما اگر FALSE برگردد، در معیار مورد نظر قرار نگرفته پس نشان داده نمی شود.
فرض کنیم چند نام در محدوده ی SAS3:$A$500 و با عنوان های شروع شده از A4 تکرار شده اند. همچنین فرض کنیم تعدادی از عنوان ها نیز چند بار تکرار شده اند. برای جدا کردن نام هایی که بیشتر از یک بار تکرار شده اند، باید از Advanced Filter و فرمول زیر به عنوا معیار استفاده کنید:
 =COUNTIF($A$5:$A$500,A5)>1
با استفاده از Advanced Filterو گزینه ی Copy to Another Location، لیستی که تازه ایجاد شده، شامل تمامنام هایی خواهد بود که بیشتر از یک بار در لیست اصلی تکرار شده اند (شکل 9 را ببینید). تعداد زیادی از این نام ها چندین بار تکرار شده اند اما می توانید به سادگی این لیست جدید را با استفاده ی مجدد از Advanced Filter، فیلتر کرده و این بار ازUnique Records Onlyاستفاده کنید (شکل 10 را ببینید). این بار نام هایی به شما نشان داده خواهد شد که بیش از یک بار در لیست آمده اند.
کسانی که از Advanced Filter استفاده می کنند این سوال را مطرح می کنند که چگونه می توان Excel را مجبور کرد که داده ها را بدون هیچ کم و کاستی با معیاری که آن ها مشخص کرده اند، فیلتر کند. اگر معیار شما Dave باشد و از Advanced Filter روی یک لیست طولانی استفاده کنید، Excel در کنار اسم Dave، اسامی مانند Davey، Dave J، Dave K و غیره را نیز نشان می دهد. به بیان دیگر، هر اسمی که با Dave شروع شود در این معیار قرار می گیرد. برای اینکه Excel مجبور به پیدا کردن عبارت دقیق، در این مثال فقط نام Dave، شود باید از معیار =”=Dave” استفاده کنید.

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

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

تکنیک اول:

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

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

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

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

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

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

از قسمت   Select a rule type

عبارت

  Use a formula to determine which cells to format

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

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

نکاتی در مورد مقادیر خطا در اکسل

کاربرگ هایی که در کاربردهای عملی با آن ها رو به رو خواهید شد . معمولا بسیار بزرگ بوده و در صورت مواجه شدن با این کاربرگ ها با حجم بسیار بزرگتری از داده ها سرو کار خواهید داشت . در چنین مواردی تشخیص این که یک فرمول به کدام خانه ارجاع دارد یا یک خانه در کدام فرمول مورد استفاده قرار گرفته است . می تواند کارمشکلی باشد .گاهی اوقات بعد از درج یک تابع در خانه ای از خانه های کاربرگ . به جای نمایش رقم علایمی نظیر ###,#DIV,#REF,#VALUE,#NAME را مشاهده می کنید . این علایم مشخص می کنند که برنامه اکسل به دلیلی نمی تواند نتیجه محاسبه خود را در این خانه نمایش دهد.
خطاهای اکسل و ویرایش توابع
به محض اینکه شما نحوه کارکرد فرمول ها در اکسل را فرا گرفتید . شما این نیاز را پیدا خواهید کرد که تصحیح کردن و پوشش دادن خطاهایی را که در نتیجه محاسبه نادرست فرمول ها ایجاد می شود را هم فرا بگیرید.یکی از مهم ترین و ابتدایی ترین نیاز شما در مواجه شدن با مقادیر خطا این است که خطای ایجاد شده چه معنا و مفهومی دارد با فهم نوع خطا بر طرف کردن آن به مراتب آسان تر خواهد بود .
#Null
این خطا اغلب زمانی اتفاق خواهد افتاد که شما یک دامنه متقاطع را انتخاب کنید ولی در واقع در دامنه متقاطع ایجاد شده هیچ خانه مشترکی وجود نداشته باشد .
نکته : دامنه های متقاطع به دامنه هایی گفته می شود که یک یا چندین خانه مشترک در آن ها وجود داشته باشد . برای درک بیشتر فرمول زیر را در یکی از سل های اکسل وارد کنید .خواهید دید که سل B2 یک سل مشترک در هر دو دامنه می باشد .
=A1:F1 B1:B10
در فرمول بالا سل B2 یک سل مشترک در هر دو محدوده خواهد بود واگر این فرمول را در سل A30 وارد کرده باشید مقدار موجود در سل B2 در سل A30 برگردان خواهد شد . حال دقت کنید اگر به جای فرمول بالا از فرمول زیر استفاده کنید .اکسل مقدار خطای #Null را نشان خواهد داد .
=A1:F1 B2:B10

دلیل نشان دادن مقدار خطا در فرمول بالا این است که هیچ محل تقاطع یا خانه مشترکی در بین دو محدوده A1:F1 با B2:B10 وجود ندارد .

#DIV/0!
این خطا زمانی اتفاق خواهد افتاد که یک مقدارعددی تقسیم بر عدد صفر و یا تقسیم بر سلی شده باشد که هیچ مقداری در آن خانه وجود نداشته باشد . برای جلوگیری از به وجود آمدن این گونه خطاها در محاسبات کاربرگ شما می توانید از چندین تابع استفاده کنید .
۱- استفاده از تابع IF :
=IF(A2=0,0,=A1/A2)
مفهوم فرمول بالا بدین معناست که اگر مقدار عددی موجود در خانه A2 برابر با صفر بود عدد صفر برگردان خواهد شد و اگر مقدار عددی موجود در خانه A2 برابر با صفر نبود مقدار سلول  A1 بر سلول A2 تقسیم خواهد شد .
۲- استفاده از تابع Error.type در excel :
=IF(ERROR.TYPE(A1/A2)=2,0,A1/A2)
نکته مهمی که در مورد نحوه استفاده از این تابع باید به آن توجه کنید انتخاب نوع خطایی است که از لیست خطاها باید آن را انتخاب کنید .
۳- استفاده از تابع IsError  در اکسل :
=IF(ISERROR(A1/A2),0,A1/A2)

یا
=IF(ISERR(A1/A2),0,A1/A2)
#VALUE
یکی از تکراری ترین نوع خطاهای موجود به احتمال زیاد همین مقدار خطا می باشد . این خطا زمانی اتفاق خواهد افتاد که در محاسبات فرمول ها به رشته متنی ارجاع داده شود . به عنوان مثال یک سل که حاوی مقدارعددی می باشد تقسیم یا ضربدر یک سلی شود که حاوی رشته متنی باشد .
#REF
این نوع خطا زمانی اتفاق خواهد افتاد که دامنه ای از خانه های کاربرگ که در فرمول ها به آن ارجاع داده شده است حذف شده باشد . مثل حذف کردن ستون ها . ردیف ها .
#خطای  NAME در اکسل
این خطا زمانی اتفاق خواهد افتاد که شما در فرمول ها ی کاربرگ از تابعی استفاده کرده باشید که آن تابع در اکسل شناسایی نشده باشد . که اکثرا به دلیل تایپ اشتباه نام تابع اتفاق خواهد افتاد .برای جلوگیری از ایجاد این خطا سعی کنید همیشه نام توابع را با حروف کوچک تایپ کنید اکسل بعدا به صورت اتوماتیک ان ها را به حروف بزرگ تبدیل خواهد کرد .
دومین دلیل اتفاق اقتادن این خطا وارد کردن اسمی در فرمول است که این اسم در فهرست کادر محاوره ای Define Name وجود ندارد . این امکان وجود دارد که نام را غلط تایپ کرده باشید و یا یک نام حذف شده را تایپ نموده باشید .
سومین دلیل اتفاق افتادن این نوع خطا زمانی می باشد که شما از توابع سفارشی استفاده کنید ولی این توابع که باید به صورت Add-in بر روی اکسل نصب شده باشند را نصب نکرده باشید .
#NUM
این خطا زمانی اتفاق خواهد افتاد که شما از یک مقدار نامناسب در آرگومان های توابع استفاده کنید . به عنوان مثال استفاده از یک عدد منفی زمانی که باید از یک عدد مثبت استفاده کنید و یا استفاده از علامت های (%,$) با همراه مقادیر عددی .
#N/A
یکی از مهم ترین دلیلی که این خطا اتفاق خواهد افتاد استفاده از توابع Lookup یا توابع جستجو خواهد بود . بدین معنا که اکسل نمی تواند مقدار مورد نظر شما را پیدا کند .

مطالبی پیرامون چگونگی تغییر رنگ خطوط راهنمای اکسل

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

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

 چگونگی تغییر رنگ خطوط راهنمای اکسل

برای تغییر رنگ خط های راهنما (Gridlines) ابتدا بر روی تب "File" کلیک کنید.

 

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

 

 

پنجره ای به اسم “Excel Options” باز می شود. بر روی بخش “Advanced” در لیست سمت چپ کلیک کنید.

 

 

 

در تنظیماتی که باز می شود به قسمت “Display options for this worksheet” بروید. بر روی دکمه ی کنار “Gridline color” کلیک کرده و رنگ دلخواه خود را انتخاب نمایید. همچنین توجه داشته باشید که تیک گزینه ی “Show gridlines” فعال باشد.

 

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

 

 

 

خطوط راهنمای کاربرگ شما با رنگی که خودتان انتخاب کرده اید ظاهر می شود..

 

 

 

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

 

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

در نرم‌افزار Microsoft Excel امکان Copy و Paste کردن سلول‌ها در محیط نرم‌افزار به آسانی وجود دارد. اما فرض کنید قصد دارید سلول‌ها را در محیط یک‌ نرم‌افزار دیگر نمایش دهید. با ما همراه باشید

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

 

 کپی کردن سلول ها به عنوان عکسل در اکسل

• ابتدا فایل مورد نظر خود را در Microsoft Excel فراخوانی کنید.
• سپس سلول‌های مورد نظر خود را به حالت انتخاب دربیاورید.
• اکنون در تب Home، بر روی فلش کنار دکمه‌ی Copy کلیک کنید.
• حالا در منوی باز شده بر روی Copy as Picture را انتخاب کنید.

 

• در پنجره‌ی باز شده از فعال بودن گزینه‌های As show on screen و Bitmap اطمینان حاصل کرده و سپس بر روی دکمه‌ی OK کلیک کنید.
• با این کار تصویر سلول‌های انتخابی به Clipboard کپی شده است.
• اکنون می‌توانید درون نرم‌افزار مورد نظر خود (نظیر Paint یا Microsoft Word)، این تصویر را Paste کنید.

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

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

۱- با استفاده از کد تغییر فرمت در اکسل

سلول یا سلول‌هایی که می‌خواهید اعداد درون آن‌ها فارسی شود را انتخاب کنید و سپس راست کلیک کرده و گزینه Format Cells را انتخاب کنید تا پنجره Format Cells باز شود. در قسمت Custom، یکی از کدهای زیر را وارد کنید و روی Ok کلیک کنید.

 

۲- با استفاده ماکرو نویسی در اکسل

 

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

سپس  کلید F5 را فشار دهید. پس از این کار پنجره Tarfandha-En2Faظاهر می‌شود.

 

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

 

• تبدیل اعداد فارسیبه انگلیسی در excel :

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

 

۱- با استفاده از کد تغییر فرمت

سلول یا سلول‌هایی که می‌خواهید اعداد درون آن‌ها انگلیسی شود را انتخاب کنید و سپس راست کلیک کرده و گزینه Format Cells را انتخاب کنید تا پنجره Format Cells باز شود. در قسمت Custom، ابتدا بررسی کنید که هیچ کدی مشابه کدهای حالت قبل قرار نداشته باشد. اگر قرار دارد آن را پاک کنید و یا حالت General را انتخاب کنید. اگر پس از پاک کردن اعداد همچنان فارسی هستند در قسمت Custom، کد زیر را وارد کنید و روی Ok کلیک کنید.

 

۲- با استفاده ماکرو

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

سپس  کلید F5 را فشار دهید تا پنجره Tarfandha-Fa2Enظاهر شود. مشابه حالت قبل محدوده مورد نظرتان را انتخاب کنید و روی Ok کلیک کنید تا تغییرات انجام شود.

 

توجه: اعداد درون کد ماکرو در واقع کد یونیکد اعداد فارسی و انگلیسی هستند

مطالبی در مورد شماره گذاری صفحه از عدد دلخواه و ابزار paste picture link در اکسل

تمامی تنظیمات مربوط به چاپ در اکسل در پنجره Page Setup قرار دارند و برای انجام هر تغییری در این تنظیمات ،از جمله شماره گذاری صفحه ها در اکسل ابتدا پنجره Page Setup را باز می کنیم .

 

 

سپس در برگه Header/Footer دکمه Custom Footer را برای درج شماره در پایین صفحهات کلیک می کنیم.

 

برای درج شماره در بخش دلخواه از فوتر (چپ، وسط، راست) داحل بخش مورد نظر کلیک کرده و روی دکمه Insert Page Number کلیک می کنیم.

 

مشاهده خواهید کرد که شماره درج شده از ۱ شروع می شود.

اگر می خواهید شماره گذاری از عدد دیگر شروع شود (مثلا ۵) کافیست که در برگه Page از پنجره Page Setup و در کادر First Page Number مقدار مورد نظر خود را بنویسید.

 ابزار paste picture link در اکسل                                                                                               paste picture link در اکسل ابزاری بسیار جالب و پر استفاده جهت حفاظت از محتوای فایل اکسل می باشد.

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

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

سپس در روبان Home دکمه Paste را باز کرده و در زیر منوی as picture گزینه paste as picture را انتخاب کنید.

 

 

مشاهده خواهید کرد که یک تصویر تولید می شود که دقیقا محتوای کادر انتخاب شده را نمایش می دهد و با تخییر در محتوای اصلی، این تصویر نیز تغییر می کند.

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

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

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

 

روش مخفی کردن:

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

سپس از سربرگ Home به کادر گروه Cells مراجعه کرده و بر روی گزینه Format کلیک کنید و از کادر باز شده آبشاری آن گزینه Hide & Unhide را انتخاب نمایید. با کلیک بر روی این گزینه، کادری باز خواهد شد که گزینه‌های آن و کارکرد هرکدام عبارتند از:

 

• گزینه های مربوط به مخفی کردن سطر، ستون و برگه انتخاب شده :

Hide Rows: با انتخاب این گزینه سطر(های) انتخاب شده به حالت مخفی تغییر می‌کنند. البته پس از انتخاب سطر(های) مورد نظرتان، با راست کلیک کردن هم می‌توانید گزینه Hide را مشاهده کنید و پس از کلیک بر روی آن، سطر و یا سطرهای انتخاب شده را مخفی کنید.

 

Hide Columns: با انتخاب این گزینه ستون‌ (های) انتخاب شده به حالت مخفی تغییر می‌کنند. مشابه حالت قبل گزینه Hide را منوی راست کلیک نیز می‌توانید مشاهده کنید.

Hide Sheet: با انتخاب این گزینه قادر خواهید بود تا برگه سند (صفحه اکسل) انتخاب شده را به حالت مخفی درآورید.

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

---

روش آشکار کردن:

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

• گزینه های مربوط به آشکار کردن سطر ستون و برگه پنهان شده :

Unhide Rows: این گزینه برای آشکار کردن سطر (های) پنهان شده کاربرد دارد.

Unhide Columns: از این گزینه برای آشکار کردن ستون (های) انتخاب شده استفاده می‌گردد.

...Unhide Sheet: با انتخاب این گزینه قادر خواهید بود تا برگه پنهان شده را آشکار سازید.

• آشکار کردن برگه‌ی مخفی شده در اکسل:

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

 

اما اگر برگه‌ای مخفی نشده باشد گزینه Unhide Sheet حالت غیر فعال دارد.

 

• آشکار کردن ردیف یا ستون مخفی شده در اکسل :

اگر می‌خواهید که تمام ردیف‌ها یا ستون‌های مخفی شده در یک برگه آشکار شوند کافیست با استفاده از شرتکات Ctrl A و یا با کلیک روی دکمه Select All، کل برگه را انتخاب کنید.

 

سپس با کلیک روی گزینه‌های Unhide Rows و Unhide Column تمام ردیف‌ها یا ستون‌های مخفی شده در برگه‌ی فعال آشکار می‌شوند. اما اگر می‌خواهید ردیف یا ستون مشخصی که مخفی شده است را آشکار کنید شرایط کمی متفاوت است.

اگر ستون یا ستون‌های مخفی شده به غیر ستون اول (ستون A) باشد مثلا ستون B، حال با انتخاب ستون‌های قبل و بعد از آن یعنی ستون‌های A و C و سپس انتخاب گزینه Unhide Columns ستون یا ستون‌های مخفی شده آشکار می‌شوند. البته از طریق منوی راست کلیک هم می‌توانید گزینه Unhide را انتخاب کنید.

 

اما اگر ستون مخفی شده ستون اول باشد چون قبل از آن ستونی وجود ندارد نمی‌توان آن را انتخاب کرد. برای حل این مشکل در قسمت نشان داده شده (Name Box) عبارت A1 را تایپ  فعال شود Unhide Columns را انتخاب کنید.

 

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

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

 

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

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

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

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

=LOOKUP (lookup_value, lookup_vector, result_vector)

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

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

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

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

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

 

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

---

۲- تابع VLOOKUP:

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

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

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

---

۳- تابع HLOOKUP:

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

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

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

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

چند کابرد ساده از فرمول در اکسل :
فرض کنید شما لیستی 8 تایی از اسامی دارید حال میخواهید بدانید هشتمین آیتم از این لیست چه نامی است ، کافی است فرمول زیر را بنویسید


=INDEX(list, 8)
فرض کنید در ستون سوم از این لیست شماره تلفن وارد شده باشد می خواهید شماره تلفن هشتمین نفر از لیست را بدانید


=INDEX(list, 8,3)

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


INDEX(range or table, row number, column number)


در این ترکیب با دادن شماره سطر و ستون یک ناحیه می توان به رفرنس یا مقدار دست یافت


INDEX(range, row number, column number, area number)


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



دلیل اول : بدست آوردن n امین داده
این فرمول بهترین و ساده ترین فرمول برای این کار است کافیست بنویسیم:


=index(list;n)

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


=index(list;n;m)


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


=average(age column)

همچنین می توانید از فرمول زیر نیز استفاده کنید سن افراد در ستون 5 جدول قرار دارد می توان نوشت:


=average(index(list; ;5))


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

دلیل چهارم: جستجو به سمت چپ
می دانیم که استفاده از فرمول vlookup امکان جستجو در خلاف جهت را به ما نمی دهد اما با ترکیب فرمول های index&match می توان به این مشکل فائق آمد ، در مثال فوق می خواهیم بدانیم کدام فرد سنگین ترین وزن را دارد.

=INDEX(List[F-Name];MATCH(MAX(List[weight]);List[weight];0);1)


دلیل پنجم: ایجاد محدوده داینامیک در Excel

تا کنون با برخی از توانایی های تابع index آشنا شده اید که به نظر ساده بوده است ، حال به برخی کاربرد ها پیچیده تر این تابع می پردازیم ، توانایی واقعی index در ماهیت آن نهفته است شما در ظاهر می بینید که index مقداری را نمایش می دهد اما در واقع این تابع شما را به سلولی که حاوی آن مقدار است ارجاع می دهد به عنوان مثال وقتی می نویسد index(list;8) شما 8 امین مقدار از لیست را می بینید اما در واقع شما به سلول حاوی هشتمین مقدار ارجاع داده شده اید پس حاصل این تابع یک ارجاع است و هرگاه شما نیاز به ارجاعی داشته باشید می توانید از تابع استفاده کنید .
کمی گیج کننده است ، با مثال هایی به شرح موضوع می پردازیم :
شما وقتی می خواهید ناحیه ای مانند A1:A10 را جمع بزنید از sum(A1:A10) استفاده میکنید در این فرمول a1 , a10 رفرنس هستند حال به این فرمول توجه کنید :
=sum(A1:index(A1:A50;10))


جواب ها در هر دو یکسان است اما در دومی index از محدوده a1 تا a50 شما را به 10 خانه اول ارجاع می دهد.

مثال1 : متوسط قد X نفر اول :
فرض کنید می خواهید متوسط قد x نفر اول افراد لیست را بیابید ، x عددی متغیر است در این صورت فرمول زیر را خواهیم داشت:


=AVERAGEA(G4:INDEX(List[length];K7))


مثال 2 : متوسط قد افراد در یک لیست داینامیک
گاهی شما میخواهید متوسط قد همه افراد لیست را داشته باشید اما نمیدانید این افراد چند نفر هستند در این صورت هر بار که داده ای اضافه می شود باید فرمول ها را بروز کنید اما چگونه می توان از فرمول های خودکار استفاده کرد، یکی از راه ها استفاده از فرمول offset است :


=offset(A1;0;0;counta(A:A);1)


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


=A1:index(A:A;counta(A:A))


دلیل ششم : ارجاع به محدوده خاصی از چندین محدوده
سه لیست یا محدوده مجزا دارید list1,list2,list3 میخواهیم متوسط داده های این سه لیست را بدست آوریم لذا از ترکیب دوم تایع index استفاده مکنیم



=AVERAGE(INDEX((list1;list2;list3);;;D2))



(list1;list2;list3) نشان دهنده کل محدوه ها و d2 نشانگر شماره محدوده مورد نظر است.


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

ماهیت تابع index به گونه ای است که بدون استفاده از CTRL+SHIFT+ENTER داده های آرایه ای را پردازش کند ، به عنوان مثال شما می توانید متوسط سنی افرادی که نام آنها با حرف F شروع شده بیایبد


=SUM(INDEX(((LEFT(List[F-Name];1))="F")*(List[Age]);0))

نکاتی پیرامون چگونگی تغییر طول و عرض سلول ها را در اکسل

بصورت پیش فرض هنگامیکه یک سند اکسل جدید باز می کنید، عرض تمام ردیف ها و طول تمام ستون ها با یک مقدار مشخص نمایش داده می شود. برای مثال در نسخه های جدید اکسل طول ستون ها ۱۵ ، عرض ردیف ها ۸٫۳۸ و فونت پیش فرض نیز Calibri و با شماره ۱۵ می باشد. با تغییر نوع و اندازه فونت، طول و عرض سلول ها نیز متناسب با آن تغییر خواهد کرد. با این حال راه های مختلفی نیز برای تنظیم این طول و عرض بصورت دلخواه وجود دارد که در ادامه ی مطلب به آنها اشاره خواهیم کرد. پس با مودمها همراه باشید.

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

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

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

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

هم اکنون بر روی ناحیه انتخاب شده کلیک راست کرده و گزینه ی Row Height را انتخاب نمایید. به جای کلیک راست همچنین می توانید از کلیدهای میانبر Shift+F10 نیز استفاده نمایید.

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

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

مقدار طول موردنظر خود را وارد کرده و سرانجام بر روی دکمه ی OK کلیک کنید.

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

همچنین یک گزینه ی دیگر نیز وجود دارد که می توانید طول ها را بصورت خودکار تعیین نماید. این اندازه ها با توجه به نوع و اندازه فرمتی که تعیین کرده اید تنظیم می شوند و اهمیتی هم ندارد که سلول های سند شما خالی باشند یا خیر.
بدین منظور می توانید پس از انتخاب ستون های موردنظر بر روی دکمه ی Format کلیک کرده و در داخل منوی Cell Size گزینه ی AutoFit Column Width را انتخاب نمایید. اگر یکی از سلول های ستون انتخاب شده نیز دارای داده ای باشد، طول بر اساس آن تغییر خواهد کرد در غیر اینصورت اگر تمامی سلول ها خالی باشند هیچ تغییری را احساس نخواهید کرد.

همین کار را می توانید اینبار با انتخاب گزینه AutoFit Row Height برای عرض ردیف ها نیز انجام دهید.

شما همچنین می توانید طول کل سلول های موجود در اکسل را به اندازه مورد نظر خود تغییر دهید. عرض سلول ها هم مطمئناً به نوع فونت استفاده شده و سایز آنها بستگی خواهد داشت. بدین منظور ابتدا بر روی دکمه Format کلیک کرده و سپس گزینه ی Default Width را انتخاب کنید.

هم اکنون مقدار دلخواه خود را برای طول سلول ها وارد کرده و سرانجام بر روی دکمه ی OK کلیک کنید.

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

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

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

در اکسل، ذخیره سازی اطلاعات در بین کاربرگ ها (یا worksheets) به صورت نسبی انجام میگیرد و همین مساله سبب میشود تا از تکرار اطلاعات و داده ها در پروژه های بزرگ جلوگیری شود. مزیت نسبی بودن پایگاه داده (Database) در این است که میتوانید اطلاعات یک Sheet یا برگه را در جدول و یا شیت دیگر مورد استفاده قرار دهید. اکسل این کار را با استفاده از لینک کردن سلول به سایر کاربرگ ها انجام میدهد.

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

۱# گام نخست

نرم افزار اکسل را فراخوانی کنید و یک کاربرگ (Worksheet) جدید با نام “دی” ایجاد کنید. سپس در سلول A1 عبارت “میزان فروش” و در سلول B1 مقدار “۱۰۰۰” را وارد نمایید.

 

۲# گام دوم

کاربرگ دیگری ایجاد کرده و نام آن را “بهمن” بگذارید. سپس در سلول A1 عبارت “میزان فروش” و در سلول B1 مقدار “۲۰۰۰” را وارد نمایید.

به این ترتیب ۲ کاربرگ ساده با نام های “دی” و “بهمن” دارید. حال میتوانید از اطلاعات موجود در این دو کاربرگ (worksheet) در کاربرگ سوم استفاده کنید.

۳# گام سوم

کاربرگ جدیدی با نام “مجموع” ایجاد کنید و در سلول A1 عبارت “جمع کل” را وارد نمایید و در سلول B1 فرمول زیر را تایپ کنید:

=دی!B1

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

=worksheet_name!cell_name

که worksheet_name همان نام کاربرگ و cell_name همان آدرس سلول مورد نظر است.

با این توضیحات اکنون در کاربرگ “مجموع” بر روی سلول B1 کلیک کنید تا انتخاب شود و سپس در بخش وارد کردن فرمول، فرمول مذکور را وارد نمایید. با فشردن کلید Enter مشاهده میکنید که در خانه ی B1 از کاربرگ “مجموع” مقدار عددی ۱۰۰۰ نمایش داده میشود (اطلاعات مربوط به سلول B1 از کاربرگ “دی”).

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

=دی!B1+بهمن!B1

 

در فرمول ترکیبی و ساده ی بالا، مقادیر دو سلول B1 از کاربرگهای “دی” و “بهمن” با استفاده از علامت “+” (به اضافه) با همدیگر جمع شده اند. بدین ترتیب هر تغییری که در یکی از دو سلول موجود در کاربرگ های دی یا بهمن اعمال کنید، نتیجه اش در سلول B1 کاربرگ “مجموع” نمایش داده میشود.

نکاتی ارزنده درمورد ( پنهان کردن و آشکار کردن سطرها و ستون ها )در Excel

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

 

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

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

 

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

 

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

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

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

 

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

 

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

 

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

 

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

 

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

 

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

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

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

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

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

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

 

 

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

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

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

 

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

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

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

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

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

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

 

 

خروجی این تابع ۶۴ خواهد بود. عدد ۴ به توان ۳ رسیده است.

تابع جذر | Sqrt

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

 

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

=Power(64,1/3)

 

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

 

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

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

---

1 در نرم‌افزار اکسل روی سلولی که می‌خواهید متن نتیجه‌ی شما وارد شود کلیک کنید.

 

---

2 روی سلول یا در قسمت نوار بالای اکسل شروع به تایپ نمایید. برای این که این سلول شامل یک فرمول می‌باشد از کاراکتر “=” در اول آن استفاده کنید.

 

---

---

3 پس از تایپ “=” تایپ کنید “concatenate”. در حین تایپ نیز نرم افزار اکسل همین تابع را به شما پیشنهاد می‌دهد و شما می‌توانید آن را با ماوس انتخاب کنید.

 

---

4 پس از تایپ این کلمه پرانتز باز را تایپ کنید. هر تعداد سلول و متن که می‌خواهید وارد کنید و هر یک را با , از یکدیگر جدا کنید و کلید Enter  را فشار دهید.

 

---

5 همان طور که مشاهده می کنید متن مورد نظر شما آماده شد.

 

---

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

 

---

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

 

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

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

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

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

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

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

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

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

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

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

AutoWordSelect: False

LinkedCell: B3

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

MatchEntry: ۲ – fmMatchEntryNon

 

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

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

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

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

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

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

ستون کمکی ۱

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

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

 

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

 

ستون کمکی ۲

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

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

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

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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

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

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

مرحله سوم

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

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

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

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

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

Private Sub ComboBox1_GotFocus()

ComboBox1.ListFillRange = “DropDownList“

Me.ComboBox1.DropDown

End Sub

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

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

 

 

 

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

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

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

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

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

 

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

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

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

( INDEX(I5:M10,F5,G5=

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

 

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

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

 

 

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

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

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

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

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

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

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

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

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

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

 

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

 

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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

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

 

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

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

 

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

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

 

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

 

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

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

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

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

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

 

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

 

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

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

 

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

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

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

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

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