行の並び順の影響を受けない方法 2

質問

図は入出金管理のための【テーブル】です。
・集金月は文字データであり2020年01月～2020月12月などとする
・当月分データをG,H,Iに打ち込む
・残高Jが計算される
・翌月になったら当月分の約300行をコピーし、当月の下に貼り付け、集金月を翌月度に変更する
・翌月の最前行の前月残高の欄に=J3などと数式を入れると最後尾まで数式が入る
・月に１－２件は行の増減がある
※作業のため当月または翌月の行を並べ替えると翌月度の前月残高が狂う（当然）

※の問題を起こさないためには、画像の2020年10月の各物件の各番号と11月の各物件の番号が「固くリンク(一意)」することです。

そのためにF列に入る数式を教えて下さい。

今まで紙による管理でしたが、PCでの管理に移行するべく試行錯誤しております。

よろしくお願いします。

回答No.8

SI299792 · ベストアンサー

私のデータを並べ替えて送られてもあなたの表がどうなっているが解りません。私のを送り返すなんて、失礼にも程があります。あなた自身の表を送ってほしかった。自分の情報を出さず回答だけ求める人間の何と多いことか。
値が変わったのは、データ作成が面倒で残高を関数にしてしまったので、残高そのものが変化してしまった。10月残高=110、11月前月繰越=110、Ｆ列の数式は合っています。

しかし原因は思い当たりました。多分
J3_ =F3+G3-I3
の数式が入っているのでしょう（想定していなかった）。その結果
""+6000+I3となり、エラーになります。

F3_ =SUMIFS(J:J,B:B,B3,C:C,C3,E:E,TEXT(E3-1,"YYYY年MM月"))
にして下さい。
しかし、これでは、データのない場合も０が出ます。
データ無しの場合空白にしたい場合、前の数式のままで、
J3_ =SUM(F3)+G3-I3
にすれば前の数式を利用できます。
同じ所に新しい表を上げておきました。

あなたのような人には回答したくはないのですが、プライドがあるので今回は回答します。次の質問からは回答しません。

お礼

まず、回答者さんを怒らせてしまったことをお詫びします。
私は１番のお礼欄に
＞・１１月の残高の欄→#value!
＞　エラーの検証→""+6000(←下線付)-[@入金額]
と書いたので、J列の式が「F+G-I」であると理解してくれるだろうと勝手に思い込んでおりました（「表」ではなく「テーブル」です）。

また
・私のテーブルでは年月の確認をしたにも関わらず相変わらずF列が無表示なので【アップしても意味がない】と思い込み、
・さらに回答者さんがJ列に私とは異なる式を入れていたのに気づかず
・行を入れ替えると値が変わってしまうことを報告したほうが良いと考え、
回答者さんの表を加工してアップしたのです。

それ以外に他意はありません。

現在は教えていただいた式を入れ、テーブルが正常に動くかどうかを検証している途中です。イケそうな感じです！

もうすぐ月次の締切が来て最終的な検証作業をする予定です。質問の締切はその後行います。補足欄で結果の報告をいたします。

最後になりますが、私の非礼についてお詫びと度々のご回答にお礼とを申し上げます。

補足

月次処理を行い、テーブルが運用に耐えることを確認できました。

どうもありがとうございました。

回答No.7

HohoPapa

データ便にポストされた入出金管理.xlsxを確認しました。
私が確認する限り、
関数の計算結果は、エラーにはならず、期待通りに機能しています。

並べ替えを行うことで課題関数で計算した結果が変化してしまうのは、
F列に埋めている
=IFERROR(AVERAGEIFS(J:J,B:B,B3,C:C,C3,E:E,TEXT(DATEVALUE(E3&"1日")-1,"YYYY年MM月")),"")
この関数の問題ではなく、

J列に埋めている
=ROW()*COLUMN()
この関数の影響と思います。

このJ列の関数は、
関数の埋まったセルの行番号と列番号を掛け算していますので
行位置が変化すれば、呼応して変化してしまいます。
J列の値が変化してしまえば、必然的にF列の計算結果も変化してしまいます。

いかがでしょうか？

お礼

８の回答を見れば分かる通り、その回答者さんが私の表とは異なる数式を使っていたのが原因でした。私の技量ではそれに気づくことができず、結果的に回答者さんを怒らせることになってしまい、大変申し訳無い気持ちになっています。

ありがとうございました。

回答No.6

HohoPapa

E列の中に当方の予想に反した年月がある可能性を強く疑います。

E列に埋まった年月は、yyyy年mm月の形式
言い換えれば、数式バーに表示される内容は、
全数、半角の数字4桁、年、半角の数字2桁、月の形式ですか？

また1か月当たり300行程度あるようですが
これを5行程度にしたテストデータを新たなシートに用意し
試してみてください。

お礼

E列の問題ではなくJ列の関数がF+G-Iであって、その結果がエラーとなっていたのが、F列に何も表示されない原因でした。J列の関数を別の回答者さんに教えていただいたものに変更してうまく動くようになりました。

ありがとうございました。

回答No.5

SI299792

私の所ではうまくいっています。
https://drive.google.com/file/d/1p6WO2cj8X9DUno55r2l7Z3IyiCE-S3Gp/view?usp=sharing

可能性としては、2020年10月の後ろにスペース等見えない文字が入っている他です。

解らない場合、
データ便
https://www.datadeliver.net/1
グーグルドライブ
https://www.google.com/intl/ja_ALL/drive/
に上げていただければ見ます。

お礼

確かに動きますね。ただし、私の求める動作では無いと思います。行を動かすと前月繰越の値が変わってしまいます。
↓
https://www.datadeliver.net/receiver/file_box.do?fb=00b96b1b243c457d8c1c89ab7a6c1e3e&rc=7349ed8a9ff84bc2a49afc824afcd2c9&lang=ja

私の意図が伝わっていないのか、式が違うのかわかりません。引き続きサポートをお願いしたいところです。

いつもありがとうございます。

回答No.4

HohoPapa

E列が日付シリアル値なのか文字列なのかがポイントと思います。
Ｅ列に数百行の年月が埋まっているものと思いますが
その一部は文字列、一部は日付シリアル値の可能性を疑います。

添付画像の数式バーのように
2020年10月　と表示されれば文字列
2020/10/1　と表示されれば日付シリアル値です。
混在していないかを確認してみてください。

お礼

全てのセルで「2020年10月と2020年11月」が「文字列」であることを確認しましたが、同じエラーです。

↓F3の式
=IFERROR(AVERAGEIFS(J:J,B:B,B3,C:C,C3,E:E,TEXT(DATEVALUE(E3&"1日")-1,"YYYY年MM月")),"")

どうすれば良いでしょうか？

ありがとうございます。

回答No.3

HohoPapa

>集金月は単なる「文字列」です。
>日付形式にするとフィルタリングの時に日にちまで認識して非常に面倒だからです。
この発言から、
E列は日付シリアル値ではなく文字列と思います。

であれば、先に示されたコードをちょっと直して
=IFERROR(AVERAGEIFS(J:J,B:B,B6,C:C,C6,E:E,TEXT(DATEVALUE(E6&"1日")-1,"YYYY年MM月")),"")
とすれば
期待の動作になると思います。

お礼

セル座標をE3に直して全行にコピーしましたが、同じエラーになっているようです。

ありがとうございます。

引き続き教えていただければ幸いです。

回答No.2

SI299792

この式をF335に入れていませんか。
無駄に見えても、F3に入れて下へコピペして下さい。

お礼

確かにF335に入れていました。しかしF3から全行にコピーしても結果は同様でした。10月分までエラーです。

2020年01月に強いコダワリはありません。式を理解したいので1月～12月にします。この前提で式を教えて下さい。

どうぞよろしくお願い致します。

回答No.1

SI299792

前回の質問で、式が複雑だといわれたのですが、AVERAGEIFSを使えば簡単になります。
また、この式なら遅くなるという問題もありません。
F3_ =IFERROR(AVERAGEIFS(J:J,B:B,B3,C:C,C3,E:E,TEXT(E3-1,"YYYY年MM月")),"")
下へコピペ。

＞2020年01月～2020月12月
とはっきりかいてあったのですが、データを９月～10月にしてくれた方が一目瞭然で解りやすいです。

お礼

お世話になります。
エラーになりました。画像を貼れないので言葉で説明します。
・１１月の前月繰越の欄→式が入っているのに無表示
・１１月の残高の欄→#value!
　エラーの検証→""+6000(←下線付)-[@入金額]

何度もすみませんが、よろしくお願い致します。