Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Убрать автоматическое изменение формата ячейки при изменении формулы
 
#1
09.03.2010 15:16:53
Опять достал навязчивый сервис от MS...  
 
Excel 2003  
 
В ячейке B2 введено значение типа А123Б. Есть ещё ячейка. Формат её Числовой с двумя десятичными знаками. В ячейке формула типа =ПСТР(B2;2;3)*1. Отображается правильно, т.е. 123.00  
Захожу в ячейку для корректировки формулы вручную. Меняю формулу на, например, =ПСТР(B2;2;2)*1. Но вместо ожидаемого значения 12.00 отображается сама формула (=ПСТР(B2;2;2)*1) и формат ячейки самопроизвольно становится текстовым. Приходится опять менять формат ячейки на числовой, входить в ячейку и нажимать Enter.  
 
Как избавиться от этого маразма?  
PS. В Excel 97 такого идиотизма не было...
 
 
 
#2
09.03.2010 15:43:04
Это последствие неявного преобразования типов, имеется в виду умножение на единицу текстового значения.  
 
Рекомендации следующие:  
Вариант 1: установить формат ячейки B2, на которую ссылается формула, не текстовым, а общим.    
Вариант 2:  явно преобразовать в число =Ч(ПСТР(B2;2;3)*1)  
Вариант 3 (лучший):  =--ПСТР(B2;2;3)
 
 
 
#3
09.03.2010 16:42:34
ZVI, подскажите, пожалуйста, что означают два минуса в формуле =--ПСТР(B2;2;3)?  
 
спасибо заранее
 
 
 
#4
09.03.2010 17:09:48
Попробуйте набрать в поиске по Форуму: двойное отрицание
 
 
 
#5
09.03.2010 17:44:29
или "бинарное отрицание"
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#6
10.03.2010 01:12:19
Обсуждалось здесь не раз и достаточно подробно. Сейчас проверил такую тему и обнаружил, что один "роман" все же исчез после летнего сбоя сервера. Но так как "рукописи не горят" :-) , то привожу копию:    
----  
В Excel есть так называемое неявное преобразование типов. Например, формула ="2" * 3, перемножающая текстовое значение "2" на числовое, автоматически преобразует текстовое значение "2" в числовое 2. Результат будет равен 6, как будто перемножались два числа.  
 
Такое преобразование происходит при любым математических действиях: сложении, вычитании, умножении, возведении в степень и проч., включая смену знака на противоположный. Так -"2" автоматически преобразуется в отрицательное число -2.  
А если еще раз сменить знак --"2", то получим число 2.  
 
Если записать в ячейку формулу: =-(-"2") и с помощью меню Сервис - Зависимости формул - Вычислить формулу - Вычислить(2 раза) посмотреть, что с ней сделает Excel, то эволюция будет такой: -(-"2"), потом -(-2), а затем 2.  
 
Важно отметить, что если записать формулу без скобок =--"2", то --"2" преобразуется в число 2 без промежуточных действий (в одно действие).  
Аналогичные преобразования будут и при добавлении к текстовому значению нуля ="911"+0, умножении текста на единицу ="02"*1 и прочих математических действиях, не меняющих результата мат. выражения.  
 
Подобные преобразования Excel выполняет не только с текстовыми значениями, но и с логическими:  
=ЛОЖЬ+0 или =ЛОЖЬ*1 или =--ЛОЖЬ дает 0, так как логическое значение ЛОЖЬ автоматически преобразуется в 0 (ноль).  
=ИСТИНА+0 или = ИСТИНА*1 или =-- ИСТИНА дает 1, так как логическое значение ИСТИНА автоматически преобразуется в 1.  
 
Любое математическое действие само по себе запускает механизм преобразования в числа текстовых значений математического выражения:  
="3"+"5" выдает числовое значение 8      
Собственно, поэтому =СУММПРОИЗВ(--(Выражение1=Условие); --(Выражение2)) можно заменить на =СУММПРОИЗВ((Выражение1=Условие)*(Выражение2)), т.к. математическое действие умножения автоматически запускает механизм преобразования типов множителей, и дополнительных преобразователей типа двойного отрицания уже не требуется.  
 
Ну и, наконец, почему всё же часто используют именно двойное отрицание, а не добавление нуля или умножение на единицу?    
Ответ прост: двойное отрицание теоретически выполняется быстрее умножения на 1, сложения с нулем и других математических действий. Экономия времени вычисления небольшая, и по некоторым оценкам составляет примерно 15%  
----
 
 
 
#7
10.03.2010 09:29:29
ZVI,  
а разъясните, пожалуйста, почему  
в формулах ИСТИНА=1, ЛОЖЬ=0,  
а в VBA True=-1, False=0 ( CInt(True)=-1, CInt(False)=0, но при этом CStr(True)="ИСТИНА", CStr(False)="ЛОЖЬ")  
 
постоянно из-за этого путаюсь, а особенно, когда нужно макросом нужно считать с листа результат работы формулы.
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#8
13.03.2010 18:49:07
Спасибо за объяснения. Особенно ZVI.  
А ответ на вопрос от Alex_ST тоже хотелось бы услышать. Но это, наверное, к MS.
 
 
 
#9
13.03.2010 22:54:44
Общеизвестно, что числа в компьютере хранятся в бинарном представлении, то есть сочетанием нулей и единиц, так как элементарная ячейка памяти может иметь только два состояния. Если же нужно этими сочетаниями представлять отрицательные, положительные значения и ноль, то нужно один бит (2-чный разряд) зарезервировать для знака. Что из этого получается, посмотрите здесь:    
http://ru.wikipedia.org/wiki/Дополнительный_код_(представление_числа)  
 
И Excel и VBA хранит значение ЛОЖЬ (False) как 0, а ИСТИНА (True) как -1 или FFFF в 16-чной системе счисления.  
 
Но формулы Excel созданы для нормальных людей, поэтому в формулах разработчики Excel любезно выполняют так называемое неявное преобразование типов, преобразовывая True в 1. Это удобно, особенно  для формул массивов. Подробнее об этом было выше.    
 
А VBA - для тех, кто типа программист, то есть знает, как хранятся числа и умеет использовать бинарные операции с числами. Можете, кстати, проверить свои знания выполнения бинарных операций. Для этого попробуйте предсказать, не выполняя кода, какое сообщение получится если выполнить такой вот макрос:  
 
Sub Test()  
 MsgBox Not 2  
End Sub  
 
Не расстраивайтесь, если не угадаете, профи ведь тоже ошибаются :-)  
 
Если же в VBA потребуется выполнять преобразования True в единицу, аналогичные Excel, то для этого существует функция Evaluate(выражение), или, что то же самое,  выражение  в квадратных скобках [Выражение]
Например:    
 
' Использование Evaluate()  
Sub Test1()  
 MsgBox Evaluate("(1+1=2)+0")  
End Sub  
 
' То же самое, но квадратными скобками  
Sub Test2()  
 MsgBox [(1+1=2)+0]
End Sub
 
 
 
#10
13.03.2010 23:01:23
Еще парочка примеров попроще по проверке знаний бинарным операций, попробуйте предугадать результат:  
 
Sub Test3()  
 MsgBox 2 Or 4  
End Sub  
 
Sub Test4()  
 MsgBox 2 And 4  
End Sub
 
 
 
#11
13.03.2010 23:05:03
данунафиг) я ничего не понял почему так выходит)
 
 
 
#12
13.03.2010 23:38:39
Бинарное представление чисел Integer типа.  
 
Десятичн ... Бинарн(Integer)  
-32768 ..... 1000 0000 0000 0000 ' самое.отриц.число  
...  
-3 .......... 1111 1111 1111 1101  
-2 .......... 1111 1111 1111 1110  
-1 .......... 1111 1111 1111 1111  
0 .......... 0000 0000 0000 0000  
1 .......... 0000 0000 0000 0001  
2 .......... 0000 0000 0000 0010  
3 .......... 0000 0000 0000 0011  
4 .......... 0000 0000 0000 0100  
5 .......... 0000 0000 0000 0101  
6 .......... 0000 0000 0000 0110  
...  
32767 ....... 0111 1111 1111 1111 ' макс. полож. число  
 
Оператора Or, And, Not выполняют поразрядные (побитные) операции.  
 
Таблица для Or  
0 Or 0 = 0  
1 Or 0 = 1  
0 Or 1 = 1  
1 Or 1 = 1  
 
Таблица для And  
0 And 0 = 0  
1 And 0 = 0  
0 And 1 = 0  
1 And 1 = 1  
 
Таблица для Not  
0 = Not 1  
1 = Not 0  
 
Пример 2 Or 4  
Дес ....... Двоичн (бинарн)  
2 ......... 0000 0000 0000 0010  
4 ......... 0000 0000 0000 0100  
=  
6 ......... 0000 0000 0000 0110  
 
Пример 2 And 4  
Дес ....... Двоичн (бинарн)  
2 ......... 0000 0000 0000 0010  
4 ......... 0000 0000 0000 0100  
=  
0 ......... 0000 0000 0000 0000  
 
Пример Not 2  
Дес ....... Двоичн (бинарн)  
2 ......... 0000 0000 0000 0010  
=  
? ......... 1111 1111 1111 1101  
По таблице выше найдите сами, какому десятичному числу соответствует результат
 
 
 
#13
14.03.2010 14:56:51
спасибо) немного прояснилось)
 
 
 
Страницы: 1
Наверх