[소소한컴퓨터공부] 엑셀의 표 입력키 FIND함수 INDIRECT 함수

엑셀

 

엑셀의 표 :

테이블표, 기본적인 표, 보통 데이터를 기록하고 보관하는 목적, 병합기능을 사용하지 않아야 한다(데이터 관리기능이 작동 저해)

크로스탭표, 테이블형식의 표를 요약 및 집계할 때

서식표, 인쇄목적, template

 

위아래 셀을 병합한 경우, 병합한 셀 값ㅇ은 첫번쩨 셀에 저장된다, 따라서 병합을 해제하는 가장 간단한 방법은 아래 행을 삭제하는 것

 

입력키

엔터 : 선택한 셀에 값 또는 수식을 입력

컨트롤+엔터 : 선택한 범위에 입력한 값 또는 수식 복사

알트+엔터 : 선택한 셀에 값 또는 수식을 입력할 때 다음 줄로 입력 위치 변경

컨트롤+쉬프트+엔터 : 선택한 셀 또는 범위에 수식을 배열 수식으로 입력

예) 범위를 지정한 다음 1,2,3….을 입력하면 지정한 범위의 셀마다 배열 값이 하나씩 입력

열로 구분하려면 콤마, 행으로 구분하려면 세미콜론

 

Find 함수

전체 문자열 중에서 특정문자(열)가 몇 번째 위치에 있는지 알려 주는 역할을 하는 함수로, 다음과 같은 구문을 사용

FIND(찾을 문자(열), 전체문자열)

 

LEFT 함수

전체 문자열 왼쪽부터 지정된 문자 개수만큼 잘라내는 함수

LEFT(전체 문자열, 잘라낼 문자 개수)

Ex. LEFT(E2, FIND(“ “, E2)-1)

 

MID 함수

전체 문자열의 시작위치에서 지정된 문자 개수만큼 잘라낼 때 사용하는 함수

MID(전체문자열, 시작위치, 잘라낼 문자개수)

Ex. MID(E@, FIND(“ “, E2)+1, 100)

                         ★세번째 인수값을 크게 잡으면 해당 문자 개수만큼 있는 경우 해당 문자개수만큼 잘라내고, 잘라낼 문자 개수보다 작다면 있는 값만 자름★

 

IF 함수

첫번째 인수인 조건식의 결과에 따라, 조건식의 결과 true 이면 두번째 인수값을 반환하고, false이면 세번쩨 인수값을 반환

IF(조건식, 참일 때 반환할 값, 거짓일 때 반환할 값)

 

사용자 지정 서식 코드 사용

서식 코드 0 : 숫자 서식 코드, 사용한 개수만큼의 숫자표시

 입력된 숫자 개수보다 많은 서시코드 사용 → 해당 위치에 0이 표시

 입력된 숫자 개수보다 적은 수의 서식코드 → 입력된 값이 그대로 표시

서식 코드 # : 숫자 서식 코드 0과 유사하나, 불필요한 0을 표시하지 않음

서식 코드 ? : 숫자 서식 코드 0과 유사하나,

입력된 숫자 개수보다 많은 서시코드 사용 → 해당 위치에 공백이 표시

 

조심할 사항

1.    가능하면 데이터를 입력하거나 수정하는 날짜와 시간을 함께 기록

데이터를 집계할 때 가장 많이 사용하는 유형이 바로 시계열로 집계하는 작업

따라서 테이블에 날짜값을 갖는 열이 있어야 식켸열 집계 보고서를 만들기 편리

2.   데이터를 연, 분기, 월, 업체별, 제품별 등 조건별 시트(또는 파일)로 나누지 않고,

같은 데이터는 하나의 표에 기록

데이터를 나눠 기록하면 나중에 한번에 집계 보고서를 만들 때 불편

3.   데이터 입력을 최호화할 수 있도록 계산해서 얻을 수 있는 값은 수식을 사용하도록 구성

예, 주민등록번호 등을 입력함 성별이나 나이 등을 계산해 얻을 수 있음

 

 

구조적 참조

열머리글을 이용해 참조하는 방법

열데이터를 빠르게 참조할 수 있도록 해준다, 집계작업을 할 때 엑셀 표의 구조적 참조 방식을 이용한다면 매우 효과적으로 작업대상범위만 함수에 전달할 수 있고 데이터가 추가됨에 따라 집계결과가 자동으로 변화되는 것을 확인할 수 있다.

2010이후 버전 : @기호를 사용하면 셀을 하나만 참조 ex.[@ ]

