在日常工作中,經常會涉 及到各類 姓名、手機號、身份證號等資訊,為了防止「數據外泄」,在發送給別人的時候往往需要進行脫敏處理。
簡單點來說就是將 部份內容替換成星號「*」 。
今天就來給大家介紹一些Excel中常用的 數據脫敏公式。
註意:文中的姓名、手機號、身份證號碼 均為虛擬 。
第一類:姓名脫敏
規則:保持姓名字數不變的情況下,只顯示姓氏(復姓只顯示第一個字),其他資訊都用*代替。
這類問題的特點是顯示的字數固定,需要用*代替的字數不固定,下面介紹幾個常用的公式。
公式1:=LEFT(A2,1)&REPT("*",LEN(A2)-1)
公式中的LEFT(A2,1)是截取姓名中的第一個字,REPT("*",LEN(A2)-1)的意思是將「*」重復姓名的字數-1次。
當LEFT的第二參數為1時可以省略,因此公式也可以修改為=LEFT(A2)&REPT("*",LEN(A2)-1)。
公式2:=REPLACE(A2,2,LEN(A2)-1,REPT("*",LEN(A2)-1))
這個公式主要利用了REPLACE函式實作數據中的內容替換,REPLACE函式有四個參數,函式結構是:REPLACE(要進行替換的文本,從第幾個字開始替換,要替換的字數,替換成什麽內容)。
在本例中要進行替換的文本就是姓名所在的單元格,按要求是從第二個字開始替換,LEN(A2)-1表示要替換的字數比姓名的字數少一個,REPT("*",LEN(A2)-1)表示要替換的內容,這個和公式1是一樣的。
公式3:=SUBSTITUTE(A2,RIGHT(A2,LEN(A2)-1),REPT("*",LEN(A2)-1))
第二類:手機號脫敏
規則:將手機號的中間4位元用*代替
這類問題的特點是要顯示的內容和用*代替的內容長度都是固定的,下面介紹幾個常用的公式。
公式1:=LEFT(B2,3)&"****"&RIGHT(B2,4)
這個公式很好理解,分別提取左邊的三位LEFT(B2,3)和右邊的四位RIGHT(B2,4),再用&將四個*連線起來即可。
這個公式也可以改變為=MID(B2,1,3)&"****"&MID(B2,8,4)
用MID函式取代LEFT和RIGHT,實際上還是同一個思路。
公式2:=REPLACE(B2,4,4,"****")
REPLACE在前面已經介紹了,公式很好理解,這裏就不重復了。
公式3:=SUBSTITUTE(B2,MID(B2,4,4),"****")
公式4:=TEXT(C2,"000****0000")
這個公式是利用了TEXT函式中的數位占位符「0」,將指定位置的數位用*顯示。
這方面的知識可以參考教程
第三類:身份證號碼脫敏
規則:將身份證號碼的最後5位用*代替
這類問題與手機號脫敏的特點差不多,區別是對字串右邊的內容進行替換。解決的公式和原理也都類似,下面僅列舉常用的公式,不再一一解釋了。
公式1:=LEFT(B2,13)&"*****"
公式2:=REPLACE(B2,14,5,"*****")
公式3:=SUBSTITUTE(B2,RIGHT(B2,5),"*****")
公式4:=TEXT(C2,"0!*****")
寫在最後:
用公式處理後的數據,還需要將其貼上為數值,然後再刪除原始數據。 而數據脫敏是不可逆的,脫敏後的數據無法再得到原始數據,因此做好原始數據的備份,這一點非常重要!
歡迎掃碼進群交流學習Excel
最後,歡迎加入Excel函式訓練營,學習68個函式、練習課件、輔導答疑。