Помогите плз со след задачей. Поступают таблицы с данными: ФИО, телефон, оклад, серия и номер паспорта. От файла к файлу порядок столбцов в таблице может меняться; таблица может начинаться не с первой строки и не с первого столбца, может не иметь шапки. Теперь вопрос, как заставить макрос определить тип данных по столбцам. Мозговой штурм открыт Заранее спасибо всем кто примет участие.
Помогите плз со след задачей. Поступают таблицы с данными: ФИО, телефон, оклад, серия и номер паспорта. От файла к файлу порядок столбцов в таблице может меняться; таблица может начинаться не с первой строки и не с первого столбца, может не иметь шапки. Теперь вопрос, как заставить макрос определить тип данных по столбцам. Мозговой штурм открыт Заранее спасибо всем кто примет участие.WeRiX
Сообщение отредактировал WeRiX - Четверг, 12.06.2014, 08:33
Гадалка бессильна, а за денежку внедрят нормальную систему к концу года. А сейчас дабы упростить себе жизнь, охота хоть немного кривого, но рабочего решения. И да сообщение немного подредактировал, облегчил)
Гадалка бессильна, а за денежку внедрят нормальную систему к концу года. А сейчас дабы упростить себе жизнь, охота хоть немного кривого, но рабочего решения. И да сообщение немного подредактировал, облегчил)WeRiX
Таблицу тока завтра могу скинуть. Хотя тут можно просто строчки 2 набросать и поэкспериментировать. Ну да ладно таблицу скину. А вот на счет кода, так нет его ). Мне хоть какие-нибудь наброски на сей счет, уже был бы очень признателен.
Таблицу тока завтра могу скинуть. Хотя тут можно просто строчки 2 набросать и поэкспериментировать. Ну да ладно таблицу скину. А вот на счет кода, так нет его ). Мне хоть какие-нибудь наброски на сей счет, уже был бы очень признателен.WeRiX
ikki, зачем стока желчи. Я уж понял, что конкретно от тебя я помощи бесплатной не дождусь, хотя у тебя возможно есть мысли по решению. Не нужно портить настроение ни себе, ни людям. И да я решил помочь коллегам упростить работу, т.к. немного в этом разбираюсь, и да я не готов платить ни за них, ни за работодателя. И я не прошу сразу готового решения, просто может у кого есть какие наброски, куски подходящего кода, просто мысли на счет логики перебора. Поделитесь, большое спасибо. Вместе решим задачу.
ikki, зачем стока желчи. Я уж понял, что конкретно от тебя я помощи бесплатной не дождусь, хотя у тебя возможно есть мысли по решению. Не нужно портить настроение ни себе, ни людям. И да я решил помочь коллегам упростить работу, т.к. немного в этом разбираюсь, и да я не готов платить ни за них, ни за работодателя. И я не прошу сразу готового решения, просто может у кого есть какие наброски, куски подходящего кода, просто мысли на счет логики перебора. Поделитесь, большое спасибо. Вместе решим задачу.WeRiX
ну, примеры я просил не зря. "минут за 10", конечно, не получится. это была гипербола.
основной затык пока: наличие в строках перед таблицей отдельных ячеек с "левой" информацией и наличие пустых столбцов (в примере org3.xls)
поэтому хотел бы уточнить: после последней собственно "табличной" ячейки могут быть строки с "левой" инфой? какой примерно объем такой инфы? можно ли использовать в макросе ориентир - не более одной строки с такими данными, после которых перед табличной частью есть одна или несколько пустых строк или это не всегда так?
и один из главных вопросов - все ли столбцы для каждой строки всегда заполнены? или могут быть случаи, когда для некоторых строк нет данных (пусто), к примеру, в столбце с паспортом или телефоном?
по мелочи - что за "ооо этта" в org2.xlsx? что там ещё может быть?
схема алгоритма представляется примерно такая: для каждого файла берем usedrange листа. в этом usedrange находим последнюю заполненную ячейку начиная с этой ячейки движемся влево для каждого столбца берем значение из нижней ячейки плюс одно-два значения из ячеек сверху. проверяем соответствие одному из шаблонов (десять цифр и 7-кой/8-кой в начале - телефон, три слова из русских букв - ФИО и т.п.) конечно, есть нюансы (например, паспорт тоже может начинаться с 7-ки и тоже содержать 10 цифр без пробелов) всё это можно уточнять и отлаживать. ну и - вверх до первой заполненной или границы листа.
кстати, вариантов "с шапкой" вообще нет. придумывать? или нет?
жду ответов на вопросы. желательно - на все подряд.
ну, примеры я просил не зря. "минут за 10", конечно, не получится. это была гипербола.
основной затык пока: наличие в строках перед таблицей отдельных ячеек с "левой" информацией и наличие пустых столбцов (в примере org3.xls)
поэтому хотел бы уточнить: после последней собственно "табличной" ячейки могут быть строки с "левой" инфой? какой примерно объем такой инфы? можно ли использовать в макросе ориентир - не более одной строки с такими данными, после которых перед табличной частью есть одна или несколько пустых строк или это не всегда так?
и один из главных вопросов - все ли столбцы для каждой строки всегда заполнены? или могут быть случаи, когда для некоторых строк нет данных (пусто), к примеру, в столбце с паспортом или телефоном?
по мелочи - что за "ооо этта" в org2.xlsx? что там ещё может быть?
схема алгоритма представляется примерно такая: для каждого файла берем usedrange листа. в этом usedrange находим последнюю заполненную ячейку начиная с этой ячейки движемся влево для каждого столбца берем значение из нижней ячейки плюс одно-два значения из ячеек сверху. проверяем соответствие одному из шаблонов (десять цифр и 7-кой/8-кой в начале - телефон, три слова из русских букв - ФИО и т.п.) конечно, есть нюансы (например, паспорт тоже может начинаться с 7-ки и тоже содержать 10 цифр без пробелов) всё это можно уточнять и отлаживать. ну и - вверх до первой заполненной или границы листа.
пс. а может - и описанной схемы достаточно? чем писать здесь ответы на все вопросы - м.б. проще ответить на них самому себе и реализовать предложенное в коде? впрочем, не настаиваю. а то опять неправильно подумаете
ппс. а, я чуть со счету сбился. если телефон с семеркой-восьмеркой в начале, то это 11 цифр. а в паспорте 10. всё равно - могут ведь и в 10-значном формате телефоны. так что не суть. всё равно как-то различать надо будет. если есть соображения по этому поводу - выкладывайте.
пс. а может - и описанной схемы достаточно? чем писать здесь ответы на все вопросы - м.б. проще ответить на них самому себе и реализовать предложенное в коде? впрочем, не настаиваю. а то опять неправильно подумаете
ппс. а, я чуть со счету сбился. если телефон с семеркой-восьмеркой в начале, то это 11 цифр. а в паспорте 10. всё равно - могут ведь и в 10-значном формате телефоны. так что не суть. всё равно как-то различать надо будет. если есть соображения по этому поводу - выкладывайте.ikki
помощь по Excel и VBA ikki@fxmail.ru, icq 592842413, skype alex.ikki
Сообщение отредактировал ikki - Четверг, 12.06.2014, 14:48
после последней собственно "табличной" ячейки могут быть строки с "левой" инфой?
Да могут быть, но там цифры меньше шести знаков. их не сложно отсечь условием. с этим не заморачиваемся
Цитата
какой примерно объем такой инфы?
до 1500 строк в основном
Цитата
можно ли использовать в макросе ориентир
нет я такой не нашел) кому как вздумается тот так и заполняет, бесполезно уже что-то требовать.
Цитата
и один из главных вопросов - все ли столбцы для каждой строки всегда заполнены?
всегда одинаковое кол-во строк в столбцах фио и паспорт, телефон может быть не у каждого либо такой столбец вообще может отсутствовать
Цитата
что за "ооо этта"
это название вымышленной организации))
тут указали про особенности русских фио вот замутил с функцией find относительно фио, однако из разряда очень редкого, бывают списки из одного двух людей и у людей не бывает отчества - свихнуться все учесть)
окончанияФИО = Array("вич", "вна") For Each a In окончанияФИО Set ПоискФИО = .Cells.Find(What:=a, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not ПоискФИО Is Nothing Then столбецФИО = ПоискФИО.Column: Exit For Next
но как ее заставить искать по окончанию строки или началу иль середины, касаемо кодов операторов??? а то могут быть промахи
Лан всем спс кто сегодня откликнулся, надо уже и праздновать идти. Всех с праздником!!!
Цитата
после последней собственно "табличной" ячейки могут быть строки с "левой" инфой?
Да могут быть, но там цифры меньше шести знаков. их не сложно отсечь условием. с этим не заморачиваемся
Цитата
какой примерно объем такой инфы?
до 1500 строк в основном
Цитата
можно ли использовать в макросе ориентир
нет я такой не нашел) кому как вздумается тот так и заполняет, бесполезно уже что-то требовать.
Цитата
и один из главных вопросов - все ли столбцы для каждой строки всегда заполнены?
всегда одинаковое кол-во строк в столбцах фио и паспорт, телефон может быть не у каждого либо такой столбец вообще может отсутствовать
Цитата
что за "ооо этта"
это название вымышленной организации))
тут указали про особенности русских фио вот замутил с функцией find относительно фио, однако из разряда очень редкого, бывают списки из одного двух людей и у людей не бывает отчества - свихнуться все учесть)
окончанияФИО = Array("вич", "вна") For Each a In окончанияФИО Set ПоискФИО = .Cells.Find(What:=a, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not ПоискФИО Is Nothing Then столбецФИО = ПоискФИО.Column: Exit For Next
но как ее заставить искать по окончанию строки или началу иль середины, касаемо кодов операторов??? а то могут быть промахи
Лан всем спс кто сегодня откликнулся, надо уже и праздновать идти. Всех с праздником!!!WeRiX
Вот ща пришла мысль, если все надписи на русском заменить на какие-нить китайские иероглифы, то человеческий мозг все равно поймет где и что находится. И с цифренными данными тож человеку сразу понятно где и что. Почему ж так сложно описать логику мышления в коде. Я в этих если уже запутался, должно ж быть что-то простое и гениальное)) Как заставить код видеть целое и переходить к частному. Эт так мысли далеко не трезвого человека))
Вот ща пришла мысль, если все надписи на русском заменить на какие-нить китайские иероглифы, то человеческий мозг все равно поймет где и что находится. И с цифренными данными тож человеку сразу понятно где и что. Почему ж так сложно описать логику мышления в коде. Я в этих если уже запутался, должно ж быть что-то простое и гениальное)) Как заставить код видеть целое и переходить к частному. Эт так мысли далеко не трезвого человека))WeRiX
нагородил сам не понял половину чего, но вроде с самым легким примером работает
Sub интеллект() СокрОрг = Array("""", "ооо", "зао", "оао") With Worksheets(1) Set rFndRng = .Cells.Find(What:="*", LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If rFndRng Is Nothing Then MsgBox "Лист не содержит данных", vbInformation, "Информация": Exit Sub ' lFirstRow = rFndRng.Row: lFirstCol = rFndRng.Column For Each org In СокрОрг Set ПоискОрганизации = .Cells.Find(What:=org, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not ПоискОрганизации Is Nothing Then Организация = ПоискОрганизации: Exit For Next For a = .UsedRange.Column To .UsedRange.Columns.Count lastrow = .Cells(Rows.Count, a).End(xlUp).Row If .Range(.Cells(.UsedRange.Row, a), .Cells(lastrow, a)) Is Nothing Then GoTo qaz For b = .UsedRange.Row To .UsedRange.Rows.Count If .Cells(b, a) = "" Then GoTo qaz1 If IsNumeric(.Cells(b, a)) = True Then If Len(.Cells(b, a)) < 5 Then GoTo qaz If Len(.Cells(b, a)) <= 6 Then Set Оклад = .Range(.Cells(b, a), .Cells(lastrow, a)): GoTo qaz
If Len(.Cells(b, a)) = 10 Then For Each Stroka In .Range(.Cells(b, a), .Cells(lastrow, a)) If Left(Stroka, 1) = "9" Then tel = tel + 1 Else pas = pas + 1 End If Next If pas > tel Then Set Паспорт = .Range(.Cells(b, a), .Cells(lastrow, a)) tel = 0 pas = 0 GoTo qaz Else Set Телефон = .Range(.Cells(b, a), .Cells(lastrow, a)) tel = 0 pas = 0 GoTo qaz End If End If Else If Not ПоискОрганизации Is Nothing Then If ПоискОрганизации.Column = a Then GoTo qaz End If If Len(.Cells(b, a)) < 10 Then GoTo qaz For Each fio In .Range(.Cells(b, a), .Cells(lastrow, a)) n = Len(fio) probel = Replace(fio, " ", "") m = Len(probel) k = n - m If k = 2 Then probel2 = probel2 + 1 ElseIf k = 1 Then probel1 = probel1 + 1 Else GoTo qaz End If Next If probel2 > probel1 Then Set фио = .Range(.Cells(b, a), .Cells(lastrow, a)): GoTo qaz GoTo qaz End If qaz1: Next qaz: Next last = .Cells.SpecialCells(xlLastCell).Row .Cells(last + 1, фио.Column) = "фио" .Cells(last + 1, Оклад.Column) = "Оклад" .Cells(last + 1, Паспорт.Column) = "Паспорт" .Cells(last + 1, Телефон.Column) = "Телефон" .Cells(last + 1, ПоискОрганизации.Column) = "Организация" End With End Sub
идем потихоньку)
нагородил сам не понял половину чего, но вроде с самым легким примером работает
Sub интеллект() СокрОрг = Array("""", "ооо", "зао", "оао") With Worksheets(1) Set rFndRng = .Cells.Find(What:="*", LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If rFndRng Is Nothing Then MsgBox "Лист не содержит данных", vbInformation, "Информация": Exit Sub ' lFirstRow = rFndRng.Row: lFirstCol = rFndRng.Column For Each org In СокрОрг Set ПоискОрганизации = .Cells.Find(What:=org, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not ПоискОрганизации Is Nothing Then Организация = ПоискОрганизации: Exit For Next For a = .UsedRange.Column To .UsedRange.Columns.Count lastrow = .Cells(Rows.Count, a).End(xlUp).Row If .Range(.Cells(.UsedRange.Row, a), .Cells(lastrow, a)) Is Nothing Then GoTo qaz For b = .UsedRange.Row To .UsedRange.Rows.Count If .Cells(b, a) = "" Then GoTo qaz1 If IsNumeric(.Cells(b, a)) = True Then If Len(.Cells(b, a)) < 5 Then GoTo qaz If Len(.Cells(b, a)) <= 6 Then Set Оклад = .Range(.Cells(b, a), .Cells(lastrow, a)): GoTo qaz
If Len(.Cells(b, a)) = 10 Then For Each Stroka In .Range(.Cells(b, a), .Cells(lastrow, a)) If Left(Stroka, 1) = "9" Then tel = tel + 1 Else pas = pas + 1 End If Next If pas > tel Then Set Паспорт = .Range(.Cells(b, a), .Cells(lastrow, a)) tel = 0 pas = 0 GoTo qaz Else Set Телефон = .Range(.Cells(b, a), .Cells(lastrow, a)) tel = 0 pas = 0 GoTo qaz End If End If Else If Not ПоискОрганизации Is Nothing Then If ПоискОрганизации.Column = a Then GoTo qaz End If If Len(.Cells(b, a)) < 10 Then GoTo qaz For Each fio In .Range(.Cells(b, a), .Cells(lastrow, a)) n = Len(fio) probel = Replace(fio, " ", "") m = Len(probel) k = n - m If k = 2 Then probel2 = probel2 + 1 ElseIf k = 1 Then probel1 = probel1 + 1 Else GoTo qaz End If Next If probel2 > probel1 Then Set фио = .Range(.Cells(b, a), .Cells(lastrow, a)): GoTo qaz GoTo qaz End If qaz1: Next qaz: Next last = .Cells.SpecialCells(xlLastCell).Row .Cells(last + 1, фио.Column) = "фио" .Cells(last + 1, Оклад.Column) = "Оклад" .Cells(last + 1, Паспорт.Column) = "Паспорт" .Cells(last + 1, Телефон.Column) = "Телефон" .Cells(last + 1, ПоискОрганизации.Column) = "Организация" End With End Sub
ой! у Вас закончились праздники? тогда я тоже чего-то нагородил.
в тестовом варианте - работает не с файлами, а с листами в одной книге (начиная со второго) результат - на первом листе. вроде без комментариев всё должно быть понятно.
ой! у Вас закончились праздники? тогда я тоже чего-то нагородил.
в тестовом варианте - работает не с файлами, а с листами в одной книге (начиная со второго) результат - на первом листе. вроде без комментариев всё должно быть понятно.ikki
Это конечно мощно) я ведь отчаялся искать что-то, что работает с шаблонами. В одном файле заменил все буквы на Х, цифры на ноль кроме первых, и решил строить логику исходя из структуры и местонахождения написанного-вышло конечно слабо. Этот regexp его ж выкладывали выше, а я чет быстро пробежался по нему и не принял его в расчет - все новое сложное) Все что я избегал ikki, взял и использовал, с массивами я тож на Вы) лан пойду разбираться Спасибо ikki, время - его, наверное, много на это ушло, еще раз спс, что мучаешься с этим.
Это конечно мощно) я ведь отчаялся искать что-то, что работает с шаблонами. В одном файле заменил все буквы на Х, цифры на ноль кроме первых, и решил строить логику исходя из структуры и местонахождения написанного-вышло конечно слабо. Этот regexp его ж выкладывали выше, а я чет быстро пробежался по нему и не принял его в расчет - все новое сложное) Все что я избегал ikki, взял и использовал, с массивами я тож на Вы) лан пойду разбираться Спасибо ikki, время - его, наверное, много на это ушло, еще раз спс, что мучаешься с этим.WeRiX