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

مدل های بعدی را میتوانید روی لینک های زیر مطالعه بفرمایید:

مدل شماره 2) تولید انبوه پسورد (Password):  http://www.emfps.org/2017/09/2-password.html

مدل شماره 3) دینامیک و حرکت در اکسل:    http://www.emfps.org/2017/09/3.html

مدل 4) آنالیز سه بعدی با کلیک:   http://www.emfps.org/2017/10/4.html

مدل 5-1) کنترل سیستم های مالی با یک ایده جدید: http://www.emfps.org/2017/10/5.html

اکسل بهمراه کدهای VBA که یک زبان برنامه نویسی با مخفف Visual Basic for Applications میباشد, یک نرم افزار بسیار قوی بوده که توسط شرکت مایکروسافت طراحی گردیده است. یکی از شاخصه های مهم نرم افزار اکسل نسبت به دیگر نرم افزارها, در دسترس بودن آن بدون هزینه اضافی برای عموم مردم است. بوسیله نرم افزار اکسل شما توانایی انجام هرکارمحاسباتی را خواهید داشت از محاسبات سازه یک ساختمان در مهندسی عمران گرفته تا محاسبه و طراحی یک قطعه تحت ارتعاش در مهندسی مکانیک و یا طراحی مدارهای الکتریکی در مهندسی برق و همچنین ساخت مدل های اقتصادی و یا مدل های مالی بهمراه مدل های مدیریت ریسک برای پیش بینی بورس و کلیه بازارهای مالی و غیره. در این خصوص بیایید نگاهی کنیم به بیانیه ماموریت شرکت مایکروسافت که به نظر من زیباترین بیانیه ای است که تاکنون بین شرکت ها دیده ام:

"توانایی دادن به مردم و کسب وکارها در سرتاسرجهان برای دستیابی به همه پتانسیل هایشان"

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

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

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

"پس بیایید با هم ایده های نو تولید کنیم"

* قبل از شروع مطالعه این سری مقالات و جهت تمرین عملی و یادگیری بهتر, لطفا برنامه نرم افزاراکسل (2010 یا 2013) خود را بشرح ذیل تنظیم کنید:

- یک شیت اکسل باز کنید و در قسمت بالا سمت چپ روی آیکون File وسپس روی Options و Formula  کلیک کنید

- در قسمت بالا سمت راست,  Enable iterative calculation را تیک بزنید

- در سمت چپ روی Add-Ins کلیک کرده و در قسمت پایین صفحه در محل Manage روی Go کلیک کنید

- در Add-Ins محل Analysis ToolPak و Analysis ToolPak-VBA و Solver Add-in را تیک یزنید سپس آیکون OK را کلیک کنید

** دوستانی که تمایل به دریافت فایل های اکسل دارند, فقط کافیست شماره مدل و شماره کد را به آدرس ایمیل یا تلگرام ارسال کنند. بطور مثال: فایل" 1_5022"

شماره 1) مدل H – V

یکی از فرمولهایی که در اکسل کاربرد بسیار وسیعی دارد, دستور Vlookup میباشد کار با این فرمول بسیار ساده است. جهت یادگیری شما میتوانید روی شیت اکسل در قسمت نوار بالا سمت راست علامت سوال "؟" را کلیک کرده و در قسمت Search  نام این دستور را تایپ کنید و سپس با کلیک آن توضیحات کامل را بهمراه مثال به شما نشان میدهد. خیلی از دوستان از این دستور فقط برای کنترل جدول ها استفاده میکنند در حالی که کاربرد اصلی این فرمول برای ساخت مدل های مبتنی بر روش آنالیز مونت کارلو بوده که برای آنالیز ریسک در مدیریت و توسعه مدل های پیش بینی در مدیریت مالی و همچنین ساخت مدل های طراحی در مهندسی استفاده زیادی از آن میشود که در آینده در همین سری از مقالات روش ساخت این نوع مدل ها را به شما نشان خواهم داد.

