درسنامه آموزشی پودمان 2 ارائه دهنده خدمات رایانه‌ای کلاس دهم شبکه و نرم افزار

آیا تا به حال اندیشیده‌اید

- اگر بخواهید یک دفترچه نمره هوشمند طراحی کنید که به‌طور خودکار وضعیت تحصیلی را براساس میانگین نمرات رنگی کند (سبز= عالی، قرمز= نیاز به تلاش)، چگونه عمل می‌کنید؟
- فرض کنید مسئول تحلیل داده‌های آب‌وهوایی یک ماه هستید. چگونه با اکسل میانگین دما، روزهای بارانی و بیشترین دما را محاسبه و بصری‌سازی می‌کنید؟
- چگونه می‌توانید با استفاده از پیش‌بینی‌ها (PivotTables)، الگوهای پنهان در داده‌های فروش یک فروشگاه را کشف کنید؟
- اگر یک فایل اکسل با 5000 ردیف داده فروش داشته باشید، چگونه با فیلتر پیشرفته (Advanced Filter) می‌توانید فروش بالای 10 میلیون تومان در منطقه خاص را استخراج کنید؟
- چرا مرتب‌سازی چندسطحی (مثلاً اول براساس شهر، سپس براساس تاریخ) برای تحلیل داده‌های لجستیک ضروری است؟
- اگر هر ماه مجبور باشید یک گزارش تکراری با فرمت‌بندی خاص (رنگ‌آمیزی سطرها، محاسبه مجموع ستون‌ها) ایجاد کنید، چگونه با ضبط ماکرو این فرایند را خودکار می‌کنید؟

آنچه از هنرجو انتظار می‌رود

1- داده‌های موردنظر را فیلتر کند.
2- داده‌های جدول را مرتب کند.
3- برای داده‌های ورودی اعتبارسنجی انجام دهد.
4- داده‌های جدول را گروه‌بندی و نمودار تحلیلی آن‌ها رسم کند.
5- برای سرعت در عملیات تکراری از ماکروها استفاده کند.
6- برای گرفتن خروجی از انواع داده‌ها استفاده کند.
7- بتواند داده‌های جدول را برای چاپ آماده و تنظیمات موردنظر را انجام دهد.
8- ابزار هوش مصنوعی در اکسل را شناسایی کرده و از آن‌ها برای مدیریت بهتر عملیات در پردازش داده‌ها استفاده کند.

استاندارد عملکرد

در پایان این واحد، هنرجو باید بتواند بانک داده را پردازش و مدیریت کرده و خروجی مناسب را تهیه کند.

---

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

فیلتر کردن داده‌ها

1- فایلی به نام «Student.xlsx» ایجاد کنید که شامل فیلدهای مختلف «کد ملی، نام و نام‌خانوادگی، نام پدر، پایه، رشته تحصیلی و نمره» باشد.

2- از میان آمار هنرجویان، رکوردهایی که پایه و رشته یکسان دارند را پیدا کرده و به کاربرگ‌های (Sheet) مجزا منتقل کنید. برای این کار از ابزار فیلتر استفاده کنید.
ابزار فیلتر می‌تواند داده‌های مرتبط را از یک مجموعه بزرگ استخراج و داده‌های غیرضروری را پنهان کند.

3- ابتدا جهت صفحه را راست به چپ کنید (Right to Left Sheet).

4- یکی از سلول‌های حاوی اطلاعات را انتخاب کرده سپس در زبانه Data در گروه Sort & Filter روی گزینه Filter کلیک کنید. با این کار، علامت پیکان در سمت چپ فیلدهای جدول ظاهر می‌شود.

5- فهرست کشویی مربوط به پایه را باز کرده و تیک گزینه Select All را بردارید و «دهم» را انتخاب کنید.
برای مشاهده نتیجه، روی OK کلیک کنید. پس از اعمال فیلتر، علامت قیف کنار پیکان دیده می‌شود (شکل 31).

6- به همین روش رشته‌های پایه دهم را از هم تفکیک کنید.

7- نتیجه را مشاهده و در یک کاربرگ مجزا کپی کنید.

8- برای تفکیک پایه یازدهم و دوازدهم مرحله 5 و 4 را تکرار کنید.

فیلتر را از کاربرگ اول حذف کنید. برای این کار، روی پیکان مربوطه کلیک و فرمان Clear Filter From را اجرا کنید (شکل 32).

10- در هر کاربرگ، با توجه به پایه و رشته مربوطه، فیلدهای نمرات دروس را کپی کنید (شکل 33).

فیلتر مقادیر عددی

با اعمال فیلتر و کلیک روی ستون‌های مقادیر عددی، گزینه Number Filters در فهرست کشویی فیلتر، ظاهر می‌شود.

آقای محمدی به اسامی هنرجویانی که نمره درس «ریاضی 2» آن‌ها کمتر از 12 هست نیاز دارد. اسامی این هنرجویان را در کاربرگی با عنوان «افت تحصیلی» قرار دهید.

1- بعد از انتخاب سلول «ریاضی 2» و اجرای فیلتر، از فهرست Number Filters گزینه …Less Than را انتخاب کنید.

2- عدد 12 را وارد کرده و دکمه OK را کلیک و نتیجه را مشاهده کنید (شکل 34).

مرتب‌سازی داده‌ها

