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라는것만

주의하시면 됩니다.

엑셀 랜덤 함수 사용하기.(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 시트)와 대상시트(ExportData) 두개를 만듭니다.

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

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

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

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

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

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

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

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

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

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

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

도움되셨길 바래요.

엑셀 xml 만들기 (MS Office 2010기준)

엑셀을 사용하는데 xml로 변환할 일이 생겨서 이곳저곳 찾아봤네요.

찾아본 결과 중 가장 쉬운걸로 포스팅 하려고 합니다.

사실 맞는 방법인지는 모르지만 작동은 되니까.

Step 1. xml로 만들 엑셀 시트 구성하기.

테스트는 아주 쉽게 다음처럼 하겠습니다.

참고 : 여기에서 id와 value라는 이름 다른걸로 하셔도 됩니다.

Step 2. xml 스키마 만들기

스키마는 엑셀에서 만든거와 구조가 같게끔 하고, 최소 두개 이상 써야 합니다.

아래 보시면 Field를 두개 넣었습니다.

두개 이상이 되어야 반복 되는걸 인식한다고 하더라구요.

그리고 엑셀에서 만든 필드 이름과 같아야 자동 매칭 기능도 된다고 합니다.

이렇게 했다면 test.xml 로 저장하겠습니다.

Step 3. 엑셀에 xml스키마 맵핑하기

다시 엑셀로 돌아갑니다.

첫번째로 해야할 게 xml 내보내기 할 수 있는 메뉴를 보이게 하는거에요.

파일->옵션을 눌러 줍니다.

옵션 창이 뜨면 리본 사용자 지정을 선택하고 개발 도구를 체크 해준다음에 확인을 눌러줍니다.

개발 도구 메뉴가 생겼습니다. 이제 실제 맵핑 할 차례.

개발 도구를 선택하고, 원본을 눌러 줍니다.

그리고 XML맵을 눌러줍니다.

추가를 누르면 탐색기가 열리는데 아까 만든 "test.xml" 을 열어줍니다.

그러면 다음처럼 등록이 되는게 보여요. 그리고 확인을 눌러줄께요.

그러면 우측에 MyTest라는게 생겨났네요.

맵핑이 거의 끝나가네요.

이제 좌측 Sheet에서 모두 선택해주세요.

그리고 우측 MyTest에서 우측마우스를 눌러 요소 매핑을 눌러줍니다.

그럼 맵핑할 위치를 선택하라고 나오는데 확인을 눌러줍니다.

그러면 다음처럼 변합니다. 이렇게 변하면 완료가 된거에요.

Step 4. xml로 내보내기(Export)

개발도구 메뉴에서 내보내기를 눌러 저장하면 완료됩니다.

xml로 저장한 파일을 열어보니 다음처럼 잘 나오네요.

엑셀을 xml로 만들기 어렵지 않네요. 

도움 되셨길 바래요~