مدل های بعدی را
میتوانید روی لینک های زیر مطالعه بفرمایید:
اکسل بهمراه کدهای 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) دارد که مربوط به ستون
اول از چپ و ردیف اول از بالا هست و یک محل خروجی دارد که مربوط به استخراج
اطلاعات از جدول است.
حالا من ستون اول جدول فوق که "کد پرسنلی" است را با ستون
"نام خانوادگی" طبق شکل زیر جابجا میکنم:
بر اساس شکل زیرشما میتوانید ببینید که آنالیزو نتیجه کنترلی از این
به بعد بر اساس "نام خانوادگی" خواهد بود نه "کد پرسنلی":
نکته مهم این است که شما میتوانید این مدل را کنار هم به تعداد کپی
کرده و همزمان اطلاعات پرسنلی چند کارمند را آنالیز کنید. درست مثل شکل زیر:
فیلم آموزشی زیر روش کار با این مدل را به شما نشان میدهد:
جهت دریافت این مدل درخواست خود را به آدرس های زیر بفرستید:
*** توجه: مدل فوق برای
حداکثراطلاعات ورودی 10 ستون و 25 ردیف (25*10 (میباشد.
در صورتیکه شما نیاز به ابعاد بیشتری برای ورود اطلاعات دارید, میتوانید درخواست خود را به آدرس های فوق سفارش دهید.
- مثال برای مدیریت مالی
کد 2251) فرض
کنید اطلاعات مربوط به بورس کلیه شرکتها را روی یک جدول در اکسل شبیه شکل زیر
دارید:
با استفاده از مدل H -V شما میتوانید براحتی و خیلی سریع اطلاعات
مندرج در جدول را استخراج و جهت آنالیز و یا محاسبات بعدی به هر فایل اکسل دیگر
انتقال دهید. همانند شکل زیر:
بر اساس شکل فوق, این مدل برای تحلیل بورس دارای یک ورودی (Input) بیشتر نسبت به مدل منابع
انسانی است. در حقیقت شما در این مدل دو ستون اول را با ردیف اول (نوع پارامتر
کنترلی) مقایسه و اطلاعات را استخراج و یا منتقل میکنید. همانند مدل منابع انسانی, میتوان ستون اول و دوم را
با ستون های دیگر جایگزین کرد و روش آنالیز را تغییر داد. بطور مثال در شکل زیر, من "درصد قیمت
پایانی" را با نماد شرکت جایگزین کرده ام.
در این حالت شما میتوانید "درصد قیمت پایانی" را با یک
پارامتر کنترلی ردیف اول مقایسه نموده و اطلاعات را از جدول استخراج و یا انتقال
دهید. دقیقا شبیه شکل زیر:
ضمنا شما میتوانید این مدل را کنار هم به تعداد کپی کرده و همزمان
اطلاعات بورسی چند شرکت را آنالیز کنید. درست مثل شکل زیر:
فیلم آموزشی زیر روش کار با این مدل را به شما نشان میدهد:
جهت دریافت این مدل درخواست خود را به آدرس های زیر بفرستید:
*** توجه: مدل فوق برای
حداکثراطلاعات ورودی 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) را انتخاب و در محل مورد
نظر جایگزین میکنیم.
حالا در مثال زیر من موارد استفاده از انتقال دیتا از جدول و روش ساخت
نرم افزار بر اساس انتقال دیتا را توضیح میدهم.
محاسبه فشار مجاز در لوله:
جهت محاسبه فشارمجاز در لوله ها از رابطه بارلو بشرح زیر استفاده
میکنیم:
Pa =
2 Sy Fd Fe Ft t
/ do
در فرمول فوق داریم:
Pa = ماکزیمم فشار طراحی
مجاز بر حسب psi
Sy = تنش تسلیم لوله بر حسب psi
t = ضخامت لوله برحسب اینچ
do
= قطرخارجی لوله برحسب اینچ
Fd =
ضریب ایمنی طراحی
Fe =
فاکتور درز جوش
Ft =
ضریب حرارتی
حال اگر فرمول فوق را بصورت الگوریتم در اکسل بنویسیم, میتوانیم بر اساس خروجی (Output) جدول فوق, مستقیما به عدد فشار مجاز
طراحی برسیم. این بدان معناست که ما شروع به ساخت یک نرم افزار کرده ایم. دقیقا
مثل شکل زیر:
فیلم آموزشی زیر روش کار با این مدل را به شما نشان میدهد:
جهت دریافت این مدل درخواست خود را به آدرس های زیر بفرستید:
- مثال برای مهندسی برق (قدرت):
کد 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 درجه را وارد
کنیم درصد افت ولتاژ طبق شکل زیر محاسبه خواهد شد:
فیلم آموزشی زیر روش کار با این مدل را به شما نشان میدهد:
جهت دریافت این مدل درخواست خود را به آدرس های زیر بفرستید:
