EXCEL (12) 썸네일형 리스트형 XLOOKUP 함수 - 표에서 값찾기(VLOOKUP 단점 해결) XLOOKUP 함수는 기존 VLOOKUP, HLOOKUP 함수의 몇 가지 단점(찾는 키값이 항상 1열에 있어야 하고, 가로로 된 표는 VLOOKUP 대신 HLOOKUP 사용 등)을 해결하고 사용하기 쉽게 개선된 엑셀의 새로운 찾기 함수입니다. 예를 들어 VLOOKUP 함수로 값을 찾을 때 아래 그림과 같이 찾는 '키값(상품코드)'이 반드시 표의 첫 번째 열에 있어야 합니다. 만약, 찾는 키 값이 첫 번째 열이 아니라 다른 열에 있다면 VLOOKUP 함수로는 값을 찾을 수 없습니다. 이런 경우 INDEX, MATCH 함수를 조합해서 값을 찾든지 해야 하는데 XLOOKUP 함수를 이용하면 이런 단점을 한 번에 해결할 수 있습니다. XLOOKUP함수는 Microsoft 365 또는 엑셀 2021 이상 버전에서.. 엑셀에서 와일드카드 =COUNTIF(B5:B11,"모나미") 엑셀에서 다음과 같이 COUNTIF 함수를 사용하면 [B5:B11] 범위에서 정확히 '노트'인 것만 찾아서 COUNT합니다. 만약 '모나미볼펜', '모나미형광펜'등 유사한 값을 한 번에 찾고 싶다면 와일드카드를 사용하면 됩니다. 와일드카드는 다음 3가지가 있습니다. *(별) 여러 개의 문자를 의미, '모나미*'로 찾으면 '모나미볼펜', '모나미형광펜' 등 '모나미'로 시작하는 텍스트는 모두 찾을 수 있다. ?(물음표) 한개의 문자를 의미, '모나미???'로 찾으면 '모나미볼펜', '모나미형광펜'에서 '모나미'로 시작하고 뒤에 3글자인 '모나미형광펜'만 찾는다. ~(물결표) 와일드카드 해제 문자, 찾는 텍스트에 와일드카드가 포함되어 있을 때 '~'를 붙여서 찾으면 .. 수식에 사용되는 연산자 이번 글에서는 엑셀 수식의 구성, 입력, 편집에 이어서 수식에 사용되는 연산자에 대해 살펴보겠습니다. 엑셀 연산자는 크게 4종류가 있으며 연산자별로 계산이 실행되는 순위가 정해져 있습니다. 산술 연산자 산술 연산자는 6개가 있습니다. 연산자 용도 사용 예 + 더하기 =10+20+30 => 60 - 빼기 =100-30 => 70 *(별표) 곱하기 =10*10 => 100 /(슬래쉬) 나누기 =10/2 => 5 (10을 2로 나눈값인 5가 표시됨) % 백분율 =50% => 0.5 (셀 표시형식이 백분율이면 50%로 표시됨) =50%*10 => 5 (셀 표시형식이 백분율이면 500%로 표시됨) ^(캐럿) 제곱 =5^2 => 25 (5의 제곱인 25가 표시됨) 비교 연산자 비교 연산자는 간단해 보이지만 잘못쓰는.. INDEX 함수 - 범위에서 행과 열을 이용하여 값 찾기 엑셀의 INDEX 함수는 테이블이나 범위에서 행과 열에 해당하는 값을 찾아줍니다. INDEX 함수에는 배열형과 참조형이라는 두 가지 형식이 있습니다. INDEX 함수는 단독으로 쓰이기 보다는 MATCH 함수 등 다른 함수와 결합하여 자주 사용됩니다. INDEX와 MATCH함수를 같이 사용하면 VLOOKUP으로 해결할 수 없는 다중 조건으로 값찾기 등의 문제를 해결할 수 있습니다. 1. INDEX 함수 - 배열형 구문(Syntax) INDEX(array, row_num, [column_num]) 테이블 또는 배열에서 행과 열에 해당하는 값을 구해준다. 인수 : array : 값을 찾을 테이블 또는 배열 row_num : 값이 위치한 행번호 column_num : (생략가능)값이 위치한 열번호 사용 예 1).. MATCH 함수 - 범위에서 값의 위치 찾기 엑셀의 MATCH 함수는 지정된 범위에서 찾고자 하는 값의 위치를 반환합니다. 단순히 위치만 찾아 주므로 위치만 가지고는 쓸 일이 많이 없습니다. INDEX 함수 등 다른 함수와 함께 사용해서 성적 등급을 계산하는 등 응용해서 많이 사용되므로 함수의 구조를 잘 이해해 두면 복잡한 문제 해결 시 많은 도움이 됩니다. 구문(Syntax) MATCH MATCH(lookup_value, lookup_array, [match_type]) 지정된 범위에서 찾고자 하는 값의 위치를 반환한다. 인수 : lookup_value : 찾고자 하는 값 lookup_array : 값을 찾을 범위 match_type : 일치 유형 1 또는 생략 : lookup_value와 일치하거나 작은 것을 찾음, lookup_array 는 .. VLOOKUP으로 안될때 INDEX, MATCH 함수 사용하기 다음과 같은 표에서 상품코드 'A005'에 해당하는 상품 '마우스패드'를 찾아야 한다면 어떻게 해야 할까요? 찾는 키값이 첫 번째 열이 아니라 다른 열에 있기 때문에 값을 찾을 때 자주 쓰는 VLOOKUP 함수로는 해결이 안됩니다. 대신 엑셀 2021 버전부터 사용할 수 있는 XLOOUP 함수를 사용하거나 INDEX, MATCH 함수를 조합해서 해결해야 합니다. 1. 원리 이해하기 1) INDEX 함수로 값 찾기 INDEX, MATCH 함수를 조합해서 값을 찾기 전에 원리 이해를 위해 INDEX 함수만 사용해서 상품명 '마우스패드'를 찾아 보겠습니다. INDEX 함수는 값이 있는 행과 열의 위치를 지정하면 값을 찾아줍니다. [B16] 셀에 다음 수식을 입력합니다. =INDEX(B5:B11,5,1) 다음과.. 엑셀 피벗테이블 디자인(스타일 적용/수정/새로 만들기) 엑셀의 피벗테이블 "디자인" 메뉴의 "피벗 테이블 스타일"에서는 피벗테이블의 내용은 손대지 않고 원하는 대로 스타일을 다양하게 바꿀 수 있다. 만약 기존의 스타일이 마음에 들지 않을 경우에는 스타일을 수정하거나 새로 만들 수 도 있다. 피벗테이블 디자인 메뉴 피벗테이블 "디자인" 메뉴는 아래와 같이 피벗테이블의 아무 곳이나 클릭해야 리본메뉴에 나타난다. 피벗테이블이 아닌 곳을 클릭하면 "디자인"메뉴가 나타나지 않는다. 피벗 테이블 스타일 적용/수정/새로만들기 "피벗 테이블 스타일" 메뉴에서 7개의 스타일이 기본으로 보여지는데, 오른쪽 아래의 버튼을 클릭하면 아래와 같이 수십 개의 스타일이 나타난다. 원하는 스타일을 적용할 수 있다. 기존 스타일을 수정하거나 추가도 가능하다. 1) 기존에 만들어진 스타일 .. 엑셀 피벗테이블 디자인(압축형식,개요형식,테이블형식으로 표시) 이번 강에서는 엑셀 피벗테이블 디자인메뉴에서 "보고서 레이아웃"을 조정하는 방법을 알아보자. 보고서 레이아웃은 압축형식,개요형식,테이블형식으로 조정할 수 있고, 그룹 항목의 레이블을 반복하거나 그룹 항목 사이에 빈 행을 삽입할 수도 있다. 피벗테이블 디자인 메뉴 피벗테이블 "디자인" 메뉴는 아래와 같이 피벗테이블의 아무 곳이나 클릭해야 리본메뉴에 나타난다. 피벗테이블이 아닌 곳을 클릭하면 "디자인"메뉴가 나타나지 않는다. 보고서 레이아웃 조정 보고서 레이아웃 조정을 해보기 위해 첨부의 파일을 다운로드한 후 아래와 같이 피벗테이블을 만든다. "거래처별판매실적"시트의 Data를 선택한 후 리본메뉴에서 "피벗테이블"을 삽입한다. 행에는 거래처명, 상품분류, 품명을 추가하고 값에는 "합계(a*b)"를 추가한다... 이전 1 2 다음
