블로그 이미지
주로 인재개발원 등의 사이버학습을 정리, 요약하는 상시학습 블로그입니다. 깨비형
« 2017/10 »
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31        

Archive»

체험 블로그 마케팅 서비스 OLPOST

Category»

Notice»

Statistics Graph

 
 


COLUMN 함수는 열 값을 반환하는 함수입니다.


앞 시간에 배운 ROW 함수가 행 값을 반환하는 대신 COLUMN 함수는 열 값을 반환하기 때문에 두 함수는 짝을 이룬다고 보면 되겠죠.


그런데 활용하는 분야는 조금 다릅니다.


주로 한 셀에 있는 내용을 여러 칸에 나눠 입력하는 데 사용되는데요,

대표적인 것이 단위가 각각 한 칸씩 있는 세금계산서입니다.


우선 개념부터 이해하고 가겠습니다.


아래 B4셀과 B5셀에 입력된 내용은 각각 =COLUMN()과 =COLUMN(E2)입니다.

함수뒤에 () <--- 이 표시는 해당 셀을 말하는 것이죠.

B4셀에 있는 수식은 B4셀의 열 값을 반환하라는 것이죠. B열 이므로 2가 되겠죠.

반면 B5에 있는 수식은 E2셀의 열 값을 가져오라는 것이죠, E열은 5가 되겠죠.




이제 본격적으로 실습으로 들어가겠습니다.

예제파일은 아래와 같이 세금계산서와 업체데이터 두 시트로 이루어져 있습니다.

여기서 주의할 점은 업체데이터 중 사업자등록번호는 하이픈(-)까지 입력해야 한다는 점입니다.

하이픈(-)을 생략하면 세금계산서에 빈칸이 생깁니다.(직접 해보시면 이해가실 겁니다.)





참고로 이 파일은 제가 직접 작성한 것이 아니고, 인터넷 검색을 통해 가져온 것을 가지고 내용을 수정한 것임을 먼저 알려드립니다.




그럼 지금부터 수식 설명을 하겠습니다.


F4:Q4 이 범위에 있는 사업자등록번호가 한칸에 한 숫자 또는 하이픈(-)으로 들어갑니다.

가져올 데이터는 업체데이터 시트에 있는 C2셀의 데이터가 되겠습니다.


들어간 수식은 아래와 같습니다.


=MID(업체데이터!$C$2,COLUMN()-5,1)


조금 복잡해 보이죠. 원리를 이해하면 아주 쉽습니다.


우선 사업자등록번호는 하이픈(-)을 포함하면 모두 12자리로 이루어져 있습니다.

그래서 MID 함수를 이용해서 사업자등록번호를 자리수에 맞게 가져오려고 합니다.


위 수식은 업체데이터 C2셀에 있는 데이터를 한개씩 가져오라는 것은 알겠는데, COLUMN()-5가 무슨 뜻인지만 알면 되겠죠.


COLUMN()의 값은 현재 셀의 주소가 F4셀이므로 6이 되겠죠. 그런데 맨 처음 글자를 가져와야 하기 때문에 -5를 해줍니다. 그러면 F4셀의 수식은 아래와 같은 모양이 되겠죠.


=MID(업체데이터!$C$2,1,1)


그렇게 입력했으면 채우기 핸들을 이용해서 Q4셀까지 수식을 복사해줍니다.


G4에서 위 수식은 COLUMN()-5의 값이 2가 됩니다. 그래서 아래와 같은 식이 되겠죠.


=MID(업체데이터!$C$2,2,1)


같은 방법으로 H4셀부터 Q4셀까지 3번째부터 끝번째까지 한 글자만 가져오는 수식이 되는 거죠. 이해가 가셨나요?



다음은 공급받는자가 있는 오른편의 사업자등록번호를 입력해보겠습니다.



앞서 만든 수식에서 달라져야 할 부분은 두 곳 밖에 없죠.

가져올 데이터 주소가 업체데이터 시트의 C9셀로 바뀌는 것과COLUMN()에서 얼마를 빼줘야 하는 것만 수정해주면 되죠.


맨 첫 셀이 있는 곳이 V열이죠. 알파벳 순서대로 세면 22번째 열이 됩니다.

COLUMN()-21 하면 되겠죠.


그래서 들어간 수식은 다음과 같습니다. 물론 채우기 핸들로 오른쪽으로 복사해주면 되겠죠.


=MID(업체데이터!$C$2,COLUMN()-21,1)



다음은 F14셀에 들어갈 공란수를 구하는 방법입니다.


공급가액에 들어갈 수 있는 숫자는 백억까지죠. 숫자로 표시하면 11자리까지 표시가 가능합니다.

그런데 공란수란 비어있는 칸이 몇 개인지를 말하는거죠.


공급가액에 들어갈 숫자는 아래 U16:Z19 입력된 가액을 모두 더해야 합니다.

SUM 함수를 사용한 이유입니다.

11자리에서 공급가액을 모두 더한 값의 길이를 빼면 공란수가 나오겠죠.

그래서 F14셀에는 다음과 같은 수식이 들어갑니다.


=11-LEN(SUM(U16:Z19)


U16:Z19의 범위는 U16:Z16으로 표기해도 되고, 그림과 같이 절대셀이 아닌 상대셀로 표기해도 됩니다.




다음은 공급가액을 입력할 차례입니다.

공급가액은 아까도 말했듯이 U16:Z19까지 모두 합한 금액을 각 자리수에 넣어줘야 한다고 했죠.

그래서 일단 들어갈 데이터는 SUM($U$16:$Z419)이 되는 것은 이해가시죠. 또 오른쪽을 끌어서 복사할 예정이므로 절대셀로 지정해 주어야겠죠.

다음은 모두 합한 금액을 11자리로 지정하기 위해 TEXT함수를 사용하겠습니다.


TEXT(값,"???????????") : 물음표(?)의 갯수만큼의 자리수 데이터로 인식하라는 뜻입니다.

TEXT(값, "#,###") : 수치 값을 천 단위마다 구둣점(,)을 찍어라는 뜻입니다.


따라서 위 방법대로 하면 TEXT(SUM($U$16:$Z419),"???????????")의 의미는 공급가액을 모두 합한 숫자를 11자리 문자로 인식하라는 것이죠.

다음 COLUMN()-7은 더 이상 설명하지 않아도 이해할 수 있겠죠.


=MID(TEXT(SUM($U$16:$Z$19),"???????????"),COLUMN()-7,1)




세액에 들어갈 수식도 마찬가지로 작성하면 되겠죠.

수식은 아래와 같습니다.


=MID(TEXT(SUM($AA$16:$AE$19),"??????????"),COLUMN()-18,1)




이제 마지막입니다.


B21셀에 합계금액을 구하는 수식을 입력할 예정인데요.

이건 너무 쉽죠.


U16셀에서 AE19셀까지 모두 더하면 되겠죠.

따라서 수식은 다음과 같습니다.


=SUM(U16:AE19)




여기서 잠깐 팁 한가지.

B14셀부터 E14셀까지 날짜 입력하는 곳 있죠.

오늘을 기준으로 날짜가 자동으로 들어가게 했는데요.


사용한 수식은 각 각  =YEAR(TODAY()), =MONTH(TODAY())=DAY(TODAY()) 입니다.


예제 파일을 첨부합니다. 



tax-data.xlsx



도움이 되셨다면 공감 꾸~욱 부탁드립니다!!!



저작자 표시 동일 조건 변경 허락
신고

1. 참조 표에서 찾기


  1) HLOOKUP 함수

○ 개요 : 테이블의 첫 행에 있는 값 또는 값의 배열을 검색한 다음 테이블이나 배열에서 지정한 행으로부터 같은 열에 있는 값을 반환하는 함수 

○ 구문 =HLOOKUP(Lookup_value, Table_array, Row_index_num, [Range_lookup])

○ 인수

▷Ÿ Lookup_value : 필수 요소, 테이블의 첫 행에서 찾을 값, 참조, 텍스트 문자열

▷Ÿ Table_array : 필수 요소, 데이터를 찾을 정보 테이블, 범위에 대한 참조나 범위 이름을 사용

▷Ÿ Row_index_num : 필수 요소, 반환하려는 값이 있는 Table_array의 행 번호

