Код формулы Excel / VBA, который будет суммировать только ячейки, содержащие только значения (пропустить любые ячейки, содержащие формулы)?

Есть ли формула, которая суммирует ТОЛЬКО ячейки, содержащие значение (а не ячейки с формулой)? Например, скажем, в столбце A электронной таблицы у меня есть смесь введенных значений и формул, которые возвращают значения. Если я использую формулу суммы в конце, она естественным образом суммирует все числа в выбранном массиве независимо от того, являются ли они введенными значениями или значениями, полученными из формулы. (Может быть, какая-то комбинация кода СУММЕСЛИ и VBA ..) В случае, если мое описание не было ясным, вот гипотетическая настройка электронной таблицы, в которой мне понадобится эта формула:

      A
1|  400
2|  =SUM(B1:B3)
3|  =AVERAGE(B1:B3)
4|  200
5|  100
6|  =COUNT(B1:B3)
7|  I want the sum Formula in this cell (A7) to return the value 700 (the sum of the values above).

excel excel-formula sum vba conditional
person Jack    schedule 21.12.2010    source источник

Ответы (4)


arrow_upward
1
arrow_downward

Чтобы прояснить ответ Мартина.

Невозможно узнать, содержит ли ячейка формулу, используя формулы Excel.

Вы должны определить UDF (определяемую пользователем функцию). Учебное пособие здесь.. Из учебника:

  1. Откройте новую книгу.
  2. Войдите в VBA (нажмите Alt + F11)
  3. Вставьте новый модуль (Вставить> Модуль)
  4. Скопируйте и вставьте примеры пользовательских функций Excel
  5. Выйти из VBA (нажмите Alt + Q)
  6. Используйте функции (они появятся в диалоговом окне «Вставить функцию», Shift + F3, в категории «Определено пользователем»)

Ваш UDF будет выглядеть примерно так:

Public Function isformula(rng As Range) As Variant()
    Dim aryIn() As Variant
    Dim a As Variant
    Dim i As Integer, j As Integer
    Dim temp() As Variant

    aryIn = rng.Value
    ReDim temp(LBound(aryIn) To UBound(aryIn), _
               LBound(aryIn, 2) To UBound(aryIn, 2))
    For i = LBound(aryIn) To UBound(aryIn)
        For j = LBound(aryIn, 2) To UBound(aryIn, 2)
            If (Left(rng(i, j).Formula, 1) = "=") Then
               temp(i, j) = True
            Else
               temp(i, j) = False
            End If
        Next j
    Next i
    isformula = temp()
End Function

Затем вы можете использовать его в своем коде. Что-то вроде:

{=SUM(IF(NOT(isformula(A1:A6)),A1:A6,0))}

Фигурные скобки {} обозначают формулу ARRAY (вводится с помощью Ctrl-Shift-Enter)

HTH!

person Dr. belisarius    schedule 21.12.2010
comment
Спасибо. В случае, если кто-то еще использует это, убедитесь, что для формулы ARRAY вы нажимаете Ctrl-Shift-Enter при создании формулы (а не только Shift-Enter) - person Jack; 22.12.2010
comment
Вы также можете использовать XLM с именами диапазонов для этого, поэтому решение не ограничивается только UDF. - person brettdj; 30.12.2011

arrow_upward
3
arrow_downward

Если вы используете ПРОМЕЖУТОЧНЫЙ ИТОГ для всех своих функций, вы можете это сделать. ПРОМЕЖУТОЧНЫЙ ИТОГ игнорирует любые другие функции ПРОМЕЖУТОЧНЫЙ ИТОГ в диапазоне. В A2

=SUBTOTAL(9,B1:B3)

In A3

=SUBTOTAL(1,B1:B3)

In A6

=SUBTOTAL(2,B1:B3)

In A7

=SUBTOTAL(9,A1:A6)

A7 будет 700 (что, я полагаю, вы имели в виду). Если у вас есть формулы, которых нет в ПРОМЕЖУТОЧНОМ ИТОГЕ, это не сработает.

person Dick Kusleika    schedule 22.12.2010
comment
Верно, 400 + 100 + 200 это 700. Я не могу сложить. :) - person Jack; 22.12.2010

arrow_upward
0
arrow_downward

Существует свойство HasFormula, которым вы можете быть возможность комбинировать с СУММЕСЛИ, чтобы делать то, что вы хотите.

person Martin Broadhurst    schedule 21.12.2010
comment
Вам нужно создать UDF, а затем вы можете использовать его, как предлагает Мартин - person Dr. belisarius; 21.12.2010

arrow_upward
0
arrow_downward

Это сработает, хотя почему-то кажется неряшливым, и наверняка должен быть лучший способ. Немного поработав, вы можете превратить это в UDF.

Sub SumNumbersOnly()
    Dim sumAllCells As Long
    Dim sumFormulaCells As Long
    Dim sumNumberCells As Long

    sumAllCells = Application.Sum(Selection)
    sumFormulaCells = Application.Sum(Selection.Cells.SpecialCells(xlCellTypeFormulas))

    sumNumberCells = sumAllCells - sumFormulaCells

    Debug.Print sumNumberCells //Returns 700 (400 + 200 + 100 as in your example)

End Sub
person Alex P    schedule 21.12.2010