[算表] excel請教單元格絕對引用

[yyingx (bless)]

軟體:ms2007

版本:2007

比如絕對引用A1  可以直接寫成=$A$1，但我要類似這樣格式寫=$"A"&$ROW(A1)
就是行數要用row返回，就是要當我刪除工作表多余列，引用的單元格的行數不
變，請問該怎么寫

--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 27.38.52.180
※ 文章網址: https://www.ptt.cc/bbs/Office/M.1440244540.A.599.html
※ 編輯: yyingx (27.38.52.180), 08/22/2015 20:04:03
→ soyoso: 函數indirect來合併儲存格參照位址字串
→ soyoso: 函數也可address試試
→ yyingx: 這樣的寫法能套用到sumproduct么，該函數要求每列個數一樣
→ soyoso: indirect套用到sumproduct的話可再配合n()
→ yyingx: 請教n()是什么意思？SUMPRODUCT((x.xls]x!$G$4:$G$5600=
→ yyingx: 我想把5600弄成一個定量，并收到刪除4-5600之間行而減少
→ yyingx: 請問該怎么寫呢
→ soyoso: n()是指函數n
→ soyoso: 原po寫$G$4:$G$5600，5600不就是一個定量了，還是指變量
→ yyingx: 當刪除4-5600之間行，5600會發生變化。
→ yyingx: 我現在想當刪除4-5600之間行 ,  這個5600不受影響
→ soyoso: 那用indirect("[x.xls]x!g4:g5600") 試試
→ soyoso: 跨檔使用indirect時括號內的檔案也要開啟，不然重新運算時
→ soyoso: 會出現#ref!的錯誤
→ yyingx: 請教如何我替換公式里面的內容，比如我要把$G$4:$G$38
→ yyingx: 替換成 G 4: G 5600
→ yyingx: 顯示公式后，還是無法^h 替換
→ yyingx: 原來是單元格匹配不能勾選
→ soyoso: 類似這樣https://goo.gl/dB7LDL
→ yyingx: 能否po到郵箱，麻煩你了
→ soyoso: 和原po原文一樣，尋找目標打上的是$g$4:$g$38，取代成打上
→ soyoso: 的是g4:g5600，搜尋範圍:工作表，搜尋:循列，搜尋:公式
→ soyoso: 連結圖示的設定為以上文字說明，執行後有取代公式內容
→ yyingx: 您的意思這樣寫嗎？
=SUMPRODUCT(INDIRECT("[成品入倉記2.xls]成品入庫!G4:G5600")="TZR")....
我是跨檔了，跨檔文件也打開了，但還是報錯了

※ 編輯: yyingx (27.38.52.180), 08/22/2015 22:03:33
※ 編輯: yyingx (27.38.52.180), 08/22/2015 22:04:29
→ soyoso: g4:g5600)"←引號位置有誤
→ yyingx: 剛改過來了，返回結果是0，我再檢查檢查
→ soyoso: 如果sumproduct()括號內只有這個判斷式的話，判斷式外圈要
→ soyoso: 配合--或n()
→ yyingx: 謝謝你了，搞定，掉括號了
→ yyingx: 還有三個判斷在里面
→ soyoso: 嗯那應無需--或n()
→ yyingx: 其實如有只有一個判斷，就直接sumif了
→ soyoso: 原文上方有寫到版本是2007的話，另有sumifs可試試
→ yyingx: 接著請教，我有四個判斷，且判斷沒什么規律，我需要手動
→ yyingx: 一個一個寫,而且是跨檔引用，請問有什么思路用巨集呢？
→ yyingx: 我的想法是用ado +sumproduct不知道能實現不。
→ yyingx: 主要是不想打開跨檔文件
→ soyoso: 用巨集寫的話，於觸發執行時直接寫[成品入倉記2.xls]成品
→ soyoso: 入庫!g4:g5600，而不用indirect，填上公式
Sub testSQL()
    Dim cnn, sql1$
    Set cnn = CreateObject("adodb.connection")
    cnn.Open "Provider=Microsoft.ACE.OLEDB.8.0;Extended Properties=Excel 8.0
;Data Source="& ThisWorkbook.Path & " \ 成品入倉記2.xls
    sql = SUMPRODUCT(([成品入倉記2.xls]成品入庫!T4:T6000="楊明")*[
成品入倉記2.xls]成品入庫!W4:W6000)
    sql2..
    sql3...
   Range("A2").CopyFromRecordset cnn.Execute(sql1)
     .....
     .....
  cnn.Close
   Set cnn = Nothing
End Sub
我想寫成這樣樣式，請糾正
※ 編輯: yyingx (27.38.52.180), 08/23/2015 12:47:41
※ 編輯: yyingx (27.38.52.180), 08/23/2015 12:48:30
※ 編輯: yyingx (27.38.52.180), 08/23/2015 12:54:23
→ soyoso: 可查sql語法select sum(column) from table where..之類的
→ yyingx: 能和sumproduct函數結合么
→ soyoso: 結合是指？直接用於sql語法內嗎？
→ yyingx: 我要寫A工作簿sheet1 a1 = B工作簿sheet1, A列為1，B列為A
→ yyingx: C列的和，該怎么寫呢？
→ soyoso: 如以sql語法的話，測試如回文column為c，table為b活頁簿的
→ soyoso: 工作表名，where條件為a=1 and b='a'
→ soyoso: 另外microsoft.ace.oledb.8.0測試在2007內執行找不到提供
→ soyoso: 者，不知這段巨集碼那裡來的呢？因為有看過的是ace 12.0
→ soyoso: jet 4.0
→ yyingx: 引用的工作簿xls，运行代码的工作簿是xlsm
Sub ADO匯55總()
    Dim cnn As Object, SQL$
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties=excel 12
.0;data source=" & ThisWorkbook.Path & "\.成品入倉記2.xlsx"
    SQL = "select sum(W) from [Excel 12.0;Database=" & ThisWorkbook.FullName
 & ";].[Sheet2$a3:w65536] where T='楊明' and G='TZR'"
    [a2].CopyFromRecordset cnn.Execute(SQL)
    cnn.Close
    Set cnn = Nothing
