EXCEL 作業列を使わずにどこまでできるか。

質問

適切なタイトルでなくてすみません。添付の画像にあるエクセルの表があるとします。表１は、縦軸は生徒の名前、横軸は複数回行ったテストの各自の得点とします。　表２は、表１を検索して点数毎の人数を得るものです。（例では、1回でも20点以上とった人は5人、30点以上とった人は3人、40点以上とった人は1人を示しています）。表２のB12～B15の各セルに答えを入れます。

さて、B12～B15に入れる関数を知りたいのですが、作業列を作ってしまえば簡単にできてしまいますが、今回は作業列を使わないでこれを求めたいのです。いくら考えても良いアイデアが浮かびません。配列数式を使う場合と使わない場合と両方ともに解があるならば、両方とも教えてもらえないでしょうか。

回答No.10

bunjii · ベストアンサー

>求めたかったのは「1回でもXX点以上をとった人の数」なのです。例えばB12は、A君～E君全員が10点以上を1回でもとっているので5。B13の20点も全員が1回でもとっているので5。B14ではA君、C君、E君の3人が30点以上を1回でもとっているので3。という数式を求めたいのです。
自分が考えていることを第三者に伝えることは難しいですね。
B12へ次の数式を設定して、下へオートフィルコピーしてください。
=SUMPRODUCT((MMULT(($B$4:$F$8>=A12)*1,ROW(A$4:A$8)/ROW(A$4:A$8))>0)*1)
添付画像はExcel 2013で検証した結果です。

お礼

ありがとうございました。こう使うんですね！

回答No.9

Chiquilin

#4です。それなら
=SUM(IF(MMULT(N($B$4:$F$8>=$A12),{1;1;1;1;1}),1))

でいいんじゃないですか？

回答No.8

ushi2015

こんにちは、No2です
セルB3:F3から1行ずつオフセットした範囲、
B4:F4～B8:F8の各範囲で、
1回でもXX点以上があれば「1」、なければ「0」として、
各範囲の結果を合計しています。

3行目が非表示になっているのに気付かなかったので、行目が項目行
としています。

回答No.7

kkkkkm

=IF(AND(COUNTIF($B$4:$F$4,">=" & A12),COUNTIF($B$4:$F$4,"<" & A13)),1,0)+IF(AND(COUNTIF($B$5:$F$5,">=" & A12),COUNTIF($B$5:$F$5,"<" & A13)),1,0)+IF(AND(COUNTIF($B$6:$F$6,">=" & A12),COUNTIF($B$6:$F$6,"<" & A13)),1,0)+IF(AND(COUNTIF($B$7:$F$7,">=" & A12),COUNTIF($B$7:$F$7,"<" & A13)),1,0)+IF(AND(COUNTIF($B$8:$F$8,">=" & A12),COUNTIF($B$8:$F$8,"<" & A13)),1,0)

