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

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

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

شرح مسئله :

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

شرط اول : کدام طبقه

بازه  E5:E1000 ستونی است که طبقات ما در ان مشخص است و i5 سلولی است که شرط ما به ان حساس است (در اینجا STORY5 شرط مورد نظر ماست)

شرط دوم : نام تیر

بازه  B5:B1000 ستونی است که نام تیر ما در ان مشخص است و j6 سلولی است که شرط ما به ان حساس است (در اینجا b7 شرط مورد نظر ماست)

شرط سوم : ابعاد تیر

بازه  C5:C1000 ستونی است که ابعاد تیرهای ما در ان مشخص است و k6 سلولی است که شرط ما به ان حساس است (در اینجا B45xB45 شرط مورد نظر ماست)

شرط چهارم : محل قرارگیری ارماتور

بازه  D5:D1000 ستونی است که محل قرارگیری ارماتور در ان مشخص است ( چپ – میانه – راست ) و m5 سلولی است که شرط ما به ان حساس است (در اینجا End-i شرط مورد نظر ماست)

 

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

چگونگی استفاده از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 كلیك كنید، محدوده اول را انتخاب كنید، كلید كاما (,) را بفشارید و بعد محدوده بعدی را انتخاب كنید. بین تمام محدوده های بعدی هم باید كاما تایپ كنید.

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

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

 

تابع Countدر اکسل  

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

 

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

 

 

تابع Countifدرexcel

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

 

تابع Countifs

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

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

 

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

 

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

تابع Sum

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

 

تابع Sumif

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

 

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

 

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

 

تابع Sumifs

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

 

 

 

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

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

*توجه  *

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

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

 

جمع كردن بیش از یك محدوده در excel

 

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

 

مرتب سازی سفارشی (Custom Sort)در اکسل

برای مرتب کردن داده‌ها (Sort)  میتوان از ابزار Sort در تب Data استفاده کرد که شامل دو حالت مرتب سازی از کوچک به بزرگ و بزرگ به کوچک (یا به ترتیب حروف الفبا و عکس حروف الفبا) می‌باشد. ولی در برخورد با داده های کیفی ترتیبی هیچ یک از این دو گزینه مد نظر ما نیست.                                                                                                                                    با آموزش اکسل امروزهمره باشید

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

 

در این حالت، ابتدا باید ترتیب داده ها برای اکسل مشخص شود (این عمل یکبار صورت میگیرد و در تمامی فایلها قابل استفاده می‌باشد) به این صورت که ابتدا لیست تمامی ماه‌ها را به ترتیب در یک ستون وارد می‌نماییم و از تب File، قسمت Option، حالت Advanced را انتخاب کرده و در بخش General بر روی گزینه Edit Custom Listدراکسل  کلیک نمایید.                                                                                       سپس در قسمت پایین ادرس سلولهایی که ماههای شمسی در آنها ثبت شده است را وارد نموده و بر روی گزینه Import کلیک کرده تا لیست ماهها در قسمت Custom List مشاهده شود. از این مرحله به بعد در تمامی فایلهای اکسل، لیست و ترتیب ماههای شمسی تعریف شده است و میتوان با نوشتن اسم یک ماه و اعمال کردن برای سلول های دیگر، لیست ماهها را به ترتیب داشته باشیم.

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

 

 

 

بعد از تعریف ترتیب ماههای شمسی، به منظور مرتب سازی اطلاعات بر اساس ماههای شمسی، کافی است که از تب Data، بر روی گزینه Sort کلیک کرده و در صفحه باز شده، در قسمت Column نام ستونی که اسامی ماهها در آن ثبت شده است را انتخاب نماییم و در قسمت Order گزینه Custom List را و در پنجره باز شده، از قسمت Custom List حالت ماههای شمسی را بر گزیده تا با Ok نمودن تمامی پنجره ها، داده ها به ترتیب ماههای شمسی مرتب شوند.

 

 

 

 اگر در یک ستون شماره سال و در ستون دیگر نام ماه شمسی ثبت شده باشد و بخواهیم داده ها به ترتیب سال مرتب شوند و داده های هر سال به ترتیب ماه، کافی است در پنجره Sort بر روی گزینه Add Level کلیک کرده تا دو قسمت برای تعریف معیار مرتب سازی ایجاد شود، سپس در قسمت بالایی ستون سال و در قسمت پایینی ستون ماه به صورت Custom List انتخاب شود.

 

 

                                                                                          

نحوه استفاده از تابع SUBTOTAL اکسل

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

از تابع SUBTOTAL اکسل برای انجام عملیاتی مانند جمع کردن، میانگین گرفتن و … روی یک محدوده خاص استفاده میکنیم. بر خلاف توابعی مانند SUM، AVERAGE و … که تنها یک عملیات مشخص رو روی یک محدوده یا RANGE انجام میدهند، تابع SUBTOTAL اکسل این قابلیت را دارد  که با استفاده از پارامتری که به آن  میدهیم عملیات های مختلفی ا روی محدوده مدنظر انجام میدهد

قاعده کلی تابع SUBTOTAL اکسل:

=SUBTOTAL(محدوده مدنظر; شماره تابع)

شماره تابع: این پارامتر عملیاتی راکه میخواهیم روی محدوده مد نظر انجام انجام دهیم . این عملیات ها شامل موارد زیر  ا ست. دقت کنید که در شماره تابع های 1 تا 11، تنها سلول هایی که فیلتر شده باشند لحاظ خواهند شد. شماره های 101 تا 111 دقیقا مشابه توابع 1 تا 11 هستند با این تفاوت که در این شماره توابع، سلول هایی که HIDE شده باشند نیز لحاظ نخواهند شد :

شماره تابع	تابع	توضیحات
1	AVERAGE	میانگین سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
2	COUNT	تعداد سلول های محدوده مدنظر رو شمارش میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
3	COUNTA	تعداد سلول های محدوده مدنظر رو شمارش میکنه. سلول هایی که محتوی متن باشند هم شمارش خواهند شد. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
4	MAX	ماکزیمم سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
5	MIN	مینیمم سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
6	PRODUCT	حاصلضرب سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
7	STDEV.S	انحراف معیار نمونه سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
8	STDEV.P	انحراف معیار سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد

محدوده مدنظر: این محدوده سلولهایی که قراراست  عملیات روی آنها  انجام شود رانشان می دهد.        

تعیین شماره تابع SUBTOTAL اکسل:

همانطور که میدانیم تابع جمع در اکسل SUM است. پس کافیست  شماره تابع را  برابر 9 قرار بدیم. (در صورتی که بخواهیم سلول های HIDE شده هم در جمع لحاظ شوند  میتوانیم بجای 9 از شماره 109 استفاده کنیم).

تعیین محدوده مدنظر:

 اگر تعداد محصولات در سلول های D5 تا D14 قرار دارد  پس محدوده مدنظر ما برای جمع، محدوده D5:D14 است.

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

=SUBTOTAL(9;D5:D15)

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

 

نکته:

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

تبدیل جدول متقاطع (Cross Table) به جدول معمولی (List Table) واستفاده از جمع شرطی برای چند شیت در اکس

گاهی از اوقات برای آنالیز داده‌ها (بطور مثال استفاده در نرم افزاری نظیر TableCurve برای برازش منحنی) نیاز می شود تا یک جدول متقاطع از اعداد را به یک جدول لیستی معمولی تبدیل کنیم. شاید تغییرات بصورت دستی برای یک جدول ۴ در ۴ کار آسانی باشد ولی برای یک جدول ۳۰ در ۲۰ چطور؟ در ادامه مطلب با ماهمراه باشید تا با نحوه انجام این کار بصورت خودکار بوسیله نرم افزار اکسل آشنا شوید.

 

تبدیل جدول معمولی به جدول متقاطع در excel                                                                                ظاهرا در نرم‌افزار اکسل چنین امکانی وجود ندارد. اما می‌توان با نصب افزونه Kutools for Excel در اکسل، این عملیات را انجام داد. پس از نصب این افزونه و اطمینان از فعال شدن آن در نرم‌افزار اکسل (اضافه شدن یک تب جدید با نام Kutools در اکسل)، مراحل زیر را طی کنید:

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

مرحله ۲: پس از کلیک روی تب Kutools، در قسمت Range Converter، گزینه ….Transpose Table Dimensions را انتخاب کنید.

 

مرحله ۳: در پنجره Transpose Table Dimensions، گزینه List to cross table را انتخاب کنید. البته در این مرحله هم می‌توانید از قسمتSource range، محدوده داده‌های ورودی را تعیین کنید. محدوده خروجی نتایج را در قسمت Results range مشخص کنید. اگر لیست داده‌های ورودی دارای سربرگ یا عنوان هستند گزینه My data has headers را انتخاب نمایید (مطابق شکل زیر).

 

 

مرحله ۴: در پایان روی Ok کلیک کنید و نتایج را مشاهده کنید.

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

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

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

 

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

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

---

روش دوم) تابع Sumproduct

مثال زیر را در نظر بگیرید. می‌خواهیم مجموع اعداد ستون H در سه شیت ۱ تا ۳، البته با شرطی که در ستون G حرف A باشد را محاسبه کنیم و نتیجه آن در شیت ۴ درج شود.

 

برای اینکار می‌توان از فرمول زیر استفاده کرد. البته باید در شیت ۴، نام شیت‌ها تایپ شود (A2:A4):

=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A4&"'!G2:G5"),C2,INDIRECT("'"&A2:A4&"'!H2:H5")))

خروجی رابطه فوق برابر می‌شود با ۱۵۰.

 

---

روش سوم) تعریف تابع جدید SUMIF3D

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

برای ساخت ماکرو کلیدهای Alt + F11 را فشار دهید

در واقع این توابع، مجموع اعداد محدوده B2:B5 در سه شیت مشخص شده را محاسبه می‌کنند البته در صورتیکه در محدوده A2:A5 عبارت موجود در سلول A2 یعنی حرف B وجود داشته باشد.

=SUMIF3D(A2:A5,A2,B2:B5,"Sheet1","Sheet2","Sheet3")

یا

=SUMIF3D(A:A,A2,B:B,"Sheet1","Sheet2","Sheet3")

خروجی رابطه فوق برابر می‌شود با ۶۰.

توجه: در تابع SUMIF3D نام شیت‌ها باید بین علامت نقل قول " " قرار گیرد. همچنین ترتیب نام‌ها اهمیت ندارد.

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

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

 

روش‌های زیر به طور نمونه برای انجام عملیات جمع توضیح داده شده است. هدف محاسبه مجموع اعداد محدوده B2:B6 در شکل زیر است که در سلول B3 خطا وجود دارد. همانطور که مشاهده می‌کنید خروجی تابع Sum بصورت خطا ظاهر شده است.

 

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

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

=IF(ISNUMER(B2),B2,0)

 

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

---

🔵 روش دوم) استفاده از تابع  Sumifدراکسل

با استفاده از تابع Sumif که در این مطلب معرفی شده است، می‌توان کاری کرد که در حین محاسبه جمع از خطا صرف نظر شود. به طور مثال در مثال فوق خطای !VALUE# در سلول B3 وجود دارد. باید در تابع Sumif از این خطا صرف نظر شود مانند فرمول زیر:

=SUMIF(B2:B6,"<>#VALUE!")

خروجی این فرمول برابر با ۱۳ می‌شود.

فرمول فوق زمانی کاربرد دارد که فقط خطای !VALUE# وجود داشته باشد اما چون ممکن است چندین نوع خطا وجود داشته باشد باید از معیاری استفاده کرد که همه خطاها را صرف نظر کند مانند فرمول زیر که برای همه خطاها قابل استفاده است:

=SUMIF(B2:B6,"<="&9.99E+307)

 

---

🔵 روش سوم) استفاده از فرمول‌نویسی آرایه‌ای

روش بعد، استفاده از فرمول‌نویسی آرایه‌ای است که در این مطلب توضیح داده شده است. در این نوع فرمول‌نویسی پس از نوشتن فرمول باید به جای اینتر، کلیدهای Ctrl + Shift+ Enter را همزمان بفشارید. در فرمول زیر در حین جمع سلول‌ها تابع IFERROR خطاها را به صفر تبدیل می‌کند.

=SUM(IFERROR(B2:B6,0))

 

تابع IFFERROR در اکسل ۲۰۰۷ و بالاتر موجود است، اما اگر از اکسل ۲۰۰۳ استفاده می‌کنید باید از فرمول‌های زیر استفاده نمایید. (حتما در انتها کلیدهای Ctrl+Shift+Enter را فشار دهید)

=SUM(IF(ISERROR(B2:B6),0,B2:B6))

=SUM(IF(ISNUMBER(B2:B6),B2:B6,0))

---

🔵 روش جهارم) تابع AGGREGATE (اکسل ۲۰۱۰ به بعد)

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

=AGGREGATE(9,6,B2:B6)

 

---

🔵 روش پنجم) ماکرونویسی در excel

 

معرفی فرمول SUMIFS

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

 

همه ما می دانیم که فرمول SUMIF برای جمع بستن چندین داده با قید یک شرط بکار میرود. برای مثال اگر شما داده های زیر را داشته باشید و بخواهید بدانید که در منطقه “تهران” مجموعاً چقدر فروش داشته اید؟ و از آنجاکه فقط یک شرط برای محاسبات جمع مد نظر است پس در نتیجه باید از فرمول SUMIF استفاده کنید. حاصل عدد ۳۴۳ خواهد بود، یعنی:

 

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

=sumif(range;criteria;[sum_range])

=sumif(محدوده ای که شرط در آن قرار دارد; خود شرط; محدوده ای که میخواهید اعدادش را جمع کنید)

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

حال اگر اطلاعاتمان به شکل زیر بود و در نظر داشتیم بدانیم که از محصول شماره ۱ در تهران چقدر فروش داشته ایم باید از چه فرمولی استفاده می کردیم؟

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

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

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

=sumifs(sum_range;criteria_range1;criteria1;criteria_range2;criteria2;…)

 

 

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

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

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

طریقه ایجادTABLE در اکسل

برای تبدیل یک محدوده به جدول می توان به ۲ طریق زیر عمل کرد:

1. محدوده ی سلول هایی که قصد تبدیل آنها به جدول دارید را انتخاب کنید. سپس از طریق Home > Styles > Format As Table با انتخاب یکی از حالات، استایلی برای محدوده انتخاب کنید. در اینجا پنجره ای باز خواهد شد و این سوال را از شما می پرسد که آیا این محدوده را تبدیل به جدول کنم که با زدن دکمه ok محدوده تبدیل به جدول خواهد شد.
2. محدوده را انتخاب کنید و از Insert > Tables > table ابزار table را انتخاب نمایید یا اینکه کلید ترکیبی Ctrl+T را فشار دهید.

چند تکنیک درباره جدولی که ایجاد کردیم:

1. برای جدول ایجاد شده به طور خودکار اکسل نامی در نظر می گیرد. از این نام در فرمول نویسی ها می توانیم استفاده کنیم.
2. اگر در زمان ایجاد جدول چک باکس مربوط به سربرگ را تیک زده باشید، ردیف اول داده ها به عنوان سربرگ جدول در نظر گرفته می شود. نام هر یک از ستون ها نیز با عنوان سربرگ شناخته می شود.
3. زمانی که به سمت پایین حرکت کنید دیگر حروف الفبایی مربوط به نام ستون ها نشان داده نمی شود و به جای آن عنوان سربرگ نمایان خواهد شد.
4. در پایین جدول می توانید ردیف خودکاری اضافه کنید که این ردیف می تواند جمع، شمارش تعداد سلول ها و… را به صورت خودکار انجام دهد. این ردیف با کلیک بر روی جدول و فعال شدن Table Tools از آدرس Design>Table Style Options>Total Row انجام می شود. با زدن تیک چک باکس Total Row ردیف Total اضافه خواهد شد. نوع خروجی که این ردیف قرار است نمایش بدهد از لیست مربوط به هر سلول قابل انتخاب می باشد.
5. جدول قابلیت بزرگ شدن دارد. زمانی که در آخرین سلول جدول مقداری را وارد کنید و بعد از آن بخواهید به ردیف بعدی بروید با نگارش داده ای در سلول، آن ردیف به جدول اضافه خواهد شد. حتی ردیف جمع که در شماره ۴ بیان کردیم هم یک ردیف به سمت پایین جابجا می شود.
6. در صورتی می خواهید داده های تکراری را از جدول حذف کنید می توانید با کلیک بر روی سلولی از جدول و فعال شدن Table Tools از Design>Tools>Remove Duplicates حذف کردن داده های تکراری را فعال کنید. پنجره ای باز خواهد شد و این موضوع را باید در این پنجره مشخص کنید که داده ها از کدام ستون بررسی و حذف شوند.
7. فیلتر و مرتب سازی هوشمندانه تر در جدول قابل انجام می باشد. در جدولی که ایجاد کردیم سربرگ آن در حالت عادی به صورت فیلتر در خواهد آمد. با زدن هر کدام از این فیلترها و انتخاب نوع آن می توانیم مواردی که قصد فیلتر آنها را داریم انتخاب کنیم.
8. در صورتی که  خواهیم دکمه فیلتر در سربرگ نمایش داده نشود با فعال کردن table Tools و بعد از آن از Design>Table Style Options>Filter Button چک باکس بودن یا نبودن فیلتر در سربرگ را انتخاب می کنیم.
9. در جداول لازم نیست فرمولی که می نویسیم را برای بقیه سلول ها کپی کنیم بلکه با نوشتن فرمول برای اولین سلول و زدن کلید اینتر بقیه سلول های جدول به صورت خودکار تکمیل خواهند گشت.
10. ابزاری که در جدول خیلی مورد توجه هست، Structure Reference ها هستند. در زمان فرمول نویسی با انتخاب هر سلول، نام عادی آن در نظر گرفته نمی شود بلکه نامی از ستون آن انتخاب می شود که به این حالت خواهد بود:

=[@عنوان سربرگ ستون]

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

[عنوان سربرگ ستون] نام جدول=

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

 

فرمول در کسل

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

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

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

 

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

 

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

پیدا کردن سطرهای تکراری در اکسل و حذف آنها با تابع داده DATA FUNCTION

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

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

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

در این پنجره اگر تیک My data has headers را بزنید، اکسل از عنوان ستون های شما در کادر مربوط به Columns استفاده خواهد کرد. در کادر Columns تمامی ستون هایی که باید تکراری بودن مقادیر آنها بررسی شود، لیست شده است. پس از اتمام عملیات، اکسل تعداد سطرهای تکراری و غیر تکراری را اعلام خواهد کرد.

پیدا کردن سطرهای تکراری در اکسل با قالب بندی شرطی

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

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

با استفاده از قالب بندی شرطی می‌توانید سطرهایی با داده های تکراری را به راحتی پیدا کرده و قالب آن را به دلخواه خود تغییر دهید. برای این منظور محدودهای که در آن داده‌های سطرها تکراری هستند را انتخاب کنید. از منوی HOME به زیر منوی Styles رفته در بخش Conditional Formatting کلیک کنید. در منوی باز شده قسمت Highlight Cells Rules را انتخاب کنید و در این قسمت به Duplicate Values بروید. در پنجره باز شده اگر مقدار Duplicate را انتخاب کنید، داده‌های تکرای قالب بندی خواهند شد و اگر Unique را انتخاب کنید، داده‌های بدون تکرار قالب بندی می‌شوند. قالب موردن نظرتان را از سمت راست و از منوی کره‌کره‌ای انتخاب کنید یا در Custom Format قالب دلخواه خود را تعریف کنید.

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

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

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

اگر می‌خواهید از داده‌های موجود به سرعت ستونی از داده‌های غیر تکراری تولید کنید، استفاده از جدول پاشنه‌ای مناسب است. برای ایجاد یک جدول پاشنه‌ای، از منوی INSERT به زیر منوی Tables رفته و روی Pivot Table کلیک کنید. در پنجره باز شده محدوده داده‌ها را انتخاب کنید. OK نمایید تا جدولتان ایجاد شود. به طور پیش فرض جدول در برگه‌ای جدید ایجاد خواهد شد، اگر می‌خواهید جدولتان در همان برگه داده‌ها ایجاد شود، دکمه رادیویی Existing Worksheet را انتخاب کرده و سلول مورد نظر، برای درج جدول را انتخاب کنید. اگر داده‌های شما در جای دیگری به غیر از برگه فعلی قرار دارد، دکمه رادیویی Use an external data source  را انتخاب نموده و آنها را فراخوانی کنید.

 پس از ایجاد جدول پاشنه‌ای، از قسمت Choose filed to add report عنوان ستونی که اضافه کرده‌اید را به قسمت ROWS درگ کنید. با تنظیم Value Filed Settings بر روی شمارنده، تعداد داده‌های تکراری را می‌توانید مشاهده کنید.

 

تنظیمات بیشتر بر روی جداول پاشنه‌ای

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

 

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

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

پیدا کردن سطرهای تکراری در اکسل با استفاده از مرتب کردن داده ها Data Sort

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

• تعداد داده‌هایتان کم است و می‌توانید با کنترل چشمی آنها را حذف کنید.
• می‌خواهید سطرهای تکراری را ببنید، آنالیز کنید و قبل از حذف، تکراری بودن آنها را تایید کنید.
• نوع داده‌هایتان ساده است و می‌توانید تکراری بودن آنها را تشخیص بدهید. (داده هایتان ۱۵ رقمی و ترکیبی از عدد و حرف نیست!)

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

برای مرتب کردن داده‌ها، کل داده‌هایتان را انتخاب کرده و از منوی DATA به زیر منوی Sort&Filter رفته و بر روی Sort کلیک کنید. در پنجره باز شده در قسمت Column ستونی را که می‌خواهید بر اساس آن مرتب سازی کنید، انتخاب نماید. OK کنید داده‌هایتان مرتب خواهند شد.

 

پیدا کردن سطرهای تکراری در اکسل با استفاده از فیلتر پیشرفته Advanced Filter

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

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

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

برای اعمال فیلتر پیشرفته بر روی داده‌ها، از منوی DATA به زیر منوی Sort&Filter رفته و بر روی Advanced کلیک کنید. در پنجره باز شده محدوده داده‌ها را انتخاب کرده و دکمه رادیویی Unique records only را بزنید. OK کنید. اگر می‌خواهید داده‌های اصلی بدون تغییر باقی بماند، دکمه رادیویی Copy to another location را بزنید تا اکسل داده‌هایتان را به جای دیگری کپی کرده و سپس فیلتر نماید.

 

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

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

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

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

 

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

 

سطرهایی که داده‌های تکراری دارند را می‌توان در هم ترکیب کرد. بدین معنی که اگر شماره و طول آرماتور ها یکسان است، می‌توان تعداد آرماتورهای این دو سطر را با هم جمع کرده و داده‌های دو سطر را تنها در یک سطر نوشت. در ردیف ۱ آرماتور با شماره ۱۰ و طول ۲۴۸ سانتیمتر با ردیف ۹ تکراری است. تعداد آرماتور ردیف ۱ و ردیف ۹ را جمع کرده (۱۳۵=۲۳+۱۱۲) و کلاً در یک سطر بنویسید.

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

درج یا قرار دادن اطلاعات در جدول

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

 با آموزش excel امروز همراه باشید:

برای درج یک جدول ابتدا باید ستون هایی که می خواهید آنها را در جدول درج کنید را انتخاب کنید. سپس از زبانه Insert گزینه Table را کلیک کنید. در تصویر زیر سلول های G7 تا H14 انتخاب شده اند.

 

 

