외부 참조

Excel의 외부 참조는 다른 통합 문서의 셀 또는 셀 범위에 대한 참조입니다. 아래에서 3 개 부서 (North, Mid 및 South)의 통합 문서를 찾을 수 있습니다.

외부 참조 생성

외부 참조를 생성하려면 다음 단계를 실행하십시오.

1. 모든 통합 문서를 엽니 다.

2. Company 통합 문서에서 B2 셀을 선택하고 등호 =

3.보기 탭의 창 그룹에서 창 전환을 클릭합니다.

4. North 클릭

5. North 통합 문서에서 B2 셀을 선택합니다.

6. "+" 입력

7. Mid 통합 문서에 대해 3 ~ 6 단계를 반복합니다.

8. South 통합 문서에 대해 3 ~ 5 단계를 반복합니다.

9. B2 셀의 수식에서 $ 기호를 제거합니다. 결과:

10. 수식을 다른 셀에 복사합니다.

경고

모든 통합 문서를 닫습니다. 부서의 통합 문서에서 번호를 변경합니다. 모든 통합 문서를 다시 닫습니다. 회사 통합 문서를 엽니다.

A. 모든 링크를 업데이트하려면 콘텐츠 사용을 클릭합니다.

B. 링크를 업데이트하지 않으려면 X를 클릭합니다.

참고 : 다른 경고가 표시되면 업데이트 또는 업데이트 안 함을 클릭합니다.

링크 편집

데이터 탭의 쿼리 및 연결 그룹에서 링크 편집을 클릭하여 링크 편집 대화 상자를 시작합니다.

1. 링크를 업데이트하지 않은 경우에도 여기에서 링크를 업데이트 할 수 있습니다. 통합 문서를 선택하고 값 업데이트를 클릭하여이 통합 문서에 대한 링크를 업데이트합니다. 상태가 어떻게 확인으로 변경되는지 확인합니다.

2. 경고를 표시하지 않고 링크를 자동으로 업데이트하지 않으려면 시작 프롬프트를 클릭하고 세 번째 옵션을 선택한 다음 확인을 클릭합니다.

왼쪽 Lookup

VLOOKUP 함수는 오른쪽으로만 찾습니다.

열에서 값을 찾고 왼쪽에 해당 값을 반환하려면 INDEX 및 MATCH를 사용하면됩니다.

1. MATCH 함수는 주어진 범위에서 값의 위치를 ​​반환합니다.

설명 : $G$4:$G$7 범위의 위치 4에서 104 개를 찾았습니다.

2.이 결과와 INDEX 함수를 사용하여 $E$4:$E$7 범위의 네 번째 값을 반환합니다.

3. B2 셀의 수식을 B11 셀로 끕니다.

참고 :이 공식을 아래로 드래그하면 절대 참조 ($E$4:$E$7 및 $G$4:$G$7)는 동일하게 유지되고

상대 참조 (A2)는 A3, A4, A5 등으로 변경됩니다.

2020/12/16 - [엑셀] - Vlookup

2020/09/10 - [분류 전체보기] - index, match 엑셀 도움말

함수를 사용하여 텍스트 분할

LEFT 함수

LEFT는 텍스트 문자열의 첫 번째 문자부터 시작하여 지정한 문자 수만큼 문자를 반환합니다.

LEFT(text, [num_chars])

• text    필수 요소입니다. 추출하려는 문자가 들어 있는 텍스트 문자열입니다.

• num_chars    선택 요소입니다. LEFT 함수로 추출할 문자 수를 지정합니다.

  • num_chars는 0이거나 0보다 커야 합니다.

  • num_chars가 문자열의 길이보다 길면 LEFT는 텍스트 전체를 반환합니다.

  • num_chars를 생략하면 1로 간주됩니다.

LEN 함수는 텍스트 문자열의 문자 수를 반환합니다.

LEN(text)

LEN 함수 구문에는 다음과 같은 인수가 사용됩니다.

• text    필수 요소입니다. 길이를 확인하려는 문자열입니다. 공백도 문자로 계산됩니다.

앞3자리를 지우고 나머지 숫자만 나타나게 하는 수식

Microsoft Community 사이트에서는 다음과 같이 답변해주고 있습니다.

문의 하신 Excel에서 Shift키를 누르면 한자가 나오는 문제에 대해 안내를 드리겠습니다.

먼저 사용하시는 키보드 말고 다른 키보드가 연결되어 있다면, 확인 부탁 드립니다.

다른 Office 프로그램이나 응용 프로그램에서 어떤지도 확인해 주시기 바랍니다.

