Как подсветить слово в ячейке excel
Имеется таблица Excel размером 193 строки на 132 столбца.
Нужно в каждом столбце случайным образом выделить жёлтым цветом отдельные ячейки.
1) случайный выбор ячейки по каждому столбцу должен происходить независимо;
2) в каждом столбце должно быть выбрано и покрашено жёлтым цветом разное количество случайных ячеек (например, в 1-ом столбце таких ячеек должно быть выбрано случайным образом 5 штук, а во 2-ом столбце 52 штуки).
Не могли бы Вы подсказать, как это сделать?
Заранее большое спасибо!
Какие мин и макс количества закрашенных в одном столбце? === Какой смысл в таком отображении? Всего сразу не увидеть, пролистывать долго. Какие дальнейшие действия? Может быть, красить не нужно, а, например, создать отдельный список.
@Asidert, я так понимаю, что если случайным образом выделить данные из разных столбцов в отдельный список, то я не смогу произвести импутацию значений. То есть, по идее, пустые клеточки должны быть хаотично разбросаны по таблице, приблизительно повторяя характер missing data в моей основной таблице с данными, для которой я и пытаюсь выбрать наиболее подходящую технику импутации на этой опытной выборке. Мин. и макс. количества данных в разных столбцах разные: от 1% до 50% значений.
@Александр: их по всей этой таблице будет около 1000 - что-то мало Вы насчитали. При указанных данных практика показала - 5000-8000
2 ответа 2
Предполагается, что диапазон начинается с ячейки A1. Константами задаются количество строк и столбцов диапазона, max и min процент количества выделяемых ячеек в одном столбце.
Чтобы в циклах не проверять наличие диапазона rRng, формируем диапазон сразу (Set rRng = Cells(1, 1)). Поэтому следует учитывать, что в ячейке A1 заливка "технологическая". Для большей чистоты обработки "технологической" можно назначить ячейку вне обрабатываемого диапазона.
Процедура формирует диапазон ячеек, выбранных случайным образом. Далее с диапазоном можно проводить любые операции: красить, чистить, удалять данные, записывать, сравнивать.
При тестовом запуске процедуры с указанными константами в диапазон записывалось от 5229 до 8173 ячеек (запуск на 100 000 итераций)
В голову пришло такое решение. У меня есть таблица со словами, мне нужно, чтобы в каждом столбце подсвечивалось случайное слово:
Для этого я в новой строке, в ячейке прописал формулу, которая записывала бы в эту ячейку случайное слово из одного столбца =ИНДЕКС(A3:A43;СЛУЧМЕЖДУ(1;СЧЁТЗ(A3:A1227))) . Где А3:А43 " - это диапазон ячеек в котором нужно подсветить слово
СЛУЧМЕЖДУ(1;СЧЁТЗ(A3:A1227)) - это случайное значение от 1 до СЧЁТЗ(A3:A1227) ,
СЧЁТЗ(A3:A1227) - выдаёт общее количество заполненных словами ячеек в диапазоне A3:A1227 . Номер ячейки 1227 я сделал таким большим, т.к. в перспективе столбцы будут дополняться. Если не будут дополняться, то диапазон можно оставить такой же, как в начале А3:А43 .
Растянул эту формулу за край ячейки на остальные столбцы.
Применил условное форматирование для всей таблицы (подсветить повторяющиеся значения)
Т.к. в первой строке записываются значения из моей таблицы, то в таблице эти значения подсвечиваются. Затем я просто скрыл первую строку, чтобы не мешала.
Все очень просто. Хотим, чтобы ячейка меняла свой цвет (заливка, шрифт, жирный-курсив, рамки и т.д.) если выполняется определенное условие. Отрицательный баланс заливать красным, а положительный - зеленым. Крупных клиентов делать полужирным синим шрифтом, а мелких - серым курсивом. Просроченные заказы выделять красным, а доставленные вовремя - зеленым. И так далее - насколько фантазии хватит.
Чтобы сделать подобное, выделите ячейки, которые должны автоматически менять свой цвет, и выберите в меню Формат - Условное форматирование (Format - Conditional formatting) .
В открывшемся окне можно задать условия и, нажав затем кнопку Формат (Format) , параметры форматирования ячейки, если условие выполняется. В этом примере отличники и хорошисты заливаются зеленым, троечники - желтым, а неуспевающие - красным цветом:
Кнопка А также>> (Add) позволяет добавить дополнительные условия. В Excel 2003 их количество ограничено тремя, в Excel 2007 и более новых версиях - бесконечно.
Если вы задали для диапазона ячеек критерии условного форматирования, то больше не сможете отформатировать эти ячейки вручную. Чтобы вернуть себе эту возможность надо удалить условия при помощи кнопки Удалить (Delete) в нижней части окна.
Другой, гораздо более мощный и красивый вариант применения условного форматирования - это возможность проверять не значение выделенных ячеек, а заданную формулу:
Если заданная формула верна (возвращает значение ИСТИНА), то срабатывает нужный формат. В этом случае можно задавать на порядок более сложные проверки с использованием функций и, кроме того, проверять одни ячейки, а форматировать - другие.
Выделение цветом всей строки
Главный нюанс заключается в знаке доллара ($) перед буквой столбца в адресе - он фиксирует столбец, оставляя незафиксированной ссылку на строку - проверяемые значения берутся из столбца С, по очереди из каждой последующей строки:
Выделение максимальных и минимальных значений
Ну, здесь все достаточно очевидно - проверяем, равно ли значение ячейки максимальному или минимальному по диапазону - и заливаем соответствующим цветом:
В англоязычной версии это функции MIN и MAX, соответственно.
Выделение всех значений больше(меньше) среднего
Аналогично предыдущему примеру, но используется функция СРЗНАЧ (AVERAGE) для вычисления среднего:
Скрытие ячеек с ошибками
Чтобы скрыть ячейки, где образуется ошибка, можно использовать условное форматирование, чтобы сделать цвет шрифта в ячейке белым (цвет фона ячейки) и функцию ЕОШ (ISERROR) , которая выдает значения ИСТИНА или ЛОЖЬ в зависимости от того, содержит данная ячейка ошибку или нет:
Скрытие данных при печати
Аналогично предыдущему примеру можно использовать условное форматирование, чтобы скрывать содержимое некоторых ячеек, например, при печати - делать цвет шрифта белым, если содержимое определенной ячейки имеет заданное значение ("да", "нет"):
Заливка недопустимых значений
Сочетая условное форматирование с функцией СЧЁТЕСЛИ (COUNTIF) , которая выдает количество найденных значений в диапазоне, можно подсвечивать, например, ячейки с недопустимыми или нежелательными значениями:
Проверка дат и сроков
Поскольку даты в Excel представляют собой те же числа (один день = 1), то можно легко использовать условное форматирование для проверки сроков выполнения задач. Например, для выделения просроченных элементов красным, а тех, что предстоят в ближайшую неделю - желтым:
Счастливые обладатели последних версий Excel 2007-2010 получили в свое распоряжение гораздо более мощные средства условного форматирования - заливку ячеек цветовыми градиентами, миниграфики и значки:
Вот такое форматирование для таблицы сделано, буквально, за пару-тройку щелчков мышью. :)
У вас большой монитор, но таблицы, с которыми вы работаете - еще больше. И, пробегая взглядом по экрану в поисках нужной информации, всегда есть шанс "соскользнуть" взглядом на соседнюю строчку и посмотреть не туда. Я даже знаю людей, который для таких случаев постоянно держат недалеко от себя деревянную линейку, чтобы приложить ее к строке на мониторе. Технологии будущего!
А если при движении активной ячейки по листу будет подсвечиваться текущая строка и столбец? Своего рода координатное выделение примерно такого вида:
Поудобнее, чем линейка, правда?
Есть несколько способов разной сложности, чтобы реализовать такое. Каждый способ - со своими плюсами и минусами. Давайте разберем их детально.
Способ 1. Очевидный. Макрос, выделяющий текущую строку и столбец
Самый очевидный путь для решения нашей проблемы "в лоб" - нам нужен макрос, который будет отслеживать изменение выделения на листе и выделять целую строку и столбец для текущей ячейки. Также желательно иметь возможность при необходимости включать и отключать эту функцию, чтобы такое крестообразное выделение не мешало нам вводить, например, формулы, а работало только тогда, когда мы просматриваем список в поисках нужной информации. Это приводит нас к трем макросам (выделения, включения и выключения), которые нужно будет добавить в модуль листа.
Откройте лист со таблицей, в которой хотите получить такое координатное выделение. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этих трех макросов:
Измените адрес рабочего диапазона на свой - именно в пределах этого диапазона и будет работать наше выделение. Затем закройте редактор Visual Basic и вернитесь в Excel.
Нажмите сочетание клавиш ALT+F8, чтобы открыть окно со списком доступных макросов. Макрос Selection_On, как нетрудно догадаться, включает координатное выделение на текущем листе, а макрос Selection_Off - выключает его. В этом же окне, нажав кнопку Параметры (Options) можно назначить этим макросам сочетания клавиш для удобного запуска.
Плюсы этого способа:
- относительная простота реализации
- выделение - операция безобидная и никак не изменяет содержимое или форматирование ячеек листа, все остается как есть
Минусы этого способа:
- такое выделение некорректно работает в том случае, если на листе есть объединенные ячейки - выделяются сразу все строки и столбцы, входящие в объединение
- если случайно нажать клавишу Delete, то очистится не только активная ячейка, а вся выделенная область, т.е. удалятся данные из всей строки и столбца
Способ 2. Оригинальный. Функция ЯЧЕЙКА + Условное форматирование
Этот способ хотя и имеет пару недостатков, мне представляется весьма изящным. Реализовать что-либо, используя только встроенные средства Excel, минимально влезая в программирование на VBA - высший пилотаж ;)
Способ основан на использовании функции ЯЧЕЙКА (CELL), которая может выдавать массу различной информации по заданной ячейке - высоту, ширину, номер строки-столбца, числовой формат и т.д.. Эта функция имеет два аргумента:
- кодовое слово для параметра, например "столбец" или "строка"
- адрес ячейки, для которой мы хотим определить значение этого параметра
Хитрость в том, что второй аргумент не является обязательным. Если он не указан, то берется текущая активная ячейка.
Вторая составляющая этого способа - условное форматирование. Эта крайне полезная функция Excel позволяет автоматически форматировать ячейки, если они удовлетворяют заданным условиям. Если соединить эти две идеи в одно целое, то получим следующий алгоритм реализации нашего координатного выделения через условное форматирование:
- Выделяем нашу таблицу, т.е. те ячейки, в которых в будущем должно отображаться координатное выделение.
- В Excel 2003 и более старших версиях открываем меню Формат - Условное форматирование - Формула (Format - Conditional Formatting - Formula) . В Excel 2007 и новее - жмем на вкладке Главная (Home) кнопку Условное форматирование - Создать правило (Conditional Formatting - Create Rule) и выбираем тип правила Использовать формулу для определения форматируемых ячеек (Use formula)
- Вводим формулу для нашего координатного выделения:
Все почти готово, но остался один нюанс. Дело в том, что Excel не считает изменение выделения изменением данных на листе. И, как следствие, не запускает пересчет формул и перекраску условного форматирования только при изменении положения активной ячейки. Поэтому добавим в модуль листа простой макрос, который будет это делать. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этого простого макроса:
Теперь при изменении выделения будет запускаться процесс пересчета формулы с функцией ЯЧЕЙКА в условном форматировании и заливаться текущая строка и столбец.
Плюсы этого способа:
- Условное форматирование не нарушает пользовательское форматирование таблицы
- Этот вариант выделения корректно работает с объединенными ячейками.
- Нет риска удалить целую строку и столбец с данными при случайном нажатии Delete.
- Макросы используются минимально
Минусы этого способа:
- Формулу для условного форматирования надо вводить вручную.
- Нет быстрого способа включить-выключить такое форматирование - оно включено всегда, пока не будет удалено правило.
Способ 3. Оптимальный. Условное форматирование + макросы
Золотая середина. Используем механизм отслеживания выделения на листе при помощи макросов из способа-1 и добавим к нему безопасное выделение цветом с помощью условного форматирования из способа-2.
Откройте лист со таблицей, в которой хотите получить такое координатное выделение. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этих трех макросов:
Не забудьте изменить адрес рабочего диапазона на адрес своей таблицы. Закройте редактор Visual Basic и вернитесь в Excel. Чтобы использовать добавленные макросы, нажмите сочетание клавиш ALT+F8 и действуйте аналогично способу 1.
Способ 4. Красивый. Надстройка FollowCellPointer
Excel MVP Jan Karel Pieterse родом из Нидерландов раздает у себя на сайте бесплатную надстройку FollowCellPointer(36Кб), которая решает ту же задачу, отрисовывая с помощью макросов графические линии-стрелки для подсветки текущей строки и столбца:
Красивое решение. Не без глюков местами, но попробовать точно стоит. Качаем архив, распаковываем на диск и устанавливаем надстройку:
Excel для Microsoft 365 Word для Microsoft 365 PowerPoint для Microsoft 365 Excel 2021 Word 2021 PowerPoint 2021 Excel 2019 Word 2019 PowerPoint 2019 Excel 2016 Word 2016 PowerPoint 2016 Excel 2013 Word 2013 PowerPoint 2013 Excel 2010 Word 2010 PowerPoint 2010 Еще. Меньше
В Microsoft Word, Excel и PowerPoint можно применять эффекты свечения и сияния к фигурам, тексту и другим объектам. Чтобы просмотреть параметры свечения и сияния:
Выберите объект, который вы хотите отформатирование.
В области Средства рисованияна вкладке Формат нажмите кнопку Эффекты фигуры или Текстовые эффекты.
Подсветка
Заранее заранее. Чтобы применить один из наиболее распространенных вариантов свечения, нажмите кнопку Заранее ивыберите нужный вариант.
Цвет. Чтобы создать пользовательский цвет для свечения, щелкните Цвет и выберите нужный цвет. Чтобы изменить цвет, который не входит в Цвета темы, выберите команду Другие цвета, а затем выберите готовый вариант на вкладке Обычные или создайте собственный на вкладке Спектр. Пользовательские цвета и цвета на вкладке Обычные не обновляются при последующем изменении тема документа.
Размер. Чтобы задать размер свечения, переместили ползунок Размер или введите число в поле рядом с ползуноком. Чем больше число, тем больше размер свечения.
Прозрачность. Чтобы указать, какая часть свечения видна, переместили ползунок Прозрачность или введите число в поле рядом с ползуноком. Вы можете изменять процент прозрачности от 0 (непрозрачная, по умолчанию) до 100 % (полностью прозрачный).
Soft Edges
Заранее заранее . Чтобы применить один из наиболее распространенных сжатий, нажмите кнопку Заранее ивыберите нужный.
Размер. Чтобы задать размер слажмого края, переместили ползунок Размер или введите число в поле рядом с ползуноком. Чем больше число, тем мягче края.
Совет: Чтобы удалить эффект свечения или сжатия, нажмите кнопку Заранее ивыберите нет свечения или Без степенных краев.
При изменении параметров в этой области или диалоговом окне изменения немедленно применяются к рисунку, фигуре, тексту или другим объект, что делает просмотр эффектов изменений рисунка без закрытия области или диалогового окна. Однако поскольку изменения мгновенно применяются, нажать кнопку Отмена в этой области или диалоговом окне невозможно. Чтобы удалить изменения, нажимайте кнопку Отменить на панели быстрого доступа для каждого из них.
Вы можете одновременно отменить несколько изменений, внесенных в одно диалоговое окно, если между ними не были внесены изменения в другое диалоговое окно.
Вы можете переместить области или диалоговое окно, чтобы одновременно видеть рисунок, фигуру, текст или другой объект, а также окно или диалоговое окно.
Данные на листах можно сделать более удобными для восприятия, изменив цвет шрифта в ячейках или диапазонах, цвет ярлычков листов или формул.
Сведения об изменении цвета фона ячеек, применении узоров или заливки см. в справке по добавлению или изменению цвета фона ячеек.
Изменение цвета текста в ячейке или диапазоне
Выделите ячейку или диапазон ячеек с данными, которые вы хотите отформатировать. Вы также можете выбрать часть текста в ячейке.
На вкладке Главная щелкните стрелку рядом с кнопкой Цвет шрифта .
Выберите цвет в группе Цвета темы или Стандартные цвета.
Совет: Чтобы применить последний выбранный цвет текста, на вкладке Главная нажмите кнопку Цвет текста.
Применение дополнительного цвета
Если вам нужен определенный цвет текста, вот как можно его получить:
На вкладке Главная щелкните стрелку рядом с кнопкой Цвет текста и выберите команду Другие цвета.
На вкладке Спектр в поле Цвета выберите нужный цвет.
Если вы знаете числовые значения составляющих нужного цвета, в поле Цветовая модель выберите модель RGB (Red, Green, Blue — красный, зеленый, синий) или HSL (Hue, Sat, Lum — тон, насыщенность, яркость), а затем введите числа, в точности соответствующие искомому цвету.
Форматирование цвета ярлычка листа
Щелкните правой кнопкой мыши ярлычок листа, цвет которого вы хотите изменить.
Щелкните Цвет ярлычка, а затем выберите нужный цвет.
Изменится цвет ярлычка, но не цвет шрифта. При выборе темного цвета ярлычка цвет шрифта меняется на белый, а при выборе светлого цвета — на черный.
Читайте также: