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

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

تجزیه متن یک سلول به چند سلول در اکسل

برای تجزیه محتویات یک سلول دو روش وجود دارد:

• نوشتن فرمول
• ابزار Text To Columns

 قبل از بیان این قسمت، نیاز است تعدادی از توابع متنی توضیح داده شود.

•  تابع Find:

این فرمول درون متن مورد جستجو موقعیت آغاز یک کاراکتر (حرف، عدد و کلمه) را پیدا می کند. لازم به ذکر است که هر فاصله (space) نیز یک کارکتر محسوب می شود.

=FIND(find_text , within_text , start_num)

این فرمول 3 آرگومان (پارامتر یا شیء) دارد:

1-   find_text (آرگومان 1): متنی که بایستی جستجو شود این متن می تواند یک حرف یا یک کلمه یا متن باشد.

2-  within_text (آرگومان 2): متنی که در آن به جستجو خواهیم پرداخت. این متن نیز می‌تواند یک حرف یا یک کلمه یا متن طولانی باشد.

3-   Start_num (آرگومان 3): نقطه آغاز جستجو است و در آرگومان 2 مشخص می کنیم که از چندمین کاراکتر شروع به جستجو کند که دراین صورت کاراکترهای قبلی جستجو نخواهند شد. این آرگومان از یک شروع و تا تعداد کارکتر موجود در آرگومان 2 می‌تواند باشد.

درصورتیکه عدد صفر یا عددی بیش از تعداد کارکتر موجود در آرگومان 2 به آن اختصاص یابد خطای #VALUE! را نشان خواهد داد.

اگر متن مورد جستجو (آرگومان 1) در متن قابل جستجو (آرگومان 2) موجود نباشد خطای #VALUE! را خواهیم دید.

• تابع :Search

=Search(find_text , within_text , start_num)

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

• تابع Right:

=RIGHT(text,num_chars)

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

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

آرگومان Num_chars: تعداد کاراکترهایی است که می خواهید از انتهای رشته متن استخراج شود.

• تابع Left:

=LEFT(text,num_chars)

این تابع تعداد مشخصی از اولین کاراکترهای یک رشته متنی را استخراج می‌کند (متن انگلیسی از سمت چپ و متن فارسی از سمت راست).

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

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

• تابع Mid:

=MID(text,start_num,num_chars)

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

• تابع Len:

=LEN(text)

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

تجزیه متن یک سلول از طریق نوشتن فرمول در excel

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

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

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

در تصویر زیر نیز نحوه جدا کردن نام و نام خانوادگی نشان داده شده است. همانطور که در این تصویر دیده می شود چند تابع درون تابعی دیگر نوشته شده است.
برای جدا کردن نام، از تابع LEFT استفاده می‌شود به طوریکه برای ورودی دوم این تابع (تعداد کاراکترهای مورد نظر که قرار است استخراج شوند یعنی تعداد کاراکترهای نام) تابع FIND نوشته می‌شود. این تابع موقعیت کاراکتر فاصله را درون رشته متن می یابد و سپس یک واحد از آن کم می گردد تا موقعیت آخرین حرف نام مشخص شود.
برای جدا کردن نام خانوادگی، تابع MID بکار گرفته می شود. برای ورودی دوم این تابع (موقعیت اولین کاراکتر نام خانوادگی) از تابع FIND استفاده می‌گردد. این تابع موقعیت کاراکتر فاصله را درون رشته متن می یابد و سپس یک واحد به آن اضافه می‌گردد تا موقعیت اولین حرف نام خانوادگی تعیین شود. برای ورودی سوم تابع MID می‌بایست به طریقی مشخص شود که از اولین حرف نام خانوادگی به بعد چه تعداد کاراکتر باید استخراج شود. این کار به راحتی با استفاده از تابع LEN امکانپذیر است.

تجزیه متن یک سلول از طریق ابزار Text to columns در اکسل

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

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

1. سلول‌هایی که قرار است تجزیه شوند را انتخاب نمایید.
2. از منوی Data، بر روی گزینه Text to columns کلیک کنید.
3. در مرحله 1 از 3، گزینه Fixed Width را انتخاب نمایید.
4. در مرحله 2 از 3، بوسیله کلیک کردن در نقاطی که می خواهید کاراکترها از یکدیگر جدا شوند، ستونها را تجزیه کنید.
5. در مرحله 3 از 3 در فیلد Destination آدرس سلولی که می خواهید داده ها درج شوند را وارد کنید و بر روی Finish کلیک نمایید

 برای حالت دوم، به منظور جدا کردن نام و نام خانوداگی به طریق زیر عمل کنید:

1. سلولهایی که قرار است تجزیه شوند را انتخاب نمایید.
2. از منوی Data، بر روی گزینه Text to columns کلیک کنید.
3. در مرحله 1 از 3، گزینه Delimited را انتخاب نمایید.
4. در مرحله 2 از 3، گزینه Space را انتخاب کنید.
5. در مرحله 3 از 3 در فیلد Destination آدرس سلولی که می خواهید داده ها درج شوند را وارد کنید و بر روی Finish کلیک نمایید

ترکیب چند سلول در یک سلول در اکسل

برای این کار می توان به دو روش عمل نمود:

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

روش دوم) از تابع CONCATENATE استفاده می‌شود. ورودی های این تابع داده‌هایی است که باید با یکدیگر ترکیب شوند. برای مثال زیر این تابع سه ورودی دارد؛ ورودی اول نام، ورودی دوم یک فاصله و ورودی سوم نام خانوادگی می باشد.

کاربرد ترانهاده در اکسل

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

روش‌های ترانهاده در اکسل

در اکسل عوض کردن جای سطر و ستون و بالعکس (ترانهاده در اکسل ) با سه روش مختلف قابل انجام است. استفاده از گزینه موجود در منوی الحاق (Paste)، سریع ترین روش ممکن برای این کار است. تابع Transpose هم روشی دومی است که اغلب برای این منظور استفاده می‌شود و از فرمول‌های آماده آرایه‌ای است. استفاده از فرمول نویسی آرایه‌ای نیز با اندکی پیچدگی روش سومی است که به ندرت استفاده می‌شود.

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

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

ترانهاده در اکسل به روش اول

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

1- انتخاب داده‌های مورد نظر از سلول‌ها، به طور مثال سلول‌های A1 تا C1 که حاوی داده‌های مورد نظر هستند را انتخاب کنید.

2- برای کپی کردن دکمه Ctrl + C را فشار دهید. یا راست کلیک کرده و گزینه کپی را از بین گزینه‌های موجود انتخاب کنید.

3- سلول E2 را که سلول ابتدایی ستون مقصد است انتخاب کنید.

4- راست کلیک کرده و دکمه Paste را کلیک کنید و در آن گزینه Paste Special را انتخاب کنید.

5- Paste Special باز می‌شود و در پایین پنجره Transpose را تیک بزنید.

با کلیک دکمه OK، سطر و ستون جایگزین می‌شود.

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

همواره توجه داشته باشید، در تمامی عملیات که از گزینه های موجود در بخش Paste استفاده کرده و کارهای خاصی انجام می‌دهیم استفاده از کلید میانبر Ctrl+v کاربردی نداشته و عملیات مورد نظر را انجام نخواهد داد. کلید Ctrl+v، میانبر اولین گزینه از گزینه‌های موجود در بخش Paste است، که تقریباً بدون هیچ ویژگیِ خاصی فقط عمل الحاق کردن را انجام می‌دهد.

‌

ترانهاده در اکسل به روش دوم – تابع ترانهاده Transpose Function

برای وارد کردن تابع ترانهاده مراحل زیر انجام می‌شود.

1- محدوده‌ای از سلول‌هایی که قصد ترانهاده کردن داده‌ها به آن را دارید را انتخاب کنید.

2- تابع ()=TRANSPOSE را در Formula bar تایپ کنید. و برای آرگومان خواسته شده تابع، سلول‌هایی که داده‌های اولیه در آن قرار دارد را انتخاب نمایید

