帮助excel vba宏验证规则。 [英] Help with excel vba macro validation rule .

问题描述

大家好，



我在excel表格中有以下格式的数据。

位置Dept_Name Alloc％
纽约金融50.00 
斯德哥尔摩IT 45.00 
日内瓦服务30.00 
纽约IT 50.00 
斯德哥尔摩服务45.00 
 Geneva IT 60.00 
 Stockholm Finance 10.00 
 Geneva Corporate 10.00 

我想写一个宏根据以下规则验证数据是否正确。



对于每个位置，无论它与之关联多少个Dept名称，总分配％应始终等于100.00％

例如

纽约金融50.00 
纽约IT 50.00 
纽约总计= 100.00％
日内瓦服务30.00 
日内瓦IT 60.00 
日内瓦公司10.00 
日内瓦总计= 100.00％

同样适用于al l其他地点。



我们非常感谢任何帮助。



谢谢，

Aakar。



我的尝试：



我尝试将值存储在数组和数组中总结它们。

然而，我似乎没有得到组合，即位置和部门权利，因为一个特定的位置可能有多个部门。

解决方案

最简单的方法是先将数据排序到位置和部门顺序。然后，您可以对每个组的值求和。首先手动尝试，然后您可以轻松地看到每个步骤需要完成的操作。一旦你知道所有的步骤，你可以重复它们，只需记录宏。

解决方案1 ​​

使用数据透视表，然后你'我能够计算行和列总和。

请参阅：

数据透视表和数据透视图报告概述 - Excel [ ^ ]

创建数据透视表以分析工作表数据 - Excel [ ^ ]

数据透视表I：数据透视表入门报告i n Excel 2007 - Excel [ ^ ]

您必须在位置列上透视数据。



解决方案2

如果您想通过宏来做，请检查：

在Excel 2010中使用VBA创建数据透视表和图表 [ ^ ]



解决方案3

另一种方法是在单独的位置创建唯一位置列表工作表（Sheet2），然后添加公式数组：

 AB 
 Location1 = SUM（IF（Sheet1！

A

Hi All,

I have my data in the below format in an excel sheet.

Location	Dept_Name	Alloc%
New York	Finance	        50.00
Stockholm	IT	        45.00
Geneva	        Services	30.00
New York	IT	        50.00
Stockholm	Services	45.00
Geneva	        IT	        60.00
Stockholm	Finance 	10.00
Geneva	        Corporate	10.00

I want to write a macro that would validate that the data is correct based on the below rules.

For every location no matter how many Dept Names it may have associated with it, the total alloc% should always be equal to 100.00%
e.g.

New York	Finance	50.00
New York	IT	50.00
Total for New York = 100.00% 
Geneva	Services	30.00
Geneva	IT	        60.00
Geneva	Corporate	10.00
Total for Geneva = 100.00%

Same goes for all other locations.

Any help would be much appreciated.

Thanks,
Aakar.

What I have tried:

I have tried storing the values in an array & summing them up.
However, I do not seem to get the combination i.e. Location as well as Department right as one particular location could have multiple departments.

解决方案

The simplest way would be to sort the data first into location and department order. You can then sum the values for each group. Try it manually first, then you can easily see what needs to be done at each step. Once you know all the steps you can repeat them whil recording the macro.

Solution 1
Use pivot table, then you'll be able to calculate row & column sums.
See:
Overview of PivotTable and PivotChart reports - Excel[^]
Create a PivotTable to analyze worksheet data - Excel[^]
PivotTable I: Get started with PivotTable reports in Excel 2007 - Excel[^]
You have to pivot data on Location column.

Solution 2
If you would like to do it via macro, check this:
Creating PivotTable Reports and Charts with VBA in Excel 2010[^]

Solution 3
Another way is to create the list of unique locations in a separate worksheet (Sheet2), then add fomula array:

A            B
Location1    =SUM(IF(Sheet1!

A

这篇关于帮助excel vba宏验证规则。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！