Вопрос по excel, array-formulas – Категоризация банковских транзакций в Excel

4

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

The Scenario

Мой заголовок таблицы выглядит так:

<code>| A    | B           | C      | D        |
==========================================
| Date | Description | Amount | Category |
------------------------------------------
</code>

Дата, описание и сумма поступают предварительно из моего банка. Я заполняю категорию для каждой транзакции в столбце D.

Это хорошо, но требует много времени, потому что каждая категория должна быть введена индивидуально и вручную.

The Need

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

Вывод, который я хочу, выглядит следующим образом:

<code>| A        | B             | C       | D           | E         | F           |
==============================================================================
| Date     | Description   | Amount  | Manual cat. | Rule      | Auto cat.   |
------------------------------------------------------------------------------
| 04/08/12 | Starbucks NYC |  -$5.42 | Coffee      | starbucks | Coffee      |
| 04/09/12 | Wal-Mart 468  | -$54.32 | Supermarket | wal-mart  | Supermarket |
| 04/10/12 | Starbucks SF  |  -$3.68 |             |           | Starbucks   |
</code>

Как вы видите, я ввел «руководство» категории в столбце D. Где бы я ни делал это, я вводил классификацию «правило»; в столбце E. Excel затем использует мои записи для автоматического заполнения столбца F,

Логика проста:

Where I enter a manual category, Excel does two things:

Populates my manual category in Column F. Create a rule using the text entered in Column E.

Where Excel comes across a description that contains text used in one of my rules, it populates the relevant category in Column F.

The Benefit

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

My best attempt so far

Я создал способ сделать это, используя только формулы, но у него есть три недостатка:

It necessitates the creation of as many columns as transactions. There is no convenient way of listing the categories and associated rules. There is no way of changing the order the rules are applied in.
Я исправил вопрос, чтобы убрать фокус с подхода, основанного только на формулах. Решения VBA с благодарностью приняты. Matt
Моя система обработки и объединения выписок для моих различных учетных записей старая и скрипит по швам. Я решил использовать ваш вопрос в качестве оправдания, чтобы заменить его. Это будет через день или два, прежде чем я закончу, потому что у меня есть другие задачи. Кто-то еще может найти удовлетворительное решение раньше, но если нет, у меня будет кое-что для вас позже на этой неделе. Tony Dallimore
Это не по теме для переполнения стека. В качестве вопроса, не связанного с программированием, он лучше подходит для Super User brettdj

Ваш Ответ

4   ответа
0

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

Я не хотел писать VBA, поэтому я написал сценарий PowerShell, чтобы сделать это для меня. у него есть массив с именем$Rules где вы определяете скороговорки и их категории. Последним сопоставленным шаблоном будет категория для элемента. Я добавляю * в конец каждого шаблона и использую оператор -подобного оператора.

Это немного медленно, потому что PowerShell медленно обращается к ячейкам Excel и занимает пару минут для 1000 строк, которые есть в моем экспорте выписки по счету. $DesColumn ссылается на колонку с описанием банковской выписки и$CatColumn столбец, в котором будет сохранена категория

После применения сценария вы можете использовать функции Excel PIVOT для создания круговых диаграмм, обобщающих ваши данные. Не забудьте сделать резервную копию вашего файла!

 $xl = New-Object -comobject Excel.Application
 # Show Excel
 $xl.visible = $false
 $xl.DisplayAlerts = $False
 # Create a workbook
 $wb = $xl.Workbooks. open("C:\Accounting\Accounting_2013.xls" )
 # Get sheets
 $ws = $wb.WorkSheets.item( "Costs")
 $ws.activate()
 $DescColumn = 6
 $CatColumn = 7
 $Rng = $ws.UsedRange.Cells
 $intRowMax = $Rng.Rows.Count
 #$intRowMax = 50
 $Rules [email protected](
 @("*FOOD","GROCERY"),
 @("*Hotel","FUN"),
 @("*ADVENTURES","FUN"),
 @("CINEPLEX","FUN"),
  @("EVENT CINEMAS","FUN"),
 @("*Rent","RENT"),
 @("Wdl ATM","ATM"),
 @("IKEA","HOME"),
 @("FORM HOME","HOME"),
  @("KMART","HOME"),
  @("BIG W","HOME"),
  @("PILLOW TALK","HOME"),
  @("BUNNING","HOME")
 @("IGA","GROCERY"),
  @("COLES","GROCERY"),
  @("ALDI","GROCERY"),
   @("FRUITY CAPERS","GROCERY"),
 @("WOOLWORTHS","GROCERY"),
  @("MEGAFRESH","GROCERY"),
 @("CALTEX","CAR"),
 @("COLES EXP","CAR"),
 @("CTX WOW","CAR"),
 @("BP EXPRESS","CAR"),
 @("QLD TRANSPORT","CAR"),
 @("REPCO","CAR"),
 @("FREEDOM FUEL","CAR"),
 @("BP THE GAP","CAR"),
@("MCDONALDS","DINE"),
@("RED ROOSTER","DINE"),
@("*SIZZLER","DINE"),
@("DOMINO","DINE"),
  @("SUBWAY","DINE"),
 @("ROUTE 74","DINE"),
 @("KFC","DINE"),
 @("*PIZZA","DINE"),
 @("GUZMAN","DINE"),
 @("NANDOS","DINE"),
 @("*PIZZERI","DINE"),
 @("MISS INDIA","DINE"),
 @("INDIAN FEAST","DINE"),
 @("VIVIDWIRELESS","BILL"),
 @("TPG","BILL"),
 @("AGL","BILL"),
 @("EnergyAustralia","BILL"),
 @("TRANSLINK","PTRANSPORT")
 )
