Экспорт и последующий анализ данных spc

@Cortes1984 · Регистрация: 01.09.2013

Author24 — интернет-сервис помощи студентам

приветствую!

программист я не очень)) но имеется желание облегчить себе жизнь)
собственно суть:
имеем файл базы данных spc (лог системы контроля параметров) во вложении, данный фаил содержит примерно сутки работы системы количество строк всегда 10002 количество столбцов при преобразовании может варьироваться
необходимо:
1. наладить импорт из данного файла в таблицу Excel, ну или экспорт (с этим у меня пока самая большая проблема)
предполагаю что в конечной таблице должна открываться юзерформа где можно указать путь к файлу из которого нужно взять данные при этом копируемые данные должны помещаться в начало листа
2.после переноса в основной файл должны ракладываться в нормальные столбцы и проверятся на ряд параметров : Во первых должны удалятся строки содержащие хотя бы одно 0 значение, во вторых в конце массива могут содержаться дубли уже внесенной ранее информции т.к. лог снимается вручную втретих нужно проводить проверку на наполнение листа т.е при достижении 1кк строк начинать новый лист.
3. на отдельном листе или в юзерформе создать группу графиков с изменяемым интервалом дат т.е забиваем дата/время начало-дата/время конец и получаем графическое отображение изменений данных параметров.

собственно вопросы следующие:
насколько это реализуемо средствами excel/vba
как реализовать п 1.

п.2 сейчас потихоньку делаю сам, хотя как конролировать наполнение листа тоже пока не представляю.

toiai · 11.09.2013, 23:33

Сейчас только увидел есть проблемы со временем не 24 часовом формате. Надо думать...

@Cortes1984 · 12.09.2013, 09:44 **[ТС]**

toiai, в том то и дело что когда прогоняем разложение столбца А руками все ОК получаем 2 столбца, а когда работает макрос получается 3.

Добавлено через 1 час 8 минут
причем если отменить сделанные изменения, и запустить записанный макрорекордером макрос получаем опять 3 столбца

@Igor_Tr · 12.09.2013, 10:55

To Cortes1984. А не решает проблему настройка форматов даты системы? Или это родной Вам формат? Если да - тогда форматировать к нужному при вставке.
И у меня такой еще вопрос. Вы это все делаете для графиков. Графики у вас в пределах одних суток? Если да - не проблема. Есле период времени затягивает часть одной сутки и часть другой - нужен свой формат, или время в 00:00:00 тоже будет 0.

@Cortes1984 · 12.09.2013, 11:15 **[ТС]**

на работе вот так, дома в принципе тоже, родной формат времени 24 часовой, пишу слева направо

я кстати опять искалку пустых ячеек сломал

на этот раз в крайнем файле toiai.

создал пустой файл, удалил 3 лист второй переименовал в DataLogSPC и перенес модуль с макросом, искалка не работает теперь)

@Cortes1984 · 12.09.2013, 12:13 **[ТС]**

в общем путем длительного тыканья пальцем в небо получилось следующее:

Кликните здесь для просмотра всего текста

Visual Basic

With .Sheets(1)
                .Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
                .Range("A3:A" & .UsedRange.Rows.Count).Replace What:="  ", Replacement:=";", LookAt:=xlPart, SearchOrder:=xlByRows
                .Range("A3:A" & .UsedRange.Rows.Count).TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _
                        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
                        Semicolon:=True, Comma:=False, Space:=False, Other:=True, FieldInfo _
                        :=Array(Array(1, 1), Array(1, 1)), TrailingMinusNumbers:=True
                .Range("B3:B" & .UsedRange.Rows.Count).NumberFormat = "hh:mm:ss"
                .Range("A3:A" & .UsedRange.Rows.Count).NumberFormat = "dd/mm/yyyy"
                .Range(.[a3], .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count)).Copy
            End With

АМ/РМ все равно откуда то вылазит но теперь его не отрывает в отдельный столбец, а потом корректно переводит в 24ч.формат

@Igor_Tr · 12.09.2013, 12:47

Ребус какой-то. У меня системная дата как у Вас - но отображает нормально. Попробуйте поэкспериментировать. Выделите ячейку с "косой" датой. Ctrl + 1, число, (все форматы), там измените отображение (вверху будете видеть). Получится - все это запишите рекордером в макрос.

