컴퓨터활용 - 실기 기능정리

함수와 부등호 연결

=">="&AVERAGE(F3:F18)

• 부등호는 ""로 감싸주고 함수와는 &로 연결한다.

고급필터의 조건 논리식으로 작성

• 범위가 바뀌지 않아야 할 부분에는 절대화 필요

<일반식 / 논리식 구분>

• 일반식

  • 필드명이 일치해야 한다.

  • 식에 첫 행의 셀 주소가 없다.

  • and, or 사용 불가

  • 와일드키 사용 o

• 논리식

  • 필드명이 일치해서는 안된다.

  • 식에 첫 행의 셀 주소가 포함 된다.

  • and, or 사용 가능

  • 와일드키 사용 x

수식을 이용한 조건부 서식

• 행 전체에 대해 적용된다.

• 항상 첫 번째 셀을 기준으로 설정

• 고급필터와의 차이점

  • 고급필터는 내부적으로 아래 쪽으로 채우기 드래그 하는 원리

  • 조건부 서식은 아래 및 옆으로도 채우기 드래그 하는 원리

    • 절대화를 신경써야한다.

시트 보호 / 통합 문서 보호

• 시트 보호

  • 한 시트 내의 내용을 수정하지 못하도록 막음

• 통합 문서 보호

  • 구조

    • 시트 삽입, 시트 삭제, 시트 이름 바꾸기 막음

  • 창

    • 창의 크기나 위치를 수정하지 못하게 막음

MATCH(), VLOOKUP()

• 수직 데이터를 비교

  • 첫번째 열을 아래 방향으로 비교한다.

    

• MATCH()는 열방향이건 행방향이건 상관없이 데이터를 찾음

  • 위에서 VLOOKUP()으로 열방향을 찾았으니 여기서는 행방향을 찾는 역할

    

배열수식에서 채우기의 원리

• 위와 같이 3개의 함수를 조합해서 사용하는 경우 행 방향으로는 조건이 잘 지정되나, 열 방향으로는 잘 적용되지 않는다.

• 그러므로, 하나의 열만을 지정하고 행 부분만 절대화를 시켜서 채우기를 이용해서 조건을 사용한다.

  • 단, 원본과 결과 필드의 순서가 같아야 한다.

  ={MAX(($B$3:$B$18=$B41)*(I$3:I$18))}

• 최종

  ={INDEX($D$3:$D$18,MATCH(MAX(($B$3:$B$18=$B41)*(I$3:I$18)),($B$3:$B$18=$B41)*(I$3:I$18),0),1)}

MATCH()

• 배열 수식에서는 조건을 주어야하고 (중복되는 값이 있을 수 있기에)

• 나머지 상황에서는 조건을 줄 수 없다.

• 배열 수식에서는 Lookup_value에서 조건과 조건 범위를 지정했다면, 그것의 조건과 조건 범위를 가져와서 Lookup_array에 넣어준다.

사용자 정의 함수

• Visual Basic: alt + f11

• <삽입> - 모듈로 모듈 추가 후, 작성해야 한다.

피벗테이블

• 숫자나 날짜를 그룹화할 때에는 문자열처럼 블록을 잡고 그룹을 지정하는 것이 아니라 해당하는 곳 한 곳을 클릭한 후, 그룹으로 통해 옵션을 지정한다.

<열의 총 합계 해제>

<빈 셀에 특정 값 나오게 하기>

<피벗 테이블 이동>

• 혹은 드래그를 이용해서 이동할 수도 있다.

데이터 유효성 검사

• 설명 메시지, 오류 메시지 여기서 설정 가능

필터

차트에 데이터 추가

• 추가하려는 부분을 필드를 포함하여 블록으로 잡은 후, ctrl + c 한 후,
  차트에 대고 ctrl + v

• 또는 데이터 선택 이용

  

  

도넛형 차트 각도 조절

사용자 지정 서식 적용 방법 (양수;음수;0;문자열)

• 양수; 음수; 0; 문자열

  • 예) #,###; -#,###; "영"; "연습"

• 서식 기호

  • #: 값(숫자)이 없으면 공백

    • 예) 5를 #.##으로 표현하면 5.으로 출력

  • 0: 값(숫자)이 없어도 0이 출력

    • 예) 5를 0.00으로 표현하면 5.00으로 출력

  • @: 문자 표현 서식 기호

    • 예) "컴활"을 @"합격"으로 표현하면 "컴활합격"이 출력

콤보(목록)상자에 목록값을 입력하는 법

• 개체명.Additem "국어"

• 개체명.Additem "영어"

• 개체명.Additem "수학"

  • 하나하나씩 지정

• 개체명.Rowsource = "A1:A5"

  • 셀의 범위 지정

  

날짜, 시간 함수

• YEAR(): 년도 추출

• MONTH(): 월 추출

• DAY(): 일 추출

• HOUR(): 시각 추출

• MINUTE(): 분 추출

• SECOND(): 초 추출

홀수, 짝수

• 엑셀

  • Mod(수, 2) = 1 : 홀수

  • Mod(수, 2 = 0 : 짝수

• 액세스

  • 수 mod 2 = 1 : 홀수

  • 수 mod 2 = 0 : 짝수

페이지 설정

인쇄 영역 설정

배열수식에서 IF문이 필수로 필요한 함수

• 배열 수식에서 COUNT(), AVRAGE()와 같은 함수는 IF문과 무조건 함께 사용되어야 한다.

  • IF문이 없으면 사용할 수 없다.

• 배열 수식에서 개수를 셀 때만 예외적으로 범위를 1로 둘 수 있다.

  ={COUNT(IF($C$3:$C$32=M7,$G$3:$G$32))&"명"}

  ={COUNT(IF($C$3:$C$32=M7,1))&"명"}

사용자 정의 함수 - 중첩 IF문

피벗테이블 계산 필드 추가

표시형식 - 쉼표 스타일

• 스타일 적용은 셀 서식에서 설정할 수 없다.

• 셀 스타일 서식을 적용하라고 하는 경우 다음을 적용해야 한다.

  

데이터 통합

• 결과가 나올 필드, 셀 전체 지정

• 범위 지정(필드 포함) 후, <추가> 버튼 클릭

  • 첫 행, 왼쪽 열 박스 클릭

    

    • 클릭하지 않으면 표시한 부분이 나오지 않게 된다.

누적 세로 막대형 차트 만들기 및 차트 이동

축 서식에서 단위 변경

데이터 레이블 서식

데이터 표

• 열 입력 셀에서, 왼쪽 표에서 이자율 클릭

  • 참조 값이 열 방향으로 나열되어 있기 때문

행 방향, 열방향

셀서식 - 사용자 정의

• [조건]# - 위치 지정"문자열"

VBA - 목록 상자

VBA - 시트

• RANGE("셀주소). : 지정된 셀 변경

• TARGET. : 커서가 있는 곳 변경

• CELLS. : 시트 전체 변경

• FONT.NAME = "궁서체"

• FONT.SIZE = "14"

• FONT.BOLD = TRUE(FALSE)

• FONT.ITALIC = TRUE(FALSE)

• 셀의 위치가 명시되지 않은 문제는 값들이 변경된 상태이면 안 된다.

고급필터

• 블럭설정을 안하고 아무곳에 커서를 두고 사용하려면 맨 윗줄에 '[표1]'과 같은 것들은 있어선 안된다.

LEFT, RIGHT 값 비교

• LEFT와 RIGHT 함수의 출력 값은 문자열이기에 value()로 감싸서 정수형태로 만들어 주거나 위와 같이 ""로 정수를 문자열 형태로 바꿔준다.

부등호와 함수가 같이 있을 때는 ">=" & 함수

SMALL()

일반식 & 논리식의 필드명

• 일반식은 필드명과 같게, 논리식(TRUE, FALSE)은 다르게

  • 논리식은 첫번째 행의 셀만 지정하고, 값이 TRUE, FALSE로 나옴

  • 일반식은 전체 행을 지정

  

<위의 월불입액 합쳐서 논리식으로 처리>

조건부 서식

• 절대화 되는 것은 행인지 열인지 구분해서 절대화 되면 안되는 부분은 $ 지워주기

• 수식 여러개일 때는 and(), or()로 연결하기

WEEKDAY()

RANK.EQ() - 순위 구하기

• 기본 정렬: 내림차순

• 점수가 높은 사람이 순위가 높기 때문에 어떤 순위 이상인 사람은 <=

페이지 레이아웃

• 인쇄 창에서 확인 가능

머릿글, 바닥글

• 짝수와 홀수 페이지를 다르게 지정 체크

• 바닥글 편집 클릭

반복

계산 작업

SUMPRODUCT()

MIN()

DCOUNTA()

• 숫자 이외의 문자도 함께 센다.

• 데이터 함수는 조건을 걸 수 있다.

🛑 채우기가 없는 문제에 대해서는 절대화를 할 필요는 없다.

글자 붙이기 - TEXT()

• 형식을 지정할 때 0, # 둘 다 사용 가능

  • 값이 없을 때, #은 공백으로 표시

  • 0은 0으로 표시

배열 수식

IFERROR()

=IFERROR(AVERAGE(IF(($E$24:$E$46=I31)*($G$24:$G$46 >= 80), $F$24:$F$46)),"해당없음")

사용자 정의 함수

SELECT CASE

Select case 개체명

- Case "사과" (문자일 때)

- Case is >= 1 (숫자일 때)

- Case else (이것도 저것도 아닐 때)

End Select

FORMAT()

FORMAT(값, "형식")

- 0: 값이 없어도 0 표시
- #: 값이 없으면 아무 표시 X

피벗테이블

외부 데이터 가져오기

자동 필터

• 같은 필드에서는 and, or연산이 가능하지만, 다른 필터와의 비교는 불가능하다.

  • 이런 기능은 고급 필터에서만 가능하다.

데이터 유효성 검사

목록 상자

• 직접 적어줘도 기능한다.

차트

항목에 따라 다른 차트

축, 보조축

• 직접 변경해서 다시 설정으로 변경되어야한다.

그림 붙이기

매크로

데이터 표

• 미래가치에 있는 값의 식을 그대로 클릭해서 가져와야 한다.

  • 변경되면 X

미래가치 - FV()

VBC

상향, 하향 버튼

시트 이벤트

고급 필터

FIND(), IFERROR()

조건부 서식

DAYS()

• 두 날짜 사이 차이를 구한다.

DAYS360

• 두 날짜 사이 차이를 구한다.

  • 단, 1년을 360일로 처리

  • 모든 달을 30일로 계산

    • 월급같은 부분을 계산하기 위해 존재

페이지 레이아웃

계산 작업

DGET()

IF(), ISERROR(), VALUE()

FREQUENCY()

• 먼저 범위를 지정하고 사용하기 때문에, 즉 채우기를 하지 않기 때문에 절대화 x

• 배열수식에 대한 함수이기 때문에 ctrl + shift + enter

REPT()

• 소수점 아래는 버린다.

MIN(), IF(), MONTH(), ROUND()

• ROUND()

  

• ROUND(N23, -1)

  • 정수 자리까지 반올림

  • 값이 없어지면 0처리 된다.

    

🛑 IF와 함께 사용해야만, 배열수식으로 사용할 수 있는 함수

• AVERAGE
• MIN
• COUNT

사용자 정의 함수

피벗테이블

계산 필드

필드 서식

• 각 필드에 각각 적용시켜야한다.

  • 사용자함수 가장 아래쪽에 방금 전에 사용한 형식이 있음

정렬

• 블록 설정을 하면 한 필드만 움직임 (연관된 값 이동 x)

  • 따라서 정렬 시에는 한 개만 잡고 정렬

  

데이터 새 시트로 빼기

• 피벗테이블의 셀을 더블클릭하면 원본 레코드들이 새 시트로 생성된다.

정렬

사용자 지정

• 필드 포함하여 전체 블록 범위로 잡기

데이터 유효성 검사

차트

• 범례를 통해서도 차트 수정이 가능하다

  • 차트에 계열차트가 나와있지 않을 수 있기 때문

데이터 계열 서식

추세선 추가

도형 스타일

차트 영역 서식

매크로

정렬 & 부분합

<첫번째 부분합>

<두번째 부분합>

배지

조건부 서식

단추

VBA

다른 시트 값 참조

메시지 상자 - Msgbox

<어떤 버튼을 눌렀는지를 저장하지 않을 경우>

Msgbox "나올 메시지", 단추 옵션, "제목"

<어떤 버튼을 눌렀는지를 저장할 경우>

변수 = Msgbox ("나올 메시지", 단추 옵션, "제목")

• 변수는 메시지 박스의 결과 값이 저장되는 곳

• 아이콘 유형

  

종료

고급 필터

<> (NOT)

• 논리식에서 날짜를 표현하기 위해서 ""로 묶어준다.

  • 아니면 일년번호 형식으로 바뀌기 때문

조건부 서식

MOD()로 배수 구하기

• 3으로 나눈 나머지가 0이어야지 3의 배수가 된다.

COUNTIF()

• 조건 부분을 ""로 묶어줘야 한다.

페이지 레이아웃

시트 흑백 처리 및 간단하게 인쇄, 오류 표시

계산 작업

CHOOSE()

• 주어진 번호에 맞는 값 출력

PMT() - (대출 상상하기)

• 분기별 불입액 계산

TRUNC() - 절삭

<INT()와의 차이점>

• 음수일 때 드러난다.

• 정수형은 반올림 시키고, TRUNC는 올림, 내림 없이 그대로 소수점 자리만 잘라낸다.

• TRUNC는 -1이 나오고, INT는 -2가 나온다.

FV() - 미래가치 산출 (적금 상상하기)

• 아래 두개는 선택사항

ROUNDUP() - 올림

• 0: 소수점을 주지 말아라

• 1, 2 ~: 소수 첫째까지 나와라, 소수 둘째자리까지 나와라...

• -1 ~ : 정수 자리 부분이 삭제된다.

<ROUND(), ROUNDUP(), ROUNDDOWN()>

• 반올림, 무조건 올림, 무조건 버림

LARGE(), SMALL() - 범위와 순위 지정해서 큰 값, 작은 값 찾기 + 배열 수식

• =LARGE(IF(조건, 계산범위), 번호)

• LARGE((조건1)*(계산범위), 번호)

  • 조건이 and일 때는 (조건1) * (조건2)

  • 조건이 or일 때는 (조건1) + (조건2)

사용자 정의 함수

• #2017-08-20# or #2017-8-20# 이라고 입력하면 엔터를 치는 순간 알아서 #8/20/2017#이렇게 변경된다.

피벗테이블

• 숫자 데이터의 경우에는 그룹을 블록하는 게 아니라, 아무거나 1개만 잡기

• 범위가 지정되어서 출제되었을 시, 자동에 체크는 해제하기

• 나누어 떨어지지 않을 시, 시작값을 기준으로 하고, 포함되지 않는 것들은 그룹화하지 않는다.

• 문자열은 블럭 설정을 하고 그룹화를 해야한다.

부분합

값 필드

열의 총합계 값을 기준으로 정렬

자동 필터

데이터 유효성 검사

차트

• 범위가 지정되어 있지 않을 시, 행/열 전환 기능을 사용할 수 없기에 전부 제거하고, 다시 범위 설정

매크로

목표값 찾기

조건부 서식

VBA

• 엑세스, 프로시저에서는 ()를 사용하지 않는다.

ListIndex

체크 상자

취소 버튼

• 둘 다 기본값이므로 생략 가능

기본작업

고급필터

=AVERAGE(D4:G4) > 80

=G4>AVERAGE($G$4:$G$26)*70%

조건부 서식

인쇄 시, 메모 설정

계산작업

IF(), PV(), ROUND()

DCOUNT(), REPT(), AND(), ABS()

=AND(ABS(D13-E13)>=10, F13="A")

• 조건을 적용할 때는 필드명을 포함한다.

배열수식

• COUNT()를 이용할 때에는 전체의 범위가 어느 필드의 배열이든 상관이 없지만, SUM()의 경우에는 전체의 범위를 필드의 배열을 설정하면 합계가 나오게 되므로, 수를 셀 때에는 무조건 1을 넣어준다.

HLOOKUP(), LEFT(), RIGHT(), VALUE(), & 연산자

사용자 정의 함수

분석작업

피벗테이블

통합

• 조건 직접 입력

  

🛑첫행과 왼쪽열을 체크해줘야 필드값이 그대로 있음

<조건부 서식>

차트

매크로

셀 이름 주기

시나리오 분석

🛑표시 버튼을 클릭하면 변경된 값이 적용되기 때문에 등록만 할 경우에는 누르면 안된다!

시나리오 테스트

• 이름 초기화

  

• 시나리오 삭제

• 버튼 클릭

![]

프로시저

<입력행을 계산하는 방법>

🛑연산자와 연산자 사이를 꼭 띄워줘야 한다. (엑셀과는 다름)