회사에서 교육점수 채우려고 공부하는 내용입니다.
1. 이것저것 함수
=trimmean(array, percent) : percent에 포함되는 위/아래 값을 제외하고 나머지 값으로 평균
- =trimmean(10개 array, 0.2) ==> 2(=100.2)개 제외(위1, 아래1)
- =trimmean(13개 array, 0.2) ==> 2(=13
0/2=2.6 ==> 위/아래로 나눠야 되므로 근접한 짝수개인 2)개 제외(위1, 아래1)
- =trimmean(5개 array, 2/5) ==> 5개중에서 상위1개, 하위1개를 제외하고 평균구함
=int(number) : 숫자를 정수로 변환하여 소수 부분 잘라버림
- =int(0.345) ==> 0
- =int(0.678) ==> 0
- =int(-1.1) ==> -2
- =int(-1) ==> -1
=if(logical_test, value_if_true, false_if_true) : 논리식
- =if(a1>70, “합격”, “불합격”)
- =if(a1>=90, “A”, if(a1>=80, “B”, “C”))
- =if(and(a1>=80, a2>=80, a3>=80), “합격”, “불합격”) : and, or 연산자 사용 가능
- 비교식에서 같다는 = 하나이다. (== 아니네)
=averagea(value1, [value2], …) : 공백이외의 데이타(문자열)도 평균계산에 포함, 즉 문자열은 0 값으로 인식됨
=rank(number, 범위, [0 또는 생략=내림차순, 1 또는 그외의값=오름차순]) : 특정값의 범위(절대참조로 지정하는게 복사할때 편하다)내의 순위를 구함
- tip : 합계를 통해 rank를 매길때 동점자 처리를 위해 특정항목에 가중치를 두고 싶으면, 가중치 항목에 0.01와 같은 값을 곱한후 합계에 더한값을 통해 rank 한다.
=left(텍스트, 갯수)
=right(텍스트, 갯수)
=mid(텍스트, 시작위치, 갯수) : 시작위치는 1부터 시작한다
=choose(index_num, value1, [value2]…) : index_num은 1,2,3,4.. 의 값을 갖는다, 1일때는 value1, 2일때는 value2…
- 값이 1,2,3,4 와 같을때 if문 대신 쉽게 사용할 수 있다.
- tip : 주민등록번호에서 남/여를 판별시 사용할 수 있다
ex) =CHOOSE(MID(F5,8,1), “남”, “여”,”남”, “여”)
- tip : 주민등록번호에서 만 나이를 구할 수 있다
ex) =DATEDIF(DATE(LEFT(F5,2), MID(F5,3,2), MID(F5,5,2)), today(), “Y”)
- tip : 월~목요일 배송일은 당일, 금~일요일은 다음주 월요일 배송
ex) =CHOOSE(WEEKDAY(B5,2), B5, B5, B5, B5, B5+3, B5+2, B5+1)
=count(value1, [value2]…) : 숫자가 입력된 셀의 갯수
=counta(value1, [value2]…) : 문자열셀도 포함하여 셀의 갯수
=countblank(value1, [value2]…) : 아무것도 입력되지 않은 셀의 갯수
=countif(범위, “조건”) : 조건을 만족하는 셀의 갯수
- ex) 신용카드인 셀의 갯수
=COUNTIF(E5:E12, “신용카드”)
- ex) 숫자값이 15이상인 셀의 조건
=COUNTIF(E5:E12, “>=15”)
- ex) 신용카드인 행의 결제금액
=SUMIF(E5:E12, “신용카드”, F5:F12)
- ex) 숫자값이 15이상인 셀의 합계
=SUMIF(E5:E12, “>=15”, F5:F12)
- ‘조건범위’와 ‘합계를 구할 범위’가 같다면 ‘합계를 구할 범위’는 생략 가능
- tip : countif 또는 sumif는 1개의 조건만 가능하다. 따라서 15이상이고 20이하 (15<x<20) 의 범위를 구하고 싶으면
=countif(범위, “>=15″) – countif(범위, “>20″)
- tip : 셀영역 이름을 지정해 놓은 후 사용하자 (다른 sheet에서도 사용가능하다)
=round(숫자, 표시할 자리수) : 반올림
=roundup(숫자, 표시할 자리수) : 올림
=rounddown(숫자, 표시할 자리수) : 버림
- 표시할 자리수 : 양수(소수점 이하 표시할 자리수), 0(정수가 됨), 음수(소수점 이상의 자리수.. 즉 0 으로 채움)
=isblank(value) : 빈칸이면 #N/A 오류가 표시됨, if절에 사용가능
- 향후에 입력되는 값을 통해 자동계산 하도록 엑셀을 구성시, vlookup 등의 함수등에서 값이 없음으로 인해 발생하는 오류메시지를 제거
=numberstring(value, type) : 숫자를 문자열로
- =numberstring(123, 1) : 일백이십삼
- =numberstring(123, 2) : 壹百貳拾參
- =numberstring(123, 3) : 일이삼
2. 날짜/시간 관련 함수
=year(일련번호) : 날짜에서 년을 추출, 일련번호는 1900년1월1일 기준으로 일일 단위의 숫자임
=month(일련번호) : 날짜에서 월을 추출
=day(일련번호) : 날짜에서 일을 추출
=date(year, month, day) : 숫자를 날짜형식으로 변환
=today() : 오늘날짜
=datedif(시작일, 종료일, 단위) : 로터스 호환을 위해 추가된 함수라서 함수리스트에는 안보임
- 단위 : Y(경과된 년수), M(경과된 월수), D(경과된 일수), YM(1년 미만의 월수), MD(1달 미만의 일수), YD(1년 미만의 일수)
- 시작일보다 종료일이 나중이여야 한다
tip1 : 근속 년수를 구하기 위해서는 두 날짜의 년도(=year(..))를 구해서 빼자
tip2 : 현재날짜를 기준으로 만 근속기간 년,월,일을 구하기 위해서는 아래를 사용하자
=datedif(입사일,today(),”Y”)
=datedif(입사일,today(),”YM”)
=datedif(입사일,today(),”MD”)
=hour(일련번호)
=minute(일련번호)
=second(일련번호)
=time(hour, minute, second)
=text(value, format_text)
- TEXT(“삼십만”,”일금@원정”) = “일금삼십만원정”
- TEXT(100000,”#,”) = 100
- TEXT(“2001-7-7″,”yyyy/mm/dd”) = “2001/07/07”
- TEXT(WEEKDAY(“2001-1-1″),”aaa”) = “월”
- TEXT(WEEKDAY(“2001-1-1″),”aaaa”) = “월요일”
- TEXT(WEEKDAY(“2001-1-1″),”ddd”) = “Mon”
- TEXT(WEEKDAY(“2001-1-1″),”dddd”) = “Monday”
- TEXTt(날짜, “YYYY-MM-DD”) : 2006-03-12
- TEXT(날짜, “YY-M-D”) : 06-3-12
=weekday(일련번호, [return_type]) : 요일에 해당하는 숫자를 리턴
- return_type : 1 –> 일요일부터 1,2,…7
- return_type : 2 –> 월요일부터 1,2,…7
- return_type : 3 –> 월요일부터 0,1,…6
- choose(weekday(날짜, 1), “일”, “월”, “화”, “수”, “목”, “금”, “토”)
- if(weekday(날짜,2) <= 5, “평일”, “주말”)
3. 조건부 서식
셀영역 선택 > 메뉴에서 서식 > 조건부 서식 > 특정조건의 서식을 변경가능


4. hlookup / vlookup / lookup
=hlookup(값, 찾을 범위, 찾을 범위에서 반환할 값의 행번호, 방식)
- 찾을 범위는 항상 절대참조로 하자
- 찾을 범위를 셀영역에 이름을 붙여서 사용하는것을 추천, 이 경우 다른 sheet에서도 사용할 수 있다. 또는 찾을 범위를 블럭선택 후 “삽입 > 이름 > 만들기”를 실행하면 자동으로 만들어 준다.
- 특정값을 지정한 범위의 가장위쪽행에서 찾아서 행번호만큼 떨어진 값을 반환함
- 반환할 값의 행번호 : 찾을범위의 첫행은 찾을값이 되고 1부터 시작한다.
- 방식
- true(1 또는 생략) : 범위로 찾을 때
- false(0) : 정확한 값을 찾을 때
=vlookup(값, 찾을 범위, 찾을 범위에서 반환할 값의 행번호, 방식)
- 찾을 범위가 세로일때는 vlookup 을 사용한다. 사용법은 동일
- 찾을 범위의 데이터는 미리 오름차순(1,2,3… 가,나,다…)으로 정렬되어 있어야 한다.
5. 그밖에
- &를 사용하면 셀내용과 텍스트를 합칠 수 있음
- =”합계는 “&A1+B1&”점 입니다.”
- 날짜값이 들어 있는 셀의 값을 & 로 묶을시 날짜가 일련번호로 들어가게 된다. 이는 text 함수를 사용하여 변경하여야 한다.
- =”합계는 “&A1+B1&”점 입니다.”
- 데이터 유효성 검사
- 데이터 > 유효성검사 를 통해 셀입력값을 제한할때 combo box를 생성
ex) 셀에 남,여 만 입력받을때 : 목록 선택 후 “남,여” 입력
ex) 셀에 셀영역 이름을 정한곳의 값만 입력받을때 : 목록 선택 후 “=이름” 입력
- 데이터 > 유효성검사 를 통해 셀입력값을 제한할때 combo box를 생성
—————————————————-
아래부터는 스프링 노트에서 정리한 내용임
—————————————————-
통계함수
- max(숫자들 또는 영역) : 영역에서 최대값
- min (숫자들 또는 영역): 영역에서 최소값
- large(영역, 몇번째) : 영역에서 몇번째로 큰 수
- small(영역, 몇번째) : 영역에서 몇번째로 작은 수
- mode(숫자들 또는 영역) : 영역에서 최고 빈도값
- median(숫자들 또는 영역) : 영역에 중앙값을 구함, 평균이 아니라 중간에 위치한 값
- 영역이 홀수개이면 중간값은 1개가 선택
- 영역이 짝수개이면 중앙값인 2개 값의 평균이 됨
- 영역이 홀수개이면 중간값은 1개가 선택
기타 함수
- index(영역, 행번호, 열번호)
- match(lookup_value, lookup_array, [match_yype]
- match_type : 0(정확히 같은 값을 찾음), 1, -1
- 영역내에서 찾는 값이 몇번째 위치에 있는지 구함
- index 함수와 같이 사용하면 좋다
- match, index 함수는 “[보기]-[도구모음]-[양식]” 에서 콤보상자를 삽입후 나오는 index 값을 통해 처리하면 보기 좋다.
- match_type : 0(정확히 같은 값을 찾음), 1, -1
특정 행/열 색상 바꾸기
- row([셀]) : 선택셀(생략시 현재셀)의 행번호
- tip : 세로로 일련번호(1, 2, 3….)을 넣을때 숫자(1,2,3…) 대신 =row()를 쓰면 행이 전체가 삭제되어도 일련번호는 그대로 유지된다.
- column([셀]) : 선택셀(생략시 현재셀)의 열번호
- mod(number, devisor) : 나머지 값
- tip : if(mod(row(), 2) = 0, “짝수행”, “홀수행”)
- 짝수행만 서식 변경하기
- [서식]-[조건부 서식]
- 조건 : “수식이”
- 식 : “=mod(row(),2)=0”
- 서식 : 배경 색상 변경
- 조건 : “수식이”
- [서식]-[조건부 서식]
Database
- dcount(데이타베이스, 필드, 건이 입력되어 있는 셀) : 숫자만
- dcounta(데이타베이스, 필드, 조건이 입력되어 있는 셀) : 숫자 및 문자
- 조건의 첫행에는 반드시 필드명이 입력되어 있어야 함
- countif는 단일 조건만 가능하지만, dcount/dcounta는 다중조건 비교 가능
- 필드는 숫자(1부터 시작) 또는 필드명이 들어감
- 조건이 같은행 : and 조건
소속
총점
서울
>=800
- 조건이 다른행 : or 조건
소속
서울
경기
소속
총점
서울
>=800
- 조건의 첫행에는 반드시 필드명이 입력되어 있어야 함
- dsum(database, 필드, 조건)
- daverage(database, 필드, 조건)
- sumif(범위, “조건”, 합계를 구할 범위) 은 하나의 조건만 사용할 수 있음
- dmax(database, 필드, 조건)
- dmin(database, 필드, 조건)
- dget(database, 필드, 조건) : database에서 조건에 만족하는 값을 필드에서 찾아줌
- 찾으려는 값이 중복되면 오류가 발생 : dcounta 함수로 중복데이터인지 판단해 줘야 한다.
- if(dcount(데이터, “실적”, 조건영역)=1, dget(데이터, “실적”, 조건영역), “대략중복”)
- 찾으려는 값이 중복되면 오류가 발생 : dcounta 함수로 중복데이터인지 판단해 줘야 한다.
- 데이타베이스 함수와 “데이터 > 표” 기능을 조합하여 집계가 가능하다
- 원본 데이타베이스와 집계할 표
- 원본 데이타베이스와 집계할 표
- 집계할 표의 좌측상단 교차셀에 데이타베이스함수 사용 & 조건은 값이 없음
- 집계할 표의 좌측상단 교차셀에 데이타베이스함수 사용 & 조건은 값이 없음
- 집계할 표의 영역을 선택후 “데이터 > 표”를 선택하여 조건셀의 행값과 열값을 선택
- 집계할 표의 영역을 선택후 “데이터 > 표”를 선택하여 조건셀의 행값과 열값을 선택
와!! 교육 다 끝났다!!

Notice

썖
퍼가연