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

Вход

Регистрация

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

 

= Мир MS Excel/Исправить формат экспортированных как текст чисел - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, китин  
Мир MS Excel » Вопросы и решения » Готовые решения » Исправить формат экспортированных как текст чисел (Excel)
Исправить формат экспортированных как текст чисел
Alex_ST Дата: Понедельник, 08.07.2013, 11:34 | Сообщение № 1
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3199
Репутация: 606 ±
Замечаний: 0% ±

2003
Макрос исправляет экспортированные как текст числовые данные в числа в соответствии с форматом ячеек.
При внедрении (экспорте) в Excel данных из отчётов внешних баз данных информация часто воспринимается ячейками листа как текст, а не как числа.
При этом на взгляд на листе всё выглядит нормально, но получаются ошибки в формулах листа, обрабатывающих такие данные, и фильтрах.
Чтобы формат ячейки нормально автоматически переопределился достаточно дважды кликнуть в каждую ячейку или воспользоваться спец.вставкой со сложением с 0 (или умножением на 1).
"Даблкликать" в каждую ячейку - это, конечно, не выход когда их больше десятка smile
В ручную делать спец.вставку - лениво.
Изначально я написал макрос, реализующий спец.вставку со сложением с 0:[vba]
Код
Sub Repair_Value_()   ' исправить(обновить)записи в ячейках чтобы приняли формат
' не все данные почему-то обрабатывает... <img src="http://s5.ucoz.net/sm/1/sad.gif" border="0" align="absmiddle" alt="sad" />
      On Error Resume Next
      Application.CutCopyMode = False   ' сбросить буфер обмена чтобы при ошибке копирования ничего не прибавилось к ячейкам спец.вставкой
      Intersect(ActiveWindow.RangeSelection, ActiveSheet.UsedRange).Cells.SpecialCells(xlCellTypeLastCell).Offset(1).Copy
      ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
      Application.CutCopyMode = False   ' только чтобы погасить пунктирную рамку выделения скопированного диапазона
      If Err Then MsgBox Err.Description, vbExclamation, "Не получилось!"
End Sub
[/vba]
Но он почему-то не всегда все данные обрабатывает и потому нельзя быть 100%-но уверенным в результате sad
Потом где-то кем-то был предложен другой подход
[offtop]Извините, ребята, не помню, с кем вместе и где это "полировалось", потому не могу назвать автора :([/offtop]
Этот макрос даёт достоверный результат всегда:[vba]
Код
Sub Repair_Value()   ' в выделенных ячейках исправить экспортированные как текст данные чтобы нормально опознались числа
    Dim rArea As Range
    On Error Resume Next
    ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select
    If Err Then Exit Sub
    With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With
    For Each rArea In Selection.Areas
       rArea.FormulaLocal = rArea.FormulaLocal
    Next rArea
    With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With
End Sub
[/vba]



С уважением,
Алексей
MS Excel 2003 - the best!!!


Сообщение отредактировал Alex_ST - Понедельник, 08.07.2013, 11:35
 
Ответить
СообщениеМакрос исправляет экспортированные как текст числовые данные в числа в соответствии с форматом ячеек.
При внедрении (экспорте) в Excel данных из отчётов внешних баз данных информация часто воспринимается ячейками листа как текст, а не как числа.
При этом на взгляд на листе всё выглядит нормально, но получаются ошибки в формулах листа, обрабатывающих такие данные, и фильтрах.
Чтобы формат ячейки нормально автоматически переопределился достаточно дважды кликнуть в каждую ячейку или воспользоваться спец.вставкой со сложением с 0 (или умножением на 1).
"Даблкликать" в каждую ячейку - это, конечно, не выход когда их больше десятка smile
В ручную делать спец.вставку - лениво.
Изначально я написал макрос, реализующий спец.вставку со сложением с 0:[vba]
Код
Sub Repair_Value_()   ' исправить(обновить)записи в ячейках чтобы приняли формат
' не все данные почему-то обрабатывает... <img src="http://s5.ucoz.net/sm/1/sad.gif" border="0" align="absmiddle" alt="sad" />
      On Error Resume Next
      Application.CutCopyMode = False   ' сбросить буфер обмена чтобы при ошибке копирования ничего не прибавилось к ячейкам спец.вставкой
      Intersect(ActiveWindow.RangeSelection, ActiveSheet.UsedRange).Cells.SpecialCells(xlCellTypeLastCell).Offset(1).Copy
      ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
      Application.CutCopyMode = False   ' только чтобы погасить пунктирную рамку выделения скопированного диапазона
      If Err Then MsgBox Err.Description, vbExclamation, "Не получилось!"
End Sub
[/vba]
Но он почему-то не всегда все данные обрабатывает и потому нельзя быть 100%-но уверенным в результате sad
Потом где-то кем-то был предложен другой подход
[offtop]Извините, ребята, не помню, с кем вместе и где это "полировалось", потому не могу назвать автора :([/offtop]
Этот макрос даёт достоверный результат всегда:[vba]
Код
Sub Repair_Value()   ' в выделенных ячейках исправить экспортированные как текст данные чтобы нормально опознались числа
    Dim rArea As Range
    On Error Resume Next
    ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select
    If Err Then Exit Sub
    With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With
    For Each rArea In Selection.Areas
       rArea.FormulaLocal = rArea.FormulaLocal
    Next rArea
    With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With
End Sub
[/vba]

Автор - Alex_ST
Дата добавления - 08.07.2013 в 11:34
Неопытный Дата: Вторник, 17.09.2013, 17:00 | Сообщение № 2
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Не всё правильно преобразуется в число, например когда системный разделитель стоит запятая, а импортировались числа с обоими видами разделителей (точка,запятая).
Например число такого ввида 3.000 (разделитель точка при системном разделителе запятая и перед числом
стоит апостроф).Это число располагается посередине ячейки, а не слева или справа.также число 1.2(разделитель точка при системном разделителе запятая,апострофа нет,excel показывает,что формат текстовый. Более точен такой приведенный здесь алгоритм:
http://www.planetaexcel.ru/forum....N_1=193
в сообщении #11
 
Ответить
СообщениеНе всё правильно преобразуется в число, например когда системный разделитель стоит запятая, а импортировались числа с обоими видами разделителей (точка,запятая).
Например число такого ввида 3.000 (разделитель точка при системном разделителе запятая и перед числом
стоит апостроф).Это число располагается посередине ячейки, а не слева или справа.также число 1.2(разделитель точка при системном разделителе запятая,апострофа нет,excel показывает,что формат текстовый. Более точен такой приведенный здесь алгоритм:
http://www.planetaexcel.ru/forum....N_1=193
в сообщении #11

Автор - Неопытный
Дата добавления - 17.09.2013 в 17:00
Alex_ST Дата: Вторник, 17.09.2013, 17:30 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3199
Репутация: 606 ±
Замечаний: 0% ±

2003
Неопытный,
я не понимаю цели Вашего поста.
Вы хотите подвигнуть меня к переработке моего макроса под чей-то чужой алгоритм, изложенный хоть и на очень мною уважаемом, но всё-таки стороннем форуме?
Так этого не будет, т.к. приведённая в старт-топике процедура вполне устраивает меня и большинство пользователей, с которыми я поделился ею.
К тому же, я предпочитаю при сборе данных из разных документов сначала хоть немного обрабатывать их на отдельном листе, а потом уже копировать в сборную таблицу (не говорю "сводную" чтобы не было разночтений). Да и вставляю я данные чаще всего спецвставкой чтобы были приняты мои форматы ячеек.
Так намного проще, чем потом в одной таблице, набранной по кускам из разных источников, искать, где разделитель - точка, а где перед нормальным числом стоит невидимый апостроф.



С уважением,
Алексей
MS Excel 2003 - the best!!!
 
Ответить
СообщениеНеопытный,
я не понимаю цели Вашего поста.
Вы хотите подвигнуть меня к переработке моего макроса под чей-то чужой алгоритм, изложенный хоть и на очень мною уважаемом, но всё-таки стороннем форуме?
Так этого не будет, т.к. приведённая в старт-топике процедура вполне устраивает меня и большинство пользователей, с которыми я поделился ею.
К тому же, я предпочитаю при сборе данных из разных документов сначала хоть немного обрабатывать их на отдельном листе, а потом уже копировать в сборную таблицу (не говорю "сводную" чтобы не было разночтений). Да и вставляю я данные чаще всего спецвставкой чтобы были приняты мои форматы ячеек.
Так намного проще, чем потом в одной таблице, набранной по кускам из разных источников, искать, где разделитель - точка, а где перед нормальным числом стоит невидимый апостроф.

Автор - Alex_ST
Дата добавления - 17.09.2013 в 17:30
DJ_Marker_MC Дата: Понедельник, 23.09.2013, 16:09 | Сообщение № 4
Группа: Друзья
Ранг: Ветеран
Сообщений: 991
Репутация: 213 ±
Замечаний: 0% ±

Excel 2019
Alex_ST, Спасибо за РЕШЕНИЕ. Очень удобно и полезно.
 
Ответить
СообщениеAlex_ST, Спасибо за РЕШЕНИЕ. Очень удобно и полезно.

Автор - DJ_Marker_MC
Дата добавления - 23.09.2013 в 16:09
=XYZ Дата: Вторник, 15.04.2014, 08:19 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Alex_ST, Круто, СПС. first
[offtop]Не берусь формулировать новую тему, боюсь "убежать" в сторону. Поэтому спрошу здесь. А, нельзя-ли проделать все тоже самое (формат ячейки), только из пользовательской формы? Нет, из формы все ЭТО работает на УРА!. Вот только, когда работаешь с NamPad, то вставляется по-умолчанию "," и решение какой-либо формулы работает только до второго знака. Если в форме (TextBox) вставляешь число, также работая с NamPad, содержащее знаков после запятой больше двух, то получается косяк-переделывает вводимое дробное число и меняет формат ячейки на числовой с округлением до 0 после запятой, хотя стоит общий. Прошу прощение за сумбур. А, может, я совсем все неправильно делаю? Подскажите. Приложу файлик примера, чтобы Вам не делать лишних движений. Спасибо![/offtop]
К сообщению приложен файл: 6387292.xls (43.5 Kb)


Сообщение отредактировал =XYZ - Среда, 16.04.2014, 05:52
 
Ответить
СообщениеAlex_ST, Круто, СПС. first
[offtop]Не берусь формулировать новую тему, боюсь "убежать" в сторону. Поэтому спрошу здесь. А, нельзя-ли проделать все тоже самое (формат ячейки), только из пользовательской формы? Нет, из формы все ЭТО работает на УРА!. Вот только, когда работаешь с NamPad, то вставляется по-умолчанию "," и решение какой-либо формулы работает только до второго знака. Если в форме (TextBox) вставляешь число, также работая с NamPad, содержащее знаков после запятой больше двух, то получается косяк-переделывает вводимое дробное число и меняет формат ячейки на числовой с округлением до 0 после запятой, хотя стоит общий. Прошу прощение за сумбур. А, может, я совсем все неправильно делаю? Подскажите. Приложу файлик примера, чтобы Вам не делать лишних движений. Спасибо![/offtop]

Автор - =XYZ
Дата добавления - 15.04.2014 в 08:19
Alex_ST Дата: Вторник, 15.04.2014, 12:12 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3199
Репутация: 606 ±
Замечаний: 0% ±

2003
К сожалению, из Вашего объяснения ничего не понял :(
Что за зверь такой NamPad ? Может быть, Вы имели в виду боковую секцию клавиатуры с цифрами? Так она называется NumPad
Я чаще всего именно её для ввода цифр и использую (NumLock выставлен по умолчанию при запуске) и никаких проблем с вводом данных куда бы то ни было никогда не было.
А файлик Ваш я посмотреть не могу - на работе параноики-сисадмины своей антивирью откоцывают от файлов модули с кодом VBA.



С уважением,
Алексей
MS Excel 2003 - the best!!!
 
Ответить
СообщениеК сожалению, из Вашего объяснения ничего не понял :(
Что за зверь такой NamPad ? Может быть, Вы имели в виду боковую секцию клавиатуры с цифрами? Так она называется NumPad
Я чаще всего именно её для ввода цифр и использую (NumLock выставлен по умолчанию при запуске) и никаких проблем с вводом данных куда бы то ни было никогда не было.
А файлик Ваш я посмотреть не могу - на работе параноики-сисадмины своей антивирью откоцывают от файлов модули с кодом VBA.

Автор - Alex_ST
Дата добавления - 15.04.2014 в 12:12
=XYZ Дата: Вторник, 15.04.2014, 12:30 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
[offtop]Да, я ошибся, прошу прощения. Именно NumPad. Ну, и видимо совсем замакросился. Смысл того, что хотел сказать, так это то, как сделать, чтобы и "," и "." всегда распознавалась как запятая. Просто при работе с клавиатурой где NumPad вынесен в боковую панель проблем нет, а на ноуте нужны дополнительные переключения (конечно если работаешь с русской раскладкой). Короче: ничего не переключая, не трогая "Fn", не зажимая "Shift" пользоваться одной клавишей при этом вводя цифры с TextBox в ячейку(ки), не обращая внимания на то, какая языковая раскладка на клаве, и не трогая "Fn". А, коды там такие.
В форме с одним TextBox и кнопкой выхода из формы:
[vba]
Код
Private Sub TextBox1_Change()
       [A1] = Me.TextBox1
Dim rArea As Range
       On Error Resume Next
       ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select
       If Err Then Exit Sub
       With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With
       For Each rArea In Selection.Areas
       rArea.FormulaLocal = rArea.FormulaLocal
       Next rArea
       With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub
[/vba]
В стандартном модуле для запуска формы с листа:
[vba]
Код
Sub Macro()
UserForm1.Show
End Sub
[/vba][/offtop]


Сообщение отредактировал =XYZ - Среда, 16.04.2014, 05:52
 
Ответить
Сообщение[offtop]Да, я ошибся, прошу прощения. Именно NumPad. Ну, и видимо совсем замакросился. Смысл того, что хотел сказать, так это то, как сделать, чтобы и "," и "." всегда распознавалась как запятая. Просто при работе с клавиатурой где NumPad вынесен в боковую панель проблем нет, а на ноуте нужны дополнительные переключения (конечно если работаешь с русской раскладкой). Короче: ничего не переключая, не трогая "Fn", не зажимая "Shift" пользоваться одной клавишей при этом вводя цифры с TextBox в ячейку(ки), не обращая внимания на то, какая языковая раскладка на клаве, и не трогая "Fn". А, коды там такие.
В форме с одним TextBox и кнопкой выхода из формы:
[vba]
Код
Private Sub TextBox1_Change()
       [A1] = Me.TextBox1
Dim rArea As Range
       On Error Resume Next
       ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select
       If Err Then Exit Sub
       With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With
       For Each rArea In Selection.Areas
       rArea.FormulaLocal = rArea.FormulaLocal
       Next rArea
       With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub
[/vba]
В стандартном модуле для запуска формы с листа:
[vba]
Код
Sub Macro()
UserForm1.Show
End Sub
[/vba][/offtop]

Автор - =XYZ
Дата добавления - 15.04.2014 в 12:30
Alex_ST Дата: Вторник, 15.04.2014, 14:54 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3199
Репутация: 606 ±
Замечаний: 0% ±

2003
[offtop]Понял Вас. Сам, пользуясь NumPad, постоянно наступаю на эти грабли.
Можно, наверное, попробовать поиграться с заданным в системе разделителем целой и дробной части (Настройки - Панель управления - Язык и региональные стандарты на вкладке "Форматы" кнопка "Дополнительные параметры...")
Вот только не знаю, не вылезут ли где-нибудь другие грабли из-за этого?
А конкретно в Вашем текстбоксе можно устроить программную автозамену запятой на точку с запретом ввода букв:
[vba]
Код
Private Sub TextBox1_Change()
    'by ZVI: http://www.planetaexcel.ru/forum.php?thread_id=7712
    'Ввод в TextBox только чисел в заданном диапазоне _
     с любым децимальным разделителем (можно и точку, и запятую) _
     и ограничением количества десятичных разрядов
    Const min = 0   ' минимальное значение
    Const max = 100000000   ' максимальное значение
    Const DecSYMB = 2   ' количество десятичных разрядов
    Dim DecSEP: DecSEP = Application.DecimalSeparator   ' децимальный разделитель приложения
    Dim VAL#, j%, sVAL$
    Static sOldVAL$
    On Error Resume Next
    sVAL = Replace((Replace(Replace(TextBox1.Value, " ", ""), ",", DecSEP)), ".", DecSEP)
    If Len(sVAL) = 0 Then sOldVAL = "": TextBox1.Value = "": Exit Sub
    TextBox1.Value = sVAL
    VAL = CDbl(sVAL)
    j = InStr(sVAL, DecSEP)
    If j > 0 Then j = Len(sVAL) - j
    If Err Or VAL < min Or VAL > max Or j > DecSYMB Then
       TextBox1.Value = sOldVAL
    Else
       sOldVAL = TextBox1.Value
    End If
End Sub
[/vba]
Но, вообще-то, здесь - это ОФФТОП. Так что дискуссию прекращаем, а то получим люлей от Сержа. И он будет абсолютно прав.[/offtop]



С уважением,
Алексей
MS Excel 2003 - the best!!!


Сообщение отредактировал Alex_ST - Вторник, 15.04.2014, 15:05
 
Ответить
Сообщение[offtop]Понял Вас. Сам, пользуясь NumPad, постоянно наступаю на эти грабли.
Можно, наверное, попробовать поиграться с заданным в системе разделителем целой и дробной части (Настройки - Панель управления - Язык и региональные стандарты на вкладке "Форматы" кнопка "Дополнительные параметры...")
Вот только не знаю, не вылезут ли где-нибудь другие грабли из-за этого?
А конкретно в Вашем текстбоксе можно устроить программную автозамену запятой на точку с запретом ввода букв:
[vba]
Код
Private Sub TextBox1_Change()
    'by ZVI: http://www.planetaexcel.ru/forum.php?thread_id=7712
    'Ввод в TextBox только чисел в заданном диапазоне _
     с любым децимальным разделителем (можно и точку, и запятую) _
     и ограничением количества десятичных разрядов
    Const min = 0   ' минимальное значение
    Const max = 100000000   ' максимальное значение
    Const DecSYMB = 2   ' количество десятичных разрядов
    Dim DecSEP: DecSEP = Application.DecimalSeparator   ' децимальный разделитель приложения
    Dim VAL#, j%, sVAL$
    Static sOldVAL$
    On Error Resume Next
    sVAL = Replace((Replace(Replace(TextBox1.Value, " ", ""), ",", DecSEP)), ".", DecSEP)
    If Len(sVAL) = 0 Then sOldVAL = "": TextBox1.Value = "": Exit Sub
    TextBox1.Value = sVAL
    VAL = CDbl(sVAL)
    j = InStr(sVAL, DecSEP)
    If j > 0 Then j = Len(sVAL) - j
    If Err Or VAL < min Or VAL > max Or j > DecSYMB Then
       TextBox1.Value = sOldVAL
    Else
       sOldVAL = TextBox1.Value
    End If
End Sub
[/vba]
Но, вообще-то, здесь - это ОФФТОП. Так что дискуссию прекращаем, а то получим люлей от Сержа. И он будет абсолютно прав.[/offtop]

Автор - Alex_ST
Дата добавления - 15.04.2014 в 14:54
=XYZ Дата: Вторник, 15.04.2014, 15:40 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
[offtop]Вот и я нашел. СПАСИБО KuklP
[vba]
Код
Private Sub TextBox1_Change()
      [A1] = Me.TextBox1
      Cells.Replace ".", ".", 2
Dim rArea As Range
      On Error Resume Next
      ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select
      If Err Then Exit Sub
      With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With
      For Each rArea In Selection.Areas
      rArea.FormulaLocal = rArea.FormulaLocal
      Next rArea
      With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With
End Sub
[/vba]
Добавил строчку к Вашему коду. Всем СПАСИБО![/offtop]


Сообщение отредактировал =XYZ - Вторник, 15.04.2014, 15:40
 
Ответить
Сообщение[offtop]Вот и я нашел. СПАСИБО KuklP
[vba]
Код
Private Sub TextBox1_Change()
      [A1] = Me.TextBox1
      Cells.Replace ".", ".", 2
Dim rArea As Range
      On Error Resume Next
      ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select
      If Err Then Exit Sub
      With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With
      For Each rArea In Selection.Areas
      rArea.FormulaLocal = rArea.FormulaLocal
      Next rArea
      With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With
End Sub
[/vba]
Добавил строчку к Вашему коду. Всем СПАСИБО![/offtop]

Автор - =XYZ
Дата добавления - 15.04.2014 в 15:40
Yujin Дата: Понедельник, 29.05.2017, 14:40 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Добрый день, не могли бы пояснить:
если я вместо
[vba]
Код
For Each rArea In Selection.Areas
    rArea.FormulaLocal = rArea.FormulaLocal
Next rArea
[/vba]

буду использовать
[vba]
Код
rAreaF.FormulaLocal = rAreaF.FormulaLocal
[/vba]
где rAreaF - это полный диапазон (который выделяли)
какие могут быть нюансы и грабли?
просто при использовании конструкции For Each ... Next в составе большого макроса почему то долго работает, хотя в отдельном файле все проходит быстро - почему - так и не разобрался
[p.s.]таблица примерно 800х10 ячеек


Сообщение отредактировал Yujin - Понедельник, 29.05.2017, 14:42
 
Ответить
СообщениеДобрый день, не могли бы пояснить:
если я вместо
[vba]
Код
For Each rArea In Selection.Areas
    rArea.FormulaLocal = rArea.FormulaLocal
Next rArea
[/vba]

буду использовать
[vba]
Код
rAreaF.FormulaLocal = rAreaF.FormulaLocal
[/vba]
где rAreaF - это полный диапазон (который выделяли)
какие могут быть нюансы и грабли?
просто при использовании конструкции For Each ... Next в составе большого макроса почему то долго работает, хотя в отдельном файле все проходит быстро - почему - так и не разобрался
[p.s.]таблица примерно 800х10 ячеек

Автор - Yujin
Дата добавления - 29.05.2017 в 14:40
Alex_ST Дата: Понедельник, 29.05.2017, 21:23 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3199
Репутация: 606 ±
Замечаний: 0% ±

2003
какие могут быть нюансы и грабли?
Как раз для того, чтобы обрабатывать не все ячейки выделенного диапазона, а только те, в которых константы, используется [vba]
Код
ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select
[/vba]После этого Selection - это обычно уже не один непрерывный диапазон, а несколько областей. Как раз каждую из них-то и обрабатывает цикл [vba]
Код
For Each rArea In Selection.Areas
    rArea.FormulaLocal = rArea.FormulaLocal
Next rArea
[/vba]Если Вы просто сразу ко всему непрерывному Selection примените [vba]
Код
Selection.FormulaLocal = Selection.FormulaLocal
[/vba], то работать должно, но как раз медленнее, чем при обработке только областей с константами



С уважением,
Алексей
MS Excel 2003 - the best!!!
 
Ответить
Сообщение
какие могут быть нюансы и грабли?
Как раз для того, чтобы обрабатывать не все ячейки выделенного диапазона, а только те, в которых константы, используется [vba]
Код
ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select
[/vba]После этого Selection - это обычно уже не один непрерывный диапазон, а несколько областей. Как раз каждую из них-то и обрабатывает цикл [vba]
Код
For Each rArea In Selection.Areas
    rArea.FormulaLocal = rArea.FormulaLocal
Next rArea
[/vba]Если Вы просто сразу ко всему непрерывному Selection примените [vba]
Код
Selection.FormulaLocal = Selection.FormulaLocal
[/vba], то работать должно, но как раз медленнее, чем при обработке только областей с константами

Автор - Alex_ST
Дата добавления - 29.05.2017 в 21:23
Yujin Дата: Вторник, 30.05.2017, 10:26 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Alex_ST, спасибо, я понял - не разобрался в коде до конца
ну у меня просто создание таблицы данных без пропусков, поэтому выделяю я весь диапазон с константами вручную (границы мне заранее известны)
я так понял в случае моем время расчета должно быть примерно одинаковым
 
Ответить
СообщениеAlex_ST, спасибо, я понял - не разобрался в коде до конца
ну у меня просто создание таблицы данных без пропусков, поэтому выделяю я весь диапазон с константами вручную (границы мне заранее известны)
я так понял в случае моем время расчета должно быть примерно одинаковым

Автор - Yujin
Дата добавления - 30.05.2017 в 10:26
Alex_ST Дата: Вторник, 30.05.2017, 11:14 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3199
Репутация: 606 ±
Замечаний: 0% ±

2003
P.S.
таблица примерно 800х10 ячеек
Такую мелочь процедура должна обрабатывать практически мгновенно.
А Вы случайно не удалили отключение/включение обновления листа, обработки событий и пересчёта: [vba]
Код
With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With
.................
With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With
[/vba]
Тормоза очень часто бывают именно из-за пренебрежения этими методами.



С уважением,
Алексей
MS Excel 2003 - the best!!!
 
Ответить
Сообщение
P.S.
таблица примерно 800х10 ячеек
Такую мелочь процедура должна обрабатывать практически мгновенно.
А Вы случайно не удалили отключение/включение обновления листа, обработки событий и пересчёта: [vba]
Код
With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With
.................
With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With
[/vba]
Тормоза очень часто бывают именно из-за пренебрежения этими методами.

Автор - Alex_ST
Дата добавления - 30.05.2017 в 11:14
Yujin Дата: Среда, 31.05.2017, 10:06 | Сообщение № 14
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Alex_ST, ScreenUpdating и EnableEvents я отключаю перед запуском основного модуля.а вот при установке .Calculation=xlManual - ругается, поэтому стоит на автомате
но я проверял в отдельном созданном файле - с такой же таблицей - работает быстро и без отключения данных событий, и, что характерно, с установкой .Calculation=xlManual
наверное и ваш код работает долго из-за того же , почему вычисления не ставятся на ручной режим...


Сообщение отредактировал Yujin - Среда, 31.05.2017, 10:06
 
Ответить
СообщениеAlex_ST, ScreenUpdating и EnableEvents я отключаю перед запуском основного модуля.а вот при установке .Calculation=xlManual - ругается, поэтому стоит на автомате
но я проверял в отдельном созданном файле - с такой же таблицей - работает быстро и без отключения данных событий, и, что характерно, с установкой .Calculation=xlManual
наверное и ваш код работает долго из-за того же , почему вычисления не ставятся на ручной режим...

Автор - Yujin
Дата добавления - 31.05.2017 в 10:06
Мурад Дата: Четверг, 10.01.2019, 17:52 | Сообщение № 15
Группа: Проверенные
Ранг: Ветеран
Сообщений: 513
Репутация: 18 ±
Замечаний: 0% ±

Excel 2007
Спасибо, очень полезный код!
Но вот момент. В соответствии с кодом числа исправляются на числа, ТОЛЬКО если формат области, в которой они находятся, является Общий, Числовой. Если формат области равен Текстовый, то числа так и останутся текстом.
Если же вначале указать формат принимаемой области как Общий или Числовой, то часть экспортируемых данных, например, 03.04.01 (код специальности обучения), преобразуются в число, что недопустимо. Поэтому заранее формат всех ячеек устанавливаю как Текстовый.
Можно ли скорректировать макрос, чтобы он вначале определял области, содержащие "числа как числа", затем менял формат ячейки на Общий/Числовой, и в конце обрабатывал числовое значение?
 
Ответить
СообщениеСпасибо, очень полезный код!
Но вот момент. В соответствии с кодом числа исправляются на числа, ТОЛЬКО если формат области, в которой они находятся, является Общий, Числовой. Если формат области равен Текстовый, то числа так и останутся текстом.
Если же вначале указать формат принимаемой области как Общий или Числовой, то часть экспортируемых данных, например, 03.04.01 (код специальности обучения), преобразуются в число, что недопустимо. Поэтому заранее формат всех ячеек устанавливаю как Текстовый.
Можно ли скорректировать макрос, чтобы он вначале определял области, содержащие "числа как числа", затем менял формат ячейки на Общий/Числовой, и в конце обрабатывал числовое значение?

Автор - Мурад
Дата добавления - 10.01.2019 в 17:52
Alex_ST Дата: Четверг, 10.01.2019, 22:10 | Сообщение № 16
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3199
Репутация: 606 ±
Замечаний: 0% ±

2003
Мурад, эту проблему методом, обсуждаемым в топике, исправить можно вряд ли.
Общеизвестная "бяка" шибко дюже грамотного Excel'я - самовольное преобразование вводимого текста и разделителями ./- в дату.
А моя процедура именно это гадское свойство и использует - копирует данные из ячеек и имитирует ручной ввод данных обратно во все ячейки выделенного диапазона.



С уважением,
Алексей
MS Excel 2003 - the best!!!
 
Ответить
СообщениеМурад, эту проблему методом, обсуждаемым в топике, исправить можно вряд ли.
Общеизвестная "бяка" шибко дюже грамотного Excel'я - самовольное преобразование вводимого текста и разделителями ./- в дату.
А моя процедура именно это гадское свойство и использует - копирует данные из ячеек и имитирует ручной ввод данных обратно во все ячейки выделенного диапазона.

Автор - Alex_ST
Дата добавления - 10.01.2019 в 22:10
Мир MS Excel » Вопросы и решения » Готовые решения » Исправить формат экспортированных как текст чисел (Excel)
  • Страница 1 из 1
  • 1
Поиск:

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