Закраска ячеек, которые содержат нуль и не равны пусто - Мир MS Excel

Регистрация

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

= Мир MS Excel/Закраска ячеек, которые содержат нуль и не равны пусто - Мир MS Excel

Страница 1 из 1 1
Модератор форума: китин, _Boroda_, DrMini

Закраска ячеек, которые содержат нуль и не равны пусто (Условное Форматирование/Conditional Formattings)

Закраска ячеек, которые содержат нуль и не равны пусто

Дата: Суббота, 21.03.2015, 16:01 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 37

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Добрый день.

Необходимо, чтобы в диапазоне закрашивались только те ячейки, которые не равно пусто и равно нуль. Если создавать правило, чтобы окрашивались ячейки, содержащие нуль, то автоматически окрашиваются и пустые ячейки. Как этого избежать?

Спасибо.

К сообщению приложен файл: .xlsx.xls (27.5 Kb)

Ответить

Дата: Суббота, 21.03.2015, 16:06 | Сообщение № 2

Группа: Админы

Ранг: Местный житель

Сообщений: 19516

Репутация: 4632 ±

Замечаний: ±

Excel 365 & Mac Excel

Здравствуйте.
Например, так

Код

=НЕ(ЕПУСТО($A1))*($A1=0)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Дата: Суббота, 21.03.2015, 16:07 | Сообщение № 3

Группа: Друзья

Ранг: Старожил

Сообщений: 1347

Репутация: 249 ±

Замечаний: 0% ±

Excel 2010

Или:

Код

=($A1=0)*($A1<>"")

Ответить

Дата: Суббота, 21.03.2015, 16:30 | Сообщение № 4

Группа: Пользователи

Ранг: Новичок

Сообщений: 37

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Получилось. Спасибо большое.

Ответить

Дата: Суббота, 21.03.2015, 16:45 | Сообщение № 5

Группа: Друзья

Ранг: Ветеран

Сообщений: 770

Репутация: 255 ±

Замечаний: 0% ±

Excel 2013

Pelena, ShAM, а почему именно через умножение?
ведь логическое И быстрее умножения и менее ресурсоемко, да и выглядит читабельнее...

Код

=И(B1<>"";B1=0)

KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333

Ответить

Дата: Суббота, 21.03.2015, 16:57 | Сообщение № 6

Группа: Админы

Ранг: Местный житель

Сообщений: 19516

Репутация: 4632 ±

Замечаний: ±

Excel 365 & Mac Excel

Цитата KSV, 21.03.2015 в 16:45, в сообщении № 5

логическое И быстрее умножения

Намного?

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Дата: Суббота, 21.03.2015, 17:17 | Сообщение № 7

Группа: Друзья

Ранг: Ветеран

Сообщений: 770

Репутация: 255 ±

Замечаний: 0% ±

Excel 2013

Pelena, конечно же нет, но зачем "платить" больше?...

[p.s.]просто, я думал, что такое решение связано с чем-то конкретным, поэтому и спросил...

KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333

Ответить

Дата: Суббота, 21.03.2015, 17:27 | Сообщение № 8

Группа: Друзья

Ранг: Старожил

Сообщений: 1347

Репутация: 249 ±

Замечаний: 0% ±

Excel 2010

Цитата KSV, 21.03.2015 в 16:45, в сообщении № 5

а почему именно через умножение?

Привычка.

Ответить

Дата: Суббота, 21.03.2015, 19:24 | Сообщение № 9

Группа: Админы

Ранг: Местный житель

Сообщений: 19516

Репутация: 4632 ±

Замечаний: ±

Excel 365 & Mac Excel

Цитата KSV, 21.03.2015 в 16:45, в сообщении № 5

логическое И быстрее умножения

На миллионе однотипных операций с умножением и логическим И типа
[vba]

Код

а=([A1]=0)*([A1]<>"")
a=([A1]=0) and ([A1]<>"")

[/vba]
разница получилась несущественная, порядка 2%, в пользу умножения

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Дата: Суббота, 21.03.2015, 23:44 | Сообщение № 10

Группа: Друзья

Ранг: Ветеран

Сообщений: 770

Репутация: 255 ±

Замечаний: 0% ±

Excel 2013

Елена, не сочтите за занудство

, сам протестировал...

разница оказалась мизерной (я предполагал, что должна быть значительно больше) - в среднем, 0.3%, но все же в пользу логического И

(статистика)
[vba]

Код

' при пустой A1
Test1 =  115890
Test1 =  116594
Test1 =  116735
Test2 =  116688
Test2 =  116688
Test2 =  116390

' при A1 = 0
Test1 =  117718
Test1 =  118109
Test1 =  116968
Test2 =  119843
Test2 =  117297
Test2 =  118110

