Excel相关常识
Excel前言
1.認識Excel
Excel中幾種常用的文件類型：
a.XLS/XLSX 工作簿文件；
b.XLW 工作區(qū)文件；
把一個窗口變成兩個窗口：視圖選項卡——>新建窗口——>全部重排——>垂直并排，點擊保存工作區(qū)，后續(xù)即可直接查看（選擇xlw文件類型）新版的Excel沒有這個功能了
Excel能做什么：數(shù)據(jù)存儲——>數(shù)據(jù)處理——>數(shù)據(jù)分析——>數(shù)據(jù)呈現(xiàn)
敲F1會跳轉(zhuǎn)到幫助
文件——>選項——>高級——>Lotus兼容性勾上，就可以寫公式的時候不寫“=”
注意：平時不要勾！！！
更改工作表標簽顏色
1）交換兩列的順序：
選中整列，按住shift鍵拖拽
2）怎樣到一個表格的最后：
選中一個單元格，在鼠標變成上下左右那個標識時雙擊朝下（就會到表格最后），朝上（到表格開頭）
3）凍結(jié)窗格：
總是以當前單元格在哪進行凍結(jié)（總是凍結(jié)該單元格上方和左側(cè)的單元格）；
（滾動的時候保持第一行不動）視圖——>凍結(jié)窗格（凍結(jié)拆分窗格，可選中多行凍結(jié)）——>凍結(jié)首行
4）按Ctrl 再拖拽就會填充序列
右擊——>以工作日填充
自定義填充：文件——>選項——>高級——>編輯自定義列表
在名稱框中輸入即可選中任意單元區(qū)域
1.分類匯總、數(shù)據(jù)有效性
分類匯總之前需要先排序！！！（把同類的數(shù)據(jù)放在一起）
先排序：
做兩次分類匯總：不勾選替換當前分類匯總！
1）復制匯總后的數(shù)據(jù)：定位——>可見單元格
步驟：先選中數(shù)據(jù)——定位條件——>可見單元格——>ctrl C——>ctrl V
2）使用分類匯總批量合并內(nèi)容相同的單元格
復制——>右擊——>選擇性粘貼——>僅粘貼格式
結(jié)果：
然后順手點擊合并單元格，結(jié)果：
最后刪除分類匯總，這樣就會多一列（合并單元格的這一列）
3）數(shù)據(jù)有效性
自定義中可直接寫公式定義該單元格
2.單元格格式設(shè)置
1）使用"分列"工具
2）數(shù)字格式（Excel日期從1900年開始）
**當Excel中日期是文本格式的時候你這時候修改日期樣式是不會變的！！！**如何處理？使用分列工具，它可以讓數(shù)據(jù)在不同值之間跳轉(zhuǎn)！（什么都不選，直接點擊下一步）
一、數(shù)據(jù)透視表
數(shù)據(jù)透視表：做數(shù)據(jù)匯總統(tǒng)計的工具，選中任意含有數(shù)據(jù)的單元格，插入數(shù)據(jù)透視表。
經(jīng)典數(shù)據(jù)透視表布局：右擊——>數(shù)據(jù)透視表選項——>顯示
1.更改數(shù)據(jù)透視表匯總方式：值字段設(shè)置，雙擊數(shù)據(jù)透視表中匯總的值可返回該值原始數(shù)據(jù)
2.數(shù)據(jù)透視表中的組合
1）日期組合：
注意：表中如果有空格，會導致日期沒法組合（或者必須是同一類型：全是日期或者全是數(shù)值）
選擇任意一個時間，右擊——>組合
2）數(shù)值的組合
點擊任一數(shù)據(jù)——>組合
3.匯總多列數(shù)據(jù)
員工姓名——>值字段設(shè)置——>無，即可取消匯總（使姓名和工號一一對應）
多次添加該字段，即可匯總多列數(shù)據(jù)
4.創(chuàng)建計算字段（即在數(shù)據(jù)透視表中寫公式）
數(shù)據(jù)透視表中寫公式：域，項目合集（計算字段）
右擊——>設(shè)置單元格格式——>百分比；
設(shè)置錯誤值顯示方式：數(shù)據(jù)透視表任一單元格——>數(shù)據(jù)透視表選項——>勾選對于錯誤值顯示
5.生成多張工作表
生成多張工作表（分析，選項，顯示報表篩選頁）
按住shift鍵選中所有的表！！！
二、函數(shù)
&：連字符
<>：不等于
比較運算符的結(jié)果：TRUE FALSE
TRUE 1
FALSE 0
單元格引用：
絕對引用（位置不變） 按F4
相對引用（位置變）
使用定位工具（寫公式時手工選區(qū)域不方便）：
ctrl +Enter 批量填充公式
1.IF函數(shù)：函數(shù)語法：IF(logical_test,[value_if_true],[value_if_false])
參數(shù)：logical_test 可為數(shù)值或者邏輯表達式；
value_if_true：當logical_test 為True時返回的結(jié)果；
value_if_false：當logical_test 為False時返回的結(jié)果
IF嵌套
公式不加雙引號：
套的層次太多考慮使用vlookup函數(shù)
=IF(G6=“A級”,10000,0)+IF(G6=“B級”,9000,0)+IF(G6=“C級”,8000,0)+IF(G6=“D級”,7000,0)+IF(G6=“E級”,6000,0)+IF(G6=“F級”,5000,0)+IF(G6=“G級”,4000,0) 注：沒有嵌套，只是每一個可能都想加起來
使用VLOOKUP:
=IF(G21=“A級”,“一級”,“”)&IF(G21=“B級”,“二級”,“”)&IF(G21=“C級”,“三級”,“”)&IF(G21=“D級”,“四級”,“”)
=IF(ISERROR(D35/C35),0,D35/C35)
=IF(AND(A3=“男”,B3>=60),1000,0)
=IF(OR(AND(B20>60,A20=“男”),AND(B20<40,A20=“女”)),1000,0)
2.COUNTIF()函數(shù)：帶條件的數(shù)數(shù)
COUNT() 數(shù)數(shù)（數(shù)帶有數(shù)字的單元格有多少個）
COUNTIF(range,criteria) 帶條件的數(shù)數(shù)，參數(shù):range,條件區(qū)域；criteria，條件
某個區(qū)域內(nèi)某個銀行卡有幾個：
正確做法：（仍舊有點問題）
注意：需要使用絕對引用
思路：左邊的人在右邊里面有幾個？如果有1個就是體檢過了，0就是沒有體檢
**加顏色：**條件格式（當那個值符合什么條件的時候就把它改為什么顏色）開始——>條件格式——>新建規(guī)則：使用公式確定要設(shè)置格式的單元格
數(shù)據(jù)有效性：在某個區(qū)域里做一個屬性
注意：需要絕對引用
1）COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2):多個條件
參數(shù)：條件區(qū)域1，條件1，條件區(qū)域2，條件2
3.SUMIF()函數(shù)：對滿足條件的單元格求和
SUMIF(range,criteria,[sum_range])
參數(shù)：條件區(qū)域，條件，[求和區(qū)域] 可選
關(guān)于第三參數(shù)簡寫問題的注意事項（sumif ），第三參數(shù)可以簡寫成F1之類的
1）跨區(qū)域求和：第一個參數(shù)和第三個參數(shù)平齊
2）把多個條件組合成一個條件：
或者使用sumifs（多個條件求和），不同的是第一參數(shù)就是求和區(qū)域，后面的參數(shù)是條件！
參數(shù)：求和項，條件區(qū)域1，條件1，條件區(qū)域2，條件2
替代vlookup
數(shù)據(jù)有效性：
4.VLOOKUP函數(shù)：總共四個參數(shù)
lookup_value：要查找的值；
table_array：要在其中查找值的區(qū)域；最左側(cè)不能多選，或者說需要保證最左側(cè)一列和要查找的值那列相同
col_index_num：區(qū)域中包含返回值的列號；
range_lookup：精確匹配或者近似匹配（精確匹配用0或者False指代，近似匹配用1或True指代，參數(shù)省略時默認近似匹配）
如果沒有選取整列，而是選擇的某一個區(qū)域，需要使用絕對引用！
1）基本用法
2）跨表引用(注意表不要來回切換）
3）注意使用連字符和需要使用精確匹配0
使用通配符做精確匹配！！！
4）模糊匹配：覺得表里面沒有還要去找的時候
工作中大部分都是精確匹配！！！找一些數(shù)值區(qū)間的劃分會使用模糊匹配！使用模糊匹配時查找的值要從小到大排序！
5）數(shù)字格式問題：通過數(shù)值找文本
1.把一個數(shù)值連接一個東西Excel會把它處理成文本
2.文本轉(zhuǎn)數(shù)值
把文本*1，文本會變成數(shù)值（或者將它經(jīng)過運算，都會變成數(shù)值）
3.先用數(shù)值找然后再用文本找（或者直接文本轉(zhuǎn)換也行）
如果數(shù)值找不出來，就用文本，否則就用數(shù)值！
6）HLOOKUP:之前一行是一條數(shù)據(jù)，現(xiàn)在一列是一條數(shù)據(jù)
5.Match和Index
1）Vlookup函數(shù)語法
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
2）MATCH(lookup_value,lookup_array,[match_type])
Vlookup缺點： 在使用它時都是使用左側(cè)的數(shù)據(jù)來查詢右側(cè)的某一個數(shù)據(jù)，無法通過右側(cè)的查找左側(cè)的數(shù)據(jù)！！!（有時可以互換AB兩列位置）
使用vlookup的時候做了兩件事：一是查找；二是引用。
**Match只負責找到位置！！！**不負責引用。Match的第一參數(shù)相當于vlookup的第一參數(shù)，第二參數(shù)相當于vlookup的第二參數(shù)的左側(cè)的一半，第三參數(shù)相當于vlookup的第四參數(shù)
3）INDEX(array,row_num,[column_num])
給它一個區(qū)域，到區(qū)域中返回值！：在什么范圍取，取這個范圍的第幾個。
組合兩個函數(shù)：
4）Match+Index與Vlookup函數(shù)比較
數(shù)據(jù)源：
5）使用Match與Vlookup函數(shù)嵌套返回多列結(jié)果
混合引用
與原表結(jié)構(gòu)一致的：
使用match()做：
與原表結(jié)構(gòu)不一致的：
6）認識column函數(shù)：求列號的
括號里什么都不寫都可以，這時候它會返回當前所在單元格的列號
按住Alt鍵拖，使圖片充滿單元格！
6.郵件合并（Word中）
1）每頁顯示多條記錄
郵件——>開始郵件合并——>郵件合并分部向?qū)А?gt;選擇目錄——>選擇使用當前文檔
7.日期函數(shù)
實際是天數(shù)！（自1900年起）
datedif（）函數(shù)：
第一參數(shù)：起始日期；
第二參數(shù)：終止日期；
第三參數(shù)：返回的值（年或月或者日）
YM:去掉年份，算月數(shù)；（忽略日期中的天和年份）
MD:去掉月份，算天數(shù)
WEEKNUM():返回第幾周，兩個參數(shù)：第二個參數(shù)，把星期幾做為一周的第一天。
WEEKDAY():算周幾
把文本*1會變成數(shù)字！，然后設(shè)置單元格格式選擇日期即可
8.條件格式與公式
切片器：
可以看成篩選按鈕或者分類按鈕
回到最初的時候：清除篩選器
先做大范圍，再做小范圍：（和if函數(shù)相反）
自定義條件格式：
9.簡單文本函數(shù)
1）從中間開始取：mid()
先取17位，然后再獲取最后一位數(shù)：
2）FIND():
返回一個字符串在另一個字符串中出現(xiàn)的起始位置
3）LEN(),LENB() 分別求字符和求字節(jié)
通過文本處理函數(shù)處理的數(shù)據(jù)一定是文本！需要先將文本轉(zhuǎn)成數(shù)值
2）使用MOD函數(shù)與文本函數(shù)，提取身份證號性別信息
10.數(shù)學函數(shù)
ROUND():參數(shù)1：四舍五入的值；參數(shù)二：小數(shù)點后的位數(shù)
ROUNDUP()：進位
ROUNDDOWN()：舍棄
INT():直接取整
MOD():求余數(shù)
轉(zhuǎn)置：
ROW():求行數(shù)
11.VLOOKUP函數(shù)與數(shù)組
sumif（）：參數(shù)：條件區(qū)域，條件，求和區(qū)域
SUMIFS()參數(shù):求和區(qū)域，條件區(qū)域1，條件1，條件區(qū)域2，條件2
使用VLOOKUP，作多條件查詢用啥呢？VLOOKUPS？沒有！
如果是數(shù)組公式，按住Ctrl+Shift+Enter
注意：以上選的是一個區(qū)域，需要使用絕對引用！
可以使用SUMPRODUCT()，就不用三鍵了直接按回車即可
復習VLOOKUP()
LOOKUP()沒有第四參數(shù)（精確匹配或者模糊匹配）
它采用的是模糊匹配，需要把它變成精確匹配
第一個參數(shù)寫0或者1都行。
使用LOOKUP做精確匹配
注意：上圖使用Ctrl+Shift+Enter
12.indirect函數(shù)
間接引用
1）跨表引用
做多個表的匯總
首先使用文本描述的方式，再使用INDIRECT()
跨表引用（順序不同）
需要使用混合引用！
2）制作二級下拉列表
數(shù)據(jù)驗證——序列
制作二級下拉列表
為什么使用indirect(F1)：因為之前已經(jīng)將吉林省下面的所有城市定義成了吉林省
三、圖表基礎(chǔ)
動態(tài)圖表
在系列創(chuàng)建中是單列引用的！
總結(jié)
- 上一篇： 量化风控全流程
- 下一篇： 第7章 PCA与梯度上升法