취미로 엑셀하기

안녕하세요 오랜만에 엑셀 관련 포스팅이네요 이번에 일을 하다 보니 100만 줄이 넘어가는 실제로는 1900만줄짜리 데이터를 활용하게 되었는데요 100만줄이 넘어가는 데이터는 실제 엑셀로 열리지도 않더라고요...... 정확히는 워크시트 한계가 사실 105만 줄인데 그 이상 데이터는 불가능합니다... 임의로 메모장으로 만든 120만 줄 데이터를 열어보니 저렇게 열리고 한계를 넘어가는 데이터는 관리가 불가능합니다. 실제 업무에서는 1900만 줄 데이터를 분석을 해야 했거든요.. 그래서 기능을 찾아보니 파워 피벗이 있더라고요(저는 엑셀 365 사용 중입니다) 엑셀의 컴 추가 기능이며 설치는 다음과 같이 했습니다. 왼쪽 상담 파일-> 옵션-> 추가 기능-관리-COM추가 기능-> 이동 확인을 누르면 파워 피벗 기능..

안녕하세요 오늘은 정말 간단한 엑셀 팁입니다. 엑셀을 잘 사용하시는 분들 일 하실때 보면 키보드 방향키만 가지고 화면이 휙휙 이동하는 걸 보실 수 있습니다. 워크시트 안에서 그렇게 이동하는 경우는 컨트롤키+방향키 를 누르신겁니다. 예를들어 지금 선택된 셀이 A1인경우 여기서 컨트롤+아래 방향키를 누르면 정말 제일 밑에 있는 셀로 이동합니다. 이런식인거죠 오른쪽 키도 마찬가지 입니다. 그런데 이렇게 값이 들어가 있는 표 B2에서 컨트롤+아래 키를 누르면 값이 쭉 있는 셀중에 제일 아래인 B7셀로 이동하는걸 볼 수 있습니다. 이렇게 말이죠 만약 표가 정말 길게 있었고 한번에 표의 처음이나 끝 부분으로 이동하는 경우에는 컨트롤+방향키가 정말 편리한 기능인거죠 비슷한것으로 워크시트를 막 넘어다니는 방법도 있습니..

안녕하세요 오랜만에 엑셀 팁을 올리게 되었네요 일을 하다보면 동일한 내용을 두번씩 써야 할때가 있습니다. 예를들어 안녕 하세요 나는 직장인입니다. 라고 4줄이 써져있을 때 안녕 안녕 하세요 하세요 나는 나는 직장인입니다. 직장인입니다. 이렇게 연속되게 써야 할 때가 있어요 막상 해볼려니 함수로 쓸만한게 있나? 라는 생각이 들었는데 생각보다 간단하게 해결이 되더라구요 예시를 보시죠 이렇게 여러 행의 내용이 있을때 이걸 중복해서 두줄 씩 써보고 싶을때가 있죠 그럴때는 아래와 같이 현재 있는 내용에다가 연속된 숫자를 적어줍니다. 그리고 같은 내용을 아래로 한번 복사해 주는거죠 그리고 나서 필터를 걸어서 오름차순 정렬을 합니다 그럼 짜잔 하고 이렇게 만들어집니다. 그리고 만들었던 숫자 필터는 지워버리면 되는거죠..

안녕하세요 또 오랜만에 블로그에 글을 올리게 되었네요 이번은 진짜 제가 일을 하면서 아 이게 이렇게 하면 되는구나 하고 알게된 팁입니다. 저보다 잘 아시는분들은 이미 쓰고계셧을 함수지만 저는 막상 하려니까 당황스럽더라구요 데이터가 약 만개쯤있는데 그룹별로 1,2,3,4....이렇게 순번을 매겨야 했습니다. 묶을 그룹 자체가 10개 미만이면 그냥 오름 혹은 내림차순 정렬을 해놓고 1,2,3, 쭉 긁어버릴텐데 그룹이 100개가 넘어가니까 이거 너무 노가다 인게 생각이 들더라구요 아래의 예시를 보시면 제가 임의로 만들 목록입니다. 장난감의 1,2,3,4,6 가수의 1~10 뭐 이런식으로 순번을 매기는거죠 저정도 목록이면 그냥 해도 됩니다. 근데 목록과 데이터가 훨씬 많을 경우는 노가다 할수는 없죠? 아래처럼 ..

안녕하세요 취미로 엑셀하기 블로그입니다 2019년 첫 포스팅이네요 새해에 엑셀로 생일이나 일정 관리를 하다보면 엑셀 날짜에서 월일 정보만 뽑아서 쓸 일이 생깁니다. 예를들어 2019-02-01 이라는 날짜가 있을때 아 나는 0201만 뽑아서 쓰고싶은데 어떻게 변환하지? 라고 생각이 들때가 있는데요 그럴때는 다음과 같이 if 함수와 month함수, day함수를 이용하면 쉽게 뽑아낼 수 있습니다. 예를들어 A4 셀에 있는 날짜를 mmdd 형태로 바꾸고 싶을때 걸어놓은 수식인데요 수식을 자세히 살펴보면 다음과 같습니다. =IF(MONTH(A4)

엑셀에서 수식하고 관련도 없고 그림도 아니여서 지워지지 않는 화살표가 보일때가 있습니다. 오늘 말씀드릴 내용은 참조하는 셀 추적 참조되는 셀 추적 입니다. 먼저 아래 그림을 보시면 장려금 비율 밑에 파란색 칸에서 여러 파란색 줄들이 뻗쳐져 있는걸 볼 수 있습니다. 이것은 E18칸에서 위쪽에 참조하는 셀 추적 키를 누른 상태입니다. 즉 현재 파일안에서 함수로든 어떤 식으로든 E18의 값을 가져다가 쓴 모든 셀이 어디있는지 화살표로 보여주라는 뜻입니다. 그럼 참조되는 셀은 어떤식으로 표기가 될까요 일단 D7칸을 한번 눌러줍니다. 그리고 참조되는셀을 누르면 다음과 같은 화살표를 볼 수 있습니다. D7셀에 있는 함수 수식은 C7, C18, C19, E18, E19를 값으로 가져다가 썻습니다. 이처럼 참조하는셀과..

이번 포스팅은 정말 간단한 포스팅입니다. 엑셀 하시다보면 주소록 같은걸 정리하실때 이름 뒤에 '님' 혹은 '선생님' 등 수식어를 붙여야 할때가 있습니다. 근데 이름을 일일히 수정하면 정말 시간이 오래걸리는데요 이때는 & 를 사용해서 편하게 할 수 있습니다. 먼저 위와 같은 예시를 만들어 보았는데요 아래처럼 아무데나 일단 붙이고 싶은 글자를 써주도록 합니다. 여기서는 님 이라는 글자인데요 그리고 함수를 걸어주도록 합니다. =B5&$E$5 어떤 뜻이냐면 B5셀에 있는내용 &(앤드) E5에 있는 내용 붙여서 보여줘라 라는 뜻잇입니다. 함수를 건다고 저거를 직접 타이필 하실필요는 없고 = 누르고 홍길동 칸 누르고 & 써주고 님 글자를 누르고 F4를 한번 눌러주세요 F4를 누르면 E5가 $E$5로 변하는데 이는 ..

엑세로 이거저거 작업을 하시다보면 아래처럼 필터를 걸어놓고 합계를 바로바로 봐야할 때가 자주 생깁니다. 근데 SUM 함수로 수식을 걸어놓거나 엑셀에 합계 버튼으로 합계 식을 걸어놓으면 아무리 필터링을 해도 안보이는 값까지 전부 다 더해서 보여줘 버려서 이럴때는 어떡하지 라는 생각이 들 때가 있습니다. 그럴때 사용하는게 subtotal 함수입니다. 먼저 아래 예시에서 나는 콜라의 단가 합만 보고 싶을때 예시를 들어 보았습니다. 분명 데이터 제일 위쪽에 있는 항목에 필터를 걸으셨을겁니다. 그리고 서브토탈과 섬 함수를 다 걸어보죠 이때까지는 필터된 값이 따로 없기때문에 서브토탈과 섬의 값이 같습니다. 다만 아래처럼 품명에 콜라만 선택을 했을경우를 보면 이런식으로 서브토탈함수는 필터링된 콜라의 단가 합계만 보여..

오늘은 엑셀로 간단하게 단가표를 만들어서 한눈에 제품별 단가가 변했을 경우 볼 수 있도록 만들어 보겠습니다. 먼저 위와 같은 자료가 있을때 아 이거를 좀 한눈에 보이게 만들수는 없을까? 라는 생각이 들때 사용하는 방법인데요 일단 피벗테이블을 사용하는 방법과 엑셀 함수를 사용하는 방법 이렇게 두가지가 있는데 피벗테이블을 이용해 보도록 하겠습니다. 주의할 점은 지금 만드는 자료는 월별+제품별+용량별 단가이기 때문에 같은 월,제품명,용량에 두가지 단가가 있어서는 안됩니다. 그렇기 때문에 꼭 중복체크를 하고 하셔야 합니다. (같은 조건에 단가가 여러개면 그냥 평균단가를 쓰는게 더 맞을것입니다) 중복 체크 방법은 제 블로그에 이름+생년월일로 명단 중복체크 하기 편을 보시면 되겠네요 자 그럼 피벗테이블 만들어 보겠..

매출액에 따른 장려금 계산하는 방법에 대해 알아보자. 사실 엑셀로 일을 하다가 가장먼저만나고 가장 자주 쓰는 함수는 vlookup 다음이 이 if 함수일 것이다. if 함수는 구조가 간단해서 쓰기가 참 좋은데 아래처럼 장려금 계산식을 만들다 보면 if함수 안에 if함수를 써야하는 즉 if 함수를 중첩해서 써야하는 경우가 꽤나 발생한다. 이때 if 함수를 연속해서 쓰는거를 잘 못하시는 분들을 위해 이번 포스팅을 준비했다. 아래 예시를 보자 위에 보면 구조는 다음과 같다 1월 매출액이 위와 같을때 장려금액을 아래 기준에 따라 계산하는 것이다. 스킨하이 라는 거래처라 125만원의 매출이 나왔을때 장려금은 2%니까 2만5천원이된다. 이제 if 함수의 구조에 대해 보면 다음과 같다 =IF(B6>=$B$17,B6*..