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

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

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

 

 

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

1. ابتدا کل ناحیه اطلاعات را انتخاب می کنیم. (اسامی ماهها در بالا و اسامی محصولات در سمت راست نیز باید انتخاب شده باشند)

 

1. سربرگ Formulas را انتخاب کرده و از زیر مجموعه های آن عبارت Create from selection را انتخاب می کنیم.
2. پنجره ای با عنوان Create names from selection ظاهر می شود. در این پنجره تیک مربع کنار گزینه های Top row و Right column را فعال کرده و بر روی دکمه ok کلیک می کنیم.

 

 

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

 

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

نکته: همانطور که متوجه شدید در هنگام تایب حرف "م" که حرف اول مهر می باشد کلیه اسامی محصولات و ماهها لیست می شوند و می توانید پس از انتخاب یکی از آنها با استفاده از کلید Tab آن را در فرمول قرار دهید (مجبور به نوشتن اسم کامل نیستید).                                                                                   فرمول SUMPRODUCT در اکسل                                                                                            

 

 

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

 

ساختار فرمول SUMPRODUCT و روش استفاده از آن

 

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

 

=SUMPRODUCT(list1,list2,…)

 

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

 

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

 

اگر شما دارای داده هایی به شکل {۲,۳,۴} در یک لیست و {۵,۱۰,۲۰} در لیست دیگر باشید و فرمول SUMPRODUCT را روی این لیست ها به کار ببرید نتیجه تابع عدد ۱۲۰ خواهد بود ( زیرا ۲*۵+۳*۱۰+۴*۲۰ برابر ۱۲۰ خواهد شد)

 

SUMPRODUCT (A1:A3,B1:B3)

 

=۲*۵+۳*۱۰+۴*۲۰

 

=۱۰+۳۰+۸۰=۱۲۰

 

 

 

در اینجا نیز به نظر می رسد که این تابع کارایی زیادی نداشته باشد. اما اگر به خواندن ادامه دهید، نظر شما عوض خواهد شد.

 

فرمول SUMPRODUCT و آرایه هادر EXCEL

 

فرض کنید شما جدولی از اطلاعات فروش دارید و ستونها با عنوان های  (نام – منطقه –  مقدار فروش) نامگذاری شده اند. شما قصد دارید بفهمید که تعداد محصولات فروخته شده توسط فروشنده ای به نام “رضا” چقدر است؟

 

این یک مسئله ساده است . برای این کار می توانید از یک فرمول SUMIF استفاده کنید به طوری که در آن criteria range برابر “نام” و sum range آن برابر “مقدار فروش” باشد.

 

 

 

اما اگر بخواهید مقدار فروش فروشنده ای به نام “سعید” را در منطقه غرب پیدا کنید چکار خواهید کرد؟

 

شما در اینجا دو راه دارید.

 

1. استفاده از فرمول های آرایه ای
2. استفاده از جدول محوری
   

 

 استفاده از تابع SUMPRODUCTدراکسل 

 

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

 

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

 

فرض کنید اطلاعات فروش ما در ناحیه A1:C10 قرار داشته باشند. در ستون A اسم فروشنده، در ستون B نام منطقه و در ستون C مقدار فروش قرار دارند.

 

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

 

=SUMPRODUCT(–(A1:A10=”سعید“),–(B1:B10=”غرب“),C1:C10)

 

توضیح روش کار فرمول

 

 (–(A1:A10=”سعید“)

 

این قسمت در ستون نام فروشنده به دنبل کلمه “سعید” میگردد. در صورتی که آن را پیدا کند نتیجه عدد ۱ در غیر این صورت عدد ۰ خواهد بود.

 

(–(B1:B10=”غرب“)

 

این قسمت نیز عملی مشابه مرحله قبل را انجام می دهد. اما این بار روی ناحیه B1:B10 به دنبال عبارت “غرب” میگردد.

 

C1:C10

 

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

 

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

 

مطالبی جامع در مورد توابع ریاضی در اکسل (Math & Trig)

توابع موجود در Math & Trig مجموعآ 74 تابع می باشند که در این آموزش  سعی مشود تا برای تفکیک بهتر این توابع و یادگیری آسانتر ، آنها را به چهار بخش ، توابع جمع و ماتریس ها و اعداد صحیح دراکسل    دسته بندی کنیم و شرح مختصری از کاربرد آنها برای شما به نمایشمی گذاریم .

 

ریاضیات پایه
ردیف	تابع (دستور اکسل)	شرح مختصر کاربرد
1	ABS	قدر مطلق یک عدد را محاسبه می کند
2	SIGN	علامت یک عدد را نشان می دهد
3	SQRT	رادیکال با فرجه 2 یک عدد را محاسبه می کند
4	SQRTPI	رادیکال با فرجه 2 یک عدد*π را محاسبه می کند
5	GCD	بزرگترین مقسوم علیه مشترک چند عدد را محاسبه می کند
6	LCM	کوچکترین مضرب مشترک یک عدد را محاسبه می کند
7	MOD	باقی مانده تقسیم را نشان می دهد
8	POWER	یک عدد را به توان دلخواه می رساند
9	PRODUCT	برای ضرب چند عدد به کار می رود
10	FACT	فاکتوریل یک عدد را محاسبه می کند
11	FACTDOUBLE	فاکتوریل دوبل یک عدد را محاسبه می کند
12	LOG10	لگاریتم بر مبنای 10 را محاسبه می کند
13	LOG	لگاریتم یک عدد بر مبنای دلخواه را محاسبه می کند
14	LN	لگاریتم طبیعی (Ln) یک عدد را محاسبه می کند
15	EXP	Exp یک عدد(Exp(x)=eˣ)  را محاسبه می کند
16	COMBIN	برای محاسبه ترکیب به کار می رود
17	COMBINA	برای محاسبه ترکیب با در نظر گرفتن تکراربه کار می رود
18	RAND	یک عدد رندوم بین صفر و یک نشان می دهد
19	RANDBETWEEN	یک عدد رندوم بین اعداد دلخواه نشان می دهد
20	BASE	یک عدد را بر مبنای دلخواه می برد
21	MULTINOMIAL	نسبت فاکتوریل جمع اعداد را به ضرب فاکتوریل آنها محاسبه می کند
22	ARABIC	یک عدد با فرمت رومی را به فرمت اعداد تبدیل می کند
23	DECIMAL	یک رشته متنی که معرف یک عدد بر مبنای عددی خاص است را به اعداد اعشاری تبدیل می کند
24	ROMAN	یک عدد را به فرمت رومی نشان می دهد ( با فرمت text)

 

توابع مثلثاتی در اکسل
ردیف	تابع (دستور اکسل)	شرح مختصر کاربرد
1	COS	کسینوس عدد وارد شده را محاسبه می کند
2	COT	کتانژانت یک عدد را محاسبه می کند
3	CSC	کسکانت یک عدد را محاسبه می کند
4	SEC	سکانت یک عدد را محاسبه می کند
5	SIN	سینوس یک عدد را محاسبه می کند
6	TAN	تانژانت یک عدد را محاسبه می کند
7	COSH	کسینوس هایپربولیک یک عدد را محاسبه می کند
8	COTH	کتانژانت هایپربولیک یک عدد را محاسبه می کند
9	CSCH	کسکانت هایپربولیک یک عدد را محاسبه می کند
10	SECH	سکانت هایپربولیک یک عدد را محاسبه می کند
11	SINH	سینوس هایپربولیک یک عدد را محاسبه می کند
12	TANH	تانژانت هایپربولیک یک عدد را محاسبه می کند
13	ACOSH	کسینوس هایپربولیک معکوس یک عدد را محاسبه می کند
14	ACOTH	کتانژانت هایپربولیک معکوس یک عدد را محاسبه می کند
15	ASINH	سینوس هایپربولیک معکوس یک عدد را محاسبه می کند
16	ATANH	تانژانت هایپربولیک معکوس یک عدد را محاسبه می کند
17	ACOS	آرک کسینوس یک عدد را محاسبه می کند
18	ACOT	آرک کتانژانت یک عدد را محاسبه می کند
19	ASIN	آرک سینوس یک عدد را محاسبه می کند
20	ATAN	آرک تانژانت یک عدد را محاسبه می کند
21	ATAN2	آرک تانژانت را بر مبنای محورهای x , y محاسبه می کند
22	DEGREES	رادیان را به درجه تبدیل می کند
23	PI	مقدار عددی π را نشان می دهد
24	RADIANS	درجه را به رادیان تبدیل می کند

 

توابع جمع دراکسل
ردیف	تابع (دستور اکسل)	شرح مختصر کاربرد
1	SUM	جمع
2	SUMIF	سلول ها را با داشتن یک شرط خاص جمع می کند
3	SUMIFS	سلول ها را با داشتن چند شرط خاص جمع می کند
4	SUMPRODUCT	ضرب بین سلول ها را با هم جمع می کند
5	SUBTOTAL	جمع جزیی (یا سایر عملیات قابل انتخاب ) را برای سلول های مرجع انتخابی شما نشان می دهد
6	AGGREGATE	جمع و یا سایر عملیات قابل انتخاب را با در نظر گرفتن شرایط قابل انتخاب برای سلول های مرجع شما نشان می دهد
7	SERIESSUM	بر اساس یک رابطه جمع سری های توانی را نشان می دهد
8	SUMSQ	جمع مربعات اعداد وارد شده
9	SUMX2MY2	جمع تفاضل مربعات
10	SUMX2PY2	جمع مربعات آرایه های وارد شده
11	SUMXMY2	مربع تفاضل آرایه های وارد شده

 

ماتریس ها و اعداد صحیح در excel
ردیف	تابع (دستور اکسل)	شرح مختصر کاربرد
1	CEILING.MATH	عدد وارد شده را به نزدکترین عدد صحیح و یا مضرب وارد شده رو به بالا روند می کند
2	EVEN	عدد وارد شده را به نزدیکترین عدد صحیح رو به بالا روند می کند
3	INT	عدد وارد شده را به نزدیکترین عدد صحیح رو به پایین روند می کند
4	FLOOR.MATH	عدد وارد شده را به نزدکترین عدد صحیح و یا مضرب وارد شده رو به پایین روند می کند
5	MROUND	یک عدد را به نزدیکترین مضرب از عدد دلخواه شما روند می کند
6	ODD	یک عدد را به نزدیکترین عدد فرد رو به بالا روند می کند
7	QUOTIENT	بخش صحیح یک تقسیم را بر می گرداند (3=10/3)
8	ROUND	یک عدد را با در نظر گرفتن تعداد اعداد بعد از اعشار دلخواه روند می کند
9	ROUNDDOWN	یک عدد را به سمت پایین با در نظر گرفتن تعداد اعداد بعد از اعشار دلخواه روند می کند
10	ROUNDUP	یک عدد را به سمت بالا با در نظر گرفتن تعداد اعداد بعد از اعشار دلخواه روند می کند
11	TRUNC	هر تعداد دلخواه از اعداد بخش اعشاری مقدار وارد شده را حذف می کند
12	MDETERM	برای محاسبه دترمینال به کار می رود
13	MINVERSE	ماتریس معکوس را محاسبه می کند
14	MMULT	برای ضرب ماتریس ها به کار می رود
15	MUNIT	برای محاسبات ماتریس واحد به کار می رود

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

ایجاد ردیف خالی بین سطرهای متوالی و ابزارGoal Seek دراکسل

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

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

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

مثال:

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

۱- در اولین مرحله در کنار دادها های خود همانند شکل زیر یک ستون خالی ایجاد کنید (در این مثال ستون F) و به عنوان مثال نام آن را ردیف بگذارید و سپس در سلول F2 عدد ۱ و در سلول F3 عدد ۲ را وارد کنید. سپس اعداد ۱ و ۲ را انتخاب کرده و همانند شکل زیر روی گوشه سمت چپ سلول F3 دو بار کلیک کنید، اکسل بدین ترتیب اعداد سلول های ستون F را به ترتیب پر می کند.

 

 

۳- داده های سلول های F2 تا F6 را کپی کردهپ

۴- سلول های F1 تا F11 را انتخاب کرده و از منوی Data  روی دکمه Sort AZ کلیک کنید و  سپس همانند شکل زیر در پنجره پیغام ظاهر شده گزینه Expand The Selection را انتخاب کرده و بر روی دکمه Sort کلیک نمایید.

 

۵- همان طور که مشاهده می کنید ردیف های خالی به صورت یکی در میان در بین ردیف های شما ایجاد شدند.

 

 

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

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

معرفی ابزار Goal Seek:

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

مثال:

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

 

 

اگر بخواهیم بدانیم که مقدار فروش (متغیر مستقل) به چه میزانی بایستی افزایش پیدا کند تا سود عملیاتی (متغیر وابسته) از ۱۰ میلیون ریال به ۳۰ میلیون ریال افزایش کند، می توانیم از ابزار Goal Seek استفاده کنیم.

برای اینکار مطابق شکل زیر از تب دیتا گزینه What-If Analysis را انتخاب کرده و در منوی باز شده ابزار Goal Seek را کلیک می کنیم.

 

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

Set cell: در این بخش متغیر وابسته ای را که به دنبال رسیدن به مقدار مشخصی برای آن هستیم را  تعریف می کنیم. در مثال ما این متغیر سود شرکت است که در سلول B8  قرار دارد.

To value: در این بخش مقدار مورد نظر را برای متغیر وابسته تعریف می کنیم. از آنجایی که هدف ما رسیدن به سود ۳۰ میلیون ریال است، بنابراین این مقدار این بخش رو برابر ۳۰ میلیون قرار می دهیم.

By changing cell: در این بخش متغیری که می خواهیم با تغییر آن مقدار متغیر وابسته را به عدد مورد نظر برسانیم تعریف می کنیم. در این مثال ما می خواهیم با تغییر میزان تولید سود را افزایش دهیم، بنابراین مقدار این گزینه رو برابر سلول B2 قرار می دهیم.

 

در پابان بر روی گزینه Ok کلیک می کنیم.

 

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

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

متاسفانه اکسل بصورت پیش‌فرض در موارد زیر قادر به مرتب کردن نیست:

۱- مرتب کردن بر اساس ماه‌های سال شمسی (از فروردین تا اسفند)

۲- مرتب کردن بر اساس روزهای هفته شمسی (از شنبه تا جمعه)

۳- مرتب کردن بر اساس ترتیب حروف الفبا، به‌ صورتی‌ که کلمات شروع شده با «ا» بعد از کلمات شروع شده با «آ» قرار گیرند.

۴- مرتب کردن بر اساس ترتیب حروف الفبای فارسی. (ترتیب الفبای عربی مانند فارسی است با این تفاوت که حرف (ه) قبل از حرف (و) قرار دارد)

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

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

 

برای این کار ابتدا کل اطلاعات یعنی از سلول B3 تا J14 را انتخاب کنید. سپس از تب Home روی دکمه Sort & Filter کلیک کنید و گزینه Custom Sort را انتخاب کنید تا پنجره Soft باز شود.

 

در پنجره باز شده در قسمت Column ستون B را انتخاب کنید و در قسمت Order عبارت Custom List را انتخاب کنید.

 

تا پنجره Custom Lists باز شود.

در قسمت List entries نام ماه‌های سال را به ترتیب از فروردین تا اسفند وارد کنید و روی دکمه Add کلیک کنید.

 

پس از کلیک کردن روی دکمه Add اطلاعات لیستی که تهیه کرده‌اید به قسمت Custom Lists انتقال خواهد یافت و شما آن‌ها را در آن قسمت نیز خواهید دید. اکنون لیست دلخواه شما آماده است. در پایان روی OK کلیک کنید تا پنجره بسته شود.

 

پس از بسته شدن پنجره فوق به پنجره Sort باز خواهید گشت که در قسمت Order قاعدتا نام ماه‌های سال نمایش داده خواهد شد. دقت نمایید در قسمت Column ستون B انتخاب شده باشد، آنگاه روی OK کلیک کنید.

 

 ردیف‌های اطلاعات بر اساس ماه‌های شمسی مرتب شده است.

 

لازم به ذکر است برای دست یافتن به Custom Lists می‌توانید از مسیر زیر نیز استفاده کنید:

File Button > Options > Advanced > General > Edit Custom lists

که در این صورت قادر خواهید بود از قابلیت Import List From Cells نیز استفاده کنید.

 

 

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

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

متاسفانه اکسل بصورت پیش‌فرض در موارد زیر قادر به مرتب کردن نیست:

۱- مرتب کردن بر اساس ماه‌های سال شمسی (از فروردین تا اسفند)

۲- مرتب کردن بر اساس روزهای هفته شمسی (از شنبه تا جمعه)

۳- مرتب کردن بر اساس ترتیب حروف الفبا، به‌ صورتی‌ که کلمات شروع شده با «ا» بعد از کلمات شروع شده با «آ» قرار گیرند.

۴- مرتب کردن بر اساس ترتیب حروف الفبای فارسی. (ترتیب الفبای عربی مانند فارسی است با این تفاوت که حرف (ه) قبل از حرف (و) قرار دارد)

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

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

 

برای این کار ابتدا کل اطلاعات یعنی از سلول B3 تا J14 را انتخاب کنید. سپس از تب Home روی دکمه Sort & Filter کلیک کنید و گزینه Custom Sort را انتخاب کنید تا پنجره Soft باز شود.

 

در پنجره باز شده در قسمت Column ستون B را انتخاب کنید و در قسمت Order عبارت Custom List را انتخاب کنید.

 

تا پنجره Custom Lists باز شود.

در قسمت List entries نام ماه‌های سال را به ترتیب از فروردین تا اسفند وارد کنید و روی دکمه Add کلیک کنید.

 

پس از کلیک کردن روی دکمه Add اطلاعات لیستی که تهیه کرده‌اید به قسمت Custom Lists انتقال خواهد یافت و شما آن‌ها را در آن قسمت نیز خواهید دید. اکنون لیست دلخواه شما آماده است. در پایان روی OK کلیک کنید تا پنجره بسته شود.

 

پس از بسته شدن پنجره فوق به پنجره Sort باز خواهید گشت که در قسمت Order قاعدتا نام ماه‌های سال نمایش داده خواهد شد. دقت نمایید در قسمت Column ستون B انتخاب شده باشد، آنگاه روی OK کلیک کنید.

 

 ردیف‌های اطلاعات بر اساس ماه‌های شمسی مرتب شده است.

 

لازم به ذکر است برای دست یافتن به Custom Lists می‌توانید از مسیر زیر نیز استفاده کنید:

File Button > Options > Advanced > General > Edit Custom lists

که در این صورت قادر خواهید بود از قابلیت Import List From Cells نیز استفاده کنید.

 

 

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

 

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

این کار را می توانید از راههای مختلفی انجام دهید. مثلاً ناحیه مورد نظر را انتخاب نموده و سپس در پنجره تنظیمات چاپ و از بخش setting در اکسل  عبارت print selection را انتخاب نمایید. و یا از سربرگ page layout و از قسمت print area عبارت set print area را انتخاب نمایید.

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

 

• ابتدا ناحیه شماره ۱ را انتخاب کرده و از سربرگ Formulas و از قسمت Define Name در کادر مقابل Name نام دلخواهی را انتخاب می کنیم (مثلاً par1) و بر روی دکمه OK در پایین پنجره کلیک می کنی

 

 

• برای سایر نواحی باقیمانده دقیقاً قدم اول را تکرار می کنیم و نام های Part2 , part 3 , … را به عنوان نام انتخاب می نماییم. (تذکر: عمل نامگذاری را می توانید در قسمت سمت چپ نوار فرمول نیز انجام دهید

• ناحیه اول را انتخاب نموده و از سربرگ Page layout عبارت print Area/Set print Area را انتخاب نمایید (خط چین اطراف ناحیه ظاهر می شود که به معنی این است که اگر دستور پرینت صادر شود این ناحیه پرینت خواهد شد)

 

• از سربرگ Developer و از بخش Insert تعداد ۴ عدد دکمه رادیویی (Option Button) از قسمت Form contrlدر قسمتی از کاربر قرار می دهیم. (سربرگ Developer در حالت پیش فرض در نوار منو قرار ندارد. برای ظاهر کردن آن این مسیر را دنبال کنید. File/Options/Customize ribbon و از پنجره سمت راست تیک کنار گزینه Developer را فعال نمایید

 

 

• می توانید متن کنار دکمه های Option Button را به نام دلخواه تغییر دهید (مثلاً برای دکمه اول نام part1 و برای دکمه دوم نام part2 و … را انتخاب نمایید) برای تغییر نام روی دکمه راست کلیک نموده و سپس Edit Text را انتخاب نمایید
• بر روی یکی از دکمه ها راست کلیک کرده و Format control را در انتخاب نمایید. پنجره ای باز می شود . در کادر مقابل Cell Link آدرس یک سلول دلخواه را تایپ نمایید (یا بر روی فلش قرمز رنگ کنار کادر کلیک کرده و سپس یک سلول را کلیک نمایید و سپس برای بازگشت به کادر بر روی همین فلش یک بار دیگر کلیک کنید که در این حالت آدرس سلول به صورت مطلق در کادر مقابل Cell Link نوشته می شود. مثلاً $A$1 . اگر بر روی هر کدام از دکمه های رادیویی کلیک کنید یک عدد در سلول لینک شده نوشته می شود

• یک بار دیگر از سربرگ Formulas عبارت Name Manager را انتخاب نمایید. کادر Name Manager باز می شود . در یک خط عبارت Print_Area و در همچنین سایر محدوده های اسم گذاری شده را می توانید ببینید. عبارت Print_Area را انتخاب نموده و بر روی دکمه Edit… کلیک کنید. کادر Edit Name باز می شود. فرمول نوشته شده در کادر مقابل Refers to را به صورت

(CHOOSE(Sheet1!$A$1,part1,part2,part3,part4 تغییر دهید و بر روی دکمه Close کلیک کنید.

(در اینجا $A$1 آدرس سلول لینک دکمه های Option Button می باشد)

 

 

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

 

 

 

حل مشکل خطای Runtime error R6025 در هنگام ذخیره فایل و برازش یک منحنی برای چند سری داده در اکسل

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

 

Runtime Error! R6025: Pure virtual function call

 

در نرم افزار Excel  از منوی File گزینه Options را انتخاب کرده تا پنجره Excel Options مطابق شکل زیر باز شود. در سمت چپ این پنجره گزینه Add-Ins را انتخاب نمایید. در قسمت پایین این محیط، کادری با عنوان Manage وجود دارد. از گزینه‌های موجود، گزینه COM Add-Ins را انتخاب کنید.
 

 

 

سپس بر روی کلید GO کلیک کرده و از کادر باز شده تیک گزینه‌هایی که مربوط به نرم‌افزارهایی است که اخیرا نصب کرده‌اید و یا اینکه احساس می‌کنید بعد از نصبشان خطای Runtime ایجاد شده را برداشته و روی Ok کلیک کنید. (در رایانه من پس از نصب نرم افزار Aspen خطای Runtime ایجاد شده بود.)

 

 

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

 برازش یک منحنی برای چند سری داده در اکسل

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

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

۱- ابتدا نمودارهای دو سری ۱ و ۲ که قبلا رسم شده است را پاک کنید. در جدول داده‌ها یک سری جدید به داده‌هایتان اضافه کنید که ترکیب دو سری ۱ و ۲ باشد. سپس مطابق شکل زیر تنها نمودار سری جدید یعنی سری ۳ را رسم کنید.

 

۲- اکنون منحنی مورد نظرتان را برای نقاط سری ۳ برازش کنید. 

 

۳- حال باید کاری کنیم که در نمودار فوق فقط منحنی برازش شده نشان داده شود و نقاط سری ۳ مشخص نباشد. نمی‌توان نقاط سری ۳ را حذف کرد زیرا با حذف این نقاط، منحنی برازش نیز حذف می‌شود. برای حل این مشکل باید نقاط نمودار سری ۳ را مخفی کرد. برای اینکار روی یکی از نقاط نمودار سری ۳ راست کلیک کنید و روی گزینه Format Data Series کلیک کنید تا پنجره جدیدی باز شود. در پنجره Format Data Series، در سمت چپ روی گزینه Marker Options کلیک کرده و در سمت راست گزینه None را انتخاب کنید.

 

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

 

۴- در پایان دوباره نمودارهای سری ۱ و ۲ را به منحنی فوق اضافه می‌کنیم تا شکل زیر حاصل شود.

 

اضافه و حذف نمودن نوار خطا (Error bar) در اکسل

وار خطا یا Error bar، ابزار انعطاف پذیری برای نشان دادن خطای اندازه‌گیری داده‌ها در نمودارها است و تفاوت بین مقدار گزارش شده (معمولا میانگین نتایج است) و مقدار واقعی را نشان می‌دهد. بطور مثال اگر در آزمایشی برای یک پارامتر در شرایط ظاهرا یکسان چندین مقدار بدست آید با استفاده از نوار خطا مقدار میانگین این داده‌ها به همراه بازه‌ی تغییر سایر داده‌های مشاهده شده نمایش داده می‌شود. در ادامه مطلب با آموزش اکسل امروز همراه باشید تا با نحوه‌ی اضافه کردن و حذف نوار خطا در نمودارهای اکسل آشنا شوید.

 

 

• اضافه کردن نوار خطا برای یک نمودار

مرحله ۱: روی نمودار کلیک کنید تا بخش جدید Chart Tools بالای تب‌های اکسل ظاهر شود.

 

مرحله ۲: از تب‌های جدید موجود در این بخش، در تب Layout، بخش Analysis روی دکمه Error Bars کلیک کنید.
مرحله ۳: مطابق شکل فوق، ۵ گزینه وجود دارد:

۱- گزینه None: این گزینه زمانی کاربرد دارد که بخواهید نوار خطای موجود را حذف کنید.

۲- گزینه Error Bar with Standard Error: مشاهده نوار خطا با استفاده از خطای استاندارد (Standard Error)

۳- گزینه Error Bars with Percentage: مشاهده نوار خطا با مقدار خطای پیش‌فرض ۵ درصد (Percentage) که البته قابل تغییر است.

۴- گزینه Error Bars with Standard Deviation: مشاهده نوار خطا با انحراف معیار پیش‌فرض ۱ (Standard Deviation) که البته قابل تغییر است.

۵- گزینه More Error Bars Options: این گزینه برای مشاهده و تغییر تنظیمات نوار خطا استفاده می‌شود.

 

تنظیمات نوار خطا در EXCEL
به منظور انجام تنظیمات دلخواه در مراحل طی شده انتهای لیست قبل، روی گزینه More Error Bars Options کلیک کنید تا پنجره Format Error Bars باز شود.

 

در قسمت بالای این پنجره یعنی Displayمی‌توانید مشخص کنید که آیا نوار خطا دارای دو جهت باشد یا تک جهته به سمت منفی یا مثبت ترسیم شود.
در قسمت End Styleمی‌توانید مشخص کنید انتهای خط نوار خطا کلاهک داشته باشد (Cap) یا نداشته باشد (No Cap).

در قسمت Error Amount، اگر میزان خطا در تمام نقاط مقداری ثابت باشد، می‌توانید از گزینه Fixed value استفاده کنید.

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

اما از آنجایی که معمولا برای هر نقطه، خطای مجزایی وجود دارد برای وارد کردن این خطاها باید از گزینه آخر یعنی Custom استفاده شود. پس از انتخاب این گزینه روی دکمه Specify Value کلیک کنید تا پنجره Custom Error Bars باز شود. اکنون پس از تعیین مقادیر مثبت و منفی خطا (positive/Negative error value)، روی OK کلیک کنید.

 

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

اگر فرض کنیم مطابق داده‌های زیر در یک دمای خاص، ۳ مرتبه فشار یک سیستم اندازه‌گیری شود.

 

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

به طور مثال در جدول زیر برای دمای برابر با ۲، برای تعیین میانگین از فرمول زیر

=AVERAGE(B2:D2)

و برای تعیین مقادیر مثبت و منفی خطا از فرمول زیر

=(MAX(B2:D2)-MIN(B2:D2))/2

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

 

بنابراین داده‌های ستون A و E برای رسم نمودار و داده‌های ستون F به عنوان مقادیر خطا در کادرهای positive error value و Negative error value از پنجرهCustom Error Bars در نظر گرفته می‌شود.

 

 

کپی کردن سریع فرمول موجود در یک سلول در سایر سلول‌های مجاور و نمایش ممیز (/) به جای نقطه در اعداد اع

برای کپی کردن فرمول یک سلول در سایر سلول‌های همان ستون، روش معمول به این صورت است که باید نشانگر ماوس را روی دستگیره پرکن یا Fill handle ببرید. Fill handle نقطه مربع شکلی است که در گوشه پایین سمت راست سلول فعال ظاهر می‌شود.

 

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

روش اول) این روش تنها در حالت ستونی امکان پذیر است.

برای کپی سریع فرمول، بجای Drag کردن کافیست روی Fill handle دابل کلیک کنید.

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

 

 

---

روش دوم) بدون استفاده از ماوس

کپی ستونی:

پس از انتخاب سلول حاوی فرمول، اگر کلیدهای Ctrl+Shift+End را همزمان فشار دهید اکسل به صورت خودکار تا آخرین سلول ستون مورد نظر را انتخاب می‌کند. اما اگر نمی‌خواهید تا آخرین سلول انتخاب شود همزمان با فشردن کلید Shift، کلید جهت پائین را فشار دهید تا تعداد خاصی سلول انتخاب شود. حال کافیست کلیدهای Ctrl + d را فشار دهید تا فرمول در تمام سلول‌ها کپی شود.

 

کپی ردیفی در اکسل :

برای حالت ردیفی می‌توانید با نگه داشتن کلید Shift، بوسیله کلید جهت راست، بصورت ردیفی نیز سلول‌های مورد نظرتان را انتخاب کنید. پس از فشار همزمان کلیدهای Ctrl + R، فرمول در تمام سلول‌ها کپی می‌شود.

نمایش ممیز (/) به جای نقطه در اعداد اعشاری در اکسل                                                                

برای نمایش ممیز (/) به جای نقطه در اعداد اعشاری در اکسل سه روش وجود دارد.

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

روش اول: تغییر نمایش علامت اعشار فقط در نمودارهای اکسل 

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

روش سوم: تغییر نمایش علامت اعشار در کل ویندوز

---

روش اول: تغییر نمایش علامت اعشار فقط در نمودارهای excel

 

اعداد نمودار اگر صحیح باشند و اعشاری نباشند تفاوتی ندارد که کدامیک از این کدها استفاده شوند ولی برای اعداد اعشاری باید از کدهایی استفاده کنید که دارای عدد ۳ باشند. با استفاده از کدهای شامل عدد ۳، چنانچه یک سری از فونت‌ها را انتخاب کنید مثلا Arial، نقطه به ممیز / تبدیل می‌شود ولی اگر از فونت‌هایی مثل Calibri یا فونت‌های فارسی سری B استفاده کنید نقطه به ویرگول (,) تبدیل می‌شود. پس اگر می‌خواهید بجای نقطه، ممیز نشان داده شود فونت اعداد باید Arial باشد. البته از فونت‌‌های دیگری نیز می‌توانید استفاده کنید که خودتان می‌توانید بررسی کنید.

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

 

 

---

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

در نرم افزار اکسل (نسخه ۲۰۰۷ به بعد) به مسیر زیر بروید:

File menu –> Options –> Advanced

در قسمت Editing options تیک گزینه Use system separators را بردارید و علامت / را در قسمت Decimal separator تایپ کنید.

 

---

روش سوم: تغییر نمایش علامت اعشار در کل ویندوز

 

در پنجره باز شده گزینه Additional settings را انتخاب کنید.

 

در پنجره Customize Format علامت اعشار در قسمت Decimal Symbol نشان داده شده است که می توانید علامت / را در آن تایپ کنید.

 

 

 

میانه‌یابی روی نمودار زمانی در اکسل

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

• فعال‌سازی ToolPakدر اکسل
  

این افزونه بصورت پیش فرض در برنامه Excel غیر فعال است. جهت فعال‌سازی آن نشانگر را روی Ribbon برده و راست کلیک نمائید و از پنجره باز شده گزینه Customize The Ribbon را انتخاب نمائید.

پس از باز شدن پنجره Excel Option روی تب Add-Ins کلیک کنید و از لیست افزونه‌ها Analysis ToolPak را انتخاب و گزینه …Go را کلیک کنید.

 

به این ترتیب پنجره‌ای  باز می‌شود که برای فعال‌سازی افزونه‌ها می‌باشد. از این پنجره تیک Analysis ToolPak را فعال و گزینه OK را انتخاب نمائید.

 

به این صورت در تب DATA قسمت Analysis گزینه Data Analysis اضافه می‌گردد.

 

• صورت مسئله

فرض کنید در کسب و کارتان هر ماه مقدار درآمد ناخالص خود را بصورت جدول زیر تهیه می‌نمائید (که فرض یقین است!).

افزونه Data Analysis بصورت خودکار نمودارها را ترسیم می‌کند ولی با این حال روش ترسیم متداول نمودار بصورت زیر می‌باشد.

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

• کل بازه اعداد را Select نمائید (از ۱ تا ۴۰۰).

• از تب Insert بخش Chart، حالت Scatter را انتخاب نمائید.

نمودار داده‌های شما بصورت زیر ترسیم می‌گردد.

• نحوه میانه‌یابی در excel

در این مرحله قصد داریم میانگین درآمد ۲، ۳ و ۶ ماهه برای نمودار درآمدی سال ۹۴ ترسیم گردد. از تب DATA بخش Analysis گزینه Data Analysis را انتخاب کنید. در پنجره باز شده نوع تحلیل Moving Average را انتخاب و OK کنید تا پنجره زیر نمایش داده شود.

در باکس Input Range باید آدرس ستون داده‌های درآمد وارد شود (C4:C15) و در باکس Output Range آدرس یک ستون خالی که محاسبات افزونه در آن جای گیرد (مثلا D4:D15). باکس Interval بازه میانگین‌گیری را نیاز دارد که اعداد ۲، ۳ و ۶ ماه بترتیب باید داده شود. تعداد ماهی که شما برای بازه تعریف می‌نمایید معادل شماره ماهی است که از آن به بعد محاسبات صورت می‌گیرد. مثلا بازه ۳ ماهه یعنی میانگین ۳ ماه قبل پس در ماه دوم سال نتیجه نخواهد داشت. در آخر تیک Chart Output را فعال نمائید تا نمودارها را برای شما ترسیم گردد. نتیجه بصورت زیر حاصل می‌گردد.

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

 

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

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

۱- صرفه‌جویی در زمان با استفاده از الگو‌های آماده در excel

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

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

 

۲- ثابت کردن عنوان سطر‌ها و ستون‌ها

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

۱- در تب View، گزینه‌ی Freeze Panes را در قسمت مربوط به ribbon انتخاب کنید.

۲- در منوی کشویی مربوط به Freeze Panes، Freeze Top Row و سپس Freeze First Column را انتخاب کنید. در صورت لزوم می‌توانید هر دو گزینه‌ را انتخاب کنید.

 

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

توجه داشته باشید که در نسخه‌های قدیمی‌تر Excel، روش کار کمی متفاوت است. در این نسخه‌ها باید سلول مشترک بین سلول‌های مربوط به سطر و ستونی که می‌خواهید ثابت بماند، انتخاب کنید و گزینه‌ی Freeze Panes را بزنید.

۳- شماره‌گذاری کردن یک ستون در EXCEL

شماره‌گذاری و دسته‌بندی یک ستون در اکسل تا حدود زیادی وقت‌گیر است؛ به‌خصوص اگر بخواهید برای چندین ستون این کار را تکرار کنید. با استفاده از این روش می‌توانید تا حدود زیادی در وقت خود صرفه‌جویی کنید و به‌سرعت لیستی از اعداد ایجاد کنید. به این ترتیب که عدد ۱ و سپس ۲ را در سلول‌‌های مورد نظر و زیر هم وارد کنید. در ادامه هر دو سلول را انتخاب کنید و پس از مشاهده‌ی آیکن مربوط به fill handle، کافی است با استفاده از کشیدن ماوس، خانه‌های مورد نظر خود را انتخاب کنید.

 

هنگامی که از این ویژگی برای واردکردن تاریخ استفاده می‌کنید، می‌توانید به‌سادگی یک ستون یا سطر را به‌صورت افزایشی و تنها با واردکردن تاریخ اولیه، پر کنید. مثلا، می‌توانید تاریخ ۱۶/۲۵/۱۲ را در یک سلول وارد و آن را انتخاب کنید، هنگامی که آیکون مربوط به fill handle ظاهر شد، تعداد سلول‌های مورد نظر خود را انتخاب کنید. از این ویژگی می‌توانید برای ورود اسامی روز‌های هفته و همچنین ماه‌های سال در سلول‌های یک سطر یا ستون نیز استفاده کنید.   

 

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

۴- جابه‌جا کردن سطر‌ها و ستون‌ها

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

۱- سلول‌های حاوی عناوین ستون‌ها را انتخاب کنید.

۲- سپس روی آن‌ها راست کلیک کنید و عبارت Copy را انتخاب کنید. همچنین می‌توانید با رجوع به تب Home در قسمت ribbon، دکمه‌ی کپی را انتخاب کنید.

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

۴- راست کلیک کنید و Paste Special را انتخاب کنید. به‌علاوه می‌توانید در تب Home قسمت ribbon، روی عبارت Paste و پس از آن Paste Special، کلیک کنید.

۵- چک باکس مربوط به Transpose را انتخاب و روی کلید OK کلیک کنید.

 

۵- دسترسی به ماشین حساب

علاوه بر این‌که ممکن است از یک افزونه‌ی مربوط به ماشین‌حساب در نوار کناری صفحه استفاده کنید، می‌توانید از ویژگی مربوط به ماشین‌حساب نیز بهره ببرید. این ویژگی در مواردی کاربرد دارد که نیاز دارید محاسبات بدون فرمول را به‌سرعت انجام دهید. شما می‌توانید به دو روش ماشین‌حساب را به صفحه‌ی خود اضافه کنید؛ یکی با استفاده از ribbon و دیگری با استفاده از نوار ابزار Quick Access.

برای اضافه کردن ماشین‌حساب در مکان مورد نظر خود؛ از منوی فایل، گزینه‌ی Options را انتخاب کنید. سپس یکی از دو گزینه‌ی Customize Ribbon یا Quick Access Toolbar را بر اساس جایی که می‌خواهید از آن استفاده کنید، انتخاب کنید. در قسمت Choose commands from موجود در لیست کشویی، All Commands را انتخاب کنید. لیست را به طرف پایین پیمایش و Calculator را انتخاب کنید و برای درج آن در نوار ابزار دکمه‌ی Add را بزنید. توجه داشته باشید که اگر بخواهید آن را در ribbon اضافه کنید، باید گروهی سفارشی ایجاد و ماشین‌حساب را در آنجا اضافه کنید.

 

۶- لینک کردن به برگه‌ها و صفحه‌ها

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

• سلول حاوی اطلاعاتی را که می‌خواهید به‌عنوان لینک از آن استفاده کنید، انتخاب کنید.
• راست کلیک کنید و Hyperlink را انتخاب کنید، به‌علاوه می‌توانید به تب Insert بروید و روی Hyperlink در قسمت ribbon کلیک کنید.
• در پنجره‌ی بازشده Place in this Document را انتخاب کنید.
• سپس عبارتی را که می‌خواهید در آن سلول نمایش داده شود، به همراه مرجع آن و صفحاتی در گزارش که شما به اطلاعات آن لینک کرده‌اید، مشخص کنید.
• کلید OK را بزنید

۶- استفاده از کلیدهای میانبر صفحه کلیددر اکسل

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

• F4 (‌یا Fn+F4‌) جهت تکرار آخرین دستوری که برای تغییر قالب‌بندی جمله مثل رنگ یا فونت به کار رفته است.
• Alt + H,E, A  جهت حذف محتویات، قالب‌ها و سایر داده‌های متصل به سلول‌های انتخاب‌شده مورد استفاده قرار می‌گیرد.
• Ctrl + W، برای بستن یک صفحه و Ctrl + O برای باز کردن یک صفحه جدید استفاده می‌شود.
• از Ctrl + Shift + Colon برای واردکردن زمان و از Ctrl + Shift + Semicolon برای ورود تاریخ جاری سیستم استفاده می‌شود.
• Shift + F9 جهت محاسبه‌ی کاربرگ فعال مورد استفاده قرار می‌گیرد.
• Shift + Tab برای انتقال به سلول یا گزینه‌ی قبلی استفاده می‌شود.
• Ctrl + Home جهت انتقال به ابتدای صفحه، مورد استفاده قرار می‌گیرد.
• Ctrl + Page Down برای انتقال به صفحه‌ی بعد و Ctrl + Page Up برای حرکت به صفحه‌ی قبل استفاده می‌شود.

 

نکاتی در مورد نمودارهای ترکیبی

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

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

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

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

 دو گزینه در اختیار شما قرار میگیرد. یکی Secondary Axis و دیگری Chart Type. با استفاده از لیستهای موجود در chart Type براحتی می توانید نوع هر یک از نمودارهایتان را تغییر دهید.

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

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

ابزار ساخت فهرست Sheet ها در اکسل

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

یکی از روش‌های بسیار مفید برای این دسته از فایلها  در اکسل ساخت فهرستی از تمامی worksheet‌ها است که با کلیک بر روی هر نام یک worksheet، آن worksheet فعال شود. اینکار با ابزار Hyperlink در اکسل به سادگی قابل انجام است (کلید میانبر ساخت و ویرایش Hyperlink در اکسل Ctlr+k است.)

 

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

این برنامه علاوه بر ساخت فهرست Sheetها و لینک کردن آنها به Sheet مربوطه ، به صورت جداگانه در هر Sheet فایل شما یک دکمه در خانه A1 قرار می‌دهد که با کلیک بر روی آن به "فهرست" باز می‌گردید . در شکل زیر دکمه قرمز رنگ "Home" اینکار را انجام می دهد. در ضمن این دکمه در Print چاپ نخواهد شد در عکس زیر دکمه قرمز رنگ با کلمه Home را می توانید مشاهده کنید.

 

 

تمامی این برنامه به زبان ویژوال بیسیک اکسل نوشته شده است و در صورت نیاز می توانید آنرا تغییر و یا توسعه دهید. برای وارد شدن به محیط ویژوال بیسیک اکسل کلید Alt+F11 را بزنید

 

1- یک پنجره به کاربر نمایش داده می شود که در آن می تواند دکمه‌های Home (بازگشت به فهرست) را ایجاد / حذف نماید.

2- کاربر می تواند هر دکمه / عکس / آیکون دلخواه را به عنوان دکمه خانه (بازگشت به فهرست) را همه شیت های فایل اضافه کند.

 

 

نحوه اجرای ابزار ساخت فهرست شیت‌های اکسل

 

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

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

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

 کلید ALT+F8 را بزنید تا در پنجره ای که باز خواهد شد، لیست همه ماکروها را مشاهده نمایید.

 گزینه "Build_Sheet_Navigator" را انتخاب کنید و کلید Run را بزنید تا ماکروی "ساخت فهرست شیت ها" اجرا شود .

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

 

 

نحوه استفاده 

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

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

 

2- درج دکمه‌های خانه

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

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

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

 

3- ایجاد دکمه‌های خانه دلخواه

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

الف) یک عکس / آیکون / شکل دلخواه خود را در یک شیت داشته باشید.

ب) آن عکس / آیکون / شکل را به شیت فهرست خود لینک کنید. 

ج) آن عکس / آیکون/ شکل را انتخاب نمایید. 

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

 

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

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

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

برای این کار کافی است کلید Ctrl را نگه داشته و با کلیک ماوس، کاربرگهای بعدی را انتخاب کنید. پس از اضافه شدن دومین کاربرگ، عبارت [Group] در عنوان فایل ظاهر می گردد که نشان دهند فعال شدن قابلیت فوق است.

همچنین جهت انتخاب کلیه کاربرگها می بایست روی یک کاربرگ کلیک راست کرده و گزینه Select all sheets را انتخاب کنید.

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

 

** توجه شود که در گروه بندی در excel، کلیه تغییرات بر تمامی کاربرگهای انتخاب شده اعمال می گردد. لذا می بایست به بخش عنوان برنامه توجه شود که حالت Group فعال است یا خیر؟.                               مزایای استفاده از ماکرو در اکسل                                   

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

تعویض محتویات دو سلول‌ یا سطر یا ستون‌ وتغییر عملکرد کلید Enter در اکسل

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

 

هنگام تعویض محتویات دو ستون یا سطر یا سلول ممکن است چند حالت پیش بیاید:

• حالت اول) دو ستون یا سطر یا سلول مجاور در اکسل
  

روش ۱)  اگر بخواهید  بخواهید محتویات دو ستون مجاور A و B را با هم تعویض کنید. برای اینکار باید ستون سمت راست را انتخاب نمایید. (حتما باید ستون سمت راست انتخاب شود. برای تعویض محتویات دو سطر، حتما باید سطر پائین را انتخاب نمایید.)

 

پس از انتخاب ستون سمت راست (ستون B) آنرا Cut کنید (شرتکات Ctrl+X). سپس ستون سمت چپ (ستون A) را انتخاب کنید و کلیدهای Ctrl و + را همزمان فشار دهید تا عملیات تعویض انجام شود.

 

روش ۲) برخلاف روش بالا دیگر نیاز نیست حتما ستون سمت راست را cut کرد. مطابق شکل زیر کافیست بطور مثال محتویات ستون A را انتخاب کنید، با بردن ماوس روی کادر سبز ستون A و پس از تبدیل نشانگر ماوس به علامت +، کلید Shift را نگه دارید و نشانگر ماوس را به سمت راست ستون B بکشید (Drag). هنگامیکه علامت 工 ظاهر گردید دکمه ماوس را رها کنید. به همین سادگی محتویات دو ستون با یکدیگر تعویض شده‌اند. با همین روش محتویات سطرها و سلول‌ها را نیز می‌توانید تعویض کنید.

 

---

• حالت دوم) دو ستون یا سطر یا سلول غیر مجاوردر اکسل

روش ۱) ماکرونویسی

  

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

 

 

سپس  کلید F5 را فشار دهید.

 دو کادر نمایش داده می‌شود که باید دو محدوده مورد نظر که می‌خواهید محتویات آن‌ها را با تعویض کنید را انتخاب نمائید و پس از فشار دکمه Ok، نتیجه نهایی تعویض محتویات دو ستون می‌باشد.

