[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『一致データともう１つデータを抽出する方法』（HIRO_）
A列に1～30までの連番と連番に対するデータを1セットとして、そのセットが約1000セットあります。
aaaは通常データなので何も処理は行わず、aabのデータはB列に抽出し
その連番をデータの横のC列に抽出するということはできるでしょうか？
連番とデータはA列で上下ペアに並んでいますが、連番とデータはどちらが上にあるかは、決まっていません。
A B C D E F
データ 番号 データ 番号
1 1
2 aaa
3 3
4 aab aab 3
5 2
6 8
7 aaa
8 30
9 aaa
10 23
11 aac aac 30
12 30
確認ですが、5行目、6行目共に数字が入っていますが これでいいんでしょうか？ (SS)
aac は D 列に書くんですか？ そしてそのデータは E 列に書くんですか？ aac は，23 じゃないんですか？ aaa, aab, aac って，aa* の * 部分は何種類あるんですか？ 細かい仕様がわからないと，どうしようもないと思うんですが？ (阿部真三＠美しくない日本)
内容が大まかですみません。 5行目、6行目は自分の入力ミスです。 aabはB列に、aacはD列に1列飛びに増やしたいと思っています。 数は全部で13種類あります。 A列の内容ですが上から(連番・データ)、(連番・データ)、(データ・連番)、(データ・連番)です。 1セットの中には1～30まですべての連番がないものもあります。またどの番号がないか決まっていません。
A B C D E F
データ 番号 データ 番号
1 1
2 aaa
3 3
4 aab aab 3
5 aaa
6 30
7 aaa
8 23
11 aac aac 30
12 30
なぜ，aab, aac, ... を同じ列に入れてはいけないのか，理由があるんでしょうねぇ。 もし，aac もB 列に入れて良いのなら，
で，2行ある片方が数値，もう一方がテキストということを利用して，以下のように関数を組めば，D列とE列に貴方の望む結果が入っている（もちろん，B,C 列に求まるようにするのは容易）
蛇足ながら，最初の2行を入れて，2行単位でドラッグコピーすること。
1 =IF(ISTEXT(A1),IF(A1<>"aaa",A1,""),"") =IF(ISNUMBER(A1),A1,A2) =IF(AND(ISTEXT(A2),A2<>"aaa"),A2,"") =IF(AND(D1<>"",D1<>"aaa"),C1,"") aaa =IF(ISTEXT(A2),IF(A2<>"aaa",A2,""),"") =IF(ISNUMBER(A2),A2,"") =IF(AND(ISTEXT(A1),A1<>"aaa"),A1,"") =IF(AND(D2<>"",D2<>"aaa"),C2,"") 3 =IF(ISTEXT(A3),IF(A3<>"aaa",A3,""),"") =IF(ISNUMBER(A3),A3,A4) =IF(AND(ISTEXT(A4),A4<>"aaa"),A4,"") =IF(AND(D3<>"",D3<>"aaa"),C3,"") aab =IF(ISTEXT(A4),IF(A4<>"aaa",A4,""),"") =IF(ISNUMBER(A4),A4,"") =IF(AND(ISTEXT(A3),A3<>"aaa"),A3,"") =IF(AND(D4<>"",D4<>"aaa"),C4,"") 2 =IF(ISTEXT(A5),IF(A5<>"aaa",A5,""),"") =IF(ISNUMBER(A5),A5,A6) =IF(AND(ISTEXT(A6),A6<>"aaa"),A6,"") =IF(AND(D5<>"",D5<>"aaa"),C5,"") 8 =IF(ISTEXT(A6),IF(A6<>"aaa",A6,""),"") =IF(ISNUMBER(A6),A6,"") =IF(AND(ISTEXT(A5),A5<>"aaa"),A5,"") =IF(AND(D6<>"",D6<>"aaa"),C6,"") aaa =IF(ISTEXT(A7),IF(A7<>"aaa",A7,""),"") =IF(ISNUMBER(A7),A7,A8) =IF(AND(ISTEXT(A8),A8<>"aaa"),A8,"") =IF(AND(D7<>"",D7<>"aaa"),C7,"") 30 =IF(ISTEXT(A8),IF(A8<>"aaa",A8,""),"") =IF(ISNUMBER(A8),A8,"") =IF(AND(ISTEXT(A7),A7<>"aaa"),A7,"") =IF(AND(D8<>"",D8<>"aaa"),C8,"") aaa =IF(ISTEXT(A9),IF(A9<>"aaa",A9,""),"") =IF(ISNUMBER(A9),A9,A10) =IF(AND(ISTEXT(A10),A10<>"aaa"),A10,"") =IF(AND(D9<>"",D9<>"aaa"),C9,"") 23 =IF(ISTEXT(A10),IF(A10<>"aaa",A10,""),"") =IF(ISNUMBER(A10),A10,"") =IF(AND(ISTEXT(A9),A9<>"aaa"),A9,"") =IF(AND(D10<>"",D10<>"aaa"),C10,"") aac =IF(ISTEXT(A11),IF(A11<>"aaa",A11,""),"") =IF(ISNUMBER(A11),A11,A12) =IF(AND(ISTEXT(A12),A12<>"aaa"),A12,"") =IF(AND(D11<>"",D11<>"aaa"),C11,"") 30 =IF(ISTEXT(A12),IF(A12<>"aaa",A12,""),"") =IF(ISNUMBER(A12),A12,"") =IF(AND(ISTEXT(A11),A11<>"aaa"),A11,"") =IF(AND(D12<>"",D12<>"aaa"),C12,"")
A B C D E 1 1 1 2 aaa 3 3 3 aab 3 4 aab aab 5 2 2 6 8 8 7 aaa 30 8 30 30 9 aaa 23 10 23 23 11 aac aac 30 12 30 30 aac 30 (阿部真三＠美しくない日本)
ISTEXTで表示するかどうか判断し、 連番の方はISNUMBERで表示を判断するのですね。 ありがとうございました、早速挑戦します。 (HIRO_)
1行目に検索するデータを入力しておけば、 A B C 1 aab aac 2 1 3 aaa 4 3 5 aab 3 6 aaB 7 30 8 AAB 9 23 10 aac 30 11 30
B2セルに =IF(EXACT($A2,B$1),OFFSET($A2,IF(MOD(ROW(),2)=0,1,-1),0),"") として、範囲にコピペ。 という方法もあり？？？ (sin)
(阿部真三＠美しくない日本)様、本日シートを作成し見事に成功しました。 (sin)様、この様な方法で連番を抜出すことも出来るのですね。 挑戦します。ありがとうございました。
A列 B列(上段1行目） D列（下段2行目） C列(上段）E列（下段）
1 =IF(ISNUMBER($A1),"",IF(RIGHT($A1,1)="b",$A1,"")) =IF(B1="","",$A2)
=IF(ISNUMBER($A1),"",IF(RIGHT($A1,1)="c",$A1,"")) =IF(D1="","",$A2)
aaa =IF(ISNUMBER(A2),"",IF(RIGHT(A2,1)="b",A2,"")) =IF(B2="","",$A1)
=IF(ISNUMBER($A2),"",IF(RIGHT($A2,1)="c",$A2,"")) =IF(D2="","",$A1)
3 =IF(ISNUMBER(A3),"",IF(RIGHT(A3,1)="b",A3,"")) =IF(B3="","",$A4)
=IF(ISNUMBER($A3),"",IF(RIGHT($A3,1)="c",$A3,"")) =IF(D3="","",$A4)
aab =IF(ISNUMBER(A4),"",IF(RIGHT(A4,1)="b",A4,"")) =IF(B4="","",$A3)
=IF(ISNUMBER($A4),"",IF(RIGHT($A4,1)="c",$A4,"")) =IF(D4="","",$A3)
aaa =IF(ISNUMBER(A5),"",IF(RIGHT(A5,1)="b",A5,"")) =IF(B5="","",$A6)
=IF(ISNUMBER($A5),"",IF(RIGHT($A5,1)="c",$A5,"")) =IF(D5="","",$A6)
30 =IF(ISNUMBER(A6),"",IF(RIGHT(A6,1)="b",A6,"")) =IF(B6="","",$A5)
=IF(ISNUMBER($A6),"",IF(RIGHT($A6,1)="c",$A6,"")) =IF(D6="","",$A5)
aaa =IF(ISNUMBER(A7),"",IF(RIGHT(A7,1)="b",A7,"")) =IF(B7="","",$A8)
=IF(ISNUMBER($A7),"",IF(RIGHT($A7,1)="c",$A7,"")) =IF(D7="","",$A8)
23 =IF(ISNUMBER(A8),"",IF(RIGHT(A8,1)="b",A8,"")) =IF(B8="","",$A7)
=IF(ISNUMBER($A8),"",IF(RIGHT($A8,1)="c",$A8,"")) =IF(D8="","",$A7)
aac =IF(ISNUMBER(A9),"",IF(RIGHT(A9,1)="b",A9,"")) =IF(B9="","",$A10)
=IF(ISNUMBER($A9),"",IF(RIGHT($A9,1)="c",$A9,"")) =IF(D9="","",$A10)
30 =IF(ISNUMBER(A10),"",IF(RIGHT(A10,1)="b",A10,"")) =IF(B10="","",$A9)
=IF(ISNUMBER($A10),"",IF(RIGHT($A10,1)="c",$A10,"")) =IF(D10="","",$A9)
B列 D列は1行数式を入力で下へフィールコピー C列 E列は1,2行数式を入力で2行選択で下へフィールコピー （super）
A B C D E 1 1 2 aaa 3 3 4 aab aab 3 5 aaa 6 30 7 aaa 8 23 9 aac aac 30 10 30 （super）
これ、面白そうなんで、寄せてくらはい。
13種類という事でB列から26列目までデータを抽出します。
Alt+F11でVBEを開き、「挿入」→「標準モジュール」選択
下のコードをコピペ
'------------------------
Sub hiro()
Dim dic As Object, i As Long, y As Integer, x, data
Dim n As Integer, t
Set dic = CreateObject("scripting.dictionary")
Range("b2").Resize(Range("a" & Rows.Count).End(xlUp).Row, 27).ClearContents
tbl = Range("a2").Resize(Range("a" & Rows.Count).End(xlUp).Row, 27).Value
ReDim x(1 To UBound(tbl, 1), 1 To UBound(tbl, 2))
y = 1
ReDim data(1 To UBound(tbl, 1))
If IsNumeric(tbl(1, 1)) Then
data(1) = "up"
Else
data(1) = "down"
End If
For i = 2 To UBound(tbl, 1)
If (Not IsNumeric(tbl(i, 1)) And Not IsNumeric(tbl(i - 1, 1))) _
Or (IsNumeric(tbl(i, 1)) And IsNumeric(tbl(i - 1, 1))) Then
data(i) = IIf(data(i - 1) = "up", "down", "up")
Else
data(i) = data(i - 1)
End If
Next i
For i = 1 To UBound(tbl, 1)
If Not IsNumeric(tbl(i, 1)) And tbl(i, 1) <> "aaa" Then
If dic.exists(tbl(i, 1)) Then
For n = 1 To UBound(tbl, 2)
If dic.Item(tbl(i, 1)) = n Then
x(i, n) = tbl(i, 1)
x(i, n + 1) = IIf(data(i) = "down", tbl(i + 1, 1), tbl(i - 1, 1))
n = n + 1
Else
x(i, n) = Empty
End If
Next n
Else
dic(tbl(i, 1)) = y
For n = 1 To UBound(tbl, 2)
t = IIf(n = y, tbl(i, 1), IIf(n = y + 1, _
IIf(data(i) = "down", tbl(i + 1, 1), tbl(i - 1, 1)), Empty))
x(i, n) = t
Next n
y = y + 2
End If
End If
Next i
Application.EnableEvents = False
Cells(2, 2).Resize(UBound(tbl, 1), UBound(tbl, 2)) = x
End Sub
'右のプロジェクト欄のシート名をWクリックして下のコードをコピペ
'------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
hiro
Application.EnableEvents = True
End Sub
新しいブックのA列２行目からデータを放り込んでみておくんなはれ。
そして、任意のセル（A列）のデータを変更すると、データが抽出されるように
なっとります。
勿論単独でhiroを実行してもOKでっす。
（弥太郎）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.