Нарастающие итоги в Microsoft Excel

Что такое нарастающие итоги (Running Totals) и как их вычислить в Microsoft Excel разными способами: формулами, в сводной таблице, в запросе Power Query.
Скачать пример www.planetaexcel.ru/technique...
Мои книги planetaexcel.ru/books/
Мои онлайн-курсы www.planetaexcel.ru/learning/
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru/plex/
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel

[@user-zj4ub3cx3z]

Я наверное никогда не перестану удивляться.... Спасибо за буферизацию.

[@xrayxxi4569]

Такого толкового объяснения редко можно найти. Спасибо огромное!

[@Albertus99]

Низкий поклон великому человеку!

[@Gamma9971]

Как всегда у Николая Павлова полезные знания не в заголовке видео, а в конце видео) В этом видео это буферизация списка) Спасибо за ваши уроки.

[@tinahope4045]

Спасибо огромное за Ваши видео. Всегда самое лучшее объяснение и самые полезные примеры. Снимите пожалуйста побольше видео про работу в квери 🙏

[@user-ch3bo9hn5k]

Лучший канал по работе в MS Excel! Спасибо Вам, Николай!

[@ignatpoddubnykh]

Спасибо за прием с буферизацией запроса!

[@user-hg5mu2lp4g]

Николай, спасибо! Просто, когда знаешь! :)

[@Yevd1977]

Давно Николая смотрю. Все время поражаюсь, насколько качественная и грамотная речь. Ни одного лишнего слова. Какой-то "интеллектуальный эталон".

[@alexanderkamilov2722]

Вот только пару дней назад появилась такая необходимость и тут вуаля - аж три метода!:)) благодарю! Как всегда профессионально.

[@litwinczuk_jd]

👍❤️ 1:19 Нарастающие итоги формулами 2:42 нарастающие итоги в сводной таблице 6:19 Нарастающий итог в Power Query 12:23 способ ускорить запрос в Power Query

[@user-lm5dt2jv7x]

Лучшая подача материала

[@olgatarasevich9156]

Как Вы вовремя с ускорением работы Power Query. 👍 Спасибо огромное, Вы столько делаете))). ♥️ И реально во многом помогаете.

[@mixailchikishev7851]

Огромное спасибо! Это видео заставило задуматься о причинах длительной обработки используемой цепочки запросов с применением Table.Buffer, ведь не хватает ещё и буферизации списков.

[@rash0807]

Самый лучший в России Excel сэнсэй!!! Спасибо большое! Здоровья Вам!

[@yurd563]

Большое спасибо за видеоурок!

[@user-wy1wq6lj4l]

Отличный урок! Спасибо!!!

[@dorodorobov9402]

Как всегда: сложное - доходчиво и просто. Спасибо Николай!

[@olca_t]

Спасибо большое!! Как всегда очень выручаете!😍

[@SeloUrala]

Спасибо, полезные уроки!

[@user-wn9od1cl8k]

Круто! Спасибо, Николай. Как всегда - подача материала на высшем уровне✊🏻

[@gaybulloaslonov7950]

Брависимо!!!!!!!!!! как всегда.

[@user-ey7gh4ky2b]

Спасибо Николай! 👍

[@jekosjekos9990]

Спасибо за ваши видео!

[@Gunselinger]

Спасибо! Интересно и очень полезно!

[@vladislavmartynov5476]

Спасибо, Николай, за интересные и красивые решения. Пополнил копилку знаний в очередной раз.

[@tisseract]

Шикарно! Как раз не мог сделать в Query нарастающий итог для промежеточных вычислений. Спасибо, Николай!

[@never5786]

Это гениально, спасибо!!!

[@uygunnurullaev5224]

Как всегда самые полезные знания, спасибо за всё. Николай ты супер 👍

[@user-xx4zp7lg6o]

Супер!спасибо огромное,Ваши уроки очень помогают расширить познания в Exel!

[@ivanmamchych5802]

Спасибо за интересную и полезную информацию, Николай. Вы уже наверно знаете, что у инсайдеров Office 365 есть новая формула SCAN, работающая в сочетании с LAMBDA. Она тоже может подсчитать нарастающий итог. Так что в будущем ждём обзора этой и других новых LAMBDA-сочетаемых функций. Как Вы сказали, интересное - впереди!

[@ExcelStore]

Познавательный материал, спасибо!

[@user-pq4yj7iu6l]

Космос какой то!! Супер. Спасибо...))

[@user-bk4gp9wy5p]

На онлайн- уроках Николая Павлова я значительно поднял свой уровень в Exel. Качество моей работы выросло. Спасибо, Николай за реальные "фишки" в этой интересной программе.

[@Albertus99]

Все сделал все получилось большое спасибо еще раз!

[@krekssss1]

Спасибо большое за вашу работу!!! Как всегда в нужное время))) как это?))) Но круто!!!

[@antonkharitonov9007]

Нарастающие лайки за такие видео!

[@user-mp9wj9wm9h]

Век живи, век учись. Про сводные прям откровение ( доп вычисления)

[@SamSambl4]

Великолепно!!!

[@Croatoan_PL]

Полезная штука. Пользовался для расчета в период времени нарастающего итога по суммам актов выполненных работ и суммам оплат. Но пользовался формулами, каким-то боком сам до него допёр. Пока не могу понять где использовать остальные два. Но за лайфхаки по буфферизации спасибо!

[@AlexKite68]

Спасибо! Круто!

[@max4445]

Супер! Спасибо :)

[@user-uw2kr8rk5i]

Я тоже пользуюсь двумя формулами. А оказывается можно и одной. Сводные таблицы с доп вычислением тоже использовал, но реже. Объемов данных таких, чтобы оправдать использование сводных таблиц, у меня встречается редко. Ну а PQ , с его гибкостью, это ваще... В любом случае очень полезная информация. Спасибо. Лайк.

[@user-oq8iu7nn8e]

Спасибо!

[@user-le6ix1ud3f]

СПАСИБО МАСТЕР))))

[@Evgeniy_Antonov_88]

1С очень похожа в настройках отчётом со сводными таблицами Excel. Логика вроде бы схожая. Спасибо за видео.

[@biweb13]

Супер!

[@architecture3347]

Я обычно ставлю формулу в первой ячейке "=А2" к примеру, затем ниже"=В2+А3", затем протягиваю двойным кликом. Занимает 5 секунд.

[@planetaexcel]

Тоже можно :)

[@Rice0987]

Я делал так в самом начале своего эксель дао. Потом стал пользоваться суммой с закреплённым первым значением. Но выявились проблемы при удалении/перемещении строк. Диапазоны разрывались, как в условном форматировании, из-за чего отчёты каждый раз приходилось обновлять сверху донизу. Потом я случайно открыл чудо сводных таблиц и ошибки практически исчезли. Настало время мощных запросов. Но поскольку все формы, шаблоны и отчёты настроены и работают по накатанной, то на переделку их нужно время, а его, как понимаете, конечно же, нет. Поэтому приходится дальше влачить ветхие методы ведения учёта. И применение методик мощных запросов остаётся пока увлекательным факультативом.

[@architecture3347]

@@Rice0987 сводные таблицы это же пайвоты? С пайвотами тоже работаем. Поскольку пайвот нельзя модифицировать, то переношу как данные копированием на новую вкладку и далее работаю с ней. При необходимости из новой копии с добавленными столбцами с формулами делаю новые пайвоты и т.д.

[@user-hc2zh2wz7z]

@@architecture3347 попробуйте вычисляемые поля в сводных таблицах. Вам понравится)

[@user-bk1jk4wh3w]

Так же делал, когда быстро нужно было посчитать..

[@owl1891]

Фура с лайками подъехала 🚚 Где разгружать?

[@elanovz]

Спасибо.

[@diavolo-o]

F4 пригодилось) спасибо!) ато каждый раз исправлял после копирования, еще и не понимал с чего вдруг в формуле меняется ссылка)

[@anton_a388]

Спасибо

[@olga_kuznetsova]

Николай, спасибо большое за лайфхак с буферизацией! Всю голову сломала, как ускорить ABC-анализ - использую в нём как раз-таки нарастающие итоги. И вот готовое решение!

[@MrTrylobeat]

Я делал с исходника сводные с сортировкой по штукам, рублям, накидывал формулу для суммирования накопленной доли, затем загонял обратно именованный список в pq и джойнил к исходным данным а, б или ц

[@LLl_y_T]

Спасибо, Николай за ваш труд! Давно и плотно сижу на ваших видео и книгах. В данном видео для первого варианта с формулами можно делать СУММЕСЛИМН, где диапазоном условий указать период, а условием >= чем период самой строки. Таким образом можем получить сумму накопительным итогом на конкретную дату, даже если они не отсортированы. Что очень помогает когда надо быстро сообразить ABC анализ для неструктурированных данных. Но ваш 2ой вариант конечно же все равно удобнее всего для такого быстрого ABC анализа. п.с. Спасибо за буферизацию.

[@Haak9555]

а в PowerQuery плохой он вариант показал, там в нарастующий итог все значение суммируются, даже отрицателньные. Хотя по логике вещей, они должны наоборот вычитаться из общего итога, так как они со знаком минус идут.

[@mstrJons]

Як завжди, на висоті

[@user-ln8nj3cm1z]

Можно использовать формулу ранга, которая будет проставлять от максимальные и минимальные значения дат а потом, через суммесли суммировать все значения которые равны или меньше рангу. Тогда и сортировка не нужна, правда формула по сложнее. Хочу предупредить об одном если использовать метод с частичной фиксацией диапазона в умных таблицах, то старый значения будут считаться правильно, но уже для новых значений диапазон не заканчивается до уровня конкретной строки а на все добавленные строки, это возможно решить с помощью ДВССЫЛ (по крайней мере я столкнулся с данной проблемой в 2016 офисе). За видео респект)))

[@user-li2ke3zp9x]

Интересно наоборот, как быстро выводить итоги по месяцам из идущих нарастающих итогов.

[@UC0LMA1CQ6b3s6nrBlO4]

Спасибо большое за контент я Ваш фанат ! Удачи Вам и творческих успехов ! Хотелось бы Вас попросить уделить внимание учету и подсветке при складцком учете и аналитеке по этой тематике. Задача найти товар в ячейке ее подсветить и оптимизация расположения товар в этих ячейках.

[@odmitv]

Элементарно, Ватсон! Используйте *Условное оформление* на закладке *Главная*

[@zabrus24]

👏👏👏👏👏👏

[@user-vm3zz5iz2d]

Много вариантов формул перепробовал для нарастающих итогов, в итоге остановился на вот таком =sum(offset(C2;-1;0;1;1);B2), в данном случае данные в столбце B, а нарастающий итог в столбце C. Такая формула позволяет безболезненно добавлять строки в середину массива данных и после протяжки каждая формула в столбце итогов по-прежнему ссылается на предыдущую строку.

[@planetaexcel]

Отличный вариант. Единственное, что нужно упомянуть - функция СМЕЩ (OFFSET) жутко медленная на больших массивах - будьте осторожны.

[@user-vm3zz5iz2d]

Спасибо, Николай. На массивах данных примерно до 5 тысяч строк у меня с ней проблем не было, а на большем объеме данных не пробовал. Как правило это всякие реестры транзакций - касса, расчетные счета и т.п. А какая из формул лучше по быстродействию для большого массива - с offset или с закреплением верхней ячейки?

[@user-hf3zc6jz3f]

Николай, все здорово, спасибо большое. Не планируете сделать урок по преобразованию таблицы с иерархией в плоскую? Было бы очень уместно....

[@planetaexcel]

Сделаем ;)

[@user-cu4ey5ch5d]

Добрый день. Очень помог ваш урок. Подскажите, есть ли возможность суммировать нарастающие итоги в группе (по клиентам) до определённого значения (по 100тыс)? Например, при продажах превышающих 100тыс руб нарастающим итогом, необходимо с контрагентом заключать новый договор и суммировать заново до 100тыс руб.

[@MrTrylobeat]

Ээхь, ещё бы добавить в pq к нарастающему итогу долю в процентах, также как в сводной

[@user-zi9en8tq6p]

Здравствуйте!

[@stephane7211]

Где же вы были пол года назад когда мне это было нужно? кстати в конце решил задачу используя пользовательскую формулу в павер кваери. Если кому понадобится: (values as list, grouping as list) as list => let GRTList = List.Generate ( ()=> [ GRT = values{0}, i = 0 ], each [i] < List.Count(values), each try if grouping{[i]} = grouping{[i] + 1} then [GRT = [GRT] + values{[i] + 1}, i = [i] + 1] else [GRT = values{[i] + 1}, i = [i] + 1] otherwise [i = [i] + 1] , each [GRT] ) in GRTList данная формула позволяет суммировать групируя по признаку (grouping). Все списки лучше буферизовать (так быстрее)

[@qzRONINzq]

Всё очень просто

[@TheAdeges1]

