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

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

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

آدرس دهی در اکسل و تقسیم بندی انواع داده

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

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

 در سلول‌های B8 و B9 با اینکه مقدار سلول‌های C8 و C9 مساوی و برابر ۲/۴۷ است، ولی به واسطه تغییر در آدرس، داده متغییر محسوب می‌شوند.

 

داده ثابت: داده‌ای که فارغ از مقدار، صرفاً آدرسش در آرگومان فرمول ثابت باشد داده ثابت گفته می‌شود. ممکن است در فرمولی از یک داده با آدرس ثابت استفاده شود ولی مقدار داده به وسیله کاربر یا توابع دیگر (مانند تابع RAND)  تغییر کند، این داده نیز ثابت در نظر گرفته می‌شود. اعداد ثابتی که بعضاً در فرمول‌ها استفاده می‌شوند نیز، نوعی داده ثابت هستند.

آدرس دهی در اکسل چگونه انجام می‌شود؟

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

آدرس دهی در اکسل به صورت نسبی

 در سلول B6 نوشته شده است که وزن کل آرماتور شماره ۱۶ را با استفاده از طول (سلول E6)، تعداد (سلول D6) و وزن واحد (سلول C6) آن محاسبه می‌کند. این یک فرمول ساده بوده و با استفاده از عملگر ضرب مقدار سلول‌های D6، E6 و C6 را در هم ضرب کرده و حاصل را در سلول B6 می‌نویسد. در این فرمول آدرس ها به صورت نسبی نوشته شده‌اند و اکسل برای اجرای این فرمول (با توجه به آدرسی که داده‌ شده است) به ترتیب مقادیرِ یک ستون، دو ستون و سه ستون بعد از سلول فرمول را در هم ضرب می‌کند. یعنی سطرها ثابت بوده و سلول‌ داده‌ها، فواصل یک، دو و سه ستونی از سلول فرمول دارند.

اگر این فرمول را در سلول B7 کپی کنید، با توجه به نسبی بودن آدرس ها، این بار اکسل مقدار سلول‌های D7، E7 و C7 را در هم ضرب کرده و حاصل را در سلول B7 می‌نویسد.

 

باز اگر این فرمول را در سلول A8 کپی کنید، با توجه به نسبی بودن آدرس ها، این‌ بار نیز اکسل، مقدار سلول‌های C8، D8 و B8 را در هم ضرب کرده و حاصل را در سلول A8 می‌نویسد. و چون سلول B8 مقداری ندارد، نتیجه صفر خواهد بود.

 

 

آدرس دهی در اکسل به صورت مطلق

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

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

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

 

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

 

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

در آدرس دهی نسبی در اکسل چه اتفاق می‌افتد؟

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

چگونه یک آدرس دهی در اکسل را مطلق کنیم؟

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

 آدرس دهی در اکسل به صورت مطلق و ترکیبی

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

 

در جدول فوق هر یک از مقادیر مصالح (سیمان، ماسه، نخودی و بادامی) در طرح اختلاط، باید به حجم بتن ریزی ضرب شوند تا مقدار کل مصالح مصرفی برای هر طرح بدست آید. با نوشتن فرمول در سلول D7 مقدار سیمان مصرفی به ازای ۵۸۵ متر مکعب بتن ریزی محاسبه می‌شود. با تعمیم این فرمول به صورت سطری (در سلول‌های F7، E7 و G7 ) برای اینکه با جابجایی فرمول، آدرس سلول مقدار بتن ریزی (۵۸۵ متر مکعب) تغییری نکند، این آدرس به صورت مطلق و با محدود کردن حرکت در جهت سطرها نوشته شده است. برای طرح شماره ۲ طرح اختلاط، فرمول در سلول D8 نوشته شده با این تفاوت که آدرس حجم بتن ریزی کاملاً مطلق نوشته شده است. بسته به مورد از هر ترکیب آدرس دهی مطلق می‌توان استفاده کرد. با کنترل مناسب ، نتایج یکسان خواهد بود.

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

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

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

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

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

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

 

تجزیه متن یک سلول به چند سلول

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

۱- نوشتن فرمول 

۲- ابزار در در

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

 

 

 

تجزیه متن یک سلول به چند سلول

 

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

 

۱- نوشتن فرمول 

 

۲- ابزار Text To Columns

 

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

 

• تابع Right:

 

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

 

=RIGHT(text,num_chars)

 

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

 

آرگومان Num_chars: تعداد کاراکترهایی است که می‌خواهید از انتهای رشته متن استخراج شود.

 

 

 

• تابع Left:

 

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

 

=LEFT(text,num_chars)

 

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

 

آرگومان Num_chars: تعداد کاراکترهایی است که می‌خواهید از ابتدای رشته متن استخراج شود.

 

 

 

• تابع Mid:

 

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

 

=MID(text,start_num,num_chars)

 

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

 

 

 

•  تابع Len:

 

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

 

=LEN(text)

 

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

 

 

 

---

 

تجزیه متن یک سلول

 

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

 

روش اول) از طریق نوشتن فرمول

 

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

 

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

 

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

 

 

 

مثال) جداسازی نام و نام‌ خانوادگی

 

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

 

 

 

---

 

روش دوم) از طریق ابزار Text To Columns

 

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

 

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

 

1. سلول‌هایی که قرار است تجزیه شوند را انتخاب نمایید.
2. از تب Data، بر روی گزینه Text to columns کلیک کنید.
3. گزینه Fixed Width را انتخاب نمایید.
4. بوسیله کلیک کردن در نقاطی که می‌خواهید کاراکترها از یکدیگر جدا شوند، ستون‌ها را تجزیه کنید.
5. در فیلد Destination آدرس سلولی که می‌خواهید داده‌ها درج شوند را وارد کنید و بر روی Finish کلیک نمایید.

 

 

 

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

 

1. سلول‌هایی که قرار است تجزیه شوند را انتخاب نمایید.
2. از تب Data، بر روی گزینه Text to columns کلیک کنید.
3. گزینه Delimited را انتخاب نمایید.
4. گزینه Space را انتخاب کنید.
5. در فیلد Destination آدرس سلولی که می‌خواهید داده ها درج شوند را وارد کنید و بر روی Finish کلیک نمایید.

 

 

 

---

 

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

 

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

 

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

 

 

 

روش دوم) از تابع CONCATENATE استفاده می‌شود. ورودی های این تابع داده‌هایی است که باید با یکدیگر ترکیب شوند. برای مثال زیر این تابع سه ورودی دارد؛ ورودی اول نام، ورودی دوم یک فاصله و ورودی سوم نام خانوادگی می باشد.

 

 

 

 

 

Text To Columns

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

• تابع Right:

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

=RIGHT(text,num_chars)

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

آرگومان Num_chars: تعداد کاراکترهایی است که می‌خواهید از انتهای رشته متن استخراج شود.

 

• تابع Left:

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

=LEFT(text,num_chars)

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

آرگومان Num_chars: تعداد کاراکترهایی است که می‌خواهید از ابتدای رشته متن استخراج شود.

 

• تابع Mid:

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

=MID(text,start_num,num_chars)

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

 

•  تابع Len:

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

=LEN(text)

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

 

---

تجزیه متن یک سلول

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

روش اول) از طریق نوشتن فرمول

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

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

 

 

مثال) جداسازی نام و نام‌ خانوادگی

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

 

---

روش دوم) از طریق ابزار Text To Columns

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

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

1. سلول‌هایی که قرار است تجزیه شوند را انتخاب نمایید.
2. از تب Data، بر روی گزینه Text to columns کلیک کنید.
3. گزینه Fixed Width را انتخاب نمایید.
4. بوسیله کلیک کردن در نقاطی که می‌خواهید کاراکترها از یکدیگر جدا شوند، ستون‌ها را تجزیه کنید.
5. در فیلد Destination آدرس سلولی که می‌خواهید داده‌ها درج شوند را وارد کنید و بر روی Finish کلیک نمایید.

 

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

1. سلول‌هایی که قرار است تجزیه شوند را انتخاب نمایید.
2. از تب Data، بر روی گزینه Text to columns کلیک کنید.
3. گزینه Delimited را انتخاب نمایید.
4. گزینه Space را انتخاب کنید.
5. در فیلد Destination آدرس سلولی که می‌خواهید داده ها درج شوند را وارد کنید و بر روی Finish کلیک نمایید.

 

---

ترکیب چند سلول در یک سلول

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

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

 

روش دوم) از تابع CONCATENATE استفاده می‌شود. ورودی های این تابع داده‌هایی است که باید با یکدیگر ترکیب شوند. برای مثال زیر این تابع سه ورودی دارد؛ ورودی اول نام، ورودی دوم یک فاصله و ورودی سوم نام خانوادگی می باشد.

 

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

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

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

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

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

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

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

 

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

 

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

 

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

 

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

 

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

 

 

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

 

 

 

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

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

 

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

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

 

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

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

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

 

 

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

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

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

نمودار خطی:

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

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

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

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

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

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

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

آموزشی که آماده کردیم راه حل این مشکل است .
برای این کار مطابق دستور العمل زیر عمل می کنیم
1- ایجاد جدول ابتدا جدولی را طراحی کنید
مطابق معمول هدف از ایجاد اینچنین نمودار هایی زیبایی و جلب نظر مخاطب است .پس این بار هم پاره ای از اقدامات برای زیبایی نمودار خود انجام می دهیم .
2- از قسمت INSERT گزینه SHAPES را انتخاب می کنیم و شکل مربع را بر می گزینیم .و رنگ خاکستری را برای ان انتخاب می کنیم
3- از مسیر فوق شکل مثلث را انتخاب کرده (رنگ مشکی )
4- و در نهایت شکل یک مربع جهت دار را انتخاب می کنیم (رنگ سیز )


5- حالا از قسمت DEVELOPERدراکسل( برای اضافه کردن این قسمت در ریبون خود مسیر زیر را دنبال کنید :
FILE – OPTION – CUSTOMIZE RIBBON –
و از کادر سمت راست تیک مربوطه را بزنید )
وارد قسمت INSERT گزینه کامبو باکس را انتخاب می کنیم

روی باکس ایجاد شده کلیک راست کرده و گزینه FORMAT CONTROL را انتخاب و در کادر ظاهر شده در قسمت INPUT RANGE محدوده REGION را انتخاب میکنیم و در قسمت CELL LINK یک سلول خالی اختصاص دهید .در اینجا ما سلول C33 را اختصاص دادیم .سپس OK
حالا شما از کرکره ایجاد شده هر محدوده ای را انتخاب کنید عدد متناظر با آن محدوده در سلول C33 نمایش داده میشود

 

 

 

6-
دقت داشته باشید که در جدول اول عدد 4 مقابل SELECTED REGION همان خانه C33 است که در CELL LINK انتخاب کرده ایم.

در فرمول های فوق به دلیل اینکه هر محدوده از جدول در قسمت DEFINE برای اکسل تعریف شده نام آن نمایش داده شده است تا شما نیز بهتر متوجه محدوده انتخاب شده شوید .شما می توانید محدوده ای که  نام آن نمایش داده شده را به صورت دستی و یا با موس انتخاب کنید .
لازم به ذکر است که فرمولهای index و match از خانوانده lookup بوده و برای جستجو بکار می روند .در اینجا فرمول index به این معنی است که مثلاً از ستون then عدد واقع در ردیف 4 را بخوان ..
در شکل های فوق جدول calculations برای معرفی و نمایش ، جدول break-up و xy نیز که در ادامه برا آنها نمودار های bar و scatter ترسیم خواهد شد .
7- برای رسم نمودار bar برای جدول break – up محدوده جدول را انتخاب کرده

 

و برای رسم نمودار scatter برای جدول x,y ابتدا محدوده جدول را انتخاب کرده

حالا نمودار های ایجاد شده را در مربع خاکستری رنگ که اول ایجاد کردیم قرار می دهیم .
8- در نمودار scatter فوق در نقاط ابتدا و انتهای نمودار یک دایره و یک فلش مشاهده می شود .
این نمودار با توجه به جدول x,y از چهار نقطه تشکیل شده است شما لازم است روی نتقه ابتدایی و انتهایی جداگانه کلیک کرده به طوری که یکی از انها فقط انتخاب شود حالا  مسیر را دنبال کنید layout – format selection

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

9-


ما در فرمول ها هر چیز را که بخواهیم به عنوان متن به نمایش در بیاید بین " " مینویسیم.
علامت & برا ی اتصال متن و فرمول استفاده می شود .
فرمول text : اگر بخواهید عددی را به فرمت خاصی نمایش دهید استفاده میشود

• شما برای کسب اطلاعات بیشتر می توانید در قسمت Help نرم افزار text را تایپ کرده سپس text function را انتخاب کرده تا با کارایی بیشتر این فرمول آشنا شوید .
در اینجا ما می خواهیم عدد مورد نظر به شکل درصد نشان داده شود
فرمول ABS : این فرمول قدر مطلق عدد را بر می گرداند
حالا زمان اختصاص این فرمول به کادر سبز رنک است . روی کادر سبز رنک کلیک کرده تا به حالت انتخاب در بیاید سپس در نوار فرمول ، فرمول زیر را بنویسید و اینتر را بزنید
=$C$46
حالا نمودار شما اماده است .                                                                 امیدوارم این آموزش اکسل  مورد استفاده شما قرار بگیرد و از آن لذت برده باشید

جابه جایی ، صفحه بندی و چاپ در اکسل

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

1-سلول یا منطقه ی مورد نظررا انتخاب می کنید .

2-در نوار ابزار اکسل ،روی نشانه برش (cut)،کلیک می کنید در این هنگام به دور منطقه ی انتخاب شده مرز متحرک ظاهر می شود که نشان میدهد ،منطقه ی مورد نظر بریده شده است.                                                                                 

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

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

1-سلول یا منطقه ی مورد نظررا انتخاب می کنید .

2-در نوار ابزار excel ،روی نشانه برش (cut)،کلیک می کنید در این هنگام به دور منطقه ی انتخاب شده مرز متحرک ظاهر می شود که نشان میدهد ،منطقه ی مورد نظر بریده شده است.                                                                                

 

 

3-در سلول مورد نظر برای انتقال کلیک کنید تا انتخاب شود.

 

4-روی نشانه چسباندن (paste) در نوار ابزار اکسل ،کلیک کنید.منطقه ی سلول مورد نظر،در مکان جدید چسبانده واز جای قبلی ناپدید می شود. 

 

 

 

 

 

کپی کردن در اکسل   

 

 

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

 

برای این کار، به روش زیر عمل کنید:

 

1-سلول یا منطقه مورد نظر را انتخاب کنید.

 

2- در نوار ابزار اکسل، روی نشانه برش (copy)، کلیک کنید. در این هنگام، به دور منطقه انتخاب شده مرز متحرکی ظاهر میشود که نشان میدهد منطقه مورد نظر، در تخته برش(clipboard) ویندوز ، قرار داده شده است. 

 

 

 

