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

Вход

Регистрация

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

 

= Мир MS Excel/ВРЕМЯ ? Выделение строк по условию временного интервала - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » ВРЕМЯ ? Выделение строк по условию временного интервала (Формулы/Formulas)
ВРЕМЯ ? Выделение строк по условию временного интервала
evgven Дата: Вторник, 12.09.2017, 09:20 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Нужна помощь. в приложении кусочек большой огромной таблицы.. которая выгружается с портала и имеет несколько десятков тысяч строк..Имеются ячейки ,в которых ставится время включения прибора(начала сеанса работы) -час.мин.сек. За один сеанс делается несколько отметок времени в смежной ячейке,а время начала сеанса дублируется пока сеанс не закончится и не начнется следующий.. Нужна формула для поиска строк и выделения(например цифрой или буквой) тех,которые попадают под условие- Временная отметка сделана ранее,чем за 5 минут до следующего сеанса . заранее спасибо) если требуется развернутое пояснение-поясню..
К сообщению приложен файл: 7663165.xlsx(13Kb)
 
Ответить
СообщениеНужна помощь. в приложении кусочек большой огромной таблицы.. которая выгружается с портала и имеет несколько десятков тысяч строк..Имеются ячейки ,в которых ставится время включения прибора(начала сеанса работы) -час.мин.сек. За один сеанс делается несколько отметок времени в смежной ячейке,а время начала сеанса дублируется пока сеанс не закончится и не начнется следующий.. Нужна формула для поиска строк и выделения(например цифрой или буквой) тех,которые попадают под условие- Временная отметка сделана ранее,чем за 5 минут до следующего сеанса . заранее спасибо) если требуется развернутое пояснение-поясню..

Автор - evgven
Дата добавления - 12.09.2017 в 09:20
Pelena Дата: Вторник, 12.09.2017, 09:41 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11491
Репутация: 2555 ±
Замечаний: 0% ±

Excel 2010, 2016 & Mac Excel
И Вам здравствуйте.
Код
=(D2<>D3)*(C3-C2<--"0:5")*(C3<>"")

