다둥이네(상똥/예뚱/영뚱/잼뚱)~♡

VLOOKUP 함수를 사욜할 때는 꼭 지켜야 할 규칙이 있다. 참조할 값이 찾을 값의 오른쪽에 있어야 한다는 것으로 이 규칙이 지켜지지 않으면 값을 참조해 올 수가 없다. VLOOKUP 함수에 대한 규칙을 이해하고 중첩하여 사용할 수 있는 INDEX, MATCH 함수의 사용방법에 대해서 확인해 보자. 위의 첨부파일을 열면 아래와 같은 표를 확인할 수 있다. [A1:C17] 범위의 표에서 [F2:F4] 범위와 같이 매출 순위 1, 2, 3의 실적을 집게한 경우, 해당 실적 지역을 [G2:G4] 범위에 참조하는 수식을 작성한다. VLOOKUP 함수를 이용해 값을 참조해보자. [G2] 셀을 선택하고 다음 수식을 입력하면 화면과 같이 #N/A 오류가 발생하게 된다. =VLOOKUP(F2,$A$2:$C$16,FA..

IT정보/엑셀 팁(Excel Tip) 2021. 12. 3. 07:00

참조할 값이 여러 개의 표에 나누어 기록된 경우에는 VLOOKUP 함수의 두 번째 인수를 지정할 때 IF 함수를 사용해 참조할 표 범위를 조건에 따라 분리할 수 있다. 여러 개의 표에서 필요한 값을 참조하는 수식을 작성하는 방법에 대해 알아보자. 위의 첨부파일을 열면 아래와 같은 표를 확인할 수 있다. 제품이 입출고되는 창고 A와 B로 나누어져 있을 때, 이 두 개의 표를 참조하여 제품별 재고 값을 왼쪽 표ㅗ 통합하는 수식을 작성해 보자. 값을 참조할 표가 두 개이기 때문에 IF함수를 VLOOPKUP 함수 안에 중첩해서 사용한다. [B3] 셀을 선택하고 다음 수식을 입력한다. 채우기 핸들을 7행까지 드래그해서 수식을 복사한다. =VLOOKUP($A3,IF(B$2="창고A",$E$3:$H$5,$E$9:$H..

IT정보/엑셀 팁(Excel Tip) 2021. 12. 2. 07:00

대/소문자를 구분해 값을 참조할 필요가 있다면 EXACT 함수와 INDEX, MATCH 함수를 중첩해서 사용하는 배열수식을 작성해야 한다. 배열수식이라고 해도 이전의 INDEX, MATCH 함수 구성과 같기 때문에 쉽게 사용할 수 있다. EXACT함수는 두 텍스트 값을 비교해 같으면 TRUE, 틀리면 FLASE를 반환한다. 특히 영어 대/소문자를 구분해 비교할 수 있다. 함수의 사용법은 다음과 같다. =EXACT(텍스트1, 텍스트2) 위의 첨부파일을 열면 아래와 같은 표를 확인할 수 있다. [F1] 셀에 입력된 제품 코드를 이용해 왼쪽 표의 [C2:C11] 범위의 가격을 참조해 오는 수식을 입력한다. 이때 주의할 점은 [A2:A11] 범위의 제품 코드에는 대/소문자가 구분되어 입력되었다는 것이다. [F2..

IT정보/엑셀 팁(Excel Tip) 2021. 12. 1. 07:00

특정 범위의 데이터를 정렬할 필요가 있다면 [데이터] - [정렬 및 필터] 그룹 - [오름차순 정렬] 이나 [내림차순정렬]을 이용한다. 하지만 정렬 기능은 동일한 위치에 결과를 반환하기 때문에 데이터 표시 순서를 다시 원래대로 되돌려야 할 때는 불편하다. 수식을 사용하면 다른 위치에 값을 정렬하여 표시할 수 있어야 편리하다. 위의 첨부파일을 열면 아래와 같은 표를 확인할 수 있다. [A2:A12] 범위의 제품명을 수식을 이용해 [C2:C12] 범위에 오름차순으로 정렬해 보자. 오름차순으로 값을 정렬하기 위해 [C2]셀을 선택하고 아래의 수식을 작성한 다음 [Ctrl + Shift + Enter]을 입력한다. [C2] 셀의 채우기 핸들을 [C12] 셀까지 드래그해서 수식을 복사한다. =INDEX($A$2:..

IT정보/엑셀 팁(Excel Tip) 2021. 11. 30. 07:07

표에서 특정 값의 위치를 알고 싶다면 MATCH함수를 사용한다. MATCH 함수는 활용도가 매우 높은 함수로 LOOKUP 계열 함수인 INDEX, OFFSET, INDIRECT 등의 참조 함수와 중첩하여 사용한다. MATCH함수를 사용하는 방법에 대해 알아보자. MATCH 함수는 찾을 값이 지정한 범위 내에서 몇 번째에 있는지 알려주는 함수로 사용법은 다음과 같다. =MATCH(찾을 값, 범위, 찾기 옵션) ① 찾을 값 : 범위에서 찾을 값을 의미한다. ② 범위 : 찾을 값을 포함하는 데이터 범위를 하나의 열 또는 행 데이터 범위만 가능하다. ③ 찾기 옵션 : 찾을 값을 범위에서 찾을 때 그 방법을 지정하는 옵션이다. ○ 1 또는 생략 : 범위의 값이 오름차순으로 정렬되고 있다고 가정하고 값을 찾는다. ..

IT정보/엑셀 팁(Excel Tip) 2021. 11. 29. 07:00

특정 범위를 참조해 작업할 때, 데이터가 추가되거나 삭제된다면 변경된 범위를 다시 참조하기 위해 참조 위치를 수정해야 한다. 이때 표를 엑셀 표로 변환해 구조적 참조를 사용하는 방법이 있기는 하지만 엑셀 표를 변환할 수 없거나 변환시 하위 버전과의 호환성에 문제가 있다면 동적 범위 참조 수식을 사용한다. OFFSET, COUNT 계열 함수를 사용하여 동적 범위 참조 수식을 작성하는 방법에 대해 알아보자. OFFSET 함수는 시작 셀에서 원하는 행, 열 방향 개수만큼 위치를 이동한 후 원하는 행, 열 방향 개수 만큼의 범위를 참조한다. 함수의 사용법은 다음과 같다. = OFFSET(시작 셀, 행 방향 이동 셀 개수, 열 방향 이동 셀 개수, 행 방향 포함 셀 개수, 열 방향 포함 셀 개수) ① 시작 셀 :..

IT정보/엑셀 팁(Excel Tip) 2021. 11. 28. 07:00

VLOOKUP 함수는 조건에 맞는 값 하나를 참조하거나 트정 구간별 값의 위치를 참조한다. 가간별 값 참조 방법은 여러 가지 실무에서 활용할 수 있으므로 조건 설정 등을 반드시 이해하는 것이 좋다. 위의 첨부파일을 열면 아래와 같은 표를 확인할 수 있다. [F5:F13] 범위의 보너스 금액을 계산할 때 [D5:D13] 범위의 근속년수를 [H5:H8] 범위에서 찾아 가능한 보너스 비율 값을 참조해 보자. 보너스 금액을 계산하기 위해 [F5]셀을 선택하고 아래의 수식을 입력한다. [F5] 셀의 채우기 핸들을 [F13] 셀까지 드래그해서 수식을 복사한다. =E5*VLOOKUP(D5,$I$5:$J$8,2,TRUE) 위의 수식은 보너스 금액을 계산하기 위해 오른쪽 표에서 근속년수별 보너스 비율을 참조해야 한다. ..

IT정보/엑셀 팁(Excel Tip) 2021. 11. 27. 07:00

표에서 특정 조건에 맞는 제품을 선별해 별도로 정리하는 일은 실무에서 자주 하는 업무 중의 하나일 것이다. 이 업무에서 가장 중요한 작업은 추출해야 하는 데이터의 조건 수식을 작성하는 것으로 IF, AND, OR 등의 함수를 사용하면 편리하게 작업할 수 있다. 표에서 특정 조건에 맞는 데이터를 IF, AND, OR 함수로 표시하고, 조건에 맞는 데이터만 자동 필터로 축출해 보자. 위의 첨부파일을 열면 아래와 같은 표를 확인할 수 있다. 새로 발주할 제품을 확인하기 위해서 [H2:H16] 범위에 다음 조건을 수식으로 작성한다. 조건에 맞는 데이터만 복사해 보자. - 재고가 20개 미만인 제품 - 단종이 아닌 제품 조건을 수식으로 작성하기 위해 [H2] 셀을 선택하고 아래의 수식을 입력한다. [H2] 셀의 ..

IT정보/엑셀 팁(Excel Tip) 2021. 11. 26. 07:00