' A1 = 777
Test1 =  118703
Test1 =  118453
Test1 =  118656
Test2 =  118860
Test2 =  118797
Test2 =  118921

[/vba]

код для проверки:
[vba]

Код

Option Explicit

Private Declare Function GetTickCount Lib "kernel32" () As Long

Private Const IterationCount As Long = 1000000

Private Sub Test1()
     Dim lngTime As Long
     Dim i       As Long
     Dim b       As Boolean

     lngTime = GetTickCount()

     For i = 1& To IterationCount
         b = (([A1] <> "") And ([A1] = 0))
     Next

     lngTime = GetTickCount() - lngTime

     Debug.Print "Test1 = "; lngTime
End Sub

Private Sub Test2()
     Dim lngTime As Long
     Dim i       As Long
     Dim b       As Boolean

     lngTime = GetTickCount()

     For i = 1& To IterationCount
         b = (([A1] <> "") * ([A1] = 0))
     Next

     lngTime = GetTickCount() - lngTime

     Debug.Print "Test2 = "; lngTime
End Sub

[/vba]

[p.s.]i5-3317U, Win 8.1 (x64), Office 2013 Pro (x32)

KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333

Ответить

Дата: Воскресенье, 22.03.2015, 00:00 | Сообщение № 11

Группа: Друзья

Ранг: Старожил

Сообщений: 2348

Репутация: 997 ±

Замечаний: 0% ±

Excel 2007,2010,2013

еще вариант формулы для УФ

Код

=A1&""="0"

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Ответить

Дата: Воскресенье, 22.03.2015, 00:23 | Сообщение № 12

Группа: Друзья

Ранг: Ветеран

Сообщений: 770

Репутация: 255 ±

Замечаний: 0% ±

Excel 2013

ну, действительно, же krosav4ig! hands

hands

просто, как все гениальное! yes

yes

и в 2 раза быстрее! (подозреваю, что это из-за того, что содержимое ячейки считывается только один раз)

KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333

Ответить

Дата: Воскресенье, 22.03.2015, 02:53 | Сообщение № 13

Группа: Друзья

Ранг: Старожил

Сообщений: 2348

Репутация: 997 ±

Замечаний: 0% ±

Excel 2007,2010,2013

KSV, пасяба

Цитата KSV, 22.03.2015 в 00:23, в сообщении № 12

подозреваю, что это из-за того, что содержимое ячейки считывается только один раз

тут это значительно влияет на результат
[offtop]добавил еще 1 процедуру

на выходе получил

[p.s.]intel core 2 duo E6550, Win 6.1 build 7600 x32, Office 2013 Pro+ x32[/offtop]

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Воскресенье, 22.03.2015, 03:12

Ответить

Дата: Воскресенье, 22.03.2015, 03:02 | Сообщение № 14

Группа: Друзья

Ранг: Старожил

Сообщений: 2348

Репутация: 997 ±

Замечаний: 0% ±

Excel 2007,2010,2013

[offtop]для чистоты эксперимента считываю значение из A1 в переменную
на выходе получается не все так однозначно

[vba]

Код

'A1 пусто
Test1 =  329
Test1 =  312
Test1 =  328
Test1 =  328
Test1 =  312
Test1 =  313
Test1 =  328
Test1 =  328
Test1 =  328
Test1 =  312
Test2 =  328
Test2 =  328
Test2 =  328
Test2 =  328
Test2 =  328
Test2 =  328
Test2 =  344
Test2 =  328
Test2 =  328
Test2 =  329
Test3 =  531
Test3 =  515
Test3 =  516
Test3 =  516
Test3 =  531
Test3 =  516
Test3 =  531
Test3 =  515
Test3 =  516
Test3 =  531
'A1 = 0
Test1 =  469
Test1 =  453
Test1 =  453
Test1 =  453
Test1 =  468
Test1 =  469
Test1 =  469
Test1 =  469
Test1 =  453
Test1 =  469
Test2 =  468
Test2 =  469
Test2 =  453
Test2 =  484
Test2 =  469
Test2 =  469
Test2 =  468
Test2 =  469
Test2 =  469
Test2 =  484
Test3 =  625
Test3 =  641
Test3 =  641
Test3 =  640
Test3 =  641
Test3 =  640
Test3 =  641
Test3 =  641
Test3 =  640
Test3 =  641
'A1 = 777
Test1 =  641
Test1 =  640
Test1 =  641
Test1 =  641
Test1 =  625
Test1 =  656
Test1 =  625
Test1 =  656
Test1 =  625
Test1 =  656
Test2 =  625
Test2 =  641
Test2 =  641
Test2 =  625
Test2 =  625
Test2 =  625
Test2 =  640
Test2 =  641
Test2 =  625
Test2 =  625
Test3 =  813
Test3 =  828
Test3 =  828
Test3 =  797
Test3 =  844
Test3 =  812
Test3 =  828
Test3 =  829
Test3 =  828
Test3 =  828
'A1 = lkj
Test1 =  1109
Test1 =  1094
Test1 =  1094
Test1 =  1109
Test1 =  1109
Test1 =  1110
Test1 =  1093
Test1 =  1110
Test1 =  1109
Test1 =  1172
Test2 =  1125
Test2 =  1109
Test2 =  1094
Test2 =  1094
Test2 =  1109
Test2 =  1094
Test2 =  1109
Test2 =  1109
Test2 =  1094
Test2 =  1094
Test3 =  406
Test3 =  391
Test3 =  390
Test3 =  407
Test3 =  406
Test3 =  406
Test3 =  406
Test3 =  391
Test3 =  422
Test3 =  406

