본문 바로가기

Excel/엑셀 함수49

엑셀] 숨기기로 숨긴 열(세로) 제외하고 계산하기 어떤 열을 제외하고 계산할 때 사용하는 팁.대표적인 예로는 숨겨진 열을 제외하고 계산할 때 필요하다. 엑셀에서는 행이나 열을 숨길 수 있고, 행을 숨겼을 때는 subtotal 함수를 이용해야 숨기기한 행을 계산에서 제외하거나 포함하면서 원하는 결과값을 얻을 수 있다. - 엑셀] 필터, 숨기기로 셀 숨기고 계산하기 - SUBTOTAL 아래 그림과 같은 표가 있다고 가정해보자. 월 데이타와 분기 데이터가 함께 있는 경우이다. 합계를 구하기 위해서 sum함수나 subtotal함수를 사용해도 원하는 값을 얻을 수 없다. 여기서는 cell함수를 사용할 예정인데, 이해의 편의를 위해서 분기값을 구한 열의 너비를 조금 더 넓혀서 다음과 같이 식을 입력해보자. cell("width", 원하는 셀주소) 함수는 셀의 넓이.. 2016. 6. 20.
엑셀] 해당 월의 마지막 날짜 구하기 엑셀의 EOMONTH 함수는 End OF Month 즉 그 달의 마지막 날을 계산해서 돌려주는 함수이다. 함수는 두 개의 인수를 받는데 첫번째 인수는 값을 구하길 원하는 날짜(start_date)이고, 두번째 인수는 해당 월, 또는 이전 달이나 다음 달을 지정할 수 있다. EOMONTH 함수는 결과값으로 정수를 돌려주기 때문에, 셀서식을 날짜로 바꿔주어야 원하는 값을 얻을 수 있다. 엑셀] 날짜 표시하고 계산하기 - DATE 엑셀] 날짜 차이 구해서 년, 개월, 일 형식으로 계산하기 - DATEDIF 엑셀] 요일 표시하기 - 셀서식 & TEXT 함수 엑셀] 텍스트 형식으로 된 날짜 변경하기 엑셀] 날짜 데이터로 해당 분기 구하기 엑셀] 날짜 관련 .. 2016. 1. 8.
엑셀] 초단위, 분단위 반올림하기 데이터를 외부에서 가져올 경우 초단위 또는 분단위의 시간을 필요에 의해 반올림해서 정리해야 할 때가 있다. 먼저 초단위 반올림부터 알아보자. 엑셀에서 시간에 대한 계산은 1일을 기준으로 하기 때문에 1시간은 1/241분은 1/1440으로 계산한다. 그렇기 때문에 초단위 시간에 1440을 곱해서 분단위 시간으로 바꾸고, 일의 자리를 기준으로 반올림한 후 다시 1440으로 나눠서 시간형식으로 표현하면 원하는 결과를 얻을 수 있다. 30분을 기준으로 시간단위로 반올림하는 것은 아래처럼24를 곱해서 시간 기준으로 바꿔서 반올림처리하고다시 24로 나눠준다 2016. 1. 5.
엑셀] 텍스트에서 오른쪽 단어만 추출하기 엑셀 텍스트에서 오른쪽 끝에 있는 단어만 추출하기 위해서 사용하는 함수는 RIGHT함수이다. RIGHT함수는 2개의 인수를 사용한다. RIGHT(text, num_chars)- text : 추출하려는 문자가 들어있는 텍스트- num_chars : 오른쪽 끝에서부터 추출할 문자의 수 RIGHT 함수는 오른쪽에서부터 필요한 문자수 만큼 반환하기 때문에 위 그림처럼 규칙적인 글자수를 갖는 경우는 어렵지 않게 추출할 수 있다. 오른쪽에서 추출해야 하는 단어가 이름처럼 일정한 문자수를 갖지 않는 경우는 어떻게 해야 할까? 아래 그림처럼 말이다. 조금 복잡하지만 키포인트는 RIGHT 함수를 사용하되 마지막 띄어쓰기(" ") 다음부터 추출하도록 하면 된다. 수식이 조금 복잡하니까 단계별로 알아보자. 1. 띄어쓰기를 .. 2015. 12. 29.
엑셀] 텍스트에서 첫 단어만 추출 엑셀로 저장된 주소록 같은 텍스트에서 첫번째 나오는 단어만 추출하는 방법을 알아본다. 텍스트의 왼쪽 부분만 가지고 오는 엑셀함수는 LEFT 함수이다. 이전 글 참조 - 엑셀] 텍스트 조작 (&, LEFT, MID, FIND함수) LEFT 함수는 왼쪽에서 몇 글자를 떼서 저장할 경우 쉽게 사용 가능하지만, 첫단어의 글자수를 특정할 수 없는 경우는 find 함수와 함께 사용해서 이 문제를 해결 할 수 있다. 위 예제는 FIND 함수로 띄어쓰기가 몇 번째 글자에 나오는지 찾아내서 그 값을 인수로 LEFT 함수에게 전달한다. 2015. 12. 27.
엑셀] 텍스트가 아닌 빈 칸 없애기 - TRIM 함수 시스템으로부터 텍스트 형태의 데이터를 얻었을 때 엑셀 형식에 맞추는 방법은 텍스트 파일을 그대로 엑셀에서 불러와서 각 셀을 구분짓는 방법이 있지만, 간단하게 한 열 정도의 데이터만 필요한 경우는 아래한글을 사용하는게 편한 경우도 있다. 텍스트 파일을 복사해서 아래한글에 붙여넣고 복사해올 데이터의 시작부분에 커서를 두고 F4 키를 누른다(원래 아래한글에서 복사할 때 사용하는 단축키는 F3이다) 오른쪽 화살표로 해당 데이터 끝부분으로 이동하고 그 다음 아래쪽 화살표로 복사할 줄의 끝까지 이동한다. 이렇게 하면 필요한 열에 대한 데이터만 복사 가능하다. 하지만 가끔 이렇게 복사한 데이터로 정렬이나 필터를 했을 때 원하는 결과가 나오지 않을 때가 있다. 또 중복제거 기능도 제대로 작동하지 않는다. 이런 경우 복.. 2014. 2. 24.
엑셀] 텍스트 바꾸기 - REPLACE함수, SUBSTITUTE 함수 엑셀에서 문서 보고서 작성시 주민등록번호나 전화번호 등 개인정보와 관련된 내용을 * 표시로 대체하는 법에 대해 알아본다. REPLACE 함수 REPLACE 함수는 텍스트의 일부를 다른 텍스트로 바꾸는 함수이다. 모두 4개의 인수를 받는다. REPLACE(old_text, start_num, num_chars, new_text) - old_text : 텍스트를 바꿀 셀 주소 - start_num : 몇 번째 글자부터 바꿀까? - num_chars : 모두 몇 글자를 바꿀까? - new_text : 무슨 글자로 바꿀껀데?? G2 셀의 수식은 다음과 같다 =REPLACE(F2, 9, 6, "******") F2 셀의 텍스트를 9번째부터(주민등록번호 뒷자리 성별 다음부분) 6글자를 "******"로 채우는 것이.. 2014. 2. 23.
엑셀] 피벗 테이블 엑셀에서 피벗 테이블은 많은 데이터들을 카테고리화해서 요약하여 집계를 구할 때 사용하는 기능이다. 피벗 테이블의 생소한 명칭 때문에 처음 접할 때 다소 어렵게 느껴지지만 간단한 기능부터 알아보자. 아래 그림과 같은 데이터들을 피벗 테이블로 집계해보자. 표 범위 아무 곳이나 클릭하고서 을 클릭한다. 표 또는 범위를 확인하고, 피벗 테이블 보고서가 만들어질 위치를 기존 워크시트로 하거나 새 워크시트로 정하고 확인 버튼을 클릭한다. 다음 그림과 같은 피벗 테이블 도구가 새로운 시트에 열린다. 여기서 열 레이블과 행 레이블, 값. 이렇게 3가지가 제일 중요한 개념이다. 가로가 열레이블, 세로가 행 레이블이고, 가운데 값 영역이 집계하고 싶은 데이터의 계산이 이루어지는 영역이다. 먼저 부서별로 무엇인가를 집계해서.. 2014. 2. 17.
엑셀] 엑셀 이름 정의와 표에 데이터 추가를 편리하게 - 엑셀표 엑셀이 원래 표 형식으로 데이터를 입력하는 프로그램인데 엑셀 표는 뭐???엑셀표 기능은 엑셀 2007부터 시작된 기능이다. (그 이전의 명칭은 목록)일단 표를 만들어놓고, 그냥 엑셀 워크시트에 데이터를 입력하는 것과 무엇이 다른지 알아보자. 1) 엑셀에서 표 만들기그림처럼 행에 제목을 넣고 원하는 표를 두어줄 만든다. 표 전체를 셀블록으로 잡고 버튼을 클릭한다. 표에 사용할 데이터 범위를 지정해주고, 표 상단에 제목에 해당하는 열이 있으므로 "머리글 포함"에 체크표시한다 아래그림은 표가 설정된 화면이다. 작업이 완료된 다음을 보면 리본에 이 추가되었고 맨 윗줄에 필터 버튼이 붙은 것 이외 변화된 것은 없어보인다. 이제부터 "표"로 작업하면 어떤 잇점이 있는지 하나씩 살펴보자. 2) 행 추가시 자동으로 표.. 2014. 2. 10.

티스토리툴바