Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
заменить нулевые значения на длинное тире (прочерк)
 
#1
02.06.2016 16:53:15
Доброго времени суток. Есть таблица, условно 200 столбцов, 200 строк. В некоторых ячейках введены формулы, с основном сумма других ячеек, как по строкам, так и по столбцам. А в некоторых ячейках вводятся данные ручками. Требуется во всех ячейках с нулевыми значениями отобразить длинное тире (прочерк). Длинное тире должно отображаться и в тех ячейках где не введены данные (пустая ячейка), и там где формула насчитала ноль.
Если возможно, то формула не должна затираться длинным тире.
Алгоритм_1
1. Изменить формат ячеек на общий. (может и не нужно...)
2. Макрос проверяет пустая ячейка или нет, если непустая то преобразует формулу в значение. Если значение (введено руками) оставляет как есть.
3. Если пустая то ставит в нее длинное тире (юникод 2014).
4. проверяет не пустые ячейки, если "значение=0", то заменить на длинное тире.
Макрос выполняется в выделенном диапазоне.

Алгоритм_2 (не уверен в правильности)
1. Изменить формат ячеек на общий. (может и не нужно...)
3. Если ячейка не пустая, проверяет есть ли формула в ячейке, если есть, то изменить формулу на =ЕСЛИ(A1+A2=0;"-";A1+A2), где A1+A2 формула которая была в ячейке.
4. Если в ячейке нет формулы (есть значение введенное руками) проверяет "значение=0", если да, то заменить на длинное тире. !Но тут случается конфуз. Если эта ячейка участвует в формуле, формула выдаст ошибку. При этом если формула типа A1+A2 будет ошибка, если это =СУММ(B2:B15) то ошибки не будет. Что с этим сделать не знаю...!
5. Если значение не равно нулю (текст или число отличное от нуля), то оставить как есть.

Помогите описать это на VBA.
Изменено: nafanja2007 - 02.06.2016 20:03:23 (поправил алгоритм)
 
 
 
#2
02.06.2016 16:57:58
Код
Sub qqq()
With Range("A1").CurrentRegion
For Each x In .Cells.SpecialCells(xlCellTypeFormulas)
If x = 0 Then
    x.Value = "-"
End if
Next
For Each x In .Cells.SpecialCells(xlCellTypeConstants, 1)
 if x = 0 Then
    x.Value = "-"
 End if
Next
For Each x In .Cells.SpecialCells(xlCellTypeBlanks)
f x = 0 Then
    x.Value = "-"
 End if
Next
End With
End Sub
Примера нет, тестируйте сами.
Изменено: Владимир - 02.06.2016 17:11:57
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#3
02.06.2016 17:09:44
А потом начнутся страдания, как посчитать сумму, или еще что, там где стоят прочерки.
"— Тебя как, сразу кончить, или желаешь помучиться?
— Лучше, конечно, помучиться"
(с) "Белое солнце пустыни"
Изменено: kuklp - 02.06.2016 17:11:49
Я сам - дурнее всякого примера! ...
 
 
 
#4
02.06.2016 17:14:48
Цитата
Владимир написал:
1.Формулы нужно будет переписать, примерно так:
=если(сумм(a1:a20)=0;"---";сумм(a1:a20))
В том то и дело, что в разных ячейках разные формулы. И надо что бы макрос дописывал (изменял) формулу на =ЕСЛИ(A1-A3=0;"-";A1-A3), где A1-A3 формула которая БЫЛА в ячейке.
Я не знаю (уже не помню) VBA и прошу помочь мне описать на VBA этот алгоритм.
 
 
 
#5
02.06.2016 17:17:39
Цитата
kuklp написал:
А потом начнутся страдания, как посчитать сумму, или еще что, там где стоят прочерки.
Не начнутся ))) можно ведь скопировать лист\книгу. Скажем так, большой босс лучше понимает если стоит прочерк в ячейке. поэтому перед печатью, скопировал лист, выполнил макрос в выделенном диапазоне и напечатал ))) и все довольны.
 
 
 
#6
02.06.2016 17:43:05
Цитата
Владимир написал:
Примера нет, тестируйте сами
в 14 строке, f x = 0 Then - syntax error
дописал i   получилось    if x = 0 Then
макрос выполнился, после выполнения Run-time error '13': type mismatch

Результат выполнения макроса:
пустые ячейки (без формул и значений) остались пустыми, а должно быть тире.
ячейки в которых значение_формулы=0 были переписаны на тире, а хочется что бы там оставалась формула вида =ЕСЛИ(A1-A3=0;"-";A1-A3), где A1-A3 формула которая БЫЛА в ячейке

Есть два варианта. Или в этот макрос добавить еще условие, что если в ячейке есть формула значение которой не равно нулю, заменить его на значение.
Или второй вариант, даже если значение формулы равно нулю, то все равно ее менять на формулу вида =ЕСЛИ(A1-A3=0;"-";A1-A3), где A1-A3 формула которая БЫЛА в ячейке

И еще интересный вопрос, можно ли применить к пустой ячейке без формул и значений ту же формулу =ЕСЛИ(A1=0;"-"), где A1 адрес самой ячейки. Хотя по идее если в ячейке введено тире, то ячейка все равно должна корректно участвовать в формуле.?
 
 
 
