Сумма значений на основе значения строки и заголовка столбца

У нас есть документ Google Sheet, который содержит столбец даты с каждой датой в качестве значения строки, а также несколько столбцов (некоторые из которых имеют одно и то же имя), и мы хотим суммировать значения в ячейках, где строка является определенной датой, и столбец имеет определенный заголовок. Например, если дата - 01/03/2017, мы хотим просуммировать все значения, которые имеют заголовок столбца «X» на эту дату. Можно ли это сделать?


google-sheets google-sheets-formula sum gs-vlookup
person Matt Scott Gibbs    schedule 10.03.2017    source источник
comment
Примеры данных для тех, кому они нужны ниже ....   -  person Matt Scott Gibbs    schedule 13.03.2017

Ответы (3)


arrow_upward
0
arrow_downward

Да, это может быть сделано

=SUMIF($C$3:$J$3,"X",OFFSET(C3:J3,MATCH(B1,B4:B15,0)+3,0))

Сломан

=sumif($C$3:$J$3 [<-header row with X],"X" [<-what we're looking for],C3:J3 [<-row to sum])

формула выше суммирует строку заголовка, если есть "X" (не очень полезно)

Я использовал смещение (C3: J3, [номер строки], 0) с диапазоном строки заголовка, чтобы подтолкнуть его к строке, соответствующей 01/03/2017.

Чтобы получить номер строки от 01.03.2017, я использовал Match () и поместил 01.02.2017 в ячейку B1

MATCH(B1,B4:B15 [range of dates] ,0)

Я добавляю 3, потому что мой диапазон начинается с 4

Вы можете жестко закодировать дату в формуле, заменив B1 на

DATEVALUE("01/03/2017")

Я не пробовал это в Google Таблицах, поскольку у меня нет доступа в данный момент, но он работает в Excel, и я попробую это сделать позже в Таблицах.

Вот формула, которую вы можете вставить в A2 на листе «Сумма данных».

= СУММЕСЛИ (Данные! $ B $ 1: $ J $ 1, B $ 1, СМЕЩЕНИЕ (Данные! $ B $ 1: $ J $ 1, ПОИСКПОЗ ($ A2, Данные! $ A $ 2: $ A, 0), 0))

Все дело в изменении исходной формулы в соответствии с вашими данными, а также в правильной блокировке диапазонов с помощью $, чтобы она автоматически заполнялась вниз и поперек без сбоев.

person Gordon    schedule 10.03.2017
comment
Привет, Гордон, большое спасибо за ответ, похоже, мы не за горами, однако я все еще не могу заставить формулу работать правильно. См. Ниже таблицу, которую я пытаюсь суммировать. docs.google.com/spreadsheets как ячейки в сумме данных, которые необходимо заполнить, на основе значений на вкладке «Данные». Не могли бы вы поделиться своим опытом по этой проблеме. Большое спасибо, - person Matt Scott Gibbs; 13.03.2017
comment
Я обновил свой ответ формулой, которая работает на вашем листе и может быть заполнена автоматически. - person Gordon; 13.03.2017
comment
Гордон, друг мой, большое тебе спасибо за помощь. Если вы когда-нибудь окажетесь в Лондоне, я должен вам пинту пива! Удачного дня и еще раз спасибо! - person Matt Scott Gibbs; 13.03.2017

arrow_upward
0
arrow_downward

Используйте INDEX, чтобы отделить соответствующий столбец для SUMIF.

=SUMIF(A:A, G4, INDEX(A:E, 0, MATCH(H4, 1:1, 0)))

введите здесь описание изображения

person Community    schedule 10.03.2017
comment
Как я понял, OP будет несколько столбцов, отмеченных x. Я могу ошибаться. - person Scott Craner; 10.03.2017
comment
Просто перечитайте вопрос, и я считаю, что ваша интерпретация верна. Жаль, что не было предоставлено никаких выборочных данных (с ожидаемыми результатами). На самом деле, похоже, 01/03/2017 уникален. В этом случае OP необходимо перенести это решение. - person ; 10.03.2017
comment
Привет всем, я создал для вас образец таблицы данных, но пока у меня все еще возникают проблемы. Любая помощь приветствуется. Большое спасибо. docs.google.com/ spreadsheets - person Matt Scott Gibbs; 13.03.2017

arrow_upward
0
arrow_downward

Учитывая лист, где:

  • Ячейка B1 содержит интересующую дату (например, 01.03.2017)
  • Ячейка B2 содержит интересующий заголовок (например, X)
  • Ячейка B3 возвращает интересующую сумму (значения всех столбцов с заголовком «X» на 01.03.2017).
  • Строка 4 содержит заголовки для оценки (например, «Дата», «A», «B», «X», «C», «X», «D»).
  • Последующие строки содержат фактические данные (например, даты в столбце A и данные в столбцах B: G).

Подробности см. На изображении по ссылке ниже:
Пример с интересующими ячейками выделено желтым


Следующая формула должна вернуть ожидаемый результат:

=SUMIF(4:4,B2,INDEX(A:G,MATCH(B1,A:A,0),0))


Я использовал Google Таблицы на португальском языке. Итак, фактически протестированная формула была:

=SOMASE(4:4;B2;ÍNDICE(A:G;CORRESP(B1;A:A;0);0))

Надеюсь, это было полезно.

person Tadeu    schedule 23.05.2020