Страницы: 1
RSS
Как создать свой формат ячейки для учёта времени, ЧЧ:ММ'CC"ДС
 
Здравствуйте, друзья.

Задача проста: в Microsoft Excel 2010 я хочу создать графики (столбиками), отображающие прогресс в занятиях бегом, плаванием и некоторыми другими видами активностей, допускающих отслеживание результатов с помощью секундомера.

Мой секундомер способен выгружать данные времени вида ЧЧ:ММ'CC"ДС (часы : минуты ' секунды " доли секунды, доли секунды измеряются от 0 до 100).

К сожалению попытка построить график по этим значениям показывает, что Microsoft Excel 2010 не понимает этот формат записи времени ни в одном из испробованных мной форматов ячейки. График у меня не строится.
Вот для примера ряд значений:
00:03'24"83
00:03'36"92
00:03'38"29
00:01'15"08
00:02'19"27
00:03'40"63
00:03'44"38
00:03'39"82

Если я правильно понимаю - необходимо создавать свой формат ячейки. Я пробовал, зарылся в мануалы, узнал очень много интересной информации об устройстве Microsoft Excel 2010, о форматах, о времени, и ничего из этого не помогло мне решить поставленную задачу. Прошу помощи у старших товарищей.
 
Хоть попытка была поискать по форуму? "формат времени доли секунды". Читайте правила форума. Надо.
Я сам - дурнее всякого примера! ...
 
Для более продвинутых посетителей темы посоветовал бы заглянуть в тему: http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=46336&MID=385420#message385420
Я сам - дурнее всякого примера! ...
 
:?:
 
Цитата
KuklP пишет:
Хоть попытка была поискать по форуму? "формат времени доли секунды". Читайте правила форума. Надо.

Я искал по форуму и не нашёл искомого. Всего несколько похожих тем и моему случаю они не помогли. Уточню задание:
Я хочу научить эксель понимать именно такой формат записи времени: 00:03'24"83, а не такой 00:03:24,83 и ни какой другой. Мне хотелось бы иметь возможность вставлять в файл время из секундомера без модификаций знаков преминания между значениями.
Если Вы знаете где обсуждался столь тривиальный вопрос - прошу указать ссылку.
По указанной ссылке обсуждается даже близко не относящийся к теме вопрос.

anvg,
В Вашем файле есть всего одно заполненное поле с формулой =ТДАТА, которая возвращает целое число, представляющее текущие дату и время. Это шутка или зачем?
Изменено: Georg7490 - 08.06.2013 18:52:17
 
Цитата
Georg7490 пишет:
Если я правильно понимаю - необходимо создавать свой формат ячейки.
Неправильно понимаете. Ваши данные записаны как текст, соответственно никакие "танцы с бубнами" с форматами ничего не даст. Чтобы построить график - необходимо сначала преобразовать Ваши данные в числа.
 
DV, Да, это то что нужно! Благодарю Вас за помощь, Вы меня очень сильно выручили! Сейчас попробую доработать Вашу формулу в исходной таблице.

Для аналогичных таблиц, где я точно знаю, что учтённое секундомером время никогда не превысит часа, т.е. мне не потребуется учёт часов, а так же долей секунд - как можно округлить или отбросить эти значения из того же исходного текстового формата ЧЧ:ММ'СС"ДС до числового значения ММ:CC?
Изменено: Georg7490 - 08.06.2013 20:18:28
 
Формулы в файле:
 
DV, Да, файл Ваш сильно пригодился. Почти получилась моя табличка уже.

2 вопроса по формату дат:

1. Как из текстового значения вида ДД.ММ.ГГ Ч:CC перевести в число в обратном прочтении вида ГГ.ММ.ДД ЧЧ:ММ (так удобно для сортировки), при этом заставить выводится нули перед часами, днями и месяцами (так удобно визуально, что бы строка с датой всегда имела фиксированную длину)

2. В прикреплённом файле оставлен один столбец. Это фрагмент выгрузки данных c КПК из спортивной программы, которая к сожалению писана Америкосом и представляет в выгрузку даты только в формате ММ/ДД/ГГГГ. Эксель это пытается читать в стандарте другого формата - ДД/ММ/ГГГГ и, как видно, некорректно распознаёт некоторые даты. Они автоматически выравниваются по правому краю столбца. Выделенный красным фрагмент переведён в формат ячейки вида ММ/ДД/ГГГГ. Прошу помочь перевести в число вида ГГГГ.ММ.ДД.
 
