'빈셀제거'에 해당되는 글 1건

[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),"")}

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