Форматирование заголовка умной таблицы по условию

@donch5555 · Регистрация: 26.05.2013

Author24 — интернет-сервис помощи студентам

Здравствуйте. Подскажите, пожалуйста, как реализовать задумку, если это вообще возможно без макросов?
Имеется умная таблица с любым кол-вом строк, в ней есть столбцы "Кол-во" и "Остаток".
Нужно чтобы если хоть в одной строке значение кол-ва < остатка, то заголовок таблицы выделяется форматированием ячеек (например, красным текстом).

@Vlad999 · 28.06.2021, 08:07

смотрите в сторону УФ. При наличии файла-примера ответ может быть более детальным.

@donch5555 · 28.06.2021, 08:33 **[ТС]**

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

@Vlad999 · 28.06.2021, 09:35

проверяйте, для каждой таблицы придется делать свое правило.

@donch5555 · 28.06.2021, 09:56 **[ТС]**

Vlad999, вроде бы то, что надо. Но только в формуле уф ипользуются обычные диапазоны E$2:$E$5 и $F$2:$F$5. Нельзя ли их заменить на диапазоны данных этих столбцов? Название_таблицы[[#Данные];[Кол-во]] и Название_таблицы[[#Данные];[Остаток]] соответственно.
Боюсь, что при изменении количества строк в таблице, в форматировании с обычными диапазонами начнется балаган.

@Vlad999 · 28.06.2021, 12:32

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

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

Добавлено через 5 минут
еще, при использовании Название_таблицы[[#Данные];[Кол-во]] придется проверять на пусто еще, т.к. диапазон захватывает последнюю пустую строчку.

@donch5555 · 28.06.2021, 12:47 **[ТС]**

Vlad999, у меня в 2016 не даёт заменить обычный диапазон на "умный", пишет про ошибку в формуле.
А как пустые строки помешают сравнению? Если обычным диапазоном их тоже захватывать, всё хорошо работает.
Если не сложно, можете показать как удалось вставить диапазон умной таблицы в правило форматирования?

Добавлено через 7 минут
Или я неправильно понял, и умный диапазон в правило УФ не получается вставить? И про вставку строк речь шла при использовании обычного диапазона?

@Ruella · 28.06.2021, 13:01

donch5555, посмотрите это (УФ верхней таблицы). И да, пустые диапазоны без проверки мешают - если нужно, проверку добавьте в формулу сами:

@donch5555 · 29.06.2021, 05:14 **[ТС]**

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

@Vlad999 · 29.06.2021, 09:24

Сообщение от donch5555

не учитывать пустые ячейки в диапазоне, безрезультатно

подкорректировал формулу от Ruella,

Код

=СУММПРОИЗВ((ДВССЫЛ("ФИЛЬТРЫ_2[Кол-во]")<=ДВССЫЛ("ФИЛЬТРЫ_2[Остаток]"))*(ДВССЫЛ("ФИЛЬТРЫ_2[Кол-во]")<>""))

@donch5555 · 29.06.2021, 11:22 **[ТС]**

Vlad999, вот теперь работает отлично, большое спасибо.
И на последок еще вопрос, не совсем по разделу, но чтобы не создавать отдельную тему, спрошу тут. Можно ли как-нибудь (скорее всего, через макросы) автоматизировать подхват имени каждой таблицы и ввод этих значений в первую ячейку заголовка каждой таблицы на листе? Т.е. у таблицы с именем "Табл.1" в ячейке заголовка первого столбца (например, А1) должно быть текстовое значение "Табл.1".

Просто думаю, как упростить добавление УФ к каждой таблице, чтобы в правилах не менять каждый раз имя таблицы, а обойтись копированием.
Решил немного переиграть. В случае срабатывания УФ красным цветом выделяется не весь заголовок таблицы, а только заголовок столбца "Кол-во". И, если правило немного поменять:

Код

=СУММПРОИЗВ((ДВССЫЛ(A1&"[[Кол-во]]")<=ДВССЫЛ(A1&"[[Остаток]]"))*(ДВССЫЛ(A1&"[[Кол-во]]")<>""))

, и в каждой таблице в заголовке указать её имя, то копируя ячейку заголовка "Кол-во", будет так же копироваться и УФ, с автоизменением ссылки в правиле УФ (вместо A1). Если непонятно объяснил, прикрепил файл, ячейку E1 скопировал и вставил в E8.

@donch5555 · 29.06.2021, 11:38 **[ТС]**

Нашел такой код (ссылка)

Visual Basic

Function getObjName(rng As Range) As String
‘Updated by Extendoffice 20180420
    Dim xTable As ListObject
    Dim xPivotTable As PivotTable
    Dim xTableName As String
    Dim xPtName As String
    On Error Resume Next
    xTableName = ""
    xPtName = ""
    Set xTable = rng.Cells(1).ListObject
    xTableName = xTable.Name
    If xTable Is Nothing Then
        Set xPivotTable = rng.Cells(1).PivotTable
        xPtName = xPivotTable.Name
    End If
    getObjName = xTableName & xPtName
End Function

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

@Vlad999 3911 / 2311 / 782 Регистрация: 02.11.2012 Сообщений: 6,147
	29.06.2021, 09:24	10
	Сообщение от donch5555 не учитывать пустые ячейки в диапазоне, безрезультатно подкорректировал формулу от Ruella, Код =СУММПРОИЗВ((ДВССЫЛ("ФИЛЬТРЫ_2[Кол-во]")<=ДВССЫЛ("ФИЛЬТРЫ_2[Остаток]"))*(ДВССЫЛ("ФИЛЬТРЫ_2[Кол-во]")<>"")) 1

@donch5555 0 / 0 / 0 Регистрация: 26.05.2013 Сообщений: 20
		1
	Форматирование заголовка умной таблицы по условию 28.06.2021, 07:08. Показов 1485. Ответов 11 Метки нет (Все метки) Здравствуйте. Подскажите, пожалуйста, как реализовать задумку, если это вообще возможно без макросов? Имеется умная таблица с любым кол-вом строк, в ней есть столбцы "Кол-во" и "Остаток". Нужно чтобы если хоть в одной строке значение кол-ва < остатка, то заголовок таблицы выделяется форматированием ячеек (например, красным текстом). 0

@Vlad999 3911 / 2311 / 782 Регистрация: 02.11.2012 Сообщений: 6,147
	28.06.2021, 08:07	2
	смотрите в сторону УФ. При наличии файла-примера ответ может быть более детальным. 1

@donch5555 0 / 0 / 0 Регистрация: 26.05.2013 Сообщений: 20
	28.06.2021, 09:56 [ТС]	5
	Vlad999, вроде бы то, что надо. Но только в формуле уф ипользуются обычные диапазоны E$2:$E$5 и $F$2:$F$5. Нельзя ли их заменить на диапазоны данных этих столбцов? Название_таблицы[[#Данные];[Кол-во]] и Название_таблицы[[#Данные];[Остаток]] соответственно. Боюсь, что при изменении количества строк в таблице, в форматировании с обычными диапазонами начнется балаган. 0

@Vlad999 3911 / 2311 / 782 Регистрация: 02.11.2012 Сообщений: 6,147
	28.06.2021, 12:32	6
	в 2007 с диапазонами умной таблицы не захотело работать, по этому заменил на обычные. На счет удаления и добавления нужно проверять. Добавлено через 1 минуту на одной таблице проверял, если добавлять или удалять в середине то все менялось норм. Добавлено через 5 минут еще, при использовании Название_таблицы[[#Данные];[Кол-во]] придется проверять на пусто еще, т.к. диапазон захватывает последнюю пустую строчку. 1

@donch5555 0 / 0 / 0 Регистрация: 26.05.2013 Сообщений: 20
	28.06.2021, 12:47 [ТС]	7
	Vlad999, у меня в 2016 не даёт заменить обычный диапазон на "умный", пишет про ошибку в формуле. А как пустые строки помешают сравнению? Если обычным диапазоном их тоже захватывать, всё хорошо работает. Если не сложно, можете показать как удалось вставить диапазон умной таблицы в правило форматирования? Добавлено через 7 минут Или я неправильно понял, и умный диапазон в правило УФ не получается вставить? И про вставку строк речь шла при использовании обычного диапазона? 0

@donch5555 0 / 0 / 0 Регистрация: 26.05.2013 Сообщений: 20
	29.06.2021, 05:14 [ТС]	9
	Ruella, спасибо за формулу. Пытался сам найти как исключить/не учитывать пустые ячейки в диапазоне, безрезультатно. Не подскажете, как это лучше сделать? Во всех умных таблицах нужны пустые строки (для добавления в будущем новых данных). 0

	29.06.2021, 11:38