Обучение условному форматированию в Excel с примерами. Создать правило в excel

Как создать правило в excel 2010 — Комплексная юридическая помощь

Правила условного форматирования в Excel

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

поля данных;
цветовая палитра;
настройка шрифтов.

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

Как сделать условное форматирование в Excel

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

Допустим столбец, содержит диапазон ячеек с числовыми значениями. Если определить их соответствующим условием форматирования, то все значения с числом более чем 100 будут отображаться красным цветом. Чтобы реализовать эту задачу данный инструмент Excel проанализирует в соответствии с условиями критериев значение каждой ячейки заданного диапазона. Результаты анализа дают положительный результат, например (A2>100=ИСТИНА), тогда будет присвоен предварительно заданный новый формат (красный цвет). В противоположном результате (A2>100=ЛОЖЬ), формат ячеек не изменяется.

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

Как создать правило условного форматирования в Excel

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

Допустим в ячейке A1 находиться числовое значение 50:

Определим следующие условия для формата отображения значений в A1:

Если число больше чем 15, то шрифт будет отображаться зеленым цветом.
Если число больше чем 30, то шрифт будет отображаться желтым цветом.
Если число больше чем 40, то шрифт будет отображаться красным.

Определенно вы заметили, что значение 50 в ячейке A1 соответствует всем условиям (A1>15, A1>30 и A1>40 = ИСТИНА). Каким же цветом шрифта Excel отобразит числовое значение 50?

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

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

Создать второе правило

Второй пример. Допустим нам нужно отформатировать расходы в колонке C следующим образом:

Все суммы в пределах 300$-600$ должны закрасить свои ячейки желтым фоном, а суммы меньше чем 500$ должны еще иметь и красный цвет шрифта.

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

Выделите диапазон ячеек C4:C7 и выберите инструмент: «Главная»-«Условное форматирование»-«Управление правилами».
В появившемся окне нажмите на кнопку «Создать правило».
В окне «Создание правила форматирования», выберите тип правила «Форматировать только ячейки, которые содержат».
В первом выпадающем списке укажите «Значение ячейки», во втором «между». А в остальных двух введите границы придела 300 и 600.

Нажмите на кнопку «Формат» и на вкладке «Заливка» укажите желтый цвет.
Нажмите «ОК» для возврата к окну «Диспетчер правил условного форматирования».
Снова нажмите на кнопку «Создать правило» и снова же выберите тип правила «Форматировать только ячейки, которые содержат».
Первый выпадающий список оставляем без изменений «Значение ячейки», а во втором выбираем опцию «меньше». В поле ввода указываем значение 500.
Нажмите на кнопку «Формат» и на вкладке «Шрифт» укажите красный цвет.
На всех окнах нажмите «ОК».

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

exceltable.com

Как создать правило в excel 2010

Что научитесь делать, посмотрев это видео?

Вы научитесь пользоваться инструментами «Условного форматирования», а также самостоятельно создавать правила для условного форматирования ячеек в программе Excel 2010.

О том, как в экселе удалить правила условного форматирования смотрите тут.

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

Выделяем весь диапазон таблицы, в котором записаны оценки. На ленте «Главная», в группе «Стили» заходим в меню «Условное форматирование» и кликаем на вкладку «Создать правило». В выпавшем окошке нужно выбрать вкладку «Форматировать только ячейки, которые содержат». Во второй половине окошка есть выпадающий список, в котором по умолчанию прописано условие «Между». Согласно наших требований это условие нужно изменить на «Больше или равно». Рядом указываем значение 10. Внизу того же окошка есть кнопка «Формат». На неё нужно кликнуть и в открывшемся окне «Формат ячеек» указать цвет шрифта зеленый.

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

Основные сведенья о форматах данных в эксель можно посмотреть в этом видео.

www.kak-v.com

Применение условного форматирования с помощью формулы

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

Создание правил условного форматирования с формулами

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

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

Как создать первое правило

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

Затем выберите вкладку Главная > Условное форматирование > Создать правило.

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

В разделе Форматировать значения, для которых следующая формула является истинной: введите формулу: =A2>СЕГОДНЯ().

Функция СЕГОДНЯ используется в формуле для определения значения дат в столбце A, превышающих значение сегодняшней даты (будущих дат). Ячейки, удовлетворяющие этому условию, форматируются.

Нажмите кнопку Формат.

В раскрывающемся списке Цвет выберите Красный. В списке раздела Начертание выберите пункт Полужирный.

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

Теперь форматирование применено к столбцу A.

Как создать второе правило

Выделите ячейки от C2 до C7.

Повторите действия 2–4, указанные выше, и введите следующую формулу: =C2=»Y».

Формула определяет ячейки в столбце C, содержащие символ «Y» (прямые кавычки вокруг символа «Y» указывают, что это текст). Ячейки, удовлетворяющие этому условию, форматируются.

В раскрывающемся списке Цвет выберите Белый. В списке раздела Начертание выберите пункт Полужирный.

Перейдите на вкладку Заливка и выберите Зеленый.

Теперь форматирование применено к столбцу C.

Попробуйте попрактиковаться

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

При создании правила убедитесь в том, что оно применяется к ячейкам D2:D11. Задайте цветовое форматирование, которое должно применяться к ячейкам, удовлетворяющим условию (т. е. если название города встречается в столбце D более одного раза, а это — Москва и Мурманск).

support.office.com

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Условное форматирование в Excel

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

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

Виды условного форматирования

Когда вы нажимаете на кнопку Условное форматирование, которая находится в группе Стили вкладки Главная, вы увидите выпадающее меню со следующими опциями:

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

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

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

Цветовые шкалы позволяет задавать двух- и трехцветовые шкалы для цвета фона ячейки на основе ее значения относительно других ячеек в диапазоне

Наборы значков отображает значок в ячейке. Какой именно значок отображается, зависит от значения ячейки относительно других ячеек. Excel 2013 предоставляет 20 наборов значков на выбор (при этом вы можете смешивать и сочетать значки из разных наборов). Количество значков в наборах колеблется от трех до пяти.

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

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

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

Графическое условное форматирование

Вероятно, самое крутое (и конечно, простое) условное форматирование, которое можно применить к диапазону ячеек – это форматирование с применением графических элементов – Гистограммы, Цветовые шкалы и Наборы значков.

На рисунке изображено применение двух различных правил для форматирования для диапазона от 6 до 1 и наоборот. В первом случае применялись Цветовые шкалы, где мы видим, как изменяется формат при изменении значения от 6 до 1, во втором – 3 цветные стрелки.

Определение конкретных значений в диапазоне ячеек

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

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

Примеры условного форматирования

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

Выделяем диапазон ячеек, к которому мы хотим применить условное форматирование. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование -> Правила выделения ячеек -> Текст содержит.

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

Щелкаем ОК, чтобы наше правило вступило в силу.

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

Скажем, вы хотите применить три различных условных форматирования к одному и тому же диапазону ячеек: первый тип формата, когда ячейка содержит целевое значение, второй – когда больше цели и третий – когда меньше. Ниже описаны шаги по заданию формата Желая заливка с темно-желтым текстом для ячеек содержащих значение 95, Зеленая заливка с темно-зеленым текстом для ячеек со значениями больше 95 и Светло-красная заливка и темно-красным текстом для ячеек меньше 95.

Выделяем диапазон ячеек, к которому мы хотим применить три различных правила условного форматирования. Начнем с создания правила для ячеек, содержащих значение равное 95. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование -> Правила выделения ячеек -> Равно. Excel откроет диалоговое окно Равно, где в левом текстовом поле необходимо указать условие 95, а в правом выпадающем списке выбрать формат для этого условия Желая заливка с темно-желтым текстом.

Далее задаем условное форматирование для значений больше 95. Из меню Условное форматирование -> Правила выделения ячеек выбираем Больше, в появившемся диалоговом окне Больше указываем значение, выше которого ячейка будет закрашиваться в зеленый цвет, и сам формат.

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

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

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

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

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

В поле Измените описание правила задаем условия и формат для нашего правила. В нашем случае, условием будет формула =ИЛИ(ДЕНЬНЕД($A2;2)=6;ДЕНЬНЕД($A2;2)=7). В качестве формата я выбрал темно красную заливку.

Послесловие

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

Вам также могут быть интересны следующие статьи

31 комментарий

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

Эльнур, такую штуку можно реализовать с помощью создания пользовательской функции, например, такой:

exceltip.ru

Управление приоритетом правил условного форматирования

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

