☰

Google 表格到 Excel

tag-icon • tag-icon microsoft-excel google-spreadsheets
Google 表格到 Excel

我有这个数组公式。

=ArrayFormula(IF(P3:P="",,IFERROR(VLOOKUP(P3:P,sort({{C:C,L:L,A:A};{D:D,M:M,A:A}},3,0),2,0),UPLUS(V3:V))))

它在 Google Sheets 中运行良好。但是当我将 Google Sheets 转换为 Excel 时，它说排序公式无效。我注意到数组在 Excel 中的工作方式略有不同。那么我如何才能将此公式从 Google Sheets 转换为 Excel 而不丢失功能呢？

答案1

错误出在sort函数中。在开始之前，让我们先看一个sort您使用过的 Google Sheets 函数的示例：

让我们看看我们有一个从 A1 到 C3 的网格，如下所示：

          -------------------------------
          |    A    |    B    |    C    |
-----------------------------------------
|    1    |    3    |    11   |    61   |
-----------------------------------------
|    2    |    7    |    13   |    21   |
-----------------------------------------
|    3    |    5    |    12   |   141   |
-----------------------------------------

然后，如果您在 E7 中发出此公式：

=SORT(A1:C3, 2, 0)

这将创建一个排序的网格，A1:C3其最顶部和最左侧的单元格位于 E7，并根据第二列（列 B 或列2）排序。第三个参数要求ascending (1) or descending (0) order列 2 或列 B。结果是：

          -------------------------------
          |    E    |    F    |    G    |
-----------------------------------------
|    7    |    7    |    13   |    21   |
-----------------------------------------
|    8    |    5    |    12   |   141   |
-----------------------------------------
|    9    |    3    |    11   |    61   |
-----------------------------------------

您的值范围是分段的（不是连续的），并且{{C:C,L:L,A:A};{D:D,M:M,A:A}}粘贴时的输入看起来像这样，假设 Q14：

          -------------------------------------------------
          |       Q       |       R       |       S       |
-----------------------------------------------------------
|    14   |entire C column|entire L column|entire A column|
-----------------------------------------------------------
|14+Len(C)|entire D column|entire M column|entire A column|
-----------------------------------------------------------

其中 Len(C) 是 C 列的长度。请注意，所有列必须具有相同的长度（行数）才能使用 SORT 函数。现在，您在此处发出以下公式：

sort({{C:C,L:L,A:A};{D:D,M:M,A:A}},3,0)

您肯定已经猜到了，第 3 列现在是 S 列，它基本上是两次 A 列的合并。0 表示按降序排序。

您需要在 Excel 中模仿此功能，因为 Excel 没有相同的 SORT 功能。

如果你看到这一页，它解释了如何使用 INDEX、MATCH 和 ROWS 函数对数组进行排序，以在 MS Excel 中执行相同的操作，但是，Excel 数组的工作方式与 Google Sheet 数组不同（后者使用该sort函数要简单得多）。只需查看链接上巨大的 Excel 排序公式即可。不幸的是，Excel 中没有更简单的方法。从技术上讲，当使用 Excel 中的公式进行排序时，您正在自己编写排序算法。

我敢说，在这种情况下，您必须将排序与主公式隔离开来。例如，首先对动态数据进行排序（使用公式排序），然后对其应用 VLOOKUP 和其他操作。

相关内容