[스크랩] ◆ 엑셀에서 서식과 수식관련 TIPS

◆ 서식과 수식관련 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으로 지정

출처 : 다빈치의 건축 이야기

글쓴이 : 백발제비 원글보기

메모 : 엑셀수식