3- دور سلول مورد نظر برای انتقال ، کلیک می کنید تا انتخاب شود.

 

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

 

 

مرجع مطلق و مرجع نسبی در اکسل   

 

    

 

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

 

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

 

 

 

   

 

 

 

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

 

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

 

علامت دلاررا درنشانی سلول یامنطقه،می توان پیش ازحرف نشانی،پیش از

 

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

 

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

 

 

 

اگرفرمول مثلابه این شکل واردشود	وقتی فرمول به جای دیگری کپی شوداتفاق زیرمی افتد.
C1	هم ستون وهم سطرعوض می شود.
C1$	ستون عوض نمی شودولی عرض عوض می شود.
C$1	ستون عوض می شودولی سطرعوض نمی شود.
$1$	ستون وسطر،هیچ یک عوض نمی شوند.

 

 

 

 

 

شمامی توانید درحین وارد کردن نشانی سلول یامنطقه،مستقیماّ علامت $راتایپ کنیدویا می توانید پس ازتایپ آن،با زدن کلیدf4نشانی رابه صورت مطلق درآورید.

 

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

 

2-سلول B8 را در سلول B9،سلول C8 را در سلول C9 و سلول D8 را در سلول D9 کپی کنید.چه اتفاقی افتاد؟

 

3-روی سلول B9 دو بار کلیک کنید.آیا منطقه ی تابع جمع آن درست است؟روی سلول C9 و D9 هم دو بار کلیک کنید.آیا منطقه ی تابع جمع آن ها درست است؟چرا غلط است؟

 

سلول های B9،C9،D9 را پاک کنید.

 

4-اکنون روی سلول B8 کلیک کنید و در نوار فرمول، کل فرمول آن را انتخاب کنید.کلید F4 را فشار دهید.در نوار فرمول چه تغییری در فرمول مشاهده می کنید.چند بار کلید F4 را بزنید و تغییرات فرمول را مشاهده کنید.اکنون به قدری کلید F4 را بزنید تا فرمول به شکل کاملاً مطلق =SUM($B$5:$B$7) درآید.حالا کلید ENTER را بزنید.

 

5-اکنون سلول B8 را در سلول B9 کپی کنید.محتوای سلول B9 را با B8 مقایسه نمایید،نتیجه را شرح دهید.

 

6-روی سلول B9 دو بار کلیک کنید.منطقه یاب کدام منطقه را نشان می دهد؟حال روی سلول B8 دو بار کلیک کنید.منطقه یاب کدام منطقه را نشان می دهد؟علت چیست؟

 

7-فرمول های موجود در سلول های C8 و D8 را هم نسبی و در سلول های C9 و D9 کپی کنید.اگر این عمل را درست انجام دهید،محتوای سلول های بالا،با سلول های پایین باهم برابر خواهند بود.فایل را برای استفاده ی بعدی ذخیره کنید.اگر تمام فرمول را انتخاب کنید،F4 روی تمام آن عمل می کند اما اگر بخشی از فرمول را برگزینید،F4 روی همان بخش انتخاب شده عمل می کند.  

 

 

 

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

 

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

 

 

 

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

 

1 – در تمرین قبل ، در سلول B3 ، کلمه فروش را تایپ کنید که با سلول پایین آن فروش فروردین خوانده شود .

 

2 – اکنون دستگیره ی پرکردن سلول B3 را بکشید و تکمه ی ماوس را رها کنید . چه اتفاقی افتاد ؟ شرح دهید .   

 

 

 

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

 

 ( Drag & Drop)در ویرایش داده ها  

 

 

 

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

 

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

 

1-منطقه مورد نظر را با ماوس یا صفحه کلید،انتخاب کنید.

 

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

 

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

 

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

 

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

«لغو کردن» [Undo] و «انجام مجدّد 

 

 عمل [Redo] در اکسل 

 

اکسل ، هر کاری را که شما انجام می دهید، یادداشت می کند. بنابراین همیشه می داند که در اثر کدام عمل شما ،چه تغییری در کار برگ حاصل شده است.پس،همیشه می توانید آخرین عمل خود را لغو کنید و به محض انجام کار، همه چیز به یک مرحله قبل بر میگردد.به این خصوصیّت اکسل «لغو عمل»[Undo] می گویند.

 

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

 

بنابراین ، اگر شما پشیمان شدید و خواستید عملی را که لغو کرده بوده اید دوباره انجام دهید،می توانید این کار را از طریق خصوصیّت«انجام مجدّد عمل»[Redo] صورت دهید.

 

در منوی Edit هم فرمان UndoوRedo وجود دارد.

کاربر، همچنین می تواند چندین عمل را یک باره لغو و یا مجدّدا،اجرا کند. برای این کار، در نوار ابزار استاندارد اکسل، کنار  نشانه های UndoوRedo نشانه ای دیگر اضافه شده است که پیکان دارد و با کلید زدن بر روی آن، فهرستی از اعمال انجام شده ظاهر می شود و شما می توانید از این فهرست، هر چند گزینه ای را که می خواهید، انتخاب وUndoو Redoکنید. در شکل زیر در منوی Undo دو عمل اولی که  از فهرست انتخاب شده اند، حذف خواهند شد. لازم به ذکر است که عمل undo اکسل2003 حداکثر 16 مرحله قبل را نشان می دهد

چاپ  

اکسل می تواند به سادگی کاربرگ شما را چاپ کند . اما همیشه توصیه می شودذ پیش از چاپ چیزی را که میس خواهید چاپ شود ، پیش نگری (Preview) کنید .

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

برای پیش نگری چاپ  با ماوس روی کلید « پیش نگری » یا همان Preview print در نوار ابزار استاندارد کلیک کنید . بالافاصله کاربرگ به صورتی که چاپ خواهد شد در صفحه ، نمودار خواهد شد .                                                                         

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

در بالای پنجره پیش نگری چند نشانه ی دیگر هست که بر نحوه ی چاپ یا پیش نگری ، تاثیر می گذارد . و بدین شرح اند . 

  نشانه ی Next ( صفحه بعدی ) : با کلیک کردن روی آن ، به صفحه ی قابل چاپ بعدی خواهیم رفت .

  نشانه ی Previous    ( صفحه ی قبلی ) :  با کلیک کردن روی این نشانه ، به صفحه ی قابل چاپ قبلی خواهیم رفت .

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

  نشانه ی Page break preview ( پیش نگری مجزا شدن صفحات ) : با کلید زدن روی این نشانه ، نمای کوچک شده ای از کاربرگ ، ظاهر می شود که خطوط برش  چاپ در آن ترسیم شده است . و صفحات از هم مجزا شده اند . در زمینه ی صفحه هم شماره صفحه گذاشته شده است. 

  نشانه   Close  ( بستن ) : این نشانه پنجره ی پیش نگری چاپ را می بندد .

  نشانه ی Help   ( کمک ) : با زدن این نشانه ، امکسل در زمینه ی چاپ به شما کمک می کند . 

مشخص کردن نحوه ی چاپ

 

یکی از نشانه های منوی پیش نگری preview، که Page set up   است ، منویی با همین نام را بر صفحه ظاهر می کند که از طریق آن نیز برخی از پارامترهای چاپ ، تعیین می شود . در اینجا به آن منو پرداخته می شود . 

تعیین جهت صفحه:

 

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

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

اکنون میتوانید، اندازه حاشیه ها را در منوهای با عنوان top(بالا)،bottom (پایین)،left(چپ)،right(راست) تعیین کنید. اندازه حاشیه سر صفحه و پا صفحه را نیز میتوانید با تعیین عدد دقیق در زیر عنوان header(سر صفحه) وfooter (پا صفحه) تعیین کنید.

 

وسط در وسط کردن منطقه چاپی:

 

وقتی چیزی در صفحه چاپ میشود از سمت راست حاشیه چپ تراز میشود. اگر میخواهید فاصله راست و چپ چیزی که چاپ میشود از گوشه راست و چپ صفحه به یک فاصله باشد، در قسمت center on pageاز زبانه margins ، گزینه horizotally(به طور افقی) را انتخاب کنید. اگر میخواهید فاصله بالا و پایین صفحه با آنچه در آن چاپ میشود مساوی باشد، در همین زبانه ، از منو گزینه vertically (به طور عمودی) را انتخاب کنید. بلافاصله در شکل کوچک موجود در منو ، تاثیر این انتخاب ها دیده خواهد شد و در صفحه چاپی نیز ، به هنگام چاپ اعمال خواهد شد.

تعیین سر صفحه و پا صفحه چاپی: 

 

 برای تعیین سر صفحه و پا صفحه چاپی در منوی page set up ، زبانه header/footer را فعال میکنیم . 

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

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

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

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

 

از چپ به راست ،این نشانه ها به شرح زیر عمل می کنند:

 

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

 

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

 

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

 

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

 

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

 

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

 

با زدن این نشانه ،کد &(TAB) به سر صفحه یا پا صفحه افزوده می شود ودرهنگام چاپ ،نام کاربرگ به جای آن چاپ می شود

  تکرار سطر ها و ستون ها در نسخه های  چاپی                                                            

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

در این منو بخشی بنام print titles وجود دارد که در شکل بالا مشخص است .دراین بخش دو گزینه داریم که یکی rowz to repeat at top(سطرهایی که در بالا تکرار می شوند) و دیگریcolumns to repeat at edge(ستونهایی که در لبه تکرار می شوند )است.

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

1-پس از فعال کردن این بخش از منو در خانه ی مقابل عنوان rowz to repeat at top[ کلیک میکنید تا فعال شود .

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

3-در این منو روی سطر هایی که میخواهید در بالای هر صفحه ی چاپی تکرار شود کلیک می کنید.میتوانید یک یا چند سطر را انتخاب کنید.

4-اکنون روی تکمه کناری این منو که در شکل بالا نشان داده شده است کلید می زنید تا کل منو دوباره ظاهر شود

5-برای گزینه ی columns to repeat at edge نیز به همین ترتیب کار میکنید .

6-با استفاده از print preview حاصل کار را می بینید و اگر مناسب تشخیص دادید چاپ میکنید .

مشخص کردن ترتیب چاپ

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

 

1-در منوی page set up زبانه ی sheetرا فعال میکنید .

2- در بخشpage order دو انتخاب است:اگر مقابل گزینه یdown then over(اول ستون ،بعد سطر )را بزنید ، ابتدا کاربرگ به شکل عمودی می برد و بعد ،هر ستون را تکه تکه چاپ میکند و به سراغ ستون بعد می رود .حال ،اگر گزینه ی،over then down(اول سطر ،بعد ستون ) را انتخاب کنید ، کاربرگ را به شکل افقی تکه تکه میکند و بعد از چاپ هر تکه ،ان را جداگانه چاپ می کند.

چاپ منطقه ی خاصی از یک کاربرگ

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

1 – در منوی page set up زبانه ی sheetرا فعال کنید .

2 – حالا در خانه ی مقابل عنوان print areae ( محدوده ی چاپ ) را کلیک کنید تا فعال شود . 

3 – روی نشانه ی پنهان کردن موقت منو ، در کگوشه ی راست ، خانه ی کذکور ، کلیک کنید تا منو موقتا پنهتان شود  .

4 – حالا منطقه ی مورد نظر را با ماوس یا صفحه کلید ، انتخاب کنید و مجدداً  روی همان نشانه کلیک کنید .

5 – با زدن نشانه ی   Preview print نتیجه را ببینید .

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

نکات کاربردی اکسل در حسابداری

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

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

ترفند شماره 3 اکسل: نمایش همه فرمول‌ها
با زدن کلید ` + CTRL حالت شیت اکسل عوض می‌شود و تمامی فرمول ها را به شما نماش می‌دهد. فراموش نکنید که با دوباره زدن همین کلید مجدد به حالت عادی شیت بر خواهید گشت.
نکته: علامت ` را در سمت چپ عدد 1 کیبورد کامیپوتر باید پیدا کنید. روی این کلید علامت ~ هم می بینید.)

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

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

ترفند شماره 6 اکسل: اضافه و حذف یک سطر یا ستون
تقریبا یک کار مهم در اکسل اضافه و یا کم کردن سطر و ستون ها است . برای اینکار می توانید به راحتی از کلید- + CTRL (کنترل و منها) برای حذف و برای اضافه کردن = + CTRL+SHIFT (کنترل شیفت مساوی) برای اضافه کردن استفاده کنید.
توجه: اگر قبل از زدن این کلیدها سطر یا ستونی را انتخاب کرده باشد خود اکسل متوجه می‌شود منظور شما چیست و دیگر پنجره تصویر متحرک را نمایش نمی دهد.

ترفند شماره 7 اکسل: تنظیم عرض ستون‌ها در excel
فکر کنم که تقریبا همه شما این تکنیک را بلد هستید، اما بگذارید که برای آنهایی که آشنا نیستم بگویم که اگر بخواهید اندازه عرض ستون‌های یک لیست اکسل طوری تنظیم شود که متناسب با نوشته های آن ستون باشد (یعنی عرض ستون برابر شود با عرض بزرگترین نوشته) آنوقت کافیست که بر روی مرز بین دو ستون Double-click کنید.

ترفند شماره 8 اکسل: جابجا شدن سریع بین شیت ها
وقتی که شیت های شما زیاد است و می خواهید یک شیت خاص را از روی ظاهرش (نه اسم آن) پیدا کنید باید تک تک شیت ها را ببیند. خوب یک کلید عالی برای جابجا شدن بین شیت های اکسل داریم Ctrl + PageUP و Ctrl + pageDown

ترفند شماره 9 اکسل: Double-click بر روی ابزار Format Painter
تقریبا همه کاربران اکسل، آتلوک و ورد با ابزار Format Painter آشنا هستند و می دانند که این ابزار برای copy-paste کردن فرمت (تنظیمات ظاهر) استفاده می شود و جالب است که اکثر آنها نمی دانند که اگر روی این ابزار Double-Click کنند ، می توانند چندی بار از آن در جاهای مختلف استفاده نمایند.                                                                                                                 امید واریم این آموزش برایتان مفید بوده باشد

مرتب کردن اعداد با کمک 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 نام محصول را فراخوانی کند.

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

فیت کردن دو یا چند نمودار در نرم افزار Excel

اکثر کاربران نرم‌افزار اکسل برای یک بار هم که شده با برازش منحنی برای داده‌های x و y یا Trend Line برخورد داشته‌اند. در واقع Trend Line به شما کمک می‌کند تا علاوه بر تشخیص روند تغییر داده‌ها، بتوانید تا حدودی وضعیت داده‌ها را پیش‌بینی (Forecasting) کنید. در ادامه مطلب با آموزش همراه شوید تا علاوه بر جزئیات Trend Line، با توابع کاربردی اکسل برای برازش منحنی نیز آشنا گردید.

 

از Trend Line درEXCEL فقط می‌توان در منحنی‌های Area ،Bar ،Column ،Line و XY استفاده کرد.به خاطر داشته باشید که نمی‌توانید در نمودارهای 3D ،Radar ،Pie ،Doughnut و Bubble از Trend Line استفاده کنید.

برای اضافه‌ کردن Trend Line، پس از راست کلیک کردن روی منحنی داده‌ها، گزینه‌ی Add Trendline را انتخاب کنید تا پنجره Format Trendline باز شود.

 

در پنجره Format Trendline

 

در قسمت راست پنجره Format Trendline یعنی قسمت Trendline Options، بخش‌های زیر وجود دارد:

بخش اول: بخش Trend/Regression Type انواع Trendline‌ها را نشان می‌دهد که به شرح زیر است:

• Exponential / نمایی؛ با فرمول Y=C.e^bx که b و c اعداد ثابت هستند.

          - نکته: هنگامی که داده‌ها شامل اعداد منفی یا صفر باشند قابل استفاده نیست!

• Linear / خطی؛ با فرمول Y=m.x+b که m شیب خط و b عدد ثابت (عرض از مبدا) است.

• Logarithmic / لگاریتمی؛ با فرمول Y=c.Lnx+b که c و b اعداد ثابت هستند. 

• Polynomial / چند جمله‌ای؛ با فرمول Y=b+c₁x+c₂x²+c₃x³+...+c_nxⁿ که در آن c عدد ثابت است.

• Power / توانی؛ با فرمول Y=C.x^b که b و c اعداد ثابت هستند.
  - نکته: هنگامی که داده‌ها شامل اعداد منفی یا صفر باشند قابل استفاده نیست!

• Moving Average / میانگین متحرک؛ با فرمول F_t=(A_t+A_t-1+...+A_t-n+1)/n

بخش دوم، بخش TrendLine Name می‌باشد.

بخش سوم، بخش Forecast یا پیش‌بینی می‌باشد که بر اساس نوع معادلات انتخابی در بخش اول، yهای قبل و یا بعد متناظر با xهای داده شده را پیش بینی می‌کند.

Set Intercept هم برای تعیین عرض از مبداء دلخواه می‌باشد.

با تیک زدن دو گزینه آخر یعنی Display Equation on chart و Display R-squared value on chart، به ترتیب معادله و ضریب رگرسیون (R²) متناظر با نوع Trendline انتخاب شده، روی نمودار نمایش داده می‌شود. در رگرسیون خطی ضریب رگرسیون مجذور ضریب همبستگی (R) است.

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

۱- معادله‌ای مناسب است که ضریب رگرسیون آن نزدیک به یک باشد مثلا 0.99.

۲- اکسل بصورت پیش فرض، ضرایب معادله‌ را تا ۲ رقم اعشار نمایش می‌دهد. برای اینکه بتوانید با استفاده از معادله، y متناظر با یک x را محاسبه کنید برای دقت بیشتر باید از معادله‌ای استفاده کنید که تعداد ارقام اعشاری بیشتری داشته باشد. برای این کار مطابق شکل زیر روی معادله خط، راست کلیک کنید و گزینه Format trendline label را انتخاب کنید.

 

 

در پنجره باز شده زیر در قسمت Category گزینه Number را انتخاب و در قسمت Decimal places تعداد ارقام بعد از ممیز را افزایش دهید. دکمه Close را بزنید و از معادله جدید استفاده کنید.

 

---

علاوه بر استفاده گرافیکی از ابزار Trend Line، می‌توان از توابع اکسل نیز اطلاعات مفیدی بدون رسم نمودار به دست آورد.
۱- تابع Slope: محاسبه شیب رگرسیون خطی.

=SLOPE(Known Y values, Known X values)

برای مثال زیر شیب خط تقریبا 2.15 می‌باشد.

=SLOPE(B2:B6,A2:A6) = 2.15

 

۲- تابع Intercept: محاسبه عرض از مبدا رگرسیون خطی.که برای مثال بالا تقریبا 0.47- می باشد.

=INTERCEPT(Known Y values, Known X values)

=INTERCEPT(B2:B6,A2:A6) = -0.47

یعنی در واقع معادله رگرسیون خطی این مثال برابر است با:        y = 2.15*x -0.47

 

۳- تابع Forecast: برای پیش‌بینی y متناظر با یک x جدید بر مبنای رگرسیون خطی.

=FORECAST(New X Value, Known Y values, Known X values)
=FORECAST(15,B2:B6,A2:A6) = 31.778

۴- تابع GROWTH: برای پیش بینی y متناظر با یک x جدید بر مبنای رگرسیون نمائی.

=GROWTH(Known Y Values, Known X Values, New X Values, Const)
=GROWTH(B2:B6,A2:A6,15,TRUE) = 48.68

عبارت Const در تابع GROWTH، دارای دو حالت True (محاسبه b) و False (مقدار 1 برای b) می‌باشد.

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

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

                                                                                                                                             به احتمال زیاد تاکنون برای جمع زدن چند سلول از تابع 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")

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

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

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

اگر اعدادی که رقم اول آنها صفر است مثل (01234) تایپ کنیم Excel ، صفر را حذف می کند زیرا صفر قبل از عدد معنی ندارد و یا اگر عدد مثبت را به همراه آن تایپ کنیم (مثل 23 +) ، Excel ، + آن را حذف می کند. همچنین اگر 2/6 را تایپ کنیم سلول 6 مارس را نمایش نمی دهد یعنی آن را تبدیل به فرمت تاریخ می کند                                                                                                                                                                                                                                                                              برای رفع این مشکلات باید عدد را تبدیل به متن کنیم برای این کار می توانیم یکی از علائم زیر را قبل از عدد بنویسیم :
' (آپستروف) : این علامت باعث می شود عدد به همان صورت و با تراز چپ نوشته شود.
" : این علامت باعث می شود عدد به همان صورت و با تراز راست نوشته شود .
^ : این علامت باعث می شود عدد به همان صورت و با تراز وسط نوشته شود.
= : این علامت باعث می شود سلول با عدد تایپ شده پر شود.
1- داده های متنی : می توانیم هر نوع داده متنی را در سلولهای Excel وارد کنیم.
2- داده های نوع تاریخ : می توانیم بدون تنظیم خاصی داده های نوع تاریخ در سلولهای Excel وارد کنیم ، کافی است در سلول تاریخی با فرمت yy/mm/dd یا yy-mm-dd وارد کنیم ، در آینده با قالب بندی های تاریخ آشنا می شویم . با انجام این کار (-) تبدیل به (/) شده و سال به صورت چهار رقمی (19yy) نمایش داده می شود .
3- داده های نوع زمانی : می توانیم بدون تنظیم خاصی داده های نوع زمان در سلولهای Excel وارد کنیم ، کافی است در سلول تاریخی با فرمت hh:mm:ss وارد کنیم ، در آینده با قالب بندی های زمان آشنا می شویم.
2- اطلاعات متنی :میتوانیم در سلولهای Excel هر متن دلخواهی را چه به فارسی و چه انگلیسی تایپ کنیم.
توانایی Excel برای در نظر گرفتن اعداد مثل متن :
اگر عددی مثل 2/6 را وارد کنیم ، پس از Enter کردن ، Excel، 6 مارس را نمایش میدهد. یعنی این اعداد را به تاریخ در نظر میگیرد. برای اینکه این اعداد با همین قالب نوشته شد قبل از آن علامت (') آپستروف قرار میدهیم. یعنی بنویسیم ( '6/2 )
3- اطلاعات از نوع تاریخ :
اگر در سلول Excel یک تاریخ به فرم dd-mm-yy یا dd/mm/yy وارد کنیم به طور اتوماتیک قالب سلول تبدیل به قالب تاریخ شده و خط فاصله ها تبدیل به ( / ) شده و سال در چهار رقم نمایش داده میشود.
4- اطلاعات از نوع زمان :
میتوانیم در سلول Excel یک زمان را با فرمت H:M:S وارد کنیم که قالب سلول به طور اتوماتیک زمان میشود.
5- اطلاعات از نوع یادداشت :
این نوع اطلاعات توضیحات یا یادداشتهایی هستند که بر روی سلول ظاهر شده و در مورد آن توضیح میدهند. در مباحث بعدی با نحوه ایجاد توضیحات آشنا میشویم.
توجه:

 فرمول نویسی در اکسل
عملگرها در Excel :
1- عملگرهای محاسباتی : از عملگرهای ریاضی + (برای جمع) ، - (برای تفریق) ، * (برای ضرب) و / (برای تقسیم)، ^(برای توان) و % (برای درصد) در Excel استفاده کنیم.
ایجاد یک فرمول ریاضی
برای ایجاد یک فرمول ریاضی به یکی از روشهای زیر عمل می کنیم.
روش اول : در سلولی که می خواهیم نتیجه نوشته شود یا در خط فرمول علامت = را تایپ کرده ، سپس آدرس عملوند اول را تایپ کرده عملگر را می نویسیم و بعد آدرس عملوند دوم و بهمین ترتیب ادامه می دهیم .
روش دوم : در سلولی که می خواهیم نتیجه نوشته شود علامت = را تایپ کرده ، سپس بر روی سلولی که حاوی اولین عملوند است کلیک کرده ، سپس عملگرد را تایپ کرده و بعد بر روی سلول حاوی در بین عملوند کلیک می کنیم و به همین ترتیب ادامه می دهیم .
* تقدم عملگرهای ریاضی :
اگر در یک فرمول چندین عملگر ریاضی داشه باشیم ، این عملگرها دارای تقدم هستند که عبارتند از :
()
^
%
*و/
+و-
می بینیم که * و / در یک خط نوشته شده اند زیرا این دو دارای تقدم یکسان هستند و اگر در فرمولی هر دو عملگر بودند از سمت چپ اولین عملگر انجام می شود .
2- عملگر متنی : برای عبارتهای متنی می توانیم از عملگر & (الحاق) استفاده کنید. با قرار دادن این عملگر بین دو متن آن دو را به هم پیوند می دهید.
3- عملگر آدرس : برای بیان محدوه ای از آدرس می توانیم از عملگر : استفاده کنیم. کافی است آدرس ابتدای محدوده را نوشته، سپس عملگر آدرس را تایپ کنیم و بعد آدرس انتهای محدوده .

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