چگونگی حذف سطرها وستون های خالی در اکسل

نر م افزار اکسل یکی از نرم افزارهای کاربردی مجموعه آفیس است. اگر با این نرم افزار کار کرده باشید ممکن است در سطر و ستون های مختلف شیت های خالی بوجود آورده باشید.  اگر در صفحه‌ی شیت اکسل تعداد زیادی سطر خالی یا Blank وجود داشته باشد، می‌توان بر روی آن‌ها جداگانه کلیک راست کرده و با انتخاب گزینه Delete، آن‌ها را حذف کرد. با این وجود راهی سریع و آسان‌تر برای پاک کردن سطر و ستون‌های خالی در نرم‌افزار Excel 2016 وجود دارد.در اداغمه با ما همراه باشید تا با چگونگی حذف سلول های خالی بیشتر آشنا شویم.

1. محدوده‌ای از Spreadsheet  را که می‌خواهید سطرهای خالی آن  پاک شود، انتخاب نمایید. مطمئن شوید که سطر بالای اولین سطر خالی و سطر پایین آخرین سطر خالی را نیز انتخاب کرده باشید.
بر روی گزینه Find & Select در قسمت Editing از تب Home کلیک کرده و گزینه Go To Special  را از منوی آن انتخاب کنید.

2. کمی صبر کنید تا پنجره تنظیمات باز شود. در پنجره Go To Special گزینه Black را انتخاب کنید و بر روی OK کلیک نمایید.

3. به سندتان نگاه کنید. مشاهده می‌شود که در محدوده انتخابی در Sheet تمامی سلول‌های غیر خالی از حالت انتخاب خارج شده‌اند و تنها سلول‌های خالی در حالت Select هستند.

4.در مرحله بعدی در بخش Cell در تب Home بر روی گزینه Delete کلیک کرده و از منوی باز شده گزینه Delete Sheet Rows را انتخاب کنید.

5. تمامی سطرهای خالی حذف می‌شوند و سطرهای دارای مقدار پشت سر هم قرار خواهند گرفت.

6. همچنین شما می‌توانید به همین صورت ستون‌های خالی را نیز حذف نمایید. برای انجام این کار ابتدا محدوده‌ای را که در آن ستون‌های Black وجود دارند، انتخاب کنید. از انتخاب ستون سمت چپ اولین ستون خالی و ستون سمت راست آخرین ستون خالی مطمئن شوید. مجددا بر روی، Find & Select در قسمت Editing از تب Home کلیک کرده و گزینه Go To Special را انتخاب کنید.
مجددا گزینه Blank را در پنجره Go To Special انتخاب و تایید نمایید.

حذف سطر و ستون های خالی در excel

پنجره Go To نمایش داده می شود. بر روی کلید OK کلیک کنید. در پنجره باز شده چک باکس Blanks را انتخاب کرده و بر روی کلید OK کلیک کنید.

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

1- ابتدا ستون یا محدوده مورد نظر خود را انتخاب کنید.

2- در سربرگ Home از  Excel  (به این نوار در Excel 2007 به بعد Ribbon می‌گویند) گزینه Conditiona Formatting را بزنید.

3- در لیست باز شده، گزینه Highlight Cell Ruls را بزنید.

4- در این لیست گزینه Duplicate Value  را بزنید.

5 – در پنجره آخر، از شما پرسیده می‌شود که سلولهای تکراری چه رنگی شوند.

چگونه در Excel از ورود اعداد یا مقادیر تکراری توسط کاربر جلوگیری کنیم؟

 می‌توانید روی سلولهای Excel ابزار Validation را فعال کنید، ابزار Excel Validation مقادیری را که شما در سلول وارد می‌کنید چک می‌کند و در صورتیکه که معتبر باشد آنرا قبول می‌نماید.

معتبر بودن با شرطی که شما قبلا روی این سلول گذاشته اید سنجیده می‌شود.

ابتدا ستون مورد نظر را انتخاب نمایید.

1- دقت نماید که آدرس سلول فعال شما چیست، مثلاسلول فعال A1 است.

2- از Ribbon به سربرگ Data بروید و سپس گزینه Validation را انتخاب کنید.

3-تنظمات را انجام دهید.

تابع  COUNTIF در اکسل مشخص می کند که در ستون A، چند بار مقادر سلول A1 تکرار شده است و هدف ما این است که این مقدار تکراری نباشد، بنابراین باید این تعداد برابر با یک باشد، که تکراری نشود.

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

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

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

برای تأیید آن می توان کلید Enter را زده و یا در نوار ابزار بالا از این قسمت طبق شکل زیر آن را تأیید کرد.

البته اگر از نوشتن آن منصرف شده باشیم می توانیم، کلید X یا Cancel و یا کیبورد، کلید ESC را فشار دهی

ویرایش متن در excel

سلول متنی را انتخاب کرده و می خواهیم که کلمه نرم افزار را حذف کنیم. ستون A را به اندازه اطلاعات داخل آن باز می کنیم.

حال به سراغ نوار فرمول می آییم. توسط این نوار می توانیم به راحتی اطلاعات درون سلول ها را مدیریت کنیم. پس با کمک درگ کردن، کلمه نرم افزار را در نوار فرمول انتخاب کرده و با زدن کلید Deleteآن را حذف می کنیم و سپس کلید Enter را می زنیم.

و یا می توان درون سلول 2 بار کلیک کرده و هر قسمتی را که بخواهیم از متن مورد نظر حذف کنیم.

پس 2 روش راحت برای ویرایش متن در سلول را یاد گرفتیم و با کلیک روی سلول تغییراتی را که اعمال کرده بودیم را تثبیت می کنیم.

جا به جایی بین سلول ها

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

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

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

1. در سلول A1 2 بار کلیک می کنیم و می نویسیم فاکتور فروش.
2. حالا با کلیک و درگ تا سلول F1 آن را می آوریم و با استفاده از گزینه Merge & Center این سلول ها را یکی می کند.

3. حال به سراغ سلول A2 می رویم. اکنون می توانم با موس روی آن کلیک کنم و یا در کیبورد، کلید Enter را فشار دهم.

