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

Вход

Регистрация

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

 

= Мир MS Excel/Преобразовать неизвестный формат ячейки в формат времени - Мир MS Excel

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

Excel 2007
Здравствуйте.
Есть файл детализации вызовов от мобильного оператора (см. файл). Требуется сосчитать количество исходящих звонков продолжительностью более 10 секунд по каждому номеру в контракте (нижняя табличка в файле). Сложность в том, что в файле детализации продолжительность вызова, указанная в формате 00:00:00, Экселем как время не воспринимается. Никакие операции с форматом ячейки (перевод в формат времени или в формат [сс]) мне не помогли.

Максимум, до чего я додумался, это разбить текст по столбцам с разделителем ":" и затем просуммировать значения ячеек ("часы"*3600+"минуты"*60+"секунды"). Но какой-то это способ нетехнологичный ))) особенно, если учесть, что в оригинале на листе больше 5000 строк

Какие могут быть более технологичные варианты?
К сообщению приложен файл: Operations_01.xlsx(10.6 Kb)


Сообщение отредактировал brener - Среда, 13.03.2019, 20:11
 
Ответить
СообщениеЗдравствуйте.
Есть файл детализации вызовов от мобильного оператора (см. файл). Требуется сосчитать количество исходящих звонков продолжительностью более 10 секунд по каждому номеру в контракте (нижняя табличка в файле). Сложность в том, что в файле детализации продолжительность вызова, указанная в формате 00:00:00, Экселем как время не воспринимается. Никакие операции с форматом ячейки (перевод в формат времени или в формат [сс]) мне не помогли.

Максимум, до чего я додумался, это разбить текст по столбцам с разделителем ":" и затем просуммировать значения ячеек ("часы"*3600+"минуты"*60+"секунды"). Но какой-то это способ нетехнологичный ))) особенно, если учесть, что в оригинале на листе больше 5000 строк

Какие могут быть более технологичные варианты?

Автор - brener
Дата добавления - 13.03.2019 в 20:07
Nic70y Дата: Среда, 13.03.2019, 20:16 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 5262
Репутация: 1143 ±
Замечаний: 0% ±

Excel 2013
так
Код
=СЧЁТЕСЛИМН(A2:A20;B25;G2:G20;"Исходящий*";I2:I20;">--00:00:10")
наверное
а точнее так
Код
=СЧЁТЕСЛИМН(A$2:A$20;B25;G$2:G$20;"Исходящий*";I$2:I$20;">--00:00:10")
типа


ЯД(poison) 41001841029809

Сообщение отредактировал Nic70y - Среда, 13.03.2019, 20:18
 
Ответить
Сообщениетак
Код
=СЧЁТЕСЛИМН(A2:A20;B25;G2:G20;"Исходящий*";I2:I20;">--00:00:10")
наверное
а точнее так
Код
=СЧЁТЕСЛИМН(A$2:A$20;B25;G$2:G$20;"Исходящий*";I$2:I$20;">--00:00:10")
типа

Автор - Nic70y
Дата добавления - 13.03.2019 в 20:16
krosav4ig Дата: Среда, 13.03.2019, 20:19 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 2049
Репутация: 852 ±
Замечаний: 0% ±

Excel 2007,2010,2013
brener, время в столбце записано текстом
выделите, столбец, ctrl+h найти : заменить на : заменить все


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
Сообщениеbrener, время в столбце записано текстом
выделите, столбец, ctrl+h найти : заменить на : заменить все

Автор - krosav4ig
Дата добавления - 13.03.2019 в 20:19
bmv98rus Дата: Среда, 13.03.2019, 20:22 | Сообщение № 4
Группа: Проверенные
Ранг: Старожил
Сообщений: 2134
Репутация: 360 ±
Замечаний: 20% ±

Excel 2013/2016
Ответ на вопрос темы ,
например домножить на 1 при помощи специальной вставки

ответ по не теме использовать другую формулу
Код
=SUMPRODUCT(($A$2:$A$20=B25)*(LEFT($G$2:$G$20;9)="Исходящий")*(--$I$2:$I$20>--"00:00:10"))


Замечательный медведь, процентов на 20.
 
Ответить
СообщениеОтвет на вопрос темы ,
например домножить на 1 при помощи специальной вставки

ответ по не теме использовать другую формулу
Код
=SUMPRODUCT(($A$2:$A$20=B25)*(LEFT($G$2:$G$20;9)="Исходящий")*(--$I$2:$I$20>--"00:00:10"))

Автор - bmv98rus
Дата добавления - 13.03.2019 в 20:22
brener Дата: Среда, 13.03.2019, 20:23 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
krosav4ig, блин! Гениально! Получилось! А можете объяснить принцип такой замены? А то мало ли, где еще подобный текстовый формат встретится.
 
