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

Условное форматирование в excel как это работает?

как в экселе выделить ячейку цветом при условии Podsvetka dat 1 Как подсветить сроки и даты в ячейках в Excel? Таблица с данными

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

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

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

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

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

Не все фирмы покупают специальные программы для ведения дел. Многие пользуются MS Excel, ведь эта хо.

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

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

Как в экселе менять цвет ячейки в зависимости от значения – да очень просто и быстро. Для выделения ячеек цветом предусмотрена специальная функция «Условное форматирование», находящаяся на вкладке «Главная»:

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

Правила выделения ячеек

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

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

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

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

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

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

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

  • выделить цветом первое или последнее N-ое количество ячеек;
  • применить форматирование к заданному проценту ячеек;
  • выделить ячейки, содержащие значение выше или ниже среднего в массиве;
  • во вкладке «Другие правила» задать необходимый функционал.

Гистограммы

Если заливка ячейки цветом вас не устраивает – применяйте инструмент «Гистограмма». Предлагаемая окраска легче воспринимается на глаз в большом объеме информации, функциональные правила подстраиваются под требования пользователя.

Цветовые шкалы

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

Наборы значков

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

Создание, удаление и управление правилами

Функция «Создать правило» полностью дублирует «Другие правила» из перечисленных выше, создает выборку изначально по требованию пользователя.

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

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

Отбор ячеек по датам

Чтобы разобраться, как в excel сделать цвет ячейки от значения установленной даты, рассмотрим пример с датами закупок у поставщиков в январе 2019 года. Для применения такого отбора нужны ячейки с установленным форматом «Дата». Для этого перед внесением информации выделите необходимый столбец, щелкните правой кнопкой мыши и в меню «Формат ячеек» найдите вкладку «Число». Установите числовой формат «Дата» и выберите его тип по своему усмотрению.

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

  • выделяем столбцы с датами (в нашем случае за январь);
  • находим инструмент «Условное форматирование»;
  • в «Правилах выделения ячеек» выбираем пункт «Дата»;
  • в правой части форматирования открываем выпадающее окно с правилами;
  • выбираем подходящее правило (на примере выбраны даты за предыдущий месяц);
  • в левом поле устанавливаем готовый цветовой подбор «Желтая заливка и темно-желтый текст»
  • выборка окрасилась, жмем «ОК».

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

Выделение цветом столбца по условию

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

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

  • выделяем столбец с январскими закупками;
  • кликаем инструмент «Условное форматирование»;
  • переходим в «Правила выделения ячеек»;
  • пункт «Больше…»;
  • в правой части форматирования устанавливаем сумму 100 000 рублей;
  • в левом поле переходим на вкладку «Пользовательский формат» и выбираем синий цвет;
  • необходимая выборка окрасилась в синий цвет, жмем «ОК».

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

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

2. Проверка дат и сроков

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

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

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

2. Выберите в меню Формат — Условное форматирование

3. Задайте условие 1:

  • выберите — «формула»
  • введите формулу =B2<$D$1

4. Нажмите «А также >>» для ввода второго условия

5. Задайте условие 2:

  • выберите — «формула»
  • введите формулу =(B2-$D$1) < 7 , (разница между датами меньше 7 дней)
  • выберите необходимый формат

Формат

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

Как в Excel сравнить два столбца и удалить дубликаты (выделить, раскрасить, переместить) — Microsoft Excel для начинающих

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

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

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

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

Представьте, что у нас есть 2 столбца с именами людей – 5 имён в столбце A и 3 имени в столбце B. Необходимо сравнить имена в этих двух столбцах и найти повторяющиеся. Как Вы понимаете, это вымышленные данные, взятые исключительно для примера. В реальных таблицах мы имеем дело с тысячами, а то и с десятками тысяч записей.

Вариант А: оба столбца находятся на одном листе. Например, столбец A и столбец B.

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

Вариант В: Столбцы расположены на разных листах. Например, столбец A на листе Sheet2 и столбец A на листе Sheet3.

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

В Excel 2013, 2010 и 2007 есть встроенный инструмент Remove Duplicate (Удалить дубликаты), но он бессилен в такой ситуации, поскольку не может сравнивать данные в 2 столбцах. Более того, он может только удалить дубликаты. Других вариантов, таких как выделение или изменение цвета, не предусмотрено. И точка!

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

Сравниваем 2 столбца в Excel и находим повторяющиеся записи при помощи формул

