найти следующую дату после сегодняшнего дня в группе в сводной таблице Excel

Question

У меня есть таблица на одном листе с "транзакциями". Каждая строка содержит имя поставщика, причитающуюся или уплаченную сумму в зависимости от типа транзакции и дату оплаты / дату транзакции. Вот несколько упрощенных примеров данных:

Vendor      Date    Invoice  Payment
Vendor A    6/30    $200       
Vendor A    6/30            ($200)  
Vendor B    7/5     $500    
Vendor B    7/5             ($500)
Vendor C    10/28   $50
Vendor A    10/30   $100
Vendor C    11/15   $50       

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

Вот как выглядит моя сводная таблица, плюс столбец даты, который я хотел бы добавить (при условии Today () = 10/23):

Vendor      Date    Owed
Vendor B    7/5     -    
Vendor C    10/28   $100
Vendor A    10/30   $100

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

tl; dr: я хочу найти следующую самую раннюю дату в каждой группе или последнюю дату. Как мне это сделать?

Answer 1

Ну, может быть, это не самый простой способ приблизиться к этому, но я бы использовал VBA для создания функции, которая возвращает следующую дату исполнения (или наибольшую дату, если следующая дата исполнения <Сегодня). Формулы массивов - это еще один вариант, но они довольно неэффективны.

1. Сохраните книгу в формате .xlsm (с поддержкой макросов).
2. Выберите все значения в столбце Vendor и определите именованный диапазон для них (код ниже предполагает, что вы назвали именованный диапазон "Vendor"). Я не рекомендую выбирать весь столбец, потому что это заставит код работать очень медленно. Просто выберите ячейки с датами; именованный диапазон будет динамически расширяться, если вы вставите новые строки. Чтобы быть в безопасности, вы можете выбрать несколько дополнительных клеток внизу; в том числе пустые клетки ничего не повредит.
3. Нажмите ALT+F11, чтобы открыть редактор VBA.
4. В меню "Вставка" выберите "Модуль".

5. Вставьте эту функцию VBA:

   Public Function NextByVendor(ByVal rngVendor As Range)
   
   Dim c As Range
   Dim strVendorName
   Dim lngToday As Long
   Dim lngNextDate As Long
   
   lngToday = Int(CDbl(Now()))
   lngNextDate = 0
   
   strVendorName = rngVendor.Value
   
   'Get largest date value
   For Each c In Range("Vendor")
       If c.Value = strVendorName And _
       c.Offset(0, 1).Value > lngNextDate Then
           lngNextDate = c.Offset(0, 1).Value
       End If
   Next c
   
   'If largest date is > today, get next available date
   If lngNextDate > lngToday Then
       For Each c In Range("Vendor")
           If c.Value = strVendorName And _
           c.Offset(0, 1).Value < lngNextDate And _
           c.Offset(0, 1).Value > lngToday Then
               lngNextDate = c.Offset(0, 1).Value
           End If
       Next c
   End If
   
   NextByVendor = lngNextDate
   
   End Function
   

6. Сохраните и закройте окно VBA. Вернитесь в электронную таблицу и добавьте новый столбец справа от ваших данных. В первой строке данных (скажем, это 2) введите формулу "= NextByVendor (A2)".

7. Скопируйте формулу полностью вниз. Вы должны получить что-то вроде этого:

   Vendor          Date            Invoice   Payment   Next Payment Due
   Vendor A        6/30/2012       $200.00             10/30/2012
   Vendor A        6/30/2012                 ($200)    10/30/2012
   Vendor B        7/5/2012        $500.00             7/5/2012
   Vendor B        7/5/2012                  ($500)    7/5/2012
   Vendor C        10/28/2012      $50.00              10/28/2012
   Vendor A        10/30/2012      $100.00             10/30/2012
   Vendor C        11/15/2012      $50.00              10/28/2012
   

8. Обновите свою сводную таблицу; новые данные теперь должны быть доступны для него. Вы можете установить тип значения "Срок оплаты следующего платежа" в сводной таблице на "Среднее" или «Мин.».

Надеюсь это поможет!

- Джм