Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Правило, подсчитывающее количество истин - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 212»
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Правило, подсчитывающее количество истин (Условное Форматирование/Conditional Formattings)
Правило, подсчитывающее количество истин
FrameG2 Дата: Воскресенье, 28.02.2016, 17:31 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Здравствуйте!
Сразу скажу, что в Excel я слабовато разбираюсь.
Итак, есть число, из которого вычитаются другие числа из диапазона и проверяется, >=0 или нет. В зависимости от результата ячейки из диапазона закрашиваются определённым цветом. Выглядит это примерно так (прикрепил пример, там наглядней):

число-СУММ(диапазон чисел)>=0 (зелёный цвет)
число-СУММ(диапазон чисел)<0 (жёлтый цвет)

Но надо добавить ещё одно правило: если правило "число-СУММ(диапазон чисел)<0 (жёлтый цвет)" повторилось 5 раз (дало 5 истин), то ячейка закрашивается в красный. Т.е. в примере после 5-ти жёлтых ячеек должны идти красные. Как такое правило сделать?
К сообщению приложен файл: Test.xlsx(10Kb)
 
Ответить
СообщениеЗдравствуйте!
Сразу скажу, что в Excel я слабовато разбираюсь.
Итак, есть число, из которого вычитаются другие числа из диапазона и проверяется, >=0 или нет. В зависимости от результата ячейки из диапазона закрашиваются определённым цветом. Выглядит это примерно так (прикрепил пример, там наглядней):

число-СУММ(диапазон чисел)>=0 (зелёный цвет)
число-СУММ(диапазон чисел)<0 (жёлтый цвет)

Но надо добавить ещё одно правило: если правило "число-СУММ(диапазон чисел)<0 (жёлтый цвет)" повторилось 5 раз (дало 5 истин), то ячейка закрашивается в красный. Т.е. в примере после 5-ти жёлтых ячеек должны идти красные. Как такое правило сделать?

Автор - FrameG2
Дата добавления - 28.02.2016 в 17:31
_Boroda_ Дата: Воскресенье, 28.02.2016, 18:10 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 9380
Репутация: 3951 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?
К сообщению приложен файл: Test_1.xlsx(11Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?

Автор - _Boroda_
Дата добавления - 28.02.2016 в 18:10
FrameG2 Дата: Воскресенье, 28.02.2016, 18:57 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
_Boroda_, то, что нужно! Спасибо!
 
Ответить
Сообщение_Boroda_, то, что нужно! Спасибо!

Автор - FrameG2
Дата добавления - 28.02.2016 в 18:57
vikttur Дата: Воскресенье, 28.02.2016, 19:08 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 2030
Репутация: 355 ±
Замечаний: 0% ±

Код
=СЧЁТ(1/($B$2-ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ($C$2;;;1;СТОЛБЕЦ($A$1:A$1)))<=0))>5


Саня, тех, которых >0, не обязательно 4
понял :)


Сообщение отредактировал vikttur - Воскресенье, 28.02.2016, 19:10
 
Ответить
Сообщение
Код
=СЧЁТ(1/($B$2-ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ($C$2;;;1;СТОЛБЕЦ($A$1:A$1)))<=0))>5


Саня, тех, которых >0, не обязательно 4
понял :)

Автор - vikttur
Дата добавления - 28.02.2016 в 19:08
AlexM Дата: Воскресенье, 28.02.2016, 19:17 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3053
Репутация: 740 ±
Замечаний: 0% ±

Еще вариант формулы в УФ
Код
=СТОЛБЕЦ(A1)>=ПОИСКПОЗ(1;--($B2<ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ($C2;;;;СТОЛБЕЦ($A2:$O2))));)+5

vikttur, Если в В2 вставить 340, то желтых получится 4 шт.
Формулу Александра Excel2003 не видит
К сообщению приложен файл: Test.xls(25Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеЕще вариант формулы в УФ
Код
=СТОЛБЕЦ(A1)>=ПОИСКПОЗ(1;--($B2<ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ($C2;;;;СТОЛБЕЦ($A2:$O2))));)+5

