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

Вход

Регистрация

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

 

= Мир MS Excel/Сверка даты прибытия с расчетной - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сверка даты прибытия с расчетной (Формулы/Formulas)
Сверка даты прибытия с расчетной
Julika Дата: Среда, 13.03.2019, 11:21 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Огромнейшая просьба к компетентным людям в Excel формулах - помогите, пожалуйста, разработать формулу!!!
Нужно, чтобы с колонке "Н" выскакивало слово "Опоздание" при таких условиях - если мы получаем заявку на загрузку до 16:00(столбик В), то машина должна прийти не позже чем до конца следующего дня(столбик G), а если после 16:00, то в течении двух дней, если позже то это ОПОЗДАНИЕ. Файл во вложении, посмотрите пожалуйста!
Буду очень благодарна Вам!
Хорошего дня!


Спасибо, всего хорошего!
Julika


Сообщение отредактировал Julika - Среда, 13.03.2019, 12:15
 
Ответить
СообщениеОгромнейшая просьба к компетентным людям в Excel формулах - помогите, пожалуйста, разработать формулу!!!
Нужно, чтобы с колонке "Н" выскакивало слово "Опоздание" при таких условиях - если мы получаем заявку на загрузку до 16:00(столбик В), то машина должна прийти не позже чем до конца следующего дня(столбик G), а если после 16:00, то в течении двух дней, если позже то это ОПОЗДАНИЕ. Файл во вложении, посмотрите пожалуйста!
Буду очень благодарна Вам!
Хорошего дня!

Автор - Julika
Дата добавления - 13.03.2019 в 11:21
_Boroda_ Дата: Среда, 13.03.2019, 11:26 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 14442
Репутация: 5779 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
- Прочитайте Правила форума
- Исправьте название темы согласно п.2 Правил форума - Вам обязательно использовать функцию ЕСЛИ или Вам нужно сравнить даты (без разницы какой формулой)?
- Приложите файл с исходными данными и желаемым результатом (можно вручную) в формате Excel размером до 100кб согласно п.3 Правил форума


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение- Прочитайте Правила форума
- Исправьте название темы согласно п.2 Правил форума - Вам обязательно использовать функцию ЕСЛИ или Вам нужно сравнить даты (без разницы какой формулой)?
- Приложите файл с исходными данными и желаемым результатом (можно вручную) в формате Excel размером до 100кб согласно п.3 Правил форума

Автор - _Boroda_
Дата добавления - 13.03.2019 в 11:26
Julika Дата: Среда, 13.03.2019, 11:44 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Здравствуйте!
Совсем не обязательно формулой ЕСЛИ. Возможно и другой какой-то. Это мне показалось, что можно этой. Пробовала - не получилось.
Файл прицепляю. Там две вкладки - одна с условием, а другая с желаемым результатом.
Спасибо!
К сообщению приложен файл: 4589796.xlsx(13.3 Kb)


Спасибо, всего хорошего!
Julika
 
Ответить
СообщениеЗдравствуйте!
Совсем не обязательно формулой ЕСЛИ. Возможно и другой какой-то. Это мне показалось, что можно этой. Пробовала - не получилось.
Файл прицепляю. Там две вкладки - одна с условием, а другая с желаемым результатом.
Спасибо!

Автор - Julika
Дата добавления - 13.03.2019 в 11:44
_Boroda_ Дата: Среда, 13.03.2019, 11:48 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 14442
Репутация: 5779 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Совсем не обязательно формулой ЕСЛИ

Тогда измените название темы - правка первого поста

Ровно 16:00 - это 1 или 2 дня?

И я ж написал - желаемый результат вручную забейте. Что в Вашем понятии "должно быть плюс один день(День Б -до конца суток)" - это только Вам известно
Пардон, не заметил второй лист


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Совсем не обязательно формулой ЕСЛИ

Тогда измените название темы - правка первого поста

Ровно 16:00 - это 1 или 2 дня?

И я ж написал - желаемый результат вручную забейте. Что в Вашем понятии "должно быть плюс один день(День Б -до конца суток)" - это только Вам известно
Пардон, не заметил второй лист

