엑셀 숫자 범위, 조건 - egsel susja beom-wi, jogeon

- PGR21 관련된 질문 및 건의는 [건의 게시판]을 이용바랍니다.
- (2013년 3월 이전) 오래된 질문글은 [이전 질문 게시판]에 있습니다.
통합 규정을 준수해 주십시오. (2015.12.25.)

제가 원하는 것은
만약 A의 셀의 값이 100 ~ 1000 사이의 값이라면 B의 셀에 2.5라고 나오고
만약 A의 셀의 값이 1001 ~ 10000 사이의 값이라면 B의 셀에 4.4라고 나오게 하는 방법을 알고 싶습니다.

참고로 결과적으로 A셀과 B셀을 곱하여 C셀에 나타나게 하는 것입니다.

참고로 문과입니다. 엑셀은 초급정도로 다루는 수준입니다.

뭘 써야할지 망막하네요...직장에서 매일같이 임의의 값이 주어지면 해당하는 조건에 맞춰서 딱딱딱 어떤 값들을 구하는 것인데..
내 능력 밖이라고 형님들의 조언을 구합니다.

감사합니다.

"Pgr은 '명문화된 삭제규정'이 반드시 필요하지 않은 분을 환영합니다.
법 없이도 사는 사람, 남에게 상처를 주지 않으면서 같이 이야기 나눌 수 있는 분이면 좋겠습니다."

학점 같이 범위별로 등급을 나눠야할 때가 있다.

점수별 학점이 아래와 같다면

A학점: 100점

B학점: 80이상 ~ 100미만

C학점: 60이상 ~ 80미만

D학점: 40이상 ~ 60미만

F학점: 0이상 ~ 40미만

IF 함수를 써서 표현할 수 있다

= IF (학점 < 40, "D", IF(학점 <60, "C", IF(학점 < 80, "B", "A")))

IF속으로 계속 파고들면서 아닐 경우의 인수에 IF을 추가하면 된다
그런데 구간이 많을 경우 조금 헷갈리고 귀찮기 때문에

더 쉬운 방법으로 등급을 나눠보려 한다.

간단한 학점, 시간, 날짜 이렇게 세가지 예제를 다뤄보자

예제1 - 점수범위별 학점구하기
예제2 - 시간별 점수구하기
예제3 - 근속연수별 추가연차

1. 점수별 학점
먼저 구간을 적자
Xx점 이상이면 xx등급
* 여기서 첫열은 꼭 오름차순으로 해줘야하고
* 이 값은 오른쪽 등급을 가질수 있는 최소값이다

여기 학생들 점수가 있다

학점을 내기 위해서 VLOOKUP 함수를 사용하겠다

= VLOOKUP (학생점수, 구간테이블, 2, TRUE)

VLOOKUP 마지막 인수를 TRUE나 1로 넣으면
테이블에 정확히 일치하는 값이 없으면

찾으려는 값보다 작은 값 중에서 가장 큰 값을 반환한다

예를 들어, 79점이면

등급테이블에서 79보다는 작은 값인 

0, 40, 60 중 가장 큰 값인 60에 해당하는 학점 D가 출력된다

2. 시간별 점수
시간별 구간도 적용해보겠다
분:초를 문자로 받고 

0초 이상이고 00초 미만일 때 15점 

00초 이상이고 00초 미만일 때 14점.. 

이렇게 적고

이것도 똑같이 VLOOKUP 사용하면 된다

= VLOOKUP (시간, 구간테이블, 2, TRUE) 

3. 근속연수 추가연차
회사에서도 쓸 수 있다
보통 회사는 근속연수 따라 연차를 몇개 더 주는데

이렇게 3년차부터 00개,

6년차부터는 00개

이렇게 추가가 된다고 하자

근속연수도 구해둬야지
오늘 날짜를 TODAY()함수로 넣고, DATEDIF함수로 근속연수를 구한다.

이 근속연수에 따라 추가 연차가 생기니까
앞의 예제처럼 = VLOOKUP(근속연수, 구간테이블, 2, TRUE)

완성!

주의
• 구간테이블의왼쪽열은'오름차순'!작은숫자부터쓴것

• 해당등급을가질수있는최소값
• VLOOKUP마지막값1또는TRUE
을 적어야한다는 것
주의해야 한다~~~

쩡보귀가 개수를 구하기 위하여 사용 할 함수는 COUNTIFS 함수입니다. 너무 너무 쉬우니 1분만 투자해주세요^^ 위 예제에서 성적이 70점이상 100점 이하인 개수(명)를 구해보도록 하겠습니다.

먼저, 구하려는 개수 셀에 블록(클릭)한 상태에서 상단의 함수 fx를 더블클릭 해주세요. 더블 클릭을 하면 위와 같이 함수 마법사 탭이 열리게 됩니다.

함수 마법사 탭이 열리면 함수검색을 하는곳에 countifs 를 치고 검색을 눌러줍니다. 그러면 함수선택란에 countifs 가 검색되고 그것을 클릭하고 확인을 눌러주세요.

확인을 눌러주면 위와 같이 '함수 인수' 탭이 열립니다. 여기서 criteria_range1란에 찾을조건의 전체 범위를 입력합니다. 예제에서는 C2부터 C10까지 이니 C2:C10 입력 또는 C2부터 C10까지 드래그를 해주세요.

