[[20031202102712]]　『職種毎の合計？』（かっち）　ページの最後に飛ぶ

『職種毎の合計？』（かっち）

 いつも大変お世話になっております。
 今回は職種毎の合計d2:d30までを求めたいのですが・・・
 　　　a　　　　　　　　　　b　　　　　　　　　c　　　　　　　d
 1　   職種（日毎）　　　 人員　　　　　　　職種合計　　　　　合計
 2　　仮設工　 　　　　　２＋３　　　　　　　仮設工　　　　　　５
 3　　衛生設備工　　　　 １＋１　　　　　　　土工　　　　　　　９
 4　　空調設備工　　　　 ２＋１　　　　　　　衛生設備工　　　　３
 5　　電気設備工　　　　 ０＋５　　　　　　　空調設備工　　　　１
 6　　土工　　　　　     ７＋０　　　　　　　電気設備工　　　　５
 7　  塗装工　　　　     １＋１　　　　　　　塗装工　　　　　　２
 8　　解体工　　　　　　 ０＋１　　　　 　　 美装工
 9　　衛生設備工　　　　 ３＋２　　　　 　　 鉄筋工　　　　　
 10　 土工　　　　　　　 ０＋２　　　 　　　 鉄骨工
　　　　　　　　　　　　　　　　　　　　　　 解体工　　　　　　１
 12　 　　　　　　　　　　　　　　　　　　　 設備電気社員　　　６

  ① a2:b30には、当日の職種人員を入力します。人員には＊＋＊のように
　　人員を分けて入力しています。（表示も同じように）左が社員。右が従業員
　② b2:b30で入力した人員の合計を職種が同じものどおしで＋の左と右を合計し
　　 d2:d30に求めます。
　　　その際、a3,a4,a5は＋を境に左の合計だけをd12へ、右の金額はそれぞれd4,d5,d6
　　　に計算したい。
　③ 職種の種類はc2:c30までとします。

 sumif、right、leftなどでいろいろチャレンジしてみましたがどうしても
 自力ではできません。
 説明がへたくそで申し訳ございませんが、どなたか教えていただけないでしょうか？

 なにか無理に難しくしているような？
 １．B列を3列構成にする（B　C　D）
 ２．C列を「＋」の入力セルにする
 以上で単純にSUM関数で求めることが出来ます。

 どうしてもひとつのセルへ入力する方法が必要な人には
 D2を選択、←忘れないで！
 「挿入」→「名前」→「定義」で
 「名前」をMYCALC
 「参照範囲」へ
  =EVALUATE(INDIRECT(ADDRESS(MATCH(C2,$A$2:$A$100,0),2,1)))+NOW()*0
 として「OK」
 D2へ
 =MYCALC
 として、以降の列へフィル。 
 ただし、この方法はかなりイレギュラーな方法なので・・・
 最初の方法をお勧めします。
 (KAMIYA)

 上記「没」です!
 同じ職種が複数A列にありますね･･･。
 これでは一番上の行のものしか計算してくれません。
 もう少し他の方法で考えて見ます。
 (KAMIYA)

 KAMIYA様、早速ありがとうございます。
 KAMIYAさまの言うとおりb列を分けて計算すれば簡単なような気がしますが
 どうしてもひとつのセルで計算したいのです。
 宜しくお願いいたします。
　（かっち）

 多分これでいけると思います。
 D2へ
 =SUMPRODUCT(($A$2:$A$10=C2)*((LEFT($B$2:$B$10,LEN($B$2:$B$10)-FIND("＋",$B$2:$B$10)))+RIGHT($B$2:$B$10,LEN($B$2:$B$10)-FIND("＋",$B$2:$B$10))))
 を入力して、下の行へフィル。
 $A$2:$A$10、$B$2:$B$10は職種、人員の範囲なので、適当に調節してください。

 (KAMIYA)

 念のため追記「＋」は全角で検索してますので、半角の場合は
 式中の「＋」を半角に変更してください。
 ＋がなかったり未入力のセルがあるとすべての式がエラーになります。
