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

این مقاله حاوی ترفندهای ساده ای است که اجازه می دهد. آنها به ویژه برای کسانی که به گزارش مدیریت مشغول هستند، گزارش های تحلیلی مختلفی را بر اساس بارگیری از 1C و گزارش های دیگر تهیه می کنند و ارائه ها و نمودارهایی را برای مدیریت از آنها تشکیل می دهند. من تظاهر نمی کنم که یک تازگی مطلق هستم - به هر شکلی، این تکنیک ها احتمالاً در انجمن ها یا انجمن ها مورد بحث قرار گرفته اند.

جایگزین های ساده برای VLOOKUP و GLOOKUP اگر مقادیر مورد نظر شما در ستون اول جدول نیستند: LOOKUP، INDEX+MATCH

نتیجه:

V این موردعلاوه بر تابع CHAR (برای نمایش نقل قول ها)، از تابع IF (IF) استفاده می شود که به شما امکان می دهد متن را بسته به روند فروش مثبت تغییر دهید و تابع TEXT که به شما امکان نمایش یک شماره در هر فرمتی نحو آن در زیر توضیح داده شده است:

متن ( معنی؛ قالب)

فرمت در علامت نقل قول مشخص می شود به همان صورتی که اگر یک قالب سفارشی را در پنجره Format Cells وارد کرده باشید.

متون پیچیده تر نیز می توانند خودکار شوند. در عمل من، اتوماسیون نظرات طولانی اما معمولی در مورد گزارش مدیریت در قالب "INDICATOR کاهش / افزایش XX نسبت به برنامه داشت، عمدتاً به دلیل افزایش / کاهش FACTOR1 توسط XX، افزایش / کاهش در FACTOR2). توسط YY ..." با یک لیست متغیر از عوامل. اگر اغلب چنین نظراتی را می نویسید و روند نوشتن آنها قابل الگوریتم سازی است، ارزش دارد که یک بار با ایجاد یک فرمول یا یک ماکرو که حداقل بخشی از کار شما را نجات می دهد، گیج شوید.

نحوه نگهداری داده ها در هر سلول پس از ادغام

هنگام ادغام سلول ها، تنها یک مقدار حفظ می شود. اکسل هنگام تلاش برای ادغام سلول ها در مورد این هشدار می دهد:

بر این اساس، اگر فرمولی داشتید که به هر سلول بستگی دارد، پس از ترکیب آنها از کار می‌افتد (خطای #N/A در خطوط 3-4 مثال):

برای ادغام سلول‌ها و همچنان نگه داشتن داده‌ها در هر یک از آنها (شاید فرمولی داشته باشید، مانند این مثال انتزاعی؛ شاید بخواهید سلول‌ها را ادغام کنید، اما تمام داده‌ها را برای آینده نگه دارید یا عمداً آن‌ها را پنهان کنید)، سلول‌های موجود را ادغام کنید. کاربرگ را انتخاب کنید و سپس با استفاده از دستور "Format Painter" قالب بندی را به سلول هایی که باید ادغام کنید منتقل کنید:

چگونه از چندین منبع داده یک خلاصه بسازیم

اگر نیاز به ایجاد خلاصه ای از چندین منبع داده به طور همزمان دارید، باید آن را به نوار یا پانل اضافه کنید. دسترسی سریع PivotTable و PivotChart Wizard که این گزینه را دارد.

می توانید این کار را به صورت زیر انجام دهید: "File" → "Options" → "Quick Access Toolbar" → "All Commands" → "PivotTable and PivotChart Wizard" → "Add":

پس از آن، نماد مربوطه روی نوار ظاهر می شود، با کلیک بر روی آن، همان جادوگر را فرا می خواند:

وقتی روی آن کلیک می کنید، یک کادر محاوره ای ظاهر می شود:

در آن، شما باید مورد "در چندین محدوده ادغام" را انتخاب کنید و روی "بعدی" کلیک کنید. در پاراگراف بعدی می توانید "Create one page field" یا "Create page fields" را انتخاب کنید. اگر می‌خواهید خودتان نامی برای هر یک از منابع داده پیدا کنید، مورد دوم را انتخاب کنید:

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

پس از آن، در آخرین کادر محاوره ای، مشخص کنید که گزارش جدول محوری در کجا قرار می گیرد - در یک صفحه موجود یا جدید:

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

نحوه محاسبه تعداد دفعات متن A در متن B ("MTS تعرفه SuperMTS" - دو اتفاق از مخفف MTS)

در این مثال، چندین خط متن در ستون A وجود دارد و وظیفه ما این است که بفهمیم هر یک از آنها چند بار حاوی متن جستجوی واقع در سلول E1 است:

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

1. DLSTR (LEN) - طول متن را محاسبه می کند، تنها آرگومان متن است. مثال: DLSTR ("ماشین") = 6.
2. SUBSTITUTE - متن خاصی را در یک رشته متنی با متن دیگری جایگزین می کند. نحو: SUBSTITUTE ( متن; متن_قدیمی متن_جدید). مثال: SUBSTITUTE ("car";"car";"")= "موبایل".
3. UPPER - همه کاراکترهای یک رشته را به حروف بزرگ تغییر می دهد. تنها استدلال متن است. مثال: UPPER("machine") = "MACHINE". ما به این تابع برای انجام جستجوی بدون حروف بزرگ نیاز داریم. بالاخره UPPER("ماشین") = UPPER("ماشین")

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

DLSTR("MTS Tariff سوپر ام تی اس”) – DLSTR ("تعرفه فوق العاده") = 6

و سپس این تفاوت را بر طول رشته ای که به دنبال آن بودیم تقسیم کنید:

6 / DLSTR ("MTS") = 2

دو بار است که رشته "MTS" در اصل گنجانده شده است.

باقی مانده است که این الگوریتم را به زبان فرمول ها بنویسیم (اجازه دهید متنی را که در آن به دنبال رخدادها هستیم و با "مطلوب" - موردی که تعداد رخدادهای آن مورد علاقه ما است را با "متن" مشخص کنیم):

=(DLSTR( متن)-DLSTR(SUBSTITUTE(UPPER( متن);بالا( دلخواه);"")))/DLSTR( دلخواه)

در مثال ما، فرمول به صورت زیر است:

=(LEN(A2)-LONG(SUBSTITUTE(UPPER(A2)، UPPER($E$1)،"")))/LEN($E$1)

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

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

برای مثال هزینه های مالی یکسانی را در ماه های مختلف در نظر بگیرید.

ایجاد جداول محوری

برای ساختن چنین جدولی باید مراحل زیر را انجام دهید.

1. برای شروع، باید کاملا انتخاب شود.

1. سپس به تب "Insert" بروید. روی نماد "جدول" کلیک کنید. از منوی ظاهر شده "Pivot Table" را انتخاب کنید.

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

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

1. بلافاصله پس از آن، PivotTable Wizard به اندازه کوچکی جمع می شود. علاوه بر این، آن نیز تغییر خواهد کرد ظاهرمکان نما شما باید در هر مکان مناسب برای شما یک کلیک چپ ماوس را انجام دهید.

1. در نتیجه، ارجاع به سلول مشخص شده به طور خودکار جایگزین می شود. سپس باید روی نماد سمت راست پنجره کلیک کنید تا آن را به اندازه اصلی خود بازگردانید.

1. برای تکمیل تنظیمات، روی دکمه "OK" کلیک کنید.

1. در نتیجه یک قالب خالی برای کار با جداول محوری مشاهده خواهید کرد.

1. در این مرحله باید مشخص کنید که کدام فیلد خواهد بود:
   1. ستون
   2. رشته؛
   3. ارزش برای تجزیه و تحلیل

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

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

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

1. برای دیدن نتیجه کامل، می توانید ببندید نوار کناریتنظیمات. برای انجام این کار، فقط روی ضربدر کلیک کنید.

1. در نتیجه موارد زیر را مشاهده خواهید کرد. با استفاده از این ابزار می توانید میزان هزینه های هر ماه را برای هر مورد خلاصه کنید. علاوه بر این، اطلاعاتی در مورد نتیجه کل در دسترس است.

1. اگر میز را دوست ندارید، می توانید سعی کنید آن را کمی متفاوت بسازید. برای انجام این کار، باید فیلدها را در مناطق ساخت و ساز تغییر دهید.

1. دستیار ساخت را دوباره ببندید.

1. این بار، می بینیم که PivotTable بسیار بزرگتر شده است، زیرا اکنون ستون ها ماه نیستند، بلکه دسته بندی هزینه ها هستند.

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

برای این کار باید موارد زیر را انجام دهید اما ابتدا تمام اطلاعات را به صورت کامل انتخاب کنید.

1. به تب "Insert" بروید. سپس بر روی نماد "جدول" کلیک کنید. در منوی ظاهر شده، مورد دوم را انتخاب کنید.

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

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

1. شما می توانید آنچه را که بیشتر دوست دارید انتخاب کنید.

1. برای درج گزینه انتخاب شده کافیست بر روی دکمه "OK" کلیک کنید.

1. در نتیجه نتیجه زیر را دریافت خواهید کرد.

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

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

بیایید هر یک از آنها را با جزئیات بیشتری در نظر بگیریم.

با کلیک بر روی دکمه مشخص شده در اسکرین شات می توانید موارد زیر را انجام دهید:

• تغییر نام؛

• پنجره تنظیمات را باز کنید

در پنجره گزینه ها چیزهای جالب زیادی خواهید دید.

زمینه فعال

با این ابزار می توانید کارهای زیر را انجام دهید:

1. ابتدا باید یک سلول را انتخاب کنید. سپس بر روی دکمه "فیلد فعال" کلیک کنید. در منوی ظاهر شده، روی آیتم "Field Options" کلیک کنید.

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

1. علاوه بر این، می توانید قالب شماره را سفارشی کنید. برای این کار بر روی دکمه مربوطه کلیک کنید.

1. با این کار پنجره Format Cells ظاهر می شود.

در اینجا می توانید مشخص کنید که می خواهید نتیجه تجزیه و تحلیل اطلاعات را به چه شکلی نمایش دهید.

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

چسباندن برش

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

1. یک ستون را انتخاب کنید. سپس بر روی دکمه "Insert Slice" کلیک کنید.
2. در پنجره ای که ظاهر می شود، به عنوان مثال، یکی از فیلدهای پیشنهادی را انتخاب کنید (در آینده می توانید آنها را به تعداد نامحدود انتخاب کنید). پس از انتخاب چیزی، دکمه "OK" بلافاصله فعال می شود. روی آن کلیک کنید.

1. در نتیجه یک پنجره کوچک ظاهر می شود که می توان آن را به هر جایی منتقل کرد. همه چیز ممکن را ارائه خواهد داد ارزش های منحصر به فردکه در این زمینه هستند. به لطف این ابزار، فقط برای ماه های خاص (در این مورد) قادر خواهید بود مبلغ را برداشت کنید. به طور پیش فرض، اطلاعات برای همیشه نمایش داده می شود.

1. می توانید روی هر یک از موارد کلیک کنید. بلافاصله پس از آن، تمام مقادیر در قسمت sum تغییر می کند.

1. بنابراین، انتخاب هر دوره زمانی امکان پذیر خواهد بود.

1. در هر زمان، همه چیز را می توان به شکل اصلی خود بازگرداند. برای این کار روی نماد سمت راست کلیک کنید گوشه بالاییاین پنجره

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

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

به عنوان مثال، بیایید یک جدول کوچک با تاریخ های مختلف ایجاد کنیم.

سپس باید یک جدول محوری بسازید.

به تب "درج" برگردید. روی نماد "جدول" کلیک کنید. در منوی فرعی که ظاهر می شود، گزینه مورد نیاز خود را انتخاب کنید.

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

1. برای این کار کافی است کل جدول را انتخاب کنید.

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

1. ویرایشگر اکسل تنها یک گزینه را به ما ارائه می دهد، زیرا جدول بسیار ساده است (برای مثال نیازی به بیش از این نیست).

1. دوباره روی نماد "Insert Timeline" (که در برگه "Analysis" قرار دارد) کلیک کنید.

1. این بار هیچ خطایی وجود نخواهد داشت. از شما خواسته می شود که یک فیلد را برای مرتب سازی انتخاب کنید. کادر را علامت بزنید و روی دکمه OK کلیک کنید.

1. با تشکر از این، پنجره ای ظاهر می شود که در آن می توانید تاریخ مورد نظر را با استفاده از نوار لغزنده انتخاب کنید.

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

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

اگر تصمیم دارید فیلدهایی را که بنای ساختمان باید بر اساس آنها باشد را تغییر دهید، انجام این کار در تنظیمات بسیار ساده تر است به جای حذف جدول و ایجاد مجدد آن با در نظر گرفتن اولویت های جدید.

برای انجام این کار، روی نماد "منبع داده" کلیک کنید. سپس آیتم منو با همین نام را انتخاب کنید.

در نتیجه، پنجره ای ظاهر می شود که در آن می توانید مقدار اطلاعات مورد نیاز را مجدداً انتخاب کنید.

اقدامات

با استفاده از این ابزار شما قادر خواهید بود:

• میز را تمیز کن؛
• برجسته؛
• آن را حرکت دهید

محاسبه

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

این شامل:

• فیلد محاسبه شده؛

• شی محاسبه شده؛

• ترتیب محاسبه (فرمول های اضافه شده در لیست نمایش داده می شوند)؛

• نمایش فرمول ها (بدون اطلاعات، زیرا هیچ فرمول اضافه شده ای وجود ندارد).

در اینجا می توانید یک PivotChart ایجاد کنید یا نوع جدول توصیه شده را تغییر دهید.

با استفاده از این ابزار می توانید ظاهر فضای کاری ویرایشگر را شخصی سازی کنید.

با تشکر از این، شما قادر خواهید بود:

• نمایش نوار کناری را با لیستی از فیلدها سفارشی کنید.

• فعال یا غیرفعال کردن دکمه های مثبت/منفی؛

• نمایش سرصفحه های فیلد را سفارشی کنید.

هنگام کار با جداول محوری، علاوه بر برگه "Analysis"، یکی دیگر نیز ظاهر می شود - "Designer". در اینجا می توانید ظاهر شیء خود را تا زمانی که در مقایسه با حالت پیش فرض غیر قابل تشخیص باشد تغییر دهید.

قابل سفارشی سازی:

• جمع های فرعی:
  • نشان نده؛
  • نمایش تمام مجموع در پایین.
  • تمام مجموع ها را در سرصفحه نمایش دهید.

• نتایج کلی:
  • غیرفعال کردن برای سطرها و ستون ها؛
  • فعال کردن برای سطرها و ستون ها؛
  • فعال کردن فقط برای ردیف ها؛
  • فعال کردن فقط برای ستون ها

• طرح گزارش:
  • به شکل فشرده نشان داده شود؛
  • به شکل ساختار نشان داده شود.
  • نمایش به شکل جدول؛
  • تکرار تمام برچسب های عنصر.
  • برچسب های عناصر را تکرار نکنید.

• خطوط خالی:
  • بعد از هر عنصر یک خط خالی درج کنید.
  • بعد از هر عنصر خط خالی را حذف کنید.

• گزینه های سبک PivotTable (در اینجا می توانید هر مورد را فعال/غیرفعال کنید):
  • سرصفحه های خط؛
  • سرفصل های ستون؛
  • خطوط متناوب؛
  • ستون های متناوب

• سفارشی کردن سبک عناصر

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

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

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

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

برای این کار موارد زیر را انجام دهید.

1. روی مثلث کنار فیلد مورد نیاز کلیک کنید.
2. در نتیجه منوی زیر را مشاهده خواهید کرد. در اینجا می توانید گزینه مرتب سازی مورد نظر ("A به Z" یا "Z به A") را انتخاب کنید.

اگر گزینه استاندارد کافی نیست، می توانید روی مورد کلیک کنید " گزینه های اضافیمرتب سازی".

در نتیجه پنجره زیر را مشاهده خواهید کرد. برای تنظیمات دقیق تر، روی دکمه "پیشرفته" کلیک کنید.

همه چیز در اینجا به صورت خودکار تنظیم شده است. اگر این کادر را پاک کنید، می توانید کلید مورد نیاز خود را مشخص کنید.

جداول محوری در اکسل 2003

مراحل بالا برای ویرایشگرهای مدرن (2007، 2010، 2013 و 2016) مناسب است. V نسخه قدیمیهمه چیز متفاوت به نظر می رسد البته فرصت ها بسیار کمتر است.

برای ایجاد یک جدول محوری در اکسل 2003، باید موارد زیر را انجام دهید.

1. به بخش منوی "داده" بروید و مورد مناسب را انتخاب کنید.

1. در نتیجه، یک جادوگر برای ایجاد چنین اشیایی ظاهر می شود.

1. پس از کلیک بر روی دکمه "Next"، پنجره ای باز می شود که در آن باید محدوده ای از سلول ها را مشخص کنید. سپس دوباره بر روی "Next" کلیک کنید.

1. برای تکمیل تنظیمات، روی "Finish" کلیک کنید.

1. در نتیجه موارد زیر را مشاهده خواهید کرد. در اینجا باید فیلدها را به مناطق مناسب بکشید.

1. برای مثال، ممکن است نتیجه زیر را دریافت کنید.

بدیهی است که ایجاد چنین گزارش هایی در ویرایشگرهای مدرن بسیار بهتر است.

نتیجه

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

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

آموزش تصویری

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

ویدیوی مقاله را تماشا کنید:

گزارش های استاندارد موجود در ما ماژول‌های هوش تجاری (BI) به شما این امکان را می دهد که به 99٪ از محبوب ترین سوالات کاربران پاسخ دهید.

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

با تشکر از در قلب هر یک از ماژول های هوش تجاری (BI) ما یک مکعب OLAP قرار دارد، اتصال که کاربران می توانند به راحتی هر گزارشی را در اکسل با استفاده از Pivot Tables بسازند.

همچنین، گزارش‌های ساخته‌شده در اکسل روی مکعب‌های OLAP را می‌توان ذخیره کرد، و پس از باز شدن مجدد، به‌طور خودکار با آخرین داده‌ها به‌روزرسانی می‌شوند. سیستم هوش تجاری شرکتی (BI) .

نمونه هایی از گزارش های ساخته شده بر روی مکعب های OLAP:

شرح گزارشات:

(ویدئوی این مقاله را برای روند گزارشات ساختمان ببینید)

1. گزارش فروش به روبل و واحد:

• دوره زمانی: برای سال جاری
• شاخص ها: واقعیت فروش
• جزئیات: ستون/ربع، ماه
• جزئیات: خطوط/کانال های فروش، مشتریان، برندها، محصولات

2. گزارش ساختار فروش به روبل و واحد (فیلتر):

• دوره زمانی: برای سال جاری
• شاخص ها: فروش واقعی، فروش واقعی به صورت درصد
• جزئیات: ستون/ربع، ماه
• جزئیات: خطوط/کانال های فروش، برندها

3. گزارش اجرای طرح فروش به روبل و واحد (فیلتر):

• دوره زمانی: برای سال جاری
• شاخص ها: طرح فروش، واقعیت فروش، اجرای طرح
• جزئیات: ستون/ربع، ماه
• جزئیات: خطوط/کانال های فروش، مشتریان، دسته ها، محصولات

4. گزارش رشد فروش به روبل و واحد (فیلتر):

• دوره زمانی
• شاخص ها
• جزئیات: خطوط/مناطق فدرال، مناطق، دسته ها، محصولات

5. گزارش رشد فروش بر حسب زمان به روبل و واحد (فیلتر):

• دوره زمانی: برای سال جاری نسبت به سال قبل
• شاخص ها: فروش سال قبل، طرح فروش، فروش واقعی، اجرای طرح، رشد فروش به صورت مطلق و نسبی نسبت به سال قبل.
• جزئیات: ردیف / چهارم، ماه، دسته ها، محصولات

6. گزارش حساب های دریافتنی:

• دوره زمانی: برای تاریخ فعلی
• شاخص ها: مبلغ سند، کل مطالبات، مطالبات جاری، مطالبات معوق بر اساس دوره - تا 15 روز، تا 30 روز، تا 60 روز، حداکثر تا 90 روز، حداکثر تا 180 روز، حداکثر تا 365 روز یا بیشتر، ساختار مطالبات
• جزئیات: خطوط/کانال های فروش، طرف مقابل، تاریخ پرداخت سند، تاریخ سند، سند

7. گزارش پویایی مطالبات:

• دوره زمانی: برای سال جاری
• شاخص ها: کل مطالبات، مطالبات جاری، مطالبات معوق، ساختار مطالبات
• جزئیات: ردیف/سال، هفته، حساب

8. گزارش رسید پرداخت بر اساس تاریخ سند:

• دوره زمانی: برای سال جاری
• شاخص ها: مبلغ سند، مبلغ پرداختی سند، انحرافات پرداختی به صورت مطلق و نسبی
• جزئیات: خطوط/سال، ماه، طرف مقابل، نوع سند، تاریخ سند، شماره سند

9. گزارش رسید پرداخت بر اساس تاریخ پرداخت:

• دوره زمانی: برای سال جاری
• شاخص ها: مبلغ پرداختی، مبلغ توزیع پرداخت، انحرافات توزیع به صورت مطلق و نسبی
• جزئیات: خطوط/سال، ماه، طرف مقابل، نوع پرداخت، تاریخ پرداخت، شماره پرداخت

10. گزارش موجودی:

• دوره زمانی: برای تاریخ فعلی
• شاخص ها: موجودی محصول، ماندگاری باقیمانده بر حسب درصد (طبق DSO)، موجودی محصول بر اساس تاریخ انقضا - مدت 0٪، مدت 10-30٪، مدت 40-60٪، مدت 70-90٪، مدت 99٪
• جزئیات: خطوط/دسته، محصول، وضعیت، انبار، تاریخ سند رسید

سلام به همه! مطالب امروز برای کسانی است که به کار با برنامه های کاربردی تسلط دارند و نمی دانند چگونه جدول محوری در اکسل بسازند.

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

ایجاد یک جدول محوری اکسل نیازمند رعایت شرایط خاصی است:

1. داده ها در جدول قرار می گیرند، جایی که ستون ها و لیست هایی با نام وجود دارد.
2. بدون فرم های خالی
3. بدون اشیاء پنهان

نحوه ساخت جدول محوری در اکسل: دستورالعمل های گام به گام

برای ایجاد یک جدول محوری شما نیاز دارید:

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

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


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

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


دسته بندی «محصولات» را به صورت رشته ای قرار می دهیم. در "Line Names" فیلد مورد نیاز خود را منتقل می کنیم.


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

ستون "واحدها" که در جدول اصلی قرار دارد، مقدار کالاهای فروخته شده توسط یک فروشنده خاص را با قیمتی خاص نشان می دهد.


برای نمایش فروش، به عنوان مثال، برای هر ماه، باید فیلد "تاریخ" را به جای "نام ستون" قرار دهید. با کلیک بر روی تاریخ، دستور "گروه" را انتخاب کنید.


دوره های تاریخ و مرحله را مشخص کنید. ما انتخاب را تایید می کنیم.

ما چنین جدولی را می بینیم.


بیایید فیلد "مقدار" را به منطقه "ارزش ها" منتقل کنیم.


نمایش اعداد قابل مشاهده شد و دقیقاً به فرمت اعداد نیاز داریم


برای رفع، سلول ها را با فراخوانی پنجره با ماوس انتخاب کنید، "Format شماره" را انتخاب کنید.

ما فرمت عددی را برای پنجره بعدی انتخاب می کنیم و "جداکننده رقم" را علامت می زنیم. با دکمه "OK" تایید کنید.

طراحی میز محوری

اگر کادر تأیید کننده انتخاب چندین شیء به طور همزمان را علامت بزنید، می‌توانیم داده‌های چند فروشنده را همزمان پردازش کنیم.


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


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


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

به میز نگاه کن. به وضوح نشان می دهد که فروشنده در یکی از ماه ها پیراهن هایی را به مبلغ 2 عدد فروخته است.


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

با انتخاب سلول‌ها در PivotTable، برگه‌ای به نام PivotTable Tools با دو تب دیگر، Options و Design ظاهر می‌شود.


در واقع، شما می توانید برای مدت بسیار طولانی در مورد تنظیمات جداول محوری صحبت کنید. با ایجاد تغییراتی در سلیقه خود، به استفاده ای راحت برای خود دست یابید. از فشار دادن و آزمایش کردن نترسید. همیشه می توانید با فشار دادن کلیدهای ترکیبی Ctrl + Z، هر عملی را تغییر دهید.

امیدوارم همه مطالب را یاد گرفته باشید و اکنون می دانید که چگونه یک جدول محوری در اکسل بسازید.

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

به عنوان نقطه شروع، از ( درج / جداول / جدول) حاوی اطلاعات مربوط به فروش دسته ای از محصولات. ردیف های جدول حاوی داده هایی در مورد عرضه یک دسته از یک محصول و فروش آن است. از جدول مشابهی در مقاله استفاده شده است .

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

• تولید - محصول- نام دسته ای از کالاها، به عنوان مثال، " پرتقال ها»;
• گروه- گروه محصول، به عنوان مثال، " پرتقال ها"در گروه گنجانده شده اند" میوه»;
• تاریخ تحویل- تاریخ تحویل کالا توسط تامین کننده؛
• منطقه فروش- منطقه ای که محموله کالا در آن فروخته شده است.
• حراجی- هزینه ای که امکان فروش محموله کالا وجود داشت.
• حراجی- دوره فروش واقعی کالا در منطقه (در روز)؛
• سود- یادداشتی در مورد اینکه آیا از مقدار فروش کالا سود دریافت شده است یا خیر.

در سراسر مدیر نامجداول را به صورت " تنظیم کنید منبع_جدول» (نمونه فایل را ببینید).

با استفاده از فرمول ها، 5 گزارش ساده ایجاد می کنیم که در برگه های جداگانه قرار می دهیم.

گزارش شماره 1 کل فروش کالا

کل فروش هر محصول را بیابید.
این کار به سادگی با استفاده از تابع SUMIF() حل می شود، با این حال، ساخت گزارش خود به مهارت های خاصی در کار با برخی از ابزارهای EXCEL نیاز دارد.

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

• به برگه با جدول اصلی بروید.
• زنگ زدن ( داده / مرتب سازی و فیلتر / پیشرفته);
• فیلدها را مطابق شکل زیر پر کنید: سوئیچ را روی موقعیت قرار دهید نتیجه را در مکان دیگری کپی کنید; در قسمت Source Range، $A$4:$A$530 را وارد کنید. کادر را علامت بزنید فقط رکوردهای منحصر به فرد.

• لیست حاصل را در برگه ای که گزارش در آن قرار می گیرد کپی کنید.
• مرتب کردن لیست محصولات ( داده ها / مرتب سازی و فیلتر / مرتب سازی A تا Z).

شما باید لیست زیر را دریافت کنید.

در یک سلول B6 فرمول زیر را وارد کنید، سپس آن را تا انتهای لیست کپی کنید:

SUMIF(Source_Table[Item],A6,Source_Table[Sales])

COUNTIF(Source_Table[Item],A6)

گزارش شماره 2 فروش محصول بر اساس مناطق

کل فروش هر محصول را در مناطق بیابید.
بیایید از لیست کالاهای ایجاد شده برای گزارش شماره 1 استفاده کنیم. به طور مشابه، ما لیستی از نام مناطق دریافت خواهیم کرد (در قسمت Initial range، $D$4:$D$530 را وارد کنید).
محدوده عمودی حاصل را در کپی کنید کلیپ بوردو آن را به صورت افقی. محدوده به دست آمده حاوی نام مناطق را در هدر گزارش قرار دهید.

در یک سلول ب 8 ما فرمول زیر را معرفی می کنیم:

SUMIFS(Source_Table[Sales];
Source_Table[Item];$A8;
Source_Table[منطقه فروش]؛ B$7)

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

کپی کردن فرمول بالا در سلول های سمت راست با کمک کار نمی کند (این کار برای گزارش شماره 1 انجام شد)، زیرا در این مورد در سلول C8 فرمول به صورت زیر خواهد بود:

SUMIFS(جدول_اصلی[فروش، روز]؛
Source_Table[Group];$A8;
Source_Table[فروش]؛ 7 دلار کانادا)

گزارش شماره 3 فیلتر کردن محصولات بر اساس سودآوری

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

FREQUENCY(Source_Table[فروش، روز]؛A7:A12)

یک محدوده را برای وارد کردن فرمول انتخاب کنید C6: C12 ، سپس در نوار فرمولفرمول بالا را وارد کرده و کلیک کنید CTRL+ تغییر مکان+ وارد.

همین نتیجه را می توان با استفاده از تابع SUMPRODUCT () معمول به دست آورد:
=SUMPRODUCT((Source_Table[Sales, days]>A6)*
(منبع_جدول[فروش، روز]<=A7))

گزارش شماره 5 آمار عرضه کالا

اکنون گزارشی از عرضه کالا در ماه تهیه می کنیم.
ابتدا بیایید فهرستی از ماه ها به سال ایجاد کنیم. در جدول اصلی، اولین تاریخ تحویل 07/11/2009 است. می توانید آن را با استفاده از فرمول محاسبه کنید:
=MIN(منبع_جدول[تاریخ تحویل])

بیایید لیستی از تاریخ ها ایجاد کنیم - از اولین تاریخ تحویل شروع می شود. برای این کار از فرمول استفاده می کنیم:
=OMONTH($C$5,-1)+1

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

با اعمال قالب سلولی مناسب، نمایش تاریخ ها را تغییر دهید:

فرمول محاسبه تعداد محموله های تحویلی کالا در ماه:

SUMPRODUCT((منبع_جدول[تاریخ تحویل]>=B9)*
(منبع_جدول[تاریخ تحویل]

حالا بیایید ردیف هایی را اضافه کنیم تا تعداد کل بازی های هر سال را بشماریم. برای انجام این کار، جدول را با برجسته کردن سالی که در آن تحویل انجام شده است در یک ستون جداگانه با استفاده از تابع YEAR() تغییر می دهیم.

اکنون، برای خروجی بر اساس سال، ساختاری را از طریق آیتم منو ایجاد خواهیم کرد:

• هر سلول از جدول اصلاح شده را انتخاب کنید.
• پنجره تماس از طریق آیتم منو داده/ساختار/جمع فرعی;
• فیلدها را مطابق شکل پر کنید:

پس از کلیک بر روی OK، جدول به صورت زیر تغییر می کند:

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

خلاصه :

البته می‌توان گزارش‌هایی مشابه گزارش‌های ایجاد شده را با استفاده یا استفاده از آن تهیه کرد فیلترهابه جدول اصلی یا با کمک توابع دیگر BDSUMM() , BIzVARCH() , COUNT() و غیره. انتخاب رویکرد به موقعیت خاص بستگی دارد.