비어 있는 셀의 값은?

비어 있는 셀은 엑셀에서 주의해야 할 사항입니다. 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셀에 수식을 입력했습니다.

Web 접속 방법 Rust, Python 비교

러스트의 경우 처음에 명령 프롬프트 창에서 포트를 열어놓고 Cargo run을 하라고 하여 Python은 포트를 열 필요없이 잘 접속되는데 해서 비교하게 되었습니다. 그리고, 보면 파이썬은 편리한 상태를 만들어 놓았는데, Rust는 처음부터 내가 만들어가야 하는 상황입니다.

1. Rust를 이용한 웹 접속

가. 명령 프롬프트 방식

(1) 명령 프롬프트에서 프트 열기

chromedriver –port=9515를 실행해서 Chromedriver를 실행시킵니다.

명령 프롬프트에서 9515 포트 열기

Chromedriver가 Path에 있다면 그냥 실행하면 되는데, path가 설정되어 있지 않아, chromedriver.exe가 있는 폴더로 이동해서 실행했습니다.

(2) 브라우저 열기 Rust 코드

(가) Cargo.toml
[dependencies]
thirtyfour = "0.36.1"
tokio = { version = "1", features = ["full"] }

thirtyfour와 tokio 라이브러리를 가져와야 합니다.

(나) main.rs
use thirtyfour::prelude::*;
use tokio;

#[tokio::main]
async fn main() -> WebDriverResult<()> {
    // 이미 chromedriver가 9515 포트에서 실행 중이라고 가정
    let driver = WebDriver::new("http://localhost:9515", DesiredCapabilities::chrome()).await?;

    // 페이지 접속
    driver.get("https://www.rust-lang.org").await?;

    // 타이틀 가져오기
    let title = driver.title().await?;
    println!("Page title: {}", title);

    // 종료
    driver.quit().await?;
    Ok(())
}

코드이 내용은 port가 열려 있기 때문에 다시 열 필요는 없고, 9515 포트로 driver를 설정한 다음, rust-lang.org에 접속한 후 title을 가져와서 화면에 출력하는 것입니다.

문제 없이 코드가 실행되고, title이 표시됩니다.

cargo run을 이용해 rust-lang-org에 접속한 후 타이틀을 가져와 화면에 출력

나. 코드로 포트 열기 방식

명령 프롬프트에서 포트를 연 다음 Cargo run을 한다는 것이 이상하므로 Rust에서 포트를 열고, 실행하려면 아래와 같이 코드를 작성하면 됩니다.

Cargo.toml은 동일하고,

main.rs만 아래와 같이 수정하면 됩니다.

use std::process::Command;
use thirtyfour::prelude::*;
use tokio;

#[tokio::main]
async fn main() -> WebDriverResult<()> {
    // 실행파일이 있는 디렉토리 경로
    let exe_dir = std::env::current_exe()
        .unwrap()
        .parent()
        .unwrap()
        .to_path_buf();
    let chromedriver_path = exe_dir.join("chromedriver.exe");
    println!("chromedriver 경로: {}", chromedriver_path.display());

    // chromedriver 실행
    let mut child = Command::new(&chromedriver_path)
        .arg("--port=9515")
        .spawn()
        .expect("chromedriver 실행 실패");

    // WebDriver 클라이언트 연결
    let caps = DesiredCapabilities::chrome();
    let driver = WebDriver::new("http://localhost:9515", caps).await?;

    driver.get("https://www.rust-lang.org").await?;
    println!("현재 페이지 타이틀: {}", driver.title().await?);

    // 브라우저 닫기
    driver.quit().await?;
    // chromedriver 프로세스 종료
    child.kill().ok();

    Ok(())
}

use std::process::Command;가 추가되었습니다.

main에서 먼저 chromedriver_path를 설정하고,

Command::new를 이용해 포트를 연 다음 child에 저장하고,

caps는 python Selenium에서 사용하는 ChromeOptions 역할입니다. 기본적인 옵션만 설정하는 것입니다. caps.add_arg(“–headless”)?; 등을 추가해서 옵션을 추가할 수 있습니다.

그리고, WebDriver::new로 http://localhost:9515라고 9515포트를 이용해 localhost를 연 다음

driver.get로 https://www.rust-lang.org를 연 다음

driver.title().await?로 title을 가져와서 화면에 출력합니다.

아래는 Visual Studio Code에서 Run한 장면입니다.

Compile과 실행 잘 되고, ChromeDriver was started successfully on port 9515.와

현재 페이지 타이틀 : Rust Programming Language라고 잘 나옵니다.

9515 포트 열기와 rust-lang.org에 접속해서 title 가져오기를 통합한 실행 화면

2. Python을 이용한 웹 접속

가. chrome_connect.py

from selenium import webdriver
from selenium.webdriver.chrome.service import Service
import os

def main():
    # 실행 파일이 있는 디렉토리 기준으로 chromedriver.exe 찾기
    exe_dir = os.path.dirname(os.path.abspath(__file__))
    chromedriver_path = os.path.join(exe_dir, "chromedriver.exe")

    # chromedriver.exe 고정 경로
    # chromedriver_path = r"C:\android\chromedriver.exe"
    # print(f"chromedriver 경로: {chromedriver_path}")

    # chromedriver 실행 (포트 지정 없이 내부적으로 관리)
    service = Service(chromedriver_path)
    options = webdriver.ChromeOptions()

    driver = webdriver.Chrome(service=service, options=options)

    try:
        driver.get("https://www.rust-lang.org")
        print(f"현재 페이지 타이틀: {driver.title}")
    finally:
        driver.quit()

if __name__ == "__main__":
    main()

Python은 Cargo.toml과 같은 설정이 필수가 아니고,

바로 python code를 위와 같이 작성하면 됩니다.

실행 결과는 아래와 같습니다.

파이썬으로 chromedriver를 실행한 후 rust-lang.org의 타이틀을 화면에 출력

나. 코드 내용

from selenium import webdriver
from selenium.webdriver.chrome.service import Service
import os

필요한 selenium과 os 라이브러리를 불러옵니다.

    # 실행 파일이 있는 디렉토리 기준으로 chromedriver.exe 찾기
    exe_dir = os.path.dirname(os.path.abspath(__file__))
    chromedriver_path = os.path.join(exe_dir, "chromedriver.exe")

    # chromedriver.exe 고정 경로
    # chromedriver_path = r"C:\android\chromedriver.exe"


실행 파일이 있는 폴더의 chromedriver.exe를 chromedriver_path로 설정합니다.

주석 처리한 것처럼 고정 경로로 지정할 수도 있습니다. 여러 프로그램에서 공통적으로 사용할 수 있으므로 PC에서만 작업한다면 이것이 편할 수 있습니다.

print(f"chromedriver 경로: {chromedriver_path}")

chromedriver_path를 화면에 출력합니다.

    # chromedriver 실행 (포트 지정 없이 내부적으로 관리)
service = Service(chromedriver_path)
options = webdriver.ChromeOptions()

Service 메소드를 이용해 service를 생성하고, webdriver.ChromeOptions()로 크롬 설정을 기본으로 합니다.

driver = webdriver.Chrome(service=service, options=options)

service와 options 설정으로 크롬을 열고, driver 객체에 담습니다.

    try:
driver.get("https://www.rust-lang.org")
print(f"현재 페이지 타이틀: {driver.title}")
finally:
driver.quit()

www.rust-lang.org 열기를 시도해서 성공하면 페이지의 타이틀을 driver.title로 가져와서 화면에 표시합니다.

그리고, 크롬을 종료합니다.

3. 러스트와 파이썬 비교

Rust는 Port를 반드시 지정해야 하는데, Python은 selenium을 이용하기 때문에 포트를 지정할 필요가 없는 차이점이 있습니다.

자세히 말하면 Rust의 thirtyfour는 W3C WebDriver 프로토콜을 따르는 라이브러리라서, chromedriver.exe를 서버처럼 띄우고 http://localhost:9515 같은 포트를 통한 HTTP 요청으로 제어하는데 비해

Python의 selenium은 내부적으로 chromedriver.exe를 subprocess로 실행하고, 외부에서 포트 번호를 직접 지정할 필요 없이 Selenium이 알아서 관리합니다.

4. 실행 파일 사이즈

pyinstaller -F -w chrome_connect.py로 실행한 후 파일 사이즈를 보면

18메가 정도되는데,

cargo run으로 생성한 실행 파일 크기는 8메가 정도되는데,

cargo build –release해서 만든 Rust 실행 파일을 보면 4메가 정도로 파이썬 18메가의 22%뿐이 안됩니다.

속도도 미세하지만 Rust가 빠른 듯 합니다.

Rust 까다롭고, 생소한 측면이 너무 많지만 좋기때문에 자꾸 익히고 적응해나가야 하겠습니다.

충전이후 운행시간 구하기

등록번호별로 운행, 충전중, 완충과 구동시간이 표시된 데이터를 기준으로 월별 최소, 최대, 평균 충전 사용 시간을 구하려고 합니다. 그런데, 운행과 충전중, 완충 데이터가 연속되는 경우도 있고, 최초 충전의 경우는 이전 충전 기록이 없음에 따라 이들을 고려한 수식을 만들려고 합니다.

1. 문제

아래 표를 보면 A열이 구동시간, B열이 년-월, C열이 날짜, D열이 배터리 상태, E열이 등록번호, F열이 배터리 잔량, G가 최종시간으로 되어 있고,

구동시간, 월, 날짜, 배터리상태, 등록번호, 배터리 잔량, 최종시간이 있는 기초 데이터

오른쪽에 원하는 집계표 양식이 있습니다. 월별로 1회 충전 사용 시간의 최소, 최대, 평균 값을 구하는 것입니다.

월별 최소, 최대, 평균 1회 충전 사용 시간 집계표 양식

2. 문제의 정의

문제를 풀려면 문제가 뭔지 알아야 합니다.

처음에는 완충이 왜 여러 번 나오지, 다른 차라서 그렇겠지 했는데, 차는 같은데 계속 꽂아 놓아서 10분 간격으로 완충이라고 표시되는 것이었다.

완충 데이터가 연속돼서 다른 차량인 줄 알았으나 동일한 차량이 완충 데이터를 계속 보내고 있음

완충 시간이 연속될 경우 시간을 어떻게 구하는지 물어보니 첫번째 완충에서 이전 마지막 완충시간을 빼서 구한다고 한다.

운행이후 최초 완충 시간에서 운행 이전 마지막 완충 시간을 빼서 운행 시간을 구함

완충이 아니라 충전중이다가 운행을 할 경우에도 최초 충전중 시간에서 이전 최종 완충 또는 충전중 시간을 뺀다

처음에는 충전중이었다가 완충이 되는 경우에 완충에서 이전의 충전중 또는 완충시간을 빼는 거라고 잘못 생각하고,

아래와 같은 경우 완충이 179행에 있고, 이전 충전중이 168행에 있으니 179행의 A열에서 168행의 A열을 빼니 1:49:35이고, 중간에 운행에서 충전중일 때는 게산 안하는 것인 줄 알았다.

충전중이다가 완충인 경우 수식과 최초 충전중으로 이전에 충전중 또는 완충이 없을 때 처리 방법 고민

