آموزش رایگان ترفند اکسلی که فقط حرفه ایی ها می دانند !

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

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

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

1 : تابع SUM یا +ALT

یکی از آموزش های مقدماتی اکسل برای جمع زدن داده این می باشد که ما ALT+= را زیر دادها مانند نمونه زیر بزنیم. خود اکسل به صورت پیش فرض تابع SUM را می نویسد و همچنین خود اکسل محدوده رو انتخاب می کند و مابا زدن Enter می توانیم جمع داده ها را به راحتی ببینیم.

2: تابع SUBTOTAL یا +ALT

اگر جمع داده ها را بذاریم بعد از این که داده ها را فیلتر کردیم بعد بیایم زیر داده ها Auto Sum یاALT+ = را بزنیم، سیستم به جای تابع SUM از تابع SUBTOTAL استفاده می کند و موارد فیلتر شده را در این تابع در نظر نمی گیرد.

3: نکته تابع SUBTOTAL

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

برای اینکارALT+A+C را میزنیم و داده ها از فیلتر خارج می شوند. یعنی با این کار تمام فیلتر ها را حذف می کنیم.
حالا اگر ما از تابع SUBTOTAL استفاده کردیم و بخواهیم داده ها را پنهان کنیم چه اتفاقی برای جمع داده ها خواهد افتاد.
اگر الان جمع داده ها را نگاه کنیم مبلغ 749.45 طبق جدول زیر خواهد بود .

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

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

حالا چی کار می توانیم بکنیم ؟

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

با پاک کردن عدد 9 تابع SUBTOTAL پنجره ای مطابق زیر باز می گردد.

اینجا اگر ببینید هرکدام از توابع ما یک عدد دارد، ازعدد 1 تا عدد 11 نسبت به ردیف های پنهان حساس نیستند ولی از عدد 101 به بعد نسبت به ردیف های پنهان شده حساس هستند.

یعنی ما برای SUM که عدد 9 می باشد باید عدد 109 را انتخاب کنیم

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

4: ظاهر شدن ردیف های پنهان

این که اگر ما ردیف های را که پنهان کردیم را بیایم گزینه فیلتر رو باز کنم بدون این که عملیاتی انجام دهیم

فقط گزینه OK رو بزنیم خواهید دید که ردیف های پنهان شده ظاهر می گردد

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

نکته :

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

مثلا ستون F و E را پنهان کنیم.

حالا بخواهیم جمع دو ستون باقی مانده را با تابع SUM بنویسیم که با زدن ALT+= جمع داده ها رو میبینیم.

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

در این جا اکسل هیچ راه حل مستقیمی در این ارتباط ندارد.

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

یعنی حتی اگر از SUBTOTAL استفاده کرد باز هم همان نتیجه را به ما نشان می دهد. پس راه حل چی هست؟
در این جا ما مجبوریم یک تابع خودمان برای اسکل باALT+ F11 تعریف کنیم. این تابعی که ما تعریف کردیم و کدهاش رو نوشتیم

و اگر شما این کدها را بنوسید تابعی به نام SumVisible به اکسل شما اضافه می کند و بعد از آن می توانید از این تابع استفاده کنید و این تابع برای شما پاسخگو خواهد بود.
و حالا اگر شما بخواهید این کدها را در اکسل بنویسید. بایدALT+F11 را بزنید و بعد گزینه Insert را بزنید

و یک ماژول(Module) ایجاد کنید و توی صفحه خالی ایجاد شده کدهای مورد نظر را بنوسید.
خوب حالا می توانیم تابع SumVisible را تست کنیم.

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

5 : آموزش قابلیت Smart Lookup

یکی دیگر از تکنیک های اکسل این است که اگر داده‌ای درون یک سلول وارد کرده باشید با کلیک راست روی این داده گزینه‌ای تحت عنوان Smart Lookup را مشاهده می‌کنید.

