Формула для поиска недостающих серийных номеров

Вот 2 метода, которые вы можете использовать для поиска пропущенных чисел:

Метод 1: формула Google Таблиц

=let(
studio, "A",
arr, sort(filter(A2:C, A2:A=studio),2,0),
arr_x, torow(unique(filter(arr, index(arr,,2) = "X-X"))),
arr_o, torow(unique(filter(arr, index(arr,,2) = "O-O"))),
find_x, IFNA(join(",", torow(filter(sequence(max(arr_x),1,1), countif (arr_x,sequence(max(arr_x),1,1))=0))),"No Missing"),
find_o, IFNA(join(",", torow(filter(sequence(max(arr_o),1,1), countif (arr_o,sequence(max(arr_o),1,1))=0))),"No Missing"),
output, map(unique(index(arr,,2)), vstack(find_x,find_o), lambda(a,b,concat(concat(a,": "), b))),
join(char(10), "MISSING NUMBERS:", concat(studio,":"), output)
)

Примечание. Если вы используете формулу Google Таблиц, вам необходимо принять во внимание два ограничения: (1) Вам необходимо вручную ввести букву Studio внутри формулы или использовать раскрывающийся список, чтобы выбрать букву Studio. (2) Если вы добавите новую букву Studio, вам понадобятся пары X-X и O-O.

Выход:

Способ 2. Скрипт Google Apps.

    function missingNumbers(data) {
      // map studio letters
      const letters = [... new Set(data.map(x => x[0][0]))] 
    
      return "MISSING NUMBERS:\n\n" + letters.map((l) => {
      //filters Fin Year
      const uniqueFinYear = [... new Set(data.filter(x => x[0][0] == l).map(x => x[1]))];
    
        //check sequence of number
        const res = uniqueFinYear.map(x => data.filter(d => d[1] == x).filter(d => d[0] == l))
          .map(x => {
            return x.map(y => y[2]).sort((a, b) => a - b).map((d, i, arr) => d + 1 == arr[i + 1] || d == arr[i + 1])
              .filter((x, i, arr) => arr.length == 1 && x == false ? true : i !== arr.length - 1 && x == false)[0] == false && x
          }).filter(x => x);
    
        return res.map((x) => {
          if (x.length > 0) {
            const newArr = Array.from({ length: Math.max(...x.map(x => x[2])) }, (_, index) => index + 1);
            return `${[... new Set(x.map(x => x[0]))]}: (${[... new Set(x.map(x => x[1]))]}): ${newArr.map(z => [... new Set([... new Set(x.map(x => x[2]).sort((a, b) => a - b))]
              .map(y => y != z && z))].length == 1 && [... new Set([... new Set(x.map(x => x[2]).sort((a, b) => a - b))]
                .map(y => y != z && z))]).flat().filter(d => d).join(',')}  `;
          }
        }).join('');
      }).join(" \n");
    }

Добавьте приведенный выше код в Расширение > Скрипт приложений. Затем вызовите имя функции missingNumbers в ячейке E2. Это будет работать как пользовательская формула.

Выход:

Использованная литература:

• Пусть

• Присоединяйтесь

• Сортировать

• Карта

• Найдите недостающие числа (на чем основывалась логика формулы)

Возможно, это поможет:

=ARRAYFORMULA(LET(a, A2:A20, b, B2:B20, c, C2:C20, 
 p, "max(Col3) where Col3>1 group by Col1,Col2 label max(Col3)''", 
 q, "select max(Col2) group by Col2 pivot Col1",
 f, FLATTEN(INDEX(QUERY({a&":đ("&b&"):¤×", {b,c}}, "select Col1,"&p, ),, 1)&
 BYROW(QUERY({a,b,c}, "select "&p, ), LAMBDA(c, MAKEARRAY(1,c,LAMBDA(c,r,r))))), 
 SUBSTITUTE("MISSING NUMBERS:Đ"&TEXTJOIN(, 1, "Đ"&TRIM(QUERY(QUERY(SPLIT(SUBSTITUTE(
 REGEXREPLACE(FLATTEN(TRIM(QUERY(QUERY(SPLIT(QUERY(FILTER(f, 
 NOT(COUNTIF({a&":đ("&b&"):¤×"&c}, f))), "where not Col1 ends with '×'", )&",", "×"), 
 q),,9^9))), "\s|,$", ), "¤", " "), "đ"), q),,9^9))), "Đ", CHAR(10))))

Обновлять:

=ARRAYFORMULA(LET(z, A2:C20, i, SPLIT(TRANSPOSE(SPLIT(JOIN(,BYROW(FILTER(z, INDEX(z,,1)<>""), 
 LAMBDA(r,JOIN(",",REDUCE(";",r,LAMBDA(c,cc,FLATTEN(FLATTEN(c)&","&SPLIT(cc,",")))))))),";,",)),","), 
 a, INDEX(i,,1), b, INDEX(i,,2), c, INDEX(i,,3), 
 p, "max(Col3) where Col3>1 group by Col1,Col2 label max(Col3)''", 
 q, "select max(Col2) group by Col2 pivot Col1",
 f, FLATTEN(INDEX(QUERY({a&":đ("&b&"):¤×", {b,c}}, "select Col1,"&p, ),, 1)&
 BYROW(QUERY({a,b,c}, "select "&p, ), LAMBDA(c, MAKEARRAY(1,c,LAMBDA(c,r,r))))), 
 SUBSTITUTE("MISSING NUMBERS:Đ"&TEXTJOIN(, 1, "Đ"&TRIM(QUERY(QUERY(SPLIT(SUBSTITUTE(
 REGEXREPLACE(FLATTEN(TRIM(QUERY(QUERY(SPLIT(QUERY(FILTER(f, 
 NOT(COUNTIF({a&":đ("&b&"):¤×"&c}, f))), "where not Col1 ends with '×'", )&",", "×"), 
 q),,9^9))), "\s|,$", ), "¤", " "), "đ"), q),,9^9))), "Đ", CHAR(10))))

Вот один из подходов, который вы можете опробовать:

=iferror(let(Λ,map(A2:A,B2:B,lambda(a,b,join(",",let(Σ,sequence(maxifs(C:C,A:A,a,B:B,b)),ifna(filter(Σ,isna(xmatch(Σ,filter(C:C,A:A&B:B=a&b))))))))),
 Γ,unique(filter({A2:A&":","("&B2:B&"): "&Λ},Λ<>"")), Ξ,index(Γ,,1), join(char(10),vstack("MISSING NUMBERS:",,map(unique(Ξ),lambda(x,concat(x&" ",join(" ",filter(index(Γ,,2),Ξ=x)))))))),"-_-")