@Cortes1984 · 12.09.2013, 12:50 **[ТС]**

Igor_Tr, в том то и дело я уже эти форматы как только не менял, делим столбец в ручную все ОК
, отменяем изменения запускаем запись с макрорекордера лезет третий столбец с АМ/РМ.

@Cortes1984 · 12.09.2013, 13:20 **[ТС]**

в общем вот как это выглядит:
запускаем пошагово макрос из этого поста toiai
останавливаемся на строке "Set WbCSV = ActiveWorkbook", перед нами разложенный на столбцы CSV.
вариант1: в ручную в этом файле в ячейке А1 меняем / на пробел, вставляем пустой столбец справа от столбца А, выделяем столбец A проводим разделение на столбцы по пробелу, получаем результата как на рисунке.
вариант2: выполняем код дальше до строки ".Columns(3).Delete"

@Igor_Tr · 12.09.2013, 13:25

Я уже плачу... Сильно.... Ничего не понимаю.... Выбрал ячейку, в которой дата моей системы. Включил рекондер. Вызвал формат ячейки. Выбрал "(все форматы)", в поле "Тип" руками записал (лень искать было) dd/mm/yyyy h:mm AM/PM. И получил. Вот макрос, и вот скины:

Visual Basic

Sub Makros()
    Range("A5").Select
    Selection.NumberFormat = "dd\/mm\/yyyy hh:mm AM/PM"
End Sub

@Cortes1984 · 12.09.2013, 14:09 **[ТС]**

Igor_Tr,
если не сложно сделайте пожалуйста следующее:
1 откройте исходный файл DataLogSPC.csv (тот который одним столбцом)
2 вручную разделите его на столбцы по "," (нам сейчас сами значения не важны важен только результат в первом столбце)
3 включите запись макроса
4 выделите столбец А (ну или первый в зависимости от того какой у Вас стоит стиль ссылок)
5 проведите замену " "(пробел) на ";"(в целом не важно на что, я менял на ";"), посмотрите результат (у меня получается так "09.09.2013;;15:46:11")
6 завершите запись макроса
7 сделайте отмену автозамены
8 выполните записанный макрос и посмотрите результат.

@Igor_Tr · 12.09.2013, 14:56

...откройте исходный файл DataLogSPC.csv

Попробую, только открыть можна по разному. Как? Из Excel - Даные - получить.. - из текста?
Можна открыть из блокнота. Ctrl + A, Ctrl + C, и на листе Excel - Ctrl + V. Это в смысле чистоты эксперимента. Тут могу накрутить, и получу опять то, что мне нужно.
Но самое главное - пункт 5. Зачем пробел на ";" ? Это для чего?

@Cortes1984 · 12.09.2013, 14:58 **[ТС]**

Igor_Tr, я открывал excel (просто 2 щелчком),";" для наглядности

@Igor_Tr · 12.09.2013, 15:05

Понятно. Но зачем пробел на ";"?

@Cortes1984 · 12.09.2013, 15:13 **[ТС]**

по сути мы производим замену разделителя столбцов с пробела на ";"
при работе руками мы получаем один сдвоенный разделитель следовательно если ConsecutiveDelimiter:=True то 2 столбца или если False то 3 столбца
при работе макроса мы получаем, внезапно, 1 сдвоенный и один одинарный разделитель то есть 3 или 4 столбца соответственно.

@Igor_Tr · 12.09.2013, 15:30

Святая Мария!!!!

А Вы не пробовали указать для замены на ";" не один пробел (" ", или space(1)), а два пробела (" ", или space(2))? Попробуйте, на всякий...

@Cortes1984 · 12.09.2013, 15:35 **[ТС]**

при ручном разложении этого сделать нельзя (в смысле разложить по 2м пробелам), программно почему то не работало поэтому я сделал замену 2х пробелов на ";" и разделение по ней, в целом код работает, да и в общем вопрос не в том как это обойти, а почему так происходит.

собственно этот код так и делает

@Igor_Tr · 12.09.2013, 15:39

Думаю, потому, что система между датой и временем ставит два пробела сама.

@Cortes1984 · 12.09.2013, 15:42 **[ТС]**

Igor_Tr,

