How to save CSE Excel formula in plain CSV format?

问题:

I would like to form CSV excel file in my program. Some cells contains CSE (Ctrl+Shift+Enter) formulas (array formulas).

This does not work:

=SUM(IF(COUNTIF(B4:B1000;B4:B1000)=0; ""; 1/COUNTIF(B4:B1000;B4:B1000)))

This does not work too:

{=SUM(IF(COUNTIF(B4:B1000;B4:B1000)=0; ""; 1/COUNTIF(B4:B1000;B4:B1000)))}

This formula makes distinct count on cells array...

回答1:

Try converting your array formulas to normal formulas by using instead:

=SUMPRODUCT((B4:B1000<>"")/COUNTIF(B4:B1000;B4:B1000&""))

A general technique to do this is to wrap functions with array arguments in MMULT(...;1):

=SUM(IF(MMULT(COUNTIF(B4:B1000;B4:B1000);1)=0; ""; 1/MMULT(COUNTIF(B4:B1000;B4:B1000);1)))

Example

When saving the CSV file, make sure to display formulas by pressing Ctrl+` [back-quote] which is a shortcut for Excel Options > Advanced > Show Formulas in cells instead of their calculated result.

The setup above saves in CSV format as below.

Sheet1;
;"=SUMPRODUCT((B4:B1000<>"""")/COUNTIF(B4:B1000;B4:B1000&""""))"
;Values
;1
;2
;
;3
;1
;a
;a

You can check this by copying the contents of the text file to cell A1 in a new sheet and choosing Data > Text To Columns > Delimited > Delimiter: Semicolon.

I'm assuming Decimal Separator:, Thousands Separator:. (in Excel Options> Advanced).

回答2:

If you want to preserve the brackets themselves, then you can run the following macro, then save the file as CSV. You can do this in addition to the [Ctrl + `] method lori_m mentions.

Option Explicit


Sub AddBrackets()

Dim cell As Range

With Sheet1
    For Each cell In UsedRange
        If cell.HasArray Then
            cell.Value = "{" & cell.FormulaLocal & "}"
        End If
    Next
End With

End Sub