rand(life)

위의 경우에서처럼, 3행이 숨겨져 있는데, 거기에 숫자 3이 쓰여있다countif함수를 써서 A1:A5범위에서 3의 개수를 찾으라고 하면 숨겨진 3행도 포함하여 찾기에 1이라는 결과가 나온다 만약, 숨겨진 셀은 제외하고, 보이는 셀에서만 countif함수를 적용하려면 어떻게 할까?subtotal이라는 함수는 숨겨진 셀을 제외하고 보이는 셀만 셀 수 있는 함수다. 다만, countif처럼 조건을 줄 수는 없고위에 링크된 글에서 보듯이,셀의 개수, 평균, 합계, 최대값, 최소값 등을 낼 수 있다. 지식인에 질문글을 올렸더니 기발한 답이 도착했다.답은 아래와 같다.=SUMPRODUCT(--(A1:A5=C9), SUBTOTAL(103,OFFSET(A1,ROW(A1:A5)-ROW(A1),0))) 여기서 핵심은 아..

[수식으로 날짜 계산을 위한 팁]지식인에서 근태 관련한 질문에 대한 답변으로 나온 수식이다. 1. Mod함수 이용F3에는 2017-10-31 9:15:00 PM 와 같은 날짜+시간 값이들어있다 여기서 MOD(F3,1)-TIME(18,,) 이렇게하면 9:15:00 PM에서 6:00:00 PM 을 뺀 값인 3:15:00 이 나온다. 2017-10-31 9:15:00 PM 은 엑셀에서 43039.8854166677 과 같이 계산하는데여기서 정수 부분은 날짜를, 소수부분은 시간을 나타낸다. 그러므로 여기서 시간 부분만 가져오려면1로 나눈 나머지를 구하는 수식 Mod(F3, 1)을 쓴다 그러면 43039.8854166677 을 1로 나눈 나머지인 0.8854166677가 결과로 나오게 되며이 부분은 9:15:00..

이동 옵션 (F5 키 - 옵션)은 여러모로 쓸 모가 많은 기능이다. 그런데 가끔씩, "마지막 데이터 셀"이 의도대로 기능하지 않을 때가 있다. 우리 생각에는 이 기능이 "현재 데이터 중에서 맨 마지막행,열에 있는 셀"을 보여준다고 생각하는데실제로 해보면 "지금까지 이 시트에서 기록되었던 셀 중에서 맨 마지막행.열에 있는 셀"을 보여주는 것이다. 즉, 한번이라도 값이 기록된 적이 있었던 셀 전체 중에서 맨 끝셀을 보여준다. 예를 들어, A1셀에 데이터가 있는 상태에서 A1048576 셀 (A열의 맨 마지막 행이다)에 데이터를 기록했다가 삭제하면이동 옵션에서 마지막 데이터 셀을 선택하고 확인하면 일반인의 생각에는 A1셀로 이동해야겠지만 실제로는 A1048576 셀로 이동한다. 물론, 이 현상은 파일을 저장하..

성명 주민번호 김1 111111-1111111 김2 111111-1111112 김3 111111-1111113 김4 111111-1111114 김5 111111-1111115 김6 111111-1111116 이런식의 구조로 된 표에서 사람이름을 주면 주민번호를 찾는 방법은 vlookup을 쓰면 간단히 해결된다. 하지만, 데이터가 좌우로 넓게 퍼져있다면? 성명 주민번호 성명 주민번호 성명 주민번호 김1 111111-1111111 이1 222222-2222222 박1 333333-3333333 김2 111111-1111112 이2 222222-2222223 박2 333333-3333334 김3 111111-1111113 이3 222222-2222224 박3 333333-3333335 김4 111111-111..

MS엑셀 전문가 "급상승 랭킹"에서 1위를 했다.답변수 125개 중 채택이 105개 정도 된 것 같다.다음주가 되면 또 내려가겠지만, 그래도 기념이 될 듯 하여 남긴다.

sumif 함수는 여러 시트에 걸쳐서 적용할 수는 없다는 내용의 글을 이전에 작성한 적이 있습니다.그런 종류의 질문이 올라오면 VBA를 이용하던지 데이터- 통합 기능을 이용하라는 답변을 달았습니다.그런데, 우연히 이런 답변을 발견했습니다. 역시 큰 형님의 작품입니다. 비슷한 예제를 만들어 첨부하였습니다. 해당 예제에서 수식 계산 기능을 이용해, 이 수식이 어떤 식으로 작동하는지 보려고 합니다. =SUMPRODUCT(SUMIF(INDIRECT(ROW(INDIRECT("1:3"))&"월분!$C$5:$C$12"),C4,INDIRECT(ROW(INDIRECT("1:3"))&"월분!$D$5:$D$12"))) 일단, 수식을 전체적으로 보면맨 바깥에 sumproduct함수가 감싸고 있고그 안에 sumif함수가 있습니..

지식인 질문 중 큰형님 답변 F:H 범위와 같이 주어진 표를 이용해노란색 셀에 들어갈 수식을 찾는 문제이다. 처음에는 If 구문을 세 번 쓰고그 안에서 match 함수를 이용해 찾는다고 생각을 했다. 오른쪽으료 표가 더 길어지만 VBA를 이용하고...... 그런데 큰형님의 답은=MAX(INDEX(($F$2:$H$6=B2)*$F$1:$H$1,)) 아주 간결하다.이 수식이 어떻게 동작하는지 알려면 엑셀에서 범위의 곱셈이 어떻게 동작하는지 알아야한다.엑셀의 범위는 수학에서 배우는 행렬과 유사하게 생겼지만 연산은 약간 다르다 일단 수학에서 말하는 행렬의 연산을 위키백과에서 알아보면 다음과 같다. 행렬 {\displaystyle A={\begin{bmatrix}a\\b\end{bmatrix}}}와 {\displa..

지식인 질문에 대한 답이다. 내가 한 답은 아니지만, 발상이 놀라와서 가져온다. 1부터 n까지의 숫자 합을 구하는 식은n∑ k 이렇게 표현이 된다 k=1 이것을 엑셀에서 수식으로 표현하는 방법이다. 정답은 =SUMPRODUCT(ROW(OFFSET(A1,,,A1,))) offset 함수의 성질을 이용했다.offset함수의 설명을 반복한다. offset 함수의 구문은 다음과 같다. OFFSET(reference, rows, cols, [height], [width])이 중, 뒤에 height와 width를 이용한 방법이다. 평소에 이 옵션을 빈칸으로 주면 offset함수는 reference위치에서 rows만큼 아래로, cols만큼 오른쪽으로 간 위치의 셀값을 보여준다. 하지만 height와 width를 표시..