Ячейка excel функция: ЯЧЕЙКА (функция ЯЧЕЙКА) — Служба поддержки Майкрософт

Как сделать условие в ячейке excel?

Содержание

• 1 Синтаксис функции ЕСЛИ с одним условием
• 2 Функция ЕСЛИ в Excel с несколькими условиями
• 3 Расширение функционала с помощью операторов «И» и «ИЛИ»
• 4 Как сравнить данные в двух таблицах
• 5 Функция ЕСЛИ
  • 5.1 Использование условия «И»
  • 5.2 Использование условия «ИЛИ»
• 6 СУММЕСЛИ
• 7 СУММЕСЛИМН
• 8 СЧЕТЕСЛИ
• 9 СЧЕТЕСЛИМН
• 10 Функция ПОИСКПОЗ
• 11 Использование условий в VBA
• 12 Заключение
• 13 Видеоинструкция
• 14 Как использовать функцию
  • 14.1 Пример №1
  • 14.2 Пример №2
• 15 Используем несколько условий
• 16 Другие варианты использования функции
  • 16.1 Находим сумму столбцов или ячеек
  • 16.2 Количество повторений элементов на листе
  • 16.3 Используем СЧЕТЕСЛИ и СУММЕСЛИ при подсчете данных
  • 16.4 Поиск нескольких вхождений элемента в списке
  • 16.5 Как сократить количество вложений ЕСЛИ

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

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

Синтаксис функции ЕСЛИ с одним условием

Синтаксис оператора в Excel – строение функции, необходимые для ее работы данные.

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

Разберем синтаксис функции:

Логическое_выражение – ЧТО оператор проверяет (текстовые либо числовые данные ячейки).

Значение_если_истина – ЧТО появится в ячейке, когда текст или число отвечают заданному условию (правдивы).

Значение,если_ложь – ЧТО появится в графе, когда текст или число НЕ отвечают заданному условию (лживы).

Пример:

Оператор проверяет ячейку А1 и сравнивает ее с 20. Это «логическое_выражение». Когда содержимое графы больше 20, появляется истинная надпись «больше 20». Нет – «меньше или равно 20».

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

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

Обратите внимание: оператор ЕСЛИ должен проверить не цифровой тип данных, а текстовый. Поэтому мы прописали в формуле В2= «зач.». В кавычки берем, чтобы программа правильно распознала текст.

Функция ЕСЛИ в Excel с несколькими условиями

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

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

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

Здесь оператор проверяет два параметра. Если первое условие истинно, то формула возвращает первый аргумент – истину. Ложно – оператор проверяет второе условие.

Примеры несколько условий функции ЕСЛИ в Excel:

Таблица для анализа успеваемости. Ученик получил 5 баллов – «отлично». 4 – «хорошо». 3 – «удовлетворительно». Оператор ЕСЛИ проверяет 2 условия: равенство значения в ячейке 5 и 4.

В этом примере мы добавили третье условие, подразумевающее наличие в табеле успеваемости еще и «двоек». Принцип «срабатывания» оператора ЕСЛИ тот же.

Расширение функционала с помощью операторов «И» и «ИЛИ»

Когда нужно проверить несколько истинных условий, используется функция И. Суть такова: ЕСЛИ а = 1 И а = 2 ТОГДА значение в ИНАЧЕ значение с.

Функция ИЛИ проверяет условие 1 или условие 2. Как только хотя бы одно условие истинно, то результат будет истинным. Суть такова: ЕСЛИ а = 1 ИЛИ а = 2 ТОГДА значение в ИНАЧЕ значение с.

Функции И и ИЛИ могут проверить до 30 условий.

Пример использования оператора И:

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

Как сравнить данные в двух таблицах

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

Чтобы сравнить 2 таблицы в Excel, можно воспользоваться оператором СЧЕТЕСЛИ. Рассмотрим порядок применения функции.

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

Исходные данные (таблицы, с которыми будем работать):

Выделяем первую таблицу. Условное форматирование – создать правило – использовать формулу для определения форматируемых ячеек:

В строку формул записываем: =СЧЕТЕСЛИ (сравниваемый диапазон; первая ячейка первой таблицы)=0. Сравниваемый диапазон – это вторая таблица.

Чтобы вбить в формулу диапазон, просто выделяем его первую ячейку и последнюю. «= 0» означает команду поиска точных (а не приблизительных) значений.

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

Выделяем вторую таблицу. Условное форматирование – создать правило – использовать формулу. Применяем тот же оператор (СЧЕТЕСЛИ).

Скачать все примеры функции ЕСЛИ в Excel

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

Microsoft Excel – довольно мощный редактор для работы с таблицами. При этом его возможности не ограничиваются только этим. Работать с текстом можно и в редакторе Word, но в нем нет динамики. Именно поэтому для анализа информации лучше всего использовать Эксель. В данной статье мы рассмотрим функцию ЕСЛИ с несколькими условиями. В Excel стандартных примеров нет, поэтому будем использовать что-то своё.

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

Функция ЕСЛИ

Принцип действия довольно простой. Вы указываете какое-нибудь условие и что нужно делать в случаях истины и лжи.

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

Полное описание можно увидеть в окне «Вставка функции».

1. Нажмите на иконку
2. Выберите категорию «Полный алфавитный перечень».
3. Найдите там пункт «ЕСЛИ».
4. Сразу после этого вы увидите описание функции.

Далее появится окно, в котором требуется указать «Аргументы функции» (логическое выражение, значение если истина и значение если ложь).

В качестве примера добавим столбец с премией для учителей высшей категории.

Затем необходимо выполнить следующие действия.

1. Перейдите на первую ячейку. Нажмите на иконку «Fx». Найдите там функцию «ЕСЛИ» (её можно отыскать в категории «Полный алфавитный указатель»). Затем кликните на кнопку «OK».

2. В результате этого появится следующее окно.

3. В поле логическое выражение введите следующую формулу.

D3=»Высшая»

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

4. После подстановки вы увидите, что данное выражение ложно.

5. Затем указываем значения дли «Истины» и «Лжи». В первом случае какое-то число, а во втором – ноль.

6. После этого мы увидим, что логический смысл выражения будет ложным.

7. Для сохранения нажимаем на кнопку «OK».

8. В результате использования этой функции вы увидите следующее.

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

1. Наведите курсор в правый нижний угол первой ячейки.
2. Если вы сделали всё правильно, то он превратится в черный крестик.

3. Теперь сделайте левый клик мыши и не отпуская палец опуститесь до конца таблицы.

4. В результате этого вы увидите следующее.

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

В данном случае информации не так много. А теперь представьте, что такая таблица будет огромной. Ведь в организации всегда работает большое количество людей. Если работать в редакторе Word и делать такое сравнение квалификации сотрудников вручную, то кто-нибудь (вследствие ошибок, связанных с человеческим фактором) будет выпадать из списка. Формула в Экселе никогда не ошибется.

Использование условия «И»

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

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

1. Кликните на первую ячейку в столбце «Премия».
2. Затем нажмите на иконку «Fx».

3. Сразу после этого появится окно с используемой функцией со всеми указанными аргументами. Таким образом редактировать намного проще – непосредственно в ячейке.

4. В графе логическое выражение укажите следующую формулу. Для сохранения изменений нажмите на кнопку «OK».

И(D3=»Высшая»;E3=»Математика»)

6. Скопируйте эту формулу во все остальные ячейки.

7. Теперь премия будет только у одного человека, поскольку только у него высшая категория и он математик.

Использование условия «ИЛИ»

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

1. Перейдите в первую ячейку.
2. Кликните на иконку «Fx».

3. Текущее логическое выражение нас не устраивает.

4. Нужно будет поменять его на следующее.

ИЛИ(D3=»Первая»;D3=»Вторая»)

5. Сразу после этого мы увидим, что у первого преподавателя появилась премия, поскольку у него первая категория.

6. Дублируем эту формулу в остальные ячейки.

В результате этого мы увидим следующее.

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

СУММЕСЛИ

В качестве демонстрации данного инструмента попробуем посчитать общую зарплату для учителей первой категории. Для этого создадим еще одну строку.

Затем выполним следующие шаги.

1. Переходим на соседнюю клетку и вызываем функцию (повторяем описанные выше действия) СУММЕСЛИ. Активной должна быть именно та ячейка, в которой будет выводиться сумма.

2. Сразу после этого вы увидите окно, в котором нужно указать аргументы функции:
   • диапазон;
   • критерий;
   • диапазон суммирования.

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

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

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

6. Затем нам необходимо выделить графу зарплата. Но перед этим кликаем на последнее поле – нужно, чтобы оно стало активным.

7. Только после этого можно указывать нужный диапазон.

8. Как только вы отпустите палец, увидите следующее.

9. Для сохранения формулы достаточно кликнуть на кнопку «OK».

В результате мы увидим, что общая зарплата учителей с первой категорией составила 1200.

СУММЕСЛИМН

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

Для этого необходимо сделать следующее.

1. Добавим строчку, где будем производить расчёты.

