|
◆ 서식과 수식관련 TIPS ▶ 일정 범위들의 셀값들을 더하기 =SUM(a1:a5,a7:a10)
▶ 특정범위값들의 셀값들을 조건부로 더하기 =SUM((범위>=21)*(범위<=30)) ctrl+shift+enter
▶ 셀의 특정문자를 기준으로 셀값들을 더하기 성이 김씨인 경우에는 =SUM(N(MID($A$1:$A$20,1,1)="김")) 이름이 주현인 경우에는 =SUM(N(MID($A$1:$A$20,2,2)="주현"))
▶ 여러 워크시트의 특정셀의 값들을 더하기 =SUM(Sheet1:Sheet250!E15)
▶ 홀수행의 값들만 더하기 각 행의 일련행수를 2로 나누어서 나머지가 1인 것이 홀수행 의 데이터이므로 =SUM((MOD(ROW(A1:A100),2)=1)*(A1:A100)) ctrl+shift+enter
▶ 셀값중 특정열의 text만 추출하기 A B 1 3011820001 20 = mid(a1,6,2) 2 3011845001 45 = mid(a2,6,2) 3 3011870001 70 = mid(a3,6,2)
▶ 특정시트에 여러 시트의 셀값들을 자동 채우기로 여러 셀에 입력하기 =INDIRECT("Sheet"&ROW()&"!"&"$C$3")
▶ 부분합,또는 합계등과 같은 특정셀 값들만 선택해서 서식지정하기 조건의 "수식" 을 선택하고 다음식을 사용한다. =NOT(ISERROR(FIND("합","~~~합계로 된 셀주소")))
▶ 주민등록번호를 이용하여 나이 계산하기 A B 1 주민등록번호 현재나이 2 751204-1524752 ? =YEAR(NOW())-LEFT(a2,2)-1899&"세"
▶ 주민등록번호를 년월일로 변경표시하기 =dateval!!ue(text(left(주민등록번호,6),"00-00-00"))
▶ 증가되는 셀과 값들의 평균을 자동으로 구하기(공란포함) =AVERAGE(OFFSET(A$1,COUNTA(A:A)+COUNTIF(A:A,"")-4,0,4,1))
▶ 음수 양수 구별하여 색 기호 넣기 음수 값이면 파랑의 역 삼각형과 글꼴, 양수 값이면 빨강의 삼각형과 글꼴 모양으로 표시 셀 서식의 사용자정의 서식으로 설정한다. [빨강]"△" #,##0;[파랑]"▽" #,##0;0;@
▶ #N/A, #VALUE!, #REF!, #NUM! 등의 에러값을 화면에 표시않기 셀에 입력되는 기존의 수식 대신에 IF문을 사용해 다음과 같은 수식을 입력한다. =IF(ISERROR(수식),"",수식) 위의 IF 함수는 수식의 결과가 에러인 경우 빈 셀로 나타내고 그렇지 않은 경우 수식값을 나타낸다.
▶ 수식을 결과값이 아닌 문자식로 표현하기(수량산출서의 산식) B1셀에 마우스를 놓고 "수식 보이기"등으로 이름을 정의. 참조란에 "CET.CELL(6,Seet1!,A1)" 입력 B1셀에 =SUBSTITUTE(수식보이기(이름),"=","") 와 같이 입력한다
▶ 문자식을 수식으로 계산하기(수량산출서의 결과값) B1셀에 마우스를 놓고 "수식을 문자로"등으로 이름을 정의. 참조란에 "EVAL!!UAT(Seet1!,A1)" 입력 B1셀에 "=수식을문자로(이름)" 와 같이 입력한다
▶ 수식을 표현하여 나타내기 A1,B1에 각각 1000과 2000이 입력되어 있고, C1셀에 1000+2000=3000을 나타내려고 한다. 물론, A1과 B1의 데이터가 변경되면 C1의 결과도 바뀌게 하려면? C1셀에 =A1&"+"&B1&"="&A1+B1을 입력한다. C1셀에 서식을 적용하려면 TEXT()함수를 이용한다.
TEXT()함수 안에 넣을 수 있는 서식은 셀 서식의 표시 형식에 있는 서식이면 모두 가능하다. 예를 들어, 아래의 수식을 사용하면 C1셀에 결과값은 1,000+2,000=3,000로 나타난다. =TEXT(A1,"#,###")&"+"&TEXT(B1,"#,###")&"="&TEXT(A1+B1,"#,###") --#,###은 천단위콤마표시
▶ 엑셀에서, 소수점이 0.5미만이면 소수 값을 0.5로 0.5이상이면 반올림을 하려고 한다. 예를 들어 아래와 같이 나타내고 싶다면, 5.1 => 5.5 5.5 => 6 6.2 => 6.5 6.6 => 7 7.3 => 7.5 7.7 => 8 8.4 => 8.5 8.8 => 9 다음의 수식을 이용한다. =(if(A1-int(A1)<0.5,int(A1)+0.5,round(A1)) A1은 값이 들어 있는 셀 주소이다.
▶ 엑셀에서 어떤 특정 셀에 숫자만을 입력되게 하려면? 엑셀 워크 시트 기능에서 numeric값 아닌 것을 입력되지 않게 하는 방법은 없지만, 표시 형식을 이용하여 non-numeric값이 입력되었을 때 셀에 메시지를 나타나게 할 수 는 있다. 다음의 예는 원화 표시 형식을 나타내고, 입력된 값이 숫자가 아닐 때는 "Must enter a number"라는 경고가 나타낸다. ￦0.00_);[red]￦(0.00);￦0_);[red]"Must enter a number!"
▶ 숫자를 한글로 나타내기 =NUMBERSTRING(A1,1) =IF(LEFT(A1,1)="1","일","")&NUMBERSTRING(A1,1)
▶ 단 단위 숫자를 두자리로 표현하기 > A8셀에 =SUM(A1:A7)란 수식이 적용 답은 8 > 08이 모두 표현될수 있는 방법 - 셀서식을 00 으로 지정하거나 [>99]0000;[>9]000;00으로 지정 |
출처: 다빈치의 건축 이야기 원문보기 글쓴이: 백발제비
