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

Как быстро оценить прерывистый диапазон в Excel?

Как быстро оценить прерывистый диапазон в Excel?

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

= Если (это правда, то сделайте это, иначе сделайте что-то еще)

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

Операторы в I F чрезвычайно надежны и являются основой многих моделей электронных таблиц, но также могут быть источником многих проблем электронных таблиц. В идеале оператор IF должен применяться к минимальным условиям, таким как мужской/женский и да/нет/возможно, но иногда встречаются более сложные случаи, требующие применения более 3 функций IF во вложенном формате.

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

Когда определенное условие выполняется или нет, функция IF выдает другое значение.

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

Название аргументов

лог_выражение

Проверяемые условия.

Значение_если_истинно.

Если выражение log_expression равно TRUE, будет возвращаться это значение.

Значение_если_лжи

В случае, если log_expression установлено в FALSE, будет возвращено это значение.

Примечания

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

Чтобы построить цепочку из нескольких операторов IF и убедиться, что они работают для всех условий на этом пути, нужно хорошо подумать. Формула, которая работает в 75% случаев, может дать неожиданный результат в оставшихся 25%, если вы допустите малейшую ошибку. Маловероятно, что вы найдете эти 25%.

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

Если вы обнаружите, что оператор IF растет бесконечно, пора положить мышку и пересмотреть свою стратегию.

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

Примеры

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

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

Если результат теста (в ячейке D2) выше 89, студент получает оценку A.

Если результат теста выше 79, студент получает оценку В.

Если тестовый балл ученика превышает 69, он получает тройку.

Если результат экзамена выше 59 баллов, студент получает оценку D.

Если нет, студент получает оценку F.

Благодаря тому, что связь между тестовыми баллами и буквенными оценками вряд ли изменится, этот пример относительно безопасен. А если оценки нужно разделить на A+, A и A- (и т.д.)? Теперь ваши четыре условных оператора IF должны быть переписаны с учетом 12 условий! Вот как будет выглядеть ваша формула:

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

Читайте так же:
Как быстро вставить текущую дату с определенным форматом даты в Excel?

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

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

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

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

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

Если ваша строка формул содержит сложные формулы, можно подумать о вставке переносов строк. ALT+ENTER — это клавиши для переноса текста на другую строку.

Пример неверного расчета комиссионных:

Вы видите, что не так? Сравните порядок получения дохода с приведенным выше примером. Как это будет выглядеть? Правильно, снизу вверх (от 5 000 до 15 000 рублей), а не наоборот. Но почему это так важно? Это очень важно, поскольку формула не может пройти первую оценку для любой стоимости выше 5 000 рублей. Допустим, вы получаете доход в размере 12 500 долларов — если вы получаете 10%, потому что это более 5 000 рублей, и на этом все заканчивается. Это может быть крайне проблематично, потому что во многих ситуациях подобные ошибки остаются незамеченными до тех пор, пока не приведут к негативным последствиям. Что же делать, если вы знаете, что сложные вложенные операторы IF имеют серьезные недостатки? В большинстве случаев вместо создания сложной формулы с помощью функции IF можно использовать VLOOKUP. При использовании VLOOKUP сначала необходимо создать справочную таблицу:

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

Значение ячейки B9 находится в диапазоне B2:B22 с помощью этой формулы. После нахождения значения она возвращает соответствующее значение из той же строки в столбце C.

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

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

Вложенные детали в этой области могут быть более подробными, но это гораздо проще, чем создавать 12-уровневый сложный вложенный оператор if/then! Есть и некоторые менее очевидные преимущества:

Справочные таблицы VRP открыты и легко доступны для просмотра.

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

Чтобы спрятать справочную таблицу, просто перенесите ее на отдельный лист.

Вы знали?

Теперь есть функция CONDITION, которая может заменить несколько вложенных операторов IF. Итак, в нашем первом примере расчета с 4 вложенными функциями IF:

Вы можете сделать все намного проще с помощью одной функции ELIMIN:

Функция ELIMIN — это просто находка! Используя ее, вам больше не нужно запоминать все эти операторы IF.

Если у вас нет подписки на Microsoft 365, эта функция недоступна. Убедитесь, что на вашем компьютере установлена последняя версия Office, если вы подписаны на Microsoft 365.

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

Вы всегда можете задать вопрос в техническом сообществе Excel или обратиться за помощью в сообщество «Ответы».

Как быстро оценить прерывистый диапазон в Excel?

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

Браузер не поддерживает видео.

Проверьте, как это работает!

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

Выберите «Данные» > «Проверить данные».

Проверка данных

На вкладке Параметры в списке Тип данных выберите соответствующий параметр:

Integer, чтобы вы могли ввести только целое число.

Можно вводить только десятичные цифры.

Это позволяет выбирать данные из раскрывающегося списка.

Вам нужно ввести только дату.

Время, чтобы иметь возможность вводить только время.

Длина текста для ограничения длины текста.

Альтернативный вариант — задать настраиваемую формулу.

Необходимо выбрать условие из списка Value (Значение).

Определите остальные обязательные значения с учетом параметров Data Type и Value.

Настройте сообщение, которое будут видеть пользователи при вводе данных на вкладке Input Hint (Подсказка ввода).

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

Откройте вкладку Сообщение об ошибке для настройки сообщения об ошибке и выберите Просмотр.

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

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

Скачивание примеров

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

