Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Посчитать для строки разницу в заголовке между 1-ым и последним значениями.
 
#1
08.06.2010 15:04:04
Всем, добрый день!  
Помогите, пожалуйста, посчитать значения. Объяснять в тексте сложно. На примере будет понятнее (в приложении). Надеюсь на оперативную помощь!  
Заранее спасибо!
 
 
 
#2
08.06.2010 15:14:21
На примере не понятнее. Если Вам нужно это: =M5-Z5(выдаст 0), то же для следующей строки? Может переформулируете вопрос?
Я сам - дурнее всякого примера! ...
 
 
 
#3
08.06.2010 15:17:29
IMHO надо Z4-M4 и V4-I4. Макросом несложно, но вот формулой я вообще не понимаю, как подступиться.
 
 
 
#4
08.06.2010 15:22:48
{quote}{login=KuklP}{date=08.06.2010 03:14}{thema=}{post}На примере не понятнее. Если Вам нужно это: =M5-Z5(выдаст 0), то же для следующей строки? Может переформулируете вопрос?{/post}{/quote}  
 
Если прокомментировать, по формуле, то нужно найти разницу между ячейками =M4-Z4 (и для всех строк нужно находить разницу по 4 строке), при этом для каждой строки, буквы определяются по 1-ому и последнему значению в этой строке. Т.е. для строки 6 значение нужно искать = V4-I8.  
Готов дать комментарии.    
Спасибо!
 
 
 
#5
08.06.2010 15:23:32
{quote}{login=Hugo}{date=08.06.2010 03:17}{thema=}{post}IMHO надо Z4-M4 и V4-I4. Макросом несложно, но вот формулой я вообще не понимаю, как подступиться.{/post}{/quote}  
Кажется Вы правы, разность в днях. Я такое писал макросом. А формулами...
Я сам - дурнее всякого примера! ...
 
 
 
#6
08.06.2010 15:28:15
Что-нубудь типа  
=VLOOKUP(1;TRANSPOSE(B4:AF6);3;TRUE)  
может сработать?
 
 
 
#7
08.06.2010 15:38:28
{quote}{login=The_Prist}{date=08.06.2010 03:33}{thema=}{post}В данном примере вычитаем из значения первой непустой значение последней непустой.{/post}{/quote}  
 
The_prist спасибо за вариант, но нужно получить разницу для этих ячеек только в заголовке. Для 5-ой строки 25-12=13, для 6-ой строки 21-8=13.  
 
to all: могли бы Вы поделится макросом? Добавлю, что строк в реальном файле 5-10 тыс. шт.  
 
Спасибо!
 
 
 
#8
08.06.2010 15:44:16
{quote}{login=}{date=08.06.2010 03:38}{thema=Re: }{post}{quote}{login=The_Prist}{date=08.06.2010 03:33}{thema=}{post}В данном примере вычитаем из значения первой непустой значение последней непустой.{/post}{/quote}  
 
The_prist спасибо за вариант, но нужно получить разницу для этих ячеек только в заголовке. Для 5-ой строки 25-12=13, для 6-ой строки 21-8=13.  
 
to all: могли бы Вы поделится макросом? Добавлю, что строк в реальном файле 5-10 тыс. шт.  
 
Спасибо!{/post}{/quote}  
 
Упрощённый вариант The_prist:  
=ПОИСКПОЗ(3.02E+32;$B5:$AF5)-СУММПРОИЗВ(ПОИСКПОЗ(ИСТИНА;$B5:$AF5<>0;0))
 
 
 
#9
08.06.2010 15:50:33
Макросом:  
Public Sub RaznDat()  
Dim SPCol As Integer  
Dim SPCol1 As Integer  
Dim SPCol2 As Integer  
Dim SPRows As Integer  
Dim d As Integer  
Application.ScreenUpdating = False  
With ActiveSheet  
SPCol2 = .Columns.Count  
SPRows = Cells(Rows.Count, 1).End(xlUp).Row  
For d = 5 To SPRows 'строки  
   SPCol = Cells(d, 1).End(xlToRight).Column  
   SPCol1 = Cells(d, SPCol2).End(xlToLeft).Column  
   .Cells(d, 33) = Cells(4, SPCol1) - Cells(4, SPCol)  
Next  
End With  
Application.ScreenUpdating = True  
End Sub
Я сам - дурнее всякого примера! ...
 
 
 
#10
08.06.2010 15:52:27
The_Prist, объясни, пожалуйста, как работает функция ПОИСКПОЗ(ИСТИНА;$B5:$AF5<>0;0). То, что это работа с массивами понятно. А дальше...
 
 
 
#11
08.06.2010 16:02:34
{quote}{login=The_Prist}{date=08.06.2010 03:49}{thema=Re: Re: Re: }{post}{quote}{login=Sergey S.}{date=08.06.2010 03:44}{thema=Re: Re: }{post}Упрощённый вариант The_prist:  
=ПОИСКПОЗ(3.02E+32;$B5:$AF5)-СУММПРОИЗВ(ПОИСКПОЗ(ИСТИНА;$B5:$AF5<>0;0)){/post}{/quote}Добавлю, что такой вариант актуален лишь в том случае, если заголовки не меняются и идут по порядку. В данном случае, раз уж это дни месяцев, то да, пойдет и упрощенный вариант...{/post}{/quote}  
 
Спасибо за варианты, у меня 2003 офис и формула пишет - Ошибка фомулы, может ли быть связано с версией?
 
 
 
#12
08.06.2010 16:13:41
{quote}{login=The_Prist}{date=08.06.2010 04:06}{thema=}{post}ПОИСКПОЗ(ИСТИНА;$B5:$AF5<>0;0)  
 
$B5:$AF5<>0 - создается массив, где в качестве элементов булевы значения - ИСТИНА и ЛОЖЬ. И в этом массиве мы ищем номер первого вхождения, удовлетворяющего критерию - в данном случае Критерий - ИСТИНА.  
 
В принципе все это хорошо разбирается в пошаговом вычислении формулы. Советую испробовать, если я объяснил непонятно...{/post}{/quote}  
 
Спасибо! Разобрался!!! Вещь =)
 
 
 
#13
08.06.2010 17:20:26
{quote}{login=The_Prist}{date=08.06.2010 04:07}{thema=Re: Re: Re: Re: Re: }{post}{quote}{login=}{date=08.06.2010 04:02}{thema=Re: Re: Re: Re: }{post}Спасибо за варианты, у меня 2003 офис и формула пишет - Ошибка фомулы, может ли быть связано с версией?{/post}{/quote}Не должно. Вы мой пример скачали и там ошибка?{/post}{/quote}  
 
The_prist мучаюсь уже час - могли бы Вы выслать с изменненным диапазоном файлик?, который посчитает нужное значение.  
 
Спасибо!
 
 
 
#14
08.06.2010 17:25:15
пожалуйста
 
 
 
#15
08.06.2010 17:33:33
пытаюсь разобраться, как это работает - можете объяснить, что в формуле ПОИСКПОЗ(3.02E+32;$B5:$AF5)означает 3.02E+32? Спасибо.
 
 
 
#16
08.06.2010 17:35:37
Н-да. В файле "на5-10тыс. строк" такая формула будет весить прилично. А чем макрос хуже?  
Public Sub RaznDat()  
Dim SPCol As Integer  
Dim SPCol1 As Integer  
Dim SPCol2 As Integer  
Dim SPRows As Integer  
Dim d As Integer  
Application.ScreenUpdating = False  
With ActiveSheet  
.Columns(33).ClearContents  
SPCol2 = .Columns.Count  
SPRows = Cells(Rows.Count, 1).End(xlUp).Row  
For d = 5 To SPRows 'строки  
SPCol = Cells(d, 1).End(xlToRight).Column  
SPCol1 = Cells(d, SPCol2).End(xlToLeft).Column  
.Cells(d, 33) = Cells(4, SPCol1) - Cells(4, SPCol)  
Next  
End With  
Application.ScreenUpdating = True  
End Sub  
Это с пересчетом(вначале удаляется стобец результатов).  
19 строк
Я сам - дурнее всякого примера! ...
 
 
 
#17
08.06.2010 17:44:52
Ну тогда уж если привязываемся к первому столбцу слева, то можно привязаться к 33-му справа (мало ли что там правее написано:  
 
SPCol1 = Cells(d, 33).End(xlToLeft).Column  
 
и SPCol2 не нужно.  
Я бы тоже делал макросом.
 
 
 
#18
08.06.2010 17:49:27
Ну да. Можно и так.
Я сам - дурнее всякого примера! ...
 
 
 
#19
08.06.2010 18:02:48
А никто больше не обратил внимание, что если учитывать первый и последний день, результат возрастает на единицу?  
То есть: один день отработан за месяц - результат:0.  
С учетом сказанного у меня нужно:  
.Cells(d, 33) = Cells(4, SPCol1) - Cells(4, SPCol)+1
Я сам - дурнее всякого примера! ...
 
 
 
#20
08.06.2010 18:54:07
Разрешите мой вариант выложить.  
 
=МАКС(($B5:$AF5<>"")*($B$4:$AF$4))-НАИМЕНЬШИЙ(ЕСЛИ($B5:$AF5<>"";СТОЛБЕЦ($B5:$AF5)-1);СТОЛБЕЦ(A5))
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#21
08.06.2010 19:37:49
Или:  
=ПОИСКПОЗ(1E+300;B5:AF5)-ИНДЕКС(ПОИСКПОЗ(;-(B5:AF5=""););)
 
 
 
#22
08.06.2010 19:57:49
еще вариант:  
{=МАКС(ЕСЛИ(($B$4:$AF$4+B5:AF5)<>$B$4:$AF$4;$B$4:$AF$4))-МИН(ЕСЛИ(($B$4:$AF$4+B5:AF5)<>$B$4:$AF$4;$B$4:$AF$4))}
 
 
 
#23
08.06.2010 20:08:04
Хотя автору наверное это нужно:  
=ПОИСКПОЗ(1E+300;B5:AF5)-ПОИСКПОЗ(МИН(B5:AF5);B5:AF5;)  
Формула не массивная и быстрая...
 
 
 
#24
08.06.2010 20:10:28
И что я тут делал? :-)
Я сам - дурнее всякого примера! ...
 
 
 
#25
08.06.2010 20:31:09
{quote}{login=kim}{date=08.06.2010 08:08}{thema=}{post}Хотя автору наверное это нужно:  
=ПОИСКПОЗ(1E+300;B5:AF5)-ПОИСКПОЗ(МИН(B5:AF5);B5:AF5;)  
Формула не массивная и быстрая...{/post}{/quote}  
 
Ваша первая формула лучше, эта будет считать ошибочно, если первое число слева >1
 
 
 
#26
08.06.2010 21:44:12
5nizza, а как же разуметь вот эти слова автора?  
"The_prist спасибо за вариант, но нужно получить разницу для этих ячеек только в заголовке. Для 5-ой строки 25-12=13, для 6-ой строки 21-8=13"
 
 
 
#27
08.06.2010 22:18:58
Меня сбил с панталыку post_130038.xls - ориентировался на него, там почему-то в ячейке H5 стоит константа 2, вот и подумалось: "почему это автор для пятой строки считает  25-12=13, может он хочет найти первую позицию минимального значения?" Вобщем, как говорится, подогнал результат :)  
Сейчас скачал оригинальный файл от автора и вижу - двоечки то той и нету.  
Так что первая формула верна (кстати как массив вводить не нужно, правда KL на такой фокус ругается, но думаю, в производительности формула сильно не потеряет, да и в наших любимых объединенных ячейках использовать можно :-), кстати нужно у Кирилла спросить: что всетаки лучше в данной ситуации ИНДЕКС() или СУММПРОИЗВ())  
Вторая формула не верна.
 
 
 
#28
09.06.2010 05:17:18
Сделал проверку на наличие ошибок и МИН заработала.  
 
=МАКС(($B5:$AF5<>"")*($B$4:$AF$4))-МИН(ЕСЛИ($B5:$AF5<>"";$B$4:$AF$4))
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#29
09.06.2010 08:30:56
Спасибо за объяснение! Теперь понятно.
 
 
 
Страницы: 1
Читают тему
Наверх