19가지 쉬운 Excel 팁, 트릭 및 바로 가기

때로는 Excel이 너무 뛰어나서 사실이 아닌 것 같습니다. 수식을 입력하기만 하면 수동으로 수행해야 하는 거의 모든 작업을 자동으로 수행할 수 있습니다.

데이터가 유사한 두 시트를 병합해야 합니까? 엑셀은 할 수 있습니다.

간단한 수학을 해야 합니까? 엑셀은 할 수 있습니다.

여러 셀의 정보를 결합해야 합니까? 엑셀은 할 수 있습니다.

이 게시물에서는 Excel 게임을 다음 단계로 끌어올리기 위해 지금 사용할 수 있는 최고의 팁, 트릭 및 바로 가기를 살펴보겠습니다. 고급 Excel 지식이 필요하지 않습니다.

엑셀이란?

Microsoft Excel은 스프레드시트를 사용하여 공식과 함수로 데이터 세트를 저장, 구성 및 추적하는 강력한 데이터 시각화 및 분석 소프트웨어입니다. Excel은 마케터, 회계사, 데이터 분석가 및 기타 전문가가 사용합니다. Microsoft Office 제품군의 일부입니다. 대안으로는 Google 스프레드시트 및 숫자가 있습니다.

여기에서 더 많은 Excel 대안을 찾아보세요.

Excel은 강력한 계산 능력으로 인해 주로 재무 문서를 만드는 데 사용됩니다. 회계사가 자동으로 합계, 평균 및 합계를 볼 수 있도록 하기 때문에 회계 사무실 및 팀에서 소프트웨어를 자주 찾을 수 있습니다. Excel을 사용하면 비즈니스 데이터를 쉽게 이해할 수 있습니다.

Excel은 주로 회계 도구로 알려져 있지만 모든 유형의 데이터를 추적하는 데 사용할 수 있기 때문에 모든 분야의 전문가, 특히 마케터가 Excel의 기능과 공식을 사용할 수 있습니다. 셀 수를 세거나 성능 수치를 복사하여 붙여넣는 데 몇 시간을 할애할 필요가 없습니다. Excel에는 일반적으로 프로세스 속도를 높이는 바로 가기 또는 빠른 수정 기능이 있습니다.

마케팅 요구 사항에 따라 아래에서 Excel 템플릿을 다운로드할 수도 있습니다.

템플릿을 다운로드한 후에는 소프트웨어 사용을 시작할 차례입니다. 먼저 기본 사항을 살펴보겠습니다.

엑셀 기초

Excel을 처음 사용하는 경우 익숙해지는 것이 좋습니다. 다음과 같은 것들이 있습니다.

• 처음부터 새 스프레드시트 만들기.
• 더하기, 빼기, 곱하기, 나누기와 같은 기본 계산을 실행합니다.
• 열 텍스트 및 제목 작성 및 서식 지정.
• Excel의 자동 채우기 기능을 사용합니다.
• 단일 열, 행 및 스프레드시트 추가 또는 삭제 (아래에서 여러 열과 행과 같은 항목을 추가하는 방법에 대해 알아보겠습니다.)
• 스프레드시트에서 스크롤할 때 열과 행 제목을 계속 표시하여 문서 아래로 이동할 때 채우고 있는 데이터를 알 수 있습니다.
• 데이터를 알파벳순으로 정렬합니다.

이 중 몇 가지를 더 자세히 살펴보겠습니다.

예를 들어 자동 채우기 가 중요한 이유는 무엇입니까?

기본적인 Excel 지식이 있는 경우 이 빠른 트릭을 이미 알고 있을 것입니다. 그러나 우리의 기반을 덮기 위해 자동 완성의 영광을 보여드리겠습니다. 이를 통해 값, 계열 및 수식을 비롯한 여러 유형의 데이터로 인접한 셀을 빠르게 채울 수 있습니다.

이 기능을 배포하는 방법에는 여러 가지가 있지만 채우기 핸들이 가장 쉬운 방법입니다. 소스로 사용할 셀을 선택하고 셀의 오른쪽 하단 모서리에 있는 채우기 핸들을 찾은 다음 채우기 핸들을 끌어서 채우려는 셀을 덮거나 두 번 클릭합니다.

마찬가지로 정렬 은 Excel에서 데이터를 구성할 때 알아야 하는 중요한 기능입니다.

때로는 조직이 전혀 없는 데이터 목록이 있을 수 있습니다. 마케팅 연락처 또는 블로그 게시물 목록을 내보냈을 수 있습니다. 어떤 경우라도, 엑셀의 정렬 기능 목록을 알파벳순으로 정렬하는 데 도움이 됩니다.

정렬하려는 열의 데이터를 클릭합니다. 그런 다음 도구 모음에서 "데이터" 탭을 클릭하고 왼쪽에서 "정렬" 옵션을 찾습니다. "A"가 "Z" 위에 있으면 해당 버튼을 한 번만 클릭하면 됩니다. "Z"가 "A" 위에 있으면 버튼을 두 번 클릭합니다. "A"가 "Z" 위에 있으면 목록이 알파벳 순서로 정렬됩니다. 그러나 "Z"가 "A" 위에 있으면 목록이 알파벳 역순으로 정렬됩니다.

다음으로 고급 기능과 함께 Excel의 기본 사항을 더 살펴보겠습니다.

엑셀 사용법

Excel을 사용하려면 행과 열에 데이터를 입력하기만 하면 됩니다. 그런 다음 수식과 함수를 사용하여 해당 데이터를 통찰력으로 전환합니다.

우리는 당신이 알아야 할 최고의 공식과 함수를 살펴볼 것입니다. 그러나 먼저 소프트웨어를 사용하여 만들 수 있는 문서 유형을 살펴보겠습니다. 그렇게 하면 일상에서 Excel을 사용하는 방법을 전반적으로 이해할 수 있습니다.

Excel에서 작성할 수 있는 문서

팀에서 실제로 Excel을 어떻게 사용할 수 있는지 잘 모르십니까? 만들 수 있는 문서 목록은 다음과 같습니다.

• 손익 계산서: Excel 스프레드시트를 사용하여 회사의 판매 활동 및 재무 상태를 추적할 수 있습니다.
• 대차 대조표: 대차 대조표는 Excel로 만들 수 있는 가장 일반적인 문서 유형 중 하나입니다. 이를 통해 회사의 재무 상태를 전체적으로 볼 수 있습니다.
• 캘린더: 스프레드시트 월간 캘린더를 생성하여 이벤트 또는 기타 날짜에 민감한 정보를 추적할 수 있습니다.

다음은 마케터를 위해 특별히 만들 수 있는 몇 가지 문서입니다.

이것은 Excel에서 만들 수 있는 마케팅 및 비즈니스 문서 유형 중 일부에 불과합니다. 마케팅, 송장 발행, 프로젝트 관리, 예산 책정 등에 지금 바로 사용할 수 있는 광범위한 Excel 템플릿 목록을 만들었습니다.

더 효율적으로 작업하고 지루한 수동 작업을 피하기 위해 알아야 할 몇 가지 Excel 수식과 함수가 있습니다.

엑셀 수식

데이터를 이해하는 데 사용할 수 있는 광범위한 Excel 수식에 압도되기 쉽습니다. Excel 사용을 막 시작하는 경우 학습 경로의 복잡성을 추가하지 않고도 다음 공식을 사용하여 몇 가지 복잡한 기능을 수행할 수 있습니다.

• 등호: 수식을 만들기 전에 결과를 표시할 셀에 등호 (=) 를 작성해야 합니다.
• 더하기 : 둘 이상의 셀 값을 더하려면 + 기호를 사용합니다. 예: =C5+D3 .
• 빼기 : 둘 이상의 셀 값을 빼려면 - 기호를 사용합니다. 예: =C5-D3 .
• 곱하기 : 두 개 이상의 셀 값을 곱하려면 * 기호를 사용합니다. 예: =C5*D3 .
• 나누기 : 둘 이상의 셀 값을 나누려면 / 기호를 사용합니다. 예: =C5/D3 .

이 모든 것을 합치면 하나의 셀에서 더하기, 빼기, 곱하기, 나누기를 모두 수행하는 수식을 만들 수 있습니다. 예: =(C5-D3)/((A5+B6)*3) .

더 복잡한 수식의 경우 실수로 PEMDAS 연산 순서를 사용하지 않도록 식 주위에 괄호를 사용해야 합니다. 수식에 일반 숫자를 사용할 수 있습니다.

엑셀 함수

Excel 함수는 일반적인 수식에서 사용하는 일부 작업을 자동화합니다. 예를 들어 + 기호를 사용하여 셀 범위를 추가하는 대신 SUM 함수를 사용합니다. 계산 및 작업을 자동화하는 데 도움이 되는 몇 가지 기능을 더 살펴보겠습니다.

• SUM: SUM 함수는 셀 또는 숫자 범위를 자동으로 더합니다. 합계를 완성하려면 시작 셀과 마지막 셀을 콜론으로 사이에 입력합니다. 다음은 SUM(Cell1:Cell2) 입니다. 예: =SUM(C5:C30) .
• AVERAGE: AVERAGE 함수는 셀 범위의 값을 평균화합니다. 구문은 SUM 함수 AVERAGE(Cell1:Cell2)와 동일합니다. 예: =AVERAGE(C5:C30) .
• IF: IF 함수를 사용하면 논리적 테스트를 기반으로 값을 반환할 수 있습니다. 구문은 다음과 같습니다. IF(logical_test, value_if_true, [value_if_false]) . 예: =IF(A2>B2,"예산 초과","OK") .
• VLOOKUP: VLOOKUP 기능을 사용하면 시트의 행에서 모든 항목을 검색할 수 있습니다. 구문은 VLOOKUP(조회 값, 테이블 배열, 열 번호, 대략적인 일치(TRUE) 또는 정확히 일치(FALSE)) 입니다. 예: =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).
• INDEX: INDEX 함수는 범위 내에서 값을 반환합니다. 구문은 다음과 같습니다. INDEX(array, row_num, [column_num]) .
• MATCH: MATCH 함수는 셀 범위에서 특정 항목을 찾고 해당 항목의 위치를 ​​반환합니다. INDEX 기능과 함께 사용할 수 있습니다. 구문은 MATCH(lookup_value, lookup_array, [match_type]) 입니다.
• COUNTIF: COUNTIF 함수는 특정 기준을 충족하거나 특정 값을 갖는 셀의 수를 반환합니다. 구문은 COUNTIF(범위, 기준)입니다. 예: =COUNTIF(A2:A5,”런던”).

