excel数组公式：不必'ctrl-shift-enter'？ [英] excel array formula: not have to 'ctrl-shift-enter'?

问题描述

按下f2来编辑数组公式，例如{= MATCH（TRUE，（B2：B100）> 0,0）}，大括号当然消失了。编辑后，您必须按ctrl + shift + enter才能使大括号重现。

After you press f2 to edit an array formula e.g. {=MATCH(TRUE,(B2:B100)>0,0)}, the curly brackets disappear of course. After editing, you have to press ctrl + shift + enter to make the curly brackets reappear.

有没有一些设置让{}即使你按正常的'输入'？

Is there some setting to keep the {} even if you press a normal ‘enter’?

在复制上面的单元格（即包含数组公式）之后，您无法将其粘贴到10个其他单元格上。您只能一次将其粘贴到一个新的单元格上。有没有办法一次粘贴到多个单元格？

Also after copying the cell above (i.e. containing the array formula) you cannot paste it onto say 10 x other cells. You can only paste it onto one new cell at a time. Is there a way to paste onto more than one cell at once?

谢谢

推荐答案

数组公式应该复制并粘贴，并保留它的数组，只要您复制单元格，而不是复制公式栏中的公式。如果您向下或向下拖动或填充单元格，它也会复制。可能如果你想保持B2：B100的范围，然后使用$ B $ 2：$ B $ 100来阻止它增加。

The array formula should copy and paste fine and retain it's array as long as you copy the cell rather than copy the formula in the formula bar. It also copies fine if you drag/fill the cell down or across. It may be if you want to keep the range B2:B100 when dragging then use $B$2:$B$100 to stop it incrementing.

F4是一个很好的捷径这可以帮助这里。将光标放在B2：B100上，然后按F4。

F4 is a nice short cut that can help here. Position your cursor over B2:B100 and press F4.

如果要强制某些单元格始终保留公式数组我可以想到一个脏的解决方法，但我认为它可能有比正面更负面。

If you want to enforce certain cells always retain formula array I can think of a dirty workaround but I think it may have more negatives than positives.

否定：

- 涉及VBA

- 将清除撤消堆栈/剪贴板

-It will clear the Undo stack/clipboard

- 这可能更令人困惑

-It's potentially more confusing

解决方法

标签公式必须是一个数组公式，其中的术语对结果没有影响。例如（0 * 0）+ 。这个术语可以被检查，并且通过代码始终是一个数组公式。

Tag formulae that must be an array formula with a term that has no effect on the outcome. Eg (0*0)+. This term can then be checked and made to always be an array formula through code.

所以你上面的公式变为 {=（0 * 0） + MATCH（TRUE，（B2：B100}> 0,0）}

so your formula above becomes {=(0*0)+MATCH(TRUE,(B2:B100}>0,0)}

然后在THISWORKBOOK

Then in the THISWORKBOOK

输入此代码

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim cell As Range

For Each cell In Target
    If Target.Cells.Count = 1 Then
        If InStr(1, Target.Formula, "(0*0)+", vbTextCompare) And _
            Target.HasArray = False Then
            Target.FormulaArray = Target.Formula
        End If
    End If
Next cell

End Sub

当一个单元格更改时，代码将检查该项（0 * 0）+并将其分配为数组公式，如果它不是已经存在的。

whenver a cell is changed, the code will check for the term (0*0)+ and assign it as an array formula if it is not one already.

这篇关于excel数组公式：不必'ctrl-shift-enter'？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！