엑셀 -gseek 강의 내용*
0. 인쇄
- 머리글, 바닥글: 보기 -> 페이지레이아웃
페이지 레이아웃 -> 페이지 설정.더보기icon->머리긋/바닥글 tab
- 인쇄자료 페이지 중앙에 인쇄 : 페이지 레이아웃 -> 페이지 설정.더보기icon->여백tab-> [v]가로,[v]세로
- 예약어 : 디자인-> 여러icon (예, &[날짜], &[페이지 번호])
- 상단 Grid title을 매 페이지에 인쇄하기 : 페이지 레이아웃 -> 페이지 설정.더보기icon->시트tab->반복할행 선택
- 특정행에서 페이지 나누기 : 페이지 레이아웃 ->나누기
0. 기타
- 특수문자 입력 : ㅁ -> 한자 key -> Tab
- ctrl+; : 오늘일자 얻기
- ctrl+shipt+; : 현재시각
- 서식지우기 : 홈 -> 편집 -> 지우기
- cell 맨 앞에 : ' 이면 문자형, 0 이면 숫자형 (예, 0 1/3 )
- and, or 연산 :=and(조건1, 조건2), =or(조건1,조건2), =AND($C4>=90,$E4>=70), =OR($C4>=90,$E4>=70)
0. 연산자
- & : 문자 결합
- 참조연산자 : 콜론(:) : 범위 연산자, 쉼표(,) : 결합연산자, 공백(array1 array2 ) : 교점연산자 (두개의 array의 교차점만 취함)
- 주소 : 상대주소(예, C4, G6), 절대주소(예, $C$4, $G$6), 혼합주소:(예, C#3,$D4)
- 셀이름 지정 후 계산 : =C4*D4*부가세율 (부가세율 cell에 이름 지정. 장점은 자동 절대주소가 됨)
0. 셀서식
- # : 유효한 자리만 표시, 유효하지 않은 0은 표시하지 않음
- 0 : 유효하지 않은 자리수는 0으로 표시 (또는 숫자 하나를 지칭)
- @ : 문자 데이터의 표시위치를 지정
- , : 서식 마지막에 쉼표(,)를 지정하면 오른쪽부터 3자리를 생략한다.
- ; : 하나의 cell 다향한 표시형식을 지정 시 구분자
- _- : (언더라인, 빼기) 공백한칸 추가
- 날짜 서식
. yy, yyyyy : 연도,
. 월 -> m : 1~12, mm : 01~12, mmm : Jan ~ Dec
. 일 -> d : 1~31, dd : 01~31, ddd : Sun ~ Sat, dddd : Sunday~Saturday
. 요일 -> aaa : 요일을 월~일로 표시, aaaa : 요일을 월요일~일요일로 표시
- 예)
. 이름 뒤에 님자 붙이기 : @"님"
. 숫자뒤에 원 붙이기 : #"원"
. 2019년 08월 15일 (목) : yyyy"년" mm"월" dd"일" (aaa)
. #,##0;[빨강]-#,##0;#.00;@"님" : 양수인경우;음수인경우;0인경우;문자인경우
. #,##0_-;[빨강]△#,##0_-;"-"_-; : 양수인경우;음수인경우;0인경우
(참고, _- [믿줄 하이픈] : 한줄공백띄우기)
(참고, 문자입력시 값이 살아짐)
. 0"박"0"일" : 값이 45 일 경우 -> 4박 5일로 표시됨 (0이 숫자 하나를 뜻함)
0. & : 문자열 결합
~ : 예약특수문자, 문자열로 사용하고 싶은 경우에는 ~~(2개)로 사용
substitute : 문자열 replace
type(cell) : 해당 cell의 숫자,문자 구분값 return
=ROW() : 순번(seq)
=SUBTOTAL(3, $B$2:B36) : 필터후에도 순번(seq)가능
필터 : 디자인->슬라이서 삽입
주석 : =SUM(..) + N("주석내용")
0. OneDrive (ms)
- https://onedrive.live.com/about/ko-kr
0. 조건부 서식
- 위치 :홈 -> 스타일 -> 조건부서식
- and, or 연산 :=and(조건1, 조건2), =or(조건1,조건2), =AND($C4>=90,$E4>=70), =OR($C4>=90,$E4>=70)
- 월비교 : =month($D4)=5
0. 데이터유형성검사
- 위치 : 데이터 -> 데이터도구 -> 데이터유효성검사-> 데이터유효성검사
- 콤보 설정하기 (1)
. 종류1: 특정 cell array에 1기,2기,3기중 하나만 선택가능 : 특정cell 영역 선택 -> .. -> 설정tab -> 제한대상.[목록], 원본(1기,2기,3기)
. 종류2: 특정 cell array에 엑셀에 존재하는 목록 선택가능 : 특정cell 영역 선택 -> .. -> 설정tab -> 제한대상.[목록], 원본(엑셀목록선택)
- 콤보 설정하기 (2) -> 이미 입력되어 있는 값중에 잘 못 입력된 값 찾기
. 종류3: 특정 cell array에 이미 1기,2기,3기,4기 값이 입력 되어 있는 경우 : 특정cell 영역 선택 -> .. -> 설정tab -> 제한대상.[목록], 원본(1기,2기,3기)
데이터 -> 데이터도구 -> 데이터유효성검사-> 잘못된 데이터
- 특정 범위만 가능
. 종류4: 특정 cell array에 범위(0~100) 설정 : 특정cell 영역 선택 -> .. -> 설정tab -> 제한대상.[정수], 최소값[0], 최대값[100]
(참고, 설정 메시지 tab에서 풍선도움말 및 잘못된 값 입력시 메시지 내용 입력가능)
- 특정 글자수만 가능
. 종류5:정 cell array에 5자만 가능 : 특정cell 영역 선택 -> .. -> 설정tab ->제한대상.[텍스트 길이], 제한방법[=], 길이[5]
- 자동 key 전환
. 종류6: 특정 cell array에 자동영문key전환, 한글key전환 : 특정cell 영역 선택 -> .. -> IME모드 tab -> 모드[에서 선택]
- 목록이름 설정 방법
1. 여러row, cell에 목록기술
2. 해당 cell array선택
3. 수식 -> 이름정의 또는 excell A 열 위왼쪽에 이름 기술해도 됨
4. 목록이름 보기 : 수식 -> 수식에서 사용
5. 이 이름을 데이터 유혀성검사에서 사용 가능
. 제한대상.[목록], 원본[=목록이름기술] (즉, 원본란에 목록이름을 기술하거나 .. 수식->수식에서사용 클릭 해서 찾아도 됨)
0. 고급필터
- 조건범위란 : 필터할 조건으로 별도 CELL에 기술. 이때 반드시 data array의 title명과 동일해야 됨
- 조건범위 작성방법 :
. and 필터인 경우, 2개의 조회조건을 cell에 기술하고, 선택시 2개의 열을 모두 한번에 선택
. or 필터 인 경우, 필터값지정시 서로 다른 row의 cell에 입력한다.
. 예, 구분:학습, 품명:테이블,자석
. 예, 대소비교인 경우, 거래일: >=2013-11-03, 거래일 : <=2013-11-11, 수량:>=50
. 예, 대소비교인 경우, 실기시험: >=70, 필기시험 : <=80
. 예, 이로 시작하는 문자열: 이*, 이로끝나는 문자열: *이
. 예, 성적에서 평균이상인 학생목록 : 평균:=D2>average($D$2:$D$30)
(예외적으로 함수가 들어가면 title명이 grid의 title명과 달라야 됨)
- 데이트 > 고급
: 목록범위 : 원자료 목록 (TITLE도 반드시 선택)
: 조건범위 : 필터에 적용될 목록 (원자료목록의 title과 필터목록값으로 나열된 열 선택)
: 만약, 목록범위가 10개 컬럼일 경우 이중 4개(즉, 일부컬럼만)만 필터후 copy할 수 도 있다.
: 목록범위와 다른 sheet에 필터결과절를 보여주기
. 기본적으로는 안됨
. 고급필터 클릭 시 -> 결과필터sheet를 먼저 선택 하고 -> 고급필터를 클릭 하면 됨
0. 간트차트
- 목록범위 선택 -> 삽입 -> 2차원가로막대형 ->
0. 일반함수
- IF : =IF(C4>60, "합격", "불합격"), =IF (and(C4>60, C4<70), "합격", "불합겨")
- SUMIF : =SUMIF(C4:C10, ">0", C4:C10) : 0보다 큰값만 SUM
- count함수 : COUNT, COUNTA, DCOUNT, DCOUNTA(특정조건에 대한 COUNT)
- sum함수 : SUM, DSUM(특정조건에 대한 SUM)
- LARGE(array, n) : array영역에서 n번째 큰값 얻기
- SMALL(array, n) : array영역에서 n번째 작은값 얻기
1. =VLOOKUP(찾을값, 조견표, 가져올 열의인수, 옵션)
- 정의 : 조견표상의 찾을값 PK가 첫번째 열로 나열된 경우.
- 찾을값 : 마스터에서 찾을값 PK의 CELL
like문장 [ left(A4, 7) & "*" ] : 7개 문자까지는 동일하고 나머지값은 like처리
- 조견표 : 상세 목록. 반드시 첫번재 열이 PK(기준열)가 됨
조견표영역은 찾은 후 'F4'를 눌러 변하지 않게 한다.
- 가져올 열의인수 : 조견표상의 열 Index
- 옵션 : TRUE:PK값이 동일, FALSE:PK값이 유사
1 : 절대평가(작거나 같은값중 최대값을 찾음)
(이때 조견표는 반드시 오름차순으로 해야 됨)
(사용예, 주로 대학교 평점구할때 사용)
1:상대평가가 가능
2. =HLOOKUP(찾을값, 조견표, 가져올 행의인수, 옵션)
- 정의 : 조견표상의 찾을값 PK가 첫번째 행으로 나열된 경우
3. =INDEX(array, MATCH(lookup_value, lookup_array, match_type))
- 정의 : 조견표상에 PK(기준열)가 첫번째 열이 아닌 경우
- array : 얻고자 하는 값 열의 array+F4
- lookup_value : 마스터에서 찾을값 PK의 CELL
- lookup_array : 조견표의 PK(기준열)의 열의 array + F4
3. = IFERROR(함수명(인자,,,,), "찾는값 없음")
- 내부 함수에서 오류가 발생하면 처리
4. =IF문
- 예) IF(F4="도매", 3, 4)
5. PIVOT
1. 생성방법
. 일반 표를 -> 삽입 > 표 로 변경 (ctrl + t)
. 디자인 -> 피법 테이블로 요약
5. 매크로함수
1. evaluate(cell) : 테스트로 되어있는 cell의 내용을 연산으로 수행 됨 (예, 3+4, 4+2, ...)
2. get.cell( )
