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

Вход

Регистрация

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

 

= Мир MS Excel/Проверка данных в Google Sheets (работа с временем) - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Проверка данных в Google Sheets (работа с временем) (Формулы/Formulas)
Проверка данных в Google Sheets (работа с временем)
LeSSSSS Дата: Понедельник, 30.09.2019, 14:32 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Добрый день!

Пытаюсь решить такую задачу в Google табличке. Есть два столбца: 1) Дата (установлена проверка данных по дате), т.е. пользователь ничего, кроме даты в заданном формате указать не может; 2) Время (необходимо настроить так, чтобы пользователь мог указать время в формате HH:MM:ss, при том время должно быть больше указанного ранее на три часа). Т.е. если в предыдущей строке была указана дата 01.10.2019, а время 23:00, то в строке ниже должно быть указано любое время, но больше 02:00 уже 02.10.2019. Если пользователь укажет 02:00, но дату оставит 01.10, должна сработать проверка данных на запрет ввода такого времени. Пробовал зависимые списки с фильтром по набору времени, но это жесть, при условии, что моей задачей является составление некого планнинга на целый месяц. Если кто сможет помочь решить задачу, буду премного благодарен!
 
Ответить
СообщениеДобрый день!

Пытаюсь решить такую задачу в Google табличке. Есть два столбца: 1) Дата (установлена проверка данных по дате), т.е. пользователь ничего, кроме даты в заданном формате указать не может; 2) Время (необходимо настроить так, чтобы пользователь мог указать время в формате HH:MM:ss, при том время должно быть больше указанного ранее на три часа). Т.е. если в предыдущей строке была указана дата 01.10.2019, а время 23:00, то в строке ниже должно быть указано любое время, но больше 02:00 уже 02.10.2019. Если пользователь укажет 02:00, но дату оставит 01.10, должна сработать проверка данных на запрет ввода такого времени. Пробовал зависимые списки с фильтром по набору времени, но это жесть, при условии, что моей задачей является составление некого планнинга на целый месяц. Если кто сможет помочь решить задачу, буду премного благодарен!
Автор - LeSSSSS
Дата добавления - 30.09.2019 в 14:32
Gustav Дата: Среда, 02.10.2019, 21:12 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2728
Репутация: 1132 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Сделал примерно так. В ячейках первой строки - заголовки колонок: в A1 - Дата, в B1 - Время.
И далее настроил две проверки данных:

1. Диапазон ячеек: A2:A1000
Правила: Ваша формула:
[vba]
Код
=AND(
ISDATE(A2);
OR(AND(ISDATE(A1);A2>=A1); AND(NOT(ISDATE(A1));NOT(ISBLANK(A1))));
OR(AND(ISDATE(A3);A3>=A2); ISBLANK(A3));
OR(ISBLANK(B2); ISDATE(B2))
)
[/vba]Для неверных данных: запрещать ввод данных
Оформление: Показывать текст справки для проверки данных:
[vba]
Код
Введите правильную ДАТУ, согласующуюся с соседними ячейками!
[/vba]

2. Диапазон ячеек: B2:B1000
Правила: Ваша формула:
[vba]
Код
=AND(
AND(ISDATE(B2); B2<1);
ISDATE(A2);
OR(AND(ISDATE(B1);A2+B2-IFERROR(A1+B1;A2+B2)>=TIME(3;0;0)); AND(NOT(ISDATE(B1));NOT(ISBLANK(B1))))
)
[/vba]Для неверных данных: запрещать ввод данных
Оформление: Показывать текст справки для проверки данных:
[vba]
Код
Введите правильное ВРЕМЯ, согласующееся с соседними ячейками!
[/vba]

Посмотрите, может быть, Вам что-то подойдёт. Запрет, конечно, не абсолютный - абсолютных не бывает: всегда остается операция копирования из другого места, а также очистка ячеек клавишей Delete. Но даже если Delete, то начинают подсвечиваться соседние ячейки, говорящие человеку разумному о том, что что-то не так в данных и надо что-то подправить. Именно разумному, который ратует за порядок в данных, а не табличному садисту, злорадно пытающемуся вольно или невольно этот порядок разрушить и сломать хоть какие-то, но проверки (подобных деятелей надо по возможности удалять из трудового коллектива).

Против копирования же, как против лома, увы, нет приема. Но можно человека (опять-таки разумного) попросить не использовать эту операцию в рассматриваемых диапазонах (точнее, даже не копирования, а его заключительной фазы - вставки).

Общие правила, которыми я руководствовался при составлении проверок:
* ячейка из столбца "Время" не может быть заполнена при незаполненной "Дате" из той же строки
* в ячейку из столбца "Время" не может быть введено значение больше, чем 23:59:59
* значения в столбце "Дата" должны монотонно возрастать, т.е. значение даты из определенной строки не может быть меньше значения даты из предыдущей строки и не может быть больше значения из последуюшей строки (при условии непустоты этой последующей ячейки)
* разница сумм "Дата"+"Время" в текущей строке и "Дата"+"Время" в предыдущей строке не может быть меньше 3-х часов (в формуле обеспечивается условием ">=TIME(3;0;0)", которое может быть изменено на любое другое значение дельты)

МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеСделал примерно так. В ячейках первой строки - заголовки колонок: в A1 - Дата, в B1 - Время.
И далее настроил две проверки данных:

1. Диапазон ячеек: A2:A1000
Правила: Ваша формула:
[vba]
Код
=AND(
ISDATE(A2);
OR(AND(ISDATE(A1);A2>=A1); AND(NOT(ISDATE(A1));NOT(ISBLANK(A1))));
OR(AND(ISDATE(A3);A3>=A2); ISBLANK(A3));
OR(ISBLANK(B2); ISDATE(B2))
)
[/vba]Для неверных данных: запрещать ввод данных
Оформление: Показывать текст справки для проверки данных:
[vba]
Код
Введите правильную ДАТУ, согласующуюся с соседними ячейками!
[/vba]

2. Диапазон ячеек: B2:B1000
Правила: Ваша формула:
[vba]
Код
=AND(
AND(ISDATE(B2); B2<1);
ISDATE(A2);
OR(AND(ISDATE(B1);A2+B2-IFERROR(A1+B1;A2+B2)>=TIME(3;0;0)); AND(NOT(ISDATE(B1));NOT(ISBLANK(B1))))
)
[/vba]Для неверных данных: запрещать ввод данных
Оформление: Показывать текст справки для проверки данных:
[vba]
Код
Введите правильное ВРЕМЯ, согласующееся с соседними ячейками!
[/vba]

Посмотрите, может быть, Вам что-то подойдёт. Запрет, конечно, не абсолютный - абсолютных не бывает: всегда остается операция копирования из другого места, а также очистка ячеек клавишей Delete. Но даже если Delete, то начинают подсвечиваться соседние ячейки, говорящие человеку разумному о том, что что-то не так в данных и надо что-то подправить. Именно разумному, который ратует за порядок в данных, а не табличному садисту, злорадно пытающемуся вольно или невольно этот порядок разрушить и сломать хоть какие-то, но проверки (подобных деятелей надо по возможности удалять из трудового коллектива).

Против копирования же, как против лома, увы, нет приема. Но можно человека (опять-таки разумного) попросить не использовать эту операцию в рассматриваемых диапазонах (точнее, даже не копирования, а его заключительной фазы - вставки).

Общие правила, которыми я руководствовался при составлении проверок:
* ячейка из столбца "Время" не может быть заполнена при незаполненной "Дате" из той же строки
* в ячейку из столбца "Время" не может быть введено значение больше, чем 23:59:59
* значения в столбце "Дата" должны монотонно возрастать, т.е. значение даты из определенной строки не может быть меньше значения даты из предыдущей строки и не может быть больше значения из последуюшей строки (при условии непустоты этой последующей ячейки)
* разница сумм "Дата"+"Время" в текущей строке и "Дата"+"Время" в предыдущей строке не может быть меньше 3-х часов (в формуле обеспечивается условием ">=TIME(3;0;0)", которое может быть изменено на любое другое значение дельты)
Автор - Gustav
Дата добавления - 02.10.2019 в 21:12
LeSSSSS Дата: Четверг, 10.10.2019, 11:43 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Цитата Gustav, 02.10.2019 в 21:12, в сообщении № 2 ()
=AND(
AND(ISDATE(B2); B2<1);
ISDATE(A2);
OR(AND(ISDATE(B1);A2+B2-IFERROR(A1+B1;A2+B2)>=TIME(3;0;0)); AND(NOT(ISDATE(B1));NOT(ISBLANK(B1))))
)

Gustav, огромное Вам человеческое спасибо! Вернулся из командировки, чтобы дальше ломать голову со своей задачей, а Вы ее проактивно уже помогли мне решить))
Подставил формулы, и все заработало именно так, как я и хотел. Буду разбирать логику формул для самообразования! ОГРОМНОЕ СПАСИБО!
 
Ответить
Сообщение
Цитата Gustav, 02.10.2019 в 21:12, в сообщении № 2 ()
=AND(
AND(ISDATE(B2); B2<1);
ISDATE(A2);
OR(AND(ISDATE(B1);A2+B2-IFERROR(A1+B1;A2+B2)>=TIME(3;0;0)); AND(NOT(ISDATE(B1));NOT(ISBLANK(B1))))
)

Gustav, огромное Вам человеческое спасибо! Вернулся из командировки, чтобы дальше ломать голову со своей задачей, а Вы ее проактивно уже помогли мне решить))
Подставил формулы, и все заработало именно так, как я и хотел. Буду разбирать логику формул для самообразования! ОГРОМНОЕ СПАСИБО!
Автор - LeSSSSS
Дата добавления - 10.10.2019 в 11:43
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Проверка данных в Google Sheets (работа с временем) (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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