📌 9 ترفند اکسل،( با موس و کیبورد سریعتر از همیشه کار کنید) 🖥

📌 9 ترفند اکسل،( با موس و کیبورد سریعتر از همیشه کار کنید) 🖥

✅ ترفند شماره 1 اکسل: جمع زدن سریع با کلید ALT و =

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

✅ ترفند شماره 2 اکسل: کلیدهای تنظیم فرمت اعداد

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

✅ ترفند شماره 3 اکسل: نمایش همه فرمول‌ها

با زدن کلید + CTRL حالت شیت اکسل عوض می‌شود و تمامی فرمول ها را به شما نماش می‌دهد. فراموش نکنید که با دوباره زدن همین کلید مجدد به حالت عادی شیت بر خواهید گشت.
نکته: علامت را در سمت چپ عدد 1 کیبورد کامیپوتر باید پیدا کنید. روی این کلید علامت ~ هم می بینید.)

✅ ترفند شماره 4 اکسل: پرش به ابتدای یک سطر یا ستون از لیست با کیبورد

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

✅ ترفند شماره 5 اکسل: کپی کردن سریع فرمول در یک ستون از لیست

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

✅ ترفند شماره 6 اکسل: اضافه و حذف یک سطر یا ستون

تقریبا یک کار مهم در اکسل اضافه و یا کم کردن سطر و ستون ها است . برای اینکار می توانید به راحتی از کلید- + CTRL (کنترل و منها) برای حذف و برای اضافه کردن = + CTRL+SHIFT (کنترل شیفت مساوی) برای اضافه کردن استفاده کنید.
توجه: اگر قبل از زدن این کلیدها سطر یا ستونی را انتخاب کرده باشد خود اکسل متوجه می‌شود منظور شما چیست و دیگر پنجره تصویر متحرک زیر را نمایش نمی دهد.

✅ ترفند شماره 7 اکسل: تنظیم عرض ستون‌ها

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

✅ ترفند شماره 8 اکسل: جابجا شدن سریع بین شیت ها

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

✅ ترفند شماره 9 اکسل: Double-click بر روی ابزار Format Painter

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

 

آموزش نحوه فرمول نویسی در اکسل

فرمول نویسی پیشرفته در اکسل

همان طور که بیان کردیم یکی از ویژگی های مهم ، قابلیت فرمول نویسی در اکسل است. نحوه فرمول نویسی در اکسل بسیار ساده است و با کمی آموزش می توان آن را فرا گرفت. برای نوشتن فرمول در اکسل اولین کاری که باید انجام داد ، گذاشتن یک علامت « = » در سلول است. علامت مساوی در اکسل نشان دهنده این است که در سلول مربوطه قصد نوشتن فرمول را دارید. علاوه بر علامت مساوی ، از علامت های مثبت و منفی نیز می توان استفاده نمود ولی توصیه نمی شود. فرمول نویسی در اکسل دارای ساختار و عناصری است که در ادامه به آن ها اشاره خواهیم کرد.

فرمول نویسی پیشرفته در اکسل | ویراتیم

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

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

 

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

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

  • فرمول نوشته شده در هر سلول اکسل فقط در مورد همان سلول است و در محتوای سلول های دیگر تاثیری ندارد.
  • خروجی تمام فرمول ها در اکسل یک عدد است و نمی توان انتظار داشت با نوشتن یک فرمول چند خروجی به دست آورد.
  • اکسل برای عملگرهای مختلف حق تقدم قائل می شود. به عنوان مثال فرمول توان نسبت به فرمول ضرب در اکسل و هم چنین فرمول تقسیم در اکسل نسبت به فرمول جمع و تفریق دارای اولویت است.

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

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

  • برای انتخاب داده ها در اکسل می توان از shift استفاده کرد.
  • برای کپی یک فرمول می توان دو بار بر روی گوشه سمت راست سلول کلیک کرد.
  • با استفاده از کلید F4 می توان یک سلول را قفل کرد.
  • با استفاده از علامت & می توان داده های متنی را با هم ترکیب کرد.
  • با استفاده از تابع RAND() می توان اعداد تصادفی بین صفر و یک ایجاد کرد و با فشردن کلید F9 می توان این عدد را تغییر داد.
  • با استفاده از توابع LEFT ، RIGHT و LEN می توان داده های مورد نظر را پاک کرد.
  • با استفاده از داده CountIF می توان تعداد دفعات تکرار هر داده را مشخص کرد.
  • با استفاده از کلید Ctrl + می توان بین سلول ها جا به جا شد.
  • با زدن کلید Alt و = پس از سطر یا ستون می توان جمع داده ها را مشاهده کرد.
  • برای جا به جایی شیت ها می توان از کلید CTRL+PGUP و یا CTRL+PGDN استفاده کرد.
  • برای تنظیم فرمت اعداد می توان از CTRL+SHIFT به همراه اعداد یک تا پنج استفاده کرد.
  • هنگام کار با چند فایل به جای جداگانه باز کردن فایل ها می توان تمام فایل ها را انتخاب کرد و با زدن Enter تمام آن ها را به صورت همزمان باز کرد.
  • با دوبار کلیک می توان اسم صفحات را تغییر داد.
  • با استفاده از AutoCorrect می توان ورود اطلاعات را سرعت بخشید.
  • علاوه بر استفاده از Ctrl+A ، با استفاده از دکمه ای در گوشه صفحه می توان تمام داده ها را انتخاب کرد.
  • یکی دیگر از نکات مهم تبدیل تاریخ شمسی به میلادی در اکسل است. با توجه به اینکه اکسل فقط از تاریخ میلادی پشتیبانی می کند ، برای تبدیل تاریخ می توان از توابع مختلف که به این منظور تهیه شده است استفاده کرد.
  • برای تغییر فونت اعداد در اکسل پس از انتخاب تمام داده ها می توان فونت آن ها را همزمان تغییر داد.

ترفندهای مفید در اکسل |ویراتیم

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

 

برای رسم نمودار در اکسل چه کنیم؟

برای رسم نمودار در اکسل چه کنیم؟

یکی از بهترین راه‏ های ارائه اطلاعات و گزارش ‏ها، نمودارها هستند. نمودارها بدلیل ویژگی‏ هایی که دارند، اختلاف‏ها، نقاط حساس و … بطور کلی تحلیل وضعیت رو تسهیل می ‏کنند. با توجه به تنوع و امکانات گرافیکی جذابی که وجود داره، رسم نمودار در اکسل از اهمیت ویژه ‏ای برخوردار هست. تسلط به نمودارها، یکی از موارد بسیار مهم در تهیه داشبوردهای مدیریتی است. اینکه هر نمودار چه کاربردی داره، چطور رسم میشه و برای نمایش چه موضوعاتی استفاده میشه خیلی مهمه. در این آموزش نکات مهمی که باید در رسم نمودار رعایت کنیم رو توضیح میدم:

نکته اول: انتخاب نمودار متناسب با جنس داده ها

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

نکته دوم: شناخت اجزای نمودار

برای رسم نمودار در اکسل ، شناخت اجزای نمودار و تطبیق اونها با داده ها به ما در چیدن داده ها کمک میکنه. مثلا اینکه بدونیم از بین داده هامون، کدومش محور افقی رو باید نشون بده، کدومش محور عمودی، داخل نمودار داده ها چطور نمایش داده بشن، چیا با هم مقایسه بشن، هر جزء نمودار چه شکلی باشه (مثلا یک سری داده ستونی و یک سری دیگه خطی باشه) و … اگه بتونیم اینها رو بصورت مفهومی برای خودمون روشن کنیم، در چیدن داده ها در جای مناسب موفق خواهیم بود. نموداری داریم که دمای هوا را در روزهای یک هفته نمایش میده. علاوه بر این میخوایم میانگین کل این هفته رو هم نمایش بده که بتونیم تشخیص بدیم. خب میدونم که توی نمودارم بادی روزهای هفته رو به عنوان محور افقی و دمای هوا رو در محور عمودی. از طرفی میخوام دمای هوا رو در هر روز بصورت نقطه ای نشون بدم و بهتره که (بنا به تشخیص خودم) نمودار میانگین دما رو بصورت خطی نمایش بدم تا بتونم مقایسه کنم که چه روزهایی بالای میانگین بوده، چه روزهایی زیر میانگین؟

شکل ۱- رسم نمودار در اکسل – نحوه چیدن داده ها با توجه به نیاز و خواسته کاربر

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

نکته:
موضوع خیلی مهمی که باید بهش توجه داشته باشیم، جهت صفحه اکسل (Right To Left/Left To Right) هست. یعنی اگه صفحه راست به چپ هست، حتما باید دقت کنیم که محور ایکس نمودار باید سمت راست داده ها قرار بگیره. اشتباهی که افراد مرتکب میشن اینه که مثلا صفحه راست به چپه ولی داده ها رو مطابق شکل ۲ چیدن و نمودار رو رسم کردن. که این باعث میشه نمودار اشتباه رسم بشه.

شکل ۲ – رسم نمودار در اکسل- نحوه چیدمان صحیح داده ها با توجه به جهت صفحه

نکته سوم: رسم نمودار

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

چهارم: انجام تنظیمات گرافیکی

بعد از اینکه نمودار رسم شد، حالا باید تنظیمات گرافیکی دلخواه رو روی نمودار اعمال کنیم. تنظیمات نمودار به دو دسته تقسیم میشه که با کلیک روی نمودار دو تب Design و Format فعال میشه که همه تنظیمات در دسترس هست. در تب Design ساختار نمودار، نوع نمودار، محورهای نمودار، ساختار یا همون Chart Layout و تنظیماتی از این دست رو می بینید. در تب Format هم همه تنظیمات مربوط به رنگ، سایه، فرمت های عددی و … مربوط به همه اجزای نمودار رو مشاهده میکنید. تنظیمات موجود در تب Format از نوار سمت راست اکسل نیز در دسترس هست که بهترین راه برای استفاده از آن، روش زیر هست. یعنی یک بار Format رو انتخاب کنیم و بعد روی هر قسمت نمودار کلیک کنیم. به این ترتیب در سمت راست تنظیمات فرمت مربوط به همون قسمت رو می بینید.

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

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

حذف داده های تکراری (Remove Duplicates)

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

به شکل ۱ توجه کنید. در ستون کد ملی، کدهای تکراری داریم که میخواهیم کل ردیف مربوط به کدهای تکراری حذف بشه و از هر کد واطلاعات مربوطه فقط یکی بمونه. برای این کار از ابزار Remove Duplicate استفاده می کنیم. از تب Data گزینه Remove Duplicate رو انتخاب میکنیم.

اگر ستون خاصی رو انتخاب کرده باشیم، و گزینه Remove Duplicate رو بزنیم پیامی مطابق شکل ۱ ظاهر می شه. که از ما می پرسه میگه میخوای این کار رو روی همین ستون انجم بدی، یا میخوای کل داده های مربوط به هر داده تکراری هم حذف بشه؟

چون حالت دوم (یعنی حذف کل ردیف مربوط به داده تکراری) مد نظر ماست پس گزینه Expand the Selection رو انتخاب میکنیم و گزینه Remove Duplicates رو میزنیم.

شکل ۱- حذف داده های تکراری – انتخاب محدوده مورد نظر برای حذف تکراری ها

بعد از زدن Remove Duplicates پنجره شکل ۲ باز میشه. در این پنجره ستونهایی رو انتخاب میکنیم که معیار ماست برای تعیین تکراری ها. در اینجا معیار ما برای تکراری بودن، کد ملی است. چون هیچ دو نفری نیستن که کد ملی مشترک داشته باشن. پس ستون کد ملی رو انتخاب میکنیم.

شکل ۲- حذف داده های تکراری – انتخاب ستون معیار برای تعیین داده های تکراری

بعد از زدن OK، ردیف های مربوط به کدهای ملی تکراری حذف میشه و از هر کد ملی یک ردیف باقی خواهد موند. پیامی مطاب شکل ۳ ظاهر میشه با این مضمون که چند داده تکراری حذف شده و چند داده یونیک، در نهایت باقی موندن.

شکل ۳- حذف داده های تکراری – حذف ردیف های مربوط به کد ملی تکراری

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

شکل ۴- حذف داده های تکراری – حذف داده های تکراری بر اساس نام و نا خانوادگی تکراری

در شکل ۴ مشاهده میکنید که علی راد و علی رادی، با اینکه کد ملی مشابه دارند، باقی موندن. چرا؟ چون معیار ما تکراری بودن نام و نام خانوادگی بوده که در اینجا، فامیل ها با هم تفاوت دارن.

نکته:
یکی از راه های تهیه لیست یونیک از داده ها همین Remove Duplicate است. مثلا ستونی از نام شهر ها رو داریم که میخوایم از هر کدوم یکی داشته باشیم. برای این کار کل ستون شهر رو ی جادیگه کپی (برای اینکه بانک اطلاعاتی تغییر نکنه) میکنیم. بعد، با استفاده از توضیحات بالا، داده های تکراری رو حذف میکنیم. اینطوری یک لیست منحصر بفرد از داده های تکراری موجود در یک بانک اطلاعاتی خواهیم داشت.

ایجاد پیوند یا Hyperlink

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

حتما تا به حال به فایل هایی برخورد کرده اید که تب شیت آنها در دسترس نیست و با کلیک بر روی متن ها و اشکال مختلف، می توانید بین شیت ها حرکت کنید.
این سوال برای خیلی ها بوجود می آید که چگونه این کار ممکن است؟ یکی از کارکردهای ابزار Hyperlink در اکسل ، ایجاد ارتباط بین اجزای مختلف در یک فایل و یا حتی در یک شیت است. فرض کنید فایلی مشابه شکل ۱ داریم. میخواهیم با کلیک بر روی هر فصل وارد شیت مربوط به آن فصل شویم. همچنین در هر شیت مربوط به هر فصل، یک دکمه بازگشت نیز داشته باشیم.

برای این کار، بعد از رسم چهار Shape برای هر فصل، روی Shape مربوط به بهار کلیک راست کرده و مطابق شکل۲ از منوی ظاهر شده Hyperlink را انتخاب میکنیم.

Hyperlink در اکسل - گام دوم

شکل۲

 

از جدول باز شده، مطابق شکل ۳ بر روی Place in this document کلیک میکنیم. به محض کلیک بر روی این مورد، سمت راست، همه شیتها و محدوده های نامگذاری شده مشاهده می شود. با توجه به اینکه shape مورد نظر مربوط به فصل بهار بود، روی نام بهار کلیک کرده و Ok را می زنیم.

Hyperlink در اکسل - گام سوم

شکل۳

برای اینکه زمانیکه موس را روی Shape نگه می داریم یک راهنمایی ظاهر شود، می توان ScreenTip را مطابق شکل ۴ تنظیم نمود.

Hyperlink در اکسل - گام چهارم

شکل۴