vikttur, Если в В2 вставить 340, то желтых получится 4 шт.
Формулу Александра Excel2003 не видит

Автор - AlexM
Дата добавления - 28.02.2016 в 19:17
vikttur Дата: Воскресенье, 28.02.2016, 19:30 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 2030
Репутация: 355 ±
Замечаний: 0% ±

Если в В2 вставить 340, то желтых получится 4 шт.

Не смертельно - вместо "<=" оставить "<"
 
Ответить
Сообщение
Если в В2 вставить 340, то желтых получится 4 шт.

Не смертельно - вместо "<=" оставить "<"

Автор - vikttur
Дата добавления - 28.02.2016 в 19:30
FrameG2 Дата: Понедельник, 29.02.2016, 00:00 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
А можно вас попросить немного модифицировать решение?
Правило, которое закрашивает в красный цвет, смещая вправо на 5, должно теперь учитывать символ "-". Т.е. от начала жёлтых ячеек смещать вправо на 5 + на количество "-" в жёлтых ячейках.
На обновлённом примере, который я прикрепил, в 3-ей строке в жёлтых ячейках присутствует 3 символа "-", следовательно, красные ячейки должны сместиться не на 5, а на 5+3=8 (т.е. ячейки с числами 50, 60, 40 должны остаться жёлтыми, а все, что правее - красными). Поможете с решением?
К сообщению приложен файл: Test2.xlsx(11Kb)


Сообщение отредактировал FrameG2 - Понедельник, 29.02.2016, 00:02
 
Ответить
СообщениеА можно вас попросить немного модифицировать решение?
Правило, которое закрашивает в красный цвет, смещая вправо на 5, должно теперь учитывать символ "-". Т.е. от начала жёлтых ячеек смещать вправо на 5 + на количество "-" в жёлтых ячейках.
На обновлённом примере, который я прикрепил, в 3-ей строке в жёлтых ячейках присутствует 3 символа "-", следовательно, красные ячейки должны сместиться не на 5, а на 5+3=8 (т.е. ячейки с числами 50, 60, 40 должны остаться жёлтыми, а все, что правее - красными). Поможете с решением?

Автор - FrameG2
Дата добавления - 29.02.2016 в 00:00
Nic70y Дата: Понедельник, 29.02.2016, 08:45 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3493
Репутация: 724 ±
Замечаний: 0% ±

Excel 2013
Код
=СТОЛБЕЦ()>НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ(($B2-ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ($C2;;;;СТОЛБЕЦ($A2:$O2))));ЕСЛИ(($B2-ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ($C2;;;;СТОЛБЕЦ($A2:$O2))))<0;($B2-ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ($C2;;;;СТОЛБЕЦ($A2:$O2)))));)=СТОЛБЕЦ($A2:$O2);СТОЛБЕЦ($A2:$O2));1=2);5)+2
К сообщению приложен файл: 2096949.xlsx(11Kb)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщение
Код
=СТОЛБЕЦ()>НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ(($B2-ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ($C2;;;;СТОЛБЕЦ($A2:$O2))));ЕСЛИ(($B2-ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ($C2;;;;СТОЛБЕЦ($A2:$O2))))<0;($B2-ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ($C2;;;;СТОЛБЕЦ($A2:$O2)))));)=СТОЛБЕЦ($A2:$O2);СТОЛБЕЦ($A2:$O2));1=2);5)+2

Автор - Nic70y
Дата добавления - 29.02.2016 в 08:45
FrameG2 Дата: Понедельник, 29.02.2016, 14:14 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Nic70y, спасибо! Вроде работает! Довольно жёсткое решение :)
Правильно ли я понял, если мне понадобится
- изменить красное "смещение" с 5 до 7, я просто заменяю цифру 5 на 7 ?
- увеличить таблицу (область с условным форматированием) вправо, то придётся вручную менять O2 на новый столбец?
- добавить столбцы слева, то придётся вручную прибавлять такое же кол-во к последней цифре 2 ?
Можно ли последние 2 пункта как-то автоматизировать? :)
И что означает 1=2 в конце?


Сообщение отредактировал FrameG2 - Понедельник, 29.02.2016, 14:14
 
Ответить
СообщениеNic70y, спасибо! Вроде работает! Довольно жёсткое решение :)
Правильно ли я понял, если мне понадобится
- изменить красное "смещение" с 5 до 7, я просто заменяю цифру 5 на 7 ?
- увеличить таблицу (область с условным форматированием) вправо, то придётся вручную менять O2 на новый столбец?
- добавить столбцы слева, то придётся вручную прибавлять такое же кол-во к последней цифре 2 ?
Можно ли последние 2 пункта как-то автоматизировать? :)
И что означает 1=2 в конце?

Автор - FrameG2
Дата добавления - 29.02.2016 в 14:14
Nic70y Дата: Понедельник, 29.02.2016, 14:17 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3493
Репутация: 724 ±
Замечаний: 0% ±

Excel 2013
FrameG2, да все верно, задайте сразу максимальный диапазон
добавить столбцы слева
можно усложнить формулу, но думаю не стоит...
И что означает 1=2 в конце?
=ЛОЖЬ


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
СообщениеFrameG2, да все верно, задайте сразу максимальный диапазон
добавить столбцы слева
можно усложнить формулу, но думаю не стоит...
И что означает 1=2 в конце?
=ЛОЖЬ

Автор - Nic70y
Дата добавления - 29.02.2016 в 14:17
FrameG2 Дата: Понедельник, 29.02.2016, 14:33 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Nic70y, да, сразу не обратил внимания, есть небольшой минус у решения. Правило должно учитывать "-", но оно учитывает ещё и пустые ячейки, хотя не желательно. Т.е. пустые жёлтые ячейки и ячейки с числами должны обрабатываться одинаково. Например, если жёлтая ячейка начинается с числа, а затем идут 4 пустые ячейки, то дальше сразу должны идти красные. Возможно ли подкорректировать решение?
 
Ответить
СообщениеNic70y, да, сразу не обратил внимания, есть небольшой минус у решения. Правило должно учитывать "-", но оно учитывает ещё и пустые ячейки, хотя не желательно. Т.е. пустые жёлтые ячейки и ячейки с числами должны обрабатываться одинаково. Например, если жёлтая ячейка начинается с числа, а затем идут 4 пустые ячейки, то дальше сразу должны идти красные. Возможно ли подкорректировать решение?

Автор - FrameG2
Дата добавления - 29.02.2016 в 14:33
Nic70y Дата: Вторник, 01.03.2016, 07:22 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3493
Репутация: 724 ±
Замечаний: 0% ±

Excel 2013
FrameG2, а если пустые будут в зеленых, то откуда должны начаться желтые?


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
СообщениеFrameG2, а если пустые будут в зеленых, то откуда должны начаться желтые?

Автор - Nic70y
Дата добавления - 01.03.2016 в 07:22
FrameG2 Дата: Вторник, 01.03.2016, 12:03 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Nic70y, а жёлтые вычисляются по формуле, которая в правилах указана. Первая ячейка, в которой "число-СУММ(диапазон чисел)<0", становится жёлтой. Зелёные и жёлтые работают как надо (можно проследить, как они работают, заменив ячейки с числами на пустые). А вот красные должны появиться от начала жёлтых через 5 ячеек, если в них только ячейки с числами или пустые ячейки (помогло решение, предложенное во 2-ом посте), или на 5+x, где x - кол-во ячеек с символом "-" в жёлтой области (модифицированное задание).
Пустые ячейки могут быть и в зелёных, и в жёлтых, и в красных. И ячейки с "-" тоже могут быть и в зелёных, и в жёлтых, и в красных. Пустые ячейки каждым правилом воспринимаются как 0. Ячейки с "-" воспринимаются как 0 "зелёным" и "желтым" правилами, а для "красного" это означает сместиться ещё правее (это и есть модифицированное задание).
Извиняюсь, что сразу не указал эти моменты.