Спасибо за видео! Может ошибаюсь, но во втором случае (со сводной таблицей) нарастающий итог по датам - месяцам не совсем нарастающий? Ведь там группировка по названию месяцев, а судя по исходной таблице есть, к примеру, январи 2019, 2020, 2021 годов. Их ведь не корректно суммировать с нарастающей логикой?

[@user-bk1jk4wh3w]

Недавно делал выборку и таблицы с 90к строками на основании другой таблицы (50 строк). Т.е. нужно было достать только те строки, которые были во второй таблице (по типу ВПР но на каждое значение во торой таблице, в первой была не одна строка, вышел ответ с 7к строками). так пока я правильно сделал запрос, у меня PQ раз 20 завис при попытке обработать информацию. Так вот вопрос, как самостоятельно находить вот эти "узкие места"? Хотя запрос то был легкий, можно сказать по шаблону соединение запросов... но первую таблицу(на 90к строк) я сначала через PQ сделал удобной для работы, ибо с корпоративной САП она 6-8 уровневой выгружается..

[@Rice0987]

Николай сменил пластинку. :)

[@AlexanderGorshkov]

В М коде в конце может помочь также изменение Int64.Type на, например Int16.Type - если цифры не превышают некоторого диапазона (2 в 16 для данного типа). А код ускорится!

[@planetaexcel]

Спасибо за идею. Нужно потестировать.

[@AlexanderGorshkov]

@@planetaexcel Николай, в Экселе нет встроенного секундомера для измерения времени исполнения команд? Было бы полезной фичей для подобных экспериментов. А подобная техника изменения типов данных отлично работает в Питоне, в частности, в Пандас для работы с большими данными. Там изменения типа чисел даёт буст в сотни раз по потраченной опер.памяти и времени.

[@farinsaifutdinov5534]

@@AlexanderGorshkov Ну, пандас это для работы со средними объемами данных : in-memory. Для эффективной работы с действительно большими объемами порядка 100Гб на одиночном ПК, которые не вмещаются в оперативную память это datatable либо vaex.

[@user-pu1wl2tm4y]

У Вас для 2020 года нарастающий итог будет содержать данные 2019 года. Как сделать , что бы для 2020 считалось отдельно? И если есть группировка план/факт (в первой графе), потом сам показатель «продажи» и только потом даты? Как сделать, что бы и по голом расчёт нарастающего итога был отдельный и для показателей план/факт

[@mbassador_19]

Здравствуйте. Вы хорошо объясняете, но как понять самому то чтт вы говорите, а не просто готовое взять у вас. Например, как вы узнаете что детализация служит превращению в список? Что это за функция? На подобных моментах, их объяснении пожалуйста акцентируйте внимание. Как вы замечаете что из столюца индекс именно то количество берется что суммируется? То есть при добавлении этого столбца уже была как цель эта задумка, чему этот столбец будет служить?

[@user-co1hx9td9n]

Добрый! У меня тоже засада по нарастающему итогу и решение нигде не нашла( Мне небходимо нарастающие за месяц по хронологии с годами за весь период. Сводная вспотыкается на "год" и следующий год начинает наращивать не с предыдущего декабря, а идет новая веха с января следующего года. Приходиться уходить в формульный подсчет. PQ для меня темный лес

[@user-ih7fn9pl8j]

Первый😊

[@Aqil_Eliyev]

👍🇦🇿

[@Pupochek22]

Отличные видео. Автору огромное спасибо. Вопрос знатокам - как мне ленивому сделать так, чтобы при добавлении значений в столбец одной умной таблицы они автоматически появлялись в столбце другой умной таблицы, только в том случае, что такого значения в этом столбце еще нет? Желающим помочь, заранее благодарен. (Пробовал использовать Power Query, работает но только с одной таблицей на листе... Хотелось бы на листе иметь 2 умные таблицы с автоматически заполняемыми столбцами, для наглядности. Не смог разобраться как впихнуть 2 запроса Power Query на один лист...).

[@kselnaga7303]

здравствуйте, а как бы отобразить название столбца? например есть столбы с названиями предметов, а таблица выбора предметов у студентов или школьников, и в них им проставлены 1 если выбрал предмет, и 0 если не выбрал, и нам нужно взять список предметов через запятую в строке, которые студент или школьник выбрал (по единицам)?

[@Rice0987]

Всё ок, но непонятно, каким образом мощный запрос учитывает в расчётах УДАЛЁННЫЙ столбец индекса? Или он перед удалением отправляет его куда-то в буфер?

[@odmitv]

Задержка в вычислении происходит на этапе "Добавлен пользовательский столбец", а удалённый столбец с индексом находится ниже него и запросы в PQ вычисляются поэтапно (сверху вниз).

[@Pesssimistos]

Эх, а я в пятницу пытался в умной таблице накопление сделать. А оказывается вон как можно!

[@nbuhblhfrjy]

4 - нужно было ещё в DAX )

[@planetaexcel]

Хотел, но тогда видео было бы длительностью минут 30-40 :) Лучше отдельным выпуском сделаю.

[@skrypai]

@@planetaexcel Будем ждать с нетерпением вариант с DAX. Очень часто приходится вычислять балансы (остатки) по счетам.

[@artemon3158]

Просто умоляю Николай, сделайте доступное видео по множественному впр, может быть просмотрХ, любой функционал из эксель 365 тоже подойдет. Как можно по одному значению вытащить все совпадения из таблицы. Ведь впр вытаскивает только 1 значение...

[@ph1llcovr1g1ne3]

Добрый, подскажите почему необходимо сортировать данные по дате при использовании fx: sum. Спасибо!)

[@planetaexcel]

Нарастающий итог всегда считается по какому-то столбцу - обычно по дате. По этому столбцу нужна сортировка перед вводом формулы нарастающего итога.

[@ph1llcovr1g1ne3]

@@planetaexcel Спасибо!) Это я понял, а fx:sum от сортировки не зависит, только логика результата зависит, чтобы так сказать не было пересортицы, верно?

[@sanya-san4384]

Объясните, пжл, 8.04% напротив 881, что означают?

[@1iuh]

3:40 Группировку по неделям можно ли настроить?

[@1iuh]

Столбец с номером недели в году. Неделя может принадлежать двум месяцам - чтобы показывал для каждого месяца по номеру недели.

[@valerchik67]

В примере со сводной таблицей не совсем нарастающие итоги. Поскольку в сумму за каждый месяц попадают данные и 2019г. и 2020г. и 2021г.

[@user-qz1np3rw4p]

Николай, а как Вы определили точное время обновления запроса 0.24 сек? Где это можно посмотреть? Заранее спасибо!

[@planetaexcel]

У меня есть специальный макрос для замера скорости обновления запросов Power Query. Добавлю его в ближайшее обновление PLEX, кстати :)

[@heisnod]

еще через DAX можно

[@2008ulman]

Дмитрий Васильевич,как Вам?

[@natalyajio2132]

Подскажите пожалуйста, как можно протянуть по дате вот в таком формате: 10.04.22 10:00 10.04.22 18:00 11.04.22 10:00 11.04.22 18:0 И так до конца месяца. Спасибо.

[@natalyajio2132]

11.04.22 18:00

[@sergmal3364]

А зачем в первом и третьем варианте необходимо проводить сортировки? Вроде как в условии задачи нет задания посчитать нарастающие итоги в хронологическом порядке. А формула отработает и без сортировки на ура. Ну а второй вариант вообще не доведён до нужного формата ответа. За видео спасибо, как всегда интересно.

[@user-bk1jk4wh3w]

В условиях задачи сказано, что итоги нужны с нарастанием... а если есть одна переменная и значение, не тяжело догадаться что должно нарастать. Дальше все зависит от частоты предоставления информации, если есть инфа по годам, то нарастание по годам, если есть по месяцам, то делаем по месяцам и т.д.

[@mr.london6839]

а как подсчитать среднее значение?

[@user-ov7cg8bg1l]

Для этого нужна формула?

[@BALTHASAR174]

Делаю формулу =сумм($C$2:C2) и компьютер зависает, просчитывая формулу для вообще всех ячеек, даже пустых, хотя я внес формулу только в одну ячейку, что делать?

[@user-zi9en8tq6p]

Как сделать excel в формате А4

[@user-wr5zh6dm9g]

сэнсэй однако...

[@goshik73]

И какому одному чудозвону не понравилось?

[@stanissovet9571]

Классическому VBA да да да маркетологическому даксу павер биай и павер квери нет нет нет

[@planetaexcel]

На вкус и цвет маркеры разные :) На мой взгляд но совсем корректно сравнивать эти два инструмента - они из разных вселенных.

[@user-ng4vp4wb9n]

Спасибо за Ваш острый ум, порой смотрю и диву даюсь, как же все оказывается легко, если знаешь как подойти к решению! Спасибо и еще раз спасибо!

[@alekskapitonov2738]

Спасибо

[@Kira368kawai]

Спасибо