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

Вход

Регистрация

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

 

= Мир MS Excel/Подкрашивать несколько столбцов по одной формуле - Мир MS Excel

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

Excel 2013
Добрый день.

Заранее прошу прощения - и себя, и всех запутал! - откорректировал, должно быть правильно!

В двух словах о моём файле:
столбец B - произошел инцидент
столбец C - его зарегистрировали
столбец D - присвоили номер.

Далее идёт рассчёт по следующим параметрам - если инцидент произошёл до 15:00, то зарегистрировать его надо до 17:00 текущего дня, если после 15:00, то до 12:00 следующего дня, но! - если инцидент произошел после 15:00 пятницы, то он должен быть зарегистрирован до 17:00 ближайшего понедельника.
Столбцы Е-S - это вспомогательные столбцы, проще я не сумел ничего придумать, чтоб корректно считало, если знаете легче способ - буду очень признателен, но суть не в этом - если регистрация произошла позже установленных сроков, то таймер (столбец U) подкрашивает "просрочку" в красный цвет. А хочется, все столбцы с E по U скрыть, а подкрашивался чтобы столбец D. Помогите пожалуйста.
К сообщению приложен файл: 9451748.xlsx (14.3 Kb)


Сообщение отредактировал ЩтуКув - Вторник, 05.01.2021, 11:35
 
Ответить
СообщениеДобрый день.

Заранее прошу прощения - и себя, и всех запутал! - откорректировал, должно быть правильно!

В двух словах о моём файле:
столбец B - произошел инцидент
столбец C - его зарегистрировали
столбец D - присвоили номер.

Далее идёт рассчёт по следующим параметрам - если инцидент произошёл до 15:00, то зарегистрировать его надо до 17:00 текущего дня, если после 15:00, то до 12:00 следующего дня, но! - если инцидент произошел после 15:00 пятницы, то он должен быть зарегистрирован до 17:00 ближайшего понедельника.
Столбцы Е-S - это вспомогательные столбцы, проще я не сумел ничего придумать, чтоб корректно считало, если знаете легче способ - буду очень признателен, но суть не в этом - если регистрация произошла позже установленных сроков, то таймер (столбец U) подкрашивает "просрочку" в красный цвет. А хочется, все столбцы с E по U скрыть, а подкрашивался чтобы столбец D. Помогите пожалуйста.

Автор - ЩтуКув
Дата добавления - 04.01.2021 в 23:52
Gustav Дата: Вторник, 05.01.2021, 04:58 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2695
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Вначале рисуем формулу для вычисления допустимого времени регистрации (помещаем её в ячейку E2):
Код
=ОТБР(B2)+ГПР(ДЕНЬНЕД(B2;2)*100+ЧАС(B2);{100;115;200;215;300;315;400;415;500;515;600;700:17;36;17;36;17;36;17;36;17;89;65;41};2)/24

Получилось длинно, зато независимо. В файле есть также версия со ссылкой на диапазон (наверное, более привычного вида):
Код
=ОТБР(B2)+ГПР(ДЕНЬНЕД(B2;2)*100+ЧАС(B2);Лист1!$Q$18:$AB$19;2)/24

Поскольку служебная таблица для ГПР получилась не очень большой (12 пар значений):
[vba]
Код
100    115    200    215    300    315    400    415    500    515    600    700
17    36    17    36    17    36    17    36    17    89    65    41
[/vba]
, я этим и ограничился (т.е. продемонстрировал истинно инженерный подход) и не полез в выяснение какой-либо регулярности/периодичности цифр, которую наверняка можно описать более элегантной формулой (при подходе математическом).

Далее - условное форматирование диапазона =$C$2:$C$11 с использованием формулы:
Код
=--C2>--E2
К сообщению приложен файл: SchtuKuv_03.xlsx (17.7 Kb)


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеВначале рисуем формулу для вычисления допустимого времени регистрации (помещаем её в ячейку E2):
Код
=ОТБР(B2)+ГПР(ДЕНЬНЕД(B2;2)*100+ЧАС(B2);{100;115;200;215;300;315;400;415;500;515;600;700:17;36;17;36;17;36;17;36;17;89;65;41};2)/24

