بایگانی برچسب برای: excel

آموزش فرمول نویسی در اکسل

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

• پرکردن خودکار(بطور کامل)
• فرمول نویسی بین کاربرگ ها

پرکردن خودکار

در ادامه آموزش فرمول نویسی در اکسل همانند تطبیق و کپی فرمولها در اکسل با استفاده از مربع کوچک گوشه سلولها میتوان متن و اعداد را نیز تطبیق یا ادامه داد. فرض کنید میخواهیم در سلول A1 تا A10 اعداد ۱ تا ۱۰ را بنویسیم. برای این کار میتوانیم تک تک در سلولها این اعداد را نوشت که کاری وقتگیر است. اما راه سریعتری برای این کار وجود دارد. برای این کار میتوان در سلول A1 عدد ۱ و در سلول A2 عدد ۲ را نوشت، سپس این دو سلول را با هم انتخاب کرد ،بصورت زیر.

حال اگر ماوس را روی مربع کوچک گوشه سلول A2 ببریم خواهیم دید شکل ماوس به صورت + تغییر مییابد. در این زمان باید کلیک کنیم و کلیک را نگه داریم و ماوس را تا سلول A10 بکشیم و رها کنیم.

مثال

جدول ارزش مرکب یک ریال را برای یک تا ده سال و با نرخ بهره سالانه ۱ تا ۵ درصد در سال را تشکیل دهید. نتیجه حاصل به شکل زیر خواهد بود.

در ابتدا شماره دورهها را مینویسیم. برای این کار در سلول A1 عبارت “دوره”، در سلول A2 عدد ۱ و در سلول A3 عدد ۲ را مینویسیم. حالا دو سلول A2 و A3 را با هم انتخاب میکنیم و سپس روی مربع کوچک سلول A3 کلیک کرده و تا سلول A11 میکشیم و رها میکنیم.

حالا برای نوشتن درصد ها در سطر ۱ ابتدا در سلول B1 عدد ۰٫۰۱ و در سلول C1 عدد ۰٫۰۱ را مینویسیم و سپس دو سلول B1 و C1 را با هم انتخاب کرده و سپس روی مربع کوچک گوشه سلول C1 کلیک کرده و نگه داشته و تا سلول F1 میکشیم و رها میکنیم.

تا اینجای کار شکل جدول مورد نظر بصورت زیر است.

حالا نوبت نوشتن فرمول ارزش مرکب است. فرمول ارزش مرکب بصورت زیر است:

(۱+i)ˆn

در سلول B2 پس از علامت “=” پرانتز باز کرده و عبارت “(۱+B1)” را مینویسیم و روی نام سلول B1 یک بار کلید F4 را فشار میدهیم تا بصورت =(۱+B$1) تغییر یابد و در بیرون پرانتز بعد از علامت توان “^” روی سلول A2 کلیک کرده و سه بار کلید F4 را بفشارید تا فرمول بصورت

=(۱+B$1)^$A2

تغییر یابد و در پایان کلید ENTER را بفشارید. تا اینجای کار شکل زیر حاصل شده است.

حالا یکبار روی مربع کوچک سلول B2 کلیک کرده و نگه داشته و تا سلول F2 میکشیم و کلیک را رها میکنیم.

و حالا در حالی که ۵ سلول B2 تا F2 با هم انتخاب شدهاند، روی مربع سلول F2 کلیک کرده و نگه میداریم و تا سطر ۱۱ میکشیم و رها میکنیم. در پایان شکل حاصل بصورت زیر است.

مثال
فروش فصلی یک شرکت برای سه سال متوالی به شرح زیر است.

در ادامه آموزش فرمول نویسی در اکسل میخواهیم جمع فروش سالانه را در سطر ۶ و میانگین فروش هر فصل را در ستون E محاسبه کنیم. برای جمع فروش هر سال ابتدا برای سال ۱۳۸۹ روی سلول B6 کلیک میکنیم و فرمول B2+B3+B4+B5= را مینویسیم و کلید ENTER را میفشاریم.

حالا برای محاسبه جمع فروش دو سال دیگر کافیست روی مربع کوچک گوشه سلول B6 کلیک کنیم و نگهداریم و تا سلول D6 بکشیم و کلیک را رها کنیم. اعداد حاصل بصورت زیر نمایش داده میشوند.

برای محاسبه میانگین فروش فصل بهار در سه سال گذشته در سلول E2 فرمول (B1+C1+D1)/3)= را مینویسیم و کلید ENTER را میفشاریم.

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

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

مثال

