충전이후 운행시간 구하기

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

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으로 나누는 에러 표시

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

구조체와 튜플을 조합한 데이터 모델링

Rust에서 구조체(struct)와 튜플(tuple)을 조합해 복잡한 데이터 모델링을 하는 방법은, 각 자료구조의 장점을 살려 중첩(nesting)하거나, 서로 포함시켜 계층적인 구조를 만드는 것입니다. 이렇게 하면 의미 있는 필드(구조체)와 위치 기반 데이터(튜플)를 효과적으로 결합할 수 있습니다.

실제 프로젝트에서는 구조체로 주요 엔티티(예: 사용자, 상품, 센서 등)를 정의하고,
구조체의 일부 필드를 튜플로 선언해 위치, 좌표, 설정값 등 간단한 데이터를 묶어 표현하거나, 구조체로 선언해서 의미를 명확히 합니다.

1. 구조체 안에 튜플을 포함하는 예시

struct Employee {
name: String,
age: u32,
// (연, 월, 일) 생년월일을 튜플로 표현
birth_date: (u16, u8, u8),
}

fn main() {
let emp = Employee {
name: String::from("Kim"),
age: 28,
birth_date: (1997, 5, 14),
};
println!("{}의 생년월일: {}-{}-{}", emp.name, emp.birth_date.0, emp.birth_date.1, emp.birth_date.2);
}
  • 구조체는 필드의 의미를 명확히하고, 튜플은 간단한 데이터 묶음에 적합합니다.
  • Emplyee라는 구조체를 선언(정의)하면서 필드명과 형식을 지정하는데, birthdate는 생년월일의 연,월,일을 튜플 형식으로 지정한 것입니다.
  • let 문을 이용해서 Employee의 instance를 생성하고, 여기서는 emp, 출력할 때는 emp를 이용해서 emp.필드명 식으로 하면 되는데, 튜플 타입은 emp.필드명 다음에 튜플이므로 index를 붙여서 emp.birth_date.0, .1, .2식으로 표현합니다.
  • 출력값은 Kim의 생년월일: 1997-5-14입니다. 두 자릿수로 출력하려면 {:02}로 수정하면 됩니다. 두 자릿수로 출력하는데, 부족하면 0으로 채우라는 의미입니다.

2. 튜플 안에 구조체를 포함하는 예시

struct Product {
id: u32,
name: String,
}

fn main() {
// (상품, 수량) 형태로 장바구니 항목 표현
let cart_item: (Product, u32) = (
Product { id: 1, name: String::from("Book") },
3,
);
println!("{}: {}개", cart_item.0.name, cart_item.1);
}
  • 튜플로 여러 정보를 임시로 묶되, 각 요소가 구조체라면 의미를 명확히 할 수 있습니다.
  • cart_item을 튜플 형식으로 지정해서 Product와 수량을 받는데, Product를 구조체와 연결해서 id와 name으로 의미를 명확히하는 것입니다.
  • 튜플 속에 구조체가 들어있으므로 출력할 때 cart_item 다음에 인덱스를 적고, 구조체의 필드명을 적어서 표시합니다. 예) cart_item.0.id, cart_item.0.name, cart_item.1
  • 출력 결과는 ‘Book: 3개’입니다.

3. 중첩 구조체와 튜플을 활용한 복합 모델

struct Address {
city: String,
zip: String,
}

// (위도, 경도) 위치 정보를 튜플로 표현
struct Store {
name: String,
address: Address,
location: (f64, f64),
}

fn main() {
let store = Store {
name: "Rust Mart".to_string(),
address: Address {
city: "Seoul".to_string(),
zip: "12345".to_string(),
},
location: (37.5665, 126.9780),
};
println!("{} ({}, {}) - 위치: ({}, {})",
store.name, store.address.city, store.address.zip, store.location.0, store.location.1
);
}
  • Address 구조체를 정의한 다음 Address 구조체를 Store의 address 필드의 type으로 사용하고, Store의 location은 위도와 경도를 튜플 형식으로 정의했습니다.
  • 따라서, Store 구조체의 인스턴스를 만들 때도 address를 Address 구조체로 입력하고, location은 위도와 경도를 튜플 형식으로 입력했습니다.
  • 그리고, 출력할 때는 인스턴스명.필드명인데, address는 구조체이므로 다시 한번 더 필드명을 적어주었고, tuple 타입은 필드명 다음에 인덱스를 추가했습니다.
  • 출력 결과는 ‘Rust Mart (Seoul, 12345) – 위치: (37.5665, 126.978)’입니다.

4. 튜플 구조체와 일반 구조체 조합

struct Point(i32, i32, i32);

struct Sensor {
id: u32,
position: Point,
}

fn main() {
let sensor = Sensor { id: 101, position: Point(10, 20, 30) };
println!("센서 {} 위치: ({}, {}, {})", sensor.id, sensor.position.0, sensor.position.1, sensor.position.2);
}
  • 이번에는 튜플 구조체를 정의한 다음, 일반 구조체의 타입으로 사용한 예입니다.
  • 일반 구조체의 타입이 튜플이냐 아니냐만 다를 뿐 표현하는 방식은 위와 동일합니다.

이처럼 구조체와 튜플을 조합하면 복잡한 데이터도 명확하고 효율적으로 모델링할 수 있습니다.

  • 구조체는 필드의 의미와 계층 구조를,
  • 튜플은 간단한 값 묶음이나 위치 기반 데이터를 담당하게 하여,
  • 코드의 가독성과 확장성을 모두 높일 수 있습니다

