Question

我要说的是,我有一份Excel表格,列出了作者在A和B栏中的书籍。在“Adam”和“Alice”一栏中,如果该书的特征与该名称发生关系,则该书有“x”。

Column A	Column B	Adam	Alice
Title1	Author1		x
Title2	Author2	x
Title3	Author1	x

在另一个表格中,一栏有所有名字(即Adam和Alice)。我现在想找到所有载有具体名称的书籍,并将它们列入一个与漫画相同的单一牢房:

Names	In books
Adam	Title2 by Author2, Title3 by Author1
Alice	Title1 by Author1

I am not quite sure how to approach this problem. Do I have to use VBA or could I use nested functions? I thought I could use MATCH and perhaps TEXTJOIN() inside some kind of IF function or loop, but I don t know how. Help!

Answer 1

在<代码>B7上作此改动,并抄录:

=TEXTJOIN({" by ";", "},,FILTER($A$2:$B$4,INDEX($C$2:$D$4,,MATCH($A7,C$1:D$1,0))="x"))

In A7:

=TRANSPOSE(C$1:D$1)

Answer 2

Filter Excel Table Data

页: 1

=IFNA(TEXTJOIN(", ",,FILTER(
    Table1[Titles]&" by "&Table1[Authors],
    CHOOSECOLS(Table1,XMATCH([@Names],Table1[#Headers]))="x",
    "No titles!")),"Not listed!")

关于Excel 2021,取代CHOOSECOLS (Table1,XMATCH([@Names],Table1[#Headers])=“x”,。

INDEX(Table1,0,XMATCH([@Names],Table1[#Headers]))="x",

For Excel 2019, replace CHOOSECOLS(Table1,XMATCH([@Names],Table1[#Headers]))="x", with

INDEX(Table1,0,MATCH([@Names],Table1[#Headers],0))="x",

Answer 3

有很多工作方式,但就flexibility而言,我将使用。 POWER questRY here which can effortlessly do the auto such Save time and efforts. <代码>Power Query,可在Windows Excel 2010+和Excel 365 (Windows or Mac)上查阅。

<>Method UsingExcelps www.un.org/Depts/DGACM/index_spanish.htm

• 手机G2

=TEXTJOIN({" by ";", "},,TAKE(FILTER(Authorstbl,
  CHOOSECOLS(Authorstbl,XMATCH([@Names],Authorstbl[#Headers]))="x",""),,2))

适用<代码>Excel 20

=TEXTJOIN(", ",1,FILTER(Authorstbl[Titles]&" by "&Authorstbl[Authors],
  INDEX(Authorstbl,,MATCH([@Names],Authorstbl[#Headers],0))="x",""))

www.un.org/Depts/DGACM/index_spanish.htm 采用<条码>的方法

To use Power Query follow the steps:

First convert the source ranges into a table and name it accordingly, for this example I have named it as Authorstbl

Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query

The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done

let
    Source = Excel.CurrentWorkbook(){[Name="Authorstbl"]}[Content],
    UnpivotOtherCols = Table.UnpivotOtherColumns(Source, {"Titles", "Authors"}, "Names", "Value"),
    RemoveCols = Table.RemoveColumns(UnpivotOtherCols,{"Value"}),
    MergeCols = Table.CombineColumns(RemoveCols,{"Titles", "Authors"},Combiner.CombineTextByDelimiter(" by ", QuoteStyle.None),"Merged"),
    GroupRowsAndJoin = Table.Group(MergeCols, {"Names"}, {{"In Books", each Text.Combine([Merged],", "), type text}}),
    Sorted = Table.Sort(GroupRowsAndJoin,{{"Names", Order.Ascending}})
in
    Sorted

Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.

Answer 4

This is an option with a single formula that moves and shakes with the data as it grows...

=LET(
  KeyColumnCount, 2,
  JustNames, TRANSPOSE(DROP(TAKE(DataTable[#All], 1), , KeyColumnCount)),
  NamesWithTitles, MAKEARRAY(ROWS(JustNames), 1, LAMBDA(r,c,
      LET(
        FilteredData, FILTER(DataTable, INDEX(DataTable[#Data], , r+KeyColumnCount) = "x"),
        KeyColumns, TAKE(FilteredData, , 2),
        ConcatenatedKeys, MAKEARRAY(ROWS(KeyColumns), 1, LAMBDA(r,c, TEXTJOIN(" by ", FALSE, CHOOSEROWS(KeyColumns, r)))),
        AllConcatenated, TEXTJOIN(", ", TRUE, ConcatenatedKeys),
      AllConcatenated))),
  FinalData, HSTACK(JustNames, NamesWithTitles),
FinalData)

......你刚刚需要把数据表命名为<代码> 可发射<>/代码>(如果你想要使用与该编码不同的东西,很容易改动)

基督教青年会有一些小小点,可以做得更好,但我已经听了屏幕,因此没有双管齐下。

将需要相当最新的Excel版本。

Filter Excel Table Data

友情链接