エクセルで記号ごとに加算した結果を表示

質問

Ａさん　　Ｂさん
１日　　　○　　　　●
２日　　　●　　　　◇
:
３１日　　△　　　　○
合計　　　？　　　　？

上のような表があり、○は８時間、●も８時間、△は６時間、◇は４時間と計算し、合計欄に数字がはいるような式を教えてください。記号はパターンが増えることもあり、そのたびに式をかえなくてもすむようにしたいです。よろしくお願いします。

回答No.4

mu2011 · ベストアンサー

ご回答者と同じ、配列を使用して次の方法で如何でしょうか。
(1)合計関数
=SUM((COUNTIF(A1:An,シート2!$A$2:$A$n)*シート2!$A$1),COUNTIF(A1:An,シート2!$B$2:$B$n)*シート2!$B$1,COUNTIF(A1:An,シート2!$C$2:$C$n)*シート2!$B$1))
入力完了時、ｓｈｉｆｔ＆ｃｔｒｌ＆ｅｎｔｅｒとして式が｛｝で囲まれるようにして下さい。

(2)別シートに記号と係数を作成
　Ａ列　Ｂ列　Ｃ列
　８　　６　　４
　○　　△　　◇
　●

記号が増減する場合は別シートを更新するだけですが、計算パターンが増える場合は計算式の追加が必要です。

お礼

これならできそうです。やってみます。

回答No.3

KenKen_SP

私も他の方が回答されてます、配列数式が良いと思います。（なるべくマクロを使わないという意味で）
ただ、

＞そのたびに式をかえなくてもすむようにしたいです..

という事情であれば、関数をネストさせるよりVBAマクロでユーザー定義関数を作ってしまった方が楽かも。

この方法のメリットは、メンテナンス性が良いことです。記号を追加する場合は、組み込み関数による方法では数式の入ったセル全て修正が必要ですが、ユーザー定義関数の場合、VBAマクロのコードを修正するだけで済みます。
デメリットは、マクロを使用することです。

サンプルコードを挙げておきます。ご参考までに。

標準モジュールに貼り付けて、通常のワークシート関数のように使用します。

EX) =MarkSum(A2:A32)

Function MarkSum(TargetArea As Range)

　　Dim Buf, tmp
　　Dim rngCel As Range

　　Buf = 0
　　For Each rngCel In TargetArea
　　　　Select Case rngCel.Value
　　　　　　'記号と値を対応させる部分
　　　　　　'記号を増やすなら同様に追加して下さい
　　　　　　Case Is = "○": tmp = "8:00"
　　　　　　Case Is = "●": tmp = "8:00"
　　　　　　Case Is = "△": tmp = "6:00"
　　　　　　Case Is = "◇": tmp = "4:00"
　　　　　　'どれにも該当しなければゼロ
　　　　　　Case Else: tmp = 0
　　　　End Select
　　　　Buf = Buf + CDate(tmp)
　　　　tmp = 0
　　Next rngCel
　　MarkSum = Buf

End Function

お礼

ちょっと私にはむずかしそうです。参考にします。ありがとうございました。

回答No.2

SAKURAMYLOVE

A2～A32まで1日～31日が、ＡさんがB1に入力されているとして、B33に以下の式を入力し、SHIFT+Crtlを押しながら、ENTERキーを押します。

=SUM((B2:B32="○")*8+(B2:B32="●")*8+(B2:B32="△")*6+(B2:B32="◇")*4)

式の両側に{=SUM・・・}となったら、ＯＫです。
以下、Ｂさんはその式をコピーしC33に貼り付けていきます。

お礼

参考にします。ありがとうございました。

回答No.1

shkwta

記号が変わらないなら,
=SUM(IF(A2:A32="○",8,IF(A2:A32="●",8,IF(A2:A32="△",6,IF(A2:A32="◇",4,0)))))
このような式を入れて Shift+Ctrl+Enterを押すと配列数式として計算され、合計が出ます。

ところが、お望みの
＞記号はパターンが増えることもあり、そのたびに式をかえなくてもすむようにしたいです。
を実現するには、別に記号の一覧表を作っておかなければなりません。これは、やや複雑です。

シートを３つ用意します。
Sheet1は、ご質問のものです。
Sheet2のA列とB列に、記号の一覧表を作ります。
○　8
●　8
△　6
◇　4
Sheet3に、記号を数字に置き換えたものを求めます。
=VLOOKUP(Sheet1!A2,$A:$B,2,FALSE)
これをA2に入れ、たとえばA2～N32など、日数分・人数分の範囲にコピーします。

Sheet1上に戻り、Sheet3の合計を求めます。
=SUM(Sheet3!A2:A32)

お礼

参考になりました。
式をかえないでやるには、複雑なほうでないとだめですね。試してみたいと思います。

補足

ちなみに、表にはなんにも記号がないところがあります。つまり、ブランクは０で計算したいのです。
教えていただいた数式ではエラーがでるので、どうしたらいいですか？