라벨이 문자열인 게시물 표시

LOOKUP함수. 문자중 일부를 문자열에서 찾는 방법

이미지
이런 식으로 과일에 대한 가격이 매겨져 있습니다. 이제 다른 곳에 견적서를 작성한다고 하면, 품명에 "사과" "배' 등으로 기록하면 그 품목에 대한 가격을 구하는 것은 VLOOKUP함수로 쉽게 할 수 있다.  하지만, 지금처럼 해당 품명이 들어있긴하지만 다른 문자들과 섞여 들어가 있어 VLOOKUP함수를 그대로 적용할수는 없는 상황이 있습니다.  이런때, 다른 문자들이 섞여 있긴 하지만 해당 품목을 문자속에서 찾아서 해당 품목의 가격을 알고 싶다면 어떻게 할까요? 큰형님 이 멋진 답을 주셨습니다. =LOOKUP(1,1/FIND($E$2:$E$7,A2),$F$2:$F$7) 수식   해석 함수   LOOKUP(1 1을 다음 배열에서 찾는다  1/  뒤의 배열에 있는 숫자각각으로 1을 나눈다 lookup함수의 두번째인수, 1을 찾을 배열  FIND($E$2:$E$7,A2) A2안에 사과~수박의 문자배열중 있는 글자가 있는지 찾는다  lookup함수의 두번째인수, 1을 찾을 배열  $F$2:$F$7   위에서 찾은 배열의 문자가 해당 배열에서 차지하는 위치와 같은 위치에 있는 문자를 F2:F7 배열에서 찾는다   lookup함수의 세번째 인수, 1이 위치한 곳과 같은 위치를 찾을 곳  여기서 find함수의 쓰임에 주목할 필요가 있습니다. FIND(find_text, within_text, [start_num]) 즉, find(찾을문자,찾을장소,[시작위치])로 써야하는 함수입니다. 그런데 여기서는 find(범위,문자)로 썼습니다. 보통의 사용법과는 반대입니다. FIND($E$2:$E$7,A2) 을 말로 풀이하면 "E2:E7 안에 있는 문자들 중 A2셀의 문자와 같은게 있으면 그 위치가 어딘지 각자 배열로 표시해봐"가 될것입니다 FIND($E$2:$E$7,A2)을 수식계산에서 돌려보면 

여러 셀에 여러 문자열이 섞여있는 환경에서 특정 문자열의 위치 찾기

이미지
위의 표에서, C열에 "하분류"에 단어들이 들어가 있습니다. 하분류를 넣으면 상분류, 중분류까지 나오게 하고 싶습니다 이런 식으로 정리가 되어있다면 INDEX, match함수로 간단하게 됩니다. 하분류가 A열에 가 있다면 VLOOKUP함 로 더욱 간단하게. 하지만 지금 형태를 그대로 두고서 수식을 만들면 위와 같습니다 이 수식을 말로 설명하면 다음과 같습니다 (FIND($E2,$C$2:$C$7)):  E2에 있는 단어 "호박"이란 단어가 C2:C7의 배열에서 어느 셀에 위치하는지 찾아서 ISERROR :  배열에 결과값이 숫자인지 오류인지 확인 (숫자가 결과값이라면 해당 단어가 존재한다는 의미) NOT ~~ *1 :  결과값이 오류이면 0을, 숫자이면 1을 부여 SUMPRODUCT :  위의 0과1의 배열에 행번호를 곱한다 (해당 단어"호박"이 위치한 행번호가 나온다) -1 :  결과값 위치와 실제행번호가 다르기때문에 조정 출처: 네이버지식인

1일1함수 (37) SUBSTITUTE 함수 : 특정 문자열을 다른 문자열로 바꾸기

이미지
지난 번에는 REPLACE함수를 사용해서, 특정위치에 있는 문자열 을 다른 문자열로 바꾸었습니다. 오늘 다루게 될 SUBSTITUTE함수는 특정한 문자열을 찾아, 다른 문자열로 바꾸는 함수 입니다. 구문 SUBSTITUTE ( text , old_text , new_text ,instance_num) text   문자를 대체할 텍스트가 포함된 셀의 참조 또는 텍스트입니다. old_text   바꿀 텍스트입니다. new_text   old_text를 대신할 텍스트입니다. instance_num    text에서 몇 번째에 있는 old_text를 new_text로 바꿀 것인지를 지정하는 수입니다 . instance_num을 지정하면 해당하는 위치에 있는 old_text만 바뀝니다. 그렇지 않으면 모든 old_text가 new_text로 바뀝니다. 다음과 같이 전화번호의 국번표시를 바꾸는 함수를 생각해 봅시다. SUBSTITUTE를 적용하니까 앞의 괄호는 바뀌지 않았습니다. 위의 수식으로 나온 결과값에다 한번 더 SUBSTITUTE를 적용하여, 앞에 있는 괄호를 공백으로 바꾸는 수식을 만들어 봅시다. 사실, 이것은 단축키 CTRL+F를 누르면 나오는 "찾기-바꾸기"와 유사한 기능입니다. CTRL+F를 눌러서 ")"를 찾아 "–"로 바꾸어주고 난뒤, "("를 찾아 빈칸으로 바꾸는 작업을 해도 됩니다. 하지만, CTRL+F로는 할 수 없는 경우도 있습니다. 위의 작업을 거꾸로 한다고 가정합시다. 즉, 02-222-2222라는 전화번호를 (02)-222-2222로 바꾼다고 해봅시다. 위의 그림처럼 CTRL+F를 하여 찾을내용을"–", 바꿀내용을" )"로 입력한 뒤 "모두바꾸기"하면 02)222)2222가 됩니다. 물론, 02뒤에 있는 "–"에 커서가 오면

