人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

エクセルのマクロについて質問です。
配列のそれぞれを関数で変換して別の配列にしてその配列を使った関数の答えを取得したいのです。

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

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

●質問者: zuguimo
●カテゴリ:ビジネス・経営 コンピュータ
✍キーワード:28 エクセル セル データ ベル
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● y3kz
●30ポイント

空白セルは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
◎質問者からの返答

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


2 ● Mook
●40ポイント

基本的な構造は 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
◎質問者からの返答

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


3 ● ardarim
●30ポイント

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

データが「規則どおり」並べられていると仮定できるなら、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式を入れれば目的のことはできると思いますが、あまりマクロにする意味がないですね。

◎質問者からの返答

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

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