使用公式显示excel表/矩阵作为列表 [英] Display an excel table/matrix as a list using formulas
问题描述
这是我以前的问题的后续这里，因为我认为它的优点自己的探索。
This is a follow-on to my previous question here, as i think it merits its own exploration.
我的源数据按照列标题（B1：E1）和事件作为行标题（A2：A50）进行组织。剩余的单元格（B2：E50）填充有表示每个位置的下一个事件将被保持的日期。并不是所有的位置都为每个事件提供服务，所以N / A是可能的。事件可能会在同一天在相同的位置举行。
My source data is organized with locations as column headings (B1:E1), and events as row headings (A2:A50). The remaining cells (B2:E50) are populated with dates representing when the next event at each location will be held. Not all locations serve each event, so "N/A"s are possible. events may be held at the same location on the same day.
在上一个答案中，我学习了如何按照日期排序在每个位置显示事件作为单独的列表。对于此练习，我希望将所有事件合并成一个由三列（日期，事件，位置）组成的列表，按照该顺序排序，并使用公式进行操作。与上一个练习一样，数据透视表不成问题。
In the previous answer, I learned how to display events as separate lists under each location, sorted by date. for this exercise, I wish to consolidate all events into a single list consisting of three columns (date, event, location), sorted in that order, and do so using formula. As in the previous exercise, pivot tables are out of the question.
源数据
A B C D E
1 San Francisco Tampa Philadelphia Houston
...
6 Introduction 7-Feb-15 24-Jan-15 17-Jan-15 10-Jan-15
7 Beginner I 9-Feb-15 26-Jan-15 19-Jan-15 12-Jan-15
8 Beginner II 23-Feb-15 9-Feb-15 2-Feb-15 26-Jan-15
9 Beginner III 9-Mar-15 23-Feb-15 16-Feb-15 9-Feb-15
10 Intermediate I 16-Mar-15 19-Jan-15 N/A 19-Jan-15
11 Intermediate II 12-Jan-15 2-Feb-15 N/A 2-Feb-15
12 Intermediate III 26-Jan-15 16-Feb-15 N/A 16-Feb-15
13 Advanced I 9-Feb-15 N/A N/A 2-Mar-15
14 Advanced II 23-Feb-15 N/A N/A 16-Mar-15
15 Advanced III 9-Mar-15 N/A N/A 30-Mar-15
所需输出：
January
10-Jan-15 Introduction Houston
12-Jan-15 Beginner I Houston
12-Jan-15 Intermediate II San Francisco
17-Jan-15 Introduction Philadelphia
19-Jan-15 Beginner I Philadelphia
19-Jan-15 Intermediate I Houston
19-Jan-15 Intermediate I Tampa
24-Jan-15 Introduction Tampa
26-Jan-15 Beginner I Tampa
26-Jan-15 Beginner II Houston
26-Jan-15 Intermediate III San Francisco
推荐答案
您可以以与前一样的方式获取日期列表，因为公式不在乎源是单列还是矩阵，因此在单元格A1中的第一个月在你的总结作品中你可以从A3的这个数组公式开始。
You can get the date list in almost the same way as before because the formula doesn't care if the source is a single column or a matrix, so with 1st of the month in cell A1 in your "summary" worksheet you can start with this array formula in A3
= IFERROR（SMALL（如果（Master！B $ 2：E $ 50> = A $ 1 ，IF（主设备B $ 2：E $ 50℃， EOMONTH（A $ 1,0）+ 1，Master！B $ 2：E $ 50）），ROWS（A $ 3：A3）），）
=IFERROR(SMALL(IF(Master!B$2:E$50>=A$1,IF(Master!B$2:E$50< EOMONTH(A$1,0)+1,Master!B$2:E$50)),ROWS(A$3:A3)),"")
然后在得到城市之前可能更容易，所以在C3
Then it's probably easier next to get the cities, so in C3
= IF（A3 =， ，INDEX（主设备B $ 1：！E $ 1，小型（IF（主B $ 2：E $ 50 = A3，COLUMN（主B $ 2：E $ 50）-column（主B $ 2）+1），COUNTIF（A $ 3：A3，A3）））））
=IF(A3="","",INDEX(Master!B$1:E$1,SMALL(IF(Master!B$2:E$50=A3,COLUMN(Master!B$2:E$50)-COLUMN(Master!B$2)+1),COUNTIF(A$3:A3,A3))))
然后在B3文本中
= IF（A3 =，，INDEX（Master！A $ 2：A $ 50，SMALL（IF（INDEX（Master！B $ 2：E $ 50,0，MATCH ！，主设备B $ 1：E $ 1,0））= A3，ROW（主A $ 2：甲$ 50）-ROW（主A $ 2）+1），COUNTIFS（A $ 3：！A3，A3，C $ 3：C3 ，C3）））））
=IF(A3="","",INDEX(Master!A$2:A$50,SMALL(IF(INDEX(Master!B$2:E$50,0,MATCH(C3,Master!B$1:E$1,0))=A3,ROW(Master!A$2:A$50)-ROW(Master!A$2)+1),COUNTIFS(A$3:A3,A3,C$3:C3,C3))))
所有公式需要用 CTRL + SHIFT + ENTER ，并根据需要将列复制到
All formulas need to be confirmed with CTRL+SHIFT+ENTER and copied down the columns as far as you need
这篇关于使用公式显示excel表/矩阵作为列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