این کار را برای هر چهار shape مربوط به هر فصل انجام می دهیم. شکل ۵، چهار شیت مربوط به هر فصل را نشان می دهد که در هر شیت، مشخص است بر روی کدام Shape کلیک شده است. (توجه کنید که گرافیک Shapeها را در شیت های مربوطه طوری تنظیم کردیم که با انتخاب هر شیت، بقیه Shape ها که مربوط به شیت های دیگر است، غیرفعال به نظر برسند و فقط Shape مربوط به همان شیت فعال به نظر برسد)

شکل۵

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

شکل ۶

این shape ایجاد شده را در همه شیت ها کپی می کنیم. که دکمه بازگشت را در همه شیت ها داشته باشیم.

حالا کافیست شیت تب های نشان داده شده در شکل ۷ را از طریق تنظیمات Excel Option و مطابق شکل ۸ پنهان کنیم.

Hyperlink در اکسل - تنظیم شیت تب

شکل۸

نکته: توجه کنید که در صورت تغییر نام شیت، پیوندهای ایجاد شده کار نمی کند و نیاز به ویرایش خواهد داشت.

تبدیل متن به عدد

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

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

در این بخش، به معرفی چهار روش متفاوت برای تبدیل متن به عدد می­پردازیم.

  • تبدیل متن به عدد با استفاده از دستور Error Checking
  • تبدیل متن به عدد با استفاده از دستور text to columns
  • تبدیل متن به عدد با استفاده از دستور  paste special
  • تبدیل متن به عدد با استفاده از توابع اکسل

تبدیل متن به عدد با استفاده از دستور Error Checking

یکی از قابلیت­ های اکسل، امکان بررسی کردن خطا است که وجود محتوای نمایش متنی اعداد رو به شما تذکر می­ده. این قابلیت، طبق شکل ۱ به صورت یک مثلث کوچک رنگی (نشانگر خطا) در گوشه بالا سمت چپ سلول شما دیده خواهد شد.

متن به عدد - نشانگر خطا در سلول

شکل ۱- نشانگر خطا در سلول

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

برای اینکار مراحل زیر را انجام دهید:

  1. انتخاب سلول (یا سلول­ها) شامل مقادیری که می­ خواید به عدد تبدیل کنید.

با انجام این مرحله، یک نماد هشدار در بالای سلول(ها) ظاهر می­شود. اگر ماوس را بر روی نماد هشدار نگه دارید، پیام هشدار به نمایش در می­ آید (شکل ۲).

متن به عدد - نمایش پیغام خطا

شکل ۲- نمایش پیغام خطا

  1. مطابق شکل ۳ بر روی نماد هشدار کلیک کنید تا منوی بررسی خطا ظاهر بشه.

متن به عدد - مشاهده منو بررسی خطا

شکل ۳- مشاهده منو بررسی خطا

  1. گزینه Convert to Number را برای تبدیل مقادیر به اعداد انتخاب کنید.

 

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

File/ Excel Options/ Formulas/

مطمئن شوید که:

  1. در بخش Error Checking ، گزینه Enable background error checking و
  2. در بخش Error checking rules ، گزینه Number formatted as text or preceded by an apostrophe تیک خورده باشند.

تبدیل متن به عدد با استفاده از دستور Text to Columns

یکی از کاربردهای ابزار Text to Columns تبدیل فرمت انواع داده­های اکسلی است. برای استفاده از این ابزار مراحل زیر رو انجام بدید:

  1. بازه سلول (ها)ی مورد نظر برای تبدیل رو انتخاب کنید (نباید بیشتر از یک ستون باشد).
  2. در قسمت Data از نوار ابزار اکسل، گزینه Text to Columns رو انتخاب کنید.

با انجام این کار پنجره Convert Text to Columns Wizard باز می­ شه. در این پنجره:

  • مطمئن بشید گزینه Delimited انتخاب شده باشه. سپس بر روی Next کلیک کنید.
  • مطمئن بشید که هیچ کدام از گزینه­ های بخش Delimiter انتخاب نشده باشند، سپس بر روی Next کلیک کنید.
  • حالا باید یک فرمت برای ستون داده ­های خود انتخاب کنید. پس گزینه General را انتخاب و بر روی دکمه Finish کلیک کنید.
  • متن به عدد - تبدیل متن به عدد با استفاده از Text to Columns

    شکل۴- تبدیل متن به عدد با استفاده از Text to Columns

    تبدیل متن به عدد با استفاده از دستور Paste Special

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

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

    1. در یکی از سلول­ های صفحه گسترده اکسل عدد صفر را وارد کنید.
    2. سلولی را که شامل صفر است انتخاب کرده و این سلول را کپی کنید.
    3. سلول (ها) ی مقصد که جنس عدد نیستند و میخواید تبدیل به عدد بشن رو انتخاب کنید.

    متن به عدد - مسیر Paste Special

    شکل ۵- مسیر Paste Special

    1. مطابق شکل ۵ از قسمت Home در نوار ابزار اکسل، گزینه Paste → Paste Special رو انتخاب کنید.

    پنجره Paste Special همانند شکل ۶ ظاهر خواهد شد.

    متن به عدد - پنجره Paste Special

    شکل ۶- پنجره Paste Special

    1. در پنجره Paste Special گزینه Add را در قسمت Operation انتخاب و سپس بر روی OK کلیک کنید.
    نکته:
    کلید میانبر کپی Ctrl+C است.
    کلید میانبر  Paste Specialنیز ، Ctrl+Alt+V است.

     

    تبدیل متن به عدد با استفاده از تابع عددی اکسل

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

    مثال­هایی از تابع Value

    مثال ۱

    ستون A از صفحه گسترده زیر شامل نمایش متنی اعداد است. برای تبدیل مقادیر متنی به عدد، از تابع Value اکسل، در ستون B صفحه گسترده استفاده می ­شه.

  • متن به عدد - تابع Value

    مثال ۲

    در مثال زیر، از تابع Value اکسل به همراه تابع Left اکسل برای استخراج مقادیر عددی از رشته­ های متنی در سلول A1 استفاده شده است.

    متن به عدد - نتیجه کار با تابع Value

    توجه داشته باشید، اگر متنی که قرار است به عنوان ورودی تابع عددی اکسل قرار بگیرد، قابلیت تبدیل به مقادیر عددی را نداشته باشد، تابع خطای #VALUE! Error را نمایش می ­دهد.

     

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