جدول استهلاک یک دارایی به روش خط مستقیم با بهای تمام شده ۵۰۰۰۰۰۰ ریال و عمر مفید ۴ سال با ارزش اسقاط ۲۰۰۰۰۰۰ ریال را تشکیل دهید. دقت کنید جدول حاصل بصورت زیر خواهد بود.

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

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

در ادامه آموزش فرمول نویسی در اکسل بنابراین فرمول محاسبه استهلاک سال اول بصورت  ۴/(۲۰۰۰۰۰۰۰-۵۰۰۰۰۰۰)= که اگر به جای عددها آدرس سلول آنها را قرار دهیم (فرمول را بصورت نسبی بنویسیم)، فرمول این سلول بصورت B1-B3)/B2)= خواهد بود. چون فرمول محاسبه استهلاک برای همه سالها یکسان است، پس میتوان این فرمول را با ماوس به پایین کشید تا اکسل بطور خودکار این فرمول را برای سایر سالها نیز تطبیق دهد. اما قبل از این کار باید عناصر این فرمول را ثابت کرد تا با کپی فرمول به سلولهای پایینی، فرمول تغییر نکند. برای این کار به این صورت فرمول سلول B6 را مینویسیم.

ابتدا علامت “=” را قرار میدهیم و سپس یک پرانتز باز کرده و روی سلول B1 کلیک میکنیم، در این لحظه برای ثابت کردن این سلول در فرمول یک بار کلید F4 را میفشاریم.

حالا علامت “-” را قرار داده و روی سلول B3 کلیک کرده و سپس کلید F4 را برای ثابت شدن آن میفشاریم و پرانتز را میبندیم.

بعد از پرانتز هم علامت کسر “/” را قرار داده و روی سلول B2 کلیک کرده و کلید F4 و در پایان کلید ENTER را میفشاریم.

تا اینجا تنها استهلاک سال اول را محاسبه کردهایم. برای کپی این فرمول به سالها بعد کافیست روی مربع کوچک کادر سلول B6 دبل کلیک کنیم.

برای محاسبه استهلاک انباشته دارایی در سلول C6 چون استهلاک انباشته سال اول برابر با هزینه استهلاک همین سال است فرمول B6= را قرار داده و کلید ENTER را میفشاریم.

برای استهلاک انباشته سالهای بعد در سلول C7 فرمول C6+B7= را نوشته و کلید ENTER را میفشاریم .فرمول محاسبه استهلاک انباشته هر سال برابر است با هزینه استهلاک همان سال بعلاوه استهلاک انباشته سالهای قبل. بنابراین در سلول C7 هزینه استهلاک سال دوم (B7) و استهلاک انباشته سالهای قبل (C6) را با هم جمع کرده ایم.

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

برای نمونه فرمول موجود در سلول C9 بصورت C8+B9= است که به معنای جمع هزینه استهلاک سال چهارم (B9) و استهلاک انباشته سه سال گذشته(C8)میباشد.

حال برای محاسبه ارزش دفتری هر سال در سول D6 فرمول

=$B$1-C6

را مینویسیم و کلید ENTER را میفشاریم. ارزش دفتری هر سال برابر است با بهای تمام شده دارایی(B1، چون در همه سالها باید ثابت باشد روی آن کلید F4 را فشردهایم و بصورت $B$1 تغییر پیدا کرده است) منهای استهلاک انباشته همان سال (برای سال اول سلول C6 و چون هر سال باید تغییر کند آن را ثابت نمیکنیم.)

حالا با یک دبل کلیک روی مربع کوچک سلول C6 جدول ما کامل میشود.

مثال

میخواهیم جدول ارزش مرکب برای یک ریال با نرخهای بهره ۱ تا ۱۰ درصد و در دورهای ۱ تا ۱۵ سال را تشکیل دهیم. برای این کار ابتدا عناوین سطر و ستون جدول را بصورت زیر در اکسل تشکیل میدهیم.

حالا در سلول B2 برای محاسبه ارزش مرکب سال اول و با نرخ بهره ۱ درصد در سال، باید فرمول

=(۱+B1)^A2

را بنویسیم. اما میخواهیم این فرمول را برای همه سلولهای این جدول کپی کنیم. برای اینکه در هنگام جابجایی فرمول آدرس سلولها بهم نریزد باید دو سلول A2 و B1 را ثابت کرد. اما این بار بجای اینکه یک بار کلید F4 را روی هر کدام از آنها فشار دهیم، برای سلول B1 دوبار کلید F4 را فشار دهید تا آدرس این سلول بصورت B$1 تغییر یابد. این کار باعث میشود در هنگام کپی فرمول به پایین برای دورههای دیگر، همواره این قسمت سطر یک را نشان دهد و همچنین در هنگام کپی فرمول به سمت راست برای نرخهای بهره دیگر، ستونها تغییر کند.

برای سلول A2 سه بار کلید F4 را بفشارید تا به شکل A2$ تغییر یابد. این کار باعث میشود تا در هنگام کپی فرمول به سمت راست، همواره فرمول در قسمت توان در ستون A ثابت باشد و در هنگام کپی به پایین، بدلیل ثابت نبودن عدد این سلول، اعداد توان تغییر کند.

در پایان کلید ENTER را بفشارید. حالا در سلول B2 ارزش مرکب یک ریال در یک سال بعد با نرخ بهره ۱ درصد در سال نمایش داده شده است.

حالا برای محاسبه سایر سلولها، روی مربع سلول B2 کلیک کرده و نگه میداریم و تا سلول K2 میکشیم و رها میکنیم.

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

مثال

در یک شرکت تولیدی، در دایره مشترک آن که دایره اول تولید است، سه محصول اصلی با نامهای الف، ب و ج تولید میشود. تعداد تولید هر محصول بترتیب ۲۵۰۰، ۳۵۰۰ و ۴۵۰۰ واحد و هزینه این دایره مشترک ۱۲۰۰۰۰۰۰۰ ریال است. سایر اطلاعات بشرح زیر در اکسل موجود است.

میخواهیم جدول فوق را تکمیل و بهای تمام شده هر واحد را محاسبه کنیم. برای این کار ابتدا در سلول D2 نسبت تعداد هر محصول به کل محصولات را محاسبه میکنیم. برای این کار در این سلول فرمول

=B2/($B$2+$B$3+$B$4)

ENTER را نوشته و کلید را میفشاریم.

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

حالا در ستون “سهم از هزینه مشترک” باید برای هر محصول عدد ۱۲۰۰۰۰۰۰ را در نسبت تولید هر محصول ضرب کنیم. برای این کار در سلول E2 فرمول =۱۲۰۰۰۰۰۰*D2 را نوشته و کلید ENTER را میفشاریم. سپس با دبل کلیک بر مربع توپر گوشه سلول E2 سهم هر محصول از هزینه مشترک محاسبه میشود.

برای محاسبه بهای تمام شده هر محصول باید “مبلغ پرداخت اضافی” و “سهم از هزینه مشترک” هر محصول را جمع کرده و در ستون F وارد کنیم.

برای محاسبه بهای تمام شده هر واحد کافیست بهای تمام شده کل محصول را بر تعداد تولید آن تقسیم کرد. برای این کار در سلول G2 فرمول F2/B2= را نوشته و ENTER را میفشاریم. سپس با دبل کلیک بر مربع توپر سلول G2 بهای تمام شده همه محصولات محاسبه میشود.

فرمول موجود در این سلولها بصورت زیر است.

فرمول نویسی بین کاربرگ ها

در ادامه آموزش فرمول نویسی در اکسل گاهی اوقات میخواهیم اطلاعات و دادهها در اکسل در یک کاربرگ و تجزیه تحلیلها، محاسبات و فرمولها در کاربرگی دیگر وارد شوند. فرض کنید مبلغ ۵۰۰۰۰۰۰ ریال را در حسابی در بانک با نرخ بهره سالانه ۷ درصد سپردهگذاری کردهاید. میخواهید بدانید در پایان دوره ۴ ساله سرمایه گذاری موجودی حساب شما با احتساب بهره متعلقه به آن، چه مقدار خواهد بود. اطلاعات سوال را در کاربرگ اول (SHEET1) بصورت زیر وارد کرده ایم.

حالا میخواهیم فرمول محاسبه ارزش آتی این سرمایه گذاری در سلول A1 در SHEET2 وارد شود و نتیجه در کاربرگ دوم نمایش داده شود. برای این کار ابتدا به کاربرگ دوم میرویم و روی سلول A1 کلیک میکنیم تا فعال شود. سپس علامت “=” را قرار میدهیم. حالا برای کلیک بر سلول حاوی مبلغ وام (سلول B1 در کاربرگ اول) ابتدا روی نام کاربرگ اول در پایین صفحه کلیک میکنیم تا به کاربرگ اول منتقل شویم، سپس روی سلول B1 کلیک میکنیم.

مشاهده میکنید نام سلول B1 در کاربرگ اول بصورت SHEET1!B1 نمایش داده شده است. حالا ادامه فرمول را در نوار فرمول مینویسیم. فرمول نهایی بصورت زیر است.