در حالی که روی سلول A1 قرار گرفته اید با زدن کلید Enter به A2 می آیید. البته می توانید از کلید جهت دار  Arrow key down هم استفاده کنید. (منظور کلید جهت دار پایین روی کیبورد است.)

در سلول A2 ، شماره فاکتور را تایپ کنید. دوباره کلید Enter را زده و می نویسیم: نام مشتری.

و دوباره Enter را زده و کلمه ردیف را تایپ می کنیم.

حال می خواهیم به سراغ سلول B4 برویم. این بار از کلید Tab استفاده می کنیم و یا از Arrow Key (کلید جهت دار سمت چپ) و اکنون درون سلول (لیست قطعات) را تایپ می کنیم.

حالا در سلول C4 می نویسیم: مدل قطعه

به همین ترتیب قیمت هر قطعه، تعداد و جمع را وارد می کنیم.

به سلول B5 آمده و در این سلول می نویسیم: 1

به سلول B5 رفته و می نویسیم: مادربرد.

به همین ترتیب این سلول ها را پر می کنیم. بعد از سلول B13، 2 سطر خالی در نظر می گیریم و در سلول B20 هزینه اسمبل را وارد می کنیم. این فاکتور فروش را  تا سطر 24 پر کردیم و در درس های آینده بیشتر در مورد آن بحث خواهیم کرد.

اگر بخواهیم به سطر اول اطلاعاتمان دسترسی پیدا کنیم کافی است از کلید ترکیبی Ctrl+Home استفاده کنیم. و اگر بخواهیم به سلول آخری که اطلاعات در آن وارد شده است یعنی به سطر 24 برویم باید از کلید Ctrl + End استفاده کنیم.

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

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

اگر بخواهیم مستقیم به سلول C4 برویم و تک تک این خانه های خالی را انتخاب نکنیم، می توانیم همراه با زدن این فلش رو به بالای دکمه Arrow key کلید کنترل را نگه داریم. حالا دیگر سلول های خالی انتخاب نمی شوند و مستقیما به سلول C4 می رویم. و با زدن کلید +Ctrl جهتدار رو به پایین می توانیم دوباره به سلول C21 برویم.

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

اگر اطلاعاتی که وارد می کنیم بسیار زیاد باشد و مجبور باشیم آنها را صفحه به صفحه ببینیم می توانیم از کلید Page+Up و page down هم استفاده کنیم. با زدن این دو کلید می توانیم تمام اطلاعاتمان را در اکسل، به صورت صفحه به صفحه ببینیم.

ذخیره  کردن فایل اکسل

حالا می خواهیم با هم فاکتوری که ساخته ایم را ذخیره کنیم. به منوی Office  رفته و روی save کلیک می کنیم. بلافاصله پنجره save as برای ما باز می شود. در این قسمت ما به تمام قسمت های هارددیسک دسترسی داریم و می توانیم محل ذخیره سازی را مشخص کنیم. در قسمت File name می توانیم نام مورد نظر را وارد نماییم.

نام این فایل را فاکتور می گذاریم و با کلیک روی دکمه  Save، این فایل را ذخیره می کنیم و پنجره save as هم بسته می شود.

حال شما نام فایل را می توانید در قسمت Title bar اکسل (نوار عنوان) آن مشاهده کنید

انواع داده ها در اکسل

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

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

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

در سلول D5 می خواهیم قیمت یک قطعه را وارد کنیم. مثلا قیمت 200000 تومان (2000000  ریال) را در اینجا وارد می کنیم.

و سپس دکمه Enter را می زنیم.

این دقیقا مانند یک عدد معمولی وارد می شود. در سلول D6، آمده و روی علامت $ در زبانه Home و ریبون Number کلیک می کنیم.                                                               

حالا دوباره روی D6 رفته، عدد 2000000  را وارد می کنیم و در نهایت بدین شکل در خواهد آمد.

الان این عدد را به صورت یک مبلغ یا واحد پول به اکسل معرفی کردیم که واحد آن دلار است و البته 2 تا صفر اضافه شده است که توسط شکل زیر آن را حذف می کنیم.

حال روی سلول کلیک راست کرده و گزینه Format Cells را انتخاب می کنیم.

البته راه های دیگری هم برای دسترسی به این پنجره وجود دارد. برای مثال روی سلول کلیک کرده و به ریبون Number رفته و روی $ کلیک کرده و سپس گزینه More Accounting Formatsرا انتخاب می کنیم و وارد پنجره Format Cells می شویم.

و یا در ریبون Number ، روی فلش کنار آن طبق شکل زیر کلیک کرده و در Format Cells گزینه Currency را انتخاب می کنیم.

سپس در قسمت Symbol ، Persian ریال را انتخاب کرده به شکل زیر و Ok می کنیم.

راستی باید حواسمان به Decimal Places باشد که اعشار را مشخص می کند.من آن را روی صفر می گذاریم. البته می توانیم ان را روی 1 قرار دهیم و در سلول اکسل که وارد می کنیم آن را به تومان بنویسیم.

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

انتقال و کپی برداری از داده ها

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

سپس روی مثلا سلول G6 رفته و گزینه paste را انتخاب می کنیم. به صورت زیر:

حال شما آن عدد را به صورت  # (Number sign) می بینید. قبلا هم گفته شد که دلیل آن به خاطر این است که عرض سلول از داده کمتر است که می توان عرض آن را افزایش داد.

با دقت نگاه کنید.

خط چین شکل 75  هنوز از دور سلول حذف نشده است. ما می توانیم هر جای دیگری که بخواهیم برویم و آن را paste کنیم.

و یا می توانیم به جای یک سلول، به وسیله درگ کردن چندین سلول را انتخاب کنیم. و سپس دستور Paste را صادر کنیم.

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

کلید ترکیبی ctrl+Z را می زنیم تا کپی هایی که گرفتیم از بین بروند.

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

اما بین این دو روش یک تفاوت عمده وجود دارد.

هنگامی که Copy کرده و دستور  Paste (Ctrl+V) را انتخاب می کنیم، یک آیکون به نام Paste Option کنار آن به شکل زیر باز می شود.

