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

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が見つからない時は、教えて!gooで質問しましょう!

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

Qエクセルで種類を数える関数が無いのは何故?

エクセルで種類を数える関数が無いのは何故なんでしょうか?

エクセルで種類を数えるには、いくつかの関数を組み合わせるのが一般的ですよね?
直接数える関数が無いのは、訳があるんでしょうか?

Aベストアンサー

>>エクセルで種類を数える関数が無いのは何故なんでしょうか?

やっぱり、そういう関数が必要な方が全体からみたら少数派だと、エクセルの開発者たちが考えているからではないかと思います。
また、既存の関数を組み合わせたら、対処可能だから、無理して新しい関数を作る必要性もない、開発の優先順位が低いって判断もあるでしょうね。

私は、エクセルの表を作ったり、エクセルVBAでプログラムを作ったりしますけど、そういう関数が必要になったことが全くありませんし。

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)関数を単純コピーしていないか
  (参照セルがずれるときがあります)

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

Qエクセルの関数で

エクセルの関数辞典を見ていたら、CUMPRINC関数というのがありました。
しかし、エクセルの「挿入」→「関数」→関数の分類で「財務」というのを選択したのですが、一覧表に載っていません。
どこに載っているのでしょうか?
どうすればこの関数を使えますか?
ちなみにシートの上でやっても関数の反応をしませんでした。

Aベストアンサー

Yahooで検索してみると、参考URLが引っかかりました。

参考になりませんか?

参考URL:http://money-sense.net/doc/20041215_224257.php

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

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

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

Aベストアンサー

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

Qエクセルの関数 ネスト

エクセルの関数 ネスト

エクセルの関数で、ネストさせるときがあるとおもうのですが、

関数を内側に書いたらよいのか外側に書いたらよいのか分からなくなる時があります。

エクセルの関数に関してわかりやすく書いてあるページなどありますか。

Aベストアンサー

こんばんは

Excel2003までは、ネストが7まで、2007では64までが可能です。
http://www.google.co.jp/search?hl=ja&source=hp&q=excel+%E3%83%8D%E3%82%B9%E3%83%88%E3%80%802003%E3%80%802007&aq=f&aqi=&aql=&oq=&gs_rfai=

「仕様上は可能」でも、複雑なネストは間違いが生じやすいですし、変更もしにくくなります。「出来るだけネストはしない」「適宜、中間結果をセルに出力する」という方法を採った方が、間違いが少なく、柔軟性のあるシステムになると思います。

>エクセルの関数に関してわかりやすく書いてあるページなどありますか。
関数の個別の機能ならば、Webサイトも書籍も多数あるのですが、「組み合わせて使う」というのはその場その場での発想になってしまうと思います。

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エクセル関数の解読サイトなんてありますか?

エクセル関数の解読サイトなんてありますか?

いつもお世話になっております<(_ _)>

エクセルファイルに関数の入った数式が入力されています。
セルごとに複数の関数が入っていますが、私にはちっともわかりません。

そこで質問です。
こんなとき「エクセル関数を解読」してくれるようなサイトってありませんか?

たとえば検索窓があってそこに「=SUM(S1:S13)」わからなくて困っている関数式を入力。
すると答えの別ボックスに「S1~S13までの数値の合計」と出てくるようなサイト。

それに近いサイトでも良いので知っている方がいらっしゃればぜひ、教えてください<(_ _)>

Aベストアンサー

もし、

=IF(E14="","",IF(O14="",(IF(E14>"18:00"*1,"18:00",E14)-IF(C14<="8:00"*1,"8:00",C14))*24*1300,(IF(E14>"18:00"*1,"18:00",E14)-IF(C14<="8:00"*1,"8:00",C14))*24*1625))

だったら、どういう文章が出て欲しいのでしょうか?

もしE14が空白だったら、
 空白、
そうじゃなかったから、
 もしO14が空白だったら、
  (もしE14が18:00より大きかったら18:00、そうじゃなかったらE14)-(もしC14が8:00以下だったら8:00、そうじゃなかったらC14)×24×1300
 そうじゃなかったら、
  (もしE14が18:00より大きかったら18:00、そうじゃなかったらE14)-(もしC14が8:00以下だったら8:00、そうじゃなかったらC14)×24×1625

って感じですか?
数式をそのまま読解したほうが解りやすくないですか?

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

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

Aベストアンサー

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

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

Qエクセル関数を、書き写して分析できるツールはある?

タイトルの件、質問します。

エクセルの関数を分析する際に、エクセルの数式バーや、セルに入っている関数を
F2を教えて見るのでは、見にくい場合があります。

現在は、私は、メモ帳に関数をコピーして、分析したり、修正したりしています。
エクセルの機能or他ソフトで、関数を分析できるツールはあるのでしょうか??

【エクセルバージョン】
2003、2007

Aベストアンサー

難解な数式を理解したいとき,最も便利に利用できるのは,2003ではツールメニューのワークシート分析にある「数式の検証」です。
2007では数式タブにあります。

メンドクサイ数式のセルで数式の検証を使い,どの関数やどのカッコから計算が進んでいくのかを1ステップずつトレースして理解します。また意図しない結果がどの段階で発生しているのか追跡します。

このやり方は勿論間違った数式(意図しない結果が出てきた場合)を追跡するのにも使いますが,むしろ誰かに教わった「正しい数式」を理解する時に便利な方法です。
そもそも計算が通っていない(たとえばカッコの対応が間違えていて,Enterしても受け付けてくれないようなミスをしている場合)には使えません。



また,数式バーの中で数式の「中」にカーソルを入れて左右の矢印キーでカーソルを動かしていったときに,「(」や「)」をまたいだ瞬間に,対応する「閉じカッコ」「始まりのカッコ」が色つきで強調表示されるのを確認しながら,カッコの対応がまちがえてないかなどを調べるのも簡易な良い方法です。


あまり使わない方法ですが,数式の中で適宜ALT+Enterを打って「セル内改行」してしまい,数式を縦に分解して書いてみるのも整理しやすい方法のひとつです。

難解な数式を理解したいとき,最も便利に利用できるのは,2003ではツールメニューのワークシート分析にある「数式の検証」です。
2007では数式タブにあります。

メンドクサイ数式のセルで数式の検証を使い,どの関数やどのカッコから計算が進んでいくのかを1ステップずつトレースして理解します。また意図しない結果がどの段階で発生しているのか追跡します。

このやり方は勿論間違った数式(意図しない結果が出てきた場合)を追跡するのにも使いますが,むしろ誰かに教わった「正しい数式」を理解する時に便利...続きを読む

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

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

Aベストアンサー

こんにちは!

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

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

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

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

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


人気Q&Aランキング