انجام محاسبات بر روی زمان

 

محاسبات مربوط به زمان (جمع ساعت در اکسل)

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

در این آموزش هر نکته ای راجع به زمان وجود داره رو با هم مرور خواهیم کرد.

مطابق شکل 1 در دو سلول، زمان یکسان رو وارد کنید. فرمت یک سلول رو به Number تغییر بدید. این عدد به چه معنی است؟

 

شکل 1- مفهوم مقدار زمان

همونطور که در شکل 1 میبینید، 12:53 بعد از ظهر، معادل 0.54 روز هست. بعبارتی 24 ساعت کامل در اکسل معادل عدد 1 هست. در واقع محاسبات ساعت در اکسل، نسبت به روز سنجیده میشه. پس اگر میخوایم محاسباتی که داریم انجام میدیم، بر اساس ساعت باشه، مثلا میخوایم ساعت اضافه کاری رو در مبلغ هر ساعت ضرب کنیم. باید حواسمون باشه که اکسل اون جمع ساعت رو به روز تبدیل میکنه و بعد در مبلغ هر ساعت ضرب میکنه. به نکات زیر دقت کنید:

همونطور که در شکل 2 می بینید، حاصل جمع چند ساعت اضافه کاری بدرستی نمایش داده نمیشه. با یک حساب سرانگشتی می بینیم که مجموع ساعت ها بیش از 50 ساعت هست. برای اینکه نمایش جمع رو اصلاح کنیم، بر روی سلول B8 کلیک راست کرده و در Format Cell/ Custom کد زیر رو وارد میکنیم:

در واقع فرمت زمان هر چی که بود با ثانیه/بدون ثانیه یا مثلا AM/PM …. در هر صورت ساعت یا همون h رو داخل براکت [ ] قرار میدیم.

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

همونطور که در شکل 3 مشاهده می کنید در مرحله اول، حاصل ضرب مجموع ساعات در 10000 به فرمت ساعت هست، در حالیکه ما مبلغ را به فرمت عدد نیاز داریم. پس فرمت آن را به Number تغییر میدهیم. اما می بینیم که پس از تغییر فرمت به Number، میزان حاصلضرب درست نیست. علت همون مسئله ای سهت که در بالا به آن اشاره شد. اکسل مجموع ساعت را به روز تبدیل کرده (یعنی 61:05 رو تقسیم بر 24ساعت کرده) و حاصل را در 10000 ضرب میکند. پس اگر بخواهیم خود ساعت در 10000 ضرب بشه، باید حاصل را در 24 ضرب کنیم.

شکل 3- محاسبات زمان و ساعت

حالا که جنس زمان و منطق محاسبه اکسل رو فهمیدیم، به راحتی میتونیم هر نوع محاسباتی (جمع ساعت در اکسل) رو روی زمان های مختلف انجام بدیم، جمع، تفریق، ضرب و ….

در ادامه چند تابع کاربردی در خصوص زمان معرفی میکنم که در محاسبات مفید خواهد بود:

مثلا زمانی که میخوایم دو ساعت رو از هم کم کنیم، از یکی از حالت های نشان داده شده در شکل 4 استفاده میکنیم:

انجام محاسبات بر روی تاریخ

محاسبات مربوط به تاریخ در اکسل

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

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

اولین و مهم ترین چیزی که راجع به تاریخ میلادی باید بدونید اینه که تاریخ در واقع عدد هست. به شکل 1 نگاه کنید. اگر تاریخ ها رو در حالت General/Number قرار بدیم به عدد تبدیل میشن. اما مفهوم این عدد چیست؟

شکل 1- مفهوم مقدار تاریخ

همونطور که در شکل 1 می بینید، هر تاریخ برابر با یک عدد است. مبنای محاسبات تاریخ در اکسل، 1/1/1900 است و عدد 43001 یعنی این تعداد روز از تاریخ 1/1/1900 گذشته است. برای همین تاریخ 1/1/1900 معادل عدد 1 هست.

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

 

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

