編按：

工作資料中經(jīng)常有姓名、手機號、身份證號、客戶名稱、銀行賬號等涉及隱私或秘密的信息，在公開展示這些信息時往往需要脫敏處理，即用符號替換或取代部分關(guān)鍵數(shù)字實現(xiàn)隱私或秘密的保護。符號可以是星號、小圓點、短橫線等任意符號。今天就來給大家介紹一些Excel中常用的數(shù)據(jù)脫敏公式。

數(shù)據(jù)脫敏是指對某些敏感信息通過脫敏規(guī)則進(jìn)行數(shù)據(jù)的變形，實現(xiàn)敏感隱私數(shù)據(jù)的可靠保護。在涉及客戶安全數(shù)據(jù)或者一些商業(yè)性敏感數(shù)據(jù)，在不違反系統(tǒng)規(guī)則條件下，需要對真實數(shù)據(jù)進(jìn)行改造，如身份證號、手機號、卡號、客戶姓名等個人信息。下面就為大家介紹一些Excel中常用的數(shù)據(jù)脫敏公式。

注意：本文中的姓名、手機號、身份證號碼均為虛擬。

第一類：姓名脫敏

規(guī)則：保持姓名字?jǐn)?shù)不變的情況下，只顯示姓氏（復(fù)姓只顯示第一個字），其他信息都用*代替。

這類問題的特點是顯示的字?jǐn)?shù)固定，需要用*代替的字?jǐn)?shù)不固定，下面介紹幾個常用的公式。

公式1：=LEFT(A2,1)&REPT("*",LEN(A2)-1)

公式中的LEFT(A2,1)是截取姓名中的第一個字，REPT("*",LEN(A2)-1)的意思是將“*”重復(fù)姓名的字?jǐn)?shù)-1次。

當(dāng)LEFT的第二參數(shù)為1時可以省略，因此公式也可以修改為=LEFT(A2)&REPT("*",LEN(A2)-1)。

公式2：=REPLACE(A2,2,LEN(A2)-1,REPT("*",LEN(A2)-1))

這個公式主要利用了REPLACE函數(shù)實現(xiàn)數(shù)據(jù)中的內(nèi)容替換，REPLACE函數(shù)有四個參數(shù)，函數(shù)結(jié)構(gòu)是：REPLACE（要進(jìn)行替換的文本，從第幾個字開始替換，要替換的字?jǐn)?shù)，替換成什么內(nèi)容）。

在本例中要進(jìn)行替換的文本就是姓名所在的單元格，按要求是從第二個字開始替換，LEN(A2)-1表示要替換的字?jǐn)?shù)比姓名的字?jǐn)?shù)少一個，REPT("*",LEN(A2)-1)表示要替換的內(nèi)容，這個和公式1是一樣的。

公式3：=SUBSTITUTE(A2,RIGHT(A2,LEN(A2)-1),REPT("*",LEN(A2)-1))

這個公式是利用SUBSTITUTE函數(shù)實現(xiàn)數(shù)據(jù)中的內(nèi)容替換，函數(shù)結(jié)構(gòu)是SUBSTITUTE (要替換的文本,舊文本,新文本,[替換第幾個])。

與前兩個公式的差別主要是第二參數(shù)，RIGHT(A2,LEN(A2)-1)表示姓名中第二個字后面的內(nèi)容。關(guān)于SUBSTITUTE函數(shù)的詳細(xì)講解可以參考之前的教程：替換函數(shù)SUBSTITUTE的5個應(yīng)用技巧

第二類：手機號脫敏

規(guī)則：將手機號的中間4位用*代替

這類問題的特點是要顯示的內(nèi)容和用*代替的內(nèi)容長度都是固定的，下面介紹幾個常用的公式。

公式1：=LEFT(B2,3)&"****"&RIGHT(B2,4)

這個公式很好理解，分別提取左邊的三位LEFT(B2,3)和右邊的四位RIGHT(B2,4)，再用&將四個*連接起來即可。

這個公式也可以改變?yōu)?span>=MID(B2,1,3)&"****"&MID(B2,8,4)

用MID函數(shù)取代LEFT和RIGHT，實際上還是同一個思路。

公式2：=REPLACE(B2,4,4,"****")

REPLACE在前面已經(jīng)介紹了，公式很好理解，這里就不重復(fù)了。

公式3：=SUBSTITUTE(B2,MID(B2,4,4),"****")

公式4：=TEXT(C2,"000****0000")

這個公式是利用了TEXT函數(shù)中的數(shù)字占位符“0”，將指定位置的數(shù)字用*顯示。

這方面的知識可以參考教程TEXT函數(shù)用法

第三類：身份證號碼脫敏

規(guī)則：將身份證號碼的最后5位用*代替

這類問題與手機號脫敏的特點差不多，區(qū)別是對字符串右邊的內(nèi)容進(jìn)行替換。解決的公式和原理也都類似，下面僅列舉常用的公式，不再一一解釋了。

公式1：=LEFT(B2,13)&"*****"

公式2：=REPLACE(B2,14,5,"*****")

公式3：=SUBSTITUTE(B2,RIGHT(B2,5),"*****")

公式4：=TEXT(C2,"0!*****")

寫在最后：

用公式處理后的數(shù)據(jù)，還需要粘貼為數(shù)值，然后再刪除原始數(shù)據(jù)。數(shù)據(jù)脫敏不同于數(shù)據(jù)加密，雖然二者都有保護數(shù)據(jù)的作用，但是數(shù)據(jù)加密并沒有對數(shù)據(jù)本身做處理，也就是說，只要將數(shù)據(jù)解密后就可以看到原始數(shù)據(jù)。而數(shù)據(jù)脫敏是不可逆的，也就是說，沒有辦法用脫敏后的數(shù)據(jù)得到原始數(shù)據(jù)，因此做好原始數(shù)據(jù)的備份，非常重要！

本文配套的練習(xí)課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學(xué)習(xí)Excel：

相關(guān)推薦：

如何提取品牌信息？LOOKUP函數(shù)有絕招！

沒有Textjoin函數(shù)，如何解決提取數(shù)據(jù)的問題？

Excel教程：如何制作帶有層次和透視感的圖表？

八大查找函數(shù)公式，輕松搞定數(shù)據(jù)中的多條件查找

版權(quán)申明：

本文作者老菜鳥；同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。