편리한 프로그램 배우자

엑셀 특정 평균값은 어떻게 구하나요?

엑셀을 사용하면서 평균값을 자주 구합니다. 

학생의 평균 성적은? 판매 평균은? 등 다양한 이유로 평균값을 구합니다.

일반적인 평균 값을 구하는 것은 쉽게 가능합니다.

혹시 알고 싶으시면 아래 링크를 클릭해보세요 예전에 기록한 내용입니다.

구글 스프레드시트 평균(AVERAGE 함수) 구하기

평균값을 구하는 과정에서 특정한 값은 제외하는 방법은 없을까?
특정한 값을 빼고 특정값을 구하고 싶은데 방법이 없을까?

AVERAGEIFS 함수로 평균을 구하면 됩니다.

 → 특정 조건들을 제외한 평균값을 구합니다.

     AVERAGEIF는 1개의 조건만 제외하지만 AVERAGEIFS는 1개 이상 조건들을 제외합니다.

과일 야채 판매 현황 중 판매를 안 한 달 0을 제외하고 평균값을 구해 보겠습니다. 

1.  AVERAGEIFS 함수 마법사를 실행합니다. 

   1번 : 함수 마법사를 실행합니다.

   2번 : 함수 검색에서 average로 검색을 합니다. 

   3번 : 함수 중 averageifs를 선택합니다.

   4번 : 확인을 클릭합니다. 

2.  AVERAGEIFS 함수마법사에 조건을 입력합니다.

   1번 : 평균을 구할 셀의 범위를 설정 (1월~6월 범위 설정)

   2번 : 특정 조건의 범위를 설정 (1월~6월 범위 설정)

   3번 : 특정 조건을 입력합니다. (0 이상으로 입력)

   4번 : 확인을 클릭합니다. 

3.  AVERAGEIFS 함수 사용 후 채우기를 통해서 나머지 칸에 값을 넣습니다.

   1번 : 점을 클릭 후 드레그를 해서 채우기를 합니다.  

4.  평균값하고 AVERAGEIFS 함수 값을 비교해봤습니다. 

   1번 : 1월 ~ 6월까지 데이터중에 0이 입력된 제품명에 비교를 위해 색깔로 구별해두었습니다.

엑셀 조건에 맞는 행값 찾기 (HLOOKUP 함수)

엑셀에서 가장 많이 쓰는 함수 중에 하나인 VLOOKUP 함수가 있습니다.

VLOOKUP 함수 형제로 HLOOKUP 함수가 있습니다.

회사 보고형태에 따라서 HLOOKUP 함수를 더 많이 사용하는 곳도 있을것입니다.

HLOOKUP 함수란?

 첫 행의 항목과 가져올 데이터 항목 행과 비교하여 찾고자 하는 순서의 데이터 값을 가져옵니다.

HLOOKUP 함수의 의미를 쉽게 적는다고 생각했는데도 어려워보이네요 

HLOOKUP 함수도 VLOOKUP 함수처럼 쉽게 할수 있으니 따라 해보세요 

조건 : 원본데이터에서 1번에 비어져 있는 곳에 값을 채워 넣어 보세요 

   1번을 보면 13행의 항목이 5행의 항목 순서가 틀립니다

   HLOOKUP 함수 활용하면 쉽게 할수있습니다. 

1. HLOOKUP 함수를 실행시킵니다.

  1번 : 함수 마법사를 클릭합니다

  2번 : 함수 검색란에 HLOOKUP를 기입합니다. 

  3번 : 검색을 클릭해서 HLOOKUP 함수를 찾습니다

  4번 : 확인을 클릭해서 HLOOKUP 함수를 실행합니다 

2. HLOOKUP 함수에 인수를 입력합니다. 

  1번 : 13행을 선택 후 F4 클릭(절대참조 설정) _ 조건 항목 선택

  2번 : 5행 선택 후 F4 클릭(절대참조 설정) _ 찾을 항목을 선택 

         단 13행의 항목과 5행의 첫 항목의 조건이 같아야함

  3번 : 동일한 조건중에 1월데이터를 가져오면 2번째이기 때문에 2를 입력

  4번 : 0을 입력 (정확히 일치하는 항목)

3. 드래그를 통해서 채워넣기를 진행합니다. 

   1번과 같이 채워 넣기를 하였는데 열무와, 고추는데이터에 없는 항목이어서 에러 메세지가 나옴

에러일경우 IFERROR함수를 통해서 개선할수있음

IFERROR함수 사용법은 아래 링크를 눌려서 확인해보세요