Создание, редактирование, удаление и просмотр всех правил условного форматирования в книге выполняются в диалоговом окне Диспетчер правил условного форматирования. (Для этого на вкладке Главная выберите команду Условное форматирование и выберите пункт Управление правилами.)

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

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

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

Первое правило (которое задает красный цвет фона ячейки, если значение — «истина») проверяет значение даты в столбце B напротив текущей даты (вычисленной с помощью функции СЕГОДНЯ в формуле). Формула для этого правила назначается первому значению в столбце B — B2 и выглядит так: =B2

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

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

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

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

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

Если к диапазону ячеек применимо правило форматирования, то оно имеет приоритет над форматированием вручную. Форматирование вручную можно выполнить с помощью кнопок из группы Шрифт на вкладке Главная в Excel 2016 или 2013 либо из группы Ячейки на вкладке Главная в Excel 2010 или 2007. При удалении правила условного форматирования форматирование, вручную примененное к диапазону ячеек, остается.

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

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

Например, если в более ранних версиях Excel, предшествующих версии Excel 2007, к диапазону ячеек применяется более трех правил условного форматирования, в данной версии Excel:

выполняется оценивание только трех первых правил;

применяется первое истинное правило в последовательности;

пропускаются истинные правила с более низким приоритетом.

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

support.office.com

e-s-co.ru

Обучение условному форматированию в Excel с примерами

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

Как сделать условное форматирование в Excel

Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».

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

Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».

Введем в диапазон А1:А11 ряд чисел:

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

Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:

Выходим из меню нажатием кнопки ОК.

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

Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.

Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» - «Меньше»).

В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.

Результат форматирования сразу виден на листе Excel.

Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.

Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.

Выделим исходный диапазон (А1:А11). Нажмем «УФ» - «Правила выделения ячеек» - «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ!, а не абсолютная.

Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.

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

В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.

Сравните:

Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.

Проверить правильность заданного условия можно следующим образом:

Выделите первую ячейку диапазона с условным форматированим.
Откройте меню инструмента, нажмите «Управление правилами».

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

Условное форматирование – несколько условий

Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.

1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» - «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.
2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».

Заполняем параметры форматирования по первому условию:

Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.

Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».

То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).

Условное форматирование даты в Excel

Выделяем диапазон с датами.

Применим к нему «УФ» - «Дата».

В открывшемся окне появляется перечень доступных условий (правил):

Выбираем нужное (например, за последние 7 дней) и жмем ОК.

Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).

Условное форматирование в Excel с использованием формул

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

Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.

Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:

Для закрытия окна и отображения результата – ОК.

Условное форматирование строки по значению ячейки

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

Таблица для примера:

Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).

Выделяем диапазон со значениями таблицы. Нажимаем «УФ» - «Создать правило». Тип правила – формула. Применим функцию ЕСЛИ.

Порядок заполнения условий для форматирования «завершенных проектов»:

Обратите внимание: ссылки на строку – абсолютные, на ячейку – смешанная («закрепили» только столбец).

Аналогично задаем правила форматирования для незавершенных проектов.

В «Диспетчере» условия выглядят так:

Получаем результат:

Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».

«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.

exceltable.com

Условное форматирование в Excel - ExcelHack

В этом уроке мы рассмотрим основы применения условного форматирования в Excel.

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

Основы условного форматирования в Excel

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

закрашивать значения цветом
менять шрифт
задавать формат границ

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

Где находится условное форматирование в Эксель?

Кнопка “Условное форматирование” находится на панели инструментов, на вкладке “Главная”:

Где находится пункт условное форматирование в Excek

Как сделать условное форматирование в Excel?

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

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

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

В таблице с данными выделим диапазон, для которого мы хотим применить выделение цветом:

Условное форматирование в Excel - выделение диапазона данных

Перейдем на вкладку “Главная” на панели инструментов и кликнем на пункт “Условное форматирование”. В выпадающем списке вы увидите несколько типов формата на выбор:
- Правила выделения
- Правила отбора первых и последних значений
- Гистограммы
- Цветовые шкалы
- Наборы значков
В нашем примере мы хотим выделить цветом данные с отрицательным значением. Для этого выберем тип “Правила выделения ячеек” => “Меньше”:

Условное форматирование - правило меньше

Также, доступны следующие условия:

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

