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

Содержание:

Не знаете, как использовать Microsoft Excel? Тогда вы попали в нужное место! Microsoft Excel входит в состав большинства пакетов Microsoft и является общепризнанным компьютерным приложением, которое многие из нас используют в то или иное время.

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

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

Дополнительные ресурсы для изучения использования Excel

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

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

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

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

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

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

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

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

УСЛОВНОЕ ФОРМАТИРОВАНИЕ СТРОКИ ПО ЗНАЧЕНИЮ ЯЧЕЙКИ

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

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

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

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

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

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

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

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

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

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

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

Выберите начальную ячейку в первой из тех строк, которые Вы планируете форматировать. Кликните Conditional Formatting (Условное Форматирование) на вкладке Home (Главная) и выберите Manage Rules (Управление Правилами).

В открывшемся диалоговом окне Conditional Formatting Rules Manager (Диспетчер правил условного форматирования) нажмите New Rule (Создать правило).

В диалоговом окне New Formatting Rule (Создание правила форматирования) выберите последний вариант из списка – Use a formula to determine which cells to format (Использовать формулу для определения форматируемых ячеек). А сейчас – главный секрет! Ваша формула должна выдавать значение TRUE (ИСТИНА), чтобы правило сработало, и должна быть достаточно гибкой, чтобы Вы могли использовать эту же формулу для дальнейшей работы с Вашей таблицей.

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

=$G15 – это адрес ячейки.

G – это столбец, который управляет работой правила (столбец Really? в таблице). Заметили знак доллара перед G? Если не поставить этот символ и скопировать правило в следующую ячейку, то в правиле адрес ячейки сдвинется. Таким образом, правило будет искать значение Yes, в какой-то другой ячейке, например, H15 вместо G15. В нашем же случае надо зафиксировать в формуле ссылку на столбец ($G), при этом позволив изменяться строке (15), поскольку мы собираемся применить это правило для нескольких строк.

=”Yes” – это значение ячейки, которое мы ищем. В нашем случае условие проще не придумаешь, ячейка должна говорить Yes. Условия можно создавать любые, какие подскажет Вам Ваша фантазия!

Говоря человеческим языком, выражение, записанное в нашей формуле, принимает значение TRUE (ИСТИНА), если ячейка, расположенная на пересечении заданной строки и столбца G, содержит слово Yes.

Теперь давайте займёмся форматированием. Нажмите кнопку Format (Формат). В открывшемся окне Format Cells (Формат ячеек) полистайте вкладки и настройте все параметры так, как Вы желаете. Мы в своём примере просто изменим цвет фона ячеек.

Когда Вы настроили желаемый вид ячейки, нажмите ОК. То, как будет выглядеть отформатированная ячейка, можно увидеть в окошке Preview (Образец) диалогового окна New Formatting Rule (Создание правила форматирования).

Нажмите ОК снова, чтобы вернуться в диалоговое окно Conditional Formatting Rules Manager (Диспетчер правил условного форматирования), и нажмите Apply (Применить). Если выбранная ячейка изменила свой формат, значит Ваша формула верна. Если форматирование не изменилось, вернитесь на несколько шагов назад и проверьте настройки формулы.

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

Когда сделаете это, нажмите иконку справа от поля с адресом, чтобы вернуться к диалоговому окну. Область, которую Вы выделили, должна остаться обозначенной пунктиром, а в поле Applies to (Применяется к) теперь содержится адрес не одной ячейки, а целого диапазона. Нажмите Apply (Применить).

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

Вот и всё! Теперь осталось таким же образом создать правило форматирования для строк, в которых содержится ячейка со значением No (ведь версии Ubuntu с кодовым именем Chipper Chameleon на самом деле никогда не существовало). Если же в Вашей таблице данные сложнее, чем в этом примере, то вероятно придётся создать большее количество правил. Пользуясь этим методом, Вы легко будете создавать сложные наглядные таблицы, информация в которых буквально бросается в глаза.