تغییر عملکرد کلید Enter در اکسل

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

۱- بر روی زبانه ی File کلیک کنید.

 

۲- Options را انتخاب نمایید.

 

۳- در سمت چپ گزینه Advanced را انتخاب نمایید.

 

 

۴- در سمت راست در قسمت Editing options به صورت پیش فرض این گزینه فعال است. شما می‌توانید با استفاده از لیست مقابل Direction جهت حرکت کلید Enter را به بالا، پایین، چپ و راست تغییر دهید. همچنین با برداشتن تیک عبارت After pressing Enter, move selection  عملیات حرکت کلید Enter به جهات مختلف را غیر فعال کنید.

 

راه های ایجاد لیست های کشویی(ComboBox) وابسته بهم در اکسل

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

روش اول:  استفاده از فرمول  Offset در اکسل

۱. روی سلول B3 کلیک کنید.

۲.  به آدرس  data > validation بروید.

۳. اسم دفاتر فروش رو تایپ کنید.

۴.روی سلول D3 کلیک کنید.

۵.کل معجزه اینجاست! فرمول OFFSET:

 

=OFFSET($C$6,MATCH($B$3,$B$7:$B$13,0),0,COUNTIF($B$7:$B$13,$B$3),1)

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

توضیح فرمول:

=Offset(Reference,Rows,Cols,height,width)

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

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

