Отображение и скрытие нулевых значений. Если excel

Функция УСЛОВИЯ - Служба поддержки Office

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

Простой синтаксис

Примечания: 

• Функция ЕСЛИМН позволяет проверить до 127 различных условий.

• Например:

• Расшифровка: если(значение A1 равно 1, вывести 1, если значение A1 равно 2, вывести 2, иначе если значение A1 равно 3, вывести 3).

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

Синтаксис

• УСЛОВИЯ(лог_выражение1;значение_если_истина1,[лог_выражение2;значение_если_истина2],[лог_выражение3;значение_если_истина3],...)

Аргумент	Описание
лог_выражение1 (обязательно)	Условие, принимающее значение ИСТИНА или ЛОЖЬ.
значение_если_истина1 (обязательно)	Результат, возвращаемый, если условие лог_выражение1 принимает значение ИСТИНА. Может не указываться.
лог_выражение2…лог_выражение127 (необязательно)	Условие, принимающее значение ИСТИНА или ЛОЖЬ.
значение_если_истина2...значение_если_истина127 (необязательно)	Результат, возвращаемый, если условие лог_выражениеN принимает значение ИСТИНА. Каждый аргумент значение_если_истинаN соответствует условию лог_выражениеN. Может не указываться.

Пример 1

Формула для ячеек A2:A6:

Расшифровка: ЕСЛИ(значение A2 больше 89, возвращается "A", если значение A2 больше 79, возвращается "B", и т. д. Для всех значений меньше 59 возвращается "F").

Пример 2

В ячейке G7 находится следующая формула:

Расшифровка: ЕСЛИ(значение в ячейке F2 равно 1, возвращаемое значение в ячейке D2, если значение в ячейке F2 равно 2, возвращается значение в ячейке D3, и т. д. Если ни одно из других условий не выполняется, возвращается значение в ячейке D8.

Замечания

• Чтобы задать результат по умолчанию, введите значение ИСТИНА для последнего аргумента лог_выражение. Если ни одно из других условий не выполняется, возвращается соответствующее значение. Это показано в строках 6 и 7 примера 1 (с 58 баллами).

•  Если условие лог_выражение указывается без соответствующего аргумента значение_если_истина, появляется сообщение "Указано недостаточно аргументов".

•  Если аргумент лог_выражение принимает значение, отличное от ИСТИНА или ЛОЖЬ, функция возвращает ошибку #ЗНАЧ!.

•  Если условия со значением ИСТИНА не найдены, возвращается ошибка #Н/Д!

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также:

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

support.office.com

Функция СУММЕСЛИ - Служба поддержки Office

Функция СУММЕСЛИ используется, если необходимо просуммировать значения диапазона, соответствующие указанному условию. Предположим, например, что в столбце с числами необходимо просуммировать только значения, превышающие 5. Для этого можно использовать следующую формулу: =СУММЕСЛИ(B2:B25;">5")

Это видео — часть учебного курса Сложение чисел в Excel 2013.

Советы: 

• При необходимости условия можно применить к одному диапазону, а просуммировать соответствующие значения из другого диапазона. Например, формула =СУММЕСЛИ(B2:B5; "Иван"; C2:C5) суммирует только те значения из диапазона C2:C5, для которых соответствующие значения из диапазона B2:B5 равны "Иван".

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

Синтаксис

СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])

Аргументы функции СУММЕСЛИ описаны ниже.

• Диапазон   . Обязательный аргумент. Диапазон ячеек, оцениваемых на соответствие условиям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки на числа. Пустые ячейки и ячейки, содержащие текстовые значения, пропускаются. Выбранный диапазон может содержать даты в стандартном формате Excel (см. примеры ниже).

• Условие   . Обязательный аргумент. Условие в форме числа, выражения, ссылки на ячейку, текста или функции, определяющее, какие ячейки необходимо суммировать. Например, условие может быть представлено в таком виде: 32, ">32", B5, "32", "яблоки" или СЕГОДНЯ().

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

• Диапазон_суммирования   . Необязательный аргумент. Ячейки, значения из которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона. Если аргумент диапазон_суммирования опущен, Excel суммирует ячейки, указанные в аргументе диапазон (те же ячейки, к которым применяется условие).

• В аргументе условие можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует одному любому символу, а звездочка — любой последовательности символов. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак "тильда" (~).

Примечания

• Функция СУММЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 символов или применяется к строке #ЗНАЧ!.

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

Диапазон	Диапазон суммирования	Фактические ячейки
A1:A5	B1:B5	B1:B5
A1:A5	B1:B3	B1:B5
A1:B4	C1:D4	C1:D4
A1:B4	C1:C2	C1:D4

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

Примеры

Пример 1

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

Стоимость имущества	Комиссионные	Данные
1 000 000 ₽	70 000 ₽	2 500 000 ₽
2 000 000 ₽	140 000 ₽
3 000 000 ₽	210 000 ₽
4 000 000 ₽	280 000 ₽
Формула	Описание	Результат
=СУММЕСЛИ(A2:A5;">160000";B2:B5)	Сумма комиссионных за имущество стоимостью больше 1 600 000 ₽.	630 000 ₽
=СУММЕСЛИ(A2:A5; ">160000")	Сумма по имуществу стоимостью больше 1 600 000 ₽.	9 000 000 ₽
=СУММЕСЛИ(A2:A5;300000;B2:B5)	Сумма комиссионных за имущество стоимостью 3 000 000 ₽.	210 000 ₽
=СУММЕСЛИ(A2:A5;">" &C2;B2:B5)	Сумма комиссионных за имущество, стоимость которого превышает значение в ячейке C2.	490 000 ₽

Пример 2

Скопируйте образец данных из приведенной ниже таблицы и вставьте его в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. Кроме того, вы можете настроить ширину столбцов в соответствии с содержащимися в них данными.

Категория	Продукты	Объем продаж
Овощи	Помидоры	23 000 ₽
Овощи	Сельдерей	55 000 ₽
Фрукты	Апельсины	8 000 ₽
	Масло	4 000 ₽
Овощи	Морковь	42 000 ₽
Фрукты	Яблоки	12 000 ₽
Формула	Описание	Результат
=СУММЕСЛИ(A2:A7;"Фрукты";C2:C7)	Объем продаж всех продуктов категории "Фрукты".	20 000 ₽
=СУММЕСЛИ(A2:A7;"Овощи";C2:C7)	Объем продаж всех продуктов категории "Овощи".	120 000 ₽
=СУММЕСЛИ(B2:B7;"*ы";C2:C7)	Объем продаж всех продуктов, названия которых заканчиваются на "ы" ("Помидоры" и "Апельсины").	43 000 ₽
=СУММЕСЛИ(A2:A7;"";C2:C7)	Объем продаж всех продуктов, категория для которых не указана.	4 000 ₽

К началу страницы

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Дополнительные сведения

Функция СУММЕСЛИМН суммирует все аргументы, которые соответствуют нескольким условиям

Функция СУММКВ суммирует несколько значений, предварительно возведя каждое из них в квадрат

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

Функция СЧЁТЕСЛИМН подсчитывает только те значения, которые соответствуют нескольким условиям

Функция ЕСЛИМН (Office 365, Excel 2016 и более поздние версии)

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Обнаружение ошибок в формулах с помощью функции проверки ошибок

Математические и тригонометрические функции

Функции Excel (по алфавиту)

Функции Excel (по категориям)

support.office.com

СЧЁТЕСЛИ (функция СЧЁТЕСЛИ) - Служба поддержки Office

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

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

Например:

СЧЁТЕСЛИ(диапазон;критерий)

Имя аргумента	Описание
диапазон    (обязательный)	Группа ячеек, для которых нужно выполнить подсчет. Диапазон может содержать числа, массивы, именованный диапазон или ссылки на числа. Пустые и текстовые значения игнорируются. Узнайте, как выбирать диапазоны на листе.
критерий    (обязательный)	Число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать. Например, критерий может быть выражен как 32, ">32", В4, "яблоки" или "32". В функции СЧЁТЕСЛИ используется только один критерий. Чтобы провести подсчет по нескольким условиям, воспользуйтесь функцией СЧЁТЕСЛИМН.