Автор - _Boroda_
Дата добавления - 13.03.2019 в 11:48
Julika Дата: Среда, 13.03.2019, 12:21 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Тему изменила. Надеюсь так подойдет - сравнение двух дат любой формулой.

Да, первого дня. Т.е. Если мы получили заявку на авто первого дня до 16:00(столбик В), то машина должна прийти до конца дня этих суток или следующего до конца дня(столбик G). Если позже - это Опоздание. Если после 16:00, то машина должна прийти этот день до конца суток или следующие два дня, до конца суток. Если позже - то это Опоздание.

Спасибо.)


Спасибо, всего хорошего!
Julika
 
Ответить
СообщениеТему изменила. Надеюсь так подойдет - сравнение двух дат любой формулой.

Да, первого дня. Т.е. Если мы получили заявку на авто первого дня до 16:00(столбик В), то машина должна прийти до конца дня этих суток или следующего до конца дня(столбик G). Если позже - это Опоздание. Если после 16:00, то машина должна прийти этот день до конца суток или следующие два дня, до конца суток. Если позже - то это Опоздание.

Спасибо.)

Автор - Julika
Дата добавления - 13.03.2019 в 12:21
_Boroda_ Дата: Среда, 13.03.2019, 12:53 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 14442
Репутация: 5779 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?
Код
=ЕСЛИ(G2>ОТБР(B2)+2+(ОСТАТ(B2;1)-"16:">0);"опоздание";"")
К сообщению приложен файл: 4589796_1.xlsx(13.3 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?
Код
=ЕСЛИ(G2>ОТБР(B2)+2+(ОСТАТ(B2;1)-"16:">0);"опоздание";"")

Автор - _Boroda_
Дата добавления - 13.03.2019 в 12:53
Julika Дата: Среда, 13.03.2019, 14:46 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Да, супер!!! hands hands
Как Вы это делаете!!!

Огромнейшее спасибо!!!

ПОдскажите, а возможно ли как-то исключить не рабочие дни, субботу и воскресенье.
Т.е. если в четверг пришла заявка до 16:00, то машина должна быть в пятницу. А если после 16:00 и пятница до 16:00, то в понедельник включительно.
А если в пятницу после 16:00, то машина должна быть в вторник включительно.
Очень благодарна!


Спасибо, всего хорошего!
Julika
 
Ответить
СообщениеДа, супер!!! hands hands
Как Вы это делаете!!!

Огромнейшее спасибо!!!

ПОдскажите, а возможно ли как-то исключить не рабочие дни, субботу и воскресенье.
Т.е. если в четверг пришла заявка до 16:00, то машина должна быть в пятницу. А если после 16:00 и пятница до 16:00, то в понедельник включительно.
А если в пятницу после 16:00, то машина должна быть в вторник включительно.
Очень благодарна!

Автор - Julika
Дата добавления - 13.03.2019 в 14:46
_Boroda_ Дата: Среда, 13.03.2019, 14:56 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 14442
Репутация: 5779 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А праздничные дни? А переносы выходных на будни (если есть)? А переносы будних на выходные (если есть) Если да, то нужен список праздников и переносов


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеА праздничные дни? А переносы выходных на будни (если есть)? А переносы будних на выходные (если есть) Если да, то нужен список праздников и переносов

Автор - _Boroda_
Дата добавления - 13.03.2019 в 14:56
Julika Дата: Среда, 13.03.2019, 16:35 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Вот нашла календарь, наконец-то в Ексель-формате.
Вообще стандартные выходные и праздники, а то, что переноситься я указала в файле справа и соответственно отметила в календаре.
Спасибо!
К сообщению приложен файл: 2019ua_.xls(43.0 Kb)


Спасибо, всего хорошего!
Julika
 
Ответить
СообщениеВот нашла календарь, наконец-то в Ексель-формате.
Вообще стандартные выходные и праздники, а то, что переноситься я указала в файле справа и соответственно отметила в календаре.
Спасибо!

Автор - Julika
Дата добавления - 13.03.2019 в 16:35
Julika Дата: Четверг, 14.03.2019, 14:54 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Здравствуйте, уважаемый Boroda, очень благодарна за вчерашнее решение вопроса, формула работает классно.
Но хотела уточнить, получилось ли у Вас привязать эту формулу к календарю или это не совсем возможно? Может нужно в другом формате календарь?
Заранее благодарна!
Хорошего дня!


Спасибо, всего хорошего!
Julika
 
Ответить
СообщениеЗдравствуйте, уважаемый Boroda, очень благодарна за вчерашнее решение вопроса, формула работает классно.
Но хотела уточнить, получилось ли у Вас привязать эту формулу к календарю или это не совсем возможно? Может нужно в другом формате календарь?
Заранее благодарна!
Хорошего дня!

Автор - Julika
Дата добавления - 14.03.2019 в 14:54
_Boroda_ Дата: Четверг, 14.03.2019, 17:34 | Сообщение № 11
Группа: Модераторы
Ранг: Местный житель
Сообщений: 14442
Репутация: 5779 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Там все не так просто, обычной РАБДЕНЬ.МЕЖД не получится
Посмотрите такой вариант
Код
=ЕСЛИ(G2>НАИМЕНЬШИЙ(ИНДЕКС(--ТЕКСТ(НЕ((ДЕНЬНЕД(B2+СТОЛБЕЦ(A:I);2)>5)+ЕЧИСЛО(ПОИСКПОЗ(ОТБР(B2)+СТОЛБЕЦ(A:I);L$2:L$30;))-ЕЧИСЛО(ПОИСКПОЗ(ОТБР(B2)+СТОЛБЕЦ(A:I);M$2:M$30;)))*(ОТБР(B2)+СТОЛБЕЦ(A:I));"0;;99999"););2+(ОСТАТ(B2;1)-"16:">0));"опоздание";"")

Наверняка можно попроще, даже похоже знаю как, но сейчас занят сильно, не могу думать
К сообщению приложен файл: 4589796_1-1-1.xlsx(14.2 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТам все не так просто, обычной РАБДЕНЬ.МЕЖД не получится
Посмотрите такой вариант
Код
=ЕСЛИ(G2>НАИМЕНЬШИЙ(ИНДЕКС(--ТЕКСТ(НЕ((ДЕНЬНЕД(B2+СТОЛБЕЦ(A:I);2)>5)+ЕЧИСЛО(ПОИСКПОЗ(ОТБР(B2)+СТОЛБЕЦ(A:I);L$2:L$30;))-ЕЧИСЛО(ПОИСКПОЗ(ОТБР(B2)+СТОЛБЕЦ(A:I);M$2:M$30;)))*(ОТБР(B2)+СТОЛБЕЦ(A:I));"0;;99999"););2+(ОСТАТ(B2;1)-"16:">0));"опоздание";"")

Наверняка можно попроще, даже похоже знаю как, но сейчас занят сильно, не могу думать

Автор - _Boroda_
Дата добавления - 14.03.2019 в 17:34
Julika Дата: Пятница, 15.03.2019, 17:56 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Большое спасибо!
Вроде бы работает, еще немного потестирую, потом отпишусь.
Боже, какой Вы, молодец!! hands
Два ряда формул, как разобрать, что и где.!!! %)

Если будет у Вас время и вы сможете еще предложить варианты - буду признательна!

А пока огроменское спасибо и на том!!
Хороших выходных!


Спасибо, всего хорошего!
Julika
 
Ответить
СообщениеБольшое спасибо!
Вроде бы работает, еще немного потестирую, потом отпишусь.
Боже, какой Вы, молодец!! hands
Два ряда формул, как разобрать, что и где.!!! %)

