'Excel'에 해당되는 글 3건

[Excel] 순위 확인 및 공동 순위 찾아내기

엑셀로 공동 순위를 찾아내는 방법을 설명드리려고 합니다. 말씀드리면 이 방법은 많은 데이터에 사용해 보거나, 심심할 때 해보는 방법일 뿐이지, 수작업으로 하는 것이 훨씬 빠를 경우가 있습니다. 수작업이 빠르시면 수작업으로 하시는 것을 추천드립니다.

공동 순위를 찾아내는 방법입니다.

예를 들어 위와 같은 데이터가 있을 경우 1등부터 순위를 나열하고 공동 순위로는 누가누가 있는지 알고 싶을 것입니다. 그럼 가장 먼저 순위를 매겨 보도록 하겠습니다.

1. 순위 매기기

RANK라는 함수를 사용하면 순위를 매길 수 있습니다. RANK(값, 범위) 이런 식으로 작성하면 선택한 값이 범위 중에 몇 번째 인지 알 수 있습니다.

이렇게 하면 각 ID마다의 순위를 알 수 있습니다. 순위를 오름차순으로 정렬하게 되면 편하게 볼 수 있을 것입니다. 데이터가 적으면 아래와 같이 보면 더 편하게 정리가 가능합니다. 

하지만 데이터가 많거나 하나의 표로 정리하고 싶은 충동이 들 수도 있습니다. 그러기 위해 표로 정리해 보도록 하겠습니다.

2. 표로 정리하기

표로 정리하기 위하여 순위 중에서 원하는 순위만 골라내 보도록 하겠습니다. IF 함수를 이용하여 원하는 순위만 남기고 그 순위가 해당하는 행의 값이 남도록 하겠습니다.

IF($C$3:$C$16=$E4,ROW($C$3:$C$16),"")

이 함수를 이용하니 원하는 순위의 행의 값이 남게 되고, INDEX 함수를 사용하면 각 순위에 해당하는 ID가 불러와지게 됩니다. 하지만 공동 순위가 문제가 됩니다. 공동 순위에 해당하는 ID가 전부 불러오는데 문제가 생기게 됩니다. 이를 해결하기 위해 SMALL 함수를 이용하여 표에 나타날 수 있도록 하겠습니다.

SMALL(IF($C$3:$C$16=$E4,ROW($C$3:$C$16),""),COLUMN()-5)

위의 함수를 이용하니 표에 원하는 셀 (F열)에 IF 함수로 원하는 순위만 남은 값 중에 가장 위에 값 (첫 번째 순서)을 불러올 수 있게 됐습니다. 그리고 G열에는 원하는 순위 중 2번째 순서로 나온 값을 불러올 수 있게 됐습니다. 이제 여기에 INDEX 함수를 붙여서 표를 드래그해보도록 하겠습니다.

IFERROR(INDEX($A$3:$A$16,SMALL(IF($C$3:$C$16=$E4,ROW($C$3:$C$16),""),COLUMN()-5)-2),"-")

위의 그림과 같이 표에 정리가 되게 됩니다. 각 순위에 해당하는 ID가 불러와지고, 공동 순위의 경우 열 순서대로 나오게 됩니다. 공동 순위가 많아질수록 표가 가로로 길어질 것입니다. 

위의 예시는 간단히 보여드리려고 데이터를 적게 하였지만, 데이터가 많아지게 되면 유용하게 쓰일 수 있을 것 같습니다. 아니면 심심하신 분들도 한번 해보시길 추천드립니다. 엑셀 공부하는 셈 치고 하는 것도 재미있습니다.

*참고: 위의 함수는 표 안의 'F4'의 기준으로 작성된 것입니다.

[엑셀] 두그룹 리스트 합치기 (공통항목 제외하기)

엑셀을 하시다가 두 그룹의 리스트를 합쳐야 하는 경우가 있습니다. 쉽게 하는 방법은 그냥 복사+붙여 넣기 하면 쉽게 합쳐집니다. 하지만, 여기서 문제는 A 그룹과 B 그룹에서 공통으로 들어간 항목이 있어 겹친다는 것입니다.

예를 들면 아래와 같습니다.

복사+ 붙여 넣기 하면 색칠한 것과 같이 저렇게 몇 개의 항목이 겹쳐지게 되는 문제점이 있습니다. 겹치는 항목을 제외하고 목록을 합치는 법을 설명하고자 합니다.

<전체적인 방법 설명>

방법은 위의 예시 표를 기준으로 설명하겠습니다. 우선 이과를 기준으로 문과에서 이과에 해당되는 과목이 있으면 이를 제외한 리스트를 만들어서 합칠 예정입니다.

1. 이과와 문과 공통인 과목 찾아내기!

이는 'VLOOKUP' 함수를 이용하여 실시했습니다. 

=IFERROR(IF(VLOOKUP(B2,$A$2:$A$9,1,FALSE)=B2,"-"),B2)

위와 같이 함수를 이용하여 같은 과목이면 "-"표시를 하게 했고, 다른 과목이면 그냥 그대로 표시되게 했습니다.

위의 표는 그 결과입니다. '문과' 과목 중에서 '이과'와 공통인 과목은 지워지고 '문과'에만 있는 과목들이 남았습니다. (문과-1 열)

2. 문과의 남은 과목 리스트 정리

문과의 남은 리스트를 지난번에 포스팅했던 것과 같이 위쪽으로 몰아서 정리를 해 주었습니다.

지난번 리스트 빈칸 없애고 정리하는 법을 포스팅했던 내용 링크입니다. 

2020/06/30 - [일상 이것저것] - [Excel] 리스트 중 비셀 없애고 정리하는 법

위의 링크대로 정리해주면 아래와 같이 됩니다. (문과-2 열)

=IFERROR(IF(VLOOKUP(B2,$A$2:$A$9,1,FALSE)=B2,"-"),B2)

3. 이과와 문과 합치기

이제 '이과'와 '문과-2'의 두열을 함수 "OFFSET"을 이용하여 합칠 예정입니다. '이과'를 전부 불러오고, '이과'에서 끝나게 되면 '문과-2'로 넘어가서 불러오라는 함수를 사용할 예정입니다. '이과'에서 다 불러오고 그다음 빈칸을 넘어오게 되면 "OFFSET" 함수가 '숫자 0'으로 불러오게 됩니다.

이를 이용하여 '숫자 0'이 나오면 '문과-2'로 넘어가라고 "IF" 함수를 이용하였습니다.

=IF(OFFSET($A$1,ROW(A1),0)=0,OFFSET($D$1,ROW(D1)-COUNTA(A:A)+1,0),OFFSET($A$1,ROW(A1),0))

그 결과 나온 표입니다. 기존에 복사+붙여 넣기 한 항목과 비교해 보면 공통된 과목이 제외된 것을 보실 수 있습니다.

두열을 합치는데 공통된 내용을 제외하고 합치는 것은 이제 하실 수 있을 거라 생각합니다. 손으로 하나하나씩 찾아가며 하기에는 시간도 걸리고 실수도 있을 수 있으니 이렇게 사용하면 편할 것 같습니다. 또한, 지금 예시는 짧은 목록이지만 목록이 몇천 개 이상이라면 당연히 위와 같은 방법이 유용하게 쓰일 것 같습니다.

[엑셀] 겹치지 않게 난수 생성하기

엑셀 난수 생성한다고 하면 가장 먼저 떠오르는 함수는 바로 "RANDBETWEEN" 일 것입니다. 하지만 "RANDBETWEEN"의 단점은 난수를 생성하지만, 반복한 값이 나올 수 있다는 것이 문제입니다.

바로 위와 같은 상황입니다. 각 칸에는 RANDBETWEEN(1,100)을 입력했습니다. 난수 값은 1~100 중에 겹치지 않게 20개를 선택하고 싶을 때 위와 같은 일 때문에 문제가 생깁니다. 그래서 이런 상황을 없애보려고 합니다.

1. 우선은 겹치는 것이 무엇인지 확인해 봤습니다.

함수는 =IF(COUNTIF($B$4:B14, B14)=1,B14,"-") 이렇게 사용했습니다. 즉, 난수 생성해서 첫 번째 행부터 해서 선택된 행까지 반복된 숫자가 1개 이상이면 "-" 표시가 나타나게 했습니다.

2. 이후에는 지난번 설명했던 리스트 정리 함수를 이용해서 정리를 합니다.

2020/06/30 - [Excel] - [Excel] 리스트 중 비셀 없애고 정리하는 법

그러면 리스트가 정리되었습니다. 겹치지 않게 원하는 범위에서 원하는 개수만큼 난수를 생성했습니다.

이상 겹치지 않게 난수 생성하는 방법이었습니다.