Вариант А: оба столбца находятся на одном листе

  1. В первой пустой ячейке (в нашем примере это ячейка C1) запишем вот такую формулу:

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

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

$B$1 и $B$10000 это адреса первой и последней ячеек второго столбца, с которым будем выполнять сравнение.

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

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

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

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

Выделите ячейку C1 и нажмите Ctrl+C (чтобы скопировать формулу в буфер обмена), затем нажмите Ctrl+Shift+End (чтобы выделить все не пустые ячейки в столбе С) и, наконец, нажмите Ctrl+V (чтобы вставить формулу во все выделенные ячейки).

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

  1. Отлично, теперь все повторяющиеся значения отмечены как “Duplicate“:

Вариант В: два столбца находятся на разных листах (в разных книгах)

  1. В первой ячейке первого пустого столбца на листе Sheet2 (в нашем случае это столбец В) введите такую формулу:

Здесь Sheet3 – это название листа, на котором расположен 2-ой столбец, а $A$1:$A$10000 – это адреса ячеек от 1-ой до последней в этом 2-ом столбце.

Обработка найденных дубликатов

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

Показать только повторяющиеся строки в столбце А

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

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

Кликните правой кнопкой мыши и в контекстном меню выберите Insert (Вставить):

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

Дайте названия столбцам, например, “Name” и “Duplicate?” Затем откройте вкладку Data (Данные) и нажмите Filter (Фильтр):

После этого нажмите меленькую серую стрелку рядом с “Duplicate?“, чтобы раскрыть меню фильтра; снимите галочки со всех элементов этого списка, кроме Duplicate, и нажмите ОК.

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

  • Чтобы снова отобразить все строки столбца А, кликните символ фильтра в столбце В, который теперь выглядит как воронка с маленькой стрелочкой и выберите Select all (Выделить все). Либо Вы можете сделать то же самое через Ленту, нажав Data (Данные) >Select & Filter (Сортировка и фильтр) >Clear (Очистить), как показано на снимке экрана ниже:

Изменение цвета или выделение найденных дубликатов

Если пометки “Duplicate” не достаточно для Ваших целей, и Вы хотите отметить повторяющиеся ячейки другим цветом шрифта, заливки или каким-либо другим способом…

В этом случае отфильтруйте дубликаты, как показано выше, выделите все отфильтрованные ячейки и нажмите Ctrl+1, чтобы открыть диалоговое окно Format Cells (Формат ячеек).

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

Конечно, Вы можете изменить цвет заливки при помощи инструмента Fill (Цвет заливки) на вкладке Home (Главная), но преимущество диалогового окна Format Cells (Формат ячеек) в том, что можно настроить одновременно все параметры форматирования.

  1. Теперь Вы точно не пропустите ни одной ячейки с дубликатами:

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

  • Отфильтруйте таблицу так, чтобы показаны были только ячейки с повторяющимися значениями, и выделите эти ячейки.
  • Если 2 столбца, которые Вы сравниваете, находятся на разных листах, то есть в разных таблицах, кликните правой кнопкой мыши выделенный диапазон и в контекстном меню выберите Delete Row (Удалить строку):
  • Нажмите ОК, когда Excel попросит Вас подтвердить, что Вы действительно хотите удалить всю строку листа и после этого очистите фильтр. Как видите, остались только строки с уникальными значениями:

Если 2 столбца расположены на одном листе, вплотную друг другу (смежные) или не вплотную друг к другу (не смежные), то процесс удаления дубликатов будет чуть сложнее. Мы не можем удалить всю строку с повторяющимися значениями, поскольку так мы удалим ячейки и из второго столбца тоже. Итак, чтобы оставить только уникальные записи в столбце А, сделайте следующее:

  1. Отфильтруйте таблицу так, чтобы отображались только дублирующиеся значения, и выделите эти ячейки. Кликните по ним правой кнопкой мыши и в контекстном меню выберите Clear contents (Очистить содержимое).
  2. Очистите фильтр.
  3. Выделите все ячейки в столбце А, начиная с ячейки А1 вплоть до самой нижней, содержащей данные.
  4. Откройте вкладку Data (Данные) и нажмите Sort A to Z (Сортировка от А до Я). В открывшемся диалоговом окне выберите пункт Continue with the current selection (Сортировать в пределах указанного выделения) и нажмите кнопку Sort (Сортировка):
  5. Удалите столбец с формулой, он Вам больше не понадобится, с этого момента у Вас остались только уникальные значения.
  6. Вот и всё, теперь столбец А содержит только уникальные данные, которых нет в столбце В:

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

