[решено] Посчитать сумму - Компьютерный форум OSzone.net

Компьютерный форум OSzone.net (http://forum.oszone.net/index.php)

- Microsoft Office (Word, Excel, Outlook и т.д.) (http://forum.oszone.net/forumdisplay.php?f=115)

- - [решено] Посчитать сумму (http://forum.oszone.net/showthread.php?t=233146)

как в OpenOffice Calc или Excel посчитать сумму значений находящихся за дробью, например:
есть ячейки со значениями
103680/128
86400/10
86400/10
51840/8
51840/8
172800/36
25920/8
103680/4
51840/6
172800/36
129600/24
Как посчитать сумму значений находящихся до дроби (числитель) и посчитать сумму значений находящихся после дроби (знаменатель)

Имеется тема тут
Там вопрос звучал для программы Open Office Calc, то же самое помогите реализовать в Excel'e

Например, так (для Microsoft Excel):

Для OpenOffice.org Calc: просто скопируйте ячейки из Microsoft Excel. Получите преобразованные данные и формулы.

VanUliK, для excel 2010: выделите диапазон с дробями. Данные -> Текст по столбцам -> Разделитель - "/"
Получите 2 столбца: в одном - числители, в другом - знаменатели

А в формулах (второй вариант), если у вас, например, число в А2:
Для числителей: =ЗНАЧЕН(ПСТР(A2;1;ПОИСК("/";A2)-1))
Для знаменателей: =ЗНАЧЕН(ПСТР(A2;ПОИСК("/";A2)+1;ДЛСТР(A2)-ПОИСК("/";A2)))

Iska, а что выводит функция в стобец B?
В OpenOffice.org Calc формулы получились таковыми
=FIND("/";$A12)
=VALUE(LEFT($A1;$B1-1))
=VALUE(MID($A1;$B1+1;LEN($A1)))
Соответствие команд Excel и OOo Calc

или так
числитель=VALUE(LEFT($А9;FIND("/";$А9)-1))
знаменатель==VALUE(MID($А9;FIND("/";$А9)+1;LEN($А9)))

а не взглянете на эту темку?
попробую объяснить попроще:
есть таблица:

в столбце кол-во часов надо автоматически подсчитывать кол-во часов (значение в знаменателе из столбцов ТО ТР К), т.е. если встречается ТО, то берем значение знаменателя из стобца ТО и т.д. (вроде бы попонятней пояснил.

VanUliK, в столбец «B» выводится позиция символа «/» из строки в столбце «A». Мне думалось, что так Вам будут понятнее формулы.

Цитата:

Цитата VanUliK

а не взглянете на эту темку? »

Я смотрел. Оно мне не понравилось ;) Я в своё время пытался приобщить главного инженера к сделанной базе данных по ТО, ему не понравилось — «многа букафф», он остался на ручном «рулении» ТО в листах таблицы Excel, в чём я ему не стал мешать. Так что, сожалею, но — увольте, нет желания.

Сижу ломаю голову, начал поэтапно, вот что наломал:
http://s019.radikal.ru/i612/1204/0b/9e689148fca2.jpg
Все эти формулы можно объединить в одну, но это такой трэш получится. помогите для начала упростить формулу для ячейки сумма

может есть какие-нить функции цикла. Вставить в цикл проверку условия и выполнять по 12 раз, всё же сократится формула...

уф.... вот что накорябал:
это только для ячейки D2, при проверке на наличие надписи ТО

Код:

=IF(IF(D2="ТО";1;0)=1;VALUE(MID($A2;FIND("/";$A2)+1;LEN($A2)));0)

Если таких 36 строчек сложить то получится то, что я хочу))) пока составишь такую формулу, я вручную посчитаю быстрее.

Если быть точнее то для той же самой ячейки, если проверять на наличие ТО, ТР и К, то получается следующее:

Код:

=IF(IF(D2="ТО";1;0)=1;VALUE(MID($A2;FIND("/";$A2)+1;LEN($B2)));0)+IF(IF(D2="ТР";1;0)=1;VALUE(MID($B2;FIND("/";$B2)+1;LEN($C2)));0)+IF(IF(D2="К";1;0)=1;VALUE(MID($C2;FIND("/";$A2)+1;LEN($C2)));0)

Цитата:

Цитата VanUliK

может есть какие-нить функции цикла »

есть функции счетесли и суммесли (countif и sumif), а еще суммпроизв (SUMPRODUCT)

У вас в столбцах D-O меняются данные?

И почему вы подсчитываете общее количество ТО и ТР?
Это количество считается формулой =счётз(D2:O2)

В целом обрисую картину, это график ППР, в числителе стоит значение часов между ремонтами, в знаменатели стоит время простоя оборудования, мне надо знать сколько в году простаивает оборудование (при остановах на ТО ТР и К)
Значения в столбцах D-O ещё не полностью внесены, нужно расчитать и расставить их так, чтобы не получилось в каком-то месяце электрик "с мыльной задницей" в каком-то - "семечки щелкает"
В дальнейшем, при составлении на следующий год, соответственно будут изменения.

