'리스트정리'에 해당되는 글 2건

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

엑셀을 하시다가 두 그룹의 리스트를 합쳐야 하는 경우가 있습니다. 쉽게 하는 방법은 그냥 복사+붙여 넣기 하면 쉽게 합쳐집니다. 하지만, 여기서 문제는 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))

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

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

[Excel] 리스트 중 빈셀 없애고 정리하는 법

다양한 데이터를 다루다 보면 엑셀을 필수적입니다. 그래서 엑셀을 잘 사용하기 위해 계속 공부하고 함수들을 합쳐보기도 하고, 새로운 함수도 배우기도 합니다. 그중 유용하게 쓰고 있는 엑셀 방법을 소개하고자 합니다. 리스트 중의 빈셀을 없애고 정리하는 법입니다.

<리스트 중 빈셀 없이 정리하는 법>

예를 들어 아래와 같은 상황입니다.

저기서 빈셀을 없애고 정리를 하고 싶습니다. 이게 몇 줄 안된다면 지워가며 정리하면 되지만 1000행이 넘어간다면 어떨까요? 그러면 함수를 이용해서 정리를 하게 되면 손쉽게 할 수 있을 것입니다. 하지만 기본적으로 제공되는 함수 중에서는 이를 해결할 만한 것은 없어 보입니다. 그래서 사용할 수 있는 함수를 만들어 보기로 했습니다.

1. 리스트에 값이 있느냐 비어 있느냐 검사하기

같지 않다 (<>)를 이용하여 함수를 우선 만들었습니다. 그랬더니 TRUE or FALSE로 나타납니다. 여기서 우리가 정리할 건 TRUE로 나타나는 것들입니다.

2. 행 수를 구하여 값을 부여합니다.

ROW 함수를 이용하여 행수를 구하고 이를 나눠줌으로써 위에서부터 큰 값을 갖게 임의의 값을 부여합니다. (FALSE는 0으로 나타나도록)

3. 임의의 값을 정리

LARGE 함수를 이용하여 0으로 나온 값을 제외하고, 큰 수부터 차례대로 정리합니다. 

4. 리스트에 맞는 순번을 찾습니다.

2번 단계에서 부여한 임의의 값을 이용하여 각 값들이 리스트에서 몇 번째 인지 계산합니다. 즉, 2번 단계에서 부여한 방법을 역으로 그대로 취한 다음 1을 빼주면 되겠죠?? (1을 빼는 이유는 2행부터 리스트가 시작하기 때문입니다.)

5. 리스트에서 각 번호에 맞는 값을 불러옵니다.

INDEX 함수를 이용하여 리스트에서 몇 번째에 해당하는 값을 불러옵니다.

6. FALSE로 나왔던 부분을 제거해 줍니다.

#DIV/0! 이게 보기 싫으니 제거해 주기로 합시다. "IFERROR" 함수를 이용하여 빈칸이 되도록 해줍니다. 최종적으로 원하는 대로 빈 셀들은 제거되고, 값이 있는 리스트로만 정리가 되었죠?

최종적인 함수의 형태입니다.

{=IFERROR(INDEX($B$2:$B$21,1/LARGE(($B$2:$B$21<>"-")/ROW($B$2:$B$21),ROW(A1))-1),"")}

필요하신 분들은 이 함수를 변형하셔서 사용하시면 될 것 같습니다. 이 함수는 제가 어렵게 정리한 걸 수도 있습니다. 정답이 아닐 수도 있지만 잘 정리가 되는 결과를 보여줍니다. 이를 활용하여 각자 자신에게 맞는 함수로서 사용하시기 바랍니다.