본문 바로가기

WeekdayLife268

[엑셀] 자동필터(숫자필터로 상위 30% 찾기) 엑셀 자동필터(숫자필터로 상위 30% 찾기)자동필터는 엑셀에 있어 빼놓을 수 없는 기능이지만, 의외로 자주 쓰지는 않는다.특히 감추기 이후에 활용도가 매우 떨어지는 점은 나만.... 그런가 솔직히 자동 필터 중 숫자 필터가 있다는 점은 나의 경우는 좀 뒤늦게 알았다.이건 좀 유용하다. 이번에는 여러가지 숫자가 있을 때 상위 30%를 고르는 필터를 사용해보자.아래와 같이 점수가 있다고 치자.   자동필터를 선택한다.    자동필터가 붙었다.   중간부분에 숫자 필터가 있다.    상위 10 이라고 되어 있는 메뉴를 선택한다.나머지는 한글이라 대충 메뉴만 보고 이해가 가는데,상위 10... 음... 이건 잘 안누르게 되서 몰랐던거 같다.    상위 30으로, %를 고른다.   아주 심플하게 상위 30% 필터.. 2016. 11. 28.
[엑셀] 조건부 서식(1) 엑셀 조건부 서식(1)직장생활을 시작하고 꽤 오랜시간 엑셀을 썼지만, 엑셀은 자기가 쓰는 기능만 쓴다.난, 엑셀을 네이버를 통해 배웠기 때문에, 조건부 서식같은 괴로운 기능은 쓰지 않는다.하지만,, 대충 보면 뭔지는 알겠다.만약, 컴활 1급을 준비한다면, 발로 할 수 있을 정도로 익히는 것이 좋을 것이다. 아래와 같은 표가 있다고 치자. 남자이면서, 70킬로그램 이상의 사람들이 있는 열에 노란색 하일라이트를 치고 싶다면...(이런 복잡한 일을 할 일이 있을까,,,) 조건부 서식을 써보는 것도 나쁘지 않다.    내가 색을 칠하고 싶은 영역을 선택하고 나서,조건부 서식에서 새규칙을 누른다.칠하고 싶은 영역을 버튼 누르기 전에 선택하는걸 잊지말자. 조건부 서식은 데이터 탭이 아니라, 홈탭에 있다.(그만.. 2016. 11. 28.
[엑셀] 중복된 항목 제거하기(지우기) 중복된 항목 제거하기(지우기)중복된 데이터를 지우고 싶을때, 어떻게 하시는지..나는 정렬하고 눈으로 보면서 지웠다 ㅠㅜ중복된 항목 지우기 기능은 알면서도 왠지 정이 안간다. 아래와 같이 표가 두개 있다고 하자..그 중에 왼쪽 항목에서 중복되는 항목을 지워보자.데이터 탭에 있는 중복된 항목 제거 버튼을 누른다.    모든 값이 C열에 들어있으므로 C열을 체크한다.머리글이 없으므로 체크박스에는 체크를 하지 않는다.   확인을 누르면, 왼쪽 표에서 간단하게 중복되는 값(셀)이 사라졌다.  그렇다면, 오른쪽 표는 어떨까.E,F,G 열이 있고 각각 들어가 있는 값이 다르다.해당 영역을 선택하여, 중복된 항목 제거 버튼을 눌러준다.    버튼을 누르면, 아까와 다르게 열이 세개가 보인다.모두 체크를 한다.. 2016. 11. 28.
[엑셀] 엑셀 하이퍼링크 쉽기 지우기(하이퍼링크 삭제) 엑셀 하이퍼링크 쉽기 지우기(하이퍼링크 삭제)웹 관련 업무를 보다 보면 엑셀 보고서에 URL를 써야 할 일이 많은데,그러다보면 원치 않는 하이퍼링크가 걸려 문서 작업에 매우 방해가 된다.   아래 그림은 원치 않는 하이퍼링크가 들어온 모습이다.물론 기본 세팅에서 바꿔주는 방법도 있으나,정작 필요할때, 쓰지 못하게 되므로 그 옵션은 살려두고,한방에 시트에 있는 하이퍼링크를 모두 삭제 하는 방법을 알아보자.   VBA를 사용하여 삭제를 하는 방법으로 매우 간단하다.일단, 원하는 시트에서 ALT + F11 키를 눌러 VBE 창을 띄운다. 일반적으로 우하단에 메모장 형태로 보이는 직접실행 창에다가 아래와 같이 입력한다.입력 중간중간에 가이드창이 뜨므로 오타가 날 가능성은 매우 적다. CELLS.HYPERLINK.. 2016. 11. 28.
[엑셀] 엑셀 텍스트 문자별로 나누기 엑셀 텍스트 문자별로 나누기얼마전, VBA로 문장을 글자별로 셀에 나누어 담는 방법을 포스팅 한 적이 있다.   [엑셀VBA] 텍스트 글자별로 나누기텍스트 글자별로 나누기엑셀 기본기능에 텍스트 나누기 기능이 있다.실무에서 아직 쓴적은 없지만, 강력한 기능인것 같아,코드로 짜봤다. 매크로로 녹화해서 봤더니, ARRAY로 프로그래밍되서,나sunnybong.tistory.com 사실 그 포스팅은 VBA를 연습하기 위함이었고, 엑셀상에서도 엄연히 텍스트 나누기 기능이 있다. 잘 쓰지 않는 기능이지만 가끔 텍스트 파일을 불러올 떄 요긴하게 쓸 수 있으니, 한번 알아보자 아래와 같이 셀 안에 텍스트가 들어있다고 치자.각각의 스펠을 나누어 순서대로 셀 안에 담을 예정이다.  데이터 메뉴에서 데이터도구>텍스트 나누기 .. 2016. 11. 26.
[엑셀] 특정 문자가 포함된 SUMIF함수 특정 문자가 포함된 SUMIF함수SUMIF 함수 사용법을 자세하게 보자.아래와 같이 비용과 이익이 비고 부분에 함께 들어가 있다고 치자.   SUMIF 함수를 통해 비용이 들어간 항목과 이익이 들어간 항목을 나누어 계산해 보자 우선 연간 비용은=SUMIF(B3:B8,"*"&"비용",C3:C8)로 계산할 수 있다. 비용이 공통텍스트 조건에 비용 앞에는 아무글자나 있어도 상관없음을 "*"&로 표기해주자    마찬가지로 연간 이익은=SUMIF(B3:B8,"*"&"이익",C3:C8)로 계산할 수 있다. 방법은 동일하다    앞뒤로 변경되는 텍스트가 있다면,아래처럼 처리하자.여기에선 월비를 넣으니 연간비용과 답이 동일해졌다.=SUMIF(B3:B8,"*"&"월비"&"*",C3:C8)"*"&"월비"&"*".. 2016. 11. 25.
[엑셀] 병합된 셀 풀고 값 채우기(IF함수 활용) 병합된 셀 풀고 값 채우기(IF함수 활용)셀병합은 표를 보기에는 좋으나, 정렬을 할때는 매우 불편함을 안겨준다.때로는 셀병합을 풀고 싶을 때가 있다. 아래와 같이 셀병합이 되어 있다고 하자.    병합된 셀은 그 첫번째 셀에만 값이 있는 것으로 인식을 한다.그것을 이용해서 IF 함수로 값을 떼어내보자 바로 옆에 열을 하나 만들고, 첫 행에 아래와 같이 입력한다.IF(만약 왼쪽 옆의 병합된 셀중에 내 옆에 있는 셀이 첫번째가 아니라면(공백이라면),내 위의 값을 복사한다,내 왼쪽옆 값을 복사한다) 장황하지만, 간단하게, 내옆에 놈에 값이 있으면 그대로 복사, 아니면 바로 위에 있는 셀 값 복사이다.그냥 한번 해보면 이해가 갈 것이다.    왼쪽 병합셀에 값이 있다고 인식되는 첫번째 셀들은 왼쪽 옆.. 2016. 11. 25.
[엑셀] 셀 값 드롭다운 메뉴 사용하기 - 데이터 유효성 검사(목록) 드롭다운 메뉴 사용하기 - 데이터 유효성 검사(목록)엑셀에서도 웹에서처럼 입력되어야 하는 값이 정해져 있을 때나,불필요한 값의 입력을 막기 위해 드롭다운 메뉴을 많이 활용하게 됩니다.아래와 같이 과일명을 채울 때 드롭다운 메뉴를 활용한다고 가정해봅시다.   드롭다운 메뉴를 적용할 영을 드래그하여 선택합니다.리본메뉴의 데이터 > 데이터 유효성 검사를 선택합니다.    데이터 유효성 검사창에서 제한대상을 목록으로 설정합니다.   입력대상을 선택하는 방법은 두가지가 있습니다.원본에 직접 입력하는 방법과 목록을 선택하는 방법이 있습니다. 아래는 원본을 직업 입력하는 방법입니다.종류가 몇가지 안될 때나, 문서에 해당 원본을 넣을 곳이 마땅치 않을 때 씁니다.각 항목은 콤마(,)로 구분합니다.   확인을 누르고 셀.. 2016. 11. 25.
[엑셀] 다중조건에 대한 값 찾기(SUMIF, SUMIFS) 다중조건에 대한 값 찾기(SUMIF, SUMIFS)아래와 같은 표가 있다.조건에 대한 답을 찾아보자.     1-1. 서준열씨의 매출액 합계 - sumif 함수 활용sumif 함수를 사용하면 간단하다.sumif 함수는 sumif(조건 찾을 영역, 조건, 합산 값 영역) 으로 정해진다.담당자열을 조건 찾을 영역으로, 조건에는 서준열, 합산 값 영역은 매출액 열로 정하면 끝이다.=SUMIF(C6:C10,"서준열",E6:E10) 1-2. 서준열씨의 매출액 합계 - sum + if 배열함수 활용배열함수를 쓰면 식을 간소화 할 수 있다.sum(if(조건),합산 값 영역) 아래와 같이 된다.{=SUM(IF((C6:C10="서준열"),E6:E10))}   1-3. 서준열씨의 매출액 합계 - sum 배열함수 활용.. 2016. 11. 24.
[엑셀] 다중조건에 대한 값 찾기(SUMPRODUCT, SUM배열) 다중조건에 대한 값 찾기(SUMPRODUCT, SUM배열)SUMPRODUCT와 SUM배열함수로 특정 값을 찾는 두개 방법은 그 사용법이 비슷하다. 대충 써보면함수명((조건)*(조건)*(찾을영역))이 되겠다. 단지, SUM배열은 엔터대신 컨트롤+쉬프트 엔터를 눌러서 배열함수로 만들어줘야 한다. 컴활1급을 준비한다면 SUM배열함수는 눈감고도 할수 있어야 하겠지만,회사 실무에서 배열함수를...... 아직은 써본 기억이 없다.     이해하기 보다 암기하자,나는 그랬다. 2016. 11. 24.
[엑셀] 다중조건에 대한 값 찾기(INDEX, MATCH, VLOOKUP, HLOOKUP) 다중조건에 대한 값 찾기(INDEX, MATCH, VLOOKUP, HLOOKUP) 예전에 어떤 블로그에서인가. 다중찾기 함수를 본적이 있다.기억을 더듬어 다시 적어봤다. 아래와 같은 조건이 있다고 하자,이 표 중에 서울지역의 학생수를 찾으려고 한다.   VLOOKUP과 MATCH함수를 사용해서 찾아보자** 컴활1급 시험을 준비한다면, 자다가도 벌떡얼어나서 쓸수 있을정도로 알아두는 것이 좋겠다. 그냥 글로 풀어보면MATCH함수로 조건2에 대한 열번호를 알아낸 다음, VLOOKUP의 열을 찾는데 쓰는 방법이다. MATCH함수는 사실 영역 내에서 값이 몇번째인지 나타내는 함수로,만약 혼자 엑셀을 상식선에서 쓰고 있다면 죽었다 깨나도 써먹을 일이 없을 함수이기도 하다.   MATCH(찾을값,배열,정확한값을찾을지.. 2016. 11. 24.
[엑셀] 숫자 한글로 바꾸기(계약서 금액 변경하기) NUMBERSTRING 함수 숫자 한글로 바꾸기(계약서 금액 변경하기) NUMBERSTRING 함수계약서는 왜 다 한글 아니면 워드로 되어있을까,간단한 계약서는 엑셀로 바꾸는 것이 여러모로 편하다. 특히 이 부분"일금 일백만원정" 처럼 숫자를 한글로 바꿔야 하는 건 정말 해도 해도 짜증난다. 간단하게 엑셀로 바꿔보자.   NUMBERSTRING 함수만 있으면 된다.=NUMBERSTRING(바꿀 값, 형식) 형식은 1, 2, 3 중에 고르자.(다른건 잘 안쓰게 된다)1 : 한글 #12 : 한자3 : 한글 #2 무슨 말인지는 그냥 그림만 보면 이해가 갈 거 같다."원정" 이라는 말도 아예 붙여 버렸다.   매우 쉽다. 2016. 11. 22.

티스토리툴바