Как сделать да нет в excel?

• Чтобы выполнить действие только тогда, когда ячейка не пуста (содержит какие-то значения), вы можете использовать формулу, основанную на функции ЕСЛИ.
• В примере ниже столбец F содержит даты завершения закупок шоколада.
• Поскольку даты для Excel — это числа, то наша задача состоит в том, чтобы проверить в ячейке наличие числа.
• Формула в ячейке F3:
• =ЕСЛИ(СЧЁТЗ(D3:D9)=7,СУММ(C3:C9),»»)

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

Функция СЧЕТЗ (английский вариант — COUNTA) подсчитывает количество значений (текстовых, числовых и логических) в диапазоне ячеек Excel.

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

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

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

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

=ЕСЛИ(ИЛИ(ЕПУСТО(D3),ЕПУСТО(D4),ЕПУСТО(D5),ЕПУСТО(D6),ЕПУСТО(D7),ЕПУСТО(D8),ЕПУСТО(D9)),»»,СУММ(C3:C9))

Функция ИЛИ (английский вариант — OR) позволяет объединить условия и указать, что нам достаточно того, чтобы хотя бы одна функция ЕПУСТО обнаружила пустую ячейку. В этом случае никаких вычислений не производим и функция ЕСЛИ возвращает пустую строку. В противном случае — производим вычисления.

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

Рассмотрим теперь более универсальные решения.

=ЕСЛИ(СЧИТАТЬПУСТОТЫ(D3:D9),»»,СУММ(C3:C9))

В качестве условия в функции ЕСЛИ мы используем СЧИТАТЬПУСТОТЫ (английский вариант — COUNTBLANK). Она возвращает количество пустых ячеек, но любое число больше 0 Excel интерпретирует как ИСТИНА.

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

=ЕСЛИ(ЕЧИСЛО(D3:D9),СУММ(C3:C9),»»)

Функция ЕЧИСЛО ( или ISNUMBER) возвращает ИСТИНА, если ссылается на число. Естественно, при ссылке на пустую ячейку возвратит ЛОЖЬ.

А теперь посмотрим, как это работает. Заполним таблицу недостающим значением.

Как видите, все наши формулы рассчитаны и возвратили одинаковые значения.

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

1. Итак, перед нами уже знакомая формула
2. =ЕСЛИ(СЧЁТЗ(D3:D9)=7,СУММ(C3:C9),»»)
3. Для функции СЧЕТЗ не имеет значения, число или текст используются в ячейке Excel.
4. =ЕСЛИ(СЧИТАТЬПУСТОТЫ(D3:D9),»»,СУММ(C3:C9))
5. То же можно сказать и о функции СЧИТАТЬПУСТОТЫ.

А вот третий вариант — к проверке условия при помощи функции ЕЧИСЛО добавляем проверку ЕТЕКСТ (ISTEXT в английском варианте). Объединяем их функцией ИЛИ.

=ЕСЛИ(ИЛИ(ЕТЕКСТ(D3:D9),ЕЧИСЛО(D3:D9)),СУММ(C3:C9),»»)

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

Надеемся, этот материал был полезен. А вот еще несколько примеров работы с условиями и функцией ЕСЛИ в Excel.

Функция ЕСЛИ: примеры с несколькими условиями — Для того, чтобы описать условие в функции ЕСЛИ, Excel позволяет использовать более сложные конструкции. В том числе можно использовать и несколько условий. Рассмотрим на примере. Для объединения нескольких условий в… Проверка ввода данных в Excel — Подтверждаем правильность ввода галочкой. Задача: При ручном вводе данных в ячейки таблицы проверять правильность ввода в соответствии с имеющимся списком допустимых значений. В случае правильного ввода в отдельном столбце ставить… Функция ЕСЛИ: проверяем условия с текстом — Рассмотрим использование функции ЕСЛИ в Excel в том случае, если в ячейке находится текст. СодержаниеПроверяем условие для полного совпадения текста.ЕСЛИ + СОВПАДИспользование функции ЕСЛИ с частичным совпадением текста.ЕСЛИ + ПОИСКЕСЛИ… Визуализация данных при помощи функции ЕСЛИ — Функцию ЕСЛИ можно использовать для вставки в таблицу символов, которые наглядно показывают происходящие с данными изменения. К примеру, мы хотим показать, происходит рост или снижение продаж. В столбце N поставим… Как функция ЕСЛИ работает с датами? — На первый взгляд может показаться, что функцию ЕСЛИ для работы с датами можно использовать так же, как для числовых и текстовых значений, которые мы только что обсудили. К сожалению, это…

