%3F.png)
계층적 데이터 세트, 바람직하게는 피벗 테이블 내에서 데이터를 집계하는 방법을 찾으려고 노력하고 있지만 다른 방법도 괜찮을 수 있습니다. 아래와 같은 데이터 세트(예를 들어 매우 단순화됨)를 고려하십시오. 이 데이터를 바탕으로 다음과 같은 질문에 답할 수 있는 함수 세트를 구축하려고 합니다.
"과일의 총 재고는 얼마나 되나요?"
"나는 얼마나 많은 종류의 음식을 팔고 있나요?"
Item Category
======= ========
Apples Fruit
Bacon Meat
Chicken Meat
Corn Veg
Food
Fruit Food
Grapes Fruit
Meat Food
Squash Veg
Steak Meat
Veg Food
각안건(다른 많은 정보 중에서)범주, 실제로는 "부모"라고 생각할 수 있습니다. 그러나 데이터 세트 내에서 모든 "상위"에는 자체 상위 카테고리도 있다는 점에 유의하세요. 이 데이터 세트에서 계층 구조의 하나의 샘플 '분기'는 다음과 같습니다.음식->고기->닭.
"몇 가지 종류의 과일을 판매합니까?"와 같은 질문에 대답하는 것은 어렵지 않습니다. 왜냐하면 이것이 첫 번째 수준 카테고리이기 때문입니다. COUNTIF 함수를 사용하여 ""과일" 카테고리에 속하는 항목 수는 몇 개입니까?"라고 말하면 됩니다. -- 그러면 다음과 같은 테이블이 표시됩니다.
Item Category COUNTIF(categories,me)
Apples Fruit 0
Bacon Meat 0
Chicken Meat 0
Corn Veg 0
Food Food 3
Fruit Food 2
Grapes Fruit 0
Meat Food 3
Squash Veg 0
Steak Meat 0
Veg Food 2
쉬움 - 첫 번째 행에서는 "사과"가 다른 사람의 카테고리로 나타나는 횟수를 확인할 수 있습니다. (0이므로 Apples가 부모가 아니라는 것을 알고 있습니다... 도움이 되겠지만 어떻게 될지는 모르겠습니다...) 이제 행 5인 "과일"이 다른 사람의 카테고리로 두 번 나타납니다. 0이 아닙니다. 단순한 항목이 아닌 카테고리라는 것을 알고 있습니다. 첫 번째 수준의 수학에는 모두 훌륭하고 좋지만...
그러다보니 제가 해결하지 못한 부분이 생겼네요...내가 가지고 있는 "음식"의 총 종류가 몇 개인지 어떻게 알 수 있나요? 그리고 실제 데이터에 더 많은 계층 수준이 있다는 점을 고려하면 트리를 위아래로 이동하여 각 데이터에 총 자식 수가 몇 개인지 파악해야 합니다. 첫 번째 수준 COUNTIF 함수는 음식에 세 가지 하위 범주(과일, 채소, 고기)가 있음을 알려줍니다. 하지만 제가 정말로 원하는 것은 과일, 채소, 고기가 무엇인지 재귀적으로 결정하도록 하는 것입니다.또한 그럴 수도 있다분류하고 해당 어린이에 해당하는 숫자를 합산합니다. Excel 용어로 제가 정말로 원하는 것은 전체 하위 트리에 있는 총 항목 수를 재귀/반복적으로 계산하는 또 다른 열을 구축할 수 있는 것입니다. 이 경우 Food에 속하는 7개의 고유 항목이 있습니다: 고기 3개, 야채 2개, 과일 2개.
몇 가지 복잡한 요소:
없다명백한특정 항목이 카테고리인지 또는 하위 항목인지 여부를 알려주는 데이터의 식별자입니다.
각 항목은 해당 항목의 카테고리/상위 항목만 알고 있습니다. 하위 항목이 있는지 여부를 알려주는 명시적인 데이터는 없습니다. 다르게 말하면 모든 항목은 카테고리에 속하지만 일부 항목만 카테고리이기도 합니다.
실제 데이터에서 상위 관계의 깊이는 10레벨까지 가능하지만 계층 구조의 각 분기 깊이가 일관적이라는 보장은 없습니다. 일부 항목은 3레벨 깊이일 수 있고 다음 항목은 8레벨일 수 있습니다.
루트 또는 최종 상위에는 카테고리가 제공되지 않지만 이는 수동으로 쉽게 처리할 수 있는 일회성 사례입니다.
나는 이것이 '실제' 프로그래밍 언어(Perl, Python 등)에서는 사소한 연습이라는 것을 충분히 알고 있습니다... 하지만 궁극적으로 프로그래밍 경험이 없는 사람에게 이 작업을 넘겨야 하므로 이것을 "표준" Excel 통합 문서에 맞추기 위해 매우 열심히 노력했습니다.
답변1
처음에는 @Raystafarian의 의견에 전적으로 동의합니다. Excel은 이에 적합한 도구가 아닙니다.
그러나 정말로 여기에서 수행하려면 몇 가지 도우미 열이 있는 솔루션이 있습니다.
- level: 계층 구조의 실제 항목 수준(루트 항목은 수준 1, 하위 항목의 수준은 증가)
=IFERROR(INDEX([level],MATCH([@Category],[Item],0))+1,1) - 레벨 코드: 각 항목에 대한 실행 코드, 레벨 내에서 고유함
=CHAR(CODE("a")+COUNTIF($C$2:C2,[@level])-1) - 긴 코드: 상위 및 항목의 연결된 코드
=IF([@level]>1,INDEX([long code],MATCH([@Category],[Item],0)),"")&[@[level code]] - has child: 항목에 자식이 있는지 여부를 알려주는 부울
=COUNTIF([Category],[@Item])>0
fruit이 모델 을 사용하면 카테고리에는 코드가 상위 코드와 동일한 순서로 시작하는 모든 항목과 하위 카테고리가 포함 됩니다 aa.aa
귀하의 질문에 대한 답변:
"과일의 총 재고는 얼마나 되나요?"
=COUNTIFS(Table1[long code],VLOOKUP(I3,Table1,5,FALSE)&"*",Table1[has child],FALSE)
모델에 따라 모든 항목은 동일한 시작 순서를 갖습니다. 여기에는 카테고리가 아닌 항목만 포함되어 있지 않습니다(두 가지 유형의 과일을 판매하고 apples있으며 판매하라는 grapes제품이 없습니다 fruit). 카테고리도 계산하려면 수식의 두 번째 부분을 제외하면 됩니다.
"나는 얼마나 많은 종류의 음식을 팔고 있나요?"
=SUMIF(Table1[long code],VLOOKUP(I10,Table1,5,FALSE)&"*",Table1[inventory])
꽤 비슷하다SUMIF
경고
이 솔루션에는 두 가지 제한 사항이 있습니다.
- 문자 수: 현재
a코드는 97이고CHAR함수에서 마지막으로 지원되는 문자는 255이므로 모든 레벨에서 158개 이상의 서로 다른 범주를 사용하면 오류가 발생합니다(문자를 사용하여 약간 확장할 수 있음). 첫 번째 코드는 더 작은 코드) - 데이터베이스가 증가함에 따라 성능이 저하될 수 있습니다(복잡한 계산). 계산 방법을 "데이터 테이블을 제외하고 자동"으로 설정하고 필요할 때 수동으로 계산할 수 있습니다.
답변2
Máté가 좋은 대답을 했다고 생각합니다. 내가 할 방법은 목록을 사용하는 것입니다.
(모든 배열 수식이므로 Ctrl+ Shft+ Enter)
따라서 수식은 (아래로 드래그)
D2 =INDEX($A$2:$A$12,MATCH(0,IF(ISBLANK($B$2:$B$12),COUNTIF($D$1:$D1,$A$2:$A$12),""),0))
E2 =INDEX($A$2:$A$12,MATCH(0,IF($B$2:$B$12=$D$2,COUNTIF($E$1:$E1,$A$2:$A$12),""),0))
F2 =INDEX($A$2:$A$12,MATCH(0,IF($B$2:$B$12=$E$2,COUNTIF($F$1:$F1,$A$2:$A$12),""),0))
G2 =INDEX($A$2:$A$12,MATCH(0,IF($B$2:$B$12=$E$3,COUNTIF($G$1:$G1,$A$2:$A$12),""),0))
H2 =INDEX($A$2:$A$12,MATCH(0,IF($B$2:$B$12=$E$4,COUNTIF($H$1:$H1,$A$2:$A$12),""),0))
이제 정렬 방법에 따라 계층 구조를 만들거나 피벗 테이블을 사용할 수 있습니다.
명명된 범위로 작업할 수 있도록 A 및 B 열 이름을 지정했을 것입니다.