作者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/cn.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