Источник: https://mister-office.ru/formuly-excel/calculate-if-not-blank.html

Функция «ЕСЛИ» в Excel: пошаговая инструкция по работе

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

Принцип работы очень простой: если выражение истинное, то сделать это, иначе – сделать что-то другое.

То есть, в результате проверки условия может произойти два события:

• первое – при истине;
• второе – если возвращается ложь.

Рассмотрим основной синтаксис. При вызове окна «Вставка функции», ниже указывается описание каждого предложенного варианта.

Справка по функции «ЕСЛИ» выглядит следующим образом.

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

Как вставить функцию «ЕСЛИ»

Для этого нужно выполнить следующие действия:

1. Кликните на иконку вставки формул (Fx).
2. В появившемся меню выберите пункт «ЕСЛИ».

1. В результате этого вы увидите окно, в котором нужно указать аргументы функции:
   • логическое выражение;
   • значение, если истина;
   • значение, если ложь.
2. После ввода значений в эти поля, правее их будет отображаться предварительный результат.
3. Для вставки нажмите на кнопку «OK».

Примеры

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

1. Сделайте активной ячейку «А1».
2. Повторите описанные выше действия по вызову окна вставки «Аргументов».
3. Введите в поле условие следующий код.

C1=””

Смысл этого выражения в следующем: если ячейка C1 содержит пустоту. То есть в ней ничего не находится.

1. В поле для истины вводим следующее.

”Ячейка C1 пустая”

Именно этот текст будет выводиться в случае выполнения этого условия.

1. В поле «если ложь» вводим следующее.

”Ячейка C1 не пустая”

Данный текст мы увидим в случае ложного выполнения условия.

1. Для вставки нашей формулы нажимаем на кнопку «OK».

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

1. Введите любой текст в ячейку C.

1. После нажатия на кнопку Enter вы увидите следующий результат.

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

Операторы сравнения

Ниже вы видите список операторов сравнения:

Оператор	Значение	Пример использования
+	Сложение	5+5
–	Вычитание или отрицание	5-1 или -3
*	Умножение	5*5
/	Деление	5/5
%	Процент	50%
^	Степень	5^3

  Как в Excel объединить ячейки

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

А это таблица арифметических операторов:

=C3

Оператор	Значение	Пример использования
>	Больше	B3>C3

Источник: https://os-helper.ru/excel/funkciya-esli.html

Как добавить поле со списком да / нет для каждой ячейки в одном столбце в Excel 2010

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

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

Option Explicit
Sub insertCheckboxes()

Dim myBox As CheckBox
Dim myCell As Range

Dim cellRange As String
Dim cboxLabel As String
Dim linkedColumn As String

cellRange = InputBox(Prompt:=»Cell Range», _
Title:=»Cell Range»)

linkedColumn = InputBox(Prompt:=»Linked Column», _
Title:=»Linked Column»)

cboxLabel = InputBox(Prompt:=»Checkbox Label», _
Title:=»Checkbox Label»)

With ActiveSheet
For Each myCell In .Range(cellRange).Cells
With myCell
Set myBox = .Parent.CheckBoxes.Add(Top:=.Top, _
Width:=.Width, Left:=.Left, Height:=.Height)

With myBox
.LinkedCell = linkedColumn & myCell.Row
.Caption = cboxLabel
.Name = «checkbox_» & myCell.Address(0, 0)
End With

.NumberFormat = «;;;»
End With

Next myCell
End With
End Sub

Вы должны скопировать это в модуль VBA. Нажмите Alt + F11, чтобы открыть редактор VBA, выберите модуль текущей рабочей книги и вставьте этот код. Если в вашей текущей книге нет модуля, щелкните правой кнопкой мыши имя рабочей книги и используйте Insert -> New Module чтобы добавить его.

Вернитесь к своей книге и нажмите Alt-F8, чтобы запустить макрос. Если вы запустите макрос insertCheckboxes , вы получите следующий диалог:

Введите диапазон, в котором вы хотите установить флажки (например, A1: A10)

Введите столбец, к которому вы хотите привязать флажки (если вы выберете B, это означает, что столбец B будет показывать результат TRUE / FALSE флажка).

Введите нужную метку на флажки. Если вы хотите только коробку, оставьте это поле пустым.

