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

Вход

Регистрация

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

 

= Мир MS Excel/Вычислить разницу из "меняющихся" вычитаемых ячеек - Мир MS Excel

  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_, DrMini  
Вычислить разницу из "меняющихся" вычитаемых ячеек
ALARMus Дата: Вторник, 06.10.2015, 10:15 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 20% ±

Здравствуйте.
Помогите составить формулу для вычисления количества отпечатков на принтерах.
Надо вычитать из последних показаний счетчика первые, отдельно для монохромных отпечатков и цветных.

В файле указано цветом какие ячейки в данном случае подлежит использовать в вычислении для строки. А в последних колонках указано какой результат должны давать вычисления.
К сообщению приложен файл: 9601205.xlsx (12.2 Kb)


Сообщение отредактировал ALARMus - Вторник, 06.10.2015, 10:17
 
Ответить
СообщениеЗдравствуйте.
Помогите составить формулу для вычисления количества отпечатков на принтерах.
Надо вычитать из последних показаний счетчика первые, отдельно для монохромных отпечатков и цветных.

В файле указано цветом какие ячейки в данном случае подлежит использовать в вычислении для строки. А в последних колонках указано какой результат должны давать вычисления.

Автор - ALARMus
Дата добавления - 06.10.2015 в 10:15
китин Дата: Вторник, 06.10.2015, 11:23 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 7035
Репутация: 1079 ±
Замечаний: 0% ±

Excel 2007;2010;2016
И вам не хворать!!! :D вот такой массивный монстр получился.проверяйте
Код
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/(ПОИСК(ПРАВБ(V$1;4);$B$1:$U$1)*($B3:$U3<>""));$B3:$U3);0)-ЕСЛИОШИБКА(ИНДЕКС($B3:$U3;;ПОИСКПОЗ(СТРОЧН(ПРАВБ(V$1;4));ЕСЛИ($B3:$U3<>"";ЛЕВБ($B$1:$U$1;4));0));0)
К сообщению приложен файл: ALARMus.xlsx (12.4 Kb)


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
СообщениеИ вам не хворать!!! :D вот такой массивный монстр получился.проверяйте
Код
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/(ПОИСК(ПРАВБ(V$1;4);$B$1:$U$1)*($B3:$U3<>""));$B3:$U3);0)-ЕСЛИОШИБКА(ИНДЕКС($B3:$U3;;ПОИСКПОЗ(СТРОЧН(ПРАВБ(V$1;4));ЕСЛИ($B3:$U3<>"";ЛЕВБ($B$1:$U$1;4));0));0)

Автор - китин
Дата добавления - 06.10.2015 в 11:23
ALARMus Дата: Вторник, 06.10.2015, 13:12 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 20% ±

Спасибо - смотрю, разбираю по составляющим - чтоб понять суть.

У Вас изменились значения моего исходного файла для последних 2 строк.
Главное для последней строки где было только одно значение на всю строку.


Сообщение отредактировал ALARMus - Вторник, 06.10.2015, 15:36
 
Ответить
СообщениеСпасибо - смотрю, разбираю по составляющим - чтоб понять суть.

У Вас изменились значения моего исходного файла для последних 2 строк.
Главное для последней строки где было только одно значение на всю строку.

Автор - ALARMus
Дата добавления - 06.10.2015 в 13:12
китин Дата: Вторник, 06.10.2015, 15:36 | Сообщение № 4
Группа: Модераторы
Ранг: Экселист
Сообщений: 7035
Репутация: 1079 ±
Замечаний: 0% ±

Excel 2007;2010;2016
мдя обшибся.ну тогда еще монструознее получите.
Код
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/(ПОИСК(ПРАВБ(V$1;4);$B$1:$U$1)*($B3:$U3<>""));$B3:$U3);0)-ЕСЛИ(СЧЁТ(ЕСЛИ((ЛЕВБ($B$1:$U$1;4)=ПРАВБ(V$1;4))*$B3:$U3<>0;$B3:$U3))=1;0;ЕСЛИОШИБКА(ИНДЕКС($B3:$U3;;ПОИСКПОЗ(СТРОЧН(ПРАВБ(V$1;4));ЕСЛИ($B3:$U3<>"";ЛЕВБ($B$1:$U$1;4));0));0))
тоже массивная
К сообщению приложен файл: 3333333.xlsx (12.4 Kb)


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852


Сообщение отредактировал китин - Вторник, 06.10.2015, 15:41
 
Ответить
Сообщениемдя обшибся.ну тогда еще монструознее получите.
Код
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/(ПОИСК(ПРАВБ(V$1;4);$B$1:$U$1)*($B3:$U3<>""));$B3:$U3);0)-ЕСЛИ(СЧЁТ(ЕСЛИ((ЛЕВБ($B$1:$U$1;4)=ПРАВБ(V$1;4))*$B3:$U3<>0;$B3:$U3))=1;0;ЕСЛИОШИБКА(ИНДЕКС($B3:$U3;;ПОИСКПОЗ(СТРОЧН(ПРАВБ(V$1;4));ЕСЛИ($B3:$U3<>"";ЛЕВБ($B$1:$U$1;4));0));0))
тоже массивная

Автор - китин
Дата добавления - 06.10.2015 в 15:36
ALARMus Дата: Вторник, 06.10.2015, 16:24 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 20% ±

А можно не привязываясь к значению слов в заголовке колонок.

Чтоб не искать в колонке "ИТОГО МОНО" слова МОНО - просто заранее известно что тут будет МОНО (так же и про "ИТОГ ЦВЕТ")
А то могут потом изменить шапку и написать "МОНО ИТОГО" или еще что. (так и уменьшиться монстр :) )
 
Ответить
СообщениеА можно не привязываясь к значению слов в заголовке колонок.

Чтоб не искать в колонке "ИТОГО МОНО" слова МОНО - просто заранее известно что тут будет МОНО (так же и про "ИТОГ ЦВЕТ")
А то могут потом изменить шапку и написать "МОНО ИТОГО" или еще что. (так и уменьшиться монстр :) )

Автор - ALARMus
Дата добавления - 06.10.2015 в 16:24
pabchek Дата: Вторник, 06.10.2015, 16:53 | Сообщение № 6
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
Можно так, формула для "моно" (массивная)
Код
=МАКС(ЕСЛИОШИБКА(B3:U3*ПОИСК("моно";B$1:U$2);0))-1/МАКС(ЕСЛИОШИБКА(1/B3:U3*ПОИСК("моно";B$1:U$2);0))

для цвета
Код
=МАКС(ЕСЛИОШИБКА(B3:U3*ПОИСК("цвет";B$1:U$2);0))-1/МАКС(ЕСЛИОШИБКА(1/B3:U3*ПОИСК("цвет";B$1:U$2);0))


"Учиться, учиться и еще раз учиться!"
WM: R399923528092


Сообщение отредактировал pabchek - Вторник, 06.10.2015, 16:55
 
Ответить
СообщениеМожно так, формула для "моно" (массивная)
Код
=МАКС(ЕСЛИОШИБКА(B3:U3*ПОИСК("моно";B$1:U$2);0))-1/МАКС(ЕСЛИОШИБКА(1/B3:U3*ПОИСК("моно";B$1:U$2);0))

для цвета
Код
=МАКС(ЕСЛИОШИБКА(B3:U3*ПОИСК("цвет";B$1:U$2);0))-1/МАКС(ЕСЛИОШИБКА(1/B3:U3*ПОИСК("цвет";B$1:U$2);0))

Автор - pabchek
Дата добавления - 06.10.2015 в 16:53
китин Дата: Вторник, 06.10.2015, 17:00 | Сообщение № 7
Группа: Модераторы
Ранг: Экселист
Сообщений: 7035
Репутация: 1079 ±
Замечаний: 0% ±

