تکنیک ها و مهارت های بسیار زیادی در مورد نرم افزار اکسل وجود دارد که بهتر هست آنها را بدانیم.
در این مقاله، درباره بعضی از تکنیک هایی که ما به عنوان یک کاربر اکسل باید بدانیم را مطرح می نماییم.
– خروجی به صورت Pdf
گاهی نیاز است شما فایل اکسل تان یا گزارشی که از آن تهیه نموده اید را اغلب به کاربر یا فردی دیگری لازم هست ارائه دهیم.
هرچند راه های زیادی برای جلوگیری از مشاهده و تغییر اطلاعات توسط کاربران دیگر وجود دارد (مثل مخفی کردن ، محافظت نمودن، یا پنهان کردن مقدار با فرمت ) ولی با کمی دانش در زمینه اکسل تمام این راه ها را می شود دور زد.
اگر بخواهیم تا از تغییر دادن داده ها و فرمول هایتان توسط دیگران جلوگیری کنیم خیلی راحت و سریع آن را به pdf تبدیل می کنیم و با خیال راحت هر جایی می می خواهیک بفرستیم.
– استفاده از توابع برای تکمیل داده ها
شما حساب های پیچیده شامل ۱۲ ماه را دارید. باید فایلی با ۱۲ sheet و آنها را از فروردین تا اسفند نام گذاری کنید. محاسبات و فرمت هایتان را روی شیت فروردین انجام دهید، و بعد از آن کارهای انجام گرفته را به ۱۱ ماه دیگر کپی کنیم.
این یک روش خیلی خسته کننده است. اگر داده ها به اشتباه کپی شوند که کار شما بسیار سخت می شود. در حقیقت بهترین روش برای انجام این دسته از کارها در اکسل ابزار “Fill Across Sheets” است.
این ابزار اجازه کپی اطلاعات از یک شیت به بقیه شیت ها به صورت خیلی سریع انجام می دهد.
برای این کار بر روی یکی از تب شیت ها کلیک کنید و Select All را بزنید. سپس از تب Home گزینه Fill را پیدا کنید. از لیستی که برایتان باز می شود گزینه Across Worksheet را انتخاب کنید.
-استفاده از نام برای محدوده ها
فرض کنید فایلی داریم با سه محدوده متفاوت در سه شیت مختلف، قرار است تا از تابع جمع استفاده نماییم:
=SUM (‘Month1’!$D$10:$L$10, ‘Month2’!$D$10:$L$10, ‘Month3’!$D$10:$L$10)
حالا همین فرمول که برای صورت حساب سه ماه از سه شیت مختلف فراخوانی کردیم و جمع آن را حساب نمودیم به این شکل می نویسم:
=SUM(Month1Total, Month2Total, Month3Total)
هر دوتای این فرمول ها یک کار را انجام می دهند. اما کدام را ترجیح می دهید؟
مسلما شما فرمول دوم را انتخاب می کنید چون قابل فهم تر و نوشتنش هم به مراتب راحت تر است ( لازم نیست تا به شیت های دیگر برای انتخاب محدوده ها بروید).
در فرمول دوم از اختصاص نام به محدوده ها استفاده می کنیم. نام می تواند به یک سلول تا یک محدوده اختصاص داده شود.
نکته دیگری که در نام گذاری وجود دارد این است که این ناحیه یا سلول نام گذاری شده به صورت مطلق می باشد. در حالت معمول و عادی که از نام گذاری استفاده نمی کنیم ، فرمول مطلق نخواهد بود و باید از علامت دلار برای مطلق نمودن ورودی های فرمول استفاده کنیم.
– فرمت اکسل
خیلی از کاربران اکسل وقتی داده های شان را وارد می کنند کار را تمام شده می دانند، ولی به عنوان یک کاربر حرفه ای این پایان کار نیست. توصیه می کنیم حتما به فونت، فونت سایز ، رنگ بندی ، خطوط راهنما اکسل و … رسیدگی نمایید. استایل و فرمتی را حتما برای فایل خود استفاده نمایید.
-محافظت از کار خود
بعد از اینکه همه کارتان روی فایل تمام شده است، آخرین موردی که نیاز پیدا خواهید کرد این است که اگر شخصی فایل شما را باز کرد و سهواً فرمولی را تغییر داد یا موردی که شما فرض بر تغییر آن نداده بودید را تغییر داد؛ کل زحمت تان بر باد نرود بنابراین در اینجا محافظت از فایل مطرح می شود.
اکسل ابزار قدرتمندی را برای محافظت از فایل و کنترل کاربر برای تغییرات محدود ارائه کرده است.
با قفل نمودن فایل اکسل می توان از فایلتان در مقابل تغییر داده و فرمول ، تغییر اندازه ستون و ردیف ، تغییر ساختار فایل با کم یا زیاد کردن شیت جلوگیری کرد.
حتی می توان سطح دسترسیهایی تعریف کرد تا کاربر فقط ناحیه ای از سلول ها را بتواند تغییر دهد.
-کنترل داده های ورودی
از بزرگترین مشکلات در فایل های اکسل وارد کردن داده های عجیب و غریب می باشد. کاربر اعداد خیلی بزرگ را در سلول وارد می کند یا از آنچه مقرر می کنیم کمتر یا زیاد تر است. یا اینکه در سلولی باید مقدار متنی وارد کند ولی کاربر مقدار عددی را به فرمول می دهد لذا خروجی مطلوب را دریافت نخواهد کرد.
برای جلوگیری از این کار می توانیم با Data Validation کاربر را محدود نماییم . با این کار می توانیم محدوده اختیارات کاربر را مشخص کنیم .
-کلیدهای ترکیبی
اکثر افراد همیشه دست به موس هستند درحالی که با تعداد کمی کلید ترکیبی سرعت شان را می توانند در محیط کار یا پیش رئیس شان بالا ببرند تا هم در زمانشان صرفه جویی شود هم جایگاهی دیگری در سازمانشان برای خویش ایجاد کنند.
برخی از کلید ترکیبی ها را با هم بررسی کنیم:
Ctrl + Space انتخاب ستون کنونی
Shift + Space انتخاب ردیف کنونی
Ctrl + 0 مخفی کردن ستون کنونی
Ctrl + 9 مخفی کردن ردیف کنونی
F4 آدرس دهی مطلق در فرمول
Alt + = جمع خودکار
– ذخیره وقت با Auto-fill
اگر برای یک سلول فرمولی نوشته اید و لازم هست تا آن فرمول را دوباره برای سلول کناری آن هم بنویسید، بجای تایپ مجدد فرمول می توانید از Auto-fill استفاده کنید.
Auto-fill به شما این اجازه را به شما می دهد تا به سرعت مقداری را از یک سلول به سلول های کناری آن نیز کپی کنید. که این سلول کناری هم می تواند در راستای افقی یا عمودی باشد.
این ابزار فقط برای کپی نمودن فرمول کارایی ندارد بلکه می تواند برای ایجاد روند صعودی یا نزولی در پرکردن سلول ها از عدد، تاریخ، روز ، ماه و یا هر داده ی دیگری باشد.
-معین کردن مقادیر کلیدی
وقتی کارتان با یک سری اعداد زیاد باشد، کار مشکلی هست که یک مقدار دارای اهمیت یا جذابیت خاص مثل: ۱۰ مقدار بالا، همه مقادیر بیشتر از میانگین، مقادیر تکراری و… را پیدا کنید.
Conditional formatting بر اساس نقش ها ( rules) مقادیر کلیدی را برجسته و هایلایت می نماید . وقتی داده ها تغییر می کند مقادیر کلیدی نیز بالطبع تغییر خواهد نمود.
– چگونه از داده های تکراری لیستی واحد بسازیم؟
در ساخت داشبورد در چند بخش نیاز به لیستی از داده هایمان داشتیم که به صورت یکتا و بدون داده تکراری باشد. اگر قرار باشد تا یکی یکی داده ها را بررسی نماییم و لیست داده ها را به صورت دستی مهیا کنیم ممکنست ساعت ها طول بکشد، ولی در کمتر از چند ثانیه می توانیم این لیست را آماده کنیم .
برای ایجاد این لیست ستون مد نظر را انتخاب کردیم و سپس از آدرس زیر روند حذف موارد تکراری را انجام دادیم.
Data > Data Tools > Remove Duplicates
– چگونه تعداد کاراکتر مشخصی از یک رشته متنی را انتخاب کنیم؟
گاهی وقت ها برای ساخت داشبورد یا هر گزارش دیگری لازم است داده های تان را ورودی تان را بسازیم. مثلا در ستونی اطلاعاتی به این شکل باشد 990501 که خروجی یک نرم افزار خاص بوده است که شامل روز ماه سال می باشد. اگر قرار باشد سال را از درون این شماره سریال خارج نماییم می توانیم از تابع زیر استفاده نماییم.
تابع Mid یک رشته متنی را از ما دریافت می کند سپس از ما می خواهد تا کاراکتری که می خواهید جداسازی از آن شروع شود همچنین تعداد کاراکتری که می خواهد جدا کنید را مشخص کنید.
MID(text, start_num, num_chars)
لذا برای ساخت شماره سال به این صورت عمل می کنیم که :
=MID(A1,1,2)
Paste Values – به جای Paste
با تکنیک های کاربردی اکسل که عنوان شد ما می توانیم ورودی های ساخت گزارش مان را بسازیم ولی برای ساخت یک لیست واحد از این داده ها مشکلی وجود دارد. در صورتی که تکراری ها را بسازیم این داده ها تغییر خواهند کرد و نتیجه دلخواه ما نخواهد بود. چون هنوز وابستگی به فرمول و سلول های ورودی خود دارند.
برای اینکه این وابستگی را قطع کنیم ، می توانیم این داده های حاصل از فرمول نویسی را کپی کنیم ولی به جای اینکه Paste کنیم از حالت Paste Value استفاده نماییم. تفاوت این دو در این است که حالت اول کپی را به حالت عادی منتقل می کند و در حالی که حالت دوم مقادیر را به صورت ثابت منتقل می کند. حال اگر تکراری ها را حذف کنیم کار به نحو احسن انجام خواهد شد.
– افزایش سرعت فایل اکسل
راه های افزایش سرعت فایل های اکسل زیاد ولی یکی از راه هایی که سرعت فایل تان را افزایش می دهد استفاده از حالت محاسبه Manual هست.
این حالت محاسبات فایل را تا زمانی که شما به آن دستور ندهیم اجرا نمی کند. یعنی شما می توانید ابتدا تمام فرمول نویسی ها و عدد گذاری هایتان را انجام دهید در آخر به اکسل دستور دهید که الان برایم محاسبات را انجام بده.
برای این منظور می توانید از آدرس زیر حالت Manual را فعال نمایید.
Formula > Calculation > Calculation Options > Manual
حالا هر زمانی که می خواهید محاسباتی را انجام دهید می توانید با کلید F9 یا گزینه Calculate Now استفاده نمایید.
– استفاده از کلیدهای جهتی به منظور ویرایش
یکی از مشکلانی که موقع ویرایش یک فرمول در سلولی وجود دارد این است که تا کلیدهای جهت را می زنید می بینید که سلول های اطراف را انتخاب کرده و در فرمول جای گذاری نموده است. در حالی که قصد شما حرکت در فرمول نوشته شده بوده است.
برای رهایی از این موضوع و اینکه دیگر لازم نباشد موقع ویرایش فرمول دست به موس شوید خیلی راحت از کلید F2 استفاده کنید. با زدن کلید F2 حالت (Mode) نرم افزار به حالت ویرایش (Edit) تغییر می کند. وقتی شما بر روی یک سلول هستید و کلید f2 را می زنید فرمول آن سلول باز می شود. حالا می توانید به راحتی با کلیدهای جهتی شروع به ویرایش فرمول تان نمایید.
برای خروج از حالت ویرایش با زدن کلید Enter یا Esc به حالت نرمال (Ready) باز می گردید. این حالت های مختلف در نوار وضعیت پایین نرم افزار نمایش داده می شود. با این ترفند کاربردی اکسل سرعت عمل تان چند برابر خواهد شد.
شما می توانید آموزش مقدماتی و پیشرفته اکسل را در آموزشگاه سبحان فرابگیرید و حرفه ای شوید .