찾을 조건의 전체범위를 지정해주었다면 다음은 구하려는 조건의 범위를 입력합니다.

①criteria1에 70점 이상이니 ' >=70 '을 입력하고 다음은 ②criteria_range2 란에 똑같은 전체범위인 C2:C10를 입력합니다. 마지막으로 ③criteria2에 100점 이하이므로 ' <=100 '을 입력하고 확인을 눌러주면 구하려는 조건의 개수가 구해집니다.

함수인수 탭에 구하려는 조건을 모두 입력하니 개수 5가 나왔네요. 위의 예제에서는 표본이 9명이라 그냥 세어도 개수5가 쉽게 나옵니다. 그러나 실생활에서 몇십개 또는 몇백개 이상의 표본에서 조건의 개수를 구하려 한다면 일일이 세는것은 불가능하겠죠? 이런경우 COUNTIFS 함수를 활용하여 쉽게 해결하길 바랍니다^^

SUMIF 함수는 SUM 함수와 IF 함수가 합쳐진 형태입니다.

"만약(IF) 어떤 조건을 만족하면 SUM하라"는 의미입니다.

이 함수는 자료에서 특정 조건을 만족하는 범위의 숫자 합계를 구해야 할 때 많이 사용됩니다.

구문(Syntax)

SUMIF

SUMIF(range, criteria, [sum_range])

조건(criteria)에 맞는 범위의 숫자의 합계를 구해준다.

인수 :

• range : 조건을 적용할 셀 범위
• criteria : 합계를 구할 조건
• sum_range : 합계를 구할 범위(생략 가능)

실습용 엑셀파일 다운로드 : 엑셀-SUMIF-함수-조건을-만족하는-범위의-합계구하기.xlsx

사용 예

1) 판매실적에서 거래처별 합계구하기

아래 그림의 자료에서 거래처가 '신촌 아트박스'인 곳의 판매금액 합계를 구하려면 [E14] 셀에 다음 수식을 입력합니다.

=SUMIF(C4:C11,"신촌 아트박스",E4:E11)

수식 풀이:

SUMIF 함수는 아래 그림과 같이

• 거래처명이 입력된 조건 범위 [C4:C11]의 값이 '신촌 아트박스'이면
• 판매금액이 입력된 합계 범위 [E4:E11] 에서 같은 행의 판매금액만 더해서 710,000원을 구해줍니다.

2) 두 번째 인수(값을 더할 조건)를 셀 참조로 입력하기

위의 예에서는 두 번째 인수를 아래와 같이 직접 입력했는데 하나하나 입력해 주어야 하는 불편함이 있습니다.
=SUMIF(C4:C11,"신촌 아트박스",E4:E11)

다음과 같이 두 번째 인수를 셀 참조로 입력하면 편리합니다.
=SUMIF($C$4:$C$11,D18,$E$4:$E$11)

'신촌 아트박스'를 [D18]셀에 입력하고 수식에서는 [D18]셀을 참조하도록 합니다. 이렇게 하면 D열에 있는 거래처의 이름만 바꾸면, 바꾼이름으로 SUMIF 함수가 작동합니다.

3) 특정 텍스트가 포함된 거래처 실적 구하기

이번에는 전체 거래처에서 특정 텍스트가 포함된 거래처만 골라내서 판매실적을 더해보겠습니다.

=SUMIF(C4:C11,"*아트박스",E4:E11)

두 번째 인수 '*아트박스'에서 '*'은 여러 텍스트를 뜻하는 와일드카드이므로,

거래처명이 아무 텍스트로 시작하고 '아트박스'로 끝나는 곳을 찾으라는 뜻입니다.

따라서 이 수식은 아래 그램과 같이 거래처가

• 신촌 아트박스
• 서초 아트박스

인 곳의 판매금액을 더해서 832,000원을 구해줍니다.

4) 판매금액을 조건으로 합계 구하기

이번에는 판매금액을 조건으로 해서 합계를 구해보겠습니다.

전체 판매실적에서 6만원 이상인 것의 판매금액 합계를 구하려면 다음 수식을 입력합니다.

=SUMIF(E4:E11,">=60000",E4:E11)

• 두번째 인수 '>=60000'에서 '>='는 크거나 같다는 뜻이므로
• 판매실적에서 6만원 이상인 것만 구해서 합계 1,840,000원을 구해줍니다.

위의 경우와 비슷하지만 이번에는 5만원 미만인 판매실적의 합계를 구해보겠습니다.

=SUMIF(E4:E11,"<50000",E4:E11)

• 두번째 인수 '<50000'에서 '<'는 작다는 뜻이므로
• 판매실적에서 5만원보다 작은 것만 구해서 합계 42,000원을 구해줍니다.

※ 참고 : 위 수식에서 >=, < 등 비교 연산자가 사용되었는데 다음과 같이 다양한 방법으로 조건을 설정할 수 있습니다.

• =100 : 100인
• <>100 : 100이 아닌
• >100 : 100보다 큰
• >=100 100보다 크거나 같은(100이상인)
• <100 : 100보다 작은(100미만인)
• <=100 : 100보다 작거나 같은
• <>신촌 아트박스 : '신촌 아트박스'가 아닌

관련 글

• 인쇄
• 전자우편
• Tweet

• 

이것이 좋아요:

좋아하기 가져오는 중...