Excel 2007;2010;2016
А то могут потом изменить шапку и написать "МОНО ИТОГО" или еще что
ну дык все всё могут. а вы то зачем? :D .просто захотелось одной формулой, вот и привязался к ИТОГ
ЗЫ.Виктор браво.Мне МАКС прикрутить не удалось :'(


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852


Сообщение отредактировал китин - Вторник, 06.10.2015, 17:02
 
Ответить
Сообщение
А то могут потом изменить шапку и написать "МОНО ИТОГО" или еще что
ну дык все всё могут. а вы то зачем? :D .просто захотелось одной формулой, вот и привязался к ИТОГ
ЗЫ.Виктор браво.Мне МАКС прикрутить не удалось :'(

Автор - китин
Дата добавления - 06.10.2015 в 17:00
pabchek Дата: Вторник, 06.10.2015, 17:09 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
[offtop]Щас придет AlexM и напишет формулу в 3 раза короче)))) обычно так бывает)))


"Учиться, учиться и еще раз учиться!"
WM: R399923528092
 
Ответить
Сообщение[offtop]Щас придет AlexM и напишет формулу в 3 раза короче)))) обычно так бывает)))

Автор - pabchek
Дата добавления - 06.10.2015 в 17:09
китин Дата: Вторник, 06.10.2015, 17:15 | Сообщение № 9
Группа: Модераторы
Ранг: Экселист
Сообщений: 7035
Репутация: 1079 ±
Замечаний: 0% ±

Excel 2007;2010;2016
[offtop]ну да они ГУРУ и не такое могут yes


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщение[offtop]ну да они ГУРУ и не такое могут yes

Автор - китин
Дата добавления - 06.10.2015 в 17:15
ALARMus Дата: Среда, 07.10.2015, 10:12 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 20% ±

Можно так

Ну как, как Вы это придумываете ?
Я вот смотрю и не могу понять, что именно вычисляется (кроме как ПОИСК)
 
Ответить
Сообщение
Можно так

Ну как, как Вы это придумываете ?
Я вот смотрю и не могу понять, что именно вычисляется (кроме как ПОИСК)

Автор - ALARMus
Дата добавления - 07.10.2015 в 10:12
китин Дата: Среда, 07.10.2015, 13:13 | Сообщение № 11
Группа: Модераторы
Ранг: Экселист
Сообщений: 7035
Репутация: 1079 ±
Замечаний: 0% ±

Excel 2007;2010;2016
есть волшебная клавиша F9 выделяете в строке формул ту часть формулы, которая вам непонятна,нажимаете клавишу и видите результат только этой части


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщениеесть волшебная клавиша F9 выделяете в строке формул ту часть формулы, которая вам непонятна,нажимаете клавишу и видите результат только этой части

Автор - китин
Дата добавления - 07.10.2015 в 13:13
ALARMus Дата: Среда, 07.10.2015, 13:59 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 20% ±

Формула с МАКС показывает неверный результат если
1) есть только одна цифра для МОНО\ЦВЕТ - показывает 0, а должен ту самую единственную цифру
2) если нет ни одной цифры в строке для МОНО\ЦВЕТ, то показывает ошибку (ДЕЛО...), а должна либо 0 либо пусто


Сообщение отредактировал ALARMus - Среда, 07.10.2015, 13:59
 
Ответить
СообщениеФормула с МАКС показывает неверный результат если
1) есть только одна цифра для МОНО\ЦВЕТ - показывает 0, а должен ту самую единственную цифру
2) если нет ни одной цифры в строке для МОНО\ЦВЕТ, то показывает ошибку (ДЕЛО...), а должна либо 0 либо пусто

Автор - ALARMus
Дата добавления - 07.10.2015 в 13:59
AlexM Дата: Среда, 07.10.2015, 16:44 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Коротко не получилось, но работает в Excel 2003
Код
=МАКС((ЛЕВБ(B$1:U$1)="м")*B3:U3)-(СУММ((ЛЕВБ(B$1:U$1)="м")*(B3:U3>0))>1)*МИН(ЕСЛИ((ЛЕВБ(B$1:U$1)="м")*B3:U3;B3:U3))
это формула для моно, для цвета поменять "м" на "ц"
К сообщению приложен файл: 9601205_1.xls (33.0 Kb)



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.


