rand(life)

이런 식으로 과일에 대한 가격이 매겨져 있습니다. 이제 다른 곳에 견적서를 작성한다고 하면, 품명에 "사과" "배' 등으로 기록하면 그 품목에 대한 가격을 구하는 것은 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 :  결과값 위치와 실제행번호가 다르기때문에 조정 출처: 네이버지식인

점수를 구간으로 나누어 각 구간마다 등급을 매기고 싶을 때가 있습니다.  지난번에 소개한 것처럼  사용자정의함수를 이용하는 방법 도 있습니다만, 좀 더 간단한 방법을 보여드리려고 합니다. 바로 vlookup 함수를 사용하는 방법입니다.  VLOOKUP함수의 구문중 다음 주의사항을 한번 보시죠. VLOOKUP ( lookup_value , table_array , col_index_num ,range_lookup) range_lookup   정확하게 일치하는 값을 찾을 것인지, 근사값을 찾을 것인지를 결정하는 논리값입니다. TRUE이거나 생략되면  정확한 값이나 근사값을 반환합니다.  정확하게 일치하는 값이 없으면 lookup_value보다 작으면서 그 다음으로 가장 큰 값을 반환합니다 . table_array의 첫 번째 열 값은  오름차순으로 정렬해야 합니다 .  그렇지 않으면 VLOOKUP 함수를 실행하여 올바른 결과를 얻을 수 없습니다.  오늘 할 일은 위의 주의사항중 밑줄친 부분의 내용을 이용하는 것입니다. 위 그림과 같이 학생들의 성적이 있고, 각 성적에 따른 등급을 매기고 싶습니다.  지난번의 글 에서 제시한 상황과 같습니다. 이때, 다른 시트나 빈 공간아무곳에나 오른쪽과 같은 등급표를 작성합니다. 왼쪽의 숫자는  각 등급의 최소값 을 보여줍니다. 즉, 오른쪽의 등급표의 의미는  0~49점은 F,  50~59점은 E,  60~69점은 D,  70~79점은 C,  80~89점은 B,  90점 이상은 A라는 의미입니다. 그리고 학생들의 등급이 들어가는 셀의 수식을 다음과 같이 입력합니다.  =VLOOKUP(B2,$F$3:$G$8,2) 그러면 각 성적에 따라 등급이 매겨졌습니다. VLOOKUP함수의  마지막 인수를 TRUE로 주거나 생략했을때 작거나 같은 값을 찾는다는 성질 을 이용한 것입니다.  사용자정의 함수를 쓰지 않아도 되니 편합니다. 위에 설명한 두 가지만 주의하시면 됩니다. 한번 더 설명하면,  1.

INDEX함수가 상당히 자주 쓰이는 유용한 함수인데 작년에 빠뜨려서 이번에 보충합니다.  엑셀 도움말에는 다음과 같이 되어있습니다. INDEX ( array ,row_num,column_num) array     배열 상수나 셀 범위입니다. 배열에 행이나 열이 하나만 있을 때는 row_num이나 column_num 인수를 생략할 수 있습니다. 배열에 행과 열이 두 개 이상 있을 때 row_num이나 column_num만 사용하면 배열의 전체 행이나 열이 하나의 배열로 반환됩니다. row_num     값을 반환할 배열의 행을 선택합니다. row_num을 생략하면 column_num을 지정해야 합니다. column_num     값을 반환할 배열의 열을 선택합니다. column_num을 생략하면 row_num을 지정해야 합니다. 주의 row_num과 column_num 인수를 모두 사용하면 row_num과 column_num이 교차하는 셀의 값이 반환됩니다. row_num이나 column_num을 0으로 설정하면 전체 열이나 전체 행에 대한 값의 배열이 각각 반환됩니다. 배열로 반환된 값을 사용하려면 행에 대한 가로 셀 범위와 열에 대한 세로 셀 범위에 INDEX 함수를 배열 수식 (배열 수식: 하나 이상의 값 집합에 대해 여러 가지 계산을 수행하고 하나 또는 여러 개의 결과를 반환하는 수식입니다. 배열 수식은 중괄호 { } 안에 표시되고 Ctrl+Shift+Enter를 눌러 입력합니다.)으로 입력합니다. 배열 수식을 입력하려면 Ctrl+Shift+Enter를 누릅니다. row_num과 column_num은 반드시 배열에 있는 셀이어야 합니다. 그렇지 않으면 #REF! 오류 값이 반환됩니다. INDEX함수에는 사실, 배열형과 참조형이 있으며, 위의 도움말은 배열형에 대한 설명입니다. 하지만, 참조형을 지금 알 필요는 없을 듯하여 배열형만을 설명하겠습니다. 당분간은 INDEX함수를 쓰실때 다음 그림처럼 배열형이나 참조형중에 선택

