본문 바로가기

Excel/엑셀 함수49

엑셀] 셀 안에 들어있는 특정 단어 수 세기 셀 안에 들어있는 특정 글자 또는 단어의 수를 세는 방법. 결론적으로 수식을 먼저 이야기하면 아래와 같다. =(LEN(A1)-LEN(SUBSTITUTE(A1,"찾는단어","")))/찾는단어글자수 조금 더 자세히 설명하면(1) len함수로 해당 셀에 들어있는 글자수를 세고,(2) len함수와 substitute함수로 찾고있는 글자수를 제외한 글자수를 계산한다. (3) 이 두 계산값의 차를 찾고있는 단어의 글자수로 나눠준다. - 엑셀] 텍스트 바꾸기 - REPLACE함수, SUBSTITUTE 함수 2016. 10. 17.
엑셀] 여러 셀의 텍스트 URL에 하이퍼링크 적용하기 웹에서 데이터를 수집하거나 작업중에 얻은 URL이 텍스트로 되어 있는 경우 여기에 하이퍼링크를 적용시켜야 할 때 사용하는 팁. 한 두 개의 하이퍼링크를 연결해야 할 경우에는 [삽입 탭 - 하이퍼링크] 주소에 url을 입력하면 된다. 하이퍼링크로 연결해야 하는 셀들이 위 그림처럼 많은 경우는 하나씩 하이퍼링크로 연결하기보다 Hyperlink 함수를 사용하는 것이 효율적이다. 매크로를 이용해서 작업해야 할 경우는 아래 매크로 VBA코드 참고(하이퍼링크를 연결할 셀들을 블록으로 설정한 상태에서 매크로 실행) Sub HyperAdd() 'Converts each text hyperlink selected into a working hyperlink For Each xCell In Selection ActiveS.. 2016. 10. 14.
엑셀] RANK함수로 순위 구하고, 그룹별 순위 구하기 RANK함수는 엑셀2007부터 RANK.EQ함수로 바뀌었다. (엑셀2007 이하 버전에서 정상적으로 작동하기 위해서는 RANK함수를 사용해야 한다는 뜻) RANK.EQ함수를 이용해서 등수를 구해보자. RANK.EQ함수의 사용법은 아래처럼 간단하다. =RANK.EQ(순위를 구하려는 값, 범위, [정렬방법]) 첫번째 인수로 순위를 구하려는 값두번째 인수로 순위를 구하려는 범위를 지정하면 된다. (세번째 인수를 사용하지 않거나 0을 사용하면 기본값인 내림차순으로, 1을 사용하면 오름차순으로 순위를 계산한다. 즉. 세번째 인수로 1을 사용하면 성적이 제일 낮은 사람이 1로 계산된다) 아래 그림에서 다음 수식을 입력하였다. =RANK.EQ(D2,$D$2:$D$14) 위 그림에서 볼 수 있듯, 동점자가 있을 때 모.. 2016. 10. 12.
엑셀] 여러 조건을 만족하는 경우 합계 구하기 복수의 조건을 만족하는 경우의 합계를 구할 때 배열수식으로 구하는 것과 sumifs를 이용해서 구하는 방법의 차이에 대해서 물어보시는 분이 계시길래 정리해봤다. 딱히 정답이라기보다 손에 익은 방법, 편리한 방법, 빠르게 작업할 수 있는 방법이 맞는 방법 아닐까?? ^^;; 아래 그림에서 두 가지 조건에 해당하는 경우 합계를 구하는 방법을 왼쪽에서는 배열수식으로, 오른쪽에서는 sumif함수와 sumifs함수를 이용해서 구했다. (클릭하면 큰 그림으로 볼 수 있다. 합계를 구한 아래 셀에 수식을 복사해서 붙여넣었다. ) - 엑셀] 조건에 따라 합계 구하기 - SUMIF, SUMIFS- 엑셀] 배열수식 활용 - and 조건과 or 조건으로 값 구하기 2016. 10. 7.
엑셀] 진수 변환(2진수, 8진수, 10진수, 16진수) 진법 또는 진수의 변환을 엑셀의 함수로 해보자. 엑셀은 4개의 진법을 지원한다. 진수의 변환 함수는 다음과 같고, 함수의 사용법은 동일하다. 일단 함수의 종류부터 알아보자 ㅇ 2진수를 10진수로 변환 : BIN2DEC함수ㅇ 2진수를 16진수로 변환 : BIN2HEX함수ㅇ 2진수를 8진수로 변환 : BIN2OCT함수 ㅇ 10진수를 2진수로 변환 : DEC2BIN함수ㅇ 10진수를 16진수로 변환 : DEC2HEX함수ㅇ 10진수를 8진수로 변환 : DEC2OCT함수 ㅇ 16진수를 2진수로 변환 : HEX2BIN함수ㅇ 16진수를 10진수로 변환 : HEX2DEC함수ㅇ 16진수를 8진수로 변환 : HEC2OCT함수 ㅇ 8진수를 2진수로 변환 : OCT2BIN함수ㅇ 8진수를 10진수로 변환 : OCT2DEC함수ㅇ .. 2016. 9. 22.
엑셀] 예측값으로 그래프를 그리는 예측시트 이전 블로그에서 forecast함수를 이용해서 기존 값에 의거한 예측값을 구하는 방법에 대해 정리했다. - 엑셀] 실적을 기준으로 예측하기(forecast함수) 이번 글에서는 엑셀2016에 추가된 "예측시트" 기능을 사용하여 기존 값에 의거한 추세 그래프를 그려보자. 예측시트 기능을 사용하기 위해서는 날짜 또는 시간 단위로 된 계열과 이 계열에 해당하는 값의 두 계열이 있어야 사용할 수 있다. 다음의 표는 왼쪽이 날짜, 오른쪽이 실적 데이터로 구성되어 있다. 표 안의 한 셀을 클릭한 다음 [데이터 탭 - 예측시트]를 클릭한다. 왼쪽 아래 옵션 버튼을 클릭하면 더 많은 세부적인 사항에 대해 설정할 수 있다. 예측시 시작되고, 종료되는 날짜를 수정하거나 신뢰구간을 수정할 수도 있다. 설정이 끝나면 [만들기].. 2016. 9. 21.
엑셀] 실적을 기준으로 예측하기(forecast함수) 엑셀의 FORECAST함수는 기존값에 의거한 선형추세에 따라 예측값을 구하는 함수이다. (엑셀 도움말에 나오는 말 ^^;;) FORECAST함수의 사용법은 이렇다. =FORECAST(예측하려는 데이터 요소, 종속 데이터 범위, 독립 데이터 범위) 뭔말인고? 인수에 대한 용어가 어렵지만 아래 그림을 기준으로 설명해보면 조금 쉬울지도 모르겠다. 아래 그림은 1월부터 8월까지의 실적을 집계해 놓은 표이다. 이 표의 1월~8월 실적 데이터로 9월의 실적을 FORECAST함수로 예측값을 구하려고 한다. 여기서 예측하려는 데이터의 요소는 9월,종속 데이터의 범위는 9월 실적에 영향을 주는 1월~8월의 실적 데이터독립 데이터 범위는 실적 데이터에 대한 기간인 1월~8월을 의미한다. 이제 아래 그림처럼 FORECAST.. 2016. 9. 21.
엑셀] iferror, ifna 함수로 수식 오류(에러) 처리하기 엑셀 수식 결과 나타나는 오류(에러)가 예상된다면 if 수식으로 조건을 주어서 처리하는 방법이 아마 정석(?)이지 않을까? 예전에는 if함수와 iserr함수를 사용해서 에러인지 여부를 확인하고 if 조건으로 처리하는 방법을 사용했지만 엑셀 2007부터 iferror함수가 지원되면서 더 간단한 수식으로 처리가 가능하게 되었다. 기본적인 사용법은 [IFERROR(값, 에러일 때의 값)] 으로 두 개의 인수를 사용한다. 즉 첫번째 인수로는 원하는 계산 수식을, 두번째 인수로 계산 결과 에러일 때 나타낼 문자 등을 인수로 사용한다. #N/A 오류 처리를 위한 IFNA함수가 별도로 마련되어 있지만, 구글링을 해봐도 iferror함수와 ifna함수를 따로 사용해야 할 필요성은 찾지 못했다. iferror함수로 통일.. 2016. 9. 20.
엑셀] 재직기간(근속년도) 계산하기 재직기간 또는 근속년수 등을 계산하는 방법을 정리해본다. 재직기간이나 근속기간처럼 어떤 두 날짜를 기준으로 시작일로부터 종료일까지의 날짜 간격을 구하는 함수는 datedif()함수를 사용한다. datedif()함수는 시작일과 종료일을 인수로 받고, 세번째 인수로 경과된 날짜를 어떻게 계산해서 반환할지 결정하는 옵션을 받는다. 도움말을 그대로 옮기면 반환할 정보의 형식인 Unit의 종류는 "Y" - 해당 기간에 포함된 전체 연도 수"M" - 해당 기간에 포함된 전체 개월 수"D" - 해당 기간에 포함된 날짜 수"MD" - start_date와 end_date의 날짜 차이. 두 날짜 사이의 만 개월을 제외한 일수를 의미"YM" - start_date와 end_date의 개월 차이. 두 날짜 사이의 만 햇수를.. 2016. 8. 16.

티스토리툴바