Оцените качество статьи. Нам важно ваше мнение:

Анализ выбросов в Excel

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

Если первое квартильное значение находится в C2, третье квартильное значение находится в ячейке D2, а межквартильный диапазон находится в ячейке E2, вы должны набрать "= C2- (1.5 E2) «найти нижний предел» и = D2 + (1,5 E2) "чтобы найти верхний предел. В общем, вы вводите" = первый квартиль — (1,5 межквартильный диапазон) «найти нижний предел и» = третий квартиль + (1,5 interquartile range) ", чтобы найти верхний предел.

Все, что ниже нижнего предела или выше верхнего предела, является выбросом.

Чтобы завершить тест выбросов в Excel, используйте логическую функцию «ИЛИ», чтобы определить, какие значения в вашем классе данных являются выбросами эффективным способом. Введите «= ИЛИ (ячейка данных> верхний предел, ячейка данных <нижний предел)», чтобы найти выбросы с соответствующими ссылками на ячейки вместо величин в квадратных скобках. Например, если данные находятся в ячейках с A2 по A101, верхний предел находится в ячейке F2, а нижний предел находится в ячейке G2, перейдите в ячейку B2 и введите «= OR (A2> $ F $ 2, A2 <$ G». $ 2) «использовать функцию (где знаки доллара перед« F »,« G »и« 2 »говорят Excel, что это не должно измениться при перетаскивании формулы вниз).

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

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

Создание списка с применением инструментов разработчика

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

  1. В первую очередь, эти инструменты нужно найти и активировать, так как по умолчанию они выключены. Переходим в меню “Файл”.Создание списка с применением инструментов разработчика
  2. В перечне слева находим в самом низу пункт “Параметры” и щелкаем по нему.Создание списка с применением инструментов разработчика
  3. Переходим в раздел “Настроить ленту” и в области “Основные вкладки” ставим галочку напротив пункта “Разработчик”. Инструменты разработчика будут добавлены на ленту программы. Кликаем OK, чтобы сохранить настройки.Создание списка с применением инструментов разработчика
  4. Теперь в программе есть новая вкладка под названием “Разработчик”. Через нее мы и будем работать. Сначала создаем столбец с элементами, которые будут источниками значений для нашего выпадающего списка.Создание списка с применением инструментов разработчика

Создать контрольный список в Excel

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

1] Активировать вкладку разработчика в Excel

В качестве первого шага необходимо включить вкладку «Разработчик» в Excel. Для этого перейдите в Файл и выберите Параметры . В диалоговом окне «Параметры Excel» выберите « Настроить ленту» , а в правой части установите флажок «Разработчик» и нажмите «ОК». Теперь вы можете видеть вкладку «Разработчик» на ленте Excel.

2] Подготовить задачи в Excel

Теперь подготовьте столбец Задачи и введите задачи, которые вы хотите выполнить. Скажем, я добавил задачи от E7 до E9.

3] Добавить флажки в Excel

Настало время добавить флажки. Рядом со столбцом Задачи нам нужно добавить флажки. Итак, для каждой записи в столбце E7-E9 нам нужно добавить флажки от F7 до F9. Для этого сначала нажмите «Разработчик», нажмите «Вставить» и установите флажок рядом с « Элементами управления». Теперь нажмите на ячейку Excel, в которую мы хотим вставить этот флажок, и в данном случае это F7.

Мы видим, что некоторый текст по умолчанию был добавлен вместе с флажком. Чтобы удалить это, щелкните правой кнопкой мыши по флажку, выберите «Редактировать текст» и удалите текст. Повторите этот шаг также для оставшихся столбцов (в данном случае F8 и F9).

4] Назначьте ячейку для каждого флажка

Теперь нам нужно назначить ячейку каждому флажку, чтобы при установке и снятии флажка значения TRUE и FALSE должны отображаться соответственно. Для этого щелкните правой кнопкой мыши по флажку и выберите « Формальный контроль».

В диалоговом окне «Формальное управление» на вкладке «Управление» укажите адрес ячейки в поле « Ссылка на ячейку» , которое вы хотите назначить для флажка. Позвольте мне выбрать ячейку “H7”. Убедитесь, что адрес ячейки указан только как H7 ’, а не в каком-либо другом формате. Повторите этот шаг для остальных флажков.

