#excel #excel-formula #offset
#excel #excel-формула #смещение
Вопрос:
Привет, я пытаюсь получить сумму, которая будет фильтроваться по условию («A») и добавлять все даты в диапазоне (пример 6/1/19 — 6/12/19). Используя смещение, соответствие и индекс, я получаю результат, который намного превышает ожидаемый результат. Не уверен, что я ошибаюсь в этом. Прилагаю скриншот формулы, данных и выходных данных. Ниже приведены данные, вставленные в виде текста.
Скриншот данных и текущей формулы
Вот текущая формула:
=SUM(OFFSET($B$3,MATCH("A",$B$3:$B$17,0),MATCH($D$1,$B$3:$N$3,0),COUNTIF($B$3:$B$17,"A"),MATCH($D$2,$C$3:$N$3,0)-MATCH($D$1,$C$3:$N$3,0)))
Вот данные:
| Type | 6/1/2019 | 6/2/2019 | 6/3/2019 | 6/4/2019 | 6/5/2019 | 6/6/2019 | 6/7/2019 | 6/8/2019 | 6/9/2019 | 6/10/2019 | 6/11/2019 | 6/12/2019 |
|------|----------|----------|----------|----------|----------|----------|----------|----------|----------|-----------|-----------|-----------|
| A | 983 | 950 | 1222 | 1329 | 1254 | 1176 | 1120 | 1018 | 974 | 931 | 989 | 925 |
| A | 454 | 483 | 412 | 376 | 366 | 366 | 338 | 414 | 456 | 369 | 390 | 380 |
| A | 308 | 361 | 337 | 377 | 361 | 340 | 323 | 361 | 383 | 385 | 350 | 305 |
| B | 196 | 190 | 198 | 212 | 173 | 180 | 181 | 185 | 179 | 173 | 21969 | 16945 |
| C | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| B | 191 | 250 | 244 | 222 | 197 | 172 | 181 | 155 | 184 | 184 | 168 | 147 |
| C | 211 | 233 | 158 | 142 | 128 | 139 | 164 | 189 | 214 | 225 | 162 | 139 |
| D | 370 | 403 | 420 | 833 | 1177 | 632 | 432 | 419 | 467 | 438 | 397 | 365 |
| A | 202 | 244 | 230 | 263 | 215 | 193 | 178 | 231 | 754 | 514 | 246 | 251 |
| B | 229 | 242 | 261 | 281 | 253 | 251 | 235 | 246 | 307 | 274 | 224 | 216 |
| C | 262 | 261 | 259 | 212 | 209 | 205 | 211 | 238 | 273 | 223 | 222 | 241 |
| A | 370 | 403 | 420 | 833 | 1177 | 632 | 432 | 419 | 467 | 438 | 397 | 365 |
| D | 262 | 261 | 259 | 212 | 209 | 205 | 211 | 238 | 273 | 223 | 222 | 241 |
| D | 370 | 403 | 420 | 833 | 1177 | 632 | 432 | 419 | 467 | 438 | 397 | 365 |
Комментарии:
1. Что мы должны делать с вашим скриншотом? Это практически бесполезно для правильного устранения неполадок. Это нельзя скопировать / вставить в рабочий лист. Необходимость ручного ввода данных обескураживает тех, кто мог бы вам помочь. Чтобы сделать данные полезными, отредактируйте свой вопрос, разместив его в виде текста, возможно, используя этот генератор таблиц Markdown ,
2. @RonRosenfeld спасибо за ваш комментарий. Я отредактировал свой вопрос, чтобы включить данные в виде текста. Форматирование не самое лучшее. Спасибо!
3. Я могу помочь с форматированием формулы. Но, пожалуйста, используйте ссылку на генератор таблиц Markdown в моем комментарии выше, чтобы данные могли быть правильно отформатированы.
Ответ №1:
Для формульного подхода я предлагаю:
=SUM(INDEX($C$4:$N$17,AGGREGATE(15,6,1/($B$4:$B$17=C22)*ROW($B$4:$B$17)-3,ROW(INDEX($A:$A,1):INDEX($A:$A,COUNTIF($B$4:$B$17,C22)))),COLUMN(INDEX($1:$1,1):INDEX($1:$1,COUNTA($C$3:$N$3)))))
где C22 содержит тип, по которому вы хотите отфильтровать
Если у вас есть Excel O365 с функцией фильтрации, вы можете использовать:
=SUM(FILTER($C$4:$N$17,$B$4:$B$17=C21))
Или же вы могли бы использовать Сводную таблицу или Power Query
Ответ №2:
Видишь https://exceljet.net/formula/sum-matching-columns-and-rows
Ваша реализация будет:
=SUMPRODUCT(C4:N17*(B4:B17=E1)*(C3:N3>=D1)*(C3:N3<=D2))