تمرین عملی اکسل
تمرین: مدیریت فروش محصولات
فرض کنید یک فروشگاه سه محصول مختلف میفروشد و دادههای فروش ماهانهی آنها به شرح زیر است:
| ماه | kala1 (تعداد) | kala2 (تعداد) | kala3 (تعداد) |
| ژانویه | 120 | 80 | 150 |
| فوریه | 100 | 90 | 200 |
| مارس | 140 | 110 | 170 |
| آوریل | 130 | 100 | 180 |
اهداف تمرین:
- محاسبه مجموع فروش هر محصول.
- محاسبه میانگین فروش ماهانه برای هر محصول.
- شناسایی پرفروشترین محصول هر ماه.
- ایجاد نمودار فروش ماهانه برای هر محصول.
گام ۱: ورود دادهها
ابتدا دادههای جدول بالا را در اکسل وارد کنید.
گام ۲: محاسبه مجموع فروش هر محصول
- در سلول E2، فرمول زیر را وارد کنید:
=SUM(B2:B5)
این فرمول مجموع فروش kala1 را محاسبه میکند. آن را به سلولهای دیگر E3 و E4 کپی کنید تا مجموع فروش برای kala2 و ۳ نیز محاسبه شود.
گام ۳: محاسبه میانگین فروش ماهانه
- در سلول F2، فرمول زیر را وارد کنید:
=AVERAGE(B2:B5)
این فرمول میانگین فروش kala1 را محاسبه میکند. آن را به سلولهای دیگر F3 و F4 کپی کنید تا میانگین فروش برای kala2 و ۳ نیز محاسبه شود.
گام ۴: شناسایی پرفروشترین محصول هر ماه
- در سلول G2، فرمول زیر را وارد کنید:
=IF(MAX(B2:D2)=B2,”kala1″,IF(MAX(B2:D2)=C2,”kala2″,”kala3″))
این فرمول بررسی میکند که کدام محصول بیشترین فروش را در ژانویه داشته است. آن را به سایر سلولهای ستون G کپی کنید تا برای سایر ماهها نیز محاسبه شود.
گام ۵: ایجاد نمودار فروش ماهانه
- دادههای ستونهای A تا D (ماه و تعداد فروش محصولات) را انتخاب کنید.
- به بخش Insert بروید و نمودار Column Chart یا Line Chart را انتخاب کنید.
- نمودار را به دلخواه ویرایش کنید (اضافه کردن عنوان، رنگها و …).
\
چند تمرین دیگر برای تقویت مهارت کار با اکسل
تمرین ۱: مدیریت هزینهها و درآمدها
دادهها:
| ماه | هزینه (تومان) | درآمد (تومان) |
| ژانویه | 5000000 | 7000000 |
| فوریه | 4000000 | 6000000 |
| مارس | 4500000 | 7500000 |
| آوریل | 6000000 | 8000000 |
اهداف تمرین:
- محاسبه سود خالص هر ماه (درآمد – هزینه) در ستون جدید.
- شناسایی ماهی که بیشترین سود خالص را داشته است.
- محاسبه مجموع هزینهها و درآمدها در پایان جدول.
- ایجاد نمودار خطی برای نمایش سود خالص ماهانه.
گامها:
- در ستون D، فرمول زیر را وارد کنید:
=C2-B2
و آن را برای سایر ماهها کپی کنید.
- برای شناسایی ماه با بیشترین سود خالص، از تابع MAX و MATCH استفاده کنید:
=INDEX(A2:A5,MATCH(MAX(D2:D5),D2:D5,0))
- مجموع هزینهها و درآمدها را با تابع SUM در زیر ستونها محاسبه کنید.
- نمودار خطی برای ستونهای A و D ایجاد کنید.
تمرین ۲: نمرات دانشآموزان
دادهها:
| نام دانشآموز | ریاضی | علوم | زبان انگلیسی | معدل |
| علی | 18 | 15 | 17 | |
| رضا | 12 | 14 | 16 | |
| سارا | 20 | 19 | 18 | |
| مریم | 14 | 13 | 15 |
اهداف تمرین:
- محاسبه معدل هر دانشآموز (میانگین نمرات در ستون معدل).
- شناسایی دانشآموزی که بالاترین معدل را دارد.
- هایلایت کردن نمرات زیر 12 با استفاده از Conditional Formatting
گامها:
- در ستون E، فرمول زیر را وارد کنید:
=AVERAGE(B2:D2)
و آن را برای سایر دانشآموزان کپی کنید.
- برای شناسایی بالاترین معدل:
=INDEX(A2:A5,MATCH(MAX(E2:E5),E2:E5,0))
- از منوی Conditional Formatting گزینه Highlight Cell Rules را انتخاب کرده و نمرات زیر 12 را قرمز کنید.
تمرین ۳: مدیریت انبار
دادهها:
| محصول | تعداد موجودی | قیمت واحد (تومان) | ارزش کل (تومان) |
| kala1 | 50 | 20000 | |
| kala2 | 30 | 50000 | |
| kala3 | 100 | 15000 | |
| محصول ۴ | 20 | 70000 |
اهداف تمرین:
- محاسبه ارزش کل موجودی برای هر محصول (تعداد × قیمت واحد).
- محاسبه مجموع ارزش کل موجودی انبار.
- شناسایی محصولی که بیشترین ارزش را دارد.
گامها:
- در ستون D، فرمول زیر را وارد کنید:
=B2*C2
و آن را برای سایر ردیفها کپی کنید.
- برای مجموع ارزش کل:
=SUM(D2:D5)
- برای شناسایی محصول با بیشترین ارزش:
=INDEX(A2:A5,MATCH(MAX(D2:D5),D2:D5,0))
تمرین ۴: تحلیل دادههای فروش
دادهها:
| ماه | محصول A | محصول B | محصول C |
| ژانویه | 100 | 150 | 200 |
| فوریه | 120 | 130 | 180 |
| مارس | 110 | 140 | 220 |
| آوریل | 130 | 160 | 210 |
اهداف تمرین:
- محاسبه مجموع فروش هر ماه.
- محاسبه درصد سهم هر محصول از کل فروش هر ماه.
- ایجاد نمودار دایرهای برای نمایش درصد سهم محصولات در ماه ژانویه.
گامها:
- در ستون D، مجموع فروش هر ماه را محاسبه کنید:
=SUM(B2:D2)
- در ستونهای جدیدE، F، G، درصد سهم هر محصول را محاسبه کنید:
=B2/$D2*100
برای سایر ستونها نیز این فرمول را اعمال کنید.
- برای نمودار دایرهای، دادههای ماه ژانویه را انتخاب کنید و از Insert > Pie Chart استفاده کنید.
تمرین ۵: تحلیل زمانی دادهها
دادهها:
| تاریخ | فروش (تومان) |
| 2025-01-01 | 5000000 |
| 2025-01-02 | 6000000 |
| 2025-01-03 | 5500000 |
| 2025-01-04 | 7000000 |
اهداف تمرین:
- محاسبه مجموع فروش.
- محاسبه میانگین فروش روزانه.
- ایجاد نمودار خطی برای نمایش روند فروش.
گامها:
- مجموع فروش را با تابع SUM محاسبه کنید.
- میانگین فروش را با تابع AVERAGE به دست آورید.
- نمودار خطی برای ستونهای A و B ایجاد کنید.
- ایجاد یک شیت جدید
کلیک روی تب + در پایین صفحه یا فشار دادن Shift+F11. - وارد کردن داده به یک سلول
انتخاب سلول –> تایپ داده مورد نظر. - انتخاب چندین سلول
کلیک و کشیدن موس یا استفاده از Shift + جهتنما. - کپی کردن دادهها
انتخاب سلولها –> Ctrl+C –> انتخاب محل جدید –> Ctrl+V. - انتقال دادهها به محل جدید
انتخاب سلولها –> Ctrl+X –> انتخاب محل جدید –> Ctrl+V. - حذف دادهها از یک سلول
انتخاب سلول –> Delete. - استفاده از فرمول جمع (SUM)
انتخاب سلول –> تایپ =SUM(A1:A5) –> Enter. - استفاده از فرمول میانگین (AVERAGE)
انتخاب سلول –> تایپ =AVERAGE(A1:A5) –> Enter. - استفاده از فرمول شمارش (COUNT)
انتخاب سلول –> تایپ =COUNT(A1:A5) –> Enter. - ایجاد یک جدول (Table)
انتخاب دادهها –> انتخاب تب Insert –> Table. - ایجاد نمودار (Chart)
انتخاب دادهها –> انتخاب تب Insert –> انتخاب نوع نمودار. - افزودن فیلتر به دادهها
انتخاب دادهها –> انتخاب تب Data –> Filter. - مرتبسازی دادهها به ترتیب صعودی یا نزولی
انتخاب دادهها –> انتخاب تب Data –> Sort –> انتخاب گزینه مرتبسازی. - یافتن دادهها با استفاده از جستجو
فشار دادن Ctrl+F –> تایپ کلمه یا عدد مورد نظر. - فرمت کردن سلولها به عنوان درصد
انتخاب سلول –> راستکلیک –> Format Cells –> Percentage. - ایجاد یک سلول با تاریخ جاری
انتخاب سلول –> تایپ =TODAY() –> Enter. - ایجاد یک سلول با ساعت جاری
انتخاب سلول –> تایپ =NOW() –> Enter. - اعمال فرمت پولی (Currency) به سلولها
انتخاب سلول –> راستکلیک –> Format Cells –> Currency. - استفاده از شرطی (Conditional Formatting)
انتخاب دادهها –> انتخاب تب Home –> Conditional Formatting –> انتخاب نوع فرمت. - قرار دادن رنگ پسزمینه به سلولها
انتخاب سلولها –> راستکلیک –> Format Cells –> Fill –> انتخاب رنگ. - چرخاندن متن در یک سلول
انتخاب سلول –> راستکلیک –> Format Cells –> Alignment –> انتخاب Rotate Text. - محاسبه بیشترین مقدار (MAX)
انتخاب سلول –> تایپ =MAX(A1:A5) –> Enter. - فرمول جمع
=A2+A3
فرمول معدل:
=(A2+A3+A4)/3
- محاسبه کمترین مقدار (MIN)
انتخاب سلول –> تایپ =MIN(A1:A5) –> Enter. - استفاده از توابع IF
انتخاب سلول –> تایپ =IF(A1>10, “Yes”, “No”) –> Enter. - مخفی کردن ستون یا ردیف
راستکلیک روی شماره ستون یا ردیف –> Hide. - افزودن حاشیه به سلولها
انتخاب سلولها –> راستکلیک –> Format Cells –> Border –> انتخاب نوع حاشیه. - ایجاد یک پیوند (Hyperlink)
انتخاب سلول –> راستکلیک –> Hyperlink –> وارد کردن URL. - ایجاد یک فیلد جستجو با استفاده از VLOOKUP
انتخاب سلول –> تایپ =VLOOKUP(A1, B1:C5, 2, FALSE) –> Enter. - استفاده از فرمول CONCATENATE برای ترکیب متن
انتخاب سلول –> تایپ =CONCATENATE(A1, ” “, B1) –> Enter. - درج یک کاما در فرمول برای تعداد هزارگان
انتخاب سلول –> راستکلیک –> Format Cells –> Number –> انتخاب Thousands Separator. - تغییر عرض ستونها
انتخاب ستونها –> راستکلیک –> Column Width –> وارد کردن اندازه جدید. - تغییر ارتفاع ردیفها
انتخاب ردیفها –> راستکلیک –> Row Height –> وارد کردن ارتفاع جدید. - قرار دادن شماره ردیفها در یک ستون
تایپ=ROW()در سلول اول و کشیدن آن به پایین. - ایجاد یک سلول با محاسبه مجموع (SUM) از سلولهای مختلف
انتخاب سلول –> تایپ=SUM(A1, B2, C3)–> Enter. - استفاده از تابع COUNTA برای شمارش تعداد سلولهای غیرخالی
انتخاب سلول –> تایپ=COUNTA(A1:A5)–> Enter. - انتخاب سلول با استفاده از Go To
فشار دادن Ctrl+G –> وارد کردن مرجع سلول (مثلا A1) –> Enter. - مخفی کردن شیتها
راستکلیک روی نام شیت –> Hide. - نمایش شیتهای مخفی شده
راستکلیک روی هر شیت –> Unhide –> انتخاب شیت مخفی. - محاسبه تعداد کاراکتر در یک سلول با استفاده از LEN
انتخاب سلول –> تایپ=LEN(A1)–> Enter. - ایجاد تاریخ و زمان در یک سلول
تایپ=NOW()در سلول –> Enter. - استفاده از TEXT برای تغییر فرمت تاریخ
تایپ=TEXT(A1, "mm/dd/yyyy")–> Enter. - جدا کردن متن در یک سلول با استفاده از TEXT TO COLUMNS
انتخاب سلول –> انتخاب تب Data –> Text to Columns –> انتخاب Delimited. - ساخت یک نمودار با دادههای انتخابی
انتخاب دادهها –> انتخاب تب Insert –> انتخاب نوع نمودار. - درج یک خط افقی در بین دادهها
انتخاب یک سلول –> تایپ=REPT("-", 50)–> Enter. - ترتیب دادهها به صورت صعودی یا نزولی
انتخاب دادهها –> انتخاب تب Data –> Sort –> انتخاب گزینه مورد نظر. - استفاده از تابع IF برای مقایسه مقادیر
تایپ=IF(A1>10, "Yes", "No")–> Enter. - برجسته کردن سلولهایی که شرایط خاصی دارند (Conditional Formatting)
انتخاب دادهها –> انتخاب تب Home –> Conditional Formatting –> انتخاب نوع شرط. - استفاده از فرمول SUBTOTAL برای محاسبات با فیلترها
انتخاب سلول –> تایپ=SUBTOTAL(9, A1:A5)–> Enter. - پیدا کردن اولین یا آخرین سلول در یک ستون با استفاده از INDEX
تایپ=INDEX(A1:A5, 1)برای اولین یا=INDEX(A1:A5, COUNTA(A1:A5))برای آخرین. - ایجاد یک فیلتر برای دادهها
انتخاب دادهها –> انتخاب تب Data –> Filter –> فعال کردن فیلتر.
