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

質問

いつもこの掲示板でお世話になっています。前にアップした同名の質問のバリエーションです。

添付の画像にあるエクセル表で、縦軸は生徒の名前、横軸は6回行ったテストの各自の得点とします。前回の質問で、1回でも50点以上をとった生徒の合計を求めるのに

=SUMPRODUCT((MMULT((B2:G6>=50)*1,ROW(A1:A6)/ROW(A1:A6))>0)*1)

という方法を教わりましたが、このように作業列や配列数式を使わない方法で、「最初に50点に到達した回の全員の平均値」を求められないでしょうか。添付の画像の例だと、A君4回目、B君5回目、C君5回目、D君6回目、E君2回目なので、平均は4.4回となります。簡単にするために皆必ず50点以上はどこかでとっているとして構いません。

他の人の質問での回答で、最初に50点に到達した回数というのはA君の場合だと

=INDEX(B1:G6,MATCH(0,INDEX(0/(B2:G2>=50),0),0))
または
=MIN(INDEX((B2:G2<50)*10^5+B1:G6,0))

とすると個々には求められるのはわかっているのですが、作業列を使わずに1つの関数で全員分の演算をしたいのです。よろしくお願いします。

回答No.8

bunjii · ベストアンサー

>実際に求めたいのは、例えば算数と国語のテストを行い、それぞれにあるボーダー点数（算数と国語で異なる）に到達する回数を求め（例えば算数ではA君4回目、B君5回目、C君5回目、D君6回目、E君2回目。国語ではA君1回目、B君6回目、C君3回目、D君7回目、E君1回目）、国語での到達回数が速かった生徒の数を求めるというものなのです。
>このイメージとしては、各科目での各生徒の到達回数が得られれば =SUMPRODUCT(({4;5;5;6;2}>={1;6;3;7;1})*1) → 3 みたいな感じでした。
今回の提示で目的が見えてきました。しかし、A君からE君までの最初に閾値越えたのは何回目かを配列で返す数式は見当たりませんので組み込み関数では解決できないでしょう。
MMULT関数では閾値越えが何回有ったかを各自について配列で返せますが質問の内容では初回の閾値越えは何回目のテストかを求めるので目的が異なります。
MATCH関数では1行または1列に対して1つの値で検索しますので、配列で戻す値はありません。
MIN関数は配列全体を対象に最小値を抽出してしまいますので、行毎または列毎の個別に求める必要があります。
従って、作業用に算数と国語の閾値越えを生徒毎に算出してから最終の求める値の算出にSUMPRODUCT関数等を使えば良いでしょう。

お礼

色々考えていただいてありがとうございました。
組み込み関数の組み合わせではやはり無理そうという事了解しました。回答No7さんに提案していただいた、VBAユーザ定義関数を作り込んで行く方向で考えていきたいと思います。

回答No.10

Chiquilin

> =SUMPRODUCT((MMULT((B2:G6>=50)*1,ROW(A1:A6)/ROW(A1:A6))>0)*1)
=SUM((MMULT(N(B2:G6>=50),1^ROW(A1:A6))>0)*1)

SUMでいいものを SUMPRODUCTにしたり 前回の回答で MMULT内の計
算に「INDEX(……,0)」なんて入れてしまうのも 理解がないからです。
配列計算をただただ人の物真似で使っているから こんなことになります。

「ROW(A1:A6)/ROW(A1:A6)」はもっとひどい。 TRANSPOSEもそう。
使わなくても計算できます。こっちは質問者が理解できないと思って敢え
て使ったのかもしれませんが……

正直 配列計算をちゃんと理解していれば「最初に50点に到達した回の全員
の平均値」くらい別に難しくありません。前提をすっ飛ばすから 変な計算
をしないと求められないのでしょう。

作業列なしで計算したいのなら 配列計算を１から勉強し直した方がいいと
思います。別にいじわるで数式の回答を控えている訳じゃありません。
配列計算を使うのなら 人真似じゃダメです。

回答No.9

Chiquilin

