大盘指数 excel_Excel中的指数问题

大盘指数 excel

Let's call this installment, "The Mysterious Case of the Vanishing Parts." (Read carefully -- that's paRts, not paNts.)

让我们把这部分称为“消失零件的神秘案例”。 (请仔细阅读-这是纸张，而不是纸张。)

And before I begin this sad story, remember to add your Excel question in the comments for Friday's Excelerators Quiz post, so you have the chance to win a shiny new monitor.

在开始这个悲伤的故事之前，请记住在星期五的Excelerators测验帖子的评论中添加您的Excel问题，这样您就有机会赢得一台崭新的显示器。

奇怪的格式 (Strange Formatting)

Last Friday, I was working on a client's Excel file, revising some VBA code that splits a list of manufacturing parts into multiple columns, strips a couple of characters off the front of the part name, and copies the results to another column.

上周五，我正在处理客户的Excel文件，修改了一些VBA代码，该代码将制造零件的列表分成多列，在零件名称的前面去除了几个字符，然后将结果复制到另一列。

It seemed to be going well, until I got an email from my client, saying that some of the part numbers looked funny. He included a screenshot, and indeed, those part numbers did look odd. Here's an example, using some dummy data.

直到我收到客户的来信，说其中的一些零件编号看起来很有趣，这一切似乎进展顺利。 他提供了一个屏幕截图，确实，那些零件编号确实看起来很奇怪。 这是一个使用一些伪数据的示例。

科学符号格式 (Scientific Notation Formatting)

"Aha!" I thought. (Yes, I actually talk to myself like that. 😉 ) Those parts were all numbers, so Excel just formatted them as Scientific Notation. I could simply format the column as General at the end of the macro, to make them look right.

“啊哈！” 我想。 (是的，我实际上是这样自言自语的。)这些部分都是数字，因此Excel只是将其格式化为科学计数法。 我可以简单地将宏末尾的列格式设置为General，以使它们看起来正确。

Unfortunately, it wasn't that simple. When I clicked on one of the affected cells, the formula bar showed 220 as the actual part number. So, if I changed the formatting to General, 220 is the part number that would be copied to other cells, later in the macro.

不幸的是，事情并非如此简单。 当我单击一个受影响的单元格时，编辑栏显示的实际零件号为220。 因此，如果我将格式更改为“常规”，则220是将要复制到其他单元格(稍后在宏中)的部件号。

However, after a bit more investigation, I found that the original part number wasn't 220, it was 22E1. Close, but manufacturing might be adversely affected if Excel starts making up new part numbers!

但是，经过更多调查，我发现原始零件号不是220，而是22E1。 关闭，但是如果Excel开始制作新零件号，则制造可能会受到不利影响！

为什么零件号被更改 (Why the Part Number Was Changed)

Because the original part number (22E1) started with numbers, followed by the letter E, then another number, Excel interpreted it as a number in Scientific Notation. It converted that number to Excel's style of Scientific Notation (exponential) formatting – 2.20E+02.

由于原始零件号(22E1)以数字开头，后跟字母E，然后是另一个数字，因此Excel将其解释为科学计数法中的数字。 它将数字转换为Excel的科学计数法(指数)格式– 2.20E + 02。

I'm sure Excel was trying to help, but that creates problems, just as it does when Excel changes 6-10 to a date for you, without asking.

我确定Excel会尝试提供帮助，但这会产生问题，就像Excel将6-10更改为您的日期而没有询问一样。

The workaround to this unsolicited help is to force the data to be recognized as text, as Microsoft explains in its article: Text or number converted to unintended number format in Excel.

如Microsoft在其文章中所述，此主动提供的帮助的解决方法是强制将数据识别为文本：将文本或数字转换为Excel中的意外数字格式 。

解决问题 (Fixing the Problem)

In my client's macro, instead of formatting the parts column after copying the part names, I added an apostrophe at the start of each part name.

在客户的宏中，我没有在复制零件名称之后格式化零件列，而是在每个零件名称的开头添加了撇号。

hat left the "E" parts in their original format, and the problem was solved. Here's the formula that is added in the VBA code:

帽子将“ E”部分保留为原始格式，此问题已解决。 这是在VBA代码中添加的公式：

.Range("D2?).Formula = "=IF($A2<>$A1,""'"" & $U2,"""")"

.Range(“ D2？)。Formula =” = IF($ A2 <> $ A1，“”'“”＆$ U2，“”“”)“

科学符号解释 (Scientific Notation Explained)

If you'd like to know how scientific notation works, in fairly simple terms, you can read this article: Scientific Notation

如果您想以简单的方式了解科学计数法的工作原理，则可以阅读以下文章： 科学计数法

And for an even shorter and simpler description, here's a short video in which a math teacher explains scientific notation. And remember to do your homework!

为了更简短，更简单的描述，以下是一段简短的视频，其中数学老师解释了科学计数法。 并记得做作业！

演示地址

翻译自: https://contexturesblog.com/archives/2010/01/25/exponential-problems-in-excel/

大盘指数 excel