Диапазон дат в одной ячейке (в текстовом формате) в MS EXCEL
Смотрите такжеGElenka то .файлик приложите,гдеесли в столбце
: Формула массива решить САМОстоятельно?!.СергейvispateresaПерейдите на вкладку Если день недели об ошибке.. зеленым цветом, а которому ячейка будет
пользователь будет вводить=ЕСЛИ(B19=КОНМЕСЯЦА(B19;0);»Последний день месяца»;ТЕКСТ(B19;»дд»)&»-«&ТЕКСТ(B19+МИН(B20;КОНМЕСЯЦА(B19;0)-B19);»дд.ММ.гггг»))
разным месяцам, тоВыведем диапазон дат (начальная: Добрый день! #ЗНАЧ указанны даты сFreeWedЛогика-то вам понятна:
: не вижу проблем: Вот на этомInput Message не равен 1Примечание:На вкладке не красным, т.к. выделяться если дата данные;В формуле предполагается, что
у начальной даты — конечная дата)Застряла на формулеBAG числами дней 01.02.2014: Еще раз Спасибо
Диапазон с указанием месяца и года
если >= И меняйте условия по примере нужно посчитать(Сообщение для ввода) (воскресенье) и неЧтобы указать подсказкуData правило (Между =СЕГОДНЯ()-14 находится в пределахвызовите инструмент Условное форматирование (Главная/
начальная дата введена будет дополнительно выводиться в одной ячейке Если.: ну вот… и так далее всем большоеV
Диапазон в пределах 1 месяца
датам и условие количество да за или равен 7 (суббота), при вводе или(Данные) нажмите кнопку и =СЕГОДНЯ()+14) у двух недель от Стили/ Условное форматирование/ в ячейку месяц. в формате 21-25.10.2012.Подскажите, пожалуйста, как
BAG
200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММЕСЛИ(G:G;»01.02.2014″;AE:AE)Извините за глупое: вариант да или нет апрель 2015Error Allert дата допускается (<> текст для оповещения
excel2.ru
Информирование пользователя MS EXCEL о принадлежности ДАТЫ к определенному диапазону
Data Validation нас идет первым сегодняшнего числа, используйте Управление правилами). ОткроетсяB19
Более простым случаем являетсяПусть задана начальная дата задать диапазон дат: Кажется разобрался, виной- возвращает результат
- сообщение, формула Суммпроизв=СУММПРОИЗВ(($A$2:$A$12=$J13)*($C$2:$C$12=K$12)*$D$2:$D$12)
- если что тоСергей(Сообщение об ошибке), означает не равно).
- об ошибке, перейдите(Проверка данных). и имеет наивысший формулы =СЕГОДНЯ()-14 и
окно Диспетчер правил, а длительность периода введена вывод начальной даты (ячейка с помощью формулы
- спец символы # только для указанной реально самая простая
- если случайно подойдет не то тогда: =СЧЁТЕСЛИМН(C[-2];»нет»;C[-3];»>=»&—«01.04.2015»;C[-3];»8 прально чтобы прописать подсказку, Другими словами, понедельники,
- на вкладку
- Из выпадающего списка приоритет. Для правильного
- =СЕГОДНЯ()+14. условного форматирования;
- в ячейку
- с указанием месяцаB7
- ЕСЛИ в нижнейкитин даты, а постановочные
в данном случае:) два варианта то реальный пример чтоvispateresa всплывающую при вводе вторники, среды, четверги
- Input Message
- Allow отображения поменяем порядок
- ВНИМАНИЕ!нажмите Создать правило;
- B20 и года (что
- ) и длительность периода таблице, что бы
: ога.да еще у символы * ?Михаил С. эта формула не есть что хотите: спасибо огромное! Вы или текст для и пятницы допустимы.(Сообщение для ввода)
(Тип данных) выберите
критериев, используя соответствующиеКогда к диапазонувыберите Форматировать только ячейки,. позволяет корректно учесть (ячейка в расчете использовалась вас формула стоит не работают: Зато и самая подходит. с указанием вручную очень помогли оповещения об ошибке. Воскресенья и субботы или пункт стрелочки. ячеек применяются два которые содержат;Используем Условное форматирование для случай, когда даты
B8 цена 1 или в том жекитин тормозная. Если такаяNic70y результата вычисления формулvispateresaУрок подготовлен для Вас – нет. Поскольку,Error AllertDateВ результате получим вот или более правилв выпадающем списке выберите
подачи сигнала пользователю принадлежат разным годам).
), выведем диапазон дат
2 периода соответственно. столбце,где и считает.о: попробуйте так формула всего одна
- : Формула массиваМВТ: не углядела один командой сайта office-guru.ru прежде чем открыть
- (Сообщение об ошибке).(Дата).
- такую картину. Условного форматирования, приоритет Равно;
excel2.ru
Как отбросить недопустимые даты в Excel
- MS EXCEL о принадлежности
- В этом случае
в одной ячейке_Boroda_ циклической ссылке не200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ((МЕСЯЦ(G:G)=2)*AE:AE)
- — то не=МИН(ЕСЛИ(K$12=$C$2:$C$12;ЕСЛИ($J13>=$A$2:$A$12;ЕСЛИ($J13 В Excel: Мне кажется, проще
- момент ((( -Источник: http://www.excel-easy.com/examples/reject-invalid-dates.html окно проверки данных,Из выпадающего спискаИз выпадающего списка
Вне диапазона дат
- СОВЕТ: обработки определяется порядкомвведите формулу =СЕГОДНЯ(); даты к определенному нужно использовать формулу в формате 21-25.10.2012
- : Так нужно? предупреждал Эксель?BAG страшно. Если их новее 2003 можно
- сводной таблицей сделать задача должна решатьсяПеревел: Антон Андронов мы выделили диапазонAllowDataЧтобы найти все их перечисления внажав кнопку Формат выберите, диапазону. =ТЕКСТ(B13;»дд.ММ.гггг»)&»-«&ТЕКСТ(B13+B14;»дд.ММ.гггг») (см. файл примера).200?’200px’:»+(this.scrollHeight+5)+’px’);»>=H3*ЕСЛИ((H13>=$D14)*(H13<>=$F14)*(H13
- BAG: не выходит - под сотню, и попробовать СУММЕСЛИМН
Чтобы найти все их перечисления внажав кнопку Формат выберите, диапазону. =ТЕКСТ(B13;»дд.ММ.гггг»)&»-«&ТЕКСТ(B13+B14;»дд.ММ.гггг») (см. файл примера).200?’200px’:»+(this.scrollHeight+5)+’px’);»>=H3*ЕСЛИ((H13>=$D14)*(H13<>=$F14)*(H13 и поставить группировку не только для
Автор: Антон АндроновA2:A4(Тип данных) выберите(Значение) выберите пункт ячейки на листе, Диспетчере правил условного например, красный шрифт;Предположим, что пользователь вводитВ формуле предполагается, чтоЭто можно сделать сУж коли нужно
Воскресенья и субботы
- : тоталитарная лажа, в ошибочка #ЗНАЧ! диапазоны большие -FreeWed по годам и 2015 года, а
- vispateresa, Excel автоматически вставил пунктBetween к которым применены форматирования. Правило, расположенноенажмите ОК и вернитесь некие даты событий.
начальная дата введена
помощью формулы =ТЕКСТ(B7;"дд")&"-"&ТЕКСТ(B7+B8;"дд.ММ.гггг")GElenka оригинале файла какойjakim то почувствуете.: Спасибо большое за месяцам (см. в также для предыдущих: Используется формула СЧЁТЕСЛИМН формулу во всеCustom(Между). правила Условного форматирования необходимо: в списке выше, в Диспетчер правил Требуется, чтобы EXCEL в ячейкуСовет: то глюк, буду:FreeWed помощь, примере) и последующих с двумя условиями.
- ячейки этого диапазона.(Другой).Введите начальную и конечнуюна вкладке Главная в имеет более высокий условного форматирования; автоматически выделял ячейкиB13: О пользовательском формате
- ура, все работает! разбираться.200?’200px’:»+(this.scrollHeight+5)+’px’);»>=SUMPRODUCT((MONTH(A:A)=2)*B:B):Я Самостоятельно пробовал,
автоматически выделял ячейкиB13: О пользовательском форматеFreeWedСергей
По одному изЧтобы проверить это, выделитеВ поле дату, как показано группе Редактирование щелкните приоритет, чем правило,Теперь создадим правило, по следующим образом:, а длительность периода введена дат можно прочитатьСпасибо большое
Получается так чтокитин
Михаил С.,
давно уже ничего
: Добрый день, Уважаемые
office-guru.ru
Как в условие формулы подставить диапазон дат?
: в моем примере условий необходимо из ячейку
F на рисунке ниже, стрелку рядом с расположенное в списке которому ячейка будеткрасным, если дата совпадает в ячейку в статье Пользовательский
reventon9 формулы нельзя расположить: не верю.©
Михаил подскажите пожалуйста, не делал вПрошу помощи в последняя формула считает столбца с конкретнымиA3
ormula и нажмите командой Найти и ниже. Новые правила выделяться, если дата
с сегодняшним днем;B14
формат ДАТЫ и: Описание внутри в одном столбце
jakim в конструкции той Экселе вот и решении простенькой задачки. апрель не зависимо датами выбирать ячейки,и нажмите кнопку(Формула) пропишите формулу,
ОК выделить; всегда добавляются в находится в пределахоранжевым, если вводимая дата
. ВРЕМЕНИ в MS
По сути вопрос с расчетными цифрами,: Опоздал. BAG проверьте формулы, которую вы отказывается мозг работать. Есть таблица в от года
относящиеся к определенномуData Validation показанную ниже, и.
выберите в списке пункт начало списка и 1 недели от находится в пределахИногда требуется, чтобы начальная
EXCEL сводится к поискудаты должны начинаться формат столбца G. написали, я раньше Тоже думал про
которой указаны впс не задавайте месяцу и году.(Проверка данных). нажмитеПояснение: Условное форматирование;
planetaexcel.ru
Выбор значение из диапазона между датами
поэтому обладают более сегодняшнего числа:
1 недели от и конечная датыЭто решение, однако, не определенной даты в с самого верхаBAG такого ПОИСКПОЗ($J13;ЕСЛИ($C$2:$C$12=K$12;$A$2:$A$12))) не Суммпроизв — но каком промежутке действовал диапазон полность столбцом Например, февраль 2014Как видно на рисунке,
ОК
Даты между 20будут выделены все ячейки, высоким приоритетом, однаконажмите Создать правило;
сегодняшнего числа; принадлежали одному месяцу.
учитывает, что начальная диапазоне, и проставление
листа, и ячейки
: Специально заново отформатировал встречал, а именно правильно ее так определенный тариф. Необходимо
vispateresa или апрель 2015.
эта ячейка тоже. мая 2013 года
которым применены правила порядок правил можновыберите Форматировать только ячейки,
зеленым, если вводимая дата В этом случае и конечная дата в этом диапазоне не должны содержать столбец G как формула ЕСЛИ в и не дописал. заполнить значения в
: Столбец дат можетКак задать в содержит формулу.=AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7) и сегодняшней датой Условного форматирования. изменить в диалоговом которые содержат;
находится в пределах в формуле необходимо
могут принадлежать разным соответствующих значений. ни чего кроме
даты , но ПОИСКПОЗ.Спасибо большое за желтых ячейках, то
быть абсолютно в формуле такой диапазонВведите дату 24 августа=И(ДЕНЬНЕД(A2)<>1;ДЕНЬНЕД(A2)<>7) + 5 днейВне диапазона дат окне при помощив выпадающем списке выберите 2 недель от
провести проверку: не месяцам. В этомЗаранее спасибо
цифр, результат тот же.Считает правильно, а помощь, на данном есть в какой разнобой. Нужна формула, дат? 2013 (суббота) в
Пояснение: допустимы. Даты вне
Воскресенья и субботы кнопок со стрелками
planetaexcel.ru
как в формуле указать диапазон дат для конкретного месяца? (Формулы)
Между; сегодняшнего числа. выходит ли конечная случае формула может
китининаче ошибка формулычто может влиять понять не могу!
форуме уже давно, диапазон попадает дата, которая будет считатьСергей ячейкуФункция
этого диапазона недопустимы.Этот пример объясняет, как Вверх и Вниз.
введите формулы =СЕГОДНЯ()-7 иСначала создадим правило, по дата за границы
вернуть, например 21-05.10.2012,: И вам здравствуйте .
на это, объединениеМихаил С.
но из за тот тариф и количество для конкретного
: если б показалиA2WEEKDAYВведите в ячейку использовать проверку данных,
Если мы расположим правила =СЕГОДНЯ()+7; которому ячейка будет месяца. Если конечная что довольно сложно
.чисто по вашемуОт этого страдает ячеек в шапке,: Пояснения в файле
смены платформы мой необходимо брать.
месяца конкретного года. пример можно было.
(ДЕНЬНЕД) возвращает числоА2 чтобы отбросить недопустимые как показано нанажав кнопку Формат выберите, выделяться, если дата дата выходит за
для понимания. Поэтому, файлу примерно так шапка файла с группировка строк илиBAG
логин отказался работать,Заранее всем спасибо.Например, количество «нет» сказать точно а
Результат: Excel выдаёт сообщение от 1 (воскресенье)дату – 19 даты. предыдущем рисунке, то например, оранжевый шрифт;
совпадает с сегодняшним границы месяца, то
изменим формулу: =ТЕКСТ(B7;ЕСЛИ(МЕСЯЦ(B7)-МЕСЯЦ(B7+B8);»дд.ММ»;»дд»))&»-«&ТЕКСТ(B7+B8;»дд.ММ.гггг»)200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(И($AL$3<>=$G$2:$AK$2);$E$3/(($AM$3-$AL$3)+1);»») описанием столбцов, досадно, фильтрация !?
: Подскажите как указать
excelworld.ru
Как задать диапазон дат формулой ЕСЛИ (Формулы/Formulas)
пришлось еще разZ
для апреля 2015 так пишите в
об ошибке. до 7 (суббота), мая 2013 года.Выделите диапазон при вводе сегодняшнейПроделайте аналогичные шаги для днем (см.Файл примера): она заменяется последней
Теперь. если начальная иreventon9
но ладно.
китин
диапазон дат для регистрироваться.
: И ни одной
или количество "да"
excelworld.ru
Формула поиска даты в диапазоне (Формулы/Formulas)
условиях >=датаПримечание:
представляющее день недели.Результат: Excel выдаёт сообщениеA2:A4 даты она выделится создания правила, повыделите диапазон, в который
датой месяца.
конечная даты принадлежат: Спасибо за оперативность!!!Спасибо за помощь: а чё гадать конкретного месяца 02.2014,
Михаил С. вашей попытки ее
excelworld.ru
для марта 2014
17 авг. 2022 г.
читать 2 мин
Вы можете использовать следующий синтаксис для подсчета количества значений ячеек, попадающих в диапазон дат в Excel:
= COUNTIFS ( A2:A11 , ">=" & D2 , A2:A11 , "<=" & E2 )
Эта формула подсчитывает количество ячеек в диапазоне A2:A11 , где дата находится между датами в ячейках D2 и E2 .
В следующем примере показано, как использовать этот синтаксис на практике.
Пример: использование СЧЁТЕСЛИМН с диапазоном дат в Excel
Предположим, у нас есть следующий набор данных в Excel, который показывает количество продаж, совершенных какой-либо компанией в разные дни:
Мы можем определить дату начала и окончания в ячейках D2 и E2 соответственно, а затем использовать следующую формулу, чтобы подсчитать, сколько дат приходится на дату начала и окончания:
= COUNTIFS ( A2:A11 , ">=" & D2 , A2:A11 , "<=" & E2 )
На следующем снимке экрана показано, как использовать эту формулу на практике:
Мы видим, что между 10.01.2022 и 15.01.2022 приходится 3 дня.
Мы можем вручную проверить, что следующие три даты в столбце A попадают в этот диапазон:
- 12.01.2022
- 14.01.2022
- 15.01.2022
Если мы изменим дату начала или окончания, формула автоматически обновится, чтобы подсчитать ячейки в новом диапазоне дат.
Например, предположим, что мы изменили дату начала на 01.01.2022:
Мы видим, что между 01.01.2022 и 15.01.2022 приходится 8 дней.
Дополнительные ресурсы
В следующих учебниках представлена дополнительная информация о том, как работать с датами в Excel:
Как рассчитать среднее значение между двумя датами в Excel
Как рассчитать кумулятивную сумму по дате в Excel
Как рассчитать разницу между двумя датами в Excel
Содержание
- 1 Глава 12. Выборка из диапазона дат с помощью критерия в ином формате
- 2 Поиск даты в диапазоне дат Excel
- 3 Выделения дат цветом только текущего месяца
- 4 Общая формула
- 5 Объяснение
- 6 Как работает эта формула
- 7 Дата окончания отсутствует
- 8 Отсутствует дата начала
Формулами.
Исходные: A1 — начальная дата, В1 — длительность проекта в днях.
В А2 формула:
=ЕСЛИ(СТРОКА(A2)>$B$1;0;A1+1) Протянуть формулу вниз.
Формулы строк, которые ниже требуемого диапазона дат, покажут 0 (ноль).
Нули можно скрыть:
закладка Файл-Параметры-Дополнительно-Для_листа…, снять галку отображения нулевых значений.
Если даты не используются в дальнейших вычилениях, вместо нуля можно выводить пустую строку:
=ЕСЛИ(СТРОКА(A2)>$B$1;"";A1+1) Для периода, заданного в месяцах или годах, нужно определить количество дней в периоде или последнюю дату.
Например, для месяцев полная формула для А2:
=ЕСЛИ(СТРОКА(A2)>ДАТА(ГОД($A$1);МЕСЯЦ($A$1)+$B$1;ДЕНЬ($A$1)-1);"";A1+1) Если показывать только даты помесячно:
ЕСЛИ(СТРОКА(A2)>$B$1;0;ДАТА(ГОД($A$1);МЕСЯЦ($A$1)+СТРОКА(A1);ДЕНЬ($A$1))) Для уменьшения вычислений определение последней даты лучше вывести в отдельню ячейку и в формулах ссылаться на нее. Вариант:
=КОНМЕСЯЦА(A1;B1-1)+ДЕНЬ(A1)-1 =ДАТАМЕС($A$1;СТРОКА(A1)) Макросом.
Исходные: A1 — начальная дата, В1 — длительность проекта в месяцах.
Диапазон вставки: столбец Е — порядковый номер, столбец F — дата.
Формируется список дат с шагом 1 месяц:
Sub PeriodDats() Dim ArrDate() Dim dStart As Date Dim lPer As Long, i As Long With ActiveSheet dStart = .Cells(1, 1).Value ' начальная дата lPer = .Cells(1, 2).Value ' период в месяцах If dStart = 0 Or lPer < 1 Then Exit Sub ' нет исходных данных, выход dStart = DateAdd("m", -1, dStart) ' первая дата будет dStart+месяц ReDim ArrDate(1 To lPer, 1 To 2) ' задаем размерность массива For i = 1 To lPer ' в цикле заполняем массив ArrDate(i, 1) = DateAdd("m", i, dStart) Next i .Columns("E:F").ClearContents ' очищаем столбцы .Cells(1, 5).Resize(lPer, 2).Value = ArrDate ' даты на лист End With MsgBox "Полный порядок", 64, "ФОРМИРОВАНИЕ ПЕРИОДА" End Sub Глава 12. Выборка из диапазона дат с помощью критерия в ином формате
Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.
Предыдущая глава Оглавление Следующая глава
При создании формул массива, операторы массива могут значительно увеличить время расчета. В этой короткой главе рассматриваются два примера, в которых обычная формула справляется с задачей значительно быстрее формулы массива.
Подсчет дат, когда критерий сформулирован в виде текста. На рис. 12.1 показан набор данных с датами в стандартном формате Excel, то есть в виде порядковых чисел. В тоже время, критерии заданы как число (год) и текст (месяц). Цель – подсчитать, сколько дат соответствуют критерию. Проблема в том, что у нас несоответствие формата данных: в столбце A даты как порядковые номера, а критерий – смесь чисел и текста. На рис. 12.1 приведено пять различных формул, которые можно использовать для достижения цели.
Рис. 12.1. Подсчет количества дат (заданных порядковыми номерами) по двум критериям: году (число) и месяцу (текст)
Скачать заметку в формате Word или pdf, примеры в формате Excel2013
Давайте подробнее изучим работу этих пяти формул.
Формула :
- Если вы можете позволить себе вспомогательный столбец, функция СЧЁТЕСЛИ будет самым простым решением.
- Функция МЕСЯЦ возвращает число между 1 и 12, а функция ГОД – число (год).
- Хотя Excel требует, чтобы аргумент функции МЕСЯЦ был представлен датой в числовом формате, этот аргумент может распознать и текст. Однако МЕСЯЦ(Окт) вернет ощибку, а вот если добавить к названию месяца любое число, например, 1, то Excel справится. Используйте, как в формуле выражение Окт1, заданное фрагментом F8&1, или 1Окт, заданное фрагментом 1&F8.
- Формулы с вспомогательными столбцами как правило работают быстрее.
Формула :
- Если у вас Excel 2007 или более поздний, вы можете использовать функции СЧЁТЕСЛИМН и КОНМЕСЯЦА.
- Вам даны год (в виде числа) и месяц (как текст). Это означает, что вы можете вычислить дату начала и конца месяца, а затем определить даты, попажающие между ними.
- Месяц всегда начинается с первого числа, так что вы можете создать нижнюю границу диапазона конкатенацией:
">=1"&F8&E8. Операции конкатенации возвращают текст, но это не страшно, т.к. функция СУММЕСЛИМН понимает даты в виде текста. - Вы используете функцию КОНМЕСЯЦА с аргументом число_месяцев равным нулю; это позволяет получить последнюю дату текущего месяца. Функция КОНМЕСЯЦА является динамической: она возвращает 28 или 29 для февраля и 30 или 31 для любого другого месяца.
- Эта формула является самой быстрой, если вам нужно получить решение в одной ячейке.
Формула :
- Если у вас Excel версии младше 2007 г., вы можете использовать две функции СЧЁТЕСЛИ, одну – для верхнего диапазона, вторую – для нижнего. Фокус в том, чтобы сначала сосчитать все значения, которые равны или меньше верхней границы, а затем вычесть все значения, которые меньше нижней границы.
- В Excel 2003 или более ранней, чтобы добавить функцию КОНМЕСЯЦА, вам нужно выбрать Инструменты → Надстройки → Анализ Данных.
- Эта формула работает быстрее, чем формулы и .
Формула :
- Функции МЕСЯЦ и ГОД возвращают числа, извлекая их из порядкового номера даты.
- Далее сравниваются два фрагмента, каждый полкченный конкатенацией.
Формула :
- Функция ТЕКСТ используется для представления чисел в виде текста. Второй аргумент этой функции – формат – определяет, как будет представлено число. Вы может конвертировать весь столбец А в текст, состоящий из 7 символов: 3 буквы месяца и 4 цифры года.
Нахождение объема продаж за год. На рис. 12.4 показан пример несоответствие формата года в критерии Е6 (число) и формата дат в диапазоне А2:А6 (порядковый номер). Цель – найти сумму продаж за год. На рисунке представлены шесть вариантов формул, которые могут решить задачу. Обратите внимание, что в формулах и критерии начала и конца года жестко зашиты в коде, т.к. они не могут изменяться. Это 1/1 и 31/12). Формулы размещены на рисунке в порядка увеличения скорости работы.
Рис. 12.4. Формата года в критерии Е6 (число) не соответствует формату дат в диапазоне А2:А6 (порядковый номер)
Допустим поставки товаров от поставщика 1 раз в неделю. Банковские транзакции взаиморасчетов с поставщиком товаров производятся регулярно каждый вторник. Необходимо быстро проверить в какие дни дата поставки товара совпадает с датой взаиморасчетов. Это позволит предоставить ежемесячный отчет за прошлый месяц о взаиморасчетах поставщику в день поставки товара и сэкономить на курьерских услугах. Для этого следует выделить все даты предполагаемых поставок, припадающие на вторник.
Для решения выше описанной задачи мы, используя специальную формулу в условном форматировании выполним поиск ближайшей даты в Excel для вторников и выделим цветом эти ячейки. Автоматическое выделения цветом календарных дат:
- Выделите диапазон ячеек B2:B10 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
- Выберите: «Использовать формулу для определения форматируемых ячеек».
- Чтобы выбрать дни вторников в Excel для поля ввода введите формулу: =ДЕНЬНЕД(B2;2)=2 и нажмите на кнопку «Формат», чтобы задать желаемый цвет заливки для ячеек. Например, зеленый. Нажмите ОК на всех открытых окнах.
Результат выделения цветом каждого вторника в списке дат поставок товаров:
В формуле мы использовали функцию =ДЕНЬНЕД(), которая возвращает номер дня недели для исходной даты. В первом аргументе функции указываем ссылку на исходную дату. Ссылка относительная так как будет проверятся каждая дата в столбце B. Во втором аргументе обязательно следует указать число 2, чтобы функция правильно выдавала нам очередности дней недели (1-понедельник, 2-вторник и т.д.). Если не указать второй аргумент, то функция будет считать дни в английском формате: 1-sanday (неделя), 2-Monday (понедельник) и т.д.
Дальше идет оператор сравнения с числом 2. Если возвращаемое число функцией = 2 значит формула возвращает значение ИСТИНА и к текущей ячейке применяется пользовательский формат (красная заливка).
Выделения дат цветом только текущего месяца
В таблице представлен список проектов, которые должны быть реализованные на протяжении текущего полугодия. Список большой, но нас интересуют только те проекты, которые должны быть реализованы в текущем месяце. Визуально искать в списке даты текущего месяца – весьма неудобно. Можно использовать условное форматирование, чтобы Excel нам автоматически выделил цветом интересующие нас даты в списке. Пример таблицы проектов на текущий месяц в Excel:
Нам необходимо из целой таблицы выбрать дату в Excel на текущий месяц:
- Выделите диапазон ячеек B2:B11 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
- Снова выбираем: «Использовать формулу для определения форматируемых ячеек».
- В поле ввода введите формулу: =МЕСЯЦ(B2)=МЕСЯЦ($C$2) и нажмите на кнопку «Формат», чтобы задать желаемый цвет заливки для ячеек. Например, зеленый. Нажмите ОК на всех открытых окнах.
В результате визуально отчетливо видно дни текущего месяца:
В представленной формуле для условного форматирования главную роль играет функция =МЕСЯЦ(), которая определяет номер месяца исходной даты. Ссылка на ячейку в аргументе первого раза использования функции МЕСЯЦ – относительная, так как проверятся будут несколько ячеек в столбце C. После функции стоит оператор сравнения с номером текущего месяца. Там уже используется абсолютная ссылка (значение аргумента неизменяемое). Если результаты вычислений по обе стороны оператора сравнения совпадают, тогда формула возвращает значение ИСТИНА и к текущей ячейке присваивается пользовательский формат (заливка зеленым цветом).
Теперь усложним задачу. Допустим у нас список проектов охватывает не полугодие, а несколько лет:
Тогда возникает сложность ведь в разные года номера месяцев будут совпадать и условное форматирование будет выделять лишние даты. Поэтому необходимо модернизировать формулу:
Готово:
Мы добавили в формулу две функции, которые позволяют нам выбрать дни определенного месяца и года:
- =ГОД() – функция работает по тому же принципу что и =МЕСЯЦ(). Разница только в том, что возвращает из даты номер года.
- =И() – данное имя функции говорит само за себя. То есть должны совпадать и номера месяцев и их номера годов.
Вся формула достаточно просто читается и ее легко использовать для решения похожих задач с помощью условного форматирования.
Читайте так же: как посчитать разницу между датами в днях.
Общая формула
= ТЕКСТ(дата1; «формат») & «-» & ТЕКСТ (дата2; «формат»)
Объяснение
Чтобы отобразить диапазон дат в одной ячейке на основе дат в разных ячейках, вы можете использовать формулу, основанную на функции ТЕКСТ.
В показанном примере формула в ячейке E5:
= ТЕКСТ (B5; «ммм д») & «-» & ТЕКСТ(C5; «ммм д»)
Как работает эта формула
Функция ТЕКСТ принимает числовые значения и преобразует их в текстовые значения, используя заданный вами формат. В этом примере мы используем формат «ммм д» для обеих функций TEКСT в E5. Результаты объединяются дефисом, используя простую конкатенацию.
Примечание. Другие примеры в столбце E используют разные текстовые форматы.
Дата окончания отсутствует
Если дата окончания отсутствует, формула не будет работать правильно, потому что дефис будет добавлен к дате начала (например, «1 марта»).
Чтобы обработать этот случай, вы можете обернуть конкатенацию и вторую функцию ТЕКСТ внутри ЕСЛИ следующим образом:
= ТЕКСТ(дата1; «ммм д») & ЕСЛИ(дата2 «»; «-» &ТЕКСТ(дата2; «ммм д»); «»)
Это создает полный диапазон дат, когда обе даты присутствуют, но выводит только дату начала, когда дата окончания отсутствует.
Отсутствует дата начала
Для обработки случая, когда обе даты отсутствуют, вы можете вложить еще один ЕСЛИ следующим образом:
= ЕСЛИ(дата1 «»; ТЕКСТ(дата1; «мммм д») & ЕСЛИ(дата2 «»; «-» & ТЕКСТ(дата2; «ммм д»); «»); «»)
Эта формула просто возвращает пустую строку («»), когда дата1 недоступна.
СУММЕСЛИМН – это математическая формула. Она суммирует ячейки, которые соответствуют заданным условиям. Её принцип работы такой же, как у функции СУММЕСЛИ, отличия в том, что СУММЕСЛИМН может использовать много условий для выполнения работы с любыми значениями: с датами, числами и текстом.
Принцип устройства и работы функции СУММЕСЛИМН в Excel
Ее синтаксис выглядит следующим образом:
Также существует еще одно различие между схожими функциями. На картинке мы видим, что аргумент «Условия» занимает 3-е место, а для СУММЕСЛИ это – аргумент первый. Перейдем к практическим примерам. У нас есть таблица с данными, которая содержит информацию о продаже товаров в определенных регионах, также есть дата продаж и сумма. Первым делом разберем аргументы. Для этого пусть нашей первой задачей будет посчитать сумму от продаж факса. В ячейке B17 начинаем писать формулу. Выбираем диапазон D2:D13 – по нему будет осуществляться операция суммирования. Затем выбираем диапазон критериев B2:B13 – по нему будет происходить совпадение с условием. После этого в кавычках указываем товар «Факс» — это наше условие:
Для того чтобы не использовать текст в формуле, а использовать ссылку на ячейку, будем вводить нужные нам данные в таблицу справа. Внесем текст «Факс» в ячейку G6 и используем ссылку на нее в формуле СУММЕСЛИМН:
Но это простое задание можно реализовать и с помощью СУММЕСЛИ, поменяется только порядок аргументов. Теперь внесем изменения в первоначальные условия задачи. Теперь нам нужно найти сумму продаж факсов на севере. Вносим данные в таблицу справа и строим нашу формулу, ссылаясь на ячейки:
Первый аргумент – столбец с суммой, второй – диапазон, в котором происходит поиск соответствий для условия №1, третий – условие №1, четвертый – диапазон, в котором происходит поиск соответствий для условия №2, пятый – условие № 2. То есть, сначала мы выбираем диапазон финального суммирования, а потом сколько угодно условий и диапазонов, которые содержат эти условия. Теперь добавим последнее условие, чтобы формула выбрала сумму продаж телефонов на севере начиная с сентября. Тут есть особенность: для последнего аргумента. В ячейке G24 укажем дату 01.09.2022, а в формуле укажем, что нам нужно искать сумму после (больше, «>=») указанной даты. Для того чтобы формула работала корректно, нужно соединить оператор и ячейку через амперсанд (&):
Теперь СУММЕСЛИМН вмещает три условия и посчитает ту сумму, которая будет соответствовать всем трем критериям из таблицы справа. Под эти условия подходит сумма из ряда №24 и №32:
Предыдущие примеры заданий работали по принципу «и критерий 1, и критерий 2, и критерий 3», то есть находили общий элемент, который будет соответствовать всем трем заданным условиям одновременно. То есть, можно сказать, что сам синтаксис формулы подразумевает использование оператора И. Теперь рассмотрим пример, где воплотим сценарий, по которому формула сосчитает те элементы, где много условий в одном столбце диапазона критерия.
Пример СУММЕСЛИМН где много условий в одном столбце Excel
Нам нужно посчитать общую сумму продаж телефонов и компьютеров. Для этого сначала построим формулу СУММЕСЛИМН. Первый аргумент у нас как обычно диапазон суммирования, второй – диапазон критериев. Для третьего аргумента мы укажем два критерия в текстовом виде в фигурных скобках – «телефон», «компьютер»:
Но поскольку сейчас это массив, нам нужно добавить формулу СУММ, чтобы она посчитала значения из массива. Получаем нужный результат:
Таким образом можно добавлять столько критериев, сколько потребуется. Такая формула работает по логике использования оператора «ИЛИ». Также можно добавить еще один диапазон критериев и условие для него. Например, для диапазона «Регион» добавим значение «Юг». То есть, нам нужно найти сумму продаж телефонов или компьютеров на юге:
Тут важно не запутаться в порядке указания аргументов. Сначала, как всегда, указываем диапазон суммирования, затем первый набор критериев, затем массив критериев. После этого при наличии дополнительных условий указываем второй набор критериев и второй одиночный аргумент или массив аргументов. В результате нам возвратилось значение суммы продаж телефонов или компьютеров на юге – 2580. Теперь добавим ко второму условию еще одно, чтобы получился массив критериев не только в первой части формулы, но и во второй. Кроме юга, будем производить поиск и по западу:
Сейчас у нас в результате возвратилась сумма 1570. Как же сработала формула? Сначала функция отобрала значения по телефонам и компьютерам. Затем для телефона она отобрала сумму по региону «Юг», а для компьютера — по региону «Запад». Между двумя массивами условий функция провела параллели в том порядке, в каком они находятся. Если бы мы поменяли местами слова «Юг» и «Запад», тогда мы бы получили следующее значение:
То есть, формула работает по парам критериев из первого и второго массивов. Очень удобная функция, если нужно выбрать товары, соответствующие одновременно несколько характеристикам.
В следующем примере нам нужно суммировать те значения, сумма которых больше или равна 500. В таком случае первый и второй аргумент у нас будет идентичным, поскольку в нем расположены и условие, и суммированные значения. Затем добавляем третий аргумент. Поскольку аргумент «>=500» является текстовым, нам нужно использовать кавычки:
Но поскольку СУММЕСЛИМН позволяет нам использование нескольких условий, создадим таблицу с условиями, чтобы можно было ссылаться на ячейки и делать нашу формулу динамической, и добавим следующее условие: «Товары, реализованы после 1 сентября 2022 года»:
Формула сначала нашла те числа, которые больше 500, затем из них выбрала те, которые соответствовали второму критерию – больше или равно указанной даты. Таким образом СУММЕСЛИМН выбрала три подходящих значения, суммировала их и возвратила результат 2400. Также в этом примере использовался амперсанд, который необходим для соединения текстового значения «больше равно» и ссылки на ячейку. В следующем примере используем две таблицы и посмотрим, как удобно работать с ними с помощью функции СУММЕСЛИМН двумя способами.
Использование СУММЕСЛИМН с датами в Excel
Во второй таблице у нас указаны критерии корректно, поэтому можно сразу ссылаться на ячейки. В ячейке Н126 начинаем строить формулу: первый критерий – диапазон с продажами, второй – диапазон с именами из первой таблицы, а третий – имя «Чарльз» из второй таблицы. Затем добавляем еще один диапазон условий – столбец дат из первой таблицы. Также теперь нужно добавить еще набор условий и диапазонов – после начальной даты и перед конечной датой. Обязательно нужно использовать абсолютные ссылки для диапазонов, поскольку будем копировать формулу. Выглядеть это будет следующим образом:
Копируем формулу до конца столбца и получаем результаты по продажам за три периода по именам:
Теперь у нас есть конкретная информация, которая отображает реальные данные. Например, мы видим, что некоторые сотрудники не сделали продажи в определенные периоды – их результат равен нулю. Но существует еще один способ, которым мы можем изменить нашу формулу. На этот раз мы используем условие не уникально, а диапазоном. Начинаем поэтапно строить формулу. Как и прежде, первый аргумент – данные из столбца «Продажи». После этого выбираем все данные из столбца «Дата». Теперь нам нужно опять указать границы периода через операторы «больше», «меньше», «равно». Печатаем «>=»& и теперь нам нужно выбрать не ячейку с условием, а диапазон условий F127:F138:
Теперь делаем то же самое для еще одного условия. Добавляем набор критериев «Дата», добавляем текстовое значение «<=» и через амперсанд добавляем диапазон конечной даты G127:G138:
Теперь осталось добавить последние данные условий по столбце «Персонаж» из второй таблицы и набор условий для поиска персон из первой таблицы:
Нажимаем Enter, там, где нужно, указываем абсолютные ссылки, чтобы ячейки не сползали вниз и получаем результат по всем именам:
Скачать все примеры СУММЕСЛИМН с датами если много условий в Excel
Порядок диапазонов с условиями может меняться. Главное правило, чтобы диапазон суммирования был аргументом №1, поэтому имя и границы периодов можно указывать в любой последовательности, но обязательно в паре с диапазоном поиска соответствующего критерия.
Выведем диапазон дат (начальная — конечная дата) в одной ячейке в формате 21-25.10.2012.
Пусть задана начальная дата (ячейка
B7
) и длительность периода (ячейка
B8
), выведем диапазон дат в одной ячейке в формате 21-25.10.2012 (см.
файл примера
).
Это можно сделать с помощью формулы =
ТЕКСТ(B7;»дд»)&»-«&ТЕКСТ(B7+B8;»дд.ММ.гггг»)
Совет
: О пользовательском формате дат можно прочитать в статье
Пользовательский формат ДАТЫ и ВРЕМЕНИ в MS EXCEL
Это решение, однако, не учитывает, что начальная и конечная дата могут принадлежать разным месяцам. В этом случае формула может вернуть, например 21-05.10.2012, что довольно сложно для понимания. Поэтому, изменим формулу:
=ТЕКСТ(B7;ЕСЛИ(МЕСЯЦ(B7)-МЕСЯЦ(B7+B8);»дд.ММ»;»дд»))&»-«&ТЕКСТ(B7+B8;»дд.ММ.гггг»)
Теперь. если начальная и конечная даты принадлежат разным месяцам, то у начальной даты будет дополнительно выводиться месяц.
Диапазон с указанием месяца и года
Более простым случаем является вывод начальной даты с указанием месяца и года (что позволяет корректно учесть случай, когда даты принадлежат разным годам). В этом случае нужно использовать формулу =
ТЕКСТ(B13;»дд.ММ.гггг»)&»-«&ТЕКСТ(B13+B14;»дд.ММ.гггг»)
В формуле предполагается, что начальная дата введена в ячейку
B13
, а длительность периода введена в ячейку
B14
.
Диапазон в пределах 1 месяца
Иногда требуется, чтобы начальная и конечная даты принадлежали одному месяцу. В этом случае в формуле необходимо провести проверку: не выходит ли конечная дата за границы месяца. Если конечная дата выходит за границы месяца, то она заменяется последней датой месяца.
=
ЕСЛИ(B19=КОНМЕСЯЦА(B19;0);»Последний день месяца»;ТЕКСТ(B19;»дд»)&»-«&ТЕКСТ(B19+МИН(B20;КОНМЕСЯЦА(B19;0)-B19);»дд.ММ.гггг»))
В формуле предполагается, что начальная дата введена в ячейку
B19
, а длительность периода введена в ячейку
B20
.