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

[µo°Ý] ¦p¦ó­pºâ¥X¨â¦C¦@¦Pªº©M

[µo°Ý] ¦p¦ó­pºâ¥X¨â¦C¦@¦Pªº©M

¥»©«³Ì«á¥Ñ K_Wing ©ó 2014-11-28 20:42 ½s¿è


ABCD
E FGH
1
·|ij«ÇA
·|ij«ÇB
·|ij«ÇC
®É¬q1
®É¬q2
¤H¼Æ
·|­û
«D·|­û
2
1


1

40
1

3

1

1

50

1
41



1
201
5


1

1
30
1

6
1


1

25

1
7

1


1
30
1

8

1


1
30
1

9


1

1
25

1


11 AB
C
D E
F
12
®É¬q1®É¬q2  
·|­û
«D·|­û
¦U·|ij«Ç¨Ï¥Î¤H¼Æ
13·|ij«ÇA





14·|ij«ÇB





15·|ij«ÇC





(¦]½s½s®É­­¤w¹L)¦b¦¹¸É¥R:
¤p§Ì¹ïExcelªº»{ÃÑ©|²L
¥i¯à³s¼ÐÃD·Qªí¹Fªº·N«ä¤]·|¥X¿ù
¦b¦¹»¡Án©êºp

·Q°Ý¥H¤W¹Ïªº¸ê®Æ
¦p¦ó¹B¥ÎFormula¦b¤U¹ÏªºªÅ®æ¤º
±N¨â¶µ¸ê®Æ¹Bºâ¥X¦¸¼Æ©M¨Ï¥Î¤H¼Æ
¦b¦¹¥ý¦æÁ¹L

TOP

¦^´_ 2# K_Wing


    ¥i¥H§âµª®×§¹¾ã§e²{¦b¤Uªí¸Ì¶Ü¡H³o¼Ë¤~¯à¤F¸Ñ§A·Q­n°µªk¨ì©³¬O¤°»ò¡C

TOP

¦^´_ 3# owen06
ÁÂÁ¤F¸Ñ¡G
¥[¤Jµª®×«á¦p¤Uªí¡G
"1"¬°¨Ï¥Î¦¸¼Æ

A
B
C
D
E
F
G
H
1 ·|ij«ÇA ·|ij«ÇB ·|ij«ÇC ®É¬q1 ®É¬q2 ¤H¼Æ ·|­û «D·|­û
2 1


1 40 1
3
1
1
50
1
4 1

1
   20
1

5

1
1 30 1
6 1

1
25
1
7
1

1 30 1
8



1 30 1
9

1
1 25
1


¥u»Ý­n¦¸¼Æ¬Û¥[ªº¼Æ¦r
11 A B C D EF
12  ®É¬q1
®É¬q2
·|­û
«D·|­û
°Ñ»P¤H¼Æ
13 ·|ij«ÇA 2(¦¸) 1 2
1
  85(40+20+25)-¥u­nÁ`¼Æ
14 ·|ij«ÇB 1 2
2
1
110
15 ·|ij«ÇC 0 2 1
1
  55

TOP

¦^´_ 3# owen06
¤T¤ÀÄÁ½s¿è®É­­¤w¹L
¤~µoı­×§ï«á¤´¦³¿òº|
¹ï¤£°_
³o­Ó¤~¹ï
"1"¬°¨Ï¥Î¦¸¼Æ

A
B
C
D
E
F
G
H
1 ·|ij«ÇA ·|ij«ÇB ·|ij«ÇC ®É¬q1 ®É¬q2 ¤H¼Æ ·|­û «D·|­û
2 1


1 40 1
3
1
1
50
1
4 1

1
   20
1

5

1
1 30 1
6 1

1
25
1
7
1

1 30 1
8
¢°


1 30 1
9

1
1 25
1


¥u»Ý­n¦¸¼Æ¬Û¥[ªº¼Æ¦r
11 A B C D EF
12  ®É¬q1
®É¬q2
·|­û
«D·|­û
°Ñ»P¤H¼Æ
13 ·|ij«ÇA 2(¦¸) 1 2
1
  85(40+20+25)-¥u­nÁ`¼Æ
14 ·|ij«ÇB 1 2
2
1
110
15 ·|ij«ÇC 0 2 1
1
  55

TOP

¦^´_ 5# K_Wing

·|ij«ÇA
B13=SUMPRODUCT(($A$2:$A$9=1)*($D$2:$D$9=1))
C13=SUMPRODUCT(($A$2:$A$9=1)*($E$2:$E$9=1))
D13=SUMPRODUCT(($A$2:$A$9=1)*($G$2:$G$9=1))
E13=SUMPRODUCT(($A$2:$A$9=1)*($H$2:$H$9=1))
F13=SUMPRODUCT(($A$2:$A$9=1)*$F$2:$F$9)

·|ij«ÇB¸òC¡A´N§â$A$2:$A$9¤À§O³£§ï¦¨$B$2:$B$9ÁÙ¦³$C$2:$C$9´N¦æ¤F

TOP

¦^´_ 6# owen06
«D±`·PÁÂ

TOP

¦p¦ó­pºâ¥X¨â¦C¦@¦Pªº©M.rar (7.43 KB)

B13 =SUMIF(OFFSET($A$2:$A$9,,MATCH($A13,$1:$1,)-1),1,OFFSET($A$2:$A$9,,MATCH(B$12,$1:$1,)-1))
¥k©Ô¤U©Ô
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 8# ML089

»Ý¨D¥u·Q¶V²³æ¶V¦n
¬G6¼Óªº¸û¾A¦X
¤´·PÁ«e½ú¦^ÂÐ

TOP

        ÀR«ä¦Û¦b : °ß¨ä´L­«¦Û¤vªº¤H¡A¤~§ó«i©óÁY¤p¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD