آخرین به‌روزرسانی: 18 بهمن 1401

مدل سازی ساده با اکسل

چگونه می توان به سادگی حقوق هفتگی هر یک از کارکنان یک شرکت را مشخص نمود؟

چگونه می توان مشخص کرد که یک نانوایی چقدر به فروشندگان مواد اولیه بدهکار است؟

چگونه می توان تعداد مشتریان یک باشگاه سلامت تازه تاسیس را، در ده سال دیگر پیش‌بینی کرد؟

چرا باید برای درست نوشتن فرمول های اکسل، عبارت (PEMDAS) را اعمال کرد؟

قهوه خانه محل ما چگونه می تواند دریابد که تغییر در قیمت و هزینه هر فنجان بر سود اثر می گذارد؟

یکی از دوستانم “دنیس فولر” می‌گوید کاربرگ بوم‌نقاشی تحلیلگر است هیچ کدام از ما نمی تواند یک تابلو همچون شب ستاره باران “ونگوگ” را نقاشی کند اما اگر بر اکسل مسلط باشیم می‌توانیم از یک کاربرگ خالی شروع کرده و شاهکار خود را که مجازا تقریباً هر وضعیتی را مدل‌سازی می‌کند بیافرینیم.

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

پاسخ به سوالات این فصل

چگونه می توان به سادگی حقوق هفتگی هر یک از کارکنان یک شرکت را مشخص نمود؟

در فایل Wagestemp.xlsx  تصویر 1-1 ساعت کار و دستمزد ساعتی تعدادی از کارکنان داده شده است میخواهیم پرداختی به هر نفر، جمع کل ساعات کارکرد، کار کنان و متوسط حقوق ساعتی را محاسبه کنیم.

تصویر1-1 محاسبه دستمزد هفتگی

برای محاسبه دستمزد هفتگی “Luka” برای مثال باید مقدار سلول C4 را در مقدار سلول D4 ضرب کنیم فرمول =C4*D4  را در سلول E4 بنویسید.

در ادامه می توانید برای محاسبه حقوق هفتگی ” Terry” در سلول E5 فرمول =C5*D5 را بنویسید اما فرمان کپی در اکسل به شما امکان می دهد که حقوق هفتگی همه کارکنان را حتی اگر تعدادشان یک میلیون نفر باشد (اکسل ۲۰۰۷ و پس از آن یک میلیون و ۴۸ هزار و ۵۷۶ سطر دارند) به یکباره و به سادگی با رفتن به سلول E4  و کپی فرمول آن با ترکیب کلید های Ctrl+C  و سپس انتخاب کل محدوده E5:E11 و فشردن کلیدهای کنترل Ctrl+V برای وارد کردن فرمول به این سلول ها انجام دهید. روش دیگر برای انجام این کار آن است که نشانگر موس را بر روی مربع کوچک سمت راست پایین سلول برده و پس از تغییر شکل به علامت بعلاوه”+” دکمه سمت چپ ماوس را فشرده آن را تا محدوده  E5:E11 بکشید تا فرمول در همه محدوده کپی شود. در هر سلولی که فرمول کپی شود اکسل مقادیر دو سلول سمت چپ ستون را در هم ضرب می کند. برای نمایش فرمول های سلول در اکسل (از اکسل ۲۰۱۳ به بعد) از تابع FORMULATEXT استفاده می کنیم. برای مثال نوشتن فرمول =FORMULATEXT(E4)  در سلول F4  فرمول درون سلول E4  را نشان خواهد داد.

با وارد کردن فرمول =SUM(C4:C11) در سلول C12 آنطور که من در فایل Wagesfinal.xlsx انجام داده‌ام کل ساعات کارکرد کارکنان را در هفته حساب میکنیم و در سلول E14 با وارد کردن فرمول =AVERAGE(E4:E11) میتوان میانگین پرداخت ساعتی به کارکنان را محاسبه کر‍‌‌د.

چگونه می توان مشخص کرد که یک نانوایی چقدر به فروشندگان مواد اولیه بدهکار است؟

در فایل Bakery1temp.xlsx قیمت هر پوند شکر کره و آرد ، که نانوایی به شش فروشنده می پردازد آورده شده است. مقدار پرداختی به هر فروشنده بابت این سه قلم و کل پرداختی نانوایی را محاسبه کنید.

همانطور که در تصویر 2-1 آمده، فرمول =E5*E14 را برای محاسبه پول شکر فروشنده اول استفاده کرده ایم یعنی قیمت هر پوند شکر ضربدر مقدار آن آورده شده است.

تصویر2-1 محاسبه هزینه های نانوایی: قیمت های متفاوت برای هر فروشنده

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

