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

[µo°Ý] ¦h­«±ø¥ó¦¡²Î­p»P¥[Á`

[µo°Ý] ¦h­«±ø¥ó¦¡²Î­p»P¥[Á`

¦U¦ì¥ý¶i­Ì¦n¡I
¦h­«±ø¥ó¦¡²Î­p»P¥[Á`
¦pªþ¥ó¡AÁöµM¥i¥H¥Î {=SUM(  )}§¹¦¨
¦ý½Ð¥ý¶i­Ì¥i§_¥ÎVBAµ{¦¡¨Ó°õ¦æ
«D±`·PÁ«ü¾É
B.rar (5.4 KB)

¦U¦ì¥ý¶i«e½ú
¦¸¼Æ²Î­p°ÝÃD¤v¸Ñ¨M¡A¦ý¼Æ¶q¥[Á`Àµ½Ð¨ó§U«ü¾É¡C
¨Ò¦p¡G
[E35]¤§­È¬°³æ¸¹=ABS and ¦a°Ï=WZ and «~¦W=K ²Å¦X¤W­z¤T±ø¥ó¤§¾P°â¼Æ¶q¥[Á`¡C
½Ð¬Ýªþ¥óB1
¤U¦Cµ{¦¡¦p¦ó­×§ï¦¨¥i¥H¥[Á`
For i = 5 To 11
For m = 35 To 42
.Cells(m, i) = f(.Cells(10, i) & .Cells(m, 3) & .Cells(m, 4))
Next m
Next i

·PÁÂ

B1.rar (20.62 KB)

TOP

¦U¦ì¥ý¶i«e½ú
¤Wºô¬d¸ß¨ì¨Ï¥Î Application.Evaluate("sumproduct()")
µ{¦¡½X¦p¤U¡G¦ý°õ¦æ«áÀx¦s®æ¤º¬°¡ª#NAME?¡ª
Àµ½Ð«e½ú­Ì«ü¾É¡C
m = Sheets("Re").Range("C65536").End(xlUp).Row
For i = 5 To 11
    For j = 35 To 42        
        Cells(j, i) = Application.Evaluate("sumproduct((MID(Re!C7:C100, 5, 3) = cells(34, i))*(Re!K7:K100 = cells(j, 3))*(Re!AM7:AS100 = cells(j, 4))*(Re!BA7:BG100))")
    Next j
Next i

TOP

©êºp¤W«H¨S¦³ªþ¥[ÀÉ®× B2.rar

B2.rar (20.85 KB)

TOP

³oºØÃþ«¬¸ê®Æ·J¾ã
·Ó¥¿±`¨Ó»¡ ¨Ï¥Î¼Ï¯Ã¤ÀªR¤ñ¸û§Ö

¦ý¬Ý¤Fdata
data±Æ¦C­n¦A­×¥¿
¤~·|§ó¦n°õ¦æ
ªø´Á¨Ó»¡­×¥¿data¨Ï¥Î¼Ï¯Ã·|§ó¤è«K

TOP

·PÁªü¦N¥S
¼ö¤ß«ü¾É»¡©ú¡A¦ý¡ª¼Ï¯Ã¤ÀªR¡ª¶W¥X¤p§Ìªº¯à¤O¤Ó¦h¤F¡C
ÁÙ¬O·PÁ±z

TOP

·PÁªü¦N¥S
¼ö¤ß«ü¾É»¡©ú¡A¦ý¡ª¼Ï¯Ã¤ÀªR¡ª¶W¥X¤p§Ìªº¯à¤O¤Ó¦h¤F¡C
ÁÙ¬O·PÁ±z
b9208 µoªí©ó 2010-5-16 00:26



    ±z¦n
¨ä¹ê¼Ï¯Ã¤ÀªR
¤ñVBA¾ã²z¸ê®Æ§Ö«Ü¦h
¤]¨S¨º»ò½ÆÂø
·Ó¨BÆJ°µ....¤£·|¤ÓÃø! B2-2¼Ï¯Ã¤ÀªR·§¹Ï.rar (136.33 KB)
ÁÂÁÂ!

TOP

¦U¦ì«e½ú
¥H¤U¨Ï¥ÎR1C1¤è¦¡¥i¥Î¡A¨Ñ¤j®a°Ñ¦Ò¡C

For i = 5 To 11
    For j = 35 To 42
       Cells(j, i).FormulaR1C1 = "=SUMPRODUCT((MID(Re!R7C3:R100C3, 5, 3) =R34C)*(Re!R7C11:R100C11 =RC3)*(Re!R7C39:R100C45 =RC4)*(Re!R7C53:R100C59))"
       Cells(j, i) = Cells(j, i).Value
    Next
Next

½Ð«ü¾É¤@¤U¡G¤W¦¡¤¤ R34C ¤Î RC3 ¥Nªí¤°»ò¡A¦]®Ñ¤¤R OR C «á­±¦³¼Æ¦r¡A¦p R5C7, R10C70.....
¨Ò¦p
Cells(35, 5).FormulaR1C1 ====> R34C = ?,     RC3 = ?
Cells(36, 5).FormulaR1C1 ====> R34C = ?,     RC3 = ?
Cells(35, 6).FormulaR1C1 ====> R34C = ?,     RC3 = ?

TOP

¦^´_ 8# b9208


    ³o¤èªk´N¬O¼g¤½¦¡,¦ó¥²¦h¦¹¤@Á|
R1C1ªí¥Üªk
R1C1¡÷A1
RC1¡÷¦P¦CAÄæ
R1C¡÷¦PÄæ²Ä¤@¦C
R[1]C¡÷¦PÄæ¤U¤@¦C
RC[1]¡÷¦P¦C¦V¥k¤@Äæ
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 8# b9208


    ­Y¯uªº¤£¥h§ïÅܸê®Æ®w®æ¦¡,¤£¶û³Â·Ðªº¸Ü
°Ñ¦Òªþ¥ó B2.rar (20.77 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¤Ñ¤W³Ì¬ü¬O¬P¬P¡A¤H¥Í³Ì¬ü¬O·Å±¡¡C
ªð¦^¦Cªí ¤W¤@¥DÃD