• ベストアンサー

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

A列からF列までの各列に数値が入っていて(以下ID) 関数を用いてG列に全列(A~F)に含まれているIDだけを抜き出したい場合 どのような式になるのでしょうか? 条件としては各列の行数はバラバラ(A列は500行、E列は30行のような)で IDの桁数は上限なしです。

質問者が選んだベストアンサー

  • ベストアンサー
noname#70958
noname#70958
回答No.2

ご質問の趣旨を取り違えていましたらすみません。 >全列(A~F)に含まれているIDだけを抜き出したい 私は「A~Fの各列に【共通して存在するID】を抽出」するのかと思ったのですが…。 もしそうであれば 1.  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列の行数分下方にフィル。 2.  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で動作確認。以上ご参考まで。

rei00122
質問者

お礼

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

その他の回答 (3)

noname#204879
noname#204879
回答No.4

  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 1.範囲 I1:I3 に数値 0 を入力 2.次の各セルにそれぞれ次式を入力   J1: =COUNTA(A:A)   J2: =SUM($J1:J1)   J3: =J2+0.5 3.範囲 J1:J3 を右方にズズーッとドラッグ&ペースト 4.セル 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))

  • imogasi
  • ベストアンサー率27% (4737/17069)
回答No.3

IDについて、A-F列の全体の集合の中から、重複なく抜き出す(といっても作成に近い)のが質問の真意でしょうね。 各列に共通して含まれるIDを見つける、とも取れる質問で、明確で無い。 ーー 関数だけでは難しいと思う。 #1のご回答のような式が理解できる人はそれを使えばいいのかもしれないが。 複数列にあるデータを対象にするということが、エクセル関数では やりにくくしていると思う。 1列に人間が並べる(すなわち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キーを押す。

rei00122
質問者

補足

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

  • zap35
  • ベストアンサー率44% (1383/3079)
回答No.1

かなりトリッキーな式になります。 >IDの桁数は上限なしです とありますが、それでは難しいので以下の条件はつけます 1)IDは数値である 2)IDの最大桁数は9桁とする(式を理解できれば変更可能です) 3)データ行の最大は999行までとする 以下の式を任意のセルに貼り付けて確定は通常の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はちょっと…」というなら仕方ありませんが、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」に変更してください

関連するQ&A

専門家に質問してみよう