Excel 함수




회사에서 교육점수 채우려고 공부하는 내용입니다.
순서는 교육과정을 따르고 있지만 제 맘대로 정리합니다.. ^^


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(, 찾을 범위, 반환할 값의 범위)



  • 찾을 범위의 데이터는 미리 오름차순(1,2,3… 가,나,다…)으로 정렬되어 있어야 한다.



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(데이터, “실적”, 조건영역), “대략중복”)



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


    • 원본 데이타베이스와 집계할 표
      사용자 삽입 이미지
















    • 집계할 표의 좌측상단 교차셀에 데이타베이스함수 사용 & 조건은 값이 없음
      사용자 삽입 이미지

























    • 집계할 표의 영역을 선택후 “데이터 > 표”를 선택하여 조건셀의 행값과 열값을 선택
      사용자 삽입 이미지














실습화일 Download
1362831138.zip


와!! 교육 다 끝났다!!
사용자 삽입 이미지

CC BY-NC-ND 2.0 KR

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

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

2 댓글

댓글 남기기