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

Вход

Регистрация

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

 

= Мир MS Excel/Деление числа в исходной ячейке - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Деление числа в исходной ячейке (Макросы/Sub)
Деление числа в исходной ячейке
DrMini Дата: Пятница, 13.03.2020, 09:29 | Сообщение № 1
Группа: Друзья
Ранг: Старожил
Сообщений: 1877
Репутация: 269 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Доброе время суток форумчане и гости форума!
Приходит по электронной почте файл с данными (приложил часть файла) на нескольких листах. На листе Данные находятся данные для поиска на других листах через ВПР.
Мне нужно все цифры на листе Данные, в диапазоне G2:BY1000 разделить на 4,1 с округлением до целых чисел. Для этого приходится копировать на дополнительно созданный лист всё из диапазона G2:BY1000 и через формат ячейки округлять до целого числа, а затем переносить значение ячейки на лист Данные с заменой в исходных ячейках.
Присылаемый файл всегда содержит лист Данные с одинаковым диапазоном. Название файла в зависимости от квартала меняется.
Подскажите пожалуйста, как можно упростить этот процесс. Например файлом с макросом находящийся в одном каталоге. Как это сделать формулами я ума не приложу.
К сообщению приложен файл: 2176449.xlsx (205.5 Kb)


Сообщение отредактировал DrMini - Пятница, 13.03.2020, 09:31
 
Ответить
СообщениеДоброе время суток форумчане и гости форума!
Приходит по электронной почте файл с данными (приложил часть файла) на нескольких листах. На листе Данные находятся данные для поиска на других листах через ВПР.
Мне нужно все цифры на листе Данные, в диапазоне G2:BY1000 разделить на 4,1 с округлением до целых чисел. Для этого приходится копировать на дополнительно созданный лист всё из диапазона G2:BY1000 и через формат ячейки округлять до целого числа, а затем переносить значение ячейки на лист Данные с заменой в исходных ячейках.
Присылаемый файл всегда содержит лист Данные с одинаковым диапазоном. Название файла в зависимости от квартала меняется.
Подскажите пожалуйста, как можно упростить этот процесс. Например файлом с макросом находящийся в одном каталоге. Как это сделать формулами я ума не приложу.
Автор - DrMini
Дата добавления - 13.03.2020 в 09:29
RAN Дата: Пятница, 13.03.2020, 11:15 | Сообщение № 2
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
Может я чего не понимаю?
Код
=ОКРУГЛ(J2/4,1;0)

Быть или не быть, вот в чем загвоздка!
 
Ответить
СообщениеМожет я чего не понимаю?
Код
=ОКРУГЛ(J2/4,1;0)
Автор - RAN
Дата добавления - 13.03.2020 в 11:15
DrMini Дата: Пятница, 13.03.2020, 12:28 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1877
Репутация: 269 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Всё Вы правильно поняли. Но хотелось бы изменить данные в этих же ячейках без дополнительных манипуляций с копированием.
ПРОШУ ПРОЩЕНИЯ. ОТОСЛАЛ ФАЙЛ С УЖЕ ВТОРЫМ ЛИСТОМ.
Лист Данные приходит вот в таком виде. И надо в этих же ячейках поменять данные.
Правильный файл прилагаю.
[p.s.]Прошу прощения за невнимательность.
К сообщению приложен файл: 8787117.xlsx (12.8 Kb)
 
Ответить
СообщениеВсё Вы правильно поняли. Но хотелось бы изменить данные в этих же ячейках без дополнительных манипуляций с копированием.
ПРОШУ ПРОЩЕНИЯ. ОТОСЛАЛ ФАЙЛ С УЖЕ ВТОРЫМ ЛИСТОМ.
Лист Данные приходит вот в таком виде. И надо в этих же ячейках поменять данные.
Правильный файл прилагаю.
[p.s.]Прошу прощения за невнимательность.
Автор - DrMini
Дата добавления - 13.03.2020 в 12:28
RAN Дата: Пятница, 13.03.2020, 15:32 | Сообщение № 4
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
Цитата DrMini, 13.03.2020 в 09:29, в сообщении № 1 ()
для поиска на других листах через ВПР

может и формулы хватит?
Код
=ВПР(ОКРУГЛ(J2/4,1;0);$N$2:$P$16;2;0)
К сообщению приложен файл: 8787117-1-.xlsx (12.6 Kb)

Быть или не быть, вот в чем загвоздка!
 
Ответить
Сообщение
Цитата DrMini, 13.03.2020 в 09:29, в сообщении № 1 ()
для поиска на других листах через ВПР

