Evolcom.ru

Бытовая техника
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как подсчитать в Excel ячейки длиной больше определенного числа?

Функция СУММЕСЛИ в Excel с примерами

Мы только что рассмотрели синтаксис функции SUMMESLY в Excel. Давайте проверим наше понимание на нескольких примерах формулы:

СУММЕСЛИ в Excel примеры с логическими операторами (больше, меньше или равно)

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

Примечание Обратите внимание, что в формулах Excel оператор сравнения, за которым следует число или текст, всегда должен быть заключен в инвертированную запятую («»).

Пример формулы СУММЫ

Сумма, на которую намного больше

Добавьте значения больше 5 в ячейки A2:A10.

Количество, если менее

=СУММЕСЛИ(A2:A10; «<10"; B2:B10)

Просуммируйте значения в ячейках B2:B10, если соответствующее значение в столбце A меньше 10.

Эта сумма равна

(невозможно указать)

Добавляет значения в ячейки B2:B10, если соответствующая ячейка в столбце A не равна значению в ячейке D1.

Сумма, если больше или равно

Добавляет значения больше или равно 5 в диапазоне A2:A10.

Сумма, если она меньше или равна

=СУММЕСЛИ(A2:A10; «<=10"; B2:B10)

Добавляет значения в ячейки B2:B10, если соответствующее значение в столбце A меньше или равно 10.

СУММЕСЛИ в Excel примеры с текстовым критерием

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

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

Пример формулы СУММЫ

Сумма, если равна

=СУММЕСЛИ(A2:A8; «бананы»; C2:C8)

Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит точное слово «бананы» и никакие другие слова или символы. Ячейки, содержащие «зеленые бананы», «бананы зеленые» или «бананы!» не будут считаться.

=СУММЕСЛИ(A2:A8; «*бананы*»; C2:C8)

Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит слово «бананы», отдельно или в сочетании с любыми другими словами. Ячейки, содержащие «зеленые бананы» или «бананы зеленые», будут учитываться для суммирования.

Сумма, если не равно

=СУММЕСЛИ(A2:A8; «<>бананы»; C2:C8)

Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит любое значение, отличное от слова «бананы». Если ячейка содержит «бананы» вместе с некоторыми другими словами или символами, такими как «желтые бананы» или «бананы желтые», такие ячейки будут учитываться для суммирования.

Читайте так же:
Как правильно суммировать числа с единицами в диапазоне в Excel?

=СУММЕСЛИ(A2:A8; «<>*бананы*»; C2:C8)

Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A не содержит слова «бананы», отдельно или в сочетании с любыми другими словами. Ячейки, содержащие «желтые бананы» или «бананы желтые», не суммируются.

Подробнее о перекрытии см. в разделе СУММЕСЛИ Примеры формул с подстановочными знаками.

А теперь, давайте посмотрим пример формулы «Сумма, если не равно» в действии. Как показано на изображении ниже, формула суммирует количество всех продуктов, кроме «Банана Дамский пальчик»:

=СУММЕСЛИ(A2:A8; «<>Банан Дамский пальчик»; C2:C8)

3-funktciia-summesli-v-excel

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с проверкой на неравенство

Примечание . Как и большинство других функций Excel, СУММЕСЛИ нечувствительна к регистру, что означает, что «<> бананы», «<> Бананы» и «<> БАНАНЫ» будут давать точно такой же результат.

СУММЕСЛИ в Excel примеры операторов сравнения со ссылками на ячейки

Если вам нужна более универсальная формула Excel, можно заменить числовое или текстовое значение в критериях ссылкой на ячейку, например

В этом случае вам не придется менять формулу СУММЕСЛИ, основанную на другом критерии – вы просто вводите новое значение в ссылочной ячейке.

4-funktciia-summesli-v-excel

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ, суммирование исключая значение в ячейке F1

Примечание. Когда вы используете логическое выражение с ссылкой на ячейку, вы должны использовать двойные кавычки («»), чтобы начать текстовую строку и амперсанд (&), чтобы объединить и завершить строку, например «<>» и F1.

Оператор «равенства» (=) можно не использовать до ссылки на ячейку, поэтому обе приведенные ниже формулы эквивалентны и правильны:

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

Доступны следующие символы подстановки:

Звездочка (*) — представляет собой любое количество знаков

Знак вопроса (?) — обозначает один символ в определенном месте

Пример 1. Суммирование значений на основе перекрытия

Предположим, что вы хотите сложить количества для всех видов бананов. Следующие формулы SUMMESLY будут очень эффективны в таких случаях:

= SUMMESLY(A2:A8; «*бананы*»;C2:C8) — Критерий включает текст, заключенный в звездочках (*).

Читайте так же:
Как показать / отобразить вкладку разработчика в ленте Excel?

=СУММЕСЛИ(A2:A8; «*»&F1&»*»; C2:C8) — критерий включает ссылку на ячейку, заключенную в звездочки, обратите внимание на использование амперсанда (&) до и после ссылки на ячейку для конкатенации строки.

5-funktciia-summesli-v-excel

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с подстановочными знаками для суммирования по частичному совпадению

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

=СУММЕСЛИ(A2:A8; «бананы*»; C2:C8) — значения суммы в C2:C8, если соответствующая ячейка в столбце A начинается со слова «бананы».

=СУММЕСЛИ(A2:A8; «*бананы»; C2:C8) — значения суммы в C2:C8, если соответствующая ячейка в столбце A заканчивается словом «бананы».

6-funktciia-summesli-v-excel

Функция СУММЕСЛИ в Excel с примерами – Пример использования функции СУММЕСЛИ с текстовым условием

Пример 2: Суммирование по заданному количеству символов

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

7-funktciia-summesli-v-excel

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с условием суммирования, если длина текстовой строки в шесть букв

Пример 3: Сумма ячеек, соответствующих значениям текста

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

=СУММЕСЛИ(A2:A8; «?*»; C2:C8) – суммирует значения из ячеек C2:C8, если соответствующая ячейка в столбце A содержит не менее 1 символа.

=СУММЕСЛИ(A2:A8; «*»; C2:C8) – учитывает пустые ячейки, содержащие строки нулевой длины, возвращаемые некоторыми другими формулами, например =»».

Обе формулы игнорируют такие переменные, как ошибки, булевы значения, цифры и даты.

Пример 4: Использование * или ? как общих символов

Если вы хотите использовать * или ? для обработки в функции СУММЕСЛИ в качестве буквы, а не символа подстановки, используйте тильду (

). Например, приведенная ниже формула СУММЕСЛИ суммирует значения в ячейках C2:C8, если ячейка в столбце A той же строки содержит знак вопроса:

8-funktciia-summesli-v-excel

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

СУММЕСЛИ в Excel примеры с датами

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

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

Читайте так же:
Как очистить ячейки, если они кажутся пустыми, но их нет в Excel?

Пример формулы СУММЕСЛИ

Сумма за конкретную дату

Добавьте значения в ячейках C2:C9, если соответствующая дата в столбце B равна 29.10.2017.

Сумма, если дата больше или равна дате, заданной в формуле

В столбце B, если соответствующая дата больше или равна 29.10.2017, значения в ячейках C2-9 складываются.

Импортировать, если дата больше, чем дата, введенная в ячейке.

Складывает значения в ячейках С2:С9, если соответствующая дата в столбце В больше, чем дата в ячейке F1.

Чтобы добавить значения на основе текущей даты, используйте функцию SUMMESLY в сочетании с TODAY(), как показано далее:

Пример формулы СУММЫ

Сумма значений, для текущей даты

=СУММЕСЛИ(B2:B9; СЕГОДНЯ (); C2:C9)

Сумма значений меньше текущей даты, то есть до сегодняшнего дня.

=СУММЕСЛИ(B2:B9; «<"&СЕГОДНЯ(); C2:C9)

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

=СУММЕСЛИ(B2:B9; «>»& СЕГОДНЯ(); C2:C9)

Сумма значений за неделю от текущей даты. (т.е. сегодня + 7 дней).

=СУММЕСЛИ(B2:B9; » Пример функции СУММЕСЛИ с суммированием количества продуктов, которые будут отправлены через неделю — Функция СУММЕСЛИ в Excel» src=»https://naprimerax.org/images/751/primer-funktcii-summesli-s-summirovaniem-kolichestva-produktov-kotorye-budut-otpravleny-cherez-nedeliu-funktciia-summesli-v-excel.png» alt=»9-funktciia-summesli-v-excel» />

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с суммированием количества продуктов, которые будут отправлены через неделю

СУММЕСЛИ в заданном диапазоне дат

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

=СУММЕСЛИ(B2:B9; «>=01.11.2017»; C2:C9) — СУММЕСЛИ(B2:B9; «>=01.12.2017»; C2:C9)

Эта формула добавляет значения в ячейки C2:C9, если дата в столбце B находится между 1 ноября 2017 года и 30 ноября 2017 года, включительно.

10-funktciia-summesli-v-excel

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ дата в диапазоне

На первый взгляд это может показаться немного сложным, но при ближайшем рассмотрении все становится намного проще. Сначала СУММИРУЕМ все ячейки в C2:C9, где соответствующая ячейка больше или равна начальной дате (в данном примере 1 ноября). Используя вторую функцию СУММЕСЛИ, следует выполнить вычитание для всех значений, которые выпадают после конечной даты (30 ноября).

Читайте так же:
Как показать все окна Excel на панели задач?

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

Описание формул для подсчета случаев появления текста, символов и слов в Excel

Приложения Microsoft 365 для предприятий заменили Office 365 ProPlus. Подробнее об этом изменении читайте в этом блоге.

Аннотация

В этой статье приведены и описаны формулы для расчета

  • Количество вхождений текстовой строки в диапазон ячеек.
  • Количество вхождений символа в диапазон ячеек.
  • Количество вхождений данного символа в диапазон ячеек
  • Количество слов (или текстовых строк), разделенных символом в ячейке.

Дополнительная информация

Формула для подсчета количества вхождений текстовой строки в диапазоне

=SUM(LEN(range)-LEN(SUBSTITUTE(range,"text","")))/LEN("text")

Где диапазон — диапазон ячеек, а "text" заменяется конкретной текстовой строкой, которую вы хотите посчитать.

Приведенная выше формула должна быть введена как матричная формула. Чтобы ввести формулу в виде матрицы в Excel для Windows, нажмите CTRL+SHIFT+ENTER. Чтобы ввести формулу в виде матрицы в Excel для Macintosh, нажмите COMMAND+ENTER BACK.

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

Пример 1. Подсчет количества вхождений текстовой строки в диапазоне

Запустить Excel и открыть новую рабочую книгу

На листе 1 должна быть представлена следующая информация:

Значение ячейки A8 составляет 4, потому что текст "яблоко" появляется четыре раза в диапазоне.

Формула для подсчета количества происшествий одного символа в одной ячейке

=LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,"a",""))

Где cell_ref ссылка на ячейку, и "а" заменяется символом, который вы хотите посчитать.

Эту формулу не следует приводить в качестве формулы массы.

Пример 2. Подсчет количества появлений символа в одной ячейке.

Используйте те же данные из предыдущего примера; предполагая, что вы хотите подсчитать количество появлений символа "p" в A7. Введите следующую формулу в ячейку A9:

Читайте так же:
Как преобразовать дату в формат гггг-мм-дд в Excel?

Значение ячейки A9 составляет 3, потому что символ "p" появляется три раза в A7.

Формула для подсчета количества появлений одного символа в диапазоне

=SUM(LEN(range)-LEN(SUBSTITUTE(range,"a","")))

Где range — это диапазон нужной ячейки, а "а" заменяется символом, который вы хотите сосчитать.

Приведенная выше формула должна быть введена как формула таблицы. Чтобы ввести формулу как матричную формулу в Excel, нажмите CTRL+SHIFT+ENTER.

Пример 3. Подсчет количества появлений символа в диапазоне

Используйте те же данные из предыдущего примера; предполагая, что вы хотите подсчитать количество символа "p" в A2:A7. В ячейку А10 введите следующую формулу:

Приведенная выше формула должна быть введена как формула массива. Чтобы ввести формулу в виде формулы массива в Excel, нажмите CTRL+SHIFT+ENTER.

Значение ячейки A10 составляет 11, потому что символ "p" появляется 11 раз в A2:A7.

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

=IF(LEN(TRIM(cell_ref))=0,0,LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,char,""))+1)

Где cell_ref — это ссылка на ячейку, а char — это символ, разделяющий слова.

Формула выше не содержит пробелов; несколько строк используются только для того, чтобы поместить формулу в этот документ. При вводе формулы в ячейку не добавляйте пробелы. Эту формулу не следует вводить как массивную формулу.

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

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

Запустить Excel, а затем открыть новую книгу

Лист 1 должен содержать следующие данные:

Формула в ячейке A2 возвращает значение 4, чтобы отразить, что строка содержит четыре слова, разделенные пробелами. Пробелы не имеют значения, если слова разделены более чем одним пробелом, или если слова начинаются или заканчиваются пробелами. Функция TRIM удаляет лишние пробелы и пробелы в начале и конце в тексте ячейки.

В Excel также можно использовать макрос для подсчета количества раз появления определенного символа в ячейке или в диапазоне ячеек.

Ссылки

Дополнительные сведения о количестве символов см. в следующей статье базы знаний Microsoft:

89794 Как использовать Visual Basic для приложений для подсчета вхождений символов в выделениях Excel

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector