“计数”基于数据透视表中设置的过滤器
因此,我们导入了查询的数据,然后根据该查询创建了一个数据透视表。它本质上是一个文件列表,每个文件都有唯一的 ID 号和各种属性(文件扩展名、文档类型、哈希值等)。无论如何,该数据基于来自不同程序的关键字搜索的“点击率”。这意味着同一个唯一 ID 可能有多个记录,因为存在多个命中。
数据透视表允许我们通过过滤掉某些条件来说明/操作(例如,我们不想要某些文件扩展名,或者我们不想要带有FIELD X或FIELD Y0的记录。报告很好,但我们想要制作一个表格/query/report/whatever 会提取一个忽略重复项的“计数”(基于唯一 ID),例如,一旦在数据透视表中设置了所有过滤器,我们就需要根据数据透视表的过滤器/输出。像这样的东西:
.PDF 文件:200 |字段 x 的总计 |总场 y |等等
.DOCX 文件:320 |字段 x 的总计 |字段 y 的总计 |等等
,我们希望忽略计数中相同唯一 ID 的重复项。
考虑到我们将经常动态地操作数据透视表,最好的方法是什么?理想的情况是打开数据透视表和另一个对象(表单/报告/等),并且随着数据透视表的操作,显示的任何内容也会发生变化。
So, we have imported data which we have queried and then created a pivot table off that query. It is essentially a list of files, each having unique ID numbers, and various attributes (file extension, type of document, hash, etc). In any case, this data is based off "hits" on keyword searches from a different program. This means that there might be multiple records for the same Unique ID since there are multiple hits.
The pivot table allows us to illustrate/manipulate via filtering out certain criteria (e.g. we don't want certain file extensions or we don't want records with FIELD X or FIELD Y0. The report is fine, but we want to make a form/query/report/whatever that will pull a "count" (based off unique ID) which ignores duplicates. For example, once all the filters are set in the pivot table, based on the filters/output of the pivot table, we want something like this:
.PDF Files: 200 | total for field x | total field y | etc
.DOCX files: 320 | total for field x | total for field y | etc
Obviously, we want to ignore duplicates of the same Unique ID in the counts.
What is the best way to do this considering we will be manipulating the pivot table dynamically and often? The ideal scenario would to have the pivot table and another object (form/report/etc) open, and as the pivot table is manipulated whatever is displaying counts changes as well.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
这里有一些非常粗略的笔记。它们仅经过最低限度的测试,并且使用 IN 将是一场具有很多值的灾难,但是,切换这一轮并使用排除列表很容易。也许你能得到一些想法。
Here are some very rough notes notes. They are only minimally tested, and using IN would be a disaster with a lot of values, however, it would be easy enough to switch this round and use an excluded list. Perhaps you can get some ideas.
如果有帮助的话:
http://lazyvba.blogspot.com/2010 /11/improve-your-pivot-table-to-count.html
它将根据您想要的数字为您提供 ID 号的唯一计数,并且您仍然可以操纵枢轴
if that helps:
http://lazyvba.blogspot.com/2010/11/improve-your-pivot-table-to-count.html
it will get you the unique count of ID numbers by numbers you want, and you can still manipulate the pivot