어떤 조건이 참일때 최대값과 최소값 찾기

E4셀에  =MIN(IF($A$1:$A$10=D4,$B$1:$B$10)) 입력한 후 Ctrl + Shift + Enter 해서 배열함수를 완성한다.

F4셀에  =MAX(IF($A$1:$A$10=D4,$B$1:$B$10)) 입력한 후 Ctrl + Shift + Enter 해서 배열함수를 완성한다.

다른 방법으로

MINIFS 함수 와 MAXIFS함수가 있습니다.

MINIFS 함수는 주어진 조건 집합에 맞는 셀에서 최소값을 반환합니다.

minifs(①최소값을 구할 범위, ②조건1의 범위, ③조건1, ④조건2의 범위, ⑤조건2... ...)

MAXIFS 함수는 주어진 조건에 맞는 셀에서 최대값을 반환합니다.

maxifs(①최대값을 구할 범위, ②조건1의 범위, ③조건1, ④조건2의 범위, ⑤조건2... ...)

Vlookup

VLOOKUP 함수는 Excel에서 가장 많이 사용되는 함수 중 하나입니다. 이 페이지에는 따라하기 쉬운 VLOOKUP 예제가 많이 포함되어 있습니다.

정확히 일치하여 찾기

대부분의 경우 Excel에서 VLOOKUP 함수를 사용할 때 정확히 일치하는 항목을 찾고 있습니다. VLOOKUP 함수의 인수를 살펴 보겠습니다.

1. 아래의 VLOOKUP 함수는 빨간색 테이블 (두 번째 인수)의 가장 왼쪽 열에서 값 53 (첫 번째 인수)을 찾습니다.

2. 값 4 (세 번째 인수)는 VLOOKUP 함수에 빨간색 테이블의 네 번째 열에서 같은 행의 값을 반환하도록 지시합니다.

참고 : 부울 FALSE (네 번째 인수)는 VLOOKUP 함수에 정확한 일치를 반환하도록 지시합니다. VLOOKUP 함수가 첫 번째 열에서 값 53을 찾을 수없는 경우 # N / A 오류를 반환합니다.

3. 여기 또 다른 예가 있습니다. 급여를 반환하는 대신 아래의 VLOOKUP 함수는 ID 79의 이름 "율곡"을 반환합니다 (세 번째 인수는 3으로 설정 됨).

근사 일치 모드

근사 일치 모드에서 VLOOKUP 함수의 예를 살펴 보겠습니다 (네 번째 인수가 TRUE로 설정 됨).

1. 아래의 VLOOKUP 함수는 빨간색 테이블 (두 번째 인수)의 가장 왼쪽 열에서 85 (첫 번째 인수) 값을 찾습니다. 한 가지 문제가 있습니다. 첫 번째 열에는 값 85가 없습니다.

2. 다행히도 부울 TRUE (네 번째 인수)는 VLOOKUP 함수에 대략적인 일치를 반환하도록 지시합니다. VLOOKUP 함수가 첫 번째 열에서 값 85를 찾을 수없는 경우 85보다 작은 가장 큰 값을 반환합니다.이 예에서는 값이 80이됩니다.

3. 값 2 (세 번째 인수)는 VLOOKUP 함수에 빨간색 테이블의 두 번째 열에서 같은 행의 값을 반환하도록 지시합니다.

참고 : 근사 일치 모드에서 VLOOKUP 함수를 사용하는 경우 항상 빨간색 테이블의 가장 왼쪽 열을 오름차순으로 정렬합니다 (네 번째 인수는 TRUE로 설정 됨).

Vlookup은 오른쪽 값을 찾습니다.

VLOOKUP 함수는 항상 테이블의 가장 왼쪽 열에서 값을 조회하고 오른쪽 열에서 해당 값을 반환합니다.

1. 예를 들어 아래의 VLOOKUP 함수는 성을 조회하고 이름을 반환합니다.

