컴퓨터를 만나는 기쁨

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. 문제 아래와 같이 직원별, 일자별 근무 현황이 있는데, 근무 유형별, 일자별로 누가 근무했는지와 시간외근무를 오른쪽 '시간외근무 유형'에 따라 괄호 안에 시간수를 입력해서 표시하려고 하는 것에서 앞의 문제는 간단하니 넘어가고 뒷부분의 문제만 365 버전용 함수를 이용해 풀어보겠습니다. 2. 해결 방안 1 Scan 함수와 IFs 함수 등을 이용해 줄 별로 결과를 추출하는 수식을 만들어 봤습니다. ​ O2셀 : =TEXTJOIN(",",,SCAN("",OFFSET($B$3:$B$8,,ROWS(C$3:$C3)-1),LAMBDA(a,b,IF(LEFT(b,3)="시간외",OFFSET($A$3,ROW(b)-3,0),"")&IFS(b="시간외1","(1)",b="시간외2","(2)",b="시간외3","(2)",..

1. 문제 왼쪽에 데이터가 있고, 오른쪽에 prc_name과 eq.no가 일치하는 날짜를 찾는 것입니다. 2. 여러 가지 해결방안 prc_name과 eq.no에 해당하는 값을 찾은 다음 offset 함수를 이용해 오른쪽으로 다섯 번째 날짜를 찾을 수도 있고, index 함수를 이용해 날짜 범위를 지정한 후 행과 열 수를 지정해서 원하는 값을 구할 수도 있습니다. 3. Offset 함수 이용 가. Offset 함수의 구문 OFFSET(reference, rows, cols, [height], [width])로 reference는 참조 셀 rows는 이동할 행 수로 +면 아래, -면 위로 이동하고, 0은 현재 위치를 가리킵니다. cols는 이동할 열 수로 +면 오른쪽, -면 왼쪽으로 이동하고, 0은 현재 위..

1. 문제 아래와 같이 일별 데이터가 있는데, 1월은 말일까지 있지만 4월의 경우는 2023/4/30일이 일요일이라 근무일인 4/28까지의 데이터만 있는 특이점이 있습니다. 따라서, 단순히 EOMonth로 말일을 구할 수 없는 한계가 있으며, 5월은 2일까지뿐이 없어서 월말이라고 볼 수가 없습니다. 이와 같은 경우 해결책에 대해 알아보겠습니다. 2. Scan 함수의 구문 Microsoft 공식 홈페이지의 도움말을 보면 =SCAN ([initial_value], array, lambda(accumulator, value)) 라고 되어 있는데, lambda의 인수로 세 번째에 Calculation이 있어야 하는데 누락된 것 같습니다. ​ 맞는 구문은 =SCAN ([initial_value], array, l..

1. 문제 아래와 같이 전기 재고가 있고, 입고와 출고가 되었을 때 먼저 들어온 것이 먼저 나가도록 한 후 재고를 구하는 것이 선입선출법(FIFO : First In First Out)입니다. 입고 순서는 아래와 같습니다. 예를 들어 A품목의 경우 전기 재고가 20개 있고, 출고가 140개 되었으므로 입고 물량을 120개 차감해야 하는데, 선입선출이므로 1월 물량부터 순서대로 차감합니다. 1월 물량이 20, 2월 물량이 30, 3월 물량이 30, 4월 물량 30을 더하면 110이 되므로 5월 물량 20개 중 10개가 남고, 6월 물량 10개는 모두 남아서 재고 금액은 5월 10개 * 53 + 6월 10개 * 54 = 1,070이 됩니다. 이와 반대되는 것으로 나중에 들어온 것부터 차감하는 것을 후입선출법..

1. 문제 D열의 값과 O열의 월에 해당하는 값, 다시 말해 2024/01은 E열, 2024/02는 F열의 값을 곱한 다음 그 합을 구해서 P열에 입력하는 것입니다. P5열에 예시로 든 수식은 =(D5*E5)+(D6*E6)+(D7*E7)+(D8*E8)+(D9*E9)로 전형적인 SumProduct 함수의 사용 예입니다. 2. SumProduct 함수 SumProduct 함수의 구문은 =SUMPRODUCT(array1, [array2], [array3],...)으로 곱할 값들의 범위 또는 배열을 1개 이상 입력하는 것입니다. 위와 같은 경우 array1은 D5:D9가 되고, array2는 E5:E9가 됩니다. P4셀에 =SUMPRODUCT(D5:D9,E5:E9)라고 입력하면 =(D5*E5)+(D6*E6)+(..

1. 문제 A셀의 문자 길이가 4인 경우 B셀의 값을 합계하는 것입니다. 2. 해결 방안 다양한 해결방안이 있을 수 있습니다. ① A열의 코드를 살펴보니 A3세의 값이 5116인 경우 A4셀부터 5116으로 코드가 시작되므로 4개를 잘랐을 때 코드 값이 같은 것을 더할 수도 있고, ② 코드 길이가 7자리에서 4자리로 바뀔 때, 다시 말해 코드의 길이가 4인 것 전까지 더할 수도 있습니다. 그리고, 함수도 Sum과 If 배열 수식을 이용할 수도 있고, SumIf를 이용할 수도 있고, sumProduct, 그리고, Offset과 Match함수를 이용할 수도 있습니다. (1) Sum과 If 배열 수식을 이용하는 경우 =SUM(IF(LEFT($A$4:$A$24,4)=A3,$B$4:$B$24,0)) A열의 왼쪽 ..

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월 이후는 기준 월에 계획이 있는지 여부에 따라 있다면 ① 기준월까지의 계획 대비 수행이 모두 됐다면 '완료'이고, ② 전월까지 계획 대비 수행이 모두 됐다면 '미완료'이고, ③ 전월까지 계획 대비 수행이 모두 완료되지 않았다면 '지연'이 됩니다. 없다면..