Или аналогичную формулу в Условное форматирование, тогда будет подкрашиваться цветом (сделано для столбца С)
К сообщению приложен файл: 2428643.xlsx(13Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеИ Вам здравствуйте.
Код
=(D2<>D3)*(C3-C2<--"0:5")*(C3<>"")

Или аналогичную формулу в Условное форматирование, тогда будет подкрашиваться цветом (сделано для столбца С)

Автор - Pelena
Дата добавления - 12.09.2017 в 09:41
_Boroda_ Дата: Вторник, 12.09.2017, 09:50 | Сообщение № 3
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11327
Репутация: 4676 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Не уверен, что правильно понял
Код
=--ТЕКСТ(ИНДЕКС(D3:D99;СЧЁТЕСЛИ(D2:D98;D2))-C2;"[>"&--"0:5"&"]\0;[>0]1;\0")

Или, если не обращать внимания на последний блок, то
Код
=--(ИНДЕКС(D3:D99;СЧЁТЕСЛИ(D2:D98;D2))-C2-"0:5"<0)

И формат ячеек
0;;
К сообщению приложен файл: 7663165_1.xlsx(14Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеНе уверен, что правильно понял
Код
=--ТЕКСТ(ИНДЕКС(D3:D99;СЧЁТЕСЛИ(D2:D98;D2))-C2;"[>"&--"0:5"&"]\0;[>0]1;\0")

Или, если не обращать внимания на последний блок, то
Код
=--(ИНДЕКС(D3:D99;СЧЁТЕСЛИ(D2:D98;D2))-C2-"0:5"<0)

И формат ячеек
0;;

Автор - _Boroda_
Дата добавления - 12.09.2017 в 09:50
evgven Дата: Вторник, 12.09.2017, 09:53 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Pelena, здравствуйте, спасибо,сейчас проверю по результату сообщу .А скажите в формуле 0:5 это пять минут, чтобы корректно считало все ячейки должны иметь формат времени какой конкретно? 13:30:55 со звездочкой или без ?
 
Ответить
СообщениеPelena, здравствуйте, спасибо,сейчас проверю по результату сообщу .А скажите в формуле 0:5 это пять минут, чтобы корректно считало все ячейки должны иметь формат времени какой конкретно? 13:30:55 со звездочкой или без ?

Автор - evgven
Дата добавления - 12.09.2017 в 09:53
Pelena Дата: Вторник, 12.09.2017, 10:07 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11491
Репутация: 2555 ±
Замечаний: 0% ±

Excel 2010, 2016 & Mac Excel
Да, 0:5 это пять минут.
Да, ячейки должны иметь формат времени. Формат времени любой, потому что формат влияет только на внешнее представление, а не на само значение времени.

И да, забыла написать, что в столбце Е нули скрыты форматом 0;;


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеДа, 0:5 это пять минут.
Да, ячейки должны иметь формат времени. Формат времени любой, потому что формат влияет только на внешнее представление, а не на само значение времени.

И да, забыла написать, что в столбце Е нули скрыты форматом 0;;

Автор - Pelena
Дата добавления - 12.09.2017 в 10:07
evgven Дата: Вторник, 12.09.2017, 10:19 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, Добрый день, чего то не получается, дело в том,что нужно на примере файла сравнивать Ячейки С с 1 по 15ю с ячейкой D16 т.е с временем начала следующего сеанса, был 5.33.34 а следующий 5.38.34. Последняя отметка С15,которая была сделана во время сеанса,начатого в 5.33.34 имеет разницу всего 22 секунды до начала следующего сеанса 5.38.34,который зафиксирован в ячейке d16 .непонятно еще как фиксировать смену сеансов,(хотя можно по условию равно С и D..)
 
Ответить
Сообщение_Boroda_, Добрый день, чего то не получается, дело в том,что нужно на примере файла сравнивать Ячейки С с 1 по 15ю с ячейкой D16 т.е с временем начала следующего сеанса, был 5.33.34 а следующий 5.38.34. Последняя отметка С15,которая была сделана во время сеанса,начатого в 5.33.34 имеет разницу всего 22 секунды до начала следующего сеанса 5.38.34,который зафиксирован в ячейке d16 .непонятно еще как фиксировать смену сеансов,(хотя можно по условию равно С и D..)

Автор - evgven
Дата добавления - 12.09.2017 в 10:19
_Boroda_ Дата: Вторник, 12.09.2017, 10:25 | Сообщение № 7
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11327
Репутация: 4676 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
сравнивать Ячейки С с 1 по 15ю с ячейкой D16 т.е с временем начала следующего сеанса

Именно так у меня формула и работает.
В первом блоке все записи меньше, чем на 5 минут от начала второго блока. Поэтому там везде стоят единички.
Или нужно как-то иначе? Вы прямо в файле вручную набейте необходимый результат, а то так не совсем понятно что Вам нужно


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

Именно так у меня формула и работает.
В первом блоке все записи меньше, чем на 5 минут от начала второго блока. Поэтому там везде стоят единички.
Или нужно как-то иначе? Вы прямо в файле вручную набейте необходимый результат, а то так не совсем понятно что Вам нужно

Автор - _Boroda_
Дата добавления - 12.09.2017 в 10:25
evgven Дата: Вторник, 12.09.2017, 10:44 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, ДА я извиняюсь работает, на примере этой вырезки из таблицы.сейчас попробую вставить в реальную таблицу и по результату отпишусь. Данная формула вставляется копированием или запуск как массив? в моей таблице 485162 строки..


Сообщение отредактировал evgven - Вторник, 12.09.2017, 10:46
 
Ответить
Сообщение_Boroda_, ДА я извиняюсь работает, на примере этой вырезки из таблицы.сейчас попробую вставить в реальную таблицу и по результату отпишусь. Данная формула вставляется копированием или запуск как массив? в моей таблице 485162 строки..

Автор - evgven
Дата добавления - 12.09.2017 в 10:44
_Boroda_ Дата: Вторник, 12.09.2017, 10:50 | Сообщение № 9
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11327
Репутация: 4676 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Копируйте первую ячейку в моем файле, вставляйте в первую ячейку к себе, проверяйте, что ссылки именно туда, куда нужно и протягивайте формулу вниз
Да, 99 в формуле - это максимально возможное количество строк в одном блоке. Если у Вас больше, то замените 99 на, например, 999


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

Автор - _Boroda_
Дата добавления - 12.09.2017 в 10:50
evgven Дата: Среда, 20.09.2017, 11:45 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, Добрый день. В моей таблице формула работает не корректно, мне не понятно формула сравнивает ячейку С2 с D3? нижестоящей? мне требуется сравнение именно с началом следующего периода.. формула мне проставляет 1 там где надо и не надо.. и максимальное количество строк в блоке это ровно столько сколько строк в моей таблице?
 
Ответить
Сообщение_Boroda_, Добрый день. В моей таблице формула работает не корректно, мне не понятно формула сравнивает ячейку С2 с D3? нижестоящей? мне требуется сравнение именно с началом следующего периода.. формула мне проставляет 1 там где надо и не надо.. и максимальное количество строк в блоке это ровно столько сколько строк в моей таблице?

Автор - evgven
Дата добавления - 20.09.2017 в 11:45
_Boroda_ Дата: Среда, 20.09.2017, 11:49 | Сообщение № 11
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11327
Репутация: 4676 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
http://www.excelworld.ru/forum/2-35384-232148-16-1505889101

максимальное количество строк в блоке это ровно столько сколько строк в моей таблице?

Нет, это максимальное количество строк в БЛОКЕ однотипных записей


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщениеhttp://www.excelworld.ru/forum/2-35384-232148-16-1505889101

максимальное количество строк в блоке это ровно столько сколько строк в моей таблице?

Нет, это максимальное количество строк в БЛОКЕ однотипных записей

Автор - _Boroda_
Дата добавления - 20.09.2017 в 11:49
evgven Дата: Среда, 20.09.2017, 12:23 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
У меня получается что вся таблица целиком это блок однотипных записей? Сейчас попробовал еще раз на части таблицы протянуть формулу. Получил верный результат после фильтрации по возрастанию дат. Я понял,что формула работает,если хронологический порядок верный. если фильтровать только по возрастанию времени,то отмечается не то,что нужно. Вроде начинаю вникать,спасибо .попробую применить .
 
Ответить
СообщениеУ меня получается что вся таблица целиком это блок однотипных записей? Сейчас попробовал еще раз на части таблицы протянуть формулу. Получил верный результат после фильтрации по возрастанию дат. Я понял,что формула работает,если хронологический порядок верный. если фильтровать только по возрастанию времени,то отмечается не то,что нужно. Вроде начинаю вникать,спасибо .попробую применить .

Автор - evgven
Дата добавления - 20.09.2017 в 12:23
evgven Дата: Среда, 20.09.2017, 13:31 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, Сейчас попробовал еще раз на части таблицы протянуть формулу. Получил верный результат после фильтрации по возрастанию дат. Я понял,что формула работает,если хронологический порядок верный. если фильтровать только по возрастанию времени,то отмечается не то,что нужно. Вроде начинаю вникать,спасибо .попробую применить . но последняя ячейка у меня 485316, Все таки в формуле я указываю последнюю ячейку? У меня сам файл очень тяжелый 80мб и при протяжке формулы виснет на обработке и расчете..
 
Ответить
Сообщение_Boroda_, Сейчас попробовал еще раз на части таблицы протянуть формулу. Получил верный результат после фильтрации по возрастанию дат. Я понял,что формула работает,если хронологический порядок верный. если фильтровать только по возрастанию времени,то отмечается не то,что нужно. Вроде начинаю вникать,спасибо .попробую применить . но последняя ячейка у меня 485316, Все таки в формуле я указываю последнюю ячейку? У меня сам файл очень тяжелый 80мб и при протяжке формулы виснет на обработке и расчете..

Автор - evgven
Дата добавления - 20.09.2017 в 13:31
_Boroda_ Дата: Среда, 20.09.2017, 13:53 | Сообщение № 14
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11327
Репутация: 4676 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Вы как думаете, я Вам в посте 11 зачем дал ссылку на вашу закрытую тему, в которой я написал про "приложите файл" и задал вопрос "почему ..."? Как я могу отвечать на ваши вопросы, если вы игнорируете мои?

По поводу ссылки - блок - это не весь массив, блок - это то, что у вас покрашено одним цветом. Каждый период - это свой блок. Сколько у вас максимально может быть записей в каждом сеансе? Допустим, 134. Прибавьте еще немного, например, пусть будет 170. Тогда берем мою формулу, вставляем ее в ячейку в строке 2, меняем 99 на 170, а 98 на 169 и протягиваем вниз. Но по-любому на полмиллиона строк работать быстро не будет


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

По поводу ссылки - блок - это не весь массив, блок - это то, что у вас покрашено одним цветом. Каждый период - это свой блок. Сколько у вас максимально может быть записей в каждом сеансе? Допустим, 134. Прибавьте еще немного, например, пусть будет 170. Тогда берем мою формулу, вставляем ее в ячейку в строке 2, меняем 99 на 170, а 98 на 169 и протягиваем вниз. Но по-любому на полмиллиона строк работать быстро не будет

Автор - _Boroda_
Дата добавления - 20.09.2017 в 13:53
evgven Дата: Четверг, 21.09.2017, 10:43 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, Спасибо за пояснение,как раз это я не понимал.. Еще раз проверил формулу на моей таблице. Вроде начало получаться.Вот прикладываю файл опять кусок реальной таблицы(всю не получится скинуть из за объема) Я понял,что формула работает корректно,если выстроить сортировку по возрастанию сначала времени,потом Пофамильно,а потом дату. Точнее не прикладываю файл а отсылаю Вам на почту,объем не позволяет..
 
Ответить
Сообщение_Boroda_, Спасибо за пояснение,как раз это я не понимал.. Еще раз проверил формулу на моей таблице. Вроде начало получаться.Вот прикладываю файл опять кусок реальной таблицы(всю не получится скинуть из за объема) Я понял,что формула работает корректно,если выстроить сортировку по возрастанию сначала времени,потом Пофамильно,а потом дату. Точнее не прикладываю файл а отсылаю Вам на почту,объем не позволяет..

Автор - evgven
Дата добавления - 21.09.2017 в 10:43
evgven Дата: Четверг, 21.09.2017, 10:55 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, И еще вопрос по количеству блоков. Подкрашивал я в примере,для наглядности. а вообще ,если посмотрите реальную таблицу Идет список по порядку(который можно сорировать) и есть разбиение по фамилиям, датам,времени начала сеанса и потом временных отметок.. Каждый оператор за день делает от 5 до 20 сеансов и в каждом сеансе от 5 до 150 отметок... поэтому на всякий случай я поставил 200 кол-во записей в блоке. Не знаю правильно или нет,но сейчас отметки формула ставит верно в сравнении с ручной проверкой..
 
Ответить
Сообщение_Boroda_, И еще вопрос по количеству блоков. Подкрашивал я в примере,для наглядности. а вообще ,если посмотрите реальную таблицу Идет список по порядку(который можно сорировать) и есть разбиение по фамилиям, датам,времени начала сеанса и потом временных отметок.. Каждый оператор за день делает от 5 до 20 сеансов и в каждом сеансе от 5 до 150 отметок... поэтому на всякий случай я поставил 200 кол-во записей в блоке. Не знаю правильно или нет,но сейчас отметки формула ставит верно в сравнении с ручной проверкой..

Автор - evgven
Дата добавления - 21.09.2017 в 10:55
_Boroda_ Дата: Четверг, 21.09.2017, 16:33 | Сообщение № 17
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11327
Репутация: 4676 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Точнее не прикладываю файл а отсылаю Вам на почту

Вас спасло от замечания только то, что никакого файла мне не пришло. Прочитайте Правила форума. Особенно внимательно п.5о


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

Вас спасло от замечания только то, что никакого файла мне не пришло. Прочитайте Правила форума. Особенно внимательно п.5о

Автор - _Boroda_
Дата добавления - 21.09.2017 в 16:33
Мир MS Excel » Вопросы и решения » Вопросы по Excel » ВРЕМЯ ? Выделение строк по условию временного интервала (Формулы/Formulas)
Страница 1 из 11
Поиск:

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