for ($intRow = 2 ; $intRow -le $intRowMax ; $intRow++) {
     $SvrName = $Rng.cells.item($intRow, $DescColumn).value2
    ""+$intRow+"/"+$intRowMax+" "+ $SvrName
        $Rules | ForEach-Object{
            $key = ($_[0])+"*"
            if($SvrName -like $key)
            {
                $Rng.cells.item($intRow, $CatColumn).value2 = $_[1]
            }
        }
     }
$wb.Save()
$wb.Close()
$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

1

Introduction

Как я уже указывал ранее, приведенное ниже решение является чрезмерно разработанным для ваших неотложных потребностей, поскольку

он предназначен для моих нужд, которые более разнообразны, чем те, которые вы перечислили в своем вопросе:

  • I am in the process of changing banks so I have two current (checking) accounts and two credit card accounts. I also have a number of savings accounts. I hold the electronic statements for all these accounts, which have different formats, across several workbooks.
  • Your example statement is very neat compared with those I receive. These are some descriptions from a recent MasterCard statement which I tidy to a preferred format of "Organisation Name, Location".

.

SAINSBURY'S S/MKT MONKS CROSS
Amazon *Mktplce EU-UK AMAZON.CO.UK LUX
WRAP LOUGHBOROUGH
SAINSBURYS PETROL MONKS CROSS
  • Like you I categorise transactions.
  • Some organisations bill annually in advance or quarterly in advance or arrears. My income varies from month to month. For these transactions, I apportion the amount over the appropriate months so the result better reflects my true financial situation.

Мое решение этих многочисленных требований состоит в том, чтобы иметь подпрограмму контроля для каждой учетной записи, которая знает, где она и для чего используется каждый столбец. Они называют общие процедуры, которые принять рабочую книгу, рабочий лист и т. д. в качестве параметров и выполнить необходимые преобразования и дополнения. В основе этих преобразований и дополнений лежит рабочий лист, который я назвал & Quot; Правила & Quot; который имеет три столбца:

RuleType       A code such as "OrgCat" 
In-keyword     A string, such as "Starbucks", to be found in a text column
Out-keyword    A string, such as "Coffee", to be returned if the In-keyword
               is found

Другие типы правил, которые я использую:

"OrgOrg"   Convert an organisation name used in the source statement to my
           preferred name for the organisation.
"CatPer"   Return a code identifying the apportioning rule for a category. For
           example, "Utility" returns "B3" (Back 3) because my utility bills
           are issued for three months in arrears.

В вашем вопросе у вас есть "версия Scenerio" вашей учетной записи и & quot; нужна версия & quot; вашего аккаунта. Я предполагаю, что вы вручную создали «нужную версию» вашей учетной записи, чтобы вы могли видеть, как это выглядит. Я предоставил макрос CopyFromAcctToRule (), который обрабатывает «нужную версию». вашей учетной записи, проверка и извлечение правил типа & quot; OrgCat & quot ;. Если он не находит ошибок, он выводит извлеченные правила на лист & quot; Правило & quot; и преобразует «нужную версию» на "версию scenerio". Если вы не создали «нужную версию», я подозреваю, что самый простой подход - создать частичную «нужную версию». как это:

| A        | B             | C       | D           | E         |
================================================================
| Date     | Description   | Amount  | Category    | Rule      |
| 04/08/12 | Starbucks NYC |  -$5.42 | Coffee      | Starbucks |
| 04/09/12 | Wal-Mart 468  | -$54.32 | Supermarket | Wal-Mart  |
| 04/10/12 | Starbucks SF  |  -$3.68 |             |           |
| 04/11/12 | Wal-Mart 512  |-$123.45 |             |           |

