自建常用excel2013小筆記（待改善版）

08-26

1. excel 單元格只顯示可選項：

首先選中要製作下拉菜單的單元格或單元格區域----數據---數據驗證---設置---選擇"列表"---源---輸入要填充的各個選項---比如輸入: 男,女 (兩個選項之間用英文半形逗號相隔)---確定。

文本長度可以用於設置身份證固定長度。單元格格式需要設置成文本。

2.excel 清除單元格所有格式：

開始---清除---全部清除

3.excel 顯示公式：

公式---顯示公式 / ctrl + ～

mac 需要在系統偏好設置---鍵盤---修飾鍵將 command換成 crtl

4. 特定字體顏色：

條件格式----新建規則----典型

5.快速填充選定單元格：

Ctrl+Enter 快速在多個單元格中填充相同的數據

開始---填充

6.自動刷新

Alt+F11 快捷鍵打開VBE編輯器，輸入代碼：

Private Sub Worksheet_Activate() ActiveSheet.PivotTables(工作表1).PivotCache.RefreshEnd Sub

（待驗證）

7.excel 刪除單元格空格：

SUBSTITUTE函數主要對指定的字元串進行替換，```

=substitute(text,old_text,new_text,[instance_num])=substitute（需要替換的文本，舊文本，新文本，第N箇舊文本）

TRIM函數主要用於把單元格內容前後的空格去掉，但並不去除字元之間的空格。

=TRIM()

CLEAN函數，用於刪除文本中不能列印的字元。對從其他應用程序中輸入的文本使用該函數，將刪除其中含有當前操作系統無法列印的字元。

=CLEAN()

8.excel匹配查找：

lookup（模糊查找，超出返回相鄰最大值）與vlookup （false / 0，精確查找，超出不返回值；true/1/不填，模糊查找）

VLOOKUP功能是按列查找，最終返回該列所需查詢列序所對應的值；與之對應的HLOOKUP是按行查找的。

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

參數 |簡單說明 |輸入數據類型

lookup_value |要查找的值 |數值、引用或文本字元串

table_array |要查找的區域 |數據表區域

col_index_num |返回數據在查找區域的第幾列數 |正整數

range_lookup |模糊匹配/精確匹配 |TRUE/FALSE（或不填）

Vlookup最容易出錯的地方是查找區域的首列必須含有查找的內容

9.絕對引用區域：

加$符號，如$A$2:$F$12。

10. 兩列查重

MATCH(lookup_value, lookup_array, match_type)

MATCH函數用於在指定區域內按指定方式查詢與指定內容所匹配的單元格位置

lookup_value：需要在數據表（lookup_array）中查找的值。

lookup_array：可能包含有所要查找數值的連續的單元格區域，區域必須是某一行或某一列

match_type：表示查詢的指定方式，用數字-1、0或者1表示，省略相當於match_type為1的情況。為1時，查找小於或等於lookup_value的最大數值在lookup_array中的位置，lookup_array必須按升序排列。為0時，查找等於lookup_value的第一個數值，lookup_array按任意順序排列。為-1時，查找大於或等於lookup_value的最小數值lookup_array中的位置，lookup_array必須按降序排列。

=IF(MATCH(A2,$A:$A,0)=ROW(),"不重複"，"重複")。=IF(COUNTIF(A:A,A2&"*")>1,"重複","") 可以看11。

11. 統計符合某個條件所出現的個數：

COUNTIF

=countif(計數區域,計數條件)

用來查身份證重複：IF(COUNTIF(A:A,A2&"*")>1,"重複","") ，因為excel對數字只查找前15位，所以需要添加字元幫助改成文本格式進行比對。

12. 統計字母長度：

LENB 函數主要用來統計字元串的位元組數。一個數字或一個字母按一個位元組算，一個漢字按兩個位元組算。

例如=lenb(A1)

13.選擇定位：

CRTL+G

可以對可見單元格進行定位複製。怕接觸篩選後定位錯位對話，可以先對可見單元格進行標記在複製，比如在後面單元格添加一個數字做標記，之後再對標記做排序。

一次性將這些有公式的單元格選中設置為鎖定

1.選中表格，當然也可以選中整張工作表；

2.選擇格式---單元格---保護，然後取消「鎖定」；

3.打開編輯---查找---定位，或按F5 / fn +F5(os系統)；

4.選擇「定位條件」，這時又選中了所有需要的單元格；

5.打開格式---單元格---保護，然後將「鎖定」和「隱藏」全都選中；

6.由於只有在工作表被保護的狀態下，保護單元格和隱藏公式才能生效，因此，我們還需要將這張工作表給保護起來，方法當然是應用「工具」菜單的「保護工作表」功能了。

14.excel計算年齡：

=（today（）-日期）/365 格式為數字

= DATEDIF(A1,TODAY(),"y")

15.數列批量修改：

數據---分列，操作前先修改好單元列的格式。

16.截取：

TRUNC（number，number_digits）截取並非四捨五入，需要區別INT（）取整

第一個參數number：指的是需要截尾取整的數字。

第二個參數number_digits：指定取整精度的數字。默認情況下，number_digits的值為0，也就是取整數了；為1，保留一個小數點。

為什麼叫待改善版本呢，因為以後還會添加一些，目前是我從之前筆記本上摘錄的，以及一些網上摘抄。