4차원 아이디어와 IT

엑셀로 달력은 서식파일을 불러오는 방법도 있지만 이미 만들어져있는 달력을 수정해서 사용할 수 없을때 간단하게 함수를 이용하여 달력을 만들수 있습니다.

제가 개인적으로 사용하는 방법이니 여러분들도 따라하시어 간단하게 달력을 만들어 보세요.

1. 드롭다운 만들기

※ 년도와 월 데이터를 이름관리자로 만들었습니다.

위 그림처럼 드롭다운이 만들어 졌습니다.

2. 드롭다운 선택된 년도와 월을 가지고 함수 작성.

1의 함수 : "=DATE($J$2,$J$3,1)"

  - 년도(J2), 월(J3)로 날짜를 만드는 함수입니다.

  - DATE함수는 "DATE(연도, 월, 일)"을 넣어 날짜를 만드는 함수입니다.

  - 일은 1일로 지정(1일이 없는 년,월은 없으니까요)

2의 함수 : "=TEXT($A$1,"AAA")"  

  - 선택한 년,월,일이 무슨요일인지를 알아야 합니다. 

  - TEXT함수는 어떤형식으로 값을 출력할지를 임의로 지정하는 함수입니다.

  - TEXT(값, 형식)은 값인 날짜를 요일("AAA")로 반환하도록 했습니다.

3의 함수 : "=IF($B$1=A2,1,"")"

  - 그 요일이 2번행에 적혀있는 요일중에 어디에 위치해야 하는지를 알기 위해 1을 반환하는 함수를 작성하였습니다.

  -  A3셀을 드레그앤 드롭으로 G3까지 넣으세요.

※ 여기까지 따라하셨으면 기준년도와 기준월에 따라 3행에 1이 일~토중에 어디에 있는지 왔다갔다하는것을 볼 수 있습니다. 기준년도 : 2023, 기준월 : 11 로 변경해보시면 D3에 1이 표시되는걸 알 수 있습니다.

1의 함수 : "=COUNTIF(OFFSET($A$3,0,0,1,COLUMN()),1)"

  - COUNTIF는 범위안에 특정 값이 몇개 있는지 세는 함수입니다.

  - OFFSET은 범위를 만드는 함수입니다.

  - A4셀을 G4까지 드래그앤드롭하세요.

  - COUNTIF(OFFSET(A3부터, 아래로 0칸, 오른쪽으로 0칸, 아래로는 1행이 범위, 우측으로는 컬럼 수 만큼 범위),1을 찾아라) 이러한 의리입니다. OFFSET을 사용하여 유동적인 범위가 되었습니다. 이 함수를 B4에 입력되었다면 COLUMN은 B열이므로 A=1, B=2 로 2라는 값을 반환하여 A3~B3까지가 범위가 되는 것이고, 그안에서 1을 찾는 것입니다.

 - 예시는 수요일에 1이 있습니다. 따라서 D4~G4까지만 1이 반환될것입니다.

2의 함수 : "=SUM(OFFSET($A$4,0,0,1,COLUMN()))"

 - 1과 같은 논리로 함수가 입력된 곳까지 더할 수 있도록 했습니다.

 - A5에 입력하시고 G5까지 드래그 앤 드롭하세요.

3의 함수 : "=IF(A5=0,"",DATE($J$2,$J$3,A5))"

 - 만약 A5가 0이라면(11월 1일이 수요일이므로 그 전까지는 0이 반환됩니다.)아무것도 출력하지 말고, 아니라면 DATE함수를 사용하여 년도는 J2, 월은 J3, 일은 A5로 날짜를 반환하는 함수입니다.

 - A6셀을 G6까지 드래그 앤드롭하세요.

 - C5까지는 0이므로 아무것도 출력하지 않을테고 수요일(D6)부터 날짜를 적을 것입니다.

 - 보이는 5자리 숫자는 날짜이며, 형식을 날짜로 바꾸면 날짜로 보입니다.

 - 날짜로 변경하여 보기 쉽게 해주세요

3. 달력만들기

1의 함수 : =$J$2&"년도 "&$J$3&"월"

 - 몇년 몇월 달력인지 표기하기 위한 수식으로 &를 사용하여 만들었습니다.

2의 함수 : =A6

 - 미리 만들어 놓은 6행에 있는 날짜를 불러오는 것입니다.

 - I8셀을  O8까지 드래그엔 드롭하세요.

3의 서식 

 - Ctrl+1을 눌러서 서식창을 여세요. 

 - 사용자지정에 형식은 dd(일자만 표시)로 입력하세요.