#7
02.06.2016 17:49:12
nafanja2007, добрый вечер. Если нужно РЕАЛЬНО длинное тире, то используйте код 0150 или 0151. Результатом будет «–» или «—» соответственно.
Коды набираются с зажатым Alt на дополнительной Num-клавиатуре (справа) или с помощью функции =СИМВОЛ() (в функции ведущие нули можно не указывать)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#8
02.06.2016 17:57:34
Формулисты, про УФ никому в голову не прилетело? ;)
Я сам - дурнее всякого примера! ...
 
 
 
#9
02.06.2016 18:05:14
Цитата
nafanja2007 написал:
большой босс лучше понимает если стоит прочерк в ячейке.
Тогда мона не трогать документ. Копировать лист, в нем пустые без цикла заменить на тире, после заменить ноли на тире(ctrl+f) , отправить на печать и помножить отчет на ноль. Макрос несложный, но и неинтересный :)
Я сам - дурнее всякого примера! ...
 
 
 
#10
02.06.2016 19:13:37
Цитата
kuklp написал:
Копировать лист, в нем пустые без цикла заменить на тире, после заменить ноли на тире(ctrl+f) , отправить на печать и помножить отчет на ноль.
Тут есть два момента, в момент замены нулей на тире в тех ячейках которые участвуют в формуле, формула становится ...эээ плохой, почему-то ексель не хочет что бы в формуле были в качестве значений тире.
 
 
 
#11
02.06.2016 19:22:32
Цитата
nafanja2007 написал:
в момент замены нулей на тире в тех ячейках которые участвуют в формуле, формула становится ...эээ плохой
- голуба, а ну еще раз перечитайте №3 и свой ответ на него (Чемберлену, гы). Вы же не первый на форуме такой. Но выход все же есть - скопировали лист, поменяли формулы на значения... далее по списку. :)
Я сам - дурнее всякого примера! ...
 
 
 
#12
02.06.2016 19:26:08
Цитата
Jack Famous написал:
Если нужно РЕАЛЬНО длинное тире, то используйте код 0150 или 0151. Результатом будет «–» или «—» соответственно.
Коды набираются с зажатым Alt на дополнительной Num-клавиатуре (справа) или с помощью функции =СИМВОЛ() (в функции ведущие нули можно не указывать)
Код среднего тире 2013, а длинного 2014 в юникоде, и 150 или 151 в ASCII и кириллица

с помощью функции =СИМВОЛ() Это и в макросах работает?
 
 
 
#13
02.06.2016 20:01:38
nafanja2007, в макросах символы указываются через Chr(). Вам нужно 150 или 151. Ввести через другие сходу не удастся
Изменено: Jack Famous - 02.06.2016 20:02:56
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#14
02.06.2016 20:01:59
Цитата
kuklp написал:
- голуба, а ну еще раз перечитайте №3 и свой ответ на него (Чемберлену, гы).
Перечитал. Поправил свой первый пост. Лист отчета с которым это нужно сотворить, достаточно большой и возможно будет не один и все разные. поэтому и задумался о макросе, выделил таблицу или вообще весь лист, запустил макрос, получил готовый для печати документ. Макрос будет выполнятся бухгалтером, методом "нажатия на кнопку и получения результата".
 
 
 
#15
26.07.2019 13:43:02
У меня вопрос по теме. Идет копирование ячеек через цикл с предварительным вопросом, и если ячейки пустые или 0 она пишет все кроме заполненных. Как сделать запрос если она находит хотя бы одну >0 и далее уже без вопроса меняет все следующие? пробовал делать через ElseIf но она начинает работать с циклом совместно спрашивая для каждой ячейки
Скрытый текст
 
 
 
#16
26.07.2019 14:02:50
Увидели  о пустых ячейках - и считаете, что вопрос такой же? Создайте отдельную тему с названием, отражающим суть задачи
 
 
 
#17
26.07.2019 14:44:18
Можно макросом изменить все пустые ячейки на нули, а потом формат ячеек поставить - все форматы, тип   _-* # ##0_-;-* # ##0_-;_-* "-"_-;_-@_-
Код
Sub ooo()

Dim diapazon As Range

Set diapazon = [a4:d10]
    For Each x In diapazon
        If x.Value = "" Then x.Value = 0
    Next
End Sub
Изменено: Настя_Nastya - 26.07.2019 14:44:45
 
 
 
#18
26.07.2019 16:01:08
Настя_Nastya, теперь мне такой способ кажется самым логичным, только было это в далёком 2016ом)))
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#19
26.07.2019 16:09:23
Цитата
nafanja2007 написал:
Код среднего тире 2013, а длинного 2014 в юникоде, и 150 или 151 в ASCII и кириллица
Если уж возвращаться к старому, то для юникода есть функции VBA ChrW и AscW и функции рабочего листа UNICODE и UNICHAR (ЮНИСИМВ) (только для версий Excel 2013+). Использование кодов 150 и 151 кодовой страницы Win-1251 не рекомендуется с точки зрения переносимости на другие конфигурации.
Владимир
 
 
 
#20
26.07.2019 16:10:19
Jack Famous, ну, может, кому-другому пригодится) мало у нас, что ли, таких капризных начальников?)))
 
 
 
Страницы: 1
Наверх