c_c_lai

¦^´_  kai6929


    =SUMPRODUCT((¥Øªº¦a=E3)*(¥XµoÂI=E4),°Ï°ì¶O²v!$C$4H$28)
    ³o¦U¥i¥H¶Ü
   ...
freeffly µoªí©ó 2012-11-6 08:42

»yªkªº¥iŪ©ÊÆZ°ªªº¡A¨ì¥Ø«e¬°¤î§ÚÁÙ¨S¨Ï¥Î SUMPRODUCT ªº«ü¥O¡A
½Ð°Ý¥¦ªº¤º²[¬°¦ó¡B³q±`¬OÀ³¥Î¦b­þ¸Ì¸û¾A©y¡H
Ä´¦p¦b¦¹³B¡A±z¬°¦ó¨Ï¥Î¥¦¨ÓÁIÄÀ¡H

freeffly

¦^´_ 1# kai6929


    =SUMPRODUCT((¥Øªº¦a=E3)*(¥XµoÂI=E4),°Ï°ì¶O²v!$C$4:$H$28)
    ³o¦U¥i¥H¶Ü
   ¥i¥H±o¨ìµª®×

c_c_lai

¦^´_ 11# Hsieh
«D±`·PÁ±z¡I
¸g´ú¸Õ¤@¤Á OK¡I

Hsieh

¦^´_ 7# c_c_lai
§Q¥ÎSheet1ªº¨Æ¥óµ{§Ç

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. With Me
   
3. If Intersect(Target, .[E3:E4]) Is Nothing Then Exit Sub
   
4.   a = .[E3]
   
5.   b = .[E4]
   
6.   With Sheet2
   
7.     c = .Range("B3").CurrentRegion.Find(a, lookat:=xlWhole).Column
   
8.     r = .Range("B3").CurrentRegion.Find(b, lookat:=xlWhole).Row
   
9.     MsgBox .Cells(r, c)
   
10.   End With
    
11. End With
    
12. End Sub

½Æ»s¥N½X

c_c_lai

¦^´_ 9# JBY
¤£¦n·N«ä¡A§Ú¬O«ü¦pªG¨Ï¥Î VBA µ{¦¡»y¨¥¡A
¦³§_§ó¦nªºªí¹F¤è¦¡¡AÁÂÁ¡I

JBY

¦^´_ 1# kai6929

©ÎªÌ :

=VLOOKUP(E4,°Ï°ì¶O²v!B3:H100,MATCH(E3,°Ï°ì¶O²v!B3:H3,0),0)

JBY

¦^´_ 1# kai6929

=INDEX(°Ï°ì¶O²v!B3:H100,MATCH(E4,°Ï°ì¶O²v!B3:B100,0),MATCH(E3,°Ï°ì¶O²v!B3:H3,0))

c_c_lai

¦^´_ 5# freeffly
§Ú¸ÕµÛ¼g¤F¤@­Ó´ú¸Õ¼Ò²Õ¡A¦p¤U¡G

1. Sub Test()
   
2.     [D6] = Sheets("°Ï°ì¶O²v").[B3].Offset(WorksheetFunction.Match([C3], Sheets("°Ï°ì¶O²v").Range("B4:B28"), 0), _
   
3.                                              WorksheetFunction.Match([D3], Sheets("°Ï°ì¶O²v").Range("C3:H3"), 0))
   
4.     MsgBox Sheets("°Ï°ì¶O²v").[B3].Offset(WorksheetFunction.Match([C3], Sheets("°Ï°ì¶O²v").Range("B4:B28"), 0), _
   
5.                                      WorksheetFunction.Match([D3], Sheets("°Ï°ì¶O²v").Range("C3:H3"), 0)).Address
   
6. End Sub

½Æ»s¥N½X

¦³¨S¦³§ó¨Îªº¤è¦¡ªí¹F¡H

c_c_lai

¦^´_ 5# freeffly
¬Oªº¡I
¥H VBA »yªk¨¤«×À³¦p¦ó¼¶¼g¡H

freeffly

¦^´_ 4# c_c_lai


    "=INDEX(°Ï°ì¶O²v!C[-3]:C[3],MATCH(R[-2]C,°Ï°ì¶O²v!C[-3],0),MATCH(R[-3]C,°Ï°ì¶O²v!R[-3],0))"
    ¬O­n³o¦U¶Ü