Во всплывающем окне в поле “Форматировать ячейки которые МЕНЬШЕ” укажем значение “0”, так как нам нужно выделить цветом отрицательные значения. В выпадающем списке справа выберем формат отвечающих условиям:

Форматирование ячеек в Excel меньше чем

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

Пользовательский формат условного форматирования в Эксель

Во всплывающем окне формата укажите:
- цвет заливки
- цвет шрифта
- шрифт
- границы ячеек

Пользовательский формат ячеек

По завершении настроек нажмите кнопку “ОК”.

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

Как создать правило

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

Выделим диапазон данных. Кликнем на пункт “Условное форматирование” в панели инструментов. В выпадающем списке выберем пункт “Новое правило”:

Создание собственного правила условного форматирования в Excel

Во всплывающем окне нам нужно выбрать тип применяемого правила. В нашем примере выберем тип “Форматировать только ячейки, которые содержат” и зададим условие выделить данные, значения которых больше “57” но меньше “59”:

условное форматирование на основе значений между числами

Кликнем на кнопку “Формат” и зададим формат, как мы это делали в примере выше. Нажмите кнопку “ОК”:

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

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

Для создания условия по значению другой ячейки выполним следующие шаги:

Выделим первую ячейку для назначения правила. Кликнем на пункт “Условное форматирование” на панели инструментов. Выберем условие “Меньше”.
Во всплывающем окне указываем ссылку на ячейку, с которой будет сравниваться данная ячейка. Выбираем формат. Нажимаем кнопку “ОК”.

условное форматирование в Excel по значению другой ячейки

Повторно выделим левой клавишей мыши ячейку, которой мы присвоили формат. Кликнем на пункт “Условное форматирование”. Выберем в выпадающем меню “Управление правилами” => кликнем на кнопку “Изменить правило”:

Изменение правила ячейки по значению другой ячейки

В поле слева всплывающего окна “очистим” ссылку от знака “$”. Нажимаем кнопку “ОК”, а затем кнопку “Применить”.

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

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

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

На скриншоте ниже цветом выделены данные, в которых курс валюты стал ниже к предыдущему периоду:

Как применить несколько правил условного форматирования к одной ячейке

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

Например, в таблице с прогнозом погоды мы хотим закрасить разными цветами показатели температоры. Условия выделения цветом: если температура выше 10 градусов – зеленым цветом, если выше 20 градусов – желтый, если выше 30 градусов – красным.

Для применения нескольких условий к одной ячейке выполним следующие действия:

Выделим диапазон с данными, к которым мы хотим применить условное форматирование => кликнем по пункту “Условное форматирование” на панели инструментов => выберем условие выделения “Больше…” и укажем первое условие (если больше 10, то зеленая заливка). Такие же действия повторим для каждого из условий (больше 20 и больше 30). Не смотря на то, что мы применили три правила, данные в таблице закрашены зеленым цветом:

условное форматирование в Excel по нескольким условиям

Кликнем на любую ячейку с присвоенным форматированием. Затем, снова кликнем по пункту “Условное форматирование” и перейдем в раздел “Управление правилами”. Во всплывающем окне, распределим правила от большего к меньшему и напротив первых двух поставим галочку “Остановить, если истина”. Этот пункт позволяет не применять остальные правила к ячейке, при соответствии первому. Затем кликнем кнопку “Применить” и “ОК”:

Условное форматирование в Эксель с несколькими условиями

Применив их, наша таблица с данными температуры “подсвечена” корректными цветами, в соответствии с нашими условиями.

Как редактировать правило условного форматирования

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

Выделить левой клавишей мыши ячейку, правило которой вы хотите отредактировать.
Перейдите в пункт меню панели инструментов “Условное форматирование”. Затем, в пункт “Управление правилами”. Щелкните левой клавишей мыши по правилу, которое вы хотите отредактировать. Кликните на кнопку “Изменить правило”:

Изменение правила ячейки по значению другой ячейки

После внесения изменений нажмите кнопку “ОК”.

Как копировать правило условного форматирования

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

Выделим диапазон данных с примененным условным форматированием. Кликнем по пункту на панели инструментов “Формат по образцу”.
Левой клавишей мыши выделим диапазон, к которому хотим применить скопированные правила формата:

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

Как удалить условное форматирование

Для удаления формата проделайте следующие действия:

Выделите ячейки;
Нажмите на пункт меню “Условное форматирование” на панели инструментов. Кликните по пункту “Удалить правила”. В раскрывающемся меню веберите метод удаления:

удаление правил условного форматирования в Эксель

excelhack.ru

Правила условного форматирования в Excel

поля данных;
цветовая палитра;
настройка шрифтов.

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

Как сделать условное форматирование в Excel

Как создать правило условного форматирования в Excel

Допустим в ячейке A1 находиться числовое значение 50:

Определим следующие условия для формата отображения значений в A1:

Если число больше чем 15, то шрифт будет отображаться зеленым цветом.
Если число больше чем 30, то шрифт будет отображаться желтым цветом.
Если число больше чем 40, то шрифт будет отображаться красным.

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

Создать второе правило

Второй пример. Допустим нам нужно отформатировать расходы в колонке C следующим образом:

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

Выделите диапазон ячеек C4:C7 и выберите инструмент: «Главная»-«Условное форматирование»-«Управление правилами».
В появившемся окне нажмите на кнопку «Создать правило».
В окне «Создание правила форматирования», выберите тип правила «Форматировать только ячейки, которые содержат».
В первом выпадающем списке укажите «Значение ячейки», во втором «между». А в остальных двух введите границы придела 300 и 600.
Нажмите на кнопку «Формат» и на вкладке «Заливка» укажите желтый цвет.
Нажмите «ОК» для возврата к окну «Диспетчер правил условного форматирования».
Снова нажмите на кнопку «Создать правило» и снова же выберите тип правила «Форматировать только ячейки, которые содержат».
Первый выпадающий список оставляем без изменений «Значение ячейки», а во втором выбираем опцию «меньше». В поле ввода указываем значение 500.
Нажмите на кнопку «Формат» и на вкладке «Шрифт» укажите красный цвет.
На всех окнах нажмите «ОК».

exceltable.com

Как создать собственное правило для выделения ячеек Excel? — MS Excel

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

В окне открытого листа перейдите к вкладке «Главная» и в группе «Стили» раскройте меню кнопки «Условное форматирование».
Выберите в списке команд пункт «Создать правило».
В окне «Создание правила форматирования» в группе «Выберите правило» щелкните по строке «Форматировать только ячейки, которые содержат» (рис. 3.32).

Рис. 3.32. Окно «Создание правила форматирования». Пункт «Форматировать только ячейки, которые содержат»

В группе «Изменить описание правила» раскройте в графе «Форматировать только ячейки, для которых выполняется следующее условие» первый список слева и выберите объект выборки – значение ячейки, текст, даты, пустые, непустые, ошибки или без ошибки.

При работе с текстом можно использовать кавычки и подстановочные знаки. Максимальная длина строки при этом должна составлять не более 255 знаков.При выборе значения «Ошибки» или «Без ошибки» значения ошибок включают #####, #ЗНАЧЕНИЕ!, #ДЕЛ/0!, #ИМЯ?, #Н/Д, #ССЫЛКА!, #ЧИСЛО! и #ПУСТО!.

Раскройте следующий список и выберите оператор сравнения, подходящий для заданного объекта выборки – между, вне, равно и т. д.
В соответствии с выбранным оператором сравнения в следующих полях вводится или выбирается на табличном поле нужные число, дата или время. При этом для вычисления числа, даты или времени в данных полях можно вводить и формулы.
При необходимости нажмите кнопку «Формат» и в окне «Формат ячеек» на вкладках «Число», «Шрифт», «Заливка» и «Граница» и задайте нужные параметры форматирования.
Закройте окно кнопкой «ОК».

Пример

В окне открытого листа выделите диапазон ячеек с текстом.
Перейдите к вкладке «Главная» и в группе «Стили» раскройте меню кнопки «Условное форматирование».
Наведите курсор на пункт «Правила выделения ячеек» и выберите пункт «Другие правила» (рис. 3.33).

Рис. 3.33. Вкладка «Главная». Меню кнопки «Условное форматирование». Пункт «Другие правила»

В окне «Создание правила форматирования» в группе «Изменить описание правила» раскройте первый список слева и выберите пункт «Текст» (рис. 3.34).
В следующей графе раскройте список и выберите пункт «Содержит».
Раскройте следующий список и выделите нужный текст.
Нажмите кнопку «Формат» и в окне «Формат ячеек» на вкладке «Заливка» выберите цвет для заливки ячейки.

