회사에서 교육점수 채우려고 공부하는 내용입니다.
순서는 교육과정을 따르고 있지만 제 맘대로 정리합니다.. ^^
여러 함수들
trimmean
- trimmean(array, percent) : percent에 포함되는 위/아래 값을 제외하고 나머지 값으로 평균
- trimmean(10개 array, 0.2) ==> 2(=100.2)개 제외(위1, 아래1)
- trimmean(13개 array, 0.2) ==> 2(=130/2=2.6 ==> 위/아래로 나눠야 되므로 근접한 짝수개인 2)개 제외(위1, 아래1)
- trimmean(5개 array, 2/5) ==> 5개중에서 상위1개, 하위1개를 제외하고 평균구함
int
- int(number) : 숫자를 정수로 변환하여 소수 부분 잘라버림
- int(0.345) ==> 0
- int(0.678) ==> 0
- int(-1.1) ==> -2
- int(-1) ==> -1
if
- 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
- averagea(value1, [value2], …) : 공백이외의 데이타(문자열)도 평균계산에 포함, 즉 문자열은 0 값으로 인식됨
rank
- rank(number, 범위, [0 또는 생략=내림차순, 1 또는 그외의값=오름차순]) : 특정값의 범위(절대참조로 지정하는게 복사할때 편하다)내의 순위를 구함
- tip : 합계를 통해 rank를 매길때 동점자 처리를 위해 특정항목에 가중치를 두고 싶으면, 가중치 항목에 0.01와 같은 값을 곱한후 합계에 더한값을 통해 rank 한다.
left, right, mid
- left(텍스트, 갯수)
- right(텍스트, 갯수)
- mid(텍스트, 시작위치, 갯수) : 시작위치는 1부터 시작한다
choose
- 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
- count(value1, [value2]…) : 숫자가 입력된 셀의 갯수
- counta(value1, [value2]…) : 문자열셀도 포함하여 셀의 갯수
- countblank(value1, [value2]…) : 아무것도 입력되지 않은 셀의 갯수
- countif(범위, "조건") : 조건을 만족하는 셀의 갯수
- ex) 신용카드인 셀의 갯수
=COUNTIF(E5:E12, "신용카드") - ex) 숫자값이 15이상인 셀의 조건
=COUNTIF(E5:E12, ">=15")
sumif
- sumif(범위, "조건", [합계를 구할 범위]) : 조건을 만족하는 셀의 합계를 구함
- ex) 신용카드인 행의 결제금액
=SUMIF(E5:E12, "신용카드", F5:F12) - ex) 숫자값이 15이상인 셀의 합계
=SUMIF(E5:E12, ">=15", F5:F12) - '조건범위'와 '합계를 구할 범위'가 같다면 '합계를 구할 범위'는 생략 가능
- tip : countif 또는 sumif는 1개의 조건만 가능하다. 따라서 15이상이고 20이하 (15=15") - countif(범위, ">20")
- tip : 셀영역 이름을 지정해 놓은 후 사용하자 (다른 sheet에서도 사용가능하다)
round
- round(숫자, 표시할 자리수) : 반올림
- roundup(숫자, 표시할 자리수) : 올림
- rounddown(숫자, 표시할 자리수) : 버림
- 표시할 자리수 : 양수(소수점 이하 표시할 자리수), 0(정수가 됨), 음수(소수점 이상의 자리수.. 즉 0 으로 채움)
isblank
- isblank(value) : 빈칸이면 #N/A 오류가 표시됨, if절에 사용가능
- 향후에 입력되는 값을 통해 자동계산 하도록 엑셀을 구성시, vlookup 등의 함수등에서 값이 없음으로 인해 발생하는 오류메시지를 제거
numberstring
- numberstring(value, type) : 숫자를 문자열로
- numberstring(123, 1) : 일백이십삼
- numberstring(123, 2) : 壹百貳拾參
- numberstring(123, 3) : 일이삼
날짜/시간 관련 함수
- 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"
- text(날짜, "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, "평일", "주말")
조건부 서식
셀영역 선택 > 메뉴에서 서식 > 조건부 서식 > 특정조건의 서식을 변경가능
hlookup / vlookup/ lookup
hlookup
- hlookup(값, 찾을 범위, 찾을 범위에서 반환할 값의 행번호, 방식)
- 찾을 범위는 항상 절대참조로 하자
- 찾을 범위를 셀영역에 이름을 붙여서 사용하는것을 추천, 이 경우 다른 sheet에서도 사용할 수 있다. 또는 찾을 범위를 블럭선택 후 "삽입 > 이름 > 만들기"를 실행하면 자동으로 만들어 준다.
- 특정값을 지정한 범위의 가장위쪽행에서 찾아서 행번호만큼 떨어진 값을 반환함
- 반환할 값의 행번호 : 찾을범위의 첫행은 찾을값이 되고 1부터 시작한다.
- 방법 #1 : true(1 또는 생략) : 범위로 찾을 때
- 방법 #2 false(0) : 정확한 값을 찾을 때
vlookup
- vlookup(값, 찾을 범위, 찾을 범위에서 반환할 값의 행번호, 방식)
- 찾을 범위가 세로일때는 vlookup 을 사용한다. 사용법은 동일
lookup
- lookup(값, 찾을 범위, 반환할 값의 범위)
- 찾을 범위의 데이터는 미리 오름차순(1,2,3… 가,나,다…)으로 정렬되어 있어야 한다.
기타사항
- &를 사용하면 셀내용과 텍스트를 합칠 수 있음
- ="합계는 " & A1 + B1 & "점 입니다."
- 날짜값이 들어 있는 셀의 값을 & 로 묶을시 날짜가 일련번호로 들어가게 된다. 이는 text 함수를 사용하여 변경하여야 한다.
- 데이터 유효성 검사
- 데이터 > 유효성검사 를 통해 셀입력값을 제한할때 combo box를 생성
- ex) 셀에 남,여 만 입력받을때 : 목록 선택 후 "남,여" 입력
- ex) 셀에 셀영역 이름을 정한곳의 값만 입력받을때 : 목록 선택 후 "=이름" 입력
통계함수
- max(숫자들 또는 영역) : 영역에서 최대값
- min (숫자들 또는 영역): 영역에서 최소값
- large(영역, 몇번째) : 영역에서 몇번째로 큰 수
- small(영역, 몇번째) : 영역에서 몇번째로 작은 수
- mode(숫자들 또는 영역) : 영역에서 최고 빈도값
- median(숫자들 또는 영역) : 영역에 중앙값을 구함, 평균이 아니라 중간에 위치한 값
- 영역이 홀수개이면 중간값은 1개가 선택
- 영역이 짝수개이면 중앙값인 2개 값의 평균이 됨
기타 함수
- index(영역, 행번호, 열번호)
- match(lookup_value, lookup_array, [match_yype]
- match_type : 0(정확히 같은 값을 찾음), 1, -1
- 영역내에서 찾는 값이 몇번째 위치에 있는지 구함
- index 함수와 같이 사용하면 좋다
- match, index 함수는 "[보기]-[도구모음]-[양식]" 에서 콤보상자를 삽입후 나오는 index 값을 통해 처리하면 보기 좋다.
특정 행/열 색상 바꾸기
- 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부터 시작) 또는 필드명이 들어감
- dsum(database, 필드, 조건)
- daverage(database, 필드, 조건)
- sumif(범위, "조건", 합계를 구할 범위) 은 하나의 조건만 사용할 수 있음
- dmax(database, 필드, 조건)
- dmin(database, 필드, 조건)
- dget(database, 필드, 조건) : database에서 조건에 만족하는 값을 필드에서 찾아줌
- 찾으려는 값이 중복되면 오류가 발생 : dcounta 함수로 중복데이터인지 판단해 줘야 한다.
- if(dcount(데이터, "실적", 조건영역)=1, dget(데이터, "실적", 조건영역), "대략중복")
- 데이타베이스 함수와 "데이터 > 표" 기능을 조합하여 집계가 가능하다
- 원본 데이타베이스와 집계할 표
- 집계할 표의 좌측상단 교차셀에 데이타베이스함수 사용 & 조건은 값이 없음
- 집계할 표의 영역을 선택후 "데이터 > 표"를 선택하여 조건셀의 행값과 열값을 선택
와!! 교육 다 끝났다!!
썖
퍼가연