상대 및 절대 셀 참조 및 서식 지정

이 강의에서는 셀 참조, 수식 복사 또는 이동 방법 및 셀 서식 지정 방법에 대해 설명합니다. 우선, 수식과 함수의 많은 기능과 다양성을 뒷받침하는 셀 참조가 의미하는 바를 명확히 설명합니다. 셀 참조가 작동하는 방식에 대한 구체적인 파악을 통해 Excel 스프레드 시트를 최대한 활용할 수 있습니다.!

학교 이동

왜 공식과 함수가 필요한가요??
수식 정의 및 작성
상대 및 절대 셀 참조 및 서식 지정
유용한 기능 알아야 할 사항
조회, 차트, 통계 및 피벗 테이블

노트 : 우리는 여러분이 이미 셀이 스프레드 시트의 사각형 중 하나이며 가로 및 세로로 실행되는 글자와 숫자로 참조되는 열과 행으로 배열되어 있다는 것을 이미 알고 있다고 가정합니다.

셀 참조 란 무엇입니까??

"셀 참조"는 다른 셀이 참조하는 셀을 의미합니다. 예를 들어 A1 셀에 = A2가있는 경우 그러면 A1은 A2를 의미합니다..

셀 참조를 더 자세히 탐색 할 수 있도록 행과 열에 대해 2 단원에서 설명한 내용을 검토해 봅시다..

스프레드 시트의 셀은 행과 열로 참조됩니다. 열은 세로이고 문자로 표시되어 있습니다. 행은 수평이며 숫자로 표시되어 있습니다..

스프레드 시트의 첫 번째 셀은 A1, 즉 열 A, 행 1, B3은 두 번째 열, 세 번째 행 등에있는 셀을 나타냅니다..

셀 참조에 대한 학습 목적으로, 우리는 행, 열로 써야 할 때가 있습니다. 이것은 스프레드 시트에서 유효한 표기법이 아니며 간단하게 명확하게하기위한 것입니다.

셀 참조 유형

세 가지 유형의 셀 참조가 있습니다..

Absolute - 다른 셀로 셀을 복사하거나 이동하면 셀 참조가 동일하게 유지됩니다. 행과 열을 고정하여 수행하므로 복사하거나 이동할 때 변경되지 않습니다..

상대 참조 - 상대 참조는 복사하거나 이동할 때 셀 주소가 변경됨을 의미합니다. 즉, 셀 참조는 그 위치에 관련된다.

혼합 - 셀을 복사하거나 이동할 때 행이나 열을 고정하여 하나가 변경되고 다른 하나는 변경되지 않도록 선택할 수 있음을 의미합니다. 예를 들어 행 참조를 고정한 다음 셀을 두 행 아래로 이동하고 네 개의 열에 걸쳐 놓고 행 참조가 동일하게 유지할 수 있습니다. 아래에서 더 설명 할 것입니다..

예 : 텍스트 수식

다른 예를 들어 봅시다. 전체 이름을 이름과 성으로 나누어야하는 2 단원을 기억하십니까? 이 수식을 복사하면 어떻게됩니까??

수식 = RIGHT (A3, LEN (A3) - FIND ( ","A3) - 1)을 쓰거나 C3 셀에 텍스트를 복사하십시오. 실제 셀을 복사하지 말고, 텍스트 만 복사하고, 텍스트를 복사하십시오. 그렇지 않으면 참조가 업데이트됩니다..

"fx"가 표시된 옆의 상자에서 스프레드 시트의 맨 위에있는 셀의 내용을 편집 할 수 있습니다.이 상자는 셀이 넓기 때문에 길기 때문에 편집하기가 더 쉽습니다..

이제 우리는 :

복잡한 것은 없지만 C3 셀에 새로운 수식을 작성했습니다. 이제 C3을 C2 및 C4 셀로 복사하십시오. 아래 결과를 관찰하십시오 :

이제 알렉산더 해밀턴과 토마스 제퍼슨의 이름이 있습니다..

커서를 사용하여 셀 C2, C3 및 C4를 강조 표시합니다. 커서를 B2 셀에 놓고 내용을 붙여 넣습니다. 무슨 일이 있었는지보세요 - 오류가 발생했습니다 : "# REF."?

우리는 C 열에서 B 열로 셀을 복사 할 때 왼쪽의 오른쪽 열 (A2, LEN (A2) - FIND ( ","A2 "- 1).

그것은 A2에 대한 모든 참조를 A의 왼쪽에있는 열로 바 꾸었습니다. 그러나 A 열 왼쪽에 열이 없습니다. 그래서 컴퓨터는 당신이 무엇을 의미하는지 알지 못합니다..

예를 들어 B2의 새로운 수식은 = RIGHT (#REF!, LEN (#REF!) - FIND ( ",", # REF!) - 1)이고 결과는 # REF :

셀 범위에 수식 복사

하나의 수식을 작성하여 넓은 영역에 복사 할 수 있고 참조가 업데이트되므로 셀을 복사하는 것이 매우 편리합니다. 이렇게하면 각 셀을 편집하여 올바른 위치를 가리키는 것을 방지 할 수 있습니다..

"range"는 하나 이상의 셀을 의미합니다. 예를 들어, (C1 : C10)은 셀 C1에서 셀 C10까지의 모든 셀을 의미합니다. 그래서 그것은 세포의 기둥입니다. 또 다른 예 (A1 : AZ1)는 열 A에서 열 AZ까지의 맨 위 행입니다.

범위가 5 열 10 행을 교차하는 경우 왼쪽 위 셀과 오른쪽 하단 셀 (예 : A1 : E10)을 작성하여 범위를 나타냅니다. 이것은 열과 행의 일부가 아닌 행과 열을 교차하는 정사각형 영역입니다..

다음은 한 셀을 여러 위치에 복사하는 방법을 보여주는 예제입니다. 우리가 예산을 책정 할 수 있도록 스프레드 시트에 그 달의 예상 비용을 보여주고 싶다고합시다. 다음과 같은 스프레드 시트를 만듭니다.

이제 셀 C3 (= B3 + C2)의 수식을 나머지 열에 복사하여 예산에 맞는 균형을 유지하십시오. Excel에서 복사 할 때 셀 참조를 업데이트합니다. 결과는 다음과 같습니다.

보시다시피 새로운 셀마다 업데이트됩니다. 상대적인 새 위치로 이동하면 셀 C4는 수식을 = B4 + C3으로 업데이트합니다.

셀 C5는 = B5 + C4 등으로 업데이트됩니다.

절대 참조

셀을 이동하거나 복사 할 때 절대 참조는 변경되지 않습니다. 우리는 달러 기호를 절대 참조로 사용합니다. 달러 기호를 앵커로 생각하십시오..

예를 들어 모든 셀에 수식 = $ A $ 1을 입력하십시오. 열 A 앞에있는 $는 열을 변경하지 않는다는 것을 의미합니다. 행 1 앞에있는 $는 셀을 복사하거나 다른 셀로 이동할 때 열을 변경하지 않는다는 것을 의미합니다..

아래 예제에서 볼 수 있듯이 B1 셀에는 상대 참조 = A1이 있습니다. B1 셀을 그 아래의 4 셀에 복사하면 상대 참조 = A1이 왼쪽 셀로 변경되므로 B2는 A2, B3이됩니다. A3가 될 것입니다. 그 셀들은 분명히 아무 값도 입력하지 않으므로 출력은 0입니다..

그러나 C1에서와 같이 = $ A1 $ 1을 사용하고 그 아래의 네 개의 셀에 복사하면 참조가 절대적이므로 절대 변경되지 않으며 출력은 항상 A1 셀의 값과 같습니다..

아래 예와 같이 관심사를 파악하고 있다고 가정 해보십시오. C4 = B4 * B1의 공식은 "이자율"* "균형"= "이자율"입니다.

이제 예산을 변경하고 뮤추얼 펀드를 사기 위해 2,000 달러를 추가로 절약했습니다. 고정 금리로 동일한 이자율을 지불한다고 가정합니다. 새 계정을 입력하고 스프레드 시트에 잔액을 입력 한 다음 수식 = B4 * B1을 셀 C4에서 셀 C5로 복사합니다..

새 예산은 다음과 같습니다.

새로운 뮤추얼 펀드는이자를 분명히 5 % 받기 때문에 일년에이자가 0 달러가됩니다..

Excel은 수식이 참조하는 셀을 강조 표시합니다. 위에서 금리 (B1)에 대한 참조가 빈 셀 B2로 이동되었음을 알 수 있습니다. 우리는 달러 기호를 사용하여 $ B $ 1을 작성하여 행과 열 참조를 고정시킴으로써 B1 절대에 대한 참조를 만들어야했습니다..

아래 그림과 같이 C4의 첫 번째 계산을 read = B4 * $ B $ 1으로 다시 작성하십시오.

그런 다음 C4에서 C5로 수식을 복사하십시오. 이제 스프레드 시트는 다음과 같습니다.

수식 1 셀을 복사 한 (행을 1 씩 증가시킨) 이후 새로운 수식은 = B5 * $ B $ 1입니다. 금리가 셀 B1에 고정되어 있기 때문에 뮤추얼 펀드 이자율이 올바르게 계산됩니다..

이는 "이름"을 사용하여 셀을 참조 할 수있는 좋은 예입니다. 이름은 절대적인 참조입니다. 예를 들어 셀 B1에 이름 "이자율"을 지정하려면 셀을 마우스 오른쪽 단추로 클릭 한 다음 "이름 정의"를 선택합니다.

이름은 하나의 셀 또는 범위를 나타낼 수 있으며 수식에서 이름을 사용할 수 있습니다 (예 : interest_rate * 8은 writing = $ B $ 1 * 8과 같은 것임)..

혼합 레퍼런스

혼합 참조는 어느 한 쪽 행 또는 열이 고정됨.

예를 들어, 예산을 세는 농부라고 가정 해보십시오. 당신은 또한 사료 보관소를 소유하고 있으며 씨앗을 판매합니다. 당신은 옥수수, 콩 및 알팔파를 심을 것입니다. 아래 스프레드 시트는 에이커 당 비용을 보여줍니다. "에이커 당 비용"= "파운드 당 가격"* "1 에이커 당 씨앗 파운드"- 그것은 당신이 에이커를 심어야하는 비용입니다.

에이커 당 비용을 D2 셀에 $ B2 * C2로 입력하십시오. 당신은 파운드 열당 가격을 고정시키고 싶다고 말하고 있습니다. 그런 다음 해당 수식을 같은 열의 다른 행에 복사하십시오.

이제 씨앗 재고 목록의 가치를 알고 싶습니다. 인벤토리의 가치를 알기 위해서는 파운드당 가격과 인벤토리 파운드 수가 필요합니다..

"인벤토리에 시드 파운드"와 "인벤토리 값"이라는 두 개의 열을 추가합니다. 이제 D2 셀을 F4에 복사하고 원래 수식 ($ B2)의 첫 번째 부분에있는 행 참조가 행으로 업데이트됩니다 4는 고정되어 있지만 $는 "B"에 고정되어 있기 때문에 열은 고정 된 상태입니다.