ترفند های excel

[Lean 56968]

ترفند 14

 

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

 

 

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

[Lean 56968]

ترفند15

در این ترفند قصد داریم به جستجوی عمودی (vertical look up) یا تابع vlookup بپردازیم :

 

آرگومان های تابع:

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

 

نمونه ای از این تابع به شرح زیر میتوان بکار برد

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

 

در این مثال مقداری که جستجو میکنیم در سل A2 واقع شده و table array در شیت دو قرار دارد عدد 2 یعنی مقداری که میخواهیم تابع به ما بدهد در ستون 2 قرار دارد و false تعیین میکند که عین مقدار a2 را جستجو کند در صورتی که مقدار true بدهیم مقادیر نزدیک به مقدار a2 را نیز مورد جستجو قرار داده و نتیجه دقیق به ما نمیدهد شایان ذکر است مقدار پیشفرض فاکتور چهارم true است بنابراین حتما مقدار false را برای دریافت مقدار دقیق وارد کنید.

 

 

.

[Lean 56968]

ترفند16

 

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

 

.

 

با ابراز remove arrow فلش های مربوطه پاک میشود

[Lean 56968]

ترفند17

 

یکی از ابزار های بسیار مفید و کاربردی در زمینه فرمول نویسی ابزار Evaluate formula از مجموعه formula auditing از تب Formula است ، با این ابزار شما قادرید نتایج فرمولی که نوشتید مرحله به مرحله مشاهده کنید و در صورتی که مرتکب اشتباهی در بخشی از فرمول شده باشید متوجه بشید .

 

.

[Lean 56968]

ترفند 18

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

 

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

[Lean 56968]

ترفند19

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

 

 

.

 

[Lean 56968]

ترفند 20

 

این ترفند همانند ترفند 19 روش ساختن لیست فروریز بااستفاده از data validation است با این تفاوت که داده ها در شیت دیگری قرار دارد ، از data validation فقط در شی جاری به صورت مستقیم می توان استفاده کرد برای حالتی که داده ها در شیت جاری نباشد باید از نام گذاری از طریق name manager استفاده کرد در تصویر زیر نحوه کار نمایش داده شده لازم به ذکر است برای نام گذاری از کلید های میانبر ctrl+alt+F3 استفاده کردم.

 

 

.

[Lean 56968]

ترفند 21

 

احتمالا همه به فرایندfind&replace آشنا هستن با زدن ctrl+h پنجره مربوطه باز میشه شما میتونید با استفاده از این امکان فرمت سل هارو هم عوض کنید مثلا همه خانه هایی که زرد کردین سبزش کنید کافی دکمه option رو بزنید و فرمتی که میخواید جستجو بشه و فرمتی که میخواید جایگزین بشه وارد کنید و replace all بزنید

.

[baran1989 12]

سلام

من میخوام بااکسل نمودارتیتراسیون بکشم برای گزارشکارم میخوام ولی متاسفانه من اصلاازاکسل هیچی بلد نیستم حالاهم شدیدا وسریعا به کمک نیاز دارم:icon_pf (34)::icon_pf (34)::icon_pf (34):

لطفابه من کمک کنید...باتشکر:icon_gol:

[Lean 56968]

ترفند22

برای مقایسه داده های دو ستون روش های مختلفی هست مثل conditional formatting یا VBA یا مقایسه از طریق فرمول مثل EXACT ، یک روش سریع و ساده در مواقعی که میخوایم یک کنترل ساده داشته باشیم استفاده از ابزار GO TO هست به این صورت که ابتدا داده های دو ستون رو انتخاب کنید سپس به ابزار GO TO SPECIAL مراجعه کنید و اونجا گزینه ROW DIFFERENCES رو انتخاب کنید در اینصورت داده هایی که برابر نیستند انتخاب میشن حالا کافیه فقط رنگ اونهارو عوض کنید تا مشخص بشن

 

 

.

[Lean 56968]

ترفند23

 

یک ترفند ساده ولی کار راه انداز اینه که برای جدا سازی آرگومان های یک فرمول از کلون(,( یا سمیکلون(;( استفاده میشه شما با ترکیب ctrl+click میتونید اتوماتیک آرگومان ها رو جدا کنید.

[Lean 56968]

ترفند 24

 

در اینجا قصد دارم راهکار هایی برای افزایش قابلیت و سرعت برنامه هایی که با VBA نوشته میشه ارائه بدم بعضی از این راه کار ها به صورت تجربی بدست آوردم

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

 

1-غیر فعال کردن برخی از خصوصیت ها حین اجرای ماکرو برای این کار کد های زیر را به ابتدا و انتهای برنامه بیافزایید.

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

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

 

Application.StatusBar = false

Application.PrintCommunication = False

 

 

 

2-تا حد امکان از انتخاب کردن سل ها و محدوده ها خودداری کنید،بجای انتخاب کردن کپی کردن و paste کردن از تساوی استفاده کنید.

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

کد فوق از کد زیر سریعتر است.

 

 

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

3- تا حدی که می توانید از توابع و متد های زبان برنامه نویسی استفاده کنید و از کد نویسی برای نیاز های خود پرهیز کنید مثلا استفاده از کد زیر

 

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

بجای

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

 

 

4- از ایجاد حلقه ها بخصوص حلقه های تودر تو اجتناب کنید ، برای حل یک مسئله ابتدا روش ها مختلف را بررسی کنید به عنوان مثلا برای جستجوی یک مقدار از توابع search,find,index,match,lookup,… استفاده کنید .

5- اگر میخواهید عملیاتی بر روی یک محدوده اعمال کنید محدوده را نامگذاری کنید برای توسعه محدوده از فرمول offset استفاده کنید بجای اینکه محدوده بزرگی انتخاب کنید تا نیاز اینده را برطرف کند بلکه محدوده را به تدریج توسعه دهید.

6-زمانی که میخواهید عملیات متفاوتی بر روی یک ابجکت اعمال کنید از with block استفاده کنید این کار علاوه بر نظم ظاهری در عمکرد کد هم موثر است.

7-زمانی که میخواهید نیاز خود را از طریق کد نویسی در اکسل مرتفع کنید به جوانب مسئله خود فکر کنید و ان را به دو بخش تقسیم کنید 1- بخشی که لزوما باید کد نویسی کنید و 2-بخشی که از طریق ابزار های اکسل اعم از فرمول ها ، conditional formattingو addins های اکسل و... میتوان حل کرد بنا براین لزومی ندارد کل مسئله به صورت کد پیاده شود تلفیق کلیه امکانات در دسترس نتیجه بهتری میدهد

8-در نوشتن فرمول ها از قواعد نامگذاری استفاده کنید و محدوده ها را با offset توسعه دهید

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

10-برنامه خود را قبل از شروع به کد نویسی روی کاغذ پیاده کنید و طرح اولیه انرا ایجاد کنید سپس به قسمت های کوچک تقسیم کنید سعی نکنید یک پروسه را با یک کد طولانی انجام دهید بلکه برنامه را به توابع و رویه های کوچک تقسیم کنید.

11- از کنترل خطاها یا error handing تا حد امکان استفاده نکنید مگر اینکه طیف وسیعی از افراد کاربر برنامه شما باشند در صورتی که خود شما کاربر برنامه هستید از حداقل کنترل خطا استفاده کنید البته میزان دقت خود شما در این روش مؤثر است.

12-با استفاده از ctrl+end آخرین سل استفاده شده را یافته و در صورتی که نیاز ندارید انها را حذف کنید

13-در صورتی که قصد ایجاد جدول و یا دیتابیس در اکسل دارید از table استفاده کنید.

14-اطلاعات موجود را قبل از استفاده sort کنید.

15- در هر خط برنامه میتوان از خلاقیت برای افزایش کارایی استفاده کرد به عنوان مثال نتیجه دو کد زیر یکسان است اما قابلیت کد دوم به مراتب بیشتر است.

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

16-برای کاهش حجم میتوانید از فرمت xlsb بجای xlsmیا xlsx استفاده کنید در این حالت برنامه شما کم حجم تر خواهد بود.

17-از عوامل مهم کاهش سرعت استفاده از منابع و لینک های خارجی است لذا از ایجاد لینک های غیر ضروری باید پرهیز شود.

18-اگر فایل هایی با فرمت 2003 دارید و در حال حاضر از ورژن های 2007 به بالا استفاده می کنید آنها را به فرمت های جدید convert نمایید.

19-استفاده از pivot table بر کارایی و سرعت برنامه شما نسبت به سایر روش ها خواهد افزود.

[Lean 56968]

ترفند25

 

 

trend line یا سیر روند چیست؟

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

 

سیر روند چیست؟

 

Trend Line يا سير روند؛ نمايشی گرافيگی از روند سری داده‌هاست، مثل خط شيب‌داری كه افزايش فروش را طی چند ماه نشون میده. سير روند كه اغلب برای مطالعه مشكلات پيش‌بينی استفاده میشه «تجزيه و تحليل براساس رگرسيون» بهش میگن.

 

از سير روند در منحنی‌های Area - Bar - Column - Line و XY استفاده می‌شه. برای اضافه‌كردن سير روند به نمودار مورد نظر از تب chart tools گزينه‌ی Trendline را انتخاب كنيد. اما به خاطر داشته باشيد كه در نمودارهای 3d - Radar - Pie - Doughnut و Buuble نمی‌شه از سير روند استفاده كنيد

 

انواع سير روند:

 

Linear / خطی؛ با فرمول Y=m.x+b كه m شيب خط و b عدد ثابت (همان عرض از مبدا خودمان) است.

 

Logarithmic / لگاريتمی؛ با فرمول Y=c.Lnx+b كه c و b اعداد ثابت هستند.

 

Polynomial / قطبی؛ با فرمول Y=b+c1x+c2x2+c3x3+...+cnxnكه در آن c عدد ثابت است.

 

Powre / توانی؛ با فرمول Y=C.xb كه b و c اعداد ثابت هستند.

- نكته: هنگامی كه داده‌ها شامل اعداد منفی يا صفر باشند قابل استفاده نيست!

 

Emponential / توصيفی؛ با فرمول Y=C.ebx كه b و c اعداد ثابت و e مبنای لگاريتم نپرين است(e=2.71).

- نكته: هنگامی كه داده‌ها شامل اعداد منفی يا صفر باشند قابل استفاده نيست!

 

Moving Average / ميانگين متحرك که تو درس کنترل موجودی دوستان صنایعی به وفور ازش استفاده میکنن؛ با فرمول Ft=(At+At-1+...+At-n+1)/n

- نكته: تعداد نقاط در اين سير روند برابر است با تعداد داده‌ها منهای نقطه‌ای كه معين می‌كنيد. به‌طور مثال، در مقايسه 12 ماهه، شروع منحنی از ابتدای ماه دومه، ولی اگر عدد 5 رو انتخاب كنيد منحنی از ماه 5 رسم خواهد شد.

 

برای اینکه کنترل بیشتری بر روی trend line داشته باشید از منوی فروریزی که باز میشه more trend line options رو انتخاب کنید

. در پنجره forecast با انتخاب forward آینده وگزینه Backward گذشته را پیش بینی میکنیم.

دو گزینه پرکابردی که در تحلیل پیش بینی و همچنین براورد صحت اون به ما کمک میکنه دو گزینه display equation on chart و display R-squared value on chart هستند که اولی تابع y رو بر حسب x نمایش میده(فرمول رگرسیون) و دومی مقدار r^2 رو در رگرسیون (میزان تطبیق پیش بینی با واقعیت )به ما نمایش خواهد داد

 

 

 

.

[Lean 56968]

ترفند 26

 

 

نحوه import کردن از یک csv به اکسل

معرفی csv

 

CSV یا همان Comma Seperated Values یک قالب فایل معروف و خیلی قدیمی (از حدود ۴۵ سال پیش!) است که در محیط‌ها و نرم افزارهای بسیار مختلفی مورد استفاده قرار می‌گیرد. در این قالب هر رکورد از اطلاعات در یک سطر فایل ذخیره می‌شود. و در هر سطر هم فیلدها به وسیله کاراکتر کاما «,» از هم جدا می‌شوند.

 

برای CSV هیچ استاندارد واحدی وجود ندارد. حتی RFC 4180 هم به صورت Informational ارائه شده است یعنی استاندارد واحدی را مشخص نکرده و صرفا قالبی که در بیشتر پیاده‌سازی‌ها مورد استفاده قرار گرفته را معرفی می‌کند. این یعنی اینکه CSV که از اکسل می‌گیرید لزوما با CSV که ممکن است از Gmail Contacts یا MySQL بگیرید یکی نخواهد بود. مثلا در یکی از header استفاده شده و در دیگری نه و یا مقدار فیلدها در یکی با double qution محصور شده و در دیگری نه. بدترین قسمت این ماجرا این است که در بیشتر پیاده سازی هیچ اهمیتی به Enocding داده نشده و فرض همه به ASCII بودن فایل است و بدتر از این از آنجا که به نظر می‌رسد CSV از Byte Order استفاده نمی‌کند،در نتیجه فایل آن حتما باید تک بایتی باشد مثل ASCII و UTF-8 و باز هم در نتیجه نمی‌توان از قالب‌های ۲ بایتی (یعنی هر کاراکتر در ۲ بایت ذخیره شود) مثل فایل‌های متنی یونیکد ویندوز در آن استفاده کرد. البته در حال تک بایتی هم Encoding را خود استفاده کننده باید بفهمد و نوع Encoding مورد استفاده از هیچ جای یک فایل CSV قابل استخراج نیست. در بعضی جاهای خاص مثل وقتی که قرار است فایل CSV به عنوان یک MIME TYPE به اسم text/csv رد و بدل شود یک header به نام charset هست که می‌توان Encoding را در آن معرفی نمود. فراموش نشود که این header خارج از خود فایل CSV قرار دارد.

 

در بعضی حالات به نظر می‌رسد که CSV از Byte Order استفاده می‌کند یعنی مثل فایل‌های متنی داخل ویندوز می‌شود یک یا چند بایت خاص را به منظور تعیین Encoding مورد استفاده در ابتدای فایل قرار داد و سپس هر کاراکتر را با توجه به Encoding انتخابی ۱ یا ۲ بایت در نظر گرفت. البته در یکی دو آزمایش معلوم شد که خیلی برنامه‌ها از جمله Excel 2007 با فایل‌های متنی ۲ بایتی (ذخیره هر کاراکتر در ۲ بایت) مشکل دارند و این روش خیلی قابل اطمینان نیست.

 

هر فایل CSV به صورت یک فایل متنی خالص تک بایتی بدون Byte Order ذخیره شده ولی برای ذخیره مقادیر یونیکد آنها را به قالب UTF-8 در آورده و در جای فیلدها ذخیره شود. برای خواندن این CSV هم همه فیلدها از UTF-8 به متن معمولی decode شود. به جای UTF-8 از هر روش دیگری هم که کاراکترهای یونیکد را به رشته‌ای از کاراکترهای تک بایت تبدیل می‌کنند هم می‌شود استفاده کرد. مثلا مثل این روش در صفحات HTML که کاراکتر «ن» فارسی با کد «#1606;» نمایش داده می‌شود. فقط باید دقت شود که استفاده کننده فایل CSV هم از روش Encoding مورد استفاده ما با خبر باشد

 

نحوه import کردن از یک csv به اکسل

 

براى مشاهده فايل CSV در اکسل به این صورت عمل کنید: از منوى File گزينه Open را انتخاب نماييد. - در پنجره Open بعد از يافتن فايل مربوط, گزينه(Text Files(*.prn; *.txt; *.csv را انتخاب کنيد. - در پنجره Text Import Wizard ابتدا گزينه Delimited , سپس گزينه (Unicode (Utf-8 و دکمه Next را انتخاب کنيد. - در پنجره بعدى , در قسمت Delimiters گزينه Comma را انتخاب و گزينه Tab را از حالت انتخاب شده خارج کنيد. - دکمه Finish را کليک کنيد. - حال با انتخاب گزينه Save از منوى فايل , مى توانيد اين فايل با قالب مورد نظر خود ذخيره نماييد.

[Lean 56968]

ترفند27

 

گاهی برای ارائه یک فایل و یا زمانی که قصد داریم کاربر را از دسترسی به امکانات اکسل محروم کنیم نیاز به نمایش فایل به صورت فول اسکرین خواهیم داشت ، برای اجرای این کار میبایست از کد های VBA استفاده نماییم لذا در VBA Editor اکسل کد های زیر را ران نمونده و نتیجه را مشاهده نمایید

1

 

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

 

2

 

Private Sub Worksheet_Deactivate()

With Application
.DisplayFullScreen = False
.CommandBars("worksheet menu bar").Enabled = True
End With

End Sub

کد شماره یک صفحه را فول اسکرین نموده و کد شماره 2 این حالت را غیر فعال می نماید

[Lean 56968]

ترفند 28

 

در اکسل برای سلول ها می توان فرمت شخصی تعریف کرد ، در ابتدا باید به پنجره فرمت سل اجرا کنیم که یا از CTRL+1 استفاده میکنیم و یا راست کلیک کرده و گزینه format cells را انتخاب نماییم:

 

 

.

در پنجره ای که باز می شود باید تب number و از category قسمت custom رو انتخاب کنیم:

 

 

.

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

 

در اینده به این موضوع خواهیم پرداخت.

[Lean 56968]

ترفند29

 

در ادامه مطلبی که در ترفند قبل مطرح شد به بحث Custom Format می پردازیم:

 

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

.

 

.

 

.

 

.

[Lean 56968]

ترفند30

0 : دقیقاً مانند # عمل می کند با این تفاوت که صفر را نشان می دهد

 

 

 

.

[Lean 56968]

ترفند31

 

در ادامه مبحث costum format cells به کاربرد @ می پردازیم

 

@ : معادل Text است که دارای موضوع همراه با علامت ; مفهوم پیدا خواهد کرد!

 

 

فرض کنید میخواهید بعد از نوشتن هر حرف بعد از ان نقطه چین قرار گیرد کافیست در بخشی که قبلا توضیح داده شد از عبارت .*@ استفاده کنید لازم به ذکر است هر کاراکتری بعد از علامت ستاره (*) استفاده شود تکرا خواهد شد

.

[Lean 56968]

ترفند32

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

 

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

"0"0000000000

 

کاراکتری که بین علامت کوتیشن "" قرار گیرد به صورت ثابت در سل نمایش داده می شود

 

.