мы видим в ячейке 2 пробела и раскладываем по ним ячейку на две
когда это делает excel получаем 2 столбца
когда это делает VBA получаем 3 столбца

почему?

@Igor_Tr · 12.09.2013, 16:00

Так понимаю, что Вы колете дату. А просто сделать? Весь ст. А загнать в двумерный массив и очистить (столбик). В массиве раскидать arr(i,1) - это дата, arr(i,2) - это время. Если нужно - там и форматировать. Добавить поле, закинуть массив.

Добавлено через 1 минуту
На Ваше почему один ответ - ищите проблему в коде (или подходе к решению). Не получается так - пробуйте по другому. Дорог много.

Добавлено через 13 минут
Такое впечатление, что Вы сцепились с Excel на смерть. И как в классических фильмах, пока побеждает плохой Excel.

Попробуйте вот это. В активной ячейке должна быть Ваша дата. Если делать в массиве - то это простая аналогия.

Visual Basic

Sub dateSEP()
   ActiveCell.Offset(0, 1).Value = Format(TimeValue(ActiveCell.Value), "h:mm:ss;@")
   ActiveCell.Value = Format(DateValue(ActiveCell.Value), "dd/mm/yy;@")
End Sub

@Cortes1984 · 12.09.2013, 16:02 **[ТС]**

Igor_Tr, мне кажется тут проблема даже не в коде (собственно код макрорекордером записан) а во взаимодействии Excel - VBA, ведь по факту получается что Excel и VBA видят разные значения в одной и той же ячейке, причем формат этой ячейки не принципиален я пробовал по всякому ее форматировать а результат был всегда один - время отображается неверно.

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

toiai 3218 / 967 / 223 Регистрация: 29.05.2010 Сообщений: 2,087
	11.09.2013, 23:33	81
	Сейчас только увидел есть проблемы со временем не 24 часовом формате. Надо думать... 0

@Cortes1984 0 / 0 / 0 Регистрация: 01.09.2013 Сообщений: 66
	12.09.2013, 09:44 [ТС]	82
	toiai, в том то и дело что когда прогоняем разложение столбца А руками все ОК получаем 2 столбца, а когда работает макрос получается 3. Добавлено через 1 час 8 минут причем если отменить сделанные изменения, и запустить записанный макрорекордером макрос получаем опять 3 столбца 0

@Igor_Tr 4377 / 661 / 36 Регистрация: 17.01.2010 Сообщений: 2,134
	12.09.2013, 10:55	83
	To Cortes1984. А не решает проблему настройка форматов даты системы? Или это родной Вам формат? Если да - тогда форматировать к нужному при вставке. И у меня такой еще вопрос. Вы это все делаете для графиков. Графики у вас в пределах одних суток? Если да - не проблема. Есле период времени затягивает часть одной сутки и часть другой - нужен свой формат, или время в 00:00:00 тоже будет 0. 0

@Igor_Tr 4377 / 661 / 36 Регистрация: 17.01.2010 Сообщений: 2,134
	12.09.2013, 12:47	86
	Ребус какой-то. У меня системная дата как у Вас - но отображает нормально. Попробуйте поэкспериментировать. Выделите ячейку с "косой" датой. Ctrl + 1, число, (все форматы), там измените отображение (вверху будете видеть). Получится - все это запишите рекордером в макрос. 0

@Cortes1984 0 / 0 / 0 Регистрация: 01.09.2013 Сообщений: 66
	12.09.2013, 12:50 [ТС]	87
	Igor_Tr, в том то и дело я уже эти форматы как только не менял, делим столбец в ручную все ОК , отменяем изменения запускаем запись с макрорекордера лезет третий столбец с АМ/РМ. 0

@Cortes1984 0 / 0 / 0 Регистрация: 01.09.2013 Сообщений: 66
	12.09.2013, 11:15 [ТС]	84
	на работе вот так, дома в принципе тоже, родной формат времени 24 часовой, пишу слева направо я кстати опять искалку пустых ячеек сломал на этот раз в крайнем файле toiai. создал пустой файл, удалил 3 лист второй переименовал в DataLogSPC и перенес модуль с макросом, искалка не работает теперь) Миниатюры 0

