컴퓨터를 만나는 기쁨

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

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. 문제아래와 같은 문자열을 보면 ￦가 여러 번 나오는데, 첫 번째 ￦부터 두 번째 ￦전까지의 문자, 두 번째부터 세 번째 전까지의 문자를 추출해 보겠습니다. 2. 마지막 기호 다음의 문자열을 추출하는 Lambda 식 마지막 반복 문자 이후의 문자열 추출하기 (2)에서 했던 방법을 유사하게 적용하면 됩니다. 아래는 마지막으로 나오는 ￦다음의 문자열을 추출하기 위한 Lambda 식으로 재귀 호출을 사용해서 last_word를 반복 실행하면서 ￦가 나올 때마다 그다음의 문자열을 잘라내서 last_word 함수에 대입한 다음 마지막으로 남는 문자열을 반환하는 것입니다.=lambda(textString, findChars, if(iserror(find(findChars, textString)),textStri..

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)에 계획과 수행이면 "완료", 아니면 "진행필요"라고 표시..