برای افزایش سرعت جست‌وجو در اطلاعات، لازم است داده‌ها مرتب شوند. می‌توانید انواع داده‌های عددی، متنی و تاریخی را به‌صورت صعودی یا نزولی مرتب کنید. مرتب‌سازی داده‌های متنی براساس حروف الفبا به‌صورت صعودی (A _ Z) و یا نزولی (Z _ A) و برای داده‌های عددی از کوچک به بزرگ (Smallest to Largest) یا از بزرگ به کوچک (Largest to Smallest) است.

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

1- فایل "Student.xlsx" را باز کنید.
2- در کاربرگ مربوط به هر کلاس، در یکی از سلول‌های ستون نام‌خانوادگی کلیک کنید تا انتخاب شود.
3- در زبانه Data از بخش Sort & Filter روی گزینه  کلیک و نتیجه را مشاهده کنید.
4- فایل را ذخیره کنید.

ثابت نگه‌داشتن سطر یا ستون در پیمایش رکوردها

آقای محمدی به پیشنهاد هنرآموزان دروس شایستگی غیرفنی، کاربرگی مشابه شکل 35 برای درج نمرات دروس شایستگی غیرفنی در هر پایه ایجاد کرده است. او درنظر دارد، نمرات هنرجویان را در کاربرگ «الزامات محیط کار» وارد کند. هنگام پیمایش رکوردها برای ورود نمرات در رکوردهای پایین‌تر جدول، با مشکل عدم مشاهده عنوان فیلدهای جدول مواجه است. برای حل این مشکل، باید سطر عنوان جدول را ثابت نگه دارد.

ثابت نگه‌داشتن سلول‌ها

1- فایل «student.xlsx» را باز کنید. کاربرگ «الزامات محیط کار» را انتخاب کنید.

سلول A1 را انتخاب کنید. در زبانه View از گروه Window، ابزار Freeze Panes را انتخاب و فرمان Freeze Top Row را اجراکنید، تا اولین سطر در زمان پیمایش، به سمت پایین ثابت باقی بماند (شکل 36).

2- نمرات سایر هنرجویان را ثبت کنید. با پیمایش رکوردها و ورود نمرات در رکوردهای پایین جدول، همچنان عناوین جدول مشاهده می‌شوند.

3- در زبانه View از گروه Window روی ابزار Freeze Panes کلیک و گزینه Unfreeze Panes را اجرا کنید، سطر عنوان از حالت ثابت خارج می‌شود.

گروه‌بندی داده‌ها

گروه‌بندی، روشی مؤثر برای دسته‌بندی کردن و سازماندهی سطرها و ستون‌های مرتبط به‌صورت مجموعه واحد که باعث نظم بیشتر و مدیریت راحت‌تر اطلاعات می‌شود. از این طریق می‌توان داده‌ها را در ستون‌ها یا سطرها به شکل یک مجموعه واحد نمایش داد.

1- محدوده موردنظر را انتخاب کنید.

2- از زبانه Data گروه Outline گزینه Group را انتخاب کنید.

3- از کادر باز شده بر اساس نیاز یکی از گزینه‌های سطر یا ستون را انتخاب کنید.

4- پس از گروه‌بندی داده‌ها می‌توان آن‌ها را به‌صورت جمع‌شونده تنظیم نمود تا به طور موقت مخفی شوند و فضای کمتری را اشغال کنند.

اعتبارسنجی داده‌های ورودی

همان‌طور که می‌دانید برای نمره پایانی یک پودمان فقط می‌توان «عدم احراز شایستگی، احراز شایستگی و بالاتر از حد انتظار» را ثبت نمود حال اگر در کاربرگ «الزامات محیط کار»، در سلول مربوط به نمره پایانی پودمان، نمره‌ای به جز این موارد، وارد شود بدون نمایش پیام خطا، داده را می‌پذیرد. در حالی که این مقدار، نامعتبر است. بنابراین باید به روشی از ورود داده‌های غیرمجاز جلوگیری شود. با استفاده از ابزار Data Validation، می‌توان داده ورودی را کنترل و از صحت آن اطمینان حاصل کرد. در ادامه، با چند مورد از کاربردهای این ابزار آشنا می‌شویم.

ایجاد فهرست کشویی: می‌توانید با استفاده از ابزار Data Validation یک فهرست کشویی از انتخاب‌های موردنظر ایجاد کنید به طوری که کاربر فقط بتواند از بین آن گزینه‌ها انتخاب کند. برای مثال: ایجاد یک فهرست کشویی برای انتخاب گزینه‌های «دختر» یا «پسر».

تنظیم محدوده مقادیر: با استفاده از این ابزار، می‌توانید محدودیت‌های مختلفی بر روی ورودی‌ها اعمال کنید؛ به عنوان مثال: محدود کردن نمرات دانش‌آموزان در بازه 0 تا 20، محدود کردن سن افراد در فرم استخدام در محدوده 24 تا 35 سال و... .

ایجاد عبارت شرطی: این نوع تنظیمات به شما این امکان را می‌دهد که مقادیر وارد شده در یک سلول را بر اساس داده‌ای که در سلول دیگری وارد می‌شود محدود کنید. فرض کنید شما در یک فرم ثبت نام، دو سلول دارید: یکی برای وضعیت ازدواج که می‌‌تواند «متأهل» یا «مجرد» باشد دیگری برای تعداد فرزندان که باید بر اساس وضعیت ازدواج، تنظیم شود.

ایجاد راهنما برای ورود داده‌ها در سلول‌ها: این قابلیت معمولاً به صورت پیامی در کنار سلول‌ها ظاهر می‌شود و به کاربر کمک می‌کند تا بداند چه نوع داده‌ای باید وارد کند.

سفارشی‌سازی پیام خطا: با استفاده از این ابزار، می‌توانید پیامی خاص و دلخواه را برای نمایش به کاربر، زمانی که داده‌ای غیرمجاز یا اشتباه وارد می‌شود تنظیم کنید.

تنظیمات Data Validation

برای اعتبارسنجی داده‌های یک سلول، ابتدا سلول موردنظر را انتخاب کنید. از سربرگ Data و قسمت Data Tools، گزینه Data Validation را انتخاب کنید. اعتبارسنجی داده در پنجره‌ای با سه زبانه تعریف می‌شود. در شکل 39، این پنجره را مشاهده می‌کنید.

زبانه Settings: از فهرست کشویی Allow، ابتدا نوع داده را تعیین نمایید. داده‌های معتبر شامل گزینه‌های Whole Number (اعداد)، Date و Time (تاریخ و زمان)، Text Length (طول رشته متنی)، List و Custom است. به‌عنوان مثال، اگر گزینه Whole Number را انتخاب کنید، به این معنا است که تنها داده‌های عددی (اعداد) به عنوان محتوای سلول موردنظر، معتبر می‌باشند.

زبانه Input Message: اگر بخواهید قبل از ورود داده و با انتخاب سلول موردنظر، پیام راهنمایی نمایش داده شود، عنوان پیام را در کادر Title و متن پیام را در قسمت Input Message وارد کنید.

زبانه Error Alert: با این زبانه می‌توان پیامی را طراحی کرد که در صورت ورود داده نامعتبر، این پیام نشان داده شود. در بخش Style یک نماد مناسب در کادر Title عنوان پیام و در Error Message متن پیام را وارد کنید.

معتبرسازی داده‌های ورودی

آقای فروتن جهت اعتبارسنجی نمرات مستمر پودمان‌های درس «الزامات محیط کار» پیشنهاد داد که به کمک ابزار Data Validation بازه‌ای از اعداد «0، 0/5، 1، 1/5، 2، 2/5، 3، 3/5، 4، 4/5، و 5» تعریف شود.

او مراحل اجرای این تنظیمات را به صورت زیر توضیح داد.

1- فایل «student» را باز کنید. کاربرگ درس الزامات محیط کار را انتخاب کرده و مراحل زیر را در آن کاربرگ انجام دهید.

2- ستون مربوط به نمرات مستمر 5 پودمان را برای اعتبارسنجی انتخاب کنید.

3- فرمان Data Validation را برای اعتبارسنجی اجرا کنید.

4- معیار اعتبارسنجی را مطابق جدول 8 انتخاب کنید. در قسمت Allow گزینه Decimal را برای وارد کردن اعداد اعشاری انتخاب کنید.

5- شرط محدودکننده معیار را تعیین کنید. در قسمت Data گزینه Between را انتخاب کنید.

مقدار Minimum را برابر با مقدار 0 و مقدار Maximum را برابر با مقدار 5 وارد کنید.

6- پیام راهنمای اعتبارسنجی را وارد کنید. زبانه Input Message را انتخاب کرده و در کادر Title واژه «راهنما» و در قسمت Input Message، پیام «فقط اعداد 0، 0/5، 1، 1/5، 2، 2/5، 3، 3/5، 4، 4/5 و 5 را می‌توانید ثبت کنید» را وارد نمایید (شکل 40).

7- پیام خطای اعتبارسنجی را تعیین کنید. زبانه Error Alert را انتخاب کنید. در قسمت Title واژه «خطا» و در قسمت Error Message پیغام «نمره وارد شده نامعتبر است.» را وارد کرده و Style را از نوع Stop انتخاب و روی دکمه OK کلیک کنید (شکل 41).

با استفاده از دکمه Clear All می‌توانید تغییرات اعمال شده در این پنجره را به حالت اولیه برگردانده و از هیچ شرطی هنگام ورود داده‌ها استفاده نکنید.

آشنایی با PivotTable در اکسل

یکی از روش‌های گزارش‌نویسی در اکسل، استفاده از ویژگی PivotTable است که با استفاده از آن می‌توانید داده‌های خود را به راحتی گروه‌بندی کنید.

برای ایجاد PivotTable، چیزی به داده‌ها اضافه یا از داده‌ها کم نمی‌کنید و آن‌ها را تغییر نمی‌دهید بلکه فقط داده‌ها را به سادگی سازماندهی می‌کنید تا بتوانید اطلاعات مفیدی را از آن‌ها استخراج نمایید. از این رو امکان تهیه گزارش‌های بهتری برای شما فراهم می‌شود. این مطلب مقدمه‌ای برای آشنایی با Pivot Table و آموزش گام به گام با داده‌های نمونه است.

گام‌های ایجاد PivotTable

برای اینکه مراحل ساخت PivotTable را بدون خطا دنبال کنید رعایت نکات زیر الزامی می‌باشد:

• تمام ستون‌های جدول دارای عنوان باشد.
• بین سطرها و ستون‌های حاوی اطلاعات، سطر یا ستون خالی یا Merge شده وجود نداشته باشد.
• در جدول حاوی اطلاعات، توابعی نظیر Subtotal ،Sum و نظیر اینها استفاده نشده باشد.
• واردکردن داده‌ها در محدوده‌ای از ردیف‌ها و ستون‌ها

