如何以纯CSV格式保存CSE Excel公式? [英] How to save CSE Excel formula in plain CSV format?
问题描述
我想在我的程序中形成CSV excel文件。某些单元格包含CSE(Ctrl + Shift + Enter)公式(数组公式)。
这不起作用:
= SUM(IF(COUNTIF(B4:B1000; B4:B1000)= 0;; 1 / COUNTIF(B4:B1000; B4:B1000)))
这不起作用:
{= SUM(IF(COUNTIF(B4:B1000; B4:B1000)= 0;; 1 / COUNTIF(B4:B1000; B4:B1000))}
这个公式对单元格数组...进行不同的计算...
解决方案尝试将数组公式转换为普通公式:
= SUMPRODUCT((B4:B1000< ;")/ COUNTIF(B4:B1000; B4:B1000&))
<一个通用的技术是用MMULT(...; 1)
: b (COUNTIF(B4:B1000; B4:B1000); 1)= 0;; 1 / MMULT(COUNTIF ; 1)))
示例
保存CSV文件时,请务必按Ctrl +`[back-quote](它是单元格中的Excel选项>高级>显示公式的快捷方式)显示公式,而不是计算结果。
>
以上设置以CSV格式保存如下。
Sheet1;
;= SUMPRODUCT((B4:B1000)/ COUNTIF(B4:B1000; B4:B1000&))
; 1
; 2
;
; 3
; 1
; a
; a
您可以通过将文本文件的内容复制到新工作表中的单元格A1,然后选择数据>文本到列>分隔>分隔符:分号来检查。
我假设小数分隔符:,
千位分隔符:。
> Excel选项>高级)。
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...
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).
这篇关于如何以纯CSV格式保存CSE Excel公式?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!