エクセルのマクロについて質問です。

配列のそれぞれを関数で変換して別の配列にしてその配列を使った関数の答えを取得したいのです。

具体的には、1行目はラベル、2行目以降がデータとします。
A列に年齢(数値),B列からI列に8種類の記号(◎,○,●,△,▲,□,☆,?)が入ります。記号がないセルもあります。
22 ● ○ ▲ ○ ☆ □ □ ◎
33 ◎ ○ 空白 □ □ □ □ ○
28 空白
18 ○ ○ ○ ○ ○ □ □ 空白
……

ここでJ列に例えばBからI列の平均を入力したいのです。◎=8,○=7,●=6,△=5,…,?=1という感じでそれぞれをMatch関数などを使って変換してさらにその平均を取得したい。Array関数なんかを使えるのかどうなのか分からないのですがこんなことが可能なのでしょうか。
配列関数を使えば簡単かもしれませんがあくまでマクロを使用した回答でお願いします。。
以上よろしくお願いします。

回答の条件
  • 1人2回まで
  • 登録:2007/09/25 22:33:36
  • 終了:2007/09/26 12:25:16

回答(3件)

id:y3kz No.1

y3kz回答回数31ベストアンサー獲得回数92007/09/25 23:45:04

ポイント30pt

空白セルは0とみなして計算対象にするということでいいのでしょうか。

そうだとして、以下のマクロでどうでしょうか。

Sheet1に対象データがあると仮定しています。

Public Sub myGetAverage()
    Dim myRow As Range '計算対象とする行
    Dim lSum As Long '数値の合計
    Dim i As Long 'ループ変数
    
    Set myRow = Sheets("Sheet1").Rows(2)  'Sheet1の2行目から処理を始める
    
    Do While myRow.Cells(1, 1).Value <> ""  'A列の値がなくなるまで繰り返す
        lSum = 0
        
        'B列からI列までの記号を数値に変換し、加えていく。
        For i = 1 To 8
            lSum = lSum + myDecoding(myRow.Cells(1, i + 1).Value)
        Next
        
        '数値の合計を8で割って、J列に書き込む
        myRow.Cells(1, 10).Value = lSum / 8
        
        '計算対象を1行下に移す
        Set myRow = myRow.Offset(1, 0)
    Loop
End Sub

'記号を数値に変換する関数
Private Function myDecoding(sSrc As String) As Long
    Select Case sSrc
    Case "◎"
        myDecoding = 8
    Case "○"
        myDecoding = 7
    Case "●"
        myDecoding = 6
    Case "△"
        myDecoding = 5
    Case "▲"
        myDecoding = 4
    Case "□"
        myDecoding = 3
    Case "☆"
        myDecoding = 2
    Case "?"
        myDecoding = 1
    Case Default
        myDecoding = 0
    End Select
End Function
id:zuguimo

早速ありがとうございます。実は空白のときは計算に加えないようにしたいです。また平均だけでなく標準偏差なども計算したい時もありますので ISum のところがちょっと問題あります。ありがとうございました。

2007/09/26 00:08:06
id:Mook No.2

Mook回答回数1312ベストアンサー獲得回数3912007/09/26 00:38:37

ポイント40pt

基本的な構造は y3kz さんと同じですが、平均と標準偏差を計算するようにしてみました。

Option Explicit
Sub myCalc()
    Dim lastLine&
'--- A 列にデータがある範囲を計算
    lastLine = Range("A65535").End(xlUp).Row
    
    Dim i&, j&, cSum#, sqSum#, ret#, cnt&
    For i = 2 To lastLine
        cSum = 0
        sqSum = 0
        cnt = 0
        For j = 2 To 8
            ret = Sym2Val(Cells(i, j).Value)
            If ret >= 0 Then
                cSum = cSum + ret
                sqSum = sqSum + ret ^ 2
                cnt = cnt + 1
            End If
        Next
'--- 変換する数値があった場合に平均を計算
        If (cnt > 0) Then
'--- 平均
            Cells(i, "J").Value = cSum / cnt
'--- 標準偏差
            Cells(i, "K").Value = (sqSum / cnt - (cSum / cnt) ^ 2#) ^ 0.5
        End If
    Next
End Sub
'--- 記号を数値にする関数:定義がない場合は-1
Function Sym2Val#(sym$)
    Select Case sym
    Case "◎": Sym2Val = 8#
    Case "○": Sym2Val = 7#
    Case "●": Sym2Val = 6#
    Case "△": Sym2Val = 5#
    Case "▲": Sym2Val = 4#
    Case "□": Sym2Val = 3#
    Case "☆": Sym2Val = 2#
    Case "?": Sym2Val = 1#
    Case Else: Sym2Val = -1#
    End Select
End Function
id:zuguimo

ありがとうございます。一応目先の目標は達成されそうですが A = A +1 の部分で、例えばSUM関数のようなものが使えればと思っているのですが欲張りすぎでしょうか?もしあればよろしくお願いします。

2007/09/26 01:11:44
id:ardarim No.3

ardarim回答回数892ベストアンサー獲得回数1422007/09/26 05:36:32

ポイント30pt

上で回答されているとおりですが、一応自分なりの実装をしてみました。

データが「規則どおり」並べられていると仮定できるなら、Instrとかを使っちゃいますね。

Option Explicit
Option Base 0

Sub CalcAverage()

    Dim r As Long, c As Long
    Dim i As Long, m As Long
    Dim v(8) As Long
    Dim s As Double
    Dim avg As Double
    
    r = 2
    Do While ActiveSheet.Cells(r, 1).Value <> ""
        m = 0
        For c = 2 To 9
            v(m) = InStr(" ?☆□▲△●○◎■", ActiveSheet.Cells(r, c).Value) - 1
            If v(m) > 0 Then
                m = m + 1
            End If
        Next c
        If m > 0 Then
            ' ---- 平均
            s = 0
            For i = 0 To m - 1
                s = s + v(i)
            Next i
            avg = s / m
            ActiveSheet.Cells(r, "J").Value = avg
            ' ---- 標準偏差
            s = 0
            For i = 0 To m - 1
                s = s + (v(i) - avg) ^ 2
            Next i
            ActiveSheet.Cells(r, "K").Value = Sqr(s / m)
        End If
        r = r + 1
    Loop

End Sub

ちなみに上のコメントへの回答ですが、マクロではSumのような統計関数は用意されていないので、自分で計算するしかないのです。

統計関数が使えるのはセル内での計算式だけです。一時的にセルの記号を数値に置き換えて、J列にSUM式を入れれば目的のことはできると思いますが、あまりマクロにする意味がないですね。

id:zuguimo

ありがとうございました。勉強になりました。

2007/09/26 12:18:04
  • id:Mook
    コメントの内容が良くわからないのですが、
    A = A +1 の部分とはどこをさしているのでしょうか。
  • id:zuguimo
    すみません。
    cSum = cSum + ret
    sqSum = sqSum + ret ^ 2
    cnt = cnt + 1
    以上が A=A+1 の部分です。
    また、MOOKさんのの方法で試しましたが平均、標準偏差とも実際と合いませんでした。(AVERAGE,STDEV関数使用)

        平均       標準偏差
    マクロ4.571428571  1.916629695
    関数 5        2.267786838
    対策あればお願いします。


  • id:Mook
    計算列が1列足りませんでした。
      For j = 2 To 8

      For j = 2 To 9
    にして下さい。

    ちなみに私の回答した式に該当するEXCEL の標準偏差の関数は
      STDEVP
    になります。
  • id:zuguimo
    ありがとうございます。コメントを書いた後に気がつきました。

この質問への反応(ブックマークコメント)

トラックバック

  • マクロと関数は仲良し  question:1190727213にExcel上のワークシート関数は、マクロの中で使えないように記載がありましたけど、、 そんなことはないですね。自由に使えます。 関数の引数は
「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません