Подсчёт значений, разделённых запятыми, в ячейке

В этом руководстве вы узнаете, как с помощью комбинации функций ДЛСТР, ПСТР и ПОДСТАВИТЬ подсчитывать значения, разделённые запятыми, в ячейке Excel.

Как подсчитать значения, разделённые запятыми, в ячейке Excel?

Как показано на снимке экрана ниже, в столбце B содержится список строк, разделённых запятыми. Чтобы подсчитать количество значений, разделённых запятыми, в каждой ячейке, выполните следующие действия.

Универсальная формула

=IF(ISBLANK(B3),"",LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3),",",""))+1)

Аргументы

Как использовать эту формулу?

1. Выберите пустую ячейку для вывода результата — в данном случае это ячейка D3.

2. Введите в неё приведённую ниже формулу и нажмите клавишу Enter. Затем перетащите маркер заполнения вниз, чтобы применить её к другим ячейкам.

=IF(ISBLANK(B3),"",LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3),",",""))+1)

Примечание: в этой формуле B3 — ячейка со строкой, разделённой запятыми, количество значений в которой нужно подсчитать. При необходимости её можно изменить.

Как работает эта формула?

=IF(ISBLANK(B3),"",LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3),",",""))+1)

1. LEN(TRIM(B3)): сначала функция ПСТР удаляет все лишние пробелы из строки «AA, BB, CC, DD», оставляя только один пробел между словами, а затем функция ДЛСТР вычисляет общую длину получившейся строки и возвращает результат 14;

2. LEN(SUBSTITUTE(TRIM(B3),",",""))

• SUBSTITUTE(TRIM(B3),",","")Как указано в пояснении выше, функция ПСТР удаляет из строки «AA, BB, CC, DD» все лишние пробелы и возвращает результат «AA, BB, CC, DD». Затем функция ПОДСТАВИТЬ заменяет все запятые в этой строке на пустую строку, получая «AA BB CC DD».
• LEN("AA BB CC DD")Функция ДЛСТР возвращает количество символов в текстовой строке «AA BB CC DD» — результат равен 11.

3. 14-11+1: из общей длины строки «AA, BB, CC, DD» вычитается длина той же строки без запятых («AA BB CC DD»), чтобы определить количество запятых (14 − 11 = 3). Поскольку после последней запятой всегда следует слово, к полученному числу добавляется 1 — так мы получаем общее количество слов, разделённых запятыми. В данном случае результат равен 4.

4. IF(ISBLANK(B3),«»,14): функция ЕСЛИ проверяет, пуста ли ячейка B3. Если да — возвращается пустое значение, если нет — число 14. Поскольку ячейка B3 содержит данные, функция возвращает 14.

Связанные функции

Связанные формулы