PDA

Показать полную графическую версию : [решено] Посчитать сумму


VanUliK
19-04-2012, 12:17
как в 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
Как посчитать сумму значений находящихся до дроби (числитель) и посчитать сумму значений находящихся после дроби (знаменатель)

Имеется тема тут (http://forum.oszone.net/thread-232955.html)
Там вопрос звучал для программы Open Office Calc, то же самое помогите реализовать в Excel'e

Iska
19-04-2012, 14:24
Например, так (для Microsoft Excel):

http://img543.imageshack.us/img543/2975/image00020120419141816.png

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

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

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

VanUliK
20-04-2012, 12:26
Iska, а что выводит функция в стобец B?
В OpenOffice.org Calc формулы получились таковыми
=FIND("/";$A12)
=VALUE(LEFT($A1;$B1-1))
=VALUE(MID($A1;$B1+1;LEN($A1)))
Соответствие команд Excel и OOo Calc (http://wiki.services.openoffice.org/wiki/%D0%A1%D0%BE%D0%BE%D1%82%D0%B2%D0%B5%D1%82%D1%81%D1%82%D0%B2%D0%B8%D0%B5_%D0%BA%D0%BE%D0%BC%D0%B0%D0 %BD%D0%B4_Excel_%D0%B8_OOo_Calc)

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

VanUliK
20-04-2012, 12:41
а не взглянете на эту (http://forum.oszone.net/thread-233147.html) темку?
попробую объяснить попроще:
есть таблица:
http://s019.radikal.ru/i615/1204/09/327f6a566b7a.jpg
в столбце кол-во часов надо автоматически подсчитывать кол-во часов (значение в знаменателе из столбцов ТО ТР К), т.е. если встречается ТО, то берем значение знаменателя из стобца ТО и т.д. (вроде бы попонятней пояснил.

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

а не взглянете на эту (http://www.oszone.net/go.php?url=http://forum.oszone.net/thread-233147.html) темку? »
Я смотрел. Оно мне не понравилось ;) Я в своё время пытался приобщить главного инженера к сделанной базе данных по ТО, ему не понравилось — «многа букафф», он остался на ручном «рулении» ТО в листах таблицы Excel, в чём я ему не стал мешать. Так что, сожалею, но — увольте, нет желания.

VanUliK
20-04-2012, 15:23
Сижу ломаю голову, начал поэтапно, вот что наломал:
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)

okshef
20-04-2012, 15:42
может есть какие-нить функции цикла »
есть функции счетесли и суммесли (countif и sumif), а еще суммпроизв (SUMPRODUCT)

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

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

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

okshef
20-04-2012, 16:36
VanUliK, формула для V9
=СУММПРОИЗВ(--((J9:U9)="ТО"))*(ЗНАЧЕН(ПСТР(D9;ПОИСК("/";D9)+1;ДЛСТР(D9)-ПОИСК("/";D9))))+(СУММПРОИЗВ(--((J9:U9)="ТР")))*(ЗНАЧЕН(ПСТР(E9;ПОИСК("/";E9)+1;ДЛСТР(E9)-ПОИСК("/";E9))))+(СУММПРОИЗВ(--((J9:U9)="К")))*(ЗНАЧЕН(ПСТР(F9;ПОИСК("/";F9)+1;ДЛСТР(F9)-ПОИСК("/";F9))))

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

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

VanUliK
23-04-2012, 09:51
=СУММПРОИЗВ(--((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))))

okshef
29-04-2012, 16:15
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)
)

Как вы понимаете, для дальнейшей оптимизации своего ума не хватило, обратился к планетянам (http://www.planetaexcel.ru/forum.php?thread_id=40878):)
из решений:
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. Ценность последних трех формул в их универсальности. Если будут добавлены новые параметры в первые столбцы (например, между А и В), формулу не нужно менять - она учтет добавленный диапазон.

DjArtas
12-11-2015, 13:52
как в OpenOffice Calc подсчитать количество буквы X со всех ячеек A2:E4?

http://forum.oszone.net/attachment.php?attachmentid=130902&d=1447325511

okshef
12-11-2015, 21:14
DjArtas, в Excel эта задача решается с помощью функции СЧЁТЕСЛИ (https://support.office.com/ru-RU/article/СЧЁТЕСЛИ-функция-СЧЁТЕСЛИ-E0DE10C6-F885-4E71-ABB4-1F464816DF34)(диапазон;условие).
Соответственно в OOc c помощью COUNTIF (https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_COUNTIF_function)




© OSzone.net 2001-2012