5] Применить условное форматирование

Выберите задачи, которые вы добавили в предыдущих шагах, нажмите «Условное форматирование» на вкладке «Главная» и выберите «Новое правило». Теперь выберите тип правила «Использовать формулу, чтобы определить, какие ячейки форматировать». В текстовом поле условия нам нужно проверить значение ячейки, которая обновляется, когда флажок отмечен как ИСТИНА или нет.

Затем нажмите кнопку «Формат», выберите «Зачеркнутый» в разделе «Эффекты», выберите красный цвет в раскрывающемся списке «Цвет» и нажмите «ОК». Повторите этот шаг для каждой задачи, которую вы ввели.

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

Это простой способ создать контрольный список в Excel. Смотрите этот пост, если вы хотите знать, как сделать контрольный список в Word.

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

Всего предусмотрено три варианта форматирования:

  • Гистограмма;
  • Цветовая шкала;
  • Значки.

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

  1. Для начала выделите ячейки с данными, которые собираетесь оформить для акцентирования внимания.
  2. Теперь переместитесь во вкладку «Главная», если она не открыта у вас по умолчанию.
  3. Здесь, в блоке «Стили» кликните по кнопке «Условное форматирование». Появится выпадающее меню, где вам нужно будет обратить внимание на пункты: «Гистограммы», «Цветовые шкалы» и «Наборы значков». О них мы писали выше.

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

Настройка гистограммы

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

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

Цветовая шкала

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

Наборы значков

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

  • Направления;
  • Фигуры;
  • Индикаторы;
  • Оценки.

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

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

При выборе индикаторов из блока «Направления» помимо цветового оформления, используется ещё сигнализирование в виде направлений. Так, стрелка, повернутая указателем вверх, применяется к большим величинам, влево – к средним, вниз – к малым.

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

Изменение правил выделения ячеек

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

  1. Кликните по блоку инструментов «Условное форматирование». Из контекстного меню выберите «Правила выделения ячеек».
  2. Там будет отображено семь основных правил:
    • «Больше». В случае выбора этого вариант открывается окно, где нужно установить, значения больше какого числа требуется выделять условным форматированием. Вы можете указать адрес ячейки, из которой требуется брать это минимальное число или прописать его вручную, если оно будет неизменным. В соседнем поле нужно выбрать, каким образом будет идти выделение подходящих ячеек: светло-красная заливка и темно-красный цвет (по умолчанию); желтая заливка и темно-желтый текст; красный текст, и т.д. Можно задать пользовательский формат.
    • Очень похожий принцип работы у варианта «Меньше», но только здесь происходит наоборот – выделяются ячейки с числами меньше указанных.
    • Правило «Между» создает интервал между чисел. Данные, выходящие в этот интервал выделяются по аналогии с предыдущими правилами.
    • «Равно». Здесь вы задаете какое-то конкретное число и программа выделяет все ячейки, в которых оно содержится (в выделенной области).
    • «Текст содержит». Это правило уже применяется в тех случаях, когда работа ведется с ячейками, где прописан текст, а не числа. В окне установки правила следует указать слово, часть слова, или последовательный набор слов, при нахождении которых, соответствующие ячейки будут выделяться. Выделение ячеек настраивается аналогичным образом, как и в предыдущих вариантах.
    • «Дата». Правило применяется к ячейкам, которые содержат данные в формате дат. Вы можете задать как какую-то конкретную дату, так и выбрать готовые варианты дат: сегодня, вчера, завтра, за последние 7 дней, и т.д.
    • «Повторяющиеся значения». Здесь выделение происходит по одному из критериев: повторяющиеся это данные или уникальные.

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

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

Рассмотрим доступные критерии отбора подробнее:

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

Примечательно, что вы можете настроить эти фильтры под себя. Например, сделать выделение не первых 10 элементов, а первых 5 или 3.

Создание пользовательских правил

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

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

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

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

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

Удаление «Условного форматирования»

Если вам нужно удалить условное форматирование для ячеек, то здесь нет ничего сложного:

  1. Кликните в блоке с инструментами по пункту «Условное форматирование».
  2. Из контекстного меню выберите «Удалить правила».
  3. Здесь вы можете выбрать два варианта:
    • «Удалить правила из выделенных ячеек». В таком случае очищается условное форматирование только в выделенных ячейках. Если на листе есть другие ячейки с ним, но они не выделены, то условное форматирование в них не пострадает.
    • «Удалить правила со всего листа». В этом случае происходит удаление всего условного форматирования на листе, где вы сейчас находитесь.

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

