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

Вход

Регистрация

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

 

= Мир MS Excel/Неверный результат СУММЕСЛИМН() или СУММ() - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Неверный результат СУММЕСЛИМН() или СУММ() (Формулы/Formulas)
Неверный результат СУММЕСЛИМН() или СУММ()
ZorKon71 Дата: Воскресенье, 18.09.2022, 00:49 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 35
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Доброго времени суток!

Столкнулся сегодня со странной математической ошибкой.
Думаю функция СУММЕСЛИМН() на это повлияла...

Файл состоит из двух частей.
В 1 находятся данные которые потом складываются во вторую часть по части кода (AVTM_xxx > AVTM).
В обоих частях есть итоги, и есть сводные колонки-итоги (в файле выделены цветом).
Так вот, Оказалось что при сложении во вторую часть, если данные в разных строках, то некоторые числа могут задваиваться при суммировании!!!
Посмотрите коды AVMA и RAT0 (выделил красным).
Задвоение во второй части на сумму 62579 (836+61743) ячейка С47.
Я так думаю что данные правее колонки BL находятся в разных строках для этого кода и почему-то задваивается...
Хотя итоги в обоих частях правильные.
Если что непонятно описал - можно увидеть по формулам...

Файл-пример сделал из рабочего убрав лишнее.
Не ожидал такого фокуса от EXCEL...
К сообщению приложен файл: 2296710.xlsx(45.4 Kb)


Сообщение отредактировал ZorKon71 - Воскресенье, 18.09.2022, 00:50
 
Ответить
СообщениеДоброго времени суток!

Столкнулся сегодня со странной математической ошибкой.
Думаю функция СУММЕСЛИМН() на это повлияла...

Файл состоит из двух частей.
В 1 находятся данные которые потом складываются во вторую часть по части кода (AVTM_xxx > AVTM).
В обоих частях есть итоги, и есть сводные колонки-итоги (в файле выделены цветом).
Так вот, Оказалось что при сложении во вторую часть, если данные в разных строках, то некоторые числа могут задваиваться при суммировании!!!
Посмотрите коды AVMA и RAT0 (выделил красным).
Задвоение во второй части на сумму 62579 (836+61743) ячейка С47.
Я так думаю что данные правее колонки BL находятся в разных строках для этого кода и почему-то задваивается...
Хотя итоги в обоих частях правильные.
Если что непонятно описал - можно увидеть по формулам...

Файл-пример сделал из рабочего убрав лишнее.
Не ожидал такого фокуса от EXCEL...

Автор - ZorKon71
Дата добавления - 18.09.2022 в 00:49
Pelena Дата: Воскресенье, 18.09.2022, 08:39 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 18560
Репутация: 4204 ±
Замечаний: ±

Excel 2016 & Mac Excel
Здравствуйте.
Вот такой подсчёт суммы BK17+AK17+Y17+K17+BO17 чреват ошибками, если суммируемые ячейки пустые или с текстом. Используйте СУММ()
Код
=СУММ(BK17;AK17;Y17;K17;BO17)