1. Что Вам мешает поставить формат ячейки ГГГГ.ММ.ДД чч:мм ?
Добавлено:
Покажите данные в файле, если они текстовые, то надо их переводить в число, а потом уже использовать формат.
2. В первоначальном варианте можно так:
Код
=ЕСЛИ(ЕЧИСЛО(A8);A8;--(ПСТР(A8;ПОИСК("/";A8)+1;ПОИСК("/";A8;4)-ПОИСК("/";A8))&ПСТР(A8;1;ПОИСК("/";A8))&ПРАВСИМВ(A8;4)))

Работает, но если еще подумать, то можно наверняка сократить.
Изменено: DV - 09.06.2013 15:37:18 (Неправильное прочтение п.1 предыдущего столбца)
 
DV, Ваша формула восхитительно работает. Вы великолепно владеете экселем, я бы сказал - Вы владыка экселя! :) Мне придётся прочитать дюжину книг и справочных материалов, провести массу человекочасов за напряжённой практикой, прежде чем удастся уразуметь работу формулы даже в таком виде, не говоря о её упрощениях. Вероятно, к тому времени я бы уже и забыл за решением какой задачи обратился к экселю. :) Всё же, действительно стоит учиться, но для решения насущной проблемы перенесения дневника на ПК Ваша скорая помощь просто неоценима! =)

Не будем распыляться, обсудим только второй вопрос, он видится самым сложным и фундаментально важным, только с его решением я наконец смогу корректно обработать накопленные данные:

Как я уже говорил, файл выгружается из программы на КПК с датами в виде ММ/ДД/ГГГГ. И когда я его открываю на ПК в экселе, то он сразу же интерпретирует видимые ему даты под свой стандарт ДД.ММ.ГГГГ. И в этом загвоздка. Ваше решение великолепно работает, но оно не учитывает разделение в интерпретации прочтения дат экселем. Как видно в исходном файле, эксель делает различия между двумя типами дат:

1) Даты любого года и месяца с числом месяца в интервале с 1 по 12 включительно. Эта часть дат экспортированных с КПК в формате ММ/ДД/ГГГГ эксель без разбора интерпретирует словно видит их как ДД.ММ.ГГГГ и в этом ошибка экселя, не учитываемая Вашей формулой.
Пример: Эксель читает 01/12/2009, которое на КПК означало 12 января 2009, а на компьютере автоматически ошибочно распознаётся как 1 декабря 2009, и записывает егокак 12.01.2009, т.е. меняет число и месяц местами автоматически.
Примета: Эти данные в исходном столбце записываются не через слеш, а через точку. Они же автоматически выравниваются по правому краю. Им же автоматически назначается формат ячейки из категории ДАТА вида *14.03.2001.
ОШИБКА: В этих датах эксель автоматически меняет местами месяц и день в отличии от остальных дат. Починить это сменой формата на ММ/ДД/ГГГГ мне не удалось, что бы хотя бы весь исходный столбец был одинакового Америкосовского формата... Тогда бы Ваша формула сработала на ура!

2) Даты любого месяца с числом месяца в интервале от 13 до конца месяца. Эти даты эксель не может интерпретировать как даты, потому что не может ошибочно посчитать число за месяц, потому что 13го и т.д. месяцев не существует в принятой системе разделения времени и поэтому оставляет эти записи в файле как есть.
Примета: Эти даты записаны через слеш, выровнены по левому краю, формат ячейки назначается ОБЩИЙ.
Здесь ошибки нет и Ваша формула работает идеально.