• Есть несколько небольших проблем с VBA (например, он устанавливает ячейки, в которые устанавливаются флажки ;;; заставляет их ничего не отображать, вероятно, в случаях, когда вы связываете флажки с ячейками, в которых они расположены), но немного творческой инженерии должно помочь вам справиться с этим
• После того, как вы воспользовались макросом, вы можете удалить модуль — макрос просто создает флажки, их не нужно поддерживать.
• Примечание: приведенный выше код был протестирован и работал в Excel 2010 для меня. Ваша среда может отличаться

Источник: https://geekquestion.com/16240874-kak-dobavit-pole-so-spiskom-da-net-dlja-kazhdoj-jachejki-v-odnom-stolbce-v-excel-2010/

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

Сегодня мы рассмотрим функцию ЕСЛИ.

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

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

• Функция ЕСЛИ проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет.
• Синтаксис функции ЕСЛИ очень простой:
• ЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь])
• лог_выражение – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Что это значит?   Выражение при вычислении дает значение ИСТИНА если это выражение верно.

1. В этой части необходимо проверить на соответствие выражения.
2. Например:
3. =ЕСЛИ(А1=10; [значение_если_истина]; [значение_если_ложь])  — если А1 равно 10, то выражение  А1=10 даст значение ИСТИНА, а если не равно 10, то ЛОЖЬ
4. Другой пример
5. =ЕСЛИ(А1>30; [значение_если_истина]; [значение_если_ложь])  — если в ячейки А1 число больше 30, то А1>30 вернет ИСТИНА, а если меньше, то ЛОЖЬ
6. Еще пример
7. =ЕСЛИ(С1=”Да”; [значение_если_истина]; [значение_если_ложь])  — если в ячейки C1 содержится слово “Да” то выражение вернет значение ИСТИНА, а если нет, то С1=”Да” вернет ЛОЖЬ
8. Надеюсь с этим понятно, поехали дальше. Рассмотрим следующие компоненты функции ЕСЛИ
9. =ЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь])
10. значение_если_истина, значение_если_ложь – как видно из их названия, это то что необходимо сделать в зависимости от того, что вернул лог выражения: ИСТИНА и ЛОЖЬ

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

Рассмотрим использование функции ЕСЛИ на практическом примере. У нас есть таблица заказов, которую мы использовали при рассмотрении работы функции ВПР.

Нам необходимо заполнить столбец по заказам Ведер (ошибочно на картинке указано «Заказы Cтолов»), то есть необходимо выбрать только заказы с Ведрами.

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

• 
• Для решения поставленной задачи напишем формулу с использованием функции ЕСЛИ
• =ЕСЛИ(A3=»Ведро»;D3;»-«)
• и нажмем Enter
• 
• Как вы смогли заметить аргументы функции ЕСЛИ разделены точкой с запятой.

Итак, первый аргумент (лог выражения) A3=»Ведро» проверяет содержится ли в ячейке А3 слово «Ведро», если содержится, то выполняется второй аргумент функции ЕСЛИ (значение_если_истина), в нашем случае это D3 (т.е стоимость заказа),  если в ячейка А3 не равна слову «Ведро», то выполняется третий аргумент функции ЕСЛИ (значение_если_ложь), в нашем случае это «-» (т.е будет написано тире).

Таким образом, в ячейки E3 появится значение D3, т.е число 240.

Далее необходимо протянуть формулу вниз для остальных значений. Для этого необходимо выделить ячейку  Е3 и потянуть вниз за нижний правый угол (смотрите рисунок 3)

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

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

Спасибо за внимание.

Источник: https://sirexcel.ru/priemi-excel/funkciya-esli-v-excel-s-primerami/

Как добавить поле со списком да/нет для каждой ячейки в одном столбце в Excel 2010

В Excel нет встроенного способа генерации загрузки флагов Form, связанных с базовыми ячейками. Если вы скопируете один флажок, он будет иметь все те же свойства (включая связанную ячейку), что означает, что вам придется вручную редактировать его каждый раз. Чтобы добавить кучу сразу, вам нужно создать функцию VBA, чтобы сделать это для вас.

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

Option Explicit
Sub insertCheckboxes()

Dim myBox As CheckBox
Dim myCell As Range

Dim cellRange As String
Dim cboxLabel As String
Dim linkedColumn As String

cellRange = InputBox(Prompt:=»Cell Range», _
Title:=»Cell Range»)

linkedColumn = InputBox(Prompt:=»Linked Column», _
Title:=»Linked Column»)

cboxLabel = InputBox(Prompt:=»Checkbox Label», _
Title:=»Checkbox Label»)

With ActiveSheet
For Each myCell In .Range(cellRange).Cells
With myCell
Set myBox = .Parent.CheckBoxes.Add(Top:=.Top, _
Width:=.Width, Left:=.Left, Height:=.Height)