Если будет у Вас время и вы сможете еще предложить варианты - буду признательна!

А пока огроменское спасибо и на том!!
Хороших выходных!

Автор - Julika
Дата добавления - 15.03.2019 в 17:56
_Boroda_ Дата: Воскресенье, 17.03.2019, 01:22 | Сообщение № 13
Группа: Модераторы
Ранг: Местный житель
Сообщений: 14442
Репутация: 5779 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
как разобрать, что и где

Достаточно просто попросить объяснить :D
Давайте по порядку
1. СТОЛБЕЦ(A:I) - даст нам номера столбцов от А до I - 1,2,3,...,9 (в России теоретически может быть до 14 выходных подряд, поэтому нужно брать больше, до столбца Q - 14+2+1)
2. B2+СТОЛБЕЦ(A:I) - прибавит к значению В2 последовательно день, два, три, ..., девять
3. ДЕНЬНЕД(B2+СТОЛБЕЦ(A:I);2) - даст нам дни недели для каждого дня из полученного в п.2 массива. Второй аргумент 2 показывает, что дни начинаются с понедельника = 1 (см. справку ДЕНЬНЕД)
4. п.3 > 5 - даст ЛОЖЬ для понедельника-пятницы и ИСТИНА для сб и вскр

5. ОТБР(B2) - отсечет от В2 время, оставит только дату
6. ОТБР(B2)+СТОЛБЕЦ(A:I) - см. п.2, только здесь уже без времени
7. ПОИСКПОЗ(п.6;L$2:L$30;) - пытаемся найти полученное в п.6 в списке выходных и праздничных дней. Если найдем, то будет какое-то число, если не найдем, то будет ошибка
8. ЕЧИСЛО(п.7) - дает ИСТИНА, если в п.7 число и ЛОЖЬ, если ошибка

9. ПОИСКПОЗ(п.6;M$2:M$30;) - пытаемся найти полученное в п.6 в списке рабочих выходных дней. Если найдем, то будет какое-то число, если не найдем, то будет ошибка
10. ЕЧИСЛО(п.9) - дает ИСТИНА, если в п.9 число и ЛОЖЬ, если ошибка

11. п.4 + п.8 - п10 - даст массив из 0 или 1. Всего по количеству n = 9 элементов (см. п.1). Если каждый из дней выходной, то п.4 даст нам 1, если праздник, то п.8 тоже даст 1 (они не должны пересекаться), а если это рабочий выходной, то п.10 даст минус 1, который вместе с единичкой из п.4 даст 0
12. НЕ(п.11) - переворачивает все наоборот - 1 становится 0, а 0 становится 1
13. п.11 * п.6 - получаем наш список 9-и дней из п.6, умноженный на такой же по величине массив, состоящий из 1 (рабочие дни) и 0 (нормальные дни). Получаем рабочие дни или нули
14. ТЕКСТ(п.13;"0;;99999") преобразует п.13 в те же рабочие дни и 99999 вместо нулей. Но все это не числами, а "текстовыми" числами.
15. --п.14 - преобразует п.14 в нормальные числа
* Отступление. Даты в Excel - это числа. Количество дней, прошедших с 00.01.1900г. 01 марта 2019г = 43525. Это заведомо меньше, чем 99999, это будет 12 октября 2173г., к тому времени табличка будет неактуальна, да и мы возможно не доживем
16. ИНДЕКС(п.15;0) преобразует массив из п.15 в как бы список. Визуально ничего не изменяется, но если бы мы этого не сделали, то формулу пришлось бы вводить как формулу массива - одновременным нажатием Контрл Шифт Ентер

17. ОСТАТ(B2;1) - остаток от деление В2 на 1 даст нам только время, без даты
18. ОСТАТ(B2;1)-"16:">0 - вычитаем из п.17 контрольное время 16 часов и сравниваем с нулем. Если больше нуля, то ИСТИНА, иначе - ЛОЖЬ
19. 2+п.18 - два наших контрольных дня (текущий и тот, который должны ехать) плюс 1 или 0 из п.18. Итог покажет нам количество рабочих дней, которое машина должна ехать (считая день выезда)
20. НАИМЕНЬШИЙ(п.16; п.19) - из списка рабочих дней и 99999 выбираем тот наименьший день, который нам нужен. Получаем 0:00:00 часов найденного дня, что по сути равно 24:00:00 дня предыдущего
21. ЕСЛИ(G2>п.20;"опоздание";"") - сравниваем полученное с временем прибытия и, если оно больше, то ругаемся


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
как разобрать, что и где

