Знак не равно в excel в формуле: Операторы в Excel — НА ПРИМЕРАХ

Операторы в Excel — НА ПРИМЕРАХ

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

Арифметические операторы в Excel

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

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

Рассмотрим примеры использования арифметических операторов.

Операторы в Excel – Примеры использования арифметических операторов

Логические операторы в Excel

Логический оператор используется в Excel для сравнения двух значений. Логические операторы иногда называют булевыми операторами, потому что результат сравнения в любом случае может быть только ИСТИНА или ЛОЖЬ.

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

На изображении ниже показаны результаты, возвращаемые с помощью логических операторов, таких как Равно, Не равно, Больше и Меньше.

Операторы в Excel – Использование операторов Равно, Не равно, Больше и Меньше

Операторы ссылок в Excel

Операторы ссылок используются для задания ссылок на диапазоны ячеек.

Рассмотрим наглядные примеры с использованием операторов ссылок с функцией СУММ:

Операторы в Excel – Примеры использования операторов ссылок

Текстовый оператор в Excel

В Microsoft Excel существует один текстовый оператор. И это – амперсанд (&), который используется для объединения текстовых строк.

Операторы в Excel – Пример использования текстового оператора

Больше примеров с объединением текста, вы можете найти в статье Объединить текст в Excel.

Формула вычитания в excel: примеры решений

Microsoft Office Excel благодаря мощному встроенному вычислительному механизму позволяет проводить не только простейшие математические операции типа сложения, вычитания, умножения и деления, но и более сложные расчеты с большим количеством слагаемых. Сегодня детально разберемся, как работает формула вычитания в excel.

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

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

Примеры использования

Пример 1. Необходимо найти разность трех различных чисел.

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

• 1. Ставите равно в строке формул и пишете числа через знак минус, который можно найти на цифровом блоке клавиатуры.

• 2. Нажимаете Enter и получаете результат расчета.

Пример 2. Нужно посчитать разность нескольких ячеек.

1. Заполняете цифрами таблицу.

2. Устанавливаете курсор в любом удобном месте и записываете формулу через знак равно со ссылками на заполненные ячейки.

3. Нажимаете ввод на клавиатуре и получаете результат. Поскольку числа разные, то ответ вышел отрицательным.

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

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

Пример 3. Вычитание столбцов.

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

2. В столбце прибыль записываете формулу и нажимаете Enter.

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

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

Пример 4. Вычитание из столбца фиксированного числа.

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

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

3. Применяете маркер автозаполнения. Фиксация одного значения позволяет не заполнять остальные строки столбца Налоги.

Бонус! Пример использования СУММ

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

Правила ввода формул в Excel

<<Назад 
| 
Содержание 
|  Далее>>

1. Убедитесь в том, что активна (выделена
курсивной рамкой) та ячейка, в которой вы хотите получить
результат вычислений.

2. Ввод формулы начинается со знака “=”.
Этот знак вводится с клавиатуры.

3. После ввода знака “=” Excel переходит
в режим ввода формулы. В этом режиме, при выделении какой-либо
ячейки, ее адрес автоматически заносится в формулу. Это
позволяет избавить пользователя от необходимости знать адреса
ячеек и вводить их в формулу с клавиатуры.

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

§       
Знаки операций должны вводиться между адресами
ячеек.

§       
Удобнее вводить знаки операций с правого цифрового
блока клавиатуры. Чтобы этот блок работал в нужном режиме,
индикатор <Nun Lock> должен быть включен.

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

§       
<Enter> завершает ввод формулы, и переводит курсор
в следующую ячейку.

§       
“Зеленая галочка” на панели ввода формулы
завершает ввод формулы, и оставляют курсор в той же ячейке.

Например, если в ячейке
D2 должна помещаться разность чисел из
ячеек B2 и C2,
то после установки курсора на D5
следует указать мышью на B2, ввести с
клавиатуры знак “-”, указать мышью на C2
и нажать <Enter> или “зеленую галочку”. 2

=А8*С6

=D4/N5

=B2+5

=9-G6

Продолжение таблицы 18.

Меньше

Больше

Меньше или равно

Больше или равно

Не равно

Диапазон

Объединение диапазонов

Максимум

Минимум

< 

> 

<=

>=

<> 

:

;

МАКС

МИН

=СУММ(А1:С10)

=СУММ(А1;А2;А6)

=МАКС(А3:С5)

=МИН(Е2:Р7)

<<Назад 
| 
Содержание 
|  Далее>>

Символ не равно в excel

Написание знака «не равно»

Прежде всего, нужно сказать, что в Экселе существует два знака «не равно»: «<>» и «≠». Первый из них используется для вычислений, а второй исключительно для графического отображения.

Символ «<>»

Элемент «<>» применяется в логических формулах Эксель, когда нужно показать неравенство аргументов. Впрочем, его можно применять и для визуального обозначения, так как он все больше входит в обиход.

Наверное, уже многие поняли, что для того, чтобы набрать символ «<>», нужно сразу набрать на клавиатуре знак «меньше» ( ). В итоге получается вот такая надпись: «<>».

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

1. Выделяем ячейку, куда должен быть вписан знак. Переходим во вкладку «Вставка». На ленте в блоке инструментов «Символы» жмем на кнопку с наименованием «Символ».

Таким образом, наша задача полностью выполнена.

Символ «≠»

Знак «≠» используется исключительно в визуальных целях. Для формул и других вычислений в Excel его применять нельзя, так как приложение не распознаёт его, как оператора математических действий.

В отличие от символа «<>» набрать знак «≠» можно только при помощи кнопки на ленте.

Кликаем по ячейке, в которую планируется вставить элемент. Переходим во вкладку «Вставка». Жмем на уже знакомую нам кнопку «Символ».

Как видим, элемент «≠» в поле ячейки вставлен успешно.

Мы выяснили, что в Эксель существует два вида символов «не равно». Один из них состоит из знаков «меньше» и «больше», и используется для вычислений. Второй (≠) – самодостаточный элемент, но его использование ограничивается только визуальным обозначением неравенства.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Проверка истинности условий и логических сравнений между выражениями, которые являются общими для многих задач. Для создания условных формул можно использовать функции » и», «,» и » Если «.

Например, функция Если использует указанные ниже аргументы.

Формула, использующая функцию если

лог_выражение: условие, которое нужно проверить.

значение_если_истина: возвращаемое значение, если условие истинно.

значение_если_ложь: возвращаемое значение, если условие имеет значение false.

Дополнительные сведения о том, как создавать формулы, можно найти в разделе Создание и удаление формул.

В этой статье

Создание условной формулы, которая приводит к логическому значению (истина или ложь)

Для выполнения этой задачи используйте функции и операторы and, orи Not , как показано в следующем примере.

Пример

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Выделите пример, приведенный в этой статье.

Важно: Не выделяйте заголовки строк или столбцов.

Выделение примера в справке

Нажмите клавиши CTRL+C.

В Excel создайте пустую книгу или лист.

Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.

Важно: Чтобы пример правильно работал, его нужно вставить в ячейку A1.

Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показывать формулы.

Скопировав пример на пустой лист, вы можете настроить его так, как вам нужно.

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

