[태그:] textjoin

pyhub.mcptools로 엑셀 문제 풀기

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

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

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

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

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

세로로 중복된 값 다루기

1. 문제

아래와 같이 중복된 숫자가 있으면 중복을 제거해야 할 경우가 생깁니다. 이와 관련해서 다양한 경우를 다루고자 합니다.

세로로 중복된 값 다루기(문제)다운로드

2. 중복된 숫자의 개수 세기

=COUNTIF(A2:A9,A2:A9)라고 하면

A2셀에서 A9셀까지의 범위에서 같은 숫자의 개수를 구해줍니다.

Microsoft 365라 아래와 같이 보이는 것이지 낮은 버전이라면 B2셀에서 B9셀까지 선택한 후 수식을 입력하고 Ctrl + Shift + Enter 키를 눌러야 할 겁니다.

A10셀에 2를 추가하고, 수식을 A10셀까지 수정하면 2라는 숫자의 개수가 모두 3으로 변경됩니다.

엑셀이 구 버전이라면 수식을 모두 지우고 새로 수식을 입력해야 할 수도 있습니다. 그렇다면 수식을 먼저 복사해서 다른데 붙여넣고 작업하는 것이 안전합니다.

2. 순수한 숫자의 개수 세기

위의 경우 1, 2, 4, 5, 7, 8과 같이 중복된 수라도 한번만 세려면

B열의 수식을 1/로 수정해서

=1/COUNTIF(A2:A10,A2:A10)이라고 하면 1/중복 개수가 되므로 1이면 1이지만, 2라면 1/2=0.5가 되고, 1/3=0.333333이 됩니다.

따라서, 위 수식을 sum 하면 0.5+0.5 = 1, 0.333333*3 = 1과 같이 고유한 숫자의 개수가 구해집니다.

=SUM(1/COUNTIF(A2:A10,A2:A10))을 하니 개별적으로 표시되던 것이 합계값 하나로 6이라고 표시됩니다. 1,2,4,5,7,8이므로 6 맞습니다.

3. 순수한 숫자만 표시하기

순수한 숫자 1, 2, 4, 5, 7, 8을 구해보겠습니다.

가. 중복 숫자는 한 번만 표시하고, 두번째부터는 공란으로 표시하기

D2셀에 =IF(COUNTIF($A$2:A2,A2)=1,A2,””) 이라고 입력합니다.

아래로 내려가면서 해당 셀의 개수를 세서 1보다 크면 빈 셀로 만들려고 하는 것입니다. 첫 셀인 A2셀을 절대 참조로 하는 것이 중요합니다. 왜냐하면 항상 A2셀부터 현재 셀(상대 참조)까지의 현재 셀의 개수를 세려고 하는 것이기 때문입니다.

D2셀의 채우기 핸들(D2셀 오른쪽 아래 네모)을 더블 클릭하면 D10셀까지 수식이 채워지는데(복사),

1, 2, “”, 4, 5, “”, 7, 8, “”라고 숫자가 중복되면 빈 셀로 표시되므로 이 숫자 들을 결합하면 됩니다.

나. 숫자 결합해서 표시하기

(1) TextJoin 함수

TextJoin함수는 Delimiter를 지정해서 텍스트를 결합할 수 있기 때문에 Concat함수보다 훨씬 편리합니다.

=TEXTJOIN(“, “,,D2:D10)라고,

구분자로 ,를 지정하고, 빈셀을 무시하도록 두번째 인수는 입력하지 않고 통과하고, 세번째 인수로 결합할 텍스트의 범위를 지정하면 원하는대로 1, 2, 4, 5, 7, 8이 구해집니다.

(2) Concat 함수

Concat 함수는 구분자를 지정할 수 없고, 결합할 텍스트만 지정할 수 있어서

=CONCAT(D2:D10)라고 하면 124578이라고 숫자가 단순히 결합된 결과만을 반환합니다.

(대체 해법)

어렵지만 할 수 없는 것은 아닙니다.

먼저 숫자가 있을 경우는 숫자 뒤에 공백을 한 칸 추가한 다음

Concat으로 연결한 다음 Substitute 함수를 이용해 공백 한 칸을 쉼표 + 공백 한칸으로 대체하면 됩니다.

=SUBSTITUTE(CONCAT(IF(D2:D10<>””,D2:D10&” “,””)),” “,”, “)라고 입력하면

결괏값을 보니 필요없이 마지막에도 ,가 들어가 있습니다.

따라서, concat한 후 trim을 해서 빈 공백을 제거해줘야 합니다.

수정된 수식은

=SUBSTITUTE(TRIM(CONCAT(IF(D2:D10<>””,D2:D10&” “,””))),” “,”, “) 입니다.

원하는대로 마지막에 쉼표 없이 1, 2, 4, 5, 7, 8이 구해졌습니다.

세로로 중복된 값 다루기(해법)다운로드