我一直在绞尽脑汁试图弄清楚如何制作如附图所示的统计表:
我有大约 670k 个对象(Item_id
),分为大约 1000 个 ID(FID
),我需要制作一个表格来显示每个 ID 出现一次、两次、三次、四次、五次、六次或更多次的对象数量。
我拥有的是:
FID Item_ID
1 TH22
1 Th2
1 Th9
1 TH22
2 TH3
2 TH22
2 TH99
所以最后(这个非常小的例子)它应该看起来像:
ID Once Twice Three times Four Times Five Times Six Times or greater
1 2 1 0 0 0 0
2 3 0 0 0 0 0
我认为 Excel 可以做到这一点,但我可能需要使用 R 或 SQL。但我真的很感激任何关于如何制作此表的想法和说明。
祝大家一切顺利
答案1
假设数据如示例所示,在F2
单元格中输入以下公式:
=SUMPRODUCT(($B$2:$B$1001=$E2)*(COUNTIFS($B$2:$B$1001,$E2,$C$2:$C$1001,$C$2:$C$1001)=F$1)/F$1)
标题F:K
是出现次数为 1、2...6 次的条目。对于出现次数为 6 次或更大的条目,此公式无法满足您的要求。应说明所有出现次数,因此,如果出现次数超过 6 次的条目,则应将其放在表头中,因此您必须通过添加数字 7、8、9 等来使其更长。由于我将找到的条目数除以出现次数以找到确切的条目数,因此不可能(至少我目前认为如此)找到出现次数为 6 次或更大的条目。我需要明确说明这个数字。
我还添加了总计和检查控件,这样就不会出错。检查单元格应为 0。每列的总计将是,例如对于 F 列 ->sum(F2:F11)*F1
对于其他列,只需拖动公式即可。检查将从 item_ID 计数中减去所有列的总计。