مجموع در حال اجرا در اکسل

روش 1. فرمول ها

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

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

معایب این روش آشکار است:

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

روش 2. جدول محوری

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

ما جدول اصلی خود را به یک میانبر صفحه کلید "هوشمند" (پویا) تبدیل می کنیم کلیدهای Ctrl+T یا تیم صفحه اصلی – قالب بندی به صورت جدول (صفحه اصلی - قالب بندی به عنوان جدول)و سپس با دستور یک جدول محوری روی آن می سازیم درج - PivotTable (درج - جدول محوری). تاریخ را در قسمت ردیف ها در خلاصه قرار می دهیم و تعداد کالاهای فروخته شده را در ناحیه ارزش ها قرار می دهیم:

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

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

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

مزایای این روش:

• حجم زیادی از داده ها به سرعت خوانده می شوند.
• هیچ فرمولی نیازی به وارد کردن دستی نیست.
• هنگام تغییر در داده های منبع، کافی است خلاصه را با دکمه سمت راست ماوس یا با دستور Data – Refresh All به روز کنید.

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

روش 3: Power Query

بیایید جدول "هوشمند" خود را با داده های منبع در ویرایشگر کوئری Power Query با استفاده از دستور بارگذاری کنیم داده ها - از جدول / محدوده (داده ها - از جدول/محدوده). اتفاقاً در آخرین نسخه های اکسل، نام آن تغییر یافت - اکنون نامیده می شود با برگ (از برگه):

سپس مراحل زیر را انجام خواهیم داد:

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

2. کمی بعد، برای محاسبه کل در حال اجرا، به یک ستون کمکی با شماره ردیف ترتیبی نیاز داریم. بیایید آن را با دستور اضافه کنیم اضافه کردن ستون - ستون شاخص - از 1 (افزودن ستون - ستون فهرست - از 1).

3. همچنین برای محاسبه کل در حال اجرا نیاز به ارجاع به ستون داریم فروخته شده، جایی که داده های خلاصه شده ما نهفته است. در Power Query به ستون ها لیست (list) نیز گفته می شود و برای دریافت لینک به آن، روی سربرگ ستون راست کلیک کرده و دستور را انتخاب کنید. جزئیات (نمایش جزئیات). عبارتی که ما نیاز داریم در نوار فرمول ظاهر می شود که شامل نام مرحله قبل است #"شاخص اضافه شد"، از جایی که جدول و نام ستون را می گیریم [حراجی] از این جدول در پرانتز:

این عبارت را برای استفاده بیشتر در کلیپ بورد کپی کنید.

4. مرحله آخر غیر ضروری را حذف کنید فروخته شده و به جای آن یک ستون محاسبه شده برای محاسبه کل در حال اجرا با دستور اضافه کنید اضافه کردن یک ستون - ستون سفارشی (افزودن ستون - ستون سفارشی). فرمول مورد نیاز ما به شکل زیر خواهد بود:

در اینجا تابع لیست. محدوده لیست اصلی را می گیرد (ستون [حراجی]) و عناصر را از آن استخراج می کند، که از اول شروع می شود (در فرمول، این 0 است، زیرا شماره گذاری در Power Query از صفر شروع می شود). تعداد عناصری که باید بازیابی شوند، شماره ردیفی است که از ستون می گیریم [فهرست مطالب]. بنابراین این تابع برای سطر اول فقط یک سلول اول ستون را برمی گرداند فروخته شده. برای خط دوم - در حال حاضر دو سلول اول، برای سوم - سه اول و غیره.

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

باقی مانده است که ستون Index را که دیگر به آن نیاز نداریم حذف کنیم و نتایج را با دستور Home – Close & Load به اکسل برگردانیم.

مشکل حل شده است.

سریع و خشن

در اصل، این می توانست متوقف شود، اما یک مگس کوچک در پماد وجود دارد - درخواستی که ما ایجاد کردیم با سرعت یک لاک پشت کار می کند. به عنوان مثال، در ضعیف ترین رایانه شخصی من، جدولی با 2000 ردیف در 17 ثانیه پردازش می شود. اگر داده های بیشتری وجود داشته باشد چه؟

برای افزایش سرعت، می توانید از بافر با استفاده از تابع ویژه List.Buffer استفاده کنید، که لیست (لیست) ارائه شده به آن را به عنوان آرگومان در RAM بارگذاری می کند، که دسترسی به آن را در آینده بسیار افزایش می دهد. در مورد ما، منطقی است که فهرست #"افزوده شده" [فروخته شده] را بافر کنیم، که Power Query باید هنگام محاسبه مجموع در حال اجرا در هر ردیف از جدول 2000 ردیفی به آن دسترسی داشته باشد.

برای انجام این کار، در ویرایشگر Power Query در تب Main، روی دکمه Advanced Editor (Home – Advanced Editor) کلیک کنید تا کد منبع درخواست ما به زبان M ساخته شده در Power Query باز شود:

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

پس از انجام این تغییرات، درخواست ما به طور قابل توجهی سریعتر می شود و تنها در 2000 ثانیه با جدول 0.3 ردیفی مقابله می کند!

یک چیز دیگر، درست است؟ 🙂

• نمودار پارتو (80/20) و نحوه ساخت آن در اکسل
• جستجوی کلمات کلیدی در متن و بافر پرس و جو در Power Query