Parus16.ru

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

Используем цвет ячеек в Excel

Используем цвет ячеек в Excel

Почему возникает необходимость раскрасить таблицы в Excel? У всех разные потребности. Кто-то создает расписание занятий для ребенка и хочет сделать его красочным. У кого-то сугубо деловой подход: разный цвет ячеек позволяет быстро анализировать данные. Для тех, и для других предназначена эта статья.

Начнем с простейшего случая, то есть с раскрашивания просто потому, что хочется:

  • выделите ячейки, которые должны быть закрашены одинаково (если ячейки расположены не рядом, то держите нажатой клавишу CTRL);
  • в контекстном меню или на вкладке Главная (меню кнопки Формат) выберите Формат ячеек;
  • на вкладке Заливка выберите основной цвет, и, если это вам нужно, цвет узора и узор (в примере цвет узора белый, основной цвет салатовый, узор — точки).

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

Используем цвет ячеек в Excel

Интересную возможность выделения ячеек цветом обеспечивают Цветовые шкалы. Градиентным способом окрашиваются наибольшие, наименьшие и средние значения.

Используем цвет ячеек в Excel

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

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

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

Используем цвет ячеек в Excel

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

Используем цвет ячеек в Excel

К ячейкам, оформленным цветом можно применять фильтр по цвету (фильтр можно включить на вкладке Данные).

Используем цвет ячеек в Excel

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

Читайте так же:
Можно ли сделать молочный коктейль миксером

Developing.ru

Как менять цвет ячейки по значению другой ячейки

  • Версия для печати
  • Цитата

Стоит такая задача в Excelе .

Ячейка С4 = 15, т.е. в нее записывается некоторое числовое значение.
Ячейка D4 = принимает значение «Good» или «Bad»

Необходимо менять цвет фона ячеки С4 по правилу:
Если D4 = «Good», то цвет ячеки C4 — Зеленый
Если D4 = «Bad», то цвет ячеки C4 — Красный

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

  • Цитата
  • Цитата

Спасибо pashulka,
Как раз то, что мне надо было,
далее хотел задать вопрос по тому, как менять цвет если в D4 находится число от 0 до 100% достоверности параметра,
но функция Условное форматирование реализует и это, все же какой функционал заложен в Excel по умолчанию ! 😛

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

Ну да ладно, зато это форматирование переносится кисточкой — командой Формат по образцу.

  • Цитата

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

В меню Правка выберите команду перейти (CTRL+G, F5), в появившемся стандартном диалоговом окне нажмите кнопку Выделить. Затем установите переключатель напротив условные форматы и кнопка OK.

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

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

IMHO Подобный вариант хорош тем, что контекстное меню не так легко изменить вручную, как стандартную панель инструментов … там удалить Вашу кнопку можно за пару секунд (впрочем как и восстановить)

  • Цитата

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

Читайте так же:
Бинбанк вход старая версия юридические лица

Но кнопка Формат по образцу, по моему не имеет аналогов, ведь все логично, ЭТО хочу СДЕЛАТЬ как TO и все работает — 3 действия.
Жаль что не во всех продуктах, где эту функцию можно было сделать, это не реализовано (перенос свойств одного объекта другому)

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

Цветной выпадающий список в Microsoft Excel

Из этой статьи Вы узнаете как создать выпадающий список в Excel и раскрасить его.

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

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

Пишите в том порядке, в котором хотите увидеть в списке. Раскрашивать пока не обязательно. По-настоящему цвет задаётся на другом этапе.

Создание выпадающего меню

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

предварительный вид выпадающего списка изображение с сайта www.andreyolegovich.ru

2. Выделите всё, что написали и кликните правой кнопкой мыши → Выберите команду Присвоить имя (Define Name).

предварительный вид выпадающего списка изображение с сайта www.andreyolegovich.ru

3. В поле Имя (Name) введите имя записей, например Результаты (Results), и нажмите кнопку ОК.

Убедитесь, что имя не содержит пробелы.

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

выбор названия для выпадающего списка www.andreyolegovich.ru

Выбор названия
www.andreyolegovich.ru

