MS Excel에서 작업하는 방법. MS Excel에서 작업하는 방법 Excel의 칩

앤서니 도마니코. Excel 고급 사용자를 위한 11가지 요령 PC월드.

피벗 테이블에서 Power View에 이르기까지 이러한 기능을 알고 있으면 스프레드시트 전문가 대열에 합류하는 데 도움이 됩니다.

사용자 마이크로 소프트 엑셀전자는 작은 테이블을 간신히 관리할 수 있는 반면, 후자는 복잡한 차트, 강력한 데이터 분석, 공식 및 매크로의 효과적인 적용 마법으로 동료를 놀라게 합니다. 이 기사에서 살펴볼 11가지 요령은 두 번째 그룹의 정회원이 되는 데 도움이 될 것입니다.

VLOOKUP을 사용하면 여러 시트에 흩어져 있거나 다른 워크시트에 저장된 데이터를 캡처할 수 있습니다. 엑셀 통합 문서합계 보고 및 계산을 위해 한 곳에 보관합니다.

소매점에서 판매되는 상품을 취급한다고 가정해 보겠습니다. 각 항목에는 일반적으로 "VPR"에 대한 링크로 사용할 수 있는 고유한 인벤토리 번호가 할당됩니다. VLOOKUP 수식은 다른 시트에서 해당 식별자를 찾아 제품 설명, 가격, 재고 수준 및 기타 데이터를 통합 문서의 지정된 위치로 대체합니다.

"VLOOKUP" 기능은 예를 들어 사용 가능한 분류 목록이 포함된 대형 테이블에서 정보를 찾는 데 도움이 됩니다.

"VLOOKUP" 함수를 수식에 삽입하고 첫 번째 인수에 원하는 연결 값(1)을 지정합니다. 두 번째 인수에서 선택해야 하는 셀 범위(2)를 지정하고, 세 번째 인수에는 데이터를 대체할 열의 번호를 지정하고, 네 번째 인수에는 원하는 경우 FALSE 값을 입력합니다. 정확히 일치하는 항목을 찾거나 가장 가까운 근사값( 4)을 원하면 TRUE를 찾습니다.

차트 만들기

차트를 만들려면 다음을 입력하십시오. 엑셀 데이터열 제목(1)을 표시하고 "삽입" 탭(2)에서 "차트" 항목을 선택한 다음 원하는 차트 유형을 지정합니다. Excel 2013에는 입력한 데이터와 일치하는 유형이 포함된 권장 차트 탭(3)이 있습니다. 차트의 일반적인 특성을 결정한 후 Excel은 "디자이너" 탭을 엽니다. 여기에서 차트가 더 미세하게 조정됩니다. 여기에 존재하는 엄청난 수의 매개 변수를 사용하면 다음과 같은 다이어그램을 제공할 수 있습니다. 모습당신이 필요합니다.

Excel 2013에는 입력한 데이터와 일치하는 차트 유형을 표시하는 권장 차트 탭이 있습니다.

함수 "IF" 및 "IFERROR"

"IF" 및 "IFERROR"는 가장 널리 사용되는 Excel 함수 중 하나입니다. IF 함수를 사용하면 조건이 충족될 때 하나의 값을 평가하고 조건이 충족되지 않을 때 다른 값을 평가하는 조건부 수식을 정의할 수 있습니다. 예를 들어, 시험에서 80점 이상(C열에 표시됨) 점수를 받은 학생에게는 합격 속성이 지정될 수 있고 79점 이하인 학생에게는 불합격 속성이 지정될 수 있습니다.

IFERROR 함수는 보다 일반적인 IF 함수의 특별한 경우입니다. 수식 계산 중에 오류가 발생한 경우 특정 값(또는 빈 값)을 반환합니다. 예를 들어 다른 시트나 테이블에서 VLOOKUP 함수를 실행할 때 "IFERROR" 함수는 "VLOOKUP"이 첫 번째 인수로 지정된 원하는 매개 변수를 찾지 못하는 경우 빈 값을 반환할 수 있습니다.

IF 함수는 지정한 조건에 따라 결과를 계산합니다.

피벗 테이블

피벗 테이블은 기본적으로 항목 수를 계산하고 사용자 정의 참조 지점을 기반으로 평균, 합계 및 기타 함수를 계산할 수 있는 요약 테이블입니다. Excel 2013에는 필요한 데이터를 표시하는 테이블을 쉽게 만들 수 있는 권장 피벗 테이블이 도입되었습니다.

예를 들어, 나이를 기준으로 학생의 GPA를 계산하려면 나이 필드를 행 섹션(1)으로, 채점 필드를 값 섹션(2)으로 이동합니다. 값 메뉴에서 "값 필드 옵션"을 선택하고 작업을 "평균"으로 설정합니다(3). 같은 방식으로 다른 범주에 대한 합계를 계산할 수 있습니다. 예를 들어 성별에 따라 합격 및 불합격 시험 수를 계산할 수 있습니다.

피벗 테이블은 선택한 기준점에 따라 테이블에서 다양한 최종 계산을 수행하는 도구입니다.

피벗 차트

피벗 차트에는 피벗 테이블과 기존 Excel 차트의 기능이 모두 있습니다. PivotChart를 사용하면 복잡한 데이터 세트의 시각적 표현을 이해하기 쉽고 쉽고 빠르게 만들 수 있습니다. 피벗 차트는 계열, 범주 등을 포함하여 기존 차트의 많은 기능을 지원합니다. 대화형 필터를 추가하는 기능을 사용하면 선택한 데이터 하위 집합을 조작할 수 있습니다.

Excel 2013에는 권장 피벗 차트가 도입되었습니다. "삽입" 탭을 열고 "차트" 섹션으로 이동한 다음 "추천 차트"를 선택합니다. 선택한 옵션 위로 마우스 포인터를 이동하면 어떻게 보이는지 볼 수 있습니다. 피벗 차트를 수동으로 만들려면 삽입 탭에서 피벗 차트 단추를 클릭하십시오.

피벗 차트는 복잡한 데이터를 이해하기 쉽게 보여줍니다.

인스턴트 채우기

아마도 가장 새로운 엑셀 기능 2013 - "Instant Fill" - 인접한 셀에서 필요한 정보 블록을 빠르게 전송하는 것과 관련된 일상적인 작업을 효과적으로 해결할 수 있습니다. 과거에는 성, 이름 형식으로 표시된 열로 작업할 때 사용자가 수동으로 이름을 추출하거나 일부 매우 복잡한 해결 방법을 찾아야 했습니다.

이제 성 및 이름이 있는 동일한 열이 Excel 2013에 있다고 가정합니다. 오른쪽에 가장 가까운 셀(1)에 첫 번째 사람의 이름을 입력하고 홈 탭에서 채우기 및 빠른 채우기를 선택하는 것으로 충분합니다. . Excel은 다른 모든 이름을 자동으로 추출하여 원래 이름 오른쪽에 있는 셀에 채웁니다.

"Instant Fill"을 사용하면 필요한 정보 블록을 추출하고 인접 셀을 채울 수 있습니다.

빠른 분석

Excel 2013의 새로운 빠른 분석 도구를 사용하면 간단한 데이터 집합에서 빠르게 차트를 만들 수 있습니다. 데이터를 선택하면 선택한 영역의 오른쪽 하단에 특성 아이콘(1)이 나타납니다. 클릭하면 "빠른 분석" 메뉴(2)로 이동합니다.

메뉴에는 서식, 차트, 총계, 표 및 스파크라인 도구가 포함되어 있습니다. 이러한 도구를 클릭하면 지원하는 기능을 볼 수 있습니다.

빠른 분석은 간단한 데이터 세트로 작업 속도를 높입니다.

파워 뷰

Power View는 외부 소스에서 대량의 데이터를 추출하고 분석하도록 설계된 대화형 데이터 탐색 및 시각화 도구입니다. Excel 2013에서 Power View 기능을 호출하려면 "삽입" 탭(1)으로 이동하여 "보고서" 버튼(2)을 클릭합니다.

Power View로 만든 보고서는 프레젠테이션 준비가 되어 있으며 읽기 및 전체 화면 보기를 지원합니다. 대화형 버전은 PowerPoint로 내보낼 수도 있습니다. Microsoft 웹 사이트에서 제공되는 비즈니스 분석 가이드는 가능한 한 짧은 시간에 이 분야의 전문가가 되는 데 도움이 될 것입니다.

Power View 모드를 사용하면 프레젠테이션 준비가 된 대화형 보고서를 만들 수 있습니다.

조건부 서식

고급 조건부 기능 엑셀 서식원하는 데이터를 쉽고 빠르게 선택할 수 있습니다. 해당 컨트롤은 홈 탭에 있습니다. 서식을 지정할 셀 범위를 선택하고 "조건부 서식" 버튼(2)을 클릭합니다. 셀 선택 규칙 하위 메뉴(3)는 가장 일반적인 서식 조건을 나열합니다.

조건부 서식 기능을 사용하면 최소한의 노력으로 원하는 데이터 영역을 강조 표시할 수 있습니다.

열을 행으로 또는 그 반대로 바꾸기

테이블의 행과 열을 바꿔야 하는 경우가 있습니다. 이렇게 하려면 원하는 영역을 클립보드에 복사하고 붙여넣을 영역의 왼쪽 상단 셀을 마우스 오른쪽 버튼으로 클릭한 다음 컨텍스트 메뉴에서 선택하여 붙여넣기를 선택합니다. 화면에 나타나는 창에서 "조옮김" 상자를 체크하고 확인을 클릭합니다. 나머지는 Excel이 알아서 처리합니다.

선택하여 붙여넣기 기능을 사용하면 열과 행을 바꿀 수 있습니다.

가장 중요한 키보드 단축키

여기에 표시된 키보드 단축키는 이메일을 빠르게 탐색하는 데 특히 유용합니다. 엑셀 표일반적인 작업을 수행합니다.

여기 몇 가지가 있습니다 간단한 방법이 필수 프로그램의 사용을 크게 향상시킬 것입니다. 해제함으로써 엑셀 2010, Microsoft는 무수히 많은 를 추가했지만 즉시 눈에 띄지는 않습니다. "너무 간단해!"작업에 도움이 되는 기술을 시도하도록 초대합니다.

Excel로 작업할 때 유용한 20가지 유용한 정보

1. 이제 클릭 한 번으로 모든 셀을 선택할 수 있습니다. Excel 시트의 모서리에 있는 마법 버튼을 찾으면 됩니다. 물론 Ctrl + A 키 조합 인 전통적인 방법을 잊지 마십시오.
2. 동시에 여러 파일을 열려면 찾고 있는 파일을 선택하고 Enter 키를 눌러야 합니다. 시간 절약 효과는 분명합니다.



3. Ctrl + Tab 키 조합을 사용하여 Excel에서 열려 있는 통합 문서 사이를 쉽게 탐색할 수 있습니다.
4. 패널 빠른 접근 3개의 표준 버튼이 포함되어 있으므로 필요한 번호로 쉽게 변경할 수 있습니다. "파일" ⇒ "옵션" ⇒ "빠른 액세스 도구 모음" 메뉴로 이동하여 아무 버튼이나 선택하십시오.



5. 특수 분할을 위해 테이블에 대각선을 추가해야 하는 경우 홈페이지 Excel에서 친숙한 테두리 아이콘으로 이동하고 "추가 테두리"를 선택합니다.



6. 빈 줄을 여러 개 삽입해야 하는 상황이 발생하면 원하는 행 또는 열 수를 선택하고 "삽입"을 클릭하십시오. 그런 다음 셀을 이동할 위치를 선택하기만 하면 작업이 완료됩니다.
7. Excel에서 정보(셀, 행, 열)를 이동해야 하는 경우 해당 정보를 선택하고 테두리 위로 마우스를 이동합니다. 그런 다음 원하는 위치로 정보를 이동합니다. 정보를 복사해야 하는 경우 Ctrl 키를 누른 상태에서 동일한 작업을 수행합니다.



8. 이제 작업에 방해가 되는 빈 셀을 매우 쉽게 삭제할 수 있습니다. 한 번에 모두 제거할 수 있습니다. 원하는 열을 선택하고 "데이터" 탭으로 이동한 다음 "필터"를 클릭하십시오. 아래쪽을 가리키는 화살표가 각 열 위에 나타납니다. 그것을 클릭하면 빈 필드 제거 메뉴로 이동합니다.



9. 무언가를 찾는 것이 훨씬 더 편리해졌습니다. Ctrl + F 키 조합을 누르면 테이블에서 필요한 모든 데이터를 찾을 수 있습니다. 그리고 "?"라는 문자를 사용하는 방법도 배우면 및 "*", 검색 기능을 크게 확장할 수 있습니다. 물음표는 알 수 없는 문자 하나이고 별표는 여러 개입니다. 어떤 쿼리를 입력해야 할지 정확히 모른다면 이 방법이 확실히 도움이 될 것입니다. 찾아야 한다면 물음표또는 별표를 사용하고 Excel에서 알 수 없는 문자를 찾지 않도록 하려면 그 앞에 "~"를 입력합니다.
10. 반복되지 않는 정보는 고유한 항목으로 쉽게 강조 표시됩니다. 이렇게 하려면 원하는 열을 선택하고 "필터" 항목 왼쪽에 있는 "고급"을 클릭합니다. 이제 상자를 선택하고 소스 범위(복사할 위치)와 결과를 배치할 범위를 선택합니다.



11. 선택 항목 만들기 - Excel의 새로운 기능으로 침을 뱉습니다. 메뉴 항목 "Data" ⇒ "Data Validation"으로 이동하여 선택을 결정하는 조건을 선택합니다. 이 조건에 맞지 않는 정보를 입력하면 사용자는 정보가 올바르지 않다는 메시지를 받게 됩니다.



12. Ctrl + 화살표를 누르기만 하면 편리한 탐색이 가능합니다. 이 키보드 단축키 덕분에 문서의 극단 지점으로 쉽게 이동할 수 있습니다. 예를 들어 Ctrl + ⇓는 시트의 맨 아래에 커서를 놓습니다.
13. 열에서 열로 정보를 바꾸려면 바꿀 셀 범위를 복사합니다. 그런 다음 원하는 위치를 마우스 오른쪽 버튼으로 클릭하고 선택하여 붙여넣기를 선택합니다. 보다시피 이렇게 하는 것은 더 이상 문제가 되지 않습니다.
14. Excel에서는 정보를 숨길 수도 있습니다! 원하는 셀 범위를 선택하고 "포맷" ⇒ "숨기기 또는 표시"를 클릭하고 원하는 작업을 선택합니다. 이국적인 기능이지 않습니까?



15. 여러 셀의 텍스트가 자연스럽게 하나로 결합됩니다. 이렇게 하려면 연결된 텍스트를 배치할 셀을 선택하고 "="를 누르십시오. 그런 다음 셀을 선택하고 각각 앞에 "&" 문자를 붙여 텍스트를 가져옵니다.
16. 글자의 대소문자는 원하는 대로 변경됩니다. 텍스트의 모든 문자를 대문자 또는 소문자로 만들어야 하는 경우 이를 위해 특별히 설계된 기능 중 하나를 사용할 수 있습니다. "UPPER" - 모든 문자는 대문자, "LOWER" - 소문자. "PROPNACH" - 각 단어의 첫 글자는 대문자로 표시됩니다. 건강에 사용!



17. 숫자 시작 부분에 0을 두려면 숫자 앞에 아포스트로피 "'"를 입력하십시오.
18. 이제 좋아하는 프로그램에는 스마트폰의 자동 고침과 유사한 단어 자동 고침이 있습니다. 어려운 단어더 이상 문제가 아니며 약어로 대체할 수 있습니다.
19. 창의 오른쪽 하단에 있는 다양한 정보를 따르십시오. 그리고 거기에서 마우스 오른쪽 버튼을 클릭하면 불필요한 줄을 제거하고 필요한 줄을 추가할 수 있습니다.
20. 시트 이름을 바꾸려면 마우스 왼쪽 버튼으로 시트를 두 번 클릭하고 새 이름을 입력하십시오. 파이처럼 쉽게!

일이 더 즐겁고 쉬워지도록 하십시오. 이 유익한 기사가 마음에 드셨습니까? 사랑하는 사람에게 보내세요!

이것은 진정한 크리에이티브 랩입니다! 같은 생각을 가진 진정한 사람들로 구성된 팀으로 각자 자신의 분야에서 전문가이며 사람들을 돕는다는 공통의 목표로 통합되었습니다. 우리는 정말 공유할 가치가 있는 자료를 만들고 사랑하는 독자들은 우리에게 무한한 영감의 원천이 됩니다!

Excel은 세상에서 가장 친숙한 프로그램이 아닙니다. 일반 사용자능력의 5%만 사용하고 깊이를 숨기는 보물이 무엇인지 잘 모릅니다. H&F는 Excel 전문가의 조언을 읽고 몇 번의 클릭으로 가격표를 비교하고 비밀 정보를 숨기고 분석 보고서를 작성하는 방법을 배웠습니다. (그렇습니다. 때때로 이러한 클릭이 15번 발생합니다.)

수입 환율

Excel에서 지속적으로 업데이트되는 환율을 설정할 수 있습니다.

메뉴에서 데이터 탭을 선택합니다.

"웹에서" 버튼을 클릭합니다.

나타나는 창의 "주소" 줄에 http://www.cbr.ru를 입력하고 Enter 키를 누릅니다.

페이지가 로드되면 Excel에서 가져올 수 있는 테이블에 검은색과 노란색 화살표가 나타납니다. 이러한 화살표를 클릭하면 가져올 테이블을 표시합니다(그림 1).

환율로 테이블을 표시하고 "가져오기" 버튼을 클릭하십시오.

과정이 워크시트의 셀에 나타납니다.

마우스 오른쪽 버튼으로 이러한 셀 중 하나를 클릭하고 메뉴에서 "범위 속성" 명령을 선택합니다(그림 2).

표시되는 창에서 속도 업데이트 빈도를 선택하고 확인을 클릭합니다.

슈퍼 시크릿 리프

통합 문서에서 작업하는 다른 사용자로부터 Excel의 일부 시트를 숨기고 싶다고 가정해 보겠습니다. 고전적인 방법으로 시트 탭을 마우스 오른쪽 버튼으로 클릭하고 "숨기기"(그림 1)를 클릭하면 숨겨진 시트의 이름이 다른 사람에게 계속 표시됩니다. 완전히 보이지 않게 하려면 다음과 같이 행동해야 합니다.

Alt+F11을 누릅니다.

왼쪽에는 길쭉한 창이 있습니다(그림 2).

창 상단에서 숨기려는 시트의 번호를 선택합니다.

- 맨 아래 목록 맨 끝에서 "Visible" 속성을 찾아 "xlSheetVeryHidden"으로 만듭니다(그림 3). 이제 아무도 이 시트에 대해 알지 못할 것입니다.

소급 변경 금지

우리 앞에는 빈 필드 "Date"와 "Quantity"가 있는 테이블(그림 1)이 있습니다. 관리자 Vasya는 오늘 그가 하루에 판매한 당근 수를 표시합니다. 나중에 이 테이블을 소급하여 변경할 수 없도록 만드는 방법은 무엇입니까?

날짜가 있는 셀에 커서를 놓고 메뉴에서 "데이터"를 선택합니다.

"데이터 확인" 버튼을 클릭합니다. 표가 나타납니다.

"데이터 유형" 드롭다운 목록에서 "기타"를 선택합니다.

"수식" 열에 =A2=TODAY()라고 씁니다.

"빈 셀 무시"에서 확인 표시를 제거합니다(그림 2).

"확인"버튼을 누릅니다. 이제 다른 날짜를 입력하려고 하면 경고 메시지가 나타납니다(그림 3).

"수량" 열의 숫자 변경을 금지할 수도 있습니다. 수량으로 셀에 커서를 놓고 동작 알고리즘을 반복합니다.

중복 입력 금지

반복되지 않도록 가격표에 제품 목록을 입력하려고 합니다. 이러한 반복을 금지할 수 있습니다. 이 예에서는 10개 셀의 열에 대한 수식을 보여 주지만 물론 개수에 제한이 없습니다.

금지 대상이 될 셀 A1:A10을 선택합니다.

"데이터" 탭에서 "데이터 유효성 검사" 버튼을 클릭합니다.

"매개변수" 탭의 "데이터 유형" 드롭다운 목록에서 "기타" 옵션을 선택합니다(그림 1).

"수식" 열에서 =COUNTIF($A$1:$A$10;A1)<=1.

같은 창에서 "오류 메시지" 탭으로 이동하여 중복 입력을 시도할 때 표시될 텍스트를 입력합니다(그림 2).

"확인"을 클릭합니다.

선택적 합계

다음은 다른 고객이 일정 금액에 대해 여러 번 다른 상품을 구입했음을 확인할 수 있는 표입니다. ANTON이라는 고객이 게살(Boston Crab Meat)을 위해 얼마를 구입했는지 알고 싶습니다.

셀 G4에 고객 ANTON의 이름을 입력합니다.

셀 G5에서 Boston Crab Meat 제품의 이름입니다.

금액을 계산할 셀 G7에 일어나서 수식을 작성합니다(=SUM((C3:C21=G4)*(B3:B21=G5)*D3:D21)). 처음에는 그 양에 겁이 나지만 점차 쓰다 보면 그 의미가 분명해집니다.

먼저 (= SUM을 입력하고 괄호를 열면 세 가지 요소가 포함됩니다.

첫 번째 승수(С3:С21=G4)는 지정된 클라이언트 목록에서 ANTON 언급을 검색합니다.

두 번째 승수(B3:B21=G5)는 Boston Crab Meat에서도 동일하게 작동합니다.

세 번째 요소 D3:D21은 비용 열을 담당하며 그 후에 괄호를 닫습니다.

피벗 테이블

특정 관리자가 판매한 제품, 고객, 금액을 나타내는 표(그림 1)가 있습니다. 성장하면 개별 데이터를 선택하기가 매우 어렵습니다. 예를 들어 당근이 얼마나 팔렸는지 또는 어떤 관리자가 가장 많은 주문을 완료했는지 알고 싶습니다. Excel에서 이러한 문제를 해결하기 위해 피벗 테이블이 있습니다. 그것을 만들려면 다음이 필요합니다.

삽입 탭에서 피벗 테이블 버튼을 클릭합니다.

나타나는 창에서 "확인"을 클릭하십시오(그림 2).

관심 있는 데이터만 사용하여 새 테이블을 만들 수 있는 창이 나타납니다(그림 3).

판매 영수증

총 주문 금액을 계산하려면 평소와 같이 할 수 있습니다. 가격과 수량을 곱해야 하는 열을 추가한 다음 이 열의 금액을 계산합니다(그림 1). 공식을 두려워하지 않으면 더 우아하게 할 수 있습니다.

셀 C7을 선택합니다.

=SUM(을 입력합니다.

B2:B5 범위를 선택합니다.

Excel에서 곱셈 기호인 별표를 입력합니다.

범위 C2:C5를 선택하고 브래킷을 닫습니다(그림 2).

Excel에서 수식을 작성할 때 Enter 대신 Ctrl + Shift + Enter를 입력해야 합니다.

가격 비교

이것은 고급 Excel 사용자를 위한 예입니다. 두 개의 가격표가 있고 가격을 비교하려고 한다고 가정해 보겠습니다. 첫 번째와 두 번째 사진에는 2010년 5월 4일과 5월 11일의 가격이 나와 있습니다. 일부 상품이 일치하지 않습니다. 어떤 상품인지 확인하는 방법은 다음과 같습니다.

책에 또 다른 시트를 만들고 첫 번째 및 두 번째 가격표의 상품 목록을 여기에 복사합니다(그림 3).

중복 제품을 제거하려면 이름을 포함하여 전체 제품 목록을 선택하십시오.

메뉴에서 "데이터" - "필터" - "고급 필터"를 선택합니다(그림 4).

나타나는 창에서 다음 세 가지를 표시합니다. a) 결과를 다른 위치로 복사합니다. b) 범위에 결과 배치 - 결과를 쓸 위치를 선택합니다. 이 예에서는 셀 D4입니다. c) "고유 레코드만" 상자를 선택합니다(그림 5).

