返回列表 上一主題 發帖

[發問] 函數提升為代碼

[發問] 函數提升為代碼

excel的前輩們
工作表 Sheet2  B , C , O , P 欄設有函數 , 可以把它們以程式代碼的方式處理嗎 ?
B欄設函數如下
=IF(A2<> "",VLOOKUP($A$2:$A$409,資料!$A$1:$C$64,2,FALSE), "")
C欄設函數如下
=IF(B2<> "",VLOOKUP($A$2:$A$409,資料!$A$1:$C$64,3,FALSE), "")
O欄設函數如下
=IF(AND(COUNTIF($A$2:$A2,$A2)=1,SUMIF($A$2:$A$301,$A2,$D$2)),SUMIF($A$2:$A$301,$A2,$D$2),"")
P欄設函數如下
=IF(AND(COUNTIF($E$2:$E2,$E2)=1,SUMIF($E$2:$E$301,$E2,$D$2)),SUMIF($E$2:$E$301,$E2,$D$2),"")

謝謝
無止盡的努力上進

dear sir-
於此網站執行"搜索" key 關鍵字 "VLOOKUP" "COUNTIF" "SUMIF"
會有許多vba範例供參考使用.

TOP

本帖最後由 yen956 於 2016-3-7 18:24 編輯

通常這種情形我會用錄製的,

1. [B2]輸入公式:
=IF(A2<> "",VLOOKUP($A$2:$A$409,資料!$A$1:$C$64,2,FALSE), "")
2. 錄製巨集
3. 在公式中間的空白處再加一空白(不會影嚮公式的運作)
4. 停止錄製巨集
如此就獲得VBA的公式, 如下:
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]<>  """",VLOOKUP(R2C1:R409C1,資料!R1C1:R64C3,2,FALSE), """")"
再修為
    [B2].FormulaR1C1 = _
        "=IF(RC[-1]<>  """",VLOOKUP(R2C1:R409C1,資料!R1C1:R64C3,2,FALSE), """")"
即可,
當然真正高手可能不必如此, 但我一向如此寫VBA公式.
試試看!!

TOP

回復 1# vinejason


    嗯!我不是很懂函數,所以只能回答部份
平常若遇到這問題,我大多數是以工作表事件去做,以下給你參考,不過我只做到VLOOKUP部份
另2個,呵呵! 不懂。以下給你參考
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Dim Rng As Range, c As Range
  3.     For Each Rng In Target
  4.         If Rng.Column = 1 Then
  5.             If Rng <> "" Then
  6.                 With Sheets("資料")
  7.                     Set c = .Columns(1).Find(Rng.Value, LookIn:=xlValues, LookAt:=xlWhole)
  8.                     If Not c Is Nothing Then
  9.                         Application.EnableEvents = False
  10.                         Rng.Offset(, 1) = c.Offset(, 1) 'B欄
  11.                         Rng.Offset(, 2) = c.Offset(, 2) 'c欄
  12.                         Application.EnableEvents = True
  13.                     End If
  14.                 End With
  15.             Else
  16.                 Rng.Offset(, 1) = "" 'B欄
  17.                 Rng.Offset(, 2) = "" 'c欄
  18.             End If
  19.         End If
  20.     Next
  21. End Sub
複製代碼

TOP

回復 1# vinejason

Sub ex()

    With Sheets("Sheet2")
        'B欄設函數如下
        '=IF(A2<> "",VLOOKUP($A$2:$A$409,資料!$A$1:$C$64,2,FALSE), "")
        Range("B2:B409") = "=IF(A2<> """",VLOOKUP($A2,資料!$A$1:$C$64,2,FALSE), """")" '與原式略有不同
'        Range("B2:B409") = Range("B2:B409").Value '公式轉為值
        
        'C欄設函數如下
        '=IF(B2<> "",VLOOKUP($A$2:$A$409,資料!$A$1:$C$64,3,FALSE), "")
        Range("C2:C409") = "=IF(B2<> """",VLOOKUP($A2,資料!$A$1:$C$64,3,FALSE), """")" '與原式略有不同
'        Range("C2:C409") = Range("C2:C409").Value '公式轉為值

        'O欄設函數如下
        '=IF(AND(COUNTIF($A$2:$A2,$A2)=1,SUMIF($A$2:$A$301,$A2,$D$2)),SUMIF($A$2:$A$301,$A2,$D$2),"")
        Range("O2:O409") = "=IF(AND(COUNTIF($A$2:$A2,$A2)=1,SUMIF($A$2:$A$301,$A2,$D$2)),SUMIF($A$2:$A$301,$A2,$D$2),"""")"
'        Range("O2:O409") = Range("O2:O409").Value '公式轉為值

        'P欄設函數如下
        '=IF(AND(COUNTIF($E$2:$E2,$E2)=1,SUMIF($E$2:$E$301,$E2,$D$2)),SUMIF($E$2:$E$301,$E2,$D$2),"")
        Range("P2:P409") = "=IF(AND(COUNTIF($E$2:$E2,$E2)=1,SUMIF($E$2:$E$301,$E2,$D$2)),SUMIF($E$2:$E$301,$E2,$D$2),"""")"
'        Range("P2:P409") = Range("P2:P409").Value '公式轉為值

    End With

End Sub
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

回復 3# yen956

謝謝提供好方法
又學會一招
無止盡的努力上進

TOP

        靜思自在 : 我們要做好社會的環保,也要做好內心的環保。
返回列表 上一主題