2020/08/19 - [엑셀/엑셀 함수] - 엑셀 수식 오류 발생시 제거 하는 방법(IFERROR 함수)

엑셀 수식 오류 발생시 제거 하는 방법(IFERROR 함수)

엑셀로 VLOOKUP 함수 사용하여 데이터를 만든후 에러가 발생하였을 때 

에러가 난 상태로 보고서를 제출하기가 어려워서 에러를 지우고 제출을 하는데 

함수작성시 IFERROR 함수를 함게 사용하면 편리하게 이용가능합니다.

IFERROR 함수는 에러 났을때 어떻게 표현 해라는 명령어입니다. 

1. VLOOKUP 함수를 사용하였는데 고구마, 오이 데이터가 없어서 에러가 발생한 상황입니다. 

   IFERROR 함수를 넣어서 에러를 다르게 표현해보겠습니다. 

2. IFERROR 함수 마법사를 실행은 어렵고 입력창에 함수를 입력해야됩니다. 

   IFERROR 함수 마법사는 아래 화면에 표현됩니다 "오류일때 변환합니다"

   IFERROR 함수와 VLOOKUP함수를 중복사용하기 때문에 VLOOKUP 함수 이후에 입력창에 직접 입력해야됩니다.

   1번 : 1월데이터 선택

   2번 : 입력창에 IFERROR을 입력합니다 

3. VLOOKUP 함수를 사용하고 에러발생시 어떻게 표현할지 설정

  1번: H5번 셀을 선택 ( 기본 VLOOKUP 함수가 입력되어있음)

  2번 : VLOOKUP 함수에 IFERROR 함수를 추가합니다.

        IFERROR( VLOOKUP 함수, 에러시표현방법 ) 이렇게 표시됩니다. 

  3번 : 에러시 "" 이렇게 조건을 입력합니다. 

         "" 뜻은 빈칸으로 표현해달라는 뜻입니다.

        다 완료되면 드래그해서 자동입력 고구마, 오이, 무 빈칸으로 나타납니다. 

4. 2탄 _ IFERROR 함수 에러발생시 어떻게 표현할지 설정 

  1번: H5번 셀을 선택 ( 기본 VLOOKUP 함수가 입력되어있음)

  2번 : VLOOKUP 함수에 IFERROR 함수를 추가합니다.

        IFERROR( VLOOKUP 함수, 에러시표현방법 ) 이렇게 표시됩니다. 

  3번 : 에러시 0 이렇게 조건을 입력합니다. 

         0 뜻은 빈칸으로 표현해달라는 뜻입니다.

        다 완료되면 드래그해서 자동입력 고구마, 오이, 무에 0으로 나타납니다

5. 3탄 _ IFERROR 함수 에러발생시 어떻게 표현할지 설정 

  1번: H5번 셀을 선택 ( 기본 VLOOKUP 함수가 입력되어있음)

  2번 : VLOOKUP 함수에 IFERROR 함수를 추가합니다.

        IFERROR( VLOOKUP 함수, 에러시표현방법 ) 이렇게 표시됩니다. 

  3번 : 에러시 1 이렇게 조건을 입력합니다. 

         1 뜻은 빈칸으로 표현해달라는 뜻입니다.

        다 완료되면 드래그해서 자동입력 고구마, 오이, 무에 1로 나타납니다

6. 4탄 _ IFERROR 함수 에러발생시 어떻게 표현할지 설정 

  1번: H5번 셀을 선택 ( 기본 VLOOKUP 함수가 입력되어있음)

  2번 : VLOOKUP 함수에 IFERROR 함수를 추가합니다.

        IFERROR( VLOOKUP 함수, 에러시표현방법 ) 이렇게 표시됩니다. 

  3번 : 에러시 미판매 이렇게 조건을 입력합니다. 

         미판매 뜻은 빈칸으로 표현해달라는 뜻입니다.

        다 완료되면 드래그해서 자동입력 고구마, 오이, 무에 다시 에러로 표시됩니다.

        에러로 표시되는 이유는 숫자를 제외 글을 입력시 "" 를 함께 사용해야됩니다

7. 4탄 _ IFERROR 함수 에러발생시 어떻게 표현할지 설정 

  1번: H5번 셀을 선택 ( 기본 VLOOKUP 함수가 입력되어있음)

  2번 : VLOOKUP 함수에 IFERROR 함수를 추가합니다.

        IFERROR( VLOOKUP 함수, 에러시표현방법 ) 이렇게 표시됩니다. 

  3번 : 에러시 "미판매" 이렇게 조건을 입력합니다. 

         "미판매" 뜻은 빈칸으로 표현해달라는 뜻입니다.

        다 완료되면 드래그해서 자동입력 고구마, 오이, 무에 다시 미판매로 표시됩니다.

엑셀 서로 매칭되는 조건 값 찾기 (VLOOKUP 함수)

회사에 입사하면 가장 많이 사용한다는 VLOOKUP 함수입니다.

회사에서 VLOOKUP 함수를 왜 많이 사용할까? 

 → 원본데이터에서 내가 원하는 조건에 맞는 데이터 값을 찾아야 할일이 많기 때문입니다.

또 VLOOKUP 함수 형제인 HLOOKUP 함수도 있는데 다음시간에 설명드리겠습니다. 

쉽게 VLOOKUP 함수를 사용할수 있으니 따라 해보세요 

1. 원본데이터에서 원하는제품명의 1월데이터를 찾아보세요,.

   1번 : 제품명의 1월 데이터를 찾아보세요.

   2번 : 원본데이터에서 1번에 맞는 값이 찾는 형태입니다.  

2. VLOOKUP 함수를 실행합니다.

  1번 : 입력할 H5에 셀을 두고 함수마법사 실행합니다. 

  2번 : VLOOKUP를 입력합니다.

  3번 : 검색 클릭후 VLOOKUP 함수를 클릭후 확인클릭

3. VLOOKUP 함수 조건 설정

  1번 : 찾을 조건 범위 설정, 보통 범위만 설정하는데 열 전체로 잡아도 됩니다.

  2번 : 찾을 조건에 첫행이 되게하여 데이터를 가져올 범위까지 선택

         1번에서 열 전체를 선택하였기 때문에 2번도 열 전체를 선택해야됩니다. 동일한 조건으로

  3번 : 입력할 데이터가 몇번째 있는지 선택, 1월데이터를 가져와야 하기때문에 2를 입력

  4번 : 0을 입력

4. 함수를 입력했으면 드래그를 통해 자동입력시켜주세요.

  1번 : 함수 확인

  2번 : 드래그하여 채워넣기 진행

엑셀 자동으로 순번 입력하기(ROW함수)

엑셀 데이터 표를 만들때 순번을 입력합니다. 

순번을 입력하고 데이터를 변경하면 또 순번을 변경해야되는데 

ROW 함수를 활용하면 순번을 한번 입력해놓으면 변경을 안해도 됩니다. 

지난번 구글 스프레드시트에서도 해봤는데 엑셀에서도 동일한 함수로 가능합니다. 

구글 스프레드 시트에서 자동순번넣기를 알고 싶으면 아래 링크를 눌려보세요.

2020/07/30 - [분류 전체보기] - 구글 쉽게 순번 넣는 방법(ROW 함수)

엑셀 자동으로 순번 넣기

1. 아래화면 처럼 과일, 채소 앞에 순번을 자동으로 입력하는 ROW함수에 대해서 알아보겠습니다. 

1. ROW 함수와 정수를 함께 해서 사용

  1번 : 순번을 입력할 셀 선택하여 입력 진행 ROW()-4

        입력창에 입력된 내용으로 입력 진행 

        왜? - 4를 입력해야되는지? ROW() 함수만 입력시 5로 입력되어 -4를 입력하여 1로 변환한것입니다. 

        왜? ROW() 입력하면 5가 되는이유는 행번와 동일하게 입력된다고 보시면됩니다 지금 1번이 5행에 있기 때문에 5

2. ROW() 함수

  1번 : 함수마법사를 통해서 ROW 함수 실행

  2번 : ROW 함수에 셀 지정 하기전에 "수식 결과 = 5"로 표시됨

        확인을 눌리면 수식 결과와 동일하게 5로 입력됨

  현재 입력되는 위치가 B5에 입력됨, 행만 표현된다고 생각하시면됩니다. 

3. ROW함수에 셀 지정

  1번 : 함수마법사를 통해서 ROW 함수 실행

  2번 : ROW 함수 B1 셀 지정  "수식 결과 = 1"로 표시됨

        확인을 눌리면 수식 결과와 동일하게 1로 입력됨

4. ROW 함수 표현하는 모습 정리

  1번과, 2번, 3번에 입력된 순번이 동일합니다. 

  입력창에 ROW() 입력하면 현재 행번호의 숫자가 입력됩니다. 

  5번의 ROW함수는 3행에 입력창에 입력된 내용입니다. 

