Excel可以存儲10w行，100M的數(shù)據(jù)，作為數(shù)據(jù)分析esp三件套的大哥，其中的知識點較為雜亂，因此xue微整理了一下常用的知識點，簡單記錄下~

1、導(dǎo)入數(shù)據(jù)

數(shù)據(jù) → 新建查詢 → 從文件（選擇對應(yīng)格式的文件即可）

保存后不會改變原有數(shù)據(jù)（若原有表格刪除，Excel也會刪除；若原有表格更新，可表設(shè)計刷新 Excel 表格）

2、文件加密

文件菜單 → 信息 → 保護工作簿 → 用密碼加密 → 設(shè)置密碼 → 確定

3、保護單元格

1）保護工作表

審閱 → 工作表保護

2）允許編輯部分

第一步：選中?→ Ctrl + 1 調(diào)出單元格格式設(shè)置 → 保護 →?鎖定前面的勾選去掉?→ 確定

第二步：審閱?→ 選擇保護工作表（默認選項直接確定）

4、凍結(jié)窗格

鎖定標題行：視圖 → 窗口 → 凍結(jié)窗格?→?凍結(jié)首行

鎖定首列：視圖 → 窗口 → 凍結(jié)窗格 → 凍結(jié)首列

鎖定定位單元格行列：視圖 → 窗口 → 凍結(jié)窗格 → 凍結(jié)窗格

5、數(shù)據(jù)格式（單元格格式）

5.1? 類型

文本格式：默認靠左

數(shù)值格式：默認靠右

日期格式：默認靠右（其本質(zhì)是數(shù)值格式）

5.2? 格式設(shè)置

1）保留原格式增加單位

單元格格式?→ 數(shù)字選項卡 →?自定義?→?類型輸入【##"單位"】

2）數(shù)值區(qū)間顏色設(shè)置

單元格格式?→ 數(shù)字選項卡 →?自定義?→?類型輸入【[顏色][規(guī)則]】

例：將 ≥5 設(shè)置藍色，＜5 設(shè)置紅色輸入為：[藍色][>=5];[紅色][<5]

3）數(shù)值保留兩位小數(shù)且負值標紅

單元格格式?→ 數(shù)字選項卡 →?自定義?→?類型輸入【00.00;[紅色]"-"00】

5.3? 格式轉(zhuǎn)換

1）文本批量去除引用的單引號'

使用空單元格的格式批量格式刷

2）在單元格內(nèi)輸入01

先輸入一個英文引號'，再輸入數(shù)字，如【'01】

3）長數(shù)字按科學計數(shù)法顯示

① 在數(shù)字最前輸入英文引號變?yōu)槲谋靖袷?，如?i>'123123123123123】

② 錄入之前將單元格格式設(shè)置為文本類型

4）以文本儲存的數(shù)值轉(zhuǎn)換為數(shù)值

① 輸入公式：【=1*文本】

② 輸入公式：【=value(文本)】

5）TRUE/FALSE轉(zhuǎn)換為數(shù)值

輸入公式：【=1*選中】

6）文本格式日期轉(zhuǎn)換為日期

① 標準日期格式的文本：輸入公式【=value(文本)】→ 單元格格式選擇為日期

② 不標準的格式如230101：設(shè)置格式?→ 自定義?→ 類型輸入【2000-00-00】即可將格式轉(zhuǎn)換為2023-01-01

7）數(shù)字固定位數(shù)

在選定區(qū)域右鍵 → 設(shè)置單元格格式 → 數(shù)字選項卡 → 自定義 → 類型輸入【n個0】，即可將選中區(qū)域的數(shù)字改為n位數(shù)字，不足位的數(shù)字左邊補0

8）隱藏單元格內(nèi)所有的值

選中單元格右鍵 → 設(shè)置單元格格式 → 數(shù)字選項卡 → 自定義 → 類型輸入【;;;】→ 確定

9）公式復(fù)制時只粘貼值，不粘貼公式

復(fù)制 → 目標單元格右鍵 → 選擇性粘貼 → 數(shù)值 → 確認

6、數(shù)據(jù)類型

錯誤值：

#VALUE!（文本與數(shù)值進行運算）

DIV/0!（兩數(shù)相除，分母為零）

#NAME!（公式名稱錯誤）

#N/A（查找值不存在）

#REF!（所引用單元格被刪除）

#NULL!（兩數(shù)組無交集）

7、數(shù)據(jù)驗證

1）填寫單元格時設(shè)置下拉菜單

提前在其他的空白單元格內(nèi)輸入選項

選中單元格范圍 → 數(shù)據(jù)菜單 → 數(shù)據(jù)驗證 →?設(shè)置選項卡 → 驗證條件【允許選序列，來源選范圍】→ 確定

2）圈示無效數(shù)據(jù)

選中 → 數(shù)據(jù) →?數(shù)據(jù)驗證 →?設(shè)置/圈示無效數(shù)據(jù)

3）防止重復(fù)錄入數(shù)據(jù)

選取防止重復(fù)錄入的區(qū)域 → 數(shù)據(jù) → 數(shù)據(jù)驗證 → 設(shè)置選項卡 →?驗證條件【允許選擇自定義】?→ 輸入公式【= COUNTIF(A:A,A1) = 1】，之后再輸入重復(fù)值會有報錯（其中公式的意思是A列中等于A1的個數(shù)為1）

4）禁止輸入?yún)^(qū)域設(shè)置

選中區(qū)域 →?數(shù)據(jù)驗證 →?自定義 →?公式【0】

8、條件格式

開始 → 樣式 → 條件格式

1）突出顯示

突出顯示重復(fù)值，數(shù)值范圍值，包含文本值等

2）最前/最后規(guī)則

前N、后N的數(shù)值

3）數(shù)據(jù)條

以帶顏色的數(shù)據(jù)條展示數(shù)值的大小

9、排序篩選

開始 → 編輯 → 排序與篩選

快捷篩選：Ctrl + Shift + L 進入篩選模式

高級篩選

注意：條件區(qū)域選擇必須帶表頭，同一行表示且，不同行表示或

1）篩選出不重復(fù)值列表

數(shù)據(jù) → 排序和篩選?→?高級?→?勾選將篩選結(jié)果復(fù)制到其他位置?→?列表區(qū)【需篩選的列】?→?條件區(qū)域【空即可】?→?復(fù)制到【復(fù)制目標區(qū)域】?→?勾選選擇不重復(fù)的記錄?→?確定

2）篩選多條件數(shù)據(jù)-且：篩選出部門為一車間，科目為郵寄費的數(shù)據(jù)

復(fù)制條件?→?數(shù)據(jù)?→?排序和篩選?→?高級?→?勾選將篩選結(jié)果復(fù)制到其他位置?→?列表區(qū)【全部數(shù)據(jù)區(qū)域】?→?條件區(qū)域【條件區(qū)域】?→?復(fù)制到【復(fù)制目標區(qū)域】?→?確定

3）篩選多條件數(shù)據(jù)-或：篩選出部門為一車間，或科目為郵寄費的數(shù)據(jù)

4）篩選多條件數(shù)據(jù)-或：篩選一車間或發(fā)生額大于3000的二車間或發(fā)生額大于10000的數(shù)據(jù)

5）篩選成本大于金額的數(shù)據(jù)

添加輔助，公式【=G3>F3】

注意：名稱不能和表頭一樣

10、查找替換

開始 → 編輯 → 查找和選擇

1）快速查找

Ctrl + F 彈出查找窗口，輸入查找內(nèi)容，再點擊【選項】可以更改范圍、查找范圍等選項，可以查找批注、公式或者單元格本身內(nèi)容

2）快速定位

Ctrl + G 彈出定位窗口，可以通過定位條件來進行定位

例：刪除空白行

選擇需要刪除的區(qū)域 → Ctrl + G → 定位條件 → 選擇【空值】 → 刪除 → 刪除工作表行

3）快速替換

Ctrl + H 跳出替換窗口，填寫查找內(nèi)容與替換內(nèi)容即可

例：小數(shù)取整數(shù)（不進行四舍五入，使用替換實現(xiàn)）

選擇需要更改的區(qū)域 →?Ctrl + H?→ 查找內(nèi)容處輸入【.*】，替換為處不需要填寫，然后點擊全部調(diào)換，即可去掉數(shù)據(jù)小數(shù)點后面的數(shù)字

11、分列

例：有 A-001 這樣一列數(shù)據(jù)，需要把數(shù)字/字母單獨成一列

① 選中數(shù)據(jù) → 數(shù)據(jù)工具 → 分列 → 分隔符號 → 分隔符號其他輸入【分隔的自定義符號，這里為 - 】?→ 完成

② 手動填好第一個，然后點數(shù)據(jù)菜單 → 快速填充（快捷鍵：Ctrl+E），即可智能填充

12、分類匯總

數(shù)據(jù) → 分級顯示 → 分類匯總

13、快速計算

1）整列乘 1.5

空白單元格輸入1.5 → 復(fù)制 →?選中數(shù)值部分 →?右鍵 →?選擇性粘貼 → 運算選擇【乘】→ 確定

2）將金額轉(zhuǎn)換為萬元顯示

在一個空白單元格內(nèi)輸入10000，設(shè)置為數(shù)字格式 ，復(fù)制

選則要修改的金額 → 右鍵 → 選擇性粘貼 → 運算選擇【除】→ 確定

14、快速填充

1）序列填充

填充柄【鼠標右鍵下拉】→ 選擇填充方式【序列】

2）批量填充

Ctrl + Enter：多個單元格填充相同的數(shù)據(jù)，可以選中任意區(qū)域，先在一個單元格內(nèi)輸入，然后按下快捷鍵，即可在選中區(qū)域填入相同的數(shù)據(jù)

3）取消合并后，快速填充空單元格內(nèi)容

選中區(qū)域 → 定位條件 → 空值 → 輸入公式【=↑】Ctrl + 回車

4）快速輸入當前日期和時間

① 輸入當前日期：【ctrl + ;?】

② 輸入當前時間：【ctrl + shift + ;?】

5）按月補充日期

第一個單元格是日期時，正常拖動數(shù)據(jù)，下面的單元格不會順延，若想讓日期順延，可以

① 拖動一兩格后，選右下角的以月填充后再繼續(xù)拖動

② 全拖之后再選擇以月填充

6）快速求和

Alt + =：選中數(shù)據(jù)區(qū)域及空白區(qū)域（用于填寫求和后的數(shù)據(jù)）然后 Alt + = 即可將求和結(jié)果填入空白單元格內(nèi)

15、快捷操作

1）全選表格內(nèi)容

Ctrl + A：定位數(shù)據(jù)表中的任一單元格，然后?Ctrl + A?即可選中全部數(shù)據(jù)

2）快速撤銷/恢復(fù)

Ctrl + Z/Y：撤銷操作與恢復(fù)操作

3）快速移動

Ctrl + 方向鍵：當數(shù)據(jù)量比較多時，鼠標滾動不方便，按住一個單元格，然后按 Ctrl + 方向鍵 即可到達最后一個數(shù)字，比如按↓就會到達本列的最后一個數(shù)

4）快速框選

Ctrl + Shift + 方向鍵：從當前選中單元格開始快速框選到存在數(shù)據(jù)的最后一個單元格

【Ctrl + Shift + →】 快速選中單元格右邊內(nèi)容

【Ctrl + Shift + ↓】 快速選中單元格下方內(nèi)容

Shift + 雙擊邊線：快速選中雙擊邊線方向的連續(xù)單元格內(nèi)容

5）插入行列

Ctrl + Shift + 加號：選擇一行或者一列，按住快捷鍵，可以快速加入一行或者一列

6）刪除行列

Ctrl + Shift + 減號：選擇一行或者一列，按下快捷鍵，即可快速刪除

7）快速插入多行空白行

鼠標選中要插入的行，鼠標放在行旁邊，顯示實心十字的時候，按住Shift下拉，就會插入多行空白行

8）互換兩行、列的內(nèi)容

選中行或者列，光標放到邊線上，當光標變?yōu)槭旨^時，按住 Shift 拖拽

9）自動生成下拉列表

Alt + ↓?：空白單元格點擊【Alt + ↓】即可生成已輸入過的所有數(shù)據(jù)的下拉列表

10）快速美化表格

Ctrl + T：選擇任意單元格，按住Ctrl + T，彈出的窗口中可以選擇美化范圍，直接點擊確定后便會生成一個美化后的表格，也可以通過【設(shè)計】選項卡進行進一步的美化

11）快速分析

Ctrl +Q：選中需要分析的數(shù)據(jù)區(qū)域，按下快捷鍵，可以根據(jù)窗口提示選擇合適的功能模塊對數(shù)字進行分析

12）快速復(fù)制公式

直接拖動，或者直接雙擊實心加號

13）快速調(diào)整最適合列寬

選擇所有的列，任一列的邊線雙擊

14）插入批注

Shift + F2：選中需要添加的單元格，按 Shift + F2 鍵

15）快速關(guān)閉所有Excel文件

按住Shift，選擇其中一個Excel文件點擊關(guān)閉

16、其他

1）僅復(fù)制可見單元格

查找和選擇 → 定位條件 → 勾選可見單元格

2）單元格內(nèi)換行

Alt + Enter 即可實現(xiàn)單元格內(nèi)換行

3）隔行插入1空行

如圖所示，需要將分數(shù)列的數(shù)值插入至每個姓名的下方

左 → 右

① 方法一

插入空列（輔助列C），第一個輸入1，第二個空，以此復(fù)制全列

選中三列數(shù)據(jù)，定位條件選擇常量，確定

右擊，選擇插入，選擇活動單元格下移

選中分數(shù)列數(shù)據(jù)，復(fù)制，定位在第一個姓名下的空單元格位置，選擇性粘貼，選擇跳過空單元格

② 方法二

插入空列（復(fù)制列D），在D2:D4輸入1-4等差數(shù)列，在D5:D8輸入1.5-4.5等差數(shù)列

對D列進行升序排序

選中分數(shù)列數(shù)據(jù)，復(fù)制，定位在第一個姓名下的空單元格位置，選擇性粘貼，選擇跳過空單元格

4）刪除重復(fù)值

選擇包含重復(fù)值的單元格區(qū)域 → 選擇數(shù)據(jù)按鈕 → 刪除重復(fù)值 → 全選，對所有字段進行刪除

5）隱藏0值

選擇文件選項 → 高級 → 此工作表的顯示選項 → 在具有零值的單元格中顯示零 → 去掉勾選

6）文字跨列居中

選取需要跨的列，右鍵選擇設(shè)置單元格格式，選擇對齊選項卡 → 水平對齊 → 跨列居中 → 確定

7）合并多個單元格的內(nèi)容

選擇想要合并的區(qū)域 → 開始菜單 → 填充 → 內(nèi)容重排

注意把列寬改為合適的長度

8）合并相同內(nèi)容的單元格

思路：使用分類匯總工具，將不同的數(shù)據(jù)行分開

① 將需合并的所屬區(qū)域列按相同內(nèi)容進行排序?→?數(shù)據(jù)選項卡?→?分類匯總?→?按所屬區(qū)域分類，（可選）匯總方式選擇計數(shù)?→?確定

② 此時同類值下都有一行分類標簽

③ 從A2開始選中A列數(shù)據(jù)區(qū)域?→?開始?→?查找和替換?→?定位條件?→?空值?→?確定?→?合并單元格

④ 刪除分類匯總：數(shù)據(jù)選項卡?→?分類匯總?→?全部刪除

⑤ 將A列格式刷到B列：方法1-使用格式刷，方法2-復(fù)制粘貼格式

9）同時查看一個Excel文件的兩個工作表

選擇視圖菜單 → 新建窗口，此時會出現(xiàn)兩個窗口，然后選擇【全部重排】可以將兩個窗口平鋪至桌面

10）同時修改兩個sheet中相同位置的值

按住 Shift 選擇多個工作表，在其中一個中進行修改即可修改全部列表此處的值

11）恢復(fù)未保存文件

文件菜單 → 選項 → 保存 → 保存工作簿【自動恢復(fù)文件位置】有文件保存路徑，緩存設(shè)置可以調(diào)整文件緩存時間