Массивы, СУММА + ИНДЕКС/ПОИСКПОЗ
Примечание. Пробовал в Excel и Google Таблицах, но я предпочитаю Таблицы.
В основном я хочу получить сумму группы данных, используя INDEX и MATCH (потому что параметры будут зависеть от раскрывающегося списка):
Желаемый результат:
Итак, для этого потребуется несколько вещей:
- Converting the cell D13(April) to a Month
- Converting the "weekof" column to a Month
- Using INDEX and MATCH and MATCH again, I'm assuming because it's multiple cell references.
Вот мое решение в настоящее время ниже:
=SUM(INDEX(D5:I9, MATCH(MONTH(D13&1),ARRAYFORMULA(MONTH(C5:C9)),0), MATCH(E12,D4:I4,0)))
Это возвращает БЛИЖАЙШЕЕ значение:
270
Вместо:
804
Почему это значение?
270+500+34 = 804
Ответы 3
Если вы не строго используете ИНДЕКС и ПОИСКПОЗ, вы можете использовать следующее решение:
Добавьте дополнительное имя столбца «Месяц», этот столбец будет извлекать название месяца из столбца даты, используя функцию ТЕКСТ, как показано ниже:
=IF(C3<>"",TEXT(C3,"mmmm"),"")
Операторы if гарантируют, что только заполненные даты будут иметь значение месяца, поскольку вы должны заполнить этот столбец приведенной выше формулой для определенного количества ячеек.
Теперь вы можете просто использовать функцию СУММЕСЛИ в ячейке E13 или где угодно:
=SUMIF(B:B,D13,D:D)
Если вы не хотите, чтобы столбец «Месяц» отображался в вашей таблице данных, вы можете поместить его в конец таблицы и скрыть.
'''=МАССИВФОРМУЛА(СУММЕСЛИ(B:B,D13,ИНДЕКС(D4:I9,МАССИВФОРМУЛА(4:9),ПОИСКПОЗ(E12,D4:I4,0))''' это моя попытка, но это не работает
Вы имеете в виду, что выпадающий список должен содержать только месяцы, указанные в столбце даты? Не все месяцы с января по декабрь
либо работает. Я могу получить часть месяцев, но я не могу получить показатели отдельных столбцов (count1, избранное и т. д.).
Добро пожаловать. Удачи ?
Вы можете использовать сводную таблицу и группировать даты по годам и месяцам.
Вместо этого вы можете напрямую использовать FILTER, а затем SUM результат, чтобы упростить формулу до этой:
Формула:
=SUM(FILTER(D:D, TEXT(C:C,"MMMM") = E13))
Выход:
ОБНОВЛЕНИЕ:
- Приведенная выше формула также должна обновляться, когда значение раскрывается. Выпадающий список — это просто данные, которые можно изменить с заранее определенными значениями, кроме того, они должны быть такими же при использовании обычной ячейки.
- Чтобы сопоставить столбцы, используйте
MATCHиINDEXвместе с приведенной выше формулой. См. модифицированную формулу ниже. - Будьте осторожны с циклической зависимостью. убедитесь, что ваши диапазоны не мешают фактической ячейке, в которую вы помещаете свою формулу.
Соответствие столбцов:
Но это не приводит к раскрывающейся зависимости? Моя проблема в том, что я хотел бы, чтобы коллеги могли выбирать месяц и столбец
использование раскрывающегося списка для месяца должно по-прежнему работать. Но для столбца я не смог его включить. Попробую изменить его для части столбца с помощью фильтра, мне придется объединить его с индексом и сопоставить, я верю
@clucko87, я изменил приведенную выше формулу, см. раскрывающиеся значения месяца и столбцов.
Это хороший звонок. Но как я думаю добиться раскрывающейся зависимости на основе столбца?