压缩具有重叠索引/重复单词出现的Excel数据 [英] Condensing Excel data with overlapping index/repetitive word occurrence

问题描述

我有一个格式如下的excel工作表：

I have an excel sheet that is formatted like so:

我想将其格式化为这样：

I would like to format it to be something like this:

这是大约40,000个单元格的信息，所以有没有办法做到这是不是手动？

It is about 40,000 cells of information, so is there any way to do this that isn't manually?

推荐答案

你可以使用= SUMIF来实现这一点，因为你似乎有数字作为值。
创建一个新工作表，将A列从数据表复制到新工作表，并删除重复的工作表。将数据表中的第1行复制到新工作表。
在表2单元格B2中使用此公式：

You could probably use =SUMIF to achieve this, since you appear to have numbers as values. Create a new sheet, copy column A from your data sheet to your new sheet and remove duplicates. Copy row 1 from your data sheet to your new sheet. Use this formula in sheet 2 cell B2:

=SUMIF(Sheet1!$A:$A;Sheet2!$A2;Sheet1!B:B)

将公式向右拖动，然后向下拖动。

Drag the formula to the right, then down.

这篇关于压缩具有重叠索引/重复单词出现的Excel数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！