4-  دکمه CTRL + SHIFT + ENTER را فشار دهید تا تابع به صورت آرایه‌ای بر سلول‌های مقصد اعمال شود.

فرمت اعداد در اکسل

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

بدین منظور بعد از انتخاب سلول یا محدوده اعداد، می توان از تب Home تنظیمات مربوط به نحوه نمایش اعداد را انجام داد ولی بهتر است بعد از انتخاب محدوده بر روی یکی از سلولها راست کلیک کرده و گزینه Format Cell را انتخاب نموده (یا از صفحه کلید دکمه Ctrl+1 را زده) و سپس از تب Number فرمت مد نظر را انتخاب نموده. در این قسمت حالت های ذیل وجود دارد:

عمومی General

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

عدد Number

برخلاف حالت General در این حالت می توان تنظیمات مربوط به تعداد رقم اعشار، جدا کننده سه رقم سه رقم اعداد و نحوه نمایش اعدا منفی را نیز انجام داد بدین صورت که در صورت تیک زدن چک باکس Use 1000 List Separator، در صورت بزرگتر از 1000 بودن عدد، ارقام آن سه رقم سه رقم جدا می شود (برای مثال 1,000).

به همین صورت عدد ثبت شده در قسمت Decimal Place بیانگر تعداد اعداد اعشاری عدد خروجی می باشد، برای مثال اگر در این قسمت عدد 2 وارد شود، در صورتی که کاربر در آن سلول مقدار 124 را وارد نماید خروجی به صورت 124٫00 نمایش داده می شود).

در نهایت در قسمت پایین تنظیمات در باکس Navigate Number چهار حالت پیشنهادی برای نحوه نمایش اعداد منفی وجود دارد برای مثال می توان اعداد منفی را با یک علامت منفی نمایش داد یا بدون علامت منفی و به رنگ قرمز نمایش داد….

مقادیر پولی Currency

جهت تنظیم فرمت اعداد در حالت Currency علاوه بر تنظیمات مربوط به قسمت Number می توان نماد پولی مد نظر را نیز از قسمت Symbol انتخاب نمود بدین صورت که با انتخاب کردن نماد ریال از این قسمت، در صورتی که کاربر عدد 125 را درون سلول ثبت نماید، مقدار 125 ریال درون سلول نمایش داده می شود.

حسابداری Accunting

حالت Accunting بسیار مشابه با حالت Currency می‌باشد با این تفاوت که در این قسمت تنظیمات مربوط به فرمت اعداد منفی و جدا کننده سه رقم سه رقم اعداد از استاندارد حسابداری استفاده می شود کاربر صرفا می تواند تنظیمات مربوط به تعداد رقم اعشار یا واحد پولی را انجام دهد.

تاریخ Date

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

زمان Time

این قسمت مربوط به تنظیمات نحوه نمایش زمان (ساعت) است بدین صورت که نحوه نمایش به صورت 24 ساعته باشد یا 12 ساعت و با علامتهای AM/PM یا این که به صورت تجمیعی نمایش داده شود. در این قسمت بعد از انتخاب گزینه Time کافی است از بین حالاتپیشنهادی در قسمت Type حالت مد نظر را انتخاب نماییم.

درصد Percentage

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

اعداد اعشاری Fraction

فرمت Fraction به منظور نمایش اعداد به صورت کسر مورد استفاده قرار می گیرد بدین صورت که اگر بخواهیم عدد ثبت شده درون یک سلول را به جای 0٫5 به صورت 1/2 نمایش دهیم کافی است بعد از انتخاب آن سلول، از قسمت تنظیمات اعداد حالت Fraction را انتخاب نماییم.

نماد علمی Scientific

Scientific یا نماد علمی به منظور نمایش اعداد بسیار بزرگ یا بسیار کوچک مناسب می باشد. در نماد علمی اعداد به صورت مضربی از توان n ام 10 نمایش داده می شود بدین صورت که تنها یک رقم قبل از اعشار نگه داشته می شود و سایر ارقام بعد از اعشار نمایش داده می شوند، و عدد حاصل در توان مثبت یا منفی از 10 ضرب می شود برای مثال عدد 1256 به صورت نماد علمی به شکل 1٫25x10³ نمایش داده می شود.

متن Text

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

حالات خاص  Specialدر excel

این حالت تنظیمات عمومی برای کدپستی یا Id-Number می‌باشد ولی فرمت تعریف شده مناسب برای کشور ایران نیست.

حالت سفارشی  Customeدر اکسل

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

 در اکسل برای متخصص شدن در اکسل، تشخیص دلیل بروز خطاها بسیار حائز اهمیت است.

1_ VALUE در اکسل:
در فرمول ریاضی وارد شده بجای مقادیر عددی از مقادیر متنی استفاده شده است.
  راه حل:
مقادیر ورودی وسلول های مورد استفاده در فرمول را کنترل می کنیم.

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

3_ NAME:
نام یا برچسبی در فرمول استفاده شده که Excel نمی تواند آن را تشخیص دهد مثلا ممکن است عبارتی را بدون قرار دادن در بین علائم درفرمول استفاده کرده باشیم یا نام سلول یا متغییری را که حذف شده است استفاده کرده باشیم یا حتی نام آنها را غلط تایپ کرده باشیم.
  راه حل:
نام متغییرها وبرچسب های استفاده در فرمول ونحوه استفاده آنها را کنترل می کنیم.

4_ تابع N/Aدر اکسل:
وقتی که یک مقدار در دسترس تابع یا فرمول نباشد اتفاق می افتد مثلا داده های مورد استفاده درفرمول غلط است یا هنوز درسلولهای وجود ندارند.
  راه حل:
مقادیر وسلولها ی مورد استفاده در فرمول را بررسی می کنیم.

5_ REF!:
وقتی که سلول ارجاع شده وجود نداشته باشد اتفاق می افتد
  راه حل:
سلولهای مورد اشاره درفرمول را بررسی می کنیم تاحذف نشده باشند ووجود داشته باشند.

6_ NUM!
وقتی که یک پارامتر نادرست در توابع وفرمول استفاده کنیم اتفاق می افتد.
  راه حل:
مقادیر وپارامترهای مورد استفاده در فرمول وتوابع رابررسی می کنیم.

7_ NULL! در اکسل
وقتی که در یک فرمول از آدرس اشتراک دو آدرس استفاده کرده باشیم که این اشتراک وجود نداشته باشد اتفاق می افتد.
  راه حل:
پارامترها ونحوه استفاده صحیح آنها را در فرمول بررسی می کنیم.

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

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

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

2- موس را روی لبه محدوده انتخابی برده تا به شکل چهار جهت شده سپس با نگهداشتن کلیک راست محدوده مورد نظر را به سمت راست کشیده بدون رها کردن کلیک موس به جای خود بازگردانید.

3- از منوی ظاهر شده گزینه Copy here as value only را انتخاب نمایید.

4- به همین سادگی.

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

1. کپی کرده محدوده حاوی فرمول (CTRL+C)
2. کلید  ALT+ESV را زده سپس اینتر را بزنید                                                                       

    روش ساده تر با صفحه کلید برای انجام کار فوق…

   1. کپی کردن محدوده فرمول (CTRL+C)
   2. زدن کلید نمایش منوی راست کلیک روی صفحه کلید و سپس زدن کلید V مانند شکل زیر
   3. تمام
   
   

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

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

نکته بسیار مهم در طراحی گزارشات حرفه ای این است که همواره سعی کنید ورودی داده هایتان را بصورت Table ایجاد نمایید. از مزیتهای ایجاد جداول در اکسل و نحوه کار با آنها است و در همین راستا ابتدای امر داده هایمان را به جدوا تبدیل می کنیم. برای اینکار می توانید از کلید میانبر Ctrl+T استفاده کنید.

سپس به تب کمکی DESIGN که بواسطه ایجاد جدول در  اکسل ایجاد شده است میرویم و روی گزینه Summarize With PivotTable کلیک می نماییم.

از نکات زیر برای تهیه گزارشات تان استفاده نمایید:

نکته اول: می توانید اطلاعات روزانه تان را به هفته، ماه، فصل و سال گروه بندی کنید، بدون اینکه در داده های اصلی تغییری ایجاد نمایید.

برای اینکار فیلد ماه را در Rows قرار دهید و فروش را در Values می توانید سایر فیلدها را در Filters و یا Columns قرار دهید.

حالا روی یکی از سلولهای مرتبط با ستون Row Lables که تاریخهای روزانه در آن قرار دارد کلیک راست نمایید و گزینه Group را انتخاب کرده و هر یک از گروه های مورد نظرتان(ماه، فصل، سال) را انتخاب نمایید. می توانید همه موارد مذکور را هم انتخاب کنید.

اگر میخواهید گزارشات هفتگی بسازید باید گزینه Days را انتخاب کنید و قسمت Number of days را عدد هفت تعیین کنید. در نتیجه براحتی گزارشی به تفکیک سریهای زمانی مختلف ایجاد نموده اید.

نکته دوم:  نقش Filter در PivotTable در اکسل

تا بحال به نقش فیلتر در PivotTable دقت کرده اید؟ آیا تا بحال فکر می کردید که فقط می توانید از طریق فیلد فیلتر گزارشات Customize شده بسازید؟ آیا می دانستید که می توانید بدون اینکه نیاز به ساخت PivotTableهای جدید باشد گزارشات مورد نظرتان را از یک PivotTable بسازید. منظور چیست؟ فرض کنید می خواهید همین گزارش فروش سالهای مختلف را به تفکیک برای هر محصول، هر شهر و هر ویزیتور داشته باشید. آیا از اول PivotTable می سازید؟ اینکار را نکنید اجازه دهید راهی فوق العاده را به شما نشان بدهم:

هر فیلدی را که میخواهید از آن Pivottable مشابه بسازید در فیلد فیلتر قرار دهید. در اینجا ما هم محصولات، هم شهرها و هم ویزیتورها را در فیلتر قرار داده ایم. سپس در تب Analyze روی قسمت Options کلیک کرده و دومین گزینه یعنی Show Report Filter Pages را انتخاب نمایید.

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

فرض کنید ما گزینه ویزیتور را انتخاب کنیم آنگاه  به ازای نام هر ویزیتور یک صفحه جدید PivotTable ایجاد خواهد شد. تمام داده های Pivot Table نیز بر اساس نام ویزیتورها فیلتر می شود.

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

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

فرض کنید می خواهید حاشیه سود را طی سه سال و در فصول مختلف محاسبه کنید برای اینکار باید میزان سود در هر بازه زمانی را بر فروش همان دوره تقسیم کنید برای اینکار به تب Analyze بروید روی گزینه Fields, Items & Sets کلیک نمایید. اولین گزینه Calculated Fields را انتخاب نمایید.

در پنجره ای که باز می شود باید دو قسمت را تکمیل نمایید. در قسمت Name برای شاخصی که میخواهید محاسبات آنرا انجام دهید یک نام بگذارید در اینجا ما “حاشیه سود” نوشتیم و در قسمت Formula باید با استفاده از فیلدهای موجود در لیست پایین آن محاسبات را انجام دهید که ما در اینجا سود/زیان را بر فروش تقسیم کردیم. برای تقسیم از علامن / روی کیبرد استفاده کنید. بعد از اینکه ok کردید فیلد جدید خودبخود به PivotTable اضافه می شود.

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

نکته چهارم: ساختار نمودارهای داینامیک در اکسل

آیا تا بحال با خودتان فکر کرده اید که چه می شد اگر می توانستید نمودارهایی بسازید که با اعمال فیلتر روی دیتاها نمودارتان نیز تغییر می کرد؟ بله درست است PivotTable این امکان را به شما می دهد. برای اینکار کافیست در تب Insert روی PivotChart کلیک نموده و نمودار مورد نظرتان را ایجاد نمایید. سپس در همان تب روی گزینه Slicer کلیک کرده و اسلایسرهای مورد نیازتان را بسازید.

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

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

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

ابتدا بر روی گزینه File در محیط اکسل  کلیک و سپس بر روی Options کلیک نمایید.

در پنجره Options از سمت چپ بر روی بخش Advanced کلیک کنید.

دکمه Edit Custom Lists در قسمت انتهایی بخش Advanced  است آن را پیدا کرده و کلیک کنید.

برای ایجاد لیست سفارشی جدید، آیتم‌های مورد نظر خود  را در قسمت  list entries وارد کرده و بر دکمه Add کلیک کنید. در اینجا شماره آرماتورها را که از موارد و لیست های پرکاربرد هستند را به لیست خود اضافه می‌کنیم

همچنین اگر در بخشی از سلول ها لیستی را وارد کرده‌اید که قرار است در بخش‌های دیگر به صورت تکراری آن را تایپ کنید، می‌توانید با استفاده از گزینه Import در بخش Custom Lists، سلول‌های حاوی لیست را انتخاب کرده و به لیست سفارشی اضافه نمایید.

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

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

یکی از روش‌های بسیار مفید برای این دسته از فایلها ساخت فهرستی از تمامی worksheet‌ها است که با کلیک بر روی هر نام یک worksheet، آن worksheet فعال شود. اینکار با ابزار Hyperlink در اکسل به سادگی قابل انجام است (کلید میانبر ساخت و ویرایش Hyperlink در اکسل Ctlr+k است.)

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

این برنامه علاوه بر ساخت فهرست Sheetها و لینک کردن آنها به Sheet مربوطه، به صورت جداگانه در هر Sheet فایل شما یک دکمه در خانه A1 قرار می‌دهد که با کلیک بر روی آن به "فهرست" باز می‌گردید. در شکل زیر دکمه قرمز رنگ "Home" اینکار را انجام می دهد. در ضمن این دکمه در Print چاپ نخواهد شد در عکس زیر دکمه قرمز رنگ با کلمه Home را می توانید مشاهده کنید.

تمامی این برنامه به زبان ویژوال بیسیک اکسل نوشته شده است و در صورت نیاز می توانید آنرا تغییر و یا توسعه دهید. برای وارد شدن به محیط ویژوال بیسیک اکسل کلید Alt+F11 را بزنید

1- یک پنجره به کاربر نمایش داده می شود که در آن می تواند دکمه‌های Home (بازگشت به فهرست) را ایجاد / حذف نماید.

2- کاربر می تواند هر دکمه / عکس / آیکون دلخواه را به عنوان دکمه خانه (بازگشت به فهرست) را همه شیت های فایل اضافه کند.

نحوه اجرای ابزار ساخت فهرست شیت‌های اکسل

1- ابتدا فایل این ابزار را که از قسمت فایلهای ضمیمه دانلود کرده اید، را باز کنید.

2- مطمئن شوید که ماکروی آن را فعال کرده اید، اگ

3- فایل اکسل مورد نظر خود را که می خواهید برای آن "فهرستی" بسازید را باز کنید.

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

3- به فایل اکسل مورد نظر خودتان بروید.

4- کلید ALT+F8 را بزنید تا در پنجره ای که باز خواهد شد، لیست همه ماکروها را مشاهده نمایید.

5- گزینه "Build_Sheet_Navigator" را انتخاب کنید و کلید Run را بزنید تا ماکروی "ساخت فهرست شیت ها" اجرا شود.

6- حال در پنجره باز شده تنظیمات مورد نظر خود را با زدن هر دکمه اعمال کنید.

نحوه استفاده 

1- با زدن دکمه "ایجاد فهرست" برای شما یک شیت ایجاد می شود و در آن لینک تمامی شیت‌ها را خواهید داشت.

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

2- درج دکمه‌های خانه

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

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

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

3- ایجاد دکمه‌های خانه دلخواه

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

الف) یک عکس / آیکون / شکل دلخواه خود را در یک شیت داشته باشید.

ب) آن عکس / آیکون / شکل را به شیت فهرست خود لینک کنید. 

ج) آن عکس / آیکون/ شکل را انتخاب نمایید. 

د) برنامه را اجرا کنید و سپس گزینه "کپی دکمه من" را کلیک کنید تا این دکمه در همه شیت ها به راحتی کپی گردد.

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