Как у чисел разделить целую и дробную часть и вывести каждую сумму в разных ячейках?

Question

AntHTML @anthtml

Системный администратор программист радиолюбитель

Как у чисел разделить целую и дробную часть и вывести каждую сумму в разных ячейках?

Необходимо просуммировать числа в диапазоне ячеек (30-40 ячеек), но так чтобы целые части чисел суммировались отдельно, а дробные отдельно.
К примеру результатами суммы 8.1+7.3+4.2 были числа 19 и 6 (в разных ячейках).
Возможно ли это сделать группируя формулы СУММ(), СУММЕСЛИ(), ОТБОР(), ОСТАТ() или данная операция возможна только с привлечением макросов VBA?

Вопрос задан более трёх лет назад
5425 просмотров

1 комментарий

Подписаться 1 Простой 1 комментарий

Решения вопроса 1

6 комментариев

AntHTML @anthtml Автор вопроса

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

Написано более трёх лет назад
Антон Киселёв @zamboga

AntHTML, можно и формулами, но придётся вводить колонку для промежуточных значений. Стандартный СУММ() и все его вариации работают либо с диапазоном, либо с массивом отдельных данных, и этот массив надо собирать ручками или макросом.

UPD. Можно без промежуточных столбцов, через формулы массива { }.

Написано более трёх лет назад
Антон Киселёв @zamboga

AntHTML, а разные диапазоны -- в разных столбцах?

Написано более трёх лет назад
AntHTML @anthtml Автор вопроса

Помог немного скорректированный Вариант 2. Через формулы массива. Спасибо

Написано более трёх лет назад
Антон Киселёв @zamboga

AntHTML, а что пришлось исправить?
У меня на тестовом файле ошибок нет: https://yadi.sk/d/obnm-KEq3RZicq

Написано более трёх лет назад
AntHTML @anthtml Автор вопроса

zamboga, там просто в диапазон попало еще пару строк, поетому пришлось внутрь засунуть проверку ЕЧИСЛО, потому как просто СУММ такие вещи сама пропускает, а сборная функция в ошибку ушла

Написано более трёх лет назад

Пригласить эксперта

Ответы на вопрос 2

14 комментариев

AntHTML @anthtml Автор вопроса

Целая часть =ЦЕЛОЕ(СУММ(A1:A3))

Выбивает целое от общей суммы, причем с округлением в большую сторону
Дробная часть =ЗНАЧЕН(ЗАМЕНИТЬ(СУММ(A1:A3);1;ДЛСТР(ЦЕЛОЕ(СУММ(A1:A3)))+1;""))

Вообще что-то непонятное считает.
А мне нужно СУММ(ОТБОР()) и СУММ(ОСТАТ()) - но вот как подставить в формулы для обработки диапазона функцию для каждого элемента

Написано более трёх лет назад
h8nor @honor8

Дайте ваши цифры, я проверю (можете сохранить в CSV)
Вот этот диапазон A1:A3 нужно в формулах заменить на ваш диапазон.
ЗЫ: Если вам макросами понятнее, пишите в VBA.

Написано более трёх лет назад
Антон Киселёв @zamboga

Дѣаволъ, да нагенерите самостоятельно через СЛУЧМЕЖДУ.

У вас основная ошибка в формулах в том, что вы сначала вычисляете сумму, а потом вычленяете целую или дробную части. А надо наоборот.

Написано более трёх лет назад
h8nor @honor8

zamboga, лады, ~~вписал исключение, если дробная часть отсутствует~~. Понял, нельзя дробную часть отдельно в виде числа представлять, т.к. мы не знаем, сколько порядков после запятой.
Нужно уточнить.

Написано более трёх лет назад
Антон Киселёв @zamboga

Дѣаволъ, да причём тут обработка ошибок, если, сорян за тавтологию, у вас ошибка в самой логике?

У вас формула
=ЦЕЛОЕ(СУММ(A1:A3))
Т.е. вы сначала суммируете, а потом вычленяете целую часть. А надо наоборот.

Написано более трёх лет назад
Антон Киселёв @zamboga

Дѣаволъ, дробную часть можно выразить проще, без заморочек с "заменой" и в независимости от знаков после запятой:
=A1-ЦЕЛОЕ(A1)

Написано более трёх лет назад
Антон Киселёв @zamboga

AntHTML,
Выбивает целое ... с округлением в большую сторону

Вы ошибаетесь.

Функция ЦЕЛОЕ просто отбрасывает дробную часть.
Т.е. из числа 5,1 вернет 5; из числа 5,9999 вернет тоже 5.

Написано более трёх лет назад
h8nor @honor8

сорян за тавтологию, у вас ошибка в самой логике?
zamboga, пусть автор сначала ответит на ваш вопрос. Потому как по примеру в теме с логикой у меня всё в порядке.

дробную часть можно выразить проще, без заморочек ... =A1-ЦЕЛОЕ(A1)
С массивами формула не сработает.

Написано более трёх лет назад
Антон Киселёв @zamboga

Дѣаволъ, у автора вопроса, AntHTML, просто пример чисел неудачный: там сумма дробной части меньше 1.

На объёме данных 30-40 чисел (см исходный вопрос) сумма дробной части почти в любом случае станет больше 1 и ваше решение будет ошибочным.
Например, можете проверить ваш способ на наборе 7,8+5,9+3,8. Ваша формула вернёт 17, а надо 15.

Написано более трёх лет назад
h8nor @honor8

zamboga, да, я так и считал.
Если ваше решение верно, пусть автор использует формулы массива (плюсанул). Это самое рациональное решение.

Написано более трёх лет назад
Антон Киселёв @zamboga

Дѣаволъ,
=A1-ЦЕЛОЕ(A1)
С массивами формула не сработает.

Сработает. Одномерный массив — тоже массив, и обрабатывается также. https://yadi.sk/d/obnm-KEq3RZicq

Написано более трёх лет назад
h8nor @honor8

zamboga, дык, нет в формуле массива {=A1-ЦЕЛОЕ(A1)}. Введите массив.

Написано более трёх лет назад
Антон Киселёв @zamboga

Дѣаволъ, ну а пруф я зачем выложил?
Скачайте файл по ссылке из прошлого сообщения и проверьте ячейку F1.

Написано более трёх лет назад
h8nor @honor8

zamboga, напишу конкретнее. В ячейке F1 в формуле массива, которую вы написали, указаны ссылки на ячейку. Замените ссылки на ячейку на ссылки на массив ячеек, и проверьте результат.

Написано более трёх лет назад

Комментировать

Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации

Похожие вопросы

Excel

Средний
Как реализовать поиcк значений для множества ячеек excel?
- 2 подписчика
- 9 часов назад
- 15 просмотров
0

ответов
Excel

Средний
Как из ячейки извлечь структурированные данные?
- 2 подписчика
- 14 апр.
- 264 просмотра
1

ответ
Excel

Средний
Как разделить содержимое ячейки?
- 2 подписчика
- 14 апр.
- 291 просмотр
1

ответ
Excel

Простой
Есть ли в excel формула вставки строк?
- 1 подписчик
- 12 апр.
- 61 просмотр
3

ответа
C#

+2 ещё

Простой
Как настроить переход по ссылкам?
- 1 подписчик
- 11 апр.
- 62 просмотра
1

ответ
Windows

+3 ещё

Средний
Как очистить кэш MS TeamsNew на компьютерах домена?
- 1 подписчик
- 10 апр.
- 56 просмотров
1

ответ
Windows

+1 ещё

Простой
Как поменять excel по умолчанию с одной версии приложения на другую?
- 1 подписчик
- 07 апр.
- 89 просмотров
2

ответа
Excel

Простой
Как в Excel по подстроке подставить сумму?
- 2 подписчика
- 27 мар.
- 168 просмотров
2

ответа
Excel

Простой
Какая формула нужна для поиска номера последней строки с определенным значением?
- 1 подписчик
- 20 мар.
- 149 просмотров
2

ответа
Excel

Средний
Как разложить число на n слагаемых?
- 1 подписчик
- 20 мар.
- 116 просмотров
1

ответ
Показать ещё Загружается…

Аналитик 1С

Автомакон

До 21 750 ₽

Специалист в Финансовый Отдел

Rails

от 50 000 до 90 000 ₽

Помощник менеджера по продажам

Rails

от 50 000 до 150 000 ₽

Запуск django-проекта с помощью gunicorn + nginx

17 апр. 2024, в 00:02

1000 руб./за проект

Нужно отредактировать PDF

17 апр. 2024, в 00:00

1500 руб./за проект

Сделать небольшой веб-сервис

16 апр. 2024, в 23:47

20000 руб./за проект

@anthtml
А если 7,8+5,9+3,8 -- то какое нужно решение по сумме "дробной части", ответ должен быть 2,5 или 0,5?

А если 6,7+5,2+7,1 -- то какой ответ "дробной части", 1,0 или 0?

Если "0.5 и 0" - то у меня есть решение.
=СУММ(A1:A4)-ЦЕЛОЕ(СУММ(A1:A4))
где A1:A4 — нужный диапазон.

UPD. Если "2.5 и 1" — то решение тоже есть, см. мой основной ответ, там UPD.

Answer 1 · 2018-01-15 12:40:45

Вариант 1. Разбить эту колонку на столбцы, символ разделения -- запятая или точка (в зависимости от исходника). Далее суммируете каждую новую колонку по отдельности.

UPD
Вариант 2. Через формулы массива.
Для суммы целых: =СУММ(ЦЕЛОЕ(A1:A4))
Для суммы дробной части: =СУММ(A1:A4-ЦЕЛОЕ(A1:A4))
A1:A4 — нужный вам диапазон.

Если не имели дело с формулами массивов, то обратите внимание, что формула будет в фигурных скобках: { }, иначе получите ошибку.
Только с клавиатуры { } вводить бесполезно, EXCEL воспримет их как текст.
Для ввода формулы массива введите формулу, как обычно, а потом нажмите CTRL+SHIFT+ENTER. Формула станет формулой массива и на экране станет в фигурных скобках. После редактирования формулы массива надо опять нажимать CTRL+SHIFT+ENTER.

Answer 2 · 2018-01-15 16:08:06

Если данные записаны в диапазоне A1:A3, то формулы для целой и дробной части:

=ЦЕЛОЕ(СУММ(A1:A3))
=ЕСЛИ(ОСТАТ(СУММ(A1:A3);1)>0;ЗАМЕНИТЬ(СУММ(A1:A3);1;ДЛСТР(ЦЕЛОЕ(СУММ(A1:A3)))+1;"");"0")

Чтобы быстро редактировать суммируемый диапазон, можно заменить в формулах A1:A3 на ссылку ДВССЫЛ(B1), записав в ячейке B1 значение на ссылаемый диапазон A1:A3.
Если массивы данных одинаковых размеров, то можно просто копировать формулы в нужную колонку.

Answer 3 · 2020-04-24 16:30:48

Я вышел из этой ситуации следующим образом:
Если данные записаны в диапазоне A1:A3, то
=ЦЕЛОЕ(СУММ(A1:A3))
=ПРАВСИМВ(ТЕКСТ(СУММ(A1:A3);"0,00");2)

Как у чисел разделить целую и дробную часть и вывести каждую сумму в разных ячейках?

Войдите, чтобы написать ответ

Минуточку внимания

Войдите на сайт