エクセルで収入と支出と利益を計算する単純なシートで、数年に渡る月次のデータ(sheet2)から、sheet1で指定した特定の期間の3ヶ月の収入や支出を検索として合計をする関数を作成しました(sheet2の右の方の罫線の箇所)。
SUM関数とINDIRECT関数、ADDRESS関数、MATCH関数を組み合わせているのですが、この状態で例えば収入(2)の後ろに収入(3)を挿入しようとすると、ADDRESS関数の行番号(ADDRESS(7,MATCH~)の数字の7のところ)
が自動的に8に変わらないので、都度関数を修正しなければなりません。行を挿入したり削除したりしたら、ADDRESS関数の行番号も増えたり減ったりするようにできる方法はないでしょうか。
もしくは、そもそももっと簡単で使いやすい関数はないでしょうか。
教えていただけるととても助かります。
No.1
- 回答日時:
・Sheet1での期間指定方法
年、月は別々のセルなのか?
○○年□月~○○年×月のように「期間」で指定するのか?
○○年□月のように単月で指定してそこから3ヶ月(前?後?)を計算するのか?
など。
・Sheet2のデータの項目とできれば具体的なデータの例
例えば2009年9月が「200909」という数値で入っているのか?
「2009年9月」という文字列で入っているのか?
「2009」と「9」という数値で別々のセルに入っているのか?
など。
・今、SUM関数、INDIRECT関数、ADDRESS関数、MATCH関数をどう組み合わせているのか?
・使っているExcelのバージョン(2007かそれ以前か)
を補足して下さい。
何となく質問を読んだ限りでは、SUMIFやSUMPRODUCTを
使ったほうがよさそうな気がしたのですが。
この回答への補足
早速のご回答ありがとうございます。エクセルファイルを添付しようとしたのですが、できないようで、時間切れになってしまいました。中途半端な質問になってしまい申し訳ございません。
Excelのバージョンは2003です。
sheet1には、入力日の始期(例2008/1/1)と、集計したい年度の期(例 第1期2009/1/1、第1期終期2009/3/31、第2期始期2009/4/1~第4期終期2009/12/31)の日付をセルに手入力しています。
sheet2には、sheet1を受けて、EOMONTH関数を使って、始期から5年分の毎月の収支表が作成されます。収入と支出は手入力します。そして、収支表と行を揃える形で、収支表の右側に、集計したい期(3ヶ月毎)のデータを合計して、第1期から第4期までのデータが表示されるといったものです。
そこで使っている関数は以下のようなものです(2009/1/1から3/31の3ヶ月間の、5行目の収入(1)の集計の場合)。
SUM(INDIRECT(ADDRESS(5,MATCH(検査値:2009/1/1,検査範囲:収支表の日付))):INDIRECT(ADDRESS(5,MATCH(検査値:2009/3/31,検査範囲:収支表の日付)))
要は、sheet1で集計したい日付を指定すれば、5年間のデータから該当の期日のデータの合計を自動的に集計するようにするものです。
SUM関数だけでは、集計したい期が変わる度に、都度合計するセル範囲を変更しなければならないので。
よろしくお願いいたします。
No.2
- 回答日時:
すみません。
再度補足要求です。>2009/1/1から3/31の3ヶ月間の、5行目の収入(1)の集計の場合
「2009/1/1から3/31の3ヶ月間の集計」であれば、
Sheet2から該当する期間のレコードを探して
それを集計、ということになるわけですから
行自体は指定した条件によって変動するはずです。
「5行目」と行を固定する意味がわかりません。
それと収入(1)の(1)とは何でしょうか?
収入の列が複数あるということでしょうか?
折角補足いただいたのですが、収支表の構成がイメージできません。
もう少し具体的に収支表の構成を
A列が何の項目でどんなデータが入っている(具体例:○○)
B列が何の項目でどんなデータが入っている(具体例:○○)
C列は…
で、集計したい列はどの列
のように記述していただけないでしょうか。
この回答への補足
説明不足でお手数をおかけします…
5行目としたのはあくまで例であって、私の作成している表では、収入と支出で全部で100項目(100行)はあります。しかも、案件によって、項目(行)が追加されたり削除されたりします。
もう少し具体的に申し上げますと、
A列には収入と支出の科目名(例:賃料、水光熱費等)があり、B列以降は、各月の各科目の金額が入力されています。B列の1行目には、Sheet1から引用した2008/1/1が入力されており、C列1行目には、次で説明するB列2行目の日付+1として、2008/2/1となり、D列は2008/3/1~と5年間続きます。2行目には1行目の日付にEOMONTH関数を使って、B列には2008/1/31、C列には2008/2/28~と、1行目の日付の月末の日付が5年間入力されています。
そして、5年間の収支表の右側には、Sheet1で指定した年度の第1期、2期、3期、4期の3ヶ月毎のデータを集計する表があります。
例えば、BA列の1行目にはsheet1から引用した2009/1/1、2行目には同じくsheet1から引用した2009/3/31とあり、3行目以下には、左側の各月の収支表から集計した2009年1月から3月の第1期の3ヶ月(3つのセルの)合計の数字が反映されます。BB列の1行目は4/1、2行目は6/30で、3行目以下には同じく第2期の3ヶ月合計の各収入・支出の数字が反映されます。毎月の収支表と集計する表は、行が平行になっています(2009年1月の賃料の数字が5行目なら、2009年1月~3月の賃料の合計を集計するセルも、5行目です)。これでわかりますでしょうか。
EXCELのシートが貼り付けられれば一目瞭然なのですが…
科目を増やしたり減らしたりするために、行を挿入したり削除したりもするのですが、そうするとADDRESS関数の行数が変わらないので、集計表で集計する数字が変わってしまうのは質問に記載している通りです。
よくありそうな集計シートですので、もっと単純な方法があるのではないかと思っています。よろしくお願いいたします。
No.3
- 回答日時:
縦軸に科目があって、横軸に月毎のデータがあって
ヘッダ行は1行目が月初日、2行目が月末日
実際のデータは3行目以降に入っている
集計列のヘッダは1行目が期首日付、2行目が期末日付
同じ行のデータのみを集計する
ということですか。
であればSUMPRODUCTを試してみてください。
画像をつけます。
No.4ベストアンサー
- 回答日時:
No.3ですが画像がちょっと見づらいですね。
表の作りはB列からJ列の9列に2009年1月~2009年9月のデータ
E~G列(4~6月)は非表示
L列、M列、N列でそれぞれ1~3月、4~6月、7~9月の集計
L3の式は
=SUMPRODUCT(($B$1:$J$1>=L$1)*($B$2:$J$2<=L$2)*($B3:$J3))
です(以下説明)
自分自身のセルと同じ行のB列からJ列のうち
B1:J1が自分自身のセルの1行目以上、かつ
B2:J2が自分自身のセルの2行目以下、であるセルの値を合計
絶対参照と相対参照が混ざっていますので$をつける場所を
間違えないように注意してください。
その代わり縦でも横でもコピー&ペースト可能です。
(図中黄色の範囲は全てL3の式を複写したものです)
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
パソコンからショートメールを...
-
ちょっとテレビを移動させた時...
-
パソコンで、LINEを使っていま...
-
3teneから2Dキャラ作成後、OBS...
-
TikTokののコメントなんですが...
-
ps3のシステムアップデートをし...
-
スマホの電話番号だけ変更する...
-
OPPO について教えてください。...
-
マイクの、息当たり音や接触・...
-
車載カーナビのSDカードに保存...
-
Googleアカウント再設定
-
彼氏のiPadを彼氏の留守中にみ...
-
スマホの画面をTVに映したいの...
-
EOKIOのヘッドフォンのペアリン...
-
G-SHOCKの異常について
-
Redmi Pad SE はmineoのau回線...
-
キャプボが最近この赤色になる...
-
ウェブ面談って何年ぐらい前か...
-
ピンマイクで収録された音声の...
-
Apple WatchのボイスメモをiPho...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
急に非通知着信がめちゃくちゃ...
-
ps3のシステムアップデートをし...
-
「PC Helpsoft Driver Updated...
-
EOKIOのヘッドフォンのペアリン...
-
このチップを教えて下さい
-
Simejiって危険なんですか? 友...
-
Windows11 24H2について
-
SONY Vaio ノートPCのシリーズ...
-
中古のpcでも、買ってすぐの新...
-
シュレッダーにアースジェット...
-
スマホはそのうち、GoogleとiPh...
-
携帯のWi-Fiを一定時間だけ切る...
-
彼氏のiPadを彼氏の留守中にみ...
-
タブレットを大型のスマホスタ...
-
スマートウオッチ
-
すぐ調べる癖
-
i久しぶりにプレステ4で遊ぼう...
-
こういったCD-ROMがなくてもア...
-
エンジン式と同レベルの刈払機
-
ノートパソコンの修理について
おすすめ情報