Поиск максимального значения в процессе суммирования

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по VBA » Поиск максимального значения в процессе суммирования (Макросы/Sub)

ant6729

Дата: Вторник, 02.05.2017, 03:15 | Сообщение № 1

Группа: Проверенные

Ранг: Ветеран

Сообщений: 533

Репутация: 2 ±

Замечаний: 40% ±

Excel 2010

Всем доброй ночи!

Есть ограничение по сумме для последнего в первой колонке значения (=4 в этом случае), Ограничение суммы для данного значения по второй колонке условно = 30.
Нужно, чтобы если это ограничение превышается, искалось первое максимальное во второй колонке значение, удовлетворяющее условию не более 30.

Если поиск первого сверху вниз по второй колонке максимального значения завершен и максимальное значение, удовлетворяющее условию найдено, далее исполняется строка кода, например, Range ("H1" ) = 33 или какой-нибудь Sub

Напротив первого найденного максимального значения из второй колонки, удовлетворяющего условию , в первой колонке ставится значение Cells (lr,1) value(так напишу).

Подскажите, пожалуйста, как решить эту искусственную задачу. Пример приложил. Во вложениях пока что неудачные попытки.

К сообщению приложен файл: 6669200.xlsm (18.6 Kb)

Сообщение отредактировал ant6729 - Вторник, 02.05.2017, 03:17

Ответить

Michael_S

Дата: Вторник, 02.05.2017, 05:02 | Сообщение № 2

Группа: Друзья

Ранг: Старожил

Сообщений: 2012

Репутация: 373 ±

Замечаний: 0% ±

Excel2016

Что-то непонятно... что с чем и где суммируется

Покажите пример вручную, как должно быть.

Сообщение отредактировал Michael_S - Вторник, 02.05.2017, 05:03

Ответить

ant6729

Дата: Вторник, 02.05.2017, 09:05 | Сообщение № 3

Группа: Проверенные

Ранг: Ветеран

Сообщений: 533

Репутация: 2 ±

Замечаний: 40% ±

Excel 2010

Michael_S, вложил документ, в нем на Лист2 пример действия кода.

К сообщению приложен файл: 4252416.xlsm (19.8 Kb)

Ответить

Wasilich

Дата: Вторник, 02.05.2017, 12:16 | Сообщение № 4

Группа: Друзья

Ранг: Старожил

Сообщений: 1232

Репутация: 326 ±

Замечаний: 0% ±

2003

Цитата ant6729, 02.05.2017 в 09:05, в сообщении № 3 ()

на Лист2 пример действия кода.

Цитата

Поиск максимального значения происходит в выделенной области Он будет равен 7 После этого напротив него станет 4 Всё

Так что ли?[vba]

Код

Sub www()
  ps = Selection.Row
  vs = Selection.Rows.Count
  x = 0
  For i = ps To ps + vs - 1
    If Cells(i, "B") > x Then
       x = Cells(i, "B")
       sz = i
    End If
  Next
  Cells(sz, "A") = 4
End Sub

[/vba]

Ответить

ant6729

Дата: Вторник, 02.05.2017, 21:07 | Сообщение № 5

Группа: Проверенные

Ранг: Ветеран

Сообщений: 533

Репутация: 2 ±

Замечаний: 40% ±

Excel 2010

Вообщем, выделенная область имел ввиду не selection а область, по которой нужно считать.
Но взял на вооружение Вашу идею, немного извращался... опять в своем стиле. И получил то, что нужно.
[vba]

Код

Sub Макрос3()

    Range("A1").Select
    Selection.End(xlDown).Select
    Selection.Offset(0, 1).Select
    Selection.Offset(1, 0).Select

    Range(Selection, Selection.End(xlDown)).Select

lr = Cells(Rows.Count, 1).End(xlUp).Row

ps = Selection.Row
vs = Selection.Rows.Count
x = 0
For i = ps To ps + vs - 1
    If Cells(i, "B") > x Then
    x = Cells(i, "B")
    sz = i
    End If
Next
Cells(sz, "A") = Cells(lr, 1).Value

ActiveWorkbook.Worksheets("Лист1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Лист1").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "A1:A300"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Лист1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

[/vba]

Спасибо, Wasilich!

Ответить

RAN

Дата: Вторник, 02.05.2017, 21:33 | Сообщение № 6

Группа: Друзья

Ранг: Экселист

Сообщений: 5660

Репутация: 1163 ±

Замечаний: 0% ±

2010

Я, конечно, дико извиняюсь, но в чем великая сермяжная правда этого попрыгунства?

Цитата ant6729, 02.05.2017 в 21:07, в сообщении № 5 ()

  Range("A1").Select
    Selection.End(xlDown).Select
    Selection.Offset(0, 1).Select
    Selection.Offset(1, 0).Select

    Range(Selection, Selection.End(xlDown)).Select

Идем в 1 класс, потом во 2, потом... в 10, и получаем по шапке.
А сразу зайти в 10 и получить по шапке слабо?

Быть или не быть, вот в чем загвоздка!

Ответить

ant6729

Дата: Вторник, 02.05.2017, 22:20 | Сообщение № 7

Группа: Проверенные

Ранг: Ветеран

Сообщений: 533

Репутация: 2 ±

Замечаний: 40% ±

Excel 2010

RAN, ну, не могу я пока, как Вы писать в одну строку. ..Пытался... пришлось лезть в макрорекодер...
Подскажите, пожалуйста, как Вы видите?!

Ответить

RAN

Дата: Вторник, 02.05.2017, 22:53 | Сообщение № 8

Группа: Друзья

Ранг: Экселист

Сообщений: 5660

Репутация: 1163 ±

Замечаний: 0% ±

2010

[vba]

Код

Range("A1").Select
    Selection.End(xlDown).Select ' пущай будет A5
    Selection.Offset(0, 1).Select ' B5
    Selection.Offset(1, 0).Select ' B6
    Range(Selection, Selection.End(xlDown)).Select ' B6: (Selection.End(xlDown) пущай будет B100)

[/vba]
итого в сухом остатке
[vba]

Код

Range(Range("A1").End(xlDown).Offset(1, 1), Range("A1").End(xlDown).Offset(1, 1).End(xlDown)).Select

[/vba]
Я, конечно, думаю, что можно и проще, но сие дословный перевод.