Crocodile Tears

[엑셀] 문자열 비교하기

Office 2016 기준

다음과 같은 조건이 있습니다.

"만약 이름이 홍길동 이면 TRUE, 그렇지 않으면 FALSE"

IF문처럼 보이지만, 문자열 비교는 IF가 아닌 EXACT 함수를 사용해야 합니다.

레퍼런스 : 링크

설명

두 텍스트 문자열을 비교하여 정확히 같으면 TRUE를 반환하고 그렇지 않으면 FALSE를 반환합니다. 

EXACT는 대/소문자를 구분하지만 서식 차이는 무시합니다.

포멧 => EXACT( 문자열1, 문자열2)

다음처럼 데이터가 있을 때 홍길동을 찾기 위해서는 다음과 같은 수식을 써주면 됩니다.

=EXACT(A1, "홍길동")

그러면 TRUE 또는 FALSE를 반환합니다.

여기에서는 A2가 홍길동이므로 TRUE를 반환합니다.

좀 더 나아가 IF와 조합하여 사용할 수 있습니다.

IF 포스팅 클릭

만약 조건이 TRUE이면 1을, FALSE는 0으로 설정하도록 하겠습니다.

= IF(EXACT(A2, "홍길동", 1, 0 )

그리고 설정하고자 하는 셀들을 선택해 쭉 내려주면

모두 적용됩니다.

엑셀 문자열 비교할 때는 IF가 아닌 EXACT라는것만

주의하시면 됩니다.

[엑셀] 텍스트 나누기

Office 2013 기준

다음과 같은 텍스트가 메모장에 있습니다.

안녕하세요|반갑습니다|즐겁습니다|행복합니다

1. 엑셀을 열고 A1에 붙여줍니다.

2. 메뉴에서 데이터 -> 텍스트 나누기를 눌러줍니다.

3. 구분 기호로 분리됨을 선택하고 다음을 눌러줍니다.

4. 기타를 체크하고, 구분자를 설정합니다. 

여기에서는 | 입니다.

그리고 마침을 눌러줍니다.

5. 완료. 텍스트가 분리되어 각 셀에 들어갔습니다.

엑셀 랜덤 함수 사용하기.(RAND와 RANDBETWEEN)

(Office 2013 기준)

엑셀을 사용하다 보면 임의의 난수를 뽑고 싶을때가 있습니다.

이럴 때 RAND 또는 RANDBETWEEN을 사용하시면 됩니다.

우선 포멧부터 보시겠습니다.

RAND() : 0 보다 크거나 같고 1보다 작은, 규등하게 분포된 난수를 구합니다.

(재계산 시에는 바뀝니다.) => 소수만 가능.(EX> 0.11231)

RANDBETWEEN(시작 값, 마지막 값) : 지정한 두 수 사이의 난수를 반환합니다. => 정수만 가능(EX>1, 5, 10 등)

아래 있는 항목으로 테스트를 하려고 합니다.

우측 설명처럼 체력은 0~1000까지 랜덤하게 나오게 할 것이고

치명확률은 0.0~1.0까지 랜덤하게 나오게 할 것입니다.

우선 체력 첫번째 셀(C2)를 선택하고 

= RAND라고 치다 보면 자동완성 기능으로 아래 그림처럼 나옵니다.

그러면 Tab키 또는 마우스 더블클릭 하시면 선택 됩니다.

(아니면 그냥 다 타이핑 하셔도 됩니다)

RANDBETWEEN을 먼저 하겠습니다.

RANDBETWEEN은 정수만 가능합니다. 

즉, 0.5572 같은 소수는 안됩니다.

체력은 0부터 1000까지 나오게 할 거이므로, 

= RANDBETWEEN(0, 1000) 으로 세팅합니다.

RAND함수를 사용해서 곱셈을 이요해서 하셔도 됩니다.

값이 나왓다면 아래로 쭉 끌어서 

모두적용 시킵니다.

이제 치명확률을 할 차례입니다.

0.0부터 1.0까지 나타나게 할 것이므로, 

= RAND() 를 사용합니다.

치명확률도 값이 나왔습니다.

아래로 쭉 끌어서 모두 적용 시켜줍니다.

RAND와 RANDBETWEEN은 사용법이 아주 간단합니다.

그런데 응용하려면 조금 생각을 해야 하는 함수이기도 하죠.

예를 들어 0.0 ~ 0.5까지만 랜덤으로 출력. 아니면 1~1000까지 수 중 랜덤으로 홀수만 출력 등.

다른 함수(또는 연산자입력해서)와 같이 사용해서 응용해야 겠지요.

여기까지 엑셀 랜덤함수 RAND와 RANDBETWEEN 이었습니다.

엑셀 중복값 제거하기

(Office 2013 기준)

캐릭터가 사용할 스킬리스트를 만들었는데

중복된 것들이 너무 많았습니다.

정리가 필요해서 엑셀을 이용해 중복값을 제거하였습니다.

엑셀에서 마우스 몇 번 클릭하면 중복값 제거가 바로 가능합니다.

데이터가 총.. 100개? 정도 나왔습니다.

< 엑셀에서 중복값을 제거할 데이터 >

중복값을 제거하도록 하겠습니다.

먼저 (예제의 경우) B 라인(대상들)을 모두 선택합니다.

그리고 메뉴에서 데이터를 선택하시고,

중복된 항목 제거를 누릅니다.

그러면 다음과 같은 창이 뜹니다.

( 만약 하나의 열만 있다면 안나올 것입니다.)

(지금 저는 세개의 열이 있으므로 다음과 같은 창이 뜨는거구요.)

그러면 현재 열만 하면 되므로, 

"현재 선택 영역으로 정렬"을 선택하고 버튼 클릭.

그리고 확인을 눌러줍니다.

결과가 나왔네요.

저의 경우 총 102개에서 중복된 것 제거되고

30개가 나왔습니다.

엑셀은 정말 편한 것 같습니다.

도움 되셨길 바랍니다.

엑셀 VBA를 이용해 CSV를 UTF-8로 Export하기

(Office 2013 기준)

기본적으로 엑셀 시트를 CSV로 저장하게 되면 인코딩이 ANSI로 되어있습니다.

< CSV로 저장했을 때의 인코딩 >

인코딩을 바꾸려면 모메장으로 열고 인코딩을 직접 바꿔주어야 하는데

데이터가 많을 경우 시간이 걸리는 작업입니다.

VBA를 이용하여 매크로를 만들면 이런 번거러운 작업을 없앨 수 있습니다.

테스트를 위해  이전 포스팅 한 데이터(VLOOKUP 사용하기)를 이용하여 하도록 하겠습니다.

우선 개발도구 탭에 있는 Visual Basic을 클릭합니다.

(개발도구 탭이 없으신 분은 여기를 눌러Step 3을 보시기 바랍니다)

그럼 다음과 같이 개발 툴이 나타납니다.

매크로를 만들기 위해서 

상단 메뉴 삽입 -> 모듈 을 클릭합니다.

그리고 다음 사이트를 들어가서 코드를 모두 복사합니다.

그리고 붙여넣기를 해줍니다.

링크 클릭

이제 다시 엑셀 파일로 돌아옵니다.

버튼을 하나 만들어서 매크로가 실행되게끔 해야합니다.

개발도구에서 삽입을 누르고, 단추(버튼)을 클릭합니다.

그리고 아무 셀쪽에 가서 드래그를 하게 되면

이런 창이 나타나게 됩니다.

WriteCSV가 보이는데, 이게 아깝 복사/붙여넣기 한 매크로 입니다.

선택하시고, 매크로 위치는.. 그냥 현재 통합 문서로 하도록 하겠습니다.

그리고 확인.

창이 닫아지면 아까 드래그 해서 만든 버튼이 저렇게 보여집니다.

단추를 딱 클릭을 하면 다른 이름으로 저장 창이 나타나는데,

파일 형식이 바로 CSV로 되어있습니다. 

아무 이름이나 하고 저장을 할께요.

저장이 완료되면 다음처러 "다 되었다"라고 창이 하나 뜹니다.

이제 확인만 하면 됩니다.

저장된 파일을 메모장으로 열고, 

다른 이름으로 저장을 눌러보면 인코딩을 볼수 있습니다.

UTF-8로 되어있습니다.  잘 되네요.

관심 있는 분들은 VBA 검색하셔서 공부해보세요.

자동화 해두면 나중에 아주 많이 편리하니 도움되실 꺼에요.

** 추가 **

해당 링크로 가서 받은 코드에 약간의(?) 문제가 있습니다.

최대 10라인만 되도록 되어있더라구요.

코드 조금 수정하겠습니다.

아래 빨간색으로 된 부분만 수정/추가해 주시면 되겠습니다.

(다른 방법도 있겠지만, 간단히 처리했습니다.)

엑셀 VLOOKUP 사용하기

LOOKUP 함수는 표나 범위에서 행 또는 열 기준으로 항목을 찾을 경우 사용합니다.

VLOOKUP(Vertical Lookup : 세로 기준)과 HLOOKUP(Horizontal Lookup : 가로 기준)이 있습니다.

여기에서는 VLOOKUP을 사용해 보려고 합니다.

VLOOKUP의 구문(출처 : MSDN)

이전 포스팅들을 이용해서 사용을 할 것인데, 우선은 한 시트 안에서 처리하도록 하겠습니다.

왼쪽 상단이 기본 데이터이고, 데이터 참조를 하여 똑같이 값을 구성하였습니다.

그리고 [ 장비 ] 값들에 우측 [ 장비데이터 참조 ]의 인덱스를 표시하려고 합니다.

현재는 무기, 방어구, 장신구 로 표시되어 있으므로 

VLOOKUP을 이용하여 인덱스를 표시 하도록 하겠습니다.

첫번째 들어갈 내용은 조회하려는 값입니다. 

여기에서는 "무기" 가 되겠습니다. 

"무기"를 선택하면 다음처럼 상대주소(C2)가 설정됩니다.

(상대주소, 절대주소, 혼합주소는 다음에 포스팅 하겠습니다)

그리고, 두번째에 들어갈 내용은 반환 값을 검색하는 셀의 범위입니다.

여기에서는 주황색으로 되어있는 부분은 영역이 되겠습니다.

그래서 저렇게 영역을 드래그 하면 상대주소가 설정됩니다.

또는 이전 포스팅(이름정의하기)를 통하여 영역을 지정해도 됩니다.

예를 들면, VLOOKUP(C2, NAME1,..) 이런식으로도 가능합니다.

세번째에 들어갈 내용은 이전에 잡았던 영역에서 몇번째 데이터를 보여줄 것인가 입니다.

참고 : 가장 왼쪽에 있는 열에서 1부터 시작합니다.

여기에서는  "장비데이터 참조"에 이름이 1번, 인덱스가 2번, 설명이 3번입니다.

우리는 "무기"라는 이름 대신 "1"이라는 인덱스 값을 보여주고 싶으므로

2 라고 설정합니다.

(만약 1이라고 설정한다면 이름 항목이 나올것이고, 3을 설정하면 설명 항목이 나오게 됩니다.)

마지막에 들어갈 내용은 유사한것을 찾을 것인지, 완전히 일치하는 것을 찾을 것인지 입니다.

여기에서는 완전히 일치할 경우로 설정하겠습니다.

모두 세팅이 되었습니다. 자동채우기를 한 다음에 결과화면은 다음과 같습니다.

< 결과 >

현재까지 한 내용으로 ExportData쪽에 세팅을 하게되면 다음과 같은 결과를 얻게 됩니다.

(ITEM_REF는 네이밍 포스팅에서 LOOKUP에서 사용할 영역을 잡아논 것입니다.)

도움 되셨길 바랍니다.

[엑셀] 드롭다운 만들기

엑셀 이름 정의하기 데이터를 가지고 하도록 하겠습니다.

우선 시트를 하나 만들고 아래처럼 데이터를 넣도록 합니다.

저는 OriginData라는 시트를 만들고 다음처럼 하였습니다.

그리고 장비부분에서 드롭다운 데이터를 넣도록 할 예정입니다.

이제 장비 부분에서 주황색으로 네모 처진 부분을 선택하고, 

데이터 -> 데이터 유효성 검사 를 클릭합니다.

그러면 데이터 유효성 검사 창이 나타납니다.

이 창에서 드롭다운 변경/삭제 등을 하실 수 있습니다.

그리고 설정에서 제한 대상을 목록으로 바꿔줍니다.

그리고 원본 부분에서 드롭다운 메뉴가 들어갈 리스트를 위해 영역을 선택합니다.

저의 경우 Naming 시트에 이름쪽을 리스트로 사용하겠습니다.

그리고 확인을 눌러줍니다.

이제 데이터를 보시면 드롭다운이 나타나는 것을 확인하실 수 있습니다.

현재 C:2 셀만 적용되어있으므로, 아래로 쭉 끌어서 모두 적용되게 해주세요.

도움되셨길 바랍니다.

엑셀 이름 정의하기

엑셀에서 이름을 정의하여 영역들을 쉽게 관리할 수 있습니다.

또한 수식에서도 편리하게 사용할 수도 있습니다. (이 부분은 LOOKUP할 때 다시 설명하겠습니다.)

이름을 설정하기 위해 다음과 같은 데이터를 만들었습니다.

< 기본 데이터 >

데이터 영역의 이름을 정의하기 위해 방금 작성한 데이터를 선택합니다.

그리고 우측 상단에 이름을 넣고 엔터를 치시면 이름정의가 완료됩니다.

만약 정의한 이름을 변경, 삭제 등을 할 경우에는 

수식 -> 이름관리자 를 통해서 가능합니다.

이름 관리자를 누르면 좀전에 정의한 이름에 대한 정보가 나타납니다.

이곳에서 변경, 삭제 등을 수행 할 수 있습니다.

함수를 사용할 때 영역을 지정하는 것보다 이름을 지정하여 사용하는게

이해도 쉽고, 관리도 쉽습니다.

도움되셨길 바랍니다.

[엑셀] 다른 시트 값 가져오기

우선 아래 그림 처럼 원본시트(OriginData 시트)와 대상시트(ExportData) 두개를 만듭니다.

그리고 원본 시트에 값을 입력합니다.

저 같은 경우 테스트로 아래처럼 하였습니다.

원본시트에 값이 입력 되었으면, 대상 시트로 이동을 합니다.

저같은 경우 첫행은 키값으로 사용하기 때문에 

첫행의 이름은 직접 입력을 해주었습니다.

그럼 원본시트 값을 가져오기 위해 A2를 선택합니다.

그리고 =시트명!셀 을 해줍니다.

저의 경우 =OriginData!A2 가 되겠네요.

값이 제대로 들어왔다면 행/열로 쭉 끌어서 모두 채워넣습니다.

다음처럼 모든값이 적용되었습니다.

간단한건데 자주 사용을 하지 않으니 깜빡깜빡하네요. 

도움되셨길 바래요.