آموزش اکسل هفته ۲۲

آموزش اکسل هفته ۲۲ – ایران ترجمه – Irantarjomeh

مقالات ترجمه شده آماده گروه کامپیوتر

مقالات ترجمه شده آماده کل گروه های دانشگاهی

مقالات

چگونگی سفارش مقاله

الف – پرداخت وجه بحساب وب سایت ایران ترجمه(شماره حساب)ب- اطلاع جزئیات به ایمیل irantarjomeh@gmail.comشامل: مبلغ پرداختی – شماره فیش / ارجاع و تاریخ پرداخت – مقاله مورد نظر --مقالات آماده سفارش داده شده پس از تایید به ایمیل شما ارسال خواهند شد.

آموزش فراگیری اکسل از طریق آقای اکسل

هفته ۲۲

استفاده از فرمت اتوماتیک با جداول محوری

مشکل: بواسطه طبیعت دینامیک جداول محوری، امکان فرمت آنها نسبتاً سخت می‌باشد. همانگونه که در شکل ۹۴۳ نشان داده شده است ممکن است خواسته باشید که هدینگ‌ها یا عناوین D4:F4 را با توجه به مضامین خاص فرمت نمایید،‌ چرا که آنها بعنوان رئوس عناوین ستون می‌باشند. با این وجود، بهنگامی که جدول محوری را به شکل دیگر در می‌آورید این فرمتها از بین خواهد رفت.

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

۱- از دکمه گزارش فرمت در قسمت نوار ابزار جدول محوری، همانگونه که در شکل ۹۴۴ نشان داده شده است استفاده کنید.

جعبه دیالوگ فرمت اتوماتیک نشان دهنده ۶ چارچوب گزارش مختلف می‌باشد. این مثالها نشان دهنده فرمت بر روی یک گزارش همراه با ۴ فیلد در ناحیه ستون می‌باشند. همانگونه که در شکل ۹۴۵ نشان داده شده است درگزارش یک آنها به شما نشان می‌دهند که داخلی ترین سطح (منطقه)معمولاً دارای فونت تیره رنگ بر روی زمینه سفید می‌باشند. مرحله بعد (ناحیه) دارای فونت سیاه و زمینه خاکستری می‌باشد. سطح بیرونی (Qtr) دارای فوت بولد یا ضخیم می‌باشد.

۲- همانگونه که در شکل ۹۴۶ نشان داده شده است، از نوار پیمایش برای دیدن فرمتها گزارش ۷ الی ۹ و جداول ۱ الی ۲ استفاده نمایید.

شکل ۹۴۷ نشان دهنده جداول ۳ الی ۸ می‌باشد.

 چند گزینه آخر جدول ۹، ۱۰ جدول محوری کلاسیک در شکل ۹۴۸ نشان داده شده است.

۳- یکی از موارد اخیر را انتخاب و OK را فشار دهید.

نتیجه: شما می‌توانید ظاهر سریعی را برای داده‌ها در نظر بگیرید. شکل ۹۴۹ گزارش ۶ را نشان می‌دهد.

در صورتی که شکل خاصی را دوست نداشته باشید، آیکن فرمت ریپورت را انتخاب نموده و شکل دیگری را انتخاب نمایید. شکل ۹۵۰ نشان دهنده جدول ۷ می‌باشد.

حتی با این فرمت‌ها، شما می‌توانید حالت معمول خود را داشته باشید. همانگونه که در شکل ۹۵۱ نشان داده شده است، فیلد محصول به ناحیه ستون حرکت می‌نماید، فیلد ناحیه حذف شده و فیلد مشتری در ناحیه ردیف اضافه می‌گردد.

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

آموزش اکسل هفته ۲۲

مشخص کردن فرمت عددی برای فیلد جدول محوری

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

استراتژی:

۱- یک فرمت عددی را برای فیلد محوری درنظر بگیرید. دکمه مجموع درآمد را کلیک نمایید تا آنکه جعبه محاوره‌ای فیلد جدول محوری باز شود.

۲- دکمه «عدد …» را انتخاب نمایید، همانگونکه در شکل ۹۵۳ نشان داده شده است. با انتخاب این دکمه نگارش خلاصه  فرمت سلولها همراه با تب عدد باز می‌شود.

۳- نسبت به انتخاب یک فرمت عددی مناسب اقدام و کلید ‌OK را براساس شکل ۹۵۴ انتخاب کنید.

نتیجه: همانگونه که در شکل ۹۵۵ نشان داده شده است، فیلد درآمد هم‌ اکنون فرمت انتخابی را همیشه نشان می‌دهد، بدون آنکه تغییرات جدول محوری را بشمار آورد.

