РЕДАКТИРОВАТЬ: РЕШЕНИЕ НИЖЕ
Пример облегчит понимание. Идея очень проста, по крайней мере, так кажется.
С применением значений динамического поиска я хочу сопоставить значения одного столбца со значениями другого и перечислить отдельные результаты. Если из-за динамического диапазона пространства значений поиска я могу «суммировать» выходные данные в одной ячейке в случае нескольких совпадений.
Также возможно добавить желаемое количество строк вместо того, чтобы учесть любые возможные дополнительные совпадения, но я пока не смог найти формулу для достижения этой цели.
Все столбцы, данные и, следовательно, значения поиска являются динамическими.
Выделены проблемные области:
Следующие формулы, которые у меня есть сейчас (перетащите вниз на ячейки ниже):
Колонна Е
{=IF(F7=0,"",IFERROR(INDEX($B:$B,MATCH($F7,$C:$C,0),),0))}
Колонна F
{=IFERROR(INDEX($C$1:$C$26,SMALL(IF($C$1:$C$26>0,ROW($C$1:$C$26)),ROW(2:2))),0)}
Колонна G
{=IFERROR(IF(E7<>E6,IF(E7<>E8,SUMIF($B$7:$B$26,E7,$D$7:$D$26),IF(COUNTIFS($B$7:$B$26,E7,$D$7:$D$26,">0")>2,SUMIF($B$7:$B$26,E7,$D$7:$D$26),INDEX($D$7:$D$26,MATCH(0,IF($E7=$B$7:$B$26,COUNTIF($J$8:$J9,$D$7:$D$26),""),0)))),IF(COUNTIFS($B$7:$B$26,AQ7,$D$7:$D$26,">0")>2,SUMIF($B$7:$B$26,AQ7,$D$7:$D$26),INDEX($D$7:$D$26,MATCH(0,IF($AQ7=$B$7:$B$26,COUNTIF($AU$5:$AU7,$D$7:$D$26),""),0)))),"")}
РЕДАКТИРОВАТЬ: НАЙТИ ПРИЕМЛЕМОЕ РЕШЕНИЕ
Поэтому мне удалось довольно легко выполнить то, что я хотел, с помощью дополнительных столбцов «помощник». Я решил согласиться с тем, что в столбце бюджета отображается только сумма всех записей бюджета за определенный день. Затем, чтобы добавить одну пустую строку, чтобы освободить место для даты, на которую была введена запись бюджета, но на которую не было совершено ни одной покупки («квитанция»), не нарушая динамический динамический диапазон столбца даты и квитанции, Я использовал следующую настройку со ссылкой на пример экрана:
Условие: запись квитанции никогда не разделяет ту же строку, что и запись бюджета
1) Вспомогательный столбец № 1 заменяет каждую запись бюджета в столбце D уникальным значением, которое можно использовать для поиска.
2) Вспомогательный столбец № 2, каждая ячейка отображает свой номер строки.
3) Вспомогательный столбец № 3 копирует точные даты совпадения во все значения поступления столбца C в соответствующих номерах строк.
4) Вспомогательный столбец № 4 делает то же, что и выше, с той разницей, что все пробелы заменяются датами, в которые были сделаны записи бюджета.
5) Вспомогательный столбец № 5 объединяет вспомогательные столбцы № 3 и № 4 , добавляя только уникальные даты бюджета в пустые места, которых нет в столбце № 3.
6) Желаемый результат для столбца Дата (столбец I) формируется путем удаления всех пустых мест из вспомогательного столбца № 5.
7) ЖЕЛАЕМЫЙ ВЫХОД для столбца Receipt (столбец J) формируется путем выполнения той же процедуры со вспомогательными столбцами, чтобы добавить желаемую дополнительную строку. Это достигается добавлением еще одного вспомогательного столбца № 6, заменив дату, добавленную в строку, пустой ячейкой в вспомогательном столбце № 4 нулевым значением. Таким образом, делается различие между добавленной ячейкой "нулевого значения" и другими пустыми ячейками. При удалении всех пустых ячеек в желаемом выходном столбце J нулевое значение создаст дополнительную нужную строку, не затрагивая записи поступления и соответствующие им даты.
8) Желаемый результат для столбца Бюджет (столбец K) теперь просто показывает сумму всех записей бюджета на конкретную дату, добавляя это значение в первую строку каждого нового экземпляра соответствующей даты в столбце I.
Смотрите все формулы ниже:
Все приведенные ниже формулы помещаются в первую строку каждого соответствующего столбца, который можно скопировать. Для этого примера я использую столбец AA для вспомогательного столбца № 1, AB для № 2 и так далее:
Вспомогательная колонна № 1 - АА
= Если (d7> 0, АВ7 "")
Вспомогательная колонна № 2 - AB
= СТРОКА ()
Вспомогательная колонна № 3 - AC
= ЕСЛИ (ИЛИ (ЕПУСТО (С7), С7 = 0), "", ИНДЕКС ($ B:$ B, ПОИСКПОЗ ($ С7, $ C:$ С, 0),))
Вспомогательная колонна № 4 - н.э.
= ЕСЛИ (ЕПУСТО (АА7), "", ИНДЕКС ($ B:$ B, ПОИСКПОЗ ($ АА7, $ АА:$ А. А., 0),))
Вспомогательная колонна № 5 - AE
= IF(AC7 = "", IF(COUNTIF($ AC:$ AC, AA7)= 0, AA7, ""), AC7)
Вспомогательная колонна № 6 - AF
= IF(C7 = "", IF(COUNTIF($ AC:$ AC, AA7)= 0, 0, ""), C7)
ЖЕЛАЕМАЯ ВЫХОДНАЯ КОЛОННА I
{= ЕСЛИОШИБКА (ИНДЕКС ($ AE $ 1:$ АЯ $ 100, МАЛЫЙ (ЕСЛИ ($ AE $ 1:$ АЯ $ 100 <> "", СТРОКА ($ АИ $ 1:$ АЯ $ 100)), СТРОКА (1:1))), 0)}
ЖЕЛАЕМАЯ ВЫХОДНАЯ КОЛОННА J
{= ЕСЛИОШИБКА (ИНДЕКС ($ AF $ 1:$ AF $ 100, МАЛЫЙ (ЕСЛИ ($ AF $ 1:$ AF $ 100 <> "", СТРОКА ($ AF $ 1:$ AF $ 100)), СТРОКА (1:1))), 0)}
ЖЕЛАЕМАЯ ВЫХОДНАЯ КОЛОННА K
= ЕСЛИОШИБКА (ЕСЛИ (I9 <> I8, SUMIF ($ B $ 7:$ B $ 100, I9, $ D $ 7:$ D $ 100)"")"")
Не уверен, если это, добавив 6 (!) дополнительные вспомогательные столбцы, это наиболее эффективный способ, но он выполняет свою работу. Было бы здорово, чтобы формула массива объединила все это, но я не знаю, возможно ли это. Любые рабочие предложения для лучших альтернатив приветствуются.