نکته:
اگر تاریخ بزرگتر رو از کوچکتر کم کنیم، مسلما عددی منفی خواهد بود. اما این عدد منفی در برخی سیستم ها بصورت ###### دیده میشه.  برای حل این مشکل و مشاهده اختلاف بصورت عدد منفی، کافیه از مسیر زیر، تیک use 1904 date system رو بزنیم.

Excel Options/Advance/ When calculating this workbook

 

خب با درک منطق و مفهوم تاریخ میلادی در اکسل، حالا میتونید محاسبات متنوعی رو انجام بدید. در ادامه چند تابع کاربردی در خصوص تاریخ معرفی میکنم که در محاسبات مفید خواهد بود:

یکی از توابع خیلی کاربردی مخصوصا برای دوستانی که با افراد خارج از کشور کار میکنند، تابع شماره هفته است. همونطور که میدونید، یکسال از 52 هفته تشکیل شده است. برای اینکه ببینیم هر تاریخ در چندمین هفته سال هست از تابع weeknum استفاده میکنیم. به مثال زیر توجه کنید:

یکی دیگه از توابع کاربردی تاریخ در اکسل  تابع Today هست. این تابع تاریخ جاری سیستم رو نشون میده. آرگومان هم نداره. یعنی با تایپ =today() در یک سلول، با باز کردن فایل در هر روز، تاریخ همان روز رو مشاهده میکنید.

مشابه همین تابع Now() هم وجود داره که زمان جاری سیستم رو برمیگردونه. بعبارتی تاریخ و ساعت رو. برای درک بهتر تفاوت این دو تابع به شکل 3 توجه کنید:

 

شکل 3- تفاوت تابع Now() و Today()

همونطور که در شکل 3 می بینید، معادل عددی خروجی تابع today()، 43001 هست ولی معادل عددی خروجی تابع NOW() 43001.66118 هست. در واقع عدد 0.66118 معادل ساعت 15:52 هست.

نکته:
کلید میانبر برای ثبت تاریخ سیستم در اکسل کلید Ctrl+: و ثبت ساعت سیستم در  اکسل Ctrl+Shift+: است.
توجه داشته باشید که این تاریخ ثابت هست و بروز نمیشه.

 

انجام محاسبات بر روی تاریخ شمسی

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

برای انجام محاسبات بر روی تاریخ شمسی در اکسل چندین روش ارائه میکنم:

روش اول: برنامه نویسی VBA یا Add_Ins توابع شمسی

یکی از روش های کار با تاریخ های شمسی استفاده از زبان برنامه نویسی VBA است. می توان با استفاده از کدهای VBA، توابعی مشابه توابع موجود در اکسل تعریف کرد که بتواند همه محاسبات مشابه برای تاریخ های میلادی را روی تاریخ های شمسی اعمال کند. اگر زمان یا دانش کدنویسی ویژوال بیسیک نداشته باشیم، باید از افزونه های (Add_Ins) آماده که در این خصوص نوشته شده است استفاده کرد. با نصب این افزونه ها، یک سری توابع به توابع اکسل اضافه می شن که عموما در دسته بندی User Defined قرار می گیرند. این افزونه ها به همراه خود یک فایل راهنما دارند که توابع موجود در این افزونه ها را معرفی می کنند و نحوه عملکرد آنها را توضیح می دهند. با جستجو در این اینترنت، می تونید این افزونه ها را تهیه کنید.

نکته:
جابجا کردن فایل های حاوی Add Ins باعث میشه که فایل عملکرد درستی نداشته باشه برای اینکه فایل در صورت انتقال هم با مشکلی در اجرا مواجه نشوند، مطابق مثال زیر (با استفاده از Drag & Drop) کدهای موجود در افزونه را به فایل اصلی انتقال دهید.

روش دوم: نوشتن تاریخ بصورت عدد (بدون /)

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

وجود / بین اعداد باعث میشه این اعداد به متن تبدیل شوند و خاصیت محاسبه و مقایسه را از دست بدهند. برای اینکه بتونیم براحتی محاسبه و مقایسه روی تاریخ های شمسی انجام بدیم،  تاریخ رو بدون /  و بصورت عدد ثبت میکنیم که این ویژگی (عدد بودن) حفظ شود. به عنوان مثال برای تاریخ ۱۳۹۷۱۲۰۴

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

پس روی سل کلیک راست کرده و Format Cell رو انتخاب می کنیم. روی Custom کلیک میکنیم و مطابق شکل ۲ کد (۰۰۰۰”/”۰۰”/”۰۰) رو تایپ میکنیم و Ok می زنیمتاریخ شمسی در اکسل - تنظیم نمایش عدد به صورت تاریخ

شکل ۱- تاریخ شمسی در اکسل – تنظیم نمایش عدد به صورت تاریخ

بعد از زدن Ok، سلول مورد نظر رو بصورت ۱۳۹۷/۱۲/۰۴ می بینیم.

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

