اضافه کردن یک یا چند ردیف خالی بین ردیف‎های متوالی در اکسل

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

برای اینکار 3 روش وجود دارد:

روش اول) روش آسان‌تر ولی غیرمستقیم

برای مثال اگر داده‌ها بصورت زیر باشد، طبق مراحل زیر عمل کنید:

1) در کنار داده‌ها به یک ستون خالی نیاز داریم. بطور مثال برای داد‌های بالا از ستون F استفاده می‌کنیم.

2) در سلول F1 عدد 1 و در سلول F2 عدد 2 را درج می‌کنیم.

3) اعداد 1 و 2 را انتخاب می‌کنیم و روی علامت fill handle دو بار کلیک می‌کنیم. 

اکسل بطور خودکار سلول‌های ستون F را پر می‌کند. بطور مثال از 1 تا 9

4) سپس از سلول‌های پر ستون F (از F1 تا F9) کپی می‌گیریم و در سلول F10 پیست می‌کنیم.

5) اکنون سلول‌های ستون F (از F1 تا F18) را انتخاب می‌کنیم و از تب Data روی دکمه Sort A to Z کلیک می‌کنیم.

6) پس از کلیک پیغام زیر ظاهر می‌شود گزینه Expand the selection راتیک می‌زنیم.

7) در پایان روی دکمه Sort کلیک کنیم. نتیجه زیر حاصل می‌شود.

* اگر می‌خواهید بجای یک ردیف خالی، 2 یا 3 ردیف خالی اضافه کنید باید 2 یا 3 بار اعدادی که در ابتدا در ستون F درج کردید را کپی کنید.

روش دوم) با استفاده از ماکرو نویسی در اکسل (قابل استفاده برای حالت ستونی)

1) کلیدهای Alt + F11 را فشار دهید یا از تب Developer قسمت code گزینه Visual Basic را انتخاب کنید. پنجره Microsoft Visual Basic ظاهر می‌شود. در این پنجره از تب Insert گزینه Module را انتخاب نمائید.

) روی کلید F5 را فشار دهید. با انجام اینکار پنجره زیر ظاهر می‌شود. سپس داد‌ه‌های مدنظرتان را انتخاب کنید.

3) در پایان روی ok کلیک کنید تا مشابه روش قبل بین تمام ردیف‌ها یک ردیف خالی ایجاد شود.

روش سوم)  ابزار Kutools for Excel

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

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

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

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

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

جلوگیری از ورود اعداد یا مقادیر تکراریدر اکسل

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

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

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

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

2- از Ribbon به سربرگ Data بروید و سپس گزینه Validation را اتابع  COUNTIF(A:A,A1) در اکسل مشخص می کند که در ستون A، چند بار مقادیر سلول A1 تکرار شده است و هدف ما این است که این مقدار تکراری نباشد، بنابراین باید این تعداد برابر با یک باشد، که تکراری نشود.                                                      3-تابع  COUNTIF در اکسل مشخص می کند که در ستون A، چند بار مقادیر سلول A1 تکرار شده است و هدف ما این است که این مقدار تکراری نباشد، بنابراین باید این تعداد برابر با یک باشد، که تکراری نشود.

وقتی کار پوشه ای را باز می کنیم. کاربرگها حاوی نامهای پیش فرض Sheet 1, Sheet 2 , … هستند. برای نامگذاری Sheet ها می توان تا حداکثر 21 کاراکتر استفاده کرد. به جز کاراکترهای /. \. *.؟. [. ] بقیه کاراکترها مجاز می باشند.

تغییر نام Sheet ها:

روش اول:

1- انتخاب Sheet

2- انتخاب منوی Format

3- انتخاب گزینه Sheet

4- انتخاب گزینه Rename

روش دوم:

1- راست کلیک بر روی Sheet

2- انتخاب گزینه Rename

روش سوم:

دابل کلیک بر روی Sheet

اضافه کردن Sheet ها:

روش اول:

1- انتخاب Sheet که می خواهیم Sheet جدید قبل از آن قرار گیرد.

2- انتخاب منوی Insert

3- انتخاب گزینه Work Sheet

4- OK

