エクセルで収入と支出と利益を計算する単純なシートで、数年に渡る月次のデータ(sheet2)から、sheet1で指定した特定の期間の3ヶ月の収入や支出を検索として合計をする関数を作成しました(sheet2の右の方の罫線の箇所)。
SUM関数とINDIRECT関数、ADDRESS関数、MATCH関数を組み合わせているのですが、この状態で例えば収入(2)の後ろに収入(3)を挿入しようとすると、ADDRESS関数の行番号(ADDRESS(7,MATCH~)の数字の7のところ)
が自動的に8に変わらないので、都度関数を修正しなければなりません。行を挿入したり削除したりしたら、ADDRESS関数の行番号も増えたり減ったりするようにできる方法はないでしょうか。
もしくは、そもそももっと簡単で使いやすい関数はないでしょうか。
教えていただけるととても助かります。

このQ&Aに関連する最新のQ&A

A 回答 (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の式を複写したものです)

この回答への補足

すべて上手くいきました。本当にありがとうございました!!

補足日時:2009/10/13 23:11
    • good
    • 0
この回答へのお礼

本当に丁寧にありがとうございます!
さっそく試してみますが、上手くいきそうな気がします。
結果はまたご報告させて頂きます。

お礼日時:2009/10/06 00:12

縦軸に科目があって、横軸に月毎のデータがあって


ヘッダ行は1行目が月初日、2行目が月末日
実際のデータは3行目以降に入っている
集計列のヘッダは1行目が期首日付、2行目が期末日付
同じ行のデータのみを集計する
ということですか。

であればSUMPRODUCTを試してみてください。
画像をつけます。
「エクセル関数で、行を挿入した時にADDR」の回答画像3
    • good
    • 0

すみません。

再度補足要求です。

>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関数の行数が変わらないので、集計表で集計する数字が変わってしまうのは質問に記載している通りです。
よくありそうな集計シートですので、もっと単純な方法があるのではないかと思っています。よろしくお願いいたします。

補足日時:2009/10/01 23:02
    • good
    • 0

・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関数だけでは、集計したい期が変わる度に、都度合計するセル範囲を変更しなければならないので。
よろしくお願いいたします。

補足日時:2009/09/30 23:45
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qsheet2からsheet1へのデータの飛ばし方(エクセル関数)

Sheet1
A  B C D E (列)
――――――――――――――――――――――――
1| 番号  氏名   所属  入社  生年月日
2| 1000  斉藤   東京  10/4  (入力箇所)

sheet2
A B C D (列)
―――――――――――――――――――
1| 番号  氏名  所属  生年月日
2| 1000  斉藤  東京  1988/11/1


sheet1のE行(生年月日)にsheet2を参照し、同じ番号の人の生年月日が表示されるようにしたいのですが、どのような式(関数)を入れたら表示されるでしょうか?
ほかの似たような質問の解答を見てMatchやvlookupを使ってみましたが、できなかったので教えてください。
よろしくお願いします。

Aベストアンサー

#1です。
お手数ですが以下をご確認ください。

1)対象となるデータが存在するか(検索機能などで)
2)対象となるキーが一致しているか
3)参照元となるsheet2は昇順でソートされているか
4)関数を単純コピーしていないか
  (参照セルがずれるときがあります)

それでもダメならお役に立てなかったことになってしまいますね。
でしゃばってごめんなさい。

QMusic Match Jukeboxのライセンスコード

新PCに引越ししてから使っていなかったのですが、
Music Match Jukeboxを使いたいと思いました。
しかしシェアウェアでライセンスコードが必要です。
しかしライセンスコードの載っていたメールが見つかりません。
製造元は既になく、問い合わせ・再発行もできません。
どなたかライセンスコードを教えていただけないでしょうか。

音楽データベースにアクセスしてアルバム名や曲名を探してくれる機能やCDをMP3に変換してプレイリストで再生する機能がうれしかったのですが、これらの機能があれば他のソフトでも構いません。
紹介して下さい。

Aベストアンサー

希望を見ると このソフトが思い浮かぶね
http://www.apple.com/jp/itunes/download/