　また、この部分の計算がどうしても計算が合わないのですが･･･

 　② b2:b30で入力した人員の合計を職種が同じものどおしで＋の左と右を合計し
 　　 d2:d30に求めます。
 　　　その際、a3,a4,a5は＋を境に左の合計だけをd12へ、右の金額はそれぞれd4,d5,d6
 　　　に計算したい。

 ひょっとして、「？？設備工の左側の数値の合計」でしょうか。

 (KAMIYA)

 いや、どうも根本的に思い違いをしていたようです。
 何回も修正ご迷惑をおかけします。
 多分こういうことでしょう。

 C4 衛生設備工 
 D4=SUMPRODUCT(($A$2:$A$30=C4)*RIGHT($B$2:$B$30,LEN($B$2:$B$30)-FIND("＋",$B$2:$B$30)))
 C5 空調設備工 
 D5=SUMPRODUCT(($A$2:$A$30=C5)*RIGHT($B$2:$B$30,LEN($B$2:$B$30)-FIND("＋",$B$2:$B$30)))
 C6 電気設備工 
 D6=SUMPRODUCT(($A$2:$A$30=C6)*RIGHT($B$2:$B$30,LEN($B$2:$B$30)-FIND("＋",$B$2:$B$30)))
 C12 設備電気社員
 D12=SUMPRODUCT((ISERROR(FIND("設備工",$A$2:$A$30))=FALSE)*((LEFT($B$2:$B$30,LEN($B$2:$B$30)-FIND("＋",$B$2:$B$30)))))

 他の職種
  =SUMPRODUCT(($A$2:$A$30=C2)*((LEFT($B$2:$B$30,LEN($B$2:$B$30)-FIND("＋",$B$2:$B$30)))+RIGHT($B$2:$B$30,LEN($B$2:$B$30)-FIND("＋",$B$2:$B$30))))

 でいかがでしょうか。
 (KAMIYA)

 KAMIYAさま。
 申し訳ございません。VALUEになってしまいます。
 +は半角入力なので半角に直して作成したのですが・・・。
 （かっち）

 KAMIYAさま。
 すいません。答えがでました。
 説明不足だったのでもう少しお願いしてよろしいでしょうか？
 A2:A30には全ての行が入力されるとは限りません。
 空白行が必ずでてしまいます。
 A2:B10までしかデータが今回は入っていませんが、翌日はA2:B15までだとか
 日によってばらばらです。　KAMIYA様の数式を入力したとき最初VALUEになって
 しまいましたが $A$2:$A$30→$A$2:$A$10　$B$2:$B$30→$B$2:$B$10
 に直して見ると答えがきちんと表示されました。
 毎日範囲を変更するのは大変なのでなんとかならないでしょうか？
　何度もお手数かけまして申し訳ございません。
 （かっち）

 だと思いました。
 前掲「他の職種」を可変範囲にすると
 =SUMPRODUCT((INDIRECT("$A$2:$A$"&COUNTA(A:A))=C2)*((LEFT(INDIRECT("$B$2:$B$"&COUNTA(B:B)),LEN(INDIRECT("$B$2:$B$"&COUNTA(B:B)))-FIND("+",INDIRECT("$B$2:$B$"&COUNTA(B:B)))))+RIGHT(INDIRECT("$B$2:$B$"&COUNTA(B:B)),LEN(INDIRECT("$B$2:$B$"&COUNTA(B:B)))-FIND("+",INDIRECT("$B$2:$B$"&COUNTA(B:B))))))
 なんてことになって、後から見ても何を計算しているのかさっぱりわからないことになります。

 ちょっと他の方法を使って整理します。
 A列とB列に可変の範囲名を設定します。
 「挿入」→「名前」→「定義」
 「名前」を「職種」
 「参照範囲」「=INDIRECT("$A$2:$A$"&COUNTA(Sheet1!$A:$A))」とし「追加」

 「名前」を「人員」
 「参照範囲」「=INDIRECT("$B$2:$B$"&COUNTA(Sheet1!$B:$B))」とし「追加」、「OK」

 C4 衛生設備工 
 D4=SUMPRODUCT(((職種=C4)*RIGHT(人員,LEN(人員)-FIND("+",人員))))
 C5 空調設備工 
 D5=SUMPRODUCT(((職種=C4)*RIGHT(人員,LEN(人員)-FIND("+",人員))))
 C6 電気設備工 
 D6=SUMPRODUCT(((職種=C4)*RIGHT(人員,LEN(人員)-FIND("+",人員))))
 C12 設備電気社員
 D12=SUMPRODUCT((ISERROR(FIND("設備工",(職種)))=FALSE)*((LEFT(人員,LEN(人員)-FIND("+",人員)))))

 他の職種（D2の場合）
  =SUMPRODUCT((職種=C2)*((LEFT(人員,LEN(人員)-FIND("+",人員)))+RIGHT(人員,LEN(人員)-FIND("+",人員))))
 でいかがでしょうか。
 (KAMIYA)

 おもしろそうなんで、私もよせておくんなはれ。
 これは、ユーザー関数を使うていますさかい、下のコードをコピペしてＤ２に=kei(c2)
 と入力して、必要分だけ下方向にコピーするとお望みの数字が出力されます。
 空調設備工、電気設備工、衛生設備工、設備電気社員等はどの行に有ってもＯＫです。

 コードのコピーですけどな。
 Alt+F11でVBEを開く
 「挿入」→「標準モジュール」開いてコードをコピペ
 Alt+Qでエクセルに戻り
 前述してようにＤ２に=kei(c2)と打ち込んでみておくんなはれ。
 　　ほな...（弥太郎）

 Function kei(data)
    Dim i As Integer, n As Integer
    Dim repl As String
    Dim data_a

    Application.Volatile
    Select Case data
        Case "設備電気社員"
            For i = 2 To 30
                If Cells(i, 1) = "衛生設備工" Or Cells(i, 1) = _
                    "空調設備工" Or Cells(i, 1) = "電気設備工" Then
                    repl = StrConv(Cells(i, 2), vbNarrow)
                    data_a = Split(repl, "+")
                    n = data_a(0) * 1
                    totl = totl + n
                End If
            Next i
        Case "衛生設備工", "空調設備工", "電気設備工"
            For i = 2 To 30
                If Cells(i, 1) = data Then
                    repl = StrConv(Cells(i, 2), vbNarrow)
                    data_a = Split(repl, "+")
                    t = data_a(1) * 1
                    totl = totl + t
                End If
            Next i
        Case Else
            For i = 2 To 30
                If Cells(i, 1) = data Then
                    repl = StrConv(Cells(i, 2), vbNarrow)
                    data_a = Split(repl, "+")
                    n = data_a(0) * 1
                    t = data_a(1) * 1
                    totl = totl + n + t
                End If
            Next i
    End Select
    kei = totl

 End Function

 KAMIYA様、大変ありがとうございます・・・。
 でも、なぜだかエラーが表示され計算されません。
 無効な名前のエラーとでてしまいます。
 今更何ですが、
　職種の範囲は実際は$A$2:$E$30（A:E）
  人員の範囲は$R$2:$S$30（R:S）結合されています。
  ここの掲示板にのせるためセルを結合しない方が解りやすいのかと思い
　のせてしまいました。
  もしかしてこれが原因でエラーがでるのでしょうか。
　本当に本当に申し訳ございません。
 ちなみに範囲名は以下のように設定したのですが・・・。
 =INDIRECT(作業シート!"$A$2:$E$30"&COUNTA(作業シート!$A$2:$E$30))
 =INDIRECT(作業シート!"$R$2:$S$30"&COUNTA(作業シート!$R$2:$S$30))

 弥太郎様、
 うわーVBAだ！！ちんぷんかんぷんです・・・。
 もう少し時間下さい。コピペして使ってみます。
 （かっち）

 結合セルの場合は左端セルが代表アドレスになります。
 ですから左端の列だけ気にすればよいということです。

 =INDIRECT("$A$2:$A$"&COUNTA(Sheet1!$A:$A))
 =INDIRECT(作業シート!"$A$2:$E$30"&COUNTA(作業シート!$A$2:$E$30))

 上の二つの式を比べてみてください。
 INDIRECT関数は「文字列から参照を返す関数｣です。
 ま、この場では覚えなくてよいので、とりあえず（　）の中に

 "作業シート!$A$2:$A$n"

 のようなセル範囲をあらわす文字列を作り出すことに気をつけます。

 結果から書きます。参照範囲を
 =INDIRECT("作業シート!$A$2:$A$"&COUNTA(作業シート!$A$2:$A$30))
 =INDIRECT("作業シート!$R$2:$R$"&COUNTA(作業シート!$R$2:$R$30))

 にしてみてください。今回はコピーしてテキストボックスへCtrl+Vが楽でしょう。
 COUNTA(作業シート!$A$2:$A$30)で"n"の行数を求めています。

 ＞弥太郎さん
 相変わらずすばやいコーディングですね！
 後でゆっくりやってみます。
 （ユーザー定義関数はなぜか苦手)
 (KAMIYA)

 KAMIYA様、長い間無知な私のために本当にありがとうございました。
 お陰で、無事完成することができました。
 詳しく説明まで書いていただき感謝です！
 これからもお世話になると思いますが宜しくお願い致します。
 （かっつ）

コメント返信：

[ 一覧(最新更新順) ]

[[20031202102712]] 『職種毎の合計？』（かっち） ページの最後に飛ぶ

[[20031202102712]]　『職種毎の合計？』（かっち）　ページの最後に飛ぶ