我經歷了一些反復試驗，為我認為我需要的錯誤公式作業，現在我已經將其縮小到我的用例所需的確切公式/概念。
我需要一個公式來搜索標題行（第 1 行的第 1 行）以查找特定字串的出現，例如 HouseNumber，當它找到一個出現時，它會從其下方的單元格中獲取資料（來自第 1 表的第 2 行） ) 并將其復制到不同的作業表（從作業表 2 的第 2 行開始，將第 1 行作為標題行），然后繼續查找第二次出現的位置，一旦找到，它就會從其下方的單元格中獲取資料（從作業表 1 的第 2 行），并將其復制到不同作業表上的下一行（作業表 2 的第 3 行），然后繼續直到找不到更多的出現，等等......
因此，例如，在下面的第 1 行中，搜索第 1 行以查找所有出現的 HouseNumber，并將其下方單元格中的值從第 2 行復制到第 2 行中的單獨行（因此公式應在第 2 行中的 A2 處） .
第 1 頁：
| HouseNumber.1 | HouseStreet.1 | HouseNumber.2 | HouseStreet.2 |
|---|---|---|---|
| 123 | 第一大道 | 456 | 第二大道 |
第 2 頁：
| 漢化 |
|---|
| 123 |
| 456 |
然后執行與上述相同的操作，除了搜索 HouseStreet 的出現次數（因此應在表 2 的 B2 中使用單獨的公式）。
第 2 頁：
| 漢化 | HS |
|---|---|
| 123 | 第一大道 |
| 456 | 第二大道 |
我一直在嘗試使用 INDEX 和 MATCH 的嵌套函式，但它并沒有完全作業，而且它的結構也只是搜索/回傳一個事件/值，而不是所有的事件/值。
uj5u.com熱心網友回復：
如果您有 Windows Excel 2021 （具有使用的函式），您可以使用以下公式：
對于地址表 1，我使用了具有結構化尋址的表，但如果您愿意，也可以使用常規尋址
HN: =FILTERXML("<t><s>" & TEXTJOIN("</s><s>",TRUE,FILTER(adrTbl,ISNUMBER(FIND("Number",adrTbl[#Headers])))) & "</s></t>","//s")
HS: =FILTERXML("<t><s>" & TEXTJOIN("</s><s>",TRUE,FILTER(adrTbl,ISNUMBER(FIND("Street",adrTbl[#Headers])))) & "</s></t>","//s")
結果應該根據需要溢位
adrTbl
結果
請注意，您可以展開表格行和/或列，公式應該仍然有效。
如果您有早期版本的 Excel，我建議您使用 Power Query
沒有聚合的自定義函式
樞軸
//credit: Cam Wallace https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/
//rename this query fnPivotAll
(Source as table,
ColToPivot as text,
ColForValues as text)=>
let
PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
#"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
TableFromRecordOfLists = (rec as record, fieldnames as list) =>
let
PartialRecord = Record.SelectFields(rec,fieldnames),
RecordToList = Record.ToList(PartialRecord),
Table = Table.FromColumns(RecordToList,fieldnames)
in
Table,
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
#"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
#"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
#"Expanded Values"
主要查詢
let
Source = Excel.CurrentWorkbook(){[Name="adrTbl"]}[Content],
//Unpivot all the columns to => data in just two columns
unPivot = Table.UnpivotOtherColumns(Source,{},"Attribute","Value"),
//split off the number from the column header to normalize them
//If you have other columns => unwanted attributes, may need to filter after the Split
#"Split Column by Delimiter" = Table.SplitColumn(unPivot, "Attribute", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Attribute.2"}),
//Pivot on the Attribute with no aggregation
pivot=fnPivotAll(#"Removed Columns","Attribute.1","Value")
in
pivot
uj5u.com熱心網友回復：
用公式解決：
=arrayformula(query(transpose(to_text(Sheet1!1:2)),"Select Col2 where Col1 contains 'HouseNumber'"))
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/397961.html
標籤：谷歌表格