☰

原始表

tag-icon • tag-icon microsoft-excel worksheet-function vba libreoffice libreoffice-calc
原始表

原始表

我在 Excel 或 LibreOffice 中有这张表格。

单元编号 类型 姓名
1 目的 顶部
1 目的 底部
1 目的 左边
1 目的 正确的
1 目的 后退
1 目的 正面
1 财产 右固定
1 财产 左固定
1 财产 四条腿

新表

我想将此表转换为新表。我只想保留包含类型等于目的并应用特性作为新列。如下所示。

单元编号 类型 姓名 右固定 左固定 四条腿
1 目的 顶部
1 目的 底部 真的
1 目的 左边 真的
1 目的 正确的 真的
1 目的 后退
1 目的 正面

问题

我如何在 Excel 或 LibreOffice 中执行此操作？我的选择是：

  • 基于公式的方法。
    • 它会不会变得太复杂？
  • VBA 宏编程。
    • 对于这个问题是不是有点小题大做？

我将不胜感激任何提示或帮助。

笔记

上面，我仅展示了单元编号作为1样本。但单位数字可以继续，如2，3等等。

答案1

有很多属性吗？

我建议您尝试以下步骤：

  • 使用类似公式=FILTER(A2:C10,B2:B10<>"Property")列出所有类型值。

在此处输入图片描述

  • 使用类似=IFERROR(IF(FIND("Right",C2)=1,"True",""),"")公式正确的财产，
  • 用户公式=IFERROR(IF(FIND("Left",C2)=1,"True",""),"")类似于左边财产，
  • 使用类似=IFERROR(IF(FIND("Bottom",C2)=1,"True",""),"")公式四条腿。

在此处输入图片描述

答案2

假设没有Excel Constraints，则以下公式应按照发布的标签工作。以下公式是单输入动态数组公式。

在此处输入图片描述

=LET(
     _data, A2:C10,
     _object, FILTER(_data,INDEX(_data,,2)="Object"),
     _property, UNIQUE(TOROW(TAKE(FILTER(_data,INDEX(_data,,2)="Property"),,-1)),1),
     _rows, ROWS(_object),
     _cols, COLUMNS(_property),
     _name, SWITCH(LEFT(_property,2),"Ri","Ri","Le","Le","4-","Bo"),
     _convert, HSTACK(_object,MAKEARRAY(_rows,_cols,LAMBDA(r,c,N(INDEX(LEFT(TAKE(_object,,-1),2),r)=INDEX(_name,c))))),
     _headers, HSTACK(A1:C1,_property),
     VSTACK(_headers,_convert))
  • _data--> 用于数据范围的变量，不包括标题，
  • _object--> 使用FILTER()返回仅包含的范围object
  • _property--> 使用FILTER()返回仅由组成的范围property，然后取该范围的最后一列，并将唯一值从行转换为列。
  • _rows--> 使用ROWS()来获取对象行数。
  • _cols--> 用于COLUMNS()获取属性列的数量。
  • _name--> 使用SWITHC()函数修改名称以便与属性相匹配。
  • _convert--> 这使用 的组合HSTACK()作为一部分和MAKEARRAY()的另一部分。因此，将根据应用的自定义计算MAKEARRAY()返回一个包含 n行和列的数组，因此它所做的只是尝试执行并返回和，其自定义格式为。在使用 时，我们将 的返回值和变量结合起来。nLAMBDA()BOOLEAN LOGIC1TRUE0FALSE[=1]TRUE;;HSTACK()MAKEARRAY()_object
  • _headers--> 顾名思义，返回数组的各自标题。
  • 最后，使用VSTACK()附加两个数组，这里_headers使用_convert。

_name如果您确定属性标签，可以通过删除变量来进行一个小的更改，

• 取而代之的是

N(INDEX(LEFT(TAKE(_object,,-1),2),r)=INDEX(_name,c))

• 用这个代替那个。记得_name先删除变量。