Случайная выборка в Excel

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

Исходный набор данных:

Сначала вставим слева два пустых столбца. В ячейку А2 впишем формулу СЛЧИС (). Размножим ее на весь столбец:

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

Чтобы вставились значения, а не формула, щелкаем правой кнопкой мыши по столбцу В и выбираем инструмент «Специальная вставка». В открывшемся окне ставим галочку напротив пункта «Значения»:

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

Выбор уникальных и повторяющихся значений в Excel

Для примера возьмем историю взаиморасчетов с контрагентами, как показано на рисунке:

История взаиморасчетов.

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

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

  1. Выделите первый столбец таблицы A1:A19.
  2. Выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Дополнительно».

Дополнительно.

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

Только уникальные записи.

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

Теперь нам необходимо немного модифицировать нашу исходную таблицу. Выделите первые 2 строки и выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Вставить» или нажмите комбинацию горячих клавиш CTRL+SHIFT+=.

Вставить 2 строки.

У нас добавилось 2 пустые строки. Теперь в ячейку A1 введите значение «Клиент:».

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

Перед тем как выбрать уникальные значения из списка сделайте следующее:

    Перейдите в ячейку B1 и выберите инструмент «ДАННЫЕ»-«Работа с данными»-«Проверка данных».

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

Источник.

выпадающих список.

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

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

Выборка ячеек из таблицы по условию в Excel:

    Выделите табличную часть исходной таблицы взаиморасчетов A4:D21 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматируемых ячеек».

Создать правило.Использовать формулу.

Зеленая заливка.

Готово.

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

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

Принцип действия автоматической подсветки строк по критерию запроса очень прост. Каждое значение в столбце A сравнивается со значением в ячейке B1. Это позволяет найти уникальные значения в таблице Excel. Если данные совпадают, тогда формула возвращает значение ИСТИНА и для целой строки автоматически присваивается новый формат. Чтобы формат присваивался для целой строки, а не только ячейке в столбце A, мы используем смешанную ссылку в формуле =$A4.

Excel works!

menu

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

Специальная вставка в Excel

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

Специальная вставка в Excel. Пример использования

Применяется, если вы скопировали данные с расчетами и хотите вставить только значения этих данных уже без формул. Сделайте — Копировать (Ctrl+ C) нужные ячейки — выберите ячейку/диапазон для вставки — кликаем правой кнопкой мыши и выбираем Специальную вставку (Ctrl + Alt + V) .

Специальная вставка в Excel

Затем появится таблица с картинки номер 1. Выбираем галочку «только значения».

Это был пример, а теперь разберем все предлагаемые варианты.

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

Далее еще интереснее

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

Т.е. если нужно наложить дополнительные условия.

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

— вставить без рамки. Вставляем без формата границ

— вставить ширины столбцов. Вставляем форматирование Ширина столбца.

— формулы и форматы чисел. Вставляются и формулы и форматы

— значения и форматы чисел. Очень удобно. Вставляется не только значения, но и форматы чисел.

Раздел Операция (сложить, вычесть, разделить, умножить)

Классная штука! Например, у вас есть два диапазона

Специальная вставка в Excel

А вам нужно разделить данные из столбца D на данные из столбца B (см. картинку). Без формул. Здесь нам и пригодится Специальная вставка в Excel.

Выделяем диапазон B3:B5. Копируем. Выделяем диапазон D3:D5. Нажимаем Специальная вставка. И отмечаем галочку «разделить»

Специальная вставка в Excel

Вуаля! Попробуйте сами — понравится.

Напоследок три бомбы специальной вставки

Гарантирую, эти возможности сохранят вам кучу времени

1. Пропускать пустые ячейки

У вас есть диапазон из столбца D. А надо его вставить в В без потери данных в В.

Специальная вставка в Excel

Ну вы поняли. Если не использовать функцию «Пропускать пустые ячейки» специальной вставки, то пустыми значениями из столбца D затрутся непустые значение из B. Это огорчает. Выручает Специальная вставка.

2. Транспонировать. Когда столбцы надо сделать строками и наоборот. Я даже отдельную статью написал

