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

[S.Cheraghi]

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

 

[S.Cheraghi]

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

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

و …

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

خب حالا بپردازیم به انواع قفل گذاری بر روی فایل اکسل. محافظت از فایل اکسل در ۲ سطح صورت میگیره:

سطح اول: قفل کردن کل فایل
برای این کار از مسیر زیر که در تصویر شماره ۱ هم نشان داده شده، رمزی برای فایل در نظر میگیریم.

File/ Save As/ Tools/ General Options

شکل ۱- قفل کردن فایل اکسل – پسورد گذاشتن روی کل فایل

بعد از زدن General Options پنجره شکل ۲ باز میشه:

شکل ۲- امنیت فایل های اکسل – پنجره تعیین پسورد برای فایل

در این پنجره دو نوع پسورد میتونیم تعیین کنیم. اولی برای باز کردن فایل، دومی برای تغییر فایل. یعنی کسی که Password to open رو نداشته باشه، اصلا محتویات داخل شیت رو مشاهده نخواهد کرد. کسی که Password to Modify رو نداشته باشه، امکان تغییر روی اصل فایلرو نخواهد داشت. یعنی فایل رو تغییر میده، اما به محض اقدام برای ذخیره کردن، پنجره Save as باز میشه. به این ترتیب اصل فایل بدون تغییر میمونه. چون فرد، Password to Modify رو نداشته.

این آموزش داره طولانی میشه
بقیش رو در لینک زیر مطالعه کنید:
https://excelpedia.net/excel-security/

 

[S.Cheraghi]

حذف داده های تکراری

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

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

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

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

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

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

حتما بخوانید: تابع Countif اکسل | آشنایی و کاربرد آن

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

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

ادامه مطلب رو در لینک زیر بخوانید:
https://excelpedia.net/remove-duplicate/

 

[S.Cheraghi]

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

برای این کار از افزونه (که لینک آن در پایان این آموزش قرار داده شده) استفاده میکنیم. پس از نصب این افزونه، تابعی به نام ABH (عدد به حروف) به توابع اکسل اضافه می شود که یک آرگومان دارد و آن هم عدد مورد نظر که باید به حروف تبدیل شود. به تصویر زیر دقت کنید:

شکل ۱- تبدیل عدد به حروف در اکسل – تابع ABH و تبدیل عدد به حروف فارسی

نحوه نصب افزونه:

• فایل افزونه را دانلود کنید.
• طبق مسیر زیر افزونه را نصب کنید:

File/ Excel options/ Add-ins/ Go/ Browse/ انتخاب فایل افزونه/ Ok

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

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

همانطور که در بالا مشاهده میکنید، کافیه ماژول مربوطه در افزونه رو با Drag & Drop به فایل مقصد منتقل کنیم. با این روش، در صورت انتقال فایل نیز، عملکرد صحیح خواهد داشت.

نکته:
حتما فایل را در نهایت با فرمت Xlsm ذخیره کنید. در غیر اینصورت، کدهای VBA از بین خواهند رفت.

برای دانلود افزونه و فایل های این آموزش لینک زیر رو حتما ببینید:
https://excelpedia.net/number-to-text/

 

[S.Cheraghi]

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

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

فارسی کردن اعداد داخل سلول
این کار براحتی با اعمال فونت های فارسی بر روی اعداد امکان پذیر هست. کافیه محدوده اعداد رو انتخاب کنید و یکی از فونت هایی که با B شروع میشه مثل B mitra یا هر فونت فارسی دیگه ای رو انتخاب کنید.

شکل ۱- اعداد فارسی در اکسل ، داخل سل ها

فارسی کردن اعداد نمودار
برای فارسی کردن اعداد نمودار، ۲ تا راه حل داریم. یکی استفاده از فونت IranSans(FaNum) هست. البته شاید فونت های دیگه ای هم باشه. اما من اینو میدونم، امتحان کردم و بهتون پیشنهاد میدم.

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

[$-,۳۰۱]۰

برای استفاده از این تکنیک، کافیه قسمتی از نمودار که میخواد بصورت فارسی نمایش داده بشه (لیبل ها، محور افقی یا عمودی و …) رو انتخاب کنید و بعد از کلیک راست، فرمت رو بزنید و از قسمت Number، فرمت مورد نظر رو اعمال کنید:

شکل ۲- اعداد فارسی در اکسل ، نمودار

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

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

 

[S.Cheraghi]

ویژگی بسیار جالب تابع Index

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

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

با صفر قرار دادن هر یک از آرگومان های ردیف یا ستون، میتونیم یک محدوده رو در نظر بگیریم. یعنی چی؟

فرض کنید میخواهیم بیشترین فروش در شعبه ۲ مشخص بشه. به تابع زیر دقت کنید:

=Index(A1:F13,3,0)

این تابع، ردیف سوم از محدوده A1:F13 رو در نظر میگیره و چون ستون رو ۰ قرار دادیم، کل ستون های مربوط به اون ردیف رو در نظر میگیره. بعبارتی، این فرمول محدوده A3:F13 رو در حافظه خود نگه میداره. برای حل سوال خودمون که میخواستیم بیشترین فروش شعبه ۳ رو پیدا کنیم، طبق شکل ۱ این فرمول رو مینویسیم:

=Max(Index(A1:F13,3,0))

شکل ۱- کار با محدوده ها​

حتما این سوال براتون پیش میاد که چرا مستقیم نمی نویسیم Max(A3:F13) ؟

خب مثال زیر رو ببینید، متوجه جواب سوال میشید.

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

حتما بخوانید: تابع Countifs اکسل | آشنایی و کاربرد آن

قبلا ترکیب Index و Match رو با هم بررسی کردیم. در این مسئله، از طریق تابع Match تشخیص میدیم که هر شعبه چندمین ردیف از محدوده داده هاست. آرگومان ستون رو هم که صفر قرار دادیم. یعنی هر بار میاد میبینه شعبه مورد نظر ما چندمین ردیف هست، بعد کل ردیف مربوط به اون شعبه رو داخل Maxx قرار میده. به این ترتیب بیشترین فروش مربوط به هر شعبه فراخوانی میشه.

=Max(Index(A1:F13,Match(H3,A1:A13,0),0))

نحوه محاسبه این فرمول رو در زیر می بینید:

نکته:
این ویژگی برای ردیف هم صادقه. یعنی با صفر قرار دادن آرگومان ردیف، کل ستون مربوطه در نظر گرفته میشه. به فرمولMax(Index(A1:F13,0,0)) دقت کنید. این تابع بیشترین مقدار کل محدوده A1:F133 رو به ما میده. چون صفر گذاشتن آرگومان ردیف، یعنی همه ستون ها و صفر گذاشتن آرگومان ستون، یعنی همه ردیف ها. پس یعنی همه محدوده رو در نظر بگیر.


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

منبع آموزش:
گروه تخصصی اکسل پدیا

ویژگی بسیار جالب تابع Index

 

[S.Cheraghi]

افزودن راهنما به فرمول

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

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

تابع N در اکسل مقدار عددی یک عبارت رو بر میگردونه. یعنی اگر عبارت مورد نظر تاریخ میلادی باشه، معادل عددی اونو با مبدا ۱۹۰۰/۰۱/۰۱ بر میگردونه. اگه True باشه، ۱ بر میگردونه و متن های دیگه رو معادل ۰۰ در نظر میگیره. یکی از کاربردهای خاصی که برای این تابع میشه در نظر گرفت، افزودن کامنت به یک فرمول هست. به شکل ۱ دقت کنید:

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

همونطور که در شکل ۱ می بینید، در انتهای فرمول نوشته شده تابع N را با یک + به فرمول مورد نظر اضافه میکنیم و توضیحات مورد نظر رو داخل این تابع می نویسیم. چون خروجی این تابع عدد ۰ است، تاثیری بر محاسبات فرمول اصلی نخواهد داشت.


منبع آموزش:
گروه تخصصی اکسل پدیا

 

[S.Cheraghi]

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

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

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

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

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

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

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

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

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

حتما بخوانید: رسم نمودار خطی در اکسل | چرا نمودار خطی ؟

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

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

رسم نمودار در اکسل مثل یک کوه یخی است: بیش از چیزی که در ظاهر نشان می‏دهد در باطن دارد.


منبع آموزش:
گروه تخصصی اکسل پدیا | توابع اکسل | اکسل چیست

 

[S.Cheraghi]

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

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

تشریح آرگومان های تابع Index اکسل
Array: آرایه یا محدوده ای که در آن جستجو می کنیم. (یک Range یا محدوده نامگذاری شده است)

Row-Num: شماره ردیف داده مورد نظر که به دنبال آن هستیم. (از جنس عدد است)

[Column_Num]: شماره ستون داده مورد نظر در محدوده که به دنبال آن هستیم. (از جنس عدد است)

با تشریح مثال های مختلف، با این تابع آشنا میشیم:

مثال ۱

در مجموعه داده زیر میخوایم میزان فروش محصول۳ در شعبه ۶ را بیابیم.

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

=Index(A1:F13,7,4)

شکل ۱- تابع index اکسل – فراخوانی داده حاصل از تقاطع سطر و ستون

این تابع سلی که در تقاطع ردیف ۷ و ستون ۶ در محدوده A1:F13 قرار گرفته رو بر میگردونه.

مثال ۲

همین مسئله رو با ترکیب تابع Match حل میکنیم. یعنی، اینکه محصول ۳ چندمین ستون و شعبه ۶ چندمین ردیف از دیتابیس هست رو از تابع Match استخراج میکنیم.

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

با توجه به ماهیت تابع Match که مکان یک سل رو در یک محدوده به ما میده، میتونیم از آن در دو آرگومان Row-Num و Col-Num استفاده کنیم.

=Index(A1:F13,Match(E15,A1:A13,0),Match(D15,A1:F1,0))

شکل ۲- تابع index اکسل – ترکیب Index و Match

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

=Match(E15,A1:A13,0 )

و محصول۴، پنجمین ستون است که از تابع زیر نتیجه میشه:

=Match(D15,A1:F1,0 )

برای اینکه نحوه عملکرد این فرمول ترکیبی رو به خوبی متوجه بشید ویدئو زیر رو ببینید:

تابع Index عملکردها و کاربردهای بسیار متنوعی داره. مثلا اینکه آرگومان Col-Num اختیاری است چه کاربردی میتونه داشته باشه. تابع Index در فرمول نویسی آرایه ای، جستجوهای پیشرفته مثلا Vlookup عقبگرد یا پیدا کردن موارد تکراری و …. همه از حالت های متنوع کاربرد تابع Index هستند که در آموزش های بعدی به این موضوعات می پردازیم.


منبع آموزش:
گروه تخصصی اکسل پدیا | توابع اکسل | اکسل چیست
تابع Index، تابعی قدرتمند در حل مسائل

 

[S.Cheraghi]

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

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


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

شکل ۱ – اجزای نمودار – نمونه نمودار

Axes: محور افقی و عمودی نمودار

شکل ۲ – اجزای نمودار – محورهای افقی و عمودی نمودار (اولیه و ثانویه)

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

شکل ۳- نمایش محور عمودی ثانویه (برای نمایش سری عدد با مقیاس متفاوت)

Axis Title: عنوان محور عمودی و افقی نمودار

شکل ۴ – اجزای نمودار – عنوان محورهای عمودی و افقی (اولیه و ثانویه)

در شکل ۵ عنوان باکس محورهای عمودی و افقی (اولیه) و عمودی (ثانویه) رو می بینید:

شکل ۵ – اجزای نمودار – نمایش باکس عنوان محورهای عمودی و افقی

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

نکته:
ترفندی که در بالا ذکر شد (ارتباط Shapee به سل) یکی از ترفندهای پرکاربرد در تهیه نمودارهای پویا است.
Chart title: عنوان نمودار
از این قسمت مکان عنوان نمودار رو مشخص می کنیم. اگر هم گزینه اول (None) رو انتخاب کنیم عنوان نمودار حذف میشه. عنوان نمودار رو هم بهتره با استفاده از ترفند بالا، به سل وصل کنیم.
همونطو که در شکل ۶۶ مشاهده می کنید، با نگه داشتن روی اجزای مختلف نمودار، نام آن جز نمایش داده میشه.

شکل ۶ – اجزای نمودار – نمایش عنوان نمودار

Data Lables: مقادیر داده ها در نمودار
نحوه نمایش مقدار هر نقطه از نمودار از این قسمت تنظیم میشه. پیش فرض هم عدم نمایش مقادیر داده هاست.

حتما بخوانید: نمودار آبشاری چیست و چرا باید آن را بشناسیم؟ | Waterfall Chart

