3. 이름 적용 : 기존 수식에 이름 적용하기
=2*b2*b1이라고 수식을 입력했는데, 셀 주소 등 참조 대상을 이름으로 바꾸는 기능입니다. 다시 말해 b2를 이름인 원주율로 바꾸고, b1을 반지름으로 바꾸는 기능이 이름 적용하기입니다.
이름 관리자 창이 열려 있다면 닫고
B3셀과 B4셀의 pi를 b2로 바꿉니다.
이럴 때는 Ctrl + H키 누르거나, 홈 탭에서 찾기 및 선택을 누른 다음 바꾸기를 누르면
찾기 및 바꾸기 대화 상자가 표시되는데, 찾을 내용에 pi, 바꿀 내용에 b2라고 입력한 다음 모두 바꾸기 버튼을 누르면 됩니다.
그러면 2개 항목이 바뀌었다는 메시지가 보이고, 확인 버튼을 누르고 닫기 버튼을 누르면,
수식의 pi가 모두 B2로 바뀌었고, B3셀과 B4셀의 #NAME? 에러도 없어졌습니다.
① 이름 적용 명령은 이름 정의 옆의 콤보 상자 버튼을 누르면 표시됩니다. 이름 적용 명령을 누르면
② 적용 가능한 이름 목록이 표시되는데(mile은 표시되지 않음), 반지름만 선택되어 있으므로 원주율도 마우스 왼쪽 버튼으로 클릭해서 선택한 다음 확인 버튼을 누르면
B3셀의 수식에서 B2는 원주율로, B1은 반지름으로 바뀌었습니다.
위에서 반지름만 선택된 상태라면 아래와 같이 반지름만 이름이 바뀝니다.
B4셀의 수식을 확인하기 위해 B4셀을 마우스로 클릭하면 =원주율*반지름^2로 되어 있습니다.
이름 적용하기를 할 때 범위를 지정하지 않아도 됩니다.
4. 수식에서 사용 : 이름을 사용하여 새로운 수식 만들기
이름이 영어인 경우에는 첫 글자만 일치해도 이름 목록이 표시되는데, 한글은 그것이 안되어 직접 입력하거나 셀 주소를 클릭해야 했습니다.
그런데, 수식에서 사용 명령을 이용하면 한글로 된 이름도 편리하게 입력할 수 있습니다.
C3셀을 마우스로 클릭하고, =2* 까지 입력하고,
수식 탭에서 이름 관리자 옆의 수식에서 사용 명령을 누릅니다.
그러면 이름 목록이 표시되고, 맨 아래에는 이름 붙여넣기 메뉴가 있습니다. 따라서, pi 또는 원주율을 클릭합니다.
그리고 * 를 누른 다음 다시 수식에서 사용 명령을 누른 후 반지름을 클릭하면
수식이 완성되므로 마우스로 C4셀을 클릭하면 C3셀에 수식이 입력됩니다.
C4셀을 클릭하고 원의 면적도 구해보기 바랍니다.
5. 선택 영역에서 만들기 : 선택 영역에서 첫 행 또는 왼쪽 열 등을 이용하여 이름 만들기
A7셀에서 E11셀까지 마우스로 끌어서 범위를 선택한 후 수식 탭의 선택영역에서 만들기 명령을 누르면
이름 만들기 기준이 첫 행, 왼쪽 열, 끝 행, 오른쪽 열로 표시되고, 첫 번째와 두 번째에 체크가 되어 있습니다.
첫 행은 국어, 영어 등으로 이름을 만드는데 그 아래 셀을 참조 대상으로 하겠다는 것이고, 왼쪽 열은 갑돌이, 갑순이 등으로 이름을 만들고 오른쪽 셀을 참조 대상으로 하겠다는 것입니다. 그리고, 성명은 모서리에 있기 때문에 첫 행과 왼쪽 열을 제외한 숫자가 있는 영역 전체를 참조 대상으로 합니다.
수식 탭의 이름 관리자를 클릭해보면 이름이 한꺼번에 만들어진 것과 이름, 값과 참조 대상이 어떻게 되어 있는지 알 수 있습니다.
이제 F열에서 합계를 구하기 위해 F8셀을 클릭하고 홈 탭의 자동 합계 명령을 클릭하면
수식이 =SUM(갑돌이)로 입력되는데 갑돌이는 B8:E8입니다.
그러나, F8셀의 채우기 핸들을 끌어도 자동으로 갑순이로 바뀌지 않기 때문에 불편합니다.
이것은 참조 대상이 =Sheet1!$B$8:$E$8 식으로 행과 열이 절대 참조로 되어 있기 때문입니다.
따라서, 해결책을 아래 6번에서 알아보겠습니다.
6. 절대 참조, 상대 참조를 이용하여 참조 대상 수정하기
F열에서 합계를 구할 때는 열을 고정되더라도 행은 아래로 이동할 때 자동으로 바뀌어야 합니다.
따라서, 갑돌이의 참조 대상 맨 뒤를 클릭한 후 F4키를 2번 눌러
=Sheet1!$B$8:$E8 형식으로 행에는 $표시를 제거하고, 엔터키를 눌러 확정한 후
닫기 버튼을 누른 후 F8셀의 채우기 핸들을 더블 클릭하면 수식은 모두 =SUM(갑돌이)이지만 수식에서 행이 자동으로 변경되어 값도 맞습니다.
F10셀을 클릭한 후 수식 입력 줄에서 갑돌이를 마우스로 끌어 범위를 잡은 후 F9키를 누르면 왼쪽 열의 숫자가 배열로 표시됩니다.
원래의 수식으로 돌리기 위해 Esc키를 누릅니다.
이제 B12셀부터 E12셀까지의 합계도 제대로 계산하기 위해 국어의 참조 대상을 열은 변경되게 하고 행은 고정되도록 =Sheet1!B$8:B$11로 바꾸고 엔터키를 누른 다음 닫기 버튼을 누릅니다.
그리고, B12셀을 선택한 후 자동 합계 명령을 누르고, B12셀의 채우기 핸들을 F12셀까지 끌면 수식이 맞게 입력됩니다.
상대/절대 참조에 대한 글은 아래를 참고 바랍니다.
https://lsw3210.tistory.com/70
7. 시트가 변경되더라도 참조 대상이 자동으로 변경되도록 하기
수식 탭에서 이름 관리자를 열어보면 mile을 제외하고는 참조 대상에 Sheet1이 모두 포함되어 있으므로,
Sheet2를 마우스로 클릭하고, F8셀을 선택한 다음
홈 탭에서 자동 합계 명령을 누르고, 수식 탭에서 수식에서 사용 명령을 누르고,
갑돌이를 클릭한 다음
엔터키를 누르면 333이 구해지는데, 이것은 Sheet1의 B8셀에서 E8셀까지의 합계이고, Sheet2의 B8셀에서 E8셀까지의 합계는 331입니다.
따라서 Sheet1을 지우면 됩니다. !까지 지우면 다시 Sheet1이 추가되므로 !는 그대로 둬야 합니다. Sheet2에서 이름 관리자를 클릭하고, 갑돌이를 클릭한 다음 참조 대상에서 Sheet1만을 지우고 엔터키를 누른 다음 닫기 버튼을 누릅니다.
F8셀의 수식은 여전히 =SUM(갑돌이)이지만 합계는 331로 바뀌었습니다.
8. 함수를 포함하여 참조 대상 지정하기
이름 관리자를 실행하고 갑돌이를 선택한 다음 참조 대상 입력 칸에
= 다음에 sum(을 넣고, 참조 대상 입력 줄 끝을 마우스로 클릭한 다음 )를 닫고 엔터키를 입력한 다음
닫기 버튼을 누릅니다.
화면을 보면 =SUM(갑돌이)의 결괏값이 변하지 않고 331로 동일한데 이것은 =SUM(SUM(갑돌이))이므로 합계가 동일한 것이고,
이제 =갑돌이라고만 입력해도 합계가 구해집니다.
선택영역에서 만들기 명령을 이용해 이름을 만들다 보니 이름이 갑돌이로 이상한데, 왼쪽가로합계 등으로 이름을 변경하는 것이 좋겠습니다.
9. 이름을 사용하여 Vlookup 함수 내 인수 입력하기
VLOOKUP 함수는 인수로 lookup_value, table_array, col_index_num, [range_lookup] 값을 받는데, lookup_value(찾을 값)를 table_array(찾을 범위)의 첫 열에서 찾은 후 그 행에서 col_index_num(순번)에 해당하는 값을 찾는 함수입니다.
위 표에서 갑돌이의 국어, 영어 점수 등을 찾을 때 사용합니다.
table_array를 이름으로 정의해보겠습니다.
위에서 '선택 영역에서 만들기'명령을 이용해 성명 이름을 만들었는데, 성명의 참조 대상이 B8에서 E11이므로 A8에서 A11이 포함되어 있지 않아서 사용할 수 없습니다.
따라서, 수식 탭에서 이름 정의 명령을 누른 후 이름에 table_array라고 입력하고, 설명에는 vlookup에서 사용할 table_array라고 입력하고, 참조 대상의 셀 주소를 다른 주소로 바꾸기 위해 마우스로 끌어서 모두 선택한 다음
마우스로 A8셀에서 E11셀까지 끌어서 범위를 지정하는데, Sheet1에도 적용되도록 Sheet2를 삭제한 다음(=!$A$8:$E$11가 됨) 확인 버튼을 누릅니다.
이제 i8셀에 수식을 입력하는데
=vl까지 입력하면 vlookup 함수가 표시되므로 탭키를 눌러 완성하고,
찾을 값으로는 A8셀을 마우스로 클릭합니다.
그리고, table_array(찾을 범위)을 입력하기 위해 ta까지 입력하면 table_array가 표시되므로 다시 탭키를 누르고,
국어 점수는 성명 다음에 있어서 두 번째이므로 2를 입력하고, range_lookup으로는 정확히 일치해야 하니 false 또는 0을 입력하고 괄호를 닫은 다음
엔터키를 누르면 갑돌이의 국어 점수 60이 구해집니다.
10. 이름 붙여넣기 : 이름과 참조 대상 출력하기, 수식에서 사용하기
이름 붙여넣기는 작성된 이름의 목록과 참조 대상을 출력하는 기능입니다.
가. 목록 붙여 넣기
A16셀을 마우스로 클릭하고, 수식 탭에서 수식에서 사용 명령을 누르고, 맨 아래 수식으로 붙여넣기 명령을 누르면
이름 목록 아래 목록 붙여넣기 버튼을 누르면
이름과 참조 대상이 표시됩니다.
나. 수식에서 사용
E16셀을 선택하고 다시 수식에서 사용 명령을 누르고, 이름 붙여넣기를 선택한 후 목록에서 이름을 하나 선택하면 확인 버튼이 활성화, 다시 말해 글자가 진해지면서 선택 가능한 상태가 되는데,
확인 버튼을 누르면
아래와 같이 수식이 입력되는데, 갑순이 성적이 배열로 표시되는데, Sheet1을 수정하지 않아 Sheet1의 성적입니다.
위 내용을 참고하여 Sheet1을 제거하고, 비어 있는 합계, 평균 등도 구해보기 바랍니다.
'Excel' 카테고리의 다른 글
| 엑셀 배우기(12) - 테두리, 행 높이, 열 너비, 셀 병합(2) (0) | 2022.07.22 |
|---|---|
| 엑셀 배우기(11) - 테두리, 행 높이, 열 너비, 셀 병합(1) (0) | 2022.07.21 |
| 엑셀 배우기(9) - 셀 주소 또는 범위 등에 이름을 정의하고 수식에 사용하기(1) (0) | 2022.07.19 |
| 엑셀 배우기(8) - 범위 지정하기 (0) | 2022.07.18 |
| 엑셀 배우기(7) - 고급 필터 (0) | 2022.07.15 |