اکنون از زبانه Insert گزینه Table را کلیک کنید. در این صورت پنجره ای باز می شود که سوال «Where is the data for your table» به معنای «داده های شما برای جدول کجاست؟» از شما پرسیده می شود. در همین پنجره سلول های G7 تا H14 نوشته شده است. با کلیک بر روی دکمه OK جدول شما ایجاد می شود.

 

 زبانه مخفی Design به زبانه های موجود  در اکسل اضافه شده است. گزینه های این زبانه برای ویرایش جدول درج شده به کار می رود. مثلا از گزینه های بخش Table Styles برای تفکیک رنگی سلول ها و از گزینه های بخش Table Styles Optionبرای متمایز کردن سلول های داخلی با یکدیگر استفاده می شود.

 

درج تصویر و اشکال گرافیکی در اکسل

برای درج تصویر در یک سند اکسل از زبانه Insert بر روی گزینه Picture کلیک کنید. در این صورت پنجره ای باز می شود که می توانید تصویر مورد نظرتان را از حافظه کامپیوتر انتخاب و با کلیک بر روی دکمه OK آن را در سند درج کنید.

 

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

 

درج نمادها در اکسل

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

 

وارد کردن اطلاعات خارجی به برگه ها

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

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

 

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

برای این کار از گزینه From Text استفاده می کنیم.

 

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

 

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

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

 

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

 

پس از پایان کار، بر روی دکمه Finish کلیک کنید. در این صورت پنجره محاوره ای Import Data باز می شود که آدرس سلولی که قرار است اولین داده را در خود جای دهد در آن نشان داده می شود. با کلیک بر روی دکمه OK اطلاعات متنی در اکسل درج می شود اما پیش از آن می توانید آدرس نخستین سلول را تغییر دهید.

 

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

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

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

 

از منوی باز شده، گزینه Hide and Unhide را انتخاب کنید. در مقابل این گزینه و در بخش نخست آن، سه عبارت Hide Rows و Hide Columns و Hide Sheets قرار دارد که به ترتیب برای مخفی کردن سطر، ستون و برگه به کار می رود.

 

آشکار کردن سطرها، ستون ها و برگه ها

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

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

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

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

 

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

 

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

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

 

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

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

 

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

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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

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

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

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

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

 

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

۱۱. ایجاد اعداد تصادفی با تابع RAND                            

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

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

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

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

نمودار میله ای:

هنگامی که داده ها یا متغیرهای گسسته یا طبقه‌بندی شده داریم، نمودار میله‌ای یک نمودار گویا برای نشان‌‌دادن داده‌هاست. برای مثال تعداد فرزندان خانواده یا تعداد افرادی که به یک سوال پاسخ مثبت داده‌اند، داده‌های گسسته‌اند. متغیر دما را می‌توان به عنوان مثال به سه دسته‌ی 34-25، 44-35 و 54-45 طبقه بندی کرد. برای تحلیل بسیاری از پرسش‌نامه‌ها نیز نمودار میله‌ای می‌تواند مناسب باشد.

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

 

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

 

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

 

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

 

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

 

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

 

 

مجددا با رفتن به قسمت insert و انتخاب Column یا Bar نمودار میله‌ای مورد نظرتان را انتخاب کنید. با کلیک روی نمودار مورد نظر، نمودار میله‌ای رسم می‌شود.

 

 

 

نمودار دایره‌ای:

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

 

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

با رفتن به قسمت Insert و انتخاب Pie نمودار دایره‌ای یا قطاعی مورد نظرتان را انتخاب کنید. با کلیک روی آن، نمودار دایره ای رسم می‌شود.

 

نمودار پراکنش:

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

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

 

 

سپس ستون‌های مورد نظر را انتخاب می‌

با رفتن به قسمت Insert و انتخاب الگوی Scatter نمودار مورد نظرتان را انتخاب کنید. با کلیک روی آن، نمودار پراکنش رسم می‌شود.

هم‌چنین برای پراکندگی داده‌هایی که دارای دو صفت هستند و مقایسه‌ی فراوانی، این نمودار مناسب به نظر می‌رسد.

نمودار خطی:

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

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

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

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

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

مرتب کردن اعداد با کمک Spin Button

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

گام اول: اعدادتان را منحصربفرد کنید:

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

 

گام دوم: یک Spin Button از تب Developer بیاورید.

گام سوم: با استفاده از فرمول CHOOSE, LARGE و SMALL ستون مرتب سازی شده را بسازید.

از آنجاییکه SPIN BUTTON را طوری تنظیم کردیم که فقط دو مقدار ۱ و ۲ را بگیرد در نتیجه می توانیم در اکسل با استفاده از فرمول CHOOSE مشخص کنیم که اگر عدد خروجی لینک  SPIN BUTTON یک بود از فرمول LARGE استفاده کند و اگر عدد دو برگردانده شد از فرمول SMALL استفاده کند.

=CHOOSE($G$2,LARGE($E$3:$E$10,$B3),SMALL($E$3:$E$10,$B3))

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

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

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

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

معرفی توابع جمع شرطی در اکسل

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

 

جمع شرطی چیست؟

جمع شرطی یعنی محاسبه مجموع چند عدد البته با توجه به شرط یا شروط موجود.

برای انتقال بهتر مفهوم جمع شرطی، در ادامه با ذکر چند مثال این مفهوم را شرح می‌دهیم.

مثال ۱) جدولی را در نظر بگیرید که میخواهید:در محدودهA1:A6  از جدول زیر جمع اعداد کوچکتر از ۲۸ را محاسبه کنید

برای محاسبه این جمع از تابع sum دراکسل نمی‌توان استفاده کرد زیرا یک شرط وجود دارد و آن این است که جمع اعدادی را محاسبه کنید که کوچکتر از ۲۵ باشند. برای حل این مشکل می‌توان از تابع Sumifدر excel  استفاده کرد.

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