نکات قابل توجه: یکی از مقوله‌های مربوط به فرمت اینگونه بیان شده است که می‌بایست نسبت به اضافه نمودن علامت پول در اولین ردیف و ردیف‌های جمع بانک اطلاعات اقدام شود. هیچ گونه راه مناسبی برای انجام این کار با جدول محوری وجود ندارد. از فرمت عددی متصل شده به دکمه محصول جهت نسبت دادن فرمت واحد پولی اقدام کنید. پس از آن اولین ردیف سلول‌ها را انتخاب وفرمت جدید را با استفاده از CTRL+1 انتخاب نموده تا آنکه جعبه محاوره‌ای فرمت سلول منطبق با شکل ۹۵۶ باز شود.

این امر بصورت اولیه همانگونه که در شکل ۹۵۷ نشان داده می‌شود محقق خواهد شد.

در صورتی که بعداً نسبت به تجدید توالی جدول محوری اقدام نمایید، نظیر سورت فیلد درآمد، فرمت خاص بجای ماندن در ردیف اول به جدول محوری انتقال داده می‌شود، همانند شکل ۹۵۸٫

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

آموزش اکسل هفته ۲۲

متوقف سازی جمع‌ها در جدول محوری

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

استراتژی: شما می‌توانید جمع‌های فرعی را برای هر فیلد ببندید.

۱- فیلد داده را دابل کلیک کنید. نسبت به تغییر تنظیمات زیر جمع‌ها از اتوماتیک به غیراتوماتیک اقدام نمایید، همانند شکل ۹۶۰٫

۲- فیلد ناحیه را دابل کلیک نمایید. زیر جمع‌ها را از حالت اتوماتیک به غیر اتوماتیک، همانگونه که در شکل ۹۶۱ نشان داده شده است، تغییر دهید.

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

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

حذف فضاهای خالی در فرمت رئوس مطالب یک جدول محوری

مشکل : شما نسبت به ساخت گزارش جدول محوری اقدام نموده و می‌خواهید تا این اطلاعات را بعنوان بانک اطلاعات در یک کارکتابی دیگر داشته باشید. گزارش جدول محوری همیشه از این فرمت رئوس مطالب استفاده می‌کند. این امر فوق العاده می‌باشد، اما برای آنالیز داده چندان جالب نخواهد بود. همانگونه که در شکل ۹۶۳ نشان داده شده است، شما نیاز خواهید داشت تا هدینگ ژانویه به نقطه A6:A13 کپی شود و برچسب شرق نیز به نقطهB6  و B7 کپی گردد.

استراتژی: جهت پر نمودن سلول‌های خالی در بخش رئوس مطالب جدول محوری، شما باید تنها اقدام به کپی مقادیر به جدول محوری نمایید. یک کاربرگ جدید را درج نمایید. A4:D111 را از جدول محوری کپی کنید. در برگ جدید، از کلید «کپی – چسباندن مخصوص» استفاده کنید تا آنکه جدول محوری را به مقادیر استاتیک همانگونه که در شکل ۹۶۴ نشان داده شده است تبدیل کنید.

در این حالت، شما می‌بایست سلول‌های خالی را در ستون‌های A و B با مقدار سلول بالا پر نمایید. این مراحل را پی گیرید.

۱- سلول‌های A3:B108 را همانگونه که در شکل ۹۶۵ نشان داده شده است انتخاب کنید.

۲- کلید F5 را فشار دهید تا دیالوگ «برو به» ظاهر شود. درگوشه سمت چپ پایین، دکمه «Special… button»، همانگونه که در شکل ۹۶۶ نشان داده شده است، را انتخاب کنید.

۳- در جعبه دیالوگ مربوطه، فضای خالی یا Blanks را از ستون اول انتخاب نموده و سپس OK را فشار دهید، مطابق با شکل ۹۶۷٫

۴- قسمت بعد تا اندازه‌ای گمراه کننده است اما در عین حال عملی می‌باشد. علامت مساوی را تایپ کنید. پیکان به سمت بالا را فشار دهید، منطبق با شکل ۹۶۸٫

۵- Ctrl+Enter را فشار دهیدو نتیجه را بر حسب آنچه در شکل ۹۶۹ نشان داده شده است ببینید.

در اینجا دلیل کارکرد مشخص می‌گردد. بهنگامی که علامت مساوی را تایپ می‌کنید، به اکسل خواهید گفت که درحال وارد کردن یک فرمول می‌باشید. با زدن پیکان به سمت بالا، اکسل این فرمول را بعنوان سلول بالای سلول جاری در نظر می‌گیرد. Ctrl+Enter را زده تا اکسل یک فرمول نسبی مشابه را در کلیه سلول‌های انتخابی وارد نماید. این مسئله حقیقتاً مطرح نمی‌باشد که کدام سلول بعنوان سلول فعال مد نظر است، البته به شرط آنکه شما بطور موفق کلیه سلول‌های خالی را در ابتدا انتخاب کرده باشید.

۶- مرحله بعد تبدیل کلیه این فرمول‌های جدید به مقادیر می‌باشد. بر این اساس می‌توانید از Ctrl+C و کلید کپی و چسباندن خاص – مقادیر مرتبط استفاده کنید اما نمی‌توانید از دستور Copy command در انتخاب‌های چندگانه استفاده نمایید، مطابق با شکل ۹۷۰٫

۷- مجدداً محدوده A3:B108 را انتخاب کنید. Ctrl+C را کپی بزنید. با مشخص کردن  Edit – Paste Special Values  می‌توان باعث شد تا فرمول‌ها به مقادیر تبدیل گردند.

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

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

دستورات بحث شده: Edit – Copy; Go To Special – Blanks; ، Ctrl+Enter

آموزش اکسل هفته ۲۲

استفاده از جدول محوری جهت مقایسه دو لیست

مشکل: شما دارای گزارشات پیش بینی سفارش هفته قبل هستید. بر این اساس یک گزارش پیش بینی جدید دریافت می‌دارید. همانگونه که در شکل ۹۷۲ نشان داده شده است، شما می‌بایست تعیین کنید که کدام پیش بینی جدید می‌باشد، کدام پیش‌بینیها تغیر یافته و کدام پیش‌بینی‌ها حذف شده‌اند.

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

• یک هدینگ را در سلول C3 اضافه نمایید. مقدار «هفته گذشته» را در نواحی C4:C19 همانگونه که در شکل ۹۷۳ مشخص شده است، بگنجانید.

۲- داده مربوطه را از ستون‌های E و F به A20 کپی کنید. در ناحیه C20:C37 مقدار این هفته را وارد نمایید، برحسب شکل ۹۷۴٫

۳- نسبت به ساخت یک جدول محوری اقدام کنید. فیلد مشتری را در ناحیه ردیف، فیلد منبع در ناحیه ستون و فیلد پیش‌بینی را در ناحیه داده قرار دهید.

۴- در گزینه‌های جدول محوری، عبارت « Grand Total for Rows» را ببندید. همانگونه که در شکل ۹۷۵ نشان داده شده است، شما باید مقایسه‌ای را برای دو لیست داشته باشید.

نتایج: برای هر سلول بدون ورودی و هفته قبل، پیش بینی جدید خواهد بود. برای هر پیش بینی بدون ورودی به ستون C، پیش بینی حذف می‌گردد. برای هر پیش بینی جایی که ستون‌های B و C منطبق نباشند، پیش بینی تغییر می‌نماید.

نکات حائز اهمیت: جالب توجه خواهد بود تا نسبت به اضافه نمودن یک فرمول به ستون D اقدام نماییم که نشان دهنده تفاوت بین هفته گذشته و این هفته خواهد بود. با این وجود، در صورتی که مشخص سازی یک سلول در جدول محوری با موس یا کلید‌های اشاره بهنگام ورود فرمول استفاده کنیم،‌اکسل بصورت اتوماتیک رفرانس B5 را به تابع GetPivotData تغییر می‌دهد. بجای داشتن یک فرمول ساده نظیر  یک فرمول پیچیده را با فرمول‌های GetPivotData همانند شکل ۹۷۶ به دست می‌آورید.

بهنگامی که نسبت به کپی این فرمول به D5 به D6 اقدام می‌کنید، دارای رفرانس نسبی نخواهید بود. همانگونه که در شکل ۹۷۷ نشان داده شده است. نتایج در بقیه ردیف‌ها غلط خواهد بود.

برای وارد نمودن ۲ فرمول معمولی خارج از جدول محوری دارای دو گزینه خواهید بود.

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

گزینه دوم: بطور جایگزین، شما می‌توانید یک کلید را به جعبه ابزار اضافه کنید تا بطور همیشگی نسبت به بستن توابع ساخت اتوماتیک GetPivotData اقدام شود. از منوی   « Tools – Customize» را انتخاب نمایید و پس از آن تب دستورات را برگزینید. در دسته‌بندی لیست‌های باز شده داده «Data» را انتخاب نمایید. و سپس به سمت پایین لیست دستورات بیایید. در  اینجا آیکنی برای ساخت GetPivotData خواهد بود. این آیکن را به وسیله ماوس گرفته و آنرا در نوار ابزار مطابق با شکل ۹۷۸ رها نمایید.

آموزش اکسل هفته ۲۲

 شما اکنون کلید ساخت توابع GetPivotData را بصورت اتوماتیک با انتخاب این قلم در نوار ابزار مطابق با شکل ۹۷۹ بسته‌اید.

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

دستورات بحث شده: Data – PivotTable; Generate GetPivotData