그러나. 운행에서 충전중으로 바뀌는 시점에 충전중인 172행에서 168행을 빼는 것이 맞았다.

정리를 하면 운행에서 충전중 또는 완충으로 바뀔 때(신기하게 충전중을 거치지 않고 바로 완충 표시가 나옴) 운행 이전의 충전중 또는 완충 시간을 빼서 운행 시간을 구하는 것이다. => 기준 1

그리고, 이전 충전 기록이 없을 때 0으로 표시할 것이지, 아니면 최초 운행 시간을 기준으로 할 것인지 의문이 있는데, 0으로 표시하는 것보다는 최초 운행시간을 기준으로 하는 것이 맞다. => 기준 2

3. 해결방법

이제 문제와 기준을 알았으니 수식만 만들면 된다.

가. 텍스트로 된 날짜 데이터 변환하기

A2셀에서 A4셀까지 범위를 잡은 후 상태 표시줄을 보니 개수: 3이라고 표시된다. 날짜 형식이 아니라 텍스트이다.

날짜인 줄 알았더니 텍스트라 개수만 표시되고 숫자인 경우 표시되는 합계 등은 표시되지 않음

(1) 날짜 시간 데이터 바꾸기

H2셀에 1이라고 입력하고 복사한 후 A2셀부터 맨 아래까지 선택한 후 마우스 오른쪽 버튼을 누른 후 선택하여 붙여넣기를 누르고(오른쪽 꺾기를 누르면 한번 더 선택하여 붙여넣기를 눌러야 하므로 불편),

1을 복사한 후 마우스 오른쪽 버튼을 누르고 선택하여 붙여넣기 메뉴를 클릭함

곱하기를 누르고, 확인 버튼을 누른다.

선택하여 붙여넣기 대화 상자에서 곱하기 선택 화면

그러면 모두 실수로 바뀌는데 날짜만 있으면 정수인데, 시간까지 있어서 실수가 되는 것입니다.

텍스트 형식의 날짜에 1을 곱하니 실수로 표시됨

이제 A열의 표시형식을 yyyy-mm-dd hh:mm:ss로 바꿉니다.

그런데, 마우스 오른쪽 버튼을 누른 후 셀 서식을 선택하고 사용자 지정으로 들어가봐도 yyyy-mm-dd h:mm까지만 있고, hh:mm:ss는 없습니다. 그러면 형식 바로 아래에 있는 yyyy-mm-dd h:mm를

표시형식에 yyyy-mm-dd hh:mm:ss가 없고 yyyy-mm-dd h:mm만 있음

yyyy-mm-dd hh:mm:ss로 수정하면 됩니다. 형식을 수정하니 위의 보기도 바로 변경됩니다. 이제 확인 버튼을 누르면

날짜 형식 데이터의 표시형식을 숫자에서 yyyy-mm-dd hh:mm:ss 으로 변경

A열은 날짜 표시 형식이 연-월-일 시:분:초로 바뀌었는데, B열과 C열은 여전히 실수입니다.

날짜 형식의 데이터를 yyyy-mm-dd hh:mm:ss 표시형식으로 변경한 화면

(2) 연-월 바꾸기

B2셀의 수식을 살펴보니 =LEFT(A2,7)이라고 문자열 기준으로 수식이 되어 있습니다.

A열에 텍스트 형식의 날짜가 있다는 전제로 Left 함수를 이용해 연월을 구했으나, A열이 날짜 형식으로 변경됨에 따라 실수로 표시됨

따라서, 수식부터 Year와 Month 함수를 이용하거나 Text 함수를 이용해 수정해야 합니다.

Text 함수를 이용하면 =TEXT(A2,”yyyy-mm”)가 됩니다. 표시형식을 지정하는 것과 비슷합니다.

연월 데이터를 Text 함수를 이용해 수정 입력

(3) 날짜(상세) 바꾸기

C2셀의 수식으 보면 =LEFT(A2,10)으로 문자열 기준으로 수식이 되어 있습니다.

이 때도 Text함수를 이용할 수 있는데, 이번에는 복잡하지만 Date 함수와 Year, Month, Day 함수를 이용해서 구해보겠습니다.

C2셀에 =DATE(YEAR(A2),MONTH(A2),DAY(A2))라고 입력하니 숫자가 아닌 날짜 표시형식으로 표시됩니다.

텍스트 형식으로 입력된 수식을 Date 함수를 이용해 수정하니 날짜 표시형식으로 표시됨

이제 B2셀과 C2셀을 마우스로 끌어서 선택한 후 C2셀의 채우기 핸들을

연월과 날짜 수식이 있는 B2셀과 C2셀을 선택하면 C2셀에 채우기 핸들이 표시됨

더블 클릭하면 맨 아랫줄까지 자동으로 수식이 복사됩니다.

월과 날짜 수식을 맨 아랫줄까지 복사

이제 날짜 변환이 모두 끝났으니 H2셀의 1을 지웁니다.

나. 운행시간 구하는 수식 작성하기

그런데 https://overmt.com/pyhub-mcptools로-엑셀-문제-풀기/ 방법을 알고 나니 생각이 없어집니다.

claude desktop을 실행한 후 아래와 같이 질문하니, 이 때는 이전 완충 또는 충전중인 구동시간이 없을 때는 0으로 처리해야 하겠다고 생각할 때입니다.

클로드에게 수식에 필요한 요구사항을 명시한 프롬프트

계산을 잘 못하므로 H561셀에 수식을 =A561-A541이라고 넣고 다시 물어봅니다.

클로드가 헤매고 있어서 맞는 수식을 예시로 입력한 화면

수식을 넣으면 실수로 표시돼서 E열의 표시형식을 [h]:mm:ss로 바꿔야 합니다.

