$B%(%/%;%k(B $B%j%9%H(B05
$B%(%/%;%k$NM}2r%7%j!<%:(B
$B#5!%%G!<%?$NF~NO5,B'$N%j%9%H$G!V85$NCM!W$K;HMQ$9$k4X?t$rM}2r$9$k(B
$B#5!]#4!%(BMATCH$B4X?t(B
$B!JBh0lCJ3,(B
$B!'!K(B
$B%j%9%H$NCf$G!"8!:w$r9T$&:]$K!"(B
$B!V2?$N!W(B $B!V2?$r!W(B $B8!:w$7$?$$$H$$$&>l9g$,$"$j$^$9!#(B
$B!V2?$N!W(B $B$KAjEv$9$kBP>]$r!V8!::CM!W(B $B$H8@$$$^$9!#(B
$B!V2?$N!W(B $B$rF~NO$7$?%;%kHVCO$r(B
$B!V8!::CM!W(B $B$H$7$F;XDj$7$^$9!#(B
$B!JBhFsCJ3,(B $B!'!K(B
$B!V2?$N!W(B $B$KAjEv$9$kBP>]!JCM!K$,B8:_$9$k(B
$B%j%9%H$N!VNs!W$N%;%kHO0O$NHVCO$r(B
$B!V8!::HO0O!W(B $B$H$7$F;XDj$7$^$9!#(B
$B!JBh;0CJ3,(B $B!'!K(B
$BJB$YBX$($r$7$J$$$G!"8!:w$9$k$?$a$K!"!V#0!J%<%m!K!W$r;XDj$7$^$9!#(B
$B$3$l$r!V>H9g$N7?!W$H8@$$$^$9!#(B
MATCH$B4X?t$NL?Na$r
$B>H9g$N7?(B$B$K=>$C$F(B
$B8!::HO0O(B$BFb$r8!:w$7!"(B
$B8!::CM(B$B$H0lCW$9$kBP>]$N!"(B
$BHO0OFb$G$NAjBPE*$J0LCV$rI=$9?t(B$BCM(B$B$rJV$7$^$9!#(B
$B$D$^$j!"!V2?$N!W(B $B$,4^$^$l$F$$$k9`L\Ms!&Ns!J%U%#!<%k%I!K$G!"(B
$B!V2?$N!W(B $B$KAjEv$9$kBP>]!JCM!K$K!H0lCW!I$9$kBP>]!JCM!K$O(B
$B$=$N9`L\HO0O$NCf$G>e$+$i2?HVL\$KEv$?$k$N$+$H$$$&(B
$B!V?t!W$rEz$($F$/$l$kLu$G$9!#(B
$B$3$N$3$H$O!"0z?t$H$7$F!V9THV9f!W$d!V9T?t!J$:$i$92s?t!K!W$r;XDj$9$k(B
$BI,MW$,$"$k4X?t$K$*$$$FMxMQ$,=PMh$k$H$$$&$3$H$G$9!#(B
$B=q<0$rJ,2r$7$FM}2r$7$^$7$g$&!#(B
|
MATCH |
( |
$B8!::CM(B, |
$B8!::HO0O(B, |
$B>H9g$N7?(B |
) |
||||||||||||||||||||||||||||||||||||
|
|
|
$B8!:w$9$kCM$N%;%kHVCO(B |
$B8!:w$9$kCM$,4^$^$l$F$$$kNs$N%;%kHO0O$NHVCO(B |
$BJB$YBX$($r$7$J$$(B |
|
||||||||||||||||||||||||||||||||||||
|
$BNc(B |
( |
$B#E#2(B$B!$(B |
$B#A#2(B:$B#A#5(B$B!$(B |
$B#0(B |
) |
||||||||||||||||||||||||||||||||||||
|
$B0UL#(B |
$B!'(B |
$BJB$YBX$($r$7$J$$$G!"(B |
|
||||||||||||||||||||||||||||||||||||||
|
$B?^Nc(B |
|
||||||||||||||||||||||||||||||||||||||||
|
$B7k2L(B |
$BHO0OFb$G$NAjBPE*$J0LCV$rI=$9?tCM(B$B!V#3!W$rJV$7$^$9!#(B |
|
|||||||||||||||||||||||||||||||||||||||
MATCH$B4X?t$r;HMQ$7$?(B$B8!:w4X?t$r$b$&0lEY8+$F$_$^$7$g$&!#(B
$BFCDj$N%;%kHO0O$G%G!<%?$r8!:w$9$kJ}K!$N$^$H$a(B
$B6LG,$NG/Np$r8!:w$9$kJ}K!(B
|
|
A |
B |
C |
D |
E |
|
1 |
$BL>A0(B |
$BIt=p(B |
$BG/Np(B |
|
$B8!:w$9$kCM(B |
|
2 |
$B;30*(B |
$B1D6H(B |
28 |
|
$B6LG,(B |
|
3 |
$B@8U*(B |
$BAmL3(B |
19 |
|
|
|
4 |
$B6LG,(B |
$B7PM}(B |
22 |
|
|
|
5 |
$B>.>>:Z(B |
$B3+H/(B |
29 |
|
|
|
|
$B4X?t$N9=J8!J=q<0!K(B |
|
$B8!(B |
=INDEX(A2:C5,MATCH(E2,A2:A5,0),3) |
|
$B$:(B |
=OFFSET(A1,MATCH(E2,A2:A5,0),2) |
INDEX$B4X?t$G$b!"(BOFFSET$B4X?t$G$b(B
MATCH$B4X?t$NCf?H!J0z?t!K$OF1$8$G$9!#(B
$B$I$A$i$N4X?t$r;HMQ$7$F$b!"G/Np$N!V#2#2!W$rJV$7$^$9!#(B
INDEX$B4X?t$G$O!"(B
|
|
A |
B |
C |
D |
E |
|
1 |
$BL>A0(B |
$BIt=p(B |
$BG/Np(B |
|
$B8!:w$9$kCM(B |
|
2 |
$B;30*!!(B1 |
$B1D6H(B |
28 |
|
$B6LG,(B |
|
3 |
$B@8U*!!(B2 |
$BAmL3(B |
19 |
|
|
|
4 |
$B6LG,!!(B3 |
$B7PM}(B |
22 |
|
|
|
5 |
$B>.>>:Z(B |
$B3+H/(B |
29 |
|
|
$BG[Ns(B$B!J%;%kHO0O!K$K(B$B#A#2!'#C#5(B$B$r;XDj$7$F$$$^$9$N$G!"(B
$B"-(B
$B9THV9f(B$B!V#3!W$O(B$B#A#4!'#C#4(B$B$rG'<1$5$;$^$9!#(B
$B"-(B
$B$=$7$F!"(B$BNsHV9f(B$B$r!V(B$B#3(B$B!W$H$7$F$$$^$9$N$G!"(B
$B"-(B
$B#C#4%;%k(B$B$rG'<1$5$;$k$3$H$K$J$j!"(B
$B"-(B
$B$=$NCM!J#2#2!K$,La$jCM$K$J$j$^$9!#(B
OFFSET$B4X?t$G$O!"(B
|
|
A |
B |
C |
D |
E |
|
1 |
$BL>A0(B |
$BIt=p(B |
$BG/Np(B |
|
$B8!:w$9$kCM(B |
|
2 |
$B;30*(B |
$B1D6H(B |
28 |
|
$B6LG,(B |
|
3 |
$B@8U*(B |
$BAmL3(B |
19 |
|
|
|
4 |
$B6LG,(B |
$B7PM}(B |
22 |
|
|
|
5 |
$B>.>>:Z(B |
$B3+H/(B |
29 |
|
|
$B4p=`(B$B$H$7$F(B$B#A#1%;%k(B$B$r;XDj$7$F$$$^$9$N$G!"(B
$B"-(B
$B9T?t!V#3!W$O!V2<$K#32s$:$i$7$?!W(B$B#A#4%;%k(B$B$rG'<1$5$;$^$9!#(B
$B"-(B
$B$=$7$F!"(B$BNs?t(B$B!J@5$N>l9g!"1&$X$:$i$92s?t!K$r!V#2!W$H$7$F$$$^$9$N$G!"(B
$B"-(B
$B#C#4%;%k(B$B$rG'<1$5$;$k$3$H$K$J$j!"(B
$B"-(B
$B$=$NCM!J#2#2!K$,La$jCM$K$J$j$^$9!#(B
MATCH$B4X?t$G!V#3!W$rJV$7$F$$$^$9$,!"(B
$B$=$NLa$jCM!V#3!W$r(B
INDEX$B4X?t$H#O#F#F#S#E#T4X?t$G$O!"(B
$B$=$N0UL#9g$$!"5Z$SMxMQJ}K!$,0[$J$k$3$H$rM}2r$7$F2<$5$$!#(B
|
$B"((B |
$B0z?t$H$7$F!"!V(B$B9THV9f!W$d!V9T?t!W$N?tCM$r;XDj$9$k!"B>$N4X?t$N0z?t$H$7$FJ#9g!J%M%9%H!K$7$FMxMQ$9$k$H!"<+F0E*$K?tCM$rF~$l$F$/$l$kF/$-$r$9$k4X?t$@$HM}2r$7$F2<$5$$!#(B |
||
|
|
|
||
|
$BCOUNTA$B4X?t$K$D$$$F$4>R2p$7$^$9!#(B |
|||
|
|
|||
|
|
|
|
|