تشریح روش محاسبه

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

تاریخ شمسی در اکسل - شیت تنظیمات

شکل ۲- تاریخ شمسی در اکسل – شیت تنظیمات

حالا با استفاده از تابع Countifs محاسبات مربوط به این روش رو شرح میدیم:

سوال: فرض کنید میخواهیم ببینیم فاصله بین دو تاریخ ۹۶/۰۲/۰۴ و ۹۶/۱۰/۰۱ چند روز است؟

پاسخ: برای این کار، کافیست ببینیم بین این دو عدد در شیت Setting، چند عدد وجود دارد. پس از تابع Countifs استفاده میکنیم:

=Countifs(A1:A365,”<=961001″,A1:A365,”>=960204″)

در این تابع اعدادی که بزرگتر مساوی از ۹۶۰۲۰۴ و کوچکتر مساوی از ۹۶۱۰۰۱ هستند، شمرده می شوند.

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

پاسخ: کافیست یک شرط دیگر، جمعه بودن رو به تابع بالا اضافه کنیم:

=COUNTIFS(A1:A365,”<=961001″,A1:A365,”>=960204″,B1:B365,””جمعه)

این تابع اعدادی که بزرگتر مساوی از ۹۶۰۲۰۴ و کوچکتر مساوی از ۹۶۱۰۰۱ هستند به شرط اینکه سلول مجاور آنها در ستون B معادل کلمه جمعه باشد را شمارش می کند.

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

حسن استفاده از این روش، انعطاف پذیری بسیار بالاست، چرا که اگر تقویم خاص با ویژگی های منحصر بفرد (مثلا روزهای خاص محل کار، روزهای تولد و …) داشته باشید، با این روش می تونید محاسبات دلخواه رو اعمال کنید.

روش سوم: استفاده از توابع Date & Time و تبدیل جواب نهایی به تاریخ شمسی در اکسل

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

برای تبدیل تاریخ میلادی به شمسی (بدون VBA) باید از ترکیب توابع مختلفی از جمله توابع متنی، منطقی، ریاضی و … استفاده کرد. برای مشاهده نمونه ای از ترکیب این توابع در تبدیل تاریخ میلادی به شمسی، می تونید از فایلی (تبدیل تاریخ میلادی به شمسی و برعکس) که در انتهای آموزش قرار داده شده استفاده کنید.

این روش هم مانند سایر روش ها نقاط ضعف و قوتی داره. مثلا برای محاسبه روزهای تعطیل و کاری و … از این روش نمیشه استفاده کرد چرا که روزهای تعطیل تاریخ میلادی با شمسی متفاوت است. از این روش برای مقایسه تاریخ ها و محاسبه فاصله بین دو تاریخ و … میشه استفاده کرد.

 

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

 

روش چهارم: استفاده از فرمت نمایش تاریخ شمسی در اکسل ۲۰۱۶

این گزینه که در نسخه اکسل ۲۰۱۶ اضافه شده به شما امکان تغییر ظاهر تاریخ های میلادی به شمسی را داره. توجه کنید ظاهر آن را تغییر میده یعنی در اصل تاریخ میلادی هست و توابع و محاسبات انجام شده روی این تاریخ بر اساس تاریخ میلادی انجام میشود.

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

 

نامگذاری محدوده در اکسل

نامگذاری محدوده در اکسل

نامگذاری محدوده در اکسل

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

یک محدوده نام گذاری شده در اکسل با اختصاص دادن یک نام انتخاب شده به محدوده ای مشخص از سلول ها بوجود میاد. بعد از اینکه برای محدوده سلول ها اسم انتخاب شد، محدوده اسم دار می تونه بجای استفاده از آدرس دهی به سلول در توابع و فرمول ها استفاده بشه (برای استفاده از این نام ها نیازی به “” نیست. چرا که این اسم دیگه یک متن معمولی نیست و ما از قبل به اکسل معرفی کرده ایم).

نحوه نامگذاری محدوده ها

فرض کنید که می خوایم محدوده مربوط به میزان فروش رو نامگذاری کنیم. (محدوده B2:B8)

روش اول: نامگذاری از طریق Name Manager

  • محدوده ای که می خوایم نام گذاری کنیم رو انتخاب کنیم (سلول های B2:B8).
  • از مسیر زیر Define Name رو می زنیم.