2. Переходим в нужную клетку и кликаем на иконку «Fx». Находим нужную функцию и нажимаем на кнопку «OK».

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

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

5. Теперь указываем «Диапазон условия 1». Например, здесь можно указать категорию сотрудников. Перед выделением обязательно кликните на это поле, иначе выделенные ячейки попадут в первую строку. Сразу после этого вы увидите новое поле для ввода.

6. Введите туда слово «Первая». Сразу после этого вы увидите поля для второго условия.

7. Делаем клик на поле «Диапазон условия 2». Затем выделяем столбик с предметом.

8. В строку «Условие 2» напишите нужный вам предмет. В данном случае – «Математика». Для того чтобы увидеть корректность вашей формулы, достаточно будет кликнуть в любое уже ранее указанное поле. В результате этого второе условие окажется в кавычках (согласно правилам) и вы увидите результат данной функции.

Для сохранения формулы необходимо нажать на кнопку «OK».

В итоге вы увидите следующее.

СЧЕТЕСЛИ

В редакторе Эксель для расчётов количества ячеек можно использовать формулу «СЧЕТ». Но как быть, если нужно посчитать не все ячейки? Более того, позиция нужных клеток заранее неизвестна, поскольку таблица может содержать большое количество данных.

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

1. Добавим новую строку. Сделаем активной нужную ячейку и кликнем на иконку вставки функции.

2. В появившемся окне находим нужную формулу и нажимаем на кнопку «OK».

3. Затем вас попросят указать диапазон и критерий отбора.

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

5. Затем в поле критерий указываем слово «Высшая». Для сохранения нажмите на кнопку «OK».

6. В результате этого вы увидите следующее.

СЧЕТЕСЛИМН

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

1. Добавляем новую строку для расчётов. Кликаем на нужную ячейку и вызываем окно «Вставка функции». Находим нужную и кликаем на кнопку «OK».

2. В графу «Диапазон условия» указываем поле «Категория». Для этого достаточно выделить нужные ячейки.

3. После клика в поле «Условие 1» у вас появится строка для второго диапазона.

4. Введите нужную категорию учителя. В данном случае – «Высшая».

5. После этого сделайте клик в поле «Диапазон условия 2» и выделите столбец с названием предмета.

6. Затем в последнее поле указываем слово «Математика». Для сохранения нажимаем на кнопку «OK».

7. Результат будет следующим.

Расчёт произошел корректно. В нашей таблице всего 1 преподаватель математики с высшей категорией.

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

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

Для этого нужно сделать следующее.

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

2. В появившемся окне нужно будет указать:
   • искомое значение;
   • просматриваемый массив;
   • тип сопоставления.

3. В первое поле достаточно написать слово «География», поскольку именно этого преподавателя мы ищем.

4. Затем нужно выделить столбец с названием предметов (предварительно кликнув на второе поле).

5. В третьем параметре укажите цифру «0». Для сохранения нажмите на кнопку «OK».

6. В результате этого мы увидели, что ячейка со словом «География» находится под номером 8 (среди указанного диапазона).

Более подробно про третий параметр можно прочитать на официальном сайте центра поддержки компании Microsoft. Там написано следующее.

Использование условий в VBA

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

Для этого необходимо выполнить следующие шаги.

1. По умолчанию вкладка с макросами скрыта от пользователей. Её нужно открыть. Нажмите на пункт меню «Файл».

2. Перейдите в раздел «Параметры».

3. В появившемся окне выберите категорию «Настроить ленту». Затем поставьте галочку возле пункта «Разработчик». Для сохранения нажмите на кнопку «OK».

4. Сразу после этого вы увидите, что указанная вкладка появилась на панели инструментов.

5. Перейдите на неё и нажмите на кнопку «Visual Basic».

6. Сразу после этого появится окно для написания кода.

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

8. Введите следующий код:

Sub ProverkaPoiskaUchiteley() If = 0 Then MsgBox «Учителя не найдены» End If If > 0 Then MsgBox «Учителя найдены» End If End Sub

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

9. Закройте этот редактор. Теперь кликните на иконку «Макросы».

10. В появившемся окне нажмите на кнопку «Выполнить».

11. В результате этого вы увидите сообщение о том, что учителя найдены, поскольку в ячейке «F17» содержится число больше нуля.

12. Если вы измените значение этой ячейки на «0», то увидите совсем другой результат.

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

1. Снова нажмите на иконку «Макросы». В появившемся окне нажмите на кнопку «Параметры».

2. Сразу после этого вам предложат указать какую-нибудь кнопку и описание к этому макросу.

