如何在复制和粘贴Excel公式到列的末尾时选择下一列而不是下一行?

huangapple go评论42阅读模式
英文:

How do I select the next column instead of the next row when copying and pasting an Excel Formula to the end of a column?

问题

所以我的公式是=SUMPRODUCT(--(RawData!B2:B5580=TRUE))/COUNTA(RawData!B2:B5580)。当我复制它时,它会变成这样-

=SUMPRODUCT(--(RawData!B3:B5581=TRUE))/COUNTA(RawData!B3:B5581)
=SUMPRODUCT(--(RawData!B4:B5582=TRUE))/COUNTA(RawData!B4:B5582)

等等。

我想要的是在下一行(原始方程的下方)选择下一列而不是下一行。这是我要找的-

=SUMPRODUCT(--(RawData!B2:B5580=TRUE))/COUNTA(RawData!B2:B5580)
=SUMPRODUCT(--(RawData!C2:C5580=TRUE))/COUNTA(RawData!C2:C5580)
=SUMPRODUCT(--(RawData!D2:D5580=TRUE))/COUNTA(RawData!D2:D5580)

我已经在互联网上搜索过,但没有找到解决方法。如果您能帮助的话,谢谢!

英文:

So my formula is =SUMPRODUCT(--(RawData!B2:B5580=TRUE))/COUNTA(RawData!B2:B5580). and when I copy it down it does this-

=SUMPRODUCT(--(RawData!B3:B5581=TRUE))/COUNTA(RawData!B3:B5581)
=SUMPRODUCT(--(RawData!B4:B5582=TRUE))/COUNTA(RawData!B4:B5582)

etc.

What I want is for the next row (the one below the original equation) to select the next column instead of the next row. This is what I'm looking for-

=SUMPRODUCT(--(RawData!B2:B5580=TRUE))/COUNTA(RawData!B2:B5580)
=SUMPRODUCT(--(RawData!C2:C5580=TRUE))/COUNTA(RawData!C2:C5580)
=SUMPRODUCT(--(RawData!D2:D5580=TRUE))/COUNTA(RawData!D2:D5580)

I've scoured the internet to no avail. Please help if you can. Thank you!

答案1

得分: 1

=SUMPRODUCT((INDEX(RawData!$B$2:$XFD$5580,0,ROW(A1))=TRUE)/COUNTA(INDEX(RawData!$B$2:$XFD$5580,0,ROW(A1))))

英文:

Use INDEX:

=SUMPRODUCT((INDEX(RawData!$B$2:$XFD$5580,0,ROW(A1))=TRUE)/COUNTA(INDEX(RawData!$B$2:$XFD$5580,0,ROW(A1))))

如何在复制和粘贴Excel公式到列的末尾时选择下一列而不是下一行?

答案2

得分: 0

这里有一种解决方法。取出您的查找表(其中包含您要为百分比求和的值)。仅选择您要进行筛选的字段。选择这些字段并将它们粘贴到一个工作表中(主工作表)。在您粘贴了要进行筛选的字段之后,选择该表的其余部分并将其复制。创建另一个工作表(原始数据)并粘贴它,转置。这是非常重要的部分。当您右键单击并出现粘贴选项时,选择看起来像是将俄罗斯方块向左旋转的那个选项;那就是您的转置粘贴按钮。

现在您有了查找表、原始数据表和主工作表,您可以简单地复制转置列标题并将它们粘贴到主工作表上所选的列旁边。请注意,当您筛选这些字段时,转置的列将会消失并在选择时重新出现。

现在所有这些都已完成,将您的公式放在转置列标题旁边的单元格中(=SUMPRODUCT(--(原始数据!A4:HFP4=TRUE))/COUNTA(原始数据!A4:HFP4))。在这样做之后,将其复制并粘贴到转置列的末尾,如果您想要精确显示百分比,可以从主页选项卡中的数字框中选择“百分比”。

祝您好运并玩得开心!

英文:

Here's one way to solve this. Take your lookup table (the one with the values you're looking to sum for a percentage). Take only the fields that you want to filter on. Select those fields and paste them into one sheet (MainSheet). After you've pasted the fields you want to filter on, select the rest of that table and copy it. Make another sheet (RawData) and paste it tranposed. That's the very important part there. When you right-click and have your paste options, select the one that looks like it's rotating a tetris block to the left; that is your transpositional pasting button.

Now that you have your Lookup table, your RawData Table, and your MainSheet, you can simply copy the transposed column headers and paste them next to your selected columns on the MainSheet. Note now that when you filter those fields the transposed columns will disappear and reappear with those selects.

Now that all that has been done, put your formula in the cell next to your transposed column header (=SUMPRODUCT(--(RawData!A4:HFP4=TRUE))/COUNTA(RawData!A4:HFP4)). After doing that, copy and paste it all the way down to the end of your transposed columns and, if you want to be fancy, make it a percentage by selecting Percentage from the number box in the Home tab.

Good luck and have fun!

huangapple
  • 本文由 发表于 2020年1月4日 01:38:11
  • 转载请务必保留本文链接:https://go.coder-hub.com/59582949.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定