2. 열 인덱스 번호 (세 번째 인수)를 3으로 변경하면 VLOOKUP 함수는 성을 조회하고 급여를 반환합니다.

참고 :이 예에서 VLOOKUP 함수는 이름을 조회하고 ID를 반환 할 수 없습니다. VLOOKUP 함수는 오른쪽으로 만 보입니다. 걱정하지 마세요. Excel에서 INDEX 및 MATCH를 사용하여 왼쪽 조회를 수행 할 수 있습니다.

첫번째 일치

테이블의 가장 왼쪽 열에 중복 항목이있는 경우 VLOOKUP 함수는 첫 번째 인스턴스와 일치합니다. 예를 들어 아래의 VLOOKUP 함수를 살펴보십시오.

설명 : VLOOKUP 함수는 이순신, 이율곡이 아닌 이성계의 급여를 반환합니다.

Vlookup은 대소문자를 구분하지 않습니다.

Excel의 VLOOKUP 함수는 대 / 소문자를 구분하지 않는 조회를 수행합니다. 예를 들어 아래의 VLOOKUP 함수는 테이블의 가장 왼쪽 열에서 MIA (셀 G2)를 조회합니다.

설명 : VLOOKUP 함수는 대소 문자를 구분하지 않으므로 MIA 또는 Mia 또는 mia 또는 miA 등을 조회합니다. 결과적으로 VLOOKUP 함수는 Mia Clark (첫 번째 인스턴스)의 급여를 반환합니다. Excel에서 INDEX, MATCH 및 EXACT를 사용하여 대 / 소문자 구분 조회를 수행합니다.

다중 조건

여러 기준에 따라 값을 조회 하시겠습니까? Excel에서 INDEX 및 MATCH를 사용하여 2 열 조회를 수행합니다.

참고 : 위의 배열 수식은 James Anderson이 아니라 James Smith가 아닌 James Clark의 급여를 조회합니다.

#N/A error

VLOOKUP 함수가 일치하는 항목을 찾을 수 없으면 # N / A 오류를 반환합니다.

1. 예를 들어 아래의 VLOOKUP 함수는 가장 왼쪽 열에서 값 28을 찾을 수 없습니다.

2. 원하는 경우 IFNA 함수를 사용하여 # N / A 오류를 친숙한 메시지로 바꿀 수 있습니다.

참고 : IFNA 함수는 Excel 2013에서 도입되었습니다. Excel 2010 또는 Excel 2007을 사용하는 경우 IFNA를 IFERROR로 바꾸면됩니다. IFERROR 함수는 다른 오류도 포착합니다. 예를 들어 #NAME? 실수로 VLOOKUP이라는 단어를 잘못 입력하면 오류가 발생합니다.

다중 Lookup Table

Excel에서 VLOOKUP 함수를 사용하면 조회 테이블이 여러 개있을 수 있습니다. IF 함수를 사용하여 조건이 충족되는지 확인하고 TRUE 인 경우 하나의 룩업 테이블을 반환하고 FALSE 인 경우 다른 룩업 테이블을 반환 할 수 있습니다.

1. 두 개의 명명 된 범위 (Table1 및 Table2)를 만듭니다.

2. 셀 E4를 선택하고 아래 표시된 VLOOKUP 함수를 입력합니다.

설명 : 보너스는 시장 (영국 또는 미국)과 판매량에 따라 다릅니다. VLOOKUP 함수의 두 번째 인수가 트릭을 수행합니다. UK 인 경우 VLOOKUP 함수는 Table1을 사용하고 USA 인 경우 VLOOKUP 함수는 Table2를 사용합니다. VLOOKUP 함수의 네 번째 인수를 TRUE로 설정하여 대략적인 일치를 반환합니다.

3. Enter를 누르십시오.

4. E4 셀을 선택하고 E4 셀의 오른쪽 아래 모서리를 클릭 한 다음 E10 셀로 끕니다.

