Закрасить ячейку по условию или формуле. Функция ячейка в excel и примеры ее использования Как работает функция ячейка в excel
Работа с VB проектом (12)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)
Часто из всем известной 1С отчеты выгружаются в Excel. Что не удивительно, ведь многие используют 1С как базу ведения всевозможных данных, а анализ предпочитают производить в Excel. И это удобно, это работает. Но часто при получении файла из 1С форматы ячеек изменены так, что отображается в ячейках одно значение, а на деле там значение совершенно иное:
Чтобы не возникло недопонимания, что это такое на картинках выше
. Например, если в ячейку записать число 1077 , то оно и отобразится так же. Однако его визуальное отображение в ячейках можно изменить: выделяем ячейку -правая кнопка мыши -Формат ячеек (Format Cells)
-вкладка Число (Number)
. Далее в списке слева выбрать Дополнительный (Special)
и установить Почтовый индекс
. Тогда в ячейке визуально будет отображаться 001077 , в то время как реально в ячейке будет оставаться все то же число 1077 . Тоже и с датами. Реально в ячейке число, а визуально дата в одном из форматов из категории Дата. Подробнее про то, почему так происходит можно прочесть в статье: Как Excel воспринимает данные?
И как это всегда бывает - порой просто необходимо работать не с тем значением, которое реально в ячейке, а именно с теми, которые отображаются в ячейках. Яркий пример такой необходимости - это сцепление данных двух ячеек, в одной из которых записана дата. Например, в A1 записана дата " 06.02.2016 ", а в B1 текст вроде " Отчет по магазину за " и необходимо сцепить текст из B1 с датой из A1 . Если применить просто функцию СЦЕПИТЬ (CONCATENATE)
или по простому = B1 & A1 , то результатом будет такой текст: Отчет по магазину за 42406 .
Если формат лишь один - можно стандартно попробовать побороть при помощи функции ТЕКСТ(TEXT). Например, в ячейках столбца А записаны даты в формате 31 января 2016г. Тогда формулу можно записать так:
=ТЕКСТ(A2 ;"[$-F800]ДДДД, ММММ ДД, ГГГГ")
=TEXT(A2 ,"[$-F800]dddd, MMMM yy, yyyy")
На примере той же СЦЕПИТЬ (CONCATENATE)
:
=СЦЕПИТЬ(B1 ;ТЕКСТ(A1 ;"[$-F800]ДДДД, ММММ ДД, ГГГГ"))
=CONCATENATE(B1 ,TEXT(A1 ,"[$-F800]dddd, MMMM yy, yyyy"))
Сами вид формата для использования в функции ТЕКСТ
можно подсмотреть непосредственно в форматах ячеек: правая кнопка мыши на ячейке -Формат ячеек (Format Cells)
-вкладка Число (Number)
-(все форматы). Там в поле Тип будет как раз приведен применяемый код формата. Можно его просто скопировать оттуда и вставить в функцию ТЕКСТ
.
Но если форматы в ячейках различаются и записаны в разнобой...Стандартно этого никак не сделать, кроме как каждую ячейку руками перебивать. Но если прибегнуть к помощи Visual Basic for Applications(VBA), то можно написать простую функцию пользователя(Что такое функция пользователя(UDF)) и применить её:
Function VisualVal_Text(rc As Range) VisualVal_Text = rc.Text End Function |
Function VisualVal_Text(rc As Range) VisualVal_Text = rc.Text End Function
Для применения надо внимательно прочитать про создание функций пользователя . После этого в ячейку останется записать:
=VisualVal_Text(A1)
и раскопировать ячейку на весь столбец. После этого можно заменить результат функции значениями(Как удалить в ячейке формулу, оставив значения) и все готово. Но и в этой функции есть недостаток. Если в ячейке отображается значение, которое не помещается в границы ячейки, то оно может быть обрезано или вместо значения будут решетки. Например, если дата в указанном формате не помещается в ячейку - вместо значений будут решетки #######:
и функция VisualVal_Text вернет так же решетки. Это тоже решаемо. Можно либо перед применением расширить столбцы с исходными данными так, чтобы значение отображалось правильно и полностью, либо применить чуть другую функцию пользователя:
Function VisualVal(rc As Range) VisualVal = Application.Text(rc.Value, rc.NumberFormat) End Function |
Function VisualVal(rc As Range) VisualVal = Application.Text(rc.Value, rc.NumberFormat) End Function
Используется и записывается в ячейку так же, как и предыдущая:
=VisualVal(A1)
Эта функция без всяких танцев с бубном вернет отображаемое форматом ячейки значение.
И вариант применения функции вместе с функцией СЦЕПИТЬ:
=СЦЕПИТЬ(B1 ;VisualVal_Text(A1))
=CONCATENATE(B1 ,VisualVal_Text(A1))
=СЦЕПИТЬ(B1 ;VisualVal(A1))
=CONCATENATE(B1 ,VisualVal(A1))
Как видно не надо задумываться о том какой применить формат - будет записано так же, как оно отображается в ячейке.
Все варианты решений можно посмотреть в примере:
(48,5 KiB, 562 скачиваний)
Статья помогла? Поделись ссылкой с друзьями! Видеоуроки{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}
При работе с таблицами первоочередное значение имеют выводимые в ней значения. Но немаловажной составляющей является также и её оформление. Некоторые пользователи считают это второстепенным фактором и не обращают на него особого внимания. А зря, ведь красиво оформленная таблица является важным условием для лучшего её восприятия и понимания пользователями. Особенно большую роль в этом играет визуализация данных. Например, с помощью инструментов визуализации можно окрасить ячейки таблицы в зависимости от их содержимого. Давайте узнаем, как это можно сделать в программе Excel.
Конечно, всегда приятно иметь хорошо оформленную таблицу, в которой ячейки в зависимости от содержимого, окрашиваются в разные цвета. Но особенно актуальна данная возможность для больших таблиц, содержащих значительный массив данных. В этом случае заливка цветом ячеек значительно облегчит пользователям ориентирование в этом огромном количестве информации, так как она, можно сказать, будет уже структурированной.
Элементы листа можно попытаться раскрасить вручную, но опять же, если таблица большая, то это займет значительное количество времени. К тому же, в таком массиве данных человеческий фактор может сыграть свою роль и будут допущены ошибки. Не говоря уже о том, что таблица может быть динамической и данные в ней периодически изменяются, причем массово. В этом случае вручную менять цвет вообще становится нереально.
Но выход существует. Для ячеек, которые содержат динамические (изменяющиеся) значения применяется условное форматирование, а для статистических данных можно использовать инструмент «Найти и заменить» .
Способ 1: условное форматированиеС помощью условного форматирования можно задать определенные границы значений, при которых ячейки будут окрашиваться в тот или иной цвет. Окрашивание будет проводиться автоматически. В случае, если значение ячейки, вследствие изменения выйдет за пределы границы, то автоматически произойдет перекрашивание данного элемента листа.
Посмотрим, как этот способ работает на конкретном примере. Имеем таблицу доходов предприятия, в которой данные разбиты помесячно. Нам нужно выделить разными цветами те элементы, в которых величина доходов менее 400000 рублей, от 400000 до 500000 рублей и превышает 500000 рублей.
Кроме того, можно использовать условное форматирование несколько по-другому для окраски элементов листа цветом.
Способ 2: использование инструмента «Найти и выделить»
Если в таблице находятся статические данные, которые не планируется со временем изменять, то можно воспользоваться инструментом для изменения цвета ячеек по их содержимому под названием «Найти и выделить» . Указанный инструмент позволит отыскать заданные значения и изменить цвет в этих ячейках на нужный пользователю. Но следует учесть, что при изменении содержимого в элементах листа, цвет автоматически изменяться не будет, а останется прежним. Для того, чтобы сменить цвет на актуальный, придется повторять процедуру заново. Поэтому данный способ не является оптимальным для таблиц с динамическим содержимым.
Посмотрим, как это работает на конкретном примере, для которого возьмем все ту же таблицу дохода предприятия.
Но существует возможность поступить несколько по-другому, что нам даст тот же результат. Можно в строке поиска задать следующий шаблон «3?????» . Знак вопроса означает любой символ. Таким образом, программа будет искать все шестизначные числа, которые начинаются с цифры «3» . То есть, в выдачу поиска попадут значения в диапазоне 300000 – 400000 , что нам и требуется. Если бы в таблице были числа меньше 300000 или меньше 200000 , то для каждого диапазона в сотню тысяч поиск пришлось бы производить отдельно.
Вводим выражение «3?????» в поле «Найти» и жмем на кнопку «Найти все ».
Как видим, существует два способа окрасить ячейки в зависимости от числовых значений, которые в них находятся: с помощью условного форматирования и с использованием инструмента «Найти и заменить» . Первый способ более прогрессивный, так как позволяет более четко задать условия, по которым будут выделяться элементы листа. К тому же, при условном форматировании цвет элемента автоматически меняется, в случае изменения содержимого в ней, чего второй способ делать не может. Впрочем, заливку ячеек в зависимости от значения путем применения инструмента «Найти и заменить» тоже вполне можно использовать, но только в статических таблицах.
Excel для Office 365 Excel для Office 365 для Mac Excel Online Excel 2019 Excel 2016 Excel 2019 для Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 для Mac Excel для Mac 2011 Excel для iPad Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Excel Starter 2010 Меньше
В этой статье описаны синтаксис формулы и использование функции ЯЧЕЙКа в Microsoft Excel. Ссылки на дополнительные сведения о форматировании данных в ячейках и применении стилей ячеек в разделе " связанные темы ".
ОписаниеЯЧЕЙКА Функция возвращает сведения о форматировании, расположении или содержимом ячейки. Например, если перед выполнением вычислений с ячейкой необходимо удостовериться в том, что она содержит числовое значение, а не текст, можно использовать следующую формулу:
= ЕСЛИ( ЯЧЕЙКА("тип", A1) = "v"; A1 * 2; 0)
Эта формула вычисляет произведение A1*2, только если в ячейке A1 содержится числовое значение, и возвращает значение 0, если в ячейке A1 содержится текст или она пустая.
СинтаксисАргументы функции ЯЧЕЙКА описаны ниже.
Тип_сведений - обязательный аргумент. Текстовое значение, задающее тип сведений о ячейке при возвращении. В приведенном ниже списке указаны возможные значения аргумента "тип_сведений" и соответствующие результаты.
"столбец" |
|
1, если форматированием ячейки предусмотрено изменение цвета для отрицательных значений; во всех остальных случаях - 0 (ноль). Примечание: |
|
"содержимое" |
|
"имяфайла" |
Имя файла (включая полный путь), содержащего ссылку, в виде текстовой строки. Если лист, содержащий ссылку, еще не был сохранен, возвращается пустая строка (""). Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter. |
Текстовое значение, соответствующее числовому формату ячейки. Значения для различных форматов показаны ниже в таблице. Если ячейка изменяет цвет при выводе отрицательных значений, в конце текстового значения добавляется "-". Если положительные или все числа отображаются в круглых скобках, в конце текстового значения добавляется "()". Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter. |
|
1, если форматированием ячейки предусмотрено отображение положительных или всех чисел в круглых скобках; во всех остальных случаях - 0. Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter. |
|
"префикс" |
Текстовое значение, соответствующее префиксу метки ячейки. Одиночная кавычка (") соответствует тексту, выровненному влево, двойная кавычка (") - тексту, выровненному вправо, знак крышки (^) - тексту, выровненному по центру, обратная косая черта (\) - тексту, распределенному по всей ширине ячейки, а пустой текст ("") - любому другому содержимому ячейки. Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter. |
0, если ячейка разблокирована, и 1, если ячейка заблокирована. Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter. |
|
Текстовое значение, соответствующее типу данных в ячейке. Значение "b" соответствует пустой ячейке, "l" - текстовой константе в ячейке, "v" - любому другому содержимому. |
|
Ширина столбца ячейки, округленная до целого числа. Единица измерения равна ширине одного знака для шрифта стандартного размера. Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter. |
Ссылка - необязательный аргумент. Ячейка, сведения о которой требуется получить. Если этот аргумент опущен, сведения, указанные в аргументе "тип_сведений", возвращаются для последней измененной ячейки. Если аргумент ссылки указывает на диапазон ячеек, функция ЯЧЕЙКА возвращает сведения только для левой верхней ячейки диапазона.
В приведенном ниже списке описаны текстовые значения, возвращаемые функцией ЯЧЕЙКА, если в качестве аргумента "тип_сведений" указано значение "формат", а аргумент ссылки указывает на ячейку, отформатированную с использованием встроенного числового формата.
$# ##0_);($# ##0) |
|
$# ##0_);[Красный]($# ##0) |
|
$# ##0,00_);($# ##0,00) |
|
$# ##0,00_);[Красный]($# ##0,00) |
|
# ?/? или # ??/?? |
|
д.М.гг или дд.ММ.гг Ч:мм или дд.ММ.гг |
|
Д МММ ГГ или ДД МММ ГГ |
|
д.м, или дд.ммм, или Д МММ |
|
ммм.гг, ммм.гггг, МММ ГГ или МММ ГГГГ |
|
ч:мм:сс AM/PM |
|
Примечание: Если аргумент "тип_сведений" функции ЯЧЕЙКА имеет значение "формат", а формат ячейки был изменен, для обновления значения функции ЯЧЕЙКА необходимо пересчитать лист.
ПримерСкопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем - клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Для выполнения этой задачи будем использовать возможности условного форматирования.
Возьмем таблицу, содержащую список заказов, сроки их исполнения, текущий статус и стоимость. Попробуем сделать так, чтобы ее ячейки раскрашивались сами, в зависимости от их содержимого.
ВКЛЮЧИТЕ СУБТИТРЫ!
ВКЛЮЧИТЕ СУБТИТРЫ!
Выделим ячейки с ценами заказов и, нажав на стрелочку рядом с кнопкой «Условное форматирование», выберем «Создать правило».
Выберем четвертый пункт, позволяющий сравнивать текущие значения со средним. Нас интересуют значения выше среднего. Нажав кнопку «Формат», зададим цвет ячеек.
Подтверждаем наш выбор, и ячейки с ценой выше средней окрасились в голубой цвет, привлекая наше внимание к дорогим заказам.
Выделим ячейки со статусами заказов и создадим новое правило. На этот раз используем второй вариант, позволяющий проверять содержимое ячейки. Выберем «Текст», «содержит» и введем слово «Выполнен». Зададим зеленый цвет, подтверждаем, и выполненные работы у нас позеленели.
Ну и сделаем еще одно правило, окрашивающее просроченные заказы в красный цвет. Выделяем даты выполнения заказов. При создании правила снова выбираем второй пункт, но на этот раз задаем «Значение ячейки», «меньше», а в следующем поле вводим функцию, возвращающую сегодняшнюю дату.
«ОК», и мы получили весело разукрашенную таблицу, позволяющую наглядно отслеживать ход выполнения заказов.
Обратили внимание, что статусы задаются выбором из выпадающего списка значений? Как делать такие списки, мы рассказывали в инструкции .
ВКЛЮЧИТЕ СУБТИТРЫ!
«Условное форматирование» в меню «Формат». Тут понадобится немного больше ручной работы. Вот так будут выглядеть настройки для нашей первой задачи – закрасить ячейки со значениями больше средних.
Придется вручную ввести функцию «=СРЗНАЧ()», поставить курсор между скобками, нажать на кнопочку рядом и мышкой указать нужный диапазон.
Но принцип действий тот же самый.
Покоряйте Excel и до новых встреч!
Поймал себя на мысли, что не до конца освоил функционал функции =ЯЧЕЙКА() (CELL в английской версии). Исправляюсь. Далее расскажу подробнее про функционал функции и как можно его применить на практике, например, как найти номер строки или столбца, адрес ячейки, или даже адрес и имя файла. Функция ЯЧЕЙКА в Excel, разбираем подробнее.
Кто знает VBA, удивиться, функционал легко заменяется программно. Но мы здесь занимаемся оптимизацией без программирования, для пользователей.
Если в двух словах, то функция =ЯЧЕЙКА() работает со свойствами ячейки и имеет два аргумента
- тип_сведений — обязательный реквизит, текст, заполняется аргументами на выбор в таблице ниже
- ссылка — необязательный аргумент, адрес ссылки на ячейку, для некоторых типов сведений, например таких как «имяфайла» его не нужно указывать. Если аргумент это диапазон, т.к. стандартно выводится результат верхней левой ячейки.
Таблица аргумента Тип_сведений представлена на рисунке в начале страницы, таблице ниже, а так же в примере (список представлен в последовательности формулы)
Тип_сведений | Описание | Значение ячейки | Возвращается |
«адрес» | Адрес ячейки в формате А1 (если диапазон то верхней левой ячейки) | $D$3 | |
«защита» | 0, если ячейка разблокирована в Формате ячейки, и 1, если ячейка заблокирована. | 1 | |
«имяфайла» | Полный путь к файлу, включая имя и название листа. Для несохраненгого листа возвращается пусто «» C:\Users\….\Пример.xlsx |
C:\Users\User\Desktop\[Функция Ячейка.xlsx]1 | |
«префикс» | Текст. Код обозначающий выравнивание ячейки, расшифровка ниже | текст | ^ |
«скобки» | 1, если формат отрицательных чисел в скобках; иначе — 0. | 0 | |
«содержимое» | Не формула, значение ячейки | 5 | 5 |
«столбец» | Номер столбца аргумента «ссылка». | 4 | |
«строка» | Номер строки аргумента «ссылка». | 10 | |
«тип» | Текст. Зависит от типа данных, значение «b» — пустая ячейка, «l» - константа, «v» - любому другому содержимому. | 1 | v |
«формат» | Текст. Буква соответствует определенному формату (). Значения для различных форматов показаны ниже в таблице. Если меняется цвет при отрицательных значениях то добавляется «-«. | C2 | |
«цвет» | 1, если ячейка изменяет цвет для отрицательных значений; иначе = 0 (ноль) | 0 | |
«ширина» | Целое число. Ширина столбца указанной ссылки | 8 |
Расшифровка типа сведений «префикс »
Как найти номер строки/столбца и адрес файла думаю вы поняли из таблицы.
Разберем более интересные примеры:
1. Необходимо отметить в столбце все ячейки начиная с 5 и записать в них 1