N(INDEX(TAKE(_object,,-1),r)=INDEX(TEXTBEFORE(SUBSTITUTE(_property,"4","Bottom"),"-"),c))

编辑：

更新的公式根据所需输出返回：

在此处输入图片描述

=LET(
     _Data, A1:C19,
     _Mapping, {
         "Right-Fixed", "Right";
         "Left-Fixed", "Left";
         "4-Legs", "Bottom";
         "Front-Fixed", "Front";
         "Back-Fixed", "Back";
         "Folder", "Top"
      },
     _Headers, HSTACK(
         TAKE(_Data, 1),
         TOROW(
             TAKE(_Mapping, , 1)
         )
      ),
     _Object, FILTER(
         _Data,
         INDEX(_Data, , 2) =
             "Object"
     ),
     _Rows, ROWS(_Object),
     _Columns, COLUMNS(
         DROP(_Headers, , 3)
     ),
     _Matched, MAKEARRAY(_Rows,_Columns,LAMBDA(r,c, LET(
     _Property, FILTER(INDEX(_Data,,3),(INDEX(_Object,r,1)=INDEX(_Data,,1))*(INDEX(_Data,,2)="Property")),
     _MappedProps, VLOOKUP(_Property,_Mapping,2,0),
     _MappedObj, VLOOKUP(INDEX(DROP(_Headers,,3),c),_Mapping,2,0),
      N(OR((_MappedProps=_MappedObj)*(_MappedProps=INDEX(_Object,r,3))))))),
     _Merged, HSTACK(
         _Object,
         _Matched
      ),
     VSTACK(_Headers, _Merged)
     )

答案3

这也可以使用 Windows Excel 2010+ 和 Microsoft 365（Windows 或 Mac）中提供的 Power Query 来实现

使用 Power Query

  • 在数据表中选择一些单元格
  • Data => Get&Transform => from Table/Range
  • 当 PQ 编辑器打开时：Home => Advanced Editor
  • 记下表格姓名在第 2 行
  • 将下面的 M 代码粘贴到您所看到的位置
  • 将第 2 行的表名改回最初生成的表名。
  • 阅读评论并探索Applied Steps以了解算法
let
    Source = Excel.CurrentWorkbook(){[Name="Table45"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unit number", Int64.Type}, {"Type", type text}, {"Name", type text}}),

//Create list of all properties to use in the final expansion
    #"All Properties" = List.Distinct(Table.SelectRows(#"Changed Type", each [Type]="Property")[Name]),

// for multiple units Group on unit
    #"Group Unit" = Table.Group(#"Changed Type",{"Unit number"}, {
        {"Props", (t)=>
            let 

            //List of Objects and Properties for this unit only
                Objects = Table.SelectRows(t, each [Type]="Object"),
                Properties = List.Distinct(Table.SelectRows(t, each [Type]="Property")[Name]),

            //Add Property columns to the Objects in the correct location
            //May need to change the logic of the `AddColumn` function depending on your
            //   actual data
                #"Add Property Columns" = List.Accumulate(
                    Properties,
                    Objects,
                    (s,c)=> Table.AddColumn(s,c, 
                        each if Text.StartsWith(c,[Name]) or (c = "4-legs" and [Name]="Bottom") then true else null, type logical))
            in
                #"Add Property Columns",
                type table[Unit number=Int64.Type, Type=text, Name=text,#"Right-Fixed"=logical, #"Left-Fixed", #"4-legs"=logical]}}),

//Remove unneeded column and expand the subtable
    #"Removed Columns" = Table.RemoveColumns(#"Group Unit",{"Unit number"}),
    #"Expanded Props" = Table.ExpandTableColumn(#"Removed Columns", "Props", 
        Table.ColumnNames(#"Changed Type") & #"All Properties")
in 
    #"Expanded Props"

原始数据
显示两个单位
在此处输入图片描述

结果
在此处输入图片描述

相关内容