Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
периодически не работает формула массива
 
#1
13.04.2010 07:04:51
нашел мощную формулу массива типа автофильтра (выборка по 2 и более критериям!!!!)  
 
http://spreadsheetpage.com/index.php/tip/summing_and_counting_using_multiple_criteria/  
 
НО!!!  
то ли от активности на солнце, то ли еще чего  
размер и положение "просматриваемых диапазонов" меняется. (((((((  
 
например формула  
=SUM((A2:A1000="Jan")*(B2:B1000="North")*C2:C1000)  
сегодня все работает.  
 
завтра добавляю в массив еще 1000 строк получаю формулу  
=SUM((A2:A2000="Jan")*(B2:B2000="North")*C2:C2000)  
НЕ РАБОТАЕТ!!!!  
 
формат ячеек копировал. не помогает.  
было такое: нужно просматривать колонку "А", а формула видит только с колонки "С".  
пришлось вставлять пустые колонки, что бы сместить "А" на место "С"  
 
в чем заковырка?  
хелп плиз.
 
 
 
#2
13.04.2010 08:40:49
После ввода или изменения формулы массива нажать Ctrl+Shift+Enter не забываете?
 
 
 
#3
13.04.2010 08:47:05
{quote}{login=}{date=13.04.2010 08:40}{thema=}{post}После ввода или изменения формулы массива нажать Ctrl+Shift+Enter не забываете?{/post}{/quote}  
 
не забываю.
 
 
 
#4
13.04.2010 09:14:22
вычислите формулу пошагово -посмотрите как и где спотыкается, выложите пример с неработающей формулой.
 
 
 
#5
13.04.2010 09:32:48
{quote}{login=StalkeR}{date=13.04.2010 07:04}{thema=периодически не работает формула массива}{post}было такое: нужно просматривать колонку "А", а формула видит только с колонки "С". пришлось вставлять пустые колонки, что бы сместить "А" на место "С"{/post}{/quote}  
Меня это смутило. В любом случае лучше показать "вредный" файл (Не более 100 Кб!)
 
 
 
#6
13.04.2010 09:38:22
{quote}{login=The_Prist}{date=13.04.2010 09:31}{thema=}{post}Может стоит диапазоны закрепить?  
 
=SUM(($A$2:$A$2000="Jan")*($B$2:$B$2000="North")*$C$2:$C$2000)  
 
А еще можно обойтись без Ctrl+Shift+Enter  
 
=SUMPRODUCT(($A$2:$A$2000="Jan")*($B$2:$B$2000="North");$C$2:$C$2000){/post}{/quote}  
 
извините, а по-русски это как? ))))  
 
я написал по англицки ибо со страницы формулу стащил для простоты. )))
 
 
 
#7
13.04.2010 09:39:20
{quote}{login=vikttur}{date=13.04.2010 09:32}{thema=}{post}{quote}{login=StalkeR}{date=13.04.2010 07:04}{thema=периодически не работает формула массива}{post}было такое: нужно просматривать колонку "А", а формула видит только с колонки "С". пришлось вставлять пустые колонки, что бы сместить "А" на место "С"{/post}{/quote}  
Меня это смутило. В любом случае лучше показать "вредный" файл (Не более 100 Кб!){/post}{/quote}  
 
более. ((( 4,8мб.  
 
архив в мыло?
 
 
 
#8
13.04.2010 09:41:00
{quote}{login=}{date=13.04.2010 09:38}{thema=Re: }{post}{quote}{login=The_Prist}{date=13.04.2010 09:31}{thema=}{post}Может стоит диапазоны закрепить?  
 
=SUM(($A$2:$A$2000="Jan")*($B$2:$B$2000="North")*$C$2:$C$2000)  
 
А еще можно обойтись без Ctrl+Shift+Enter  
 
=SUMPRODUCT(($A$2:$A$2000="Jan")*($B$2:$B$2000="North");$C$2:$C$2000){/post}{/quote}  
 
извините, а по-русски это как? ))))  
 
я написал по англицки ибо со страницы формулу стащил для простоты. ))){/post}{/quote}  
SUM=СУММ  
SUMPRODUCT=СУММПРОИЗВ  
"Jan"=Январь  
North=Север
 
 
 