참고 : 예를 들어 Walker는 $ 1,500의 보너스를받습니다. 명명 된 범위를 사용하고 있기 때문에 셀 참조에 대해 걱정하지 않고이 VLOOKUP 함수를 다른 셀에 쉽게 복사 할 수 있습니다.

Index & Match 함수

VLOOKUP을 사용하는 대신 INDEX 및 MATCH를 사용하십시오. 고급 조회를 수행하려면 INDEX 및 MATCH가 필요합니다. Excel에서 제공하는 다른 많은 강력한 수식 중 하나를 보여줍니다.

Xlookup

Excel 365가있는 경우 VLOOKUP 대신 XLOOKUP을 사용하십시오. XLOOKUP 함수는 사용하기 쉽고 몇 가지 추가 이점이 있습니다.

현재 셀위치에서 전체 열 선택, 전체 행선택하기 단축키

전체 열 선택 : Ctrl + 스페이스바

전체 행 선택 : Shift + 스페이스바

새화면으로 엑셀열기

한개의 엑셀창에 여러파일을 열어서 작업하면 불편한점이 많습니다.

여러개의 창을 열어서 사용하면

왼쪽 모니터와 오른쪽 모니터 각각 펼쳐 놓고 작업하면 작업 능률이 많이 오르죠.

인터넷 검색해서 어떻게 하는지 찾아 봤습니다.

  1. 첫 번째 엑셀 파일을 연다

  2. 작업 표시 줄에서 Shift + 마우스 왼쪽으로 엑셀 아이콘을 누른다

  3. 새로운 엑셀 프로그램이 열린다

수식 복사

수식을 복사하면 Excel은 수식이 복사되는 각 새 셀에 대한 셀 참조를 자동으로 조정합니다.

수식복사

CTRL + c 및 CTRL + v를 사용하여 Excel에서 수식을 복사하여 붙여 넣기 만하면됩니다.

1. 예를 들어 수식을 복사하려면 아래의 A3 셀을 선택하고 CTRL + c를 누릅니다.

2.이 수식을 붙여 넣으려면 B3 셀을 선택하고 CTRL + v를 누릅니다.

3. 수식 입력 줄을 클릭하여 수식이 B 열의 값을 참조하는지 명확하게 확인합니다.

채우기 핸들

Excel의 채우기 핸들을 사용하여 수식을 다른 셀에 빠르게 복사합니다.

1. 예를 들어 아래의 A3 셀을 선택하고 A3 셀 (채우기 핸들)의 오른쪽 아래 모서리를 클릭 한 다음 F3 셀로 드래그합니다.

결과.

채우기 핸들을 사용하여 수식을 열 아래로 빠르게 복사 할 수도 있습니다.

2. 예를 들어, 아래의 C1 셀을 선택하고 C1 셀의 오른쪽 아래 모서리를 클릭 한 다음 C6 셀로 드래그합니다.

결과.

절대참조

절대 참조를 만들어 셀 또는 셀 범위에 대한 참조를 수정합니다. 수식을 복사 할 때 절대 참조는 변경되지 않습니다.

1. 예를 들어, 열 문자와 행 번호 앞에 $ 기호를 배치하여 아래 셀 E2에 대한 참조를 수정합니다.

2. C2 셀을 선택하고 C2 셀의 오른쪽 아래 모서리를 클릭 한 다음 C7 셀로 끕니다.

확인:

설명 : 절대 참조 ($E$2)는 동일하게 유지되고 상대 참조 (B2)는 B3, B4, B5, B6 및 B7로 변경됩니다.

F4키를 누르면 순자적으로 절대참조를 변경할 수 있습니다.

F4한번 누르면 $E$2 -> F4 E$2 -> F4 $E2 -> F4 E2

수식 이동

Excel에서 수식을 이동하려면 잘라 내기 (CTRL + x) 및 붙여 넣기 (CTRL + v)를 사용하면됩니다. Excel 전문가는 다음 트릭을 사용하여 수식을 이동합니다.

1. 수식이 있는 셀을 선택합니다.

2. 선택 테두리 위로 마우스를 이동합니다. 4면 화살표가 나타납니다.

3. 마우스 왼쪽 버튼을 클릭 한 채로 있습니다.

4. 수식을 새 위치로 이동합니다.

5. 마우스 왼쪽 버튼을 놓습니다.

정확한 복사

셀 참조를 변경하지 않고 수식의 정확한 복사본을 만들려면 다음의 간단한 단계를 실행하십시오.

1. 수식 입력 줄을 클릭하고 수식을 선택합니다.

2. CTRL + c를 누르고 Enter를 누르십시오.

3. 다른 셀을 선택하고 CTRL + v를 누릅니다.

결과:

결론 : A3 셀과 B3 셀에는 동일한 수식이 포함되어 있습니다.

복사 꼼수

여러 수식의 정확한 복사본을 만들려면 각 수식에 대해 이전 단계를 반복합니다.

다음과 같은 마술을 사용할 수도 있습니다.

1. 여러 수식을 선택합니다.

2. 모든 등호를 xxx로 바꿉니다.

결과.

3. CTRL + c 및 CTRL + v를 사용하여 텍스트 문자열을 복사하고 붙여 넣습니다.

4. 범위 B6 : B10을 선택하고 CTRL을 누른 상태에서 범위 E6 : E10을 선택한 다음 모든 'xxx'항목을 등호로 바꿉니다 (2 단계의 정반대).

결과.

결론 : 셀 B6과 셀 E6에는 똑같은 수식이 포함되어 있고 셀 B7과 셀 E7에는 똑같은 수식이 포함되어 있습니다.

절대값

Excel의 ABS 함수는 숫자의 절대 값을 반환합니다. 즉, ABS 기능은 음수에서 빼기 기호 (-)를 제거하여 양수로 만듭니다.

1. 예를 들어 아래 B1 셀의 ABS 함수는 음수의 절대 값을 반환합니다.

ABS 기능은 0 (영) 또는 양수에 영향을주지 않습니다.

2. 0의 절대 값은 0입니다.

3. 양수의 절대 값은 동일한 양수입니다.

Excel에서 ABS 기능은 언제 필요합니까?

4. 예를 들어, 월별 예측 오류 (실제 값과 예측 값의 차이)를 계산합니다.

참고 : 이러한 오류의 합 (0)을 살펴보면이 예측 모델은 완벽 해 보이지만 그렇지 않습니다!

5. ABS 기능을 사용하여 매달 절대 오차를 계산하십시오.

6. 시트에 예측 오류를 표시하지 않으려면 SUMPRODUCT 및 ABS를 사용합니다.

7. ABS 기능을 사용하여 각 차이의 절대 값을 계산합니다.

8. 값이 허용 오차 내에 있는지 테스트하려면 IF 함수를 추가합니다.

설명 : 차이가 3보다 작거나 같으면 IF 함수는 1을 반환하고 그렇지 않으면 0을 반환합니다.

E4셀에 2020-01-01이 입력되어 있다고 가정하고

=EDATE(E4,1) 

결과  2020-02-01을 출력합니다.

=EDATE(E4,-1)

결과  2019-12-01을 출력합니다.

설명

지정한 날짜(start_date) 전이나 후의 개월 수를 나타내는 날짜의 일련 번호를 반환합니다. EDATE를 사용하여 발행일과 월과 일이 같은 만기일이나 기한을 계산합니다.

구문

EDATE(start_date, months)

EDATE 함수 구문에는 다음과 같은 인수가 사용됩니다.

• start_date    필수 요소입니다. 시작 날짜입니다. 날짜는 DATE 함수를 사용하거나 다른 수식 또는 함수의 결과로 입력해야 합니다. 예를 들어 2008년 5월 23일에 대해서는 DATE(2008,5,23)을 사용합니다. 날짜를 텍스트로 입력하면 문제가 발생할 수 있습니다.

