自动完成公式在插入表格行时。

huangapple
go评论67阅读模式
英文:

Autocomplete formulas when inserting table row

问题

File > Options > Proofing > AutoCorrect Options > 填写表格中的公式以创建计算列，确定在表格单元格中编写公式时，该公式是否也会在表格列的其余单元格中编写。如果选项已激活且列中的所有单元格都为空，则该公式会复制到其余单元格中。

但是，如果选项被停用或列中的任何单元格已包含数据，则该公式不会自动复制到列的其余单元格中，而会出现一个图标，允许我们选择是否复制该公式。

然而，无论是否选中该选项，即使列中不是所有单元格都为空，当您插入新行到表格中时，新行中的公式总是会自动填充。

我曾以为这是由于列范围的 .FormulaArray 值引起的。然而，这并不决定性，因为 .FormulaArray 存储了当整个范围具有相同公式时的公式值，但如果范围中的任何单元格具有不同的颜色或公式，则为 null。因此，.ListColumns(c).DataBodyRange.FormulaArray 并不确定在插入新行时是否会复制表字段的默认公式。

那么，我提出的问题是，每个表格（ListObject）列的默认公式值存储在哪里？而且，为什么在某些情况下，在插入新行时，某些列会采用默认公式，而其他列则不会采用？

因为我找不到可以解释这一现象的因果关系，因为有些列在它们的单元格中具有不同的公式和值，但在插入新行时仍然采用默认公式（最初在该列中引入的公式），而其他列则没有分配任何公式。

我知道这是一个非常技术性和正式的问题，但如果有人能够提供解释，我将不胜感激，因为我在工作中遇到了困难。谢谢大家。祝一切顺利。

我尝试过使用完全空白的列和部分填充单元格的列输入数据。这决定了 .FormulaArray 何时为 null 以及何时不为 null。但在插入行时，某些字段会自动填充公式，而其他字段则不会，我无法弄清楚其中的差异。

英文:

File > Options > Proofing > AutoCorrect Options > Fill Formulas in tables to create calculated columns, determines if when writing a formula in a cell of a table, this formula will also be written in the rest of the cells of that column of the table. If the option is activated and all the cells in the column are empty, the formula is copied to the rest of the cells.

But if the option is deactivated or any cell in the column already contains some data, then the formula is not automatically copied to the rest of the cells in the column, but an icon appears that allows us to choose whether the formula is copied or not.

However, whether the option is checked or not, and even if not all cells are blank in the column, when you INSERT A ROW into the table, the formulas in the new row are always filled automatically.

I had thought this was due to the value of .FormulaArray for the column range. However this is not determinative, because .FormulaArray stores the value of the formula when the entire range has the same formula, but is null if any cell in the range has a different color or formula. Therefore .ListColumns(c).DataBodyRange.FormulaArray does not determine whether the default formula of a table field will be copied when inserting a new row.

The question I am asking, then, is where is that value of the default formula associated with each column of a table (ListObject) stored? And also, why in some cases, when inserting a new row, some columns take the default formula and others don't?

Because I can't find a causal relationship that justifies it, since there are columns that have different formulas and values in their cells and still, when inserting a new row, they take a default formula (the formula that was first introduced in the column), while other columns are not assigned any formula.

I know it's a very technical and formal question, but I would appreciate if someone has the explanation, because i'm stuck at my job. Thank you all. All the best.

I have tried to enter data with the column completely empty and with some cells occupied. This determines when .FormulaArray is null and when it is not. But when inserting rows, some fields autofill formulas and others don't, and I can't figure out what the difference is.

答案1

得分: 1

看起来可能没有为此提供任何VBA属性，但在DocumentFormat.OpenXml.Spreadsheet对象模型中有一个TableColumn.CalculatedColumnFormula属性：https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.tablecolumn.calculatedcolumnformula?view=openxml-2.8.1

如果您解压带有预定义公式分配给列表列的Excel文件，您可以在那里看到它（例如，我从listobject1.xml中获取了这个）：

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="xr xr3" 
xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" id="3" 
xr:uid="{4BC10EFA-3170-4F60-865C-90CE4562C734}" name="Table3" displayName="Table3" ref="A1:G18" totalsRowShown="0"><autoFilter ref="A1:G18" 
xr:uid="{4BC10EFA-3170-4F60-865C-90CE4562C734}"/>
<tableColumns count="7">
   <tableColumn id="1" xr3:uid="{EBC090AD-7675-48CC-945A-DAB4FC16438B}" 
name="Col0001"/>
   <tableColumn id="2" xr3:uid="{D2C68174-33C4-4917-9394-D75590CB1510}" name="Col0002"/>
   <tableColumn id="3" xr3:uid="{9506BA3E-94FB-46F4-9CD7-C4B9B27257F9}" name="Col0003"/>
   <tableColumn id="4" xr3:uid="{17CAF190-27B3-47E7-A3C0-961A01DD85F4}" name="Col0004"/>
   <tableColumn id="5" xr3:uid="{256C70AC-6327-410C-AC87-DCD6815B4CDE}" name="Col0005"/>
   <tableColumn id="6" xr3:uid="{E70A6FB0-2F5A-4226-8173-85088B227273}" name="Col0006"/>
   <tableColumn id="7" xr3:uid="{F6346482-77CB-4CED-B717-FFB314B03774}" name="Col0007" dataDxfId="0">
       <calculatedColumnFormula>ROW()</calculatedColumnFormula>
   </tableColumn>
</tableColumns>
<tableStyleInfo name="TableStyleMedium2" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0"/>
</table>
英文:

Looks like there may not be any VBA property exposed for this, but there is a TableColumn.CalculatedColumnFormula property in the DocumentFormat.OpenXml.Spreadsheet object model: https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.tablecolumn.calculatedcolumnformula?view=openxml-2.8.1

If you unzip an Excel file with a listobject which has a pre-defined formula assigned to a list column, you can see it there (I got this from listobject1.xml for example):

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="xr xr3" 
xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" id="3" 
xr:uid="{4BC10EFA-3170-4F60-865C-90CE4562C734}" name="Table3" displayName="Table3" ref="A1:G18" totalsRowShown="0"><autoFilter ref="A1:G18" 
xr:uid="{4BC10EFA-3170-4F60-865C-90CE4562C734}"/>
<tableColumns count="7">
   <tableColumn id="1" xr3:uid="{EBC090AD-7675-48CC-945A-DAB4FC16438B}" 
name="Col0001"/>
   <tableColumn id="2" xr3:uid="{D2C68174-33C4-4917-9394-D75590CB1510}" name="Col0002"/>
   <tableColumn id="3" xr3:uid="{9506BA3E-94FB-46F4-9CD7-C4B9B27257F9}" name="Col0003"/>
   <tableColumn id="4" xr3:uid="{17CAF190-27B3-47E7-A3C0-961A01DD85F4}" name="Col0004"/>
   <tableColumn id="5" xr3:uid="{256C70AC-6327-410C-AC87-DCD6815B4CDE}" name="Col0005"/>
   <tableColumn id="6" xr3:uid="{E70A6FB0-2F5A-4226-8173-85088B227273}" name="Col0006"/>
   <tableColumn id="7" xr3:uid="{F6346482-77CB-4CED-B717-FFB314B03774}" name="Col0007" dataDxfId="0">
       <calculatedColumnFormula>ROW()</calculatedColumnFormula>
   </tableColumn>
</tableColumns>
<tableStyleInfo name="TableStyleMedium2" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0"/>
</table>

huangapple
  • 本文由 发表于 2023年8月5日 06:27:53
  • 转载请务必保留本文链接：https://go.coder-hub.com/76839403.html
  • excel
  • listobject
  • vba
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定