js建立excel表格_建立Excel足球联赛表格-传统vs动态数组方法

介绍 (Introduction)

I am going to show you the different ways you can build a football league table in Excel. Some of the methods are old school but others utilise Excel’s new capabilities.

我将向您展示在Excel中建立足球联赛表格的不同方法。 其中一些方法过时，而另一些则利用Excel的新功能。

In case you weren’t already aware, Excel has undergone a big change to its calculation engine fairly recently. The concept of dynamic arrays was first introduced back in September 2018, however, for many Microsoft 365 users the first batch of new functions took an awfully long time to appear. Unless you have been an Office Insider, you will not have been able to use them. Even though the update was rolled out to my copy towards the start of the year, there were still swathes of users who were kept waiting.

如果您还没有意识到，Excel会在最近对其计算引擎进行重大更改。 动态数组的概念最早是在2018年9月引入的，但是，对于许多Microsoft 365用户而言，第一批新功能花了很长时间才出现。 除非您是Office Insider，否则您将无法使用它们。 即使更新已于今年年初发布到我的副本中，但仍有大量用户一直在等待。

Since dynamic arrays were introduced in Excel, array formulas no long require you to press Ctrl + Shift + Return every time you edit a cell. This was an annoying practice that made many users, including myself, reluctant to use arrays. They just didn’t feel like a native and integrated part of Excel. Now you can use an array formula like any other — without that additional step.

由于动态数组是在Excel中引入的，因此数组公式不再需要您每次编辑单元格时都按Ctrl + Shift + Return。 这种令人讨厌的做法使许多用户(包括我自己)都不愿意使用数组。 他们只是感觉不像Excel的本地集成部分。 现在，您可以像其他数组一样使用数组公式了-无需执行其他步骤。

If you want to find out more about the new functions, I recommend you visit Microsoft’s help page for each one: XLOOKUP, FILTER, UNIQUE, SORTBY, SORT, SEQUENCE, RANDARRAY.

如果您想了解有关新功能的更多信息，建议您访问Microsoft的每个帮助页面： XLOOKUP ， FILTER ， UNIQUE ， SORTBY ， SORT ， SEQUENCE ， RANDARRAY 。

开始之前 (Before we start)

Download the workbook from here: https://bit.ly/39mlqkp.

从此处下载工作簿： https : //bit.ly/39mlqkp 。

Quick caveat: if you have an older version of Excel, you will find some of the examples do not work because of compatibility issues. This is unavoidable unless you purchase a Microsoft 365 subscription. Personally, I would recommend you do so.

快速警告：如果您使用的是旧版Excel，则会发现某些示例由于兼容性问题而无法使用。 除非您购买Microsoft 365订阅，否则这是不可避免的。 就个人而言，我建议您这样做。

采取的步骤 (Steps taken)

Firstly, a dataset is required containing a list of all the matches played and their respective results. I have used English Premier League data from the 2019/20 season for this example. To conserve space elsewhere, the matches are stored in a separate worksheet called Data — with the table itself named DataTable.

首先，需要一个数据集，其中包含所有进行的比赛及其各自结果的列表。 在此示例中，我使用了2019/20赛季的英超联赛数据。 为了节省其他地方的空间，匹配项存储在名为Data的单独工作表中，表本身名为DataTable 。

You’ll notice there’s a calculated column on the end called Result. This formula looks at the home_goal and away_goal fields for each match played and determines whether the outcome was a home win (H), draw (D) or away win (A).

您会注意到最后有一个计算列Result 。 该公式查看每场比赛的home_goal和away_goal字段，并确定结果是主场获胜( H )，平局( D )还是客场获胜( A )。

There are three sections: Part A, Part B and Part C. Each contains multiple league tables that output identical values, but the method used differs.

共有三个部分： A 部分，B 部分和C部分 。 每个表都包含多个输出相同值的联赛表，但是使用的方法不同。

Any kind of system that involves ranking data is typically going to require an unordered and ordered table. The former houses the mathematical calculations and determines the ranking of each row, whilst the latter references it to output the data in the correct order. Part A and Part B are based off this principle. Part C, however, contains two variants that are not dependent on an additional table.

任何涉及对数据进行排名的系统通常都需要无序和有序的表。 前者存储数学计算并确定每行的排名，而后者引用它以按正确的顺序输出数据。 A部分和B部分基于此原理。 但是， C部分包含两个不依赖于其他表的变体。

The tables in the workbook use these headers:

工作簿中的表使用以下标头：

• POS (position)

  POS ( 位置 )

• TEAM

  球队

• P (matches played)

  P (参加比赛)

• W (matches won)

  W (赢得比赛)

• D (matches drawn)

  D (匹配结果)

