Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

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

 

= Мир MS Excel/исключить подсчёт значений-ошибок - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » исключить подсчёт значений-ошибок (Формулы/Formulas)
исключить подсчёт значений-ошибок
Gold_Barsik Дата: Вторник, 16.07.2019, 23:40 | Сообщение № 1
Группа: Проверенные
Ранг: Обитатель
Сообщений: 459
Репутация: 6 ±
Замечаний: 0% ±

2003
Всем привет!

В лист вносятся как значения, так и считанные ошибки (типа "Е2"). Формула пересчитывает Значения в СРЕДНЕЕ, но если есть ошибка типа "Е2", то принимает её за значение и выдаёт неверный результат. В ячейке K29 вместо 6,3 стоит значение 6,3/2, т.е 3,15.
Как исключить подсчёт ошибок?
К сообщению приложен файл: _3.xls (87.0 Kb)
 
Ответить
СообщениеВсем привет!

В лист вносятся как значения, так и считанные ошибки (типа "Е2"). Формула пересчитывает Значения в СРЕДНЕЕ, но если есть ошибка типа "Е2", то принимает её за значение и выдаёт неверный результат. В ячейке K29 вместо 6,3 стоит значение 6,3/2, т.е 3,15.
Как исключить подсчёт ошибок?
Автор - Gold_Barsik
Дата добавления - 16.07.2019 в 23:40
bigor Дата: Среда, 17.07.2019, 00:15 | Сообщение № 2
Группа: Проверенные
Ранг: Старожил
Сообщений: 1099
Репутация: 209 ±
Замечаний: 0% ±

нет
Цитата Gold_Barsik, 16.07.2019 в 23:40, в сообщении № 1 ()
Как исключить подсчёт ошибок?


вычесть из количества ячеек, ячейки с текстовым содержимым

Код
=IF(OR((SUMIF($Лист2.A:A;"<"&G29+1;$Лист2.B:B)-SUMIF($Лист2.A:A;"<"&G29;$Лист2.B:B))=0;(COUNTIF($Лист2.A:A;"<"&G29+1)-COUNTIF($Лист2.A:A;"<"&G29))=0);0;(SUMIF($Лист2.A:A;"<"&G29+1;$Лист2.B:B)-SUMIF($Лист2.A:A;"<"&G29;$Лист2.B:B))/(SUMPRODUCT((INT($Лист2.A4:A470)=INT(G29))*ISNUMBER($Лист2.B4:B470))))


Сообщение отредактировал bigor - Среда, 17.07.2019, 00:39
 
Ответить
Сообщение
Цитата Gold_Barsik, 16.07.2019 в 23:40, в сообщении № 1 ()
Как исключить подсчёт ошибок?


вычесть из количества ячеек, ячейки с текстовым содержимым

Код
=IF(OR((SUMIF($Лист2.A:A;"<"&G29+1;$Лист2.B:B)-SUMIF($Лист2.A:A;"<"&G29;$Лист2.B:B))=0;(COUNTIF($Лист2.A:A;"<"&G29+1)-COUNTIF($Лист2.A:A;"<"&G29))=0);0;(SUMIF($Лист2.A:A;"<"&G29+1;$Лист2.B:B)-SUMIF($Лист2.A:A;"<"&G29;$Лист2.B:B))/(SUMPRODUCT((INT($Лист2.A4:A470)=INT(G29))*ISNUMBER($Лист2.B4:B470))))
Автор - bigor
Дата добавления - 17.07.2019 в 00:15
Nic70y Дата: Среда, 17.07.2019, 08:52 | Сообщение № 3
Группа: Друзья
Ранг: Экселист
Сообщений: 8759
Репутация: 2272 ±
Замечаний: 0% ±

Excel 2010
в примере даты по-порядку, тогда можно так
Код
=СУММ(ИНДЕКС(Лист2!B:B;СЧЁТЕСЛИ(Лист2!A:A;"<"&G29)+4):ИНДЕКС(Лист2!B:B;СЧЁТЕСЛИ(Лист2!A:A;"<"&G29+1)+3))/МАКС(1;СЧЁТ(ИНДЕКС(Лист2!B:B;СЧЁТЕСЛИ(Лист2!A:A;"<"&G29)+4):ИНДЕКС(Лист2!B:B;СЧЁТЕСЛИ(Лист2!A:A;"<"&G29+1)+3)))

ЮMoney 41001841029809
 
Ответить
Сообщениев примере даты по-порядку, тогда можно так
Код
=СУММ(ИНДЕКС(Лист2!B:B;СЧЁТЕСЛИ(Лист2!A:A;"<"&G29)+4):ИНДЕКС(Лист2!B:B;СЧЁТЕСЛИ(Лист2!A:A;"<"&G29+1)+3))/МАКС(1;СЧЁТ(ИНДЕКС(Лист2!B:B;СЧЁТЕСЛИ(Лист2!A:A;"<"&G29)+4):ИНДЕКС(Лист2!B:B;СЧЁТЕСЛИ(Лист2!A:A;"<"&G29+1)+3)))
Автор - Nic70y
Дата добавления - 17.07.2019 в 08:52
Gold_Barsik Дата: Среда, 17.07.2019, 12:18 | Сообщение № 4
Группа: Проверенные
Ранг: Обитатель
Сообщений: 459
Репутация: 6 ±
Замечаний: 0% ±

2003
bigor, Если по удалять $ и . (точку) после Лист, то что то считает. Но при практическом использовании в файле (при смене дат) работает неверно.

Nic70y, не работает :( (
К сообщению приложен файл: _4.xls (93.5 Kb)


Сообщение отредактировал Gold_Barsik - Среда, 17.07.2019, 12:18
 
Ответить
Сообщениеbigor, Если по удалять $ и . (точку) после Лист, то что то считает. Но при практическом использовании в файле (при смене дат) работает неверно.

Nic70y, не работает :( (
Автор - Gold_Barsik
Дата добавления - 17.07.2019 в 12:18
Nic70y Дата: Среда, 17.07.2019, 12:45 | Сообщение № 5
Группа: Друзья
Ранг: Экселист
Сообщений: 8759
Репутация: 2272 ±
Замечаний: 0% ±

Excel 2010
Цитата Gold_Barsik, 17.07.2019 в 12:18, в сообщении № 4 ()
не работает
ну да,
моя формула на 1, существующий день,
на остальное не смотрел...

ЮMoney 41001841029809
 
Ответить
Сообщение
Цитата Gold_Barsik, 17.07.2019 в 12:18, в сообщении № 4 ()
не работает
ну да,
моя формула на 1, существующий день,
на остальное не смотрел...
Автор - Nic70y
Дата добавления - 17.07.2019 в 12:45
bigor Дата: Среда, 17.07.2019, 13:39 | Сообщение № 6
Группа: Проверенные
Ранг: Старожил
Сообщений: 1099
Репутация: 209 ±
Замечаний: 0% ±

нет
Цитата Gold_Barsik, 17.07.2019 в 12:18, в сообщении № 4 ()
Если по удалять $ и . (точку) после Лист, то что то считает.

прошу прощения, формулу делал в LO.
Массивность там не нужна, проверял на нескольких датах, укажите на ошибку в конкретной дате
 
Ответить
Сообщение
Цитата Gold_Barsik, 17.07.2019 в 12:18, в сообщении № 4 ()
Если по удалять $ и . (точку) после Лист, то что то считает.

прошу прощения, формулу делал в LO.
Массивность там не нужна, проверял на нескольких датах, укажите на ошибку в конкретной дате
Автор - bigor
Дата добавления - 17.07.2019 в 13:39
Gold_Barsik Дата: Среда, 17.07.2019, 14:34 | Сообщение № 7
Группа: Проверенные
Ранг: Обитатель
Сообщений: 459
Репутация: 6 ±
Замечаний: 0% ±

2003
bigor,
Цитата
формулу делал в LO
это что за зверь?

значение-ошибка это E2 и ей подобные. При определённых фактах в приборе происходят ошибки и высвечиваются как Е2, Е3 и т.д. до Е11. Но так как на эти операции затрачены тест-полоски, то приходиться их вносить вместе со значениями через форму Лист1 на Лист2. Так в строке 312 Лист2 образовалась эта проблема. Нужно что бы при текущем и дальнейшем подсчёте среднего значения как то исключать подобные ошибки.


Сообщение отредактировал Gold_Barsik - Среда, 17.07.2019, 14:36
 
Ответить
Сообщениеbigor,
Цитата
формулу делал в LO
это что за зверь?

значение-ошибка это E2 и ей подобные. При определённых фактах в приборе происходят ошибки и высвечиваются как Е2, Е3 и т.д. до Е11. Но так как на эти операции затрачены тест-полоски, то приходиться их вносить вместе со значениями через форму Лист1 на Лист2. Так в строке 312 Лист2 образовалась эта проблема. Нужно что бы при текущем и дальнейшем подсчёте среднего значения как то исключать подобные ошибки.
Автор - Gold_Barsik
Дата добавления - 17.07.2019 в 14:34
bigor Дата: Среда, 17.07.2019, 14:53 | Сообщение № 8
Группа: Проверенные
Ранг: Старожил
Сообщений: 1099
Репутация: 209 ±
Замечаний: 0% ±

нет
Еще раз - формула приведенная мной, не массивная, вводится простым нажатием Enter. Если она где-то неправильно считает, то укажите дату, в которой ошибка.

[offtop]
LO - LibreOffice
 
Ответить
СообщениеЕще раз - формула приведенная мной, не массивная, вводится простым нажатием Enter. Если она где-то неправильно считает, то укажите дату, в которой ошибка.

[offtop]
LO - LibreOffice
Автор - bigor
Дата добавления - 17.07.2019 в 14:53
Gold_Barsik Дата: Среда, 17.07.2019, 15:23 | Сообщение № 9
Группа: Проверенные
Ранг: Обитатель
Сообщений: 459
Репутация: 6 ±
Замечаний: 0% ±

2003
Ну как то так выдаёт
период дата: Ваша формула
1 сутки 16.07.19 6,3 (верно)
10 суток 17.07.19 5,6 (не верно)
14 суток 17.07.19 5,6 (не верно)

период дата: Ваша формула
1 сутки 17.07.19 5,6 (верно)
10 суток 17.07.19 5,6 (не верно)
14 суток 17.07.19 5,6 (не верно)

период дата: Ваша формула
1 сутки 18.07.19 0 (верно)
10 суток 18.07.19 0 (не верно)
14 суток 18.07.19 0 (не верно)

период дата: Ваша формула
1 сутки 18.07.19 0 (верно)
14 суток 18.07.19 0 (не верно)
14 суток 18.07.19 0 (не верно)

т.е. при изменении периода во второй строке результат Не верный
при изменении периода в третьей строке (большую сторону) результат Не верный
в сравнении с предыдущими показателями в скобках мои умозаключения

формула считает то правильно но не пользуется условиями поставленной задачи. Наверно отсюда и не верный результат. Имхо


Сообщение отредактировал Gold_Barsik - Среда, 17.07.2019, 15:34
 
Ответить
СообщениеНу как то так выдаёт
период дата: Ваша формула
1 сутки 16.07.19 6,3 (верно)
10 суток 17.07.19 5,6 (не верно)
14 суток 17.07.19 5,6 (не верно)

период дата: Ваша формула
1 сутки 17.07.19 5,6 (верно)
10 суток 17.07.19 5,6 (не верно)
14 суток 17.07.19 5,6 (не верно)

период дата: Ваша формула
1 сутки 18.07.19 0 (верно)
10 суток 18.07.19 0 (не верно)
14 суток 18.07.19 0 (не верно)

период дата: Ваша формула
1 сутки 18.07.19 0 (верно)
14 суток 18.07.19 0 (не верно)
14 суток 18.07.19 0 (не верно)

т.е. при изменении периода во второй строке результат Не верный
при изменении периода в третьей строке (большую сторону) результат Не верный
в сравнении с предыдущими показателями в скобках мои умозаключения

формула считает то правильно но не пользуется условиями поставленной задачи. Наверно отсюда и не верный результат. Имхо
Автор - Gold_Barsik
Дата добавления - 17.07.2019 в 15:23
bigor Дата: Среда, 17.07.2019, 15:40 | Сообщение № 10
Группа: Проверенные
Ранг: Старожил
Сообщений: 1099
Репутация: 209 ±
Замечаний: 0% ±

нет
про десять суток ничего не было в первоначальном условии.
Цитата Gold_Barsik, 16.07.2019 в 23:40, в сообщении № 1 ()
В ячейке K29 вместо 6,3 стоит значение 6,3/2, т.е 3,15.

для K29 и делал.
 
Ответить
Сообщениепро десять суток ничего не было в первоначальном условии.
Цитата Gold_Barsik, 16.07.2019 в 23:40, в сообщении № 1 ()
В ячейке K29 вместо 6,3 стоит значение 6,3/2, т.е 3,15.

для K29 и делал.
Автор - bigor
Дата добавления - 17.07.2019 в 15:40
Светлый Дата: Среда, 17.07.2019, 18:44 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Можно проще. Массивная формула:
Код
=ЕСЛИ(СУММ((Лист2!A:A<G29+1)*(Лист2!A:A>=G29)*(Лист2!B:B<99));СРЗНАЧ(ЕСЛИ((Лист2!A:A<G29+1)*(Лист2!A:A>=G29);Лист2!B:B));)

Программировать проще, чем писать стихи.
 
Ответить
СообщениеМожно проще. Массивная формула:
Код
=ЕСЛИ(СУММ((Лист2!A:A<G29+1)*(Лист2!A:A>=G29)*(Лист2!B:B<99));СРЗНАЧ(ЕСЛИ((Лист2!A:A<G29+1)*(Лист2!A:A>=G29);Лист2!B:B));)
Автор - Светлый
Дата добавления - 17.07.2019 в 18:44
Gold_Barsik Дата: Среда, 17.07.2019, 20:37 | Сообщение № 12
Группа: Проверенные
Ранг: Обитатель
Сообщений: 459
Репутация: 6 ±
Замечаний: 0% ±

2003
Светлый, Вообще всё по нулям (((
А Вы её не пробовали обкатать на приложенном файле? Только не сочтите за неуважение.


Сообщение отредактировал Gold_Barsik - Среда, 17.07.2019, 20:46
 
Ответить
СообщениеСветлый, Вообще всё по нулям (((
А Вы её не пробовали обкатать на приложенном файле? Только не сочтите за неуважение.
Автор - Gold_Barsik
Дата добавления - 17.07.2019 в 20:37
Gold_Barsik Дата: Среда, 17.07.2019, 20:45 | Сообщение № 13
Группа: Проверенные
Ранг: Обитатель
Сообщений: 459
Репутация: 6 ±
Замечаний: 0% ±

2003
bigor, Так ведь там и не сказано чем заменить имеющуюся формулу.
Было написано:
Цитата
Как исключить подсчёт ошибок?

это значить как в ЭТОЙ формуле исключить просчёт. Формула от Pelena, меня вполне устраивала.


Сообщение отредактировал Gold_Barsik - Среда, 17.07.2019, 22:29
 
Ответить
Сообщениеbigor, Так ведь там и не сказано чем заменить имеющуюся формулу.
Было написано:
Цитата
Как исключить подсчёт ошибок?

это значить как в ЭТОЙ формуле исключить просчёт. Формула от Pelena, меня вполне устраивала.
Автор - Gold_Barsik
Дата добавления - 17.07.2019 в 20:45
bigor Дата: Четверг, 18.07.2019, 08:53 | Сообщение № 14
Группа: Проверенные
Ранг: Старожил
Сообщений: 1099
Репутация: 209 ±
Замечаний: 0% ±

нет
Цитата Gold_Barsik, 17.07.2019 в 20:37, в сообщении № 12 ()
А Вы её не пробовали обкатать на приложенном файле?

А Вы?

Цитата Gold_Barsik, 17.07.2019 в 20:45, в сообщении № 13 ()
Так ведь там и не сказано чем заменить имеющуюся формулу.

Цитата Gold_Barsik, 17.07.2019 в 20:45, в сообщении № 13 ()
это значить как в ЭТОЙ формуле исключить просчёт

я знаю Только два способа: или изменить формулу, что я и сделал или удалить текст из расчетной таблицы, что вряд ли Вас устроит, его же туда зачем-то заносят

Вот формула Светлого и моя в Вашем файле для 1 и 10 дней, с 14 как-нибудь сами
К сообщению приложен файл: __2.xls (83.5 Kb)
 
Ответить
Сообщение
Цитата Gold_Barsik, 17.07.2019 в 20:37, в сообщении № 12 ()
А Вы её не пробовали обкатать на приложенном файле?

А Вы?

Цитата Gold_Barsik, 17.07.2019 в 20:45, в сообщении № 13 ()
Так ведь там и не сказано чем заменить имеющуюся формулу.

Цитата Gold_Barsik, 17.07.2019 в 20:45, в сообщении № 13 ()
это значить как в ЭТОЙ формуле исключить просчёт

я знаю Только два способа: или изменить формулу, что я и сделал или удалить текст из расчетной таблицы, что вряд ли Вас устроит, его же туда зачем-то заносят

Вот формула Светлого и моя в Вашем файле для 1 и 10 дней, с 14 как-нибудь сами
Автор - bigor
Дата добавления - 18.07.2019 в 08:53
Nic70y Дата: Четверг, 18.07.2019, 09:20 | Сообщение № 15
Группа: Друзья
Ранг: Экселист
Сообщений: 8759
Репутация: 2272 ±
Замечаний: 0% ±

Excel 2010
а я думаю, что за фигня такая,
а вон оно шо в ячейке A301 Лист2 2001 год

ЮMoney 41001841029809
 
Ответить
Сообщениеа я думаю, что за фигня такая,
а вон оно шо в ячейке A301 Лист2 2001 год
Автор - Nic70y
Дата добавления - 18.07.2019 в 09:20
Светлый Дата: Четверг, 18.07.2019, 10:31 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата Светлый, 17.07.2019 в 18:44, в сообщении № 11 ()
Массивная формула
Вводится одновременным нажатием Ctrl+Shift+Enter
Цитата Gold_Barsik, 17.07.2019 в 20:37, в сообщении № 12 ()
Только не сочтите за неуважение
Я сам виноват, лень каждый раз делать эту приписку.

Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Цитата Светлый, 17.07.2019 в 18:44, в сообщении № 11 ()
Массивная формула
Вводится одновременным нажатием Ctrl+Shift+Enter
Цитата Gold_Barsik, 17.07.2019 в 20:37, в сообщении № 12 ()
Только не сочтите за неуважение
Я сам виноват, лень каждый раз делать эту приписку.
Автор - Светлый
Дата добавления - 18.07.2019 в 10:31
Gold_Barsik Дата: Четверг, 18.07.2019, 13:51 | Сообщение № 17
Группа: Проверенные
Ранг: Обитатель
Сообщений: 459
Репутация: 6 ±
Замечаний: 0% ±

2003
Nic70y, <_< %) Долго бы я "замечал" hands Спасибо!
Вот только не догоняю, а на что она влияла? Она же тоже ДАТА.

Светлый, Да ни в чём не виноваты, у Вас же написано было
Цитата Светлый, 17.07.2019 в 18:44, в сообщении № 11 ()
Можно проще. Массивная формула:

Я её сделал "массивной" пишет #ЧИСЛО!, это может смотрит первые три строчки? (диапазон Лист2!A:A) ?

bigor, Спасибо! Для Вас может EXCEL семечки, а по мне так это "кокосовый орех для беззубого". :(


Сообщение отредактировал Gold_Barsik - Четверг, 18.07.2019, 14:01
 
Ответить
СообщениеNic70y, <_< %) Долго бы я "замечал" hands Спасибо!
Вот только не догоняю, а на что она влияла? Она же тоже ДАТА.

Светлый, Да ни в чём не виноваты, у Вас же написано было
Цитата Светлый, 17.07.2019 в 18:44, в сообщении № 11 ()
Можно проще. Массивная формула:

Я её сделал "массивной" пишет #ЧИСЛО!, это может смотрит первые три строчки? (диапазон Лист2!A:A) ?

bigor, Спасибо! Для Вас может EXCEL семечки, а по мне так это "кокосовый орех для беззубого". :(
Автор - Gold_Barsik
Дата добавления - 18.07.2019 в 13:51
Gold_Barsik Дата: Среда, 24.07.2019, 19:46 | Сообщение № 18
Группа: Проверенные
Ранг: Обитатель
Сообщений: 459
Репутация: 6 ±
Замечаний: 0% ±

2003
Светлый, Почему формула массива
Код
{=ЕСЛИ(СУММ((Лист2!A:A<G29+1)*(Лист2!A:A>=G29)*(Лист2!B:B<99));СРЗНАЧ(ЕСЛИ((Лист2!A:A<G29+1)*(Лист2!A:A>=G29);Лист2!B:B));)}
вызывает ошибку#ЧИСЛО! ссылаясь на даты хх.хх.хх или хх.хх.хххх, даже в формате Общий (ххххх) ?


Сообщение отредактировал Gold_Barsik - Среда, 24.07.2019, 19:48
 
Ответить
СообщениеСветлый, Почему формула массива
Код
{=ЕСЛИ(СУММ((Лист2!A:A<G29+1)*(Лист2!A:A>=G29)*(Лист2!B:B<99));СРЗНАЧ(ЕСЛИ((Лист2!A:A<G29+1)*(Лист2!A:A>=G29);Лист2!B:B));)}
вызывает ошибку#ЧИСЛО! ссылаясь на даты хх.хх.хх или хх.хх.хххх, даже в формате Общий (ххххх) ?
Автор - Gold_Barsik
Дата добавления - 24.07.2019 в 19:46
Nic70y Дата: Четверг, 25.07.2019, 08:35 | Сообщение № 19
Группа: Друзья
Ранг: Экселист
Сообщений: 8759
Репутация: 2272 ±
Замечаний: 0% ±

Excel 2010
Цитата Gold_Barsik, 18.07.2019 в 13:51, в сообщении № 17 ()
а на что она влияла?
на мои расчеты
Код
=ЕСЛИ(СЧЁТЕСЛИ(Лист2!A:A;"<"&G29-F29+1)+4<СЧЁТЕСЛИ(Лист2!A:A;"<"&G29+1)+3;СРЗНАЧ(ИНДЕКС(Лист2!B:B;СЧЁТЕСЛИ(Лист2!A:A;"<"&G29-F29+1)+4):ИНДЕКС(Лист2!B:B;СЧЁТЕСЛИ(Лист2!A:A;"<"&G29+1)+3));)
"для себя" бы сделал допы, см. второй файл
К сообщению приложен файл: 4144666.xls (89.5 Kb) · 8707278.xls (89.5 Kb)

ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Четверг, 25.07.2019, 09:05
 
Ответить
Сообщение
Цитата Gold_Barsik, 18.07.2019 в 13:51, в сообщении № 17 ()
а на что она влияла?
на мои расчеты
Код
=ЕСЛИ(СЧЁТЕСЛИ(Лист2!A:A;"<"&G29-F29+1)+4<СЧЁТЕСЛИ(Лист2!A:A;"<"&G29+1)+3;СРЗНАЧ(ИНДЕКС(Лист2!B:B;СЧЁТЕСЛИ(Лист2!A:A;"<"&G29-F29+1)+4):ИНДЕКС(Лист2!B:B;СЧЁТЕСЛИ(Лист2!A:A;"<"&G29+1)+3));)
"для себя" бы сделал допы, см. второй файл
Автор - Nic70y
Дата добавления - 25.07.2019 в 08:35
Мир MS Excel » Вопросы и решения » Вопросы по Excel » исключить подсчёт значений-ошибок (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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