Google电子表格中的逻辑“除"/“差"范围 [英] Logical except/difference ranges in google spreadsheets

问题描述

我想在Google电子表格中获取逻辑(A - B)或(A \ B).

I want to get logical (A - B) or (A \ B) in google spreadsheets.

因此，拥有:

A:A

{1,2,3,4}

和

B:B

{2,3,5,6}

所以我的公式

=my_amazing_formula(A:A, B:B)

应该返回

{1,4}

(B中不存在A的元素)

(Elements of A not being present in B)

问题

我该如何实现?

推荐答案

因为其中没有像 HashSet 尚未解决，这是一个很难解决的棘手问题.您可以选择二次解，对于第一个范围中的每个项目，它都会遍历整个第二个项目，以尝试找到匹配项"，以便将其丢弃.看起来有点像@Cooper的解决方案.

Since in there is no such data structure as a HashSet in Google Apps Script yet, this is a somewhat tricky problem to solve efficiently. You can opt for the quadratic solution, which for each item in the first range would iterate over the whole second one trying to find a 'match', in order to discard it. This would look somewhat like @Cooper 's solution.

作为替代方案，考虑到Google Apps脚本的对象保留属性插入顺序，您可以使用以下代码，这些代码在理论上应能产生更好的性能结果(特别是对于较大的工作负载).

As an alternative, and considering that Google Apps Script's objects preserve property insertion order, you can use the following code which should theoretically yield better performance results (specially for larger workloads).

function DIFFERENCE(range1, range2) {
  var o = {};
  for (var i=0; i<range1.length; i++) {
    for (var j=0; j<range1[0].length; j++) {
      if (!o[range1[i][j]])
        o[range1[i][j]] = true;
    }
  }

  for (var i=0; i<range2.length; i++) {
    for (var j=0; j<range2[0].length; j++) {
      if (o[range2[i][j]])
        o[range2[i][j]] = false;
    }
  }

  return Object.keys(o).filter(function f(key) { return o[key]; }).map(function(res) { return parseFloat(res) });
}

此功能假定您正在处理数字.如果您还希望它也可以与字符串一起使用，则可以用以下代码替换最后一行代码:return Object.keys(o).filter(function f(key) { return o[key]; });

This function assumes that you are dealing with numbers. If you want it to work with strings as well, you can replace the last line of code by the following: return Object.keys(o).filter(function f(key) { return o[key]; });

您还可以在此处看到几个示例:

You can also see a couple of examples here:

这篇关于Google电子表格中的逻辑“除"/“差"范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！