엑셀에서 셀의 내용을 편집할 때 Alt+Enter키를 누르면 여러줄로 셀의 내용을 채울 수 있습니다. 이때 Alt+Enter키를 누른 부분에는, 눈에 보이지 않는 줄바꿈 문자, 즉 개행문자(CR-LF)가 들어갑니다.


이 엑셀 셀(Cell)의 개행문자를 검색, 치환하는 방법입니다. 즉, 줄바꿈문자 찾기와 바꾸기입니다.


요점은 줄바꿈 문자를 Alt+10 으로 변환하여 찾기와 바꾸기를 하는 것입니다. Alt+10 이란 키보드의 Alt키를 누르면서, 우측 키패드에 있는 1과 0을 연달아 눌러주는 것입니다.


(1) 셀 속의 개행문자를 찾으려면, Ctrl+F키로 찾기 대화상자를 꺼낸 후


(2) "찾을 내용"이라는 입력칸을 마우스로 클릭한 후,


(3) 키보드의 Alt키를 누른 상태에서,


(4) 키보드의 맨 우측의 "숫자 키패드(Numeric Keypad; 계산기 모양으로 숫자만 나열된 작은 자판)"에 있는 아라비아 숫자 1과 0을 연속으로 눌러 줍니다.


(5) 이제 키보드에서 손을 뗍니다. 눈에 보이지는 않지만 개행문자가 "찾기 대상 문자열"로서 입력되었습니다.


(6) "다음 찾기" 버튼을 누르면, 셀 속에 개행문자가 들어 있는 셀을 찾아줄 것입니다.


바꾸기 작업도 마찬가지입니다.


만약 Alt+10 이 먹히지 않는다면, Alt+010 을 눌러 줍니다.


잘 안될 수도 있는데 여러번 시도해 보면 됩니다.


Alt키를 누르면서, 십진수 아스키 코드 번호를 누르면, 그 번호에 해당하는 문자들이 찍히는데 그것을 이용하는 것입니다. 엔터키의 십진수 아스키 코드 번호는 10 입니다. (13 일 수도 있지만 엑셀에서는 10 으로 해야 합니다.)

Posted by 경제/부동산/프로그래머/안드로이드/PHP/mysql/asp 당양부부
엑셀 특정 문자열 포함 갯수 : COUNTIF

=IF(COUNTIF(A1,"*"&B2&"*"),"포함","")



Posted by 경제/부동산/프로그래머/안드로이드/PHP/mysql/asp 당양부부
- 참조함수 INDIRECT, ADDRESS -

'자료'라는 시트에 회원자료가 입력되어 있다면 다른 시트에서 '자료'시트를 참조하여 입력된

값을 불러와야 할 경우가 많이 있습니다. 특히나 급여, 자재, 품목, 회원관리등에서 절대적인

위치를 차지하는게 바로 참조함수입니다.

엑셀에서 제공되는 참조함수는 아주 많습니다..

대표적으로 VLOOKUP, INDEX, MATCH, OFFSET, ROW, COLUMN 등이 흔히 사용됩니다

하지만!! 개인적으로 참조함수 중에서도 INDIRECT함수를 참 좋아한답니다^^

왜냐면 모든 참조함수가 반환할수 있는 값을 참조하여 새로운 값을 반환할 수 있기 때문입니다

그래서 이번에는 INDIRECT함수와 ADDRESS함수등을 복합적으로 이용하여 참조값을 반환해보겠습니다. 당연,, 꼭 하나의 해결책만이 존재하지는 않는답니다..

좀더 단순하게 좀더 명료하게 좀더 유동적인 방법을 찾아보려는거죠^^

미리 입력된 자료가 있을때 그 자료가 같은 시트내에 있다면 INDEX, MATCH 등으로 대부분 참조가 됩니다만, 간혹 셀병합이 된 자료에서 값을 참조할 경우나, 다른 시트, 특히 다른 파일에서 참조해야 할 경우, 기존의 참조함수만으로는 애매한 경우가 많습니다..

다른 시트나 다른 파일의 참조, 이름정의에서 빛을 보는게 INDITECT, ADDRESS함수입니다

그럼, INDIRECT 함수에 대한 도움말을 한 번 볼까요??

===========================================================================================

[INDIRECT 함수]

설명 : 텍스트 문자열로 지정된 참조를 반환합니다. 참조가 바로 계산되어 해당 내용이 표시됩니다.

수식 자체는 변경하지 않고서 수식 안에 있는 셀에 대한 참조를 변경하려는 경우에 INDIRECT 함수를 사용합니다.

구문 : INDIRECT(ref_text,a1)

인수 : ref_text => A1 스타일 참조, R1C1 스타일 참조,

참조로 정의된 이름이 들어 있는 셀에 대한 참조이거나 셀에 대한 텍스트 문자열 참조입니다.

ref_text가 유효한 셀 참조가 아닌 경우, 외부참조의 경우 다른 통합문서가 열려있지 않은 경우,

행 제한이나 열제한을 초과하는 범위를 참조할 경우에는 #REF! 오류 값이 반환됩니다.

a1 => ref_text 셀에 들어 있는 참조 유형을 지정하는 논리값입니다.

a1이 TRUE이거나 이를 생략하면 ref_text는 A1 스타일의 참조로 해석됩니다.

a1이 FALSE이면 ref_text는 R1C1 스타일의 참조로 해석됩니다.

[ADDRESS 함수]

설명 : 행 및 열 번호가 지정되었을 때 워크시트에서 셀의 주소를 확인할 수 있습니다.

예를 들어 ADDRESS(2,3)은 $C$2를 반환합니다. 또 다른 예로 ADDRESS(77,300)은 $KN$77을 반환합니다. ROW 및 COLUMN 같은 다른 함수를 사용하여 행 및 열 번호를 ADDRESS 함수의 인수로 제공할 수 있습니다.

구문 : ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

인수 : row_num => 셀 참조에 사용할 행 번호를 지정하는 숫자 값으로서 필수 항목.

column_num => 셀 참조에 사용할 열 번호를 지정하는 숫자 값으로서 필수 항목.

abs_num => 반환할 참조 유형을 지정하는 숫자 값으로서 선택 항목.(절대참조,상대참조)

a1 => A1 또는 R1C1 참조 스타일을 지정하는 논리값으로서 선택 항목.

A1 스타일에서는 열 레이블에 영문자를 사용하고 행 레이블에 숫자를 사용합니다.

R1C1 참조 스타일에서는 열과 행 모두의 레이블에 숫자를 사용합니다. A1 인수가 TRUE이거나

생략하면 A1 스타일의 참조를, 이 인수가 FALSE이면 R1C1 스타일의 참조를 반환합니다.

sheet_text => 외부 참조할 워크시트의 이름을 지정하는 텍스트 값으로서 선택 항목.

예를 들어 수식 =ADDRESS(1,1,,,"Sheet2")는 Sheet2!$A$1을 반환합니다.

===============================================================================

역시나 도움말은 언제봐도 항상 복잡합니다^^;;

INDIRECT함수를 유용하게 사용하려면 A1스타일과 R1C1스타일의 참조에 대해 알아야 합니다

A1스타일의 참조는 A1, B15, H33.. 등 열을 알파벳으로 행을 숫자로 나타내는 우리에게 친숙한

스타일의 참조 방식이구요.. R1C1스타일의 참조는 행과 열을 모두 숫자로 나타냅니다

예를 들면, RC - 현재행 현재열(상대참조 A1과 같은 역할 => 행이동, 열이동)

RC1 - 현재행 첫째열(A열, 상대행 절대열참조, $A1과 같은 역할 => 행이동, 열고정)

R4C2 - 넷째행 둘째열(4행B열, 절대행 절대열참조, 절대참조 $B$4 => 행과 열고정)

R[1]C - 행이 아래로 한칸 이동, 열은 현재열

R[-2]C[2] - 행이 위로 두칸 이동, 열은 오른쪽으로 두칸 이동

복잡하지만 사용해보면 의외로 단순하고 적응하기 쉬운 참조랍니다^^;;;

이젠 실제 사용하는 방법으로 들어가 볼까요,, 글자 보는게 슬슬 지겨워지잖아요^^

'자료'시트에 다음과 같은 내용이 입력되어 있을때, '불러오기'시트에서 참조하는 방법입니다

시트에 자료를 입력하여 관리할때는 기본적으로 셀병합을 사용하지 않으셔야 합니다

기본적인 자료를 입력해둘 시트하나는 어떠한 수식이나 서식도 없이 아주 밋밋하고 단순하게

만들어두고 그걸 보기좋게 출력할 출력시트를 별도로 두어 원하시는만큼 꾸며주시면 됩니다

출력시트에서 값을 불러오다보면 입력시트의 자료배치가 아주 중요하다는걸 알게 된답니다..

셀마다 수식을 직접 변경해준다면 굳이 엑셀의 유용함을 느끼기 어렵겠지요^^;;;

파일을 다운받아 보시면 간단한 몇 개의 예제풀이가 있습니다

예제가 많이 조잡하지만 어쩔 수 없달까요,, 전 예제만드는걸 제일 싫어하지요^^

있는 자료보고 푸는건 하겠는데 예제,, 크~ 너무 어려움

** 첫번째 예제는 회원명으로 회원번호를 불러오는 방법입니다.


INDEX함수는 범위가 정해져 있을경우 행번호와 열번호를 이용하여 값을 반환하는데 유용하고

INDIRECT함수는 행과 열을 찾아 주소를 만들고 해당 텍스트로 된 주소를 찾아 값을 반환합니다

위의 경우 열은 바뀌지 않고 행만 변하기 때문에 직접 입력하여 주소를 만들어 줍니다.

자료시트의 A열에 있는 회원번호중 이름과 같은 열에 있는 값을 반환하면 되기 때문에

"자료!A"&MATCH() 이런 수식으로 주소를 찾아오면 어떤 결과가 나올까요?

MATCH(A3,자료!$B$3:$B$16,0) => 범위에서 일치하는 값의 위치는 13입니다..

하지만 실제 행번호는 15인걸요..??

이런경우 MATCH(A3,자료!$B$3:$B$16,0)+2 로 행번호를 맞춰주거나

MATCH(A3,자료!$B$1:$B$16,0) 이렇게 찾는 범위를 1행부터 넣어주면 해결이 됩니다..

"자료!A"&MATCH(A3,자료!$B$3:$B$16,0)+2"자료!A15" 라는 셀주소를 불러옵니다.

하지만 지금 필요한건 "자료!A15"라는 셀주소가 아니라 셀에 입력된 값이니까

텍스트로 된 셀주소를 INDIRECT함수로 감싸줍니다..

INDIRECT("자료!A"&MATCH(A3,자료!$B$3:$B$16,0)+2) -> S1113[자료!A15의 값]

수식이 사용되는 과정이 이해가시나요.. 셀주소를 만들고 INDIRECT로 불러옵니다.

그런데, 텍스트로 된 주소를 만드는건 ADDRESS함수를 이용해도 가능하잖아요??

ADDRESS(행, 열,,,시트명) .. 두개의 선택항목은 크게 쓸일이 별로 없어서 ㅠㅠ;;;

위의 식도 이렇게 변경이 가능합니다.

INDIRECT(ADDRESS(MATCH(A3,자료!$B$1:$B$16,0),1,,,"자료"))

어느 수식이 사용하기에 편하시나요,, 내키는대로 쓰세요^^;;

** 두번째의 예제는 여러셀에서 값을 참조하여 불러와야 할 경우입니다.

여러셀을 불러와야 할 경우 위의 수식은 셀마다 "자료!A"& "자료!B"& 이렇게 변경해야합니다

연속된 자료를 불러오는데 이렇게 셀마다 손이 간다면 복잡한 수식을 이용하는 의미가 없죠^^


처음 사용된 수식은 INDEX함수와 MATCH함수의 조합입니다.

INDEX(자료!$B$3:$D$16,MATCH($A8,자료!$A$3:$A$16,0),COLUMN()-1)

'자료'시트와 '불러오기'시트의 머리글 순서가 일치하므로 열마다 참조를 변경하면 됩니다

단, 휴대전화의 경우 행은 한칸 내려가고 열은 앞칸의 열과 같으므로 수식이 다릅니다

INDEX(자료!$B$3:$D$16,MATCH($A8,자료!$A$3:$A$16,0)+1,COLUMN()-2)

두번째 사용된 수식은 INDIRECT함수와 ADDRESS함수의 조합입니다.

INDIRECT(ADDRESS(MATCH($A9,자료!$A$3:$A$16,0),COLUMN(),,,"자료"))

행과 열이 변한다면 [예제1]의 경우와 같이 직접 입력하는 것보다

ADDRESS와 함께 사용하는게 훨씬 유용하답니다.

역시나 휴대전화의 경우 규칙에 어긋나므로 수식을 변경해줍니다

INDIRECT(ADDRESS(MATCH($A9,자료!$A$3:$A$16,0)+1,COLUMN()-1,,,"자료"))

** 세번째는 입력된 자료를 순차적으로 불러옵니다



이 부분까지는 수식만으로 충분히 이해가 가시리라 봅니다..

사실, 기본적인 셀참조에서는 그다지 INDIRECT와 ADDRESS의 조합이 빛을 발하진 않죠^^;;;

INDEX(자료!$A$3:$D$16,ROW(A1)*2-1,COLUMN())

INDIRECT(ADDRESS(ROW(A2)*2-1,COLUMN(),,,"자료"))

** 시트상에서 바로 사용하는 수식으로써의 INDIRECT는 활용폭이 많지는 않습니다

하지만 다른 시트에서 입력값이 있는지의 여부를 확인한다거나

다른시트나 다른파일의 자료를 유효성검사의 목록으로 사용하고 싶을 때,

다른 각각의 시트의 일정셀의 값을 한꺼번에 계산하려 할때 아주 유용합니다.

만약, 자료1, 자료2, 자료3..., 자료12 의 12개의 월별시트가 있고

각 시트의 A10셀에 입력된 값을 한꺼번에 합한 결과를 원한다면 어떻게 할까요

자료1!A10 + 자료2!A10 + 자료3!A10 + ... + 자료12!A10

쉽게 생각하면 어느 12층 아파트의 베란다를 한꺼번에 부수고 싶다... 면 그 면적을 한꺼번에

옥상에서 위치를 잡아 뚫고 내려가면 되겠지요^^;; 실현가능성 없는 예입니다만 ㅡ ㅡㅋ

여러범위를 한꺼번에 계산할 때 배열수식을 사용하고, 범위를 불러올때 INDIRECT함수를 쓰고,

그럼 다음과 같은 배열수식이 가능하겠네요 =SUM(INDIRECT("자료"&.......&"!A10"))

수식의 ........부분에 1~12를 한꺼번에 넣어주려면 ROW(1:12)를 이용합니다..

INDIRECT("자료"&ROW(1:12)&"!A10") => {"자료1!A10" ; "자료2!A10" ; ... ; "자료12!A10"}

배열의 반환 결과가 텍스트로 나오는 경우가 있으니 N()함수로 감싸줍니다

SUM(N(INDIRECT("자료"&ROW(1:12)&"!A10"))) 복잡하지만 이해가시지요^^;;;

** 이젠 INDIRECT함수의 백미,, 이름정의로 넘어가 볼까요

첨부파일에 보시면 수식을 볼수 있게 수식이 드러난 부분들이 있습니다

이제까지는 사용된 수식을 나타내려면 수식을 복사해서 '=이용된 수식 이렇게 했잖아요

그런데 수식이 입력된 셀에 가보니 [=수식] 이라는 묘한 수식아닌 수식이 들어있네요^^;;;

이런 경우 바로 이름정의를 활용했구나~ 하고 이름정의를 확인해봅니다.

이름정의에 보니 여러개의 이름이 있군요.. 그 중 '수식'이라는 이름을 확인해봅니다

=GET.CELL(6,INDIRECT("rc[-1]",FALSE))

처음보는 GET.CELL 이라는게 나오네요,, GET.CELL은 매크로 함수로 셀의 정보를 반환합니다

바로 이 GET.CELL 함수를 이용해서 셀의 색상이나 글자색상으로 합을 구할 수 있습니다

이름정의에 GET.CELL함수를 사용한 경우 파일을 저장할때 매크로 포함파일로 저장해야하고,

파일을 열때도 매크로 포함으로 열어야 결과를 보실수 있답니다.

아마도 첨부파일을 매크로 포함하지 않고 열어두셨다면 #NAME? 에러를 구경하셨을 듯^^;;

GET.CELL(인수, 참조셀) 함수의 인수값에 따라 셀의 여러가지 정보를 얻을 수 있는데요

여기에서 사용되는 6이라는 인수를 넣으면 참조셀에 사용된 수식을 알아낼 수 있습니다.

그 다음 참조셀에 사용된 수식이 특이하군요.. INDIRECT("RC[-1]",FALSE)

INDIRECT함수를 사용할 경우 A1스타일 참조나 ADDRESS함수로 셀주소를 만들경우 인수를

생략가능 하지만, R1C1스타일의 참조는 반드시 FALSE 인수를 사용해야 합니다.

RC[-1] 의 의미는?? 행은 현재행, 열은 한칸 앞[왼쪽]

즉, =수식을 입력하면 바로 한칸 [왼쪽]앞의 셀에 사용된 수식을 보여달라고 하는거죠^^

물론 수식을 INDIRECT(ADDRESS(ROW(),COLUMN()-1)) 이렇게 사용하셔도 되고

INDIRECT("E"&ROW())도 결과는 같지만 열번호를 매번 변경해주셔야 한다는 단점이 있죠

다른 이름도 한번 확인해볼까요

[인덱스] =ADDRESS(QUOTIENT(ROW(),10)*10+2,1)

'인덱스'는 예제 3과 예제 4의 자료시작 위치를 반환합니다.

QUOTIENT()함수는 MOD()함수와 반대로 나눈 몫을 반환합니다.

ADDRESS()함수로 만들어진 이름이므로 결과는 $A$2, $A$12, $A$22 ...

[회원번호] =GET.CELL(6,OFFSET(INDIRECT(인덱스),1,0))

'인덱스'의 결과가 셀주소이므로 INDIRECT()함수로 반환받은 후

OFFSET()함수로 참조의 위치를 바꿔줍니다. (아래로 한칸 => 회원번호)

=> 첫번째 회원번호에 사용된 수식을 반환해줍니다

[회원명] =GET.CELL(6,OFFSET(INDIRECT(인덱스),1,1))

'인덱스'에서 한칸 아래, 오른쪽으로 한칸 이동합니다 => 회원명

=> 첫번째 회원명에 사용된 수식을 반환해줍니다

[휴대전화] =GET.CELL(6,OFFSET(INDIRECT(인덱스),1,4))

'인덱스'에서 한칸 아래, 오른쪽으로 네칸 이동합니다 => 휴대전화

=> 첫번째 휴대전화에 사용된 수식을 반환해줍니다

** 이제는 조건부 서식을 이용해보겠습니다~ 급 속도냄 ㅋㅋㅋ


위의 범위에 사용된 조건부 서식은

=COUNTIF($A$32:$C$35,A32)>1 입니다.

셀의 영역을 직접적으로 수식에 사용한 경우입니다

아래의 범위에 사용된 조건부 서식은

=COUNTIF($A$37:$C$40,INDIRECT("rc",FALSE))>1 입니다

차이는 A32 대신에 INDIRECT("rc",FALSE)를 사용한 점인데요

이름정의나 조건부서식을 이용할 경우

시작할 셀을 항상 정확하게 짚고 시작해야 한다는 불편함 아닌 불편함이 있습니다..

셀을 지정하는 부분에서 어긋나서 결과가 바로 안나오는 경우를 종종 겪어서^^;;;

그래서 저는 이름정의나 조건부 서식에서 INDIRECT("rc",FALSE) 를 사용하는 경우가 많은데요

어느 지점에서 적용하더라도 현재 행, 현재 열을 반환하기 때문에

셀의 위치를 잘못잡아서 결과가 틀려지는 경우는 없어지는 거죠^^

한가지더 확인해보고 마무리 하겠습니다..