هر PivotTable در اکسل، با یک جدول اصلی شروع می‌شود که تمام داده‌های شما در آن قرار دارد. برای ساخت این جدول، ابتدا باید داده‌ها را در یک سری ردیف‌ها و ستون‌های کاربرگ وارد کنید. (هر ستون جدول باید دارای یک عنوان مشخص باشد.) شکل زیر را درنظر بگیرید که شامل داده‌هایی مناسب برای ساخت PivotTable است (شکل 42).

درج PivotTable

برای شروع، یکی از سلول‌های حاوی داده را انتخاب و مطابق شکل 43، از زبانه Insert گروه Tables روی ابزار PivotTable کلیک کنید (شکل 43).

کادر محاوره‌ای PivotTable from table or range نمایش داده می‌شود که شامل سه بخش زیر است:

Choose the data that you want to analyze:

با انتخاب گزینه Select a table or range می‌توانید یک جدول یا آدرس یک محدوده را برای تحلیل داده‌ها انتخاب کنید. با کلیک روی علامت پیکان، محدوده موردنظر را انتخاب کرده و دوباره روی پیکان کلیک کنید (شکل 44).

Choose where you want the PivotTable to be placed:

در این بخش، با استفاده از گزینه‌های زیر می‌توانید انتخاب کنید که PivotTable در یک کاربرگ جدید یا در یکی از کاربرگ‌های موجود، ایجاد شود. در صورتی که گزینه New Worksheet را انتخاب کنید، PivotTable در یک کاربرگ جدید ایجاد می‌شود اما اگر گزینه Existing Worksheet انتخاب گردد، PivotTable در یکی از کاربرگ‌های موجود که آدرس آن از طریق کادر Location مشخص می‌شود، ایجاد می‌گردد (شکل 44).

Choose whether you want to analyze multiple tables:

این بخش، مربوط به ویژگی جدید Data Model هست که به شما اجازه می‌‌دهد اطلاعات چند جدول مختلف را به‌طور هم‌زمان تحلیل کنید که توضیح آن فراتر از محدوده این مطالب است.

پس از انجام تنظیمات دلخواه، دکمه OK را کلیک کرده تا PivotTable مورد نظر، ایجاد شود.

در شکل زیر جزئیات و تنظیمات جدول، نشان داده شده است. همان‌طور که در شکل زیر مشاهده می‌کنید، در سمت راست قسمت بالا، نام فیلدهای جدول داده‌های صفحه اکسل شما و در سمت چپ ساختار PivotTable مربوطه بدون هیچ داده‌ای، قرار گرفته است. هر ستون از داده‌های اصلی به‌عنوان یک فیلد با همان عنوان نشان داده می‌شود و در قسمت پایین هم چهار بخش مختلف PivotTable قرار دارد که می‌توان این فیلدها را به وسیله ماوس به یکی از این چهار بخش درگ کرد (شکل 45).

ویرایش فیلدهای PivotTable

اکنون ساختار کلی PivotTable را در اختیار دارید و لازم است به کمک اجزای تشکیل‌دهنده آن که در شکل 45 مشاهده کردید، آن را تکمیل نمایید.

اجزای تشکیل‌دهنده PivotTable در اکسل

Rows: فیلدهایی که قرار است براساس آن‌ها گزارش‌گیری انجام شود را به این بخش درگ می‌کنیم. این فیلدها در واقع ردیف‌هایی هستند که در سمت چپ یک PivotTable ظاهر می‌شوند.

Values: در این بخش مقادیری که قصد بررسی آن‌ها را داریم قرار می‌دهیم. نکته‌ای که وجود دارد این است که محاسبات بر روی فیلدهایی که در این ناحیه قرار دارند انجام می‌شود.

Columns: با کشیدن هر فیلد به قسمت Columns، یک ستون جداگانه برای داده‌های شما ایجاد خواهد شد.

Filter: با کشیدن فیلدی به این بخش، می‌توانیم از آن برای فیلتر کردن گزارش خود استفاده کنیم. افزون بر این اگر تمایل دارید، می‌توانید داده‌ها را از بزرگ به کوچک یا از کوچک به بزرگ مرتب‌سازی کنید. برای این کار، روی یکی از مقادیر ستون Grand Total راست کلیک کرده، گزینۀ Sort را انتخاب کنید و سپس Sort Largest to Smallest یا Sort Smallest to Largest را بزنید.

تحلیل PivotTable

زمانی که PivotTable را آماده کردید، باید هدف اولیه خود را دنبال کنید. چه اطلاعاتی را می‌خواهید از طریق این ابزار به دست بیاورید؟ مثلاً، فرض کنید می‌خواهیم بفهمیم که در هر کلاس چه کسی، کمترین و بیشترین نمره را در هر درس کسب کرده است؟!

ساخت PivotTable در اکسل

آقای محمدی در کاربرگ‌های موجود در فایل «Student»، اطلاعات مربوط به نمرات دروس عمومی تمام هنرجویان هنرستان را به تفکیک پایه و رشته، وارد کرده است. در شکل 42 کاربرگ مربوط به نمرات دروس عمومی هنرجویان دهم شبکه و نرم‌افزار رایانه را به صورت کاربرگ نمونه، در اختیار دارد. او می‌خواهد گزارشی تهیه کند که نشان دهد بیشترین و کمترین میانگین نمرات در هر کلاس، به کدام هنرجو اختصاص دارد؟ برای انجام این کار، از قابلیت PivotTable در اکسل استفاده می‌کند.

