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

Вход

Регистрация

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

 

= Мир MS Excel/Как подтянуть цвет фона ячейки в другую ячейку? - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как подтянуть цвет фона ячейки в другую ячейку? (Формулы/Formulas)
Как подтянуть цвет фона ячейки в другую ячейку?
KIMVSR Дата: Вторник, 19.03.2019, 23:38 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 118
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Здравствуйте,

Подскажите, пожалуйста, как можно сделать такую хитрую штуку:

Определённым номерам соответствуют определённые цвета ячеек, которые я назначил через Условное Форматирование в Таблице #01.
Рядом сводная Таблица #02, где эти же самые номера повторяются множество раз, по несколько штук в каждой строке.

Каким образом я могу подтянуть цвет ячеек из Таблицы #01 на все номера из Таблицы #02?



В результате должно получиться примерно следующее (на примере номеров 1111 и 9999):



Желательно без макросов, если возможно.

Заранее большое спасибо!
К сообщению приложен файл: kimvsr_example_.xlsm (10.4 Kb)


Сообщение отредактировал KIMVSR - Вторник, 19.03.2019, 23:41
 
Ответить
СообщениеЗдравствуйте,

Подскажите, пожалуйста, как можно сделать такую хитрую штуку:

Определённым номерам соответствуют определённые цвета ячеек, которые я назначил через Условное Форматирование в Таблице #01.
Рядом сводная Таблица #02, где эти же самые номера повторяются множество раз, по несколько штук в каждой строке.

Каким образом я могу подтянуть цвет ячеек из Таблицы #01 на все номера из Таблицы #02?



В результате должно получиться примерно следующее (на примере номеров 1111 и 9999):



Желательно без макросов, если возможно.

Заранее большое спасибо!
Автор - KIMVSR
Дата добавления - 19.03.2019 в 23:38
_Boroda_ Дата: Вторник, 19.03.2019, 23:59 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 16790
Репутация: 6557 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Добрый вечер!
Перепишите правила УФ
Сделал для зеленых
Код
=(E4>=80)*(E4<=89)+ЕСЛИОШИБКА((ВПР(E4;$B$4:$E$12;4;)>=80)*(ВПР(E4;$B$4:$E$12;4;)<=89);)


И диапазоны УФ конечно поменяйте

* Добавлено
Для всех сделал (файл 888_2)
В красных другая формула (с проверкой на непусто)
К сообщению приложен файл: 4715541.xlsm (10.3 Kb) · 888_2.xlsm (10.4 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеДобрый вечер!
Перепишите правила УФ
Сделал для зеленых
Код
=(E4>=80)*(E4<=89)+ЕСЛИОШИБКА((ВПР(E4;$B$4:$E$12;4;)>=80)*(ВПР(E4;$B$4:$E$12;4;)<=89);)


И диапазоны УФ конечно поменяйте

* Добавлено
Для всех сделал (файл 888_2)
В красных другая формула (с проверкой на непусто)
Автор - _Boroda_
Дата добавления - 19.03.2019 в 23:59
bmv98rus Дата: Среда, 20.03.2019, 08:06 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4134
Репутация: 770 ±
Замечаний: 0% ±

Excel 2013/2016
_Boroda_, а чего не вместо
Код
=(ВПР(E4;$B$4:$E$12;4;)>=80)*(ВПР(E4;$B$4:$E$12;4;)<=89)

Код
=LOOKUP(VLOOKUP(I6;$B$4:$E$12;4;);{8;9}*10)=80

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение_Boroda_, а чего не вместо
Код
=(ВПР(E4;$B$4:$E$12;4;)>=80)*(ВПР(E4;$B$4:$E$12;4;)<=89)

Код
=LOOKUP(VLOOKUP(I6;$B$4:$E$12;4;);{8;9}*10)=80
Автор - bmv98rus
Дата добавления - 20.03.2019 в 08:06
_Boroda_ Дата: Среда, 20.03.2019, 09:09 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 16790
Репутация: 6557 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
А смысл? Кроме вопроса "А как работает эта формула?", что это нам еще даст? Это во-первых, а во-вторых - ты сам-то пробовал? Попробуй, заодно вспомнишь ограничения УФ :D

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеА смысл? Кроме вопроса "А как работает эта формула?", что это нам еще даст? Это во-первых, а во-вторых - ты сам-то пробовал? Попробуй, заодно вспомнишь ограничения УФ :D
Автор - _Boroda_
Дата добавления - 20.03.2019 в 09:09
bmv98rus Дата: Среда, 20.03.2019, 10:24 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4134
Репутация: 770 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата _Boroda_, 20.03.2019 в 09:09, в сообщении № 4 ()
Попробуй, заодно вспомнишь ограничения УФ

а помню я просто в данном случае в имена
Код
=VLOOKUP(G4;$B$4:$E$12;4;);G4);{0;1;2;3;4;5;6;7;8;9}*10)
и уже результат сравнивать с 0,10,20,30 ... в УФ ж. вот предназначение этого =(E4>=80)*(E4<=89) я совсем не понял, так и зачем у ТС это в УФ Between?

Код
=IF(Sheet1!G4<>"";LOOKUP(IF(Sheet1!G4>89;VLOOKUP(Sheet1!G4;Sheet1!$B$4:$E$12;4;);Sheet1!G4);{0;1;2;3;4;5;6;7;8;9}*10);-1)
К сообщению приложен файл: Copy_of_725.xlsm (19.9 Kb)

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Среда, 20.03.2019, 11:44
 
Ответить
Сообщение
Цитата _Boroda_, 20.03.2019 в 09:09, в сообщении № 4 ()
Попробуй, заодно вспомнишь ограничения УФ

а помню я просто в данном случае в имена
Код
=VLOOKUP(G4;$B$4:$E$12;4;);G4);{0;1;2;3;4;5;6;7;8;9}*10)
и уже результат сравнивать с 0,10,20,30 ... в УФ ж. вот предназначение этого =(E4>=80)*(E4<=89) я совсем не понял, так и зачем у ТС это в УФ Between?

Код
=IF(Sheet1!G4<>"";LOOKUP(IF(Sheet1!G4>89;VLOOKUP(Sheet1!G4;Sheet1!$B$4:$E$12;4;);Sheet1!G4);{0;1;2;3;4;5;6;7;8;9}*10);-1)
Автор - bmv98rus
Дата добавления - 20.03.2019 в 10:24
_Boroda_ Дата: Среда, 20.03.2019, 10:44 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 16790
Репутация: 6557 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Вот так да. Хоть и с именем, но зато правила УФ намного легче пишутся

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеВот так да. Хоть и с именем, но зато правила УФ намного легче пишутся
Автор - _Boroda_
Дата добавления - 20.03.2019 в 10:44
KIMVSR Дата: Среда, 20.03.2019, 13:15 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 118
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Блин, ребят, это очень круто, спасибо огромнейшее - всё работает так, как я хотел!

Если честно, то я не понял даже самой простой логики:

Код
=(E4>=80)*(E4<=89)+ЕСЛИОШИБКА((ВПР(E4;$B$4:$E$12;4;)>=80)*(ВПР(E4;$B$4:$E$12;4;)<=89);)

Можно как-то на словах для чайников, по частям?

Код
=(E4>=80)*(E4<=89)

В нашем случае, (89 больше или равно 80) умножить (89 меньше или равно 89) - что мы хотим сделать этой частью формулы?

Код
+ЕСЛИОШИБКА((ВПР(E4;$B$4:$E$12;4;)>=80)

Цитата
Данная функция возвращает указанное значение, если вычисление по формуле вызывает ошибку; в противном случае функция возвращает результат формулы.

Т.е. если первая часть формулы вызывает ошибку, то мы прибегаем к ВПР, ищем "89" в диапазоне Таблица #01 (а что такое 4? Я знаю 0 и 1, как ЛОЖЬ и ИСТИНА) и больше или равно 80...? Пфф...

Код
*(ВПР(E4;$B$4:$E$12;4;)<=89);)

Ну и тут я окончательно понял, что всё сложно %)

Был бы очень признателен за "словесное" пояснение!


Сообщение отредактировал KIMVSR - Среда, 20.03.2019, 13:16
 
Ответить
СообщениеБлин, ребят, это очень круто, спасибо огромнейшее - всё работает так, как я хотел!

Если честно, то я не понял даже самой простой логики:

Код
=(E4>=80)*(E4<=89)+ЕСЛИОШИБКА((ВПР(E4;$B$4:$E$12;4;)>=80)*(ВПР(E4;$B$4:$E$12;4;)<=89);)

Можно как-то на словах для чайников, по частям?

Код
=(E4>=80)*(E4<=89)

В нашем случае, (89 больше или равно 80) умножить (89 меньше или равно 89) - что мы хотим сделать этой частью формулы?

Код
+ЕСЛИОШИБКА((ВПР(E4;$B$4:$E$12;4;)>=80)

Цитата
Данная функция возвращает указанное значение, если вычисление по формуле вызывает ошибку; в противном случае функция возвращает результат формулы.

Т.е. если первая часть формулы вызывает ошибку, то мы прибегаем к ВПР, ищем "89" в диапазоне Таблица #01 (а что такое 4? Я знаю 0 и 1, как ЛОЖЬ и ИСТИНА) и больше или равно 80...? Пфф...

Код
*(ВПР(E4;$B$4:$E$12;4;)<=89);)

Ну и тут я окончательно понял, что всё сложно %)

Был бы очень признателен за "словесное" пояснение!
Автор - KIMVSR
Дата добавления - 20.03.2019 в 13:15
bmv98rus Дата: Среда, 20.03.2019, 13:27 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4134
Репутация: 770 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата KIMVSR, 20.03.2019 в 13:15, в сообщении № 7 ()
(89 меньше или равно 89) - что мы хотим сделать этой частью формулы?

просто будет или 0 или 1 что будет означать выполнение двух условий или нет ну и соответственно находится ли число в диапазоне
Цитата KIMVSR, 20.03.2019 в 13:15, в сообщении № 7 ()
Т.е. если первая часть формулы вызывает ошибку, то мы прибегаем к ВПР, ищем "89"

нет наоборот, Сперва ВПР и если ошибка в одном из ВПР то возвращаем 0.
Далее предыдущий 0 или 1 складывается с другим 0 и 1 что также может дать 0 или 1. все

Пожалуй №5 пояснять не буду, поберегу Вас, хотя там ничего сложного.

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Цитата KIMVSR, 20.03.2019 в 13:15, в сообщении № 7 ()
(89 меньше или равно 89) - что мы хотим сделать этой частью формулы?

просто будет или 0 или 1 что будет означать выполнение двух условий или нет ну и соответственно находится ли число в диапазоне
Цитата KIMVSR, 20.03.2019 в 13:15, в сообщении № 7 ()
Т.е. если первая часть формулы вызывает ошибку, то мы прибегаем к ВПР, ищем "89"

нет наоборот, Сперва ВПР и если ошибка в одном из ВПР то возвращаем 0.
Далее предыдущий 0 или 1 складывается с другим 0 и 1 что также может дать 0 или 1. все

Пожалуй №5 пояснять не буду, поберегу Вас, хотя там ничего сложного.
Автор - bmv98rus
Дата добавления - 20.03.2019 в 13:27
_Boroda_ Дата: Среда, 20.03.2019, 14:27 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 16790
Репутация: 6557 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Давайте по порядку. УФ отработает в том случае, когда формула, в нем записанная, даст ИСТИНА или ненулевое число
Мы начинаем писать УФ, находясь в ячейке Е4
Кусок (E4>=80)*(E4<=89) даст нам не ноль только в том случае, если оба неравенства выполняются. А это случается, если мы действительно нажобимся в ячейке столбца Е (в Е12)
А когда мы находимся в столбцах G:J, то должны отработать ВПРы. Но у нас же вроде написано ВПР(E4;... - Е4. Да, все верно, мы пишем для ячеек столбца, например, G, находясь при этом в столбце Е (см. второй абзац). Поэтому в формуле мы пишем Е, по представляем себе, что это G :D :D :D
Так вот, формула ВПР(E4;$B$4:$E$12;4;) тогда должна бы быть написана так ВПР(G4;$B$4:$E$12;4;) и вот здесь уже понятно - в диапазоне $B$4:$E$12 ищим ячейку G4, когда нашли - берем четвертый столбец и сверяем его с 80
Аналогично с 89. Если где-то ВПР даст ошибку, то для этого как раз и ЕСЛИОШИБКА написана
Получаем как бы два одинаковых куска (E4>=80)*(E4<=89), но первый для столбюца Е, а второй для столбцов G:J. Отрабатывает или первый, или второй. А потом мы их складываем и перечитываем второе предложение первого абзаца этого поста

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеДавайте по порядку. УФ отработает в том случае, когда формула, в нем записанная, даст ИСТИНА или ненулевое число
Мы начинаем писать УФ, находясь в ячейке Е4
Кусок (E4>=80)*(E4<=89) даст нам не ноль только в том случае, если оба неравенства выполняются. А это случается, если мы действительно нажобимся в ячейке столбца Е (в Е12)
А когда мы находимся в столбцах G:J, то должны отработать ВПРы. Но у нас же вроде написано ВПР(E4;... - Е4. Да, все верно, мы пишем для ячеек столбца, например, G, находясь при этом в столбце Е (см. второй абзац). Поэтому в формуле мы пишем Е, по представляем себе, что это G :D :D :D
Так вот, формула ВПР(E4;$B$4:$E$12;4;) тогда должна бы быть написана так ВПР(G4;$B$4:$E$12;4;) и вот здесь уже понятно - в диапазоне $B$4:$E$12 ищим ячейку G4, когда нашли - берем четвертый столбец и сверяем его с 80
Аналогично с 89. Если где-то ВПР даст ошибку, то для этого как раз и ЕСЛИОШИБКА написана
Получаем как бы два одинаковых куска (E4>=80)*(E4<=89), но первый для столбюца Е, а второй для столбцов G:J. Отрабатывает или первый, или второй. А потом мы их складываем и перечитываем второе предложение первого абзаца этого поста
Автор - _Boroda_
Дата добавления - 20.03.2019 в 14:27
bmv98rus Дата: Среда, 20.03.2019, 14:46 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4134
Репутация: 770 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата _Boroda_, 20.03.2019 в 14:27, в сообщении № 9 ()
Давайте по порядку. УФ отработает в том случае, когда формула, в нем записанная, даст ИСТИНА или ненулевое число
и примечание, что если возвращается ошибка, то это приравнивается к FALSE и если не требуется инфертировать, то обрабатывать эту ошибку не нужно.

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Среда, 20.03.2019, 14:47
 
Ответить
Сообщение
Цитата _Boroda_, 20.03.2019 в 14:27, в сообщении № 9 ()
Давайте по порядку. УФ отработает в том случае, когда формула, в нем записанная, даст ИСТИНА или ненулевое число
и примечание, что если возвращается ошибка, то это приравнивается к FALSE и если не требуется инфертировать, то обрабатывать эту ошибку не нужно.
Автор - bmv98rus
Дата добавления - 20.03.2019 в 14:46
_Boroda_ Дата: Среда, 20.03.2019, 14:49 | Сообщение № 11
Группа: Админы
Ранг: Местный житель
Сообщений: 16790
Репутация: 6557 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Дык ошибка не равна ИСТИНА или ненулевому числу, поэтому УФ с ошибкой не отработает - фраза
Цитата _Boroda_, 20.03.2019 в 14:27, в сообщении № 9 ()
УФ отработает в том случае, когда формула, в нем записанная, даст ИСТИНА или ненулевое число
достаточна и без примечаний

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеДык ошибка не равна ИСТИНА или ненулевому числу, поэтому УФ с ошибкой не отработает - фраза
Цитата _Boroda_, 20.03.2019 в 14:27, в сообщении № 9 ()
УФ отработает в том случае, когда формула, в нем записанная, даст ИСТИНА или ненулевое число
достаточна и без примечаний
Автор - _Boroda_
Дата добавления - 20.03.2019 в 14:49
bmv98rus Дата: Среда, 20.03.2019, 15:46 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4134
Репутация: 770 ±
Замечаний: 0% ±

Excel 2013/2016
Александр, примечание к тому, что ошибку не стоит обрабатывать когда она означат, что условие может не выполняться.

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Среда, 20.03.2019, 17:09
 
Ответить
СообщениеАлександр, примечание к тому, что ошибку не стоит обрабатывать когда она означат, что условие может не выполняться.
Автор - bmv98rus
Дата добавления - 20.03.2019 в 15:46
krosav4ig Дата: Среда, 20.03.2019, 20:28 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 997 ±
Замечаний: 0% ±

Excel 2007,2010,2013
Цитата KIMVSR, 19.03.2019 в 23:38, в сообщении № 1 ()
Желательно без макросов, если возможно.

А чего так? УФ можно делать по UDF (если меня память не ошибает), получив намного больше возможностей, можно задать неограниченное количество критериев
единственное, может работать не очень стабильно

UPD
Вспомнил, как примерно делалось, посмотрел - постоянно отваливается, наверно не зря забыл

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Среда, 20.03.2019, 21:23
 
Ответить
Сообщение
Цитата KIMVSR, 19.03.2019 в 23:38, в сообщении № 1 ()
Желательно без макросов, если возможно.

А чего так? УФ можно делать по UDF (если меня память не ошибает), получив намного больше возможностей, можно задать неограниченное количество критериев
единственное, может работать не очень стабильно

UPD
Вспомнил, как примерно делалось, посмотрел - постоянно отваливается, наверно не зря забыл
Автор - krosav4ig
Дата добавления - 20.03.2019 в 20:28
KIMVSR Дата: Четверг, 21.03.2019, 12:44 | Сообщение № 14
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 118
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
_Boroda_, bmv98rus, большое вам спасибо за объяснение формулы - я потратил 4 часа, чтобы разобраться.
Сегодня пришёл на работу и со свежей головой стал разбирать формулу по частам и вникать...

Получилось перенести логику из примера в свою собственную таблицу:



Единственное, что в моей собственной таблице левая и правая части (Таблица #01 и #02) находились на разных листах.
У меня не получилось указать диапазон УФ сразу на два разных листа - пришлось совместить две части на одном листе.

Я немного погуглил, насколько я понял, такую функциональность Excel не поддерживает.

Огромное вам спасибо за потраченное время!


Сообщение отредактировал KIMVSR - Четверг, 21.03.2019, 12:51
 
Ответить
Сообщение_Boroda_, bmv98rus, большое вам спасибо за объяснение формулы - я потратил 4 часа, чтобы разобраться.
Сегодня пришёл на работу и со свежей головой стал разбирать формулу по частам и вникать...

Получилось перенести логику из примера в свою собственную таблицу:



Единственное, что в моей собственной таблице левая и правая части (Таблица #01 и #02) находились на разных листах.
У меня не получилось указать диапазон УФ сразу на два разных листа - пришлось совместить две части на одном листе.

Я немного погуглил, насколько я понял, такую функциональность Excel не поддерживает.

Огромное вам спасибо за потраченное время!
Автор - KIMVSR
Дата добавления - 21.03.2019 в 12:44
_Boroda_ Дата: Четверг, 21.03.2019, 13:00 | Сообщение № 15
Группа: Админы
Ранг: Местный житель
Сообщений: 16790
Репутация: 6557 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Да, совершенно верно, штатными методами одно УФ сразу не несколько листов сделать не получится

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеДа, совершенно верно, штатными методами одно УФ сразу не несколько листов сделать не получится
Автор - _Boroda_
Дата добавления - 21.03.2019 в 13:00
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как подтянуть цвет фона ячейки в другую ячейку? (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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