[엑셀] 엑셀로 작업하다보면 은근히 궁금한, 어쩌면 있을 것 같은 함수들 정리

엑셀을 쓰다보면 이런 생각이 들 때가 있습니다. 

'아~ 이런 데이터는 어떻게 만들어야 하나?' 또는 '텍스트를 이렇게 정리하면 좋겠는데..'

이럴 때마다 만드는 주먹구구 함수식을 올려봅니다. 

다른 분들도 다 할 줄 아는 거지만, 여기 올리는 건 순수하게 저의 연구로 얻어진 것입니다.. ㅎㅎ

(검색도 연구에 포함시켜주세요.. ㅠㅠ.. 인터넷의 능력자 분들에게 감사드리며.. )

계속 업데이트 될 예정입니다.~!

그럼 마음껏 퍼가세요~!

1. 문자열 중 오른쪽 한글자 삭제 : =TRIM(LEFT(A2,LEN(A2)-1))  결과 : excel → exce

2. 빈자리 0 (zero)로 채우기 : =CONCATENATE(REPT(0,4-LEN(Sheet1!C2)),Sheet1!C2)  결과 : 0001

3. 다른 표에서 변수에 해당하는 값 가져오기 : =INDEX(sheet1!$A$1:$B$10,MATCH(TRIM(Sheet2!A2),sheet1!$A$1:$A$10))

3-1. 다른 표에서 변수에 해당하는 값 가져오기, 변수와 같이 정확히 일치하는 것을 원하면 MATCH함수의 마지막 인자로 "0"을 추가 : =INDEX(sheet1!$A$1:$B$10,MATCH(TRIM(Sheet2!A2),sheet1!$A$1:$A$10,0))

4. 엑셀에서 특정 값 항목의 누계만 자동 합계되는 수식 : =SUMIF(edit!$A$2:$A$403,'최다 클릭 키워드'!A161,edit!$C$2:$C$403)

5. 행사 참석 여부를 확인하는 수식 : =IF(COUNTIF(guest!$D$3:$D$164,check!K98)=0,"","v")

6. 범위에서 공란이 아닌 셀의 갯수 : =COUNTA(J2:J201)

7. 테이블 범위 안에서 내가 원하는 값과 일치하는 내용을 가져오는 함수

 : vlookup("검색할 값","검색할 테이블 범위","테이블 왼쪽기준 인덱스","정확도 인수")

검색할 값 - 말그대로 찾고자 하는 값. 

검색할 테이블 범위 - 검색할 대상을 제일 좌측열에 두고 테이블 범위 설정, 검색할 대상열보다 왼쪽에 있는 값의 인덱스는 가져올 수 없음

테이블 왼쪽기준 인덱스 - 검색할 값이 위치한 행에서, 기준값으로부터 인덱스만큼 열 이동을 하여 값을 가져옴.

정확도 인수 - TRUE : 검색할 값과 유사한 값을 검색, FALSE : 검색할 값과 정확하게 일치하는 값을 검색. 

※ #N/A 오류 : 원하는 겂이 존재하지 않는 경우. 

sample : =VLOOKUP('시트1'!B2,'시트2'!$A$2:$I$32474,3,FALSE)

meaning : vlookup 함수를 이용하여 "시트1 B2"있는 값을 "시트2 A2부터 I32474"범위에서 "똑같은(FALSE)" 것으로 찾아, 해당 행의 "3번째 열"에 있는 값을 가져온다.