Содержание

Как сделать градацию в excel?

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

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

Располагается эта полезная возможность на вкладке «Главная» в области «Стили» под одноименной пиктограммой:

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

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

Выбрав пункт «Создать правило…», приложение отобразит окно:

В нем Вы можете выбрать тип правила и настроить его описание (подробнее читайте далее в статье).

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

Форматировать все ячейки на основании их значений

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

Гистограмма

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

Ширина ячейки принимается за 100%, что соответствует максимальному значению диапазона правила. Т.е. ячейка, содержащая максимальное значение будет залита полностью, а ячейка со значением в 2 раза меньшим максимальному – наполовину. В случае отрицательного значения, столбец будет окрашен другим цветом и иметь другую направленность (это можно изменить).

  • Показывать только столбец – установив флажок на данном поле, Вы сообщаете, что для диапазона ячеек правила необходимо скрывать содержимое и оставлять только формат;
  • Параметры значений – здесь устанавливаются максимальные и минимальные значения и их типы. В качестве типа может выступать число, процент, формула, процентиль либо по умолчанию (авто). Значение может быть только числовым. Все числа, меньше минимального (включая отрицательные), приравниваются к нулю, т.е. не содержат столбца. А те, которые больше максимального, приравниваются к 100% и закрашиваются полностью.
  • Внешний вид столбца – устанавливает способ заливки (сплошной или градиентный), границу и их цвета;
  • Направление столбца – определяет способ направленности (слева направо либо наоборот);
  • Кнопка «Отрицательные значения и ось…» – настройки отображения столбцов для отрицательных чисел. Что они позволяют:
    • Установить свой цвет заливки столбца и его границу или сделать их одинаковыми для всех значений (положительных и отрицательных. По умолчанию они различаются);
    • Задать положение оси или одинаковую направленность для всех значений.

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

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

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

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

  • Минимальным числом задан ноль, а значения меньше его, будут иметь такие же цвет и насыщенность;
  • Средним значением указана единица и желтый цвет. Это значит, что переход шкалы от красного к желтому будет осуществлен между 0 и 1;
  • 4 является максимальным значением. Все, что превышает его, получает те же установки. Переход от желтого к зеленому происходит между 1 и 4.

Наборы значков (флажков)

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

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

Форматировать только ячейки, которые содержат

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

Рассмотрим правила, которые имеются в этом пункте:

  • Значение ячейки. Предполагает работу с числами и текстом. Сравнение производится по шкале сортировки.
  • Текст. Позволяет проверить наличие или отсутствие подстроки в тексте.
  • Даты. С его помощью легко создать правила типа «вчера», «сегодня», «завтра», «на прошлой неделе», «в следующем месяце» и т.п.
  • Пустые. Форматирует пустые ячейки. Пробелы не учитываются.
  • Непустые. Противоположное предыдущему правилу.
  • Ошибки. Истинно, когда значением ячейки является ошибка.
  • Без ошибки. Противоположное предыдущему правилу.

Форматировать только первые и последние значения

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

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

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

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

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

Используем 2 условия со следующими формулами:

  • Если на складе нет товара, т.е. равен 0, то подсвечиваем позицию заказа красным – =ВПР(D3;A:B;2;ЛОЖЬ)=0;
  • Если на складе есть товар, но его количество меньше, чем указано в позиции заказа, то последнюю подсвечиваем желтым – =И(ВПР(D3;$A:$B;2;ЛОЖЬ) 0).

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

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

Остальные правила

Ничего не было сказано о еще двух видах правил, а именно:

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

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

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

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

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

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

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

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

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

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

Читать еще:  Как сделать дни недели в excel?

Разные цвета маркеров одного ряда данных на диаграмме

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

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

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

Также можно изменять цвета, изменив цвет каждый маркер данных вручную.

Важно: Чтобы успешно выполнить эти процедуры, необходимо иметь существующей диаграмме. Дополнительные сведения о выборе и добавление диаграмм просмотреть Доступные типы диаграмм в Office.

Автоматически изменяться все цвета маркер данных с помощью точка или фрагмента

На диаграмме выберите ряд данных, для которого вы хотите изменить цвета.

На вкладке Формат в группе Текущий фрагмент нажмите кнопку Формат выделенного фрагмента.

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

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

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

Варьироваться маркер цвета отдельных данных вручную

На диаграмме выделите маркер отдельных данных, который вы хотите изменить.

На вкладке Формат в группе Стили фигур выберите элемент Заливка фигуры.

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

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

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

Чтобы удалить цвет выделенного элемента диаграммы, выберите вариант Нет заливки.

Чтобы использовать цвет заливки, который не поддерживается в разделе Цвета темы или Стандартные цвета, щелкните Другие цвета заливки. В диалоговом окне цвета выберите цвет, который вы хотите использовать вкладку стандартный или Настраиваемый и нажмите кнопку ОК.

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

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

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

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

