با سلام
بسیار خوشحال و خرسندیم که برای افزایش مهارت خود سرمایه گذاری کرده اید. و راه پیشرفت و یادگیری این مهارت را انتخاب کرده اید.
براساس جمله معروف :
بهترین سرمایه گذاری، سرمایه گذاری بر روی خود است.
همان طور که می دانید یادگیری، در دو بخش آموزش و تمرین خلاصه می شود.
شما با حضور در کلاس های حضوری یا آنلاین اکسل باز بخش آموزش را پشت سر می گذارید. اما اگر این آموزش بدون تمرین باشد پس از مدتی از ذهن شما پاک خواهد شد و عملا این دوره ها برای شما بازدهی نخواهد داشت.
بنابراین با تمرین ها و چالش هایی که در این تمرین ها قرار داده شده، شما می توانید میزان مهارت و میزان یادگیری خود را بعد از هر جلسه محک بزنید.بنابراین در انجام آنها حداکثر توان خود را به کار بگیرید.
تمرین ها براساس آموزش های هر جلسه تنظیم شده است.
پس از پایان هر جلسه، تمرین های مربوطه را با تمرکز کامل انجام داده و سوالات و موارد مبهم را یادداشت کرده تا در جلسه بعدی رفع اشکال شود.
با آرزوی موفیت برای تمام مردم سرزمینم
تمرین جلسه اول:
مباحث جلسه اول در مورد
- Copy , Paste , Paste special , Foramt Painter
- Clip Board , Alignment
- محاسبات ریاضی بر روی داده ها با Paste Special
- کلیدهای ترکیبی حرکت در جداول داده
- کلیدهای ترکیبی انتخاب ، ایجاد و حذف داده ها
- کلیدهای ترکیبی برخی از ابزارهای مهم اکسل
لطفا فایل زیر را دانلود کرده و مباحث مطرح شده در این جلسه را بر روی آن تمرین کنید.
و در نهایت سوالات زیر را در این فایل اجرا کرده و پاسخ ها را به پشتیبانی سایت با ذکر نام خودتان ارسال نمائید.
تمرین های جلسه اول:
| ۱ | اطلاعات Sheet1 را با فونت Tahoma سایز ۱۴ تنظیم نمائید |
| ۲ | بدون تغییر عرض ستون ها سعی کنید نوشته ها را درون سلول ها بگنجانید. |
| ۳ | محل نوشته ها را به صورت Horizontal و Vertical در مرکز سلول قرار دهید. |
| ۴ | اطلاعات sheet1 را Copy کرده و در Sheet2 فقط فرمت ان را Paste کنید. |
| ۵ | عرض ستون های Sheet2 را دقیقا مطابق با Sheet1 تنظیم کنید. |
| ۶ | با استفاده از ابزار Clip Board سلول های زرد رنگ Sheet1 را به ترتیب نزولی(یعنی از آخر به اول) به sheet3 رونوشت دهید. |
| ۷ | در Sheet4 اگر تعداد فروش را ۳۵% اضافه کنیم. مبلغ کل فروش پس از کسر تخفیف چند درصد رشد خواهد کرد؟ |
| ۸ | در ستون مبلغ Sheet4 مبالغ را به هزار ریال تبدیل کنید. |
مباحث جلسه دوم
- , Format as Table , Cell Style (قالب های آماده جدل و سلول)
- Insert Cell , Delete Cells , Insert Row & Column (حذف و اضافه نمودن سلول، حذف و اضافه کردن سطر و ستون همراه با کلیدهای ترکیبی)
- conditional Formating (قالب بندی سلول ها و هایلایت کردن آنها براساس شرط های از پیش تعیین شده)
- Hide & Unhide Cells And Columns (مخفی کردن و آشکار کردن سلول ها،سطرها و ستون ها برای مشاهده و آنالیز بخشی از داده ها)
- Fit And Auto Fit cells,Row Height , Column Width (تنظیم ارتفاع و عرض سطر و ستون ها و همچنین تنظیم اندازه سطر و ستون ها به صورت اتوماتیک به میزان داده های موجود در آن)
- انواع Fill (پر کردن محدوده سلول ها با استفاده از الگو و یا سلول های دیگر)
- ابزار Clear (حذف پیشرفته داده ها از سلول ها یا محدوده ها و تفاوت هر کدام)
- ابزار Auto Sum (جمع زدن اعداد ستون ها و سطرها در یک ثانیه)
لطفا فایل تمرین جلسه دوم را دانلود کنید و سوالات زیر را در آن تمرین کنید. و نتیجه را ارسال نمائید.
| ۱ | در Sheet1 تعدادهای(ستون G) بالای ۱۰٫۰۰۰ عدد را به رنگ سبز درآورید.تعدادهای بین ۵٫۰۰۰ تا ۱۰٫۰۰۰ عدد را با رنگ نارنجی و تعداد زیر ۵٫۰۰۰ عدد به رنگ قرمز |
| ۲ | در Sheet1 مشتریانی(ستون F) که در نام آنها “ان” وجود دارد به صورت Bold (نوشته ضخیم) درآورید. |
| ۳ | در Sheet2 نام کالاهای(ستون D) تکراری را با رنگ قرمز و کالاهای غیر تکراری را با رنگ زرد نمایش دهید. |
| ۴ | در Sheet2 مبالغ فروش(ستون J) بیشتر از میانگین را با رنگ سبز و مبالغ پایین تر از میانگین را با رنگ زرد نمایش دهید |
| ۵ | در Sheet3 با استفاده از Data Bar مبلغ فروش ها (ستون J) را به صورت نمودار مقایسه ای نشان دهید. |
| ۶ | یک Cell Style با این مشخصات ایجاد و ذخیره کنید. Font: Tahoma – FontSize:12 – Fill Color: Green – Border:All Borders |
| ۷ | در Sheet4 با استفاده از ابزار Auto Fit ارتفاع سطرها و عرض ستون های داده را تنظیم نمائید. |
| ۸ | Tab Color – Sheet4 را به رنگ آبی درآورید. |
مباحث جلسه سوم
- Sort (مرتب سازی داده ها براساس پارامترهای اختصاصی و تنظیم شده)
- Custome Sort ( مرتب کردن داده ها به صورت صعودی و نزولی و همچنین مرتب سازی چند سطحی داده ها)
- Filter (غربال کردن داده ها و مشاهده داده های خاص – قابلیت تحلیل داده با استفاده از فیلتر کردن و فیلتر براساس پارامترهای تعیین شده – فیلتر داده ها براساس بازه ای از اعداد و …)
- Advanced Filter (فیلتر پیشرفته داده ها – فیلتر کردن همزمان چندین ستون با هم و تهیه خروجی از داده های فیلتر شده با یک کلیک)
- Find (جست و جو در اطلاعات وارد شده و یافتن داده مورد نظر در همان شیت و یا در کل شیت ها – جست و جو براساس فرمت سلول، رنگ سلول و …)
- Replace (جست و جو و جایگزین کردن داده ها در سلول ها در یک شیت و یا در کل شیت ها – همچنین تغییر فرمت تعداد زیادی سلول یا کل شیت با یک کلیک)
لطفا فایل جلسه سوم را دانلود کرده و تمرینات زیر را در آن انجام دهید. و نتیجه را ارسال کنید.
| ۱ | در Sheet1 ستون تاریخ را به صورت نزولی مرتب نمائید. |
| ۲ | در Sheet2 اطلاعات را به گونه ای مرتب کنید که در هر تاریخ مبالغ فروش از نزولی باشد. |
| ۳ | در Sheet3 اطلاعات را به گونه ای مرتب کنید که مشخص باشد خرید هر مشتری در هر روز صعودی باشد. |
| ۴ | در Sheet1 مبالغ فروش بالای ۱۰ میلیون ریال را فیلتر کنید.به صورتی که تعداد ها بین ۵۰۰۰ تا ۱۰٫۰۰۰ باشد. |
| ۵ | در Sheet2 اطلاعات را به گونه ای مرتب کنید که در هر تاریخ مبالغ فروش از نزولی باشد. |
| ۶ | در Sheet3 کسانی که بیش از ۴% تخفیف گرفته اند را فیلتر کنید. |
| ۷ | در Sheet4 عبارت “صدیقی” را یافته و رنگ سلول آن را به رنگ سبز مشخص نمائید. |
| ۸ | در Sheet4 به جاری “فرزانه” کلمه “خانم فرزانه” درج شود. |
| ۹ | در Sheet4 تمام کسانی که شامل حروف “ا” و”ل” هستند را بیابید و رنگ سلول آنها را به رنگ نارنجی در آورید. |
| ۱۰ | در Sheet2 تمام سلول های حاوی فرمول را با رنگ زرد مشخص کنید. |
| ۱۱ | در Sheet2 تمام سلول های حاوی اعداد را با رنگ سبز مشخص کنید. |
مباحث جلسه چهارم
این جلسه شروع فرمول نویسی خواهد بود.
پس از آموزش فرمول های ساده مانند Sum,Average,Count,Counta و … سراغ فرمول های شرطی می رویم
خیلی اوقات در محل کار محاسبات به صورت شرطی صورت می گیرید و در این جلسه سعی می شود که به طور کامل و با انواع مثال های چالشی فرمول های محاسبات شرطی مرور شود
انواع فرمول های محاسبات شرطی در این جلسه مانند Sumif, Countif,Sumifs,Countifs,IF, And,OR می باشد که به طور کامل آموزش داده خواهد شد
سوالات و تمرین های جلسه چهارم به شرح زیر است:
لطفا فایل تمرینی را دانلود کرده و این سوالات را بر روی آن پاسخ دهید. (متن سوالات در فایل نیز موجود است)
| ۱ | در Sheet2 اطلاعات خواسته شده جدول را با فرمول مناسب محاسبه نمائید. |
| ۲ | در Sheet3 به جای علامت ؟ فرمولی بنویسید که مقدار فروش هر مشتری بابت هر کالا را در بازه تاریخی بالای جدول نمایش دهد. |
| ۳ | در Sheet4 جمع تعداد و جمع مبلغ برای هر مشتری و کالا را براساس تاریخ بالای جدول بدست آورید. |
| ۴ | در Sheet4 سهم هر عدد از جمع پایین جدول را محاسبه نمائید. |
| ۵ | در Sheet4 در ستون پاداش۱ برای کسانی که بالای ۳% از فروش سهم داشته اند و کالای ۲۰۰۱۷ را خریداری کرده اند. ۱% مبلغ فروش پاداش محاسبه نمائید. |
| ۶ | در Sheet4 در ستون پاداش۲ برای کسانی که بیش از ۱۰۰۰۰ عدد خرید داشته اند به شرط آنکه مبلغ فروش بالای ۱۰۰٫۰۰۰٫۰۰۰ ریال باشد ۲% پاداش در نظر بگیرید |
| ۷ | در Sheet4 فرمول ها را به روشی بنویسید که در صورت بروز خطا عدد صفر درج شود. |
سوالات موجود در جدول شیت دوم:
| ۱- تعداد فروش محصولات به کد مشتری ۱۰۰۹۳ چقدر بوده است؟ |
| ۲- تعداد فروش محصول ۲۰۰۱۷ به کد مشتری ۱۰۰۹۳ چقدر بوده است؟ |
| ۳- تعداد فروش محصول ۲۰۰۳۲ در فصل تابستان چقدر بوده است؟ |
| ۴- کد مشتری ۱۰۰۹۳ از تاریخ ۱۳۹۷/۰۵/۰۹ تا تاریخ ۱۳۹۷/۱۰/۱۲ چه مبلغی خرید داشته است؟ |
| ۵- جمع مبلغ فروش در شش ماهه اول سال چقدر بوده است؟ |
| ۶- مشتری ۱۰۰۳۱ در فصل تابستان چند ردیف فاکتور داشته است؟ |
| ۷- چند ردیف فاکتور بدون تخفیف صادر شده است؟ |
| ۸- تعداد ردیف های فاکتور کد مشتری ۱۰۰۹۳ که کمتر از ۱۰۰۰ عدد بوده است؟ |
در این جلسه ابزار بسیار کاربردی و حرفه ای Pivot table آموزش داده می شود.
PivotTable یک ابزار قدرتمند برای محاسبه، خلاصه و تجزیه و تحلیل داده ها است که به شما اجازه می دهد مقایسه ها، الگوها و روند داده های خود را ببینید.
این ابزار جز جدایی ناپذیر گزارش گری در اکسل می باشد، بسیاری از گزارشات که با ابزار Pivot table تنها با چند کلیک طراحی می شود، احتیاج به ترکیب چندین فرمول دارد و گاها بعضی عملگرهای آن با فرمول ها و توابع اکسل غیر ممکن است
سوالات این بخش تمرین به شرح زیر است:
| ۱ | با ابزار Pivot table تعداد خرید هر مشتری از هر کالاپ را در یک شیت جدید بدست آورید |
| ۲ | تعداد فروش هر کالا به هر مشتری در هر ماه را با یک جدول نشان دهد. |
| ۳ | تعداد فروش را برای مشتریانی که که با حرف الف شروع می شوند محاسبه نمائید به صورت ماهانه |
| ۴ | مالیات ارزش افزوده فروش(۹%) را به صورت فصلی و ماهانه در یک جدول نمایش دهید |
| ۵ | میزان فروش ماهانه را در یک جدول و سپس در یک نمودار متصل به Pivot table نمایش دهید. |
| ۶ | سهم فروش هر ماه از کل فروش را بدست آورید. (یعنی اگر فروش کل ۱۰۰۰ عدد است و فروش ماه اول ۱۰۰-یعنی ماه اول ۱۰% کل فروش را تشکیل داده است) |
| ۷ | تعداد فروش را در گروه های ۲۰٫۰۰۰ عددی طبقه بندی کنید و جمع تعدادی هر گروه را بدست آورید- مانند تصویر sheet2 |
| ۸ | با ابزار Slicer چندین برش از جدول Pivot table ایجاد کنید (ماه،کالا،مشتری) و به این صورت چیدمان کنید. مشتری ۱۰۰۹۳ از کالای ۲۰۰۱۷ در فصل تابستان چه تعدادی خریداری کرده است |
| ۹ | با ابزار Pivot Charts دو نمودار رسم کنید. یکی فروش به تفکیک ماه و یکی محصولات فروش رفته در همان ماه، یک Slicer را به هر دو Pivot متصل کنید که با تغییر در فیلتر یکی از جداول، دیگری هم تغییر کند |
| ۱۰ | جدول Pivot که در سوال اول ایجاد کردید، طوری تنظیم کنید که با هر بار Refresh اندازه ستون های آن ثابت باقی بماند |