자, 본론으로 들어갈 준비가 되었나요? 해보자. (그리고 그곳에 있는 모든 해리포터 팬 여러분... 미리 환영합니다.)

참고 : GIF 및 영상은 이전 버전의 Excel에서 가져온 것입니다. 해당되는 경우 최신 버전과 이전 버전의 Excel 사용자 모두에게 지침을 제공하도록 복사본이 업데이트되었습니다.

1. 피벗 테이블을 사용하여 데이터를 인식하고 이해합니다.

피벗 테이블은 스프레드시트의 데이터를 재구성하는 데 사용됩니다. 그들은 당신이 가지고 있는 데이터를 변경하지 않지만 당신이 원하는 것에 따라 값을 요약하고 스프레드시트의 다른 정보를 비교할 수 있습니다.

예를 들어 보겠습니다. 호그와트의 각 집에 몇 명이 있는지 살펴보고 싶다고 가정해 봅시다. 당신은 내가 너무 많은 데이터를 가지고 있지 않다고 생각할 수도 있지만, 더 긴 데이터 세트의 경우 이것이 유용할 것입니다.

피벗 테이블을 생성하려면 데이터 > 피벗 테이블 로 이동합니다. 최신 버전의 Excel을 사용하는 경우 삽입 > 피벗 테이블 로 이동합니다. Excel은 자동으로 피벗 테이블을 채우지만 언제든지 데이터 순서를 변경할 수 있습니다. 그런 다음 4가지 옵션 중에서 선택할 수 있습니다.

• 보고서 필터 : 데이터 세트의 특정 행만 볼 수 있습니다. 예를 들어 집별로 필터를 만들고 싶다면 모든 학생 대신 Gryffindor에 있는 학생만 포함하도록 선택할 수 있습니다.
• 열 레이블 : 데이터 세트의 헤더가 됩니다.
• 행 레이블 : 데이터세트의 행이 될 수 있습니다. 행 및 열 레이블 모두 열의 데이터를 포함할 수 있습니다.
• 값 : 이 섹션에서는 데이터를 다르게 볼 수 있습니다. 숫자 값을 가져오는 대신 합계, 개수, 평균, 최대, 최소, 개수를 계산하거나 데이터를 사용하여 몇 가지 다른 조작을 수행할 수 있습니다. 실제로 기본적으로 필드를 값으로 끌면 항상 계산됩니다.

각 하우스의 학생 수를 계산하고 싶기 때문에 피벗 테이블 작성기로 이동하여 하우스 열을 행 레이블과 값으로 끌어다 놓습니다. 이것은 각 집과 관련된 학생 수를 합산합니다.

2. 둘 이상의 행이나 열을 추가합니다.

데이터를 가지고 놀다 보면 계속해서 더 많은 행과 열을 추가해야 한다는 사실을 알게 될 수도 있습니다. 때로는 수백 개의 행을 추가해야 할 수도 있습니다. 이 작업을 하나씩 수행하는 것은 매우 지루할 것입니다. 운 좋게도 항상 더 쉬운 방법이 있습니다.

스프레드시트에 여러 행이나 열을 추가하려면 추가하려는 동일한 수의 기존 행이나 열을 강조 표시합니다. 그런 다음 마우스 오른쪽 버튼을 클릭하고 "삽입"을 선택합니다.

아래 예에서는 3개의 행을 더 추가하고 싶습니다. 세 행을 강조 표시한 다음 삽입을 클릭하면 스프레드시트에 빈 행 세 개를 빠르고 쉽게 추가할 수 있습니다.

3. 필터를 사용하여 데이터를 단순화하십시오.

매우 큰 데이터 세트를 볼 때 일반적으로 모든 단일 행을 동시에 볼 필요는 없습니다. 특정 기준에 맞는 데이터만 보고 싶을 때가 있습니다.

필터가 들어가는 곳입니다.

필터를 사용하면 한 번에 특정 행만 보도록 데이터를 줄일 수 있습니다. Excel에서는 데이터의 각 열에 필터를 추가할 수 있으며 여기에서 한 번에 보려는 셀을 선택할 수 있습니다.

아래의 예를 살펴보겠습니다. 데이터 탭을 클릭하고 "필터"를 선택하여 필터를 추가합니다. 열 머리글 옆에 있는 화살표를 클릭하면 데이터를 오름차순 또는 내림차순으로 구성할지 여부와 표시하려는 특정 행을 선택할 수 있습니다.

내 Harry Potter 예에서 Gryffindor의 학생들만 보고 싶다고 가정해 보겠습니다. Gryffindor 필터를 선택하면 다른 행이 사라집니다.

전문가 팁 : 필터가 켜져 있을 때 스프레드시트의 값을 복사하여 붙여넣으면 다른 스프레드시트에서 추가 분석을 수행할 수 있습니다.

4. 중복 데이터 포인트 또는 세트를 제거합니다.

더 큰 데이터 세트에는 중복 콘텐츠가 있는 경향이 있습니다. 회사에 여러 연락처 목록이 있고 보유한 회사 수만 보고 싶을 수 있습니다. 이와 같은 상황에서는 중복을 제거하는 것이 매우 편리합니다.

중복 항목을 제거하려면 중복 항목을 제거하려는 행이나 열을 강조 표시합니다. 그런 다음 데이터 탭으로 이동하여 "중복 항목 제거"(이전 버전의 Excel에서는 도구 하위 헤더 아래에 있음)를 선택합니다. 작업할 데이터를 확인하는 팝업이 나타납니다. "중복 제거"를 선택하면 됩니다.

이 기능을 사용하여 중복 열 값을 기반으로 전체 행을 제거할 수도 있습니다. 따라서 Harry Potter 정보가 포함된 3개의 행이 있고 하나만 표시해야 하는 경우 전체 데이터 세트를 선택한 다음 이메일을 기반으로 중복을 제거할 수 있습니다. 결과 목록에는 중복 없이 고유한 이름만 있습니다.

5. 행을 열로 바꿉니다.

스프레드시트에 데이터 행이 있는 경우 실제로 해당 행 중 하나의 항목을 열로(또는 그 반대로) 변환하기로 결정할 수 있습니다. 각 개별 헤더를 복사하여 붙여넣는 데 많은 시간이 걸리지만 조바꿈 기능을 사용하면 행 데이터를 열로 옮기거나 그 반대로 하면 됩니다.

행으로 바꾸려는 열을 강조 표시하여 시작합니다. 마우스 오른쪽 버튼을 클릭한 다음 "복사"를 선택합니다. 그런 다음 스프레드시트에서 첫 번째 행이나 열을 시작할 셀을 선택합니다. 셀을 마우스 오른쪽 버튼으로 클릭한 다음 "선택하여 붙여넣기"를 선택합니다. 모듈이 나타납니다. 하단에 조옮김 옵션이 표시됩니다. 확인란을 선택하고 확인을 선택합니다. 이제 열이 행으로 또는 그 반대로 전송됩니다.

최신 버전의 Excel에서는 팝업 대신 드롭다운이 나타납니다.

6. 열 간에 텍스트 정보를 분할합니다.

한 셀에 있는 정보를 두 개의 다른 셀로 분할하려면 어떻게 해야 합니까? 예를 들어 이메일 주소를 통해 누군가의 회사 이름을 꺼내고 싶을 수 있습니다. 또는 이메일 마케팅 템플릿을 위해 누군가의 전체 이름을 성과 이름으로 분리하고 싶을 수도 있습니다.

Excel 덕분에 둘 다 가능합니다. 먼저 분할하려는 열을 강조 표시합니다. 그런 다음 데이터 탭으로 이동하여 "텍스트를 열로"를 선택합니다. 추가 정보가 있는 모듈이 나타납니다.

먼저 "Delimited" 또는 "Fixed Width"를 선택해야 합니다.

• "구분된"은 쉼표, 공백 또는 탭과 같은 문자를 기반으로 열을 분할하려는 것을 의미합니다.
• "고정 너비"는 분할이 발생하려는 모든 열의 정확한 위치를 선택하려는 것을 의미합니다.

아래 예제의 경우 "Delimited"를 선택하여 전체 이름을 성과 이름으로 구분할 수 있습니다.

그런 다음 구분 기호를 선택할 차례입니다. 탭, 세미콜론, 쉼표, 공백 등이 될 수 있습니다. (예를 들어 "Something other"는 이메일 주소에 사용된 "@" 기호일 수 있습니다.) 이 예에서는 공백을 선택하겠습니다. 그러면 Excel에서 새 열의 모양을 미리 볼 수 있습니다.

미리보기가 마음에 들면 "다음"을 누르십시오. 이 페이지에서는 원하는 경우 고급 형식을 선택할 수 있습니다. 완료되면 "마침"을 클릭합니다.

7. 간단한 계산에는 공식을 사용하십시오.

매우 복잡한 계산을 수행하는 것 외에도 Excel을 사용하면 데이터의 더하기, 빼기, 곱하기 또는 나누기와 같은 간단한 산술 연산을 수행할 수 있습니다.

• 추가하려면 + 기호를 사용합니다.
• 빼려면 - 기호를 사용하십시오.
• 곱하려면 * 기호를 사용하십시오.
• 나누려면 / 기호를 사용하십시오.

괄호를 사용하여 특정 계산이 먼저 수행되도록 할 수도 있습니다. 아래 예(10+10*10)에서 두 번째와 세 번째 10을 곱한 다음 10을 더합니다. 그러나 (10+10)*10으로 만들면 첫 번째와 두 번째 10을 먼저 더합니다. .

8. 세포에 있는 숫자의 평균을 구하십시오.