То есть найдите первый Starbucks и заполните его категорию и правило; найдите первый Wal-Mart и заполните его категорию и правило; и так далее. Запустите CopyFromAcctToRule (), и он отобразит сообщения об ошибках в столбце & quot; G & quot; за несоответствия и организации, которые вы пропустили. Для одноразового заполнения заполните категорию, но оставьте правило пустым. Повторите, исправляя ошибки и запуская CopyFromAcctToRule (), пока он не найдет никаких ошибок и не создаст рабочий лист & quot; Правило & quot ;. Примечание: отсутствующие категории не будут добавлены на этом этапе; это происходит ниже.

Я предоставил макрос FillDerivedCol () с демонстрацией того, как я его использую, заполнив столбец «Категория» версии «scenerio». учетная запись. Если вы не хотите создавать частичную «нужную версию», FillDerivedCol () предлагает альтернативный подход. Если он не может найти категорию для описания, он копирует описание в конец рабочего листа & quot; Rule & quot ;. Например, предположим, что вы неправильно определили правило против Starbucks, & quot; Правило & quot; будут изменены:

| A        | B             | C            |
===========================================
| Type     | In keyword    | Out keyword  |
| OrgCat   | Sarbucks      | Coffee       |
| OrgCat   | Wal-Mart      | Supermarket  |
| OrgCat   | Starbucks NYC |              |
| OrgCat   | Starbucks SF  |              |              

То есть будет одна новая строка для каждой ветви Starbucks. Здесь самый простой подход - исправить строку Sarbucks и удалить новые строки. Однако, если это была новая организация, вы можете отредактировать ключевое слово In, чтобы удалить информацию о ветви, и ввести категорию в столбце Out-keyword. Предупреждение: я превысил 30 000 символов для ответа. Мне пришлось редактировать эти процедуры, чтобы удалить диагностический код. Я надеюсь, что не сделал никаких ошибок при этом.

Я надеюсь, что это полезно. Удачи.

Global

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

Option Explicit
  ' I use constant for objects such as column numbers which are fixed
  ' for long periods but which might change. Any code using a column
  ' that has moved can be updated by changing the constant.
  Public Const ColRuleType As Long = 1
  Public Const ColRuleKeywordIn As Long = 2
  Public Const ColRuleKeywordOut As Long = 3
  Public Const ColRuleLast As Long = 3
  Public Const RowRuleDataFirst As Long = 2

  ' Rules are accumulated in this array by CopyFromAcctToRule
  ' Rules are loaded to this array by UpdateNewTransactions
  ' See GetRuleDetails() for a description of this array.
  Public RuleData() As Variant
Public Sub GetRuleDetails(ByVal RuleType As String, ByVal SrcText As String, _
                          ByRef KeywordIn As String, ByRef KeywordOut As String, _
                          Optional ByRef RowRuleSrc As Long)

  ' This routine performs a case-insensive search of a list of in-keywords for
  ' one that is present in SrcText.  If one is found, it returns the in-keyword
  ' and the matching out-keyword.

  ' This routine uses the previously prepared array RuleData.  Since RuleData
  ' is to be loaded to, or has been loaded from, a worksheet, the first
  ' dimension is for the rows and the second dimension is for the columns.

  ' RuleData has three columns:
  '  * RuleType: a code identifying a type of rule.  Only rows in RuleData for
  '    which this column matches the parameter RuleType will be considered.
  '  * KeywordIn: a string.  The first row in RuleData where the value of this
  '    column is contained within parameter SrcText is the selected Rule.
  '  * KeywordOut: a string.

  ' Input parameters
  '  * RuleType: Foe example, the rule type "OrgCat" will return a
  '    category for an organisation.
  '  * SrcText: The text field to be searched for the in keyword.

  ' Output parameters
  '  * KeywordIn: The value from the KeywordIn column of RuleData for the first
  '    row of RuleData of the required RuleType for which the KeywordIn value can
  '    be found in Desc.  The value in SrcText may be of any case although it is
  '    likely to be capitalised.  This value is the preferred display value.
  '  * KeywordOut: The value from the KeywordOut column of RuleData of the
  '    selected row.  For this routine, KeywordOut is a string with no
  '    significance.  It is the calling routine that understands the rule type.
  '  * RowRuleSrc: Only used during build of RuleData so the caller can access
  '    non-standard data held in RuleData during build.

  Dim LCSrcText As String
  Dim RowRuleCrnt As Long

  LCSrcText = LCase(SrcText)
  For RowRuleCrnt = RowRuleDataFirst To UBound(RuleData, 1)
    If RuleData(RowRuleCrnt, ColRuleKeywordIn) = "" Then
      ' Empty row.  This indicated end of table during build
      KeywordIn = ""
      KeywordOut = ""
      Exit Sub
    End If
    If RuleType = RuleData(RowRuleCrnt, ColRuleType) Then
      ' This row is for the required type of rule
      If InStr(1, LCSrcText, _
                  LCase(RuleData(RowRuleCrnt, ColRuleKeywordIn))) <> 0 Then
        ' Have found first rule with KeywordIn contained within SrcText
        KeywordIn = RuleData(RowRuleCrnt, ColRuleKeywordIn)
        KeywordOut = RuleData(RowRuleCrnt, ColRuleKeywordOut)
        If Not IsEmpty(RowRuleSrc) Then
          RowRuleSrc = RowRuleCrnt
        End If
        Exit Sub
      End If
    End If
  Next
  ' No rule found
  KeywordIn = ""
  KeywordOut = ""

End Sub

Extract rules and convert account from Need to Scenerio style

См. Введение для деталей того, как я использовал бы эту рутину. После того, как вы создали рабочий лист & quot; Правила & quot; для существующих транзакций этот код, вероятно, больше не будет иметь значения. Я бы поместил его в свой собственный модуль, чтобы он мог быть заархивирован и удален после использования. В этом коде предполагается, что рабочие листы "Правило" и "Matt" s Acct "; находятся в той же книге. Я предлагаю вам сделать копию своей учетной записи, создать лист & quot; Правило & quot; а затем запустите CallCopyFromAcctRule () для учетной записи копирования и оцените результат. Предупреждение: вы используете & quot; правило & quot; где я использую & quot; в ключевом слове ;; Я пытался быть последовательным в своих комментариях и сообщениях об ошибках, но не могу гарантировать, что у меня есть.

Option Explicit
Sub CallCopyFromAcctRule()

  ' This routine exists simply to make it easy to change the names of the
  ' worksheets accessed by CallCopyFromAcctRule.

  Call CopyFromAcctToRule("Rule", "Matt's Acct")

End Sub
Sub CopyFromAcctToRule(ByVal Rule As String, ByVal Acct As String)

  ' * This routine builds the worksheet Rule from worksheet Acct.
  ' * It works down worksheet Acct extracting rules from rows where
  '   there is both a Rule and a Category.  Note: this routine does not
  '   distinguish between Manual and Automatic Categories although, if both are
  '   present, they must be the same.
  ' * The routine checks for a variety of error and possible error conditions.
  '   Error and warning messages are placed in columns defined by ColAcctError
  '   and ColAcctWarn.
  ' * If any errors are found, the routine does not change either worksheet
  '   Acct, apart from adding error messages, or worksheet Rule.
  ' * If no errors are found, worksheet Rule is cleared and the contents of
  '   RuleData written to it.
  ' * If no errors are found, any warning added to worksheet Acct are discarded
  '   and the following additional changes made:
  '    * The values in the Automatic category column are merged into the Manual
  '      category column which is relabelled "Category".
  '    * The Rule and Automatic category columns are cleared.

  Dim ColAcctCatAuto As Long
  Dim ColAcctCatMan As Long
  Dim ColAcctCrnt As Long
  Dim ColAcctDesc As Long
  Dim ColAcctError As Long
  Dim ColAcctRule As Long
  Dim ColAcctWarn As Long
  Dim ColRuleRowSrc As Long
  Dim DescCrnt As String
  Dim ErrorFoundAll As Boolean
  Dim ErrorFoundCrnt As Boolean
  Dim KeywordInCrnt As String
  Dim KeywordInRetn As String
  Dim KeywordOutCrnt As String
  Dim KeywordOutRetn As String
  Dim RowAcctCrnt As Long
  Dim RowAcctDataFirst As Long
  Dim RowAcctLast As Long
  Dim RowRuleCrntMax As Long
  Dim RowRuleSrc As Long

  ' These column values must be changed if the true value do not match those
  ' in the example in the question.
  ColAcctDesc = 2
  ColAcctCatMan = 4
  ColAcctRule = 5
  ColAcctCatAuto = 6
  ColAcctError = 8
  ColAcctWarn = 9
  ColRuleRowSrc = ColRuleLast + 1
  RowAcctDataFirst = 2

  With Worksheets(Acct)
    RowAcctLast = .Cells.SpecialCells(xlCellTypeLastCell).Row

    ' Size the array for the output data ready to be loaded to worksheet
    ' Rule with rows as the first dimension.  Allow for the maximum number of
    ' rows because an array cannot be resized to change the number of
    ' elements in the first dimension.  Allow an extra column for use during
    ' the build process.
    ReDim RuleData(1 To RowAcctLast, 1 To ColRuleRowSrc)
    RuleData(1, ColRuleType) = "Type"
    RuleData(1, ColRuleKeywordIn) = "In keyword"
    RuleData(1, ColRuleKeywordOut) = "Out keyword"
    RowRuleCrntMax = 1      ' Last currently used row

    With .Cells(1, ColAcctError)
      .Value = "Error"
      .Font.Bold = True
    End With
    With .Cells(1, ColAcctWarn)
      .Value = "Warning"
      .Font.Bold = True
    End With

    ErrorFoundAll = False
    For RowAcctCrnt = RowAcctDataFirst To RowAcctLast
      .Cells(RowAcctCrnt, ColAcctError).Value = ""  ' Clear any error or warning
      .Cells(RowAcctCrnt, ColAcctWarn).Value = ""   ' from previous run
      ErrorFoundCrnt = False
      ' Determine Category, if any
      If .Cells(RowAcctCrnt, ColAcctCatMan).Value = "" Then
        ' There is no manual category.
        If .Cells(RowAcctCrnt, ColAcctCatAuto).Value <> "" Then
          KeywordOutCrnt = .Cells(RowAcctCrnt, ColAcctCatAuto).Value
        Else
          ' Neither manual nor automatic category
          KeywordOutCrnt = ""
        End If
      Else
        ' There is a manual category.  Is it consistent with automatic category?
        KeywordOutCrnt = .Cells(RowAcctCrnt, ColAcctCatMan).Value
        If .Cells(RowAcctCrnt, ColAcctCatAuto).Value <> "" Then
          ' Automatic category exists.  It must be the same
          ' as the manual category to be valid.
          If LCase(KeywordOutCrnt) <> _
                             LCase(.Cells(RowAcctCrnt, ColAcctCatAuto).Value) Then
            ErrorFoundCrnt = True
            .Cells(RowAcctCrnt, ColAcctError).Value = _
                                       "Manual and automatic categories different"
          End If
        End If
      End If
      If Not ErrorFoundCrnt Then
        ' Match Rule, if any, against Category, if any
        KeywordInCrnt = .Cells(RowAcctCrnt, ColAcctRule).Value
        If KeywordInCrnt <> "" Then
          ' This row has keyword
          If KeywordOutCrnt = "" Then
            ' Rule but no Category
            DescCrnt = .Cells(RowAcctCrnt, ColAcctDesc).Value
            Call GetRuleDetails("OrgCat", DescCrnt, KeywordInRetn, KeywordOutRetn)
            If KeywordInRetn <> "" Then
              ' Rule found that would generate a category for this Keyword.
              ' No warning necessary
            Else
              ' No rule found that would generate a category for this keyword
              ErrorFoundCrnt = True
              .Cells(RowAcctCrnt, ColAcctError).Value = _
                            "There is no existing rule that would " & _
                            "generate a Category from this Rule"
            End If
          Else
            ' Both Rule and Category found
            ' Is match already recorded?
            DescCrnt = .Cells(RowAcctCrnt, ColAcctDesc).Value
            Call GetRuleDetails("OrgCat", DescCrnt, KeywordInRetn, _
                                                   KeywordOutRetn, RowRuleSrc)
            If KeywordInRetn <> "" Then
              If KeywordInCrnt <> KeywordInRetn Then
                ' A different rule would be applied to this Description
                If InStr(1, LCase(DescCrnt), LCase(KeywordInCrnt)) = 0 Then
                  ' The current Rule is not within the Description
                  ErrorFoundCrnt = True
                  .Cells(RowAcctCrnt, ColAcctError).Value = _
                      "The Rule in column " & Chr(64 + ColAcctRule) & _
                      " is not within the Description.  The Rule " & _
                      "from row " & RowRuleSrc & " would generate " & _
                      "the required Category '" & KeywordOutRetn & _
                      "' from this Description"
                Else
                  ' The current Rule is within the Description
                  If LCase(KeywordOutRetn) = LCase(KeywordOutCrnt) Then
                    ' It would generate the same category
                    ErrorFoundCrnt = True
                    .Cells(RowAcctCrnt, ColAcctError).Value = _
                      "The Rule in column " & Chr(64 + ColAcctRule) & _
                      " is within the Description but the Rule from " & _
                      "row " & RowRuleSrc & " would be selected to " & _
                      "generate the required Category '" & _
                      KeywordOutRetn & "' from this Description"
                  Else
                    ' It would generate a different category
                    ErrorFoundCrnt = True
                    .Cells(RowAcctCrnt, ColAcctError).Value = _
                      "The Rule in column " & Chr(64 + ColAcctRule) & _
                      " is within the Description but the Rule from " & _
                      "row " & RowRuleSrc & " would be selected to " & _
                      "generate Category '" & KeywordOutRetn & _
                      "', not Category '" & KeywordOutCrnt & _
                      "', from this " & "Description"
                  End If
                End If
              Else
                ' Rule already recorded
                If LCase(KeywordOutRetn) = LCase(KeywordOutCrnt) Then
                  ' Rule already recorded for this category. No action required.
                Else
                  ' Rule already recorded but not for this category
                  ErrorFoundCrnt = True
                  .Cells(RowAcctCrnt, ColAcctError).Value = _
                                "The rule from row " & RowRuleSrc & _
                                " would generate category """ & _
                                KeywordOutRetn & """ for this Rule"
                End If
              End If
            Else
              ' New rule
              RowRuleCrntMax = RowRuleCrntMax + 1
              RuleData(RowRuleCrntMax, ColRuleType) = "OrgCat"
              RuleData(RowRuleCrntMax, ColRuleKeywordOut) = KeywordOutCrnt
              RuleData(RowRuleCrntMax, ColRuleKeywordIn) = KeywordInCrnt
              RuleData(RowRuleCrntMax, ColRuleRowSrc) = RowAcctCrnt
            End If
          End If  ' If CatCrnt = ""
        Else
          ' No keyword
          If KeywordOutCrnt = "" Then
            ' No Keyword and no Category
            DescCrnt = .Cells(RowAcctCrnt, ColAcctDesc).Value
            If DescCrnt = "" Then
              ' Probably a blank line.  Ignore
            Else
              ' Would an existing rule generate a Category for Description
              Call GetRuleDetails("OrgCat", DescCrnt, KeywordInRetn, KeywordOutRetn)
              If KeywordInRetn = "" Then
                ' No rule found that would generate a category
                ' for this description
                .Cells(RowAcctCrnt, ColAcctError).Value = _
                          "There is no rule that would generate " & _
                          "a Category from this Description"
              Else
                ' Rule found that would generate a category for
                ' this description.
              End If
            End If
          Else
            ' No Keyword but have Category
            ' Check for a rule that would give current category
            ' from current description
            DescCrnt = .Cells(RowAcctCrnt, ColAcctDesc).Value
            Call GetRuleDetails("OrgCat", DescCrnt, KeywordInRetn, _
                                                   KeywordOutRetn, RowRuleSrc)
            If KeywordInRetn <> "" Then
              ' Have found a rule for the description
              If LCase(KeywordOutRetn) = LCase(KeywordOutCrnt) Then
                ' Rule generates current category
              Else
                ' Rule does not generate current category
                ErrorFoundCrnt = True
                .Cells(RowAcctCrnt, ColAcctError).Value = _
                     "The rule from row " & RuleData(RowRuleSrc, ColRuleRowSrc) & _
                     " would generate Category '" & KeywordOutRetn & _
                     "' from this Description"
              End If
            Else
              ' There is no rule for this Description.  This is not necessarily
              ' an error.  The category may have to be set manually.
              .Cells(RowAcctCrnt, ColAcctWarn).Value = _
                            "There is no rule that would generate " & _
                            "this Category from this Description"
            End If
          End If    ' If KeywordOutCrnt = ""
        End If      ' KeywordInCrnt <> ""
      End If  ' If Not ErrorFoundCrnt
      If ErrorFoundCrnt Then
        ErrorFoundAll = True
      End If
    Next
  End With

  If ErrorFoundAll Then
     Exit Sub
  End If

  ' No errors found

  ' Clear existing contents from worksheet Rule and load with RuleData
  With Worksheets(Rule)
    .Cells.EntireRow.Delete
    .Range(.Cells(1, 1), .Cells(RowRuleCrntMax, _
                                        ColRuleKeywordOut)).Value = RuleData
    .Range("A1:C1").Font.Bold = True
    .Columns.AutoFit
  End With

  With Worksheets(Acct)
    ' Merge values from automatic category column into manual category column
    For RowAcctCrnt = 2 To RowAcctLast
      If .Cells(RowAcctCrnt, ColAcctCatMan).Value = "" Then
        ' There is no manual category so set to automatic category.
        .Cells(RowAcctCrnt, ColAcctCatMan).Value = _
                                    .Cells(RowAcctCrnt, ColAcctCatAuto).Value
      End If
    Next
    ' Clear automatic category
    .Columns(ColAcctCatAuto).ClearContents
    ' Change column heading
    With .Cells(1, ColAcctCatMan)
      .Value = "Category"
      .Font.Bold = True
    End With
    ' Clear Error and Warning columns
    .Columns(ColAcctError).ClearContents   ' Only heading to clear
    .Columns(ColAcctWarn).ClearContents
    ' Clear Rule column
    .Columns(ColAcctRule).ClearContents
  End With

End Sub

Completing the Category column of your scenerio version account

Это демонстрирует, как я заполняю столбец Категория для новых транзакций.

Option Explicit
Sub CallFillDerivedCol()

  ' I use FillDerivedCol() on worksheets loaded with transactions for different
  ' accounts.  They are in different workbooks, different worksheets and have
  ' different columns.  This routine exists to call FillDerivedCol() for my
  ' test version of your account

  Call FillDerivedCol(ActiveWorkbook, "Rule", _
                      ActiveWorkbook, "Matt's Acct", "OrgCat", 2, 4)

  ' For this example, I had the rules and the account in same workbook.  To
  ' have them in different workbooks, as I normally do, you will need something
  ' like:

  '  Dim PathCrnt As String
  '  Dim WBookOrig As Workbook
  '  Dim WBookOther As Workbook

  '  Set WBookOrig = ActiveWorkbook
  '  PathCrnt = ActiveWorkbook.Path & "\"
  '  Set WBookOther = Workbooks.Open(PathCrnt & "xxxxxxx")

  '  Call FillDerivedCol(WBookOrig, "Rule", _
  '                      WBookOther, "Matt's Acct", "OrgCat", 2, 4)

  '  WBookOther.Close SaveChanges:=True

End Sub
Sub FillDerivedCol(ByVal WBookRule As Workbook, ByVal WSheetRule As String, _
                   ByVal WBookTrans As Workbook, ByVal WSheetTrans As String, _
                   ByVal RuleType As String, _
                   ByVal ColSrc As Long, ByVal ColDest As Long)

  ' Fill any gaps in WBookTrans.Worksheets(WSheetTrans).Columns(ColDest) based on
  ' rules in worksheet WBookRule.Worksheets(WSheetRule).

  ' WBook.Worksheets(WSheetTrans).Columns(ColSrc) is a text field which
  ' contains in-keywords.  Rules of type RuleType convert in-keywords to
  ' out-keywords which are the values required for .Columns(ColDest).

  Dim CellEmptyDest As Range
  Dim KeywordIn As String
  Dim KeywordOut As String
  Dim MissingRule() As Variant
  Dim RowAcctCrnt As Long
  Dim RowAcctPrev As Long
  Dim RowMissingCrntMax As Long
  Dim RowRuleLast As Long

  ' Load array RuleData from worksheet Rule
  With WBookRule.Worksheets(WSheetRule)
    RowRuleLast = .Cells(Rows.Count, 1).End(xlUp).Row
    RuleData = .Range(,.Cells(1, 1), .Cells(RowRuleLast, ColRuleLast)).Value
  End With

  ' * Prepare MissingRule() in case any calls to GetRuleDetails() fails to
  '   find a known in-keyword in WBook.Worksheets(WSheetName).Columns(ColDest).
  ' * The number of occurrences of the first dimension cannot be changed. 500
  '   is intended to be more occurrences than could possible be needed. If
  '   more than 500 missing rules are found, only the first 500 will be added
  '   to worksheet "Rule"  This routine c,an be immediately run again to add
  '   another 500 missing rules.
  ReDim MissingRule(1 To 500, 1 To ColRuleLast)
  RowMissingCrntMax = 0

  With WBookTrans
    With .Worksheets(WSheetTrans)
      RowAcctPrev = 1
      ' Find the next empty cell in column ColDest for a transaction row
      Set CellEmptyDest = .Columns(ColDest).Find(What:="", _
                      After:=.Cells(RowAcctPrev, ColDest), LookIn:=xlFormulas, _
                      LookAt:=xlWhole, SearchOrder:=xlByRows, _
                      SearchDirection:=xlNext, MatchCase:=False, _
                      SearchFormat:=False)
      Do While True
        If CellEmptyDest Is Nothing Then
          ' No empty cell found in column.  This is not a realistic situation
          ' because it would require every row in the worksheet to have a value.
          Exit Do
        End If
        RowAcctCrnt = CellEmptyDest.Row
        If RowAcctCrnt < RowAcctPrev Then
          ' Have looped back to the top.  This is not a realistic situation
          ' because it would require every row in the worksheet to have a value.
          Exit Do
        End If
        If .Cells(RowAcctCrnt, ColSrc).Value = "" Then
          ' This row has no value in either the source or the destination
          ' columns.  Assume all transactions finished
          Exit Do
        End If
        Call GetRuleDetails(RuleType, .Cells(RowAcctCrnt, ColSrc).Value, _
                                                          KeywordIn, KeywordOut)
        If KeywordIn = "" Then
          ' No in-keyword found within source column.  Add source column value
          ' to MissingData for user to edit.
          If RowMissingCrntMax >= UBound(MissingRule, 1) Then
            ' All available rows in MissingRule already used
          Else
            RowMissingCrntMax = RowMissingCrntMax + 1
            MissingRule(RowMissingCrntMax, ColRuleType) = RuleType
            MissingRule(RowMissingCrntMax, ColRuleKeywordIn) = _
                                              .Cells(RowAcctCrnt, ColSrc).Value
          End If
        Else
          .Cells(RowAcctCrnt, ColDest).Value = KeywordOut
        End If
        RowAcctPrev = RowAcctCrnt
        Set CellEmptyDest = .Columns(ColDest).FindNext(CellEmptyDest)
      Loop
    End With
  End With

  If RowMissingCrntMax > 0 Then
    ' Transactions found for which no rule exists.  Add to worksheet "Rule"
    ' for attention by the user.
    With WBookRule.Worksheets(WSheetRule)
      RowRuleLast = .Cells(Rows.Count, 1).End(xlUp).Row
      .Range(.Cells(RowRuleLast + 1, 1), _
             .Cells(RowRuleLast + RowMissingCrntMax, ColRuleLast)).Value _
                                                                 = MissingRule
    End With
  End If

End Sub
@assylias. Я не думал об использовании перечислений таким образом. Спасибо за предложение.
Я мог бы использовать это один день;) Только один комментарий: я предпочитаю использовать enum против констант для столбцов, поскольку они предоставляют 3 вещи: пространство имен, автоинкремент, простоту вставки нового столбца без необходимости что-либо менять.
0

ю VBA, потому что я считаю, что описания противоречивы и требуют различных методов для их классификации.

Подход, который я использую, состоит в том, чтобы иметь рабочий лист, который я назвал Rule, содержащий:

Organisation     Category
Starbucks NYC    Coffee shop
Starbucks SF     Coffee shop
Wal-Mart 468     Supermarket

Обратите внимание, у меня есть строки на ветку. Это боль, если вы много путешествуете, но без последовательности у вас мало выбора.

В столбце D заявления я набираю=VLOOKUP(B2,Rule!A:B,2,FALSE) а затем скопируйте это вниз.

Каждый месяц новые организации классифицируются как "# N / A". Я либо набираю одноразовую классификацию, либо добавляю организацию к правилу листа.

Вы имеете в виду, что вы хотите "Starbucks" где-нибудь в описании приводить к категории "кафе"? Это то, что делает моя система, но я не знаю, как это сделать с формулами. Я, вероятно, могу предложить вам решение VBA. Возможно, кто-то еще может предложить вам более сложное решение на основе формул.
Спасибо за Ваш ответ. Я действительно ищу то, что автоматически создает и применяет правила, основанные на части ячейки. Matt
1

тся действительно мощными, но гораздо сложнее, чем я хотел.

Моя идея проста: разработать набор правил категоризации на основе ключевых слов. Если в описании найдено ключевое слово, правило применяется и категория устанавливается. Не довольный идеей использования VBA или PowerShell, продолжал осматриваться и нашел следующий пост:

как к группе-Excel-элементы на основе-на-пользовательских-правил Джон Бустос (пожалуйста, кредитуйте его)

Решение Джона использует очень простой подход:

Rules are defined in two columns (Keyword - Category) - if we assume that they are in columns F and G:

Column F     Column G
Keyword      Category
Starbucks    Coffee shop
Wal-Mart     Supermarket
Safeway      Supermarket
In-N-Out     Fast Food
Comcast      Internet Service
Verizon      Mobile Phone Service

Then add this ARRAY formula to the cell where you want to insert the category pointing to the cell that you want to check for the rule (let's assume is cell A2):

=IFERROR(INDEX(G$2:G$7,MATCH(TRUE,ISNUMBER(SEARCH(F$2:F$7,A2)),0)),"Other")

Remember to use CTRL+SHIFT+ENTER to make sure that it goes in as an array formula. If you have more rules, you will need to change the range height. Afterwards you can simply populate down the formula to all the rows that you need to categorize. Also, the categorization uses the first rule and sticks to that, so if you have two different keywords present in one of the target cells, the first keyword categorization rule will be applied. The rules have to be created manually, when a cell shows "Other" it means that there are no keywords found.

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

Похожие вопросы