仓库进销存表[“EXCEL应用技巧班”版块培训教程]

温柔的雪 · 发表于 2007-4-27 15:25

谢谢

yuhai0012 · 发表于 2007-5-1 19:15

<div class="msgheader">QUOTE:</div><div class="msgborder">以下是引用jx1680000在2007-4-27 9:10:26的发言： <div class="msgheader">QUOTE:</div><div class="msgborder">以下是引用yuhai0012在2007-4-26 14:43:52的发言：   </div>老师,我在这个表里遇到2个问题:1.在装箱单表格里没有数据时按"康售后"",会把标题输入到"康售后"里.                                                   2.在"康售后"实行自动筛选后,装箱单录进"康售后"里的数据会覆盖原来的数据.

esprit · 发表于 2007-4-30 17:08

顶  [em08][em08][em08]

jx1680000 · 发表于 2007-5-4 13:29

老师,我在这个表里遇到2个问题:1.在装箱单表格里没有数据时按"康售后"",会把标题输入到"康售后"里. 2.在"康售后"实行自动筛选后,装箱单录进"康售后"里的数据会覆盖原来的数据.<hr/>回复您两个问题：1.标题在数据空白是会录入这是一个BUG，但问题不大，你在代码前面加一个限制就好了（“康售后”宏里）if y>4 then ...end if,中间就是整个 With Sheets("装箱单")...end with的代码。传上样子：2.在"康售后"实行自动筛选后,装箱单录进"康售后"里的数据会覆盖原来的数据！这个问题在前面的仓库录入的代码里就谈到过的问题，这也是那里我为什么不用，“Range("a65536").End(xlUp).Row”的原因，在筛选状态下是不能得出正确的行数的。这里我以为你不会有筛选的，所以这样写了。解决的方法：1。是在录入时打开筛选。2.把这里的计数行用仓库表里的那种计数法：x=1Do While Not (IsEmpty(.Cells(x, 1).Value))             x = x + 1 Loop这种方法，那怕你是隐藏也是一样计数的。这里我就不做了，你试一试，不行我们再谈。

jx1680000 · 发表于 2007-5-4 13:34

<div class="msgheader">QUOTE:</div><div class="msgborder">以下是引用apollo_ju在2007-4-26 12:25:04的发言： <table cellspacing="0" cellpadding="0" width="949" border="0" xstr="" style="WIDTH: 713pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="75" style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742;"></col><col></col><col span="3" width="110" style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022;"></col><col></col><col width="75" style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742;"></col><col></col><col width="75" style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742;"></col><col></col><col span="2" width="61" style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230;"></col><col></col><col width="75" style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742;"></col><col></col><col span="2" width="61" style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230;"></col><col></col><col width="75" style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742;"></col><col></col></colgroup><tbody><tr height="20" style="HEIGHT: 15pt; mso-height-source: userset;"><td class="xl28" width="75" height="20" style="BORDER-RIGHT: #ece9d8 1pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8 1pt; WIDTH: 56pt; BORDER-BOTTOM: windowtext 1.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">客户代码</td><td class="xl28" width="110" style="BORDER-RIGHT: #ece9d8 1pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent;">客户名称</td><td class="xl28" width="110" style="BORDER-RIGHT: #ece9d8 1pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent;">品名</td><td class="xl28" width="110" style="BORDER-RIGHT: #ece9d8 1pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent;">型号</td><td class="xl29" width="75" style="BORDER-RIGHT: #ece9d8 1pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 56pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: white;">期初库存</td><td class="xl30" width="75" style="BORDER-RIGHT: #ece9d8 1pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 56pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: #ccffff;">入库数量</td><td class="xl30" width="61" style="BORDER-RIGHT: #ece9d8 1pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 46pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: #ccffff;">重量</td><td class="xl30" width="61" style="BORDER-RIGHT: #ece9d8 1pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 46pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: #ccffff;">体积</td><td class="xl31" width="75" style="BORDER-RIGHT: #ece9d8 1pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 56pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: #ff99cc;">出库数量</td><td class="xl31" width="61" style="BORDER-RIGHT: #ece9d8 1pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 46pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: #ff99cc;">重量</td><td class="xl31" width="61" style="BORDER-RIGHT: #ece9d8 1pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 46pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: #ff99cc;">体积</td><td class="xl32" width="75" style="BORDER-RIGHT: #ece9d8 1pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 56pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent;">期末库存</td></tr><tr height="20" style="HEIGHT: 15pt; mso-height-source: userset;"><td height="20" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-ignore: style;">1</td><td xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style;">1</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style;"></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style;"></td><td class="xl24" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: white; mso-ignore: style;">　</td><td class="xl26" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: #ccffff; mso-ignore: style;">　</td><td class="xl26" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: #ccffff; mso-ignore: style;">　</td><td class="xl26" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: #ccffff; mso-ignore: style;">　</td><td class="xl27" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: #ff99cc; mso-ignore: style;">　</td><td class="xl27" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: #ff99cc; mso-ignore: style;">　</td><td class="xl27" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: #ff99cc; mso-ignore: style;">　</td><td class="xl25" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style;"></td></tr><tr height="20" style="HEIGHT: 15pt; mso-height-source: userset;"><td height="20" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-ignore: style;">1</td><td xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style;">1</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style;"></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style;"></td><td class="xl24" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: white; mso-ignore: style;">　</td><td class="xl26" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: #ccffff; mso-ignore: style;">　</td><td class="xl26" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: #ccffff; mso-ignore: style;">　</td><td class="xl26" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: #ccffff; mso-ignore: style;">　</td><td class="xl27" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: #ff99cc; mso-ignore: style;">　</td><td class="xl27" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: #ff99cc; mso-ignore: style;">　</td><td class="xl27" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: #ff99cc; mso-ignore: style;">　</td><td class="xl25" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style;"></td></tr><tr height="20" style="HEIGHT: 15pt; mso-height-source: userset;"><td height="20" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">2</td><td xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">2</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td><td class="xl24" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white;">　</td><td class="xl26" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffff;">　</td><td class="xl26" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffff;">　</td><td class="xl26" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffff;">　</td><td class="xl27" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ff99cc;">　</td><td class="xl27" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ff99cc;">　</td><td class="xl27" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ff99cc;">　</td><td class="xl25" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td></tr><tr height="20" style="HEIGHT: 15pt; mso-height-source: userset;"><td height="20" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">3</td><td xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">3</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td><td class="xl24" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white;">　</td><td class="xl26" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffff;">　</td><td class="xl26" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffff;">　</td><td class="xl26" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffff;">　</td><td class="xl27" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ff99cc;">　</td><td class="xl27" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ff99cc;">　</td><td class="xl27" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ff99cc;">　</td><td class="xl25" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td></tr></tbody></table></div>我不知如何来回答， 您上传一份表格好吗？ 把您的要求写在里面， 只要我能做到的，一定给您回复！ 呵呵！以后在论坛里向别人提问时， 给别人创造一个好的回复条件， 朋友们才可能更好的出手呀1

jx1680000 · 发表于 2007-5-4 13:42

说明： １．在第二行紫色单元格处怎样用函数显示月份？ ２．用函数在第一行橙色单元格显示按单元格颜色筛选或按单元格字体颜色筛选后，表中金额的汇总.按照您写的公式SUBTOTAL(109,求和列)这是个函数公式还是个VBA代码写的公式我用VBA也试了显示不出结果？ ３．分表按钮的作用是将B列中符合指定条件的整行复制到各分表中.如B列中等于北部的就将北部整行复制到北部表格中.请您指出分表代码中的错误.点击分表后显示编译错误.语法错误.这是您的附件：

rsrsrs123 · 发表于 2007-5-9 20:06

很好,谢谢！

yuhai0012 · 发表于 2007-5-8 14:54

<div class="msgheader">QUOTE:</div><div class="msgborder">以下是引用jx1680000在2007-5-4 13:29:20的发言： 老师,我在这个表里遇到2个问题:1.在装箱单表格里没有数据时按"康售后"",会把标题输入到"康售后"里. 2.在"康售后"实行自动筛选后,装箱单录进"康售后"里的数据会覆盖原来的数据.<hr/>回复您两个问题：1.标题在数据空白是会录入这是一个BUG，但问题不大，你在代码前面加一个限制就好了（“康售后”宏里）if y>4 then ...end if,中间就是整个 With Sheets("装箱单")...end with的代码。传上样子：2.在"康售后"实行自动筛选后,装箱单录进"康售后"里的数据会覆盖原来的数据！这个问题在前面的仓库录入的代码里就谈到过的问题，这也是那里我为什么不用，“Range("a65536").End(xlUp).Row”的原因，在筛选状态下是不能得出正确的行数的。这里我以为你不会有筛选的，所以这样写了。解决的方法：1。是在录入时打开筛选。2.把这里的计数行用仓库表里的那种计数法：x=1Do While Not (IsEmpty(.Cells(x, 1).Value))             x = x + 1 Loop这种方法，那怕你是隐藏也是一样计数的。这里我就不做了，你试一试，不行我们再谈。</div>老师,我可能是人笨了,不知怎么了,公式改了,运行时还是显示调试.

[此贴子已经被作者于2007-5-9 9:03:23编辑过]

秋水长天 · 发表于 2007-5-9 20:39

不错,很有启发,大有收获,顶了!

apollo_ju · 发表于 2007-5-9 21:08

老师!能给个电话挖?我想做个仓库进销存的表格!现在无从下手.EXCEL知识太少!

		自动登录	找回密码
密码			注册

[技巧] 仓库进销存表[“EXCEL应用技巧班”版块培训教程]

回复“杨柳青”