в файле в желтых ячейках изменила формулу
К сообщению приложен файл: 4665315.xlsx(39.9 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Вот такой подсчёт суммы BK17+AK17+Y17+K17+BO17 чреват ошибками, если суммируемые ячейки пустые или с текстом. Используйте СУММ()
Код
=СУММ(BK17;AK17;Y17;K17;BO17)

в файле в желтых ячейках изменила формулу

Автор - Pelena
Дата добавления - 18.09.2022 в 08:39
ZorKon71 Дата: Воскресенье, 18.09.2022, 12:21 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 35
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Спасибо!
такой подсчёт суммы BK17+AK17+Y17+K17+BO17 чреват ошибками

Никогда бы не подумал!!!
Получается в моём случае надо так:
Код

=ЕСЛИОШИБКА(СУММ(J1;V1;AH1;BH1;BL1);0)

А это надо наверное и в первой и второй части, если уж чревато ошибками?!
Спасибо ещё раз!!!
 
Ответить
СообщениеСпасибо!
такой подсчёт суммы BK17+AK17+Y17+K17+BO17 чреват ошибками

Никогда бы не подумал!!!
Получается в моём случае надо так:
Код

=ЕСЛИОШИБКА(СУММ(J1;V1;AH1;BH1;BL1);0)

А это надо наверное и в первой и второй части, если уж чревато ошибками?!
Спасибо ещё раз!!!

Автор - ZorKon71
Дата добавления - 18.09.2022 в 12:21
Pelena Дата: Воскресенье, 18.09.2022, 15:40 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 18560
Репутация: 4204 ±
Замечаний: ±

Excel 2016 & Mac Excel
Думаю, что в данном случае и без ЕСЛИОШИБКА() будет работать, если, конечно, там нет более сложных вычислений


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеДумаю, что в данном случае и без ЕСЛИОШИБКА() будет работать, если, конечно, там нет более сложных вычислений

Автор - Pelena
Дата добавления - 18.09.2022 в 15:40
Gustav Дата: Воскресенье, 18.09.2022, 15:52 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 2141
Репутация: 841 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Получается в моём случае надо так:
=ЕСЛИОШИБКА(СУММ(J1;V1;AH1;BH1;BL1);0)

Не думаю, что так надо делать.

В прошлой редакции формулы:
Код
=ЕСЛИОШИБКА(J1+V1+AH1+BH1+BL1;0)
функция ЕСЛИОШИБКА хоронила весь результат, пытаясь сложить пустой текст из ячейки BH1 (она оказалась не пустая, а именно с пустым текстом) с суммой остальных нормальных ячеек с числами.

В новой редакции формулы:
Код
=СУММ(J1;V1;AH1;BH1;BL1)
функция СУММ решает вопросы преобразования непустого текста в число 0 до сложения, поэтому она выдает вполне разумный результат - сумму четырех остальных нормальных ячеек.

По-хорошему прошлую редакцию нужно было бы записать с обработкой ошибки по каждому слагаемому, типа как-то так:
Код
=ЕСЛИОШИБКА(J1;0)+ЕСЛИОШИБКА(V1;0)+ЕСЛИОШИБКА(AH1;0)+ЕСЛИОШИБКА(BH1;0)+ЕСЛИОШИБКА(BL1;0)


Но дело в том, что в ячейке BH1 - НЕТ ОШИБКИ, а есть вполне легитимный пустой текст. Ошибка же возникает при попытке выполнения операции сложения "+". Но я с трудом (и ужасом!) представляю себе формулу с ЕСЛИОШИБКА на каждую возможную пару слагаемых из пяти (т.е. не просто четырех последовательных знаков "+", как они идут в формуле, а знаков "+" в количестве числа сочетаний из 5 по 2).

И слава Богу, что есть функция СУММ, оставляющая за кадром все подобные переживания.

Теперь о сочетании ЕСЛИОШИБКА(СУММ. Вы, видимо, по инерции пытаетесь пристроить ЕСЛИОШИБКА и в новую формулу, думая, что так будет надежнее. Но, как уже сказано, ошибки, которые вы ожидали и пытались обрабатывать, теперь нивелированы функцией СУММ. Если же вдруг в ячейке BH1 возникнет настоящая ошибка, например, деление на 0 - #ДЕЛ/0!, то СУММ ее отобразит как ошибку, а ЕСЛИОШИБКА(СУММ скроет за общим значением 0, и вы опять будете выяснять, из-за какого из пяти слагаемых это произошло - медвежья услуга!

С ЕСЛИОШИБКА при СУММ, скорее, имеет какое-то право на жизнь такая редакция формулы:
Код
=СУММ(ЕСЛИОШИБКА(J1;0);ЕСЛИОШИБКА(V1;0);ЕСЛИОШИБКА(AH1;0);ЕСЛИОШИБКА(BH1;0);ЕСЛИОШИБКА(BL1;0))

Но подобным образом, пардон, "перебдевать" сразу на все случае жизни... Не знаю, сами решайте! Можно добавлять обработку ошибок по мере их устойчивого возникновения. Возникает в ячейке BH1 деление на 0 - ну, добавим для нее (и только для нее!) обработку:
Код
=СУММ(J1;V1;AH1;ЕСЛИОШИБКА(BH1;0);BL1)


Но это всё, возвращаясь в русло исходного вопроса, лишь мои фантазии с делением на 0. Если же максимальная "ошибка" - не слишком удобный текст в ячейке, который вполне логично превращается в 0 при суммировании, то - обычная формула с СУММ без общей ЕСЛИОШИБКА:
Код
=СУММ(J1;V1;AH1;BH1;BL1)


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
Получается в моём случае надо так:
=ЕСЛИОШИБКА(СУММ(J1;V1;AH1;BH1;BL1);0)

Не думаю, что так надо делать.

В прошлой редакции формулы:
Код
=ЕСЛИОШИБКА(J1+V1+AH1+BH1+BL1;0)
функция ЕСЛИОШИБКА хоронила весь результат, пытаясь сложить пустой текст из ячейки BH1 (она оказалась не пустая, а именно с пустым текстом) с суммой остальных нормальных ячеек с числами.

В новой редакции формулы:
Код
=СУММ(J1;V1;AH1;BH1;BL1)
функция СУММ решает вопросы преобразования непустого текста в число 0 до сложения, поэтому она выдает вполне разумный результат - сумму четырех остальных нормальных ячеек.

По-хорошему прошлую редакцию нужно было бы записать с обработкой ошибки по каждому слагаемому, типа как-то так:
Код
=ЕСЛИОШИБКА(J1;0)+ЕСЛИОШИБКА(V1;0)+ЕСЛИОШИБКА(AH1;0)+ЕСЛИОШИБКА(BH1;0)+ЕСЛИОШИБКА(BL1;0)


Но дело в том, что в ячейке BH1 - НЕТ ОШИБКИ, а есть вполне легитимный пустой текст. Ошибка же возникает при попытке выполнения операции сложения "+". Но я с трудом (и ужасом!) представляю себе формулу с ЕСЛИОШИБКА на каждую возможную пару слагаемых из пяти (т.е. не просто четырех последовательных знаков "+", как они идут в формуле, а знаков "+" в количестве числа сочетаний из 5 по 2).

И слава Богу, что есть функция СУММ, оставляющая за кадром все подобные переживания.

Теперь о сочетании ЕСЛИОШИБКА(СУММ. Вы, видимо, по инерции пытаетесь пристроить ЕСЛИОШИБКА и в новую формулу, думая, что так будет надежнее. Но, как уже сказано, ошибки, которые вы ожидали и пытались обрабатывать, теперь нивелированы функцией СУММ. Если же вдруг в ячейке BH1 возникнет настоящая ошибка, например, деление на 0 - #ДЕЛ/0!, то СУММ ее отобразит как ошибку, а ЕСЛИОШИБКА(СУММ скроет за общим значением 0, и вы опять будете выяснять, из-за какого из пяти слагаемых это произошло - медвежья услуга!

С ЕСЛИОШИБКА при СУММ, скорее, имеет какое-то право на жизнь такая редакция формулы:
Код
=СУММ(ЕСЛИОШИБКА(J1;0);ЕСЛИОШИБКА(V1;0);ЕСЛИОШИБКА(AH1;0);ЕСЛИОШИБКА(BH1;0);ЕСЛИОШИБКА(BL1;0))

Но подобным образом, пардон, "перебдевать" сразу на все случае жизни... Не знаю, сами решайте! Можно добавлять обработку ошибок по мере их устойчивого возникновения. Возникает в ячейке BH1 деление на 0 - ну, добавим для нее (и только для нее!) обработку:
Код
=СУММ(J1;V1;AH1;ЕСЛИОШИБКА(BH1;0);BL1)


Но это всё, возвращаясь в русло исходного вопроса, лишь мои фантазии с делением на 0. Если же максимальная "ошибка" - не слишком удобный текст в ячейке, который вполне логично превращается в 0 при суммировании, то - обычная формула с СУММ без общей ЕСЛИОШИБКА:
Код
=СУММ(J1;V1;AH1;BH1;BL1)

Автор - Gustav
Дата добавления - 18.09.2022 в 15:52
ZorKon71 Дата: Воскресенье, 18.09.2022, 18:28 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 35
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Не думаю, что так надо делать.

Понятно, спасибо.
Учту замечания...
Хорошо что сделал контроль двух подитогов! И не мог подумать, что может такая разница получиться...
 
Ответить
Сообщение
Не думаю, что так надо делать.

Понятно, спасибо.
Учту замечания...
Хорошо что сделал контроль двух подитогов! И не мог подумать, что может такая разница получиться...

Автор - ZorKon71
Дата добавления - 18.09.2022 в 18:28
ZorKon71 Дата: Воскресенье, 18.09.2022, 18:30 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 35
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
если, конечно, там нет более сложных вычислений

Попробую... Ошибки там разные... от дел на ноль до знач...
Спасибо!
 
Ответить
Сообщение
если, конечно, там нет более сложных вычислений

Попробую... Ошибки там разные... от дел на ноль до знач...
Спасибо!

Автор - ZorKon71
Дата добавления - 18.09.2022 в 18:30
Pelena Дата: Воскресенье, 18.09.2022, 19:30 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 18560
Репутация: 4204 ±
Замечаний: ±

Excel 2016 & Mac Excel
Я подозреваю, что там надо складывать столбцы, которые можно выбрать, исходя из заголовка столбца (шапки таблицы), тогда можно приспособить СУММЕСЛИ().
Но шапку мы не видим, поэтому и решение такое себе)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЯ подозреваю, что там надо складывать столбцы, которые можно выбрать, исходя из заголовка столбца (шапки таблицы), тогда можно приспособить СУММЕСЛИ().
Но шапку мы не видим, поэтому и решение такое себе)

Автор - Pelena
Дата добавления - 18.09.2022 в 19:30
ZorKon71 Дата: Воскресенье, 18.09.2022, 22:30 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 35
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Я подозреваю, что там надо складывать столбцы, которые можно выбрать, исходя из заголовка столбца (шапки таблицы)

Не... левая часть просто складывает одинаковые столбцы справа.
Вот только правая часть наполняется в соответствии с заголовками. Потому там и возможны результаты с ошибками.
Хотя я уже задумался уже там исправлять ошибки к 0. Что бы слева убрать проверку ЕСЛИОШИБКА().
Проблема то основная в том что из строк AVTM_xx1, AVTM_xx2, AVTM_xx2 получается задвоение при сложении в одну строку AVTM.
Не ожидал. Впервые встретился с таким.
 
Ответить
Сообщение
Я подозреваю, что там надо складывать столбцы, которые можно выбрать, исходя из заголовка столбца (шапки таблицы)

Не... левая часть просто складывает одинаковые столбцы справа.
Вот только правая часть наполняется в соответствии с заголовками. Потому там и возможны результаты с ошибками.
Хотя я уже задумался уже там исправлять ошибки к 0. Что бы слева убрать проверку ЕСЛИОШИБКА().
Проблема то основная в том что из строк AVTM_xx1, AVTM_xx2, AVTM_xx2 получается задвоение при сложении в одну строку AVTM.
Не ожидал. Впервые встретился с таким.

Автор - ZorKon71
Дата добавления - 18.09.2022 в 22:30
Pelena Дата: Понедельник, 19.09.2022, 07:41 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 18560
Репутация: 4204 ±
Замечаний: ±

Excel 2016 & Mac Excel
Проблема то основная в том что из строк AVTM_xx1, AVTM_xx2, AVTM_xx2 получается задвоение при сложении в одну строку AVTM.

я не увидела задвоения. Куда смотреть? Напишите, где считается неправильно и что там должно быть на самом деле


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Проблема то основная в том что из строк AVTM_xx1, AVTM_xx2, AVTM_xx2 получается задвоение при сложении в одну строку AVTM.

я не увидела задвоения. Куда смотреть? Напишите, где считается неправильно и что там должно быть на самом деле

Автор - Pelena
Дата добавления - 19.09.2022 в 07:41
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Неверный результат СУММЕСЛИМН() или СУММ() (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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