Расширенная функция 5: создание сводных таблиц

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

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

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

Чтобы создать сводную таблицу, выделите данные, которые вы хотите включить, и перейдите на вкладку «Таблицы». Перейдите в «Форматировать как таблицу» и выберите один из форматов. Это упрощает добавление и редактирование информации позже, а сводная таблица сохраняет изменения. Выберите «Суммировать с помощью сводной таблицы».

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

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

КАК В ФОРМУЛЕ EXCEL ОБОЗНАЧИТЬ ПОСТОЯННУЮ ЯЧЕЙКУ

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

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

1. Вручную заполним первые графы учебной таблицы. У нас – такой вариант:

2. Вспомним из математики: чтобы найти стоимость нескольких единиц товара, нужно цену за 1 единицу умножить на количество. Для вычисления стоимости введем формулу в ячейку D2: = цена за единицу * количество. Константы формулы – ссылки на ячейки с соответствующими значениями.

3. Нажимаем ВВОД – программа отображает значение умножения. Те же манипуляции необходимо произвести для всех ячеек. Как в Excel задать формулу для столбца: копируем формулу из первой ячейки в другие строки. Относительные ссылки – в помощь.

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

Отпускаем кнопку мыши – формула скопируется в выбранные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами.

Ссылки в ячейке соотнесены со строкой.

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

Чтобы указать Excel на абсолютную ссылку, пользователю необходимо поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.

1. Создадим строку «Итого». Найдем общую стоимость всех товаров. Выделяем числовые значения столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9

2. Воспользуемся функцией автозаполнения. Кнопка находится на вкладке «Главная» в группе инструментов «Редактирование».

3. После нажатия на значок «Сумма» (или комбинации клавиш ALT+«=») слаживаются выделенные числа и отображается результат в пустой ячейке.

Сделаем еще один столбец, где рассчитаем долю каждого товара в общей стоимости. Для этого нужно:

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

2. Чтобы получить проценты в Excel, не обязательно умножать частное на 100. Выделяем ячейку с результатом и нажимаем «Процентный формат». Или нажимаем комбинацию горячих клавиш: CTRL+SHIFT+5

3. Копируем формулу на весь столбец: меняется только первое значение в формуле (относительная ссылка). Второе (абсолютная ссылка) остается прежним. Проверим правильность вычислений – найдем итог. 100%. Все правильно.

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

• $В$2 – при копировании остаются постоянными столбец и строка;
• B$2 – при копировании неизменна строка;
• $B2 – столбец не изменяется.

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

• Как следует из названия, данный тип отформатирует любое значение, к которому применяется конкретная формула. В этом примере все данные Excel, которые касаются будущих дат, будут выделены красным цветом.
• Выберите область в таблице, для которой вы хотите применить форматирование, и создайте новое правило с типом «Использовать формулу для определения форматируемых ячеек».
• Введите формулу = B2> СЕГОДНЯ ().
• Нажмите кнопку «Формат …» и выберите красный цвет в новом окне.
• Подтвердите выбор дважды нажав «ОК».

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

Фото: компании-производители, pixabay.com

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

Использование формул в таблицах

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

Ознакомиться с полным списком и вписываемыми аргументами пользователь может, нажав на ссылку «Справка по этой функции».

Для задания формулы:

• активировать ячейку, где будет рассчитываться формула;
• открыть «Мастер формул»;

или

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

или

применить и активирует плавающие подсказки.

На панели инструментов находится пиктограмма «Автосумма», которая автоматически подсчитывает сумму столбца. Чтобы воспользоваться инструментом:

• выделить диапазон;
• активировать пиктограмму.

Что такое Microsoft Excel?

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

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

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

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

Управление правилами

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

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

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

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

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

Для того, чтобы удалить правило, нужно его выделить, и нажать на кнопку «Удалить правило».