[/vba]

[/offtop]

[offtop]для чистоты эксперимента считываю значение из A1 в переменную
на выходе получается не все так однозначно

[vba]

Код

'A1 пусто
Test1 =  329
Test1 =  312
Test1 =  328
Test1 =  328
Test1 =  312
Test1 =  313
Test1 =  328
Test1 =  328
Test1 =  328
Test1 =  312
Test2 =  328
Test2 =  328
Test2 =  328
Test2 =  328
Test2 =  328
Test2 =  328
Test2 =  344
Test2 =  328
Test2 =  328
Test2 =  329
Test3 =  531
Test3 =  515
Test3 =  516
Test3 =  516
Test3 =  531
Test3 =  516
Test3 =  531
Test3 =  515
Test3 =  516
Test3 =  531
'A1 = 0
Test1 =  469
Test1 =  453
Test1 =  453
Test1 =  453
Test1 =  468
Test1 =  469
Test1 =  469
Test1 =  469
Test1 =  453
Test1 =  469
Test2 =  468
Test2 =  469
Test2 =  453
Test2 =  484
Test2 =  469
Test2 =  469
Test2 =  468
Test2 =  469
Test2 =  469
Test2 =  484
Test3 =  625
Test3 =  641
Test3 =  641
Test3 =  640
Test3 =  641
Test3 =  640
Test3 =  641
Test3 =  641
Test3 =  640
Test3 =  641
'A1 = 777
Test1 =  641
Test1 =  640
Test1 =  641
Test1 =  641
Test1 =  625
Test1 =  656
Test1 =  625
Test1 =  656
Test1 =  625
Test1 =  656
Test2 =  625
Test2 =  641
Test2 =  641
Test2 =  625
Test2 =  625
Test2 =  625
Test2 =  640
Test2 =  641
Test2 =  625
Test2 =  625
Test3 =  813
Test3 =  828
Test3 =  828
Test3 =  797
Test3 =  844
Test3 =  812
Test3 =  828
Test3 =  829
Test3 =  828
Test3 =  828
'A1 = lkj
Test1 =  1109
Test1 =  1094
Test1 =  1094
Test1 =  1109
Test1 =  1109
Test1 =  1110
Test1 =  1093
Test1 =  1110
Test1 =  1109
Test1 =  1172
Test2 =  1125
Test2 =  1109
Test2 =  1094
Test2 =  1094
Test2 =  1109
Test2 =  1094
Test2 =  1109
Test2 =  1109
Test2 =  1094
Test2 =  1094
Test3 =  406
Test3 =  391
Test3 =  390
Test3 =  407
Test3 =  406
Test3 =  406
Test3 =  406
Test3 =  391
Test3 =  422
Test3 =  406

[/vba]

[/offtop]krosav4ig

К сообщению приложен файл: test.xls (50.5 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Воскресенье, 22.03.2015, 03:11

Ответить

Сообщение[offtop]для чистоты эксперимента считываю значение из A1 в переменную
на выходе получается не все так однозначно

[vba]

Код

'A1 пусто
Test1 =  329
Test1 =  312
Test1 =  328
Test1 =  328
Test1 =  312
Test1 =  313
Test1 =  328
Test1 =  328
Test1 =  328
Test1 =  312
Test2 =  328
Test2 =  328
Test2 =  328
Test2 =  328
Test2 =  328
Test2 =  328
Test2 =  344
Test2 =  328
Test2 =  328
Test2 =  329
Test3 =  531
Test3 =  515
Test3 =  516
Test3 =  516
Test3 =  531
Test3 =  516
Test3 =  531
Test3 =  515
Test3 =  516
Test3 =  531
'A1 = 0
Test1 =  469
Test1 =  453
Test1 =  453
Test1 =  453
Test1 =  468
Test1 =  469
Test1 =  469
Test1 =  469
Test1 =  453
Test1 =  469
Test2 =  468
Test2 =  469
Test2 =  453
Test2 =  484
Test2 =  469
Test2 =  469
Test2 =  468
Test2 =  469
Test2 =  469
Test2 =  484
Test3 =  625
Test3 =  641
Test3 =  641
Test3 =  640
Test3 =  641
Test3 =  640
Test3 =  641
Test3 =  641
Test3 =  640
Test3 =  641
'A1 = 777
Test1 =  641
Test1 =  640
Test1 =  641
Test1 =  641
Test1 =  625
Test1 =  656
Test1 =  625
Test1 =  656
Test1 =  625
Test1 =  656
Test2 =  625
Test2 =  641
Test2 =  641
Test2 =  625
Test2 =  625
Test2 =  625
Test2 =  640
Test2 =  641
Test2 =  625
Test2 =  625
Test3 =  813
Test3 =  828
Test3 =  828
Test3 =  797
Test3 =  844
Test3 =  812
Test3 =  828
Test3 =  829
Test3 =  828
Test3 =  828
'A1 = lkj
Test1 =  1109
Test1 =  1094
Test1 =  1094
Test1 =  1109
Test1 =  1109
Test1 =  1110
Test1 =  1093
Test1 =  1110
Test1 =  1109
Test1 =  1172
Test2 =  1125
Test2 =  1109
Test2 =  1094
Test2 =  1094
Test2 =  1109
Test2 =  1094
Test2 =  1109
Test2 =  1109
Test2 =  1094
Test2 =  1094
Test3 =  406
Test3 =  391
Test3 =  390
Test3 =  407
Test3 =  406
Test3 =  406
Test3 =  406
Test3 =  391
Test3 =  422
Test3 =  406

[/vba]

[/offtop]

Автор - krosav4ig
Дата добавления - 22.03.2015 в 03:02

Дата: Воскресенье, 22.03.2015, 17:13 | Сообщение № 15

Группа: Друзья

Ранг: Ветеран

Сообщений: 770

Репутация: 255 ±

Замечаний: 0% ±

Excel 2013

Наоборот, все ясно и понятно!

При числовых значениях A1 для Test1 и Test2 нет неявных преобразований типов, а для Test3 есть, поэтому он чуть медленнее, а при текстовом значении A1, наоборот, преобразования типов есть для Test1 и Test2, а для Test3 - нет, поэтому он и быстрее.
И наглядно видно, что больше половины времени уходило на считывание из ячейки, соответственно, чем меньше обращений к ячейкам, тем быстрее операции... (это же касается и формул Excel, а не только кода VBA)

KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333

Ответить

Дата: Понедельник, 23.03.2015, 21:30 | Сообщение № 16

Группа: Друзья

Ранг: Старожил

Сообщений: 2348

Репутация: 997 ±

Замечаний: 0% ±

Excel 2007,2010,2013

[offtop]KSV, ну про неявное приведение типа мне тоже понятно, я имел ввиду разницу времени выполнения между логическим И и произведением при числовых(!=0) и текстовых значениях A1[/offtop]

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Вторник, 24.03.2015, 16:37

Ответить

Дата: Понедельник, 23.03.2015, 23:13 | Сообщение № 17

Группа: Друзья

Ранг: Ветеран

Сообщений: 770

Репутация: 255 ±

Замечаний: 0% ±

Excel 2013

Да, действительно, странно...
При A1 != 0 и при A1 = "текст", средние значения различаются так же незначительно, но в пользу умножения...
(для наглядности различий, увеличил кол-во итераций до 10 млн., время выполнения стало ~5±0.2 сек. для чисел, и 3±0.15 для текста, что еще больше запутывает...)
Ведь, рассуждая логически, при числовых значениях A1,
операции сравнения выглядят так: (("777" <> "") ... (777 = 0)),
а при текстовых значениях – так: (("txt" <> "") ... ("txt" = "0")),
и получается, что сравнить текст с текстом быстрее, чем целое число с целым числом? Это же абсурд!...
Но, видимо, у процессора своя логика...

KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333

Ответить

Дата: Понедельник, 23.03.2015, 23:22 | Сообщение № 18

Группа: Админы

Ранг: Местный житель

Сообщений: 19516

Репутация: 4632 ±

Замечаний: ±

Excel 365 & Mac Excel

Сергей, предлагаю Вам создать отдельную тему, а я перенесу туда посты, касающиеся тестирования скорости выполнения условий. В этой теме это оффтоп. ОК?

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Дата: Вторник, 24.03.2015, 20:07 | Сообщение № 19

Группа: Друзья

Ранг: Ветеран

Сообщений: 770

Репутация: 255 ±

Замечаний: 0% ±

Excel 2013

Елена, прошу прощения!
Переносить не нужно, мы закончили.

KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333

Ответить

Закраска ячеек, которые содержат нуль и не равны пусто (Условное Форматирование/Conditional Formattings)

Страница 1 из 1
1

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