روش دوم:

1- راست کلیک بر روی Sheet هایی که می خواهیم Sheet جدید قبل از آن قرار گیرد.

2- انتخاب گزینه Insert

3- انتخاب گزینه Work Sheet

4- Ok

حذف Sheet ها  در اکسل:

روش اول:

1- انتخاب Sheet

2- انتخاب منوی Edit

3- انتخاب کزینه Delete Sheet

روش دوم:

1- راست کلیک بر روی Sheet

2- انتخاب گزینه Delete

انتقال Sheet ها در excel
روش اول:
1- انتخاب Sheet

2- انتخاب منوی Edit

3- انتخاب گزینه move or copy sheet

4- در پنجره باز شده در کادر Before sheet نام Sheet هایی را که می خواهیم این Sheet قبل از آن قرار گیرد انتخاب می کنیم یا اگر می خواهیم به انتها انتقال دهیم، Move to end را انتخاب می کنیم.

5- OK

روش دوم:

1- راست کلیک بر روی Sheet

2- انتخاب گزینه Move or Copy

3- در پنجره باز شده در کادر Before Sheet نام Sheet یی را که می خواهیم این Sheet قبل از آن قرار گیرد انتخاب می کنیم.

4- OK

کپی کردن Sheet ها در یک پوشه:

مانند انتقال Sheet ها می باشد فقط کافی است در انتها در کنار Create a copy تیک بزنیم.

انتقال یک Sheet از book هائی به book دیگردر EXCEL:

1- راست کلیک بر روی Sheet

2- انتخاب گزینه move or copy sheet

3- در کادر To Bookنام فایل مورد نظر را که می خواهیم Sheet را به آن انتقال دهیم انتخاب می کنیم.

4- در قسمت Before Sheet هم نام Sheet هایی راکه می خواهیم Sheet انتقالی قبل از آن قرار گیرد، انتخاب می کنیم.

5- OK

کپی کردن Sheet های از یک کار پوشه به کار پوشه دیگر:

تمام مراحل ماند انتقال است فقط در کادر Create a copy تیک می زنیم.

تذکر: باید توجه داشته باشیم که Book ئی که می خواهیم فایل را به آن منتقل یا کپی کنیم حتما باز باشد.

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

اضافه کردن یادداشت توضیحی در اکسل

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

از طریق منوی راست کلیک و با کلید میانبر Shift+F2

سلول مورد نظر را انتخاب کنید. کلید میانبر Shift+F2 را بزنید یا راست کلیک کرده و گزینه Insert Comment را انتخاب کنید. متن مورد نظر در گوشه سمت راست را تایپ کنید. در خارج مستطیل کلیک کنید.

به طور اتوماتیک نام کاربری، به یادداشت‌ توضیحی در اکسل اضافه می‌شود، برای تغییر نام کاربری مراحل زیر را انجام  دهید:

در قسمت FILE، بر روی گزینه Options کلیک کنید و به قسمت General منتقل شوید. در این قسمت به بخش Personalize your copy of Microsoft Office  رفته و نام کاربری خود را تغییر دهید.

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

برای اضافه کردن تاریخ یادداشت، پس از نوشتن آن، کلید های Ctrl و + را بفشارید. ساعت یادداشت نیز با فشردن کلیدهای Ctrl,Shift و + اضافه می‌شود.

ویرایش و حذف یادداشت توضیحی در اکسل

برای ویرایش یادداشت‌ توضیحی در اکسل، سلول مورد نظر‌ را انتخاب کنید. راست کلیک کرده و گزینه Edit Comment را انتخاب کنید. کادر محتوی یادداشت قابل ویرایش خواهد شد. متن را تغییر دهید.

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

نشان دادن یا پنهان کردن یادداشت‌ توضیحیدر excel

سلول مورد نظر‌ را انتخاب کنید. به منوی REVIEW بروید. در زیر منوی Comments اگر بر روی Show/Hide Comment یکبار کلیک نمایید، یادداشت نوضیحی نمایش داده می‌شود، اگر بار دوم کلیک نمایید یادداشت پنهان خواهد شد. در صورتیکه بر روی Show All Comments کلیک کنید، تمامی یادداشت ها نشان داده خواهد شد.