1- فایل «Student» را باز کنید. کاربرگ کلاس دهم شبکه و نرم‌افزار رایانه را انتخاب کرده و مراحل زیر را در آن کاربرگ انجام دهید.

2- یکی از سلول‌های حاوی داده را انتخاب و از تب Insert گروه Tables روی ابزار PivotTable کلیک کنید.

3- تنظیمات مربوط به PivotTable را همان‌طور که در بالا گفته شد، انجام داده و روی دکمه OK کلیک کنید تا PivotTable شما ایجاد شود.

4- اکنون باید برای تنظیم گزارش، فیلد «نام و نام‌خانوادگی» را به بخش Rows و فیلد «میانگین نمرات» را به بخش Values درگ کنید (شکل 46).

5- ستون «میانگین نمرات» را انتخاب کرده و سپس راست کلیک کرده و از منوی ظاهر شده فرمان Format Cells را انتخاب کنید. در کادر محاوره‌ای Format Cells و زبانه Number از بخش Category روی عنوان Number کلیک کنید تا میانگین نمرات هر هنرجو با دو رقم اعشار نمایش داده شود.

6- نوع محاسبه برای مقدار میانگین نمرات را تنظیم کنید. بر روی اولین مقدار (میانگین نمرات Sum of) از قسمت Values کلیک کرده و از منوی باز شده فرمان Value Field Settings را انتخاب کنید. از سربرگ Summarize Value By در کادر محاوره‌ای Value Field Settings در کادر Custom Name یک نام دلخواه برای ستون میانگین نمرات درج کرده و از فهرست کشویی نوع محاسباتی که قرار است بر روی فیلد موردنظر، انجام شود را انتخاب نمایید. از آنجایی که قرار است مشخص کنید که بیشترین و کمترین میانگین نمرات، متعلق به کدام هنرجو است، پس از لیست بازشو، فرمان Max  را انتخاب و روی دکمه OK کلیک کنید (شکل 47).

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

7- کمترین مقدار میانگین نمرات را در PivotTable مربوطه نمایش دهید. دوباره فیلد «میانگین نمرات» را به بخش Values درگ کنید و همان مراحلی را که در دستورالعمل شماره 6 آمده است را دنبال کنید با این تفاوت که این بار از فهرست کشویی، نوع محاسبات را روی Min تنظیم نمایید. با انجام این کار، مقدار کمترین میانگین نمرات را در پایین ستون آن مشاهده خواهید کرد (شکل 48).

به‌روزرسانی PivotTable در اکسل: زمانی که داده‌ها در جدول اصلی تغییر می‌کنند، لازم است تا PivotTable را به‌روزرسانی کرده و تغییرات را مشاهده کنید. برای این کار، ابتدا PivotTable را انتخاب، سپس بر روی آن راست کلیک کرده و Refresh را انتخاب کنید و یا می‌توانید بر روی تب PivotTable Analyze کلیک کرده و گزینه Refresh را انتخاب کنید. این قابلیت تضمین می‌کند که گزارش‌گیری در اکسل با PivotTable همیشه بر اساس آخرین داده‌ها انجام شود.

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

درج PivotChart (نمودار محوری): یک PivotChart نمایش گرافیکی یک PivotTable است که با استفاده از آن می‌توان نمودارهای ساده و زیبا برای ارائه گزارش تهیه نمود.

برای ایجاد یک PivotChart در اکسل می‌توان به روش زیر، عمل کنید:

1- روی یکی از سلول‌های داخل PivotTable کلیک کنید.

2- در زبانه PivotTable Analyze، در گروه Tools، روی ابزار PivotChart کلیک کنید (شکل 49).

3- در کادر محاوره‌ای Insert Chart از لیست سمت چپ، نوع نمودار مورد نظر را انتخاب کرده و روی دکمه OK کلیک کنید.

با انجام این کار، PivotChart شما ایجاد و نمایش داده می‌شود. پس از آن در صورت لزوم می‌توانید نوع نمودار را تغییر دهید یا برای محدود کردن داده‌ها در PivotChart خود، از فیلترها استفاده کنید.

تحلیل و گزارش‌گیری میزان فروش ماهانه یک فروشگاه با استفاده از PivotTable

1- تهیه داده‌های اولیه شامل: تاریخ فروش، شناسه محصول، نام محصول، دسته‌بندی محصول، قیمت واحد، تعداد فروخته شده

2- ایجاد PivotTable

3- تحلیل داده‌ها با استفاده از PivotTable

4- استفاده از فیلترها: با اضافه کردن فیلترها، داده‌ها را بر اساس معیارهای مختلف مانند تاریخ، دسته‌بندی محصول و...، فیلتر کنید و تحلیل‌های دقیق‌تری انجام دهید.

5- ایجاد PivotChart: با استفاده از PivotTable ایجاد شده، PivotChart مناسبی (مانند نمودار ستونی یا دایره‌ای) برای نمایش بصری داده‌ها ایجاد کنید.

6- فایل را با نام «Market» ذخیره کنید.

مفهوم ماکرو در اکسل

ماکروها، مجموعه‌ای از فرمان‌ها و دستوراتی هستند که در یک فایل اکسل در قالب کدهای VBA(Visual Basic For Applications) ذخیره می‌شوند. ماکروها در واقع، ابزارهایی هستند که به کاربران امکان می‌دهند تا وظایف تکراری و زمان بر را به عملیاتی خودکار، تبدیل کنند.

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

فایل «Student» را باز کنید. کاربرگ مربوط به کلاس «دهم مکانیک خودرو» را انتخاب کرده و مراحل زیر را در آن کاربرگ انجام دهید.

فعال‌سازی تب Developer

تمام عملیات و تنظیمات مربوط به ماکرو، در تب Developer انجام می شود. از آنجایی که معمولاً این زبانه در اکسل به‌صورت پیش‌فرض فعال نیست، پس در ابتدا باید آن را فعال کنید. برای این کار، روی یکی از تب‌ها (مثلاً Home) راست‌کلیک کرده و گزینه …Customize the Ribbon را انتخاب کنید. در پنجره باز شده از سمت راست گزینه Developer را انتخاب و روی دکمه OK کلیک کنید (شکل 50).

با انجام مراحل بالا تب Developer در کنار سایر تب‌ها در صفحه اکسل نمایش داده می‌شود.

شروع ضبط ماکرو

برای ضبط ماکرو یکی از مراحل زیر را می‌توان به کار برد:

- از زبانه Developer گزینه Use Relative References را انتخاب کنید.

- از زبانه Developer گزینه Record Macro را انتخاب کنید (شکل 51).

- در کادر محاوره‌ای Record Macro، مشخصات ماکرویی که قرار است ضبط شود را مطابق شکل مقابل، تنظیم کنید (شکل 52).

در ادامه بخش‌های مشخص شده در شکل 52 توضیح داده می‌شود:

- در کادر Macro name نامی برای ماکرو وارد کنید که پیشنهاد می‌شود یک نام مختصر و تاحدی گویای عملکرد ماکرو باشد. در نام انتخابی می‌توانید از حروف، اعداد و کاراکتر هم استفاده کنید اما نکته‌ای که وجود دارد این است که نام آن، حتماً باید با یک حرف شروع شود و استفاده از فاصله (Space) در نام انتخابی هم مجاز نیست.

- در قسمت Shortcut Key، می‌توانید برای اجرای ماکرو یک کلید میانبر تعریف کنید. برای انجام این کار، از الگوی Ctrl + Shift + letter استفاده کنید.

- از بخش Store macro in، می‌توانید محل ذخیره‌سازی ماکرو را مشخص کنید که شامل گزینه‌های زیر می‌باشد:

Personal Macro Workbook: ماکرو را در یک فایل اکسل به نام Personal.xlsb ذخیره می‌کند. هر زمان که از Excel استفاده می‌کنید، همه ماکروهای ذخیره شده در این فایل، در دسترس هستند.

This Workbook: این گزینه به‌صورت پیش‌فرض تعریف شده است. در این حالت، ماکرو در فایل جاری، ذخیره می‌شود و زمانی که فایل را باز می‌کنید یا آن را با کاربران دیگر به اشتراک می‌گذارید، ماکروی موردنظر در دسترس خواهد بود.

New Workbook: با انتخاب این گزینه یک کارپوشه جدید ایجاد شده و ماکرو در فایل جدید ضبط می‌شود.

- در کادر Description می‌توانید شرح مختصری از عملکرد ماکروی موردنظر بنویسید که انجام این کار، اختیاری می‌باشد ولی توصیه می‌شود که این قسمت را تکمیل نمایید تا اگر تعداد ماکروها، زیاد بود بتوانید با مشاهده توضیحات سریع‌تر ماکروی موردنظر را پیدا کنید.

پس از تکمیل قسمت‌های توضیح داده شده، دکمه OK را کلیک کنید.

از نوار وضعیت (Status Bar) مطابق شکل زیر، روی آیکن نمایش داده شده کلیک کنید تا فرایند ضبط ماکرو آغاز شود (شکل 53).

در این مرحله، کارهایی که می‌خواهید در قالب ماکرو ضبط شوند را انجام دهید. در اینجا باید تمام مراحل انجام شده در تعیین مقادیر مربوط به کمترین و بیشترین میانگین نمرات دروس عمومی را مجدداً دنبال کنید. در انتها روی دکمه Stop Recording کلیک کنید (شکل 54).

دوباره روی گزینه Use Relative References کلیک کنید تا غیرفعال شود.

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

مدیریت ماکروهای ضبط شده

تمامی تنظیمات مربوط به ماکروها در پنجره‌ای به نام Macro قابل انجام است. برای دسترسی به این تنظیمات، از زبانه Developer و گروه Code روی دکمه Macros کلیک کنید (شکل 55).

با انتخاب دکمه Macros کادر محاوره‌ای نمایش داده می‌شود که در آن، لیستی از ماکروهایی که در تمام فایل‌های باز، موجود هستند قابل مشاهده است (شکل 56).

کاربرد دکمه‌های موجود در سمت راست کادر محاوره‌ای Macro، در جدول 9 به‌طور مختصر، بیان شده است.

جدول 9 - عملکرد دکمه‌های کادر محاوره‌ای Macro

دکمه	عملکرد
Run	با زدن این دکمه، ماکروی انتخاب شده (ماکرویی که از لیست سمت چپ انتخاب کرده‌اید) اجرا می‌شود.
Step into	این امکان را می‌دهد تا ماکروی انتخاب شده را در محیط Visual Basic Editor اشکال‌زدایی و تست شود.
Edit	با زدن این دکمه، ماکروی انتخابی در محیط Visual Basic Editor نمایش داده می‌شود در این حالت می‌توانید کدهای موجود در ماکرو را ویرایش کنید.
Create	برای ایجاد یک ماکروی جدید به کار می‌رود.
Delete	ماکروی انتخاب شده را حذف می‌کند.
Options	برای تغییر مشخصات ماکروی انتخاب شده مثل کلید میانبر و توضیحات نحوه عملکرد ماکرو به کار می‌رود.

