Ошибка в функции Excel СУММЕСЛИ()

[Gamser]

Вчера нашел ошибку :-))
При некоторых условиях функция СУММЕСЛИ возвращает неверный результат
Условия следующие:
Объявляем колонку текстовым типом
например колонку А
Вносим в колонку значения:
20.1
20.2
20.3
20.01
20.02
20.03
В принципе так записываются счета бухучета, на чем и поймал косяк )
В колонку В пишем
1
2
2
4
5
6

В колонке С вносим формулы
=СуммЕсли(A1:A6;"=20.1";B1:B6)
=СуммЕсли(A1:A6;"=20.2";B1:B6)
=СуммЕсли(A1:A6;"=20.3";B1:B6)
=СуммЕсли(A1:A6;"=20.01";B1:B6)
=СуммЕсли(A1:A6;"=20.02";B1:B6)
=СуммЕсли(A1:A6;"=20.03";B1:B6)

И наслаждаемся результатом

Бред во ВСЕХ версиях EXCEL!!!!
Микрософту написал письмо и позвонил
Обещали исправить в следующем выпуске заплатки
на просьбу о подарке - посмеялись :-)))
Сергей Гамзиков

[Ser Artur]

в 2003
=SUMIF(A2:A4,20.1,B2:B4)
очень даже работает

[Pavel55]

Получается, что значения 20.1 и 20.01 (20.2 и 20.02; 20.3 и 20.03) - для функции СУММЕСЛИ одинаковы и она их суммирует). А вот 20.1 и 20.001 уже разные )

[Avsha]

поставил запятые вместо точек в данных столбца A и в формулах столбца С.
нормально считает ...

[Avsha]

Ser Artur

в 2003
=SUMIF(A2:A4,20.1,B2:B4)
очень даже работает

У меня тоже работает ваш вариант, правда необходимо в "Языках и Стандартах" в качестве разделителя целой и дробной части установить "." - точку.

[Aent]

Ну в общем понятно откуда у этой ситуации ноги растут.
Смотрим HELP по СУММЕСЛИ - видим:
-----------------------------------------------------------------------------------------------------------------------------------------------
Синтаксис

СУММЕСЛИ(диапазон;условия;диапазон_суммирования)

Диапазон — диапазон ячеек, оцениваемый по условиям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки, содержащие числа. Пустые ячейки и ячейки, содержащие текстовые значения, не учитываются.
----------------------------------------------------------------------------------------------------------------------------------------------
Т.е. Excel пытается перевести строку в диапазоне критерия выборки в число ....
А ЗНАЧЕН("20.1") = ЗНАЧЕН("20.01") = ЗНАЧЕН("20.янв") = ЗНАЧЕН("20.01.2007") = 39102
при десятичном разделителе - запятой
Так как строка распознаётся как дата а дата потом конвертируется в число ...
Так что это не баг а системная функция
Большое спасибо Gamser, за интересный вопрос.
Отмечу, что в CALC (OpenOffice 2.3) подобная проблема не возникает ...

[Avsha]

Aent,
добавлю в ваш ряд 39102-ых еще пару значений:
=ЗНАЧЕН("20/01")
=ЗНАЧЕН("20/1")
Дата ведь и так вводится в Excel, через /

А когда в "Языках и Стандартах" в качестве разделителя целой и дробной части установлена "." - точка, то наши равенства уже принимают другой вид...
ЗНАЧЕН("20.1") = 20.1
ЗНАЧЕН("20.01") =20.01

[Gamser]

Весь писк в том, что меня не устраивали ни числовые значения, ни значения типа дата, нужно было обработать именно текст "20.1", "20.01"- выгружал данные из 1С в ЕХЕЛ. За что и поплатился

[Aent]

Gamser, для строк нужно просто было использовать в колонке С не СУММЕСЛИ а массивную формулу типа
={СУММ(ЕСЛИ(A1:A6="20.1";1;0)*B1:B6)}
На всякий случай напомню, что для её ввода нужно одновременно нажимать Ctrl+Shift+Enter