▷Ÿ Range_lookup : HLOOKUP을 사용하여 정확하게 일치하는 값을 찾을지 아니면 근사값을 찾을지를 지정하는 논리값

- TRUE, 값을 생략 → 근사값 반환

- FALSE → 정확하게 일치하는 값 반환


  2) VLOOKUP 함수

○ 개요 : 테이블의 첫 열에 있는 값 또는 값의 배열을 검색한 다음 테이블이나 배열에서 지정한 열으로부터 같은 행에 있는 값을 반환하는 함수 

○ 구문 =VLOOKUP(Lookup_value, Table_array, Col_index_num, [Range_lookup])

○ 인수

▷ Lookup_value : 필수 요소, 테이블의 첫 열에서 찾을 값, 참조, 텍스트 문자열

▷ Table_array : 필수 요소, 데이터를 찾을 정보 테이블, 범위에 대한 참조나 범위 이름을 사용

▷ Col_index_num : 필수 요소, 반환해야 하는 값이 있는 Table_array 인수의 열 번호

▷ Range_lookup : VLOOKUP을 사용하여 정확하게 일치하는 값을 찾을지 아니면 근사값을 찾을지를 지정하는 논리값

- TRUE, 값을 생략 → 근사값 반환

- FALSE → 일치하는 값 반환


<사용 예시>

[B5]셀의 경우 : [A5]셀 값을 표의 첫 열인 [H5:H9]셀에서 검색

 [H5] → 동일 행의 두 번째 열 값인 '아현점'표시

[F4]셀의 경우 : 표 범위인 [K4:O9]셀 범위에서 6행과 3열이 교차하는 위치

→ [M9]셀 값인 '40000' 표시



2. 행 · 열의 위치를 검색하여 찾기


  1) INDEX 함수

○ 개요 :  테이블이나 범위에서 값 또는 값에 대한 참조를 반환하는 함수

○ 형식 : 배열형과 참조형

(1) 배열형 : 행과 열 번호 인덱스로 선택한 배열이나 테이블 요소의 값을 반환

▷ 구문 =INDEX(Array, Row_num, [Column_num])

▷ 인수

- Array : 필수 요소, 배열 상수나 셀 범위

-Ÿ Row_num : 값을 반환할 배열의 행 선택

-Ÿ Column_num : 값을 반환할 배열의 열 선택

-Ÿ Row_num 인수와 Column_num 인수는 둘 중 하나는 반드시 지정해야함

(2) 참조형 : 특정 행과 열이 교차되는 위치의 셀 참조를 반환하며, 참조가 인접하지 않은 영역으로 이루어진 경우에는 찾아볼 영역 선택 가능

▷ 구문 =INDEX(Reference, Row_num, [Column_num], [Area_num])

▷ 인수

-Ÿ Reference : 필수 요소, 한 개 이상의 셀 범위에 대한 참조

-Ÿ Row_num : 참조 범위에서 참조를 반환할 행 번호

-Ÿ Column_num : 참조 범위에서 참조를 반환할 열 번호

-Ÿ Area_num : 선택 요소, row_num과 column_num이 교차하는 셀을 반환할 참조 범위 선택


  2) MATCH 함수

○ 개요 : 셀 범위에서 지정된 항목을 검색한 다음 범위 내에서 해당 항목의 상대 위치를 반환하는 함수

○ 구문=MATCH(Lookup_value, Lookup_array, [Match_type])

○ 인수

▷Ÿ Lookup_value :  필수 요소, Lookup_array에서 찾으려는 값

            숫자, 텍스트, 논리값 등의 값 또는 숫자, 텍스트, 논리값에 대한 셀 참조

▷Ÿ Lookup_array : 필수 요소, 검색할 셀 범위

▷Ÿ Match_type 

- 선택 요소, 숫자 -1, 0, 1

- Lookup_array의 값을 사용하여 Lookup_value를 찾는 방법을 지정하는 숫자

- 기본값은 1

Match_type

동작

1 또는 생략

Lookup_value보다 작거나 같은 값 중에서 최대값 검색

Lookup_array 인수 값은 오름차순으로 지정

