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

Вход

Регистрация

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

 

= Мир MS Excel/Условное форматирование при условии разницы в числах 10% - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Условное форматирование при условии разницы в числах 10% (Формулы/Formulas)
Условное форматирование при условии разницы в числах 10%
Ефгений Дата: Воскресенье, 22.12.2019, 23:49 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

2010
Здравствуйте форумчане. Помогите пожалуйста с формулой для условного форматирования.
1. В ячейку В записываем пробег когда нужно сделать ТО (например на 10000 км)
2. В ячейку А записываем фактический пробег (например 7000 км)
1 условие-Нужно чтобы окрашивалось число в ячейке В, если разница у этого числа с числом в ячейке А, 10%
2 условие-Чтобы так же окрашивались если число в ячейке А больше числа в ячейке В
Смысл заключается в том, чтобы понимать когда подходит ТО. Данные по пробегам заносятся програмно ежедневно в таблицу.
Оставил ссылку на файл https://docs.google.com/spreads....sharing
 
Ответить
СообщениеЗдравствуйте форумчане. Помогите пожалуйста с формулой для условного форматирования.
1. В ячейку В записываем пробег когда нужно сделать ТО (например на 10000 км)
2. В ячейку А записываем фактический пробег (например 7000 км)
1 условие-Нужно чтобы окрашивалось число в ячейке В, если разница у этого числа с числом в ячейке А, 10%
2 условие-Чтобы так же окрашивались если число в ячейке А больше числа в ячейке В
Смысл заключается в том, чтобы понимать когда подходит ТО. Данные по пробегам заносятся програмно ежедневно в таблицу.
Оставил ссылку на файл https://docs.google.com/spreads....sharing

Автор - Ефгений
Дата добавления - 22.12.2019 в 23:49
Gustav Дата: Понедельник, 23.12.2019, 02:19 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1755
Репутация: 703 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Цитата Ефгений, 22.12.2019 в 23:49, в сообщении № 1 ()
1 условие-Нужно чтобы окрашивалось число в ячейке В, если разница у этого числа с числом в ячейке А, 10%
2 условие-Чтобы так же окрашивались если число в ячейке А больше числа в ячейке В

Формулы ниже - для ячейки B2, т.е. делаем два отдельных правила УФ, стоя на ячейке B2. В другие ячейки столбца- копируем ячейку B2.

Правило 1 - например, жёлтая заливка - "приближение":
Код
=И(B2-A2<=10%*B2;A2<=B2)


Правило 2 - например, красная заливка - "превышение":
Код
=A2>B2


Если же нужно одно правило (с одним цветом) на эти два условия, то соединяем их по ИЛИ в одной формуле:
Код
=OR(AND(B2-A2<=10%*B2;A2<=B2); A2>B2)

Ну, или после несложного математического упрощения:
Код
=A2>=90%*B2

Хотя неупрощенный вариант, на мой взгляд, более концептуален :)


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Понедельник, 23.12.2019, 02:42
 
Ответить
Сообщение
Цитата Ефгений, 22.12.2019 в 23:49, в сообщении № 1 ()
1 условие-Нужно чтобы окрашивалось число в ячейке В, если разница у этого числа с числом в ячейке А, 10%
2 условие-Чтобы так же окрашивались если число в ячейке А больше числа в ячейке В

Формулы ниже - для ячейки B2, т.е. делаем два отдельных правила УФ, стоя на ячейке B2. В другие ячейки столбца- копируем ячейку B2.

Правило 1 - например, жёлтая заливка - "приближение":
Код
=И(B2-A2<=10%*B2;A2<=B2)


Правило 2 - например, красная заливка - "превышение":
Код
=A2>B2


Если же нужно одно правило (с одним цветом) на эти два условия, то соединяем их по ИЛИ в одной формуле:
Код
=OR(AND(B2-A2<=10%*B2;A2<=B2); A2>B2)

Ну, или после несложного математического упрощения:
Код
=A2>=90%*B2

Хотя неупрощенный вариант, на мой взгляд, более концептуален :)

Автор - Gustav
Дата добавления - 23.12.2019 в 02:19
Ефгений Дата: Понедельник, 23.12.2019, 11:34 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

2010
Спасибо дружище. Очень помогло. От меня донат. :)
 
Ответить
СообщениеСпасибо дружище. Очень помогло. От меня донат. :)

Автор - Ефгений
Дата добавления - 23.12.2019 в 11:34
Ефгений Дата: Понедельник, 23.12.2019, 11:40 | Сообщение № 4
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

2010
Имеется такой момент. Окрашиваются все пустые ячейки, пока не занести в них данные.
И еще такой момент, я забыл о нем упомянуть, в ячейку В2 данные попадают с другой вкладки, с помощью функции VLOOKUP, и как я понимаю из за этого не работает УФ.


Сообщение отредактировал Ефгений - Понедельник, 23.12.2019, 11:53
 
Ответить
СообщениеИмеется такой момент. Окрашиваются все пустые ячейки, пока не занести в них данные.
И еще такой момент, я забыл о нем упомянуть, в ячейку В2 данные попадают с другой вкладки, с помощью функции VLOOKUP, и как я понимаю из за этого не работает УФ.

Автор - Ефгений
Дата добавления - 23.12.2019 в 11:40
Gustav Дата: Понедельник, 23.12.2019, 12:06 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1755
Репутация: 703 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Цитата Ефгений, 23.12.2019 в 11:40, в сообщении № 4 ()
От меня донат.

Спасибо, видел, приятно! :)
Цитата Ефгений, 23.12.2019 в 11:40, в сообщении № 4 ()
Окрашиваются все пустые ячейки, пока не занести в них данные

Надо добавить в первую формулу условие непустоты ячейки A2:
Код
=AND(B2-A2<=10%*B2; A2<=B2; NOT(ISBLANK(A2)))

Это минимум. Максимум - можно добавить еще и проверку самой B2:
Код
=AND(B2-A2<=10%*B2; A2<=B2; NOT(ISBLANK(A2)); NOT(ISBLANK(B2)))


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
Цитата Ефгений, 23.12.2019 в 11:40, в сообщении № 4 ()
От меня донат.

Спасибо, видел, приятно! :)
Цитата Ефгений, 23.12.2019 в 11:40, в сообщении № 4 ()
Окрашиваются все пустые ячейки, пока не занести в них данные

Надо добавить в первую формулу условие непустоты ячейки A2:
Код
=AND(B2-A2<=10%*B2; A2<=B2; NOT(ISBLANK(A2)))

Это минимум. Максимум - можно добавить еще и проверку самой B2:
Код
=AND(B2-A2<=10%*B2; A2<=B2; NOT(ISBLANK(A2)); NOT(ISBLANK(B2)))

Автор - Gustav
Дата добавления - 23.12.2019 в 12:06
Gustav Дата: Понедельник, 23.12.2019, 12:11 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1755
Репутация: 703 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Цитата Ефгений, 23.12.2019 в 11:40, в сообщении № 4 ()
в ячейку В2 данные попадают с другой вкладки, с помощью функции VLOOKUP, и как я понимаю из за этого не работает УФ

Должно работать. Покажите на примере.


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
Цитата Ефгений, 23.12.2019 в 11:40, в сообщении № 4 ()
в ячейку В2 данные попадают с другой вкладки, с помощью функции VLOOKUP, и как я понимаю из за этого не работает УФ

Должно работать. Покажите на примере.

Автор - Gustav
Дата добавления - 23.12.2019 в 12:11
Ефгений Дата: Понедельник, 23.12.2019, 12:36 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

2010
Вот. Правда вместо столбцов А и В, у меня Q и R
https://docs.google.com/spreads....sharing


Сообщение отредактировал Ефгений - Понедельник, 23.12.2019, 12:36
 
Ответить
СообщениеВот. Правда вместо столбцов А и В, у меня Q и R
https://docs.google.com/spreads....sharing

Автор - Ефгений
Дата добавления - 23.12.2019 в 12:36
Gustav Дата: Понедельник, 23.12.2019, 14:18 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1755
Репутация: 703 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Цитата Ефгений, 23.12.2019 в 12:36, в сообщении № 7 ()
вместо столбцов А и В, у меня Q и R

И в чем проблема? То, что пустая - красная? А зачем же Вы применили 1-е условие, а потом формулу для объединенного -1-го и 2-го условий? Масло масляное получилось. Надо чистое 2-е условие взять для красного, т.е. формулу с исходным видом:
Код
=A2>B2

Или после преобразования:
Код
=Q2>R2


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
Цитата Ефгений, 23.12.2019 в 12:36, в сообщении № 7 ()
вместо столбцов А и В, у меня Q и R

И в чем проблема? То, что пустая - красная? А зачем же Вы применили 1-е условие, а потом формулу для объединенного -1-го и 2-го условий? Масло масляное получилось. Надо чистое 2-е условие взять для красного, т.е. формулу с исходным видом:
Код
=A2>B2

Или после преобразования:
Код
=Q2>R2

Автор - Gustav
Дата добавления - 23.12.2019 в 14:18
Ефгений Дата: Понедельник, 23.12.2019, 18:32 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

2010
И в чем проблема? То, что пустая - красная? А зачем же Вы применили 1-е условие, а потом формулу для объединенного -1-го и 2-го условий? Масло масляное получилось. Надо чистое 2-е условие взять для красного, т.е. формулу с исходным видом:

Я запутался. Честно сказать я уже ничего не понимаю. В целом я прописал формулы которые Вы написали выше, изменив разве что только название столбцов. Первым условием покрасил все в желтый, убрав как я понимаю заливку для пустых ячеек, а вторым условием покрасил все красным т.к. пробег выше положенного. Красится не совсем как хотелось бы. Например в ячейке реальный пробег (А), у меня 100000, а пробег для ТО (В), 104000 и при этом ячейка красится. План был красить ячейки за 200-400 км до того что в В


Сообщение отредактировал Ефгений - Понедельник, 23.12.2019, 19:15
 
Ответить
Сообщение
И в чем проблема? То, что пустая - красная? А зачем же Вы применили 1-е условие, а потом формулу для объединенного -1-го и 2-го условий? Масло масляное получилось. Надо чистое 2-е условие взять для красного, т.е. формулу с исходным видом:

Я запутался. Честно сказать я уже ничего не понимаю. В целом я прописал формулы которые Вы написали выше, изменив разве что только название столбцов. Первым условием покрасил все в желтый, убрав как я понимаю заливку для пустых ячеек, а вторым условием покрасил все красным т.к. пробег выше положенного. Красится не совсем как хотелось бы. Например в ячейке реальный пробег (А), у меня 100000, а пробег для ТО (В), 104000 и при этом ячейка красится. План был красить ячейки за 200-400 км до того что в В

Автор - Ефгений
Дата добавления - 23.12.2019 в 18:32
Gustav Дата: Понедельник, 23.12.2019, 19:26 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1755
Репутация: 703 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Цитата Ефгений, 23.12.2019 в 18:32, в сообщении № 9 ()
Я запутался.

Давайте еще раз. Следите за моим движением.

В моем сообщении №2 - четыре формулы.

Чтобы покраситься двумя цветами - жёлтым и красным - нужны только 1-я и 2-я формулы из этих четырех (в 1-ю формулу надо добавить последующую поправку на непустые ячейки)

Чтобы покраситься ТОЛЬКО ОДНИМ цветом на все случаи жизни - например, фиолетовым - нужна только 3-я формула (или ее упрощенный вариант - 4-я формула).

Когда я писал выше сообщение №8, у Вас в таблице была мешанина из 1-й и 4-й формул. Это - НЕПРАВИЛЬНО. Надо - либо 1-я + 2-я, либо только 3-я.


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
Цитата Ефгений, 23.12.2019 в 18:32, в сообщении № 9 ()
Я запутался.

Давайте еще раз. Следите за моим движением.

В моем сообщении №2 - четыре формулы.

Чтобы покраситься двумя цветами - жёлтым и красным - нужны только 1-я и 2-я формулы из этих четырех (в 1-ю формулу надо добавить последующую поправку на непустые ячейки)

Чтобы покраситься ТОЛЬКО ОДНИМ цветом на все случаи жизни - например, фиолетовым - нужна только 3-я формула (или ее упрощенный вариант - 4-я формула).

Когда я писал выше сообщение №8, у Вас в таблице была мешанина из 1-й и 4-й формул. Это - НЕПРАВИЛЬНО. Надо - либо 1-я + 2-я, либо только 3-я.

Автор - Gustav
Дата добавления - 23.12.2019 в 19:26
Ефгений Дата: Понедельник, 23.12.2019, 19:38 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

2010
Давайте еще раз. Следите за моим движением.

Нет, я конечно глуп, но не на столько. Давайте я Вам объясню. Я сделал именно так как вы описали выше, но не работает и начал городить. Вот я заново сделал так как вы описали. Не работает! Можете сами убедится!
 
Ответить
Сообщение
Давайте еще раз. Следите за моим движением.

Нет, я конечно глуп, но не на столько. Давайте я Вам объясню. Я сделал именно так как вы описали выше, но не работает и начал городить. Вот я заново сделал так как вы описали. Не работает! Можете сами убедится!

Автор - Ефгений
Дата добавления - 23.12.2019 в 19:38
Gustav Дата: Понедельник, 23.12.2019, 20:12 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 1755
Репутация: 703 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Цитата Ефгений, 23.12.2019 в 19:38, в сообщении № 11 ()
Не работает! Можете сами убедится!

Убедился... что про проверку на пустоту в сообщении №5 мы просто так поговорили. Вот так должна выглядеть формула для 1-го ("рыжего") правила:
Код
=AND(R2-Q2<=10%*R2; Q2<=R2; NOT(ISBLANK(Q2)); NOT(ISBLANK(R2)))


[p.s.]Или лучше так, поскольку у Вас там контроль ошибки с возвратом пустой строки:
Код
=AND(R2-Q2<=10%*R2; Q2<=R2; Q2<>""; NOT(ISBLANK(R2)))

т.е. вместо
Код
NOT(ISBLANK(Q2))

пишем проще
Код
Q2<>""

И тогда не будет зажигаться рыжим при пустом Q2


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Понедельник, 23.12.2019, 20:36
 
Ответить
Сообщение
Цитата Ефгений, 23.12.2019 в 19:38, в сообщении № 11 ()
Не работает! Можете сами убедится!

Убедился... что про проверку на пустоту в сообщении №5 мы просто так поговорили. Вот так должна выглядеть формула для 1-го ("рыжего") правила:
Код
=AND(R2-Q2<=10%*R2; Q2<=R2; NOT(ISBLANK(Q2)); NOT(ISBLANK(R2)))


[p.s.]Или лучше так, поскольку у Вас там контроль ошибки с возвратом пустой строки:
Код
=AND(R2-Q2<=10%*R2; Q2<=R2; Q2<>""; NOT(ISBLANK(R2)))

т.е. вместо
Код
NOT(ISBLANK(Q2))

пишем проще
Код
Q2<>""

И тогда не будет зажигаться рыжим при пустом Q2

Автор - Gustav
Дата добавления - 23.12.2019 в 20:12
Ефгений Дата: Понедельник, 23.12.2019, 20:30 | Сообщение № 13
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

2010
Убедился... что про проверку на пустоту в сообщении №5 мы просто так поговорили. Вот так должна выглядеть формула для 1-го ("рыжего") правила:

Да, потому что я хотел указать на то что формулы все равно не решают задачу. Да и пустые все равно окрашены. Как писал выше цель была окрашивать пробеги когда подходит время делать ТО
 
Ответить
Сообщение
Убедился... что про проверку на пустоту в сообщении №5 мы просто так поговорили. Вот так должна выглядеть формула для 1-го ("рыжего") правила:

Да, потому что я хотел указать на то что формулы все равно не решают задачу. Да и пустые все равно окрашены. Как писал выше цель была окрашивать пробеги когда подходит время делать ТО

Автор - Ефгений
Дата добавления - 23.12.2019 в 20:30
Gustav Дата: Понедельник, 23.12.2019, 20:42 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1755
Репутация: 703 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Цитата Ефгений, 23.12.2019 в 20:30, в сообщении № 13 ()
формулы все равно не решают задачу

Ну как это... Все прекрасно решается! Включите доступ на редактирование для вошедших в аккаунт - я Вам всё настрою по материалам этого обсуждения.

[p.s.]Вот моя таблица, скачайте, посмотрите как сделано:
https://docs.google.com/spreads....1446747


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Понедельник, 23.12.2019, 20:47
 
Ответить
Сообщение
Цитата Ефгений, 23.12.2019 в 20:30, в сообщении № 13 ()
формулы все равно не решают задачу

Ну как это... Все прекрасно решается! Включите доступ на редактирование для вошедших в аккаунт - я Вам всё настрою по материалам этого обсуждения.

[p.s.]Вот моя таблица, скачайте, посмотрите как сделано:
https://docs.google.com/spreads....1446747

Автор - Gustav
Дата добавления - 23.12.2019 в 20:42
Ефгений Дата: Понедельник, 23.12.2019, 21:11 | Сообщение № 15
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

2010
Ну как это... Все прекрасно решается! Включите доступ на редактирование для вошедших в аккаунт - я Вам всё настрою по материалам этого обсуждения.

Я открыл доступ. Я прошу прощения за мой уровень мышления. Со мной всегда все сложно. Я не способен. Спасибо Вам заранее.
 
Ответить
Сообщение
Ну как это... Все прекрасно решается! Включите доступ на редактирование для вошедших в аккаунт - я Вам всё настрою по материалам этого обсуждения.

Я открыл доступ. Я прошу прощения за мой уровень мышления. Со мной всегда все сложно. Я не способен. Спасибо Вам заранее.

Автор - Ефгений
Дата добавления - 23.12.2019 в 21:11
Gustav Дата: Вторник, 24.12.2019, 10:39 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 1755
Репутация: 703 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
OK, cделал в Вашем файле вот так для рыжей закраски:
Код
=AND(R2-Q2<=10%*R2; Q2<=R2; Q2<>""; R2<>"")

Цитата Ефгений, 23.12.2019 в 18:32, в сообщении № 9 ()
План был красить ячейки за 200-400 км до того что в В

В этом случае ту же формулу надо изменить вот так:
Код
=AND(R2-Q2<=400; Q2<=R2; Q2<>""; R2<>"")


Мой tip box - яд 41001663842605
 
Ответить
СообщениеOK, cделал в Вашем файле вот так для рыжей закраски:
Код
=AND(R2-Q2<=10%*R2; Q2<=R2; Q2<>""; R2<>"")

Цитата Ефгений, 23.12.2019 в 18:32, в сообщении № 9 ()
План был красить ячейки за 200-400 км до того что в В

В этом случае ту же формулу надо изменить вот так:
Код
=AND(R2-Q2<=400; Q2<=R2; Q2<>""; R2<>"")

Автор - Gustav
Дата добавления - 24.12.2019 в 10:39
Ефгений Дата: Вторник, 24.12.2019, 23:29 | Сообщение № 17
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

2010
В этом случае ту же формулу надо изменить вот так:

Спасибо Вам огромное за терпение. Вы очень, очень мне помогли. Спасибо еще раз.
 
Ответить
Сообщение
В этом случае ту же формулу надо изменить вот так:

Спасибо Вам огромное за терпение. Вы очень, очень мне помогли. Спасибо еще раз.

Автор - Ефгений
Дата добавления - 24.12.2019 в 23:29
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Условное форматирование при условии разницы в числах 10% (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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