엑셀 날짜수 쉽게 구하는법

엑셀 날짜 계산할때 DAYS360함수 사용에 대해서 알아보았는데

혹시 기억이 안나신다면 바로 가기를 통해서 DAYS360함수를 참고 해보세요

2020/06/22 - [엑셀/엑셀 함수] - 엑셀 DAYS360 함수 (기간 날짜 일수 구하기)

하지만 오늘 알게 되는 날짜 계산법을 보신다면 DAYS360함수는 안찾으실것 같아요

날짜 계산하는 방법 쉽게 알아보겠습니다. 

아래와 같이 예제를 통해서 여행일수와 숙박을 계산하겠습니다.

여행일수는 시작 부터 종료하는 날짜까지 전체 계산해야되고

숙박은 마지막 날은 숙박이 없기때문에 여행일수보다 1일이 작습니다. 

1. 날짜 계산을 + - 덧셈 뺄셈을 통해서 계산하는 방식입니다.

   아래를 참고하면 날짜는 회계를 체크를 하면 숫자로 변환되는 것을 알수있습니다.

   날짜가 숫자인것을 참고해서 일수를 계산할예정입니다. 

2. 날짜 계산법과 Tip도 적용하겠습니다.

   1번 : 숙박 = 도착날짜 빼기 출발날짜로 셀을 선택 (DAYS360함수 값과 동일)

          여행일수 = 도착 날짜 빼기 출발날짜로 셀을 선택 후 +1 

   2번 : 출발날짜와 도착 날짜를 3번에 회계로 변경하면 숫자로 변경됨

          3번 회계밑에 있는 , 쉼표를 클릭해도 숫자로 변경됨

          2번 숫자값을 응용해서 1번처럼 덧셈 뺄셈을 활용해서 날짜를 계산함

   4번 : Tip으로 여행일수 값뒤에 "일" 숙박 값뒤에는 "박"을 삽입

          & -> 이어서 표시 해라는 명령어라고 생각하시면 쉽게 이해됩니다.

          "" -> ""표시 사이 값을 붙여진다고 생각하시면 쉽게 이해됩니다.

                 "  " 이렇게 붙여지면 뛰어쓰기가 붙여집니다.

         여러가지를 붙여넣을 수 있으니 활용해보세요.

엑셀 날짜 일수 구하기(기간 일수 구하기)

엑셀을 하다가 날짜 계산을 해야되는 경우가 있습니다.

 예1) 보고서 프로젝트 남은 일수

 예2) 여행 기간

 예3) 재 생산기간 등

다양하게 날짜를 계산해야되는 경우가 있습니다. 

엑셀로 쉽게 날짜를 계산하는 방법에 대해서 알아보겠습니다.

문제 : 아래와 같이 여행을 가는데 몇박 몇일인지 DAYS360함수로 계산 해보세요

1. DAYS360함수를 실행해서 숙박일수를 구해보겠습니다.

   1번 : 숙박에 커서를 가져다 둡니다.

   2번 : 함수마법사를 실행시킵니다.

   3번 : DAYS를 입력하여 함수를 검색합니다( 함수 이름을 알경우 바로 함수를 선택하셔도됩니다)

   4번 : DAYS360 함수를 클릭합니다.

   5번 : 확인을 클릭해서 함수 인수를 실행시킵니다.

   6번 : DYA360함수에서 일수를 계산해야되는 시작 시점의 날짜를 선택합니다. 

   7번 : DYA360함수에서 일수를 계산해야되는 종료 시점의 날짜를 선택합니다. 

2. 확인후 드레그하시면 숙박이 아래와 같이 나옵니다.

다음시간에 다른 방법으로 여행일수와 숙박 일수를 계산하는 방법에 대해서 설명드리겠습니다. 

엑셀 제곱근함수 (SQRT 함수)

엑셀 수식을 통해서 제곱근 값을 구하거나 제곱근 함수를 통해서 값을 구할수 있습니다.

아래와 같이 제곱근의 값을 구해야할때 수식으로 해도되고, SQRT함수를 통해서 해도됩니다. 

1. 제곱근 9를 수식으로 바로 적게 되면 아래와 같이 적으면 됩니다. 

   ^ 기호를 통해서 제곱근를 표현하면됩니다. 

2. 함수 마법사를 통해서 엑셀 SQRT 함수를 불러옵니다. 

3. 엑셀 SQRT함수 값에 인수를 입력 또는 셀을 입력

   아래와 같이 9입력시 값이 3이 나옴