こんな式を書くくらいなら作業列しますよね(^^;

回答No.6

Chiquilin

多分 興味ないでしょうけど ピボットテーブルを使うのなら 作業列も作業
シートもいりません。

Alt + D + P でウィザードを起動
[複数のワークシート範囲]を選択して[次へ]
→[指定]を選択して[次へ]
→範囲に「A2:F8」を指定して[追加]し[次へ]
→適当なセルを選択して[完了]

行フィールド：[値]
列フィールド：なし
値フィールド：[値 / データの個数]

作成されたピボットテーブルの行フィールド上で[グループ化]
先頭の値を「0」 末尾の値を「39」 単位「10」で[OK]
空白と総計行を表示させないよう設定

以上

お礼

ありがとうございます。でも関数でやりたかったのです。

回答No.5

imogasi

関数でやるのが当然という質問でしょうが、あえてピボットテーブルの操作でやってみます。このやり方を宣伝したい。
作業列どころか、作業シートを使っているが。この考え・やり方は将来仕事で役立つと思うから。
エクセル　２０１３
例データ画像の通り　B2:F6
10 20 30 20 20
20 16 10 0 10
30 35 40 20 5
10 25 5 10 5
20 25 0 35 5
データを組換え
VBAで、標準モジュールに
Sub test01()
j = 2
For Each cl In Worksheets("Sheet1").Range("B2:F6")
Worksheets("Sheet2").Cells(j, "A") = cl
Worksheets("Sheet2").Cells(j, "B") = 1
j = j + 1
Next
End Sub
実行結果（関数でもできるかもしれないが複雑だろう）
点数 頻度
10 1
20 1
30 1
20 1
20 1
20 1
16 1
10 1
0 1
10 1
30 1
35 1
40 1
20 1
5 1
10 1
25 1
5 1
10 1
5 1
20 1
25 1
0 1
35 1
5 1
（その後の操作）
表データのセルをVBAで２列データにする（A列は点数、B列は１ばかり。氏名は捨象）
このデータをもとにピボットテーブルを作る。
（どの行でもよい）行ラベルをアクチブ
右クリック
グループ化
先頭ー末尾ー単位を入力
結果　単位１０
ーー
結果のピボットテーブル
行ラベル 合計 / 頻度
0-9 　　6
10-19 6
20-29 8
30-40 5
総計　 25

回答No.4

Chiquilin

例で書いている人数がどうにも合わないように見えます。

=INDEX(FREQUENCY($B$4:$F$8,$A$12:$A$15-1),ROW(A2))

「ROW(A2)」からスタートしているのは「0以上10未満」の分布が不足して
いるように見えるからです。

それはともかく ピボットテーブルの[複数のワークシート範囲]を使って数を
グループ化して集計すれば 何てことない話です。

補足

Chiquilinさん
ありがとうございます。私の質問の表現が全く悪かったのですが、求めたかったのは「1回でもXX点以上をとった人の数」なのです。例えばB12は、A君～E君全員が10点以上を1回でもとっているので5。B13の20点も全員が1回でもとっているので5。B14ではA君、C君、E君の3人が30点以上を1回でもとっているので3。という数式を求めたいのです。

なのでみなさんの回答を私が例で書いている人数が合わないのです。これはできますでしょうか。

回答No.3

real beatin（@realbeatin）

こんにちは。

区間がきちんと設定されていないことが数式づくりを難しくしています。
作表の仕方として、
分布というからには、10点未満も反映させるのが普通なのでは？と。
そうすれば、区間が設定された表になるので、
ずーっと簡単な数式になりますけれど、、、という提案です。
元々、行の非表示を使っている（添付画像）ようですから、
どうしても10点未満を表示したくなければ、非表示にすれば良さそうです。

　　|　A　　　B
11　|　分布　 人数
12　|　 0　　 6
13　|　10　　 6
14　|　20　　 8
15　|　30　　 4
16　|　40　　 1

配列数式の場合、
１）B12:B16を選択
２）数式バーに以下の数式を入力。
=FREQUENCY(B4:F8,A12:A16+9)
３）Ctrl+Shift+Enter で配列数式を確定。
４）確定した数式が、
{=FREQUENCY(B4:F8,A12:A16+9)}
　と表示されているのを確認。

配列数式ではない数式の場合、
１）B12:B16を選択
２）数式バーに以下の数式を入力。
=SUMPRODUCT(--(MATCH($B$4:$F$8,$A$12:$A$16,1)=INT(A12/10)+1))
または、
=SUMPRODUCT(--(MATCH($B$4:$F$8,$A$12:$A$16,1)=ROW(A1)))
３）Ctrl+Enter で数式を確定。

上記の数式の意図としては、セル範囲の参照回数を減らす、
という狙いで書いています。

もしも、設問で例示されたように、
どうしても10点未満を無視して作表しなければならないのであれば、
その理由を補足して貰えれば、また考えてみます。

以上です。

補足

realbeatinさん
ありがとうございます。私の質問の表現が全く悪かったのですが、求めたかったのは「1回でもXX点以上をとった人の数」なのです。例えばB12は、A君～E君全員が10点以上を1回でもとっているので5。B13の20点も全員が1回でもとっているので5。B14ではA君、C君、E君の3人が30点以上を1回でもとっているので3。という数式を求めたいのです。これはできますでしょうか。

回答No.2

ushi2015

こんにちは
セルB12に
=SUM(IF(COUNTIFS(OFFSET($B$3:$F$3,{1,2,3,4,5},0),">="&$A12,OFFSET($B$3:$F$3,{1,2,3,4,5},0),IF(ISNUMBER(A11),"<",">=")&IF(ISNUMBER(A13),A13,MAX($A$4:$F$8)+1))>0,1,0))
で下方にフィルコピーとか。
関数苦手なのでダメかも・・・・

補足

教えてください。OFFSET($B$3:$F$3,{1,2,3,4,5},0)というのはどういう領域を指定しているのですか？　　OFFSET(基準,行数,列数,高さ,幅)と比べると全く形式が異なるように見えるのですが。

回答No.1

bunjii

Excel 2007以降のバージョンではCOUNTIFS関数で簡単に計数できます。
表2は10以上20未満、20以上30未満、30以上40未満、40以上の其々をカウントすればよいでしょうか？

B12=COUNTIFS($B$4:$F$8,">="&A12,$B$4:$F$8,"<"&IF(ISNUMBER(A13),A13,MAX($A$4:$F$8)+1))
B12を下へB15までオートフィルコピーすれば添付画像のようになります。

補足

bunjiiさん
ありがとうございます。私の質問の表現が全く悪かったのですが、求めたかったのは「1回でもXX点以上をとった人の数」なのです。例えばB12は、A君～E君全員が10点以上を1回でもとっているので5。B13の20点も全員が1回でもとっているので5。B14ではA君、C君、E君の3人が30点以上を1回でもとっているので3。という数式を求めたいのです。これはできますでしょうか。