美国国债报价的 Excel 自定义数字格式
我在使用 Excel 中的自定义数字格式功能显示美国国债期货报价时遇到一些问题。
它们的显示格式为：
121'167
相当于 121 + 16.75/32
如果最后一位数字是 5，如 121'165，则相当于
121 + 16.5/32
如果最后一位数字 是 121 + 16.5/32是 2，如 121'162，那么它相当于
121 + 16.25/32
最后，如果最后一位数字是 0，如 121'160，那么它相当于
121 + 16/32
有没有办法使用自定义数字格式化功能来实现此定义还是超出了它的能力？
I am having some trouble using the custom number format feature in Excel to display US Treasury futures quotes.
The format by which they are displayed in is:
121'167
Which is equivalent to 121 + 16.75/32
If the last digit is a 5, as in 121'165, then it is equivalent to
121 + 16.5/32
If the last digit is a 2, as in 121'162, then it is equivalent to
121 + 16.25/32
and finally if the last digit is a 0, as in 121'160, then it is equivalent to
121 + 16/32
Is there a way to implement this definition using the custom number formatting feature or is it beyond it's capability?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这是一种疯狂的格式：添加分数，然后在除数中使用小数，然后截断这些小数......
但已经够咆哮了。如果我正确理解您的规范，这应该可以完成这项工作：
仅数字格式无法做到这一点，因为它们无法进行数字的实际转换（除了日期/时间等内置功能之外）。
This is one insane format: add fractions, then use decimals in the dividend, and then truncate those decimals...
But enough of the ranting. This should do the job, if I understood your specification correctly:
Number formats alone cannot do this, because they cannot do actual conversions of the numbers (apart from the built-ins like date/time).
您可以将 TEXT() 函数应用于两个值并将它们连接成一个字符串。
您需要计算出舍入（我在第二项中放入常数 *10-1）以及如何获得“/32”...我无法从您的问题中发现这一点
希望这有助于祝
您好运
you can apply the TEXT() function to both of your values and concatenate them into a single string.
You need to work out rounding (I put a constant *10-1 into the 2nd term) and how to obtain the "/32" ... I couldn't spot that from your question
Hope that helps
good luck
这对我来说是最有效的。我只需以 109'20.5 的形式获取 2 年期票据报价，然后在 Excel 中复制即可。我将报价转换为 109.6406 (109 + 20.5/32) 并应用上述公式。
This is what worked best for me. I simply had to take a 2-year note quote in the form of, as an example, 109'20.5 and replicate in excel. I converted the quote into 109.6406 (109 + 20.5/32) and applied the above formula.
我知道这有点过时了，但今天偶然发现了这一点并且需要相同的信息。这是我使用的方法，替换公式中的单元格以提取报价。
5岁：
=左(J22,3)+(右(J22,3)/320)+IF(INT(右(J22,1))=2,0.0015625,IF(INT(右(J22,1))=7,0.0015625 ,0))
10 年: =左(B28,3)+(右(B28,3)/320)
I know this is a bit dated, but stumbled upon this today and needed the same information. Here is what I use, replace the cell across the formula to pull the quote.
5 yr:
=LEFT(J22,3)+(RIGHT(J22,3)/320)+IF(INT(RIGHT(J22,1))=2,0.0015625,IF(INT(RIGHT(J22,1))=7,0.0015625,0))
10 yr: =LEFT(B28,3)+(RIGHT(B28,3)/320)