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

Question

[AntHTML]

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

Comments

[Антон Киселёв]

@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

[Антон Киселёв]

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

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

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

Comments

[AntHTML]

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

[Антон Киселёв]

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

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

[Антон Киселёв]

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

[AntHTML]

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

[Антон Киселёв]

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

[AntHTML]

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

Answer 2

[h8nor]

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

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

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

Comments

[AntHTML]

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

Выбивает целое от общей суммы, причем с округлением в большую сторону

Дробная часть =ЗНАЧЕН(ЗАМЕНИТЬ(СУММ(A1:A3);1;ДЛСТР(ЦЕЛОЕ(СУММ(A1:A3)))+1;""))

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

[h8nor]

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

[Антон Киселёв]

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

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

[h8nor]

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

[Антон Киселёв]

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

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

[Антон Киселёв]

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

[Антон Киселёв]

AntHTML,

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

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

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

[h8nor]

сорян за тавтологию, у вас ошибка в самой логике?

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

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

С массивами формула не сработает.

[Антон Киселёв]

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

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

[h8nor]

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

[Антон Киселёв]

Дѣаволъ,

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

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

[h8nor]

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

[Антон Киселёв]

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

[h8nor]

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

Answer 3

[Sergey]

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