표내부에서 다른 열 데이터를 참조하는 방법

표 외부에서 엑셀 표  내부의 열 데이터를 참조하는 방법, 엑셀 표이름을 열 머리글과 함께 사용

표이름[열머리글]

IFERROR 함수

수식오류가 발생할 경우, 반환할 새 값을 지정할 수 있다

IFERROR(계산식, 오류가 발생할 때 반환할 값)

COUNT함수

인수로 전달한 범위의 숫자 값 개수를 세는 함수

[계산된 열] : 하나의 수식을 공통으로 사용하는 열, 엑셀의 표의 특징

            자동고침옵션에서 계산된 열 실행을 취소하고 개별적 입력 가능

 

[요약행] 표데이터를 요약하고 집계

표스타일옵션 : 요약형

SUBTOTAL 함수

대표적인 집계함수 중의 하나, 평균, 개수, 합계 등 여러 집계 작업을 하나의 함수에서 처리할 수 있고, 숨겨진 데이터를 제외하고 화면에 표시된 데이터 범위만 집계할 수 있다

SUBTOTAL(함수번호, 집계범위)

 

유효성 검사로 입력값 제한하고 메시지 표시하기

-     데이터 –데이터도구-데이터유효성검사

-      

조건에 맞는 날짜만 입력되도록 하기 : 유효성 검사

TODAY 함수 : 항상 오늘 날짜를 기준으로 어떤 조건을 지정

DATE 함수 : 종료일과 같은 특정 날짜를 지정하고 작업

 

l  날짜조건 이해하기

항상 오늘 날짜만 입력 

제한방법 =

날짜 =TODAY()

최근 일주일 날짜만 입력

제한방법 = 해당범위

시작날짜 = TODAY()-6

끝날짜 = TODAY()

올해 날짜만 입력

          제한방법 = 해당범위

          시작날짜 = DATE(YEAR(TODAY())),1,1)

          끝날짜 = DATE(YEAR(TODAY)), 12, 31)

n  YEAR함수 : 날짜 값에서 연도 값만 숫자로 반환하는 함수

YEAR(TODAY())는 항상 올해 연도를 반환

 

텍스트 길이 제한하기

유효성검사 중 텍스트길이조건

 

목록 지정하여 입력 값을 목록에서 선택하게 만들기

유효성검사조건 중 목록 : 셀에 입력될 값 범위를 지정하거나 값을 미리 입력하는 방법을 통해 목록에서 선택해서 값을 지정할 수 있도록 해준다

          원본란에서 다른 워크시트 참조하기

          =워크시트이름! : 원본란에 정의된 이름을 사용할 때 반드시 앞에 등호를 입력, 입력하지 않으면 참조된 범위값은 나오지 않는다

          2010부터 가능한 기능

 

이중 유효성 검사 또는 연결목록

-     이름정의와 INDIRECT함수를 이용하여 여러 개의 목록을 서로 연동하는 듯한 방법

 

★INDIRECT함수

텍스트값을 참조로 변환하는 함수

INDIRECT(텍스트)

유효성검사를 활용한 연결목록을 구성할 때는 반드시 사용 : 하위목록

                                     

유효성 검사와 COUNTIF 수식으로 중복 값 제한하기

-중복이란 지정된 범위에 동일한 값이 둘 이상 있는 것이므로 개수를 세는 함수를 사용해 중복 여부를 확인할 수 있다

COUNTIF 함수

COUNTIF(범위, 조건)

범위를 제한하면서 중복이 없도록 하려면

=AND(COUNTIF(범위, 조건), COUNTIF(범위, 조건))

 

여러 열의 중복 조건 참고하기

COUNTIFS 함수 사용

-     조건이 둘 이상인 다중 조건인 경우 개수를 세는 함수

-     COUNTIFS(범위1, 조건1, 범위2, 조건2, ….)

 

★이름정의 : 엑셀 표의 구조적 참조와 호환되지 않는 다른 기능에서 엑셀 표의 구조적 참조를 사용하는 경우에 도움이 되는 방법

공백무시 : 유효성 검사를 설정한 범위에 빈 셀이 있는 경우를 처리하는 옵션

            수식 조건을 사용할 때 수식 조건을 해제하는 역할을 한다, 따라서 해제함으로써 발생하는 문제 해결

 

유효성검사범위 수정

유효성 검사가 설정된 데잍 범위를 확인하려면 이동 명령 사용

조건부 서식

사용자가 지정한 조건에 맞는 데이터에만 서식을 적용하는 기능

조건에 따라 입력된 데이터에 지정한 서식이 나타나도록 할 수 있으므로 서식이 나타난 셀만 확인하면 잘못 입력된 데이터를 빠르게 확인할 수 있어 편리

 

표가 한눈에 구분되도록 하는 방법

-     색조로 분포와 변화를 시각적으로 표현 : 조건부서식-색조

 

데이터 마대로 값과 그래프를 함께 표시하기

스타일그룹 –조건부서식 –데이터막대 (–기타규칙)

 

아이콘 집합을 이용해 증감을 아이콘으로 표시하기

스타일그룹 –조건부서식 –아이콘집합 –규칙편집

 

상위 실적 n개에 원하는 서식 저장하기

스타일그룹-조건부서식-상위/하위 규칙

스타일그룹-조건부서식-새규칙(수식을 사용하여 서식을 지정한 셀 결정)

 

LARGE(범위, N)

범위에서 N번째로 큰 값을 반환하는 함수

LARGE함수의 반대 : SMALL함수

 

중복된 값에 서식지정하기

스타일-조건부서식-셀강조규칙-중복값

스타일-조건부서식-새규칙-수식을 사용하여 서식을 지정할 셀 결정 –COUNTIF

DOUNTIF($D$4:$D$14, $D4)>1

유효성 검사에서는 COUNTIF 함수결과가 1인 경우로 조건식을 구성해 고유 항목만 입력하도록 헸고

조건부서식에서는 COUNTIF 함수결과가 1을 초과하는 중복데이터에만 서식을 표시하도록 한 것에 차이

** 조건부서식을 이용하여 중복값을 확인하고 정리한 다음 유효성검사로 이후 입력될 중복값을 방지하면 바람직

데이터-데이터도구-데이터유효성검사-사용자지정(COUNTIF 함수)

COUNTIFS(범위1, 조건1, 범위2, 조건2…….)

COUNTIFS([이름], C4, [전화번호], D4)=1

 

평균이상 또는 이하인 데이터만 따로 표시하기

스타일-조건부서식-상위하위규칙-평균미만

조건부서식-규칙관리-규칙편집

 

여러 개의 조건부 서식 함께 적용하기

스타일-조건부서식-셀강조규칙-보다큼

 

조건부 서식이 설정된 범위와 규칙 확인하기 : 규칙편집

외부데이터 가져오기

 

외부파일의 표를 현재 파일로 가져오기

데이터 – 외부데이터가져오기 –기존연결

l  테이블 이름 뒤에 $표시 의미 : 테이블 이름이 워크시트라는 것을 의미하는 기호

$가 붙지 않는 테이블 이름은 이름으로 정의된 범위를 의미

외부파일에 데이터를 추가하고 동기화 확인하기

 디자인 –외부표데이터-새로고침

 

표범위 이름 지정하여 원하는 표만 가져오기

 

쿼리로 조건에 맞는 데이터만 가져오기

데이터-욉데이터가져오기-기타원본-Microsoft query

*데이터 원본에 볼 수 있는 테이블이 없다는 경고메시지

쿼리 마법사 대화상자의 옵션 – 표시옵션에서 시스템 레이블 체크 –확인

>를 클릭하여 쿼리에 포함된 열 목록 추가- 다음 –가져올 데이터 조건 설정 – 다음

가져올 데이터의 정렬조건설정

엑셀로 데이터를 가져오기 위해 Microsoft excel로 데이터 되돌리기

 

텍스트 파일 가져오기

텍스트삽입, 구분기호없는 텍스트삽입, ODBC 연결

 

텍스트파일가져오기

데이터-외부데이터가져오기-텍스트

원본파일의 한국어가 깨져있다면 다면 언어 설정이 잘못된 것임 : 언어선택

텍스트마법사2단계 : 열구분

텍스트마법사3단계 : 데이터형식지정

 

텍스트파일에 날짜가 yyyy.mm.dd형식으로 입력된 값을 그대로 엑셀로 가져오면 텍스트형식으로 인식되어 계산 작업 불가

엑셀은 하이픈이나 슬래시 구분기호를 사용하는 값만 날짜 형식으로 인식

텍스트파일에서 데이터를 가져올 때 데이터형식을 날짜로 변경적용해야 함

 

텍스트형식선택하기

슬래시구분기호가 포함된 숫자값은 그대로 가져오면 날짜로 변환되므로 데이터형식을 텍스트로 변환해야 한다

 

*구분기호가 없는 텍스트 파일

파일 –열기 –텍스트파일로 지정하여 열기