Excelについての質問です。

質問

図のように整数が並んだ表があります。（実際は数千）
その中から、例えば６．９、１５が同時に含まれる行の数を求めたいのです。
この図では、答えは「2」です。

できれば、“数式ひとつで求める方法をお願いします。”

という質問に対して、以下のような回答を得ました。

=SUMPRODUCT((MMULT(COUNTIF(OFFSET(A1:J1,ROW(1:n)-1,),{6,9,15}),1^ROW(1:m))=m)*1)
★n：行数
★m：含まれる数字の数（6,9,15の3つ

この回答は使わせていただくのですが、
さらに進化させ、3つの数字をあらかじめ書き出しておいて、
その数字を参照しながらオートフィルで処理したいのです。
上記の式では、６、９、１５を書き換えていかなければなりません。

どなたか、お知恵を拝借できないでしょうか？
よろしくお願いします。

回答No.1

keithin · ベストアンサー

その式でほとんどそのまま
=SUMPRODUCT((MMULT(COUNTIF(OFFSET($A$1:$J$1,ROW($1:$5)-1,),B6:D6),1^ROW($1:$3))=3)*1)
とかでいいですよ。
ｍだのｎだのも元の式の説明の通りです。

お礼

ありがとうございます。
できました。

すっきりしました。

回答No.3

某HN クロメート(Chromate)（@CoalTar）

E6セルに
=SUMPRODUCT((MMULT(COUNTIF(B6:D6,$A$1:$J$3),TRANSPOSE(COLUMN($A$1:$J$1)^0))=3)*1)
[Ctrl]+[Shift] +[Enter] で確定、配列数式です({}で囲まれる)
似て非なる、、、(-_-)zzz

お礼

できました！
ありがとうございます。

配列数式？　どこかで使ったことがあるような気がしますが、
難しい。

回答No.2

tom04

こんばんは！
実際のデータは何千行？もあるというコトなので、VBAでの一例です。

Sheet1の1行目からあるデータをSheet2に表示するようにしてみました。
Sheet2の配列は↓の画像のようになっているとします。

画面左下のSheet1のSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i, j As Long
Dim ws As Worksheet
Dim Area As Variant
Set ws = Worksheets(2)
ws.Columns(4).ClearContents
Application.ScreenUpdating = False
For j = 1 To ws.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Set Area = Range(Cells(i, 1), Cells(i, 10))
If WorksheetFunction.CountIf(Area, ws.Cells(j, 1)) And _
WorksheetFunction.CountIf(Area, ws.Cells(j, 2)) And _
WorksheetFunction.CountIf(Area, ws.Cells(j, 3)) Then
ws.Cells(j, 4) = ws.Cells(j, 4) + 1
End If
Next i
Next j
Application.ScreenUpdating = True
End Sub 'この行まで

※　データが変わるたびにマクロを実行してみてください。

こんな感じではどうでしょうか？m(_ _)m

お礼

ありがとうございます。いろいろ書いていただいたのですが、
私のスキルでは理解不能した。
申し訳ありません。