Я пытаюсь создать электронную таблицу для расчета суммы часов в каждом месяце на основе соответствующих строк. Чтобы сделать пример немного более понятным, для Excel ниже я должен получить значение 28 для Клиента 1 и 16 для Клиента 3.
Я пытаюсь использовать функцию СУММЕСЛИ, но диапазон суммы не может охватывать несколько ячеек.
И вот как я хотел бы видеть информацию:
решение1
Вы можете создать простую формулу:
=SUM(IF(B5:B21="Client 1",G5:AK21))
Это выполнение вычисления массива в 2 шага:
- Возвращает ВСЕ СТРОКИ чисел (даже пробелы), если столбец B = «Клиент 1».
- Как только у меня будут все строки (большинство из которых будут пустыми), вы просто суммируете эти значения.
Вы можете заменить "Client 1"
в формуле ссылку на внешнюю ячейку, если хотите. Также убедитесь, что весь диапазон месяца является точным во второй части формулы G5:AK21
.
решение2
Опираясь на превосходный =SUM(IF())
подход,@Дэйвможно использоватьOFFSET
функция выбора столбцов, соответствующих каждому из 12 месяцев года.
Функция OFFSET
определяет прямоугольный диапазон через его высоту и ширину, причем верхний левый угол диапазона определяется с точки зрения количества строк ниже и столбцов справа от указанной ячейки рабочего листа. В подходе ниже я использую ячейку, выделенную желтым цветом, как указанную ячейку (ячейка E17
).
Диапазон A2:E14
(см. скриншот ниже) предоставляет некоторую информацию, илиметаданные, о том, где расположены столбцы ежедневных значений для каждого из 12 месяцев года относительно выделенной ячейки. Полезные значения находятся в столбцах, озаглавленныхCol_OffsetиКолоннытак, например, для января ежедневные значения начинаются с 1-го столбца справа от желтой ячейки и занимают 31 столбец, для февраля ежедневные значения начинаются с 32-го столбца справа от желтой ячейки и занимают 29 столбцов (изменение года на невисокосный в ячейке изменит B1
значение в ячейке E4
на 28) и т. д. Столбцы, озаглавленныеМесяц#иНакопительныйпромежуточные значения, необходимые для расчетаCol_OffsetиКолонны.
Формулу для суммирования итоговых данных по месяцам и клиентам можно записать в разбивочном формате следующим образом:
=SUM(
IF(Sheet1!$B$18:$B$23=$A2,
OFFSET(Sheet1!$E$17,
1,
VLOOKUP(B$1,Sheet1!$A$3:$E$14,4),
6,
VLOOKUP(B$1,Sheet1!$A$3:$E$14,4)
)
)
)
или, как полная формула
=SUM(IF(Sheet1!$B$18:$B$23=$A2,OFFSET(Sheet1!$E$17,1,VLOOKUP(B$1,Sheet1!$A$3:$E$14,4),6,VLOOKUP(B$1,Sheet1!$A$3:$E$14,5))))
где предполагается, что метаданные и ежедневные данные содержатся на рабочем листе Sheet1
.
Эта формула применяется к Январю/Клиенту 1 и может быть скопирована в оставшиеся ячейки для других месяцев и клиентов, как частично показано на снимке экрана ниже. Убедитесь, что названия месяцев, используемые в метаданных и таблице результатов, точно совпадают, чтобы избежать #N/A
ошибок в результатах.
Второй и четвертый аргументы функции OFFSET
— 1 и 6, поскольку данные начинаются в первой строке после выделенной желтым ячейки, и в показанном примере имеется 6 строк данных. Если имеется другое количество строк, просто подставьте правильное значение.
Третий и пятый аргументы просто используют функцию VLOOKUP
для поискаCol_OffsetиКолоннызначения в метаданных соответствующего месяца.