Excel中如何对多范围单元格批量执行BITAND运算并统计非零配对数
无宏高效统计实现方案
- 适用场景:完全不需要VBA、不需要生成多列辅助列,替代手动逐行逐列生成BITAND运算结果的低效流程,哪怕单列为200个数值也能快速计算
- 核心逻辑:利用Excel内置的数组广播/数组运算能力,一次性完成所有A、B列数值对的BITAND运算与非零值统计,全程无额外单元格写入负载
具体操作
- 找到任意空白单元格,根据你的Excel版本选择对应公式:
- Microsoft 365、Excel 2021及以上(支持动态数组):假设第一列数值范围为
A2:A201,第二列数值范围为B2:B256,直接输入公式:=SUMPRODUCT(--(BITAND(A2:A201, TOROW(B2:B256))<>0)) - Excel 2019及更早无动态数组的版本:输入以下公式后,按下
Ctrl+Shift+Enter组合键触发数组运算:=SUMPRODUCT(--(BITAND(A2:A201, TRANSPOSE(B2:B256))<>0))
- Microsoft 365、Excel 2021及以上(支持动态数组):假设第一列数值范围为
- 根据你的实际数据范围替换公式里的单元格地址,公式会直接输出最终的非零配对总数量。
- 如果选定的范围包含空白单元格,可在公式中增加空值判断,避免将空白单元格默认为0参与运算。
公式计算原理
TOROW(纵向B列范围)/TRANSPOSE(纵向B列范围)会把纵向排列的B列数值转换为横向行向量,和纵向的A列范围形成广播匹配BITAND(纵向A列范围, 横向B列范围)会自动生成「A列数值总数 × B列数值总数」维度的运算矩阵,自动完成所有数值对的BITAND计算,完全不需要手动逐行逐列生成辅助列--(运算矩阵<>0)会把所有BITAND结果非零的位置标记为1,结果为零的位置标记为0SUMPRODUCT直接累加所有标记为1的数值总和,就是需要统计的非零配对总数量
效率对比
传统手动生成辅助列的方案,需要在工作表中写入A列数量 × B列数量个单元格的临时运算结果,伴随大量的单元格读写操作;上述公式全程在Excel内存中完成所有计算,没有额外的单元格写入开销,哪怕两列各含200个数值(总配对数40000组),也能秒出计算结果。
准确性验证
代入你提供的测试示例(A列数值为1、2、4、8、16;B列数值为0、64、8、7),公式输出结果为4,和手动统计的非零配对数量完全一致。
内容的提问来源于stack exchange,提问作者user1285419
相关产品推荐
相关产品推荐

