Максимальное значение с условием - Мир MS Excel

Регистрация

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

= Мир MS Excel/Максимальное значение с условием - Мир MS Excel

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

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

Максимальное значение с условием (Макросы/Sub)

Максимальное значение с условием

Дата: Воскресенье, 07.10.2018, 16:57 | Сообщение № 1

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

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

Сообщений: 523

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

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

Excel 2016

Добрый день!
И вновь два больших массива
Пытаюсь в столбец R подставить максимальное значение номера квартиры из M:N
Формула массива

Код

=МАКС((Q2=$M$2:$M$64)*$N$2:$N$64)

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

Код

Sub МинМакс()
    Application.ScreenUpdating = 0
    Application.Calculation = 3
    r0_ = 2 '
    n1_ = Cells(Rows.Count, 13).End(3).Row - r0_ + 1
    n2_ = Cells(Rows.Count, 17).End(3).Row - r0_ + 1
    Cells(r0_, 18).Clear
    ar1 = Cells(r0_, 13).Resize(n1_, 2)
    ar2 = Cells(r0_, 17)
    Set slov = CreateObject("Scripting.Dictionary")
    With slov
        For i = 1 To n2_
            .Item(ar2(i, 1)) = i
        Next i
        For j = 1 To n1_
            If .exists(ar1(j, 1)) Then
                s_ = .Item(ar1(j, 1))
                For k = 2 To 2
                    ar1(j, k) = ar2(s_, k)
                Next k
            End If
        Next j
    End With
    Cells(r0_, 18) = ar1
    Application.Calculation = 1
    Application.ScreenUpdating = 1
End Sub

[/vba] но, во-первых он ругается на[vba]

Код

.Item(ar2(i, 1)) = i

[/vba]
а, во-вторых, я как-то совсем не понимаю как заставить искать максимум, да еще и с условием совпадений элементов в столбце M со столбцом Q игнорируя ячейки, где содержатся ЛЮБЫЕ не цифры (дроби, пробелы, слеши, буквы и тд)

Добрый день!
И вновь два больших массива
Пытаюсь в столбец R подставить максимальное значение номера квартиры из M:N
Формула массива

Код

=МАКС((Q2=$M$2:$M$64)*$N$2:$N$64)

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

Код

Sub МинМакс()
    Application.ScreenUpdating = 0
    Application.Calculation = 3
    r0_ = 2 '
    n1_ = Cells(Rows.Count, 13).End(3).Row - r0_ + 1
    n2_ = Cells(Rows.Count, 17).End(3).Row - r0_ + 1
    Cells(r0_, 18).Clear
    ar1 = Cells(r0_, 13).Resize(n1_, 2)
    ar2 = Cells(r0_, 17)
    Set slov = CreateObject("Scripting.Dictionary")
    With slov
        For i = 1 To n2_
            .Item(ar2(i, 1)) = i
        Next i
        For j = 1 To n1_
            If .exists(ar1(j, 1)) Then
                s_ = .Item(ar1(j, 1))
                For k = 2 To 2
                    ar1(j, k) = ar2(s_, k)
                Next k
            End If
        Next j
    End With
    Cells(r0_, 18) = ar1
    Application.Calculation = 1
    Application.ScreenUpdating = 1
End Sub

[/vba] но, во-первых он ругается на[vba]

Код

.Item(ar2(i, 1)) = i

[/vba]
а, во-вторых, я как-то совсем не понимаю как заставить искать максимум, да еще и с условием совпадений элементов в столбце M со столбцом Q игнорируя ячейки, где содержатся ЛЮБЫЕ не цифры (дроби, пробелы, слеши, буквы и тд)AVI

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

Ответить

СообщениеДобрый день!
И вновь два больших массива
Пытаюсь в столбец R подставить максимальное значение номера квартиры из M:N
Формула массива

Код

=МАКС((Q2=$M$2:$M$64)*$N$2:$N$64)

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

Код

Sub МинМакс()
    Application.ScreenUpdating = 0
    Application.Calculation = 3
    r0_ = 2 '
    n1_ = Cells(Rows.Count, 13).End(3).Row - r0_ + 1
    n2_ = Cells(Rows.Count, 17).End(3).Row - r0_ + 1
    Cells(r0_, 18).Clear
    ar1 = Cells(r0_, 13).Resize(n1_, 2)
    ar2 = Cells(r0_, 17)
    Set slov = CreateObject("Scripting.Dictionary")
    With slov
        For i = 1 To n2_
            .Item(ar2(i, 1)) = i
        Next i
        For j = 1 To n1_
            If .exists(ar1(j, 1)) Then
                s_ = .Item(ar1(j, 1))
                For k = 2 To 2
                    ar1(j, k) = ar2(s_, k)
                Next k
            End If
        Next j
    End With
    Cells(r0_, 18) = ar1
    Application.Calculation = 1
    Application.ScreenUpdating = 1
End Sub

[/vba] но, во-первых он ругается на[vba]

Код

.Item(ar2(i, 1)) = i

[/vba]
а, во-вторых, я как-то совсем не понимаю как заставить искать максимум, да еще и с условием совпадений элементов в столбце M со столбцом Q игнорируя ячейки, где содержатся ЛЮБЫЕ не цифры (дроби, пробелы, слеши, буквы и тд)

Автор - AVI
Дата добавления - 07.10.2018 в 16:57

Дата: Воскресенье, 07.10.2018, 18:36 | Сообщение № 2

Группа: Авторы

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

Сообщений: 1161

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

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

Excel 2010

Цитата AVI, 07.10.2018 в 16:57, в сообщении № 1 ()

он ругается на

Я бы на его месте тоже стал ругаться. Вы же переменной ar2 присвоили значение типа Variant (String):[vba]

Код

ar2 = Cells(r0_, 17)

[/vba]а теперь пытаетесь с ней обращаться как с массивом.

Интуитивно понятный код - это когда интуитивно понятно, что это код.

Сообщение отредактировал StoTisteg - Воскресенье, 07.10.2018, 18:37

Ответить

Дата: Воскресенье, 07.10.2018, 18:50 | Сообщение № 3

Группа: Авторы

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

Сообщений: 1161

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

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

Excel 2010

Я не очень уловил, что Вы пытаетесь сделать, но скорее всего [vba]

Код

ar2 = Cells(r0_, 17).Resize(n2_, 2)
...
For i = 0 To Ubound(ar2)

[/vba]

Интуитивно понятный код - это когда интуитивно понятно, что это код.

Сообщение отредактировал StoTisteg - Воскресенье, 07.10.2018, 18:52

Ответить

Дата: Понедельник, 08.10.2018, 06:26 | Сообщение № 4

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

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

Сообщений: 523

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

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

Excel 2016

Да, вы правы. Написал какую-то ерунду.
Мне нужно найти и отобразить в столбец R максимальное значение из столбца N при условии сопадения адресов в столбце Q со столбцом M

Ответить

Дата: Понедельник, 08.10.2018, 09:48 | Сообщение № 5

Группа: Админы

Ранг: Местный житель

Сообщений: 16718

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

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Вашу формулу можно вот так переписать

Код

=МАКС(ЕСЛИ(Q2=$M$2:$M$64;$N$2:$N$64))

А макрос, как вариант, вот так
[vba]

Код

Sub tt()
    nM_ = Cells(Rows.Count, "M").End(3).Row - 1
    nQ_ = Cells(Rows.Count, "Q").End(3).Row - 1
    arM = Cells(2, "M").Resize(nM_, 2)
    arQ = Cells(2, "Q").Resize(nQ_)
    Set slovQ = CreateObject("Scripting.Dictionary")
    With slovQ
        For i = 1 To nQ_
            aaa = .Item(arQ(i, 1))
        Next i
        For j = 1 To nM_
            If .exists(arM(j, 1)) Then
                If IsNumeric(arM(j, 2)) Then
                    If .Item(arM(j, 1)) < arM(j, 2) Then
                        .Item(arM(j, 1)) = arM(j, 2)
                    End If
                End If
            End If
        Next j
        Cells(2, "R").Resize(nQ_) = Application.Transpose(.Items)
    End With
