[Feature] Some functions supports CubeValueObject

[Dushusir]

Initial checklist

• [X] Is this really a problem?
• [X] I have searched the Github Issues for similar issues, but did not find anything.

Problem

=SUM((D20:D21,E20:E21,F20:F21)) returns 0

Solution

calculate =SUM((D20:D21,E20:E21,F20:F21))

[Dushusir]

类似的还有三维引用功能，也是支持一个参数表示多个范围。

Excel 中的三维引用是一种特殊的引用方式，用于跨多个工作表引用相同单元格或单元格区域。让我来解释一下如何使用三维引用：

1. 在公式输入状态下，单击第一个工作表标签。
2. 按住 Shift 键，再单击最后一个工作表标签。
3. 选定要引用的单元格区域。
4. 完成公式后按 回车。

例如：

• =SUM(Sheet1:Sheet3!A1) 表示对 Sheet1、Sheet2 和 Sheet3 三个工作表的 A1 单元格求和。
• =SUM(Sheet1:Sheet3!A1:A7) 表示对这三个工作表的 A1:A7 单元格区域求和。

支持三维引用的函数包括：SUM、AVERAGE、AVERAGEA、COUNT、COUNTA、MAX、MIN、PRODUCT、STDEV、STDEVA、STDEVP、STDEVPA、VAR、VARA、VARP、VARPA。

请注意以下几点：

1. INDIRECT 函数不支持三维引用，因此不能使用类似 SUM(INDIRECT("Sheet1:Sheet3!A1:A7")) 的公式。
2. 引用的工作表必须是相连的，如果某个工作表不在其他工作表之间，它不会被引用。
3. 引用区域的大小和位置必须一致，即这几个工作表的引用区域必须相同。
4. 此种三维引用的函数返回单值，不能返回数组结果。
5. SUM 只支持类似 Sheet1:Sheet3!A1 这种三维引用，不支持函数产生的三维引用。

希望这对您有所帮助！📊👍

源: 与必应的对话， 2024/3/21 (1) Excel三维引用什么意思_百度知道. https://zhidao.baidu.com/question/512480254.html. (2) excel 三维引用的概念和使用，并归纳出支持excel三维引用的函数_office教程网. https://www.office26.com/excelhanshu/excel_function_6540.html. (3) 对 Excel 表格使用结构化引用 - Microsoft 支持. https://support.microsoft.com/zh-cn/office/%E5%AF%B9-excel-%E8%A1%A8%E6%A0%BC%E4%BD%BF%E7%94%A8%E7%BB%93%E6%9E%84%E5%8C%96%E5%BC%95%E7%94%A8-f5ed2452-2337-4f71-bed3-c8ae6d2b276e.

[univer-bot[bot]]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

Similarly, there is the three-dimensional reference function, which also supports one parameter to represent multiple ranges.

Three-dimensional reference in Excel is a special way of reference that is used to reference the same cell or range of cells across multiple worksheets. Let me explain how to use 3D references:

1. In Formula Input Status, click on the first worksheet tab.
2. Hold down the Shift key and click the last worksheet tab.
3. Select the range of cells to be referenced.
4. Press Enter after completing the formula.

For example:

• =SUM(Sheet1:Sheet3!A1) means summing the A1 cells of the three worksheets Sheet1, Sheet2 and Sheet3.
• =SUM(Sheet1:Sheet3!A1:A7) means summing the A1:A7 cell range of these three worksheets.

Functions that support three-dimensional reference include: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, VARPA **.

Please note the following:

1. The INDIRECT function does not support three-dimensional references, so formulas like SUM(INDIRECT("Sheet1:Sheet3!A1:A7")) cannot be used.
2. The referenced worksheets must be connected. If a worksheet is not between other worksheets, it will not be referenced.
3. The size and position of the reference area must be consistent, that is, the reference areas of these worksheets must be the same.
4. This kind of three-dimensional reference function returns a single value and cannot return an array result.
5. SUM only supports three-dimensional references like Sheet1:Sheet3!A1, and does not support three-dimensional references generated by functions.

Hope this helps! 📊👍

Source: Conversation with Bing, 2024/3/21 (1) What does Excel three-dimensional reference mean_Baidu knows. https://zhidao.baidu.com/question/512480254.html. (2) The concept and use of excel three-dimensional reference, and summarize the functions that support excel three-dimensional reference_office tutorial website. https://www.office26.com/excelhanshu/excel_function_6540.html. (3) Use structured references for Excel tables - Microsoft Support. https://support.microsoft.com/zh-cn/office/%E5%AF%B9-excel-%E8%A1%A8%E6%A0% BC%E4%BD%BF%E7%94%A8%E7%BB%93%E6%9E%84%E5%8C%96%E5%BC%95%E7%94%A8-f5ed2452-2337-4f71- bed3-c8ae6d2b276e.

[Dushusir]

总结出来支持以下几种写法

1. =SUM(A1:D1): A1:D1求和
2. =SUM(A1:B1:C1:D1): A1:D1求和
3. =SUM((A1:B1,C1:D1)): A1:D1求和
4. =SUM(Sheet1:Sheet2!A1:D1): Sheet1 A1:D1 和 Sheet2 的 A1:D1 总和
5. =SUM(Sheet1:Sheet1!A1:D1): 会被转成 =SUM(Sheet1!A1:D1) A1:D1求和

[univer-bot[bot]]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

In summary, the following writing methods are supported:

1. =SUM(A1:D1): sum of A1:D1
2. =SUM(A1:B1:C1:D1): sum of A1:D1
3. =SUM((A1:B1,C1:D1)): Sum of A1:D1
4. =SUM(Sheet1:Sheet2!A1:D1): The sum of Sheet1 A1:D1 and Sheet2’s A1:D1
5. =SUM(Sheet1:Sheet1!A1:D1): will be converted into =SUM(Sheet1!A1:D1) A1:D1 sum