=SUMIF(range, criteria,[sum_range])

range: همان محدوده‌ای می‌باشد که قصد داریم شرط را بر آن اعمال کنیم. این محدوده می‌تواند شامل عدد، نام، آرایه و یا محدوده‌ای از اعداد باشد. (سلول‌های خالی و کاراکترها محاسبه نمی‌شود). در مثال فوق محدوده A1:A6 است.

criteria: همان شرط ما می‌باشد. این شرط می‌تواند شامل عدد، عبارت، یک سلول، متن و تابع باشد. در مثال فوق شرط اعداد کوچکتر از ۲۸ است.

نکته ۱: کلیه عبارات و شرط‌های ریاضی باید داخل گیومه " " باشد مگر اینکه شرط شما عدد باشد.

نکته ۲: در شرایط مشابه مثال فوق چنانچه بخواهید بجای نوشتن عدد ۲۸ در تابع آدرس آن یعنی A6 را در تابع درج کرد باید شرط را بصورت زیر بنویسید:

"<"&A6

sum_range: این گزینه اختیاری می‌باشد. زمانی از این آرگومان استفاده می‌کنیم که Range  ما شامل اعداد نباشد و یا اینکه اعدادی که قصد داریم مجموع آن‌ها را محاسبه کنیم در محدوده range قرار نداشته باشند که در مثال بالا هیچ یک از دو حالت فوق برقرار نیست. چون هم Range ما شامل اعداد است و هم اینکه اعدادی که قصد داریم مجموع آن‌ها را محاسبه کنیم در محدوده range قرار دارند. بنابراین در مثال ۱ از آرگومان sum_range استفاده نمی‌کنیم.

بنابراین مطابق توضیحات بالا برای مثال ۱ تابع Sumif به صورت زیر نوشته می‌شود.

=Sumif(A1:A6,"<28")

خروجی فرمول بالا برابر است با ۶۰.

در مثال بالا فقط از دو آرگومان range و criteria استفاده شده‌است. اگر بخواهیم از هر ۳ آرگومان Range و Criteria و Sum_range استفاده کنیم مثال ۲ را مشاهده کنید.

---

مثال ۲) جدولی را در نظر بگیرید که مربوط به پخش اقلام در یک منطقه زلزله زده می‌باشد.  ومی خواهیم مجموع کل “آب معدنی”های توزیع شده در کلیه مناطق را محاسبه کنید

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

 

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

=Sumif(B2:B10,"آب معدنی",C2:C10)

مطابق با ساختار تابع ،Sumif محدوده یا Range مورد نظر سلول‌های B2:B10 هستند که عدد نمی‌باشد. اکسل باید این محدوده را با شرط مطابقت دهد.

 Criteriaیا شرط ما در اینجا “آب معدنی” می‌باشد.

اعدادی که قصد داریم جمع کنیم در محدوده range قرار ندارند بنابراین باید از آرگومان اختیاری sum_range استفاده کرد. Sum_range محدوده اعداد متناظر با Range می‌باشد یعنی C2:C10 که در صورت مطابقت شرط، اکسل آن‌ها را با هم جمع می‌کند.

جواب مثال فوق برابر است با ۴۸۰۰.

 

---

مثال ۳) در مثال ۲ جمع کل "آب معدنی" را در منطقه "جنوب" محاسبه کنید.

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

• ساختار تابع Sumifs به شکل زیر است:

=SUMIFS(sum_range, criteria_range1, criteria1,[criteria_range2, criteria2],…)

sum_range: محدوده‌ای که می‌خواهیم آن را جمع کنیم. (در مثال فوق محدوده C2:C10 می‌باشد)

criteria_range1: محدوده شرط اول (محدوده شرط اول ما در این مثال، نوع کالا است یعنی محدوده B2:B10)

criteria1: شرط اول (آب معدنی)

criteria_range2: محدوده شرط دوم (محدوده شرط دوم ما در این مثال، منطقه است یعنی محدوده A2:A10)

criteria2:شرط دوم (جنوب)

=Sumifs(C2:C10,B2:B10,"آب معدنی",A2:A10,"جنوب")

خروجی فرمول فوق برابر است با ۲۰۰۰.

---

مثال ۴) در مثال ۲ جمع کل "آب معدنی" را در مناطق "جنوب" و "شمال" محاسبه کنید.

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

در اینحالت باید مشابه مثال ۳ از تابع Sumifs استفاده نمود اما با کمی تفاوت. شاید بخواهید برای حل مثال فوق، در فرمول قبل شرط جدید یعنی منطقه "شمال" را اضافه کنید اما نمی‌توان اینکار را انجام داد. چون تابع Sumifs پس از بررسی شرط منطقه "جنوب"، شرط منطقه "شمال" را بررسی می‌کند یعنی باید هر دو شرط برقرار باشد که چنین چیزی ممکن نیست که در یک سلول هم جنوب باشد و هم شمال. از طرفی درون تابع Sumifs نمی‌توان از OR استفاده کرد. برای حل این مشکل دو راهکار وجود دارد:

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

=Sum(Sumifs(C2:C10,B2:B10,"آب معدنی",A2:A10,{"جنوب","شمال"})

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

=Sumifs(C2:C10,B2:B10,"آب معدنی",A2:A10,"جنوب")+Sumifs(C2:C10,B2:B10,"آب معدنی",A2:A10,"شمال")

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

 

---

مثال ۵) محاسبه مجموع اعداد بین دو تاریخ مشخص:

مطابق جدول زیر مجموع مقادیر ستون B در صورتیکه بین دو تاریخ 3/4/2014 و 5/10/2014 باشند را محاسبه کنید.

 

در واقع در این مثال هم دو شرط وجود دارد که بصورت زیر در تابع Sumifs قرار داده می‌شوند.

=SUMIFS(B2:B8,A2:A8,">=2014-03-04",A2:A8,"<=2014-05-10")

خروجی فرمول بالا برابر است با ۹۱.

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

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

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

روش ۱) فرمول نویسی:

