☰

공유 값을 기준으로 데이터 열 그룹화

tag-icon • tag-icon microsoft-excel
공유 값을 기준으로 데이터 열 그룹화

내가해야 할 일을 제대로 설명하는 방법을 모르므로 예를 들어 보겠습니다. 동료가 Excel에 다음과 같은 데이터 세트를 가지고 있습니다.

Col A    Col B    Col C
aaaaa    aaaaa    bbbbb
bbbbb    ccccc    ccccc
ccccc    ddddd    eeeee

최종 결과는 다음과 같아야 합니다.

Col A    Col B    Col C
aaaaa    aaaaa
bbbbb             bbbbb
ccccc    ccccc    ccccc
         ddddd
                  eeeee

또는:

      Col A    Col B    Col C
aaaaa Yes      Yes      No
bbbbb Yes      No       Yes

등.

(도움이 된다면, 열은 단백질 추출 방법이고 문자는 단백질 ID입니다. 어떤 단백질이 어떤 방법으로 추출되는지 확인해야 합니다)

내 동료가 이 작업을 수동으로 수행하고 있지만 자동화하는 것이 정말 도움이 될 만큼 충분한 데이터가 있습니다.

Excel에 이 작업을 자동으로 수행하는 수식이 있습니까?

답변1

이는 "턴키" 솔루션은 아니지만 수천 개의 행이 있는 경우에는 약간의 노력을 덜 수 있습니다. ("실행 취소"가 항상 작동하는 것은 아니기 때문에 무언가가 터지거나 녹을 경우를 대비하여 파일의 스크래치 복사본에서 이 작업을 수행하십시오.) 참고: 이 절차는 Excel 2007용으로 개발되었습니다(그러나 Excel 2007에서 다시 확인했습니다). 엑셀 2013).

먼저 모든 데이터를 스크래치 열에 복사합니다. 이를 V라고 부르겠습니다. A열의 제목을 복사하거나 V1 셀에 더미 값을 입력해야 합니다.

데이터 복사 그림

이제 "데이터" 탭, "정렬 및 필터" 그룹으로 이동하여 "고급"을 클릭하세요.

                        '고급'이 강조 표시된 '정렬 및 필터' 그룹

그러면 "고급 필터" 대화 상자가 나타납니다.

                  "고급 필터" 대화 상자

"목록 범위"가 V열에 데이터를 표시하는지 확인합니다. "다른 위치에 복사" 및 "고유 레코드만"을 선택합니다. "복사 대상" 필드에 "W1"을 입력하거나 필드를 클릭한 다음 W1을 클릭합니다(동일한 결과를 얻을 수 있는 여러 기술이 있습니다). “확인”을 클릭하세요. 다음과 같은 결과를 얻어야 합니다:

                              중복 항목이 제거된 V의 데이터가 W로 복사되었습니다.

즉, 고유한 데이터 값 목록입니다. W열을 정렬해야 할 수도 있습니다.

이제 =NOT(ISNA(VLOOKUP($W2,A$2:A$4,1,FALSE)))X2를 입력하고( 를  4데이터가 포함된 마지막 행의 번호로 대체) W 열(즉, 원본 데이터의 각 고유 값에 대해 한 행)과 일치하도록 아래로 끌어서 채우고 Z ​​열( 즉, 데이터의 열 수).

                              각 값이 각 열에 있는지 여부에 대한 TRUE/FALSE를 표시하는 테이블

그러면 질문에서 원하는 결과의 두 번째 형식에 해당하는 진리표가 제공됩니다(그러나 "예"와 "아니요" 대신 "TRUE"와 "FALSE"가 사용됨). 예를 들어,

  • A 열에 "aaaaa"가 포함되어 있으므로 X2는 TRUE입니다.
  • A 열에 "bbbbb"가 포함되어 있으므로 X3은 TRUE입니다.
  • B열에 "aaaaa"가 포함되어 있으므로 Y2는 TRUE입니다.
  • B열에 "bbbbbb" 등이 포함되어 있지 않기 때문에 Y3은 FALSE입니다.

V열을 삭제하고 여유 시간에 제목(행 1)을 수정하세요. 스프레드시트에 AC 열을 유지하지 않으려면 WZ 열을 복사하여 값을 붙여넣으세요.

공식에 대한 설명: 위에 제시된 공식은 X열에 사용하기 위한 것입니다. A열에 해당합니다.  를 사용했기 때문에 $W2이는 W열에 대한 절대 참조이며 셀을 참조합니다.Wn 이는 W열에 대한 절대 참조이며 수식이 행으로 드래그/채워질 때 N어떤 열의. 이와 대조적으로 는 A$2:A$42~4행에 대한 절대 참조이지만 A열에 대한 상대 참조입니다. 수식을 Y열로 드래그하면 이 참조는 자동으로 로 변경됩니다  B$2:B$4. 수식을 Z열로 드래그하면 이 참조가 자동으로 로 변경됩니다  C$2:C$4.

관련 정보