ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

¦h±ø¥ó¤º´¡ªk¬d¸ß

1 ¥ý¨Ï¥Î ¤½¦¡ - ©w¸q¦WºÙ (³»ºÝ)
2 ª½±µ¨Ï¥Î¤º´¡­pºâ¦¡
J8 =-LOOKUP(,-TEXT(IF((À£¤O½d³ò=I$2)*(¶ZÂ÷=I$3),(OFFSET(Amp,1,)-Amp)/(OFFSET(Type,1,)-Type)*(SMALL(QUARTILE(IF((À£¤O½d³ò=I$2)*(¶ZÂ÷=I$3),Type),{0,4})*{1;1;0}+I8*{0;0;1},3)-Type)+Amp,""),"[<"&Amp&"] ;[>"&OFFSET(Amp,1,)&"] ;0.0"))
°}¦C¿é¤J¤½¦¡
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2021-2-8 11:04 ½s¿è

ºë²¤@¤UÃä¬É­pºâ¦¡
J8 =-LOOKUP(,-TEXT(IF((À£¤O½d³ò=I$2)*(¶ZÂ÷=I$3),(OFFSET(Amp,1,)-Amp)/(OFFSET(Type,1,)-Type)*(MEDIAN(QUARTILE(IF((À£¤O½d³ò=I$2)*(¶ZÂ÷=I$3),Type),{0,4}),I8)-Type)+Amp,""),"[<"&Amp&"] ;[>"&OFFSET(Amp,1,)&"] ;0.0"))
°}¦C¤½¦¡

­n³t«×§ÖÁÙ¬O2¼Óªº¤½¦¡¦n
¦n¤[¨S¦³°Ê¸£µ¬¡A¼g¤@­Ó¸ò2¼Ó¤£¦P­pºâ¤½¦¡µ¹¤j®a°Ñ¦Ò
¤½¦¡ =(Amp2 - Amp1) / (Type2 - Type1) * (Type(i) - Type1) + Amp1
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¤w¸g¦bI4/J4 ¥ý­pºâ¥X³Ì¤p/³Ì¤jType¡A¥i¦AÀu¤Æ
1 ¨Ï¥Î MATCH(V, Array, 1)¨ç¼Æ®É¡AType¼Æ­È¤Ó¤p®É»Ý­n¶i¦æ¸É©³  MAX(I$4,I8)
2 ¨Ï¥Î TREND()¨ç¼Æ®É¡A­Y¬OType¼Æ­È¤Ó¤jor¤Ó¤p¡A«h±a¤Jªí³æ³Ì¤jor³Ì¤pType¹ïÀ³ªºAmp­È¡A¦¹®É»Ý­n­×¥¿Type¤£¶W¹L³Ì¤jor¤£©³©ó³Ì¤p¡C

I8 =IF(I8=0,"",TREND(OFFSET(F$5,MATCH(MAX(I$4,I8),E$6:E$111/(C$6:C$111=I$2)/(D$6$111=I$3)),,2),OFFSET(E$5,MATCH(MAX(I$4,I8),E$6:E$111/(C$6:C$111=I$2)/(D$6$111=I$3)),,2),MEDIAN(I$4,I8,J$4)))
°}¦C¿é¤J
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¤w¸g¦bI4/J4 ¥ý­pºâ¥X³Ì¤p/³Ì¤jType¡A¥i¦AÀu¤Æ
1 ¨Ï¥Î MATCH(V, Array, 1)¨ç¼Æ®É¡AType¼Æ­È¤Ó¤p®É»Ý­n¶i¦æ¸É©³  MAX(I$4,I8)
2 ¨Ï¥Î TREND()¨ç¼Æ®É¡A­Y¬OType¼Æ­È¤Ó¤jor¤Ó¤p¡A«h±a¤Jªí³æ³Ì¤jor³Ì¤pType¹ïÀ³ªºAmp­È¡A¦¹®É»Ý­n­×¥¿Type¤£¶W¹L³Ì¤jor¤£©³©ó³Ì¤p¡C

I8 =IF(I8=0,"",TREND(OFFSET(F$5,MATCH(MAX(I$4,I8),E$6:E$111/(C$6:C$111=I$2)/(D$6:D$111=I$3)),,2),OFFSET(E$5,MATCH(MAX(I$4,I8),E$6:E$111/(C$6:C$111=I$2)/(D$6:D$111=I$3)),,2),MEDIAN(I$4,I8,J$4)))
°}¦C¿é¤J
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ­n¥Î¤ß¡A¤£­n¾Þ¤ß¡B·Ð¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD