Как просуммировать каждую строку в именованном диапазоне в Excel?

Question

Я пытаюсь перепроектировать шаблон бюджета, чтобы иметь возможность его изменить. В нем есть разделы для разных расходов (дом, развлечения, транспорт и т. д.). В каждом разделе есть несколько расходов (транспорт включает в себя страхование автомобиля, бензин, парковка и т. д.). Каждый раздел представляет собой именованный диапазон, и каждый расход суммируется в ячейке в правом конце строки. Однако шаблон делает каждую строку в разделе с той же функцией, и я не могу понять, как это работает.

Вот его изображение с функцией наверху и именем manager. Как я уже сказал, выделенная функция — это та же самая функция во всех итогах строк для «Ежедневной жизни». https://i.stack.imgur.com/mbMij.jpg

У меня точно такие же группы имен (менеджер имен и все диапазоны идентичны) и та же раскладка, но я не могу получить ту же функцию, которая выдает ошибку и не принимается. Лучшее, что я смог сделать, это ввести SUM(Daily), которая суммирует все строки в разделе, а не каждую строку по отдельности.

Может ли кто-нибудь объяснить эту функцию на рисунке =SUM(Daily[@[January]:[December]]), в частности символ «@», и как Excel узнает, что JAN (в заголовке столбца) означает январь, если его нет в менеджере имен?

БОНУС: Может ли кто-нибудь объяснить, как работают функции суммы по разделу и общей суммы? https://i.stack.imgur.com/ydMfz.jpg

Answer 1

Ну, вопросов много, и я надеюсь, что отвечу на каждый. Надеюсь.

Первое и самое главное: макет данных — это Table. A Tableотличается для Excel от диапазона ячеек, в которых данные появляются в старом виде. Это формальный объект, в котором соблюдаются простые правила, и взамен Excel не нужно принимать во внимание все возможные странные вещи, которые кто-либо в мире может сделать при настройке (я видел, как итоговые ячейки появляются В данных, а не ниже, выше или слева или справа, и в том же диапазоне, появляются в разных строках для разных столбцов. Это вписывалось в схему парня, слабоумным образом, но что, черт возьми, Excel должен сделать с этим, когда это выглядит странным и запутанным для человека? Или десяти человек?

В любом случае, ваша таблица называетсяЕжедневно. Кстати, вы можете сказать, что это таблица, по тому, как набраны элементы формулы, хотя бы по какой-то другой причине: видите квадратные скобки ("[ ]")? Они почти всегда используются только в таблицах и еще в нескольких местах (все они, вероятно, используют технологию таблиц для работы). В любом случае, при создании простой формулы SUM(), когда вы выделяете всю строку, Excel преобразует адреса ячеек, которые вы видели, когда выделяли, в ссылки на таблицы на основе заголовков столбцов: с января по декабрь, являющихся границами диапазона, который вы здесь выделяете. Таким образом, вместо ссылки типа D20:O20 вы получаете [Январь:Декабрь].

В этой формуле остался один элемент:@. Поскольку это Таблица, если бы у вас не было возможности сообщить Excel, что вы имеете в виду ТОЛЬКО ЭТУ СТРОКУ, он бы подумал, что вы суммируете ВСЕ строки. В Excel сообщает @Excel "только эту строку" и используется не только в Таблицах и других местах, но и в КРУПНОМ смысле при использовании Spillформул. Если говорить точнее, то это сообщает Excel ЭТУ СТРОКУ, поэтому вы суммируете только строку Химчистка, суммируя числа во всех столбцах с января по декабрь.

Во многих отношениях, SUM()используемые как здесь, и SUBTOTAL()(используемые где угодно) являются одним и тем же, в некотором роде. Но SUBTOTAL()имеет огромную разницу: Excel готов игнорировать ПРОМЕЖУТОЧНЫЕ ИТОГИ, полученные с помощью него при суммировании столбца. Но он не будет игнорировать промежуточные итоги, созданные с помощью, SUM()поэтому, если они находятся в том же столбце, что и некоторые необработанные числа, вы получите в два раза больше итога, чем должны. SUBTOTAL()также имеет кучу разных вещей, которые он будет возвращать, например, среднее значение, которое SUM()само по себе не может.

Человек, который создал электронную таблицу, явно выбрал очень стандартный и разумный способ настройки итогов. Он получил горизонтальные итоги, используя SUM()так, чтобы вертикальные итоги, которые он затем сделал, не игнорировали их автоматически, а затем использовал SUBTOTAL()для создания вертикального итога, который вы видите в таблице.

Ничего из этого нет в именованных диапазонах, потому что они доступны автоматически (настолько автоматически, что иногда их трудно избежать...), потому что все это — Таблица. Поскольку это Таблица, вы можете использовать заголовки столбцов в качестве ссылок. Раньше вы могли делать это постоянно, но они отняли это около 17 лет назад, а затем вернули в этой форме... вздох...

Также иногда раздражает тот факт, что заголовки столбцов автоматически являются текстом, поэтому они не могут быть формулами. Поэтому динамические заголовки невозможны в Таблицах.

Одна из причин, по которой это может не выглядеть как таблица, заключается в том, что вы не видите неприятных значков фильтрации справа от каждого заголовка, обычно лежащих поверх части заголовка... Их легко убрать, и автор явно так и сделал. Без них большинство людей долгое время НЕ будут думать о "таблице", но формирование ссылок на формулы - это явный признак. Еще одна вещь, скрывающая этот факт, заключается в том, что большинство людей привыкли к виду "зеленой полосы" в таблицах (любого цвета, но эта бумага раньше была в основном зеленой, а мне 845 лет, так что...). Это тоже можно отформатировать, когда это неуместно или считается ненавистным (Почему? Почему? Это делает таблицы на весь экран гораздо более читабельными!), и в этом случае, очевидно, что это не к месту. Можно было бы использовать его в каждой области данных, но поскольку они различаются по количеству строк в каждой, это потребовало бы больших усилий из года в год.

Так:

1. Это Tableи поэтому у него есть некоторые хорошие вещи, доступные вам
2. Данные находятся в двух разделах, левый столбец с заголовками раздела и затем подраздела (отдельные строки в вашем случае). Например, «Химчистка», а затем раздел «заполнить по мере его существования» под заголовками с января по декабрь.
3. Каждая строка подсчитывается за год справа с использованием SUM()функций.
4. В каждом столбце с помощью функций подсчитывается сумма за месяц (и год в крайнем правом столбце) SUBTOTAL().
5. Функции SUM()работают только с одной строкой за раз, поскольку они помещают @перед первым элементом в своей ссылке на диапазон («@January»).
6. ОТДЕЛЬНЫЕ разделы («ДОХОД», «ДОМ» и т. д.) имеют итоги по своим битам за каждый месяц и за год с использованием SUBTOTAL()функций, поскольку они будут игнорироваться при подсчете итогов по общему столбцу. (В отличие от того SUM(), чьи результаты по каждому разделу БУДУТ включены в нижние, общие итоги, дающие результаты в два раза больше, чем они должны быть.)

Очень правдоподобно и полезно изложено с вниманием к важным деталям. Можно утверждать, что такие вещи лучше поместить в простую таблицу или даже версию таблицы... простую таблицу, если хотите, а затем сводную таблицу (да, 845 лет, так что перекрестная таблица, даже перекрестные вкладки, все еще звучат для меня нормально) для представления. В конце концов, сводные таблицы, или, если быть точнее, таблицы Power Pivot имеют много преимуществ (оооо... срезы...) и возможности суммирования сами по себе, и так далее и тому подобное, но это совсем другой материал для изучения, и это очень хорошо решило чьи-то потребности.

Не уверен, что вам нужно сделать, поэтому не могу предложить никакой помощи. Но если вы просто хотите как-то улучшить усилия или сделать это как-то своим, вы не сможете, это так хорошо сделано.Если только вы не пойдете по пути сводных таблиц. Так что если это все, то избавьте себя от некоторых проблем: это очень прилично, и вам не нужно искать улучшения. Если это просто босс хочет, чтобы что-то было сделано или что-то еще, ну, я понимаю вас, мы все через это проходили.