3. Сочетания клавиш необязательно должны быть только с клавишей Ctrl. Можно использовать дополнительное сочетание с кнопкой Shift. В качестве примера назначим комбинацию Ctrl+ Shift+ E. Для сохранения нажимаем на «OK».

4. Закройте это окошко. Теперь нажмите на сочетание клавиш Ctrl+Shift+ E. В результате этого вы увидите сообщение о результате проверки. Так намного удобнее, чем каждый раз заходить в меню.

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

Заключение

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

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

Видеоинструкция

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

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

ЕСЛИ условие истинно, то выполняем это, а иначе делаем что-либо еще

Синтаксис этой функции достаточно прост:

ЕСЛИ (логическое_условие; значение_в_случае_ИСТИНА;  значение в случае_ЛОЖЬ)

Разберем подробнее:

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

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

Пример №1

На скриншотах ниже показан самый просто вариант использования. Мы сначала проверяем заданное условие А1>25. ЕСЛИ это требование выполняется, тогда выводим в ячейку «больше 25», иначе «меньше или равно 25».

Пример №2

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

Нам необходимо в столбце С вывести результаты переаттестации, которые могут принимать бинарное значение: СДАЛ или НЕ СДАЛ. Критерии у нас будут такими: кто набрал более 45 балов, тот считается сдавшим экзамен, ну а все остальные нет.

Для выполнения задачи необходимо:

1. Выбрать ту ячейку, в которой будем в водить формулу. У нас это С3.
2. Затем определяемся с необходимыми параметрами:
   • Логическое_условие – B3>45
   • Значение_в_случае_ИСТИНА – Сдал
   • Значение_в_случае_ЛОЖЬ – не сдал
3. Таким образом, получаем формулу =ЕСЛИ (В3>45; «Сдал»; «Не сдал»)
4. Формула будет сравнивать число в ячейке В3 и 45. Если наше требование выполняется и Баллы больше 45, то в ячейке результата мы увидим «Сдал», иначе «Не сдал».
5. Копируем формулу в каждую ячейку удобным способом, и смотрим результат.

Используем несколько условий

При необходимости, функцию ЕСЛИ можно вложить друг в друга. Таким образом, мы расширим варианты решений.

Для примера, возьмем переаттестацию сотрудников, которую рассмотрели раньше. Изменим критерии результата и выставим каждому оценку: Плохо, Хорошо и Отлично. Отлично будем ставить, когда баллы превысят 60. Оценку Хорошо можно будет получить, набрав от 45 до 60 балов. Ну и в остальных случаях ставим Плохо.

1. Для решения этой задачи составим формулу, включив в нее необходимые критерии оценивания: =ЕСЛИ (В3>60;»Отлично»;ЕСЛИ(B2>45;»Хорошо»;»Плохо»)).
2. Эта формула использует одновременно два условия. Первая проверка В3>60. Если баллы действительно больше 60, то в поле Результата мы получаем Отлично, и дальнейшая проверка условий не выполняется. Если Баллы меньше 60, то срабатывает вторая часть формулы, и мы проверяем В3>45 или нет. Если все верно, то возвращается значение «Хорошо», иначе «Плохо».
3. Затем достаточно скопировать формулу во все ячейки столбца и увидеть результат сдачи переаттестации.

Как видно из примера, вместо второго и третьего значения функции можно подставлять условие. Таким способом добавляем необходимое число вложений. Однако стоит отметить, что после добавления 3-5 вложений работать с формулой станет практически невозможно, т.к. она будет очень громоздкой.

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

Находим сумму столбцов или ячеек

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

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

Синтаксис: СУММЕСЛИ(диапазон_ячеек; используемое_условие; )

• Диапазон – обязательный аргумент. Тут задается диапазон, который оценивается на соответствие условию.
• Используемое_условие – тут указываем, какие именно ячейки необходимо суммировать.
• Диапазон_суммирования – указываем, какой именно диапазон использовать для суммирования.

Количество повторений элементов на листе

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

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

Используем СЧЕТЕСЛИ и СУММЕСЛИ при подсчете данных

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

Поиск нескольких вхождений элемента в списке

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

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

Используем поиск по клиенту Кристина Агилера. Используя формулу ВПР («Кристина Агилера3», диапазон_поиска, 2, ЛОЖЬ), мы получим третий номер этого клиента. В формуле, последним значением ставим ЛОЖЬ, поскольку список не сортирован, и нам необходимо точное совпадение элементов.

Как сократить количество вложений ЕСЛИ