4. Щелкните ячейку на листе, в которую требуется поместить раскрывающийся список.

Перейдите в раздел Data и нажмите Data Validation.

Если в Вашей версии Excel надписи Data Validation нет — ищите значок

выбор названия для выпадающего списка www.andreyolegovich.ru

5. На вкладке Параметры (Settings) в поле Разрешить (Allow) нажмите кнопку Список (List).

указание источника для выпадающего списка www.andreyolegovich.ru

Указать источник
www.andreyolegovich.ru

6. В поле Источник (Source) введите знак равенства
(=), а сразу за ним — имя, присвоенное списку в шаге 3. Например: =Results.

указание источника для выпадающего списка www.andreyolegovich.ru

Указать источник
www.andreyolegovich.ru

7. Нажмите ОК

указание источника для выпадающего списка www.andreyolegovich.ru

Указать источник
www.andreyolegovich.ru

8. В выбранной ячейке появится список. Пока что чёрно-белый.

выпадающий список изображение с сайта www.andreyolegovich.ru

Выпадающий список (пока без цвета)
www.andreyolegovich.ru

Редактирование выпадающего списка

Изменение раскрывающегося списка, основанного на именованном диапазоне.

Откройте лист, содержащий именованный диапазон для раскрывающегося списка.

Мой список уже раскрашен, но на добавление нового элемента это никак не влияет.

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

выпадающий список добавление элемента изображение с сайта www.andreyolegovich.ru

Добавление элемента
www.andreyolegovich.ru

На вкладке Формулы (Formulas) нажмите кнопку Диспетчер имен (Name Manager).

выпадающий список добавление элемента изображение с сайта www.andreyolegovich.ru

Добавление элемента
www.andreyolegovich.ru

Читайте так же:
Можно ли в облако загрузить видео

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

выпадающий список добавление элемента изображение с сайта www.andreyolegovich.ru

Добавление элемента
www.andreyolegovich.ru

Изменяем диапазон. Я заменил 7 на 8.

выпадающий список добавление элемента изображение с сайта www.andreyolegovich.ru

Увеличение диапазона
www.andreyolegovich.ru

Затем добавляем цвет если нужно.

Чтобы посмотреть список всех доступных диапазонов нажминте CTRL + G или из вкладки Home перейдите в Find & Select и нажмите Go to:

выпадающий список диапазоны www.andreyolegovich.ru

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

выпадающий список диапазоны изображение с сайта www.andreyolegovich.ru

Добавление цвета в выпадающие ячейки

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

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

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

Чтобы сделать список цветным выделяем ячейку. Переходим в Home → Условное форматирование (Conditional Formatting) → Новое правило (New Rule)

выпадающий список изображение с сайта www.andreyolegovich.ru

Выбираем Format only cells that contain → Меняем условие на equal to Вводим первый элемент из нашего списка в данном случае OK → Format

форматирование элемента выпадающего списка изображение с сайта www.andreyolegovich.ru

Новое правило
www.andreyolegovich.ru

Я хочу, чтобы у варианта OK был зелёный фон.

Чтобы раскрасить все элементы выпадающего списка перейдём в редактирование правила.

Conditional Formattiong → Manage Rules

Создаем новое правило (New Rule) для каждого элемента списка. И так пока не создадите все необходимые правила. Я создал семь правил.

редактирование правила для выпадающего списка изображение с сайта www.andreyolegovich.ru

Проверяем созданный список правил.

редактирование правила для выпадающего списка изображение с сайта www.andreyolegovich.ru

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

Обучение

Пройти обучение Excel можно на сайте SkillBox

Возможные проблемы

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

Форматировать цвет ячейки на основе значения в другом листе и ячейке

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

например, если значение листа 2, строка 6, столбец 2, скажем, 4, то я хотел бы, чтобы цвет фона листа 1, строка 4, столбец 1 был зеленым. Если ни одно из значений в листе 2, столбец 2 не ссылается на строку particlar в листе 1, я хотел бы оставить его без цвета. Нет никакого запрета на одно и то же значение появляется несколько раз во второй колонке листа 2. Бонус престижность, если вы можете сказать мне, как снять цвет, если последнее значение в листе 2, указывающее на строку в листе 1 удаляется.

