合并具有相同单词的单元格 [英] Merge cells with same words

问题描述

我创建了一个包含多个行和列的数组. 在B列中，我有蔬菜的类型，在C列中，有品种的. 在数组中，在B列然后在c列中进行了自动排序.

I created an array containing multiple rows and columns. In column B, I have the type of vegetables, and in column c, the varieties. In the array, there is an automatic sorting which is carried out in column B then column c.

是否可以自动合并包含相同类型的B列单元格?

Is it possible to merge automatically the cells of column B containing the same type?

我只知道如何与合并范围:

I just know how to merge a range with :

var range1 = sheet.getRange("b4:b9");
range1.merge();

此处为测试数组

Cordially.

Cordially.

推荐答案

该示例脚本如何?我认为对于这种情况有几种答案，因此请考虑其中一种.该脚本的流程如下.

How about this sample script? I think that there are several answers for this situation, so please think of this as one of them. The flow of this script is as follows.

1. 检索列B的值.
2. 获取重复值的数量.
3. 合并单元格.

示例脚本:

function myFunction() {
  var start = 4; // Start row number for values.
  var c = {};
  var k = "";
  var offset = 0;
  var ss = SpreadsheetApp.getActiveSheet();

  // Retrieve values of column B.
  var data = ss.getRange(start, 2, ss.getLastRow(), 1).getValues().filter(String);

  // Retrieve the number of duplication values.
  data.forEach(function(e){c[e[0]] = c[e[0]] ? c[e[0]] + 1 : 1;});

  // Merge cells.
  data.forEach(function(e){
    if (k != e[0]) {
      ss.getRange(start + offset, 2, c[e[0]], 1).merge();
      offset += c[e[0]];
    }
    k = e[0];
  });
}

结果:

• 此示例脚本假设B列的值已排序.
• 从您的共享电子表格中，它假设在B列有用于合并单元格的值.
• 在共享电子表格中，排序值的顺序为Composées, Cucurbitacées, Légumineuses, Liliacées, Solanacées.另一方面，希望"的顺序为Composées, Cucurbitacées, Légumineuses, Solanacées, Liliacées.
  • 我不明白Liliacées, Solanacées和Solanacées, Liliacées之间的区别逻辑.
  • 在此示例脚本中，它使用排序后的值的顺序.
  • This sample script supposes that the values of column B is sorted.
  • From your shared spreadsheet, it supposes that there are the values for merging cells at column B.
  • In your shared spreadsheet, the order of sorted values is Composées, Cucurbitacées, Légumineuses, Liliacées, Solanacées. On the other hand, the order of "Wish" is Composées, Cucurbitacées, Légumineuses, Solanacées, Liliacées.
    • I couldn't understand the difference logic between Liliacées, Solanacées and Solanacées, Liliacées.
    • In this sample script, it uses the order of sorted values.
    • merge()

    如果我误解了你的问题，对不起.

    If I misunderstand your question, I'm sorry.

    对于您的下一个问题，我认为以下流程可以实现您想要的.但我认为可能还有其他解决方案.

    For your next question, I think that the following flow can achieve what you want. But I think that there may be other solution.

    1. 由用户添加值.
    2. 使用breakApart()断开合并的单元格.
       • 例如，它合并值为"sample", "sample", "sample"的"A1:A3"单元格.当使用breakApart()破坏此合并的单元格时，getValues()检索到的每个"A1:A3"值将变为[["sample"],[""],[""]].
    1. Add values by an user.
    2. Break the merged cells using breakApart().
       • For example, it merges cells of "A1:A3" which have the values of "sample", "sample", "sample". When this merged cell is broken using breakApart(), each value of "A1:A3" retrieved by getValues() becomes [["sample"],[""],[""]].

    参考:

    • breakApart()

    Reference :

    • breakApart()

    如果要破坏"B1:B"，请按以下方式使用.

    If you want to break "B1:B", please use as follows.

    var ss = SpreadsheetApp.getActiveSheet();
    ss.getRange("B1:B").breakApart()
    

    这篇关于合并具有相同单词的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！