دستور دیگر که شبیه به فرمول بالا است, دستور Hlookup میباشد. در حقیقت دستور Vlookup به یک آیتم در یک ستون از کل جدول نگاه میکند وهم ردیف آن آیتم را در هرستون دلخواه دیگرجدول به شما میدهد. اما دستور Hlookup بلعکس به یک آیتم در یک ردیف از کل جدول نگاه میکند وهم ستون آن آیتم را در هرردیف دلخواه دیگرجدول به شما میدهد. همانطور که می دانیم برای استفاده از هر جدول ما پارامتر های درج شده در اولین ستون را با متغیر های درج شده در اولین ردیف جدول مقایسه نموده و اطلاعات مورد نیاز خود را از جدول استخراج میکنیم. در حالیکه هیچیک از فرمول های Vlookup و Hlookup بتنهایی این امکان را به ما نمی دهند. مدل H – V که ترکیبی است از هر دو فرمول توانایی های زیر را به ما میدهد:

- استخراج و انتقال سریع اطلاعات مورد نیاز از هر جدول اطلاعات (Data Table) بر اساس متغیر های ردیف اول عمودی و افقی جدول درحالی که با استفاده از Freeze Panes  امکان انتقال دیتا را ندارید و از طرفی هنوز احتمال اشتباه وجود دارد.

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

- این مدل قابلیت جابجایی دارد یعنی بر اساس نیاز, شما میتوانید ردیف اول عمودی و افقی جدول را با هر ستون و ردیف جدول با استفاده از Matrix Transpose جایگزین کرده و تحلیل و آنالیز جدیدی داشته باشید

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

بطور مثال در اجراء پروژه های ساختمانی و صنعتی و در کارگاه ها و سایت ها ما بطورمتداول به جداول اطلاعات فنی (Data Sheets) روی شیت های اکسل همانند مثال های زیر برخورد میکنیم:

MTO (Material Take Off0

BOM (Bill of Material)

BOQ (Bill of Quantities)

Joint History Piping

Control Instrumentation data sheet

جهت تسهیل در استخراج و انتقال اطلاعات از جداول فوق مخصوصا بدون هیچگونه اشتباهی, مدل H – V میتواند کمک بسیار زیادی به ما کند. 

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

- مثال برای مدیریت منابع انسانی

کد 2250 ) فرض کنید جدول پرسنلی زیر موجود است:

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

حالا من ستون اول جدول فوق که "کد پرسنلی" است را با ستون "نام خانوادگی" طبق شکل زیر جابجا میکنم:

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

نکته مهم این است که شما میتوانید این مدل را کنار هم به تعداد کپی کرده و همزمان اطلاعات پرسنلی چند کارمند را آنالیز کنید. درست مثل شکل زیر:

فیلم آموزشی زیر روش کار با این مدل را به شما نشان میدهد:

 جهت دریافت این مدل درخواست خود را به آدرس های زیر بفرستید:

https://t.me/Allisnumber