جابجا کردن و تغیر اندازه یادداشت توضیحی در اکسل

با توجه به حجم متن یادداشت‌ توضیحی در اکسل، بعضاً متن در مقابل داداه‌ها قرار می‌گیرد (متن هایی که همواره نشان داده می‌شوند). برای جابجا کردن یادداشت های توضیحی در اکسل، سلول مورد نظر‌ را انتخاب کنید. راست کلیک کرده و گزینه Edit Comment را انتخاب کنید. نشانگر ماووس را بر روی حاشیه کادر محتوی یادداشت نگه دارید، علامت چهار جهته حرکت نمایان خواهد شد. به مکان دلخواهتان درگ کنید.

جستجو در بین یادداشت های توضیحی در اکسل

بعضاً تعداد یادداشت های توضیحی به حدی زیاد است که پیدا کردن یک یادداشت خاص بسیار زمانبر می‌شود. برای جستجو در میان یادداشت ها به منوی HOME رفته در زیر منوی Editing کلیلک کنید. در زیر منوی Editing به بخش Find & Select  بروید. در این بخش Find را انتخاب کنید (یا کلید میانبر Ctrl+F را بزنید). در منوی باز شده دکمه Options را بزنید تا پنجره گسترده تر شود. در این پنجره در قسمت Look in گزینه Comment را انتخاب کنید. در قسمت Find what کلمه مورد نظر را تایپ کرده با زدن دکمه Find all تمامی یادداشت هایی که این کلمه را دارند، بیابید. با زدن دکمه Find next دربین سلول هایی که یادداشت توضیحی آنها این کلمه را دارند، حرکت خواهید کرد.

تغییر قالب یادداشت توضیحی در اکسل

برای تغییر قالب یک یادداشت توضیحی در اکسل، سلول حاوی یادداشت را انتخاب کنید. راست کلیک کرده و گزینه Edit Comment را انتخاب کنید. بر روی حاشیه یادداشت توضیحی راست کلیک کرده و گزینه Format Comment را انتخاب کنید. در پنجره بازشده قادر به تغییر دادن فرمت نوشته یادداشت خواهید بود. در تب های مختلف این پنجره می‌توانید عملیات مختلفی انجام دهید.

اگر در وسط کادر یادداشت راست کلیک کتید و Format Comment را انتخاب کنید، فقط می‌توانید فونت نوشته را تغییر دهید.

کدام سلول ها یادداشت توضیحی دارند

زمانی که یک یادداشت‌ توضیحی در اکسل اضافه می‌کنید، یک مثلث قرمز رنگ (نشانگر یادداشت توضیحی) در بالا گوشه سمت راست سلول ظاهر می‌شود. گزینه‌های مختلف چگونه نشان دادن یادداشت توضیحی یا نشانگر آن، در بخش Options قرار دارد. در قسمت FILE، بر روی گزینه Options کلیک کنید و به قسمت Advanced منتقل شوید. در این قسمت به بخش Display  رفته و یکی از گزینه های مربوط به نحوه نمایش یادداشت توضیحی را انتخاب کنید.

No comments or indicators: با انتخاب این گزینه نه نشانگرها نمایش داده می‌شود و نه خود یادداشت ها.

Indicators only, and comments on hover: در این حالت فقط نشانگر یادداشت نمایش داده می شود. همچنین یادداشت زمانی ظاهر می‌شود که ماوس را روی سلول ببرید.

Comments & indicators: هم نشانگرها و هم یادداشت ها نمایش داده می‌شوند.

در صورتیکه نه نشانگر و نه یادداشت نمایش داده نمی‌شوند، با استفاده از Go To Special تمامی سلول هایی که یادداشت توضیحی دارند را انتخاب و در صورت نیاز رنگ آنها را تغییر دهید. برای این کار به منوی HOME رفته در زیر منوی Editing کلیلک کنید. در زیر منوی Editing به بخش Go To Special بروید. در منوی باز شده دکمه رادیویی  Comment را انتخاب کرده و OK کنید. تمامی سلول هایی که یادداشت توضیحی دارند انتخاب خواهند شد. در زیر منوی Font  از منوی HOME رنگ مورد نظر برای سلول ها را انتخاب کنید. سلول ها رنگی شده‌اند.

پرینت کردن یادداشت‌ توضیحی در اکسل

دو مدل برای پرینت کردن یادداشت‌ توضیحی در اکسل وجود دارد. یک اینکه یادداشت ها را در همان مکانی که دیده می‌شوند پرینت کنید. دو اینکه تمامی یادداشت ها را در انتهای برگه و به صورت جداگانه پرینت کنید. برای پرینت یادداشت در مکان دیده شدن، از منوی  REVIEW بر روی Show All Comments کلیک کنید، تمامی یادداشت ها نشان داده خواهد شد. به منوی PAGE LAYOUT بروید. در زیر منوی Print Titles به تب Sheet منتقل شوید. در این تب به قسمت Comment  رفته و در منوی کرکره‌ای باز شونده گزینه As displayed in sheet را انتخاب کنید.

برای پرینت تمامی یادداشت ها در انتهای برگه، دستوررات فوق را دوباره اجرا کنید. ولی در منوی کرکره‌ای باز شونده گزینه At end of sheet را انتخاب کنید.

اضافه کردن یادداشت توضیحی به فرمول

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

اضافه کردن یادداشت توضیحی به کد ماکرودر EXCEL

اضافه کردن یادداشت در کد نویسی، به فهم بهتر آن کمک می‌کند. در مراجعات بعدی نیز اصلاح یا ویرایش کد را آسان تر خواهد کرد. بعضاً نیز قصد دارید بخشی از کد به صورت موقت اجرا نشود، در این موارد نیز تبدیل کردن آن به یادداشت توضیحی، بهتر از پاک کردن آن قسمت است! برای این منظور در خطی که می‌خواهید یادداشت اضافی را بنویسید، قبل از یادداشت، یک آپاستروف (‘) بگذارید. یا اینکه یادداشت را نوشته و از ابزار ویرایشی ویژوال بیسیک استفاده کنید.

کاربرد تابع CONCATENATE

فرض کنید یک فایل اکسل دارید که در  ستون A نام و در  ستون B نام خانوادگی رانوشته اید و واکنون میخواهیدکه در ستون C نام و نام خانوادگی را با هم داشته باشید به صورتی که بین نام و نام خانوداگی هم فاصله وجود داشته باشد.                                                                                                       در ادامه باما   همراه باشید                     

در ابتدا فرمول زیر را در  سلول C1 وارد مکنیم:

=A1&” “&B1

این فرمول به راحتی برای ما مقادیر موجود توی سلول A1 و B1 را با در نظر گرفتن یک فاصله بین اونها به ما نمایش میده. ولی باید بدونید که برای این کار یک تابع مخصوص وجود دارد به نام CONCATENATE

کاربرد تابع  CONCATENATE در اکسل

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

جدا کردن نوشته های یک ستون و تقسیم آن به دو ستون در excel

فرض کنید در سلولی  ستون A1:A10 تعداد 10 تا نام نام خانوادگی وارد کردید که نام با فاصله از نام خانوادگی جدا شده اند ولی همراه سلول قرار دارند و هدف ما این است که  کاری کنیم که نامها در ستون B1:B10 قرار بگیرn و نام خانوادگی در ستون C1:C10 نوشته بشه برای این کار اکسل یک قابلیتی در نظر گرفته به نام Text To Columns که متن  را از ستونها جدا می کنه.

برای استفاده از این قابلیت باید به تب Data برید و گزینه Text To Columns رو انتخاب کنید  گزینه Next رو بزنید.

در قسمت بعدی بالا سمت چپ از شما میخواهد که جدا کننده متن راانتخاب کنید که باید Space را انتخاب کنیدو Next رو بزنید.در پنجره بعدی از شما میخواهد که فرمت جدید و محل و دیتای جدا شده را مشخص کنید کهدر این مثال ما فرمت General و سلول B1 را انتخاب می کنیم که اکسل به صورت خودکار نامها را در  ستون B و نام خانوادگی ها را در ستون C قرار میده.

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