VanUliK, формула для V9

Код:

=СУММПРОИЗВ(--((J9:U9)="ТО"))*(ЗНАЧЕН(ПСТР(D9;ПОИСК("/";D9)+1;ДЛСТР(D9)-ПОИСК("/";D9))))+(СУММПРОИЗВ(--((J9:U9)="ТР")))*(ЗНАЧЕН(ПСТР(E9;ПОИСК("/";E9)+1;ДЛСТР(E9)-ПОИСК("/";E9))))+(СУММПРОИЗВ(--((J9:U9)="К")))*(ЗНАЧЕН(ПСТР(F9;ПОИСК("/";F9)+1;ДЛСТР(F9)-ПОИСК("/";F9))))

В понедельник буду на работе, попробую, а что означает в формуле "--" логический оператор "Если"?

Нет. Это =-1*(-1) - служит для преобразования логических 0 и 1 в цифры (двойное отрицание)

Цитата:

Цитата okshef

=СУММПРОИЗВ(--((J9:U9)="ТО"))*(ЗНАЧЕН(ПСТР(D9;ПОИСК("/";D9)+1;ДЛСТР(D9)-ПОИСК("/";D9))))+(СУММПРОИЗВ(--((J9:U9)="ТР")))*(ЗНАЧЕН(ПСТР(E9;ПОИСК("/";E9)+1;ДЛСТР(E9)-ПОИСК("/";E9))))+(СУММПРОИЗВ(--((J9:U9)="К")))*(ЗНАЧЕН(ПСТР(F9;ПОИСК("/";F9)+1;ДЛСТР(F9)-ПОИСК("/";F9)))) »

Спасибо, всё работает, вот так формула выглядит после перевода в опенофис:

Код:

=SUMPRODUCT(--((J9:U9)="ТО"))*(VALUE(MID(D9;SEARCH("/";D9)+1;LEN(D9)-SEARCH("/";D9))))+(SUMPRODUCT(--((J9:U9)="ТР")))*(VALUE(MID(E9;SEARCH("/";E9)+1;LEN(E9)-SEARCH("/";E9))))+(SUMPRODUCT(--((J9:U9)="К")))*(VALUE(MID(F9;SEARCH("/";F9)+1;LEN(F9)-SEARCH("/";F9))))

VanUliK, немножко формулу оптимизировал

Код:

=СУММПРОИЗВ(((J9:U9)="ТО")*ПСТР(D9;ПОИСК("/";D9)+1;10)

+((J9:U9)="ТР")*ПСТР(E9;ПОИСК("/";E9)+1;10))+((J9:U9)="К")*ПСТР(F9;ПОИСК("/";F9)+1;10))

в ней функция СУММПРОИЗВ используется только один раз, максимальное количество возвращаемых символов принимается разным 10, не выполняется преобразование текста в число - преобразование происходит при перемножении. Убрал "--" - справляется без него.

Кстати, интересный способ записи формул в ячейку с использованием переноса строк, и в скобках проще разобраться:

Код:

=СУММПРОИЗВ(

((J9:U9)="ТО")*ПСТР(D9;ПОИСК("/";D9)+1;10)

+((J9:U9)="ТР")*ПСТР(E9;ПОИСК("/";E9)+1;10)

+((J9:U9)="К")*ПСТР(F9;ПОИСК("/";F9)+1;10)

)

Как вы понимаете, для дальнейшей оптимизации своего ума не хватило, обратился к планетянам:)
из решений:
1. Вводится как формула массива (Ctrl+Shift+Enter - CSE), фигурные скобки "рисовать" не нужно, они появляются после нажатия CSE:

Код:

=СУММ(ЕСЛИ(D$7:F$7=ТРАНСП(J9:U9);--(ЗАМЕНИТЬ(D9:F9;1;ПОИСК("/";D9:F9);))))

протягивается по диапазону
2. Не массивная формула:

Код:

=СУММПРОИЗВ(СЧЁТЕСЛИ($J9:$U9;$D$7:$F$7);--ЗАМЕНИТЬ($D9:$F9;1;ПОИСК("/";$D9:$F9);))

3. Массивная (CSE), но очень быстрая

Код:

=СУММ(ПСТР(D9:F9;ПОИСК("/";D9:F9)+1;9)*СЧЁТЕСЛИ(J9:U9;$D$7:$F$7))

P.S. Ценность последних трех формул в их универсальности. Если будут добавлены новые параметры в первые столбцы (например, между А и В), формулу не нужно менять - она учтет добавленный диапазон.

как в OpenOffice Calc подсчитать количество буквы X со всех ячеек A2:E4?

http://forum.oszone.net/attachment.p...2&d=1447325511

DjArtas, в Excel эта задача решается с помощью функции СЧЁТЕСЛИ(диапазон;условие).
Соответственно в OOc c помощью COUNTIF