숫자 집합의 평균을 원하면 수식을 사용할 수 있습니다 =AVERAGE(Cell1:Cell2) . 숫자 열을 합산하려면 =SUM(Cell1:Cell2) 수식을 사용할 수 있습니다.

9. 조건부 서식을 사용하여 데이터에 따라 셀의 색상이 자동으로 변경되도록 합니다.

조건부 서식을 사용하면 셀 내의 정보를 기반으로 셀의 색상을 변경할 수 있습니다. 예를 들어, 평균 이상이거나 스프레드시트 데이터의 상위 10%에 속하는 특정 숫자에 플래그를 지정하려는 경우 그렇게 할 수 있습니다. Excel에서 서로 다른 행 간의 공통점에 색상을 지정하려면 그렇게 할 수 있습니다. 이렇게 하면 중요한 정보를 빠르게 볼 수 있습니다.

시작하려면 조건부 서식을 사용할 셀 그룹을 강조 표시합니다. 그런 다음 홈 메뉴에서 "조건부 서식"을 선택하고 드롭다운에서 논리를 선택합니다. (다른 것을 원할 경우 고유한 규칙을 만들 수도 있습니다.) 서식 지정 규칙에 대한 추가 정보를 제공하라는 창이 팝업됩니다. 완료되면 "확인"을 선택하면 결과가 자동으로 나타나는 것을 볼 수 있습니다.

10. IF Excel 공식을 사용하여 특정 Excel 기능을 자동화합니다.

때때로 우리는 값이 나타나는 횟수를 계산하고 싶지 않습니다. 대신 해당 정보가 있는 해당 셀이 있는 경우 다른 정보를 셀에 입력하려고 합니다.

예를 들어 아래 상황에서 그리핀도르 집에 속한 모든 사람에게 10점을 주고 싶습니다. 각 그리핀도르 학생 이름 옆에 10을 수동으로 입력하는 대신 IF Excel 공식을 사용하여 학생이 그리핀도르에 있으면 10점을 받아야 한다고 말할 수 있습니다.

공식은 IF(logical_test, value_if_true, [value_if_false])입니다.

아래에 표시된 예: =IF(D2="Gryffindor","10","0")

일반적으로 수식은 IF(Logical Test, value of true, value of false)가 됩니다. 각 변수에 대해 알아보겠습니다.

• Logical_Test : 논리적 테스트는 문의 "IF" 부분입니다. 이 경우 논리는 D2="Gryffindor"입니다. 왜냐하면 학생에 해당하는 셀이 "Gryffindor"라고 표시되도록 하기 때문입니다. 여기에 Gryffindor를 큰따옴표로 묶어야 합니다.
• Value_if_True : 값이 true인 경우 셀에 표시하고자 하는 것입니다. 이 경우 학생이 10점을 받았음을 나타내기 위해 셀에 "10"이 표시되기를 원합니다. 결과를 숫자 대신 텍스트로 표시하려는 경우에만 따옴표를 사용하십시오.
• Value_if_False : 값이 false인 경우 셀에 표시하려는 것입니다. 이 경우 Gryffindor에 있지 않은 학생의 경우 셀에 "0"이 표시되기를 원합니다. 결과를 숫자 대신 텍스트로 표시하려는 경우에만 따옴표를 사용하십시오.

참고 : 위의 예에서 저는 Gryffindor에 있는 모든 사람에게 10점을 부여했습니다. 나중에 총 포인트 수를 합산하고 싶다면 10이 따옴표 안에 있으므로 Excel에서 합산할 수 있는 숫자가 아닌 텍스트로 만들기 때문에 할 수 없습니다.

IF 함수의 진정한 힘은 여러 IF 문을 문자열로 묶을 때 나옵니다. 함께, 또는 둥지를 틀 수 있습니다. 이를 통해 여러 조건을 설정하고 보다 구체적인 결과를 얻을 수 있으며 궁극적으로 데이터를 보다 관리하기 쉬운 청크로 구성할 수 있습니다.

범위는 더 나은 분석을 위해 데이터를 분할하는 한 가지 방법입니다. 예를 들어 데이터를 10, 11~50 또는 51~100보다 작은 값으로 분류할 수 있습니다. 실제로는 다음과 같습니다.

=IF(B3<11,"10 이하",IF(B3<51,"11~50",IF(B3<100,"51~100")))

시행 착오가 필요할 수 있지만 일단 익숙해지면 IF 수식이 새로운 Excel의 가장 친한 친구가 될 것입니다.

11. 달러 기호를 사용하여 이동 위치에 관계없이 한 셀의 공식을 동일하게 유지합니다.

Excel 수식에서 달러 기호를 본 적이 있습니까? 수식에 사용될 때 미국 달러를 나타내지 않습니다. 대신, 인접한 행에서 동일한 수식을 복사하더라도 정확한 열과 행이 동일하게 유지되도록 합니다.

