需要有关excel公式的建议 [英] Need advice on excel formula

问题描述

大家好，

我面临Excel公式的问题。

我有一个用钢梁工作的Excel工作簿。

我有一组命名范围，都具有相同的结构：ref_ipe，ref_ipea，ref_hea，ref_heb，ref_upn，ref_upe ...

到目前为止一切都很好。



我有这种公式，我需要根据用户输入切换命名范围：

Hi all,
I am facing a problem with Excel formula.
I have an Excel WorkBook which play with steel beams.
I have a set named ranges, all with same structure: ref_ipe, ref_ipea, ref_hea, ref_heb, ref_upn, ref_upe ...
All nice so far.

I have this kind of formula where I need to switch named ranges depending on user inputs:

=VLOOKUP(B287,IF(A287="IPE",ref_ipe,IF(A287="HEA",ref_hea,IF(A287="UPN",ref_upn,IF(A287="TUBE",ref_tubec,ref_l)))),2,FALSE))

这个公式有效并且速度很快，但我的问题是我被要求添加更多的命名范围，这是添加越来越多嵌套IF的痛苦之处。

作为替代方案，我已经这样做了：

This formula works and is fast, but my problem is that I am asked to add more named ranges, and it is where it become a pain to add more and more nested IFs.
As an alternative I have done this:

=VLOOKUP(B23,INDIRECT(VLOOKUP(A23,{"IPE","ref_ipe";"IPEA","ref_ipea";"HEA","ref_hea"},2,FALSE)),2,FALSE)

这是多余的扩展范围，但由于INDIRECT是不稳定的，我的表因为我有成千上万的公式需要改变，所以我们会迅速降级。



我尝试过的方法：



我试过了

which is neat to extend with more ranges, but since INDIRECT is volatile, my sheet is downgrade quickly as I have thousands of formulas to change.

What I have tried:

I have tried

=VLOOKUP(B23,VLOOKUP(A23,{"IPE",ref_ipe;"IPEA",ref_ipea;"HEA",ref_hea},2,FALSE),2,FALSE)

但是excel不想要它，因为 VLOOKUP无法返回范围不允许带有命名范围的常数必须。



你知道更好的解决方案吗？



Nota ：公式可能包含从法语Excel翻译的错误。

but excel don't want it, because VLOOKUP can't return a range constant musts with named ranges are not allowed.

Do you know a better solution ?

Nota: formulas may contain errors as they are translated from French Excel.

推荐答案

我会为每个 IF（）和 VLOOKUP（）带值的那个（可能是这些列连接在一起的中介）

I would create a column for each IF() and VLOOKUP() the one with a value (with possibly an intermediary of those columns concatenated together)

我想我得到它

I think I got it

=VLOOKUP(B23,
CHOOSE(MATCH(A23,{"IPE","IPEA","HEA"},0),ref_ipe,ref_ipea,ref_hea),
2,FALSE)

A MATCH 获取用户输入的排名，选择 CHOOSE 来选择范围。

A MATCH to get rank of user input and a CHOOSE to select the range.

这篇关于需要有关excel公式的建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！