如果以前提出过这个问题，请带我去。我是因为我不知道该如何搜索。
因此，情况如下所示，我有一个销售报告，需要一个月，国家，分销商和产品列，这些销售列和产品列是预定义的，然后是该数据输入的值和单位列。
根据描述表格
截至今天，我一直在手动创建年度，月份的组合，国家，分销商和产品。但是我想知道是否有一种方法可以根据一组表或国家/分销商/产品的目录来填充此列表，这些列表可以在需要时无用更新，然后添加了Country/Distribure/Product的新组合在表的底部，将它们乘以保存在单独的表中的所有一个月/年组合，同时弥补目录更改之前输入的数据。
数据应如何流动并组合以创建数据输入布局
我以前完成了通过将两个预定义的表（一个目录表和一个月/年表）组合在一起，这是通过PowerQuery的，这给了我适当的布局，但是如果我更改一个表的值，当我更新时，新表将混合使用的输入数据在更新之前在那里。
我还愿意尝试使用此操作，但我需要一种创建动态目录的方法，这样我就不必每年手动复制12倍相同的组合，而我想保留的年份在我的桌子上...任何帮助都得到赞赏
if this question has been asked before, please lead me to it. I'm asking because I don't know how to search for this.
So the scenario is as follows, I have a sales report that needs to have a year, month, country, distribuitor and product columns that are predefined and then next to this, are a values and units columns for data input.
Table according to description
As of today, I've been manually creating the combination of year, month, country, distribuitor and product. But I was wondering if there might be a way to populate this list based on a set of tables or a catalogue of country/distribuitor/product that can be indepently updated when needed, which then, adds the new combinations of country/distribuitor/product at the bottom of the table and multiplys them by all the month/year combinations that are kept in a separate table, while mantaining the data that was input before the changes in the catalogue.
Idea of how the data should flow and combine to create a data input layout
I've previously done this with powerquery by combining two predefined tables(A catalogue table and a month/year table), this gives me the proper layout, but if i change the values of one table, when I update, the new table mixes up the input data that was there before the update.
Im also willing to try this in access but i need a way to create a dynamic catalogue so that i don't have to manually copy-paste 12 times the same combination for each year and that times the amount of years that i want to keep in my table... Any help is appreciated
发布评论
评论（1）
为了使您的销售报告保持最新状态，您确实需要三个初始表格 - 产品，分销商和销售。
产品将包含列[产品名称]，[产品代码]，[分销商]，[单位值]。
分销商将包含[分销商名称]，[country]。
销售看起来像您提供的图像“根据描述”的表“表”
设置了您的销售列中的所有列，以填充A vlookup 或 index-match-match 公式 formula [产品代码]。这样，如果您更新供应商或产品的详细信息，您的销售表也会自动更新。
我还建议在销售表中为您的[值]列创建一个公式，以减少用户错误。
您可以通过使用数据验证列表填写[产品代码]销售表中的列。
这是一个粗略的设置！
像这样设置它，这意味着您也应该能够使用此数据在 pivot table。
我希望这会有所帮助！
In order for your sales report to keep itself up to date, you really need three initial tables - Products, Distributors, and Sales.
Products would contain columns [Product Name],[Product Code],[Distributor],[Value per Unit].
Distributors would contain columns [Distributor Name],[Country].
Sales could look like your supplied image 'table according to description'
Set up all the columns in your sales columns to fill with a VLOOKUP or INDEX-MATCH formula except for [Product Code]. That way if you update the details of a supplier or product, your Sales table updates automatically as well.
I'd also recommend creating a formula for your [Values] column in the Sales table to reduce user error.
You can further reduce user error by using Data Validation Lists to fill in the [Product Code] column in your Sales table.
Here's a rough setup!
Setting it up like this means you should also be able to use this data in a Pivot Table.
I hope this helps a bit!