컴퓨터를 만나는 기쁨

1. 문제 아래와 같이 이름과 값이 자료가 있을 때, 이름에 해당하는 값의 합계를 구하려고 합니다. 2. 해법 1 위와 같은 표에서 이름이 홍길동이거나 장발산에 해당하는 값의 합계를 구하려면 =SUMPRODUCT(($A$3:$A$7=D3)+($A$3:$A$7=D4),$B$3:$B$7) 라고 입력해서 합계를 구합니다. 위 수식을 보면 ($A$3:$A$7=D3)+($A$3:$A$7=D4)라고 ($A$3:$A$7=D3)과 ($A$3:$A$7=D4)가 +로 연결되어 있는데, 이것은 두 가지 조건 중 하나만 일치해도 된다는 Or 조건입니다. 다시 말해 위 수식은 이름이 홍길동이거나, 이무인 경우가 됩니다. 그리고, 값의 범위 $B$3:$B$7에서 조건에 맞는 값을 찾아 합계를 구하는 것입니다. 이때 쉼표(,)를 ..

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

1. 문제 오른쪽 기준 표의 제품별, 잔량별 생산일수보다 왼쪽의 생산일수가 크다면 확인란에 "불량"이라고 표시하고, 아니면 공란으로 내버려 두려고 합니다. 2. 해법 여러 가지 함수로 해결할 수 있습니다. 가. Index + Match 함수 (1) 잔량을 0과 "있음"으로 구분하기 오른쪽 기준표를 보면 제품이 같더라도 잔량이 0인지 아닌지에 따라 생산일수가 다르다는 것을 알 수 있습니다. 그런데 왼쪽 표를 보면 잔량이 숫자로 표시되어 있으므로 두 개를 Match 하려면 왼쪽 표의 잔량 수치를 0과 "있음"으로 바꿔줘야 합니다. 따라서, if 함수를 이용해 0보다 크다면 "있음" , 0이면 0이라고 표시하면 되므로 수식은 =if(d3>0,"있음",0)이 됩니다. (2) 왼쪽 제품과 잔량에 해당하는 생산일수..

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. 문제 아래와 같이 직원별, 일자별 근무 현황이 있는데, 근무 유형별, 일자별로 누가 근무했는지와 시간외근무를 오른쪽 '시간외근무 유형'에 따라 괄호 안에 시간수를 입력해서 표시하려고 합니다. 원 데이터는 다른 시트에 있는건데 이해와 설명의 편의를 위해 한 개 시트에 모았습니다. 2. 해결 방안 365 버전용 함수를 이용하는 방법과 이전 버전용 함수를 이용하는 두 가지 방법에 대해 알아보겠습니다. 가. 365 이전 버전 (1) d1, d2, n1, n2 유형에 따라 일자별 근무자 및 괄호안에 숫자 표시 일자와 유형 2가지 조건을 만족해야 하므로 두가지 조건을 * 연산자로 연결하면 =($B$3:$H$8=K$2)*($B$2:$H$2=$J3)이 되는데, #분산!라고 표시되고, 6*7의 크기로 테두리가 쳐집..

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. 문제 아래와 같이 단위 앞에 숫자가 있는데, 숫자의 길이가 무작위일 것입니다. 이와 같은 경우 문자를 기준으로 숫자만 추출하는 것을 해보고자 합니다. 2. 해결 방안 여러 가지가 있을 것입니다. 단순하게 each라면 간단하게 추출이 가능한데, pack까지 있다면 2가지를 찾아야 하고, 이런 것이 많아진다면 복잡해질 겁니다. 두번째는 단위인 each나 pack과는 무관하게 문자가 나올 때 그 위치 이전까지가 문자이므로 이를 이용하는 것입니다. 그런데 영어면 간단한데 한글이라면 어떻게 해야 할까요? 이렇게 3가지로 나눠서 해결 방안을 찾아보겠습니다. 3. 방법 1 : find 함수 이용 가. 단위의 위치 찾기 each와 pack을 찾아서 그 위치를 기준으로 숫자를 추출해 보겠습니다. =FIND("pac..

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)+(..