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

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

در زیر کاربرگی را که از آن استفاده می‌کنیم، مشاهده می‌نمایید:

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

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

قالب‌بندی محدوده داده‌ها به عنوان یک جدول

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

برای فرمت یک محدوده به عنوان یک جدول، محدوده سلول‌ها را انتخاب کنید و بر روی Insert > Table کلیک کنید.

صحت محدوده داده‌ها را بررسی کنید و پس از اطمینان از وجود هدر در ردیف اول این محدوده، بر روی “OK” کلیک کنید.

محدوده در حال حاضر به عنوان یک جدول، فرمت شده است. نام‌گذاری جدول باعث می‌شود که هنگام ایجاد جداول محوری، نمودارها و فرمول‌ها در آینده راحت‌تر بتوانید به آن مراجعه کنید.

بر روی تب “Design” زیر ابزار جدول کلیک کنید و یک نام را در جعبه‌ای که در ابتدای نوار ظاهر شده است، وارد کنید. جدول زیر به نام “Sales” نام‌گذاری شده است.

همچنین در صورت تمایل می‌توانید ظاهر جدول را از همین بخش تغییر دهید.

ایجاد یک جدول محوری برای نمایش درصد تغییر

اکنون می‌خواهیم بحث را با ایجاد جدول محوری ادامه دهیم. از داخل جدول جدید، بر روی Insert > PivotTable کلیک کنید.

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

تاریخ‌ها را به ماه دسته‌بندی کنید

فیلد تاریخی را که می‌خواهید دسته‌بندی کنید به داخل ناحیه سطرهای جدول محوری، بکشید. در این مثال نام فیلد Order Date است.

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

گروه‌هایی که می‌خواهید استفاده کنید را انتخاب نمایید. در این مثال تنها سال‌ها و ماه‌ها انتخاب شده‌اند.

اکنون سال و ماه فیلد‌هایی هستند که می‌توان برای آنالیز از آن‌ها استفاده کرد. ماه‌ها هنوز هم با عنوان Order Date نام‌گذاری شده‌اند.

فیلدهای مقادیر را به جدول محوری اضافه کنید

فیلد سال را از Rows به قسمت Filter منتقل کنید. با این کار، کاربر می‌تواند به جای شلوغ کردن جدول با اطلاعات بیش از حد، جدول محوری را برای یک‌ سال فیلتر کند. فیلد حاوی مقادیر (در این مثال Total sales) را که می‌خواهید به محاسبه و ارایه تغییر در آن بپردازید به داخل ناحیه values بکشید.

تصویر زیر ممکن است خیلی شبیه به نتیجه نهایی به نظر نرسد؛ اما جدول به زودی تغییر خواهد کرد.

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

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

بر روی یکی از اعداد ستون اول راست‌کلیک کنید. از شورتکاتی که ظاهر می‌شود “Number Formatting” را انتخاب نمایید.

از Format Cells، فرمت “Accounting” را با 0 عدد اعشار، انتخاب کنید.

ایجاد ستون تغییر درصد

بر روی یک مقدار در ستون دوم راست‌کلیک کنید. بر روی “Show Values” بروید و گزینه “% Difference from” را انتخاب نمایید.

گزینه “(Previous)” را به عنوان Base Item انتخاب کنید. این بدین معنی است که ارزش ماه جاری همیشه با ارزش ماه قبل (فیلد Order Date) مقایسه می‌شود.

حال جدول محوری هم مقادیر و هم درصد تغییرات را نشان می‌دهد.

بر روی خانه‌ای که حاوی برچسب سطرها است، کلیک کنید و “Month” را به عنوان هدر آن ستون تایپ کنید. سپس بر روی خانه هدر برای ستون‌های دومین مقادیر کلیک کنید و نوع “Variance” را انتخاب نمایید.

چند فلش واریانس اضافه کنید

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

بر روی یکی از مقادیر دومین ستون کلیک کنید و سپس بر روی Home > Conditional Formatting > New Rule کلیک نمایید.
در پنجره Edit Formatting Rule که باز می‌شود، مراحل زیر را دنبال کنید‌:

1. گزینه “All cells showing “Variance” values for Order Date” را انتخاب کنید.
2. از منو کشویی Format Style، گزینه “Icon Sets” را انتخاب کنید.
3. از Icon Style، مثلث‌های قرمز، سبز و زرد را انتخاب کنید.
4. در ستون Type، گزینه لیست را به‌جای درصد به “Number” تغییر دهید. با این حساب ستون مقادیر به 0 تغییر می‌کند؛ درست همانطور که ما می‌خواهیم!

برای اعمال تغییرات بر روی جدول محوری‌، “OK” را کلیک کنید.

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

نوشته چگونه در اکسل درصد تغییر را با جدول محوری محاسبه نماییم؟ اولین بار در وب‌سایت فناوری پدیدار شد.

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

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

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

میانبرهای عمومی برنامه

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

Ctrl + N: ایجاد یک ورک‌بوک جدید

Ctrl + O: بازکردن ورک‌بوک‌های موجود

Ctrl + S: ذخیره کردن ورک‌بوک‌ها

F12: باز کردن کادر محاوره‌ای Save As

Ctrl + W: بستن یک ورک‌بوک

Ctrl + F4: بستن اکسل

F4: آخرین دستور یا عمل را تکرار می‌کند. به عنوان مثال، اگر آخرین چیزی که در سلول تایپ کرده‌اید، کلمه “سلام” باشد و یا اگر رنگ فونت را تغییر داده باشید، کافی است بر روی سلول دیگری کلیک کنید و با فشار دادن F4 این عمل را در سلول جدید تکرار کنید.

Shift + F11: یک برگه جدید را وارد می‌کند.

Ctrl + Z: اقدام انجام شده را لغو می‌کند.

Ctrl + Y: یک اقدام را دوباره انجام می‌دهد.

Ctrl + F2: صفحه را به حالت پیش‌نمایش چاپ تغییر می‌دهد.

F1: پنجره راهنما را باز می‌کند.

Alt + Q: شما را به جعبه “به من بگویید آن‌چه شما می‌خواهید انجام دهید” منتقل می‌کند.

F7: املا را بررسی می‌کند.

F9: تمام ورک‌شیت‌ها را در تمام ورک‌بوک‌های باز محاسبه می‌کند.

Shift + F9: محتویات ورک‌شیت‌‌های فعال را برآورد می‌کند.

Alt یا F10: راهنمای کلیدی را روشن یا خاموش می‌کند.

Ctrl + F1: نوار ریبون را نمایش داده یا پنهان می‌کند.

Ctrl + Shift + U: نوار فرمول را باز یا بسته می‌کند.

Ctrl + F9: پنجره ورک‌بوک را کوچک می‌کند.

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

Alt + F1: یک نمودار میله‌ای جاسازی شده بر اساس اطلاعات انتخاب شده در همان ورق ایجاد می‌کند.

Ctrl + F: یک عبارت خاص را در یک صفحه گسترده جستجو کرده و یا جایگزین می‌کند.

Alt + F: منوی زبانه File را باز می‌کند.

Alt + H: شما را به تب Home منتقل می‌کند.

Alt + N: تب Insert را برای‌تان باز می‌کند.

Alt + P: شما را به تب Page Layout منتقل می‌کند.

Alt + M: تب Formulas را برای‌تان باز می‌کند.

Alt + A: تب Data را برای شما باز می‌کند.

Alt + R: شما را به تب Review منتقل می‌کند.

Alt + W: شما را به تب View منتقل می‌کند.

Alt + X: شما را به تب Add-ins منتقل می‌کند.

Alt + Y: تب راهنما (Help) را برای شما باز می‌کند.

Ctrl + Tab: جابه‌جایی بین ورک‌بوک‌های باز

Shift + F3: برای وارد کردن یک تابع استفاده می‌شود.

Alt + F8: به منظور ایجاد، اجرا، ویرایش و یا حذف یک ماکرو استفاده می‌شود.

Alt + F11: پنجره ویرایشگر Visual Basic که برای ماکرونویسی مورد استفاده قرار می‌گیرد را باز می‌کند و شما می‌توانید بین یک ورک‌شیت یا سلول حرکت کنید.

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

کلیدهای جهت‌دار چپ و راست: برای حرکت یک سلول به سمت چپ یا راست استفاده می‌شوند.

Ctrl + کلیدهای جهت‌دار چپ و راست: حرکت به دورترین سلول چپ یا راست در ردیف

کلیدهای جهت‌دار بالا یا پایین: حرکت یک سلول به بالا یا پایین

Ctrl + کلیدهای جهت‌دار بالا یا پایین: به سمت سلول بالا یا پایین در ستون حرکت می‌کنید.

Tab: به سلول بعدی منتقل می‌شوید.

Shift + Tab: به سلول قبلی منتقل می‌شوید.