1일1함수 (22) CONCATENATE함수 : 문자열 결합

이미지
오늘부터는 문자열에 관련된 함수를 진행하겠습니다. 지난번에는  LEN함수 와  MID함수 를 배웠습니다. 오늘은 문자열을 결합하는 CONCATENATE함수를 배우겠습니다. CONCATENATE 함수 2개 이상의 텍스트 문자열을 하나의 문자열로 연결하려면 텍스트 함수 중 하나인  CONCATENATE 를 사용합니다. 구문:  CONCATENATE(text1, [text2], ...) 예: =CONCATENATE(A2, " ", A3,"의 ", "하천 서식 밀도는 ", A4,"/킬로미터입니다.") =CONCATENATE(B2, " ", C2) 모범 사례 방법 설명 CONCATENATE 함수 대신 앰퍼샌드(&)를 사용합니다. 앰퍼샌드(&) 계산 연산자를 사용하면 함수를 사용하지 않고 텍스트 항목을 연결할 수 있습니다. 예를 들어  =A1 & B1 은  =CONCATENATE(A1,B1) 과 동일한 값을 반환합니다. 대부분의 경우 문자열을 만들 때 앰퍼샌드 연산자를 사용하는 것이 CONCATENATE를 사용하는 것보다 빠르고 간단합니다. 연산자 사용 방법에 대해 자세히 알아보세요. 아래에 써 있지만, 앰퍼샌드(&)연산자와 똑 같은 일을 합니다. 그러므로 간단한 문자열을 합치는데 철자도 길고 어려운 CONCATENATE함수를 사용할 필요는 없겠지요. 앰퍼샌드가 여러 개 붙어있으면 보기 싫을 때, 문자열 결합이라는 것을 분명히 알려주고 싶을 때 사용할 만한 것 같습니다. 참고로, CONCATENATE라는 영어단어의 뜻은 "사슬처럼 잇다" 입니다.   2009/04/22 - [컴퓨터/엑셀] - 1일1함수 (19) LEN함수 : 문자열의 길이 구하기 2009/04/23 - [컴퓨터/엑셀] - 1일1함수 (20) MID함수 : 문자열

1일1함수 (20) MID함수 : 문자열에서 지정한 위치의 문자 추출

이미지
오늘은 MID함수에 대해 다룹니다.  이전 포스트 에서 MID함수에 대해 잠깐 언급하였기 때문에, 이번에는 전에 만든 이원목적분류표와 관련하여 유용한 팁을 하나 알려드리겠습니다. MID ( text , start_num , num_chars ) text   추출할 문자가 들어 있는 텍스트 문자열입니다. start_num   추출할 첫 문자의 위치입니다. text의 첫 문자 위치는 start_num 1입니다. num_chars   MID 함수를 사용하여 텍스트에서 반환할 문자의 개수를 지정합니다. 주의 start_num이 문자열의 길이보다 크면 ""(빈 문자열)을 반환합니다. start_num이 문자열의 길이보다 작고 start_num과 num_chars의 합이 문자열의 길이를 초과하면 MID는 마지막 문자까지  반환합니다. start_num이 1보다 작으면 #VALUE! 오류 값이 반환됩니다. num_chars가 음수이면 #VALUE! 오류 값이 반환됩니다. 이원목적분류표 간단버전 에서 정답의 개수를 보여주는 수식을 살펴보겠습니다. 아래 그림에서는 O9셀의 수식입니다. =COUNTIF($C$9:$C$38,N9) C9:C38의 범위에서 정답이 1인 개수를 세어야하기 때문에,  =COUNTIF($C$9:$C$38,1)  이라고 수식을 써도 되지만 그렇게하면 2번답의 개수, 3번답의 개수등도 일일이 수식을 손으로 써줘야합니다. (상수인 1,2는 끌어채우기를 하면 3,4,5로 자동변환되지 않기 때문입니다.)  그래서 N9셀의 값을 참조하게하고 끌어채우기를 하면, 셀주소는 자동변환되기때문에 N10,N11,N12... 로 수식이 자동변환됩니다.  아무튼, 수식에서 N9셀을 참조하게한 건 이해가 되는데, 정작 N9셀에는 "1번답"이라고 써있는 것입니다. 그렇다면, "1번답"이라는 문자열에서 "1"이라는 문자만 참조해야하므로 위의 수식은 다음과 같이 고쳐야하지 않을까요?