Версии до EXCEL 2003 включительно поддерживали до 7 уровней вложенности ЕСЛИ. Начиная с версии Excel 2007, это ограничение убрали. Но стоит заметить, что ниже 3-4 уровня вложенности мало кто опускается.

Для того чтобы уменьшить количество использования вложений ЕСЛИ, можно использовать функцию ВЫБОР. Она работает со значениями или действиями из заданного списка по номеру индекса.

Синтаксис: ВЫБОР (номер_индекса; значение_индекса1; значение_индекса2; ;…)

Для примера, функция ВЫБОР (1; «Третий»; «Второй»; «Первый»), вернет нам слово «Третий», если мы ее добавим в ячейку.

Стоит отметить, что имеются некоторые ограничения. В частности, в качестве индекса могут выступать только числа.

Функция PARENT | Справочные статьи Smartsheet

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

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

PARENT([Имя задачи]6)

Синтаксис

PARENT(

• [

  ссылка

  ]

)

Примеры

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

Вот несколько примеров использования функции PARENT для данных из таблицы выше.

Функция ячеек VBA — Cells, Worksheet.Cells и Range.Cells

Вернуться к примерам кода VBA

В этой статье

• Синтаксис ячеек VBA
• Worksheet.Cells
• Range.Cells
• Перебор диапазона с помощью функции Cells

Эта статья продемонстрирует, как использовать VBA Function.

Функция ячеек VBA позволяет делать две вещи:

1. Ссылаться на все ячейки на листе
2. Ссылка на конкретную ячейку

Это похоже на объект диапазона VBA.

Синтаксис ячеек VBA

Традиционный синтаксис функции ячеек VBA:

Этот тип синтаксиса называется синтаксисом R1C1, в отличие от синтаксиса A1 буквы столбца, а затем номера строки, который используется при использовании Объект Range в Excel VBA.

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

 Cells(3, 4).Select 

это переместит указатель Excel в ячейку C4 на вашем листе.

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

 Sub PopulateCell()
   Cells(2, 2) = "Анализ продаж за 2022 год"
   Ячейки(2, 2).Шрифт.Полужирный = Истина
   Ячейки(2, 2).Font.Name = "Arial"
   Ячейки(2, 2).Шрифт.Размер = 12
End Sub 

Это заполнит ячейку B2 текстом, а затем отформатирует ячейку.

Worksheet.Cells

Если мы хотим выбрать весь рабочий лист с помощью функции Cells , мы можем ввести следующее: 1 таким же образом, как и нажатие CTRL + A на клавиатуре.

Range.Cells

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

 Диапазон ("A5:B10"). Ячейки (2, 2). Выберите 

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

Перебор диапазона с помощью функции Cells

Теперь, когда мы понимаем функцию Cells , мы можем использовать ее для выполнения некоторых задач в циклах VBA.

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

 Sub FormatCells()
   Dim x как целое число
   Для х = от 1 до 10
      Если Ячейки(х, 1) > 5 Тогда
         Ячейки(x, 1).Interior.Color = vbRed
      Конец, если
   Следующий х
End Sub 

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

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

 Sub FormatCells()
   Dim x как целое число
   Dim y As Integer
   Для х = от 1 до 10
      Для у = от 1 до 5
         Если Ячейки(х, у) > 5 Тогда
            Ячейки(x, y).Interior.Color = vbRed
         Конец, если
      Следующий г
   Следующий х
End Sub 

Теперь цикл начнется с ячейки A5, но продолжится до ячейки E10.

Мы также можем использовать функцию Cells для перебора определенного диапазона ячеек

 Sub LoopThrouRange()
   Dim rng As Range
   Dim x как целое число
   Dim y As Integer
   Установить rng = диапазон («B2: D8»)
   Для х = от 1 до 7
      Для у = от 1 до 3
         Если rng.Cells(x, y) > 5 Тогда
            rng.Cells(x, y).Interior.Color = vbRed
         Конец, если
      Следующий г
   Следующий х
Конец суб 

Этот цикл просматривает только диапазон B2:D8, а затем перебирает строки и столбцы этого конкретного диапазона. Помните, что когда диапазон предоставляется функции ячеек , функция ячеек будет просматривать только этот диапазон, а не весь рабочий лист.

Кодирование VBA стало проще

Прекратите искать код VBA в Интернете. Узнайте больше об AutoMacro — конструкторе кода VBA, который позволяет новичкам создавать процедуры с нуля с минимальными знаниями в области кодирования и множеством функций, позволяющих сэкономить время для всех пользователей!

Узнать больше!

Информационные функции Excel (примеры + образцы файлов)

1. Функция ячейки

2. Информационная функция

3. Функция ISBLANK

