【EXCEL】関数を用いての抽出

質問

A列からF列までの各列に数値が入っていて（以下ID）
関数を用いてG列に全列（A～F）に含まれているIDだけを抜き出したい場合
どのような式になるのでしょうか？

条件としては各列の行数はバラバラ（A列は500行、E列は30行のような）で
IDの桁数は上限なしです。

回答No.2

noname#70958 · ベストアンサー

ご質問の趣旨を取り違えていましたらすみません。

>全列（A～F）に含まれているIDだけを抜き出したい
私は「A～Fの各列に【共通して存在するID】を抽出」するのかと思ったのですが…。

もしそうであれば

１．
　G1セル：　=IF(ISNA(MATCH(F1,A:A,0)+MATCH(F1,B:B,0)+MATCH(F1,C:C,0)+MATCH(F1,D:D,0)+MATCH(F1,E:E,0)),"",ROW())
　としてF列の行数分下方にフィル。
２．
　H1セル：　=IF(ROW()>COUNT(G:G),"",INDEX(F:F,SMALL(G:G,ROW())))
　として空白が返るまで下方にフィル。

H列が求める結果となります。
--------------------------------------------------------------------------------
※G列は下記のように書くこともできますが、データ数が多いとおそらく重いです。
　=IF(SUMPRODUCT(--(COUNTIF(OFFSET($A$1,,{0,1,2,3,4},9999),F1)>0))=5,ROW(),"")

※作業列を使わず直接G列に返すのも不可能というわけではありませんが、重すぎて実用にはならないと思います。
　=INDEX(F:F,1/LARGE(INDEX(NOT(ISNA(MATCH(F$1:F$999,A:A,0)+MATCH(F$1:F$999,B:B,0)+MATCH(F$1:F$999,C:C,0)+MATCH(F$1:F$999,D:D,0)+MATCH(F$1:F$999,E:E,0)))/ROW(F$1:F$999),),ROW()))

いずれもExcel2003で動作確認。以上ご参考まで。

お礼

返事が遅くなってすいません。
教えていただいた関数を用いることで抽出ができました。
ありがとうございました！

回答No.4

noname#204879

I　　J　　K　　L　　 M　　 N　　 O
1　　0　　7　　2　　 4　　 3　　 5　　 6
2　　0　　7　　9　　13　　16　　21　　27
3　　0　7.5　9.5　13.5　16.5　21.5　27.5

１．範囲 I1:I3 に数値 0 を入力
２．次の各セルにそれぞれ次式を入力
　　J1: =COUNTA(A:A)
　　J2: =SUM($J1:J1)
　　J3: =J2+0.5
３．範囲 J1:J3 を右方にズズーッとドラッグ＆ペースト
４．セル G1 に次式を入力して、此れを下方にズズーッとドラッグ＆ペ
　　ースト
　　G1: =INDEX(A$1:F$1000,ROW(A1)-INDEX($I$2:$O$2,MATCH(ROW(A1),I$3:O$3)),MATCH(ROW(A1),I$3:O$3))

回答No.3

imogasi

IDについて、A-F列の全体の集合の中から、重複なく抜き出す（といっても作成に近い）のが質問の真意でしょうね。
各列に共通して含まれるIDを見つける、とも取れる質問で、明確で無い。
ーー
関数だけでは難しいと思う。
＃１のご回答のような式が理解できる人はそれを使えばいいのかもしれないが。
複数列にあるデータを対象にするということが、エクセル関数では
やりにくくしていると思う。
１列に人間が並べる（すなわちA列の最後の下にB列、A+B列データの最後の次からC列・・とコピー貼り付けをすれば、自称imogasi方式（Googleで照会すると多数出ます）でも出来ます。
初出のデータに連番を振る。
それを別シートにつめて表示する。
ーーー
VBAは経験なく、関数で、と希望しているのだろうが、詳しくVBAのコードの中身がわからなくても、下記を標準モジュールに貼り付けて実行すればどうですか。
関数のカウントイフだってエクセルがどういう処理をしているか、プログラムが組まれていると思うが、だれも知らないで使っている。
ーー
Sub test01()
k = 1
For Each cl In Range("A1:F10")
If cl <> "" And Application.WorksheetFunction.CountIf(Range("H:H"), cl) = 0 Then
Cells(k, "H") = cl
k = k + 1
End If
Next
End Sub
A1:F10は適当に、多目でもかまわない。
結果はH列に出している
例データ
A列　　B列　　　C列・・
1 2 3
2 1 3
13 5 6
8 9 7
3 4 6
12 34
8
14
結果
1
2
3
13
5
6
8
9
7
4
12
34
14
関数CountIfを使っているので、何をやっているか関数COUNTIFを知っておれば、推測はつと思う。
（注）標準モジュールは
シートガ出ているところで、ALT+F11キー
そこで出てくる画面の
メニューの挿入ー標準モジュールで出てくる白紙的画面にコピペ。
実行は、カーソルをコードの何処かにおき、F5キーを押す。

補足

すいません、質問文がわかりにくかったですね。
No2の方がおっしゃってるとおり共通して存在しているIDのみ抜き出すことが目的です。

回答No.1

zap35

かなりトリッキーな式になります。
＞IDの桁数は上限なしです
とありますが、それでは難しいので以下の条件はつけます
１）ＩＤは数値である
２）ＩＤの最大桁数は９桁とする（式を理解できれば変更可能です）
３）データ行の最大は９９９行までとする

以下の式を任意のセルに貼り付けて確定は通常のEnterキーではなく、Shift＋Ctrl＋Enterで確定してください。すると配列数式となって、数式バーには{ }で式が囲まれた状態になります

=MOD(SMALL(IF(ISNUMBER($A$1:$F$999),VALUE(COLUMN($A$1:$F$999)&TEXT(ROW($A$1:$F$999),"0000")&TEXT($A$1:$F$999,"000000000")),99000000000000),ROW(A1)),100000000)

後はセルを下方向にコピーします。
ただし処理が重いので、ものすごく反応が鈍くなると思います。ですから実用的な処理とはいえませんね。（それだけのことを要求されている訳ですから仕方ないですが…）

普通ならこのような処理はVBAを用いるのが一般的ではないでしょうか
「ＶＢＡはちょっと…」というなら仕方ありませんが、VBAならあっという間に終わりますし、シートが重くなることもありません

以下のマクロをALT＋F11でVBE画面を開き、左上のVBA Projectでシート名を右クリックし「挿入」→「標準モジュール」で表示される画面に貼り付けて下さい。マクロの実行はワークシート画面に戻りALT＋F8でマクロ一覧を開き、マクロ名を選択して「実行」ボタンです。

Sub Macro1()
Dim r, rng As Range
Dim cnt As Long
　Set rng = Intersect(Cells(1, 1).SpecialCells(xlCellTypeConstants, 1), Columns("A:F"))
　If Not rng Is Nothing Then
　　For Each r In rng
　　　cnt = cnt + 1
　　　Cells(cnt, "G").Value = r.Value
　　Next r
　End If
End Sub

上記マクロで処理する対象は「数値が入力されたセル」に限定しています。文字列も対象にするなら
SpecialCells(xlCellTypeConstants, 1)　の部分の「1」を「3」に変更してください