지난번에 엑셀로 이원목적 분류표 를 만들었습니다. 여러가지 경우의 수를 생각하면서 수식을 만들다보니 이렇게 고생할바에야 그냥 개수를 세는게 낫겠다는 생각도 들었지만, 써보니 편하다고 말씀하시는 분들이 많아 보람도 느낍니다. 사소한 몇가지 오류를 수정하여 이전포스트에 올려두었습니다. 그리고 또 한가지 느낀 점은, 엑셀을 거의 사용하지 않으시는 분들이 의외로 많다는 것이었습니다. 제가 이것을 만들때는 어느정도는 엑셀을 사용할 줄 아시는 분들을 대상으로 만들었기에, 엑셀을 처음 다뤄보시는 분들이 난감해 하는 것을 볼때 제가 편하자고 만든 것이 또 어떤 분에게는 불편함이 되는구나 하는 생각도 들었습니다. 그래서, 다음 버전업때는 엑셀 초보이신 분들도 큰 불편없이 사용할 수 있게 대대적으로 바꾸어 볼까 합니다. 예를 들면, 주관식 문항수와 객관식 문항수를 입력하면, 자동으로 그 수만큼 표가 만들어져서, 행을 삭제하거나 추가하는 수고를 덜어드리는 것이라든지, 아니면 HWP파일에서처럼 주관식,객관식 밑에 빈칸이 있어도 오류가 생기지 않도록 하는 방법을 연구한다던지 하는 것입니다. 혹시 좋은 생각이 있으시면 제안해주시면 고맙겠습니다. (P.S. 사실은 수식이 이렇게 복잡할 필요가 없었습니다. 엑셀의 기본적인 상식을 깜빡한 탓입니다. 다시 이원목적분류표를 다시 수정해서 올렸습니다.) 2009/04/18 - [컴퓨터/엑셀] - 엑셀로 작성한 이원목적 분류표 간단버전 이번주는 엑셀로 만든 이원목적분류표안에 있는 함수들을 알아보는 시간으로 하겠습니다. 사용된 수식들은 다음과 같습니다. 문항수합계의 수식은 =COUNTA(INDIRECT("R"&MATCH("유형",$A:$A,0)+2&"C"&COLUMN()&":R"&ROW()-1&"C"&COLUMN(),FALSE)) 문항배점합계의 수식은 =SUM(INDIRE

오늘은 COLUMN함수입니다. 지난번에 잠깐 언급했던 ROW함수와 짝을 이루는 함수입니다.   그때 제대로 설명못했으니, 이번에COLUMN함수를 설명하는 김에 ROW함수를 잠깐 언급하겠습니다. ROW (reference) reference   행 번호를 구할 셀 또는 셀 범위입니다. reference를 생략하면 ROW 함수를 입력한 셀의 참조가 사용됩니다. reference가 셀 범위이고 ROW를 세로  (배열: 여러 가지 결과를 만들거나 행과 열로 구성되는 인수 그룹에 대해 연산이 이루어지는 한 개의 수식을 작성하기 위해 사용됩니다. 배열 범위는 공통 수식을 공유하며 배열 상수는 한 개의 인수로 사용되는 상수 그룹입니다.)로 입력한 경우에는 참조의 행 번호가 세로 배열로 반환됩니다. reference는 여러 개의 영역을 참조할 수 없습니다. ROW()라고 괄호안에 아무것도 입력하지 않으면 현재의 행번호가 반환된다는점이 활용하기에 좋겟지요. 이번에는 COLUMN함수입니다. COLUMN 함수 구문에는 다음과 같은  (인수: 동작, 이벤트, 메서드, 속성, 함수 또는 프로시저에 정보를 제공하는 값입니다.)가 사용됩니다. reference   열 번호를 반환하려는 셀 또는 셀 범위 (범위: 시트의 둘 이상의 셀입니다. 범위 내의 셀은 인접될 수도 있고 인접되지 않을 수도 있습니다.)로서 선택 항목입니다. reference 인수를 생략하거나 이 인수가 셀 범위를 가리키는 경우   COLUMN   함수를 가로 배열 수식으로 입력하면   COLUMN   함수는 참조 대상의 열 번호를 가로 배열로 반환합니다. 수식을 배열 수식으로 입력하려면   수식 셀부터 시작하여 배열 수식을 포함할 범위를 선택하고 F2 키를 누른 다음 Ctrl+Shift+Enter를 누릅니다. reference 인수가 셀 범위이고   COLUMN   함수를 가로 배열 수식으로 입력하지 않은 경우   COLUMN   함수는 가장 왼쪽에 있는 열의 번호를 반환합니다. ref

VLOOKUP함수는 표형태에서 첫째열에서 값을 찾아 지정된 수만큼 오른쪽으로 이동한 곳의 값을 반환하는 함수입니다. VLOOKUP(A,범위B,숫자,옵션) 범위B에서 A의 값을 찾은 후에, 해당숫자만큼 이동한 후 값을 반환합니다. 옵션에 FALSE로 쓰면 정확한 값을 찾고, 안 쓰거나 TRUE를 쓰면 비슷한 값을 찾습니다. 학생명렬표에서 특정 학생의 이름을 입력하면, 그 학생의 주소나 전화번호등이 나오는 수식을 만들어 봅시다. 다음처럼 표를 만듭니다. 이때 주의할 것은,   VLOOKUP함수는 반드시 첫째열에서 값을 검색하기   때문에, 찾을 고유의 이름이 있는 열을 맨 첫열로 보내어두는 습관을 들이는 것이 좋습니다. 지금처럼, 이름을 검색해서 찾는다면 이름이 들어있는 열을 맨 첫열로 보내는 것이 찾기가 쉽겠지요. 그리고 검색할 폼을 만듭니다. 허접하지만, 다음과 같이 만들어 보았습니다. 파란색 사각형 속에 학생의 이름을 넣으면, 번호, 주소, 학생전화번호, 계발활동반이 입력됩니다. 번호란 (F3셀)에 들어가는 수식은 다음과 같습니다. =VLOOKUP($H$3,sheet1!$A$1:$H$11,2,FALSE)   =VLOOKUP(  $H$3  H3셀의 값을  ,sheet1!$A$1:$H$11,  SHEET1의 A1:H11의 범위의 첫열(여기서는 A열)에서 찾아서  2,  오른쪽으로 첫번째 열의 값을 출력하라  FALSE)  단, H3셀의 값과 똑같은 값을 찾아야한다(비슷한 값을찾으면안된다) 여기서 주의하실 것은, 숫자가 2가 입력되었으니까 오른쪽으로 "두 칸 가서" C열의 주소를 출력한다고 생각하면 안된다는 것입니다. 1이면 범위의 첫열이, 2이면 오른쪽으로 한 칸 옆의 열이, 3이면 오른쪽으로 두 칸 옆의 열 이 출력됩니다. 수식에 입력하는 숫자와 "오른쪽으로 이동한 칸의 숫자"가 다른 것입니다. 그러므로 VLOOKUP함수에서 세번째 인수 의 의미는