エクセル質問です。

質問

A 　　 B 　C
5 80-070502 　　 60 　 10
6 80-070505 　　 50 　 12
7 80-070601 　　 45 　 15
8 80-070603 　　 55 　17
9 80-070605 　　 66 　 18
10 80-070608 　　 70 　45
というデータから
　D1にAの列の80-070502の05の部分を選び(070502と070505）そのときのBの値の平均を
　D2にAの列の80-070602の06の部分を選び(070601～070618）そのときのBの値の平均を
またできれば上記条件で　平均でなく、SUM、MIN、MAXの値も同じ様に出したいと思っています。
前に似たような質問をさせていただきましたが、そのときはDAVERAGEの式を教えていただきました。その場合検索条件のところを入れ替えれば、平均値はでる感じでしたが、１つの表にするのが、厳しい感じでして！
よろしくお願いいたします。

回答No.5

noname#204879

[ANo.4この回答への補足]に対するコメント、

》 しかしできません
》 いろいろ試したのですがうまくいきません

私は「丁寧に」書いたつもりなので、貴方も結果がどうなったのかを「丁寧に」書いてください。
「できません」とか「いろいろ」だけでは分かりません。

》 VAL2のところの平均と

貴方は「Bの値の平均を」と仰っていましたね。Ｂ列は VAL1 ですけど。

》 06をE3でなくE4にする意味はなんでしょう？

セル F1 を空白にせねばならないのと同様に、（ステップ５を有効ならしめるために）セル F3 を空白にする必要があるのです。

》 …の２は行のいみですよね？

違います。範囲 $A$4:$C$10 の２「列」目（つまりＢ列）の意味です。

》 $F1:$F2はF2ではいけないのですか？

はい、いけません。Criteria範囲は２行以上必要です。

回答No.4

noname#204879

A　　　 B　　 C　　D　E　　 F　　 G　　H　　I　　J
1　　　　　　　　　　　　　　　　　　　　AVG　SUM　MIN　MAX
2　　　　　　　　　　　　　　 05　 TRUE　 55　110　 50　 60
3
4　 CODE　　　 VAL1　VAL2　　 06　FALSE　 59　236　 45　 70
5　 80-070502　　60　　10
6　 80-070505　　50　　12
7　 80-070601　　45　　15
8　 80-070603　　55　　17
9　 80-070605　　66　　18
10　80-070608　　70　　45

１．セル E2、E4 にそれぞれ '05、'06 と（冒頭にアポストロフィを付けて）入力
２．セル F2、F4 に次式を入力
　　F2: =MID(A5,6,2)=E$2
　　F4: =MID(A5,6,2)=E$4
３．セル G2 に式 =DAVERAGE($A$4:$C$10,2,$F1:$F2) を入力して、此れを右方にズズーッと複写
４．範囲 H1:J1 の各式中の DAVERAGE を次のように書き換え
　　H1: DSUM
　　I1: DMIN
　　J1: DMAX
５．範囲 H1:J1 をセル G4 に複写

補足

丁寧にありがとうございます。
しかしできません。
VAL2のところの平均と
06のほうが！また、06をE3でなくE4にする意味はなんでしょう？

DAVERAGE($A$4:$C$10,2,$F1:$F2）の...,2,...の２は行のいみですよね？$F1:$F2はF2ではいけないのですか？
と質問していますが、いろいろ試したのですがうまくいきません。
よろしくお願いいたします。

回答No.3

Wendy02

こんにちは。

データベース関数と同じようなものを作ってみました。ただし、VBAですから、データベース関数よりかなりスピードは遅いです。

標準モジュールに取り付けてみてください。

標準モジュールへの取り付け方：

Alt　+　 F11 (Altを押しながらF11）を押すと、Visual Basic Editor 画面が出てきます。
次に、メニューの[挿入]－[標準モジュール]と開けて、クリックすると、画面が現れますので、以下のコードを貼り付けて、

Alt　+　Q で、画面を閉じます。

使い方は、

範囲やセルのアドレス等を入れて、右にドラッグしてください。
=DData($A$5:$A$10,$B$5:$B$10,$D5,COLUMN(A1)-1)
A列は検索範囲、B列が計算範囲

D列は、ここでは、*0705* などと入れると良いと思います。
　*はワイルドカードです。
　他に使えるものは、? は、1文字のワイルドカード
　[0-9] で、0～9まで

*7050*
*7060*
*7070*
*7080*
*7090*

COLUMN(A1)-1　は、引数の自動代入です。

SUM　　　AVERAGE　　　　MIN　　　MAX
の順に出てきます。
AVERAGE のオプションは、1で、0が、SUMになっていることに注意してください。

--------------------------------------------------------------
Function DData(DataRng As Range, _
　　　　　　　 mData As Range, _
　　　　　　　 ByVal SWord As String, _
　　　　　　　 Optional opt As Integer = 0)
'引数-DataRng;検索範囲, mData;数字計算範囲,SWord;検索値,opt;オプション値
'オプション-なしor0;合計, 1;平均,2;最小値,3;最大値,4;Count, 5;CountA
Dim c As Range
Dim i As Long
Dim j As Long
Dim k As Long
Dim buf As Variant
Dim oData() As Variant
Dim nData As Variant
i = 0: j = 0
For Each c In DataRng
　j = j + 1
　If c.Text Like SWord Then
　　 If VarType(mData.Cells(j, 1)) = vbDouble Then
　　　　ReDim Preserve oData(i)
　　　　oData(i) = mData.Cells(j, 1).Value
　　　　i = i + 1
　　 End If
　　 k = k + 1
　 End If
Next c
　On Error Resume Next
　 buf = oData(0)
　 If Err.Number > 0 Then
　　 nData = Array(0)
　　 k = 0
　　 i = 0
　 Else
　　 nData = oData()
　 End If
　On Error GoTo 0
　On Error GoTo ErrHandler
　Select Case opt
　 Case 0: buf = WorksheetFunction.Sum(nData)
　 Case 1:
　　 If i > 0 Then
　　 buf = WorksheetFunction.Sum(nData) / i
　　 End If
　 Case 2: buf = WorksheetFunction.Min(nData)
　 Case 3: buf = WorksheetFunction.Max(nData)
　 Case 4: buf = i　'Count
　 Case 5: buf = k　'CountA

　 Case Else: buf = WorksheetFunction.Sum(nData)
　End Select
ErrHandler:
　If Err.Number > 0 Then
　　DData = CVErr(xlErrValue)
　Else
　　DData = buf
　End If
End Function

回答No.2

maron--5

◆平均を配列数式で求める方法
=AVERAGE(IF(MID($A$5:$A$10,6,2)*1=D5,$B$5:$B$10))
★この式は「配列数式」です。式を入力後、Ctrl＋Shift＋Enter　をおして、式を確定させてください。
★確定すると、式の両端に｛　｝がつきます。

回答No.1

maron--5

A　　　　　　B　　　　C　　　D　　　　E　　　　F　　　　G　　　　H
4　　　　　　　　　　　　　　　　　　　　　　　　AVE.　　SUM　　MIN　　MAX
5　　80-070502　　　60　　　10　　　5　　　55　　　110　　　50　　　60
6　　80-070505　　　50　　　12　　　6　　　59　　　236　　　45　　　70
7　　80-070601　　　45　　　15
8　　80-070603　　　55　　　17
9　　80-070605　　　66　　　18
10　　80-070608　　　70　　　45

◆D1に「5」をD2に「6」と月を入力します
★SUMの式
F5=SUMPRODUCT((MID($A$5:$A$10,6,2)*1=D5)*$B$5:$B$10)
★AVE.の式
E5=SUMPRODUCT((MID($A$5:$A$10,6,2)*1=D5)*$B$5:$B$10)/SUMPRODUCT((MID($A$5:$A$10,6,2)*1=D5)*1)
★MINの式
G5=MIN(INDEX(SUBSTITUTE((MID($A$5:$A$10,6,2)*1=D5)*1,0,10^5)*$B$5:$B$10,))
★MAXの式
H5=MAX(INDEX((MID($A$5:$A$10,6,2)*1=D5)*$B$5:$B$10,))

★共に下にコピー

お礼

ありがとうございます。
早速やってみたのですが、
＃VALUEとエラーになってしまいます。
ちなみに　AVEとMAXをやってみました。
これができると凄い便利になりますので、ぜひともマスターしたいと思います。
お手数ですが、よろしくお願いいたします。