Formula/ Defined Names

  • مطابق شکل 1 پنجره ‘New Name’ برای شما باز میشه و نام محدوده رو در قسمت Name وارد میکنیم.
  • شکل 1- نامگذاری محدوده در اکسل – نامگذاری محدوده ها از طریق Name manager

    Comment: توضیحات مربوط به این محدوده رو در این قسمت ثبت میکنیم.

    Scope: در قسمت Scope محدوده ای که این نام باید شناخته بشه رو مشخص میکنیم. مثلا اینکه این نام فقط مخصوص یک شیت (Sheet) هست یا اینکه کل فایل (Workbook).

    • در آخر که از همه تنظیمات مطمئن شدیم Ok را میزنیم. از این به بعد اکسل نام جدید میزان_فروش رو بوجود میاره که به محدوده B2:B8 اشاره داره.

    نکته:

    بصورت پیشفرض، اکسل نام سلول بالای محدوده انتخاب شده رو (در صورت پر بودن) به عنوان نام محدوده و محدوده انتخابی رو به عنوان محدوده نامگذاری شده در نظر میگیره، که هر دو مورد قابل ویرایش و تغییر هستند.

     

    اصول نامگذاری محدوده ها

    • نام ها نباید بیشتر از 255 کاراکتر باشند؛
    • نام ها باید با یک حرف، خط تیره یا بک اسلش شروع بشن؛
    • بقیه اسم باید از حروف، اعداد، خط تیره تشکیل بشن (کاراکتر های دیگه مجاز نیستن)؛
    • آدرس سلول مثلا B1 یا حروف تنها، “C”, “c”, “R” یا “r” نمی تونن به عنوان نام استفاده بشن؛
    • در صورتی که نام محدوده بیش از یک قسمت باشد باید با _ به هم متصل شوند زیرا استفاده از Space در نامگذاری محدوده ها غیرمجاز هست.
    • ترجیحا از حروف فارسی در نامگذاری محدوده ها استفاده نشه. مثلا در این مثال بجای میزان_فروش نام Sale انتخاب بشه.

     

    نکته:

    اصول نامگذاری حتما باید رعایت شوند. در غیر اینصورت با خطا مواجه خواهیم شد.

     

    روش 2: نامگذاری از طریق  ‘Create from Selection’

    اکسل دستور ‘Create from Selection’ رو نیز در اختیارمون گذاشته، که به ما اجازه میده تا خیلی سریع یک محدوده نام گذاری شده از سلول هایی که حاوی سرستون (Header) هستن بوجود بیاریم.

  • یک محدوده نام گذاری شده از مقادیر فروش مثال بالا رو می تونیم به صورت زیر بوجود بیاریم:
    • محدوده ای که می خوایم براش اسم بذاریم رو انتخاب می کنیم طوری که شامل هدر ستون هم باشه ( یعنی سلول های B1:B8 ).
    • گزینه Create from Selection رو از ریبون بالای صفحه، در تب Formulas انتخاب میکنیم.
    • مطابق شکل 2 پنجره ‘Create Names From Selection’ باز میشه
    • شکل 2- نامگذاری محدوده در اکسل – نامگذاری محدوده ها از Create from selection

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

      در این مثال، ردیف بالا شامل نام محدوده هست، بنابراین گزینه Top row رو انتخاب کرده OK می کنیم.

      چون اسم یک محدوده نامگذاری شده نمی تونه شامل فاصله باشه، اکسل یک _ رو بجای فاصله قرار می ده.

      روش 3: نامگذاری از طریق Name Box

      روش سوم و راحت ترین روش برای نامگذاری محدوده ها استفاده از Name Box هست.Name Box در بالای صفحه، سمت چپ نوار Formulas  قرار داره.

      • محدوده سلول هایی رو که میخواید نام گذاری کنید انتخاب کنید.
      • نام مورد نیاز رو در Name Box بنویسید و دکمه Enter روفشار بدید.

      نامگذاری محدوده در اکسل - نامگذاری از طریق Name Box

      شکل 3- نامگذاری محدوده در اکسل – نامگذاری از طریق Name Box

      اگه در حال حاضر برای نامی که نوشتید، محدوده ای وجود داشته باشه، محدوده موجود انتخاب خواهد شد. درغیر این صورت، یک محدوده نام گذاری شده جدید بوجود میاد که به انتخاب جاری شما اشاره خواهد کرد.

      ویرایش و یا حذف محدوده های نامگذاری شده

    • برای ویرایش نام، محدوده و یا حذف محدوده های از قبل نامگذاری شده از تب Formula گزینه Name Manager رو انتخاب میکنیم. در پنجره Name Manager لیست همه محدوده های نامگذاری شده قابل مشاهده هستند محدوده مورد نظر برای ویرایش و یا حذف را انتخاب کرده و گزینه Delete (حذف) و یا Edit (ویرایش) رو میزنیم.

      نکته:

      Table هم نوعی محدوده نامگذاری شده است که ویژگی های خاصی دارد و نحوه ایجاد، حذف و ویرایش آن با نامگذاری معمولی متفاوت است.

       

      اگر محدوده های نامگذاری شده زیاد باشن و بخوایم محدوده هایی با ویژگی های خاص رو پیدا کنیم میتونیم مطابق شکل 4 از Filter استفاده کنیم. از این ویژگی برای فیلتر محدوده های نامگذاری شده در شیت/فایل، محدوده های نامگذاری شده ای که با خطا مواجه هستن/نیستن و Tableها استفاده کرد.

    • شکل 4- نامگذاری محدوده در اکسل – فیلتر محدوده های نامگذاری شده

      در این آموزش مزایا، اصول و روشهای نامگذاری محدوده ها رو با هم مرور کردیم. در قسمت های بعدی، در مورد نحوه استفاده از این نام ها و همچنین فرمول نویسی در Name Manager صحبت خواهیم کرد.