'자료'시트에 입력된 값인지 아닌지를 확인하는 건데요.

물론 INDIRECT를 사용하지 않고 사용할 셀범위를 잡고 일반수식을 사용하셔도 결과는 같습니다.

여기에서 사용된 수식은 =일치값>0 입니다

조건부 서식을 사용할 경우 수식이 복잡하거나 참조가 많거나 배열이거나 그런 경우

결과가 바로 나오지 않는 경우가 있다면 이름정의를 이용하여 해결하면 된답니다.

사용된 이름정의를 확인해볼까요

[일치값] =COUNTIF(자료!$B$3:$B$16,INDIRECT("rc",FALSE))

자료시트에서 현재 셀의 값이 있다면 빨간색으로 표시하는 조건부서식입니다

하나의 이름정의가 남았습니당...

[수식찾기] =GET.CELL(48,INDIRECT(ADDRESS(ROW(),COLUMN())))

=GET.CELL(48,INDIRECT("RC",FALSE))

GET.CELL함수가 또 나왔네요.. 인수 48은 셀의 값이 수식의 결과인 경우 TRUE를 반환합니다

예제 3과 예제 4의 범위에서 노란색으로 칠해진 부분이 수식으로 구한 결과임을 알수있군요^^

이 밖에도 다중유효성검사등에서 유용하게 쓰일수 있는 함수가 바로 INDIRECT입니다

INDIRECT함수를 유동적으로 도와주는게 ADDRESS함수라고 생각하시면 될듯합니다^^

아래 링크에 있는 내용을 참고로 봐두시면 많은 활용이 되실듯 하네요,,

[lantan님의 매크로 함수]

http://www.examo.co.kr/tn/board.php?board=qqqCommon&page=10&command=body&no=25

[INDIRECT함수를 이용하여 그림불러오기]

http://www.examo.co.kr/tn/board.php?board=qqqCommon&page=9&command=body&no=38

http://www.examo.co.kr/tn/board.php?board=qqqCommon&page=9&command=body&no=37

[GET.CELL함수를 이용해 수식으로 필터링하기]

http://www.examo.co.kr/tn/board.php?board=qqqCommon&page=9&command=body&no=40

 

Posted by 경제/부동산/프로그래머/안드로이드/PHP/mysql/asp 당양부부

OFFSET 함수

▷함수셀 또는 쎌 범위에서 지정된 수의 행과 열로 구성되는 범위에 대한 참조를 반환합니다.
▷반환되는 참조는 단일 셀 또는 셀 범위 일 수 있습니다.

 

▶ 사용 형식

=OFFSET(reference, rows, cols,[height], [width])

 

▶ 사용 설명

1) reference : 필수 항목

   - OFFSET 함수가 동작하기 위한 기준점을 지정합니다.

 

2) rows : 필수 항목

   - reference에서 시작하는 행 이동의 수입니다.

   - 양수라면 reference에서 아래로, 음수라면 위로 이동합니다.

 

3) cols : 필수 항목

   - reference에서 시작하는 열 이동의 수입니다.

    - 양수라면 reference에서 오른쪽으로, 음수라면 왼쪽으로 이동합니다.

4) [height] : 선택 항목

   - rows와 cols로 이동한 셀에서 부터 반환되는 높이(행의 수) 입니다.

   - 양수만 가능합니다.

 

5) [width] : 선택항목

   - rows와 cols로 이동한 셀에서 부터 반환되는 너비(열의 수) 입니다.

 

 

6) 주의 사항

   - reference에서 행과 열의 이동은 reference 자체를 포함하지 않습니다.

   - 그러나, 이동한 곳에서의 height와 width를 계산할 때는 이동한 곳을 포함합니다

 

 

Posted by 경제/부동산/프로그래머/안드로이드/PHP/mysql/asp 당양부부
이전버튼 1 이전버튼

블로그 이미지
경제를 좋아하는 일산의 행복한 프로그래머입니다. 감사합니다.
당양부부
Yesterday21
Today7
Total116,403

달력

 « |  » 2018.12
            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          

최근에 달린 댓글

최근에 받은 트랙백

글 보관함