1의 함수 : =IF(O8="","",IFERROR(IF(MONTH(O8+1)=MONTH($A$1),O8+1,""),""))

 - IF(O8="","", 부분 : 만약 O8(전주 토요일)이 비어있다면 아무것도 하지 않은다.

 - IF(MONTH(O8+1)=MONTH($A$1),O8+1,"") 부분 : 만약 O8(전주 토요일)+1의 월이 A1(해당월의 1일)의 월과 같다면(요일날짜에서 +1일이 11월이라면 이라는 의미입니다.) O8+1을 하고, 아니라면 공백을 반환

 - IFERROR(함수,"") 부분 : 만약 에러가 난다면 공백을 반환

2의 함수 : =IF(I10="","",IFERROR(IF(MONTH(I10+1)=MONTH($A$1),I10+1,""),""))

 - 1의 함수와 같으나 더해야 하는 날짜가 전주 토요일이 아닌 바로 왼쪽에 있는 전일을 더하는 것입니다. 

 - J10셀을 O10까지 드래그 앤 드롭하세요.

계산만 하고 볼 필요가 없는 A~G열까지는 숨기기 처리 하세요.

완성된 달력을 볼 수 있습니다.

안녕하세요~

이번에는 택배 정리나 기타 데이터를 정리할때 여러개의 행이 중복되는것을 볼 수 있습니다.

이때 이런 데이터를 하나만 남기고 싶다라는 생각이 들때 작업하는 방법을 알려드릴께요!!

1. 함수를 이용해 특정 열을 기준으로 하나만 남기고 싶을때

예시는 그림의 A열을 하나로 만들고자 할때 H열에 입력된 수식입니다.

 논리 : 위에서 부터 차례대로 살펴보고 중복된것이 있는지 확인 후 불러오기.

 수식 : =IF(COUNTIF(OFFSET($A$1,0,0,ROW(),1),A2)=1,MAX(OFFSET($H$1,0,0,ROW()-1,1))+1,"")

 해석 : 만약 A1부터 수식을 입력한 줄까지 범위에서 A2를 찾고, 그것이 1이라면(중복되었다면 1이상입니다.) 수식이 입력된 맨 위부터 바로 위까지 가장 큰수에서 +1을 해라.

저러한 수식을 입력하면 중복되지 않은 값에만 연번이 출력됩니다. 그럼 이제 하나로 나열하면 되요.

그림과 같이 연번을 순서대로 작성하고, K2셀에 수식을 입력합니다.

 논리 : A열을 가져오는데 조건은 J2에 적힌 숫자(연번)를 보고 H에 일치하는 행의 값을 가져와라.

 수식 : =INDEX(A:A,MATCH($J2,$H:$H,0),1)

 해석 : INDEX(A:A 는 찾을 곳입니다. $값을 안붙인것은 오른쪽으로 복사했을때 A~G까지 이동합니다.

         MATCH($J2,$H:$H,0) 는 찾아야 할 값은 $J2입니다. $표시는 복사되었을때 숫자는 움직이고, J열은 움직이지 말라는 뜻이예요. J2의 값을 H에서 찾는것입니다. $H:$H의 $표시는 고정이며, 복사했을 움직이지 말라는 뜻이예요. 뒤에 있는 0은 정확히 일치했을때입니다.

간단하게 함수를 이용한 방법을 적어 봤습니다.

2. 엑셀 함수/필터를 이용해 특정 열을 기준으로 하나만 남기고 싶을때

이번에도 함수를 살짝 넣어줘야 해요.

 논리 : 위에서 부터 차례대로 중복된 값 숫자세기.

 수식 : =COUNTIF(OFFSET($A$1,0,0,ROW(),1),A2)

 해석 : A1부터 수식을 입력한 줄까지 범위에서 A2를 찾아라.

이렇게 입력하면 중복된 것을 표현해 줍니다.

그럼 필터를 입혀서 1만 빼고 체크하세요.

그런 후에 남겨진 열을 선택하고 삭제하세요.

그럼 제목만 남고 모두 없어질거예요. 깨끗하게 없어져요~

그 후에 필터를 해제 합니다. 그럼 하나만 남은걸 알 수 있어요.

3. 만약 모든 정보가 같은 것을 없애고 싶을때

위의 예시는 주문번호만을 가지고 비교했습니다. 하지만 아래 그림과 같이 정확히 내용들이 모두 일치할 때도 있어요.

이럴때는 고급 필터를 사용해보세요. 

우선 제목부분을 똑같이 만들어줍니다.

그리고 "데이터 > 정렬 및 필터 > 고급"을 클릭해주세요.

결과 = 다른 장소에 복사

목록 범위 = 특정값을 찾는 것인데요. 이번 예시는 중복된 값 제거한 후 모두 불러올 것이기 때문에 빈칸 아무곳이나 2행을 찍어주세요.

복사위치 = 위에서 제목을 똑같이 만들어준 곳입니다.

아래 동일한 레코드는 하나만에 체크 해주세요. 이것을 안하시면 중복값까지 다 불러와요.

우측에 완벽히 중복값이 제거된 상태로 나온 것을 확인 할 수 있습니다.

많은 분들이 이러한 것을 고민하고 계실텐데요. 간단하게 함수나 기능으로 중복값을 제거하여 빠른 퇴근을 노리자구요!

안녕하세요~

이번에는 엑셀 중복값 제거에 대해 말해볼까해요.

우리가 엑셀을 하다보면 여러개의 값들을 유니크하게 만들어서 그것을 가지고 어떠한 액션을 할 때가 많은데요. 

이럴때 항상 중복값을 제거해야만 합니다. 하지만 우리는 매크로도 모르고 VBA라는 건 처음 들어보는 단어라 어떻게 할 지 몰라 당황할때가 많은데요. 여러분 이제 당황하지 말고 하나씩 같이 해봅시다!

사실 중복값을 제거하는 방법은 매우 많아요. 하나씩 말씀드려볼께요.

1. 엑셀 기능 중 데이터 > 중복된 항목 제거 를 누르면 한방에 됩니다. 중복된 것을 복사해서 쓸 수 있지만 계속적으로 무언가 추가될때마다 이 짓을 해야 한다는 단점이 있습니다.

2. 함수를 사용하는 방법. 범위를 설정하고, 그 범위에서 발생하는 모든 것을 실시간으로 중복값을 제거한 상태에서 보여주는것이 장점이긴 하나 범위가 넓으면 부하가 심하다는 단점이 있습니다.

3. 위의 1번의 방법을 매크로로 녹화해서 버튼 누를때마다 중복값제거한 값을 가져올 수 있도록 합니다. 

4. 이것도 저것도 다 힘들다면 구글 스프레드시트로 가세요. UNIQUE 함수로 한방에 해결됩니다.

1. 엑셀 기능 데이터 > 중복된 항목 제거

가장쉬운방법입니다. 

장점 : 굉장히 쉽습니다.

단점 : 연동이 안됩니다.

① 중복값 제거한 곳을 복사하여 다른 곳에서 행위를 합니다.

② 복사된 곳에서 작업을 합니다.

③ 복사한 범위를 선택한 후

④ 데이터 탭을 클릭하세요.

⑤ 중복된 항목제거 아이콘을 클릭하세요.

⑥ - 팝업이 뜨는데요. 머리글(제목) 있다면 내 데이터에 머리글 표시를 클릭합니다.  없다면 클릭하지 마세요.

    - 아래 체크박스는 중복된 값이 있는 곳을 선택하는 거예요. 여러개의 행을 범위로 설정했다면 중복 값이 어디있는지를 엑셀에게 알려주세요~ 그리고 확인을 클릭합니다.

⑦ 완료되었다는 팝업이 뜨고 확인을 클릭해주세요.

⑧ 중복값이 제거 되었습니다. 이 데이터를 가지고 활용하시면 됩니다^^

2. 중복값 제거 함수 사용

가장 어려운 방법입니다. 

장점 : 실시간 연동이 됩니다.

단점 : 범위가 늘어나면 늘어날 수록 부하가 심해집니다.

- 이 방법의 단점을 최대한 줄여주기 위해서 범위를 고정형태가 아닌 유동적 형태로 잡아야 하는데요. 그것을 위해서 데이터를 표로 제작할 필요가 있습니다.

① 표를 제작할 범위를 선택해주세요.

② 삽입 탭을 클릭해주세요.

③ 표를 클릭합니다.

④ 팝업이 하나 뜨는데요. 제목이 있다면 머리글 포함을 체크해서 확인을 눌러주세요.

⑤ 완성된형태입니다. 좀 달라보이죠??

 - 이후 필요한 곳에 함수를 작성합니다. (예시는 J3에 적었어요.)

  =IFERROR(LOOKUP(2,1/(COUNTIF(OFFSET($J$2,0,0,ROW()-2,1),표1[종류])=0),표1[종류]),"")

 - J3에 입력되었고, 아래로 쭉 드레그 하시면 됩니다. 드레그 하는 한계점은 중복값을 제거해야 하는범위(예시의 33번줄(연번 31번))로 설정하시는게 좋겠죠?

 - 여기까지 하셔도 되지만 아무래도 순서가 거꾸로 되어 있어서 깨름직하다 하시는 분들은 역순으로 만들어 주시면됩니다.

① =IF(MATCH("",$J$3:$J$33,0)-(ROW()-2)<1,"",OFFSET($J$2,MATCH("",$J$3:$J$33,0)-(ROW()-2),0))

② {=IFERROR(INDEX(OFFSET($J$3,0,0,MATCH("",$J$2:$J$33,0)-2,1),MATCH(ROW(1:1),COUNTIF(OFFSET($J$3,0,0,MATCH("",$J$2:$J$33,0)-2,1),"<="&OFFSET($J$3,0,0,MATCH("",$J$2:$J$33,0)-2,1)),0)),"")}

- 위 함수 쓰시고 ctrl+Shift+Enter 누르시면 양 끝에 {} 붙어요~

조금은 어려웠지만 여러분들이라면 잘 따라오셨을것이라 믿습니다. 못하시겠다면 복붙 ok?

3. 중복값 제거 매크로 만들기

중간 난이도의 방법입니다. 

장점 : 만들기가 쉽습니다. 엑셀 부하를 안줍니다.

단점 : 실시간 연동이 안됩니다. 할때마다 클릭해줘야 해요.

- 우리 같은 VBA 하나도 모르는 사람한테는 녹화가 따봉입니다!

- 일단 먼저 개발도구 옵션을 활성화 할 것입니다.  파일 > 옵션 들어가주세요! 

-이렇게 하시면 아래처럼 개발 도구 메뉴가 활성화 되었을것입니다.

-그럼 매크로 기록을 클릭해주세요.

① 매크로 기록 아이콘을 클릭해주세요.

② 팝업에서 이름을 정하고, 설명을 쓴 후 확인을 클릭해주세요. 바로가기 키를 만드실 분들은 만들어주세요. 이번 예시에서는 버튼을 활용하여 제작될 것입니다.

- 이후 1번에서 했던것을 똑같이 해주세요.

- 그리고 매크로 기록 중지

① 삽입 아이콘을 클릭하시고,

② 단추를 클릭해주세요.

③ 단추를 넣을 곳에 클릭하시고

④ 팝업이 뜨면 아까 만들어 놨던 매크로를 클릭하여 확인을 눌러주세요.

⑤ 그러면 저렇게 단추가 만들어집니다. 이름을 바꿔주세요.

⑥ 이름까지바꾸면 완벽하게 만들어 지고 클릭할 때마다 같은 작업을 반복할 것입니다.

- 여기까지가 엑셀로 하는 작업이었습니다!

4. 구글스프레드시트 유니크함수로 중복값 제거하기

너무너무 쉬운방법입니다. 

장점 : 만들기가 쉽습니다. 부하를 안줍니다. 실시간 연동됩니다. 알아서 범위도 설정해줍니다.

단점 : 엑셀이 아니고 엑셀 내려받기 해도 UNIQUE 함수가 엑셀에 없어서 연동안됩니다.

① 첫번째 셀에만 함수를 쓰셔야 해요. 아래칸은 모두 비워두셔야 정상으로 나옵니다.

  - 함수는 =UNIQUE(C3:C1000) 입니다.

② 범위는 C3~ 끝까지 하세요. 알아서 됩니다.

너무 허무할 정도로 쉬웠죠? 가장 좋은 게 이것입니다!

여러분 구글 스프레드 시트로 갈아타죠. 화이팅입니다!

안녕하세요! 

엑셀을 하나 잘 만들어 놓으면 계속 복사해서 쓰게 되죠. 그러다 보면 복사할때 원래 있던 곳의 데이터를 참조하는 경우가 자주 발생해서 새로 만드는 시간이나 참조를 바꾸는 시간이나 비슷하게 걸릴때도 있습니다. 이것을 방지하고 복사해서도 간단하게 바꿀 수 있는 방법이 있는데요.  지금부터 그 방법에 대해 알아볼께요.

offset과 indirect함수를 이용합니다.

1. 아래 그림처럼 같은파일 내에서 다른 시트에 값을 참조하여 vlookup을 했다면 아래와 같은 화면과 함수를 볼 수 있습니다.

2. 하지만 다른 파일을 참조할경우 파일 연결이 되어버리는데요. 아래 그림처럼 파일을 참조하게 됩니다.

3. 이런경우를 대비하여 우리는 offset함수와 indirect 함수를 사용하여 다른 파일 참조를 방지할 수 있습니다.

 - 우선 OFFSET함수에 대해 알아볼 필요가 있는데요. 오프셋함수는 

    OFFSET(시작셀주소,아래로 이동, 오른쪽 이동, 아래로 범위, 오른쪽으로 범위)입니다. 

    그림으로 표현하자면 아래와 같습니다.

 - 이것을 바로 대입해봅시다. 여기서 참조시트명을 적어줌으로서 범용적으로 사용이 가능합니다.

① 이것은 어떤 시트를 참조할 것인가를 나타내는 것이며, INDIRECT로 해당 셀을 바로 참조할 수 있습니다. 풀이하자면 

    INDIRECT(INDIRECT("'"&$C$1&"'!B1") 함수는 INDIRECT('Sheet1'!B1)과 같은 이야기입니다.

    따라서 범위를 지정하는 함수인 OFFSET의 시작점은 'Sheet1'!B1 이라는 것으로 풀이됩니다.

② 아래로 0칸 이동이므로 제자리입니다.

③ 오른쪽 0칸 이동이므로 제자리입니다.

④ 현재 셀 포함 아래 1000줄이 범위입니다.

⑤ 현재 셀 포함 오른쪽 2줄이 범위입니다.  

- 그림으로 보면 아래와 같이 범위가 설정되었습니다.

4. 바로 다른 파일에 복사하여 적용해보겠습니다.

 ① 그대로 복사하였기 때문에 처음에는 Sheet1로 되어 있고, 

 ② Sheet1은 해당시트기 때문에 당연히 오류가 나는것입니다.

 ③ 과일가격시트를 생성하고, 데이터를 넣은 후 참조시트명을 똑같이 참조해야할 시트로 바꿔줍니다.

 ④ 즉시 답이 나오게 됩니다.

이렇게 작업을 한다면 한개의 시트로 여러개의 데이터시트를 참조해서 볼 수도 있답니다. 

여기까지 여러분도 성공하시길 바랄께요! 화이팅!

안녕하세요. 

전 글에서는 vlookup을 할 때 복수조건이라면 하나의 조건으로 만들 후에 vlookup을 사용하기를 했었죠.

그것은 조금 어렵게 느껴지는 index와 match를 피하고 싶은 마음을 갖고 계신분들에게는 최고라고 할 수 있지 않을까 생각합니다. 

이번에는 딱 한번만 이해하면 너무나도 쉬워지는!

복수조건 때문에 vlookup이 안될 때 사용할 수 있는!

vlookup을 해야 하는데 찾을 값이 왼쪽이 아닌 오른쪽에 있을때!

그럴때는  index와 match로 한방에 해결할 수 있습니다.

이게 뭐야? 라고 생각하시는 분들을 위해 잠시 설명드리자면...

index는 범위를 지정하고 지정된 범위에서 가로 몇번째, 세로 몇번째에 무엇이 있니? 라고 물어볼 수 있는 함수입니다.

match는 아래로 또는 오른쪽으로 찾고 싶은 값이 몇번째 있니? 라고 물어볼 수 있는 함수입니다.

참 쉽죠윙?

복수 조건, vlookup으로 안될때 index와 match 함수로 사용.

1. 아래 그림은 vlookup으로 불가능합니다. 그 이유는 두가지입니다.

  - ① 찾아야할 것이 ② 범위보다 왼쪽에 있다.

  - ② 조건이 2개 있다.

① 찾아야할 것이 왼쪽에 있어서 vlookup이 안된다면!?

1. index와 match의 조합으로 해결합니다.

 - index함수는 =index(범위, 세로 몇 번째, 가로 몇번째) 라고 사용합니다. 따라서 세로 몇 번째인지를 숫자로 표현하여 불러올 위치를 선정하고 가져오는 것입니다. 반환하는 값은 셀에 있는 데이터입니다.

 - match함수는 =match(찾을값, 범위, 정확한일치 또는 유사일치)입니다. 반환하는 값은 위치가 몇번째 있는지 숫자를 반환합니다.

 - 즉! 논리로 표현하자면 ①에서 찾는데, 찾을 곳의 세로는 match로 찾은 숫자만큼 내려가고, 오른쪽은 ④1칸(제자리)에 있는 값을 가져와라! 라고 하는것입니다.

 - 세로 match로 찾은 숫자만큼의 논리는 ②I3(바지)를 ③D3~D9까지의 범위의 몇번째인지를 찾는데 정확히 I3(바지)과 똑같은 것을 찾아서 몇번째인지를 알려달라는 것입니다.

① 가져올 값이 있는 범위

② 찾을값

③ 찾을값이 있는 범위

④ 정확한일치(0) 또는 보다작음(1) 또는 보다큼(-1)

⑤ 찾는 곳의 가로(모르시면 그냥 한줄 안에서 찾는거니까 1 쓰세요~)

2. 조금더 풀어서 그림으로 보여드리자면!

 - match만 딱 보면, ①에 입력되어 있는 함수는 =MATCH(I3,D3:D9,0) 입니다. 반환되는 값은 1입니다.

②를 ③에서 찾는데 몇번째에 그 값이 있는지를 말하는 것입니다. ③의 첫번째에 있으니 답은 1입니다.

- index는 =index(범위, 세로 몇 번째, 가로 몇번째) 이렇게 쓰기 때문에 ..

 - ①의 범위에서 match로 찾은 1을아래로 내려가고 index함수의 마지막에 있는 숫자인 1만큼 오른쪽에 있는 값을 추출하라는 것입니다.

 - index 함수는 시작지점을 1로 계산하기 때문에 아래한칸, 오른쪽 한칸은 제자리겠네요. 그럼 반환되는 값은 10,000입니다. 

 - 따라서 찾아야하는 범위 오른쪽에 조건이 있을때에도 찾아야하는 값을 찾을 수 있겠습니다!

② 조건이 복수로 있어서 vlookup이 안된다면!?

이럴때는 index, match, index를 써줘야 합니다. 결론부터 말씀드리면 함수식은 아래와 같습니다.

=INDEX($C$3:$C$9,MATCH(I3&J3,INDEX($D$3:$D$9&$E$3:$E$9,),0),1)

① 값이 있는 범위

② 찾아야할 조건을 &로 묶기

③ 찾아야할 범위를 ②에서 묶은 순서대로 &로 묶기

④ 찾은값이 찾을범위에 정확하게 일치

⑤ 값이 있는 범위의 1칸

※ 주의하실 점은 범위는 반드시 같은 행으로 써야 합니다. C열의 3부터 C열의 9까지를 범위로 잡았다면 무엇이든지 3~9까지로 범위를 잡아야 한다는것입니다~

- 논리를 보면

  C3~C9까지에서 찾아야 하는데 C3에서 시작해서 아래로 match와 index(그림 [복수 조건의 index, match, index]의 함수의 빨간줄과 파란줄) 만큼 내려가고, 오른쪽으로는 한칸에 있는 값을 찾아라 입니다.

 - match와 index 조합의 논리는 찾을 값은 I3&J3(바지분홍바지)이며, 범위는 D3~D9&E3~D9(종류&세분류)의 몇번째에 있는지 숫자로 보여줘 입니다.

이렇게 하면 복수 조건의 값을 찾을 수 있습니다.

여기까지 잘 따라오셨으면 좋겠네요!

여러분의 성공을 바라며! 저는 이만 뿅~

안녕하세요!

지난 시간에 이어 복수조건으로 vlookup을 사용하는 방법을 알아볼 차례입니다.

복수조건으로 특정값을 가져올때는 두가지의 방법이 있습니다. 

1. 데이터를 가공하여 조건을 하나로 만든다.

2. vlookup 대신 index와 match 조합으로 함수를 만든다.

이 두가지 모두 사용해보았을때 각각의 장단점이 있습니다. 

1. 데이터를 가공하여 조건을 하나로 만들었을때는 데이터자체를 가공해야 한다는 단점이 있어요. 따라서 어떠한 데이터를 어떠한 프로그램에서 다운받아 붙여넣기 하여 사용하고자 할때는 한번 데이터를 가공해야 한다는 단점이 있습니다. 

2. index와 match를 사용했을때는 데이터의 가공없이 함수로 사용이 가능하지만 데이터 량이 많아질 경우 엑셀 자체에 부하를 초래하여 엑셀 파일이 느려질 수 있다는 단점이 있습니다. 

따라서 1. 데이터를 가공하여 속도를 올릴것인지, 2. 데이터 량이 많지않아 부하가 없으니 index와 match를 사용할 것인지를 각자에 맞게 사용하시면 되겠습니다. 

일단은 첫번째 방법은 데이터를 가공하여 조건을 하나로 만드는 방법을 설명드릴께요.

데이터를 가공하여 조건을 하나로 만든 후 vlookup을 복수 조건처럼 사용하는 방법.

1. 아래 그림처럼 종류와 세분류 두가지의 조건을 가지고 있습니다.

  - 따라서 한가지 조건밖에 인식하지 못하는 vlookup을 쓸 수 없기에 & 함수를 사용하여 조건을 하나로 만들어 줍니다.

2. 아래 그림처럼 단가표와 가져와야 할 표에 모두 병합열을 제작하고 &함수로 여러개의조건을 하나로 만들어 줍니다.

 ① 단가표에 함수를 사용하여 병합열을 만들어줍니다. 반드시 단가의 왼쪽에 있어야 합니다.

 ※ vlookup 함수는 왼쪽에서 오른쪽방향으로 찾기 때문에 찾아야할 것의 왼쪽에 병합을 만들어 주세요.

 ② 찾아야할 표에도 조건을 함수를 이용하여 병합해주세요. 

 ③ 함수는 &입니다. 예시에 보이는 것은 J3과 K3을 병합하는 형태인데요. 중간에 "/"를 넣어줘서 구분이 될 수 있도록 해습니다. 함수는 =$J3&"/"&$K3 입니다.  이번에도 함수 내의 셀 주소에 알파벳 앞에만 $를 넣어줘서 가로를 고정했습니다.  

 ※ 주의 하실 점은 vlookup은 ~ 특수문자를 찾지 못한다는 것입니다. 

 ※ 모두 정상적으로 vlookup을 사용했는데 값을 찾지 못할때는 ~ 특수문자가 있어서입니다. 무엇을 하시든 ~ 특수문자의 사용은 자제해주세요. 만약 ~ 표시가 있다면 SUBSTITUTE 함수를 써서 ~을 - 나 다른 특수문자로 변경해서 사용하시면 vlookup에서 찾을 수 있습니다.

3. 완성된 형태입니다.

 - 그럼 아래처럼 병합을 조건으로 단가를 찾을 수 있겠네요!

4. vlookup을 다시 해보면 복수의 조건을 일치 시키는 형태로 단가를 불러올 수 있습니다.

 - 함수식은 =VLOOKUP($M3,$E$3:$F$9,2,FALSE) 입니다. 

 - 이번에도 조건에가로를 고정하고 범위를 고정한 형태로 수식을 작성했습니다.

5. 단가가 정상적으로 나온것을 확인할 수 있습니다.

 - 완료되었다면 볼 필요가 없는 병합 열은 숨기기하여 가려주세요. 그럼 완벽한 형태로 볼 수 있습니다.

이렇게 & 함수로 병합하여 복수조건의 vlookup을 해보았구요. 다음 글에서는 index와 match를 사용한 복수 조건의 값을 찾은 것을 알아보도록 하겠습니다!

여러분도 언제나 화이팅입니다!

안녕하세요!

요 근래에 동생에게 엑셀을 가르쳐 보았는데요. 그 동안 가장 쉽다고 생각했던 vlookup에 대해 잘 이해하지 못하는 것을 보고 쉽게 이해할 수 있도록 하려면 어떻게 해야 하나 싶어서 고민고민 하다가 이렇게 글로도 남겨봅니다. 

사실 엑셀에서 vlookup과 hlookup만큼 쉬운 함수는 많지 않아요. 이해만 하신다면 사용하는 것도 무궁무진하게 많은곳에서 사용할 수 있답니다. 

자! 본론으로 들어가기 전에 vlookup과 hlookup을 설명 드려볼께요.

※ vlookup, hlookup은 단일 조건의 범위에서 값을 찾아옵니다. 

※ 찾아야할 값은 범위에 가장 앞에 있어야 합니다. 

[1. vlookup 사용하기]

1. 아래 그림처럼 ①단가를 넣어야 하는데요. 단가표는 있으므로 그 단가표에서 매칭시켜서 가지고 와야 합니다. 

    - 조건은 종류(바지 등)와 세분류(분홍바지 등)가 되겠네요. 

    - 그럼 조건이 두가지이니까 vlookup으로 될까요? 물론 안됩니다. 되는 방법을 찾아야 해요. 일단은 보통적 상황의 vloopup을 말씀드릴께요.

2. 그럼 단가표를 살펴보겠습니다. 아래 그림은 단가 표이며, 종류, 세분류를 이용하여 단가를 불러올 수 있음을 확인 할 수 있습니다.

②의 단가를 가져오기 위해서는 그 앞의 종류와 세분류를 보고 가져와야 합니다. 지금은 일단 종류를 보고 가져와 보겠습니다.

3. 아래 그림처럼 두 표의 ③종류를 비교해서 단가표의 단가를 자져올 것입니다.

4. 함수는 아래와 같습니다.

  =VLOOKUP(I3,C3:E9,3,FALSE)

  - 여기서 I3에 있는 것은 찾을 값입니다.

  - C3:E9는 범위입니다.

  - 3은 범위안에서 3번째에 있는 값 이라는 뜻입니다.

  - FALSE는 정확하게 일치할때만 그 값을 찾으라는 뜻이며, 유사 검색시에는 TRUE가 될것입니다. (사실 TRUE를 찾는 일이 별로 없어요.)

 - 이 함수를 논리로 풀어보자면 "바지를 C3부터 C9까지에서 찾고 그 옆에 3번째에 있는 값을 가져와라"가 되겠네요.

※ 주의하실 점은 우측으로 C~E까지는 3칸이므로 3번째까지 찾을 수 있습니다. 4번째를 찾으려면 우측으로 범위를 최소 4개 이상 설정하셔야 합니다.(예. C~F까지 범위를 설정)

5. 이후 드레그나 복사를 통해 아래까지 쭉 채워야 하기 때문에 $표시로 고정해줍니다.

 - 함수 안에 있는 셀주소(I3 또는 C3:E9등)를 클릭하고, F4 키를 한번 누르면 가로와 세로 모두 고정.

 - F4키를 2번 누르면 세로만 고정

 - F4키를 3번 누르면 가로만 고정

 - F4키를 4번 누르면 고정 해제 (이후 반복)

 ※ =VLOOKUP(I3,C3:E9,3,FALSE) 이렇게 써진 함수를 =VLOOKUP($I3,$C$3:$E$9,3,FALSE) 이렇게 만들어 줍니다.

 그림으로 잠깐 표현해보자면 

 - 단가(수식 부분)의 ①부분을 잡고 드레그 할때 ② 처럼 가로 방향으로 할지 ③ 처럼 세로방향으로 할지를 정하게 되는데요. 이것에 따라 $ 위치를 잡아줘야 합니다.

 - 위 예시는 아래 방향 즉 ③처럼 해야 하는데요. 이때 셀 주소의 알파벳부분은 움직이지 않아야 하고, 숫자부분은 움직여야만 2번째의 기준인 바지~10번째의 기준인 점퍼를 참조할 수 있습니다. 따라서 기준이 되는 I3은 가로는 고정하고 세로는 고정하지 않는 형태로 제작되어야 합니다. 이유는 아래로 내렸을때 참조되어야 할 값과 범위가 상대적으로 움직이기 때문이예요.

 - 결과는 $I3입니다. I 앞에만 $가 붙은 형태가 됩니다.

 - 범위 또한 ③처럼 내렸을 경우 상대적으로 범위도 내려가게 되는데요. 이것은 가로와 세로 모두 고정해줘야만 하죠. 따라서 숫자와 알파벳 모두 $표시를 걸어줘야 합니다.

 - 결과는 $C$3:$E$9 처럼 모두 $가 붙은 형태가 됩니다.

그래서 완성된 함수는 =VLOOKUP($I3,$C$3:$E$9,3,FALSE) 이렇게 됩니다.

 - 그 후에 ①을 마우스로 잡고 아래로 드레그 한다면 아래와 같은 결과를 얻게 됩니다.

여기까지 vlookup에 대해 알아봤습니다!

다음 포스팅에서 복수의 조건을 vlookup으로 가져오는 방법을 알아보겠습니다.

그럼 여러분도 화이팅 하세요!

안녕하세요!

그래프 난애하고 어렵게만 생각하셨다면 잘오셨습니다. 

참 쉽게 엑셀 그래프를 만드는 방법을 알려드릴께요.

1. 데이터를 준비합니다.

 - 데이터를 준비하실때에는 표형태가 가장 좋아요. 

2. 그래프를 만들 구간을 선택해주세요.

 - 그냥 만들고 싶은 곳을 선택해보세요.

 - 아래는 날짜별 각 열의 금액을 보고자 할때를 예시로 든것입니다.

 - 필요한 열이 있다면 그 부분만 선택하세요.

 - 이 때 주의 하실점은 가로축, 세로축을 생각해야 해요. 예를 들어 가로와 세로는 비용과 날짜로 하겠다는 식으로 예상하고 선택해야 한다는 거죠. 

3. 그 후 추천차트를 보고 어떠한 차트를 이용할 것인지를 정할 거예요.

 - 삽입 > 추천차트 를 클릭해서 팝업을 띄워서 필요한 차트를 보고 확인을 눌러주세요.

4. 아래처럼 여러종류의 차트를 만들어 낼 수가 있어요.

※ 여기까지 하셨다면 그래프는 만들어졌습니다. 이제 차트 디자인, 데이터 수정만이 남았습니다.

5. 차트 디자인 수정

 ① 먼저 생성한 차트를 클릭해서 ②디자인/서식 메뉴를 활성화 해야 합니다.(클릭 안하시면 안나와요.)

 ② 디자인 탭을 클릭하시면 ③, ④, ⑤, ⑥을 볼 수 있습니다.

 ③ 차트에 요소를 추가하는데요. 축이나 데이터를 건들수가 있어요. 그것은 ⑦으로도 가능하니 참고해주세요.

 ④ 색은 그래프의 선, 막대 등의 선들을 붉은계열, 푸른 계열 등으로 선택할 수 있는 메뉴입니다.

 ⑤ 가장 많이 사용하실 부분일 수도 있습니다. 이 곳은  어느정도 샘플로 구현되어 있어서 원클릭으로 디자인을 바꾸는 거예요.

 ⑥ 선차트, 막대차트, 도넛차트 등 여러가지 모양으로 차트 종류를 변경할 수 있어요.

 ※ 추가적으로 그래프를 선택하거나 그래프 막대 또는 선을 클릭하시면 아래처럼 보라색 박스와 파란색 박스, 빨간색 박스를 볼 수 있는데요. 마우스 드레그엔드롭으로 간단히 차트에 표현될 데이터를 바꿀 수 있습니다.

간단하게 엑셀 그래프를 만드는 방법을 알아봤구요. 

이 밖에도 데이터를 피벗으로 만들어 피벗 차트로 만드는 방법도 있으니 그것은 다음 글에서 만나보겠습니다.

(1) 날짜와 시간 함수

(2) 수학/삼각함수

(3) 통계함수

(4) 찾기/참조영역 함수

(5) 데이터베이스 함수

(6) 문자열 함수

(7)논리 함수

․입력된 논리식들이 참이면 TRUE, 거짓이면 FALSE를 출력하는 함수이다.

1) AND 함수

․입력되는 논리식들이 모두 참이어야 TRUE 출력, 하나라도 거짓이 있으면 FALSE 출력

․=AND(논리식1, 논리식2....)

2) OR 함수

․입력되는 논리식들 중 하나라도 참이 있으면 TRUE 출력

․=OR(논리식1, 논리식2.....)

3) IF 함수

․조건에 해당하여 참과 거짓을 판별하는 조건문 함수

․=IF(조건, 참 문장, 거짓 문장) => 조건이 참이면 참 문장 실행, 거짓이면 거 짓 문장 실행

․=IF(평균>=70, “합격”, “불합격”) => 평균이 70점 이상이면 합격, 그렇지 않 으면 불합격을 출력한다.

(8) 재무 함수

1) 재무함수의 공통 인수

․rate => 이자 지급 기간당 이율을 의미

․nper => 납입 총 횟수를 의미

․pmt => 각 기간의 납입액으로 전체 기간 동안 일정하며 일반적으로 기타 비용 이나 세금은 포함되지 않고 원금과 이자만 포함된다.

․pv => 현재 가치를 의미

․type => 지급 기일이 기말인지 기초인지를 구분

․fv => 미래 가치 또는 최종 상환 후의 현금 잔고를 의미

2) FV 함수

․일정 금액을 정기적으로 불입하고 일정한 이율이 적용되며 매월 복리로 이자가 계산되는 것으로 가정한 투자의 미래 가치를 계산해 주는 함수

․=FV(rate, nper, pmt, pv, type)

3) PV 함수

․매월이나 매년 일정한 금액을 일정 기간동안 지불해 주는 연금이나 보험의 지급 총액에 대한 현재 가치를 구해주는 함수

․=PV(rate, nper, pmt, fv, type)

4) NPV 함수

․특정한 금액을 투자하고 매월 일정한 수입이 보장될 때 해당 투자의 현재 가치 를 구해주는 함수

․=NPV(rate, value1, value2... value29)

(9) 정보 함수

1) ISBLANK 함수

․인수로 지정한 셀이 비어 있으면 TRUE를 구해주고, 비어 있지 않으면 FALSE를 구해준다.

․=ISBLANK(셀 주소)

2) ISERROR 함수

․인수로 입력한 셀에 오류가 발생하였으면 TRUE를 구해주고, 오류가 발생하지 않 으면 FALSE를 구해준다.

․=ISERROR(셀 주소)