Достаточно просто попросить объяснить :D
Давайте по порядку
1. СТОЛБЕЦ(A:I) - даст нам номера столбцов от А до I - 1,2,3,...,9 (в России теоретически может быть до 14 выходных подряд, поэтому нужно брать больше, до столбца Q - 14+2+1)
2. B2+СТОЛБЕЦ(A:I) - прибавит к значению В2 последовательно день, два, три, ..., девять
3. ДЕНЬНЕД(B2+СТОЛБЕЦ(A:I);2) - даст нам дни недели для каждого дня из полученного в п.2 массива. Второй аргумент 2 показывает, что дни начинаются с понедельника = 1 (см. справку ДЕНЬНЕД)
4. п.3 > 5 - даст ЛОЖЬ для понедельника-пятницы и ИСТИНА для сб и вскр

5. ОТБР(B2) - отсечет от В2 время, оставит только дату
6. ОТБР(B2)+СТОЛБЕЦ(A:I) - см. п.2, только здесь уже без времени
7. ПОИСКПОЗ(п.6;L$2:L$30;) - пытаемся найти полученное в п.6 в списке выходных и праздничных дней. Если найдем, то будет какое-то число, если не найдем, то будет ошибка
8. ЕЧИСЛО(п.7) - дает ИСТИНА, если в п.7 число и ЛОЖЬ, если ошибка

9. ПОИСКПОЗ(п.6;M$2:M$30;) - пытаемся найти полученное в п.6 в списке рабочих выходных дней. Если найдем, то будет какое-то число, если не найдем, то будет ошибка
10. ЕЧИСЛО(п.9) - дает ИСТИНА, если в п.9 число и ЛОЖЬ, если ошибка

11. п.4 + п.8 - п10 - даст массив из 0 или 1. Всего по количеству n = 9 элементов (см. п.1). Если каждый из дней выходной, то п.4 даст нам 1, если праздник, то п.8 тоже даст 1 (они не должны пересекаться), а если это рабочий выходной, то п.10 даст минус 1, который вместе с единичкой из п.4 даст 0
12. НЕ(п.11) - переворачивает все наоборот - 1 становится 0, а 0 становится 1
13. п.11 * п.6 - получаем наш список 9-и дней из п.6, умноженный на такой же по величине массив, состоящий из 1 (рабочие дни) и 0 (нормальные дни). Получаем рабочие дни или нули
14. ТЕКСТ(п.13;"0;;99999") преобразует п.13 в те же рабочие дни и 99999 вместо нулей. Но все это не числами, а "текстовыми" числами.
15. --п.14 - преобразует п.14 в нормальные числа
* Отступление. Даты в Excel - это числа. Количество дней, прошедших с 00.01.1900г. 01 марта 2019г = 43525. Это заведомо меньше, чем 99999, это будет 12 октября 2173г., к тому времени табличка будет неактуальна, да и мы возможно не доживем
16. ИНДЕКС(п.15;0) преобразует массив из п.15 в как бы список. Визуально ничего не изменяется, но если бы мы этого не сделали, то формулу пришлось бы вводить как формулу массива - одновременным нажатием Контрл Шифт Ентер

17. ОСТАТ(B2;1) - остаток от деление В2 на 1 даст нам только время, без даты
18. ОСТАТ(B2;1)-"16:">0 - вычитаем из п.17 контрольное время 16 часов и сравниваем с нулем. Если больше нуля, то ИСТИНА, иначе - ЛОЖЬ
19. 2+п.18 - два наших контрольных дня (текущий и тот, который должны ехать) плюс 1 или 0 из п.18. Итог покажет нам количество рабочих дней, которое машина должна ехать (считая день выезда)
20. НАИМЕНЬШИЙ(п.16; п.19) - из списка рабочих дней и 99999 выбираем тот наименьший день, который нам нужен. Получаем 0:00:00 часов найденного дня, что по сути равно 24:00:00 дня предыдущего
21. ЕСЛИ(G2>п.20;"опоздание";"") - сравниваем полученное с временем прибытия и, если оно больше, то ругаемся

Автор - _Boroda_
Дата добавления - 17.03.2019 в 01:22
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сверка даты прибытия с расчетной (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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