Подсветка строки в Excel цветом
Чтобы создать свой широкий курсов на целую строку в Excel, необходимо создать макрос и использовать его вместе с условным форматированием. Для примера создадим таблицу с широкими строками и большим количеством столбцов. Нам нужно выделить строку цветом по условию Excel. Например, если активная ячейка находится в строке 2, тогда выделить цветом всю строку 2 (в пределах таблицы или на весь лист). Для примера создадим таблицу с широкими строками и большим количеством столбцов:
Подсветка текущей строки в Excel цветом:
- Откройте редактор VBA для этого выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Visual Basic». Или нажмите комбинацию горячих клавиш ALT+F11.
- В правом окне к активному проекту добавьте новый модуль. Для того в редакторе VBA выберите опцию: «Insert»-«Module»
- В окне модуля вставьте этот VBA-код макроса:
- Не закрывая окно VBA-редактора перейдите на рабочий лист с исходной таблицей. Выделите целую вторую строку таблицы A2:H2 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматированных ячеек:».
- В полю ввода введите формулу:
- Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите зеленый цвет. На всех окнах жмем ОК.
- Скопируйте вторую строку таблицы.
- Выделите остальные строки и выберите инструмент: «ГЛАВНАЯ»-«Вставить»-«Специальная вставка»-«Форматы» (или нажмите комбинацию клавиш CTRL+ALT+V, потом отметить форматы и нажать ОК). Или воспользуйтесь инструментом: «ГЛАВНАЯ»-«Буфер обмена»-«Формат по образцу».
- Вернитесь к редактору VBA и в окне «Project» дважды щелкните по «Лист1». В появившемся окне вставьте следующий VBA-код макроса:
- Сохраните и закройте редактор VBA.
В результате происходит выделение строки условным форматированием только там, где находится активная ячейка. Эффект широкого курсора таблицы для комфортного просмотра данных с большим объемом информации. Сделав активной любую ячейку таблицы и сразу же выделяется цветом строка в которой находится данная ячейка.
Полезный вопрос! А как же выделить активный столбец цветом? Аналогичным методом реализовывается с помощью макроса не только подсветка цветом активной строки, но и столбца. Для этого:
- В модуль где находится код подсветки строки VBA-макроса, добавим код новой функции АктивныйСтолбец:
- Добавим новое правило в условном форматировании для нашей таблицы в диапазоне A1:A7. Теперь в новом правиле пишем функцию АктивныйСтолбец(), а вместо функции СТРОКА – СТОЛБЕЦ. И указываем синий цвет форматирования заливки ячейки.
- Проверим все созданные правила для таблицы. Перейдите в ячейку A2 и ввыберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Управление правилами». Диспетчер правил условного форматирования должен выглядеть так:
- Скопируйте формат столбца A1:A7 в остальные столбцы таблицы диапазона B1:H7. Например, с помощью инструмента: «ГЛАВНАЯ»-«Буфер обмена»-«Формат по образцу».
- Результат превышает ожидания. Идеальный курсор для просмотра огромных таблиц с большим количеством данных на множестве строк и столбцов:
Принцип действия создания пользовательского курсора для таблицы
Применяемая в условном форматировании созданная нами функция VBA =АктивнаяСтрока() позволяет нам получить номер текущей строки в которой стала активной любая из ее ячеек. Результат вычисления пользовательской функции =АктивнаяСтрока() сравнивается с результатом функции =СТРОКА(), которая также возвращает номер текущей строки в данном моменте вычисления условного форматирования.
Так как обе функции выполняются при активной ячейке в одной и той же строке их значения равны между собой и возвращают общее логическое значение ИСТИНА. В результате чего происходит выделение строки цветом по условию. Если значение ИСТИНА, тогда присваивается формат цвета для целой активной строки таблицы.
Как посчитать количество пустых и непустых ячеек?
Посмотрим, как можно применить функцию СЧЕТЕСЛИ в Excel для подсчета количества пустых или непустых ячеек в указанном диапазоне.
Непустые.
В некоторых руководствах по работе с СЧЕТЕСЛИ вы можете встретить предложения для подсчета непустых ячеек, подобные этому:
СЧЕТЕСЛИ(диапазон;”*”)
Но дело в том, что приведенное выше выражение подсчитывает только клетки, содержащие любые текстовые значения. А это означает, что те из них, что включают даты и числа, будут обрабатываться как пустые (игнорироваться) и не войдут в общий итог!
Если вам нужно универсальное решение для подсчета всех непустых ячеек в указанном диапазоне, то введите:
СЧЕТЕСЛИ(диапазон;”<>” & “”)
Это корректно работает со всеми типами значений – текстом, датами и числами – как вы можете видеть на рисунке ниже.
Также непустые ячейки в диапазоне можно подсчитать:
=СЧЁТЗ(E2:E22).
Пустые.
Если вы хотите сосчитать пустые позиции в определенном диапазоне, вы должны придерживаться того же подхода – используйте в условиях символ подстановки для текстовых значений и параметр “” для подсчета всех пустых ячеек.
Считаем клетки, не содержащие текст:
Поскольку звездочка (*) соответствует любой последовательности текстовых символов, в расчет принимаются клетки, не равные *, т.е. не содержащие текста в указанном диапазоне.
Для подсчета пустых клеток (все типы значений):
Конечно, для таких случаев есть и специальная функция
Но не все знают о ее существовании. Но вы теперь в курсе …
Базовые формулы для получения уникальных значений.
Чтобы избежать путаницы, сначала давайте договоримся о том, что мы называем уникальными значениями в Excel.
Уникальные значения – это значения, которые присутствуют в списке только один раз. Например:
Чтобы получить список уникальных значений в Excel, используйте одну из следующих формул.
Формула уникальных значений массива (заполняется нажатием Ctrl + Shift + Enter):
Можно воспользоваться и обычной формулой (вводится нажатием Enter):
В приведенных выше формулах используются следующие ссылки:
- A2: A10 – исходных перечень данных.
- B1 – верхняя ячейка уникального списка минус одна строка. В этом примере мы начинаем создавать список уникальных в B2, и поэтому мы записываем B1 в формулу (B2 – 1 строка = B1). Если ваш список начинается, скажем, с ячейки C3, измените $B$1:B1 на $C$2:C2.
В этом примере мы извлекаем уникальные имена из столбца A (точнее из диапазона A2: A10), а следующий скриншот демонстрирует формулу в действии:
Вот наш порядок действий:
- Измените любую из формул в соответствии с вашим диапазоном данных.
- Введите ее в первую ячейку, с которой начнётся формирование списка (в данном примере B2).
- Если вы используете формулу массива, нажмите Ctrl + Shift + Enter. Если вы выбрали обычную, нажмите просто клавишу Enter.
- Скопируйте вниз настолько, насколько это необходимо, перетащив мышкой маркер заполнения. Поскольку обе формулы заключены в функцию ЕСЛИОШИБКА, вы можете скопировать вниз с запасом. Это не испортит ваши данные какими-либо ошибками, независимо от того, сколько уникальных значений было извлечено.
Как найти уникальные значения при помощи формул.
Самый простой способ сделать это – использовать функции ЕСЛИ и СЧЁТЕСЛИ. В зависимости от типа данных, которые вы хотите найти, может быть несколько вариантов формулы, как показано в следующих примерах.
Как найти уникальные значения в столбце.
Чтобы найти различные или уникальные значения в списке, используйте одну из следующих формул, где A2 — первая, а A10 — последняя ячейка с данными.
Чтобы найти уникальные значения в Excel:
Чтобы определить различные значения:
Во второй формуле есть только одно небольшое отличие во второй ссылке на ячейку, что, однако, имеет большое значение:
Совет. Если вы хотите найти уникальные значения между двумя столбцами , т.е. найти значения, которые присутствуют в одном столбце, но отсутствуют в другом, используйте формулу, описанную в статье Как сравнить 2 столбца на предмет различий.
Уникальные строки в таблице.
Аналогичным образом вы можете найти уникальные строки в таблице Excel на основе изучения записей не в одном, а в двух или более столбцах. В этом случае вам необходимо использовать СЧЁТЕСЛИМН вместо СЧЁТЕСЛИ для оценки значений (до 127 пар диапазон/критерий можно обработать в одной формуле).
Формула для получения уникальных строк:
Формула для поиска различных строк:
В нашем случае уникальная комбинация Имя+Фамилия встречается 2 раза. А всего в списке 6 человек, из которых трое дублируются.
Как найти уникальные записи с учетом регистра?
Если вы работаете с набором данных, где важен регистр букв, вам понадобится немного более сложная формула массива.
Поиск уникальных значений с учетом регистра :
Поиск различных значений с учетом регистра :
Поскольку обе они являются формулами массива, обязательно нажмите , чтобы правильно их записать.
Когда уникальные или различные значения найдены, вы можете легко отфильтровать, выбрать или скопировать их, как будет описано ниже.
Решение без формул массива
Для отбора уникальных значений можно обойтись без использования формул массива . Для этого создайте дополнительный служебный столбец для промежуточных вычислений (см. лист «Без CSE» в файле примера ).
СОВЕТ: Список уникальных значений можно создать разными способами, например, с использованием Расширенного фильтра (см. статью Отбор уникальных строк с помощью Расширенного фильтра ), Сводных таблиц или через меню Данные/ Работа с данными/ Удалить дубликаты . У каждого способа есть свои преимущества и недостатки. Преимущество использования формул состоит в том, чтобы при добавлении новых значений в исходный список, список уникальных значений автоматически обновлялся.
СОВЕТ2 : Для тех, кто создает список уникальных значений для того, чтобы в дальнейшем сформировать на его основе Выпадающий список , необходимо учитывать, что вышеуказанные формулы возвращают значение Пустой текст «» , который требует аккуратного обращения, особенно при подсчете значений (вместо обычной функции СЧЕТЗ() нужно использовать СЧЕТЕСЛИ() со специальными аргументами ). Например, см. статью Динамический выпадающий список в MS EXCE L.
Примечание : В статье Восстанавливаем последовательности из списка без повторов в MS EXCEL решена обратная задача: из списка уникальных значений, в котором для каждого значения задано количество повторов, создается список этих значений с повторами.
голоса
Рейтинг статьи
6 способов создать список уникальных значений в Excel
В этой статье я хочу рассказать о возможности создать список уникальных значений в таблицах Excel. Эта возможность очень часто используется при работе с таблицами, так как часто возникает потребность с большого массива данных выбрать уникальные данные, которые не повторяются. Это может быть нужно для разнообразных целей, и уже вам решать каким способом и как произвести отбор нужных вам уникальных значений.
Список уникальных значений возможно создать 6-ю способами:
Создать список уникальных значений с помощью специальной функции
Это очень простой способ для владельцев Excel выше 2007 версии как произвести отбор уникальных значений. Вам нужно на вкладке «Данные», в разделе «Работа с данными», использовать специальную команду «Удалить дубликаты».
В появившемся диалоговом окне «Удалить дубликаты», вы выделяете те столбики, где необходимо произвести отсев уникальных значений и нажимаете «Ок». В случае, когда в выделенном диапазоне размещается и заголовок таблицы, то поставьте галочку на пункте «Мои данные содержат заголовки», что бы вы случайно не удалили данные.
Внимание!Когда вы будете производить отсев уникальных значений в таблице, где столбиков больше 2 и они взаимосвязаны информацией, Excel предложит вам расширить диапазон выбора, с чем вы должны, согласится, иначе будет нарушена логическая связь с другими столбиками
Создать список уникальных значений с помощью расширенного фильтра
Это также не сложный способ произвести отбор уникальных значений в таблице. Использовать этот инструмент возможно на вкладке «Данные», потом выбрать «Фильтр», и наконец «Расширенный фильтр», этот путь подходит для Excel 2003, а вот владельцы более юных версий, от 2007 и выше стоит пройти по пути: «Данные» — «Сортировка и фильтр» — «Дополнительно». Огромный плюс этого способа в том, что вы можете создать новый список уникальных значений в другом месте.
«Расширенный фильтр»Скопировать результат в другое место»«Исходный диапазон»«Диапазон критериев»«Поместить результат в диапазон»«Только уникальные записи»«Ок»
«Фильтровать список на месте»
Внимание! Если программа запрещает вам переносить отфильтрованные данные на другой лист, вы просто запустите «Расширенный фильтр» на том листе, куда вам надо перенести отобранные уникальные значения
Создать список уникальных значений с помощью формул
Этот способ более сложен, нежели те, что мы рассматривали ранее, но его преимущество в том, что он более динамичен и работает на постоянной основе. В разных случаях вам будут нужны разные формулы, вот и рассмотрим несколько вариантов и примеров.
Пример 1. Вам нужно пронумеровать, уникальные, значение в списке значений, для этого нужно использовать функцию ЕСЛИ в формуле следующего вида:
=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;»«)
Суть формулы в том, что она проверяет сколько раз, текущее значение встречается в вашем диапазоне (начиная с начала), и если это значение равно 1, то есть это первое уникальное значение, формула ставит последовательно возвращающий номер по порядку.
=ЕСЛИ(МАКС(A1:A100)
Заливка ячеек в зависимости от значения в Microsoft Excel
При работе с таблицами первоочередное значение имеют выводимые в ней значения
Но немаловажной составляющей является также и её оформление. Некоторые пользователи считают это второстепенным фактором и не обращают на него особого внимания
А зря, ведь красиво оформленная таблица является важным условием для лучшего её восприятия и понимания пользователями. Особенно большую роль в этом играет визуализация данных. Например, с помощью инструментов визуализации можно окрасить ячейки таблицы в зависимости от их содержимого. Давайте узнаем, как это можно сделать в программе Excel.
Как можно использовать сводную таблицу.
Вот обычная задача, которую все пользователи Excel должны время от времени выполнять. У вас есть список данных (к примеру, названий товаров), и нужно узнать количество уникальных позиций в этом списке. Как это сделать? Проще, чем вы думаете
В версиях Excel выше 2013 есть специальная функция, которая позволяет автоматически пересчитывать различные значения в сводной таблице. На следующем рисунке показано, как выглядит этот счетчик:
Чтобы создать сводную таблицу со счетчиком для определенного столбца, выполните следующие действия.
- Выберите данные для включения в сводную таблицу, перейдите на вкладку «Вставка» и нажмите кнопку «Сводная таблица» .
- В диалоговом окне «Создание сводной таблицы» выберите, следует ли разместить сводную таблицу на новом или существующем листе, и обязательно установите флажок «Добавить эти данные в модель данных» .
- Когда откроется сводная таблица, расположите области строк, столбцов и значений так, как вам нужно. Если у вас нет большого опыта работы со сводными таблицами Excel, могут оказаться полезными следующие подробные рекомендации: Создание сводной таблицы в Excel.
- Переместите поле, количество уникальных элементов которого вы хотите вычислить ( поле « Товар» в этом примере), в область « Значения» , щелкните его и выберите «Параметры значения поля…» из раскрывающегося меню.
- Откроется диалоговое окно , прокрутите вниз до операции «Число разных элементов» , которая является самым последним пунктом в списке, выберите ее и нажмите OK .
Вы также можете дать собственное имя своему счетчику, если хотите.
Готово! Вновь созданная сводная таблица будет отображать количество различных товаров, как показано на самом первом скриншоте в этом разделе.
Вот как можно подсчитать различные и уникальные значения в столбце и целиком в таблице Excel.
Благодарю вас за чтение и надеюсь увидеть вас снова. Пожалуйста, не переключайтесь!
Как найти и выделить уникальные значения в столбце – В статье описаны наиболее эффективные способы поиска, фильтрации и выделения уникальных значений в Excel. Ранее мы рассмотрели различные способы подсчета уникальных значений в Excel. Но иногда вам может понадобиться только просмотреть уникальные…
Как получить список уникальных значений – В статье описано, как получить список уникальных значений в столбце с помощью формулы и как настроить эту формулу для различных наборов данных. Вы также узнаете, как быстро получить отдельный список с…
Как выделить цветом повторяющиеся значения в Excel? – В этом руководстве вы узнаете, как отображать дубликаты в Excel. Мы рассмотрим различные методы затенения дублирующих ячеек, целых строк или последовательных повторений с использованием условного форматирования. Ранее мы исследовали различные…
Как посчитать количество повторяющихся значений в Excel? – Зачем считать дубликаты? Мы можем получить ответ на множество интересных вопросов. К примеру, сколько клиентов сделало покупки, сколько менеджеров занималось продажей, сколько раз работали с определённым поставщиком и т.д. Если…
Как убрать повторяющиеся значения в Excel? – В этом руководстве объясняется, как удалять повторяющиеся значения в Excel. Вы изучите несколько различных методов поиска и удаления дубликатов, избавитесь от дублирующих строк, обнаружите точные повторы и частичные совпадения. Хотя…
Условное форматирование Excel для нулей, но не пробелов
По умолчанию условное форматирование Excel не различает 0 и пустую ячейку, что во многих ситуациях действительно сбивает с толку. Чтобы решить эту затруднительную ситуацию, есть два возможных решения:
- Создайте 2 правила: одно для пробелов, а другое для нулевых значений.
- Создайте 1 правило, которое проверяет оба условия в одной формуле.
Создайте отдельные правила для пробелов и нулей
- Сначала создайте правило для выделения нулевых значений. Для этого нажмите «Условное форматирование» > «Новое правило» > «Форматировать только те ячейки, которые содержат», а затем установите значение ячейки равным 0, как показано на снимке экрана ниже. Нажмите кнопку Формат и выберите нужный цвет.
Это условное форматирование применяется, если ячейка пуста или равна нулю:
- Создайте правило для пробелов без установленного формата. Затем откройте Диспетчер правил, переместите правило «Пробелы» в начало списка (если его там еще нет) и установите рядом с ним флажок «Остановить, если верно». Подробные инструкции см. .
В результате ваше условное форматирование будет включать нули, но игнорировать пробелы. Как только выполняется первое условие (ячейка пуста), второе условие (ячейка равна нулю) никогда не проверяется.
Создайте одно правило, чтобы проверить, является ли ячейка нулевой, а не пустой
Другой способ условно отформатировать 0, но не пробелы, — создать правило с формулой, которая проверяет оба условия:
=И(B3=0, B3«»)
=И(B3=0, ДЛСТР(B3)>0)
Где B3 — верхняя левая ячейка выбранного диапазона.
Результат точно такой же, как и с предыдущим методом — условное форматирование выделяет нули, но игнорирует пустые ячейки.
Вот как использовать условное форматирование для пустых ячеек. Я благодарю вас за чтение и с нетерпением жду встречи с вами на следующей неделе.