Qmusic matchからi-tuneの移行はどうすれば?

初めてwindows使用可となった頃のi-podを使用しています。
この頃はソフトがi-tunesではなくmusic matchでそのまま使用していたのですが、先日yahoo(US)からメールが届き、7月いっぱいで完全にmusic matchのサービスをやめると連絡がありました。
i-tunesを利用して現在の古い(5年位前)i-podを引き続き利用する方法分かりましたら教えてください。
またその時は今i-podに入っている、若しくはmusic matchを通してPCに保管している楽曲をそのままi-tunesで管理する移行方法もお願いします。

Aベストアンサー

iTunes for Windows: MUSICMATCH の曲を自分の iTunes ライブラリに移す方法
http://docs.info.apple.com/jarticle.html?artnum=93390
単純にドラッグ&ドロップで移せると思います。
保護が(DRM)掛かっていれば市販ソフトで(tunebite)録音し直しです。http://security.okwave.jp/qa2752677.html

ipodに有る音楽ファイルはフリーソフト
注意:iTunesがインストールして有ると同期するかもしれませんので一度「iTunes」をプログラムの追加と消去から消去してから下記ソフト使って下さい。

「ipod野郎」
http://buin2gou.nyaa.co.uk/ipod/manual/
「YamiPod」
http://www.altech-ads.com/product/10002106.htm

自力で挑戦
http://security.okwave.jp/qa2547931.html

確認として、iTunes for Windowsでは2000とXPでしか使えないですので注意して下さい。

iTunesバージョン7.2.0.35で不都合でたら他のバージョン試してみてみてください
http://www1.atwiki.jp/itunes/pages/7.html

iTunes for Windows: MUSICMATCH の曲を自分の iTunes ライブラリに移す方法
http://docs.info.apple.com/jarticle.html?artnum=93390
単純にドラッグ&ドロップで移せると思います。
保護が(DRM)掛かっていれば市販ソフトで(tunebite)録音し直しです。http://security.okwave.jp/qa2752677.html

ipodに有る音楽ファイルはフリーソフト
注意:iTunesがインストールして有ると同期するかもしれませんので一度「iTunes」をプログラムの追加と消去から消去してから下記ソフト使って下さい。

「ipod野郎...続きを読む

Q事務所のインターホンの設置をしようと思うのですが、その都度席から立って

事務所のインターホンの設置をしようと思うのですが、その都度席から立って「はい」と受けるのが大変なので、
事務所の固定電話に接続できるものがあればなー、と思っています。
そういったものは既に市場にあるのでしょうか?
もしご存知の方がいらっしゃいましたら教えてください。
よろしくお願いします。

Aベストアンサー

固定電話機にインターフォン機能というがあります。ただし電話機自体とそのインターフォンオプションをすべて新規に買うことになります。

設置が簡単な無線式のインターフォンもあります。
http://www.e-bouhan.com/list/4_inter_phone.html

Qexcelの置換え関数またはif関数の使い方教えてください

エクセルであるセルの数字の種類によって別のセルを色分けしたいのです。例えば、あるセルが「1」だったら別のセルは黄色、「2」だったら赤色という具合に自動的に色分けしたいのですが、可能でしょうか。また可能ならその方法を教えてください。

Aベストアンサー

こんにちは!

一つのセルに条件付き書式を複数設定すれば可能です。
具体的な配置が判らないので、
仮にA1セルに1、2 といったデータを入力するとします。
Excel2007以降のバージョンをお使いだとすると

色を付けたいセルを選択 → 条件付き書式 → 新しいルール → 数式を使用して・・・ → 数式欄に
=A1=1
と入力 → 書式 → 塗りつぶしから「黄」を選択しOK

同様におなじセルに条件付き書式を設定します。
・・・中略・・・ → 数式欄に
=A1=2
と入力 → 書式 → 塗りつぶしから「赤」を選択しOK

これでA1セルが「1」の場合は「黄」に「2」の場合は「赤」になるはずです。

※ Excel2007以降であれば条件はいくらでも設定できるはずです。m(_ _)m


このカテゴリの人気Q&Aランキング

おすすめ情報