실수를 시:분:초 표시형식으로 변경

그리고 시간이 좀 지나서 H168에 수식을 넣었다고 해서 확인해보니

클로드가 입력한 수식

=A168-A101로 계산한 값과 일치합니다.

다른 셀에서도 수식이 맞는지 체크하는 화면

J168셀의 채우기 핸들을 J90까지 끌고 값을 =A90-A2로 확인해보니 맞습니다.

클로드가 제시한 수식을 검산하는 장면

중간에 아래와 같이 수식을 제공해서

프롬프트에 따라 클로드가 제시한 수식

아래와 같이 수정 요구하고,

claude가 제시한 수식을 수정 요청하는 프롬프트

수식에서 OFFSET(D561,-1,0)를 간단하게 D560으로 수정하는 등 약간의 손길이 가야합니다.

=IF(AND(E561=E561,OR(D561=”완충”,D561=”충전중”),OFFSET(D561,-1,0)=”운행”), A561-IFERROR( INDEX(A$2:A560,MAX(IF((E$2:E560=E561)*((D$2:D560=”완충”)+(D$2:D560=”충전중”)),ROW(A$2:A560)-1))), INDEX(A$2:A560,MIN(IF((E$2:E560=E561)*(D$2:D560=”운행”),ROW(A$2:A560)-1))) ),””)

그리고, J168셀의 수식을 채우기 핸들을 더블 클릭해서 다른 셀에도 채운 후 등록번호가 바뀌는 시점에서 어떻게 되는지 해보니 #VALUE!에러가 발생합니다.

claude가 제시한 수식

다시 왜 오류가 발생하는지 물어보니

=IF(AND(E288<>””,OR(D288=”완충”,D288=”충전중”),D287=”운행”),A288-IFERROR(INDEX(A$2:A287,MAX((E$2:E287=E288)((D$2:D287=”완충”)+(D$2:D287=”충전중”))(ROW(E$2:E287)))-1),INDEX(A$2:A287,MATCH(E288,E$2:E287,0))),””)

라고 충전중 또는 완충이 없을 때 값을 등록번호가 일치하는 최초 행으로 바꿔줍니다.

이제 다른 셀에도 적용하니 문제없습니다.

좀 손은 가지만, 그리고, 엑셀에 대한 기본 지식이 있어야 제시한 수식을 이해하고 수정 요청을 할 수 있지만 대단합니다.

다. 집계표 만들기

필터를 없애고,집계표 왼쪽에 등록번호가 없으므로 추가합니다.

S5셀에

MINIFS($H:$H,$E:$E,$I5,month($B:$B),VALUE(LEFT(S$2,LEN(S$2)-1)),$H:$H,”>0″)

라고 입력하면 month($B:$B)때문에 에러가 발생하므로 B열을 월만 표시하던가, 월이 있는 다른 열을 만들어야 합니다. B열을 =month(A2)로 해서 월로 바꾸겠습니다.

MaxIfs 수식 안에서 Month 함수를 사용 시 에러 발생

그리고, 아래와 같이 입력하면

=MINIFS($H:$H,$E:$E,$I5,$B:$B,VALUE(LEFT(S$2,LEN(S$2)-1)),$H:$H,”>0″)

S5셀의 값이 구해집니다.

이런 식으로 다른 셀에도 수식을 복사해서 붙여넣는데, min은 max 또는 average로 수정하고, 집계표의 월을 지정하는 주소도 수정해야 합니다. 복사할 때는 최소,최대,평균 3개를 한꺼번에 복사해야 합니다.

그런데 해당 월의 데이터가 없을 때 AverageIfs 수식에서 #DIV/0!에러가 발생하므로 ifError함수를 추가해서 0으로 지정합니다.

=IFERROR(AVERAGEIFS($H:$H,$E:$E,$I5,$B:$B,VALUE(LEFT(J$2,LEN(J$2)-1)),$H:$H,”>0″),0)

#DIV/0! 0으로 나누는 에러 표시

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

chromedriver.exe 파일이 문제가 있네요

크롬 버전이 140.0.7339.81로 139에서 업그레이드가 돼서 chromedriver.exe를 다운받아 실행하는데, 그동안 문제없이 잘 되던 프로그램이었는데, V3에서 차단하고, 명령 프롬프트 창이 뜨면서 사이트에 접속이 안됩니다.

1. 크롬 업그레이드 정보 확인

크롬이 업그레이드 되면 selenium을 이용한 프로그램 실행 시 “크롬드라이버 버전이 다르다”고 하면서 안됩니다. 그래서 버전을 확인해보니 139에서 140으로 업그레이드가 됐습니다.

2. Chromedriver.exe 다운로드

https://googlechromelabs.github.io/chrome-for-testing

사이트를 확인하니

Stable 140.0.7339.80 버전이 있어서 다운로드 받아 설치하고

앱을 실행하니 V3가 “앱 격리 검사 중지” 메시지를 표시하고,

예전에는 뜨지 않던 명령 프롬프트 창이 뜨는데,

내용을 보니, 두번째 문단에 “Only local connections are allowed.”라고 나오고,

