[excel] 데이터(sheet)간 join된 값 나타내기 외

출처 : kuduz.tistory.com/1086

 

데이터(sheet)간 join된 값 나타내기

vlookup 함수를 사용하면 된다.

 

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

 

lookup_valu : 조회 값 ( 참조키값 cell위치 ex)A1 )

table_array :  조회 값이 포함 된 범위(절대범위) $B$1:$D$4 단축키 F4를 누르면 절대범위로 바뀜

col_index_num :  반환 값이 포함 된 범위의 열 번호. 범위내에서 1부터 시작하는 column의 순서번호

[rang_lookup] : 근사값 (TRUE) 또는 정확히 일치 (FALSE))

 

셀의 일부만 substring하기?

    =LEFT(셀, FIND("찾을문자열", 셀)-1)

 

여러 열 합치기
    =CONCATENATE(G2,"(",H2,")")

 

IF를 사용하여 셀이 비어 있는지 검사

    =IF(ISBLANK(D2),"Blank","Not Blank")

 

여러 열 합할 때 조건 주기

=CONCATENATE(F4,IF(ISBLANK(G4),"","("),G4,IF(ISBLANK(H4),"",","),H4,IF(ISBLANK(G4),"",")"))

 

엑셀 다른 파일 참조하여 값 불러오기

같은 디렉토리에 있다면 [파일명.xls] 요것만 덧붙이면 된다. (출처 : seogilang.tistory.com/903)

단어설명 가져오기 : =VLOOKUP(B2,'[행정용어사전_수정본.xls]new sheet'!$C$3:$F$14138,4,FALSE)
단어영문명 가져오기 : =VLOOKUP(B2,'[행정용어사전_수정본.xls]new sheet'!$C$3:$F$14138,2,FALSE)

 

문자열 SUBSTRING

"AS 담당자 명"  문자열 중 "담당자"를 추출하고 싶을 때 FIND(find_text, within_text, [start_num])을 사용한다.

MID(text, start_num, num_chars)

=mid(A1,find("*",A1)+1,find("*",A1,find("*",A1)+1)-(find("*",A1)+1))

FIND(" ", A1) + 1 : 4

FIND(" ", A1, 4) : 7

MID(A1,4, 7-4) : 담당자  

 

=IF(조건, 결과(참), 결과(거짓))

 

용어영문약어명1 : 

=VLOOKUP(IF(ISERROR(FIND(" ",B2)),B2,LEFT(B2, FIND(" ",B2)-1)),단어!$B$2:$D$365,2,FALSE)

ISERROR(FIND(" ",B2)) : " "가 없다면 ERROR이므로 TRUE

 

용어영문약어명2 : 

=VLOOKUP(IF(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))>=2, MID(B2,FIND(" ",B2)+1,FIND(" ",B2,FIND(" ",B2)+1)-(FIND(" ",B2)+1)), RIGHT(B2, LEN(B2)-FIND(" ",B2))),단어!$B$2:$D$365,2,FALSE)

 

용어영문약어명3 : 

=IF(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))>=2, VLOOKUP(RIGHT(B2,LEN(B2)-FIND(" ",B2,FIND(" ",B2)+1)),단어!$B$2:$D$365,2,FALSE),"")