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

Вход

Регистрация

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

 

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

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » исключить подсчёт значений-ошибок (Формулы/Formulas)
исключить подсчёт значений-ошибок
Gold_Barsik Дата: Вторник, 16.07.2019, 23:40 | Сообщение № 1
Группа: Проверенные
Ранг: Обитатель
Сообщений: 406
Репутация: 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
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 123
Репутация: 24 ±
Замечаний: 0% ±

Excel 2016
Как исключить подсчёт ошибок?


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

Код
=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
 
Ответить
Сообщение
Как исключить подсчёт ошибок?


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

Код
=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
Группа: Друзья
Ранг: Экселист
Сообщений: 5734
Репутация: 1259 ±
Замечаний: 0% ±

ru13;10;03
в примере даты по-порядку, тогда можно так
Код
=СУММ(ИНДЕКС(Лист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)))


ЯД(poison) 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
Группа: Проверенные
Ранг: Обитатель
Сообщений: 406
Репутация: 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
Группа: Друзья
Ранг: Экселист
Сообщений: 5734
Репутация: 1259 ±
Замечаний: 0% ±

ru13;10;03
не работает
ну да,
моя формула на 1, существующий день,
на остальное не смотрел...


ЯД(poison) 41001841029809
 
Ответить
Сообщение
не работает
ну да,
моя формула на 1, существующий день,
на остальное не смотрел...

Автор - Nic70y
Дата добавления - 17.07.2019 в 12:45
bigor Дата: Среда, 17.07.2019, 13:39 | Сообщение № 6
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 123
Репутация: 24 ±
Замечаний: 0% ±

Excel 2016
Если по удалять $ и . (точку) после Лист, то что то считает.

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

прошу прощения, формулу делал в LO.
Массивность там не нужна, проверял на нескольких датах, укажите на ошибку в конкретной дате

Автор - bigor
Дата добавления - 17.07.2019 в 13:39
Gold_Barsik Дата: Среда, 17.07.2019, 14:34 | Сообщение № 7
Группа: Проверенные
Ранг: Обитатель
Сообщений: 406
Репутация: 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
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 123
Репутация: 24 ±
Замечаний: 0% ±

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

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

[offtop]
LO - LibreOffice

Автор - bigor
Дата добавления - 17.07.2019 в 14:53
Gold_Barsik Дата: Среда, 17.07.2019, 15:23 | Сообщение № 9
Группа: Проверенные
Ранг: Обитатель
Сообщений: 406
Репутация: 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
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 123
Репутация: 24 ±
Замечаний: 0% ±

Excel 2016
про десять суток ничего не было в первоначальном условии.
В ячейке K29 вместо 6,3 стоит значение 6,3/2, т.е 3,15.

для K29 и делал.
 
Ответить
Сообщениепро десять суток ничего не было в первоначальном условии.
В ячейке K29 вместо 6,3 стоит значение 6,3/2, т.е 3,15.

для K29 и делал.

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

Excel 2010, 2013
Можно проще. Массивная формула:
Код
=ЕСЛИ(СУММ((Лист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
Группа: Проверенные
Ранг: Обитатель
Сообщений: 406
Репутация: 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
Группа: Проверенные
Ранг: Обитатель
Сообщений: 406
Репутация: 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
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 123
Репутация: 24 ±
Замечаний: 0% ±

Excel 2016
А Вы её не пробовали обкатать на приложенном файле?

А Вы?

Так ведь там и не сказано чем заменить имеющуюся формулу.

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

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

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

А Вы?

Так ведь там и не сказано чем заменить имеющуюся формулу.

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

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

Вот формула Светлого и моя в Вашем файле для 1 и 10 дней, с 14 как-нибудь сами

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

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


ЯД(poison) 41001841029809
 
Ответить
Сообщениеа я думаю, что за фигня такая,
а вон оно шо в ячейке A301 Лист2 2001 год

Автор - Nic70y
Дата добавления - 18.07.2019 в 09:20
Светлый Дата: Четверг, 18.07.2019, 10:31 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 1368
Репутация: 363 ±
Замечаний: 0% ±

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


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

Автор - Светлый
Дата добавления - 18.07.2019 в 10:31
Gold_Barsik Дата: Четверг, 18.07.2019, 13:51 | Сообщение № 17
Группа: Проверенные
Ранг: Обитатель
Сообщений: 406
Репутация: 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
Группа: Проверенные
Ранг: Обитатель
Сообщений: 406
Репутация: 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
Группа: Друзья
Ранг: Экселист
Сообщений: 5734
Репутация: 1259 ±
Замечаний: 0% ±

ru13;10;03
а на что она влияла?
на мои расчеты
Код
=ЕСЛИ(СЧЁТЕСЛИ(Лист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)


ЯД(poison) 41001841029809

Сообщение отредактировал Nic70y - Четверг, 25.07.2019, 09:05
 
Ответить
Сообщение
а на что она влияла?
на мои расчеты
Код
=ЕСЛИ(СЧЁТЕСЛИ(Лист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-2019 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!