Starting ChromeDriver 140.0.7339.80 (670b6f192f4668d2ac2c06bd77ec3e4eeda7d648-refs/branch-heads/7339_41@{#3}) on port 63428
Only local connections are allowed.
Please see https://chromedriver.chromium.org/security-considerations for suggestions on keeping ChromeDriver safe.
ChromeDriver was started successfully on port 63428.

네번째 문단에 “ChromeDriver가 성공적으로 63428 포트에서 시작되었다”하는데, 구글 사이트에 접속하지 못하고 여기서 멈춰있습니다.

그런데 오전에는 아래와 같은 메시지도 나왔는데,

PdhAddEnglishCounter failed for ‘\Hyper-V Hypervisor Logical Processor(_Total)\% Total Run Time’

이것은 관리자 권한으로 명령 프롬프트를 실행하고

lodctr /r라고 실행하니

Info: ??? ?? ?????? ?? ??? ??? ?? ??????.
라고 이상한 문자로 표시됐지만, ChatGPT에게 물어보니 한글 인코딩 문제이고, “정상 완료 메시지”라고 합니다.

이렇게 한글이 깨져서 보이는 건 메시지 출력 시 콘솔 인코딩 문제예요.
내용 자체는 “성능 카운터 레지스트리를 다시 빌드했습니다” 같은 정상 완료 메시지인데, 한글이 깨져서 표시된 거예요.

그래서 더 이상 위 PdhAddEnglishCounter 에러 메시지는 안나오는 듯 합니다.

3. ChromeDriverManager로는 된다.

계속 ChatGPT에게 물어봐도 해결되지 못하고 있었는데, chromedrive 버전을 확인하라고 해서 확인하니 135라고 표시돼서, 그러면 “ChromeDriverManager를 실행하면 어떻겠냐”고 제안해서 해보니 된다.

하루 묶은 체증이 다 내려가는 느낌이다.

그렇게 안되더니 이럴 수가…

그래서 ChromeDriverManager로 인스톨한 chromedriver.exe와 크롬 드라이버 다운로드 사이트에서 받은 파일을 비교해보니 파일 사이즈가 다릅니다. 어찌 이럴 수가 있죠?

작은 것(chromedriver2.exe라고 파일명 변경)이 제대로 돌아가는 파일이고, 문제가 있는 파일이 chromedriver.exe입니다.

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

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

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

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

nuitka와 가상 환경

nuitka가 python version 3.대와 맞지 않는다는 ChatGPT의 말에 따라 venv를 3.12.6으로 낮추고 nuitka –standalone –onefile df_base.py를 실행하니 python 3.13에서 nuitka 2.7.13이 실행된다는 메시지가 나옵니다. 그래서 python -m nuitka –standalone –onefile df_base.py를 하니 python 버전 3.12에서 실행됩니다.

1. python code

import pandas as pd
import numpy as np
import openpyxl # nuitka때문에 해야 하나?

index = pd.date_range('1/1/2000', periods=8)
print(index)

df = pd.DataFrame(np.random.rand(8,3),index = index, columns=list('ABC'))
df['D'] = df['A'] / df['B']
df['E'] = np.sum(df, axis=1)
df = df.sub(df['A'], axis=0)
df = df.div(df['C'], axis=0)
df.to_csv('test.csv')
print(df.head())
# print(df)
df2 = df[df['B']>0.4].T
print(df2.head())
df.to_excel('날짜랜덤.xlsx', engine="openpyxl")

import pandas as pd
import numpy as np
=> pandas와 numpy 라이브러리를 호출해서 각각 pd와 np라는 alias로 선언합니다.

index = pd.date_range(‘1/1/2000’, periods=8)
=> 2000/1/1부터 8개를 생성해서 index로 삼습니다.

2000-01-01부터 8일간을 인덱스 변수에 대입함

dtype(데이터 형식)은 datetime64인데, ns는 nanosecond의 약자로 nanosecond까지 저장한다는 의미이고,

freq는 frequency의 약자로 D(날짜), 다시 말해 1일 단위라는 것입니다.

print(index)
=> index를 화면에 출력합니다.

df = pd.DataFrame(np.random.rand(8,3),index = index, columns=list(‘ABC’))
=> random 함수로 0과 1사이의 숫자를 8행 3열로 생성하고, index는 위에서 생성한 index 변수로 지정하고, column은 A,B,C로 함.

8행 3열로 난수를 생성 후 데이터프레임 df 생성

df[‘D’] = df[‘A’] / df[‘B’]
=> A열을 B열로 나눠 D열을 생성해서 값을 넣고

A를 B로 나눈 후 D열을 만들어 저장함

df[‘E’] = np.sum(df, axis=1)
=> df에 E열을 생성해서 행별 합계를 넣습니다.

행별로 합산 후 출력 화면
구분장점주의점
np.sum(df, axis=1)빠름, NumPy 배열과 함께 사용 시 효율적DataFrame에 숫자가 아닌 값이 있으면 오류
df.sum(axis=1)pandas 데이터에 최적화, numeric_only 옵션으로 안전속도는 NumPy보다 조금 느림

df = df.sub(df[‘A’], axis=0)
=> 모든 열에서 A열의 값을 빼서 새로운 df를 생성합니다.
아래는 5개만 출력해서 5일까지만 보이는 것입니다.

모든 열에서 A열 값을 뺀 후 출력 화면

위 표에서 2000-01-01의 B값은 당초 0.933993에서 A값인 0.632852을 빼서 0.301141이 된 것입니다. 이런 식으로 모든 열에서 A열의 값을 빼서 기록한 것입니다.

df = df.div(df[‘C’], axis=0)
=> 모든 열의 값을 C열 값으로 나눠서 새로운 df를 생성합니다.

C열 기준으로 나눈 후 출력 화면

위 표에서 2000-01-01의 B값은 당초 0.301141을 C값인 0.338077으로 나눠서 0.890747이 된 것입니다. 이런 식으로 모든 열을 C열의 값으로 나눠서 기록한 것입니다.

df.to_csv(‘test.csv’)
=> df를 test.csv 파일로 저장합니다.

csv 파일을 열어보면 아래와 같이 ,(쉼표)로 열이 구분되어 있고, 소숫점이하 자릿수가 화면에 표시되는 것보다 훨씬 많아 15~17자리로 표시됩니다.

데이터프레임을 csv 파일로 저장한 화면

print(df.head())
=> df의 값을 처음부터 5개 화면에 출력합니다.

C열 기준으로 나눈 후 결과 출력 화면

df2 = df[df[‘B’]>0.4].T
=> B열이 0.4보다 큰 것만을 구해서 df에 넣고,
Transpose, 다시 말해 행과 열을 바꾼 후 df2라는 새로운 데이터프레임에 넣습니다.

print(df2.head())
=> df2 데이터프레임 중 첫 5개를 화면에 출력합니다.

B열을 기준으로 0.4보다 큰 행만 고르기때문에 1/1, 1/5, 1/6, 1/7만 추출됐고, 행/열 전환이 되다보니 아래와 같이 표시됩니다.

데이터프레임을 B열 기준으로 조건을 지정한 후 행/열 전환한 경우

df.to_excel(‘날짜랜덤.xlsx’, engine=”openpyxl”)
=> df를 날짜랜덤.xlsx라는 엑셀 파일로 저장하는데, openpyxl을 사용합니다.

날짜가 시,분,초까지 표시되고, 숫자는 저장하지를 않아서 다르지만, 참고로 보기 바랍니다.

데이터프레임을 엑셀로 저장한 경우

2. 실행 파일 만들기

그동안 pyinstaller를 사용했는데, nuitka(뉴트카)가 실행속도가 빠르다고 해서 해보는데, 다른 실행파일 생성 도구와 비교할 때 장,단점은 아래와 같습니다.

가. Python 실행파일 생성 도구 정리

도구특징장점단점
PyInstaller스크립트를.exe로 묶음사용 쉽고 자료 많음, GUI/CLI 모두 지원일부 대형 라이브러리(TensorFlow 등) 복잡, 초기 실행 느림
cx_Freeze비슷하게 스크립트 묶음안정적, 초기 실행 빠름설정 조금 까다로움
Nuitka파이썬을 C로 변환 후 컴파일실행 속도 빠르고 네이티브, 소스 보호빌드 느림, 대형 프로젝트는 빌드 복잡
py2exe윈도우 전용단순 CLI/GUI에 가벼움윈도우 전용, 유지보수 제한적
UV (UltraViolet / uv-py?)최근 언급되는 Python 컴파일러비교적 빠름, 단일 실행파일 생성 가능자료가 적고 안정성 검증 필요

PC에 파이썬 버전이 여러 개 있을 경우 실행 방법이 다르다는 것을 이제야 알았습니다.

나. nuitka로 바로 실행하기

nuitka –standalone –onefile df_base.py
로 바로 실행하니 venv는 3.12.6인데

가상환경의 파이썬 버전이 3.12.6임

Nuitka: Version ‘2.7.13’ on Python 3.13 … 이라고 파이썬 버전 3.13에서 실행됩니다.

nuitka로 바로 실행하니 python 3.13에서 실행됨

그래서 계속 3.13에서 실행 파일 만드면 openpyxl 모듈이 없다는 에러 메시지가 떠서

python 3.13에서 nuitka로 실행 파일을 만들고 실행하니 openpyxl 에러 발생함

Ctrl + C키를 눌러 실행을 중단시켰습니다.

다. python -m nuitka로 실행하기

그런데 python -m 다음 nuitka 명령을 넣어 실행하니

python -m nuitka로 실행하니 venv의 파이썬 버전과 일치되게 실행됨

python 버전이 3.13이 아니라 3.12로 바뀝니다.

라. 실행 파일 실행

그리고, 실행하니 아무런 에러 메시지 없이 잘 됩니다.

nuitka로 에러 없이 실행결과를 보여주는 화면

마. 코드 수정

python 3.13 버전에서 컴파일시 openpyxl 라이브러리를 import하려면 import openpyxl이 있어서 한다고 해서 넣었었는데, python 3.12에서는 필요 없어서 뺐고,

df.to_excel(‘날짜랜덤.xlsx’,  engine=”openpyxl”)에서도
openpyxl을 강제로 import하도록 넣어야 한다고 해서 넣고 컴파일 했었는데, 3.13에서는 결국은 안되고, 3.12에서는 필요가 없어서 뺐습니다.

바. pyinstaller는 .\.venv\pyinstaller로 실행

아무 생각없이 pyinstaller를 pyinstaller -F -w df_base.py로 실행하니 아래와 같은 에러가 발생해서

pyinstaller를 가상환경은 3.12이고, 3.13에서 실행 후 오류 화면

컴파일 과정을 살펴보니 마찬가지로 python 버전이 3.13으로 실행됐네요.

python 3.12가상 환경에서 pyinstaller 바로 실행시 python 3.13에서 실행됨

그런데 nuitka와 마찬가지로 python -m pyinstaller -F -w df_base.py라고 하니
pyinstaler가 있는데, pyinstaller 모듈이 없다고 나옵니다.

pyinstaller를 python -m으로 실행시 오류 화면

그래서 .venv\Scripts\pyinstaller.exe -F -w df_base.py
로 해야 합니다.

pyinstaller를 가상환경에서 실행하기

경우에 따라 많이 다르네요.

그리고, 또 하나의 차이점은 pyinstaller의 경우는 .\dist 폴더에 실행파일이 생기는데,

nuitka의 경우는 .py 폴더에 생깁니다.

‘import “pandas” could not be resolved from source’ error

이전에는 문제 없었던 것인데, 오랫만에 실행했더니 ‘import “pandas” could not be resolved from source’ 에러가 나오고, pandas와 numpy 아래에 노란색 물결이 그려져 있습니다. 이럴 경우 해결 방법을 알아보려고 합니다. 이미 문제가 있으므로 “Ⅰ. 원인 찾기”는 하지 않아도 됩니다.

(import “pandas” 에러 화면)

import pandas could not be resolved from source

(pandas와 numpy 아래 노란색 물결 표시)

pandas와 numpy에 오류 물결 표시

Ⅰ. 원인 찾기

1. 현재 실행되는 Python 확인

가. 첫번째 방법

where python   # 윈도우

출력 예시 :

python 위치 찾기

나. 두번째 방법

python -c "import sys; print(sys.executable)"

을 입력하면 실제 실행되는 Python 경로가 나옵니다.

출력 예시 :

python 경로 찾는 두번째 방법

2. pip이 설치한 Python 확인

pip -V

출력 예시 :

pip와 관련된 파이썬 버전 확인

여기서 (python 3.12)이 실제 설치된 Python 버전입니다.

3. 경로가 같은지 비교

  • sys.executable로 확인한 경로와
  • pip -V의 마지막에 나온 Python 경로

👉 이 둘이 같아야 하는데, 위와 같이 실행 파일의 버전은 3.13인데, pip이 설치한 버전은 3.12으로 다릅니다.

Ⅱ. 파이썬 버전이 다를 경우 조치 방법

가장 깔끔하게 해결할 수 있는 방법은 “가상환경 새로 만들기”입니다.

1. 현재 .venv 삭제

.venv 폴더 찾기

마우스 우클릭 → 삭제(Delete)

2. 프로젝트 루트에서 새 가상환경 생성:

python -m venv .venv

3. 가상환경 활성화:

.\.venv\Scripts\activate

4. pandas 설치:

python -m pip install --upgrade pip
pip install pandas

pip install pandas를 하면 numpy도 같이 설치되기 때문에 numpy는 따로 설치할 필요가 없습니다.

이제 노란색 물결도 없어지고, 실행도 잘 됩니다.

Offline PC 파이썬 환경 설정

파이썬은 관련 라이브러리를 온라인으로 가져와 설치하는데, Offline PC는 인터넷이 안돼서 포기하고 있었는데, 방법을 알게 되어 공유합니다. VS Code와 Python 설치, VS Code Extension과 파이썬 관련 라이브러리 설치 세 가지로 나뉩니다.

1. VS Code와 Python 설치 파일 다운로드 및 설치

가. 설치 파일 다운로드

설치를 해야 하니 온라인 PC에서 설치 프로그램을 다운 받아 오프라인 PC로 옮기로 실행하면 간단히 끝납니다.

VS Code 설치 사이트는 https://code.visualstudio.com/이고,

VS Code 다운로드 사이트

파이썬 설치 사이트는 https://www.python.org/downloads/입니다.

python download 사이트

나. 오프라인 PC로 전송

USB를 이용하던, 다른 전송 방법을 사용하던 설치 파일을 오프라인 PC로 옮깁니다.

다. 설치

VS Code 설치는 어렵지 않은데,

파이썬 설치시 주의할 점은 설치 초기 화면에 “Add Python.exe to PATH” 부분에 체크가 되어 있지 않은데, 반드시 체크하고 설치해야 한다는 점입니다. 물론 나중에 할 수도 있지만 설정 방법이 어렵기때문입니다.

python 설치 시 'Add python.exe to PATH' 설정 화면

2. VS Code Extension 설치

가. Python 익스텐션 다운로드

파이썬을 편리하게 사용하기 위한 최소 익스텐션은 Python입니다. 이건 파이썬 설치 파일이 아니므로 1번에서 설치하는 파이썬과는 다르며, 이것만 있다고 파이썬 실행이 되는 것이 아닙니다.

https://www.vsixhub.com/vsix/12/#google_vignette 사이트에서 파이썬 익스텐션 VSIX 파일을 다운로드 받을 수 있습니다.

위 사이트로 접속한 후 아래로 내려가면 ‘Download Latest VSIX File 버튼이 있으므로 이걸 눌러 익스텐션 설치 파일을 다운로드 받습니다.

VSIX 사이트의 'Download Latest VSIX File 메뉴

나. 오프라인 PC로 옮김

VS Code를 실행하고 왼쪽 아이콘에서 Extensions를 선택하고

Extensions: Marketplace 오른쪽의 점 3개를 누르면 맨 아래에 ‘Install from VSIX…’ 메뉴가 보이므로 이걸 누른 다음

VS Code의 'Install from VSIX 메뉴

오프라인에 저장된 폴더를 지정하면 설치가 진행됩니다.

이 정도면 될 것 같은데, 다른 것이 필요하다면 위 사이트에서 맨 위 메뉴에서 ‘Top Extensions’ 메뉴를 클릭한 다음 원하는 것을 다운로드 받아 설치하면 됩니다.

VSIX Top Extensions 메뉴

아래로 내려가면 Jupyter Notebook ‘Download VSIX‘ 버튼이 있습니다.

Jupyter noterbook VSIX 파일 다운로드 사이트

3. Python 관련 라이브러리 설치

많이 사용하는 파이썬 라이브러리로는 Pandas, Numpy, Openpyxl, Matplotlib, Seaborn 등등이 있습니다.

인터넷이 지원된다면

pip install pandas numpy openpyxl matplotlib seaborn

으로 한번에 설치할 수 있는데, 안된다면 매우 불편하지만 관련 whl(wheel) 파일을 다운로드 받아 설치해야 합니다.

하나만 해보면 나머지는 동일하게 하게 됩니다.

가. pansdas 라이브러리 파일 다운로드

폴더를 하나 만들어 거기에 관련된 whl 파일을 모두 넣은 다음 압축 파일로 만들어 넘기는 것이 좋습니다.

① 명령 프롬프트 창을 띄웁니다.

검색 창에 cmd라고 입력하면 명령 프롬프트가 표시되므로 클릭하면 됩니다.

명령 프롬프트 실행하기

② 폴더(디렉토리)를 만들고, 그 폴더로 이동하기

cd 명령을 이용해서 원하는 폴더로 이동한 다음

md wheel (wheel 폴더를 만듦)

cd wheel (wheel 폴더로 이동)

③ pip download pandas

pip download pandas 라고 입력하면 pandas만 다운로드 하는 것이 아니라 관련된 라이브러리를 함께 다운로드 받습니다.

맨 아래 줄에 “Successfully downloaded pandas numpy python-dateutil pytz six tzdata”라고 되어 있어 numpy도 포함되어 있습니다.

pandas 관련 wheel 파일을 다운로드 받기

이런 식으로 openpyxl 등 필요한 라이브러리를 모두 다운도드 받는데,

여러 개를 한꺼번에 다운로드 받으려면

pip download pandas openpyxl

이라고 하면 됩니다.

④ wheel 폴더 내의 파일들을 압축합니다.

pandas 관련 다운로드된 wheel 파일

나. 오프라인 PC로 옮긴 다음 설치하기

압축 파일을 오프라인 PC로 옮긴 다음

압축을 풀고 그 폴더에서

pip install –no-index –find-links=./ pandas openpyxl

식으로 관련 라이브러리명을 한꺼번에 지정해서 설치할 수 있고,

pandas 라이브러리만 설치한다고 하면

pip install –no-index –find-links=./ pandas

라고 하면 됩니다.

pandas 라이브러리 설치 화면

이미 설치되어 있기 때문에 Requirement already satisfied라고 나왔지만 설치되어 있지 않다면 성공적으로 인스톨됐다는 메시지가 나올 것입니다.

다. 주의할 점

python 버전이 맞지 않으면 아래와 같은 에러가 발생하므로

python 버전 불일치로 pip install 실패 화면

python -V 를 실행해서 파이썬 버전을 확인한 후

pip download pandas –only-binary=:all: –python-version 312 –platform win_amd64

라고 python 버전, 여기서는 3.12를 312로 지정한 후 whl 파일을 다운로드 받아야 합니다.

가로 또는 전체 영역 기준으로 중복된 값 세기(여러 열)

일정 영역에 배치되어 있는 숫자의 중복 개수를 가로 또는 전체 영역을 기준으로 세는 것을 해보도록 하겠습니다.

1. 가로로 중복 개수 세기

아래와 같이 숫자가 10개씩 10줄 있을 때 가로로 중복 값이 있는지 세보겠습니다.

M4셀에 =COUNTIF(C4:L4,C4:L4)라고 입력하면 한 행에서 중복된 숫자의 개수를 알려줍니다.

다시 말해 세번째인 51이 2번 나옵니다.

그러면 중복된 숫자만 표시해보겠습니다.

M4셀에서

=TEXTJOIN(“, “,,IF(COUNTIF(C4:L4,C4:L4)>1,C4:L4,””))

라고 입력하고 M4셀의 채우기 핸들을 더블 클릭하면 51과 26만이 한 행에 중복된 것을 알 수 있습니다.

2. 전체 영역에서 중복 개수 세기

이번에는 전체 영역에서 숫자가 얼마나 중복되는지 알아보겠습니다.

가. 전체 영역에서 중복된 숫자의 개수 세기

C15셀에 =COUNTIF(C4:L13,C4:L13)라고 입력하면

해당 셀의 숫자가 지정된 영역에 몇 개가 있는지 알려줍니다.

예를 들어 20이란 숫자는 C4셀과 H6셀 두 군데 있고, 가장 큰 숫자인 7에 해당하는 숫자인 36은 F2, D3, H4, H6, H8, H9, H10에 있습니다.

(나) 중복 개수가 4이상인 숫자 표시하기

위 표에서 4이상인 숫자를 표시해보겠습니다.

=IF(COUNTIF(C4:L13,C4:L13)>=4,C4:L13,””)

라고, 숫자가 4이상 경우 값을 표시하고, 아니면 공백을 표시하도록 하면 아래와 같이 4이상 숫자만 표시됩니다.

(다) 행별로 4이상인 숫자의 개수 표시하기

이번에는 위 숫자를 행 별로 몇 개인지 N열에 표시하도록 하겠습니다.

N4셀에 =COUNT(C15:L15)라고 입력하고, 채우기 핸들을 더블 클릭하면 1, 6, 1, … 식으로 값이 표시됩니다.

(라) 행별로 숫자가 4이상인 경우 O, 아니면 X 표시하기

N4셀에 =IF(COUNT(C15:L15)>=4,”O”,”X”)

라고 해서 O, X를 표시할 수 있습니다.

(5) ByRow 함수로 한 줄로 표시하기

ByRow 함수를 이용해 C15셀의 수식과 N3셀의 수식을 묶으면 한 번에 N4셀부터 N13셀까지의 값을 구할 수 있습니다.

수식은 아래와 같습니다.

=BYROW(IF(COUNTIF(C4:L13,C4:L13)>=4,C4:L13,””),LAMBDA(r,IF(COUNT(r)>=4,”O”,”X”)))

IF(COUNTIF(C4:L13,C4:L13)>=4,C4:L13,””)를 Lambda 함수에서 r로 받아, count함수를 이용해 4이상이면 O, 아니면 X를 표시하는데, 동적 배열 수식이라 채우기 핸들을 누르지 않더라도 한 번에 값이 구해집니다.

세로로 중복된 값 다루기

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이 구해졌습니다.