저는 이 StackExchange를 처음 접했습니다. 따라서 이것이 다른 곳에 속해 있다면 옮겨주세요. 그래도 여기가 가장 좋은 곳일 거라고 생각했어요.
Check Number기본 재무 데이터를 5개 열( , Date of Transaction, Description, Profit from Transaction및 Balance After Transaction)과 무한행 에 간단히 저장하는 Excel 워크시트를 만들고 있습니다 . 각 워크시트는 한 달을 나타내고 각 워크북은 1년을 나타냅니다. 지불을 하거나 받을 때 이를 새 행으로 저장하므로 본질적으로 월별 행 수가 무제한이 됩니다. 각 거래의 셀은 그 위에 있는 행의 셀과 해당 행의 셀 Balance의 합계입니다 . 매월 요약을 표시하는 특별한 행(열 머리글 다음의 첫 번째 행)으로 시작하고 싶습니다.BalanceProfit마지막월별 거래. 예를 들어 Balance After Transaction셀에는 마지막 행의 잔액이 표시되고 셀 Profit from Transaction에는 해당 월의 전체 수익이 표시됩니다.
매달 비용이 정확히 100개라는 것을 안다면 각각 다음과 같은 이익 및 잔고 공식을 사용하여 3월에 이를 달성할 수 있다는 것을 알고 있습니다.
=February!E2 - February!E102
=February!E102
그러나 나는한다아니다매월 테이블에 몇 개의 행이 있는지 알고 있으며 이를 최대한 자동화하고 싶습니다. 예를 들어 1월에 누락되거나 중복된 비용을 발견한 경우 모든 수식을 업데이트할 필요가 없습니다. 이는 1월 말 잔액을 의미합니다).Excel이 특정 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월, 2월 등. 이를 사용하여 날짜 열의 날짜 항목 수를 계산하여 마지막 행을 결정합니다.
예를 들어 이름 "Jan"을 삽입하고 RefersTo =COUNT(January!A:A)+2 이렇게 하면 행 개수가 제공되며 수식에서 이를 간접적으로 사용하여 범위 참조를 작성할 수 있습니다.
행의 마지막 셀은 =INDIRECT("E"&Jan)이 되며 원하는 경우 다른 곳에서도 요약 시트에 이러한 참조를 사용할 수 있습니다.
이것은 열 A의 숫자(날짜)를 가정합니다. 텍스트인 경우 헤더를 무시하지만 행 2에는 숫자 값이 없는지 확인하십시오. 그렇지 않으면 행 수가 왜곡됩니다.