将公式复制并粘贴到具有下拉选项卡的行的单元格中 [英] Copying and pasting a formula into cells of rows that have a drop down tab

问题描述

我正在使用一个公共使用文件，我将其下载并重新格式化为一个数据透视表：

I am working with a public use file, which I downloaded and reformatted into a pivot table:

我正在尝试将公式从单元格D5复制并粘贴到从我所提供的图像开始的每个细胞都以美国国家缩写开始，之后是任何大写字母（例如AKAleutians East，AKAleutians West，AKAnchorage等），看起来像这些细胞每三个细胞一样出现;然而，电子表格进一步下降，它们有时会出现在第六个单元格，第五个单元格等。另外一个问题是阵列可能会移动，下拉菜单之间的单元格数也会增加（例如，数组可能包含四个或五个单元格）。

I am trying to copy and paste the formula from cell D5 into every cell that begins with a US State abbreviation followed by any capital letter (e.g AKAleutians East, AKAleutians West, AKAnchorage, etc.) from the image I have provided, it looks like these cells appear every third cells; however, further down the spreadsheet, they will sometimes appear every sixth cell, fifth cell, etc. The other thing about this is that the array might shift as well as the number of cells between the drop down menus increase (for example, the array might contain four or five cells).

有没有办法这样做，还是需要以其他方式重新组织数据？

Is there any way to do this or do I need to reorganize my data in some other way?

谢谢！

推荐答案

如果您有一个列表，您可以轻松地做一个公式并跳过数据透视表。所以如果在你的一个列表中，你有A列的国家和B列的县和你显示的数据在一个名为DATA的表格，那么这样做。

If you have a list of the counties you could easily do a formula and skip the pivot table. So if in your one for one list you had the State in column A and the County in Column B and the Data you showed was on a sheet called DATA then this would do it.

此公式将进入C2

=INDEX(DATA!F:F,MATCH(SMALL(IF(DATA!A:A = A2,IF(DATA!B:B = B2,DATA!Z:Z)),2),IF(DATA!A:A = A2,IF(DATA!B:B = B2,DATA!Z:Z)),0))

它是一个数组公式，需要用Ctrl确认-Shift输入。然后复制您的列表。

It is an array formula and would need to be confirmed with Ctrl-Shift-Enter. Then copy down your list.

这将根据价格返回第二低供应商。

This would return the second lowest provider based on price.

这篇关于将公式复制并粘贴到具有下拉选项卡的行的单元格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！