Повторите эти действия для каждой маркер данных, который вы хотите изменить.

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

Важно: Чтобы успешно выполнить эти процедуры, необходимо иметь существующей диаграмме. Дополнительные сведения о выборе и добавление диаграмм просмотреть Доступные типы диаграмм в Office.

Автоматически изменяться все цвета маркер данных с помощью точка или фрагмента

На диаграмме выберите ряд данных, для которого вы хотите изменить цвета.

Откройте вкладку Формат и выберите ряд из раскрывающегося списка элементов диаграммы и нажмите кнопку Область «Формат» на ленте.

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

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

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

Варьироваться маркер цвета отдельных данных вручную

На диаграмме выделите маркер отдельных данных, который вы хотите изменить.

На вкладке Формат выберите Заливка фигуры.

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

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

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

Чтобы удалить цвет выделенного элемента диаграммы, выберите вариант Нет заливки.

Чтобы использовать цвет заливки, который не поддерживается в разделе Цвета темы или Стандартные цвета, щелкните Другие цвета заливки. В диалоговом окне цветов для создания пользовательского заливку, используя доступные параметры и нажмите кнопку ОК.

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

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

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

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

Повторите эти действия для каждой маркер данных, который вы хотите изменить.

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

Условное форматирование в Excel. Примеры, цветовая шкала, наборы значков

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

Работа с большими таблицами Excel

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

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

Для чего нужно условное форматирование ячеек в Excel?

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

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

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

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

В старой версии программы Excel 2003 необходимо зайти в меню «Формат» и выбрать строчку «Условное форматирование». Для последующих версий Excel 2007, 2010 и 2013 в верхней панели во вкладке «Главная» следует отыскать раздел «Стили», в котором и присутствует меню «Условное форматирование». Кликнув на него, пользователь увидит весь перечень возможных операций.

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

Во всплывающем меню данного инструмента показано, какие существуют основные правила условного форматирования в Excel.

Читать еще:  Склад в excel как сделать самому

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Например, используя в работе набор из трех флажков, можно получить следующую картину: красные флажки будут стоять рядом с маленькими значениями (то есть менее 33%), желтые рядом со средними значениями (находящимися в диапазоне между 33 и 67%), а зеленые – с наибольшими значениями (более 67%).

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

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

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

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

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

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

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

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

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

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

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

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

Для этого следует зайти в вышеуказанное меню и в строку формулы ввести следующее: = B5>C5. Здесь B – это столбец марта, С – столбец апреля. Записанное условие можно будет протянуть на весь диапазон, в котором собраны данные. Его можно также протянуть дальше по столбцам при добавлении новых месяцев.

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

Добавление шкалы гистограммы в ячейку Excel

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

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

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

В первую очередь графически экспонируем в таблице количество заказов. В старых версиях Excel для реализации данной задачи нужно было прибегать к написанию сложных макросов или выполнять большой объемы работы, вручную вводить множество формул и форматировать диапазоны ячеек. Но начиная с версии Excel 2010 можно очень быстро получить такой эффект. В принципе достаточно просто выделить диапазон E4:E15 и присвоить форматирование гистограммой: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Гистограммы»-«Синяя»:

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

  1. В ячейку F2 введите относительную ссылку на ячейку =E2 и скопируйте ее вдоль целого столбца. А потом выделите диапазон F2:F13 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Гистограммы»-«Другие правила».
  2. В появившемся окне «Создание правила форматирования» выберите опцию: «Форматировать все ячейки на основании их значений» и поставьте галочку на против опции «Показывать только столбец».
  3. При необходимости выберите желаемый цвет и нажмите на кнопку ОК.

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

Читать еще:  Как из xls сделать xml в excel 2010?

Чтобы удалить только эти гистограммы выделите диапазон F2:F13 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Удалить правила»-«Удалить правила из выделенных ячеек». Или еще проще удалите целый столбец F предварительно выделив его (F:F), нажмите комбинацию горячих клавиш CTRL+-(минус).

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

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

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

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

Минидиаграммы в ячейках

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

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

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

2. Также можно воспользоваться текстовой функцией ПОВТОР, которая выводит в ячейку любой заданный символ нужное количество раз. Для этого вводим в соседнюю от цифр ячейку формулу =ПОВТОР(«|»;B2) и нажимаем «Enter», при шрифте Arial Cyr получится так:

3. Для вывода нестандартных символов (заранее зная код) можно использовать функцию СИМВОЛ. Например, символ с кодом 103 — черный прямоугольник шрифта Webdings, вводим в соседнюю ячейку формулу =ПОВТОР(СИМВОЛ(103);C2:C8) и нажимаем «Enter». Поэтому предварительно установите этот шрифт для ячеек C2:C8.

Также можно использовать символы других шрифтов, например символ с кодом 110 из шрифта Wingdings.

4. Остался применить последний способ – это стрелки в разделе «наборы значков», здесь имеется большой выбор разных элементов, которые также отображаются в самой ячейке.

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

Имеющиеся символы в шрифтах Webdings и Wingdings можно посмотреть в разделе «Вставка» — «Символы»

И в конце предлагаю оценить какая минидиаграмма самая интересная

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

Подписка. Посмотрел другие посты про Ексель- автор молодец.
Эксель — это простейший, крутейший инструмент в умелых руках.
Сделай обзор на формулы ВПР (vlookup) и Если(IF)

Жизнь многих офисных сотрудников, не знающих их, никогда не станет прежней)))

Спасибо)) В планах это уже есть и ещё многое другое)

вы часто ей пользуетесь?

Подскажите пожалуйста — есть столбик где появляется или «0» в ячеках или «1» в какой нибудь одной. Какой формулой найти номер ячейки в формате «B1» где выскочила эта «1»? У меня пока так: =ПОИСКПОЗ(1;O1:O67;0) тут выдается номер строки. Других вариантов нет?

Ну и еще — такие вложения — можно ли как то уменьшить? А то они и длиннее бывают 🙂 Имеется в виду вложение ЕСЛИ

=ЕСЛИ(‘Ввод Данных’!E55=»»;ЕСЛИ(‘Ввод Данных’!D54<>«Услуга (в руб.)»;ЕСЛИ(‘Ввод Данных’!D55<>«руб.»;ЕСЛИ(‘Ввод Данных’!D55<>«рублей»;’Ввод Данных’!B55*’Ввод Данных’!$F$35;’Ввод Данных’!B55);’Ввод Данных’!B55);’Ввод Данных’!B55);(‘Ввод Данных’!E55/100)*(ЕСЛИ(‘Ввод Данных’!D54<>«Услуга (в руб.)»;ЕСЛИ(‘Ввод Данных’!D55<>«руб.»;ЕСЛИ(‘Ввод Данных’!D55<>«рублей»;’Ввод Данных’!B55*’Ввод Данных’!$F$35;’Ввод Данных’!B55);’Ввод Данных’!B55);’Ввод Данных’!B55)))

Не совсем понятно.

Т.е. есть поле, примерно такое

И нужен адрес ячейки, в которой стоит 1? она всегда одна?

По второму вопросу — как вариант «раскидать» формулу по нескольким ячейкам, если хотите уменьшить вложенность, либо использовать именованные диапазоны, тогда вместо какого нибудь ‘Ввод Данных’!D54 будет имя диапазона, например InsData.

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

Да вложенность и удобство отслеживания переходов. Так и мучаюсь — сначала напишу в ряд (разложу) потом соединяю вместе ) 🙂 . замучился с этим ЕСЛИ — Думал может есть какая нибудь полегче формула, или с большим кол-вом параметров чтобы все в одной ЕСЛИ прописать для удобства написания программы 🙂 По диапазонам — какие это формулы назовите? почитаю про них.. Спасибо.

ну смотрите, если у вас столбец всегда один то можно просто написать =сцепить(«O»;ПОИСКПОЗ(1;O1:O67;0)) и все дела.

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

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

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

Заработало 🙂 Спасибо — интересная формула получилась, буду применять. Я правда в 2003 офисе сижу. Под новое оформление никак не перелезу половину того, что мне нужно в новом меню найти не могу 🙂 Старый наверное стал 🙂 Поэтому некоторые формулы не работают. Посмотрю где у меня в 2003 именованные диапазоны сидят и как с ними работать. Понял что это буду искать. 🙂 Еще раз спасибо.

2003 офис сильно устарел, в новых офисах заметно много разных фич добавилось, и не только в экселе. Не так давно ощутил на себе, насколько неудобно работать в офисе ниже 2010, когда мне на работе решили с какого-то перепугу понизить версию с 2010 до 2007) Быстро вернули обратно)

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

Я, возможно, непонятно объяснил, наверное, надо пример какой-то?

ВПР ищет только по строкам, ГПР — только по столбцам. С индекс и поискпоз можно возвращать значение ячейки, находящейся на пересечении искомых значений столбца и строки.

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

1. Разобрались ли Вы для себя, что все-таки быстрее работает, одна составная формула или много простых, которые по-очереди друг на друга ссылаются? На форумах результаты разные, собственные эксперименты точного результата не дают.

2. Получилось ли у Вас определить максимально возможную производительность самого Excel, после которой дальнейший апгрейд ПК не ускоряет работу? Провожу вычисления, которые затрагивают много миллионов ячеек разом, для чего поставил себе 10-ти ядерный Xeon. Прирост по сравнению со средними i7 был существенный. Обрадовался результату, купил двухсокетную плату, соответственно, поставил 2 десятиядерника. Прироста производительности практически не заметил.

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

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