Как суммировать текст в ячейках в Excel

Обновлено: 04.07.2024

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

Примеры формул Excel «Считать, если ячейка содержит»

В Microsoft Excel есть две функции для подсчета ячеек на основе их значений: СЧЁТЕСЛИ и СЧЁТЕСЛИМН. Эти функции охватывают большинство, хотя и не все, сценариев. Приведенные ниже примеры научат вас, как выбрать подходящий счетчик, если ячейка содержит формулу для вашей конкретной задачи.

Учитывать, содержит ли ячейка текст

В ситуациях, когда вы хотите подсчитать ячейки, содержащие какой-либо текст, используйте подстановочный знак звездочки в качестве критерия в формуле СЧЁТЕСЛИ:

Или используйте функцию СУММПРОИЗВ в сочетании с ИСТЕКСТ:

Во второй формуле функция ISTEXT оценивает каждую ячейку в указанном диапазоне и возвращает массив значений TRUE (текст) и FALSE (не текст); двойной унарный оператор (--) преобразует ИСТИНА и ЛОЖЬ в 1 и 0; и СУММПРОИЗВ суммирует числа.

Как показано на снимке экрана ниже, обе формулы дают одинаковый результат:

Учитывать, если ячейка содержит определенный текст

Чтобы подсчитать количество ячеек, содержащих определенный текст, используйте простую формулу СЧЕТЕСЛИ, как показано ниже, где диапазон – это ячейки для проверки, а текст – текстовая строка для поиска. или ссылку на ячейку, содержащую текстовую строку.

Например, чтобы подсчитать количество ячеек в диапазоне A2:A10, содержащих слово "платье", используйте следующую формулу:

Или тот, что показан на скриншоте:

Учитывать, если ячейка содержит текст (частичное совпадение)

Чтобы подсчитать количество ячеек, содержащих определенную подстроку, используйте функцию СЧЕТЕСЛИ с подстановочным знаком звездочки (*).

Например, чтобы подсчитать, сколько ячеек в столбце A содержат слово "платье" как часть своего содержимого, используйте следующую формулу:

Или введите нужный текст в какую-либо ячейку и соедините эту ячейку с подстановочными знаками:

Учитывать, если ячейка содержит несколько подстрок (логика И)

Чтобы подсчитать ячейки с несколькими условиями, используйте функцию СЧЁТЕСЛИМН. Excel COUNTIFS может обрабатывать до 127 пар диапазон/критерий, и будут учитываться только ячейки, соответствующие всем указанным условиям.

Например, чтобы узнать, сколько ячеек в столбце A содержат слова "платье" И "синий", используйте одну из следующих формул:

Считать, если ячейка содержит число

Формула для подсчета ячеек с числами — самая простая формула, которую можно себе представить:

Имейте в виду, что функция СЧЁТ в Excel подсчитывает ячейки, содержащие любое числовое значение, включая числа, даты и время, поскольку в терминах Excel последние два числа также являются числами.

В нашем случае формула выглядит следующим образом:

Чтобы подсчитать ячейки, НЕ содержащие чисел, используйте функцию СУММПРОИЗВ вместе с ЕСНИМБЕР и НЕ:

Сумма, если ячейка содержит текст

Если вы ищете формулу Excel для поиска ячеек, содержащих определенный текст, и суммирования соответствующих значений в другом столбце, используйте функцию СУММЕСЛИ.

Например, чтобы узнать, сколько платьев есть в наличии, используйте следующую формулу:

Где A2:A10 — текстовые значения для проверки, а B2:B10 — числа для суммирования.

Или поместите интересующую подстроку в какую-нибудь ячейку (E1) и укажите ссылку на эту ячейку в своей формуле, как показано на снимке экрана ниже:

Чтобы суммировать по нескольким критериям, используйте функцию СУММЕСЛИМН.

Например, чтобы узнать, сколько синих платьев доступно, используйте следующую формулу:

Или используйте этот:

Где A2:A10 — ячейки для проверки, а B2:B10 — ячейки для суммирования.

Выполнять различные вычисления на основе значения ячейки

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

Предположим, что у вас есть данные о продажах в столбце B, и вы хотите рассчитать бонусы на их основе: если объем продаж превышает 300 долларов США, бонус составляет 10 %; при продажах от 201 до 300 долларов бонус составляет 7%; для продаж на сумму от 101 до 200 долларов США бонус составляет 5%, а при продажах менее 100 долларов бонус не взимается.

Для этого просто умножьте объем продаж (B2) на соответствующий процент. Как узнать, на какой процент умножать? Тестируя различные условия с вложенными ЕСЛИ:

=B2*ЕСЛИ(B2>=300,10%, ЕСЛИ(B2>=200,7%, ЕСЛИ(B2>=100,5%,0)))

В реальных рабочих листах может быть удобнее вводить проценты в отдельных ячейках и ссылаться на эти ячейки в формуле:

Ключевым моментом является закрепление ссылок на бонусные ячейки знаком $, чтобы предотвратить их изменение при копировании формулы вниз по столбцу.

Условное форматирование Excel, если ячейка содержит определенный текст

Если вы хотите выделить ячейки с определенным текстом, настройте правило условного форматирования Excel на основе одной из следующих формул.

Без учета регистра:

С учетом регистра:

Например, чтобы выделить артикулы, содержащие слова "платье", создайте правило условного форматирования с приведенной ниже формулой и примените его к необходимому количеству ячеек в столбце A, начиная с ячейки A2:

Формула условного форматирования Excel: если ячейка содержит текст (несколько условий)

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

=И(ПОИСК("платье", A2)>0, ПОИСК("синее", A2)>0)

Если ячейка содержит определенный текст, удалить всю строку

Если вы хотите удалить строки, содержащие определенный текст, используйте функцию поиска и замены Excel следующим образом:

Выберите все ячейки, которые хотите проверить.
Нажмите Ctrl + F, чтобы открыть диалоговое окно Найти и заменить.
В поле Найти введите текст или число, которое вы ищете, и нажмите кнопку Найти все
Нажмите на любой результат поиска, а затем нажмите Ctrl + A, чтобы выбрать все.
Нажмите кнопку Закрыть, чтобы закрыть окно Найти и заменить
Одновременно нажмите клавишу Ctrl и кнопку "минус" ( Ctrl - ), которая является ярлыком Excel для удаления.
В диалоговом окне Удалить выберите Вся строка и нажмите OK. Готово!

На снимке экрана ниже мы удаляем строки, содержащие слово «платье»:

Если ячейка содержит, выберите или скопируйте целые строки

В ситуациях, когда вы хотите выбрать или скопировать строки с релевантными данными, используйте автофильтр Excel для фильтрации таких строк. После этого нажмите Ctrl+A, чтобы выбрать отфильтрованные данные, Ctrl+C, чтобы скопировать их, и Ctrl+V, чтобы вставить данные в другое место.

Чтобы отфильтровать ячейки с двумя или более критериями, используйте расширенный фильтр, чтобы найти такие ячейки, а затем скопируйте все строки с результатами или извлеките только определенные столбцы.

Подробные пошаговые инструкции см. в следующих руководствах:

Вот как вы манипулируете ячейками на основе их значения в Excel. Чтобы поближе познакомиться с формулами, обсуждаемыми в этом руководстве, вы можете загрузить образец книги Excel, если ячейка содержит.Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Вас также может заинтересовать

76 комментариев к "Excel: если ячейка содержит, то подсчитать, суммировать, выделить, скопировать или удалить"

Здравствуйте, моя компания дает комиссионные нашим водителям (называемым "капитанами", как показано на снимке экрана Excel). Их комиссия составляет 10% от продажной цены надбавки, которую они продают.

Я хочу отслеживать их ежедневные комиссионные на капитана, а затем суммировать каждую ежедневную комиссию с их еженедельной общей комиссией на капитана.

Я уже настроил их ежедневное отслеживание для автоматического расчета их ежедневных комиссий на основе цен надбавок и общего количества проданного для каждого из них.

Теперь моя проблема заключается в том, чтобы создать формулу, которая автоматически ищет в строках от P5 до T5 записи их уникальных имен. Каждый раз, когда их имя пишется в этой строке, это указывает на один рабочий день с выполненными комиссиями (общая сумма для каждой ежедневной комиссии указана в строках от P30 до T30) в их собственном столбце.

Тогда я хотел бы, чтобы та же формула имела автозаполнение от N34 до N36, если есть имена капитанов, найденные в P5 до T5 (без дублирования их имен), а затем суммировали их ежедневные комиссионные, относящиеся к имени каждого капитана, в еженедельных итогах от O34 до О36.

Надеюсь, это имеет смысл. Дайте мне знать, если вам нужны разъяснения.

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

Привет!
Ваш запрос выходит за рамки советов, которые мы даем в этом блоге. Это сложное решение, которое невозможно найти с помощью одной формулы. Если у вас есть конкретный вопрос о работе функции или формулы, я постараюсь на него ответить.

Здравствуйте,
функция СУММЕСЛИМН у меня работает, но СУММЕСЛИМН не работает, хотя я много раз пытался. =СУММЕСЛИМН(J2:J29,G2:G29,"Пассивный",G2:G29,"Коннектор") - это формула, которую я пытаюсь использовать в своем Excel. Я также использовал * перед текстовыми значениями, но безрезультатно, вместо этого отображается 0. Я не могу понять, почему отображается 0. Пожалуйста, помогите.

Здравствуйте!
Я не вижу ваших данных и не могу проверить, но эта формула должна работать

Я рекомендую прочитать это руководство: СУММЕСЛИ в Excel с несколькими критериями (логика ИЛИ).
Надеюсь, это то, что вам нужно.

У меня есть лист Excel, где в столбце A указаны различные даты продаж за трехлетний период, а в столбце BI есть еще один столбец, содержащий текст, относящийся к типу продажи, либо «Прямой», либо «Косвенный», чтобы указать если продажа была прямой продажей или через другую сторону. Я хочу иметь возможность подсчитать, сколько прямых или непрямых продаж было совершено за каждый год. Вы можете помочь? Я попытался объединить формулы COUNTIF и COUNTIFS, но это не работает. Я могу получить количество продаж в каждом году, используя формулу COUNTIFS, но проблема начинается, когда я пытаюсь извлечь количество продаж в каждом году для каждого из Direct или Indirect, комбинируя две формулы. Спасибо! Ваш сайт потрясающий! Мне удалось решить много проблем, проверив ваши решения.

Здравствуйте!
Какие формулы вы используете? Что в них не работает? Вы должны использовать формулу СЧЁТЕСЛИМН с двумя условиями — Дата и Тип продажи.
Пожалуйста, ознакомьтесь с этой статьей - СЧЁТЕСЛИСЛИ и СЧЁТЕСЛИ в Excel с несколькими критериями И/ИЛИ
Если они не работают для вас, подробно опишите свою задачу, я постараюсь предложить решение.

Какую формулу можно использовать для сложения цифр и букв в одном столбце? Например, у меня есть 12 ячеек со значениями в одном столбце ("x" представляет 1):

До того, как числа были добавлены и мы считали только "x", мы использовали эту формулу =СЧЁТЕСЛИ(G73:G85,"=x")

Теперь, когда числа также включены, мне трудно найти формулу, которая складывает как "x" как 1, так и остальные числа.

Здравствуйте!
Если я правильно понимаю вашу задачу, следующая формула должна работать для вас:

Функция СУММ только складывает числа и игнорирует текст.

У меня есть две вкладки:

Первая вкладка:
Имя столбца A
Столбец B Да ( или Нет)

Например:
Столбец A Столбец B
Анна Да
Лили Нет

Если значение столбца B равно "Да", я хочу добавить значение столбца A (Анна) на другую вкладку A1.
Если значение столбца B равно "Нет", я не хочу добавлять значение столбца A (Лили) на другую вкладку.

Как этого добиться?

Здравствуйте!
Вы можете использовать рекомендации и примеры из статьи: Как выполнять ВПР несколько значений в Excel с одним или несколькими критериями.
Вы также можете использовать функцию ФИЛЬТР, чтобы получить нужные значения.
Надеюсь, мой совет поможет вам решить вашу задачу.

Мне очень трудно получить нужную мне формулу. Таблица предназначена для опционов на акции. Для простого примера столбец A представляет собой дебет, который стоил для открытия контракта. Соседняя ячейка в столбце B — это цена, по которой я продаю, чтобы закрыть контракт. У меня есть все, что мне нужно для прибыли/убытка и взятого процента.Я пытаюсь понять, как мне рассчитать мой истинный доход? если, например, я открываю контракт на -500 долларов и продаю его за 750 долларов, я получаю доход в размере 50% по этой конкретной строке. Однако, если я открою еще одну сделку, используя те же 500 долларов, а затем 250 долларов, которые я получил для сделки, как мне рассчитать общий доход? Он хотел бы рассчитать мой доход на 1250 долларов вместо 750 долларов. Будем признательны за любую помощь.

Привет!
Мы не специализируемся на акциях. Если у вас есть конкретный вопрос о формуле Excel, мы постараемся помочь.

Пытаюсь составить формулу, используя суммы в B1 "lol" && C1 "wow"

A1 пустая ячейка (я хочу поместить сюда текст имени, который будет ссылкой на A2:A10)
B1 показать общее количество lol
C1 показать общее количество вау
A2:A10 Имена
B2:B10 [sum_range]
C2:C10 текст "Lol" или "вау"

Я хочу суммировать значение в B2:B10, если оно соответствует условию:
Суммировать:
Условие 1 Если A2:A10 верно (или то же самое с текстом, который я ввожу в A1)
/>Условие 2, если C2:C10 "лол" или в категории "вау"

экв.
A1 "10/06"
B1 (если A1 верно, A2;A10 суммирует общее количество 10/06 с lol
C1 суммирует общее количество 10/06 wow

Здравствуйте!
Чтобы найти сумму значений условий, используйте функцию СУММЕСЛИМН.
Если что-то по-прежнему неясно, не стесняйтесь спрашивать.

Привет, Алекс,
Начнем с того, что сайт очень замечательный и информативный.
У меня есть данные в следующих столбцах:
Col A Col B
21-Sep-21 55
23-Sep-21 74
02-Oct-21 21
05-окт-21 05

Все, что я хочу сделать, это добавить значения для сентября и октября, как показано ниже
Sep'21 xxxxx
Oct'21 yyy

Привет!
Я не понимаю, что вы хотите сделать. Объясните подробнее.

Я пытаюсь найти формулу для расчета общего среднего балла, но буду вычитать из нее оценки, имеющие "TR" в столбце семестра. Какая формула подойдет для этого?
ПРИМЕР

Оценка Часов Очки Семестр
A 3 12 SP20
B 3 9 TR
C 2 4 FA21
D 1 1 TR

Привет!
Попробуйте использовать функцию СРЗНАЧЕСЛИ, чтобы вычислить среднее значение с условиями.

Мне нужна помощь с этой формулой. =СУММЕСЛИ($D:$D,"*Волна 1*",$F:$F)
Сейчас я хочу добавить значения из F, если D говорит Волна 1, однако, если сумма равна 0, я хочу это сказать TBD .. не знаю, как сделать часть TBD

Привет!
Что такое TBD? Пожалуйста, опишите вашу проблему более подробно.

Здравствуйте,
Мне нужно добавить значения в альтернативные ячейки. например: значения в I5, K5, M5 и сумма должны игнорировать текст в этих ячейках. Пожалуйста, предложите

На самом деле я хотел бы знать, может ли Excel отображать значение ячейки в другой ячейке с помощью условия ЕСЛИ.
например, значением A1 является "ЦВЕТЫ"
введите текст "FL" в B1
результатом должно быть значение A1, которое будет отображаться в C1, если вы поставите условие, что если значение B1 = "FL"
Есть ли какая-нибудь формула для решения этой проблемы?

Здравствуйте!
Вы можете узнать больше о функции ЕСЛИ в Excel в этой статье в нашем блоге.

Добавление ячеек в Excel — простая, но мощная задача. Существует несколько способов суммирования ячеек с числовыми данными в Excel. О некоторых из них я уже рассказывал в одном из своих предыдущих постов. Вы можете проверить это здесь, 4 способа суммирования ячеек в MS Excel. В этой статье я рассказал, как суммировать ячейки, содержащие как текстовые, так и числовые данные, с помощью формул.

Загрузить книгу

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

2 формулы для суммирования ячеек с текстом и числами в MS Excel

1. Использование функции «ЗАМЕНИТЬ»

В формуле, которую я использовал здесь, используются две функции Excel: функция ПОДСТАВИТЬ и функция СУММ. Формула сначала отделяет текст от числовых значений с помощью функции ПОДСТАВИТЬ, а затем складывает их с помощью функции СУММ.

Синтаксис функции ПОДСТАВИТЬ следующий:

ЗАМЕНИТЬ (текст, старый_текст, новый_текст, [экземпляр])

текст: ячейка или ячейки для изменения.

old_text: старый текст для замены.

new_text: новый текст для замены.

instance [Необязательно]: заменяемый экземпляр. По умолчанию все экземпляры будут заменены.

Подробнее об этой функции можно узнать, прочитав эту документацию Microsoft.

Синтаксис функции СУММ следующий:

Функция СУММ принимает 3 типа входных данных: положительные или отрицательные числовые значения, диапазон и ссылки на ячейки. Он принимает эти входные данные и показывает их суммирование в качестве выходных данных. Первый аргумент является обязательным, остальные необязательны, и он может содержать до 255 номеров.

Подробнее об этой функции можно узнать, прочитав эту документацию Microsoft.

Здесь я рассчитал общий вес продуктов (ячейка D14), добавив отдельные веса в столбце D.

Формула выглядит следующим образом:

СУММ(ПОДСТАВИТЬ("25 кг, 10 кг, 10 кг, 7 кг, 30, кг", " кг", "")+0)
СУММ("25, 10, 10, 7, 30" +0)

[Кг заменен пустым текстом.]

[После замены «Kg» Excel по-прежнему воспринимал его как текст. При добавлении нуля (0) значения были изменены на числовые.]

[Функция СУММ добавила значения и вернула желаемое суммирование.]

2. Использование функций «ВЛЕВО» и «НАЙТИ»

В формуле, которую я использовал здесь, используются четыре функции Excel: функция НАЙТИ, функция ЛЕВЫЙ, функция ЗНАЧ и функция СУММ. Формула сначала разделяет числовые значения, а затем суммирует их.

Синтаксис функции НАЙТИ следующий:

Эта функция используется для поиска определенной текстовой строки в текстовой строке.

=НАЙТИ (find_text, inside_text, [start_num])

find_text: текст, который необходимо найти.

within_text: текст для поиска.

start_num [Необязательно]: начальная позиция поиска. По умолчанию start_num=1. Первый символ в пределах_текста — это символ номер 1.

Подробнее об этой функции можно узнать, прочитав эту документацию Microsoft.

Синтаксис функции LEFT следующий:

Эта функция используется для извлечения символов из текста.

текст: текстовая строка, из которой нужно извлечь символы.

num_chars [Необязательно]: количество символов для извлечения. Он начинается слева. По умолчанию num_chars=1.

Подробнее об этой функции можно узнать, прочитав эту документацию Microsoft.

Синтаксис функции ЗНАЧЕНИЕ следующий:

Эта функция преобразует текст в числовые значения.

текст: текст, который необходимо преобразовать в числа.

Подробнее об этой функции можно узнать, прочитав эту документацию Microsoft.

Ознакомьтесь с разделом функции СУММ первого метода, чтобы понять функцию СУММ. Там это объясняется.

Здесь я рассчитал общий вес продуктов (ячейка D14), добавив отдельные веса в столбце D.

Формула выглядит следующим образом:

СУММ(ЗНАЧ(ЛЕВО(D9:D13,НАЙТИ(" ","25 кг, 10 кг, 10 кг, 7 кг, 30 кг))-1)))
СУММ(ЗНАЧ(ЛЕВО(D9:D13,(3, 3, 3, 2, 3)-1)))

[Функция НАЙТИ вернула позиции пробелов в каждой ячейке.]

[Поскольку каждое число имеет пробел в конце, вычитание единицы из его местоположения дает цифровое значение каждой ячейки.]

СУММ(ЗНАЧ(ЛЕВО("25 кг, 10 кг, 10 кг, 7 кг, 30 кг",(2, 2, 2, 1, 1))))
СУММ(ЗНАЧ(25, 10, 10, 7, 30))

[Функция LEFT извлекает числовые символы из текста, используя числовые значения каждой ячейки.]

[Функция ЗНАЧ преобразовала числовые символы в числовые значения.]

[Функция СУММ добавила значения и вернула желаемое суммирование.]

Заключение

Есть много ситуаций, когда числа и текст связаны вместе в одной ячейке в Excel. В таких сценариях нахождение суммы этих типов ячеек становится сложной задачей. В этой статье рассмотрена эта проблема и предоставлены две формулы Excel, которые вычисляют суммы таких ячеек. Надеюсь, вы найдете решение, которое искали. Пожалуйста, оставьте комментарий, если у вас есть какие-либо предложения или вопросы. Спасибо.

Сначала мы рассмотрим, как суммировать числа, сохраненные или отформатированные как текст.

В следующем примере столбец "Зарплата" хранится как текст. Если попытаться суммировать значения, Excel отобразит ноль.

Вместо этого, чтобы выполнить операцию СУММ над числами, хранящимися в виде текста, можно использовать формулу массива с функциями СУММ и ЗНАЧЕНИЕ, например:

Функция ЗНАЧ преобразует текст, представляющий число, в число. Функция SUM суммирует эти числа.

В Excel 365 и версии Excel более новой, чем 2019, вы можете просто ввести формулу, как обычно.Однако при использовании Excel 2019 и более ранних версий вы должны ввести формулу массива, нажав CTRL + SHIFT + ENTER (вместо ENTER), сообщая Excel, что формула является формулой массива. Вы узнаете, что это формула массива, по фигурным скобкам вокруг формулы (см. верхнее изображение). В более поздних версиях Excel и Excel 365 вместо этого можно просто нажать клавишу ВВОД.

Давайте рассмотрим следующее пояснение, чтобы лучше понять формулу.

Функция ЗНАЧЕНИЕ

Функция ЗНАЧ, используемая в качестве формулы массива, преобразует весь диапазон чисел, хранящихся в виде текста, в массив чисел и возвращает его в качестве входных данных для функции СУММ.

Чтобы узнать, что возвращает функция VALUE, выберите нужную функцию и нажмите F9.

СУММА текстовых значений

Для СУММИРОВАНИЯ диапазона текстовых значений, где каждому такому текстовому значению назначается уникальный код, можно использовать формулу массива.

В следующей таблице указано, что люди выбирают для трех заданных вопросов. В таблице справа перечислены четыре возможных ответа, каждому из которых присвоено кодовое значение. Мы должны суммировать коды для каждого человека.

Мы рассмотрим приведенную ниже формулу.

Функция ПОИСКПОЗ

Функция ПОИСКПОЗ ищет указанный элемент в диапазоне и возвращает его относительное положение в этом диапазоне. Его синтаксис:

Поскольку в этом примере используется формула массива, давайте посмотрим, что возвращает функция ПОИСКПОЗ.

Для заданного человека функция ПОИСКПОЗ находит относительное положение каждого ответа в диапазоне H3:H6. Результатом является массив позиций.

Примечание. В формуле массива, чтобы просмотреть, что возвращает функция, выберите нужную функцию и нажмите F9.

Функция ЕСЛИ и N

Функции ЕСЛИ и N, используемые вместе, возвращают следующий массив в качестве входных данных для функции ИНДЕКС.

Здесь две функции возвращают массив относительных позиций ответов в диапазоне H3:H6. Целью использования функций IF и N является выполнение процесса, называемого разыменованием. Проще говоря, две функции заставляют функцию ИНДЕКС передать весь массив кодовых значений функции СУММ.

Мы объясним это в следующем разделе.

Функция ИНДЕКС

Функция ИНДЕКС возвращает значение, расположенное на пересечении указанной строки и столбца в диапазоне. Его синтаксис:

Давайте посмотрим, как это работает в виде формулы массива:

Функция ИНДЕКС находит значения кода в диапазоне I3:I6 в соответствии с заданными номерами позиций. Затем он возвращает массив значений, т. е. соответствующий код для каждого ответа, в функцию SUM для выполнения операций.

Убедитесь, что количество строк и столбцов в столбцах "Ответ" и "Код" одинаковое.

Функция СУММ

Функция СУММ суммирует значения кода, возвращаемые функцией ИНДЕКС.

Таблицы Excel — отличный способ хранения и анализа данных. Электронные таблицы обычно состоят из ячеек с комбинацией чисел и текста. Чтобы лучше понять ваши данные, вам нужно различать ячейки с текстом. Это намного проще, чем кажется.

В этой статье объясняется, как выбрать диапазон ячеек, а затем использовать функцию «СЧЕТЕСЛИ» для подсчета ячеек, содержащих текст. Кроме того, вы узнаете, как находить определенный, повторяющийся и цветной текст в электронной таблице.

Как подсчитать ячейки с текстом в Excel на ПК с Windows

Чтобы подсчитать ячейки с текстом в электронной таблице на компьютере с Windows, выполните следующие действия:

Как считать ячейки с текстом в Excel на Mac

Действия для подсчета ячеек с текстом в электронной таблице через macOS такие же, как и в Excel для Windows:

Запустите Excel, затем откройте таблицу, которую хотите проанализировать. Вы также можете просто "дважды щелкнуть" файл, если он использует Excel в качестве программы по умолчанию.
Выберите пустую ячейку в электронной таблице, чтобы ввести формулу.
В пустой ячейке введите « =СЧЁТЕСЛИ (диапазон, критерии) ». Эта формула подсчитывает количество ячеек в указанном диапазоне с текстом в них.
В поле "диапазон" введите диапазон ячеек, который вы хотите просмотреть. Введите первую и последнюю ячейки, разделенные двоеточием. Например, чтобы подсчитать ячейки от B2 до B10, введите « B2:B10 ».
В части «критерии» введите « «*» » в кавычках. Это подсчитывает количество ячеек с текстом в введенном вами диапазоне. Например, ваша полная формула должна выглядеть примерно так: «=СЧЕТЕСЛИ (B2:B10, «*»)».
На клавиатуре нажмите Enter, чтобы применить формулу. Результат появится в выбранной ячейке.

Как подсчитать ячейки с текстом в Excel 365

Чтобы подсчитать ячейки, содержащие текст в электронной таблице, с помощью Excel 365, вы применяете ту же функцию «СЧЁТЕСЛИ», которая используется в Excel для Windows и macOS. Вот как:

Откройте таблицу Excel, которую вы хотите изучить.
Нажмите пустую ячейку, чтобы ввести формулу.
В пустой ячейке введите: « =СЧЁТЕСЛИ (диапазон, критерии) ». Эта формула подсчитывает количество ячеек с текстом в указанном диапазоне ячеек.
В части «диапазон» введите диапазон ячеек, который вы хотите просмотреть. Введите первую и последнюю ячейки, разделенные двоеточием. Например, чтобы подсчитать ячейки с C2 по C11, введите « C2:C1 ».
В поле "критерии" введите или вставьте ""*"" в кавычках. Это подсчитывает количество ячеек (в указанном диапазоне), в которых есть текст. Например, полная формула должна выглядеть примерно так: « =СЧЁТЕСЛИ (C2:C11, «*»)».
Теперь нажмите Enter, чтобы применить только что созданную формулу. Результат отобразится в ячейке формулы.

Как подсчитать ячейки с текстом в Excel в приложении для iPhone

Чтобы подсчитать количество ячеек с текстом в электронной таблице с помощью приложения Excel на iPhone, выполните следующие действия:

Как подсчитать ячейки с текстом в Excel в приложении для Android

Чтобы подсчитать количество ячеек с текстом в электронной таблице с помощью приложения Excel для Android, выполните следующие действия:

Запустите приложение Excel для Android
Откройте таблицу, которую хотите просмотреть, нажав Открыть, чтобы просмотреть сохраненные таблицы, затем нажмите нужный файл, чтобы автоматически открыть его.< бр />
Дважды нажмите на пустой ячейке, чтобы ввести формулу «СЧЁТЕСЛИ». Либо нажмите и удерживайте пустую ячейку, затем нажмите Изменить во всплывающем меню.
В пустой ячейке введите « =СЧЁТЕСЛИ (диапазон, критерии) » без кавычек. Эта формула подсчитывает количество ячеек с текстом внутри диапазона ячеек.
Для части формулы "диапазон" введите диапазон ячеек, который вы хотите подсчитать. Введите первую и последнюю ячейки, разделенные двоеточием. Чтобы подсчитать ячейки с E2 по E12 в одном столбце, введите « E2:E12 ».
Для части формулы "критерии" введите " "*" " с кавычками. Это подсчитывает количество ячеек с текстом в указанном диапазоне, включая более одной строки. Ваша полная формула должна выглядеть примерно так: " =СЧЁТЕСЛИ (A2:E12, "*") ".
Теперь нажмите Ввод, чтобы применить формулу. Результат отображается в ячейке формулы.

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

С помощью функции «СЧЁТЕСЛИ» вы можете подсчитать количество ячеек, содержащих определенные текстовые строки, например «Excel», «Джон» или «Джон Мейерс». Формула аналогична подсчету ячеек, содержащих любой текст, но вы изменяете часть формулы «критерии» для поиска определенного текста. В этом примере вы увидите, сколько раз слово «Excel» появляется в определенном диапазоне ячеек:

Запустите Excel на своем устройстве и откройте "назначенный файл" или просто запустите "назначенный файл", если он настроен на открытие Excel по умолчанию.
Нажмите пустую ячейку, чтобы ввести формулу.
В пустой ячейке введите « =СЧЁТЕСЛИ (диапазон, критерии) » без кавычек.
Для части формулы "диапазон" введите диапазон ячеек, который вы хотите подсчитать. Введите первую и последнюю ячейки, разделенные двоеточием. Чтобы подсчитать ячейки от A2 до A20, введите « A2:A20 » без кавычек.
В разделе формулы "критерии" введите ""Excel"" с кавычками.Это подсчитывает количество ячеек с «Excel» в указанном диапазоне. Ваша формула должна выглядеть примерно так: « =СЧЕТЕСЛИ (A2:A20, «Excel») ».

Как подсчитать ячейки с повторяющимся текстом в Excel

Помимо подсчета ячеек с текстом и определенного текста, вы можете подсчитать количество ячеек с повторяющимся содержимым.

В следующем примере мы ищем повторяющиеся оценки учащихся. Наша таблица настроена следующим образом:

Столбец A – список наших учеников A2:A10.
Столбец B – перечислены оценки каждого учащегося (A, B или C).
Столбец D — список доступных оценок. D2 – "А", D3 – "В" и D4 – "В".
Столбец E — количество баллов по каждой оценке.

Подсчет ячеек с повторяющимся текстом, включая первый экземпляр

Чтобы подсчитать количество ячеек в электронной таблице с экземплярами оценок "A", "B" или "C", включая первый экземпляр, введите следующие формулы:

Теперь у вас есть количество повторяющихся оценок, включая первый экземпляр, указанный в столбце "E".

Подсчет ячеек с повторяющимся текстом, исключая первый экземпляр

Чтобы подсчитать количество ячеек в электронной таблице с экземплярами оценок "A", "B" или "C", исключая первый экземпляр, введите следующие формулы:

Для экземпляров класса "A", за исключением первого экземпляра, щелкните ячейку E2 и введите формулу "=СЧЁТЕСЛИ ($B$2 : $B$10, D2)-1".
Для экземпляров класса «B», за исключением первого экземпляра, щелкните ячейку E3 и введите формулу «=СЧЁТЕСЛИ ($B$2: $B$10, D3)-1».
Для экземпляров категории "C", за исключением первого экземпляра, щелкните ячейку E4 и введите формулу "=СЧЁТЕСЛИ ($B$2: $B$10, D4)-1".

Теперь у вас есть количество повторяющихся оценок, за исключением первого экземпляра, указанного в столбце "E".

Как посчитать ячейки с цветным текстом в Excel

В Excel нет формулы для подсчета ячеек на основе цвета их текста. Чтобы обойти это, отфильтруйте результаты, а затем посчитайте. Вот как:

После того как вы нажмете Ввод, чтобы применить фильтр, Excel отобразит только ячейки, содержащие этот цвет, и скроет остальные значения.

Функция «ПРОМЕЖУТОЧНЫЙ ИТОГ» исключит значения в скрытых строках, поэтому возвращает количество только для выбранного цвета текста.

Поиск ячеек с текстом

Приложение Excel отлично справляется с хранением данных и упрощает их анализ. Он обрабатывает как текст, так и числа.

Его более четырехсот функций, включая функцию «СЧЁТЕСЛИ». Эта функция полезна для определения общего количества ячеек, содержащих определенную информацию, например ячеек с текстом, и количества вхождений определенного текста.

Удалось ли вам выяснить, что вам нужно знать о данных вашей электронной таблицы? Насколько полезным вы считаете Excel в целом? Поделитесь своим мнением в разделе комментариев ниже.

Читайте также: