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

Вход

Регистрация

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

 

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

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Перенос значения ячейки с изменением формата и коррект. (Формулы/Formulas)
Перенос значения ячейки с изменением формата и коррект.
IDK Дата: Четверг, 01.02.2018, 13:17 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Всем доброго времени суток!

Без лишних предисловий, сразу к делу. Есть таблица, в которой есть некоторые данные (числовые коды, номера). Появилась необходимость перенести эти данные в другие ячейки, но с изменением формата + корректировкой.

Допустим есть у нас 11-значный код: 12345678901 и он в формате "Дополнительный". Необходимо перенести его значение в соседнюю (или не обязательно) ячейку с изменением формата на "числовой" с разделением групп разрядов, чтобы в итоге он приобрел такой вид: 5 678 901. Важно! Иногда в одной ячейке более 1-го кода (до 5,6), разделённым между собой запятыми. Необходимо перенести ВСЕ без исключения коды, а не только первый, то есть правило должно применяться ко всем донорам.

Пользоваться функцией ВПР уже научился, а вот добавлять в неё дополнительные функции и правила покамест не умею.

Буду признателен любой подсказке.
К сообщению приложен файл: 6909016.xls (26.5 Kb)
 
Ответить
СообщениеВсем доброго времени суток!

Без лишних предисловий, сразу к делу. Есть таблица, в которой есть некоторые данные (числовые коды, номера). Появилась необходимость перенести эти данные в другие ячейки, но с изменением формата + корректировкой.

Допустим есть у нас 11-значный код: 12345678901 и он в формате "Дополнительный". Необходимо перенести его значение в соседнюю (или не обязательно) ячейку с изменением формата на "числовой" с разделением групп разрядов, чтобы в итоге он приобрел такой вид: 5 678 901. Важно! Иногда в одной ячейке более 1-го кода (до 5,6), разделённым между собой запятыми. Необходимо перенести ВСЕ без исключения коды, а не только первый, то есть правило должно применяться ко всем донорам.

Пользоваться функцией ВПР уже научился, а вот добавлять в неё дополнительные функции и правила покамест не умею.

Буду признателен любой подсказке.
Автор - IDK
Дата добавления - 01.02.2018 в 13:17
Che79 Дата: Четверг, 01.02.2018, 13:27 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
Здравствуйте.
Если правильно понял задачу, то, как вариант.
1)Текст по столбцам с разделителем запятая
2) Ко всем разделенным применить формулу
Код
=--ПСТР(A1;5;99)
+ формат Числовой

Делай нормально и будет нормально!

Сообщение отредактировал Che79 - Четверг, 01.02.2018, 13:28
 
Ответить
СообщениеЗдравствуйте.
Если правильно понял задачу, то, как вариант.
1)Текст по столбцам с разделителем запятая
2) Ко всем разделенным применить формулу
Код
=--ПСТР(A1;5;99)
+ формат Числовой
Автор - Che79
Дата добавления - 01.02.2018 в 13:27
krosav4ig Дата: Четверг, 01.02.2018, 14:26 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 997 ±
Замечаний: 0% ±

Excel 2007,2010,2013
Код
=ЕСЛИОШИБКА(ОСТАТ(ПСТР(ПОДСТАВИТЬ(","&$A1;",";ПОВТОР(" ";99));99*СТОЛБЕЦ(A1);99);10^7);"")
К сообщению приложен файл: 2014932.xls (26.0 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
Сообщение
Код
=ЕСЛИОШИБКА(ОСТАТ(ПСТР(ПОДСТАВИТЬ(","&$A1;",";ПОВТОР(" ";99));99*СТОЛБЕЦ(A1);99);10^7);"")
Автор - krosav4ig
Дата добавления - 01.02.2018 в 14:26
IDK Дата: Пятница, 02.02.2018, 13:30 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Цитата Che79, 01.02.2018 в 13:27, в сообщении № 2 ()
=--ПСТР(A1;5;99)


Спасибо. Ваша формула достаточно проста, но применима только в том случае, если в ячейке одно значение. По крайней мере при 2х и более значениях она у меня работать переставала (выводя результат первого значения, и не применяя правило ко всем остальным).

Цитата krosav4ig, 01.02.2018 в 14:26, в сообщении № 3 ()
=ЕСЛИОШИБКА(ОСТАТ(ПСТР(ПОДСТАВИТЬ(","&$A1;",";ПОВТОР(" ";99));99*СТОЛБЕЦ(A1);99);10^7);"")
К сообщению приложен файл: 2014932.xls(26Kb)


У вас формула посложнее, мне нужно будет посидеть, подумать, и понять зависимость применяемых правил. Все работает (в приложенном вами файле), но есть ли возможность выводить результат в одну ячейку, снова таки разделяя запятыми? Пример: 32111234567, 32111234568 и в результате применения формулы получаем в соседней ячейке 2 значения в формате: 1 234 567, 1 234 568. Подойдет даже без применения правила формата ячейки (это можно будет сделать после), то есть даже если в итоге будет: 1234567, 1234568.
 
Ответить
Сообщение
Цитата Che79, 01.02.2018 в 13:27, в сообщении № 2 ()
=--ПСТР(A1;5;99)


Спасибо. Ваша формула достаточно проста, но применима только в том случае, если в ячейке одно значение. По крайней мере при 2х и более значениях она у меня работать переставала (выводя результат первого значения, и не применяя правило ко всем остальным).

Цитата krosav4ig, 01.02.2018 в 14:26, в сообщении № 3 ()
=ЕСЛИОШИБКА(ОСТАТ(ПСТР(ПОДСТАВИТЬ(","&$A1;",";ПОВТОР(" ";99));99*СТОЛБЕЦ(A1);99);10^7);"")
К сообщению приложен файл: 2014932.xls(26Kb)


У вас формула посложнее, мне нужно будет посидеть, подумать, и понять зависимость применяемых правил. Все работает (в приложенном вами файле), но есть ли возможность выводить результат в одну ячейку, снова таки разделяя запятыми? Пример: 32111234567, 32111234568 и в результате применения формулы получаем в соседней ячейке 2 значения в формате: 1 234 567, 1 234 568. Подойдет даже без применения правила формата ячейки (это можно будет сделать после), то есть даже если в итоге будет: 1234567, 1234568.
Автор - IDK
Дата добавления - 02.02.2018 в 13:30
Che79 Дата: Пятница, 02.02.2018, 13:46 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
Цитата IDK, 02.02.2018 в 13:30, в сообщении № 4 ()
применима только в том случае, если в ячейке одно значение

IDK, вот вариант , расписанный по шагам на трёх закладках. Результат на листе "Шаг 2_Формула ПСТР" (ячейки D-F), ошибки скрыты с помощью УФ. В этом варианте выводится по одному значению в каждую ячейку.
К сообщению приложен файл: 6909016_1.xls (32.5 Kb)

Делай нормально и будет нормально!
 
Ответить
Сообщение
Цитата IDK, 02.02.2018 в 13:30, в сообщении № 4 ()
применима только в том случае, если в ячейке одно значение

IDK, вот вариант , расписанный по шагам на трёх закладках. Результат на листе "Шаг 2_Формула ПСТР" (ячейки D-F), ошибки скрыты с помощью УФ. В этом варианте выводится по одному значению в каждую ячейку.
Автор - Che79
Дата добавления - 02.02.2018 в 13:46
krosav4ig Дата: Пятница, 02.02.2018, 15:03 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 997 ±
Замечаний: 0% ±

Excel 2007,2010,2013
Вариант с UDF
[vba]
Код
Function Перенос$(s$)
    With CreateObject("vbscript.regexp")
        .Pattern = "(\d{1,3}(?=\d{4}))|\d+"
        .Global = True
        s = .Replace(StrReverse(s), "$1 ")
    End With
    Перенос = StrReverse(Application.Trim(s))
End Function
[/vba]
К сообщению приложен файл: 7236614.xls (36.5 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
СообщениеВариант с UDF
[vba]
Код
Function Перенос$(s$)
    With CreateObject("vbscript.regexp")
        .Pattern = "(\d{1,3}(?=\d{4}))|\d+"
        .Global = True
        s = .Replace(StrReverse(s), "$1 ")
    End With
    Перенос = StrReverse(Application.Trim(s))
End Function
[/vba]
Автор - krosav4ig
Дата добавления - 02.02.2018 в 15:03
IDK Дата: Понедельник, 05.02.2018, 18:21 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Цитата Che79, 02.02.2018 в 13:46, в сообщении № 5 ()
IDK, вот вариант , расписанный по шагам на трёх закладках. Результат на листе "Шаг 2_Формула ПСТР" (ячейки D-F), ошибки скрыты с помощью УФ. В этом варианте выводится по одному значению в каждую ячейку.


Спасибо, но в сущности это ничего не меняет. Я "за" сведения к минимуму кол-ва действий для достижения необходимого результата, собственно поэтому и спросил, можно ли это сделать поячеечно, чтобы формула/макрос проходила/проходил каждую ячейку, определяла/определял коды, выполняла/выполнял набор операций и выводила/выводил результат, снова же, в одну, скажем, соседнюю ячейку.

krosav4ig, вот! Это оно! То, что нужно! Мне вообще без разницы, каким способом будет реализован перенос и изменение, и если для этого нужна пользовательская функция, реализованная в VBA, то пусть так и будет. Отличный вариант, благодарю!
 
Ответить
Сообщение
Цитата Che79, 02.02.2018 в 13:46, в сообщении № 5 ()
IDK, вот вариант , расписанный по шагам на трёх закладках. Результат на листе "Шаг 2_Формула ПСТР" (ячейки D-F), ошибки скрыты с помощью УФ. В этом варианте выводится по одному значению в каждую ячейку.


Спасибо, но в сущности это ничего не меняет. Я "за" сведения к минимуму кол-ва действий для достижения необходимого результата, собственно поэтому и спросил, можно ли это сделать поячеечно, чтобы формула/макрос проходила/проходил каждую ячейку, определяла/определял коды, выполняла/выполнял набор операций и выводила/выводил результат, снова же, в одну, скажем, соседнюю ячейку.

krosav4ig, вот! Это оно! То, что нужно! Мне вообще без разницы, каким способом будет реализован перенос и изменение, и если для этого нужна пользовательская функция, реализованная в VBA, то пусть так и будет. Отличный вариант, благодарю!
Автор - IDK
Дата добавления - 05.02.2018 в 18:21
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Перенос значения ячейки с изменением формата и коррект. (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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