excel很实用的办公小技巧

​​​​Excel里简单粗暴的7个小技巧!

办公软件看似简单,其实花样很多,尤其Excel表格。真心后悔当初大学没好好学计算机,只顾着用电脑玩LOL,看美国大片,工作后才知道office的重要性,不夸张的说,只要玩转了office在哪个城市都不会失业。

本文只是简单介绍一下,我平时在工作中用的比较多的7个小技巧,希望对您有些许帮助。

1、制作打钩的方框

EXCEL超级实用的小技巧

方法:在单元格内输入“R”→设置字体为Wingdings2(设置好字体可以试试其他的字母,会出来各种好玩的形状哦)。

2、快速选中一列/一行数据

excel中快速选择一列

方法:选中2行以上,同时按“Ctrl+Shift+↓”即可。

对于较少的数据可以选中,然后随着鼠标一点一点往下拉,但是一旦数据量较大,传统的方式十分不便捷。此方式同样适用于快速选中一行数据。

3、批量去除数字上方的“绿色小三角”

EXCEL中批量去除绿色小三角

方法:选中该列中带有绿色小三角的任意单元格,鼠标向下拖动,然后点击该列的右侧,记住一定要右侧,选择“转换为数字”即可。

在使用VLOOKUP函数时,若是数字带有绿色小三角容易出现“#N/A”的现象,所以使用函数前最好均“转换为数字”。

4、分段显示手机号码

分段显示手机号码

方法:选中号码列,点击鼠标右键(或者直接Ctrl+1)→设置单元格格式→自定义→G/通用格式输入000-0000-0000。

此种方式主要运用于汇报材料中,看号码不用那么费劲,一目了然。

5、用斜线分割单个单元格

插入直线

方法:选中对象→插入形状(直线)→ALT+鼠标,快速定位单元格边角(自动识别)。

以前三分单元格中的两条线都是一点一点凑上去的,有没有?

6、带有合并单元格的排序

横竖转化方法:选中对象→排序→取消勾选数据包含标题→选择序列、排序依据、次序。

再也不用把合并的单元格删除后再进行排序啦,啦啦啦……

7、横竖转化

横竖转化EXCEL

方法:选中对象→复制→选择性粘贴→转置。

高中和大学的计算机考试应该都考过这个题目吧,朋友以前参加公务员考试的时候竟然也遇见了这个题,从此告别一个一个复制粘贴。

以上只是一些我在平时工作中经常使用的小技巧,Excel里面的函数包罗万象。

听说软件都是版本越低越好用,offic2007要吗?附上下载地址请猛搓:链接: https://pan.baidu.com/s/1qZ0dooG 密码: apw6 若还需要软件或者教程可以微信呼我

杨永的微信

杨永的微信

 

未经允许不得转载:杨永博客 » excel很实用的办公小技巧

赞 (0)

