作者ayudow912 (歐吉吉)
看板Office
標題[問題] excel 總計問題請益
時間Tue Mar 19 11:29:52 2019
(若是和其他不同軟體互動之問題 請記得一併填寫)
軟體:Microsoft Office Excel
版本:2007
我的表格同一個藥品如果這個月有進貨，會於表格中自動列出一列，想請問有沒有設定可
以讓他自動加總（同一藥品的使用量），不用一筆一筆加總，因為有些單位的人員是年長
者，不太會用軟體，想要設計給各個單位使用，直接設計好公式，不用讓他們自己加總，
求求各位大大幫忙小妹
https://i.imgur.com/eglI8iR.jpg
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 42.74.170.249
※ 文章網址: https://webptt.com/m.aspx?n=bbs/Office/M.1552966194.A.F24.html
1F：→ soyoso: 同個藥品最多只會有二列嗎？還是有可能三列以上03/19 12:10
4F：→ soyoso: 因會判斷下一列，如儲存格c2會判斷a3和b3資料，所以如果會03/19 12:21
5F：→ soyoso: 要以插入列方式新增藥品同月有進貨且公式已經拖曳的話03/19 12:22
6F：→ soyoso: 這方面a3和b3方面要以函數indirect配合row03/19 12:22
7F：→ soyoso: 如果是插入列，最後才加總拖曳的話則不用03/19 12:23
8F：→ soyoso: 修改一下如果要以回文連結有輔助欄d欄的話，d2公式則再加03/19 12:40
9F：→ soyoso: 上判斷同一列領用量(舉例是在b欄，再依實際修改)是否無數03/19 12:41
10F：→ soyoso: 值，無的話回傳空字串""03/19 12:42
11F：→ ayudow912: 一個藥品最多2行，大師超專業，我來試試，大感恩03/19 12:45
12F：→ ayudow912: 我剛剛試了一下，如果表格上面是文字則會出現錯誤 （03/19 14:34
如果我把文字改成數字，則可以顯示數量（藍色的部分）
https://i.imgur.com/k3DekdU.jpg
請問可以顯示文字（標題）但是後面仍呈現數字嗎
※ 編輯: ayudow912 (42.74.170.249), 03/19/2019 14:44:14
14F：→ soyoso: 剛才我有回，i8改為n(i8)，為何要刪除我的回文03/19 14:48
我沒有刪除大師的回文，有的話應該是手機操作不熟悉，誤刪，請您見諒
※ 編輯: ayudow912 (42.74.170.249), 03/19/2019 16:17:26
想再請問，承上，我想將連續三個月每個藥品的總使用量平均，如何設定？
剛剛有試過合併彙算，將第一、二分頁加總，但是無法只算欄位C的部分，只能將A到C全
選才能彙算，可以請教大師我哪裡設定錯誤？
※ 編輯: ayudow912 (42.74.170.249), 03/19/2019 17:00:10
15F：→ soyoso: 先問第一、二分頁的分頁指什麼？如果指的話下方工作表索引 03/19 17:05
16F：→ soyoso: 標籤內的話這叫工作表，並非稱為分頁 03/19 17:06
17F：→ soyoso: 只算欄位C方面是指以我回文的範例為標準的意思嗎？實際上 03/19 17:09
18F：→ soyoso: 是原po連結內m欄的領用總量？ 03/19 17:10
是的，因為每個月都會有一個工作表，我需要算出每個藥品3個月的平均使用量（m欄）
※ 編輯: ayudow912 (42.74.170.249), 03/19/2019 17:22:06
19F：→ soyoso: 那先來看合併彙算是否符合所需03/19 17:53
20F：→ soyoso: 以提供的公式來看單一藥品二列(加總於空白儲存格)那列03/19 17:54
22F：→ soyoso: 合併彙算輸出在i:j欄03/19 17:55
23F：→ soyoso: 但這應該不是原po要的結果吧
03/19 17:55
大師真強！知道這不是我要的結果，看來合併彙算不符合我的需求，我是期望可以在N欄
呈現三個月的平均使用量（3個月的M欄平均）
※ 編輯: ayudow912 (42.74.170.249), 03/19/2019 18:04:10
24F：→ soyoso: 回到回文提到的只算m欄(領用總量)的部分，如無合適方法，03/19 18:03
25F：→ soyoso: 就c:m的範圍皆新增，輸出後再隱藏欄03/19 18:04
26F：→ soyoso: 我的回文又消失了，17:55~18:03中間還有回文 03/19 18:06
27F：→ soyoso: 內容如下03/19 18:07
28F：→ soyoso: 那公式加總上調整於非空白儲存格的那列03/19 18:07
30F：→ soyoso: 如是n欄回傳平均使用量的話，可vlookup上述合併彙算的結果 03/19 18:14
31F：→ soyoso: 或是公式加總上調整於非空白儲存格那列後，vlookup前二個 03/19 18:15
32F：→ soyoso: 月資料再加總當月除3或average這三個月的值 03/19 18:16
大師～我試了你的vlookup公式，好像可以用唷！但是有個問題，如果兩行加總在第二行
的話，我用vlookup公式的話，會變成#N/A,那有辦法把兩行加總的結果顯示在第一行嗎？
https://i.imgur.com/Jx83qKf.jpg
※ 編輯: ayudow912 (42.75.85.184), 03/20/2019 11:56:03
※ 編輯: ayudow912 (42.75.85.184), 03/20/2019 11:58:21
33F：→ soyoso: 加總顯示於非空白儲存格那列話 03/20 12:07
大師～我成功了，超級感謝您
https://i.imgur.com/0TFOwGz.jpg
，最後一個問題，我套完公式後空白處會顯示#N/A（黃色部分）,可以隱藏或顯示-嗎？
※ 編輯: 912 (42.75.85.184), 03/20/2019 15:30:54
35F：→ soyoso: 配合函數iferror將錯誤值改為要文數字，隱藏可以空字串""03/20 15:33
36F：→ soyoso: 或回文要顯示的"-"03/20 15:33
我剛剛發現報表中有一個藥品包含3行，請問如果有三格的話該如何設定公式？要將3個領
用量加總
https://i.imgur.com/8lEK6UI.jpg
3格加總應為86，但只顯示8，請大師幫我解惑
※ 編輯: ayudow912 (42.75.85.184), 03/20/2019 16:07:36
37F：→ soyoso: 如有三列以上就會回到03/19 12:11所回的方式 03/20 16:11
38F：→ soyoso: 但當時的加總是歸類在同一藥品的最後一筆，目前要改為第一 03/20 16:14
39F：→ soyoso: 筆的話則是 03/20 16:14
41F：→ soyoso: 如不要輔助輔(連結內d欄的部分)的話 03/20 16:23
※ 編輯: ayudow912 (42.75.85.184), 03/20/2019 16:24:52
43F：→ soyoso: 先說，1.沒看到最後一筆藥品的下方是否還會有資料或是資料03/20 16:25
44F：→ soyoso: 有的話，是如何呈現的。模擬上就是最後一筆藥品下方無任何03/20 16:26
45F：→ soyoso: 資料了 03/20 16:26
46F：→ soyoso: 2.也不確定count(b欄)，應是為領用量是否除了藥品會鍵入該 03/20 16:27
47F：→ soyoso: 欄外就無其他資料，模擬上也是只有藥品才會鍵入03/20 16:28
48F：→ soyoso: 公式內+2方面也要依實際表格調整，會於連結內+2是因為標題03/20 16:32
49F：→ soyoso: 列在第一列且為文字或空白儲存格再加1，實際上是否會影響 03/20 16:33
50F：→ soyoso: count計數數字上就要依原po實際情況了03/20 16:34
大師真強，知道我的問題，我的每個工作表最後一個藥品的數量都不對，請問有什麼方式
可以改善嗎？如圖黃色的部分
https://i.imgur.com/6nAkgDw.jpg
※ 編輯: ayudow912 (42.75.85.184), 03/20/2019 16:54:55
51F：→ soyoso: 這還是要看公式實際應用於原po檔案內如果寫了，截圖上沒有03/20 17:12
52F：→ soyoso: 看到這部分的資料03/20 17:12
53F：→ soyoso: 模擬一下L9:L146為連續數字的話03/20 17:26
55F：→ soyoso: 但無法確定L5是否有鍵入資料且為數字，如果是數字型態的話 03/20 17:28
56F：→ soyoso: 則是+6 03/20 17:28
+6成功了，感謝大師～
現在我需要製作另一個工作表，將原先工作表中6個月內過期的藥品名稱、庫存量及有效
期限帶入新製作的工作表，請問如何設定？
https://i.imgur.com/GMq0MTU.jpg
※ 編輯: ayudow912 (42.75.85.184), 03/21/2019 08:47:55
57F：→ soyoso: 原po效期是如何鍵入的，1101231或是2021/12/31再去改儲存 03/21 10:16
58F：→ soyoso: 格格式 03/21 10:16
原效期格式1101231
※ 編輯: ayudow912 (42.75.85.184), 03/21/2019 10:20:34
59F：→ soyoso: 我的問題不是效期格式是什麼，而是鍵入資料是什麼 03/21 10:31
60F：→ soyoso: 鍵入2021/12/31這樣符合日期序列值是有可能於儲存格格式內 03/21 10:32
61F：→ soyoso: 改為1101231的方式顯示 03/21 10:33
因為這是系統報表轉換的excel檔案，我不確定他是否為日期序列，但是他上面顯示「通
用格式」
※ 編輯: ayudow912 (42.75.85.184), 03/21/2019 10:39:51
63F：→ soyoso: 庫存數量、有效期限方面再去改index儲存格範圍(欄) 03/21 11:16
我以大師的公式設定有成功，但是我想要合併F2及H2兩個公式，但是只有第一個成功，往
下來拉都沒東西
https://i.imgur.com/D2777Ap.jpg
※ 編輯: ayudow912 (42.75.85.184), 03/21/2019 14:01:17
64F：→ soyoso: 以回文舉例的格式，效期只出現在a欄有藥品名稱處的話03/21 14:43
66F：→ soyoso: 庫存和有效期限就以函數vlookup03/21 14:45
大師～H2公式後面被截掉了，看不到，另外公式列加上Ctrl +shift +enter,這個我不太
懂意思
※ 編輯: ayudow912 (42.75.85.184), 03/21/2019 15:45:41
67F：→ soyoso: 被截掉是指什麼？h2公式並無被截掉 03/21 15:49
68F：→ soyoso: 還是指的是分頁線蓋到字串，蓋到的字串為逗號,03/21 15:50
69F：→ soyoso: 加上組合鍵為鍵入公式，先不要按下enter或是選取其他儲存03/21 15:52
70F：→ soyoso: 格之類的，按下組合鍵ctrl+shift+enter 03/21 15:52
請問同一品項有兩個效期6個月內過期，下面那個效期有辦法顯示上面那一個藥名嗎？
https://i.imgur.com/69x1p5f.jpg
設定到另一個工作表藥名變成0
https://i.imgur.com/G9t27TY.jpg
※ 編輯: ayudow912 (42.75.85.184), 03/21/2019 16:31:22
71F：→ soyoso: 那就用一欄輔助欄將空白儲存格的藥名填上，index就以該輔03/21 16:36
72F：→ soyoso: 助欄 03/21 16:36
73F：→ soyoso: 這也就是為什麼我的回文上要假設"以回文舉例的格式，效期 03/21 16:37
74F：→ soyoso: 只出現在a欄有藥品名稱處的話" 03/21 16:37
75F：→ soyoso: 這也是原po模擬資料上所呈現的03/21 16:38
76F：→ soyoso: 因此請原po模擬上就不要是以這種方式提供，不然就只會讓回03/21 16:39
77F：→ soyoso: 文的人在使用公式上的不確性03/21 16:39
78F：→ soyoso: 不確定性 03/21 16:40
79F：→ soyoso: 因藥品出現複數筆數，不以函數vlookup，而是同藥品名稱的 03/21 16:51
80F：→ soyoso: 公式index儲存格範圍改以效期的欄位 03/21 16:52
81F：→ soyoso: 庫存數量(這我也不確定是要以加總後的還是個別的)，假設是 03/21 16:54
82F：→ soyoso: 個別的好了，如藥品名稱和效期來看是唯一的話，以函數03/21 16:55
83F：→ soyoso: sumifs03/21 16:55
84F：→ soyoso: 非唯一的話，一樣同藥品名稱，改範圍 03/21 16:56
85F：→ soyoso: 但如果要用輔助欄了，還不如再多設一欄，如03/21 11:15回 03/21 16:58
86F：→ soyoso: 文的，公式還比較簡潔，還不用按組合鍵 03/21 16:59
87F：→ soyoso: 如果什麼輔助欄都不想要的話 03/21 18:02
大師～我用你的公式套用練習的檔案，我要的東西都有跑出來
https://i.imgur.com/FNoC4NT.jpg
但是我套到我的檔案時有一些6個月內到期的藥品就沒有出現
https://i.imgur.com/cVudumF.jpg
套完公式跑出來的結果如下
https://i.imgur.com/6VFQse4.jpg
第二張圖黃色的標示有在第三張圖出現
但是第二張圖綠色的標示在第三張圖沒有出現
※ 編輯: ayudow912 (42.75.85.184), 03/22/2019 13:42:22
89F：→ soyoso: 公式是從儲存格a4開始拖曳的話，到儲存格a12時row('1月'!C 03/22 13:58
90F：→ soyoso: 17)，這樣表示儲存格a4是row('1月'!c9) 03/22 14:00
91F：→ soyoso: 1.a4為row(a1) 03/22 14:00
92F：→ soyoso: 2.row內不用工作表名稱 03/22 14:01
93F：→ soyoso: 3.其他工作表範圍的話，寫法可以'1月!e$9:e$300的方式 03/22 14:03
94F：→ soyoso: '1月'!e$9:e$300 03/22 14:03
感謝大師的提點，6個月到期的藥品已成功，現在剩庫存為0的部分，要顯示庫存為0的藥
品名稱（I2),我剛剛用了6個月到期藥品的公式下去改，但是沒有成功，要拜託大師指點
，感謝！
https://i.imgur.com/vYnDbvv.jpg
※ 編輯: ayudow912 (42.75.85.184), 03/25/2019 11:06:28
95F：→ soyoso: 要以small(if(...))的方式，if(e2:e300=0)←這個)刪除 03/25 11:21
96F：→ soyoso: small()←這個)要加於row(a1))後面03/25 11:22
大師～我失敗了，還是帶不出來，請幫忙解惑，感恩！
https://i.imgur.com/gtBuJg8.jpg
※ 編輯: ayudow912 (42.75.85.184), 03/27/2019 17:19:16
97F：→ soyoso: 未加回文的row(a1)的部分，這方面看03/21 18:03回文的連結03/27 17:31
98F：→ soyoso: ，裡面公式有03/27 17:31
我把small()加在row(a1)後面，還是不行，請大師幫忙
https://i.imgur.com/tPvWIs6.jpg
※ 編輯: ayudow912 (42.75.85.184), 03/28/2019 12:34:38
99F：→ soyoso: 03/21 18:03回文row(a1)不是加在那裡 03/28 15:36
100F：→ soyoso: 我03/25 11:22的回文是指small()←這個）要加於row(a1))後 03/28 15:37
101F：→ soyoso: 不是small()加在row(a1)後面 03/28 15:37
103F：→ soyoso: 個連結1.少用了small() 2.前後括號就要調整到適當的地方 03/28 15:41
我的公式終於沒錯誤了，但是沒有東西跑出來，求解惑
https://i.imgur.com/I9WYN3k.jpg
※ 編輯: ayudow912 (42.75.85.184), 03/29/2019 11:32:35
104F：→ soyoso: 有按組合鍵ctrl+shift+enter嗎？如果有的話公式前後應有 03/29 11:44
105F：→ soyoso: {=...}的大括號03/29 11:44
有按組合鍵了，但是還是空白
https://i.imgur.com/9VIim34.jpg
※ 編輯: ayudow912 (42.75.85.184), 03/29/2019 12:40:50
106F：→ soyoso: 將row(a1))))刪除一個，row($1:$300))增加一個 03/29 14:15
成功了，但是往下拉都無法出現
https://i.imgur.com/laxgG4c.jpg
※ 編輯: ayudow912 (42.75.85.184), 03/29/2019 15:41:59
107F：→ soyoso: ？有按組合鍵嗎？有的話公式前後應有{=...}的大括號，這不 03/29 15:56
108F：→ soyoso: 是上面回文就提到的 03/29 15:56
大師～太感動了....我成功了，剩下最後一個問題，我的公式往下拉會把最後一筆一直重
複
https://i.imgur.com/Wp6ejbA.jpg
https://i.imgur.com/L5gkdmF.jpg
※ 編輯: ayudow912 (223.138.185.98), 04/01/2019 15:12:20
109F：→ soyoso: 條件上除了=0外，再多加一個不為<>空字串"" 04/01 15:29
110F：→ soyoso: 寫法03/21 18:03內的公式有 04/01 15:31
請問如果我要設定半年內過期的藥品要顯示X
我要把哪裡改掉
https://i.imgur.com/B48m2rx.jpg
※ 編輯: ayudow912 (223.138.185.98), 04/02/2019 17:31:53
111F：→ soyoso: 公式內的判斷，前項就是判斷日期的部分，if為真就顯示回文 04/02 17:48
112F：→ soyoso: 要的字元(串) 04/02 17:48