중복 항목 수를 계산하고 선택한 셀에 값을 반환한 다음 중복 항목을 삭제하는 수식을 만들려면 어떻게 해야 합니까?

Question 1

내 제안은 첫 번째 부분(정렬된 제품 목록)에만 적용됩니다.

예를 들어 Excel 테이블에 목록을 배치 Table1하고 이 목록을 로 참조할 수 있습니다 Table1[List].
열에는 개수 C와 D함께 유니케이트 목록이 표시됩니다.
수식 C2: =UNIQUE(Table1[List])
수식 D2: =COUNTIF(Table1[List],C2#)
다음 단계에서는 열 F및 에 표시된 대로 이 두 열을 정렬할 수 있습니다 G.
공식 F2:=SORT(HSTACK(C2#,D2#),2,-1)

Answer

내 제안은 첫 번째 부분(정렬된 제품 목록)에만 적용됩니다.

예를 들어 Excel 테이블에 목록을 배치 Table1하고 이 목록을 로 참조할 수 있습니다 Table1[List].
열에는 개수 C와 D함께 유니케이트 목록이 표시됩니다.
수식 C2: =UNIQUE(Table1[List])
수식 D2: =COUNTIF(Table1[List],C2#)
다음 단계에서는 열 F및 에 표시된 대로 이 두 열을 정렬할 수 있습니다 G.
공식 F2:=SORT(HSTACK(C2#,D2#),2,-1)

Question 2

다음 데이터가 있고 사용하는 경우 MS365다음 방법을 수행할 수 있습니다.

섬유	칼슘	철	마그네슘	아연	구리	망간	요오드	셀렌	불화
강낭콩	우유	강화 시리얼	호박씨	소고기	게	밀 배아	구운 감자	브라질 땅콩	홍차
렌틸 콩	요거트	밀기울 플레이크	아몬드	호박씨	랍스터	고구마	우유	해바라기 씨	새우
핀토 콩	파마산	거칠게 탄 옥수수	시금치	해바라기 씨	표고버섯	콜라드 그린	새우	참치	건포도
병아리콩	스위스	그래 놀라	검은 콩	렌틸 콩	흰색 버튼	완두콩	칠면조	연어	라즈베리
리마 콩	코티지 치즈	잘게 썬 밀	연어	칠면조	포터벨라	소나무 견과류	강낭콩	랍스터	요리된 오트밀
아보카도	시금치	쌀크림	요거트	달걀	크레미니스	헤이즐넛	달걀	새우	맥주
호박씨	콜라드 그린	소고기	우유	표고버섯	고구마	피칸		돼지고기 안심	적포도주
참깨	순무잎	갈은 쇠고기	아보카도		순무잎	귀리		소고기	하드 사이다
아몬드	브로콜리	쇠고기 안심	바나나		구운 감자	리마 콩		양고기	거칠게 탄 옥수수
피스타치오	검은 눈 완두콩	버팔로 등심			시금치	병아리콩		닭	검은 콩 수프
피칸	강낭콩	들소			해바라기 씨	강낭콩		칠면조	구운 감자
해바라기 씨	연어	술집			호박씨	렌틸 콩		강낭콩	당근
도토리 스쿼시	도토리 스쿼시	쇠고기 스튜 고기			브라질 땅콩	시금치		핀토 콩	시금치
허바드 스쿼시	랍스터	돼지고기 안심			호두	파인애플		리마 콩	멜론

• --> GROUPBY()에만 해당되는 기능을 사용합니다 .MS365Office Insiders

=LET(
     _Data, A3:J16,
     _SingleCol, TOCOL(_Data,1),
     GROUPBY(_SingleCol,_SingleCol,ROWS,,0,-2))

또는, BYROW()또는MMULT()

=LET(
     _Data, A3:J16,
     _SingleCol, TOCOL(_Data,1),
     _Uniq, UNIQUE(_SingleCol),
     _Counts, MMULT(N(_Uniq=TOROW(_SingleCol)),SEQUENCE(ROWS(_SingleCol))^0),
     HSTACK(_Uniq,_Counts))

=LET(
     _Data, A3:J16,
     _SingleCol, TOCOL(_Data,1),
     _Uniq, UNIQUE(_SingleCol),
     _Counts, BYROW(_Uniq,LAMBDA(x,SUM(--(x=_SingleCol)))),
     HSTACK(_Uniq,_Counts))

위의 모든 공식은 각 고유 레코드의 개수를 제공합니다.
함수를 사용하여 TOCOL()여러 범위 데이터를 중복을 제외하고 단일 열 배열로 변환합니다.
방법 1--> 첫 번째 방법에서는 우리가 지정한 필드에 따라 설계된 GROUPBY()함수를 group사용 했습니다. 그러나 해당 기능은 해당 옵션을 활성화하지 않는 한 작동하지 않으며 오류로 표시됩니다.aggregatesortOffice-Insiders#NAME!
방법 2--> 또한 LET()읽기를 더 쉽게 만들고 중복된 범위/수식의 사용을 줄이는 함수로 시작합니다.
첫 번째 방법과 마찬가지로 먼저 TOCOL()함수를 적용한 다음 UNIQUE()함수를 사용하여 고유한 값을 반환하고 마지막으로 MMULT()함수를 사용하여 주어진 두 배열의 행렬 곱인 개수를 반환합니다.
마지막으로, 를 사용 HSTACK()하여 반환된 배열 UNIQUE()과 MMULT()함수를 모두 결합하여 원하는 출력을 반환합니다.
방법 3BYROW()--> 각 항목의 일치하는 개수를 가져오는 기능을 사용한다는 점을 제외하면 위와 동일한 단계로 시작합니다 .

그러나 이러한 방법이 복잡하다고 생각되면 아래에 표시된 가장 간단한 방법을 사용하십시오.

먼저 TOCOL()& UNIQUE()함수를 사용하여 비어 있는 항목과 중복 항목을 제외한 여러 범위를 하나의 배열로 반환합니다. 그리고 셀에 넣으세요.L2

=UNIQUE(TOCOL(A3:J16,1))

마지막으로 다음 함수를 입력하여 개수를 가져옵니다. A3:A16귀하의 소송에 따라 변경해야 할 수 있는 데이터 범위를 취했음을 기억하십시오 . 다음은 쏟아질 수 있으므로 채울 필요가 없습니다. L2위에서 다음을 사용하여 반환된 수식은 어디에 있습니까 TOCOL()?UNIQUE()

=COUNTIFS(A3:J16,L2#)

POWER QUERY여전히 위의 내용을 따르기를 꺼려하는 경우 이후 부터 사용 가능한 Excel 2010+, 에서 사용 가능한 항목 을 사용할 수 있습니다 Windows Excel 2010+.Excel 365 (Windows or Mac)

파워 쿼리를 사용하려면 다음 단계를 따르세요.

먼저 소스 범위를 테이블로 변환하고 그에 따라 이름을 지정합니다. 이 예에서는 이름을 다음과 같이 지정했습니다.Table1

Data다음으로 Tab --> Get & Transform Data--> Get Data--> From Other Sources--> 에서 빈 쿼리를 엽니다.Blank Query

위의 내용을 Power Query사용하면 이제 Home탭 --> --> 보이는 내용을 제거하여 Advanced Editor다음을 붙여넣은 후 창을 열 수 있습니다.M-CodeDone

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Value"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

마지막으로 다시 가져오려면 Excel--> 클릭 Close & Load또는 Close & Load To--> 클릭한 첫 번째 항목은 New Sheet필요한 출력을 생성하고 후자는 결과를 어디에 배치할지 묻는 창을 표시합니다.

^{참고: 수식은 중복 항목을 삭제할 수 없으며 대신 특정 조건에 따라 셀에 배치될 때 값을 추출합니다. 위에 보여드린 것처럼요. 원하는 출력을 얻으려면 기본적으로 UNIQUE(), TOCOL()& COUNTIF()또는 기능이 필요합니다.COUNTIFS()}

다음의 이름이 Rough List있는 빈 셀에 이 수식을 넣기만 하면 됩니다 .ListTable

=LET(
     _Uniq, UNIQUE(List),
     _Output, SORT(HSTACK(_Uniq, COUNTIF(List,_Uniq)),2,-1),
     VSTACK({"Nutrients","Counts"},_Output))

해당되는 경우 기능을 사용하면 더 쉽습니다 GROUPBY().

=GROUPBY(List,List,ROWS,,0,-2)

Answer