Блог

Я создаю электронную таблицу Excel для еженедельного расписания занятий. Есть 3 столбца, в которых люди вводят расписание в следующем формате: «[день] [время начала] [время окончания]»

Пример:

В этом примере я хочу, чтобы Excel выделил строки 1 и 3, потому что они перекрываются по времени в понедельник

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

До сих пор я мог выделить только совпадения во времени, но не рассматривал день. Я использовал приведенную ниже формулу продукта:

 =SUMPRODUCT((M15<$N$15:$N$26)*(N15>$M$15:$M$26))>1
 

Где M15-время начала, а N15-время окончания. Диапазон столбцов N относится ко всем временам окончания, а диапазон столбцов M относится ко всем временам начала

Насколько это возможно, я хотел бы использовать формулы Excel и избегать любых сценариев VB. Но если это невозможно только с помощью формул, я открыт для предложений. Спасибо!

Ссылка/скриншоты см.:

Вне условного форматирования

 =(B2:B7<=G2)*(C2:C7>=G1)*(A2:A7=G3)
 

В графическом интерфейсе условного форматирования:

Шаг 1 — построение формулы:

 =($B2<=$G$2)*($C2>=$G$1)*($A2=$G$3)
 

Шаг 2 — применение в желаемом диапазоне:

Простой шаг:

Примечания

• Обратите внимание — sumproduct является излишним/ненужным (я нахожу, что это, как правило, так, и sum часто работает так же хорошо, как sumproduct, как «на один уровень выше» от подхода/функций, которые я представил здесь)
• Если наличие общей формулы как для «вне», так и для «внутри» рамок условного формата имеет центральное значение для цели, то используйте формулу «внутри» в обоих случаях (и просто перетащите вниз по желаемому диапазону «снаружи»)
• Ключевые различия (вне и внутри условного форматирования):
• Снаружи: формула охватывает как «условие», так и «диапазон», к которому она применяется (таким образом, используя массив, нет необходимости «исправлять» ссылки на ячейки с помощью «маркера», т. е. знаков$)
• Внутри: здесь формула, построенная для определенной ячейки (обычно удобно делать это, используя верхний левый по отношению к смежному блоку, к которому она должна быть применена); второй шаг «применяет» это условие в указанном диапазоне. Последствия включают в себя: массивы обычно не фигурируют в «шаге» построения формулы; и маркированные ссылки могут иметь значение для успешного «применения» на следующем шаге
• Бонус: хотя вы уже упоминали, что у вас есть таблица «конверсии», ссылки на названия / метки дней (т. Е. M = Mon, S=Sat и т. Д.), Посмотрите на подход, который я использовал, Который определяет этот список в одной ячейке (при условии, что расписание упорядочено соответствующим образом, это может быть полезно для этого/связанных приложений)
• =»(«amp;ВЫБРАТЬ(СОВПАДЕНИЕ(G3,A2:A7,0),»Пн»,»Вт»,»Ср»,»Чт»,»Пт»,»Сб»)amp;»)»

Вы могли бы использовать это неумело

массив-формула, которая должна быть подтверждена с помощьюCTRLSHIFTENTER .

 =SUMPRODUCT((MMULT((LEN(SUBSTITUTE($L$15:$L$24,"Th","H"))-LEN(SUBSTITUTE(SUBSTITUTE($L$15:$L$24,"Th","H"),$K$1:$P$1,"")))*(ROW($K$15:$K$24)<>ROW()),TRANSPOSE(LEN(SUBSTITUTE(L15,"Th","H"))-LEN(SUBSTITUTE(SUBSTITUTE(L15,"Th","H"),$K$1:$P$1,"")))))*(N15>$M$15:$M$24)*(M15<$N$15:$N$24))>0
 

И вам в настоящее время нужно поместить M T W H F S в ячейки K1 , чтобы P1 .

Его нельзя использовать для условного форматирования, так как это формула массива, но вы можете использовать условное форматирование в результатах формулы.