그저 놀랍습니다. 석찬님!

충실한 활동과 일지도 물론이지만,

무엇보다 어려움을 대하는 긍정의 힘.

그래서 대안도 늘 분명하신것 같아요^^

취업준비라는 것이, 아무리 해본 사람도. 

지금 치열한 사람을 이해하기 쉽지 않은 일이라고 생각되는데,

석찬님은 즐기며 이루실수 있으실겁니다! 응원할게요!

일차 / 날짜 : 050일차 / 7월 7일 (월)

취침 / 기상시간 : 오전 11시 35분/ 오전 5시 25분

수면/ 활동시간 : 5시간 50분/ 7시 20분 ~  8시 40분 (엑셀), 오후 9시 ~ 9시 20분(재활)

[함수식 내에서 큰 따옴표 사용관련]
고정되어 있는 애들만 큰 따옴표 사용
예를 들면 부등호 ">" 변동 되는 애들(예로 셀주소인 B3, 함수의 경우)은 &를 붙이고 큰따옴표를 쓰지 않고  바로 씀. 더 이상 헷갈리지 말자.

[SUMIFS, 여러 조건 걸어서 더하기]
SUMIFS(금액,매장명,$C$19,날짜,">="&$B21, 날짜, "<="&EOMONTH($B21,0))

[배열수식, COUNTIF, SUMPRODUCT, 찾을문자(/, \, #) 갯수구하기, 와일드카드문자사용]
COUNTIF에서 조건을 배열로 주면 배열생성
SUMPRODUCT(COUNTIF(A16,"*"&찾을문자&"*"))

[배열수식, 필드1에서 0 초과되는 값들 중에서 최소값에 해당하는 필드2의 합계]
{=SUMIFS(필드2,필드1, MIN(IF(필드1>0,필드1)))}

몰입도 4

만족도 4

재활

몰입도 3

만족도 3

일차 / 날짜 : 051일차 / 7월 8일 (화)

취침 / 기상시간 : 오전 10시 56분/ 오전 5시 16분

수면/ 활동시간 : 6시간 20분/ 5시 50분 ~  7시 20분 (엑셀), 오후 7시~ 7시 30분

배열수식 깊게 파보자

A.[배열수식, COUNTIF, SUMPRODUCT, 찾을문자(/, \, #) 갯수구하기, 와일드카드문자사용]
COUNTIF에서 조건을 배열로 주면 배열생성
SUMPRODUCT(COUNTIF(A16,"*"&찾을문자&"*"))

B.[배열수식, 필드1에서 0 초과되는 값들 중에서 최소값에 해당하는 필드2의 합계]
B_1 -> {=SUMIFS(필드2,필드1, MIN(IF(필드1>0,필드1)))}

둘의 차이를 찾아보자. 일단 첫번째 꺼는 countif 에서 찾을문자 의 조건이 둘 이상이 되어 countif 함수의 결과가 배열이 나온다.

하지만 두번째 수식에서 나는 sumifs에서 CTE를 하지 않고, SUMPRODUCT 로 계산을 마무리 하니 , 그러니까

B_2 -> SUMPRODUCT(SUMIFS(필드2,필드1,MIN(IF(필드1>0,필드1)))) 이렇게 계산했더니 계산 값이 달랐다.

B_2 수식을 작성한 이유는 필드 1이 배열이니까 IF 문을 걸면 자동적으로 배열이 생성되고 SUMPRODUCT로 해결 될 거라고 생각했다.

B_1과 B_2 둘의 차이는? 두번째 수식에서 IF 문까지는 배열이 나온다. 하지만 MIN 에서 배열이 상수로 바뀌어 버린다.

B_2 수식에서 CTE를 치면 결과가 제대로 나온다. {=SUMPRODUCT(SUMIFS(필드2,필드1,MIN(IF(필드1>0,필드1))))}

 이 수식에서 사실 SUMPRODUCT 는 필요없는 함수다.

결론, 배열함수냐 아니냐에 따라 결과가 달라짐. 배열함수는 CTE 필요 없음. 함수의 계산 순서 생각도 필요!

A와 B_1의 차이는 SUMPRODUCT 는 배열함수 라서 COUNTIF결과를 바로 배열로 받아 계산하지만

SUMIFS는 배열함수가 아니기 때문에 CTE를 치지 않으면 배열의 첫번째 값만을 사용하게 되버린다.

뒤에 SUMPRODUCT가 나오더라도 SUMIFS가 먼저 계산이 들어가기 때문에 SUMPRODUCT는 효과가 없어진거다.

그러니까 SUMIFS에서 배열로 계산을 시작하려면 CTE 가 필요하다.

엑셀장인의 정리 복습

배열: `항목의 모임 또는 집합`

항목은 하나의 행 또는 열(1차원배열)에 있거나 행과 열(2차원배열)에 있을 수 있습니다.

배열식은 이 항목을 가지고 있는 계산식이라고 보면됩니다.

배열식은 수식작성후 커서가 깜박이는 상태에서 Ctrl+Shift+Enter 입력하여 수식을 완성해야합니다.

배열함수는 굳이 그럴 필요는 없습니다.(단, 단일 셀 배열식이나 수식이 복잡해지는 경우에는 예외입니다.)

배열식은 주로 항목군 간 연산이 이뤄지며 항목의 개수가 서로 같거나 하나인 것이 일반적입니다(1:N, N:1, N:N)

엑셀에서는 배열함수를 제공하기도 함.

배열함수는 SUMPRODUCT, TRANSPOSE, FREQUENCY, MMULT 등이 있고,

배열함수는 해당 함수자체가 내부적으로는 배열식으로 계산되는 엑셀 내장함수입니다.

배열식 종류는 2가지이며,

단일 셀 배열식: 배열식이 한 셀에 있음.  (대부분의 배열식)

다중 셀 배열식: 동일 배열식이 여러 셀에 들어감 (TRANSPOSE, FREQUENCY)

수식을 분석하는 방법, F9 누르고 확인, 함수는 안에서부터 확인,

몰입도 3.5

만족도 3.5

재활

몰입도 3

만족도 2

일차 / 날짜 : 052일차 / 7월 9일 (수)

 취침 / 기상시간 : 오후 10시 45분/ 오전 5시 00분

수면/ 활동시간 : 6시간 15분 / 오전7시 20분 ~ 8시 30분(엑셀)

숙면에 대해 : 밤에 배아파서 뒤척거림(예비군 점심? 저녁에 매운 제육?)추웠음, 코막힘, 문 닫고 자서?

엑셀함수 강의

index_match

인덱스 에서 행, 열 위치에 0 값을 넣음 전부 선택

실무함수

DATEDIF 두 날짜 사이에 기간 산출.   안쓰니까 잊어버림. DATEDIF(I4,J4,"m") "m"은 월 단위, "y", "d" 로도 환산단위 생각

인수1의 날짜가 인수2 의 날짜보다 빠른 날짜여야함

인수1에 1986-12-26 으로 하면=1986-12-26 계산값이 들어감. date 함수 이용이 편리

if

주민번호로 나이 구하기. DATEDIF(DATE(MID(W3,1,2),MID(W3,3,2),MID(W3,5,2)),TODAY(),"y")+2

date 에서 년 단위는 기본으로 1900년 대로 반영 되기 때문에 2000년 대의 주민등록번호를 날짜로 바꾸려면

년도에 100을 더해주어야한다.

예시)DATE(MID(W4,1,2)+100,MID(W4,3,2),MID(W4,5,2))

S12시트와같음

index와 배열을 이용해서 두가지 조건이 일치하는 값 찾기

두가지 조건은 이름, 분류. 가저올 값은 코드

key포인트는 조건 두가지를 배열 조건으로 주어서 true 끼리 곱해서 1이 나오는 배열을 생성 후, 그 배열에서 1을 찾는 것

INDEX(코드,MATCH(1,INDEX((A2=이름)*(B2=분류),),0))

내가 짠 결과가 안나오는 수식

INDEX(I2:I12,MATCH(1,(B9=분류)*(A9=이름),0), 0)

이렇게 짜면 match 부분에서 배열로 안 받고 식 하나만 계산 => CSE 하면 됨(7/22)

몰입도 3

만족도 3

일차 / 날짜 : 053일차 / 7월 10일 (목)
취침 / 기상시간 : 오후 11시 30분/ 오전 5시 0분
수면/ 활동시간: 5시간 30분 / 오전 7시 45분 ~ 8시 45분(엑셀)

늦게 잔 이유: 자기 전 공채 확인하다가. 불빛 때문. 창문열고 커텐치고 자니 코막히지 않음. 긴팔 입고자서 안추웠음.

엑셀 함수 강의

시간_날짜

일단위의 피벗을 월단위로 그룹 만들기
피벗 테이블에서 날짜 아무거나 클릭 후 우클릭 그룹 월단위로 지정

엑셀교재 397번

피벗테이블에서 데이터가 없어도 0으로 표시하는 법(실제신계약비 상품 별 배부에서 피벗 테이블에 값이 없어서 칸이 달라지면 수식줄이 달라지는 경우를 null 데이터 입력 없이 방지 할 수 있음)

400번 계산 필드 삽입해 '세금' 항목 추가하기

옵션 -> 계산 그룹 -> 필드, 항목 및 집합 명령 단축 -> 계산필드

401번 계산 항목 삽입해 성장률 계산하기

기본강의

피벗_db

`피벗 테이블` 시트를 다른 파일에 복사하면 참조 데이터가 현재파일을 가리키므로 현재파일을 저장하고 복사가 아닌 이동하면 됨.

피벗 옵션 -> 피벗 테이블이동

P2

피벗테이블에서 누계 및  항목 표시하는 법

누계를 할 필드 추가 후 값 필드 설정 -> 값 표시 형식

여유롭게 씻다가 버스 놓침. 좀 더 일찍 일어나거나, 출근 준비를 빨리하거나.

몰입도 4

만족도 4

재활은 못했음

일차 / 날짜 : 054일차 / 7월 11일 (금)
취침 / 기상시간 : 오후 10시 30분/ 오전 5시 0분
수면/ 활동시간: 6시간 30분 / 오전 7시 17분 ~ 8시 40분(엑셀)

숙면에 대해: 버스에서 먹은 바나나가 안 좋았는지 배탈. 먹는거 신경써야 될 듯

함수강의

실무함수

EOMONTH 특정일에서 몇개월 전후의 말일을 리턴 <=> EDATE 특정일로부터 n개월 후(전)의 일, 말일이 일치하지 않는 경우는 해당 달의 말일

NETWORKDAYS 시작일과 종료일 사이의 휴일을 제외한 일수

시간_날짜

잊어버린 것 => 날짜를 요일로 표기하는 형식 aaa

월 별로 해당 일자가 몇 주인지 표기하는 함수 식. 예를 들면 5월 1일은 5월의 1째주.

키포인트는 해당일자의 주에서 해당월의 1일의 주를 뺀 후 1을 더하는 것.

=WEEKNUM(B24,2)-WEEKNUM(EOMONTH(B24,-1)+1,2)+1

WEEKNUM(serial_num,return_type)

return_type 주 시작 요일
1 일요일부터 주가 시작됩니다. 요일에는 1부터 7까지의 번호가 부여됩니다.
2 월요일부터 주가 시작됩니다. 요일에는 1부터 7까지의 번호가 부여됩니다

if

예제)

1.44  1.5  1.47  1.12  1.88 여기서 1.2 미만이 하나라도 있으면 NG , 아니면 OK

어느사용자의 식 IF(K3:O3>1.2,"OK","NG") => 식이 틀렸음. 계산 순서를 보면, K3의 값이 1.44가 1.2보다 크므로 OK 표기.

IF 조건의 배열에서 K3만 조건 확인이 되고 나머지는 안 돌아감.

이럴 때 countif 를 사용.

답은 괄호 안에        ( IF(COUNTIF(K3:O3,"<1.2")>0,"NG","OK")  )

sumproduct

조건 값1 값2 값8 값9 값10
A 1  1 2 2
B 1 1 2  5
A 2 1 1 4 5
C 1 1 5 4 5
B 5 1 1  

조건 값이 A면서 데이터가 2인 셀의 개수 세기

=SUMPRODUCT((A14:A18="A")*(B14:F18=2)) 

잘 모르겠음 다시 봐야 됨

SUMPRODUCT(N(A14:A18="A"),N(B14:F18=2))  이건 안되는데 위에는 왜 되는건지 모르겠음
 

몰입도 3.5

만족도 3.5

일차 / 날짜 : 055일차 / 7월 12일 (토)
취침 / 기상시간 : 오후 10시 40분/ 오전 5시 20분
수면/ 활동시간: 6시간 40분 / 오전 5시 30분 ~ 오전 6시 40분(다른사람 단군일지 읽기, 낯선곳에서의 아침 읽기) 오전 6시 40분 ~ 7시 00분 (엑셀) 7시 00분 ~ 09시 30분 아침식사, 딴짓, 09시 30분 ~ 11시  (엑셀) 

숙면에 대해: 4시에 한번씩 깨는 이유는?

어제 sumproduct 문제에 대해 해결

sumproduct

조건 값1 값2 값8 값9 값10
A 1  1 2 2
B 1 1 2  5
A 2 1 1 4 5
C 1 1 5 4 5
B 5 1 1  

조건 값이 A면서 데이터가 2인 셀의 개수 세기

=SUMPRODUCT((A14:A18="A")*(B14:F18=2)) 

SUMPRODUCT(N(A14:A18="A"),N(B14:F18=2)) 

둘의 다른 건 위에는 sumproduct 안에 곱하기(*) 지만 아래는 쉼표( , )임.

sumproduct 내 인수 끼리는 배열의 형태가 같아야 계산이 됨.

배열의곱셈 계산에서는 두 배열의 열수가 일단 같아야 함.

예로 아래 sumproduct 내의 배열을 보면 첫 번째 배열은 매트릭스로 치면 1*2, 두 번째는 5*2 이다.

=SUMPRODUCT({1;0}*{0,0,0,1,1;0,0,0,0,0})

계산 방식은

1. 첫 번째 배열의 1행의 값인 1이 두 번째 배열의 1행의 5개의 값(0,0,0,1,1)들에 각각 곱해진 후 더함.

2. 첫 번째 배열의 2행의 값인 0 이 두 번째 배열의 2행의 4개의 값(0,0,0,0,0)들에 각각 곱해진 후 더함.

3. 1과 2의 결과를 더함.

배열 계산에서 첫 번째 배열이 1*2 가 아니면서 계산 가능한 배열의 매트릭스 구성은 5*2 임!

5*2 일 경우는 계산하는 각 배열에 1대1 대응으로 계산하면 됨.

여기서 배열을 매트릭스로 표현 하면 직사각형이 나와야 한다는 것. (배열 내의 각 행을 구성하는 숫자가 같아야 한다.)

엑셀장인 카페에서 엑셀 수식이 안 먹힐 때에 관한 것 알아본 내용

1. 서식 지정이 텍스트인 경우

2. 단축키 art+~ 수식결과 보기

3. db에서 가져올 때 문제 발생, 리프레쉬(F2 enter)나 텍스트 나누기(art+ d, e) 이용해서 data 가공

몰입도 2

만족도 3

오늘은 바로 엑셀 공부에 들어가지 않았고, 중간에 아침먹고 인터넷 딴짓을 해서 몰입도가 낮았다.

무엇보다도 7월 말까지 기본강의 함수를 체계적으로 끝내야겠다는 생각으로 계획을 구체적으로 잡아야

몰입도가 올라갈 것 같다. 적어도 8월에 엑셀장인 매크로 강의를 들을 수 있도록 준비를 해놔야 한다. 그래야

9월 10월 부터 매크로 단련 들어갈 수 있다. 무엇보다 내가 빨리 엑셀을 잘 할 생각을 하면 설렌다. 재밌기도 하고.

좀 더 스퍼트를 올리자.

일차 / 날짜 : 056일차 / 7월 13일 (일)
취침 / 기상시간 : 오후 11시 00분/ 오전 5시 20분
수면/ 활동시간: 6시간 20분 / 오전 5시 30분 ~ 오전 5시 45분(요가, 잠깨기)오전 5시 45분 ~ 8시 00분 (엑셀) 

엑셀장인 함수

sumproduct

상반기 건수 구하기
SUMPRODUCT(N(MONTH($E$43:$E$49)<7))
논리 연산자를 통해 true, false 배열 만들어 내기
N 함수를 이용해서 true,false를 숫자로 변환

전체횟수와 출석이 같은 행의 개수?
SUMPRODUCT(N(E62:E69=F62:F69))

엑셀기본강의

정렬

주소 순으로 정렬 하기

1 바꾸기 이용하여 `번지`-->` 번지`
2 텍스트나누기(공백문자 구분자)
3 숫자필드로 정렬
4 3개필드를 한필드로 합치기

신정동 11 번지
신정동 21 번지
신정동 111 번지

여기서 정렬을 할 때 위 처럼 정렬이 되려면 활성셀이  11이 들어 있는 셀 위에 존재해야 모두 정렬이 됨. 정렬하는 필드명에서 정렬해야 하는데 숫자에 대한 필드명이 따로 없기 때문에 그 위에 칸이 자동으로 필드명이 있는 곳으로 여겨지는 듯.

행기준으로 정렬(엑셀교재 352) 

몰입도 2.5

만족도 2.5

앉아서 2시간을 쭉 이어나가지 못한다. 중간에 일어나고. 요즘 배탈이 잦은게 원인인가. 오늘 계획을 세워서 목표를 확실히 하자.

일차 / 날짜 : 057일차 / 7월 14일 (월)
취침 / 기상시간 : 오후 11시 00분/ 오전 5시 00분
수면/ 활동시간: 6시간 00분 / 오전 6시 30분~ 7시(엑셀복습,버스안), 7시 40분 ~ 8시 40분 (엑셀) 

index 에 대해(엑셀내 함수 설명)

요약 

index는 배열형과 참조형 두가지가 있음.

index에서 첫 번째 인수에서 area를 여러개 지정할 수 있음, area 선택 인수는 제일 마지막인수로 결정. INDEX((A1:C6,A8:C11),2,2,2).

SUM(B2:INDEX(A2:C6,5,2)) B2에서 시작하여 A2:A6 범위의 다섯 번째 행과 두 번째 열이 교차하는 위치 사이의 범위의 합, 즉 B2:B6의 합

테이블이나 범위에서 값 또는 값에 대한 참조를 반환합니다. INDEX 함수에는 배열 (배열: 여러 가지 결과를 만들거나 행과 열로 구성되는 인수 그룹에 대해 연산이 이루어지는 한 개의 수식을 작성하기 위해 사용됩니다. 배열 범위는 공통 수식을 공유하며 배열 상수는 한 개의 인수로 사용되는 상수 그룹입니다.)형과 참조형이라는 두 가지 형식이 있습니다.

원하는 작업 참고 항목
지정된 셀이나 셀 배열의 값 반환 배열형
지정된 셀에 대한 참조 반환 참조형

배열형
행과 열 번호 인덱스로 선택한 배열 (배열: 여러 가지 결과를 만들거나 행과 열로 구성되는 인수 그룹에 대해 연산이 이루어지는 한 개의 수식을 작성하기 위해 사용됩니다. 배열 범위는 공통 수식을 공유하며 배열 상수는 한 개의 인수로 사용되는 상수 그룹입니다.)이나 테이블 요소의 값을 반환합니다.

INDEX 함수의 첫 번째 인수가 배열 상수이면 배열형을 사용합니다.

INDEX(array,row_num,column_num)

array    배열 상수나 셀 범위입니다.

배열에 행이나 열이 하나만 있을 때는 row_num이나 column_num 인수를 생략할 수 있습니다.
배열에 행과 열이 두 개 이상 있을 때 row_num이나 column_num만 사용하면 배열의 전체 행이나 열이 하나의 배열로 반환됩니다.
row_num    값을 반환할 배열의 행을 선택합니다. row_num을 생략하면 column_num을 지정해야 합니다.

column_num    값을 반환할 배열의 열을 선택합니다. column_num을 생략하면 row_num을 지정해야 합니다.

주의

row_num과 column_num 인수를 모두 사용하면 row_num과 column_num이 교차하는 셀의 값이 반환됩니다.
row_num이나 column_num을 0으로 설정하면 전체 열이나 전체 행에 대한 값의 배열이 각각 반환됩니다. 배열로 반환된 값을 사용하려면 행에 대한 가로 셀 범위와 열에 대한 세로 셀 범위에 INDEX 함수를 배열 수식 (배열 수식: 하나 이상의 값 집합에 대해 여러 가지 계산을 수행하고 하나 또는 여러 개의 결과를 반환하는 수식입니다. 배열 수식은 중괄호 { } 안에 표시되고 Ctrl+Shift+Enter를 눌러 입력합니다.)으로 입력합니다. 배열 수식을 입력하려면 Ctrl+Shift+Enter를 누릅니다.
row_num과 column_num은 반드시 배열에 있는 셀이어야 합니다. 그렇지 않으면 #REF! 오류 값이 반환됩니다.

데이터 데이터
사과 레몬
바나나 배
수식 설명(결과)
=INDEX(A2:B3,2,2) 범위에서 두 번째 행과 두 번째 열이 교차하는 위치의 값(배)
=INDEX(A2:B3,2,1) 범위에서 두 번째 행과 첫 번째 열이 교차하는 위치의 값(바나나)

수식 설명(결과)
=INDEX({1,2;3,4},0,2) 배열 상수에서 첫 번째 행, 두 번째 열에 있는 값(2)
 배열 상수에서 두 번째 행, 두 번째 열에 있는 값(4)

참조형
특정 행과 열이 교차되는 위치의 셀 참조를 반환합니다. 참조가 인접하지 않은 영역으로 이루어진 경우에는 찾아볼 영역을 선택할 수 있습니다.

INDEX(reference,row_num,column_num,area_num)

reference    한 개 이상의 셀 범위에 대한 참조입니다.

인접하지 않은 범위를 참조로 입력하려면 참조를 괄호로 묶어야 합니다.
참조의 각 영역마다 행이나 열이 한 개만 들어 있는 경우에는 row_num 또는 column_num 인수가 각각 선택 사항이 됩니다. 예를 들어 한 개의 행 참조에 대해서는 INDEX(reference,,column_num)을 사용합니다.
row_num    참조 범위에서 참조를 반환할 행 번호입니다.

column_num    참조 범위에서 참조를 반환할 열 번호입니다.

area_num    row_num과 column_num이 교차하는 셀을 반환할 참조 범위를 선택합니다. 첫 번째로 선택하거나 입력한 영역의 번호는 1이 되고, 두 번째 영역의 번호는 2가 됩니다. area_num을 생략하면 영역 1이 사용됩니다.

예를 들어 reference가 (A1:B4,D1:E4,G1:H4) 셀을 나타내면 area_num 1은 A1:B4 범위를, area_num 2는 D1:E4 범위를, area_num 3은 G1:H4 범위가 됩니다.
주의

reference와 area_num으로 특정 범위가 선택된 후에는 row_num과 column_num이 특정 셀을 선택합니다. 즉, row_num 1은 범위의 첫째 행이고, column_num 1은 첫째 열의 방식으로 선택됩니다. INDEX 함수로 반환되는 참조는 row_num과 column_num이 교차되는 위치입니다.
row_num이나 column_num을 0으로 설정하면 전체 열이나 행에 대한 참조가 각각 반환됩니다.
row_num, column_num, area_num은 반드시 reference 안의 셀이어야 합니다. 그렇지 않으면 #REF! 오류 값이 반환됩니다. row_num과 column_num이 생략되면 area_num으로 지정된 참조의 영역이 반환됩니다.
INDEX 함수의 결과는 참조이며 다른 수식에 의해 참조로 해석됩니다. INDEX 함수의 반환 값은 수식에 따라 참조로 사용될 수도 있고 값으로 사용될 수도 있습니다. 예를 들어 수식 CELL("width",INDEX(A1:B2,1,2))는 CELL("width",B1)과 동일합니다. CELL 함수는 INDEX의 반환 값을 셀 참조로 사용합니다. 반면에 2*INDEX(A1:B2,1,2)와 같은 수식은 INDEX의 반환 값을 B1 셀의 숫자로 인식합니다.

과일 가격 개수
사과 0.69 40
바나나 0.34 38
레몬 0.55 15
오렌지 0.25 25
배 0.59 40
  
아몬드 2.80 10
캐슈넛 3.55 16
땅콩 1.25 20
호두 1.75 12
수식 설명(결과) 
 
=INDEX((A1:C6,A8:C11),2,2,2) A8:C11의 두 번째 영역에서 두 번째 행과 두 번째 열이 교차하는 위치, 즉 B9 셀의 내용(3.55) 
=SUM(INDEX(A1:C11,0,3,1)) A1:C11 범위에서 첫 번째 영역의 세 번째 열의 합, 즉 C1:C6의 합(216) 
=SUM(B2:INDEX(A2:C6,5,2)) B2에서 시작하여 A2:A6 범위의 다섯 번째 행과 두 번째 열이 교차하는 위치 사이의 범위의 합, 즉 B2:B6의 합(2.42)

52일차 index 관련 복습

몰입도 3

만족도 3.5

일차 / 날짜 : 058일차 / 7월 15일 (화)
취침 / 기상시간 : 오후 10시 40분/ 오전 5시 00분
수면/ 활동시간: 6시간 20분 / 오전 6시 30분~ 7시(어제 하루 돌아보기), 7시 20분 ~ 8시 40분 (엑셀) 

엑셀함수강의

sumproduct 

조건에 맞는 건수 구하기.
key- 논리연산자로 true, false 배열 생성 후 n 함수 이용
상반기건수
SUMPRODUCT(N(MONTH(일자)<7))

그룹별 순위 구하기
그룹에 따라 중량의 순위 구하기
key -> 동일 그룹을 true, false로 구분, 중량의 순위는 각 중량보다 큰 값을 true로 반환, 둘을 곱해서 각 그룹에서 자신보다 큰 값의
개수 파악, 순위니까 마지막에 1을 더하는 센스
SUMPRODUCT((그룹=A101)*(중량>B101))+1

offset

몰입도 3.5

만족도 3.5

중간에 사람들과 인사해서 흐름이 끊어 질 때가 있음.

석찬님 안뇽~:)

오랜만에 단군일지 복귀한 부족장 반야입니다.

석찬님의 일지를 보니, ㅋㅋ

대단! + 힘이 납니다.

그동안 꾸준히 묵묵히 열심히 해주셔서 넘 감사해요.

그 자체로 저에게 많은 힘이 되네요.

중반을 넘어가는데, 마지막 날까지 화이팅합시당당당!

저 좀 많이 도와주세욤!

대단하세요^___^!

이대로 쭉 직진입니다. 

석찬님 화이팅!

일차 / 날짜 : 059일차 / 7월 16일 (수)
취침 / 기상시간 : 오후 10시 40분/ 오전 5시 00분
수면/ 활동시간: 6시간 20분 / 오전 7시 40분 ~ 8시 40분 (엑셀) 

offset

offset과 row의 결합 이용!

기본강의 단축키

최근 찾기한 검색어로 다음 찾기  Shift+F4

최근 찾기한 검색어로 이전 찾기 Ctrl+Shift+F4

시트 삭제 Alt+E, L

행 숨기기, 열 숨기기 Ctrl+9, Ctrl+0

행 보기(숨긴 행 위, 아래 행까지 선택 후), 열 보기(숨긴 열 좌, 우 열까지 선택 후) Ctrl+Shift+9, Ctrl+Shift+0

창 복원/최대화(토글키) Ctrl+F10

바로 윗 셀 수식 복사 Ctrl+' (': 작은 따옴표) 

내가 무엇이 부족한지 파악하고 계속 개선해 나가는게 정말 중요! 무엇이 부족하고, 개선해야하는지를 알아야 그 부분에 집중을 할 수 있고, 집중을 해서 노력해야 결과로 이루어짐. 그리고 물을 끓이기 위해 마지막 1도를 올릴 마무리 노력.

 몰입도 3.5

 만족도 2.5  공부시간이 적음.

일차 / 날짜 : 060일차 / 7월 17일 (목)
취침 / 기상시간 : 오후 11시 40분/ 오전 5시 15분
수면/ 활동시간: 5시간 35분 / 오전 7시 30분 ~ 7시 50분, 8시 10분 ~ 8시 40분 (엑셀)

늦게 잔 이유, 숙면에 대해:

집에와서 밥 먹으니 박 먹기 전 딴 짓 조금. 아버지랑 이야기 조금. 라이나 생명 채용 뒤늦게 보고 확인 하다 늦게 잠. 역시 밥은 버스 안에서 빨리 먹어야 겠음. 그래야 집에와서 바로 재활 후 씻는게 편해짐. 아침에 속도 편하고.

엑셀

버스에서 단군일지로 엑셀 복습 (20분)

엑셀기본강의에서 텍스트나누기로 데이터 변환 복습 및 선택한 행들 숨기기

몰입도 2

만족도 1.5 

일차 / 날짜 : 061일차 / 7월 18일 (금)
취침 / 기상시간 : 오후 11시 40분/ 오전 5시 00분
수면/ 활동시간: 5시간 20분 / 활동 무,

늦게 잔 이유: 자소서, 자소서 쓴다고 4시 일어났다 다시자고, 5시 기상. 기상 후 느긋하게 출근 준비하느라 시간 보냄. 버스에서 엑셀 보다가 졸아 버림. 피로 누적이 원인인듯.

AIA생명 건물 이전으로 이사 준비 한창 한 날.

일차 / 날짜 : 062일차 / 7월 19일 (토)
취침 / 기상시간 : 오전 1시 20분/ 오전 5시 20분
수면/ 활동시간: 4시간 00분 / 오전 5시 50분 ~ 8시, 9시30분 ~10시 30분(엑셀), 오후 9시 45분 ~ 10시 15분(재활)

늦게 잔 이유: 자소서 집에와서 늦게까지 씀, 미리 준비 안한게 원인. 복날이라 집에서 저녁 8시 넘어서 삼겹살 구워먹음.

오늘은 주중에 못한 엑셀 몰아치기! 엑셀 기본강의 완벽하게 보기! 5시간 초 집중으로 끝낸다! 끝내고 슬램덩크랑 영화 보고 자겠어.

우리 그거 해요 우리는 강하다. 슬램덩크 서태웅 말이 왜인지 떠오름.

엑셀기본강의

조건부서식<Alt+O,D>

시트 숨기기/보이기 Alt+O, H, H/U

자동 합계(숫자 값이 있는 셀 범위 근처의 빈 셀 선택 후) Alt+H, U, Enter

활성 셀을 한 화면 좌(우)측으로 이동 Alt+Page Up(Down)

피벗 테이블의 원본 데이터 선택 Alt+D+P+B (Alt 누른채, 연속으로 D,P,B를 누릅니다)

`고급 필터` Alt+A,Q 

표시형식

000 과 ### 의 차이 구분! 000은 0이 아닌 유효한 숫자가 입력시 그대로 표기 아니면 0으로 표기, ###은 0은 표기하지 않음.

그래서 끝자리는 #,##0 이런식으로 사용.

#,##0,천단위로 숫자 표시(끝에 쉼표가 2개면 백만단위)

시간표시 방법

경과시간을 알기위해서는 대괄호[] 입력

몰입도 3.5

만족도 2.5 , 주말 파워를 발휘하지 못했다. 더 많이 해야했는데 잠 못자서 너무 졸았다.

재활은

몰입도 3

만족도 3

일차 / 날짜 : 063일차 / 7월 20일 (일)
취침 / 기상시간 : 오후 10시 50분/ 오전 5시 25분
수면/ 활동시간: 6시간 35분 / 오전 5시 50분 ~ 7시 50분, 10시 40분 ~  12시, 3시 30분 ~ 4시 30분(엑셀)

늦게 잔 이유: 인터넷 딴짓, 늦은 재활운동

엑셀교재

피벗테이블

390 다중통합 범위의 피벗 테이블 다루기 => 여러 시트의 데이터를 하나의 피벗 테이블로 만드는 법, 보고서 시트도 구분이 되어 있고.

핵심은 art + d + p

391 피벗 테이블의 필드명과 항목명 변경하기, 다중 테이블의 경우 필드 레이블 표시가 되지 않기 때문. 그냥 이름 고치면 됨. 

399 값 참조해 보고서 요약하기- GETPIVOTDATA 함수, 핵심은 피벗에서 값(데이터)을 수식으로 사용 시 GETPIVOTDATA 수식이 나옴. GETPIVOTDATA 사용 여부는 홈 옵션 수식 에서!

410 피벗 테이블 선택하고 삭제 및 이동하기 , 핵심 => 피벗 테이블 빠르게 선택하는 법 , 옵션 =>동작=> 선택 명령단추=>선택 가능 이 선택되어 있을 때 아래 표의 위 모서리에 마우스 대면 아래를 향한 화살표 나옴. 그때 누르면 됨.

피벗 p4 시트

피벗 테이블에서 용어 구분 할 것 => 그래야 피벗에서 레이블로 계산 항목을 추가할 건지 레이블 값으로 계산항목을 추가할 건지를 확실히 구분 할 수 있음

피벗            원본데이터

레이블             필드명

레이블 값(항목) 필드값 

p7시트

원본 데이터의 날짜형식을 피벗을 해서 년 => 월 로 표현 하는 방법

다시말하면, 원본데이터는에서 2014-06-14를 레이블 값으로 피벗으로 가져 오는데 여기서 데이터를

2014년=> 하부로 6월 이렇게 표현하는 법. 날짜 레이블값 아무거나 하나 택하고 오른쪽 마우스 그룹화 에서 "년" 과 "월"을 둘다 택함.

중복

고급 필터로 중복 된 레코드 제거하는 법

고급 필터 단축키 다시 한번 art+a, q

§ 고유 행만 조회(머리글 필수)

`데이터` 탭-- `고급`-- `목록 범위`에 범위설정-- `동일한 레코드는 하나만`에 체크

§ 중복 행 제거(머리글 없어도 가능)

표 범위 선택-- `데이터` 탭-- `중복된 항목 제거`-- 중복 열 체크 

정렬

왼쪽에서 오른쪽으로 정렬(아래 정렬 창에서 `옵션`클릭) ,

이 설정은 계속 유지되므로 위에서 아래로 할 때는 다시 `옵션`을 눌러서 바꿔야 함을 주의하세요.

정렬할 범위 드래그 해 놓는 것 체크

함수 

NETWORKDAYS 함수
NETWORKDAYS(start_date, end_date, [holidays])

날짜 설명
2008-10-01 프로젝트 시작 날짜입니다.
2009-03-01 프로젝트 끝 날짜입니다.
2008-11-26 공휴일입니다.
2008-12-04 공휴일입니다.
2009-01-21 공휴일입니다.
수식 설명(결과)
=NETWORKDAYS(A2,A3) 위 시작 날짜와 끝 날짜 사이의 작업 일수(108)
=NETWORKDAYS(A2,A3,A4) 첫째 공휴일을 제외한 시작 날짜와 끝 날짜 사이의 작업 일수(107)
=NETWORKDAYS(A2,A3,A4:A6) 위의 모든 공휴일을 제외한 시작 날짜와 끝 날짜 사이의 작업 일수(105)

여러개의 레코드를 하나의 셀에 붙여 넣는 함수 => phonetic,

몰입도 3.5

만족도 3 주말치고는 많이 못했다.  집중도가 떨어짐. 잠을 자야 함.

일차 / 날짜 : 064일차 / 7월 21일 (월)
취침 / 기상시간 : 오후 10시 15분/ 오전 5시 15분
수면/ 활동시간: 7시간 00분/ 5시 45분 ~ 6시 50분

고급필터 복습 , 교재

367

조건을 줄 때 AND와 OR의 차이, 같은 행의 조건은 AND, 다른행 조건은 OR

수식으로 조건을 줄 때는 데이터베이스의 필드명과 다르게 입력하거나 생략!

368 고급 필터한 결과 다른 시트에 복사,

고급필터에서 다른 장소에 복사 클릭 후 이용!

370 원하는 결과 필드만 추출하기,

 고급필터 결과를 복사할 장소에 추출할 필드명만을 입력(복사 붙여넣기), 복사할 장소를 입력한 필드명을 지정 하면 OK

371 특정요일에 주문한 제품만 추출

 필터 조건에 수식을 줄 경우 필드명은 데이터 베이스와는 다르게! 그리고 수식을 주는 경우는 데이터베이스의 첫 행만 줄 것!

373 텍스트가 모두 일치하는 데이터만 추출하기

핵심은 '= 모이스처 로션  , 수식일 경우에만 조건에 입력하는 필드명이 데이터베이스 필드명과 다름. 이것을 기억. 텍스트 일치 경우는 데이터베이스와 필드명이 같아야 쓸 수 있음.

몰입도 3

만족도 3