4. Функция ISERR

5. Функция ISERROR

6. Функция ISEVEN

7. Функция ISFORMULA

8. Функция ISLOGICAL

9. Функция ISNA

10. Функция ISNONTEXT

11. Функция ISNUMBER

12. Функция ISODD

13. Функция ISREF

14. Функция ISTEXT

15. N Функция

16. Функция NA

17. Функция листа

18. Функция листов

19. Функция

20. Ошибка.

Дополнительные функции Excel

файл примера

1.

Функция ЯЧЕЙКА

Функция ЯЧЕЙКА возвращает определенную информацию о ячейке. Вы можете выбрать (которые вы можете указать в функции) из нескольких типов информации, чтобы получить результат.

Синтаксис

CELL(info_type, [reference])

Аргументы

• info_type: Тип информации о ячейке, которую вы хотите проверить. У вас есть раскрывающийся список, чтобы выбрать, какой тип информации вам требуется о ссылке.
• [ссылка]: Ячейка, для которой вы хотите получить информацию.

Примечания

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

Пример

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

2. Функция INFO

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

Синтаксис

INFO(type_text)

Аргументы

• type_text:  Информация, которая вам нужна в результате.

Примечания

• У вас есть 7 различных параметров для получения информации о вашей текущей операционной среде.

Пример

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

3. Функция ЕПУСТО

Функция ЕПУСТО возвращает ИСТИНА, если ячейка пуста. Проще говоря, с помощью функции ISBLANK вы можете обратиться к ячейке и проверить, пуста она или нет, и если она пуста, она возвращает TRUE.

Синтаксис

ISBLANK(значение)

Аргументы

• значение: Ячейка или значение, которое вы хотите проверить.

Примечания

• ЕОШИБКА проверяет наличие ошибки в обоих условиях, будь то ошибка в абсолютном значении или результат другой формулы.
• Он будет оценивать все типы ошибок из ячейки. #N/A, #DIV/0!, #NAME?, #NULL!, #NUM!, #REF! и #VALUE!.

Пример

В приведенном ниже примере мы использовали IПУСТО с ЕСЛИ для доставки сообщения пользователю, если ячейка F1 пуста.

4. Функция ЕОШИБКА

Функция ЕОШИБКА возвращает ИСТИНА, если значение является ошибкой, отличной от #Н/Д. В отличие от ЕОШИБКИ, вы можете проверить, что он учитывает все ошибки, кроме #Н/Д, и если есть ошибка, он возвращает ИСТИНА, иначе ЛОЖЬ.

Синтаксис

ISERR(значение)

Аргументы

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

Примечания

• ISERR проверит ошибку в обоих условиях, будь то ошибка в абсолютном значении или результат другой формулы.
• Он оценивает все типы ошибок из ячейки #DIV/0! #ИМЯ? #НУЛЕВОЙ! #ЧИСЛО! #ССЫЛКА! и #ЗНАЧ! но не #Н/Д. Чтобы проверить #N/A, вы можете использовать ISNA и IERROR.

Пример

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

В приведенном ниже примере мы использовали ISERR с диапазоном массива для проверки ошибок из диапазона ячеек.

5. Функция ЕОШИБКА

Функция ЕОШИБКА возвращает ИСТИНА, если значение является ошибкой. Вы можете проверить, что он учитывает все ошибки, и если есть ошибка, он возвращает TRUE, иначе FALSE.

Синтаксис

ОШИБКА(значение)

Аргументы

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

Примечания

• ЕОШИБКА проверит ошибку в обоих условиях, будь то ошибка в абсолютном значении или результат другой формулы.
• Он будет оценивать все типы ошибок из ячейки. #N/A, #DIV/0!, #NAME?, #NULL!, #NUM!, #REF! & #ЦЕННОСТЬ!.

Пример

В приведенном ниже примере мы использовали ЕОШИБКА с ЕСЛИ, чтобы получить конкретный текст, если в ячейке есть ошибка.

В приведенном ниже примере мы использовали ЕОШИБКА с диапазоном массива для проверки ошибки из диапазона ячеек.

6. Функция ISEVEN

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

Синтаксис

ISEVEN(число)

Аргументы

• число:  Значение, которое вы хотите оценить.

Примечания

• Вы также можете вставить число непосредственно в функцию, используя двойные кавычки или даже без них.
• Если указать нечисловое значение, будет возвращено значение #ЗНАЧ! значение ошибки.

Пример

Ниже мы использовали разные аргументы:

