1

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

Данные содержат имена клиентов, даты, типы транзакций и суммы транзакций. Мне удалось получить число уникальных значений в заданный период времени, которое соответствует моим критериям с этим уравнением:

=SUM(--(FREQUENCY(IF(Table13[Contract/Offer Date]<=EOMONTH(A12,0),IF(Table13[Contract/Offer Date]>=A12,IF(Table13[Type of Transaction]="Offer",IF(Table13[Client]<>"",IF(Table13[Client]=Table13[Client],MATCH(Table13[Client],Table13[Client],0)))))),ROW(Table13[Client])-ROW(C$20)+1)>0))

A12 - это месяц, за который я подвожу, C20 - то, с которого начинаются данные.

Для A12 (октябрь 2016 г.) я получаю уникальный счет 33. Что мне нужно, так это сумма этих 33 строк из столбца: table13 [сумма контракта / предложения]

Я попытался эту формулу, но дал неточные результаты:
=SUM(IF(Table13[Contract/Offer Date] <=EOMONTH(A12,0),IF(Table13[Contract/Offer Date]>=A12,IF(Table13[Type of Transaction]="Offer",IF(Table13[Client] <>"",IF(Table13[Client]=Table13[Client],MATCH(Table13[Client],Table13[Client],0))),Table13[Contract/Offer Amount]))))

Данные клиента содержат несколько пустых ячеек, поэтому важно исправить это.

Я знаю, что приведенная выше формула неверна, потому что суммы контракта / предложения находятся в позиции формулы «Значение, если ложно», но это самое близкое, что я получил.

Любая помощь будет высоко ценится, и дайте мне знать, если вам нужно больше деталей / разъяснений. Полностью застрял

ОБНОВИТЬ:

Вернувшись к исходной сумме, если, частотному уравнению, я смог вернуть "почти" правильные ответы. Счет уникальных номеров выглядит следующим образом: =SUM(IF(FREQUENCY(IF(Table13[Contract/Offer Date]<=EOMONTH(A12,0),IF(Table13[Contract/Offer Date]>=A12,IF(Table13[Type of Transaction]="Offer",IF(Table13[Client]<>"",MATCH(Table13[Client],Table13[Client],0))))),ROW(Table13[Client])-ROW(C$21)+1),1))

Подставляя суммы предложений вместо итоговой "1", я возвращаю почти правильные результаты.

=SUM(IF(FREQUENCY(IF(Table13[Contract/Offer Date]<=EOMONTH(A12,0),IF(Table13[Contract/Offer Date]>=A12,IF(Table13[Type of Transaction]="Offer",IF(Table13[Client]<>"",MATCH(Table13[Client],Table13[Client],0))))),ROW(Table13[Client])-ROW(C$21)+1),Table13[Contract/Offer Amount]))


Неясно, почему это работает только частично. Я представляю результаты частотной формулы построения массива 1 и 0, которые умножаются на суммы предложений. Это верно?

|источник

1 ответ1

0

ОРИГИНАЛ

Итак, я собираюсь ответить на этот вопрос:

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

Предполагая следующее:

  • "Клиент", который вы хотите, находится в A1 (я использовал client3)
  • "Начало периода времени" в B1 (20.11.2016)
  • "Конец периода времени" в C1 (30.11.2016)
  • "Тип" в D1 (предложение)

Тогда эта формула:

=SUMPRODUCT(--(Table13[Client]=A1)*--(Table13[Contract/Offer Date]>=B1)*--(Table13[Contract/Offer Date]<=C1)*--(Table13[Type of Transaction]=D1)*Table13[Contract/Offer Amount])

Вернет 402 000 долларов.

ОБНОВИТЬ

Попытка разбить эту формулу:

=SUM(IF(
  FREQUENCY(
    IF(Table13[Contract/Offer Date]<=EOMONTH(A12,0),
      IF(Table13[Contract/Offer Date]>=A12,
        IF(Table13[Type of Transaction]="Offer",
          IF(Table13[Client]<>"",
            MATCH(Table13[Client],Table13[Client],0)
      )))),
    ROW(Table13[Client])-ROW(C$21)+1),
  Table13[Contract/Offer Amount]
))

Это собирается найти частоту всех клиентов, которые соответствуют критериям (в определенный месяц и только предложения). Здесь две проблемы.

  1. FREQUENCY собирается дополнительно вернуть частоту всех неучтенных клиентов. Это даст вам дополнительный индекс, когда вы попытаетесь SUM к сумме.
  2. Если вы исправили проблему # 1, вы будете суммировать первую "сумму" от этого клиента (которая, скорее всего, выходит за пределы условного диапазона дат).
|источник

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .