[엑셀] 중복값 찾아 나열하는 수식 작성 방법.

안녕하세요.

행아아빠 입니다.

 

오늘 작성해볼 엑셀 내용으로는

이미 작성된 데이터에서 2번이상 중복된 값이 있는 경우 중복된값을 원하는 셀에 1번씩만 나열하도록 정리할 수 있는 수식 작성 방법 입니다.

 

이 내용은 개인적으로 회사 업무 뿐만 아니라 가계부를 엑셀로 작성하시는 분들에게도 굉장히 유용할 것 같습니다.

또한 많은 부분으로 적용하여 시간을 줄일 수 있는 수식이 될 것 같습니다.

도움이 되셨다면

구독과 좋아요, 공유 부탁드리겠습니다. 감사합니다.^^!

 

---

■ 중복값은 1번으로,

    1번 작성된 값을 포함하여 

    모두 1번씩만 나열하여 정리하는 수식 작성 방법.

 

▲ 위 그림처럼 D열에 작성된 날짜를 A열에 옮기고자 할때

    D열의 중복된 날짜는 한번만 작성하고, 1번이라도 작성되어 있는 날짜는 그대로 A열에 1번만 작성하여

    중복된 날짜 없이 정리하고자 할때!!

    활용할 수 있는 수식이 되겠습니다.

    ※ 예시로는 날짜를 예로 들고 있지만, D열에 작성된 내용이 숫자, 텍스트등의 내용이라고 하더라도 동일하게 적용 될 수 있습니다.

    2019-11-01일이 2번

    2019-11-02일이 1번

    2019-11-03일이 5번

    2019-11-04일이 2번

    2019-11-05일이 1번

    위처럼 작성되어 있을때 A열에 중복된 값을 1번으로 작성하여 다시 나열하게 된다면

    2019-11-01

    2019-11-02

    2019-11-03

    2019-11-04

    2019-11-05

    ↑ 이렇게 나열되도록 하여 결과값을 자동으로 값이 표기 되도록 해야겠습니다.

    * 가계부 작성할 때 요즘 카드 많이 사용 하지기 때문에

      카드사 홈페이지에 들어가서 카드 사용내역을 엑셀로 다운받아서 해당 수식이 작성된 규칙에 맞게 내용을 복사해 주시면,

      카드 사용내역을 일별로 따로 정리할 필요 없이 자동으로 사용한 일자가 입력이 될 것 같습니다.

      또한 회사 업무에서는 그래프나 표를 미리 삽입해 놓았을 경우

      데이터를 기준으로 자동으로 결과가 나오게 되기때문에 업무시간을 줄일 수 있겠습니다.

▲ 해당수식을 작성해 보면 아래와 같이 작성 할 수 있겠습니다.

    =IFERROR(OFFSET($D$1,MIN(IF(($D$2:$D$1000<>"")*(COUNTIF(A$1:A1,$D$2:$D$1000)=0),ROW($D$2:$D$100)))-1,),"")

 

    하지만 수식을 작성하였을 때 결과값을 확인해 보면 원하지 않는 이상한 값으로 표기됩니다.

    표기형식이 날짜가 아닌 일반으로 되어 있기 때문인데,

    일단은 수식을 원하는 위치까지 드래그하여 복사해 주겠습니다.

▲ A열을 수식으로 채워넣은 결과값 입니다.

    원하는 값이 아닐뿐더러, A3셀은 공란으로 결과값이 표기되어 보여지게 됩니다.

 

    이것은 방금 말씀드린대로 표기형식의 문제이기 때문에 표기형식을 변경하여 주면 간단하게 해결 되는 것이니

    혹시라도 수식을 작성했을때 원하는 값이 아니거나 형식이 다르게 표기 된다면 표기형식을 한번 확인해 주시면 되겠습니다.

▲ 표기형식을 변경해 주는 것은 위 그림에서 보듯이

    상단의 빨간 네모박스의 "일반"을 날짜로 변경해 주거나

 

    특별히 원하는 양식이 있다면

    표기형식을 변경할 셀을 블럭지정하여 → 마우스 오른쪽을 클릭 → "셀 서식" 클릭 → 사용자지정에서

    YYYY-MM-DD 또는 MM-DD등과 같이 원하는 날짜 표기형식으로 변경해 주시면 되겠습니다.

▲ 표기형식을 YYYY-MM-DD 형식으로 바꿔주었을때 변경되어 나온 값입니다.

    그런데...!!

    A3셀은 공란이고... 나머지 셀들은 2019-11-01일자 하나로만 쭉~~ 입력이 되어 있네요...

​

    ​여기서 잘못된 것은 딱 한가지입니다.

    해당수식은 #배열수식 이기 때문에 반듯이 작성한 수식에 { } 기호를 붙여주어야 합니다.

    기호를 붙여주는 방법은 수식을 모두 작성후에 Enter로 끝내시면 안되고,

    Ctrl + Shift + Enter 을 해 주시면, 자동으로 { } 기호가 들어가게 됩니다.

▲ 수식의 종료를 Ctrl + Shift + Enter 로 배열수식 이란놈으로 종료를 해 주었더니

    A열의 결과값이 원하는 값으로 나오게 됩니다.

    수식입력줄에 보시면 빨간동그라미 양쪽 끝에 { } 의 기호가 들어간 것 보이시죠?? 배열수식을 뜻하는 것입니다!!

---

생각보다 간단하지만 어떤 함수를 써야 할지 또는 함수 수식을 어떻게 작성해야 할지, 어떻게 응용할 수 있을까? 하다가

포기하지 마시고, 찾아보시고 배우셔서 시간이 많이 뺏기는 업무시간 이제 줄이셔서, 야근 하지마시고 칼퇴하시기를 바라겠습니다.

​

그럼, 오늘도 좋은 하루 되세요~~^^!!