مدیریت خروجی‌ها در نرم‌افزار اکسل

نرم‌افزار اکسل، امکان تهیه خروجی به فرمت‌های مختلفی غیر از xlsx. را برای کاربران، فراهم می‌کند.

انتخاب نوع خروجی اکسل، بستگی به نیاز کاربر دارد. در جدول 10 با برخی از خروجی‌های نرم‌افزار اکسل و کاربرد آن‌ها آشنا می‌شوید.

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

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

1- فایل «Student. xlsx» را باز کنید.کاربرگ گزارش یک کلاس را انتخاب و مراحل زیر را در آن کاربرگ انجام دهید.

2- از سربرگ File دستور Save As را اجرا کنید.

3- روی گزینه Browse کلیک کنید.

4- در پنجره‌ای که ظاهر شده، محل ذخیره‌سازی را در قسمت نوار آدرس، مشخص و نام فایل جدید را در بخش File name وارد کنید.

5- در بخش Save as type، نوع فایل ذخیره شده را از نوع PDF انتخاب کنید.

6- نحوه ذخیره‌سازی و ساخت فایل PDF را به دلخواه خود می‌توانید تنظیم کنید (شکل 57).

در پنجره‌ای که شکل 57 ظاهر می‌شود، امکاناتی وجود دارد که نحوه ذخیره‌سازی و ساخت فایل PDF را به دلخواه شما در می‌آورد. ابتدا به معرفی آن‌ها می‌پردازیم.

در بخش Optimize for، انتخاب گزینه Standard بهینه‌سازی فایل PDF برای حالت نمایش استاندارد (محیط وب یا چاپ) را مشخص می‌کند. به این ترتیب کنترلی روی کیفیت و البته حجم فایل ایجاد شده خواهید داشت. با انتخاب گزینه Minimum size، امکان کاهش کیفیت و در عوض کاهش حجم یا اندازه فایل PDF برای انتشار فایل به صورت برخط (Online) فراهم می‌شود. این دو گزینه به صورت پیش‌فرض تنظیم شده‌اند و احتیاجی به تنظیمات اضافه ندارند.

با انتخاب گزینه Open file after publishing پیش‌نمایش نتیجه تبدیل فایل به فرمت PDF نشان داده می‌شود.

برای تنظیمات بیشتر در مورد حجم و کیفیت فایل PDF ایجاد شده، می‌توانید از دکمه Options استفاده کنید.

روی دکمه save کلیک کنید تا ذخیره‌سازی فایل در محلی که مشخص کرده‌اید، انجام شود.

چاپ کاربرگ

گاهی خروجی‌هایی به‌صورت چاپی نیاز است. برای مثال در سیستم مدرسه، خروجی به‌صورت کارنامه در اختیار هنرجویان قرار می‌گیرد و یا برای هنرآموزان، فهرست حضور و غیاب به‌صورت چاپی در نظر گرفته می‌شود. در یک سیستم فروشگاهی، فاکتور فروش به‌صورت خروجی چاپی در اختیار مشتری قرار می‌گیرد.

برای چاپ اطلاعات در اکسل، می‌توانید از سربرگ File دستور Print را انتخاب کنید یا کلید میانبر Ctrl+P را از صفحه کلید، فشار دهید.

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

تعیین محدوده چاپ (Print Area)

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

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

1- فایل «student» را باز کنید. کاربرگ «درس الزامات محیط کار» را انتخاب کنید.

2- بر روی فیلد «رشته»، فیلتری اعمال کنید که تنها هنرجویان رشته الکترونیک در لیست، نمایش داده شوند.

3- محدوده داده‌های موردنظر را انتخاب کنید (شکل 58).

از زبانه Page Layout گروه Page Setup ابزار Print Area را انتخاب و از منوی باز شده روی گزینه Set Print Area کلیک کنید (شکل 59).

از منوی File دستور Print را انتخاب کنید یا کلید میانبر Ctrl+P را از صفحه کلید فشار دهید (شکل 60).

4- در بخش Settings، می‌توانید تنظیمات چاپ را انجام دهید و تعیین کنید کدام داده‌ها باید چاپ شوند.

روی فلش کنار Print Active Sheets کلیک و یکی از این گزینه‌ها را بر اساس جدول 11 انتخاب کنید.

جدول 11 - تنظیمات محدوده چاپ

دستور	توضیحات
Print selection	چاپ محدوده خاصی از سلول‌ها (محدوده موردنظر را انتخاب و سپس روی گزینه Print Selection کلیک کنید.)
Print Active sheet (s)	چاپ داده‌های کاربرگ یا کاربرگ‌هایی که درحال حاضر فعال می‌باشد.
Print entire workbook	چاپ تمام کاربرگ‌های پوشه‌کار
Print Selected Table	چاپ داده‌های یک جدول (این گزینه فقط در صورت انتخاب جدول یا قسمتی از آن ظاهر می‌شود.)

5- برای تنظیم حاشیه‌های صفحه، روی دکمه Show Margins در گوشه پایین سمت راست کلیک کنید.

برای بزرگ‌تر یا محدودتر کردن حاشیه‌ها، کافی است دستگیره‌ها را با استفاده از ماوس درگ کنید. با کشیدن دستگیره‌ها در بالا یا پایین پنجره پیش‌نمایش چاپ، می‌توانید عرض ستون را نیز تنظیم کنید.

6- با انتخاب گزینه Page Setup پنجره تنظیمات صفحه باز می‌شود. برحسب نیاز می‌توانید حالت‌های عمودی (Portrait) و افقی (Landscape) را انتخاب کنید. تنظیمات مربوط به حاشیه صفحه، اندازه کاغذ، تعیین سرصفحه و پاصفحه و غیره را انجام دهید.

7-در نهایت، قبل از اقدام به چاپ بهتر است پیش‌نمایش آن را مشاهده کنید تا اگر شکل نهایی اطلاعات هنگام چاپ اشکالی دارد آن را اصلاح کنید.

8- با انتخاب دکمه Print داده‌های موردنظر را چاپ کنید.

درج شکست صفحه (Break Page) در اکسل

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

برای درج Break Page، روی یکی از سلول‌هایی که قرار است سطر یا ستون آن در صفحه جدید چاپ شود، کلیک کنید. از زبانه Page Layout در گروه Page Setup روی گزینه Breaks کلیک کنید. سپس گزینه Insert Page Break را انتخاب کنید. با این کار یک شکست صفحه درج شده است. برای نمایش بصری داده‌های موجود در صفحات مختلف، در زبانه View گزینه Page Break Preview را فعال کنید.

اگر می‌خواهید موقعیت شکست یک صفحه خاص را تغییر دهید، با درگ کردن خط شکست، آن را به هر جایی که می‌خواهید منتقل کنید. برای حذف Page Break، روی یکی از سلول‌های ردیفی که Page Break دارد کلیک کرده، روی آیکن Breaks و سپس گزینه Remove Break Page را انتخاب کنید (شکل 61).

استفاده از هوش مصنوعی در اکسل

هوش مصنوعی در اکسل امکانات متنوعی را برای تحلیل داده، فرمول‌نویسی و خودکارسازی وظایف ارائه می‌دهد.

در این بخش، چهار قابلیت مهم که بدون نیاز به دانش برنامه‌نویسی قابل استفاده هستند، معرفی می‌شود.

Flash Fill (پرکردن سریع سلول‌ها): قابلیت Flash Fill یکی از ابزارهای هوشمند اکسل است که می‌تواند الگوهای داده را شناسایی کرده و اطلاعات را به‌طور خودکار تکمیل کند. این ویژگی زمانی مفید است که داده‌های موجود دارای الگوی مشخصی باشند و نیاز به پردازش سریع آن‌ها باشد.

Analyze Data (تحلیل خودکار داده‌ها): ابزار Analyze Data در اکسل از هوش مصنوعی برای تحلیل سریع داده‌ها استفاده می‌کند. این قابلیت به کاربر امکان می‌دهد تا بدون نیاز به دانش آماری پیشرفته، خلاصه‌ای از اطلاعات خود را دریافت کند.

Power Query (پاک‌سازی و آماده‌سازی هوشمند داده‌ها): Power Query یکی از ابزارهای قدرتمند اکسل برای مدیریت و پردازش داده‌های خام است. این ابزار امکان ترکیب، فیلتر کردن و تغییر شکل داده‌ها را بدون نیاز به کدنویسی فراهم می‌کند.

فرمول‌نویسی هوشمند با Chat GPT: یکی از ساده‌ترین روش‌ها برای نوشتن فرمول‌های اکسل، استفاده از ابزارهای مبتنی بر هوش مصنوعی مانند Chat GPT است. این ابزار به کاربر اجازه می‌دهد تا به‌جای جست‌وجو در منابع مختلف، مستقیماً سؤال خود را مطرح کرده و فرمول موردنظر را دریافت کند.

پر کردن سریع سلول‌ها

آقای محمدی در یک فایل اکسل به نام «Tel» اطلاعات تماس هنرجویان را ثبت کرده است. در ستون A کاربرگ «اطلاعات هنرجویان»، فیلد نام و نام‌خانوادگی ثبت شده است. آقای محمدی می‌خواهد لیست هنرجویان را براساس نام‌خانوادگی مرتب کند و برای انجام این کار، لازم است فیلد نام و نام‌خانوادگی را به‌صورت مجزا در لیست داشته باشد. او به پیشنهاد آقای فروتن، تصمیم گرفت از قابلیت Flash Fill (پرکردن سریع سلول‌ها) که یکی از امکانات هوش مصنوعی در اکسل می‌باشد طبق مراحل زیر، استفاده کند.

1- فایل «Tel» را باز کنید. در کاربرگ «اطلاعات هنرجویان» در کنار ستون نام و نام‌خانوادگی یک ستون درج کنید.

2- در ستونی که درج شده، در مقابل فیلد نام و نام‌خانوادگی اولین رکورد، نمونه‌ای از الگوی موردنظر که در اینجا نام‌خانوادگی هنرجو است تایپ کنید.

3- پس از تایپ اولین مقدار، کلید Enter را بزنید.

4- از زبانه Data قسمت Data Tools گزینه Flash Fill را انتخاب کنید. اکسل به‌صورت خودکار سایر مقادیر را بر اساس الگوی مشخص شده تکمیل می‌کند (شکل 62).