• L (matches lost)

  L (输掉比赛)

• F (goals for)

  F (目标)

• A (goals against)

  A (反对)

• GD (goal difference)

  GD (目标差)

• PTS (points)

  PTS (点)

• RANK* (table position when points are sorted in descending order)

  RANK *( 点按降序排序时的表位置 )

*Table A2 only

*仅限表A2

甲部 (Part A)

The approaches here are all based on official Excel tables. The way to tell if what looks like a table is indeed a table— is to check if it has a small blue triangle in the bottom-right, or to click on it and the Table Design tab will appear in the ribbon.

这里的方法都是基于官方的Excel表。 判断表看起来是否确实是表的方法是 ：检查表的右下角是否有一个小的蓝色三角形，或者单击它，然后“表设计”选项卡将出现在功能区中。

We start off by creating Table A1, which is unordered and forms the base for Table A2, Table A3 and Table A4 to work off. The P, W, D, L columns use COUNTIFS formulas to count the number of matches a team has played, won, drawn and lost respectively. It’s important to note that a single COUNTIFS formula only allows for AND conditions. That means all criteria must be met for a successful count. As we have home and away matches to consider, we need to use two COUNTIFS statements in the same cell to add the counts together. The same concept applies to the SUMIFS function, which has been used for the columns that involve addition: F and A.

我们首先创建表A1，它是无序的，并构成了表A2，表A3和表A4的基础 。 P ， W ， D ， L列使用COUNTIFS公式来计算球队分别打过，赢过，输过和输过的比赛次数。 重要的是要注意，单个COUNTIFS公式仅允许AND条件。 这意味着必须满足所有条件才能成功计数。 考虑到本场比赛和客场比赛，我们需要在同一单元格中使用两个COUNTIFS语句将计数加在一起。 相同的概念适用于SUMIFS函数，该函数已用于涉及加法的列： F和A。

The goal difference (GD) column is as simple as F minus A. Custom formatting has also been applied so positive numbers are preceded by a ‘+’ symbol and negatives with a ‘-’.

目标差( GD )列很简单，即F减去A。 自定义格式也已应用，因此正数前带有“ +”符号，负数前带有“-”。

The PTS column uses a rather convoluted formula to produce a very long number, but is necessary so the ordered table sorts correctly. In the case of tiebreakers, where two or more teams are level on points, we need to ensure a unique ranking for each team. The priority order that determines rank is as follows:

PTS列使用一个相当复杂的公式来产生一个很长的数字，但是这是必需的，因此有序表可以正确排序。 对于平局决胜局，其中两个或更多团队在得分上持平，我们需要确保每个团队的排名都是唯一的。 确定等级的优先级顺序如下：

1. Points (PTS)

1.点数( PTS )

2. Goal difference (GD)

2.目标差( GD )

3. Goals scored (F)

3.进球( F )

4. First letter of Team name (TEAM)

4.团队名称的首字母( TEAM )

5. Position number (POS)

5.职位编号( POS )

To prove the points column works, we set the values of the P/W/D/L/F/A/GD/PTS columns to pure zeros. With all things being level now, the numbers in RANK directly mirror the alphabetical order of the teams. If you look at Arsenal and Aston Villa, for example, both of them begin with the letter ‘A’, so the only thing separating their points are the position numbers.

为了证明points列有效，我们将P / W / D / L / F / A / GD / PTS列的值设置为纯零。 现在一切都平了， RANK中的数字直接反映了球队的字母顺序。 例如，如果您查看阿森纳(Arsenal)和阿斯顿维拉(Aston Villa)，它们都以字母'A'开头，因此，将它们分开的唯一点就是位置编号。

Table A2 uses VLOOKUP to extract the relevant data from Table A1 and display it in the correctly sorted order. VLOOKUP is a popular function, but is less flexible than INDEX/MATCH and the new XLOOKUP. For this reason, many people will stop using it once they’ve discovered one of the latter two.

表A2使用VLOOKUP从表A1中提取相关数据并以正确排序的顺序显示它们。 VLOOKUP是一种流行的功能，但不如INDEX / MATCH和新的XLOOKUP灵活。 因此，一旦发现后两者之一，许多人就会停止使用它。

One of the biggest downsides of VLOOKUP is having to reorder your data so that the lookup range is the furthest-left column. In this case, the RANK column has had to be moved in Table A1 so it comes before all the columns that we want to return data from.

VLOOKUP的最大缺点之一是必须对数据重新排序，以使查找范围在最左列。 在这种情况下，必须在表A1中移动RANK列，因此它位于我们要从中返回数据的所有列之前。