Читайте так же:
Как быстро вывести список всех гиперссылок в Excel?

Ограничение ввода данных

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

В разделе Проверка данных выберите Проверка данных.

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

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

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

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

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

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

Нажмите ссылку Проверить данные в нижней части вкладки Данные.

Примечание: Если команда проверки недоступна, электронная таблица может быть защищена или книга может быть разделена. Если книга разделена или электронная таблица защищена, вы не сможете изменить параметры проверки. Дополнительные сведения о защите книги см. в разделе Защита книги.

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

Заголовок сообщения вводится в поле Title (Заголовок).

В поле Ввод подсказки введите сообщение, которое будет выведено на экран.

Отображение сообщения об ошибке при вводе недопустимых данных

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

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

На вкладке Данные щелкните Контроль данных > Контроль данных.

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

На вкладке Сообщение об ошибке в поле Заголовок введите название сообщения.

В поле Message (Сообщение) введите сообщение, которое будет отображаться при вводе недопустимых данных.

Выполнить одно из следующих действий.

Выберите в контекстном меню пункт Вид

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

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

Предупреждение

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

Добавление проверки данных в ячейку или диапазон ячеек

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

Читайте так же:
Как быстро перейти к концу выделения в Excel?

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

На вкладке Данные в группе Управление данными нажмите кнопку Проверка данных.

На вкладке Предпочтения выберите Список в поле Разрешить.

Введите значения списка, разделенные запятыми, в поле Источник. Например, введите Низкий, Средний, Высокий.

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

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

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

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

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

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

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

Выполните шаги 1 и 2, описанные выше.

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

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

Укажите минимальное, максимальное или конкретное значение, которое допускается.

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

В качестве примера рассмотрим проверку значений в ячейке F1. Минимальная сумма отчислений будет равна значению ячейки, умноженному на 2 в поле Данные, и формуле =2*F1 в поле Минимальное значение.

Разрешить ввод только десятичных чисел из определенного интервала

Выполните шаги 1 и 2 выше.

Выберите Десятичная в поле Разрешить.

Выберите тип предела в поле Данные. Выберите опцию Предел диапазона, например, для установки верхнего и нижнего пределов.

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

Вы также можете ввести формулу, которая возвращает числовое значение. Например, чтобы установить максимальное значение комиссионных и бонусов в размере 6% от зарплаты продавца в ячейке E1, выберите Less than or equal to в поле Data и введите формулу =E1*6% в поле Maximum value.

Примечание: Чтобы разрешить пользователям вводить проценты, например «20%», выберите Decimal в поле Allow, установите нужный тип ограничения в поле Data, введите минимальное, максимальное или определенное значение в виде десятичного числа, например 0,2, а затем отобразите ячейку проверки данных в виде процентного значения, выделив ее и нажав кнопку Percentage Format на вкладке Home в группе Number.

Разрешить вводить только даты в пределах указанного интервала времени

Выполните шаги 1 и 2, описанные выше.

В поле «Позволить» выберите значение даты.

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

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

Введите дату начала, дату окончания или определенную дату, которую разрешено указать.

Вы также можете ввести формулу, которая возвращает дату. Например, чтобы задать интервал между текущей датой и датой через 3 дня после текущей даты, выберите Between в поле Data, затем введите =TODAY() в поле Start Date, затем =TODAY()+3 в поле End Date.

Позволяет вводить только время в определенном диапазоне

Выполните описанные выше шаги 1-2.

В поле «Разрешить» выберите параметр «Время».

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

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

Ячейка E2 имеет время начала (8:00), а ячейка F2 — время окончания (17:00), и вы хотите, чтобы встречи ограничивались только этим интервалом, выберите между в поле Данные, а затем введите =E2 в поле Время начала и =F2 в поле Время конца.

Разрешить ввод только текста определенной длины

Выполните шаги 1 и 2 выше.

Поле Allow должно содержать значение Text Length.

В поле Данные выберите тип ограничения. Задайте определенное количество символов, например, выбрав Ограничение меньше или равно.

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

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

Выполните шаги 1-2 выше.

Выберите тип данных из поля Разрешить.

В поле Данные выберите нужный тип ограничения.

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

Например, чтобы разрешить ввод только для счета, общая сумма которого не превышает бюджет в ячейке E1, выберите Число десятичных знаков из списка Разрешенные, Ограничение меньше или равно из списка Данные и введите >= =E1 в поле Максимальное значение.

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

Скриншоты, показанные в этой статье, были созданы в Excel 2016, но функции аналогичны Excel онлайн.

Ввод формулы

В значении в ячейке C2, содержащем код товара, должно быть не менее 10 (или более 9) знаков.

Ячейка с названием товара (D2) содержит только текст.

Ячейка B6 содержала значение, которое превышало количество лет, указанное в ячейке B4.

=ЕСЛИ(B6<=(СЕГОДНЯ()-(365*B4));TRUE,FALSE)

Каждая ячейка в диапазоне A2:A10 содержала уникальное значение.

=СЧЁТЕСЛИ($A$2:$A$10;A2)=1

Пример 4. Формулы для проверки данных

Примечание: Вы должны сначала ввести формулу проверки данных в ячейку A2, а затем скопировать эту ячейку в ячейки A3:A10, чтобы второй аргумент аргумента READ соответствовал текущей ячейке. Часть A2)=1 превратится в A3)=1, A4)=1 и т.д.

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