В этом выпуске показаны три способа подсчета промежуточных результатов.
Промежуточная сумма для списка числовых значений представляет собой сумму значений от первой строки до строки промежуточной суммы. Обычно промежуточная сумма используется в регистре чековой книжки или бухгалтерском листе. Есть много способов создать промежуточную сумму, два из которых описаны ниже.
Самый простой способ - добавить в каждой строке промежуточную сумму из строки выше к значению в строке. Итак, первая формула в строке 2:
=SUM(D1,C2)
Причина, по которой мы используем функцию СУММ, заключается в том, что в первой строке мы смотрим на заголовок в строке выше. Если мы воспользуемся более простой и интуитивно понятной формулой, =D1+C2будет сгенерирована ошибка, поскольку значение заголовка является текстовым, а не числовым. Магия в том, что функция СУММ игнорирует текстовые значения, которые добавляются как нулевые значения. Когда формула копируется во все строки, в которых требуется промежуточный итог, ссылки на ячейки корректируются соответствующим образом:
Другой метод также использует функцию СУММ, но каждая формула суммирует все значения из первой строки в строку, отображающую промежуточную сумму. В этом случае мы используем знак доллара ($), чтобы сделать первую ячейку ссылки абсолютной ссылкой, что означает, что она не корректируется при копировании:
На оба метода не влияет сортировка и удаление строк, но при вставке строк формулу необходимо скопировать в новые строки.
В Excel 2007 появилась таблица, которая является повторной реализацией списка в Excel 2003. Таблицы представили ряд очень полезных функций для таблиц данных, таких как форматирование, сортировка и фильтрация. С появлением таблиц нам также предоставили новый способ ссылки на части таблицы. Этот новый стиль ссылок называется структурированными ссылками.
Чтобы преобразовать приведенный выше пример в таблицу, мы выбираем данные, которые хотим включить в таблицу, и нажимаем Ctrl + T. После отображения запроса с просьбой подтвердить диапазон таблицы и наличие существующих заголовков, Excel преобразует данные. в форматированную таблицу:
Обратите внимание, что формулы, которые мы ввели ранее, остаются прежними.
Одной из полезных функций, предлагаемых таблицами, является автоматическое форматирование и обслуживание формул по мере добавления, удаления, сортировки и фильтрации строк. В частности, мы сконцентрируемся на поддержании формулы, и это может быть проблематичным. Чтобы таблицы работали во время манипулирования ими, Excel использует вычисляемые столбцы, которые являются столбцами с формулами, такими как столбец D в приведенном выше примере. Когда новые строки добавляются в нижнюю часть, Excel автоматически заполняет новые строки формулой «по умолчанию» для этого столбца. Проблема с приведенным выше примером заключается в том, что Excel путается со стандартными формулами и не всегда обрабатывает их правильно. Это становится очевидным, когда новые строки добавляются в нижнюю часть таблицы (выбирая нижнюю правую ячейку в таблице и нажимая TAB):
Этот недостаток устраняется за счет использования более новых структурированных ссылок. Структурированные ссылки устраняют необходимость ссылаться на определенные ячейки с использованием стиля ссылок A1 или R1C1 и вместо этого используют имена столбцов и другие ключевые слова для идентификации и ссылки на части таблицы. Например, чтобы создать ту же формулу промежуточного итога, которая использовалась выше, но с использованием структурированных ссылок, мы имеем:
=SUM(INDEX((Sales),1):(@Sales))
В этом примере у нас есть ссылка на имя столбца «Продажи» вместе со знаком (@) для ссылки на строку в столбце, в котором находится формула, который также известен как текущая строка.
Чтобы реализовать первый пример выше, где мы добавили значение промежуточной суммы в предыдущей строке к сумме продаж в текущей строке, вы можете использовать функцию СМЕЩЕНИЕ:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Если суммы, используемые для расчета промежуточной суммы, указаны в двух столбцах, например, один для «Дебет» и один для «Кредиты», то формула будет следующей:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Здесь мы используем функцию ИНДЕКС, чтобы найти ячейки кредита и дебета первой строки, и суммировать весь столбец до значений текущей строки включительно. Промежуточная сумма - это сумма всех кредитов до текущей строки включительно за вычетом суммы всех дебетований до текущей строки включительно.
Для получения дополнительной информации о структурированных ссылках в частности и таблицах в целом мы рекомендуем книгу «Таблицы Excel: полное руководство по созданию, использованию и автоматизации списков и таблиц» Зака Барресса и Кевина Джонса.
Когда я попросил читателей проголосовать за понравившиеся советы, таблицы были популярны. Спасибо Питеру Альберту, Снорре Эйкеланду, Нэнси Федерис, Колину Майклу, Джеймсу И. Моеде, Кейеру Пателю и Полу Петону за предложение этой функции. Питер Альберт написал бонусную подсказку «Читаемые ссылки». Зак Барресс написал бонусную подсказку к промежуточным результатам. Четыре читателя предложили использовать OFFSET для создания расширяющихся диапазонов для динамических диаграмм: Чарли Баак, Дон Ноулз, Фрэнсис Логан и Сесилия Риб. Таблицы теперь делают то же самое в большинстве случаев.
Смотреть видео
- В этом выпуске показано три способа подсчета промежуточных результатов.
- В первом методе формула в строке 2 отличается от формулы во всех других строках.
- Первый метод: = Влево в строке 2 и = Влево + Вверх в строках с 3 по N.
- Если вы попытаетесь использовать ту же формулу, вы получите ошибку # Значение с = Всего + Число
- Метод 2 использует
=SUM(Up,Left)или=SUM(Previous Total,This Row Amount) - SUM игнорирует текст, поэтому вы не получите ошибку VALUE
- В методе 3 используется расширяющийся диапазон:
=SUM(B$2:B2) - Расширять диапазоны - это круто, но медленно
- Прочтите технический документ Чарльза Вильямса о скорости формул Excel
- Третий способ - проблема, когда вы используете Ctrl + T и добавляете новые строки
- Excel не может понять, как написать формулу
- Для обходных путей требуются некоторые знания структурированных ссылок в таблицах.
- Обходной путь 1 - медленный
=SUM(INDEX((Qty),1):(@Qty)) - Обходной путь 2 - нестабильный
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) относится к количеству в этой строке
- (Кол-во) относится ко всем значениям Кол-во
Стенограмма видео
Изучение Excel для подкастов, серия 2004 - Промежуточные итоги
Я буду подкастировать всю эту книгу. Щелкните значок I в правом верхнем углу, чтобы подписаться.
Привет, добро пожаловать обратно в сеть мистических сотовых. Я Билл Джелен. Эту тему в книге внес мой друг Зак Париз. Говоря о таблицах Excel, Зак - мировой эксперт по таблицам Excel. Он написал книгу о таблицах Excel, но сначала давайте поговорим о промежуточных итогах не в таблицах.
Итак, когда я думаю о промежуточных итогах, есть три разных способа сделать промежуточные итоги, и способ, которым я всегда начинал, - это в первой строке, которую вы просто говорите: перенести значение. Так равняться тому, что слева от меня. Хорошо, этот формат здесь просто = B2. Все это текст формулы здесь, в правом углу, так что вы видите, что мы используем, а затем оттуда и вниз, это простая небольшая формула, равная предыдущему значению плюс текущее значение справа и скопируйте это вниз , но теперь вы знаете, что у нас есть проблема, заключающаяся в том, что для этого требовались две разные формулы, и вы знаете, что в идеальной ситуации у вас есть одна и та же формула на всем протяжении, и причина, по которой мы должны иметь другую формулу в первой строке, заключается в что когда вы пытаетесь сложить 7 плюс общее количество слов, это ошибка значения,но крутой рабочий здесь не просто использовать левый плюс вверх, но использовать = (SUM) предыдущего значения плюс количество в этой строке, и увидеть, что некоторые из них достаточно далеко, чтобы игнорировать текст. Правильно, что позволяет та же формула. всю дорогу вниз.
Хорошо, это было, когда я начинал использовать Excel, я использовал это, а затем я обнаружил расширяющийся диапазон, расширяющийся диапазон говорит, что мы собираемся сделать L $ 2: L2, и что происходит, это всегда начинается со строки 2, но затем он переходит в текущую строку. Итак, когда вы посмотрите, как это работает при копировании, мы всегда запускаем строку 2, но мы спускаемся к текущей строке, и это стало моим любимым методом. Я подумал, о, это намного сложнее, и когда мы заходим в Параметры Excel, переходим на вкладку Формулы и выбираем R1C1 в Справочном стиле. Хорошо, видите ли, R1C1, все эти формулы полностью одинаковы. Я не знаю, понимаете ли вы R1C1, просто хорошо знать, что у нас есть идентичные формулы R1C1 на всем протяжении.
Давай вернемся. Итак, этот метод мне нравился, пока Чарльз Уильямс, специалист по программе Excel MBP из Англии, у которого есть потрясающая статья о скорости формул, скорости формул Excel, полностью не развенчал этот метод. Этот метод, допустим, у вас есть 10 000 строк, каждая формула смотрит на две ссылки. Итак, вы смотрите на 20 000 ссылок, но это, это два, это три, это четыре, это пять, а последний, 10000 ссылок, и это ужасно медленнее. и поэтому я перестал пользоваться этим методом.
Затем я продолжаю читать Зака в книге Кевина Джонса о таблицах Excel и обнаруживаю еще одну проблему с этим методом. Таким образом, одна из полезных функций, предлагаемых таблицами, - это «автоматическое форматирование и добавление, удаление, сортировка и фильтрация строк формул». Хорошо, это цитата из его книги. А чтобы добавить строку в таблицу, вы просто переходите к самой последней ячейке в таблице и нажимаете вкладку. Так что здесь все работает. Мы упали до 70, это здорово, а затем A104, и я поставлю здесь 100. Хорошо, так что 70 должно измениться на 170, и это так, но это 70 вообще не должно было меняться. Хорошо 68 + 2 - это не 170. Я сделаю это еще раз. 104 и положить еще сотню в последнюю - правильно. Эти двое не правы. Хорошо, у нас есть странная ситуация, когда выИспользуя эту формулу, и вы конвертируете в таблицу, вы начинаете добавлять строки, промежуточная сумма не будет работать. Насколько это плохо?
Итак, Зак предлагает два обходных пути, и оба требуют немного знаний о том, как работают ссылки на структуру. У нас просто будет новый столбец, и если бы я хотел указать количество, равное количество, правильно, чтобы = (@ Qty) указывал количество в этой строке. О, круто, есть еще одна ссылка, где мы используем Qty без @. Проверь это. Итак, = SUM (INDEX ((Qty), 1: (@ Qty)) означает все количества, и мы собираемся сказать, что мы хотим SUM от первого количества, поэтому (INDEX ((Qty), 1 говорит, что здесь первое значение, вплоть до текущего количества строк, и здесь используется действительно специальная версия индекса, когда за индексом следует двоеточие, оно фактически изменяется на ссылку на ячейку. Хорошо, этот обходной путь, к сожалению, нарушает правило Чарльза Вильямса из, мы 'нам придется просматривать каждую ссылку, и поэтому, когда вы получите 10 000 строк, это будет происходить очень, очень медленно.
У Зака есть другой обходной путь, который не нарушает проблему Чарльза Вильямса, но использует ужасное смещение. OFFSET - это непостоянная функция, поэтому каждый раз, когда вы что-то вычисляете, OFFSET будет пересчитываться, а все, что находится ниже OFFSET, будет пересчитано. Это просто отличный способ полностью испортить ваши формулы, и что это делает, он говорит, что мы берем сумму из этой строки, поднимаемся на одну строку выше нуля столбцов и поэтому то, что это делает, говорит: возьмите сумму из предыдущей строки, а затем мы добавляем к ней количество из этой строки. Хорошо, теперь все мы каждый раз просматриваем две ссылки, но, к сожалению, OFFSET вводит изменчивые функции.
Что ж, вот и все, больше, чем вы когда-либо хотели знать о промежуточных итогах. Я думаю, что мое окончательное мнение здесь - использовать этот метод, потому что он выглядит только двумя. Та же самая формула, и ваши ссылки на структурированные таблицы будут работать.
Для этого исследования и 39 других действительно хороших советов ознакомьтесь с этой книгой XL, 40 величайших советов по работе с Excel всех времен.
Recap for this episode we talked about three ways to do running totals. The first method has a different formula, row 2, than all the other rows. It's equal left in row 2 and then equal left plus up in rows 3 through N, but if you try and just use that same formula, equal left plus up, all the way down, how you're going to get a #Value Error. So =SUM(Up,Left), which is previous total, plus this roadmap, that works great, no Value Errors and then the expanding range which I use to love. They're cool, but until I read Charles Williams white paper on Excel form of speed. Then I started to hate these expanding references. It also has a problem when you use CTRL T and add new rows. Excel can't figure out how to expand that formula, how to add new rows. I love this tip go to the very last cell in the table and press Tab, that will add a new row and then we talked about some structured referencing, where we're using quantity in this row and then all quantities. =SUM(OFFSET((@Total),-1,00,(@Qty)).
Хорошо, я хочу поблагодарить Зака за этот совет. Я хочу поблагодарить вас за то, что заглянули. Увидимся в следующий раз на другой сетевой трансляции от.
Скачать файл
Загрузите образец файла здесь: Podcast2004.xlsx
