许多人都在为从microsoftexcel的复杂单元格中提取信息而挣扎。我写的关于如何用这个函数从Excel中提取数字或文本的文章中的许多评论和问题证明了这一点。显然,如何从Excel工作表中分离出所需的数据并不总是很清楚。
我们已经从那篇文章中选择了几个问题在这里进行讨论,所以您可以看到解决方案是如何工作的。快速学习Excel并不容易,但使用这样的现实问题会有很大帮助。
读者阿德里问了以下问题:
I would like to extract the first set of numbers from a list, i.e. (122,90,84,118.4,128.9)Any ideas on what formula I can use?COIL112X2.5COIL90X2.5COIL84X2.0COIL118.4X1.8COIL128.9X2.0
事实上,要提取的数字长度不同,这比仅仅使用MID函数要复杂一些,但是通过组合几个不同的函数,我们可以去掉左边的字母以及“X”和右边的数字,在新的单元格中只留下所需的数字。
下面是我们用来解决这个问题的公式:
=VALUE(LEFT((RIGHT(A1,(LEN(A1)-4))),FIND("X",A1)-5))让我们从中间开始,找出解决办法,看看它是如何工作的。首先,我们从FIND函数开始。在本例中,我们使用FIND(“X”,A1)。这个函数在A1单元格中查找字母X。当它找到一个X时,它返回它所在的位置。例如,对于第一个条目COIL112X2.5,它返回8。对于第二个条目,它返回7。
接下来,让我们看看LEN函数。这只是返回单元格中字符串的长度减去4。结合正确的函数,我们从单元格中得到字符串减去前四个字符,这将从每个单元格的开头删除“COIL”。这部分的公式如下:RIGHT(A1,(LEN(A1)-4))。
下一级是LEFT函数。现在我们已经用FIND函数确定了X的位置,用RIGHT函数去掉了“COIL”,LEFT函数返回剩下的。让我们把这个问题进一步细分。当我们简化这两个论点时,会发生以下情况:
=LEFT("112X2.5", (8-5))LEFT函数返回字符串中最左边的三个字符(参数末尾的“-5”确保通过计算字符串中的前四个字符来消除正确的字符数)。
最后,VALUE函数确保返回的数字被格式化为数字,而不是文本。这个例子并不像在Excel中构建一个俄罗斯方块工作游戏那样有趣,但它确实是一个很好的例子,说明了如何组合一些函数来解决问题。
读者Yadhu Nandan也提出了类似的问题:
I want to know how to separate the numerical and alphabets.Example is – 4552dfsdg6652sdfsdfd5654I want 4552 6652 5654 in different cells
另一位读者Tim K SW为这个特殊问题提供了完美的解决方案:
Assuming that 4552dfsdg6652sdfsdfd5654 is in A1 and you want these numbers extracted into 3 different cells. 4452 in say cell B1 and 6652 in C1 and 5654 in D1 you'd use these.B1:=MID(A1,1,4)C1:=MID(A1,10,4)D1:=MID(A1,21,4)
这些公式相当简单,但是如果您不熟悉MID函数,您可能不了解它是如何工作的。MID有三个参数:单元格、结果开始的字符数和应提取的字符数。例如,MID(A1,10,4)告诉Excel从字符串的第十个字符开始取四个字符。
在三个单元格中使用三个不同的中间函数,可以将数字从这长串数字和字母中提取出来。显然,只有在每个单元格中始终有相同数量的字符(字母和数字)时,此方法才有效。如果每个公式的数量不同,你需要一个更复杂的公式。
文章中最困难的一个请求是读者Daryl:
Hi, I just want to ask how to separate very unformatted entry like:Rp. 487.500 (Nett 50% OFF)Rp 256.500 Nett 40% OFFRp99.000Rp 51.000/orang Nett (50% Off)
我花了一些时间来研究这个问题,自己也没能想出解决办法,于是我就去了Reddit。用户UnrepiredyMnast提供了这个公式,它很长而且非常复杂:
=SUMPRODUCT(MID(0&LEFT(A1,IFERROR(SEARCH("%",A1)-4,LEN(A1))),LARGE(INDEX(ISNUMBER(--MID(LEFT(A1,IFERROR(SEARCH("%",A1)-4,LEN(A1))),ROW($1:$99),1))*ROW($1:$99),0),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)如您所见,解码此公式需要相当长的时间,并且需要非常扎实的Excel函数知识。为了帮助您弄清楚这里到底发生了什么,我们需要了解一些您可能不熟悉的函数。第一个是IFERROR,它捕获一个错误(通常用磅符号表示,如#N/a或#DIV/0)并用其他内容替换它。在上面,您将看到IFERROR(SEARCH(“%,A1)-4,LEN(A1))。让我们把它分解一下。
IFERROR查看第一个参数,即SEARCH(“%”,A1)-4。因此,如果A1单元格中出现“%”,则返回其位置,并从中减去四个位置。如果字符串中没有“%”,Excel将创建错误,而返回A1的长度。
您可能不熟悉的其他函数要简单一些;例如,SUMPRODUCT将数组的元素相乘然后添加。大返回一个范围内的最大数。ROW与美元符号组合,返回对行的绝对引用。
了解所有这些函数是如何协同工作的并不容易,但是如果你从公式的中间开始,向外工作,你就会开始了解它在做什么。这需要一段时间,但如果你对它的工作原理感兴趣,我建议你把它放到一个Excel表格中,然后玩玩它。这是了解你工作的最好方法。
(另外,避免此类问题的最佳方法是更整洁地导入数据——这并不总是一个选项,但它应该是您的首选。)
正如您所见,解决任何Excel问题的最佳方法是一步一个脚印:从您所知道的开始,看看它给您带来了什么,然后从那里开始。有时你会得到一个优雅的解决方案,有时你会得到一个非常长,混乱,复杂的东西。但只要成功,你就成功了!
别忘了寻求帮助。有一些非常有才华的Excel用户在那里,他们的帮助可以是非常宝贵的解决一个棘手的问题。
你有什么解决棘手的提取问题的建议吗?你知道我们解决上述问题的其他解决方案吗?分享他们和你在下面的评论中的任何想法!
... 你的倒数第二个解决方案是把一个大的CSV文件分解成小块,但实际上并没有分解它。相反,它允许您将大量CSV文件加载到Excel中,并使用powerpivot工具打开它。是的;您可以有效地忽略Excel...
... 这种计算SWR的方法非常简单。但这并不能解释很多事情。 ...
... 本课程使用5种不同类型的数据和正确的图表类型来解释它们。两个案例研究应进一步巩固这些概念。从Excel2007或其任何版本开始。熟悉VBA和Excel函数者优先。 ...
...准备好的时候会是这样的: 按“确定”来解决您的目标。当它看起来不错的时候,就按OK。当Goal Seek找到解决方案时,Excel会通知您。 再次单击“确定”,您将看到通过更改单元格在选定单元格中求...
...Pandas,您需要导入Pandas库。如果你正在寻找一个重量级的解决方案,你可以下载anacondapython发行版,它内置了Pandas。如果你没有使用水蟒,熊猫很容易安装在你的终端。 ...