如下兩個表格:
第一種情況是按姓名排序的, 第二種情況姓名未加排序。
兩種情況下, 怎樣給相同姓名添加相同編號?
今天韓老師給出函數法與VBA法。
相同姓名有序排列:
1、在A2儲存格輸入編號1;
2、在A3儲存格輸入公式:
=IF(B3=B2,A2,A2+1)
該公式的含義是:
如果B3和B2姓名相同, 則填寫B2對應的編號A2, 否則A2+1;
相同姓名淩亂無序排列:
其實在真正工作中, 有序排列的姓名不多, 更多的是無序排列。
1、在F2中輸入公式:
=IFERROR(VLOOKUP(G2,IF({1,0},G$1:G1,F$1:F1),2,0),N(F1)+1)
向下填充, 即得結果:
其中:
IF({1,0},G$1:G1,F$1:F1):
是由IF函數重新構建一動態區域, 該區域有兩列:
第一列是姓名, 起始儲存格是G1, 結束儲存格隨公式向下填充而擴展;
第二列是編號, 起始儲存格是F1, 結束儲存格隨公式向下填充而擴展;
公式不管填充到哪一行, 該動態區域的結束行都是當前公式所在行的上一行。
VLOOKUP(G2,IF({1,0},G$1:G1,F$1:F1),2,0):
在上述動態區域中精確查找姓名對應的編號。
IFERROR(VLOOKUP(G2,IF({1,0},G$1:G1,F$1:F1),2,0),N(F1)+1):
如果查找姓名G2對應的編號出錯, 則返回N(F1)+1;
公式向下填充, 如果到第12行, 則:查找姓名G12對應的編號出錯, 則返回N(F11)+1。
N函數:
語法:N(VALUE);
功能:將不是數值的值轉換為數值形式;
不同參數VALUE, 對應的返回值:
本示例中, N(F1)的返回值是0。
注:無序排列的公式同樣適合有序排列!
VBA法代碼為:
Sub 編號()
Dim a As Object, i As Long '創建字典/
Set a = CreateObject("scripting.dictionary")
For i = 2 To Range("b1").CurrentRegion.Rows.Count
If Not a.exists(Cells(i, 2).Value) Then '添加編號到字典, 第一個編號不存在), 則
k = k + 1 '計數
a(Cells(i, 2).Value) = k '給字典key編號, 第一個編號是1, 第二個編號是2......
Cells(i, 1) = k '返回給第一列, 分組
Else '如果字典(編號)存在
Cells(i, 1) = a(Cells(i, 2).Value) '直接在字典裡查詢編號
End If
Next
End Sub
用法: