私は StackExchange のこのタイプには不慣れなので、この投稿が他の場所に属する場合は移動してください。ただし、ここが最適な場所だと思いました。
私は、基本的な財務データを 5 つの列 ( 、、、、、およびCheck Number)と不定の行に格納するだけのDate of TransactionExcelワークシートを作成しています。各ワークシートは 1 か月を表し、各ワークブックは 1 年を表します。支払いを行ったり受け取ったりすると、新しい行として保存するため、月あたりの行数は本質的に不定になります。各取引のセルは、その上の行のセルとその行のセルの合計です。各月は、特別な行 (列ヘッダーの後の最初の行) で始まり、その行に取引の概要が表示されるようにしたいと考えています。DescriptionProfit from TransactionBalance After TransactionBalanceBalanceProfit最後月の取引。たとえば、Balance After Transactionセルには最終行の残高が表示され、Profit from Transactionセルにはその月の総利益が表示されます。
毎月の支出がちょうど 100 件あることがわかっていれば、3 月については、利益と残高についてそれぞれ次の式を使用してこれを達成できることがわかります。
=February!E2 - February!E102
=February!E102
しかし、私はない各月のテーブルに何行あるかを知っており、これをできるだけ自動化したいと考えています (たとえば、1 月に漏れた費用や重複した費用が見つかった場合、1 月の末残高を指すすべての数式を更新する必要はありません)。特定の Excel スプレッドシート内の列に最後に入力された値を数式で自動的に使用するにはどうすればよいですか?
答え1
私の最初の提案は、この範囲をテーブルに変換することです ([挿入] -> [テーブル])。次に、合計行を追加します。この行は、名前で別のシート/ワークブックから参照でき、行数を気にする必要がなくなります。
それが選択肢にない場合は、次の操作を実行できます。
前月の利益の概要を取得するには、
=SUM(Month!$D:$D)
(利益の列には利益以外の数字がないことを前提としています)。
前月の残高については、@Jesse の式が機能するはずですが、テーブルが最初の行から始まっている場合のみです。そうでない場合は、次を試してください。
=INDEX(Month!$E:$E,MATCH(TRUE,ISBLANK(Month!$E:$E),0)-1)
入力するときは、必ず を使用してくださいCTRL+SHIFT+ENTER。これにより、ISBLANK列全体を使用して最初の空のセルが検索され、そのすぐ上 (最後の行) の値を取得できます。
これをより動的かつ将来性のあるものにするために、名前付き範囲を使用することをお勧めします。そうすれば、必要に応じて、列への参照をすべて変更する必要がなくなります。
答え2
間接は使用しないでください。このレベルの計算では面倒なことになります。間接は不安定で、何かを変更するたびにシート内のすべての数式が再計算されることになります。
代わりにインデックスを使うこともできます。次のようになります:
=INDEX(Month!A:E,COUNTA(Month!A:A),COLUMN())
列全体を参照するだけで、列オフセットは関数によって自動的に計算されますCOLUMN()。
答え3
もう 1 つの簡単な方法は、ワークブックに名前を挿入することです。Jan、Feb など。これらを使用して、日付列の日付エントリの数を数えて最後の行を決定します。
たとえば、「Jan」という名前を挿入し、RefersTo =COUNT(January!A:A)+2 とします。これにより行数が得られ、それを間接的な数式で使用して範囲参照を構築できます。
行の最後のセルは =INDIRECT("E"&Jan) となり、必要に応じて他の場所の概要シートでもこれらの参照を使用できます。
これは、列 A に数値 (日付) があることを前提としています。ヘッダーがテキストの場合は無視されますが、行 2 には数値が含まれていないことを確認してください。数値が含まれていると、行数が歪んでしまいます。