"확인"버튼을 누르고 D4 셀부터 중복이없는 목록을 얻습니다 (그림 6).

원래 제품 목록을 삭제합니다.

비교 열에 공식 =D5-C5를 입력하면 차이가 계산됩니다(그림 7).

가격 목록의 값을 "5월 4일" 및 "5월 11일" 열에 자동으로 로드해야 합니다. 이렇게 하려면 =VLOOKUP(search_value; table; column_number; interval_lookup) 함수를 사용합니다.

- "Lookup_value"는 가격 테이블에서 찾을 문자열입니다. 제품을 검색하는 가장 쉬운 방법은 이름으로 검색하는 것입니다(그림 8).

- "테이블"은 필요한 값을 찾을 데이터 배열입니다. 4일부터 가격이 포함된 표를 참고해야 한다(그림 9).

- “Column_number”는 데이터 검색을 위해 설정한 범위의 컬럼의 서수입니다. 검색을 위해 두 개의 열이 있는 테이블을 정의했습니다. 가격은 그 중 두 번째에 포함되어 있습니다(그림 10).

interval_lookup. 값을 찾고 있는 테이블이 오름차순 또는 내림차순으로 정렬되어 있으면 값을 TRUE로 설정하고 정렬되지 않은 경우 FALSE를 작성해야 합니다.

범위를 고정하는 것을 잊지 않고 수식을 아래로 끕니다. 이렇게하려면 열 문자 앞과 행 번호 앞에 달러 기호를 넣으십시오 (원하는 범위를 선택하고 F4 키를 눌러 수행 할 수 있음).

마지막 열에는 두 가격 목록에 있는 항목의 가격 차이가 반영됩니다. 합계 열에 #N/A가 표시되면 지정된 제품이 가격 목록 중 하나에만 있으므로 차액을 계산할 수 없음을 의미합니다.

투자 평가

Excel에서 순 현재 가치(NPV), 즉 현재까지 지불 흐름의 할인된 가치의 합계를 계산할 수 있습니다. 이 예에서 NPV 값은 1개의 투자 기간과 4개의 소득 기간을 기준으로 계산됩니다(3행 "현금 흐름").

셀 B6의 수식은 재무 함수 =NPV($B$4;$C$3:$E$3)+B3(그림 1)을 사용하여 NPV를 계산합니다.

다섯 번째 줄에서 각 기간의 할인 흐름 계산은 두 가지 다른 공식을 사용하여 찾습니다.

셀 C5에서 =C3/((1+$B$4)^C2) 수식 덕분에 결과를 얻을 수 있습니다(그림 2).

셀 C6에서 (=SUM(B3:E3/((1+$B$4)^B2:E2))) 수식을 통해 동일한 결과를 얻습니다(그림 3).

투자 제안 비교

Excel에서 두 가지 투자 제안 중 수익성이 더 높은 것을 비교할 수 있습니다. 이렇게하려면 필요한 투자 금액과 단계적 수익 금액을 두 개의 열에 작성하고 투자 할인율을 백분율로 별도로 표시해야합니다. 이 데이터를 사용하여 순현재가치(NPV)를 계산할 수 있습니다.

빈 셀에 수식 =npv(b3/12,A8:A12)+A7을 입력해야 합니다. 여기서 b3은 할인율, 12는 1년의 개월 수, A8:A12는 숫자가 있는 열입니다. 단계별 투자 수익의 경우 A7은 필요한 투자 금액입니다.

정확히 동일한 공식을 사용하여 다른 투자 프로젝트의 순 현재 가치를 계산합니다.

이제 그들은 비교할 수 있습니다. NPV가 더 많은 사람은 그 프로젝트가 더 수익성이 있습니다.

Excel에서 모든 셀에 대한 값의 드롭다운 목록을 설정할 수 있다는 것을 알고 계셨습니까? 예를 들어 특정 열의 경우 셀의 위치만 선택해야 합니다. 설마. 또는 제한된 목록에서 하나의 값을 지속적으로 지정합니다.

탭에서 선택 데이터 - 데이터 유효성 검사 - 데이터 유효성 검사 항목 - 데이터 유형: 목록.

소스 라인에 범위를 지정합니다. 어떤 이유로 기본적으로 Excel에서는 콤보 상자와 동일한 시트의 범위만 선택할 수 있습니다. 그러나 다른 시트를 지정해야 하는 경우 펜으로 정확한 주소를 간단히 작성할 수 있습니다. 스크린샷에서 시트 2에 대한 링크입니다.

두 번째 시트에는 각각 값 자체의 범위가 있습니다.

결과:

다른 파일의 범위를 쉽게 참조할 수도 있습니다. 서로 다른 보고서에서 동일한 목록을 사용하면 편리합니다. 소스의 공식은 다음과 같습니다.

=INDIRECT("[Table.xls]목록!$A$1:$A$2")

INDIRECT 함수(또는 영어 버전 INDIRECT)와 소스는 테이블 파일입니다(예를 들어 필요한 데이터 범위를 저장하는 위치). 항상 같은 파일을 사용할 수 있습니다. 그러나 테이블이 목록이 있는 문서와 동일한 폴더에 없으면 파일 이름 외에 다음과 같이 전체 경로도 지정해야 합니다. =INDIRECT("")

나중에 테이블에서 일부 이름을 추가하거나 제거하면 드롭다운 목록의 데이터가 그에 따라 변경됩니다.

엑셀의 특징

프로그램을 사용하여 생산적으로 작업하려면 Excel 기능에 대한 지식이 필요합니다. 일반적으로 테이블 및/또는 컴퓨팅 서비스로 사용됩니다.

대부분의 사용자는 Excel 작업 과정에서 특정 지식을 적용하는 것에 대한 일반적인 아이디어를 가지고 있습니다.

그러나 업무와 개인 시간을 최적화하는 관점에서 보면 추가적인 기능 기술을 알고 구현하는 것이 중요합니다.

#1 테이블 생성

또한 읽으십시오: 단축키 Excel(Excel) 2007-2016: 모든 조합 선택

몰랐을 수도 있지만 프로그램의 셀 자체는 테이블이 아닙니다.. 셀을 사용하여 데이터를 쓴 다음 단일 단위로 배열할 수 있습니다.

또 다른 옵션은 표를 만든 다음 채우는 것입니다.

즉, 완료된 정보를 기반으로 테이블을 생성하려면 다음을 수행해야 합니다.

1 표와 유사한 텍스트를 만듭니다(아래 그림).

4 "테이블" 메뉴 항목을 클릭하면 테이블 데이터가 있는 창이 화면에 표시되고 프레임으로 강조 표시됩니다. 그런 다음 "확인"버튼을 클릭하십시오.

5 상단 패널의 "디자이너" 탭에서 테이블 디자인 및 설정을 선택할 수 있습니다.

6 팝업 메뉴 중간에 "전체 라인"이라는 문구가 포함되어 있습니다. 그것을 클릭하면 사용자는 테이블 하단에 행을 표시하고 드롭 다운 화살표를 사용하여 목적 (합계, 평균 등)을 조정합니다.

테이블을 생성하면 데이터를 다른 문서로 전송하는 작업이 크게 간소화됩니다. 이러한 방식으로 정보를 사용하는 것이 대부분 훨씬 쉽습니다.

#2 한 번의 클릭으로 모든 셀 선택

또한 읽으십시오: PDF 파일을 MS Word로 변환하기 위한 10가지 온라인 서비스 | 2019년

모든 셀은 두 가지 방법으로 선택됩니다.

• 왼쪽 숫자 열 위 모서리와 문자 값 왼쪽에 있는 삼각형 아이콘을 클릭할 수 있습니다.
• Ctrl 및 A 키 조합을 사용할 수 있습니다.

이러한 옵션은 동일하며 사용자 자신이 자신에게 더 가까운 셀을 선택하는 방법을 선택합니다.

실무에서 문서를 비교하거나 데이터를 사용하기 위해 두 개 이상의 파일을 열어야 하는 경우가 종종 있습니다.

이 프로세스를 단순화하고 각각을 두 번 클릭하여 열지 않아도 됩니다.

이렇게 하려면 필요한 모든 파일을 선택하고(마우스 하나를 사용하거나 Ctrl 키를 누른 상태에서) Enter 키를 누릅니다.

결과적으로 파일이 자동으로 열립니다.

한 창에서 다른 창으로 빠르게 이동하려면 Control + Tab 을 사용할 수 있습니다.

처음에 빠른 실행 도구 모음에는 최대 3개의 아이콘이 포함되어 있습니다.

그러나 도달하기 어려운 아이콘을 계속 사용해야 하는 경우 해당 아이콘을 추가할 수 있습니다.

이렇게 하려면 "파일"(패널 자체 바로 아래에 있음)을 클릭하십시오. 왼쪽 열 하단에 "옵션" 항목이 있는 창이 나타납니다.

그것을 클릭하면 두 개의 열이 나타납니다. 하나는 가능한 아이콘 옵션을 표시하고 다른 하나는 이미 사용 가능합니다.

최종 선택을 위해 필요한 항목을 선택하고 "추가"를 클릭합니다.

이제 아이콘이 빠른 실행 도구 모음에 있습니다.

#5 하나 이상의 셀에 대각선 만들기

또한 읽으십시오: [설명] 컴퓨터에서 스크린샷을 찍는 방법: 가능한 모든 방법

일부 활동의 경우 대각선을 삽입해야 할 수도 있습니다.

이렇게 하면 시간 및 날짜 메트릭과 테이블의 세로 및 가로 제목을 구분할 수 있습니다.

한 셀과 관련하여 이 작업을 수행하려면 해당 셀에 커서를 놓고 마우스 오른쪽 버튼을 클릭해야 합니다.

목록에서 "셀 형식"에서 선택을 중지해야 합니다. 여기서 "테두리" 탭에서 대각선 아이콘을 선택해야 합니다.

그런 다음 "확인"을 클릭합니다.

또한 정보를 문서의 다른 위치로 빠르게 이동해야 하는 경우도 종종 있습니다.

하나의 셀로 이를 수행하는 방법 - 많은 일반 사용자가 알고 있습니다.

그러나 모든 사람이 셀 그룹이나 정보를 원하는 위치로 이동할 수 있는 것은 아닙니다.

따라서 셀 그룹을 이동하려면 커서로 셀 그룹을 선택하고 커서가 이동 표시가 될 경계 위치를 찾아야합니다 (두 개의 교차 화살표).

이 아이콘을 사용하여 셀을 대상으로 이동해야 합니다.

원하는 경우 정보가 있는 셀을 선택하여 원래 형태로 정보를 전송할 수 있습니다.

후속 작업 - 이전 이동 방식에 따라(+ 길게 누르기 제어 ) - 원하는 위치로 이동합니다.

이 경우 정보가 원래 테이블에 있었든 셀에 있었든 복사됩니다.

#7 빈 셀 삭제

또한 읽으십시오: 일일 상위 15개 FireFox 플러그인: 설치 대상 | 2019년

종종 빈 셀은 사용자에게 실제 문제입니다.

이 기술을 모르면 간격을 수정하는 데 많은 시간과 노력을 낭비할 수 있습니다.

Excel에서 빈 셀을 제거하려면 다음을 수행해야 합니다.

1 먼저 누락된 셀이 있는 열을 하나 이상 선택합니다.

2 그런 다음 상단 패널에서 "필터" 메뉴 항목이 있는 "데이터" 탭을 열어야 합니다.

3 필터를 클릭하면 강조 표시된 각 열의 상단에 아래쪽 화살표가 표시됩니다.

4 불필요한 셀을 필터링할 수 있는 목록을 열려면 클릭해야 합니다. 일부 셀이 표시되지 않도록 하려면 목록에서 선택을 취소해야 합니다.

문서에서 표준 검색을 수행하려면 Ctrl + F 조합을 사용하고 검색어(문구)를 입력하면 됩니다.

그러나 고급 검색을 위해서는 추가 지식이 필요합니다. 즉, 사용자가 일부 문자에 대해 확신이 없으면 물음표를 넣을 수 있습니다.

물음표 자체를 찾아야 하는 경우 앞에 "⁓" 기호가 표시됩니다.

알 수 없는 문자가 여러 개 있는 단어나 구를 검색해야 하는 경우 "*" 기호가 사용됩니다.

"*" 기호 자체를 찾아야 하는 경우 앞에 "⁓"를 입력해야 합니다.

설문 조사 또는 기타 이벤트에서 참가자를 정렬해야 할 때 샘플이 생성됩니다.

성별, 연령 범주, 활동 유형, 인생관 등을 제한할 수 있습니다.

이렇게 하려면 상단 패널의 "데이터" 탭으로 이동하여 "데이터 유효성 검사"를 클릭하고 목록에서 "데이터 유효성 검사"를 다시 클릭합니다.

설정을 할 수 있는 기회와 부적절한 참가자에게 오류를 보고하는 방법이 있을 것입니다.

이것은 설문 조사를 만들 때 많은 준비 작업이 필요하기 때문에 유용한 기술입니다. 몇 개의 버튼을 누르면 다른 세부 정보에 더 많은 자유 시간이 제공됩니다.

#10 운송 데이터

또한 읽으십시오: Windows 폴더에 암호를 설정하는 방법은 무엇입니까? | 7가지 최고의 암호화 방법 및 프로그램 | 2019년

생활 해킹이자형엑셀데이터 전송을 포함합니다.

이 방법의 장점은 뛰어난 속도와 사용 편의성입니다.

전송은 열에서 행으로 데이터를 전송하는 것입니다.

이렇게 하려면 열에서 특정 수의 행을 선택하고 복사(Ctrl + C)합니다. 그런 다음 이 데이터가 있는 행이 시작되는 셀을 선택하고 마우스 오른쪽 버튼으로 클릭해야 합니다.

목록이 나타나고 상단에 아이콘이 있으므로 운송하는 아이콘을 선택해야 합니다(아래 그림 참조).

이제 정보가 라인으로 완전히 전송됩니다.

때로는 설명의 완전성과 일관성 또는 기타 목적을 위해 텍스트의 특정 부분을 일시적으로 숨길 필요가 있습니다.

이를 위해 사용자가 숨기려는 개체를 선택할 수 있습니다.

그런 다음 상단 패널의 탭에서 "형식"을 찾아야 합니다(오른쪽 그림 참조). 선택하면 "숨기기 및 표시" 항목 위로 마우스를 가져갈 가치가 있는 목록이 나타납니다.

목록에서 화살표는 숨기기, 행, 열 또는 시트를 제안합니다(동일한 방식으로 여기에서 다시 표시할 수 있음).

사용자의 선택에 따라 원하는 항목을 누르면 원치 않는 정보가 숨겨집니다.

행, 열 또는 시트를 숨기는 방법

#12 & 기호로 텍스트 결합

또한 읽으십시오: PSD 파일을 어떻게 열 수 있나요? 읽기 및 편집을 위한 최고의 프로그램: Photoshop 없이 수행

셀 병합은 구식 방식으로 수행할 필요가 없습니다.

결합이 시작될 셀에 "=" 기호를 넣은 다음 다음 셀을 순차적으로 눌러 각각 앞에 "&" 기호를 넣을 수 있습니다.

이 작업은 기간과 연속 창 수에 관계없이 열을 사용하여 수행할 수 있습니다.

거기에서 화살표 "텍스트"가 있는 항목을 클릭해야 합니다.

제안된 텍스트 수식 목록이 여기에 표시되며 하단 단락에 자신의 기능을 삽입할 수도 있습니다. 따라서 "UPPER"는 텍스트를 대문자로, "LOWER"를 소문자로 변환하도록 프로그램을 설정합니다.

"PROPNACH"항목을 켜면 각 단어의 시작 부분이 대문자로 작성됩니다.

Excel에서 작업의 비밀은 프로그램 작업의 일반적인 경우와 사적인 상황 모두와 관련이 있습니다.

№14 숫자 계열의 시작 부분에 0 삽입

많은 사용자들은 숫자 행 앞에 0 또는 여러 개의 0을 입력하면 숫자 행만 남게 된다는 점을 이미 알고 있습니다.

이는 일반적인 숫자 값, 식 및 기타 메서드 집합에 편리합니다.

그러나 숫자 바로 앞에 0을 남겨야 하는 경우 아포스트로피 "'"를 넣을 수 있습니다.

#15 자주 쓰는 단어의 타이핑 속도 향상 - 교체

긴 의미, 복잡한 단어 또는 구로 계속 작업할 때 입력하는 데 많은 시간이 소요됩니다.

단조로운 작업의 이 부분을 최적화하는 것이 가능합니다.

문자 교체는 다음 경로를 극복한 후에 발생합니다.

1 페이지 왼쪽 상단에는 파일 탭이 있습니다.

2 왼쪽 열에서 "매개변수"를 찾아야 합니다.

3 옵션을 클릭하면 "자동 수정 옵션" 창이 있는 설정 창으로 이동합니다.

4 자동 고침 창이 열리고 단어, 구 등을 입력하는 창이 있고 첫 번째 창에 문자를 대체하는 값을 쓰는 창이 있습니다.

5 확인을 클릭하여 확인합니다.

#16 열별 자동 맞춤

셀의 내용이 주어진 너비에 맞지 않고 사용자에게 셀의 격자가 표시되는 경우가 종종 있습니다.

값이 이미 있으므로 문자열을 수동으로 조정하여 문자를 표시해야 합니다.

그러나 자동 선택을 사용하는 것이 훨씬 쉽고 빠릅니다.

맨 위에 있는 열 사이의 선을 두 번 클릭하기만 하면 됩니다.

이렇게 하면 해당 내용의 지정된 크기로 셀이 자동으로 확장됩니다.

셀 또는 열 그룹에서도 동일한 작업을 수행할 수 있습니다.

이를 위해 필요한 범위를 선택하고 셀 머리글의 일부 열 교차점에서 커서를 두 번 클릭합니다. 이제 세포가 충분히 확장되었습니다.

№17 마커

종종 수식을 입력해야 하는 경우 사용자가 채우기 핸들을 사용하여 결과를 아래로 드래그합니다(위 그림 참조).

소수의 값에 편리하고 선택하기 쉽고 다른 방법을 생각하지 않습니다.

그러나 이러한 식의 양이 많을 경우 1,000개 이상의 행으로 구성된 열을 선택하기가 더 어려워집니다.

따라서 첫 번째 표현식을 채운 후 기호가 있는 마지막 셀로 커서를 이동하고 마우스 왼쪽 버튼으로 오른쪽 하단 모서리를 클릭하는 옵션이 있습니다.

이렇게 하면 같은 방식으로 할당된 공간이 자동으로 채워집니다.

여기서 원칙은 프로그램이 왼쪽의 값을 보는 것입니다.

왼쪽(공백)에 문자가 없으면 이 줄에서 프로세스가 중지됩니다.

즉, 그러한 브로치는 마지막 빈 셀까지 작동합니다.

#18 드롭다운 목록

사용자가 테이블에 유사한 값을 입력하는 경우가 종종 있습니다.

프로세스를 최적화하고 항상 수행하지 않아도 됩니다.

드롭다운 목록은 일종의 템플릿으로 생성됩니다.

변화하는 데이터로만 채워집니다.

따라서 이러한 목록을 만들려면 변수 열을 강조 표시하고 주소가 있는 행에 이름을 지정해야 합니다.

이제 드롭다운 목록이 적용될 두 번째 테이블로 이동합니다. 상단 패널에서 "데이터" 탭을 열고 "데이터 유효성 검사" 항목을 선택해야 합니다.

데이터를 입력해야 하는 창이 나타납니다. "데이터 유형"은 이 경우 "목록"이라는 이름을 제안합니다.

그리고 소스가 있는 곳에 범위에 부여된 이름은 반드시 등호를 통해 표기한다. "확인"을 클릭합니다.

이제 두 번째 테이블에 드롭다운 목록이 나타납니다. 테이블 머리글의 화살표처럼 보입니다.

이를 클릭하면 사용자가 적절한 값을 선택합니다.

19. 언후크

연속으로 여러 셀의 정보를 하나로 또는 그 반대로 수집해야 하는 경우가 있습니다. 이를 후킹 및 언후킹이라고 합니다.

많은 사용자는 프로그램에 이러한 아이콘이 있다는 것을 알고 있습니다. 등호를 사용하여 연결하고 "&" 기호로 셀을 순차적으로 연결할 수도 있습니다.

분리를 위해서는 분리해야 하는 정보의 양을 선택해야 합니다. 그런 다음 상단 패널의 "데이터" 탭에서 "열별 테이블" 항목을 찾습니다.

수행할 작업을 세 단계로 지정해야 하는 창이 열립니다.

1 데이터 형식을 지정합니다. 구분(필드 값은 구분 문자로 구분됨) 또는 고정 너비(필드에 지정된 너비가 있음)로 구분될 수 있습니다. 첫 번째 옵션이 더 일반적으로 사용됩니다.

목록에서 "셀 서식"을 선택합니다. 오른쪽에는 "날짜"가 아니라 "모든 형식"을 클릭해야하는 열이 있습니다.

"type"이라는 단어 아래 줄에는 비문이 있으며 그 앞에 "DDD."를 추가하여 형식을 변경합니다.

결과는 창 상단에 표시됩니다. 그리고 여기 "DDDD"가 있습니다. 날짜 앞의 요일의 전체 이름으로 결과를 제공합니다. "확인"을 클릭합니다.

№21 메모의 사진

사용자가 메모를 작성하고 싶지만 텍스트가 아닌 사진을 삽입한다고 상상해보십시오.

다음과 같이 할 수 있습니다.

셀을 선택하고 마우스 오른쪽 버튼으로 클릭한 다음 목록에서 "메모 삽입"을 선택합니다.

마우스 오른쪽 버튼을 클릭해야하는 주변에 무색 창이 나타납니다. 그러면 "노트 형식"을 선택할 수 있게 됩니다. 대화 상자에서 "색상 및 선"을 선택합니다.

이제 상단에서 "색상"이라는 단어 옆에있는 화살표를 클릭하고 나타나는 창 하단에서 "채우기 방법"을 클릭해야합니다.

채우기 방법에서 "그림" 탭은 이미 선택되어 있습니다.

이미 여기에서 "그림"을 통해 사용자가 메모에 삽입하려는 그림을 찾을 수 있습니다.

이것은 그림이 있는 파일을 사용하여 수행되며 마우스로 클릭하고 "열기"합니다.

SHARE YEAR 기능

세 번째 열에는 연령을 나타내는 실제 숫자가 표시됩니다.

#23 대체 값

마침표로 올린 작품에 값이 나오는 경우가 있다. 이러한 값을 서비스에서 이해할 수 있도록 하려면 대체해야 합니다.

"대체" 기능을 사용하여 점을 쉼표로 바꿀 수 있습니다.

그리고 세미콜론을 통해 따옴표 안에 새 텍스트, 즉 ","를 입력해야 합니다.

따라서 점 대신 쉼표가 있고 프로그램이 텍스트를 올바르게 인식할 수 있도록 설정이 이루어졌습니다.

그러나이 텍스트는 여전히 값이 아닙니다.

따라서 등호와 함수 자체 사이에 VALUE를 삽입해야 합니다.

이제는 가치가 되었고 칼럼 전체에 그런 사건이 있다면 그런 결과를 확장할 수 있다.

상위 25가지 Excel 비밀 및 기능: 가장 유용한 작업 해킹