Сообщение отредактировал AlexM - Среда, 07.10.2015, 19:08
 
Ответить
СообщениеКоротко не получилось, но работает в Excel 2003
Код
=МАКС((ЛЕВБ(B$1:U$1)="м")*B3:U3)-(СУММ((ЛЕВБ(B$1:U$1)="м")*(B3:U3>0))>1)*МИН(ЕСЛИ((ЛЕВБ(B$1:U$1)="м")*B3:U3;B3:U3))
это формула для моно, для цвета поменять "м" на "ц"

Автор - AlexM
Дата добавления - 07.10.2015 в 16:44
китин Дата: Среда, 07.10.2015, 17:17 | Сообщение № 14
Группа: Модераторы
Ранг: Экселист
Сообщений: 7035
Репутация: 1079 ±
Замечаний: 0% ±

Excel 2007;2010;2016
Щас придет AlexM и напишет формулу в 3 раза короче

[offtop]пришел и написал :D


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщение
Щас придет AlexM и напишет формулу в 3 раза короче

[offtop]пришел и написал :D

Автор - китин
Дата добавления - 07.10.2015 в 17:17
ALARMus Дата: Четверг, 08.10.2015, 14:26 | Сообщение № 15
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 20% ±

Коротко не получилось


как всегда неожиданные обстоятельства. В некоторые ячейки пользователи умудрились вписать текст. и как следствие в результате стоит #ЗНАЧ
 
Ответить
Сообщение
Коротко не получилось


как всегда неожиданные обстоятельства. В некоторые ячейки пользователи умудрились вписать текст. и как следствие в результате стоит #ЗНАЧ

Автор - ALARMus
Дата добавления - 08.10.2015 в 14:26
Manyasha Дата: Четверг, 08.10.2015, 14:34 | Сообщение № 16
Группа: Модераторы
Ранг: Старожил
Сообщений: 2198
Репутация: 902 ±
Замечаний: 0% ±

Excel 2010, 2016
ALARMus,
Надо вычитать из последних показаний счетчика первые

а как Вы хотите вычесть текст? Такое должно срабатывать? Если да, то чему приравнивать текстовое значение?
Если нет, то запретите пользователям вводить текст (напр. проверка данных на вкладке Данные)


ЯД: 410013299366744 WM: R193491431804
 
Ответить
СообщениеALARMus,
Надо вычитать из последних показаний счетчика первые

а как Вы хотите вычесть текст? Такое должно срабатывать? Если да, то чему приравнивать текстовое значение?
Если нет, то запретите пользователям вводить текст (напр. проверка данных на вкладке Данные)

Автор - Manyasha
Дата добавления - 08.10.2015 в 14:34
ALARMus Дата: Четверг, 08.10.2015, 14:45 | Сообщение № 17
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 20% ±

я не хочу вычесть текст, текст надо не вычитать, а считать то место где текст пустым. (это не конкретное решение, а логика того как поступать с текстом)
Да и я допустил ошибку в условии, там где в строке для МОНО\ЦВЕТ только 1 цифра в итого надо не эту цифру, а 0 (т.е. нет разницы значит 0, так же как сделано для случая полной пустоты в строке для МОНО\ЦВЕТ)
Я конечно подставил в решение от AlexM условие на вычитаемое, если оно равно 0, то из уменьшаемого будет вычитаться оно же иначе то что и было раньше. Но может это как то изящнее можно :)


Сообщение отредактировал ALARMus - Четверг, 08.10.2015, 15:07
 
Ответить
Сообщениея не хочу вычесть текст, текст надо не вычитать, а считать то место где текст пустым. (это не конкретное решение, а логика того как поступать с текстом)
Да и я допустил ошибку в условии, там где в строке для МОНО\ЦВЕТ только 1 цифра в итого надо не эту цифру, а 0 (т.е. нет разницы значит 0, так же как сделано для случая полной пустоты в строке для МОНО\ЦВЕТ)
Я конечно подставил в решение от AlexM условие на вычитаемое, если оно равно 0, то из уменьшаемого будет вычитаться оно же иначе то что и было раньше. Но может это как то изящнее можно :)

