Word 中的明细帐单
我想以 Word 邮件合并的形式为非营利组织生成逐项账单。
目前，源数据以类似数据透视表的结构存储在 Excel 中（这不是我的想法），每个客户的每个项目类型有两个单元格（欠款和已付款）。 （每个客户有一行，每个项目类型有两列）。现有数据采用这种结构，但如果有一种方法可以自动将其转换为更好的数据，我愿意接受想法。
我目前有一个由 Word 宏生成的 Word 邮件合并文档，该宏为每对列（项目类型）写入 Word IF 字段代码。 （并非所有客户都拥有所有项目类型，因此我需要 IF 字段来排除每个客户不欠的项目）。
问题是只有我知道如何操作这个系统，我不再有时间自己生成所有账单。
理想情况下，我会编写自己的计费应用程序（这也可以解决许多其他问题），但我也没有时间这样做。
有没有办法（相当快地）建立一个系统，让技术水平较低的人在没有我的情况下完成所有计费？
我们研究了 QuickBooks，但它无法导入现有交易。
I'd like to generate itemized bills for a non-profit as a Word mail merge.
Right now, the source data is stored in Excel in a pivot-table-like structure (this wasn't my idea) with two cells (owed and paid) per item type per customer. (Each customer has one row, and each item type has two columns). The existing data is in this structure, but if there's a way to automatically convert it to something better, I'm open to ideas.
I currently have a Word mail merge document generated by a Word macro that writes a Word IF field code for every pair of columns (item type). (Not all customers have all item types, so I need IF fields to exclude the ones each customer doesn't owe).
The problem is that only I know how to operate this system, and I no longer have time to generate all of the bills myself.
Ideally, I'd write my own billing app (this would also solve a number of other problems), but I don't have time for that, either.
Is there any way to (reasonably quickly) make a system to allow somewhat less technical people to do all of the billing without me?
We've looked into QuickBooks, but it cannot import the existing transactions.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这是我想到的一个解决方案：
将 Excel 数据处理为非透视形式，每个事务一行（每个客户的每个项目，或当前结构中的每对单元格）。事实上，我已经在尝试将交易导入 QuickBooks 时失败了。
每一行将变成帐单中的一个项目（与当前的旋转表单相反，其中每行变成包含许多项目的整个页面）
然后，创建一个 Word 合并文档，检查当前行是否是第一行不同的客户，如果是，则将账单的整个文本（而不是项目）包含在两个（非常大的）IF 字段代码中。
所有行都会在账单中生成一个项目。
每个客户的最后一行将跟随老客户文本的后半部分（项目后面的部分）。
每个客户的第一行前面都会有新客户文本的前半部分（项目之前的部分）。
需要两个 IF 来处理第一个和最后一个客户。
我必须在电子表格中添加一个公式列，告诉我是否插入前缀、后缀或两者都不插入，因为这在 Word 中是不可能做到的。 （你无法查看下一条记录）
合并生成器仍然是一个复杂的怪物，但它永远不需要改变；将从源数据中选取新的项目类型。 （与当前的合并生成器不同，当前的合并生成器需要为每个新项目类型生成新的宏生成的字段代码）。即便如此，我可能会制作一个宏来生成它，以防文本发生变化。 （在域代码中编辑文本非常烦人）。
Here is one solution I've thought of:
Manipulate the Excel data into a non-pivoted form with one row per transaction (per item per customer, or per pair of cells in the current structure). I actually already did this in a failed attempt to import the transactions to QuickBooks.
Each row would turn into one item in the bill (as opposed the the current, pivoted, form where each row turns into an entire page with many items)
Then, make a Word merge document that checks whether the current row is the first row for a different customer, and, if it is, include the entire text of the bill (as opposed to the items) in two (very large) IF field codes.
All of the rows would generate an item in the bill.
Each customer's last row would follow that with the second half of the text for the old customer (the part after the items).
Each customer's first row would precede it with the first half of the text for the new customer (the part before the items).
Two IFs are necessary to handle the first and last customer.
I would have to add a formula column to the spreadsheet that tells me whether to insert the prefix, the suffix, or neither, as that's impossible to do in Word. (You can't peek the next record)
The merge generator would still be a complicated monstrosity, but it would never need to change; new item types would be picked up from the source data. (Unlike the current merge generator which requires a new macro-generated field code for each new item type). Even so, I'd probably make a macro to generate it in case the texts change. (Editing texts inside field codes is very annoying).
Word 和 Excel 都内置了 VBA，如果您使用它们，那么您的功能将不受限制。这种类型的应用程序实际上应该由数据库（Access）驱动，该数据库将生成逐项账单，添加序言以及逐项后的底部总计和杂项。
但是，如果您需要坚持使用 Excel 数据，则可以将 Excel 视为数据库，在 Excel 中编写 VBA 代码来生成账单，然后在 Excel (VBA) 中组装 Word 文档或将其放入工作表格式只需要在 Word 模板中进行一些简单的工作即可将其全部导入。
Both Word and Excel have VBA built-in and if you use these, then you are not limited in what you can do. This type of application really should be driven from a database (Access) which would generate itemized bills, add a preamble, and a bottom total and miscellaneous after the itemization.
However, if you need to stick with the Excel data, then think of Excel as the database, write your VBA code in Excel to produce the bills, and then either assemble the word document in Excel (VBA) or place it in a worksheet format that only requires trivial work from a Word template to pull it all in.