> 私の質問に回答してくれた方を愚かよわばりするのは控えていただけますか。
お断りします。この数式を見て平然としている人ばかりなのだとしたら
回答するのは控えて欲しいものです。そのくらい数式の理解が足りない。

回答No.7

real beatin（@realbeatin）

こんにちは。お邪魔します。

前回は設問を正しく理解出来ていなくてすみませんでした。
補足を受けて今回と同趣旨の回答を書いてはいたのですが、控えました。
今回と前回の分、合わせてユーザー定義関数（ＵＤＦ）にてここでお応えします。

その前に、
「配列数式」って何でもかんでも悪者みたいに言う方も結構居ますけれど、
それと同じ位「配列計算数式」にも偏見持っている方も居て、
Excelのバージョンが新しくなるに連れ、
同名の関数の中身が変わってより再計算が高速になっていたり、
計算の仕組み（内部的な計算順序）が変わったと思ったらまた戻ってたり、
マルチスレッドで再計算が速くなったと思ったら不適切な関数を使うと却って遅くなったり、
以前速かった（/遅かった）数式が相対的に遅く（/速く）なったり、再逆転したり、
Officeの32/64ビットの違いでも逆転する場合もあったり、と、
先入観を切り離して、受け止められている人が、
どれだけ居らっしゃるのだろう、凄いな、って思います。
（その手の資料があってもなくっても、泥臭く
　カットアンドトライで自分で確かめるの派？な私です。）
という訳で、前回も今回もバージョンに関する言及が無い中で、
再計算の処理速度のお話が出てきたことには少し違和感があります。
斯く言う私もバージョンを知らないまま回答を重ねている訳ですが、
それを押してもＵＤＦを紹介して置きたいと今回は思いました。

●今回の「閾値を達成した"回数"の平均」を求める関数
の使い方として、
=AvgTimesAchieved(B2:G6,50)
のように、テーブル、閾値、のふたつを渡せば求まります。
離散化した閾値を配列として指定することで、
計算をより高速にすることも出来ます。
添付画像の配置例だと、
　K2:K11 を選択した状態で、
　数式バーに、=AvgTimesAchieved(B2:G6,I2:I11)
　Ctrl+Shfit+Enter で「複数セル配列数式」を確定します。
後々、[参照元]を変更する場合は、置換が便利ですけれど、
事前に[名前の定義]で[定義された名前]を参照しておく方が簡単かもです。

◇試しに人数を増やして、5000"人"＊６"回"（閾値＊１０）
簡単にベンチに掛けてみましたが、どちらのＵＤＦも、
私の環境では、0.015 から 0.035 Sec. 平均 0.029 Sec. で
再計算（その他の数式の再計算を停めた状態）されました。
最初に数式を確定する時は、Excel数式と同様、
オーバーヘッドが増えますが、それでも 2-3倍程度 で
確定→再計算（→条件付き書式再計算は未計測）完了でした。

●前回の「閾値達成人数」の場合（前回の設問で例示された配置で）
　B12:B15 を選択した状態で、
　数式バーに、=Num_P_Achieved(B4:F8,A12:A15)
　Ctrl+Shfit+Enter で「複数セル配列数式」を確定します。
以下同様です。

参考）
『Excel 2010 のパフォーマンス: パフォーマンスの問題を最適化するヒント』
https://msdn.microsoft.com/ja-jp/library/office/ff726673%28v=office.14%29.aspx#xlVBAUDF
｜VBA ユーザー定義関数の高速化
｜｜通常、Excel の数式計算とワークシート関数を使用した方が、VBA ユーザー定義関数を使用するよりも高速です。...
｜｜...ただし、ユーザー定義関数の設計と呼び出しが適切であれば、複雑な配列数式よりもはるかに高速になります。
｜｜...
｜｜ユーザー定義関数を呼び出すたびに、および Excel から VBA にデータを転送するたびに、時間のオーバーヘッドが発生します。
｜｜場合によっては、1 つの複数セル配列数式のユーザー定義関数によって、複数の関数呼び出しを単一の関数 (結果の範囲を返す複数セル入力範囲を持つ) にまとめることで、これらのオーバーヘッドを最小限に抑えることができます。

