본문 바로가기

Excel

Excel Q&A #2. 수식을 이용한 조건부서식 엑셀의 조건부서식은 말그대로 "조건에 따라 서식을 지정"해주는 기능이다. 그래서 영어로는 Conditional Formatting. 여러가지 규칙 유형을 선택할 수 있고, 그 중에서 "수식을 사용하여 서식을 지정할 셀 결정" 유형에 대해 설명한다. 이 유형은, "참을 반환하는 수식일 경우에만 서식을 적용"시킨다. 당연한 말이면서도 굉장히 중요한 개념. 먼저, Boolean의 개념을 알아야한다. 흔히 우리가 알고있는 TRUE(참), FALSE(거짓) 을 의미한다. 따라서, 저 수식입력란에는 "결과가 TRUE 혹은 FALSE로 반환되는 수식을 넣어야한다" 질문으로 돌아가서, 질문자는 조건1. 업체가 "삼성" 혹은 "엘지" 이면서, 조건2. 보증일이 365일이 지난경우 서식을 바꾸고 싶어하므로, 조건1 → G.. 더보기
엑셀 반응형 하이라이트 셀 구현 가끔 복잡한 데이터들을 검토해야할 때가 있다. 특히 칼럼(열) 갯수가 많은 데이터는 내가 보고있는 행을 헷갈릴때가 많은데, 그럴때 편하게 데이터를 보시라고 만든 하이라이트셀. 우측 Ctrl 키(어따 쓰는겨...) 를 토글키로 하이라이트를 껐다 켜는키로 설정해두었다. 데이터의 범위를 자동으로 인식하고 해당 데이터의 어느부분을 누르든 해당되는 행을 하이라이트한다. 엑셀의 조건부서식을 이용해서 생성하고 삭제하는 방식으로 코딩했다. 그러므로 원래 있던 배경색상에는 영향을 미치지 않는다는 것. 웜마.. EXE 안올라가네.... 더보기
네이버메일 전송 자동화 고객관리 차원이든, 주기적인 보고메일이든 우리 일상에서는 반복적인 이메일 전송으로 소통하는 경우가 잦다 보냈던 내용을 약간만 수정해서 주기적으로 보내야한다던가 혹은, 비슷한 서식의 메일내용으로 여러 사람에게 보내야하는 상황들이 있다. 메일 본문에서의 변수값을 엑셀의 셀로 구분지어 DB화하고, 각각의 변수값을 수식화하여 하나의 메일 본문으로 만들어 준 뒤, 짜여진 스크립트를 실행하면, 자동으로 메일 전송되는 프로그램을 만들어 봤다. 메일 본문 뿐만 아니라, 파일첨부도 자동으로 할 수 있다. 1. 네이버메일 SMTP 사전세팅 SMTP(Simple Mail Transfer Protocol)는 이메일을 보내는데 사용되는 프로토콜이다. 이 설정을 사전에 세팅해줘야, 코딩을 통해 이메일서버에 접근할 수 있는 권한을.. 더보기
대시보드 무작정 따라하기 #2 집중할 수 없었던 시간 동안 짬나는대로 아무 생각 없이 진행할 수 있는 카피를 해보기로 했고, 오랜만에 대시보드를 만들어보기로했다. 엑셀로 실현가능한 가장 완벽하고 지속가능한 보고자료가 갖춰야할 것들. 1. 데이터 수집의 자동화 2. 데이터 전처리의 자동화 3. 목적에 맞는 대시보드 구현 이 세가지라고 생각한다. 유의미한 것들을 나타내기 위한 자료들을 분석하기 위해 방대한 데이터를 자동으로 수집하는 크롤링기법. 수집한(수집되고 있는) 데이터를 시계열로 정리하고, 분석을 용이하게하기 위한 서식으로의 전환을 의미하는 전처리. 그렇게 수집되고 편집된 자료들을 동적으로 연결시켜, 시각적효과로 보여주기 위한 대시보드. 모든 과정이 중요하고, 유의미 하다. 대시보드는 사실 노가다스러운 부분이 많아, 손이 잘 가지 .. 더보기
Excel Q&A #1. 다중IF 수식의 다른 접근방법 오늘부터 간간히 사람들이 물어보는 것들중 의미 있는것들을 풀어보고 해석해보는 포스팅을 올려보고자 한다. (게을러서 포스팅 주기는 장담 못함 ㄷㄷ) 대망의 첫 질문 내용은 다음과 같다. 정리하자면, 값의 레인지 조건범위에 따라 해당하는 값을 나타내기 위해 IF함수를 복합적으로 사용한 사례.. 개인적으로 조건 세개가 넘어가면 무조건 표로 먼저 정리해보는걸 추천한다. 물론 다중 IF 문으로도 해결이 안되는건 아니지만, 유지보수가 어렵고 논리적으로 실수할 가능성이 높아진다. 조건에 비어있는 값이 있을 수 있다는 얘기다('MECE 하지 않다'라고도 표현함). 따라서 다음과 같이 표로 정리했고... 해당 조건에 따라 VLOOKUP 함수로 수식을 생성했다. 첫번째 인수(찾을 값)는 J3셀(500) , 두번째 인수(참.. 더보기
엑셀 365 신규 베타함수 리뷰 ** 본 포스팅은 엑셀 구독형 365 이용자를 위한 포스팅이며, 그 중 베타 채널에 가입된 이용자들을 위한 글입니다. 우선 구독형 엑셀인 365 이용자라면, 베타채널에 가입해보자. https://m.blog.naver.com/lsw3210/222179539328 Office 참가자(Insider) 베타 채널(테스터)로 참여하기 최신 기능을 사용하겠다고 신청했는데도 새로운 기능, 함수 등을 사용할 수 없어서 살펴보니, 업데이트 버... blog.naver.com 다른 분이 올린 포스팅을 참고.. 베타 채널 테스터로 가입하게 되면, 새로운 신규함수들을 사용할 수 있게 된다. VSTACK, HSTACK, TOCOL, TOROW, TAKE, DROP, LAMBDA.. (더 있나..?) 대체로 배열을 컨트롤하기 .. 더보기
VLOOKUP, INDEX & MATCH 함수 총망라(고급 활용편) 많은 분들의 이야기를 들어보면 VLOOKUP 함수와 SUMIF / AVERAGEIF 함수 수준을 자유롭게 구사하느냐, 아니냐가 엑셀을 잘 다루느냐 아니냐를 판단하는 기준이 되는 것 같다. 실제로 VLOOKUP 함수는 실무에서 쓰는 엑셀 활용도 측면에서도 빈도수가 상당히 높은 함수이다. 기본적인 기능이나 사용법은 여타 다른 자료에서도 쉽게 찾아볼 수 있으니, 본 포스팅에서는 더 깊은 이야기를 해볼까 한다. VLOOKUP을 자주 접하는 분들에게도 항상 아쉬운 부분들이 있기마련이다. 내가 생각하는 그 한계성에 대해서 아래에 간단하게 3가지 정도로 정리해 보았다. VLOOKUP 함수의 한계 조회하고자 하는 데이터열보다 좌측에 위치한 값을 조회할 수 없음 다중 조건을 만족하는 값을 찾기 어려움 중복된 값이 있다면.. 더보기
엑셀 365함수 모듈 365버전에서만 쓸 수 있는 함수를 사용할 수 있게 해주는 사용자함수 모음툴이라고 생각하면 된다. 탐색기 열고 주소창에 %APPDATA% 라고 쓰고, 마이크로소프트 → 엑셀 → XLSTART 폴더 안에 넣고, 엑셀 재시작하면 적용된다. 제공되는 함수는 다음과 같다. XLOOKUP XMATCH aFILTER RANDARRAY aSEQUENCE aSORT SORTBY UNIQUE CONCAT IFS MAXIFS MINIFS SWITCH TEXTJOIN aSINGLE 이 사용자함수 모듈을 직접 만들어 낸 것은 물론 아니고, 어느 외국형이 만든거같음 (...) 모두 365함수와 같은 기능을 하지만, 앞에 a가 붙은 일부 함수들은 같은 이름으로 함수명을 지정했을때 오류가 나는 것 같다. 배열을 필요로 하는 함수들.. 더보기

티스토리툴바