شکل ۷- اجزای نمودار – نمایش مقدار هر نقطه در جاهای مختلف

Data Tables: نمایش جدول داده های نمودار
هر زمان بخواهیم داده هایی که بر اساس آنها نمودار رسم شده، در داخل نمودار نمایش داده شوند، این جزء نمودار رو فعال میکنیم. حالت پیشفرض بر روی عدم نمایش جدول داده ها تنظیم شده است. در دو گزینه بعدی، یکی با راهنما و یکی بدون راهنمای نمودار، جدول داده ها رو در پایین نمودار نمایش می دهد. (شکل ۸)

شکل ۸ – اجزای نمودار – نمایش جدول داده های نمودار، در پایین نمودار

نکته:
اگر حتی یکی از نمودارها Scatter باشه، Data Tablee قابل نمایش نیست.


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



منبع آموزش:
گروه تخصصی اکسل پدیا | توابع اکسل | اکسل چیست
مهمترین اجزای نمودار ها که باید بشناسید!

 

[S.Cheraghi]

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

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

روش دوم استفاده از فرمول برای ایجاد سطر جدید در سلول:
تابع Char کاراکتر معادل کد اسکی (کد اسکی، کدی هست که معادل یک کارکتر هست و برای شناسایی کارکترها استفاده میشه) که بهش داده میشه رو بر میگردونه. عدد 10 کد اسکی کاراکتر Alt+Enter هست. یعنی اگر بخوایم در یک سلول از طریق فرمول خط جدید ایجاد کنیم به روش زیر عمل میکنیم:

شکل 1- ایجاد خط جدید در یک سلول اکسل​

نکته:
سلولی حاوی فرمول رو حتما باید در حالت Wrap Textt قرار بدیم تا خط جدید نمایش داده بشه.


برای دسترسی به ابزار Wrap Text از دو راه ممکن هست، این مسیر رو در شکل 2 و 3 مشاهده میکنید.

شکل 2- Wrap Text در تب Home​

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

شکل 3- Wrap text از Format Cell​

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


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

 

[S.Cheraghi]

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

ایجاد لیست فروریز در یک سلول از اکسل از طریق ابزار دیتا ولیدیشن (Data Validation) امکان پذیر است.

روش اول (با سلول کمکی):

1. داده های مورد نظر رو در یک محدوده تایپ کنید. مطابق شکل 1 روزهای هفته را تایپ میکنیم.
2. روی سلول مورد نظر که مایلیم لیست در آن قرار گیرد کلیک میکنیم. سل C2.
3. از مسیر Data/Data Validation/Settings/Allow گزینه List را انتخاب میکنیم.
4. در قسمت Source آدرس محدوده داده های مورد نظر را وارد میکنیم و سپس Ok.

شکل 1- ایجاد لیست کشویی در اکسل (DropDown List)​

حالا سلول مورد نظر ما حاوی لیستی از روزهای هفته است. (شکل 2)

شکل 2- ایجاد لیست کشویی در اکسل- لیست ایجاد شده در سلول C2​

روش دوم (بدون سلول کمکی):
داده های منبع لیست رو میتونیم مستقیما داخل Data Validation تایپ کنیم.

1. روی سلول مورد نظر که مایلیم لیست در آن قرار گیرد کلیک میکنیم. سل C2.
2. از مسیر Data/Data Validation/Settings/Allow گزینه List را انتخاب میکنیم.
3. داده های مورد نظر را در قسمت Source تایپ میکنیم. و با , از هم جدا میکنیم و Ok.

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

شکل 3- ایجاد لیست کشویی در اکسل- بدون سلول کمکی و تایپ مستقیم در DataValidation​

راه انتقال لیست تهیه شده به سلول های دیگر

1. سلول حاوی لیست فروریز را انتخاب کرده و کپی میکنیم.
2. محدوده مورد نظر که میخواهیم لیست فروریز را به آن انتقال بدیم رو انتخاب کرده و از Paste Special گزینه Validation را انتخاب میکنیم و Ok را میزنیم. (شکل 4)

شکل 4- انتقال لیست فروریز به سلول های دیگر​

منبع آموزش:
گروه تخصصی اکسل پدیا | توابع اکسل | اکسل چیست
ایجاد لیست کشویی در اکسل و کار با Data Validation