index match

(ΣHi) / (Σ(Hi/Vsi)) 조화평균(harmonic mean)

배열함수로 풀이

=ROUND(SUM(A2:A6)/SUM(A2:A6/B2:B6),0)

=ROUND(SUM(A2:A6)/SUMPRODUCT(A2:A6/B2:B6),0)

수식 입력 후 Ctrl + Shift + Enter

배열함수

1의 자리의 수가 2이하는 버리고, 2초과는 올림

값이 양수일 경우

=IF(MOD(A1,10)<=2,A1-MOD(A1,10),A1-MOD(A1,10)+10)

=IF(MOD(A1,10)<=2,INT(A1/10)*10,(INT(A1/10)+1)*10)

값이 음수일 경우 고려하기

=SIGN(A1)*IF(MOD(ABS(A1),10)<=2,INT(ABS(A1)/10)*10,(INT(ABS(A1)/10)+1)*10)

$KM$11:$OE$11영역에서 양의 값만 있는 셀만 범위로 설정하고 싶어.

KM11:INDEX(11:11,LOOKUP(2,1/($KM$11:$OE$11>0),COLUMN($KM$11:$OE$11)))

이 수식은 KM11부터 시작해서 KM11:OE11 구간 중 양수(0보다 큰 값)가 있는 마지막 셀까지를 범위로 잡습니다.

구성 요소별 설명

KM11:
시작점을 의미합니다. 즉, 범위는 항상 KM11에서 시작합니다.

($KM$11:$OE$11>0)
KM11:OE11 각 셀이 0보다 큰지 확인해서 TRUE/FALSE 배열을 만듭니다. 양수만 골라내는 조건입니다.

1/($KM$11:$OE$11>0)
TRUE는 1, FALSE는 0이 되는데, FALSE는 1/0이 되어 오류가 납니다. 이 오류값은 무시되고, TRUE인 위치만 남는 방식으로 마지막 양수 위치를 찾는 데 활용합니다.

LOOKUP(2, ..., COLUMN($KM$11:$OE$11))
2라는 값은 배열 안에 없으므로, LOOKUP이 가장 마지막으로 유효한 값을 찾습니다. 그 결과 마지막 양수가 있는 열 번호를 돌려줍니다.

INDEX(11:11, ...)
찾은 열 번호에 해당하는 셀을 가리킵니다. 즉, 11행에서 마지막 양수 셀 위치를 반환합니다.

LambertW 함수

Function LambertW(z As Double) As Double

    Dim w As Double
    Dim i As Integer
    
    w = Log(z + 1)
    
    For i = 1 To 20
        w = w - (w * Exp(w) - z) / _
            (Exp(w) * (w + 1))
    Next i
    
    LambertW = w

End Function

7.4 Calculating the trajectory of quality points based on the principle of least action

2026.01.27 - [엑셀] - 엑셀로 과학기술계산

AGGREGATE

SUBTOTAL

2020.06.08 - [엑셀] - 목록이나 데이터베이스의 부분합을 반환하는 SUBTOTAL 함수

7. Optimisation problems using Excel Solver

7.3 Cycloid curve

https://youtu.be/4qtZcxb1eAM

https://youtu.be/-TDqr2iDXG8

주어진 곡선이

Z=aX^3+bX^2+cX+d

이고 다음 정보를 알고 있다.

• 점 P1 : (X1,Z1)
• 점 P2 : (X2,Z2)
• P1에서 접선 기울기 : m1
• P2에서 접선 기울기 : m2

이 4개의 조건으로 미지수 a,b,c,d 를 구할 수 있습니다.

1. 기본식 (곡선이 두 점을 지남)

Z= aX^3 + bX^2 + cX + d

따라서

P1 조건

aX_1^3 + bX_1^2 + cX_1 + d = Z_1

P2 조건

aX_2^3 + bX_2^2 + cX_2 + d = Z_2

2. 접선 기울기 조건

접선 기울기는 미분으로 구합니다.

따라서

P1에서

3aX_1^2 + 2bX_1 + c = m_1

P2에서

3aX_2^2 + 2bX_2 + c = m_2

3. 최종 연립방정식

정리하면 다음 4개 방정식입니다.

​

이것을 행렬 형태로 쓰면

​​​

따라서

로 계산할 수 있습니다.

두 점의 좌표 + 접선기울기로 곡선을 정의하는 방법이 Cubic Hermite interpolation (Hermite cubic) 입니다.

1. Hermite Cubic 기본식

두 점

• P_1(X_1, Z_1)
• P_2(X_2, Z_2)

접선 기울기

• m_1​ (P1에서)
• m_2​ (P2에서)

일 때 정규화 변수를 사용합니다.

​​

따라서

• t=0 → P1
• t=1 → P2

2. Hermite Cubic 공식

곡선은 다음 형태가 됩니다.

​

여기서 Hermite basis 함수는

3. 전체 공식

따라서 최종식은

​

4. 특징 (검증)

이 식은 자동으로 다음 조건을 만족합니다.

P1 통과

Z(X_1)=Z_1

P2 통과

Z(X_2)=Z_2

P1 기울기

​

P2 기울기

즉 4개 조건이 정확히 만족됩니다.

5. 계산 절차 (실무용)

1️⃣

L=X_2 - X_1

2️⃣

t=(X-X_1)/L

3️⃣ Hermite 식 계산