بوسیله توابع Row ،Index و Column می‌توان اینکار را انجام داد.

ابتدا پس از کپی داده‌ها درون اکسل، باید مشخص کنید که می‌خواهید داده‌ها به چند ستون تبدیل شوند، تعداد این ستون‌ها را با n نمایش می‌دهیم. مثلا  می‌خواهیم تغییرات دما و فشار در طول زمان نشان داده شود بنابراین n=۳ ستون باید ایجاد شود. ستون زمان، ستون دما و ستون فشار.

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

=INDEX(محدوده,n*(ROW(A1)-1)+COLUMN(A1))

چون برای مثال بالا محدوده A1:A9 و n=3 است بنابراین فرمول بصورت زیر اصلاح می‌شود. البته دقت کنید که محدوده را در فرمول بالا با قرار دادن علامت $ در اطراف آن باید ثابت نگه داشت تا تغییر نکند.

=INDEX($A$1:$A$9,3*(ROW(A1)-1)+COLUMN(A1))

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

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

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

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

=MOD(ROW(A1)+n-1,n)

که برای مثال بالا بجای n باید عدد ۳ قرار داد.

=MOD(ROW(A1)+2,3)

تابع MOD:
تابع MOD برای تعیین باقیمانده تقسیم استفاده می‌شوند. در واقع این تابع دو ورودی را دریافت می‌کند و خروجی آن برابر است با میزان باقی‌مانده ناشی از تقسیم ورودی اول بر ورودی دوم. برای مثال اگر در سلول A1 عبارت زیر را وارد کنید، خروجی آن برابر با ۱ می‌شود، زیرا باقیمانده تقسیم ۴ بر ۳ برابر است با ۱.

=MOD(4,3)

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

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

 

در پنجره جدید باز شده کدهای زیر را کپی کنید.

SubOneCol2nCols()'n = 3
m = 9
k = 1
For i = 1 To m / n
For j = 1 To n
Cells(k, 1).Select
  Selection.Copy
   Cells(i, j + 2).Select
    ActiveSheet.Paste
    k = k + 1
    Next
Next
End Sub

دقت کنید که در کد فوق باید بجای n و m با توجه به داده‌های خود عدد بگذارید. n تعداد ستون‌ها و m تعداد کل داده‌ها است که در مثال ما n=3 و m=9.

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

از کد زیر نیز می‌توانید استفاده کنید:

SubTrsPose()
Dim Area As Range, i As Long
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For Each Area In Range("A1:A" & LR).SpecialCells(xlCellTypeConstants).Areas
    i = i + 1
    Range("B" & i).Resize(, Area.Rows.Count).Value = Application.Transpose(Area)
Next Area
End Sub

روش ۴) با استفاده از افزونه‌های اکسل

• افزونه ASAP Utilities:

پس از دانلود و نصب این Add-in، تب جدیدی با عنوان ASAP Utilities به اکسل اضافه می‌شود.

 

نحوه استفاده از این افزونه در این سایت توضیح داده شده است.

• افزونه Kutools for Excel:

افزونه Kutools for Excel نیز مشابه افزونه قبلی قابلیت تبدیل یک ستون به چند ردیف را دارد. پس از دانلود و نصب این Add-in، تب جدیدی به اکسل با عنوان Kutools اضافه می‌شود. نحوه استفاده از این افزونه در این سایت توضیح داده شده است.

 

---

 

 

 

۱- نصب افزونه Kutools for Excel که در بالا به آن اشاره شد

۲- ماکرو نویسی: با استفاده از ماکرو زیر می‌توانید عملیات فوق را انجام دهید. نحوه استفاده از ماکرو در بالا در قسمت روش ۳ توضیح داده شده است. فقط باید دقت کنید که عامل توقف حلقه وجود سلول خالی است نه Hello، بنابراین وجود سلول خالی بین مجموعه‌ها الزامی است. البته باید به جای عدد ۴، تعداد ردیف‌هایی که می‌خواهید ایجاد شود را قرار دهید. که در مثال فوق ۴ است.

SubOneCol2Cols()
k = 1
For i = 1 To 4
j = 1
   Do Until IsEmpty(Cells(k, 1))
   Cells(k, 1).Select
   Selection.Copy
   Cells(i, j + 2).Select
    ActiveSheet.Paste
    k = k + 1
    j = j + 1
    Loop
    k = k + 1
Next
End Sub

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

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

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

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

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

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

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

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

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

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

   

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

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

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

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

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

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

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

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

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

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

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

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

  =COUNT(C2:C11)

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

  =COUNTIF(C2:C11,"Available")

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

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

  نکات مهم:

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

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

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

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

فیلتر کردن اطلاعات در اکسل

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

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

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

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

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

 

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

• گزینه Filter by Color که فیلتر بر اساس رنگ می‌باشد و با حرکت فلش ماوس بر روی این گزینه کادر زیر مجموعه‌های آن باز خواهد شد. چون از نام این فیلتر پیداست که چه کاری انجام خواهد داد مثالی از آن نخواهیم زد.
• گزینه Text Filter که برای فیلتر نمودن نوشته‌ها بکار می‌رود.

 

 بر اساس حرف (س) نام‌ها را فیلتر کنید . برای این کار ابتدا به روی آرم فیلتر که در ابتدای ستون قرار دارد کلیک کرده و از کادر باز شده آن ، فلش ماوس را به روی گزینه Text Filter می‌بریم تا کادر کشویی دیگری در کنار کادر فیلتر باز شود. از کادر دوم انتخاب می‌کنیم که این ستون بر چه اساسی باید فیلتر شود.

 

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

…Equals (برابر با…)

…ِDoes Not Equals (برابر نیست با…)

…Begins With  (شروع شدن با…)

…Ends With  (تمام شدن با…)

…Contains (شامل … می‌شود)

…Does Not Contain  (شامل … نمی‌شود)

…Custom Filter ( فیلتر سلیقه‌ای)

