본문 바로가기
[엑셀VBA] 중복값 처리 여러가지 방법 중 무엇을... 중복값 처리중복값을 확인하는 이유는 너무도 많기 때문에, 어떻게 처리할 것인지에 따라, 다양한 방법이 있으리라.아래와 같은 간단한 원본 데이터에 대하여, 자주 쓰는 몇가지 방법을 요약해 보았다.    COUNTIF아래 수식만으로도 중복값을 알아낼 수 있으며, 자기 자신을 배제해야하니, 결과값이 2이상인 경우 중복이라 볼 수 있다.중복된 수량을 직관적으로 알 수 있으며, 정렬을 통해 손쉽게 후처리가 가능하다.=COUNTIF($B$3:$B$11,B3)아래와 같은 식으로 결과값을 조정할 수 있고, 조건부 서식을 병행하여 색으로 구분할 수도 있다.=IF(COUNTIF($B$3:$B$11,B3)>1,COUNTIF($B$3:$B$11,B3)&"개 중복","유니크") 단점은 다른 열을 하나 더 사용해야한다는 점이겠다.. 2023. 2. 13.
[잡설] 파이썬보다 엑셀을 배워야 하는 이유 당신이 회사를 다닌다면, 엑셀을 배워야 하는 생각을 한번이라도 하게 된다.그 생각이 들었을 때, 직무가 무엇이든간에 어떤 방식으로든 배워두는 것을 추천한다.그 이유는 아래와 같다. 변화하지 않는다.변화무쌍한 개발 프레임워크들과는 다르게, 적어도 내가 직장생활을 했던 십여년간, 엑셀의 핵심은 바뀌지 않았다.아니, 그 어떤 프로그램보다 느리게 발전한다는게 맞겠다.게다가, 온라인으로 전환이 진행되면서, 오히려 기능을 라이트하게 바꾸는 느낌까지 든다.(협업방식이야 모두가 새로 배워야 하므로 논외로 한다.)   엑셀파일은 그 어디에나 있다.어떤 일을 하든 간에, PC를 사용한다면, 어떤 식으로든 엑셀파일을 보게 된다. 남이 만든 것이라도... 아무리 시스템화가 급격하게 진행되고 있어도, 과거 문서들이 이런식으로 .. 2023. 2. 10.
[엑셀VBA] 엑셀, 파워포인트 리본메뉴 작업시 아이콘(FACEID) 추가기능 작성 시...매크로를 열심히 만들고, 리본메뉴에 등록할 때, 아이콘이 필요하다.이 때, FACE ID 값으로 아이콘을 불러오는데, 아이콘 값을 모르면, 아무 숫자나 넣어볼 수 밖에 없다. 아래 사이트에서 FACE ID 값을 확인할 수 있다.   VBA RIBBON FACE ID(ICON) 확인하기 언제 없어질지 모르므로, 나도 하나 카피본을 가지고 있어야겠다. 2023. 1. 16.
[엑셀] 파워쿼리 - 2019버전 열피벗해제 문제의 시작아래와 같은 테이블이 있다고 하자. 이 테이블이 만약 정제된 결과라면 훌륭하겠으나, raw데이터라면 큰 문제를 가지고 있다. 글로 설명하기는 힘들지만, 예를 들어 이 테이블을 가지고 2차 가공하여 추가 요약 테이블을 만들어야하는 경우, 큰 어려움이 발생한다. 이는 컬럼레이블이 "월"이라는 레이블 아래 값으로 들어가야 하는 놈들이 들어가 있기 때문이다.;;;(뭔말..? 유식하게 표현하지 못하겠다.) 이 때문에, 추가 요약 테이블을 만들 때 수식이 복잡해진다는 뜻이다.   파워쿼리아직 파워쿼리를 자유자재로 사용할만큼 실무단계에서의 데이터가 복잡하지는 않았기에, 대부분 위에 기술한 상황에서 전처리를 위한 도구로 사용하고 있다. 이는 딱 버튼 하나면 된다. 일단 해당 테이블(범위)을 파워쿼리로 불러온.. 2022. 12. 20.
[엑셀VBA] 날짜간 차이 표시(datediff) 첫번째 날짜 : 오늘날짜두번째 날짜 : 원하는 날짜첫번째 날짜와 두번째 날짜의 차이를 구하고 싶다면 아래와 같은 수식을 작성할 수 있다. Sub datediff_func()    Dim date1 As Date    Dim date2 As Date    date1 = Now 'now    date2 = #1/15/2023# 'end date of service        Debug.Print DateDiff("d", date1, date2)End Sub DateDiff의 첫번째 argument는 interval로서, 아래와 같은 옵션이 있고, 쌍따옴표를 써서, String type으로 기재를 해줘야 한다.    더 구체적인 설명은 아래 도움말을 참고할 수 있다.  DateDiff function (Vi.. 2022. 12. 19.
[엑셀] 셀 값(텍스트) 배열로 만들기 엑셀의 배열은 대부분 영역(RANGE)을 기준으로 생각하는 것이 일반적이다. 문득 한개의 셀 안에 들어있는 텍스트 값에 대해서 배열로 만들 수 있을까 궁금해졌다. 이는 일반적인 코딩에서는 리스트화 시키는 것이 일반적인데, 엑셀에서는 문자열 나누기라는 초강력한 기능이 있기 때문에 잘 사용되지는 않는 듯하다. 각설하고 아래 수식들을 이해해 보자. 셀 안의 글자(텍스트) 길이대상텍스트를 아래 주소라고 하자. 해당 값을 영역 "대상텍스트"로 설정하고, 글자 길이를 세어보자. LEN 함수를 사용하면, 결과는 17로 띄어쓰기를 포함하여 총 17자라는 것을 알 수 있다.    글자 길이만큼 배열 만들기ROW와 INDIRECT 함수로 대상텍스트의 길이에 해당하는 배열을 만들 수 있다. 즉 {1;2;3;4;~~~~17}.. 2022. 12. 15.
[엑셀VBA] 셀 행/열 너비/높이 복사하기 셀 너비/높이 복사하기표를 이리 저리 복사를 하다보면, 셀의 너비와 높이가 엉망이 될때가 있다. 적어도 너비는 복사할때 옵션 중에 선택하는 항목이 있다고 하지만, 높이는 그때 그때, 높이를 확인해서 바꿔줘야 한다.    단순하게, 선택영역의 높이와 너비 값을 동일하게 복사해주면 좋겠다 싶었다. 단, 두개의 영역을 선택해야하는데, 원본과 대상이다. 이 또한 귀찮다고 하면 할 말은 없다. 일단 원본 테이블 영역을 복사한다.    다음, 대상영역을 선택해준다.    여러가지 선택옵션이 있으면 더 귀찮아지기 때문에, 여기까지만 했다.    막상 사용자 폼 버튼에 등록하려고 하니, 귀찮음이 엄습한다. 몇개 몰아서 한꺼번에 해야겠다.  Sub copyWidthHeight()Dim rArea As RangeDim t.. 2022. 12. 6.
[엑셀] 날짜에 따라 테이블 색 변경하기 / WBS 서식 만들기 날짜 수식은 괴롭다엑셀이든 DB든 날짜를 계산하는 작업은 짜증나는 작업이다. 사무직이라면 반드시 한번은 보게되는 WBS, 프로젝트 관리 툴이 엄청나게 많아졌지만, 아직 WBS는 역시 엑셀이다. WBS를 여러번 만들다보면 날짜 수식을 잘하게 되는 날이 올것이다. 흔해 빠진 WBS 모양은 아래와 같을 것이다. 시작일과 종료일을 기준으로 기간을 적고, 일자대로 색을 칠해야한다.두가지 짜증나는 작업이 동반되는 것을 예상할 수 있다._시작일과 종료일 사이 기간에 해당하는 날짜별에 색칠을 해야한다._칠해준 기간에서 주말을 빼줘야하고, 공휴일도 빼줘야한다. 아래 그림은 대략 완성된 이미지이다. 주말은 회색처리를 했으며, 친절하게 오늘 날짜에는 빨간색 하일라이트도 넣어줬다. 살짝 애매한건 공휴일이다.    기간 내 날.. 2022. 12. 5.
[엑셀VBA] 참조표(테이블)을 이름정의에 사용하기 위한 배열로 변환 얼마 전에, 이름정의에 배열로 참조표(테이블)을 넣어서, 사용하는 방법에 대한 글을 적은 적이 있다. 한번 해놓으면 편하게 사용할 수 있지만, 단점은 아무래도, 따옴표와 컴마, 그리고 세미콜론까지 입력이 용이하지 않은 창에 수기로 타자를 치기가 어렵다는 점이겠다.   [엑셀] 등급 찾아서 넣기 : 이름정의에서 배열 사용#2(VLOOKUP)이름정의에서 배열상수를 넣어서, 별도의 참조테이블을 사용하지 않는 방법을 얼마전 글에서 적은 적이 있다. 이를 사용하여 점수별 등급을 손쉽게 넣을 수 있다. 이는 VLOOKUP의 마지막 상수(RANGEsunnybong.tistory.com  VBA로 간단하게 테이블을 배열 텍스트로 변환하는 코드이다.테이블 영역을 선택하면 txt파일이 생기고, 이를 클립보드에 복사하는 과.. 2022. 11. 9.
[엑셀] 점수 구간별 등급, vlookup의 range_lookup 옵션으로 쉽게 설정하기 점수나 포인트 구간이 있을 때, vlookup으로 해당 점수에 대한 구간을 구하는 방법을 알아본다. 어렴풋이는 알아도 쓸때마다 좀 해깔려서 정리를 해둔다. 아래와 같은 기준 테이블이 있다. 60점 미만은 과락, 80점 미만은 보통, 100점 미만은 우수, 100점은 최우수인 기준이 있다고 하자.    vlookup에서 참조할 테이블로 기준을 다시 정리했다. 여기서 아주아주아주 중요한 점은 점수가 오름차순으로 정렬되어있어야 한다는 점이다.    vlookup함수를 적용한다. 대부분의 경우에서 마지막 lookup_range 변수에 false값을 넣는데, 구간을 찾을 것이기 때문에 TRUE 값을 넣어준다. 그럼, 순차적인 구간으로 처리가 된다. 해당 변수는 optional 이기 때문에, 생략을 할 경우, 기본.. 2022. 11. 9.
[엑셀VBA] 정의된 이름 편하게 확인하기 이름정의로 정의된 이름 확인하기이름정의 기능은 잘쓰면 아주 유용하지만, 어설프게 사용한다면, 꼬여서 상당히 골치가 아파질 수 있는 기능이다. 정의된 이름 관련 리본 메뉴는 수식 탭에서 확인할 수 있다. 직관적으로 찾기 좀 애매한 위치에 있다.   정의된 이름 관리정의된 이름은 "수식에서 사용" 버튼을 통해 수식안에 넣을 수 있고, "이름관리자"에서 편집 혹은 삭제가 가능하다.    또는, 주소창에서 등록된 이름을 확인, 이동할 수 있고,   F5키를 눌러 해당 영역으로 이동할 수도 있다.   정의된 이름 좀 편하게 확인 가능?그럼에도 이름이 여러개 만들어지다 보면, 프로그래밍하다가 변수명이 뭐였는지 잘 기억이 안나는 것처럼, 꼬이게 된다.그래서 아래와 같이 등록된 이름을 확인할 수 있는 창을 만들어 보았.. 2022. 11. 7.
[엑셀VBA] A4사이즈로 시트 만들기 워드로 엑셀 쓰기신나게 표를 만들고 나서, A4로 출력을 하려고 하면, 원치 않는 사이즈로 나올 때가 있다. %로 조정해서 사이즈를 맞출 수는 있지만, 그러다 보면 글씨가 작아지는 등 시행착오를 동반하게 된다. 시작하자마자, 100% 비율의 A4 사이즈를 맞춘다면 좋을 것 같다. 특히 셀병합을 하기 위해서 적당히 작은 셀들로 구성이 되어 있으면 좋겠다. 이를 위한 매크로를 짜서 실행하면 다음과 같이 설정된다.A열부터 좀 넉넉하게 Z열까지 열 너비를 적당히 줄이고, 용지 사이즈는 A4, 세로로 설정했다.    여백은 "좁게" 옵션과 비슷하게 설정했다.    가장 외곽에 테두리를 치고, 인쇄 미리보기를 하면 다음과 같다.    사용된 코드는 다음과 같다. Sub seta4()Dim rng As rangeDi.. 2022. 10. 13.

티스토리툴바