Может быть какие-то обходные варианты можно(проще) сделать? Допустим, взять решение со второго поста и добавить 4-ое правило, которое поверх красных докрашивает жёлтые, если в жёлтых имеются ячейки с "-". :)


Сообщение отредактировал FrameG2 - Вторник, 01.03.2016, 13:46
 
Ответить
СообщениеNic70y, а жёлтые вычисляются по формуле, которая в правилах указана. Первая ячейка, в которой "число-СУММ(диапазон чисел)<0", становится жёлтой. Зелёные и жёлтые работают как надо (можно проследить, как они работают, заменив ячейки с числами на пустые). А вот красные должны появиться от начала жёлтых через 5 ячеек, если в них только ячейки с числами или пустые ячейки (помогло решение, предложенное во 2-ом посте), или на 5+x, где x - кол-во ячеек с символом "-" в жёлтой области (модифицированное задание).
Пустые ячейки могут быть и в зелёных, и в жёлтых, и в красных. И ячейки с "-" тоже могут быть и в зелёных, и в жёлтых, и в красных. Пустые ячейки каждым правилом воспринимаются как 0. Ячейки с "-" воспринимаются как 0 "зелёным" и "желтым" правилами, а для "красного" это означает сместиться ещё правее (это и есть модифицированное задание).
Извиняюсь, что сразу не указал эти моменты.

Может быть какие-то обходные варианты можно(проще) сделать? Допустим, взять решение со второго поста и добавить 4-ое правило, которое поверх красных докрашивает жёлтые, если в жёлтых имеются ячейки с "-". :)

Автор - FrameG2
Дата добавления - 01.03.2016 в 12:03
МВТ Дата: Вторник, 01.03.2016, 21:30 | Сообщение № 14
Группа: Проверенные
Ранг: Обитатель
Сообщений: 476
Репутация: 136 ±
Замечаний: 0% ±

Excel 2007
Я формулами не умею, вот макрос нарисовался. Можно повесить на событие листа Change или Calculate, в зависимости от реальной задачи
[vba]
Код
Sub tt()
Dim I As Long, Sm As Long, Cnt As Long, iCol As Long
iCol = Cells(2, Columns.Count).End(xlToRight).Column
For I = 3 To iCol
    Sm = WorksheetFunction.Sum(Range(Cells(2, 3), Cells(2, I)))
    Sm = [b2] - Sm
    If Sm >= 0 Then
        Cells(2, I).Interior.Color = vbGreen
        Cnt = 0
    Else
        If Cells(2, I) <> "-" Then Cnt = Cnt + 1
        If Cnt >= 5 Then Cells(2, I).Interior.Color = vbRed Else Cells(2, I).Interior.Color = vbYellow
    End If
Next
End Sub
[/vba]
 
Ответить
СообщениеЯ формулами не умею, вот макрос нарисовался. Можно повесить на событие листа Change или Calculate, в зависимости от реальной задачи
[vba]
Код
Sub tt()
Dim I As Long, Sm As Long, Cnt As Long, iCol As Long
iCol = Cells(2, Columns.Count).End(xlToRight).Column
For I = 3 To iCol
    Sm = WorksheetFunction.Sum(Range(Cells(2, 3), Cells(2, I)))
    Sm = [b2] - Sm
    If Sm >= 0 Then
        Cells(2, I).Interior.Color = vbGreen
        Cnt = 0
    Else
        If Cells(2, I) <> "-" Then Cnt = Cnt + 1
        If Cnt >= 5 Then Cells(2, I).Interior.Color = vbRed Else Cells(2, I).Interior.Color = vbYellow
    End If
Next
End Sub
[/vba]

Автор - МВТ
Дата добавления - 01.03.2016 в 21:30
FrameG2 Дата: Среда, 02.03.2016, 13:59 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
МВТ, спасибо, только с макросами я плохо дружу :)
Как повесить на событие листа я пока не разобрался (если подскажете, как это сделать, буду благодарен), разобрался только как вручную макрос запускать.
И есть вопросы по коду:
- почему-то он красные ячейки закрашивает через 4 жёлтых, а не через 5 (хотя в коде >=5). Если поставить >=6, то вроде нормально становится (странно).
- что надо изменить, чтобы код работал не на одну строку, а на несколько?
- как можно ограничить кол-во закрашиваемых столбцов? Т.е. чтобы закрашивало не до бесконечности, а до определённого предела?


Сообщение отредактировал FrameG2 - Среда, 02.03.2016, 14:00
 
Ответить
СообщениеМВТ, спасибо, только с макросами я плохо дружу :)
Как повесить на событие листа я пока не разобрался (если подскажете, как это сделать, буду благодарен), разобрался только как вручную макрос запускать.
И есть вопросы по коду:
- почему-то он красные ячейки закрашивает через 4 жёлтых, а не через 5 (хотя в коде >=5). Если поставить >=6, то вроде нормально становится (странно).
- что надо изменить, чтобы код работал не на одну строку, а на несколько?
- как можно ограничить кол-во закрашиваемых столбцов? Т.е. чтобы закрашивало не до бесконечности, а до определённого предела?

Автор - FrameG2
Дата добавления - 02.03.2016 в 13:59
МВТ Дата: Среда, 02.03.2016, 18:55 | Сообщение № 16
Группа: Проверенные
Ранг: Обитатель
Сообщений: 476
Репутация: 136 ±
Замечаний: 0% ±

Excel 2007
FrameG2, так приведите адекватный пример: каким числом ограничить количество колонок, откуда берутся данные (вводятся вручную или получаются в результате работы формул), какие строки будут задействованы в "процессе"? А пока это - "пойди туда, не знаю куда..."
 
Ответить
СообщениеFrameG2, так приведите адекватный пример: каким числом ограничить количество колонок, откуда берутся данные (вводятся вручную или получаются в результате работы формул), какие строки будут задействованы в "процессе"? А пока это - "пойди туда, не знаю куда..."

Автор - МВТ
Дата добавления - 02.03.2016 в 18:55
FrameG2 Дата: Среда, 02.03.2016, 23:39 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
МВТ, виноват!
Итак, опишу ещё раз и максимально подробно своё задание :)
Имеется файл, который я прикрепил. В нём простая таблица, которую условно можно разделить на 2 части: столбец B (B2 и ниже) и всё остальное (C2 и правее-ниже). Таблица в дальнейшем будет расширяться вправо-вниз. Ячейки, которые расположены правее столбца B, закрашиваются в 3 цвета (зелёный, жёлтый, красный) в зависимости от результата вычислений по формуле и от одного дополнительного условия. Красный цвет в прикреплённом примере работает не совсем правильно, т.к. не учитывает дополнительное условие (ячейки с символом "-"). Именно его нужно учесть! В остальном же всё верно.

Опишу алгоритм закрашивания на примере строки №2:
* Зелёный цвет (2 условия): ячейка_B2_не_пуста * B2-сумма(диапазон_чисел_справа_от_B2)>=0. В области 'C2 и правее' пустые ячейки и ячейки с символом "-" воспринимаются "зелёным цветом" как 0.
* Жёлтый цвет (2 условия): ячейка_B2_не_пуста * B2-сумма(диапазон_чисел_справа_от_B2)<0. В области 'C2 и правее' пустые ячейки и ячейки с символом "-" воспринимаются "жёлтым цветом" как 0.
* Красный цвет (2 условия): ячейка_B2_не_пуста * смещение 5+X от начала жёлтых ячеек, где 5 означает 5 раз подряд повторилось "жёлтое" условие B2-сумма(диапазон_чисел_справа_от_B2)<0, X означает количество ячеек с символом "-" среди жёлтых ячеек (именно среди жёлтых, не зелёных!). В области 'C2 и правее' пустые ячейки воспринимаются "красным цветом" как 0, а ячейки с символом "-" среди жёлтых ячеек воспринимаются "красным цветом" как дополнительное смещение вправо.