Извините, за излишние подробности, я постарался дать подробное описание проблемы. Я так понимаю нужно привести исходный столбец в единый исходный формат, что бы все даты были записаны одинаково в виде ММ/ДД/ГГГГ. Как это сделать - я могу только догадываться, знаний нюансов логики экселя мне не хватает. :(
 
Если файл выгружается в csv (это ни где Вы не упомянули, но вероятно это так) - открывайте его Экселем через импорт внешних данных->из текста, задавайте текстовый формат столбцам с этими датами. Или нужный формат дат (если возможно).
Ещё вариант - открывать макросом и делать с файлом что угодно - можно вообще сразу брать только результат анализа файла.
Изменено: Hugo - 10.06.2013 11:58:44
 
Может возможно задать формулой условие проверки на наличие разделителей "/" и "."
Даты через слеш - оставлять как есть.
Даты с точкой - "неправильно понятые экселем", их вернуть к исходному виду путём перемены мест числа и месяца  :?:
Изменено: Georg7490 - 10.06.2013 12:17:50
 
Hugo, Прикреплённый изначально файл и есть исходный файл выгрузки. Я только убрал все данные не относящиеся к проблеме с датами и сократил длину списка дат, оставив лишь ряд значений для примера. Сама же структура файла, его разрешение, формат ячеек, их заполнение оставленно без изменений.

К сожалению, выгрузка не изобилует какими-либо настройками и представляется только в старом *.xls формате, загрузить его как текст эксель не хочет уметь. Как иначе задавать форматы ячеек при открытии файла - мне не ведомо.
Изменено: Georg7490 - 10.06.2013 12:22:17 (произведена попытка загрузки)
 
Изначально файла нет. Он появился позже, уже после редактирования Вами.
Вообще я сильно сомневаюсь, что программа выгружает в честный xls. Вполне может быть что это текст с расширением xls - попробуйте его открыть блокнотом.
Если текст - то с ним поступайте как с csv.
Изменено: Hugo - 10.06.2013 12:25:21
 
Hugo,

Исходный файл WorkoutTrackerExport.xls был прекреплён мной 8 Июня 2013 23:23:26 в 9м сообщении. До сей поры оно не редактировалось.

Данный файл несколькими текстовыми редакторами (включая блокнот) открывается в не читаемом формате, всё там квадратиками и кракозябрами.

Импорт "как текст" данных из этого файла экселем не производится. Пробовал.
 
"2. В прикреплённом файле оставлен один столбец. Это фрагмент выгрузки данных " - т.е. Вы этот файл уже редактировали Экселем. Следовательно, это не тот файл, который выгрузила программа.
 
Тогда надо немного скорректировать формулу:
Код
=--ЕСЛИ(ЕЧИСЛО(A1);ТЕКСТ(A1;"ММ/ДД/ГГГГ");ПСТР(A1;ПОИСК("/";A1)+1;ПОИСК("/";A1;4)-ПОИСК("/";A1))&ПСТР(A1;1;ПОИСК("/";A1))&ПРАВСИМВ(A1;4))
Изменено: DV - 10.06.2013 13:15:10
 
Hugo, Всё верно. Я неправильно Вас понял, вы имели ввиду редактирование файла, а не сообщения. Я отыскал исходный файл выгрузки. Он, действительно, открывается блокнотом в относительно сносном прочтении! Не меняя разрешения, я импортировал этот файл как текст указав кодировку 1251, разделители знаком табуляции (что бы это ни значило) и указал формат МДГ и файл, к моей великой радости, открылся экселем в читаемом виде! И даты стали все в одном порядке!!!

Только даты теперь записались не через слеш, а через точку. Всё же я попробовал. Добавил столбец рядом с исходными датами в котором применил формулу многоуважаемого DV. Она сработала, не смотря на то что в ней присуствовало условие поиска "/", а не ".". Забавно.

Я очень рад. Друзья. Вы мне очень помогли! Сейчас я вынужден отойти, а когда вернусь - приступлю к детальной проработке данных. На первый взгляд всё в файле кажется корректным!

Разрешите поблагодарить Вас за оказанное внимание. Я желаю Вам всего самого наилучшего, здоровья и процветания, что бы Вам часто встречались люди, добрая воля и сильные качества которых оказались востребованными в разрешении актуализировавшихся препядствий на жизненном пути, преодолевая которые самостоятельно или с дружественной помощью, мы как растём, так и больше ценим достигнутые плоды, которые в свою очередь можем возложить на алтарь добрых дел для достижения новых результатов, в процессе которых и происходит наша жизнь, добра и радости в которой я желаю Вам от всей души! :)
 
DV, Эта формула что-то переводит всякую дату в одно целое число.

9/17/2009 40073
9/15/2009 40071
8/27/2009 40052
8/25/2009 40050
8/20/2009 40045
 
Так даты выглядят при формате "Общий". Поставьте формат даты.
Цитата
Georg7490 пишет:
...Только даты теперь записались не через слеш, а через точку....
Если у Вас даты в ячейке, а не текст, то просто поменяйте формат ячеек и не надо никаких формул.
Изменено: DV - 10.06.2013 13:25:28
 
Цитата
Georg7490 пишет:
формула что-то переводит всякую дату в одно целое число
В Excel дата и есть число ))
Страницы: 1
Наверх