수정할 수 있도록 예산 템플릿을 리버스 엔지니어링하려고 합니다. 다양한 지출(가정, 엔터테인먼트, 교통 등)에 대한 섹션이 있습니다. 각 섹션에는 다양한 지출이 있습니다(교통에는 자동차 보험, 주유, 주차 등이 포함됩니다). 각 섹션은 이름이 지정된 범위이며 각 지출은 행 오른쪽 끝에 있는 셀에 합산됩니다. 그러나 템플릿은 섹션의 각 행을 동일한 기능으로 수행하므로 어떻게 작동하는지 알 수 없습니다.
다음은 상단에 기능과 이름 관리자가 있는 사진입니다. 내가 말했듯이 강조 표시된 기능은 '일상 생활'에 대한 모든 행 합계에서 동일한 기능입니다. https://i.stack.imgur.com/mbMij.jpg
나는 정확히 동일한 이름 그룹(이름 관리자와 모든 범위가 동일함)과 동일한 레이아웃을 가지고 있지만 동일한 기능이 오류를 생성하고 허용되지 않도록 할 수 없습니다. 내가 할 수 있었던 최선의 방법은 각 행을 개별적으로 합산하는 것이 아니라 섹션의 모든 행을 합산하는 SUM(Daily)에 넣는 것입니다.
누군가 그림 =SUM(Daily[@[January]:[December]]), 특히 '@' 기호에서 이 기능을 설명할 수 있습니까? 이름 관리자에 없을 때 JAN(열 헤더에 있음)이 1월을 의미한다는 것을 Excel이 어떻게 알 수 있습니까?
보너스: 섹션 합계와 전체 합계 기능이 어떻게 작동하는지 설명해 줄 수 있나요? https://i.stack.imgur.com/ydMfz.jpg
답변1
글쎄요, 많은 질문을 받았는데 각각에 대해 답변해 드리고 싶습니다. 희망.
무엇보다도 데이터 레이아웃은 Table. A는 Table이전에 나타나는 셀 데이터 범위와 Excel의 경우 다릅니다. 이는 간단한 규칙을 따르고 그 대신 Excel을 사용하는 형식적인 개체입니다. 전 세계 누구라도 설정 시 수행할 수 있는 이상한 일을 모두 수용할 필요가 없습니다. (합계 셀이 아래가 아닌 데이터에 나타나는 것을 본 적이 있습니다.) , 위, 왼쪽 또는 오른쪽, 그리고 같은 범위에서 다른 열에 대해 다른 행에 나타납니다. 그것은 동료의 계획에 어렴풋이 맞지만, 그것을 보면 악마가 Excel이라는 것을 알 수 있습니다. 인간에게는 기괴하고 혼란스러운가? 아니면 10명의 인간에게?
어쨌든 테이블 이름은 다음과 같습니다.일일. 그런데 다른 이유가 없다면 수식의 요소가 입력되는 방식으로 인해 표라는 것을 알 수 있습니다. 대괄호("[ ]")를 참조하세요. 이것들은 거의 테이블과 이상한 다른 장소에서만 사용됩니다(아마도 모두 테이블 기술을 사용하여 작업할 것입니다). 어쨌든 간단한 SUM()수식을 만들 때 전체 행을 강조 표시하면 Excel은 강조 표시된 셀 주소를 여기에서 강조 표시한 범위의 가장자리인 제목: 1월부터 12월까지의 열을 기반으로 하는 테이블 참조로 변환합니다. 따라서 D20:O20과 같은 참조를 보는 대신 [January:December]가 표시됩니다.
해당 공식에는 한 가지 요소만 남았습니다.@. 이것은 테이블이므로 Excel에 이 행만 의미한다고 알릴 방법이 없다면 모든 행을 합산하고 있다고 생각할 것입니다. Excel에서 는 @Excel에 "이 행만"을 알리고 표 및 기타 여러 위치에서뿐만 아니라 Spill수식을 사용하는 데에도 크게 사용됩니다. 명시적으로 말하면 Excel에 이 행을 알려주므로 드라이 클리닝 행만 합산하여 1월과 12월 사이의 모든 열에 있는 숫자를 합산합니다.
여러 가지 면에서 SUM()여기처럼 사용되는 것과 SUBTOTAL()(어디서나 사용되는) 것은 같습니다. 그러나 SUBTOTAL()큰 차이점이 있습니다. Excel은 열을 합산할 때 이를 사용하여 얻은 소계를 기꺼이 무시합니다. 그러나 생성된 부분합은 무시되지 않으므로 SUM()일부 원시 숫자와 동일한 열에 있는 경우 필요한 합계의 두 배를 얻게 됩니다. SUBTOTAL()또한 평균과 같이 그 자체로는 반환할 수 없는 여러 가지 다른 항목이 반환됩니다 SUM().
스프레드시트를 작성한 사람은 합계를 설정하는 데 매우 표준적이고 합리적인 방법을 사용했음이 분명합니다. 그는 수평 합계를 사용하여 SUM()수직 합계가 자동으로 무시되지 않도록 한 다음 SUBTOTAL()표에 표시된 수직 합계를 만드는 데 사용했습니다.
이러한 것 중 어느 것도 자동으로 사용 가능하기 때문에 명명된 범위에 없습니다(자동이므로 때로는 피하기가 어렵습니다...). 모든 것이 테이블이기 때문입니다. 테이블이므로 열 헤더를 참조로 사용할 수 있습니다. 예전에는 이것을 항상 할 수 있었지만 약 17년 전에 가져갔다가 이런 형태로 다시 돌려줬습니다... 윽...
또한 때로는 열 머리글이 자동으로 텍스트이므로 수식이 될 수 없다는 사실이 불쾌합니다. 따라서 테이블에서는 동적 헤더를 사용할 수 없습니다.
테이블처럼 보이지 않는 한 가지 이유는 각 헤더의 오른쪽에 일반적으로 헤더의 일부 위에 있는 불쾌한 필터링 아이콘이 표시되지 않기 때문입니다. 이 아이콘을 없애는 것은 간단하며 작성자는 분명히 그렇게 했습니다. . 이것이 부족하면 대부분은 한동안 "테이블"을 생각하지 않을 것입니다. 그러나 공식 참조의 형성은 아주 좋은 결과입니다. 사실을 모호하게 만드는 또 다른 사실은 대부분의 사람들이 테이블에 "녹색 띠" 모양을 갖는 데 익숙하다는 것입니다(어떤 색상이든 상관없지만 그 종이는 주로 녹색이었고 저는 845세이므로...). 하지만 그것도 적절하지 않을 때 서식을 지정하거나 혐오스러운 것으로 간주될 수 있습니다(왜? 왜? 화면 전체 테이블을 훨씬 더 읽기 쉽게 만듭니다!). 이 경우에는 분명히 속하지 않습니다. 각 데이터 영역에서 이를 사용할 수 있지만 각각의 행 수가 다르기 때문에 계속해서 많은 노력이 필요합니다.
그래서:
- 그것은
Table당신에게 좋은 것들을 제공합니다. - 데이터는 섹션과 하위 섹션(귀하의 경우 개별 행) 헤더가 있는 왼쪽 열의 두 섹션으로 구성됩니다. "드라이클리닝"과 마찬가지로 1월부터 12월까지 제목 아래에 "시간이 지남에 따라 작성" 섹션이 있습니다.
- 각 행은 함수를 사용하여 오른쪽 연도에 대한 합계를 구합니다
SUM(). - 각 열은 함수를 사용하여 월(및 가장 오른쪽 열의 연도)에 대한 합계를 구합니다
SUBTOTAL(). - 함수 는 범위 참조("@January")의 첫 번째 항목 앞에
SUM()를 넣기 때문에 한 번에 한 행에서만 작동합니다 .@ SUBTOTAL()INDIVIDUAL 섹션("INCOME", "HOME" 등)에는 전체 열의 합계를 계산할 때 무시되므로 함수를 사용하여 매월 및 연도의 비트 합계가 있습니다 . (SUM()각 섹션의 결과가 하단 전체에 포함되는 것과 대조적으로 총계는 원래 값보다 두 배의 결과를 제공합니다.)
중요한 세부 사항에 주의를 기울여 매우 그럴듯하고 유용하게 배치되었습니다. 이런 종류의 것을 간단한 테이블이나 테이블 버전에 넣는 것이 더 낫다고 주장할 수도 있습니다. 원할 경우 간단한 테이블과 피벗 테이블(예, 845년 된 것이므로 크로스 테이블, 심지어 크로스 탭, 나에게는 여전히 정상적으로 들립니다.) 프레젠테이션용입니다. 결국, 피벗 테이블, 더 정확하게 말하면 Power Pivot 테이블에는 많은 장점(오오오오... 슬라이서...)과 자체적인 합계 기능 등이 있지만 이는 완전히 다른 것입니다. 배우고 이것은 누군가의 요구를 아주 훌륭하게 해결했습니다.
무엇을 해야 하는지 잘 모르겠어서 도움을 드릴 수 없습니다. 하지만 노력을 어떻게든 향상시키거나 어떻게든 자신의 것으로 만들고 싶다면 실제로 그렇게 할 수는 없습니다. 정말 훌륭하게 완료된 것입니다.피벗 테이블 경로로 가지 않는 한. 따라서 그게 전부라면 문제를 피하십시오. 이것은 매우 괜찮은 수준이며 실제로 개선할 부분이 없습니다. 단지 상사가 뭔가를 해주기를 바라는 것뿐이라면, 글쎄요, 우리 모두 거기에 가본 적이 있을 것 같아요.