VBAもやってみたけれど遅過ぎで開発を諦めた、というようなお話をお見掛けしましたし、
数式研究会という事でもないようですから、
事案の解決、への参考にでもなればと思っています。

' ' //　★？　[ As Long | As Double | As Currency | As Date ]　？

' ' //　qa9198807 準拠
Function AvgTimesAchieved(rSourceRange As Range, vThresholdValues)
Dim mtxS()
Dim tnY As Long, tnX As Long
　　mtxS() = rSourceRange.Value
　　tnY = UBound(mtxS())
　　tnX = UBound(mtxS(), 2)

Dim mtxThrd　'　二次元配列
Dim tnW As Long
　　If UCase(TypeName(vThresholdValues)) = "RANGE" Then
　　　　mtxThrd = vThresholdValues.Value
　　Else
　　　　mtxThrd = vThresholdValues
　　End If
On Error GoTo Scalar2Matrix_
　　tnW = UBound(mtxThrd)
On Error GoTo 0

Dim mtxD()　'　★？
Dim nTotal As Long, cnt As Long
Dim y As Long, x As Long, w As Long
　　ReDim mtxD(1 To tnW, 1 To 1)
　　For w = 1 To tnW
　　　　cnt = 0
　　　　nTotal = 0
　　　　For y = 1 To tnY
　　　　　　For x = 1 To tnX
　　　　　　　　If mtxS(y, x) >= mtxThrd(w, 1) Then Exit For
　　　　　　Next x
　　　　　　If Not x > tnX Then
　　　　　　　　cnt = cnt + 1
　　　　　　　　nTotal = nTotal + x
　　　　　　End If
　　　　Next y
　　　　If cnt Then mtxD(w, 1) = nTotal / cnt
　　Next w

　　AvgTimesAchieved = mtxD()
Exit Function
Scalar2Matrix_:
　　ReDim mtxThrd(1 To 1, 1 To 1)
　　mtxThrd(1, 1) = vThresholdValues
Resume
End Function
' ' //

' ' //　qa9191157 準拠
Function Num_P_Achieved(rSourceRange As Range, vThresholdValues)
Dim mtxS()
Dim tnY As Long, tnX As Long
　　mtxS() = rSourceRange.Value
　　tnY = UBound(mtxS())
　　tnX = UBound(mtxS(), 2)

Dim mtxPBest()　'　★？
Dim tmp　'　★？
Dim y As Long, x As Long
　　ReDim mtxPBest(1 To tnY)
　　For y = 1 To tnY
　　　　tmp = 0
　　　　For x = 1 To tnX
　　　　　　If mtxS(y, x) > tmp Then tmp = mtxS(y, x)
　　　　Next x
　　　　mtxPBest(y) = tmp
　　Next y

Dim mtxThrd　'　二次元配列
Dim tnZ As Long
　　If UCase(TypeName(vThresholdValues)) = "RANGE" Then
　　　　mtxThrd = vThresholdValues.Value
　　Else
　　　　mtxThrd = vThresholdValues
　　End If
On Error GoTo Scalar2Matrix_
　　tnZ = UBound(mtxThrd)
On Error GoTo 0

Dim mtxD()　'　★？
Dim cnt As Long, nThrd As Long
Dim z As Long
　　ReDim mtxD(1 To tnZ, 1 To 1)
　　For z = 1 To tnZ
　　　　cnt = 0
　　　　nThrd = mtxThrd(z, 1)
　　　　For y = 1 To tnY
　　　　　　If mtxPBest(y) >= nThrd Then cnt = cnt + 1
　　　　Next y
　　　　mtxD(z, 1) = cnt
　　Next z

　　Num_P_Achieved = mtxD()
Exit Function
Scalar2Matrix_:
　　ReDim mtxThrd(1 To 1, 1 To 1)
　　mtxThrd(1, 1) = vThresholdValues
Resume
End Function
' ' //

お礼