حالا روی آیکون paste option کلیک می کنیم.

ولی در حالتی که سلول را Enter می کردیم این آیکون ظاهر نمی شد.

کاربردهای آن:

1. Keep Source Formatting: محتویات و اطلاعات سلول کپی شده را به سلول انتخابی انتقال می دهد.

2. Match Destination Formatting: فقط داده کپی می شود و فرمت آن سلول کپی نمی شود.

3. Formatting Only:  این بار به جای محتویات فقط فرمت آن سلول کپی شده در اینجا منتقل می شود.

  Paste کردن مواجه نشویم، این بار وقتی که در سلولی دستور Pasteنکته: برای اینکه با گزینه ای مثل # هنگام 

را صادر می کنیم، گزینه Keep Source Columns With را انتخاب می کنیم.

عرض این سلول با عرض سلول کپی شده در واقع یکی شد.

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

حالا این بار گزینه Link Cells را انتخاب می کنیم.

به ظاهر که تغییری دیده نشد. حالا روی سلول D6 آمده و روی آن کلیک می کنیم و کلید Enter را زده و محتویات آن را تغییر می دهیم.

به محض زدن Enter محتویات آن سلول ها نیز تغییر می کند.

حالا کار دیگری انجام می دهیم. روی سلول D6 کلیک راست می کنیم.

Format Cells را انتخاب کرده و General در قسمت Category را انتخاب می کنیم و آن را به فرمت یک سلول معمولی تبدیل می کند.

برای انتقال سلول ها از سلولی به سلول دیگر هم می توان از دستور Cut استفاده کرد و هنگامی که اطلاعات را در سلول مورد نظر Paste کنیم، از سلول اول به سلول مورد نظر انتقال پیدا می کند.

وقتی که یک سلول در حال انتخاب است و موس را روی حاشیه آن می آوریم، فلش به فلش 4 جهته تبدیل می شود که هنگامی که آن را درگ کنیم می توانیم به هر سلولی که بخواهیم انتقال دهیم. البته این طور نیست که فقط 1 سلول انتخاب شود.

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

خاصیت ویژه سلول ها در پر کردن داده 

یک صفحه جدید ایجاد می کنیم.

حالا مثلا در سلول D1 کلیک می کنیم و شروع به وارد کردن یک سری از اطلاعات می کنیم.

روزهای هفته را به ترتیب می نویسیم.

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

در واقع اکسل خودش برای وارد کردن داده به ما کمک می کند.

راه دیگر کپی کردن داده های یک یا چند سلول استفده از Fill handle است.

ما الان به سراغ یک سلول می آییم که شنبه را در آن نوشته ایم و روی آن کلیک می کنیم.

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

به گوشه سمت راست سلول انتخابی خوب نگاه کنید. یک مربع کوچک را می بینید. ما موس را روی آن آورده و خواهیم دید که شکل موس تغییر می کند.

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

به این قابلیت Autofill گفته می شود.

به محض اینکه موس را رها کنیم یک آیکون کوچک به نام Autofill Options ظاهر می شود.

این آیکون 3 گزینه دارد:

1. Copy Cells: یعنی کل محتویات سلول کپی شود.

2. Fill Formatting Only: تنها فرمت آن در سلول کناری اش کپی می شود.

3. Fill Without Formatting: فقط محتویات سلول کپی می شود و دیگر فرمت آن سلول کپی نمی شود.

ما با کمک Autofill می توانیم چند تا سلول هم کپی کنیم. هیچ فرقی نمی کند.

اما کار اصلی آن چیز دیگری است که معمولا از آن استفاده کرده و بسیار هم کاربردی است.

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

Mon را در یک سلول تایپ می کنیم و به سمت پایین درگ کرده و خواهیم دید که یک گزینه به نام   Fill Series، اضافه می شود.

اعداد به ترتیب و پشت سر هم قرار می گیرند.

ممکن است این سؤال پیش بیاید که چرا این اتفاق در مورد روزهای هفته به فارسی رخ نداد.

جواب: Autofill در مورد داده های عددی عمل می کند و آنها را به ترتیب قرار می دهد.

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

یا در واقع یک سری را خودمان در اکسل درست کنیم.

ساختن یک سری یا لیست در اکسل یسیار راحت است.

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

پنجره Excel Options باز می شود.

سپس سراغ کلید Edit Custom lists آمده و روی آن کلیک می کنیم تا پنجره Custom Lists برایمان باز شود.

در این پنجره قسمت سمت چپ، 4 لیست مشخص است که روزهای هفته و ماه های سال هستند و یک گزینه به نام New List ، که پیش فرض در حال انتخاب است. ما سمت راست شروع به نوشتن لیست روزهای هفته به فارسی می کنیم.

سپس دکمه Add را زده و به قسمت Custom Lists اضافه می کنیم. و سپس ok را می زنیم.

و پنجره excel option را هم کلیک می کنیم.

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

پس ما بسیار راحت توانستیم در اکسل یک لیست بسازیم.

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

در این مثال 3 چک لیست گروه سنی، شغل و علاقه مندی ساخته شده و با فرمول نویسی به یکدیگر لینک شده اند. به طور مثال تنها با گذاشتن تیک های مربوطه و با صرف کمترین زمان شما می توانید تعداد پزشکان بین 20 تا 24 سال علاقه مند به سینما را پیدا کنید.

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

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

به صورت زیر سه جدول را ایجاد می کنیم و در کنار هر ردیف آن یک چک باکس می سازیم. در جدول ها به ترتیب؛ گروه سنی، نوع مشاغل و نوع علاقه مندی را در ستون Cمیاوریم. ستون D را برای نوشتن فرمول محاسبه تعداد، خالی می گذاریم.

برای ساخت چک باکس در اکسل  ابتدا باید تب DEVELOPER را در تب های اصلی اکسل فعال کنیم. برای این کار در اکسل 2013 از تب FILE بر روی Options کلیک کرده و در پنجره  پیشرو از منوی سمت چپ گزینه Customize Ribbon را انتخاب می کنیم. در سمت چپ در منوی آبشاری با زدن تیک گزینه Developer را فعال می کنیم.

به شیت گزارش باز می گردیم، از تب Developer قسمت Controls و از جعبه ابزار Insert قسمت Form Controls گزینه Check Box را انتخاب می کنیم.

در قسمتی که چک لیست هارا ایجاد کردیم، در کنار جدول ها و درست مقابل هر یک از ردیف های آن، یک چک باکس رسم می کنیم. پس از رسم چک باکس بر روی آن کلیک راست کرده و از منو، قسمت Edit Text، عبارت Check را از جلوی چک باکس حذف می کنیم. سپس از همان منو، پنجره Format Controls … را باز کرده و از تب Control (مطابق شکل زیر) در قسمت Cell link آدرس سلول سمت راست چک باکس را برای لینک کردن (F4) می دهیم. این کار را برای تمامی چک باکس ها انجام می دهیم. در صورت صحیح انجام شدن موارد ذکر شده در صورت تیک دار کردن چک باس، سلول مجاور TRUE را نشان داده و در صورت برداشتن تیک FALSE می شود.

تا اینجای کار، مقدمات انجام شده است. حال به قسمت سخت کار یعنی نوشتن فرمول محاسبه تعداد، در سلول های ستون D می رسیم.
با توجه به پیچیدگی فرمول در ادامه تا جای امکان فرمول نوشته شده را شرح خواهیم داد. برای این منظور فرمول نوشته شده در سلول D4 را تا رسیدن به نتیجه فرمول؛ یعنی عدد 2، شرح می دهیم. باید توجه داشت که فرمول نوشته شده آرایه ای یک بعدی و نوشته شده در یک سلول می باشد. این فرمول را می توان تنها در سلول های ستون D برای یک چک لیست کپی کرد و فرمول نوشته شده در چک لیست های دیگر از لحاظ آدرس دهی کمی تفاوت دارند و نمی توان به طور کامل از فرمول های چک لیست های دیگر در آن استفاده کرد.

در فرمول بالا از ترکیب توابع IF، SUMPRODUCT، ISNUMBER، MATCH به صورت توابع آرایه ای استفاده شده است. برای راحتی کار فرمول را 3 تکه کرده ایم.
1) DATABASE!$B$3:$B$12=C4:
فرمول بالا قصد شمردن تعداد گروه سنی در هر ردیف چک لیست را از روی دیتا بیس را دارد.

در این فرمول؛ گروه سنی 24- 20 سال واقع در سلول C4، با گروه های سنی واقع در ستون B در شیت دیتا بیس مقایسه شده و در صورت مطابق بودن، جواب TRUE دریافت می شود. تنها بدلیل آرایه ای بودن فرمول، قابلیت چک کردن یک سلول (C4) را با سلول های بازه دیگر(ستون B دیتابیس) داریم. در صورتی که اگر فرمول، معمولی نوشته می شد تنها اجازه مقایسه دو سلول را داشتیم. در روش آرایه ای این مقایسه در حافظه موقت برنامه، سلول به سلول (مطابق شکل بالا) انجام شده و تعداد تطابق ها بدست می آید (در شکل بالا 2 سلول می باشد) و در محاسبات فرمول اصلی استفاده می شود. (در فرمول مسئله ما تابع SUMPRODUCT از این 2 تطابق پیدا شده استفاده می کند.)