در این بخش از آموزش  میخواهیم فیلتر بر اساس حرف (س) را اعمال کنیم بنابراین به گزینه …Begins With ( شروع می‌شود با…) نیاز خواهیم داشت. با انتخاب این گزینه پنجره‌ای باز خواهد شد. در این پنجره و در کادر مشخص شده در تصویر زیر حرف (س) را وارد کنید و سپس با کلیک بر گزینه Ok فیلتر روی ستون نام خریدار اعمال میشود.

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

حال با نگاه به برگه (Sheet) مورد نظر، نتیجه کار را مشاهده خواهید کرد.

 

---

در جدول قبل می‌خواهیم بجز نام‌هایی با حرف اول (س و الف) آغاز می‌گردند، مابقی را فیلتر کنیم. برای این کار مانند قبل روی آرم Filter روی ستون مورد نظر (ستون نام‌ها) کلیک کرده و با باز شدن کادر آن گزینه Custom Filter را انتخاب کرده و در پنجره باز شده  گزینه Or را انتخاب کرده و در کادرهای سمت راست بالا و پایین حرف‌های (س) و (الف) را وارد می‌کنیم.

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

 

---

حال اگر بخواهیم بجز قیمت‌های کل بین (۵۰۰۰۰) تا (۱۰۰۰۰۰) را فیلتر کنیم، یا به عبارتی دیگر فیلتر عددی را اعمال کنیم:

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

 

…Equals (برابر با…)

…Does not Equals (برابر نبودن با…)

…Greater Than (بزرگتر از…)

…Greater Than  or Equal To  (بزرگتر یا مساوی با…)

…Less Than (کوچکتر از…)

…Less Than or Equal To (کوچکتر یا مساوی با…)

…Between (بین…)

…Top 10 (ده مورد اول… )

…Above Average (بالاتر از میانگین…)

…Below Average (پایین‌تر از میانگین…)

…Custom Filter (فیلتر سلیقه‌ای…)

برای فیلتر کردن اعداد بزرگ‌تر از ۵۰۰۰۰ تا کوچک‌تر از ۱۰۰۰۰۰ از گزینه …Between در پنجره مربوطه مانند تصویر زیر عمل می‌کنیم:

 

 

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

 

---

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

روش اول این است که از سربرگ Home به بخش Sort & Filter رفته و با کلیک بر روی این گزینه، از کادر باز شده گزینه Clear را انتخاب کنید.

 

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

 

---

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

پس از انتخاب ستون‌های فیلتر شده، به سربرگ Data مراجعه کرده و در کادر گروه Sort & Filter بر روی گزینه Clear کلیک کنید.

 

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

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

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

وقتی که می خواهید کاربرگ را انتقال و یا کپی کنید، بر روی زبانه کاربرگ راست کلیک کنید و در کادری که نمایش داده می شود گزینه move or copy را انتخاب کنید.                                                                                                                          

توجه:همچنین شما می توانید در نوار سربرگ صفحه اصلی Excel در بخش Cells، دکمه Format را انتخاب کنید. سپس، از منوی کشویی که باز می شود از قسمت Organize Sheets گزینه Move or Copy Sheet… را انتخاب کنید.

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

اگر شما در حال انتقال و یا کپی کردن برگه و یا یکی دیگر از کاربرگ های موجود هستید، قبل از آن شما می بایست برای انتقال و یا کپی برگه را در لیست برگه های قبل انتخاب کنید. انتخاب کردن (move to end) برای وارد شدن به کاربرگ بعد از تمام برگه های موجود در کاربرگ لازم است.

برای کپی کردن برگه به تنهایی روی آن کلیک کنید تا باکس Move or Copy نمایش داده شود و تیک گزینه Create a copy را بزنید و سپس Ok کنید.

 

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

 

برای کپی کردن یا انتقال کاربرگ انتخاب به کارنامه جدید روی آن کلیک کنید تا باکس Move or Copy ظاهر شود در فیلد To book  بر روی new book کلیک کنید و در آخر تیک گزینه Create a copy را بزنید و سپس Ok کنید.

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

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

معرفی تابع Match در نرم‌افزار اکسل

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

 

در excelبا استفاده از تابع Match می‌توان موقعیت یک مقدار را در یک محدوه مشخص، تعیین نمود. این محدوده باید بصورت برداری، یعنی تنها دارای یک سطر یا یک ستون باشد. البته امکان جستجو در مجموعه نیز وجود دارد. مثلا فرمول زیر حرف b را در مجموعه {a,b,c} جستجو می‌کند و موقعیت آن‌ یعنی مقدار ۲ را برمی‌گرداند، چون حرف b دومین حرف مجموعه فوق است.

=MATCH("b",{"a","b","c"},0)

ساختار تابع Match به شکل زیر است:

=MATCH (lookup_value, lookup_array,[match_type])

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

• اگر در این قسمت چیزی نوشته نشود یا عدد ۱ درج شود، اعدادی که در آن‌ها جستجو انجام می‌شود باید از کوچک به بزرگ مرتب شده باشد. اگر در محدوده مورد جستجو، آرگومان اول وجود نداشته باشد، تابع، شماره سطر نزدیک‌ترین عبارت کوچک‌تر از آرگومان اول را باز می‌گرداند.
• اگر عدد ۰ نوشته شود، تابع شماره سطری را که اولین تطابق رخ دهد را بر می‌گرداند. در صورتیکه مقدار موردنظر در محدوده تعیین شده یافت نشود خطای N/A# نمایش داده می‌شود.
• اگر ۱- نوشته شود، اعدادی که در آن‌ها جستجو انجام می‌شود باید از بزرگ به کوچک مرتب شده باشد. اگر در محدوده مورد جستجو، آرگومان اول وجود نداشته باشد، تابع، شماره سطر نزدیک‌ترین مقدار بزرگ‌تر از آرگومان اول را باز می‌گرداند.

نکته ۱: مرتب شدن داده‌ها بصورت صعودی یعنی از کوچک به بزرگ باید مطابق ترتیب زیر باشد:

..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

حالت نزولی برعکس ترتیب بالا است.

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