• months    필수 요소입니다. start_date 전이나 후의 개월 수입니다. 앞으로의 날짜는 양수로, 지나간 날짜는 음수로 표시됩니다.

주의

• 날짜는 계산에 사용할 수 있도록 순차적인 일련 번호로 저장됩니다. 기본적으로 1900년 1월 1일이 일련 번호 1이고, 2008년 1월 1일은 1900년 1월 1일에서 39,448일째 날이므로 일련 번호 39448이 됩니다.

• start_date가 유효한 날짜가 아니면 EDATE에서는 #VALUE! 오류 값이 반환됩니다.

• months가 정수가 아니면 소수점 이하는 무시됩니다.

시간 반올림을 해보자.

2020/12/01 - [엑셀] - 15분 단위로 올림, 반올림 하기

15분 단위로 올림, 반올림하기

1. 15분 단위로 반올림하는 수식을 알아보자.

10:17:18 을 15분 단위로 올림하면 10:30:00 이 된다.

15분은 1시간의 1/4로 0.25에 해당한다.

24시간을 15분으로 나누면 96등분으로 나누어진다.

이 숫자를 시간에 곱하여 올림한 후 

다시 0.25/24를 곱하여 15분단위로 올림한다.

=ROUNDUP(A1*24/0.25,0)*(0.25/24)

2. 15분단위로 반올림하려면

위 수식에서 ROUNDUP 대신 ROUND로 바꾸면 된다.

3. 30분단위로 올림, 반올림하려면

24/0.25대신 24/0.5

=ROUNDUP(A1*24/0.25,0)*(0.25/24)

2020/12/01 - [엑셀] - 시간 반올림

셀이 비어있는 경우

Excel에서 IF 함수와 빈 문자열을 사용하여 셀이 비어 있는지 확인합니다. IF 및 ISBLANK를 사용하여 정확히 동일한 결과를 생성합니다.

공백인 경우

Excel의 IF 함수는 조건이 충족되는지 확인하고 true 인 경우 하나의 값을 반환하고 false 인 경우 다른 값을 반환합니다.

1. 아래의 IF 함수는 입력 값이 빈 문자열 (사이에 아무것도없는 두 개의 큰 따옴표)이면 Yes를 반환하고, 그렇지 않으면 No를 반환합니다.

참고 : 입력 셀에 공백이 있으면 공백으로 보입니다. 그러나이 경우 입력 값은 빈 문자열과 같지 않으며 위의 IF 함수는 No를 반환합니다.

2. IF 및 ISBLANK를 사용하여 정확히 동일한 결과를 생성합니다.

참고 : ISBLANK 함수는 셀이 비어 있으면 TRUE를 반환하고 그렇지 않으면 FALSE를 반환합니다. 입력 셀에 공백이나 빈 문자열을 반환하는 수식이 포함되어 있으면 공백으로 보입니다. 그러나이 경우 입력 셀은 비어 있지 않으며 위의 수식은 No를 반환합니다.

공백이 아닌 경우

Excel에서 <>는 같지 않음을 의미합니다

1. 아래의 IF 함수는 입력 값이 빈 문자열 (사이에 아무것도없는 두 개의 큰 따옴표)과 같지 않으면 입력 값에 2를 곱하고 그렇지 않으면 빈 문자열을 반환합니다.

2. IF, NOT 및 ISBLANK를 사용하여 정확히 동일한 결과를 생성합니다.

빈 셀 강조

Excel에서 조건부 서식을 사용하여 비어있는 셀을 강조 표시 할 수 있습니다.

1. 예를 들어 A1 : H8 범위를 선택합니다.

2. 홈 탭의 스타일 그룹에서 조건부 서식을 클릭합니다.

3. 셀 강조 규칙(H), 기타 규칙(M)...을 클릭합니다.

4. 드롭 다운 목록에서 공백을 선택하고 서식 스타일을 선택한 다음 확인을 클릭합니다.

결과: