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

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

4

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

Vlookup

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

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

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

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

به منظور ایجاد نمودارها، اطلاعات خود را با سرخط‌های ستونی وارد اکسل کنید و سپس از منوی Insert، گزینه Chart و نهایتا Chart Type را انتخاب کنید. اکسل 2013 مجموعه‌ای از نمودارهای توصیه‌شده را بسته به نوع اطلاعاتی که با آن‌ سروکاردارید پیش روی شما قرار می‌دهد. پس از ایجاد نسخه پیش‌فرض از نمودار می‌توانید از طریق منوی Chart Tools به شخصی‌سازی هرچه بیشتر نمودار یادشده اقدام کنید. مدنظر داشته باشید که در این قسمت نباید هیچ ترسی از سروکله زدن با گزینه‌های مختلف داشته باشید زیرا گزینه‌های شگفت‌انگیز زیادی در این قسمت موجود هستند.creating-charts-and-recommended-charts

فرمول‌های شرطی

فرمول‌های IF و IFERROR دو فرمول مفید شرطی در اکسل هستند. فرموی IF به شما این امکان را خواهد داد تا از فرمول‌های شرطی که به بررسی درست و یا نادرست بودن یک گزاره می‌پردازند استفاده کنید. به عنوان مثال می‌توانید مشخص کنید که در ستون C جدول، برای دانش‌آموزانی که نمره 80 یا بالاتر کسب‌کرده‌اند از گزینه Pass استفاده شود و دانش‌آموزانی که 79 یا کمتر گرفته‌اند، عبارت Fail در مقابل نامشان ثبت شود.if-formula

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

PivotTables

PivotTables جداول خلاصه‌سازی ضروری برای انجام اعمالی از قبیل شمارش، میانگین‌گیری، جمع‌زدن و اعمال دیگر محاسبات بر اساس مقادیر مرجعی است که شما وارد کرده‌اید. اکسل 2013 با اضافه‌کردن جدول‌های پیشنهادی کار را از نیز ساده‌تر کرده تا بتوانید جداول مورد نیاز برای نمایش اطلاعات مورد نظرتان را به سادگی ایجاد کنید.pivot-table-2

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

برای مثال به منظور شمارش تعداد دانش‌آموزانی که قبول و یا رد شده‌اند کافی است ستون Pass/Fail را در سربرگ Row Lables قرار دهید و مجددا آن را در قسمت Values در PivotTables خود قراردهید. پس از آن معمولا ادامه عملیات براساس داده‌ها انجام خواهد شد با این وجود می‌توانید از میان عملیات پیش‌فرض موجود در پنجره بازشونده Values، تابع و نوع عملیات مورد نظر خود را انتخاب کنید. همچنین می‌توانید زیرجدول‌هایی که اطلاعات را بر اساس دسته‌های دیگر ( به عنوان مثال جنسیت افراد قبول و یا ردشده) دسته‌بندی می‌کنند نیز ایجاد کنید.

PivotChart

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

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

Flash Fill

یکی از بهترین قابلیت‌های جدید در اکسل 2013، Flash Fill، یکی از مشکلات موجود در اکسل را به خوبی مرتفع نمود. پیش از این استخراج قسمتی از اطلاعات موجود در یک سلول ترکیبی همواره دردسرهای خاص خود را داشت. به عنوان مثال وقتی که با ستونی حاوی اسامی اشخاص به ترتیب “نام خانوادگی، نام” سروکار دارید به طور سنتی باید به منظور جداسازی داده‌ها (مثلا نام) به تایپ مجدد نام‌ها در ستون‌ها اقدام می‌کردید اما در اکسل 2013 می‌توانید نام اولین شخص را در ستون مجاور ستونی که در آن کار می‌کنید تایپ کرده و سپس با طی کردن مسیر Home>Fill>Flash Fill اکسل به‌طور خودکار، نام اشخاص باقی‌مانده را با این الگوبرداری در جدول شما وارد خواهد کرد.flash-fill

تحلیل سریع

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

در این منو می‌توانید ابزارهایی مانند Formatting,Charts,Totals,Tables,Sparklines را مشاهده کنید. با نگه‌داشتن ماوس بر روی هر یک‌ می‌توانید پیش‌نمایشی زنده از آن‌ها مشاهده کنید.

Power View

Power View روشی ایده‌آل و تعاملی برای کاوش و نمایش مقادیر گسترده اطلاعات موجود در فایل‌های اطلاعاتی جانبی است. به منظور استفاده از این ابزار به منوی Insert مراجعه و گزینه Reports را انتخاب کنید.power-view

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

Conditional Formatting

برای اکثر جداول ایجاد شده، قابلیت Conditional Formatting در اکسل به شما امکان مشخص‌کردن و ایجاد تمایز در داده‌های مورد علاقه شما را می‌دهند. این قابلیت را می‌توانید از سربرگ Home بیابید. به منظور استفاده از این رویه، ابتدا محدوده سلول‌هایی که می‌خواهید تغییرات را بر روی آن اعمال کنید انتخاب و سپس منوی پایین‌رونده Conditional Formatting را انتخاب کنید. قابلیتی که در این قسمت معمولا بیش از سایر قابلیت‌ها مورد استفاده قرار می‌گیرد، Highlight Cells Rules است.conditional-formatting

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

جابجایی سطر و ستون

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

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

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

Ctrl+ کلیدهای جهتی پایین/بالا: این عمل شما را به پایین‌ترین/بالاترین سلول ستون فعلی رهنمون خواهد‌کرد.

Ctrl+ کلیدهای جهتی چپ/راست: شما را به دورترین سلول سمت چپ/راست در ردیف جاری رهنمون خواهد‌کرد.

Ctrl+Shift+ کلیدهای جهتی پایین/بالا: کلیه سلول‌های موجود در پایین/بالای سلول فعلی را انتخاب می‌کند.

Shift+F11: یک صفحه‌کاری جدید خالی در ورک‌بوک شما ایجاد خواهدکرد.

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

Ctrl+Home: شما را به سلول A1 رهنمون خواهدکرد.

Ctrl+End: شما را به آخرین سلولی که حاوی اطلاعات است رهنمون خواهدکرد.

Alt+=: به جمع‌زدن سلول‌های قرارگرفته در بالای سلول فعلی مبادرت می‌ورزد.

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

 

Pcworld

شاید بخوای اینا رو هم بخونی:

نوشتن دیدگاه

آدرس ایمیل شما منتشر نخواهد شد.

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

4 نظر برای این مطلب
  1. آقا مهدی می‌گوید

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

  2. بیژن می‌گوید

    با سلام وخسته نباشید لطفا در مورد مغایرت گیری با دابلیوکیت توضیح بدین ممنونم-آخه من هرکاری میکنم قسمت بالایی رنگش عوض نمیشه

  3. quietly brilliant می‌گوید

    بااجازه بوکمارک کردم :)!اگه ممکنه راجبه VBAتو محیط اکسل مطالبی بنویسید،مرسی

    1. محسن صحافی می‌گوید

      سلام
      خدا رو شکر که به دردتون خورده
      والا مبحث وی بی ای خیلی طولانیه و به نظرم بهترین سورسی که اتفاقا نیازی به ترجمه نداره اینه:
      http://excelvbatutor.com/vba_tutorial.html