エクセルでの集計作業について

質問

30名ほどの朝食・昼食・夕食の食事の1ヶ月の集計をしたいのですが
ややこしいことにとＡさんは毎日食事をとるが、Ｂさんは金曜日はいらない
Ｃさんは月曜日・木曜日のみだけ食事をとるなど・・・（そしてキャンセルという場合もあります）

今はエクセルで地道に個人ごと朝・昼・晩と食事をとったら「1」と入力し
月末にＳＵＭ関数で集計しています。

エクセルでもっと簡単に集計できるようになるでしょうか？
例えば、詳しくないですがマクロやＶＢＡなどで。
個別の基本情報が自動的に出来ている状態で、食事キャンセルの場合
キャンセル処理をし、臨時で食事を取る場合追加などの処理を行うことはできるでしょうか？

回答No.5

MackyNo1 · ベストアンサー

No4の回答をして気が付いたのですが、わざわざ計算負荷が高く複雑な関数を提示してしまったようです。

私の例示したレイアウトなら（データ範囲がB列からCP列までで、X9セルに「朝」と入力されている）以下の式を入力して右方向および下方向にオートフィルするだけで集計できます。

=SUMIF($B$2:$CP$2,X$9,$B3:$CP3)

お礼

回答ありがとうございます。
今日、会社で表を作ってみたのですが、ちょっと人数が多かった為
予定変更の修正でセルが赤く塗りつぶされるという条件付書式の設定から
ちょっと手間取っています。
少し時間をかけて作っていきたいと思います。
ありがとうございました。

補足

何度も回答ありがとうございます。
昨日今日と会社が休みのため、明日会社で表を作ってみたいと思います。
（自宅のエクセルが2000のため作れないので・・・）

また表を作った後にコメントさせていただきたいと思います。
宜しくお願いいたします。

回答No.4

MackyNo1

>最終的に月末に朝・昼・夕の合計を出したいと思っていますが
やっぱり一番最後の列にAさんの朝の合計だったら
＝B3+E3+H3・・・というような数式しかないでしょうか？

規則的に飛び飛びのセルを集計（B列が開始セルで3列ごとに集計）するなら以下のような関数を利用します。
添付画像X10セルに入力して下方向にオートフィルコピーしてください

=SUMPRODUCT($B3:$CP3,((MOD(COLUMN($B3:$CP3),3))=2)*1)

C列が開始列なら以下の式になります（Y10セル：X10セルを右方向にオートフィルして「2」を「0」に変更する）。

=SUMPRODUCT($B3:$CP3,((MOD(COLUMN($B3:$CP3),3))=0)*1)

同様にD列が開始セルなら以下の式になります（Z10セル）。

=SUMPRODUCT($B3:$CP3,((MOD(COLUMN($B3:$CP3),3))=1)*1)

回答No.3

MackyNo1

No2の回答の補足です。

私の提示した数式は未来の日付まで表示していますが、未確定の今日以後の日付は実績を表示しないようにする（たとえば現時点での集計をしたい場合など）には、数式を以下のように変更してください。

=IF(OFFSET(B$1,0,-MOD(COLUMN(A:A)-1,3))>TODAY(),元の式）

補足

回答ありがとうございます。
使用したことがない関数ばかりで感心して見ています。
最終的に月末に朝・昼・夕の合計を出したいと思っていますが
やっぱり一番最後の列にAさんの朝の合計だったら
＝B3+E3+H3・・・というような数式しかないでしょうか？

回答No.2

MackyNo1

関数で対応するなら以下のようなシステムが簡単です。

まず準備として対象者の曜日と朝～夕食の注文パタンの表B3を作成しておきます（添付画像下）。

次に、B1:B3セルを結合し、セルの表示形式をユーザー定義にして「m/d(aaa)」と設定しておき、このセルに開始日（たとえば3/1）を入力し右方向にオートフィルします（添付画像上）。

B3セルに以下の式を入力し、右方向および下方向にオートフィルすれば、対応する日の予定の食事が自動入力されます。

=INDEX($B10:$V10,MATCH(TEXT(OFFSET(B$1,0,-MOD(COLUMN(A:A)-1,3)),"aaa"),$B$8:$V$8,0)+MOD(COLUMN(A:A)-1,3))

次にB3セル以下の数式範囲を選択してホームタブの条件付き書式で、「数式を使って・・・」を選択して以下の式を入力し「書式」から塗りつぶしを赤にします。

=INDEX($B10:$V10,MATCH(TEXT(OFFSET(B$1,0,-MOD(COLUMN(A:A)-1,3)),"aaa"),$B$8:$V$8,0)+MOD(COLUMN(A:A)-1,3))<>B3

このようにしておけば、予定変更で修正を加えたセルだけ赤く塗りつぶしされます。