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

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

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

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

 Vlookup -1

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

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

فرمول Vlookup را در منوی فرمول ها فرابخوانید و سلولی که شامل شماره ارجاع شماست (1) وارد کنید، سپس محدوده سلول های موجود در برگه یا کتاب کاری که لازم است داده ها را از آنها استخراج کنید، وارد نمایید (2) شماره ستون مربوط به نقطه داده ای که بدنبالش هستید را وارد کنید (3) و در صورتی که نزدیکترین آیتم به مرجع را می خواهید، True و در صورتی که آیتم دقیق را میخواهید، False را وارد نمائید.

2-ساخت چارت ها ترفندهای اکسل

برای ساخت یک چارت، داده ها را همراه با هدرهای ستون(1) در اکسل وارد نمائید و Insert > Chart > Chart Type را انتنخاب نمائید(2).

اکسل 2013 شامل بخش چارت های پیشنهادی (Recommended Charts) (3)همراه با طرح های مبتنی بر نوع داده هایست که با آن ها کار می کنید. پس از ساختن نسخه عمومی یک چارت، منوهای Chart Tools را برای اختصاصی کردن آن باز کنید. گزینه های جالبی را می بینید که میتوانید برای کارتان از آن ها استفاده کنید.

3- فرمول IF and IF ERROR

IF and IFERROR دو نوع از مفیدترین فرمول های IF در اکسل هستند. IF به شما امکان می دهد از فرمول های شرطی استفاده کنید که از یک سو صحیح (true) بودن یک چیز خاص را محاسبه می کند و از سوی دیگر غلط (false) بودن آن چیز خاص را. مثلاً میتوانید دانش آموزانی که امتیاز 80 یا بالاتر را در یک امتحان گرفته اند شناسایی کنید. به این ترتیب که اگر امتیاز درون ستون C بالای 80 باشد، سلول می باید ‘Pass’ را گزارش دهد و اگر امتیاز 79 یا زیر 79 باشد، باید «Fail» را گزارش نماید.

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

4- PivotTable

PivotTable یک جدول خلاصه یا summary است که به شما امکان شمارش، معدل گرفتن، جمع کردن و اجرای محاسبات دیگر را براساس نقاط ارجاعی که وارد کرده اید، می دهد. اکسل 2013، شامل Recommended Pivot Table است تا ساخت جدولی که داده های مورد نیاز شما را نمایش می دهد، آسانتر شود.

مثلاً، برای شمارش تعداد قبول شده ها و رد شده های یک آزمون در یک چارت، ستون Pass/Fail خود را در تب Row Labels و سپس دوباره در بخش Values از (PivotTable (2 خود قرار دهید. می توانید متغییرهای دیگر را از کادر کرکره ای Values انتخاب نمائید(3) . همچنین می توانید جدول های فرعی بسازید که داده ها را به صورت طبقه بندی شده ، مثلاً تعداد Pass/Fail براساس جنسیت خلاصه می کنند.

5- PivotChart

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

اکسل 2013، شامل بخش Recommended PivotCharts است که در زیر آیکن Recommended Charts در قسمت Charts تب Insert لیست شده اند. با چرخاندن اشاره گر ماوس روی آن گزینه میتوانید پیش نمایش یک چارت را ببینید. برای ساخت دستی PivotChart ، آیکن PivotChart را در تب Insert انتخاب نمائید.

6-Flash Fill

Flash Fill بهترین ویژگی جدید اکسل2013 است که یکی از آزاردهنده ترین مشکلات این برنامه را حل می کند: چگونه بخشهایی از اطلاعات مورد نیاز خود را از یک سلول بهم پیوسته استخراح نماییم. مثلاً در گذشته، وقتی در ستونی با نام هایی در فرمت «Last ,First» کار می کردید، یا مجبور بودید همه چیز را به صورت دستی تایپ کنید یا یک workaround(پیچیده) بسازید.

فرض کنید که با فیلد نام اشخاص در اکسل 2013 کار می کنید. شما نام کوچک اولین شخص را در یک فیلد سریعا در کنار فیلدی که با آن کار می کنید (1)، تایپ می کنید و Home > Fill > Flash Fill را انتخاب می کنید. در پاسخ، اکسل به طور خودکار نام اول تمام افرادی که در فیلد موجود ثبت شده اند را استخراج می کند و فیلد جدید را با این نام ها پر می کند.

7- Quick Analysis

ابزار جدید Quick Analysis اکسل 2013، مقدار زمان لازم جهت ساخت چارت های با مجموعه داده های ساده را کاهش می دهد. وقتی شما داده های خود را انتخاب می کنید، آیکنی در گوشه پائین راست صفحه ظاهر می شود (1)، آن آیکن را کلیک کنید تا منوی Quick Analysis ظاهر شود (2).

این منو ابزارهایی را برای Formatting، Charts، Totals، Tables و Sparklines ارائه می دهد. با چرخاندن اشاره گر ماوس روی هر گزینه، می توانید پیش نمایش از آن بسازید.

8- Power View

Power view یک ابزار تعاملی کشف و تجسم سازی داده هاست که میتواند حجم بزرگی از داده ها را از فایل های داده ای خارجی، استخراج و تجزیه و تحلیل کند. در اکسل 2013، به Insert>Reports بروید(1) تا از Power View استفاده کنید (2).

گزارشات ساخته شده با Power View، آماده نمایش هستند همراه با مدهایی برای خواندن و نمایش تمام صفحه. شما می توانید حتی یک نسخه تعاملی را در PowerPoint صادر کنید. راهنمای آموزشی (tinyurl.com/nqyLvnl(BI در سایت مایکروسافت به شما کمک می کند تا در این زمینه یک کارشناس شوید.

9- Conditional Formatting

عملکرد گسترده فرمتینگ شرطی اکسل، شناسایی نقاط داده ای مورد نظر را آسان می سازد. این ویژگی در تب Home در نوار وظیفه (1) قرار دارد. محدوده سلول هایی که میخواهید فرمت شوند را انتخاب نمائید و Conditional Formatting را کلیک نمائید (2). زیر منوی( Highlight Cells Rules (3 همانند یک میزبان برای ویژگی هایی که اغلب از آن ها استفاده می کنید، عمل می کند.

10- تبدیل ستون ها به سطرها (و بالعکس)

گاه ممکن است بخواهید داده ها را از فرمت ستون به فرمت سطر(یا بالعکس) تبدیل کنید. برای اینکار، سطر یا ستونی که میخواهید تبدیل در آن ها انجام دهید را کپی کنید، سلول مقصد را راست کلیک نمائید و Paste Special را انتخاب نمائید. سپس کادری با برچسب Transpose که در پائین پنجره ظاهر می شود را تیک بزنید و ok را کلیک نمائید. اکسل بقیه کارها را انجام می دهد.

11- میانبرهای ضروری صفحه کلید

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