excel输入公式显示公式_快速输入复杂的Excel公式

excel输入公式显示公式

On Chandoo's blog, Hui is running a series of articles with Excel Tips. There were some great tips in this week's list, but my favourite one was down in the comments section – how to enter complex Excel formulas fast.

在Chandoo的博客上，Hui使用Excel Tips运行了一系列文章。 在本周的列表中有一些很棒的技巧，但是我最喜欢的一个技巧是在注释部分–如何快速输入复杂的Excel公式。

I've been using Excel for a long time, and I've never seen this suggestion before. And it's a real time saver!

我已经使用Excel很长时间了，而且以前从未见过这个建议。 这是一个实时的节省者！

Excel技巧文章 (Excel Tips Articles)

Last week was the second article in Hui's series of Excel tips. It featured Chandoo's Excel Ninjas, and you can see his list here. The Ninjas are the wonderful people who help out with questions in Chandoo's Excel forum.

上周是Hui的一系列Excel技巧中的第二篇文章。 它以Chandoo的Excel Ninjas为特色，您可以在此处查看他的列表 。 忍者是在Chandoo的Excel论坛中帮助解决问题的出色人选。

The previous week's list had some great tips too, and there will be more tips coming in a couple of weeks, so watch for those.

前一周的列表中也有一些很棒的技巧，并且在接下来的几周内还会有更多的技巧，所以请注意这些。

快速输入复杂的Excel公式 (Enter Complex Excel Formulas Fast)

Anyway, back to my favourite tip from the Ninja edition – I saw it in the comments section, posted by Wyn Hopkins. Wyn suggested using an AutoCorrect entry, so it's easier to create INDEX / MATCH formulas.

无论如何，回到我最喜欢的忍者版技巧-我在Wyn Hopkins发表的评论部分中看到了。 Wyn建议使用自动更正条目，因此创建INDEX / MATCH公式更加容易。

I almost ignored this tip, because I thought, "Excel already shows us the formula syntax. Why would I need this?" Fortunately, I read it carefully, and realized that it could be a daily time saver.

我几乎没有理会这个技巧，因为我想：“ Excel已经向我们显示了公式语法。为什么需要这个？” 幸运的是，我仔细阅读了一下，并意识到它可以节省每天的时间。

So, I tried it, and Wyn was right – this tip does make it easy to enter complex Excel formulas fast.

因此，我尝试了一下，Wyn是对的-此提示确实使您可以轻松快速地输入复杂的Excel公式。

复杂公式 (The Complex Formula)

In the comment, Wyn showed the formula that he puts in AutoCorrect. It is an INDEX / MATCH formula, with placeholders for the cell references. (You can read more about that powerful function duo on my website.)

在评论中，Wyn显示了他放入自动更正中的公式。 它是一个INDEX / MATCH公式，带有用于单元格引用的占位符。 (您可以在我的网站上阅读有关该功能强大的二重奏的更多信息 。)

If you want to try this tip (and I highly recommend it), then copy his formula. I'll show you what to do with it in a minute.

如果您想尝试这个技巧(我强烈推荐)，请复制他的公式。 一分钟后，我将告诉您如何处理。

=INDEX( DblClk_to_Select_Column_to_return, MATCH( DblClk_Single_Lookup_Cell, DblClk_Lookup_Column, 0),0)

= INDEX(DblClk_to_Select_Column_to_return，MATCH(DblClk_Single_Lookup_Cell，DblClk_Lookup_Column，0)，0)

如何添加自动更正条目 (How to Add an AutoCorrect Entry)

In the comment, Wyn didn't describe how to create an AutoCorrect entry, so here's how to do that:

在评论中，Wyn没有描述如何创建“自动更正”条目，因此，该操作如下：

1. First, copy Wyn's formula, if you didn't do that already
   首先，复制Wyn的公式(如果您尚未这样做的话)
2. In Excel, click the File tab (or Office circle) at the top left of the screen
   在Excel中，单击屏幕左上方的“文件”选项卡(或Office圆圈)
3. Click Options, then click Proofing, and click the AutoCorrect Options button.
   单击选项，然后单击校对，然后单击自动更正选项按钮。

4. In the Replace box, type a short code that Excel can "AutoCorrect" for you -- I used fnim (short for Function Index Match). Don't use a real word, because Excel will change this, every time you type it.

   在“替换”框中，键入一个简短的代码，Excel可以为您“自动更正”-我使用了fnim (“功能索引匹配”的缩写)。 不要使用真实的单词，因为每次键入时Excel都会更改它。

5. Click in the With box, and press Ctrl + V to paste Wyn's formula that you copied
   在“使用”框中单击，然后按Ctrl + V粘贴您复制的Wyn公式
6. Click Add, then click OK, twice, to exit.
   单击添加，然后单击确定两次，以退出。

如何使用自动更正条目 (How to Use an AutoCorrect Entry)

After you add an AutoCorrect entry, here's how to use it:

添加自动更正条目后，以下是使用方法：

1. Select the cell where you want to enter the formula
   选择要在其中输入公式的单元格

2. Type your code (fnim), followed by a space character (That tells Excel you've finished typing the code)

   键入您的代码( fnim )，然后输入一个空格字符(这告诉Excel您已经完成了代码的输入)

3. The formula with placeholders will appear in the cell
   带占位符的公式将出现在单元格中
4. Double-click on each placeholders, then click on the cell or range for that reference.
   双击每个占位符，然后单击该引用的单元格或范围。

添加更多自动更正公式 (Add More AutoCorrect Formulas)

If you find that you're saving time with this AutoCorrect entry, maybe there are a few more that you could add. What other complex formula structures do you use on a regular basis?

如果您发现使用此“自动更正”条目节省了时间，则可能还有更多可以添加的内容。 您还定期使用其他哪些复杂的公式结构？

I'll probably add an IFERROR version of INDEX / MATCH, because that's another one that I use quite often.

我可能会添加INDEX / MATCH的IFERROR版本，因为那是我经常使用的另一个版本。

Then, you just have to remember to use those shortcut codes!

然后，您只需要记住使用这些快捷方式代码即可！

翻译自: https://contexturesblog.com/archives/2016/05/05/enter-complex-excel-formulas-fast/

excel输入公式显示公式