End Sub
把暫時把成品入倉記2.xls轉換成了成品入倉記2.xlsx，寫了如上巨集，但依然運行不了
請看看
※ 編輯: yyingx (27.38.52.180), 08/23/2015 17:31:32
※ 編輯: yyingx (27.38.52.180), 08/23/2015 17:32:51
→ soyoso: from [excel 120;database=" & thiskworkbook.fullname &"
→ soyoso: ;]...這裡寫法怪怪的 W,T,G是原po的標題名稱嗎？不是欄號
→ soyoso: 喔
→ soyoso: 另外請問原po如何查到[Excel 12.0;Database=" & ThisWorkb
→ soyoso: ook.fullname & ";]...這樣的寫法呢？是否可以提供看看，
→ soyoso: 因為沒看過想說是否有其他不同寫法可以學習的
→ soyoso: 我測試用xlsm來讀xls方面是可以執行的，不用另存
→ yyingx: 由于大陸這么mega上傳很慢，dropbox是封禁的，所以po您的
→ yyingx: 郵箱了
→ soyoso: 將[Excel 12.0;Database=" & ThisWorkbook.FullName & ";]
→ soyoso: .[Sheet1$]改為
→ soyoso: " & ThisWorkbook.FullName & ".[Sheet1$] 試試
→ soyoso: 測試是可以執行
→ yyingx: 是针对我那个附件也可以执行吗？
→ soyoso: 是測試附件內"連接"這個檔案
→ yyingx: 连接附件本身是呵例子，我想模仿那个，但不成功。因为
→ yyingx: 我有四个判断。
→ soyoso: 也是，再測試一下[Excel 12.0;Database=" & ThisWorkbook.
→ yyingx: 有一列是函数，我已经写好了，但函数需要打开跨档文件
→ yyingx: 。所以我弄一个巨集达到一样的效果，可以测试了一个下
→ yyingx: 午都没什么效果
→ soyoso: .FullName & ";]的寫法，也是ok
→ soyoso: 1.發現"庫存"模組ado匯總，變數上有誤
→ soyoso: 宣告變數為cnn，但set 變數時卻用conn
→ soyoso: 2.同該模組，因有標題hdr=no要調整一下
→ yyingx: 能否直接发给我？调的我头都大了。
→ yyingx: 您的意思要除了要寫成cnn，hdr要等于yes,是這樣意思嗎
→ soyoso: 原po可試試是否可正常讀入
→ yyingx: 請問如何測試正常讀入？
因為跨檔文件是xlsx,所以我寫成了這樣
Sub ADO查詢（）
    Dim cnn As Object, SQL$
    Set cnn = CreateObject("adodb.connection")
    cnn.Open "provider=microsoft.jet.oledb.12.0;extended properties='excel 1
2.0;hdr=yes';data source=" & ThisWorkbook.Path & "\.成品入倉記2.xlsx"
    SQL = "select sum(庫存支) from [Sheet2$a3:w65536] where 備注='楊明'"
     Range("A2").CopyFromRecordset cnn.Execute(SQL)
    cnn.Close
    Set cnn = Nothing
End Sub
※ 編輯: yyingx (27.38.52.180), 08/24/2015 00:48:47
→ soyoso: 先以不出現錯誤訊息，巨集可正常被讀入，再調整原po要的結
→ soyoso: 果
→ yyingx: 但還是您說的最基本的讀入都不通
→ soyoso: jet.oledb.12.0抱歉我這就沒看過這樣寫了
→ soyoso: 我看過的是jet.oledb.4.0
→ soyoso: 或是您上面一點回文的ace.oledb.12.0的寫法
→ soyoso: 指定檔名原po上面打的是".成品入倉記2.xlsx"，但郵件附件
→ soyoso: 內是"成品入倉記2.xlsx"
→ soyoso: sql內的"庫存支"有找到是儲存格w3，但"備注"卻沒有找到
Sub ADO查詢()
    Dim cnn As Object, SQL$
    Set cnn = CreateObject("adodb.connection")
    cnn.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties=excel 12
.0;hdr=yes';data source=" & ThisWorkbook.Path & "\成品入倉記2.xlsx"
    SQL = "select sum(庫存支) from [Sheet2$a3:w6000] where 備注='楊明'"
    Range("A100").CopyFromRecordset cnn.Execute(SQL)
    cnn.Close
    Set cnn = Nothing
End Sub

※ 編輯: yyingx (27.38.52.180), 08/24/2015 01:05:23
※ 編輯: yyingx (27.38.52.180), 08/24/2015 01:07:06
→ yyingx: 但"備注"卻沒有找到 這個我也填上去了
→ soyoso: 錯誤？回傳無值？成品入倉記2.xlsx內的工作表名是"成品入
→ soyoso: 庫"，但上面是打"sheet2"
Sub ADO查詢()
    Dim cnn As Object, SQL$
    Set cnn = CreateObject("adodb.connection")
    cnn.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties=excel 12
.0;hdr=yes';data source=" & ThisWorkbook.Path & "\成品入倉記2.xlsx"
    SQL = "select sum(庫存支) from [成品入庫$a3:w6000] where 備注 ='楊明'"
    Range("A100").CopyFromRecordset cnn.Execute(SQL)
    cnn.Close
    Set cnn = Nothing
End Sub
※ 編輯: yyingx (27.38.52.180), 08/24/2015 01:18:43
→ soyoso: properties='excel 12，'←這個小引號於00:48:47原po重新
→ yyingx: 請教sheet2和成品入庫不能等效么
→ soyoso: 編輯有打，為什麼接著就又都不見了呢？
Sub ADO查詢()
    Dim cnn As Object, SQL$
    Set cnn = CreateObject("adodb.connection")
    cnn.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties='excel 1
2.0;hdr=yes';data source=" & ThisWorkbook.Path & "\成品入倉記2.xlsx"
    SQL = "select sum(庫存支) from [成品入庫$a3:w6000] where 備注 ='楊明'"
    Range("A100").CopyFromRecordset cnn.Execute(SQL)
    cnn.Close
    Set cnn = Nothing
End Sub
※ 編輯: yyingx (27.38.52.180), 08/24/2015 01:25:54
→ yyingx: 貌似能讀入了
→ soyoso: sheet2和成品入庫是否能等效，這您實測就可了解
→ soyoso: sheet2可被正確讀入那等效，無法正確讀入那就不等效
→ yyingx: 這么晚，一點點跟給我這個菜鳥糾錯，太難為了你了
→ yyingx: where 備注 ='楊明' and 單重 = 3.5 " 不能這樣直接加判斷
→ yyingx: 是嗎？
→ soyoso: 彼此討論討論
→ yyingx: 請教如何在where后面在加判斷呢？
→ soyoso: 在有標題"單重"，單重 = '3.5'，測試這樣是有抓出資料
→ yyingx: 備注 ='楊明' and  單重 =  '3.5' 像這樣嗎，報錯了
→ yyingx: 跨檔文件我已經把“單重”補上去了
→ yyingx: 數據單引號不能要，要直接寫成 3.5
→ yyingx: 好了，真的十分感謝您，晚安！
→ soyoso: 那很抱歉因我不知那一欄才是原po要的單重，就隨意找一欄，
→ soyoso: u欄，但單重=3.5是報錯的，所以才回單重='3.5'這反而可行
→ soyoso: 應是只是找楊明key數值來測試而已
→ yyingx: 備注欄如果想判斷是空值，該如何寫，備注=''，這樣寫NG
→ soyoso: google sql 空值
→ yyingx: is null