With myBox
.LinkedCell = linkedColumn & myCell.Row
.Caption = cboxLabel
.Name = «checkbox_» & myCell.Address(0, 0)
End With

.NumberFormat = «;;;»
End With

Next myCell
End With
End Sub

Вы должны скопировать это в модуль VBA. Нажмите Alt + F11, чтобы открыть редактор VBA, выберите модуль вашей текущей книги и вставьте этот код. Если в вашей текущей книге нет модуля, щелкните правой кнопкой мыши имя книги и используйте Insert → New Module, чтобы добавить один.

Вернитесь к своей книге и нажмите Alt-F8, чтобы запустить макрос. Если вы запустите макрос insertCheckboxes, вы получите следующий диалог:

Введите диапазон, в который вы хотите поставить флажки (например, A1: A10)

Введите столбец, к которому вы хотите привязать флажки (если вы выберете B, это означает, что столбец B отобразит результат TRUE/FALSE).

Введите ярлык, который вы хотите на флажках. Если вы хотите только поле, оставьте поле пустым.

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

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

Примечание. Вышеприведенный код был протестирован и работал в Excel 2010 для меня. Ваша среда может отличаться

Источник: http://qaru.site/questions/3093449/how-to-add-yesno-combo-box-to-the-each-cells-in-one-column-in-excel-2010

Как не добавить да / нет выпадающего на каждую ячейку в одном столбце в Excel 2010

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

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

Option Explicit
Sub insertCheckboxes()
Dim myBox As CheckBox Dim myCell As Range
Dim cellRange As String Dim cboxLabel As String Dim linkedColumn As String
cellRange = InputBox(Prompt:=»Cell Range», _ Title:=»Cell Range»)
linkedColumn = InputBox(Prompt:=»Linked Column», _ Title:=»Linked Column»)
cboxLabel = InputBox(Prompt:=»Checkbox Label», _ Title:=»Checkbox Label»)
With ActiveSheet For Each myCell In .Range(cellRange).Cells With myCell Set myBox = .Parent.CheckBoxes.Add(Top:=.Top, _ Width:=.Width, Left:=.Left, Height:=.Height)
With myBox .LinkedCell = linkedColumn & myCell.Row .Caption = cboxLabel .Name = «checkbox_» & myCell.Address(0, 0) End With
.NumberFormat = «;;;» End With
Next myCell End With
End Sub

Вы должны скопировать и модуль VBA. Нажмите Alt + F11 , чтобы открыть редактор VBA, выберите модуль текущей книги, и вставить этот код в случае , если нет модуля в текущей книге, щелкните правой кнопкой мыши имя рабочей книги и использование. Insert-> New Moduleдобавить один.

Вернитесь в книгу и нажмите Alt-F8 для запуска макроса. Если запустить insertCheckboxesмакрос, вы получите следующий диалог:

Введите требуемый диапазон флажков (например, A1: A10)

Введите столбец, который флажки быть связаны (если вы выбрали B, это означает , что столбец B будет показывать TRUE/ FALSEрезультат флажка).

Введите метку, которую флажки. Если вы хотите просто поле, оставьте его пустым.

• Есть несколько небольших проблемы с VBA (например, он устанавливает клетку флажки идут в как ;;;сделать их не показывают ничего, вероятно , за исключением случаев , когда вы связываете флажки клетки они находятся в), но немного творческая инженерии должны получить вас вокруг этого.
• После того, как вы использовали макрос, вы можете удалить модуль — макрос просто создает флажки, не требуется, чтобы поддерживать их.
• Примечание: Приведенный выше код был проверен и работал в Excel 2010 для меня. Среда обитания может варьироваться

Источник: https://ru.coredump.biz/questions/3087037/how-to-add-yesno-combo-box-to-the-each-cells-in-one-column-in-excel-2010

Функция ЕСЛИ() в MS EXCEL

Функция ЕСЛИ(), английский вариант IF(), используется при проверке условий. Например, =ЕСЛИ(A1>100;»Бюджет превышен»;»ОК!»). В зависимости от значения в ячейке А1 результат формулы будет либо «Бюджет превышен» либо «ОК!».

Функция ЕСЛИ()относится к наиболее часто используемым функциям.

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

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

Лог_выражение   — любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. =ЕСЛИ(A1>=100;»Бюджет превышен»;»ОК!») Т.е. если в ячейке A1 содержится значение большее или равное 100, то формула вернет ОК!, а если нет, то Бюджет превышен.