0

Lookup_value와 같은 첫째 값 검색

Lookup_array 인수 값은 임의의 순서로 지정 가능

-1

Lookup_value보다 크거나 같은 값 중 최소값 검색

Lookup_array 인수 값은 내림차순으로 지정


<사용 예시>

- [D4]셀의 경우 : [C4]셀의 값을 [J4:J9]셀 범위에서 검색 → [J9]

 → 6번째에 위치 → 6

- [E4]셀의 경우 : [B4]셀의 값을 [K3:O3]셀 범위에서 검색 → [M3]

 → 3번째에 위치 → 3

- [F4]셀의 경우 : 표 범위인 [K4:O9]셀 범위에서 6행과 3열이 교차하는 위치

 → [M9]셀 값인 '40000' 표시



3. 행 · 열 정보를 이용해 배열로 찾기


  1) COLUMN 함수

○ 개요 : 참조의 열 번호를 반환하는 함수

○ 구문 =COLUMN([Reference])

○ 인수

▷Ÿ Reference : 선택 요소로 열 번호를 반환하려는 셀 또는 셀 범위

                     생략하면 현재 셀의 열 번호를 반환

       

  2) ROW 함수

○ 개요 : 참조의 행 번호를 반환하는 함수

○ 구문 =ROW([Reference])

○ 인수

▷Ÿ Reference : 선택 요소로 행 번호를 반환하려는 셀 또는 셀 범위

                     생략하면 현재 셀의 행 번호를 반환


  3) LOOKUP 함수

○ 개요 : 한 개의 행이나 한 개의 열로 이루어진 범위에서 값 또는 값에 대한 참조를 반환하는 함수

○ 형식 : 벡터형과 배열형

(1) 벡터형 : 한 개의 행이나 한 개의 열로 이루어진 범위에서 값을 찾고, 한 개의 행이나 한 개의 열로 이루어진 두 번째 범위의 같은 위치에서 값을 반환

▷ 구문 =LOOKUP(Lookup_value, Lookup_vector, [Result_vector])

▷ 인수

-Ÿ Lookup_value : 필수 요소, LOOKUP 함수를 사용하여 첫 번째 벡터에서 검색하려는 값

                        숫자, 텍스트, 논리값, 값을 참조하는 이름이나 값

-Ÿ Lookup_vector : 필수 요소, 행이나 열을 한 개만 포함하는 범위로 텍스트, 숫자 또는 논리값

- Result_vector : 선택 요소, 행이나 열을 한 개만 포함하는 범위, Lookup_vector와 크기가 같아야 함

(2) 배열형 : 배열의 첫 번째 행이나 열에서 지정된 값을 찾고, 배열의 마지막 행이나 열의 같은 위치에서 값을 반환, 찾으려는 값이 배열의 첫 번째 행이나 열에 있는 경우 사용(일반적으로 LOOKUP함수의 배열형 대신 HLOOKUP 또는 VLOOKUP 함수를 사용)

▷ 구문 =LOOKUP(Lookup_value, Array)

▷ 인수

-Ÿ Lookup_value : 필수 요소, 배열에서 찾으려는 값으로 숫자, 텍스트, 논리값, 값을 참조하는 이름이나 참조

-Ÿ Array : 필수 요소, Lookup_value와 비교할 텍스트, 숫자 또는 논리값을 포함하는 셀 범위


<사용 예시>

- [A4]셀의 경우 : [B4]셀의 값을 사원명부 성명란[B4:B15]셀 범위에서 검색

                  → [B4]셀확인 → 일련번호 확인 → 1001

- [C4~E4]셀의 경우 : [A4]셀의 값을  사원명부[A4:H15]셀 범위에서 검색

 → 해당셀이 있는 열번호를 확인하여 열번호(3, 4, 5)만큼 오른쪽에 있는 데이터를 가져옴  인사팀, 사원, 2

- [B7~D7]셀의 경우 : 위와 같은 방법으로 데이터 검색

 → 해당셀이 있는 열번호를 확인하여 열번호+3 만큼 오른쪽에 있는 데이터를 가져옴  1200000, -, 80000




저작자 표시 동일 조건 변경 허락
신고