может и формулы хватит?
Код
=ВПР(ОКРУГЛ(J2/4,1;0);$N$2:$P$16;2;0)
Автор - RAN
Дата добавления - 13.03.2020 в 15:32
DrMini Дата: Пятница, 13.03.2020, 15:46 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1877
Репутация: 269 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
RAN, К сожалению этот вариант не подходит. К листу Данные имеются ещё 4-ре листа с таблицами в которых протянуть формулу не получится. Подсчитываются промежуточные итоги.
Самое простое это менять данные только на листе Данные и в эти же ячейки. Так, как в книге создаётся Именной диапазон с листа Данные диапазона G2:BY1000 в «Диспетчере имён». Да и геморройно всё это менять в таблицах.
 
Ответить
СообщениеRAN, К сожалению этот вариант не подходит. К листу Данные имеются ещё 4-ре листа с таблицами в которых протянуть формулу не получится. Подсчитываются промежуточные итоги.
Самое простое это менять данные только на листе Данные и в эти же ячейки. Так, как в книге создаётся Именной диапазон с листа Данные диапазона G2:BY1000 в «Диспетчере имён». Да и геморройно всё это менять в таблицах.
Автор - DrMini
Дата добавления - 13.03.2020 в 15:46
Pelena Дата: Пятница, 13.03.2020, 16:07 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 19405
Репутация: 4555 ±
Замечаний: ±

Excel 365 & Mac Excel
Пишем в любую пустую ячейку 4,1 -- Копировать (Ctrl+C) -- выделяем диапазон с числами -- Специальная вставка -- Разделить -- ОК
Дальше можно выставить формат без десятичных знаков и при необходимости в параметрах установить Точность как на экране

Потом число 4,1 можно удалить

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеПишем в любую пустую ячейку 4,1 -- Копировать (Ctrl+C) -- выделяем диапазон с числами -- Специальная вставка -- Разделить -- ОК
Дальше можно выставить формат без десятичных знаков и при необходимости в параметрах установить Точность как на экране

Потом число 4,1 можно удалить
Автор - Pelena
Дата добавления - 13.03.2020 в 16:07
DrMini Дата: Пятница, 13.03.2020, 16:20 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1877
Репутация: 269 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Елена большое спасибо. Так и правда проще.
[p.s.]Может, кто макросом решит эту проблему.
 
Ответить
СообщениеЕлена большое спасибо. Так и правда проще.
[p.s.]Может, кто макросом решит эту проблему.
Автор - DrMini
Дата добавления - 13.03.2020 в 16:20
Pelena Дата: Пятница, 13.03.2020, 16:48 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 19405
Репутация: 4555 ±
Замечаний: ±

Excel 365 & Mac Excel
Макрос
[vba]
Код
Sub hhh()
    Dim c As Range
    For Each c In Range("G2:BY1000")
        c = Round(c / 4.1, 0)
    Next c
End Sub
[/vba]

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеМакрос
[vba]
Код
Sub hhh()
    Dim c As Range
    For Each c In Range("G2:BY1000")
        c = Round(c / 4.1, 0)
    Next c
End Sub
[/vba]
Автор - Pelena
Дата добавления - 13.03.2020 в 16:48
RAN Дата: Пятница, 13.03.2020, 18:10 | Сообщение № 9
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
Лен, не правда ваша. VBA.Round выдает не математическое округление, а с банковское. Для математического нужно Appication.Round.

Быть или не быть, вот в чем загвоздка!
 
Ответить
СообщениеЛен, не правда ваша. VBA.Round выдает не математическое округление, а с банковское. Для математического нужно Appication.Round.
Автор - RAN
Дата добавления - 13.03.2020 в 18:10
Pelena Дата: Пятница, 13.03.2020, 18:56 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 19405
Репутация: 4555 ±
Замечаний: ±

Excel 365 & Mac Excel
ОК, замечание принимается)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеОК, замечание принимается)
Автор - Pelena
Дата добавления - 13.03.2020 в 18:56
DrMini Дата: Суббота, 14.03.2020, 07:16 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1877
Репутация: 269 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Прошу прощения за долгое молчание.
Уважаемые Андрей и Елена. Спасибо за это решение. Но для меня макрос это, как обезьяне граната.
Подскажите, что с ним делать. Моих познаний хватило вставить его в Лист, Книгу, Модуль. результат нулевой.
В идеале нужен файл с макросом содержащий кнопку.
При нажатии кнопку выбираем нужный файл в котором на листе Данные нужно произвести деление всех ячеек на 4,1 находящихся в диапазоне G2:BY1000


Сообщение отредактировал DrMini - Суббота, 14.03.2020, 07:16
 
Ответить
СообщениеПрошу прощения за долгое молчание.
Уважаемые Андрей и Елена. Спасибо за это решение. Но для меня макрос это, как обезьяне граната.
Подскажите, что с ним делать. Моих познаний хватило вставить его в Лист, Книгу, Модуль. результат нулевой.
В идеале нужен файл с макросом содержащий кнопку.
При нажатии кнопку выбираем нужный файл в котором на листе Данные нужно произвести деление всех ячеек на 4,1 находящихся в диапазоне G2:BY1000
Автор - DrMini
Дата добавления - 14.03.2020 в 07:16
Pelena Дата: Суббота, 14.03.2020, 08:01 | Сообщение № 12
Группа: Админы
Ранг: Местный житель
Сообщений: 19405
Репутация: 4555 ±
Замечаний: ±

Excel 365 & Mac Excel
DrMini, почитайте про личную книгу макросов. На мой взгляд, Вам это лучше подойдет, чем "файл с кнопкой". Перенесете нужный макрос в Личную книгу макросов и сможете его использовать в любом файле Excel на Вашем компьютере. А кнопку для этого макроса можно вынести на Панель быстрого доступа

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеDrMini, почитайте про личную книгу макросов. На мой взгляд, Вам это лучше подойдет, чем "файл с кнопкой". Перенесете нужный макрос в Личную книгу макросов и сможете его использовать в любом файле Excel на Вашем компьютере. А кнопку для этого макроса можно вынести на Панель быстрого доступа
Автор - Pelena
Дата добавления - 14.03.2020 в 08:01
DrMini Дата: Суббота, 14.03.2020, 08:34 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1877
Репутация: 269 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Цитата
почитайте про личную книгу макросов.

Спасибо Pelena, я о таком не знал.
 
Ответить
Сообщение
Цитата
почитайте про личную книгу макросов.

Спасибо Pelena, я о таком не знал.
Автор - DrMini
Дата добавления - 14.03.2020 в 08:34
DrMini Дата: Суббота, 14.03.2020, 09:06 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1877
Репутация: 269 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Сделал через личную книгу макросов. Работает.
Но такие вычислительные ресурсы на работе будут недоступны. На моём CPU Intel Core i5 с ОЗУ 16Gb и SSD SAMSUNG 970 EVO Plus MZ-V7S250BW 250Гб, M.2 2280, PCI-E x4, NVMe обрабатывается лист около 15 минут. На работе на это уйдёт весь рабочий день.
Использую вариант из Сообщения №6


Сообщение отредактировал DrMini - Суббота, 14.03.2020, 09:06
 
Ответить
СообщениеСделал через личную книгу макросов. Работает.
Но такие вычислительные ресурсы на работе будут недоступны. На моём CPU Intel Core i5 с ОЗУ 16Gb и SSD SAMSUNG 970 EVO Plus MZ-V7S250BW 250Гб, M.2 2280, PCI-E x4, NVMe обрабатывается лист около 15 минут. На работе на это уйдёт весь рабочий день.
Использую вариант из Сообщения №6
Автор - DrMini
Дата добавления - 14.03.2020 в 09:06
Pelena Дата: Суббота, 14.03.2020, 11:10 | Сообщение № 15
Группа: Админы
Ранг: Местный житель
Сообщений: 19405
Репутация: 4555 ±
Замечаний: ±

Excel 365 & Mac Excel
Можно попробовать ускорить
[vba]
Код
Sub hhh()
    Dim arr, i&, j&
    arr = Range("G2:BY1000").Value
    For i = 1 To UBound(arr)
        For j = 1 To UBound(arr, 2)
            arr(i, j) = Application.Round(arr(i, j) / 4.1, 0)
        Next j
    Next i
    Range("G2").Resize(UBound(arr), UBound(arr, 2)) = arr
End Sub
[/vba]

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеМожно попробовать ускорить
[vba]
Код
Sub hhh()
    Dim arr, i&, j&
    arr = Range("G2:BY1000").Value
    For i = 1 To UBound(arr)
        For j = 1 To UBound(arr, 2)
            arr(i, j) = Application.Round(arr(i, j) / 4.1, 0)
        Next j
    Next i
    Range("G2").Resize(UBound(arr), UBound(arr, 2)) = arr
End Sub
[/vba]
Автор - Pelena
Дата добавления - 14.03.2020 в 11:10
DrMini Дата: Суббота, 14.03.2020, 11:25 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 1877
Репутация: 269 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Цитата Pelena, 14.03.2020 в 11:10, в сообщении № 15 ()
попробовать ускорить

Афигеть. СПАСИБО БОЛЬШОЕ!
Работает просто мгновенно.
 
Ответить
Сообщение
Цитата Pelena, 14.03.2020 в 11:10, в сообщении № 15 ()
попробовать ускорить

Афигеть. СПАСИБО БОЛЬШОЕ!
Работает просто мгновенно.
Автор - DrMini
Дата добавления - 14.03.2020 в 11:25
RAN Дата: Суббота, 14.03.2020, 12:22 | Сообщение № 17
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
[vba]
Код
Sub Мяу()
    [G2:BY1000] = Application.Round([G2:BY1000/4.1], 0)
End Sub
[/vba]

Быть или не быть, вот в чем загвоздка!
 
Ответить
Сообщение[vba]
Код
Sub Мяу()
    [G2:BY1000] = Application.Round([G2:BY1000/4.1], 0)
End Sub
[/vba]
Автор - RAN
Дата добавления - 14.03.2020 в 12:22
DrMini Дата: Суббота, 14.03.2020, 13:02 | Сообщение № 18
Группа: Друзья
Ранг: Старожил
Сообщений: 1877
Репутация: 269 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Афигеть. СПАСИБО БОЛЬШОЕ!
Просто пулемёт. Только намного быстрее.
RAN, а может сделаете файлик, как я просил в Сообщении №11 ?
Цитата
В идеале нужен файл с макросом содержащий кнопку.
При нажатии на кнопку выбираем нужный файл в котором на листе Данные нужно произвести деление всех ячеек на 4,1 находящихся в диапазоне G2:BY1000


Сообщение отредактировал DrMini - Суббота, 14.03.2020, 13:23
 
Ответить
СообщениеАфигеть. СПАСИБО БОЛЬШОЕ!
Просто пулемёт. Только намного быстрее.
RAN, а может сделаете файлик, как я просил в Сообщении №11 ?
Цитата
В идеале нужен файл с макросом содержащий кнопку.
При нажатии на кнопку выбираем нужный файл в котором на листе Данные нужно произвести деление всех ячеек на 4,1 находящихся в диапазоне G2:BY1000
Автор - DrMini
Дата добавления - 14.03.2020 в 13:02
RAN Дата: Суббота, 14.03.2020, 14:54 | Сообщение № 19
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
А смысл?
Кидаете любой из макросов в Personal, открываете нужный файл, нужный лист, выполняете. Оба макроса работают с активным листом.
Или вы думаете, что через диалог Excel файл открыть проще, чем через проводник?

Быть или не быть, вот в чем загвоздка!
 
Ответить
СообщениеА смысл?
Кидаете любой из макросов в Personal, открываете нужный файл, нужный лист, выполняете. Оба макроса работают с активным листом.
Или вы думаете, что через диалог Excel файл открыть проще, чем через проводник?
Автор - RAN
Дата добавления - 14.03.2020 в 14:54
DrMini Дата: Суббота, 14.03.2020, 15:28 | Сообщение № 20
Группа: Друзья
Ранг: Старожил
Сообщений: 1877
Репутация: 269 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Цитата RAN, 14.03.2020 в 14:54, в сообщении № 19 ()
А смысл?

Сам я всё это делаю без проблем. Открываю файл. Нажимаю Alt+F8, выбираю макрос и вуаля.
Час назад пытался всё это объяснить нашей работнице... После 20 минутных объяснений, как добавить макрос в личную книгу макросов сошлись на том, что она будет высылать мне этот файл по почте и я, как и прежде буду ей его править.
Вот и весь смысл. А с вариантом выбора файла через кнопку у неё всё получается. Что-то подобное они делают.
 
Ответить
Сообщение
Цитата RAN, 14.03.2020 в 14:54, в сообщении № 19 ()
А смысл?

Сам я всё это делаю без проблем. Открываю файл. Нажимаю Alt+F8, выбираю макрос и вуаля.
Час назад пытался всё это объяснить нашей работнице... После 20 минутных объяснений, как добавить макрос в личную книгу макросов сошлись на том, что она будет высылать мне этот файл по почте и я, как и прежде буду ей его править.
Вот и весь смысл. А с вариантом выбора файла через кнопку у неё всё получается. Что-то подобное они делают.
Автор - DrMini
Дата добавления - 14.03.2020 в 15:28
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Деление числа в исходной ячейке (Макросы/Sub)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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