Excelで二つのデータの関係表を作成したい

質問

Excelで画像ような二つのデータの関係表を作成したいと思います。
データの数は３００ほどであります（画像ではA～AXまでの４８です）
データの範囲は画像では(1)は５刻み、(2)は５０刻みですが、変更する場合もあります。
ご教示のほど宜しくお願いいたします。

回答No.3

tom04 · ベストアンサー

こんばんは！
すでに回答は出ていますので、参考程度で・・・

アップされている画像のA～C列がSheet1にあり、Sheet2に表示するようにしてみました。
画像ではB列・C列の範囲が同じセルになっていますので、↓の画像のように別セルに範囲の数値を入れています。

お使いのバージョンがExcel2007以降の場合は
C3セルに
=COUNTIFS(Sheet1!$B:$B,"<="&$A3,Sheet1!$B:$B,">="&$B3,Sheet1!$C:$C,">="&C$1,Sheet1!$C:$C,"<="&C$2)

Excel2003までの場合は
=SUMPRODUCT((Sheet1!$B$1:$B$1000<=$A3)*(Sheet1!$B$1:$B$1000>=$B3)*(Sheet1!$C$1:$C$1000>=C$1)*(Sheet1!$C$1:$C$1000<=C$2))

という数式を入れ、列・行方向にオートフィルでコピー！

ただこれだと「0」が表示されてしまいます。
Excelのオプションから「ゼロ値」の表示をのチェックを外してもよいのですが、そうするとC1セルの「0」も表示されなくなりますので、
条件付き書式でセルの値が「0」の場合はフォントの色を「白」にしています。

参考になりますかね？m(_ _)m

回答No.2

KURUMITO

作業列を作ってたいうすることがパソコンに負担を掛けない方法としてお勧めです。
元のデータがＡ，Ｂ，Ｃ列の2行目か下方にあるとします。
ところで刻みの数値ですが縦の列をお示しのケースでが5刻み、横の列では50刻みになっています。前者の刻み数をＦ1セルに入力、後者の刻み数をＧ1セルに入力するとします。例ではＦ1セルに5、Ｇ1セルに50と入力します。
そこで作業列ですがＤ列に作成するとしてＤ2セルには次の式を入力して下方にドラッグコピーします。

=IF(B2="","",CEILING(B2,F$1)&"/"&IF(FLOOR(C2-0.1,G$1)=0,0,FLOOR(C2-0.1,G$1)+1))

Ｆ列に縦の数値を表示させるとしてＦ3セルには次の式を入力して下方にドラッグコピーします。

=IF(CEILING(MAX(B:B),F$1)-(ROW(A1)-1)*F$1>=FLOOR(MIN(B:B),F$1),CEILING(MAX(B:B),F$1)-(ROW(A1)-1)*F$1,"")

Ｂ列の最高値と最低値を調べて数値が表示されます。
お示しの表では例えば140-136のようになっていますがここでは左側の数値である140のみが表示されるようにしています。
同じようにＧ2セルには次の式を入力して右横方向にドラッグコピーします。

=IF(COLUMN(A1)=1,0,IF((COLUMN(A1)-1)*$G$1<=CEILING(MAX($C:$C),$G$1),(COLUMN(A1)-1)*$G$1+1,""))

C列の最高値を調べて数値が表示されます。
Ｈ2セルでは51と表示されＩ3セルでは101と表示されますが、これもお示しの表のようにH2セルでの意味は51-100の範囲を意味しています。
Ｇ3セルには次の式を入力して右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(OR($F3="",G$2=""),"",IF(COUNTIF($D:$D,$F3&"/"&G$2)=0,"",COUNTIF($D:$D,$F3&"/"&G$2)))

回答No.1

keithin

ご利用のエクセルのバージョンが不明ですが、Excel2007以降ならCOUNTIFS関数でさくっと集計します。
Excel2003以前を使っている場合は、３００個程度なら次のようにします。もちろん２００７以降でも同じ関数で出来ます。

準備：
Ｄ２に横の刻み幅を５０と記入する
Ｄ３に縦の刻み幅を５と記入する
必要に応じて数字を直す。

Ｅ３に
=IF(ROW(E1)>INT(MAX(B:B)/$D$3)+1,"",FLOOR(MAX(B:B)+$D$3,$D$3)-$D$3*ROW(E1))
と記入、下向けにコピー貼り付け

Ｆ２に
=IF(COLUMN(A2)>INT(MAX($C:$C)/$D$2)+1,"",$D$2*(COLUMN(A2)-1)+1)
と記入、右向けにコピー貼り付け

＃いわずもがなですが、横列の先頭が０から始まっているのは間違いと判断します。

それぞれのセルに、セルの書式設定の表示形式のユーザー定義で
0"-"
と設定しておく

Ｆ３に
=IF(OR($E21="",K$2=""),"",SUMPRODUCT(($E21<=$B$2:$B$300)*($B$2:$B$300<$E21+$D$3)*(K$2<=$C$2:$C$300)*($C$2:$C$300<K$2+$D$2)))
と記入、セルの書式設定の表示形式のユーザー定義で
#
と設定しておく

Ｆ３をコピー、表範囲に貼り付け。

＃参考
ピボットテーブルレポートでグループ化して集計することでイミフメイな関数とか一つも使わずに集計する事も出来ますが、この手のご相談では皆さん関数の方が嬉しい方ばっかりなので説明は割愛します。

お礼

有難うございます。ただ、
＞Ｆ３に
=IF(OR($E21="",K$2=""),"",SUMPRODUCT(($E21<=$B$2:$B$300)*($B$2:$B$300<$E21+$D$3)*(K$2<=$C$2:$C$300)*($C$2:$C$300<K$2+$D$2)))
と記入、セルの書式設定の表示形式のユーザー定義で
#
と設定しておく

が上手くいきません。再度ご指導をお願いします。

補足

申し訳ありません。上手くいきました。
E21→E3、K$2→F$2に修正しました。