エクセルで経費帳簿作成～完成への道～リターン

すいませんtom04さん来て頂きましてありがとうございます。
写真の乗せ方が悪くて配置がわかりずらかったですよね。
実は下記写真上のように経費と材料の経費が分かれてるため56行目に数式が入っています
下記写真上の左側に１ヶ月の経費と材料代が羅列があり右上に経費の１０日ごとの集計で下に材料の集計の形の１ヶ月のシートが１２か月分と経費の項目をリストにしたシートと材料の項目のリストのシートで目次に各12ヶ月へのリンクと月単位の経費と材料が集計してある目次のシートになってます
この形では実際帳簿をつけるとなるとわかりづらいでしょうか？その辺も含めてアドバイスして頂けたませんでしょうか？かってわがままでご迷惑でしょうがなにとぞよろしくお願いします。ほんとうに申し訳ございません。実際に自分が今山のようにたまった領収書を早く片付けたいものであせってます。

No.3 ベストアンサー 回答者： tom04 回答日時： 2015/04/25 22:20

No.1です。

＞ただ、なぜか画像上のガソリン代とかが重複したりとうまく作動しません。ただ材料のほうは同じ式なのにうまく作動します。

ん～～～
そのような状態は1月～12月のSheetなのか？それとも月単位の集計用のSheetなのか？
またはどちらのSheetもそうなのでしょうか？

材料の方は同じ式でちゃんと表示されているというコトですので、次のコトを確認してみてください。
①　勘定科目の作業列（F～H列）が前回通りになっているか？
②　各Sheetの「勘定科目」表示列の数式がG列を参照しているか？

※　前回アップした画像通りの配置で回答した数式がちゃんと入っていれば
こちらで検証し投稿していますので、ちょっと原因が判りかねます。

実際のサンプルを少しでも提示してもらえればこちらでも確認ができるのですが・・・
（セル配置＆数式など）

この程度でごめんなさい。m(_ _)m

No.2 回答者： tom04 回答日時： 2015/04/23 22:19

No.1です。

https://oshiete.goo.ne.jp/qa/8968503.html

の質問を拝見して、作業列の数式を変更する必要を感じました。
金額欄が空白の場合、無視する場合は

F4セルを
=IF(COUNTBLANK(B4:E4),"",DATE(YEAR(B4),MONTH(B4),IF(DAY(B4)<11,1,IF(DAY(B4)<21,11,21))))

O4セルを
=IF(COUNTBLANK(I4:L4),"",DATE(YEAR(I4),MONTH(I4),IF(DAY(I4)<11,1,IF(DAY(I4)<21,11,21))))

にそれぞれ変更してください。

他の数式はそのままで大丈夫だと思います。m(_ _)m

No.1 回答者： tom04 回答日時： 2015/04/23 21:46

こんばんは！

なんとなく見えてきました。

↓の画像の上側のように1月毎のSheetが12枚あり、下側の別Sheetに各月ごとの集計をやりたい！
という解釈です。
画像では「1月」のSheetしか見えませんが、12か月分のSheetは同じ配置だとします。
尚、各Sheet名は　1月　2月　・・・12月　のように半角数値と「月」になっているという前提です。

「1月」Sheetをアクティブにし、Shiftキーを押しながらSheet見出しの「12月」をクリック！
これで「1月」～「12月」Sheetが作業グループ化されましたので、Sheet1に数式を入れていきます。

各Sheetとも同じ列に作業用の列を設けます。
F～H列が「勘定科目」と「勘定科目の月毎の集計」用の作業列、O～Q列が「材料名」と「材料名の月毎の集計」用の作業列となります。
F4セルに
=IF(B4="","",DATE(YEAR(B4),MONTH(B4),IF(DAY(B4)<11,1,IF(DAY(B4)<21,11,21))))

G4セルに
=IF(COUNTIFS(D$4:D4,D4,F$4:F4,F4)=1,F4,"")

H4セルに
=IF(COUNTIF(D$4:D4,D4)=1,ROW(),"")

O4セルに
=IF(I4="","",DATE(YEAR(I4),MONTH(I4),IF(DAY(I4)<11,1,IF(DAY(I4)<21,11,21))))

P4セルに
=IF(COUNTIFS(K$4:K4,K4,O$4:O4,O4)=1,O4,"")

Q4セルに
=IF(COUNTIF(K$4:K4,K4)=1,ROW(),"")

という数式を入れすべての列のフィルハンドルで下へコピー！

S5（配列数式です）に
=IFERROR(INDEX($D$1:$D$100,SMALL(IF($G$1:$G$100=S$3,ROW($G$1:$G$100)),ROW(A1))),"")
として、Ctrl＋Shift＋Enterで確定！

U5セルに
=IF(S5="","",SUMIF($D:$D,S5,$E:$E))
とし、S5～U5セルを範囲指定　→　四辺の上下左右の矢印になるところでCtrlキーを押しながら
W～Y列・AA～AC列にぞれぞれドラッグ
最後に各列をフィルハンドルで下へコピー！

同様にS15（配列数式）に
=IFERROR(INDEX($K$1:$K$100,SMALL(IF(($O$1:$O$100=S$13)*($Q$1:$Q$100<>""),ROW($O$1:$O$100)),ROW(A1))),"")
とし、Ctrl＋Shift＋Enterで確定

U15セルに
=IF(S15="","",SUMIFS($L:$L,$K:$K,S15,$O:$O,S$13))

とし、これもCtrlキーを押しながらW～Y列・AA～AC列へドラッグ！
さらに下へコピー＆ペースト
これで各Sheetの表示はできるはずです。
Sheet見出し上で右クリック　→　「作業グループ」解除

今度は下側画像の月別の集計Sheetになります（配列数式はありません）
E5セルに
=IFERROR(INDEX(INDIRECT(MONTH(E$3)&"月!$D:$D"),SMALL(INDIRECT(MONTH(E$3)&"月!"&"$H:$H"),ROW(A1))),"")

G5セルに
=IF(E5="","",SUMIF(INDIRECT(MONTH(E$3)&"月!$D:$D"),E5,INDIRECT(MONTH(E$3)&"月!$E:$E")))

とし、これもCtrlキーを押しながら右の次の月へ・・・と順次ドラッグ！

E56セルは
=IFERROR(INDEX(INDIRECT(MONTH(E$54)&"月!$K:$K"),SMALL(INDIRECT(MONTH(E$54)&"月!$Q:$Q"),ROW(A1))),"")

G56セルは
=IF(E56="","",SUMIF(INDIRECT(MONTH(E$54)&"月!$K:$K"),E56,INDIRECT(MONTH(E$54)&"月!$L:$L")))

とし、これもCtrlキーを押しながら右の月へ・・・と順次ドラッグ！

これで何とかご希望通りにならないでしょうか？

※　行配置は異なってもちゃんと表示されると思いますが、
列の配置は画像の配置と違うと思い通りの結果にならないと思います。

※　画像が小さくて判りにくいと思いますので
拡大して確認してください。m(_ _)m

この回答へのお礼

自分の作成使用としているとおりに説明とアドバイスをしていただきありがとうございます。ただ、なぜか画像上のガソリン代とかが重複したりとうまく作動しません。ただ材料のほうは同じ式なのにうまく作動します。しょうじきどうしたらよいですか？

お礼日時：2015/04/25 19:58