评论 90

  • 昵称 (必填)
  • 邮箱(必填)
  • 网址(选填)
  1. 民间秘术转眼又是新一年,博主万事如意不差钱!回复
  2. 1163848899博客还真是个好东西!回复
  3. 闺房独自乐不错!不错!感觉好极了!回复
  4. xing1982不错不错!内容感觉好极了!回复
  5. 屌炸天新春佳节到。祝好!祝好!回复
  6. 趣头条鸟儿叫,花儿笑,一年一季春来到!回复
  7. minecraftHello are using Wordpress for your blog platform? I'm new to the blog world but I'm trying to get started and create my own. Do you need any coding knowledge to make your own blog? Any help would be greatly appreciated!回复
  8. minecraftAw, this was an incredibly good post. Taking the time and actual effort to generate a really good article… but what can I say… I put things off a whole lot and never seem to get anything done.回复
  9. minecraftPlease let me know if you're looking for a writer for your site. You have some really good posts and I feel I would be a good asset. If you ever want to take some of the load off, I'd absolutely love to write some articles for your blog in exchange for a link back to mine. Please blast me an e-mail if interested. Thank you!回复
  10. minecraftI'm gone to tell my little brother, that he should also pay a visit this weblog on regular basis to get updated from most recent news update.回复
  11. minecraftWoah! I'm really digging the template/theme of this website. It's simple, yet effective. A lot of times it's very hard to get that "perfect balance" between usability and visual appeal. I must say you have done a superb job with this. Also, the blog loads very quick for me on Safari. Superb Blog!回复
  12. minecraft free download 2018There's certainly a lot to find out about this issue. I like all of the points you made.回复
  13. minecraft free download 2018I'm curious to find out what blog system you happen to be working with? I'm having some minor security problems with my latest blog and I would like to find something more secure. Do you have any recommendations?回复
  14. minecraft free download 2018I have been exploring for a little bit for any high quality articles or weblog posts on this kind of house . Exploring in Yahoo I finally stumbled upon this website. Studying this information So i am happy to show that I've a very excellent uncanny feeling I came upon exactly what I needed. I so much for sure will make certain to do not forget this site and provides it a look regularly.回复
  15. minecraft free download 2018Good post however , I was wondering if you could write a litte more on this subject? I'd be very thankful if you could elaborate a little bit further. Thanks!回复
  16. minecraft free download 2018I'm no longer certain the place you are getting your information, however great topic. I must spend a while studying more or understanding more. Thanks for excellent information I used to be on the lookout for this info for my mission.回复
  17. our websiteI just want to say I am just all new to blogging and site-building and honestly loved this blog. More than likely I’m going to bookmark your blog . You certainly have terrific article content. Cheers for sharing with us your webpage.回复
  18. John Deere Technical ManualsHowdy, May I grab that photo and usage it on my website?回复
  19. Travel & LeisureHello There. I found your blog using msn. This is a very well written article. I’ll be sure to bookmark it and return to read more of your useful information. Thanks for the post. I will definitely return.回复
  20. Travel & LeisureYou are a very capable individual!回复
  21. Travel & LeisureAs a Newbie, I am always browsing online for articles that can aid me. Thank you回复
  22. Home ServiceDefinitely believe that which you said. Your favorite reason seemed to be on the internet the easiest thing to be aware of. I say to you, I certainly get irked while people think about worries that they just do not know about. You managed to hit the nail upon the top and defined out the whole thing without having side-effects , people can take a signal. Will likely be back to get more. Thanks回复
  23. Homeserve Usa ComplaintsValuable info. Lucky me I found your site by chance, and I'm surprised why this coincidence didn't came about in advance! I bookmarked it.回复
  24. Home Service PlansI've been browsing on-line more than 3 hours today, but I never found any interesting article like yours. It is beautiful price sufficient for me. In my opinion, if all web owners and bloggers made good content as you did, the net shall be much more useful than ever before.回复
  25. Homeservice ClubHiya, I'm really glad I've found this info. Nowadays bloggers publish only about gossips and internet and this is really frustrating. A good site with exciting content, this is what I need. Thank you for keeping this site, I'll be visiting it. Do you do newsletters? Can't find it.回复
  26. Benefits of Coconut OilHello just wanted to give you a quick heads up. The words in your content seem to be running off the screen in Safari. I'm not sure if this is a formatting issue or something to do with browser compatibility but I thought I'd post to let you know. The design and style look great though! Hope you get the problem solved soon. Kudos回复
  27. What Is Home ServiceGreat paintings! This is the kind of information that are meant to be shared around the net. Disgrace on the search engines for no longer positioning this submit higher! Come on over and talk over with my web site . Thanks =)回复
  28. Home Service Club ReviewsGreat blog here! Also your web site loads up fast! What web host are you using? Can I get your affiliate link to your host? I wish my site loaded up as quickly as yours lol回复
  29. Men'S HealthI just couldn't depart your site before suggesting that I really enjoyed the standard information an individual provide to your visitors? Is gonna be back regularly to check up on new posts回复
  30. Health FoodMagnificent beat ! I wish to apprentice while you amend your site, how could i subscribe for a blog website? The account aided me a acceptable deal. I had been tiny bit acquainted of this your broadcast offered bright clear idea回复
  31. Health StoreI think this is one of the most vital information for me. And i am glad reading your article. But want to remark on few general things, The website style is perfect, the articles is really nice : D. Good job, cheers回复
  32. Coconut OilHey! This is my first visit to your blog! We are a team of volunteers and starting a new initiative in a community in the same niche. Your blog provided us beneficial information to work on. You have done a outstanding job!回复
  33. Department Of HealthAwsome article and straight to the point. I don't know if this is really the best place to ask but do you guys have any thoughts on where to hire some professional writers? Thank you 🙂回复
  34. Men'S HealthThank you for the auspicious writeup. It in fact was a amusement account it. Look advanced to far added agreeable from you! By the way, how could we communicate?回复
  35. NutritionWhats Going down i'm new to this, I stumbled upon this I've found It absolutely useful and it has aided me out loads. I hope to contribute & help other customers like its helped me. Great job.回复
  36. knoxcountyky.comHello There. I found your blog using msn. This is a really well written article. I will be sure to bookmark it and return to read more of your useful info. Thanks for the post. I will definitely comeback.回复
  37. Health Benefits of Coconut OilAfter I originally left a comment I seem to have clicked on the -Notify me when new comments are added- checkbox and from now on each time a comment is added I recieve 4 emails with the exact same comment. Perhaps there is a way you are able to remove me from that service? Kudos!回复
  38. HealthI like the valuable info you provide in your articles. I’ll bookmark your weblog and check again here regularly. I'm quite certain I’ll learn many new stuff right here! Good luck for the next!回复
  39. http://www.galaxymage.orgI'm extremely impressed with your writing skills as well as with the layout on your weblog. Is this a paid theme or did you customize it yourself? Anyway keep up the nice quality writing, it is rare to see a nice blog like this one nowadays..回复
  40. Online BusinessWonderful beat ! I would like to apprentice while you amend your site, how could i subscribe for a blog website? The account helped me a acceptable deal. I had been a little bit acquainted of this your broadcast provided bright clear concept回复
  41. lucyhounsom.comwhoah this weblog is fantastic i like reading your posts. Keep up the good work! You recognize, many individuals are hunting around for this info, you can aid them greatly.回复
  42. solitaireaz.comI just wanted to type a brief note to be able to thank you for these superb points you are placing at this site. My incredibly long internet search has at the end been compensated with incredibly good facts and techniques to go over with my company. I 'd point out that most of us readers actually are unquestionably blessed to be in a very good community with very many special professionals with useful techniques. I feel very happy to have discovered the webpages and look forward to really more exciting times reading here. Thank you once again for everything.回复
  43. Business LetterI’m not sure where you are getting your information, but great topic. I needs to spend some time learning much more or understanding more. Thanks for great info I was looking for this information for my mission.回复
  44. http://www.travelscour.com/As a Newbie, I am constantly browsing online for articles that can aid me. Thank you回复
  45. workfromhomemomma.comGreat website. A lot of helpful info here. I am sending it to some buddies ans additionally sharing in delicious. And obviously, thanks on your effort!回复
  46. http://www.xpertseoservices.comHiya, I am really glad I've found this information. Nowadays bloggers publish just about gossips and net and this is actually irritating. A good web site with exciting content, this is what I need. Thanks for keeping this site, I'll be visiting it. Do you do newsletters? Can't find it.回复
  47. ProfitabilityThank you for the good writeup. It in fact was a amusement account it. Look advanced to far added agreeable from you! By the way, how can we communicate?回复
  48. Benefits of Coconut OilI think the admin of this web site is in fact working hard for his web page, as here every material is quality based material.回复
  49. WomenBut wanna comment on few general things, The website pattern is perfect, the subject matter is very good. "War is much too serious a matter to be entrusted to the military." by Georges Clemenceau.回复
  50. Coconut OilWhat a material of un-ambiguity and preserveness of precious know-how regarding unexpected emotions.回复
  51. Benefits of Coconut OilHi, I do think this is a great site. I stumbledupon it 😉 I am going to revisit yet again since I book-marked it. Money and freedom is the greatest way to change, may you be rich and continue to help others.回复
  52. Financial NewsI have been surfing on-line more than 3 hours nowadays, yet I by no means found any interesting article like yours. It¡¦s beautiful worth sufficient for me. In my opinion, if all webmasters and bloggers made just right content material as you probably did, the net might be much more useful than ever before.回复
  53. OpportunityI and my pals have been reading through the nice helpful tips from the blog and so instantly came up with an awful suspicion I had not expressed respect to the web blog owner for those strategies. These people were definitely certainly stimulated to read through all of them and have actually been loving those things. Appreciate your truly being so thoughtful and also for making a decision on these kinds of extraordinary resources most people are really needing to discover. My very own honest regret for not expressing gratitude to earlier.回复
  54. OpportunityThank you for the sensible critique. Me and my neighbor were just preparing to do some research about this. We got a grab a book from our local library but I think I learned more clear from this post. I am very glad to see such wonderful info being shared freely out there.回复
  55. Hvac ServiceI intended to write you this bit of word to be able to thank you so much once again considering the amazing ideas you have discussed in this case. It was generous with people like you in giving openly what exactly numerous people would've distributed for an electronic book to help make some profit for themselves, precisely seeing that you might have tried it in case you desired. These thoughts additionally served to become a great way to know that most people have the identical dreams similar to mine to know somewhat more when it comes to this issue. I am sure there are a lot more pleasurable instances up front for those who check out your website.回复
  56. TravelOnly a smiling visitor here to share the love (:, btw great style .回复
  57. Home Service Club Warranty ReviewsI have read several just right stuff here. Certainly worth bookmarking for revisiting. I wonder how so much effort you place to make one of these fantastic informative web site.回复
  58. health careExcellent weblog right here! Also your web site loads up fast! What host are you using? Can I am getting your affiliate hyperlink for your host? I desire my website loaded up as fast as yours lol回复
  59. airline flightsUnquestionably believe that which you said. Your favorite justification appeared to be on the web the easiest thing to be aware of. I say to you, I definitely get annoyed while people think about worries that they plainly don't know about. You managed to hit the nail upon the top as well as defined out the whole thing without having side effect , people could take a signal. Will probably be back to get more. Thanks回复
  60. HealthierI savor, result in I discovered exactly what I was taking a look for. You've ended my four day lengthy hunt! God Bless you man. Have a nice day. Bye回复
  61. Health StoreI really wanted to write a word to be able to express gratitude to you for those amazing recommendations you are writing on this site. My considerable internet search has at the end been honored with reputable details to go over with my classmates and friends. I would declare that many of us readers are rather fortunate to dwell in a superb website with many outstanding individuals with interesting tips. I feel somewhat grateful to have discovered your website and look forward to some more entertaining minutes reading here. Thanks a lot again for all the details.回复
  62. Health ArticlesExcellent website. A lot of helpful information here. I¡¦m sending it to some buddies ans also sharing in delicious. And obviously, thanks on your effort!回复
  63. Whole Food VitaminsI carry on listening to the news bulletin lecture about getting boundless online grant applications so I have been looking around for the top site to get one. Could you tell me please, where could i find some?回复
  64. health newsGreat write-up, I am regular visitor of one¡¦s web site, maintain up the nice operate, and It's going to be a regular visitor for a lengthy time.回复
  65. Healthcare ArticlesAs I web site possessor I believe the content material here is rattling magnificent , appreciate it for your hard work. You should keep it up forever! Best of luck.回复
  66. Business LineExcellent post. I was checking constantly this blog and I am impressed! Very useful information specially the last part 🙂 I care for such information much. I was looking for this certain info for a long time. Thank you and good luck.回复
  67. The BusinessHey There. I found your blog using msn. This is a very well written article. I will make sure to bookmark it and come back to read more of your useful information. Thanks for the post. I will certainly return.回复
  68. IncomeExcellent website. Plenty of useful information here. I¡¦m sending it to some buddies ans also sharing in delicious. And obviously, thank you on your sweat!回复
  69. Online BusinessI must convey my appreciation for your kindness giving support to visitors who really need help on this important theme. Your special dedication to passing the solution all through turned out to be exceptionally important and have consistently made most people like me to attain their targets. Your amazing informative tutorial means a great deal to me and extremely more to my mates. Best wishes; from all of us.回复
  70. Business PlanUsually I don't learn article on blogs, but I would like to say that this write-up very forced me to check out and do so! Your writing style has been surprised me. Thank you, quite nice article.回复
  71. Business InsiderI intended to draft you this tiny word to help say thank you again on the awesome information you've contributed on this page. This has been really seriously open-handed with people like you to provide easily exactly what some people would have offered for sale for an e book to make some money for themselves, most notably given that you could have tried it if you ever desired. Those tactics also acted to become a easy way to understand that some people have the identical desire just like my very own to know way more in terms of this issue. I think there are lots of more pleasant occasions ahead for individuals that scan through your blog.回复
  72. Health StoreI think this is among the most important information for me. And i'm glad reading your article. But wanna remark on some general things, The website style is perfect, the articles is really nice : D. Good job, cheers回复
  73. Business & FinanceWow! This could be one particular of the most beneficial blogs We have ever arrive across on this subject. Basically Magnificent. I am also a specialist in this topic therefore I can understand your hard work.回复
  74. Web DesignF*ckin' awesome issues here. I'm very happy to see your post. Thanks a lot and i'm having a look forward to touch you. Will you please drop me a mail?回复
  75. Home Services MovingI haven¡¦t checked in here for a while as I thought it was getting boring, but the last several posts are good quality so I guess I will add you back to my daily bloglist. You deserve it my friend 🙂回复
  76. Home ServiceHi my family member! I want to say that this post is amazing, great written and include approximately all important infos. I¡¦d like to see extra posts like this .回复
  77. Homeserve USA Septic TankGreat goods from you, man. I've understand your stuff previous to and you're just too magnificent. I really like what you have acquired here, certainly like what you are stating and the way in which you say it. You make it entertaining and you still care for to keep it wise. I cant wait to read far more from you. This is really a great site.回复
  78. quest bars cheapHey there excellent blog! Does running a blog similar to this require a great deal of work? I've very little expertise in coding however I was hoping to start my own blog in the near future. Anyhow, should you have any recommendations or tips for new blog owners please share. I understand this is off subject but I simply wanted to ask. Many thanks!回复
  79. HealthVery interesting details you have remarked, thankyou for posting . "Strength does not come from physical capacity. It comes from an indomitable will." by Mohandas Karamchand Gandhi.回复
  80. quest bars cheapHmm is anyone else experiencing problems with the images on this blog loading? I'm trying to determine if its a problem on my end or if it's the blog. Any feedback would be greatly appreciated.回复
  81. Fashionhello!,I like your writing very a lot! share we keep in touch more about your article on AOL? I need a specialist in this house to solve my problem. May be that is you! Having a look forward to see you.回复
  82. Streaming knowing bros sub indoI simply could not depart your site prior to suggesting that I actually enjoyed the usual information a person supply in your guests? Is gonna be back often to inspect new posts回复
  83. Knowing brothers indonesiaWhat i do not understood is actually how you are no longer really much more well-favored than you may be right now. You're so intelligent. You recognize therefore significantly on the subject of this topic, produced me for my part imagine it from a lot of numerous angles. Its like women and men don't seem to be interested until it¡¦s something to accomplish with Lady gaga! Your individual stuffs outstanding. At all times handle it up!回复
  84. TravelHiya very cool web site!! Man .. Beautiful .. Wonderful .. I will bookmark your website and take the feeds also…I'm happy to find so many useful info right here in the submit, we'd like work out extra strategies in this regard, thanks for sharing.回复
  85. NewsI was studying some of your blog posts on this internet site and I conceive this site is real informative ! Continue posting .回复
  86. Business News ArticlesExcellent goods from you, man. I've understand your stuff previous to and you're just too fantastic. I actually like what you have acquired here, certainly like what you're stating and the way in which you say it. You make it entertaining and you still care for to keep it wise. I can not wait to read much more from you. This is actually a great website.回复
  87. FashionEnjoyed studying this, very good stuff, thanks . "We swallow greedily any lie that flatters us, but we sip little by little at a truth we find bitter." by Denis Diderot.回复
  88. Financial Newsfantastic put up, very informative. I ponder why the other experts of this sector don't understand this. You should proceed your writing. I'm confident, you have a huge readers' base already!回复
  89. Business ChecksGenerally I do not read post on blogs, however I wish to say that this write-up very pressured me to take a look at and do it! Your writing taste has been surprised me. Thank you, quite great post.回复
  90. Small BusinessThank you for the sensible critique. Me & my neighbor were just preparing to do some research about this. We got a grab a book from our area library but I think I learned more from this post. I am very glad to see such excellent information being shared freely out there.回复