일차 / 날짜 : 065일차 / 7월 22일 (화)
취침 / 기상시간 : 오후 12시 00분/ 오전 5시 00분
수면/ 활동시간: 5시간 00분/ 7시 15분 ~ 8시 45분

늦게 잔 이유: 더위, 모기, 밤에 컴퓨터 켜서 약간의 딴 짓.

엑셀교재

피벗테이블

392 분석한 데이터  목록 추출하기,  핵심은 피벗을 한 결과에서 해당 결과(예를 들면 dac에서 오차예상건 에 해당되는 애들의 데이터를 따로 보고 싶다)를 표로 보고 싶을 때 사용. 방법은 해당 데이터 값이 들어있는 곳을 더블클릭 or 오른쪽 마우스=> 자세한 정보 표시

393 보고서에 필요한 항목만 표시하기. 디자인이 테이블 형식이 아니라 압축형식일 때는 필드선택에서 필드명 선택후 선택

395 보고서 필터 페이지 표시 하기, 보고서 필터를 각 시트에 피벗 테이블로 표시하는 법. 옵션 => 옵션 => 보고서 필터 페이지 표시

396 피벗테이블에 빈행 삽입.  

몰입도 3.5

만족도 3.5

일차 / 날짜 : 066일차 / 7월 23일 (수)
취침 / 기상시간 : 오후 10시 00분/ 오전 5시 00분
수면/ 활동시간: 7시간 00분/ 7시 35분 ~ 8시 45분

함수강의 중

FREQUENCY 함수
값의 범위 내에서 해당 값의 발생 빈도를 계산하여 세로 배열 형태로 반환합니다. 예를 들어 FREQUENCY 함수를 사용하여 지정한 점수 범위에 들어가는 시험 성적의 개수를 구할 수 있습니다. FREQUENCY는 배열을 반환하므로 배열 수식으로 입력해야 합니다.

구문

FREQUENCY(data_array,bins_array)

data_array    빈도를 계산할 값 집합의 참조 또는 배열입니다. data_array에 값이 없으면 FREQUENCY 함수는 0의 배열을 반환합니다.

bins_array    data_array에서 값을 분류할 간격의 참조 또는 배열입니다. bins_array에 값이 없으면 FREQUENCY 함수는 data_array에 있는 요소 개수를 반환합니다.

bins_array에서  {5, 10} 배열을 준다면 분류하는 범위는 (1) 5이하 (2) 5초과 10이하, (3) 10 초과 로 나뉨

주의

분포도를 나타낼 인접 셀 범위를 선택한 후, FREQUENCY를 배열 수식으로 입력합니다.
반환된 배열의 요소 개수가 bins_array의 요소 개수보다 하나 더 많습니다. 반환된 배열에 추가된 요소는 최대 구간을 초과하는 값의 개수를 반환합니다. 예를 들어 세 개의 셀에 입력된 세 개의 값 범위(구간)를 계산할 때는 FREQUENCY를 네 개의 셀에 입력합니다. 추가 셀은 세 번째 구간값보다 큰 data_array의 값 개수를 반환합니다.
FREQUENCY 함수에서 빈 셀과 텍스트는 무시됩니다.
배열을 반환하는 수식은 배열 수식으로 입력해야 합니다.

이 예제에서는 모든 시험 점수가 정수라고 가정합니다.

예제를 빈 워크시트에 복사하면 더 쉽게 이해할 수 있습니다.
점수 구간
79 70
85 79
78 89
85 
50 
81 
95 
88 
97 
수식 설명(결과)
=FREQUENCY(A2:A10,B2:B4) 70보다 작거나 같은 점수 개수(1)
 구간 71-79에 있는 점수 개수(2)
 구간 80-89에 있는 점수 개수(4)
 90보다 크거나 같은 점수 개수(2)

예제의 수식은 배열 수식으로 입력해야 합니다. 예제를 빈 워크시트에 복사한 후 A12:A15 범위를 선택하고 F2 키를 누른 다음 Ctrl+Shift+Enter를 누릅니다. 이 수식을 배열 수식으로 입력하지 않으면 A12 셀의 결과(1) 하나만 반환됩니다.

Hlookup 복습

hlookup 의 유용성, 100점 단위 점수를 학점으로 환산 하는 문제를 푼다고 할 때 중첩 if문을 쓰지 않고 hlookup을 이용하여 간단히 점수별 구간을 나눌 수 있음. 여기서 주의 점은 hlookup에서 사용하는 구간 점수가 오름차순으로 정리 되어있어야 한다.

남 여 에 따라 점수를 나누는 기준이 다르다면, hlookup의 구간 설정을 if문으로 넣으면 편함.

HLOOKUP($B10,IF($A10="남",$F$9:$K$11,$F$10:$K$11), 4- MATCH($A10,$F$9:$F$11,0)) 

몰입도 3 

만족도 2.5, 바로 엑셀 들어가지 못해 시간 확보 못함.

일차 / 날짜 : 067일차 / 7월 24일 (목)
취침 / 기상시간 : 오후 11시 00분/ 오전 5시 00분
수면/ 활동시간: 6시간 00분/ 7시 15분 ~ 8시 45분

늦게 잔 이유: 아버지 생신이라 편지 쓴다고 하다 어영부영 시간을 보내버림.

엑셀 기본강의

데이터 통합

[통합] `데이터` 탭-- 통합<Alt+D,N>: 원본표의 셀 값, 표시형식이 복사되어 대상표에 집계, 대상표 필드명과 원본표 필드명 일치해야함

http://cafe.naver.com/xlwhy/555

주의 점

 - 하나의 열을 기준으로 통합 할 때는, 통합 할 곳에 필드명을 입력 후 필드명 아래 빈셀에 셀포인터를 옮긴 후 데이터 통합(art d n)

   이때 참조할 범위는 원본데이터의 필드명을 제외한 데이터 값들을 선택

 - 열 기준이 두개 이상 일때는? 두개의 열을 하나의 셀로 합친 후 통합 사용 후 텍스트 나누기 이용!

   예로 두가지 열이 하나는 모델(A,B) 하나는 버젼(0.1, 0.2)면 세미콜론을 이용해서 두개열으 합쳐서 표현. A;0.1  B;0.1 이런식으로!

 - 가져오는 필드의 순서를 바꿀 수 있음. 그럴 때는 바꿀 필드명과 행의 이름을 입력 후(표에서 데이터 값을 뺀 나머지 입력) 데이터 통합 전 드래그 범위를 필드명과 행의 이름이 들어간 표 전체를 선택. 데이터 통합에서 참조범위도 필드명이 다들어간 표 범위 선택.

엑셀장인 카페 들어가서 확인 해 볼 것.

여러 열에 값들을 아래 한행까지 영역잡아 병합하기(엑셀장인 카페)

필드명을 2레코드씩 병합해서 만들기 . 필드명이 a  b  c 이렇게 한 셀에 들어있는걸 2행씩 병합 하는 빠른 방법

