回答編集履歴
3
追記
answer
CHANGED
|
@@ -1,7 +1,7 @@
|
|
|
1
1
|
---
|
|
2
2
|
VBAでユーザ定義関数を作る場合
|
|
3
3
|
```Excel
|
|
4
|
-
=matching($A$2:$A$9,$E$2:$E$5,ROW()-1)
|
|
4
|
+
[D2] =matching($A$2:$A$9,$E$2:$E$5,ROW()-1)
|
|
5
5
|
```
|
|
6
6
|
```vba
|
|
7
7
|
Function matching(a, b, c)
|
|
@@ -22,4 +22,8 @@
|
|
|
22
22
|
[D2] =IFERROR(INDEX($A:$A,H2),"")
|
|
23
23
|
```
|
|
24
24
|
|
|
25
|
-
---
|
|
25
|
+
---
|
|
26
|
+
最新版のExcelでスピル関数が使える場合
|
|
27
|
+
```Excel
|
|
28
|
+
[D2] =IFERROR(UNIQUE(INDEX($A:$A,SORT(IF(COUNTIF($E:$E,$A$2:$A$9)=0,ROW($A$2:$A$9),NA)))),"")
|
|
29
|
+
```
|
2
追記
answer
CHANGED
|
@@ -1,5 +1,7 @@
|
|
|
1
|
+
---
|
|
2
|
+
VBAでユーザ定義関数を作る場合
|
|
1
3
|
```Excel
|
|
2
|
-
=matching($A$2:$A$9,$E$2:$E$5,ROW()-1)
|
|
4
|
+
=matching($A$2:$A$9,$E$2:$E$5,ROW()-1)
|
|
3
5
|
```
|
|
4
6
|
```vba
|
|
5
7
|
Function matching(a, b, c)
|
|
@@ -10,4 +12,14 @@
|
|
|
10
12
|
Next
|
|
11
13
|
matching = ""
|
|
12
14
|
End Function
|
|
13
|
-
```
|
|
15
|
+
```
|
|
16
|
+
|
|
17
|
+
---
|
|
18
|
+
ワーク列を用いる場合
|
|
19
|
+
```Excel
|
|
20
|
+
[G2] =IF(AND(COUNTIF($E:$E,$A2)=0,COUNTIF(OFFSET($A:$A,0,0,ROW($A2)-1),$A2)=0),ROW(),"")
|
|
21
|
+
[H2] =MIN(OFFSET($G:$G,N(H1),0,COUNTA($A:$A)-N(H1)))
|
|
22
|
+
[D2] =IFERROR(INDEX($A:$A,H2),"")
|
|
23
|
+
```
|
|
24
|
+
|
|
25
|
+
---
|
1
修正
answer
CHANGED
|
@@ -2,12 +2,12 @@
|
|
|
2
2
|
=matching($A$2:$A$9,$E$2:$E$5,ROW()-1)
|
|
3
3
|
```
|
|
4
4
|
```vba
|
|
5
|
-
|
|
6
5
|
Function matching(a, b, c)
|
|
7
6
|
Dim i
|
|
8
7
|
For Each matching In a.Value
|
|
9
8
|
If WorksheetFunction.CountIf(b, matching) = 0 Then i = i + 1
|
|
10
9
|
If i = c Then Exit Function
|
|
11
10
|
Next
|
|
11
|
+
matching = ""
|
|
12
12
|
End Function
|
|
13
13
|
```
|