len - Topics about IT

비어 있는 셀은 엑셀에서 주의해야 할 사항입니다. Month함수를 사용하면 1로 표시되기 때문입니다. 왜냐하면 1900-01-00이기 때문에 일이 0, 월은 1이 됩니다. 따라서, 집계할 때도 Month를 계산할 때 공백을 제외하도록 추가적인 조건을 주거나 날짜를 지정해야 합니다.

1. 문제

K45셀의 수식

=SUMPRODUCT(($B45=MONTH(H$3:H$40)&”월”)*(K$3:K$40))은

H열에서 1월을 찾아 K열 값을 더하는 수식인데 1월이 없는데도 11,787,000이란 값이 나옵니다.

비어있는 셀의 깂은 다운로드

2. 원인 찾기

M9셀에 =month(h9)라고 입력하니 100.0%, 다시 말해 1이라고 표시됩니다.

표시형식이 백분율이라 1이 100.0%로 표시되는 것입니다.

이 셀의 표시 형식을 간단한 날짜로 바꾸면

1900-01-01로 표시됩니다.

이번에는 month를 사용하지 않고 =h9라고 하면 1900-01-00으로 일이 이상하게 0이지만 월은 1로 표시됩니다.

다시 말해 빈 칸이 0이고, 1900-01-00일이기 때문에 숫자 1이나 0이나 모두 1월이 되는 것입니다.

3. 해법 1 – SumProduct

그렇다면 H3셀에서 H40셀의 값이 빈 셀이 아니거나, 값이 0보다 커야 한다는 조건을 걸어야 합니다.

=SUMPRODUCT((H$3:H$40<>””)($B45=MONTH(H$3:H$40)&”월”)(K$3:K$40))

이라고 빈 셀이 아니라는 조건을 줄 수도 있고,

=SUMPRODUCT((H$3:H$40>0)($B45=MONTH(H$3:H$40)&”월”)(K$3:K$40))

이라고 0보다 커야 한다는 조건을 줄 수도 있습니다.

3. 해법 2 – SumIfs 실패 1

=SUMifs(K$3:K$40,H$3:H$40,”>0″,MONTH(H$3:H$40)&”월”,$B45)

라고,

SumIfs 함수 구문인

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)에 맞게 수식을 작성해도 month란 함수가 SumIfs 함수 안에 있어서 오류가 발생합니다.

4. 해법 3 – SumIfs 성공

따라서, month를 사용하지 않고, 1월 1일보다 크고, 1/31보다 작다라고 수식을 수정하면 됩니다.

따라서, 수식은 아래와 같습니다.

=SUMIFS(K$3:K$40,H$3:H$40,”>=”&DATE(2025,LEFT($B45,LEN($B45)-1),1),H$3:H$40,”<=”&EOMONTH(DATE(2025,LEFT($B45,LEN($B45)-1),1),0))

2025/1/1은 B45셀을 이용하면 DATE(2025,LEFT($B45,LEN($B45)-1),1)로서

연도는 2025라고 직접 입력했고,

월은 B45셀이 ‘1월’로 문자로 되어 있으므로 월을 제외한 문자를 가져와야 하므로 left($b45,len($b45)-1)라고 왼쪽부터 문자를 가져오는데, 길이에서 1을 뺀 길이만큰 가져오도록 했고,

일도 1이라고 직접 입력했습니다.

그리고 월의 말일은 EOMonth 함수를 이용해 1/1의 0번째 월의 말일을 구하면 되며, 수식은

EOMONTH(DATE(2025,LEFT($B45,LEN($B45)-1),1),0)

입니다.

DATE(2025,LEFT($B45,LEN($B45)-1),1)와 EOMONTH(DATE(2025,LEFT($B45,LEN($B45)-1),1),0)의 값은

M12, M13에는 숫자로 표시하고, N12, N13셀에는 날짜 형식으로 표시했습니다.

그리고, 크거나 같고, 작거나 같아야 하므로

“>=”&와 “<=”&로 날짜를 연결해야 합니다.

K45셀의 수식을 복사한 후 K46셀과 K47셀에 테두리에 영향이 없도록 수식으로 붙여넣으면 값이 모두 잘 구해집니다.

3월이 화면을 보면 6,412,500인데, 7,412,500으로보이는 것은

중간의 행을 숨겨서 그렇습니다.

3행부터 40행까지 선택한 후 숨기기 취소를 하면

숨어있던 15행이 표시돼서 3월 900,000이 더해지므로 7,412,500 맞습니다.

5. 해법 4 – 날짜를 이용한 SumProduct 수식

month를 사용하는 경우에 비해 복잡하지만 날짜를 기준으로 수식을 작성할 수도 있습니다.

=SUMPRODUCT((K$3:K$40)(H$3:H$40>=DATE(2025,LEFT($B47,LEN($B47)-1),1))(H$3:H$40<=EOMONTH(DATE(2025,LEFT($B47,LEN($B47)-1),1),0)))

M39셀에 수식을 입력했습니다.

비어있는 셀의 깂은(해법)다운로드

claude desktop에 pyhub.mcptools를 설치한 뒤, claude와 엑셀과 연동하면서 clade에게 문제를 풀어달라고 하고, 잘못된 부분이 있으면 수정해달라고 하면서 완벽한 수식을 만들어갈 수 있습니다. 엑셀 도구 사용을 위해서는 엑셀 2016 이상이 설치되어 있어야 합니다.

1. pyhub.mcptools 설치

아래 사이트에 접속해서 윈도우즈용 pyhub.mcptools를 설치할 수 있습니다.

https://mcp.pyhub.kr/setup/windows

가. 실행 파일 받아서 압축 풀기

윈도우 기본 파워쉘과 파워셀 코어 7을 이용하는 두 가지 방법이 있는데,

윈도우 기본 파워쉘로는 실패해서 파워쉘 코어 7을 이용해서 설치했습니다. 명령어는 아래와 같은데, 파워쉘 버전 7이상이 설치되어 있어야 합니다.

pwsh -NoProfile -Command “iex (iwr ‘https://raw.githubusercontent.com/pyhub-kr/pyhub-mcptools/refs/heads/main/scripts/install.ps1’)”

나. 실행파일 차단 풀기

Microsoft Defender SmartScreen에서 실행을 막는다고 되어 있는데, 차단이 안되어 있어 추가 정보를 누르지 않고 바로 실행이 되었습니다.

다. 엑셀 Tool 실행 확인하기

명령 프롬프트에서 c드라이브의 c:\mcptools\pyhub.mcptools로 이동한 다음

pyhub.mcptools.exe tools-list를 입력하고 엔터키를 누르면 excel과 관련된 excel_get_opened_workbooks 등 name과 description이 화면에 표시됩니다.

라. MCP 도구를 통해 엑셀 읽어보기

굳이 할 필요 없습니다.

마. Claude Desktop에 등록하기

claude 웹 버전이 아니라 Desktop 버전이 설치되어 있어야 합니다.

claude desktop은 아래 URL에서 다운 받고, 설치한 후 로그인까지 해야 합니다. 무료 계정이라도 사용가능합니다.

https://claude.ai/download

.\pyhub.mcptools.exe setup-add란 명령어로 MCP 서버 설정이 자동으로 추가된다고 합니다.

바. Claude Desktop 재실행

여기서 좀 헤맸는데, Claude Desktop을 완전 종료하는 것이 중요합니다. 난 작업 관리자에서 ‘작업 끝내기’를 했는데,

.\pyhub.mcptools.exe kill claude 명령어를 입력하면 Claude를 완전히 종료시킬 수 있다고 합니다.

Claude Desktop 애플리케이션을 실행하고, 설명서에서는 망치가 보이는데, 여긴 볼륨조절같은 ‘검색 및 도구’ 아이콘을 누르면 그 아래에 pyhub.mcptools.exe가 보이면 성공입니다.

사. Claude를 통해 엑셀 협업하기

이 부분은 네이버 지식인의 질문을 가지고 실제 실습을 해보겠습니다.

2. Claude Desktop으로 엑셀 문제 풀기

가. 문제

C열의 데이터 개수에 따라 세로로 쌓는 문제입니다.

쉼표로 분리후 세로로 정렬 다운로드

나. Claude에 질문하기

(1) C열을 세로로 쌓기

“C열을 쉼표로 구분해서 i4셀부터 세로로 쌓는 수식 알려줘”라고 입력하고 엔터 키를 누르니

아래와 같이 TextSplit 함수와, PowerQuery, VBA를 이용하는 방법 3가지를 알려줍니다.

그런데 첫번째 수식을 적용해도 제대로 된 답이 안나옵니다. 그래서 답이 잘 안온다고 했더니 이런 저런 문제점을 지적하더니 마지막에 실제 데이터 예시를 부여달라고 합니다.

어제는 엑셀을 열면 바로 인식을 했는데, 오늘은 이상하게 모르네요.

그래서 ‘현재 엑셀 파일이 열려 있잖아’라고 하니, ‘현재 열려있는 Excel 파일의 상황을 확인해보겠다고 합니다.

그리고는 엑셀 내용을 확인하고는 문제를 확인했다고 하면서 여러가지로 시도해보고는

성공했다고 하면서 수식을 i4셀에 넣어주고, 설명도 해줍니다.

엑셀을 보니 답이 맞게 됐습니다.

(2) D열을 세로로 쌓기는 C열 수식을 이용하면 됩니다.

(3) B열 일련번호 쌓기

마찬가지로 “B열의 숫자를 H4셀부터 C열의 개수에 따라 중복 표시하려면?”이라고 물으니 여러번 시도를 해본 다음 중간 계산용이라고 G4셀에 수식 하나를 입력하고 최종 결과를 H4셀에 입력하는데, 맞는 값이 아닙니다.

그래서 중간의 공백을 제거해달라고 요청했고, G4셀 없이 수식을 만들어달라고 요청했습니다.

그런데 위 수식은 계속해서 문자열을 구해서 결합하는 식으로 되어 있어 하나의 수식으로 만들어달라고 하니 여러 번 시도를 한 후 최종 수식을 제시해 줍니다.

그런데, 마지막 셀이 빈 셀입니다. 그래서 빈 셀을 제거해달락도 요청하니

완벽한 수식을 제공합니다.

실제 화면도 보니 마지막 빈 셀이 제거되었습니다.

(4) E열 개수 세로로 쌓기

E열 개수 세로로 쌓는 것은 묻지 않고 내가 H4셀의 수식을 복사한 후 아래와 같이 수정했습니다.

수식 입력줄의 수식으 복사하면 아래와 같은데, 여기서 B4:B50을 E4:E50으로 수정하면 됩니다.

=LET(text_string,SUBSTITUTE(TEXTJOIN(“,”,TRUE,IF((B4:B50<>””)*(C4:C50<>””),REPT(B4:B50&”,”,LEN(C4:C50)-LEN(SUBSTITUTE(C4:C50,”,”,””))+1),””)),”,,”,”,”),clean_text,IF(RIGHT(text_string,1)=”,”,LEFT(text_string,LEN(text_string)-1),text_string),TRANSPOSE(TEXTSPLIT(clean_text,”,”)))

그러면 아래와 같이 잘 표시됩니다.

다. Claude가 사용한 명령어

Claude가 사용한 명령어를 살펴보니 엑셀 파일을 열고, 값을 불러드린 다음 처리한 수식을 기록하는 세 가지에 지나지 않습니다. 그래프를 그리거나, 피벗 등을 만들면 더 많은 명령어를 사용하겠지요.

Excel get opened workbooks
Excel get values
Excel set cell data

아직은 여러 번 문답을 하면서 바로 잡아가는 과정을 거쳐야 하지만 엑셀의 내용을 이해하고, 셀에 직접 기록도 해주고 하니 편리합니다.

그런데 무료 계정이다 보니 어느 정도 시간이 지나면 한도에 도달했다고 하면서 사용할 수 없는 시간이 있는 것은 불편합니다.

완성된 파일은 아래와 같습니다.

쉼표로 분리후 세로로 정렬(해법)다운로드

[태그:] len

비어 있는 셀의 값은?