На данный момент верно работают только зелёный и жёлтый цвета (реализовано через Условное Форматирование). Поэтому задание такое: реализовать правильную работу всех 3-х цветов.

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

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

upd: если что, в столбце B могут быть числа от 0 и выше, а также пустые ячейки.
К сообщению приложен файл: Test21.xlsx(10Kb)


Сообщение отредактировал FrameG2 - Четверг, 03.03.2016, 00:08
 
Ответить
СообщениеМВТ, виноват!
Итак, опишу ещё раз и максимально подробно своё задание :)
Имеется файл, который я прикрепил. В нём простая таблица, которую условно можно разделить на 2 части: столбец B (B2 и ниже) и всё остальное (C2 и правее-ниже). Таблица в дальнейшем будет расширяться вправо-вниз. Ячейки, которые расположены правее столбца B, закрашиваются в 3 цвета (зелёный, жёлтый, красный) в зависимости от результата вычислений по формуле и от одного дополнительного условия. Красный цвет в прикреплённом примере работает не совсем правильно, т.к. не учитывает дополнительное условие (ячейки с символом "-"). Именно его нужно учесть! В остальном же всё верно.

Опишу алгоритм закрашивания на примере строки №2:
* Зелёный цвет (2 условия): ячейка_B2_не_пуста * B2-сумма(диапазон_чисел_справа_от_B2)>=0. В области 'C2 и правее' пустые ячейки и ячейки с символом "-" воспринимаются "зелёным цветом" как 0.
* Жёлтый цвет (2 условия): ячейка_B2_не_пуста * B2-сумма(диапазон_чисел_справа_от_B2)<0. В области 'C2 и правее' пустые ячейки и ячейки с символом "-" воспринимаются "жёлтым цветом" как 0.
* Красный цвет (2 условия): ячейка_B2_не_пуста * смещение 5+X от начала жёлтых ячеек, где 5 означает 5 раз подряд повторилось "жёлтое" условие B2-сумма(диапазон_чисел_справа_от_B2)<0, X означает количество ячеек с символом "-" среди жёлтых ячеек (именно среди жёлтых, не зелёных!). В области 'C2 и правее' пустые ячейки воспринимаются "красным цветом" как 0, а ячейки с символом "-" среди жёлтых ячеек воспринимаются "красным цветом" как дополнительное смещение вправо.

На данный момент верно работают только зелёный и жёлтый цвета (реализовано через Условное Форматирование). Поэтому задание такое: реализовать правильную работу всех 3-х цветов.

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

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

upd: если что, в столбце B могут быть числа от 0 и выше, а также пустые ячейки.

Автор - FrameG2
Дата добавления - 02.03.2016 в 23:39
МВТ Дата: Четверг, 03.03.2016, 21:49 | Сообщение № 18
Группа: Проверенные
Ранг: Обитатель
Сообщений: 476
Репутация: 136 ±
Замечаний: 0% ±

Excel 2007
FrameG2, попробуйте так
[vba]
Код
Sub tt()
Const StartRow = 2, StartCol = 2, MaxCol = 9 ^ 9
Dim I As Long, J As Long, Sm As Long, Cnt As Long, iCol As Long
    Application.ScreenUpdating = False
    ActiveSheet.UsedRange.Interior.Color = vbWhite
    For J = StartRow To Cells(Rows.Count, StartCol).End(xlUp).Row
        iCol = WorksheetFunction.Min(Cells(J, Columns.Count).End(xlToRight).Column, MaxCol)
        For I = StartCol + 1 To iCol
            If Not IsEmpty(Cells(J, I)) Then
                Sm = WorksheetFunction.Sum(Range(Cells(J, StartCol + 1), Cells(J, I)))
                Sm = Cells(J, StartCol) - Sm
                If Sm >= 0 Then
                    Cells(J, I).Interior.Color = vbGreen
                    Cnt = 0
                Else
                    If Cells(J, I) <> "-" Then Cnt = Cnt + 1
                    If Cnt >= 6 Then Cells(J, I).Interior.Color = vbRed Else Cells(J, I).Interior.Color = vbYellow
                End If
            End If
        Next
    Next
    Application.ScreenUpdating = True
