Как подсчитать максимальное количество одинаковых значений подряд в столбце?

Question

[kushekpayev]

Как подсчитать максимальное количество одинаковых значений подряд в столбце? Желательно в Sheets, но можно и в Excel мне важно вообще понять принцип, так как ничего не нашел в интернете, а сам новичек в таблицах
Имеется столбец с определенными значениями, по определенным причинам нас интересуют именно серии из одинаковых значений подряд, а конкретно максимальная серия, таким образом нужно чтобы формула находила серии и среди них определяла наибольшую для конкретного значения. На картинке думаю понятно что нам требуется. Пожалуйста кто нибудь подскажите как это реализовать

Answer 1

[DevMan]

=MAX(ARRAYFORMULA(LEN(SPLIT(JOIN("",FILTER(--(A:A = "cat"),A:A<>"")),"0"))))
=MAX(ARRAYFORMULA(LEN(SPLIT(JOIN("",FILTER(--(A:A = "dog"),A:A<>"")),"0"))))

Comments

[kushekpayev]

Потрясающе, большое спасибо!

[Григорий Боев]

Запомнил фишку с "--()"

Answer 2

[dollar]

1. Выберите пункт меню Инструменты -> Редактор скриптов.
   
2. В появившийся редактор скопируйте следующий код:
   
   Код

   function MAX_THE_SAME(input, search) {
     if (!input) return "Нет данных";
     if (!(input instanceof Array)) return "Выбрана одна ячейка";
     var current;
     var count = 0;
     var max = 0;
     for(var x=0; x<input[0].length; x++) {
       current = null;
       for(var y=0; y<input.length; y++) {
         var val = input[y][x];
         if (!val && val !== 0 || search && val!=search) {
           current = null;
           continue;
         }
         val = val.toString();
         if (current !== val) {
           current = val;
           count = 0;
         }
         count++;
         if (max < count) max = count;
       }
     }
     return max;
   }

   
   
3. Сохраните.
   
4. В любом месте страницы вставьте формулу =MAX_THE_SAME(A:A)
   или =MAX_THE_SAME(A:A;"dog")
   

Comments

[dollar]

Еще можно, как предложил товарищ DevMan:

=MAX(ARRAYFORMULA(LEN(SPLIT(JOIN("",FILTER(--(A:A = "cat"),A:A<>"")),"0"))))

Но в случае наличия пустых строк это не сработает.

[kushekpayev]

dollar, да попробывал оба варианта ваш и DevMan, действительно в случае пустой строки он все равно видит как серию, ваш вариант более точен. Большое спасибо!

Answer 3

[Alexander Ivanov]

Для такой задачи ожидается какое-то комплексное решение, т.к. поиск исключительно по условию вхождения элемента несколько умаляет достоинства табличных расчетов.

Для пользовательской функции

/**
* Returns a consecutive array of the counts of sequences.
*
* @param {Sheet3!B1:B34} reference A range.
* @returns                      The counts of sequences.
* @customfunction
*/
function COUNT_CONSECUTIVE( reference ){
  try{
    return reference.reduce(function(p, v, i, arr){
      if(i === 0 || arr[i][0] !== arr[i - 1][0])
        p.push([v[0], 1]);
      else
        p[p.length-1][1]++;
      return p;
    }, []);
  } catch(err){
    return err.message
  }
}

для диапазона A2:A10 верно, что все группы без номеров с их количествами можно получить как

=COUNT_CONSECUTIVE(A2:A10)



только для 'cat' верно

=QUERY(COUNT_CONSECUTIVE(A2:A9),"select max(Col2) where Col1='cat' label max(Col2)''")

Для формулы

=ARRAYFORMULA(QUERY({A2:A10,VLOOKUP(ROW(A2:A10),IF(IF(A2:A10<>A1:A9,ROW(A2:A10),0)>0,{ROW(A2:A10),IF(A2:A10<>A1:A9,ROW(A2:A10),0)},),2),IF(IF(A2:A10<>A1:A9,ROW(A2:A10),0)=0, COUNTIFS(ROW(A2:A10),">=" & VLOOKUP(ROW(A2:A10),IF(IF(A2:A10<>A1:A9,ROW(A2:A10),0)>0,{ROW(A2:A10),IF(A2:A10<>A1:A9,ROW(A2:A10),0)},),2),ROW(A2:A10),"<="&ROW(A2:A10)),1)},"select Col1,max(Col3) group by Col2,Col1 label max(Col3)''"))

для диапазона A2:A10 верно, что все группы без номеров с их количествами можно получить как



только для 'cat' верно

=ARRAYFORMULA(QUERY(QUERY({A2:A10,VLOOKUP(ROW(A2:A10),IF(IF(A2:A10<>A1:A9,ROW(A2:A10),0)>0,{ROW(A2:A10),IF(A2:A10<>A1:A9,ROW(A2:A10),0)},),2),IF(IF(A2:A10<>A1:A9,ROW(A2:A10),0)=0, COUNTIFS(ROW(A2:A10),">=" & VLOOKUP(ROW(A2:A10),IF(IF(A2:A10<>A1:A9,ROW(A2:A10),0)>0,{ROW(A2:A10),IF(A2:A10<>A1:A9,ROW(A2:A10),0)},),2),ROW(A2:A10),"<="&ROW(A2:A10)),1)},"select Col1,max(Col3) group by Col2,Col1 label max(Col3)''"),"select max(Col2) where Col1='cat' group by Col1 label max(Col2)''"))

Таблицу с примером выложу в ближайшее время.

P.S. Знающие люди подсказывают, что для конкретного значения 'cat' справедливо также

=ARRAYFORMULA(MAX(FREQUENCY(ROW(A2:A10),ROW(A2:A10)*(A2:A10<>"cat")))-1)

Comments

[kushekpayev]

Дочитал, ничего не понял, но проверил, работает может однажды я пойму) . Сайт в закладки сохранил. Спасибо большое!