При необходимости на вкладках «Шрифт» и «Граница» задайте дополнительные параметры форматирования.

Закройте окно кнопкой «ОК».

Рис. 3.34. Окно «Создание правила форматирования». Пункт «Текст»

excel2010.ru

Применение условного форматирования в Excel

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

Параметры условного форматирования	Условное форматирование с цветовыми шкалами

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

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

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

Условное форматирование: выделены повторяющиеся значения

Выделите диапазон, в котором хотите выделить цветом повторяющиеся значения (в данном случае это столбец "ФИО сотрудника").
На вкладке Главная нажмите кнопку Условное форматирование, откройте подменю Правила выделения ячеек и выберите пункт Повторяющиеся значения.
В раскрывающемся списке параметров выберите формат и нажмите кнопку ОК.

Диапазон о будет отформатирован, и повторяющиеся элементы будут выделены выбранным цветом.

Сортировка по цвету

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

Выполните указанные ниже действия.

Щелкните в любом месте диапазона данных, а затем на вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка.
В раскрывающемся списке Сортировать по выберите столбец ФИО сотрудника, в раскрывающемся списке Сортировка — значение "Цвет ячейки", в раскрывающемся списке Порядок — цвет, а в последнем раскрывающемся списке — значение Сверху. Нажмите кнопку ОК, чтобы применить заданное форматирование.

Элементы "Данные" > "Сортировка" > "Сортировка по цвету"

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

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

Правило Первые 10 элементов позволяет быстро выявить лидеров в диапазоне, например 10 лучших клиентов в списке на основании показателей. Точно так же вы можете выбрать правило Последние 10 элементов, Первые/Последние 10% или Выше/Ниже среднего. Выбрав правило, вы можете изменить число 10 на другое значение.

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

Условное форматирование: к диапазону применен формат "Первые 10"

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

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

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

Условное форматирование: к диапазону применен стиль "Гистограмма"

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

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

Чтобы узнать, как это сделать, выполните указанные ниже действия.

Условное форматирование: к диапазону применен стиль "Набор значков"

Выделите диапазон, который хотите отформатировать.
На вкладке Главная нажмите кнопку Условное форматирование и выберите пункт Наборы значков. Затем выберите стиль набора значков.
Excel попытается интерпретировать и соответственно отформатировать ваши данные. Если вам нужно изменить эту интерпретацию, откройте вкладку Главная и выберите Условное форматирование > Управление правилами > правило Набор значков > Изменить правило. Затем настройте параметры в разделе Отображать каждый значок согласно этим правилам. В данном случае мы так настроили правило, чтобы все значения больше 45 000 считались положительным результатом, значения от 40 000 до 45 000 — нейтральным, а все значения меньше 40 000 — отрицательным.

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

Условное форматирование с трехцветной шкалой

Выполните указанные ниже действия.

Выделите диапазон со значениями, которые нужно отформатировать.
На вкладке Главная нажмите кнопку Условное форматирование и выберите пункт Цветовые шкалы. Затем выберите шкалу. В данном случае мы использовали вариант Цветовая шкала "Красный-желтый-зеленый".

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

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

Подробнее о таблицах Excel можно узнать здесь: Создание и удаление таблицы Excel на листе.

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

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

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

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

См. также

support.office.com

Примеры как создать пользовательскую функцию в Excel

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

Пример создания своей пользовательской функции в Excel

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

Открыть редактор языка VBA с помощью комбинации клавиш ALT+F11.
В открывшемся окне выбрать пункт Insert и подпункт Module, как показано на рисунке:
Новый модуль будет создан автоматически, при этом в основной части окна редактора появится окно для ввода кода:
При необходимости можно изменить название модуля.
В отличие от макросов, код которых должен находиться между операторами Sub и End Sub, пользовательские функции обозначают операторами Function и End Function соответственно. В состав пользовательской функции входят название (произвольное имя, отражающее ее суть), список параметров (аргументов) с объявлением их типов, если они требуются (некоторые могут не принимать аргументов), тип возвращаемого значения, тело функции (код, отражающий логику ее работы), а также оператор End Function. Пример простой пользовательской функции, возвращающей названия дня недели в зависимости от указанного номера, представлен на рисунке ниже:
После ввода представленного выше кода необходимо нажать комбинацию клавиш Ctrl+S или специальный значок в левом верхнем углу редактора кода для сохранения.
Чтобы воспользоваться созданной функцией, необходимо вернуться к табличному редактору Excel, установить курсор в любую ячейку и ввести название пользовательской функции после символа «=»:

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

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

Создайте новый макрос (нажмите комбинацию клавиш Alt+F8), в появившемся окне введите произвольное название нового макроса, нажмите кнопку Создать:
В результате будет создан новый модуль с заготовкой, ограниченной операторами Sub и End Sub.
Введите код, как показано на рисунке ниже, указав требуемое количество переменных (в зависимости от числа аргументов пользовательской функции):
В качестве «Macro» должна быть передана текстовая строка с названием пользовательской функции, в качестве «Description» - переменная типа String с текстом описания возвращаемого значения, в качестве «ArgumentDescriptions» - массив переменных типа String с текстами описаний аргументов пользовательской функции.
Для создания описания пользовательской функции достаточно один раз выполнить созданный выше модуль. Теперь при вызове пользовательской функции (или SHIFT+F3) отображается описание возвращаемого результата и переменной:

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

Примеры использования пользовательских функций, которых нет в Excel

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

Вид исходной таблицы данных:

Каждому работнику полагается 24 выходных дня с выплатой S=N*24/(365-n), где:

N – суммарная зарплата за год;
n – число праздничных дней в году.

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

Код примера:

Public Function Otpusknye(summZp As Long, holidays As Long) As LongIf IsNumeric(holidays) = False Or IsNumeric(summZp) = False Then Otpusknye = "Введены нечисловые данные" Exit FunctionElseIf holidays <= 0 Or summZp <= 0 Then Otpusknye = "Отрицательное число или 0" Exit FunctionElse Otpusknye = summZp * 24 / (365 - holidays)End IfEnd Function

Сохраним функцию и выполним расчет с ее использованием:

=Otpusknye(B3;C3)

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

Калькулятор расчета калорий в Excel

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

Вид исходной таблицы данных:

Для расчета используем формулу Миффлина - Сан Жеора, которую запишем в коде пользовательской функции с учетом пола участника. Код примера:

Public Function CaloriesPerDay(sex As String, age As Integer, weight As Integer, height As Integer) As IntegerIf sex = "женский" Then CaloriesPerDay = 10 * weight + 6.25 * height - 5 * age - 161ElseIf sex = "мужской" Then CaloriesPerDay = 10 * weight + 6.25 * height - 5 * age + 5Else: CaloriesPerDay = 0End IfEnd Function

Проверки корректности введенных данных упущены для упрощения кода. Если пол не определен, функция вернет результат 0 (нуль).

Пример расчета для первого участника:

=CaloriesPerDay(B3;C3;D3;E3)

В результате использования автозаполнения получим следующие результаты:

Пользовательская функция для решения квадратных уравнений в Excel

Пример 3. Создать функцию, которая возвращает результаты решения квадратных уравнений для указанных в ячейках коэффициентах a, b и c уравнения типа ax2+bx+c=0.

Вид исходной таблицы:

Для решения создадим следующую пользовательскую функцию:

Код примера:

Public Function SquareEquation(a As Integer, b As Integer, c As Integer) As StringDim answer1 As StringDim answer2 As StringIf a = 0 Then answer1 = "Единственный корень - " SquareEquation = answer1 & "(" & -c / b & ")"ElseIf c = 0 Then answer1 = "Единственный корень - " SquareEquation = answer1 & "(" & -b / a & ")"ElseIf b = 0 And c < 0 Then answer1 = "Единственный корень - " SquareEquation = answer1 & "(" & Sqr(a / c) & ")"ElseIf b ^ 2 - 4 * a * c >= 0 Then answer1 = "Первый корень - " answer2 = "Второй корень - " SquareEquation = answer1 & "(" & (-b + Sqr(b ^ 2 - 4 * a * c)) / (2 * a) & ")" & "; " & _ answer2 & "(" & (-b - Sqr(b ^ 2 - 4 * a * c)) / (2 * a) & ")"Else: SquareEquation = "Решений нет"End IfEnd Function

Найдем корни первого уравнения:

=SquareEquation(A3;B3;C3)

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

Скачать примеры создания пользовательский функций в Excel

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

exceltable.com