http://www.get-digital-help.com/ ... sing-excel-formula/
Identify missing values in a column using excel formula

Table of contents

Missing numbers (array formula)
Missing numbers (vba)
Missing numbers (array formula)

Question: I want to find missing numbers in a range?
Answer: C1 and C2 contain the range boundaries.

Array Formula in B5
=SMALL(IF(ISERROR(MATCH($C$1+ROW(OFFSET($A$1, 0, 0, $C$2-$C$1+1))-1, $A$2:$A$6, 0)), $C$1+ROW(OFFSET($A$1, 0, 0, $C$2-$C$1+1))-1), ROW(A1))
How to create an array formula
Select cell B5
Copy / Paste array formula
Press and hold Ctrl + Shift
Press Enter
Release all keys
How to copy array formula
Copy cell B5
Select cell B6:B11
Paste
Download excel sample file for this tutorial.
missing-values-in-a-column.xlsx
(Excel 2007 Workbook *.xlsx)
Functions in this article:
MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
ROW(reference) returns the rownumber of a reference
SMALL(array,k) Returns the k-th smallest row number in this data set.
Missing numbers (vba)

The macro demonstrated here let′s you select a cell range (values must be in a single column), start and end number.

A new sheet is created, values are sorted in the first column. The second column (B) contains all missing values.

VBA

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Sub Missingvalues()
Dim rng As Range
Dim rng1 As Range
Dim StartV As Single, EndV As Single, i As Single, j As Single
Dim k() As Single
Dim WS As Worksheet
ReDim k(0)
On Error Resume Next
Set rng = Application.InputBox(Prompt:="Select a range:", _
Title:="Extract missin values", _
Default:=Selection.Address, Type:=8)
StartV = InputBox("Start value:")
EndV = InputBox("End value:")
On Error GoTo 0
Set WS = Sheets.Add
WS.Range("A1:A" & rng.Rows.CountLarge).Value = rng.Value
With WS.Sort
    .SortFields.Add Key:=WS.Range("A1"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Range("A1:A" & rng.Rows.CountLarge)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Set rng1 = WS.Range("A1:A" & rng.Rows.CountLarge)
For i = StartV To EndV
    On Error Resume Next
    j = Application.Match(i, rng1)
    If Err = 0 Then
        If rng1(j, 1) <> i Then
            k(UBound(k)) = i
            ReDim Preserve k(UBound(k) + 1)
        End If
    Else
        k(UBound(k)) = i
        ReDim Preserve k(UBound(k) + 1)
    End If
    On Error GoTo 0
Next i
ReDim Preserve k(UBound(k) - 1)
WS.Range("B1") = "Missing values"
WS.Range("B2:B" & UBound(k) + 1) = Application.Transpose(k)
End Sub

如果做回归分析的话，在对应变量有缺失值时，系统默认是删除整条对应记录。

这里有个例子，用IF函数，share一下。
http://ms-office.wonderhowto.com ... if-function-368008/

[tongue][tongue]谢谢！谢谢分享