行の並び順に影響させない方法

質問

図は入出金管理のための【テーブル】です。
・当月分入金データをG,H,Iに打ち込む
・残高Jが計算される
・翌月になったら当月分の300行をコピーし、当月の下に貼り付ける
・翌月の最前行の前月残高の欄に=J34などと数式を入れると最後尾まで数式が入る
※このあと作業のため翌月分の行を並べ替えると前月残高が狂う（当然）

※になることを防ぐ方法は無いでしょうか？

よろしくお願いします。

回答No.5

SI299792

前回上げた式は、日付が文字列の場合使えません。
文字列であれば 9月なのか09月なのかが問題になります。
日付が文字列なら 9月なのか09月なのか含めて、最初に明記して欲しいです。
これは私の方から聞いておくべきでした。今後このような質問をする時は、そうして下さい。回答者の二度手間を防げます。
また、データが最大何行までありうるかも書いていただければ、それに合わせた数式を作ります。

F3_ =IFERROR(INDEX(J$3:J$999,MATCH(TEXT(C3-1,"YYYY年M月")&D3,INDEX(C$3:C$999&D$3:D$999,),0)),"")
・ 9月前提です。09月の場合は M→MMに変更して下さい。
・ 999行目までを前提にしています。もっとデータが多い場合変更して下さい。

数式の説明ですが、以下の様に入力すれば動きが解ると思います。
K3_ =TEXT(C3-1,"YYYY年M月")&D3
L3_ =MATCH(K3,INDEX(C$3:C$999&D$3:D$999,),0)
M3_ =INDEX(J$3:J$999,L3)
N3_ =IFERROR(M3,"")
下へコピペ。

C3-1で、前月の末日になるので、TEXTを使い年月だけにして、後ろにNoを付けます。
INDEX(C$3:C$999&D$3:D$999,) これは、INDEX を配列関数として使っています。これで
C3&D3
C4&D4
︙
の配列ができます。
INDEX とMATCH でデータを取り出し、IFERROR でエラーが出ないようにしています。

お礼

このテーブルはまだ実用化していません。今までの紙による管理方式からpcへ移行するために試行錯誤している段階です。その中で新たな課題がわかりました。

それは最初月のデータ行も順番が変わる可能性が高いことです。

今までの皆さんの回答は当然ながらその点を考慮に入れないものと思います。大変申し訳ありません。再度質問を投稿します。

またよろしくお願い致します。

回答No.4

HohoPapa

次のような式をF387に埋め、必要数下方向に複写する方法はいかがでしょうか。

=IFERROR(INDEX($J$3:$J$386,MATCH(B387,$B$3:$B$386,0),1),"")

$J$3:$J$386　...　10月（つまり前月）の残高が埋まったデータ範囲
$B$3:$B$386　...　10月（つまり前月）のNo.が埋まったデータ範囲
B387　　　　...　参照したいNo.の埋まってセル
IFERROR　　...　参照できなかった場合は空欄にする。

お礼

このテーブルはまだ実用化していません。今までの紙による管理方式からpcへ移行するために試行錯誤している段階です。その中で新たな課題がわかりました。

それは最初月のデータ行も順番が変わる可能性が高いことです。

今までの皆さんの回答は当然ながらその点を考慮に入れないものと思います。大変申し訳ありません。再度質問を投稿します。

またよろしくお願い致します。

回答No.3

kkkkkm

> その式を入れ、ctrl+enter したところすべての行が同じ式になってしまいました。かんたんで良い方法に思えたのですが。

選択範囲の元の相対参照の式を一気にすべて絶対参照に変更するためにマクロを使ってみてください。

Excelマクロ・VBA 絶対参照を一括で設定・解除する方法
https://blog-tips.sekenkodqx.jp/2019/09/04/excel-vba-lump-convert-absolute00001/

実行時にはAlt+F8キーで説明にあるマクロ一覧ダイアログが出ますのでそちらから実行してください。

お礼

このテーブルはまだ実用化していません。今までの紙による管理方式からpcへ移行するために試行錯誤している段階です。その中で新たな課題がわかりました。

それは最初月のデータ行も順番が変わる可能性が高いことです。

今までの皆さんの回答は当然ながらその点を考慮に入れないものと思います。大変申し訳ありません。再度質問を投稿します。

またよろしくお願い致します。

回答No.2

SI299792

集金月はどのようになっていますか。
2020/10/1 の様に日付で入っていて、１日にしてある。セルの書式設定で「YYYY年MM月」にしてある前提です。
F3_ =IFERROR(INDEX(J$3:J$999,MATCH(EOMONTH(C3,-2)+1&D3,INDEX(C$3:C$999&D$3:D$999,),0)),"")
下へコピペ。
データは 999行目までを想定しています。もっと多い場合 999を増やして下さい。
但し、配列関数なので行数が多いと動作が遅くなります。

もし毎月の行数とNoの並びが同じなら、
F1_ =COUNTIF(C:C,C3)
（どこかに件数が必要です、入力後どこに移動してもいいです）
F3_ =IF(ROW()-F$1>2,OFFSET(J3,-F$1,0),"")
という数式も考えれます。これなら動作は遅くなりません。

お礼

集金月は単なる「文字列」です。日付形式にするとフィルタリングの時に日にちまで認識して非常に面倒だからです。

大変複雑な数式ですね。理解できません。意味を教えていただけますか？

件数は月に１行程度増減する程度です。変化のない月もあります。

ありがとうございます。

回答No.1

kkkkkm

=$J$34
のように絶対参照にしておけばいかがですか。

お礼

その式を入れ、ctrl+enter したところすべての行が同じ式になってしまいました。かんたんで良い方法に思えたのですが。

どうしたら良いでしょうか？