*** توجه: مدل فوق برای حداکثراطلاعات ورودی 10 ستون و 25 ردیف (25*10 (میباشد. در صورتیکه شما نیاز به ابعاد بیشتری برای ورود اطلاعات دارید, میتوانید درخواست خود را به آدرس های فوق سفارش دهید.

- مثال برای مدیریت مالی

کد 2251) فرض کنید اطلاعات مربوط به بورس کلیه شرکتها را روی یک جدول در اکسل شبیه شکل زیر دارید:

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

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

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

ضمنا شما میتوانید این مدل را کنار هم به تعداد کپی کرده و همزمان اطلاعات بورسی چند شرکت را آنالیز کنید. درست مثل شکل زیر:

فیلم آموزشی زیر روش کار با این مدل را به شما نشان میدهد:

جهت دریافت این مدل درخواست خود را به آدرس های زیر بفرستید:

https://t.me/Allisnumber

*** توجه: مدل فوق برای حداکثراطلاعات ورودی 21ستون و276 ردیف (276*21) میباشد. در صورتیکه شما نیاز به ابعاد بیشتری برای ورود اطلاعات دارید, میتوانید درخواست خود را به آدرس های فوق سفارش دهید.

- مثال برای مهندسی مکانیک (پایپینگ):

کد 2252) یک جدول استاندارد دردنیا برای لوله های استنلس استیل ((Stainless Steel بدون درز و جوشی وجود دارد که براساس Size و Schedules  , مشخصات فنی لوله شامل قطر بیرونی, ضخامت و وزن واحد طول از جدول مذکورقابل استخراج است. همانند شکل زیر:

در شکل فوق سایز لوله (Size NB) در دو ردیف ضخامت (T) و وزن واحد طول (W) برای هر سایز در ستون دوم از چپ آمده است که بر اساس ردیف اول (Pipe Schedules) , قطر بیرونی (O.D) وضخامت (T) لوله بر حسب اینچ و وزن ((W هر فوت طول لوله برحسب پوند, از ردیف ها قابل استخراج میباشند.

همانطور که در شکل زیر میبینیم, این مدل برای استخراج مشخصات لوله از جدول فوق دارای دو محل ورودی (Input) و پنج محل خروجی (Output) است. که دراولین محل ورودی وروبروی محل (  ,(ODD. No ازاولین ستون سمت چپ جدول فقط شماره های فرد را انتخاب کرده و جایگزین میکنیم سپس درمحل دومین ورودی از ردیف اول جدول (Pipe Schedules) را انتخاب و در محل مورد نظر جایگزین میکنیم.

حالا در مثال زیر من موارد استفاده از انتقال دیتا از جدول و روش ساخت نرم افزار بر اساس انتقال دیتا را توضیح میدهم.

محاسبه فشار مجاز در لوله:

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

P_a = 2 S_y F_d F_e F_t t / d_o      

در فرمول فوق داریم:

P_a = ماکزیمم فشار طراحی مجاز بر حسب psi

 S_y = تنش تسلیم لوله بر حسب psi

t  = ضخامت لوله برحسب اینچ

d_o = قطرخارجی لوله برحسب اینچ

F_d  = ضریب ایمنی طراحی

F_e  = فاکتور درز جوش

F_t = ضریب حرارتی

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

فیلم آموزشی زیر روش کار با این مدل را به شما نشان میدهد:

جهت دریافت این مدل درخواست خود را به آدرس های زیر بفرستید:

https://t.me/Allisnumber

- مثال برای مهندسی برق (قدرت):

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

 در این مثال به مقایسه درصد افت ولتاژ بین جریان مستقیم (DC) و جریان متناوب (AC ) سه فازبا استفاده از جدول زیرمی پردازیم:

فرمول درصد افت ولتاژ برای جریان متناوب (AC) سه فاز بشرح زیر است:

ΔV% = (1.73Z.In.L / V).100

Z = (R^2 + X^2) ^0.5

در فرمول فوق داریم:

ΔV% = درصد افت ولتاژ

R = مقاومت هادی در 70 درجه بر حسب اهم

X = اندوکتانس (مقاومت القایی) بر حسب اهم

In = شدت جریان عبوری از کابل بر حسب آمپر

L = طول کابل بر حسب کیلومتر

V = ولتاژخط بر حسب ولت

فرمول درصد افت ولتاژ برای جریان مستقیم (DC) بشرح زیر است:

ΔV% = (2R.In.L / V).100

در فرمول فوق داریم:

ΔV% = درصد افت ولتاژ

R = مقاومت هادی در 20 درجه بر حسب اهم

In = شدت جریان عبوری از کابل بر حسب آمپر

L = طول کابل بر حسب کیلومتر

V = ولتاژخط بر حسب ولت

حالا با استفاده از مدل H – V و نوشتن فرمول های فوق بصورت الگوریتم در اکسل میتوان بسرعت درصد افت شدت ولتاژ را بین جریان مستقیم (DC) و جریان متناوب (AC ) سه فازهمانند شکل های زیر مقایسه کرد:

اگر در شکل بالا برای همان سطح مقطع 70*3, مقاومت DC هادی در 20 درجه را وارد کنیم درصد افت ولتاژ طبق شکل زیر محاسبه خواهد شد:

فیلم آموزشی زیر روش کار با این مدل را به شما نشان میدهد:

جهت دریافت این مدل درخواست خود را به آدرس های زیر بفرستید:

https://t.me/Allisnumber

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

در تحلیل تکنیکال از شکل روند دیاگرام قیمت سهام یا نرخ بازگشت سرمایه یک شرکت که در طول یک بازه زمانی چندین بارتکرار شده برای پیش بینی قیمت سهام استفاده میکنند. حتی با اینکه من به تکرار و دوره تناوب بازار سرمایه باور دارم اما در حال حاضر ریسک استفاده از این روش بدلیل افزایش خیلی سریع تغییرکه توسط تکنولوژی اتفاق افتاده, بسیار بالاست به دو دلیل مهم:

 1) شکل دوره تناوب همیشه دایره نیست گاهی اوقات شبیه یک حلزون هست و گاهی اوقات شبیه دوره های تناوبی هست که من در مقالات زیر به آن اشاره کرده ام:

http://www.emfps.org/2017/08/the-change-depends-on-direction-of_27.html

http://www.emfps.org/2017/08/the-change-depends-on-direction-of_9.html

http://www.emfps.org/2017/08/the-change-depends-on-direction-of.html

2) طول یک بازه زمانی که همان دامنه و یا شعاع های دوایر یا حلزون متحدالمرکز است بعلت تغییرات شکل گرفته توسط تکنولوژی قابل پیش بینی نیست. این یعنی مبدا بازه زمانی شما برای یک دوره تناوب کجا بایدباشد؟

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

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

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

گام بعدی برای تحلیل بنیادی, پیش بینی یک دوره پنجساله برای پارامترهای اقتصادی مثل: سود بانکی ,تورم, رشد اقتصادی و غیره میباشد

بهترین مدل برای تحلیل بنیادی استفاده همزمان از روش DCF ((Discounted Cash Flow Analysis و روش مونت کارلو برای کاهش ریسک میباشد این همان روشی است که من برای پیش بینی قیمت سهام شرکت نایک (Nike Inc.) استفاده کردم. (لینک زیر)

http://www.emfps.org/2012/01/monte-carlo-analysis-on-case-of-nike.html

در این مدل با بازی کردن با فرضیات مثل پیش بینی فروش و هزینه ها و پارامترهای اقتصادی میتوانید به کمترین و بیشترین پیش بینی قیمت سهام شرکت دست پیدا کنید.

The Change Depends on the Direction of the Motion: The Gradient Vector and Symmetric Group Action (1)

Following to article of “The Change Depends on the Direction of the Motion: The Symmetric Group Action (2)” posted on link: http://www.emfps.org/2017/08/the-change-depends-on-direction-of_9.html?m=1, before I start new operators with four points, I would like to inform you that there are many other properties which can be derived from previous theorems. The purpose of this article is, to use the gradient as an operator accompanied by symmetric group action in which they work together. In this article as an example, I only examine the properties of an operator 3*3 which works with gradient vector of function:

f (x, y, z) = x^n + y^n + z^n  

Regarding to my previous articles, I introduced to you many symmetric groups actions and 11 theorems where all of them accompanied by the gradient vector of any function will generate many properties and theorems.

As you saw, we had matrix “M” as below operator 3*3:

M =

The property of function:  w = f (x, y, z) = x^2 + y^2 + z^2

The gradient vector of this function is:

Theorem 12: Maximum and minimum magnitude of the vector produced by operator M and the gradient vector of function f (x, y, z) = x^2 + y^2 + z^2 are obtained by below formulas:

Where:

r1 = radius in operator M

r2 = radius of each point on surface or space in accordance with its polar coordinates

Theorem 13: Maximum and minimum magnitude of the vector produced by operator M and the gradient vector of function f (x, y, z) = x^3 + y^3 + z^3 are obtained by below formulas:

Where:

r1 = radius in operator M

r2 = radius of each point on surface or space in accordance with its polar coordinates

Theorem 14: Maximum and minimum magnitude of the vector produced by operator M and the gradient vector of function f (x, y, z) = x^4 + y^4 + z^4 are obtained by below formulas:

Where:

r1 = radius in operator M

r2 = radius of each point on surface or space in accordance with its polar coordinates

Theorem 15: Maximum and minimum magnitude of the vector produced by operator M and the gradient vector of function f (x, y, z) = x^5 + y^5 + z^5 are obtained by below formulas:

Where:

r1 = radius in operator M

r2 = radius of each point on surface or space in accordance with its polar coordinates

The property of function:  w = f (x, y, z) = x^n + y^n + z^n

As we as well as know, “n” time partial differential of this function will be calculated by using below formula:

Theorem 16: For function w = f (x, y, z) = x^n + y^n + z^n and operator M, we have:

Example:

Suppose you have below function:

w = f (x, y, z) = x^5 + y^5 + z^5

And also you have below conditions for operator M:

r1 = 22.6

θ = 51 Degree

β = 13 degree

Then, operator M will be:

M = 

According to above formula, we have:

Therefore, we can see:

In the reference with theorem 16, we can find a very interesting theorem as follows:

When we say n! , it means that we can consider it as a constant value for any vector:

V = c (i + j + k)

Theorem 17: Each vector V = c (i + j + k) multiplied by operator M will be equal zero.