rand(life)

지식인에서 들어온 질문. 리스트상의 단어가 데이터상의 단어를 포함한 긴 단어에 있을 경우 해당하는 모든 중복되는 데이터에 대해 Listed 밑에 리스상의 단어로 하나만 표기하면 됩니다. 가령 APPLE 이 리스트 단어이면 DATA상에 APPLE_1/APPPL_2가 있으면 그냥 APPLE 만 표기하도록 매크로를 작성해주세요. List에 없는 단어 중 Name에 있으면 Non-listed에 표시해주세요 여기서는 안보이지만, Apple, Melon, Strawberry, Pear 등으로 이루어진  List목록이  따로 있다 Potato는 List목록에는 없는데 Name에는 있기때문에 Non-listed에 표시된다. 사실 앞의 문제는 해결이 쉽다. .Find 메서드를 이용하면 된다. 시트에서 Ctrl-F 를 누른 것과 같은 기능을 한다.  c = Worksheets(1).Range("a1:a500").Find(2, lookin:=xlValues) 위는 a1:a500에서 2라는 값을 찾아서 셀을 반환한다. 즉, 여기서  C 는 Range 변수로 정의되어야한다. 그런데 두번째 문제가 좀 어려웠다.  List에 있는 각 단어를 가지고 Name에 있는 셀을 다 찾은 다음, Name에 있는 단어를 하나씩 이용해서 List를 뒤져야하나? 그러면 시간이 오래 걸릴 것 같아서 기각이다. 마침내 방법을 생각해 냈는데, 배열변수를 이용하는 것이다! 배열변수의 크기를 Name의 단어 갯수만큼 설정하고 Find 및 Findnext를 이용해서 List에 있는 단어를 계속 찾고,, 찾을 때마다 해당 셀의 위치(행번호)를 배열변수의 위치에다 매칭시킨다 예를 들어, Name에 100개의 단어가 A1셀부터 A100셀까지 들어있으면   arr라는 배열변수를 arr(1)부터 arr(100)까지 설정한다. (초기값은 0이다) 이제 List에 있는 첫번째 단어를 Name에서 찾으니, A3, A15에서 발견

'지식인에서 엑셀 관련 질문에 답변을 해주다가 VBA공부를 시작했다. 다음은 어느 분이 '연도-월'의 이름을 가진 시트가 많이 있는 파일에서, 연도별로 시트를 저장하고, 올해 1월과 작년 12월, 그리고 연도가 아닌 글자로 시트명이 된 시트들을 따로 모아 저장해달라는 의뢰(?)에 대한 연구 결과이다 파일로 다운 받기 Option Explicit                         '변수를 선언해야 사용할 수 있다는 옵션 Sub MergeWBs()                   '매크로 이름 Dim i As Integer                '순환문 작업에 필요한 변수 Dim shtnum As Integer           '시트의 총갯수를 저장하기위한 변수 Dim sh As Worksheet              '선택한 시트를 복사할 때 시트 각각에 배당하는 변수 Dim shs As Sheets                 '선택한 시트 전체에 배당하는 변수 Dim wkbtg As Workbook              '새로 만드는 엑셀파일에 배당하는 병수 Dim MyPath As String                       '저장위치 Dim strThisYr As String                        '올해 연도를 담을 변수 Dim strOldYr As String                           '이전 연도를 담을 변수 Dim strshname As String                     '시트명을 담을 변수 Application.DisplayAlerts = False          '실행속도를 빠르게하기 위해 경고,이벤트 등을 꺼둔다 Application.EnableEvents = False Application.ScreenUpdating =

이런 식으로 과일에 대한 가격이 매겨져 있습니다. 이제 다른 곳에 견적서를 작성한다고 하면, 품명에 "사과" "배' 등으로 기록하면 그 품목에 대한 가격을 구하는 것은 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