Получилось длинно, зато независимо. В файле есть также версия со ссылкой на диапазон (наверное, более привычного вида):
Код
=ОТБР(B2)+ГПР(ДЕНЬНЕД(B2;2)*100+ЧАС(B2);Лист1!$Q$18:$AB$19;2)/24

Поскольку служебная таблица для ГПР получилась не очень большой (12 пар значений):
[vba]
Код
100    115    200    215    300    315    400    415    500    515    600    700
17    36    17    36    17    36    17    36    17    89    65    41
[/vba]
, я этим и ограничился (т.е. продемонстрировал истинно инженерный подход) и не полез в выяснение какой-либо регулярности/периодичности цифр, которую наверняка можно описать более элегантной формулой (при подходе математическом).

Далее - условное форматирование диапазона =$C$2:$C$11 с использованием формулы:
Код
=--C2>--E2

Автор - Gustav
Дата добавления - 05.01.2021 в 04:58
bmv98rus Дата: Вторник, 05.01.2021, 10:27 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Может так
Код
=IF(AND(MOD(B1;1)<--"15:00";WEEKDAY(B1;2)<6);INT(B1)+"17:00";WORKDAY.INTL(B1;1;2)+IF(WEEKDAY(B1;2)>4;"17:00";"12:00"))<--C1
К сообщению приложен файл: example2040.xlsx (14.4 Kb)


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

Сообщение отредактировал bmv98rus - Вторник, 05.01.2021, 11:39
 
Ответить
СообщениеМожет так
Код
=IF(AND(MOD(B1;1)<--"15:00";WEEKDAY(B1;2)<6);INT(B1)+"17:00";WORKDAY.INTL(B1;1;2)+IF(WEEKDAY(B1;2)>4;"17:00";"12:00"))<--C1

Автор - bmv98rus
Дата добавления - 05.01.2021 в 10:27
ЩтуКув Дата: Вторник, 05.01.2021, 11:05 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 134
Репутация: 7 ±
Замечаний: 0% ±

Excel 2013
Gustav, Вы крутой! Несколько уточняющих вопросов, пока я разбираюсь в ваших формулах:
1. в первой вашей формуле выделяется "700:17" - это опечатка и там должно быть "700;17" (т.е. точка с запятой вместо двоеточия) или там всё верно?
2. при использовании вашей формулы мои рассчёты не участвуют и можно смело удалить мои вспомогательные столбцы?
3. условное форматирование настроено на столбец со временем (в вашем варианте столбец С), а я ищу вариант с подкраской именно столбца номером инцидента (столбец D), такое возможно?


Сообщение отредактировал ЩтуКув - Вторник, 05.01.2021, 11:11
 
Ответить
СообщениеGustav, Вы крутой! Несколько уточняющих вопросов, пока я разбираюсь в ваших формулах:
1. в первой вашей формуле выделяется "700:17" - это опечатка и там должно быть "700;17" (т.е. точка с запятой вместо двоеточия) или там всё верно?
2. при использовании вашей формулы мои рассчёты не участвуют и можно смело удалить мои вспомогательные столбцы?
3. условное форматирование настроено на столбец со временем (в вашем варианте столбец С), а я ищу вариант с подкраской именно столбца номером инцидента (столбец D), такое возможно?

Автор - ЩтуКув
Дата добавления - 05.01.2021 в 11:05
ЩтуКув Дата: Вторник, 05.01.2021, 11:12 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 134
Репутация: 7 ±
Замечаний: 0% ±

Excel 2013
bmv98rus, спасибо, но не на тот столбец условку расположили!
 
Ответить
Сообщениеbmv98rus, спасибо, но не на тот столбец условку расположили!

Автор - ЩтуКув
Дата добавления - 05.01.2021 в 11:12
bmv98rus Дата: Вторник, 05.01.2021, 11:27 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
но не на тот столбец условку расположили!

а подкрашивался чтобы столбец C.
Я лично С:C взял.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
но не на тот столбец условку расположили!

а подкрашивался чтобы столбец C.
Я лично С:C взял.

Автор - bmv98rus
Дата добавления - 05.01.2021 в 11:27
ЩтуКув Дата: Вторник, 05.01.2021, 11:32 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 134
Репутация: 7 ±
Замечаний: 0% ±

Excel 2013
bmv98rus, блин виноват, не тот столбец указал. Сначала описал что хочу, потом в файл добавил первый столбец с порядковым номером и всё сдвинулось. Протупил...
Можете помочь с подкраской столбца D?


Сообщение отредактировал ЩтуКув - Вторник, 05.01.2021, 11:37
 
Ответить
Сообщениеbmv98rus, блин виноват, не тот столбец указал. Сначала описал что хочу, потом в файл добавил первый столбец с порядковым номером и всё сдвинулось. Протупил...
Можете помочь с подкраской столбца D?

Автор - ЩтуКув
Дата добавления - 05.01.2021 в 11:32
bmv98rus Дата: Вторник, 05.01.2021, 11:40 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Можете помочь с подкраской столбца D?
так откройте УФи там измените диапазон применения.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Можете помочь с подкраской столбца D?
так откройте УФи там измените диапазон применения.

Автор - bmv98rus
Дата добавления - 05.01.2021 в 11:40
Gustav Дата: Вторник, 05.01.2021, 18:46 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2695
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Несколько уточняющих вопросов

ЩтуКув, ну, наверное, Вы уже со всем разобрались самостоятельно. Тем не менее, несколько комментариев дам.

Ваши вопросы 1) и 2) легко проверить самому. Достаточно сделать копию своего файла для экспериментов и попробовать:
1) заменить двоеточие на точку с запятой и увидеть, что формула перестанет работать - появится ошибка #ССЫЛКА! - значит, всё было неслучайно!

2) просто удалить ваши вспомогательные столбцы и увидеть, что формула от этого НЕ перестанет работать. В принципе достаточно и просто внимательно взглянуть на нее и понять, что она зависит только от одной ячейки в колонке B.

Ещё комментарии к вопросам 1) и 3):
1) массив для функции ГПР содержит 2 строки и 12 колонок, т.е. является "горизонтальным" (в моем сообщении №2 помимо первой формулы, он также схематично представлен в тэгах кода). Разделителем строк в данном случае как раз и является двоеточие (:), а точки с запятой (;) разделяют элементы (столбцы) внутри строки. Внутри массива констант {...} сначала перечисляются элементы первой строки, а затем - после двоеточия - элементы второй строки.

3) в формуле УФ, если захотите изменить диапазон применения или если этот диапазон будет включать несколько столбцов - следует сделать ссылки на столбцы абсолютными, т.е. добавить знаки доллара ($) перед буквами столбцов:
Код
=--$C2>--$E2


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Несколько уточняющих вопросов

ЩтуКув, ну, наверное, Вы уже со всем разобрались самостоятельно. Тем не менее, несколько комментариев дам.

Ваши вопросы 1) и 2) легко проверить самому. Достаточно сделать копию своего файла для экспериментов и попробовать:
1) заменить двоеточие на точку с запятой и увидеть, что формула перестанет работать - появится ошибка #ССЫЛКА! - значит, всё было неслучайно!

2) просто удалить ваши вспомогательные столбцы и увидеть, что формула от этого НЕ перестанет работать. В принципе достаточно и просто внимательно взглянуть на нее и понять, что она зависит только от одной ячейки в колонке B.

Ещё комментарии к вопросам 1) и 3):
1) массив для функции ГПР содержит 2 строки и 12 колонок, т.е. является "горизонтальным" (в моем сообщении №2 помимо первой формулы, он также схематично представлен в тэгах кода). Разделителем строк в данном случае как раз и является двоеточие (:), а точки с запятой (;) разделяют элементы (столбцы) внутри строки. Внутри массива констант {...} сначала перечисляются элементы первой строки, а затем - после двоеточия - элементы второй строки.

3) в формуле УФ, если захотите изменить диапазон применения или если этот диапазон будет включать несколько столбцов - следует сделать ссылки на столбцы абсолютными, т.е. добавить знаки доллара ($) перед буквами столбцов:
Код
=--$C2>--$E2

Автор - Gustav
Дата добавления - 05.01.2021 в 18:46
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подкрашивать несколько столбцов по одной формуле (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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