=Sheet1!B1*(1+Sheet1!B2)^Sheet1!B3

اگر این فرمول را در کاربرگ اول مینوشتیم بصورت B1*(1+B2)^B3= نمایش داده میشد.

امیدواریم از این مطلب در سایت صنایع سافت که درباره در ادامه آموزش فرمول نویسی اکسل بود، لذت برده باشید.نظرات خودتون رو واسه ما کامنت بزارین تا ما بتونیم هر چه بیشتر از اونها واسه بهتر شدنه تیممون استفاده کنیم.

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

احمد جعفری

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

آموزش اکسل برای مهندسان صنایع

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

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

در این آموزش به موارد زیر خواهیم پرداخت :

1. حذف یک سطر
2. حذف یک سلول
3. تغییر اندازه سلول ها
4. مدیریت کاربرگ ها
5. انتخاب همزمان چند کاربرگ
6. تغییر نوع آدرس دهی سلول ها

حذف یک سطر

حذف سطرها همانند حدف ستونها است. برای حذف یک سطر روی شماره سطر مورد نظر راست کلیک کرده و گزینه DELETE را انتخاب میکنیم.

حذف یک سلول

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

در ادامه آموزش اکسل برای مهندسان صنایع مشخص است که سلول B3 اضافی است. برای اصلاح کردن اطلاعات و برای اینکه میزان سن و تحصیلات روبروی عناوین خود به شکل صحیح قرار گیرند میتوان دو سلول B4 و B5 را با بالا انتقال داد و یا اینکه سلول B3 را حذف کرد. برای حذف سلول B3 روی آن راست کلیک کنید و گزینه DELETE را انتخاب کنید.

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

با انتخاب گزینه SHIFT CELLS UP سلولهای زیرین سلول B3 به بالا منتقل میشوند.

تغییر اندازه سلول ها

فرض کنید میخواهیم در سلول C1 عبارت “بهای تمام شده یک محصول” را بنویسیم .خواهیم دید بخشی از متن از کادر سلول C1 بیرون زده است و در کادر سلول B1 نمایش داده میشود.

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

همچنین برای تغییر اندازه خودکار توسط اکسل، میتوانید بجای به چپ و راست کشیدن خط ستون، روی خط دبل کلیک کنید.

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

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

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

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

مدیریت کاربرگ ها

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

برای ایجاد کاربرگ جدید کافیست روی علامت + کلیک کنید.

خواهید دید کاربرگ جدیدی با نام SHEET2 ایجاد شده است.

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

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

انتخاب همزمان چند کاربرگ

گاهی اوقات لازم است چند کاربرگ با شکل و فرمت و جداول یکسان داشته باشیم. فرض کنید میخواهیم جدول فروش ماهانه زیر را برای ۴ سال در ۴ کاربرگ متفاوت به یک شکل داشته باشیم.

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

در این زمان، هر تغییری که روی هر کدام از سلولهای یک کاربرگ ایجاد کنید، در سایر کاربرگها و در سلولهای متناظر ایجاد میشود. حالا جدول مورد نظر را برای اولین کاربرگ ایجاد میکنیم.

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

تغییر نوع آدرس دهی سلول ها

همانطور که قبلا گفته شد آدرس سلول ترکیبی از نام ستون و شماره سطر آن است. به این صورت که سلولی که در سطر ۴ و در ستون F قرار دارد با نام F4 شناخته میشود. اما این نوع نامگذاری در ستونهایی که نام آنها ترکیبی از دو یا سه حرف لاتین است کمی مشکل میشود. نوع دیگری از نامگذاری در اکسل وجود دارد که ستونها را نیز با شماره نامگذاری میکند. نحوه نامگذاری سلولها در این سیستم بصورت زیر است.

(شماره ستون)C (شماره سطر)R مثلا سلول F4 در این نوع نامگذاری بصورت R4C6 شناخته میشود.

برای استفاده از این سیستم نامگذاری در زبانه FILE گزینه OPTIONS را انتخاب کنید. در کادر باز شده در قسمت FORMULAS در بخش WORKING WITH FORMULAS گزینهR1C1 REFERENCE STYLE را تیک بزنید.

برای نمونه سلول C4 در این سیستم بصورت R4C3 نمایش داده میشود.

امیدواریم از این مطلب در سایت صنایع سافت که درباره آموزش اکسل برای مهندسان صنایع بود، لذت برده باشید.نظرات خودتون رو واسه ما کامنت بزارین تا ما بتونیم هر چه بیشتر از اونها واسه بهتر شدنه تیممون استفاده کنیم.

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

احمد جعفری

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