컴퓨터를 만나는 기쁨

1. 문제 왼쪽에 담당자별 지역별 현황이 있는데, 이것을 오른쪽 표와 같이 담당자별로 지역별로 집계를 하려고 하는데, 특이한 점은 지역을 묶어서 집계한다는 것입니다. 이런 경우에 일반적으로 사용하는 것이 피벗 테이블이지만, 위와 같이 수원, 용인 경우의 합계를 구하려면 두 개의 값을 더할 수밖에 없습니다. 이와 같은 경우에 수원 또는 용인인 경우라는 조건을 넣어서 건수를 집계하고자 하는 것입니다. 2. 해법 1 : TextSplit 함수 이용 가. Sum + CountIfs 이용 TextSplit 함수는 Microsoft 365에서만 사용 가능한 함수이기 때문에 이보다 낮은 버전이라면 사용할 수 없는 제한은 있지만 이와 같은 경우에 매우 쉽게 처리할 수 있습니다. 다시 말해 수원, 용인을 TextSpli..

1. 문제왼쪽 키 값을 보면 A가 2개, B가 2개이고, D는 1개입니다.이와 같은 경우 2개 이상이면 하이픈에 순번을 연결해서 보여주고, 1개면 키값만 표시하도록 하려고 합니다. 2. 해법가. 함수 이용함수를 이용하면 간단하게 구할 수 있습니다.항목이 같은 것이 있는지 세서 2개 이상이면 하이픈에 순번을 연결하도록 하고, 1개면 키 값만 표시하도록 CountIf와 &연산자를 이용해 만들면 됩니다.=IF(COUNTIF(표2[키],표2[@키])>=2,표2[@키]&"-"&COUNTIF($A$2:A2,A2),A2)위 수식에서 표2[키]는 $A$2:$A$15이며, 표2[@키]는 A2이고,$A$2:A2라고 쓴 것은 A2셀은 고정하고, 아래로 내려가면서 범위가 늘어나도록 하기 위한 것입니다. 다시 말해, 한 칸 내..

1. 문제 아래와 같이 평가그룹별로 순위를 매기려고 하는데, 동점이 있을 경우 Rank.EQ함수를 사용하면 동일한 순위로 계산돼서 10행과 13행이 모두 3위입니다. 그리고, Rank.AVG 함수를 사용하면 순위가 평균돼서 3.5라고 표시됩니다. 그렇다고 Rank함수의 경우 2개의 조건을 입력할 수도 없습니다. 이 경우의 해결 방법을 알아보려고 합니다. 2. 해법 가. 논리 위의 경우 점수가 높은 것이 순위가 높기 때문에 자신보다 점수가 높은 것의 개수를 세면 자신이 최고 점수일 경우 0이 나오게 되므로 +1이 순위가 됩니다.. 그리고, 동 순위일 경우는 자신의 계량평가 점수보다 높은 것이 있다면 자신이 후순위가 되고, 없다면 1순위가 됩니다. 그리고, 평가그룹별로 구분해서 순위를 매겨야 하므로 평가그룹..

1. 문제고지서 서식은 한 장이고, 인쇄할 내용을 아래 명단 시트에서 순서대로 찾아서 인쇄하고자 할 때 먼저 생각나는 것이 워드나 한글의 메일 머지 기능입니다. 그러나, 여기서는 엑셀의 매크로로 구현하고자 하는 것입니다. 인쇄할 내용을 채우는 방법이 두 가지가 있습니다. 이름만 입력하면 나머지 내용은 수식으로 가져오는 방법, 이름뿐만 아니라 나머지 내용까지 매크로로 처리하는 방법인데, 한 가지씩 알아보겠습니다. 2. 해법 1 : 이름만 입력하고 나머지는 수식으로 가져오는 방법 가. 연번별로 이름에 순번 부여하기(1) 로직 연번을 기준으로 인쇄하는데, 연번에 동일인이 있다면 같은 고지서에 인쇄하고, 다른 사람이라면 별도의 고지서에 인쇄해야 합니다. 이를 위해서는 고지서 발행 기준인 순번과 동일인여부 순번 ..

1. 문제 아래와 같이 셀 병합이 됐을 때 셀의 길이(높이)를 알려면 어떻게 해야 할까요? 2. 해법 병합셀 다루기 정말 어렵죠? 가. 진행 주의 셀 수 알아내기 =OFFSET(A6,1,0)이라고 A6셀을 기준으로 한 행 아래 값을 구하려고 하면 "미 진행 주"가 아니라 A7셀이 돼서 0이 반환됩니다. 그러면 어떻게 해야 할까요? Match 함수를 이용해 "미 진행 주"의 위치를 찾은 다음 빼기 1을 하면 됩니다. 먼저 '미 진행 주'의 위치를 찾기 위해 =MATCH("미 진행 주",A6:A25,0)라고 하면 11이 구해지는데, 이것은 '미 진행 주'의 처음을 검색해서 그런 것이므로 1을 빼면 진행 주의 개수는 10이 됩니다. 나. 미 진행 주의 셀 수 알아내기 미진행 주의 개수를 알아내려면 A열에서 빈..

