Excel - 从列表中选择项目 [英] Excel - Choose Item from a List

问题描述

我试图弄清楚是否可以从列表中选择一个Excel项目 - 当列表位于单个单元格中时 - 使用预定义的函数，或者如果我要编写自己的VBA让它发生。

类似于 = CHOOSE（）的功能似乎是我需要的，如果所有值都在不同的单元格中，那么它将起作用：

       ;  A                 &NBSP ;            B             &NBSP ;         

1 b> 的β            &NBSP ;                        &NBSP ;                

3 的 伽马               &NBSP ;                        &NBSP ;         

4 delta  ;                                                       

5 小量               &NBSP ;                        &NBSP ;         

单元格 B1 给我准确的我期望的价值：beta。

但是我的源数据的构造方式，这些值都在同一个单元格中：

                             A                                       B     &NB sp;       

1 alpha，beta，gamma，delta，epsilon = CHOOSE（2，A1）

在这个结构中， B1 导致 #VALUE！错误。我明白为什么会出现这种错误 - 但是我想知道是否存在以下任何一种：

1. 某种方式使Excel将单元格 A1 中的逗号分隔值解释为五个单独的值，或


2. 我应该使用的一些备用函数来完成此目标。 li>
   

如果Excel无法处理它，我将自己编写自己的函数 - 但是我觉得这样一个直截了当的需要，程序已经建立在某个地方。

解决方案

如果您单击要分割的单元格，然后单击数据>文本到列那么你可以将逗号分隔成不同的单元格。然后，您可以使用您以前使用的选择公式。

在文本到列功能中，您将希望在第一页上特别选择分隔选项如果您的数据与上述示例完全相同，则在第二页上选择逗号。

I'm trying to figure out if it is possible to select an Excel item from a list -- when that list is in a single cell -- using predefined functions, or if I'm going to have to write my own VBA to make it happen.

Something similar to the =CHOOSE() function seems to be what I need, as it works if all of the values are in separate cells:

      A                            B                       
1alpha  =CHOOSE(2,A1,A2,A3,A4,A5)
2beta                                                     
3gamma                                                 
4delta                                                     
5epsilon                                                 

The formula above in cell B1 gives me exactly the value I expect: "beta".

But the way my source data are constructed, the values are all in the same cell:

                          A                                   B             
1alpha,beta,gamma,delta,epsilon=CHOOSE(2,A1)

And in this construction, the forumula in B1 results in a #VALUE! error. I do understand why this error occurs -- but I'm wondering if there is either:

1. Some way to make Excel interpret the comma-separated values in cell A1 as five separate values, or
2. Some alternate function I should be using to accomplish this goal.

If Excel can't handle it, I'll write my own function -- but it seems like such a straightforward need that I assume the program has it built in somewhere.

解决方案

if you click on your cell you want to split and click "Data > Text to Columns" then you can split the data at the comma into separate cells. Then you can use your Choose formula that you are used to using.

Within the Text to Columns function you will want to specifically choose the "Delimited" option on the first page and then choose "Comma" on the second page if your data appears exactly as you have in your example above.

这篇关于Excel - 从列表中选择项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！