3. В левом нижнем углу есть кнопка Вставить связь. Каюсь, узнал о ней только несколько месяцев назад. Все просто: вставляются связи на те ячейки (по-простому ссылки), которые вы выделили. Если у вас разрывный диапазон супер-удобно!

Отображение чередующейся заливки

На рисунке 1 вы видите пример. Вот как применить чередующуюся окраску к строкам.

  1. Выберите диапазон ячеек для форматирования.
  2. Выполните команду Главная → Условное форматирование → Создать правило. Откроется диалоговое окно Создание правила форматирования.
  3. В разделе Выберите тип правила укажите вариант Использовать формулу для определения форматируемых ячеек.
  4. Введите следующую формулу в поле Форматировать значения, для которых следующая формула является истиной: =ОСТАТ(СТРОКА();2)=0 .

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

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

При таком условном форматировании используется функция СТРОКА (возвращающая номер строки) и функция ОСТАТ (возвращающая остаток деления ее первого аргумента на второй аргумент). Для ячеек из четных строк функция ОСТАТ возвращает значение 0, и форматирование применяется к ячейккам этих строк.

Чтобы обеспечить чередование столбцов, используйте вместо функции СТРОКА функцию СТОЛБЕЦ.

Поворот текста в ячейке

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

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

  1. Сначала создать надпись, а потом её повернуть.
  2. Настроить поворот надписи в ячейке, а потом ввести текст.

Варианты различаются незначительно, поэтому рассмотрим только один из них. Для начала я объединил шесть строк в одну при помощи кнопки Объединить и поместить в центре на Главной вкладке (см. рис. 20) и ввёл обобщающую надпись (см. рис. 21).

*

Рисунок 20 – Кнопка объединения ячеек

*

Рисунок 21 – Сначала создаём горизонтальную подпись

Далее опять же на Главной вкладке щёлкаем по кнопке направления текста и в раскрывающемся списке выбираем пункт Повернуть текст вверх (см. рис. 22).

*

Рисунок 22 – Кнопка поворота текста

Можно дополнительно уменьшить ширину столбца (см. рис. 23). Готово!

*

Рисунок 23 – Вертикальный текст ячейки

Если есть такое желание, угол поворота текста вы можете задать вручную. В том же списке (см. рис. 22) выберите пункт Формат выравнивания ячейки и в открывшемся окошке задайте произвольный угол и выравнивание (см. рис. 24).

Рисунок 24 – Задаём произвольный угол поворота текста

Расширенное форматирование списка

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

Создание столбца ИСТИНА/ЛОЖЬ

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

Правой кнопкой мыши нажмите на первое поле флажка и выберите «Формат объекта».

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

На вкладке «Элемент управления» в окне «Формат объекта» нажмите на кнопку выбора ячейки с правой стороны поля «Связь с ячейкой».

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

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

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

Общее число предметов и подсчет отмеченных предметов в списке

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

=СЧЁТЕСЛИ(C2:C8; ИСТИНА)

=COUNTIF(C2:C8,TRUE)

в ячейку справа от ячейки «Упакованные предметы» и нажмите Enter. Как показано в примере ниже, функция подсчитает число ячеек в колонке С (с С2 по С8), имеющих значение ИСТИНА или TRUE.

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

Скрыть столбец ИСТИНА/ЛОЖЬ

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

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

Как проверить, все ли галочки сняты

Для этого выберите ячейку «Я готов» и введите

=ЕСЛИ(B10=B11;»Да»;»Нет»)

=IF(B10=B11,»YES»,»NO»)

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

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

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

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

Создайте правило, открыв «Главная»«Условное форматирование»«Создать правило»«Диспетчер правил условного форматирования»«Использовать формулу для определения форматируемых ячеек». Введите

в поле «Форматировать значения, для которых следующая формула является истинной:». Замените значения В10 и В11 адресами ячеек «Общее количество предметов» и «Упакованные предметы», если это не одни и те же ячейки.

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

Создайте еще одно правило, но в поле «Форматировать значения, для которых следующая формула является истинной:» введите формулу

=$B10=$B11

Затем нажмите «Формат», выберите цвет и нажмите «ОК».

В окне «Диспетчер правил условного форматирования» введите адреса ячеек, которые должны быть заполнены цветом, в поле «Применяется к». Введите те же адреса для обоих правил. В нашем случае это =$B$13. Нажмите «ОК».

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

Ячейка «Я готов» окрасится зеленым цветом, если все галочки будут сняты, или красным, если нет.

Ссылка на основную публикацию