End Sub

[/vba]

К сообщению приложен файл: -2-3.xlsm (20.9 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Дата: Понедельник, 08.10.2018, 13:40 | Сообщение № 6

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

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

Сообщений: 523

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

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

Excel 2016

_Boroda_, Блин, я в шоке ваще... Раньше я просто уходил на полчаса от компа, когда формулой это искал... Щас успел моргуть глазом полтора раза и готово. Это просто какое-то волшебство - словари. Нашел обучалки по словарям. Пробую освоить.

А как получается, что оно максимальное ищет?

Сообщение отредактировал AVI - Понедельник, 08.10.2018, 13:41

Ответить

Дата: Понедельник, 08.10.2018, 13:42 | Сообщение № 7

Группа: Админы

Ранг: Местный житель

Сообщений: 16718

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

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Вот так

Цитата _Boroda_, 08.10.2018 в 09:48, в сообщении № 5 ()

.Item(arM(j, 1)) < arM(j, 2)

Но данные должны быть числами

Цитата _Boroda_, 08.10.2018 в 09:48, в сообщении № 5 ()

If IsNumeric(arM(j, 2)) Then

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Дата: Понедельник, 08.10.2018, 13:51 | Сообщение № 8

Группа: Админы

Ранг: Местный житель

Сообщений: 19405

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

Замечаний: ±

Excel 365 & Mac Excel

[offtop]

Цитата AVI, 08.10.2018 в 13:40, в сообщении № 6 ()

Раньше я просто уходил на полчаса от компа, когда формулой это искал

А щас и кофейку попить некогда

[/offtop]

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Дата: Понедельник, 08.10.2018, 14:09 | Сообщение № 9

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

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

Сообщений: 523

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

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

Excel 2016

Pelena,
[offtop]зато не поправлюсь)[/offtop]

Сообщение отредактировал AVI - Понедельник, 08.10.2018, 14:09

Ответить

Дата: Среда, 10.10.2018, 18:56 | Сообщение № 10

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

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

Сообщений: 523

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

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

Excel 2016

_Boroda_, метод пропускает ячейки с буквами, а подскажите, пожалуйста, как пропускать еще ячейки с пробелом и запятыми?

Ответить

Дата: Среда, 10.10.2018, 19:29 | Сообщение № 11

Группа: Админы

Ранг: Местный житель

Сообщений: 16718

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

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Это Вы о чем? В файле приведите примеры того, что нужно пропускать

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Дата: Среда, 10.10.2018, 19:51 | Сообщение № 12

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

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

Сообщений: 523

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

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

Excel 2016

_Boroda_, Добавил и выделил красным. Нужно, что бы максимум определялся только между целыми числами. Ячейки, содержащие все, что не есть целое число: пробелы, запятые, дроби, точки, буквы и прочие символы (это, кончено в идеале) - игнорировались, но если это трудоемко, то будет достаточно игнорировать Istext + Chr(160) + Chr(044). Я знаю как find'ом искать это, но это существенно скажется на скорости работы макроса.

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

Сообщение отредактировал AVI - Четверг, 11.10.2018, 04:07

Ответить

Дата: Четверг, 11.10.2018, 10:50 | Сообщение № 13

Группа: Авторы

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

Сообщений: 1161

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

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

Excel 2010

Цитата AVI, 10.10.2018 в 19:51, в сообщении № 12 ()

Нужно, что бы максимум определялся только между целыми числами

Тогда вместо [vba]

Код

If IsNumeric(arM(j, 2)) Then

[/vba] пишем [vba]

Код

If IsNumeric(arM(j, 2)) And Instr(1,arM(j, 2),",",vbTextCompare)=0 And Instr(1,arM(j, 2),".",vbTextCompare)=0 Then

[/vba]

Интуитивно понятный код - это когда интуитивно понятно, что это код.

Ответить

Дата: Четверг, 11.10.2018, 11:03 | Сообщение № 14

Группа: Админы

Ранг: Местный житель

Сообщений: 16718

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

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Цитата StoTisteg, 11.10.2018 в 10:50, в сообщении № 13 ()

If IsNumeric(arM(j, 2)) And Instr(1,arM(j, 2),",",vbTextCompare)=0 And Instr(1,arM(j, 2),".",vbTextCompare)=0 Then

StoTisteg, вы когда уже будете проверять то, что советуете?
Такой вариант
[vba]

Код

Sub tt()
    nM_ = Cells(Rows.Count, "M").End(3).Row - 1
    nQ_ = Cells(Rows.Count, "Q").End(3).Row - 1
    arM = Cells(2, "M").Resize(nM_, 2)
    arQ = Cells(2, "Q").Resize(nQ_)
    Set slovQ = CreateObject("Scripting.Dictionary")
    With slovQ
        For i = 1 To nQ_
            aaa = .Item(arQ(i, 1))
        Next i
        For j = 1 To nM_
            If .exists(arM(j, 1)) Then
                If IsNumeric(arM(j, 2)) Then
                    If CStr(CInt(arM(j, 2))) = arM(j, 2) Then
                        If .Item(arM(j, 1)) < arM(j, 2) Then
                            .Item(arM(j, 1)) = arM(j, 2)
                            hhh = .Item(arM(j, 1))
                        End If
                    End If
                End If
            End If
        Next j
        Cells(2, "R").Resize(nQ_) = Application.Transpose(.Items)
    End With
End Sub

[/vba]
Можно еще, например, проверять регэкспом наличие только чисел, но да ладно

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

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Дата: Четверг, 11.10.2018, 14:43 | Сообщение № 15

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

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

Сообщений: 523

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

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

Excel 2016

_Boroda_, я, уж, не знаю как просить, но, если значение с пробелом поменять, например, на 999 949 то вылезает ошибка overflow и ругается на строку [vba]

Код

If CStr(CInt(arM(j, 2))) = arM(j, 2) Then

[/vba]
Что-то мне подсказывает, что дело в переменной. Может поставить тип long?

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

Сообщение отредактировал AVI - Четверг, 11.10.2018, 14:46

Ответить

Дата: Четверг, 11.10.2018, 14:52 | Сообщение № 16

Группа: Админы

Ранг: Местный житель

Сообщений: 16718

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

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Вы поменяли не на 999 949, а на 999494 и поставили числовой формат с разделителями разрядов. Это разные вещи - 999494 вполне нормальный номер квартиры

А вот если Вы поставите в текстовом формате 999 949, то это другое дело

И замените CInt на CLng (надеюсь, чисел, больших 2 147 483 647, не предвидится?)

* Добавлено
Вы уже и сами догадались. Но на формат обратите внимание

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Сообщение отредактировал _Boroda_ - Четверг, 11.10.2018, 14:54

Ответить

Дата: Пятница, 12.10.2018, 03:50 | Сообщение № 17

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

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

Сообщений: 523

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

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

Excel 2016

_Boroda_, да, имелся ввиду текстовый формат) спасибо

Цитата _Boroda_, 11.10.2018 в 11:03, в сообщении № 14 ()

Можно еще, например, проверять регэкспом наличие только чисел, но да ладно

Я где-то такое видел, но сам не тыкал ни разу

Сообщение отредактировал AVI - Пятница, 12.10.2018, 05:50

Ответить

Дата: Пятница, 12.10.2018, 15:50 | Сообщение № 18

Группа: Авторы

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

Сообщений: 1161

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

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

Excel 2010

Вот в этом топике информации более чем достаточно.

Интуитивно понятный код - это когда интуитивно понятно, что это код.

Ответить

Максимальное значение с условием (Макросы/Sub)

Страница 1 из 1
1

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