Rows: تعداد ردیفی که باید از محل سلول رفرنس جابا شویم در اینجا (Match(B3,B7:B13,0

تابع Match موقعیت B3 را در محدوده B7:B13 نشان میدهد. در اینجا مثلا اگر اصفهان را انتخاب کرده باشیم نتیجه فرمول عدد ۳ می باشد.

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

خب تا اینجا یعنی از سلول C6 سه ردیف برو پایین و هیچ ستونی جابجا نشو. تا اینجا در سلول C10 هستیم.

Height: ارتفاع محدوده ای که میخواهیم برایمان نمایش داده شود. در اینجا میخواهیم مثلا مشتریهای اصفهان نمایش داده شوند. تا الان به اولین مشتری اصفهان یعنی “مهدی” رسیده ایم-سلول C10- حالا باید از فرمولی استفاده کنیم که بواسطه آن تعداد کل مشتریها اصفهان رابیاورد. طبیعتا باید تعداد مشتریهای اصفهان را بشماریم و بجای height بگذاریم.

(COUNTIF($B$7:$B$13,$B$3 نتیجه عدد سه است.

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

 

روش دوم:  استفاده از فرمول Indirect و Vlookup

 

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

حال کافیست روی سلول لیست دوم کلیک کرده و به تب Data منوی Data Validation بروید و در قسمت Source از فرمول زیر استفاده نمایید:

=Indirect(vlookup(B3,B7:F9,2,0))

 

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

روش سوم:  استفاده از فرمول Indirect و Hlookup

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

 

یادگیری زبان خارجی در اکسل

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

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

 

ابتدا در یک سلول تاریخ میلادی را وارد نمایید.(برای درج تاریخ جاری میلادی میتوانید از تابع Now() استفاده کرده یا با زدن کلید ترکیبی Ctrl+; تاریخ جاری را در سلول انتخاب شده درج نماییم.)

 

 

اکنون سلول مورد نظر را انتخاب کرده و پس از کلیک راست روی آن گزینه Format Cells را انتخاب نمایید.(همچنین میتوانید با زدن کلید ترکییبی Ctrl+1 وارد این قسمت شوید)

 

 

در پنجره فرمت سول ل نمایش داده شده در تب Number گزینه Custom را انتخاب کرده و در قسمت سمت راست پنجره در کادر Type عبارت dddd را تایپ نمایید.

 

 

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

dddd [$ -0429]

 

 

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

dddd [$ -LCID Hex Code]

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

 

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

مورد دیگری که میتوان از این کد استفاده کرد در تابع Text می باشد. به این صورت که کد مورد نظر را به صورت زیر درون تابع text قرار می دهیم:

=text(a1,”dddd [$ -0429]”)                                            

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

1- صرفه‌جویی در زمان با استفاده از الگو‌های آماده

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

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

 

۲- ثابت کردن عنوان سطر‌ها و ستون‌ها

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

۱- در تب View، گزینه‌ی Freeze Panes را در قسمت مربوط به ribbon انتخاب کنید.

۲- در منوی کشویی مربوط به Freeze Panes، Freeze Top Row و سپس Freeze First Column را انتخاب کنید. در صورت لزوم می‌توانید هر دو گزینه‌ را انتخاب کنید.

 

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

توجه داشته باشید که در نسخه‌های قدیمی‌تر Excel، روش کار کمی متفاوت است. در این نسخه‌ها باید سلول مشترک بین سلول‌های مربوط به سطر و ستونی که می‌خواهید ثابت بماند، انتخاب کنید و گزینه‌ی Freeze Panes را بزنید.

 

۳- شماره‌گذاری کردن یک ستون در excel

شماره‌گذاری و دسته‌بندی یک ستون در اکسل تا حدود زیادی وقت‌گیر است؛ به‌خصوص اگر بخواهید برای چندین ستون این کار را تکرار کنید. با استفاده از این روش می‌توانید تا حدود زیادی در وقت خود صرفه‌جویی کنید و به‌سرعت لیستی از اعداد ایجاد کنید. به این ترتیب که عدد ۱ و سپس ۲ را در سلول‌‌های مورد نظر و زیر هم وارد کنید. در ادامه هر دو سلول را انتخاب کنید و پس از مشاهده‌ی آیکن مربوط به fill handle، کافی است با استفاده از کشیدن ماوس، خانه‌های مورد نظر خود را انتخاب کنید.

 

هنگامی که از این ویژگی برای واردکردن تاریخ استفاده می‌کنید، می‌توانید به‌سادگی یک ستون یا سطر را به‌صورت افزایشی و تنها با واردکردن تاریخ اولیه، پر کنید. مثلا، می‌توانید تاریخ ۱۶/۲۵/۱۲ را در یک سلول وارد و آن را انتخاب کنید، هنگامی که آیکون مربوط به fill handle ظاهر شد، تعداد سلول‌های مورد نظر خود را انتخاب کنید. از این ویژگی می‌توانید برای ورود اسامی روز‌های هفته و همچنین ماه‌های سال در سلول‌های یک سطر یا ستون نیز استفاده کنید.

 

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

 

۴- جابه‌جا کردن سطر‌ها و ستون‌ها در اکسل پیشرفته

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

۱- سلول‌های حاوی عناوین ستون ها را انتخاب کنید.

۲- سپس روی آن‌ها راست کلیک کنید و عبارت Copy را انتخاب کنید. همچنین می‌توانید با رجوع به تب Home در قسمت ribbon، دکمه‌ی کپی را انتخاب کنید.

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

۴- راست کلیک کنید و Paste Special را انتخاب کنید. به‌علاوه می‌توانید در تب Home قسمت ribbon، روی عبارت Paste و پس از آن Paste Special، کلیک کنید.

۵- چک باکس مربوط به Transpose را انتخاب و روی کلید OK کلیک کنید.

 

۵- دسترسی به ماشین حساب در اکسل

علاوه بر این‌که ممکن است از یک افزونه‌ی مربوط به ماشین‌حساب در نوار کناری صفحه استفاده کنید، می‌توانید از ویژگی مربوط به ماشین‌حساب نیز بهره ببرید. این ویژگی در مواردی کاربرد دارد که نیاز دارید محاسبات بدون فرمول را به‌سرعت انجام دهید. شما می‌توانید به دو روش ماشین‌حساب را به صفحه‌ی خود اضافه کنید؛ یکی با استفاده از ribbon و دیگری با استفاده از نوار ابزار Quick Access.

برای اضافه کردن ماشین‌حساب در مکان مورد نظر خود؛ از منوی فایل، گزینه‌ی Options را انتخاب کنید. سپس یکی از دو گزینه‌ی Customize Ribbon یا Quick Access Toolbar را بر اساس جایی که می‌خواهید از آن استفاده کنید، انتخاب کنید. در قسمت Choose commands from موجود در لیست کشویی، All Commands را انتخاب کنید. لیست را به طرف پایین پیمایش و Calculator را انتخاب کنید و برای درج آن در نوار ابزار دکمه‌ی Add را بزنید. توجه داشته باشید که اگر بخواهید آن را در ribbon اضافه کنید، باید گروهی سفارشی ایجاد و ماشین‌حساب را در آنجا اضافه کنید.

 

۶- لینک کردن به برگه‌ها و صفحه‌ها در EXCEL

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

• سلول حاوی اطلاعاتی را که می‌خواهید به‌عنوان لینک از آن استفاده کنید، انتخاب کنید.
• راست کلیک کنید و Hyperlink را انتخاب کنید، به‌علاوه می‌توانید به تب Insert بروید و روی Hyperlink در قسمت ribbon کلیک کنید.
• در پنجره‌ی بازشده Place in this Document را انتخاب کنید.
• سپس عبارتی را که می‌خواهید در آن سلول نمایش داده شود، به همراه مرجع آن و صفحاتی در گزارش که شما به اطلاعات آن لینک کرده‌اید، مشخص کنید.
• کلید OK را بزنید

۶- استفاده از کلیدهای میانبر صفحه کلید

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

• F4 (‌یا Fn+F4‌) جهت تکرار آخرین دستوری که برای تغییر قالب بندی جمله مثل رنگ یا فونت به کار رفته است.
• Alt + H,E, A  جهت حذف محتویات، قالب‌ها و سایر داده‌های متصل به سلول‌های انتخاب‌شده مورد استفاده قرار می‌گیرد.
• Ctrl + W، برای بستن یک صفحه و Ctrl + O برای باز کردن یک صفحه جدید استفاده می‌شود.
• از Ctrl + Shift + Colon برای واردکردن زمان و از Ctrl + Shift + Semicolon برای ورود تاریخ جاری سیستم استفاده می‌شود.
• Shift + F9 جهت محاسبه‌ی کاربرگ فعال مورد استفاده قرار می‌گیرد.
• Shift + Tab برای انتقال به سلول یا گزینه‌ی قبلی استفاده می‌شود.
• Ctrl + Home جهت انتقال به ابتدای صفحه، مورد استفاده قرار می‌گیرد.
• Ctrl + Page Down برای انتقال به صفحه‌ی بعد و Ctrl + Page Up برای حرکت به صفحه‌ی قبل استفاده می‌شود.

به نظر شما کدام‌یک از این ترفند‌ها کاربردی‌تر است؟

از آنجا که همه‌ی ما از مایکروسافت اکسل استفاده می‌کنیم، با استفاده از این ترفند‌های به‌ظاهر کوچک می‌توانید تا حدود زیادی در زمان صرفه‌جویی کنید.

 

مطالبی درمورد امنيت و كاربرد اكسل

(اینکار به راحتی از طریق office button>Prepare>Encrypt document و سپس دادن پسورد امکانپذیر است.)
میتوان از worksheet ها و سلولهای خاص و نمودارها، اشیای گرافیکی، نمودارها، سناریو ها، پنجره ها را نیز محافظت کرد.
فرض کنید در برخی سلول ها فرمول دارید و نمی خواهید کسی به آنها دسترسی داشته باشد و یا فرمول شما را بببیند و یا تغییر دهید، بنابراین نیاز دارید که سلول های خاصی را غیرقابل ویرایش یا غیر قابل انتخاب یا غیرقابل رویت نمایید.



محافظت سلولها در برابر تغییرات در اکسل
1- قفل کردن یا از حالت قفل خارج کردن (lock / unlock) سلولهای مورد نظردر شیت

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


ناحیه مورد نظر یا سلول مورد نظر را انتخاب کنید و کلیک راست کنید.

به مسیر format cell>protection>lock بروید

تب lock * را تیک بزنید.

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


2- انجام عملیات محافظت شیت (Protect Sheet)در excel
در excel 2007 برای اینکار باید ابتدا به منوی review بروید.(در اکسل 2003 به منوی tools بروید) protect sheet را انتخاب کنید

برای اینکه بعدا بتوانید از حالت protect sheet(unprotect)خارج شوید میتوانید پسورد بگذارید
گزینه هایی در زیر عبارت Allow all user of this worksheet وجود دارد که عملیات را درباره سلولهای قفل شده نمایش داده میشود و میتوانید آنها را هم با توجه به سلولهای قفل شده یا نشده انتخاب کنید و کاربر را محدود کنید.

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

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


به مسیر review>protection> allow users to edit range می رویم.(در excel 2003 اینکار از منوی tools امکانپذیر است)

با استفاده از گرینه new مناطقی را که میخواهیم اضافه می کنیم

و درنهایت password *میدهیم

هنوز کار تمام نشده و مثل حالت قبلی باید sheet * را محافظت کنیم.

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

محافظت فایلهای اکسل workbook می توانید review /protection/protect workbook (در اکسل 2003 tools/protection/protect را انتخاب کنید تا از فایلهای اکسل نیز محافظت کنید


محافظت از یک workbook با استفاده از کلمات عبور
وقتی که protect workbook, protect sheetیاprotect and share workbook را انتخاب میکنید یک کلمه عبور برای غیرفعال کردن محافظت(unprotect) باید استفاده شود. می توانید از تنها ازی یک کلمه عبور برای هر workbook یا worksheet که محافظت کرده اید استفاده کنید.
قفل کردن یک workbook در 2 حالت structure و window انجام می گیرد




در حالت structure قابلیتهای زیر(درو اقعی بیشتر عملیات کار با شیت) از یک شیت گرفته می شود:



جابجا کردن sheet
تغییر نام و رنگ تب sheet
حذف و اضافه کردن sheet
hide و unhide کردن sheet



در حالت window protect کلید close (ضربدر) بالای worksheet حذف می شود

پر کردن تمام سلول های خالی اکسل درمیان سلول های دارای محتوا و ویژگی Watch Window در اکسل

  پر کردن سلول های خالی را در بین سلول های دارای محتوا در اکسل انجامبسیار ساده است  برای اینکار ابتدا در یک Sheet اکسل سلول هایی را بصورت پراکنده پر می نماییم .

 

سپس از منوی Home در اکسل  بخش Editing گزینه Go To را انتخاب می نماییم. یا از کلید ترکیبی Ctrl+G استفاده می کنیم .

 

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

 

در این پنجره گزینه Blanks را انتخاب می نماییم.و دکمه OK را می زنیم .

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

 

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

 

و سپس کلید های ترکیبی Ctrl +Inter را فشار می دهیم . ملاحضه می کنید که تمام سلول های خالی میان سلول های دارای محتوا پر شده است .

 ویژگی Watch Window در اکسل                                                                                               معمولا کسانی که به طور روزمره و مداوم با اکسل سرو کار دارند دوست دارند با استفاده از یک سری از ویژگی های موجود در اکسل از کارکردن با این نرم افزار لذت ببرند . این ویژگی مخصوص کسانی هست که می خواهند بر روی فایل های اکسل نظارت داشته باشند و در یک نگاه بتوانند به کلیه توابع اعمال شده در آن فایل بصورت دقیق دسترسی داشته باشند.برای این منظور ما ابتدا در یک Work book چند Work sheet ایجاد می کنیم و در هر sheet چند ردیف داده وارد نموده و برای هر کدام تابعی اعمال می کنیم .

 

 

 

خوب حالا از منوی Formulas و بخش Formulas Auditing گزینه Watch Windos را انتخاب می کنیم.

 

خوب با کلیلک روی این گزینه پنجره ای کوچکی باز می شود که شامل دو گزینه Add و Delete می باشد و در بخش پایین پنجره شش گزینه وجود دارد که بسته به سلول انتخابی ما این شش خانه تکمیل می گردد.با انتخاب گزینه Add و وارد کردن آدرس سلولی که فرمول را در آن وارد کرده اید، سطر های این کادر تکمیل می گردد . این کار را میتوان در Work book و Work sheet های مختلف انجام داد.و برای پاک کردن یک سطر از گزینه Delete استفاده می کنیم.

 

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

 

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

                    

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

بعنوان مثال: برای انتخاب کل فایل ها و یا هر آنچه که مورد نظر است از کلیدهای ترکیبی Ctrl+A استفاده می کنیم. یا برای کپی کردن آن نیز از کلیدهای ترکیبی Ctrl+C و …

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

ترفندها و میانبرهای اکسل (Ctrl+A):

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

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

- See more at: http://parsia.net/tricks-shortcuts-excel-part-one/#sthash.igvl1wK1.dpuf

ساخت لیست کشویی در اکسل

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

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

مرحله اول :

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

 

 

 

مرحله دوم :

ابتدا سلولی که میخواهید منوی کشویی شود را انتخاب و از سربرگ Data  گزینه Data validation  را انتخاب و Data Validation… را اجرا نمایید .

 

 

 

مرحله سوم :

در پنجره باز شده منوی کشویی Allow را بر روی List  قرار داده و از قسمت Source  بر روی روی Icon  انتخاب سلول  کلیک نمایید.

 

 

 

مرحله چهارم :

سلول هایی که محتویات کشویی شما هست را انتخب نمایید و بر روی close پنجره انتخاب کلیک نمایید.

 

 

 

مشاهده میکنید که در منوی Source سلول های انتخابی شما قرار دارد . حال بر روی OK کلیک نمایید.

 

 

 

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

 

 

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

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

۱- ابتدا ستون یا محدوده مورد نظر خود را انتخاب کنید.

۲- در سربرگ Home از   Excel  (به این نوار در Excel 2007 به بعد Ribbon می‌گویند) گزینه Conditiona Formatting را بزنید.

3- در لیست باز شده، گزینه Highlight Cell Ruls را بزنید.

4- در این لیست گزینه Duplicate Value  را بزنید.

5 – در پنجره آخر، از شما پرسیده می‌شود که سلولهای تکراری چه رنگی شوند.

 

جلوگیری از ورود اعداد یا مقادیر تکراریدر اکسل

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

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

ابتدا ستون مورد نظر را انتخاب نمایید.

1- دقت نماید که آدرس سلول فعال شما چیست، مثلادر شکل زیر سلول فعال A1 است.

2- از Ribbon به سربرگ Data بروید و سپس گزینه Validation را اتابع  COUNTIF(A:A,A1) در اکسل مشخص می کند که در ستون A ، چند بار مقادیر سلول A1 تکرار شده است و هدف ما این است که این مقدار تکراری نباشد، بنابراین باید این تعداد برابر با یک باشد ، که تکراری نشود.                                                      3-تابع  COUNTIF در اکسل مشخص می کند که در ستون A ، چند بار مقادیر سلول A1 تکرار شده است و هدف ما این است که این مقدار تکراری نباشد، بنابراین باید این تعداد برابر با یک باشد ، که تکراری نشود.

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

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

برای یافتن سل­های دارای خطا در یک کاربرگ اکسل، گزینه Find & Select در قسمت Editing منوی Home انتخاب و از منوی باز شده، گزینه Go to Special برگزیده می­شود. از پنجره نمایش­یافته، گزینه Formulas  و از گزینه­ های آن Errors انتخاب می­شود. سپس، با فشردن دکمه Ok به اولین سلول  دارای خطا منتقل می­شویم.

خطاهای فرمول نویسی
پیام خطا	دلیل	روش رفع خطا
######	عرض ستون از طول عدد یا تاریخ درج شده در سل، کوچک­تر است.	پهنای ستون سل را افزایش دهید.
	اگر نتیجه فرمول از نوع تاریخ و یا زمان، مقدار منفی شود
#DIV/0!	خطای تقسیم یک عدد بر صفر رخ داده یا یک عدد بر محتوای خالی یک خانه تقسیم شده است.	آدرس­ها و مقدارهایی را که در مخرج قرار دارند بررسی کنید.
#NAME?	اشاره به محدوده نام­گذاری شده که ایجاد نشده است	نام تابع یا آدرس موردنظر را بیابید و تصحیح کنید.
	استفاده از تابعی که وجود ندارد، برای مثال نام آن صحیح نباشد
	اسفاده از نوع برچسب­ها، زمانی که استفاده از آن­ها مجاز نباشد
	استفاده از رشته متنی که علامت  ” بسته نشده باشد
	استفاده از محدوه غیرمجاز آدرس در فرمول ­نویسی
	اشاره به کاربرگی که وجود ندارد
#REF	اشاره به سلولی که محتوای آن پاک شده باشد	آدرس­ها و محتوای سل­های آن­ها را بررسی کنید.
	اشاره به سلی که ممکن است حذف شده باشد
	استفاده از لینکی که موجود نباشد
#VALUE!	نوع اطلاعات سل لی ی که در فرمول به آن ارجاع شده اشتباه است. برای مثال، یک تابع مقداری عددی را به عنوان ورودی گرفته ولی رشته متنی به آن ارسال شود	آدرسی که در فرمول به آن ارجاع شده یا محتوای آن را تغییر دهید
#N/A	استفاده از توابع MATCH، HLOOKUP، VLOOKUP وقتی که نتواند مقایسه را انجام دهد یا فهرست ورودی آن ها مرتب نشده باشد	تصحیح تابع
	استفاده از تابع نوشته شده توسط کاربر که مجاز به استفاده از آن در کاربرگ نباشیم
	استفاده از تابع بدون آن که تمام پارامترهای ورودی به آن ارسال شده باشد
	استفاده از تابع NA()
#!NULL	استفاده نامناسب از جداساز آدرس محدوده
	زمانی که دو محدوده آدرس، اشتراک نداشته باشند
#NUM!	ارسال ورودی­های اشتباه به یک تابع	اصلاح داده یا آرگومان­های ارسال شده به یک تابع
	استفاده از تابعی که به دلیل اشتباه دستورات آن، خاتمه نیابد
	عدد نتیجه بسیار بزرگ باشد یا به اندازه­ای کوچک باشد که قابل محاسبه نباشد

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

- تابع COUNTA: شمارش تعداد کاراکترها و همچنین تعداد سلول‌های پر

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

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

=COUNTA(value1,[value2],...)

مثلا جواب فرمول زیر برابر است با ۳

=COUNTA(a,100,ش)

حالت ۲) شمارش تعداد سلول‌های پر شامل هر نوع کاراکتری حتی فاصله (منظور سلول خالی نیست، بلکه منظور کاراکتر فاصله است). در این حالت دستور این تابع به صورت زیر است:

=COUNTA(range)

مثل:

=COUNTA(B2:B10)

ترکیب دو حالت ۱ و ۲ را نیز می‌توان برای تابع COUNTA استفاده کرد مثل:

 

---

۲- تابع COUNT: شمارش تعداد اعداد و تعداد سلول‌های شامل عدد از جمله تاریخ‌ها در اکسل

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

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

=COUNT(value1,[value2],...)

حالت ۲) شمارش تعداد سلول‌های پر شامل عدد از جمله تاریخ. دستور این تابع در این حالت به صورت زیر است:

=COUNT(range)

 

 

---

۳- توابع COUNTIF و COUNTIFS: شمارش شرطی در اکسل 

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

• تابع COUNTIF:

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

=COUNTIF(range, criteria)

این تابع دو آرگومان یا ورودی دارد. اولین آرگومان آدرس فهرستی است که شمارش باید برای آن انجام شود. دومین آرگومان. معیار یا شرط شمارش است. مثلا، خروجی فرمول زیر، تعداد سلول‌هایی است که در ناحیه B2:B7 دارای عددی برابر با ۲۰۰ می‌باشند. یا:

=COUNTIF(B2:B7,200)

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

=COUNTIF(B2:B7,E2)

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

=COUNTIF(B2:B7,">200")

یا

=COUNTIF(E2:E7,"<=300")

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

=COUNTIF(E2:E7,">"&B7)

=COUNTIF(E2:E7,"<"&B7)

خروجی فرمول‌های فوق به ترتیب ۴ و ۱ می‌باشند.

 

این تابع همچنین می‌تواند برای داده‌های غیر عددی نیز استفاده شود. مثلاً تابع

=COUNTIF(B2:B25,"Excel")

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

• تابع COUNTIFS:

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

=COUNTIFS(criteria_range1, criteria1,[criteria_range2, criteria2]…)

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

تمام حالت‌های گفته شده برای تابع Countif، برای تابع Countifs نیز قابل اجرا است.

اما استفاده از فرمول فوق اشتباه است، این فرمول بیانگر این است که باید هر دو شرط با هم برقرار باشد یعنی تابع Countifs پس از بررسی شرط اول یعنی برابری با ۱۰۰، شرط دوم یعنی برابری با ۳۰۰ را بررسی می‌کند که ما چنین چیزی نمی‌خواهیم. فرمول باید طوری باشد که در صورتی که یکی از دو حالت نیز برقرار بود شمارش انجام شود. البته این نکته را نیز باید دانست که درون تابع Countifs نمی‌توان از OR استفاده کرد. برای حل این مشکل دو راهکار وجود دارد:در تصویر فوق، در ناحیه E2:E7، تعداد سلول‌های حاوی عدد ۱۰۰ یا ۳۰۰ را محاسبه کنید.

به نظر می‌رسد که مشابه مثال قبل با دو شرط روبرو هستیم و باید از تابع Countifs استفاده کنیم:

 

=COUNTIFS(E2:E7,100,E2:E7,300)

 

 

 

در واقع ما با یک شرط روبرو هستیم. باید دو عدد را درون کوشه بگذارید و همراه با تابع Countif از تابع Sum هم استفاده کنید:

=SUM(COUNTIF(E2:E7,{100,300}))

یا اینکه از مجموع دو Countif استفاده کنید:

=COUNTIF(E2:E7,100)+COUNTIF(E2:E7,300)

خروجی هر دو فرمول فوق برابر است با ۴.

 شمارش تعداد سلول‌های شامل متن (اعداد شمارش نشود) و تعداد سلول‌هایی که دارای متن نیستند:

---

۴- تابع COUNTBLANK: شمارش تعداد سلول‌های خالی در  EXCEL

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

=COUNTBLANK(range)

 

نکاتی درمورد اعدادتصافی و حل مشکل خطای Runtime error R6025 در اکسل

برای تولید عدد تصادفی بین دو عدد a و b از تابع Randbetween به صورت زیر استفاده می شود.

(RANDBETWEEN(a;b=

برای مثال برای تولید یک عدد تصادفی بین 50 و 75 دستور زیر را اجرا کنید.

(RANDBETWEEN(50;75=

 

البته می توانیداز تابع Rand به تنهایی استفاده کنید.

 

 

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

 

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

برای جلوگیری از این کار، پس از انتخاب اعداد روی آن ها کلیک راست کنید و پس از انتخاب Past... در پنجره ظاهر شده گزینه Values را انتخاب و روی OK کلیک کنید.           حل مشکل خطای Runtime error R6025 در اکسل                                          

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

 

 در نرم افزار Excel  از منوی File گزینه Options راازرا انتخاب کرده تا پنجره Excel Options مطابق شکل زیر باز شود. در سمت چپ این پنجره گزینه Add-Ins را انتخاب نمایید. در قسمت پایین این محیط، کادری با عنوان Manage وجود دارد. از گزینه‌های موجود، گزینه COM Add-Ins را انتخاب کنید.

 

 

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

 

 

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

 

برگرفته شده از pctarfand.ir

 

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

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

 

 

ساخت نمودار ساده

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

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

در منوی Insert و در گروه Chart گزینه Line را انتخاب کنید و سپس Line with Markers را انتخاب کنید، ظاهر تمامی موارد از شکل یا آیکن آن ها پیداست.

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

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

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

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

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

تغییر مبنای جدول در excel

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

منو هایی به نوار ریبون اضافه می شوند.

در تب Design روی دکمه Switch Row/Column کلیک کنید تا مقادیر جا به جا شوند.

 

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

نمودار خود را پس از ساخته شدن انتخاب کنید. و سپس وارد تب Layout شوید و روی Chart Title کلیک کنید، و مطابق آیکون های نشان داده شده مکانی برای نمایش تیتر خود انتخاب کنید.

 

 

 

نمایش راهنمای نمودار (رنگ بندی نمودار)

برای اینکه مشخص کنید راهنمای رنگ بندی نمودار در کجا نمایش داده شود یه تب Layout مطابق انتچه بالا تر ذکر شود وارد شوید و سپس روی Legend کلیک کنید و مطابق آیکن هر گزینه یک مورد به دلخواه خود انتخاب کیند، ب

 

برچسب داده ها (Data Labels)

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

 

 

 

 

توابع Count و Sum و سایر توابع مشابه در اکسل

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

 

تابع Countدر اکسل  

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

 

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

 

 

تابع Countifدرexcel

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

 

تابع Countifs

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

در مثال زیر ما می گوییم سبز و بزرگتر از ۹

 

در واقع موارد یافته شده باید در یک ردیف باشند، مثلا ۱۰ هم شرط دوم را دارد اما هم ردیفش red شرط یک را ندارد.

 

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

تابع Sum

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

 

تابع Sumif

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

 

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

 

جمع روی ستون B زده میشود اما شرط در تابع روی دو ورودی اول مشخص شده است، یعنی ستون A جایی که نوشته شده باشد green.

 

تابع Sumifs

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

 

 

 

جلوگیری از تبدیل فرمت عدد همراه با اسلش (/) و خط تیره (-) به فرمت تاریخ در اکسل

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

 

به عنوان مقدمه ابتدا بررسی می‌کنیم که چند نوع عدد می‌توان در اکسل وارد کرد. برای اینکار کافیست روی یک سلول راست کلیک کرده و روی گزینه Format Cells کلیک کنید یا کلیدهای Ctrl+1 را همزمان فشار دهید تا پنجره Format Cells باز شود.

علامت اسلش / بین دو عدد در چهار حالت Number، Fraction ،Date و Text و علامت خط تیره (ـ) بین دو عدد در ۲ حالت Date و Text ممکن است استفاده شود. بنابراین مجموعاً ممکن است عدد به چهار فرمت تبدیل شود که نحوه تبدیل به هر یک از این چهار فرمت در ادامه توضیح داده شده است:

۱- تاریخ (Date). مثلا تایپ 8/3 یا 3-8 و نمایش آن بصورت I08-Mar

برای اینکار نیاز به هیچ تنظیمات اضافه‌ای نیست و اکسل بصورت پیش‌فرض عدد وارد شده همراه با اسلش یا خط تیره را به فرمت تاریخ تبدیل می‌کند. مثلا پس از تایپ 8/3 یا 3-8، اکسل آن را بصورت خودکار، I08-Marنمایش می‌دهد. البته می‌توانید نحوه نمایش تاریخ را تغییر دهید. برای اینکار در پنجره Format Cells، از تب Number، روی گزینه Date کلیک کنید و در سمت راست یعنی قسمت Type، فرمت مورد نظرتان را انتخاب نمایید.

۲- تقسیم یک عدد بر عدد دیگر و نمایش بصورت کسری (Fraction) دراکسل                                        که دارای ۲ حالت است:

الف) نمایش آن بصورت عدد مخلوط یا مرکب (مثلا تایپ 8/3 و نمایش آن بصورت 2/3  2)

ب) نمایش عینی آن (مثلا تایپ 8/3 و نمایش آن به صورت 8/3)

برای اینکه کسر نوشته شده به یکی از دو حالت فوق نمایش داده شود کافیست قبل از تایپ عدد، یک صفر و سپس فاصله تایپ کنید (کلید Spacebar را فشار دهید) و پس از آن عدد موردنظرتان به همراه اسلش را وارد کنید. بصورت زیر:

0 8/3

اگر کسر مورد نظرتان کوچکتر از یک باشد حالت الف و ب تفاوتی ندارند. اما اگر بزرگتر از یک باشد روش گفته شده کسر را بصورت مرکب نمایش می‌دهد یعنی حالت الف (2/3 2). البته همان ابتدا هم می‌توانیدعدد را بصورت مرکب وارد کنید، یعنی بجای تایپ 8/3، تایپ کنید 2/3 2. در هر دو حالت فوق، پس از کلیک روی سلول مورد نظر، معادل اعشاری عدد 8/3 یعنی 2.66666666666667 در Formula Bar نشان داده می‌شود.

بجای تایپ صفر و فاصله از مسیر دیگر نیز می‌توان حالت کسر را فعال کرد. برای اینکار روی سلول مورد نظر راست کلیک کرده و روی گزینه Format Cells کلیک کنید برای حالت الف در پنجره Format Cells، از تب Number روی گزینه Fraction کلیک کنید و در سمت راست یعنی قسمت Type، گزینه Up to one digit را انتخاب نمایید و برای حالت ب، از تب Number روی گزینه Custom کلیک کنید و در قسمت Type یکی از کدهای زیر را وارد کنید.

?/?

#/#

? و # نشانه یک رقم می‌باشند با این تفاوت که در ازای صفرهای اضافه زمانی که از علامت ? استفاده می‌شود space (فضای خالی) جایگزین می‌شود.

۳- تقسیم یک عدد بر عدد دیگر و نمایش نتیجه آن (Number). مثلا تایپ 8/3 و نمایش آن بصورت 2.666667

برای اینکار می‌توانید قبل از تایپ عدد علامت مساوی را تایپ کنید. یعنی بصورت

=8/3

یا اینکه روی سلول مورد نظر راست کلیک کرده و روی گزینه Format Cells کلیک کنید. در پنجره Format Cells، از تب Number روی گزینه Number کلیک کنید.

۴- فرمت متن (Text). یعنی بصورت متن باشد و قابلیت شرکت در محاسبات را نداشته باشد.

برای اینکار دو راه وجود دارد:

الف) ابتدا علامت ' را تایپ نموده و سپس عدد را وارد کنید. اکسل علامت ' را نشان نمی‌دهد. مثلا تایپ کنید

'8/3    or     '8-3

ب) قبل از عدد یک فاصله تایپ کنید (کلید Space را فشار دهید).

یا اینکه در پنجره Format Cells، از تب Number فرمت Text را انتخاب کنید.

توجه: انتخاب فرمت اعداد در پنجره Format Cells در تمام حالات فوق زمانی قابل استفاده است که شما قبل از وارد کردن عدد در سلول مورد نظرتان، فرمت مدنظرتان را برای سلول انتخاب کرده باشید، در غیر اینصورت پس از وارد کردن عدد امکان جابجایی بین ۴ فرمت فوق وجود ندارد.

---

• حل مشکل فوق هنگام کپی اعداد:

چنانچه قصد دارید اعدادی که دارای / یا - هستند را از جای دیگر کپی کنید و در اکسل پیست کنید از چهار فرمت گفته شده در بالا فقط این امکان وجود دارد که فرمت تاریخ یا فرمت Text داشته باشند. برای فرمت تاریخ که نیاز به هیچ کار اضافه‌ای نیست. اگر عدد دارای / یا - باشد پس از پیست کردن بصورت خودکار به فرمت تاریخ تبدیل می‌شود. اما برای جلوگیری از این تبدیل خودکار، ابتدا محدوده‌ای که می‌خواهید عددها را در آن پیست کنید انتخاب کنید و فرمت محدوده را از حالت General به حالت Text تغییر دهید. سپس بجای پیست کردن بصورت معمولی از تب Home روی گزینه Paste کلیک کنید و گزینه Match Destination Formatting را انتخاب کنید

 

یا روی گزینه Paste Special کلیک کنید و در پنجره باز شده گزینه Text را انتخاب کنید و در پایان روی Ok کلیک کنید.

 

اضافه کردن یک یا چند ردیف خالی بین ردیف‎های متوالی در اکسل

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

 

برای اینکار ۳ روش وجود دارد:

روش اول) روش آسان‌تر ولی غیرمستقیم

برای مثال اگر داده‌ها بصورت زیر باشد، طبق مراحل زیر عمل کنید:

۱) در کنار داده‌ها به یک ستون خالی نیاز داریم. بطور مثال برای داد‌های بالا از ستون F استفاده می‌کنیم.

۲) در سلول F1 عدد ۱ و در سلول F2 عدد ۲ را درج می‌کنیم.

