엑셀 1급 실전모의고사 H형 - 고급필터 문제
고급필터 조건 작성하는 부분에서
"=D4>=LARGE($D$4:$D$17,3)"
여기 범위 지정하는 부분을 저는 그냥 절대참조번지를 지정하지 않고 그냥 범위만 잡아주었습니다.
그랬는데 결과는 4개의 레코드가 추출되었습니다.
해답을 보니 , 위에 처럼 절대참조번지가 되어있었고, 추출 되있는 레코드도 3개만 되있었습니다.
두 개의 경우 차이는 무엇인가요?
책에서 상대참조가 어떻게 바뀐다 어쩐다라는 설명을 봤는데, 말이 어려워서 그런지 그 설명이 잘 안 와닫아서
알기 쉽게 예를 들어주셔서 설명 부탁드립니다.
그리고 고급필터 문제에서 저렇게 array 범위를 넣어야하는 수식이 나오면 무조건 절대참조번지를 지정해주는게
갑 인가요?아니면 절대참조번지를 해주어야 할 경우와 하지말아야 할 경우가 따로 있는지 궁금합니다,
상대참조 : 참조하고 있는 셀이 변경될때 참조된 셀이 함께 변경되는 것
B2 셀에 =A2 라고 입력하고, B2셀을 B4셀에 복사하면, B4셀에는 A4셀을 참조 하고 있습니다.
이렇게 참조하고 있는 셀(B2)이 움직이는 만큼 참조된셀(A2)이 함께 움직이는 것을 상대참조 라고 합니다.
절대참조 : 참조하고 있는 셀이 움직여도, 참조된 셀은 절대로!! 변경되지 않는 것, 절대참조 기호는 $표시이고, 행/열번호 앞에 붙게 됩니다. (ex : $A$2 )
B2셀에 =$A$2 라고 입력하고, B2셀을 B4셀에 복하사면 B4셀에는 여전히 A2셀을 참조 하고 있습니다.
앞에 절대참조가 기호가 A앞에, 2앞에 붙어있기 때문에 A열과 2행이 절대로 고정되어있습니다.
혼합참조(부분참조) : 책마다 다른이름을 사용하기도 하는데요.. 흔히 혼합참조, 부분참조라고 많이 부르기도 합니다.
절대참조와 상대참조를 섞어서 쓴다는것인데, 쉽게 말해.. 행 과 열 둘중 하나만 고정 시키는것 이라고 생각하면 됩니다.
ex) $A2, A$2
위에 예와 같이 A열 앞에만 $기호를 붙혀주거나, 2앞에만 붙혀 주는 형태가 혼합참조 입니다.
B2셀에 =$A2라고 입력하고, B2셀을 D2셀로 복사하면, D2셀은 여전히 A2셀을 참조하고 있습니다. A앞에 $ 기호가 있어 A열이 C열로 이동하지 못한것입니다.
반면에 B2셀을 B4셀로 복사하면 B4셀은 A4셀을 참조하고 있습니다. 2앞에 $ 기호가 없어서 참조된셀도 함께 이동하게 된것입니다.
이렇게 행과 열을 따로따로 절대참조기호로 묶어줄수 있습니다.
정리하면..
$기호가 행 또는 열 앞에 적혀 있으면 절대로 그 행 또는 열은 움직이지 않고 고정되어 있다. 입니다..
상대참조, 절대참조, 혼합참조는 시험을 떠나서, 앞으로 엑셀을 사용하시는데 있어서 매우 중요한 내용입니다..
꼭 이해하시길 바랍니다...
2. 고급필터 문제에서 저렇게 전체범위에서 각각의 셀을 비교하여 조건을 따지는 경우...
무조건 절대참조를 사용하시는게 갑입니다. 제 기억으로는 지금까지 출제된 비슷한 유형의 고급필터 문제는
전부 절대참조를 해야 했습니다.(물론 상대참조를 하여도 답은 같은 경우가 있긴 하지만요..)
함수로 계산되어 기준값을 내야 하는 범위는 절대참조를 하시기를 바랍니다.
-
*2014-03-14 15:23:44
상대참조 : 참조하고 있는 셀이 변경될때 참조된 셀이 함께 변경되는 것
B2 셀에 =A2 라고 입력하고, B2셀을 B4셀에 복사하면, B4셀에는 A4셀을 참조 하고 있습니다.
이렇게 참조하고 있는 셀(B2)이 움직이는 만큼 참조된셀(A2)이 함께 움직이는 것을 상대참조 라고 합니다.
절대참조 : 참조하고 있는 셀이 움직여도, 참조된 셀은 절대로!! 변경되지 않는 것, 절대참조 기호는 $표시이고, 행/열번호 앞에 붙게 됩니다. (ex : $A$2 )
B2셀에 =$A$2 라고 입력하고, B2셀을 B4셀에 복하사면 B4셀에는 여전히 A2셀을 참조 하고 있습니다.
앞에 절대참조가 기호가 A앞에, 2앞에 붙어있기 때문에 A열과 2행이 절대로 고정되어있습니다.
혼합참조(부분참조) : 책마다 다른이름을 사용하기도 하는데요.. 흔히 혼합참조, 부분참조라고 많이 부르기도 합니다.
절대참조와 상대참조를 섞어서 쓴다는것인데, 쉽게 말해.. 행 과 열 둘중 하나만 고정 시키는것 이라고 생각하면 됩니다.
ex) $A2, A$2
위에 예와 같이 A열 앞에만 $기호를 붙혀주거나, 2앞에만 붙혀 주는 형태가 혼합참조 입니다.
B2셀에 =$A2라고 입력하고, B2셀을 D2셀로 복사하면, D2셀은 여전히 A2셀을 참조하고 있습니다. A앞에 $ 기호가 있어 A열이 C열로 이동하지 못한것입니다.
반면에 B2셀을 B4셀로 복사하면 B4셀은 A4셀을 참조하고 있습니다. 2앞에 $ 기호가 없어서 참조된셀도 함께 이동하게 된것입니다.
이렇게 행과 열을 따로따로 절대참조기호로 묶어줄수 있습니다.
정리하면..
$기호가 행 또는 열 앞에 적혀 있으면 절대로 그 행 또는 열은 움직이지 않고 고정되어 있다. 입니다..
상대참조, 절대참조, 혼합참조는 시험을 떠나서, 앞으로 엑셀을 사용하시는데 있어서 매우 중요한 내용입니다..
꼭 이해하시길 바랍니다...
2. 고급필터 문제에서 저렇게 전체범위에서 각각의 셀을 비교하여 조건을 따지는 경우...
무조건 절대참조를 사용하시는게 갑입니다. 제 기억으로는 지금까지 출제된 비슷한 유형의 고급필터 문제는
전부 절대참조를 해야 했습니다.(물론 상대참조를 하여도 답은 같은 경우가 있긴 하지만요..)
함수로 계산되어 기준값을 내야 하는 범위는 절대참조를 하시기를 바랍니다.
-
*2014-03-15 23:37:20
절대참조는 채우기핸들을 사용하여 식을 복사하여 사용할때 변하지 않아야 하는 셀에 사용하는 것입니다.
아래 설명을 참고해 주세요.
절대참조를 사용 하는 이유는 채우기 핸들을 이용하여 수식을 복사할때
변경되지 않아야 하는 범위에 사용 하는 것입니다.
하나의 셀에 수식을 입력하여 구하여야 한다면 절대참조가 필요 없겠죠.
average 함수를 이용할때 하나의 셀에 구할때는 절대참조를 하지 않으셔도 됩니다. 다른 식들도 마찮가지구요. 함수를 이용 할때 변하지 않아야 하는 범위에 대해서 절대 참조를 사용 하게 되는데
average 함수를 쓸때 표에
이름 영어 합격
김 80 a
이 70 b
박 90 c
최 70 d
이라면 평균 자리인 a에 수식을 입력하는데 만약 영어 점수가 평균 이상이면 합격 이라는 식이 있다면
=b2>=avearge(b2:b5)
로 사용 하게 되죠. 그런데 여기 average 인수에 절대값을 하지 않는다면
=b3>=avearge(b3:b6)
=b4>=avearge(b4:b7)
과 같이 평균을 구하는 범위가 달라지기 때문에
=b2>=avearge($b$2:$b$5)
과 같이 사용하여
=b3>=avearge($b$2:$b$5)
=b4>=avearge($b$2:$b$5)
와 같이 고정될 수 있게 하는 것입니다. 이해가 되시는지요?^^
아래는 절대참조와 상태참조의 설명입니다. 참고해 주세요.
절대참조
절대참조는 행, 열 값 앞에 $ 기호를 붙여줍니다.결과 셀의 위치가 바뀌어도 참조 값의 위치는 바뀌지 않는다. 는 의미주로 피 연산자가 되는 셀 위치가 고정된 경우 사용합니다.
=average($A$1:$A$14) <평균값을 절대 값으로 사용하여야 할때>
혼합참조
혼합참조는 행, 열 값 중 한쪽에만 $가 붙어있는 형태입니다.$가 붙어 있는 쪽의 값은 수식을 복사해도 결과 셀의 위치와 관계없이 값이 변경되지 않습니다.
양쪽 방향으로 수식을 복사해야 하는 경우 절대 참조를 사용합니다.
$A1 – 열만 절대참조
A$1 – 행만 절대참조
=$A2 * B$1
간단한 구구단 만들기 입니다. 열과 행을 고정하여 채우기 핸들을 사용하여도 값이 제대로 나오게 됩니다.
파일 첨부합니다. 식을 작성해서 사용해 보세요.
좋은 하루 되세요.