핵심은 선택하여붙여넣기의 행열 바꿈과 전체병합(홈에서 병합하고 가운데 맞춤 옆에 역삼각형 눌러서, 단축키는 art h m a

N(value)

N 함수 구문에는 다음과 같은 인수 (인수: 동작, 이벤트, 메서드, 속성, 함수 또는 프로시저에 정보를 제공하는 값입니다.)가 사용됩니다.

value    필수 요소입니다. 변환할 값입니다. N 함수를 사용하여 변환되는 값은 다음과 같습니다.
값 또는 참조 결과
숫자 같은 숫자
Microsoft Excel에서 사용할 수 있는 기본 제공 날짜 형식 중 하나를 사용하는 날짜 해당 날짜의 일련 번호
TRUE 1
FALSE 0
오류 값(예: #DIV/0!) 오류 값
기타 0
비고

• 일반적으로 Excel에서는 필요에 따라 값이 자동으로 변환되므로 수식에서 N 함수를 사용할 필요가 없습니다. 이 함수는 다른 스프레드시트 프로그램과의 호환을 위해 제공됩니다.
• Excel에서는 날짜를 계산에 사용할 수 있도록 일련 번호 형식으로 저장합니다. 기본적으로 1900년 1월 1일이 일련 번호 1이고, 2008년 1월 1일은 1900년 1월 1일에서 39,448일째 날이므로 일련 번호 39448이 됩니다. Macintosh용 Microsoft Excel에서는 기본적으로 다른 날짜 체계가 사용됩니다.

몰입도 3.5

만족도 3 , 알면 바로 되는데 모르는 걸 찾느라 시간이 걸렸음 

일차 / 날짜 : 068일차 / 7월 25일 (금)
취침 / 기상시간 : 오후 11시 15분/ 오전 5시 10분
수면/ 활동시간: 5시간 55분/ 7시 20분 ~ 8시 40분

숙면에 대해: 반바지 입고 자서 추웠음. 집에서 저녁먹어서 잘 때 속이 편치 않음. 6시 칼퇴 일때 빼고는 계란 싸와서 버스에서 먹는게 좋을 듯. 삼각김밥이랑.

엑셀기본

데이터의 통합 기능 보충

데이터 통합에서  "사용한 레이블"  에 행이 체크 되어 있다면 통합할 위치에 가져올 레이블 명을 똑같이 입력해놔야 됨.

그리고 열 레이블의 범위 설정은 실제 통합 되는 레코드 수 만 넘게 지정해 놓으면 데이터를 가져오는데 문제 없음.

통합된 데이터를 가져오는 열 보다 적게 드래그 해 놓으면 드래그 된 부분까지만 데이터를 가져오게됨.

통합된 데이터를 가져오는 정확한 열수를 맞추기 위해 왼쪽 기준열을 먼저 가져오면 통합된 데이터에 맞게 레코드 수 지정 가능.

왼쪽열은 피벗으로 돌리면 나오긴하는데 더 간단한 방법을 생각해야 할 듯.

데이터 통합에서 여러 표를 한번에 통합하는 부분까지 봐볼 것!

ctrl + enter 부분 복습

표 구성 바꾸기.

사번

성명     으로 구성 된 표를  =>   사번 성명     으로 표구성을 바꿀 것.

핵심은 이동(빈셀 선택, 문자선택, 숫자 선택), 셀병합 해제 처리, 연속 되지 않은 셀을 복사 후 붙여 넣을 때는 붙여넣는 값들이 연속적으로 붙는 것.

몰입도 2.5 (계리중급과정 교재가 앞에 놓여서 계속 그것보다 엑셀보다 함. 엑셀 공부계획이 막 생각남. 엑세스랑 DB를 언제 들어갈 수 있을까 하고)

만족도 3

일차 / 날짜 : 069일차 / 7월 26일 (토)
취침 / 기상시간 : 오후 10시 45분/ 오전 5시 25분
수면/ 활동시간: 6시간 40분/ 6시 00분 ~ 7시 00분

엑셀기본강의

부분합

[부분합] `데이터` 탭-- 부분합<Alt+D,B>: 표에 특정 기준 필드의 항목별로 특정 항목의 소계를 작성

단축키

일반 형식 즉, 셀 서식의 표시형식이 없음

Ctrl+Shift+`  (`: <Tab>키 위에 있는 억음 악센트 기호) 키 위치 순서대로 생각하면 편함.

글자크기 Ctrl+Shift+P 또는 Ctrl+Shift+F

몰입도 2

만족도 2 컴퓨터 딴짓으로 잠깐 새다가 7시에 아침 먹으면서 흐름 놓침. 시간을 픽스하고 변경연 싸이트외의 다른 싸이트는 키지 않도록 할것. 엑셀 장인 카페 들어갔다가 딴짓했는데, 장인카페는 시간 다 하고 마지막에 들어가서 확인할 것!

일차 / 날짜 : 070일차 / 7월 27일 (일)
취침 / 기상시간 : 오전 12시 45분/ 오전 5시 25분
수면/ 활동시간: 4시간 40분/ 6시 00분 ~ 9시 00분

숙면에 대해: 단군의 후예하고 만나는 약속 다 포기 했는데 어제가 두 번째 술마시고 늦게 들어간 날. 그래도 만나고 싶었던 친구들이라 즐거움을 따랐고 좋았음. 안주를 너무 많이 먹어서 더부룩하다. 일어나는건 편하게 일어나지네. 신기.

정렬 복습

한 열 정렬방법: 해당 열의 아무 셀 선택-> `팝업 키`(Shift+F10)-> 정렬(O)-> S나 O

왼쪽에서 오른쪽으로 정렬(아래 정렬 창에서 `옵션`클릭)

부분합

교재 378~382

부분합의 선결조건: 그룹화할 항목(필드)에 대해 정렬되어 있어야 함. 표가 아닌 일반 데이터 범위 상태에서 부분합을 실행해야 함.

378 핵심은 윤곽선을 눌러서 부분합의 요약상태를 확인하는 것

379 이중부분합으로 분기별 매출 집계하기, 피벗 했을 때 열레이블에 분기 백화점 순으로 합계 나타내는 것을 부분합으로 표현하는 방식

유의사항은 두가지 이상 부분합 할 때 부분합 할 필드 순으로 오름차순 되어있어야하고, 두 번째 부분합 할 때는 새로운 값으로 대치 언체크. 두가지 필드의 부분합 표시는 그룹화할 항목에서 구분 하고 부분합 계산 항목은 합계 낼 항목을 체크. 

381 조건부 서식 이용, [조건부서식] `홈`탭-- 조건부 서식 <Alt+O,D>, `조건부 서식` 개수는 무제한(컴퓨터 메모리 용량)

소계에만 색깔 칠하기 =FIND("소계",$A4)>0 이런 형식 기억!

표시형식 

교재 159

공백과 반복문자 표시형식

언더바(_)는 다음에 오는 문자의 너비만큼 공백으로 처리하는 기호

애스테리스크(*) 기호는 통화 기호나 단위문자와 같이 숫자와 함께 쓰이는 기호의 경우 문자의 위치를 고정시키기 위해 많이 사용

₩*-#,##0 이 서식은 애스테리스크(*) 기호 뒤의 - 기호를  ₩와 숫자 사이를 반복해서 채운다.

#,##0_↓;[빨강]-#,##0"↓" 이 서식은 ↓의 너비만큼 공백을 뒤에 줌. _ 뒤의 문자너비 만큼 공백을 주는데 음수일 때 ↓ 기호가 들어갔을 때와 양수일때의 뒤 공백을 맞추려고하는 것. 추가로 기억할 것은 특수기호는 자음 다음 한자키

[파랑]▲* #,##0 ;[빨강]▼* -#,##0 ; -  색깔 넣는 것도 참고

몰입도 4

만족도 3.5

우왕좌왕했다. 뭘볼지 교재를 어떻게 볼지.

일차 / 날짜 : 071일차 / 7월 28일 (월)
취침 / 기상시간 : 오후 10시 40분/ 오전 5시 00분
수면/ 활동시간: 6시간 20분/ 7시 15분 ~ 8시 50분

숙면에 대해: 창문을 열고 자야 환기가 좀 되서 코가 안 막힘.

조건부서식

[조건부서식] `홈`탭-- 조건부 서식 <Alt+O,D>, `조건부 서식` 개수는 무제한(컴퓨터 메모리 용량)

셀 서식은 고정이지만 이 서식은 조건에 맞을 때만 지정한 서식이 적용됨.

이 서식은 다른시트를 참조하지 못하지만 `이름정의`를 사용하면 가능

이 서식을 행전체에 적용하려면 필히 수식을 사용해야 함

서식 규칙 범위에서 규칙을 보려는 범위(선택범위(드래그)인지 어느 시트인지)를 택할 수 있음.

다음을 포함하는 셀만 서식 지정은 해당 셀만 서식이 지정 됨. 수식으로 조건을 줄 때는 셀서식이 지정되는 범위를 적용 대상에서 정할 수 있음.

시트에 모든 조건부서식을 지우려면 조건부서식-> 규칙 지우기-> `시트전체에서 규칙지우기` 

필터

`사용자 지정필터` 조건은 2개까지만 가능

조건이 3개 이상 일때는?

조건 두개를 함수로 엮어서 필드 하나를 생성!

년 별로 나눌 때는? 옵션 고급에서 날짜 그룹화 체크

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

ADDRESS 함수 구문에는 다음과 같은 인수가 사용됩니다.

• row_num    셀 참조에 사용할 행 번호를 지정하는 숫자 값으로서 필수 항목입니다.
• column_num    셀 참조에 사용할 열 번호를 지정하는 숫자 값으로서 필수 항목입니다.
• abs_num    반환할 참조 유형을 지정하는 숫자 값으로서 선택 항목입니다.

• A1    A1 또는 R1C1 참조 스타일을 지정하는 논리값으로서 선택 항목입니다. A1 스타일에서는 열 레이블에 영문자를 사용하고 행 레이블에 숫자를 사용합니다. R1C1 참조 스타일에서는 열과 행 모두의 레이블에 숫자를 사용합니다. A1 인수가 TRUE이거나 이 인수를 생략하면 ADDRESS 함수는 A1 스타일의 참조를 반환합니다. 이 인수가 FALSE이면 ADDRESS 함수는 R1C1 스타일의 참조를 반환합니다.

 참고   Excel에 사용되는 참조 스타일을 변경하려면 Microsoft Office 단추 를 클릭하고 Excel 옵션을 클릭한 다음 수식을 클릭합니다. 수식 작업 아래에서 R1C1 참조 스타일 확인란을 선택하거나 선택 취소합니다.

• sheet_text    외부 참조로 사용할 워크시트의 이름을 지정하는 텍스트 값으로서 선택 항목입니다. 예를 들어 수식 =ADDRESS(1,1,,,"Sheet2")는 Sheet2!$A$1을 반환합니다. sheet_text 인수를 생략하면 시트 이름이 사용되지 않고 함수를 통해 반환되는 주소는 현재 시트의 셀을 가리킵니다

몰입도 4

만족도 3.5

일차 / 날짜 : 072일차 / 7월 29일 (화)
취침 / 기상시간 : 오후 11시 30분/ 오전 5시 00분
수면/ 활동시간: 5시간 30분/ 7시 10분 ~ 8시 50분

늦게 잔 이유: 메트라이프 이력서 쓰다가. 취업정보 알아보는 것 계속 미스나고 있다. 아침 버스에서 체크 할 것.

찾기_바꾸기

셀에 한 줄 띄운 문자를 찾으려면 NumLock키를 누른 뒤, 찾을 내용 란에 <Alt>를 누른 채, 숫자 키패드에 1, 0을 연속으로 누름.

찾기에서 모두 찾기 기능! shift로 다 누른 후 닫으면 찾으려는 셀을 모두 선택할 수 있음. 간단한 기능이지만 반복 작업을 많이 줄일 수 있음

980 운전면허 갱신    2014.05.23     이 셀값을 4자리 공백과 뒤의 날짜를 없애려면?

핵심 1. 사이의 빈칸이 스페이스바 입력이 아닌 경우도 고려! 빈칸을 직접 복사해서 찾기 바꾸기에 붙여 넣는다.

핵심 2. 와일드카드 문자를 쓸 때 바꾸려는 부분의 시작부분을 먼저 찾기 바꾸기에 입력한다.

시행착오로 찾기에 나는 "*    *    "를 입력했다. 하지만 바꿀내용은 공백 부터 이기 때문에 "    *" 이렇게 입력하면 바꿀내용을 찾을 수 있다.

함수

indirect

3줄씩 평균 구하기. 핵심은 indirect와 row의 결합

=AVERAGE(INDIRECT("A"&ROW()*3-2&" : A"&ROW()*3))

adress 잠시

몰입도 3.5

만족도 3.5

일차 / 날짜 : 073일차 / 7월 30일 (수)
취침 / 기상시간 : 오후 11시 30분/ 오전 5시 25분
수면/ 활동시간: 5시간 55분/ 8시 00분 ~ 8시 50분

늦게 잔 이유: 라이나 생명 이력서 쓸 거 고민 좀 하다 밍기적, 단군일지 쓰다가 시간 좀 보냄.

표시형식

예제 161 *기호로 채움선 표시해 목차 만들기

핵심은 표시형식에서 기존 데이터를 유지하려면 기호를 @를 써야 함(숫자는 G/표준 , 잊어버리지 말자)

반복하기 위한 기호는 *, 반복할 문자 앞에 위치!

목차 다음 * 로 채우려면  @** 로 표시형식 만들기. @는 기존 입력된 내용 받고, ** 는 뒤의 *를 반복

※ 표시형식은 기본적으로 양수;음수;0;문자

텍스트형 숫자를 진정한 숫자로 바꾸는 확실한 방법은 `텍스트나누기`

몰입도 3.5

만족도 2

일차 / 날짜 : 074일차 / 7월 31일 (목)
취침 / 기상시간 : 오후 11시 15분/ 오전 5시 00분
수면/ 활동시간: 5시간 45분/ 8시 20분 ~ 8시 50분

함수

다중조건 배열식 복습 및 응용

=LOOKUP(1,1/(($E$2:$E$13=I2)*($F$2:$F$13=J2)),$G$2:$G$13)

핵심은 두가지 조건을 lookup에서 배열을 줘서(1 아니면 에러 나게 해서) 1을 찾게하는 것

=INDEX(G2:G13,MATCH(1,INDEX((E2:E13=I8)*(F2:F13=J8),),0))

핵심은 index 안에서 논리 배열의 곱으로 1아니면 0의 배열을 생성하고 거기서 match로 1을 찾는 것, index 이중 사용

=LOOKUP(3,1/(E2:E13=I13)/(F2:F13=J13),G2:G13)

이 경우는 두가지 동일 조건에서 마지막 값을 가져오게 되는데 그 이유는 lookup 함수의 아래 특성 때문으로 추정(확인 필요)

다중 조건 배열 식의 응용

{ =INDEX(C2:C7,MATCH(1,(A2:A7=H2)*(D2:D7<=I2)*(E2:E7>=I2),0)) }

다중조건3 시트

문제는 일자가 5월 1일 인데 두 날짜(등록기간) 사이의 값 하나가 조건 하나. 자산번호 일치 여부가 조건 둘! 

이처럼 5월 1일이 해당 날짜 기간 사이에 들어가게 하려면 배열식 두가지 (D2:D7<=I2)*(E2:E7>=I2) 를 이용해서 만들어 내면 됨

몰입도 3.5

만족도 2  공부 시간이 적었음

일차 / 날짜 : 075일차 / 8월 1일 (금)
취침 / 기상시간 : 오전 12시 40분/ 오전 5시 20분
수면/ 활동시간: 4시간 40분/ 7시 50분 ~ 8시 50분

늦게 잔 이유: 라이나생명 자소서 쓰느라. 좀 더 자려고 알람을 늦게 맞춰놨는데 신기하게 그냥 일어나짐.

함수

indiret

간접접근 함수

문자열로 주소를 받아서 그 주소의 값을 리턴

'=INDIRECT(문자열 참조,[a1])

a1: 참조 유형을 지정하는 논리값, TRUE이거나 생략하면 A1 스타일, FALSE면 R1C1스타일

indirect 를 이용해서 다른 시트의 데이터를 조건에 맞게 가져오기

여기서 알아야 할 점은 다른 시트의 데이터를 가져올 때의 셀입력 형태를 기억해야 함

헷갈리면 형식을 '외부엑셀파일명or시트명'! 형태로 기억!

=SUMIFS(INDIRECT(C$6&"!$C:$C"), INDIRECT(C$6&"!$A:$A"), $A16, INDIRECT(C$6&"!$B:$B"),$B16)

참고)

외부 통합문서의 시트나 현재 통합문서의 다른시트의 셀 참조
한글, 영어, 밑줄(_) 이외의 문자(공백포함)가 들어 있으면 이름을 작은따옴표(')로 묶어야 합니다.
※ 시트명의 일부에 영어 알파벳 R 또는 C가 있으면 '로 묶어야 함 (가령, 'r1_2'), 셀주소도 마찬가지
또한 시트명의 시작글자가 숫자일 때도 마찬가지임
=[deco_pg.xlsm]재고!$A$7
='[유니크 항목 개수.xls]Sheet1'!$A$2
='(주)서울상회'!A6

 3행단위 평균 구하기

핵심은 indirect와 row()의 결합

=AVERAGE(INDIRECT("A"&ROW()*3-2 &":"&"A"&ROW()*3))

찾는 단어의 제일 마지막 위치의 셀주소 알아내기

핵심은 if 구문에서 배열 수식을 만들어 내는 것. 다시 볼 것

'="A"&MAX(IF(ISERR(FIND("엑셀",A1:A4)),,ROW(A1:A4)))

몰입도 4

만족도 3.5 공부한게 기초가 쌓이는 느낌이 듬

일차 / 날짜 : 076일차 / 8월 2일 (토)
취침 / 기상시간 : 오후 11시 00분/ 오전 5시 25분
수면/ 활동시간: 6시간 25분/ 6시 00분 ~ 8시 35분, 오후 2시 20분 ~ 5시

 indirect

2013-5-5에서 2013-5-31사이의 일요일 개수 구하기

1. 문제를 간단히 일자를 2013-5-5 부터 2013-5-31 까지 F3부터 F29까지 입력 후

=SUMPRODUCT(N(WEEKDAY(F3:F29)=1)) 입력하면 정답 4개 나옴

2. 이제 WEEKDAY(F3:F29)=1 이 부분을 indirect 로 간단히 만드는 것이 관건

3. 시행착오

 =SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT("$B3:$B4"))=1))) 

시행착오 중 안 사실 WEEKDAY(2=3) = WEEKDAY(FALSE) = 7, WEEKDAY 안의 FALSE 논리값이 나오면 7이 나온다.

의도는 2013-5-5 부터 2013-5-31 까지 끌어오기 위해 INDIRECT("$B3:$B4")을 날짜로 받아와서 ROW 범위를 끌어와서

2013-5-5 부터 2013-5-31까지의 배열을 만들려고 했음. 결과는 14가 나옴. 왜냐하면 INDIRECT로 끌고 온건 B3:B4 이고

ROW로 받으면 {3,4} 배열생성 WEEKDAY{3,4} =WEEKDAY{FALSE,FALSE} 가 나와 버려서 7+7 이 되어 버림.

ROW와 INDIRECT를 제대로 몰랐다는 것. 항상 정의가 중요하다.

indirect 정의

간접접근 함수

문자열로 주소를 받아서 그 주소의 값을 리턴

=AVERAGE("A1"&":"&"A3") 이건 계산이 안됨. 하지만 =AVERAGE(INDIRECT("A1"&":"&"A3")) 이건 계산이 됨

이게 INDIRECT의 기능임. 주소를 수식으로 꾸며서 리턴 할 수 있는 기능.

제대로 풀어서 나온 식!

=SUMPRODUCT(  N(WEEKDAY( ROW(INDIRECT("A"&B3&": A"&B4) )) =1 ) )

참고로 B3에는 2013-5-5 값이 B4에는 2013-5-31 값이 입력되어 있음.

여기서 핵심은 ROW와 indirect와의 결합인데 이 결합을 통해 2013-5-5 부터 2013-5-31까지의 날짜(정확히는 일반형식의 숫자)를 배열로 표현 할 수 있었음. 여기서 row의 기능이 row(A1:A5) 면 {1, ~,5}의 배열 생성이 가능(CSE 또는 배열함수랑 같이 쓸때).

또 WEEKDAY 함수에 인수값에 날짜 형식이 아닌 숫자가 들어가도 값을 반환한다는 사실!

막판에 논리값을 숫자로 바꿔주는 N함수를 써주는 센스!

시간은 많이 걸렸는데 그래도 하나 혼자 했네.

 여러시트의 특정범위 합

=SUM('10-1:10-3'!A1:A4)

1. =sum( 하고 10-1 시트 선택 후 shift 누른 상태에서 10-3 시트 클릭, 후 a1~a4 드래그, 엔터)

표 만들기

http://cafe.naver.com/xlwhy/14719

http://cafe.naver.com/xlwhy/14719

몰입도 3

만족도 3.5

일차 / 날짜 : 077일차 / 8월 3일 (일)
취침 / 기상시간 : 오후 10시 20분/ 오전 5시 25분
수면/ 활동시간 : 7시간 5분/ 6시 20분 ~ 9시 30분

엑셀 표만들기 (현장관리 입력표)

http://cafe.naver.com/xlwhy/14719

1. 테두리 그릴 때는 셀병합을 해서. 그리고 나중에 병합을 푼다. 그래야 테두리 그리기 쉽다. 테두리 그리기 빠른도구모음에 추가.

2. 테두리를 복사해서 붙여 넣으면 엑셀 용량이 커지지만 그냥 셀 서식으로 행 끝까지 지정하는 경우는 그렇지 않음

3. 피벗 등을 할 때 데이터 선택을 ctrl+a로 한번에 선택하기 위해 불필요한 데이터(예로 상위 그룹명 등) 아래로 빈행 삽입 후 숨기기.

4. 필터 걸 때 필드명이 잘 보이도록 행 높이 넉넉하게. 그리고 글자 위치는 셀 위쪽으로.

5. 필드별 합계 등은 표 위에 표시. 그래야 잘 보이니까

6. 필드별 합계 등을 입력할 때 먼저 해당 필드 셀 아무거나 하나 선택해서 붙여넣기. 그래야 해당 필드의 서식까지 복사하기 쉬움.

엑셀 기초] 2엑셀 표만들기 (인구 입력표)

일단 표를 구성하면서 느낀점은

어떻게 구성할지 생각하고 들어가야 한다는 점! 이게 정말 중요하다. 무작정 달려 들었다가 한 번에 끝날 일을 두 번 세번하게 만든다.

수식 등을 여러 레코드에 적용할 때 필드 하나씩 적용하지 말구, 모든 필드의 수식 서식을 지정한 다음에 모든 필드를 한번에 끌어야 편하다.

엑셀 장인 카페에서 누가 정렬 문제로 올린 검증 파일  풀어 달라고 해서 시간 썻다. 푸는데는 몇분 안걸렸는데 글 쓰는게 오래 걸린 듯. 글 올린 사람은 그 문제 때문에 직장에서 갈굼당하고 밥먹은 것도 다 토할 정도로 스트레스 받았다고. 처음 알바할 때 내 처지랑 비슷했었구나 라는 생각도 들고, 한편으로는 정말 간단한 문제인데 적극적으로 알아보지 않으려 했구나 라는 생각도 들고.

몰입도 4

만족도 3, 좀 더 해야지 밥먹고

일차 / 날짜 : 078일차 / 8월 4일 (월)
취침 / 기상시간 : 오후 11시 00분/ 오전 5시 00분
수면/ 활동시간: 6시간 00분/ 7시 20분 ~ 8시 45분

기본강의 총 복습

[불연속 셀 선택]
불연속 셀 선택 후, <Enter> 치면 각 셀로 활성 셀이 이동함.

[병합]

표를 만들 때 병합으로 만들고 선그려야 선긋기가 쉬워짐 선 그은 후, 병합 해제

[데이터 유효성] `데이터` 탭-- 데이터 유효성검사 <Alt+D,L>

차트 부분이 공부 미흡. 아직 흥미가 없어서인 듯.

몰입도 3

만족도 2.5 새로운게 없었음. 이제 매크로 시작

일차 / 날짜 : 079일차 / 8월 5일 (화)
취침 / 기상시간 : 오후 11시 00분/ 오전 5시 00분
수면/ 활동시간: 6시간 00분/ 7시 20분 ~ 8시 45분

vba 시작

첫 vba 코드

Sub 공백문자_지우기()   'B,F열에 공백문자 제거
Sheets(Array("1", "2", "3", "4", "5")).Select
Range("B3:B68,F3:F68").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart
Sheets("1").Activate
End Sub

수식을 이용해서 결재 방법수 생각

=SUMPRODUCT(1/COUNTIF($A$2:$A$5000,$A$2:$A$5000))

핵심은 1/COUNTIF($A$2:$A$5000,$A$2:$A$5000) 이 부분!

아래 3개 레코드와 같다면 계산은 1/2 +1/1+ 1/2=3.   같은 애들을 카운트 해서 분수로 만들고 같은 애들을 더하니까 종류별로 1이 생성.

해외결제 페이팔결제
계좌이체
해외결제 페이팔결제

느낀점은 응용해보는 시간을 많이 가져야한다는 점. 아직 기본, 함수의 내용은 파악했지만 응용해보는 시간이 적었음.

우노 싸이트나 장인카페를 이용해서 문제를 풀어볼 필요 있음.

매크로의 쓰임새, 매크로 기록.

몰입도 3.5

만족도 3. 첫 술에 배부르랴. 

석찬님의 이 새벽습관으로 빛날 것이란 믿음.^^

일차 / 날짜 : 080일차 / 8월 6일 (수)
취침 / 기상시간 : 오후 10시 30분/ 오전 5시 00분
수면/ 활동시간: 6시간 30분/ 7시 25분 ~ 8시 45분

매크로 기록, 기록중단 실행, 수정, 삭제, 단추 삽입으로 매크로 실행

vbe 실행 창 art + F11

책이 구어체라 빨리 안 읽힌다.

몰입도 3

만족도 2.5

일차 / 날짜 : 081일차 / 8월 7일 (목)
취침 / 기상시간 : 오후 10시 20분/ 오전 5시 5분
수면/ 활동시간: 6시간 45분/ 7시 20분 ~ 8시 45분

vba

프로젝트 탐색기(ctrl+r)- 엑셀 파일들(프로젝트)과 그 하부의 개체들에 대한 계층도를 표시

모듈 종류, 모듈 추가, 삭제, 복사 방법

속성창(F4)- 개체애 대한 상태나 속성을 바꿀 때 사용, 폼 모듈에서 많이 사용, 이반 모듈에서는 주로 모듈의 이름을 바꿀 때 사용

모듈이름에 공백 문자 사용 불가. 영어, 한글, _ 사용

코드창(F7)- 실제 코딩이 이뤄지는 메모지,

변수명과 매크로 명 짓는 요령(62P)

1.영문만으로 해서 그 자료형의 접두어를 먼저 소문자로 입력, 내용이 되는 단어는 대문자로 시작 _ 다음에 붙이는 것

2. 한글과 영문을 섞어서.

몰입도 3

만족도 2.5 기본 설명에 모든 팁 들이 다 있어서 너무 양이 많다. 일단 속도를 내는게 중요하다.

일차 / 날짜 : 082일차 / 8월 8일 (금)
취침 / 기상시간 : 오후 11시 15분/ 오전 5시 5분
수면/ 활동시간: 5시간 50분/ 7시 30분 ~ 8시 45분

VBE 상단메뉴

되돌리기 단축키 변경하기

보기 -> 도구 -> 사용자 지정 활성화 한 상태에서 -> 되돌리기 아이콘 우클릭 -> 이름 을 &Z 로 수정 후 이미지 텍스트 표시 클릭

찾기 바꾸기

검색 -찾는 범위

현재 프로시저 : 커서가 있는 해당 매크로 안에서 찾는 것

현재 모듈: 현재 활성코드 창 전체가 대상

현재 프로젝트: 그 파일 전체 모듈에서 찾는 것

단어 단위로: 찾을 내용에 입력한 내용과 완전히 일치하는 것만

대/소문자 구분

패턴일치사용: 와일드 카드 문자(*,?)를 이용해서 찾는 것

들여쓰기, 내어쓰기

한 줄 단위 들여쓰기 할 때는 TAB(들여쓰기), SHIFT + TAB(내어쓰기)

블록을 잡아서 하면 덩어리째 들여쓰기, 내어쓰기 가능

탭 너비 조정은 도구 -> 옵션에서

smart indent -> 코드 정돈 시 유용.  후에 설치 할 것.

속성/메서드 목록:

상수 목록: 속성이나 메서드에 인수가 들어갈 수 있는데 그 인수 중에 상수(고정값) 목록을 보여줌.

몰입도 3

만족도 2.5 구어체의 책이 빨리 안 읽힌다. 기본 내용에 팁에 대한 내용이 너무 많다ㅠ 다른 책과 교차해가면서 봐야할 듯.

일차 / 날짜 : 083일차 / 8월 9일 (토)
취침 / 기상시간 : 오후 10시 45분/ 오전 5시 30분
수면/ 활동시간: 6시간 45분/ 6시 40분 ~ 8시 45분, 일어나서 쇼미더머니 유튜브로 봄. 바비, B.I, 스윙스.

71p

VBE 상단의 메뉴

파일

VBE 창 닫기 ART + Q, VBE창 토글키 ART+F11

편집메뉴

상수목록(CTRL + SHIFT +J): 속성이나 메서드에 인수가 들어갈 수 있는데 그 인수 중에 상수(고정값)의 목록을 보여주는 것

이 키의 유용성은 이미 코드가 완성된 후에 상수를 바꿀 때!

단어채우기(CTRL + SPACEBAR): 첫 글자만 입력 후 단축키 입력하면 단어 선택해서 쓸 수 있음.

여자친구가 만든 차트 따라 만들어 보기. 엑셀 장인 카페 컴활(?)관련 질문 답하기.

몰입도 3

만족도 2.5 토요일 집중력저하.