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

Вход

Регистрация

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

 

= Мир MS Excel/Определенный день в ячейке - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Определенный день в ячейке (Формулы/Formulas)
Определенный день в ячейке
Gubanov Дата: Понедельник, 07.12.2015, 09:59 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Здравствуйте.

Помогите вывести формулу.
Требуется чтобы в ячейке отображалась дата первой среды месяца, но если эта дата выпадает на выходной или на праздничный/нерабочий день, то отображать дату следующей среды.
 
Ответить
СообщениеЗдравствуйте.

Помогите вывести формулу.
Требуется чтобы в ячейке отображалась дата первой среды месяца, но если эта дата выпадает на выходной или на праздничный/нерабочий день, то отображать дату следующей среды.
Автор - Gubanov
Дата добавления - 07.12.2015 в 09:59
pabchek Дата: Понедельник, 07.12.2015, 11:07 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
вот такой монстрик у меня получился (формула массива)
Код
=1/МАКС(ЕСЛИ((ДЕНЬНЕД(КОНМЕСЯЦА(H4;-1)+СТРОКА(A$1:A$31);2)=3);1/РАБДЕНЬ(КОНМЕСЯЦА(H4;-1);СТРОКА(A$1:A$31);праздники)))

обшибся я... поправился
Код
=1/МАКС(1/ЕСЛИ(ДЕНЬНЕД(РАБДЕНЬ(КОНМЕСЯЦА(H4;-1);СТРОКА(A$1:A$31);праздники);2)=3;РАБДЕНЬ(КОНМЕСЯЦА(H4;-1);СТРОКА(A$1:A$31);праздники);-1))
К сообщению приложен файл: 3838742.xlsb (8.5 Kb)

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Сообщение отредактировал pabchek - Понедельник, 07.12.2015, 11:26
 
Ответить
Сообщениевот такой монстрик у меня получился (формула массива)
Код
=1/МАКС(ЕСЛИ((ДЕНЬНЕД(КОНМЕСЯЦА(H4;-1)+СТРОКА(A$1:A$31);2)=3);1/РАБДЕНЬ(КОНМЕСЯЦА(H4;-1);СТРОКА(A$1:A$31);праздники)))

обшибся я... поправился
Код
=1/МАКС(1/ЕСЛИ(ДЕНЬНЕД(РАБДЕНЬ(КОНМЕСЯЦА(H4;-1);СТРОКА(A$1:A$31);праздники);2)=3;РАБДЕНЬ(КОНМЕСЯЦА(H4;-1);СТРОКА(A$1:A$31);праздники);-1))
Автор - pabchek
Дата добавления - 07.12.2015 в 11:07
SLAVICK Дата: Понедельник, 07.12.2015, 11:07 | Сообщение № 3
Группа: Модераторы
Ранг: Старожил
Сообщений: 2290
Репутация: 766 ±
Замечаний: 0% ±

2019
Формула массива:
Код
=НАИМЕНЬШИЙ((ЕСЛИ(((ДЕНЬНЕД(ДАТА(ГОД(B3);МЕСЯЦ(B3);СТРОКА(D1:D20)))=4)*ЕОШИБКА(ПОИСКПОЗ(
ДАТА(ГОД(B3);МЕСЯЦ(B3);СТРОКА(D1:D20));H1:H20;0)));
ДАТА(ГОД(B3);МЕСЯЦ(B3);СТРОКА(D1:D20));9^9));1)
К сообщению приложен файл: 7216084.xlsx (9.5 Kb)

Иногда все проще чем кажется с первого взгляда.

Сообщение отредактировал SLAVICK - Понедельник, 07.12.2015, 11:16
 
Ответить
СообщениеФормула массива:
Код
=НАИМЕНЬШИЙ((ЕСЛИ(((ДЕНЬНЕД(ДАТА(ГОД(B3);МЕСЯЦ(B3);СТРОКА(D1:D20)))=4)*ЕОШИБКА(ПОИСКПОЗ(
ДАТА(ГОД(B3);МЕСЯЦ(B3);СТРОКА(D1:D20));H1:H20;0)));
ДАТА(ГОД(B3);МЕСЯЦ(B3);СТРОКА(D1:D20));9^9));1)
Автор - SLAVICK
Дата добавления - 07.12.2015 в 11:07
abtextime Дата: Понедельник, 07.12.2015, 11:16 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 117 ±
Замечаний: 0% ±

Excel 2010
Непонятно, как среда может выпасть на ВЫХОДНОЙ? На праздничный - понятно ... Но выходные вроде как суббота и воскресенье


Сообщение отредактировал abtextime - Понедельник, 07.12.2015, 11:16
 
Ответить
СообщениеНепонятно, как среда может выпасть на ВЫХОДНОЙ? На праздничный - понятно ... Но выходные вроде как суббота и воскресенье
Автор - abtextime
Дата добавления - 07.12.2015 в 11:16
abtextime Дата: Понедельник, 07.12.2015, 11:44 | Сообщение № 5
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 117 ±
Замечаний: 0% ±

Excel 2010
Код
=ЕСЛИ(ЕОШИБКА(ВПР(ДАТА(ГОД(A1);МЕСЯЦ(A1);1)+ЕСЛИ(ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1))<=4;4;11)-ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1));Праздники;1;0));ДАТА(ГОД(A1);МЕСЯЦ(A1);1)+ЕСЛИ(ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1))<=4;4;11)-ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1));ЕСЛИ(ЕОШИБКА(ВПР(ДАТА(ГОД(A1);МЕСЯЦ(A1);1)+ЕСЛИ(ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1))<=4;4;11)-ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1))+7;Праздники;1;0));ДАТА(ГОД(A1);МЕСЯЦ(A1);1)+ЕСЛИ(ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1))<=4;4;11)-ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1))+7;ДАТА(ГОД(A1);МЕСЯЦ(A1);1)+ЕСЛИ(ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1))<=4;4;11)-ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1))+14))


Сообщение отредактировал abtextime - Понедельник, 07.12.2015, 11:47
 
Ответить
Сообщение
Код
=ЕСЛИ(ЕОШИБКА(ВПР(ДАТА(ГОД(A1);МЕСЯЦ(A1);1)+ЕСЛИ(ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1))<=4;4;11)-ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1));Праздники;1;0));ДАТА(ГОД(A1);МЕСЯЦ(A1);1)+ЕСЛИ(ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1))<=4;4;11)-ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1));ЕСЛИ(ЕОШИБКА(ВПР(ДАТА(ГОД(A1);МЕСЯЦ(A1);1)+ЕСЛИ(ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1))<=4;4;11)-ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1))+7;Праздники;1;0));ДАТА(ГОД(A1);МЕСЯЦ(A1);1)+ЕСЛИ(ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1))<=4;4;11)-ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1))+7;ДАТА(ГОД(A1);МЕСЯЦ(A1);1)+ЕСЛИ(ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1))<=4;4;11)-ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1))+14))
Автор - abtextime
Дата добавления - 07.12.2015 в 11:44
Gubanov Дата: Понедельник, 07.12.2015, 11:49 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Не один не обрабатывает точно запрос.
В файле на стр. Настройки есть строка Первая среда месяца.
К сообщению приложен файл: 3837866.xlsm (39.6 Kb)
 
Ответить
СообщениеНе один не обрабатывает точно запрос.
В файле на стр. Настройки есть строка Первая среда месяца.
Автор - Gubanov
Дата добавления - 07.12.2015 в 11:49
TimSha Дата: Понедельник, 07.12.2015, 11:56 | Сообщение № 7
Группа: Проверенные
Ранг: Ветеран
Сообщений: 627
Репутация: 94 ±
Замечаний: 0% ±

Excel 2013 Pro +
Для размышлений и ... применения - Вычисление нужной даты , как вариант от соседей... ;)

"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
Ответить
СообщениеДля размышлений и ... применения - Вычисление нужной даты , как вариант от соседей... ;)
Автор - TimSha
Дата добавления - 07.12.2015 в 11:56
abtextime Дата: Понедельник, 07.12.2015, 12:00 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 117 ±
Замечаний: 0% ±

Excel 2010
Какую именно дату неправильно отрабатывает моя формула? С учетом того, что Праздники - именованный одностолбцовый диапазон с праздничными датами
 
Ответить
СообщениеКакую именно дату неправильно отрабатывает моя формула? С учетом того, что Праздники - именованный одностолбцовый диапазон с праздничными датами
Автор - abtextime
Дата добавления - 07.12.2015 в 12:00
SLAVICK Дата: Понедельник, 07.12.2015, 12:09 | Сообщение № 9
Группа: Модераторы
Ранг: Старожил
Сообщений: 2290
Репутация: 766 ±
Замечаний: 0% ±

2019
Цитата Gubanov, 07.12.2015 в 11:49, в сообщении № 6
Не один не обрабатывает точно запрос.

Вы праздники где указали?
См файл зеленый столбец. Его нужно заполнить датами праздников, или выходными Вашей фирмы.
К сообщению приложен файл: 8296872-1-.xlsm (39.3 Kb)

Иногда все проще чем кажется с первого взгляда.

Сообщение отредактировал SLAVICK - Понедельник, 07.12.2015, 12:14
 
Ответить
Сообщение
Цитата Gubanov, 07.12.2015 в 11:49, в сообщении № 6
Не один не обрабатывает точно запрос.

Вы праздники где указали?
См файл зеленый столбец. Его нужно заполнить датами праздников, или выходными Вашей фирмы.
Автор - SLAVICK
Дата добавления - 07.12.2015 в 12:09
pabchek Дата: Понедельник, 07.12.2015, 12:19 | Сообщение № 10
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
Gubanov, формула массива вводится одновременным нажатием Ctrl+Shift+Enter
К сообщению приложен файл: 3584760.xlsm (39.4 Kb)

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Сообщение отредактировал pabchek - Понедельник, 07.12.2015, 12:29
 
Ответить
СообщениеGubanov, формула массива вводится одновременным нажатием Ctrl+Shift+Enter
Автор - pabchek
Дата добавления - 07.12.2015 в 12:19
Gubanov Дата: Понедельник, 07.12.2015, 12:38 | Сообщение № 11
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
abtextime, Спасибо. Формула сработала.
 
Ответить
Сообщениеabtextime, Спасибо. Формула сработала.
Автор - Gubanov
Дата добавления - 07.12.2015 в 12:38
abtextime Дата: Понедельник, 07.12.2015, 12:40 | Сообщение № 12
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 117 ±
Замечаний: 0% ±

Excel 2010
Не за что, удачи!
 
Ответить
СообщениеНе за что, удачи!
Автор - abtextime
Дата добавления - 07.12.2015 в 12:40
pabchek Дата: Понедельник, 07.12.2015, 12:43 | Сообщение № 13
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
Цитата Gubanov, 07.12.2015 в 12:38, в сообщении № 11
abtextime, Спасибо. Формула сработала.

Поправьте, если ошибаюсь - формула abtextime, для января выдает 06/01 - но это выходной. В январе 16-го первая рабочая среда 13/01

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Сообщение отредактировал pabchek - Понедельник, 07.12.2015, 12:44
 
Ответить
Сообщение
Цитата Gubanov, 07.12.2015 в 12:38, в сообщении № 11
abtextime, Спасибо. Формула сработала.

Поправьте, если ошибаюсь - формула abtextime, для января выдает 06/01 - но это выходной. В январе 16-го первая рабочая среда 13/01
Автор - pabchek
Дата добавления - 07.12.2015 в 12:43
Gubanov Дата: Понедельник, 07.12.2015, 13:11 | Сообщение № 14
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
pabchek, Нет, на 2016 отображает 13.01
 
Ответить
Сообщениеpabchek, Нет, на 2016 отображает 13.01
Автор - Gubanov
Дата добавления - 07.12.2015 в 13:11
pabchek Дата: Понедельник, 07.12.2015, 13:28 | Сообщение № 15
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
Цитата Gubanov, 07.12.2015 в 13:11, в сообщении № 14
pabchek, Нет, на 2016 отображает 13.01

Вас не затруднит приложить пример?

"Учиться, учиться и еще раз учиться!"
WM: R399923528092
 
Ответить
Сообщение
Цитата Gubanov, 07.12.2015 в 13:11, в сообщении № 14
pabchek, Нет, на 2016 отображает 13.01

Вас не затруднит приложить пример?
Автор - pabchek
Дата добавления - 07.12.2015 в 13:28
Gubanov Дата: Понедельник, 07.12.2015, 14:09 | Сообщение № 16
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
pabchek, Конечно, можете смотреть. Работы еще много по графику. И вопросов у самого еще много.

Pelena, В графике есть список не рабочих и праздничных дней, которые исключаются.
К сообщению приложен файл: 5691006.xlsm (39.8 Kb)
 
Ответить
Сообщениеpabchek, Конечно, можете смотреть. Работы еще много по графику. И вопросов у самого еще много.

Pelena, В графике есть список не рабочих и праздничных дней, которые исключаются.
Автор - Gubanov
Дата добавления - 07.12.2015 в 14:09
abtextime Дата: Понедельник, 07.12.2015, 14:12 | Сообщение № 17
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 117 ±
Замечаний: 0% ±

Excel 2010
pabchek,

Если 06.01.16 отметить как праздничный день в именованном столбце Праздники, то будет всё ОК.

Pelena,
если первая среда выходная, формула проверяет еще _следующую среду (+7) на нахождение в из диапазоне Праздники, и в случае еще одной праздничной среды берет следующую (+14 дней)


Сообщение отредактировал abtextime - Понедельник, 07.12.2015, 14:15
 
Ответить
Сообщениеpabchek,

Если 06.01.16 отметить как праздничный день в именованном столбце Праздники, то будет всё ОК.

Pelena,
если первая среда выходная, формула проверяет еще _следующую среду (+7) на нахождение в из диапазоне Праздники, и в случае еще одной праздничной среды берет следующую (+14 дней)
Автор - abtextime
Дата добавления - 07.12.2015 в 14:12
Gustav Дата: Понедельник, 07.12.2015, 14:26 | Сообщение № 18
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2748
Репутация: 1137 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Тоже поучаствую. Формула массива, инициирующая дата в A1:
Код
=МИН(
РАБДЕНЬ(ДАТА(ГОД(A1);МЕСЯЦ(A1);0);СТРОКА($1:$21);Праздники)
*ЕСЛИ(ДЕНЬНЕД(
РАБДЕНЬ(ДАТА(ГОД(A1);МЕСЯЦ(A1);0);СТРОКА($1:$21);Праздники)
;2)=3;1;2))

Решил, что 3 недели (1:21) вполне хватит для разборок с первой средой месяца. Тест января 2019 года выдерживает :)
P.S. Поскольку тег [code] не позволяет сформатировать формулу желаемым (наглядным) образом, то продублирую также без оного:

=МИН(
РАБДЕНЬ(ДАТА(ГОД(A1);МЕСЯЦ(A1);0);СТРОКА($1:$21);Праздники)
*ЕСЛИ(ДЕНЬНЕД(
РАБДЕНЬ(ДАТА(ГОД(A1);МЕСЯЦ(A1);0);СТРОКА($1:$21);Праздники)
;2)=3;1;2))

P.P.S. А впрочем 1:21 - это же рабочие дни месяца, так что, получается, месяц и покрывается. А для некоторых (типа января) еще и в следующий месяц "залезается" :)

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Понедельник, 07.12.2015, 14:44
 
Ответить
СообщениеТоже поучаствую. Формула массива, инициирующая дата в A1:
Код
=МИН(
РАБДЕНЬ(ДАТА(ГОД(A1);МЕСЯЦ(A1);0);СТРОКА($1:$21);Праздники)
*ЕСЛИ(ДЕНЬНЕД(
РАБДЕНЬ(ДАТА(ГОД(A1);МЕСЯЦ(A1);0);СТРОКА($1:$21);Праздники)
;2)=3;1;2))

Решил, что 3 недели (1:21) вполне хватит для разборок с первой средой месяца. Тест января 2019 года выдерживает :)
P.S. Поскольку тег [code] не позволяет сформатировать формулу желаемым (наглядным) образом, то продублирую также без оного:

=МИН(
РАБДЕНЬ(ДАТА(ГОД(A1);МЕСЯЦ(A1);0);СТРОКА($1:$21);Праздники)
*ЕСЛИ(ДЕНЬНЕД(
РАБДЕНЬ(ДАТА(ГОД(A1);МЕСЯЦ(A1);0);СТРОКА($1:$21);Праздники)
;2)=3;1;2))

P.P.S. А впрочем 1:21 - это же рабочие дни месяца, так что, получается, месяц и покрывается. А для некоторых (типа января) еще и в следующий месяц "залезается" :)
Автор - Gustav
Дата добавления - 07.12.2015 в 14:26
Pelena Дата: Понедельник, 07.12.2015, 14:26 | Сообщение № 19
Группа: Админы
Ранг: Местный житель
Сообщений: 19188
Репутация: 4421 ±
Замечаний: ±

Excel 365 & Mac Excel
Ещё вариант с формулой массива (с подачи TimSha)
Код
=НАИМЕНЬШИЙ(СредыМесяца*ЕНД(ПОИСКПОЗ(СредыМесяца;праздники;0));СЧЁТ(ПОИСКПОЗ(СредыМесяца;праздники;0))+1)

имя СредыМесяца содержит формулу
Код
=ДАТА(Год;Месяц;{1;2;3;4}*7-6)+ОСТАТ(3-ДАТА(Год;Месяц;);7)
К сообщению приложен файл: 8753978.xlsm (39.1 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЕщё вариант с формулой массива (с подачи TimSha)
Код
=НАИМЕНЬШИЙ(СредыМесяца*ЕНД(ПОИСКПОЗ(СредыМесяца;праздники;0));СЧЁТ(ПОИСКПОЗ(СредыМесяца;праздники;0))+1)

имя СредыМесяца содержит формулу
Код
=ДАТА(Год;Месяц;{1;2;3;4}*7-6)+ОСТАТ(3-ДАТА(Год;Месяц;);7)
Автор - Pelena
Дата добавления - 07.12.2015 в 14:26
pabchek Дата: Понедельник, 07.12.2015, 15:09 | Сообщение № 20
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
Цитата Pelena, 07.12.2015 в 13:53, в сообщении № 16
Универсальность формулы надо проверять на январе 2019 года.

У меня 16/01/19
К сообщению приложен файл: 1165261.xlsb (8.5 Kb)

"Учиться, учиться и еще раз учиться!"
WM: R399923528092
 
Ответить
Сообщение
Цитата Pelena, 07.12.2015 в 13:53, в сообщении № 16
Универсальность формулы надо проверять на январе 2019 года.

У меня 16/01/19
Автор - pabchek
Дата добавления - 07.12.2015 в 15:09
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Определенный день в ячейке (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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