条件にあった偶数行の合計の算出方法について(2)

質問

こちらで下記の質問を行い、回答していただいた数式を利用して希望していたことが可能になりました。しかし、どのような数式を組めばよいのかわからない箇所がもう3つ出てきましたので、また教えてください。よろしくお願いします。

↓前回の質問と回答
https://okwave.jp/qa/q9333427.html

【質問１】添付画像(1)の37行目は数式バーの数式で合計値を算出することができましたが、それを基に今度は赤字Aの赤枠内にある箇所の数値が出てくるようにしたいので、その数式を教えてください。
平日：その月の平日（月～金）に数字が入っている日をカウント
長期休業日：数式必要なし
休日：その月の土日に数字が入っている日をカウント

【質問２】添付画像の(2)の上表から赤字Bの赤枠内の数値を算出する数式を教えてください。（上表の色のついた背景色の意味⇒水色：土、赤：日、ピンク：祝日、黄緑：長期休業日）数字の前に付いているhやvは集計をするために必要なので、そのままで、赤字Bの赤枠内の結果が出るようにしたいです。赤字Bの赤枠内の結果にはhやvが先頭についていてもかまいません。

【質問3】添付画像の(2)の下表、赤字Bの赤枠内の数値を基に赤字Cの赤枠内の数値を算出する数式を教えてください。
平日：その月の平日（月～金）に数字が入っている日をカウント
長期休業日：添付画像の(2)の上表で黄緑色の背景部分に数字が入っている日をカウント
休日：その月の土日に数字が入っている日をカウント

回答No.2

bunjii · ベストアンサー

>”数式内で数値の配列を生成する”方法とは具体的にはどういうことでしょうか。もしよろしければ、一例を教えていただけないでしょうか。
5月のデータを例にすると次の数式で合計が得られます。
=SUM(IFERROR((SUBSTITUTE(C14:AG14,"h","")*1),0))
内側の関数から説明します。
SUBSTITUTE関数でC14:AG14の各セルの値から"h"を""に置き換えます。
例、1→"1"、"h3"→"3"、未入力→""
それを1倍（*1）するとVALUE関数で数値化したものと同じになりますが、数値化できない""はエラーになるのでIFERROR関数を使って0に置き換えます。
その結果は数値化された配列値なので、外側のSUM関数に渡せば5月分の合計になります。
貼付画像は関数の引数を確認するため数式バーの左にあるfxボタンをクリックして表示させたものです。

お礼

お礼が遅くなり大変申し訳ありません。一例を教えていただき、ありがとうございます。”数式内で数値の配列を生成する”方法がわからなかったため、計算用シートを作成し、形式を指定して貼り付け⇒リンク貼り付けでデータをコピー、一文字目がhかvであれば、replace関数で""に置き換え、全体をvalueで括り数値化して集計していました・・・。”数式内で数値の配列を生成する”方法であれば、こんなにもスマートに集計できるのですね。大変勉強になりました。今後の業務に活かしたいと思います。いろいろと詳しく教えていただき、本当にありがとうございました。

回答No.1

bunjii

>それを基に今度は赤字Aの赤枠内にある箇所の数値が出てくるようにしたいので、その数式を教えてください。
Excel 2007以降のバージョンにはSUMIFS関数がありますので簡単に集計できるはずです。
複数の条件にマッチするセルのみの数値を合計する関数なので条件の論理はあなた自身で考えてみることをお薦めします。

>数字の前に付いているhやvは集計をするために必要なので、そのままで、赤字Bの赤枠内の結果が出るようにしたいです。
hとvは表示形式で数値の先頭に文字を表示しているのでしょうか？、それとも、文字列の値として入力されているのですか？
文字列の場合はhまたはvの文字を取り除いた数値に変換しないと合計できませんので計算用の別表を生成させるか、数式内で数値の配列を生成する必要があります。
尚、表示形式で実体の数値を文字付で表示しているのであれば、その表示文字を手掛かりに集計することは難しい問題です。（組み込み関数には検知できるものが見当たらない）

>赤字Bの赤枠内の数値を基に赤字Cの赤枠内の数値を算出する数式を教えてください。
前述のように入力されている値の実体が説明されていないので具体的な数式を提示することはできません。
あなた自身がSUMIFS関数を駆使して組み立ててみることをお薦めします。
模擬データとして提示されている画像は各セルの値の実体が見えませんので条件に一致するセルを抽出する論理が組めません。
従って、実体の値を知っているあなたが数式を組み立てないと正しい集計になりません。
あなたが考えた数式を提示して頂ければ添削することは可能です。

お礼

早速のご回答ありがとうございます。Aの「休日」は、=COUNTIFS($BB$36:$CF$36,"1",$BB$37:$CF$37,">=1")+COUNTIFS($BB$36:$CF$36,"7",$BB$37:$CF$37,">=1")で希望した結果が算出できました。BB36に、IF(OR($BA$37=1,$BA$37=2,$BA$37=3),DATE($A$3,$BA$37,BB35),DATE($A$2,$BA$37,BB35))と数式を入力し、BC36には「BB36＋1」と数式を入れていたことが、カウントされない原因になっていたようです。「休日」の数式を活かすため、BB36は、=WEEKDAY(IF(OR($BA$37=1,$BA$37=2,$BA$37=3),DATE($A$3,$BA$37,BB35),DATE($A$2,$BA$37,BB35)))に変更し、BC36は=WEEKDAY(IF(OR($BA$37=1,$BA$37=2,$BA$37=3),DATE($A$3,$BA$37,BC35),DATE($A$2,$BA$37,BC35)))に変更しました。「平日」は0以外の数字を全てカウントし、「休日」の数字を引くことで算出できました。（=COUNTIF(BB37:CF37,">=1")-CI36）

添付画像(2)の「h（holiday:休日）」や「v(vacation：長期休暇)」は平日、休日、長期休暇と分けて利用時間や日数を集計しなくてはならないため文字列として入力しています。（例：=COUNTIF(C12:AG12,"H*")）

文字列が入っているとやはり合計はできないのですね。”数式内で数値の配列を生成する”方法とは具体的にはどういうことでしょうか。もしよろしければ、一例を教えていただけないでしょうか。