예를 들어 셀 C5에서 셀 A5를 참조할 때 셀 참조는 기본적으로 상대적입니다. 이 경우 실제로 왼쪽으로 5개 열(C 빼기 A)과 같은 행(5)에 있는 셀을 참조하고 있습니다. 이것을 상대 공식이라고 합니다. 한 셀에서 다른 셀로 상대 수식을 복사하면 이동한 위치에 따라 수식의 값이 조정됩니다. 그러나 때때로 우리는 그 값이 이동 여부에 관계없이 동일하게 유지되기를 원합니다. 그리고 우리는 공식을 절대 공식으로 변환함으로써 그렇게 할 수 있습니다.

상대 공식(=A5+C5)을 절대 공식으로 변경하려면 (=$A$5+$C$5) 와 같이 행과 열 값 앞에 달러 기호를 붙입니다. (여기에서 Microsoft Office의 지원 페이지에 대해 자세히 알아보세요.)

12. VLOOKUP 기능을 사용하여 시트의 한 영역에서 다른 영역으로 데이터를 가져옵니다.

하나의 스프레드시트로 결합하려는 두 개의 다른 스프레드시트에 두 개의 데이터 세트가 있었던 적이 있습니까?

예를 들어 한 스프레드시트의 이메일 주소 옆에 사람들의 이름 목록이 있고 다른 스프레드시트의 회사 이름 옆에 동일한 사람들의 이메일 주소 목록이 있을 수 있지만 이름, 이메일 주소 및 회사 이름이 필요합니다. 그 사람들의 한 장소에 나타납니다.

이와 같이 데이터 세트를 많이 결합해야 하며 그럴 때 VLOOKUP이 가장 많이 사용되는 공식입니다.

그러나 수식을 사용하기 전에 두 위치에 동일하게 나타나는 열이 하나 이상 있는지 절대적으로 확인하십시오. 데이터 세트를 조사하여 정보를 결합하는 데 사용하는 데이터 열이 추가 공백 없이 정확히 동일한지 확인합니다.

공식: =VLOOKUP(조회 값, 테이블 배열, 열 번호, 대략적인 일치(TRUE) 또는 정확히 일치(FALSE))

아래 예제의 변수가 있는 공식: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)

이 공식에는 여러 변수가 있습니다. 다음은 Sheet 1과 Sheet 2의 정보를 Sheet 1에 결합하려는 경우에 해당됩니다.

• 조회 값 : 두 스프레드시트에 있는 동일한 값입니다. 첫 번째 스프레드시트에서 첫 번째 값을 선택합니다. 다음 예에서 이는 목록의 첫 번째 이메일 주소 또는 셀 2(C2)를 의미합니다.
• 테이블 배열 : 테이블 배열은 시트 1의 조회 값(이 예에서는 이메일 주소)과 동일한 데이터 열과 열을 포함하여 데이터를 가져올 시트 2의 열 범위입니다. 시트 1에 복사하려는 데이터의 비율입니다. 이 예에서는 "시트2!A:B"입니다. "A"는 Sheet 2의 열 A를 의미하며 Sheet 1의 조회 값(이메일)과 동일한 데이터가 나열되는 Sheet 2의 열입니다. "B"는 시트 1로 번역하려는 시트 2에서만 사용할 수 있는 정보가 포함된 열 B를 의미합니다.
• 열 번호 : Excel에 시트 1에 복사하려는 새 데이터가 있는 열을 알려줍니다. 이 예에서는 "집"이 있는 열입니다. "집"은 범위에서 두 번째 열입니다. 열(테이블 배열)이므로 열 번호는 2입니다. [ 참고 : 범위는 두 개 이상의 열이 될 수 있습니다. 예를 들어, 시트 2에 이메일, 연령, 하우스의 세 열이 있고 여전히 시트 1에 하우스를 가져오려는 경우 VLOOKUP을 사용할 수 있습니다. "2"를 "3"으로 변경하면 세 번째 열의 값을 다시 가져옵니다. =VLOOKUP(C2:Sheet2!A:C,3,false).]
• 근사 일치(TRUE) 또는 완전 일치(FALSE) : FALSE를 사용하여 정확한 값 일치만 가져오도록 합니다. TRUE를 사용하면 함수가 대략적인 일치 항목을 가져옵니다.

아래 예에서 시트 1과 시트 2에는 동일한 사람들에 대한 서로 다른 정보를 설명하는 목록이 포함되어 있으며 둘 사이의 공통 스레드는 이메일 주소입니다. Sheet 2의 모든 주택 정보가 Sheet 1로 변환되도록 두 데이터 세트를 결합한다고 가정해 보겠습니다.

따라서 수식을 입력하면 =VLOOKUP(C2,Sheet2!A:B,2,FALSE) 모든 주택 데이터를 시트 1로 가져옵니다.

VLOOKUP은 동일한 데이터가 포함된 열의 오른쪽에 있는 두 번째 시트의 값만 가져옵니다. 이로 인해 몇 가지 제한 사항이 발생할 수 있으므로 일부 사람들은 INDEX 및 MATCH 기능을 대신 사용하는 것을 선호합니다.

13. INDEX 및 MATCH 공식을 사용하여 가로 열에서 데이터를 가져옵니다.

VLOOKUP과 마찬가지로 INDEX 및 MATCH 함수는 다른 데이터 세트의 데이터를 하나의 중앙 위치로 가져옵니다. 주요 차이점은 다음과 같습니다.

• VLOOKUP은 훨씬 간단한 공식입니다. 수천 번의 조회가 필요한 대규모 데이터 세트로 작업하는 경우 INDEX 및 MATCH 함수를 사용하면 Excel에서 로드 시간을 크게 줄일 수 있습니다.
• INDEX 및 MATCH 수식은 오른쪽에서 왼쪽으로 작동하는 반면 VLOOKUP 수식은 왼쪽에서 오른쪽 조회로만 작동합니다. 즉, 결과 열의 오른쪽에 조회 열이 있는 조회를 수행해야 하는 경우 VLOOKUP을 수행하기 위해 해당 열을 재정렬해야 합니다. 이는 대규모 데이터세트에서 지루하거나 오류를 유발할 수 있습니다.

따라서 시트 1과 시트 2의 정보를 시트 1에 결합하고 싶지만 시트 1과 시트 2의 열 값이 같지 않은 경우 VLOOKUP을 수행하려면 열을 전환해야 합니다. 이 경우 대신 INDEX 및 MATCH를 선택합니다.

예를 들어 보겠습니다. Sheet 1에는 사람들의 이름과 Hogwarts 이메일 주소 목록이 포함되어 있고 Sheet 2에는 사람들의 이메일 주소 목록과 각 학생이 가지고 있는 Patronus가 포함되어 있다고 가정해 보겠습니다. (해리포터 팬이 아닌 경우 모든 마녀나 마법사에게는 "패트로누스"라는 동물 보호자가 관련되어 있습니다.) 두 시트에 모두 있는 정보는 이메일 주소가 포함된 열이지만 이 이메일 주소 열은 각 시트의 다른 열 번호에 있습니다. VLOOKUP 대신 INDEX 및 MATCH 수식을 사용하므로 열을 전환할 필요가 없습니다.

그렇다면 공식은 무엇일까요? 수식은 실제로 INDEX 수식 안에 중첩된 MATCH 수식입니다. 여기에서 다른 색상을 사용하여 MATCH 공식을 차별화했음을 알 수 있습니다.

공식: =INDEX(테이블 배열, MATCH 공식)

이것은 다음이 됩니다: =INDEX(테이블 배열, MATCH( lookup_value, lookup_array))

아래 예제의 변수가 있는 공식: =INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0)))

다음은 변수입니다.

• Table Array : Sheet 1으로 가져오려는 새 데이터가 포함된 Sheet 2의 열 범위입니다. 이 예에서 "A"는 각 사람에 대한 "Patronus" 정보가 포함된 열 A를 의미합니다.
• 조회 값 : 두 스프레드시트에서 동일한 값을 포함하는 시트 1의 열입니다. 다음 예에서 이것은 열 C인 시트 1의 "이메일" 열을 의미합니다. 따라서: Sheet1!C:C.
• 조회 배열 : 두 스프레드시트에서 동일한 값을 포함하는 시트 2의 열입니다. 다음 예에서 이것은 Sheet 2의 "email" 열을 참조하며 이는 C열이기도 합니다. 따라서 Sheet2!C:C입니다.

변수를 정확히 파악했으면 시트 1에 있는 빈 Patronus 열의 맨 위 셀에 INDEX 및 MATCH 공식을 입력합니다. 여기에서 결합된 정보를 표시할 수 있습니다.

14. COUNTIF 함수를 사용하여 Excel에서 셀 범위의 단어나 숫자를 계산하도록 합니다.

특정 값이나 숫자가 나타나는 빈도를 수동으로 계산하는 대신 Excel에서 자동으로 작업을 수행합니다. COUNTIF 함수를 사용하면 Excel에서 단어나 숫자가 셀 범위에 나타나는 횟수를 계산할 수 있습니다.

예를 들어 "Gryffindor"라는 단어가 내 데이터 세트에 나타나는 횟수를 세고 싶다고 가정해 보겠습니다.

공식: =COUNTIF(범위, 기준)

아래 예제의 변수가 있는 공식: =COUNTIF(D:D,”Gryffindor”)

이 공식에는 몇 가지 변수가 있습니다.

• 범위 : 수식이 포함할 범위입니다. 이 경우 하나의 열에만 초점을 맞추므로 "D:D"를 사용하여 첫 번째 열과 마지막 열이 모두 D임을 나타냅니다. C와 D 열을 보고 있는 경우 "C:D"를 사용합니다. .”
• 기준 : Excel에서 계산할 텍스트의 수 또는 조각. 결과를 숫자 대신 텍스트로 표시하려는 경우에만 따옴표를 사용하십시오. 이 예에서 기준은 "Gryffindor"입니다.

아무 셀에나 COUNTIF 수식을 입력하고 "Enter"를 누르기만 하면 데이터 세트에 "Gryffindor"라는 단어가 몇 번 나타나는지 표시됩니다.

15. &를 사용하여 셀을 결합합니다.

