PTT數位生活區 / Office

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

看板Office作者 (bless)時間8年前 (2015/08/22 19:55), 8年前編輯推噓0(0推 0噓 113→)
留言113則, 2人參與, 最新討論串1/1
軟體: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
→
08/22 20:04, , 1F
函數indirect來合併儲存格參照位址字串
08/22 20:04, 1F
→
08/22 20:05, , 2F
函數也可address試試
08/22 20:05, 2F
→
08/22 20:07, , 3F
這樣的寫法能套用到sumproduct么，該函數要求每列個數一樣
08/22 20:07, 3F
→
08/22 20:20, , 4F
indirect套用到sumproduct的話可再配合n()
08/22 20:20, 4F
→
08/22 20:29, , 5F
請教n()是什么意思？SUMPRODUCT((x.xls]x!$G$4:$G$5600=
08/22 20:29, 5F
→
08/22 20:30, , 6F
我想把5600弄成一個定量，并收到刪除4-5600之間行而減少
08/22 20:30, 6F
→
08/22 20:30, , 7F
請問該怎么寫呢
08/22 20:30, 7F
→
08/22 20:35, , 8F
n()是指函數n
08/22 20:35, 8F
→
08/22 20:36, , 9F
原po寫$G$4:$G$5600，5600不就是一個定量了，還是指變量
08/22 20:36, 9F
→
08/22 20:39, , 10F
當刪除4-5600之間行，5600會發生變化。
08/22 20:39, 10F
→
08/22 20:42, , 11F
我現在想當刪除4-5600之間行 , 這個5600不受影響
08/22 20:42, 11F
→
08/22 21:04, , 12F
那用indirect("[x.xls]x!g4:g5600") 試試
08/22 21:04, 12F
→
08/22 21:05, , 13F
跨檔使用indirect時括號內的檔案也要開啟，不然重新運算時
08/22 21:05, 13F
→
08/22 21:05, , 14F
會出現#ref!的錯誤
08/22 21:05, 14F
→
08/22 21:12, , 15F
請教如何我替換公式里面的內容，比如我要把$G$4:$G$38
08/22 21:12, 15F
→
08/22 21:13, , 16F
替換成 G 4: G 5600
08/22 21:13, 16F
→
08/22 21:13, , 17F
顯示公式后，還是無法^h 替換
08/22 21:13, 17F
→
08/22 21:24, , 18F
原來是單元格匹配不能勾選
08/22 21:24, 18F
→
08/22 21:25, , 19F
08/22 21:25, 19F
→
08/22 21:42, , 20F
能否po到郵箱，麻煩你了
08/22 21:42, 20F
→
08/22 21:58, , 21F
和原po原文一樣，尋找目標打上的是$g$4:$g$38，取代成打上
08/22 21:58, 21F
→
08/22 21:59, , 22F
的是g4:g5600，搜尋範圍:工作表，搜尋:循列，搜尋:公式
08/22 21:59, 22F
→
08/22 22:00, , 23F
連結圖示的設定為以上文字說明，執行後有取代公式內容
08/22 22:00, 23F
→
08/22 22:02, , 24F
您的意思這樣寫嗎？
08/22 22:02, 24F
=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
→
08/22 22:07, , 25F
g4:g5600)"←引號位置有誤
08/22 22:07, 25F
→
08/22 22:08, , 26F
剛改過來了，返回結果是0，我再檢查檢查
08/22 22:08, 26F
→
08/22 22:10, , 27F
如果sumproduct()括號內只有這個判斷式的話，判斷式外圈要
08/22 22:10, 27F
→
08/22 22:10, , 28F
配合--或n()
08/22 22:10, 28F
→
08/22 22:10, , 29F
謝謝你了，搞定，掉括號了
08/22 22:10, 29F
→
08/22 22:15, , 30F
還有三個判斷在里面
08/22 22:15, 30F
→
08/22 22:19, , 31F
嗯那應無需--或n()
08/22 22:19, 31F
→
08/22 22:24, , 32F
其實如有只有一個判斷，就直接sumif了
08/22 22:24, 32F
→
08/22 22:35, , 33F
原文上方有寫到版本是2007的話，另有sumifs可試試
08/22 22:35, 33F
→
08/23 12:25, , 34F
接著請教，我有四個判斷，且判斷沒什么規律，我需要手動
08/23 12:25, 34F
→
08/23 12:26, , 35F
一個一個寫,而且是跨檔引用，請問有什么思路用巨集呢？
08/23 12:26, 35F
→
08/23 12:35, , 36F
我的想法是用ado +sumproduct不知道能實現不。
08/23 12:35, 36F
→
08/23 12:35, , 37F
主要是不想打開跨檔文件
08/23 12:35, 37F
→
08/23 12:36, , 38F
用巨集寫的話，於觸發執行時直接寫[成品入倉記2.xls]成品
08/23 12:36, 38F
還有 39 則推文
還有 2 段內文
→
08/24 00:45, , 78F
原po可試試是否可正常讀入
08/24 00:45, 78F
→
08/24 00:46, , 79F
請問如何測試正常讀入？
08/24 00:46, 79F
因為跨檔文件是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
→
08/24 00:47, , 80F
先以不出現錯誤訊息，巨集可正常被讀入，再調整原po要的結
08/24 00:47, 80F
→
08/24 00:48, , 81F
果
08/24 00:48, 81F
→
08/24 00:49, , 82F
但還是您說的最基本的讀入都不通
08/24 00:49, 82F
→
08/24 00:52, , 83F
jet.oledb.12.0抱歉我這就沒看過這樣寫了
08/24 00:52, 83F
→
08/24 00:53, , 84F
我看過的是jet.oledb.4.0
08/24 00:53, 84F
→
08/24 01:01, , 85F
或是您上面一點回文的ace.oledb.12.0的寫法
08/24 01:01, 85F
→
08/24 01:02, , 86F
指定檔名原po上面打的是".成品入倉記2.xlsx"，但郵件附件
08/24 01:02, 86F
→
08/24 01:03, , 87F
內是"成品入倉記2.xlsx"
08/24 01:03, 87F
→
08/24 01:04, , 88F
sql內的"庫存支"有找到是儲存格w3，但"備注"卻沒有找到
08/24 01:04, 88F
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
→
08/24 01:07, , 89F
但"備注"卻沒有找到 這個我也填上去了
08/24 01:07, 89F
→
08/24 01:13, , 90F
錯誤？回傳無值？成品入倉記2.xlsx內的工作表名是"成品入
08/24 01:13, 90F
→
08/24 01:14, , 91F
庫"，但上面是打"sheet2"
08/24 01:14, 91F
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
→
08/24 01:21, , 92F
properties='excel 12，'←這個小引號於00:48:47原po重新
08/24 01:21, 92F
→
08/24 01:21, , 93F
請教sheet2和成品入庫不能等效么
08/24 01:21, 93F
→
08/24 01:21, , 94F
編輯有打，為什麼接著就又都不見了呢？
08/24 01:21, 94F
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
→
08/24 01:26, , 95F
貌似能讀入了
08/24 01:26, 95F
→
08/24 01:26, , 96F
sheet2和成品入庫是否能等效，這您實測就可了解
08/24 01:26, 96F
→
08/24 01:27, , 97F
sheet2可被正確讀入那等效，無法正確讀入那就不等效
08/24 01:27, 97F
→
08/24 01:27, , 98F
這么晚，一點點跟給我這個菜鳥糾錯，太難為了你了
08/24 01:27, 98F
→
08/24 01:30, , 99F
where 備注 ='楊明' and 單重 = 3.5 " 不能這樣直接加判斷
08/24 01:30, 99F
→
08/24 01:30, , 100F
是嗎？
08/24 01:30, 100F
→
08/24 01:31, , 101F
彼此討論討論
08/24 01:31, 101F
→
08/24 01:34, , 102F
請教如何在where后面在加判斷呢？
08/24 01:34, 102F
→
08/24 01:36, , 103F
在有標題"單重"，單重 = '3.5'，測試這樣是有抓出資料
08/24 01:36, 103F
→
08/24 01:39, , 104F
備注 ='楊明' and 單重 = '3.5' 像這樣嗎，報錯了
08/24 01:39, 104F
→
08/24 01:39, , 105F
跨檔文件我已經把“單重”補上去了
08/24 01:39, 105F
→
08/24 01:44, , 106F
數據單引號不能要，要直接寫成 3.5
08/24 01:44, 106F
→
08/24 01:45, , 107F
好了，真的十分感謝您，晚安！
08/24 01:45, 107F
→
08/24 07:08, , 108F
那很抱歉因我不知那一欄才是原po要的單重，就隨意找一欄，
08/24 07:08, 108F
→
08/24 07:09, , 109F
u欄，但單重=3.5是報錯的，所以才回單重='3.5'這反而可行
08/24 07:09, 109F
→
08/24 07:17, , 110F
應是只是找楊明key數值來測試而已
08/24 07:17, 110F
→
08/25 21:26, , 111F
備注欄如果想判斷是空值，該如何寫，備注=''，這樣寫NG
08/25 21:26, 111F
→
08/25 23:49, , 112F
google sql 空值
08/25 23:49, 112F
→
08/26 21:42, , 113F
is null
08/26 21:42, 113F
‣ 更多 yyingx 的文章
文章代碼(AID): #1Ls6CyMP (Office)
Office 近期熱門文章
更多 近期熱門文章 >>
PTT數位生活區 即時熱門文章
更多 即時熱門文章 >>
‣ 更多 yyingx 的文章
文章代碼(AID): #1Ls6CyMP (Office)