Примеры

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

Данные	Данные
яблоки	32
апельсины	54
персики	75
яблоки	86
Формула	Описание
=СЧЁТЕСЛИ(A2:A5;"яблоки")	Количество ячеек, содержащих текст "яблоки" в ячейках А2–А5. Результат — 2.
=СЧЁТЕСЛИ(A2:A5;A4)	Количество ячеек, содержащих текст "персики" (значение ячейки A4) в ячейках А2–А5. Результат — 1.
=СЧЁТЕСЛИ(A2:A5;A2)+СЧЁТЕСЛИ(A2:A5;A3)	Количество ячеек, содержащих текст "яблоки" (значение ячейки A2) и "апельсины" (значение ячейки A3) в ячейках А2–А5. Результат — 3. В этой формуле для указания нескольких критериев, по одному критерию на выражение, функция СЧЁТЕСЛИ используется дважды. Также можно использовать функцию СЧЁТЕСЛИМН.
=СЧЁТЕСЛИ(B2:B5;">55")	Количество ячеек со значением больше 55 в ячейках В2–В5. Результат — 2.
=СЧЁТЕСЛИ(B2:B5;"<>"&B4)	Количество ячеек со значением, не равным 75, в ячейках В2–В5. Знак амперсанда (&) объединяет оператор сравнения "<>" (не равно) и значение в ячейке B4, в результате чего получается формула =СЧЁТЕСЛИ(B2:B5;"<>75"). Результат — 3.
=СЧЁТЕСЛИ(B2:B5;">=32")-СЧЁТЕСЛИ(B2:B5;">85")	Количество ячеек со значением, большим или равным 32 и меньшим или равным 85, в ячейках В2–В5. Результат — 3.
=СЧЁТЕСЛИ(A2:A5;"*")	Количество ячеек, содержащих любой текст, в ячейках А2–А5. Подстановочный знак "*" обозначает любое количество любых символов. Результат — 4.
=СЧЁТЕСЛИ(A2:A5;"????ки")	Количество ячеек, строка в которых содержит ровно 7 знаков и заканчивается буквами "ки", в диапазоне A2–A5. Подставочный знак "?" обозначает отдельный символ. Результат — 2.

Распространенные неполадки

Проблема	Возможная причина
Для длинных строк возвращается неправильное значение.	Функция СЧЁТЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 символов. Для работы с такими строками используйте функцию СЦЕПИТЬ или оператор сцепления &. Пример: =СЧЁТЕСЛИ(A2:A5;"длинная строка"&"еще одна длинная строка").
Функция должна вернуть значение, но ничего не возвращает.	Аргумент критерий должен быть заключен в кавычки.
Когда формула СЧЁТЕСЛИ ссылается на другую книгу, появляется ошибка #ЗНАЧ!.	Эта ошибка возникает при вычислении ячеек, когда в формуле содержится функция, которая ссылается на ячейки или диапазон в закрытой книге. Для работы этой функции необходимо, чтобы другая книга была открыта.

Рекомендации

Действие	Результат
Помните о том, что функция СЧЁТЕСЛИ не учитывает регистр символов в текстовых строках.	Критерий не чувствителен к регистру. Например, строкам "яблоки" и "ЯБЛОКИ" будут соответствовать одни и те же ячейки.
Использование подстановочных знаков	В критерии можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует одному любому символу, а звездочка — любой последовательности знаков. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак тильды (~). Например, =СЧЁТЕСЛИ(A2:A5;"яблок?") возвращает все вхождения слова "яблок" с любой буквой в конце.
Убедитесь, что данные не содержат ошибочных символов.	При подсчете текстовых значений убедитесь в том, что данные не содержат начальных или конечных пробелов, недопустимых прямых и изогнутых кавычек или непечатаемых символов. В этих случаях функция СЧЁТЕСЛИ может вернуть непредвиденное значение. Попробуйте воспользоваться функцией ПЕЧСИМВ или функцией СЖПРОБЕЛЫ.
Для удобства используйте именованные диапазоны.	Функция СЧЁТЕСЛИ поддерживает именованные диапазоны в формулах (например, =СЧЁТЕСЛИ(фрукты;">=32")-СЧЁТЕСЛИ(фрукты;">85"). Именованный диапазон может располагаться на текущем листе, другом листе этой же книги или листе другой книги. Чтобы одна книга могла ссылаться на другую, они обе должны быть открыты.

Примечание: С помощью функции СЧЁТЕСЛИ нельзя подсчитать количество ячеек с определенным фоном или цветом шрифта. Однако Excel поддерживает пользовательские функции, в которых используются операции VBA (Visual Basic для приложений) над ячейками, выполняемые в зависимости от фона или цвета шрифта. Вот пример подсчета количества ячеек определенного цвета с использованием VBA.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Для подсчета непустых ячеек используйте функцию СЧЁТЗ

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

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

Функция СУММЕСЛИ суммирует значения, соответствующие одному критерию

Функция СУММЕСЛИМН суммирует только те значения, которые соответствуют нескольким критериям

Функция УСЛОВИЯ (Office 365, Excel 2016 и более поздней версии)

Функция СЖПРОБЕЛЫ удаляет из ячеек начальные и конечные пробелы

Функция ПЕЧСИМВ удаляет из ячеек непечатаемые символы

Общие сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Обнаружение ошибок в формулах

Статистические функции

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Использование функций СУММЕСЛИ, СЧЁТЕСЛИ и связанных с ними функций для экспресс-анализа данных (бесплатное ознакомительное видео)

support.office.com

Добавление и удаление надстроек в Excel

Некоторые надстройки, такие как "Пакет анализа" и "Поиск решения", встроены в Excel. Другие доступны в Центре загрузки, и их необходимо предварительно скачать и установить. Кроме того, некоторые надстройки создаются сторонними организациями, например поставщиками программных решений или программистами. Это могут быть надстройки модели COM, надстройки Visual Basic для приложений (VBA) и надстройки DLL. Они также требуют установки.

Большинство надстроек можно разделить на три типа, описанных ниже.

• Надстройки Excel.    Обычно к этому типу относят файлы надстроек Excel (XLAM), Excel 97–2003 (XLA) и надстроек DLL (XLL), а также надстройки автоматизации. Некоторые надстройки Excel, такие как "Пакет анализа" и "Поиск решения", становятся доступны после установки Microsoft Office или Excel. Для использования этих надстроек в большинстве случаев нужно лишь активировать их.

• Скачиваемые надстройки.    Дополнительные надстройки для Excel можно скачать и установить со страницы Загрузки на сайте Office.com.

• Пользовательские надстройки    Многие разработчики и поставщики решений создают пользовательские надстройки COM, надстройки автоматизации, VBA и XLL. Чтобы их можно было использовать, их нужно установить.

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

• Вкладка Данные. После установки и активации надстроек "Пакет анализа" и "Поиск решения" на вкладке Данные ленты становятся доступны команды Анализ данных и Поиск решения.

• Вкладка Главная. Другие надстройки можно добавить на вкладку Главная.

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

support.office.com

Исправление ошибки #ИМЯ? - Служба поддержки Office

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

Важно: Ошибка #ИМЯ? означает, что нужно исправить синтаксис, поэтому если вы видите ее в формуле, устраните ее. Не скрывайте ее с помощью функций обработки ошибок, например функции ЕСЛИОШИБКА.

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

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

Excel автоматически запустит мастер.

Щелкните любой аргумент, и Excel покажет вам сведения о нем.

Ниже приведены другие причины возникновения ошибок #ИМЯ?.

Если формула содержит ссылку на имя, которое не определено в Excel, вы увидите ошибку #ИМЯ?.

В следующем примере функция СУММ ссылается на имя Прибыль, которое не определено в книге.

Решение. Определите имя в Диспетчере имен, а затем добавьте его в формулу. Вот как это сделать:

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

2. На вкладке Формулы в группе Определенные имена нажмите кнопку Присвоить имя и выберите команду Присвоить имя.

3. Введите уникальное имя.

4. В качестве области выберите лист или всю книгу.

5. При желании введите примечание.

6. Нажмите кнопку ОК.

   Далее нужно добавить имя в формулу.

7. Курсор должен быть в том месте формулы, куда вы хотите добавить созданное имя.

8. На вкладке Формулы в группе Определенные имена нажмите кнопку Использовать в формуле и выберите нужное имя.

   

Подробнее об использовании определенных имен см. в статье Определение и использование имен в формулах.

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

Допустим, что в электронной таблице было создано определенное имя Прибыль. В приведенном ниже примере имя указано неправильно, поэтому функция по-прежнему выдает ошибку #ИМЯ?.

Решение. Исправьте опечатку и повторите попытку.

Совет: Вместо того чтобы вручную вводить определенные имена в формулах, предоставьте это Excel. На вкладке Формулы в группе Определенные имена нажмите кнопку Использовать в формуле и выберите нужное имя. Excel добавит его в формулу.

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

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

Решение. Проверьте, нет ли в формуле текстовых значений без кавычек.

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

В приведенном ниже примере формула ИНДЕКС выдает ошибку #ИМЯ?, так как в диапазоне B2 — B12 нет двоеточия.

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

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

Чтобы включить надстройку в Excel:

1. Щелкните Файл > Параметры.

2. Щелкните Надстройки.

3. В списке Управление выберите пункт Надстройки Excel и нажмите кнопку Перейти.

4. Установите нужный флажок и нажмите кнопку ОК.

У вас есть вопрос об определенной функции?

Задать вопрос на форуме сообщества, посвященном Excel

У вас есть предложения по улучшению следующей версии Excel? Если да, ознакомьтесь с темами на портале пользовательских предложений для Excel.

См. также

Общие сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Обнаружение ошибок в формулах с помощью функции проверки ошибок

Функции Excel (по алфавиту)

Функции Excel (по категориям)

support.office.com

Функция ЕСЛИ — вложенные формулы и типовые ошибки

Функция ЕСЛИ позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат.

• =ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)

Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй — если сравнение ложно.

Операторы ЕСЛИ чрезвычайно надежны и являются неотъемлемой частью многих моделей электронных таблиц. Но они же часто становятся причиной многих проблем с электронными таблицами. В идеале оператор ЕСЛИ должен применяться для минимума условий (например, "Женский"/"Мужской", "Да"/"Нет"/"Возможно"), но иногда сценарии настолько сложны, что для их оценки требуется использовать вместе больше 3 вложенных* функций ЕСЛИ.

* "Вложенность" означает объединение нескольких функций в одной формуле.

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

Синтаксис

ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])

Например:

Имя аргумента	Описание
лог_выражение    (обязательный)	Условие, которое нужно проверить.
значение_если_истина    (обязательный)	Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.
значение_если_ложь    (необязательный)	Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.

Примечания

Excel позволяет использовать до 64 вложенных функций ЕСЛИ, но это вовсе не означает, что так и надо делать. Почему?

• Нужно очень крепко подумать, чтобы выстроить последовательность из множества операторов ЕСЛИ и обеспечить их правильную отработку по каждому условию на протяжении всей цепочки. Если при вложении вы допустите в формуле малейшую неточность, она может сработать в 75 % случаев, но вернуть непредвиденные результаты в остальных 25 %. К сожалению, шансов отыскать эти 25 % немного.

• Работа с множественными операторами ЕСЛИ может оказаться чрезвычайно трудоемкой, особенно если вы вернетесь к ним через какое-то время и попробуете разобраться, что пытались сделать вы или, и того хуже, кто-то другой.

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

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

Примеры

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

• =ЕСЛИ(D2>89;"A";ЕСЛИ(D2>79;"B";ЕСЛИ(D2>69;"C";ЕСЛИ(D2>59;"D";"F"))))

  Этот сложный оператор с вложенными функциями ЕСЛИ следует простой логике:

1. Если тестовых баллов (в ячейке D2) больше 89, учащийся получает оценку A.

2. Если тестовых баллов больше 79, учащийся получает оценку B.

3. Если тестовых баллов больше 69, учащийся получает оценку C.

4. Если тестовых баллов больше 59, учащийся получает оценку D.

5. В противном случае учащийся получает оценку F.

Этот частный пример относительно безопасен, поскольку взаимосвязь между тестовыми баллами и буквенными оценками вряд ли будет меняться, так что дополнительных изменений не потребуется. Но что если вам потребуется разделить оценки на A+, A и A– (и т. д.)? Теперь ваши четыре условных оператора ЕСЛИ нужно переписать с учетом 12 условий! Вот так будет выглядеть ваша формула:

Она по-прежнему точна и будет правильно работать, но вы потратите много времени, чтобы написать ее, а потом протестировать. Еще одна очевидная проблема состоит в том, что вам придется вручную вводить баллы и эквивалентные буквенные оценки. Каковы шансы, что вы не ошибетесь? А теперь представьте, как вы пытаетесь сделать это 64 раза для более сложных условий! Конечно, это возможно. Но неужели вам хочется потратить столько сил без всякой уверенности в отсутствии ошибок, которые потом будет трудно обнаружить?

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

Дополнительные примеры

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

• =ЕСЛИ(C9>15000;20%;ЕСЛИ(C9>12500;17,5%;ЕСЛИ(C9>10000;15%;ЕСЛИ(C9>7500;12,5%;ЕСЛИ(C9>5000;10%;0)))))