realbeatinさん
ありがとうございます。以前VBAで遅かったのは、VBAでセルにパラメータをセットし、演算は作業列を用いてセル関数で行わせ、その結果をセルからVBAで拾って回答テーブルに配置する、というのを単にFOR文で廻していたからかもしれません。全く最適化していませんでしたので。回答いただいたユーザ定義関数を用いるアプローチ、別の人に依頼している標準関数の組み合わせでできなかったら、取り組ませていただきます。
どうもありがとうございました。

回答No.6

msMike

[No.1お礼]へのコメント、
「z=f(x,y)」「離散値」「2次元の配列」「3Dグラフ」「カラースケール」等々のジャーゴン(jargon)が飛び交う(私とは)全く別の世界が、ご質問の背景にあるお話だったのですね。何だかホッとしました。(*^_^*)
ご丁寧な返信、ありがとうございました。

回答No.5

imogasi

作業列を使わない方法を望む心理は判るのですが。
条件を満たす「行を抜き出す問題などで長らく、多数の回答を見ています。
この質問の回答で言えば＃です
しかしマニアックになりすぎて、趣味の世界に入り込むように思います。
余の中広いので、今まで多分苦労した人は、過去のパターンを修正して割合早く回答できるのでしょうが。
普通の人はパズルの回答で苦しむようなもので、仕事で、こういう課題をやらなくなったら
時間の浪費です。
そこで（関数では複雑な組み合わせになりそうな場合は）私はあきらめて、VBAで処理します。
こういっても、私の声は少数派でしょうし、質問者が聞くとは思いませんが。またVBAが今すぐには、できないという問題は大きいですが。
しかし仕事の絡みでエクセルを使うならばVBAの学習は必須というのが私の持論です。
どんな方法にしろ、明日までに仕上げるべし、というような場面が多い。
参考にVBAで、趣味的に（すみません）やって上げてみます。
ユーザー関数形式にしています。（普通はプロシージュアー形式にします）
VBEの画面の標準モジュールに
Function test01(x)
Dim cl As Range
Dim dt As Range
Dim n(10)
For Each cl In Range(x).Rows
j = 1
For Each dt In cl.Cells
f = "n"
If dt.Value > 20 Then '20点を超える例
n(dt.Row) = dt.Column - 1
f = "y"
GoTo p1
Else
j = j + 1
End If
If f = "n" And j = 5 Then
n(dt.Row) = 0
End If
Next
p1:
Next
test01 = WorksheetFunction.Average(n())
End Function
と入れます。
例題として「２０点を超える」にします。
例データ
A列と第１行は生徒名と第何回テストの代わりです。
データはA:G列、H列（H列が作業列に当たる）
- 1 2 3 4 5 ２０以上になったのは第何回
1 1 2 18 24 5 4
2 6 24 28 9 10 2
3 11 12 16 23 15 4
4 16 13 18 19 20 0
5 12 32 11 23 45 2
6 15 18 24 26 22 3
2.5　　＜－　=AVERAGE(H2:H7)　確認のため
シートのH10セルに
=test01("B2:F7")　と入れます。
2.5が返ります。
上記で２０点というのは（プログラムの中に定数で組み込んでいますが）、引数を増やして上記関数の第２引数で指定するようにも、上記を修正するのは簡単です。
＝＝
蛇足
悩ましいのは、最終的に越えられない人の扱いです。
上記では、０として平均を下げるままになってます。

お礼

ありがとうございます。
No.1のmsMikeさんのお礼コメントにも書いたz=f(x,y)が最終形なのですが、VBAを使うのであれば作業列を使っても良く、（x,y）の組み合わせをFor文で入れ替えて行き、作業列を使って演算し、得られたzの値を2次元配列に書込んでいけます。実はこれはやったのですが、元のデータが多いためか、全2次元配列を埋めるのにものすごく時間がかかってしまいました。そのために関数でできないかという試行をしています。もっとも関数でできたとしてもかなり処理時間がかかるかもしれませんが、ここはまだ見えていません。

回答No.4

Chiquilin

> =SUMPRODUCT((MMULT((B2:G6>=50)*1,ROW(A1:A6)/ROW(A1:A6))>0)*1)
質問者も質問者なら 回答者も回答者です……なんと愚かな。

