본문 바로가기

컴퓨터 활용/구글스프레드시트 응용

기간 수익률 조건 서식 적용하기 / 조건부 서식 이용 @구글시트로 하는 펀드수익률 관리

이전 포스팅에 일일펀드 분석에서 기간별 수익률을 구현하였는데

 

일일 펀드 분석 및 이벤트 E-mail 통보 @구글 스프레드시트로 하는 펀드수익률 관리

구글 스프레드 시트로 펀드 수익률 관리를 하고 있습니다. 재작년에 최종 수정을 하였는데 기준가 수집하는 방법 , 초기화면에 상승,하락 펀드, 설정액 증가 , 감소 펀드 표시 기능을 추가하고

richardshin.tistory.com

45일을 하나 더 추가하였습니다.

펀드별로 일일수익률이 평균에 못 미치는 펀드를 한 눈에 알아 보기 위하여 구글시트의 조건부서식을 이용하여 다음과 같이 구현해 보았습니다. 조건부 서식의 맞춤 수식은 상당히 유용합니다. 조건부 서식에 융통성을 부여 합니다.

구글 시트의 조건부서식의 형식 규칙에는 다음과 같은 것이 있습니다. 해당 셀이 다음과 같은 형식일 때 셀의 서식이 자동지정 되는 것이지요. 맞춤 수식을 지정하고 수식을 직접 지정합니다.

맞춤 수식은 =( 수식 ) 이런 식으로 입력 하면 됩니다. 수식에는 특정 셀의 주소가 들어가야 하고 리턴 값이 TRUE 또는 FALSE이 됩니다.

응용을 위하여 설명을 드리면.

1. D2 셀에 마우스를 놓고 메뉴의 서식 > 조건부서식으로 이동합니다.
2. 다음과 같은 수식을 입력 합니다.

=( D2 <= ARRAYFORMULA(AVERAGE((D$2:D<>"")*(D$2:D))) )

ARRAYFORMULA함수를 이용하여 D열의 값의 평균을 구합니다.
D2 셀이 구해진 평균 보다 작거나 같다면 TRUE 를 반환합니다. D$2:D 라고 한 이유는 2열 부터 마지막열까지 자동지정되도록 하였고 $ 는 셀 복사하더라도 상대주소가 변경되지 않도록 고정하려는 목적입니다.

3. 서식 지정 = 빨간색 배경화면으로 지정
4. "범위에 적용"에 D2:I41 로 영역을 넒게 설정

D,E,F,G, I 열에 모두 적용한다. 그러면 수식에서 D는 E로 자동으로 변하고 $ 로 지정한 것은 변하지 않는다.

도구 메뉴의 스프레드 편집기를 실행 시키면 소스를 보실 수 있습니다. 읽기 전용입니다. 수정을 위해서는 시트복사 해서 사용하세요.

해당 부분을 예제로 분리 했습니다. 
https://docs.google.com/spreadsheets/d/1lePnGMePlv096kBSab7vBNylHA05N4Srb13TLn8CUdo/edit?usp=sharing 

 

기간 수익률 조건 서식 적용하기(예제)

펀드평균등락률 펀드명,펀드코드,기준가,1,3,5,20,45,60,추종지수등락률 KB연금중국본토A주[주식]Ce,KR5223AW2316,2047.45,1.10%,-0.83%,0.76%,-1.34%,3.73%,3.76%,1% 이스트스프링차이나AShare UH_주식S,KR5229684984,1508.48,1

docs.google.com