Ответить
Сообщениеkrosav4ig, блин! Гениально! Получилось! А можете объяснить принцип такой замены? А то мало ли, где еще подобный текстовый формат встретится.

Автор - brener
Дата добавления - 13.03.2019 в 20:23
brener Дата: Среда, 13.03.2019, 20:25 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Nic70y, в то-то и дело, что в самостоятельно составленном файле эта формула благополучно работает. То есть, в ней самой ошибок нет. Кроме фиксирования диапазона, да. Это я в спешке пропустил. ))


Сообщение отредактировал brener - Среда, 13.03.2019, 20:33
 
Ответить
СообщениеNic70y, в то-то и дело, что в самостоятельно составленном файле эта формула благополучно работает. То есть, в ней самой ошибок нет. Кроме фиксирования диапазона, да. Это я в спешке пропустил. ))

Автор - brener
Дата добавления - 13.03.2019 в 20:25
brener Дата: Среда, 13.03.2019, 20:32 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Ответ на вопрос темы ,
например домножить на 1 при помощи специальной вставки

Хм... Ни разу такого не делал. Сейчас посмотрел и не понял, как домножить на 1 при помощи специальной вставки. )) Но спасибо, покопаюсь, найду.
 
Ответить
Сообщение
Ответ на вопрос темы ,
например домножить на 1 при помощи специальной вставки

Хм... Ни разу такого не делал. Сейчас посмотрел и не понял, как домножить на 1 при помощи специальной вставки. )) Но спасибо, покопаюсь, найду.

Автор - brener
Дата добавления - 13.03.2019 в 20:32
Nic70y Дата: Среда, 13.03.2019, 20:40 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 5262
Репутация: 1143 ±
Замечаний: 0% ±

Excel 2013
в то-то и дело, что в самостоятельно составленном файле эта формула благополучно работает
но Ваша же не работает.


ЯД(poison) 41001841029809
 
Ответить
Сообщение
в то-то и дело, что в самостоятельно составленном файле эта формула благополучно работает
но Ваша же не работает.

Автор - Nic70y
Дата добавления - 13.03.2019 в 20:40
_Boroda_ Дата: Среда, 13.03.2019, 20:42 | Сообщение № 9
Группа: Модераторы
Ранг: Местный житель
Сообщений: 14954
Репутация: 5916 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
выделите, столбец, ctrl+h найти : заменить на : заменить все

Я то же самое делаю немного иначе - выделяю столбец целиком, иду Данные - Текст по столбцам - жму Готово
Дело в том, что нам нужно каким-то образом "дернуть" значения в ячейках. Примерно как зайти в каждую (через F2, например) и нажать Ентер. Но это в каждую, а нам нужно сразу все
Поэтому подходит замена, текст по столбцам, умножение/деление на 1, прибавление/вычитание 0
Последние два делаются просто - в любую ячейку пишется 1 (или для 0 вообще ничего не пишется, хватит и пустой ячейки), ячейка копируется, выделяется нужный диапазон, спецвставка - умножить (или сложить для нуля). Иногда я еще ставлю галочку "Значения", но это в зависимости от необходимости переноса формата

Можно и ничего не меняя использовать почти Вашу формулу
Код
=СЧЁТЕСЛИМН(A2:A20;B25;G2:G20;"Исходящий*";I2:I20;">00:00:10я")
К сообщению приложен файл: Operations_01_1.xlsx(11.1 Kb)


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

Я то же самое делаю немного иначе - выделяю столбец целиком, иду Данные - Текст по столбцам - жму Готово
Дело в том, что нам нужно каким-то образом "дернуть" значения в ячейках. Примерно как зайти в каждую (через F2, например) и нажать Ентер. Но это в каждую, а нам нужно сразу все
Поэтому подходит замена, текст по столбцам, умножение/деление на 1, прибавление/вычитание 0
Последние два делаются просто - в любую ячейку пишется 1 (или для 0 вообще ничего не пишется, хватит и пустой ячейки), ячейка копируется, выделяется нужный диапазон, спецвставка - умножить (или сложить для нуля). Иногда я еще ставлю галочку "Значения", но это в зависимости от необходимости переноса формата

Можно и ничего не меняя использовать почти Вашу формулу
Код
=СЧЁТЕСЛИМН(A2:A20;B25;G2:G20;"Исходящий*";I2:I20;">00:00:10я")

Автор - _Boroda_
Дата добавления - 13.03.2019 в 20:42
krosav4ig Дата: Среда, 13.03.2019, 20:58 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 2049
Репутация: 852 ±
Замечаний: 0% ±

Excel 2007,2010,2013
как домножить на 1 при помощи специальной вставки

в пустую ячейку вписать 1, скопировать эту ячейку, выделить диапазон, в котором числовые данные (числа, дата, время) записаны как текст, специальной вставкой (Ctrl+Alt+V) вставить как значения со включенной опцией умножить или разделить
К сообщению приложен файл: 0926154.png(24.9 Kb)


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
Сообщение
как домножить на 1 при помощи специальной вставки

в пустую ячейку вписать 1, скопировать эту ячейку, выделить диапазон, в котором числовые данные (числа, дата, время) записаны как текст, специальной вставкой (Ctrl+Alt+V) вставить как значения со включенной опцией умножить или разделить

Автор - krosav4ig
Дата добавления - 13.03.2019 в 20:58
brener Дата: Среда, 13.03.2019, 21:14 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
_Boroda_, krosav4ig, спасибо за разъяснения.

Можно и ничего не меняя почти Вашу формулу использовать

Интересный вариант. Только мне первый, с переводом времени из текстового формата, больше нравится. По одной простой причине: мне думается, что правильнее файл подгонять под Эксель, а не Эксель под файл.
 
Ответить
Сообщение_Boroda_, krosav4ig, спасибо за разъяснения.

Можно и ничего не меняя почти Вашу формулу использовать

Интересный вариант. Только мне первый, с переводом времени из текстового формата, больше нравится. По одной простой причине: мне думается, что правильнее файл подгонять под Эксель, а не Эксель под файл.

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

2003; 2007; 2010; 2013 RUS
Полностью с Вами согласен, но здесь есть одно "но" - необходимость ручного вмешательства. Каждый раз после выгрузки Вам нужно будет совершить некие телодвижения. Это 1. утомляет, 2. заставляет держать необходимость этого в памяти (или записать где-то и не забыть прочитать), 3. ограничивает количество и качество пользователей файла (пример - а) Вы заболели и кому-то нужно это сделать, а она не знает как; б) Вы со страшных недосыпа и бодуна, это ж нужно напрячься и вспомнить). Я стараюсь у себя все делать так, чтобы был минимум телодвижений - скопировал, вставил, всё, результат готов, любая блондинка сможет

Кстати, еще вариант - рядом в пустом столбце L напишите
Код
=--I2
http://www.excelworld.ru/publ/hacks/tricks/17-1-0-9 , поставьте формат времени и протяните на 1000 строк вниз. И свою формулу перепишите на столбец L
Код
=СЧЁТЕСЛИМН(A2:A20;B25;G2:G20;"Исходящий*";L2:L20;">00:00:10")

При вставке новых данных столбец L не трогайте, там время само будет преобразовываться


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

Кстати, еще вариант - рядом в пустом столбце L напишите
Код
=--I2
http://www.excelworld.ru/publ/hacks/tricks/17-1-0-9 , поставьте формат времени и протяните на 1000 строк вниз. И свою формулу перепишите на столбец L
Код
=СЧЁТЕСЛИМН(A2:A20;B25;G2:G20;"Исходящий*";L2:L20;">00:00:10")

При вставке новых данных столбец L не трогайте, там время само будет преобразовываться

Автор - _Boroda_
Дата добавления - 13.03.2019 в 21:26
bmv98rus Дата: Среда, 13.03.2019, 21:27 | Сообщение № 13
Группа: Проверенные
Ранг: Старожил
Сообщений: 2134
Репутация: 360 ±
Замечаний: 20% ±

Excel 2013/2016
brener, на самом деле вариант из #2 рабочий и отличается от Вашего. Чуток, но отличается.
Чем хорош мой вариант - ему все равно текст или не текст в данных, плох он тем что помедленнее будет.


Замечательный медведь, процентов на 20.
 
Ответить
Сообщениеbrener, на самом деле вариант из #2 рабочий и отличается от Вашего. Чуток, но отличается.
Чем хорош мой вариант - ему все равно текст или не текст в данных, плох он тем что помедленнее будет.

Автор - bmv98rus
Дата добавления - 13.03.2019 в 21:27
sboy Дата: Четверг, 14.03.2019, 09:17 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 2482
Репутация: 698 ±
Замечаний: 0% ±

Excel 2010
[offtop]
кому-то нужно это сделать, а она не знает как

сексизм? :D


Яндекс: 410016850021169
 
Ответить
Сообщение[offtop]
кому-то нужно это сделать, а она не знает как

сексизм? :D

Автор - sboy
Дата добавления - 14.03.2019 в 09:17
_Boroda_ Дата: Четверг, 14.03.2019, 09:27 | Сообщение № 15
Группа: Модераторы
Ранг: Местный житель
Сообщений: 14954
Репутация: 5916 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
сексизм

Безусловно. На том стояли и стоять будем


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

Безусловно. На том стояли и стоять будем

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

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