Кроме того, можно удалить правила и через основное меню условного форматирования. Для этого, кликаем по пункту «Удалить правила». Открывается подменю, где можно выбрать один из вариантов удаления: либо удалить правила только на выделенном диапазоне ячеек, либо удалить абсолютно все правила, которые имеются на открытом листе Excel.

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

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

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

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

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

Рассмотрим каждый из них.

Первые 10 элементов

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

Это значит, что если вам необходимо выделить первые 10 клеток, в которых находятся самые маленькие цифры, то нужно выбрать пункт «Последние 10 элементов».

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

Первые 10%

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

Если указать число 10 (оно используется по умолчанию), то вы увидите следующее.

Последние 10 элементов

Как было сказано выше, в данном случае выделяются те ячейки, в которых содержатся минимальные данные. Принцип ввода тот же самый – указываете нужное количество и кликаете на кнопку «OK».

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

Последние 10%

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

Выше среднего

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

Ниже среднего

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

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

Примеры формул

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

Выделите заказы из Техаса

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

=$F5='TX'

Подробнее читайте в этой статье: Выделите строки с условным форматированием .

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

Выделите даты в ближайшие 30 дней

Чтобы выделить даты, наступающие в ближайшие 30 дней, нам нужна формула, которая (1) гарантирует, что даты находятся в будущем, и (2) гарантирует, что даты находятся в 30 днях или меньше от сегодняшнего дня. Один из способов сделать это — использовать И функция вместе с СЕЙЧАС функция так:

= AND (B4> NOW (),B4( NOW ()+30))

Для текущей даты 18 августа 2016 года условное форматирование выделяет даты следующим образом:

В СЕЙЧАС функция возвращает текущую дату и время. Подробнее о том, как работает эта формула, см. В этой статье: Выделите даты в ближайшие N дней .

Выделите различия столбцов

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

=$B4$C4

Выделите отсутствующие значения

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

= COUNTIF (list,B5)=

Эта формула просто проверяет каждое значение в Список А по значениям в именованном диапазоне «список» (D5: D10). Когда счетчик равен нулю, формула возвращает ИСТИНА и запускает правило, которое выделяет значения в Список А которые отсутствуют в Список Б .

Видео: Как найти пропущенные значения с помощью COUNTIF

Выделите недвижимость с 3+ спальнями до 350 тыс. Долларов

Чтобы найти в этом списке объекты недвижимости, которые имеют как минимум 3 спальни, но стоят менее 300 000 долларов, вы можете использовать формулу, основанную на функции И:

= AND ($C5350000,$D5>=3)

Знаки доллара ($) фиксируют ссылку на столбцы C и D, а И функция используется, чтобы убедиться, что оба условия ИСТИННЫ. В строках, где функция И возвращает ИСТИНА, применяется условное форматирование:

Выделите верхние значения (динамический пример)

Хотя в Excel есть предустановки для «верхних значений», в этом примере показано, как сделать то же самое с формулой и как формулы могут быть более гибкими. Используя формулу, мы можем сделать рабочий лист интерактивным — когда значение в F2 обновляется, правило мгновенно реагирует и выделяет новые значения.

Формула, используемая для этого правила:

=B4>= LARGE (data,input)

Где «данные» — это именованный диапазон B4: G11, а «вход» — именованный диапазон F2. На этой странице есть .

Диаграммы Ганта

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

В этом листе используются два правила: одно для полосок, а другое — для затенения на выходных:

= AND (D>=$B5,D$C5) // bars = WEEKDAY (D,2)>5 // weekends

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

Простое окно поиска

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

Используемая формула:

= ISNUMBER ( SEARCH ($F,B2))

Для получения дополнительных сведений и полного объяснения см .:

• Статья: Как выделить ячейки, содержащие определенный текст
• Статья: Как выделить строки, содержащие определенный текст
• Видео: Как создать окно поиска для выделения данных