В качестве аргументов функции, могут участвовать формулы, например: =ЕСЛИ(A1>100;СУММ(B1:B10);СУММ(C1:C10)) Т.е. если в ячейке A1 содержится значение >100, то суммирование ведется по столбцу B, а если меньше, то по столбцу С.

Вложенные ЕСЛИ

• В EXCEL 2007 в качестве значений аргументов значение_если_истина и значение_если_ложь можно для построения более сложных проверок использовать до 64 вложенных друг в друга функций ЕСЛИ(). =ЕСЛИ(A1>=100;»Бюджет превышен»;ЕСЛИ(A1>=90;»Крупный проект»;ЕСЛИ(A1>=50;»Средний проект»;»Малый проект «)))
• Вышеуказанную формулу можно усложнять и дальше, но есть другие подходы:
• =ПРОСМОТР(A1;{0;50;90;100};{«Малый проект»;»Средний проект»;»Крупный проект»;»Бюджет превышен»})
• =ВПР(A1;A3:B6;2)
• Для функции ВПР() необходимо создать в диапазоне A3:B6 таблицу значений:

1. Если требуется вывести разный текст в случае наличия в ячейке А1 отрицательного значения, положительного значения или 0, то можно записать следующую формулу:
2. =ПРОСМОТР(A1;{-1E+307;0;1E-307};{«0»})
3. или, если требуется вместо текстовых значений вывести формулы, можно использовать ссылки на диапазон ячеек (содержащих формулы)
4. =ПРОСМОТР(A24;{-1E+307;0;1E-307};A27:A29) (см. файл примера)

Опущен третий аргумент [значение_если_ложь]

Третий аргумент функции не обязателен, если его опустить, то функция вернет значение ЛОЖЬ (если условие не выполнено). =ЕСЛИ(A1>100;»Бюджет превышен») Если в ячейке A1 содержится значение 1, то вышеуказанная формула вернет значение ЛОЖЬ.

Вместо ИСТИНА или ЛОЖЬ в первом аргументе введено число

Т.к. значение ЛОЖЬ эквивалентно 0, то формулы

=ЕСЛИ(0;»Бюджет превышен»;»ОК!») или (если в ячейке A1 содержится значение 0) =ЕСЛИ(A1;»Бюджет превышен»;»ОК!») вернут ОК!

Если в ячейке A1 находится любое другое число кроме 0, то формула вернет Бюджет превышен. Такой подход удобен, когда проверяется равенство значения нулю.

Связь функции ЕСЛИ() с другими функциями использующие условия

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

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

Пусть данные находятся в диапазоне A6:A11 (см. файл примера)

• Подсчитаем сумму значений, которые больше 10 с помощью функции СУММЕСЛИ(), записав =СУММЕСЛИ(A6:A11;»>10″). Аналогичный результат (23) можно получить с помощью формулы массива =СУММ(ЕСЛИ(A6:A11>10;A6:A11)) (для ввода формулы в ячейку вместо ENTER нужно нажать CTRL+SHIFT+ENTER)
• Теперь подсчитаем количество вхождений чисел больше 10 в диапазоне ячеек A6:A11 =СЧЁТЕСЛИ(A6:A11;»>10″). Аналогичный результат (2) можно получить с помощью формулы массива =СЧЁТ(ЕСЛИ(A6:A11>10;A6:A11))
• Теперь, когда принцип понятен, с помощью функции ЕСЛИ() можно конструировать и другие формулы с условиями. Например, нахождение минимального значения среди чисел больше 10: =МИН(ЕСЛИ(A6:A11>10;A6:A11))

Источник: https://excel2.ru/articles/funkciya-esli-v-ms-excel-esli

Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий

Логическая функция ЕСЛИ в Экселе – одна из самых востребованных. Она возвращает результат (значение или другую формулу) в зависимости от условия.

Функция ЕСЛИ в Excel

Функция имеет следующий синтаксис.

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

лог_выражение – это проверяемое условие. Например, A230) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

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

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

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

В прогнозе запасов больше нет отрицательных значений, что в целом очень неплохо.

Формулы Excel ЕСЛИ также активно используют в формулах массивов. Здесь мы не будем далеко углубляться.

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

в Excel 2016 появились функции МИНЕСЛИ и МАКСЕСЛИ. Но для примера очень полезно ознакомиться – пригодится в другой ситуации.

Формула ЕСЛИ в Excel – примеры нескольких условий

Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.

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

Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура.

Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим.

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

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

Источник: https://statanaliz.info/excel/funktsii-i-formuly/neskolko-uslovij-funktsii-esli-eslimn-excel/

Как в ячейке с формулой вместо ошибки показать 0

Хитрости » 7 Октябрь 2015       Дмитрий       20587 просмотров

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

Например #ДЕЛ/0!(#DIV/0!). Эта ошибка возникает, если внутри формулы происходит деление на ноль: =A1/B1, где в B1 ноль или пусто. Но могут быть и другие ошибки(#Н/Д, #ЗНАЧ! и т.д.).

Можно изменить формулу, добавив проверку на ошибку:
=ЕСЛИ(ЕОШ(A1/B1);0; A1/B1)
=IF(ISERR(A1/B1),0, A1/B1)
аргументы:

=ЕСЛИ(ЕОШ(1 аргумент);2 аргумент; 1 аргумент)

Эти формулы будут работать в любой версии Excel. Правда, функция ЕОШ не обработает ошибку #Н/Д(#N/A). Чтобы так же обработать и #Н/Д необходимо использовать функцию ЕОШИБКА:
=ЕСЛИ(ЕОШИБКА(A1/B1);0; A1/B1)
=IF(ISERROR(A1/B1),0, A1/B1)
Однако далее по тексту я буду применять ЕОШ(т.к. она короче) и к тому же не всегда надо «не видеть» ошибки #Н/Д.
Но для версий Excel 2007 и выше можно применить чуть более оптимизированную функцию ЕСЛИОШИБКА(IFERROR):
=ЕСЛИОШИБКА(A1/B1;0)
=IFERROR(A1/B1,0)
аргументы:
=ЕСЛИОШИБКА(1 аргумент; 2 аргумент)
1 аргумент: выражение для вычисления

Почему ЕСЛИОШИБКА лучше и я называю её более оптимизированной? Разберем первую формулу подробнее:
=ЕСЛИ(ЕОШ(A1/B1);0; A1/B1)
Если вычислить пошагово, то увидим, что сначала происходит вычисление выражения A1/B1(т.е. деление). И если его результат ошибка – то ЕОШ вернет ИСТИНА(TRUE), которое будет передано в ЕСЛИ(IF). И тогда функцией ЕСЛИ(IF) будет возвращено значение из второго аргумента 0.
Но если результат не является ошибочным и ЕОШ(ISERR) возвращает ЛОЖЬ(FALSE) – то функция заново будет вычислять уже вычисленное ранее выражение: A1/B1
С приведенной формулой это особой роли не играет. Но если применяется формула вроде ВПР (VLOOKUP) с просмотром на несколько тысяч строк – то вычисление два раза может значительно увеличить время пересчета формул.
Функция же ЕСЛИОШИБКА(IFERROR) один раз вычисляет выражение, запоминает его результат и если он ошибочен возвращает записанное вторым аргументом. Если же ошибки нет, то возвращает запомненный результат вычисления выражения из первого аргумента. Т.е. вычисление по факту происходит один раз, что практически не будет влиять на скорость общего пересчета формул.
Поэтому если у вас Excel 2007 и выше и файл не будет использоваться в более ранних версиях – то имеет смысл использовать именно ЕСЛИОШИБКА(IFERROR).

Для чего формулы с ошибками вообще исправлять? Обычно делается для более эстетичного отображения данных в отчетах, особенно если отчеты потом руководству отправляют.

Итак, есть на листе такие формулы, ошибки которых надо обработать. Если подобных формул для исправления одна-две(да даже 10-15) – то проблем почти нет заменить вручную. Но если таких формул несколько десятков, а то и сотен – проблема приобретает почти вселенские масштабы :-). Однако процесс можно упростить через написание относительно простого кода Visual Basic for Application.
Для всех версий Excel:

Sub IfIsErrNull() Const sToReturnVal As String = «0» 'если необходимо вместо нуля возвращать пусто 'Const sToReturnVal As String = «»»»»» Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect(Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox «Выделенный диапазон не содержит данных», vbInformation, «www.excel-vba.ru» Exit Sub End If   For Each rc In rr If rc.HasFormula Then s = rc.Formula s = Mid(s, 2) ss = «=» & «IF(ISERR(» & s & «),» & sToReturnVal & «,» & s & «)» If Left(s, 9) «IF(ISERR(» Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err.Number Then MsgBox «Невозможно преобразовать формулу в ячейке: » & ss & vbNewLine & _ Err.Description, vbInformation, «www.excel-vba.ru» Else MsgBox «Формулы обработаны», vbInformation, «www.excel-vba.ru» End If End Sub

Sub IfIsErrNull() Const sToReturnVal As String = «0» 'если необходимо вместо нуля возвращать пусто 'Const sToReturnVal As String = «»»»»» Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect(Selection, ActiveSheet.

UsedRange) If rr Is Nothing Then MsgBox «Выделенный диапазон не содержит данных», vbInformation, «www.excel-vba.ru» Exit Sub End If For Each rc In rr If rc.HasFormula Then s = rc.Formula s = Mid(s, 2) ss = «=» & «IF(ISERR(» & s & «),» & sToReturnVal & «,» & s & «)» If Left(s, 9) «IF(ISERR(» Then If rc.

HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err.Number Then MsgBox «Невозможно преобразовать формулу в ячейке: » & ss & vbNewLine & _ Err.Description, vbInformation, «www.excel-vba.

ru» Else MsgBox «Формулы обработаны», vbInformation, «www.excel-vba.ru» End If End Sub

Для версий 2007 и выше

Sub IfErrorNull() Const sToReturnVal As String = «0» 'если необходимо вместо нуля возвращать пусто 'Const sToReturnVal As String = «»»»»» Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect(Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox «Выделенный диапазон не содержит данных», vbInformation, «www.excel-vba.ru» Exit Sub End If   For Each rc In rr If rc.HasFormula Then s = rc.Formula s = Mid(s, 2) ss = «=» & «IFERROR(» & s & «,» & sToReturnVal & «)» If Left(s, 8) «IFERROR(» Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc   If Err.Number Then MsgBox «Невозможно преобразовать формулу в ячейке: » & ss & vbNewLine & _ Err.Description, vbInformation, «www.excel-vba.ru» Else MsgBox «Формулы обработаны», vbInformation, «www.excel-vba.ru» End If End Sub

Sub IfErrorNull() Const sToReturnVal As String = «0» 'если необходимо вместо нуля возвращать пусто 'Const sToReturnVal As String = «»»»»» Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect(Selection, ActiveSheet.

UsedRange) If rr Is Nothing Then MsgBox «Выделенный диапазон не содержит данных», vbInformation, «www.excel-vba.ru» Exit Sub End If For Each rc In rr If rc.HasFormula Then s = rc.Formula s = Mid(s, 2) ss = «=» & «IFERROR(» & s & «,» & sToReturnVal & «)» If Left(s, 8) «IFERROR(» Then If rc.HasArray Then rc.

FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err.Number Then MsgBox «Невозможно преобразовать формулу в ячейке: » & ss & vbNewLine & _ Err.Description, vbInformation, «www.excel-vba.

ru» Else MsgBox «Формулы обработаны», vbInformation, «www.excel-vba.ru» End If End Sub

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

Копируете приведенный код, переходите в редактор VBA(Alt+F11), создаете стандартный модуль(Insert —Module) и просто вставляете в него этот код.

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

Жмете Alt+F8, выбираете код IfIsErrNull(или IfErrorNull, в зависимости от того, какой именно скопировали) и жмете Выполнить.
Ко всем формулам в выделенных ячейках будет добавлена функция обработки ошибки.

Приведенные коды учитывают так же:
-если в формуле уже применена функция ЕСЛИОШИБКА или ЕСЛИ(ЕОШ, то такая формула не обрабатывается;
-код корректно обработает так же функции массива;
-выделять можно несмежные ячейки(через Ctrl).

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

Если значение ошибки надо заменить на пусто, а не на ноль, то надо строку

Const sToReturnVal As String = «0»

Const sToReturnVal As String = «0»

Удалить, а перед строкой

'Const sToReturnVal As String = «»»»»»

'Const sToReturnVal As String = «»»»»»

Удалить апостроф (')

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

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

Потому как если формула возвращает #ИМЯ!(#NAME!), то это означает, что в формуле неверно записан какой-то аргумент и это ошибка записи формулы, а не ошибка результата вычисления.

Статья помогла? Поделись ссылкой с друзьями!

Источник: https://www.excel-vba.ru/chto-umeet-excel/kak-v-yachejke-s-formuloj-vmesto-oshibki-pokazat-0/

Нужна формула Excel, чтобы вернуть «Да» / «Нет» вместо логического

У меня есть эта проблема: Напишите формулу (используя функцию OR ), которая возвращает следующее:

если столбец D = «синий» ИЛИ столбец E> = 4, затем «да», иначе «нет»,

Мое решение возвращает TRUE или FALSE . Как я могу заставить функцию возвращать «да» или «нет»?

Вам нужно обернуть его в IF :

=IF(OR(D1=»Blue»,E1>=4),»Yes»,»No»)

Page 2

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

У меня есть файл Excel, который я пытаюсь добавить для проверки, чтобы остановить пользователя, если условия не выполнены. Ниже приведена моя формула, которая не возвращает ожидаемые результаты: =IF(A1=»»,TRUE,IF((B1*5)>A2,True,FALSE)) Вот что я пытаюсь сделать: 1) If A1 is Blank, then don't allow the user to enter anything in A2 2) The Max the user can enter […]

Какую функцию использовать в excel

im работает над таблицей Excel, которая работает как система инвентаризации. У меня есть 1 значение, которое мне нужно посмотреть (штрих-код), а затем мне нужно это значение, чтобы отбросить 6 строк данных в разных ячейках. Я уже пробовал «= VLOOKUP», но его не работает для меня sinse, он только тянет 1 ячейку. Я использую неправильную команду? […]

Excel, добавьте один диапазон в конец другого в один столбец

У меня есть два столбца данных в Excel. Я хотел бы добавить третий столбец, который объединяет первый и второй. Как я могу сделать это с помощью такой формулы, чтобы я мог добавлять или удалять данные из столбцов A и B без необходимости касаться столбца C? Column A Column B Column C Bob Mary Bob Joe […]

Мне нужно сделать Vlookup из объединенной ячейки из более чем 1 строки

Мне нужно сделать Vlookup из объединенной ячейки из более чем 1 строки. Я думаю, что это ясно в образах. sheet 1 который мне нужен, чтобы информация была на нем в одной ячейке. sheet 2 который включает информацию.

Числовое значение в имени файла, вызывающее ошибку OnAction

Я использую следующий код, чтобы назначить новый макрос кнопке: ActiveSheet.Shapes(«ButtonI»).OnAction = AName & «!Import_to_Master» где AName – это имя файла в формате: «Tool – IP1.

xls» В настоящее время он подбрасывает: Ошибка времени выполнения «1004»: не удается запустить макрос «Инструмент – IP1.xls! Import_to_Master». Нет ошибки, если в имени файла нет числового значения, т.

«Tool – IP.xls» […]

Поиск нескольких совпадений и получение данных на основе набора критериев

Я искал по всему миру и, похоже, не могу найти то, что хочу. Я пробовал использовать INDEX и MATCH, но он только подтягивает одно совпадение. Массивы будут работать, но я не могу понять, как выполнять несколько запросов массива. Итак, краткое изложение того, что мне нужно. У меня две таблицы. Один содержит заказы для пункта 1 […]

Запустить макрос в общей книге и защитить его

Я хочу запустить макрос в общей книге. Мой макрос, как показано ниже. Sheets(«Sheet1»).Unprotect Range(«A1»).Select ActiveCell.FormulaR1C1 = «ss» Sheets(«Sheet1»).

Protect Когда я запускаю макрос без совместного использования, он работает нормально. Когда делитесь им ниже подсказок об ошибках: Сбросить метод класса Worksheet не удалось.

Может ли кто-нибудь помочь запустить мой макрос в общей среде и сохранить листок в […]

Значение ячейки ячейки заголовка, присвоенное значению дочернего элемента, не отражается после изменения дочернего значения

В Excel у меня есть ячейка ( N15 ) и еще три ячейки ( N20, N21, N22 ). Здесь, в Excel, если я присвою значение N15, это должно отразиться в ячейках N20, N21, N22 ( =N15 ). Я также могу изменить дочерние значения после изменения дочерних значений, если изменить значение заголовка, которое оно не отражает […]

Уникальный отличительный список добавляет дополнительные строки '0'

В настоящее время я работаю над созданием вкладки Excel, которая объединяет два разных столбца данных в уникальный список. Это было рассмотрено в другом месте; моя формула исходит из « Извлеките уникальный отчетный список из двух столбцов … » Сама формула, чтобы не повторять подход, но, по крайней мере, дать здесь достаточно подробностей, заключается в следующем: […]

Удаление ведущих нулей в Find and Replace

Интересно, можно ли заставить Excel не удалять ведущие нули при использовании параметра «Найти и заменить». Я хочу использовать «Найти и заменить», чтобы изменить имена на свои коды (01, 02 и т. Д.). Некоторые коды начинаются с 0. У меня есть три возможности: форматируют ячейки как «нестандартные: 00» – Excel сохраняет, например, 1 как 1 но […]

Источник: https://excel.bilee.com/excel-2799.html