Ctrl + End: آخرین سلول حاوی اطلاعات را انتخاب می‌کند.

F5: با فشار دادن F5 پنجره Go to برای تایپ مختصات سلولی یا نام سلول برای شما باز می‌شود.

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

Ctrl + Home: حرکت به ابتدای یک برگه

Page Up/Down: حرکت یک صفحه به بالا یا پایین در یک برگه

Alt + Page Up / Down: حرکت یک صفحه به سمت راست یا چپ در یک برگه

Ctrl + Page Up/Down: انتقال به برگه قبلی یا بعدی

انتخاب سلول‌ها

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

Shift + Left / Right Arrow: با استفاده از این کلیدها می‌توانید انتخاب سلول را به چپ یا راست گسترش دهید.

Shift + Space: می‌توانید تمام سطر را انتخاب کنید.

Ctrl + Space: می‌توانید تمام ستون را انتخاب کنید.

Ctrl + Shift + Space: می‌توانید کل برگه را انتخاب کنید.

ویرایش سلول‌ها

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

F2: یک سلول را ویرایش می‌کند.

Shift + F2: یک تفسیر را به سلول اضافه برده یا آن را ویرایش می‌کند.

Ctrl + X: محتویات یک سلول، داده‌های انتخاب شده یا محدوده سلولی انتخاب شده را کات می‌کند.

Ctrl + C یا Ctrl + Insert: محتویات یک سلول، داده‌های انتخاب شده یا محدوده سلول انتخابی را کپی می‌کند.

Ctrl + V یا Shift + Insert: محتویات سلول، داده‌های انتخاب شده یا محدوده سلول انتخاب شده را جای‌گذاری می‌کند.

Ctrl + Alt + V: جعبه محاوره‌ای ویژه جای‌گذاری را برای شما باز می‌کند.

Delete: محتویات یک سلول، داده‌های انتخاب شده یا محدوده سلول انتخابی را حذف می‌کند.

Alt + Enter: به منظور نوشتن مطلب بیشتر از یک خط در سلول استفاده می‌شود.

F3: اگر سلول‌ها در برگه نام‌گذاری شده باشند، نام سلول را جای‌گذاری می‌کند.

Alt + H + D + C: برای حذف ستون استفاده می‌شود.

Esc: ورودی یک سلول یا نوار فرمول را لغو می‌کند.

Enter: یک ورودی را در یک سلول یا نوار فرمول تکمیل می‌کند.

قالب‌بندی سلول‌ها

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

Ctrl + B: با استفاده از آن می‌توانید محتویات یک سلول، داده‌های انتخاب شده یا محدوده سلول انتخابی را برجسته یا غیربرجسته کنید.

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

Ctrl + U: با استفاده از آن می‌توانید در زیر محتویات یک سلول، داده‌های انتخاب شده یا محدوده سلول انتخاب شده یک خط قرار دهید و یا آن را حذف کنید.

Alt + H + H: با استفاده از آن قسمت fill color از بخش font برای شما انتخاب می‌شود.

Alt + H + B: با استفاده از آن می‌توانید یک حاشیه را به سلول موردنظر اضافه کنید.

Ctrl + Shift + &: با استفاده از آن می‌توانید یک حاشیه خارجی را به سلول موردنظر اضافه کنید.

Ctrl + Shift + _ (Underline): برای حذف حاشیه خارجی سلول استفاده می‌شود.

Ctrl + 9: به منظور مخفی کردن ردیف‌های انتخاب شده استفاده می‌شود.

Ctrl + 0: به منظور مخفی کردن ستون‌های انتخاب شده استفاده می‌شود.

Ctrl + 1: جعبه متن Format Cells را برای شما باز می‌کند.

Ctrl + 5: برای اعمال یا حذف strikethrough به متن جاری استفاده می‌شود.

Ctrl + Shift + $: به منظور اعمال فرمت ارز استفاده می‌شود.

Ctrl + Shift +٪: به منظور اعمال فرمت درصد (٪) استفاده می‌شود.

کلام آخر

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

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

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

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

مجموعه آموزشی The Ultimate Excel Bootcamp Bundle، به شما کمک خواهد کرد تا خود را به سطح یک فرد حرفه‌ای در اکسل برسانید. دسترسی به امکانات این مجموعه که به صورت مادام‌العمر عرضه می‌شود، با بیش از ۱۳۰۰ دلار تخفیف (۹۶ درصد تخفیف) تنها ۵۰ دلار هزینه دارد.

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

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