@Cortes1984 0 / 0 / 0 Регистрация: 01.09.2013 Сообщений: 66
	12.09.2013, 13:20 [ТС]	88
	в общем вот как это выглядит: запускаем пошагово макрос из этого поста toiai останавливаемся на строке "Set WbCSV = ActiveWorkbook", перед нами разложенный на столбцы CSV. вариант1: в ручную в этом файле в ячейке А1 меняем / на пробел, вставляем пустой столбец справа от столбца А, выделяем столбец A проводим разделение на столбцы по пробелу, получаем результата как на рисунке. вариант2: выполняем код дальше до строки ".Columns(3).Delete" Миниатюры 0

@Cortes1984 0 / 0 / 0 Регистрация: 01.09.2013 Сообщений: 66
	12.09.2013, 14:09 [ТС]	90
	Igor_Tr, если не сложно сделайте пожалуйста следующее: 1 откройте исходный файл DataLogSPC.csv (тот который одним столбцом) 2 вручную разделите его на столбцы по "," (нам сейчас сами значения не важны важен только результат в первом столбце) 3 включите запись макроса 4 выделите столбец А (ну или первый в зависимости от того какой у Вас стоит стиль ссылок) 5 проведите замену " "(пробел) на ";"(в целом не важно на что, я менял на ";"), посмотрите результат (у меня получается так "09.09.2013;;15:46:11") 6 завершите запись макроса 7 сделайте отмену автозамены 8 выполните записанный макрос и посмотрите результат. 0

@Igor_Tr 4377 / 661 / 36 Регистрация: 17.01.2010 Сообщений: 2,134
	12.09.2013, 14:56	91
	...откройте исходный файл DataLogSPC.csv Попробую, только открыть можна по разному. Как? Из Excel - Даные - получить.. - из текста? Можна открыть из блокнота. Ctrl + A, Ctrl + C, и на листе Excel - Ctrl + V. Это в смысле чистоты эксперимента. Тут могу накрутить, и получу опять то, что мне нужно. Но самое главное - пункт 5. Зачем пробел на ";" ? Это для чего? 0

@Cortes1984 0 / 0 / 0 Регистрация: 01.09.2013 Сообщений: 66
	12.09.2013, 14:58 [ТС]	92
	Igor_Tr, я открывал excel (просто 2 щелчком),";" для наглядности 0

@Igor_Tr 4377 / 661 / 36 Регистрация: 17.01.2010 Сообщений: 2,134
	12.09.2013, 15:05	93
	Понятно. Но зачем пробел на ";"? 0

@Cortes1984 0 / 0 / 0 Регистрация: 01.09.2013 Сообщений: 66
	12.09.2013, 15:13 [ТС]	94
	по сути мы производим замену разделителя столбцов с пробела на ";" при работе руками мы получаем один сдвоенный разделитель следовательно если ConsecutiveDelimiter:=True то 2 столбца или если False то 3 столбца при работе макроса мы получаем, внезапно, 1 сдвоенный и один одинарный разделитель то есть 3 или 4 столбца соответственно. 0

@Igor_Tr 4377 / 661 / 36 Регистрация: 17.01.2010 Сообщений: 2,134
	12.09.2013, 15:30	95
	Святая Мария!!!! А Вы не пробовали указать для замены на ";" не один пробел (" ", или space(1)), а два пробела (" ", или space(2))? Попробуйте, на всякий... 0

@Cortes1984 0 / 0 / 0 Регистрация: 01.09.2013 Сообщений: 66
	12.09.2013, 15:35 [ТС]	96
	при ручном разложении этого сделать нельзя (в смысле разложить по 2м пробелам), программно почему то не работало поэтому я сделал замену 2х пробелов на ";" и разделение по ней, в целом код работает, да и в общем вопрос не в том как это обойти, а почему так происходит. собственно этот код так и делает 0

@Igor_Tr 4377 / 661 / 36 Регистрация: 17.01.2010 Сообщений: 2,134
	12.09.2013, 15:39	97
	Думаю, потому, что система между датой и временем ставит два пробела сама. 0

@Cortes1984 0 / 0 / 0 Регистрация: 01.09.2013 Сообщений: 66
	12.09.2013, 15:42 [ТС]	98
	Igor_Tr, мы видим в ячейке 2 пробела и раскладываем по ним ячейку на две когда это делает excel получаем 2 столбца когда это делает VBA получаем 3 столбца почему? 0