作業列をなくそうと思うなら 今ある数式をもっとちゃんと理解すべきです。
公式的に使ってるから そんなひどい数式を見ても平気なんでしょう。

お礼

私はエクセルに関して知識がプアなのでまあ愚かで良いんですが、私の質問に回答してくれた方を愚かよわばりするのは控えていただけますか。

回答No.3

bunjii

>他の人の質問での回答で、最初に50点に到達した回数というのはA君の場合だと・・中略・・とすると個々には求められるのはわかっているのですが、
提示の数式に誤りがあります。
=INDEX(B1:G6,MATCH(0,INDEX(0/(B2:G2>=50),0),0))
　　↓　内側のINDEX関数の返り値に♯DIV/0!が発生しますので下記のように訂正する
=MATCH(0,INDEX((B2:G2<50)*1,0),0)
また、オートフィルコピーするときのことを考えればセルの絶対番地と相対番地の使い分けが必要かと思います。

>作業列を使わずに1つの関数で全員分の演算をしたいのです。
1つの数式でA君からE君まで最初に50点以上の得点となった回数を個々に求めることはできますが、全員の回数の平均値は1つの数式で求めるには無理があります。
H2セルへA君の4回目を返す数式は次のようにすれば良いでしょう。
=MIN(INDEX(($B$2:$G$6<50)*(MAX($B$1:$G$1)+1)+($B$2:$G$6>=50)*$B$1:$G$1,ROW($B1:$G1),0))
H2セルを下へH6までコピーすれば全員の回数が個別に算出できます。

補足

そうですか。無理がありますか。
各生徒の到達回数の平均値を求めるというのは質問を簡素化した１つの例でした。実際に求めたいのは、例えば算数と国語のテストを行い、それぞれにあるボーダー点数（算数と国語で異なる）に到達する回数を求め（例えば算数ではA君4回目、B君5回目、C君5回目、D君6回目、E君2回目。国語ではA君1回目、B君6回目、C君3回目、D君7回目、E君1回目）、国語での到達回数が速かった生徒の数を求めるというものなのです。

このイメージとしては、各科目での各生徒の到達回数が得られれば
=SUMPRODUCT（({4;5;5;6;2}>={1;6;3;7;1})*1)
=3
みたいな感じでした。（いいかげんな表記でスミマセン。数字が到達回数を示しています）

やはり無理がありますでしょうか。

回答No.2

chie65536（@chie65535）

普通に

=AVERAGE(INDEX(B$1:G$1,0,MATCH(0,INDEX(0/(B2:G2>=50),0),0)),INDEX(B$1:G$1,0,MATCH(0,INDEX(0/(B3:G3>=50),0),0)),INDEX(B$1:G$1,0,MATCH(0,INDEX(0/(B4:G4>=50),0),0)),INDEX(B$1:G$1,0,MATCH(0,INDEX(0/(B5:G5>=50),0),0)),INDEX(B$1:G$1,0,MATCH(0,INDEX(0/(B6:G6>=50),0),0)))

ではダメなんですか？

お礼

ありがとうございます。
今回提示した例では頂いた回答で解が出るのですが、知りたかったのは縦軸の生徒数がもっと増えても対応できるような一般解だったのです。

回答No.1

msMike

》 個々には求められるのはわかっているのですが、作業列を
》 使わずに1つの関数で全員分の演算をしたいのです
作業列を設けたらお茶の子なのをご承知なのに、なぜそれをお嫌いになるのか、教えてください。
私自身の Excel の修行の参考にしたいのです。

お礼

質問した例題は知りたい解を得るためにかなり簡素化したものだったのですが、実際にやりかったのは、z=f(x,y)という形の2つの変数を持つ関数で、xの離散値を行側に、ｙの離散値を列側に展開し、2次元の配列をつくり、(x,y)の各交点に各zの値が入るようにし、それを3Dグラフまたはカラースケールで表示したかったのです。作業列を作る方法だとx、yの離散値数が多くなると膨大な作業列を作る必要がでてきて現実的でないので、fを作業列を用いない一発の関数として得たかったという事なのです。