Автор - ALARMus
Дата добавления - 08.10.2015 в 14:45
Manyasha Дата: Четверг, 08.10.2015, 15:22 | Сообщение № 18
Группа: Модераторы
Ранг: Старожил
Сообщений: 2198
Репутация: 902 ±
Замечаний: 0% ±

Excel 2010, 2016
ALARMus, вариант pabchek из сообщения 6 работает и с текстом. Ошибки дел/0 можно убрать с помощью функции ЕСЛИОШИБКА(...).
В варианте AlexM тоже кусочки формулы загнала в Еслиошибка...длинная получилась
Код
=МАКС(ЕСЛИОШИБКА((ЛЕВБ(B$1:U$1)="м")*B3:U3;0))-(СУММ(ЕСЛИОШИБКА((ЛЕВБ(B$1:U$1)="м")*(B3:U3>0);0))-1>0)*МИН(ЕСЛИ(ЕСЛИОШИБКА((ЛЕВБ(B$1:U$1)="м")*B3:U3;0);B3:U3))

проверьте, так должно быть?
К сообщению приложен файл: 9601205_1-1.xls (34.5 Kb)


ЯД: 410013299366744 WM: R193491431804
 
Ответить
СообщениеALARMus, вариант pabchek из сообщения 6 работает и с текстом. Ошибки дел/0 можно убрать с помощью функции ЕСЛИОШИБКА(...).
В варианте AlexM тоже кусочки формулы загнала в Еслиошибка...длинная получилась
Код
=МАКС(ЕСЛИОШИБКА((ЛЕВБ(B$1:U$1)="м")*B3:U3;0))-(СУММ(ЕСЛИОШИБКА((ЛЕВБ(B$1:U$1)="м")*(B3:U3>0);0))-1>0)*МИН(ЕСЛИ(ЕСЛИОШИБКА((ЛЕВБ(B$1:U$1)="м")*B3:U3;0);B3:U3))

проверьте, так должно быть?

Автор - Manyasha
Дата добавления - 08.10.2015 в 15:22
AlexM Дата: Пятница, 09.10.2015, 09:18 | Сообщение № 19
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Вариант Manyasha сделал без ЕСЛИОШИБКА() :)
Код
=МАКС(ЕСЛИ(ЕЧИСЛО(B3:U3);(ЛЕВБ(B$1:U$1)="м")*B3:U3))-(СУММ((ЛЕВБ(B$1:U$1)="м")*(B3:U3>0))>1)*МИН(ЕСЛИ((ЛЕВБ(B$1:U$1)="м")*ЕЧИСЛО(B3:U3);B3:U3))
К сообщению приложен файл: 9601205_1-2.xls (32.5 Kb)



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеВариант Manyasha сделал без ЕСЛИОШИБКА() :)
Код
=МАКС(ЕСЛИ(ЕЧИСЛО(B3:U3);(ЛЕВБ(B$1:U$1)="м")*B3:U3))-(СУММ((ЛЕВБ(B$1:U$1)="м")*(B3:U3>0))>1)*МИН(ЕСЛИ((ЛЕВБ(B$1:U$1)="м")*ЕЧИСЛО(B3:U3);B3:U3))

Автор - AlexM
Дата добавления - 09.10.2015 в 09:18
ALARMus Дата: Пятница, 09.10.2015, 09:33 | Сообщение № 20
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 20% ±

не получается когда уменьшаемое меньше вычитаемого - получается не отрицательное число, а словно модуль берется.
 
Ответить
Сообщениене получается когда уменьшаемое меньше вычитаемого - получается не отрицательное число, а словно модуль берется.

Автор - ALARMus
Дата добавления - 09.10.2015 в 09:33
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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