数据分析学习 excel的骚操作合集(则秀excel课程笔记)
文章目錄
- 前言
- 一、自動填充
- 二、選取區域
- 三、復制與粘貼
- 四、公式與函數
- 五、輸入公式
- 六、快速求和
- 七、絕對引用
- 八、if函數
- 九、sumif,averageif函數
- 十、定義名稱
- 十一、數字與數值
- 十二、數組計算
- 十三、日期
- 十四、日期函數
- 十五、格式數字
- 十六、邊框
- 十七、插入圖片
- 十八、格式刷
- 十九、主題
- 二十、樣式
- 二十一、凍結拆分窗格
- 二十二、快速實現取消隱藏
- 二十三、分組功能
- 二十四、自定義視圖
- 二十五、多視圖瀏覽
- 二十六、管理工作表
- 二十七、分列與連接
- 二十八、排序和篩選
- 二十九、分類匯總
- 三十、快速實現合并單元格填充
- 三十一、自定義圖形的圖片批注
- 三十二、表格
- 三十三、條件格式
- 三十四、篩選之后的復制粘貼
- 三十五、模擬分析
- 三十六、數據透視表
- 格式
- 本質上
- 計算字段和計算項
- 三十七、打印
- 三十七、頁眉頁腳
- 總結
前言
目前在自學數據分析的工具,excel的一些功能是之前沒接觸過的,這里做一下整理。
一、自動填充
選中文本之后,ctrl+delete可以刪除光標后的所有內容。
ctrl+回車可以實現輸入完成后仍然選中當前的框。
每一個框的右下角都有一個自動填充,選中之后拉開可以實現自動填充。
在自動填充選項中可以選擇復制選項,填充序列等。
二、選取區域
ctrl+→,ctrl+←,ctrl+↑,ctrl+↓都可以實現直接選取最右/左/上/下的單元格。
選取整個區域,若為整張表,直接ctrl+a即可;若是自由選擇,先選擇左上角,再按住shift選擇右下角,即可選取。
三、復制與粘貼
復制與粘貼部分需要注意一下粘貼選項,在開始的左上角可以看到,這里面提供了大量的粘貼選項,包括保留格式,粘貼為圖片等等。
四、公式與函數
公式可以清晰地看到運算過程,而函數只是一個模塊.
在列索引上方可以看到fx,點擊fx可以打開查看所有函數。
五、輸入公式
輸入公式之后,點擊框的右下角,可以實現公式的復制,但是excel會自動更改引用的框,這研究可以實現智能的填充,十分實用。
如果不放心,可以點擊公式——公式審核——顯示公式進行查看
六、快速求和
選中一大片區域,開始——查找與選擇——定位條件——定位為空值
這樣就可以實現所有空值的選定,這時候在運行自動求和指令,可以實現大量數據的求和操作
七、絕對引用
對一個小數進行操作的時候,可以選擇數字列中的各種選項,這樣就可以實現百分比,增加小數點后位數,減少小數點后位數等操作。
若要實現分子相對引用,分母絕對引用,舉例H7/H13,那這時我們就要修改為H7/$H$13,這樣就可以實現拖動框的右下角方便操作了。
八、if函數
if(A1>=1,$B1,1,1,C$1)
若A1這個值大于等于1,返回B1,否則返回C1,利用智能填充,可以實現高效操作。同樣的,我可以把返回值替換。
if(A1>=1,“不錯”,“還行”)
這樣也是行得通的。
九、sumif,averageif函數
sumif()
第一個參數是條件計算的區域,一般是需要絕對引用的。
第二個參數是你要計算的類別,因為我們還需要向下拖動公式,所以我們行相對引,列絕對引用。
第三個參數是實際求和的區域,因為要橫向拖動公式,所以我們行絕對引用,列相對引用。
如果我們想要將sumif直接替換為averageif,可以先選中需要替換的區域,直接按ctrl+f,替換功能即可。
十、定義名稱
選中表格中的一個區域后,點擊左上角的名稱,可以自行命名為“阿巴阿巴”,這時候如果我再使用sum函數,就可以直接寫成sum(阿巴阿巴),就可以實現框選區域的求和了。
十一、數字與數值
excel只能處理15位以下的數字,超過的部分會寫成00000,我們如果要輸入一個數值,但是不把他當數字處理,這樣是可以輸入很長的數字的,一般的方法是,開始菜單的數字欄里面可以進行勾選為文本格式,或者直接在很長的數字前加 ’ 號,也可以實現。
同樣的,如果excel表中全都是文本類型的數字,我們無法進行求和,這時候就可以,復制一個空白單元格,然后選中所有的文本型數字,再點擊選擇性粘貼,在運算里面點擊運算中的任意一個(加,減都可以),再點確認,這時候數字就都變成可以運算的數字了。
十二、數組計算
很強大的功能,先框選一個區域,然后輸入函數,例如
=(
選中同樣高度的一列數據,乘以另一列數據,這時候ctrl+shift+回車可以實現在框選的區域進行求和
除此之外,勾選一個格,輸入
=sum(
再選擇一列數據乘以另一列數據,這樣就可以實現兩列數分別相乘再相加的運算,省掉了之前需要先計算乘積在計算求和的過程。
十三、日期
日期可以使用兩個單元格相減實現之前的日期計算,但是注意要更改格式,如果目標格為日期格式現實就會出錯,需要改為常規。
十四、日期函數
當天日期快捷輸入:ctrl+;
當天日期輸入函數:=today()
當天時間快捷輸入:ctrl+shift+;
十五、格式數字
開始菜單下面,數字欄,里面有很多可以自定義的格式,例如貨幣,年月日的特定表示方法。
舉例來說,我想把表格中的0都顯示為-方便我的瀏覽,這時候我就可以全選所有表格,然后在數字欄中選擇自定義,里面有默認的將0轉化為-,這樣就可以實現方便我們的瀏覽。
十六、邊框
邊框可以在邊框-其他邊框中進行詳細編輯,需要先設置顏色,再進行邊框的添加。
在調整網格的寬度高度的時候,在光標變成調整的時候雙擊,就可以實現系統推薦的寬度更改。
十七、插入圖片
插入圖片后可以選擇圖片的格式中的顏色,選擇透明,并且把白色的背景刷成透明色,這樣可以實現不對網格進行遮擋。
插入形狀的時候按shift是一種方式,按住ctrl是吸附。
十八、格式刷
格式刷雙擊可以多次刷,不要再傻乎乎的一次一次框選一次一次刷了!
十九、主題
當我們使用多個來自不同文件的圖表時,會出現主題不統一,視覺上很亂的情況,這時候我們點擊 頁面布局-主題,選擇一個主題應用,就可以實現統一主題了。
二十、樣式
為某一個單元格設置樣式的時候使用樣式工具欄進行操作
若下次需要將某個樣式的單元格進行修改的話,直接更改樣式的內容,所有應用這個樣式的單元格都會被更改,十分方便。
除此之外,我們還可以設置好我們需要的樣式,然后在下次做類似的表的時候直接使用原來的藍圖樣式就可以了。
但是新建一個excel表之后,往往以前的樣式就不見了,這時候我們打開舊表,選擇合并單元格樣式,即可。
同理,word也應該使用這種方法,fuck,我要是早知道這個的話能節省很多時間。
二十一、凍結拆分窗格
使用視圖-凍結窗格命令,可以實現在翻動excel表的時候仍然看到行索引或者列索引。
注意,如果想同時凍結行和列的話,需要將想要凍結的行和想要凍結的列交叉格的右下角單元格選中,再點擊凍結窗格。
拆分可以實現距離很遠的兩行數據的對比,只要按這個小橫線,就可以實現拆分操作。雙擊拆分出現的分割線,分割線就會不見了。
二十二、快速實現取消隱藏
隱藏實際上就是將你選擇的行,列寬度設置為0,取消隱藏的時候我們只要選擇一個區域,雙擊分割行或列,就可以取消隱藏了。
二十三、分組功能
如果想實現一些行或列的隱藏,只需要選中行或列,選擇數據-分組,這時候就可以自由打開或隱藏了。
需要注意的是,分組效果會影響打印效果。
二十四、自定義視圖
選擇視圖-縮放到選定區域,可以實現視圖只顯示你選擇的區域,十分方便操作。
使用視圖-自定義視圖,可以實現各個畫面的快速切換操作,這樣可以實現高效地調用某部分內容的表,十分方便。
二十五、多視圖瀏覽
我們現在有兩個excel,一個里面有一個sheet,里一個有兩個sheet,我們如何實現把它們放在一起進行比較呢?
首先,為了區分兩個sheet的表格,在其中一個sheet上選擇 視圖-新建窗口
這時候點擊右上角的
這時候我們就實現了平鋪所有的表格,但是調整起來很麻煩,我們再選擇
視圖-全部重排,選擇默認的平鋪,
就完成了,excel會自動將較大的部分占用較大的空間。
二十六、管理工作表
可以按住ctrl選擇兩個sheet,這時候就會變成一個工作組的概念,這時候修改的單元格會在兩個sheet中同時修改,在修改同類表格時大大提升工作效率。
二十七、分列與連接
當我們想把一個單元格的內容分為很多列時,需要點擊數據-數據工具-分列
舉例來說,我現在想把下面一列按照中英文名稱分列
我首先使用ctrl+f中的替換功能將所有的"(“替換成”-(",結果如下
再點擊分列,選擇里面的
完成分割。
進行連接的時候,只需要選擇要鏈接的單元格,在中間以&進行連接就可以了。
二十八、排序和篩選
位置在 數據-排序和篩選 里面,注意排序可以以多個條件進行排序,十分方便,篩選功能主要是用打開篩選之后的索引上面的小三角進行操作的,十分簡單,打開就會用了。
二十九、分類匯總
位置在數據-分級顯示中,
這個分類匯總的意思就是以地區為分類,計算評分的平均值。效果如下
三十、快速實現合并單元格填充
https://www.bilibili.com/video/BV1qs41157Lr?p=42
三十一、自定義圖形的圖片批注
首先點擊一個單元格,選擇新建批注
然后在批注的邊框上點擊右鍵,選擇設置批注格式
選擇顏色與線條-顏色,然后選擇填充效果-圖片,導入一張喜歡的圖片即可。
然后任意添加一個形狀
點擊形狀時候,會出現這個工具欄
在里面的編輯形狀選項上,點擊右鍵,選擇添加到快速訪問工具欄。
這時我們再編輯批注,就可以實現更改形狀了
如下所示
三十二、表格
excel支持把已有數據做成一個表格,選中數據,在插入中選擇表格即可,這給了我們做很多方便的操作的可能。
三十三、條件格式
在開始-樣式中可以選擇條件格式
選擇一些數據,就可以設置條件格式了,例如下面我將評分大于4.5的重點顯示
在這時候,我直接進行更改數值,都是可以實現即時更改格式的。
想要清除格式的時候,直接點擊清楚規則就可以了。
三十四、篩選之后的復制粘貼
選擇一片區域,這片區域可能有篩選的內容,或隱藏了行和列,如果直接復制,隱藏的內容也會被復制走,但是我們可以
一、選定區域后按alt+;
二、選定區域后按ctrl+G,選擇定位條件,選擇可見單元格
這兩種操作過后,都會實現僅僅復制我們需要的內容。
三十五、模擬分析
主要是模擬分析表這個內容,具體在視頻里有,日常用到的比較少。
https://www.bilibili.com/video/BV1qs41157Lr?p=49
三十六、數據透視表
格式
首先要注意如果想制作數據透視表,需要嚴格遵守格式的需求,例如:不能有合并單元格,數字列必須是數字格式,不能是文本格式…
除此之外,還要理解一些名詞
字段:每一列
字段標題:就是每一列的標題
記錄:表中除去標題的每一行都是一行記錄
本質上
本質上就是多去嘗試這些組合,這里的原理和tableau是一樣的
合理使用創建組功能,實現更復雜的數據顯示。
雙擊透視表標題,可以選擇計算不同的值。
除此之外,還可以通過將數值信息插入到行標簽和數值中,并且設置分組,這研究可以統計數值的個數,以及某一區段該數值的個數。
計算字段和計算項
計算字段用于增加新列
計算項是用于增加新行
單擊行標簽中的任意一項,在數據透視表工具-選項中,找到域、項目和集,點擊就可以創建計算字段或者計算項了。一般會用于用公式處理特定的數據透視表中的內容。
三十七、打印
打印時候合理運用分頁預覽
并且適當的添加分頁符,可以使打印效果更好。
使用打印標題來實現多個紙張的打印都有標題
使用數據-分級顯示-分類匯總,勾選其中的每組數據分頁,可以實現快速分組,提高打印效率。
三十七、頁眉頁腳
設置頁眉頁腳最好的方法是在
這個選項中進行設置,所見即所得,非常適合后期打印。
總結
騷操作還有很多,應該會持續更新,之后再做系統的整理。
總結
以上是生活随笔為你收集整理的数据分析学习 excel的骚操作合集(则秀excel课程笔记)的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: Excel软件的使用指南
- 下一篇: 计算机文件打开方式这么还原,dat文件还