• سلول E23 را انتخاب کرده و با فشردن کلیدهای Ctrl+C و سپس انتخاب محدوده E23:G2 و فشردن کلید های Ctrl+V
• انتخاب سلول E23  و فشردن کلید های Ctrl+C سپس انتخاب محدوده E23:G28  آنگاه فشردن کلید Enter
• انتخاب سلول E23 ، پس از تغییر شکل نشانگر به علامت بعلاوه”+” فرمول را به محدوده F23:G23کشیده و پس از آن از محدوده E23:G23 به محدوده E24:G28 میکشیم.

برای محاسبه جمع کل پرداختی به هر فروشنده در سلول H23 فرمول =SUM(E23:G23) را وارد کرده سپس فرمول را به محدوده H24:H28کپی میکنیم. نوشتن فرمول =SUM(E23:E28) در سلول E29  و کپی کردن آن در محدوده F29:H29 جمع کل پرداختی برای هر قلم را محاسبه میکند.

روش سریع دیگر برای محاسبه جمع کل آن است که محدوده H23:H28  را انتخاب کرده سپس (با فشردن و پایین نگه داشتن کلید Ctrl) محدوده را انتخاب میکنیم پس از آن از سربرگ Home  در نوار ریبون،از بخش Editing  دکمه AutoSum  را کلیک میکنیم (تصویر 3-1).

تصویر 3-1 استفاده از دکمه AutoSum برای محاسبه جمع کل گروهی از اعداد

فرمان AutoSum  محدوده ای را که میخواهید حدس می زند، (دقت کنید این حدس همیشه درست نیست) در این حالت ممکن است پنج ثانیه در وقت شما صرفه جویی شود.

در فایل Bakery2temp.xlsx در تصویر4-1 فرض میکنیم قیمت فروشندگان برای همه اقلام یکسان است مجدداً کل پرداختی به هر فروشنده برای هر کالا و کل پرداختی نانوایی را به هر فروشنده محاسبه میکنیم.

تصویر4-1 محاسبه هزینه های نانوایی: قیمت های مساوی برای فروشندگان

همانند گذشته یک کاربر بی تجربه ممکن است در سلول E23  فرمول E12*E14  را وارد کرده و آن را در محدوده E23:G28کپی کند. متاسفانه در سطر های ۲۴ تا ۲۸ هر دو شماره سطر ۱2 و ۱4 در فرمول تغییر خواهد کرد. ما می خواهیم سطر ۱۴ تغییر کند ولی سطر ۱۲ ثابت بماند زیرا باید قیمت هر قلم را از سطر ۱۲ برداریم، به این منظور یک علامت دلار “$ ” پیش از ۱۲ قرار می دهیم. این روش آدرس دهی مطلق یا قفل کردن سطر نامیده می‌شود. وقتی پیش از شماره سطر علامت دلار بیاید با کپی کردن مقدار آن تغییر نخواهد کرد بنابراین در سلول E23  فرمول =E$12*E14 را وارد می کنیم.

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

چگونه میتوان تعداد مشتریان یک باشگاه سلامت تازه تاسیس را در ده سال دیگر پیش‌بینی کرد؟

پاسخ ما در فایل Chapter1customer.xlsx (تصویر5-1) آمده است. در این مورد باید مدل را از اساس بسازیم. مدل ها برای محاسبه خروجی های دلخواه، به ورودی ها یا فرضیات نیاز دارند. در مدل ساده تخمین مشتریان به سه ورودی نیاز داریم:

• تعداد مشتریان در سال اول
• نرخ چرخش: کسری از مشتریان که هرساله خارج می‌شوند (به استثنای مشتریان جدید)
• تعداد مشتریانی که هر ساله اضافه می‌شوند

تصویر 5-1 پیش بینی مشتریان با ساخت مدلی با ورودیها و خروجیها

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

در سطر های 17-8 مشتریان پایانی هر سال، با افزودن مشتریان جدید به مشتریان موجود و سپس کسر مشتریان قطع ارتباط کرده، محاسبه می شوند. C2:C4  محدوده ورودی های کاربرگ ما هستند. کلید مدل تخمین مشتریان ما روابط زیر است:

– (مشتریان پایانی سال t) = (مشتریان ابتدای سال t) + (مشتریان جدید سال t ) – (مشتریان بریده سال t )

– (مشتریان ابتدای سال 1+t ) = (مشتریان پایانی سال t)

– مشتریان سال اول = مقدار وارد شده در سلول C2

نکته کلیدی دیگر دانستن چیزهایی است که هر سال باید ردیابی کنیم:

• مشتریان آغازین
• مشتریان جدید
• مشتریان بریده
• مشتریان پایان سال

در سلول C8 مشتریان آغازین سال اول را با فرمول =C2 وارد می کنیم، سپس در ستون D مشتریان جدید هر سال را با کپی فرمول =$C$3  یا  =C$3 از سلولD8  به محدوده D9:D17  وارد میکنیم.

توجه داشته باشید قبل از عدد ۳ حتماً باید علامت دلار گنجانده شود، در غیر این صورت هنگام کپی محتوای D8  این عدد تغییر خواهد کرد و نتایج نادرستی به بار می آورد، گنجاندن علامت دلار پیش از حرف C  اختیاری است چون فرمول از این ستون به ستون دیگری کپی نمی شود و ستون بدون تغییر خواهد ماند.

مشتریان بریده هر ماه با ضرب مشتریان آغازین در نرخ چرخش به دست می‌آید. لذا در ستون E تعداد مشتریان بریده را با کپی فرمول =$C$4*C8 یا =C$4*C8  از سلول E8  به محدوده E9:E18  محاسبه میکنیم. توجه کنید که پیش از عدد ۸ علامت دلار قرار نمی دهیم زیرا می‌خواهیم هنگام کپی ۸ به ۹ و ۱۰ و … سطر 8 تغییر یابد.

مشتریان پایانی هر سال با افزودن مشتریان آغازین و مشتریان جدید و کم کردن مشتریان بریده از آنها به دست می آید. فرمول =C8+D8-F8  را که در سلول F8  آمده به محدوده F9:F18 کپی کرده و مشتریان پایانی هر سال را محاسبه میکنیم.

برای سالهای 2 تا ۱۰مشتریان آغازین برابر مشتریان پایانی سال پیش خواهد بود، بنابراین فرمول =F8 واقع در سلول C9 را به محدوده C10:C17 کپی میکنیم. در پایان درمی‌یابیم که پس از ۱۰ سال (نگران اعشار نباشید) باشگاه سلامتی ما حدود ۱۲۷ مشتری خواهد داشت.

خواننده نقادی ممکن است استدلال کند که ما نرخ چرخش و تعداد مشتریان جدید هر سال را به درستی نمیدانیم، این مطلب کاملا صحیح است و بایستی یک تحلیل حساسیت انجام داد تا ببینیم چگونه تغییر در تعداد مشتریان جدید هر سال و نرخ چرخش، در تعداد مشتریان ۱۰ سال آینده تاثیر خواهد گذاشت. در فصل ۱۷ “تحلیل حساسیت با جدول داده ها” خواهید آموخت که چگونه از داده های جدول برای انجام چنین تحلیل حساسیتی استفاده کنیم.

چرا باید برای درست نوشتن فرمول های اکسل، عبارت (PEMDAS) را اعمال کرد؟

فرمول‌های پیچیده اکسل اغلب حاوی عملگرهای پیچیده ریاضی مانند توان ضرب و تقسیم است. اکسل هنگام ارزیابی فرمول ها ترتیب [1](PEMDAS) را پیروی می‌کند:

• ابتدا عملیات درون پرانتز ها انجام می­شود
• عملیات‌های توان از چپ به راست محاسبه می­گردد
• عملیات‌های ضرب و تقسیم از چپ به راست صورت می­پذیرد
• عملیات های جمع و منها از چپ به راست محاسبه می­شود

برای مثال اکسل فرمول =3+6*(5+4)/3-7  را به ترتیب زیر انجام می دهد:

• 3+6*9/3 – 7 (پرانتزها حذف می­شود)
• 3 + 54/3 – 7 (ضرب)
• 3 + 18 – 7 (تقسیم)
• 21 – 7(جمع)
• 14 (منها)

به عنوان یک مثال دیگر فرض کنید می­خواهیم ریشه دوم درصد افزایش فروش سالیانه محصولاتمان را محاسبه کنیم فایل PEMDAStemp.xlsx  را ببینید (تصویر 6-1).

تصویر 6-1 مثالی از قانون PEMDAS

در سلول F4 فرمول =((E4-D4)/D4)^0.5 را که درست است، وارد کرده و آن را در محدوده F5:F6کپی کرده‌ایم. این فرمول باعث می‌شود که اکسل ابتدا درصد افزایش فروش داخل پرانتز را (که به صورت کسری نوشته شده) محاسبه کرده (.5)  ، سپس ریشه دوم آن را حساب کند. جواب نهایی .707  (ریشه دوم یا جذر .5) خواهد بود که درست است. توجه کنید علامت ^ واقع بر روی دکمه ۶ صفحه کلید برای به توان رساندن استفاده می‌شود.

در سلول G4 فرمول نادرست یعنی =(E4-D4)/D4^.5 را وارد کرده ایم این فرمول ابتدا E4-D4 = 50  را محاسبه نموده و سپس جذر آن را گرفته و در پایان نتیجه نهایی ما 50/10 = 5 خواهد بود که خطاست.

قهوه خانه محل ما چگونه می تواند دریابد که تغییر در قیمت و هزینه هر فنجان بر سود اثر می گذارد؟

نکته کلیدی در دریافتن اثر تغییر قیمت بر سود، تخمین صحیح منحنی تقاضا است. منحنی تقاضا نشان می‌دهد که چگونه تغییر قیمت محصول، بر تقاضای آن اثر می‌گذارد. بیایید فرض کنیم که تقاضای روزانه برای قهوه با فرمول 100-15*Price  داده شده است(برای بحث بیشتر درباره منحنی تقاضا فصول 87 تا 89 را ببینید). در فایل Coffee.xlsx  (تصویر7-)1 نشان داده شده که سود روزانه چگونه با تغییر هزینه و قیمت هر فنجان قهوه، تغییر می کند.

تصویر 7-1 وابستگی تقاضا به قیمت و هزینه

فرض می کنیم که هزینه هر فنجان قهوه بین 0.5 تا ۲ دلار و قیمت فروش هم بین ۲ تا ۵ دلار است برای محاسبه سود هر ترکیب قیمت/هزینه تنها نیاز ، گذاشتن فرمول =($F$5-$F$4*F$9)*(F$9-$E10)   در سلول F10  و کپی آن در محدوده F10:L13 می باشد.

• آدرس سلولهای F5 و F4   در فرمول مطلق هستند چون نمی‌خواهیم سطر یا ستون با کپی تغییر کند.
• آدرس سلول F9 قیمت به علامت دلار (قفل کننده سطر) نیاز دارد زیرا همیشه قیمت از سطر 9 استخراج می شود.
• آدرس سلول E10 هزینه به علامت دلار (قفل کننده ستون) نیاز دارد زیرا همیشه هزینه از ستون E استخراج می شود.

برای مثال در می­یابیم که اگر هزینه 1.5 دلار و فروش ۴ دلار باشد سود ما ۱۰۰ دلار خواهد بود:

(100-4*15)*(4-1.5) = $100

توجه کنید برای هر هزینه، قیمتی که سود را بیشینه می کند پررنگ شده است در فصل 2۴ با عنوان “Conditional formatting”  قالب بندی شرطی خواهید دید که چگونه از این ابزار برای پررنگ کردن به روش جالبی استفاده می شود.

مسائل:

من در کلاس ۵ تکلیف به شاگردان داده ام که هر کدام ۲۵ نمره دارد و سه امتحان از آنها خواهم گرفت که هرکدام ۱۰۰ نمره دارد. اکنون می خواهم نمره نهایی دانش آموزان را با این فرض که ۷۵ درصد وزن به نمرات امتحان و ۲۵ درصد به تکالیف داده ‌شود را محاسبه کنیم، کاربرگی برای محاسبه نمره نهایی بسازید که به شما اجازه دهد وزن نمره امتحان را تغییر دهید.

توده بدنی شخص (BMI)  با فرمول BMI=703*Weight/Height محاسبه می‌شود کاربرگی برای محاسبه توده بدنی افراد بسازید.

سری فیبوناچی با این فرض ها تعریف می شود F0 = 0, F1 = 1 و برای هر n  بزرگتر از 1  FN+1 = FN + FN-1

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

اثر مشهور پروانه ای می گوید اگر پروانه ای در هائیتی بال بزند این تغییر کوچک ممکن است باعث طوفانی در تگزاس شود. فرض کنید هوا در زمان t همیشه بین 0 و 1 بوده و رابطه xt+1=4*xt*(1-xt) حاکم است. برای مقادیر xt = 0.3  و xt=.3000001 مقادیر x1, x2, … x50 را محاسبه کرده و نشان دهید که این محاسبه چگونه اثر پروانه ای را ترسیم می کند.

دریاچه ای با 12230 ماهی پر شده است، هر سال نسبت زاد و ولد 1.2 و مرگ و میر 0.7 به ازای هر ماهیست. نشان دهید که اگر هر سال ۶۱۱۵ ماهی صید شود تعداد ماهی ها در دریاچه ثابت می‌ماند.

شاخص جینی معمولاً به عنوان معیار نابرابری درآمد ملی به کار می رود . اگر درآمد n نفر به صورت     (x1= smallest income, xn = largest income)  ثبت شود، شاخص جینی با فرمول زیر تعریف می شود:

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

[1] به ترتیب حروف: پرانتر توان ضرب تقسیم جمع منها