MS Excel散点图将月份转换为数字 [英] MS Excel Scatterplot converts Months to Numbers
问题描述
软件:MS Excel 2016
文件链接: GitHub存储库
month.xlsx
的 Sheet2
有表
当我选择它并插入散点图时,结果是month.xlsx
的Chart1
.无论我做什么,我都无法使X轴显示在月份"中(现在它显示月份"的数字).
帮助!
问题很可能是您存储月份的方式.从事物的外观来看,我可能会冒险猜测它们实际上是文本,而不是数字格式,可以完整显示月份的名称.为了使月份显示在图形中,您将需要输入月份作为Excel可以理解为数字的序列日期,或者需要创建一个虚拟列来保存相同的信息,然后您可以将其隐藏. >
一旦您有一个可以由excel处理的日期,您就可以选择X轴并设置格式样式.选择自定义"作为类别,然后使用"mmm"或"mmmm"作为自定义格式以仅显示月份名称.或者,您可以使用日期格式显示整个日期.
输入日期序列的两种方法是简单地输入与您的区域设置匹配的数字日期格式或使用以下公式:
=DATE(year,month,day)
年,月和日都是整数,或者计算得出可接受整数的公式
Software: MS Excel 2016
Link to files: GitHub Repository
Sheet2
of month.xlsx
has table
When I select it and Insert Scatterplot, Chart1
of month.xlsx
is the result. No matter what I try, I cannot get the X-Axis to display in Months (right now it displays Month's number).
Help!
The problem is most likely the way you have your months stored. By the looks of things I would hazard to guess they are actually text and not numbers formatted to display the name of the month in full. In order to get the months to display in your graph, you will either need to enter the months as a serial date which Excel can understand as a number, or create a dummy column to hold this same information which you can latter hide.
once you have your months in a serial date that excel can handle, you can then select the X-axis and set the format style. Select custom as the category, and then use mmm or mmmm as you custom format to display just the name of the month. Alternatively you could show the whole date using date formats.
Two ways to enter a date serial are to simply enter the numeric date format that matches your regional settings or use the following formula:
=DATE(year,month,day)
Where year, month and day are all integer, or formulas that evaluate to acceptable integers
这篇关于MS Excel散点图将月份转换为数字的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!