میزهای محوری برای همه خوب هستند - آنها به سرعت محاسبه می‌شوند و به شکلی انعطاف‌پذیر پیکربندی می‌شوند و در صورت لزوم می‌توان طرح را به زیبایی در آنها پیچید. اما چند مگس نیز در پماد وجود دارد، به ویژه، عدم توانایی در ایجاد خلاصه، که در آن منطقه ارزش نباید شامل اعداد، بلکه متن باشد.
بیایید سعی کنیم این محدودیت را دور بزنیم و در موقعیتی مشابه یک "چند عصا" پیدا کنیم.
فرض کنید شرکت ما محصولات خود را در کانتینر به چندین شهر در کشور ما و قزاقستان حمل می کند. کانتینرها بیش از یک بار در ماه ارسال نمی شوند. هر ظرف دارای یک عدد الفبایی است. به عنوان داده های اولیه، یک جدول استاندارد لیست تحویل وجود دارد که باید از آن نوعی خلاصه تهیه کنید تا به وضوح تعداد کانتینرهای ارسال شده به هر شهر و هر ماه را ببینید:
برای راحتی، بیایید جدول با داده های اولیه را از قبل با استفاده از دستور "هوشمند" کنیم صفحه اصلی – قالب بندی به صورت جدول (صفحه اصلی - قالب بندی به عنوان جدول) و اسمش را بگذار تحویل برگ سازنده (طرح). در آینده، این زندگی را ساده می کند، زیرا. استفاده از نام جدول و ستون های آن به طور مستقیم در فرمول ها امکان پذیر خواهد بود.
روش 1. ساده ترین - استفاده از Power Query
Power Query یک ابزار فوق العاده قدرتمند برای بارگذاری و تبدیل داده ها در اکسل است. این افزونه از سال 2016 به طور پیش فرض در اکسل ساخته شده است. اگر اکسل 2010 یا 2013 دارید، می توانید آن را به طور جداگانه دانلود و نصب کنید (کاملا رایگان).
کل فرآیند، برای وضوح، من گام به گام در ویدیو زیر تجزیه و تحلیل کردم:
اگر امکان استفاده از Power Query وجود ندارد، می توانید راه های دیگری را انتخاب کنید - از طریق جدول محوری یا فرمول ها.
روش 2. خلاصه کمکی
بیایید یک ستون دیگر به جدول اصلی خود اضافه کنیم، جایی که با استفاده از یک فرمول ساده تعداد هر سطر در جدول را محاسبه می کنیم:
بدیهی است که -1 مورد نیاز است، زیرا ما یک هدر یک خطی در جدول خود داریم. اگر جدول شما در ابتدای برگه نیست، می توانید از فرمول کمی پیچیده تر، اما جهانی استفاده کنید که تفاوت اعداد ردیف فعلی و هدر جدول را محاسبه می کند:
حال به صورت استاندارد بر اساس داده های خود یک جدول محوری از نوع دلخواه می سازیم اما در قسمت مقدار فیلد را رها می کنیم. شماره خط به جای آنچه می خواهیم ظرف:
از آنجایی که ما در یک ماه چندین کانتینر در یک شهر نداریم، خلاصه ما در واقع نه مقدار، بلکه شماره خطوط کانتینرهای مورد نیاز را نشان می دهد.
علاوه بر این، می‌توانید کل و فرعی را در برگه خاموش کنید سازنده - مجموع کلی и کل حجم (طراحی - کل کل، مجموع فرعی) و در همان مکان خلاصه را با دکمه به یک طرح جدول راحت تر تغییر دهید گزارش ماکت (چیدمان گزارش).
بنابراین، ما در نیمه راه به نتیجه رسیده ایم: جدولی داریم که در آن، در تقاطع شهر و ماه، یک شماره ردیف در جدول منبع وجود دارد، جایی که کد ظرف مورد نیاز ما قرار دارد.
حالا بیایید خلاصه را کپی کنیم (در همان برگه یا دیگری) و آن را به عنوان مقادیر جای گذاری کنیم، و سپس فرمول خود را در ناحیه مقدار وارد کنیم، که کد ظرف را با شماره خط موجود در خلاصه استخراج می کند:
عملکرد IF (اگر)، در این حالت بررسی می کند که سلول بعدی در خلاصه خالی نباشد. اگر خالی است، یک رشته متن خالی "" را تولید کنید، یعنی سلول را خالی بگذارید. اگر خالی نیست، از ستون استخراج کنید ظرف جدول منبع تحویل محتوای سلول بر اساس شماره ردیف با استفاده از تابع شاخص (فهرست مطالب).
شاید تنها نکته نه چندان واضح در اینجا کلمه دوگانه باشد ظرف در فرمول چنین شکل عجیبی از نوشتن:
تدارکات[[ظرف]:[ظرف]]
... فقط برای ارجاع به ستون مورد نیاز است ظرف مطلق بود (مانند یک مرجع با علائم $ برای جداول معمولی "غیر هوشمند") و هنگام کپی کردن فرمول ما به سمت راست به ستون های همسایه نمی لغزید.
در آینده، هنگام تغییر داده ها در جدول منبع تحویل، باید به یاد داشته باشیم که با کلیک راست بر روی آن و انتخاب دستور، خلاصه کمکی خود را با شماره خطوط به روز کنیم. به روز رسانی و ذخیره (تازه کردن).
روش 3. فرمول ها
این روش نیازی به ایجاد جدول محوری میانی و به روز رسانی دستی ندارد، اما از "سلاح سنگین" اکسل استفاده می کند - تابع سامسلیمن (SUMIFS). به جای جستجوی اعداد ردیف به صورت خلاصه، می توانید آنها را با استفاده از این فرمول محاسبه کنید:
با مقداری حجیم بودن خارجی، در واقع، این یک مورد استفاده استاندارد برای تابع جمع انتخابی است سامسلیمنA که تعداد ردیف های شهر و ماه را جمع می کند. باز هم، از آنجایی که ما در یک ماه چندین کانتینر در یک شهر نداریم، عملکرد ما در واقع نه مقدار، بلکه شماره خط را نشان می دهد. و سپس تابعی که قبلاً از روش قبلی آشنا بودیم شاخص همچنین می توانید کدهای کانتینر را استخراج کنید:
البته در این حالت دیگر نیازی نیست به فکر به روز رسانی خلاصه باشید، اما در جداول بزرگ، تابع SUMMESLI می تواند به طور قابل توجهی کند باشد. سپس باید به‌روزرسانی خودکار فرمول‌ها را خاموش کنید یا از روش اول - جدول محوری استفاده کنید.
اگر ظاهر خلاصه برای گزارش شما چندان مناسب نیست، می توانید اعداد ردیف را از آن به جدول نهایی استخراج کنید نه به طور مستقیم، همانطور که ما انجام دادیم، بلکه با استفاده از تابع get.pivot.table.data (get.pivot.data). نحوه انجام این کار را می توان در اینجا یافت.
- نحوه ایجاد گزارش با استفاده از جدول محوری
- نحوه تنظیم محاسبات در جداول محوری
- شمارش انتخابی با SUMIFS، COUNTIFS و غیره