۳) اعداد ۱ و ۲ را انتخاب می‌کنیم و روی علامت fill handle دو بار کلیک می‌کنیم. 

اکسل بطور خودکار سلول‌های ستون F را پر می‌کند. بطور مثال از ۱ تا ۹

۴) سپس از سلول‌های پر ستون F (از F1 تا F9) کپی می‌گیریم و در سلول F10 پیست می‌کنیم.

 

۵) اکنون سلول‌های ستون F (از F1 تا F18) را انتخاب می‌کنیم و از تب Data روی دکمه Sort A to Z کلیک می‌کنیم.

 

۶) پس از کلیک پیغام زیر ظاهر می‌شود گزینه Expand the selection راتیک می‌زنیم.

 

۷) در پایان روی دکمه Sort کلیک کنیم. نتیجه زیر حاصل می‌شود.

* اگر می‌خواهید بجای یک ردیف خالی، ۲ یا ۳ ردیف خالی اضافه کنید باید ۲ یا ۳ بار اعدادی که در ابتدا در ستون F درج کردید را کپی کنید.

---

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

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

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

 

۳) در پایان روی ok کلیک کنید تا مشابه روش قبل بین تمام ردیف‌ها یک ردیف خالی ایجاد شود.

---

روش سوم)  ابزار Kutools for Excel

 

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

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

 

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

 

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

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

 

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

 

 

سپس روی  کلید F5 را فشار دهیدد.

ناحیه نوشته‌های اولیه (در مثال فوق A1:A13) را انتخاب کنید و روی دکمه Ok کلیک کنید.

 

پس از کلیک روی OK، باید محدوده‌ی جایگزینی (دو ستون واژه اصلی و واژه جایگزین) را انتخاب کنید.