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

대/소문자를 구분해 값을 참조할 필요가 있다면 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

수식을 잘못 입력하면 오류 값이 표시된다. 오류 값이 표시되면 수식의 신뢰성이 떨어지고 다른 계산에도 영향을 미치기 때문에 오류는 발견즉시 바로 수정을 하는 것이 좋다. 오류를 수정하기 어렵다면 오류 값을 숨겨준다. IFERROR 함수를 사용하여 오류 값을 숨겨보자. 위의 첨부파일을 열면 아래와 같은 표를 확인할 수 있다. [C8:H8] 범위에 #DIV/0! 오류 값이 반환된 것을 확인할 수 있다. 오류 값을 IF, IFERROR, ISERROR 등의 함수를 이용하여 숨겨본다. 오류 값이 발생할 경우 0을 반환하기 위해 [G6] 셀을 선택한다. 다음으로 아래의 수식을 입력하고 [G6] 셀의 채우기 핸들을 [G9] 셀까지 드래그하여 수식을 복사한다. =IFERROR((E6-C6)/C6,0) IFERROR ..

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

함수 마법사를 이용하지 않고 수식 입력 줄이나 셀에서 직접 함수를 구성할 때는 함수의 인수 구성을 기억하기 어려운 경우가 있다. 이때는 함수 설명 풍선 도움말을 참고하면 편리하다. 직접 함수 인수 구성을 확인하면서 함수를 작성하는 방법 및 풍선 도움말이 나타니지 않을 때 도움말을 표시하는 방법에 대해 알아보자. 수식을 작성할 셀에 등호(=)를 입려가고 함수 명을 입력하기 시작하면 다음과 같이 함수 목록이 나타난다. 함수 명령을 쉽게 작성할 수 있도록 도와준다. Tip : 함수 목록에서 원하는 함수를 선택하고 'Tab'키를 누르면 해당 함수가 셀에 입력된다. 함수 명이 일력되면 풍선 도움말로 함수 인수에 대한 내용이 표시된다. 해당 풍선 도움말을 참고해 함수의 구성을 완료한다. 함수의 인수를 입력하려면 '..

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