#9
13.04.2010 09:41:19
Упростите пример, сохранив структуру данных, строк 20-30 достаточно.
 
 
 
#10
13.04.2010 09:42:30
{quote}{login=vikttur}{date=13.04.2010 09:32}{thema=}{post}{quote}{login=StalkeR}{date=13.04.2010 07:04}{thema=периодически не работает формула массива}{post}было такое: нужно просматривать колонку "А", а формула видит только с колонки "С". пришлось вставлять пустые колонки, что бы сместить "А" на место "С"{/post}{/quote}  
Меня это смутило. В любом случае лучше показать "вредный" файл (Не более 100 Кб!){/post}{/quote}  
 
да!!!  
и моем "вредном" файле вопрос по сути в этом!  
 
просматривает со 2 стоки по 900. делаешь больше - ошибка.  
 
один чел говорит проблемы в длинных именах просматриваемых строк.  
но ведь до 900 тоже строки текстовые длинные....
 
 
 
#11
13.04.2010 09:47:40
У Вас еще и "имена строк"? Тем более покажите зверя :)  
По поводу языка тоже настораживает. У Вас локализация русская и формула работала?  
Или Excel английский, но хотите пристроить "русскую" формулу?  
Чем дальше, тем больше вопросов...
 
 
 
#12
13.04.2010 09:51:23
{quote}{login=vikttur}{date=13.04.2010 09:47}{thema=}{post}У Вас еще и "имена строк"? Тем более покажите зверя :)  
По поводу языка тоже настораживает. У Вас локализация русская и формула работала?  
Или Excel английский, но хотите пристроить "русскую" формулу?  
Чем дальше, тем больше вопросов...{/post}{/quote}  
 
возможно я не правильно формулирую названия... )))  
короче суть в файле. только архив иначе 1мб весит  
900 строк считает - выделено желтым  
ставлю 1500 - балалайка  
(((((((((((
 
 
 
#13
13.04.2010 09:59:18
У Вас в ячейке D969 на листе "потреб" стоит "56/0.11". Это значение не суммируется и из-за этого ошибка вылетает при расчетах.
 
 
 
#14
13.04.2010 10:01:05
ну и что? сложно было самому ошибку выцепить чтоли?  
 
у вас в ячейке Д969 стоит значение 56/0.11, что не является числом и выдает ошибку.
 
 
 
#15
13.04.2010 10:03:55
D969=56/0.11 - неверное значение, об него и спотыкается.
 
 
 
#16
13.04.2010 10:32:58
{quote}{login=Dophin}{date=13.04.2010 10:01}{thema=}{post}ну и что? сложно было самому ошибку выцепить чтоли?  
 
у вас в ячейке Д969 стоит значение 56/0.11, что не является числом и выдает ошибку.{/post}{/quote}  
 
прошу прощения, что отнял время.  
всем большое спасибо.  
 
...а сводная суммирует.... 8-\\\\
 
 
 
#17
13.04.2010 10:45:17
{quote}{login=StalkeR}{date=13.04.2010 10:32}{thema=Re: }{post}  
...а сводная суммирует.... 8-\\\\{/post}{/quote}  
Ни фига. Сводная при суммировании просто игнорирует нечисловые значения.  
Проверьте.  
 
В сводной Шплинт 3.2х32.019 ГОСТ397-79 даёт значение 0,60  
из    
Шплинт 3.2х32.019 ГОСТ397-79 кг 0,10  
Шплинт 3.2х32.019 ГОСТ397-79 кг 0,10  
Шплинт 3.2х32.019 ГОСТ397-79 кг 56/0.11  
Шплинт 3.2х32.019 ГОСТ397-79 кг 0,10  
Шплинт 3.2х32.019 ГОСТ397-79 кг 0,10  
Шплинт 3.2х32.019 ГОСТ397-79 кг 0,10  
Шплинт 3.2х32.019 ГОСТ397-79 кг 0,10
 
 
 
#18
13.04.2010 11:44:04
{quote}{login=The_Prist}{date=13.04.2010 10:42}{thema=}{post}=СУММ((потреб!$B$2:$B$1500=$A5)*(потреб!$A$2:$A$1500=D$4)*Ч(потреб!$D$2:$D$1500)){/post}{/quote}  
 
это чего? Ч будет только числовые суммировать?  
когда не важна точность результата - очень даже пригодится 8)
 
 
 
#19
13.04.2010 11:55:45
{quote}{login=Serge 007}{date=13.04.2010 10:45}{thema=Re: Re: }{post}{quote}{login=StalkeR}{date=13.04.2010 10:32}{thema=Re: }{post}  
...а сводная суммирует.... 8-\\\\{/post}{/quote}  
Ни фига. Сводная при суммировании просто игнорирует нечисловые значения.  
Проверьте.  
 
В сводной Шплинт 3.2х32.019 ГОСТ397-79 даёт значение 0,60  
из    
Шплинт 3.2х32.019 ГОСТ397-79 кг 0,10  
Шплинт 3.2х32.019 ГОСТ397-79 кг 0,10  
Шплинт 3.2х32.019 ГОСТ397-79 кг 56/0.11  
Шплинт 3.2х32.019 ГОСТ397-79 кг 0,10  
Шплинт 3.2х32.019 ГОСТ397-79 кг 0,10  
Шплинт 3.2х32.019 ГОСТ397-79 кг 0,10  
Шплинт 3.2х32.019 ГОСТ397-79 кг 0,10{/post}{/quote}  
 
не просчитывал, просто исправил  
 
если число * текст = ошибка  
это есть индикатор что что-то не так  
а если сводная игноририет такие ошибки, то кому верить? (((
 
 
 
#20
13.04.2010 12:02:54
Верить правильным данным. Или ставить проверку, например, =СЧЕТЗ(диапазон)-СЧЕТ(диапазон)
 
 
 
#21
13.04.2010 12:07:55
{quote}{login=StalkeR}{date=13.04.2010 11:55}{thema=Re: Re: Re: }{post}  
если число * текст = ошибка  
это есть индикатор что что-то не так  
а если сводная игноририет такие ошибки, то кому верить? ((({/post}{/quote}  
Что значит "кому верить"?  
Если число * текст = ошибка, то оно и в сводной ошибкой будет. А у Вас не число * текст, а просто "число/текст" (т.е. текст), что ошибкой не является. В документации к сводным это есть. "При суммировании значений текстовые данные игнорируются."  
Как-то так...
 
 
 
#22
13.04.2010 12:31:57
{quote}{login=Serge 007}{date=13.04.2010 12:07}{thema=Re: Re: Re: Re: }{post}{quote}{login=StalkeR}{date=13.04.2010 11:55}{thema=Re: Re: Re: }{post}  
если число * текст = ошибка  
это есть индикатор что что-то не так  
а если сводная игноририет такие ошибки, то кому верить? ((({/post}{/quote}  
Что значит "кому верить"?  
Если число * текст = ошибка, то оно и в сводной ошибкой будет. А у Вас не число * текст, а просто "число/текст" (т.е. текст), что ошибкой не является. В документации к сводным это есть. "При суммировании значений текстовые данные игнорируются."  
Как-то так...{/post}{/quote}  
 
не то имею ввиду.  
берем ту же, пропущенную мной при форматировании, ячейку....  
ставим E969=D969*5  
пишет #ЗНАЧ!  
а сводная проглатывает - это плохо.  
 
если таких строк с материалами и количествами 10000 будет?  
нет ну я конечно буду внимательнее просматривать в следующий раз, но все же...  
((((
 
 
 
#23
13.04.2010 13:00:42
{quote}{login=StalkeR}{date=13.04.2010 12:31}{thema=Re: Re: Re: Re: Re: }Не то имею ввиду.  
берем ту же, пропущенную мной при форматировании, ячейку....  
ставим E969=D969*5  
пишет #ЗНАЧ!  
а сводная проглатывает - это плохо.  
{/post}{/quote}Именно то. Сводная #ЗНАЧ не "проглатывает". В ней тоже будет #ЗНАЧ, если Вы принудительно не поставите что-то в параметрах сводной "Для ячеек с ошибкой отображать   " .  
 
См пример.
 
 
 
Страницы: 1
Читают тему
Наверх