Smart Lookup در آفیس 2016 به اکسل اضافه شده است که با کلیک بر روی این گزینه داده شما در اینترنت جستجو می‌شود و شما می‌توانید به سایت‌های مرتبط با این داده مراجعه کرده و آنها را باز کنید.

6: آموزش قابلیت My Add-ins

در زبانه (Tab) Insert گزینه‌ای تحت عنوان My Add-ins داریم که با انتخاب این گزینه می‌توانید یک سری افزونه‌های کاربردی را پیدا کنید که این افزونه‌ها در قسمت STORE دسته بندی شده‌اند

شما می‌توانید جستجو کرده و موارد کاربردی را پیدا کنید.

یکی از مواردی که می‌توانیم اضافه کنیم Excel Colorizer Add-ins است. اگر این گزینه را انتخاب کنیم، می‌توانیم صفحه خودتان را با تم (Theme) خاصی رنگی کنیم.

برای استفاده از این تم (Theme) پیش‌فرض، محدوده را انتخاب کنید و Colorize را بزنید و بدین ترتیب همانگونه که در شکل زیر ملاحظه می‌کنید محدوده با یک تم (Theme) خاص، رنگی شد.

بر این اساس شما می‌توانید با تغییر تنظیمات این بخش، تم‌های (Themes) زیباتری را به اکسل خودتان اضافه کنید.

7- با دابل کلیک روی عنوان ریبان آن را پنهان و با تکرار این کار آن را ظاهر نمایید.

8- تکنیک چرخاندن غلطک (اسکرول)

9- راست کلیک روی مکان نشان داده شده و رفتن به شیت دلخواه

10. انتخاب چند شیت با نگهداشتن کلید Ctrl و جابجا کردن آن

11. انتخاب سریع چندین شیت با نگهداشتن کلید Shift

12- کپی قالب بندی مورد نظر و اعمال آن روی سایر سلول ها با دابل کلیک روی گزینه Format Painter

13- بزرگ و کوچک کردن ناحیه فرمولا بار با موس

14- دابل کلیک روی یک کلمه برای انتخاب کامل کلمه

15- استفاده از ابزار پرکردن (Fill handle) با استفاده از کلیک راست

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

Copy Cells
Fill Series
Fill Formatting Only
Fill Without Formatting
Fill Days
Fill Weekdays
Fill Months
Fill Years
Linear Trend
Growth Trend
Series…

16. پر کردن خودکار با دابل کلیک

17. ویرایش محدوده فرمول

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

18. پرش به اولین و آخرین سلول حاوی داده

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

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

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

20. انتخاب محدوده چندگانه با نگهداشتن کلید Ctrl

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

22. جابجا کردن یک ستون یا محدوده انتخابی با نگهداشتن کلید Shift

یکی دیگر از تکنیک های مورد علاقه من که بسیار کاربردی هم هست جابجا کردن محدوده انتخابی با استفاده از SHIFT +

Drag 23. کپی کردن محدوده انتخابی با نگهداشتن کلید Ctrl

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

24 : استفاده از ابزار Auto Sum با زدن کلید Alt + =

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

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

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

26 : نمایش فرمول های درون سلول ها با زدن کلید ترکیبی Ctrl+ `

شما با زدن کلید ترکیبی Ctrl+` میتوانید فرمول های سلولهای خود را مشاهده کنید و با زدن دوباره آن به حالت قبل برگردید.

نکته: علامت ` در سمت چپ اعداد موجود در بالای صفحه کلید قرار دارد و معمولا حرف پ فارسی یا ~ روی آن قرار دارد.

27 : پرش به ابتدا یا انتهای داده ها با کلید میانبر در اکسل

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

28 : بسط دادن فرمولها با دابل کلیک در اکسل

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

29 : اضافه یا حذف کردن یک سطر یا ستون با کلید میانبر

مدیریت سطرها و ستونها یکی از کارهای پرتکرار در اکسل هست و لازمه اینکار اضافه یا حذف کردن یک سطر یا ستون هست.

برای اضافه کردن یک سلول یا سطر یا ستون یکی از راههای سریع استفاده از کلید میانبر CTRL + SHIFT + ‘=’ می باشد یا Ctrl + (+) البته مثبت داخل پرانتز منظور علامت جمع در صفحه کلید عددی سیستم شما می باشد.

برای حذف یک سلول یا سطر یا ستون نیز میتوان از کلید ترکیبی CTRL + – استفاده نمود.

30 : تنظیم عرض یک یا چند ستون

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

31 : بسط دادن یک الگو در اکسل

تصویر زیر گویای این آموزش می باشد.

32 : جابجایی بین شیت ها در اکسل

برای جابجایی سریع بین شیت ها کافیست از کلید ترکیبی CTRL + PGUP یا CTRL + PGDN استفاده کنید.

33 : دابل کلیک روی فرمت پینتر

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

34 : نکته کاربردی اول اکسل: جدا کننده

اگر عددی داشته باشیم و بخواهیم این عدد را در داخل محدوده‌ای از سلول‌ها بطور مثال (D4:M11) تکرار کنیم، برای این کار پس از انتخاب محدوده و درج عدد مورد نظر با استفاده از کلیدهای میانبر Ctrl + Enter عدد مذکور در داخل همه سلولها تکرار خواهد شد.

حالا اگر این اعداد را بخواهیم سه رقم، سه رقم جدا کنیم در زبانه (Tab)،Home در قسمت Number بر روی گزینه کاما (,)کلیک کرده و عمل جداسازی مطابق شکل زیر انجام می‌شود.

ولی جداسازی اعداد بدین ترتیب با کاما (,) همانگونه که ملاحظه می‌نمایید با دو رقم ممیز نشان داده می‌شود، ضمن اینکه اگر سلول‌ها را بخواهیم وسط چین کنیم اجازه وسط چین کردن، داده نمی‌شود. چرا؟
به این خاطر که فرمتش طوری تعریف شده است که فضای خالی را با فاصله (Space) پُر می‌کند و در نتیجه این اعداد نمی‌توانند وسط چین بشوند.

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

حالا چه طوری می‌توانیم این مورد را اصلاح کنیم؟
برای این کار باید تنظیمات ویندوز (Windows) را تغییر دهیم. خوب ابتدا Start را باز می‌کنیم و Region & Language را جستجو و پیدا می‌کنیم.

این روش برای ویندوز 10 می ‌باشد و اگر شما از سایر ویندوزها استفاده می‌کنید، در قسمت کنترل پنل (Control Panel) گزینه Region & Language Setting را پیدا کنید و گزینه Additional Date Time & Regional Settings را انتخاب می‌کنیم.

در این پنجره، که در تمامی ویندوزها می‌توانید پیدا کنید، بر روی Region کلیک می‌کنیم و پنجره Region باز می‌شود.

در این پنجره یک سری تنظیمات انجام می‌دهیم که بر روی کلیدی که برای جدا کننده در اکسل قرار داده شده، تاثیر گذار می‌باشد. برای این کار کجا باید برویم؟ بدین منظور گزینه Additional Setting را انتخاب می کنیم.

سپس در پنجره زیر، قسمت Currency تنظیماتی است که در آن کاما(,) از اینجا آن را دریافت می‌کند.

قسمت اول Currency Symbol را باز نموده و می‌توانیم ریال یا دلار ($) را انتخاب کنیم.البته این قسمت تاثیر زیادی در کارایی ما برای آن گزینه نخواهد داشت.

قسمت دوم Positive Currency Format برای جایگاه علامت Currency می باشد که بستگی به نظر شما، می‌توانید ابتدا، آخر، با فاصله یا بدون فاصله را انتخاب کنید.

قسمت بعدی Negative Currency Format می باشد و برای جایگاه قرارگیری علامت Currency و علامت منفی در اعداد منفی می‌باشد.

به طوریکه اعداد منفی را داخل پرانتز قرار دهیم یا فقط عدد منفی را بگذاریم که در اینجا اگر شما هنگامی که کاما (,) را انتخاب می‌کنید

عدد شما منفی و در داخل پرانتز گذاشته شده و نمی‌خواهید در داخل پرانتز باشد، چه کار باید بکنید؟ یک فرمت عدد منفی انتخاب می‌کنید.

قسمت بعد Decimal Symbol می باشد و برای این است که ممیز به چه صورت باشد که بهترین حالت آن نقطه (. )می‌باشد

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

برای این قسمت به صورت پیش‌فرض روی صفر(0) قرار می‌دهیم تا از این به بعد بدون ممیز نشان بدهد و بقیه قسمتها را کاری نداریم.

35 :تاریخ در اکسل

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

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

یعنی در ابتدا (D) بگذارید، بعد(M) بگذارید و در قسمت آخر هم (YYYY). یعنی اول روز، بعد ماه، بعد سال.

یا حتی میتوانید به این صورت بنویسید که اول سال (YYYY)، بعد ماه (MM)و بعد روز (DD) و به این صورت تنظیم کنید که از این به بعد تاریخ میلادی را بر این اساس می‌نویسید و برنامه اکسل شما نیز متوجه این موضوع می‌شود.

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

36 :اعداد در اکسل

ترفند دیگر در قسمتNumber ، List Separator می‌باشد.

کلیه فرمول‌هایی که می‌نویسیم یک جدا کننده دارند، البته نه برای همه فرمول‌ها ولی اکثر فرمول‌ها یک جداکننده دارند که به قسمت بعد می‌رود و هنگامی که می‌گوییم که خوب کاما (,) می‌گذاریم که برویم به قسمت بعد فرمول و خیلی‌ها وقتی کاما (,) می‌گذارند به قسمت بعد نمی‌رود.

چرا؟
چون List separator برنامه شما (,) نیست. پس چیست؟ حتماً علامت دیگری است، حالا می‌تواند نقطه کاما (;) یا نقطه کاما برعکس یا هرچیزی باشد که لازم است حتماً آن را در حالت کاما (,) بگذارید.

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

Ok را می‌زنیم و پنجره را می‌بندیم.

تمامی تنظیمات مهم گفته شد. خوب حالا محدوده داده‌ها را انتخاب می‌کنیم، کاما (,) را می‌زنیم. مشاهده می‌ کنید که ممیز نمایش داده می‌شود.

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

ولی استفاده از کلیدهای ترکیبی Ctrl+Shift+1 فرمتی می‌باشد که به شما اجازه می‌دهد مطابق شکل زیر اعداد را پس از استفاده از کلیدهای میانبر مذکور، با استفاده از قسمت Alignment در زبانه Home وسط چین کنید.

در شکل بالا همانگونه که ملاحظه می‌کنید اعداد سه رقم ممیز دارند که ممیزها را می‌توانیم مطابق شکل زیر در قسمت Numbers عمل نموده و نمایش ندهیم.

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

فرض کنید می‌خواهیم جمع اعداد درج شده در سلولهای (A1:A8) را بدست آوریم.
خوب می‌نویسیم مساوی SUM پرانتز باز، محدوده اعداد (A1:A8) را انتخاب می‌کنیم، پرانتز بسته و Enter،

ولی نتیجه برابر صفر می‌شود. چرا؟

علت این امر فرمت اعداد درج شده می‌باشد که به صورت متنی می‌باشند. برای حل این مشکل دو راه وجود دارد:
راه حل اول که به عنوان راه اصلی این کار می‌باشد اینکه محدوده اعداد (A1:A8) را انتخاب کرده، کلیک راست و سپس گزینه دوم را با عنوان “Convert to Number” انتخاب می‌کنم و بدین ترتیب کلیه اعداد از فرمت متنی به فرمت عددی تبدیل می‌گردد.

راه حل دوم کلیک راست و گزینه Paste Special را انتخاب کرده و در منوی باز شده از قسمت Paste، گزینه Values را تیک زده و از قسمت Operation نیز گزینه Multiply را انتخاب کرده و Ok می‌کنم.

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

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

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

4/5 - (1 امتیاز)