1. 문제 B열부터 D열까지 고객, 쿠폰, 전화번호를 연결하는데 전화번호가 1개라면 한번씩만 연결하면 되는데, 6행에서 8행까지는 전화번호가 같으므로 한번 고객, 쿠폰과 전화번호를 연결한 다음부터는 쿠폰 번호만 연결하려고 하는 것입니다. 경우에 따라 결합을 달리해야 하기 때문에 문제가 되는 것입니다. 2. 해법 1 : TextJoin 함수 적용 엑셀 버전이 TestJoin 함수를 지원하지 않는다면(안된다면) 3번을 참고 바랍니다. ​ 규칙이 고객, 쿠폰 번호, 전화번호를 연결해서 표시하는 것인데, 전화번호가 같으면 고객과 전화번호는 제외하고 쿠폰 번호만 연결하는 것입니다. ​ 따라서, 전화번호의 개수를 계산한 후 1개면 고객부터 전화번호까지 모두 연결하면 되고, ​ 1개를 초과하면 고객부터 전화번호까지를..

1편에서 만든 수식을 경우에 알맞게 조합하면 됩니다. 그리고, if문을 여러 번 중복해서 사용해야 하며, if문을 중복해서 사용한다는 것은 if함수 안에서 다시 if를 사용하는 것입니다. 예를 들어 아래 화면에서 AA6셀의 수식은 =IF(AA5=1,"1임",IF(AA5>1,"1보다 큼"))로서 AA5셀의 값이 1이면 "1임"이라고 표시(반환)하고, (1이 아니고) 1보다 크면 "1보다 큼"이라고 표시(반환)하는 것입니다. 위의 경우 AA5셀의 값이 2이므로 "1보다 큼"이라고 AA6셀에 값이 표시됩니다. 이런 식으로 2번뿐만 아니라 여러 번 if함수를 중첩적으로 사용할 수 있습니다. 1. 1월인 경우 1월인 경우 참인 값(value_if_true)에 계획과 수행이면 "완료", 아니면 "진행필요"라고 표시..

프로젝트별로 계획 대비 수행 실적을 비교해서 완료, 지연, 진행 필요, 미완료를 표시하려고 합니다. 날짜에 따라 진행 상태를 확인해야 하므로 B4셀에 기준일이 있습니다. 1. 경우의 수프로그램이든 엑셀이든 맞는 값을 찾으려면 모든 경우의 수를 찾는 것이 중요합니다. 위 경우에 먼저 1월과 2월 이후로 나눠야 합니다. 따라서, 1월인 경우 계획 대비 수행을 모두 완료했다면 '완료'이고, 계획 대비 수행을 완료하지 않았다면 '진행필요'가 됩니다. 그리고, 2월 이후는 기준 월에 계획이 있는지 여부에 따라 있다면 ① 기준월까지의 계획 대비 수행이 모두 됐다면 '완료'이고, ② 전월까지 계획 대비 수행이 모두 됐다면 '미완료'이고, ③ 전월까지 계획 대비 수행이 모두 완료되지 않았다면 '지연'이 됩니다. 없다면..

아래와 같이 성명과 점수가 있을 때 점수를 내림차순으로 표시하고, 점수에 해당하는 성명을 추출하는 것을 수식을 통해 해 보겠습니다. 1. 정렬 데이터탭에서 정렬 명령을 누른 후 정렬 기준으로 점수를 선택하고, 정렬 순서를 내림차순으로 지정하고 확인 버튼을 누르면 쉽게 구할 수 있습니다. 2. index와 Aggregate 함수 이용하기 먼저 Ctrl + Z키를 눌 정렬하기 이전 상태로 되돌립니다. 가. 필요성 Vlookup함수로 찾기 어려운 일치하는 값을 찾을 때 index와 match함수를 이용하는데, match함수는 일치하는 값 또는 유사한 값 하나만의 위치를 찾아주기 때문에 위와 같이 동일한 값이 여러 개 있을 때는 적용하기 어려운 점이 있습니다. 나. 논리 index 함수의 구문은 INDEX(ar..

다. 두 항목 이상으로 중복된 경우 첫 번째 값에만 서식 적용 ① 두 항목을 비교할 때는 별도의 열에 두 항목을 연결한 값을 먼저 만들어야 합니다. 두 항목을 연결할 때는 &(결합 연산자)를 사용하면 거래처와 품목을 연결할 때는 c3&g3 식으로 입력합니다. 아래 줄에도 적용되도록 M3셀의 채우기 핸들을 더블 클릭합니다. ② 두 항목을 비교해서 M열에 적용하는 것은 M3셀에서 M27셀까지 선택된 상태에서 조건부 서식 - 새 규칙을 누른 다음 '수식을 사용하여 서식을 지정할 셀 결정'을 선택하고, 수식에 =COUNTIF($m$3:m3,m3)=1 이라고 입력하고, 서식 버튼을 눌러 채우기 색으로 파란색을 지정하고 확인 버튼을 누릅니다. 그러면 아래 화면이 되는데, 다시 확인 버튼을 누릅니다. 화면을 보면 두..