Excel 有哪些可能需要熟練掌握而很多人不會的酷炫技能?

========排版篇========

  • 給他人發送excel前,請盡量將游標定位在需要他人首先閱覽的位置,例如Home位置(A1),例如結論sheet,長表盡量將位置定位到最頂端

  • 有必要的時候請凍結首行;沒必要但可追究的內容,可以隱藏處理

  • 行標題、列標題加粗,適當處理文字顏色、填充顏色,利人利己,可參見:Excel 中的顏色要怎麼搭配,視覺上更能接受且區分度高?(知乎搜索此標題) - 設計

  • 佔用空間比較小的表格,可以放置在左上角,但留空A列和1行,並給表格加上合適的框線,觀感很不錯哦~

  • 同類型數據的行高、列寬、字體、字型大小,求你盡量一致,非要逼死強迫症嗎!

  • 定義好比較標準的格式,例如百分比預留幾位小數,手機號的列寬設置足夠,時間顯示盡量本土化...

  • 不要設置其他電腦沒有的字體,除非這個表格就在這一台電腦使用...

========操作篇========

  • Alt+Enter在表格內換行

  • Ctrl+Shift+上/下,選擇該列所有數據,當然加上左右可選擇多列

  • Ctrl+上/下,跳至表格最下方

  • Ctrl+C/V,不僅僅複製表格內容,也可以複製格式和公式!

  • Ctrl+D/R,複製上行數據/左列數據

  • 還有個很好用的單元格格式轉換,推薦大家用熟

(有點不清晰...當初偷懶直接把圖片截到印象筆記的...)

  • Ctrl+F/H的查找、替換,點擊「選項」,可以替換某種格式等等,另一片天地有木有!

  • F4,對,你沒看錯,就是F4!重複上一步操作,比如,插入行、設置格式等等頻繁的操作,F4簡直逆天!

  • 『(分號後面那個) 比如輸入網址的時候,一般輸入完會自動變為超鏈接,在網址前輸入』就解決咯

  • 複製,選擇性粘貼裡面有幾個非常好用的——僅值,轉置(個人推薦用transpose公式)

  • 公式裡面切換絕對引用,直接點選目標,按F4輪流切換,例如A1,$A$1,$A1,A$1

========公式篇========

  • if、countif、sumif、countifs、sumifs,這幾個一起學,用於條件計數、條件求和

  • max、min、large,這幾個一起,用於簡單的數據分析

  • rand、randbetween,這倆一起,用於生成隨機數,也可以用於生成隨機密碼(用rand配合char可生成中英文大小寫隨機的)

  • 定位類型的函數:MID、SEARCH、LEN、LEFT、RIGHT一起學吧,簡單但異常實用

  • 四捨五入個人偏好用round函數,舉個簡單例子,一列數據,2.04、2.03並求和,顯示保留1位小數,你會在界面上看到2.0、2.0,求和卻是4.1,表格列印出來會比較讓人難理解

  • subtotal:用於對過濾後的數據進行匯總分析

  • sumproduct:返回一個區域的乘積之和,不用A1*B1之後再下拉再求和

  • Vlookup函數,這個不多說了,神器;另外推薦lookup函數:LOOKUP(1,0/(條件),查找數組或區域)

  • offset函數,常用於配合其他函數使用,例如想將10*20的表中的每行複製成3行按原順序變成30行:=OFFSET($A$1,INT((ROW(A1)-1)/3),COLUMN(A1)-1,1,1) 下拉,由於不用到列,所以等同於=OFFSET($A$1,INT((ROW(A1)-1)/3),0),我當初是這麼做筆記的....:=(A1,向下偏移(向下取整(行數-1)/3),向右偏移0)

  • text,例如19880110 text(A1,"0-00-00"),轉為1988-01-10,用法很多

  • weekday,讓你做時間計劃表什麼的時候,把日期轉為「星期X」

  • column(目標單元格),返回目標單元格所在列數,有時候真的很好用...

  • transpose(目標區域),神奇的轉置,把行變成列,把列變成行...

  • &,可在目標單元格後面增加某些字元,偶爾用(我這種強迫患者用的是concatenate公式,我特么有病!)

  • 數組,雖然複雜,但是有的公式配上數組簡直爽爆

  • 多百度,例如曾經碰到一個難題,把X分X秒,轉為X秒,例如172分52秒,百度半天得到的公式:=IF( IFERROR( FIND( "分", $E2 ), 0) > 0, LEFT( $E2, FIND( "分", $E2 ) - 1 ) * 60 + IFERROR( MID( $E2, FIND( "分",$E2 ) + 1, FIND( "秒", $E2 ) - FIND( "分", $E2 ) - 1 ), 0 ), LEFT( $E2, FIND( "秒", $E2 ) - 1 ) * 1 ) 度娘很厲害的

========圖表篇========

  • 不同的場景請用不同的圖,轉個非常精髓的圖:

  • 數據透析表、數據透析圖,嗯嗯,推薦的人太多了...

  • 圖表設計——布局,靈活運用好多類數據時的「次坐標軸」

  • 選擇數據——右鍵——更改圖標類型,靈活在一張表上結合起來柱狀圖和折線圖

========技巧篇========

  • 數據——分列,將列內的數據拆分成多列,比如「XXX省XXX市」,拆成省、市兩列,「XX小時XX分鐘」拆成時、分兩列,可以按照寬度、文本、標點等作為界定進行拆分,非常多的場景會使用到,請優先學會...

  • 如果你不是靠excel吃飯,請不用那麼geek,而是學會excel的邏輯——配合簡單的公式、排序、替換、if等全局操作能得出的結果,不一定非要用一個長公式然後下拉,舉例:

如何將無規律的一列上下翻轉?

——創建一列,標上1、2、3……,下拉,以該列為主排序,改升序為降序,擴展目標列,得到結果,之後可以刪掉創建的輔助排序列

如何將目標區域的每一行數據下面插入一條空行?

——創建一列,標上1、2、3……,下拉,下面空白行標上1.5、2.5、3.5……下拉,同理排序~Tada~

  • 條件格式——突出顯示單元格規則,裡面的「重複值」,在實時錄入和檢查標記時很實用

  • 在條件允許的情況下,升級到office 2013吧,excel 2013比2010好到爆啊!比如新增的sumifs、averageifs等多條件if,比如選擇一個區域,右下角小標「快速分析」自動生成數據條、色階、柱形圖、匯總圖、透視表、折線圖等等啊,秒中出啊有木有!(誒,好像哪裡不對的樣子)

========插件篇========

  • Power Map :在線地圖+在線演示+製作視頻,隨便來個中國壕熱力圖:

當然,也有柱狀圖:

  • Power View:帶可視化交互效果的圖表,很適合演示

默認配色就很不錯,而且演示的時候點擊時會直接按你點擊的類型幫你顯示對應的數據(例如上面的堆積柱狀圖)

  • Power Query:這個用法很多,我主要用於以下兩點:

1. 在線Web抓取:不需要學會某個編程語言也能爬蟲+分析一些簡單數據,隨便舉個例子輸入新浪股票的網址,它自動幫忙抓取到N個表,我隨便打開一個:

2. 連接資料庫:不需要學會SQL語法也能查詢+分析資料庫內的數據,這個就不方便截圖了...

打比方說,常見的 select * from ... where xxx = xxx and xxx>xxx group by xxx這種sql語法查詢的內容,可以在Power Query中直接通過點擊、篩選等操作就列出來

  • 各種excel工具箱,這個不多介紹了,不常用,也就不打廣告了,但是挺適合部分長期使用excel的職場人士使用

  • SmartArt也是一大神器,我終於不用在Ai或者PPT上作圖再粘過來了...

========其它篇========

不會寫宏沒關係,要懂得怎麼使用別人的宏(自行百度「excel宏大全」吧~),怎麼保存xlsm,怎麼錄製宏。當你把機械化的一套操作通過錄製宏實現,並用xlsm配合auto_open自動操作,眼看錶格自動化操作,在兩秒內給你返回原來每天固定要做十幾分鐘的數據分析結果時,那個雞皮疙瘩(蔡健雅腔調...)

  • 有時間推薦泡泡excel的論壇,excelhome什麼的,神人太多了....

  • 非常推薦給初學者、中階以及「中介」的視頻:文庫課程,哪怕前幾個視頻,看標題簡單到爆,但中間真的有很多操作上的亮點不為普通人自學所知~

  • excel滿足不了你,又懂編程,想秀逼格的,請右轉百度 SPSS

出題的分割線必須華麗

額,我都寫那麼多了,出個題又不會死——

【A列】

a

a

a

a

b

b

b

c

c

d

d

d

d

e

……

……

……

每行僅一個數據,無法準確知道有多少個a,多少個b,多少個c……

請【僅用一個公式】統計出——【A列中不同單元格的個數】,比如a、b、c、d,算4個

請注意,只允許公式,其他操作均不允許,因為這個題不是為了結果,而是考excel邏輯的...

(01/30 :擦,這麼快就被解答,說好的面子呢!)

【後記】

1. 關於答案

excel是一個很龐大很完整的「系統」,各行各業用處定然不盡相同,所以答案肯定仁者見仁智者見智,我也只是拋磚引玉,說說自己在個人工作上的經驗

2. 關於疑問求解

excel里的幫助(F1)非常之有用,輸入關鍵詞就會提供給你需要的信息,請常用!個人推薦是——基礎操作找百度,公式函數找F1,偏具體的需求找excelhome

3. 關於學習與書籍

引用 @諶斌回答的 如何快速有效地提高 Excel 技能水平?——

我覺得提高Excel水平最重要的是某種「刺激」:

你需要交一個報告;

你需要做一個分布圖;

你需要做數據去重;

你需要做數據排序;

你需要做數據統計;

這種「刺激」唯一不能是:你想學Excel。個人非常感同身受,我所有的學習均是受上面所提的各種「刺激」,所以假如讓我推薦學習excel的書籍,我怕誤人子弟...不過,我個人還是比較傾向在有一定基礎之後補充完善的乾貨...

小夥伴們,5哥搜集的酷炫技能你們都掌握了沒,記得收藏哦,下次不會方便查閱哦

作者:羅翔 來源:知乎 本文經作者授權發表。


推薦閱讀:

金融中都有哪些必須熟練掌握的 Excel 公式?都有哪些技巧和心得?
Excel中數字輸好了,怎樣在數字後面批量添加「元」字?
VLOOKUP函數怎麼用?
excel怎麼輸入帶圈圈的數字?
Excel 數組公式應用及實例介紹

TAG:技能 | 酷炫 | Excel |