2) ((ISNUMBER(MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13),0

این فرمول در مسئله ما نقش شمردن تعداد مشاغل در هر ردیف چک لیست، از روی دیتابیس را دارد.
تابع کلیدی در فرمول بالا MATCH می باشد، (MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13),0

"یادآوری: تابع   ([MATCH(lookup_value, lookup_array, [match_type از سه قسمت تشکیل شده؛ قسمت اول که سلول مورد نظر ما برای تطابق است، قسمت دوم بازه ای که سلول مورد نظر برای تطابق، در آن جستجو می شود و قسمت سوم که اختیاریست، برای دقت جستجو بکار می رود که با سه عدد 0 و 1 و 1- اجرا می شود؛ اگر صفر بگذاریم دقیقا سلول مورد نظر را پیدا می کند، اگر 1 بگذاریم، درصورتی که سلول ما در بازه جستجو موجود نباشد، بزرگترین عددی که به رقم مورد نظر ما نزدیک تر و از آن کوچکتر است را پیدا می کند و در صورت استفاده از 1-،  اگر سلول ما در بازه جستجو موجود نباشد، کوچکترین عددی که به رقم مورد نظر ما نزدیک تر و از آن بزرگتر است را پیدا می کند. در صورت یافتن تطابق در بازه جستجو، تابع MATCH جایگاه ترتیبی سلول مورد نظر را در بازه جستجو نشان می دهد. (به طور مثال سلول مورد نظر، پنجمین سلول در بازه جستجو می باشد.)

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

اگر بخوایم یک بازه یا آرایه را در بازه ای دیگر جستجو کنیم کار کمی پیچیده شده و باید فرمول را به صورت آرایه ای بنویسیم. همانطور که در شکل زیر می بینید برای یافتن جایگاه اعداد ستون Iدر ستون G هر سه خانه ستون K را انتخاب کرده و فرمول شکل زیر را بصورت آرایه ای می نویسیم و در نتیجه با یک فرمول آرایه ای جایگاه هر سه عدد را در بازه جستجو بدست می آوریم. ایرادی که در بالا ذکر شد در این مثال نیز صادق است و تنها اولین جایگاه 2 را در جواب به ما می دهد یعنی جایگاه اول بازه جستجو این در حالیست که 2 در جایگاه پنجم نیز تکرار شده است. "

ابتدا برای باز شدن گره فرمول بالا به سراغ فرمول (IF($F$10:$F$13,$C$10:$C$13می رویم. این فرمول در قسمت دوم تابع MATCH و به عنوان بازه جستجو استفاده می شود. ولی با توجه به شرطی که گذاشته شده هر شغلی نمی تواند جزء سلول های بازه جستجو باشد و تنها شغل هایی که در چک لیست مشاغل تیک بخورند شرط بالا را رعایت کرده و در بازه جستجو قرار می گیرند.

همانطور که در شکل مشاهده می کنید تمامی سلول های بازه F10 تا F13 شرط را رعایت کرده و TRUE می باشند. پس سلول های نظیر آنها در ستون C جزء بازه جستجو می باشند و درصورتی که تیک هریک از چک باکس های بازه F10 تا F13 را برداریم، سلول نظیر آن از بازه جستجو  حذف می گردد.

حال که تکلیف بازه جستجو مشخص شد، به فرمول MATCH باز می گردیم. قسمت اول فرمول ویا سلول های مورد نظرما برای تطابق؛ شامل مشاغل آورده شده در ستون مشاغل دیتابیس می شود

نتیجه فرمول MATCH همانطور که مشاهده می کنید به صورت بازه بالا در فرمول اصلی استفاده می شود. بطور مثال مطابق شکل بالا، وکیل جایگاه دوم را در بازه جستجو در اختیار دارد.
چک باکس در اینجا نقش فیلتر را بازی کرده و در صورت برداشتن آن شرط فرمول IF در مورد سلول نظیر چک باکس، برقرار نشده و آن سلول از بازه جستجو حذف می شود. و در بازه نتیجه فرمول MATCH چون سلولی که برای تطابق جستجو می شود در بازه جستجو موجود نمی باشد، اثر آن در بازه نتیجه به صورت ارور #N/A نمایش داده خواهد شد و در شمارش فرمول اصلی بحساب نمی آید. در قسمت سوم فرمول MATCH هم از صفر استفاده شده تا فقط جایگاه انطباق های دقیق در بازه نتیجه ظاهر شود.

((ISNUMBER(MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13),0

حالا از فرمول شماره 2 تنها تابع ISNUMBER باقی مانده است. کار این تابع؛ تبدیل نتایج بدست آمده در بازه نتیجه MATCH به صورتTRUE و FALSE می باشد. در صورتی که در بازه نتیجه MATCH، ارور #N/A نباشد ویا به عبارتی عدد باشد، ISNUMBER به ما نتیجه TRUE یا 1 و در صورت ظاهر شدن ارور به ما نتیجه FALSE ویا 0 می دهد.
در کل می توان از فرمول 2 نتیجه گرفت که اگر در چک لیست گزینه ای تیک دار نباشد، فرمول اصلی برای آن صفر منظور کرده و در محاسبات استفاده نمی کند و در صورت تیک دار بودن، 1 منظور شده و در محاسبات استفاده می شود.

در این شکل، وکیل بدلیل نداشتن تیک و عدم رعایتشرط فرمول IFاز بازه جستجو حذف شده که اثر آن در بازه نتیجه MATCH و بازه نتیجه ISNUMBER قابل مشاهده است.

3) ((ISNUMBER(MATCH(DATABASE!$D$3:$D$12,IF($F$16:$F$18,$C$16:$C$18,""),0

این فرمول دقیقا مانند فرمول شماره 2 عمل کرده در مسئله ما نقش شمردن تعداد علاقه مندی در هر ردیف چک لیست از روی دیتابیس را دارد. فقط در آدرس دهی باید آدرس های مربوط به علاقه مندی را منظور کرد در نهایت با ترکیب سه فرمول فوق، سه نتیجه به صورت سه بازه 0 و 1 یا TRUE و FALSE بدست می آید که با قرار دادن آنها در تابعSUMPRODUCT، نتیجه نهایی (برای چک باکس اول 2 می شود) بدست می آید

*(((SUMPRODUCT((DATABASE!$B$3:$B$12=C4)*(ISNUMBER(MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13),0

(((ISNUMBER(MATCH(DATABASE!$D$3:$D$12,IF($F$16:$F$18,$C$16:$C$18,""),0)

به یک نکته باید توجه کرد که فرمول SUMPRODUCT نوشته شده تنها در صورتی بکار می آید که تیک چک باکس را گذاشته باشیم. به همین خاطر فرمول SUMPRODUCT را با یک فرمول IF به تیک دار کردن چک باکس و یا TRUE بودن سلول نظیر آن مشروط می کنیم. در صورت نداشتن تیک در ستون تعداد، مقداری برای این چک باکس یا بازه سنی محاسبه نمی شود.

*(((IF(F4,SUMPRODUCT((DATABASE!$B$3:$B$12=C4)*(ISNUMBER(MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13),0=

("",((((ISNUMBER(MATCH(DATABASE!$D$3:$D$12,IF($F$16:$F$18,$C$16:$C$18,""),0)

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

1) چک لیست گروه سنی = کپی فرمول در خانه های D4 تا D8
(((IF(F4,SUMPRODUCT((DATABASE!$B$3:$B$12=C4)*(ISNUMBER(MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13),0}=

{("",((((ISNUMBER(MATCH(DATABASE!$D$3:$D$12,IF($F$16:$F$18,$C$16:$C$18,""),0)

2) چک لیست شغل = کپی فرمول در خانه های D10 تا D14

(((IF(F10,SUMPRODUCT((DATABASE!$C$3:$C$12=C10)*(ISNUMBER(MATCH(DATABASE!$B$3:$B$12,IF($F$4:$F$7,$C$4:$C$7),0}=

{("",((((ISNUMBER(MATCH(DATABASE!$D$3:$D$12,IF($F$16:$F$18,$C$16:$C$18,""),0)

3) چک لیست علاقه مندی = کپی فرمول در خانه های D16 تا D18
(((IF(F16,SUMPRODUCT((DATABASE!$D$3:$D$12=C16)*(ISNUMBER(MATCH(DATABASE!$B$3:$B$12,IF($F$4:$F$7,$C$4:$C$7),0}=

{("",((((ISNUMBER(MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13,""),0)

همانطور که ملاحظه می کنید؛ این فرمول برای تمام چک لیست ها، مشابه می باشد و تنها در آدرس دهی تفاوت دارد. این فرمول را می توان برای هر تعداد چک لیست، با هر تعداد ردیف، بسط داد و تنها باید دقت داشت که به ازای هر چک لیست یک فرمول MATCH باید نوشته شود.

کاراکتر جدا کننده پارامترها در توابع اکسل                                                                                      به صورت پیش فرض کاراکتر “;” یا semicolon هست. بعضی مواقع  که در سیستم این کاراکتر چیزی غیر از semicolon تعریف شده. در این حالت چون به کاراکتر “;” عادت کردیم این مساله خیلی آزار دهنده می شود  این مشکل به خصوص زمانیهب وجد می آید که تنظیمات علائم در سیستم عامل روی زبان فارسی باشد.

به عنوان مثال در بعضی از سیستم ها  که هنگام نصب سیستم عامل تنظیم زبان  روی گزینه Persian بوده این کاراکتر به جای “;” روی کاراکتر “؛” تنظیم شده که خیلی باعث اشتباه می شود. برای تغییر کاراکتر جدا کننده پارامترها در توابع اکسل به کاراکتر دلخواه می توانیم  از روش زیر استفاده کنیم.

ابتدا کنترل پانل سیستم رر باز می کنیم و  روی گزینه Change date, time or number formats کلیک می کنیم.

در پنجره ای که باز می شود  روی گزینه Additional settings…‎ کلیک می کنیم.

حالا در پنجره ای که باز می شود در قسمت list separator هر کاراکتری رو که دوست داشتیم به جای کاراکتر پیش فرض قرار دهیم.

با این کار کاراکتر جداکننده پارامترها به کاراکتری که ما تعریف کردیم تغییر می کند.

در آموزش های گذشته در مورد خطا های اکسل به صورت کامل توضیح داده ایم با آموزش اکسل  امروز همراه باشید تا با معانی خطاها نیز اشنا شویم                                                                                          

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

!VALUE#: 
این خطا به این معنیاست که شما نوع اشتباهی از داده رو به عنوان ورودی فرمول استفاده کردید. به عنوان مثال ممکنن است د ر تابعی که پارامتری رو از نوع یک سلول می گیره یک محدوده رو وارد کرده باشید و یا در فرمول جایی که باید به عدد ارجاع داده شود متن به جای عدد وجود داشته باد. به عنوان مثال در فرمول A1/A2 شما مقدار موجود در سلول A2 یک متن باشه.  ?NAME#: 
این خطا زمانی رخ میده که شما نام تابعی رو در فرمولتون اشتباه نوشته باشید، مقادیر متنی استفاده شده در فرمول رو داخل علامت ” نگذاشته باشید و یا پرانتزهای خالی مربوط به تابع رو جا گذاشته باشید.

!NUM#: این خطا یعنی یکی از اعدادی که در فرمول شما وجود دار د مشکل دارد. به عنوان مثال عدد خیلی خیلی بزرگ یا خیلی خیلی کوچک هست.

DIV/0 #: این خطا نشان دهنده ی  این هست که  جایی از فرمولتون مقداری رابر صفر تقسیم کرده باشید. ممکنه مقدار یکی از سلول هایی که در فرمولتون استفاده کردید صفر باشه یا فاقد مقدار باشد. اکسل سلول هایی رو که فاقد مقدار هستند در محاسبات صفر در نظر می گیره و اگر جایی عددی رو بر اونها تقسیم کرده باشید این خطا نشون داده می شود.

!REF#: این خطا به معنی این است که شما در فرمول به سلولی اشاره کردید که وجود ندارد. این خطا معمولا زمانی رخ می دهد که شما سلول هایی رو پاک کنید یا به عنوان مثال فرمول را  که به صورت نسبی نوشته شده و در آن به سلول A3 اشاره شده، از سلول A4 اون شیت به سلول A2 شیت دیگه کپی کنید.

N/A#: این خطا معمولا زمانی نشان داده می شود که مقدار مورد نظر شما پیدا نشود. به عنوان مثال شما مقدار موردنظر شما در یک تابع LOOKUP در هیچ یک از سلول های محدوده ای که برای تابع تعریف کردید وجود نداشته باشه.

!NULL#: این خطا بیانگر این است که در فرمول جایی که باید از علایم ریاضی استفاده کنید از فاصل استفاده کردید. به عنوان مثال به جای فرمول  =A1+A2+A3  اشتباها مقدار  =A1+A2 A3 راوارد کردید. یکی از دلایل دیگری که باعث می شود  این فرمول نمایش داده شوند این است که  جایی در فرمول برای مشخص کردن یک محدوده بین دو سلول علامت: رو جا انداخته اید.

########: این علامت در واقع نشان دهنده خطای فرمول نویسی نیست. وقتی این علامت نشان داده می شود  بدین معناست که  که اکسل  با موفقیت فرمول را محاسبه کنه اما  مقدار محاسبه شده رادر سلول نمایشدهد. برای حل این مشکل معمولا نیاز دارید که کمی عرض ستون راافزایش دهید تا مقدار محاسبه شده در ستون قابل نمایششود  و یا اینکه فرمت سلول را به گزینه مناسبی تغییر دهید. 

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

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

1. استفاده بیش از حد از توابع Volatile  در اکسل: توابع Volatile توابعی هستند که با هر بار محاسبه مجدد ورکبوک آنها هم مجدداً محاسبه می شوند. برخی از این توابع شامل RAND، RANDBETWEEN،NOW،TODAY،OFFSET،CELL،INDIRECT می شود. حالا اگر از این توابع در فایل زیاد استفاده کرده باشید احتمالاً می توانید حدس بزنید با هر بار تغییر دادن مقدار یک سلول چه بلایی سر فایلتون میا ید. بنابراین بهتراست تا حد امکان از این توابع زیاد استفاده نشود و از توابع   Index، Sumif در اکسل  و … استفاده شود
2. استفاده زیاد از Conditional formatting در EXCEL  : فرمول های موجود در Conditional formatting ها هم با هر بار محاسبه ورکشیت دوباره محاسبه می شود. بنابراین باید در استفاده از آنها هم دقت زیادی کرد.
3. وجود سلول های بدون استفاده در شیت ها: گاهی ممکن است  سلول هایی که توسط اکسل به عنوان سلول های فعال یک شیت لحاظ می شود با سلول هایی که در شیت فعال هستند متفاوت باشد.
   
   برای اینکه متوجه شویم اکسل کدام سلول را به عنوان آخرین سلول فعال شیت در نظر گرفته است می توانیم از کلید ترکیبی ctrl+end استفاده کنیم. اگر سلولی که اکسل به عنوان سلول آخر در نظر گرفته اشتباه باشد باعث می شود حجم فایل بدون دلیل بالا رود.                                            برای حل این مشکل  باید سطرها و ستون های اضافه را انتخاب و پاک کنید. بعد فایل را ذخیره نماییم. این مساله بخصوص در مورد افزایش حجم فایل ها خیلی اثرگذار است.
4. لینک کردن به سایر ورکبوک ها: لینک کردن یک ورکبوک به ورکبوک های دیگر باعث کاهش سرعت می شود. بهتراست تا جایی که امکان دارد اطلاعات را در یک ورکبوک قرار دهید یا تا جایی که امکان دارد به ورکبوک های خارجی کمتری لینک کنید.
5. وجود شیت های بیش از حد: بهترست  تا حد ممکن ورکبوک هایی که می سازیم تعداد شیت های کمتری داشته باشد. این مساله هم در افزایش سرعت فایل اکسل بی تاثیر نیست.

رعایت موارد بالا در تهیه فایل های اکسل به خصوص موارد 1 و 3 تا حدود زیادی می تواند به افزایش سرعت فایل های اکسل و کاهش حجمبسیار تاثیر گذار باشد.

  در نرم افزار اکسل اگر بخواهید سطر یا ستون‌های خالی اکسل را حذف کنید واضح است که راست کلیک روی هر سطر و انتخاب گزینه‌ی Delete معقول نیست.

 باماهمراه باشید تا با روش سریعتری آشنا شویم

در ابتدا به روش حذف سطرهای خالی توجه کنید. روش حذف ستون‌های خالی شبیه سطرها است که آن را ادامه بررسی می‌کنیم.

مراحل حذف سطرهای خالی  در اکسل

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

انتخاب ناحیه‌ در اکسل

در تب Home  روی گزینه‌ی Find & Select کلیک کنید و روی لیست آبشاری ایجاد شده گزینه‌ی Go To Special را انتخاب کنید.

مرحله‌ی اول حذف سطرهای خالی

یک دیالوگ باکس باز می‌شود که در آن گزینه‌ی Blanks را انتخاب کرده و  روی دکمه‌ی OK کلیک می‌کنیم.

مرحله‌ی دوم حذف سطرهای خالی

حال مثل قبل تمام ناحیه‌ای که انتخاب کردیم هایلایت نیست و تنها خانه‌های خالی آن ناحیه به حالت انتخاب درآمده‌اند.

نمایش فیلدهای خالی

مجدداً وارد تب Home شده و در بخش Cells روی گزینه‌‌ی Delete کلیک کرده و در منوی آبشاری ایجاد شده گزینه‌ی  Delete Sheet Rows را انتخاب می‌کنیم.

مرحله‌ی آخر حذف سطرهای خالی

همه‌ی سطرهای خالی حذف می‌شوند و سطرهای باقی‌مانده مجاور یکدیگر قرار می‌گیرند.

سطرها در مجاورت یکدیگر قرار گرفته‌اند

مراحل حذف ستونهای خالی در excel

ابتدا ناحیه‌ی مورد نظر خود را مثل بخش قبلی انتخاب نمایید.

انتخاب ناحیه‌ در اکسل

سپس مثل قبل در تب Home  و در قسمت Editing روی گزینه‌ی Find & Select  کلیک کرده و در منوی آبشاری ایجاد شده Go To Special را انتخب می‌کنیم.

مرحله‌ی اول حذف ستون‌های خالی

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

مرحله‌ی دوم حذف ستون‌های خالی

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

نمایش فیلدهای خالی

در تب Home در قسمت Cells روی گزینه‌ی Delete کلیک می‌کنیم. سپس Delete Sheet Columns را از منوی ایجاد شده انتخاب می‌نماییم.

مرحله‌ی آخر حذف ستون‌های خالی

ستون‌های خالی حذف می‌شوند و ستون‌های باقی‌مانده در کنار یکدیگر قرار می‌گیرد                                  امید واریم از آموزش اکسل  امروز هم مثل همیشه لذت برده باشید                                                               آرزوی ما موفقیت روز افزون شماست

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

CTRL+PgUp , CTRL+PgDn

بین محیط‌های کاری یا همان worksheet ها جابجا می‌شویم.

CTRL+SHIFT+(

تمام ردیف‌های ناحیه‌ی انتخاب که در حالت پنهان یا hide قرار دارند را به حالت عادی باز می‌گرداند.

CTRL+SHIFT+&

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

CTRL+SHIFT+-

ابتدا ناحیه‌ای که در آن توسط شرتکات CTRL+SHIFT+& حاشیه (Border) ایجاد شده باشد را انتخاب می‌نماییم و با زدن CTRL+SHIFT+- بردرهای داخل و روی این ناحیه از بین می‌روند.

CTRL+SHIFT+$

قبل اعداد علامت $ قرار می‌دهد. ضمناً اعداد منفی را داخل پرانتز قرار می‌دهد.

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

CTRL+SHIFT+%

فرمت اعداد ناحیه را با درصد نمایش می‌دهد.

CTRL+SHIFT+^

اعداد را با نماد علمی نمایش می‌دهیم.

در نماد علمی یک عدد را به شکل ضرب دو عدد می‌نویسیم. عدد اول عددی بین 1 تا 10 است و عدد دومی توانی است برای عدد 10 تا با ضرب شدن در عدد اول، عدد اولیه به دست آید. مثلاً 11 به شکل 1٫1E+1 و در اکسل به صورت 1٫1E+1 نوشته می‌شود.

CTRL+SHIFT+#

فرمت اعداد به صورت روز، ماه، سال نوشته می‌شود.

CTRL+SHIFT+@

فرمت اعداد ناحیه‌ی انتخابی را به صورت ساعت در می‌آورد. با تایپ 15:20 برایتان 3:20PM نمایش داده می‌شود.

CTRL+SHIFT+!

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

در گرد کردن اعداد و نمایش تا دو رقم اعشار، اگر رقم سوم از 4 بیشتر بود رقم دوم یکی اضافه می‌شود. در غیر این صورت رقم دوم تغییری نمی‌کند.

CTRL+SHIFT+*

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

CTRL+SHIFT+:

زمان فعلی سیستم را نمایش می‌دهد.

CTRL+SHIFT+”

مقدار سلول را از سلول بالایی خود کپی می‌کند.

CTRL+SHIFT+Plus (+)

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

CTRL+Minus (-)

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

CTRL+;

تاریخ فعلی را نمایش می‌دهد.

CTRL+1

دایلاگ‌باکس فرمت سلول فعال را نمایش می‌دهد.

CTRL+2

فرمت ضخیم شدن یا همان Bold را حذف یا اعمال می‌کند.

CTRL+3

فرمت کج نوشته شدن متن یا همان Italic را حذف یا اعمال می‌کند.

CTRL+4

فرمت خط کشی زیر متن یا همان Underlining را حذف یا اعمال می‌کند.

CTRL+5

فرمت خط کشی روی متن را حذف یا اعمال می‌کند.

CTRL+8

گروه هایی که توسط تب Data و بخش Outline ایجاد شده است را آشکار می‌کند یا بالعکس.

CTRL+9

سطرهای انتخاب شده در اکسل را پنهان می‌کند. البته با دبل کلیک روی بین شماره‌ی سطرهای حذف شده، یکی یکی آشکار می‌شوند. حال اگر سطر بالای بازه‌‌‌‌ی انتخابی و سطر پایین آن را انتخاب کنیم و روی شماره‌ی بین این دو سطر دبل‌کلیک کنیم کل سطرها با هم از حالت hide یا مخفی در می‌آیند.

CTRL+0

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

در تمام نرم افزار ها بعضا مشکلاتی رخ می دهد که کاربران آن را گیج می کند و می تواند ساعتها آنها را سردرگم کند. اکسل هم مستثنی نیست به موارد ذیل توجه کنید:

1- عدم نمایش تبها و شیتها در قسمت پائینی اکسل

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

• روی گزینه file کلیک کنید و به قسمت option بروید.
• در پنجره باز شده به تب Advanced بروید.
• در قسمت Display option for this workbook تیک گزینه Show Sheets Tab را بزنید.

2- حل مشکل اعدادی که به صورت متن وارد شده اند

فرض  کنید در ستون A1 الی A12 مجموعه ای از اعداد را وارد کرده اید و در A13 فرمولی می نویسید که A1:A12 را جمع کند و  می بینید جواب اشتباه است و با کمی دقت متوجه می شوید که در گوشه بالا سمت راست برخی از سلولها رنگ سبزی جلب توجه می کند که می خواهد یادآوری کند که در این سلول داده عددی با قالب متن وارد شده و در نتیجه در حاصلجمع محاسبه نمی شود. راه حل چیست؟

• شما می توانید تک تک سلولهایی که این مشکل را دارند انتخاب کرده و گزینه Convert to number را بزنید که راه خسته کننده با درصد خطای بالایی است.
• راه دوم این است که در یک سلول که مطمئن هستید از نوع عددی است عدد 1 را وارد کنید و روی سلول کلیک راست کرده و Copy را بزنید و سپس محدوده ای که مشکوک هستید در آن اعداد به صورت متنی ذخیره شده باشند راست کلیک کرده و گزینه Paste Special را بزنید و مانند تصویر زیر گزینه Multiply را انتخاب کرده و ok کنید.

3- حذف فضای خالی ما بین نوشته ها در excel

بعضا فاصله های ناخواسته ای بین نوشته در اول یا آخر و یا وسط آنها وجود دارد که شما می خواهید آنها را حذف کنید.راه حل:

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

=Trim(a1)

4- حذف سلولهای خالی موجود در محدوده در اکسل و یا تغییر نام آنها

محدوده مورد نظر که دارای سلولهای خالی هست را انتخاب نمائید.

• کلیدهای ترکیبی Ctrl+G را بزنید
• روی گزینه Special کلیک کنید تا پنجره Go To Special باز شود.
• حال گزینه Blank را انتخاب کرده و OK کنید.
• می بینید که سلولهای خالی به حالت انتخاب در آمده است که می توانید آنها را حذف کنید.
• اگر خواستید آنها را تغییر نام دهید در همان حالت انتخاب متن مورد نظر خود را تایپ کرده و کلیدهای ترکیبی Ctrl+Enter را بزنید.

5- جدا کردن نام و نام خانوادگی که در یک ستون وارد شده و تقسیم به دو ستون

فرض می کنیم ستونی داریم که در آن وارد شده    “نادر بحری تهران” و شما می خواهید این ستون به سه ستون   “نادر” “بحری” “تهران” تبدیل شود.

• ستون مورد نظر خود را انتخاب کرده و کلیدهای ترکیبی Alt+A+E بزیند
• پنجره Convert Text To Column نمایش داده خواهد که مانند فیلم آموزشی عمل کنید

6- مشاهده داده های تکراری در محدوده

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

• محدوده مورد نظر را انتخاب نمائید و کلیدهای ترکیبی Alt+H+L+H+D را بزنید (در تب Home روی Conditional Formatting کلیک کنید) که منوی مربوط به Conditional Formatting باز می شود که باید Duplicate values را انتخاب کرده و نتیجه را ببینید.

7- جایگزین کردن عدد صفر با سلول خالی

• محدوده مورد نظر را انتخاب نمائید روی گزینه File کلیک کنید
• به Option بروید  و از تب Advanced بروید
• در قسمت Display Option for this worksheet تیک گزینه Show a Zero in cells… را بزنید

8- پاک کردن فرمت و تغییراتی که در شیت اعمال کرده اید.

• محدوده مورد نظر را انتخاب نمائید و به تب Home بروید
• در قسمت Editing روی Clear کلیک کنید و گزینه Clear Formatting را انتخاب کنید.