Julie는 워크 시트에 누군가 값을 입력하면 입력 된 값에 자동으로 20 %를 추가하는 입력 셀을 갖고 싶어합니다.

예를 들어, 누군가이 셀 중 하나에 200을 입력하면 실제로 입력되는 것은 240입니다.

매크로를 사용하여이 작업에 접근 할 수있는 방법에는 여러 가지가 있습니다.

가장 좋은 방법은 워크 시트에서 셀이 변경 될 때 자동으로 실행되는 매크로를 만드는 것입니다. 그런 다음 입력 셀 중 하나에서 변경 사항이 적용되었는지 확인하고 그에 따라 값을 조정할 수 있습니다.

다음 예제는 입력 된 값이 A1, C3 또는 B8의 세 셀 중 하나에서 만들어진 경우 수정합니다.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rInput As Range     Dim rInt As Range     Dim rCell As Range

'change the input cell range as desired     Set rInput = Range("A1, C3, B8")



Set rInt = Intersect(Target, rInput)

If Not rInt Is Nothing Then         For Each rCell In rInt             If IsNumeric(rCell) Then                 With Application                     .EnableEvents = False                     rCell = rCell * 1.2                     .EnableEvents = True                 End With             End If         Next     End If End Sub

이것은 이벤트 처리기이므로 워크 시트에서 무언가 변경 될 때마다 트리거됩니다 (이 경우). 이 매크로를 사용하려면 워크 시트 탭을 마우스 오른쪽 버튼으로 클릭하고 나타나는 컨텍스트 메뉴에서 코드보기를 선택합니다. Excel에 VB 편집기가 표시되고 Worksheet_Change 코드를 추가 할 수 있습니다.

세 개의 정의 된 입력 셀 중 하나에서 변경이 수행되었는지 확인하는 열쇠는 Intersect 함수입니다. 대상 범위 (Worksheet_Change 처리기를 트리거 한 변경된 셀)와 rInput 범위 (입력 셀) 사이에 교차점이 있는지 확인합니다. 있는 경우 rInt는 교차 한 셀을 포함합니다.

그런 다음 매크로는 해당 셀을 단계별로 실행하고 셀에 숫자 값이 포함 된 경우 해당 셀에 120 %를 곱합니다. (120 % 곱하는 것은 값을 20 % 늘리는 것과 같습니다.) 곱하기가 완료되면 .EnableEvents 속성이 False로 설정됩니다. 이 보호 조치를 취하지 않으면 각 곱셈이이 이벤트 핸들러를 다시 트리거하고 셀 값에 120 %를 반복적으로 (영원히) 곱합니다.

특정 소수점 또는 정수 값으로 반올림하는 등 값에 대해 실제로 다른 처리를 수행하려면 실제로 곱셈을 수행하는 단일 행을 변경하면됩니다.

입력 셀이 인접한 영역에있는 경우 더 나은 방법은 해당 입력 셀을 명명 된 범위로 정의한 다음 매크로 내에서 명명 된 범위를 사용하여 변경된 셀의 교차점을 결정하는 것입니다. 이렇게하면 입력 셀 그룹이 변경 될 때 또는 변경 될 때 매크로를 수정할 필요가 없습니다.

이 접근 방식을 사용하기 위해 입력 셀 범위가 B7 : B19라고 가정합니다. 해당 셀을 선택한 다음 셀 영역의 왼쪽 상단 모서리에있는 이름 상자를 사용하여 “plus20pct”라는 이름을 입력합니다. 이 작업은 범위에 이름을 할당합니다. 그런 다음 매크로 내에서 해당 이름을 사용할 수 있습니다.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rInt As Range     Dim rCell As Range

'change the input cell range as desired

Set rInt = Intersect(Target, Range("plus20pct"))

If Not rInt Is Nothing Then         For Each rCell In rInt             If IsNumeric(rCell) Then                 With Application                     .EnableEvents = False                     rCell = rCell * 1.2                     .EnableEvents = True                 End With             End If         Next     End If End Sub

유일한 변경 사항은 셀의 교차가 결정되는 방식입니다. Intersect 함수는 “plus20pct”범위를 매개 변수로 사용합니다. 다른 모든 것은 이전처럼 작동합니다.

이제 매크로를 사용하여이 작업을 수행하는 방법을 보았으므로 실제로 매크로를 사용하여 수행해야하는지 여부에 대한 질문이 남아 있습니다. 첫째, 이러한 매크로가 수행 할 수있는 작업에는 제한이 있습니다. 예를 들어, 사용자가 입력 셀 중 하나에 날짜 또는 시간을 입력하면 어떻게 될까요? 내부적으로 Excel은 날짜와 시간을 숫자로 처리하므로 날짜와 시간도 20 % 증가합니다.

둘째, 누군가가 행이나 열을 추가하거나 삭제하여 워크 시트 구조를 수정하는 경우 워크 시트에 어떤 일이 발생하는지 고려해야합니다.

매크로는 절대 셀 참조 (A1, C3 및 B8) 또는 명명 된 범위 (plus20pct)를 사용합니다. 명명 된 범위는 행 또는 열 추가 또는 삭제로 조정될 수 있지만 절대 셀 참조는 변경되지 않습니다. 따라서 매크로 검사 (및 조정)로 끝날 수 있습니다

더 이상 예상 데이터 입력 셀이 아닌 셀.

셋째, 누군가 입력 셀 중 하나에 값 (200)을 입력한다고 가정 해 보겠습니다. 자동으로 20 % 씩 증가하고 240이됩니다. 그 사람은이 변화를보고 무슨 일이 일어 났는지 궁금해서 셀을 선택하고 F2를 눌러 셀 편집을 시작합니다. 변경하기 전에 “오, 예. 자동으로 20 % 증가해야합니다.”라는 사실을 기억합니다. 따라서 그들은 단순히 Enter를 눌러 240 값을 승인합니다.

그러나 Excel에서는이를 변경으로 인식하고 240을 20 % 증가시켜 사용자 또는 사용자가 의도 한 바가 아닌 288이됩니다.

이 두 번째 고려 사항 (사용자 혼란)은 사용자가 워크 시트에 입력하는 내용을 자동으로 변경하는 가장 큰 잠재적 문제입니다. 덜 혼란스러운 접근 방식은 통합 문서에 대해 잘 정의 된 입력 영역을 갖는 것입니다. 사용자는 입력 영역에 그림을 입력하고 해당 그림은 입력 된 그대로 유지됩니다. 그런 다음 다른 셀이나 수식에서 20 % 조정을 수행합니다.

이 디자인 접근 방식 (쉬운 데이터 입력을 위해 워크 시트 디자인 수정)은 워크 시트 셀에 입력 한 내용을 자동으로 변경하는 것보다 사용자에게 잠재적으로 덜 혼란 스럽습니다. 또한 매크로 사용 통합 문서에 내재 된 위험을 제거합니다. 사용자는 매크로를 사용하지 않고 통합 문서를로드 할 수 있으므로 의도 한대로 수치가 조정되지 않습니다.

_ 참고 : _

이 페이지 (또는 ExcelTips 사이트의 다른 페이지)에 설명 된 매크로를 사용하는 방법을 알고 싶다면 유용한 정보가 포함 된 특별 페이지를 준비했습니다.

link : / excelribbon-ExcelTipsMacros [새 브라우저 탭에서 특별 페이지를 열려면 여기를 클릭하세요].

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (12684)은 Office 365의 Microsoft Excel 2007, 2010, 2013, 2016, 2019 및 Excel에 적용됩니다.