Блог

У меня есть ячейка Google Sheets, содержащая список людей, посещающих мероприятие. Некоторые гости приведут друзей. Таким образом, ячейка (A1) может выглядеть следующим образом:

 Ben, Sarah   2, James , Mary   5
  

Мне нужно подсчитать общее количество посетителей, которое в данном случае равно 11. И поэтому я подумал об использовании формулы в следующих строках:

 =count(SPLIT(SUBSTITUTE(A1," ",","),","))
  

Но это не работает, потому что он считает числа только как 1 элемент, а функция подсчета, похоже, не работает.

Как я могу заставить это работать, чтобы оно правильно указывало количество участников как 11?

Вы можете использовать следующую формулу

 =IF(LEN(A2), 
     SUM(COUNTA(SPLIT(A2,",")), 
         IFERROR(SPLIT(REGEXREPLACE(A2,"D"," ")," "))),"")
  

Используемые функции:

• IF
• LEN
• SUM
• COUNTA
• SPLIT
• IFERROR
• REGEXREPLACE

Вы можете получить общую сумму, выполнив это:

=if(regexmatch(A1," "),sum(ArrayFormula(query(split(transpose(split(A1,","))," "),"select count(Col1), sum(Col2)",0))),if(isblank(A1),"",counta(split(A1,","))))

Объяснение:

• if(regexmatch(A1," "), something, if(isblank(A1),"",counta(split(A1,",")))) => если в ответе нет знаков, проверьте, пуста ячейка или нет, если пусто, выведите пробел, иначе просто посчитайте, сколько людей между запятыми, в противном случае посчитайте с плюсовыми. (объяснение ниже)
• split(A1,","))," ") => красная область => разделит ячейку запятыми, а результат можно увидеть в красной области на картинке
• split(TRANSPOSE(split(A1,","))," ") => зеленая область => будет перебирать каждый из приведенных выше результатов и выделять в ячейку справа значения , между которыми есть знак, можно увидеть в зеленой области на изображении
• query(split(TRANSPOSE(split(A1,","))," "),"select count(Col1), sum(Col2)",0) => синяя область => затем мы запросим 2 столбца, в левом мы хотим подсчитать количество строк в этом столбце (столбцы с именами), в следующем столбце мы хотим суммировать значения (плюсовые)
• sum(ArrayFormula(query(split(transpose(split(A1,","))," "),"select count(Col1), sum(Col2)",0))) => желтая область => затем мы суммируем значения 2 столбцов, чтобы получить конечный результат