Здравствуйте! Сразу скажу, что в Excel я слабовато разбираюсь. Итак, есть число, из которого вычитаются другие числа из диапазона и проверяется, >=0 или нет. В зависимости от результата ячейки из диапазона закрашиваются определённым цветом. Выглядит это примерно так (прикрепил пример, там наглядней):
Но надо добавить ещё одно правило: если правило "число-СУММ(диапазон чисел)<0 (жёлтый цвет)" повторилось 5 раз (дало 5 истин), то ячейка закрашивается в красный. Т.е. в примере после 5-ти жёлтых ячеек должны идти красные. Как такое правило сделать?
Здравствуйте! Сразу скажу, что в Excel я слабовато разбираюсь. Итак, есть число, из которого вычитаются другие числа из диапазона и проверяется, >=0 или нет. В зависимости от результата ячейки из диапазона закрашиваются определённым цветом. Выглядит это примерно так (прикрепил пример, там наглядней):
Но надо добавить ещё одно правило: если правило "число-СУММ(диапазон чисел)<0 (жёлтый цвет)" повторилось 5 раз (дало 5 истин), то ячейка закрашивается в красный. Т.е. в примере после 5-ти жёлтых ячеек должны идти красные. Как такое правило сделать?FrameG2
А можно вас попросить немного модифицировать решение? Правило, которое закрашивает в красный цвет, смещая вправо на 5, должно теперь учитывать символ "-". Т.е. от начала жёлтых ячеек смещать вправо на 5 + на количество "-" в жёлтых ячейках. На обновлённом примере, который я прикрепил, в 3-ей строке в жёлтых ячейках присутствует 3 символа "-", следовательно, красные ячейки должны сместиться не на 5, а на 5+3=8 (т.е. ячейки с числами 50, 60, 40 должны остаться жёлтыми, а все, что правее - красными). Поможете с решением?
А можно вас попросить немного модифицировать решение? Правило, которое закрашивает в красный цвет, смещая вправо на 5, должно теперь учитывать символ "-". Т.е. от начала жёлтых ячеек смещать вправо на 5 + на количество "-" в жёлтых ячейках. На обновлённом примере, который я прикрепил, в 3-ей строке в жёлтых ячейках присутствует 3 символа "-", следовательно, красные ячейки должны сместиться не на 5, а на 5+3=8 (т.е. ячейки с числами 50, 60, 40 должны остаться жёлтыми, а все, что правее - красными). Поможете с решением?FrameG2
Nic70y, спасибо! Вроде работает! Довольно жёсткое решение :) Правильно ли я понял, если мне понадобится - изменить красное "смещение" с 5 до 7, я просто заменяю цифру 5 на 7 ? - увеличить таблицу (область с условным форматированием) вправо, то придётся вручную менять O2 на новый столбец? - добавить столбцы слева, то придётся вручную прибавлять такое же кол-во к последней цифре 2 ? Можно ли последние 2 пункта как-то автоматизировать? :) И что означает 1=2 в конце?
Nic70y, спасибо! Вроде работает! Довольно жёсткое решение :) Правильно ли я понял, если мне понадобится - изменить красное "смещение" с 5 до 7, я просто заменяю цифру 5 на 7 ? - увеличить таблицу (область с условным форматированием) вправо, то придётся вручную менять O2 на новый столбец? - добавить столбцы слева, то придётся вручную прибавлять такое же кол-во к последней цифре 2 ? Можно ли последние 2 пункта как-то автоматизировать? :) И что означает 1=2 в конце?FrameG2
Сообщение отредактировал FrameG2 - Понедельник, 29.02.2016, 14:14
Nic70y, да, сразу не обратил внимания, есть небольшой минус у решения. Правило должно учитывать "-", но оно учитывает ещё и пустые ячейки, хотя не желательно. Т.е. пустые жёлтые ячейки и ячейки с числами должны обрабатываться одинаково. Например, если жёлтая ячейка начинается с числа, а затем идут 4 пустые ячейки, то дальше сразу должны идти красные. Возможно ли подкорректировать решение?
Nic70y, да, сразу не обратил внимания, есть небольшой минус у решения. Правило должно учитывать "-", но оно учитывает ещё и пустые ячейки, хотя не желательно. Т.е. пустые жёлтые ячейки и ячейки с числами должны обрабатываться одинаково. Например, если жёлтая ячейка начинается с числа, а затем идут 4 пустые ячейки, то дальше сразу должны идти красные. Возможно ли подкорректировать решение?FrameG2
Nic70y, а жёлтые вычисляются по формуле, которая в правилах указана. Первая ячейка, в которой "число-СУММ(диапазон чисел)<0", становится жёлтой. Зелёные и жёлтые работают как надо (можно проследить, как они работают, заменив ячейки с числами на пустые). А вот красные должны появиться от начала жёлтых через 5 ячеек, если в них только ячейки с числами или пустые ячейки (помогло решение, предложенное во 2-ом посте), или на 5+x, где x - кол-во ячеек с символом "-" в жёлтой области (модифицированное задание). Пустые ячейки могут быть и в зелёных, и в жёлтых, и в красных. И ячейки с "-" тоже могут быть и в зелёных, и в жёлтых, и в красных. Пустые ячейки каждым правилом воспринимаются как 0. Ячейки с "-" воспринимаются как 0 "зелёным" и "желтым" правилами, а для "красного" это означает сместиться ещё правее (это и есть модифицированное задание). Извиняюсь, что сразу не указал эти моменты.
Может быть какие-то обходные варианты можно(проще) сделать? Допустим, взять решение со второго поста и добавить 4-ое правило, которое поверх красных докрашивает жёлтые, если в жёлтых имеются ячейки с "-".
Nic70y, а жёлтые вычисляются по формуле, которая в правилах указана. Первая ячейка, в которой "число-СУММ(диапазон чисел)<0", становится жёлтой. Зелёные и жёлтые работают как надо (можно проследить, как они работают, заменив ячейки с числами на пустые). А вот красные должны появиться от начала жёлтых через 5 ячеек, если в них только ячейки с числами или пустые ячейки (помогло решение, предложенное во 2-ом посте), или на 5+x, где x - кол-во ячеек с символом "-" в жёлтой области (модифицированное задание). Пустые ячейки могут быть и в зелёных, и в жёлтых, и в красных. И ячейки с "-" тоже могут быть и в зелёных, и в жёлтых, и в красных. Пустые ячейки каждым правилом воспринимаются как 0. Ячейки с "-" воспринимаются как 0 "зелёным" и "желтым" правилами, а для "красного" это означает сместиться ещё правее (это и есть модифицированное задание). Извиняюсь, что сразу не указал эти моменты.
Может быть какие-то обходные варианты можно(проще) сделать? Допустим, взять решение со второго поста и добавить 4-ое правило, которое поверх красных докрашивает жёлтые, если в жёлтых имеются ячейки с "-". FrameG2
Сообщение отредактировал FrameG2 - Вторник, 01.03.2016, 13:46
Я формулами не умею, вот макрос нарисовался. Можно повесить на событие листа 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
МВТ, спасибо, только с макросами я плохо дружу :) Как повесить на событие листа я пока не разобрался (если подскажете, как это сделать, буду благодарен), разобрался только как вручную макрос запускать. И есть вопросы по коду: - почему-то он красные ячейки закрашивает через 4 жёлтых, а не через 5 (хотя в коде >=5). Если поставить >=6, то вроде нормально становится (странно). - что надо изменить, чтобы код работал не на одну строку, а на несколько? - как можно ограничить кол-во закрашиваемых столбцов? Т.е. чтобы закрашивало не до бесконечности, а до определённого предела?
МВТ, спасибо, только с макросами я плохо дружу :) Как повесить на событие листа я пока не разобрался (если подскажете, как это сделать, буду благодарен), разобрался только как вручную макрос запускать. И есть вопросы по коду: - почему-то он красные ячейки закрашивает через 4 жёлтых, а не через 5 (хотя в коде >=5). Если поставить >=6, то вроде нормально становится (странно). - что надо изменить, чтобы код работал не на одну строку, а на несколько? - как можно ограничить кол-во закрашиваемых столбцов? Т.е. чтобы закрашивало не до бесконечности, а до определённого предела?FrameG2
Сообщение отредактировал FrameG2 - Среда, 02.03.2016, 14:00
FrameG2, так приведите адекватный пример: каким числом ограничить количество колонок, откуда берутся данные (вводятся вручную или получаются в результате работы формул), какие строки будут задействованы в "процессе"? А пока это - "пойди туда, не знаю куда..."
FrameG2, так приведите адекватный пример: каким числом ограничить количество колонок, откуда берутся данные (вводятся вручную или получаются в результате работы формул), какие строки будут задействованы в "процессе"? А пока это - "пойди туда, не знаю куда..."МВТ
МВТ, виноват! Итак, опишу ещё раз и максимально подробно своё задание :) Имеется файл, который я прикрепил. В нём простая таблица, которую условно можно разделить на 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 и выше, а также пустые ячейки.
МВТ, виноват! Итак, опишу ещё раз и максимально подробно своё задание :) Имеется файл, который я прикрепил. В нём простая таблица, которую условно можно разделить на 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
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
МВТ, пока работает не совсем так, как надо: - Красные ячейки смещаются вправо и от "-", и от пустых, а должны только от "-". - Пустые ячейки не закрашиваются, а должны. - Желательно, чтобы остальные ячейки, где отсутствуют числа, не закрашивались в белый... Т.е. лучше, чтобы сетка была видна (прозрачный цвет ячеек).
МВТ, пока работает не совсем так, как надо: - Красные ячейки смещаются вправо и от "-", и от пустых, а должны только от "-". - Пустые ячейки не закрашиваются, а должны. - Желательно, чтобы остальные ячейки, где отсутствуют числа, не закрашивались в белый... Т.е. лучше, чтобы сетка была видна (прозрачный цвет ячеек).FrameG2