نکته

 روش ساختن لیست های کشویی

اگر می خواهید مقادیر ورودی را برای کاربران محدود کنید، منوهای کشویی راه حل خوبی برای این کار هستند. یک سلول خالی را انتخاب کنید، از تب Data و مطابق تصویر Data Validation را برگزینید. در تب Settings پنجره ظاهر شده گزینه List را انتخاب کنید، اکنون با انتخاب سلول هایی که مقادیر مورد نظر شما در آن است .

نمایش متن های طولانی در سلول ها

در حالت پیش فرض وقتی متنی طولانی وارد یک سلول می کنید، اگر محتوای مورد نظر بیشتر از اندازه سلول باشد روی سلول های دیگر کشیده می شود که ظاهر خوشایندی ندارد. در ادامه ترفندهای کاربردی اکسل باید گفت برای حل این مشکل روی سلول مورد نظر کلیک راست کرده و Format Cells را بزنید، در تب Alignment گزینه Wrap Text را انتخاب و ok کنید. طول و عرض سطر مورد نظر را نیز می توانید به دلخواه خود تغییر دهید.

نمایش تب Developer

در ادامه ترفندهای کاربردی اکسل به تب Developers می رسیم، این تب به صورت پیش فرض در اکسل مخفی است، اگر نیاز داشته باشید تا کارهایی مثل نوشتن ماکروها را تجربه کنید به وجود این تب نیاز خواهید داشت، برای نمایش آن اگر اپلی هستید مطابق تصویر پنجره Preference را باز کنید و Ribbon & Toolbar را کلیک کنید و اگر ویندوزی هستید از منوی فایل و انتخاب Options و انتخاب Customize Ribbon اقدام کنید.

نحوه استفاده از VLOOKUP

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

4. ایجاد و مدیریت نمودارها

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

 ساخت جدول

در این نکته می‌خواهیم مطلبی را به شما آموزش دهیم که شاید در نگاه اول کاربرد آن برایتان نامعلوم باشد. چون کاربرگ‌های (Sheet) اکسل دارای تعداد بسیار زیادی خانه هستند. اکثر کاربرانی که با امکانات ویژه اکسل آشنایی ندارند‌ ممکن است فکر کنند این خانه‌ها همگی جدول هستند. اما در واقع این خانه‌ها دارای هیچ‌یک از خاصیت‌های ویژه جداول نیستند‌. ما با رسم جدول بخشی از سلول‌های کاربرگ جاری را از باقی سلول‌ها جدا می‌کنیم و می‌توانیم توابع و فرمول‌هایی را که می‌نویسیم محدود به همان بخش کنیم. در عین حال از فرمول‌نویسی برای تک‌تک سلول‌ها جلوگیری کنیم و با یک‌بار نوشتن فرمول و تابع، آن را برای تمامی سلول‌های مشابه در جدول به کار بگیریم. یکی دیگر از ویژگی‌های مهم جدول، ردیف Total است که همیشه در آخرین ردیف قرار می‌گیرد و کمک می‌کند تا فرمول‌هایی را برای تمامی سلول‌های یک ستون داشته باشیم. مثلا می‌توانیم مقدار کل را برای داده‌های یک سلول محاسبه کنیم. از دیگر ویژگی‌های جدول می‌توانیم به خاصیت فیلترینگ داده‌ها اشاره‌کنیم که توسط آن می‌توانیم خروجی‌های کاربردی بسازیم و با ترکیب فیلترینگ جدول و نمودار، یک شکل ویژه از اطلاعاتمان را به نمایش بگذاریم.

نحوه صحیح پرینت گرفتن

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

نمایش درست اطلاعات در صفحه مانیتور شاید خیلی مهم باشد، اما نمایش اطلاعات روی برگه چاپی نیز اهمیت ویژه‌ای دارد. زیرا هنگام ارائه اطلاعات به‌صورت چاپی، نظم صفحات می‌تواند تأثیر زیادی در انتقال اطلاعات داشته باشد. ویدیوی زیر به شما کمک می‌کند تا بتوانید بهترین حالت نمایشی در پرینت را برای اسناد تنظیم کنید.

اضافه کردن شیت با استفاده از کلید های ترکیبی Shift+F11

۵ . کپی کردن و پر کردن سلول ها با روشی جالب . بدین شکل که اگر در یک سطر یک بازه را انتخاب کنید و کلید های ترکیبی Ctrl+R را بزنید محتوای سمت چپ ترین خانه انتخابی در تمام بازه سمت راست کپی می شود . و برای ستون هم محتوای بالاترین سلول انتخابی با استفاده از کلید های ترکیبی Ctrl+D در زیر آن کپی می شود.

۱ ۲ ۳

Sub excellearn() Sheets.Select End sub

مبانی فرمول نویسی

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

توجه دادشته باشید که فرمول را در سلولی بنویسید که می خواهید پاسخ آن ظاهر شود. برای انجام این تمرین به برگه “simple” در پرونده اکسل رایگان که دانلود کردید مراجعه کنید.

مثال: مقادیر B2 ، B3 و B4 را جمع کنید و مقدار کل را در B6 قرار دهید.

در B6 فرمول زیر را وارد کنید:

= B2 + B3 + B4

رای محاسبات پیچیده تر، از توابع استفاده کنید

اگر می خواهید محاسباتی انجام دهید که فراتر از یک فرمول ساده هستند، از یک تابع در فرمول خود استفاده کنید. اکسل حدود ۴۰۰ تابع دارد، بنابراین می توانید بر حسب نیاز خود گزینه مناسب را پیدا کنید. توابعی برای تجارت، آما، امور مالی، تاریخ و زمان، متن، اطلاعات اسناد و موارد دیگر وجود دارد.

ساختار تمام توابع به شکل زیر است:

= نام تابع (……)

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

مثال: تعداد و میانگین ستون اعداد را پیدا کنید. برای انجام این تمرین به برگه “functions” در پرونده اکسل رایگان مراجعه کنید.

در B10 تابع زیر را وارد کنید:

= SUM (B2: B8)

= AVERAGE (B2: B8)

در B11 تابع زیر را وارد کنید:

 

فیلترینگ و مرتب سازی داده

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

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

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

مثال: یک لیست بر اساس نام خانوادگی، شهر یا یک ستون دیگر به ترتیب صعودی یا نزولی مرتب کنید. برای انجام این تمرین به برگه “filter and sort” در پرونده

اکسل رایگان مراجعه کنید.

قدم اول: ساخت Pivot Table

برای ساخت Pivot Table کافی است بر روی اطلاعاتی که دارید کلیک نمایید و از تب Insert بر روی گزینه Pivot Table کلیک نمایید. ولی توصیه می کنم قبل از آن اطلاعات تان اگر به صورت داده ساده هست آن ها را به Table تبدیل کنید و سپس سراغ Pivot Table بروید تا بروزرسانی های داده هایتان راحت تر انجام بگیرد.

مراحل گزارش گیری ساده در اکسل:

مرحله اول:

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

مرحله دوم:

روی برگه “Insert” کلیک کنید، سپس روی “Line” و سپس نمودار Line 2-D کلیک کنید. در کادر مربع خالی راست کلیک کرده و به “انتخاب داده …” داده انتخاب شده در این مثال هدف، نام دانش آموز و نمرات اقدام کنید.

در کنار مربع در کادر Chart Data Range کلیک کنید. داده‌های مورد نظر را در نمودار برجسته کنید. روی “OK” کلیک کنید. با به روزرسانی نمرات و اهداف، داده‌های جدید را برای گزارش گیری در اکسل اضافه کنید.

مراحل گزارش گیری پیشرفته در اکسل:

در این بخش چگونگی تهیه گزارش پیشرفته قابل چاپ از صفحه گسترده اکسل با استفاده از عملکردMail Merge در Microsoft Word 2011، بدون وقت و تلاش زیاد، توضیح داده می‌شود.

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

 

 

مرحله اول: با Word شروع کنید.

یک سند جدید را در Microsoft Word باز کنید. هر متنی را که می‌خواهید در گزارش پیشرفته اکسل ظاهر شود را تایپ کنید. به عنوان مثال: موارد مشترک ممکن است شامل نام دوره، تاریخ و برچسب‌های توصیفی مانند: نام، تعداد دانشجو، میانگین امتحان، میانگین تکالیف خانگی و جمع فعلی باشد.

مرحله دوم: از مدیر ادغام نامه(Mail Merge Manager) استفاده کنید.

در مرحله بعد، برای پیوند یک صفحه گسترده اکسل به سند تازه ایجاد شده Word، از منوی کشویی Tools در Word روی Mail Merge Manager کلیک کنید. منوی پاپ آپ که در سمت چپ نشان داده شده است، ظاهر می‌شود. عملکرد ادغام نامه در Word 2008 نیز موجود است.

با استفاده از مراحل زیر گزارش گیری در اکسل را ادامه دهید.

1. نوع سند را انتخاب کنید: ایجاد نامه جدید(Create New)> فرم نامه(Form Letters)
2. فهرست گزارش گیرندگان را انتخاب کنید: لیست را دریافت کنید(Get List)> منبع باز کردن داده ها(Open Data Source)> برگه Excel خود را انتخاب کنید. هنگامی که از شما خواسته شد، کل صفحه کار را انتخاب کنید.
3. قراردهی متغیرهای موجود در ورد. مشخصات مخاطبین و متغیرهای دیگر را در سند ورد خود به مکان‌های مورد نظر بکشید و رها کنید. به عنوان مثال، قبل از شروع مدیر ادغام نامه(Mail Merge Manager) Quiz average field را در کنار برچسب توصیفی مربوطه بکشید و رها کنید.

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

4. با کلیک روی نماد ادغام(Merge )به چاپگر(Printer) در ردیف ۶ پالت ابزار Mail Merge، ادغام را کامل کنید.

 

مرحله سوم: چاپ

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

مرحله چهارم: بازخورد مشروط

برای افزودن خودکار بازخورد کتبی که نشان دهنده رتبه هر دانش آموز است، فرمول‌های مشروط را در صفحه گسترده Excel خود وارد کنید. به عنوان مثال، اظهارات شرطی “لطفاً من را بعد از کلاس ببینید.” را برای هر دانش آموز با نمره کمتر از ۷۰ در ستون F، اظهارات”سخت کار کن!” برای هر دانش آموز با نمره ۷۰ یا بالاتر را مشخص کنید. عبارت “شما عالی کار می‌کنید!” برای هر دانش آموز با نمره بالاتر از ۸۰ اضافه می‌شود.

مرحله پنجم: نکته صرفه جویی در وقت

برای صرفه جویی در وقت کپی و چسباندن(pasting ) فرمول‌ها در اکسل، با انتخاب فرمول مورد نظر جهت کپی مجدد، از عملکرد پر کردن خودکار استفاده کنید. برای این کار به گوشه سمت راست پایین سلول انتخابی حرکت کنید. هنگامی که نماد موس به یک علامت سیاه و سفید به همراه “+” تغییر کند، دوبار کلیک کنید. اطلاعات موجود در سلول انتخابی در سلول‌های زیرین کپی و درج می‌شود. هر گونه اطلاعات ارجاع شده به طور خودکار تنظیم می‌شود. بنابراین، F2 در فرمول های فوق به صورت خودکار به F3 ، F4 ، F5 و غیره تبدیل می‌شود. این قابلیت اجازه می‌دهد تا بیانیه‌های بازخورد نمره کلاس هر دانش آموز منعکس گردد.

نتیجه نهایی گزارش گیری در اکسل از نوع پیشرفته:

اگر قبلاً نمرات موجود را در اکسل ثبت کرده‌اید، گزارش‌های پیشرفته به سرعت و بدون تلاش زیاد ایجاد می‌شوند. با بازخورد خودکار مشروط، ارائه پشتیبانی اضافی به دانشجویان در معرض خطر عدم قبولی در دروس سهل می‌شود.

 

نحوه گزارش گیری در اکسلMicrosoft Excel 2016:

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

در اکسل ۲۰۱۶، کاربران متوجه خواهند شد که روش‌های بی‌شماری برای سازماندهی و تجسم سوابق خود دارند. استفاده از این گزینه‌ها  به شما امکان می‌دهد جداول و نمودارهایی را در کنار هم قرار دهید تا گزارش‌هایی را تهیه کنید که ارزش بالایی دارند.

 

 

انواع اشکال گزارش گیری در اکسل:

نمونه اول:  نمودار پایه و ایجاد جدول

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

نحوه تهیه گزارش پیش بینی اولیه:

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

گزارش‌های پیش بینی برای محاسبه پیش‌بینی‌های فروش، رشد یا درآمد مفید هستند.

نمونه دوم: نحوه ایجاد نمودارهای سلسله مراتبی

1. سلول درون جدول داده‌ها را انتخاب کنید.
2. روی Insert در نوار کلیک کنید.
3. بر روی نمودار سلسله مراتب در زیر گروه نمودارها کلیک کنید.
4. بین نمودار TreeMap یا Sunburst، یکی را انتخاب کنید.
5. برای افزودن یا حذف عناصر نمودار مانند عنوان، برچسب داده‌ها و غیره روی علامت + (به علاوه) کلیک کنید.
6. برای سفارشی کردن ظاهر نمودار، روی پیکان سمت راست برای هر عنصر کلیک کنید.

گروه Charts یک روش مؤثر برای یافتن نمودار مناسب برای داده‌های شما است. در حقیقت Excel 2016 گزینه Charts دارد که به شما امکان می‌دهد از طریق نمودارهای لیست کوتاه یا از طریق تمام نمودارهای موجود، نوع مناسب را انتخاب کنید.

نمونه سوم: نحوه ایجاد PivotTables

این گزارش گیری در اکسل به کاربران این امکان را می‌دهد تا داده‌ها را در ستون‌ها و ردیف‌ها مرتب سازی و سازماندهی مجدد کنند تا موثرترین نما را پیدا کنند. این امر به ویژه هنگامی که با داده‌های گسترده کار می‌کنید بسیار مفید است.

نحوه ایجاد یک جدول PivotTables:

1. سلول را در محدوده جدول یا داده‌های منبع انتخاب کنید.
2. در نوار Insert به بخش جداول بروید.
3. توصیه PivotTable را انتخاب کنید.
4. انواع ارائه شده PivotTables را مرور کنید.
5. روی PivotTable مورد نظر خود کلیک کنید.
6. برای تولید بر روی OK کلیک کنید.
7. گزینه‌های Seasonality را انتخاب کنید.
8. محدوده زمانی و ارزش را تغییر دهید.
9. برای پر کردن هر نقطه از دست رفته صفر یا درون یابی را انتخاب کنید.
10. Create را برای اتمام گزارش گیری در اکسلانتخاب کنید.