보통 이러한 문제의 경우 키보드 드라이버와 호환성 문제일 가능성이 높습니다. 설치되어 있는 키보드 드라이버를 제거하시고, 드라이버 없이 사용시 어떤지 확인해 주시기 바랍니다.

또한 소프트 키보드(화상 키보드)를 이용하여 입력시 어떤지도 확인해 주시기 바랍니다.

-> 단축키로 Ctrl+Alt+F10입니다.

타사 프로그램과 충돌 - 문서 보안 DRM(Fasoo.com, McAfee, Document-Safer, SoftCamp, Mark-Any....)프로그램이 설치되어 있다면 반드시 제거하고 진행하시기 바랍니다.

http://support.microsoft.com/kb/976960/ko

(Process Explorer의 다운로드 경로가 손상되었으니 아래 경로에서 다운로드 받으시기 바랍니다.)

http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx

https://answers.microsoft.com/ko-kr/msoffice/forum/msoffice_excel-mso_windows8-mso_2013_release/%EC%97%91%EC%85%80-%EC%82%AC%EC%9A%A9%EC%8B%9C/53b4acf2-9a56-46cc-bbb4-16a47f3614cb

값으로 붙여넣기 빠르게 하는 방법

1. 리본 메뉴의 '빠른 실행 도구 모음'에 단축키를 설정할 수 있습니다.

1) 리본메뉴에 있는 삼각형 모양을 클릭한다.

2) 빠른 실행 도구 모음 사용자 지정에서 기타 명령(M)...을 클릭

3) 명령 선택에서 모든 명령선택

값 붙여넣기 선택한 후 추가 클릭

확인 클릭

4) 아래 그림과 같이 값 붙여 넣기가 추가 됩니다.

5) 수식을 복사하면 빠른 실행 도구 모음의 값 붙여 넣기가 활성화 됩니다.

붙여넣을 셀을 선택한 후 

클릭합니다.

2. 단축키를 이용하는 방법 

Alt + H, V, V를 순서대로 누른다.

1) 복사한다.

2) Alt + H를 누른다.

3) V를 누른다.

4) 다시 한번 더 V를 누른다.

특정배수열의 값만 더하기

셀의 값을 더해야 하는데 연속된 셀이 아닐 경우 각 셀을 모두 선택해야합니다.

그런데 이런셀이 아주 많다고 하면 셀 선택하는 것도 시간이 많이 걸리겠지요.

그래서 간단하게 하는 방법을 알아보도록 하겠습니다.

아래 그림과 같이 9의 배수열의 값을 모두 더하고 싶습니다.

결론적으로 수식은 아래와 같습니다.

=SUM(IFERROR(A6:AE6*(MOD(COLUMN(A6:AE6),9)=0),0))

1.

=SUM(IFERROR(A6:AE6*(MOD(COLUMN(A6:AE6),9)=0),0))

COLUMN(A6:AE6) : 더하고자 하는 영역의 열 번호를 구합니다.

2.

=SUM(IFERROR(A6:AE6*(MOD(COLUMN(A6:AE6),9)=0),0))

MOD(COLUMN(A6:AE6),9) : 더하고자 하는 영역의 열 번호 9로 나눈 나머지를 구합니다.

9의 배수열을 구하기 때문에 9의 배수열의 나머지는 0입니다.

3.

=SUM(IFERROR(A6:AE6*(MOD(COLUMN(A6:AE6),9)=0),0))

2.에서 구한 나머지의 값이 0과 같은지를 비교합니다.

9의 배수열의 나머지가 0이므로 각 열의 9의배수열만 TRUE를 반환하고 나머지는 FALSE를 반환합니다.

4.

=SUM(IFERROR(A6:AE6*(MOD(COLUMN(A6:AE6),9)=0),0))

A6:AE6에 있는 값과 각열의 FALSE와 TRUE를 곱합니다.

이렇게 되면 9의 배수열만 그셀의 값을 반환하고 다른셀을 0을 반환하거나, 문자가 있는 셀은 #VALUE!을 반환합니다.

5.

=SUM(IFERROR(A6:AE6*(MOD(COLUMN(A6:AE6),9)=0),0))

IFERROR 함수는 0 또는 #VALUE!일 경우 더하면 수식이 오류가 발생하므로 이럴 경우 값을 0으로 반환합니다.

6. 

=SUM(IFERROR(A6:AE6*(MOD(COLUMN(A6:AE6),9)=0),0))

모든 배열의 값을 더합니다.

7. Ctrl + Shift + Enter을 해서 배열수식으로 변환합니다.

두 날짜사이의 개월수 / 연도수 계산하기 DATEDIF

=DATEDIF(B2,A2,"M") : 두 날짜사이의 개월수를 계산한다.

=DATEDIF(B2,A2,"Y") : 두 날짜사이의 연도수를 계산한다.

DATEDIF(start_date,end_date,unit)

start_date  필수

지정 된 기간의 첫 번째 날짜나 시작 날짜를 나타내는 날짜입니다. 날짜는 따옴표로 묶인 텍스트 문자열(예: "2001-01-30"), 일련 번호(예: 1900 날짜 체계를 사용할 경우 2001년 1월 30일을 나타내는 값인 36921), 다른 수식 또는 함수의 결과(예: DATEVALUE("2001-01-30"))로 입력할 수 있습니다.

end_date  필수

기간의 마지막 날짜나 종료 날짜를 나타내는 날짜입니다.

Unit  

반환 하려는 정보의 종류입니다.

"Y" 해당 기간에 포함된 전체 연도 수

"M" 해당 기간에 포함된 전체 개월 수

"D" 해당 기간에 포함된 날짜 수

"MD" start_date와 end_date의 날짜 차이. 두 날짜의 월이나 연도는 무시됩니다.

중요: "MD" 인수에는 알려진 제한 사항이 있으므로 사용하지 않는 것이 좋습니다.

아래의 알려진 문제 섹션을 참조하세요.

"Ym" start_date와 end_date의 개월 차이. 두 날짜의 일과 연도는 무시됩니다.

"YD" start_date와 end_date의 날짜 차이. 두 날짜의 연도는 무시됩니다.

중복된 셀 강조하는 방법

조건부 서식 

=COUNTIF($A$1:$A$5,A1)<>1

아래 그림과 같이 A열에 빈셀이 있습니다.

A열에 숫자 1에서 10까지 입력되어 있고 중간 중간 빈셀이 있습니다.

1. 숫자 입력된 셀의 처음부터 끝까지 선택한다.

2. 홈 > 찾기 및 선택 > 이동옵션 또는

단축키 Ctrl + G, 또는  F5를 누르면 이동 창이 나타납니다.

2.1 옵션 클릭

2.2 빈셀 체크

2.3 빈셀만 선택이 됩니다.

3. 셀삭제 Ctrl + - 

4. 결과

사용자 지정 목록을 사용하여 데이터 정렬

기본 제공 사용자 지정 목록을 사용하면 요일 또는 월을 기준으로 데이터를 정렬할 수 있습니다. 또는 사전순으로 제대로 정렬되지 않는 다른 특징(예: 높음, 보통 및 낮음 또는 S, M, L, XL)을 기준으로 정렬하기 위한 사용자 지정 목록을 만들 수도 있습니다.

사용자 지정 목록을 사용하면 배달 월 또는 우선 순위를 기준으로 이 워크시트를 정렬할 수 있습니다.

기본 제공 사용자 지정 목록을 사용하여 요일 또는 월을 기준으로 정렬

다음 단계를 실행합니다.

1. 정렬할 열을 선택합니다.

   참고: 최상의 결과를 위해서는 각 열에 머리글이 있어야 합니다.

2. 리본에서 데이터 > 정렬을 클릭합니다.

   

3. 정렬 팝업 창의 정렬 기준 드롭 다운에서 정렬해야 하는 열을 선택합니다.

   예를 들어 위의 예제를 배달 날짜를 기준으로 다시 정렬하려면 정렬 기준에서 배달을 선택합니다.

   

4. 순서 드롭다운에서 사용자 지정 목록을 선택합니다.

   

5. 사용자 지정 목록 상자에서 원하는 목록을 선택한 다음 확인을 클릭하여 워크시트를 정렬합니다.

   

사용자 지정 목록 만들기

정렬할 사용자 지정 목록을 만들려면 다음 단계를 따르세요.

1. 워크시트의 한 열에 정렬 기준으로 사용할 값을 입력합니다. 정렬 기준을 정의하려는 순서대로 위에서 아래로 정렬합니다. 예를 들면 다음과 같습니다.

   

2. 이 목록에서 셀을 모두 선택하고 파일 > 옵션 > 고급을 클릭합니다.

3. 일반 섹션으로 스크롤하고 사용자 지정 목록 편집...
   
   

   

   을 클릭합니다.

4. 사용자 지정 목록 상자에서 가져오기를 클릭합니다.

   

팁: 높음, 중간, 낮음 같은 짧은 목록의 경우 사용자 지정 목록 상자의 목록 항목 상자에 직접 입력하는 것이 더 빠를 수 있습니다.