エクセル A一覧入力のデータをC表形式に振り分け

質問

家賃の入出金を管理したいと考えています。

(1)A表の入金管理表に入力すると、C表に集金月ごとに振分られるように表示されるような数式を教えて頂きたいです。
　　・黄色、オレンジ色参照：同月に2回以上の集金する場合はC表では合計を表示。
　　・青色参照：同月に入居と退去がある場合、新入居者の部屋番号を「●-2」として管理。

(2)B表の保証金管理表に入力するとC表の保証金の欄に現在の合計が表示されるような数式を教えて頂きたいです。

よろしくお願いします。

回答No.2

kagakusuki · ベストアンサー

確認したいのですが、

>・青色参照：同月に入居と退去がある場合、新入居者の部屋番号を「●-2」として管理。

という事は、例えば「ええ」さんの部屋No.は翌月には「103-2」ではなく、「103」に変わると考えれば宜しいのでしょうか？
　それから、管理する部屋番号には、101号室と1010号室という様に、前半の３桁が同じ文字列（この例では、どちらも「101」から始まっている）となる部屋が複数存在しているのでしょうか？
　もし、そうだとしますと、作業列（補助的な計算処理を行うために使用するセルを設けるための列）を設ける必要があります。

　今仮に、A、B、Cの各表がSheet1に入力されていて、Sheet2のA列とB列を作業列として使用するものとします。
　まず、Sheet2のA4セルに次の数式を入力して下さい。

=IF(OR(INDEX(Sheet1!$H:$H,ROW())="",INDEX(Sheet1!$I:$I,ROW())=""),"",LEFT(INDEX(Sheet1!$H:$H,ROW()),FIND("-",INDEX(Sheet1!$H:$H,ROW())&"-")-1)&":"&INDEX(Sheet1!$I:$I,ROW()))

　次に、Sheet2のB4セルに次の数式を入力して下さい。

=IF(OR(INDEX(Sheet1!$A:$A,ROW())="",INDEX(Sheet1!$B:$B,ROW())="",INDEX(Sheet1!$E:$E,ROW())=""),"",LEFT(INDEX(Sheet1!$A:$A,ROW()),FIND("-",INDEX(Sheet1!$A:$A,ROW())&"-")-1)&":"&INDEX(Sheet1!$B:$B,ROW())&":"&INDEX(Sheet1!$E:$E,ROW()))

　次に、Sheet2のA4～B4の範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。

　次に、Sheet1のP4セルに次の数式を入力して下さい。

=IF($O4="","",SUMIF(Sheet2!$A:$A,LEFT(INDEX($N:$N,ROW()),FIND("-",INDEX($N:$N,ROW())&"-")-1)&":"&INDEX($O:$O,ROW()),$K:$K))

　次に、Sheet1のQ4セルに次の数式を入力して下さい。

=IF(COUNTIF(Sheet2!$B:$B,"="&LEFT(INDEX($N:$N,ROW()),FIND("-",INDEX($N:$N,ROW())&"-")-1)&":"&INDEX($O:$O,ROW())&":"&Q$3),INDEX($C:$C,MATCH(LEFT(INDEX($N:$N,ROW()),FIND("-",INDEX($N:$N,ROW())&"-")-1)&":"&INDEX($O:$O,ROW())&":"&Q$3,Sheet2!$B:$B,0)),"")

　次に、Sheet1のR4セルに次の数式を入力して下さい。

=IF(COUNTIF(Sheet2!$B:$B,"="&LEFT(INDEX($N:$N,ROW()),FIND("-",INDEX($N:$N,ROW())&"-")-1)&":"&INDEX($O:$O,ROW())&":"&Q$3),SUMIF(Sheet2!$B:$B,LEFT(INDEX($N:$N,ROW()),FIND("-",INDEX($N:$N,ROW())&"-")-1)&":"&INDEX($O:$O,ROW())&":"&Q$3,$D:$D),"")

　次に、Sheet1のQ4～R4の範囲をコピーして、Sheet1のR4セルよりも右側にあるセルに貼り付けて下さい。
　次に、Sheet1のP4セルから「先程Sheet1のQ4～R4の範囲をコピーしたものを貼りつけた中で、最も右側にあるセル」にかけての範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。

　以上です。

※尚、この方法でも、同じ部屋に、過去に退去した人と同姓同名の人が入居した場合には、区別が付かず、正しく計算する事が出来なくなりますので、同姓同名で且つ同じ部屋の場合には、名前の後ろに「-2」を付ける等の工夫が必要となります。

お礼

大変細かな説明文と共に、式を教えて頂きありがとうございます。
教えて頂いた式を使わせてもらい、表を作っているのですが、
1.5Mbまで膨れ上がってしまいました。
しかし、分割するなどして、是非完成させたいと思っています。
短い文章で申し訳ありませんが、大変助かりました、ありがとうございました。

回答No.3

imogasi

自分が勝手に作った表のコピーだけ上げて、読者回答者に多大の時間を割かせて、読み解かせて、回答を求めるなんてひどいじゃないですか。
＞確認しますが、＞自信ありませんが、なんて回答者から出ているじゃないですか。
何処の項目（個人別とか）を、どうして（例えば加算して）、どういう結果を出したいのか。
そのとき注意する点（マイナス数字などあるようだが）など。
これらを例データを使いながら、文章で、ステップを追って、説明しておくのが筋ではないか。
ここは読者回答者が試験を受けているのではないし、質問者の下請けでもない。
ーー
Ｂ表の保証金列を個人別にＳＵＭＩＦで集計してＰ列に持っていけば、誤りになるのか？
質問者の場合は、保証金の分割預かりなどがあるのか。そういう注意書きも必要と思う。
あるいは全体が模擬実例で、家賃等の管理途は別か？
以上出来るだけ皇族の回答者のため細くしておいてください。
こういう表の組み換えを自由にやるとなると、関数だけでなく、ＶＢＡなどが必要かなという課題になる。その回答の式の長いことがそういうことを意味してるのかも。

お礼

imogasiさん、お返事ありがとうございました。
参考にさせて頂きます。

回答No.1

hidegii00

質問者様の意図にあっているか自信がありませんが、要は部屋別の保証金の合計を計算できればいいのですよね？

関数SUMIFを使うとできると思います。
P4のセルが部屋番号101の人の保証金合計として、
=SUMIF($H$4:$H$11,N4,$K$4,$K$11)
とすれば、お望みの計算になると思います。

$H$4:$H$11としているのは、部屋番号を照合するための範囲です。11行目にしているのは、一応欄外も含めて、データが増える前提としています。データ件数がもっと多ければ、もっと大きい数字にしてください。
また、データを追加するときには、「列の挿入」をしてから挿入した行に入力してください。
列の挿入をすれば、数式の方も自動的に参照範囲を広げてくれます。
上の式だと10行目の下に行を挿入すれば、参照範囲は12行目までになります。

N4は部屋番号を参照しています。これはC表であらかじめ手入力しておいてください。

$K$4:$K$11は、合計する対象の範囲です。
ここも、部屋番号と同様に設定してください。
なお、部屋番号の範囲と合計の範囲の行数は必ず同じにしてください。植えの場合だと4から11の8行で同じにしています。

お試しください。

お礼

お返事ありがとうございました。
画像が小さくて見づらくて申し訳ありませんでした。
大変わかりやすい説明で、お返事もはやくとても助かりました。
ありがとうございました。