Поставим в экселе знак больше или равно.

Перед нами таблица, в которой есть числа от 1 до 10. В рядом стоящем столбце, нужно прописать функцию «ЕСЛИ», с условием, что если цифра больше или равно трем ставиться «Да», а если условие не выполняется, то ставиться «Нет».

В ячейке «В2» пропишем формулу: =ЕСЛИ(A2>=3;»Да»;»Нет»), где знак >= означает больше или равно. Копируем данную формулу на все ячейки.

Поставим в экселе знак меньше или равно.

Поменяем задачу, теперь поставим «Да», если числом меньше или равно трем, а при невыполнении условия ставим слово «Нет». Для этого в ячейке «В2» пишем формулу =ЕСЛИ(A2 3;»Нет»;»Да»), где знаком <> мы обозначаем неровно трем.

Excel показывает формулу, но не результат

Вы ввели формулу, но Excel не вычисляет результат?

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

Формула ВПР верна, почему нет результата?

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

Есть две основные причины, по которым вы можете увидеть формулу вместо результата:

1. Вы случайно включили Показать формулы
2. Excel считает, что ваша формула — это текст

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

Показать формулы включен

В

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

Показать формулы отключено (нормальный режим)

Показать формулы включены

Чтобы проверить, включен ли параметр «Показать формулы», перейдите на вкладку «Формула» на ленте и нажмите кнопку «Показать формулы»:

Показать формулы включены — просто нажмите, чтобы отключить

Причина, по которой «Показать формулы» может быть случайно включена, заключается в том, что в ней есть сочетание клавиш (Control `), которое пользователь может напечатать по незнанию.Попробуйте Control `на листе, чтобы увидеть, как это работает. Вы увидите, что можете быстро включать и выключать все формулы.

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

Excel считает, что ваша формула — это текст

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

Знак равенства отсутствует

Во-первых, возможно, вы забыли знак равенства. Все формулы в Excel должны начинаться со знака равенства (=). Если вы не укажете этот параметр, Excel просто обработает формулу как текст:

Пример сломанной формулы — нет знака равенства (=)

Пробел перед знаком равенства

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

Формула в кавычках

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

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

Примечание. Вы можете использовать кавычки внутри формул .В этом случае формула выше требует кавычек вокруг критериев.

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

Для справки рабочая формула:

Формат ячейки — текст

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

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

Совет — Сохраните формулу как текст

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

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

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

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

Рисунок 1.Пример подсчета ячеек, не равных в Excel

Общая формула

= СЧЁТЕСЛИ (диапазон; <> значение)

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

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

Во-первых, СЧЁТЕСЛИ подсчитывает ячейки в диапазоне, который удовлетворяет предоставленному нами условию.Мы используем not equal to operator (<>) для подсчета ячеек в диапазоне, который не равен этому значению. Это возвращает количество ячеек, отличных от этого значения.

Настройка данных

В следующем примере используется набор данных информации о проекте. В столбцах A и B указаны названия и статус проектов. Статусы — «завершено», «продолжается» и «остановлено».

Рис. 2. Пример набора данных

Для подсчета ячеек, кроме заполненных, нам необходимо:

• Перейти в ячейку E4 .
• Присвойте формулу = СЧЁТЕСЛИ ($ B $ 2: $ B $ 8, «<> Завершено») для E4.
• Нажмите Введите .

Рисунок 3. Применение формулы

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

Банкноты

1. СЧЁТЕСЛИ без учета регистра . Мы можем искать любую комбинацию прописных и строчных букв.
2. Мы можем использовать значение из ячейки как часть критерия. Для этого нам нужно использовать символ амперсанда (&). Чтобы решить предыдущий пример для проекта, который не остановлен, нам нужно присвоить формуле = СЧЁТЕСЛИ (B2: B8, «<>» & B4) для E5.

Рис. 4. Число ячеек, не равных использованию ссылок на ячейки

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

В большинстве случаев задача, которую вам нужно будет решить, будет более сложной, чем простое применение формулы или функции.Если вы хотите сэкономить часы исследований и разочарований, попробуйте нашу живую службу Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любые ваши вопросы по Excel. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

Обнаружение ошибок в формулах — служба поддержки Office

[Исправлено] Формула Excel не показывает результат

Формулы в Excel используются для автоматизированных математических операций с данными. Текст, начинающийся с «=», считается формулой в excel.

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

В этом сообщении блога мы собрали возможные причины и решения для решения проблемы формул Excel.

Почему Excel
формулы не дают результатов?

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

1. Вы случайно включили «Показать формулы» в Excel.
2. Excel считает, что ваша формула является текстом (синтаксис не соблюдается)

Сценарии проблем и решения

Часто причины коррупции по формуле Excel подпадают под указанные выше категории.Вот различные сценарии и методы восстановления:

Показать формулы включен

Если формулы Excel повреждены, проверьте, не включили ли вы случайно или намеренно функцию «Показать формулу» в Excel. Эта функция, вместо того, чтобы применять вычисления и затем отображать результаты, отображает фактический текст, написанный вами. Его можно использовать для быстрого просмотра всех формул, но если вы не знаете об этой функции и случайно включили ее, это может стать головной болью. Чтобы отключить этот режим, перейдите к « Формулы, » и нажмите « Показать формулу, включенную ». Если он был ранее включен, он будет отключен, просто нажав на него.

Формат ячейки — текст

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

Функция автоматических расчетов

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

Чтобы исправить это, сначала нажмите «Файл» в верхнем левом углу экрана, затем нажмите «Параметры» в левой строке меню. В результате на экране появится всплывающее окно. Выберите «Формулы» в меню слева, а затем измените «Параметры расчета» на «Автоматически», если в настоящее время установлено «Вручную», и нажмите «ОК».Это перенаправит вас на свой лист и начнет выполнять вычисления вручную.

Excel рассматривает формулу как текст

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

• Знак равенства отсутствует: Каждая формула в Excel должна начинаться со знака равенства (=). Если вы его пропустите, Excel ошибочно примет вашу формулу за обычный текст
• Пробел перед знаком равенства: Пробел перед знаком равенства вводить нельзя. Возможно, нам будет сложно обнаружить единичный пробел, но для Excel он нарушает правила написания формул.
• Формула, заключенная в кавычки: Убедитесь, что ваша формула не заключена в кавычки. Обычно люди делают эту ошибку, записывая формулу в кавычках, но в Excel кавычки используются для обозначения текста.Значит, ваша формула не будет оцениваться. Но при необходимости вы можете добавить кавычки внутри формулы, например: = СУММЕСЛИМН (F5: F9, G5: G9, ”> 30 ″)
• Соответствует всем скобкам в формуле: Аргументы функций Excel вводятся в скобках. В сложных случаях вам может потребоваться ввести несколько скобок. Если эти круглые скобки не соединены / закрыты должным образом, Excel не сможет вычислить введенную формулу.
• Предел вложенности: Если вы вкладываете две или более функций Excel друг в друга, например, используя вложенный цикл IF, помните следующие правила:
  • Excel 2019, 2016, Excel 2013, Excel 2010 и Excel 2007 позволяют использовать только до 64 вложенных функций.
  • В Excel 2003 и ниже допускает до 7 вложенных функций

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

Что делать, если ручные решения не работают?

Если вы пробовали ручные решения, упомянутые выше, но все еще не можете заставить Excel отображать результаты формул, вы можете попробовать восстановить файл Excel с помощью программного обеспечения для автоматического восстановления Excel, Stellar Repair for Excel заявленной инструмент, обеспечивающий 100% целостность и точность .

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

Заключение:

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

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

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

Как считать в функциях Excel

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

Обзор

: 7 способов
Подсчитайте в Excel

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

Загрузите 7 способов
to Count образец учебной книги, чтобы вы могли следить за видео.

Подсчет чисел, всех данных или пустых ячеек

В этом видео показаны функции СЧЁТ, СЧЁТ,
и функцию СЧИТАТЬПУСТОТЫ, и предупреждает об особенностях подсчета некоторых
типы ячеек.

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

Подсчет ячеек с числами — COUNT

Функция СЧЁТ будет подсчитывать ячейки, содержащие числа. Его синтаксис
это:
= СЧЁТ (значение1 , значение2, … значение30).
Аргументы (например, значение1) могут быть ссылками на ячейки или введенными значениями.
в формулу COUNT.

В следующем примере функции COUNT используется один аргумент — ссылка
в ячейки A1: A5.

1. Введите данные образца в свой рабочий лист
2. В ячейке A7 введите формулу СЧЁТ, чтобы подсчитать числа в столбце.
   A: = СЧЁТ (A1: A5)
3. Нажмите клавишу Enter, чтобы заполнить формулу.
4. Результатом будет 3 — количество ячеек, содержащих числа.
   
   Ячейка A1 не учитывается, поскольку она содержит текст.

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

Подсчет ячеек с данными — COUNTA

Функция COUNTA подсчитывает непустые ячейки. (См. Примечание к пустым ячейкам ниже)

Его синтаксис
это:
= COUNTA (значение1 , значение2, … значение30).
Аргументы (например, значение1) могут быть ссылками на ячейки или введенными значениями.
в формулу. В следующем примере используется один аргумент — ссылка
в ячейки A1: A5.

1. Введите данные образца в свой рабочий лист
2. В ячейке A7 введите формулу COUNTA, чтобы подсчитать числа в столбце.
   A: = COUNTA (A1: A5)
3. Нажмите клавишу Enter, чтобы заполнить формулу.
4. Результатом будет 4 — количество ячеек, содержащих данные.

Подсчет пустых ячеек — СЧИТАТЬПУСТОТЫ

Функция СЧИТАТЬПУСТОТЫ подсчитывает пустые ячейки. (См. Примечание к пустым ячейкам ниже)

Его синтаксис
это:
= СЧИТАТЬПУСТОТЫ (диапазон ).
В следующем примере используется ссылка на ячейки A1: A5.

1. Введите данные образца в свой рабочий лист
2. В ячейке A7 введите формулу СЧИТАТЬПУСТОТЫ, чтобы подсчитать числа в
   столбец A: = СЧИТАТЬПУСТОТЫ (A1: A5)
3. Нажмите клавишу Enter, чтобы заполнить формулу.
4. Результатом будет 1, количество пустых ячеек.

Ячейки, которые выглядят пустыми

И COUNTA, и COUNTBLANK будут подсчитывать ячейки с формулами, которые выглядят пустыми, если результатом формулы является пустая строка {«»). Пустая строка рассматривается как текст и учитывается.

Например, = ЕСЛИ (B2 = «», «», B2).

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

• ПРИМЕЧАНИЕ. Вы можете видеть апострофы в этих «непустых» преобразованных ячейках,
  если вы добавите галочку в «Параметры Excel», «Дополнительно», «Совместимость с Lotus», «Переход
  клавиши навигации «

Чтобы исправить ячейки, которые выглядят пустыми, но подсчитываются, см. Мой блог Contextures: Исправить пустые ячейки Excel, скопированные из базы данных ▲ TOP

Подсчитать ячейки,
соответствует одному критерию

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

Письменные инструкции под видео: Матч
критерий точно и критерий соответствия в
строка

Точное соответствие одному критерию — COUNTIF

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

1. Выберите ячейку, в которой вы хотите увидеть счетчик (ячейка A12 в
   этот пример)
2. Введите знак равенства (=), чтобы начать формулу
3. Тип: СЧЁТЕСЛИ (
4. Выберите ячейки, содержащие значения для проверки критерия.
   В этом примере будут проверяться ячейки A1: A10
5. Введите запятую для разделения аргументов
6. Введите критерий.В этом примере вы проверяете текст, поэтому
   введите слово в двойных кавычках: «Pen»
   Примечание: верхний и нижний регистр обрабатываются одинаково
7. Введите закрывающую скобку
   Заполненная формула: = СЧЁТЕСЛИ (A1: A10, «Перо»)
8. Нажмите клавишу Enter, чтобы завершить ввод
9. Результатом будет 4, количество ячеек, содержащих «Pen» ▲ TOP

Критерий соответствия в строке

В Excel подсчитайте ячейки, содержащие критерий, как часть
содержание.В этом примере все заказы Pen, Gel Pen и Pencil будут
считаться, потому что они содержат строку «ручка».

1. Выберите ячейку, в которой вы хотите увидеть счетчик (ячейка A12 в
   этот пример)
2. Введите знак равенства (=), чтобы начать формулу
3. Тип: СЧЁТЕСЛИ (
4. Выберите ячейки, содержащие значения для проверки критерия.
   В этом примере будут проверяться ячейки A1: A10
5. Введите запятую для разделения аргументов
6. Введите критерий.В этом примере вы проверяете текст, поэтому
   введите слово в двойные кавычки с одной или несколькими подстановочными знаками звездочки (*)
   символы: «* Pen *»
   Примечание: верхний и нижний регистр обрабатываются одинаково
7. Введите закрывающую скобку
   Заполненная формула: = СЧЁТЕСЛИ (A1: A10, «* Pen *»)
8. Нажмите клавишу Enter, чтобы завершить ввод
9. Результатом будет 6, количество ячеек, содержащих строку,
   «Ручка» ▲ ТОП

Примечание : вместо того, чтобы вводить критерий в формуле, вы можете
относятся к ячейке.Например, формула на шаге 7 выше может быть
изменено на:
= СЧЁТЕСЛИ (A1: A10, «*» & B12 &
«*»)
, если в ячейке B12 был текст — pen .

Предупреждения COUNTIF и COUNTIFS

При использовании функций СЧЁТЕСЛИ и СЧЁТЕСЛИ следует соблюдать осторожность.

Ложные дубликаты с COUNTIF

Будьте осторожны при использовании СЧЁТЕСЛИ, потому что он соответствует числам с текстом
выглядит как числа и может привести к ложным подсчетам.

Например, если у вас есть текстовая запись — «00123» — это будет
засчитываться как дубликат для номера — 123.

= СЧЁТЕСЛИ ($ B $ 2: $ B $ 10, B2))> 1

Если ваши данные могут содержать подобные записи, используйте SUMPRODUCT вместо
из COUNTIF.

= СУММПРОИЗВ (- ($ B $ 2: $ B $ 10 = B2))> 1

Максимальное количество символов: 255

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

В строке 5 есть #VALUE! ошибка, потому что текст в ячейке C1 превышает 255 символов.

Использование другой функции

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

• = СУММПРОИЗВ (- ([Item] = [@ Item]))

Вот как работает эта формула СУММПРОИЗВ:

• Проверяет каждую строку [Item] , чтобы узнать, равна ли она записи в текущей строке [@Item]
• Результат — ИСТИНА или ЛОЖЬ для каждой строки (выделено на снимке экрана ниже — я нажал F9, чтобы оценить этот раздел формулы)
• Два знака минус (двойной унарный) изменяют ИСТИНА на 1 и ЛОЖЬ на 0
• СУММПРОИЗВ складывает единицы и нули.

Обходное решение Microsoft не работает

На странице Microsoft COUNTIF говорится, что вы можете обойти ограничение в 255 символов, объединив две длинные строки с помощью оператора конкатенации (&). Вот пример:

• = СЧЁТЕСЛИ (A2: A5, «длинная строка» & «другая длинная строка»)

Это предложение НЕ работает для меня!

Подсчитайте числа в числах

Функция СЧЁТЕСЛИ может находить определенные буквы или цифры в текстовой строке.Однако СЧЁТЕСЛИ не может найти конкретное число в действительном числе.

На снимке экрана ниже в списке 4 элемента, содержащих цифру «1». Формула СЧЁТЕСЛИ в ячейке A9 дает неверный результат 3. Она не учитывает «1» в ячейке A2, поскольку эта ячейка содержит действительное число, а не текстовую строку.

Используйте НАЙТИ или ПОИСК

Хотя функция СЧЁТЕСЛИ не может считать конкретные числа в пределах действительных чисел, функция НАЙТИ или ПОИСК найдет их.

На снимке экрана ниже в столбцах C, D и E используются следующие формулы для поиска «1».

Функция ISNUMBER возвращает True или False, а два знака минус (двойной унарный) преобразуют True в 1 и False в 0 (ноль).

• C2: = СЧЁТЕСЛИ (A2; «* 1 *»)
• D2: = — ISNUMBER (НАЙТИ (1; A2))
• E2: = — ISNUMBER (ПОИСК (1; A2))

Формулы НАЙТИ и ПОИСК нашли все 4 элемента, которые содержат «1».Формула СЧЁТЕСЛИ нашла только 3 элемента.

Используйте SUMPRODUCT с FIND или SEARCH

Вместо того, чтобы проверять каждую строку по отдельности, используйте функцию СУММПРОИЗВ с НАЙТИ или ПОИСК, чтобы получить общее количество для списка.

• Используйте FIND для подсчета чисел или с учетом регистра букв
• Используйте SEARCH для подсчета чисел или букв верхнего и нижнего регистра ( без учета регистра )

Пример 1: Числа

В этом примере формулы дают количество ячеек, содержащих 1.Результат — 4 в обоих случаях.

• A9: = СУММПРОИЗВ (- ISNUMBER (НАЙТИ (1, $ A $ 2: $ A $ 7)))
• A10: = СУММПРОИЗВ (- ISNUMBER (ПОИСК (1, $ A $ 2: $ A $ 7)))

Пример 2: Буквы

В следующем примере формулы дают количество ячеек, содержащих «a». FIND считает только строчную букву «a» (1), а SEARCH считает как заглавную «A», так и строчную «a» (2).

• A9: = СУММПРОИЗВ (- ISNUMBER (НАЙТИ («a», $ A $ 2: $ A $ 7)))
• A10: = СУММПРОИЗВ (- ISNUMBER (ПОИСК («a», $ A $ 2: $ A $ 7)))

Подсчет ячеек «Меньше» или «Больше»
Чем «

Из этого видео вы узнаете, как использовать
функция СЧЁТЕСЛИ для подсчета количества элементов в списке, которые
больше или меньше определенной суммы.Письменная инструкция под видео:

Подсчет ячеек больше или равных

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

1. Выберите ячейку, в которой вы хотите увидеть счетчик (ячейка A12 в
   этот пример)
2. Введите знак равенства (=), чтобы начать формулу
3. Тип: СЧЁТЕСЛИ (
4. Выберите ячейки, содержащие значения для проверки критерия.В этом примере будут проверяться ячейки B1: B10
5. Введите запятую для разделения аргументов
6. Введите критерий. В этом примере вы проверяете строки, в которых
   количество больше или равно 10. Оператор> =
   используется перед числом, а весь критерий заключен в двойную
   цитаты: «> = 10»
   Примечание. Несмотря на то, что это числовой критерий, он должен быть заключен в
   в двойных кавычках.
7. Тип закрывающей скобки
8. Заполненная формула:
   = СЧЁТЕСЛИ (B1: B10, «> = 10»)
9. Нажмите клавишу Enter, чтобы завершить ввод

Примечание : вместо того, чтобы вводить критерий в формуле, вы можете
относятся к ячейке. Например, формула на шаге 8 выше может быть
изменено на:
= СЧЁТЕСЛИ (B1: B10, «> =» & B12)
, если в ячейке B12 содержится число — 10 .

Или вы можете использовать функцию как часть критерия. Например:
= СЧЁТЕСЛИ (A1: A10, «<» & СЕГОДНЯ ()) ▲ TOP

Подсчет ячеек от 5 до 10

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

1. Выберите ячейку, в которой вы хотите увидеть счетчик (ячейка A12 в
   этот пример)
2. Введите формулу для подсчета строк больше или равных 5:
   = СЧЁТЕСЛИ (B1: B10, «> = 5»)
3. Введите знак минуса
4. Введите формулу для подсчета строк больше 10:
   СЧЁТЕСЛИ (B1: B10, «> 10»)
5. Заполненная формула:
   = СЧЁТЕСЛИ (B1: B10, «> = 5») — СЧЁТЕСЛИ (B1: B10, «> 10»)
6. Нажмите клавишу Enter, чтобы завершить ввод

Примечание : вместо того, чтобы вводить критерий в формуле, вы можете
относятся к ячейке.Например, формула на шаге 8 выше может быть
изменено на:
= СЧЁТЕСЛИ (B1: B10, «> =» & B12)
–
СЧЕТЕСЛИ (B1: B10, «>» & C12)
, если ячейка B12 содержит число — 5 и ячейка C12
содержал номер — 10 . ▲ ТОП

Изменить оператор для функции СЧЁТЕСЛИ

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

В этом видео показаны шаги по настройке формулы, а письменные инструкции приведены под видео.

Создание раскрывающегося списка операторов

Для создания раскрывающегося списка операторов:

1. На другом листе книги введите список операторов в
   столбец.
2. Выберите ячейки в списке и назовите
   этот список как OpList.

3. На основном листе выберите ячейку, в которой вы хотите раскрыть
   список — ячейка E6 в этом примере
4. Используйте команду проверки данных для создания раскрывающегося списка на основе
   на названном диапазоне — OpList

Используйте раскрывающийся список

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

= СЧЁТЕСЛИ (B2: B11, E6 и F6)

Затем выберите один из операторов из раскрывающегося списка в ячейке E6 и формулу
результат изменится.

Подсчет ячеек, соответствующих нескольким критериям

В этом видео показано, как использовать функцию СЧЁТЕСЛИМН для подсчета
ячейки на основе нескольких критериев.

Письменная инструкция под видео:

—Считать несколько критериев с помощью СЧЁТЕСЛИМН

—Подсчитайте несколько критериев с помощью SUMPRODUCT

—Считать все даты в конкретном месяце и году

Подсчет ячеек, соответствующих нескольким критериям

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

1. Выберите ячейку, в которой вы хотите увидеть итог
2. Введите знак равенства (=), чтобы начать формулу
3. Тип: СЧЁТЕСЛИМН (
4. Выберите ячейки, содержащие значения, которые нужно проверить в первую очередь.
   критерий. В этом примере будут проверяться ячейки A2: A10
5. Введите запятую и первый критерий: «Pen»
   Примечание. Поскольку это текстовый критерий, он заключен в двойные
   кавычки.
6. Чтобы начать следующий набор критериев, введите запятую
7. Выберите ячейки, содержащие значения для проверки второго
   критерий. В этом примере будут проверяться ячейки B2: B10
8. Введите запятую и второй критерий: «> = 10 »
   Примечание. Поскольку этот критерий включает операторы, он заключен в скобки.
   в двойных кавычках. Для подсчета строк, в которых количество равно
   10, потребуется только цифра 10.
9. Отделка закрывающей скобкой 🙂
10. Заполненная формула показана ниже.
11. Нажмите клавишу Enter, чтобы завершить ввод

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

Используйте типизированные критерии:

= СЧЁТЕСЛИМН (A2: A10, «Перо», B2: B10, «> = 10»)

= СЧЁТЕСЛИМН (A2: A10, D3, B2: B10, «> =» & E3)

Подсчет ячеек, соответствующих нескольким критериям
— SUMPRODUCT

В этом примере функция СУММПРОИЗВ используется для подсчета строк, в которых элемент — «Ручка», а количество —
больше или равно десяти.Это решение будет работать в более старых версиях Excel, где функция СЧЁТЕСЛИМН недоступна.

1. Выберите ячейку, в которой вы хотите увидеть итог
2. Введите знак равенства (=), чтобы начать формулу
3. Тип: SUMPRODUCT (- (
4. Выберите ячейки, содержащие значения, которые нужно проверить в первую очередь.
   критерий. В этом примере будут проверяться ячейки A2: A10
5. Введите первый критерий: = «Pen»
   Примечание. Поскольку это текстовый критерий, он заключен в двойные
   кавычки.
6. Тип), — (
7. Выберите ячейки, содержащие значения для проверки второго
   критерий. В этом примере будут проверяться ячейки B2: B10
8. Введите второй критерий:> = 10
   Примечание. Поскольку это числовой критерий, он не заключен.
   в двойных кавычках.
9. Отделка с закрывающими скобками :))
10. Заполненная формула показана ниже.
11. Нажмите клавишу Enter, чтобы завершить ввод

Примечание : вместо того, чтобы вводить критерий в формуле, вы можете
ссылка на ячейку, как показано во второй формуле ниже ▲ TOP

Используйте типизированные критерии:

= СУММПРОИЗВ (- (A2: A10 = «Перо»), — (B2: B10> = 10))

или ссылки на ячейки:

= СУММПРОИЗВ (- (A2: A10 = D2), — (B2: B10> = E2))

Подсчитать все даты в определенный месяц и год

В этом примере в ячейке A2 есть дата, а в списке заказов даты в ячейке A5: A26.Следующая функция СУММПРОИЗВ находится в ячейке D2 и подсчитывает все даты, которые имеют тот же месяц и год, что и дата в ячейке A2.

= СУММПРОИЗВ ((МЕСЯЦ (A5: A26) = МЕСЯЦ (A2)) * (ГОД (A5: A26) = ГОД (A2)))

• Функция МЕСЯЦ возвращает номер месяца для каждой ячейки и сравнивает его с номером месяца для даты в ячейке A2.
• Функция ГОД возвращает номер года для каждой ячейки и сравнивает его с номером года для даты в ячейке A2.
• Учитываются только строки, в которых совпадают месяц и год в ячейке A2.

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

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

В ячейках A6: C12 находится именованная таблица ( tblIDs ). В столбце ID каждый номер должен быть уникальным, но 2 вводится дважды, а 3 вводится дважды.

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

• В этом примере результат формулы должен быть 2 — есть 2 уникальных значения, которые были продублированы — 2 и 3.
• Формула НЕ сообщит нам, что 4 строки содержат повторяющиеся идентификационные номера.

Формула для подсчета повторяющихся элементов

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

= СУММПРОИЗВ ((tblIDs [ID] <> «») /
COUNTIF (tblIDs [ID], tblIDs [ID] & «») –
(COUNTIF (tblIDs [ID], tblIDs [ID])) = 1))

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

Как это работает

Формула СУММПРОИЗВ содержит 3 формулы:

• A: (tblIDs [ID] <> «») — Проверить, НЕ пуста ли ячейка — ИСТИНА (1) или ЛОЖЬ (0)
• B: COUNTIF (tblIDs [ID], tblIDs [ID] & «») — Сколько раз идентификатор появляется в столбце
• C: (COUNTIF (tblIDs [ID], tblIDs [ID]) = 1) — число (B) равно единице — TRUE (1) или FALSE (0)

).

Затем A делится на B, а C вычитается

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

Вычислить в строке формул

В строке формул вы можете выбрать каждую формулу (A, B и C) и нажать F9 для вычисления этой формулы. На этом снимке экрана показаны результаты.

Затем вычислите часть формулы A / B, чтобы увидеть эти результаты. ИСТИНА равно 1, поэтому 1/1 равно 1, а 1/2 равно 0,5.

Затем рассчитайте A / B — C, чтобы увидеть эти результаты. ИСТИНА равно 1, а ЛОЖЬ равно нулю, поэтому 1-1 равняется нулю и 0.5-0 равно 0,5:

Наконец, функция СУММПРОИЗВ дает сумму этих чисел с результатом 2.

Формулы на листе

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

На этом снимке экрана вы можете увидеть результаты A и B, затем A / B. Затем посмотрите расчеты C и A / B-C. Внизу последнего столбца отображается сумма.

Используйте результат формулы

В других частях книги вы можете обратиться к ячейке A4 (DupIds), чтобы создать предупреждающие сообщения или показать ноль вместо ожидаемых результатов. Например, покажите сообщение с такой формулой:

• = IF (DupIDs> 0, «Прежде чем продолжить, удалите повторяющиеся идентификаторы.», «»)

Или умножьте на 1 (ИСТИНА) или на ноль (ЛОЖЬ) в других формулах в зависимости от числа в ячейке DupIDs.

• = COUNT (tblIDs [ID]) * — (DupIDs = 0)

Подсчет строк в отфильтрованном списке

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

Подсчет строк в отфильтрованном списке с ПРОМЕЖУТОЧНЫМ ИТОГОМ

После фильтрации строк в списке можно использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ.
для подсчета видимых строк.

1. Примените к столу автофильтр.Здесь есть инструкции —
   Основы работы с AutoFilter
2. Отфильтруйте хотя бы один из столбцов в таблице. В этом примере
   первый столбец отфильтрован для связующих.
3. Выберите ячейку непосредственно под столбцом, для которого нужно суммировать.
4. Нажмите кнопку Автосумма на стандартной панели инструментов Excel.
   • Если вы хотите, чтобы функция ПРОМЕЖУТОЧНЫЙ ИТОГ использовалась не в той ячейке,
     непосредственно под отфильтрованным списком вы можете ввести формулу вместо
     использования кнопки Автосумма.
5. Будет автоматически вставлена ​​формула ПРОМЕЖУТОЧНЫЙ ИТОГ,
   видимые ячейки в столбце

• Первый аргумент функции ПРОМЕЖУТОЧНЫЙ ИТОГ — это номер функции,
  который определяет, как следует рассчитывать числа. По умолчанию
  равно 9, что указывает Excel на СУММИРОВАНИЕ чисел.
• Можно использовать другие номера функций, например 1 для СРЕДНЕГО и
  3 для COUNTA.Посмотрите полный список в справке Excel.

Чтобы подсчитать все непустые ячейки в столбце D, используйте 3 в качестве первого
аргумент:
= ПРОМЕЖУТОЧНЫЙ ИТОГ (3, D2: D10)

Нажмите клавишу Enter, чтобы завершить ввод формулы.

Примечание : для суммирования строк, которые были вручную скрыты или отфильтрованы, используйте 103 в качестве номера функции вместо 3 :
= ПРОМЕЖУТОЧНЫЙ ИТОГ (103, D2: D10)
▲ TOP

Подсчет строк в отфильтрованном списке с помощью AGGREGATE

После фильтрации строк в списке можно использовать функцию АГРЕГАТ
для подсчета видимых строк.Эта функция была введена в Excel 2010 и похожа на ПРОМЕЖУТОЧНЫЙ ИТОГ, но имеет 19 функций по сравнению с 11 функциями ПРОМЕЖУТОЧНЫЙ ИТОГ. Еще одно преимущество заключается в том, что он может игнорировать ошибки, а также скрытые строки.

1. Примените к столу автофильтр. Здесь есть инструкции —
   Основы работы с AutoFilter
2. Отфильтруйте хотя бы один из столбцов в таблице. В этом примере
   первый столбец отфильтрован для связующих.
3. Выберите ячейку, в которой вы хотите увидеть итог — ячейка B1 в этом примере
4. Чтобы начать формулу, введите: = AGGREGATE (
5. В списке номеров функций дважды щелкните 3-COUNTA , затем введите запятую
6. В списке номеров опций дважды щелкните 3 — Игнорировать скрытые строки, значения ошибок, вложенные функции ПРОМЕЖУТОЧНЫЙ ИТОГ и ОБЩИЙ ИТОГ , затем введите запятую
7. Выберите ячейки, содержащие значения, которые нужно проверить в первую очередь.
   критерий.В этом примере выбран столбец «Итого» в таблице.
8. Введите закрывающую скобку и нажмите клавишу Enter, чтобы завершить ввод формулы.
   = АГРЕГАТ (3,3, Таблица1 [Итого])

Подсчитываются два видимых числа, а ошибка в ячейке D9 игнорируется.

Подсчет конкретных элементов в отфильтрованном списке

Лоран Лонгре создал формулу, которая позволяет работать с видимыми строками.
после фильтра.Для получения информации см. Технику формул мощности в этом
статья на веб-сайте Джона Уокенбаха (через сайт WayBack Machine):
https://web.archive.org/web/20100110043824/ https://j-walk.com/ss/excel/eee/eee001.txt

Включая эту технику, СУММПРОИЗВ может использоваться для подсчета видимых
элементы в отфильтрованной таблице. В следующем примере столбец D был
отфильтрован для сумм больше 100. Следующая формула будет считать
количество видимых строк, содержащих «Pen» в столбце A.

1. В раскрывающемся списке в ячейке D1 выберите «Пользовательский».
2. Фильтр для строк больше 100.
3. В ячейке A12 введите: Pen
4. В ячейке B12 введите следующую формулу:

= СУММПРОИЗВ (ПРОМЕЖУТОЧНЫЙ ИТОГ (3; СМЕЩЕНИЕ (A1: A10; СТРОКА (A1: A10))
-МИН (СТРОКА (A1: A10)) ,, 1)), — (A1: A10 = A12))

5. Нажмите клавишу Enter, чтобы завершить ввод формулы. ▲ ТОП

Подсчет уникальных элементов в отфильтрованном списке

В бюллетене Excel Expert (выпуск 20, 8 июля 2001 г. — больше не доступен) есть формула для подсчета уникальных элементов в отфильтрованном списке. В этом примере список фильтруется для Центрального региона, и подсчитываются уникальные элементы в столбце D.

1. Столбец LineVal представляет собой именованный диапазон — Rge
2. Имя unRge определяется по формуле: = ЕСЛИ (ПРОМЕЖУТОЧНЫЙ ИТОГ (3, СМЕЩЕНИЕ (Rge, ROW (Rge) -MIN (ROW (Rge)) ,, 1)), Rge, «»)
3. В ячейке C2 введите следующую формулу:

= СУММ (N (ЕСЛИ (ISNA (MATCH («» «», unRge, 0)), MATCH (Rge, Rge, 0),
IF (MATCH (unRge, unRge, 0) = MATCH («» «», unRge, 0), 0,
MATCH (unRge, unRge, 0))) = ROW (Rge) -MIN (ROW (Rge)) + 1))

5. Это формула массива, поэтому нажмите Ctrl + Shift + Enter , чтобы завершить формулу. ▲ ТОП

Загрузить файлы примеров (COUNT)

1. Загрузите образец книги «Функции подсчета».
   Заархивированный файл имеет формат xlsx и не содержит макросов.
2. Загрузите 7 способов
   для подсчета образец учебной книги, чтобы следовать вместе с 7 способами подсчета
   видео. Заархивированный файл имеет формат xlsx и не содержит макросов.
3. Загрузите книгу «Максимальное количество символов 255».Заархивированный файл имеет формат xlsx и не содержит макросов.
4. Загрузите книгу «Подсчет уникальных элементов в отфильтрованном списке». Заархивированный файл имеет формат xlsx и не содержит макросов.
5. Загрузите книгу «Подсчет наборов повторяющихся номеров». Найдите повторяющиеся наборы из 6 чисел, которые могут быть в любом порядке в строке. Заархивированный файл имеет формат xlsx и не содержит макросов.

Дополнительные руководства по функциям

Список функций

Критерии подсчета в другом столбце

Количество отдельных элементов

подсчитывать
Ячейки с определенным текстом

СРЕДНЕЕ

СУММ / СУММЕСЛИ

Промежуточный итог

Что это такое и как их исправить

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

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

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

Что такое ошибка синтаксического анализа формулы?

Прежде чем мы перейдем к различным типам ошибок, вам может быть интересно, что означает ошибка синтаксического анализа формулы?

По сути, это означает, что Google Таблицы не могут интерпретировать вашу формулу.Он не может выполнить запрос формулы, поэтому возвращает сообщение об ошибке.

Это может произойти разными способами — от опечаток до математических невозможностей — и мы подробно рассмотрим их все ниже.

Понимание смысла сообщений об ошибках и умение их исправлять — важный шаг на пути к тому, чтобы стать профессионалом по формулам в Google Таблицах.

Аудит и отладка ошибок синтаксического анализа формул в Google Таблицах

Сопоставьте сообщение об ошибке в Google Таблице с приведенными ниже разделами и выясните, что может быть причиной вашей ошибки.

1. Всплывающее сообщение об ошибке синтаксического анализа формулы не позволяет мне ввести формулу
2. Я получаю сообщение об ошибке # Н / Д
3. Я получаю # DIV / 0! сообщение об ошибке
4. Я получаю # ЗНАЧЕНИЕ! сообщение об ошибке
5. Я получаю #REF! сообщение об ошибке
6. Я получаю # ИМЯ? сообщение об ошибке
7. Я получаю # ЧИСЛО! сообщение об ошибке
8. Я получаю # ОШИБКА! сообщение об ошибке
9. Я получаю # NULL! сообщение об ошибке
10. Другие стратегии работы с ошибками
11. Функции для устранения ошибок формул в Google Таблицах
12. Помогите! Моя формула ВСЕ ЕЩЕ не работает

Вот таблица Google со всеми этими примерами в формате.

1. Всплывающее сообщение об ошибке синтаксического анализа формулы не позволяет мне ввести формулу

Вы думаете, что закончили формулу, поэтому нажимаете ввод и бум! Вы получаете всплывающее окно с сообщением «Хьюстон, у нас проблема» или аналогичный:

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

Например, представьте, что, нажав клавишу Enter, вы также случайно нажали клавишу «\» (которая находится прямо над клавишей Enter) и непреднамеренно добавили это в конец вашей формулы:

Это приведет к появлению всплывающего сообщения об ошибке.Это легко исправить, удалив ненужный символ.

Как исправить эту ошибку?

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

2. Я получаю сообщение об ошибке # Н / Д. Как мне это исправить?

Ошибка синтаксического анализа формулы # N / A означает, что значение равно недоступно .

Чаще всего это происходит, когда вы используете формулу поиска (например,грамм. VLOOKUP), а поисковый запрос не найден. Именно это и произошло при точном совпадении VLOOKUP на изображении выше. Поискового запроса A-051 нет в нашей таблице данных, поэтому формула возвращает # N / A.

Эта формула не ошибочна и не нарушена, поэтому мы не хотим ее удалять. Однако было бы здорово, если бы вы могли отобразить собственное сообщение, например «Результат не найден», вместо сообщения об ошибке # Н / Д, особенно если у вас отображается много таких ошибок. Это дает пользователю таблицы гораздо больше информации и уменьшает путаницу.

К счастью, мы можем:

Как исправить ошибку # Н / Д?

Ну, вот такая удобная формула ЕСЛИ ОШИБКА:

= ЕСЛИ ОШИБКА ( исходная формула, значение, отображаемое, если исходная формула дает ошибку )

В этом примере ВПР полная формула будет выглядеть так:

= ЕСЛИОШИБКА (ВПР (поисковый запрос, таблица, индекс столбца, ЛОЖЬ), «Поисковый запрос не найден»)

, как показано в этом примере:

Вместо того, чтобы отображать ошибку синтаксического анализа формулы # Н / Д, когда значение не найдено, формула будет выводить наше настраиваемое сообщение вместо «Поисковый запрос не найден».

3. Я получаю # DIV / 0! сообщение об ошибке

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

С точки зрения непрофессионала это означает, что мы пытаемся вычислить что-то вроде этого:

= A / 0

, который не имеет значения (потому что нет ответа, который при умножении на 0 вернет A).

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

Другой пример — использование такой формулы, как СРЕДНИЙ, с пустым диапазоном.

Итак, = AVERAGE (A1: A10) вызовет # DIV / 0! ошибка, если диапазон A1: A10 не содержит числовых значений.

Как исправить ошибку # DIV / 0! ошибка?

Ну, первое, что нужно сделать, это определить, почему ваш знаменатель равен нулю.

Вы можете выбрать знаменатель и посмотреть, что он оценивает, выделив его в строке формул и увидев результат в маленьком всплывающем окне, как показано на этом изображении:

В этом случае формула в знаменателе СУММ (A1: A7) принимает значение 0, что вызывает ошибку.Поэтому проверьте, равен ли ваш результат знаменателя 0.

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

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

Как и в случае с примером ошибки # Н / Д, используйте формулу ЕСЛИОШИБКА, чтобы обернуть текущую формулу и указать результат, когда # DIV / 0! возникает ошибка. Возможно, вы захотите вывести сообщение об ошибке, например «Ошибка деления на 0» или конкретное значение, например 0:

4. Я получаю # ЗНАЧЕНИЕ! сообщение об ошибке

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

Пробелы в ячейках также могут вызывать это сообщение об ошибке.

В этом примере ячейка B1 содержит пробел, который является строковым значением и вызывает ошибку #VALUE! ошибка, потому что Google Таблицы не могут выполнить над ней математические операции, как показано в этом сообщении об ошибке:

В целом, Google Таблицы неплохо преобразовывают текст в числа, когда это необходимо. Если вы введете значение в ячейку с некоторыми пробелами, отформатируете его как текст, а затем попытаетесь вычислить его, Google Таблицы фактически превратят текст в число и все равно выполнят вычисление.

Другая причина ошибки #VALUE! ошибка смешивает форматы дат США и остального мира.

даты в США имеют формат ММ / ДД / ГГГГ, в то время как для остальных стран — ДД / ММ / ГГГГ. Если вы смешали эти два значения и попытаетесь вычесть их, например, чтобы получить количество дней между ними, вы получите #VALUE! ошибка.

(На самом деле, это та же проблема с текстом / цифрами, которая происходит под поверхностью. Даты хранятся в виде чисел, но если ваша дата находится в неправильном формате для настройки страны для вашей электронной таблицы, она будет сохранена как текстовая строка, и Google не узнает, что это свидание.)

Здесь правильным ответом должно было быть 59, количество дней между 28 февраля 2017 года и 31 декабря 2016 года.

Как исправить ошибку #VALUE! ошибка?

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

Найдите возможные несоответствия текста / чисел или ячейки, содержащие ошибочные пробелы. Если вы нажмете на ячейку, а мигающий курсор окажется между собой и элементом, рядом с которым он находится, то у вас будет пробел.

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

Даты с пробелами посередине тоже не работают:

5. Я получаю #REF! сообщение об ошибке

#REF! Ошибка синтаксического анализа формулы возникает, когда у вас есть недопустимая ссылка.

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

В этом примере исходная формула была = A1 * B1, но когда я удалил столбец A, формула пошла наперекосяк из-за отсутствия ссылки:

Другой способ, которым формула может ссылаться на отсутствующие ссылки, — это копирование формулы с относительным диапазоном на краю листа. При копировании и вставке относительный диапазон может перемещаться, как если бы он находился за пределами листа, что недопустимо и приведет к #REF! ошибка.

В этом примере функция суммы складывает ячейки в 3 строках выше.Когда я пытаюсь скопировать и вставить функцию суммы в новую ячейку с менее чем 3 строками выше, она выдаст мне #REF! ошибка:

Поиск вне пределов: Вы, наверное, видели #REF! ошибка, если вы часто используете формулы поиска, когда пытались вернуть значение за пределами указанных вами диапазонов. В этом примере ВПР я пытаюсь вернуть ответ из 3-го столбца таблицы поиска, в которой всего 2 столбца:

Круговая зависимость: Вы также получите #REF! ошибка при обнаружении круговой зависимости (когда формула ссылается на себя).

В этом примере у меня есть числа в диапазоне от A1 до A3, но формула СУММ в ячейке A4 пытается суммировать от A1 до A4, включая себя. Следовательно, у нас есть круговой аргумент, в котором ячейка A4 пытается быть как входной, так и выходной ячейкой, что недопустимо.

Как исправить ошибку #REF! ошибка?

Прежде всего, прочтите сообщение об ошибке, чтобы определить, что за #REF! ошибка, с которой вы столкнулись. Это должно дать вам подсказку, как исправить ошибку.

Для удаленных ссылок ищите #REF! ошибка внутри вашей формулы, замените # ССЫЛКА! с правильной ссылкой на ячейку или диапазон.

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

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

6. Я получаю # ИМЯ? сообщение об ошибке

# ИМЯ? Ошибка синтаксического анализа формулы означает проблему с синтаксисом формулы.

Наиболее частой причиной этой ошибки является неправильное написание одного из имен ваших функций.

В этом примере я неправильно написал функцию СУММ как СУММ, которую Google Таблицы не распознали, поэтому вернул ошибку:

Еще одна причина для # ИМЯ? ошибка ссылается на именованный диапазон, который на самом деле не существует, или написан с ошибкой.

Так

= СУММ (прибыль)

даст вам # ИМЯ? ошибка, если именованный диапазон профит не существует

Отсутствие кавычек вокруг текстового значения, как показано в этой простой формуле, также приведет к появлению #NAME? ошибка:

= CONCAT («Первый», Второй)

(В слове Second отсутствуют кавычки.)

Как исправить # ИМЯ? ошибка?

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

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

Убедитесь, что все текстовые значения введены в обязательных кавычках.

Наконец, пропустили ли вы двоеточие в ссылках на диапазон? Это будет очевидно, потому что он не будет правильно выделен.

Эта формула = СУММ (A1A10)

В

отсутствует двоеточие между A1 и A10, и выдается #NAME? ошибка.

Конечно, он должен читать = СУММ (A1: A10)

7. Я получаю # ЧИСЛО! сообщение об ошибке

# ЧИСЛО! Ошибка синтаксического анализа формулы отображается, если формула содержит недопустимые числовые значения.

Классический пример — попытка найти квадратный корень из отрицательного числа, что недопустимо:

(Для любого математика вы знаете, что вы можете разрешить квадратные корни отрицательных чисел с мнимыми числами, но это выходит за рамки электронных таблиц.)

Некоторые другие функции, результатом которых может быть # ЧИСЛО! сообщения об ошибках — это функции МАЛЕНЬКИЙ и БОЛЬШОЙ. Если вы попытаетесь найти наименьшее n-е значение в наборе данных, где n находится за пределами числа значений в наборе данных, вы получите # ЧИСЛО! ошибка.

Например, вы просите Google Таблицы найти 10-е наименьшее число в наборе данных, в котором всего 5 значений:

(Почему это не возвращает ошибку #REF!, Как пример ВПР вне пределов, я не знаю.)

Как исправить ошибку # ЧИСЛО! ошибка?

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

8. Я получаю сообщение об ошибке #ERROR! сообщение об ошибке синтаксического анализа формулы

Это сообщение об ошибке синтаксического анализа формулы является уникальным для Google Таблиц и не имеет прямого эквивалента в Excel. Это означает, что Google Таблицы не могут понять введенную вами формулу, потому что не могут проанализировать формулу для ее выполнения.

Например, если вы вручную вводите символ $ для обозначения суммы, но Google Таблицы думают, что вы имеете в виду абсолютную ссылку:

или вы пропустили «&» при объединении текста и числовых значений:

В этом случае формула должна быть такой: = «Итого» & сумма (A1: A3)

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

Как исправить ошибку #ERROR! ошибка?

Тщательно проверьте точность формулы.

Вы хотите убедиться, что у вас правильное количество скобок и правильный синтаксис соединения между текстом и числовыми значениями (например, с помощью «&»).

Если вы хотите отображать значения с помощью символов валюты или процентов, не вводите вручную символы «$» или «%». Вместо этого введите простое число, а затем используйте параметры форматирования, чтобы изменить его на нужный стиль.

9. Я получаю #NULL! сообщение об ошибке

Мне не удалось воссоздать #NULL! ошибка синтаксического анализа формул в дикой природе, но теоретически она существует!

(Если у вас есть один из них, дайте мне знать! Я хотел бы дополнить эту статью примером.)

10. Другие стратегии работы с ошибкой синтаксического анализа формул

Найдите красное выделение в своей формуле, так как это поможет определить источник ошибки, например. в случае слишком большого количества скобок лишние, лишние будут выделены красным цветом.

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

Различный синтаксис в разных странах : Некоторые европейские страны используют точку с запятой «;» вместо запятых «», так что это может быть причиной вашей ошибки. Сравните эти две формулы, которые имеют одинаковые входные и выходные данные, но синтаксис отличается для пользователей в разных странах (регионах).

= ArrayFormula (ВПР (A1; Лист2! A: I; {2 \ 3 \ 4 \ 5 \ 6 \ 7 \ 8}; ЛОЖЬ))

— это та же формула, что и эта:

= ArrayFormula (ВПР (A1, Sheet2! A: I, {2,3,4,5,6,7,8}; FALSE))

(Это пример функции ВПР, возвращающей несколько значений (массив) вместо одного значения.)

Профессиональный совет:

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

11. Функции для устранения ошибок синтаксического анализа формул в Google Таблицах

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

На самом деле, есть даже функция для генерации ошибок # N / A.Он имеет ограниченное применение, но может быть полезен для проверки данных в более сложных формулах.

= NA () выдаст ошибку # N / A. (Справка по Документам Google для Северной Америки)

= ERROR.TYPE (value) вернет число, соответствующее типу ошибки:

• 1 для #NULL!
• 2 для # DIV / 0!
• 3 для #VALUE!
• 4 для #REF!
• 5 для # ИМЯ?
• 6 для # ЧИСЛО!
• 7 для № Н / Д
• 8 для всех остальных ошибок

(Справка Google Документов по ERROR.ТИП)

= ISNA (значение)
проверяет, является ли значение ошибкой № Н / Д, и выдает на выходе ИСТИНА для ошибки № Н / Д и ЛОЖЬ в противном случае. (Справка Документов Google по ISNA)

= ISERR (значение)
проверяет, является ли значение какой-либо ошибкой, кроме ошибки # N / A.

No related posts.