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

6个Excel实用小技巧 – 译学馆
未登录,请登录后再发表信息
最新评论 (0)
播放视频

6个Excel实用小技巧

6 Excel Tips from the Mr Excel Book Giveaway Winners

欢迎来到ExcelCampus
Welcome to ExcelCampus.
我是约翰 这个视频将介绍六个你们提供的技巧
My name’s John, and in this video I’m gonna share 6 tips that were submitted by you.
所以最近我在博客上赠送《Mr Excel最好的40个Excel技巧》
So I recently held a Giveaway on the blog for the “Mr Excel 40 Greatest Tips” book,
想要获得赠书的机会 你只需要提交一个技巧 你最喜欢的excel技巧
and, to enter the contest, you just had to submit a tip, your favorite excel tip.
现在有6个技巧脱颖而出
So, here are the 6 winners from the post,
我会在视频教程中分享你们提交的技巧
and I’m gonna share the tips that you submitted here in a video tutorial.
这样每个人都能从中学习
So everyone can learn from it.
第一个技巧是琳妮提供的 是CTRL+.键 我们等下会学
So the first one here is submitted by Lynne, it’s Ctrl Period, we are gonna go with that.
拉金德拉 如果我念错了很抱歉
Rajendra, sorry if I pronounce your name wrong,
提交了一个技巧 教你怎么使用数据透视表来处理数字
submitted a tip on how to use Pivot Tables for working with numbers.
我会介绍这里面我最喜欢的技巧 报表筛选页
I’m gonna show one of my favorite tips, which is Report Filter Pages.
汤姆·克劳奇提供了一个技巧 是关于excel表格和CTRL+T键的 也非常棒
Tom Crouch submitted a tip about Excel Tables and Ctrl+T, another great one.
黛安·史密斯 双击格式刷 又一个好技巧
Diane Smith Double Clicking the Format Painter, another awesome tip.
马修喜欢用F2来编辑单元格的文本或公式
Matthew likes to use F2 to Edit the Text or Formula in a cell.
德布雷·霍尔科姆使用CTRL+;键来添加日期
and Debre Holcomb uses Ctrl Semicolonto enter today’s date.
让我们学习这些技巧吧
So let’s go over all of these tips.
第一个 CTRL+.键 移动选中的区域
So the first one Ctrl Period to move around a selected range.
这是一个我总是使用的好技巧
This is a great one that I use all the time.
好 我这里选了一张数据表
So I’m just going to go to a sheet of data here,
我会选择一小部分 可以让你看到如何使用它
I’ll just select a small range, so you can see how this works.
首先按住键盘上的Ctrl键
Basically hold down the Ctrl key on the keyboard,
然后按.键
and then press the Period button,
就可以把选择框在这选中范围的四角里移动
that will move the selection around the corners of the range right here.
你可以看到我按了句号键
You can see I’m just pressing the Period,
然后这个选择框就在这范围的四角里动
and the selected cell is moving around the corners of the range.
那么什么情况下你会用到这个呢 或者说这对你有什么帮助呢?
Now where you can actually use this, or use this to your advantage?
我总是在复制和粘贴数据时用到这个技巧
I use this all the time when I’m copying and pasting data.
比如这个表里我有一些新数据
So here in this sheet I have some new data.
我会用Ctrl+Shift+End键选择全部数据
I’m just gonna hit Ctrl Shift End to select all this,
然后我用Ctrl+C复制它
and then I’m gonna hit Ctrl C to copy it.
一般我会想把它贴在这张表的这个位置 覆盖原数据
And now basically I’m wanna paste it over this data right here on this sheet.
所以我现在按了Ctrl+V粘贴
So I hit Ctrl V to paste right now.
你可以看到它粘贴上去了
You can see that it’s pasted.
但是我并不确定它是否完全覆盖掉了原数据
But I’m not exactly sure if it’s pasted all over all of the existing data,
我想要把它们都覆盖掉
I want to paste over all of the existing data.
所以如果我按了Ctrl 按住它然后再按.键
So if I hit Ctrl, if I hold down the Ctrl key and hit Period right now,
那它就会跳到选区的右上角
that will jump me over to the right corner, the top right corner of the selection,
到了船费这一栏
over here to the shipping fee column,
然后我就可以看到它的确被覆盖了
and so I can see that that has been covered.
现在如果我再按Ctrl+.键
Now if I hit Ctrl Period again,
那它就会跳到底部
that’ll jump me down to the bottom.
所以不用滚动鼠标
So without having to scroll,
我就可以到粘贴部分的底部
this has jumped me down to the bottom of the paste range,
就是我刚刚粘贴的那部分
the range that I just pasted.
然后我就可以看到这下面还有数据
And I can see that there’s still data below this,
所以我想要删除的原数据
so I still have some existing data below this there,
还有留着的部分
that I would want to go delete.
那我想把这几行删了 再按一次Ctrl+.键
So I want to delete those rows, and again if I hit Ctrl Period one more time,
跳到左下角
that’ll jump me to the bottom left corner,
还是可以看到下面有我想删的数据
and I can again still see that there’s data down below that I did need to delete,
我并没有完全覆盖
so I didn’t copy over everthing.
所以这是一个很棒的技巧 Ctrl+.键值得一用
So that’s a great one, Ctrl Period is a great tip to use.
下面是另一个我很喜欢的技巧 用在数据透视表里
Now, another one of my favourites, working with Pivot Tables,
是我最喜欢的数据透视表技巧之一
one of my favorite Pivot Table tips,
和比尔·杰伦合著书的斯维·由哈兹也喜欢这个技巧
And Szilvia Juhasz, who co-authored the book with Bill Jelen loves this one as well.
我知道这是她的最爱之一
I know this is one of her favorite,
因为实际上她给Bill Jelen演示过这个
because she actually showed it to Bill Jelen.
所以 这是一个很棒的技巧
So, this is a great one,
它可以展示报表筛选页
which is showing the report filter pages.
所以 如果你有一个这样的数据透视表
So, if you have a Pivot Table like this.
我把售货员这一栏放在筛选区
I have the salesperson up here in the filters area,
然后一般我就可以通过售货员来浏览筛选这个表单
and basically I could go and filter this report down here by salesperson.
我可以选择一个售货员的名字
So I could just chose a salesperson’s name,
点击确定 现在我们以确切的售货员名字筛选了整张数据透视表
click ok, and now we filter this entire Pivot Table, for that specific salesperson.
但如果你想要给列表里的每一个售货员都创建一个报表呢?
But what if you wanted to create a report for each salesperson in this list?
实际上你可以用数据透视表的一个功能实现这一点
Well you can actually do that with a feature of the Pivot Table.
选择数据透视表中任何一个的单元格
So if you select any cell in the Pivot Table,
然后看到这一栏的“分析”标签或者“选择”标签
and then go up here to the Analyze tab or the Options tab in the ribbon,
再看到这边的“选项”下拉栏
and then in this Options drop-down here.
有一个选项叫“显示报表筛选页”
There is an option that says show report filter pages,
点击它
just click on that,
然后会弹出一个窗口
and then this box will appear,
它问你想要对这个报表筛选里的哪一项创建表格
and it’s gonna ask you which field within the report filter here you want to run this on.
这里只有一项 就是售货员
And there’s only one field, the salesperson.
所以我们按“确定”
So we gonna press OK.
当我按“确定”时
And when I press OK,
它就会为列表中的每一个售货员在数据集中生成一个表单
it’s actually gonna create a sheet for each salesperson in that list, in out data set.
所以我要按“确定”了
So I’m gonna hit OK,
然后你现在可以看到底部很快生成了
and you can see now very quickly down here at the bottom.
我现在拥有了一个有数据透视表的表格
I now have a sheet with the pivot table on it,
它对应列表中的每一位推销员
that’s filtered for each sales rep in that list.
表格是用那个项目的名字命名的
And it also names the sheet name with that item,
就是数据透视表的项目 在这里也就是推销员的名字
that pivot table item, in this case it’s sales person’s name.
所以这是一个很棒的功能
So this is a great feature here.
你可以看到当我拉过表格列表栏
You can see as I scroll through my list of sheets,
让我们拉到底
let’s move this over,
我拥有了这里所有推销员的表格
I have all of my sales rep, sheets for all of my sales reps here.
所以就仅仅几秒钟
So within just a few seconds,
你可以在这个工作簿里创建所有报表
you can create this entire report, this book with all these reports in it.
所以我总是用这个很棒的技巧
So the great tip, show report filter pages,
来显示报表筛选页
the one I use all the time.
下一个技巧是由汤姆提供的
Now the next tip was submitted by Tom,
关于使用excel表和用Ctrl+T生成一张表
about using Excel tables and using Ctrl T to create a table.
这里我有一组数据
So here, I have this set of data.
目前它还不是excel表的样式
This is currently not formatted as an Excel table.
但如果你选择这个区域内的任一个单元格
But if you select any cell within the range here,
你希望这些成为一张表格
the range that’s gonna be the table,
那你按住Ctrl键 再按键盘上的T
and you hold down the Ctrl key and press T on the keyboard,
它就会生成一个表格
that would actually create the table,
或者弹出创建表格的提示
or bring up the create Table prompt.
现在如果我退出这个
Now if I exit out of this,
在工具栏的“插入”选项里也可以找到它
this is also located on the insert tab here of the ribbon.
就用这里的“表格”按钮 你可以看到我鼠标在的这个地方
With the table button right here, you can see that I hover over that.
它那里也显示了快捷键Ctrl+T
It shows the keyboard shortcut there, Ctrl T.
所以我们能使用它很快生成一个表格
So that’ll allow us to very quickly ro create a table.
我就 嗯 按“确定”
I’m just, uh, press OK,
只要你在这个区域里选择了一个单元格
as long as you have any cell selected inside the range there,
Excel会自动帮你选择整个区域
Excel will typically automatically find that for you the entire range.
然后你按“确定”
And just press OK,
它会转变为一张表格
and that’ll convert that to a table.
还有另一个小技巧
And one another little quick tip,
如果你不喜欢这种样式
if you don’t like the formatting here,
你可以看到我的表头样式
you can see my header formatting,
与原先样式有些混合
it’s kind of messed up from the previous formatting.
你可以通过右上角的“表样式”来调整
You can right click here on the Actual Cell Style, or the Table Style.
然后点击“应用并清除样式”
And click Apply and Clear Formatting.
现在我们清除了原有样式
And now we clear all that previous formatting,
应用了那个新的表格样式
and just apply that new table style.
所以当你制作表格时这也是一个小技巧
So that’s another little quick tip when you are creating tables there.
关于双击格式刷 黛安有一个好建议
Now Diane had a great suggestion about the double clicking the Format Painter.
所以如果你从未使用过格式刷的话
So if you’ve nerver used the Format Painter,
我这里有一个非常简单的小报表
I just have a real simple report here,
里面有一些特殊格式的单元格
with some cell that has some special formatting in it.
在这一栏的首个选项卡里
Right here in the home tab of the ribbon,
有一个小小的笔刷图标
there’s this little paintbrush icon.
叫做格式刷
That’s called the Format Painter.
如果你点了它一下
If you click it once,
它基本上会帮你把你选中的那个单元格的格式全部复制了
it will basically allow you to copy all the formatting of the cell you have selected.
所以我就选中了那个单元格 我现在取消选择 让你们再看一遍
So I had that cell selected. And I’m gonna escape so you can see that again.
我选择了B5单元格
I have cell B5 selected.
就点击“格式刷”
Just gonna click the Format Painter,
现在 如果我任选一个单元格 比如这个
and now, any cell that I select, so if I select this cell here,
它会应用刚刚那个单元格的全部格式
it will apply that formatting, all that formatting to this cell.
运用黛安的技巧
Now with Diane’s tip,
如果你双击了这里的格式刷
basically if you double click on the Format Painter here,
就是双击它
by double click it,
现在 当我移动鼠标选择单元格时
now, when I hover over and select the cell,
格式刷一直都是有效的
the Format Painter stays enabled.
所以我选择另一个单元格
So I can now select another cell,
它也会应用那个格式
and apply that formatting as well.
接下来我就可在任何想要的地方应用那个格式
So now I can just continue to apply that formatting anywhere I want.
当完成时 你可以点击退出键
Once I’m done, you can either hit the escape key,
或者到上边再点击一次格式刷
or you can go up here and click the Format Painter button again.
就可以退出格式刷模式
And that’ll exit out the Format Painter mode.
接下来关于使用F2键
And then Matthew had a another great tip,
马修也有一个很棒的技巧
on using the F2 key on the keyboard.
这也是我一直使用的另一个技巧
This is another one I use all the time.
如果你选择了一个单元格
If you have any cell selected,
你可以获得一个文本 或者公式
you can have a text, or you can have a formula.
这个单元格是文本
This cell here has text.
敲击键盘上的F2键
Hit F2 on the keyboard,
它会进入单元格编辑模式
that’ll jump you into this edit mode for this cell.
那你就可以开始在里面打字
And you can actually start typing here,
或者添加编辑公式
or adding to your formula or editing your formula.
所以这里包含了一个VLOOKUP公式
So this cell here contains a VLOOKUP formula,
如果我按F2键
If I hit F2 on the keyboard,
它会进入编辑模式以供我编辑这个公式
that’ll jump me into the edit mode to be able to edit this formula.
那我就可以修改它
And then I can make changes to it,
进去后随便我怎样编辑
and go in here and edit it however I like.
所以这是一个很棒的技巧
So, that’s a great one.
如果你完成了修改 按Enter
And then when you done making the changes, you hit Enter,
如果你不想修改 按Escape键
if you don’t wanna make any changes, you can hit the Escape key.
那你就跳出了编辑模式
And that’ll get you out of edit mode.
能够退出去选择其他单元格
And then you can go back and select cells.
这又是另一个来自马修的好技巧
So, another great tip from Matthew.
最后德布雷有一个在单元格里插入今日日期的技巧
and then finally, Debra had a tip about entering today’s date in a cell.
你可以使用Ctrl+; 快捷键做到
And you can use the Ctrl Semicolon keyboard shortcut to do that.
在这里任一空白单元格按下Ctrl键
So any cell here blank cell just hold the down Ctrl key,
然后按;键
and press the Semicolon key.
它就会在这儿插入今日日期
And that will enter today’s date, right there.
然后按Enter
and then if you just hit Enter,
它就会填入这个单元格
that’ll basically enter it in the cell.
Ctrl+Shift+; 会插入当前时间
Ctrl Shift Semicolon will enter the current time.
所以你也同样可以在这里插入当前时间
So that’s how you can enter the current time right there in a cell as well.
当你插入日期时间时 那两个快捷键
So those are two great keyboard shortcuts that can save you a lot of time,
会节省你很多时间
when entering dates and times.
所以希望你们喜欢这些
So I hope you enjoy that.
以上就是Mr.Excel书的获得者分享的六个技巧
that’s the six tips from the winners from Mr.Excel book give away.
你们可查看这篇博客 里面包含超过350个附加技巧
You can check out this blog post that contains over 350 additional tips,
均来自你们这些excelcampus.com的粉丝读者们
submitted by you, the fans, and readers of excelcampus.com.
我再次衷心感谢你们的参与
Again I really appreciate you participating in this.
未来 我将会在博客帖子中分享更多技巧
I’ll be sharing more tips from this blog post in the future.
所以我们可学习这些技巧
So we can all learn from these tips,
从而在每天的Excel任务里节省时间
and save time with our everyday tasks in Excel.
别忘记订阅我的免费新闻邮件 里面有更多此类小贴士和小技巧
Don’t forget to subscrib to my free email news letter for more tips and tricks like this.
再次感谢你的观看
Thanks again for watching,
不久后再见
and I’ll see you soon.

发表评论

译制信息
视频概述

关于使用Excel的六个小技巧,帮你节省时间,更快完成任务!

听录译者

null;

翻译译者

mo

审核员

译学馆审核团D

视频来源

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

相关推荐