데이터베이스는 데이터를 가능한 한 정확하게 만들기 위해 데이터를 분할하는 경향이 있습니다. 예를 들어, 개인의 전체 이름을 표시하는 열을 사용하는 대신 데이터베이스의 데이터를 이름으로 포함하고 성을 별도 열에 포함할 수 있습니다. 또는 시, 주 및 우편번호로 구분된 사람의 위치가 있을 수 있습니다. Excel에서는 함수의 "&" 기호를 사용하여 데이터가 다른 셀을 하나의 셀로 결합할 수 있습니다.

아래 예제의 변수가 있는 공식: =A2&” “&B2

예제를 사용하여 공식을 함께 살펴 보겠습니다. 이름과 성을 단일 열의 전체 이름으로 결합하려는 척합니다. 이렇게 하려면 먼저 전체 이름을 표시할 빈 셀에 커서를 놓습니다. 다음으로, 이름이 포함된 하나의 셀을 강조 표시하고 "&" 기호를 입력한 다음 해당 성이 있는 셀을 강조 표시합니다.

하지만 끝이 아닙니다. =A2&B2만 입력하면 그 사람의 이름과 성 사이에 공백이 없습니다. 필요한 공간을 추가하려면 =A2&” “&B2 기능을 사용하십시오. 공백 주위의 따옴표는 Excel에서 이름과 성 사이에 공백을 넣으라고 지시합니다.

여러 행에 대해 이를 적용하려면 예제와 같이 첫 번째 셀의 모서리를 아래쪽으로 드래그하기만 하면 됩니다.

16. 확인란을 추가합니다.

Excel 시트를 사용하여 고객 데이터를 추적하고 수량화할 수 없는 항목을 감독하려는 경우 열에 확인란을 삽입할 수 있습니다.

For example, if you're using an Excel sheet to manage your sales prospects and want to track whether you called them in the last quarter, you could have a “Called this quarter?” column and check off the cells in it when you've called the respective client.

방법은 다음과 같습니다.

Highlight a cell you'd like to add checkboxes to in your spreadsheet. Then, click DEVELOPER. Then, under FORM CONTROLS, click the checkbox or the selection circle highlighted in the image below.

Once the box appears in the cell, copy it, highlight the cells you also want it to appear in, and then paste it.

17. Hyperlink a cell to a website.

If you're using your sheet to track social media or website metrics, it can be helpful to have a reference column with the links each row is tracking. If you add a URL directly into Excel, it should automatically be clickable. But, if you have to hyperlink words, such as a page title or the headline of a post you're tracking, here's how.

Highlight the words you want to hyperlink, then press Shift K. From there a box will pop up allowing you to place the hyperlink URL. Copy and paste the URL into this box and hit or click Enter.

If the key shortcut isn't working for any reason, you can also do this manually by highlighting the cell and clicking Insert > Hyperlink .

18. Add drop-down menus.

Sometimes, you'll be using your spreadsheet to track processes or other qualitative things. Rather than writing words into your sheet repetitively, such as “Yes”, “No”, “Customer Stage”, “Sales Lead”, or “Prospect”, you can use dropdown menus to quickly mark descriptive things about your contacts or whatever you're tracking.

Here's how to add drop-downs to your cells.

Highlight the cells you want the drop-downs to be in, then click the Data menu in the top navigation and press Validation.

From there, you'll see a Data Validation Settings box open. Look at the Allow options, then click Lists and select Drop-down List. Check the In-Cell dropdown button, then press OK.

19. Use the format painter.

As you've probably noticed, Excel has a lot of features to make crunching numbers and analyzing your data quick and easy. But if you ever spent some time formatting a sheet to your liking, you know it can get a bit tedious.

Don't waste time repeating the same formatting commands over and over again. Use the format painter to easily copy the formatting from one area of the worksheet to another. To do so, choose the cell you'd like to replicate, then select the format painter option (paintbrush icon) from the top toolbar.

엑셀 키보드 단축키

Creating reports in Excel is time-consuming enough. How can we spend less time navigating, formatting, and selecting items in our spreadsheet? 물어봐주셔서 기쁩니다. There are a ton of Excel shortcuts out there, including some of our favorites listed below.

Create a New Workbook

PC: Ctrl-N | Mac: Command-N

Select Entire Row

PC: Shift-Space | Mac: Shift-Space

Select Entire Column

PC: Ctrl-Space | Mac: Control-Space

Select Rest of Column

PC: Ctrl-Shift-Down/Up | Mac: Command-Shift-Down/Up

Select Rest of Row

PC: Ctrl-Shift-Right/Left | Mac: Command-Shift-Right/Left

하이퍼링크 추가

PC: Ctrl-K | Mac: Command-K

Open Format Cells Window

PC: Ctrl-1 | Mac: Command-1

Autosum Selected Cells

PC: Alt-= | Mac: Command-Shift-T

Other Excel Help Resources

Use Excel to Automate Processes in Your Team

Even if you're not an accountant, you can still use Excel to automate tasks and processes in your team. With the tips and tricks we shared in this post, you'll be sure to use Excel to its fullest extent and get the most out of the software to grow your business.

Editor's Note: This post was originally published in August 2017 but has been updated for comprehensiveness.