• Возвращает ИСТИНА, если заданное число четное.
• Точно так же оцениваются отрицательные значения.
• Обрабатывает 0 как четное число.
• Если вы укажете число с десятичными точками, оно игнорирует десятичные дроби и вычисляет целое число. В этом примере он усекает 0,5 и оценивает 2.
• Поскольку Excel хранит дату как порядковый номер, он оценивает их таким же образом.

7. Функция ISFORMULA

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

Синтаксис

ISFORMULA(ссылка)

Аргументы

• ссылка:  Ссылка на ячейку, которую вы хотите оценить.

Примечания

• Если ссылка на ячейку недействительна, возвращается #ЗНАЧ!.
• Вы также можете использовать сочетание клавиш Control + ~ для отображения всех формул на листе.

Пример

Ниже мы использовали разные аргументы:

• Простое вычисление сложения возвращает ИСТИНА.
• Нестабильная функция, которая возвращает TRUE.
• Следует отметить, что ISFORMULA работает с формулой в ячейке, а не с результатом формулы. Он вернет TRUE, даже если результат формулы пуст или ошибка.
• Если в ячейке есть значение, отличное от формулы, возвращается ЛОЖЬ.

8. Функция ISLOGICAL

Функция ISLOGICAL возвращает значение TRUE, если предоставленное значение (или значение в упомянутой ячейке) является логическим значением. При логическом значении означает ИСТИНА или ЛОЖЬ. Это означает, что если значение TRUE или FALSE, оно возвращает TRUE, иначе FALSE.

Синтаксис

ISLOGICAL(значение)

Аргументы

• значение:  Значение, которое вы хотите оценить.

Примечания

• Если ссылка на ячейку недействительна, возвращается #ЗНАЧ!.

Пример

Ниже мы использовали разные аргументы:

• В ячейку ПЕРВАЯ мы ввели простое значение ИСТИНА, а в ячейку ВТОРАЯ мы использовали функцию ИСТИНА. Он вернул TRUE для обоих значений, поскольку оба значения являются логическими значениями.
• В ячейках ТРЕТЬЯ и ЧЕТВЕРТАЯ мы использовали значение ЛОЖЬ и функцию ЛОЖЬ соответственно, и она вернула один и тот же результат для обоих значений.
• В FIFTH и SIXTH логические значения TRUE и FALSE также имеют числовые значения 1 и 0, но ISLOGICAL не будет обрабатывать эти числовые логические значения.
• В СЕДЬМАЯ и ВОСЬМАЯ, мы использовали текстовые значения для оценки, и она возвращает ЛОЖЬ.
• В ДЕВЯТОЙ мы использовали пустую ячейку, она возвращает ЛОЖЬ, а в ДЕСЯТАЯ , если значение содержит ошибку, возвращается значение #Н/Д.

9. Функция ISNA

Функция ISNA возвращает TRUE, если предоставленное значение (или значение в упомянутой ячейке) является ошибкой #Н/Д. Проще говоря, он учитывает только #Н/Д и возвращает ИСТИНА и ЛОЖЬ для всего.

Синтаксис

ISNA(значение)

Аргументы

• значение:  Ссылка на ячейку или значение, которое вы хотите проверить.

Примечания

• Будет учитываться только #Н/Д, другие значения ошибок игнорируются.

Пример

В приведенном ниже примере мы использовали ISNA для проверки различных значений ошибок, и мы получили TRUE только в случае значения ошибки #N/A.

В приведенном ниже примере мы использовали IF и VLOOKUP с ISNA для доставки значимого сообщения пользователю.

10. Функция НЕТТЕКСТ

Функция НЕТТЕКСТ возвращает ИСТИНА, если предоставленное значение (или значение в упомянутой ячейке) не является текстовым значением. с не текстовыми средствами, означает число, дату, символ и т. Д.

Синтаксис

ISNONTEXT (значение)

Аргументы

• хотите протестировать.

Примечания

• Если число заключено в двойные кавычки, оно будет обработано как текст, и формула вернет ЛОЖЬ.

Пример

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

11. Функция ЕЧИСЛО

Функция ЕЧИСЛО возвращает ИСТИНА, если предоставленное значение (или значение в упомянутой ячейке) является числом. Проще говоря, учитывается только числовое значение, а остальное игнорируется.

Синтаксис

ISNUMBER(значение)

Аргументы

• значение:  Числовое значение, которое вы хотите проверить.

Примечания

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

Пример

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

12. Функция ISODD

Функция ISODD возвращает TRUE, если предоставленное значение (или значение в упомянутой ячейке) является НЕЧЕТНЫМ числом. Простыми словами, если значение является числом, которое делится на 2, возвращается ИСТИНА, иначе ЛОЖЬ.

Синтаксис

ISODD(значение)

Аргументы

• значение: Число, которое вы хотите проверить на нечетность.

Примечания

• Если значение не является числом, возвращается ошибка #ЗНАЧ.
• Если число заключено в двойные кавычки, оно будет считаться текстом и вернет FALSE.

Пример

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

13. Функция ISREF

Функция ISREF возвращает значение TRUE, если указанное значение является допустимой ссылкой, иначе FALSE. Проще говоря, вы можете использовать ISREF, чтобы проверить, является ли текстовое значение допустимой ссылкой или нет.

Синтаксис

ISREF(значение)

Аргументы

• значение:  Значение, для которого вы хотите проверить действительную ссылку.

Примечания

• Если действительный адрес ссылки заключен в двойные кавычки, ISREF не сможет проверить эту ссылку и вернуть FALSE, даже если эта ссылка действительна.

Пример

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

14. Функция ISTEXT

Функция ISTEXT возвращает значение TRUE, если предоставленное значение (или значение в упомянутой ячейке) является текстом. Простыми словами, учитывается только текст и игнорируются все остальные типы значений.

Синтаксис

ISTEXT(значение)

Аргументы

• значение: Ссылка на ячейку или значение, которое вы хотите проверить.

Примечания

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

Пример

В приведенном ниже примере мы использовали ISTEXT с IF для создания формулы вложенности для доставки предупреждающего сообщения, если пользователь вводит недопустимое имя (кроме текста).

15. Функция N

Функция N преобразует логическое значение в число. Проще говоря, он преобразует ИСТИНА в 1 и ЛОЖЬ в 2. Эти числа являются фактическим значением логических значений.

Синтаксис

N (значение)

Аргументы

• : A Boolean, который вы хотите преобразовать в номер

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

Пример

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

16. Функция NA

Функция NA возвращает значение #N/A. Проще говоря, функция NA возвращает в результате значение ошибки #N/A. Лучшее использование функции NA — получить сообщение об ошибке, когда в ячейке есть пустая и отсутствующая информация.

Синтаксис

NA(значение)

Аргументы

• В NA нет аргументов.

Примечания

• Вы можете использовать NA с функциями, чтобы вернуть ошибку #N/A.

Пример

В приведенном ниже примере мы вставили функцию NA в ячейку A1, и она просто вернула #N/A.

17. Функция ЛИСТ

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

Синтаксис

ЛИСТ(значение)

Аргументы

• [значение]:  Название листа или ссылка на любую ячейку листа

Примечания

• Он будет включать все типы листов (лист диаграммы, рабочий лист или лист макросов).
• Вы можете ссылаться на листы, даже если они видны, скрыты или очень скрыты.
• Если вы пропустите указание какого-либо значения в функции, она даст вам номер листа, на котором вы применили функцию.
• Если вы укажете недопустимое имя листа, он вернет #N/A.
• Если вы укажете недопустимую ссылку на лист, он вернет #REF!.

Пример

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

Если у нас есть недопустимое имя листа и недопустимая ссылка на лист, функция ЛИСТ вернула ошибку.

18. Функция ЛИСТЫ

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

Синтаксис

ЛИСТЫ(ссылка)

Аргументы

• ссылка: Справочник, для которого вы хотите подсчитать количество листов.

Примечания

• Он будет включать все типы листов (лист диаграммы, рабочий лист или лист макросов).
• Вы можете ссылаться на листы, даже если они видны, скрыты или очень скрыты.
• Если вы пропустите указание какого-либо значения в функции, она даст вам общее количество листов в рабочей книге.
• Если вы укажете недопустимую ссылку, она вернет #REF!.

Пример

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

19. Функция ТИП

Функция ТИП возвращает число, представляющее тип предоставленного значения. Проще говоря, функция ТИП возвращает определенное число, представляющее тип предоставленного значения.

Синтаксис

TYPE(value)

Аргументы

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

Примечания

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

Пример

В приведенной ниже формуле мы использовали ТИП с ЕСЛИ.

 =ЕСЛИ(ТИП(F3)<>1,"Введите действительное количество",E3*F3) 

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

20. ТИП ОШИБКИ Функция

Функция ОШИБКА.ТИП возвращает число, которое представляет тип ошибки в ячейке. Для каждого типа ошибки в Excel существует определенный номер, и если ошибки нет, возвращается #N/A.

Синтаксис

ERROR.TYPE (error_val)

Аргументы

• error_val: значение, которое вы хотите оценить.

Примечания

• Вы можете использовать его с другими функциями для проверки ошибок.