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)을 수식계산에서 돌려보면 

이렇게 단어시험 문제를 낸다고 합시다.  시험범위는 B1~D1이고 총 시험문항수는 D4 우리말 뜻쓰기 유형 문항수는 C3 영어철자쓰기 유형 문항수는 C4 무작위로 저 조건에 맞게 단어를 추출하려면 어떻게 해야할까요? 대개 이런 유형의 문제들은 배열함수가 답입니다. 편의상 4번 문항의 수식을 예로 들겠습니다. B13에 들어가는 수식은 위와 같습니다. 배열수식이므로 마지막에 Ctrl-Shift-Enter입니다 수식의 각 부분의 해석은 다음과 같습니다. 수식 해석 기능 ( 함수 ) =IF($A13<=$D$4, 문항번호가 총문항수보다 적거나 같으면 이후 수식 진행 ,  아니면 공백출력 OFFSET 위치 이동 함수 (IF($A13<=$C$3,LIST!$C$1,LIST!$D$1), 미리 정해진 영어 -> 한국어 문항수보다 문항번호가 작을 때 , list 시트의  C 열 ( 영어 )  첫행 선택 .  아니면  D 열 ( 한국어 )  첫행 선택 offset 함수의 인수 .  이 위치에서 특정 거리만큼 이동함 . MOD 나머지의 크기에 따라 문항번호에 해당하는 순서의 단어의 위치를 구함 나머지 구하기 (SMALL 조건에 맞는 단어들 중 몇 번째로 작은 수에 해당하는 순위를 가진 단어를 찾음 배열함수에서 크기순으로 나열하기 위해 자주 쓰는 함수 .  배열 중 몇 번째로 작은 수를 찾는다 . (IF(IFERROR 시험범위에 해당하는 단어 배열만듬 배열이 시작됨 ((SUBSTITUTE(LIST!$A$2:$A$99,"day","")*1>=$B$1)* 단어 목록 중 시험범위에 해당하는 단어들의 번호만 배열로 출력함 . day1->1, day2-> 2.... B1 은 시험범위 시작일 배열 1 (SUBSTITUTE(LIST!$A$2:$A$99,

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

역시  네이버 지식인 에서 누군가가 올려주신 해답에서 연구해볼만한 것입니다. 이렇게 데이타가 있을 때 이런 식으로 합계를 내야합니다. 문제는 G,H,I열이 셀병합이 되어있다는 점입니다. 셀병합이 안되어있다면 SUMIFS함수로 간단하게 해결됩니다만.... 여기서 어떤 분이 답을 달았습니다. 이렇게하면 셀병합을 해제하지 않아도 답을 구할 수 있습니다. =SUMIFS(D:D,A:A,INDEX($G$3:G13,MATCH("",$G$3:G13,-1)),B:B,INDEX($H$3:H13,MATCH("",$H$3:H13,-1))) 이 수식의 핵심은  INDEX($G$3:G13,MATCH("",$G$3:G13,-1)) 이 부분입니다. 일단 INDEX함수는 주어진 범위안에서 두번째 인수값 만큼의 위치에 있는 값을 보여줍니다. 그리고 두번째 인수는 MATCH함수가 반환하는 값인데, 해당 행의 G열에 있는 셀값(여기서는 G13)이 빈칸이면 그 위쪽에서 빈칸이 아닌 셀(여기서는 G12)에 있는 텍스트를 가져오는 역할을 합니다. 셀병합을 하면 제일 첫셀(여기서는 G3과 G12)에만 데이타가 들어있고, 나머지 병합된 부분에는 아무 데이터도 들어있지 않습니다. 그래서 엑셀에서 수식을 단순하게 만드려면 셀병합은 가급적 피하는 것이 좋습니다 . 아무튼, 여기서  INDEX($G$3:G13,MATCH("",$G$3:G13,-1)) 이 수식은 G3:G13의 범위 안에서  MATCH("",$G$3:G13,-1)) 의 값만큼의 위치에 있는 값을 구합니다. 그리고 MATCH("",$G$3:G13,-1) 이 수식이 핵심중의 핵심인데요, 그 중에서 -1이라는 마지막 인수입니다. match_type  동작   1 또는 생략 :   MATCH

네이버 지식인 에서 알게 된 좋은 수식을 소개합니다. 이런 식으로 A1:D2 범위 내에 사람 이름이 중복된 것 포함해서 여러개 있습니다. 이때  중복된 이름은 제외하고 모두 몇 명의 이름이 있는지  알고 싶을 때 쓰는 수식입니다. 수식은 위와 같습니다.  일단, 이 수식의 기본적인 구조는,  각 사람의 이름이 나올 때마다 그 자리에 전체 범위에서 그 사람의 이름이 나오는 횟수를 분모로, 1을 분자로 한 값을 배정하여, 나중에 그 모든 숫자를 합하는 것입니다. 간단한 예를 들어서, 다음과 같이 이름이 6개가 배열되어 있다고 합시다. 홍길동,장길산,홍길동,홍길동,홍길동,장길산 그럼 이름 대신에 그 자리에 다음 숫자를 배당합니다. 0.25, 0.5, 0.25, 0.25, 0.25, 0.5 홍길동은 총 4번 나오니 1/4해서 0.25를 홍길동이 나오는 자리마다 배정합니다. 장길산은 총 2번 나오니 1/2해서 0.5를 장길산이 나오는 자리마다 배정합니다. 이제 모든 숫자를 더하면 0.25+ 0.5+ 0.25+ 0.25+ 0.25+ 0.5 = 2  그래서 중복된 이름을 제외하고 모두 2명의 이름이 있다는 결과가 나옵니다. 이제 수식을 살펴보겠습니다. =SUMPRODUCT((A1:D2<>"")/(1-(A1:D2<>"")+COUNTIF(A1:D2,A1:D2))) (엑셀에서 수식-수식분석-수식계산을 하시면 단계별로 수식이 어떻게 계산되는지 볼 수 있으므로 수식을 이해하는데 도움이 됩니다.  밑줄이 쳐진 부분은 이번 단계에서 계산이 될 예정이라는 것을 의미하고 이탤릭으로 표시된 부분은 바로 이전 단계에서 계산이 된 결과라는 것을 보여줍니다) 이 부분은 A1:D2 범위에서 공백이 아닌, 즉 이름이 있는 셀을 골라내는 것입니다. 그래서 이름이 들어있으면 TRUE, 공백이면 FALSE

"범위&범위"라고 주면 범위끼리 문자열을 합하여 배열을 만든다 예를 들어, 이런 형태의 데이터를  이렇게 정리하기 위해 수식을 J3셀에서는  =INDEX($B$3:$D$14,MATCH($H3&$I3,INDEX($A$3:$A$13&$A$4:$A$14,),0),COLUMNS($J3:J3)) M3셀에서는 =INDEX($B$3:$D$14,MATCH($H3&$I3,INDEX($A$3:$A$13&$A$4:$A$14,),0)+1,COLUMNS($M3:M3)) 이렇게 준다. 여기서 가운데 있는  MATCH($H3&$I3,INDEX($A$3:$A$13&$A$4:$A$14,),0) 이 수식의 의미는 다음과 같다 MATCH($H3&$I3  H3&I3의 값의 위치를 찾는데 H3&I3= 김태희서울   INDEX($A$3:$A$13&$A$4:$A$14,) A$3:$A$13와 $A$4:$A$14의 문자열을 합친 배열 중에서 찾고  김태희서울,장동건수원,유지태인천,아유미일본,유승호부산,유리서울  0  비슷한 글자가 아니라 정확히 일치하는 글자를 찾아라 이 중에서  INDEX($A$3:$A$13&$A$4:$A$14,) 여기를 잘 보면 맨 뒤에 콤마만 있고 그 뒤가 없다 바로 INDEX 함수의 설명 중 다음 사항에 해당한다. 주의 reference와 area_num으로 특정 범위가 선택된 후에는 row_num과 column_num이 특정 셀을 선택합니다. 즉, row_num 1은 범위의 첫째 행이고, column_num 1은 첫째 열의 방식으로 선택됩니다. INDEX 함수로 반환되는 참조는 row_num과 column_num이 교차되는 위치입니다. row_num이나 column_num을 0으로 설정하면 전체 열이나 행에 대한 참조가 각각 반환됩니다. 즉, INDEX함수는 주로 첫번째