ترفندهای اکسل

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

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

جمع زدن سریع با کلید ALT و =

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

کلیدهای تنظیم فرمت اعداد

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

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

با زدن کلید ` + CTRL حالت شیت اکسل عوض می‌شود و تمامی فرمول ها را به شما نماش می‌دهد. فراموش نکنید که با دوباره زدن همین کلید مجدد به حالت عادی شیت بر خواهید گشت.

نکته: علامت ` را در سمت چپ عدد 1 کیبورد کامیپوتر باید پیدا کنید. روی این کلید علامت ~ هم می بینید.)

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

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

کپی کردن سریع فرمول در یک ستون از لیست

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

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

اضافه و حذف یک سطر یا ستون

تقریبا یک کار مهم در اکسل اضافه و یا کم کردن سطر و ستون ها است . برای اینکار می توانید به راحتی از کلید- + CTRL (کنترل و منها) برای حذف و برای اضافه کردن = + CTRL+SHIFT (کنترل شیفت مساوی) برای اضافه کردن استفاده کنید.

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

تنظیم عرض ستون‌ها

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

جابجا شدن سریع بین شیت ها

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

Double-click بر روی ابزار Format Painter

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

استفاده از ابزار پرکردن (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

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

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

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

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

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

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

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

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

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

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

یک کلیک برای انتخاب همه چیز

احتمالا می دانید که با میانبر ctrl + A می توانید همه چیز را در صفحه انتخاب کنید، اما تعداد کمی می دانند که تنها با یک کلیک روی گزینه ای در گوشه صفحه همانطور که در تصویر دیده می شودف تمام داده ها ظرف یک ثانیه انتخاب می شوند.

فایل‌های اکسل را در Bulk باز کنید

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

بین فایل‌های مختلف اکسل جا به جا شوید

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

با استفاده از ctrl +tab می توانید بین چند فایل مختلف ازادانه جا به جا شوید. این دستور در محیط ویندوز، برای جا به جایی بین صفحات ویندوز و برگه های فایر فاکس (هنگام استفاده از ویندوز 7 ) هم کاربرد دارد.

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

به طور کلی در منوی اصلی بالایی سه میانبر Save, Undo Typing and Repeat Typing وجود دارد. به هر حال اگر بخواهید از میانبرهای بیشتری مثل copy و Cut استفاده کنید، به این شکل می‌توانید انها را اضافه کنید.

File->Options->Quick Access Toolbar و سپس copy و Cut را از ستون سمت چپ به راست اضافه و این تغییر را ذخیره کنید. دو میانبر جدید را که به منوی بالایی اضافه شده خواهید دید.

یک خط مورب را به هر خانه در اکسل اضافه کنید

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

چگونه این کار را می کنید؟

همه این را می دانند که Home->Font-> Borders می تواند لبه های مختلف هر خانه را تغییر دهد، حتی رنگ های متفاوتی هم به ان اضافه کند. اگر روی More Borders کلیک کنید با دیدن مواردی مثل خط مورب، تعجب خواهید کرد. روی آن کلیک و نتیجه را ذخیره کنید، حالا می توانید بی معطلی این کار را انجام دهید.

بیشتر از یک سطر یا ستون جدید اضافه کنید

احتمالاً می دانید که چگونه یک سطر یا ستون جدید اضافه کنید، اما اگر نیاز داشته باشید که تعداد زیادی سطر و ستون اضافه کنید و بخواهید این عمل را X بار انجام دهید، وقت بسیار زیادی را از دست می دهید. بهترین راه اینست که هر تعداد سطر یا ستون که برای افزودن لازم دارید را بگیرید و انتخاب کنید و روی تغییر رنگ (highlight) ایجاد شده راست کلیک کرده و Insert را از زیرمنو انتخاب کنید. سطرهای جدید در بالای اولین سطرر انتخابی شما و ستون های جدید در سمت چپ اولین ستون انتخابی شما اضافه می شوند.

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

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

به سرعت خانه های خالی را پاک کنید

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

برای اینکار ستونی را که می خواهید از کار خارج کنید، انتخاب کنید، به Data->Filter بروید.

وقتی گزینه های زیر منوی بعدی نمایان شد Select All را باز کرده و اخرین گزینه را که Blanks است، انتخاب کنید. تمام خانه های خالی، سریعا نمایش داده خواهد شد.

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

جستجوی ابهامات با استفاده از Wild Card .

احتمالا می دانید که چگونه جستجوی سریع را با استفاده از Ctrl + F فعال کنید. اما دو الگو اصلی واژه یابی وجود دارد: (؟ و *)، که برای فعال سازی وازه یابی در صفحه اکسل استفاده می شود. به طور کلی واژه یابی زمانی به کار می رود که درباره نتیجه و هدف جستجو مطمئن نیستید.

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

به یک ستون یک مقدار خاص را نسبت دهید

از وجود عملکرد کلید Filter مطلع هستید، اما تعداد کمی از کاربران از Advanced filter استفاده می کنند، که وقتی می خواهید یک مقدار یا ارزش خاص را به داده های یک ستون نسبت دهید ، یا آنرا از داده های یک ستون جدا کنید، مکرراً می توانید از ان استفاده کنید.

ستون را با کلیک انتخاب کنید و به Data->Advanced بروید. در پنجره بعدی که باز می شود همانطور که در تصویر می بینید، Copy to another location را فعال کنید تا فضای مستطیل قرمز دوم قابل استفاده شود.

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

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

شکستن خطوط و جا دادن متن‌ها در یک سلول

یکی از نکات کلیدی اکسل جا دادن یک متن طولانی در یک سلول است. مثل تصویر بالا، اگر در حالت عادی در یک سلول شروع به تایپ طولانی کنید، بخش زیادی از متن از سلول شما بیرون می‌زند و با از هایلات درآمدن سلول، قسمتی از نوشته شما نشان داده نمی‌شود، اما این بخش قابل تغییر است. اگر در سلول مشغول به تایپ هستید، برای باز کردن جا و یک خط جدید از کلید ترکیبی Alt+Enter استفاده کنید (اگر Enter بزنید به سلول بعدی می‌روید!). همچنین می‌توانید در زیر تب Home گزینه  Wrap Text option را انتخاب کنید و تغییرات مورد خود را اعمال کنید.

با عملکرد Validation محدودیت هایی را به داده ها اعمال کنید.

بمنظور حفظ اعتبار داده ها ممکن است گاهی نیاز داشته باشید که مقدار ورودی را محدود کنید و برای گام های بعدی از ترفندهای دیگری استفاده کنید. برای مثال سن افراد در این نمونه فاکتوریست که باید تماماً به عدد بیان شود و افراد دخیل در ان، باید سنی بین 18 تا 60 سال داشته باشند.

برای اینکه مطمئن شوید که هیچ عددی خارج از این بازه وارد، و ثبت نمی شود، به Data->Data Validation->Setting بروید، شرایط مورد نظر را وارد کنید و برای اعمال دستور به Input Message برگردید.

دستوری مانند این: ]لطفا سن خود را تماماً به عدد، بین 18 تا 60 وارد کنید[ کاربران وقتی نمایشگر را روی این محدوده قرار دهندد این دستور را مشاهده می کنند و اگر انچه وارد کرده اند، قابل شناسایی و طبق دستور داده شده نباشد، یک پیام هشدار یا خطا دریافت می کنند.

استفاده از AutoFill

گاهی اکسل با استفاده از هوش خود شما را شگفت زده می‌کند. اگر شما یک سری از داده‌های ترتیبی و تکرار شونده را در سلول‌های متوالی تایپ کنید، اکسل بعد از چند داده قادر است ادامه آن‌ها را تشخیص دهد. مثلا اگر بخواهید تاریخ‌ روز‌ها را مثل 1/1/20, 1/2/20, 1/3/20 تایپ کنید، اکسل بعد از چند سلول می‌تواند ادامه داده‌ها را حدس زده و برای شما تایپ کند. برای اینکار، سلول‌های تایپ شده را انتخاب کرده و از گوشه پایین و سمت راست آخرین سلول (یک شکل کوچک تو پر را مشاهده خواهید کرد) را کلیک کرده و تا سلول‌های پایینی، به اندازه نیاز خود بکشید. داده‌های تکرار شونده در تمام سلول‌های انتخاب شده شما تایپ می‌شوند. شما این کار را هم در ردیف و هم در ستون می‌توانید انجام دهید.

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

جهت یابی سریع با ترکیب کلید Ctrl و دکمه‌های جهتی

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

استفاده از Flash Fill

Flash Fill یکی از هیجان انگیزترین کاربرد‌های Excel است. Flash Fill می‌‌تواند طبق یک الگو در یک مجموعه سلول‌های دیگر (که خودتان وارد می‌کنید) بقیه سلول‌ها را نیز آن گونه تغییر دهد. مثلا فرض کنید که در یک مجموعه سلول، هزار شماره تلفن که یکی از آن‌ها به صورت 2125034111 است نوشته شده‌اند. شما می‌توانید این شماره تلفن‌ها را به صورت (212) -503-4111 درآورید. آیا باید تمام هزار شماره تلفن را تک تک تغییر دهید؟! جواب خیر است؛ شما یکی را تغییر دهید، Flash Fill برای شما 999 تای دیگر را انجام می‌دهد. کافیست در یک یا دو سلول، تغییرات مورد نظر خود را انجام دهید و سپس با استفاده از گزینه Flash Fill تمام تغییرات را به یک باره اعمال کنید.

در تصویر بالا، با قرار دادن سه سلول سمت چپ، در یک سلول سمت راست، اکسل با استفاده از Flash Fill خود حدس زده که قصد داریم سه بخش از یک نام و نام خانوادگی را در کنار هم قرار دهیم، پس خود به ما پیشنهاد این کار را داده است!

داده‌ها را از یک سطر به یک ستون پس و پیش کنید

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

برای اینکار به این ترتیب عمل کنید: محدوده ای که می خواهید جابه جا شود را کپی کنید. نشانگر را به یک محدوده خالی دیگر ببرید و به Home->Paste->Transposeبروید. دقت کنید که این گزینه، تا ابتدا داده ها را کپی نکنید، فعال نمی شود.

داده‌ها را به طور کامل پنهان کنید

تقریبا همه کاربران می دانند که چگونه داده ها را از طریق راست کلیک و انتخاب گزینه Hide، پنهان کنند.

اما این در صورتی که حجم داده ها کم باشد، به کار می آید.بهترین و آسان ترین راه برای اینکه داده ها را به طور کامل پنهان کنید، این است که از دستور Format Cellsاستفاده کنید. محدوده مورد نظر را انتخاب کنید و به Home->Font->Open Format Cells->Number Tab->Custom- بروید، ;;; را تایپ کنید و سپس بر روی ok کلیک کنید.

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

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

 ما 4 ستون داریم که حاوی متن های مختلف است. اگر بخواهیم آنها را با یک مقدار خاص، در یک خانه ترکیب کنیم (مرتبط کنیم) چطور؟

ابتدا آن خانه ای را که بناست نتیجه ترکیب را نشان دهد، تعیین کنید.

فرمول مورد نظر را با استفاده از & همانطور که در تصویر نمایش داده شده، به کار ببرید.

Enter را بزنید: تمام متن هایی که در A2, B2, C2, D2 بودند، با هم ترکیب خواهند شد و نتیجه به صورت LizaUSA25@ در F2 نمایش داده می‌شود.

دگرگونی محتوای متن

با تمام ترفندهایی که در اینجا مطرح شد، من تمام سعیم را کردم تا از ارائه فرمول‌های پیچیده پرهیز کنم. اما هنوز دستورهای ساده و آسانی باقی مانده‌اند که به شما نشان بدهم، مانند UPPER و LOWER و PROPER که می‌توانند با اهداف گوناگون در متن، تغییر شکل ایجاد کنند.

UPPER می‌تواند تمام حروف متن را بزرگ کند، LOWER می‌تواند تمام حروف متن را به حروف کوچک تبدیل کند و PROPER فقط حروف اول کلمات را بزرگ می کند.

برای دریافت گزینه‌های بیشتر، تنها یک کلیک کافیست

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

با این حال، آیا می‌دانید که با حرکت‌دادن نشانگر ماوس بر روی نوار پایینی و راست کلیک بر روی آن، می‌توانید گزینه‌های بیشتری را در اختیار داشته باشید؟

اسم صفحات کار خود را با دو بار کلیک عوض کنید

راه‌های متعددی برای تغییر اسم صفحات وجود دارد.

اکثر کاربران از راست کلیک برای تغییر نام استفاده می‌کنند، که البته این کار زمان زیادی را هدر می‌دهد. بهترین راه اینست که دوبار کلیک کنید، به این ترتیب می‌توانید مستقیماً نام را تغییر دهید.

با استفاده از کلیدهای جهت دار + Ctrl بین سلول‌ها جابه جا شوید

البته واضح است که تنها با کلیدهای جهت دار می‌توانید از یک سلول به سلول دیگری حرکت کنید. اما استفاده از کلیدهای جهت دار + Ctrl این روند را سریع‌تر می‌کند.

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

با استفاده از کلیدهای ترکیبی !+Ctrl + Shift می‌توانید فرمت عدد را به دو رقم اعشار تبدیل کنید. برای تبدیل به فرمت دلار کافی است از کلیدهای ترکیبی $+Ctrl + Shift و برای تبدیل به درصد از کلیدهای ترکیبی %+Ctrl + Shift استفاده کنید.

برای قفل سلول‌ها از کلید F4 استفاده کنید

گاهی اوقات برای کپی داده‌ی یک سلول می‌خواهید فرمول آن هم کپی شود. گاهی هم می‌خواهید که این کپی صورت نگیرد. وقتی می‌خواهید ورودی سلول را قفل کنید می‌توانید یک علامت $ قبل از اسم سلول (مثلا B1) قرار دهید.

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

خلاصه کردن داده‌ها با توابع CountIF و SumIF

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

برای مثال در حالت زیر اسم چند نویسنده به نام‌های سانسا، اریا، راب، جان، ریکون و برن آورده شده است.

هر کدام از آن‌ها چند داستان نوشته‌اند که هر داستانی تعدادی بازدیدکننده داشته است.

برای مرتب کردن داده‌ها در یک سلول خالی دستور CountIF را نوشته تا از ستون اسم نویسنده‌ها اسم آریا را شمارش کند. پس اولین ورودی تابع ستون B4 تا B50 است و دومین ورودی آن سلول G4‌ است که اسم آریا در آن نوشته شده است.

حالا می‌خواهیم کل بازدیدکنندگان هر داستان را شمارش کنیم. در این حالت از تابع SumIF استفاده می‌کنیم. این تابع سه ورودی دارد. ورودی اول ستون B4 تا B50 است.

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

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

استخراج داده‌ها با تابع VLOOKUP

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

تابع VLOOKUP چهار ورودی دارد. ورودی اول مربوط به داده‌ی مورد نظر است. در این مثال ما به دنبال عدد ۱ که رتبه‌ی دانشگاه است، هستیم.

ورودی دوم مربوط به محدوده‌ی داده‌ها است که در این مثال کل ردیف A تا F انتخاب شده است. ورودی سوم مربوط به شماره ستون داده‌ی مورد نظر است. چون ما به دنبال اسم دانشگاه هستیم ورودی را ستون ۲ در نظر می‌گیریم. ورودی آخر مربوط به وقتی است که داده‌ی مورد نظر (مثلا عدد ۱) پیدا نشود که به جای آن عدد ۰ یا عبارت False را قرار می‌دهیم.

پاک کردن متن با توابع LEFT، RIGHT و LEN

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

LEFT بر اساس تعداد کاراکترهای در نظر گرفته یک رشته‌ی متنی را به صورت خروجی تحویل می‌دهد. اولین ورودی این تابع سلول مربوط به رشته‌ی متنی و دومی تعداد حروف مورد نظر است.

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

اما سوال این است که چه تعداد کاراکتر را باید از سمت راست انتخاب کنید؟ چرا که تعداد حروف ایالت‌ها با هم برابر نیستند. در اینجا استفاده از تابع LEN کارگشا خواهد بود.

تابع LEN کل کاراکترهای یک رشته‌ی متنی را شمارش می‌کند و تنها یک ورودی دارد که آن هم سلول مربوط به رشته‌ی متنی است.

حالا با کمک تعداد کاراکترهای به دست آمده از تابع LEN می‌توانید از تابع RIGHT استفاده کنید.

از آن جا که ما می‌خواهیم حروف اختصاری و خط فاصله را حذف کنیم، وردی تابع RIGHT را عدد تابع LEN منهای ۳ قرار می‌دهیم.

ایجاد اعداد تصادفی با تابع RAND

تابع ()RAND می‌تواند اعداد تصادفی بین صفر تا ۱ ایجاد کند. این تابع هیچ گونه ورودی لازم ندارد و داخل پرانتز خالی است. با هر بار فشردن کلید F9 این تابع عدد جدیدی تولید می‌کند. دقت داشته باشید که با ایجاد هر گونه تغییر در فضای کار، یک عدد جدید ایجاد خواهد شد.

استفاده از ابزار Text to Columns

تصور کنید در هر سلول اکسل شما تعدادی متن نوشته شده است. مثلا نوشته شده: کمد/یخچال/میز. شما می‌خواهید هر سه این کلمات را جدا جدا بنویسید. برای این‌ کار تکنیک های اکسل مختلفی وجود دارد که یکی از آن‌ها ابزار Text to Columns است. از تب دیتا می‌توانید ستون مورد نظر خود را انتخاب کنید و با استفاده از گزینه Text to Columns با اعمال محدودیت‌هایی مثل انتخاب چند حرف اول و یا … داده‌های مورد نظر خود را جدا کنید.

استفاده از Paste Special برای جا به جایی سطر و ستون

Paste Special یکی از ترفند های اکسل است که با آن می‌توانید سطر و ستون مجموعه‌ای از داده‌های خود را جا به جا کنید. برای به کار بردن این ترفند اکسل کافی است مجموعه داده‌های خود را کپی کرده و پس از کلیک راست، بر روی گزینه Paste Special کلیک کنید. از این ترفند اکسل برای بدست آوردن ترانهاده یک ماتریس هم استفاده می‌شود.

تایپ یک متن در چند سلول

دلایل مختلفی هست که مجبور می‌شوید یک عبارت را مکررا در چندین سلول تایپ کنید. تایپ کردن این متن می‌تواند از بار چهارم یا پنجم به بعد خسته کننده شود! مخصوصا اگر طولانی باشد. برای این کار، یک ترفند اکسل آسان وجود دارد. سلول‌های مورد نظر خود را با نگه داشتن کلید کنترل و کلیک کردن، هایلات کنید و در آخرین سلول انتخاب شده عبارت مورد نظر خود را تایپ کنید و سپس کلید ترکیبی Ctrl+Enter را فشار دهید. تمام سلول‌های هایلات شده، عبارتی را که شما تایپ کردید، در دل خود خواهند داشت.

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

در هر بخش از Excel می‌توان عکس هم قرار داد. هر بخش، اعم از جدول، نمودار و … می‌تواند هر عنصر از جنس عکس را قبول کند. برای مثال در تصویر بالا می‌توانید پرچم دولتی داکوتای جنوبی را بر روی تکه‌ای از نمودار از طریق Series Options و گزینه Picture or texture fill قرار دهید یا لوگوی یک سایت را از طریق تب Insert و سپس قسمت Pictures بر روی نمودار وارد کنید. حتی می‌توانید تکه‌ای از نمودار را بدون رنگ و پرشدگی قرار دهید که در تصویر بالا مشخص است. شما می‌توانید اشیا مختلف را از بخش Clip art کپی کرده و بر روی عناصر تصویری خود (اعم از نمودار و …) در اکسل قرار دهید. مثلا برای مقدار مصرف آب، تصویر کوچک قطره آب و یا برای نشان دادن مقدار مخارج از تصویر کوچک دلار در کار خود استفاده کنید. البته استفاده زیاد از این تصاویر می‌تواند کار شما را به هم بریزد.

عکس در سلول‌های اکسل

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

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

از Commentها برای اضافه کردن توضیحات (یادداشتها) در یک خانه استفاده می‌شود .

برای درج Comment ، ابتدا خانه مورد نظر را انتخاب می‌کنیم و سپس از منوی Inset گزینه Comment را انتخاب کنید.

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

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

برای ویرایش یا حذف یک Comment از خانه‌ای می‌توانید روی آن خانه R-Click (کلیک راست) کنید و از منوی باز شده (Context Menu) گزینه‌های Edit، Delete را انتخاب کنید.

وقتی موس روی سلول حاوی Comment قرار می‌گیرد، Comment نمایش داده می‌شود  اگر بخواهیم به صورت ثابت نمایش داده شود گزینه Show/Hide Comments را می‌زنیم.

برای عوض کردن Background یک Comment به ترتیب مراحل زیر را انجام می‌دهیم:

1- روی خانه حاوی Comment، R-click کرده و گزینه Edit Comment را انتخاب می‌کنیم.

2- روی کادر Comment ، R-click کرده و گزینه Format Comment را کلیک می‌کنیم.

3- به سربرگ Picture بروید و با استفاده از دکمه Select Picture عکس مورد نظر خود را انتخاب کنید.

در نهایت کاربرگ (sheet) ما با اضافه کردن چند شکل به شکل زیر در خواهد آمد.

دقت داشته باشید که در هنگام Cut/Copy یک سلول، Comment آن نیز کپی می‌شود.

اعداد فارسی در Excel

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

روش ساده عدد فارسی در اکسل

برای تایپ اعداد فارسی در Excel بسیاری از کاربران تنظیمات Regional & Language Options را تغییر می‌دهند و برخی تصور می کنند که تنظیم Excel برای تایپ فارسی مانند Word است.

اما کار بسیار ساده است ، برای تایپ اعداد به شکل فارسی در Excel کافیست که فونت سلول‌ها را تغییر دهیم و یکی از فونتهایی که با کلمه B شروع می شوند مانند B Nazanin , B Lotus را انتخاب کنید.  این فونتها که ظاهرا محصول شرکت برنا رایانه در سالهای استفاده از ویندوز 98 است را بسیار به نام فونتهای استاندارد XP می شناسند.

شما می توانید این فونتها را از لینک زیر دانلود نمایید و سپس فایلها را در فولدر Fonts در Control Panel کپی نمایید.

ذخیره کردن نمودارها به عنوان قالب نموداری

نرم افزار Excel دارای بی‌نهایت نمودار و انواع قالب نمودار است که گاهی هیچ کدام برای شما مناسب نیستند! یکی از نکات کلیدی اکسل، ساختن و ذخیره کردن نمودارهای منحصر به‌فرد و ذخیره کردن آن‌ها به عنوان یک قالب است. این ترفند به عنوان یکی از ترفند های حرفه ای اکسل به شما برای سرعت کارتان کمک بسیاری می‌کند که حتما باید یادگیری آن‌ را در برنامه آموزش اکسل خود بگنجانید. زمانی که نمودار خود را کاملا کشیدید، بر روی آن کلیک راست کنید و با انتخاب گزینه Save as Template آن را به عنوان یک قالب نموداری ذخیره کنید. بعد از این کار تنها کافیست برای کشیدن یک جدول مشابه، داده‌های خود را انتخاب کنید و در تب Insert گزینه Recommended Charts را انتخاب کنید. در بخش قالب‌ها یا Templates نمودار ذخیره شده شما نمایش داده می‌شود.

توجه کنید بخش‌های خاص نمودار شما، مثل متن‌هایی که قبلا نوشته اید و یا عنوان نمودارها در نمودار جدید ترجمه و بروزرسانی نمی‌شوند مگر اینکه در داده‌های انتخاب شده Legend و عنوان جدیدی را تعریف کنید. اما مطمئن باشید تمام تنظیمات دیگر مثل رنگ نمودار، مقیاس، جلوه‌های گرافیکی و … نمودار شما عینا تکرار می‌شود.

کار با سلول‌ها در کاربرگ‌های مختلف

فرض کنید در یک کاربرگ (Sheet) نمرات دانش آموزان خود را تایپ کرده‌اید. اما می‌خواهید معدل آن‌ها را در یک کاربرگ دیگر بدست آورید. در آموزش اکسل این کار جمع سه بعدی (3D Sum) نامیده می‌شود! یعنی شما قصد دارید فرمول نویسی خود را به کاربرگ‌های مختلف ارجاع دهید. برای آدرس دهی به یک کاربرگ دیگر که یکی از تکنیک های اکسل است باید نحوه نوشتار خود را کمی تغییر دهید. فرض کنید می‌خواهید خانه‌های A1 تا A6 کاربرگ قبلی خود را که نام آن Sheet 1 است، آدرس دهی کنید. برای این کار کافیست آدرس دهی را اینگونه بنویسید: A1:A6!Sheet 1. همان طور که می‌بینید باید بعد از علامت (!) نام کاربرگ مورد نظر خود را عینا وارد کنید. اگر نام کاربرگ خود را نمی‌دانید، به پایین صفحه Excel خود مراجعه کنید. تمام کاربرگ‌های اکسل شما به همراه نام‌شان در این بخش نشان داده می‌شوند.

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

شما قطعا با مخفی کردن یک ستون یا یک سط آشنا هستید. برای این کار کافیست سطر و یا ستون خود را هایلایت کنیم، کلیک راست کنیم و Hide را انتخاب کنیم. اما با این کار نمی‌توانید دیگر محتویات سلول خود را ببینید مگر این که آن را Unhide کنید. اما اگر بخواهید تنها داده‌های محدودی را مخفی کنید چه؟ این تکنیک، یکی از جالب ترین تکنیک های اکسل است. برای اینکار داده‌های مورد نظر خود را انتخاب کنید و با کلید ترکیبی Ctrl+num1 به بخش Cell Option بروید. در ستون Category گزینه Custom را انتخاب کنید. در جعبه‌ای که برای تایپ فرمول قرار داده شده، 3 عدد سمیکالن (;;;) را تایپ کنید و سپس Enter بزنید. با این کار محتویات سلول‌های شما مخفی می‌شود، اما اگر بر روی سلول خود کلیک کنید محتویات آن را در بخش Formulla Bar می‌بینید!

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

اگر در بخش قبل، در هنگام بازگرداندن کارپوشه خود نام Personal.XLSB را به عنوان کاربرگ‌های مخفی شده دیدید، تعجب نکنید. این یک کارپوشه شخصی مختص شماست که در هنگام باز کردن نرم افزار Excel برای شما باز می‌شود. اما نقش آن چیست؟! ماکرونویسی! زمانی که شما ماکرو می‌نویسید، ماکرو نوشته شده در تمام کاربرگ‌های شما کار نمی‌کند (بر خلاف نرم افزار ورد). ماکرو نوشته شده، برای هر کاربرگی که در آن ماکرو نوشته شده صادق است و تنها در آن کار می‌کند. اما اگر ماکرویی را در کاربرگ Personal.XLSB نوشته و ذخیره کنید، برای همیشه و تمام کاربرگ‌ها این ماکرو را خواهید داشت. این موضوع یکی از ترفند های حرفه ای اکسل است که دانستن آن بسیار مفید است. تنها کافی‌ست در هنگام رکورد و ذخیره ماکرو، در بخش Store macro in گزینه Personal Macro Workbook را انتخاب کنید.

اگر Excel شما تبی تحت عنوان Developers (برای نوشتن ماکرو) ندارد، نگران نباشید. این تب غیر فعال است و باید برای فعال کردن آن از تب File بخش Options را انتخاب کنید و به تنظیمات Customize Ribbon بروید. در این قسمت در باکس Main Tabs تب Developers را پیدا کنید و تیک آن را بزنید و سپس بر روی گزینه OK کلیک کنید. از این پس تب Developers در انتهای تب‌های شما نمایش داده می‌شود.

استفاده از ابزار Conditionally Format

به مقدار فروش 20 بازاریاب شرکت خود نگاه کنید! کدام یک بیشترین فروش را داشته است؟ کدام یک کمترین؟ کدام بازاریاب‌‌ها بیشتر از میانگین و کدام کم‌تر از میانگین فروخته‌اند؟ پاسخ تمام این سوالات را می‌توانید تنها با یکی ترفند های اکسل بدهید. استفاده از ابزار Conditional Formatting که یکی از مفید ترین ترفندهای اکسل است می‌تواند راهگشای شما باشد. Conditional Formatting می‌تواند به خواست شما، مثلا کمترین داده را قرمز نشان دهد، بزرگترین را سبز نشان دهد، با کشیدن یک نمودار و یا پر کردن هر سلول به صورت بصری مقدار را به شما نشان دهد که برای مقایسه بسیار مناسب است و یا …. . ابزار Conditional Formatting در تب Home واقع شده است. با باز کردن آن و دانستن کمی زبان انگلیسی می‌توانید ترکیبی از فیلتر‌ها را برای داده‌های خود اعمال کرده و داده‌های مشخص را به گونه‌ای دیگر نشان دهید.

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

ایجاد لیست آبشاری

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

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

اضافه کردن اسکرین شات از صفحه درون یک کارپوشه

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

وارد کردن داده‌های Excel به نرم افزار Word

سال پیش وارد کردن هزاران داده از اکسل به ورد یا پاورپوینت کار بسیار زجرآوری بود. اما امروز کار ساده‌ای است! با دانستن یکی از ترفند های اکسل می‌توانیم این کار را به آسانی انجام دهیم. اگر شما نمودارهای اکسل را به صورت کپی پیست در ورد وارد کنید، در صورت تغییر در داده‌های اکسل، نمودار کپی شده در نرم افزار ورد شما هم تغییر می‌کند. اگر دوست دارید تنها تصویری از نمودار خود را وارد کنید، دو راه دارید. راه اول، استفاده از Paste Special است. می‌توانید نمودار کپی شده در اکسل را در ورد Paste Special کرده و به عنوان یک عکس وارد کنید. همچنین نرم افزار اکسل ابزاری مخصوص خود برای این کار را دارد. در نرم افزار اکسل خود به تب Home بروید و منو Copy را انتخاب کنید. در این بخش گزینه Copy as Picture را علامت بزنید تا از نمودار شما تنها یک عکس کپی شود.

استفاده از علامت دلار ($)

علامت دلار ($) یکی از مهم ترین نکات کلیدی اکسل است که می‌تواند مشکلات زیادی را حل کند! تصور کنید 20 داده که مربوط به نمره‌های دانش آموزان‌تان است را در اختیار دارید و می‌خواهید از آن‌ها معدل بگیرید. اما تمام آن‌ها دارای ضریب ثابت 3 هستند. باید ابتدا تمام ستون را در سلولی که عدد 3 نوشته شده است ضرب کنید، اما اگر Auto fill استفاده کنید نکته جالبی را متوجه می‌شوید. از سطر دوم به بعد شما تمام اعداد یا ارور هستند یا صفر! (حتما امتحان کنید) مشکل چیست؟! اگر از Auto fill استفاده کرده‌اید، از سطر دوم به بعد، داده شما در عدد 3 ضرب نمی‌شود، بلکه در سلول پایینی و پایین‌تر از آن ضرب می‌شود. اما راه حل چیست؟ راه حل این مشکل یکی از کاربردی ترین ترفند های اکسل است. حتما پیشنهاد می‌کنیم اگر به دنبال آموزش اکسل هستید، این ترفند را کاملا یاد گرفته و تمرین کنید.

برای ثابت کردن یک سلول در فرمول نویسی کافیست پشت نام سطر و ستون آن علامت دلار ($) قرار دهید. مثلا اگر عدد ضریب 3 گفته شده در سلول A5 قرار دارد با نوشتن A5 به صورت $A$5 بنویسید (یا کافی‌ست بعد از نوشتن سلول مورد نظر نشان گر کیبورد را ابتدای نام سلول قرار داده و کلید F4 را بزنید) اکنون اگر از Auto fill استفاده کنید، خانه A5 همیشه در فرمول شما ثابت می‌ماند. استفاده از علامت دلار در پشت حرف A به معنای ثابت کردن ستون و استفاده از آن در پشت عدد 4 به معنای ثابت کردن سطر است. اگر قصد دارید یک ستون یا و یک سطر را ثابت نگه دارید و فرمول شما با Auto fill کردن تنها به موازات آن سطر و یا ستون (و نه سطر و ستون‌های دیگر) پیش برود می‌توانید علامت دلار را تنها پشت عدد و یا حرف مربوطه قرار دهید. پیشنهاد می‌کنیم این ترفند را تمرین کنید.

استفاده از Quick Analysis

اگر قصد دارید که داده‌های خود را از بزرگ به کوچک بچینید و یا به نوعی از آن داده‌ها، داده‌ای خاص استخراج کنید و یا نتیجه خاصی بگیرد، داده‌ها را انتخاب کنید و باکس Quick Analysis را که به شما نمایش داده می‎شود را انتخاب کنید. این باکس به شما پیشنهادات جالب و سریعی می‌دهد که می‌تواند در زمان شما صرفه جویی بسیاری کند. به نوعی شما می‌توانید ابزار Conditional Formatting و ساخت جدول، کشیدن نمودار و … را در یک نگاه ببینید و به سرعت تصمیم بگیرید.