종종 사용자는 수식에서 셀을 고정해야 하는 문제에 직면합니다. 예를 들어, 수식을 복사하고 싶지만 링크가 원래 위치에서 복사한 셀 수만큼 위아래로 이동하지 않도록 하려는 경우에 발생합니다.
이 경우 Excel에서 셀 참조를 수정할 수 있습니다. 그리고 이것은 한 번에 여러 가지 방법으로 수행할 수 있습니다. 이 목표를 달성하는 방법을 자세히 살펴보겠습니다.
엑셀 링크란?
시트는 셀로 구성됩니다. 각각에는 특정 정보가 포함되어 있습니다. 다른 셀에서 계산에 사용할 수 있습니다. 그러나 그들은 데이터를 어디서 얻을 수 있는지 어떻게 이해합니까? 링크를 만드는 데 도움이 됩니다.
각 링크는 하나의 문자와 하나의 숫자로 된 셀을 지정합니다. 문자는 열을 나타내고 숫자는 행을 나타냅니다.
절대, 상대 및 혼합의 세 가지 유형의 링크가 있습니다. 두 번째는 기본적으로 설정되어 있습니다. 절대 참조는 열과 열 모두의 고정 주소를 갖는 참조입니다. 따라서 혼합은 별도의 열 또는 행이 고정된 것입니다.
1번 방법
열 및 행 주소를 모두 저장하려면 다음 단계를 따르십시오.
- 수식이 포함된 셀을 클릭합니다.
- 필요한 셀의 수식 입력줄을 클릭합니다.
- F4를 누릅니다.
결과적으로 셀 참조가 절대값으로 변경됩니다. 특징적인 달러 기호로 식별할 수 있습니다. 예를 들어 B2 셀을 클릭한 다음 F4를 클릭하면 링크가 $B$2와 같이 표시됩니다.
셀당 주소 부분 앞의 달러 기호는 무엇을 의미합니까?
- 문자 앞에 배치되면 수식이 이동한 위치에 관계없이 열 참조가 동일하게 유지됨을 나타냅니다.
- 숫자 앞에 달러 기호가 있으면 문자열이 고정되어 있음을 나타냅니다.
2번 방법
이 방법은 이전 방법과 거의 동일하며 F4 키를 두 번 누르기만 하면 됩니다. 예를 들어 B2 셀이 있는 경우 그 이후에는 B$2가 됩니다. 간단히 말해서, 이런 식으로 우리는 라인을 고칠 수 있었습니다. 이 경우 열의 문자가 변경됩니다.
예를 들어 맨 아래 셀의 맨 위에서 두 번째 셀의 내용을 표시해야 하는 테이블에서 매우 편리합니다. 이런 식을 여러 번 하는 대신 행을 수정하고 열을 변경하는 것으로 충분합니다.
3번 방법
이것은 이전 방법과 완전히 동일하며 F4 키를 세 번 누르기만 하면 됩니다. 그러면 열에 대한 참조만 절대적이며 행은 고정된 상태로 유지됩니다.
4번 방법
셀에 대한 절대 참조가 있지만 여기서는 상대적으로 만들어야 한다고 가정합니다. 이렇게 하려면 링크에 $ 기호가 없을 정도로 F4 키를 여러 번 누릅니다. 그러면 상대가 되며 수식을 이동하거나 복사하면 열 주소와 행 주소가 모두 변경됩니다.
넓은 범위에 대한 고정 셀
위의 방법은 수행하는 데 전혀 어려움이 없음을 알 수 있습니다. 그러나 작업은 구체적입니다. 예를 들어, 한 번에 수십 개의 공식이 있고 링크를 절대 링크로 변환해야 하는 경우 수행할 작업.
불행히도 표준 Excel 방법으로는 이 목표를 달성할 수 없습니다. 이렇게 하려면 VBA-Excel이라는 특수 애드온을 사용해야 합니다. 여기에는 Excel로 일반적인 작업을 훨씬 빠르게 수행할 수 있는 많은 추가 기능이 포함되어 있습니다.
여기에는 25개 이상의 사용자 정의 함수와 XNUMX개의 다른 매크로가 포함되어 있으며 정기적으로 업데이트됩니다. 거의 모든 측면에서 작업을 개선할 수 있습니다.
- 세포.
- 매크로
- 다양한 유형의 기능.
- 링크 및 배열.
특히 이 추가 기능을 사용하면 한 번에 많은 수식의 링크를 수정할 수 있습니다. 이렇게 하려면 다음 단계를 수행해야 합니다.
- 범위를 선택합니다.
- 설치 후 나타날 VBA-Excel 탭을 엽니다.
- "수식 잠금" 옵션이 있는 "함수" 메뉴를 엽니다.
6 - 다음으로 필요한 매개변수를 지정해야 하는 대화 상자가 나타납니다. 이 애드온을 사용하면 열과 열을 개별적으로 함께 고정할 수 있으며 패키지로 이미 존재하는 고정을 제거할 수도 있습니다. 해당 라디오 버튼을 사용하여 필요한 매개변수를 선택한 후 "확인"을 클릭하여 작업을 확인해야 합니다.
예
좀 더 명확하게 하기 위해 예를 들어 보겠습니다. 상품 원가, 총 수량 및 판매 수익을 설명하는 정보가 있다고 가정해 보겠습니다. 그리고 우리는 수량과 비용을 기반으로 손실을 공제하지 않고 얼마나 많은 돈을 벌 수 있었는지 자동으로 결정하는 테이블을 만드는 작업에 직면해 있습니다.
이 예에서는 수식을 입력해야 합니다.B2*C2. 보시다시피 아주 간단합니다. 그녀의 예를 사용하여 셀의 주소나 개별 열 또는 행을 수정하는 방법을 설명하는 것은 매우 쉽습니다.
물론 이 예에서는 자동 채우기 마커를 사용하여 수식을 아래로 드래그할 수 있지만 이 경우 셀이 자동으로 변경됩니다. 따라서 D3 셀에는 숫자가 각각 3으로 대체되는 또 다른 수식이 있습니다. 또한 D4 구성표에 따라 수식은 = B4 * C4, D5 형식을 취하지만 유사하게 숫자 5 등등.
필요한 경우(대부분의 경우) 문제가 없습니다. 그러나 드래그 할 때 변경되지 않도록 한 셀의 수식을 수정해야한다면 다소 어려울 것입니다.
달러 수익을 결정해야 한다고 가정합니다. 셀 B7에 넣어 봅시다. 조금 향수를 불러일으키고 35달러당 XNUMX루블의 비용을 표시해 보겠습니다. 따라서 달러로 수익을 결정하려면 루블 금액을 달러 환율로 나눌 필요가 있습니다.
다음은 우리의 예에서 보이는 것입니다.
이전 버전과 마찬가지로 공식을 처방하려고하면 실패합니다. 마찬가지로 수식이 적절한 것으로 변경됩니다. 이 예에서는 다음과 같습니다. =E3*B8. 여기에서 우리는 볼 수 있습니다. 공식의 첫 번째 부분이 E3으로 바뀌었고 이 작업을 스스로 설정했지만 공식의 두 번째 부분을 B8로 변경할 필요는 없습니다. 따라서 참조를 절대 참조로 변환해야 합니다. F4 키를 누르지 않고 달러 기호만 넣으면 됩니다.
두 번째 셀에 대한 참조를 절대 셀로 전환한 후 변경으로부터 보호되었습니다. 이제 자동 채우기 핸들을 사용하여 안전하게 끌 수 있습니다. 모든 고정 데이터는 수식의 위치에 관계없이 동일하게 유지되며 커밋되지 않은 데이터는 유연하게 변경됩니다. 모든 셀에서 이 줄에 설명된 루블 수익은 동일한 달러 환율로 나뉩니다.
수식 자체는 다음과 같습니다.
=D2/$B$7
주목! 우리는 두 개의 달러 기호를 표시했습니다. 이런 식으로 우리는 열과 행이 모두 고정되어야 함을 프로그램에 보여줍니다.
매크로의 셀 참조
매크로는 작업을 자동화할 수 있는 서브루틴입니다. Excel의 표준 기능과 달리 매크로를 사용하면 몇 줄의 코드로 특정 셀을 즉시 설정하고 특정 작업을 수행할 수 있습니다. 부가 기능을 설치할 방법이 없는 경우(예: 개인용 컴퓨터가 아닌 회사 컴퓨터 사용) 정보를 일괄 처리하는 데 유용합니다.
먼저 매크로의 핵심 개념은 다른 개체를 포함할 수 있는 개체라는 것을 이해해야 합니다. Workbooks 개체는 전자 책(즉, 문서)을 담당합니다. 여기에는 열려 있는 문서의 모든 시트 모음인 Sheets 개체가 포함됩니다.
따라서 셀은 Cell 개체입니다. 특정 시트의 모든 셀을 포함합니다.
각 개체는 괄호로 묶인 인수로 한정됩니다. 셀의 경우 이 순서대로 참조됩니다. 행 번호가 먼저 나열되고 그 다음에 열 번호 또는 문자가 나열됩니다(두 형식 모두 허용됨).
예를 들어, C5 셀에 대한 참조를 포함하는 코드 줄은 다음과 같습니다.
통합 문서("Book2.xlsm").Sheets("List2").Cells(5, 3)
통합 문서("Book2.xlsm").Sheets("List2").Cells(5, "C")
개체를 사용하여 셀에 액세스할 수도 있습니다. 깔끔한. 일반적으로 범위에 대한 참조를 제공하기 위한 것이지만(그런데 해당 요소가 절대적이거나 상대적일 수도 있음) Excel 문서와 동일한 형식으로 간단히 셀 이름을 지정할 수 있습니다.
이 경우 선은 다음과 같습니다.
통합 문서("Book2.xlsm").Sheets("List2").Range("C5")
이 옵션이 더 편리해 보일 수 있지만 처음 두 옵션의 장점은 대괄호 안에 변수를 사용하고 더 이상 절대적이지 않은 링크를 제공할 수 있다는 것입니다. 계산.
따라서 매크로는 프로그램에서 효과적으로 사용할 수 있습니다. 사실, 여기에서 셀이나 범위에 대한 모든 참조는 절대적이므로 수정될 수도 있습니다. 사실, 그렇게 편리하지 않습니다. 매크로의 사용은 알고리즘의 많은 단계가 있는 복잡한 프로그램을 작성할 때 유용할 수 있습니다. 일반적으로 절대 참조 또는 상대 참조를 사용하는 표준 방법이 훨씬 더 편리합니다.
결론
우리는 셀 참조가 무엇인지, 어떻게 작동하는지, 무엇을 위한 것인지 알아냈습니다. 절대 참조와 상대 참조의 차이점을 이해하고 한 유형을 다른 유형으로 바꾸기 위해(간단히 주소를 수정하거나 고정 해제) 수행해야 하는 작업을 파악했습니다. 많은 수의 값으로 즉시 수행할 수 있는 방법을 알아냈습니다. 이제 적절한 상황에서 이 기능을 유연하게 사용할 수 있습니다.