ADM-201 dump PMP dumps pdf SSCP exam materials CBAP exam sample questions

乔恩的表格校园——如何转换表格中的数字 – 译学馆
未登录,请登录后再发表信息
最新评论 (0)
播放视频

乔恩的表格校园——如何转换表格中的数字

Excel Convert Text to Numbers Keyboard Shortcuts

欢迎来到Excel学院
Welcom to ExcelCampus.
我叫乔恩 今天我将教大家几个小技巧
My name is Jon, and today I’m going to show you some quick tips
用来把文本转换为数字
on how to convert numbers stored as text.
很多情况下当你从GL软件
A lot of times when you get data out ofa GL software
或ERP系统导出数据后
or ERP system.
单元格中的内容是数字
The text that’s inside the cells arenumbers.
但它们实际上是以文本格式存储的
But they actually stored in the worksheet as text.
你可以通过单元格左上角
And you can tell when they have that
的小绿箭头来识别
small green arrow on the top left corner of the cell.
这些数字实际上都是文本格式的
Thoes numbers are actually stored as text.
这里有一个小提示框
And there’s a little error box there
告诉你这个数字是文本格式的
that shows you that this number isstored as text.
Excel有个内置功能
So there’s built-in function in Excel
可把单元格转为数字格式
to just convert that cell to a number.
你只要点一下 单元格就转换为数字了
You can click that it’ll do that this cell actually becomes a number.
显然如果你有销售额、产品序号
And obviously if you have values like sales dollar values
这一类的数据
in product codes and thoes types of things.
你得把它们转为数字
You’ll want to convert thoes to numbers.
以便于进行计算
So you can do calculation on them.
这在你进行列查找时也很有用
This is also useful if you’re doing v-lookups,
你查找或引用的一部分数据是数字格式
and some of your lookup or reference are stored as numbers,
而这些新数据是文本格式
and then these new values you have restored as text
这会导致查询错误
that cause error in your lookups.
所以转换数字格式很有必要
So there’s a lot of good reasons to convert these to numbers.
然而有时候你的工作表非常大
However sometimes when you have a really large sheet of data,
这就很麻烦了
it becomes cubersome,
因为你需要全选一列单元格
because you have to select all the cells
或者全选整个工作表
in the column or maybe all the cells in the worksheet,
然后才能进行转换
before you can then convert them.
你可以选中多个单元格然后使用该功能
So you can select mutiple cells and use that function.
只不过多选并不容易实现
It just becomes tough to select them all
有时候选择区域一下就没了
and sometimes you’ll loose this box all together.
所以我会告诉你一些快速转换的小技巧
So I’m gonna show you some quick tips on how to actually do that a little faster.
如果你……只要你选择了单元格
If you…as long as you have a cell selected
它左边会出现这个方框
that has this box appearing to the left of it.
你可用快捷键全选工作表的所有单元格
You can use some keyboard shortcuts to then select all the cells in your worksheet.
点击Ctrl和A
If you click Ctrl+A,
你就选中了显示范围内所有单元格
you’ll select all the cells in the range.
如再次按Ctrl和A则全选表中单元格
And if you hit Ctrl+A again, you’ll select all the cells in the worksheet.
那么按一次Ctrl和A可选中连续区域
So Ctrl+A once will select all the cells in the contiguous range.
如果你像例子中这样有空列
If you have a blank column like in this example,
那么再次按Ctrl和A则会选中整个表
then if you hit Ctrl+A again, it’ll select all the cells in the entire worksheet.
同样在工作表左上角会出现这个小提示框
Then that same error box should be in the top left corner of the worksheet,
你可以用“转换为数字”功能
and you can use the Convert to Number function
把所有文本转换为数字格式
to convert all of your numbers stored as text to numbers.
还有一个全选列中所有单元格的办法
Another option if you just want to highlight all the cells in this column,
比如这个第2列
it’s in column B.
你可以点击任意一个包含提示框的单元格
Again you wanna start with one cell that contains the error.
然后按Ctrl加空格 就像这样
And you can click Ctrl+Spacebar on the keyboard, it’s Ctrl+Spacebar.
也会全选整列
And that’ll highlight the entire column.
提示框会出现在列的最上方
And the error box should be at the top of the column.
你同样可以转换数据
And you can do the same thing Convert to Number.
这将把全列转换为数字格式
That’ll convert the entire column to numbers.
同样的技巧 按Ctrl加Shift
That’s same feature also works by using
可选择整行
Shift+Spacebar to select the entire row.
你可以看到提示框在最左侧
And you’ll see again that the error box is over there on the left side.
如你需要转换多列数据
If you want to do the conversion on more than one column,
你可以先按Ctrl加空格键
and you could first click Ctrl+Spacebar
全选整个D列
to select this column D.
如果按住Shift键再按左右方向键
And if you hold down the Shift key and use the Left Right arrows,
你就可以选择多列了
you can select mutiple columns.
若我们想把这2列转换为数字格式
So if we just want to convert these two columns to numbers,
我们可以选中它们然后来这个提示框
we could have those selected and then run the error box up here.
点击“转换为数字”
And click the Convert to Number.
那么我们的转换就仅限于这2列
And then we just only select in those two columns to do the conversion.
同样的方法也适用于行
It works the same with the rows as well.
如果你按Shift加空格就会选择整行
If you hold Shift+Spacebar, you’ll select the entire row.
然后按住Shift再按上下方向键
And then you can hold down the Shift key, and use the Up and Down arrows
选择多行
to select more than one row.
选定行之后你可以看到提示框在左侧
Once you have the row selected you could then see the error box on the left side,
点击“转换为数字”即可
and click Convert to Number.
这就是全选一列或者显示范围中
So that’s a quick way to select all your cells
所有单元格的快捷方法
in a column or in a range,
可以用Excel内置功能
and then convert them to number
把它们转换为数字
using the built-in function in Excel.
还有其它转换方法
There are other ways to do this
如使用菜单中“文本到列”功能
using the Text to Columns functions on the data menu as well.
你也可使用函数 或者使用选择性粘贴
And you can also use formulas, you could use a paste special,
或为单元格的文本做一个乘法
and do a multiplication on the text in the cell itself.
实现方法有很多
There’s a lot of different ways to do this.
但我发现最快最简单的
But I find that the fastest and the easiest is
还是使用Excel内置功能
to use the built-in feature in Excel.
只需要选择所有你想要转换的数据即可
Just a matter of knowing how to select the entire range that you want it to convert.
希望对你有所帮助 如果有问题
I hope this helps. If you have any questions,
请在评论区留言或者给我发电邮
please leave a comment or email me.
谢谢收看
Thank you.

发表评论

译制信息
视频概述

我们看到表格里的数字并不一定都是数字格式,所以在进行计算或者查找时才会报错。如何将其快速转换为数字格式?视频将告诉大家

听录译者

真皮沙发

翻译译者

AF

审核员

审核员YX

视频来源

https://www.youtube.com/watch?v=aUcxIO9NL5o

相关推荐