This is not the case with INDEX/MATCH and XLOOKUP. It doesn’t matter about the position of the RANK column. With both Table A3 and Table A4, we have simply referenced only the ranges we need from Table A1.

INDEX / MATCH和XLOOKUP并非如此。 RANK列的位置无关紧要。 对于表A3和表A4 ，我们仅引用了表A1中需要的范围。

B部分 (Part B)

Unlike Part A, all three of the ‘tables’ you see are not actually tables—in Excel terms anyway—although we’ll still use that term. Table B1 uses only 11 formulas—one for the headings and the other 10 for the calculations. To surprise you even more: Table B2 and Table B3 use just three. This is what dynamic arrays are all about. They grow and shrink depending on the output of the formula. This concept goes completely against traditional Excel, where one cell has one formula. The big advantage of this is you are less likely to make errors in your worksheet and will reduce inconsistencies.

与A部分不同，您看到的所有三个“表”实际上都不是表(无论如何以Excel术语而言)，尽管我们仍将使用该术语。 表B1仅使用11个公式-一个用于标题，另一个10个用于计算。 更让您惊讶的是： 表B2和表B3仅使用三个。 这就是动态数组的全部意义。 它们根据公式的输出而增长和收缩。 此概念完全与传统的Excel相抵触，在Excel中，一个单元格只有一个公式。 这样做的最大好处是，您不太可能在工作表中出错，并且可以减少不一致的情况。

For the column headings of each table, we have used an array constant to hold the column names. These are fixed values and do not change. Unfortunately, only numbers and text strings can be used in constants — not formulas.

对于每个表的列标题，我们使用了一个数组常量来保存列名。 这些是固定值，不会改变。 不幸的是，常量中只能使用数字和文本字符串，而不能用于公式。

={"POS","TEAM","P","W","D","L","F","A","GD","PTS"}

You will notice in the formulas that a cell reference followed by a hashtag is a common occurrence. This is a spilled range, meaning it is dynamic, so its size will adapt according to the data.

您会在公式中注意到，单元格引用后跟井号是常见的情况。 这是一个溢出范围，意味着它是动态的，因此其大小将根据数据进行调整。

We have again used COUNTIFS and SUMIFS for the P/W/D/L/F/A columns in the same manner as we did in Table A1, except spilled ranges have replaced regular ones.

我们再次以与表A1中相同的方式对P / W / D / L / F / A列使用COUNTIFS和SUMIFS，只是溢出范围已替换常规范围。

Some of the new Excel functions have been utilised and allow us to do a lot from a single cell.

一些新的Excel函数已被利用，使我们可以在单个单元格中完成很多工作。

For the POS column, the SEQUENCE function returns a set of numbers. By wrapping it around the COUNTA function, it will always return an incremental number in accordance to how many teams are displayed adjacent to it.

对于POS列，SEQUENCE函数返回一组数字。 通过将它包装在COUNTA函数周围，它将始终根据与其相邻显示的团队数量返回一个递增的数字。

POS: =SEQUENCE(COUNTA(D14#))

POS： =SEQUENCE(COUNTA(D14#))

The individual team names have been extracted from the team_home column in DataTable using UNIQUE, and then sorted in ascending order using SORT.

已使用UNIQUE从DataTable的team_home列中提取了各个团队名称，然后使用SORT以升序对其进行了排序。

TEAM: =SORT(UNIQUE(DataTable[team_home]))

团队： =SORT(UNIQUE(DataTable[team_home]))

Notice how the PTS column is full of integers and there is no RANK column. This is because we no longer need that awfully complex calculation you saw in Table A1. What replaces it then? The trusty SORT function again.

请注意， PTS列是如何充满整数，而没有RANK列。 这是因为我们不再需要您在表A1中看到的非常复杂的计算。 那有什么替代呢？ 可信赖的SORT功能再次发挥作用。

You’ll see in Table B2 and Table B3 that the formula for the table calculations starts at the very top of TEAM. That’s one formula controlling 180 values!

您将在表B2和表B3中看到表计算公式从TEAM的最顶部开始。 这是一个控制180个值的公式！

CHOOSE and SWITCH are great for this purpose. Multiple column values can be stored in one formula and spill vertically and horizontally.

选择和切换非常适合此目的。 多个列值可以存储在一个公式中，并且可以垂直和水平溢出。

Let’s look at the main formula in Table B2:

让我们看一下表B2中的主要公式：

=SORT(CHOOSE({1,2,3,4,5,6,7,8,9},
D14#,
E14#,
F14#,
G14#,
H14#,
I14#,
J14#,
K14#,
L14#
),{9,8,6,1},-1,-1,-1,1)

The numbers at the top between the parentheses are placed in the index_num argument. They are the index numbers that refer to each spilled range below it.

括号之间顶部的数字放在index_num参数中。 它们是引用其下方每个溢出范围的索引号。

The {9,8,6,1} in the sort_index argument refers to the sorting order and the {-1,-1,-1,1} in sort_order determines whether each sort_index number should be sorted ascending or descending.

sort_index参数中的{9,8,6,1}指的是排序顺序， sort_order的{-1,-1,-1,1}确定每个sort_index编号应升序还是降序。

Just like in Table A1, we have a priority list: PTS, GD, F and TEAM. But it’s so much easier to implement it this new way though.

就像表A1一样 ，我们有一个优先级列表： PTS ， GD ， F和TEAM 。 但是，以这种新方式实现它要容易得多。

The SWITCH method shown in Table C3 is very similar to the CHOOSE one, but the advantage is you are not limited to index numbers, so you can name values what you wish. This is ideal if you have a long formula and you want to optimise readability.

表C3中显示的SWITCH方法与CHOOSE方法非常相似，但是优点是您不仅限于索引号，因此可以根据需要命名值。 如果您有一个较长的公式并且想要优化可读性，那么这是理想的选择。

=SORT(SWITCH({"Team","P","W","D","L","F","A","GD","PTS"},
"Team",
D14#,
"P",
E14#,
"W",
F14#,
"D",
G14#,
"L",
H14#,
"F",
I14#,
"A",
J14#,
"GD",
K14#,
"PTS",
L14#
),{9,8,6,1},{-1,-1,-1,1})

C部分 (Part C)

The first thing you’ll probably notice in this section is that there is no unordered table. The two table variants you see are both self-contained, and only depend on the dataset in the Data worksheet. We have simply squeezed in all the formulas you saw in Table B1 and placed them in a CHOOSE (Table C1) and SWITCH (Table C2) statement.

在本节中您可能会注意到的第一件事是没有无序表。 您看到的两个表变体都是独立的，并且仅取决于数据工作表中的数据集。 我们只是简单地挤压了在表B1中看到的所有公式，并将它们放在CHOOSE( 表C1 )和SWITCH( 表C2 )语句中。

These really are the true definition of a ‘mega formula’! They are rather overwhelming to look at, but using line breaks appropriately (Alt + Return) to spread out the formula can solve most of the readability issues. I also recommend using the SWITCH option as named values can allow you to immediately see where each section starts and ends.

这些确实是“超级公式”的真实定义！ 他们看上去不知所措，但是适当地使用换行符(Alt + Return)来展开公式可以解决大多数可读性问题。 我还建议使用SWITCH选项，因为命名值可以使您立即看到每个部分的开始和结束位置。

One of the limitations of Excel formulas is the inability to reuse parts of a formula. For example, with the goal difference column, rather than having to repeat the formulas for columns F and A, it’d be nice if it was possible to access those values and reuse them for a different part of the formula. Given the LET function is closer upon us, this may well solve that problem for the most part, as we’ll be able to declare variables at the start of a formula and use them multiple times within the same formula.

Excel公式的局限性之一是无法重用公式的某些部分。 例如，使用目标差列，而不必重复列F和A的公式，那么可以访问这些值并将它们重新用于公式的其他部分，那就太好了。 鉴于LET函数离我们越来越近，这可以在很大程度上解决该问题，因为我们将能够在公式的开头声明变量，并在同一公式中多次使用它们。

最后的话 (Final Words)

Please see the workbook for all the examples. Hopefully they will give you food for thought for how you can go about creating formulas that make use of Excel’s dynamic array powers. The centralised, one formula = many cells approach will gradually become the de facto standard, so get ahead of the curve whilst you can.

请参阅工作簿中的所有示例。 希望他们能为您提供思考，帮助您如何使用Excel的动态数组功能创建公式。 集中的，一个公式=许多单元的方法将逐渐成为事实上的标准，因此尽您所能，走在曲线前。

Workbook download: https://bit.ly/39mlqkp.

工作簿下载： https : //bit.ly/39mlqkp 。

学习功能 (Functions to learn)

There are many resources on the web that enable you to learn in-depth about the functions used in the workbook. I’ve found Exceljet to be a particularly helpful source.

Web上有许多资源，使您可以深入了解工作簿中使用的功能。 我发现Exceljet是特别有用的资源。

RANK.EQ

排名

COUNTIFS

COUNTIFS

SUMIFS

SUMIFS

VLOOKUP

VLOOKUP

INDEX

指数

MATCH

比赛

COUNTA

COUNTA

CHOOSE

选择

SWITCH

开关

COLUMN

柱

CODE

码

UNIQUE

独特

SORT

分类

XLOOKUP

XLOOKUP

SEQUENCE

序列