Эта формула означает: ЕСЛИ(ячейка C9 больше 15 000, то вернуть 20 %, ЕСЛИ(ячейка C9 больше 12 500, то вернуть 17,5 % и т. д...

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

Совет: Чтобы сложные формулы было проще читать, вы можете вставить разрывы строк в строке формул. Просто нажмите клавиши ALT+ВВОД перед текстом, который хотите перенести на другую строку.

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

Видите, что происходит? Посмотрите порядок сравнения доходов в предыдущем примере. А как все идет в этом? Именно! Сравнение идет снизу вверх (от 5 000 до 15 000 ₽), а не наоборот. Ну и что в этом такого? Это важно, потому что формула не может пройти первую оценку для любого значения, превышающего 5 000 ₽. Скажем, ваш доход составил 12 500 ₽ — оператор ЕСЛИ вернет 10 %, потому что это больше 5 000 ₽, и на этом остановится. Это может быть очень проблематично, поскольку ошибки такого типа часто остаются незамеченными, пока не оказывают негативного влияния. Так что же вам делать теперь, когда вы знаете, какие трудности могут ожидать вас при использовании вложенных операторов ЕСЛИ? В большинстве случаев вместо сложной формулы с функциями ЕСЛИ можно использовать функцию ВПР. При использовании функции ВПР вам для начала нужно создать ссылочную таблицу:

В этой формуле предлагается найти значение ячейки C2 в диапазоне C5:C17. Если значение найдено, возвращается соответствующее значение из той же строки в столбце D.

Эта формула ищет значение ячейки B9 в диапазоне B2:B22. Если значение найдено, возвращается соответствующее значение из той же строки в столбце C.

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

Функция ВПР подробно рассматривается здесь, но очевидно, что она значительно проще, чем сложный 12-уровневый вложенный оператор ЕСЛИ. Есть и другие, менее очевидные, преимущества:

• Таблицы ссылок функции ВПР открыты и их легко увидеть.

• Значения в таблицах просто обновлять, и вам не потребуется трогать формулу, если условия изменятся.

• Если вы не хотите, чтобы люди видели вашу таблицу ссылок или вмешивались в нее, просто поместите ее на другой лист.

Вы знали?

Теперь есть функция УСЛОВИЯ, которая может заменить несколько вложенных операторов ЕСЛИ. Так, в нашем первом примере оценок с 4 вложенными функциями ЕСЛИ:

можно сделать все гораздо проще с помощью одной функции ЕСЛИМН:

Функция ЕСЛИМН — просто находка! Благодаря ей вам больше не нужно переживать обо всех этих операторах ЕСЛИ и скобках.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также:

Видео: расширенное применение функции ЕСЛИ Функция УСЛОВИЯ (Office 365, Excel 2016 и более поздние версии) Функция СЧЁТЕСЛИ (подсчитывает значения с учетом одного условия) Функция СЧЁТЕСЛИМН (подсчитывает значения с учетом нескольких условий) Функция СУММЕСЛИ (суммирует значения с учетом одного условия) Функция СУММЕСЛИМН (суммирует значения с учетом нескольких условий) Функция И Функция ИЛИ Функция ВПР Общие сведения о формулах в Exce lРекомендации, позволяющие избежать появления неработающих формул Поиск ошибок в формулах Логические функции Функции Excel (по алфавиту) Функции Excel (по категориям)

support.office.com

Отображение и скрытие нулевых значений

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

Скрытие и отображение всех нулевых значений на листе

1. Выберите Файл > Параметры > Дополнительно.

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

   • Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения.

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

Скрытие нулевых значений в выделенных ячейках

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

1. Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.

2. Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек.

   

3. Щелкните Число > Все форматы.

4. В поле Тип введите выражение 0;;;@ и нажмите кнопку ОК.

Отображение скрытых значений.

1. Выделите ячейки со скрытыми нулевыми значениями.

2. Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек.

3. Для применения числового формата, определенного по умолчанию, выберите Число > Общий и нажмите кнопку ОК.

Скрытие нулевых значений, возвращенных формулой

1. Выделите ячейку, содержащую нулевое (0) значение.

2. На вкладке Главная щелкните стрелку рядом с кнопкой Условное форматирование и выберите "Правила выделения ячеек" > "Равно".

3. В левом поле введите 0.

4. В правом поле выберите Пользовательский формат.

5. В поле Формат ячейки откройте вкладку Шрифт.

6. В списке Цвет выберите белый цвет и нажмите кнопку ОК.

Отображение нулей в виде пробелов или тире

Для решения этой задачи воспользуйтесь функцией ЕСЛИ.

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

=ЕСЛИ(A2-A3=0;"";A2-A3)

Вот как читать формулу. Если результат вычисления (A2-A3) равен "0", ничего не отображается, в том числе и "0" (это указывается двойными кавычками ""). В противном случае отображается результат вычисления A2-A3. Если вам нужно не оставлять ячейки пустыми, но отображать не "0", а что-то другое, между двойными кавычками вставьте дефис "-" или другой символ.

Скрытие нулевых значений в отчете сводной таблицы

1. Выберите отчет сводной таблицы.

2. На вкладке Анализ в группе Сводная таблица щелкните стрелку рядом с командой Параметры и выберите пункт Параметры.

3. Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.

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

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

К началу страницы

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

Отображение и скрытие всех нулевых значений на листе

1. Выберите Файл > Параметры > Дополнительно.

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

   • Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения.

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

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

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

1. Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.

2. Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек.

3. В списке Категория выберите элемент Пользовательский.

4. В поле Тип введите 0;-0;;@

Примечания: 

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

• Чтобы снова отобразить скрытые значения, выделите ячейки, а затем нажмите клавиши CTRL+1 или на вкладке Главная в группе Ячейки наведите указатель мыши на элемент Формат и выберите Формат ячеек. Чтобы применить числовой формат по умолчанию, в списке Категория выберите Общий. Чтобы снова отобразить дату и время, выберите подходящий формат даты и времени на вкладке Число.

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

1. Выделите ячейку, содержащую нулевое (0) значение.

2. На вкладке Главная в группе Стили щелкните стрелку рядом с элементом Условное форматирование, наведите указатель на элемент Правила выделения ячеек и выберите вариант Равно.

3. В левом поле введите 0.

4. В правом поле выберите Пользовательский формат.

5. В диалоговом окне Формат ячеек откройте вкладку Шрифт.

6. В поле Цвет выберите белый цвет.

Использование формулы для отображения нулей в виде пробелов или тире

Для выполнения этой задачи используйте функцию ЕСЛИ.

Пример

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

Копирование примера

1. Выберите пример в этой статье. Если вы копируете пример в Excel Web App, копируйте и вставляйте ячейки по одной.Внимание!   Не выделяйте заголовки строк или столбцов.

   

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

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

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

4. На листе выделите ячейку A1 и нажмите клавиши CTRL+V. При работе в Excel Web App повторите копирование и вставку для каждой ячейки из примера.

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

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

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

A B Данные 10 10 Формула Описание (результат) =A2-A3 Второе число вычитается из первого (0). =ЕСЛИ(A2-A3=0;"";A2-A3) Возвращает пустую ячейку, если значение равно нулю =ЕСЛИ(A2-A3=0;"-";A2-A3) Возвращает дефис (-), если значение равно нулю

Дополнительные сведения об использовании этой функции см. в статье Функция ЕСЛИ.

Скрытие нулевых значений в отчете сводной таблицы

1. Щелкните отчет сводной таблицы.

2. На вкладке Параметры в группе Параметры сводной таблицы щелкните стрелку рядом с командой Параметры и выберите пункт Параметры.

3. Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.

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

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

К началу страницы

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

Отображение и скрытие всех нулевых значений на листе

1. Нажмите Кнопку Microsoft Office , выберите пункт Параметры Excel и выберите категорию Дополнительно.

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

   • Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения.

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

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

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

1. Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.

2. Вы можете нажать клавиши CTRL+1 или на вкладке Главная в группе Ячейки щелкнуть Формат > Формат ячеек.

3. В списке Категория выберите элемент Пользовательский.

4. В поле Тип введите 0;-0;;@

Примечания: 

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

• Чтобы снова отобразить скрытые значения, выделите ячейки, а затем на вкладке Главная в группе Ячейки наведите указатель мыши на элемент Формат и выберите Формат ячеек. Чтобы применить числовой формат по умолчанию, в списке Категория выберите Общий. Чтобы снова отобразить дату и время, выберите подходящий формат даты и времени на вкладке Число.

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

1. Выделите ячейку, содержащую нулевое (0) значение.

2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите "Правила выделения ячеек" > "Равно".

3. В левом поле введите 0.

4. В правом поле выберите Пользовательский формат.

5. В диалоговом окне Формат ячеек откройте вкладку Шрифт.

6. В поле Цвет выберите белый цвет.

Использование формулы для отображения нулей в виде пробелов или тире

Для выполнения этой задачи используйте функцию ЕСЛИ.

Пример

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

Копирование примера

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

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

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

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

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

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

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

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

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

A B Данные 10 10 Формула Описание (результат) =A2-A3 Второе число вычитается из первого (0). =ЕСЛИ(A2-A3=0;"";A2-A3) Возвращает пустую ячейку, если значение равно нулю =ЕСЛИ(A2-A3=0;"-";A2-A3) Возвращает дефис (-), если значение равно нулю

Дополнительные сведения об использовании этой функции см. в статье Функция ЕСЛИ.

Скрытие нулевых значений в отчете сводной таблицы

1. Щелкните отчет сводной таблицы.

2. На вкладке Параметры в группе Параметры сводной таблицы щелкните стрелку рядом с командой Параметры и выберите пункт Параметры.

3. Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.

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

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

support.office.com

---

• 
  Виндовс 10 как отключить защитник виндовс
• 
  Как почистить реестр на windows 10
• 
  Забыл пароль от роутера
• 
  Рейтинг видеокарт для ноутбуков
• 
  Рейтинг роутеров wifi 2018 для квартиры
• 
  Драйвера видео
• 
  Как подключить роутер к ноутбуку
• 
  Пропал звук на ноутбуке windows 7 что делать и как исправить
• 
  Какой выбрать планшет
• 
  Почему нет звука на компе если все работает
• 
  Как сделать откат системы на виндовс 7