帖子: 124
主題: 43
精華: 0
積分: 167
點名: 0
作業系統: Windows7
軟體版本: office2007
閱讀權限: 20
性別: 男
來自: 台灣台北
註冊時間: 2011-3-16
最後登錄: 2021-10-7

1^# 跳轉到 » 正序看帖

字體大小: tT

發表於 2016-3-7 10:28 | 只看該作者

[發問] 函數提升為代碼

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),"")

謝謝

樓主熱帖

無止盡的努力上進

vinejason

中學生 Rank: 2

帖子: 124
主題: 43
精華: 0
積分: 167
點名: 0
作業系統: Windows7
軟體版本: office2007
閱讀權限: 20
性別: 男
來自: 台灣台北
註冊時間: 2011-3-16
最後登錄: 2021-10-7

6^#

發表於 2016-3-15 16:35 | 只看該作者

回復 3# yen956

謝謝提供好方法
又學會一招

無止盡的努力上進

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

5^#

發表於 2016-3-8 06:55 | 只看該作者

回復 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

lpk187

高中生 Rank: 3 Rank: 3

帖子: 552
主題: 3
精華: 0
積分: 578
點名: 0
作業系統: win7
軟體版本: office 2010
閱讀權限: 50
性別: 男
註冊時間: 2015-2-8
最後登錄: 2024-7-9

2016猴年全勤獎章 2015羊年全勤獎章熱烈參與勳章

4^#

發表於 2016-3-7 22:01 | 只看該作者

回復 1# vinejason

嗯！我不是很懂函數，所以只能回答部份
平常若遇到這問題，我大多數是以工作表事件去做，以下給你參考，不過我只做到VLOOKUP部份
另2個，呵呵！不懂。以下給你參考

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, c As Range
For Each Rng In Target
If Rng.Column = 1 Then
If Rng <> "" Then
With Sheets("資料")
Set c = .Columns(1).Find(Rng.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
Application.EnableEvents = False
Rng.Offset(, 1) = c.Offset(, 1) 'B欄
Rng.Offset(, 2) = c.Offset(, 2) 'c欄
Application.EnableEvents = True
End If
End With
Else
Rng.Offset(, 1) = "" 'B欄
Rng.Offset(, 2) = "" 'c欄
End If
End If
Next
End Sub

複製代碼

TOP

yen956

高中生 Rank: 3 Rank: 3

帖子: 522
主題: 36
精華: 1
積分: 603
點名: 0
作業系統: win xp sp3
軟體版本: Office 2003
閱讀權限: 50
性別: 男
註冊時間: 2012-12-13
最後登錄: 2021-7-11

3^#

發表於 2016-3-7 18:21 | 只看該作者

本帖最後由 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

rouber590324

高中生 Rank: 3 Rank: 3

帖子: 561
主題: 160
精華: 0
積分: 725
點名: 0
作業系統: WINDOWS
軟體版本: xp
閱讀權限: 50
性別: 男
來自: 桃園
註冊時間: 2014-9-10
最後登錄: 2024-8-7

論壇榮譽勳章論壇贊助勳章熱烈參與勳章

2^#

發表於 2016-3-7 14:50 | 只看該作者

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

羅

TOP