2주차 학습정리

1차시 : 엑셀 핵심 규칙과 셀 서식

셀의 핵심규칙은 셀에 입력되는 데이터 형식이 통일되야 관리하기 편리하다는 것이다.

 

셀 서식은 패션에서 스타일링이다.

셀 서식 Ctrl + 1

① #,##0 ② 세미콜론(;)

세미콜론은 양수;음수;0;텍스트 서식으로 구분한다.

 

엑셀 color index

★ 중요한 사용자 지정 서식 

정수 0

기본 #,##0

소수점 #,##0.0

단위 #,##0"원"

절사 #,##0,

절사 시 맨끝에 콤마 1개 "천", 콤마 2개 "백만", 콤마  3개 "10억", 조 4개 "조"

 

날짜 서식 : y, m, d, a의 조합

요일은 영어표시 ddd 한글표시 aaa

 

숫자를 문자로 관리하는 법 (3가지 방법)

일반서식 ctrl shift ~

방법① 1회성 : 작은 따옴표(enter 키 옆)

방법② 셀서식을 텍스트

방법③ 함수 =TEXT(셀, "셀서식")

 

숫자를 한국어로 표시 (2가지 방법)

셀 서식 / 기타 / 로캘(위치)란 한국어 / 숫자(한글)

 

문자인 숫자를 숫자로 바꾸기

방법① 새로운 셀에서 *1을 해준다

방법② 빈셀에 1을 입력하고 복사하여 바꿀 범위 드래그 후 선택하여 붙여넣기(Ctrl Alt V)로 곱하기 선택

 

2차시 : 데이터 관리

보고서 작성규칙 5가지 유의

① 숫자는 오른쪽 정렬하기

② 천단위 구분기호 표시하기 : ★Ctrl Shift 1

③ 단위 반드시 명시하기 : 셀 서식 사용

④ 들여쓰기로 위상 표현하기 : 들여쓰기 한 번하고 F4(이전 작업 반복)

⑤ 세로선을 적절한 곳에 사용하기

 

보고서 디자인하기

tip) 굵게 표시 ctrl B

tip) 상위항목 혹은 강조 부분 : ctrl 드래그 + 배경채우기

tip) 직접입력할 부분만 강조해두기 : 이름상자(ctrl G) / 옵션 / 상수 (숫자)

 

! keypeople 대비하는 법

데이터 유효성 검사

데이터 유효성 검사로 입력할 수 있는 범위를 제한하자

목록상자를 만들어 두어 입력 범위를 정해두기 : 목록확인 Alt↓

 

★ 동적 목록 상자 

동적 목록 상자 예제

동적범위 입력은 원본 범위로 직접 입력(유효성 검사, 조건부 서식, 피벗) 하거나 이름관리자(CTRL F3)에 지정 후 원본 범위에서 F3 키로 선택한다.

 

① 표 (편리)

step1. 표설정 ctrl T 

step2. 디자인 / 표이름 / 이름 설정

step3. 데이터 유효성 검사에서 =INDIRECT("이름") 

tip) 명령어 내 수식입력란에서 F2 누르면 커서이동 된다.

 

UPGRADE. 동적범위 목록 여러개 설정하기

① 머리글

목록을 머리글로 드래그 OR =INDIRECT("선생님[#머리글]")

② 입력란 (& 기호 활용)

 =INDIRECT( "표[범위]" ) → =INDIRECT( "표[셀 주소]" ) 

EX) =INDIRECT( "선생님["& $D$3 &"]" )

 

② 동적범위 (유연, 복잡)

=OFFSET(시작셀, 아래이동, 오른쪽이동, 높이, 너비)

여기서 높이는 참조범위의 상하이고 너비는 참조범위의 좌우이다.

0FFSET함수의 높이를 활용!

 

기본공식 (OFFSET입력, 시작셀, 콤마 3번, COUNTA범위, 괄호 2번 닫기)

= OFFSET(시작셀 ,,, COUNTA(세로범위) ) 

 

예외1) 중간 빈셀이 있는 경우

=OFFSET($기준셀,,,MAX(IFERROR(MATCH("*",$세로범위,-1),0),IFERROR(MATCH(9E+307,$세로범위,1),0)),가로너비INT)

 

★ 참조범위

문자가 포함된 경우

=OFFSET($시작셀, , ,MATCH("*", $참조범위, -1))

숫자가 포함된 경우

=OFFSET($시작셀, , ,MATCH(9.999999999E+307, $참조범위, 1))

예외2) 값이 있는 셀만 확장하는 경우

=OFFSET($기준셀,,,COUNTIF($세로범위,"?*"))

 

(365이상)

=FILTER(범위, 범위<>"")

시작 셀 뒤에 #키만 추가

머리글이 많은 경우

표[머리글] 명령어 활용하면 된다.

 

한영 변환 제한

데이터 유효성 검사 / IME / 영문 OR 한글

 

중복 제한 (가령, 쿠폰 발급 중복 방지)

=COUNTIF(범위, 셀)

 

데이터 유효성 검사 / 사용자 지정 / 아래 코드

=COUNTIF(열전체, 활성화 셀)<=1

EX) =COUNTIF(B:B, B5) <=1

 

값 불러오면서 중복제한 (AND함수 활용)

= AND( 제한할 셀 조건, 불러올 값 존재 조건)

= AND( COUNTIF(범위, 셀) <=1, COUNTIF(범위, 셀) >=1)

 

QnA

데이터 형식 맞추기

날짜 콤보) 날짜 범위(CTRL SPACE),  텍스트나누기(ALT/ A / E), 날짜변환(ENTER / ENTER / ALT + D / ENTER)

숫자 콤보) ALT / A / E / F

 

Byte 자동 변환 함수 만들기 (VBA)

삽입 - 모듈 - 다음 코드 삽입

 

Function ByteConversion(Rng, Unit As String)

Dim Val As Double

Select Case Unit
   Case"G"    
Val = Rng.Value / 1024 / 1024/1024
   Case "M"
           Val = Rng.Value / 1024 / 1024
    Case "K"
           Val = Rng.Value / 1024
End Select

ByteConversion = Val

End Function

 

출처 : 진짜쓰는 실무 엑셀 2주차 라이브 강의를 들었습니다.

https://www.oppadu.com/

엑셀강의 대표채널! 오빠두엑셀 - 오빠두엑셀

https://www.oppadu.com/%EC%97%91%EC%85%80-%EB%8F%99%EC%A0%81%EB%B2%94%EC%9C%84-%EC%99%84%EB%B2%BD%EC%A0%95%EB%A6%AC/

엑셀 동적범위 완벽 정리 | 업무 자동화 실무자 필수 스킬 - 오빠두엑셀