End Sub
[/vba]
 
Ответить
СообщениеFrameG2, попробуйте так
[vba]
Код
Sub tt()
Const StartRow = 2, StartCol = 2, MaxCol = 9 ^ 9
Dim I As Long, J As Long, Sm As Long, Cnt As Long, iCol As Long
    Application.ScreenUpdating = False
    ActiveSheet.UsedRange.Interior.Color = vbWhite
    For J = StartRow To Cells(Rows.Count, StartCol).End(xlUp).Row
        iCol = WorksheetFunction.Min(Cells(J, Columns.Count).End(xlToRight).Column, MaxCol)
        For I = StartCol + 1 To iCol
            If Not IsEmpty(Cells(J, I)) Then
                Sm = WorksheetFunction.Sum(Range(Cells(J, StartCol + 1), Cells(J, I)))
                Sm = Cells(J, StartCol) - Sm
                If Sm >= 0 Then
                    Cells(J, I).Interior.Color = vbGreen
                    Cnt = 0
                Else
                    If Cells(J, I) <> "-" Then Cnt = Cnt + 1
                    If Cnt >= 6 Then Cells(J, I).Interior.Color = vbRed Else Cells(J, I).Interior.Color = vbYellow
                End If
            End If
        Next
    Next
    Application.ScreenUpdating = True
End Sub
[/vba]

Автор - МВТ
Дата добавления - 03.03.2016 в 21:49
FrameG2 Дата: Четверг, 03.03.2016, 22:07 | Сообщение № 19
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
МВТ, пока работает не совсем так, как надо:
- Красные ячейки смещаются вправо и от "-", и от пустых, а должны только от "-".
- Пустые ячейки не закрашиваются, а должны.
- Желательно, чтобы остальные ячейки, где отсутствуют числа, не закрашивались в белый... Т.е. лучше, чтобы сетка была видна (прозрачный цвет ячеек).
 
Ответить
СообщениеМВТ, пока работает не совсем так, как надо:
- Красные ячейки смещаются вправо и от "-", и от пустых, а должны только от "-".
- Пустые ячейки не закрашиваются, а должны.
- Желательно, чтобы остальные ячейки, где отсутствуют числа, не закрашивались в белый... Т.е. лучше, чтобы сетка была видна (прозрачный цвет ячеек).

Автор - FrameG2
Дата добавления - 03.03.2016 в 22:07
Nic70y Дата: Пятница, 04.03.2016, 08:23 | Сообщение № 20
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3493
Репутация: 724 ±
Замечаний: 0% ±

Excel 2013
УФ
Код
=СТОЛБЕЦ()>НАИМЕНЬШИЙ(ЕСЛИ(ЕЧИСЛО(--(0&ЕСЛИ(($B2-ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ($C2;;;;СТОЛБЕЦ($C2:$V2)-2)))<0;$C2:$V2)));СТОЛБЕЦ($C2:$V2));5)
К сообщению приложен файл: 9639594.xlsx(10Kb)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
СообщениеУФ
Код
=СТОЛБЕЦ()>НАИМЕНЬШИЙ(ЕСЛИ(ЕЧИСЛО(--(0&ЕСЛИ(($B2-ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ($C2;;;;СТОЛБЕЦ($C2:$V2)-2)))<0;$C2:$V2)));СТОЛБЕЦ($C2:$V2));5)

Автор - Nic70y
Дата добавления - 04.03.2016 в 08:23
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Правило, подсчитывающее количество истин (Условное Форматирование/Conditional Formattings)
Страница 1 из 212»
Поиск:

Яндекс цитирования
© 2010-2016 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!