5. 튜플 구조체로 타입 구분

struct Point(i32, i32, i32);
struct Color(i32, i32, i32);

fn draw_sphere(center: Point, color: Color) {
// center와 color가 같은 (i32, i32, i32) 구조지만, 타입이 달라 혼동 방지
// This function would contain logic to draw a sphere at the given center
// with the specified color.

println!("Drawing sphere at center: ({}, {}, {}) with color: ({}, {}, {})",
center.0, center.1, center.2,
color.0, color.1, color.2);
}

fn main() {
let center = Point(0, 0, 0);
let color = Color(255, 0, 0); // Red color

draw_sphere(center, color);
}
  • 위와 같이 구조체를 튜플 형식으로 지정하면 draw_sphere 함수에서 입력 타입이 구조체 형식과 맞는지 체크하는데,
  • 아래와 같이 함수의 인수를 튜플 형식으로 지정하면 둘 다 튜플 형식이기 때문에 center 자리에 Point 구조체 타입이 아닌 color 튜플을 넣어도 맞는 타입인지 체크를 못합니다.
  • 튜플 구조체(예: struct Point(i32, i32, i32);)를사용하면,
    동일한 데이터 구조라도 타입별로 구분할 수 있어 실수 방지 및 타입 안전성을 높입니다.
fn draw_sphere(center: (i32, i32, i32), color: (i32, i32, i32)) {
...
}

fn main() {
let center = (0, 0, 0);
let color = (255, 0, 0); // Red color

draw_sphere(color,center);
}

6. 함수 반환값 및 임시 데이터

함수에서 여러 값을 반환할 때 튜플을 사용하고,
이 반환값을 구조체의 필드로 저장하거나, 여러 구조체 인스턴스를 튜플로 묶어 일시적으로 처리할 수 있습니다.

fn min_max(numbers: &[i32]) -> (i32, i32) {
let min = *numbers.iter().min().unwrap();
let max = *numbers.iter().max().unwrap();
(min, max)
}

struct Stats {
min: i32,
max: i32,
}

fn main() {
let numbers = [3, 7, 2, 9, 4];
let (min, max) = min_max(&numbers);

let stats = Stats { min, max };
println!("최솟값: {}, 최댓값: {}", stats.min, stats.max);
}
  • let (min, max) = min_max(&numbers);
    => numbers 배열을 참조로 가져와서 min_max 함수를 처리한 다음 결괏값을 min, max 튜플에 넣고,
  • let stats = Stats { min, max };
    => min과 max를 Stats 구조체에 넣어 stats 인스턴스(또는 변수)를 만듭니다.
    min: min, max: max라고 쓰는 것이 정석이지만 필드명과 변수명이 같기 때문에 필드명만 적으면 됩니다.
  • 그리고, 인스턴스의 min과 max를 출력하는 것입니다.

7. 설정값, 좌표, 범위 등 불변 데이터 관리

고정된 설정값이나 좌표와 같이, 변경되지 않는 데이터는 튜플로 관리하고,
이 값을 구조체의 일부로 포함시켜 사용합니다.

struct DbConfig { 
host: String,
port: u16,
credentials: (String, String), // (username, password)
}

fn main() {
let db_config = DbConfig {
host: String::from("localhost"),
port: 5432,
credentials: (String::from("user"), String::from("password")),
};

println!("DB 호스트: {}", db_config.host);
println!("DB 포트: {}", db_config.port);
println!("DB 사용자명: {}", db_config.credentials.0);
println!("DB 비밀번호: {}", db_config.credentials.1);
}

8. 튜플과 달리 Struct는 메서드와 함께 활용

구조체에 메서드를 구현하여 데이터와 동작을 결합할 수 있습니다.
예를 들어, 2차원 평면상의 점(Point)에 대해 특정 축 위에 있는지 판별하는 메서드를 추가할 수 있습니다.

struct Point(f32, f32);

impl Point {
fn on_x_axis(&self) -> bool {
self.0 == 0.0
}
fn on_y_axis(&self) -> bool {
self.1 == 0.0
}
}

fn main() {
let point = Point(0.0, 0.0);
if point.on_x_axis() && point.on_y_axis() {
println!("원점에 있습니다.");
}
}
  • 구조체에 메서드를 추가해 객체 지향적으로 사용할 수 있습니다.
  • 구조체의 메서드를 만들려면 impl 구조체라고 명명하고, 그 안에서 함수(fn, 메소드)를 작성하는데, 첫번째 인수는 &self, 구조체 자체입니다.
  • fn on_x_axis(&self) -> bool은 구조체를 인수로 받아 bool 형식인 True, False를 반환합니다.
  • self.0 == 0.0
    => 세미콜론으로 끝나지 않으므로 반환값인 표현식으로 첫번째 튜플 값이 0.0인지 비교해서 같다면 True를 반환하고, 아니면 False를 반환하는 것입니다.
  • self.1 == 0.0는 튜플의 두번째 값이 0.0인지 비교하는 것입니다.
  • 출력값은 튜플의 값이 모두 0.0이므로 ‘원점에 있습니다.’입니다.

9. 요약

  • 튜플: 간단한 값 묶음, 여러 값 반환, 임시 데이터에 적합
  • 구조체: 명확한 의미의 데이터 구조, 필드 이름, 가독성·유지보수성 강조
  • 튜플 구조체: 같은 구조이지만 다른 의미의 타입 구분으로 타입 안전성을 강화