순서는 교육과정을 따르고 있지만 제 맘대로 정리합니다.. ^^


1. 이것저것 함수
=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(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”)
  

=sumif(범위, “조건”, [합계를 구할 범위]) : 조건을 만족하는 셀의 합계를  구함

• 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 을 사용한다. 사용법은 동일

=lookup(값, 찾을 범위, 반환할 값의 범위)




5. 그밖에

1. &를 사용하면 셀내용과 텍스트를 합칠 수 있음
   
   
   
   • =”합계는 “&A1+B1&”점 입니다.”
     
   • 날짜값이 들어 있는 셀의 값을 & 로 묶을시 날짜가 일련번호로 들어가게 된다. 이는 text 함수를 사용하여 변경하여야 한다.
   
   
2. 데이터 유효성 검사
   
   
   
   • 데이터 > 유효성검사 를 통해 셀입력값을 제한할때 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부터 시작) 또는 필드명이 들어감
    
  • 조건이 같은행 : and 조건
    
    
    
    
    
    
    
    

    소속	총점
    서울	>=800

    
    
  • 조건이 다른행 : or 조건
    
  • 
    
    
    
    
    
    

    소속

    서울 경기

    
    
    
    
    
    
    
    
    

    소속	총점
    서울	>=800

• dsum(database, 필드, 조건)
  
• daverage(database, 필드, 조건)
  
  
  
  • sumif(범위, “조건”, 합계를 구할 범위) 은 하나의 조건만 사용할 수 있음

• dmax(database, 필드, 조건)
  
• dmin(database, 필드, 조건)
  
• dget(database, 필드, 조건) : database에서 조건에 만족하는 값을 필드에서 찾아줌 
  
  
  
  • 찾으려는 값이 중복되면 오류가 발생 : dcounta 함수로 중복데이터인지 판단해 줘야 한다.
    
  • if(dcount(데이터, “실적”, 조건영역)=1, dget(데이터, “실적”, 조건영역), “대략중복”)

• 데이타베이스 함수와 “데이터 > 표” 기능을 조합하여 집계가 가능하다
  
• 
  
  
  
  • 원본 데이타베이스와 집계할 표
    
    
  
  
• 
  

• 
  
  
• 
  

• 
  
  
  
  • 집계할 표의 좌측상단 교차셀에 데이타베이스함수 사용 & 조건은 값이 없음
    
    
  
  
• 
  
  
• 
  
  
• 
  
  
• 
  
  
• 
  
  
• 
  
  
• 
  
  
• 
  
  
• 
  
  
• 
  
  
• 
  
  
• 
  
  
  
  • 집계할 표의 영역을 선택후 “데이터 > 표”를 선택하여 조건셀의 행값과 열값을 선택
    
    

Notice

이 저작물은 크리에이티브 커먼즈 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용할 수 있습니다. 

저작권과 관련된 파일요청 및 작업요청을 받지 않습니다.

댓글에 대한 답변은 늦을 수도 있습니다.