使用Excel仅将唯一列转置为标题行 [英] USE Excel to transpose ONLY unique column into a header row

问题描述

我正在尝试在SHEET1中转换一个电子表格，如下所示:

I am trying to transform a spreadsheet in SHEET1 that looks like this:

COLUMN1
A 
A 
A 
B 
B 
B 
B 
C 

我希望它像这样在SHEET2中出现

I want it to be in SHEET2 like this

ROW Header: A B C

我基本上是在尝试:

在第一列中获取唯一项转置并提升这些项目作为列标题

Get unique items in first column Transpose and promote these items as column headers

我需要SHEET 2中的输出表来每次动态更新.没有VBA.例如，如果我将D添加到SHEET 1，D将在SHEET 2标头上自动更新....A B C D

I need the output table in SHEET 2 to dynamically update each time. No VBA. for example if I add D to SHEET 1, D will automatically update on SHEET 2 Header.... A B C D

COLUMN1
A 
A 
A 
B 
B 
B 
B 
C 
D

我希望它像这样在SHEET2中自动更新

I want it to automatically update in SHEET2 like this

ROW Header: A B C D

推荐答案

像这样的公式对您有用吗?它采用工作表1中该列的所有值并转置唯一值.将此公式放到工作表2中会随着工作表1中值的更改而自动更新标题.

Would a formula like this work for you? It takes all the values of the column in sheet 1 and transposes the unique values. Putting this formula in sheet 2 will automatically update a header as the values in sheet 1 change.

=TRANSPOSE(UNIQUE(FILTER(Sheet1!A2:A500,Sheet1!A2:A500<>"")))

此公式仅对使用Excel O365及更高版本的用户有效，因为较新的版本允许使用更多动态公式.

This formula will only work for those with Excel O365 and later since the newer versions allow for more dynamic formulas.

这篇关于使用Excel仅将唯一列转置为标题行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！