Я уверен, что для мастеров Excel это может быть тривиальным, но у меня редко есть возможность использовать excel и, конечно, нет времени, чтобы стать черным поясом в нем. Может ли кто-нибудь предложить мне совет, указатели или быструю формулу для этого? Если это займет какой-то сложный код VB, чтобы реализации, оно того не стоит.

Читайте так же:
Можно ли восстановить жесткий диск после форматирования

5 ответов

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

применить условное форматирование к Sheet1 использование значений из Sheet2 , вам нужно отразить значения в Sheet1 .

создание зеркала листа 2, столбец B в листе 1

  1. на Sheet1 .
  2. вставьте новый столбец, щелкнув правой кнопкой мыши заголовок столбца A и выбрав «вставить».

введите следующую формулу в А1:

Sheet1 , колонки A теперь должно точно отражать значения в Sheet2 , колонки B .

(Примечание: Если вам не нравится в столбце A , он работает так же, как и в графе Z или где еще.)

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

  1. остаться Sheet1 .
  2. выберите столбец B левой кнопкой мыши по его заголовку.
  3. выберите пункт меню Format > Conditional Formatting.

изменить Condition 1 на » Formula is » и введите следующую формулу:

выберите Format. кнопка и выберите зеленый фон.

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

скрытие зеркальной колонки

  1. остаться Sheet1 .
  2. щелкните правой кнопкой мыши заголовок столбца A и выбираем » Hide «.

это должно автоматически обновлять Sheet1 всякий раз, когда что-нибудь в Sheet2 изменяется.

вы также можете сделать это с помощью именованные диапазоны таким образом, вам не нужно копировать ячейки с листа 1 на Лист2:

определите именованный диапазон, скажем Sheet1Vals для столбца, который имеет значения, на которых вы хотите основывать свое условие. Вы можете определить новый именованный диапазон, используя InsertNameDefine. пункт меню. Введите свое имя, затем используйте браузер ячеек в Refers to поле для выбора ячеек, которые вы хотите в диапазоне. Если диапазон изменится со временем (добавление или удаление строк) вы можете использовать эту формулу вместо явного выбора ячеек:

добавить -1 до последнего ) если столбец имеет строку заголовка.

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

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

здесь [FirstCellInRange] — это адрес ячейки, которую вы хотите отформатировать и [Condition] значение вашей проверки.

например, если мои условия в Sheet1 имеют значения 1 , 2 и 3 и в колонке я форматирование столбца B в Sheet2 тогда мои условные форматы были бы чем-то вроде:

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

Я использую Excel 2003 —

проблема с использованием условного форматирования здесь заключается в том, что вы не можете ссылаться на другой лист или книгу в ваших условиях. Что вы можете сделать, это установить некоторый столбец на листе 1 равным соответствующему столбцу на листе 2 (в вашем примере =Sheet2!B6). Я использовал столбец F в моем примере ниже. Затем можно использовать условное форматирование. Выберите ячейку на листе 1, строку, столбец 1, а затем перейдите в меню Условное форматирование. Выберите «Formula Is» из выпадающее меню и установите условие «=$F$6=4». Нажмите на кнопку Формат, а затем выберите вкладку Шаблоны. Выберите цвет, который вы хотите, и вы сделали.

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

вы можете иметь до 3 условий, применяемых к ячейке (используйте кнопка Добавить >> в нижней части диалогового окна условного форматирования), поэтому, если последняя строка исправлена (например, вы знаете, что она всегда будет строкой 10), вы можете использовать ее в качестве условия для установки цвета фона в none. Предполагая, что последнее значение, о котором вы заботитесь, находится в строке 10 (все еще предполагая, что вы установили столбец F на листе 1 в соответствующие ячейки на листе 2), затем установите 1-е условие в Формулу =$F$10=»» и шаблон в None. Сделайте это первым условием, и это будет переопределите все следующие конфликтующие операторы.

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

скопируйте исходную книгу формата ctrl + g и выберите соответствующий диапазон

вставить его в новый рабочий лист, цвета будут все изменилось

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

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

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